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

302621 BulkAdmin permission

What is the status of this SQL Server 2000 problem?
I cannot find any fix for this problem.
SYMPTOMS
If a BULK INSERT command is issued by a user who
Is part of the bulkadmin server role and has INSERT
permissions to the table
Is not part of the sysadmin, db_ddladmin, or db_owner role
Is not the database owner
The following error message is generated:
Server: Msg 8104, Level 16, State 2, Line 1
The current user is not the database or object owner of
table '<table_name>'. Cannot perform SET operation.
WORKAROUND
To work around the problem, make sure that the user is
also part of either the db_ddladmin or the db_owner
database role in addition to the bulkadmin server role.
Another workaround is to make the user the owner of the
database.
ThanksIf your environment doesn't permit you to implement the workarounds
suggested in the article, consider using another technique such as DTS or
BCP.
Hope this helps.
Dan Guzman
SQL Server MVP
"Gary Billins" <gbillins@.petvalu.com> wrote in message
news:727a01c4763a$502d66f0$a401280a@.phx.gbl...
> What is the status of this SQL Server 2000 problem?
> I cannot find any fix for this problem.
> SYMPTOMS
> If a BULK INSERT command is issued by a user who
> Is part of the bulkadmin server role and has INSERT
> permissions to the table
> Is not part of the sysadmin, db_ddladmin, or db_owner role
> Is not the database owner
> The following error message is generated:
> Server: Msg 8104, Level 16, State 2, Line 1
> The current user is not the database or object owner of
> table '<table_name>'. Cannot perform SET operation.
> WORKAROUND
> To work around the problem, make sure that the user is
> also part of either the db_ddladmin or the db_owner
> database role in addition to the bulkadmin server role.
> Another workaround is to make the user the owner of the
> database.
> Thanks

3 CrossTabs

I need to insert 3 Cross Tabs into the same report, each has to use a different filter. I can create a filter on my first crosstab, however I cannot do it on the other 2. All 3 are using the same field has a filter, however based on the value in that field it determines what data will be used by the particuliar crosstab. Any help given is greatly appreciated.If i understood your question correctly, the suggested answer would be to create different groups or if that is not possible create a formula in the section expert that says to display the cross_tab only if a certain value is found.

Hope it helps
Cheers

Milos

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

2nd INSERT INTO statement in a Button click event

Hi everyone.
I am trying to write 2 INSERT INTO statements into a Button click event. Both statements go to the same database but differnet tables. The first statement works fine but the 2nd causes an error with the Try, Catch, Finally statement. When I remove the ExecuteNonQuery from the 2nd statement, the 2nd INSERT INTO statement fails to work. Any help would be brilliant. Thanks!

Private Sub btnInsertChange_Click(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.CommandEventArgs) Handles btnInsertChange.Command

'Insert Guide changes into ChangesReport table in sql server
Me.SqlCommandThemeTest.Connection = Me.SqlConnection1

Dim Name As String
Dim values As String
Dim sSQL As String
Name = "Theme, Guide, GuidePage, PageType, ChangeCategory, ChangeFrom, ChangeFromText, ChangeTo, ChangeToText ContentManager"
values = "lstTheme, lstGuideName, lstGuidePage, lstPageType, lstChangeCategory, lstChangeFrom, txtChangeFrom, lstChangeTo, txtChangeTo, Label1"
SqlCommandThemeTest.CommandText = "INSERT INTO dbo.ChangesReport (Theme, Guide, GuidePage, PageType, ChangeCategory, ChangeFrom, ChangeFromText, ChangeTo, ChangeToText, ContentManager) VALUES (@.themeValue, @.guideValue, @.guidepageValue, @.pagetypeValue, @.changecategoryValue, @.changefromValue, @.changefromtextValue, @.changetoValue, @.changetotextValue, @.contentmanagerValue)"

SqlCommandThemeTest.Parameters.Add("@.themeValue", lstTheme.SelectedItem.Text)
SqlCommandThemeTest.Parameters.Add("@.guideValue", lstGuideName.SelectedItem.Text)
SqlCommandThemeTest.Parameters.Add("@.guidepageValue", lstGuidePage.SelectedItem.Text)
SqlCommandThemeTest.Parameters.Add("@.pagetypeValue", lstPageType.SelectedItem.Text)
SqlCommandThemeTest.Parameters.Add("@.changecategoryValue", lstChangeCategory.SelectedItem.Text)
SqlCommandThemeTest.Parameters.Add("@.changefromValue", lstChangeFrom.SelectedItem.Text)
SqlCommandThemeTest.Parameters.Add("@.changefromtextValue", txtChangeFrom.Text)
SqlCommandThemeTest.Parameters.Add("@.changetoValue", lstChangeTo.SelectedItem.Text)
SqlCommandThemeTest.Parameters.Add("@.changetotextValue", txtChangeTo.Text)
SqlCommandThemeTest.Parameters.Add("@.contentmanagerValue", Label1.Text)

Try
Me.SqlConnection1.Open()
Me.SqlCommandThemeTest.ExecuteNonQuery()
Catch ex As Exception
Response.Write(ex.ToString)
Finally
Me.SqlConnection1.Close()
End Try

'Insert textbox to ChangeLogFrom ddl
Me.CmdDDLFromUpdate.Connection = Me.SqlConnection1

Name = "ChangeFromText"
values = "txtChangeFrom"
sSQL = "INSERT INTO dbo.Change Log From (ChangeLogFrom) VALUES (@.changelogfromValue)"
Me.CmdDDLFromUpdate.Parameters.Add("@.changelogfromValue", txtChangeFrom.Text)

Try
Me.SqlConnection1.Open()
Me.CmdDDLFromUpdate.ExecuteNonQuery()
Catch ex As Exception
Response.Write(ex.ToString)
Finally
Me.SqlConnection1.Close()
End Try

I have tried various different things with these 2 statements but can still only get the first to insert.

Any advice would be great. Thanks

|||

It's hard to tell since you've only cut and pasted certain parts of the code. For example, I don't see where you've set the commandtext of the second sqlcommand. I see the sSQL string being set, but that isn't the same thing.

You might also want the keep the sqlconnection open for both updates, no use in closing it then reopening it.

|||Why dont you pass all the parameters to a stored proc and do both the inserts there. That will save you a round trip and your code will be at one place.

2005/2005Express SQLOLEDB Provider problem

When creating a connection string in ADO (using Delphi7), I insert the provider like this:

Provider=SQLOLEDB;Data Source=.;Initial Catalog=master;Integrated Security=SSPI;User ID=sa;Password=masterkey

Now, when I try to run this on a SQL Server 2005 or SQL2005 Express database, it won't create the connection
unless I use the SQLNCLI provider.

Is there a way to force them to use the OLEDB provider?
I have MDAC2.8-SP1 installed.Try explicitly adding the server name

Provider=SQLOLEDB;Data Source=<Server Name>;Initial Catalog=master;Integrated Security=SSPI;User ID=sa;Password=masterkey

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