Using BCP to export to XML, SQL 2008R2

I've been trying for days now to get an xml file output from SQL2008R2.  
Today I stumbled upon this suggestion and I think it could work but I'm getting errors.  
I'll list the code below and then the errors.   I'm hoping for some insight so that I can resolve the errors and make it work..

USE server
GO

EXEC SP_CONFIGURE 'SHOW ADVANCED OPTIONS',1
GO

RECONFIGURE
GO

EXEC SP_CONFIGURE 'XP_CMDSHELL', 1
GO

RECONFIGURE
GO

EXEC SP_CONFIGURE 'SHOW ADVANCED OPTIONS', 0
GO

RECONFIGURE
GO

DECLARE @FILENAME VARCHAR(50)
DECLARE @SQLCMD VARCHAR(500)

SELECT @FILENAME = 'E:\TEST\TEST.XML'

SELECT @SQLCMD = 'BCP ' +
                        '"SELECT test ' +
                        ' FROM server ' +
                        ' FOR XML PATH (''''), ROOT(''ORDERS''), TYPE "' +
                        ' QUERYOUT ' +
                        @FILENAME +
                        ' -W -T -S ' + @@SERVERNAME


EXECUTE MASTER..XP_CMDSHELL @SQLCMD      


errors:
SQLState = 08001, NativeError = 53
Error = [Microsoft][SQL Server Native Client 10.0]Named Pipes Provider: Could not open a connection to SQL Server [53].
SQLState = 08001, NativeError = 53
Error = [Microsoft][SQL Server Native Client 10.0]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is config
ured to allow remote connections. For more information see SQL Server Books Online.
SQLState = S1T00, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]Login timeout expired
NULL
rgolenorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ioannis ParaskevopoulosCommented:
Can you provide sample data and the format of XML you want to output?

Giannis
0
Mark WillsTopic AdvisorCommented:
Well, a couple of things...

I think you need a lowercase "w" as param (which is Unicode, prefer -c instead of -w) and normally include output file in quotes, and don't have a space before the server name.

ie the server name [-Sserver_name[\instance_name]]

Your server\instance name should be per the @@servername but you will need to triple check. e,g, if local server then use -S.\instance  

Note the PRINT command - have a look in message tab to make sure it is formatted correctly

DECLARE @FILENAME VARCHAR(50)
DECLARE @SQLCMD VARCHAR(500)

SELECT @FILENAME = 'E:\TEST\TEST.XML'

SELECT @SQLCMD = 'BCP ' + 
                        '"SELECT test ' + 
                        ' FROM dbname.schema.tablename ' +
                        ' FOR XML PATH (''''), ROOT(''ORDERS''), TYPE "' +
                        ' QUERYOUT "' +
                        @FILENAME +
                        '" -w -T -S' + @@SERVERNAME

PRINT @SQLCMD

EXECUTE MASTER..XP_CMDSHELL @SQLCMD      

Open in new window

0
rgolenorAuthor Commented:
mark, I had a lowercase 'w' but I set everything to uppercase when I posted just to make it look cleaner.    

I made some of your suggested changes and the print command gives this result:

BCP "SELECT testtable FROM dbname  FOR XML PATH (''), ROOT('ORDERS'), TYPE " QUERYOUT "E:\TEST\TEST.XML" -c -T -Sservername

I'm still getting the errors.  

Giannis, I'm not sure what formats are available for XML..   Here are the columns from the table i'm trying to export however:

DEP, DOC_ALIAS, DOC_NO, SCHEDULED_DATE, ORDER_DATE, CUST_PO, ITEM_CODE, NOTE, SHIPPED, ITEM_QTY, QTY_SHIPPED, SHIP_NAME, SHIP_ADDRESS, SHIP_CITY,
SHIP_STATE, SHIP_ZIP, SHIP_COUNTRY, DELIV_METH, OPS_NOTES, CONTACT_NAME, CONTACT_ADDRESS, CONTACT_CITY, CONTACT_STATE, CONTACT_ZIP, CONTACT_COUNTRY, EXWORKS, SEQUENCE
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Mark WillsTopic AdvisorCommented:
Well, the structure of the BCP command looks OK.

Bit concerned about "SELECT testtable FROM dbname  " and assume it is something more like "SELECT <column list> from dbname..testtable"

But the error message is associated with your -Sservername

So, that is what you will need to examine in more detail.

If it is the default instance, and you don't use named instances, then you don't need it. But if it is not the default instance and/or you do have named instances then you must tell it which server to use.

Either your cmdshell proxy user (which I don't think you have and doesn't necessarily have to be used) or the SQL Server Service Account cannot see the SQL server that you are referring to (ie it is on a different machine or different service, or different instance)

You can double check via SSMS - right click on the table you are exporting and go down to properties.

Toward the bottom left of the properties dialogue box, it shows you the full server name.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rgolenorAuthor Commented:
Thanks so much Mark!   I read and re-read your post(s) and after some tinkering I finally
got it to work..  I guess I just needed to understand the structure a little better as some little tweaks were all that was needed.  So here is the actual code that worked:

DECLARE @FILENAME VARCHAR(50)
DECLARE @SQLCMD VARCHAR(500)

SELECT @FILENAME = 'E:\TEST\TEST.XML'

SELECT @SQLCMD = 'BCP ' +
                        '"SELECT * ' +
                        ' FROM everest_ics.dbo.A3_AGILITY ' +
                        ' FOR XML PATH (''''), ROOT(''ORDERS''), TYPE "' +
                        ' QUERYOUT ' +
                        @FILENAME +
                        ' -c -T '


EXECUTE MASTER..XP_CMDSHELL @SQLCMD      


Results:
NULL
Starting copy...
NULL
1 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 1      Average : (1000.00 rows per sec.)
NULL
0
rgolenorAuthor Commented:
Thanks again Mark!
0
Mark WillsTopic AdvisorCommented:
Great to see you have it working. Always happy to help :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.