database question.

[katster]

Okay, quick question. Maybe somebody out there on my friends page will know.

I have a bunch of queries that I'd like to run on a MS-SQL server. It's a remote server, and I'd like to run these queries in batch, and save the results to my local hard drive. The current way (which is the only way I know) I'm running queries is through an MS Visual Studio connection, which doesn't seem to do what I want it to do. (At least, there's no easy way to hand it a bunch of queries, and tell it to run each one and save the results.)

So, can anybody help?

-kat

Comments

[pussreboots.livejournal.com]

I use phpMyAdmin. It's freeware and it's a little tricky to get it configured but once you have it configured on your remote server you can use it for running complex searches and exporting the results.

[katster.livejournal.com]

Yeah, if this was mySQL, I could think of a half-dozen ways to accomplish the task. (Actually, if it were mySQL, I'd either whip up a PHP script to do it, or log in on the shell and pipe stuff around. Not hard.)

Unfortunately, this is Microsoft's SQL version, and I'm not sure the machine running the database is running a web browser. So there's got to be a way to hook in and grab the queries I need, but...it's not obvious.

-kat

[kowh.livejournal.com]

I have no idea if it's any good, it looks like it might be out of date and it may not work with the full server version, but http://www.msde.biz/msdequery/download.htm might be worth trying. It's free, it seems like it'll do what you want and it was linked from http://www.microsoft.com/sql/partners/msde.mspx which makes it somewhat more likely to not be entirely crap.

Unfortunately, it seems that the more interesting such programs for MS SQL cost money. At least with the open source databases the tools are generally also free (e.g. pgAdmin for PostgreSQL).

[johnpalmer.livejournal.com]

sqlcmd -S (servername)-i sql.txt -o output.txt

It's a lot tougher if you want individual files for each query result. (In fact, you'd probably be better creating a batch file that runs multiple SQLCMD commands, and use a > to set the filename for output.)

[johnpalmer.livejournal.com]

Oh: This is SQL2005; the equivalent for SQL2000 is osql. I haven't ever looked, but it'd be surprising if there weren't tutorials out on the interwebs for these.

[katster.livejournal.com]

This is *exactly* what I was looking for. Half of this afternoon's task just got a heck of a lot easier. Thanks. :)

-kat

[johnpalmer.livejournal.com]

Glad to help!