3197 record deleted error

HI,
i have an access MDB linked by ODBC to SQL2k.
I get a strange behaviour when i do an INSERT INTO to a local Mdb table
from "LEFT OUTER JOINED" SQL tables.
If the related table hasn't related record to the first table,
it get an Error 3167.
This behaviour is not present with MDB tables JOINS.
Who know something about this ?If you are getting an error 3167 in Access then it's likely
an access error - can't tell without the error message you
get. But that's probably the case and you would want to post
your question in an access newsgroup.
-Sue
On 27 Oct 2005 08:05:38 -0700, "merco"
<d.mercanti@.gmail.com> wrote:

>HI,
>i have an access MDB linked by ODBC to SQL2k.
>I get a strange behaviour when i do an INSERT INTO to a local Mdb table
>from "LEFT OUTER JOINED" SQL tables.
>If the related table hasn't related record to the first table,
>it get an Error 3167.
>This behaviour is not present with MDB tables JOINS.
>Who know something about this ?|||well, if i use only mdb tables this error doesn't occour.
So i suppose it's an odbc problem|||Make sure you have a primary key or unique identifier
defined for your tables you are linking. Make sure the
primary key or unique identifier is not an approximate data
type (e.g. float). Try putting on timestamp data type column
on the tables you are linking.
-Sue
On 28 Oct 2005 00:25:17 -0700, "merco"
<d.mercanti@.gmail.com> wrote:

>well, if i use only mdb tables this error doesn't occour.
>So i suppose it's an odbc problem

3197 record deleted error

HI,
i have an access MDB linked by ODBC to SQL2k.
I get a strange behaviour when i do an INSERT INTO to a local Mdb table
from "LEFT OUTER JOINED" SQL tables.
If the related table hasn't related record to the first table,
it get an Error 3167.
This behaviour is not present with MDB tables JOINS.
Who know something about this ?
If you are getting an error 3167 in Access then it's likely
an access error - can't tell without the error message you
get. But that's probably the case and you would want to post
your question in an access newsgroup.
-Sue
On 27 Oct 2005 08:05:38 -0700, "merco"
<d.mercanti@.gmail.com> wrote:

>HI,
>i have an access MDB linked by ODBC to SQL2k.
>I get a strange behaviour when i do an INSERT INTO to a local Mdb table
>from "LEFT OUTER JOINED" SQL tables.
>If the related table hasn't related record to the first table,
>it get an Error 3167.
>This behaviour is not present with MDB tables JOINS.
>Who know something about this ?
|||well, if i use only mdb tables this error doesn't occour.
So i suppose it's an odbc problem
|||Make sure you have a primary key or unique identifier
defined for your tables you are linking. Make sure the
primary key or unique identifier is not an approximate data
type (e.g. float). Try putting on timestamp data type column
on the tables you are linking.
-Sue
On 28 Oct 2005 00:25:17 -0700, "merco"
<d.mercanti@.gmail.com> wrote:

>well, if i use only mdb tables this error doesn't occour.
>So i suppose it's an odbc problem

2ND POST: Is there a way to insert a record using another as a template?

Hi,
I am currently inserting a record using:
insert into TABLE(FIELD1,FIELD2) values ('%s',%d)
But, the problem I have is that the table I am connecting to can either have
46 fields or 47 fields depending on its version. Is there a command that
would allow me to use an existing record as a template and only specified
the fields I want to change and then insert the new record?
Something like:
insert into TABLE(FIELD1) value ('%s') using template record where
FIELD1='9999999' ?
Thanks,
SA DevYour question is not clear
Give some sample data with expected result
Madhivanan|||Select SubQuery.* Into
NameOfYourNewTable
From
(
Select *,'Value' as NewColumnName From yourOldtable
) SubQuery
' That what you mean '
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"SA Development" <nospam38925@.forme.com> schrieb im Newsbeitrag
news:ILSdnRsNE_DG4jTfRVn-2A@.valortelecom.com...
> Hi,
> I am currently inserting a record using:
> insert into TABLE(FIELD1,FIELD2) values ('%s',%d)
> But, the problem I have is that the table I am connecting to can either
> have
> 46 fields or 47 fields depending on its version. Is there a command that
> would allow me to use an existing record as a template and only specified
> the fields I want to change and then insert the new record?
> Something like:
> insert into TABLE(FIELD1) value ('%s') using template record where
> FIELD1='9999999' ?
> Thanks,
> SA Dev
>
>|||Perhaps you could add a default for the additional column where it
exists and then you might not have to reference that column at all.
Alternatively, use SPs to insulate your application from different
schema versions. One of the big benefits of using SPs for data access
code is that they provide a single point of entry that can be made
backwards-compatible in later versions.
Failing that you may have to resort to Dynamic SQL. Make sure you read
up on all the implications first:
http://www.sommarskog.se/dynamic_sql.html
David Portas
SQL Server MVP
--

