Create XML file and copy it to Remote Web Server

Question

Create XML file and copy it to Remote Web Server

Romah

SSC-Addicted

Points: 428
More actions
December 18, 2009 at 8:59 am

#228624

What to do?
I need to create an XML file from database table and put this xml file in WebServer (I knew IP and the target folder path of webserver c:\AllSites\romah.com\).
What I did?
To solve my problem, I have written a storeprocedure that creates xml file in its local drive. Now I need to define the path in my storeprocedure to create the file in target webserver or copy the local file to target webserver after its creation.
Expected Output?
So, after executing this storeprocedure, this xml file (MyOutput.xml) should be inside c:\AllSites\romah.com\ folder of target WebServer (IP is given).
Here is the storeprocedure:
create procedure MyProc
as
begin
DECLARE @FileName VARCHAR(50)
DECLARE @SQLCmd VARCHAR(500)
SELECT @FileName = 'C:\temp\MyOutput.xml'
SELECT @SQLCmd = 'bcp ' +
'"SELECT * ' +
' FROM MyDatabase.dbo.tblTestBCP' +
' FOR XML auto,TYPE,elements,ROOT(''RecipeInfo'')"' +
' queryout ' +
@FileName +
' -S' + @@SERVERNAME + ' -Usa -Pmypasswd -c -r -t'
SELECT @SQLCmd AS 'Command to execute'
EXECUTE master..xp_cmdshell @SQLCmd
end
What will be the solution for this?
Thanks in advance !!!
Regards,
Romah

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ray K SSC-Dedicated Points: 31148 More actions · Answer 1

Just telling us what the output is supposed to look like doesn't help at all. Can you give us an actual example of how you want your output to look?

The better (and cleaner . . . and easier . . . and more succinctly . . . and more efficiently . . .) you describe this, the more responses you'll get.

Read this: http://www.sqlservercentral.com/articles/Best+Practices/61537/

+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/

Romah SSC-Addicted Points: 428 More actions · Answer 2

Dear Ray,

I don't know much more about the purpose of that xml file. But I was asked my senior to generate an xml file from the database table and put this file in webserver in specified folder. Database server and webserver are in different places and All websites in that webserver takes data from that remote database server.

In my case, I only need to execute this storeprocedure every night at 1 AM to generate the xml file and need to put that xml file to target webserver. I have scheduled the job in "SQL Server Agent" to execute this storeprocedure in specifed time. It successfully creates an xml file. But you can see in my code that it only generate the xml file in database server's local drive; not in webserver.

I need to copy or generate that xml file in webserver.

How can I do that?

Thanks !!!

Romah

Ray K SSC-Dedicated Points: 31148 More actions · Answer 3

Okay . . . that wasn't made clear in your original post, and I did not get that at all. (Just for your future reference, you should make stuff like that clear in your initial posts.)

Unfortunately, I don't have the expertise to be able to answer that.

Any other answers from the peanut gallery?

+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/

Lowell SSC Guru Points: 323506 More actions · Answer 4

details, that's the key.

you said you want to put XML in the following folder:

c:\AllSites\romah.com\ folder of target

does that mean the web server and the sql server are the same machine? or do you mean you need a seperate function to copy fromt hat folder to some web server?

is the web server on the same network? if it's not, is the "folder" for the web server exposed to http or FTP?

you really need to give us some concrete details as to what you are trying to d.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Romah SSC-Addicted Points: 428 More actions · Answer 5

Currently What I am doing in my job is

I have given IP[Lets say IPfirst], Username and Password to login another machine through Remote Desktop.

I found all the websites are in that Remote Machine [IPfirst machine] and also found those websites can access the database using this connection string

</appSettings>

In my case, I am using SQL server 2008 in that remote machine for database connection. I can access the database from that remote machine with Server Name [IPSecond that is same as in connection string], Login Name and Password provided for me.

Now, in my local machine, I have created and tested my storeprocedure to create xml file locally. It works fine and created an xml file as required. Similarly, I have copied that storeprocedure and tested in SQL Server [with IPSecond, userName and Password] and the query runs successfully without any error[No error message].

Then, I have scheduled the job in "SQL Server Agent" in IPSecond machine to execute my storeprocedure in specified time automatically. This storeprocedure needs to create that xml file in IPfirst machine's specified folder. I am trying to set the target folder path of IPfirst machine in my program.

So, how can I give that folder path in my program?

or

Is there any way to put that file in specified folder [c:\AllSites\romah.com\ ] in IPfirst?

Thanks !!!

Romah