直接这样调用没有问题
exec sp_addlinkedserver 'ITSV', '', 'SQLOLEDB', '192.168.0.151 '
exec sp_addlinkedsrvlogin 'ITSV', 'false',null, 'sa', ''
go
SELECT * FROM [ITSV].[UFDATA_888_2009].[dbo].[Table_Test]
go
exec sp_dropserver 'ITSV ', 'droplogins ' 但是一旦在存储过程中创建
SET QUOTED_IDENTIFIER on
GO
SET ANSI_NULLS on
GO
CREATE PROCEDURE [intoU8aaa]
AS
go
exec sp_addlinkedserver 'ITSV', ' ', 'SQLOLEDB', '192.168.0.151'
exec sp_addlinkedsrvlogin 'ITSV', 'false',null, 'sa', ''
go
SELECT * FROM [ITSV].[UFDATA_888_2009].[dbo].[Table_Test]
go
exec sp_dropserver 'ITSV', 'droplogins'
GO
然后打开存储过程intoU8aaa就这这样
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GOALTER PROCEDURE [intoU8aaa]
ASGO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
显然 intoU8aaa没有创建成功。很奇怪啊。
exec sp_addlinkedserver 'ITSV', '', 'SQLOLEDB', '192.168.0.151 '
exec sp_addlinkedsrvlogin 'ITSV', 'false',null, 'sa', ''
go
SELECT * FROM [ITSV].[UFDATA_888_2009].[dbo].[Table_Test]
go
exec sp_dropserver 'ITSV ', 'droplogins ' 但是一旦在存储过程中创建
SET QUOTED_IDENTIFIER on
GO
SET ANSI_NULLS on
GO
CREATE PROCEDURE [intoU8aaa]
AS
go
exec sp_addlinkedserver 'ITSV', ' ', 'SQLOLEDB', '192.168.0.151'
exec sp_addlinkedsrvlogin 'ITSV', 'false',null, 'sa', ''
go
SELECT * FROM [ITSV].[UFDATA_888_2009].[dbo].[Table_Test]
go
exec sp_dropserver 'ITSV', 'droplogins'
GO
然后打开存储过程intoU8aaa就这这样
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GOALTER PROCEDURE [intoU8aaa]
ASGO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
显然 intoU8aaa没有创建成功。很奇怪啊。
SET QUOTED_IDENTIFIER on
GO
SET ANSI_NULLS on
GO
CREATE PROCEDURE [intoU8aaa]
AS
exec sp_addlinkedserver 'ITSV', ' ', 'SQLOLEDB', '192.168.0.151'
exec sp_addlinkedsrvlogin 'ITSV', 'false',null, 'sa', '' SELECT * FROM [ITSV].[UFDATA_888_2009].[dbo].[Table_Test]exec sp_dropserver 'ITSV', 'droplogins'
GO
GO
SET ANSI_NULLS on
GO
CREATE PROCEDURE [intoU8aaa]
ASexec sp_addlinkedserver 'ITSV', ' ', 'SQLOLEDB', '192.168.0.151'
exec sp_addlinkedsrvlogin 'ITSV', 'false',null, 'sa', '' SELECT * FROM [ITSV].[UFDATA_888_2009].[dbo].[Table_Test]exec sp_dropserver 'ITSV', 'droplogins'?
远程连接语句
exec sp_addlinkedserver 'ITSV', ' ', 'SQLOLEDB', '192.168.0.151'
exec sp_addlinkedsrvlogin 'ITSV', 'false',null, 'sa', ''
必须与调用语句
SELECT * FROM [ITSV].[UFDATA_888_2009].[dbo].[Table_Test]
分离否则会提示没有'ITSV'。储存过程不能有GO,但是执行语句又必须有GO。这该怎么办哦各位给个建议把。
exec sp_addlinkedsrvlogin 'ITSV', 'false',null, 'sa', ''
放在存储过程和trigger里边。这样要报错
2.go和use都不是sql的元素,,在存储过程中肯定不能用你可以用
exec('sp_addlinkedserver ''ITSV'', '' '', ''SQLOLEDB'', ''192.168.0.151''' )
exec('exec sp_addlinkedsrvlogin 'ITSV', 'false',null, 'sa', '' ')
exec('SELECT * FROM [ITSV].[UFDATA_888_2009].[dbo].[Table_Test]')
想应该能建正常的了
具体用法去查books online