September 22, 2008 at 5:15 pm
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
September 23, 2008 at 12:44 am
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 🙂
Failing to plan is Planning to fail
February 5, 2010 at 5:17 am
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
February 5, 2010 at 5:55 am
Why posting here? It's a very old thread, I suggest that you start a new one.
-- Gianluca Sartori
February 5, 2010 at 5:56 am
BTW, it looks like you are working with SQL Server 2005/2008: be sure to pick the right forum.
-- Gianluca Sartori
February 7, 2010 at 5:29 pm
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
February 8, 2010 at 12:21 pm
Very cool, my old friend. I'll take a look at it. Thank you for spending the time on it.
--Jeff Moden
February 12, 2010 at 6:14 am
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
February 22, 2010 at 8:57 pm
Microsoft SQL Server 2000 - 8.00.2055 (SP4)
Collation SQL_Latin1_General_CP1_CI_AS
_____________
Code for TallyGenerator
February 22, 2010 at 11:11 pm
That may explain it. I'll have to do a test on 2k. Thanks, Sergiy.
--Jeff Moden
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply