Re: sql2k Collation problems.

> Based on my knowledge, parameters for function is assigned the default
> collation of the database:

Then please try the following..

Create a new database using a different collation than your server instance.

Our SQL Server instance = SQL_Latin1_General_CP1_CI_AS
The problem database = Latin1_General_CI_AS

Then run the following script against that DB.
---START
CREATE FUNCTION dbo.myfunc ()
RETURNS @Result table ( rescol varchar(10))
AS
BEGIN
INSERT @Result
SELECT 'A'
RETURN
END
GO

CREATE TABLE testtable
(
test_id varchar(10) NOT NULL
)
GO

SELECT *
FROM testtable TT,
dbo.myfunc() AS MF
WHERE TT.test_id = MF.rescol
GO

---END

I get the following error..

"Cannot resolve collation conflict for equal to operation."

If you do a sp_help myfunc and look at the collation, you'll see it has
come from the server instance, whereas if you do sp_help testtable, it will
have the collation of the database.

Please let me know if you get a different result.

Thanks
Adrian Parker



.

Relevant Pages

  • Re: Change field collation
    ... constraints and statistics on the columns before you can alter ... There are quite a lot of caveats and you use this script at your own risk. ... Changing the collation of the existing tables is unfortunately not very ... You might consider using SEM to script out the objects in the database, ...
    (microsoft.public.sqlserver.programming)
  • Re: Changing collation -- URGENT
    ... You can use the following script to change the collations of your columns, ... duplicate primary key constraints create statements, ... Changing the collation of the existing tables is unfortunately not very ... You might consider using SEM to script out the objects in the database, ...
    (microsoft.public.sqlserver.server)
  • Re: Database collation error after moving to a remote sql server
    ... I think I'm going to try plan B: Install a second instance of SQL 2000 onto ... the remote server and set its default collation to the same as my SMS ... database, then move my db to the new instance. ... The char, varchar, text, nchar, nvarchar, or ntext system data types, ...
    (microsoft.public.sms.setup)
  • Re: COLLATION PROBLEM CAN ANY ONE HELP
    ... Sounds like you may have code page X data in a code page Y database. ... database with a collation that matches the data's actual code page: ... COLLATION PROBLEM CAN ANY ONE HELP ... | Content-Type: text/plain; ...
    (microsoft.public.sqlserver.datamining)
  • Re: Need to reconfigure SQL Server to be Case-Insensitive
    ... You will want to perform ALTER DATABASE and ALTER TABLE statements. ... You can change the collation of a column by using the ALTER TABLE statement: ... applied to all columns, user-defined data types, variables, and parameters ...
    (microsoft.public.sqlserver.server)