Finally, you're ready to insert the Excel file you created into the SQL Server table you created for it. Listing 3's stored procedure, Util_Excel_to_Table, accomplishes this task by attaching the Excel table as a linked server, using SQL Server's convenient built-in stored procedures. Listing 3's code then uses the simple INSERT statement at callout A to move the data from the file to the table. Notice that the INSERT statement treats the Excel file as if it were part of a distributed transaction. The statement uses a made-up (and meaningless) server name separated from the table name (the name you earlier gave your worksheet) by three dots because the other two pieces of the four-part name aren't required.
In effect, this stored procedure calls the procedure in Listing 1 to create the Excel file, then calls Listing 2 to wait until the job is finished. When the job does finish, Listing 3's stored procedure moves the data to its destination. When the task is complete, the stored procedure drops the server link, then uses sp_cmdshell to run the MS-DOS Del command with a mask you supplied as an input parameter.
Because Listing 3 is the starting point for program execution, the input parameters require some explanation. @UserName is a unique identifier; the code appends it to the job-step name to distinguish the job step when multiple sources in the network are calling the same routine. The parameter you input for @UserName doesn't have to be an actual username, merely something that won't be duplicated between calling entities. @JobName is the name of the job the code will create. @SourceFile is the .csv file that contains the data you're importing. @Target-File is the .xls file the code is creating. @Target-Table is the SQL Server table that supplies the header-row data and into which you're importing the data. @KillMask is the list of temporary or source files the code will delete when the job is done. If you want to retain the files permanently, you have to delete this parameter and the command using it (in this case, the @KillCmd variable) from the stored procedure. If you were running Listing 3 from Query Analyzer, for example, the command line might be as follows:
EXEC Util_Excel_to_Table
'wbarton', 'MyEmbeddedScript',
'InTheBeginning.cls', 'InTheMiddle.xls',
'MyNewTable', 'InThe*.*'"
I used the techniques in this article to automate a fairly simple task set, but you can use them to perform any of the million-and-one routine DBA tasks we all know and loathe. You can use these techniques to automate any task associated with the COM-compliant applications on your environment's network, including most of your network-administration tasks. You can also reverse these procedures to export SQL Server tables and views to Excel or Access, for example, and use SQL Server's built-in email capability to send your output as attachments to an automated distribution list. You're limited only by your imagination and the needs of your job.
End of Article
Prev. page
1
2
[3]
next page -->
