Bulk Copy Program

Hi Techies,

I have a bcp which generates .txt file perfectly. I just wanted to know how can i generate a text file in distributed environment.

Assuming that my Sql Server is running in machine A. I wanted the bcp to generate in Machine B. What are the permission's i should give in order to generate it in Machine B.

Regards
--Tanveerthe user who executes the bcp-command would need write access. read access is usually handy to validate the file has been written but I don't think its required. In case machine B only has a filesystem, that user obviously needs filesystem write access. In case machine B is a sqlserver that user would need write access on the table the file is insert into.|||Thanks for the reply... I guess i have not mentioned that the above program would be executed through stored procedure. In this scenario what are the permission i should give and to which users.

--Tanveer|||Assuming bcp and the copy is executed using xp_cmdshell, the user is the user configured to run sqlserver (exec master..xp_cmdshell 'set'). If it's local user (only known to the server) you'll find it difficult to do the windows-copy. If it's a domain user it'll be easier to grant the write access. Do you have difficulty creating the .txt file using the stored procedure?

Building schema and tables from XML?

I'm looking for the "proper" way to do this:

I've been given a web site that generates some XML. I need to load the XML from the site (that's trivial, of course) and then populate a database table with the information contained in the XML.

For example, the XML looks something like this:

<OutputData>
<Response>
<Result code="0">Operation Successful</Result>
<Agents>
<Agent code="452">Bill</Agent>
<Agent code="999">Fred</Agent>
</Agents>
<Stats>
<UpSince>3993848</UpSince>
<LastHit>88288</LastHit>
</Stats>
</Response>
</OutputData>

I could make an XSD by hand, but that would be very cumbersome (the XML is actually quite huge - this is just an example of part of it). I could create the database tables and generate the XSD from them, but the tables wouldn't reflect the schema of the XML perfectly, I suspect.

The goal, of course, being the automation of reading the XML into the database.

So the question - given some XML that someone just throws at you, what's the proper way to create an XSD to read it in and eventually get it into tables in SQL 2005?

Many thanks in advance for some adult supervision :-)There are XML Schema inference tools available (such as XSD.exe in VS), that can generate an initial XSD schema for you. The SQLXML Bulkload object also has a very simple relational schema generation capability to generate tables and columns based on an annotated XSD.

What I would like to better understand though is:

1. Do you have an existing relational schema?
2. Do you know the shape of your XML data in such a way that you could write XPath expressions to propagate the values from the instance document?

If the answers to these questions are "yes", I would recommend to look into using the nodes() method to shred the XML into the relational form and expose this through a stored proc.

Best regards
Michael

Building schema and tables from XML?

I'm looking for the "proper" way to do this:

I've been given a web site that generates some XML. I need to load the XML from the site (that's trivial, of course) and then populate a database table with the information contained in the XML.

For example, the XML looks something like this:

<OutputData>
<Response>
<Result code="0">Operation Successful</Result>
<Agents>
<Agent code="452">Bill</Agent>
<Agent code="999">Fred</Agent>
</Agents>
<Stats>
<UpSince>3993848</UpSince>
<LastHit>88288</LastHit>
</Stats>
</Response>
</OutputData>

I could make an XSD by hand, but that would be very cumbersome (the XML is actually quite huge - this is just an example of part of it). I could create the database tables and generate the XSD from them, but the tables wouldn't reflect the schema of the XML perfectly, I suspect.

The goal, of course, being the automation of reading the XML into the database.

So the question - given some XML that someone just throws at you, what's the proper way to create an XSD to read it in and eventually get it into tables in SQL 2005?

Many thanks in advance for some adult supervision :-)There are XML Schema inference tools available (such as XSD.exe in VS), that can generate an initial XSD schema for you. The SQLXML Bulkload object also has a very simple relational schema generation capability to generate tables and columns based on an annotated XSD.

What I would like to better understand though is:

1. Do you have an existing relational schema?
2. Do you know the shape of your XML data in such a way that you could write XPath expressions to propagate the values from the instance document?

If the answers to these questions are "yes", I would recommend to look into using the nodes() method to shred the XML into the relational form and expose this through a stored proc.

Best regards
Michael