2ND POST: Is there a way to insert a record using another as a tem

Hi
If you specify the column list then you insert values into those columns.
Missing columns will be set to NULL (if it is allowed) or a default value.
To update existing data use the UPDATE statement, you will only change the
values for columns specified in the SET clause. More information on the
INSERT and UPDATE statements can be found in Books Online.
Using stored procedure may help to remove any issues with different versions
as you will ship the correct procedure with the changes to the schema.
You can look at the INFORMATION_SCHEMA.columns view to find out what columns
a table has, although a different approach would be to only provide a
solution for the latest version and check they are on that version otherwise
force an upgrade.
John
"SA Development" wrote:

> Hi,
> I am currently inserting a record using:
> insert into TABLE(FIELD1,FIELD2) values ('%s',%d)
> But, the problem I have is that the table I am connecting to can either ha
ve
> 46 fields or 47 fields depending on its version. Is there a command that
> would allow me to use an existing record as a template and only specified
> the fields I want to change and then insert the new record?
> Something like:
> insert into TABLE(FIELD1) value ('%s') using template record where
> FIELD1='9999999' ?
> Thanks,
> SA Dev
>
>Thanks for the good ideas everyone, sorry my first post wasn't entirely
clear.
Lets say I have a table like this:
field1(key) field2 field3 field4 field5
9999 a b c d
What I was hoping to do is add a new record (field1=123 for example), but
use the 9999 as a template record so that its field values are inserted into
my new record EXCEPT for any that I override.
It sounds like the update command will let me update just the fields I want,
is there a way to tell SQL to copy record 9999 to record 123 and use all the
field values in record 9999 ?
Thanks,
Alan|||Hi David,

> INSERT INTO YourTable (col1, col2, col3, col4, col5)
> SELECT 123, col2, col3, col4, col5
> FROM YourTable
> WHERE col1 = 9999
Thank you -- that is exactly what I needed!
Thanks to everyone else who posted as well.
Have a great day,
Alan

24000 Invalid cursor state. Prepared Statement

I have written a routine to search a unique record using prepared statement. Its my first sql coding with c++.

I am not using / importing any dlls.

I connect+allocs handels , then use SQLPrepare(StmtHandle, SQLStmt,SQL_NTS); to generate a guery.

I have written bind parameters and sqlexecute +sqlFetch in a loop and loop gets executed till ESC key is pressed.

First time when I bind paramaters using SQLBindParameter it works perfect.

When loop gets executed secondtime onwards, it gives an error.
SQLState: 24000 [ODBC Client Interface]Invalid cursor state.

If I open connection, handles, and prepared starement in same loop, THEN it gives correct record without 24000 error.

I want the advantage of prepared staement. So I do not want to close and open connection and prepare statement every time.

Have I missed any step?
Where & when I should code the cursor type? Any specific libraries I need to link?

Thanksyes you missed something. see brett's sticky at the top of the page.|||I traced the solution.

I thought that the mistake is in declaring scrollable cursors.

So I explained precisouly the problem in 7-8 lines.
Reading 50-60 lines with altogether different coding standards is difficult.

Its not coding problem but associated with scrollable cursor options.
So I did not include the code.

MSDN examples does not show such required step as the scope of example code gets over before such situation is reached!!.

2005 too fast "problem"!

I have a weird situation I had not expected....

I insert a record to a table and "later" I update it.
I have two fields to capture time information: Created and LastModified.
My update is very simple: update ... set ..,[LastModifiedDate] = GetDate() where id = @.pId.

Now my problem is that I am seeing the created and lastmodified times as the same (in format 2007-09-05 12:38:42.383) !!??!

The record has definitely been updated (other fields are populated).

Can anybody enlighten me?You'll need to post your code and tell us how long the duration is between inserts & updates.|||Well...

Maybe, upon the selection of the row, you should also grab the modifiedDate as well as the ID, and then check upon update if the dates are the same, otherwise it means someone has cheanged on you since you last grabbed the row...meaning it would appear a miracle occured.

Now this may not be your problem, but it is definetly a doubt, becausde you can't tell, and I'll always try to code toward absolut...I mean absolutes

