Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Physical XML File

Posted on 2011-09-16
4
Medium Priority
?
387 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

609 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