# character causing crash - 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)

# character causing crash

Author

Topic

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 2007-01-08 : 23:44:50

im doign this Select @sql = Coalesce(@sql, '''') + ''
INSERT INTO #fraglist
EXEC (''''DBCC SHOWCONTIG (''+so.name+'') WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS'''')
''
--USE UKbank select*
FROM sysobjects so
JOIN sysindexes si
ON so.id = si.id
WHERE so.type ='U'
AND si.indid < 2
AND si.rows > 0

and it bumping into a table with # in its name and returning this error:

Incorrect syntax near '#'.

is there a way around this

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-01-09 : 00:52:03

That's because you can't use EXEC inside a select statement. Use below statement to create DBCC SHOWCONTIG for each table and dump it to a temp table:

Create Table #temp
(
 seqno int identity(1,1), 
 sqltext varchar(8000)
)

Insert #temp
Select 'DBCC SHOWCONTIG ('+so.name+') WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS' from 
sysobjects so
JOIN sysindexes si
ON so.id = si.id
WHERE so.type ='U'
AND si.indid < 2
AND si.rows > 0

And then loop on table records and execute each statement within the loop:

While @i < @maxrows
begin
   Select @sql = sqltext from #temp where seqno = @i

   INSERT INTO #fraglist 
   Exec(@sql)

   set @i = @i + 1
end

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 2007-01-09 : 00:59:38

no the exec works fine it does work the way im doing it...

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 2007-01-09 : 01:00:00

its just not treating special characters as normal char

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 2007-01-09 : 01:13:58

if you have time see how ive done this code it works just gets stuck on one small thing i mensioned above:

CREATE TABLE #fraglist (
ObjectName CHAR (255),
ObjectId INT,
IndexName CHAR (255),
IndexId INT,
Lvl INT,
CountPages INT,
CountRows INT,
MinRecSize INT,
MaxRecSize INT,
AvgRecSize INT,
ForRecCount INT,
Extents INT,
ExtentSwitches INT,
AvgFreeBytes INT,
AvgPageDensity INT,
ScanDensity DECIMAL,
BestCount INT,
ActualCount INT,
LogicalFrag DECIMAL,
ExtentFrag DECIMAL,
)

CREATE TABLE #fraglist1 (
ObjectName CHAR (255),
ObjectId INT,
IndexName CHAR (255),
IndexId INT,
Lvl INT,
CountPages INT,
CountRows INT,
MinRecSize INT,
MaxRecSize INT,
AvgRecSize INT,
ForRecCount INT,
Extents INT,
ExtentSwitches INT,
AvgFreeBytes INT,
AvgPageDensity INT,
ScanDensity DECIMAL,
BestCount INT,
ActualCount INT,
LogicalFrag DECIMAL,
ExtentFrag DECIMAL,
)
CREATE TABLE #statstable (
DatabaseName VARCHAR(40),
ObjectName CHAR (255),
ObjectId INT,
IndexName CHAR (255),
IndexId INT,
ScanDensity DECIMAL,
NewScanDensity DECIMAL,
LogicalFrag DECIMAL,
NewLogicalFrag DECIMAL,
CountPages INT
)

exec sp_msforeachdb'

DECLARE @sql nvarchar(4000)
if (''?'') <> ''tempdb'' and (''?'') <> ''msdb'' and (''?'') <> ''model'' and (''?'') <> ''master''
BEGIN
USE ?

Select @sql = Coalesce(@sql, '''') + ''
INSERT INTO #fraglist
EXEC (''''DBCC SHOWCONTIG (''+so.name+'') WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS'''')
''
--USE UKbank select*
FROM sysobjects so
JOIN sysindexes si
ON so.id = si.id
WHERE so.type =''U''
AND si.indid < 2
AND si.rows > 0

exec sp_executesql @sql

insert into #statstable
select DatabaseName=''?'',ObjectName,ObjectId,IndexName,IndexID,ScanDensity,0,LogicalFrag,0,CountPages
from #fraglist
where scandensity < 90 --and CountPages > 1000

set @sql =''''

Select @sql = Coalesce(@sql, '''') + ''DBCC DBREINDEX(''+RTRIM(#fraglist.ObjectName)+'')WITH NO_INFOMSGS;''
from #fraglist,sysobjects where scandensity < 90 and sysobjects.name = #fraglist.ObjectName and CountPages > 1000
exec sp_executesql @sql

set @sql=''''

Select @sql = Coalesce(@sql, '''') + ''
INSERT INTO #fraglist1
EXEC (''''DBCC SHOWCONTIG (''+so.name+'') WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS'''')
''
FROM sysobjects so
JOIN sysindexes si
ON so.id = si.id
WHERE so.type =''U''
AND si.indid < 2
AND si.rows > 0

exec sp_executesql @sql

END
'

update #statstable
set NewScanDensity = (select TOP 1 fl1.ScanDensity
from #fraglist1 fl1
where fl1.ObjectId = #statstable.ObjectId
and fl1.IndexId = #statstable.IndexId)

update #statstable
set NewLogicalFrag = (select TOP 1 fl1.LogicalFrag
from #fraglist1 fl1
where fl1.ObjectId = #statstable.ObjectId
and fl1.IndexId = #statstable.IndexId)

select * from #statstable

drop table #fraglist
drop table #fraglist1
drop table #statstable

Kristen
Test

22859 Posts

Posted - 2007-01-09 : 04:10:18

I expect your #TempTable is out of scope in the EXEC

Instead of

Select @sql = Coalesce(@sql, '''') + ''
INSERT INTO #fraglist
EXEC (''''DBCC SHOWCONTIG (''+so.name+'') WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS'''')
''

either use a permanent table, or you may need to use a Cursor

This is what we do: (change the name of the Temp table etc. as appropriate for your code; you may also have to change the system tables etc. if you are using SQL 2005):


-- Declare cursor
DECLARE CUR_TABLES CURSOR FOR
	SELECT	so.id, so.[name], si.[name]
	FROM	sysobjects so
		JOIN sysindexes si
			 ON so.id = si.id
	WHERE	    so.type ='U'
		AND si.indid < 2
		AND si.rows > 0

-- Open the cursor
OPEN CUR_TABLES

-- Loop through all the tables in the database
FETCH NEXT
FROM CUR_TABLES
INTO @tableid, @so_Name, @si_Name

WHILE @@FETCH_STATUS = 0
BEGIN
	-- Do the showcontig of all indexes of the table
	SELECT	@tableidchar = CONVERT(varchar(20), @tableid),
		@strSQL = 'DBCC SHOWCONTIG (' + @tableidchar + ') WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS'

	INSERT INTO #FRAGLIST 
	EXEC (@strSQL)

	FETCH NEXT
	FROM	CUR_TABLES
	INTO	@tableid, @so_Name, @si_Name
END

-- Close and deallocate the cursor
CLOSE CUR_TABLES
DEALLOCATE CUR_TABLES

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources