Bogus syntax error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Bogus syntax error

Author

Topic

Clothahump
Starting Member

18 Posts

Posted - 2011-04-21 : 10:40:36

I am getting a bogus syntax error. SQL Server tells me Incorrect syntax near '(' on the marked line, but as we can see, there is no left paren on that line.

INSERT #DefragList (TableName, IndexName, CountRows, StatPercent, IndexID,FragPCT, IsClustered)
SELECT c.name as TableName, b.name as IndexName, d.rows as CountRows,
CASE
======>>>> WHEN d.Rows BETWEEN 0 AND 5000 THEN 100
WHEN d.Rows BETWEEN 5000 AND 25000 THEN 75
WHEN d.Rows BETWEEN 25000 AND 50000 THEN 50
WHEN d.Rows BETWEEN 50000 AND 100000 THEN 30
WHEN d.Rows BETWEEN 100000 AND 500000 THEN 20
WHEN d.Rows BETWEEN 500000 AND 1000000 THEN 10
ELSE 5
END as StatPerCent ,
ps.index_id as IndexId, ps.avg_fragmentation_in_percent as FragPct,
indexproperty(object_id(c.name),b.name,'IsClustered') as IsClustered
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps
INNER JOIN sys.indexes (nolock) AS b ON ps.OBJECT_ID = b.OBJECT_ID
AND ps.index_id = b.index_id
inner join sys.tables (nolock) as C on ps.object_id = c.object_id
inner join sysindexes (nolock) as d on ps.object_id = d.id and ps.index_id=d.indid
WHERE ps.database_id = @CurrentDBid
and b.name is not null
and c.name like 'c%'
and ps.avg_fragmentation_in_percent >= 11
and d.rows > 100

Has anyone run into something like this before? If so, what did you do?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-21 : 10:45:57

I copied your code to a query window and tried to parse it. It seems to parse without any errors. The only changes I made are shown in red (other than formatting it nicely, of course!!)


INSERT	#DefragList(
     	               TableName,
     	               IndexName,
     	               CountRows,
     	               StatPercent,
     	               IndexID,
     	               FragPCT,
     	               IsClustered
     	           )
SELECT
	c.name AS TableName,
	b.name AS IndexName,
	d.rows AS CountRows,
	CASE  --====== > > > > 
	     WHEN d.Rows BETWEEN 0 AND 5000 THEN 100
	     WHEN d.Rows BETWEEN 5000 AND 25000 THEN 75
	     WHEN d.Rows BETWEEN 25000 AND 50000 THEN 50
	     WHEN d.Rows BETWEEN 50000 AND 100000 THEN 30
	     WHEN d.Rows BETWEEN 100000 AND 500000 THEN 20
	     WHEN d.Rows BETWEEN 500000 AND 1000000 THEN 10
	     ELSE 5
	END AS StatPerCent,
	ps.index_id AS IndexId,
	ps.avg_fragmentation_in_percent AS FragPct,
	INDEXPROPERTY(OBJECT_ID(c.name), b.name, 'IsClustered') AS IsClustered
FROM
	sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps
	INNER JOIN sys.indexes (NOLOCK) AS b
		ON  ps.OBJECT_ID = b.OBJECT_ID AND ps.index_id = b.index_id
	INNER JOIN sys.tables (NOLOCK) AS C
		ON  ps.object_id = c.object_id
	INNER JOIN sysindexes(NOLOCK) AS d
		ON  ps.object_id = d.id AND ps.index_id = d.indid
WHERE
	ps.database_id =  1 -- @CurrentDBid
	AND b.name IS NOT NULL
	AND c.name LIKE 'c%'
	AND ps.avg_fragmentation_in_percent >= 11
	AND d.rows > 100

Clothahump
Starting Member

18 Posts

Posted - 2011-04-21 : 11:31:27

I know. This is what is bugging me. There are 154 lines of code before this block. If I select them and check them, they indicate no errors, so it's not like I have a spare error left over or something.

But if I then highlight from line one down to the bottom of this block of code (or, of course, if I simply parse the entire procedure), this error shows up.

I'm about ready to beat my head on my desk, because this block of code works nicely in another procedure.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-04-21 : 11:54:26

There's gotta be a CTE or a GO statement or some such in there somewhere. Your code runs for me, so you may just have to post the whole enchilada so we can see what the bug may be.

Jim

Everyday I learn something that somebody else already knew

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-21 : 11:54:38

Before you do that ("beating your head on your desk"

) can you post the entire code including the 154 lines you omitted?

Clothahump
Starting Member

18 Posts

Posted - 2011-04-21 : 13:54:48

In all its glory. This proc has been reworked, so there are a ton of commented-out lines that will eventually be removed.

USE [CTCurrent]
GO
/****** Object: StoredProcedure [dbo].[DBA_IndexMaint] Script Date: 04/14/2011 10:47:19 ******/
-- modified crs 04/14/2011 to use Alter Index instead of DBCC DBReindex
-- this was done so that we can have the online option and stop this process from being killed
-- because it's blocking other processes
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

ALTER PROCEDURE [dbo].[DBA_IndexMaint](
@CheckScanDensity Int=80, -- The Percent of Scan Density that will trigger an Index Defrag/Rebuild
@DfCheckShowContig Int=-2, -- Number of days to go back for Checking the Show Contig
@DfCheckDefrag Int=-2, -- Number of days to go back for attempt Index Defrag/Rebuild
@DfCheckStat Int=-1, -- Number of days to go back for Updating Statistics
@Override Int=0, -- Specifying a 1 will cause it to ignore the times set in tblMaintWindow
@BlockingRebuild Int=NULL, -- Specifying a 1 will cause the job to do a Index Rebuild, will override tblMaintWindow, leaving NULL will let tblMaintWindow decide
@ForceAllIndexes Int=0, -- Specifying a 1 will cause the job to do a Index Defrag/Rebuild on all Indexes in tblTableMaint
@AutoLoadTables Int=1, -- Specifying a 1 will cause a check against sysIndexes for the contents of tblTableMaint
@TestMode Int=0) -- Specifying a 1 will run this process in Test Mode so that the commands are printed instead of executed
AS

-- Written By: Jason Harrison
-- On: 10/8/07
-- Updated On: 11/16/07
-- Version: 1.0
-- Purpose: This process will run based on existing Maintenance tables in the current
-- database. These tables are:
-- tblMaintWindow -- Indicates the time period that this process can run
-- tblTableMaint -- Has a list of all tables/indexes to run this process against
-- tblIndexMaint -- Is a history of maintenance that has occurred on these tables
-- Process was written to stop processing after a certain time was exceeded. It
-- can use either IndexDefrag or DBReIndex commands based on parameter or table setting. The
-- IndexDefrag portion was written to not experience any user down time.

-- Temp Code to Preceed Maintenance -- Added 7/28/08 to be remove 7/29/2008
--EXEC DBA_UpdateReleaseToCode
-- Updated on 04/18/2011 C. Sears
-- major overhaul to run reindexing based on the fragmentation percentage
-- reported by the system

SET NOCOUNT ON

-- Declare variables
DECLARE @TableName VarChar(255),
@IndexName VarChar(255),
@ExecStr VarChar(255),
@ObjectId Int,
@IndId Int,
@ScanDensity Decimal,
@Row Int,
@Rows Int,
@CountRows Int,
@StartDate DateTime,
@StopDate DateTime,
@LastDefrag DateTime,
@LastShowContig DateTime,
@LastStatUpdate DateTime,
@ProcessDate DateTime,
@StartTime VarChar(10),
@IndexID Int,
@StatPercent Int,
@JobState Int,
@JobEnabled Int,
@Step Int,
@IndMntType VarChar(10),
@CheckLog Int,
@CheckShowContig Int,
@CheckDefrag Int,
@CheckStat Int,
@CurrentDBid int,
@FragPct float,
@IsClustered int,
@ErrorCode int,
@ErrRowCount int,
@SQL varchar (255),
@Errormsg varchar(255)

create table #DefragList (
TableName varchar (100),
IndexName varchar (100),
CountRows int,
StatPercent int,
IndexID int,
FragPct float,
IsClustered int
)

-- Clean Out tblIndexMaint
DELETE tblIndexMaint WHERE MaintDate < DATEADD(dd,-30,GETDATE())

--IF @AutoLoadTables = 1
--BEGIN
/* crs 04/18/2011 modified to select all indexes with more than
10% fragmentation and 100 rows

-- Add New Tables that meet the desired criteria
-- and don't already exist in tblTableMaint
INSERT tblTableMaint(TableName, IndexName, CountRows, StatPercent, IndID)
SELECT O.Name TableName,
CASE WHEN I.IndID = 0 THEN O.Name ELSE I.Name END AS IndexName,
I.Rows CountRows,
CASE
WHEN I.Rows BETWEEN 0 AND 5000 THEN 100
WHEN I.Rows BETWEEN 5000 AND 25000 THEN 75
WHEN I.Rows BETWEEN 25000 AND 50000 THEN 50
WHEN I.Rows BETWEEN 50000 AND 100000 THEN 30
WHEN I.Rows BETWEEN 100000 AND 500000 THEN 20
WHEN I.Rows BETWEEN 500000 AND 1000000 THEN 10
ELSE 5
END ,
I.IndID
FROM sysObjects O
INNER JOIN sysIndexes I
ON O.ID = I.ID
LEFT JOIN tblTableMaint T
ON T.Tablename = O.Name AND
T.IndexName = I.Name
WHERE O.xType = 'U' AND
I.Rows > 100 AND
I.IndID <> 255 AND
T.TableName IS NULL
ORDER BY
I.Rows DESC
END
--------- change starts below */

-- Set StartDate/StopDate based off values in tblMainWindow
-- Using Order By to get last row assigned to variables
SELECT @StartDate = CONVERT(DateTime,CONVERT(VarChar(10),GETDATE(),101) + ' ' + StartTime),
@StopDate = CONVERT(DateTime,CONVERT(VarChar(10),GETDATE(),101) + ' ' + StopTime),
@IndMntType = MaintType
FROM tblMaintWindow
WHERE DayOfWeek = DATEPART(dw,GETDATE()) AND
WeekDayInMonth IN (-1, dbo.fn_WeekDayInMonth(GETDATE()))
ORDER BY
WeekDayInMonth

-- If too early to run yet, exit and let job re-run later
IF (@StartDate > GETDATE() OR @StopDate < GETDATE()) AND @Override = 0
BEGIN
PRINT 'Cannot start yet, not within time bracket '
GOTO EndProc
--SELECT @StartTime = CONVERT(VarChar,DATEPART(hh,@StartDate)) + ':' + CONVERT(VarChar,DATEPART(mi,@StartDate))
--WAITFOR TIME @StartTime
END

select @CurrentDBid=DB_ID()
INSERT #DefragList (TableName, IndexName, CountRows, StatPercent, IndexID,FragPCT, IsClustered)
SELECT c.name as TableName, b.name as IndexName, d.rows as CountRows,
CASE
WHEN d.Rows BETWEEN 0 AND 5000 THEN 100
WHEN d.Rows BETWEEN 5000 AND 25000 THEN 75
WHEN d.Rows BETWEEN 25000 AND 50000 THEN 50
WHEN d.Rows BETWEEN 50000 AND 100000 THEN 30
WHEN d.Rows BETWEEN 100000 AND 500000 THEN 20
WHEN d.Rows BETWEEN 500000 AND 1000000 THEN 10
ELSE 5
END ,
ps.index_id as IndexId, ps.avg_fragmentation_in_percent as FragPct,
indexproperty(object_id(c.name),b.name,'IsClustered') as IsClustered
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps
INNER JOIN sys.indexes (nolock) AS b ON ps.OBJECT_ID = b.OBJECT_ID
AND ps.index_id = b.index_id
inner join sys.tables (nolock) as C on ps.object_id = c.object_id
inner join sysindexes (nolock) as d on ps.object_id = d.id and ps.index_id=d.indid
WHERE ps.database_id = @CurrentDBid
and b.name is not null
and c.name like 'c%'
and ps.avg_fragmentation_in_percent >= 11
and d.rows > 100

select @ErrRowCount = @@ROWCOUNT
PRINT 'Tables to Process: ' + CONVERT(VarChar,@ErrRowCount)
-- if we have zero, quit
if @ErrRowCount = 0
begin
INSERT tblIndexMaint(TableName, IndexName, MaintDate, MaintStep)
VALUES( 'ALL', 'ALL', getDate(), 'No tables needed index maintenance')
goto EndProc
end
-------end change

-- If BlockingRebuild is set to 0, then it was passed in as so and should override the tblMaintWindow table
/* crs this isn't used because we determine what to do based on the frag pct
IF @BlockingRebuild = 0
BEGIN
SELECT @BlockingRebuild = 0
SELECT @IndMntType = 'Defrag'
END
-- Else if either were set, then will do a blocking rebuild
ELSE IF @IndMntType = 'REBUILD' OR @BlockingRebuild = 1
BEGIN
SELECT @BlockingRebuild = 1
SELECT @IndMntType = 'Rebuild'
END
-- Otherwise, just do a defrag
ELSE
BEGIN
SELECT @BlockingRebuild = 0
SELECT @IndMntType = 'Defrag'
END
*/

-- This will enable and start a job that will watch for blocks
EXEC DBA_CheckJobState 'Watch Nightly Maintenance',
@JobState OUTPUT, -- 0 = Not idle or suspended, 1 = Executing, 2 = Waiting For Thread, 3 = Between Retries, 4 = Idle, 5 = Suspended, [6 = WaitingForStepToFinish], 7 = PerformingCompletionActions
@JobEnabled OUTPUT
EXEC DBA_CheckMaintError 'Job', 'Check State','Watch Nightly Maintenance', @@ERROR, @Step OUTPUT

IF @JobEnabled = 0
BEGIN
EXEC MSDB..sp_update_job @job_name = 'Watch Nightly Maintenance', @enabled= 1
EXEC DBA_CheckMaintError 'Job', 'Enable','Watch Nightly Maintenance', @@ERROR, @Step OUTPUT
END

IF @JobState = 4 AND @JobEnabled = 0
BEGIN
EXEC MSDB..sp_start_job @job_name = 'Watch Nightly Maintenance'
EXEC DBA_CheckMaintError 'Job', 'Start','Watch Nightly Maintenance', @@ERROR, @Step OUTPUT
END

-- set our benchmark
INSERT tblIndexMaint(TableName, IndexName, MaintDate, MaintStep)
VALUES( 'All', 'All', GETDATE(), 'Start of Table Maintenance')

/* crs changed to use the temp table
-- ShowContig Loop
-- Keep processing all rows until done or StopDate has been passed
WHILE @Row <= @Rows AND (GETDATE() < @StopDate OR @Override = 1)
BEGIN
SELECT @Step = 0

PRINT 'Get Data from @Tables for ' + CONVERT(VarChar,@Row) + ' row.'
SELECT @TableName = TableName,
@IndexName = IndexName,
@LastShowContig = ISNULL(LastShowContig,'1/1/1900'),
@LastDefrag = ISNULL(LastDefrag,'1/1/1900'),
@LastStatUpdate = ISNULL(LastStatUpdate,'1/1/1900'),
@CountRows = CountRows,
@ScanDensity = ISNULL(LastScanDensity,0),
@StatPercent = ISNULL(StatPercent, 5),
@IndID = IndID,
@CheckShowContig= ABS(ISNULL(CheckShowContig, @DfCheckShowContig)) * -1,
@CheckDefrag = ABS(ISNULL(CheckDefrag, @DfCheckDefrag)) * -1,
@CheckStat = ABS(ISNULL(CheckStat, @DfCheckStat)) * -1
FROM @Tables
WHERE Row = @Row

*/
-- we'll select from the temp table in desc order of fragmentation so that the worst indexes
-- get priority treatment
declare Frag_cursor cursor for
select * from #Defraglist order by FragPct desc

open Frag_cursor
fetch next from frag_cursor
into @Tablename,@Indexname,@Countrows,@StatPercent,@IndId,@FragPct, @IsClustered

while @@Fetch_Status =0
BEGIN

PRINT 'Processing Table: ' + @TableName + ' and Index: ' + @IndexName
select @Step = 1
SELECT @LastShowContig = GETDATE()
-- get some data on the index

INSERT tblTableMaint(TableName, IndexName, CountRows, StatPercent, IndID)
values (@tablename,@IndexName,@CountRows,@StatPercent,@indid)

INSERT tblIndexMaint(TableName, IndexName, MaintDate, MaintStep)
VALUES( @TableName, @IndexName, @LastShowContig, CONVERT(VarChar, @Step) + '-Begin Processing')

-- look at the index pct. We already know it's above 10 because it got selected. If it's above 30,
-- we'll do a rebuild, otherwise we'll just reorganize it.

if @Fragpct > 30
begin
select @IndMnttype = 'Rebuild'
end
else
begin
select @IndMnttype = 'Reorganize'
end

PRINT 'Preparing to run ' + @IndMntType + ' on Table: ' + @TableName + ' Index: ' + @IndexName

-- Note time Defrag started
SELECT @Step = @Step + 1
INSERT tblIndexMaint(TableName, IndexName, MaintDate, MaintStep)
VALUES( @TableName, @IndexName, GETDATE(), CONVERT(VarChar, @Step) + '-Start ' + @IndMntType)

if @IndMnttype = 'Rebuild'
begin
PRINT 'Rebuilding ([' + RTRIM(@tablename) + '], [' + RTRIM(@IndexName) + '])'
-- we'll rebuild using a fill factor of 90 and padding the non-leaf level pages as well
SELECT @ExecStr = 'Alter Index ' + RTRIM(@IndexName) + ' on ' + @TableName + ' REBUILD WITH (PAD_INDEX = ON, FILLFACTOR=90'
-- if it is not a clustered index, we can do it online
if @isclustered = 0
begin
select @Execstr = @Execstr + ',ONLINE=ON'
end
select @Execstr = @Execstr + ')'

IF @TestMode = 0
begin
BEGIN try
EXEC (@ExecStr)
end try
begin catch
select @Errorcode=@@Error,@ErrRowCount=@@RowCount
goto handle_error
end catch
END
EXEC DBA_CheckMaintError 'RE', @TableName, @IndexName, @@ERROR, @Step OUTPUT
END
ELSE
BEGIN
PRINT 'Reorganizing (0, [' + RTRIM(@tablename) + '], [' + RTRIM(@IndexName) + '])'
-- crs 04/14/11 SELECT @ExecStr = 'DBCC INDEXDEFRAG (0, [' + @TableName + '], [' + RTRIM(@IndexName) + '])'
SELECT @ExecStr = 'Alter Index ' + RTRIM(@IndexName) + ' on ' + @TableName + ' REORGANIZE '
IF @TestMode = 0
BEGIN
EXEC (@ExecStr)
END
EXEC DBA_CheckMaintError 'DF', @TableName, @IndexName, @@ERROR, @Step OUTPUT
END

PRINT 'Finished running ' + @IndMntType + ' on Table: ' + @TableName + ' Index: ' + @IndexName

-- Note time Defrag finished
SELECT @LastDefrag = GETDATE()
SELECT @Step = @Step + 1
INSERT tblIndexMaint(TableName, IndexName, MaintDate, MaintStep)
VALUES( @TableName, @IndexName, @LastDefrag, CONVERT(VarChar, @Step) + '-Stop ' + @IndMntType)

-- do we need to check stats?

IF @IndMnttype='Reorganize' -- If it is a Rebuild, then Index Statistics will already be rebuilt
BEGIN
PRINT 'Preparing to run Update Stats on Table: ' + @TableName + ' Index: ' + @IndexName

-- Note time Update Stats started
SELECT @Step = @Step + 1
INSERT tblIndexMaint(TableName, IndexName, MaintDate, MaintStep)
VALUES( @TableName, @IndexName, GETDATE(), CONVERT(VarChar, @Step) + '-Start Stats')

SELECT @ExecStr = 'UPDATE STATISTICS [' + @TableName + '] [' + @IndexName + '] WITH SAMPLE ' + CONVERT(VarChar, @StatPercent) + ' PERCENT'
PRINT 'Executing ' + @ExecStr
IF @TestMode = 0
BEGIN
EXEC (@ExecStr)
END
EXEC DBA_CheckMaintError 'US', @TableName, @IndexName, @@ERROR, @Step OUTPUT

PRINT 'Finished running Update Stats on Table: ' + @TableName + ' Index: ' + @IndexName

-- Note time Update Stats finished
SELECT @LastStatUpdate = GETDATE()
SELECT @Step = @Step + 1
INSERT tblIndexMaint(TableName, IndexName, MaintDate, MaintStep)
VALUES( @TableName, @IndexName, @LastStatUpdate, CONVERT(VarChar, @Step) + '-End Stats')

IF @IndID IN (0,1) -- If Index ID is 0 or 1, then all non Index Stats should be updated
BEGIN
EXEC DBA_UpdateNonIndexStatsByTable @TableName, @StatPercent, @Step OUTPUT, @TestMode
END

END

-- Check Log isn't full -- if close try to truncate -- if not successful, then stop maint
SELECT @CheckLog = 0
EXEC DBA_CheckFixLog 0, @CheckLog OUTPUT
IF @CheckLog <> 0
BEGIN
-- Force Error
EXEC DBA_CheckMaintError 'Log Full', 'Stopping', 'Nightly Maintenance', @CheckLog, @Step OUTPUT
INSERT tblIndexMaint(TableName, IndexName, MaintDate, MaintStep)
VALUES( 'All', 'All', GETDATE(), 'Stopped Maintenance because Log maybe Full')
GOTO FinishMaint
END
-- get the next index to polish
NextIndex:
fetch next from frag_cursor
into @Tablename,@Indexname,@Countrows,@StatPercent,@IndId,@FragPct, @IsClustered

END

FinishMaint:
close frag_cursor
deallocate frag_cursor
drop table #DefragList

INSERT tblIndexMaint(TableName, IndexName, MaintDate, MaintStep)
VALUES( 'All', 'All', GETDATE(), 'End Table Maintenance')

/*
-- Update Stats of non Indexed fields not in tblTableMaint
EXEC DBA_UpdateNonIndexStats @Step OUTPUT, @TestMode

INSERT tblIndexMaint(TableName, IndexName, MaintDate, MaintStep)
VALUES( 'All', 'All', GETDATE(), 'Stop of NonIndex Stats')
*/

-- Disable this process/job since it is done with the tables. There is another
-- Process that will run at midnight to re-enable it.
EXEC DBA_CheckJobState 'Nightly Maintenance', @JobState OUTPUT, @JobEnabled OUTPUT
EXEC DBA_CheckMaintError 'Job', 'Check State', 'Nightly Maintenance', @@ERROR, @Step OUTPUT

EXEC MSDB..sp_update_job @job_name = 'Nightly Maintenance', @enabled= 0
EXEC DBA_CheckMaintError 'Job', 'Disable', 'Nightly Maintenance', @@ERROR, @Step OUTPUT

-- Disable the job that watches this proces
EXEC DBA_CheckJobState 'Watch Nightly Maintenance', @JobState OUTPUT, @JobEnabled OUTPUT
EXEC DBA_CheckMaintError 'Job', 'Check State', 'Watch Nightly Maintenance', @@ERROR, @Step OUTPUT

EXEC MSDB..sp_update_job @job_name = 'Watch Nightly Maintenance', @enabled= 0
EXEC DBA_CheckMaintError 'Job', 'Disable', 'Watch Nightly Maintenance', @@ERROR, @Step OUTPUT

goto EndProc

Handle_Error:
select @Errormsg = 'ERROR ' + convert(varchar,@errorcode) + ' occured. Skipping index'
INSERT tblIndexMaint(TableName, IndexName, MaintDate, MaintStep)
VALUES( @TableName, @IndexName, GETDATE(), @Errormsg)
goto NextIndex

EndProc:
PRINT 'Finished Nightly Maintenance'

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-21 : 14:07:01

I copied and pasted it to my SSMS query window. Entire code parses correctly in SQL 2005 and SQL 2008.

Ditto for lines 1 to 144.

Lines 1 to 154 selected on its own does not parse because of a "GOTO EndProc" statement.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-04-21 : 14:26:58

if you uncomment this
I.IndID
FROM sysObjects O
INNER JOIN sysIndexes I
ON O.ID = I.ID
LEFT JOIN tblTableMaint T
ON T.Tablename = O.Name AND
T.IndexName = I.Name
WHERE O.xType = 'U' AND
I.Rows > 100 AND
I.IndID <> 255 AND
T.TableName IS NULL
ORDER BY
I.Rows DESC
END gives you an error near select (end without beginning)

and I'm thinking there may be some more like it. There's no way that this ends up in anything less than a total stupiphany!

Jim

Everyday I learn something that somebody else already knew

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-04-21 : 14:41:03

I may have miscounted, but I think there are 24 BEGINs and only 1 END!

Jim

Everyday I learn something that somebody else already knew

Clothahump
Starting Member

18 Posts

Posted - 2011-04-22 : 11:35:12

Jim, you miscounted. :-)

Sunita, I'm now going to bang my head on my desk. :-(

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2011-04-22 : 17:21:43

Two things:

1) What is the compatibility level on the database where you are executing this code? If it is not 90 or above, you will get this kind of error because of the DMV.

2) What is the Edition and Version of SQL Server where you are executing this? Depending on that, the DB_ID() function will not work inside the DMV call. Try declaring a variable to hold the database ID and use the variable instead.

Jeff

Clothahump
Starting Member

18 Posts

Posted - 2011-04-26 : 13:57:26

Jeff: both servers running SQL 2005 SP3, CL=90. I'll try the variable later this afternoon.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources