Professional Web Applications Themes

Baffling stored procedure problem - Microsoft SQL / MS SQL Server

I get the following error when I run the query pasted below it. As you can see, I'm not referencing anything called 'Col1047'. I'm thinking this is a linked server issue but I'm really lost as to how to resolve it as the query isn't particularly complicated. Just as an aside. This error is the actual error returned by this query. The original query involved about sixty different columns so, just out of curiosity, I cut it down to the six columns you see below. The only difference in the error message between the 60+ fields version and this one ...

  1. #1

    Default Baffling stored procedure problem

    I get the following error when I run the query pasted below it. As you can
    see, I'm not referencing anything called 'Col1047'. I'm thinking this is a
    linked server issue but I'm really lost as to how to resolve it as the query
    isn't particularly complicated.

    Just as an aside. This error is the actual error returned by this query.
    The original query involved about sixty different columns so, just out of
    curiosity, I cut it down to the six columns you see below. The only
    difference in the error message between the 60+ fields version and this one
    is the Invalid column "name" referenced is different. Instead of 'Col1047'
    it might be 'Col1084' or some other value that varies depending on how many
    columns are referenced.

    Server: Msg 207, Level 16, State 3, Line 15
    Invalid column name 'Col1047'.
    Server: Msg 8180, Level 16, State 1, Line 15
    Statement(s) could not be prepared.

    Declare LoadDate datetime,
    UserName varchar(20)

    Set LoadDate = GetDate()
    Set UserName = 'Initial Load'


    -----------------------------------------------
    -- Load tblAudioSvcProfile
    -----------------------------------------------
    select 'Load tblAudioSvcProfile --'

    Set IDENTITY_INSERT tblAudioSvcProfile on

    Insert into tblAudioSvcProfile
    (
    fldAudioServiceProfileID,
    fldGroupID,
    fldCreateUsername,
    fldCreateDate,
    fldModUsername ,
    fldDateModified
    )
    Select
    c.fldCompanyID,
    n.fldGroupID,
    fldCreateUsername = UserName,
    fldCreateDate = LoadDate,
    fldModUsername = UserName,
    fldDateModified = LoadDate
    From NSSQL01.Carman2000.dbo.tblCompanies c
    Left Join NSSQL01.Carman2000.dbo.tblAccounts a on a.fldCompanyID =
    c.fldCompanyID
    Left join NSSQL01.Carman2000.dbo.tblSubscribers s on s.fldAccountID =
    a.fldAccountID
    Left Join tblNumberGroupProfiles n on n.fldBridgeGroupID =
    s.fldBridgeGroupID
    Where (a.fldAccountID = (Select Max(fldAccountID) From
    NSSQL01.Carman2000.dbo.tblAccounts Where fldCompanyID = c.fldCompanyID) or
    a.fldAccountID is null)
    and (s.fldSubscriberID = (Select Max(fldSubscriberID) from
    NSSQL01.Carman2000.dbo.tblSubscribers where fldAccountID = a.fldAccountID)
    or s.fldSubscriberID is null)

    Set IDENTITY_INSERT tblAudioSvcProfile off




    Bob Guest

  2. #2

    Default Re: Baffling stored procedure problem

    Bob,

    Without the schema, it's hard to say how you would go about fixing this.
    Please provide ddl so we can try to run it in qa. We might be able to
    suggest something then.

    --
    -oj
    RAC v2.2 & QALite!
    http://www.rac4sql.net



    "Bob Frasca" <com> wrote in message
    news:%phx.gbl... 
    can 

    query 
    one 
    'Col1047' 
    many 


    oj Guest

  3. #3

    Default Re: Baffling stored procedure problem

    Bob,

    Two guesses

    1. You have an insert trigger on tblAudioSvcProfile that
    refers to the invalid columns

    2. A table in your query is actually a view, which refers to
    the invalid columns

    -- Steve Kass
    -- Drew University
    -- Ref: 5B61E6F6-0254-4CB0-BBAA-5587DCBB87C5

    Bob Frasca wrote:
     

    Steve Guest

  4. #4

    Default Re: Baffling stored procedure problem

    When I put the three tables (Companies, Accounts, and Subscribers) that were
    being referenced through the linked server on the local server and refer to
    them with the "dbName..tblName" syntax instead of the fully qualified
    pathname the query works fine.

    I'm guessing that the query optimizer got confused. I also don't have time
    to mess with this any longer so I'll just have to rule out using linked
    servers.



    "oj" <com> wrote in message
    news:phx.gbl... 
    > can [/ref]
    is 
    > query [/ref]
    query. [/ref]
    of 
    > one 
    > 'Col1047' 
    > many [/ref]
    or [/ref]
    a.fldAccountID) 
    >
    >[/ref]


    Bob Guest

Similar Threads

  1. Problem in Stored procedure
    By Rajan_cfm in forum Coldfusion Database Access
    Replies: 4
    Last Post: March 9th, 09:30 PM
  2. stored procedure problem
    By Leon Shaw in forum Dreamweaver AppDev
    Replies: 42
    Last Post: November 7th, 05:13 PM
  3. Stored procedure problem
    By ?? in forum Coldfusion - Advanced Techniques
    Replies: 11
    Last Post: November 2nd, 07:17 PM
  4. ADO/Stored Procedure Problem
    By James Arnall in forum ASP Database
    Replies: 9
    Last Post: August 13th, 08:37 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139