Proc with input paramaters does not compile
-
I have a SQL 2008 R2 proc which compiles & executes fine. It receives input variables to execute. This proc no longer compiles in SQL 2012.
The Proc is invoked like this:
EXEC MyDB.dbo.sp_MyProc
@myVar_1 ='value_x',
@myVar_2 = 1.0
@myVar_3 = 1,
@myVar_4 = 5
How should I revise this code to compile properly?
CREATE PROCEDURE [MyDB].[dbo].[sp_MyProc]
@myVar_1 nvarchar(256) = N''
,@myVar_2 FLOAT=10
,@myVar_3 BIT=0
,@myVar_4 INT=30
AS
BEGIN
SET NOCOUNT ON;
DECLARE
@myVar_5 varchar(50)
,@myVar_6 nvarchar(max)
,@myVar_7 nvarchar(255)
SET @myVar_5 = '1'
SET @myVar_6 = 'xyz'
SET @myVar_7 = 'abc' ...
Thx in advance!
BT -
Without seeing the entire of the procedure, absolutely no way to answer that. The error message would also be useful.
You are missing a comma in the EXEC statement after the second parameter. Whether that's a copy-paste error or the actual problem I can't tell.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
thx for helping me out here.. Our code is proprietary therefore I created a short example in my original post. The comma is actually in my "live" EXEC portion.. but I never get to EXEC portion because the proce itself fails during Compile)
When I compile the code in SQL 2008 R2 it is clean - then change my SSMS connection to SQL 2012 instance, compile and it fails. Looks like something in SQL 2012 changed w/ the way Procs use input variables.
Sample ERRORS I'm receiving during COMPILE.
Msg 137, Level 15, State 2, Procedure sp_MyProc, Line 195
Must declare the scalar variable "@myVar_1".
Msg 137, Level 15, State 2, Procedure sp_MyProc, Line 197
Must declare the scalar variable "@myVar_2".
Msg 137, Level 15, State 2, Procedure sp_MyProc, Line 249
Must declare the scalar variable "@myVar_3".
BT -
Express12 (11/11/2013)
thx for helping me out here.. Our code is proprietary therefore I created a short example in my original post. The comma is actually in my "live" EXEC portion.. but I never get to EXEC portion because the proce itself fails during Compile)When I compile the code in SQL 2008 R2 it is clean - then change my SSMS connection to SQL 2012 instance, compile and it fails. Looks like something in SQL 2012 changed w/ the way Procs use input variables.
Sample ERRORS I'm receiving during COMPILE.
Msg 137, Level 15, State 2, Procedure sp_MyProc, Line 195
Must declare the scalar variable "@myVar_1".
Msg 137, Level 15, State 2, Procedure sp_MyProc, Line 197
Must declare the scalar variable "@myVar_2".
Msg 137, Level 15, State 2, Procedure sp_MyProc, Line 249
Must declare the scalar variable "@myVar_3".
Without seeing the code we really can't help you. A guess would be that there is something in the code causing those variables to no longer be in scope of the declaration.
-
Express12 (11/11/2013)
Sample ERRORS I'm receiving during COMPILE.Msg 137, Level 15, State 2, Procedure sp_MyProc, Line 195
Must declare the scalar variable "@myVar_1".
Msg 137, Level 15, State 2, Procedure sp_MyProc, Line 197
Must declare the scalar variable "@myVar_2".
Msg 137, Level 15, State 2, Procedure sp_MyProc, Line 249
Must declare the scalar variable "@myVar_3".
Absolutely no way to help without seeing the procedure itself. All I can say is that somewhere in the procedure you're using variables that either haven't been declared or are out of scope.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
--Here's the proc.. compiles in 2008 R2 (ent edition), does not compile in 2012 w/ sp1 (Ent edition)
USE [Admin_DB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_MyProc]
@databasename nvarchar(256) = N''
,@FragCheckFLOAT=10
,@DensityCheckFLOAT=75
,@RebuildThresholdFLOAT=30
,@onlineBIT=0
,@runrebuildBIT=1
,@DBMirrorPerf BIT=0
,@ChangeDBRecoveryBIT=1
,@SendEmailBIT=0
,@SendSummaryOnlyBIT=0
,@MaxDaysofLogINT=30
,@MaxErrorsINT=10
AS
BEGIN
SET NOCOUNT ON;
DECLARE
@DBMode varchar(50)
,@StatusMsg nvarchar(max)
,@ErrorMsg nvarchar(255)
,@ifexistsINT
,@startmaindatetime
,@starteddatedatetime
,@endeddatedatetime
,@totalsecondspassedint
,@myeditionvarchar(50)
,@NewLineCHAR (1)
,@RetryINT
,@OutparmINT
,@schemanamesysname
,@objectnamesysname
,@indexnamesysname
,@tableidint
,@indexidint
,@currentfragfloat
,@currentdensityfloat
,@postfragfloat
,@postdensityfloat
,@partitionnumvarchar(10)
,@partitioncountbigint
,@indextypevarchar(18)
,@commandnvarchar(4000)
,@myrebuildoptionnvarchar(500)
,@myreorganizeoption nvarchar(500)
,@lob_countint
,@sqllob_countnvarchar(500)
,@parmlob_countnvarchar(50)
,@mydisabledindexbit
,@parmmydisabledindex nvarchar(50)
,@sqlmydisabledindex nvarchar(500)
,@pagelocksnotallowedcountint
,@parmmyallowpagelocks nvarchar(50)
,@sqlmyallowpagelocks nvarchar(500)
,@rowlocksnotallowedcountint
,@parmmyallowrowlocks nvarchar(50)
,@sqlmyallowrowlocks nvarchar(500)
,@myindexishypotetical bit
,@parmmyindexishypotetical nvarchar(50)
,@sqlmyindexishypotetical nvarchar(500)
,@countprocessedint
,@onofflinemessvarchar(50)
,@myservicenamevarchar(100)
,@rcint
,@mycodenvarchar(max)
,@activeconnectionsindb smallint
,@onlineeditionbit
,@RecoveryMode varchar(128)
,@RecoveryModeOld varchar(128)
,@altdbbeforenvarchar(200)
,@altdbafternvarchar(200)
,@dbStatusMsg varchar(1024)
,@dbmirrorold tinyint
,@dbmirrorwitnessnvarchar(128)
,@altdbmirrorbeforenvarchar(200)
,@altdbmirrorafternvarchar(200)
,@myfromname nvarchar(500)
,@mytoname nvarchar(4000)
,@myrecipientsVARCHAR(100)
,@mycurrentaddres VARCHAR(1024)
,@alladdresses NVARCHAR(1024)
,@mailaddress varchar (200)
,@mylogmessagenvarchar(255)
,@activelastminutesint
,@sqlstringnvarchar (512)
,@mydbidsmallint
,@sqlparmnvarchar(100)
,@numprocint
,@ToDeleteDateTimeDATETIME
,@errint
,@StatusNVARCHAR (50)
,@SubjectLocalNVARCHAR (1024)
,@MailBodyNVARCHAR (MAX)
,@JobRunStartDateTimeDATETIME
,@JobRunEndDateTimeDATETIME
,@JobIndexCountSMALLINT
,@JobStatusNVARCHAR (50)
,@MainStartDateTimeDATETIME
SET@Retry = 0
SET@StatusMsg = ''
SET@NewLine = CHAR(13)
SET@MainStartDateTime = GETDATE()
IF@Online = 1 SET @ChangeDBRecovery = 0
WHILE (@retry >= 0 AND @retry <= @MaxErrors)
BEGIN
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MyJobLog]') AND type in (N'U'))
BEGIN;
CREATE TABLE [dbo].[MyJobLog](
[DatabaseName] [sysname] NOT NULL,
[JobRunStartDateTime] [datetime] NULL,
[JobRunEndDateTime] [datetime] NULL,
[JobIndexCount] [smallint] NULL,
[JobStatus] [nvarchar](50) NULL,
[JobLog] [nvarchar](max) NULL
) ON [PRIMARY]
END;
ELSE
BEGIN;
SET@ToDeleteDateTime = DATEDIFF(dd, @MaxDaysofLog, GetDate())
DELETE
FROM[MyJobLog]
WHEREJobRunStartDateTime <= @ToDeleteDateTime
END;
BEGIN;
IF @FragCheck < 10
BEGIN;
SET @ErrorMsg = N'Fragmentation checker should not be lower than 10'
GOTO FAILONDB
END;
ELSE
IF @FragCheck >= @RebuildThreshold
BEGIN;
SET @ErrorMsg = N'Fragmentation checker should not higher or equal to rebuild threshold'
GOTO FAILONDB
END;
IF @DensityCheck > 75
BEGIN;
SET @ErrorMsg = N'Density checker should not higher than 75'
GOTO FAILONDB
END;
END;
BEGIN;
IF Cursor_Status('GLOBAL', 'Main_Cursor') >= 0
BEGIN
CLOSE Main_Cursor
DEALLOCATE Main_Cursor
END
BEGIN
IF @databasename <> '' OR LEN(@DatabaseName) > 0-- Rebuild/reorganize one database
BEGIN;
SELECT @ifexists = COUNT(name) FROM sys.sysdatabases where name = @databasename
IF @ifexists = 0
BEGIN;
SET @ErrorMsg = 'Database ' + @databasename + ' does not exist!'
GOTO FAILONDB
END;
ELSE
DECLARE Main_Cursor CURSOR FOR
SELECT name FROM sys.sysdatabases where name = @databasename
END;
ELSE
BEGIN;
DECLARE Main_Cursor CURSOR FOR
SELECTname
FROMsys.sysdatabases
WHEREname not in('tempdb','master','model','msdb')
Order BY name ASC
END;
END
END;
OPEN Main_Cursor
FETCH NEXT FROM Main_Cursor INTO @databasename
WHILE @@FETCH_STATUS=0
BEGIN;
SELECT @DBMode = 'OK'
IF (DATABASEPROPERTYEX(@databasename, 'Status') = N'ONLINE'
AND DATABASEPROPERTYEX(@databasename, 'Updateability') = N'READ_WRITE'
AND DATABASEPROPERTYEX(@databasename, 'UserAccess') = N'MULTI_USER')
SELECT @DBMode = 'OK'
ELSE
SELECT @DBMode = 'NOT AVAILABLE'
IF @DBMode <> 'OK'
BEGIN;
SET @ErrorMsg = N'Unable to rebuild/reorganize indexes on ' + @databasename + N' on SQL Server ' + @@servername + CHAR(13)
+ N'The database is ' + @DBMode + N'!' + CHAR(13)
+ N'No rebuild/reorganize can be done on this database (not ONLINE, not READ_WRITE or not MULTI_USER).'
GOTO FAILONDB
END;
ELSE
BEGIN;
BEGIN;
SELECT @starteddate = getdate()
SET @StatusMsg = @StatusMsg + @NewLine + '++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++'
SET @StatusMsg = @StatusMsg + @NewLine + '-- START OF INDEX DEFRAG FOR DATABASE ' + @databasename + ' AT ' + CONVERT (VARCHAR(20), getdate(), 120)
END;
SELECT@dbmirrorold = ''
SELECT@dbmirrorold = mirroring_safety_level, @dbmirrorwitness = mirroring_witness_name
FROMsys.database_mirroring A INNER JOIN sys.databases B
ON A.database_id = B.database_id
WHEREB.name = @databasename
AND A.mirroring_state=4
AND A.mirroring_role=1
SELECT@RecoveryMode = cast(DATABASEPROPERTYEX(@databasename, 'Recovery') as varchar(20))
SELECT@RecoveryModeOld = @RecoveryMode
IF @dbmirrorold = ''
BEGIN;
IF @RecoveryMode <> 'SIMPLE'
BEGIN;
SELECT @altdbbefore = N'ALTER DATABASE [' + @databasename + N'] SET RECOVERY SIMPLE; '
IF @runrebuild = 1
BEGIN;
IF @ChangeDBRecovery = 1
BEGIN;
EXEC(@altdbbefore)
SELECT @dbStatusMsg = '-- Recovery model for database ' + @databasename + ' was changed to SIMPLE from ' + @RecoveryModeOld + ' recovery mode.'
SET @StatusMsg = @StatusMsg + @NewLine + @dbStatusMsg
END;
END;
ELSE SELECT @altdbbefore = N''
END;
END;
ELSE -- Database in morror that requires FULL recovery model
BEGIN;
IF @RecoveryMode <> 'FULL'
BEGIN;
SELECT @altdbbefore = N'ALTER DATABASE [' + @databasename + N'] SET RECOVERY FULL; '
IF @runrebuild = 1
BEGIN;
EXEC(@altdbbefore)
SELECT @dbStatusMsg = '-- Recovery model for database ' + @databasename + ' was changed to FULL from ' + @RecoveryModeOld + ' recovery mode.'
SET @StatusMsg = @StatusMsg + @NewLine + @dbStatusMsg
END;
END;
ELSE SELECT @altdbbefore = N''
END;
IF @DBMirrorPerf = 1 and @dbmirrorold > 1
BEGIN;
IF @dbmirrorold = 2 -- DB mirroring in high protection mode
BEGIN;
SELECT @altdbmirrorbefore = N'ALTER DATABASE [' + @databasename + N'] SET PARTNER SAFETY OFF; '
IF @runrebuild = 1
BEGIN;
EXEC(@altdbmirrorbefore)
SELECT @dbStatusMsg = '-- Mirror safety level for database ' + @databasename + ' was changed to High Performance.'
SET @StatusMsg = @StatusMsg + @NewLine + @dbStatusMsg
END;
END;
IF @dbmirrorold = 3 -- DB mirroring in high availability mode
BEGIN;
SELECT @altdbmirrorbefore = N'ALTER DATABASE [' + @databasename + N'] SET PARTNER SAFETY OFF WITNESS OFF; '
IF @runrebuild = 1
BEGIN;
EXEC(@altdbmirrorbefore)
SELECT @dbStatusMsg = '-- Mirror safety level for database ' + @databasename + ' was changed to High Performance.'
SET @StatusMsg = @StatusMsg + @NewLine + @dbStatusMsg
END;
END;
END;
SELECT @mydbid = DB_ID(@databasename)
SET @sqlparm = N'@pnumproc INT output'
SET @activelastminutes = 15
SET @numproc = 0
SELECT @sqlstring = N'select @pnumproc = count(session_id)
from sys.dm_exec_connections as ec with (nolock) inner join
sys.sysprocesses as sp with (nolock) on ec.session_id = sp.spid
where ec.session_id <> ' + cast(@@SPID as varchar(10)) +
N' and DATEDIFF(minute, ec.last_read, GETDATE()) < ' + cast(@activelastminutes as varchar(10)) +
N' and DATEDIFF(minute, ec.last_write, GETDATE()) < ' + cast(@activelastminutes as varchar(10)) +
N' and sp.dbid = ' + cast(@mydbid as varchar(10))
EXECUTE sp_executesql @sqlstring, @sqlparm, @pnumproc = @numproc output
SELECT @dbStatusMsg = N'-- No of processes with connections active for the last ' + cast(@activelastminutes as nvarchar(10)) +
N' minutes in DB ' + @databasename + N' is ' + CAST(@numproc AS NVARCHAR(10))
SET @StatusMsg = @StatusMsg + @NewLine + @dbStatusMsg
SELECT @myedition = CONVERT(VARCHAR(50), SERVERPROPERTY('Edition'))
IF (@myedition LIKE 'Developer Edition%' OR @myedition LIKE 'Enterprise Edition%')
SET @onlineedition = 1 ELSE SET @onlineedition = 0
SELECT @myservicename = 'MSSQL$' + @@SERVICENAME + ':Databases'
IF @online = 1 SET @onofflinemess = 'ONLINE (users allowed)'
ELSE SET @onofflinemess = 'OFFLINE (no users allowed)'
IF @runrebuild = 0 SET @StatusMsg = @StatusMsg + @NewLine + '-- Execute the following code ' + @onofflinemess + ' to rebuild and/or reorganize indexes in database '
+ @databasename + ' for better performance!'
ELSE SET @StatusMsg = @StatusMsg + @NewLine + '-- Rebuild and/or reorganization ' + @onofflinemess + ' of indexes in database ' + @databasename + ' will now be executed!'
SET @lob_count = 0
SET @mydisabledindex = 0
SET @pagelocksnotallowedcount = 0
SET @myindexishypotetical = 0
SET @countprocessed = 0
SET @outparm = 0
SET @rc = 0
SET @currentfrag = 0.0
SET @mycode = N''
IF object_id('tempdb..#work_to_do') is not null
DROP TABLE#work_to_do
CREATE TABLE#work_to_do (
IndexIDint not null
,IndexNamevarchar(255) null
,TableNamevarchar(255) null
,TableIDint not null
,SchemaNamevarchar(255) null
,IndexTypevarchar(18) not null
,PartitionNumbervarchar(18) not null
,PartitionCountint null
,CurrentDensityfloat not null
,CurrentFragmentationfloat not null
);
INSERT INTO #work_to_do(
IndexID, TableID, IndexType, PartitionNumber, CurrentDensity, CurrentFragmentation
)
SELECT
fi.index_id
,fi.object_id
,fi.index_type_desc AS IndexType
,cast(fi.partition_number as varchar(10)) AS PartitionNumber
,fi.avg_page_space_used_in_percent AS CurrentDensity
,fi.avg_fragmentation_in_percent AS CurrentFragmentation
FROM sys.dm_db_index_physical_stats(db_id(@databasename), NULL, NULL, NULL, 'SAMPLED') AS fi
WHERE(fi.avg_fragmentation_in_percent > @FragCheck
ORfi.avg_page_space_used_in_percent < @DensityCheck)
ANDpage_count> 8
ANDfi.index_id > 0
EXEC ('UPDATE #work_to_do SET TableName = t.name, SchemaName = s.name, IndexName = i.Name
,PartitionCount = (SELECT COUNT(*) pcount
FROM ['
+ @databasename + '].sys.Partitions p
where p.Object_id = w.TableID
AND p.index_id = w.Indexid)
FROM ['
+ @databasename + '].sys.tables t INNER JOIN ['
+ @databasename + '].sys.schemas s ON t.schema_id = s.schema_id
INNER JOIN #work_to_do w ON t.object_id = w.tableid INNER JOIN ['
+ @databasename + '].sys.indexes i ON w.tableid = i.object_id and w.indexid = i.index_id');
IF Cursor_Status('LOCAL', 'Local_Rebuildindex_Cursor') >= 0
BEGIN
CLOSE Local_Rebuildindex_Cursor
DEALLOCATE Local_Rebuildindex_Cursor
END
DECLARE Local_Rebuildindex_Cursor CURSOR LOCAL FOR
SELECT
IndexID
,TableID
,CASE WHEN IndexType = 'Clustered Index' THEN 'ALL' ELSE '[' + IndexName + ']' END AS IndexName
,TableName
,SchemaName
,IndexType
,PartitionNumber
,PartitionCount
,CurrentDensity
,CurrentFragmentation
FROM#work_to_do i
WHERENOT EXISTS(
SELECT1
FROM#work_to_do iw
WHEREiw.TableName = i.TableName
ANDiw.IndexType = 'CLUSTERED INDEX'
ANDi.IndexType = 'NONCLUSTERED INDEX')
ORDER BY TableName, IndexID;
OPEN Local_Rebuildindex_Cursor;
FETCH NEXT
FROM Local_Rebuildindex_Cursor
INTO @indexid, @tableid, @indexname, @objectname, @schemaname, @indextype, @partitionnum, @partitioncount, @currentdensity, @currentfrag;
WHILE @@FETCH_STATUS = 0
BEGIN;
SET @StatusMsg = @StatusMsg + @NewLine
IF @online = 1
BEGIN;
IF @onlineedition = 1
BEGIN;
SET @myrebuildoption = N' REBUILD WITH (ONLINE = ON, FILLFACTOR = 90, MAXDOP = 0) '
SET @myreorganizeoption = N' REORGANIZE '
SELECT @err = @@error IF @err <> 0 GOTO FAILONINDEX
END;
ELSE
BEGIN;
SET @myrebuildoption = N' REORGANIZE '
SET @myreorganizeoption = N' REORGANIZE '
SELECT @err = @@error IF @err <> 0 GOTO FAILONINDEX
END;
END;
ELSE
BEGIN;
IF @activeconnectionsindb > 0
BEGIN;
IF (@onlineedition = 0 AND @runrebuild = 1)
SET @myrebuildoption = N' REORGANIZE '
IF (@onlineedition = 0 AND @runrebuild = 1)
SET @myreorganizeoption = N' REORGANIZE '
IF (@onlineedition = 0 AND @runrebuild = 0)
SET @myrebuildoption = N' REBUILD WITH (FILLFACTOR = 90) '
IF (@onlineedition = 0 AND @runrebuild = 0)
SET @myreorganizeoption = N' REORGANIZE '
IF (@onlineedition = 1 AND @runrebuild = 1)
SET @myrebuildoption = N' REBUILD WITH (ONLINE = ON, FILLFACTOR = 90, MAXDOP = 0) '
IF (@onlineedition = 1 AND @runrebuild = 1)
SET @myreorganizeoption = N' REORGANIZE '
IF (@onlineedition = 1 AND @runrebuild = 0)
SET @myrebuildoption = N' REBUILD WITH (FILLFACTOR = 90, MAXDOP = 0) '
IF (@onlineedition = 1 AND @runrebuild = 0)
SET @myreorganizeoption = N' REORGANIZE '
SELECT @err = @@error IF @err <> 0 GOTO FAILONINDEX
END;
ELSE
BEGIN;
SET @myrebuildoption = N' REBUILD WITH (FILLFACTOR = 90, MAXDOP = 0) '
SET @myreorganizeoption = N' REORGANIZE '
SELECT @err = @@error IF @err <> 0 GOTO FAILONINDEX
END;
END;
SET @parmmydisabledindex = N'@pmydisabledindex bit output'
SET @sqlmydisabledindex = N'SELECT @pmydisabledindex = is_disabled '
+ N' FROM [' + @databasename + '].sys.indexes '
+ N' WHERE object_id = ' + cast(@tableid as varchar(50))
+ N' AND index_id = ' + cast(@indexid as varchar(50))
EXECUTE sp_executesql @sqlmydisabledindex, @parmmydisabledindex, @pmydisabledindex = @mydisabledindex output
SELECT @err = @@error IF @err <> 0 GOTO FAILONINDEX
SET @parmmyallowrowlocks = N'@xrowlocksnotallowedcount int output'
SET @sqlmyallowrowlocks = N'SELECT @xrowlocksnotallowedcount = COUNT(allow_row_locks) '
+ N' FROM [' + @databasename + '].sys.indexes '
+ N' WHERE object_id = ' + cast(@tableid as varchar(50))
+ N' AND allow_row_locks = 0 '
+ N' AND is_hypothetical = 0 '
+ N' AND is_disabled = 0 '
EXECUTE sp_executesql @sqlmyallowrowlocks, @parmmyallowrowlocks, @xrowlocksnotallowedcount = @rowlocksnotallowedcount output
IF @rowlocksnotallowedcount > 0 SET @StatusMsg = @StatusMsg + @NewLine + N'-- NOTE: Row locks not allowed on object_id = ' + cast(@tableid as varchar(50)) + N', table ' + @objectname + N', index ' + @indexname
SET @parmmyallowpagelocks = N'@xpagelocksnotallowedcount int output'
SET @sqlmyallowpagelocks = N'SELECT @xpagelocksnotallowedcount = COUNT(allow_page_locks) '
+ N' FROM [' + @databasename + '].sys.indexes '
+ N' WHERE object_id = ' + cast(@tableid as varchar(50))
+ N' AND allow_page_locks = 0 '
+ N' AND is_hypothetical = 0 '
+ N' AND is_disabled = 0 '
EXECUTE sp_executesql @sqlmyallowpagelocks, @parmmyallowpagelocks, @xpagelocksnotallowedcount = @pagelocksnotallowedcount output
IF @pagelocksnotallowedcount > 0 SET @StatusMsg = @StatusMsg + @NewLine + N'-- NOTE: Page locks not allowed on object_id = ' + cast(@tableid as varchar(50)) + N', table ' + @objectname + N', index ' + @indexname
SET @parmmyindexishypotetical = N'@pmyindexishypotetical bit output'
SET @sqlmyindexishypotetical = N'SELECT @pmyindexishypotetical = is_hypothetical '
+ N' FROM [' + @databasename + '].sys.indexes '
+ N' WHERE object_id = ' + cast(@tableid as varchar(50))
+ N' AND index_id = ' + cast(@indexid as varchar(50))
EXECUTE sp_executesql @sqlmyindexishypotetical, @parmmyindexishypotetical, @pmyindexishypotetical = @myindexishypotetical output
SELECT @err = @@error IF @err <> 0 GOTO FAILONINDEX
SET @parmlob_count = N'@plob_count INT output'
SET @sqllob_count = N'SELECT @plob_count = lob_data_space_id '
+ N' FROM [' + @databasename + '].sys.tables '
+ N' WHERE object_id = ' + cast(@tableid as varchar(50))
EXECUTE sp_executesql @sqllob_count, @parmlob_count, @plob_count = @lob_count output
SELECT @err = @@error IF @err <> 0 GOTO FAILONINDEX
IF (@lob_count > 0 AND @online = 1)
SET @myrebuildoption = N' REORGANIZE '
IF (@lob_count > 0 AND @online = 0 AND @runrebuild = 1 AND @activeconnectionsindb > 0)
SET @myrebuildoption = N' REORGANIZE '
IF (@lob_count > 0 AND @online = 0 AND @runrebuild = 1 AND @activeconnectionsindb = 0 AND @onlineedition = 1)
SET @myrebuildoption = N' REBUILD WITH (FILLFACTOR = 90, MAXDOP = 0) '
IF (@lob_count > 0 AND @online = 0 AND @runrebuild = 1 AND @activeconnectionsindb = 0 AND @onlineedition = 0)
SET @myrebuildoption = N' REBUILD WITH (FILLFACTOR = 90) '
IF (@mydisabledindex = 1 OR @rowlocksnotallowedcount > 0 OR @pagelocksnotallowedcount > 0 OR @myindexishypotetical = 1)
BEGIN;
SET @StatusMsg = @StatusMsg + @NewLine + '-- Index ' + @indexname + ' for table ' + @schemaname + '.' + @objectname + ' is disabled or hypotetical or has index row/page locking disabled!'
SET @StatusMsg = @StatusMsg + @NewLine + N'Skipped index for table ' + @schemaname + '.' + @objectname + N', index ' + @indexname
+ N' partition ' + cast(@partitionnum as varchar(10)) + N', avg frag in percent ' + cast(@currentfrag as varchar(50))
+ N', avg page space used in percent ' + cast(@currentdensity as varchar(50)) + N'.'
+ N' Index ' + @indexname + N' is disabled or hypotetical or has index row/page locking disabled!'
GOTO NEXTINDEX
END;
ELSE
BEGIN;
IF @currentfrag < @RebuildThreshold
BEGIN;
SELECT @command = N'ALTER INDEX ' + @indexname + N' ON [' + @databasename + N'].[' + @schemaname + N'].[' + @objectname + N']' + @myreorganizeoption;
IF @partitioncount > 1 SELECT @command = @command + N' PARTITION = ' + @partitionnum + ';';
ELSE SET @command = @command + ';'
IF @runrebuild = 1 exec @rc = sp_executesql @command
IF @runrebuild = 0 SET @mycode = @mycode + N' ' + @command
IF @rc <> 0
BEGIN;
SELECT @outparm = 4
SET @StatusMsg = @StatusMsg + @NewLine + 'Stopped index rebuild/reorganize for database ' + @databasename + ' on SQL Server ' + @@SERVERNAME + CHAR(13)
+ ', exit on error when executing command ' + @command + ' !'
GOTO CODEEXIT
END;
ELSE SELECT @outparm = 0
END;
IF @currentfrag >= @RebuildThreshold
BEGIN;
SELECT @command = N'ALTER INDEX ' + @indexname + N' ON [' + @databasename + N'].[' + @schemaname + N'].[' + @objectname + N']' + @myrebuildoption;
IF @partitioncount > 1 SELECT @command = @command + N' PARTITION = ' + @partitionnum;
ELSE SET @command = @command + ';'
IF @runrebuild = 1 exec @rc = sp_executesql @command
IF @runrebuild = 0 SET @mycode = @mycode + N' ' + @command
IF @rc <> 0
BEGIN;
SELECT @outparm = 4
SET @StatusMsg = @StatusMsg + @NewLine + 'Stopped index rebuild/reorganize for database ' + @databasename + ' on SQL Server ' + @@SERVERNAME + CHAR(13)
+ ', exit on error when executing command ' + @command + ' !'
GOTO CODEEXIT
END;
ELSE SELECT @outparm = 0
END;
IF @lob_count > 0
BEGIN;
SELECT @err = @@error IF @err <> 0 GOTO FAILONINDEX
IF @indexid = 1
BEGIN;
SET @StatusMsg = @StatusMsg + @NewLine + '-- Processing LOB table ' + (CASE ISNULL(@Schemaname, '') WHEN '' THEN ' ' ELSE @Schemaname END)
+ '.' + (CASE ISNULL(@Objectname, '') WHEN '' THEN ' ' ELSE @Objectname END) + ', CLUSTERED index ' + @indexname + ', ' + CHAR(13)
+ '-- partition ' + cast(@partitionnum as varchar(10)) + ', avg frag in percent ' + cast(@currentfrag as varchar(50))
+ ', avg page space used in percent ' + cast(@currentdensity as varchar(50))
END;
ELSE IF @indexid >= 32000
BEGIN;
SET @StatusMsg = @StatusMsg + @NewLine + '-- Processing LOB table ' + (CASE ISNULL(@Schemaname, '') WHEN '' THEN ' ' ELSE @Schemaname END)
+ '.' + (CASE ISNULL(@Objectname, '') WHEN '' THEN ' ' ELSE @Objectname END) + ', XML index ' + @indexname + ', ' + CHAR(13)
+ '-- partition ' + cast(@partitionnum as varchar(10)) + ', avg frag in percent ' + cast(@currentfrag as varchar(50))
+ ', avg page space used in percent ' + cast(@currentdensity as varchar(50))
END;
ELSE
BEGIN;
SET @StatusMsg = @StatusMsg + @NewLine + '-- Processing LOB table ' + (CASE ISNULL(@Schemaname, '') WHEN '' THEN ' ' ELSE @Schemaname END)
+ '.' + (CASE ISNULL(@Objectname, '') WHEN '' THEN ' ' ELSE @Objectname END) + ', STANDARD index ' + @indexname + ', ' + CHAR(13)
+ '-- partition ' + cast(@partitionnum as varchar(10)) + ', avg frag in percent ' + cast(@currentfrag as varchar(50))
+ ', avg page space used in percent ' + cast(@currentdensity as varchar(50))
END;
END;
ELSE
BEGIN;
SELECT @err = @@error IF @err <> 0 GOTO FAILONINDEX
IF @indexid = 1
BEGIN;
SET @StatusMsg = @StatusMsg + @NewLine + '-- Processing STANDARD table ' + (CASE ISNULL(@Schemaname, '') WHEN '' THEN ' ' ELSE @Schemaname END)
+ '.' + (CASE ISNULL(@Objectname, '') WHEN '' THEN ' ' ELSE @Objectname END) + ', CLUSTERED index ' + @indexname + ', ' + CHAR(13)
+ '-- partition ' + cast(@partitionnum as varchar(10)) + ', avg frag in percent ' + cast(@currentfrag as varchar(50))
+ ', avg page space used in percent ' + cast(@currentdensity as varchar(50))
END;
ELSE
BEGIN;
SET @StatusMsg = @StatusMsg + @NewLine + '-- Processing STANDARD table ' + (CASE ISNULL(@Schemaname, '') WHEN '' THEN ' ' ELSE @Schemaname END)
+ '.' + (CASE ISNULL(@Objectname, '') WHEN '' THEN ' ' ELSE @Objectname END) + ', STANDARD index ' + @indexname + ', ' + CHAR(13)
+ '-- partition ' + cast(@partitionnum as varchar(10)) + ', avg frag in percent ' + cast(@currentfrag as varchar(50))
+ ', avg page space used in percent ' + cast(@currentdensity as varchar(50))
END;
END;
SET @countprocessed = @countprocessed + 1
IF @runrebuild = 1 SET @StatusMsg = @StatusMsg + @NewLine + '-- Executed: ' + (CASE ISNULL(@command, '') WHEN '' THEN ' ' ELSE @command END);
ELSE SET @StatusMsg = @StatusMsg + @NewLine + '-- Code to be executed: ' + CHAR(13) + (CASE ISNULL(@command, '') WHEN '' THEN ' ' ELSE @command END);
END;
SELECT
@postdensity= fi.avg_page_space_used_in_percent,
@postfrag = fi.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(db_id(@databasename), NULL, NULL, NULL, 'SAMPLED') AS fi
WHEREindex_id = @indexid and object_id = @tableid
SET @StatusMsg = @StatusMsg + @NewLine + '-- Results: ' + 'avg frag in percent ' + cast(@postfrag as varchar(50))
+ ', avg page space used in percent ' + cast(@postdensity as varchar(50))
NEXTINDEX:
FETCH NEXT FROM Local_Rebuildindex_Cursor INTO @indexid, @tableid, @indexname, @objectname, @schemaname, @indextype, @partitionnum, @partitioncount, @currentdensity, @currentfrag;
END;
CLOSE Local_Rebuildindex_Cursor;
DEALLOCATE Local_Rebuildindex_Cursor;
CODEEXIT:
SELECT @RecoveryMode = cast(DATABASEPROPERTYEX(@databasename, 'Recovery') as varchar(20))
IF @RecoveryMode <> @RecoveryModeOld
BEGIN;
SELECT @altdbafter = N'ALTER DATABASE [' + @databasename + N'] SET RECOVERY ' + @RecoveryModeOld + N'; '
EXEC(@altdbafter)
SELECT @dbStatusMsg = '-- Recovery model for database ' + @databasename + ' was set back to original ' + @RecoveryModeOld + ' from ' + @RecoveryMode + ' recovery mode.'
SET @StatusMsg = @StatusMsg + @NewLine + @dbStatusMsg
END;
IF @DBMirrorPerf = 1 and @dbmirrorold > 1
BEGIN;
IF @dbmirrorold = 2 -- DB mirroring in high protection mode
BEGIN;
SELECT @altdbmirrorafter = N'ALTER DATABASE [' + @databasename + N'] SET PARTNER SAFETY FULL; '
EXEC(@altdbmirrorafter)
SELECT @dbStatusMsg = '-- Mirror safety level for database ' + @databasename + ' was changed back to FULL.'
SET @StatusMsg = @StatusMsg + @NewLine + @dbStatusMsg
END;
IF @dbmirrorold = 3 -- DB mirroring in high availability mode
BEGIN;
SELECT @altdbmirrorafter = N'ALTER DATABASE [' + @databasename + N'] SET PARTNER SAFETY FULL WITNESS [' + @dbmirrorwitness + N']; '
EXEC(@altdbmirrorafter)
SELECT @dbStatusMsg = '-- Mirror safety level for database ' + @databasename + ' was changed to FULL with witness.'
SET @StatusMsg = @StatusMsg + @NewLine + @dbStatusMsg
END;
END;
SET @StatusMsg = @StatusMsg + @NewLine
IF @countprocessed = 0
BEGIN;
SET @StatusMsg = @StatusMsg + @NewLine + '-- No indexes needed rebuilding in database ' + @databasename
END;
ELSE
BEGIN;
SELECT @altdbafter = N''
SELECT @mycode = @altdbbefore + @mycode + @altdbafter
IF @runrebuild = 1
BEGIN
SET @StatusMsg = @StatusMsg + @NewLine + '-- ' + cast(@countprocessed as varchar(20)) + ' indexes were reorganized or rebuilt!'
END
ELSE
BEGIN
SET @StatusMsg = @StatusMsg + @NewLine + '-- Code for reorganize and/or rebuild of ' + cast(@countprocessed as varchar(20)) + ' indexes was generated!'
END
END;
SET @StatusMsg = @StatusMsg + @NewLine
-- Return codes (@outparm): 0=OK, 4=Exit on Other Error
IF @outparm = 0
BEGIN;
SET @StatusMsg = @StatusMsg + @NewLine + N'-- Returned execution status for Admin_DB.dbo.spr_RebuildIndexes after processing '
+ @databasename + N' on SQL Server ' + @@servername + N' is Index rebuild OK!'
SET @status = 'Ok'
END;
ELSE IF @outparm = 4
BEGIN;
SET @StatusMsg = @StatusMsg + @NewLine + N'-- Returned execution status for Admin_DB.dbo.spr_RebuildIndexes after processing '
+ @databasename + N' on SQL Server ' + @@servername + N' is Exit on Other Error!'
SET @status = 'Exit on Other Error'
GOTO MAXTIMEOUT
END;
ELSE
BEGIN;
SET @StatusMsg = @StatusMsg + @NewLine + N'-- Returned execution status for Admin_DB.dbo.spr_RebuildIndexes after processing '
+ @databasename + N' on SQL Server ' + @@servername + N' is Unknown Exit Code!'
SET @status = 'Unknown Exit Code'
GOTO MAXTIMEOUT
END;
BEGIN;
---Calculate time remaining in seconds
SELECT @totalsecondspassed = DATEDIFF(ss, @startmain, getdate())
SELECT @endeddate = getdate()
SET @StatusMsg = @StatusMsg + @NewLine + '-- END OF INDEX DEFRAG FOR DATABASE ' + @databasename + ' AT ' + CONVERT (VARCHAR(20), getdate(), 120)
SET @StatusMsg = @StatusMsg + @NewLine + '-- Processing time for database ' + @databasename + ' was ' + Cast((DATEDIFF(ss, @starteddate, getdate())) as varchar(20)) + ' seconds.'
SET @StatusMsg = @StatusMsg + @NewLine + '++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++'
END;
LOGSTATUS:
--Insert Log in Job Log table
INSERT INTO [Admin_DB].[dbo].[MyJobLog]
([DatabaseName]
,[JobRunStartDateTime]
,[JobRunEndDateTime]
,[JobIndexCount]
,[JobStatus]
,[JobLog])
VALUES
(@databasename
,@starteddate
,GetDate()
,@countprocessed
,@Status
,@StatusMsg)
IF @SendEmail = 1 and @SendSummaryOnly = 0
BEGIN
-- Name of current sender
SET @myfromname = N'Message from SQL Server ' + @@servername
-- Get e-mail adresses of operators
BEGIN
SET @alladdresses = N''
DECLARE MAILResults_CURSOR CURSOR FORWARD_ONLY READ_ONLY FOR
SELECT eMail_address FROM msdb.dbo.sysoperators WITH (NOLOCK) where name = 'MyCompany_SQLDBA'
OPEN MAILResults_CURSOR
FETCH NEXT FROM MAILResults_CURSOR INTO @myrecipients
WHILE @@FETCH_STATUS = 0
BEGIN
SET @mycurrentaddres = @myrecipients + CHAR(59)
SET @alladdresses = @alladdresses + @mycurrentaddres
FETCH NEXT FROM MAILResults_CURSOR INTO @myrecipients
END
CLOSE MAILResults_CURSOR
DEALLOCATE MAILResults_CURSOR
IF @alladdresses <> N''
BEGIN
SET@SubjectLocal = 'Status ' + @status + ': Reorg\Rebuild - ' + @databasename + ' DB on ' + @@servername
EXEC msdb.dbo.sp_send_dbmail
@profile_name = NULL
,@recipients = @alladdresses
,@copy_recipients = NULL
,@blind_copy_recipients = NULL
,@subject = @SubjectLocal
,@body = @StatusMsg
,@body_format = 'TEXT'
,@importance = 'Normal'
,@sensitivity = 'Normal'
END
SELECT @err = @@error IF @err <> 0 GOTO FAILONEMAIL
END
END
END;
NEXTDB:
SET@StatusMsg = N''
FETCH NEXT FROM Main_Cursor INTO @databasename
END;
IF @SendEmail = 1 and @SendSummaryOnly = 1
BEGIN
-- Get e-mail adresses of operators
BEGIN
SET @alladdresses = N''
DECLARE MAILResults_CURSOR CURSOR FORWARD_ONLY READ_ONLY FOR
SELECT eMail_address FROM msdb.dbo.sysoperators WITH (NOLOCK) where name = 'MyCompany_SQLDBA'
OPEN MAILResults_CURSOR
FETCH NEXT FROM MAILResults_CURSOR INTO @myrecipients
WHILE @@FETCH_STATUS = 0
BEGIN
SET @mycurrentaddres = @myrecipients + CHAR(59)
SET @alladdresses = @alladdresses + @mycurrentaddres
FETCH NEXT FROM MAILResults_CURSOR INTO @myrecipients
END
CLOSE MAILResults_CURSOR
DEALLOCATE MAILResults_CURSOR
SET@MailBody = 'Following is summary report Reorg\Rebuild Job ran on ' + @@servername
DECLARE MAILBody_CURSOR CURSOR FORWARD_ONLY READ_ONLY FOR
SELECT [DatabaseName]
,[JobRunStartDateTime]
,[JobRunEndDateTime]
,[JobIndexCount]
,[JobStatus]
FROM[Admin_DB].[dbo].[MyJobLog]
WHEREJobRunStartDateTime >= @MainStartDateTime
OPEN MAILBody_CURSOR
FETCH NEXT FROM MAILBody_CURSOR INTO @Databasename, @JobRunStartDateTime, @JobRunEndDateTime, @JobIndexCount, @JobStatus
WHILE @@FETCH_STATUS = 0
BEGIN
SET @MailBody = @MailBody + @NewLine
SET @MailBody = @MailBody + @NewLine + 'Database: ' + @DatabaseName
SET @MailBody = @MailBody + @NewLine + 'Status: ' + @JobStatus
SET @MailBody = @MailBody + @NewLine + 'Indexes Reorg\Rebuild: ' + CONVERT(NVARCHAR (50), @JobIndexCount)
SET @MailBody = @MailBody + @NewLine + 'Duration: ' + CONVERT(NVARCHAR (50), DATEDIFF(ss, @JobRunStartDateTime, @JobRunEndDateTime)) + ' Second(s)'
FETCH NEXT FROM MAILBody_CURSOR INTO @Databasename, @JobRunStartDateTime, @JobRunEndDateTime, @JobIndexCount, @JobStatus
END
CLOSE MAILBody_CURSOR
DEALLOCATE MAILBody_CURSOR
IF @alladdresses <> N''
BEGIN
SET@SubjectLocal = 'Reorg\Rebuild Summary Report For ' + @@servername
EXEC msdb.dbo.sp_send_dbmail
@profile_name = NULL
,@recipients = @alladdresses
,@copy_recipients = NULL
,@blind_copy_recipients = NULL
,@subject = @SubjectLocal
,@body = @MailBody
,@body_format = 'TEXT'
,@importance = 'Normal'
,@sensitivity = 'Normal'
END
END
END
MAXTIMEOUT:
IF Cursor_Status('GLOBAL', 'Main_Cursor') >= 0
BEGIN
CLOSE Main_Cursor
DEALLOCATE Main_Cursor
END
--Write Success to Event Log
LOGINFOANDEXIT:
EXEC master..xp_logevent 65555, 'Reorg / Rebuild Indexes Job ran successfully. Review [MyJobLog] table for details', INFORMATIONAL
SET @Retry = -1
RETURN
FAILONDB:
PRINT @ErrorMsg
EXEC master..xp_logevent 65556, @ErrorMsg, ERROR
IF Cursor_Status('GLOBAL', 'Main_Cursor') >= 0
BEGIN
CLOSE Main_Cursor
DEALLOCATE Main_Cursor
END
RETURN
FAILONINDEX:
SET @ErrorMsg = @ErrorMsg + @NewLine + 'Reorg \ Rebuild Index Job Error Information For Database:' + @databasename
SET @ErrorMsg = @ErrorMsg + @NewLine + 'Error Number: ' + CONVERT(VARCHAR(50), ERROR_NUMBER())
SET @ErrorMsg = @ErrorMsg + @NewLine + 'Error Severity: ' + CONVERT(VARCHAR(5), Error_Severity())
SET @ErrorMsg = @ErrorMsg + @NewLine + 'Error State: ' + CONVERT(VARCHAR(5), Error_State())
SET @ErrorMsg = @ErrorMsg + @NewLine + 'Error Line: ' + CONVERT(VARCHAR(5), ERROR_LINE())
SET @ErrorMsg = @ErrorMsg + @NewLine + 'Error Message: ' + Error_Message()
SET @StatusMsg = @StatusMsg + @NewLine + 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'
SET @StatusMsg = @StatusMsg + @NewLine + @ErrorMsg
SET @StatusMsg = @StatusMsg + @NewLine + 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'
SET @Retry = @Retry + 1
GOTO NEXTINDEX
FAILONEMAIL:
SET @ErrorMsg = @ErrorMsg + @NewLine + 'Reorg \ Rebuild Index Job Email Error Information For Database:' + @databasename
SET @ErrorMsg = @ErrorMsg + @NewLine + 'Error Number: ' + CONVERT(VARCHAR(50), ERROR_NUMBER())
SET @ErrorMsg = @ErrorMsg + @NewLine + 'Error Severity: ' + CONVERT(VARCHAR(5), Error_Severity())
SET @ErrorMsg = @ErrorMsg + @NewLine + 'Error State: ' + CONVERT(VARCHAR(5), Error_State())
SET @ErrorMsg = @ErrorMsg + @NewLine + 'Error Line: ' + CONVERT(VARCHAR(5), ERROR_LINE())
SET @ErrorMsg = @ErrorMsg + @NewLine + 'Error Message: ' + Error_Message()
SET @Retry = @Retry + 1
GOTO NEXTDB
END
END
GO
BT -
Parses fine on my 2012 instance. Can't run it without the tables, but it parses fine.
Since you changed the variable names when you pasted the errors, I can't try to figure out where they might be coming from. Likely from the dynamic SQL.
The errors gave you the line numbers, with those and the actual variable names you should be able to get a good idea where the problems are.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Actual ERRORs:
Msg 137, Level 15, State 2, Procedure sp_MyProc, Line 195
Must declare the scalar variable "@Online".
Msg 137, Level 15, State 2, Procedure sp_MyProc, Line 197
Must declare the scalar variable "@retry".
Msg 137, Level 15, State 2, Procedure sp_MyProc, Line 249
Must declare the scalar variable "@DatabaseName".
Msg 156, Level 15, State 1, Procedure sp_MyProc, Line 261
Incorrect syntax near the keyword 'ELSE'.
Msg 137, Level 15, State 1, Procedure sp_MyProc, Line 421
Must declare the scalar variable "@outparm".
Msg 137, Level 15, State 1, Procedure sp_MyProc, Line 680
Must declare the scalar variable "@outparm".
Msg 137, Level 15, State 1, Procedure sp_MyProc, Line 685
Must declare the scalar variable "@outparm".
Msg 137, Level 15, State 1, Procedure sp_MyProc, Line 697
Must declare the scalar variable "@outparm".
Msg 137, Level 15, State 1, Procedure sp_MyProc, Line 702
Must declare the scalar variable "@outparm".
Msg 137, Level 15, State 2, Procedure sp_MyProc, Line 710
Must declare the scalar variable "@Schemaname".
Msg 137, Level 15, State 2, Procedure sp_MyProc, Line 717
Must declare the scalar variable "@Schemaname".
Msg 137, Level 15, State 2, Procedure sp_MyProc, Line 724
Must declare the scalar variable "@Schemaname".
Msg 137, Level 15, State 2, Procedure sp_MyProc, Line 735
Must declare the scalar variable "@Schemaname".
Msg 137, Level 15, State 2, Procedure sp_MyProc, Line 742
Must declare the scalar variable "@Schemaname".
Msg 137, Level 15, State 2, Procedure sp_MyProc, Line 829
Must declare the scalar variable "@outparm".
Msg 156, Level 15, State 1, Procedure sp_MyProc, Line 835
Incorrect syntax near the keyword 'ELSE'.
Msg 137, Level 15, State 2, Procedure sp_MyProc, Line 835
Must declare the scalar variable "@outparm".
Msg 156, Level 15, State 1, Procedure sp_MyProc, Line 842
Incorrect syntax near the keyword 'ELSE'.
Msg 137, Level 15, State 2, Procedure sp_MyProc, Line 959
Must declare the scalar variable "@Databasename".
Msg 137, Level 15, State 2, Procedure sp_MyProc, Line 963
Must declare the scalar variable "@DatabaseName".
Msg 137, Level 15, State 2, Procedure sp_MyProc, Line 968
Must declare the scalar variable "@Databasename".
BT -
Is your 2012 instance case sensitive perhaps? If it is, that may be a cause. There's different capitalisation of the variables in different places and would explain why it's fine on mine and Sean's instances.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Gail -- awesome find! yes, the vendor mandated we set COLLATION to: Latin1_General_BIN
using this command: I validated the COLLATION as Latin1_General_BIN
So I modified the 1st couple of variables (eg. @databasename, @online) to be all lowercase and they disappeared from my ERRORS list during a check.
I will proceed w/ changing all of the variables to match.
In the words of Bobby D -- "you're good"
BT -
Just a suggestion based on my own philosophy, regardless of collation used (case sensitive or case insensitive) you should write your code as if it is always case sensitive. I do this and I don't run into issues like this.
-
Express12 (11/11/2013)
the vendor mandated we set COLLATION to: Latin1_General_BINFire that vendor.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Lynn -- thanks for that tip! fyi, I actually scraped that proc (a "smart reindex" proc) and have been using it for about 3 years now.
Do you guys have a recommendation on a new flavor of a "smart reindex" process out there? One which will:
• Selectively identify objects (tables/indexes) requiring ReIndex (either Rebuild or Reorg) -- based on customizable thresholds including:
-- Fragmentation % (optimally, the object is 0% fragmented)
-- Scan Density % (optimally, the object is 100% dense)
-- DBCC SHOWCONTIG renders these values for each object
• Determine whether to Rebuild or Reorg the object
• Perform Reindex operation ONLINE leaving the active object available for CRUD
• Perform Update Statistics
• Perform Recompile Procs
Again - many thx in advance!
BT -
Express12 (11/11/2013)
• Perform Recompile ProcsNot required, waste of time at best.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply