Data Synchronisation Issue

I am not quite a newbie in the area of 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 mdb 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
TonySee inline
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
<ace join_to ware@.iinet.net.au (Tony Epton)> wrote in message
news:41abdc2f.3023390@.news.m.iinet.net.au...
> I am not quite a newbie in the area of 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 mdb 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.
>
If you are running a single process, then each query is complete before the
next one begins.

> 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.
SQL table names can be up to 128 characters long... I suspect you are seeing
some Access restriction, not a sql restriction.

> 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 ?
XP DSNs hold login/password if you choose NOT to use trusted
authentication... The create DSN walks you through this... Also, make sure
you are using a system DSN, so it will be valid for any user on the
machine...
> Many thanks in advance
> Tony