January 6, 2009 at 12:46 pm
Problem #1
i've got a lot of maintenance to do on a SQL db. below are the scripts that i'm running in query analyzer. right now, i'm executing each script, identifying the output file in the dialog box that appears, and then waiting until the script completes... is there a way to identify the output file within the script so I just have to execute the script?
select *
from doc2 (nolock)
where external_identification in (
'huge list of entries - 20000 in total'
)
Problem #2
i have 26 scripts like the one above, each with a unique list of entries for the external_identification field. Can i create a master script that will call each script sequentially? nothing fancy with loops or anything like that. I'm willing to type in all 26 script names..
Thanks!
Rob
January 6, 2009 at 1:06 pm
You can use SSIS to automate output for files, including automatically naming the files. That's probably the best way to do what you want.
SSIS can run each script and create an output file for the data from each one. If the file name is different each time (has the current date in it or something like that), you have to set it with a variable, but that's easy enough to do. If it's the same each time you run the script, and you overwrite the existing file, that's even easier.
Look into that. It will be, long-term, the best solution.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 6, 2009 at 1:15 pm
I would look at DTS and write the output of the script into a temp table.
Use sql tasks within the DTS to run each script and also use workflow properties to control execution of each step.
This could be way out of date as I'm still using SQLServer2000 !
The structure would be something like
drop all defined temp tables
execute 1st script as a select into the temp table
on success
execute 2nd script etc etc
January 6, 2009 at 1:36 pm
thanks to both of you for the fast replies, and forgive the newbie reply to both your posts, but....
what is SSIS and DTS???
really - i don't need to run these scripts on a schedule. manually kicking off the script will be more than adequate. I was hoping there was a script line like 'set outputfile = output01.txt' that I could add to each of the scripts I already have written.
I'm hesitant to write the output into another table (not that I have the slightest clue how to do that) because then I still have to export the table to a text file for the CYA reports that need to go to the owners of the data...
i'm not proud - spoon feed me the specific lines, or spell out where exactly i need to look for which programs and i'll be happy to go from there.... 🙂
Thanks
Rob
January 6, 2009 at 2:55 pm
No problem on the lack of expertise. We all have to start that way, I just beat you to it by a few years!
SSIS is "SQL Server Integration Services", which is a service for building programs (very easily) that will move data around between formats. It also does a bunch of other stuff, but that's the main use. In other words, it's built to do things like output data from a database into text files, or import data into a database from text files.
DTS is "Data Transformation Services", and is basically the SQL 2000 version of SSIS. SSIS is the "new improved DTS, with fresh lemon scent!", and all that. Lots of new features, and I find it easier to work with, but for something like this, they both do the same thing.
Seriously, the easiest way to accomplish what you want will be to build an SSIS/DTS package (depends on which server version you're using - I'm assuming 2005 because of the forum you posted in, which would mean SSIS).
The easiest way to figure out how to do that is to go to SQL Server 2005 on your Start menu (All Programs), open up SQL Server Business Intelligence Development Studio, select New Project, pick SSIS Package, name it something appropriate, and just start poking around.
Data Flow Object (I think that's what it's called) is the one you'll want. You'll need to drag one onto the project, then double-click it. Pick OLE DB for your source, follow the directions on the screen for picking a server connection and entering your query script. Pick Flat File for your destination, follow the directions for that.
See if you can figure it out from there. Check the documentation if you get stuck, or ask on here, either on this thread or create a new on in the SSIS forum.
Once you get the first one built (which might take a little trial and error), building the rest of the scripts into it will be dead easy.
Then, whenever you want to output the files, you can just run the whole package, and it will do all the rest of the work for you.
That's pretty much how I taught myself how to use SSIS, and I can get it to jump through some pretty interesting hoops these days.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply