Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Automate SQL query


Automate SQL query

Author
Message
hugo321hugo
hugo321hugo
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
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
Matt Miller (#4)
Matt Miller (#4)
SSCertifiable
SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

Group: General Forum Members
Points: 7641 Visits: 18082
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?
hugo321hugo
hugo321hugo
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
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
Matt Miller (#4)
Matt Miller (#4)
SSCertifiable
SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

Group: General Forum Members
Points: 7641 Visits: 18082
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?
hugo321hugo
hugo321hugo
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
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
Matt Miller (#4)
Matt Miller (#4)
SSCertifiable
SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

Group: General Forum Members
Points: 7641 Visits: 18082
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?
Jim Russell-390299
Jim Russell-390299
SSC-Addicted
SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)

Group: General Forum Members
Points: 449 Visits: 1403
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search