Solved

Physical XML File

Posted on 2011-09-16
4
336 Views
Last Modified: 2012-08-30
I have a stored proc that writes the output of a Select Statement to a table with an xml data type (see attached file).  Everything worked fine on the Development box but when I pushed to Production, the DBA has xp_cmdshell locked down and he will not unlock it just for this process.  

Is there any other way to automate the  creation a physical XML file?  I need a file to ftp to a customer. StoredProc1.txt
0
Comment
Question by:sherbug1015
  • 3
4 Comments
 
LVL 26

Expert Comment

by:Zberteoc
ID: 36553961
You will have to execute that bcp command from outside the SQL server. You could use a DTS or SSIS or a simple batch file that you can execute. You will have to find a way to synchronize the outside process with the inside process to grab the content and export it when needed.
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 36553970
You could also use the SQLCMD utility in windows to execute what query you want and redirect the result to a file but it still the same solution and that is execute the export from outside the SQL server

There is no way to export data from SQL server itself when the xp_cmdshell is disabled.
0
 

Author Comment

by:sherbug1015
ID: 36554091
When I use bcp from within the stored procedure, I am selecting the xml_data field from the table.  Can I still do this with SSIS.

If I execute the attached stored proc (minus the bcp call) from an SSIS Execute SQL Task, then I will have an XML record in the table.  

Can I then use an Execute Process Task from SSIS and basically do the same thing as what the stored procedure does?

Can you possibly give me an example of how to do this from SSIS.   I am not that familiar with SSIS.  

0
 
LVL 26

Accepted Solution

by:
Zberteoc earned 500 total points
ID: 36556257
I am not familiar with SSIS either and I will never be and that is because I will not use it. However of course is possible to to that.

But a much simpler solution would be to use a batch file to execute the bcp command.

You place the bcp command as it is built by the stored procedure in a text file with extention .bat and simply execue it from windows, or you can execute the bcp command itself.

In bcp you have to qualify the objects you use in queries with the database name they belog to because the bcp command doesn't have a parameter/switch for the database name only for the server name. In the exemple below I added DATABASE_NAME to the table name.
bcp " SELECT '<?xml version="1.0" encoding="UTF-8" ?>'+CONVERT(VARCHAR(MAX),xml_data) FROM DATABASE_NAME.tblXMLLog WHERE xml_id =(Select Max(xml_id) from DATABASE_NAME.dbo.tblXMLLog)"  queryout LocationData.xml -w -T -SBERTEATOTH-XP

Open in new window

0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now