April 28, 2006 at 10:16 am
I need to run a select sql statement against 1 table and output to a csv file on a daily basis.
In the past I have used the program that uses the SQL Server database (like PeopleSoft). For this task, however, I need to run it directly against the database. That is virgin territory for me, and my company has no DBA on staff.
Do I need to create a User Defined Function for this or is there a better method to accomplish this task? The documentation I have seems to point to this as the approach to take, but I need a guru to validate my understanding (!).
If this is the method for me to use, is there a resource other than the delivered documentation that covers the pieces needed to Create the User Defined Function?
CREATE FUNCTION [OWNER].[FUNCTION NAME] (PARAMETER LIST)
RETURNS (return_type_spec) AS
BEGIN
(FUNCTION BODY)
END
I can't find an explanation for PARAMETER LIST, return_type_spec. I think FUNCTION BODY is the T-SQL statement, but where do I specify that I want a csv file for the output?
Apologies for my thick-headedness, and thanks in advance.
Joe
April 28, 2006 at 10:29 am
Take a look at osql.
Basically, you can write a query that will send the output directly to a text file with a few parameters thrown in for formatting.
Take a look here
http://www.di-mgt.com.au/osqlUtility.htm
To get your separator see:
-s col_separator
To schedule, set up a sql job with the t/sql embedded in it and schedule it.
April 28, 2006 at 10:38 am
You might also look into creating a DTS package and scheduling it. I've found the GUI can be much easier for those who aren't very adept with SQL.
You can just right-click the database, select All Tasks -> Export Data and create the package. At the end, save it so you can run it later and then find it under Local Packages, right-click and select Schedule Package.
April 28, 2006 at 1:42 pm
Thanks for the fast responses!
I'll look at both suggestions, but DTS sounds like the better fit for me - I'm a GUI guy.
Thanks,
Joe
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply