store output of stored proc into temp table

Question

store output of stored proc into temp table

Viewing 10 posts - 16 through 24 (of 24 total)

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

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 1

Madhivanan (9/22/2008)
My reply "Yes it is" is to your reply "Heh... not exactly true..."
Actually I agreed with your reply 🙂

Heh... pretty bad misunderstanding on my part. Thanks, Madhivanan.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Madhivanan-208264 SSCertifiable Points: 7516 More actions · Answer 2

Jeff Moden (9/22/2008)
Madhivanan (9/22/2008)
My reply "Yes it is" is to your reply "Heh... not exactly true..."
Actually I agreed with your reply 🙂
Heh... pretty bad misunderstanding on my part. Thanks, Madhivanan.

No Problem 🙂

Madhivanan

Failing to plan is Planning to fail

Chandra Sekhar Manchukonda SSC Veteran Points: 260 More actions · Answer 3

Hi Jeff,

The solution you mentioned is working fine for the procedure sp_who, but when I use the same script for user defined procedure then it throwing following error

Msg 7357, Level 16, State 2, Line 1

Cannot process the object "EXEC VacationReporting.[dbo].[BalSummary_Chand_2]". The OLE DB provider "SQLNCLI" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.

What is the issue when I use userdefined function in the OPENROWSET?

Code used is:

SELECT *

INTO #MyHead

FROM OPENROWSET('SQLNCLI','Server=.\sql2005;Trusted_Connection=Yes;',

'EXEC [DBName].[dbo].[BalSummary_Chand_2]')

Thanks

Chandra

spaghettidba SSC Guru Points: 105732 More actions · Answer 4

Why posting here? It's a very old thread, I suggest that you start a new one.

-- Gianluca Sartori

spaghettidba SSC Guru Points: 105732 More actions · Answer 5

BTW, it looks like you are working with SQL Server 2005/2008: be sure to pick the right forum.

-- Gianluca Sartori

Sergiy SSC Guru Points: 110208 More actions · Answer 6

Jeff Moden (9/20/2008)
Man, I really appreciate you watching out for me, Sergiy. I've learned a lot from you. Thanks...
Ok... like I've done before when this controversy of Select/Into has come up, I ran one very long running Select/Into and 4 short ones that all ran simultaneously 10 seconds after the long running one started. If the System Tables were locked for a long term, then the 4 smaller ones would not have been able to complete before the larger one.

Oh! I missed something at the time!

🙂

To summarize, the myth that using Select/Into locks System Tables for the duration of the query was true way back in version 6.5... but it hasn't been true for a long time. It's nothing more than an old wive's tale since way back when.

It's not quite true.

To be correct, it's true, but only when you execute precompiled query.

If you execute a procedure which needs to be recompiled on its way, or which has conditional logic in it (pretty typical, right?) then SELECT/INTO will create and hold exclusive locks.

I took your example and modified it a little.

IF Object_ID('dbo.MyTest') IS NULL

EXEC ('CREATE PROCEDURE dbo.MyTest AS Select 1 As One')

GO

ALTER PROCEDURE dbo.MyTest

@RCNT int = 5000000

AS

SET NOCOUNT ON

DECLARE @ErrNo int

--===== Create and populate a 1,000,000 row test table.

-- Column "RowNum" has a range of 1 to 1,000,000 unique numbers

-- Column "SomeInt" has a range of 1 to 50,000 non-unique numbers

-- Column "SomeLetters2" has a range of "AA" to "ZZ" non-unique 2 character strings

-- Column "SomeMoney has a range of 0.0000 to 99.9999 non-unique numbers

-- Column "SomeDate" has a range of >=01/01/2000 and <01/01/2010 non-unique date/times

-- Column "SomeCSV" contains 'Part01,Part02,Part03,Part04,Part05,Part06,Part07,Part08,Part09,Part10'

-- for all rows.

-- Column "SomeHex12" contains 12 random hex characters (ie, 0-9,A-F)

-- Jeff Moden

SET ROWCOUNT @RCNT

SELECT

RowNum = IDENTITY(INT,1,1),

SomeInt = ABS(CHECKSUM(NEWID()))%50000+1,

SomeLetters2 = CHAR(ABS(CHECKSUM(NEWID()))%26+65)

+ CHAR(ABS(CHECKSUM(NEWID()))%26+65),

SomeCSV = CAST('Part01,Part02,Part03,Part04,Part05,Part06,Part07,Part08,Part09,Part10' AS VARCHAR(80)),

SomeMoney = CAST(ABS(CHECKSUM(NEWID()))%10000 /100.0 AS MONEY),

SomeDate = CAST(RAND(CHECKSUM(NEWID()))*3653.0+36524.0 AS DATETIME),

SomeHex12 = RIGHT(NEWID(),12)

INTO #JBMTest

FROM Master.dbo.SysColumns t1,

Master.dbo.SysColumns t2 --Lack of join criteria makes this a CROSS-JOIN

SET @ErrNO = @@ERROR

SET ROWCOUNT 0

IF @ErrNo = 0

SELECT * FROM #JBMTest

ELSE

SELECT Result = N'Nothing to return'

GO

--WAITFOR TIME '20:31:00'

PRINT CONVERT(VARCHAR(30),GETDATE(),121)

SELECT *

INTO #MyHead

FROM OPENROWSET('SQLOLEDB','Server=(local);Trusted_Connection=Yes;Database=Master',

'Set FmtOnly OFF; EXEC dbo.MyTest')

PRINT CONVERT(VARCHAR(30),GETDATE(),121)

SELECT TOP 100 * FROM #MyHead

--DROP TABLE #JBMTest

DROP PROCEDURE dbo.MyTest

PRINT CONVERT(VARCHAR(30),GETDATE(),121)

"IF" at the end of procedure makes it impossible for optimiser to say what's gonna be the final recordset while the procedure is running.

I started it and then executed following query in another window:

SELECT *

FROM OPENROWSET('SQLOLEDB','Server=(local);Trusted_Connection=Yes;Database=Master',

'Set FmtOnly OFF; EXEC dbo.MyTest 20 ')

It's got stuck for a while, so I ran my procedure based on sp_lock to see what's going on down there.

You can find its outcome in the file attached.

Exclusive locks on tempdb.dbo.sysobjects and tempdb.dbo.syscolumns are clearly there.

From both instances. Despite the fact that "EXEC dbo.MyTest 20" takes less than 0.2 seconds to complete, so I could not catch its locks if that 2nd query would not waiting for the 1st one to finish.

Fortunately, exclusive locks applied to key entries of the indexes, so you must be lucky as John McClane to be caught in there.

But if you try hard (like they do in Production environment) you'll get blocked sooner or later.

If you're afraid of it, don't use it. Personally, I'm not not going to let such a valuable, high performance tool go by the wayside because of tales. Now, if you can prove that my code example is a bunch of hooey, then I'll definitely change my tune because code rules. 🙂

I cannot prove that your code example is a bunch of hooey, because it is not.

It shows how to do queries for SELECT/INTO to avoid locks on system tables.

My code shows that if to use SELECT/INTO on procedures written by typical ".Net developer with SQL experience" it may be quite dangerous.

Pretty much the same as for triggers:

- Use it if you know what to do and how to do it;

- Hold on and ask for advice if you're a grasshopper in T-SQL.

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 7

Very cool, my old friend. I'll take a look at it. Thank you for spending the time on it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 8

Sergiy,

I guess I have a magic machine because I can't make it do the same locks you've shown. Which version, edition, and service pack of SQL Server are you using? Also, just because it might make a difference (dunno... somehow), what is your default collation?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sergiy SSC Guru Points: 110208 More actions · Answer 9

Microsoft SQL Server 2000 - 8.00.2055 (SP4)

Collation SQL_Latin1_General_CP1_CI_AS

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 10

That may explain it. I'll have to do a test on 2k. Thanks, Sergiy.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)