Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Automate SQL query Expand / Collapse
Author
Message
Posted Wednesday, October 3, 2007 7:08 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 4, 2007 12:50 PM
Points: 3, Visits: 13
Hi all,

I have an SQL2000 server that I want to query every evening for reporting. I don't want to create a DTS on the SQL Server itself as I only want to access the server DB from a remote workstation.

My need is the following : From a PC workstation (WinXP), automate a task that will start at 10:00pm every night, that will do an SQL Query to the SQL2000 server. The result need to be saved locally in a certain directory in a .csv format so the PC workstation user will be able to access those reports locally whenever he wants.

I'm looking for a free SQL client that will be able to achieve that. Query Analyzer or anything else.

What would you suggest to use ?

Many thanks in advance,

Rob
Post #406566
Posted Wednesday, October 3, 2007 8:32 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:27 AM
Points: 7,139, Visits: 15,189
You can install the client tools for SQL on any machine, free. That gives you access to any of the tools you wish to use (query analyzer, DTSrun, osql, BCP, etc...)

Sounds like you want something automated through the BCP utility - take a look through BOL to read up.


----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Post #406578
Posted Wednesday, October 3, 2007 8:44 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 4, 2007 12:50 PM
Points: 3, Visits: 13
Thanks for your quick reply.

First I want apologize as I'm a newbie around these tools.

I like the idea working with the SQL2000 tools installed on the PC workstation.

I've looked at the BCP utility but it looks like I can basically only copy tables and not run SQL query into it. Actually if I can run a stored procedure that is sitting on the SQL2000 server, that would be the best. If not I will execute the SQL query everytime from the PC workstation.

Also...what is BOL ?

Many thanks in advance,

Rob
Post #406582
Posted Thursday, October 4, 2007 7:45 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:27 AM
Points: 7,139, Visits: 15,189
You can run just about anything you wish from BCP. A stored procedure that returns result is perfectly appropriate, or a view, or a function.

By the way - BOL = Books On Line (MS' online documentation for SQL server) - sounds like you're already there. Sorry - too many acronyms.


----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Post #406782
Posted Thursday, October 4, 2007 10:09 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 4, 2007 12:50 PM
Points: 3, Visits: 13
Thanks again Matt...

It is working great now ! I was struggling with username/password and alias.

I have two additionnal question is you have time :

1) By any chance, do you know what is the best way to "not show" the password on the command line (-P) ? Currently I'm testing it with the SQL username/password but I'm wondering if I use it with Windows authentication, would be the only way to achieve it ?

2) I'm using the "queryout" option and I'm wondering if there is a way to use a dynamic file name like the current date instead of having the same name. Because this query will be executed every night and I would like to change the name every night so the user can go back and pick the right day he needs.

Many thanks in advance once again...your help is greatly appreciated.

Best regards,

Rob

Post #406933
Posted Thursday, October 4, 2007 4:03 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:27 AM
Points: 7,139, Visits: 15,189
No reason you couldn't. Remember - bcp runs from a command prompt, so you can "parameterize" that any way you wish. If you were to enclose that in a CMD file (which we call runme.cmd), using very rough syntax like this

set dirpath=c:\myoutputdirectory\myfilename
Bcp [various BCP options] %dirpath%%1%.csv


You could run runme 20071004, and it would create c:\myoutputdirectory\myfilename20071004.csv from the output of the BCP.


----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Post #407137
Posted Friday, October 5, 2007 6:09 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, July 30, 2010 11:27 AM
Points: 435, Visits: 1,403
That sounds like a task for Excel (or Access). Have you tried an external query from Excel (using either the query wizard or MS Query, which is a part of Excel). If the number of rows returned exceeds Excel's limits, try creating a Pivot Table from an external source (your SQL Server database). In either case, the data can be refreshed whenever the user wants, with no intermediate batch steps.) (If you really need cvs, Excel can save as that format too.)
From Access, you can simply link to the SQL Server view and pretend the data is local.
Post #407298
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse