asked on

SQL SERVER 2008 R2 CURSOR ERROR

Hi,

Every body , I'm fighting against an script in sql server 2008. The script is for a REBUILD, and will be included in a sql job. Trying to include the parameter WITH (SORT_IN_TEMPDB = ON) but I get an error. It might be because of concatenate incorrectly. I would thank you your help. Three hours and not advance.

The section of code:

SELECT
db_name(database_id) database_name, s.name schema_name, t.name table_name, i.name index_name
, dt.avg_fragmentation_in_percent, dt.avg_page_space_used_in_percent, page_count, record_count, fragment_count
, case
when ( avg_page_space_used_in_percent < ' + convert(char(3),@min_fill_rebuild) +
' or avg_fragmentation_in_percent > ' + convert(char(3),@min_frag_rebuild) +
') then ''ALTER INDEX '' + QUOTENAME(i.name)+ '' ON '+ QUOTENAME(@database_name) + '.'' + QUOTENAME(s.name) +''.''+ QUOTENAME(t.name) +'' REBUILD '' + '' WITH

(SORT_IN_TEMPDB = ON) ''
else ''ALTER INDEX '' + QUOTENAME(i.name)+ '' ON '+ QUOTENAME(@database_name) + '.'' + QUOTENAME(s.name) +''.''+ QUOTENAME(t.name) +'' REBUILD '' + '' WITH
(SORT_IN_TEMPDB = ON) ''

The thing is I get an error " Executed as user: NT AUTHORITY\SYSTEM. Warning: Null value is eliminated by an aggregate or other SET operation. [SQLSTATE 01003] (Message 8153) Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon. [SQLSTATE 42000] (Error 319). The step failed."

Any idea? this script without + '' WITH
(SORT_IN_TEMPDB = ON) '' work correctly

HugoHiasl

Null Value is eliminated is normally a pointer that a MIN(), MAX() or other aggregate function was executed on data that contained NULLs...

The other problem could be caused by the last line of the statement:

''ALTER INDEX '' + QUOTENAME(i.name)+ '' ON '+ QUOTENAME(@database_name) + '.'' + QUOTENAME(s.name) +''.''+ QUOTENAME(t.name) +'' REBUILD '' + '' WITH (SORT_IN_TEMPDB = ON) ''

Are you sure what you are doing with single an double quotes?

'ALTER INDEX ' [your_i_name] ' ON [your_db_name] .'[your_s_name]'.'[your_t_name]' REBUILD '' WITH (SORT_IN_TEMPDB = ON)'

This looks strange. I think you used "QUOTENAME" which quoted the table and schema name for you and added accidentially additional apostrophs to the quotes.

HugoHiasl

You are also missing the

END

for the CASE WHEN.... END

And this is what I assume that you wanted to do with your concatenation (untested)

''ALTER INDEX ' + QUOTENAME(i.name)+ ' ON '+ QUOTENAME(@database_name) + '.' + QUOTENAME(s.name) +'.'+ QUOTENAME(t.name) +' REBUILD WITH (SORT_IN_TEMPDB = ON) ''
else ''ALTER INDEX ' + QUOTENAME(i.name)+ ' ON '+ QUOTENAME(@database_name) + '.' + QUOTENAME(s.name) +'.'+ QUOTENAME(t.name) +' REBUILD WITH (SORT_IN_TEMPDB = ON) ''

projectja

ASKER

Hi,

atached the image showing single quotes, I am not sure if it is right

Hugo,

yes I have END seccion

)
SELECT
db_name(database_id) database_name, s.name schema_name, t.name table_name, i.name index_name
, dt.avg_fragmentation_in_percent, dt.avg_page_space_used_in_percent, page_count, record_count, fragment_count
, case
when ( avg_page_space_used_in_percent < ' + convert(char(3),@min_fill_rebuild) +
' or avg_fragmentation_in_percent > ' + convert(char(3),@min_frag_rebuild) +
') then ''ALTER INDEX '' + QUOTENAME(i.name)+ '' ON '+ QUOTENAME(@database_name) + '.'' + QUOTENAME(s.name) +''.''+ QUOTENAME(t.name) + '' REBUILD WITH (SORT_IN_TEMPDB = ON) ''
else ''ALTER INDEX '' + QUOTENAME(i.name)+ '' ON '+ QUOTENAME(@database_name) + '.'' + QUOTENAME(s.name) +''.''+ QUOTENAME(t.name) + '' REBUILD WITH (SORT_IN_TEMPDB = ON) ''
end SENTENCIA_RECOMENDADA
EXCHANGE.png

ASKER CERTIFIED SOLUTION

HugoHiasl

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

projectja

ASKER

Thanks guy, I solved the problem with your indication