automating data export from sql server 2005
Dear All, We have around 40 databases on a server and we need to export data from them as per a complex query to a .csv file. Since this is a routine job I need to automate it. Presently I run the Export data wizard on each database, copy paste the query and it creates a single .csv file per DB. In order to automate I think of the following scenarios: scenario1 Start the wizard from a stored proc(user or system).The info needed to run the wizard will be stored in a text file which will be read by the proc and provided to wizard. scenario2 Have a stored proc which will do everything like below: for each database_name(read from a txt file) do Use database_name(read from a txt file) Go @sql = " select gdf.etert from rt ......."(complex query) Execute @sql; --Create a database_name.csv file and export the data retrieved with column names. Go Which of the above scenario is best for the data transfer? Depending on which is the best pls provide the code. If there is any other better method of doing it pls provide details. I am looking at a DB solution without creating any appln(exe) for it. The experts need to provide a working solution. Thanks for ur efforts. Today's knowledge is too little for tomorrow's job
September 18th, 2011 6:46am

I think much better would be having SSIS package includes for each loop control and having issue one main stored procedure Is that query identical for all databases or different? You can have one stored procedure that accepts @dbid as a parameter and add IF ..ELSE logic to run the query --Example SELECT @sql='SELECT SUM(size)*1.0/128 AS [size in MB] FROM ['+@dbid+'].sys.database_files ) WHERE dbname=@dbid ' EXEC sp_executesql @sql, N'@dbid nvarchar(4000)',@dbid Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
Free Windows Admin Tool Kit Click here and download it now
September 18th, 2011 9:52am

Hi Uri, Thanks for your reply. Some of the things are not clear due to lack of knowledge. 1) How I can use the for each loop control in package to loop thru' 40 databases? 2) The query is identical but does not include the DB name.The DB names can be kept in an array type structure in SQL.Is that feasible? 3) " EXEC sp_executesql @sql, N'@dbid nvarchar(4000)',@dbid" I did not get the syntax of the above system proc.Can u pls explain the syntax. Thanks.Today's knowledge is too little for tomorrow's job
September 18th, 2011 1:14pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics