Bug in sp_updatestats

Hi,
In the BOL is says that DBO and symin role can execute sp_updatestats.
However, the database owner gets this error:
Server: Msg 15247, Level 16, State 1, Procedure sp_updatestats, Line 14
User does not have permission to perform this action.
I've found that I can only run it as symin. On further inspection I see
were the code is raising the error:
DECLARE @.dbsid varbinary(85)
SELECT @.dbsid = sid
FROM master.dbo.sysdatabases
WHERE name = db_name()
/*Check the user symin*/
IF NOT is_srvrolemember('symin') = 1 AND suser_sid() <> @.dbsid
BEGIN
RAISERROR(15247,-1,-1)
RETURN (1)
END
So is the BOL wrong or the SP?
Thanks,
BryanI think that's a bug. You can now only run sp_updatestats if you are logged
in as the account that owns the database. Being a member of dbo is not
enough, where I think it should be.
You can replace
AND suser_sid() <> @.dbsid
with
AND IS_MEMBER ('db_owner') = 0
to make the procedure work as expected.
Jacco Schalkwijk
SQL Server MVP
"BDB" <reply@.to.group.com> wrote in message
news:%23qPHC52aFHA.796@.TK2MSFTNGP09.phx.gbl...
> Hi,
> In the BOL is says that DBO and symin role can execute sp_updatestats.
> However, the database owner gets this error:
> Server: Msg 15247, Level 16, State 1, Procedure sp_updatestats, Line 14
> User does not have permission to perform this action.
> I've found that I can only run it as symin. On further inspection I
> see were the code is raising the error:
> DECLARE @.dbsid varbinary(85)
> SELECT @.dbsid = sid
> FROM master.dbo.sysdatabases
> WHERE name = db_name()
> /*Check the user symin*/
> IF NOT is_srvrolemember('symin') = 1 AND suser_sid() <> @.dbsid
> BEGIN
> RAISERROR(15247,-1,-1)
> RETURN (1)
> END
>
> So is the BOL wrong or the SP?
> Thanks,
> Bryan
>|||Correction:
On rereading the BOL article about sp_updatestats is seems to me that the
article and the code of the stored procedure are technically in agreement.
However using the database owner instead of the db_owner role is highly
unusual. I think it is the first time I have seen it used in SQL Server.
Regarding changing sp_updatestats, as I suggest below, and system stored
procedures in general:
Changing system stored procedures is not recommended or supported by
Microsoft, and if something goes wrong you will be on your own.
I'll raise this issue with Microsoft on your behalf though.
Jacco Schalkwijk
SQL Server MVP
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:ejILv%233aFHA.2860@.TK2MSFTNGP10.phx.gbl...
>I think that's a bug. You can now only run sp_updatestats if you are logged
>in as the account that owns the database. Being a member of dbo is not
>enough, where I think it should be.
> You can replace
> AND suser_sid() <> @.dbsid
> with
> AND IS_MEMBER ('db_owner') = 0
> to make the procedure work as expected.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "BDB" <reply@.to.group.com> wrote in message
> news:%23qPHC52aFHA.796@.TK2MSFTNGP09.phx.gbl...
>|||Right, I'll probably just make a second system SP that works as expected and
wait for MS to fix there own bug.
Thanks a lot!
Bryan
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:uxYHKD4aFHA.1040@.TK2MSFTNGP10.phx.gbl...
> Correction:
> On rereading the BOL article about sp_updatestats is seems to me that the
> article and the code of the stored procedure are technically in agreement.
> However using the database owner instead of the db_owner role is highly
> unusual. I think it is the first time I have seen it used in SQL Server.
> Regarding changing sp_updatestats, as I suggest below, and system stored
> procedures in general:
> Changing system stored procedures is not recommended or supported by
> Microsoft, and if something goes wrong you will be on your own.
> I'll raise this issue with Microsoft on your behalf though.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid>
> wrote in message news:ejILv%233aFHA.2860@.TK2MSFTNGP10.phx.gbl...
>

Bug in Query Analyzer

Execute this in Query Analyzer:
SELECT CONVERT(varchar(8),0x0131) as A, 'X' as B
You will get the expected results if you choose "Results in text", but
if you choose "Results in grid" you will get nothing in column A and
'1' in column B.
Is there anyone at MS reading this ?
Is there any other place I should report this bug ?
Razvan Socol
I can report this as a bug. It does appear taht the grid does not return
the correct information, or atleast does not interpret it correctly.
Rand
This posting is provided "as is" with no warranties and confers no rights.
|||This inserts two characters into A: 0x01 and 0x31. 0x31 is, of course, an
ASCII '1'.
Now you know that 0x01 is the character that QA uses to separate columns
into the grid.
So, the grid has two columns defined by the select, but when populating the
columns QA sees three columns of data: NULL, '1', and 'X'. This causes the
final column of data not to have a home.
So, is this a bug? If Microsoft is willing to think so, that is great, but
I would consider this a behavior instead of a bug.
Russell Fields
"Razvan Socol" <rsocol@.fx.ro> wrote in message
news:60f52b8b.0406090141.7a9f61d7@.posting.google.c om...
> Execute this in Query Analyzer:
> SELECT CONVERT(varchar(8),0x0131) as A, 'X' as B
> You will get the expected results if you choose "Results in text", but
> if you choose "Results in grid" you will get nothing in column A and
> '1' in column B.
> Is there anyone at MS reading this ?
> Is there any other place I should report this bug ?
> Razvan Socol
|||I definitely think that it's a bug. I specifically simplified the
query to this form (the situation I encountered was more complex). I
understood all that you explained before I posted, but it's good that
you did explain it, so that other readers understand.
Razvan
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote:
> This inserts two characters into A: 0x01 and 0x31. 0x31 is, of course, an
> ASCII '1'.
> Now you know that 0x01 is the character that QA uses to separate columns
> into the grid.
> So, the grid has two columns defined by the select, but when populating the
> columns QA sees three columns of data: NULL, '1', and 'X'. This causes the
> final column of data not to have a home.
> So, is this a bug? If Microsoft is willing to think so, that is great, but
> I would consider this a behavior instead of a bug.
> Russell Fields

Browser time out: when executing a DTS package

Hello All,
I am am having a strange problem. I am trying to execute a DTS package
from VB .NET (.aspx) page. The DTS package takes 21 minutes to execute.
When the aspx page sends a command to MS SQL server to execute this DTS
package it starts executing. After 8 minutes the .aspx page shows error
"Cannot Find Server" and errors out with a white screen. The
inetresting thing is the DTS package is still being executed in backend
and completes execution successfully.
I have no clue why this is happening because there are other DTS
packages that I am executing through the same .aspx page that takes
about 3 minutes to complete execution and are working absolutely fine.
By the way I have a stored procedure wrapper over every DTS package. So
when i have to execute a DTS package I execute a stored which inturn
executes a DTS package.
Thanks for reading my query. If you have any suggestions please let me
know.
Thank you in advance.The aspx page (or the command object, or whatever you are using to execute
the stored procedure) has a timeout property. When the timeout value is
reached the web page times out.
Can you increase the timeout for that one database call?
Keith Kratochvil
"Al-Pacino" <dipenshahis@.yahoo.com> wrote in message
news:1142435924.265991.289090@.e56g2000cwe.googlegroups.com...
> Hello All,
> I am am having a strange problem. I am trying to execute a DTS package
> from VB .NET (.aspx) page. The DTS package takes 21 minutes to execute.
> When the aspx page sends a command to MS SQL server to execute this DTS
> package it starts executing. After 8 minutes the .aspx page shows error
> "Cannot Find Server" and errors out with a white screen. The
> inetresting thing is the DTS package is still being executed in backend
> and completes execution successfully.
> I have no clue why this is happening because there are other DTS
> packages that I am executing through the same .aspx page that takes
> about 3 minutes to complete execution and are working absolutely fine.
> By the way I have a stored procedure wrapper over every DTS package. So
> when i have to execute a DTS package I execute a stored which inturn
> executes a DTS package.
> Thanks for reading my query. If you have any suggestions please let me
> know.
> Thank you in advance.
>|||Hi Keith,
I tried setting the time out for the command object but it still does
not work right.
It has the exact same behaviour.
Keith Kratochvil wrote:
> The aspx page (or the command object, or whatever you are using to execute
> the stored procedure) has a timeout property. When the timeout value is
> reached the web page times out.
> Can you increase the timeout for that one database call?
> --
> Keith Kratochvil
>
> "Al-Pacino" <dipenshahis@.yahoo.com> wrote in message
> news:1142435924.265991.289090@.e56g2000cwe.googlegroups.com...

Browser Time Out: when executing a DTS package

Hello All,

I am am having a strange problem. I am trying to execute a DTS package
from VB .NET (.aspx) page. The DTS package takes 21 minutes to execute.

When the aspx page sends a command to MS SQL server to execute this DTS

package it starts executing. After 8 minutes the .aspx page shows error

"Cannot Find Server" and errors out with a white screen. The
inetresting thing is the DTS package is still being executed in backend

and completes execution successfully.

I have no clue why this is happening because there are other DTS
packages that I am executing through the same .aspx page that takes
about 3 minutes to complete execution and are working absolutely fine.

By the way I have a stored procedure wrapper over every DTS package. So

when i have to execute a DTS package I execute a stored which inturn
executes a DTS package.

Thanks for reading my query. If you have any suggestions please let me
know.

Thank you in advance.Hi

Have you though about doing this through a job and not waiting?

John

"Al-Pacino" <dipenshahis@.yahoo.com> wrote in message
news:1142436273.071208.313680@.e56g2000cwe.googlegr oups.com...
> Hello All,
> I am am having a strange problem. I am trying to execute a DTS package
> from VB .NET (.aspx) page. The DTS package takes 21 minutes to execute.
> When the aspx page sends a command to MS SQL server to execute this DTS
> package it starts executing. After 8 minutes the .aspx page shows error
> "Cannot Find Server" and errors out with a white screen. The
> inetresting thing is the DTS package is still being executed in backend
> and completes execution successfully.
>
> I have no clue why this is happening because there are other DTS
> packages that I am executing through the same .aspx page that takes
> about 3 minutes to complete execution and are working absolutely fine.
>
> By the way I have a stored procedure wrapper over every DTS package. So
> when i have to execute a DTS package I execute a stored which inturn
> executes a DTS package.
>
> Thanks for reading my query. If you have any suggestions please let me
> know.
>
> Thank you in advance.