Solved

Automate XML file build from SQL stored proc

Posted on 2008-06-23
11
796 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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
 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

809 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