INSERT using EXEC problem

Question

INSERT using EXEC problem

Viewing 15 posts - 16 through 30 (of 35 total)

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

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

Here's the link... easier to find if you have the right keywords to search for .

http://www.sqlservercentral.com/columnists/bkelley/procedurecache.asp

The whole article is awesome but if you're short on time make sure you read the "Specify the Owner" section in the middle.

Happy reading.

Sergiy SSC Guru Points: 110206 More actions · Answer 2

Jason,

do you read table dbo.agingData inside SP?

It appears locking problem.

If you perform INSERT ... EXEC you lock targeted table. If you are trying to read something from this table inside of your EXEC, then ... well you know what happens.

_____________
Code for TallyGenerator

Sergiy SSC Guru Points: 110206 More actions · Answer 3

Try this workaround:

SELECT * INTO #agingData

FROM dbo.agingData

WHERE 1=0 -- copies table structure, no data copied

INSERT #agingData

EXEC dbo.populateAging

INSERT INTO dbo.agingData

SELECT * from #agingData

If it works it's definitely transaction locking problem.

_____________
Code for TallyGenerator

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

Thanx for the idea.. I'd never have thaught of that one (the price to pay for working with only 10 employees on the DB .

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

I was wondering when Sergiy would chime in.....

Thanks, I'll try it tomorrow.

But still one question. Why would moving the insert into the proc work.

______________________________________________________________________

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

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

Oh, and do you have any idea what would cause this to "suddenly" start happening?

Could this be one of those intermittent bugs that MS hasn't seen/resolved?

______________________________________________________________________

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

Sergiy SSC Guru Points: 110206 More actions · Answer 7

Because when you have INSERT outside the whole SP appears locked in transaction (INSERT is implicit transaction, right?)

It does not happen if you perform INSERT inside of SP.

_____________
Code for TallyGenerator

Sergiy SSC Guru Points: 110206 More actions · Answer 8

Locks really depend on data statistics, indexing, etc.

Someone dropped or changed an index on this table, statistics forced Server to choose another execution plan...

It's not so easy to guess, but there are many possible reasons.

_____________
Code for TallyGenerator

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

Thanks for the info! I REALLY do appreciate it.

Just a note though, ABSOLUTELY nothing has changed with this proc. or table in (at least) three months and it runs weekly, and that is what has got my noodle tweaked!

I'm still going to see what happens with your other solution.

Thanks again!

______________________________________________________________________

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

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

Sergiy,

That did not make a difference?

______________________________________________________________________

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

S. Kusen SSChampion Points: 10936 More actions · Answer 11

I'm not sure why it would stop working, but as a regular practice, I would put the column names on your insert statement,

TRUNCATE TABLE [dbo].[agingData]

INSERT INTO [dbo].[agingData] (col1, col2, col3)

EXEC [dbo].[populateAging] --returns col1, col2, col3

That will eliminate confusion if someone else were to troubleshoot the issue. Not sure wy it would work and then stop working but that is the logic that I use when I perform inserts by using an sp that performs a select.

Sergiy SSC Guru Points: 110206 More actions · Answer 12

Which of the statements hangs now?

_____________
Code for TallyGenerator

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

INSERT #agingData EXEC dbo.populateAging

______________________________________________________________________

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

Sergiy SSC Guru Points: 110206 More actions · Answer 14

Does you SP have some

SELECT ...

INTO #Table

statements?

_____________
Code for TallyGenerator

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

Man you're good.

The procedure does a select from a linked server into a temp table. I then do another select, joining on the that table.

I think I'm seeing it now. So with the INSERT outside of the procedure, the second INSERT inside the procedure is conflicting somehow. By moving the INSERT from outside to in, it does one, then the other not both at once.

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

______________________________________________________________________

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