January 10, 2007 at 9:26 am
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/rcarlson/2801.asp
The original solution required much more than this article suggested. The response from everyone emphasized the need to always examine the alternatives. Sometimes the easiest solution is not the best and vice versa. I am very happy that this article generated an active response. The comment about what users want and what they need is an important one to remember.
Thanks for the great discussion.
February 28, 2007 at 10:12 pm
Nice solution. As a DBA I faced similar tasks many times. My usual way of completing them is by using Excel:
1. Start with 1 column
ALFKI
ANTON
AROUT
BERGS
BOLID
BONAP
BSBEV
CACTU
CONSH
DOGGY
FOLIG
FOLKO
FRANK
FRANR
FRANS
FURIB
2. Insert a column in front of it
3. Type
Placeholder1
in the first cell of this column. Drag and drop the value to populate the whole column.
4. Type
Placeholder2
in the first cell of the 3rd column. Drag and drop the value to populate the whole column.
5. "Save As" to a text file
6. Open the saved text part in notepad. do Edit/Replace replacing
Placeholder1 with
SET NOCOUNT ON SELECT C. CustomerID, C. CompanyName, C.City, O. OrderDate, O. Freight FROM Customers C INNER JOIN Orders O " ON C.CustomerID = O.CustomerID WHERE C.CustomerID = '
then replacing Placeholder2 with a single quote. Make sure all TABs and Commas are replaced with empty strings as well.
Save the output.
It is more simple than it sounds!
Regards,
Anatol Romanov
March 1, 2007 at 1:06 am
whereas my solution was to write an application in C++ which lets me use a placeholder in a sql file, and specify a tab delimited text file to provide the values.
-- test.sql
SELECT TestTable.RowID, TestTable.RowName FROM TestTable WHERE TestTable.RowID = '@1'
-- input.txt
123 321 456 654
-- console
querysql.exe -server=(local) -database=testdb -sql=test.sql -input=input.txt -output=output.txt
very handy because I don't have to create any tempoary files with all the sql statements. Output can be either to screen or to a file. Works with either a ODBC-DSN or direct to the SQL server.
Would this be of interest to anybody else?
March 1, 2007 at 1:20 am
I prefer to use a linked server using the Jet 4.0 text driver. This technique allows me to treat the incoming text file as a table, so I can join to it in the normal way. If anyone is interested, I can provide more detail on this. It's a technique I use a lot when dealing with tab-delimited files which are used either for input or output (yes, I can also INSERT new rows into my text files).
March 1, 2007 at 1:26 am
Hi,
I like the approach to solving the problem, but the VBscript seems like overkill. I've also used excel several times to solve these problems. But when they are this easy why not use good old DOS:
FOR /F %i in (custID.txt) do @echo set nocount on select %i >> custID.qry
This solved your problem in just one line of code. For more information on the FOR command, just type "help for" on the command line.
Daniel
March 1, 2007 at 1:47 am
Hi there,
this topic brings me actually to another issue. Considering the original limitations of query and report only, would it have been acceptable to write a stored procedure? That is obviously if the server is 2005 and has CLR enabled.
I had some similar topics not too long ago to implement data pushing usijng C# stored procedures rather than using polling mechanisms to update client workstations.
Using a stored procedure to output to a text file would allow you to communicate with outside processes as well as build a bridge for older or incompatible systems.
As I am new, please allow me the question: have there been any topics on CLR stored procedures?
Stefan
March 1, 2007 at 3:55 am
Interesting tecchy answers......
To my mind the problem would be better solved by asking the user what their criteria is for choosing the 17,000 customers in the first place, there must have been some reason and therefore logic behind the distinction, then write that query instead and you've saved the user the bother of ever doing the same thing again....
If I spent my life doing what users told me instead of what they need to be done I'd be an Excel expert..........
March 1, 2007 at 3:55 am
This article gave me a nice insight into vbs since I did not use it for a long time, but agree with you guys that is a bit of overkill. I would use Excel myself, but have to admit that the other 3 suggestions are very nice (Dave's querysql, Paul's Jet and Daniel's DOS).
Another solution would be MS Access (even though personally it is my last resort): import text file, link the other tables from SQL and create report.
Paul, could you please provide us with more details on your Jet 4.0 text driver solution.
Rgds
March 1, 2007 at 4:08 am
Absolutely agree Richard. But assumption here is that one would do what you suggest by default and not just jump every time end user says hop (well, unless you are junior DBA ;-)).
But once your discussion with requestor is exhausted and you end up with task like this then tech solutions discussed here is exactly what you need.
March 1, 2007 at 4:15 am
Fair enough - it could be a useful solution, I agree.
My personal preference these days is reporting services - it takes 5 minutes to set up a data source, run a query and deploy to the intranet, then dump it to Excel.
Once you've taught your users how to use autofilters you can dump anything you want in half an hour and everyone is happy.
March 1, 2007 at 7:12 am
Why not use T-SQL to generate the queries to be executed? Then cut and paste the result of this into a text file to be executed using OSQL.
For example,
SELECT DISTINCT
'SET NOCOUNT ON SELECT C. CustomerID, C. CompanyName, C.City, O. OrderDate, O. Freight FROM Customers C INNER JOIN Orders O ON C.CustomerID = O.CustomerID WHERE C.CustomerID = '''+C.CustomerId+''''
FROM Customers C
INNER JOIN
Orders O ON C.CustomerID = O.CustomerID
March 1, 2007 at 7:17 am
Since we're allowed to use QA and presumably access other DBs on the server, why not BCP in into a DB the list of customers, then use osql to run a single query, joining to the tables in the vendor's DB?
The vbs solution requires 17 000 queries, which, IMHO, is ludicrous.
P
March 1, 2007 at 7:44 am
I agree. vbs and xls are helpful, at times, but yours is the better solution.
March 1, 2007 at 7:50 am
Hello Paul,
Yes, please post more details on using the Jet 4.0 text driver for this type of query. Others have also mentioned Reporting Services and also trying to determine if there is a query that can be done straight from the database by talking to the users and finding out their logic for selecting records in the first place.
Thanks,
webrunner
P.S. I think this discussion is great. Depending on the situation, you never know which method will be available, so it is good to know as many as possible.
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
March 1, 2007 at 8:15 am
Since folks are casting around for the "more than one way to do it" - my tendency would be to use perl and sqsh. Has the advantage of working equally well from a Unix or Windows client, and works whether the database is MS SQL Server, Sybase, or both.
Which simply reflects my own background and circumstances as does the original authors.
Roger L Reid
Viewing 15 posts - 1 through 15 (of 33 total)
You must be logged in to reply to this topic. Login to reply