On a linked server I can execute a stored procedure A but Can Not execute stored procedure B I have a db server DBServer and a linked server LinkedServer. From the DBServer I can execute successfully a stored procedure StoredProcedureGood like : EXEC LinkedServer.DatabaseName.StoredProcedureGood But for another stored procedure: EXEC LinkedServer.DatabaseName.StoredProcedureBad It gives following error: OLE DB provider "MSOLEDBSQL" for linked server "LinkedServer" returned message "Query timeout expired". It is a small stored procedure which should take only a couple of seconds to execute. On the LinkedServer, for both stored procedures properties-> Permissions "look" the same to me. One difference is StoredProcedureGood only selects and there are no updates made inside the stored procedure StoredProcedureBad deletes and inserts in a table. I was able to execute an update using following two statements: UPDATE TOP(1) [LinkedServer].[DatabaseName].dbo.TableName set ParmValue = 11 where parmname= 'A' EXEC ('UPDATE TOP(1) [LinkedServer].[DatabaseName].dbo.TableName set ParmValue = 11 where parmname= ''A'' ') How can I get my stored procedure StoredProcedureBad to execute on LinkedServer?? Any suggestions would be greatly appreciated.

It is my understanding that dynamic SQL is part of a batch. Yet, when I listed for sqlserver.sql_batch_completed as follows

CREATE EVENT SESSION [CaptureBatch] ON SERVER 
ADD EVENT sqlserver.sql_batch_completed(
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.sql_text)
    WHERE ([sqlserver].[database_id]>(4))) 
ADD TARGET package0.event_file(SET filename=N'CaptureBatch',max_rollover_files=(0))
WITH (STARTUP_STATE=ON);

GO

ALTER EVENT SESSION [CaptureBatch] ON SERVER 
STATE = START;

I cannot see any calls to EXEC that also call EXEC. For example, the final line here does not show in my Extended Event's log.

CREATE PROCEDURE [TestThis]
AS
BEGIN
	SELECT 'Test';
END

GO

EXEC (N'EXEC TestThis');

Why is this? Are these kinds of EXEC not considered a batch or am I missing something? I cannot seem to find any extensive documentation on this event, so forgive me if I have missed some.

i have this Table sequence_data in database A . The table has create script for sequences . No I want to create all these sequences in another database B. If it is the same database i can just use a while loop and execute this one by one using the id field. BUT HOW DO I DO THIS if I want to create this sequences on DATABASE B.

trying to execute this script gives only the first select as a result

declare @sp nvarchar(max)
select @sp = concat(N'select 1 ', replicate('-', 5000), char(13) + char(10), N'select 2')
exec (@sp)

but when I lower hyphens to 1000, I get also the second select How can I overcome this limit?

Recently we used a sql code reviewing tool against our database. It is suggesting to use SP_EXECUTESQL instead of EXEC. I know SP_EXECUTESQL helps us to avoid sql injection. Is there any difference in performance when using EXEC vs SP_EXECUTESQL.

I am working on a datawarehouse. One of our staging tables that is refreshed every night has about 10 million rows. We are using a custom built ETL tool that I can't make too many changes to. The tool loads this staging table like this: truncate stage_table; insert into stage_table with (tablockx) (column1, column2, etc...) exec load_stage_table @batch_id = @batch_input The contents of load_stage_table has some setup and a select statement. I can't share the exact code, but here is a basic example. create table load_stage_table ( @batch_id varchar(max) = null ) as -- -- collect data select column1 = table1.column1, column2 = table2.column2, ... from table1 join table2 on table2.id = table1.table2_id -- many more similar joins The problem is that when I run the stored procedure as its meant to be run with our ETL tool, the run time is almost 30 minutes. However if I modify the stored procedure to have the insert statement on the inside, then it only takes 1 minute. create table load_stage_table ( @batch_id varchar(max) = null ) as -- -- collect data insert into stage_table with (tablockx) (column1, column2, etc...) select column1 = table1.column1, column2 = table2.column2, ... from table1 join table2 on table2.id = table1.table2_id -- many more similar joins After running this a few times both ways and examining the execution plans, it seems that parallelism is not used when the insert is outside the stored procedure. Does loading the table from the return, outside of the stored procedure, prevent parallelism? Or is this an indicator that the select statement needs some query tuning?

Let's say I have a table, which has four columns (a, b, oper and c) and some primary key column. oper means _arithmetic operation_ (+ - * /) here.

a  b  oper  c
-------------
2  3  +     5
4  2  /     3
6  1  *     9
8  5  -     3

As, we can see in some cases, a b != c. So, my question is how to filter out such cases? I've heard of execute, which is used for executing statements, but I don't know how to use it inside where clause. Also, I'm not generalizing the oper to _any_ arithmetic operation, but it would be nice to know, if any function exists.

I run this every month to extract database logs, and I have to change the dates as well. DECLARE @start DATETIME SET @start = CONVERT(DATETIME, '2020-08-01 00:00'); DECLARE @end DATETIME SET @end = CONVERT(DATETIME, '2020-08-31 23:59'); DECLARE @searchString1 NVARCHAR(256) = 'BACKUP'; DECLARE @searchString2 NVARCHAR(256) = ''; EXEC xp_readerrorlog 0, 1, @searchString1, @searchString2, @start, @end; EXEC xp_readerrorlog 1, 1, @searchString1, @searchString2, @start, @end; EXEC xp_readerrorlog 2, 1, @searchString1, @searchString2, @start, @end; EXEC xp_readerrorlog 3, 1, @searchString1, @searchString2, @start, @end; EXEC xp_readerrorlog 4, 1, @searchString1, @searchString2, @start, @end; EXEC xp_readerrorlog 5, 1, @searchString1, @searchString2, @start, @end; However, I get multiple results that I have to manually sift through and combine How can I do something like this: SELECT * FROM (EXEC xp_readerrorlog 0, 1, @searchString1, @searchString2, @start, @end) UNION ALL SELECT * FROM (EXEC xp_readerrorlog 1, 1, @searchString1, @searchString2, @start, @end) UNION ALL etc Obviously, this does not work. I get > Incorrect syntax near the keyword 'EXEC'. Is there a better way?

I'm trying to add my target server to the Multi Server SQ LAgent Job (MSX). We have multiple versions of SQL Agent jobs and I'm trying to automate adding only related targets according to SQL Server version. I'm trying to add target server using the script below but the the EXEC statement does not recognized the @TargetServer variable. Declare @SQLVersion varchar(10) set @SQLVersion = (SELECT CASE SUBSTRING(CONVERT(VARCHAR(50), SERVERPROPERTY('productversion')), 1, 2) WHEN '8.' THEN '2000' WHEN '9.' THEN '2005' WHEN '10' THEN '2008' WHEN '11' THEN '2012' WHEN '12' THEN '2014' WHEN '13' THEN '2016' WHEN '14' THEN '2017' WHEN '15' THEN '2019' END) Declare @TargetServer varchar(20) SET @TargetServer = 'ProdEMM' -- '2014' EXEC msdb.dbo.sp_add_jobserver @job_id=N'jb3510a9-d75f-4906-b9e3-en74b947d7ev', @server_name = N'@TargetServer' IF @SQLVersion = '2012' EXEC msdb.dbo.sp_add_jobserver @job_id=N'9E0GAK8P-0H74-41H7-A619-80TCDFD3E5G7', @server_name = N'@TargetServer' IF @SQLVersion < '2012' EXEC msdb.dbo.sp_add_jobserver @job_id=N'9E0GAK8P-0H74-41H7-A619-80TCDFD3E5G7', @server_name = N'@TargetServer' ERROR: Msg 14262, Level 16, State 1, Procedure msdb.dbo.sp_add_jobserver, Line 88 [Batch Start Line 0] The specified @server_name ('@TARGETSERVER') does not exist. What would be the best way to handle this situation?

SELECT Table_Name FROM INFORMATION_SCHEMA.TABLES WHILE (1=1) BEGIN EXEC 'drop view ' + Table_Name END or DECLARE my_cursor CURSOR FOR SELECT Table_Name FROM INFORMATION_SCHEMA.TABLES OPEN my_cursor WHILE 1 = 1 BEGIN FETCH my_cursor INTO @vname IF @@fetch_status != 0 BREAK EXEC('drop view ' + @vname) END CLOSE my_cursor; The examples more or less describe what I'm doing, but that's not the question. The question is: When should I be trying to use an explicitly defined cursor, as in the second example, and what are the conditions where the first example would work?

This seems like a basic question but I can't find any answers out there - I need to be able to get the server name/instance etc. from a linked server. I've tried a couple of things: select .@@SERVERNAME; select .SERVERPROPERTY('ServerName'); ... but no joy. Any ideas? This is SQL 2008 R2 & 2014 (2008R2 is the linked server) EDIT: Errors are: > Msg 102, Level 15, State 1, Line 2 Incorrect syntax near > '@@SERVERNAME'.

That is my first post here, need helps: in a stored procedure I had some following code CREATE PROCEDURE [dbo].[SP_getAvg] ( @projectId INT ,@carrierId INT ,@fetchType VARCHAR(20) ) AS BEGIN TRANSACTION GetDataSet * * DECLARE @recAvg FLOAT * * DECLARE @FACCT VARCHAR(20) DECLARE @counter INT DECLARE @carrierAlias AS VARCHAR(20) DECLARE @tmpDate VARCHAR(20) DECLARE @sql1 VARCHAR(500) SET @counter = 1 IF @projectId > 0 BEGIN SELECT @FACCT = FACCT FROM projects WHERE projectId = @projectId END SELECT @carrierAlias = carrierAlias FROM carriers WHERE carrierId = @carrierId SET @sql1 = 'SELECT @recAvg = ISNULL(AVG(cast(FWEIGHT as float)/case when CAST(FPIECES as float) = 0 then 1 else cast(FPIECES as float) end),0) from psorderh where (DATEPART(m, manifestDate) =' + cast(@counter AS VARCHAR(2)) + ') AND (DATEPART(yyyy, manifestDate) = DATEPART(yyyy, GETDATE()))' + CASE WHEN @projectId > 0 THEN 'AND FACCT = ' + @FACCT ELSE '' END + CASE WHEN @carrierId > 0 THEN 'and FCARRIER=' + @carrierAlias ELSE '' END EXEC (@sql1) PRINT @recAvg ...... I got this error message: >Must declare the scalar variable "@recAvg". where I did wrong?

Tested on: 2014+SP2+CU7, 2016+SP1+CU5, 2016+SP2 Short description: User with EXECUTE permission still gets > Cannot find the object 'sp_fakeProcedureName', because it does not exist or you do not have permission. when successfully EXECuting the stored procedure (example below). We are finally moving application access from db_owner to a more limited role. Everything has been fine with the user as a member of db_owner. We crated a new role db_webAppAccess and granted SELECT, UPDATE, DELETE, and EXECUTE. The problem occurs when the user executes most if not all of our stored procedures. The procedure runs, but at the very end fails with > Msg 15151, Level 16, State 1, Procedure sp_fakeProcedureName, Line 14 [Batch Start Line 2] > Cannot find the object 'sp_fakeProcedureName', because it does not exist or you do not have permission. We know it executes because when performed in SSMS the expected data is returned. Also, if we cope the contents of the stored procedure and put it into SSMS it executes without error. Now to the confusing part: if we add an explicit return value, the error disappears. Ex add return(0) as the line of the SP below. Sadly, we have a few hundred stored procedures that would need to be examined and set to have explicit return values in all cases. I am at an absolute loss on exactly what is different in executing a stored procedure when there is and is not an explicitly defined return code. Is there some additional grant I can give to allow access to the result codes? Thank you for any help you can provide! CREATE PROCEDURE [dbo].[sp_fakeProcedureName] AS BEGIN SET NOCOUNT ON; SELECT fakeTablePKIdentity, column2, column3, column4 FROM fakeTable ORDER BY column2 END GRANT EXEC ON [sp_fakeProcedureName] TO PUBLIC GO Here is the procedure I used to setup the role and user. USE [fakeDatabse] GO CREATE USER [usr_fakeUser] FOR LOGIN [DOMAIN\fakeUser] WITH DEFAULT_SCHEMA=[dbo] GO CREATE ROLE [db_fakeRole] GRANT CONNECT TO [db_fakeRole] AS [dbo] GRANT DELETE TO [db_fakeRole] AS [dbo] GRANT EXECUTE TO [db_fakeRole] AS[dbo] GRANT INSERT TO [db_fakeRole] AS [dbo] GRANT SELECT TO [db_fakeRole] AS [dbo] GRANT UPDATE TO [db_fakeRole] AS [dbo] ALTER ROLE [db_fakeRole] ADD MEMBER [usr_fakeUser] GO

I have tried to use Case and SET NOEXEC ON but get the following errors: >Syntax Error: unexpected 'SET' (set) >Syntax Error: missing 'closing parenthesis' INSERT INTO tag (table, repr, tag, value) SELECT 'product' AS table, @Id AS repr, 'product_code' AS tag, CASE @Code WHEN NOT null THEN @Code ELSE SET NOEXEC ON END AS value` @Id and @Code are declared parameters in the script

SQL server 2012, I have multiple execs calling the same package with different parameters. Normally i'll highlight one at a time and run them individually but I'd like to be able to hit go and run them all in order. One exec pulls results that were created from another so it has to be in order. Is there a way to pull this off? i'm not sure if it's the default nature but I haven't been able to verify it.

I have a stored procedure that queries a linked DB2 server in this fashion: SET @sql='SELECT * FROM openquery(DB2,''SELECT column1, column2 FROM table'')' exec sp_executesql @sql I am trying to store that result into a new table via another stored procedure INSERT INTO [schema].[Table] ([column1] ,[column2]) EXEC('schema.StoredProc') Where StoredProc contains the aforementioned openquery However, when I execute the second Stored Procedure (INSERT INTO), the output window prints out the text of @sql from my first stored procedure, and gives me the message: SELECT * FROM openquery(DB2,'SELECT column1, column2 FROM table') Msg 0, Level 11, State 0, Line 0 A severe error occurred on the current command. The results, if any, should be discarded. Any help is appreciated

I have one main database (MAIN) and several client DBs (CLIENTDB). I need to start query in MAIN and to get data from CLIENTDB. The client DB can be found also on linked server. My problem is that I need to do this in a function with EXEC, so I can get SELECT * FROM SRV.CLIENTDB, but this in not allowed by SQL Server. Is there another way to do that? I need to have code like inside the function: DECLARE @sSRV AS VARCHAR(128) = 'SRV'; DECLARE @sDB AS VARCHAR(128) = 'CLIENTDB1'; EXEC('SELECT * FROM ' + sSRV + '.' + @sDB + '.MyTable'); I need a function, because I need to make joins with it inside my engine.