capture SP output into a single Text field
-
I'm trying to revise a few old admin jobs that are scattered across various servers. The old jobs would call osql to run sql scripts and capture the output in files. It doesn't look like any of the outputs are too large to fit in a text field, and was thinking that since this is a DBA project, why not keep this output in .... a database!? So, that brings me back to my question.Is there a way to capture the all columns (and rows?) of a sproc (or sql) into a single Text field? basically, like setting QA option to output to Text, and then saving that output in a table with a text field.
-
Probably... but I wouldn't... have you ever worked with a TEXT datatype before? It's a pain in the sitting-muscles. I'd likely setup some table with the results of the different sprocs I made for monitoring, etc, and join them like anything else.
If you are using System Sprocs a lot, you can store their results by doing the following...
Create a table with the same columns as the output of the sproc.
INSERT INTO thattable (columnlist...)
EXEC somesproc
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Although I could imagine a procedure that builds dynamic SQL to execute a query, output its results to a temp table, then iterate over the columns of the result to generate a SELECT statement which generates a string consisting of all columns of the result table concatenated together, where each column is CAST to VARCHAR of the appropriate length........ this procedure sounds too complicated to be worth it. It's easier to just read the data from ODBC in (say) Perl. (The quoting mechanism below works in Cygwin; using ActiveState Perl, you would have to save the below as a file and run the file using perl scriptFileName.) Note, remove the space before each string ODBC below (DBD and dbi); it was causing a smiley.
perl <<"EOS"
use DBI;use DBD:: ODBC;
$dbh=DBI->connect('dbi: ODBC:driver={SQL Server};Server=.;database=pubs');
$sth=$dbh->prepare('SELECT TOP 1 * FROM pubs..employee');
$sth->execute();
@columns=@{$sth->{NAME}};
print join("\t",@columns),"\n";
while (my @row = $sth->fetchrow_array) {
print join("\t",@row),"\n";
}
EOS
The output looks like this (assuming localhost has a running SQL database, allows integrated security, and the current user has login permissions to the database server and to pubs):
emp_id fname minit lname job_id job_lvl pub_id hire_date
PMA42628M Paolo M Accorti 13 35 0877 1992-08-27 00:00:00.000
You can easily store a list of queries in a Perl script this way, as I have for a feed processing script. You could concatenate it all to a string (instead of print) and then do an INSERT into a table when done.
The same could of course be done in Visual Basic (or C#, etc), but the code would be longer.
-
I guess the real question to Josh would be... why is this needed in a single string?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
thanks guys. I guess I'll have to brush up on my perl.
My thought was that if I could come up with a "simple" way to capture the varying outputs to a single text field, then I could essentially duplicate the current file storage method that's in place with a bit of improvement in the searching ability. I know it's not a perfect solution but it seemed like the shortest route to a moderate gain. I was not planning to invest too much time in this effort. -
... and then it became a challenge.
-
I thought of converting to and from XML, but wasn't sure that was easy to do. Note that xml element names are case-sensitive (at first I was creating the document with <root> but trying to query it with /ROOT).
Try this:
use pubs
go
declare @docHandle INT
DECLARE @xml NVARCHAR(MAX) ;
select @xml = N'<root>' + cast ( (Select *
from authors
FOR XML AUTO
) AS NVarchar(MAX)) + N'</root>';
exec sp_xml_preparedocument @docHandle OUTPUT, @xml
select * from openxml(@dochandle,N'/root/authors')
WITH authors
exec sp_xml_removedocument @docHandle
The biggest problem I see with it is the WITH clause, which requires either a table name or a schema definition, eg (from books online):
SELECT *
FROM OPENXML (@idoc, '/ROOT/Customer',1)
WITH (CustomerID varchar(10),
ContactName varchar(20))
If your queries are ad-hoc SQL, you'd have to somehow determine the schema (select into to a temp table and read syscolumns?) and save that along with the XML document, then retrieve it and build the openxml query as a string and exec it as dynamic SQL.
See SQL Server 2005 Books Online:
Using FOR XML and OPENXML to Publish and Process XML Data
(if local help is installed, the url is
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/ea58e90c-0a2a-4c8f-a7d6-d61618c2d11c.htm
-
wow.
Thanks for the interesting suggestion! As much fun as that sounds, I think I'm just going to stick with files for now. It's time to move on to the next project.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply