September 9, 2013 at 10:40 am
I am not sure if I am doing something wrong, but I am getting an issue that seems to be part of the compatibility level on a 2008 server.
If I set the DB Level to 90 I can create a TVP
ALTER DATABASE YourDB SET COMPATIBILITY_LEVEL = 90
GO
IF EXISTS (SELECT * FROM sys.types st JOIN sys.schemas ss ON st.schema_id = ss.schema_id WHERE st.name = N'TreeSortList' AND ss.name = N'dbo')
DROP TYPE [dbo].[TreeSortList]
GO
CREATE TYPE [dbo].[TreeSortList] AS TABLE(
[SortId] [uniqueidentifier] NOT NULL,
[TheSort] [int] NOT NULL
)
GO
I would have expected that to fail. I can even use that TVP with the compatibility set to 2005
In the next example because of the example above I would expect Merge to work the same way and it doesn't
ALTER DATABASE YourDB SET COMPATIBILITY_LEVEL = 90
GO
CREATE TABLE #Test (TheID uniqueidentifier, TheSort int)
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Save_ProjectTreeSort]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Save_ProjectTreeSort]
GO
IF EXISTS (SELECT * FROM sys.types st JOIN sys.schemas ss ON st.schema_id = ss.schema_id WHERE st.name = N'TreeSortList' AND ss.name = N'dbo')
DROP TYPE [dbo].[TreeSortList]
GO
CREATE TYPE [dbo].[TreeSortList] AS TABLE(
[SortId] [uniqueidentifier] NOT NULL,
[TheSort] [int] NOT NULL
)
GO
CREATE PROCEDURE [dbo].[Save_ProjectTreeSort]
@TreeSortList [TreeSortList] READONLY
AS
SET NOCOUNT ON
MERGE #Test AS [Test]
USING @TreeSortList AS [TSL]
ON TSL.[SortId] = Test.TheID
WHEN MATCHED THEN
UPDATE SET Test.[TheSort] = TSL.[TheSort]
WHEN NOT MATCHED THEN
INSERT ([TheID], [TheSort])
VALUES ([SortId], [TheSort]);
--endregion
GO
You get the following error
Incorrect syntax near 'MERGE'. You may need to set the compatibility level of the current database to a higher value to enable this feature. See help for the SET COMPATIBILITY_LEVEL option of ALTER DATABASE.
When you change the compatibility level to 100 the error goes away and the sproc is created.
September 9, 2013 at 1:17 pm
What you are seeing can drive you nuts if you have to go back and forth in compatibility. Datatypes in 2008 are available even in 2005 mode (like the table parameter, it is just a custom datatype). However, language features that were introduced in 2008 will not be available (like MERGE).
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 9, 2013 at 1:25 pm
Great so I am not totally crazy. I thought it was very strange that I can even fill the TVP in 2005 compatibility. It has to do with what you just said thanks.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply