November 29, 2010 at 11:43 pm
Hi All,
Please help.Is there a way to fetch a server error to a variable.
For example.
I will create 1 SP by Name Sample1 in DB 1 Using XP_CMDSHELL.
Instead of altering it ,i will again try to create the same script again and i am persisting the error message in a output file.
Server returns the below error in output.txt file.
Msg 2714, Level 16, State 3, Server aaaaa, Procedure Sample1 , Line 3
There is already an object named 'Sample1' in the database.
How can i fetch the above error to a variable?
Thanks,
Vijay Singh
November 30, 2010 at 5:25 am
Post your script which you are using
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
November 30, 2010 at 5:36 am
Hi ,
Please find the script below.
SET NOCOUNT ON
DECLARE @ServerNamesysname
DECLARE @DBNamesysname
DECLARE @SourceFoldernvarchar(500)
DECLARE @OutputFoldernvarchar(500)
DECLARE @ScriptNamenvarchar(500)
DECLARE @ScriptFullPathNamenvarchar(500)
DECLARE @Cmdnvarchar(1000)
DECLARE @Postinyint
DECLARE @Order tinyint
DECLARE @ScriptRunOrdertinyint
DECLARE @Messagenvarchar(1000)
--DECLARE @ScriptName1nvarchar(500)
DECLARE @ScriptList table
(
ScriptRunOrder tinyint NOT NULL,
ScriptName nvarchar (500)NOT NULL
)
DECLARE @ScriptListTemp table
(
ScriptName nvarchar (500)NULL
)
DECLARE @DatabaseList table
(
DatabaseID int,
DBName sysname
)
/***************************************/
-- Initialisation...
/**************************************/
SELECT @ServerName = 'My-Server' -- change it to reflect target server name...
SELECT @SourceFolder = 't:' -- change it to reflect script source location...
SELECT @OutputFolder = 't:' -- change it to reflect script output location...
SELECT @Cmd = 'master..xp_cmdshell ''DIR ' + @SourceFolder + ' /B'''
IF ((SELECT @@SERVERNAME) <> @ServerName)
BEGIN
RAISERROR ('Sorry, wrong server !!!', 17, 1)
RETURN
END
INSERT INTO @DatabaseList (DatabaseID, DBName)
SELECT database_id, name
FROM sys.databases
--WHERE name NOT IN ('master','model', 'msdb','tempdb') -- this can be changed to include one or more databases
WHERE name IN ('Elektra') -- this can be changed to include one or more databases
ORDER BY database_id ASC
INSERT INTO @ScriptListTemp
EXEC(@Cmd)
DELETE FROM @ScriptListTemp WHERE (ScriptName IS NULL OR ScriptName LIKE '%.TXT')
IF NOT EXISTS (SELECT* FROM @ScriptListTemp)
BEGIN
RAISERROR ('Sorry, no files present in the source folder !!!', 17, 1) WITH NOWAIT
RETURN
END
SELECT TOP 1 @ScriptName = ScriptName FROM @ScriptListTemp ORDER BY ScriptName ASC
SET @Order=1
WHILE EXISTS(SELECT * FROM @ScriptListTemp)
BEGIN
SELECT @Pos =CHARINDEX('.', @ScriptName)
SELECT @ScriptFullPathName = @SourceFolder + '\'+ @ScriptName
INSERT INTO @ScriptList (ScriptRunOrder, ScriptName)VALUES (@Order, @ScriptFullPathName)
DELETE FROM @ScriptListTemp WHERE ScriptName = @ScriptName
SELECT TOP 1 @ScriptName = ScriptName FROM @ScriptListTemp ORDER BY ScriptName ASC
SET @Order =@Order+1
END
--SELECT * FROM @ScriptList
/***************************************/
-- Execution of scripts...
/**************************************/
SELECT TOP 1 @DBName = DBName FROM @DatabaseList ORDER BY DatabaseID ASC
WHILE EXISTS (SELECT * FROM @DatabaseList)
BEGIN
SELECT @Message = 'Database - '+ @DBName
RAISERROR (@Message, 10,1) WITH NOWAIT
SELECT @Message = '====================================================================='
RAISERROR (@Message, 10,1) WITH NOWAIT
SELECT TOP 1 @ScriptName = ScriptName, @Order = ScriptRunOrder FROM @ScriptList ORDER BY ScriptRunOrder ASC
WHILE EXISTS (SELECT ScriptRunOrder FROM @ScriptList WHERE ScriptRunOrder = @Order)
BEGIN
SELECT @ScriptRunOrder = @Order
SELECT @Message = 'Now processing script file ' + @ScriptName
RAISERROR (@Message, 10,1) WITH NOWAIT
SET @Cmd ='sqlcmd -E -S ' + @ServerName +' -d ' + @DBName +' -i "' + @ScriptName +'" -o "' +@OutputFolder + '\'+ @DBName + '_'+'ScriptName_['+
SUBSTRING (SUBSTRING(@ScriptName,CHARINDEX('\',@ScriptName)+1,LEN(@ScriptName)),1,CHARINDEX('.',SUBSTRING(@ScriptName,CHARINDEX('\',@ScriptName)+1,LEN(@ScriptName)))-1)+ ']'+'_Results.txt"'
EXEC master..XP_CMDSHELL @Cmd,no_output
SELECT TOP 1 @ScriptName = ScriptName, @Order = ScriptRunOrder FROM @ScriptList WHERE ScriptRunOrder > @Order ORDER BY ScriptRunOrder ASC
IF (@Order = @ScriptRunOrder) BREAK
END
DELETE FROM @DatabaseList WHERE DBName = @DBName
SELECT TOP 1 @DBName = DBName FROM @DatabaseList ORDER BY DatabaseID ASC
END
--RETURN
Thanks,
Vijay
November 30, 2010 at 6:18 am
the problem with pushing the CMD output to variable is that the output is generally more than 1 value (IE. multiple lines or "rows" in SQL Server)
Had a similar issue a while ago and used to use a Table and push the output into the table.
I think this is it...
insert into tableXXX
exec xp_cmdshell 'dir "c:\"'
For the error message above, you should get 2 rows in the table.
November 30, 2010 at 6:30 am
grahamc (11/30/2010)
the problem with pushing the CMD output to variable is that the output is generally more than 1 value (IE. multiple lines or "rows" in SQL Server)Had a similar issue a while ago and used to use a Table and push the output into the table.
I think this is it...
insert into tableXXXexec xp_cmdshell 'dir "c:\"'
For the error message above, you should get 2 rows in the table.
Hi grahamc ,
Thanks for the code.
But i need the out put which i am persisting in output file.
Thanks,
Vijay
November 30, 2010 at 6:48 am
vijay.singh 97987 (11/30/2010)
grahamc (11/30/2010)
the problem with pushing the CMD output to variable is that the output is generally more than 1 value (IE. multiple lines or "rows" in SQL Server)Had a similar issue a while ago and used to use a Table and push the output into the table.
I think this is it...
insert into tableXXXexec xp_cmdshell 'dir "c:\"'
For the error message above, you should get 2 rows in the table.
Hi grahamc ,
Thanks for the code.
But i need the out put which i am persisting in output file.
Thanks,
Vijay
Then add something like:
Exec xp_cmdshell 'bcp "SELECT * FROM tableXXX" queryout "C:\data.txt" -S.\SQL2008r2 -T -c'
to
November 30, 2010 at 6:54 am
the other option is to look at the CMD options for outputting a file, EG.
ping http://www.google.co.uk >filename.txt
December 2, 2010 at 3:36 am
Hi grahamc,
Thanks for your replies.This is not meeting my requirment.
What i am trying to do is, i am trying to automate script execution.
Here i am logging the error of the query in a text file in X drive.But i need the error to be mailed using SQL mail without using the output file attached. Instead the body of the mail should contain the error which is in output text file.
Is there any way to insert the error into physical/ temp table and then i can fetch the error and send mail?
Below is the query i am using to get the error in Output file.
SET @Cmd ='sqlcmd -E -S ' + @ServerName +' -d ' + @DBName +' -i "' + @ScriptName +'" -o "' [highlight=#ffff11]+@OutputFolder + '\'+ @DBName + '_'+'ScriptName_['+
SUBSTRING (SUBSTRING(@ScriptName,CHARINDEX('\',@ScriptName)+1,LEN(@ScriptName)),1,CHARINDEX('.',SUBSTRING(@ScriptName,CHARINDEX('\',@ScriptName)+1,LEN(@ScriptName)))-1)+ ']'+'_Results.txt[/highlight]"'
Please advice.
Thanks,
Vijay
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply