Avatar of matrix_aash
matrix_aashFlag for United Kingdom of Great Britain and Northern Ireland asked on

T-SQL On View

Hi All,

I have the below query on server1 database1 and I need to run the query on server2  database2.
But I am getting the below error

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "dbo.SalesOrder.OpportunityId" could not be bound.
on most of the columns in my select statement.

Basically I need to run a cross server cross database query from server1 database1 to server2 database2.

Hope this makes sense.

Thanks in advance.
SELECT     AllConsultancyFiles.FileNumber, AllConsultancyFiles.QuoteNumber, AllConsultancyFiles.OrderNumber, AllConsultancyFiles.DateCreated, 
                      AllConsultancyFiles.FileCategory, AllConsultancyFiles.Client, AllConsultancyFiles.QuoteTotalGBP, AllConsultancyFiles.DoesQuoteIncludeExpenses, 
                      AllConsultancyFiles.Consultant, AllConsultancyFiles.Paid, AllConsultancyFiles.AgreementSigned, AllConsultancyFiles.FeesAndExpensesLessTaxGBP, 
                      AllConsultancyFiles.Currency, AllConsultancyFiles.FeesAndExpensesLessTaxCUR, AllConsultancyFiles.ExchangeRate, 
                      AllConsultancyFiles.AircraftType, AllConsultancyFiles.AssignmentTitle, AllConsultancyFiles.ClientCategory, AllConsultancyFiles.ClientCountry, 
                      AllConsultancyFiles.ClientArea, AllConsultancyFiles.DateFileClosed, AllConsultancyFiles.DateBilled, AllConsultancyFiles.AscendInvoiceNumber, 
                      AllConsultancyFiles.ActualHours AS AscendActualHours, AllConsultancyFiles.AirclaimsFileNumber, AllConsultancyFiles.OwnerOfFile, 
                      AllConsultancyFiles.SalesOrderId, SalesOrderDetail.New_SterlingSubTotal AS ExpensesOnlyGBP, 
                      SalesOrderDetail.New_SterlingExtendedAmount AS ExpensesPlusTaxGBP, 
                      AllConsultancyFiles.OrderTotalGBP - AllConsultancyFiles.OrderTaxGBP - ISNULL(SalesOrderDetail.New_SterlingSubTotal, 0) 
                      AS AscendAndAirclaimsFeesOnlyGBP, 
                      AllConsultancyFiles.OrderTotalGBP - AllConsultancyFiles.OrderTaxGBP - ISNULL(SalesOrderDetail.New_SterlingSubTotal, 0) 
                      - ISNULL(AllConsultancyFiles.AirclaimsFeesGBP, 0) AS AscendFeesOnlyGBP, AllConsultancyFiles.AirclaimsFeesGBP AS AirclaimsFeesOnlyGBP, 
                      (AllConsultancyFiles.OrderTotalGBP - AllConsultancyFiles.OrderTaxGBP - ISNULL(SalesOrderDetail.New_SterlingSubTotal, 0) 
                      - ISNULL(AllConsultancyFiles.AirclaimsFeesGBP, 0)) / AllConsultancyFiles.ActualHours AS AscendRealisedHourRateGBP
FROM         (SELECT     dbo.New_ConsultancyFile.New_name AS FileNumber, dbo.SalesOrder.New_SterlingTotalAmount AS OrderTotalGBP, 
                                              dbo.SalesOrder.New_SterlingTotalTax AS OrderTaxGBP, dbo.SalesOrder.New_SterlingDetailAmount AS OrderBeforeTaxGBP, 
                                              dbo.SalesOrder.OrderNumber, dbo.New_ConsultancyFile.New_QuoteNumber AS QuoteNumber, dbo.SalesOrder.CreatedOn AS DateCreated, 
                                              ISNULL(dbo.SalesOrder.AccountIdName, dbo.Contact.AccountIdName) AS Client, 
                                              dbo.Opportunity.New_sterlingestimatedvalue AS QuoteTotalGBP, dbo.Opportunity.New_IncludesExpenses AS DoesQuoteIncludeExpenses, 
                                              CASE Airclaims_Limited_MSCRM.dbo.New_ConsultancyFile.New_ConsultantOwner WHEN 1 THEN 'Andy Woodruff' WHEN 2 THEN 'Anthony Brooks'
                                               WHEN 3 THEN 'Chris Seymour' WHEN 4 THEN 'Derek Wong' WHEN 5 THEN 'Eddy Pieniazek' WHEN 6 THEN 'Gary Crichlow' WHEN 7 THEN
                                               'Gehan Talwatte' WHEN 8 THEN 'George Dimitroff' WHEN 9 THEN 'Kam Rehal' WHEN 10 THEN 'Laura Davis' WHEN 11 THEN 'Les Weal' WHEN
                                               12 THEN 'Louise Gow' WHEN 13 THEN 'Mark Pritchard' WHEN 14 THEN 'Nick Payne' WHEN 15 THEN 'Olga Razzhivina' WHEN 16 THEN 'Pascal Shakarjian'
                                               WHEN 17 THEN 'Paul Hayes' WHEN 18 THEN 'Paul Scullin' WHEN 19 THEN 'Peter Morris' WHEN 20 THEN 'Phil Davies' WHEN 21 THEN 'Rob Green'
                                               WHEN 22 THEN 'Sandeep Saujani' WHEN 23 THEN 'Shamin Mughal' WHEN 24 THEN 'Vince Cloutt' WHEN 25 THEN 'Miran Bastajian' WHEN
                                               26 THEN 'Kelly Winters' WHEN 27 THEN 'Annakarin Bjorklund' WHEN 28 THEN 'Diane Tan van Zwanenburg' WHEN 29 THEN 'Lukasz Pawlowski'
                                               WHEN 30 THEN 'Rob Taylor' WHEN 31 THEN 'Yvonne Moeller' WHEN 32 THEN 'Lorna Dyos' WHEN 33 THEN 'Radka Rana' WHEN 34 THEN
                                               'Susan Bailie' WHEN 35 THEN 'Lance Hooks' WHEN 36 THEN 'Adam Samways' WHEN 37 THEN 'Faisal Majid' WHEN 38 THEN 'Genevieve Guermont'
                                               WHEN 39 THEN 'Matt Colling' WHEN 40 THEN 'Jake Reppert' WHEN 41 THEN 'Peter Bull' END AS Consultant, 
                                              dbo.New_ConsultancyFile.New_Paid AS Paid, dbo.Account.New_DateAgreementSigned AS AgreementSigned, 
                                              dbo.SalesOrder.New_SterlingTotalAmount - dbo.SalesOrder.New_SterlingTotalTax AS FeesAndExpensesLessTaxGBP, 
                                              CASE Airclaims_Limited_MSCRM.dbo.salesorder.CFPCurrency WHEN 4 THEN 'EUR' WHEN 2 THEN 'GBP' WHEN 3 THEN 'USD' ELSE 'Unknown'
                                               END AS Currency, dbo.SalesOrder.TotalAmount - dbo.SalesOrder.TotalTax AS FeesAndExpensesLessTaxCUR, 
                                              dbo.SalesOrder.New_ExchangeRate AS ExchangeRate, ISNULL(dbo.New_ConsultancyFile.New_ACPortfolioName + ' ', '') 
                                              + ISNULL(CAST(dbo.New_ConsultancyFile.New_ACPortfolioNumber AS varchar) + ' aircraft. ', '') 
                                              + ISNULL(dbo.New_ConsultancyFile.New_AC1Manufacturer + ' ', '') + ISNULL(dbo.New_ConsultancyFile.New_AC1Type + ' ', '') 
                                              + ISNULL('x' + CAST(dbo.New_ConsultancyFile.New_AC1NumberOf AS varchar) + ' ', '') 
                                              + ISNULL(dbo.New_ConsultancyFile.New_AC1MSN + ' ', '') + ISNULL(dbo.New_ConsultancyFile.New_AC1Registration + ' ', '') 
                                              + ISNULL('; ' + dbo.New_ConsultancyFile.New_AC2Manufacturer + ' ', '') + ISNULL(dbo.New_ConsultancyFile.New_AC2Type + ' ', '') 
                                              + ISNULL('x' + CAST(dbo.New_ConsultancyFile.New_AC2NumberOf AS varchar) + ' ', '') 
                                              + ISNULL(dbo.New_ConsultancyFile.New_AC2MSN + ' ', '') + ISNULL(dbo.New_ConsultancyFile.New_AC2Registration + '; ', '') 
                                              + ISNULL('; ' + dbo.New_ConsultancyFile.New_AC3Manufacturer + ' ', '') + ISNULL(dbo.New_ConsultancyFile.New_AC3Type + ' ', '') 
                                              + ISNULL('x' + CAST(dbo.New_ConsultancyFile.New_AC3NumberOf AS varchar) + ' ', '') 
                                              + ISNULL(dbo.New_ConsultancyFile.New_AC3MSN + ' ', '') + ISNULL(dbo.New_ConsultancyFile.New_AC3Registration + '; ', '') 
                                              + ISNULL('; ' + dbo.New_ConsultancyFile.New_AC4Manufacturer + ' ', '') + ISNULL(dbo.New_ConsultancyFile.New_AC4Type + ' ', '') 
                                              + ISNULL('x' + CAST(dbo.New_ConsultancyFile.New_AC4NumberOf AS varchar) + ' ', '') 
                                              + ISNULL(dbo.New_ConsultancyFile.New_AC4MSN + ' ', '') + ISNULL(dbo.New_ConsultancyFile.New_AC4Registration + '; ', '') 
                                              + ISNULL('; ' + dbo.New_ConsultancyFile.New_AC5Manufacturer + ' ', '') + ISNULL(dbo.New_ConsultancyFile.New_AC5Type + ' ', '') 
                                              + ISNULL('x' + CAST(dbo.New_ConsultancyFile.New_AC5NumberOf AS varchar) + ' ', '') 
                                              + ISNULL(dbo.New_ConsultancyFile.New_AC5MSN + ' ', '') + ISNULL(dbo.New_ConsultancyFile.New_AC5Registration + '; ', '') 
                                              AS AircraftType, dbo.SalesOrder.Name AS AssignmentTitle, 
                                              CASE Airclaims_Limited_MSCRM.dbo.Account.IndustryCode WHEN 1 THEN 'Airport' WHEN 2 THEN 'Asset Finance' WHEN 3 THEN 'Consultancy'
                                               WHEN 4 THEN 'Educational' WHEN 5 THEN 'Govt Or Regulator' WHEN 6 THEN 'Insurance Broker' WHEN 7 THEN 'Insurance Reinsurer' WHEN
                                               8 THEN 'Insurance Unbderwriter' WHEN 9 THEN 'Manufacturer (Hull)' WHEN 10 THEN 'Manufacturer (Components)' WHEN 11 THEN 'Media Publisher'
                                               WHEN 12 THEN 'Military' WHEN 13 THEN 'Modifications' WHEN 14 THEN 'MRO' WHEN 15 THEN 'Operator (Major)' WHEN 16 THEN 'Operator (Other)'
                                               WHEN 17 THEN 'Operator (Regional)' WHEN 18 THEN 'Space Operator' WHEN 19 THEN 'Space Hull Manufacturer' WHEN 20 THEN 'Space Components'
                                               WHEN 21 THEN 'Trade Association' WHEN 22 THEN 'Travel Services inc Accommodation' WHEN 24 THEN 'Other' WHEN 34 THEN 'Asset Leasing'
                                               WHEN 35 THEN 'Space Launcher' WHEN 37 THEN 'Asset Investor (Other)' WHEN 38 THEN 'Insurance Adjuster' WHEN 39 THEN 'Legal' WHEN
                                               40 THEN 'Arranger (Agent)' WHEN 41 THEN 'Construction' ELSE NULL END AS ClientCategory, 
                                              dbo.Account.Address1_Country AS ClientCountry, 
                                              CASE Airclaims_Limited_MSCRM.dbo.account.cfpcontinentcode WHEN 5 THEN 'UK' WHEN 6 THEN 'Europe' WHEN 14 THEN 'Europe' WHEN
                                               2 THEN 'Americas' WHEN 3 THEN 'Americas' WHEN 4 THEN 'Americas' WHEN 7 THEN 'Africa' WHEN 8 THEN 'Middle East' WHEN 9 THEN 'Asia'
                                               WHEN 10 THEN 'Asia' WHEN 15 THEN 'Asia' WHEN 11 THEN 'Asia' WHEN 12 THEN 'Asia' WHEN 13 THEN 'Asia' ELSE NULL 
                                              END AS ClientArea, dbo.New_ConsultancyFile.New_DateFileClosed AS DateFileClosed, 
                                              dbo.New_ConsultancyFile.New_DateBilled AS DateBilled, dbo.SalesOrder.New_OurReference AS AscendInvoiceNumber, 
                                              dbo.New_ConsultancyFile.New_ConsultancyHours AS ActualHours, 
                                              dbo.New_ConsultancyFile.New_AirclaimsFileNumber AS AirclaimsFileNumber, 
                                              dbo.SalesOrder.New_FeesToAirclaimsGBP AS AirclaimsFeesGBP, 
                                              CASE Airclaims_Limited_MSCRM.dbo.New_ConsultancyFile.New_OwnerofClient WHEN 1 THEN 'Ascend' WHEN 2 THEN 'Airclaims' ELSE NULL
                                               END AS OwnerOfFile, dbo.SalesOrder.SalesOrderId, 
                                              CASE Airclaims_Limited_MSCRM.dbo.New_ConsultancyFile.New_FileCategory WHEN 1 THEN 'C' WHEN 2 THEN 'A' WHEN 3 THEN 'D' ELSE
                                               NULL END AS FileCategory
                       FROM          dbo.SalesOrder LEFT OUTER JOIN
                                              dbo.Opportunity ON dbo.SalesOrder.OpportunityId = dbo.Opportunity.OpportunityId LEFT OUTER JOIN
                                              dbo.Account ON dbo.SalesOrder.AccountId = dbo.Account.AccountId LEFT OUTER JOIN
                                              dbo.New_ConsultancyFile ON dbo.SalesOrder.SalesOrderId = dbo.New_ConsultancyFile.New_ConsultancyOrderId LEFT OUTER JOIN
                                              dbo.Contact ON dbo.SalesOrder.ContactId = dbo.Contact.ContactId
                       WHERE      (NOT (dbo.New_ConsultancyFile.New_name IS NULL))) AS AllConsultancyFiles LEFT OUTER JOIN
                          (SELECT     New_ConsultancyFile.New_name, SalesOrder.SalesOrderId, SalesOrder.OrderNumber, CAST(ISNULL(dbo.Product.New_IsExpense, 0) 
                                                   AS INT) AS IsExpense, dbo.SalesOrderDetail.SalesOrderDetailId
                            FROM          dbo.Product INNER JOIN
                                                   dbo.SalesOrderDetail ON dbo.Product.ProductId = dbo.SalesOrderDetail.ProductId RIGHT OUTER JOIN
                                                   dbo.SalesOrder AS SalesOrder ON dbo.SalesOrderDetail.SalesOrderId = SalesOrder.SalesOrderId LEFT OUTER JOIN
                                                   dbo.New_ConsultancyFile AS New_ConsultancyFile ON 
                                                   SalesOrder.SalesOrderId = New_ConsultancyFile.New_ConsultancyOrderId
                            WHERE      (NOT (New_ConsultancyFile.New_name IS NULL)) AND (CAST(ISNULL(dbo.Product.New_IsExpense, 0) AS INT) = 1)) 
                      AS ExpenseSalesOrderDetails ON AllConsultancyFiles.SalesOrderId = ExpenseSalesOrderDetails.SalesOrderId LEFT OUTER JOIN
                      dbo.SalesOrderDetail AS SalesOrderDetail ON ExpenseSalesOrderDetails.SalesOrderDetailId = SalesOrderDetail.SalesOrderDetailId

Open in new window

Microsoft SQL Server

Avatar of undefined
Last Comment
reb73

8/22/2022 - Mon
chapmandew

I would think it should be the other way around.  instead of:

dbo.SalesOrder.OpportunityId

should be

SalesOrder.dbo.OpportunityId
ASKER
matrix_aash

Thats is not the problem.

I am getting the below error to be very specific.

So the error is with each column.

I tried with what you said and did not work

My server1 is 2005 and my server2 from where I need to run the above query is SQL 2000.

Thanks.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "SalesOrder.dbo.OpportunityId" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "dbo.Opportunity.OpportunityId" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "dbo.SalesOrder.AccountId" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "dbo.Account.AccountId" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "dbo.SalesOrder.SalesOrderId" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "dbo.New_ConsultancyFile.New_ConsultancyOrderId" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "dbo.SalesOrder.ContactId" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "dbo.Contact.ContactId" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "dbo.New_ConsultancyFile.New_name" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "dbo.New_ConsultancyFile.New_name" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "dbo.SalesOrder.New_SterlingTotalAmount" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "dbo.SalesOrder.New_SterlingTotalTax" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "dbo.SalesOrder.New_SterlingDetailAmount" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "dbo.SalesOrder.OrderNumber" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "dbo.New_ConsultancyFile.New_QuoteNumber" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "dbo.SalesOrder.CreatedOn" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "dbo.SalesOrder.AccountIdName" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "dbo.Contact.AccountIdName" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "dbo.Opportunity.New_sterlingestimatedvalue" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "dbo.Opportunity.New_IncludesExpenses" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Airclaims_Limited_MSCRM.dbo.New_ConsultancyFile.New_ConsultantOwner" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Airclaims_Limited_MSCRM.dbo.New_ConsultancyFile.New_ConsultantOwner" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Airclaims_Limited_MSCRM.dbo.New_ConsultancyFile.New_ConsultantOwner" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Airclaims_Limited_MSCRM.dbo.New_ConsultancyFile.New_ConsultantOwner" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Airclaims_Limited_MSCRM.dbo.New_ConsultancyFile.New_ConsultantOwner" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Airclaims_Limited_MSCRM.dbo.New_ConsultancyFile.New_ConsultantOwner" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Airclaims_Limited_MSCRM.dbo.New_ConsultancyFile.New_ConsultantOwner" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Airclaims_Limited_MSCRM.dbo.New_ConsultancyFile.New_ConsultantOwner" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Airclaims_Limited_MSCRM.dbo.New_ConsultancyFile.New_ConsultantOwner" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Airclaims_Limited_MSCRM.dbo.New_ConsultancyFile.New_ConsultantOwner" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Airclaims_Limited_MSCRM.dbo.New_ConsultancyFile.New_ConsultantOwner" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Airclaims_Limited_MSCRM.dbo.New_ConsultancyFile.New_ConsultantOwner" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Airclaims_Limited_MSCRM.dbo.New_ConsultancyFile.New_ConsultantOwner" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Airclaims_Limited_MSCRM.dbo.New_ConsultancyFile.New_ConsultantOwner" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Airclaims_Limited_MSCRM.dbo.New_ConsultancyFile.New_ConsultantOwner" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Airclaims_Limited_MSCRM.dbo.New_ConsultancyFile.New_ConsultantOwner" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Airclaims_Limited_MSCRM.dbo.New_ConsultancyFile.New_ConsultantOwner" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Airclaims_Limited_MSCRM.dbo.New_ConsultancyFile.New_ConsultantOwner" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Airclaims_Limited_MSCRM.dbo.New_ConsultancyFile.New_ConsultantOwner" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Airclaims_Limited_MSCRM.dbo.New_ConsultancyFile.New_ConsultantOwner" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Airclaims_Limited_MSCRM.dbo.New_ConsultancyFile.New_ConsultantOwner" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Airclaims_Limited_MSCRM.dbo.New_ConsultancyFile.New_ConsultantOwner" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Airclaims_Limited_MSCRM.dbo.New_ConsultancyFile.New_ConsultantOwner" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Airclaims_Limited_MSCRM.dbo.New_ConsultancyFile.New_ConsultantOwner" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Airclaims_Limited_MSCRM.dbo.New_ConsultancyFile.New_ConsultantOwner" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Airclaims_Limited_MSCRM.dbo.New_ConsultancyFile.New_ConsultantOwner" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Airclaims_Limited_MSCRM.dbo.New_ConsultancyFile.New_ConsultantOwner" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Airclaims_Limited_MSCRM.dbo.New_ConsultancyFile.New_ConsultantOwner" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Airclaims_Limited_MSCRM.dbo.New_ConsultancyFile.New_ConsultantOwner" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Airclaims_Limited_MSCRM.dbo.New_ConsultancyFile.New_ConsultantOwner" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Airclaims_Limited_MSCRM.dbo.New_ConsultancyFile.New_ConsultantOwner" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Airclaims_Limited_MSCRM.dbo.New_ConsultancyFile.New_ConsultantOwner" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Airclaims_Limited_MSCRM.dbo.New_ConsultancyFile.New_ConsultantOwner" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Airclaims_Limited_MSCRM.dbo.New_ConsultancyFile.New_ConsultantOwner" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Airclaims_Limited_MSCRM.dbo.New_ConsultancyFile.New_ConsultantOwner" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Airclaims_Limited_MSCRM.dbo.New_ConsultancyFile.New_ConsultantOwner" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Airclaims_Limited_MSCRM.dbo.New_ConsultancyFile.New_ConsultantOwner" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Airclaims_Limited_MSCRM.dbo.New_ConsultancyFile.New_ConsultantOwner" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Airclaims_Limited_MSCRM.dbo.New_ConsultancyFile.New_ConsultantOwner" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Airclaims_Limited_MSCRM.dbo.New_ConsultancyFile.New_ConsultantOwner" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Airclaims_Limited_MSCRM.dbo.New_ConsultancyFile.New_ConsultantOwner" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "dbo.New_ConsultancyFile.New_Paid" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "dbo.Account.New_DateAgreementSigned" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "dbo.SalesOrder.New_SterlingTotalAmount" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "dbo.SalesOrder.New_SterlingTotalTax" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Airclaims_Limited_MSCRM.dbo.salesorder.CFPCurrency" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Airclaims_Limited_MSCRM.dbo.salesorder.CFPCurrency" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Airclaims_Limited_MSCRM.dbo.salesorder.CFPCurrency" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "dbo.SalesOrder.TotalAmount" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "dbo.SalesOrder.TotalTax" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "dbo.SalesOrder.New_ExchangeRate" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "dbo.New_ConsultancyFile.New_ACPortfolioName" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "dbo.New_ConsultancyFile.New_ACPortfolioNumber" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "dbo.New_ConsultancyFile.New_AC1Manufacturer" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "dbo.New_ConsultancyFile.New_AC1Type" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "dbo.New_ConsultancyFile.New_AC1NumberOf" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "dbo.New_ConsultancyFile.New_AC1MSN" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "dbo.New_ConsultancyFile.New_AC1Registration" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "dbo.New_ConsultancyFile.New_AC2Manufacturer" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "dbo.New_ConsultancyFile.New_AC2Type" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "dbo.New_ConsultancyFile.New_AC2NumberOf" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "dbo.New_ConsultancyFile.New_AC2MSN" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "dbo.New_ConsultancyFile.New_AC2Registration" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "dbo.New_ConsultancyFile.New_AC3Manufacturer" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "dbo.New_ConsultancyFile.New_AC3Type" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "dbo.New_ConsultancyFile.New_AC3NumberOf" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "dbo.New_ConsultancyFile.New_AC3MSN" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "dbo.New_ConsultancyFile.New_AC3Registration" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "dbo.New_ConsultancyFile.New_AC4Manufacturer" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "dbo.New_ConsultancyFile.New_AC4Type" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "dbo.New_ConsultancyFile.New_AC4NumberOf" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "dbo.New_ConsultancyFile.New_AC4MSN" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "dbo.New_ConsultancyFile.New_AC4Registration" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "dbo.New_ConsultancyFile.New_AC5Manufacturer" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "dbo.New_ConsultancyFile.New_AC5Type" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "dbo.New_ConsultancyFile.New_AC5NumberOf" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "dbo.New_ConsultancyFile.New_AC5MSN" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "dbo.New_ConsultancyFile.New_AC5Registration" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "dbo.SalesOrder.Name" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Airclaims_Limited_MSCRM.dbo.Account.IndustryCode" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Airclaims_Limited_MSCRM.dbo.Account.IndustryCode" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Airclaims_Limited_MSCRM.dbo.Account.IndustryCode" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Airclaims_Limited_MSCRM.dbo.Account.IndustryCode" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Airclaims_Limited_MSCRM.dbo.Account.IndustryCode" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Airclaims_Limited_MSCRM.dbo.Account.IndustryCode" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Airclaims_Limited_MSCRM.dbo.Account.IndustryCode" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Airclaims_Limited_MSCRM.dbo.Account.IndustryCode" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Airclaims_Limited_MSCRM.dbo.Account.IndustryCode" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Airclaims_Limited_MSCRM.dbo.Account.IndustryCode" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Airclaims_Limited_MSCRM.dbo.Account.IndustryCode" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Airclaims_Limited_MSCRM.dbo.Account.IndustryCode" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Airclaims_Limited_MSCRM.dbo.Account.IndustryCode" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Airclaims_Limited_MSCRM.dbo.Account.IndustryCode" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Airclaims_Limited_MSCRM.dbo.Account.IndustryCode" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Airclaims_Limited_MSCRM.dbo.Account.IndustryCode" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Airclaims_Limited_MSCRM.dbo.Account.IndustryCode" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Airclaims_Limited_MSCRM.dbo.Account.IndustryCode" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Airclaims_Limited_MSCRM.dbo.Account.IndustryCode" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Airclaims_Limited_MSCRM.dbo.Account.IndustryCode" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Airclaims_Limited_MSCRM.dbo.Account.IndustryCode" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Airclaims_Limited_MSCRM.dbo.Account.IndustryCode" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Airclaims_Limited_MSCRM.dbo.Account.IndustryCode" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Airclaims_Limited_MSCRM.dbo.Account.IndustryCode" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Airclaims_Limited_MSCRM.dbo.Account.IndustryCode" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Airclaims_Limited_MSCRM.dbo.Account.IndustryCode" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Airclaims_Limited_MSCRM.dbo.Account.IndustryCode" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Airclaims_Limited_MSCRM.dbo.Account.IndustryCode" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Airclaims_Limited_MSCRM.dbo.Account.IndustryCode" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Airclaims_Limited_MSCRM.dbo.Account.IndustryCode" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "dbo.Account.Address1_Country" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Airclaims_Limited_MSCRM.dbo.account.cfpcontinentcode" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Airclaims_Limited_MSCRM.dbo.account.cfpcontinentcode" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Airclaims_Limited_MSCRM.dbo.account.cfpcontinentcode" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Airclaims_Limited_MSCRM.dbo.account.cfpcontinentcode" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Airclaims_Limited_MSCRM.dbo.account.cfpcontinentcode" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Airclaims_Limited_MSCRM.dbo.account.cfpcontinentcode" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Airclaims_Limited_MSCRM.dbo.account.cfpcontinentcode" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Airclaims_Limited_MSCRM.dbo.account.cfpcontinentcode" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Airclaims_Limited_MSCRM.dbo.account.cfpcontinentcode" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Airclaims_Limited_MSCRM.dbo.account.cfpcontinentcode" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Airclaims_Limited_MSCRM.dbo.account.cfpcontinentcode" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Airclaims_Limited_MSCRM.dbo.account.cfpcontinentcode" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Airclaims_Limited_MSCRM.dbo.account.cfpcontinentcode" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Airclaims_Limited_MSCRM.dbo.account.cfpcontinentcode" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "dbo.New_ConsultancyFile.New_DateFileClosed" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "dbo.New_ConsultancyFile.New_DateBilled" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "dbo.SalesOrder.New_OurReference" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "dbo.New_ConsultancyFile.New_ConsultancyHours" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "dbo.New_ConsultancyFile.New_AirclaimsFileNumber" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "dbo.SalesOrder.New_FeesToAirclaimsGBP" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Airclaims_Limited_MSCRM.dbo.New_ConsultancyFile.New_OwnerofClient" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Airclaims_Limited_MSCRM.dbo.New_ConsultancyFile.New_OwnerofClient" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "dbo.SalesOrder.SalesOrderId" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Airclaims_Limited_MSCRM.dbo.New_ConsultancyFile.New_FileCategory" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Airclaims_Limited_MSCRM.dbo.New_ConsultancyFile.New_FileCategory" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Airclaims_Limited_MSCRM.dbo.New_ConsultancyFile.New_FileCategory" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "dbo.Product.ProductId" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "dbo.SalesOrderDetail.ProductId" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "dbo.SalesOrderDetail.SalesOrderId" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "dbo.Product.New_IsExpense" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "dbo.Product.New_IsExpense" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "dbo.SalesOrderDetail.SalesOrderDetailId" could not be bound.
Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.SalesOrderDetail'.

