December 22, 2004 at 4:30 am
Hi,
I am trying to execute a batch file (which will create a tab delimited file at specified location) by using xp_cmdshell. I am getting the result as follows:
NULL
C:\WINNT\system32>Set Server="MAHESH"
NULL
C:\WINNT\system32>Set Database=TEST
NULL
C:\WINNT\system32>Set SSTPrimary=H:\TESTSHARE\
NULL
C:\WINNT\system32>SET log=H:\TESTSHARE\TEST
NULL
C:\WINNT\system32>osql -S "MAHESH" -d TEST /E /Q
NULL
C:\WINNT\system32>pause
NULL
C:\WINNT\system32>bcp "Select * from users" queryout H:\TESTSHARE\Test.tb2 -c -S"MAHESH" -T
NULL
Starting copy...
NULL
0 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.): total 1
NULL
C:\WINNT\system32>pause
Press any key to continue . . .
NULL
And the files created with 0 KB. What is the problem ?
NOTE: If i execute that batch file manually, file is created properly with data.
Thanks,
Mahesh
December 22, 2004 at 5:08 am
Have you tried adding -E to the osql statement (trusted connection) or -Uuserid -Ppassword
Good Hunting!
AJ Ahrens
webmaster@kritter.net
December 22, 2004 at 6:00 am
To output the results of a query with bcp, you need to fully qualify the table name in your query:
bcp "Select * from TEST.dbo.users" queryout H:\TESTSHARE\Test.tb2 -c -S"MAHESH" -T
or
bcp "Select * from TEST..users" queryout H:\TESTSHARE\Test.tb2 -c -S"MAHESH" -T
Also, what is the purpose of: osql -S "MAHESH" -d TEST /E /Q
/Q (or -Q) should be followed by a query. As written, osql will connect to the database, then quit without doing anything.
December 22, 2004 at 11:35 pm
Still same problem. Is this because of NT Security?
Mahesh
December 23, 2004 at 9:35 am
When you execute the batch from xp_cmdshell, the batch file is run in the security context of the SQL Server service login id.
Another problem is that the H: drive mapping may not be available to that login id. Use a UNC name, such as \\MAHESH\TESTSHARE\Test.tb2
Also, the SQL Server service account needs to given write access to the share (TESTSHARE).
bcp "Select * from TEST.dbo.users" queryout \\MAHESH\TESTSHARE\Test.tb2 -c -S "MAHESH" -T
Alternatively, look up xp_sqlagent_proxy_account in BOL.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy