Solved

Automate XML file build from SQL stored proc

Posted on 2008-06-23
11
793 Views
Last Modified: 2010-04-21
Can anyone help with automating the build of an xml file?  Essentially, I can execute a query (see below) and view desired results in SQL Server Management Studio, then obviously copy and paste to a text file.  I need to somehow replace these last two steps, by either modifying the query to write to a desired XML file or building a DTX package to accomplish the steps (or other?).  

 SELECT [item-no] as "Style",
       (select offer as "SourceCode" from zztemp3 z where z.[item-no] = c.[item-no]  for xml path (''), type) as "SourceCodeList"
FROM   (select distinct [item-no] from zztemp3) c
FOR XML PATH ('ITEM'), Root ('ITEMS')

We are using SQL 2005.  Thanks in advance --Karen
0
Comment
Question by:PetEdge
  • 4
  • 4
  • 3
11 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 21846326
I would create an SSIS package...use a data flow task w/ your stored procedure as your source and a raw data file as your desintation (the xml)
0
 
LVL 51

Accepted Solution

by:
Mark Wills earned 400 total points
ID: 21846492
Here you go...

create procedure u_XML_ZZTEMP as
SELECT [item-no] as "Style",
       (select offer as "SourceCode" from zztemp3 z where z.[item-no] = c.[item-no]  for xml path (''), type) as "SourceCodeList"
FROM   (select distinct [item-no] from zztemp3) c
FOR XML PATH ('ITEM'), Root ('ITEMS')
GO

--  then run a bcp export - change MyDB for your database, and assumes Windows / mixed security - otherwise specifiy user and password...

declare @bcp varchar(2000)
set @bcp = 'bcp "exec MyDB..u_xml_zztemp" queryout "c:\zztemp_xml_'+convert(varchar,getdate(),112)+'.xml" -T -c -CACP'
exec xp_cmdshell @bcp
0
 

Author Comment

by:PetEdge
ID: 21846590
I've tried this but haven't been able to get it to successfully work.

In Control Flow, I've created an Execute SQL task.
Under General tab, Resultset is XML.
Under Result Set tab:
   Result Name        Variable Name
   0                          User::XMLContent

SSIS variables:
Name              Scope             Data Type    Value
XMLContent   Package          String

I am able to execute the above SQL task successfully.  (Note that the Package variable never seems to get populated).

Next, I added a Script task, see below.  When the script runs, I get the below error:

Error: The script threw an exception: The element cannot be found in a collection. This error happens when you try to retrieve an element from a collection on a container during execution of the package and the element is not there.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
Pulic Sub Main()
        Dim sw As New IO.StreamWriter("C:\Temp\test.xml")
        MsgBox(Dts.Variables("XMLContent").Value.ToString())
        sw.Write(Dts.Variables("User::XMLContent").Value.ToString())
        sw.Dispose()
        Dts.TaskResult = Dts.Results.Success
    End Sub
End Class

0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 21846801
Just add a new job to sql server agent... work your way through the pop-up, add a new step, and simply paste the three line of the bcp part of the sql.
0
 

Author Comment

by:PetEdge
ID: 21846938
Hi Mark,

I was just working my way through your solution.  By default, xp_cmdshell is turned off as part of the security configuration.  Once I turned it on in my dev environment, your code worked beautifully and the file was as desired.

Can you speak to security ramifications of turning xp_cmdshell on?

Thanks,

Karen
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 60

Expert Comment

by:chapmandew
ID: 21847092
if xp_cmdshell is on, then you're leaving your machine/network open to possible attack if someone was to get into your SQL machine w/ malicious intentions.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 21847101
Create a specific user, allow them access to xp_cmdshell... Would be inclined to create them as a Windows user as well - same password. No one logs in as that user, but you can nominate that user when setting up the Job. For all intensive purposes, it is another sysadmin type user, and really only used for batch type jobs where extra permission is required. So long as you control and "own" that user, then others cannot get access. Would be a slightly different story if you were to start creating OSQL or Windows command files where it could be easier to incorporate "unauthorised" sql code.

When it is called by a user that is not a member of the sysadmin fixed server role, xp_cmdshell connects to Windows by using the account name and password stored in the credential named ##xp_cmdshell_proxy_account##. If this proxy credential does not exist, xp_cmdshell will fail. That is what you want, so do NOT create a proxy...

Requires CONTROL SERVER permission, so hopefully "mere mortals" are not part of sysadmin, nor should they have CONTROL SERVER permissions...

My esteemed colleague mr chapmandew is probably more up to date with these types of security scenarios...

0
 
LVL 60

Assisted Solution

by:chapmandew
chapmandew earned 100 total points
ID: 21847140
Recently, we had a security firm come in and do some analysis on the db servers where I work.  They found one sql server (not one I administer, mind you.  :) ) that had a blank sa pwd.  They were able to log into that server, and with the use of xp_cmdshell create a local machine user, promote that user to a domain user.  Then, do a dump of the password hashes and promote itself to a domain admin.  From there, the world was their oyster and they could do anything they wanted on the system.  Scary stuff.
0
 

Author Comment

by:PetEdge
ID: 21847251
Mark and chanmandew-- thanks to you both.  
I would like to leave this open until I hear back from my DBA, concerning use of xp_cmdShell.  If this is nixed.. then it's back to working on the SSIS solution.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 21847263
Given @chapmandews comment, then you could always turn it on for the purposes of running that Job, then turn it off again. Because that "special" user does have some privileges, then shouldn't be a problem.

If someone can access your SQL server well enough to start using xp_cmdshell, then they have to be able to get in at a privileged level - in which case, you have a bigger security concern than just xp_cmdshell... Such as a "sa" user without a password - heavens forbid !! Not surprising - there are plenty of machines out there with blank SA password, and then there's the batch jobs, web config files, and windows command files that openly display a connection string - that is the security risk.

the commands themselves are usually not the security risk as such (there are plenty), it is more a case of having a properly and tightly controlled security matrix - something that a lot of sites do not always pay attention to when starting out, and then becomes on of those "difficult" tasks all too easy to delay until "tommorrow". That's why you hear a fair bit about things like xp_cmdshell. In reality, it is a fairly privileged function and should be treated as such.

I have no problems using and recommending, but, it is normally under the guise of "control your security matrix".
0
 

Author Closing Comment

by:PetEdge
ID: 31469809
While my DBA is adverse to opening this up to users, it can be run by a service account so I can move forward this this solution.  Thanks again!
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

744 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

11 Experts available now in Live!

Get 1:1 Help Now