BCP hangs in T-SQL not at DOS prompt

Question

BCP hangs in T-SQL not at DOS prompt

randy 59569

SSC Enthusiast

Points: 180
More actions
October 20, 2013 at 11:13 pm

#98605

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

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

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

Sergiy SSC Guru Points: 110208 More actions · Answer 1

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

randy 59569 SSC Enthusiast Points: 180 More actions · Answer 2

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

Sergiy SSC Guru Points: 110208 More actions · Answer 3

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

Everett Music-289188 SSC Enthusiast Points: 113 More actions · Answer 4

Everett Music-289188

SSC Enthusiast

Points: 113

January 13, 2015 at 11:17 am

#1769900

Check for uncommitted transactions.

Lowell SSC Guru Points: 323518 More actions · Answer 5

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!