Job fails because remote server insert fails. Need testing method.
-
I have a Job that periodically calls an SP....
SP tries to insert records to a remote SQL Server using
Insert Into OpenRows et('SQLOLEDB', 'Server=[an IP #]....') .....
All is well when at the IP # the SQL Server exists and is up & running.
When not, the Job History shows ....
SQLSTATE 01000 Message 22001 SQL Server does not exist or access denied.SQLSTATE 42000 Error 17. The step failed.
, the Job "fails", and a "Warning" Application log entry goes into the Win2K Event log.
Is there a way to test 1st or lower the status of the error so that all
indicates ok, even when the INSERT fails?
Note: It is not "critical" to me that the INSERT fails or not.
Thanks
P.S. This issue is fairly time critical to me.
Once you understand the BITs, all the pieces come together
-
No way I am aware of that can guarantee the remote/linked server is up when you perform operation to it.
What do you try to achieve by lower the status of the error?
-
....What do you try to achieve by lower the status of the error?
status = severity
No Server Event Log entry or Job Failed status for the customer to "complain" about.
Any SP Statements after the failed INSERT to continue to execute.
I would like to error to be "non severe".
Once you understand the BITs, all the pieces come together -
Have a look at system table "sysmessages". Maybe you can do something from there.
-
Have your sp check for that specific error using @@error and if that error occurs, have it return 0 (success).
My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url] -
Thanks for input folks...
Rawhide, as soon as the error occurs, any subsequent SQL statements do not get executed
so as is, I can not "trap".
Try this little script in Q/A to show what I mean... I need to get to the second Print.
Exec ('select * from OpenRowset(''SQLOLEDB'',
''Server=(local);Trusted_Connection=yes;Timeout=5;'',
''Select top 1 * from sysfiles'') ')
Print 'got this far! Yeah!'
-- 192.123.123.123 must NOT be a good SQL Srv. for this test
Exec ('select * from OpenRowset(''SQLOLEDB'',
''Server=192.123.123.123;Trusted_Connection=yes;Timeout=5;'',
''Select top 1 * from sysfiles'') ')
Print 'never get here boo'
Once you understand the BITs, all the pieces come together -
Any other ideas folks?
Thanks in advance.
Once you understand the BITs, all the pieces come together -
Have you tried creating a linked server instead of using the openrowset? It may handle this situation a little differently. I don't currently have a machine to test this with. Sorry.
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB EngineerThis posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
-
Funny, I was just on anther thread about linked servers... I do not want to make a "permanent" linked server, for various reasons... but maybe the "failer" of being able to make the linked server entry may not be as "severe" as the INSERT, and I can use this "make linked server entry" just as a test, then if it does create it, just drop the entry.
Thanks I'll try it...
Once you understand the BITs, all the pieces come together -
You could throw something like this in your script and then drop it at the end....
DECLARE @LinkedServerName sysname
, @provstr nvarchar(4000)
, @ServerName sysname
-------------------------------------------------------------------------------
-- Change the following to the remote servername!
SET @ServerName = 'servername'
-------------------------------------------------------------------------------
SELECT @LinkedServerName = 'ReplicationServer_' + @ServerName
, @provstr = 'DRIVER={SQL Server};SERVER=' + @ServerName + ';UID=User;PWD=userpwd;'
-- Drop the server if it exists
IF EXISTS(SELECT * FROM master.dbo.sysservers WHERE srvname = @LinkedServerName)
EXEC sp_dropserver @LinkedServerName
-- If we don't have the login for the LexMirrorUser create it.
IF NOT EXISTS(SELECT * FROM master.dbo.syslogins WHERE name = 'User')
EXEC sp_addlogin 'User', 'userpwd', 'defaultdb'
-- Now Add the Linked Server
EXEC sp_addlinkedserver
@server = @LinkedServerName
, @provider = 'MSDASQL'
, @provstr = @provstr
, @srvproduct = 'any'
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB EngineerThis posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
-
Tried your code Gary, and plain old
sp_addlinkedserver ..., @srvproduct = '', @provider = 'SQLOLEDB',
sp_serveroption(s)...,
sp_addlinkedsrvlogin
Both ways produce no errors even if remote server does not exist.
Once I try to access something from the remote server I get either:
Server: Msg 17, Level 16, State 1, Line 1
SQL Server does not exist or access denied.
or, Using your code I get:
Server: Msg 7399, Level 16, State 1, Procedure Test, Line 1
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()).]
[OLE/DB provider returned message: [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x80004005: ].
Both are "severe" errors, and do not allow further code to execute
Even tried "2 level" approach...
select * from OpenRowset('SQLOLEDB', 'Server=(local);Trusted_Connection=yes',
'select count(1) as c from RemoteSrv.Master.DBO.SysServers')
Got error:
Server: Msg 17, Level 16, State 1, Line 5
SQL Server does not exist or access denied.
[OLE/DB provider returned message: Deferred prepare could not be completed.]
Any other ideas??
Once you understand the BITs, all the pieces come together -
Well I ended up using an SP I wrote a while back that consists of creating a Job, executing it, monitoring its success or failure, and returning any error. Since this is a once in a while task only, it did not matter to me if it takes an extra second or two to perform the operation (it takes longer on failure because it needs to "timeout").
Since the Job is a separate process, it does NOT make the current process fail.
The "temp" Job that gets created, does not "system log" any of its actions or errors, and is deleted upon completion. This work just fine for me.
Thanks all for your input.
Once you understand the BITs, all the pieces come together
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply