none
Generating SQL Schema with Linked Servers RRS feed

  • Question

  • I have the following procedure that executes correctly and generates the correct XML data when ran in Management Studio. However whan I try to generate the SQL schema using the Add Generated Items wizard it gives an error (Failed to execute SQL Statement. Please ensure that the supplied syntex is correct). I've verified that all permissions are set correctly.  Is this because the wizard cannot figure out the linked server?
    CREATE PROCEDURE [dbo].[spPollTblHouseholds]
    AS
    
    BEGIN
    
    DECLARE @LASTRUNTIME AS DATETIME
    
    --GET THE LAST TIME THE PULL RAN
    SELECT @LASTRUNTIME = LASTRUNTIME
      FROM TABLEPOLLINGSTATUS 
     WHERE TABLENAME = 'TBLHOUSEHOLDS' 
    
    --MARK AS IF AN ERROR IN CASE THIS FAILS IN ANY STEP
    UPDATE TABLEPOLLINGSTATUS 
       SET STATUS = -1
     WHERE TABLENAME = 'TBLHOUSEHOLDS' 
    
    --GET ALL THE CHANGES SINCE LAST POLL
    SELECT HouseholdID
         , HouseholdName
         , HomePhone
         , Address1
         , Address2
         , City
         , StateID
         , ZipCode
         , ZipAddOnCode
         , CountyID
         , IsUnlisted
         , DoNotSendMail
         , SchoolDistrictID
         , HouseholdTypeID
         , CREATEDATE
         , LASTMODIFIEDDATE
      FROM NHASQL110.ATSCHOOL.DBO.TBLHOUSEHOLDS TBLHOUSEHOLDS
     WHERE ((LASTMODIFIEDDATE IS NULL) AND (CREATEDATE >= @LASTRUNTIME))
        OR ((LASTMODIFIEDDATE IS NOT NULL) AND (LASTMODIFIEDDATE >= @LASTRUNTIME))
       FOR XML AUTO, XMLDATA
    
    
    --MARK SUCCESSFULL AND UPDATE TIME
    UPDATE TABLEPOLLINGSTATUS 
       SET STATUS = @@ROWCOUNT
         , LASTRUNTIME = CURRENT_TIMESTAMP
     WHERE TABLENAME = 'TBLHOUSEHOLDS' 
     
     END
    Monday, March 30, 2009 6:52 PM

Answers

  • I figured it out.  It was a DTC connection issue between the SQL Server where the procedure runs and the linked server.
    • Marked as answer by JerryMac Monday, March 30, 2009 8:16 PM
    Monday, March 30, 2009 8:15 PM