Problem with Packager 6 and SQL 2000

RBeaubien

I have a customer database I am trying to update to latest version and I am getting an error running the update. I never had a problem in the past using Packager 5, but Packager 6 seems to have an issue with it. It works when I attach their database to my SQL2005 server, but does not work on their SQL2000 server. The following is the error they get back:

<SQLError Package="TempFix"><Error>An invalid parameter or option was specified for procedure 'sp_addextendedproperty'.</Error><Assembly>TempFix</Assembly><Package>Package1.resx</Package><Batch>Batch22</Batch><SQL>EXEC sp_addextendedproperty N'MS_DiagramPane1', N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties =
Begin PaneConfigurations =
Begin PaneConfiguration = 0
NumPanes = 4
Configuration = "(H (1[40] 4[20] 2[20] 3) )"
End
Begin PaneConfiguration = 1
NumPanes = 3
Configuration = "(H (1 [50] 4 [25] 3))"
End
Begin PaneConfiguration = 2
NumPanes = 3
Configuration = "(H (1 [50] 2 [25] 3))"
End
Begin PaneConfiguration = 3
NumPanes = 3
Configuration = "(H (4 [30] 2 [40] 3))"
End
Begin PaneConfiguration = 4
NumPanes = 2
Configuration = "(H (1 [56] 3))"
End
Begin PaneConfiguration = 5
NumPanes = 2
Configuration = "(H (2 [66] 3))"
End
Begin PaneConfiguration = 6
NumPanes = 2
Configuration = "(H (4 [50] 3))"
End
Begin PaneConfiguration = 7
NumPanes = 1
Configuration = "(V (3))"
End
Begin PaneConfiguration = 8
NumPanes = 3
Configuration = "(H (1[56] 4[18] 2) )"
End
Begin PaneConfiguration = 9
NumPanes = 2
Configuration = "(H (1 [75] 4))"
End
Begin PaneConfiguration = 10
NumPanes = 2
Configuration = "(H (1[66] 2) )"
End
Begin PaneConfiguration = 11
NumPanes = 2
Configuration = "(H (4 [60] 2))"
End
Begin PaneConfiguration = 12
NumPanes = 1
Configuration = "(H (1) )"
End
Begin PaneConfiguration = 13
NumPanes = 1
Configuration = "(V (4))"
End
Begin PaneConfiguration = 14
NumPanes = 1
Configuration = "(V (2))"
End
ActivePaneConfig = 0
End
Begin DiagramPane =
Begin Origin =
Top = 0
Left = 0
End
Begin Tables =
Begin Table = "eq"
Begin Extent =
Top = 6
Left = 38
Bottom = 183
Right = 325
End
DisplayFlags = 280
TopColumn = 12
End
Begin Table = "el"
Begin Extent =
Top = 6
Left = 328
Bottom = 123
Right = 518
End
DisplayFlags = 280
TopColumn = 6
End
End
End
Begin SQLPane =
End
Begin DataPane =
Begin ParameterDefaults = ""
End
End
Begin CriteriaPane =
Begin ColumnWidths = 11
Column = 1440
Alias = 900
Table = 1170
Output = 720
Append = 1400
NewValue = 1170
SortType = 1350
SortOrder = 1410
GroupBy = 1350
Filter = 1350
Or = 1350
Or = 1350
Or = 1350
End
End
End
', 'SCHEMA', N'dbo', 'VIEW', N'EquipmentExceptions', NULL, NULL</SQL></SQLError>

Any help with this would be appreciated.

RBeaubien

BTW, I am running Packager 6.0.0.107

RBeaubien

I'm so thankful for such a quick response...

Brian Donahue

Thanks for your forum post. It seems you have run into a compatibility issue that is going to require you to make two different packages, one for SQL 2000 and one for 2005.

SQL Packager will produce SQL syntax compatible with the database being packaged -- it does not conditionally modify the SQL syntax at runtime. In your case, the problem is that the level0type of the extended property being executed is 'SCHEMA' and SQL 2000 only understands this as 'USER'. So if you were to change the failing query text from SCHEMA to USER, the query would succeed.

However, it may be better to produce a SQL 2000-compatible package anyway because other syntax problems could arise. To see how to produce a SQL 2000-compatible package from a SQL 2005 database, please reference the following knowledge base article:
http://www.red-gate.com/supportcenter/C ... 000150.htm

Hopefully this helps.

RBeaubien

The database I have attached is a sql2000 database. I copied there database from their server and attached it to mine. I can't do that other method since the customer already has data and is using it daily. Is this something I can find and modify in the script before the packager compiles the executable?

Brian Donahue

No, but you can save the scripts that packager creates, change them, and then use the "package SQL script" option in Packager to create the executable.