| Author |
Message |
johnwebbcole
Joined: 30 Jan 2007
Posts: 5
|
 Posted: Tue Feb 27, 2007 7:17 pm Post subject: issue with mssql spatial extensions... |
 |
|
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 |
|
 |
Brian Donahue
Joined: 23 Aug 2004
Posts: 6369
Location: Red Gate Software
|
| Posted: Tue Mar 06, 2007 12:15 pm Post subject: |
|
|
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
Technical Support
Red Gate Software Ltd.
44 (0)870 160 0037 ext 8521
US and CAN 1-866-RED GATE ext 8521 |
|
| Back to top |
|
|
Greg Mack
Joined: 14 May 2008
Posts: 5
|
| Posted: Wed May 14, 2008 7:56 pm Post subject: CLR Based Functions |
|
|
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 |
|
|
Brian Donahue
Joined: 23 Aug 2004
Posts: 6369
Location: Red Gate Software
|
| Posted: Thu May 15, 2008 10:19 am Post subject: |
|
|
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
Technical Support
Red Gate Software Ltd.
44 (0)870 160 0037 ext 8521
US and CAN 1-866-RED GATE ext 8521 |
|
| Back to top |
|
|
Greg Mack
Joined: 14 May 2008
Posts: 5
|
| Posted: Thu May 15, 2008 4:43 pm Post subject: Version Number |
|
|
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 |
|
|
Greg Mack
Joined: 14 May 2008
Posts: 5
|
| Posted: Thu May 15, 2008 6:12 pm Post subject: Error still exists |
|
|
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 |
|
|
Greg Mack
Joined: 14 May 2008
Posts: 5
|
| Posted: Wed May 28, 2008 6:02 pm Post subject: Known Issue |
|
|
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 |
|
|
|