Open in new window

chapmandew

ok...makes sense.  remove the dbo. from all of the above references...
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
ASKER
matrix_aash

Where do i need to add the reference server1 and database1 so that I can run the query on server2 database2.
reb73

Tim-> removing dbo. globally might mess up the code..

For e.g.

Airclaims_Limited_MSCRM.dbo.New_ConsultancyFile.New_FileCategory

will become

Airclaims_Limited_MSCRM.New_ConsultancyFile.New_FileCategory

which is an invalid identifier..
reb73

matrix_aash -

If you use table aliases in the script, it'll make it a lot more easier to read.. You only need to reference it once in the from or join segment..

If you are doing a cross-database query, you will need to use the owner prefix. If a table is not owned by dbo in the remote server, then you will have to know who is the owner and replace dbo with that owner, e.g. -

<RemoteServer>.<Database>.<Owner>.<TableName>

The owner is optional only when you query the local server..

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
matrix_aash

Hi Guys,

I have pasted complete new query with no alias.

This query runs fine on server1 database1

I need to run it on server2 database2.

I cannot figure out where do I need to enter the server1.database1.dbo.tablename things in the below query.

Hope this makes sense.
SELECT  AllConsultancyFiles.FileNumber,
        AllConsultancyFiles.QuoteNumber,
        AllConsultancyFiles.OrderNumber,
        AllConsultancyFiles.DateCreated,
        AllConsultancyFiles.FileCategory,
        AllConsultancyFiles.Client,
        AllConsultancyFiles.QuoteTotalGBP,
        AllConsultancyFiles.DoesQuoteIncludeExpenses,
        AllConsultancyFiles.Consultant,
        AllConsultancyFiles.Paid,
        AllConsultancyFiles.AgreementSigned,
        AllConsultancyFiles.FeesAndExpensesLessTaxGBP,
        AllConsultancyFiles.Currency,
        AllConsultancyFiles.FeesAndExpensesLessTaxCUR,
        AllConsultancyFiles.ExchangeRate,
        AllConsultancyFiles.AircraftType,
        AllConsultancyFiles.AssignmentTitle,
        AllConsultancyFiles.ClientCategory,
        AllConsultancyFiles.ClientCountry,
        AllConsultancyFiles.ClientArea,
        AllConsultancyFiles.DateFileClosed,
        AllConsultancyFiles.DateBilled,
        AllConsultancyFiles.AscendInvoiceNumber,
        AllConsultancyFiles.ActualHours AS AscendActualHours,
        AllConsultancyFiles.AirclaimsFileNumber,
        AllConsultancyFiles.OwnerOfFile,
        AllConsultancyFiles.SalesOrderId,
        SalesOrderDetail.New_SterlingSubTotal AS ExpensesOnlyGBP,
        SalesOrderDetail.New_SterlingExtendedAmount AS ExpensesPlusTaxGBP,
        AllConsultancyFiles.OrderTotalGBP - AllConsultancyFiles.OrderTaxGBP
        - ISNULL(SalesOrderDetail.New_SterlingSubTotal, 0) AS AscendAndAirclaimsFeesOnlyGBP,
        AllConsultancyFiles.OrderTotalGBP - AllConsultancyFiles.OrderTaxGBP
        - ISNULL(SalesOrderDetail.New_SterlingSubTotal, 0)
        - ISNULL(AllConsultancyFiles.AirclaimsFeesGBP, 0) AS AscendFeesOnlyGBP,
        AllConsultancyFiles.AirclaimsFeesGBP AS AirclaimsFeesOnlyGBP,
        ( AllConsultancyFiles.OrderTotalGBP - AllConsultancyFiles.OrderTaxGBP
          - ISNULL(SalesOrderDetail.New_SterlingSubTotal, 0)
          - ISNULL(AllConsultancyFiles.AirclaimsFeesGBP, 0) )
        / AllConsultancyFiles.ActualHours AS AscendRealisedHourRateGBP
