Getting NULL Values after execution using sp_executesql

• SQL_Hunt

  SSC-Dedicated

  Points: 33518

  More actions

  July 26, 2017 at 11:51 am

  #324705

  I am trying to execute the command in the other server which are linked. EXEC @retVal = sp_executesql @dml; statement giving me the database name. But I can't hold it in a variable for my level of checks. Can yu suggest how to modify the script?

  
DECLARE @dml nvarchar(max) = N''SELECT @dml += 'SELECT name FROM ' + QUOTENAME(@linkedserverName) +
     '.[master].[sys].databases WHERE name = ''' + @DBname + ''' AND state_desc! = ''ONLINE'''

  SELECT @dml as string
--exec(@dml)
Declare @retVal SYSNAME;

  EXEC @retVal = sp_executesql @dml;

  DECLARE @vi nVARCHAR(1000)
DECLARE @vQuery nVARCHAR(1000)

  SET @vQuery = (select @dml);

  select @vQuery

  declare @i nvarchar(1000);
exec sp_executesql @vQuery, N'@i nvarchar(1000) output', @i output
select @i

  Thanks.

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  July 26, 2017 at 12:06 pm

  #1952872

  SQL-DBA-01 - Wednesday, July 26, 2017 11:51 AM

  I am trying to execute the command in the other server which are linked. EXEC @retVal = sp_executesql @dml; statement giving me the database name. But I can't hold it in a variable for my level of checks. Can yu suggest how to modify the script?

  
DECLARE @dml nvarchar(max) = N''SELECT @dml += 'SELECT name FROM ' + QUOTENAME(@linkedserverName) +
     '.[master].[sys].databases WHERE name = ''' + @DBname + ''' AND state_desc! = ''ONLINE'''

  SELECT @dml as string
--exec(@dml)
Declare @retVal SYSNAME;

  EXEC @retVal = sp_executesql @dml;

  DECLARE @vi nVARCHAR(1000)
DECLARE @vQuery nVARCHAR(1000)

  SET @vQuery = (select @dml);

  select @vQuery

  declare @i nvarchar(1000);
exec sp_executesql @vQuery, N'@i nvarchar(1000) output', @i output
select @i

  I write a fair amount of dynamic SQL, but I am really not sure what you are attempting to accomplish or what you are expecting to be returned.  With a cursory look I question your last sp_executesql statement.  Perhaps if you provide a better explaination of what you are trying to accomplish and what you are looking to have returned we could provide better answers.

• SQL_Hunt

  SSC-Dedicated

  Points: 33518

  More actions

  July 26, 2017 at 12:13 pm

  #1952875

  Lynn Pettis - Wednesday, July 26, 2017 12:06 PM

  SQL-DBA-01 - Wednesday, July 26, 2017 11:51 AM

  I am trying to execute the command in the other server which are linked. EXEC @retVal = sp_executesql @dml; statement giving me the database name. But I can't hold it in a variable for my level of checks. Can yu suggest how to modify the script?

  
DECLARE @dml nvarchar(max) = N''SELECT @dml += 'SELECT name FROM ' + QUOTENAME(@linkedserverName) +
     '.[master].[sys].databases WHERE name = ''' + @DBname + ''' AND state_desc! = ''ONLINE'''

  SELECT @dml as string
--exec(@dml)
Declare @retVal SYSNAME;

  EXEC @retVal = sp_executesql @dml;

  DECLARE @vi nVARCHAR(1000)
DECLARE @vQuery nVARCHAR(1000)

  SET @vQuery = (select @dml);

  select @vQuery

  declare @i nvarchar(1000);
exec sp_executesql @vQuery, N'@i nvarchar(1000) output', @i output
select @i

  I write a fair amount of dynamic SQL, but I am really not sure what you are attempting to accomplish or what you are expecting to be returned.  With a cursory look I question your last sp_executesql statement.  Perhaps if you provide a better explaination of what you are trying to accomplish and what you are looking to have returned we could provide better answers.

  Trying to check of the database is present in another node
  Of always on. If it's present but not online, say restoring drop it.

  Thanks.

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  July 26, 2017 at 12:23 pm

  #1952878

  Sorry, too old to do word problems.  What are you expecting to be returned by the query (queries) you are running.

• SQL_Hunt

  SSC-Dedicated

  Points: 33518

  More actions

  July 26, 2017 at 12:27 pm

  #1952879

  Alright. I just fixed it.

  Thanks.

• Jeff Moden

  SSC Guru

  Points: 1004402

  More actions

  July 26, 2017 at 4:54 pm

  #1952927

  SQL-DBA-01 - Wednesday, July 26, 2017 12:27 PM

  Alright. I just fixed it.

  Two way street here... So what did you end up doing?

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)