SEO

Hi Everyone,

All the very best for 2004!!

i need urgent help with this problem, the users are about to skin me
alive!!

we have an access front end with linked to sql server 2k tables.

when a user tries to insert a record into one of the tables it
"randomly" returns a generic ODBC error and fails to save. on other
occasions the same record will save.

a trace was applied and the following select seemed to appear right
after the insert statement whenever it failed:

select substring('NY',status/1024&1+1,1) from master..sysdatabases
where name=DB_NAME()

i had a look at other articles in the groups re this select statement,
but could not find a clear answer.

i have tried the insert statements as both SQL pass throughs and just
plain docmd.runsql's

can someone help me with the following:

* what is the purpose of the select?
* what other investigations can i do to get more info on why this
should be happening?
* how can i stop it?

the table i am doing the inserts into is showing as have a numeric
data type field in sqlserver, but the linked table shows this numeric
field as text - could this be the problem?? this field is not used in
the insert statement.

i could not find any references in the MS knowledge base.

any and all help would very gratefully received.

Edwinah63

edwinah@customercare.com.au (Edwinah63) wrote in message news:<d714e824.0401272127.5e23e461@posting.google. com>...
> Hi Everyone,
>
> All the very best for 2004!!
>
> i need urgent help with this problem, the users are about to skin me
> alive!!
>
> we have an access front end with linked to sql server 2k tables.
>
> when a user tries to insert a record into one of the tables it
> "randomly" returns a generic ODBC error and fails to save. on other
> occasions the same record will save.
>
> a trace was applied and the following select seemed to appear right
> after the insert statement whenever it failed:
>
> select substring('NY',status/1024&1+1,1) from master..sysdatabases
> where name=DB_NAME()
>
> i had a look at other articles in the groups re this select statement,
> but could not find a clear answer.
>
> i have tried the insert statements as both SQL pass throughs and just
> plain docmd.runsql's
>
> can someone help me with the following:
>
> * what is the purpose of the select?
> * what other investigations can i do to get more info on why this
> should be happening?
> * how can i stop it?
>
> the table i am doing the inserts into is showing as have a numeric
> data type field in sqlserver, but the linked table shows this numeric
> field as text - could this be the problem?? this field is not used in
> the insert statement.
>
> i could not find any references in the MS knowledge base.
>
> any and all help would very gratefully received.
>
> Edwinah63

The SELECT is checking if the database is read-only (see
"sysdatabases" in Books Online) - possibly a standard query from your
client library?

As for your INSERT issue, it's hard to say without more information,
but I would try capturing the failed INSERT statements using Profiler,
then execute them manually in Query Analyzer. You may get a more
precise error message that way. It may be a data type mismatch, if
Access is treating the column as character data instead of numeric
data, or it may be something else entirely.

If you are still having problems, perhaps you could post the DDL for
your table (CREATE TABLE statement), along with examples of INSERT
statements that work and do not work (from Profiler), as well as the
exact ODBC error.

Simon

Hi Simon,

thanks for your reply.

the select statement above came from sql profiler. i tried to apply
an odbc trace on the machine via the ODBC settings (Win 2k operating
system on client) but it crashed.

the exact error message is "ODBC-Connection to 'Trips' failed"

here is the T-SQL insert statement that seems to fail:

INSERT INTO Crmcalls (clalpha, consultant, Attitude, Category,
subcategory, subcatdetails, CallDate, CallTime) VALUES ('FLQ0350','
',0,'Client Call','Enquiry','48618297','1/29/2004','1/29/2004
11:20:12')

the table DDL is
CREATE TABLE [dbo].[CRMCalls] (
[CRMCallsID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[CLALPHA] [char] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CONSULTANT] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[ATTITUDE] [numeric](18, 0) NULL ,
[CATEGORY] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[SUBCATEGORY] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[SUBCATDETAILS] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[CallDate] [datetime] NULL ,
[CallTime] [datetime] NULL
) ON [PRIMARY]


i have checked the sql statement and it seems to be in order

is there a limit to the number of tables that can be linked to an
access table?
do you think that the select statement is a red herring?

rgds

Edwina