sql-server - 在 sys.servers 错误中找不到服务器“DATABASE_NAME”

Question

下面的代码嵌入在 vb.net 项目的 .sql 文件中。它给了我一个错误:

在 sys.servers 中找不到服务器“DATABASE_NAME”。验证是否指定了正确的服务器名称。如有必要，执行存储过程 sp_addlinkedserver 将服务器添加到 sys.servers。

我 checkin 了sys.server，如果我执行select * from sys.servers，则会返回LinkedDatabaseName

这是我收到错误的代码......

-----------------------------------
-- Obs Set Obs Set Obs Item Xref
-----------------------------------
-- STEP 1: txmr_TABLE1 to exp_TABLE1

DELETE FROM LinkedDatabaseName...exp_TABLE1
Select distinct newid() as GUID,
       b.ObsSetCode  as 'parObsSetCode',
       c.ObsSetCode  as 'chObsSetCode',
       d.ObsItemCode as 'chObsItemCode'
Into #tmp_exp_TABLE1
From   $$DATABASE_NAME$$..txmr_TABLE1 a
      Inner Join $$DATABASE_NAME$$..txmr_obs_set  b On a.parObsSetGUID  = b.GUID
      Left  Join $$DATABASE_NAME$$..txmr_obs_set  c On a.chObsSetGUID   = c.GUID
      Left Join  $$DATABASE_NAME$$..txmr_obs_item  d On a.chObsItemGUID = d.GUID
Order By b.ObsSetCode   

Select * From #tmp_exp_TABLE1


EXEC $$DATABASE_NAME$$..dbo.txmr_ExtractPOCIS '#tmp_exp_TABLE1', 
                                         '$$DATABASE_NAME$$.dbo.txmr_TABLE1_chg', 
                                         @FromVersion, 
                                         @ToVersion;

INSERT INTO LinkedDatabaseName...exp_TABLE1 (GUID, parObsSetCode, chObsSetCode, chObsItemCode)
SELECT GUID, parObsSetCode, chObsSetCode, chObsItemCode
FROM #tmp_exp_TABLE1
ORDER BY parObsSetCode, chObsSetCode;
DROP TABLE #tmp_exp_TABLE1;

SELECT COUNT(*), 'exp_TABLE1' FROM LinkedDatabaseName...exp_TABLE1;

-- STEP 2: txmr_TABLE1_chg to exp_TABLE1_chg

DELETE FROM LinkedDatabaseName...exp_TABLE1_chg;
INSERT INTO LinkedDatabaseName...exp_TABLE1_chg (ChangeID, DateModified, parObsSetCode, chObsSetCode, chObsItemCode, RationaleLink, RationaleFreeText)
SELECT a.GUID              as 'ChangeID', 
       a.DateModified      as 'DateModified', 
       b.ObsSetCode        as 'parObsSetCode', 
       c.ObsSetCode        as 'chObsSetCode', 
       d.ObsItemCode       as 'chObsItemCode', 
       r.RationaleID       as 'RationaleLink', 
       a.RationaleFreeText as 'RationaleFreeText' 
FROM $$DATABASE_NAME$$..txmr_TABLE1_chg a
    INNER JOIN $$DATABASE_NAME$$..txmr_obs_set   b  ON a.parObsSetGUID = b.GUID
    LEFT JOIN  $$DATABASE_NAME$$..txmr_obs_set   c  ON a.chObsSetGUID  = c.GUID
    LEFT JOIN  $$DATABASE_NAME$$..txmr_obs_item  d  ON a.chObsItemGUID = d.GUID
    LEFT JOIN  $$DATABASE_NAME$$..txmr_rationale r  ON a.RationaleGUID    = r.GUID
WHERE @StartDate <= a.DateModified AND @EndDate > a.DateModified
ORDER BY a.DateModified, b.ObsSetCode;

SELECT COUNT(*), 'exp_TABLE1_chg' 
FROM LinkedDatabaseName...exp_TABLE1_chg;

Answer 1

问题在于 $$DATABASE_NAME$$ 替换字符串的用法不一致。大多数情况下它用于引用特定的数据库

$$DATABASE_NAME$$..txmr_TABLE1

但在执行字符串中:

EXEC $$DATABASE_NAME$$..dbo.txmr_ExtractPOCIS

句点太多，这意味着它现在引用的是另一台服务器而不是数据库。

将此语句更改为:

EXEC $$DATABASE_NAME$$.dbo.txmr_ExtractPOCIS

应该可以解决问题。