We help IT Professionals succeed at work.
Get Started

T-SQL On View

matrix_aash
matrix_aash asked
on
336 Views
Last Modified: 2012-05-06
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

Comment
Watch Question
Commented:
This problem has been solved!
Unlock 1 Answer and 13 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE