Intermittant fault on script.

Question

Post reply

Intermittant fault on script.

hb21l6

SSCommitted

Points: 1647
More actions
September 15, 2015 at 9:38 am

#300487

Hi all,

This is a bit of a bizarre problem and I'm not sure how to fix it..

I execute the following query a few time (shown below) and it works fine..

But randomly, I get this error.

Msg 7202, Level 11, State 2, Line 15

Could not find server 'ExcelServer2' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.

I understand the error, and when I look in sys.servers its not present. But if I leave it for a minute or two, then run the script again ... it works fine?!?!?

same excel file btw. not changed it.

IF not EXISTS(SELECT * FROM sys.servers WHERE name = N'ExcelServer2') EXEC sp_addlinkedserver @server = 'ExcelServer2', @srvproduct = 'Excel', @provider = 'Microsoft.ACE.OLEDB.12.0', @datasrc = '\\serverName\e$\ICDataDrop\WORKBOOK.XLS', @provstr = 'Excel 12.0;IMEX=1;HDR=YES;'; -- delete our temp table IF OBJECT_ID('dbo.TEMPDATA', 'U') IS NOT NULL DROP TABLE dbo.TEMPDATA; -- insert data into our temp table SELECT * INTO TEMPDATA FROM OPENQUERY(ExcelServer2, 'select * from [Warranty Data$]'); -- drop our connection to the file IF EXISTS(SELECT * FROM sys.servers WHERE name = N'ExcelServer2') EXEC sp_dropserver @server = N'ExcelServer2';

Any suggestions why it might work most times, then some times not?

Thanks in advance

Dave

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183686 More actions · Answer 1

Do you have any other process running constantly that might be dropping the linked server?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Lowell SSC Guru Points: 323482 More actions · Answer 2

could you have a script out there that tries to be bulletproof, and drops and recreates the linked server before it does any work, in order to guarantee, for that specific process, the linked server exists?

I've done that kind of thing myself, so that's what i would look for first.

here's how i search source code and job steps for possible strings; you might need to repeat in your master/dbautilities databases as well.



      SELECT objz.name  COLLATE SQL_Latin1_General_CP1_CI_AS,

             objz.type_desc  COLLATE SQL_Latin1_General_CP1_CI_AS,

             modz.definition  COLLATE SQL_Latin1_General_CP1_CI_AS,

             @SearchTerm AS SearchTerm

      FROM   sys.objects objz

             INNER JOIN sys.sql_modules modz

                     ON objz.OBJECT_ID = modz.OBJECT_ID

      WHERE  modz.definition LIKE '%sp_dropserver%'

      UNION ALL

      --jobname, jobstepname, job stepdefinition

      SELECT jobz.name + ':step '

              + CONVERT(VARCHAR, stpz.step_id)  COLLATE SQL_Latin1_General_CP1_CI_AS,

              stpz.step_name  COLLATE SQL_Latin1_General_CP1_CI_AS,

              stpz.command  COLLATE SQL_Latin1_General_CP1_CI_AS,

             @SearchTerm AS SearchTerm

       FROM   msdb.dbo.sysjobs jobz

              INNER JOIN msdb.dbo.sysjobsteps stpz

                      ON jobz.job_id = stpz.job_id

       WHERE  stpz.command LIKE '%sp_dropserver%'

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!