Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


BCP hangs in T-SQL not at DOS prompt


BCP hangs in T-SQL not at DOS prompt

Author
Message
randy 59569
randy 59569
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
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
Sergiy
Sergiy
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5825 Visits: 11396
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.
randy 59569
randy 59569
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
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
Sergiy
Sergiy
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5825 Visits: 11396
Check that the domaun account used for trusted connection is registered against the database and is allowed to select data from the table.
Everett Music-289188
Everett Music-289188
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 228
Check for uncommitted transactions.
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14942 Visits: 38940
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!

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search