Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2002
    Posts
    2

    Unanswered: Leftjoin query error 107 after installing SP3 and setting permissions.

    After installing SQL Server SP3 and applying security permissions to the \Winnt, \Winnt\System, and \Winnt\System32 directories, we not get an error when running queries that contain LEFTJOIN. The error number is 107. Does anyone have a clue as to why we cannot run these queries now?

  2. #2
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    Can you post some sample code that used to work but doesn't now.
    Which service pack did you have before?
    A lot of changes were made to sp2 (to allow sql server to work consistently).

  3. #3
    Join Date
    Feb 2002
    Posts
    2

    Sure, here's the queries...

    INSERT INTO [Active WIP] ( Product, [Part Numb], ECO, [EC Hld], Tst, Dbug, Rwrk, OBAx, Hold, Relse, [MRB Recv], MRBx, [Fnl Inspec], FA, [Eng Hld], Vndr, [Ty Vnd], [Snd to Vnd], [Rtrn frm Vnd], WIP, Wrhs, Pack, Split, Total )
    SELECT [Product Line].[Product Line] AS Product, [Part Master].[Assembly name] AS [Part Numb], Sum(IIf([Next Operation]="ECO/Upgrade",1,0)) AS ECO, Sum(IIf([Next Operation]="EC HOLD",1,0)) AS [EC Hld], Sum(IIf([Next Operation]="Test",1,0)) AS Tst, Sum(IIf([Next Operation]="Debug",1,0)) AS Dbug, Sum(IIf([Next Operation]="Rework",1,0)) AS Rwrk, Sum(IIf([Next Operation]="OBA",1,0)) AS OBAx, Sum(IIf([Next Operation]="Part Hold",1,0)) AS Hold, Sum(IIf([Next Operation]="Part Release",1,0)) AS Relse, Sum(IIf([Next Operation]="MRB Receiving",1,0)) AS [MRB Recv], Sum(IIf([Next Operation]="MRB",1,0)) AS MRBx, Sum(IIf([Next Operation]="Final Inspection",1,0)) AS [Fnl Inspec], Sum(IIf([Next Operation]="Eng FA",1,0)) AS FA, Sum(IIf([Next Operation]="Eng HOLD",1,0)) AS [Eng Hld], Sum(IIf([Next Operation]="Vendor",1,0)) AS Vndr, Sum(IIf([Next Operation]="Ty Vend",1,0)) AS [Ty Vnd], Sum(IIf([Next Operation]="Send to Vendor",1,0)) AS [Snd to Vnd], Sum(IIf([Next Operation]="Return from Vendor",1,0)) AS [Rtrn frm Vnd], Sum(IIf([Next Operation]="WIP Receiving",1,0)) AS WIP, Sum(IIf([Next Operation]="Warehouse",1,0)) AS Wrhs, Sum(IIf([Next Operation]="VMI & Pack",1,0)) AS Pack, Sum(IIf([Next Operation]="Split Station",1,0)) AS Split, [Wrhs]+[Tst]+[Rwrk]+[OBAx]+[Hold]+[Relse]+[MRBx]+[MRB Recv]+[Pack]+[FA]+[Eng Hld]+[Dbug]+[Fnl Inspec]+[Vndr]+[WIP]+[Split]+[ECO]+[Ty Vnd]+[EC Hld]+[Rtrn frm Vnd]+[Snd to Vnd] AS Total
    FROM ([Product Line] INNER JOIN [Part Master] ON [Product Line].ID = [Part Master].[Product Line]) LEFT JOIN [qry_Special Dell Act1] ON [Part Master].[Assembly name] = [qry_Special Dell Act1].[Assembly Name]
    GROUP BY [Product Line].[Product Line], [Part Master].[Assembly name]
    ORDER BY [Product Line].[Product Line], [Part Master].[Assembly name];


    and


    SELECT [Master Table].[Assembly Name], [Master Table].[Serial Number]
    FROM ([Master Table] INNER JOIN [dbo_Test History] ON [Master Table].ID = [dbo_Test History].[Master ID]) INNER JOIN [dbo_Test Station] ON [dbo_Test History].[Test Station] = [dbo_Test Station].ID
    WHERE ((([dbo_Test History].[Test Date]) Between [Start Date] And [End Date]) AND (([dbo_Test Station].[Test Station])="Test"));

    INSERT INTO [Family Test Data] ( [Family Name], [Part Number], Total )
    SELECT [dbo_Part Master].[Customer part Number] AS [Family Name], [dbo_Part Master].[Assembly name] AS [Part Number], Count([qry_Family Test Data1].[Serial Number]) AS Total
    FROM [dbo_Part Master] LEFT JOIN [qry_Family Test Data1] ON [dbo_Part Master].[Assembly name] = [qry_Family Test Data1].[Assembly Name]
    GROUP BY [dbo_Part Master].[Customer part Number], [dbo_Part Master].[Assembly name]
    ORDER BY [dbo_Part Master].[Customer part Number], [dbo_Part Master].[Assembly name];

  4. #4
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    Your last query seems to come down to

    create table #a (i int, j int)
    create table #b (j int, k int)

    SELECT #a.i AS [Family Name],
    #a.j AS [Part Number],
    Count(#b.k) AS Total
    FROM #a LEFT JOIN #b ON #a.j = #b.j
    GROUP BY #a.i, #a.j
    ORDER BY #a.i, #a.j

    which should work - can you try it.

    Can you verify that the 107 you are getting is

    The column prefix '%.*ls' does not match with a table name or alias name used in the query

    This does turn up sometimes if you're not consistent about the table owner - i.e. reference the table as dbo.a and just a in the same query.

Posting Permissions

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