SQL Server 2005 Primary Key GUID Queries fail

[Guest]

Hi.

We have two systems that need to queried on. One is in MS Access 2003 and
the other is in SQL Server 2005. We have Long Integer ID fields in the SQL
Server 2005 to allow the link to Access.

I'm having a real tough time trying to create queries in Access using SQL
Server 2005 and Access tables as sources of data. eg. If I need to have one
table in Access link to another in SQL via the Long Integer, that's fine, but
if I need the SQL table to then link to another SQL table via the GUID then
it returns no data?!?!?

Unfortunately the SQL Server tables have GUID primary keys, so when you join
tables together and add where clauses, it brings back no data. If you perform
Left or Right joins then it only brings back data for the tables that you
apply where clauses to.

I've created views but you then either have the view so large to handle all
the queries that need to use it, which slows it down or you have so many
views that you don't know what each really does.

I've also had many problems joining Access tables that have a primary key as
a Replication ID data type to the SQL Server 2005 tables as well.

Does anyone know what either the Office Team or the SQL Server Team are
doing about GUID interoperability?

 

[Stefan Hoffmann]

hi Cheval,

I've also had many problems joining Access tables that have a primary key as
a Replication ID data type to the SQL Server 2005 tables as well.

This works for me using Access 2003.

The only thing which won't work well are inserts on linked tables where
the primary key consists of a uniqueidentifier (Replication-ID) field.

Maybe you can post a simple example.


mfG
--> stefan <--

 

[Guest]

Hi Stefan,

No I've been able to insert records fine and even get the new SQL Server
Primary Key GUID through a neat trick.

From within Access, these are very simple queries that return no data in the
joined tables!?!?! Why???? I've checked and there are the correct GUIDs on
both sides of the join, but the query goes "dunno".

SELECT dbo_Item.Barcode, dbo_Shop.ShopName
FROM dbo_Shop INNER JOIN dbo_Item ON dbo_Shop.ShopGUID = dbo_Item.ShopGUID
WHERE (((Len(dbo_Item.[Barcode]))<6)) OR (((dbo_Item.[Barcode]) Like
"*[!0-9]*"));

I've read that Access Stores GUIDs in a different format to SQL Server and
has to convert them when doing the ODBC calls. Is there a workaround to this?

 

[Stefan Hoffmann]

hi Cheval,

No I've been able to insert records fine and even get the new SQL Server
Primary Key GUID through a neat trick.

That's fine.

From within Access, these are very simple queries that return no data in the
joined tables!?!?! Why???? I've checked and there are the correct GUIDs on
both sides of the join, but the query goes "dunno".
SELECT dbo_Item.Barcode, dbo_Shop.ShopName
FROM dbo_Shop INNER JOIN dbo_Item ON dbo_Shop.ShopGUID = dbo_Item.ShopGUID
WHERE (((Len(dbo_Item.[Barcode]))<6)) OR (((dbo_Item.[Barcode]) Like
"*[!0-9]*"));

It works here sing a linked table and a native Jet table joined by

I've read that Access Stores GUIDs in a different format to SQL Server and
has to convert them when doing the ODBC calls. Is there a workaround to this?

Check the linked table, is the GUID really recognized as Replication ID?
You post your SQL Server table create statement.

mfG
--> stefan <--

 

[Guest]

Hi Stefan,

CREATE TABLE [dbo].[Shop](
[ShopGUID] [dbo].[DPrimaryKey] ROWGUIDCOL NOT NULL CONSTRAINT [ShopAUTO]
DEFAULT (newsequentialid()),
etc.

The user defined type dbo.DPrimaryKey is:
CREATE TYPE [dbo].[DPrimaryKey] FROM [uniqueidentifier] NOT NULL

hi Cheval,

No I've been able to insert records fine and even get the new SQL Server
Primary Key GUID through a neat trick.

That's fine.

From within Access, these are very simple queries that return no data in the
joined tables!?!?! Why???? I've checked and there are the correct GUIDs on
both sides of the join, but the query goes "dunno".
SELECT dbo_Item.Barcode, dbo_Shop.ShopName
FROM dbo_Shop INNER JOIN dbo_Item ON dbo_Shop.ShopGUID = dbo_Item.ShopGUID
WHERE (((Len(dbo_Item.[Barcode]))<6)) OR (((dbo_Item.[Barcode]) Like
"*[!0-9]*"));

It works here sing a linked table and a native Jet table joined by

I've read that Access Stores GUIDs in a different format to SQL Server and
has to convert them when doing the ODBC calls. Is there a workaround to this?

Check the linked table, is the GUID really recognized as Replication ID?
You post your SQL Server table create statement.

mfG
--> stefan <--

 

[Stefan Hoffmann]

hi Cheval,

PLEASE: Don't use ">" (greater sign) as delimiter for the tofu. This is
a reserved sign for quoted lines.

The following works for me, on difference you don't have a primary key
in your table:

T-SQL---
CREATE TABLE [dbo].[Shop](
[ShopGUID] [dbo].[DPrimaryKey] ROWGUIDCOL NOT NULL CONSTRAINT
[ShopAUTO] DEFAULT (newsequentialid()),
[payload] [nvarchar](32) NOT NULL,
CONSTRAINT [PK_Table_123] PRIMARY KEY CLUSTERED
(
[ShopGUID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Table_2](
[ID] [int] IDENTITY(1,1) NOT NULL,
[GUID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_Table_2_GUID]
DEFAULT (newid()),
[payload] [nchar](10) NOT NULL,
CONSTRAINT [PK_Table_2] PRIMARY KEY CLUSTERED
(
[GUID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
---

Query---
SELECT dbo_Shop.payload, dbo_Table_2.payload, dbo_Table_2.ID
FROM dbo_Shop INNER JOIN dbo_Table_2 ON dbo_Shop.ShopGUID =
dbo_Table_2.GUID;

 

[Guest]

Yes, like I said the basic queries work but multi-table ones fail, especially
where you have where clauses in them.

Try this, create a SQL Server 2005 table, create an access table and then
create a query linking them together and you get no results back.

Sql Server table.
CREATE TABLE [dbo].[TtlPerson](
[TtlPersonGUID] [dbo].[DPrimaryKey] ROWGUIDCOL NOT NULL CONSTRAINT
[TtlPersonAUTO] DEFAULT (newsequentialid()),
[TtlPersonName] [varchar](255) NOT NULL,

Add some example data to the SQL Server table...

Create the Access Table:
SELECT TOP 25 dbo_TtlPerson.TtlPersonGUID, dbo_TtlPerson.TtlPersonName INTO
zzTest
FROM dbo_TtlPerson;

Run the Query:
SELECT dbo_TtlPerson.TtlPersonName, zzTest.TtlPersonName
FROM dbo_TtlPerson INNER JOIN zzTest ON dbo_TtlPerson.TtlPersonGUID =
zzTest.TtlPersonGUID;

0 Results?!? Even if you set the zzTest.TtlPersonGUID field to be the
primary key!?!

 

[Stefan Hoffmann]

hi Cheval,

Run the Query:
0 Results?!? Even if you set the zzTest.TtlPersonGUID field to be the
primary key!?!

Nope, i'll get a full match.

Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86) Mar 23 2007
16:28:52 Copyright (c) 1988-2005 Microsoft Corporation Developer
Edition on Windows NT 6.0 (Build 6000: )

Microsoft Office Access 2003 () SP2


mfG
--> stefan <--

 

[Guest]

That's the same as us on Windows 2003 R2.

What collation? Ours is Latin1_General_CI_AS

 

[Stefan Hoffmann]

hi Cheval,

What collation? Ours is Latin1_General_CI_AS

It the same on my test tables.

You may check MDAC/Jet for updates.

Can you dump your test table?


mfG
--> stefan <--

 

[Guest]

Thanks for you help stefan by the way.

How do you mean "dump". Do you mean Drop, Delete or Recreate? Then yes.

I checked both Jet and MDAC. What are your version?

Jet:
- version 4.0.8618.0 Windows XP SP2 and Security Bulletin MS04-014

MDAC :
---------------------------
ComponentChecker 2.0
---------------------------
The following product releases were matched:

MDAC 2.8 SP1 ON WINDOWS XP SP2

Would you like to re-run the query for a specific product release?

 

[Stefan Hoffmann [MVP]]

hi Cheval,

How do you mean "dump". Do you mean Drop, Delete or Recreate? Then yes.

Nope. Create a backup of SQL Server database, I think the problem
originates from it. Post it, if possible.


mfG
--> stefan <--

 

[Guest]

Hi Stefan,

Unfortunately that is not an option. It's a 30Gig replicated database.

Things are not looking good.

 

[DAVID]

There was a hotfix, still current 6 months ago:

WindowsXP-KB895751-v2-x86-ENU.exe

also, kind of old, but my favorite on the subject:

http://groups.google.com.au/group/microsoft.public.access.modulesdaovba/msg/fe9290a80eb2ef4c


btw: I HATE GOOGLE GROUPS.
Search interface is broken.
Index is broken.
Search is broken.

(david)

 

[Guest]

More on this.

The situation is even more weird. I did the following:

1) Create a table on another Sql Server 2005 Standard machine with a Primary
Key GUID and a ShopName varchar 50 fields.
2) I then added data from the original Server Shop table.
3) I then created a brand new Access 2003 database and linked the two
servers database tables.
4) I then created an Access table with a ReplicationID as the primary and a
text 50 as the Shop name.
5) I then inserted the data from the new Sql Server to the Access table.
6) I then created a query and joined the Access table to the new Sql Server.
6a) Success! All the rows were returned.
7) I then created a query to joined the Access table to the old Sql Server.
7a) Success! All rows returned.
8) I then created a query to join the Access table to both the old and new
Sql Server.
8a) FAIL! No rows returned.
9) I created a query to join the Access table to the old Sql table and then
join to another old sql tables.
9a) FAIL! No Rows returned.

Basically it is very difficult to know whether there is no data or the query
fails to run properly...

 

[Guest]

Hi David.

Thanks. I'll contact Microsoft and get the hotfix as we'll not be waiting
for Windows XP SP3.

 

[Stefan Hoffmann]

hi Cheval,

Basically it is very difficult to know whether there is no data or the query
fails to run properly...

Okay, then use the SQL Profiler to compare your working query with your
failing one. I think it is weird kind of charset problem, maybe you are
running in an implicit cast.


mfG
--> stefan <--

 

[Guest]

No, not a charset but bug that has been fixed in April 2005!?! awaiting
Windows Service Packs for it to be released!

This will be fixed with the Windows XP Service Pack 3 (SP3) & Windows 2000
Service Pack (SP5) for Access 2003 Jet. The hotfix article is KB895751.

Since installing this hotfix I've had no problems at all. Apparently the
Office team gave in to the SQL Server team.

Thank for all your help Stefan and also David for the solution.

What the most painful part of this whole event, is that I've been pulling my
hair out over this for the last 2 months, when it has been fixed since April
2005.