?
Solved

Physical XML File

Posted on 2011-09-16
4
Medium Priority
?
378 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 27

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 27

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 27

Accepted Solution

by:
Zberteoc earned 2000 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

Automating Terraform w Jenkins & AWS CodeCommit

How to configure Jenkins and CodeCommit to allow users to easily create and destroy infrastructure using Terraform code.

Question has a verified solution.

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

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Many times as a report developer I've been asked to display normalized data such as three rows with values Jack, Joe, and Bob as a single comma-separated string such as 'Jack, Joe, Bob', and vice versa.  Here's how to do it. 
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

771 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