Building a text file using Stored Proc/DTS

Building a text file using Stored Proc/DTS

Post by Scho » Wed, 21 Jan 2004 18:06:51



I need to create an XML file that represents our company tree.  The data and
relations are all contained in a single 'employee' database.  Because it is
impossible to predict the number of branches in the tree I can't use DTS to
drop a dataset into a file and using an xml file with the "FOR XML AUTO"
won't work either for the same reson.  These are the only 2 techinques I am
familiar with to create a text file from SQL.  I am a VB programmer and if
there was a way to construct an external text file using a stored procedure
(just like stringbuilder in VB) that might work best.  I already have a
stored proc that can perform the work, I just need a command(s) that allows
the code to write it out to a text file and give the file a name.  Does
anyone have any advice on where to look to find information on this, or how
to do this?

Scott

 
 
 

Building a text file using Stored Proc/DTS

Post by Valmir Menese » Wed, 21 Jan 2004 19:41:05


Look for Anith Sen response on 'how to output a query into a xls or txt file?'
It is the best I have seen.


     I need to create an XML file that represents our company tree.  The data and
     relations are all contained in a single 'employee' database.  Because it is
     impossible to predict the number of branches in the tree I can't use DTS to
     drop a dataset into a file and using an xml file with the "FOR XML AUTO"
     won't work either for the same reson.  These are the only 2 techinques I am
     familiar with to create a text file from SQL.  I am a VB programmer and if
     there was a way to construct an external text file using a stored procedure
     (just like stringbuilder in VB) that might work best.  I already have a
     stored proc that can perform the work, I just need a command(s) that allows
     the code to write it out to a text file and give the file a name.  Does
     anyone have any advice on where to look to find information on this, or how
     to do this?

     Scott

 
 
 

Building a text file using Stored Proc/DTS

Post by Valmir Menese » Wed, 21 Jan 2004 19:21:05


Hi Schoo,
AFAIK, no T-SQL command will write your XML output to an OS file.
I  suggest the following approach:
1. Build your Tree . Look for 'TREE' under the newsgroups. Save it to a temp file.
2. Use DTS to generate your OS file
 Hope this helps


     I need to create an XML file that represents our company tree.  The data and
     relations are all contained in a single 'employee' database.  Because it is
     impossible to predict the number of branches in the tree I can't use DTS to
     drop a dataset into a file and using an xml file with the "FOR XML AUTO"
     won't work either for the same reson.  These are the only 2 techinques I am
     familiar with to create a text file from SQL.  I am a VB programmer and if
     there was a way to construct an external text file using a stored procedure
     (just like stringbuilder in VB) that might work best.  I already have a
     stored proc that can perform the work, I just need a command(s) that allows
     the code to write it out to a text file and give the file a name.  Does
     anyone have any advice on where to look to find information on this, or how
     to do this?

     Scott

 
 
 

Building a text file using Stored Proc/DTS

Post by Scho » Wed, 21 Jan 2004 21:17:58


I don't see that string in this forum.  Do you have a link to it?  Is it
somewhere else?


> Look for Anith Sen response on 'how to output a query into a xls or txt
file?'
> It is the best I have seen.


>      I need to create an XML file that represents our company tree.  The
data and
>      relations are all contained in a single 'employee' database.  Because
it is
>      impossible to predict the number of branches in the tree I can't use
DTS to
>      drop a dataset into a file and using an xml file with the "FOR XML
AUTO"
>      won't work either for the same reson.  These are the only 2
techinques I am
>      familiar with to create a text file from SQL.  I am a VB programmer
and if
>      there was a way to construct an external text file using a stored
procedure
>      (just like stringbuilder in VB) that might work best.  I already have
a
>      stored proc that can perform the work, I just need a command(s) that
allows
>      the code to write it out to a text file and give the file a name.
Does
>      anyone have any advice on where to look to find information on this,
or how
>      to do this?

>      Scott

 
 
 

Building a text file using Stored Proc/DTS

Post by Scho » Wed, 21 Jan 2004 21:50:52


1.  "Build your tree" doesn't make sense to me.  Is that a function of some
kind inside of SQL Server?
2.  As I explained, exporting through DTS would not necessarily solve the
problem.

I did find the following code on
http://www.motobit.com/tips/detpg_SQLWrFile.htm which runs without error,
but doesn't create the file.  I am hoping you or someone else can help me
work out this code if it will do what it appears to be trying to do.

==================================================================

varchar(255)) AS



--Open a file


--Write Text1




==================================================================

Scott


> Hi Schoo,
> AFAIK, no T-SQL command will write your XML output to an OS file.
> I  suggest the following approach:
> 1. Build your Tree . Look for 'TREE' under the newsgroups. Save it to a
temp file.
> 2. Use DTS to generate your OS file
>  Hope this helps


>      I need to create an XML file that represents our company tree.  The
data and
>      relations are all contained in a single 'employee' database.  Because
it is
>      impossible to predict the number of branches in the tree I can't use
DTS to
>      drop a dataset into a file and using an xml file with the "FOR XML
AUTO"
>      won't work either for the same reson.  These are the only 2
techinques I am
>      familiar with to create a text file from SQL.  I am a VB programmer
and if
>      there was a way to construct an external text file using a stored
procedure
>      (just like stringbuilder in VB) that might work best.  I already have
a
>      stored proc that can perform the work, I just need a command(s) that
allows
>      the code to write it out to a text file and give the file a name.
Does
>      anyone have any advice on where to look to find information on this,
or how
>      to do this?

>      Scott

 
 
 

Building a text file using Stored Proc/DTS

Post by Wayne Snyde » Wed, 21 Jan 2004 21:49:27


Although I do not recall reading Aniths answer I suspect it would use
OSQL -Q to pass in a query and -o to send the output to a flat file.... OSQL
is doc'd in books on line, and from the command prompt osql /? will display
its parameters.

--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), C*te, NC
www.computeredservices.com
(Please respond only to the newsgroups.)

I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org


Quote:> I need to create an XML file that represents our company tree.  The data
and
> relations are all contained in a single 'employee' database.  Because it
is
> impossible to predict the number of branches in the tree I can't use DTS
to
> drop a dataset into a file and using an xml file with the "FOR XML AUTO"
> won't work either for the same reson.  These are the only 2 techinques I
am
> familiar with to create a text file from SQL.  I am a VB programmer and if
> there was a way to construct an external text file using a stored
procedure
> (just like stringbuilder in VB) that might work best.  I already have a
> stored proc that can perform the work, I just need a command(s) that
allows
> the code to write it out to a text file and give the file a name.  Does
> anyone have any advice on where to look to find information on this, or
how
> to do this?

> Scott

 
 
 

Building a text file using Stored Proc/DTS

Post by Scho » Wed, 21 Jan 2004 23:02:35


As I mentioned, I can't just pass a query because the relations are
unpredictably deep and all of the data is in a single table.  I am
re-creating a company organizational tree in XML form.  (The purpose BTW is
to feed a treeview contorl in ASP.NET).


can't get it to work.  Because I don't get errors of any kind I wonder if
there is a rights issue in SQL Server 2000.  I created a user with admin
rights and then logged into Query Analyser to run the code, but got the same
result:  no file and no errors.  Here is the code I am running:

=================================================================









--Open a file


--Write Text1




=================================================================

I am strong in programming but weak in DBA stuff.  I see you are an MVP.  I
hope you have a good idea on this one.

Thanks,

Scott


> Although I do not recall reading Aniths answer I suspect it would use
> OSQL -Q to pass in a query and -o to send the output to a flat file....
OSQL
> is doc'd in books on line, and from the command prompt osql /? will
display
> its parameters.

> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Computer Education Services Corporation (CESC), C*te, NC
> www.computeredservices.com
> (Please respond only to the newsgroups.)

> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org



> > I need to create an XML file that represents our company tree.  The data
> and
> > relations are all contained in a single 'employee' database.  Because it
> is
> > impossible to predict the number of branches in the tree I can't use DTS
> to
> > drop a dataset into a file and using an xml file with the "FOR XML AUTO"
> > won't work either for the same reson.  These are the only 2 techinques I
> am
> > familiar with to create a text file from SQL.  I am a VB programmer and
if
> > there was a way to construct an external text file using a stored
> procedure
> > (just like stringbuilder in VB) that might work best.  I already have a
> > stored proc that can perform the work, I just need a command(s) that
> allows
> > the code to write it out to a text file and give the file a name.  Does
> > anyone have any advice on where to look to find information on this, or
> how
> > to do this?

> > Scott

 
 
 

Building a text file using Stored Proc/DTS

Post by Scho » Wed, 21 Jan 2004 23:39:11


I got the code to work!  I am now able to create a text file from SQL
Server.  Now all I need to do is delete the file from the stored procedure
or clear the file because the following code keeps appending.

> =================================================================









> --Open a file


> --Write Text1




> =================================================================

.  I am now looking to see if there is an attribute that will overwrite or
clear the contents of the text file.

Scott


- Show quoted text -

> Although I do not recall reading Aniths answer I suspect it would use
> OSQL -Q to pass in a query and -o to send the output to a flat file....
OSQL
> is doc'd in books on line, and from the command prompt osql /? will
display
> its parameters.

> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Computer Education Services Corporation (CESC), C*te, NC
> www.computeredservices.com
> (Please respond only to the newsgroups.)

> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org



