Problem using EXEC() to run DBCC DBREINDEX

23 Jun 2006 6:06 PM

nosurfdj

I am trying to run DBCC DBREINDEX using EXEC(), code is below.
Based upon the error message at the bottom, the @currenttable variable
receives the value 1 but when @currenttable is referenece in the DBCC
statement, the value isn't there. Can anyone tell me what I'm doing wrong?

declare @sqltest varchar(40), @currenttable int
set @currenttable = (select table_id from Table_Space where table_id = 1)
set @sqltest = 'DBCC DBREINDEX(''@currenttable'','''',75)'
print @currenttable
print @sqltest
EXEC(@sqltest)

Below is the message I get:
1
DBCC DBREINDEX('@currenttable','',75)
Server: Msg 2501, Level 16, State 1, Line 1
Could not find a table or object named '@currenttable'. Check sysobjects.

23 Jun 2006 6:49 PM

Arnie Rowland

Quote problems around ''@currenttable''.

Try:

'DBCC DBREINDEX(' + @currenttable + ','''',75)'

--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."

*Yet Another Certification Exam

Show quote

"nosurfdj" <nosur***@discussions.microsoft.com> wrote in message news:2406FBD3-FD2A-4F69-8A8E-F446DC1473BF@microsoft.com...
>I am trying to run DBCC DBREINDEX using EXEC(), code is below.
> Based upon the error message at the bottom, the @currenttable variable
> receives the value 1 but when @currenttable is referenece in the DBCC
> statement, the value isn't there. Can anyone tell me what I'm doing wrong?
>
> declare @sqltest varchar(40), @currenttable int
> set @currenttable = (select table_id from Table_Space where table_id = 1)
> set @sqltest = 'DBCC DBREINDEX(''@currenttable'','''',75)'
> print @currenttable
> print @sqltest
> EXEC(@sqltest)
>
> Below is the message I get:
> 1
> DBCC DBREINDEX('@currenttable','',75)
> Server: Msg 2501, Level 16, State 1, Line 1
> Could not find a table or object named '@currenttable'. Check sysobjects.
>

23 Jun 2006 6:51 PM

Alejandro Mesa

nosurfdj,

DBCC DBREINDEX expects a table name and there is not table named
'@currenttable'.

declare @sqltest varchar(40), @currenttable int
declare @tn sysname

set @tn = (select table_name from Table_Space where table_id = 1)
set @sqltest = 'DBCC DBREINDEX(''' + @tn + ''','''',75)'

print @currenttable
print @sqltest

EXEC(@sqltest)
go

AMB

Show quote

"nosurfdj" wrote:

> I am trying to run DBCC DBREINDEX using EXEC(), code is below.
> Based upon the error message at the bottom, the @currenttable variable
> receives the value 1 but when @currenttable is referenece in the DBCC
> statement, the value isn't there. Can anyone tell me what I'm doing wrong?
>
> declare @sqltest varchar(40), @currenttable int
> set @currenttable = (select table_id from Table_Space where table_id = 1)
> set @sqltest = 'DBCC DBREINDEX(''@currenttable'','''',75)'
> print @currenttable
> print @sqltest
> EXEC(@sqltest)
>
> Below is the message I get:
> 1
> DBCC DBREINDEX('@currenttable','',75)
> Server: Msg 2501, Level 16, State 1, Line 1
> Could not find a table or object named '@currenttable'. Check sysobjects.
>

23 Jun 2006 6:57 PM

nosurfdj

I knew it was going to be something simple.
Thanks for your help-that did it.

Show quote

"Alejandro Mesa" wrote:

> nosurfdj,
>
> DBCC DBREINDEX expects a table name and there is not table named
> '@currenttable'.
>
> declare @sqltest varchar(40), @currenttable int
> declare @tn sysname
>
> set @tn = (select table_name from Table_Space where table_id = 1)
> set @sqltest = 'DBCC DBREINDEX(''' + @tn + ''','''',75)'
>
> print @currenttable
> print @sqltest
>
> EXEC(@sqltest)
> go
>
>
> AMB
>
> "nosurfdj" wrote:
>
> > I am trying to run DBCC DBREINDEX using EXEC(), code is below.
> > Based upon the error message at the bottom, the @currenttable variable
> > receives the value 1 but when @currenttable is referenece in the DBCC
> > statement, the value isn't there. Can anyone tell me what I'm doing wrong?
> >
> > declare @sqltest varchar(40), @currenttable int
> > set @currenttable = (select table_id from Table_Space where table_id = 1)
> > set @sqltest = 'DBCC DBREINDEX(''@currenttable'','''',75)'
> > print @currenttable
> > print @sqltest
> > EXEC(@sqltest)
> >
> > Below is the message I get:
> > 1
> > DBCC DBREINDEX('@currenttable','',75)
> > Server: Msg 2501, Level 16, State 1, Line 1
> > Could not find a table or object named '@currenttable'. Check sysobjects.
> >