Series of INSERT INTO queries, only the first one works

Question

Series of INSERT INTO queries, only the first one works

Viewing 8 posts - 16 through 23 (of 23 total)

You must be logged in to reply to this topic. Login to reply

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

pdanes2 (12/21/2010)
RBarryYoung (12/20/2010)
I'd suggest that you Profile it and see what's really going on.
However, my suspicions would be that your Access engine is not executing all of your Statements / Batches in the same Session, which is going jam up your SET IDENTITY_INSERTS...
That is exactly what appears to be (not) happening. I'm pretty new to SQL Server - can you tell me how to do this Profile business? I'm using Express - does Profile work with that?

Ah, well, not so. You see the thing about SET IDENTITY_INSERT is that you can only have one on at a time. So if you get the first one ON, but the OFF statement was in a different batch, well then the second SET IDENTITY_INSERT ON wouldn't take, and then your INSERT would fail with an error very similar to the one that you are getting. So I think that the jury is still out on that one until you can get Profiler up to prove or disprove it.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

pdanes2 SSCommitted Points: 1930 More actions · Answer 2

RBarryYoung (12/21/2010)
pdanes2 (12/21/2010)
RBarryYoung (12/20/2010)
I'd suggest that you Profile it and see what's really going on.
However, my suspicions would be that your Access engine is not executing all of your Statements / Batches in the same Session, which is going jam up your SET IDENTITY_INSERTS...
That is exactly what appears to be (not) happening. I'm pretty new to SQL Server - can you tell me how to do this Profile business? I'm using Express - does Profile work with that?
Ah, well, not so. You see the thing about SET IDENTITY_INSERT is that you can only have one on at a time. So if you get the first one ON, but the OFF statement was in a different batch, well then the second SET IDENTITY_INSERT ON wouldn't take, and then your INSERT would fail with an error very similar to the one that you are getting. So I think that the jury is still out on that one until you can get Profiler up to prove or disprove it.

Do you mean that it would execute the IDENTITY_INSERT ON statement without complaint, but then refuse to execute IDENTITY_INSERT OFF? I'm lost here - why would that happen?

I do know that you can only have one IDENTITY_INSERT on at a time - my code turns it on, does the insert and turns it back off, three statements in a row. (Or tries to, rather, since it isn't working.) The ON and OFF statements are pass-through queries, that Access passes directly to SQL Server, without modification; the INSERT runs in Access and it handles the passing internally through its own knowledge of SQL Server protocols. And again, this all used to work, exactly as is, until I upgraded to 2008 R2 Express.

I'm not clear on what you mean by batch or session. Do you mean a series of SQL statements executed at once? Access doesn't allow that, so the only way would be to create a stored procedure that turned IDENTITY_INSERT on, did the insert and turned IDENTITY_INSERT back off. Unfortunately, I can't do that, since SQL Server won't see the JET tables I have linked into my Access database, from which I am trying to copy records.

tfifield SSCrazy Eights Points: 9655 More actions · Answer 3

Are you using ODBC Direct or ADO to execute the SQL statements?

I used to use ODBC Direct until Access 2007, which doesn't support it any more. I switched to ADO and I've found that multiple statements in the same query batch are not a problem. I've done things like this:

SET IDENTITY_INSERT <table> ON

INSERT INTO <table>

SET IDENTITY_INSERT <table> OFF

Then execute using the ADO connection. This was against a SQL 2000 database. I haven't yet tried this for SQL 2005 or 2008.

Todd Fifield

pdanes2 SSCommitted Points: 1930 More actions · Answer 4

tfifield (12/22/2010)
Are you using ODBC Direct or ADO to execute the SQL statements?
I used to use ODBC Direct until Access 2007, which doesn't support it any more. I switched to ADO and I've found that multiple statements in the same query batch are not a problem. I've done things like this:
SET IDENTITY_INSERT <table> ON
INSERT INTO <table>
SET IDENTITY_INSERT <table> OFF
Then execute using the ADO connection. This was against a SQL 2000 database. I haven't yet tried this for SQL 2005 or 2008.
Todd Fifield

Didin't know you could do that with ADO. I've been using ODBC all along, including with 2007 now. I've never tried ADO since it seemed to be a dead end and ODBC has always worked fine, but I'll give it a try. Thanks for the tip.

tfifield SSCrazy Eights Points: 9655 More actions · Answer 5

I think you'll find ADO much more flexible in VBA once you get used to it. You can even do some conditional tests in the statement like:

IF EXISTS (SELECT 1 FROM MyTable WHERE PK_Val = 1)

SELECT 'YES' AS TheResult

ELSE

SELECT 'NO' AS TheResult

Open it as an ADO recordset and you'll get the correct 'YES' or 'NO'.

You also don't have to create workspaces and such like you have to do with ODBC Direct.

Here's a function that I use to run pass-through queries from Access VBA. gConstr$ is a global string containing the connection string. If it's not already set up, I set it up in the SetLinkInfo() function.

Public Function RunPassThrough(SQL As String, Optional ExistingConnection) As Boolean

Dim Cnx As ADODB.Connection

RunPassThrough = False

' Make sure we have a connection string.

If IsNull(gConStr$) Or IsEmpty(gConStr$) Or gConStr$ = "" Then

If SetLinkInfo() = False Then

Exit Function

End If

On Error GoTo RPT_OpenErr

Set Cnx = New ADODB.Connection

On Error GoTo RPT_ExeErr

Cnx.ConnectionString = gConStr$

Cnx.CommandTimeout = 0

Cnx.Open

Cnx.Execute (SQL)

RunPassThrough = True

RPT_Close:

Cnx.Close

Set Cnx = Nothing

RPT_Exit:

Exit Function

RPT_OpenErr:

MsgBox "Error opening ADO Connection..." & vbCrLf & _

Err.Description

Resume RPT_Exit

RPT_ExeErr:

MsgBox "Error Executing RunPassThrough..." & vbCrLf & _

Err.Description

Resume RPT_Close

End Function

Todd Fifield

pdanes2 SSCommitted Points: 1930 More actions · Answer 6

tfifield (12/23/2010)
I think you'll find ADO much more flexible in VBA once you get used to it. You can even do some conditional tests in the statement like:
IF EXISTS (SELECT 1 FROM MyTable WHERE PK_Val = 1)
SELECT 'YES' AS TheResult
ELSE
SELECT 'NO' AS TheResult
Open it as an ADO recordset and you'll get the correct 'YES' or 'NO'.
You also don't have to create workspaces and such like you have to do with ODBC Direct.
Here's a function that I use to run pass-through queries from Access VBA. gConstr$ is a global string containing the connection string. If it's not already set up, I set it up in the SetLinkInfo() function.
Todd Fifield

Thanks, Todd, I gave it a try. Sadly, no effect - I'm still getting the exact same behavior: the first table, regardless of which one it is, loads fine, subsequent ones do not. I'm pretty sure I've got something strange messed up on the server, since the behavior is the same for both versions (I tried reinstalling 2005 after days of fruitless efforts with 2008). And the code, exactly as is (or was, before I made all these changes) used to execute flawlessly, and all changes continue to run into the same SQL Server brick wall, so I doubt it's an issue with my Access design or calling methods.

I'm almost at the point of calling some local DB design shop and hiring one of their experts. I'd hate to do it, I'm not normally one to give up on something just because it's difficult, but this is getting serious and I have other work that needs to get done.

tfifield SSCrazy Eights Points: 9655 More actions · Answer 7

I'm really at a loss here. I set up some test conditions and they seemed to work just fine.

This was, however, using Access 2007 and ADO against a SQL 2000 database. I'll see if I can set up a test against a SQL 2008 database.

For some reason I'm not getting Email notifications of replies on my threads here at the Forum. I just got this one by looking over my history and clicking on this one to see if there was anything else.

Todd

pdanes2 SSCommitted Points: 1930 More actions · Answer 8

tfifield (12/30/2010)
I'm really at a loss here. I set up some test conditions and they seemed to work just fine.
This was, however, using Access 2007 and ADO against a SQL 2000 database. I'll see if I can set up a test against a SQL 2008 database.
For some reason I'm not getting Email notifications of replies on my threads here at the Forum. I just got this one by looking over my history and clicking on this one to see if there was anything else.
Todd

Yes, it must be something screwed up in my installation. According to all reading and advice, this should work. And it formerly did. But I'm pretty new to SQL Server and haven't even been able to figure out what to look at, much less fix it. And since a fresh re-install of 2005 is doing the same thing now, I doubt a re-install of 2008 would help. It may come down to torching the OS and re-installing Windows. I'm not looking forward to that, there is a lot of stuff on this machine, but if I can't figure out anything else...

I'm thinking about trying some reg-cleaning software, but I don't know, sometimes that causes more problems than it fixes, and I don't think there are that many capable of cleaning a 64-bit server registry properly.