Collation level error 1

[techipa]

Hello All,

I installed SQL server 2005 on the machine and restored the databases from 2000. The collation level for the user database (migrated from 2000) is different than the collation level in tempdb at 2005 server.

When I run a SP which creates the temp # table and has join with the user tables on varchar, text, char fields I get the collation mismatch error.

May I know how do I fix this issue?
How do I find all the SP, functions, triggers..objects which creates temp # tables and has joins on the text fields?

Thanks in advance,
-techiPA

 

[gmmastros]

This query will show you all stored procedures that have a # symbol in them.

[COLOR=blue]Select[/color] Specific_Name [COLOR=blue]from[/color] Information_Schema.Routines
[COLOR=blue]Where[/color] Routine_Definition Like [COLOR=red]'%#%'[/color]
AND OBJECTPROPERTY([COLOR=#FF00FF]OBJECT_ID[/color](SPECIFIC_NAME),[COLOR=red]'IsMSShipped'[/color]) =0
[COLOR=blue]Order[/color] [COLOR=blue]BY[/color] Specific_Name

Thanks goes to SQLDenis for posting this query elswhere.

My best advice is to change the queries to use table variables instead.

-George

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

 

[techipa]

George,

This is what I am doing to take care of the issue. But I have 350 procs/functions in just one database which I need to manually change to. So I was looking for some other alternative.

Can I change the collation of model database? Woudl it have negative impact?

CREATE TABLE #ABC (
	Site	INT,
	Job	INT,
	Desc1	VARCHAR(50) COLLATE database_default,
	Desc2	VARCHAR(50) COLLATE database_default,
)

Thanks,
-techiPA

 

[gmmastros]

I haven't figured out a good way to change the collation of model or tempdb. In fact, the easiest way (that I know of) is to uninstall SQL Server and install using the collation that you want. That's a rather drastic step to take, so I don't know if you want to do that.

On a positive note, most of the time table variables are faster than temp tables, so it may be worth doing it anyway.

-George

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

 

[techipa]

You mean replace the temp tables with the table variable?

 

[gmmastros]

Yes. Instead of using table variables, use a temp table. There are a couple 'gotchas' that you should be aware of, but once you get used to them, they're pretty easy. If you want, post one of your procedures that uses a temp table and I'll show you how to convert it to a table variable.

-George

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

 

[techipa]

Alex,

Here is the sample of the SP using the temp #.
One question - do I have to define default collation to the uniqueidentifier or sysname data type fields of the temp table as well. say for example

CREATE TABLE #ABC
(
[TranID] [UniqueIdentifier] NOT NULL,
Group_name sysname
)

CREATE PROC [dbo].[puABC]
	@Copy int,
	@Site int,
	@Job int,
	@StepID int,
	@LangID int
AS
CREATE TABLE #CompList (
		Site INT, 
		CompID VARCHAR(20) COLLATE database_default
	)

	INSERT INTO #CompList
		SELECT DISTINCT MBOM.Site, MBOM.CompID
		FROM BOM (NOLOCK)
 		INNER JOIN MBOM (NOLOCK)
			ON MBOM.Copy = BOM.Copy
			AND MBOM.Site = BOM.Site
			AND MBOM.Job = BOM.Job
			AND MBOM.MBOMID = BOM.MBOMID
		WHERE BOM.Copy = @Copy
			AND BOM.Site = @Site
			AND BOM.Job = @Job
			AND BOM.StepID = @StepID

	SELECT DISTINCT MBOM.CompID, Alloc.*
	FROM #CompList
 	INNER JOIN MBOM (NOLOCK)
		ON MBOM.Site = #CompList.Site
		AND MBOM.CompID = #CompList.CompID
	INNER JOIN Alloc (NOLOCK)
		ON Alloc.Site = MBOM.Site
		AND Alloc.Job = MBOM.Job
		AND Alloc.StepID = 0
		AND Alloc.MBOMID = MBOM.MBOMID

Thanks in advance,
-techiPA

 

[gmmastros]

To convert this procedure to table variables...

CREATE PROC [dbo].[puABC]
    @Copy int,
    @Site int,
    @Job int,
    @StepID int,
    @LangID int
AS
[!]Declare @CompList
Table   [/!](
        Site INT, 
        CompID VARCHAR(20)
        )

    INSERT INTO [!]@CompList[/!]
        SELECT DISTINCT MBOM.Site, MBOM.CompID
        FROM BOM (NOLOCK)
         INNER JOIN MBOM (NOLOCK)
            ON MBOM.Copy = BOM.Copy
            AND MBOM.Site = BOM.Site
            AND MBOM.Job = BOM.Job
            AND MBOM.MBOMID = BOM.MBOMID
        WHERE BOM.Copy = @Copy
            AND BOM.Site = @Site
            AND BOM.Job = @Job
            AND BOM.StepID = @StepID

    SELECT DISTINCT MBOM.CompID, Alloc.*
    FROM [!]@CompList As CompList[/!]
     INNER JOIN MBOM (NOLOCK)
        ON MBOM.Site = [!]CompList[/!].Site
        AND MBOM.CompID = [!]CompList[/!].CompID
    INNER JOIN Alloc (NOLOCK)
        ON Alloc.Site = MBOM.Site
        AND Alloc.Job = MBOM.Job
        AND Alloc.StepID = 0
        AND Alloc.MBOMID = MBOM.MBOMID

-George

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

 

[gmmastros]

You can also eliminate the temp table altogether from this query by using a derived table, as show here. Notice that the first insert query becomes the derived table for the select query.

CREATE PROC [dbo].[puABC]
    @Copy int,
    @Site int,
    @Job int,
    @StepID int,
    @LangID int
AS
    SELECT DISTINCT MBOM.CompID, Alloc.*
    FROM [!](
         SELECT DISTINCT MBOM.Site, MBOM.CompID
         FROM   BOM (NOLOCK)
                INNER JOIN MBOM (NOLOCK)
                  ON  MBOM.Copy = BOM.Copy
                  AND MBOM.Site = BOM.Site
                  AND MBOM.Job = BOM.Job
                  AND MBOM.MBOMID = BOM.MBOMID
         WHERE  BOM.Copy = @Copy
                AND BOM.Site = @Site
                AND BOM.Job = @Job
                AND BOM.StepID = @StepID
         ) As CompList[/!]
     INNER JOIN MBOM (NOLOCK)
        ON MBOM.Site = CompList.Site
        AND MBOM.CompID = CompList.CompID
    INNER JOIN Alloc (NOLOCK)
        ON Alloc.Site = MBOM.Site
        AND Alloc.Job = MBOM.Job
        AND Alloc.StepID = 0
        AND Alloc.MBOMID = MBOM.MBOMID

-George

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

 

[techipa]

Thanks George for the prompt reply and the code.

I will have to ask to the senior analyst for this change. If this change is not implemented then I will have to work with the temp tables and make the default collation to the varchar/char datatypes.

One question I have is:
do I have to define default collation to the uniqueidentifier or sysname data type fields of the temp table as well. say for example

CREATE TABLE #ABC
(
[TranID] [UniqueIdentifier] NOT NULL,
Group_name sysname
)

 

[techipa]

You are right George, in this case particularly I can eliminate the temp table. There is not need of the temp table here if I use the sub-query inside the main query.

I will look into all the SP's to see if I can create the derived query instead of table variable or temp table.

Thanks a lot.

-techipA

 

[gmmastros]

You shouldn't have to specify the collation for unique identifier, but you probably will for sysname.

According to Books on Line:

nvarchar

Variable-length Unicode data with a maximum length of 4,000 characters. sysname is a system-supplied user-defined data type that is functionally equivalent to nvarchar(128) and is used to reference database object names.

Since sysname is based on nvarchar, you need to specify the collation.

Seriously though, I strongly urge you to switch to table variables. I know a guy that re-wrote a procedure that used temp tables so that it now uses table variables. The execution time on this procedure went from 5 minutes to 9 seconds. That's a huge performance improvement.

-George

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

 

[techipa]

I fully agree with your suggestion. I will try to convince the senior analyst and will go from there. Hopefully he will agree to it.

Thanks a lot.
-techiPA