Default created with wrong name

Forum for users of SQL Packager database archive utility

Moderator: David Atkinson

Default created with wrong name

Postby powervision » Tue Jan 29, 2008 2:36 pm

Hi,

This problem apply's to Doc1 and Sql packager.
In the past i had an default value (sql 2005) named "NegOne". I deleted that default and created a new one. "NegativeOne". (This default is used in some custom data types). When i create a snapshot of the database a file is created with the name: NegativeOne. It contains this line (and some others): "create default [NegativeOne] as -1" So far so good.

When i create a exe with SQL Packager 5 the default is created with the old name (copy from script): "create default [NegOne] as -1". All other sql in the same scriptfile uses "NegativeOne" as default and the scripts fails to run (thats when i notist). I created html documentation with docman and this tool shows this:
-----------------
Default - [dbo].[NegativeOne]
(local) > CSS_WH > Defaults > [dbo].[NegativeOne]
Quick Links
Properties SQL Script Uses Used By
Properties
Property Value
Owner dbo

SQL Script
SET QUOTED_IDENTIFIER OFF
GO
create default [NegOne] as -1

Uses
----------------

notice: Default - [dbo].[NegativeOne]<>create default [NegOne] as -1

i scaned my database compleetly, there is no "NegOne" default, reference or something with this name.

Question: where does "Doc1" en "Packager 5" gets the name used to create the scripts?

(i use all the latest version's of the software (toolbelt).

Regards,
Pepijn Visser
powervision
 
Posts: 3
Joined: Tue Jan 29, 2008 2:22 pm

Postby Brian Donahue » Wed Jan 30, 2008 7:24 pm

Hello Pepijn,

SQL Packager gets the name of objects by parsing the SQL code used to create the object in the case of UDFs, stored procedures, and defaults. When SQL Server is used to rename objects, typically it updates the sysobjects table and not the syscomments table, where the actual ddl is stored.

For instance, running
Code: Select all
SELECT [text] FROM syscomments WHERE [id] IN (SELECT id FROM sysobjects WHERE [NAME]='NegOne')
More than likely, the result will show CREATE DEFAULT NeagtiveOne as... and that's the problem.

The only way around I know of is to drop and recreate the default.
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am

Drop and recreate

Postby powervision » Thu Jan 31, 2008 2:12 pm

Hi,

Thanx for the reply brian. I recreated the default and everyting works fine.

regards,
Pepijn Visser
powervision
 
Posts: 3
Joined: Tue Jan 29, 2008 2:22 pm


Return to SQL Packager Previous Versions

Who is online

Users browsing this forum: No registered users and 0 guests