Synchronisation Issue - SQL

[Tony Epton]

I am not quite a newbie with SQL server - more "knows just enough to
be dangerous"
- so please be gentle with me.
Cross posted to several groups - apologies if too far off topic

I have an application that does a lot of massaging of data (insurance
claims) from 3 different data sources to present one set of nice
homogeneous output tables. Uses about 200 various tables and about 300
queries to do the work ( sounds like a mess - but trust me - it is
quite disciplined)

We are using 4 * 1Gb backends and the client agrees it is time to move
the backend to SQL server. They have made it clear that they do not
wish to convert any of the queries to pass thru queries - ie - I am
just replacing my attachments to mdb tables with attachments to SQL
tables. They accept that it will probably run even slower due to the
extra SQL overheads.

I have dealt with most issues in the conversion but the 3 show
stoppers are:

1) Half way through the process I get a "record is deleted" message
when one of the queries attempts to run. I am guessing that there is a
synchronisation problem between any earlier query that empties a table
and an "append" query that refills it, and maybe a subsequent select
query that uses the refilled table.

Can anyone give me some code snippets in access to force a query to
flush all its results to SQL before I embark on the next query.

2) Initially I used the data transformation services to load all the
access backend tables across to SQL server.
I then retweaked my homebrew attachment routines to handle attaching
to an SQL table - everything worked fine.
Of course the tables were not updateable due to a lack of primary keys
in the SQL tables.
No problem - I worked my way through the SQL tables building
constraints and / or primary keys.
Then I found that my attachment routines would fail for some of the
tables - message being (paraphrasing) - "I can't find that table or
the table name is too long"
If I went back in to SQL server and shortened the table names down to
about 20 characters - then the problem went away.
I even adjusted my Access attachment routines so that I could still
keep the desired attached table names.
The problem is more for the client - when they go to point Cognos at
the SQL tables - they will need to do some reworking (or maybe Cognos
has an alias facility)
Can anyone shed any light on this situation.

3) When I set up DSN's on my (Win 98) machine - they appear to store
the user password quite happily.
On the client's (XP) machine - when my attachment routine runs - it
appears that the DSN is not holding the password anymore - and we get
prompted for the password, for every table that is being attached.
Can anyone explain why ?

Many thanks in advance
Tony

 

[Brian]

I am not quite a newbie with SQL server - more "knows just enough to
be dangerous"
- so please be gentle with me.
Cross posted to several groups - apologies if too far off topic

I have an application that does a lot of massaging of data (insurance
claims) from 3 different data sources to present one set of nice
homogeneous output tables. Uses about 200 various tables and about 300
queries to do the work ( sounds like a mess - but trust me - it is
quite disciplined)

We are using 4 * 1Gb backends and the client agrees it is time to move
the backend to SQL server. They have made it clear that they do not
wish to convert any of the queries to pass thru queries - ie - I am
just replacing my attachments to mdb tables with attachments to SQL
tables. They accept that it will probably run even slower due to the
extra SQL overheads.

I have dealt with most issues in the conversion but the 3 show
stoppers are:

1) Half way through the process I get a "record is deleted" message
when one of the queries attempts to run. I am guessing that there is a
synchronisation problem between any earlier query that empties a table
and an "append" query that refills it, and maybe a subsequent select
query that uses the refilled table.

Can anyone give me some code snippets in access to force a query to
flush all its results to SQL before I embark on the next query.

2) Initially I used the data transformation services to load all the
access backend tables across to SQL server.
I then retweaked my homebrew attachment routines to handle attaching
to an SQL table - everything worked fine.
Of course the tables were not updateable due to a lack of primary keys
in the SQL tables.
No problem - I worked my way through the SQL tables building
constraints and / or primary keys.
Then I found that my attachment routines would fail for some of the
tables - message being (paraphrasing) - "I can't find that table or
the table name is too long"
If I went back in to SQL server and shortened the table names down to
about 20 characters - then the problem went away.
I even adjusted my Access attachment routines so that I could still
keep the desired attached table names.
The problem is more for the client - when they go to point Cognos at
the SQL tables - they will need to do some reworking (or maybe Cognos
has an alias facility)
Can anyone shed any light on this situation.

3) When I set up DSN's on my (Win 98) machine - they appear to store
the user password quite happily.
On the client's (XP) machine - when my attachment routine runs - it
appears that the DSN is not holding the password anymore - and we get
prompted for the password, for every table that is being attached.
Can anyone explain why ?

Many thanks in advance
Tony

The only point I can throw any light on is the last one (3). My suspicion
is that when you are attaching the tables you are not actually storing the
password. So, how come it works on your machine but not your client's?
Probably because your Windows user has the necessary permissions to access
your SQL Server using NT authentication, whereas your client's Windows user
does NOT have such permissions, hence he/she is prompted to enter SQL Server
credentials.

If I'm correct, then to fix this EITHER your client's Windows user needs to
be given the necessary permissions, OR you need to store the SQL Server
password with the table links. How you do this depends on how exactly your
attachment routine works, but what you are needing to achieve is the
equivalent of ticking the "Remember Password" box when creating links
manually.

 

[Guest]

MDB against SQL is crazy; change to ADP.

ADP is fast, it is wonderful-- and it can reuse some of your existing
business logic.

It is all sql server; so you don't have to worry about the extra overhead of
linked tables.

aaron

 

[Brian]

MDB against SQL is crazy; change to ADP.

ADP is fast, it is wonderful-- and it can reuse some of your existing
business logic.

It is all sql server; so you don't have to worry about the extra overhead of
linked tables.

aaron

Nonsense, ADP has various problems, and ODBC linked tables have various
advantages. Search Google Groups for many, many previous discussions on
this subject. Or, to spark a heated debate, go to comp.databases.ms-access
and post the same comment there!