Skip to main content

SQLServer2000: Capture output of EXEC statements

About 2 years ago, I discovered that I could capture the results of an EXEC (@string_variable) statement. We had a server process that logged in as System Admin (SA), so it could perform a directory scan, bulk insert any files it found, and move those files to a "processed" subdirectory.

This process can be used to run any DOS command, and inserts each line of text as a record into a table. We still use this trick to log program responses to a journal table. When reading the sample code, notice that we are using two single-quote characters back-to-back to cause a single quoted argument to be sent to the xp_cmdshell extended command (really a registered DLL accessible only to SA).

Here's an example:

DECLARE @s varchar(255);
CREATE TABLE #cmdOutput(Line varchar(200)) ;
SET @s='INSERT INTO #cmdOutput EXEC master.dbo.xp_cmdshell ''dir c:\temp\*.txt /b''';
EXEC (@s) ;
SELECT * FROM #cmdOutput WHERE Line IS NOT NULL;
DROP TABLE #cmdOutput;

A word of caution: Do not use this code in front-line production. It is best suited to maintenance tasks, where the system admin password does not need to be shared with half of the office. If you need to convert this to front-line production code, write a simple application to read the directory, and either parse lines and call a stored procedure for each line, or call a bulk insert for each file (this still requires special permission, but it doesn't require SA).

Comments

Post a Comment

Popular posts from this blog

How does Rails scaffolding select HTML input tags?

Recently, a reader saw my fix for SQL Server booleans, and asked me a followup question: why does Rails display a yes/no selection instead of a checkbox? The short answer is look in {RUBY_HOME} /lib/ruby/gems/1.8 /gems/actionpack-1.10.2 /lib/action_view/helpers, but your path may vary depending on whether you are using gem, "edge rails", etc. Anyway, look in the file "active_record_helper.rb" for a method called "all_input_tags", and notice that it calls "default_input_block" if you don't supply an input_block. Now notice that "default_input_block" creates a label and calls "input(record, column.name)" which in turn calls "InstanceTag#to_tag" which finally looks at the datatype and maps boolean to a select tag. Perhaps a wiser Rails explorer can provide us with the rationale for this, but I guess we could add a MixIn for InstanceTag that redefines the to_tag() method, or just do a dirty and unmaintainable hack l...
2 comments
Read more

Updated ActiveRecord Code Generator

Today, I updated the ActiveRecord Code Generator a bit. I checked in changes to use primary and foreign key details from INFORMATION_SCHEMA. The original code used naming conventions to decide what various fields were used for - ID = Primary Key, Field_ID = Foreign Key to table Fields. If you want to use naming conventions, let me know and I can add a setting in App.Config to allow this (along with any "real" key constraints).
4 comments
Read more

Castle Active Record Code Generator

I have finally released my Code Generator to Google Code as Active-Record-Gen . What does it generate? It generates ActiveRecord classes mainly, but I have used it to generate stored procedures and sys-admin scripts as well. This code generator does not (yet) generate a full Windows application project or a Mono-Rail web site, but the generated code could be used in either. In fact, with a few tweaks, this could be used to generate NHibernate "poco" and .xbm files. If you want to know more, look at the screen shots above, or head over to Google Code and run it. In my haste to make my first EXE release before supper, I forgot to add the Template directory, which should be at the same directory level as the EXE and config files. I just (1.5 hours later) uploaded a new EXE, but 2 people have already downloaded the EXE (not the source though). As for the basic table object, it is built with the following assumptions: Table name is plural, class name is singular. Field ...
2 comments
Read more