Post reply

Problem with xp_cmdshell - URGENT

  • December 22, 2004 at 4:30 am

    #85033

    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

    #534732

    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

    #534738

    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

    #534850

    Still same problem.  Is this because of NT Security?

    Mahesh

  • December 23, 2004 at 9:35 am

    #534898

    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 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply