question

rakeshsharma7 avatar image
rakeshsharma7 asked

Linked query Exec() problem

I need to run a query on the linked Oracle database from SQL Server, I need to pass the DATE filter(@mDate) to the query also I need to dynamically set the Server AT based on my environment(DEV, QA, PROD). below is the sample code.... can anyone pls help me... Thanks in advance...!!! DECLARE @ECount int = 0; DECLARE @sql nvarchar(MAX); DECLARE @mdate varchar(11) DECLARS @ServerAT varchar(50) = 'LS_EG_DEV' SELECT @mdate = replace(convert(varchar(11),INITIALLOADDATE,106),' ','-') FROM LOG_INITIALDATALOAD SELECT @mdate set @sql = N'BEGIN SELECT count(*) into :mCount from myTable where ply_effdate > ? and ply_branch||ply_incomeclass not in (''ARA400'',''ARA500'',''ARAAUS''); END;' EXEC (@sql, @ECount OUTPUT, @mdate) at @ServerAT; SELECT @ECount;
linked-serverdynamic-sql
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Mart avatar image
Mart answered
Hi rakeshsharma7, welcome I'd be inclined to use SQLCMD mode to execute the query that way you can define the variable at the top and change it as needed with only a slight tweak to the rest of the code, here's how: With your query in SSMS- Click 'Query' then click 'SQLCMD Mode' Now add the following to the top of the query, using your server name (it will turn grey if SQLCMD mode is enabled correctly- :SETVAR ServerAT "MyServerName" Next alter the line EXEC (@sql, @ECount OUTPUT, @mdate) at @ServerAT; So it reads EXEC (@sql, @ECount OUTPUT, @mdate) AT [$(ServerAT)]; And away you go, shout if you need any further help Martyn New Version based on feedback, not tested but print statements look good - you'll need to put mdate back in etc: DECLARE @ECount INT = 0 , @sql NVARCHAR(MAX) , @mdate VARCHAR(11) , @ServerAT VARCHAR(50) = 'LS_EG_DEV' , @ExecStatement NVARCHAR(max); SELECT @mdate = replace(convert(varchar(11),getdate(),106),' ','-') SELECT @mdate set @sql = N'BEGIN SELECT count(*) into :mCount from myTable where ply_effdate > ? and ply_branch||ply_incomeclass not in (''ARA400'',''ARA500'',''ARAAUS''); END;' --replace any quotes in the statement with double ones ready for next stage set @sql = replace(@sql, '''','''''') PRINT @sql --now build up the next statement around the first SET @ExecStatement = N'EXEC (''' + @sql + ''', ' + cast(@ECount as nvarchar(10)) + ' OUTPUT, ''' + @mdate + ''') at ' + quotename(@ServerAT) + ';' PRINT @ExecStatement
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks Martyn for your quick response...!!! I need to use this piece of code in the Stored Proc and have to read the environment variable from the table. So I need to pass the date parameter and retrieve the count as out put from the variale @ServerAT server.
0 Likes 0 ·
I see, that makes sense. In that case the best bet is to use some dynamic sql where, it will look a little complex but will all the variable to be passed easily, I'll write something and try it :)
0 Likes 0 ·
There we go, see if you can get it going from the update, the print part seems correct so would you mind having a test and letting me know, cheers Mart
0 Likes 0 ·
Hey Martyn... I have tried to execute this but this is not working and giving error as below : Cannot use the OUTPUT option when passing a constant to a stored procedure. because when we print the @Execstatement it gives below output EXEC ('BEGIN SELECT count(*) into :mCount from myTable where ply_effdate > ? and ply_branch||ply_incomeclass not in (''ARA400'',''ARA500'',''ARAAUS''); END;', 0 OUTPUT, '05-May-2016') at [LS_EG_DEV]; where it is passing 0 OUTPUT as output parameter.. I tried also as exec(@ExecStatement, @ECount output) but it is not working as well.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.