Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

BCP hangs in T-SQL not at DOS prompt Expand / Collapse
Author
Message
Posted Sunday, October 20, 2013 11:13 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 5, 2014 6:43 PM
Points: 2, Visits: 13
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
Post #1506538
Posted Tuesday, October 22, 2013 6:54 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 4:39 PM
Points: 4,576, Visits: 8,342
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.
Post #1507439
Posted Wednesday, October 23, 2013 10:24 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 5, 2014 6:43 PM
Points: 2, Visits: 13
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
Post #1507732
Posted Wednesday, October 23, 2013 4:17 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 4:39 PM
Points: 4,576, Visits: 8,342
Check that the domaun account used for trusted connection is registered against the database and is allowed to select data from the table.
Post #1507844
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse