March 15, 2011 at 2:41 pm
Hi,
When changing collation for a database following error can occur if your table-valued functions (TF) don't specify COLLATE DATABASE_DEFAULT in their output table declaration ie:
CREATE FUNCTION dbo.fn_xxx
RETURNS
@t TABLE (
a VARCHAR(30) -- note, no collate database_default!
)
...
ALTER DATABASE [DB] COLLATE SQL_Latin1_General_CP1_CS_AS
The object 'fn_xxx' is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation.
Is it possible to figure out if collation database_default was omitted when declaring @t-table?
And without try/catch or parsing function-definition text 🙂
I tried looking into syscolumns for the table @t, but couldn't see any difference between columns with collate default specified and those without.
March 15, 2011 at 5:03 pm
I started looking into this but have to go so figured I would post what I found so far...
After setting up a little test environment:
CREATE TABLE dbo.a (NAME VARCHAR(100))
INSERT INTO dbo.a
(NAME)
VALUES ('a'),('b'),('c'),('d'),('e'),('f')
and then building a couple functions:
IF EXISTS ( SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[fn_test]')
AND type IN (N'FN', N'IF', N'TF', N'FS', N'FT') )
DROP FUNCTION [dbo].[fn_test]
GO
IF EXISTS ( SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[fn_testb]')
AND type IN (N'FN', N'IF', N'TF', N'FS', N'FT') )
DROP FUNCTION [dbo].[fn_testb]
GO
SELECT OBJECT_NAME(referencing_id) AS referencing_name,
*
FROM sys.sql_expression_dependencies
GO
CREATE FUNCTION dbo.fn_test ()
RETURNS @a TABLE (name1 VARCHAR(100))
AS
BEGIN
INSERT INTO @a
SELECT NAME FROM dbo.a
RETURN
END
GO
SELECT OBJECT_NAME(referencing_id) AS referencing_name,
*
FROM sys.sql_expression_dependencies
GO
CREATE FUNCTION dbo.fn_testb ()
RETURNS @a TABLE (name1 VARCHAR(100))
WITH SCHEMABINDING
AS
BEGIN
INSERT INTO @a
SELECT NAME FROM dbo.a
RETURN
END
GO
SELECT OBJECT_NAME(referencing_id) AS referencing_name,
*
FROM sys.sql_expression_dependencies
GO
Notice that fn_testb (the one WITH SCHEMABINDING) has two rows returned by sys.sql_expression_dependencies with the only difference in the two rows being in the referenced_minor_id column. Not sure what it means yet...have to go now. I might pick this up later.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
October 6, 2011 at 10:31 am
Took a while to reply to this, but I tried your method and still wasn't any difference as far as I could see.
Gave up on this, one just have to manually go through the functions before ALTER'ing collation, at least in SQL Server 2008.
Or try to parse function body to get the column definitions...
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply