用户 'sa' 登录失败。原因: 未与信任 SQL Server 连接相关联。
[OLE/DB provider returned message: 连接字符串属性无效]
但我用查询分析器去连就没问题,shint-lemon服务器的登录方式是混合模式
EXEC sp_addlinkedserver 'shint-test','','SQLOLEDB',NULL,NULL,'DRIVER={SQL Server};SERVER=shint-lemon;UID=sa;PWD=;'
exec sp_addlinkedsrvlogin @rmtsrvname='shint-test',@useself='false',@locallogin='sa',@rmtuser='sa',@rmtpassword=''
go
select * from [shint-test].shinthrmdb_DY.dbo.ygzl
go
sp_droplinkedsrvlogin 'shint-test','sa'
go
sp_dropserver 'shint-test'
go
sp_addlinkedserver 'shint-test','','SQLOLEDB',NULL,NULL,Provider=SQLOLEDB.1;Data Source=shint-lemon;User ID=sa;Password=;'
select * from shint-test.database.sa.table
此时,你必须用sa登录到本地服务器.
就OK了
EXEC sp_addlinkedserver 'shint-test','','SQLOLEDB',NULL,NULL,'DRIVER={SQL Server};SERVER=shint-lemon;UID=sa;PWD=123;'
exec sp_addlinkedsrvlogin @rmtsrvname='shint-test',@useself='false',@locallogin='sa',@rmtuser='sa',@rmtpassword='123'
go
select * from [shint-test].shinthrmdb.dbo.ygzl
go
sp_droplinkedsrvlogin 'shint-test','sa'
go
sp_dropserver 'shint-test'
go
SQL Server 不存在或拒绝访问。
[OLE/DB provider returned message: 连接字符串属性无效]
EXEC sp_addlinkedserver 'shint-test','','MSDASQL',NULL,NULL,'DRIVER={SQL Server};SERVER=shint-lemon;UID=sa;PWD=123;'2. use following command which I always used for adding linked SQL Server
exec sp_dropserver remote1, droplogins
exec sp_addserver shint-lemon
exec sp_setnetname shint-lemon, shint-lemon
exec sp_addlinkedsrvlogin @rmtsrvname='shint-test',@useself='false',@locallogin='sa',@rmtuser='sa',@rmtpassword=''
3. use OLE-DB provider for SQL Server (which is better than ODBC driver)
You may go to http://www.connectionstrings.com/ to get the connection string for it.
go
sp_addlinkedsrvlogin 'shint-test', false, NULL, 'sa', ''
go
select * from [shint-test].pubs.dbo.syhsobjects
go
不知道什么原因,还望高手指点。
================================================
你是在本地用“查询分析器”来连shint-lemon吗?到你执行Exec sp_AddLinkedServer 这个命令的Server上去用“查询分析器”连shint-lemon试试。告诉一下结果。