issue with mssql spatial extensions...

johnwebbcole

I've packaged a databse that uses mssql spatial extensions (http://www.codeplex.com/MsSqlSpatial) on 2005 sp2 and I'm getting the following error when I run the package:

http://www.codeplex.com/MsSqlSpatial

<SQLError Package="HUDNPS"><Error>Line 1: Length or precision specification 0 is invalid.
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
Incorrect syntax near the keyword 'EXTERNAL'.</Error><Assembly>HUDNPSStore1</Assembly><Package>Package2.resx</Package><Batch>Batch1204</Batch><SQL>CREATE FUNCTION [ST].[MPointFromText] (@wkt [nvarchar] (0), @srid [int]=N'-1')
RETURNS [varbinary] (max)
WITH EXECUTE AS CALLER
EXTERNAL NAME [MsSqlSpatialLibrary].[UserDefinedFunctions].[MPointFromText]</SQL></SQLError>

From what I can tell, this is the correct syntax for this function. How can I get this DB loaded using Packager 5?

Thanks,

John Cole

Brian Donahue

Hello John,

It sounds like you have an assembly, and the database that you are migrating to isn't capable of running the script to migrate that assembly for some reason.

Does this assembly need an asymmetric key in the database, or have the database set to allow UNSAFE? Packager can't migrate keys and I don't think it supports all database settings, and I think you may be able to get it to work by running ALTER DATABASE SET TRUSTWORTHY ON to allow external assemblies to run.

Greg Mack

I am working with scripting my database and I have received a similar error as the one listed previously.

RedGate scripted the CLR function as:

CREATE FUNCTION [dbo].[GetRecordHistoryDataString] (@RecordHistoryID [bigint])
RETURNS [nvarchar] (0)
WITH EXECUTE AS CALLER
EXTERNAL NAME [ARecordHistorySecurity].[RecordHistoryAccessor].[GetDataString]

but MSSQL throws this error:
Msg 1001, Level 15, State 1, Procedure GetRecordHistoryDataString, Line 2
Line 2: Length or precision specification 0 is invalid.
Msg 319, Level 15, State 1, Procedure GetRecordHistoryDataString, Line 3
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
Msg 156, Level 15, State 1, Procedure GetRecordHistoryDataString, Line 4
Incorrect syntax near the keyword 'EXTERNAL'.

If I use MS SQL script the function it scripts it as:

CREATE FUNCTION [dbo].[GetRecordHistoryDataString](@RecordHistoryID [bigint])
RETURNS [nvarchar](max) WITH EXECUTE AS OWNER
AS 
EXTERNAL NAME [ARecordHistorySecurity].[RecordHistoryAccessor].[GetDataString]

This version works. Do you know why there is a difference with two versions, and if the RedGate Packager supports Creating CLR Functions?
http://msdn.microsoft.com/en-us/library/ms189876.aspx

Thank you for the help,
Greg Mack

Brian Donahue

I think this is something slightly different entirely -- it's exposing a lack of support for nvarchar (MAX) in general. To versions of SQL Compare and packager that didn't support SQL Server 2005, an nvarchar (max) column would appear as nvarchar(0). Can you confirm that you are using SQL Packager 5.4?

Thanks!

Greg Mack

I am using version 5.3.0

I will get the 5.4 release and post what I find. Thanks for the tip.

Greg

Greg Mack

I have uninstalled and updated the SQL Packager and the error still exists. The Version number shows 5.4.0.89

After changing the line

RETURNS [nvarchar] (0)

to

RETURNS [nvarchar](max)

it executes without error. I have found 2 more incidences and they are both within the create statement of Functions. One also contains it as a parameter:

CREATE FUNCTION [dbo].[TranslateXMLChangesIntoChangeString] (@XMLChanges [nvarchar] (0), @EntityID [bigint])
RETURNS [nvarchar] (0)

I can make the changes to the project, but is there something I could do to get the packager to correct this for me?

Thank you for the help,
Greg

Greg Mack

I have recieved this information from support:

I'm afraid that this is a known bug in the SQL compare 5.x engine which SQL Packager uses.

This has been fixed in SQL Compare 6.0 (http://www.red-gate.com/messageboard/viewtopic.php?t=5123&highlight=max+nvarchar). We are planning to update all of our software to fully support SQL Server 2008 and release these updates to coincide with the release of SQL Server 2008 (Q3). When SQL Packager is updated it will use the latest version of the SQL Compare engine which will resolve the issue that you're seeing.

I hope this helps.

Greg