Ping Linked Server
This Stored Procedure serves to verify if some error with the source of data of a Linked Server exists. In this in case, I will use a mdb that already exists in the Office - Northwind.mdb. If we do a Link Server concerned to this mdb, this is generated without errors . But when this mdb is opened, in an exclusive way, the Link Server originates an Error ODBC that the @@ERROR cannot deal with. To solve this problem I made use of the SP's: sp_OACreate, sp_OASetProperty, sp_OAMethod and sp_OADestroy, that solved this problem very well. A Sored Procedure was created, to usp_Ping_LinkServer, that receives, as input, the Path and the ConnectionString. To copy this code to TSQL and to execute:
/*
Name:usp_Ping_LinkServer
Made by:João Fragoso
Date :2004/06/17
*/
CREATE PROCEDURE usp_Ping_LinkServer
(
@Path varchar (1000)
,@Connect varchar (1000)
,@ErrAdo int OUTPUT
,@ErrScr varchar (255) OUTPUT
,@ErrDesc varchar (255) OUTPUT
)
AS
DECLARE @Conn int -- ADO Connection object
, @hr int -- OLE return value
, @src varchar(255) -- OLE Error Source
, @desc varchar(255) -- OLE Error Description
--Create Object
EXEC @hr = sp_OACreate 'ADODB.Connection', @Conn OUT
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @Conn, @src OUT, @desc OUT
SET @ErrAdo = @HR
SET @ErrScr = @src
SET @ErrDesc = @desc
RETURN
END
EXEC @hr = sp_OASetProperty @Conn, 'ConnectionString', @Connect
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @Conn, @src OUT, @desc OUT
SET @ErrAdo = @HR
SET @ErrScr = @src
SET @ErrDesc = @desc
RETURN
END
EXEC @hr = sp_OAMethod @Conn, 'Open'
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @Conn, @src OUT, @desc OUT
SET @ErrAdo = @HR
SET @ErrScr = @src
SET @ErrDesc = @desc
RETURN
END
EXEC @hr = sp_OADestroy @Conn
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @Conn, @src OUT, @desc OUT
SET @ErrAdo = @HR
SET @ErrScr = @src
SET @ErrDesc = @desc
RETURN
END
SET @ErrAdo = 0
/*
To test we will execute the SP
DECLARE @Path varchar (1000)
,@Connect varchar (1000)
,@ErrAdo int
,@ErrScr varchar (255)
,@ErrDesc varchar (255)
SET @Path = 'C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb'
SET @Connect = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source='+@Path+''
EXECUTE usp_Ping_LinkServer
@Path
,@Connect
,@ErrAdo OUTPUT
,@ErrScr OUTPUT
,@ErrDesc OUTPUT
SELECT @ErrAdo AS 'Err'
,@ErrScr as 'ErrScr'
,@ErrDesc as 'ErrDesc'
If we keep the mdb opened, in an exclusive way, or the wrong Path, we will make an Error
In this in case, it was because we had opened the mdb in the exclusive way
ErrErrSrcErrDesc
-2147467259Microsoft JET Database EngineCould not use ''; file already in use.
*/
