July 27, 2017 at 12:47 pm
I have a query and for some reason I can't get this to work.
bcp "select [fpono] as '@ID', [fpono], [fstatus] from #postatus for XML PATH('PO'), ROOT('POStatus')"
QueryOut
"PATH\POStatusNew.xml" -c -t -T -S SERVERANME
Any help would be appreciated Thanks
July 27, 2017 at 12:56 pm
So, ... what exactly, do you mean when you say you "can't get it to work" ? What happens when you run the query? Please be more detailed than "it doesn't work". We don't know your database at all, so we've no information to go on. You'll need to be very specific. Sample data with a CREATE TABLE statement and the necessary INSERT statement(s?) would be much more valuable.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 28, 2017 at 5:54 am
Its quite a convoluted program but in the end, I am selecting data from a table and want to export it. This is the last Select statement:
--write out to "J:\Information Services\Workspace\DataXfer\FromM2M\POStatusnew.xml"
Select distinct fpono, rtrim(fstatus) as fstatus
into #postatus
from #ponew2
Except
Select distinct fpono, fstatus
from #poitems
Then I want to write out what is in #postatus to a network location.
I have successfully imported from network location from an XML but can't seem to write it.
Currently the syntax is incorrect.
I am getting Incorrect syntax near 'select [fpono] as '@ID', [fpono], [fstatus] from #postatus for XML PATH('PO'), ROOT('POStatus')'.
July 28, 2017 at 7:07 am
And how about some sample data and the CREATE TABLE statements that were asked for?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 28, 2017 at 7:14 am
They are select statements into tables such as:
Select ARIKey, fpono, fstatus,
fcreate, frelsno, OrderQty, OrigDate,
LastDate, FirstDate, LastModt,
IsConfirm, OrderQty0, DtOrderQty, IsBlanket,
IsMaster, frcpqty, OrigBlkQty
into #ponotify
from #ponew2
They are all select statements from existing tables. No tables are being created except the temp ones.
July 28, 2017 at 7:26 am
bswhipp - Friday, July 28, 2017 7:14 AMThey are select statements into tables such as:
Select ARIKey, fpono, fstatus,
fcreate, frelsno, OrderQty, OrigDate,
LastDate, FirstDate, LastModt,
IsConfirm, OrderQty0, DtOrderQty, IsBlanket,
IsMaster, frcpqty, OrigBlkQty
into #ponotify
from #ponew2They are all select statements from existing tables. No tables are being created except the temp ones.
Ya know, coming up with excuses is not a good way to get help. We NEED the details. If you insist on going against good advice, then it's a lot less likely someone will be willing to help you. I'm done with this until you post actual CREATE TABLE statements for ALL of the tables involved in at least the query you've posted so far, and also some sample data for each of those tables that at least simulates the actual data from the point of view of different scenarios that exist in your actual data. If you can't do that, then don't expect me to help you. I'm not going to ask you again.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 28, 2017 at 7:32 am
Steve, thank you for the reply. I am not a DBA by trade so I am a bit slow on the uptake here. the tables are in our ERP system so I can't get any create table statements. I will upload the complete code in a file. As for data, how would you like me to provide that? Excel file?
July 28, 2017 at 8:17 am
bswhipp - Friday, July 28, 2017 7:32 AMSteve, thank you for the reply. I am not a DBA by trade so I am a bit slow on the uptake here. the tables are in our ERP system so I can't get any create table statements. I will upload the complete code in a file. As for data, how would you like me to provide that? Excel file?
The right way to provide the data is using actual INSERT statements, after providing CREATE TABLE statements for the tables involved. If you have access to SSMS (SQL Server Management Studio), it can script out the table create statements for you. Otherwise, how are you testing your queries?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 28, 2017 at 8:26 am
I got it. It was syntax. Here is the new syntax
Declare @cmd nvarchar(255)
Set @cmd = ' bcp "select [fpono] as ''@ID'', [fpono], [fstatus] from M2MAux01.dbo.postatus for XML PATH(''PO''), ROOT(''POStatus'')" ' +
'QueryOut "\\GVL02\Shares\Information Services\Workspace\DataXfer\FromM2M\POStatusNew.xml" -c -t -T -S GVL03'
exec xp_cmdshell @cmd
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply