ole db提供程序 mysql_无法初始化链接服务器的OLE DB提供程序“MSDASQL”的数据源对象“（空）”...

/* Linked server between local(Client) SQL server and Remote SQL server 2005*/

USE master

GO

-- To use named parameters: Add linked server in the source (Local machine - eg: MachineName or LocalSeverLoginName)

sp_addlinkedserver

@server = N'LnkSrv_RemoteServer_TEST',

@srvproduct=N'', -- Leave it blank when its not 'SQL Server'

@provider=N'SQLNCLI', -- see notes

@datasrc=N'RemoteServerName',

@provstr=N'UID=sa;PWD=sa;'

--,@catalog = N'MYDATABASE' eg: pubs

GO

/*

Note:

To check provider name use the folling query in the destination server

Select Provider From sys.servers

*/

----------------------------------------------------------------------------------------------------------

-- Optional

--EXEC sp_addlinkedsrvlogin 'LnkSrv_RemoteServer_TEST', 'true' -- (self is true) -- for LocalSeverLoginName

--GO

-- Remote login

sp_addlinkedsrvlogin

@rmtsrvname = 'LnkSrv_RemoteServer_TEST',

@useself = 'False',

@rmtuser = 'sa',

@rmtpassword = 'sa'

GO

-- OR

/*

IF the above add linked server login failed then try in the Linked Server (LnkSrv_RemoteServer_TEST) Property

Select -> Security - > 'For a login not defined in the list above, Connection will:'

Choose - > Be made using this security context

SET Remote login: sa

With password: sa

*/

----------------------------------------------------------------------------------------------------------

-- Test server connection

declare @srvr nvarchar(128), @retval int;

set @srvr = 'LnkSrv_RemoteServer_TEST';

begin try

exec @retval = sys.sp_testlinkedserver @srvr;

end try

begin catch

set @retval = sign(@@error);

end catch;

if @retval <> 0

raiserror('Unable to connect to server. This operation will be tried later!', 16, 2);

-- OR

BEGIN TRY

EXEC sp_testlinkedserver N'LnkSrv_RemoteServer_TEST';

END TRY

BEGIN CATCH

PRINT 'Linked Server not available';

RETURN;

END CATCH

----------------------------------------------------------------------------------------------------------

-- Get access linked server database

SET xact_abort ON

GO

BEGIN TRANSACTION

SELECT * FROM LnkSrv_RemoteServer_TEST.DBName.dbo.tblName

COMMIT TRAN

GO

-- OR

SELECT * FROM OPENQUERY(LnkSrv_RemoteServer_TEST, 'SELECT * FROM DBName.dbo.tblName')

GO

-- OR

SELECT * FROM OPENQUERY(LnkSrv_RemoteServer_TEST, 'SELECT * FROM sys.databases Order by name')

GO

----------------------------------------------------------------------------------------------------------

2010-08-06 08:58:02