August 27, 2009 at 9:48 am
Hi
New to this so bear with me!
I have a SQL server 2005 install. That contains a 3 databases used by my client. I was using a view that pulled data from all three - all good. All was working fine until - one of the d/b providers decided to come and upgrade to a new version of their software. When then did this - they created another instance of SQL on the same server and updated and installed their database to this instance. The view I was using was part of this database - and still resides in this new database. The other 2 databases remained the same.
So far I have
1. Created a linked server on the 'new' instance that points to original instance.
2. Changed the references in the view in the new database to look for the 'linked server name'.
3. Now when I try and open the view - I get a message that the 'multi variant identifier xxxx could not be bound'.
Any pointers as to what I need to check - not a SQL guru in any way shape or form I'm afraid.
I can't get access to the server to post the view just yet - but will when I can.
Any advice in advance would be appreciated.
Thanks
August 27, 2009 at 10:41 am
Check your aliases closely if you have any.
--
:hehe:
August 27, 2009 at 10:45 am
Thanks Slick - I'll check as far as I know there weren't any, but not 100% sure
August 27, 2009 at 10:49 am
Could you post your code and the name(s) of your linked server(s).
August 28, 2009 at 5:45 am
Hi
Finally got access to site
The two linked servers are "blkviztopia" and "blkviztopia\cch"
There doesn't appear to be any aliases in it.
At the end of this message I've also posted the error message SQL is reporting.
The view is as follows:
========================================================================
SELECT ISNULL(dbo.Contact.FName + ' ', '') + ISNULL(dbo.Contact.LName, '') AS companyFullName,
dbo.ClientSupplier.ClientID,
dbo.Contact.ContactID,
dbo.ContactType.ContactTypeId,
dbo.ContactType.ContactType,
dbo.ClientSupplier.CreatedDate,
dbo.ClientSupplier.ParentClientID,
dbo.ClientSupplier.DefaultRecoveryRate,
dbo.ClientSupplier.Notes,
dbo.ClientSupplier.OfficeID,
dbo.ClientSupplier.DepartmentID,
dbo.ClientSupplier.ClientCode,
dbo.ClientSupplier.PeriodEndDate,
dbo.ClientSupplier.Vattable,
dbo.ClientSupplier.CliFileID,
dbo.ClientSupplier.Internal,
dbo.Contact.RespNotes,
dbo.ClientSupplier.ClientVATTypeID,
dbo.ClientSupplier.ZID,
dbo.ClientSupplier.CreatedBy,
dbo.ClientSupplier.DateCreated,
dbo.ClientSupplier.DefaultCurrency,
dbo.ClientSupplier.Closed,
dbo.Contact.Pref,
dbo.Contact.FName,
dbo.Contact.Mname,
dbo.Contact.LName,
dbo.Contact.Tel,
dbo.Contact.Fax,
dbo.Contact.Mobile,
dbo.Contact.Address1,
dbo.Contact.Address2,
dbo.Contact.Address3,
dbo.Contact.Town,
dbo.Contact.County,
dbo.Contact.PostCode,
dbo.Contact.Country,
dbo.Contact.EMail,
dbo.Contact.Suff,
dbo.Contact.Sex,
dbo.Contact.MailingName,
dbo.Contact.Initials,
dbo.Contact.LName AS MainName,
dbo.Contact.Salutation,
dbo.vwBPSPCEngagementLetter.CustomValue AS EngagementLetter,
dbo.vwBPSPCCoNumber.CustomValue AS CoNumber,
dbo.vwBPSPCVAT.CustomValue AS VAT,
dbo.vwBPSPCSalutaion.CustomValue AS customSalutation,
dbo.vwBPSPCDateOfIncorporation.CustomValue AS DateOfIncorporation,
dbo.vwBPSCurrentContactPartner.PartnerID,
dbo.vwBPSCurrentContactPartner.FNameTemp,
dbo.vwBPSCurrentContactPartner.SNameTemp,
dbo.vwBPSCurrentContactPartner.FNameTemp + ' ' + dbo.vwBPSCurrentContactPartner.SNameTemp AS PartnerFullName,
dbo.vwBPSCurrentContactPartner.FNameTemp + ' ' + dbo.vwBPSCurrentContactPartner.SNameTemp AS Partner,
dbo.ClientUnBilledWip.UnBilledWip AS [Wip_£], dbo.ClientUnBilledWip.UnPostedWIP AS [UnpostedWip_£],
blkviztopia.pertax.dbo.ips_PerTAX_Client_Details.HMIT_OFFICE AS HmrcOffice,
blkviztopia.pertax.dbo.ips_PerTAX_Client_Details.HMIT_REF AS UtrNumber,
blkviztopia.pertax.dbo.ips_PerTAX_Client_Details.ADDRESS AS HmrcAddress,
blkviztopia.pertax.dbo.ips_PerTAX_Client_Details.PHONE AS HmrcPhone,
blkviztopia.pertax.dbo.ips_PerTAX_Client_Details.POSTCODE AS HmrcPostcode,
blkviztopia.pertax.dbo.ips_PerTAX_Client_Details.TOTAL_TAXDUE AS [TotalTax_£],
blkviztopia.pertax.dbo.ips_PerTAX_Client_Details.FIRST_PAYMENT AS [FirstPayment_£],
blkviztopia.pertax.dbo.ips_PerTAX_Client_Details.[2ND_PAYMENT] AS [SecondPayment_£],
blkviztopia.pertax.dbo.ips_PerTAX_Client_Details.TAXYEAR,
blkviztopia.pertax.dbo.ips_PerTAX_Client_Details.DOB AS DateOfBirth,
blkviztopia.pertax.dbo.ips_PerTAX_Client_Details.NI_NUM AS NatInsNo,
blkviztopia.pertax.dbo.ips_PerTAX_Client_Details.EMPLOYER_REF AS PayeRef,
blkviztopia.pertax.dbo.ips_PerTAX_Client_Details.INSPECTOR_NAME AS HmrcRef,
blkviztopia.pertax.dbo.ips_PerTAX_Client_Details.NAME AS HmrcOfficeName,
blkviztopia.pertax.dbo.ips_PerTAX_Client_Details.FAX AS HmrcFax,
dbo.vwBPSPCSalutaion.CustomID,
dbo.LastBilled.InvoiceDate AS LastInvoiceDate,
dbo.LastBilled.BilledAmount AS [LastInvoiceNet_£],
dbo.LastBilled.BilledAmount * 1.175 AS [LastInvoiceGross_£]
FROM dbo.ClientSupplier LEFT OUTER JOIN
dbo.LastBilled ON dbo.ClientSupplier.ClientID = dbo.LastBilled.ClientId LEFT OUTER JOIN
blkviztopia.pertax.dbo.ips_PerTAX_Client_Details ON
dbo.ClientSupplier.ClientCode = blkviztopia.pertax.dbo.ips_PerTAX_Client_Details.USERCODE COLLATE SQL_Latin1_General_CP1_CI_AS RIGHT OUTER JOIN
dbo.Contact INNER JOIN
dbo.vwBPSCurrentContactPartner ON dbo.Contact.ContactID = dbo.vwBPSCurrentContactPartner.ContactId LEFT OUTER JOIN
dbo.vwBPSPCDateOfIncorporation ON dbo.Contact.ContactID = dbo.vwBPSPCDateOfIncorporation.ContactID LEFT OUTER JOIN
dbo.vwBPSPCSalutaion ON dbo.Contact.ContactID = dbo.vwBPSPCSalutaion.ContactID LEFT OUTER JOIN
dbo.vwBPSPCVAT ON dbo.Contact.ContactID = dbo.vwBPSPCVAT.ContactID LEFT OUTER JOIN
dbo.vwBPSPCPAYEref ON dbo.Contact.ContactID = dbo.vwBPSPCPAYEref.ContactID LEFT OUTER JOIN
dbo.vwBPSPCDateofBirth ON dbo.Contact.ContactID = dbo.vwBPSPCDateofBirth.ContactID LEFT OUTER JOIN
dbo.vwBPSPCCoNumber ON dbo.Contact.ContactID = dbo.vwBPSPCCoNumber.ContactID LEFT OUTER JOIN
dbo.vwBPSPCEngagementLetter ON dbo.Contact.ContactID = dbo.vwBPSPCEngagementLetter.ContactID LEFT OUTER JOIN
dbo.ClientUnBilledWip ON dbo.Contact.ContactID = dbo.ClientUnBilledWip.ContactID ON
dbo.ClientSupplier.ContactID = dbo.Contact.ContactID LEFT OUTER JOIN
dbo.ContactType ON dbo.Contact.ContactTypeID = dbo.ContactType.ContactTypeId
WHERE (dbo.ClientSupplier.ClientSupplierType = 1)
===========================================================
Error message
The multi-part identifier "blkviztopia.pertax.dbo.ips_PerTAX_Client_Details.USERCODE" could not be bound.
The multi-part identifier "blkviztopia.pertax.dbo.ips_PerTAX_Client_Details.HMIT_OFFICE" could not be bound.
The multi-part identifier "blkviztopia.pertax.dbo.ips_PerTAX_Client_Details.HMIT_REF" could not be bound.
The multi-part identifier "blkviztopia.pertax.dbo.ips_PerTAX_Client_Details.ADDRESS" could not be bound.
The multi-part identifier "blkviztopia.pertax.dbo.ips_PerTAX_Client_Details.PHONE" could not be bound.
The multi-part identifier "blkviztopia.pertax.dbo.ips_PerTAX_Client_Details.POSTCODE" could not be bound.
The multi-part identifier "blkviztopia.pertax.dbo.ips_PerTAX_Client_Details.TOTAL_TAXDUE" could not be bound.
The multi-part identifier "blkviztopia.pertax.dbo.ips_PerTAX_Client_Details.FIRST_PAYMENT" could not be bound.
The multi-part identifier "blkviztopia.pertax.dbo.ips_PerTAX_Client_Details.2ND_PAYMENT" could not be bound.
The multi-part identifier "blkviztopia.pertax.dbo.ips_PerTAX_Client_Details.TAXYEAR" could not be bound.
The multi-part identifier "blkviztopia.pertax.dbo.ips_PerTAX_Client_Details.DOB" could not be bound.
The multi-part identifier "blkviztopia.pertax.dbo.ips_PerTAX_Client_Details.NI_NUM" could not be bound.
The multi-part identifier "blkviztopia.pertax.dbo.ips_PerTAX_Client_Details.EMPLOYER_REF" could not be bound.
The multi-part identifier "blkviztopia.pertax.dbo.ips_PerTAX_Client_Details.INSPECTOR_NAME" could not be bound.
The multi-part identifier "blkviztopia.pertax.dbo.ips_PerTAX_Client_Details.NAME" could not be bound.
The multi-part identifier "blkviztopia.pertax.dbo.ips_PerTAX_Client_Details.FAX" could not be bound.
August 28, 2009 at 7:06 am
Hey Buddy,
You really need to use aliases! In your joins just assign each table a letter or point of reference that you think makes sense.
For example... Server.Database.SchemaName.TableName AS a
...and then use that 'a' or aka table alias for all the columns from that table that you need in your SELECT clause. Do this for all other tables and run the query and let me know what you see.
Your from statement below would look like:
FROM dbo.ClientSupplier A
LEFT OUTER JOIN
dbo.LastBilled B ON A.ClientID = B.ClientId
LEFT OUTER JOIN
blkviztopia.pertax.dbo.ips_PerTAX_Client_Details C ON
A.ClientCode = C.USERCODE
And so on...and so forth.
Thanks,
S
--
:hehe:
August 28, 2009 at 8:21 am
Slick84 (8/28/2009)
Hey Buddy,You really need to use aliases! In your joins just assign each table a letter or point of reference that you think makes sense.
For example... Server.Database.SchemaName.TableName AS a
...and then use that 'a' or aka table alias for all the columns from that table that you need in your SELECT clause. Do this for all other tables and run the query and let me know what you see.
Your from statement below would look like:
FROM dbo.ClientSupplier A
LEFT OUTER JOIN
dbo.LastBilled B ON A.ClientID = B.ClientId
LEFT OUTER JOIN
blkviztopia.pertax.dbo.ips_PerTAX_Client_Details C ON
A.ClientCode = C.USERCODE
And so on...and so forth.
Thanks,
S
I have to agree with Slick84, especially since using 3 and 4 part names in select lists has been depreciated and won't be supported in future versions of SQL Server. The use of table aliases and using those for two part naming of columns should be considered a SQL Server best practice.
August 28, 2009 at 9:36 am
Thanks Slick/Lynn for the advice - that has worked a treat.
I added aliases and the SQL now verifies. I've inherited this so I finding lots of worms and woodwork.
The problem now is that when I execute the view I get this error message. I've included the revised script at the end of this post.
====================================================================
SQL Execution Error.
Executed SQL statement: SELECT ISNULL(C.FName + ' ', '') + ISNULL(C.LName, '') AS companyFullName, CS.ClientID, C.ContactID, dbo.ContactType.ContactTypeId, dbo.ContactType.ContactType, CS.CreatedDate, CS.ParentClientID, CS.DefaultRecoveryRate, CS.Notes, CS.OfficeID, CS.Depa...
Error Source: .Net SqlClient Data Provider
Error Message: The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.
===========================================================
REVISED VIEW:
SELECT ISNULL(C.FName + ' ', '') + ISNULL(C.LName, '') AS companyFullName,
CS.ClientID,
C.ContactID,
dbo.ContactType.ContactTypeId,
dbo.ContactType.ContactType,
CS.CreatedDate,
CS.ParentClientID,
CS.DefaultRecoveryRate,
CS.Notes,
CS.OfficeID,
CS.DepartmentID,
CS.ClientCode,
CS.PeriodEndDate,
CS.Vattable,
CS.CliFileID,
CS.Internal,
C.RespNotes,
CS.ClientVATTypeID,
CS.ZID,
CS.CreatedBy,
CS.DateCreated,
CS.DefaultCurrency,
CS.Closed,
C.Pref,
C.FName,
C.Mname,
C.LName,
C.Tel,
C.Fax,
C.Mobile,
C.Address1,
C.Address2,
C.Address3,
C.Town,
C.County,
C.PostCode,
C.Country,
C.EMail,
C.Suff,
C.Sex,
C.MailingName,
C.Initials,
C.LName AS MainName,
C.Salutation,
dbo.vwBPSPCEngagementLetter.CustomValue AS EngagementLetter,
dbo.vwBPSPCCoNumber.CustomValue AS CoNumber,
dbo.vwBPSPCVAT.CustomValue AS VAT,
dbo.vwBPSPCSalutaion.CustomValue AS customSalutation,
dbo.vwBPSPCDateOfIncorporation.CustomValue AS DateOfIncorporation,
BPSCCP.PartnerID,
BPSCCP.FNameTemp,
BPSCCP.SNameTemp,
BPSCCP.FNameTemp + ' ' + BPSCCP.SNameTemp AS PartnerFullName,
BPSCCP.FNameTemp + ' ' + BPSCCP.SNameTemp AS Partner,
dbo.ClientUnBilledWip.UnBilledWip AS [Wip_£], dbo.ClientUnBilledWip.UnPostedWIP AS [UnpostedWip_£],
P.HMIT_OFFICE AS HmrcOffice,
P.HMIT_REF AS UtrNumber,
P.ADDRESS AS HmrcAddress,
P.PHONE AS HmrcPhone,
P.POSTCODE AS HmrcPostcode,
P.TOTAL_TAXDUE AS [TotalTax_£],
P.FIRST_PAYMENT AS [FirstPayment_£],
P.[2ND_PAYMENT] AS [SecondPayment_£],
P.TAXYEAR,
P.DOB AS DateOfBirth,
P.NI_NUM AS NatInsNo,
P.EMPLOYER_REF AS PayeRef,
P.INSPECTOR_NAME AS HmrcRef,
P.NAME AS HmrcOfficeName,
P.FAX AS HmrcFax,
dbo.vwBPSPCSalutaion.CustomID,
LB.InvoiceDate AS LastInvoiceDate,
LB.BilledAmount AS [LastInvoiceNet_£],
LB.BilledAmount * 1.175 AS [LastInvoiceGross_£]
FROM ClientSupplier CS LEFT OUTER JOIN
dbo.LastBilled LB ON CS.ClientID = LB.ClientId LEFT OUTER JOIN
blkviztopia.pertax.dbo.ips_PerTAX_Client_Details P ON
CS.ClientCode = P.USERCODE COLLATE SQL_Latin1_General_CP1_CI_AS RIGHT OUTER JOIN
dbo.Contact C INNER JOIN
dbo.vwBPSCurrentContactPartner BPSCCP ON C.ContactID = BPSCCP.ContactId LEFT OUTER JOIN
dbo.vwBPSPCDateOfIncorporation ON C.ContactID = dbo.vwBPSPCDateOfIncorporation.ContactID LEFT OUTER JOIN
dbo.vwBPSPCSalutaion ON C.ContactID = dbo.vwBPSPCSalutaion.ContactID LEFT OUTER JOIN
dbo.vwBPSPCVAT ON C.ContactID = dbo.vwBPSPCVAT.ContactID LEFT OUTER JOIN
dbo.vwBPSPCPAYEref ON C.ContactID = dbo.vwBPSPCPAYEref.ContactID LEFT OUTER JOIN
dbo.vwBPSPCDateofBirth ON C.ContactID = dbo.vwBPSPCDateofBirth.ContactID LEFT OUTER JOIN
dbo.vwBPSPCCoNumber ON C.ContactID = dbo.vwBPSPCCoNumber.ContactID LEFT OUTER JOIN
dbo.vwBPSPCEngagementLetter ON C.ContactID = dbo.vwBPSPCEngagementLetter.ContactID LEFT OUTER JOIN
dbo.ClientUnBilledWip ON C.ContactID = dbo.ClientUnBilledWip.ContactID ON
CS.ContactID = C.ContactID LEFT OUTER JOIN
dbo.ContactType ON C.ContactTypeID = dbo.ContactType.ContactTypeId
WHERE (CS.ClientSupplierType = 1)
August 28, 2009 at 10:04 am
Hi
Ignore the last post - I altered one of the other views that the script was looking at which wasn't working and now it verifies and runs.
Really appreciate your help
All sorted
Cheers
G
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply