November 13, 2014 at 11:21 am
Hello,
Was working on something I could deploy across my environment, which is a mix of 2008R2/2012 servers, to give some information on log files. Running into a silly issue right off the bat. The table that DBCC LogInfo() conjures out of magic is different between the two. In 2012 it gained the RecoveryUnitID column. So I'm trying to write some logic to create a temp table based on which version is running.
I would like to avoid a global temp table if possible. Here's what I've tried:
sp_executesql creates a table outside of the scope of my session:
DECLARE @PrVers NVARCHAR(128)
, @PrVersNum DECIMAL(10,2)
, @StageTable NVARCHAR(1024) = N''
SET @PrVers = CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(128));
SELECT @PrVersNum = SUBSTRING(@PrVers, 1,CHARINDEX('.', @PrVers) + 1 )
PRINT @PrVers
PRINT @PrVersNum
IF OBJECT_ID('tempdb..#stage') IS NOT NULL
DROP TABLE #stage;
IF @PrVersNum < 11.00
BEGIN
SET @StageTable = N'
CREATE TABLE #stage
(
FileID INT ,
FileSize BIGINT ,
StartOffset BIGINT ,
FSeqNo BIGINT ,
[Status] BIGINT ,
Parity BIGINT ,
CreateLSN NUMERIC(38)
);'
EXEC sys.sp_executesql @StageTable
END
ELSE IF @PrVersNum >= 11.00
BEGIN
SET @StageTable = N'
CREATE TABLE #stage
(
UnitID INT,
FileID INT ,
FileSize BIGINT ,
StartOffset BIGINT ,
FSeqNo BIGINT ,
[Status] BIGINT ,
Parity BIGINT ,
CreateLSN NUMERIC(38)
);'
EXEC sys.sp_executesql @StageTable
END
SELECT * FROM #stage
Msg 208, Level 16, State 0, Line 90
Invalid object name '#stage'.
Calling non-dymnamic SQL seems to attempt to create *both* tables.
DECLARE @PrVers NVARCHAR(128)
, @PrVersNum DECIMAL(10,2)
SET @PrVers = CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(128));
SELECT @PrVersNum = SUBSTRING(@PrVers, 1,CHARINDEX('.', @PrVers) + 1 )
PRINT @PrVers
PRINT @PrVersNum
IF OBJECT_ID('tempdb..#stage') IS NOT NULL
DROP TABLE #stage;
IF @PrVersNum < 11.00
BEGIN
CREATE TABLE #stage
(
FileID INT ,
FileSize BIGINT ,
StartOffset BIGINT ,
FSeqNo BIGINT ,
[Status] BIGINT ,
Parity BIGINT ,
CreateLSN NUMERIC(38)
);
END
ELSE IF @PrVersNum >= 11.00
BEGIN
CREATE TABLE #stage
(
UnitID INT,
FileID INT ,
FileSize BIGINT ,
StartOffset BIGINT ,
FSeqNo BIGINT ,
[Status] BIGINT ,
Parity BIGINT ,
CreateLSN NUMERIC(38)
);
END
SELECT * FROM #stage
Msg 2714, Level 16, State 1, Line 29
There is already an object named '#stage' in the database.
Any suggestions?
Thanks
November 13, 2014 at 11:40 am
The temp table in the dynamic sql is created in a different context and not available to the calling context. e.g.
DECLARE @sql nvarchar(4000) = N'create table #foo(a int)';
EXEC sp_executesql @sql
SELECT * FROM #foo
fails just like your code.
Gerald Britton, Pluralsight courses
November 13, 2014 at 11:42 am
g.britton (11/13/2014)
The temp table in the dynamic sql is created in a different context and not available to the calling context. e.g.
DECLARE @sql nvarchar(4000) = N'create table #foo(a int)';
EXEC sp_executesql @sql
SELECT * FROM #foo
fails just like your code.
Try calling the table ##stage instead.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
November 13, 2014 at 11:46 am
Alvin Ramard (11/13/2014)
g.britton (11/13/2014)
The temp table in the dynamic sql is created in a different context and not available to the calling context. e.g.
DECLARE @sql nvarchar(4000) = N'create table #foo(a int)';
EXEC sp_executesql @sql
SELECT * FROM #foo
fails just like your code.
Try calling the table ##stage instead.
I considered that, but I would like to avoid using a global temp table if possible.
November 13, 2014 at 12:00 pm
Your second approach is the best one. Execute it in sections and see if you can isolate where the failure occurs.
Gerald Britton, Pluralsight courses
November 13, 2014 at 12:07 pm
What about creating the table and adding or dropping columns as needed?
Here's an example dropping the column to preserve the order and prevent confusing the Intellisense.
DECLARE @PrVers NVARCHAR(128)
, @PrVersNum DECIMAL(10,2)
SET @PrVers = CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(128));
SELECT @PrVersNum = SUBSTRING(@PrVers, 1,CHARINDEX('.', @PrVers) + 1 )
PRINT @PrVers
PRINT @PrVersNum
IF OBJECT_ID('tempdb..#stage') IS NOT NULL
DROP TABLE #stage;
CREATE TABLE #stage
(
UnitID INT,
FileID INT ,
FileSize BIGINT ,
StartOffset BIGINT ,
FSeqNo BIGINT ,
[Status] BIGINT ,
Parity BIGINT ,
CreateLSN NUMERIC(38)
);
IF @PrVersNum < 11.00
ALTER TABLE #stage DROP COLUMN UnitID ;
SELECT * FROM #stage
November 13, 2014 at 12:09 pm
Nice!
Gerald Britton, Pluralsight courses
November 13, 2014 at 12:09 pm
Luis Cazares (11/13/2014)
What about creating the table and adding or dropping columns as needed?Here's an example dropping the column to preserve the order and prevent confusing the Intellisense.
DECLARE @PrVers NVARCHAR(128)
, @PrVersNum DECIMAL(10,2)
SET @PrVers = CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(128));
SELECT @PrVersNum = SUBSTRING(@PrVers, 1,CHARINDEX('.', @PrVers) + 1 )
PRINT @PrVers
PRINT @PrVersNum
IF OBJECT_ID('tempdb..#stage') IS NOT NULL
DROP TABLE #stage;
CREATE TABLE #stage
(
UnitID INT,
FileID INT ,
FileSize BIGINT ,
StartOffset BIGINT ,
FSeqNo BIGINT ,
[Status] BIGINT ,
Parity BIGINT ,
CreateLSN NUMERIC(38)
);
IF @PrVersNum < 11.00
ALTER TABLE #stage DROP COLUMN UnitID ;
SELECT * FROM #stage
That's so simple it hurts.
Thanks, Luis.
November 13, 2014 at 12:12 pm
Quick suggestion, not as elegant as Luis's but should work
😎
USE tempdb;
GO
SET NOCOUNT ON;
DECLARE @PrVers NVARCHAR(128)
, @PrVersNum DECIMAL(10,2)
, @StageTable NVARCHAR(1024) = N''
SET @PrVers = CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(128));
SELECT @PrVersNum = SUBSTRING(@PrVers, 1,CHARINDEX('.', @PrVers) + 1 )
PRINT @PrVers
PRINT @PrVersNum
IF OBJECT_ID('tempdb..#stage') IS NOT NULL
DROP TABLE #stage;
SET @StageTable = N'
CREATE TABLE #stage
(
' + CASE WHEN @PrVersNum < 11.00 THEN '' ELSE 'UnitID INT,
' END + 'FileID INT ,
FileSize BIGINT ,
StartOffset BIGINT ,
FSeqNo BIGINT ,
[Status] BIGINT ,
Parity BIGINT ,
CreateLSN NUMERIC(38)
);
SELECT * FROM #stage'
EXEC sys.sp_executesql @StageTable
November 13, 2014 at 12:14 pm
Eirikur Eiriksson (11/13/2014)
Quick suggestion, not as elegant as Luis's but should work😎
USE tempdb;
GO
SET NOCOUNT ON;
DECLARE @PrVers NVARCHAR(128)
, @PrVersNum DECIMAL(10,2)
, @StageTable NVARCHAR(1024) = N''
SET @PrVers = CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(128));
SELECT @PrVersNum = SUBSTRING(@PrVers, 1,CHARINDEX('.', @PrVers) + 1 )
PRINT @PrVers
PRINT @PrVersNum
IF OBJECT_ID('tempdb..#stage') IS NOT NULL
DROP TABLE #stage;
SET @StageTable = N'
CREATE TABLE #stage
(
' + CASE WHEN @PrVersNum < 11.00 THEN '' ELSE 'UnitID INT,
' END + 'FileID INT ,
FileSize BIGINT ,
StartOffset BIGINT ,
FSeqNo BIGINT ,
[Status] BIGINT ,
Parity BIGINT ,
CreateLSN NUMERIC(38)
);
SELECT * FROM #stage'
EXEC sys.sp_executesql @StageTable
That would work, but the problem is I need to hit that table outside of the dynamic SQL. :hehe:
November 13, 2014 at 12:22 pm
How about this
😎
USE tempdb;
GO
SET NOCOUNT ON;
DECLARE @PrVers NVARCHAR(128)
, @PrVersNum DECIMAL(10,2)
, @StageTable NVARCHAR(1024) = N''
SET @PrVers = CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(128));
SELECT @PrVersNum = SUBSTRING(@PrVers, 1,CHARINDEX('.', @PrVers) + 1 )
PRINT @PrVers
PRINT @PrVersNum
IF OBJECT_ID('tempdb..#stage') IS NOT NULL
DROP TABLE #stage;
SET @StageTable = N'
CREATE TABLE #stage
(
' + CASE WHEN @PrVersNum < 11.00 THEN '' ELSE 'UnitID INT,
' END + 'FileID INT ,
FileSize BIGINT ,
StartOffset BIGINT ,
FSeqNo BIGINT ,
[Status] BIGINT ,
Parity BIGINT ,
CreateLSN NUMERIC(38)
);
INSERT INTO #stage
EXEC (''DBCC LOGINFO'')
SELECT * FROM #stage'
EXEC sys.sp_executesql @StageTable
November 13, 2014 at 12:29 pm
Eirikur Eiriksson (11/13/2014)
How about this😎
USE tempdb;
GO
SET NOCOUNT ON;
DECLARE @PrVers NVARCHAR(128)
, @PrVersNum DECIMAL(10,2)
, @StageTable NVARCHAR(1024) = N''
SET @PrVers = CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(128));
SELECT @PrVersNum = SUBSTRING(@PrVers, 1,CHARINDEX('.', @PrVers) + 1 )
PRINT @PrVers
PRINT @PrVersNum
IF OBJECT_ID('tempdb..#stage') IS NOT NULL
DROP TABLE #stage;
SET @StageTable = N'
CREATE TABLE #stage
(
' + CASE WHEN @PrVersNum < 11.00 THEN '' ELSE 'UnitID INT,
' END + 'FileID INT ,
FileSize BIGINT ,
StartOffset BIGINT ,
FSeqNo BIGINT ,
[Status] BIGINT ,
Parity BIGINT ,
CreateLSN NUMERIC(38)
);
INSERT INTO #stage
EXEC (''DBCC LOGINFO'')
SELECT * FROM #stage'
EXEC sys.sp_executesql @StageTable
I think that since the next part of this script (below) does some further processing and iterates over all DBs on a server, it's still probably a bit less complicated to use Luis' method. I don't even need the additional column for anything. Heh.
EXEC sp_foreachdb @command = N'Use ?;
Insert Into #stage
Exec sp_executeSQL N''DBCC LogInfo(?)'';
Insert Into #results
Select DB_Name(), Count(*)
From #stage;
Truncate Table #stage;';
November 13, 2014 at 12:49 pm
You are right on this, Luis's approach is better as it doesn't switch session contexts, just wanted to make the point that there are always alternatives.;-)
😎
November 13, 2014 at 1:00 pm
And there's another alternative based on "best practices" 🙂
DECLARE @PrVers NVARCHAR(128)
, @PrVersNum DECIMAL(10,2)
, @command NVARCHAR(4000)
SET @PrVers = CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(128));
SELECT @PrVersNum = SUBSTRING(@PrVers, 1,CHARINDEX('.', @PrVers) + 1 )
PRINT @PrVers
PRINT @PrVersNum
IF OBJECT_ID('tempdb..#stage') IS NOT NULL
DROP TABLE #stage;
IF OBJECT_ID('tempdb..#results') IS NOT NULL
DROP TABLE #results;
CREATE TABLE #results(
db_name nvarchar(128),
counts int
);
CREATE TABLE #stage
(
UnitID INT,
FileID INT ,
FileSize BIGINT ,
StartOffset BIGINT ,
FSeqNo BIGINT ,
[Status] BIGINT ,
Parity BIGINT ,
CreateLSN NUMERIC(38)
);
SET @command = 'Use ?;
Insert Into #stage (
' + CASE WHEN @PrVersNum < 11.00 THEN '' ELSE 'UnitID,
' END + 'FileID,
FileSize ,
StartOffset,
FSeqNo,
[Status],
Parity,
CreateLSN
)
Exec sp_executeSQL N''DBCC LogInfo(?)'';
Insert Into #results
Select DB_Name(), Count(*)
From #stage;
Truncate Table #stage;';
EXEC sp_MSforeachdb @command;
SELECT *
FROM #results;
November 13, 2014 at 1:13 pm
Luis Cazares (11/13/2014)
And there's another alternative based on "best practices" 🙂
DECLARE @PrVers NVARCHAR(128)
, @PrVersNum DECIMAL(10,2)
, @command NVARCHAR(4000)
SET @PrVers = CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(128));
SELECT @PrVersNum = SUBSTRING(@PrVers, 1,CHARINDEX('.', @PrVers) + 1 )
PRINT @PrVers
PRINT @PrVersNum
IF OBJECT_ID('tempdb..#stage') IS NOT NULL
DROP TABLE #stage;
IF OBJECT_ID('tempdb..#results') IS NOT NULL
DROP TABLE #results;
CREATE TABLE #results(
db_name nvarchar(128),
counts int
);
CREATE TABLE #stage
(
UnitID INT,
FileID INT ,
FileSize BIGINT ,
StartOffset BIGINT ,
FSeqNo BIGINT ,
[Status] BIGINT ,
Parity BIGINT ,
CreateLSN NUMERIC(38)
);
SET @command = 'Use ?;
Insert Into #stage (
' + CASE WHEN @PrVersNum < 11.00 THEN '' ELSE 'UnitID,
' END + 'FileID,
FileSize ,
StartOffset,
FSeqNo,
[Status],
Parity,
CreateLSN
)
Exec sp_executeSQL N''DBCC LogInfo(?)'';
Insert Into #results
Select DB_Name(), Count(*)
From #stage;
Truncate Table #stage;';
EXEC sp_MSforeachdb @command;
SELECT *
FROM #results;
Son of a...
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply