Solved

Physical XML File

Posted on 2011-09-16
4
371 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 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

More Than Just A Video Library

Train for your certification. Learn the latest DevOps tools. Grow your skillset to do better work.

At Linux Academy, we release new training modules every week so you'll always be up to date on the latest tech.

Question has a verified solution.

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

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This video Micro Tutorial shows how to password-protect PDF files with free software. Many software products can do this, such as Adobe Acrobat (but not Adobe Reader), Nuance PaperPort, and Nuance Power PDF, but they are not free products. This vide…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…

705 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