Hi,
I am someone new to SQL Server in general. I am using the Trial Version of
SQL Server 2005. I want to create a stored procedure to create a table. The
table name would be the only parameter of the stored procedure. I was
thinking of something like this (I dont know if even the syntax is parcially
correct, sorry first time with Stored Procedures):
CREATE PROCEDURE CrearTabla
(
@.NyA nvarchar(100)
)
AS
BEGIN
CREATE TABLE dbo.@.NyA
(
Table's Columns
)
END
But SQL Server says that there is an error "near" @.NyA in the line with the
CREATE TABLE.
Any thoughts?
Thanks"Cargula" <Cargula@.discussions.microsoft.com> wrote in message
news:99F1F504-8C88-47BD-A0AA-A593DBA2B370@.microsoft.com...
> I am someone new to SQL Server in general. I am using the Trial Version of
> SQL Server 2005. I want to create a stored procedure to create a table.
> The
Why? There is probably a better way to do what you're trying to do.
Can you let us know what your requirements are?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--|||In your exmaple you would create a table with the name @.Nya which is
not possible beacause @. is not allowed as part of an identifier. You
have to create a string which you can execute:
See below for an example
CREATE PROCEDURE CrearTabla
(
@.NyA nvarchar(100)
)
AS
BEGIN
DECLARE @.cmd as varchar (255)
SET @.cmd = 'CREATE TABLE dbo.'+ @.NyA + '
(
Table's Columns
) '
Exec @.cmd
END|||Well, I have an application that erases all the data in certain columns of a
table when the users decides. I have alredy done this. My problem is that I
wish to backup the table (the whole table, it is small, so size isnt a
problem) before the app deletes that data. I was thinking of using stored
procedures to do this, but if there is another (hopefully more efficient) wa
y
to do this, I would be extremely happy to hear it...
Thanks a lot
"Adam Machanic" wrote:
> "Cargula" <Cargula@.discussions.microsoft.com> wrote in message
> news:99F1F504-8C88-47BD-A0AA-A593DBA2B370@.microsoft.com...
> Why? There is probably a better way to do what you're trying to do.
> Can you let us know what your requirements are?
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
>|||"Cargula" <Cargula@.discussions.microsoft.com> wrote in message
news:2B8CE28B-3D8B-4214-B482-949CF7448CAC@.microsoft.com...
> Well, I have an application that erases all the data in certain columns of
> a
> table when the users decides. I have alredy done this. My problem is that
> I
> wish to backup the table (the whole table, it is small, so size isnt a
> problem) before the app deletes that data. I was thinking of using stored
> procedures to do this, but if there is another (hopefully more efficient)
> way
> to do this, I would be extremely happy to hear it...
The usual way this is handled is by creating an after trigger, for
delete, that inserts all of the rows from the 'deleted' table into a backup
table that's already been created -- this way, you'll keep a running log of
what's been deleted, in a single place. Much more manageable than creating
a new table every single time.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
Showing posts with label version. Show all posts
Showing posts with label version. Show all posts
Tuesday, March 27, 2012
Create a table using a Stored Procedure
Hi,
I am someone new to SQL Server in general. I am using the Trial Version of
SQL Server 2005. I want to create a stored procedure to create a table. The
table name would be the only parameter of the stored procedure. I was
thinking of something like this (I dont know if even the syntax is parcially
correct, sorry first time with Stored Procedures):
CREATE PROCEDURE CrearTabla
(
@.NyA nvarchar(100)
)
AS
BEGIN
CREATE TABLE dbo.@.NyA
(
Table's Columns
)
END
But SQL Server says that there is an error "near" @.NyA in the line with the
CREATE TABLE.
Any thoughts?
Thanks
"Cargula" <Cargula@.discussions.microsoft.com> wrote in message
news:99F1F504-8C88-47BD-A0AA-A593DBA2B370@.microsoft.com...
> I am someone new to SQL Server in general. I am using the Trial Version of
> SQL Server 2005. I want to create a stored procedure to create a table.
> The
Why? There is probably a better way to do what you're trying to do.
Can you let us know what your requirements are?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
|||In your exmaple you would create a table with the name @.Nya which is
not possible beacause @. is not allowed as part of an identifier. You
have to create a string which you can execute:
See below for an example
CREATE PROCEDURE CrearTabla
(
@.NyA nvarchar(100)
)
AS
BEGIN
DECLARE @.cmd as varchar (255)
SET @.cmd = 'CREATE TABLE dbo.'+ @.NyA + '
(
Table's Columns
) '
Exec @.cmd
END
|||Well, I have an application that erases all the data in certain columns of a
table when the users decides. I have alredy done this. My problem is that I
wish to backup the table (the whole table, it is small, so size isnt a
problem) before the app deletes that data. I was thinking of using stored
procedures to do this, but if there is another (hopefully more efficient) way
to do this, I would be extremely happy to hear it...
Thanks a lot
"Adam Machanic" wrote:
> "Cargula" <Cargula@.discussions.microsoft.com> wrote in message
> news:99F1F504-8C88-47BD-A0AA-A593DBA2B370@.microsoft.com...
> Why? There is probably a better way to do what you're trying to do.
> Can you let us know what your requirements are?
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
>
|||"Cargula" <Cargula@.discussions.microsoft.com> wrote in message
news:2B8CE28B-3D8B-4214-B482-949CF7448CAC@.microsoft.com...
> Well, I have an application that erases all the data in certain columns of
> a
> table when the users decides. I have alredy done this. My problem is that
> I
> wish to backup the table (the whole table, it is small, so size isnt a
> problem) before the app deletes that data. I was thinking of using stored
> procedures to do this, but if there is another (hopefully more efficient)
> way
> to do this, I would be extremely happy to hear it...
The usual way this is handled is by creating an after trigger, for
delete, that inserts all of the rows from the 'deleted' table into a backup
table that's already been created -- this way, you'll keep a running log of
what's been deleted, in a single place. Much more manageable than creating
a new table every single time.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
I am someone new to SQL Server in general. I am using the Trial Version of
SQL Server 2005. I want to create a stored procedure to create a table. The
table name would be the only parameter of the stored procedure. I was
thinking of something like this (I dont know if even the syntax is parcially
correct, sorry first time with Stored Procedures):
CREATE PROCEDURE CrearTabla
(
@.NyA nvarchar(100)
)
AS
BEGIN
CREATE TABLE dbo.@.NyA
(
Table's Columns
)
END
But SQL Server says that there is an error "near" @.NyA in the line with the
CREATE TABLE.
Any thoughts?
Thanks
"Cargula" <Cargula@.discussions.microsoft.com> wrote in message
news:99F1F504-8C88-47BD-A0AA-A593DBA2B370@.microsoft.com...
> I am someone new to SQL Server in general. I am using the Trial Version of
> SQL Server 2005. I want to create a stored procedure to create a table.
> The
Why? There is probably a better way to do what you're trying to do.
Can you let us know what your requirements are?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
|||In your exmaple you would create a table with the name @.Nya which is
not possible beacause @. is not allowed as part of an identifier. You
have to create a string which you can execute:
See below for an example
CREATE PROCEDURE CrearTabla
(
@.NyA nvarchar(100)
)
AS
BEGIN
DECLARE @.cmd as varchar (255)
SET @.cmd = 'CREATE TABLE dbo.'+ @.NyA + '
(
Table's Columns
) '
Exec @.cmd
END
|||Well, I have an application that erases all the data in certain columns of a
table when the users decides. I have alredy done this. My problem is that I
wish to backup the table (the whole table, it is small, so size isnt a
problem) before the app deletes that data. I was thinking of using stored
procedures to do this, but if there is another (hopefully more efficient) way
to do this, I would be extremely happy to hear it...
Thanks a lot
"Adam Machanic" wrote:
> "Cargula" <Cargula@.discussions.microsoft.com> wrote in message
> news:99F1F504-8C88-47BD-A0AA-A593DBA2B370@.microsoft.com...
> Why? There is probably a better way to do what you're trying to do.
> Can you let us know what your requirements are?
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
>
|||"Cargula" <Cargula@.discussions.microsoft.com> wrote in message
news:2B8CE28B-3D8B-4214-B482-949CF7448CAC@.microsoft.com...
> Well, I have an application that erases all the data in certain columns of
> a
> table when the users decides. I have alredy done this. My problem is that
> I
> wish to backup the table (the whole table, it is small, so size isnt a
> problem) before the app deletes that data. I was thinking of using stored
> procedures to do this, but if there is another (hopefully more efficient)
> way
> to do this, I would be extremely happy to hear it...
The usual way this is handled is by creating an after trigger, for
delete, that inserts all of the rows from the 'deleted' table into a backup
table that's already been created -- this way, you'll keep a running log of
what's been deleted, in a single place. Much more manageable than creating
a new table every single time.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
Create a table using a Stored Procedure
Hi,
I am someone new to SQL Server in general. I am using the Trial Version of
SQL Server 2005. I want to create a stored procedure to create a table. The
table name would be the only parameter of the stored procedure. I was
thinking of something like this (I dont know if even the syntax is parcially
correct, sorry first time with Stored Procedures):
CREATE PROCEDURE CrearTabla
(
@.NyA nvarchar(100)
)
AS
BEGIN
CREATE TABLE dbo.@.NyA
(
Table's Columns
)
END
But SQL Server says that there is an error "near" @.NyA in the line with the
CREATE TABLE.
Any thoughts?
Thanks"Cargula" <Cargula@.discussions.microsoft.com> wrote in message
news:99F1F504-8C88-47BD-A0AA-A593DBA2B370@.microsoft.com...
> I am someone new to SQL Server in general. I am using the Trial Version of
> SQL Server 2005. I want to create a stored procedure to create a table.
> The
Why? There is probably a better way to do what you're trying to do.
Can you let us know what your requirements are?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--|||In your exmaple you would create a table with the name @.Nya which is
not possible beacause @. is not allowed as part of an identifier. You
have to create a string which you can execute:
See below for an example
CREATE PROCEDURE CrearTabla
(
@.NyA nvarchar(100)
)
AS
BEGIN
DECLARE @.cmd as varchar (255)
SET @.cmd = 'CREATE TABLE dbo.'+ @.NyA + '
(
Table's Columns
) '
Exec @.cmd
END|||Well, I have an application that erases all the data in certain columns of a
table when the users decides. I have alredy done this. My problem is that I
wish to backup the table (the whole table, it is small, so size isnt a
problem) before the app deletes that data. I was thinking of using stored
procedures to do this, but if there is another (hopefully more efficient) way
to do this, I would be extremely happy to hear it...
Thanks a lot
"Adam Machanic" wrote:
> "Cargula" <Cargula@.discussions.microsoft.com> wrote in message
> news:99F1F504-8C88-47BD-A0AA-A593DBA2B370@.microsoft.com...
> >
> > I am someone new to SQL Server in general. I am using the Trial Version of
> > SQL Server 2005. I want to create a stored procedure to create a table.
> > The
> Why? There is probably a better way to do what you're trying to do.
> Can you let us know what your requirements are?
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
>|||"Cargula" <Cargula@.discussions.microsoft.com> wrote in message
news:2B8CE28B-3D8B-4214-B482-949CF7448CAC@.microsoft.com...
> Well, I have an application that erases all the data in certain columns of
> a
> table when the users decides. I have alredy done this. My problem is that
> I
> wish to backup the table (the whole table, it is small, so size isnt a
> problem) before the app deletes that data. I was thinking of using stored
> procedures to do this, but if there is another (hopefully more efficient)
> way
> to do this, I would be extremely happy to hear it...
The usual way this is handled is by creating an after trigger, for
delete, that inserts all of the rows from the 'deleted' table into a backup
table that's already been created -- this way, you'll keep a running log of
what's been deleted, in a single place. Much more manageable than creating
a new table every single time.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--sql
I am someone new to SQL Server in general. I am using the Trial Version of
SQL Server 2005. I want to create a stored procedure to create a table. The
table name would be the only parameter of the stored procedure. I was
thinking of something like this (I dont know if even the syntax is parcially
correct, sorry first time with Stored Procedures):
CREATE PROCEDURE CrearTabla
(
@.NyA nvarchar(100)
)
AS
BEGIN
CREATE TABLE dbo.@.NyA
(
Table's Columns
)
END
But SQL Server says that there is an error "near" @.NyA in the line with the
CREATE TABLE.
Any thoughts?
Thanks"Cargula" <Cargula@.discussions.microsoft.com> wrote in message
news:99F1F504-8C88-47BD-A0AA-A593DBA2B370@.microsoft.com...
> I am someone new to SQL Server in general. I am using the Trial Version of
> SQL Server 2005. I want to create a stored procedure to create a table.
> The
Why? There is probably a better way to do what you're trying to do.
Can you let us know what your requirements are?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--|||In your exmaple you would create a table with the name @.Nya which is
not possible beacause @. is not allowed as part of an identifier. You
have to create a string which you can execute:
See below for an example
CREATE PROCEDURE CrearTabla
(
@.NyA nvarchar(100)
)
AS
BEGIN
DECLARE @.cmd as varchar (255)
SET @.cmd = 'CREATE TABLE dbo.'+ @.NyA + '
(
Table's Columns
) '
Exec @.cmd
END|||Well, I have an application that erases all the data in certain columns of a
table when the users decides. I have alredy done this. My problem is that I
wish to backup the table (the whole table, it is small, so size isnt a
problem) before the app deletes that data. I was thinking of using stored
procedures to do this, but if there is another (hopefully more efficient) way
to do this, I would be extremely happy to hear it...
Thanks a lot
"Adam Machanic" wrote:
> "Cargula" <Cargula@.discussions.microsoft.com> wrote in message
> news:99F1F504-8C88-47BD-A0AA-A593DBA2B370@.microsoft.com...
> >
> > I am someone new to SQL Server in general. I am using the Trial Version of
> > SQL Server 2005. I want to create a stored procedure to create a table.
> > The
> Why? There is probably a better way to do what you're trying to do.
> Can you let us know what your requirements are?
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
>|||"Cargula" <Cargula@.discussions.microsoft.com> wrote in message
news:2B8CE28B-3D8B-4214-B482-949CF7448CAC@.microsoft.com...
> Well, I have an application that erases all the data in certain columns of
> a
> table when the users decides. I have alredy done this. My problem is that
> I
> wish to backup the table (the whole table, it is small, so size isnt a
> problem) before the app deletes that data. I was thinking of using stored
> procedures to do this, but if there is another (hopefully more efficient)
> way
> to do this, I would be extremely happy to hear it...
The usual way this is handled is by creating an after trigger, for
delete, that inserts all of the rows from the 'deleted' table into a backup
table that's already been created -- this way, you'll keep a running log of
what's been deleted, in a single place. Much more manageable than creating
a new table every single time.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--sql
Thursday, March 22, 2012
Create a modified version of an existing rendering extension
Is there any documentation or sample code to point me in the right direction
on how to modify an existing rendering extension?
I need to add some browser checking to the existing JavaScript that is
generated for the toolbar. I was hoping to modify the existing HTML 4.0
rendering to include some more browser checking to replace some of the
specific IE JavaScript properties.
Any help would be greatly appreciated.
Thanks,This is not possible. All of our renderers have link demands and therefore
could not be loaded by your assemblies.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
".Net Report Dev" <aanargyros@.hotmail.com> wrote in message
news:OwFagExeEHA.3412@.TK2MSFTNGP11.phx.gbl...
> Is there any documentation or sample code to point me in the right
direction
> on how to modify an existing rendering extension?
> I need to add some browser checking to the existing JavaScript that is
> generated for the toolbar. I was hoping to modify the existing HTML 4.0
> rendering to include some more browser checking to replace some of the
> specific IE JavaScript properties.
> Any help would be greatly appreciated.
> Thanks,
>|||Are you saying what I am trying cannot be done or that I cannot modify an
existing rendering extension. According to the books online I should be
able to modify an existing extension. If I cannot modify an existing
extension, is there any way for me to modify the toolbar JavaScript?
"Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
news:unQWaTxeEHA.2916@.TK2MSFTNGP12.phx.gbl...
> This is not possible. All of our renderers have link demands and
therefore
> could not be loaded by your assemblies.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> ".Net Report Dev" <aanargyros@.hotmail.com> wrote in message
> news:OwFagExeEHA.3412@.TK2MSFTNGP11.phx.gbl...
> > Is there any documentation or sample code to point me in the right
> direction
> > on how to modify an existing rendering extension?
> >
> > I need to add some browser checking to the existing JavaScript that is
> > generated for the toolbar. I was hoping to modify the existing HTML 4.0
> > rendering to include some more browser checking to replace some of the
> > specific IE JavaScript properties.
> >
> > Any help would be greatly appreciated.
> >
> > Thanks,
> >
> >
>|||You can not modify the existing renderers. Can you point me to where in the
docs it says this?
We currently have no mechanism for modifying the JavaScript in the toolbar
so I am not sure how you could accomplish what you are trying to do.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
".Net Report Dev" <aanargyros@.hotmail.com> wrote in message
news:#XaMU8xeEHA.1656@.TK2MSFTNGP09.phx.gbl...
> Are you saying what I am trying cannot be done or that I cannot modify an
> existing rendering extension. According to the books online I should be
> able to modify an existing extension. If I cannot modify an existing
> extension, is there any way for me to modify the toolbar JavaScript?
>
> "Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
> news:unQWaTxeEHA.2916@.TK2MSFTNGP12.phx.gbl...
> > This is not possible. All of our renderers have link demands and
> therefore
> > could not be loaded by your assemblies.
> >
> > --
> > -Daniel
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >
> >
> > ".Net Report Dev" <aanargyros@.hotmail.com> wrote in message
> > news:OwFagExeEHA.3412@.TK2MSFTNGP11.phx.gbl...
> > > Is there any documentation or sample code to point me in the right
> > direction
> > > on how to modify an existing rendering extension?
> > >
> > > I need to add some browser checking to the existing JavaScript that is
> > > generated for the toolbar. I was hoping to modify the existing HTML
4.0
> > > rendering to include some more browser checking to replace some of the
> > > specific IE JavaScript properties.
> > >
> > > Any help would be greatly appreciated.
> > >
> > > Thanks,
> > >
> > >
> >
> >
>|||Could you point me at the section in books online that you're referring to?
You cannot extend our existing rendering extensions. You can extend report
server by writing another rendering extension.
No you cannot change/add script to the report viewer or the report manager.
-Lukasz
This posting is provided "AS IS" with no warranties, and confers no rights.
".Net Report Dev" <aanargyros@.hotmail.com> wrote in message
news:%23XaMU8xeEHA.1656@.TK2MSFTNGP09.phx.gbl...
> Are you saying what I am trying cannot be done or that I cannot modify an
> existing rendering extension. According to the books online I should be
> able to modify an existing extension. If I cannot modify an existing
> extension, is there any way for me to modify the toolbar JavaScript?
>
> "Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
> news:unQWaTxeEHA.2916@.TK2MSFTNGP12.phx.gbl...
>> This is not possible. All of our renderers have link demands and
> therefore
>> could not be loaded by your assemblies.
>> --
>> -Daniel
>> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>>
>> ".Net Report Dev" <aanargyros@.hotmail.com> wrote in message
>> news:OwFagExeEHA.3412@.TK2MSFTNGP11.phx.gbl...
>> > Is there any documentation or sample code to point me in the right
>> direction
>> > on how to modify an existing rendering extension?
>> >
>> > I need to add some browser checking to the existing JavaScript that is
>> > generated for the toolbar. I was hoping to modify the existing HTML
>> > 4.0
>> > rendering to include some more browser checking to replace some of the
>> > specific IE JavaScript properties.
>> >
>> > Any help would be greatly appreciated.
>> >
>> > Thanks,
>> >
>> >
>>
>|||Is there any documentation on creating your own rendering extension?
Maybe I can create a custom toolbar within my new rendering extension and
then hide the existing toolbar using the command provided by reporting
services.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_prog_extend_security_87oi.asp
Here is a snippet.
Writing Custom Rendering Extensions
Before you decide to create a custom rendering extension, you should
evaluate simpler alternatives. You can:
a.. Create a modified version of an existing rendering extension.
b.. Customize rendered output by specifying device information settings
for existing extensions.
c.. Add custom formatting and presentation features by combining XSL
Transformations (XSLT) with the output of the XML rendering format.
"Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
news:%23L0zLVyeEHA.2764@.TK2MSFTNGP11.phx.gbl...
> You can not modify the existing renderers. Can you point me to where in
the
> docs it says this?
> We currently have no mechanism for modifying the JavaScript in the toolbar
> so I am not sure how you could accomplish what you are trying to do.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> ".Net Report Dev" <aanargyros@.hotmail.com> wrote in message
> news:#XaMU8xeEHA.1656@.TK2MSFTNGP09.phx.gbl...
> > Are you saying what I am trying cannot be done or that I cannot modify
an
> > existing rendering extension. According to the books online I should be
> > able to modify an existing extension. If I cannot modify an existing
> > extension, is there any way for me to modify the toolbar JavaScript?
> >
> >
> >
> > "Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
> > news:unQWaTxeEHA.2916@.TK2MSFTNGP12.phx.gbl...
> > > This is not possible. All of our renderers have link demands and
> > therefore
> > > could not be loaded by your assemblies.
> > >
> > > --
> > > -Daniel
> > > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> > >
> > >
> > > ".Net Report Dev" <aanargyros@.hotmail.com> wrote in message
> > > news:OwFagExeEHA.3412@.TK2MSFTNGP11.phx.gbl...
> > > > Is there any documentation or sample code to point me in the right
> > > direction
> > > > on how to modify an existing rendering extension?
> > > >
> > > > I need to add some browser checking to the existing JavaScript that
is
> > > > generated for the toolbar. I was hoping to modify the existing HTML
> 4.0
> > > > rendering to include some more browser checking to replace some of
the
> > > > specific IE JavaScript properties.
> > > >
> > > > Any help would be greatly appreciated.
> > > >
> > > > Thanks,
> > > >
> > > >
> > >
> > >
> >
> >
>|||I guess I did not read the documentation correctly. It sounded to me that
you can modify the existing extensions but instead it is saying create
another version of an existing extension with your changes. If that is what
the document means than how is that an alternative to writing a new
extension? Its not like MS will give up the source so that I can just
create my own version.
"Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
news:%23L0zLVyeEHA.2764@.TK2MSFTNGP11.phx.gbl...
> You can not modify the existing renderers. Can you point me to where in
the
> docs it says this?
> We currently have no mechanism for modifying the JavaScript in the toolbar
> so I am not sure how you could accomplish what you are trying to do.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> ".Net Report Dev" <aanargyros@.hotmail.com> wrote in message
> news:#XaMU8xeEHA.1656@.TK2MSFTNGP09.phx.gbl...
> > Are you saying what I am trying cannot be done or that I cannot modify
an
> > existing rendering extension. According to the books online I should be
> > able to modify an existing extension. If I cannot modify an existing
> > extension, is there any way for me to modify the toolbar JavaScript?
> >
> >
> >
> > "Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
> > news:unQWaTxeEHA.2916@.TK2MSFTNGP12.phx.gbl...
> > > This is not possible. All of our renderers have link demands and
> > therefore
> > > could not be loaded by your assemblies.
> > >
> > > --
> > > -Daniel
> > > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> > >
> > >
> > > ".Net Report Dev" <aanargyros@.hotmail.com> wrote in message
> > > news:OwFagExeEHA.3412@.TK2MSFTNGP11.phx.gbl...
> > > > Is there any documentation or sample code to point me in the right
> > > direction
> > > > on how to modify an existing rendering extension?
> > > >
> > > > I need to add some browser checking to the existing JavaScript that
is
> > > > generated for the toolbar. I was hoping to modify the existing HTML
> 4.0
> > > > rendering to include some more browser checking to replace some of
the
> > > > specific IE JavaScript properties.
> > > >
> > > > Any help would be greatly appreciated.
> > > >
> > > > Thanks,
> > > >
> > > >
> > >
> > >
> >
> >
>|||Yes, the documentation seems to be a little misleading. I will forward it
to our doc team to see if they can make it a little more clear.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
".Net Report Dev" <aanargyros@.hotmail.com> wrote in message
news:u#6YtzyeEHA.4092@.TK2MSFTNGP10.phx.gbl...
> Is there any documentation on creating your own rendering extension?
> Maybe I can create a custom toolbar within my new rendering extension and
> then hide the existing toolbar using the command provided by reporting
> services.
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_prog_extend_security_87oi.asp
> Here is a snippet.
> Writing Custom Rendering Extensions
> Before you decide to create a custom rendering extension, you should
> evaluate simpler alternatives. You can:
> a.. Create a modified version of an existing rendering extension.
> b.. Customize rendered output by specifying device information settings
> for existing extensions.
> c.. Add custom formatting and presentation features by combining XSL
> Transformations (XSLT) with the output of the XML rendering format.
>
> "Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
> news:%23L0zLVyeEHA.2764@.TK2MSFTNGP11.phx.gbl...
> > You can not modify the existing renderers. Can you point me to where in
> the
> > docs it says this?
> >
> > We currently have no mechanism for modifying the JavaScript in the
toolbar
> > so I am not sure how you could accomplish what you are trying to do.
> >
> > --
> > -Daniel
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >
> >
> > ".Net Report Dev" <aanargyros@.hotmail.com> wrote in message
> > news:#XaMU8xeEHA.1656@.TK2MSFTNGP09.phx.gbl...
> > > Are you saying what I am trying cannot be done or that I cannot modify
> an
> > > existing rendering extension. According to the books online I should
be
> > > able to modify an existing extension. If I cannot modify an existing
> > > extension, is there any way for me to modify the toolbar JavaScript?
> > >
> > >
> > >
> > > "Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
> > > news:unQWaTxeEHA.2916@.TK2MSFTNGP12.phx.gbl...
> > > > This is not possible. All of our renderers have link demands and
> > > therefore
> > > > could not be loaded by your assemblies.
> > > >
> > > > --
> > > > -Daniel
> > > > This posting is provided "AS IS" with no warranties, and confers no
> > > rights.
> > > >
> > > >
> > > > ".Net Report Dev" <aanargyros@.hotmail.com> wrote in message
> > > > news:OwFagExeEHA.3412@.TK2MSFTNGP11.phx.gbl...
> > > > > Is there any documentation or sample code to point me in the right
> > > > direction
> > > > > on how to modify an existing rendering extension?
> > > > >
> > > > > I need to add some browser checking to the existing JavaScript
that
> is
> > > > > generated for the toolbar. I was hoping to modify the existing
HTML
> > 4.0
> > > > > rendering to include some more browser checking to replace some of
> the
> > > > > specific IE JavaScript properties.
> > > > >
> > > > > Any help would be greatly appreciated.
> > > > >
> > > > > Thanks,
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||Is there any documentation on creating your own rendering extension?
Maybe I can create a custom toolbar within my new rendering extension and
then hide the existing toolbar using the command provided by reporting
services.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_prog_extend_security_87oi.asp
Here is a snippet.
Writing Custom Rendering Extensions
Before you decide to create a custom rendering extension, you should
evaluate simpler alternatives. You can:
a.. Create a modified version of an existing rendering extension.
b.. Customize rendered output by specifying device information settings
for existing extensions.
c.. Add custom formatting and presentation features by combining XSL
Transformations (XSLT) with the output of the XML rendering format.
"Lukasz Pawlowski [MSFT]" <lukaszp@.online.microsoft.com> wrote in message
news:uf6ndVyeEHA.1036@.TK2MSFTNGP10.phx.gbl...
> Could you point me at the section in books online that you're referring
to?
> You cannot extend our existing rendering extensions. You can extend
report
> server by writing another rendering extension.
> No you cannot change/add script to the report viewer or the report
manager.
> -Lukasz
>
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> ".Net Report Dev" <aanargyros@.hotmail.com> wrote in message
> news:%23XaMU8xeEHA.1656@.TK2MSFTNGP09.phx.gbl...
> > Are you saying what I am trying cannot be done or that I cannot modify
an
> > existing rendering extension. According to the books online I should be
> > able to modify an existing extension. If I cannot modify an existing
> > extension, is there any way for me to modify the toolbar JavaScript?
> >
> >
> >
> > "Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
> > news:unQWaTxeEHA.2916@.TK2MSFTNGP12.phx.gbl...
> >> This is not possible. All of our renderers have link demands and
> > therefore
> >> could not be loaded by your assemblies.
> >>
> >> --
> >> -Daniel
> >> This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> >>
> >>
> >> ".Net Report Dev" <aanargyros@.hotmail.com> wrote in message
> >> news:OwFagExeEHA.3412@.TK2MSFTNGP11.phx.gbl...
> >> > Is there any documentation or sample code to point me in the right
> >> direction
> >> > on how to modify an existing rendering extension?
> >> >
> >> > I need to add some browser checking to the existing JavaScript that
is
> >> > generated for the toolbar. I was hoping to modify the existing HTML
> >> > 4.0
> >> > rendering to include some more browser checking to replace some of
the
> >> > specific IE JavaScript properties.
> >> >
> >> > Any help would be greatly appreciated.
> >> >
> >> > Thanks,
> >> >
> >> >
> >>
> >>
> >
> >
>|||Is there any documentation on creating your own rendering extension that I
can use now?
"Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
news:eTRNDCzeEHA.1644@.tk2msftngp13.phx.gbl...
> Yes, the documentation seems to be a little misleading. I will forward it
> to our doc team to see if they can make it a little more clear.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> ".Net Report Dev" <aanargyros@.hotmail.com> wrote in message
> news:u#6YtzyeEHA.4092@.TK2MSFTNGP10.phx.gbl...
> > Is there any documentation on creating your own rendering extension?
> > Maybe I can create a custom toolbar within my new rendering extension
and
> > then hide the existing toolbar using the command provided by reporting
> > services.
> >
> >
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_prog_extend_security_87oi.asp
> >
> > Here is a snippet.
> >
> > Writing Custom Rendering Extensions
> > Before you decide to create a custom rendering extension, you should
> > evaluate simpler alternatives. You can:
> >
> > a.. Create a modified version of an existing rendering extension.
> > b.. Customize rendered output by specifying device information
settings
> > for existing extensions.
> > c.. Add custom formatting and presentation features by combining XSL
> > Transformations (XSLT) with the output of the XML rendering format.
> >
> >
> > "Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
> > news:%23L0zLVyeEHA.2764@.TK2MSFTNGP11.phx.gbl...
> > > You can not modify the existing renderers. Can you point me to where
in
> > the
> > > docs it says this?
> > >
> > > We currently have no mechanism for modifying the JavaScript in the
> toolbar
> > > so I am not sure how you could accomplish what you are trying to do.
> > >
> > > --
> > > -Daniel
> > > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> > >
> > >
> > > ".Net Report Dev" <aanargyros@.hotmail.com> wrote in message
> > > news:#XaMU8xeEHA.1656@.TK2MSFTNGP09.phx.gbl...
> > > > Are you saying what I am trying cannot be done or that I cannot
modify
> > an
> > > > existing rendering extension. According to the books online I
should
> be
> > > > able to modify an existing extension. If I cannot modify an
existing
> > > > extension, is there any way for me to modify the toolbar JavaScript?
> > > >
> > > >
> > > >
> > > > "Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
> > > > news:unQWaTxeEHA.2916@.TK2MSFTNGP12.phx.gbl...
> > > > > This is not possible. All of our renderers have link demands and
> > > > therefore
> > > > > could not be loaded by your assemblies.
> > > > >
> > > > > --
> > > > > -Daniel
> > > > > This posting is provided "AS IS" with no warranties, and confers
no
> > > > rights.
> > > > >
> > > > >
> > > > > ".Net Report Dev" <aanargyros@.hotmail.com> wrote in message
> > > > > news:OwFagExeEHA.3412@.TK2MSFTNGP11.phx.gbl...
> > > > > > Is there any documentation or sample code to point me in the
right
> > > > > direction
> > > > > > on how to modify an existing rendering extension?
> > > > > >
> > > > > > I need to add some browser checking to the existing JavaScript
> that
> > is
> > > > > > generated for the toolbar. I was hoping to modify the existing
> HTML
> > > 4.0
> > > > > > rendering to include some more browser checking to replace some
of
> > the
> > > > > > specific IE JavaScript properties.
> > > > > >
> > > > > > Any help would be greatly appreciated.
> > > > > >
> > > > > > Thanks,
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||So, although the documentation alludes to modifying an existing rendering
extension, what it is actually referring to is creating a version of a
rendering extension that is similar to ones that we already have. That is,
writing a new HTML rendering extension. I must admit that it is misleading
and that writing one of our rendering extensions from scratch is not a
"simpler alternative."
Writing a rendering extension is no simple matter, in fact, it is one of the
most challenging development experiences I have seen at Microsoft. Please
see the following blog post for my take:
http://blogs.msdn.com/bryanke/archive/2004/03/16/90797.aspx
The Reporting Services team, in light of other key deliverables, elected not
to document rendering extensions in version 1 of Reporting Services because
it was considered a low usage task (only a few select partners ever
expressed an interest in developing one and only a handful have ever
successfully done it and that is after spending months with our developers
hands-on).
It is not an end-user feature and is not something for the average or even
advanced developer. If you are interested, we have included documentation
for rendering extensions in our SQL Server 2005 Books Online that was
released with SQL Server 2005 Beta 2 recently. We hope to someday create an
SDK that will explain rendering extensions in more detail, but that is
probably a ways off.
Bryan
--
Bryan Keller
Developer Documentation
SQL Server Reporting Services
A friendly reminder that this posting is provided "AS IS" with no
warranties, and confers no rights.
".Net Report Dev" <aanargyros@.hotmail.com> wrote in message
news:eB9luSzeEHA.1692@.tk2msftngp13.phx.gbl...
> Is there any documentation on creating your own rendering extension?
> Maybe I can create a custom toolbar within my new rendering extension and
> then hide the existing toolbar using the command provided by reporting
> services.
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_prog_extend_security_87oi.asp
> Here is a snippet.
> Writing Custom Rendering Extensions
> Before you decide to create a custom rendering extension, you should
> evaluate simpler alternatives. You can:
> a.. Create a modified version of an existing rendering extension.
> b.. Customize rendered output by specifying device information settings
> for existing extensions.
> c.. Add custom formatting and presentation features by combining XSL
> Transformations (XSLT) with the output of the XML rendering format.
> "Lukasz Pawlowski [MSFT]" <lukaszp@.online.microsoft.com> wrote in message
> news:uf6ndVyeEHA.1036@.TK2MSFTNGP10.phx.gbl...
> > Could you point me at the section in books online that you're referring
> to?
> > You cannot extend our existing rendering extensions. You can extend
> report
> > server by writing another rendering extension.
> >
> > No you cannot change/add script to the report viewer or the report
> manager.
> >
> > -Lukasz
> >
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >
> >
> > ".Net Report Dev" <aanargyros@.hotmail.com> wrote in message
> > news:%23XaMU8xeEHA.1656@.TK2MSFTNGP09.phx.gbl...
> > > Are you saying what I am trying cannot be done or that I cannot modify
> an
> > > existing rendering extension. According to the books online I should
be
> > > able to modify an existing extension. If I cannot modify an existing
> > > extension, is there any way for me to modify the toolbar JavaScript?
> > >
> > >
> > >
> > > "Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
> > > news:unQWaTxeEHA.2916@.TK2MSFTNGP12.phx.gbl...
> > >> This is not possible. All of our renderers have link demands and
> > > therefore
> > >> could not be loaded by your assemblies.
> > >>
> > >> --
> > >> -Daniel
> > >> This posting is provided "AS IS" with no warranties, and confers no
> > > rights.
> > >>
> > >>
> > >> ".Net Report Dev" <aanargyros@.hotmail.com> wrote in message
> > >> news:OwFagExeEHA.3412@.TK2MSFTNGP11.phx.gbl...
> > >> > Is there any documentation or sample code to point me in the right
> > >> direction
> > >> > on how to modify an existing rendering extension?
> > >> >
> > >> > I need to add some browser checking to the existing JavaScript that
> is
> > >> > generated for the toolbar. I was hoping to modify the existing
HTML
> > >> > 4.0
> > >> > rendering to include some more browser checking to replace some of
> the
> > >> > specific IE JavaScript properties.
> > >> >
> > >> > Any help would be greatly appreciated.
> > >> >
> > >> > Thanks,
> > >> >
> > >> >
> > >>
> > >>
> > >
> > >
> >
> >
>
on how to modify an existing rendering extension?
I need to add some browser checking to the existing JavaScript that is
generated for the toolbar. I was hoping to modify the existing HTML 4.0
rendering to include some more browser checking to replace some of the
specific IE JavaScript properties.
Any help would be greatly appreciated.
Thanks,This is not possible. All of our renderers have link demands and therefore
could not be loaded by your assemblies.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
".Net Report Dev" <aanargyros@.hotmail.com> wrote in message
news:OwFagExeEHA.3412@.TK2MSFTNGP11.phx.gbl...
> Is there any documentation or sample code to point me in the right
direction
> on how to modify an existing rendering extension?
> I need to add some browser checking to the existing JavaScript that is
> generated for the toolbar. I was hoping to modify the existing HTML 4.0
> rendering to include some more browser checking to replace some of the
> specific IE JavaScript properties.
> Any help would be greatly appreciated.
> Thanks,
>|||Are you saying what I am trying cannot be done or that I cannot modify an
existing rendering extension. According to the books online I should be
able to modify an existing extension. If I cannot modify an existing
extension, is there any way for me to modify the toolbar JavaScript?
"Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
news:unQWaTxeEHA.2916@.TK2MSFTNGP12.phx.gbl...
> This is not possible. All of our renderers have link demands and
therefore
> could not be loaded by your assemblies.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> ".Net Report Dev" <aanargyros@.hotmail.com> wrote in message
> news:OwFagExeEHA.3412@.TK2MSFTNGP11.phx.gbl...
> > Is there any documentation or sample code to point me in the right
> direction
> > on how to modify an existing rendering extension?
> >
> > I need to add some browser checking to the existing JavaScript that is
> > generated for the toolbar. I was hoping to modify the existing HTML 4.0
> > rendering to include some more browser checking to replace some of the
> > specific IE JavaScript properties.
> >
> > Any help would be greatly appreciated.
> >
> > Thanks,
> >
> >
>|||You can not modify the existing renderers. Can you point me to where in the
docs it says this?
We currently have no mechanism for modifying the JavaScript in the toolbar
so I am not sure how you could accomplish what you are trying to do.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
".Net Report Dev" <aanargyros@.hotmail.com> wrote in message
news:#XaMU8xeEHA.1656@.TK2MSFTNGP09.phx.gbl...
> Are you saying what I am trying cannot be done or that I cannot modify an
> existing rendering extension. According to the books online I should be
> able to modify an existing extension. If I cannot modify an existing
> extension, is there any way for me to modify the toolbar JavaScript?
>
> "Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
> news:unQWaTxeEHA.2916@.TK2MSFTNGP12.phx.gbl...
> > This is not possible. All of our renderers have link demands and
> therefore
> > could not be loaded by your assemblies.
> >
> > --
> > -Daniel
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >
> >
> > ".Net Report Dev" <aanargyros@.hotmail.com> wrote in message
> > news:OwFagExeEHA.3412@.TK2MSFTNGP11.phx.gbl...
> > > Is there any documentation or sample code to point me in the right
> > direction
> > > on how to modify an existing rendering extension?
> > >
> > > I need to add some browser checking to the existing JavaScript that is
> > > generated for the toolbar. I was hoping to modify the existing HTML
4.0
> > > rendering to include some more browser checking to replace some of the
> > > specific IE JavaScript properties.
> > >
> > > Any help would be greatly appreciated.
> > >
> > > Thanks,
> > >
> > >
> >
> >
>|||Could you point me at the section in books online that you're referring to?
You cannot extend our existing rendering extensions. You can extend report
server by writing another rendering extension.
No you cannot change/add script to the report viewer or the report manager.
-Lukasz
This posting is provided "AS IS" with no warranties, and confers no rights.
".Net Report Dev" <aanargyros@.hotmail.com> wrote in message
news:%23XaMU8xeEHA.1656@.TK2MSFTNGP09.phx.gbl...
> Are you saying what I am trying cannot be done or that I cannot modify an
> existing rendering extension. According to the books online I should be
> able to modify an existing extension. If I cannot modify an existing
> extension, is there any way for me to modify the toolbar JavaScript?
>
> "Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
> news:unQWaTxeEHA.2916@.TK2MSFTNGP12.phx.gbl...
>> This is not possible. All of our renderers have link demands and
> therefore
>> could not be loaded by your assemblies.
>> --
>> -Daniel
>> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>>
>> ".Net Report Dev" <aanargyros@.hotmail.com> wrote in message
>> news:OwFagExeEHA.3412@.TK2MSFTNGP11.phx.gbl...
>> > Is there any documentation or sample code to point me in the right
>> direction
>> > on how to modify an existing rendering extension?
>> >
>> > I need to add some browser checking to the existing JavaScript that is
>> > generated for the toolbar. I was hoping to modify the existing HTML
>> > 4.0
>> > rendering to include some more browser checking to replace some of the
>> > specific IE JavaScript properties.
>> >
>> > Any help would be greatly appreciated.
>> >
>> > Thanks,
>> >
>> >
>>
>|||Is there any documentation on creating your own rendering extension?
Maybe I can create a custom toolbar within my new rendering extension and
then hide the existing toolbar using the command provided by reporting
services.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_prog_extend_security_87oi.asp
Here is a snippet.
Writing Custom Rendering Extensions
Before you decide to create a custom rendering extension, you should
evaluate simpler alternatives. You can:
a.. Create a modified version of an existing rendering extension.
b.. Customize rendered output by specifying device information settings
for existing extensions.
c.. Add custom formatting and presentation features by combining XSL
Transformations (XSLT) with the output of the XML rendering format.
"Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
news:%23L0zLVyeEHA.2764@.TK2MSFTNGP11.phx.gbl...
> You can not modify the existing renderers. Can you point me to where in
the
> docs it says this?
> We currently have no mechanism for modifying the JavaScript in the toolbar
> so I am not sure how you could accomplish what you are trying to do.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> ".Net Report Dev" <aanargyros@.hotmail.com> wrote in message
> news:#XaMU8xeEHA.1656@.TK2MSFTNGP09.phx.gbl...
> > Are you saying what I am trying cannot be done or that I cannot modify
an
> > existing rendering extension. According to the books online I should be
> > able to modify an existing extension. If I cannot modify an existing
> > extension, is there any way for me to modify the toolbar JavaScript?
> >
> >
> >
> > "Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
> > news:unQWaTxeEHA.2916@.TK2MSFTNGP12.phx.gbl...
> > > This is not possible. All of our renderers have link demands and
> > therefore
> > > could not be loaded by your assemblies.
> > >
> > > --
> > > -Daniel
> > > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> > >
> > >
> > > ".Net Report Dev" <aanargyros@.hotmail.com> wrote in message
> > > news:OwFagExeEHA.3412@.TK2MSFTNGP11.phx.gbl...
> > > > Is there any documentation or sample code to point me in the right
> > > direction
> > > > on how to modify an existing rendering extension?
> > > >
> > > > I need to add some browser checking to the existing JavaScript that
is
> > > > generated for the toolbar. I was hoping to modify the existing HTML
> 4.0
> > > > rendering to include some more browser checking to replace some of
the
> > > > specific IE JavaScript properties.
> > > >
> > > > Any help would be greatly appreciated.
> > > >
> > > > Thanks,
> > > >
> > > >
> > >
> > >
> >
> >
>|||I guess I did not read the documentation correctly. It sounded to me that
you can modify the existing extensions but instead it is saying create
another version of an existing extension with your changes. If that is what
the document means than how is that an alternative to writing a new
extension? Its not like MS will give up the source so that I can just
create my own version.
"Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
news:%23L0zLVyeEHA.2764@.TK2MSFTNGP11.phx.gbl...
> You can not modify the existing renderers. Can you point me to where in
the
> docs it says this?
> We currently have no mechanism for modifying the JavaScript in the toolbar
> so I am not sure how you could accomplish what you are trying to do.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> ".Net Report Dev" <aanargyros@.hotmail.com> wrote in message
> news:#XaMU8xeEHA.1656@.TK2MSFTNGP09.phx.gbl...
> > Are you saying what I am trying cannot be done or that I cannot modify
an
> > existing rendering extension. According to the books online I should be
> > able to modify an existing extension. If I cannot modify an existing
> > extension, is there any way for me to modify the toolbar JavaScript?
> >
> >
> >
> > "Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
> > news:unQWaTxeEHA.2916@.TK2MSFTNGP12.phx.gbl...
> > > This is not possible. All of our renderers have link demands and
> > therefore
> > > could not be loaded by your assemblies.
> > >
> > > --
> > > -Daniel
> > > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> > >
> > >
> > > ".Net Report Dev" <aanargyros@.hotmail.com> wrote in message
> > > news:OwFagExeEHA.3412@.TK2MSFTNGP11.phx.gbl...
> > > > Is there any documentation or sample code to point me in the right
> > > direction
> > > > on how to modify an existing rendering extension?
> > > >
> > > > I need to add some browser checking to the existing JavaScript that
is
> > > > generated for the toolbar. I was hoping to modify the existing HTML
> 4.0
> > > > rendering to include some more browser checking to replace some of
the
> > > > specific IE JavaScript properties.
> > > >
> > > > Any help would be greatly appreciated.
> > > >
> > > > Thanks,
> > > >
> > > >
> > >
> > >
> >
> >
>|||Yes, the documentation seems to be a little misleading. I will forward it
to our doc team to see if they can make it a little more clear.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
".Net Report Dev" <aanargyros@.hotmail.com> wrote in message
news:u#6YtzyeEHA.4092@.TK2MSFTNGP10.phx.gbl...
> Is there any documentation on creating your own rendering extension?
> Maybe I can create a custom toolbar within my new rendering extension and
> then hide the existing toolbar using the command provided by reporting
> services.
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_prog_extend_security_87oi.asp
> Here is a snippet.
> Writing Custom Rendering Extensions
> Before you decide to create a custom rendering extension, you should
> evaluate simpler alternatives. You can:
> a.. Create a modified version of an existing rendering extension.
> b.. Customize rendered output by specifying device information settings
> for existing extensions.
> c.. Add custom formatting and presentation features by combining XSL
> Transformations (XSLT) with the output of the XML rendering format.
>
> "Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
> news:%23L0zLVyeEHA.2764@.TK2MSFTNGP11.phx.gbl...
> > You can not modify the existing renderers. Can you point me to where in
> the
> > docs it says this?
> >
> > We currently have no mechanism for modifying the JavaScript in the
toolbar
> > so I am not sure how you could accomplish what you are trying to do.
> >
> > --
> > -Daniel
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >
> >
> > ".Net Report Dev" <aanargyros@.hotmail.com> wrote in message
> > news:#XaMU8xeEHA.1656@.TK2MSFTNGP09.phx.gbl...
> > > Are you saying what I am trying cannot be done or that I cannot modify
> an
> > > existing rendering extension. According to the books online I should
be
> > > able to modify an existing extension. If I cannot modify an existing
> > > extension, is there any way for me to modify the toolbar JavaScript?
> > >
> > >
> > >
> > > "Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
> > > news:unQWaTxeEHA.2916@.TK2MSFTNGP12.phx.gbl...
> > > > This is not possible. All of our renderers have link demands and
> > > therefore
> > > > could not be loaded by your assemblies.
> > > >
> > > > --
> > > > -Daniel
> > > > This posting is provided "AS IS" with no warranties, and confers no
> > > rights.
> > > >
> > > >
> > > > ".Net Report Dev" <aanargyros@.hotmail.com> wrote in message
> > > > news:OwFagExeEHA.3412@.TK2MSFTNGP11.phx.gbl...
> > > > > Is there any documentation or sample code to point me in the right
> > > > direction
> > > > > on how to modify an existing rendering extension?
> > > > >
> > > > > I need to add some browser checking to the existing JavaScript
that
> is
> > > > > generated for the toolbar. I was hoping to modify the existing
HTML
> > 4.0
> > > > > rendering to include some more browser checking to replace some of
> the
> > > > > specific IE JavaScript properties.
> > > > >
> > > > > Any help would be greatly appreciated.
> > > > >
> > > > > Thanks,
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||Is there any documentation on creating your own rendering extension?
Maybe I can create a custom toolbar within my new rendering extension and
then hide the existing toolbar using the command provided by reporting
services.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_prog_extend_security_87oi.asp
Here is a snippet.
Writing Custom Rendering Extensions
Before you decide to create a custom rendering extension, you should
evaluate simpler alternatives. You can:
a.. Create a modified version of an existing rendering extension.
b.. Customize rendered output by specifying device information settings
for existing extensions.
c.. Add custom formatting and presentation features by combining XSL
Transformations (XSLT) with the output of the XML rendering format.
"Lukasz Pawlowski [MSFT]" <lukaszp@.online.microsoft.com> wrote in message
news:uf6ndVyeEHA.1036@.TK2MSFTNGP10.phx.gbl...
> Could you point me at the section in books online that you're referring
to?
> You cannot extend our existing rendering extensions. You can extend
report
> server by writing another rendering extension.
> No you cannot change/add script to the report viewer or the report
manager.
> -Lukasz
>
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> ".Net Report Dev" <aanargyros@.hotmail.com> wrote in message
> news:%23XaMU8xeEHA.1656@.TK2MSFTNGP09.phx.gbl...
> > Are you saying what I am trying cannot be done or that I cannot modify
an
> > existing rendering extension. According to the books online I should be
> > able to modify an existing extension. If I cannot modify an existing
> > extension, is there any way for me to modify the toolbar JavaScript?
> >
> >
> >
> > "Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
> > news:unQWaTxeEHA.2916@.TK2MSFTNGP12.phx.gbl...
> >> This is not possible. All of our renderers have link demands and
> > therefore
> >> could not be loaded by your assemblies.
> >>
> >> --
> >> -Daniel
> >> This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> >>
> >>
> >> ".Net Report Dev" <aanargyros@.hotmail.com> wrote in message
> >> news:OwFagExeEHA.3412@.TK2MSFTNGP11.phx.gbl...
> >> > Is there any documentation or sample code to point me in the right
> >> direction
> >> > on how to modify an existing rendering extension?
> >> >
> >> > I need to add some browser checking to the existing JavaScript that
is
> >> > generated for the toolbar. I was hoping to modify the existing HTML
> >> > 4.0
> >> > rendering to include some more browser checking to replace some of
the
> >> > specific IE JavaScript properties.
> >> >
> >> > Any help would be greatly appreciated.
> >> >
> >> > Thanks,
> >> >
> >> >
> >>
> >>
> >
> >
>|||Is there any documentation on creating your own rendering extension that I
can use now?
"Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
news:eTRNDCzeEHA.1644@.tk2msftngp13.phx.gbl...
> Yes, the documentation seems to be a little misleading. I will forward it
> to our doc team to see if they can make it a little more clear.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> ".Net Report Dev" <aanargyros@.hotmail.com> wrote in message
> news:u#6YtzyeEHA.4092@.TK2MSFTNGP10.phx.gbl...
> > Is there any documentation on creating your own rendering extension?
> > Maybe I can create a custom toolbar within my new rendering extension
and
> > then hide the existing toolbar using the command provided by reporting
> > services.
> >
> >
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_prog_extend_security_87oi.asp
> >
> > Here is a snippet.
> >
> > Writing Custom Rendering Extensions
> > Before you decide to create a custom rendering extension, you should
> > evaluate simpler alternatives. You can:
> >
> > a.. Create a modified version of an existing rendering extension.
> > b.. Customize rendered output by specifying device information
settings
> > for existing extensions.
> > c.. Add custom formatting and presentation features by combining XSL
> > Transformations (XSLT) with the output of the XML rendering format.
> >
> >
> > "Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
> > news:%23L0zLVyeEHA.2764@.TK2MSFTNGP11.phx.gbl...
> > > You can not modify the existing renderers. Can you point me to where
in
> > the
> > > docs it says this?
> > >
> > > We currently have no mechanism for modifying the JavaScript in the
> toolbar
> > > so I am not sure how you could accomplish what you are trying to do.
> > >
> > > --
> > > -Daniel
> > > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> > >
> > >
> > > ".Net Report Dev" <aanargyros@.hotmail.com> wrote in message
> > > news:#XaMU8xeEHA.1656@.TK2MSFTNGP09.phx.gbl...
> > > > Are you saying what I am trying cannot be done or that I cannot
modify
> > an
> > > > existing rendering extension. According to the books online I
should
> be
> > > > able to modify an existing extension. If I cannot modify an
existing
> > > > extension, is there any way for me to modify the toolbar JavaScript?
> > > >
> > > >
> > > >
> > > > "Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
> > > > news:unQWaTxeEHA.2916@.TK2MSFTNGP12.phx.gbl...
> > > > > This is not possible. All of our renderers have link demands and
> > > > therefore
> > > > > could not be loaded by your assemblies.
> > > > >
> > > > > --
> > > > > -Daniel
> > > > > This posting is provided "AS IS" with no warranties, and confers
no
> > > > rights.
> > > > >
> > > > >
> > > > > ".Net Report Dev" <aanargyros@.hotmail.com> wrote in message
> > > > > news:OwFagExeEHA.3412@.TK2MSFTNGP11.phx.gbl...
> > > > > > Is there any documentation or sample code to point me in the
right
> > > > > direction
> > > > > > on how to modify an existing rendering extension?
> > > > > >
> > > > > > I need to add some browser checking to the existing JavaScript
> that
> > is
> > > > > > generated for the toolbar. I was hoping to modify the existing
> HTML
> > > 4.0
> > > > > > rendering to include some more browser checking to replace some
of
> > the
> > > > > > specific IE JavaScript properties.
> > > > > >
> > > > > > Any help would be greatly appreciated.
> > > > > >
> > > > > > Thanks,
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||So, although the documentation alludes to modifying an existing rendering
extension, what it is actually referring to is creating a version of a
rendering extension that is similar to ones that we already have. That is,
writing a new HTML rendering extension. I must admit that it is misleading
and that writing one of our rendering extensions from scratch is not a
"simpler alternative."
Writing a rendering extension is no simple matter, in fact, it is one of the
most challenging development experiences I have seen at Microsoft. Please
see the following blog post for my take:
http://blogs.msdn.com/bryanke/archive/2004/03/16/90797.aspx
The Reporting Services team, in light of other key deliverables, elected not
to document rendering extensions in version 1 of Reporting Services because
it was considered a low usage task (only a few select partners ever
expressed an interest in developing one and only a handful have ever
successfully done it and that is after spending months with our developers
hands-on).
It is not an end-user feature and is not something for the average or even
advanced developer. If you are interested, we have included documentation
for rendering extensions in our SQL Server 2005 Books Online that was
released with SQL Server 2005 Beta 2 recently. We hope to someday create an
SDK that will explain rendering extensions in more detail, but that is
probably a ways off.
Bryan
--
Bryan Keller
Developer Documentation
SQL Server Reporting Services
A friendly reminder that this posting is provided "AS IS" with no
warranties, and confers no rights.
".Net Report Dev" <aanargyros@.hotmail.com> wrote in message
news:eB9luSzeEHA.1692@.tk2msftngp13.phx.gbl...
> Is there any documentation on creating your own rendering extension?
> Maybe I can create a custom toolbar within my new rendering extension and
> then hide the existing toolbar using the command provided by reporting
> services.
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_prog_extend_security_87oi.asp
> Here is a snippet.
> Writing Custom Rendering Extensions
> Before you decide to create a custom rendering extension, you should
> evaluate simpler alternatives. You can:
> a.. Create a modified version of an existing rendering extension.
> b.. Customize rendered output by specifying device information settings
> for existing extensions.
> c.. Add custom formatting and presentation features by combining XSL
> Transformations (XSLT) with the output of the XML rendering format.
> "Lukasz Pawlowski [MSFT]" <lukaszp@.online.microsoft.com> wrote in message
> news:uf6ndVyeEHA.1036@.TK2MSFTNGP10.phx.gbl...
> > Could you point me at the section in books online that you're referring
> to?
> > You cannot extend our existing rendering extensions. You can extend
> report
> > server by writing another rendering extension.
> >
> > No you cannot change/add script to the report viewer or the report
> manager.
> >
> > -Lukasz
> >
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >
> >
> > ".Net Report Dev" <aanargyros@.hotmail.com> wrote in message
> > news:%23XaMU8xeEHA.1656@.TK2MSFTNGP09.phx.gbl...
> > > Are you saying what I am trying cannot be done or that I cannot modify
> an
> > > existing rendering extension. According to the books online I should
be
> > > able to modify an existing extension. If I cannot modify an existing
> > > extension, is there any way for me to modify the toolbar JavaScript?
> > >
> > >
> > >
> > > "Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
> > > news:unQWaTxeEHA.2916@.TK2MSFTNGP12.phx.gbl...
> > >> This is not possible. All of our renderers have link demands and
> > > therefore
> > >> could not be loaded by your assemblies.
> > >>
> > >> --
> > >> -Daniel
> > >> This posting is provided "AS IS" with no warranties, and confers no
> > > rights.
> > >>
> > >>
> > >> ".Net Report Dev" <aanargyros@.hotmail.com> wrote in message
> > >> news:OwFagExeEHA.3412@.TK2MSFTNGP11.phx.gbl...
> > >> > Is there any documentation or sample code to point me in the right
> > >> direction
> > >> > on how to modify an existing rendering extension?
> > >> >
> > >> > I need to add some browser checking to the existing JavaScript that
> is
> > >> > generated for the toolbar. I was hoping to modify the existing
HTML
> > >> > 4.0
> > >> > rendering to include some more browser checking to replace some of
> the
> > >> > specific IE JavaScript properties.
> > >> >
> > >> > Any help would be greatly appreciated.
> > >> >
> > >> > Thanks,
> > >> >
> > >> >
> > >>
> > >>
> > >
> > >
> >
> >
>
Tuesday, March 20, 2012
Create a calculated member on one measure.
I have a Version dimension which is used to break out the Net measure by 'Actual' or 'Budget'. Net is the only measure I'm using. By not using the Version dimsion. The net is incorrectly totaled by both values. Both are not used together.
'Actual' and 'Budget' are the only two members of the Version dimension. I want to add a third member which subtracts 'Budget' Net measure from the 'Actual' Net measure and call it variance. A caculated measure isn't working for me because obviously subtracting Actual - Budget doesn't work so well.
Can anyone help me with that?
First, you need to mark your Version attribute as Not Aggregatable (set IsAggregatable property to false) - this will prevent summing up Actual and Budget.
I am not sure why is it "obviously" not working well, the following should work well for you:
CREATE Version.Variance = Version.Actual - Version.Budget;
HTH,
Mosha (http://www.mosha.com/msolap)
|||Where do this statement go? Sorry, I'm new to this.|||You add this statement to the MDX Script of the cube. It is under the Calculation tab in the Cube window. You may want to switch to Script view from Form view in order to copy it verbatim.|||Thanks for your prompt response. So I should add a 'New Script'?|||I created a new script and added this to it by dragging the objects to the form and substituting in "Variance", but it didn't work:
CREATE [Version Dim V].[Version].&[Variance] = [Version Dim V].[Version].&[Actual] - [Version Dim V].[Version].&[Budget];
MdxScript(X_GL_Data) (6, 133) Parser: The syntax for ';' is incorrect.
Any ideas?
Thanks!
|||OK I got it to the point where there are no errors, but the Variance member will still not show up in the Version dimension. WHat could be the cause?
/*
The CALCULATE command controls the aggregation of leaf cells in the cube.
If the CALCULATE command is deleted or modified, the data within the cube is affected.
You should edit this command only if you manually specify how the cube is aggregated.
*/
CALCULATE;
CREATE MEMBER CURRENTCUBE.[Version Dim V].[Version].[Variance]
AS [Version Dim V].[Version].&[Actual]-[Version Dim V].[Version].&[Budget],
FORMAT_STRING = "Currency",
NON_EMPTY_BEHAVIOR = { [Net Amount] },
VISIBLE = 1 ;
Thanks!
|||I see what it's doing. Its showing up in the Version_Heirarchy on the same level as 'All', not as a sibling of 'Budget' and 'Actual'. It's unusable there. How can I add it as a sibling?|||First - please remove NON_EMPTY_BEHAVIOR - it is defined absolutely wrong.
Second, how exactly it doesn't show up ? If you connect with SSMS to the cube and browse the dimension - don't you see this calculated member ?
|||According to your original posting, you didn't want to have All to begin with. Therefore I advised you to set IsAggregatable=false - this way you won't have level/member All in the hierarchy.|||At the time, 'all' was allowed. I didn't have it turned off. Everytime I try to set IsAggregatable to False for the Version_Key, Version_Description and the hierarchy, I get an error:
Error 1 Errors in the metadata manager. The 'Version Key' attribute with IsAggregatable=false must not have any relationship to 'Version Description' attribute that has AttributeHierarchyEnabled=true. 0 0
If I have AttributeHierarchyEnabled=False, I get two new errors:
Error 1 Dimension 'Version Dim V' > Attribute 'Version Key' : Attribute hierarchy must be enabled if attribute relationships are used. 0 0
Error 2 Dimension 'Version Dim V' > Attribute 'Version Key' : The key attribute requires attribute hierarchy enabled. 0 0
Version_Description is the only value I want to use. It contains 'Actual' and 'Budget', and should also include the 'Variance'
Thanks
|||I finally got 'Variance' to show up with Actual and Budget, but when I try to apply it as a filter expression, I get the error below:
A set has been encountered that cannot contain calculated members.
The other two members of Version work fine.
Here is the setup:
Attribute:
Version Description: IsAggregatable=False, AttributeHierarchyEnabled=True
VersionKey: Key, AttributeHierarchyVisible=False, AtrributeRelationship to VersionDescription
Here is how the member is created:
CREATE MEMBER CURRENTCUBE.[Version Dim V].[Version Hierarchy].[Variance]
AS [Version Dim V].[Version].&[Actual]-[Version Dim V].[Version].&[Budget],
FORMAT_STRING = "Currency",
NON_EMPTY_BEHAVIOR = { [Net Amount] },
VISIBLE = 1 ;
The relationship between dimension columns and measure group columns is VersionKey>VersioKey with a granularity attribute of 'version key'. Using Version Description as the granularity attribute fails indicating it can not find the Version_key attribute key.
Thanks
|||Finally, I got it by changing the hierarchy to the Attribute hierarchy for VersionDescription, not the hierarchy with Version Description as the only attribute/level in the hierarchy pane.
Thanks for your help in walking me through it.
|||Mike,
I have a very similiar problem, but didn't fully understand your solution. Can you explain how you have your dimension structured and what properties you have set again descriptively in one post? I'm a little lost trying to follow the thread.
Thanks,
-Nimitt
Create a calculated member on one measure.
I have a Version dimension which is used to break out the Net measure by 'Actual' or 'Budget'. Net is the only measure I'm using. By not using the Version dimsion. The net is incorrectly totaled by both values. Both are not used together.
'Actual' and 'Budget' are the only two members of the Version dimension. I want to add a third member which subtracts 'Budget' Net measure from the 'Actual' Net measure and call it variance. A caculated measure isn't working for me because obviously subtracting Actual - Budget doesn't work so well.
Can anyone help me with that?
First, you need to mark your Version attribute as Not Aggregatable (set IsAggregatable property to false) - this will prevent summing up Actual and Budget.
I am not sure why is it "obviously" not working well, the following should work well for you:
CREATE Version.Variance = Version.Actual - Version.Budget;
HTH,
Mosha (http://www.mosha.com/msolap)
|||Where do this statement go? Sorry, I'm new to this.|||You add this statement to the MDX Script of the cube. It is under the Calculation tab in the Cube window. You may want to switch to Script view from Form view in order to copy it verbatim.|||Thanks for your prompt response. So I should add a 'New Script'?|||I created a new script and added this to it by dragging the objects to the form and substituting in "Variance", but it didn't work:
CREATE [Version Dim V].[Version].&[Variance] = [Version Dim V].[Version].&[Actual] - [Version Dim V].[Version].&[Budget];
MdxScript(X_GL_Data) (6, 133) Parser: The syntax for ';' is incorrect.
Any ideas?
Thanks!
|||OK I got it to the point where there are no errors, but the Variance member will still not show up in the Version dimension. WHat could be the cause?
/*
The CALCULATE command controls the aggregation of leaf cells in the cube.
If the CALCULATE command is deleted or modified, the data within the cube is affected.
You should edit this command only if you manually specify how the cube is aggregated.
*/
CALCULATE;
CREATE MEMBER CURRENTCUBE.[Version Dim V].[Version].[Variance]
AS [Version Dim V].[Version].&[Actual]-[Version Dim V].[Version].&[Budget],
FORMAT_STRING = "Currency",
NON_EMPTY_BEHAVIOR = { [Net Amount] },
VISIBLE = 1 ;
Thanks!
|||I see what it's doing. Its showing up in the Version_Heirarchy on the same level as 'All', not as a sibling of 'Budget' and 'Actual'. It's unusable there. How can I add it as a sibling?|||First - please remove NON_EMPTY_BEHAVIOR - it is defined absolutely wrong.
Second, how exactly it doesn't show up ? If you connect with SSMS to the cube and browse the dimension - don't you see this calculated member ?
|||According to your original posting, you didn't want to have All to begin with. Therefore I advised you to set IsAggregatable=false - this way you won't have level/member All in the hierarchy.|||At the time, 'all' was allowed. I didn't have it turned off. Everytime I try to set IsAggregatable to False for the Version_Key, Version_Description and the hierarchy, I get an error:
Error 1 Errors in the metadata manager. The 'Version Key' attribute with IsAggregatable=false must not have any relationship to 'Version Description' attribute that has AttributeHierarchyEnabled=true. 0 0
If I have AttributeHierarchyEnabled=False, I get two new errors:
Error 1 Dimension 'Version Dim V' > Attribute 'Version Key' : Attribute hierarchy must be enabled if attribute relationships are used. 0 0
Error 2 Dimension 'Version Dim V' > Attribute 'Version Key' : The key attribute requires attribute hierarchy enabled. 0 0
Version_Description is the only value I want to use. It contains 'Actual' and 'Budget', and should also include the 'Variance'
Thanks
|||I finally got 'Variance' to show up with Actual and Budget, but when I try to apply it as a filter expression, I get the error below:
A set has been encountered that cannot contain calculated members.
The other two members of Version work fine.
Here is the setup:
Attribute:
Version Description: IsAggregatable=False, AttributeHierarchyEnabled=True
VersionKey: Key, AttributeHierarchyVisible=False, AtrributeRelationship to VersionDescription
Here is how the member is created:
CREATE MEMBER CURRENTCUBE.[Version Dim V].[Version Hierarchy].[Variance]
AS [Version Dim V].[Version].&[Actual]-[Version Dim V].[Version].&[Budget],
FORMAT_STRING = "Currency",
NON_EMPTY_BEHAVIOR = { [Net Amount] },
VISIBLE = 1 ;
The relationship between dimension columns and measure group columns is VersionKey>VersioKey with a granularity attribute of 'version key'. Using Version Description as the granularity attribute fails indicating it can not find the Version_key attribute key.
Thanks
|||Finally, I got it by changing the hierarchy to the Attribute hierarchy for VersionDescription, not the hierarchy with Version Description as the only attribute/level in the hierarchy pane.
Thanks for your help in walking me through it.
|||Mike,
I have a very similiar problem, but didn't fully understand your solution. Can you explain how you have your dimension structured and what properties you have set again descriptively in one post? I'm a little lost trying to follow the thread.
Thanks,
-Nimitt
Create 2 publications for same table
Hi
I created a DB named 'TestDB' and created a table called Users. This user table having 3 columns (Uname and pwd,version).
I need to create two publication for this user table.
1) Create a publication using all columns.
2) create a publication using Uname and pwd (not version column).
I am using Merge publication.
When I create first publication (any one - all 3 columns or any 2 coulmns) it create successfully.
When I create second publication it throws error. The details are below.
TITLE: New Publication Wizard
SQL Server Management Studio could not create article 'Users'.
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
An article with a different subscriber_upload_options value already exists for object '[dbo].[Users]'.
Changed database context to 'TestDB'. (Microsoft SQL Server, Error: 20053)
How can i create the second publication? Is it possible? If yes, please give me the steps.
Thanks
Hi
Got the solution. Solved the problem.
The problem was when i create the second publication I changed the article synchronization direction. thats why it throws the error. After givig the same article synchronization direction it is working.
Monday, March 19, 2012
Crashes all over!
Since downloading the released(?) version of SQL and Visual Studio 2005, my Visual Studio environment crashes quite often when doing Reporting Services work(and this was a clean install after a hard drive format). ASP.Net and other VS tasks work fine - it seems to be just Reprting Services that is buggy. Here are the instances in which it crashes:
- The most frustrating is when I'm trying to design a report based on stored procedures with parameters, if the parameters aren't wired up correctly and you hit preview report, Visual Studio crashes. This probably happened 20-30 times yesterday - so much that I've had to revert to a machine with Beta 2 to get some work done.
- When entering the Query Editor during the report wizard, if you click the design veiw button a couple of times too quickly, it crashes.
- The most frustrating is when I'm trying to design a report based on stored procedures with parameters, if the parameters aren't wired up correctly and you hit preview report, Visual Studio crashes. This probably happened 20-30 times yesterday - so much that I've had to revert to a machine with Beta 2 to get some work done.
- When entering the Query Editor during the report wizard, if you click the design veiw button a couple of times too quickly, it crashes.
Also, when I start the new report wizard, I can't base a report on a Stored Procedure, only text is available (the dropdown is greyed out).
All this is happening on multiple machines with the new build (9.00.1399.06).
Is anyone else having this problem? Did I get the wrong download? Is there some type of fix for this?
Thanks for any help.
Trent...if the parameters aren't wired up correctly and you hit preview report, Visual Studio crashes.Please enter a bug at http://lab.msdn.microsoft.com/ProductFeedback.
When entering the Query Editor during the report wizard, if you click the design veiw button a couple of times too quickly, it crashes.This is a known issue. It will be fixed in SP1.
...when I start the new report wizard, I can't base a report on a Stored Procedure, only text is available (the dropdown is greyed out).This is intentional. The query designer in the Wizard can only be used to create text queries.
Sunday, March 11, 2012
CPU/Core Prio. on SQL Server Express Instance?
Hi,
I am currently using a single SQL Server Express Instance on a Core 2 Duo
box.
I know that the Express version is limited to use 1 CPU/Core.
I now want to setup a second SQL Server Express instance for unrelated
purposes.
Is it possible to give one instance Core 0 prio, and the Second the Core 1
prio, or do they ALWAYS (MUST/ONLY) use Core 0?
Also, if I don't set it specifically will Windows manage it properly?
I guess my main question in detail is:
Is the Express version build in such a way that it will always use the first
core/cpu regardless how many there are? For example, if I have 3 instances
running, would they all fight for cycles on the first core/cpu?
If not, do I have to do/set something to make it work properly?
Thanks for any Info. on this!
Regards,
FrankExpress will only create one scheduler. A scheduler is by default not bound to a particular core, it
can "float" between the cores. However, there is no guarantee that your two express instances will
always be on different cores.
However, you can use sp_configure and the "affinity mask" option to specify that instance it is tied
to core 0 and instance 2 is tied to core 1. This way you've configured the two instances to use
different cores.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Frank Osterberg" <raven7370@.yahoo.com> wrote in message
news:uVFYNK6dIHA.748@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I am currently using a single SQL Server Express Instance on a Core 2 Duo box.
> I know that the Express version is limited to use 1 CPU/Core.
> I now want to setup a second SQL Server Express instance for unrelated purposes.
> Is it possible to give one instance Core 0 prio, and the Second the Core 1 prio, or do they ALWAYS
> (MUST/ONLY) use Core 0?
> Also, if I don't set it specifically will Windows manage it properly?
> I guess my main question in detail is:
> Is the Express version build in such a way that it will always use the first core/cpu regardless
> how many there are? For example, if I have 3 instances running, would they all fight for cycles on
> the first core/cpu?
> If not, do I have to do/set something to make it work properly?
> Thanks for any Info. on this!
> Regards,
> Frank
>|||> Also, if I don't set it specifically will Windows manage it properly?
To add on to Tibor's response, the OS can schedule work on any available
core when the SQL Server affinity mask is not set. You do not need to do
anything special to make this happen.
--
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"Frank Osterberg" <raven7370@.yahoo.com> wrote in message
news:uVFYNK6dIHA.748@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I am currently using a single SQL Server Express Instance on a Core 2 Duo
> box.
> I know that the Express version is limited to use 1 CPU/Core.
> I now want to setup a second SQL Server Express instance for unrelated
> purposes.
> Is it possible to give one instance Core 0 prio, and the Second the Core 1
> prio, or do they ALWAYS (MUST/ONLY) use Core 0?
> Also, if I don't set it specifically will Windows manage it properly?
> I guess my main question in detail is:
> Is the Express version build in such a way that it will always use the
> first core/cpu regardless how many there are? For example, if I have 3
> instances running, would they all fight for cycles on the first core/cpu?
> If not, do I have to do/set something to make it work properly?
> Thanks for any Info. on this!
> Regards,
> Frank
>|||Great! Thank you very much!!!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> schrieb
im Newsbeitrag news:eI1NqL7dIHA.4728@.TK2MSFTNGP03.phx.gbl...
> Express will only create one scheduler. A scheduler is by default not
> bound to a particular core, it can "float" between the cores. However,
> there is no guarantee that your two express instances will always be on
> different cores.
> However, you can use sp_configure and the "affinity mask" option to
> specify that instance it is tied to core 0 and instance 2 is tied to core
> 1. This way you've configured the two instances to use different cores.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Frank Osterberg" <raven7370@.yahoo.com> wrote in message
> news:uVFYNK6dIHA.748@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> I am currently using a single SQL Server Express Instance on a Core 2 Duo
>> box.
>> I know that the Express version is limited to use 1 CPU/Core.
>> I now want to setup a second SQL Server Express instance for unrelated
>> purposes.
>> Is it possible to give one instance Core 0 prio, and the Second the Core
>> 1 prio, or do they ALWAYS (MUST/ONLY) use Core 0?
>> Also, if I don't set it specifically will Windows manage it properly?
>> I guess my main question in detail is:
>> Is the Express version build in such a way that it will always use the
>> first core/cpu regardless how many there are? For example, if I have 3
>> instances running, would they all fight for cycles on the first core/cpu?
>> If not, do I have to do/set something to make it work properly?
>> Thanks for any Info. on this!
>> Regards,
>> Frank
>|||Ok, i was hoping it would be like that. Thank you!!!
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> schrieb im Newsbeitrag
news:D1D2B4EC-1D77-4D1E-941C-9E2E0F985F8C@.microsoft.com...
>> Also, if I don't set it specifically will Windows manage it properly?
> To add on to Tibor's response, the OS can schedule work on any available
> core when the SQL Server affinity mask is not set. You do not need to do
> anything special to make this happen.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> http://weblogs.sqlteam.com/dang/
> "Frank Osterberg" <raven7370@.yahoo.com> wrote in message
> news:uVFYNK6dIHA.748@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> I am currently using a single SQL Server Express Instance on a Core 2 Duo
>> box.
>> I know that the Express version is limited to use 1 CPU/Core.
>> I now want to setup a second SQL Server Express instance for unrelated
>> purposes.
>> Is it possible to give one instance Core 0 prio, and the Second the Core
>> 1 prio, or do they ALWAYS (MUST/ONLY) use Core 0?
>> Also, if I don't set it specifically will Windows manage it properly?
>> I guess my main question in detail is:
>> Is the Express version build in such a way that it will always use the
>> first core/cpu regardless how many there are? For example, if I have 3
>> instances running, would they all fight for cycles on the first core/cpu?
>> If not, do I have to do/set something to make it work properly?
>> Thanks for any Info. on this!
>> Regards,
>> Frank
>
I am currently using a single SQL Server Express Instance on a Core 2 Duo
box.
I know that the Express version is limited to use 1 CPU/Core.
I now want to setup a second SQL Server Express instance for unrelated
purposes.
Is it possible to give one instance Core 0 prio, and the Second the Core 1
prio, or do they ALWAYS (MUST/ONLY) use Core 0?
Also, if I don't set it specifically will Windows manage it properly?
I guess my main question in detail is:
Is the Express version build in such a way that it will always use the first
core/cpu regardless how many there are? For example, if I have 3 instances
running, would they all fight for cycles on the first core/cpu?
If not, do I have to do/set something to make it work properly?
Thanks for any Info. on this!
Regards,
FrankExpress will only create one scheduler. A scheduler is by default not bound to a particular core, it
can "float" between the cores. However, there is no guarantee that your two express instances will
always be on different cores.
However, you can use sp_configure and the "affinity mask" option to specify that instance it is tied
to core 0 and instance 2 is tied to core 1. This way you've configured the two instances to use
different cores.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Frank Osterberg" <raven7370@.yahoo.com> wrote in message
news:uVFYNK6dIHA.748@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I am currently using a single SQL Server Express Instance on a Core 2 Duo box.
> I know that the Express version is limited to use 1 CPU/Core.
> I now want to setup a second SQL Server Express instance for unrelated purposes.
> Is it possible to give one instance Core 0 prio, and the Second the Core 1 prio, or do they ALWAYS
> (MUST/ONLY) use Core 0?
> Also, if I don't set it specifically will Windows manage it properly?
> I guess my main question in detail is:
> Is the Express version build in such a way that it will always use the first core/cpu regardless
> how many there are? For example, if I have 3 instances running, would they all fight for cycles on
> the first core/cpu?
> If not, do I have to do/set something to make it work properly?
> Thanks for any Info. on this!
> Regards,
> Frank
>|||> Also, if I don't set it specifically will Windows manage it properly?
To add on to Tibor's response, the OS can schedule work on any available
core when the SQL Server affinity mask is not set. You do not need to do
anything special to make this happen.
--
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"Frank Osterberg" <raven7370@.yahoo.com> wrote in message
news:uVFYNK6dIHA.748@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I am currently using a single SQL Server Express Instance on a Core 2 Duo
> box.
> I know that the Express version is limited to use 1 CPU/Core.
> I now want to setup a second SQL Server Express instance for unrelated
> purposes.
> Is it possible to give one instance Core 0 prio, and the Second the Core 1
> prio, or do they ALWAYS (MUST/ONLY) use Core 0?
> Also, if I don't set it specifically will Windows manage it properly?
> I guess my main question in detail is:
> Is the Express version build in such a way that it will always use the
> first core/cpu regardless how many there are? For example, if I have 3
> instances running, would they all fight for cycles on the first core/cpu?
> If not, do I have to do/set something to make it work properly?
> Thanks for any Info. on this!
> Regards,
> Frank
>|||Great! Thank you very much!!!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> schrieb
im Newsbeitrag news:eI1NqL7dIHA.4728@.TK2MSFTNGP03.phx.gbl...
> Express will only create one scheduler. A scheduler is by default not
> bound to a particular core, it can "float" between the cores. However,
> there is no guarantee that your two express instances will always be on
> different cores.
> However, you can use sp_configure and the "affinity mask" option to
> specify that instance it is tied to core 0 and instance 2 is tied to core
> 1. This way you've configured the two instances to use different cores.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Frank Osterberg" <raven7370@.yahoo.com> wrote in message
> news:uVFYNK6dIHA.748@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> I am currently using a single SQL Server Express Instance on a Core 2 Duo
>> box.
>> I know that the Express version is limited to use 1 CPU/Core.
>> I now want to setup a second SQL Server Express instance for unrelated
>> purposes.
>> Is it possible to give one instance Core 0 prio, and the Second the Core
>> 1 prio, or do they ALWAYS (MUST/ONLY) use Core 0?
>> Also, if I don't set it specifically will Windows manage it properly?
>> I guess my main question in detail is:
>> Is the Express version build in such a way that it will always use the
>> first core/cpu regardless how many there are? For example, if I have 3
>> instances running, would they all fight for cycles on the first core/cpu?
>> If not, do I have to do/set something to make it work properly?
>> Thanks for any Info. on this!
>> Regards,
>> Frank
>|||Ok, i was hoping it would be like that. Thank you!!!
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> schrieb im Newsbeitrag
news:D1D2B4EC-1D77-4D1E-941C-9E2E0F985F8C@.microsoft.com...
>> Also, if I don't set it specifically will Windows manage it properly?
> To add on to Tibor's response, the OS can schedule work on any available
> core when the SQL Server affinity mask is not set. You do not need to do
> anything special to make this happen.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> http://weblogs.sqlteam.com/dang/
> "Frank Osterberg" <raven7370@.yahoo.com> wrote in message
> news:uVFYNK6dIHA.748@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> I am currently using a single SQL Server Express Instance on a Core 2 Duo
>> box.
>> I know that the Express version is limited to use 1 CPU/Core.
>> I now want to setup a second SQL Server Express instance for unrelated
>> purposes.
>> Is it possible to give one instance Core 0 prio, and the Second the Core
>> 1 prio, or do they ALWAYS (MUST/ONLY) use Core 0?
>> Also, if I don't set it specifically will Windows manage it properly?
>> I guess my main question in detail is:
>> Is the Express version build in such a way that it will always use the
>> first core/cpu regardless how many there are? For example, if I have 3
>> instances running, would they all fight for cycles on the first core/cpu?
>> If not, do I have to do/set something to make it work properly?
>> Thanks for any Info. on this!
>> Regards,
>> Frank
>
Thursday, March 8, 2012
CPU usage
I have been using SQL Server 2000 for some time and am attempting to upgrade
to SQL Server 2005 (testing the trial version).
I backed up my SQL Server 2000 database(s) and then restored them on SQL
Server 2005 which worked well.
Everything seemed to be working fine until I updated SQL Server 2005 with
service pack 2.
I am running this on a Dual-Core AMD Opteron Processor 2216HE. SQL Server
is now using 25% CPU continuously (or 100% of one of the processor
instances). The databases I converted are working as expected, however, the
SQL Server process is continuously burning CPU when nothing is happening
(this is over several days now).
Has anybody else see this problem?
Ross,
I have seen this happen in the past, for many different reasons.
I assume from your comment that you know that it is sqlservr.exe that is
using the 25%. Can you run sp_who2 to identify some process that continues
to eat up CPU time? If so, use dbcc inputbuffer, fn_get_sql, or look at
sys.dm_exec_sql_text to see what is running. Also use DBCC OPENTRAN to find
the oldest transaction in each database. (However, I assume that there is
not a blocking transaction or you would be having other problems.)
If you identify a problem spid, try to get the hostname for that spid.
Sometimes a client computer loses connectivity, but the SQL Server does not
know that, so it keeps trying (forever) to send the result set to the
client. If that is the case, logging the hostname computer off of the
domain, then logging back into the domain, often alerts SQL Server to the
problem so that it will abandon that result set.
If appropriate, you can try to KILL the spid. (But the spid may not always
be killable.)
Other software can also get into a confused state and eat up CPU. For
example, a DTS package, backup software, etc. can also get into an confused
state and never stop running. Use Windows Task Manager to check whether
another task is actually the guilty party. If it is one of those, then try
killing that Windows process.
If you cannot do anything else, you should schedule a restart of your SQL
Sever. That should (naturally) clear the problem out. But you will need to
remain alert to its returning.
But, FWIW, I have not seen much of this on SQL Server 2005 SP2.
RLF
"Ross" <Ross@.discussions.microsoft.com> wrote in message
news:3BB4D4DE-BFA5-431D-971B-4CB63C4E9381@.microsoft.com...
>I have been using SQL Server 2000 for some time and am attempting to
>upgrade
> to SQL Server 2005 (testing the trial version).
> I backed up my SQL Server 2000 database(s) and then restored them on SQL
> Server 2005 which worked well.
> Everything seemed to be working fine until I updated SQL Server 2005 with
> service pack 2.
> I am running this on a Dual-Core AMD Opteron Processor 2216HE. SQL Server
> is now using 25% CPU continuously (or 100% of one of the processor
> instances). The databases I converted are working as expected, however,
> the
> SQL Server process is continuously burning CPU when nothing is happening
> (this is over several days now).
> Has anybody else see this problem?
>
|||Thanks for your answer.
It is definitely SQL Server that is eating 25% CPU (sqlservr.exe). I have
actually restarted SQL Server (and the machine itself) and subsequently the
SQL Server process goes right back up to 25%.
As you noted regarding the blocking transaction, I'm not experiencing any
problems with the database, just the CPU usage.
The sp_who2 shows a CPUTime for a "LAZY WRITER" process id 3 of 71195468 (as
it happens, the machine was restarted at 11:19PM EST last night).
Full row information from sp_who2:
SPID 3
Status BACKGROUND
Login sa
HostName .
BlkBy .
DBName NULL
Command LAZY WRITER
CPU TIME 71195468
DiskIO 0
LastBatch 03/12 23:19:34
ProgramName (blank)
SPID 3
REQUESTID 0
Any ideas?
Thanks again for your help...
Ross
"Russell Fields" wrote:
> Ross,
> I have seen this happen in the past, for many different reasons.
> I assume from your comment that you know that it is sqlservr.exe that is
> using the 25%. Can you run sp_who2 to identify some process that continues
> to eat up CPU time? If so, use dbcc inputbuffer, fn_get_sql, or look at
> sys.dm_exec_sql_text to see what is running. Also use DBCC OPENTRAN to find
> the oldest transaction in each database. (However, I assume that there is
> not a blocking transaction or you would be having other problems.)
> If you identify a problem spid, try to get the hostname for that spid.
> Sometimes a client computer loses connectivity, but the SQL Server does not
> know that, so it keeps trying (forever) to send the result set to the
> client. If that is the case, logging the hostname computer off of the
> domain, then logging back into the domain, often alerts SQL Server to the
> problem so that it will abandon that result set.
> If appropriate, you can try to KILL the spid. (But the spid may not always
> be killable.)
> Other software can also get into a confused state and eat up CPU. For
> example, a DTS package, backup software, etc. can also get into an confused
> state and never stop running. Use Windows Task Manager to check whether
> another task is actually the guilty party. If it is one of those, then try
> killing that Windows process.
> If you cannot do anything else, you should schedule a restart of your SQL
> Sever. That should (naturally) clear the problem out. But you will need to
> remain alert to its returning.
> But, FWIW, I have not seen much of this on SQL Server 2005 SP2.
> RLF
>
> "Ross" <Ross@.discussions.microsoft.com> wrote in message
> news:3BB4D4DE-BFA5-431D-971B-4CB63C4E9381@.microsoft.com...
>
>
|||Ross,
Check out: http://support.microsoft.com/kb/931821
RLF
"Ross" <Ross@.discussions.microsoft.com> wrote in message
news:51734426-C053-4F76-9801-137FFC3DCACB@.microsoft.com...[vbcol=seagreen]
> Thanks for your answer.
> It is definitely SQL Server that is eating 25% CPU (sqlservr.exe). I have
> actually restarted SQL Server (and the machine itself) and subsequently
> the
> SQL Server process goes right back up to 25%.
> As you noted regarding the blocking transaction, I'm not experiencing any
> problems with the database, just the CPU usage.
> The sp_who2 shows a CPUTime for a "LAZY WRITER" process id 3 of 71195468
> (as
> it happens, the machine was restarted at 11:19PM EST last night).
> Full row information from sp_who2:
> SPID 3
> Status BACKGROUND
> Login sa
> HostName .
> BlkBy .
> DBName NULL
> Command LAZY WRITER
> CPU TIME 71195468
> DiskIO 0
> LastBatch 03/12 23:19:34
> ProgramName (blank)
> SPID 3
> REQUESTID 0
> Any ideas?
> Thanks again for your help...
> Ross
>
> "Russell Fields" wrote:
|||Thank you very much.
I've requested the hotfix and will see what happens.
"Russell Fields" wrote:
> Ross,
> Check out: http://support.microsoft.com/kb/931821
> RLF
> "Ross" <Ross@.discussions.microsoft.com> wrote in message
> news:51734426-C053-4F76-9801-137FFC3DCACB@.microsoft.com...
>
>
|||Unfortunately, that didn't go very well.
I installed the hotfix which appeared to solve the CPU problem (sqlservr.exe
back at 0% when no activity). I was able to run the management software and
all looked fine, but when I attempted to run my web application, sql server
choked. A simple query returning a datareader (ASP.NET) caused SQLServer to
get a memory exception.
Needless to say, I removed the Hotfix and am now back where I started. I
guess I'll have to wait until Microsoft releases the hotfix in an update.
Ross
"Russell Fields" wrote:
> Ross,
> Check out: http://support.microsoft.com/kb/931821
> RLF
> "Ross" <Ross@.discussions.microsoft.com> wrote in message
> news:51734426-C053-4F76-9801-137FFC3DCACB@.microsoft.com...
>
>
|||Ross,
Too bad. Memory exceptions are bugs, so you could report it to Microsoft if
you want.
RLF
"Ross" <Ross@.discussions.microsoft.com> wrote in message
news:8498810D-A651-4F6C-9970-A6FD26FC8835@.microsoft.com...[vbcol=seagreen]
> Unfortunately, that didn't go very well.
> I installed the hotfix which appeared to solve the CPU problem
> (sqlservr.exe
> back at 0% when no activity). I was able to run the management software
> and
> all looked fine, but when I attempted to run my web application, sql
> server
> choked. A simple query returning a datareader (ASP.NET) caused SQLServer
> to
> get a memory exception.
> Needless to say, I removed the Hotfix and am now back where I started. I
> guess I'll have to wait until Microsoft releases the hotfix in an update.
> Ross
> "Russell Fields" wrote:
to SQL Server 2005 (testing the trial version).
I backed up my SQL Server 2000 database(s) and then restored them on SQL
Server 2005 which worked well.
Everything seemed to be working fine until I updated SQL Server 2005 with
service pack 2.
I am running this on a Dual-Core AMD Opteron Processor 2216HE. SQL Server
is now using 25% CPU continuously (or 100% of one of the processor
instances). The databases I converted are working as expected, however, the
SQL Server process is continuously burning CPU when nothing is happening
(this is over several days now).
Has anybody else see this problem?
Ross,
I have seen this happen in the past, for many different reasons.
I assume from your comment that you know that it is sqlservr.exe that is
using the 25%. Can you run sp_who2 to identify some process that continues
to eat up CPU time? If so, use dbcc inputbuffer, fn_get_sql, or look at
sys.dm_exec_sql_text to see what is running. Also use DBCC OPENTRAN to find
the oldest transaction in each database. (However, I assume that there is
not a blocking transaction or you would be having other problems.)
If you identify a problem spid, try to get the hostname for that spid.
Sometimes a client computer loses connectivity, but the SQL Server does not
know that, so it keeps trying (forever) to send the result set to the
client. If that is the case, logging the hostname computer off of the
domain, then logging back into the domain, often alerts SQL Server to the
problem so that it will abandon that result set.
If appropriate, you can try to KILL the spid. (But the spid may not always
be killable.)
Other software can also get into a confused state and eat up CPU. For
example, a DTS package, backup software, etc. can also get into an confused
state and never stop running. Use Windows Task Manager to check whether
another task is actually the guilty party. If it is one of those, then try
killing that Windows process.
If you cannot do anything else, you should schedule a restart of your SQL
Sever. That should (naturally) clear the problem out. But you will need to
remain alert to its returning.
But, FWIW, I have not seen much of this on SQL Server 2005 SP2.
RLF
"Ross" <Ross@.discussions.microsoft.com> wrote in message
news:3BB4D4DE-BFA5-431D-971B-4CB63C4E9381@.microsoft.com...
>I have been using SQL Server 2000 for some time and am attempting to
>upgrade
> to SQL Server 2005 (testing the trial version).
> I backed up my SQL Server 2000 database(s) and then restored them on SQL
> Server 2005 which worked well.
> Everything seemed to be working fine until I updated SQL Server 2005 with
> service pack 2.
> I am running this on a Dual-Core AMD Opteron Processor 2216HE. SQL Server
> is now using 25% CPU continuously (or 100% of one of the processor
> instances). The databases I converted are working as expected, however,
> the
> SQL Server process is continuously burning CPU when nothing is happening
> (this is over several days now).
> Has anybody else see this problem?
>
|||Thanks for your answer.
It is definitely SQL Server that is eating 25% CPU (sqlservr.exe). I have
actually restarted SQL Server (and the machine itself) and subsequently the
SQL Server process goes right back up to 25%.
As you noted regarding the blocking transaction, I'm not experiencing any
problems with the database, just the CPU usage.
The sp_who2 shows a CPUTime for a "LAZY WRITER" process id 3 of 71195468 (as
it happens, the machine was restarted at 11:19PM EST last night).
Full row information from sp_who2:
SPID 3
Status BACKGROUND
Login sa
HostName .
BlkBy .
DBName NULL
Command LAZY WRITER
CPU TIME 71195468
DiskIO 0
LastBatch 03/12 23:19:34
ProgramName (blank)
SPID 3
REQUESTID 0
Any ideas?
Thanks again for your help...
Ross
"Russell Fields" wrote:
> Ross,
> I have seen this happen in the past, for many different reasons.
> I assume from your comment that you know that it is sqlservr.exe that is
> using the 25%. Can you run sp_who2 to identify some process that continues
> to eat up CPU time? If so, use dbcc inputbuffer, fn_get_sql, or look at
> sys.dm_exec_sql_text to see what is running. Also use DBCC OPENTRAN to find
> the oldest transaction in each database. (However, I assume that there is
> not a blocking transaction or you would be having other problems.)
> If you identify a problem spid, try to get the hostname for that spid.
> Sometimes a client computer loses connectivity, but the SQL Server does not
> know that, so it keeps trying (forever) to send the result set to the
> client. If that is the case, logging the hostname computer off of the
> domain, then logging back into the domain, often alerts SQL Server to the
> problem so that it will abandon that result set.
> If appropriate, you can try to KILL the spid. (But the spid may not always
> be killable.)
> Other software can also get into a confused state and eat up CPU. For
> example, a DTS package, backup software, etc. can also get into an confused
> state and never stop running. Use Windows Task Manager to check whether
> another task is actually the guilty party. If it is one of those, then try
> killing that Windows process.
> If you cannot do anything else, you should schedule a restart of your SQL
> Sever. That should (naturally) clear the problem out. But you will need to
> remain alert to its returning.
> But, FWIW, I have not seen much of this on SQL Server 2005 SP2.
> RLF
>
> "Ross" <Ross@.discussions.microsoft.com> wrote in message
> news:3BB4D4DE-BFA5-431D-971B-4CB63C4E9381@.microsoft.com...
>
>
|||Ross,
Check out: http://support.microsoft.com/kb/931821
RLF
"Ross" <Ross@.discussions.microsoft.com> wrote in message
news:51734426-C053-4F76-9801-137FFC3DCACB@.microsoft.com...[vbcol=seagreen]
> Thanks for your answer.
> It is definitely SQL Server that is eating 25% CPU (sqlservr.exe). I have
> actually restarted SQL Server (and the machine itself) and subsequently
> the
> SQL Server process goes right back up to 25%.
> As you noted regarding the blocking transaction, I'm not experiencing any
> problems with the database, just the CPU usage.
> The sp_who2 shows a CPUTime for a "LAZY WRITER" process id 3 of 71195468
> (as
> it happens, the machine was restarted at 11:19PM EST last night).
> Full row information from sp_who2:
> SPID 3
> Status BACKGROUND
> Login sa
> HostName .
> BlkBy .
> DBName NULL
> Command LAZY WRITER
> CPU TIME 71195468
> DiskIO 0
> LastBatch 03/12 23:19:34
> ProgramName (blank)
> SPID 3
> REQUESTID 0
> Any ideas?
> Thanks again for your help...
> Ross
>
> "Russell Fields" wrote:
|||Thank you very much.
I've requested the hotfix and will see what happens.
"Russell Fields" wrote:
> Ross,
> Check out: http://support.microsoft.com/kb/931821
> RLF
> "Ross" <Ross@.discussions.microsoft.com> wrote in message
> news:51734426-C053-4F76-9801-137FFC3DCACB@.microsoft.com...
>
>
|||Unfortunately, that didn't go very well.
I installed the hotfix which appeared to solve the CPU problem (sqlservr.exe
back at 0% when no activity). I was able to run the management software and
all looked fine, but when I attempted to run my web application, sql server
choked. A simple query returning a datareader (ASP.NET) caused SQLServer to
get a memory exception.
Needless to say, I removed the Hotfix and am now back where I started. I
guess I'll have to wait until Microsoft releases the hotfix in an update.
Ross
"Russell Fields" wrote:
> Ross,
> Check out: http://support.microsoft.com/kb/931821
> RLF
> "Ross" <Ross@.discussions.microsoft.com> wrote in message
> news:51734426-C053-4F76-9801-137FFC3DCACB@.microsoft.com...
>
>
|||Ross,
Too bad. Memory exceptions are bugs, so you could report it to Microsoft if
you want.
RLF
"Ross" <Ross@.discussions.microsoft.com> wrote in message
news:8498810D-A651-4F6C-9970-A6FD26FC8835@.microsoft.com...[vbcol=seagreen]
> Unfortunately, that didn't go very well.
> I installed the hotfix which appeared to solve the CPU problem
> (sqlservr.exe
> back at 0% when no activity). I was able to run the management software
> and
> all looked fine, but when I attempted to run my web application, sql
> server
> choked. A simple query returning a datareader (ASP.NET) caused SQLServer
> to
> get a memory exception.
> Needless to say, I removed the Hotfix and am now back where I started. I
> guess I'll have to wait until Microsoft releases the hotfix in an update.
> Ross
> "Russell Fields" wrote:
Wednesday, March 7, 2012
CPU usage
I have been using SQL Server 2000 for some time and am attempting to upgrade
to SQL Server 2005 (testing the trial version).
I backed up my SQL Server 2000 database(s) and then restored them on SQL
Server 2005 which worked well.
Everything seemed to be working fine until I updated SQL Server 2005 with
service pack 2.
I am running this on a Dual-Core AMD Opteron Processor 2216HE. SQL Server
is now using 25% CPU continuously (or 100% of one of the processor
instances). The databases I converted are working as expected, however, the
SQL Server process is continuously burning CPU when nothing is happening
(this is over several days now).
Has anybody else see this problem?Ross,
I have seen this happen in the past, for many different reasons.
I assume from your comment that you know that it is sqlservr.exe that is
using the 25%. Can you run sp_who2 to identify some process that continues
to eat up CPU time? If so, use dbcc inputbuffer, fn_get_sql, or look at
sys.dm_exec_sql_text to see what is running. Also use DBCC OPENTRAN to find
the oldest transaction in each database. (However, I assume that there is
not a blocking transaction or you would be having other problems.)
If you identify a problem spid, try to get the hostname for that spid.
Sometimes a client computer loses connectivity, but the SQL Server does not
know that, so it keeps trying (forever) to send the result set to the
client. If that is the case, logging the hostname computer off of the
domain, then logging back into the domain, often alerts SQL Server to the
problem so that it will abandon that result set.
If appropriate, you can try to KILL the spid. (But the spid may not always
be killable.)
Other software can also get into a confused state and eat up CPU. For
example, a DTS package, backup software, etc. can also get into an confused
state and never stop running. Use Windows Task Manager to check whether
another task is actually the guilty party. If it is one of those, then try
killing that Windows process.
If you cannot do anything else, you should schedule a restart of your SQL
Sever. That should (naturally) clear the problem out. But you will need to
remain alert to its returning.
But, FWIW, I have not seen much of this on SQL Server 2005 SP2.
RLF
"Ross" <Ross@.discussions.microsoft.com> wrote in message
news:3BB4D4DE-BFA5-431D-971B-4CB63C4E9381@.microsoft.com...
>I have been using SQL Server 2000 for some time and am attempting to
>upgrade
> to SQL Server 2005 (testing the trial version).
> I backed up my SQL Server 2000 database(s) and then restored them on SQL
> Server 2005 which worked well.
> Everything seemed to be working fine until I updated SQL Server 2005 with
> service pack 2.
> I am running this on a Dual-Core AMD Opteron Processor 2216HE. SQL Server
> is now using 25% CPU continuously (or 100% of one of the processor
> instances). The databases I converted are working as expected, however,
> the
> SQL Server process is continuously burning CPU when nothing is happening
> (this is over several days now).
> Has anybody else see this problem?
>|||Thanks for your answer.
It is definitely SQL Server that is eating 25% CPU (sqlservr.exe). I have
actually restarted SQL Server (and the machine itself) and subsequently the
SQL Server process goes right back up to 25%.
As you noted regarding the blocking transaction, I'm not experiencing any
problems with the database, just the CPU usage.
The sp_who2 shows a CPUTime for a "LAZY WRITER" process id 3 of 71195468 (as
it happens, the machine was restarted at 11:19PM EST last night).
Full row information from sp_who2:
SPID 3
Status BACKGROUND
Login sa
HostName .
BlkBy .
DBName NULL
Command LAZY WRITER
CPU TIME 71195468
DiskIO 0
LastBatch 03/12 23:19:34
ProgramName (blank)
SPID 3
REQUESTID 0
Any ideas?
Thanks again for your help...
Ross
"Russell Fields" wrote:
> Ross,
> I have seen this happen in the past, for many different reasons.
> I assume from your comment that you know that it is sqlservr.exe that is
> using the 25%. Can you run sp_who2 to identify some process that continues
> to eat up CPU time? If so, use dbcc inputbuffer, fn_get_sql, or look at
> sys.dm_exec_sql_text to see what is running. Also use DBCC OPENTRAN to find
> the oldest transaction in each database. (However, I assume that there is
> not a blocking transaction or you would be having other problems.)
> If you identify a problem spid, try to get the hostname for that spid.
> Sometimes a client computer loses connectivity, but the SQL Server does not
> know that, so it keeps trying (forever) to send the result set to the
> client. If that is the case, logging the hostname computer off of the
> domain, then logging back into the domain, often alerts SQL Server to the
> problem so that it will abandon that result set.
> If appropriate, you can try to KILL the spid. (But the spid may not always
> be killable.)
> Other software can also get into a confused state and eat up CPU. For
> example, a DTS package, backup software, etc. can also get into an confused
> state and never stop running. Use Windows Task Manager to check whether
> another task is actually the guilty party. If it is one of those, then try
> killing that Windows process.
> If you cannot do anything else, you should schedule a restart of your SQL
> Sever. That should (naturally) clear the problem out. But you will need to
> remain alert to its returning.
> But, FWIW, I have not seen much of this on SQL Server 2005 SP2.
> RLF
>
> "Ross" <Ross@.discussions.microsoft.com> wrote in message
> news:3BB4D4DE-BFA5-431D-971B-4CB63C4E9381@.microsoft.com...
> >I have been using SQL Server 2000 for some time and am attempting to
> >upgrade
> > to SQL Server 2005 (testing the trial version).
> >
> > I backed up my SQL Server 2000 database(s) and then restored them on SQL
> > Server 2005 which worked well.
> >
> > Everything seemed to be working fine until I updated SQL Server 2005 with
> > service pack 2.
> >
> > I am running this on a Dual-Core AMD Opteron Processor 2216HE. SQL Server
> > is now using 25% CPU continuously (or 100% of one of the processor
> > instances). The databases I converted are working as expected, however,
> > the
> > SQL Server process is continuously burning CPU when nothing is happening
> > (this is over several days now).
> >
> > Has anybody else see this problem?
> >
> >
>
>|||Ross,
Check out: http://support.microsoft.com/kb/931821
RLF
"Ross" <Ross@.discussions.microsoft.com> wrote in message
news:51734426-C053-4F76-9801-137FFC3DCACB@.microsoft.com...
> Thanks for your answer.
> It is definitely SQL Server that is eating 25% CPU (sqlservr.exe). I have
> actually restarted SQL Server (and the machine itself) and subsequently
> the
> SQL Server process goes right back up to 25%.
> As you noted regarding the blocking transaction, I'm not experiencing any
> problems with the database, just the CPU usage.
> The sp_who2 shows a CPUTime for a "LAZY WRITER" process id 3 of 71195468
> (as
> it happens, the machine was restarted at 11:19PM EST last night).
> Full row information from sp_who2:
> SPID 3
> Status BACKGROUND
> Login sa
> HostName .
> BlkBy .
> DBName NULL
> Command LAZY WRITER
> CPU TIME 71195468
> DiskIO 0
> LastBatch 03/12 23:19:34
> ProgramName (blank)
> SPID 3
> REQUESTID 0
> Any ideas?
> Thanks again for your help...
> Ross
>
> "Russell Fields" wrote:
>> Ross,
>> I have seen this happen in the past, for many different reasons.
>> I assume from your comment that you know that it is sqlservr.exe that is
>> using the 25%. Can you run sp_who2 to identify some process that
>> continues
>> to eat up CPU time? If so, use dbcc inputbuffer, fn_get_sql, or look at
>> sys.dm_exec_sql_text to see what is running. Also use DBCC OPENTRAN to
>> find
>> the oldest transaction in each database. (However, I assume that there
>> is
>> not a blocking transaction or you would be having other problems.)
>> If you identify a problem spid, try to get the hostname for that spid.
>> Sometimes a client computer loses connectivity, but the SQL Server does
>> not
>> know that, so it keeps trying (forever) to send the result set to the
>> client. If that is the case, logging the hostname computer off of the
>> domain, then logging back into the domain, often alerts SQL Server to the
>> problem so that it will abandon that result set.
>> If appropriate, you can try to KILL the spid. (But the spid may not
>> always
>> be killable.)
>> Other software can also get into a confused state and eat up CPU. For
>> example, a DTS package, backup software, etc. can also get into an
>> confused
>> state and never stop running. Use Windows Task Manager to check whether
>> another task is actually the guilty party. If it is one of those, then
>> try
>> killing that Windows process.
>> If you cannot do anything else, you should schedule a restart of your SQL
>> Sever. That should (naturally) clear the problem out. But you will need
>> to
>> remain alert to its returning.
>> But, FWIW, I have not seen much of this on SQL Server 2005 SP2.
>> RLF
>>
>> "Ross" <Ross@.discussions.microsoft.com> wrote in message
>> news:3BB4D4DE-BFA5-431D-971B-4CB63C4E9381@.microsoft.com...
>> >I have been using SQL Server 2000 for some time and am attempting to
>> >upgrade
>> > to SQL Server 2005 (testing the trial version).
>> >
>> > I backed up my SQL Server 2000 database(s) and then restored them on
>> > SQL
>> > Server 2005 which worked well.
>> >
>> > Everything seemed to be working fine until I updated SQL Server 2005
>> > with
>> > service pack 2.
>> >
>> > I am running this on a Dual-Core AMD Opteron Processor 2216HE. SQL
>> > Server
>> > is now using 25% CPU continuously (or 100% of one of the processor
>> > instances). The databases I converted are working as expected,
>> > however,
>> > the
>> > SQL Server process is continuously burning CPU when nothing is
>> > happening
>> > (this is over several days now).
>> >
>> > Has anybody else see this problem?
>> >
>> >
>>|||Thank you very much.
I've requested the hotfix and will see what happens.
"Russell Fields" wrote:
> Ross,
> Check out: http://support.microsoft.com/kb/931821
> RLF
> "Ross" <Ross@.discussions.microsoft.com> wrote in message
> news:51734426-C053-4F76-9801-137FFC3DCACB@.microsoft.com...
> > Thanks for your answer.
> >
> > It is definitely SQL Server that is eating 25% CPU (sqlservr.exe). I have
> > actually restarted SQL Server (and the machine itself) and subsequently
> > the
> > SQL Server process goes right back up to 25%.
> >
> > As you noted regarding the blocking transaction, I'm not experiencing any
> > problems with the database, just the CPU usage.
> >
> > The sp_who2 shows a CPUTime for a "LAZY WRITER" process id 3 of 71195468
> > (as
> > it happens, the machine was restarted at 11:19PM EST last night).
> >
> > Full row information from sp_who2:
> >
> > SPID 3
> > Status BACKGROUND
> > Login sa
> > HostName .
> > BlkBy .
> > DBName NULL
> > Command LAZY WRITER
> > CPU TIME 71195468
> > DiskIO 0
> > LastBatch 03/12 23:19:34
> > ProgramName (blank)
> > SPID 3
> > REQUESTID 0
> >
> > Any ideas?
> >
> > Thanks again for your help...
> >
> > Ross
> >
> >
> >
> > "Russell Fields" wrote:
> >
> >> Ross,
> >>
> >> I have seen this happen in the past, for many different reasons.
> >>
> >> I assume from your comment that you know that it is sqlservr.exe that is
> >> using the 25%. Can you run sp_who2 to identify some process that
> >> continues
> >> to eat up CPU time? If so, use dbcc inputbuffer, fn_get_sql, or look at
> >> sys.dm_exec_sql_text to see what is running. Also use DBCC OPENTRAN to
> >> find
> >> the oldest transaction in each database. (However, I assume that there
> >> is
> >> not a blocking transaction or you would be having other problems.)
> >>
> >> If you identify a problem spid, try to get the hostname for that spid.
> >> Sometimes a client computer loses connectivity, but the SQL Server does
> >> not
> >> know that, so it keeps trying (forever) to send the result set to the
> >> client. If that is the case, logging the hostname computer off of the
> >> domain, then logging back into the domain, often alerts SQL Server to the
> >> problem so that it will abandon that result set.
> >>
> >> If appropriate, you can try to KILL the spid. (But the spid may not
> >> always
> >> be killable.)
> >>
> >> Other software can also get into a confused state and eat up CPU. For
> >> example, a DTS package, backup software, etc. can also get into an
> >> confused
> >> state and never stop running. Use Windows Task Manager to check whether
> >> another task is actually the guilty party. If it is one of those, then
> >> try
> >> killing that Windows process.
> >>
> >> If you cannot do anything else, you should schedule a restart of your SQL
> >> Sever. That should (naturally) clear the problem out. But you will need
> >> to
> >> remain alert to its returning.
> >>
> >> But, FWIW, I have not seen much of this on SQL Server 2005 SP2.
> >>
> >> RLF
> >>
> >>
> >> "Ross" <Ross@.discussions.microsoft.com> wrote in message
> >> news:3BB4D4DE-BFA5-431D-971B-4CB63C4E9381@.microsoft.com...
> >> >I have been using SQL Server 2000 for some time and am attempting to
> >> >upgrade
> >> > to SQL Server 2005 (testing the trial version).
> >> >
> >> > I backed up my SQL Server 2000 database(s) and then restored them on
> >> > SQL
> >> > Server 2005 which worked well.
> >> >
> >> > Everything seemed to be working fine until I updated SQL Server 2005
> >> > with
> >> > service pack 2.
> >> >
> >> > I am running this on a Dual-Core AMD Opteron Processor 2216HE. SQL
> >> > Server
> >> > is now using 25% CPU continuously (or 100% of one of the processor
> >> > instances). The databases I converted are working as expected,
> >> > however,
> >> > the
> >> > SQL Server process is continuously burning CPU when nothing is
> >> > happening
> >> > (this is over several days now).
> >> >
> >> > Has anybody else see this problem?
> >> >
> >> >
> >>
> >>
> >>
>
>|||Unfortunately, that didn't go very well.
I installed the hotfix which appeared to solve the CPU problem (sqlservr.exe
back at 0% when no activity). I was able to run the management software and
all looked fine, but when I attempted to run my web application, sql server
choked. A simple query returning a datareader (ASP.NET) caused SQLServer to
get a memory exception.
Needless to say, I removed the Hotfix and am now back where I started. I
guess I'll have to wait until Microsoft releases the hotfix in an update.
Ross
"Russell Fields" wrote:
> Ross,
> Check out: http://support.microsoft.com/kb/931821
> RLF
> "Ross" <Ross@.discussions.microsoft.com> wrote in message
> news:51734426-C053-4F76-9801-137FFC3DCACB@.microsoft.com...
> > Thanks for your answer.
> >
> > It is definitely SQL Server that is eating 25% CPU (sqlservr.exe). I have
> > actually restarted SQL Server (and the machine itself) and subsequently
> > the
> > SQL Server process goes right back up to 25%.
> >
> > As you noted regarding the blocking transaction, I'm not experiencing any
> > problems with the database, just the CPU usage.
> >
> > The sp_who2 shows a CPUTime for a "LAZY WRITER" process id 3 of 71195468
> > (as
> > it happens, the machine was restarted at 11:19PM EST last night).
> >
> > Full row information from sp_who2:
> >
> > SPID 3
> > Status BACKGROUND
> > Login sa
> > HostName .
> > BlkBy .
> > DBName NULL
> > Command LAZY WRITER
> > CPU TIME 71195468
> > DiskIO 0
> > LastBatch 03/12 23:19:34
> > ProgramName (blank)
> > SPID 3
> > REQUESTID 0
> >
> > Any ideas?
> >
> > Thanks again for your help...
> >
> > Ross
> >
> >
> >
> > "Russell Fields" wrote:
> >
> >> Ross,
> >>
> >> I have seen this happen in the past, for many different reasons.
> >>
> >> I assume from your comment that you know that it is sqlservr.exe that is
> >> using the 25%. Can you run sp_who2 to identify some process that
> >> continues
> >> to eat up CPU time? If so, use dbcc inputbuffer, fn_get_sql, or look at
> >> sys.dm_exec_sql_text to see what is running. Also use DBCC OPENTRAN to
> >> find
> >> the oldest transaction in each database. (However, I assume that there
> >> is
> >> not a blocking transaction or you would be having other problems.)
> >>
> >> If you identify a problem spid, try to get the hostname for that spid.
> >> Sometimes a client computer loses connectivity, but the SQL Server does
> >> not
> >> know that, so it keeps trying (forever) to send the result set to the
> >> client. If that is the case, logging the hostname computer off of the
> >> domain, then logging back into the domain, often alerts SQL Server to the
> >> problem so that it will abandon that result set.
> >>
> >> If appropriate, you can try to KILL the spid. (But the spid may not
> >> always
> >> be killable.)
> >>
> >> Other software can also get into a confused state and eat up CPU. For
> >> example, a DTS package, backup software, etc. can also get into an
> >> confused
> >> state and never stop running. Use Windows Task Manager to check whether
> >> another task is actually the guilty party. If it is one of those, then
> >> try
> >> killing that Windows process.
> >>
> >> If you cannot do anything else, you should schedule a restart of your SQL
> >> Sever. That should (naturally) clear the problem out. But you will need
> >> to
> >> remain alert to its returning.
> >>
> >> But, FWIW, I have not seen much of this on SQL Server 2005 SP2.
> >>
> >> RLF
> >>
> >>
> >> "Ross" <Ross@.discussions.microsoft.com> wrote in message
> >> news:3BB4D4DE-BFA5-431D-971B-4CB63C4E9381@.microsoft.com...
> >> >I have been using SQL Server 2000 for some time and am attempting to
> >> >upgrade
> >> > to SQL Server 2005 (testing the trial version).
> >> >
> >> > I backed up my SQL Server 2000 database(s) and then restored them on
> >> > SQL
> >> > Server 2005 which worked well.
> >> >
> >> > Everything seemed to be working fine until I updated SQL Server 2005
> >> > with
> >> > service pack 2.
> >> >
> >> > I am running this on a Dual-Core AMD Opteron Processor 2216HE. SQL
> >> > Server
> >> > is now using 25% CPU continuously (or 100% of one of the processor
> >> > instances). The databases I converted are working as expected,
> >> > however,
> >> > the
> >> > SQL Server process is continuously burning CPU when nothing is
> >> > happening
> >> > (this is over several days now).
> >> >
> >> > Has anybody else see this problem?
> >> >
> >> >
> >>
> >>
> >>
>
>|||Ross,
Too bad. Memory exceptions are bugs, so you could report it to Microsoft if
you want.
RLF
"Ross" <Ross@.discussions.microsoft.com> wrote in message
news:8498810D-A651-4F6C-9970-A6FD26FC8835@.microsoft.com...
> Unfortunately, that didn't go very well.
> I installed the hotfix which appeared to solve the CPU problem
> (sqlservr.exe
> back at 0% when no activity). I was able to run the management software
> and
> all looked fine, but when I attempted to run my web application, sql
> server
> choked. A simple query returning a datareader (ASP.NET) caused SQLServer
> to
> get a memory exception.
> Needless to say, I removed the Hotfix and am now back where I started. I
> guess I'll have to wait until Microsoft releases the hotfix in an update.
> Ross
> "Russell Fields" wrote:
>> Ross,
>> Check out: http://support.microsoft.com/kb/931821
>> RLF
>> "Ross" <Ross@.discussions.microsoft.com> wrote in message
>> news:51734426-C053-4F76-9801-137FFC3DCACB@.microsoft.com...
>> > Thanks for your answer.
>> >
>> > It is definitely SQL Server that is eating 25% CPU (sqlservr.exe). I
>> > have
>> > actually restarted SQL Server (and the machine itself) and subsequently
>> > the
>> > SQL Server process goes right back up to 25%.
>> >
>> > As you noted regarding the blocking transaction, I'm not experiencing
>> > any
>> > problems with the database, just the CPU usage.
>> >
>> > The sp_who2 shows a CPUTime for a "LAZY WRITER" process id 3 of
>> > 71195468
>> > (as
>> > it happens, the machine was restarted at 11:19PM EST last night).
>> >
>> > Full row information from sp_who2:
>> >
>> > SPID 3
>> > Status BACKGROUND
>> > Login sa
>> > HostName .
>> > BlkBy .
>> > DBName NULL
>> > Command LAZY WRITER
>> > CPU TIME 71195468
>> > DiskIO 0
>> > LastBatch 03/12 23:19:34
>> > ProgramName (blank)
>> > SPID 3
>> > REQUESTID 0
>> >
>> > Any ideas?
>> >
>> > Thanks again for your help...
>> >
>> > Ross
>> >
>> >
>> >
>> > "Russell Fields" wrote:
>> >
>> >> Ross,
>> >>
>> >> I have seen this happen in the past, for many different reasons.
>> >>
>> >> I assume from your comment that you know that it is sqlservr.exe that
>> >> is
>> >> using the 25%. Can you run sp_who2 to identify some process that
>> >> continues
>> >> to eat up CPU time? If so, use dbcc inputbuffer, fn_get_sql, or look
>> >> at
>> >> sys.dm_exec_sql_text to see what is running. Also use DBCC OPENTRAN
>> >> to
>> >> find
>> >> the oldest transaction in each database. (However, I assume that
>> >> there
>> >> is
>> >> not a blocking transaction or you would be having other problems.)
>> >>
>> >> If you identify a problem spid, try to get the hostname for that spid.
>> >> Sometimes a client computer loses connectivity, but the SQL Server
>> >> does
>> >> not
>> >> know that, so it keeps trying (forever) to send the result set to the
>> >> client. If that is the case, logging the hostname computer off of
>> >> the
>> >> domain, then logging back into the domain, often alerts SQL Server to
>> >> the
>> >> problem so that it will abandon that result set.
>> >>
>> >> If appropriate, you can try to KILL the spid. (But the spid may not
>> >> always
>> >> be killable.)
>> >>
>> >> Other software can also get into a confused state and eat up CPU. For
>> >> example, a DTS package, backup software, etc. can also get into an
>> >> confused
>> >> state and never stop running. Use Windows Task Manager to check
>> >> whether
>> >> another task is actually the guilty party. If it is one of those, then
>> >> try
>> >> killing that Windows process.
>> >>
>> >> If you cannot do anything else, you should schedule a restart of your
>> >> SQL
>> >> Sever. That should (naturally) clear the problem out. But you will
>> >> need
>> >> to
>> >> remain alert to its returning.
>> >>
>> >> But, FWIW, I have not seen much of this on SQL Server 2005 SP2.
>> >>
>> >> RLF
>> >>
>> >>
>> >> "Ross" <Ross@.discussions.microsoft.com> wrote in message
>> >> news:3BB4D4DE-BFA5-431D-971B-4CB63C4E9381@.microsoft.com...
>> >> >I have been using SQL Server 2000 for some time and am attempting to
>> >> >upgrade
>> >> > to SQL Server 2005 (testing the trial version).
>> >> >
>> >> > I backed up my SQL Server 2000 database(s) and then restored them on
>> >> > SQL
>> >> > Server 2005 which worked well.
>> >> >
>> >> > Everything seemed to be working fine until I updated SQL Server 2005
>> >> > with
>> >> > service pack 2.
>> >> >
>> >> > I am running this on a Dual-Core AMD Opteron Processor 2216HE. SQL
>> >> > Server
>> >> > is now using 25% CPU continuously (or 100% of one of the processor
>> >> > instances). The databases I converted are working as expected,
>> >> > however,
>> >> > the
>> >> > SQL Server process is continuously burning CPU when nothing is
>> >> > happening
>> >> > (this is over several days now).
>> >> >
>> >> > Has anybody else see this problem?
>> >> >
>> >> >
>> >>
>> >>
>> >>
>>
to SQL Server 2005 (testing the trial version).
I backed up my SQL Server 2000 database(s) and then restored them on SQL
Server 2005 which worked well.
Everything seemed to be working fine until I updated SQL Server 2005 with
service pack 2.
I am running this on a Dual-Core AMD Opteron Processor 2216HE. SQL Server
is now using 25% CPU continuously (or 100% of one of the processor
instances). The databases I converted are working as expected, however, the
SQL Server process is continuously burning CPU when nothing is happening
(this is over several days now).
Has anybody else see this problem?Ross,
I have seen this happen in the past, for many different reasons.
I assume from your comment that you know that it is sqlservr.exe that is
using the 25%. Can you run sp_who2 to identify some process that continues
to eat up CPU time? If so, use dbcc inputbuffer, fn_get_sql, or look at
sys.dm_exec_sql_text to see what is running. Also use DBCC OPENTRAN to find
the oldest transaction in each database. (However, I assume that there is
not a blocking transaction or you would be having other problems.)
If you identify a problem spid, try to get the hostname for that spid.
Sometimes a client computer loses connectivity, but the SQL Server does not
know that, so it keeps trying (forever) to send the result set to the
client. If that is the case, logging the hostname computer off of the
domain, then logging back into the domain, often alerts SQL Server to the
problem so that it will abandon that result set.
If appropriate, you can try to KILL the spid. (But the spid may not always
be killable.)
Other software can also get into a confused state and eat up CPU. For
example, a DTS package, backup software, etc. can also get into an confused
state and never stop running. Use Windows Task Manager to check whether
another task is actually the guilty party. If it is one of those, then try
killing that Windows process.
If you cannot do anything else, you should schedule a restart of your SQL
Sever. That should (naturally) clear the problem out. But you will need to
remain alert to its returning.
But, FWIW, I have not seen much of this on SQL Server 2005 SP2.
RLF
"Ross" <Ross@.discussions.microsoft.com> wrote in message
news:3BB4D4DE-BFA5-431D-971B-4CB63C4E9381@.microsoft.com...
>I have been using SQL Server 2000 for some time and am attempting to
>upgrade
> to SQL Server 2005 (testing the trial version).
> I backed up my SQL Server 2000 database(s) and then restored them on SQL
> Server 2005 which worked well.
> Everything seemed to be working fine until I updated SQL Server 2005 with
> service pack 2.
> I am running this on a Dual-Core AMD Opteron Processor 2216HE. SQL Server
> is now using 25% CPU continuously (or 100% of one of the processor
> instances). The databases I converted are working as expected, however,
> the
> SQL Server process is continuously burning CPU when nothing is happening
> (this is over several days now).
> Has anybody else see this problem?
>|||Thanks for your answer.
It is definitely SQL Server that is eating 25% CPU (sqlservr.exe). I have
actually restarted SQL Server (and the machine itself) and subsequently the
SQL Server process goes right back up to 25%.
As you noted regarding the blocking transaction, I'm not experiencing any
problems with the database, just the CPU usage.
The sp_who2 shows a CPUTime for a "LAZY WRITER" process id 3 of 71195468 (as
it happens, the machine was restarted at 11:19PM EST last night).
Full row information from sp_who2:
SPID 3
Status BACKGROUND
Login sa
HostName .
BlkBy .
DBName NULL
Command LAZY WRITER
CPU TIME 71195468
DiskIO 0
LastBatch 03/12 23:19:34
ProgramName (blank)
SPID 3
REQUESTID 0
Any ideas?
Thanks again for your help...
Ross
"Russell Fields" wrote:
> Ross,
> I have seen this happen in the past, for many different reasons.
> I assume from your comment that you know that it is sqlservr.exe that is
> using the 25%. Can you run sp_who2 to identify some process that continues
> to eat up CPU time? If so, use dbcc inputbuffer, fn_get_sql, or look at
> sys.dm_exec_sql_text to see what is running. Also use DBCC OPENTRAN to find
> the oldest transaction in each database. (However, I assume that there is
> not a blocking transaction or you would be having other problems.)
> If you identify a problem spid, try to get the hostname for that spid.
> Sometimes a client computer loses connectivity, but the SQL Server does not
> know that, so it keeps trying (forever) to send the result set to the
> client. If that is the case, logging the hostname computer off of the
> domain, then logging back into the domain, often alerts SQL Server to the
> problem so that it will abandon that result set.
> If appropriate, you can try to KILL the spid. (But the spid may not always
> be killable.)
> Other software can also get into a confused state and eat up CPU. For
> example, a DTS package, backup software, etc. can also get into an confused
> state and never stop running. Use Windows Task Manager to check whether
> another task is actually the guilty party. If it is one of those, then try
> killing that Windows process.
> If you cannot do anything else, you should schedule a restart of your SQL
> Sever. That should (naturally) clear the problem out. But you will need to
> remain alert to its returning.
> But, FWIW, I have not seen much of this on SQL Server 2005 SP2.
> RLF
>
> "Ross" <Ross@.discussions.microsoft.com> wrote in message
> news:3BB4D4DE-BFA5-431D-971B-4CB63C4E9381@.microsoft.com...
> >I have been using SQL Server 2000 for some time and am attempting to
> >upgrade
> > to SQL Server 2005 (testing the trial version).
> >
> > I backed up my SQL Server 2000 database(s) and then restored them on SQL
> > Server 2005 which worked well.
> >
> > Everything seemed to be working fine until I updated SQL Server 2005 with
> > service pack 2.
> >
> > I am running this on a Dual-Core AMD Opteron Processor 2216HE. SQL Server
> > is now using 25% CPU continuously (or 100% of one of the processor
> > instances). The databases I converted are working as expected, however,
> > the
> > SQL Server process is continuously burning CPU when nothing is happening
> > (this is over several days now).
> >
> > Has anybody else see this problem?
> >
> >
>
>|||Ross,
Check out: http://support.microsoft.com/kb/931821
RLF
"Ross" <Ross@.discussions.microsoft.com> wrote in message
news:51734426-C053-4F76-9801-137FFC3DCACB@.microsoft.com...
> Thanks for your answer.
> It is definitely SQL Server that is eating 25% CPU (sqlservr.exe). I have
> actually restarted SQL Server (and the machine itself) and subsequently
> the
> SQL Server process goes right back up to 25%.
> As you noted regarding the blocking transaction, I'm not experiencing any
> problems with the database, just the CPU usage.
> The sp_who2 shows a CPUTime for a "LAZY WRITER" process id 3 of 71195468
> (as
> it happens, the machine was restarted at 11:19PM EST last night).
> Full row information from sp_who2:
> SPID 3
> Status BACKGROUND
> Login sa
> HostName .
> BlkBy .
> DBName NULL
> Command LAZY WRITER
> CPU TIME 71195468
> DiskIO 0
> LastBatch 03/12 23:19:34
> ProgramName (blank)
> SPID 3
> REQUESTID 0
> Any ideas?
> Thanks again for your help...
> Ross
>
> "Russell Fields" wrote:
>> Ross,
>> I have seen this happen in the past, for many different reasons.
>> I assume from your comment that you know that it is sqlservr.exe that is
>> using the 25%. Can you run sp_who2 to identify some process that
>> continues
>> to eat up CPU time? If so, use dbcc inputbuffer, fn_get_sql, or look at
>> sys.dm_exec_sql_text to see what is running. Also use DBCC OPENTRAN to
>> find
>> the oldest transaction in each database. (However, I assume that there
>> is
>> not a blocking transaction or you would be having other problems.)
>> If you identify a problem spid, try to get the hostname for that spid.
>> Sometimes a client computer loses connectivity, but the SQL Server does
>> not
>> know that, so it keeps trying (forever) to send the result set to the
>> client. If that is the case, logging the hostname computer off of the
>> domain, then logging back into the domain, often alerts SQL Server to the
>> problem so that it will abandon that result set.
>> If appropriate, you can try to KILL the spid. (But the spid may not
>> always
>> be killable.)
>> Other software can also get into a confused state and eat up CPU. For
>> example, a DTS package, backup software, etc. can also get into an
>> confused
>> state and never stop running. Use Windows Task Manager to check whether
>> another task is actually the guilty party. If it is one of those, then
>> try
>> killing that Windows process.
>> If you cannot do anything else, you should schedule a restart of your SQL
>> Sever. That should (naturally) clear the problem out. But you will need
>> to
>> remain alert to its returning.
>> But, FWIW, I have not seen much of this on SQL Server 2005 SP2.
>> RLF
>>
>> "Ross" <Ross@.discussions.microsoft.com> wrote in message
>> news:3BB4D4DE-BFA5-431D-971B-4CB63C4E9381@.microsoft.com...
>> >I have been using SQL Server 2000 for some time and am attempting to
>> >upgrade
>> > to SQL Server 2005 (testing the trial version).
>> >
>> > I backed up my SQL Server 2000 database(s) and then restored them on
>> > SQL
>> > Server 2005 which worked well.
>> >
>> > Everything seemed to be working fine until I updated SQL Server 2005
>> > with
>> > service pack 2.
>> >
>> > I am running this on a Dual-Core AMD Opteron Processor 2216HE. SQL
>> > Server
>> > is now using 25% CPU continuously (or 100% of one of the processor
>> > instances). The databases I converted are working as expected,
>> > however,
>> > the
>> > SQL Server process is continuously burning CPU when nothing is
>> > happening
>> > (this is over several days now).
>> >
>> > Has anybody else see this problem?
>> >
>> >
>>|||Thank you very much.
I've requested the hotfix and will see what happens.
"Russell Fields" wrote:
> Ross,
> Check out: http://support.microsoft.com/kb/931821
> RLF
> "Ross" <Ross@.discussions.microsoft.com> wrote in message
> news:51734426-C053-4F76-9801-137FFC3DCACB@.microsoft.com...
> > Thanks for your answer.
> >
> > It is definitely SQL Server that is eating 25% CPU (sqlservr.exe). I have
> > actually restarted SQL Server (and the machine itself) and subsequently
> > the
> > SQL Server process goes right back up to 25%.
> >
> > As you noted regarding the blocking transaction, I'm not experiencing any
> > problems with the database, just the CPU usage.
> >
> > The sp_who2 shows a CPUTime for a "LAZY WRITER" process id 3 of 71195468
> > (as
> > it happens, the machine was restarted at 11:19PM EST last night).
> >
> > Full row information from sp_who2:
> >
> > SPID 3
> > Status BACKGROUND
> > Login sa
> > HostName .
> > BlkBy .
> > DBName NULL
> > Command LAZY WRITER
> > CPU TIME 71195468
> > DiskIO 0
> > LastBatch 03/12 23:19:34
> > ProgramName (blank)
> > SPID 3
> > REQUESTID 0
> >
> > Any ideas?
> >
> > Thanks again for your help...
> >
> > Ross
> >
> >
> >
> > "Russell Fields" wrote:
> >
> >> Ross,
> >>
> >> I have seen this happen in the past, for many different reasons.
> >>
> >> I assume from your comment that you know that it is sqlservr.exe that is
> >> using the 25%. Can you run sp_who2 to identify some process that
> >> continues
> >> to eat up CPU time? If so, use dbcc inputbuffer, fn_get_sql, or look at
> >> sys.dm_exec_sql_text to see what is running. Also use DBCC OPENTRAN to
> >> find
> >> the oldest transaction in each database. (However, I assume that there
> >> is
> >> not a blocking transaction or you would be having other problems.)
> >>
> >> If you identify a problem spid, try to get the hostname for that spid.
> >> Sometimes a client computer loses connectivity, but the SQL Server does
> >> not
> >> know that, so it keeps trying (forever) to send the result set to the
> >> client. If that is the case, logging the hostname computer off of the
> >> domain, then logging back into the domain, often alerts SQL Server to the
> >> problem so that it will abandon that result set.
> >>
> >> If appropriate, you can try to KILL the spid. (But the spid may not
> >> always
> >> be killable.)
> >>
> >> Other software can also get into a confused state and eat up CPU. For
> >> example, a DTS package, backup software, etc. can also get into an
> >> confused
> >> state and never stop running. Use Windows Task Manager to check whether
> >> another task is actually the guilty party. If it is one of those, then
> >> try
> >> killing that Windows process.
> >>
> >> If you cannot do anything else, you should schedule a restart of your SQL
> >> Sever. That should (naturally) clear the problem out. But you will need
> >> to
> >> remain alert to its returning.
> >>
> >> But, FWIW, I have not seen much of this on SQL Server 2005 SP2.
> >>
> >> RLF
> >>
> >>
> >> "Ross" <Ross@.discussions.microsoft.com> wrote in message
> >> news:3BB4D4DE-BFA5-431D-971B-4CB63C4E9381@.microsoft.com...
> >> >I have been using SQL Server 2000 for some time and am attempting to
> >> >upgrade
> >> > to SQL Server 2005 (testing the trial version).
> >> >
> >> > I backed up my SQL Server 2000 database(s) and then restored them on
> >> > SQL
> >> > Server 2005 which worked well.
> >> >
> >> > Everything seemed to be working fine until I updated SQL Server 2005
> >> > with
> >> > service pack 2.
> >> >
> >> > I am running this on a Dual-Core AMD Opteron Processor 2216HE. SQL
> >> > Server
> >> > is now using 25% CPU continuously (or 100% of one of the processor
> >> > instances). The databases I converted are working as expected,
> >> > however,
> >> > the
> >> > SQL Server process is continuously burning CPU when nothing is
> >> > happening
> >> > (this is over several days now).
> >> >
> >> > Has anybody else see this problem?
> >> >
> >> >
> >>
> >>
> >>
>
>|||Unfortunately, that didn't go very well.
I installed the hotfix which appeared to solve the CPU problem (sqlservr.exe
back at 0% when no activity). I was able to run the management software and
all looked fine, but when I attempted to run my web application, sql server
choked. A simple query returning a datareader (ASP.NET) caused SQLServer to
get a memory exception.
Needless to say, I removed the Hotfix and am now back where I started. I
guess I'll have to wait until Microsoft releases the hotfix in an update.
Ross
"Russell Fields" wrote:
> Ross,
> Check out: http://support.microsoft.com/kb/931821
> RLF
> "Ross" <Ross@.discussions.microsoft.com> wrote in message
> news:51734426-C053-4F76-9801-137FFC3DCACB@.microsoft.com...
> > Thanks for your answer.
> >
> > It is definitely SQL Server that is eating 25% CPU (sqlservr.exe). I have
> > actually restarted SQL Server (and the machine itself) and subsequently
> > the
> > SQL Server process goes right back up to 25%.
> >
> > As you noted regarding the blocking transaction, I'm not experiencing any
> > problems with the database, just the CPU usage.
> >
> > The sp_who2 shows a CPUTime for a "LAZY WRITER" process id 3 of 71195468
> > (as
> > it happens, the machine was restarted at 11:19PM EST last night).
> >
> > Full row information from sp_who2:
> >
> > SPID 3
> > Status BACKGROUND
> > Login sa
> > HostName .
> > BlkBy .
> > DBName NULL
> > Command LAZY WRITER
> > CPU TIME 71195468
> > DiskIO 0
> > LastBatch 03/12 23:19:34
> > ProgramName (blank)
> > SPID 3
> > REQUESTID 0
> >
> > Any ideas?
> >
> > Thanks again for your help...
> >
> > Ross
> >
> >
> >
> > "Russell Fields" wrote:
> >
> >> Ross,
> >>
> >> I have seen this happen in the past, for many different reasons.
> >>
> >> I assume from your comment that you know that it is sqlservr.exe that is
> >> using the 25%. Can you run sp_who2 to identify some process that
> >> continues
> >> to eat up CPU time? If so, use dbcc inputbuffer, fn_get_sql, or look at
> >> sys.dm_exec_sql_text to see what is running. Also use DBCC OPENTRAN to
> >> find
> >> the oldest transaction in each database. (However, I assume that there
> >> is
> >> not a blocking transaction or you would be having other problems.)
> >>
> >> If you identify a problem spid, try to get the hostname for that spid.
> >> Sometimes a client computer loses connectivity, but the SQL Server does
> >> not
> >> know that, so it keeps trying (forever) to send the result set to the
> >> client. If that is the case, logging the hostname computer off of the
> >> domain, then logging back into the domain, often alerts SQL Server to the
> >> problem so that it will abandon that result set.
> >>
> >> If appropriate, you can try to KILL the spid. (But the spid may not
> >> always
> >> be killable.)
> >>
> >> Other software can also get into a confused state and eat up CPU. For
> >> example, a DTS package, backup software, etc. can also get into an
> >> confused
> >> state and never stop running. Use Windows Task Manager to check whether
> >> another task is actually the guilty party. If it is one of those, then
> >> try
> >> killing that Windows process.
> >>
> >> If you cannot do anything else, you should schedule a restart of your SQL
> >> Sever. That should (naturally) clear the problem out. But you will need
> >> to
> >> remain alert to its returning.
> >>
> >> But, FWIW, I have not seen much of this on SQL Server 2005 SP2.
> >>
> >> RLF
> >>
> >>
> >> "Ross" <Ross@.discussions.microsoft.com> wrote in message
> >> news:3BB4D4DE-BFA5-431D-971B-4CB63C4E9381@.microsoft.com...
> >> >I have been using SQL Server 2000 for some time and am attempting to
> >> >upgrade
> >> > to SQL Server 2005 (testing the trial version).
> >> >
> >> > I backed up my SQL Server 2000 database(s) and then restored them on
> >> > SQL
> >> > Server 2005 which worked well.
> >> >
> >> > Everything seemed to be working fine until I updated SQL Server 2005
> >> > with
> >> > service pack 2.
> >> >
> >> > I am running this on a Dual-Core AMD Opteron Processor 2216HE. SQL
> >> > Server
> >> > is now using 25% CPU continuously (or 100% of one of the processor
> >> > instances). The databases I converted are working as expected,
> >> > however,
> >> > the
> >> > SQL Server process is continuously burning CPU when nothing is
> >> > happening
> >> > (this is over several days now).
> >> >
> >> > Has anybody else see this problem?
> >> >
> >> >
> >>
> >>
> >>
>
>|||Ross,
Too bad. Memory exceptions are bugs, so you could report it to Microsoft if
you want.
RLF
"Ross" <Ross@.discussions.microsoft.com> wrote in message
news:8498810D-A651-4F6C-9970-A6FD26FC8835@.microsoft.com...
> Unfortunately, that didn't go very well.
> I installed the hotfix which appeared to solve the CPU problem
> (sqlservr.exe
> back at 0% when no activity). I was able to run the management software
> and
> all looked fine, but when I attempted to run my web application, sql
> server
> choked. A simple query returning a datareader (ASP.NET) caused SQLServer
> to
> get a memory exception.
> Needless to say, I removed the Hotfix and am now back where I started. I
> guess I'll have to wait until Microsoft releases the hotfix in an update.
> Ross
> "Russell Fields" wrote:
>> Ross,
>> Check out: http://support.microsoft.com/kb/931821
>> RLF
>> "Ross" <Ross@.discussions.microsoft.com> wrote in message
>> news:51734426-C053-4F76-9801-137FFC3DCACB@.microsoft.com...
>> > Thanks for your answer.
>> >
>> > It is definitely SQL Server that is eating 25% CPU (sqlservr.exe). I
>> > have
>> > actually restarted SQL Server (and the machine itself) and subsequently
>> > the
>> > SQL Server process goes right back up to 25%.
>> >
>> > As you noted regarding the blocking transaction, I'm not experiencing
>> > any
>> > problems with the database, just the CPU usage.
>> >
>> > The sp_who2 shows a CPUTime for a "LAZY WRITER" process id 3 of
>> > 71195468
>> > (as
>> > it happens, the machine was restarted at 11:19PM EST last night).
>> >
>> > Full row information from sp_who2:
>> >
>> > SPID 3
>> > Status BACKGROUND
>> > Login sa
>> > HostName .
>> > BlkBy .
>> > DBName NULL
>> > Command LAZY WRITER
>> > CPU TIME 71195468
>> > DiskIO 0
>> > LastBatch 03/12 23:19:34
>> > ProgramName (blank)
>> > SPID 3
>> > REQUESTID 0
>> >
>> > Any ideas?
>> >
>> > Thanks again for your help...
>> >
>> > Ross
>> >
>> >
>> >
>> > "Russell Fields" wrote:
>> >
>> >> Ross,
>> >>
>> >> I have seen this happen in the past, for many different reasons.
>> >>
>> >> I assume from your comment that you know that it is sqlservr.exe that
>> >> is
>> >> using the 25%. Can you run sp_who2 to identify some process that
>> >> continues
>> >> to eat up CPU time? If so, use dbcc inputbuffer, fn_get_sql, or look
>> >> at
>> >> sys.dm_exec_sql_text to see what is running. Also use DBCC OPENTRAN
>> >> to
>> >> find
>> >> the oldest transaction in each database. (However, I assume that
>> >> there
>> >> is
>> >> not a blocking transaction or you would be having other problems.)
>> >>
>> >> If you identify a problem spid, try to get the hostname for that spid.
>> >> Sometimes a client computer loses connectivity, but the SQL Server
>> >> does
>> >> not
>> >> know that, so it keeps trying (forever) to send the result set to the
>> >> client. If that is the case, logging the hostname computer off of
>> >> the
>> >> domain, then logging back into the domain, often alerts SQL Server to
>> >> the
>> >> problem so that it will abandon that result set.
>> >>
>> >> If appropriate, you can try to KILL the spid. (But the spid may not
>> >> always
>> >> be killable.)
>> >>
>> >> Other software can also get into a confused state and eat up CPU. For
>> >> example, a DTS package, backup software, etc. can also get into an
>> >> confused
>> >> state and never stop running. Use Windows Task Manager to check
>> >> whether
>> >> another task is actually the guilty party. If it is one of those, then
>> >> try
>> >> killing that Windows process.
>> >>
>> >> If you cannot do anything else, you should schedule a restart of your
>> >> SQL
>> >> Sever. That should (naturally) clear the problem out. But you will
>> >> need
>> >> to
>> >> remain alert to its returning.
>> >>
>> >> But, FWIW, I have not seen much of this on SQL Server 2005 SP2.
>> >>
>> >> RLF
>> >>
>> >>
>> >> "Ross" <Ross@.discussions.microsoft.com> wrote in message
>> >> news:3BB4D4DE-BFA5-431D-971B-4CB63C4E9381@.microsoft.com...
>> >> >I have been using SQL Server 2000 for some time and am attempting to
>> >> >upgrade
>> >> > to SQL Server 2005 (testing the trial version).
>> >> >
>> >> > I backed up my SQL Server 2000 database(s) and then restored them on
>> >> > SQL
>> >> > Server 2005 which worked well.
>> >> >
>> >> > Everything seemed to be working fine until I updated SQL Server 2005
>> >> > with
>> >> > service pack 2.
>> >> >
>> >> > I am running this on a Dual-Core AMD Opteron Processor 2216HE. SQL
>> >> > Server
>> >> > is now using 25% CPU continuously (or 100% of one of the processor
>> >> > instances). The databases I converted are working as expected,
>> >> > however,
>> >> > the
>> >> > SQL Server process is continuously burning CPU when nothing is
>> >> > happening
>> >> > (this is over several days now).
>> >> >
>> >> > Has anybody else see this problem?
>> >> >
>> >> >
>> >>
>> >>
>> >>
>>
Subscribe to:
Posts (Atom)