HELP!!! Executing SQL script stord in variable

• ukumawat

  SSC Veteran

  Points: 250

  More actions

  May 22, 2009 at 6:22 am

  #205036

  hi all,

  i need to write query along with its result in a text file.

  plz take a look at below mentioned query.

  declare @STR as varchar(max)

  set @STR = 'SELECT

  COUNT(*)

  FROM

  PO_Receive p(NOLOCK)

  WHERE

  NOT EXISTS (select s.pk_user from s1users s(NOLOCK) where s.pk_user = p.por_receiver and s.userId = ''bk_DMUser'')'

  (1) EXEC sp_AppendToFile 'C:\uk.txt', @STR

  (2) EXEC sp_AppendToFile 'C:\uk.txt', exec (@str)

  #(2) gives error.?????????

  this query working fine, although i've created a sp(sp_AppendToFile) which takes filename and text and put it in a text file, but i want the result of this query to be saved in another variable so that it can also be put into text file.

  any help would be highly appreciated.

  thanks

• Ken Simmons

  SSCertifiable

  Points: 7822

  More actions

  May 22, 2009 at 8:07 am

  #998877

  See if this works. You need to execute the query and set the variable before passing it to the stored procedure. Since the query only returns a single value, you can set the value pretty easy.

  DECLARE @STR VARCHAR(1000)

  SET @STR =

  (SELECT COUNT(*)

  FROM PO_Receive p(NOLOCK)

  WHERE

  NOT EXISTS (select s.pk_user from s1users s(NOLOCK) where s.pk_user = p.por_receiver and s.userId = 'bk_DMUser'))

  EXEC sp_AppendToFile 'C:\uk.txt', @STR