"Disappearing" Table. Why?
-
I was working on something, and have found a little "quirk" that I do not understand. I've simplified my example below to reproduce and illustrate my findings. Why is this table disappearing?
-- **** = separate "Executes" , not not run as as a single script.
-- Make a table in "remote" database
Select * from OpenRowset('SQLOLEDB', 'Server=(local);Trusted_Connection=yes',
'SET NoCount On
If Object_ID(''Pubs.dbo.TestTable'') Is Not NULL Drop Table Pubs.dbo.TestTable
Create Table Pubs.dbo.TestTable (TestCol1 Int)
Select * From Pubs.dbo.SysObjects where Name like ''TestTable'' ') A
-- ****************************************
-- Now see if table is still still there? (Same SELECT as used above)
Select * From Pubs.dbo.SysObjects where Name like 'TestTable'
-- ****************************************
-- Now it's NOT there
-- Now, make a table in "remote" database again, adding a COMMIT after the CREATE
Select * from OpenRowset('SQLOLEDB', 'Server=(local);Trusted_Connection=yes',
'SET NoCount On
If Object_ID(''Pubs.dbo.TestTable'') Is Not NULL Drop Table Pubs.dbo.TestTable
Create Table Pubs.dbo.TestTable (TestCol1 Int)
COMMIT
Select * From Pubs.dbo.SysObjects where Name like ''TestTable'' ') A
-- ****************************************
-- Now see if table is still still there? (Same SELECT as used above)
Select * From Pubs.dbo.SysObjects where Name like 'TestTable'
-- ****************************************
-- Now it IS there
-- Cleanup...
If Object_ID('Pubs.dbo.TestTable') Is Not NULL Drop Table Pubs.dbo.TestTable
Once you understand the BITs, all the pieces come together
-
Can't reproduce your problem. First script runs successful but received error message by running second script.
"Server: Msg 3902, Level 16, State 1, Line 1
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION."
Check connectiom property setting and post the result of "DBCC USEROPTIONS".
-
Thanks Allen.
Interesting
, when I perform my 1st-- Now see if table is still still there? (Same SELECT as used above)
Select * From Pubs.dbo.SysObjects where Name like 'TestTable'
I get an empty record set... Even if I ....
-- Now, make a table in "remote" database again, adding a COMMIT after the CREATE
Select * from OpenRowset('SQLOLEDB', 'Server=(local);Trusted_Connection=yes',
'SET NoCount On
If Object_ID(''Pubs.dbo.TestTable'') Is Not NULL Drop Table Pubs.dbo.TestTable
BEGIN TRAN
Create Table Pubs.dbo.TestTable (TestCol1 Int)
COMMIT
Select * From Pubs.dbo.SysObjects where Name like ''TestTable'' ') A
-- ****************************************
I also get empty recset once it returns
textsize 64512
language us_english
dateformat mdy
datefirst 7
quoted_identifier SET
arithabort SET
ansi_null_dflt_on SET
ansi_defaults SET
ansi_warnings SET
ansi_padding SET
ansi_nulls SET
concat_null_yields_null SET
My TRANSACTION ISOLATION LEVEL is "Stock".
Once you understand the BITs, all the pieces come together -
This is an interesting problem. I cannot duplicate, get the expected 1-row output.
Your DBCC USEROPTIONS output pertains to your client connection settings--if using Query Analyzer then those connection settings win--they're the last to be applied. But the new OpenRowSet connection can't make use of your Query Analyzer connection settings, I think it would take the default Server connection properties.
Tried to run DBCC USEROPTIONS w/in the OpenRowSet, didn't work even with tableresults...this would potentially have shown you the settings in effect w/in the OpenRowSet. But I think SQL Enterprise Mgr Server Properties should show you same basic info. Maybe you'll find a setting there that explains your result?
Otherwise, I wonder if there's another default configuration location for new OpenRowSet (or SQLOLEDB) connections?
-
Shoot, I was writing a nice reply post, and lost it (timeout maybe??)
I get the same symptom on many of the servers here.
I use Q/A and either login as a specific user, or
with Win Auth. of Administrator. Same Symptom everywhere.
Let me make double sure you understand my issue.
Org. post contains 5 T-SQL statements;
1st is an OPENROWSET, 2nd is a SELECT, 3rd is another OPENROWSET, 4th is identical to 2nd, 5th is "cleanup".
The statments should be executed one-at-a-time, in sequence.
My "problem" symptom is the 2nd statement not returning any rows when I think it should.
Meaning I expect Pubs.dbo.TestTable to continue to exist (Persist).
The 3rd, 4th statements are posted just to show what I did to get the results I want in 4th statement.
Further study:
I get no errors while running statements.
After run 1st statement, I can verify the Pubs.dbo.TestTable does NOT exist.
I can CREATE it without error
Try to DROP it, get an error (Cannot drop the table 'Pubs.dbo.TestTable', because it does not exist in the system catalog.)
If I add BEGIN TRAN before the CREATE TABLE in 3rd statement,
the 4th statement produces varying results:
If Pubs.dbo.TestTable exists prior to OPENROWSET, then I get 1 row (Persistance)
If Pubs.dbo.TestTable does NOT exist prior to OPENROWSET, then I get 0 rows (Same as 1st statemnt - bizaar!)
Profiler shows table creation only.
No errors in Win logs
Some of my tests were on basically "stock" SQL2K boxes.
None have had SQL configuration modifications dealing with Transaction Iso. levels.
Most tests run on Win2k, SQL2K, latest SPacks, varying manufactures ect.
All have either latest, or just prior MDAC.
Some have VB & .NET dev environments.
Some may be MSDE2K. I know most are full SQL2K.
I am concerned about this symptom, because once I fix all my typeOs in my CREATE TABLE statements,
I, like I think most of you also, expect it to work, and the table it creates to exist until DROPed.
In other words, I am not in the habit of checking if my objects exist after CREATE statements. I take
this for granted when I code.
Does anyone else have the same reproducable symptom?
Thanks so far....
Once you understand the BITs, all the pieces come together -
OK, sorry about my last post, got sidetracked into thinking about connection options. I can duplicate this. Try changing select in snippet a
Select *
to
Select @@trancount as "@@trancount", *
Seems that the batch run inside OpenRowSet() is all in an implicit tran. That's why it works when you add the commit in other snippet. I tested a simple select stmt and when run inside OpenRowSet, it shows @@trancount = 1.
Anyway, if you perform any update/modification inside OpenRowSet() I believe you have to add your own explicit commit (inside the OpenRowSet), else it all rolls back.
-
Mike,
It sure seems that way "explicit commit (inside the OpenRowSet)"... Seems very strange to me if this IS the case???? Still doen't explain why I get the same kind of symptom if I use bot BEGIN TRAN & COMMIT ???? unless the "outer" transaction rolls back the "inner" one.
Are you in the slight bit concerned about this apparent behaivior? Or do you think we should all just "chalk this one up" in the "you don't always get what you expect" category, and leave it at that?
Also, Allen_Cui, above, received an error when he tried it, but he may have not performed the exact same statements the same way we did ???
Once you understand the BITs, all the pieces come together -
I posted a reply last week, am sure I saw it here for a while but now gone.
From quick google search I believe this behavior is MDAC dependent, so only safe way to proceed would probably be something like this "if @@transcount > 0 commit" whenever you make modifications w/in OpenRowSet.
It bothers me that even a SELECT is thrown inside a tran...so maybe place to combat this is right at top of your SQL batch, commit or rollback before you even get started? But overall doesn't bug me too much because I have never had call to use this function, except for fiddling around...why are you using OpenRowSet?
Anyway ... most robust solution may be to just not use SQLOLEDB, use ODBC provider instead:
Select * from OpenRowset('SQLOLEDB', 'Server=(local);Trusted_Connection=yes',
'SET NoCount On
If Object_ID(''Pubs.dbo.TestTable'') Is Not NULL Drop Table Pubs.dbo.TestTable
Create Table Pubs.dbo.TestTable (TestCol1 Int)
Select @@trancount as "@@trancount", * From Pubs.dbo.SysObjects where Name like ''TestTable'' ') A
Select * from OpenRowset('MSDASQL', 'DRIVER={SQL Server};SERVER=(local);Trusted_Connection=yes',
'SET NoCount On
If Object_ID(''Pubs.dbo.TestTable'') Is Not NULL Drop Table Pubs.dbo.TestTable
Create Table Pubs.dbo.TestTable (TestCol1 Int)
Select @@trancount as "@@trancount", * From Pubs.dbo.SysObjects where Name like ''TestTable'' ') A
-
Thanks Mike.
I'll try the ODBC method to see if symptom goes away in "production" stuff.
"Stuff" = periodic copy of data from LAN to DMZ where the "establishment" of the SQL connection must be performed from the LAN side. Then I must "push" data to DMZ side of firewall. I usually "pull" using OpenRowSet, and I've never had a problem. Since I must "push" in this case, I've got to create the remote table 1st, then INSERT data into it, then remotely manipulate it.
Once you understand the BITs, all the pieces come together -
I tried Mike's suggestion of using ODBC instead, and the "disappearing table" symptom is no loger there. i.e. Its "fixed" now
. Thanks.
Once you understand the BITs, all the pieces come together
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply