Implicit conversion from sql_variant to varchar not allowed ...

[reldridge]

I'm trying to modify an existing SP to add another table from a different database from another SQL Server.
Original procedure runs without error but when I change this:

from FS_COHeader ch (readuncommitted)
left join @tbl tbl on ch.COHeaderKey = tbl.COHeaderKey

to:

from FS_COHeader ch (readuncommitted)
left join @tbl tbl on ch.COHeaderKey = tbl.COHeaderKey
left join ZULU.UK_Main_ED.Company co (readuncommitted) on ch.CustomerID = co.Account_Code

I get:

Implicit conversion from data type sql_variant to varchar is not allowed. Use the CONVERT function to run this query.

I've tried this variation:

left join ZULU.UK_Main_ED.Company co (readuncommitted) on convert(varchar(13), ch.CustomerID) = convert(varchar(13), co.Account_Code)

But I get exactly the same error - can anyone help me with this please?

 

[SQLSister]

What field has the sql_variant datatype? It probably isn;t a good thing to use. Check the length of the field with this datatype, maybe there are some records which still can't convert to varchar 13 due to their size. Even if these are not the records you are concerned with, the sql won't know that until it converts all of the records in the table and checks.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[reldridge]

Thanks for your response - I've digged deeper and found that this seems to be the wrong error message! I got this from Reporting Services but when I run the SP from within Query Analyzer I get this instead:

Server: Msg 208, Level 16, State 1, Procedure Zusp_ReportCOAcknowledgement, Line 72
Invalid object name 'ZULU.UK_Main_ED.dbo.Company'.

The SP runs on a SQLServer 2000 called BRITAIN but needs to join data from another SQLServer 2000 called ZULU.
I've already verified that both servers are linked to each other in case that affected the join.

The following syntax is accepted by the SP editor:

from FS_COHeader ch (readuncommitted)
left join [ZULU.UK_Main_ED.dbo.Company] co (readuncommitted) on ch.CustomerID = co.Account_Code

but the above error message results when executing it.
Any ideas?

 

[gmmastros]

Your square brackets are wrong. This may not solve your problem, but is good info that you should know.

from FS_COHeader ch (readuncommitted)
left join [ZULU[!]][/!].[!][[/!]UK_Main_ED[!]][/!].[!][[/!]dbo[!]][/!].[!][[/!]Company] co (readuncommitted) on ch.CustomerID = co.Account_Code

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[reldridge]

Thanks for your reply gmmastros - I tried what you said and then got:

Error 7753: Cannot specify an index or locking hint for a remote data source.

So I took out (readuncommited) and then got:

Error 446: Cannot resolve collation conflict for equal to operation.

The plot thickens ...

 

[gmmastros]

I'm just guessing here. Sorry. But perhaps you could try...

from FS_COHeader ch (readuncommitted)
left join [ZULU].[UK_Main_ED].[dbo].[Company] co (readuncommitted) on ch.CustomerID [!]Collate SQL_Latin1_General_CP1_CI_AS[/!] = co.Account_Code [!]Collate SQL_Latin1_General_CP1_CI_AS[/!]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[reldridge]

Thanks gmmastros!
I used the collation value SQL_Latin1_General_Cp437_BIN for both columns in the join and that error message has now gone.
Only to be replaced by the following message when I try to save the SP:

Error 7405: Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query.

Now I've already tried inserting:

SET ANSI_NULLS ON
SET ANSI_WARNINGS ON

before the SELECT and

SET ANSI_NULLS OFF
SET ANSI_WARNINGS OFF

afterwards but the same error message results - could it be that the Linked Server Connection needs these settings somewhere?