Problem with Packager 6 and SQL 2000

Packages and compresses schema and contents of SQL Server databases

Moderators: JonathanWatts, Chris Auckland, David Atkinson, david connell, Anu Deshpande, Michelle Taylor, james.billings, Marianne

Problem with Packager 6 and SQL 2000

Postby RBeaubien » Mon Dec 29, 2008 7:24 pm

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.
- Robert Beaubien
- Kool Software
RBeaubien
 
Posts: 19
Joined: Mon Jun 25, 2007 6:13 pm

Postby RBeaubien » Mon Dec 29, 2008 7:26 pm

BTW, I am running Packager 6.0.0.107
- Robert Beaubien
- Kool Software
RBeaubien
 
Posts: 19
Joined: Mon Jun 25, 2007 6:13 pm

Postby RBeaubien » Wed Dec 31, 2008 5:17 pm

I'm so thankful for such a quick response... :cry:
- Robert Beaubien
- Kool Software
RBeaubien
 
Posts: 19
Joined: Mon Jun 25, 2007 6:13 pm

Postby Brian Donahue » Wed Dec 31, 2008 6:46 pm

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

Postby RBeaubien » Wed Dec 31, 2008 7:05 pm

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?
- Robert Beaubien
- Kool Software
RBeaubien
 
Posts: 19
Joined: Mon Jun 25, 2007 6:13 pm

Postby Brian Donahue » Fri Jan 02, 2009 10:24 am

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


Return to SQL Packager 6

Who is online

Users browsing this forum: No registered users and 0 guests