Querying a number of tables and writing the results to a system file 2

[timfost]

Folks,

I am relatively new to sql server (coming from more of an oracle background) and was wondering if I could get some advice on the best way to proceed with solving the following problem. I need to create a file for a trading partner using information from our database. The file has multiple record layouts and the data for the file will come from multiple tables. For example we will have to do something like

if supplier = a then
for each order
create header record
write to file
for each line on order
create detail line
write to file
sum details
create summary record for order
write to file

now in an oracle world I would create a stored procedure with a number of cursors and use the UTL_FILE package to create the output file and run it using a batch script and sqlplus.

my proposed solution is to write a stored procedure and execute it using isql or osql (are these basically the same) in a dos bath script that would pass in the filename and directory. I beleive there is a system stored procedure that will write to a file. I have read a number of posts and begun looking at some sql server docs and see that a lot of people recommend staying away from cursors in sql server. Is there a reason for this? Also I see that you can put the results in a table and use bcp to create the file but this seems to be more of an adhoc solution rather than one that would be scheduled to run each day. Also I see a DTS package with sql server. Would this be a good way to create a file like I have described. And one last question (for now anyway). I think the following command will cause osql to execute a stored procedure.

OSQL -u <user> -P <password> -d <Database> -i <file.sql> -o <log file>

where file.sql would contain the statement EXEC @result = MyStoredProcedure(input parameters)

is this correct.

Thanks for any help.

 

[NoCoolHandle]

Yes... that should work..
you can also use BCP to do the same thing..
another route (just because there are a few options in sql)
is to use SQL and xpcmdshell to do the oracle cursor thing, but I think BCP (executed via xp_cmshell) is one of the best options.. Any query and a reasonable amount of output control.


HTH

Rob

 

[timfost]

nocoolhandle - thanks for the response. are you aware of why people say stay away from cursors in sql server? it bothers me a little because with the logic I am going to have to follow I can't really just write one big query to get the data and cursors are really the only way I see to implement the logic.

thanks

 

[SQLDenis]

Stay away from cursors is not applicable in your case
What people mean is that you can write 99.99% of the solutions with a curors as a set base statement, this will be many times faster and will also eat up less CPU and memory of the server (making the server faster as a whole)

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[timfost]

Thanks for the responses again. One more question. Does sql server have a utility or system stored procedure that can be used to, create, open, and write to a text file. I have seen that the sp_oamethod can do this but it seems to be a performance hit but I think I am going to have to do something like this as the records will have different layouts. Is there a better method?

 

[NoCoolHandle]

yes
xp_oacreate

allows you to create a COM object that you can then use to open and write to textfiles...


Do a google on xp_oacreate and filesystemobject
and you should find the code you need.


HTH

Rob