locked
SQL error = 7202 on linked servers

    Question

  •  

    Hi,

    I have created 5 projects using the new database project wizard. Importing the scema from SQL Server 2005 with no problems but one.

    I keep getting this error message even though when I query sysservers there is a server named ANNEALING. The SQL code works great on the on-line server.

    "Error 166 TSD4001: Could not find server 'ANNEALING' in sysservers. Execute sp_addlinkedserver to add the server to sysservers. (SQL error = 7202) C:\Documents and Settings\donron\My Documents\Visual Studio 2005\Projects\CMC-iSQL-SERVER_RuntimeAnnex\CMC-iSQL-SERVER_RuntimeAnnex\Schema Objects\Stored Procedures\dbo.HBADailyStackCollector.proc.sql 6 1 CMC-iSQL-SERVER_RuntimeAnnex"

    I have the same type of SQL code in another database and all I get is a warning. This I can understand

    "Warning 90 TSD3021: The following cross-server dependencies of [dbo].[v_TM_DelaysFromLevel2Last4Turns] could not be verified: [CMC_L2_TM].[].[BSCTM_DEV].[PDO_DELAY].[MILLUNITID], [CMC_L2_TM].[].[BSCTM_DEV].[PDO_DELAY].[TEXT], [CMC_L2_TM].[].[BSCTM_DEV].[PDO_DELAY].[DELAYTYPE], [CMC_L2_TM].[].[BSCTM_DEV].[PDO_DELAY].[DELAYEND], [CMC_L2_TM].[].[BSCTM_DEV].[PDO_DELAY].[STARTTIME], [CMC_L2_TM].[].[BSCTM_DEV].[PDO_DELAY]. You might not be able to deploy the project. C:\Documents and Settings\donron\My Documents\Visual Studio 2005\Projects\CMC-SERVER_TM\CMC-SERVER_TM\Schema Objects\Views\dbo.v_TM_DelaysFromLevel2Last4Turns.view.sql 1 1 CMC-SERVER_TM"

    Here is the code that works: (Only gives Warning)

    CREATE VIEW [dbo].[v_TM_DelaysFromLevel2Last4Turns]

    AS

    SELECT

    STARTTIME,

    DELAYEND,

    DELAYTYPE,

    [TEXT],

    DATEDIFF(minute, STARTTIME, DELAYEND) AS DelayLengthMinutes

    FROM

    CMC_L2_TM..BSCTM_DEV.PDO_DELAY

    WHERE

    STARTTIME BETWEEN dbo.fn_StartOrEndDateTimeLast4Turns('END') AND dbo.fn_StartOrEndDateTimeLast4Turns('START') AND

    DELAYEND BETWEEN dbo.fn_StartOrEndDateTimeLast4Turns('END') AND dbo.fn_StartOrEndDateTimeLast4Turns('START') AND

    MILLUNITID LIKE 'PTM';

     

     

    Here is the code that does not: (Error code)

    CREATE proc [dbo].[HBADailyStackCollector]

    @StartDate datetime,

    @EndDate datetime

    as

    Select *

    from ANNEALING..HBA.CHARGES where UNCAPTIME >= @StartDate and UNCAPTIME < @EndDate and COILCOUNT<>0

     

    So far I LOVE the product and have used it with great results. Any input will be welcome!

    Thanks

    Don

    Tuesday, January 30, 2007 9:23 AM

Answers

  • Does the linked server definition ANNEALING exist on your local SQL Server instance that you are running the project on? This error comes from the local SQL Server that is used for the compile time validation of the SQL fragments imported.

    Also I noticed, and this is probably a posting typo that the proc definition uses ANNEALING..HBA.CHARGES

    So this would mean:

    SERVER = ANNEALING
    DATABASE = ?
    SCHEMA = HBA
    TABLE OR VIEW NAME = CHARGES

    If the database name is truly not specified in this case we can not resolve it, but that does explain the TSD4xxxx SQL error, that is truly caused by the fact the your local design validation instance does not have a definition for that linked server name.

    -GertD

    Wednesday, January 31, 2007 7:27 AM

All replies

  • Hi Don,

    is the TSD / SQL Error occuring during DEPLOYMENT which means that one of the scripts actively needs the linked connection to be deployed? Apparently (if the link exists) the login used for the deployment target does not have permission to use it.

    You say the server shows up in sysservers. I assume you do mean sysservers on the LOCAL instance.  Otherwise you will also get the error on BUILD.

    The warning you show above occurs during BUILD / REBUILD and is just indicating that the deployment will fail (as it did above) when you try to deploy it without the appropriate linkedserver / permissions to the linked server. In this case the linked server is in fact available locally and can be resolved.

    HTH
    Alle

    Tuesday, January 30, 2007 7:24 PM
  • Hi Alle,

    This error occurred after I created the project with the wizard. I have not tried to build or deploy the project yet. I have created projects and imported scripts on difference database with no errors I cannot understand. I'm stumped by this one.

    I'm not worried about the warning's, the warning's I posted I understand, I only posted that one as an example of one that does not give an error.

    It's the TSD/SQL error I'm lost on.

    Thanks

    Don

    Wednesday, January 31, 2007 6:53 AM
  • Does the linked server definition ANNEALING exist on your local SQL Server instance that you are running the project on? This error comes from the local SQL Server that is used for the compile time validation of the SQL fragments imported.

    Also I noticed, and this is probably a posting typo that the proc definition uses ANNEALING..HBA.CHARGES

    So this would mean:

    SERVER = ANNEALING
    DATABASE = ?
    SCHEMA = HBA
    TABLE OR VIEW NAME = CHARGES

    If the database name is truly not specified in this case we can not resolve it, but that does explain the TSD4xxxx SQL error, that is truly caused by the fact the your local design validation instance does not have a definition for that linked server name.

    -GertD

    Wednesday, January 31, 2007 7:27 AM
  • Thanks this worked!

    " Does the linked server definition ANNEALING exist on your local SQL Server instance that you are running the project on? This error comes from the local SQL Server that is used for the compile time validation of the SQL fragments imported.

    -GertD"

    I did not have the same name on my local sql server. I was under the impression that the local server had nothing to do with the server you were importing. I was wondering how this was handled. It just so happened that in the Warning 90 TSD3021 I had the same link name on both systems because that was my project and I keep my development server and on-line server the same.

    Where can I find out how I must configure the local sql server so I don't run into this again?

    Thanks

    Don

    Wednesday, January 31, 2007 8:29 AM