February 26, 2008 at 2:53 pm
Hi!
I want to store a output message from a SQL command. Like If I run the following command
EXEC master..xp_restore_verifyonly @filename='C:\Backup\MyDB.lsb'
This command will show the following message(if backup file is good)
"The backup set on file 1 is valid.".
I want to store this message in a variable or a temp table. Do you guys any idea How can I do that.(I dont want to use sqlcmd or xp_cmdshell)
Thank you.
Mohammad Aziz
February 26, 2008 at 3:09 pm
You need to do something like this:
create table #t (
id int identity(1,1),
[text] varchar(500)
)
insert into #t ([text])
EXEC master..xp_restore_verifyonly @filename='C:\Backup\MyDB.lsb'
February 26, 2008 at 3:16 pm
I tried this before that doesn't work.
Thank you.
Mohammad Aziz
February 26, 2008 at 3:43 pm
Try this:
DECLARE @vcText varchar(200)
EXEC @vcText = master..xp_restore_verifyonly @filename='C:\Backup\MyDB.lsb'
INSERT INTO {table} ({field})
VALUES (@vcText)
This will capture the return values from the SP. Now the question is if it returns a 1/0 or the text you are looking for.
Dave Novak
February 26, 2008 at 5:27 pm
I tried this before that doesn't work.
This does work and is the standard for dumping sp data into tables. What error message do you recieve. Below is sample that DOES work and uses the same prinicpal.
declare @t table(
id int identity(1,1),
logdate datetime,
processinfo varchar(25),
text varchar(1000)
)
insert into @t (logdate, processinfo, text)
exec master..xp_readerrorlog
select * from @t
February 27, 2008 at 7:31 am
Hi Adam!
your previous query doesn't show any error, that shows empty table that is not contain any data. And your modified query shows all the log information. Can you tell me where I add my command to capture the text message. I appriciate your help.
Thank you.
Mohammad Aziz
February 27, 2008 at 7:35 am
Hi Dave!
I appreciate your help. But the command return text message which I need to capture for my further analysis. This query shows 0 if the backup file is good else show nonzero value with error message. How can I avoid this error message and just keep the nonzero value to check other conditions.
Thank you.
Mohammad Aziz
February 27, 2008 at 7:37 am
I think I know what the problem is. The stored procedure is printing the message to the screen and not returning a result set. Is this accurate?
If so, you will have to alter the procedure to return data and not a message. For example, instead of
print @message
you need
select @message
or you can use the other method posted
DECLARE @vcText varchar(200)
EXEC @vcText = master..xp_restore_verifyonly @filename='C:\Backup\MyDB.lsb'
INSERT INTO {table} ({field})
VALUES (@vcText)
you will have to filter the error message by using string manipulation in the values section of the insert.
February 27, 2008 at 7:42 am
Exactly, Can you give me more details please.
Thank you.
Mohammad
February 27, 2008 at 7:46 am
Could you give us a sample of the error text returned so that we can see how to parse out the error value from the text?
February 27, 2008 at 7:47 am
If modifying the sp is plausible, then you can alter the statement where the sp prints the error message to a select.
example if the sp is printing a message then near the end, it has something like print 'some text'. You would have to change this to select 'some text'. Doing this would alter the statement to return a results set with the required data.
If you cannot alter the stored procedure, you can use the code Dave posted and perform string manipulation to grab only the piece of information that you need.
February 27, 2008 at 7:51 am
If I run th following command(for good DB):
create table #a ( aline varchar(max))
DECLARE @vcText varchar(200)
EXEC @vcText = master..xp_restore_verifyonly @filename='\\ctsqladp01\Backup$\InstA\msdb.lsb'
INSERT INTO #a (aline)
VALUES (@vcText)
if exists (select * from #a where aline='0')
BEGIN
print 'Good DB'
return
END
else
BEGIN
print ' backup is bad'
return
END
drop table #a
OUTPUT Result:
The backup set on file 1 is valid.
CPU Seconds: 0.19
(1 row(s) affected)
Good DB
If I run th following command(for Bad DB):
create table #a ( aline varchar(max))
DECLARE @vcText varchar(200)
EXEC @vcText = master..xp_restore_verifyonly @filename='\\ctsqladp01\Backup$\InstA\ms.lsb'
INSERT INTO #a (aline)
VALUES (@vcText)
if exists (select * from #a where aline='0')
BEGIN
print 'Good DB'
return
END
else
BEGIN
print ' backup is bad'
return
END
drop table #a
OUTPUT Result:
(1 row(s) affected)
backup is bad
Msg 50204, Level 19, State 1, Line 0
LiteSpeed 2005 could not initialise the backup file. The previous system message is the reason for the failure.
Please contact support for further assistance.
Thank you,
Mohammad Aziz
February 27, 2008 at 8:11 am
I am going to make some assumtions here
This is the error text returned for a bad file:
Msg 50204, Level 19, State 1, Line 0
LiteSpeed 2005 could not initialise the backup file. The previous system message is the reason for the failure.
Please contact support for further assistance.
The assumption is that you want to just grab the '50204' in the text.
This can be done simply in the insert into sql statement
INSERT INTO #a (aline)
VALUES (CASE WHEN @vcText = '0' THEN @vcText ELSE SUBSTRING(@vcText, 5, CHARINDEX(',', @vcText, 1) - 5))
If the '50204' is not what you want to grab, then please let me know part of the error text you are trying to grab.
Dave Novak
February 27, 2008 at 8:14 am
Well you are always going to recieve the print and the error messages because you are running the entire process as one batch. Try putting a Go after your insert but before your if.
Also by using a select 'Backup is bad' instead of print 'Backup is bad' it would clear the screen for you.
February 27, 2008 at 8:16 am
Right I just want to grab 50204 for testing further conditions not the error text.
Thank you.
Mohammad Aziz
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply