issue with mssql spatial extensions...

Forum for users of SQL Packager database archive utility

Moderator: David Atkinson

issue with mssql spatial extensions...

Postby johnwebbcole » Tue Feb 27, 2007 7:17 pm

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
johnwebbcole
 
Posts: 5
Joined: Tue Jan 30, 2007 4:29 pm

Postby Brian Donahue » Tue Mar 06, 2007 12:15 pm

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.
Brian Donahue
 
Posts: 6668
Joined: Mon Aug 23, 2004 10:48 am

CLR Based Functions

Postby Greg Mack » Wed May 14, 2008 7:56 pm

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:

Code: Select all
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:
Code: Select all
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
Greg Mack
 
Posts: 5
Joined: Wed May 14, 2008 7:40 pm

Postby Brian Donahue » Thu May 15, 2008 10:19 am

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!
Brian Donahue
 
Posts: 6668
Joined: Mon Aug 23, 2004 10:48 am

Version Number

Postby Greg Mack » Thu May 15, 2008 4:43 pm

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
 
Posts: 5
Joined: Wed May 14, 2008 7:40 pm

Error still exists

Postby Greg Mack » Thu May 15, 2008 6:12 pm

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
Code: Select all
RETURNS [nvarchar] (0)
to
Code: Select all
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:
Code: Select all
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
 
Posts: 5
Joined: Wed May 14, 2008 7:40 pm

Known Issue

Postby Greg Mack » Wed May 28, 2008 6:02 pm

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
Greg Mack
 
Posts: 5
Joined: Wed May 14, 2008 7:40 pm


Return to SQL Packager Previous Versions

Who is online

Users browsing this forum: No registered users and 0 guests