|
Home > Archive > MS SQL Server > February 2006 > Sp_createstats 'fullscan' in 2005
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Sp_createstats 'fullscan' in 2005
|
|
|
| When I run the command below on SQL Server 2005 box I get the
following error.
exec sp_CreateStats 'fullscan'
Msg 15600, Level 15, State 1, Procedure sp_createstats, Line 27
An invalid parameter or option was specified for procedure
'sys.sp_createstats'
The same command seems to run fine on SQL Server 2000 box.
Any help on this regard will be greatly appreciated.
| |
| Tibor Karaszi 2006-02-13, 8:23 pm |
| Seems you are executing this incorrectly. The first parameter is @indexonly, the second is @fullscan
and the third is @norecompute.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/
Blog: http:// solidqualitylearning
.com/blogs/tibor/
"shub" <shubtech@gmail.com> wrote in message
news:1139859080.557463.146880@g43g2000cwa.googlegroups.com...
> When I run the command below on SQL Server 2005 box I get the
> following error.
>
> exec sp_CreateStats 'fullscan'
>
> Msg 15600, Level 15, State 1, Procedure sp_createstats, Line 27
> An invalid parameter or option was specified for procedure
> 'sys.sp_createstats'
>
> The same command seems to run fine on SQL Server 2000 box.
>
> Any help on this regard will be greatly appreciated.
>
| |
|
| Thanks for responding.
Aren't all the parameter in SP_CreateStats optional?
Does the order really matter then?
The one other thing is why does it work in SQL 2000 box but complains
when executed on a 2005 box?
Thanks
Sujas
| |
| Tibor Karaszi 2006-02-14, 1:23 pm |
| > Aren't all the parameter in SP_CreateStats optional?
Yes.
> Does the order really matter then?
Absolutely. How would you expect SQL Server to know that you are passing in the value for the
*second* parameter when you pass the string 'fullscan'? When you don't pass parameters by name, SQL
server will take them by order. The first parm is @indexonly. So what you did was to pass the string
'fullscan' into the @indexonly parameter. Seems like SQL server 2005 is better to catch this mistake
than what 2000 was.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/
Blog: http:// solidqualitylearning
.com/blogs/tibor/
"shub" <shubtech@gmail.com> wrote in message
news:1139939800.983795.99250@o13g2000cwo.googlegroups.com...
> Thanks for responding.
> Aren't all the parameter in SP_CreateStats optional?
> Does the order really matter then?
> The one other thing is why does it work in SQL 2000 box but complains
> when executed on a 2005 box?
>
> Thanks
> Sujas
>
| |
|
| Since the passing values could only be
'fullscan','indexonl
y','norecompute' , I figured SQL Server would know
it by the passing values.
Looks like it worked fine in SQL Server 2000.
And it works fine on SQL Server 2005 if I run the statement as
sp_createstats @fullscan = 'fullscan'
Am I mis-understanding something?
| |
| Tibor Karaszi 2006-02-15, 3:23 am |
| > Since the passing values could only be
> 'fullscan','indexonl
y','norecompute' , I figured SQL Server would know
> it by the passing values.
> Looks like it worked fine in SQL Server 2000.
If you read the source code for the sp_createstats procedure and you will see what happens. In you
case, as I recall from reading the source code yesterday, it didn't do a fullscan since you passed
the string fullscan into another parameter.
> And it works fine on SQL Server 2005 if I run the statement as
> sp_createstats @fullscan = 'fullscan'
Yes, because you are using the procedure in the correct way.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/
Blog: http:// solidqualitylearning
.com/blogs/tibor/
"shub" <shubtech@gmail.com> wrote in message
news:1139951024.903516.324070@g43g2000cwa.googlegroups.com...
> Since the passing values could only be
> 'fullscan','indexonl
y','norecompute' , I figured SQL Server would know
> it by the passing values.
> Looks like it worked fine in SQL Server 2000.
> And it works fine on SQL Server 2005 if I run the statement as
> sp_createstats @fullscan = 'fullscan'
>
> Am I mis-understanding something?
>
| |
|
| Thanks Tibor you are absolutely right. Looks like in 2005 they have
better error handling than we had in 2000 and hence it was not blowing
up.
|
|
|
|
|