> > I need to create an XML file that represents our company tree.  The data
> and
> > relations are all contained in a single 'employee' database.  Because it
> is
> > impossible to predict the number of branches in the tree I can't use DTS
> to
> > drop a dataset into a file and using an xml file with the "FOR XML AUTO"
> > won't work either for the same reson.  These are the only 2 techinques I
> am
> > familiar with to create a text file from SQL.  I am a VB programmer and
if
> > there was a way to construct an external text file using a stored
> procedure
> > (just like stringbuilder in VB) that might work best.  I already have a
> > stored proc that can perform the work, I just need a command(s) that
> allows
> > the code to write it out to a text file and give the file a name.  Does
> > anyone have any advice on where to look to find information on this, or
> how
> > to do this?

> > Scott

 
 
 

Building a text file using Stored Proc/DTS

Post by Baisong Wei[MSF » Thu, 22 Jan 2004 13:01:06


Hi Scott,

Thank you for using MSDN Newsgroup! It's my pleasure to assist you with
your issue.  

As for delete a file from SQL Server, you could run xp_cmdshell in your
code. That is:

exec xp_cmd shell 'del c:\testfile.txt'

Hope this helps! If you still have questions, please feel free to post new
message here and I am ready to help!

Best regards

Baisong Wei
Microsoft Online Support
----------------------------------------------------
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.

 
 
 

Building a text file using Stored Proc/DTS

Post by Scho » Thu, 22 Jan 2004 18:44:27


I GOT IT!

Here is the 'winning' code:

=======================================================

varchar(255)) AS

/* DELETE THE FILE */


waitfor delay '00:00:10'

/* WRITE A NEW FILE*/


--Open a file


--Write Text1



more'



GO
=======================================================

Thank you everyone for your help with this!!!  Now I can create my XML file
as deep as I need!  :)

Scott


Quote:> Hi Scott,

> Thank you for using MSDN Newsgroup! It's my pleasure to assist you with
> your issue.

> As for delete a file from SQL Server, you could run xp_cmdshell in your
> code. That is:

> exec xp_cmd shell 'del c:\testfile.txt'

> Hope this helps! If you still have questions, please feel free to post new
> message here and I am ready to help!

> Best regards

> Baisong Wei
> Microsoft Online Support
> ----------------------------------------------------
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only. Thanks.

 
 
 

Building a text file using Stored Proc/DTS

Post by Scho » Thu, 22 Jan 2004 18:45:47


Please see the last message in this string.  This indeed CAN be done in a
stored procedure.. I proved it.

Thank you for your efforts.

Scott


> Hi Schoo,
> AFAIK, no T-SQL command will write your XML output to an OS file.
> I  suggest the following approach:
> 1. Build your Tree . Look for 'TREE' under the newsgroups. Save it to a
temp file.
> 2. Use DTS to generate your OS file
>  Hope this helps


>      I need to create an XML file that represents our company tree.  The
data and
>      relations are all contained in a single 'employee' database.  Because
it is
>      impossible to predict the number of branches in the tree I can't use
DTS to
>      drop a dataset into a file and using an xml file with the "FOR XML
AUTO"
>      won't work either for the same reson.  These are the only 2
techinques I am
>      familiar with to create a text file from SQL.  I am a VB programmer
and if
>      there was a way to construct an external text file using a stored
procedure
>      (just like stringbuilder in VB) that might work best.  I already have
a
>      stored proc that can perform the work, I just need a command(s) that
allows
>      the code to write it out to a text file and give the file a name.
Does
>      anyone have any advice on where to look to find information on this,
or how
>      to do this?

>      Scott

 
 
 

1. Table Update Via Text File (DTS, Stored Proc, ActiveX, VBScript)

In your DTS package create the tasks to import the data from the text file
into a table in the SQL Server database.  Then you can create additional
Execute SQL Tasks in the same DTS package to perform the transformation
needed.   Based on the information you provided in your first email an
example could be:

In the same DTS package:
1.  Perform a Bulk Insert Task to import the data from the text file into a
table that has the SQLKeyId and UpdateKey columns.
2.  Then perform an Execute SQL Task that performs the UPDATE against the
existing table and then drops the temp table.

If all you need to do is perform SQL update statements once the data has
been imported into a temporary table then it is not necessary to use
ActiveX for these type of operations - Execute SQL Tasks are appropriate
and can all be done in the same DTS package.

For more information on using the DTS Designer and setting up packages with
different tasks, please see SQL Server Books On-Line -> DTS Designer and
the topics DTS Package Management, Saving a DTS Package and DTS Package
Component Configuration.

Sarah Henwood
Microsoft SQL Server Support

2. Nested stored procedures, user functions

3. Loading text files, DTS/Stored proc/TSQL/VB??

4. Urgent - Log Shipping Monitor show incorrect data

5. Failed to Append a Text file into another Text file by Using DTS in SQL2000

6. Relational/hierarchical data problem

7. Using a stored proc to call another stored proc

8. why backup transaction log option is greyed out in enterprise manager?

9. Creating a text file using automation without using DTS

10. Build a text file from stored procedure?

11. Stored Proc for importing a text file in SQL DB

12. Text File Import with Stored Proc

13. Unable to create DTS package that executes a stored proc that uses temp tables