Asp Forum - 2005 ROWGUID/UniqueIdentifer not initializing - microsoft.public.sqlserver.programming

Shawn Mason

3/19/2007 9:54:00 PM

Hi,

I have a client who has used the ROWGUID in 2000 with a NewID() in the
default section. It worked fine in 2000 and creatd the ROWGUID value for
them but after having moved to 2005 this no longer seems to function.

Any ideas?

Shawn

11 Answers

David Portas

3/19/2007 10:11:00 PM

On 19 Mar, 21:54, "Shawn Mason" <s...@issda.com> wrote:
> Hi,
>
> I have a client who has used the ROWGUID in 2000 with a NewID() in the
> default section. It worked fine in 2000 and creatd the ROWGUID value for
> them but after having moved to 2005 this no longer seems to function.
>
> Any ideas?
>
> Shawn

Have you been able to reproduce the problem? Please post some code if
possible and state what version and build you are using (SELECT
SERVERPROPERTY('ProductVersion');). You can use Profiler to inspect
the statements actually being executed.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/librar...(en-US,SQL.90).aspx
--

--CELKO--

3/19/2007 10:18:00 PM

>> I have a client who has used the ROWGUID in 2000 with a NewID() in the default section. It worked fine in 2000 and creatd the ROWGUID value for them but after having moved to 2005 this no longer seems to function. <<

If you are not using the GUIDs for replication, can you get a
relational design instead of this proprietary non-key stuff into the
schema?

Shawn Mason

3/20/2007 12:47:00 AM

Hmm, perhaps I didn't do a good job of explaining.

As much as I would love to change the ROWGUID's to Identity columns that
isn't possible right now. but, the column is setup as a uniqueidentifier
with the ROWGUID attribute set to yes. In the default value of the Column
Properties I have place "(newid())" (without quotes). If you try to add a
row either by a simple insert or using the "open table" and add inside that
grid, in SQL 2000 it created the uniqueidentifer just fine BUT in 2005 it
does not.

So, does that help?

Shawn

"Shawn Mason" <shawn@issda.com> wrote in message
news:ehdlkEnaHHA.4940@TK2MSFTNGP05.phx.gbl...
> Hi,
>
> I have a client who has used the ROWGUID in 2000 with a NewID() in the
> default section. It worked fine in 2000 and creatd the ROWGUID value for
> them but after having moved to 2005 this no longer seems to function.
>
> Any ideas?
>
> Shawn
>

--CELKO--

3/20/2007 1:33:00 AM

>> S..QL 2000 it created the uniqueidentifer just fine BUT in 2005 it does not. <<

This is an example of why we should write Standard, portable code.
Porting is also moving to a new releazse of the same product. <<inser
Celko lecture here>>.

I would bet that Kalen D. knows some internal trick that would help
you move your data over and get it working.

Shawn Mason

3/20/2007 4:15:00 AM

No disagreements from me but I didn't write the code, I just get to fix the
problem <g>.

Shawn

"--CELKO--" <jcelko212@earthlink.net> wrote in message
news:1174354373.118767.266030@b75g2000hsg.googlegroups.com...
>>> S..QL 2000 it created the uniqueidentifer just fine BUT in 2005 it does
>>> not. <<
>
> This is an example of why we should write Standard, portable code.
> Porting is also moving to a new releazse of the same product. <<inser
> Celko lecture here>>.
>
> I would bet that Kalen D. knows some internal trick that would help
> you move your data over and get it working.
>

Tony Rogerson

3/20/2007 5:32:00 AM

How did they 'move' to 2005?

My guess is they imported the data using the import/export on management
studio and the default property (and a lot of other stuff) hasn't been
copied across.

You need to post the scripted create table for the problem table.

Many thanks,
Tony.

"Shawn Mason" <shawn@issda.com> wrote in message
news:ehdlkEnaHHA.4940@TK2MSFTNGP05.phx.gbl...
> Hi,
>
> I have a client who has used the ROWGUID in 2000 with a NewID() in the
> default section. It worked fine in 2000 and creatd the ROWGUID value for
> them but after having moved to 2005 this no longer seems to function.
>
> Any ideas?
>
> Shawn
>

Tony Rogerson

3/20/2007 5:58:00 AM

> This is an example of why we should write Standard, portable code.
> Porting is also moving to a new releazse of the same product. <<inser
> Celko lecture here>>.

What, like use 1,000 parameters?

http://sqlblogcasts.com/blogs/tonyrogerson/archive/2007/03/17/joe-celko-don-t-use-csv-xml-use-1-000-parameters-in...

"--CELKO--" <jcelko212@earthlink.net> wrote in message
news:1174354373.118767.266030@b75g2000hsg.googlegroups.com...
>>> S..QL 2000 it created the uniqueidentifer just fine BUT in 2005 it does
>>> not. <<
>
> This is an example of why we should write Standard, portable code.
> Porting is also moving to a new releazse of the same product. <<inser
> Celko lecture here>>.
>
> I would bet that Kalen D. knows some internal trick that would help
> you move your data over and get it working.
>

Shawn Mason

3/20/2007 6:18:00 AM

To duplicate it I simply created a test table using this:

CREATE TABLE [dbo].[ROWGUID_Tesat](

[myROWGUID] [uniqueidentifier] ROWGUIDCOL NOT NULL CONSTRAINT
[DF_ROWGUID_Tesat_myROWGUID] DEFAULT (newid()),

[name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

CONSTRAINT [PK_ROWGUID_Tesat] PRIMARY KEY CLUSTERED

(

[myROWGUID] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

The above creates a simple 2 column table with the first being the
uniqueIdentifier with ROWGUID turned on with the NewID() for its default
value. The second is just a varchar column to fill in something. This
simple example fails.

Shawn

"Tony Rogerson" <tonyrogerson@torver.net> wrote in message
news:CDE0FD71-5395-4EF8-A557-9AC2627967ED@microsoft.com...
> How did they 'move' to 2005?
>
> My guess is they imported the data using the import/export on management
> studio and the default property (and a lot of other stuff) hasn't been
> copied across.
>
> You need to post the scripted create table for the problem table.
>
> Many thanks,
> Tony.
>
>
> "Shawn Mason" <shawn@issda.com> wrote in message
> news:ehdlkEnaHHA.4940@TK2MSFTNGP05.phx.gbl...
>> Hi,
>>
>> I have a client who has used the ROWGUID in 2000 with a NewID() in the
>> default section. It worked fine in 2000 and creatd the ROWGUID value for
>> them but after having moved to 2005 this no longer seems to function.
>>
>> Any ideas?
>>
>> Shawn
>>
>

David Portas

3/20/2007 6:53:00 AM

On 20 Mar, 06:18, "Shawn Mason" <s...@issda.com> wrote:
> To duplicate it I simply created a test table using this:
>
> CREATE TABLE [dbo].[ROWGUID_Tesat](
>
> [myROWGUID] [uniqueidentifier] ROWGUIDCOL NOT NULL CONSTRAINT
> [DF_ROWGUID_Tesat_myROWGUID] DEFAULT (newid()),
>
> [name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
>
> CONSTRAINT [PK_ROWGUID_Tesat] PRIMARY KEY CLUSTERED
>
> (
>
> [myROWGUID] ASC
>
> )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
>
> ) ON [PRIMARY]
>
> The above creates a simple 2 column table with the first being the
> uniqueIdentifier with ROWGUID turned on with the NewID() for its default
> value. The second is just a varchar column to fill in something. This
> simple example fails.
>

Please be more explicit. What exactly did you run and what does
"fails" mean? I tried the following and I got the expected result with
no error message. Please could you try the same and post the output.

If you are referring to editing a table using Open Table then maybe
you are just encountering some behaviour change in the UI. I would not
usually recommend that method for performing updates.

INSERT INTO dbo.ROWGUID_Tesat (name) VALUES ('');
SELECT SERVERPROPERTY('ProductVersion');
SELECT * FROM dbo.ROWGUID_Tesat;

Result:

--------------------------------------------------------------------------------------
Microsoft SQL Server 2005 - 9.00.3050.00 (Intel X86)
Mar 2 2007 20:01:28
Copyright (c) 1988-2005 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

(1 row(s) affected)

myROWGUID
name
------------------------------------
--------------------------------------------------
879EDC96-7370-41DD-B383-E198FC7B09FF

(1 row(s) affected)

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/librar...(en-US,SQL.90).aspx
--

Tony Rogerson

3/20/2007 7:47:00 AM

Hi Shawn,

How are you inserting data into this table?

Also, make sure the client hasn't changed their database compatibility
level, if its 70 you get this error....

Msg 325, Level 15, State 1, Line 7
Incorrect syntax near 'COLLATE'. You may need to set the compatibility level
of the current database to a higher value to enable this feature. See help
for the stored procedure sp_dbcmptlevel.

On compatibility mode 80 it still works fine...

insert [ROWGUID_Tesat] ( name ) values ( 'adsasda' )
select *
from [ROWGUID_Tesat]

What is the output of PRINT @@VERSION?

What is the output of sp_helpdb <dbname>

Tony.

"Shawn Mason" <shawn@issda.com> wrote in message
news:uamHGeraHHA.596@TK2MSFTNGP06.phx.gbl...
> To duplicate it I simply created a test table using this:
>
> CREATE TABLE [dbo].[ROWGUID_Tesat](
>
> [myROWGUID] [uniqueidentifier] ROWGUIDCOL NOT NULL CONSTRAINT
> [DF_ROWGUID_Tesat_myROWGUID] DEFAULT (newid()),
>
> [name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
>
> CONSTRAINT [PK_ROWGUID_Tesat] PRIMARY KEY CLUSTERED
>
> (
>
> [myROWGUID] ASC
>
> )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
>
> ) ON [PRIMARY]
>
>
>
> The above creates a simple 2 column table with the first being the
> uniqueIdentifier with ROWGUID turned on with the NewID() for its default
> value. The second is just a varchar column to fill in something. This
> simple example fails.
>
> Shawn
>
>
> "Tony Rogerson" <tonyrogerson@torver.net> wrote in message
> news:CDE0FD71-5395-4EF8-A557-9AC2627967ED@microsoft.com...
>> How did they 'move' to 2005?
>>
>> My guess is they imported the data using the import/export on management
>> studio and the default property (and a lot of other stuff) hasn't been
>> copied across.
>>
>> You need to post the scripted create table for the problem table.
>>
>> Many thanks,
>> Tony.
>>
>>
>> "Shawn Mason" <shawn@issda.com> wrote in message
>> news:ehdlkEnaHHA.4940@TK2MSFTNGP05.phx.gbl...
>>> Hi,
>>>
>>> I have a client who has used the ROWGUID in 2000 with a NewID() in the
>>> default section. It worked fine in 2000 and creatd the ROWGUID value
>>> for them but after having moved to 2005 this no longer seems to
>>> function.
>>>
>>> Any ideas?
>>>
>>> Shawn
>>>
>>
>
>

microsoft.public.sqlserver.programming

2005 ROWGUID/UniqueIdentifer not initializing

Shawn Mason

David Portas

--CELKO--

Shawn Mason

--CELKO--

Shawn Mason

Tony Rogerson

Tony Rogerson

Shawn Mason

David Portas

Tony Rogerson

x Login to ForumsZone