You can run the Util_CSV_to_Excel stored procedure from Query Analyzer, call it from another stored procedure (as I do in a moment), or run it from an external application such as Access. The stored procedure has five input parameters: @UserName, @TargetName, @SaveAsName, @TargetTable, and @JobName. The @UserName parameter is an arbitrary choice; you could generate a random string to take its place. But regardless, you need to supply a unique value from the calling entity to give the resulting temporary SQL Server Agent job a unique name. @TargetName is the name of the target .csv file, @SaveAsName is the name of the .xls file that the stored procedure will output, @TargetTable is the SQL Server table whose schema the stored procedure will use for the header row, and @JobName is an arbitrary non-unique root name for the job.
Once you declare and set the internal variables, the Util_CSV_to_Excel stored procedure checks the information_schema.columns count of the target table to find out how many columns the stored procedure will be adding to the header row that the code uses in the embedded VBScript routine's FOR ...NEXT loop. The VBScript code is embedded in a text variable defined as nvarchar(3200), the maximum size available for a SQL Server Agent JobStep object script. If you need more space than that, you have to create several job steps with successive layers of code. (For information about how to create a job step, see the Microsoft article "JobStep Object" at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqldmo/dmoref_ob_j_3gdw.asp.) From here, the task is familiar to any VBA programmer who has had to make one Office application work with another. The component-object models for all Office applications are available in their respective VBA Help systems, and Microsoft provides further information on its Microsoft Office Developer site at http://msdn.microsoft.com/library/default .asp?ur=/library/en-us/dnanchor/html/odc_ancofficedev.asp.
After Listing 1's code uses ADODB to upload the schema into a Recordset object, it iterates the column names into the Excel worksheet cell by cell and saves the worksheet as an .xls file. The VBScript component of the stored procedure, with the T-SQL variables replaced by literal text, would work if you pasted it into a SQL Server Agent job and ran it. All that remains is to create and run the job, which you can do by using the prefabricated stored procedures in the msdb database. Information about how to use these procedures is available in Query Analyzer's T-SQL Help. The one thing your program needs to do during the job creation is set the scripting language to VBScript. This option is in the Enterprise Manager SQL Server Agent scripting tool but, for some reason, got left out of the canned stored procedure. SQL Server Agent job steps are in plain sight—in the msdb sysjobsteps system table (an obvious name, that). All you have to do is update the scripting language name field called database_name for your job ID, which SQL Server output when you created the job with the sp_add_job procedure. Be careful—I have only one job step in this example job. If you do something more complicated, you'll need to pick the right step.
You need to note one other thing about this stored procedure: When you set up the ADODB connection, "Initial Catalog" is the database name that appears in the connection from which you'll load the Recordset. Initial Catalog is hard-coded in Listing 1 because the Util_CSV_to_Excel stored procedure is looking at the server and database on which it resides. Alternatively, you might want to further generalize the code by feeding the catalog name in as a parameter or even storing it in a SQL Server table for later lookup.
Now that you have a stored procedure that takes a .csv file and makes it into an Excel file with a header row derived from the table schema, it's time to insert the Excel file's data into the SQL Server table where it belongs. Before you can do that, you have to know the answers to two questions. One, of course, relates to the common problem with all deployed and unsupervised code: What happens if the stored procedure starts and never stops—running wild, as it were? The other is, Did the stored procedure do its job? Finding the answers to these questions is complicated by the fact that the scripts launched from SQL Server Agent job steps run asynchronously; they come from the job, and your code then moves on to whatever step comes next.
Listing 2 shows a simple way of learning the answers to both these questions. You supply the stored procedure in Listing 2 with the same @JobName you supplied to the stored procedure in Listing 1. Then, you give the code a numerical countdown (a primitive loop) that tells it whether to cancel the job (as callout A in Listing 2 shows) and lets the code know what you want it to do once the countdown has elapsed. For example, in Listing 2, I've specified that the return value 1 means the job has failed. If the code in Listing 2 gets a return value of 1 from the SELECT statement at callout B, it stops the job. You can accomplish this kind of job verification in several ways. But the simplest way is to use a loop that checks on the job however many times you told it to in the last_run_outcome field of your job step in the msdb sysjobsteps table. Listing 2's stored procedure tells you whether the job has stopped and, if so, whether it failed or succeeded. Once the timeout value you specify is exceeded, you have the option of canceling the job by using the msdb sp_stop_job stored procedure. One reason for letting the job run is so that you can investigate DBA-style to see whether a problem exists or whether you simply need to increase the timeout value.
Prev. page
1
[2]
3
next page 
