How to store results for a query to a table?

Question

How to store results for a query to a table?

Chinesinho

Old Hand

Points: 374
More actions
February 13, 2009 at 11:54 am

#128738

So here's the problem I'm facing - I'm running an extended stored procedure from a third party vendor, and I want to be able to capture the text (by inserting it into a table) that it generates in the "Messages" window in SQL Server Management Studio. Does anyone know how to do that via T-SQL?
Here's what I'm trying to run:
EXEC master.dbo.xp_ss_backup
@database = @DBName,
@filename = @PhyName1,
@desc = @DBName,
@backupname = @DBName,
@backuptype = @backuptype,
@compressionlevel = '1',
@overwrite = 1,
@windowsusername = 'domain\login',
@encryptedwindowspassword = 'password'
Here's what is generated by the extended stored procedure from above, and what I'm trying to capture:
xp_ss_backup did not complete. An error(0,1) was encountered. Please check the parameters and their values. [v4.9.501.6236]
SQLsafeCmd output:
------------------
SQLsafe Enterprise-scale Backup and Recovery CLI Version 4.9.501.6236
Copyright (c) 2004-2008 BBS Technologies, Inc., All Rights Reserved.
Connecting to Backup Agent on SERVER1...success. (4.9.501.6236)
Connecting to SQL Server SERVER1...success. (9.0.3282.0 SP2-CU9)
BACKUP Scratch...FAILED.
Server instance: SERVER1, Database: Scratch
Failed checking path \\bad-path\SERVER1\ because of: The network path was not found.
--- End of SQLsafeCmd output ---
(0 row(s) affected)
I know it seems like a simple task but I've been going crazy over this for the past few days. Any help is greatly appreciated.
Thanks in advance =)

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

bvaljalo-1000038 Right there with Babe Points: 760 More actions · Answer 1

I did a little googling, and it kinda looks like you have to grab this info outside of SQL Server, either through the cmdshell command (generally inadvisable to enable this feature on a production server), or through an OLEDB db connection. It appears this info gets passed back through OLEDB and is exposed in something along lines of a messages/errors collection of the connection object.

Here's a couple of the articles I perused:

http://blogs.msdn.com/mattm/archive/2007/09/13/capture-print-messages-from-a-stored-procedure.aspx

http://www.justskins.com/forums/capturing-stored-proc-print-output-from-asp-67568.html

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 2

Chinesinho (2/13/2009)
So here's the problem I'm facing - I'm running an extended stored procedure from a third party vendor, and I want to be able to capture the text (by inserting it into a table) that it generates in the "Messages" window in SQL Server Management Studio. Does anyone know how to do that via T-SQL?
Here's what I'm trying to run:
EXEC master.dbo.xp_ss_backup
@database = @DBName,
@filename = @PhyName1,
@desc = @DBName,
@backupname = @DBName,
@backuptype = @backuptype,
@compressionlevel = '1',
@overwrite = 1,
@windowsusername = 'domain\login',
@encryptedwindowspassword = 'password'
Here's what is generated by the extended stored procedure from above, and what I'm trying to capture:
xp_ss_backup did not complete. An error(0,1) was encountered. Please check the parameters and their values. [v4.9.501.6236]
SQLsafeCmd output:
------------------
SQLsafe Enterprise-scale Backup and Recovery CLI Version 4.9.501.6236
Copyright (c) 2004-2008 BBS Technologies, Inc., All Rights Reserved.
Connecting to Backup Agent on SERVER1...success. (4.9.501.6236)
Connecting to SQL Server SERVER1...success. (9.0.3282.0 SP2-CU9)
BACKUP Scratch...FAILED.
Server instance: SERVER1, Database: Scratch
Failed checking path \\bad-path\SERVER1\ because of: The network path was not found.
--- End of SQLsafeCmd output ---
(0 row(s) affected)
I know it seems like a simple task but I've been going crazy over this for the past few days. Any help is greatly appreciated.
Thanks in advance =)

It's easy... I just need to know... does the target table already exist or not?

--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)

Chinesinho Old Hand Points: 374 More actions · Answer 3

bvaljalo,

Thanks for the links. Unfortunately, I already read those articles but I appreciate your help.

Jeff,

The target table does already exist. I have tried running insert but that didn't work. So what am I missing? Thanks!

Bruce W Cassidy SSChampion Points: 12668 More actions · Answer 4

[font="Verdana"]You can log output from a job using SQL Server Agent to a file. Or if you are running it from the command line using SQLCMD, redirect the output to a file. Then you can read that file into a SQL Server table (bulk insert or OPENROWSET()) and process from there.[/font]

Chinesinho Old Hand Points: 374 More actions · Answer 5

Hi Bruce,

I know that's an alternative but I was wondering if there is a way to skip that step & just insert the output into a table directly...even when I'm running it from Management Studio.

Thanks,

Johnny

Bruce W Cassidy SSChampion Points: 12668 More actions · Answer 6

[font="Verdana"]As I'm sure you have realised, the answer is "no". 🙁 You can capture the result sets, but not the console output.[/font]

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 7

Chinesinho (2/17/2009)
Jeff,
The target table does already exist. I have tried running insert but that didn't work. So what am I missing? Thanks!

Oh, my bad and my appologies... I didn't read it all. Bruce is correct... it's easy to capture a result set from a stored proc, but you have to use one of the methods he gave to capture any error output from it.

--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)

gmason14 Newbie Points: 3 More actions · Answer 8

This is not the most elegant solution, but it does get the data from the extended stored procedure in a format that can used in a table .

create Table #MsgTable(rowid int identity(1,1) not null,Textmsgvarchar(max) null)

declare @rc int,

@cmd varchar(8000),

@error_Message nvarchar(2000),

@dbname sysname,

@backup_file_name_var1 sysname

select @dbname = 'TEST',

@backup_file_name_var1 = '\\Server\Share\FileName'

select @cmd = 'osql -S ' + @@SERVERNAME + ' -E -Q "set nocount on; declare @rc int; exec @rc = master.dbo.[xp_ss_verify] @database = ''' + @dbname +

''',@filename=''' + @backup_file_name_var1 +

'''; select @rc "'

insert into #MsgTable (Textmsg)

exec master.dbo.xp_cmdshell @cmd

delete from #MsgTable where Textmsg is null

select Top(1) @rc = Textmsg from #MsgTable order by rowid desc

if @rc <> 0

begin

select@error_Message = ''

select@error_Message = @error_Message + IsNull(Textmsg, ' ') from#MsgTable

select @error_message

end