June 4, 2015 at 1:12 pm
My ultimate goal is to run a process on one server that will execute script files on another server. I've already put that together and it runs great, but now I'm to the testing phase.
I intentionally added a script that should throw an error. It's trying to insert a character into an int.
The error shows in the xp_cmdshell output, but the @rc remains 0, failing to raise an error and jump to the catch block.
Here's some code (including some PRINT statements for debugging):
-- The script runner process is running on DEVDBSEPQA01 as a login that has sysadmin everywhere.
DECLARE @rc INT, @cmdStr VARCHAR(2000)
SET @cmdStr = 'osql -S DEVDBCRMQA01 -E -i "\\DEVDBCRMQA01\SQL_SCRIPTS\DBA\DATA MANIPULATION\PopulateTestTableBad.sql"'
BEGIN TRY
CREATE TABLE #t (cmdout VARCHAR(255), ID INT IDENTITY)
INSERT INTO #t EXEC @rc = master..xp_cmdshell @cmdStr
SELECT * FROM #t
DROP TABLE #t
PRINT '@rc = ' + CAST(@rc AS VARCHAR(1))
END TRY
BEGIN CATCH
PRINT 'Something went wrong'
END CATCH
When I execute this code in SSMS, I expect to see some sort of error involving character/int conversion.
The code in PopulateTestTableBad.sql --- ARTesting.field2 is an INT:
USE [DBA]
GO
INSERT INTO ARTesting(field2) VALUES('M')
GO
Here's what I get in the results pane:
CMDCOUT ID
------------------------------------------------------------------------------------- ---------------------------
1> 2> 1> 2> Msg 245, Level 16, State 1, Server DEVDBCRMQA01, Line 1 1
Conversion failed when converting the varchar value 'M' to data type int. 2
1> 3
The Messages pane shows:
(3 row(s) affected)
(3 row(s) affected)
@rc = 0
Viewing post 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy