June 29, 2017 at 3:22 pm
Hello,
I want to take the sp_executesql results to a variable to further compare the results.
Please suggest how to do it.
declare @linkedserverName varchar(100) = 'servername'
declare @DB_rename varchar(100) = 'dBName'
declare @cmd_4 nvarchar(1000), @cmd_5 nvarchar(1000);
Set @cmd_4 = 'SELECT DATABASEPROPERTYEX ('''''+ @DB_rename +''''', ''''Status'''')'
--select @cmd_4
set @cmd_5 = QUOTENAME(@linkedserverName) + '.master..sp_executesql N''' + @cmd_4 + ''''
--select @cmd_5
DECLARE @vi nVARCHAR(MAX)
DECLARE @vQuery nVARCHAR(MAX)
SET @vQuery = (select @cmd_5);
select @vQuery
declare @i varchar(1000);
exec sp_executesql @vQuery, N'@i varchar(1000) output', @i output
select @i
Thanks.
June 29, 2017 at 4:26 pm
Not sure this suits your needs or not, but what about selecting it into a table like this:declare @linkedserverName varchar(100) = 'servername'
declare @DB_rename varchar(100) = 'DBName'
declare @cmd_4 nvarchar(1000), @cmd_5 nvarchar(1000);
DECLARE @tmpTable TABLE(value VARCHAR(2000))
Set @cmd_4 = 'SELECT 1'
DECLARE @vi nVARCHAR(MAX)
DECLARE @vQuery nVARCHAR(MAX)
SET @vQuery = (select @cmd_4);
select @vQuery
declare @i varchar(1000);
INSERT INTO @tmpTable
EXEC sp_executesql @vQuery
select @i = (SELECT TOP 1 value FROM @tmpTable)
SELECT @i
I changed the value of your @cmd_4.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
June 29, 2017 at 5:07 pm
bmg002's answer is my preference, as it's easiest to implement off-hand.
It looks like you're not passing the output parameter to the executesql statement on the linked server, so the result of your remote SELECT statement isn't being passed to @i. Try the below.
declare @linkedserverName varchar(100) = 'servername'
declare @DB_rename varchar(100) = 'dbName'
declare @cmd_4 nvarchar(1000), @cmd_5 nvarchar(1000);
Set @cmd_4 = 'SELECT @i = cast(DATABASEPROPERTYEX ('''''+ @DB_rename +''''', ''''Status'''') as varchar(50))'
--select @cmd_4
set @cmd_5 = QUOTENAME(@linkedserverName) + '.master..sp_executesql N''' + @cmd_4 + ''', N''@i varchar(50) output'', @i = @i output'
--select @cmd_5
DECLARE @vi nVARCHAR(MAX)
DECLARE @vQuery nVARCHAR(MAX)
SET @vQuery = (select @cmd_5);
select @vQuery
declare @i varchar(1000);
exec sp_executesql @vQuery, N'@i varchar(50) output', @i output
select @i
Inserting into a table variable (as in bmg002's code sample) does seem to have a performance hit compared to the above (0.295 seconds vs. 0.0023 seconds), but I don't think you're trying to do this at high volume, and I find that method easier to work with.
June 29, 2017 at 9:13 pm
Andrew P - Thursday, June 29, 2017 5:07 PMbmg002's answer is my preference, as it's easiest to implement off-hand.It looks like you're not passing the output parameter to the executesql statement on the linked server, so the result of your remote SELECT statement isn't being passed to @i. Try the below.
declare @linkedserverName varchar(100) = 'servername'
declare @DB_rename varchar(100) = 'dbName'
declare @cmd_4 nvarchar(1000), @cmd_5 nvarchar(1000);Set @cmd_4 = 'SELECT @i = cast(DATABASEPROPERTYEX ('''''+ @DB_rename +''''', ''''Status'''') as varchar(50))'
--select @cmd_4
set @cmd_5 = QUOTENAME(@linkedserverName) + '.master..sp_executesql N''' + @cmd_4 + ''', N''@i varchar(50) output'', @i = @i output'
--select @cmd_5
DECLARE @vi nVARCHAR(MAX)
DECLARE @vQuery nVARCHAR(MAX)SET @vQuery = (select @cmd_5);
select @vQuery
declare @i varchar(1000);
exec sp_executesql @vQuery, N'@i varchar(50) output', @i output
select @iInserting into a table variable (as in bmg002's code sample) does seem to have a performance hit compared to the above (0.295 seconds vs. 0.0023 seconds), but I don't think you're trying to do this at high volume, and I find that method easier to work with.
Many thanks Andrew!!
Thanks.
June 29, 2017 at 9:14 pm
Thanks Everyone for your response. Andrew, Not sure why it skipped my mind. I could not image to cast the databaseproperty area. Spent one hour but kind of was ignoring to look in that.
By the way while the code grows it is kind of hard to keep track of Begin...End blocks. Do you apply any addd on to SSMS to easily get notified?
Please suggest your best practices!!
Thanks.
July 3, 2017 at 8:30 am
SQL-DBA-01 - Thursday, June 29, 2017 9:14 PMThanks Everyone for your response. Andrew, Not sure why it skipped my mind. I could not image to cast the databaseproperty area. Spent one hour but kind of was ignoring to look in that.By the way while the code grows it is kind of hard to keep track of Begin...End blocks. Do you apply any addd on to SSMS to easily get notified?
Please suggest your best practices!!
If you are looking for free tools, Poor Man's T-SQL Formatter works decently. There is no real support with it so when it screws things up, it can be painful to fix it.
RedGate also has SQL Prompt, but it is a paid product. I personally prefer SQL Prompt as it does so much more than the other tool.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
July 3, 2017 at 3:40 pm
bmg002 - Monday, July 3, 2017 8:30 AMSQL-DBA-01 - Thursday, June 29, 2017 9:14 PMThanks Everyone for your response. Andrew, Not sure why it skipped my mind. I could not image to cast the databaseproperty area. Spent one hour but kind of was ignoring to look in that.By the way while the code grows it is kind of hard to keep track of Begin...End blocks. Do you apply any addd on to SSMS to easily get notified?
Please suggest your best practices!!If you are looking for free tools, Poor Man's T-SQL Formatter works decently. There is no real support with it so when it screws things up, it can be painful to fix it.
RedGate also has SQL Prompt, but it is a paid product. I personally prefer SQL Prompt as it does so much more than the other tool.
I'm disciplined with indenting when I'm writing code. Some people say I'm a little too disciplined, but it works for me. I've heard great things about SQL Prompt, but have never used it.
July 3, 2017 at 5:03 pm
bmg002 - Monday, July 3, 2017 8:30 AMIf you are looking for free tools, Poor Man's T-SQL Formatter works decently. There is no real support with it so when it screws things up, it can be painful to fix it.
RedGate also has SQL Prompt, but it is a paid product. I personally prefer SQL Prompt as it does so much more than the other tool.
SSMSBoost includes a a version of Poor Man's T-SQL Formatter (or you can use their own formatter). There is a free community edition of the product. It's a decent freebie, a lot of features. Downside is they are only in beta for SSMS 16 but the version is almost done with support for SSMS 16 and 17.
Sue
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply