对象名称包含的前缀超过了最大数量。运行链接查询时，最大值为2
对象名称包含的前缀超过了最大数量。运行链接查询时，最大值为2
提问于 2017-11-07 00:38:05
我正在sql server中编写一个sql脚本，它基于数据创建动态表。我最初收到一个错误消息：“The object name 'GBIPS-I-DB324D.CoreReferenceStaging.dbo.FinData2000_1‘包含的前缀数量超过了最大值。最大值是2。”同时运行原始查询。GBIPS-I-DB324D是链接服务器对象。
我尝试使用execute语句，但现在在关键字' use‘附近出现错误语法。
原始查询
BEGIN
set nocount on
declare @startYear int, @startQuarter int, @sql nvarchar(max), @tableName varchar(50);
set @startYear = 2000;
set @startQuarter = 1;
while(@startYear < 2018)
begin
set @startQuarter = 1;
while(@startQuarter < 5)
begin
set @tableName = 'FinData' + cast(@startYear as varchar) + '_' + cast(@startQuarter as varchar);
set @sql = '
drop table [GBIPS-I-DB324D].[CoreReferenceStaging].[dbo].[' + @tableName + '];
create table [GBIPS-I-DB324D].[CoreReferenceStaging].[dbo].[' + @tableName + '] ( calendarYear int, calendarQuarter int, companyid bigint not null, dataitemid bigint not null, dataitemvalue numeric(28,6), fiscalyear int, fiscalquarter int, periodenddate datetime, filingdate datetime, latestforfinancialperiodflag bit, latestfilingforinstanceflag bit );
insert into [GBIPS-I-DB324D].[CoreReferenceStaging].[dbo].[' + @tableName + ']
select fp.calendarYear, fp.calendarQuarter, fp.companyid, fd.dataitemid, fd.dataitemvalue, fp.fiscalyear, fp.fiscalquarter, fi.periodenddate, fi.filingdate, fi.latestforfinancialperiodflag, fi.latestfilingforinstanceflag
from [Xpressfeed_dev].[dbo].[ciqFinPeriod] fp
inner join [Xpressfeed_dev].[dbo].[ciqFinInstance] fi on fi.financialPeriodId = fp.financialPeriodId
inner join [Xpressfeed_dev].[dbo].[ciqFinInstanceToCollection] fc on fc.financialInstanceId = fi.financialInstanceId
inner join [Xpressfeed_dev].[dbo].[ciqFinCollection] c on c.financialCollectionId = fc.financialCollectionId
inner join [Xpressfeed_dev].[dbo].[ciqFinCollectionData] fd on fd.financialCollectionId = c.financialCollectionId
where YEAR(fi.periodenddate) = ' + cast(@startYear as varchar) + ' and QUARTER(fi.periodenddate) = ' + cast(@startQuarter as varchar)
EXEC sp_executesql @sql
set @startQuarter += 1
end
set @startYear += 1;
end
end修改后的查询
BEGIN
set nocount on
declare @startYear int, @startQuarter int, @sql nvarchar(max), @tableName varchar(50);
set @startYear = 2000;
set @startQuarter = 1;
while(@startYear < 2018)
begin
set @startQuarter = 1;
while(@startQuarter < 5)
begin
set @tableName = 'FinData' + cast(@startYear as varchar) + '_' + cast(@startQuarter as varchar);
set @sql = '
EXECUTE (USE CoreReferenceStaging;drop table [dbo].[' + @tableName + ']) AT [GBIPS-I-DB324D]
EXECUTE (USE CoreReferenceStaging;create table [dbo].[' + @tableName + '] ( calendarYear int, calendarQuarter int, companyid bigint not null, dataitemid bigint not null, dataitemvalue numeric(28,6), fiscalyear int, fiscalquarter int, periodenddate datetime, filingdate datetime, latestforfinancialperiodflag bit, latestfilingforinstanceflag bit )) AT [GBIPS-I-DB324D]
insert into [GBIPS-I-DB324D].[CoreReferenceStaging].[dbo].[' + @tableName + ']
select fp.calendarYear, fp.calendarQuarter, fp.companyid, fd.dataitemid, fd.dataitemvalue, fp.fiscalyear, fp.fiscalquarter, fi.periodenddate, fi.filingdate, fi.latestforfinancialperiodflag, fi.latestfilingforinstanceflag
from [Xpressfeed_dev].[dbo].[ciqFinPeriod] fp
inner join [Xpressfeed_dev].[dbo].[ciqFinInstance] fi on fi.financialPeriodId = fp.financialPeriodId
inner join [Xpressfeed_dev].[dbo].[ciqFinInstanceToCollection] fc on fc.financialInstanceId = fi.financialInstanceId
inner join [Xpressfeed_dev].[dbo].[ciqFinCollection] c on c.financialCollectionId = fc.financialCollectionId
inner join [Xpressfeed_dev].[dbo].[ciqFinCollectionData] fd on fd.financialCollectionId = c.financialCollectionId
where YEAR(fi.periodenddate) = ' + cast(@startYear as varchar) + ' and QUARTER(fi.periodenddate) = ' + cast(@startQuarter as varchar)
EXEC sp_executesql @sql
set @startQuarter += 1
end
set @startYear += 1;
end
end回答 1
Stack Overflow用户
发布于 2018-01-10 19:21:47
据我所知，在使用链接服务器时，不能使用'USE‘语句。这没有回答您关于最大值为2的问题，但“Use”不是可行的方法。当游戏中没有链接服务器时，您可以使用' use '，但在执行DML操作的同一语句中构建use-statement。你将不得不使用动态SQL，因为它也有副作用
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/47141699
复制相关文章
相似问题
腾讯云开发者
