测试链接服务器中是否存在表(服务器名称是参数) [英] Test if Table Exists in Linked Server (Server Name is a Parameter)
本文介绍了测试链接服务器中是否存在表(服务器名称是参数)的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我需要测试一个表是否存在于链接服务器中,其中链接服务器是一个参数(必须是),这就是我使用exec方法的原因。我尝试了很多方法但是我没有成功。
声明 @LinkedServerName varchar ( 50 )
声明 @ DS varchar ( 50 )
声明 @ username varchar ( 50 )
声明 @ pswd varchar ( 12 )
声明 @ TableExists int
< span class =code-keyword>设置 @ DS = ' test\TestDB'
设置 @ LinkedServerName = ' LinkedServerAutoAddDrop'
设置 @ username = ' ua'
设置 @ pswd = ' pass'
设置 @ TableExists = 0
if 不 存在(选择 * < span class =code-keyword>来自 sys.servers 其中 name = @ LinkedServerName )
BEGIN
EXEC sp_addlinkedserver
@ server = @ LinkedServerName,
@srvproduct = ' ',
@ provider = ' SQLNCLI',
@ datasrc = @ DS
EXEC sp_addlinkedsrvlogin @ LinkedServerName ,N ' false', NULL , @ username , @ pswd
exec sp_serveroption @ server = @ LinkedServerName,@ optname = ' rpc',@ optvalue = ' TRUE'
exec sp_serveroption @ server = @ LinkedServerName,@ optname = ' rpc out',@ optvalue = ' TRUE'
< span class =code-keyword> END
exec (' IF(EXISTS(SELECT * FROM OPENQUERY([' + @ LinkedServerName + ' ],''从LinkedDB.INFORMATION_SCHEMA.TABLES中选择*其中TABLE_NAME =''''TableName''''' ')))
BEGIN
exec(''设置' + @ TableExists + ' = 1'')
END')
IF ( @ TableExists = 1 )
BEGIN
exec ( ' 插入...')
END
解决方案
问题是您使用的是EXEC。 EXEC启动一个新的事务,不知道你的变量在它之外声明...
你必须学习和使用sp_executesql这样的东西...
http://technet.microsoft.com/en-us/library/ms188001.aspx [ ^ ]
>
请!!!!!你想在链接服务器中选择一个数据库是多么简单
选择 * 来自 servername.dbname.dbo.tablename
- 但如果您想检查表是否存在,请执行以下操作
IF NOT EXISTS ( SELECT * FROM servername.dbname.sys.tables 其中 name = @ tablename )
BEGIN
CREATE TABLE [dbo]。[YourTable](
....
....
....
)
END
I need to test if a table exists in a linked server, where linked server is a parameter (it has to be), that's why I'm using exec method. I tried many ways but I didn't succeed.
Declare @LinkedServerName varchar(50)
Declare @DS varchar(50)
Declare @username varchar(50)
Declare @pswd varchar(12)
Declare @TableExists int
Set @DS = 'test\TestDB'
Set @LinkedServerName = 'LinkedServerAutoAddDrop'
Set @username = 'ua'
Set @pswd = 'pass'
Set @TableExists = 0
if not exists(select * from sys.servers where name = @LinkedServerName)
BEGIN
EXEC sp_addlinkedserver
@server=@LinkedServerName,
@srvproduct='',
@provider='SQLNCLI',
@datasrc=@DS
EXEC sp_addlinkedsrvlogin @LinkedServerName, N'false', NULL, @username, @pswd
exec sp_serveroption @server=@LinkedServerName, @optname='rpc', @optvalue='TRUE'
exec sp_serveroption @server=@LinkedServerName, @optname='rpc out', @optvalue='TRUE'
END
exec('IF (EXISTS (SELECT * FROM OPENQUERY([' + @LinkedServerName + '], ''select * from LinkedDB.INFORMATION_SCHEMA.TABLES Where TABLE_NAME = ''''TableName'''''')))
BEGIN
exec (''Set ' + @TableExists + ' = 1'')
END')
IF (@TableExists = 1)
BEGIN
exec('Insert Into ...')
END
解决方案
You problem is that you are using EXEC. EXEC is starts a new transaction that has no idea about your variables declared outside of it...
You have to learn and use sp_executesql fot things like this...
http://technet.microsoft.com/en-us/library/ms188001.aspx[^]
please!!!!! what is that you want to select a db in linked server its so simple
select * from servername.dbname.dbo.tablename --but if u want to check whether a table exists then do the following IF NOT EXISTS (SELECT * FROM servername.dbname.sys.tables where name = @tablename) BEGIN CREATE TABLE [dbo].[YourTable]( .... .... .... ) END
这篇关于测试链接服务器中是否存在表(服务器名称是参数)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文