Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2003
    Posts
    27

    Unanswered: Multi-part identifier could not be bound error in 2005

    Hi,

    We moved our stored procedure from sql 2000 to sql 2005 and we're getting few weird errors:

    Msg 4104. multi-part identifier /table.column/ could not be bound.

    Do we have to change anything in the stored procedure in order to make it work for sql 2005?

    Errors point to lines 25 and 68:
    25: IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GC_Contacts_Table]') AND type in (N'U'))
    68: ELSE

    Below is the code. Thanks in advance.

    set ANSI_NULLS ON
    set QUOTED_IDENTIFIER ON

    GO
    ALTER PROCEDURE [dbo].[sp_refresh_GC_Contacts]
    AS
    DECLARE

    @dropSQL varchar(2000)

    BEGIN
    SET NOCOUNT ON;

    --SET IDENTITY_INSERT GC_Contacts_Table ON
    -- drop the fulltext index

    IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[GC_Contacts]') AND name = N'pk_gc_contacts')
    DROP FULLTEXT INDEX ON [dbo].[GC_Contacts]

    -- drop the unique index
    IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[GC_Contacts]') AND name = N'pk_gc_contacts')
    DROP INDEX [pk_gc_contacts] ON [dbo].[GC_Contacts] WITH ( ONLINE = OFF )

    -- If table exists truncate it
    IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GC_Contacts_Table]') AND type in (N'U'))
    BEGIN
    TRUNCATE TABLE [dbo].[GC_Contacts_Table]
    INSERT INTO [dbo].[GC_Contacts_Table] --insert sql next line
    SELECT
    Title,
    FirstName AS First_Name,
    MiddleName AS Middle_Name,
    LastName AS Last_Name,
    Suffix,
    Company,
    JobTitle AS Job_Title, Email,
    PrimaryPhoneNumber AS Primary_Phone_Number,
    PrimaryAddress1 AS Primary_Address_1,
    PrimaryAddress2 AS Primary_Address_2,
    PrimaryAddress3 AS Primary_Address_3,
    PrimaryCity AS Primary_City,
    PrimaryState AS Primary_State,
    PrimaryZip AS Primary_Zip,
    PrimaryCountry AS CPrimary_ountry,
    Notes,
    Alias,
    FullName AS Full_Name,
    dbo.Addresses.Type AS AddressType,
    dbo.Addresses.Address1 AS Address1,
    dbo.Addresses.Address2 AS Address2,
    dbo.Addresses.Address3 AS Address3, dbo.Addresses.City AS City, dbo.Addresses.State AS State,
    dbo.Addresses.Zip as Zipcode, dbo.Addresses.Country AS Country, dbo.Addresses.PhoneNumber AS PhoneNumber,
    dbo.Addresses.FaxNumber AS FaxNumber,
    SubAward_Only =
    CASE SubAwardOnly
    WHEN 0 THEN 'No'
    WHEN 1 THEN 'Yes'
    END,

    dbo.ContactsSTUDF.*

    -- IDENTITY(int, 1,1) AS GC_Contact_ID
    -- INTO dbo.GC_Contacts_Table

    FROM dbo.Contacts
    LEFT OUTER JOIN dbo.ContactAddresses ON dboContacts.ID = dboContactAddresses.ContactID
    LEFT OUTER JOIN dbo.Addresses ON dbo.Addresses.ID = dboContactAddresses.AddressID
    LEFT OUTER JOIN dbo.ContactsSTUDF ON dbo.Contacts.ID = dbo.ContactsSTUDF.EntityID
    END
    ELSE
    BEGIN

    -- create the table from the query
    SELECT
    Title,
    FirstName AS First_Name,
    MiddleName AS Middle_Name,
    LastName AS Last_Name,
    Suffix,
    Company,
    JobTitle AS Job_Title, Email,
    PrimaryPhoneNumber AS Primary_Phone_Number,
    PrimaryAddress1 AS Primary_Address_1,
    PrimaryAddress2 AS Primary_Address_2,
    PrimaryAddress3 AS Primary_Address_3,
    PrimaryCity AS Primary_City,
    PrimaryState AS Primary_State,
    PrimaryZip AS Primary_Zip,
    PrimaryCountry AS CPrimary_ountry,
    Notes,
    Alias,
    FullName AS Full_Name,
    dbo.Addresses.Type AS AddressType,
    dbo.Addresses.Address2 AS Address2,
    dbo.Addresses.Address3 AS Address3, dbo.Addresses.City AS City, dbo.Addresses.State AS State,
    dbo.Addresses.Zip as Zipcode, dbo.Addresses.Country AS Country, dbo.Addresses.PhoneNumber AS PhoneNumber,
    dbo.Addresses.FaxNumber AS FaxNumber,
    SubAward_Only =
    CASE SubAwardOnly
    WHEN 0 THEN 'No'
    WHEN 1 THEN 'Yes'
    END,

    dbo.ContactsSTUDF.*,
    IDENTITY(int, 1,1) AS GC_Contact_ID
    INTO dbo.GC_Contacts_Table
    FROM dbo.Contacts
    LEFT OUTER JOIN dbo.ContactAddresses ON dboContacts.ID = dboContactAddresses.ContactID
    LEFT OUTER JOIN dbo.Addresses ON dbo.Addresses.ID = dboContactAddresses.AddressID
    LEFT OUTER JOIN dbo.ContactsSTUDF ON dbo.Contacts.ID = dbo.ContactsSTUDF.EntityID
    END

    SET IDENTITY_INSERT GC_Contacts_table OFF
    SET ARITHABORT ON
    SET CONCAT_NULL_YIELDS_NULL ON
    SET QUOTED_IDENTIFIER ON
    SET ANSI_NULLS ON
    SET ANSI_PADDING ON
    SET ANSI_WARNINGS ON
    SET NUMERIC_ROUNDABORT OFF

    /****** Object: Index [pk_gc_contacts] Script Date: 10/11/2007 15:34:28 ******/

    CREATE UNIQUE CLUSTERED INDEX [pk_gc_contacts] ON [dbo].[GC_Contacts]
    (
    [GC_contact_id] ASC
    )
    WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

    -- add the fulltext index

    CREATE FULLTEXT INDEX ON GC_Contacts
    ([Title]
    ,[First_Name]
    ,[Middle_Name]
    ,[Last_Name]
    ,[Suffix]
    ,[Company]
    ,[Job_Title]
    ,[Email]
    ,[Primary_Phone_Number]
    ,[Primary_Address_1]
    ,[Primary_Address_2]
    ,[Primary_Address_3]
    ,[Primary_City]
    ,[Primary_State]
    ,[CPrimary_ountry]
    ,[Notes]
    ,[Alias]
    ,[Full_Name]
    ,[AddressType]
    ,[Address1]
    ,[Address2]
    ,[Address3]
    ,[City]
    ,[State]
    ,[Country]
    ,[PhoneNumber]
    ,[FaxNumber]
    ,[SubAward_Only]
    )

    KEY INDEX pk_gc_contacts ON GCInquiryCatalog

    WITH CHANGE_TRACKING AUTO

    END
    Last edited by awegrzyn; 03-11-08 at 13:41.

  2. #2
    Join Date
    Feb 2005
    Location
    Utah
    Posts
    73
    Usually when I get this error it means that I'm referencing a table name alias that I haven't created. I'm not sure why you're getting that error here other than it probably has something to do with using the fully qualified table name (i.e., dbo.ContactAddress) in the select list. As a matter of course, it is a best practice to alias all of your table names and then include that alias as a prefix to all columns that you are selecting. It helps others read your code, and would probably fix this problem.
    Dandy
    Aspiring Database Dwarf

  3. #3
    Join Date
    Mar 2003
    Posts
    27
    Thanks,

    I actually realized there is an error with the naming. A dot is missing between dbo and table name. It allows me to compile and it allows me to execute at level 80 but not at level 90.

  4. #4
    Join Date
    Feb 2005
    Location
    Utah
    Posts
    73
    <sarcasm> Dang periods! Wouldn't we all be better off without them? </sarcasm>
    Dandy
    Aspiring Database Dwarf

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •