Solved

SQL SERVER 2008 R2 CURSOR ERROR

Posted on 2013-01-16
5
878 Views
Last Modified: 2013-02-10
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
0
Comment
Question by:projectja
  • 3
  • 2
5 Comments
 
LVL 12

Expert Comment

by:HugoHiasl
ID: 38785108
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.
0
 
LVL 12

Expert Comment

by:HugoHiasl
ID: 38785133
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) ''
0
 

Author Comment

by:projectja
ID: 38785197
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
0
 
LVL 12

Accepted Solution

by:
HugoHiasl earned 500 total points
ID: 38785238
Why are you using duplicate single quotes?

A duplicate single quote is used to escape a single quote within a string with out ending the string.

Use single quotes. Only at the start and end of the string it could make sense if you want a string as return value.
0
 

Author Closing Comment

by:projectja
ID: 38873036
Thanks guy,  I solved the problem with your indication
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now