INSERT using EXEC problem

Question

Post reply

INSERT using EXEC problem

Viewing 6 posts - 31 through 35 (of 35 total)

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

Sergiy SSC Guru Points: 110208 More actions · Answer 1

> Not to beat a dead horse, but any ideas why it worked for almost a year?

Good question.

Did anybody do anything with Distributed Transactions settings on the server? On remote server?

But anyway, try to avoid "SELECT ... INTO..."

This thing locks system tables (if you insert into local static tables) or whole tempdb (if you insert into #Table) or whole server (if you insert into remote table).

Replace it with

CREATE TABLE...

INSERT INTO ...

_____________
Code for TallyGenerator

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 2

I'm not the DBA, but I would have been consulted/notified if there were any. This particular server is "mine" and no one touches it but me. The linked server is our production DB and ANY changes would have talked about and tested for at least a week before applied.

So basically "No". Neither server has been modified in quite a while.

Thanks for the tip, I'll have to find the places where I use that and fix them.

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Ya thanx Sergiy, great little nugget of information on transactions in there.

David McFarland SSChampion Points: 11815 More actions · Answer 4

"But anyway, try to avoid "SELECT ... INTO..."

This thing locks system tables (if you insert into local static tables) or whole tempdb (if you insert into #Table) or whole server (if you insert into remote table)."

While I realize that that used to be a best practice, and I'm also aware that it remains on the best practice docs for many companies, has this actually been true for the past 6 years? I can't test right now, but I was pretty certain they fixed the problem at least as far as static tables are concerned, back in SQL Server 2k.

Sergiy SSC Guru Points: 110208 More actions · Answer 5

It's not a problem, there is nothing to fix.

It's TRANSACTIONAL SQL.

In order to prevent creating another table with the same name SQL Server checks if such table already exists in system catalogue.

If another process is trying to perform SELECT ... INTO ... it must perform the same check. But it does know if the table has been created until another SELECT ... INTO has completed. Because if that statement failed for some reason the table was not created (actually it was created, but rolled back with whole transaction when the statement failed).

Same about SELECTs, JOINs, etc., SQL Server cannot validate a statement until modifications to system catalogue are completed.

And this is right.

P.S. Ninja, don't tell you did not know about that.

Actually I started to dig in this direction after reading your post about problems with SELECT...INTO #Table .

_____________
Code for TallyGenerator

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

The Ninja still learns everyday on this site. That's why I became so good and that's also why I'll be 10 times much better in 2-3 years.

As for transactions I only have a basic understanding. I can spot potential lock problems when I see the code but I can't anticipate the problem just by looking at the behavior like you did in this case. I'm working on a very low transactional environement (15 transactions/minute at peak hours) so dead locks are really not a primary concern for me. As far as I remember I only had 1 dead lock occur in the last 3 years I worked here (my fault of course) .

Thanx again for the lesson Sergiy.