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

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  September 15, 2015 at 10:05 am

  #1826807

  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: 323518

  More actions

  September 15, 2015 at 10:07 am

  #1826809

  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!

• hb21l6

  SSCommitted

  Points: 1647

  More actions

  September 15, 2015 at 1:04 pm

  #1826880

  Thanks for the reply Louis, no I don't have any other processes/linked servers on this server.

  This is the only one.

• hb21l6

  SSCommitted

  Points: 1647

  More actions

  September 15, 2015 at 1:06 pm

  #1826882

  Thanks for the reply Lowell

  I'll give this a go when I'm back at work tomorrow.

  That was my next step, create a more robust Sp. with further checks prior to executing the row query

  Thanks for your assistance

  Dave