Red Gate forums :: View topic - issue with mssql spatial extensions...
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Packager Previous Versions
SQL Packager Previous Versions forum

issue with mssql spatial extensions...

Search in SQL Packager Previous Versions forum
Post new topic   Reply to topic
Jump to:  
Author Message
johnwebbcole



Joined: 30 Jan 2007
Posts: 5

PostPosted: Tue Feb 27, 2007 7:17 pm    Post subject: issue with mssql spatial extensions... Reply with quote

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
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6582

PostPosted: Tue Mar 06, 2007 12:15 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Greg Mack



Joined: 14 May 2008
Posts: 5

PostPosted: Wed May 14, 2008 7:56 pm    Post subject: CLR Based Functions Reply with quote

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:
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:
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
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6582

PostPosted: Thu May 15, 2008 10:19 am    Post subject: Reply with quote

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!
Back to top
View user's profile Send private message
Greg Mack



Joined: 14 May 2008
Posts: 5

PostPosted: Thu May 15, 2008 4:43 pm    Post subject: Version Number Reply with quote

I am using version 5.3.0

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

Greg
Back to top
View user's profile Send private message
Greg Mack



Joined: 14 May 2008
Posts: 5

PostPosted: Thu May 15, 2008 6:12 pm    Post subject: Error still exists Reply with quote

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:
RETURNS [nvarchar] (0)
to
Code:
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:
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
Back to top
View user's profile Send private message
Greg Mack



Joined: 14 May 2008
Posts: 5

PostPosted: Wed May 28, 2008 6:02 pm    Post subject: Known Issue Reply with quote

I have recieved this information from support:

Quote:

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
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group