SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Other Forums
 SQL Server 6.5 \ SQL Server 7.0
 schedule a SQL query, and export to a file (csv)?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

errrick
Starting Member

4 Posts

Posted - 12/30/2009 :  16:22:49  Show Profile  Reply with Quote
Hi,

Is there a way I can setup a scheduled task that will run a specific SQL query (basically a SELECT statement) and automatically export the results to a file (that could be a CSV or whatever human readable/parser friendly format)

Note: SQL Server 6.5 is not reachable via remote access. All must be done in an NT4 system by connecting locally.

Thanks!
Eric

Edited by - errrick on 12/30/2009 16:27:17

tkizer
Almighty SQL Goddess

USA
37157 Posts

Posted - 12/30/2009 :  16:27:07  Show Profile  Visit tkizer's Homepage  Reply with Quote
You can create a batch file that runs bcp.exe with a column delimiter of comma. You can then use the SQL Executive (I believe that's what the SQL Agent is called in 6.5) to schedule it as a job.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 12/31/2009 :  04:49:26  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
you could also just schedule a regular NT4 job with the AT command running the batch file but the method Tara illustrates is probably easier to keep track of as it's more logically tied to sqlserver.


Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page

errrick
Starting Member

4 Posts

Posted - 01/04/2010 :  08:10:40  Show Profile  Reply with Quote
Thanks Tara / Charlie, will try that. I thought that bcp.exe didn't even exist in 6.5 :p
Go to Top of Page

errrick
Starting Member

4 Posts

Posted - 01/04/2010 :  10:24:14  Show Profile  Reply with Quote
I just found that BCP in SQL 6.5 does not allow to run custom queries, it can only export tables (it doesn't even let me select which columns I want!)

Is there any other approach?
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37157 Posts

Posted - 01/04/2010 :  10:36:29  Show Profile  Visit tkizer's Homepage  Reply with Quote
Bcp.exe does allow you to write custom queries if you specify the queryout option. For more complex queries, you'll need to create a view and then bcp out that view.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."
Go to Top of Page

errrick
Starting Member

4 Posts

Posted - 01/04/2010 :  13:47:54  Show Profile  Reply with Quote
Tara, bcp.exe in SQL 6.5 does not have the queryout option. However I figured out the View solution already.. it seems to do what I want, thanks!
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37157 Posts

Posted - 01/04/2010 :  13:52:06  Show Profile  Visit tkizer's Homepage  Reply with Quote
That's interesting. I guess my memory doesn't go as far back as I thought. I haven't worked on 6.5 in about 10 years.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.98 seconds. Powered By: Snitz Forums 2000