Here's a sample

UPDATE [MEP]
SET
MEPType = @.MEPType
, MEPName = @.MEPName
, MEPStatus = @.MEPStatus
, MEPStatusDate = @.MEPStatusDate
, ProjectNum = @.ProjectNum
, PropCompDate = @.PropCompDate
, Comments = @.Comments
, ModifiedDate = GetDate()
, ModifiedBy = @.APPUSER

WHERE MEPRecID = @.MEPRecID
AND ModifiedDate = @.ModifiedDate

SELECT @.error = @.@.ERROR, @.rowcount = @.@.ROWCOUNT

IF @.error <> 0
BEGIN
SELECT @.Error_Loc = 1, @.Error_Type = 50001, @.rc = -1
GOTO usp_UPD_MEP_Err
END

IF @.rowcount = 0
BEGIN
IF NOT EXISTS(SELECT * FROM [MEP]
WHERE [MEPRecID]=@.MEPRecID)
BEGIN
SELECT @.Error_Loc = 2, @.Error_Type = 50002, @.rc = 1
, @.Error_Message = 'No Rows Updated. Data has been deleted or ID passed is incorrect'
GOTO usp_UPD_MEP_Err
END
ELSE
BEGIN
SELECT @.Error_Loc = 3, @.Error_Type = 50002, @.rc = 2
, @.Error_Message = 'Rows Have Been Modified since last SELECT'
GOTO usp_UPD_MEP_Err
END
END|||This is a problem? Man, I need a new job.|||This is part of a bigger system so posting code would be confusing but I guess you are suggesting I try to isolate a simple instance of the issue. The operation definitely occurs in 2 distinct steps :

1. insert record and raise an event to tell a listener that the record is available (C# stuff)
2. listener does its thing and updates the record.

I suppose my question really was whether you folks are surprised by this - or whether you believe it you be possible at all? I certainly have put a lot of effort into the performance of this piece of code (real-time system processing 1000s messages from queues, applying them to business data, raising distributed events to remote desktops, etc.) but sure don't believe that it can do even the simplest insert followed by update within the same millisecond!|||Ummmmmm...are you using stored procedures?

Raising an error so you can know, for every row that's
inserted...and you think that's fast|||sure don't believe that it can do even the simplest insert followed by update within the same millisecond!Easily. Try writing a loop to insert GETDATE into a table. You will get a fair few identical records. BTW - the smallest unit of a SQL Server datetime data type is 3.3 milliseconds - maybe you can believe it can do this in 3 times the time you thought it was ;)

The key point is the amount of time between issuing the two instructions. If this is > 3.3 milliseconds then your code is relevent because it must be wrong.|||Are you just trying to get the actual millisecons the row was inserted?|||I fully believe SQL 2005 is this fast. My time trials back in 2004 showed close to 100% improvement over SQL 2000, but it was not a very scientific test. If you need the extra precision, I am afraid you will have to wait until next summer when SQL 2008 comes out, and you can use the new date and time types.|||I trialled a datetime column as a clustered PK inserting getdate in a loop in 2K. Pk violations so it was this fast before 2005.|||Thanks for those responses.

Obviously I'm not a regular user of this forum so I'm not sure of the how you folks communicate/interact so I'll try to clarify.

Brett:

Maybe, upon the selection of the row, you should also grab the modifiedDate as well as the ID,...

..thing is that I don't have any concurrency issue with this. Just an unexpected situation since upgrading to 2005 (i.e. I have never seen this happen in 2000).

MCrowley:

This is a problem? Man, I need a new job.

..:) only problem is that I didn't ever expect such a situation. I do, however, use the Create = LastModified in some logic for lack of another field that would tell me if any attempt at all has been made to process this record. The only field/flag I have is a 'Processed' one but it means whether the data was successfully applied to the business data or not.

Probably not explaining that so well - basically the records I'm talking about are xml messages coming off queues. There are three very distinct steps once a message arrives - 1. persist it locally (withing a queue transaction) and only then 2. process it - meaning use its data to update various other data in the database and 3. broadcast the new 'business' data (business objects really) to all desktops. Important point is the taking form the queue and putting in the data is one distinct transactioned step. Processing it an updating the LastModified flag is another so I would have expected at least a millisecond to have passed.

Brett:

Ummmmmm...are you using stored procedures? Raising an error so you can know, for every row that's inserted...and you think that's fast

..not understanind this post at all - sorry.

Pootle:

Easily. Try writing a loop to insert GETDATE into ...

..yep, fair enough I would expect that. But this is two distinct (almost unrelated) stored procedure calls from two distinct processed!?

The key point is the amount of time between issuing the two instructions

..the only measure I have of this is those timestamps i'm afraid.

Brett:

Are you just trying to get the actual millisecons the row was inserted?

..No, see above response to MCrowley - I have used the "fact" that they could never be the same to provide some other logic. Bad design and all but I was limited to the existing table structure.

Just to reiterate: This is NOT any kind of major problem. Just a curiousity. It happens maybe a 5 times a day and usually when processing the contents of the message is expected to be very fast anyway - for example, when I immediated find that some content is bad.|||I'm not sure what the two distinct processes are. If you mean these are 3GL apps calling SQL Server and therer is communication in between the two statements then that sound odd. If it is all within SQL server then, irrespective of how many sproc calls there are, you would expect it to be lightening fast. Again - it all depends what goes on in between. I'm afraid I don't understand your business processes enough to get a handle on your description above.

You could add the below between the two statements to be absolutely satisfied what is going on.

WAITFOR DELAY '00:00:00:002'|||Pootle's suggestion was what I was thinking of, if this turned out to be a problem. 5 - 10 rows out of 1,000's per day is not a big problem...except for the guy who has to fix 5 - 10 rows per day. i would only add to Pootle's suggestion by adding a check to the update procedure something like

if CreatedDate = getdate()
begin
WAITFOR DELAY '00:00:00:002'
end
update stuff goes here

This might save you even more devious problems later on.|||5 - 10 rows out of 1,000's per day is not a big problem...except for the guy who has to fix 5 - 10 rows per day. Lol. That is so true. Even after years of working with sets I still sometimes forget that it is the number of distinct problems rather than the sum number of rows affected that determine how much effort I need to put into debugging\ cleansing etc..|||Are you just trying to get the actual millisecons the row was inserted?

..No, see above response to MCrowley - I have used the this "fact" that they could never be the same to provide some other logic.

Holy Cow...what would that "Logic" (Logic should be in quotes) be?

Bad design and all but I was limited to the existing table structure.

Good you think that's not a good idea, but what does it mean limites to the table structure?

And if you are limited, I would suggest triggers to fix it|||Again thanks for taking the time to reply.

Brett:
Holy Cow...what would that "Logic" (Logic should be in quotes) be?

.."fact" (in quotes) - I was being ironic(?)

Good you think that's not a good idea, but what does it mean limites to the table structure?

..should have be written limiteD meaning i couldn't change the structure to add a new field.

Generally speaking I get the vibe that you folks haven't really had any issue with this kind of stuff. I don't have either - if the update time is the same as the insert time and i can clearly see from the data that they did not/could not occur in a single transaction/statement/whatever then so be it.

Just fyi in case it all seems crazy to you...

..messages sent from other systems to mine are read from various incoming protocols - queues, ftp, etc. using a message manager process (threading/processors/etc.)

..ideally the data they contain are applied directly to the appropriate database tables, etc. BUT for safety sake (and believe me with good reason) I persist every piece of incoming data UNTIL it is processed successfully.

..if persisting a message to the database is successful that particular transaction is committed (in the case of a queue the message is truly removed). Otherwise everything is rolled back.

..that essentially is 1 process inserting some 'raw' data.

..then the component that has read the message and persisted it to the database raises an event to let any interested parties that a message has come in and is available in the message repository using Id xxx.

..any listeners then do whatever they need to do with the message to make it into something meaningful to that particular application.

..in my case here there is only one listener. It gets the event and the Id and calls a stored procedure which takes the Id, reads the message, processes its contents and finally (finally!!) updates the message with results from the processing - example: Processed = false, Reason = 'cannot find this account/etc.'

....now obviously there is a significant overhead in persisting the raw messages but the nature (trustworthyness - i.e. "we never sent that") of the other systems and the delivery protocols means that it must be done.

.. so you can see that the two processes/actions are very distinct. That is why I am amazed that the times are the same. But the proof is there in the database - the insert uses getdate() and the update uses getdate() and the update has clearly occurred (Failure Reason is populated).

I'm damn happy that the performance is so good to be perfectly honest. I just have difficulty believing it is all.

And, again, to reiterate there is no problem - i.e. the times matching is NOT a problem. Its just a curiousity. The waitfor idea is a great suggestion should I decide that I don't want these times to ever be the same. But, then, for performance stats it really helps out that they sometimes are :)

Again, thanks all for taking an interest.

-- also sorry for my bad spelling/clumsy fingers