FROM    ( SELECT    dbo.New_ConsultancyFile.New_name AS FileNumber,
                    dbo.SalesOrder.New_SterlingTotalAmount AS OrderTotalGBP,
                    dbo.SalesOrder.New_SterlingTotalTax AS OrderTaxGBP,
                    dbo.SalesOrder.New_SterlingDetailAmount AS OrderBeforeTaxGBP,
                    dbo.SalesOrder.OrderNumber,
                    dbo.New_ConsultancyFile.New_QuoteNumber AS QuoteNumber,
                    dbo.SalesOrder.CreatedOn AS DateCreated,
                    ISNULL(dbo.SalesOrder.AccountIdName,
                           dbo.Contact.AccountIdName) AS Client,
                    dbo.Opportunity.New_sterlingestimatedvalue AS QuoteTotalGBP,
                    dbo.Opportunity.New_IncludesExpenses AS DoesQuoteIncludeExpenses,
                    CASE Airclaims_Limited_MSCRM.dbo.New_ConsultancyFile.New_ConsultantOwner
                      WHEN 1 THEN 'Andy Woodruff'
                      WHEN 2 THEN 'Anthony Brooks'
                      WHEN 3 THEN 'Chris Seymour'
                      WHEN 4 THEN 'Derek Wong'
                      WHEN 5 THEN 'Eddy Pieniazek'
                      WHEN 6 THEN 'Gary Crichlow'
                      WHEN 7 THEN 'Gehan Talwatte'
                      WHEN 8 THEN 'George Dimitroff'
                      WHEN 9 THEN 'Kam Rehal'
                      WHEN 10 THEN 'Laura Davis'
                      WHEN 11 THEN 'Les Weal'
                      WHEN 12 THEN 'Louise Gow'
                      WHEN 13 THEN 'Mark Pritchard'
                      WHEN 14 THEN 'Nick Payne'
                      WHEN 15 THEN 'Olga Razzhivina'
                      WHEN 16 THEN 'Pascal Shakarjian'
                      WHEN 17 THEN 'Paul Hayes'
                      WHEN 18 THEN 'Paul Scullin'
                      WHEN 19 THEN 'Peter Morris'
                      WHEN 20 THEN 'Phil Davies'
                      WHEN 21 THEN 'Rob Green'
                      WHEN 22 THEN 'Sandeep Saujani'
                      WHEN 23 THEN 'Shamin Mughal'
                      WHEN 24 THEN 'Vince Cloutt'
                      WHEN 25 THEN 'Miran Bastajian'
                      WHEN 26 THEN 'Kelly Winters'
                      WHEN 27 THEN 'Annakarin Bjorklund'
                      WHEN 28 THEN 'Diane Tan van Zwanenburg'
                      WHEN 29 THEN 'Lukasz Pawlowski'
                      WHEN 30 THEN 'Rob Taylor'
                      WHEN 31 THEN 'Yvonne Moeller'
                      WHEN 32 THEN 'Lorna Dyos'
                      WHEN 33 THEN 'Radka Rana'
                      WHEN 34 THEN 'Susan Bailie'
                      WHEN 35 THEN 'Lance Hooks'
                      WHEN 36 THEN 'Adam Samways'
                      WHEN 37 THEN 'Faisal Majid'
                      WHEN 38 THEN 'Genevieve Guermont'
                      WHEN 39 THEN 'Matt Colling'
                      WHEN 40 THEN 'Jake Reppert'
                      WHEN 41 THEN 'Peter Bull'
                    END AS Consultant,
                    dbo.New_ConsultancyFile.New_Paid AS Paid,
                    dbo.Account.New_DateAgreementSigned AS AgreementSigned,
                    dbo.SalesOrder.New_SterlingTotalAmount
                    - dbo.SalesOrder.New_SterlingTotalTax AS FeesAndExpensesLessTaxGBP,
                    CASE Airclaims_Limited_MSCRM.dbo.salesorder.CFPCurrency
                      WHEN 4 THEN 'EUR'
                      WHEN 2 THEN 'GBP'
                      WHEN 3 THEN 'USD'
                      ELSE 'Unknown'
                    END AS Currency,
                    dbo.SalesOrder.TotalAmount - dbo.SalesOrder.TotalTax AS FeesAndExpensesLessTaxCUR,
                    dbo.SalesOrder.New_ExchangeRate AS ExchangeRate,
                    ISNULL(dbo.New_ConsultancyFile.New_ACPortfolioName + ' ',
                           '')
                    + ISNULL(CAST(dbo.New_ConsultancyFile.New_ACPortfolioNumber AS varchar)
                             + ' aircraft. ', '')
                    + ISNULL(dbo.New_ConsultancyFile.New_AC1Manufacturer + ' ',
                             '') + ISNULL(dbo.New_ConsultancyFile.New_AC1Type
                                          + ' ', '') + ISNULL('x' + CAST(dbo.New_ConsultancyFile.New_AC1NumberOf AS varchar) + ' ', '')
                    + ISNULL(dbo.New_ConsultancyFile.New_AC1MSN + ' ', '')
                    + ISNULL(dbo.New_ConsultancyFile.New_AC1Registration + ' ',
                             '') + ISNULL('; '
                                          + dbo.New_ConsultancyFile.New_AC2Manufacturer
                                          + ' ', '')
                    + ISNULL(dbo.New_ConsultancyFile.New_AC2Type + ' ', '')
                    + ISNULL('x'
                             + CAST(dbo.New_ConsultancyFile.New_AC2NumberOf AS varchar)
                             + ' ', '')
                    + ISNULL(dbo.New_ConsultancyFile.New_AC2MSN + ' ', '')
                    + ISNULL(dbo.New_ConsultancyFile.New_AC2Registration
                             + '; ', '') + ISNULL('; '
                                                  + dbo.New_ConsultancyFile.New_AC3Manufacturer
                                                  + ' ', '')
                    + ISNULL(dbo.New_ConsultancyFile.New_AC3Type + ' ', '')
                    + ISNULL('x'
                             + CAST(dbo.New_ConsultancyFile.New_AC3NumberOf AS varchar)
                             + ' ', '')
                    + ISNULL(dbo.New_ConsultancyFile.New_AC3MSN + ' ', '')
                    + ISNULL(dbo.New_ConsultancyFile.New_AC3Registration
                             + '; ', '') + ISNULL('; '
                                                  + dbo.New_ConsultancyFile.New_AC4Manufacturer
                                                  + ' ', '')
                    + ISNULL(dbo.New_ConsultancyFile.New_AC4Type + ' ', '')
                    + ISNULL('x'
                             + CAST(dbo.New_ConsultancyFile.New_AC4NumberOf AS varchar)
                             + ' ', '')
                    + ISNULL(dbo.New_ConsultancyFile.New_AC4MSN + ' ', '')
                    + ISNULL(dbo.New_ConsultancyFile.New_AC4Registration
                             + '; ', '') + ISNULL('; '
                                                  + dbo.New_ConsultancyFile.New_AC5Manufacturer
                                                  + ' ', '')
                    + ISNULL(dbo.New_ConsultancyFile.New_AC5Type + ' ', '')
                    + ISNULL('x'
                             + CAST(dbo.New_ConsultancyFile.New_AC5NumberOf AS varchar)
                             + ' ', '')
                    + ISNULL(dbo.New_ConsultancyFile.New_AC5MSN + ' ', '')
                    + ISNULL(dbo.New_ConsultancyFile.New_AC5Registration
                             + '; ', '') AS AircraftType,
                    dbo.SalesOrder.Name AS AssignmentTitle,
                    CASE Airclaims_Limited_MSCRM.dbo.Account.IndustryCode
                      WHEN 1 THEN 'Airport'
                      WHEN 2 THEN 'Asset Finance'
                      WHEN 3 THEN 'Consultancy'
                      WHEN 4 THEN 'Educational'
                      WHEN 5 THEN 'Govt Or Regulator'
                      WHEN 6 THEN 'Insurance Broker'
                      WHEN 7 THEN 'Insurance Reinsurer'
                      WHEN 8 THEN 'Insurance Unbderwriter'
                      WHEN 9 THEN 'Manufacturer (Hull)'
                      WHEN 10 THEN 'Manufacturer (Components)'
                      WHEN 11 THEN 'Media Publisher'
                      WHEN 12 THEN 'Military'
                      WHEN 13 THEN 'Modifications'
                      WHEN 14 THEN 'MRO'
                      WHEN 15 THEN 'Operator (Major)'
                      WHEN 16 THEN 'Operator (Other)'
                      WHEN 17 THEN 'Operator (Regional)'
                      WHEN 18 THEN 'Space Operator'
                      WHEN 19 THEN 'Space Hull Manufacturer'
                      WHEN 20 THEN 'Space Components'
                      WHEN 21 THEN 'Trade Association'
                      WHEN 22 THEN 'Travel Services inc Accommodation'
                      WHEN 24 THEN 'Other'
                      WHEN 34 THEN 'Asset Leasing'
                      WHEN 35 THEN 'Space Launcher'
                      WHEN 37 THEN 'Asset Investor (Other)'
                      WHEN 38 THEN 'Insurance Adjuster'
                      WHEN 39 THEN 'Legal'
                      WHEN 40 THEN 'Arranger (Agent)'
                      WHEN 41 THEN 'Construction'
                      ELSE NULL
                    END AS ClientCategory,
                    dbo.Account.Address1_Country AS ClientCountry,
                    CASE Airclaims_Limited_MSCRM.dbo.account.cfpcontinentcode
                      WHEN 5 THEN 'UK'
                      WHEN 6 THEN 'Europe'
                      WHEN 14 THEN 'Europe'
                      WHEN 2 THEN 'Americas'
                      WHEN 3 THEN 'Americas'
                      WHEN 4 THEN 'Americas'
                      WHEN 7 THEN 'Africa'
                      WHEN 8 THEN 'Middle East'
                      WHEN 9 THEN 'Asia'
                      WHEN 10 THEN 'Asia'
                      WHEN 15 THEN 'Asia'
                      WHEN 11 THEN 'Asia'
                      WHEN 12 THEN 'Asia'
                      WHEN 13 THEN 'Asia'
                      ELSE NULL
                    END AS ClientArea,
                    dbo.New_ConsultancyFile.New_DateFileClosed AS DateFileClosed,
                    dbo.New_ConsultancyFile.New_DateBilled AS DateBilled,
                    dbo.SalesOrder.New_OurReference AS AscendInvoiceNumber,
                    dbo.New_ConsultancyFile.New_ConsultancyHours AS ActualHours,
                    dbo.New_ConsultancyFile.New_AirclaimsFileNumber AS AirclaimsFileNumber,
                    dbo.SalesOrder.New_FeesToAirclaimsGBP AS AirclaimsFeesGBP,
                    CASE Airclaims_Limited_MSCRM.dbo.New_ConsultancyFile.New_OwnerofClient
                      WHEN 1 THEN 'Ascend'
                      WHEN 2 THEN 'Airclaims'
                      ELSE NULL
                    END AS OwnerOfFile,
                    dbo.SalesOrder.SalesOrderId,
                    CASE Airclaims_Limited_MSCRM.dbo.New_ConsultancyFile.New_FileCategory
                      WHEN 1 THEN 'C'
                      WHEN 2 THEN 'A'
                      WHEN 3 THEN 'D'
                      ELSE NULL
                    END AS FileCategory
          FROM      SalesOrder
                    LEFT OUTER JOIN Opportunity ON dbo.SalesOrder.OpportunityId = dbo.Opportunity.OpportunityId
                    LEFT OUTER JOIN Account ON dbo.SalesOrder.AccountId = dbo.Account.AccountId
                    LEFT OUTER JOIN New_ConsultancyFile ON dbo.SalesOrder.SalesOrderId = dbo.New_ConsultancyFile.New_ConsultancyOrderId
                    LEFT OUTER JOIN Contact ON dbo.SalesOrder.ContactId = dbo.Contact.ContactId
          WHERE     ( NOT ( dbo.New_ConsultancyFile.New_name IS NULL )
                    )
        ) AS AllConsultancyFiles
        LEFT OUTER JOIN ( SELECT    New_ConsultancyFile.New_name,
                                    SalesOrder.SalesOrderId,
                                    SalesOrder.OrderNumber,
                                    CAST(ISNULL(dbo.Product.New_IsExpense, 0) AS INT) AS IsExpense,
                                    dbo.SalesOrderDetail.SalesOrderDetailId
                          FROM      dbo.Product
                                    INNER JOIN dbo.SalesOrderDetail ON dbo.Product.ProductId = dbo.SalesOrderDetail.ProductId
                                    RIGHT OUTER JOIN dbo.SalesOrder AS SalesOrder ON dbo.SalesOrderDetail.SalesOrderId = SalesOrder.SalesOrderId
                                    LEFT OUTER JOIN dbo.New_ConsultancyFile AS New_ConsultancyFile ON SalesOrder.SalesOrderId = New_ConsultancyFile.New_ConsultancyOrderId
                          WHERE     ( NOT ( New_ConsultancyFile.New_name IS NULL )
                                    )
                                    AND ( CAST(ISNULL(dbo.Product.New_IsExpense,
                                                      0) AS INT) = 1 )
                        ) AS ExpenseSalesOrderDetails ON AllConsultancyFiles.SalesOrderId = ExpenseSalesOrderDetails.SalesOrderId
        LEFT OUTER JOIN dbo.SalesOrderDetail AS SalesOrderDetail ON ExpenseSalesOrderDetails.SalesOrderDetailId = SalesOrderDetail.SalesOrderDetailId
GO

Open in new window

reb73

Just to be clear - Airclaims_Limited_MSCRM is the database name on both servers, is that correct?
reb73

I've reformatted the query using aliases, so that it is easier to change references to the second server..

Use a search replace in the text editor with the attached file to find

dbo.

replacing it with

<Server2 goes here>.<database in server2 goes here>.dbo.
Query.txt
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
ASKER
matrix_aash

servername is server1 with database name Airclaims_Limited_MSCRM where the query runs fine.

I need to run the same query on Server2 on which the datbase name is Firebird.

I hope this makes sense.
ASKER CERTIFIED SOLUTION
reb73

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
matrix_aash

The last query with text file attachment worked file.

Brilliant.

I just have one more question. I have to change another 5 query of similar sort.

What is the logic I need to use.

Please guide me.

thanks a million.

Cheers.
reb73

All tables are usually preceded by one of the following -

FROM
JOIN

so scan through your list and assign aliases to each of them - a short 2/3 char alias will keep it neat and clean.

You define an alias as follows -

dbo.SalesOrder  SO  

where SO is the alias for dbo.SalesOrder

Once this is done, find all references of <tablename>. and replace with <alias>.

Then all that is left is just for you to follow my suggestions in my previous two posts..

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.