February 25, 2008 at 2:42 pm
I am trying to write a proc that gathers data and outputs it into a txt file. I have two problems though.
In the proc I have @Body varchar(8000). This has text assigned to it. i.e. 'HR,1,45.6,^'
If I run this: EXEC master..xp_cmdshell 'osql -E -Q"select @Body" -o c:\output.txt' it creates the text file but this error is in the txt file:
Msg 137, Level 15, State 2, Line 1
Must declare the variable '@Body'.
Of course if I declare the Body within that OSQL command it won't error but my question is:
1- How do I use a variable in an OSQL command?
2- Is it possible to have a file write to another server with OSQL? I attempted to change the output file directory to another machine and it wouldn't create the file. Appropriate rights are set so it's not a permission issue. The OSQL command was EXEC master..xp_cmdshell 'osql -E -Q"select @Body" -o \\MachineName\c$\output.txt'
TIA.
February 25, 2008 at 7:11 pm
Answers:
1. You must declare the varchar inside the osql -Q (query)
example osql -E -Q "declare @body varchar(20) set @body = 'hello world!!' select @body'
2. As long as you have enough permission to the folder in the network, yes you can execute a query in osql to be saved in another folder of another machine..
EXEC master..xp_cmdshell 'osql -E -Q"select @Body" -o \\MachineName\c$\output.txt'
this will transfer nothing because you did not declare the variable @body so there is no data to be transferred
"-=Still Learning=-"
Lester Policarpio
February 26, 2008 at 5:04 am
Lester thanks for the reply.
Yeah I tried it before by declaring it in the OSQL command and it worked. I was hoping there was another way. Not sure why the file won't write to the other machine. I'm 100% sure that the permissions are fine.
Thanks for your time!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply