Netsuite error: Cannot fetch a row from OLE DB provider “MSDASQL” for linked server.

Say you have sql job calling a stored procedure on a local SQL server that is pulling data from NS scheduled at 8pm daily.  It’s been running fine for a while.  Then you move the time up to 9pm. Suddenly you’ve been getting email alerts that the job has failed.  You look at your logs and see that it fails at different sections of the stored procedure daily. (Note the stored procedure has logging steps in the code).   You already have a try catch in the stored procedure.  So, it gives you an error “Cannot fetch a row from OLEDB provider “MSDASQL” for linked server”.  So, you’ve been reviewing the logs for a few days and it’s still the same errors at the various location of the code.  At this point, you want to find out if it’s a connection issue.  You put another try catch into the first try catch.

Here’s a sample code for the checking the linked server for Netsuite connection.  Now when you get an email about a linked server connection you can take appropriate steps if the stored procedure fails.

Declare @linkserver char(100)

Select @linkserver = ‘server1’

Begin try (1st)

            Some code processing data with logging messages.

End try

Begin catch (1st)

Begin try

            Exec(‘select * from openquery(‘+@linkserver+’,’’select current_timestamp as datetimeNS;’’)’)

End try

Begin catch

            Send email or log error.

End catch

End catch (1st)

NOTE: Can’t use getdate as it returns an error.  (Unsupported scalr function:getdate.[10149])  So using CURRENT_TIMESTAMP is an ANSI SQL function.