October 3, 2007 at 7:08 pm
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
October 3, 2007 at 8:32 pm
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?
October 3, 2007 at 8:44 pm
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
October 4, 2007 at 7:45 am
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?
October 4, 2007 at 10:09 am
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
October 4, 2007 at 4:03 pm
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?
October 5, 2007 at 6:09 am
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.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply