BCP hangs in T-SQL not at DOS prompt

  • Trying to export XML data to an XML file. Using a T-SQL trigger script.

    Using this code seems to write the file to the proper destination but it is empty and SQL seems to hang. If I try to execute another script against the database, they just sit there saying.... executing query....

    Been fighting this for an hour or more. Hope I can get an assist here.

    If I go to DOS and execute the command

    bcp 'select xmlContent from ProcureToPay.dbo.XmlOutFiles" queryout C:\Temp\OrderNo_123.xml -w -x -T -SSqlServer

    That functions but i need the code to execute reliably as T-SQL triggers. Any assist appreciated.

    Environment: Running script tests from a workstation on the network, SQL server on another machine.

    Declare @cmd varchar(500), @dest varchar(100)

    Select @dest = (Select filename from xmlOutfiles) -- it has C:\Temp\OrderNo_123.xml in filename

    Select @cmd = 'bcp "select xmlContent from ProcureToPay.dbo.XmlOutFiles" queryout '+ @dest + ' -w -x -T -S' + @@servername

    Exec xp_cmdshell @cmd

  • When you go to DOS you execute the command in your desktop/laptop environment, and "C:\Temp\OrderNo_123.xml" is a file on your computer with your user having administrative permission over it.

    When launch it from SQL Server you execute it in the server environment, and "C:\Temp\OrderNo_123.xml" would be a file on the server, and you're trying to access it using the privileges given to the account which runs that particular SQL Server instance (check Windows Services).

    Make sure it's all in order over there.

    _____________
    Code for TallyGenerator

  • I have considered that but, and checked it after your reminder. The call does does create the EMPTY file in the proper location, under the proper name. The file size remains at 0k and is apparently held open as when I try to recover, I cannot get rid of the empty file until I kill the process in task manager.

    Any other ideas?

    Seems like part of the "push the data" to the file simply doesn't!

    Maybe relavent: The data being written to the file IS XML.

    Thanks

  • Check that the domaun account used for trusted connection is registered against the database and is allowed to select data from the table.

    _____________
    Code for TallyGenerator

  • Check for uncommitted transactions.

  • i'm thinking the issue here is what account is running SQL when you drop to xp_cmdShell; you are choosing trusted connection,w hcih means if the user running the services is not a local user or domain user, it would probably fail:

    if you run this, does it return results, or is it all nulls?

    DECLARE @Results table(

    ID int identity(1,1) NOT NULL,

    TheOutput varchar(1000))

    insert into @Results (TheOutput)

    exec master..xp_cmdshell 'whoami' --nt authority\system for example

    insert into @Results (TheOutput)

    exec master..xp_cmdshell 'cd %userprofile%' --NULL because nt authority\system is not a user...command fails.

    select * from @Results

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 6 posts - 1 through 5 (of 5 total)

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