declaring variables, with procedures... whats the deal?

• neotokyo

  SSCertifiable

  Points: 7239

  More actions

  February 21, 2006 at 10:55 am

  #111915

  if i wanted to do this:

  declare @output

  set @output = (xp_cmdshell "type C:\textfile.txt")

  now this flags an error, but not sure why.

  what am i doing wrong?

  _________________________

• Krishnan Kaniappan

  Ten Centuries

  Points: 1051

  More actions

  February 21, 2006 at 11:03 am

  #622391

   

  set nocount on

  declare @output varchar(8000)

  create table #output  (outputtext varchar(8000))

  insert into #output

  exec master..xp_cmdshell "type C:\test.txt"

  select @output=outputtext from #output

  drop table #output

  print @output

  -Krishnan

• neotokyo

  SSCertifiable

  Points: 7239

  More actions

  February 21, 2006 at 11:10 am

  #622395

  thanks krishnan

  just tested this out... i'm not getting any ouput

  at all. i'm not seeing the results from the text.txt

  file.

  this file by the way... just is an error log, and was

  hoping to see the text as the output.

  is there some thing else that needs to be done?

  just wondering.

  thanks again for your help.

  _________________________

• neotokyo

  SSCertifiable

  Points: 7239

  More actions

  February 21, 2006 at 11:40 am

  #622402

  this doesn't go for all procedures in variables right?? is there another way?

  _________________________

• Yelena Varshal

  SSC-Dedicated

  Points: 34286

  More actions

  February 21, 2006 at 12:55 pm

  #622428

  Hi neotokio,

  Krishnan's solution worked for me.  There are 2 things:

  1. select @output=outputtext from #output

  line just assigns the text to the variable @output. It does not print it out. you have to have another line with Select @output to see on-screen. OR just replace this line with Select * from #output.

  2. If the file name / path is not correct the only thing that the #output table will contain is an error message that it could not find a file.

  3. from your original post why your sp_cmdshell statement did not return anything and you got an error message, it is because it has to be preceeded with Exec keyword like in Krishnan's example.

  4 as it was in your original post

   set @output = (xp_cmdshell "type C:\textfile.txt")

  this @output will return a status of SP execution (return status), not a result set. But the syntax for getting a return status should be

  Execute @output = (xp_cmdshell "type C:\textfile.txt")

   

   

  Regards,Yelena Varsha