Convert a SQLExpress File based DB Project to SQLExpress Server DB Project

Hi All,

I've been struggling with this for hours...

Could someone please advise me on how to convert my current File based SQL Server Express website to a Server based SQL Express one.

Particularly interested in what I need to do in the SQL Express management tool, changes I need to make the projecvt itself and changes needed to get IIS to understand things have been changed.

Thanks,

Martin.

Hi Martin, you can try these steps:

1. Detach the database file from your project: open the project with VS2005->right click the mdf file in Solution Explorer->choose detach

2. Attach the database into SQL: open Management Studio->right click Databases in Object Explorer-> choose Attach.

3. change your connection string: remove AttachDBFileName property; modify the database property to point to the new attached database.

For more information, you can refer tohttp://msdn2.microsoft.com/en-us/library/ms187858.aspx

Conversion issues on Output Columns with Script Task

I am not sure which type to use for my Script Transformation Editor output fields. I'm getting errors based on the Data Type I'm specifying for my fields.

Print Screens:

http://www.webfound.net/script_task.jpg

TITLE: Package Validation Error

Package Validation Error

ADDITIONAL INFORMATION:

Error at Import Maintenance (mnt) File [Split HeaderRows into Columns [5176]]: Error 30512: Option Strict On disallows implicit conversions from 'Double' to 'UInteger'.
Line 21 Column 37 through 71
Error 30512: Option Strict On disallows implicit conversions from 'Double' to 'Long'.
Line 22 Column 35 through 69
Error 30512: Option Strict On disallows implicit conversions from 'Double' to 'Long'.
Line 23 Column 37 through 71
Error 30512: Option Strict On disallows implicit conversions from 'Double' to 'Long'.
Line 25 Column 27 through 61

Error at Import Maintenance (mnt) File [Split HeaderRows into Columns [5176]]: Error 30512: Option Strict On disallows implicit conversions from 'Double' to 'UInteger'.
Line 21 Column 37 through 71
Error 30512: Option Strict On disallows implicit conversions from 'Double' to 'Long'.
Line 22 Column 35 through 69
Error 30512: Option Strict On disallows implicit conversions from 'Double' to 'Long'.
Line 23 Column 37 through 71
Error 30512: Option Strict On disallows implicit conversions from 'Double' to 'Long'.
Line 25 Column 27 through 61

Error at Import Maintenance (mnt) File [DTS.Pipeline]: "component "Split HeaderRows into Columns" (5176)" failed validation and returned validation status "VS_ISBROKEN".

Error at Import Maintenance (mnt) File [DTS.Pipeline]: One or more component failed validation.

Error at Import Maintenance (mnt) File: There were errors during task validation.

(Microsoft.DataTransformationServices.VsIntegration)

BUTTONS:

OK

I'm not sure if this is needed but here's the script I coded in my script task also:

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain

Inherits UserComponent

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Dim strWholeRow As String = Row.OutputHeaderRows

Row.BatchDate = CStr(strWholeRow.Substring(0, 8))

Row.NotUsed = CStr(strWholeRow.Substring(9, 32))

Row.TransactionCode = CStr(strWholeRow.Substring(33, 34))

Row.GrossBatchTotalAmount = CDbl(strWholeRow.Substring(35, 44))

Row.NetBatchTotalAmount = CDbl(strWholeRow.Substring(45, 54))

Row.BatchTransactionCount = CDbl(strWholeRow.Substring(55, 59))

Row.PNETID = CStr(strWholeRow.Substring(60, 63))

Row.PartnerCode = CDbl(strWholeRow.Substring(64, 67))

Row.Filler = strWholeRow.Substring(68, 100)

End Sub

End Class

Looking at the screenshot and the code it looks like you're trying to put a decimal number into an integer column and you simply can't do that. You'll have to change either the type of the output column (try using DT_DECIMAL) or change CDbl to CInt.

-Jamie

Controlling Trigger actions based on user

Is it possible to control the actions of a trigger based on the user who
updates the record?
In pseudo code, I'm trying to do the following:
on update:
if (updating user = "User_A") and (Artist_Type = "DJ" or "CL") then
{ newrecord.PIC_FIELD = oldrecord.PIC_FIELD }
Could someone shead some light on if/how this could be done in "real" code?
Any help would be GREATLY appreciated.
Thanks,
_KThe trigger's code should look very similar to your pseudo code (not
tested):
IF SUSER_SNAME() = 'User_A'
BEGIN
UPDATE T1
SET PIC_FIELD = D.PIC_FIELD
FROM T1 JOIN deleted AS D
ON T1.key = D.key
WHERE T1.Artist_Type IN('DJ', 'CL')
END
BG, SQL Server MVP
www.SolidQualityLearning.com
"KBryan" <kbryan@.noyouwont.com> wrote in message
news:%237v6ttWNFHA.2716@.TK2MSFTNGP10.phx.gbl...
> Is it possible to control the actions of a trigger based on the user who
> updates the record?
> In pseudo code, I'm trying to do the following:
> on update:
> if (updating user = "User_A") and (Artist_Type = "DJ" or "CL") then
> { newrecord.PIC_FIELD = oldrecord.PIC_FIELD }
>
> Could someone shead some light on if/how this could be done in "real"
> code?
> Any help would be GREATLY appreciated.
> Thanks,
> _K
>|||Thanks VERY much.
Would it still be deleted if this is an update trigger?
"Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in message
news:%23pug97WNFHA.3560@.TK2MSFTNGP14.phx.gbl...
> The trigger's code should look very similar to your pseudo code (not
> tested):
> IF SUSER_SNAME() = 'User_A'
> BEGIN
> UPDATE T1
> SET PIC_FIELD = D.PIC_FIELD
> FROM T1 JOIN deleted AS D
> ON T1.key = D.key
> WHERE T1.Artist_Type IN('DJ', 'CL')
> END
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "KBryan" <kbryan@.noyouwont.com> wrote in message
> news:%237v6ttWNFHA.2716@.TK2MSFTNGP10.phx.gbl...
>|||Yes; deleted holds the old image of the modified data.
BG, SQL Server MVP
www.SolidQualityLearning.com
"KBryan" <kbryan@.noyouwont.com> wrote in message
news:%23WFXaMXNFHA.1172@.TK2MSFTNGP12.phx.gbl...
> Thanks VERY much.
> Would it still be deleted if this is an update trigger?
>
> "Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in
> message news:%23pug97WNFHA.3560@.TK2MSFTNGP14.phx.gbl...
>

Controlling PDF export filename

Folks,

Our client has requested that we change the export file name (PDF and others) to match the report name. Currently, the name is based on the report's filename (eg, foo.rdl exports as foo.pdf) -- this causes problems because the same RDL is being used for a few fairly different reports. Is this controllable at all?

thanks,

--randy

If you are using the ReportViewer control, you can set the DisplayName property. This will affect the export file name and the document map root node label.

If you are using direct url access to the report server, the name can't be changed. In that case, you would need to implement your own export UI that retrieved the exported report from the server and relayed it to the client with a custom content disposition header.

|||

Hello,

I need help on this isse too, it sounded that you somehow exported it to pdf.

I am using SQL 2006 and Reporting Services 2005 and rendering report in my asp.net application. I am trying to export reports under a button click, can anyone give me a sample code?

Thanks

|||

Dear Brian,

I am coding with C# and can not find the reportViewer's DisplayName property?

Below is my code for the form that I display the reportviewer in where and what code should I insert so that I change the filename automatically?

Thanks in advance.

Best regards,

Kaan Demirtas

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Text;

using System.Windows.Forms;

namespace GECKOV2

{

publicpartialclassKonfirmasyonRaporForm : Form

{

public KonfirmasyonRaporForm()

{

InitializeComponent();

}

privatevoid KonfirmasyonRaporForm_Load(object sender, EventArgs e)

{

// TODO: This line of code loads data into the 'KonfirmasyonDataSet.KonfirmasyonVerilenOrderlar' table. You can move, or remove it, as needed.

this.KonfirmasyonVerilenOrderlarTableAdapter.Fill(this.KonfirmasyonDataSet.KonfirmasyonVerilenOrderlar);

this.reportViewer1.RefreshReport();

}

privatevoid KonfirmasyonRaporForm_FormClosed(object sender, FormClosedEventArgs e)

{

;

if (MessageBox.Show("Raporda yer alan urunlerin konfirmasyon bilgilerini gonderildi olarak isaretlemek istermisiniz?", "Konfirmasyon islemi onaylama", MessageBoxButtons.YesNo, MessageBoxIcon.Question)==DialogResult.Yes)

{

string konfirmeEdildiDiyeIsaretle = "update siparisler set FabrikaOnayiMusteriyeGonderildi=1 from KonfirmasyonVerilenOrderlar where siparisler.OrderID=KonfirmasyonVerilenOrderlar.KonfirmasyonVerilenOrderID";

MessageBox.Show(Program.SqlKomutIsle(konfirmeEdildiDiyeIsaretle));

}

}

}

}

|||

What a huge PITA! Is MS going to fix this anytime soon? This should be something that can be set regardless of whether you are using direct url access or not.

|||

>> I am trying to export reports under a button click, can anybody give me a sample code?

Sure. <s> Is this remote or local mode ? And did you want the output to show up in a window?

In this example, it happens to be a local mode report, and I bring the PDF up in a window... it is definitely not the only way... You can see where I am setting the temporary file name on the server -- considering that the user can decide what filename to save it to on the client side, I didn't see the point of getting all excited about the server-side file name, but you'll get the picture.

Also, I wrote this example for somebody else, and in that case we needed to adjust the datasource before export -- probably not true in your case, but it won't hurt to keep it in, it's not much code, and you'll get a better idea of what is really going on here, how to add in whatever you do need to add.

If it turns out you're in remote mode, it's doable also.

HTH,

>L<

Code Snippet

ProtectedSub Button1_Click(ByVal sender AsObject, ByVal e As System.EventArgs) Handles Button1.Click

Dim buffer AsByte(), f AsString, fs As System.IO.FileStream

f = System.IO.Path.GetTempFileName()

System.IO.Path.ChangeExtension(f, "PDF")

' there is probably a better way to set up the rendered PDF

' for redirecting to the Response output, but this one works.

' here is the binding bit. Revise to suit your dynamic situation.

' if you aren't really dynamically binding data against one

' loaded report, but rather changing

' reports to suit the user's needs, that will work too.

Dim ReportDataSourceX = New Microsoft.Reporting.WebForms.ReportDataSource()

ReportDataSourceX.Name = "DataSet1_Recipient"

ReportDataSourceX.Value = Me.SqlDataSource1

WithMe.ReportViewer1.LocalReport

.DataSources.Clear()

.DataSources.Add(ReportDataSourceX)

buffer = .Render("PDF", Nothing, Nothing, Nothing, Nothing, Nothing, Nothing)

EndWith

fs = New System.IO.FileStream(f, System.IO.FileMode.Create)

fs.Write(buffer, 0, buffer.Length)

fs.Close()

fs.Dispose()

Response.ContentType = "Application/pdf"

Response.WriteFile(f)

Response.End()

System.IO.File.Delete(f)

EndSub

Controlling PDF export filename

Folks,

Our client has requested that we change the export file name (PDF and others) to match the report name. Currently, the name is based on the report's filename (eg, foo.rdl exports as foo.pdf) -- this causes problems because the same RDL is being used for a few fairly different reports. Is this controllable at all?

thanks,

--randy

If you are using the ReportViewer control, you can set the DisplayName property. This will affect the export file name and the document map root node label.

If you are using direct url access to the report server, the name can't be changed. In that case, you would need to implement your own export UI that retrieved the exported report from the server and relayed it to the client with a custom content disposition header.

|||

Hello,

I need help on this isse too, it sounded that you somehow exported it to pdf.

I am using SQL 2006 and Reporting Services 2005 and rendering report in my asp.net application. I am trying to export reports under a button click, can anyone give me a sample code?

Thanks

|||

Dear Brian,

I am coding with C# and can not find the reportViewer's DisplayName property?

Below is my code for the form that I display the reportviewer in where and what code should I insert so that I change the filename automatically?

Thanks in advance.

Best regards,

Kaan Demirtas

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Text;

using System.Windows.Forms;

namespace GECKOV2

{

public partial class KonfirmasyonRaporForm : Form

{

public KonfirmasyonRaporForm()

{

InitializeComponent();

}

private void KonfirmasyonRaporForm_Load(object sender, EventArgs e)

{

// TODO: This line of code loads data into the 'KonfirmasyonDataSet.KonfirmasyonVerilenOrderlar' table. You can move, or remove it, as needed.

this.KonfirmasyonVerilenOrderlarTableAdapter.Fill(this.KonfirmasyonDataSet.KonfirmasyonVerilenOrderlar);

this.reportViewer1.RefreshReport();

}

private void KonfirmasyonRaporForm_FormClosed(object sender, FormClosedEventArgs e)

{

;

if (MessageBox.Show("Raporda yer alan urunlerin konfirmasyon bilgilerini gonderildi olarak isaretlemek istermisiniz?", "Konfirmasyon islemi onaylama", MessageBoxButtons.YesNo, MessageBoxIcon.Question)==DialogResult.Yes)

{

string konfirmeEdildiDiyeIsaretle = "update siparisler set FabrikaOnayiMusteriyeGonderildi=1 from KonfirmasyonVerilenOrderlar where siparisler.OrderID=KonfirmasyonVerilenOrderlar.KonfirmasyonVerilenOrderID";

MessageBox.Show(Program.SqlKomutIsle(konfirmeEdildiDiyeIsaretle));

}

}

}

}

|||

What a huge PITA! Is MS going to fix this anytime soon? This should be something that can be set regardless of whether you are using direct url access or not.

|||

>> I am trying to export reports under a button click, can anybody give me a sample code?

Sure. <s> Is this remote or local mode ? And did you want the output to show up in a window?

In this example, it happens to be a local mode report, and I bring the PDF up in a window... it is definitely not the only way... You can see where I am setting the temporary file name on the server -- considering that the user can decide what filename to save it to on the client side, I didn't see the point of getting all excited about the server-side file name, but you'll get the picture.

Also, I wrote this example for somebody else, and in that case we needed to adjust the datasource before export -- probably not true in your case, but it won't hurt to keep it in, it's not much code, and you'll get a better idea of what is really going on here, how to add in whatever you do need to add.

If it turns out you're in remote mode, it's doable also.

HTH,

>L<

Code Snippet

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim buffer As Byte(), f As String, fs As System.IO.FileStream

f = System.IO.Path.GetTempFileName()

System.IO.Path.ChangeExtension(f, "PDF")

' there is probably a better way to set up the rendered PDF

' for redirecting to the Response output, but this one works.

' here is the binding bit. Revise to suit your dynamic situation.

' if you aren't really dynamically binding data against one

' loaded report, but rather changing

' reports to suit the user's needs, that will work too.

Dim ReportDataSourceX = New Microsoft.Reporting.WebForms.ReportDataSource()

ReportDataSourceX.Name = "DataSet1_Recipient"

ReportDataSourceX.Value = Me.SqlDataSource1

With Me.ReportViewer1.LocalReport

.DataSources.Clear()

.DataSources.Add(ReportDataSourceX)

buffer = .Render("PDF", Nothing, Nothing, Nothing, Nothing, Nothing, Nothing)

End With

fs = New System.IO.FileStream(f, System.IO.FileMode.Create)

fs.Write(buffer, 0, buffer.Length)

fs.Close()

fs.Dispose()

Response.ContentType = "Application/pdf"

Response.WriteFile(f)

Response.End()

System.IO.File.Delete(f)

End Sub

Controlling PDF export filename

Folks,

Our client has requested that we change the export file name (PDF and others) to match the report name. Currently, the name is based on the report's filename (eg, foo.rdl exports as foo.pdf) -- this causes problems because the same RDL is being used for a few fairly different reports. Is this controllable at all?

thanks,

--randy

If you are using the ReportViewer control, you can set the DisplayName property. This will affect the export file name and the document map root node label.

If you are using direct url access to the report server, the name can't be changed. In that case, you would need to implement your own export UI that retrieved the exported report from the server and relayed it to the client with a custom content disposition header.

|||

Hello,

I need help on this isse too, it sounded that you somehow exported it to pdf.

I am using SQL 2006 and Reporting Services 2005 and rendering report in my asp.net application. I am trying to export reports under a button click, can anyone give me a sample code?

Thanks

|||

Dear Brian,

I am coding with C# and can not find the reportViewer's DisplayName property?

Below is my code for the form that I display the reportviewer in where and what code should I insert so that I change the filename automatically?

Thanks in advance.

Best regards,

Kaan Demirtas

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Text;

using System.Windows.Forms;

namespace GECKOV2

{

public partial class KonfirmasyonRaporForm : Form

{

public KonfirmasyonRaporForm()

{

InitializeComponent();

}

private void KonfirmasyonRaporForm_Load(object sender, EventArgs e)

{

// TODO: This line of code loads data into the 'KonfirmasyonDataSet.KonfirmasyonVerilenOrderlar' table. You can move, or remove it, as needed.

this.KonfirmasyonVerilenOrderlarTableAdapter.Fill(this.KonfirmasyonDataSet.KonfirmasyonVerilenOrderlar);

this.reportViewer1.RefreshReport();

}

private void KonfirmasyonRaporForm_FormClosed(object sender, FormClosedEventArgs e)

{

;

if (MessageBox.Show("Raporda yer alan urunlerin konfirmasyon bilgilerini gonderildi olarak isaretlemek istermisiniz?", "Konfirmasyon islemi onaylama", MessageBoxButtons.YesNo, MessageBoxIcon.Question)==DialogResult.Yes)

{

string konfirmeEdildiDiyeIsaretle = "update siparisler set FabrikaOnayiMusteriyeGonderildi=1 from KonfirmasyonVerilenOrderlar where siparisler.OrderID=KonfirmasyonVerilenOrderlar.KonfirmasyonVerilenOrderID";

MessageBox.Show(Program.SqlKomutIsle(konfirmeEdildiDiyeIsaretle));

}

}

}

}

|||

What a huge PITA! Is MS going to fix this anytime soon? This should be something that can be set regardless of whether you are using direct url access or not.

|||

>> I am trying to export reports under a button click, can anybody give me a sample code?

Sure. <s> Is this remote or local mode ? And did you want the output to show up in a window?

In this example, it happens to be a local mode report, and I bring the PDF up in a window... it is definitely not the only way... You can see where I am setting the temporary file name on the server -- considering that the user can decide what filename to save it to on the client side, I didn't see the point of getting all excited about the server-side file name, but you'll get the picture.

Also, I wrote this example for somebody else, and in that case we needed to adjust the datasource before export -- probably not true in your case, but it won't hurt to keep it in, it's not much code, and you'll get a better idea of what is really going on here, how to add in whatever you do need to add.

If it turns out you're in remote mode, it's doable also.

HTH,

>L<

Code Snippet

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim buffer As Byte(), f As String, fs As System.IO.FileStream

f = System.IO.Path.GetTempFileName()

System.IO.Path.ChangeExtension(f, "PDF")

' there is probably a better way to set up the rendered PDF

' for redirecting to the Response output, but this one works.

' here is the binding bit. Revise to suit your dynamic situation.

' if you aren't really dynamically binding data against one

' loaded report, but rather changing

' reports to suit the user's needs, that will work too.

Dim ReportDataSourceX = New Microsoft.Reporting.WebForms.ReportDataSource()

ReportDataSourceX.Name = "DataSet1_Recipient"

ReportDataSourceX.Value = Me.SqlDataSource1

With Me.ReportViewer1.LocalReport

.DataSources.Clear()

.DataSources.Add(ReportDataSourceX)

buffer = .Render("PDF", Nothing, Nothing, Nothing, Nothing, Nothing, Nothing)

End With

fs = New System.IO.FileStream(f, System.IO.FileMode.Create)

fs.Write(buffer, 0, buffer.Length)

fs.Close()

fs.Dispose()

Response.ContentType = "Application/pdf"

Response.WriteFile(f)

Response.End()

System.IO.File.Delete(f)

End Sub

Controlling PDF export filename

Folks,

Our client has requested that we change the export file name (PDF and others) to match the report name. Currently, the name is based on the report's filename (eg, foo.rdl exports as foo.pdf) -- this causes problems because the same RDL is being used for a few fairly different reports. Is this controllable at all?

thanks,

--randy

If you are using the ReportViewer control, you can set the DisplayName property. This will affect the export file name and the document map root node label.

If you are using direct url access to the report server, the name can't be changed. In that case, you would need to implement your own export UI that retrieved the exported report from the server and relayed it to the client with a custom content disposition header.

|||

Hello,

I need help on this isse too, it sounded that you somehow exported it to pdf.

I am using SQL 2006 and Reporting Services 2005 and rendering report in my asp.net application. I am trying to export reports under a button click, can anyone give me a sample code?

Thanks

|||

Dear Brian,

I am coding with C# and can not find the reportViewer's DisplayName property?

Below is my code for the form that I display the reportviewer in where and what code should I insert so that I change the filename automatically?

Thanks in advance.

Best regards,

Kaan Demirtas

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Text;

using System.Windows.Forms;

namespace GECKOV2

{

public partial class KonfirmasyonRaporForm : Form

{

public KonfirmasyonRaporForm()

{

InitializeComponent();

}

private void KonfirmasyonRaporForm_Load(object sender, EventArgs e)

{

// TODO: This line of code loads data into the 'KonfirmasyonDataSet.KonfirmasyonVerilenOrderlar' table. You can move, or remove it, as needed.

this.KonfirmasyonVerilenOrderlarTableAdapter.Fill(this.KonfirmasyonDataSet.KonfirmasyonVerilenOrderlar);

this.reportViewer1.RefreshReport();

}

private void KonfirmasyonRaporForm_FormClosed(object sender, FormClosedEventArgs e)

{

;

if (MessageBox.Show("Raporda yer alan urunlerin konfirmasyon bilgilerini gonderildi olarak isaretlemek istermisiniz?", "Konfirmasyon islemi onaylama", MessageBoxButtons.YesNo, MessageBoxIcon.Question)==DialogResult.Yes)

{

string konfirmeEdildiDiyeIsaretle = "update siparisler set FabrikaOnayiMusteriyeGonderildi=1 from KonfirmasyonVerilenOrderlar where siparisler.OrderID=KonfirmasyonVerilenOrderlar.KonfirmasyonVerilenOrderID";

MessageBox.Show(Program.SqlKomutIsle(konfirmeEdildiDiyeIsaretle));

}

}

}

}

|||

What a huge PITA! Is MS going to fix this anytime soon? This should be something that can be set regardless of whether you are using direct url access or not.

|||

>> I am trying to export reports under a button click, can anybody give me a sample code?

Sure. <s> Is this remote or local mode ? And did you want the output to show up in a window?

In this example, it happens to be a local mode report, and I bring the PDF up in a window... it is definitely not the only way... You can see where I am setting the temporary file name on the server -- considering that the user can decide what filename to save it to on the client side, I didn't see the point of getting all excited about the server-side file name, but you'll get the picture.

Also, I wrote this example for somebody else, and in that case we needed to adjust the datasource before export -- probably not true in your case, but it won't hurt to keep it in, it's not much code, and you'll get a better idea of what is really going on here, how to add in whatever you do need to add.

If it turns out you're in remote mode, it's doable also.

HTH,

>L<

Code Snippet

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim buffer As Byte(), f As String, fs As System.IO.FileStream

f = System.IO.Path.GetTempFileName()

System.IO.Path.ChangeExtension(f, "PDF")

' there is probably a better way to set up the rendered PDF

' for redirecting to the Response output, but this one works.

' here is the binding bit. Revise to suit your dynamic situation.

' if you aren't really dynamically binding data against one

' loaded report, but rather changing

' reports to suit the user's needs, that will work too.

Dim ReportDataSourceX = New Microsoft.Reporting.WebForms.ReportDataSource()

ReportDataSourceX.Name = "DataSet1_Recipient"

ReportDataSourceX.Value = Me.SqlDataSource1

With Me.ReportViewer1.LocalReport

.DataSources.Clear()

.DataSources.Add(ReportDataSourceX)

buffer = .Render("PDF", Nothing, Nothing, Nothing, Nothing, Nothing, Nothing)

End With

fs = New System.IO.FileStream(f, System.IO.FileMode.Create)

fs.Write(buffer, 0, buffer.Length)

fs.Close()

fs.Dispose()

Response.ContentType = "Application/pdf"

Response.WriteFile(f)

Response.End()

System.IO.File.Delete(f)

End Sub

Controlling fields in a select statement by use of parameters

Hi to all

I wish to be able to have a standard select statement which has
additional fields added to it at run-time based on supplied
parameter(s).

ie
declare @.theTest1 nvarchar(10)
set @.theTest1='TRUE'

declare @.theTest2 nvarchar(10)
set @.theTest2='TRUE'

select
p_full_name
if @.theTest1='TRUE'
BEGIN
other field1,
END
if @.theTest2='TRUE'
BEGIN
other field2
END

from dbo.tbl_GIS_person
where record_id < 20

I do not wish to use an IF statement to test the parameter for a
condition and then repeat the entire select statement particularly as
it is a UNIONed query for three different statement

ie
declare @.theTest1 nvarchar(10)
set @.theTest1='TRUE'

declare @.theTest2 nvarchar(10)
set @.theTest2='TRUE'

if @.theTest1='TRUE' AND @.theTest2='TRUE'
BEGIN
select
p_full_name,
other field1,
other field2
from dbo.tbl_GIS_person
where record_id < 20
END

if @.theTest1='TRUE' AND @.theTest2='FALSE'
BEGIN
select
p_full_name,
other field1
from dbo.tbl_GIS_person
where record_id < 20
END
..
..
..
if @.theTest<>'TRUE'
BEGIN
select
p_full_name
from dbo.tbl_GIS_person
where record_id < 20
END

Make sense? So the select is standard in the most part but with small
variations depending on the user's choice. I want to avoid risk of
breakage by having only one spot that the FROM, JOIN and WHERE
statements need to be defined.

The query will end up being used in an XML template query.

Any help would be much appreciated

Regards

GIS AnalystIf you don't want to write three separate queries, then you'll probably
have to use dynamic SQL and build up the query string dynamically:

http://www.sommarskog.se/dyn-search.html
http://www.sommarskog.se/dynamic_sql.html

Alternatively, you could simply return all the columns all the time
(perhaps using CASE to return empty values for the unwanted columns so
as to minimize the data volume) and let the client decide which ones to
present/process, but in a more complex case it might not be workable.

Simon|||Hi Simon

thanks for the ideas. I did think about genearting the statement within
a stored procedure but thought I would check to see if there were
standard sql statement to do this first.
One reason for not returning all columns all the time is to avoid
record duplication when the optional fields are included. (Duplicates
apart from the optional field)

Regards

GIS Analyst

Controlling export formats based on user role

I'm using Reporting Services 2000 and I have a situation where I need to specify the file formats available to export. I know I can change the RSReportServer.config file to change these at a global level, but what I really need is to be able to configure these differently for different user groups. For example:

group 1 - has all available export formats available

group 2 - only has the ability to export to excel

group 3 - has no option to export to any format.

Is there a way for this to be done? If so, can anybody give me any pointers on how it can be done?

Any help given will be most appreciated!!

In Report Manager there is no way to limit export formats by role.

You would need to create a custom application to host the reporting services control, and limit your users in this way.

cheers,

Andrew

|||

ok thanks I thought this might be the case but just wondered if any simpler solutions were about.

Thanks again for your reply

Context Search

Hello,

I have a web application that I need to search based on what the user entered in the input box.
e.g when the user enters in the box something like "Brain Boom"

I need to search the column in the DB table where there is anything word like
Brain or has Boom or all the above. How will I accomplish this?

Thanks

In transact-sql the query would look something like this

select * from sometable where seachcolumn like '%Brain%' or searchcolumn like '%Boom%'

This will give you all colums in records from sometable that has Brain or Boom in column named searchcolumn.

|||Since these values are in one Text box, How would I know that there are two words in the text box? Do I have to always loop through the text box to check if it is a tab/comma delimited list?|||

Yes,

T-SQL is not able to determine that by itself. You need to construct a proper query for it and execute it.

I am not sure if full-text search capabilities would be an option in this case. Maybe some other more skilled SQL developer are able to give you more options.

|||

Two possible solutions that I would use.

1. Full Text Search. This sounds like a very good case for using it. It allows you to just say:

where CONTAINS ( columnName, 'Brain Boom')

It also gives you lots of other powerful features. I would almost certainly suggest this method based on what you have told us...

2. Check the techniques here: http://www.sommarskog.se/arrays-in-sql.html

then you can take the string 'Brain Boom' and put it in a table form like:

value
--
Brain
Boom

Then join to the table

select key, count(*)
from table
join <tableofvalues> as tbl
on table.columnName like '%' + tbl.value + '%'
group by key

Then you can see the rows that have the most matches.

Constant Errors on Simple Sums... Why?

There seems to be a concept I'm not grasping. I don't understand why
I can't get a simple sum...
I have a singe table report based on a single dataset and all I want
to do is summarize some financials at 4 group levels.
I get both of these errors for every Sum expression in a report:
"A value expression used for the report parameter
'=Sum(Fields!Number.Value ,"GroupName")' includes an aggregate
function. Aggregate functions cannot be used in report parameter
expressions."
"The field expression for the data set ?DatasetName' has a scope
parameter that is not valid for an aggregate function. The scope
parameter must be set to a string constant that is equal to either the
name of a containing group, the name of a containing data region, or
the name of a data set."
I have only 2 parameters in the report, fiscalyear(int) and
fiscalperiod(int).
Half of my Sums aggregate YTD figures that do not reference either of
the parameters.
The other half aggregate MTD figures that are dependent on the
fiscalperiod parameter.
I've tried putting the expression directly into the appropriate text
field, and I have tried making the Sum expressions their own fields
and dropping those fields into the table. Nothing works.
Can anyone explain?
Thanks,
JodyHave you added groups to the table on the form? You need groups. Go to the
footer for the group. Use the expression builder to put the appropriate
values. If you are approaching it correctly it should be very straight
forward.
HTH,
Bruce L-C
"JodyT" <datagal@.msn.com> wrote in message
news:f9d864c3.0408171120.48b2b30b@.posting.google.com...
> There seems to be a concept I'm not grasping. I don't understand why
> I can't get a simple sum...
> I have a singe table report based on a single dataset and all I want
> to do is summarize some financials at 4 group levels.
> I get both of these errors for every Sum expression in a report:
> "A value expression used for the report parameter
> '=Sum(Fields!Number.Value ,"GroupName")' includes an aggregate
> function. Aggregate functions cannot be used in report parameter
> expressions."
> "The field expression for the data set 'DatasetName' has a scope
> parameter that is not valid for an aggregate function. The scope
> parameter must be set to a string constant that is equal to either the
> name of a containing group, the name of a containing data region, or
> the name of a data set."
> I have only 2 parameters in the report, fiscalyear(int) and
> fiscalperiod(int).
> Half of my Sums aggregate YTD figures that do not reference either of
> the parameters.
> The other half aggregate MTD figures that are dependent on the
> fiscalperiod parameter.
> I've tried putting the expression directly into the appropriate text
> field, and I have tried making the Sum expressions their own fields
> and dropping those fields into the table. Nothing works.
> Can anyone explain?
> Thanks,
> Jody|||All of the groups are there..
After some experimentation, I found that you don't actually have to
specify the scope in an aggregate in a table, and that helped.
Another part of the problem is that I'm getting inconsistent results
from my Preview pane and from the Debug preview window. The Debug is
generally right and the Preview if often wrong, even after I do a
rebuild.
Things are going better, but still far from what I had hoped for.
Thanks,
Jody
"Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message news:<#nIqvFJhEHA.644@.tk2msftngp13.phx.gbl>...
> Have you added groups to the table on the form? You need groups. Go to the
> footer for the group. Use the expression builder to put the appropriate
> values. If you are approaching it correctly it should be very straight
> forward.
> HTH,
> Bruce L-C
> "JodyT" <datagal@.msn.com> wrote in message
> news:f9d864c3.0408171120.48b2b30b@.posting.google.com...
> > There seems to be a concept I'm not grasping. I don't understand why
> > I can't get a simple sum...
> >
> > I have a singe table report based on a single dataset and all I want
> > to do is summarize some financials at 4 group levels.
> > I get both of these errors for every Sum expression in a report:
> >
> > "A value expression used for the report parameter
> > '=Sum(Fields!Number.Value ,"GroupName")' includes an aggregate
> > function. Aggregate functions cannot be used in report parameter
> > expressions."
> >
> > "The field expression for the data set 'DatasetName' has a scope
> > parameter that is not valid for an aggregate function. The scope
> > parameter must be set to a string constant that is equal to either the
> > name of a containing group, the name of a containing data region, or
> > the name of a data set."
> >
> > I have only 2 parameters in the report, fiscalyear(int) and
> > fiscalperiod(int).
> > Half of my Sums aggregate YTD figures that do not reference either of
> > the parameters.
> > The other half aggregate MTD figures that are dependent on the
> > fiscalperiod parameter.
> >
> > I've tried putting the expression directly into the appropriate text
> > field, and I have tried making the Sum expressions their own fields
> > and dropping those fields into the table. Nothing works.
> >
> > Can anyone explain?
> >
> > Thanks,
> > Jody|||The groups are all there.
After some monkeying around I discovered that you shouldn't specify
the scope for an aggregate in a table. Tha helped.
Another problem is that I get different results on the VS Preview Pane
and the Debug preview window. The window is right, but the pane if
often wrong, even with a rebuild
Thanks,
Jody
"Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message news:<#nIqvFJhEHA.644@.tk2msftngp13.phx.gbl>...
> Have you added groups to the table on the form? You need groups. Go to the
> footer for the group. Use the expression builder to put the appropriate
> values. If you are approaching it correctly it should be very straight
> forward.
> HTH,
> Bruce L-C
> "JodyT" <datagal@.msn.com> wrote in message
> news:f9d864c3.0408171120.48b2b30b@.posting.google.com...
> > There seems to be a concept I'm not grasping. I don't understand why
> > I can't get a simple sum...
> >
> > I have a singe table report based on a single dataset and all I want
> > to do is summarize some financials at 4 group levels.
> > I get both of these errors for every Sum expression in a report:
> >
> > "A value expression used for the report parameter
> > '=Sum(Fields!Number.Value ,"GroupName")' includes an aggregate
> > function. Aggregate functions cannot be used in report parameter
> > expressions."
> >
> > "The field expression for the data set 'DatasetName' has a scope
> > parameter that is not valid for an aggregate function. The scope
> > parameter must be set to a string constant that is equal to either the
> > name of a containing group, the name of a containing data region, or
> > the name of a data set."
> >
> > I have only 2 parameters in the report, fiscalyear(int) and
> > fiscalperiod(int).
> > Half of my Sums aggregate YTD figures that do not reference either of
> > the parameters.
> > The other half aggregate MTD figures that are dependent on the
> > fiscalperiod parameter.
> >
> > I've tried putting the expression directly into the appropriate text
> > field, and I have tried making the Sum expressions their own fields
> > and dropping those fields into the table. Nothing works.
> >
> > Can anyone explain?
> >
> > Thanks,
> > Jody|||The groups are all there.
After some monkeying around I discovered that you shouldn't specify
the scope for an aggregate in a table. That helped.
Another problem is that I get different results on the VS Preview Pane
and the Debug preview window. The window is right, but the pane if
often wrong, even with a rebuild
Thanks,
Jody
"Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message news:<#nIqvFJhEHA.644@.tk2msftngp13.phx.gbl>...
> Have you added groups to the table on the form? You need groups. Go to the
> footer for the group. Use the expression builder to put the appropriate
> values. If you are approaching it correctly it should be very straight
> forward.
> HTH,
> Bruce L-C
> "JodyT" <datagal@.msn.com> wrote in message
> news:f9d864c3.0408171120.48b2b30b@.posting.google.com...
> > There seems to be a concept I'm not grasping. I don't understand why
> > I can't get a simple sum...
> >
> > I have a singe table report based on a single dataset and all I want
> > to do is summarize some financials at 4 group levels.
> > I get both of these errors for every Sum expression in a report:
> >
> > "A value expression used for the report parameter
> > '=Sum(Fields!Number.Value ,"GroupName")' includes an aggregate
> > function. Aggregate functions cannot be used in report parameter
> > expressions."
> >
> > "The field expression for the data set 'DatasetName' has a scope
> > parameter that is not valid for an aggregate function. The scope
> > parameter must be set to a string constant that is equal to either the
> > name of a containing group, the name of a containing data region, or
> > the name of a data set."
> >
> > I have only 2 parameters in the report, fiscalyear(int) and
> > fiscalperiod(int).
> > Half of my Sums aggregate YTD figures that do not reference either of
> > the parameters.
> > The other half aggregate MTD figures that are dependent on the
> > fiscalperiod parameter.
> >
> > I've tried putting the expression directly into the appropriate text
> > field, and I have tried making the Sum expressions their own fields
> > and dropping those fields into the table. Nothing works.
> >
> > Can anyone explain?
> >
> > Thanks,
> > Jody

Constant CPU usage on server when using SQL Server 2005 Management Studio

I have recently installed SQL Server 2005 (Developer Ed) + SP1 onto a VMWare based Windows 2003 + SP1 server.

SQL Server works fine when connecting to it using Mangement Studio on Windows XP.

However, I have noticed strange CPU usage on the server which seems to be caused by Management Studio (either directly or indirectly).

When no-one is connecting to the server using Management Studio, the server happily ticks along with CPU usage around 1-5% range. However, as soon as someone connects to the SQL Server instance using Management Studio the CPU usage begin to go up and down constantly.

The CPU usage ranges from 5-50% and it goes up and down (fairly regularly) every few seconds. It does this even when nothing is actually being done in Management Studio. The moment Management Studio is closed, the CPU usage goes back to normal.

The processes on the server that appear to be causing the CPU spikes are services.exe and wmiprvse.exe.

On a possibly connected note (though possibly not), the Security log in the server's Event Viewer shows that there are logins occuring every minute or so (most of the logins are from my account).

Any ideas?

tpenrose:

The processes on the server that appear to be causing the CPU spikes are services.exe and wmiprvse.exe.

I'm not sure how Management Studio cause the CPU issue, on my machine it works fine. But the Management Studio process is sqlwb.exe, you can use SQL Server Performance Tool Profiler to trace what's SQL doing when you connect via Management Studio

|||

Thanks, but I've tried Profiler and no joy.

A basic trace just shows the standard "Existing Connections" and nothing more. There's no apparant activity going on as far as SQL Server is concerned.

I should just clarify that it's not sqlwb.exe (which is on the client) that's showing the CPU usage, but services.exe and wmiprvse.exe on the server that seem to be causing the CPU spikes.

Hope someone can help otherwise I guess it back to good old 2000. Shame though 'cause 2005 "looked" really promising. Don't think I'll be able to wait another 6-12 months for Service Pack 2.

Constant CPU usage on server when using SQL Server 2005 Management Studio

I have recently installed SQL Server 2005 (Developer Ed) + SP1 onto a VMWare based Windows 2003 + SP1 server.

SQL Server works fine when connecting to it using Mangement Studio on Windows XP.

However, I have noticed strange CPU usage on the server which seems to be caused by Management Studio (either directly or indirectly).

When no-one is connecting to the server using Management Studio, the server happily ticks along with CPU usage around 1-5% range. However, as soon as someone connects to the SQL Server instance using Management Studio the CPU usage begin to go up and down constantly.

The CPU usage ranges from 5-50% and it goes up and down (fairly regularly) every few seconds. It does this even when nothing is actually being done in Management Studio. The moment Management Studio is closed, the CPU usage goes back to normal.

The processes on the server that appear to be causing the CPU spikes are services.exe and wmiprvse.exe.

On a possibly connected note (though possibly not), the Security log in the server's Event Viewer shows that there are logins occuring every minute or so (most of the logins are from my account).

Any ideas?

I have a very similar problem as well though my details are a bit different.

I have a development PC that is running Vista and has SQL Server 2005 Developer w/SP2 installed and running. When I run SQL Management Studio locally on that pc, then services.exe starts to peg my cpu. I have a dual-core CPU and it seems that this process gets about 33% of my cycles over time (have left it running for 24 hours idle and it got about 8 hrs of cpu time). However, the interesting thing is that I can run SQL Management Studio on a remote machine and I never see any hit to the CPU unless I'm really doing something that requires it.

Monitoring mine, it seems the CPU (dual-core) is kept at 50% utilized (1 core 100% or split across cores) for about a 3-4 second period and then there's a 1-2 second period where it drops off and it seems to repeat this cycle until I close SQL Management Studio. This happens when I do nothing other than open SQL Management Studio - with nothing open aside from my Summary tab, an empty Object Explorer, and my Registerd Servers window (which does have the local server in a list there). I have tried changing whether or not SQL Management Studio has open connections to the local database server (same with the remote SQL Mgmt Studios) and no matter what I change, it seems to not affect the end result of simply having SQL Management Studio open on the server machine pegs the CPU.

-Shane

|||

Use the PRoces explorer tool from SYSINTERNALS to see what is running in that aspect on the machine, http://www.microsoft.com/technet/sysinternals/utilities/ProcessExplorer.mspx & http://www.microsoft.com/technet/sysinternals/default.mspx fyi.

I'm not sure how it works on the VMware if at all, otherwise you can try to use the Virtual Server enviornment in this case to reproduce the scneario.

|||

I have attempted this in the past without much luck (I am assuming I am only interested in the processes that are listed as children or grandchildren to the services.exe node in the treeview). Aside from many svchost.exe processes, here is what I see on mine (remember, I'm running Vista and this is with SQL Mgmt Studio local on the db server):

WmiPrvSE.exe audiodg.exe dwm.exe 2x taskeng.exe SLsvc.exe spoolsv.exe isafe.exe (CA antivirus) mdm.exe sqlbrowser.exe sqlwriter.exe vetmsg.exe (CA antivirus) SearchIndexer.exe aspnet_state.exe ccprovsp.exe (CA antivirus) lsass.exe lsm.exe|||

Just to update this thread, I have still not found a solution to this problem. I am pretty much required to either use SQL Management Studio on another machine or give up 1/3 of my CPU - neither are always good solutions, especially when I want to run a timing trial over night and still bring my laptop home.

-Shane

|||I have the same issue - Vista, SQL Management Studio with SP1 spikes the CPU in services.exe with frequent UuidCreate calls during spike. If I start management studio with no registered servers, no CPU spikes, but as soon as I register a server or connect to a server one in object explorer, spikes start. New queries do not cause spikes, so this appears to be a caused by workbench monitoring of service status.
|||It definitely has to do with WMI / counters. When I suspend the wmiprvse process the spikes stop. For something less drastic, I tried disabling various SQL counters using exctrlst but no change. Any ideas on how to disable the WMI module causing this under vista?
|||I've the same problem... does anybody have solution ?

NodeX
|||

I have not found a solution to this problem. I continue to avoid using SQL Management Studio on that box as a work-around. Worst-case scenario is that I do run it on that PC and I assume half of my CPU is unavailable until I close the app.

|||I am testing SQL 2005 Enterprise (build 3159) on an active/passive cluster and the same things occurs. If I were to so much as open the Management Studio with the system registered from a client workstation, the cyclical CPU spikes commence immediately following the engine discovery process to display the green arrow; I don't even need to 'connect' to the server.|||.....furthermore I am using XP SP2, not Vista for the client workstations....|||

Your post indicating that you're running SQL Server Enterprise made me want to run a test but with SQL Express rather than a full-blown version, just in case that helps somebody nail this down. Well, my environment for that test ended up being messed up (multiple SQL installs on the same box) so those results weren't quite useful. BUT, what may be very useful that I just happened to accidentally discover was that even with ALL of my SQL Server services turned off, this CPU strain continued to exist. And as the previous posted mentioned, no connections need to be made. As soon as you see the little green "play" arrow or the little red "stop" box appear for the local server instance, then at that time the CPU starts getting pegged on a periodic basis (the period seems pretty consistent by looking at Task Manager over a period of time).

I hope this helps somebody find this!

-Jax

|||Any solution ? Same problem with W2K3 R2 SP2, SQL 2005 Enterprise SP2, Virtual Server 2005 R2. All updates installed.|||

I still have no solution.

|||I have this problem running SQL Management Studio on a Windows 2003 server.

Constant CPU usage on server when using SQL Server 2005 Management Studio

I have recently installed SQL Server 2005 (Developer Ed) + SP1 onto a VMWare based Windows 2003 + SP1 server.

SQL Server works fine when connecting to it using Mangement Studio on Windows XP.

However, I have noticed strange CPU usage on the server which seems to be caused by Management Studio (either directly or indirectly).

When no-one is connecting to the server using Management Studio, the server happily ticks along with CPU usage around 1-5% range. However, as soon as someone connects to the SQL Server instance using Management Studio the CPU usage begin to go up and down constantly.

The CPU usage ranges from 5-50% and it goes up and down (fairly regularly) every few seconds. It does this even when nothing is actually being done in Management Studio. The moment Management Studio is closed, the CPU usage goes back to normal.

The processes on the server that appear to be causing the CPU spikes are services.exe and wmiprvse.exe.

On a possibly connected note (though possibly not), the Security log in the server's Event Viewer shows that there are logins occuring every minute or so (most of the logins are from my account).

Any ideas?

I have a very similar problem as well though my details are a bit different.

I have a development PC that is running Vista and has SQL Server 2005 Developer w/SP2 installed and running. When I run SQL Management Studio locally on that pc, then services.exe starts to peg my cpu. I have a dual-core CPU and it seems that this process gets about 33% of my cycles over time (have left it running for 24 hours idle and it got about 8 hrs of cpu time). However, the interesting thing is that I can run SQL Management Studio on a remote machine and I never see any hit to the CPU unless I'm really doing something that requires it.

Monitoring mine, it seems the CPU (dual-core) is kept at 50% utilized (1 core 100% or split across cores) for about a 3-4 second period and then there's a 1-2 second period where it drops off and it seems to repeat this cycle until I close SQL Management Studio. This happens when I do nothing other than open SQL Management Studio - with nothing open aside from my Summary tab, an empty Object Explorer, and my Registerd Servers window (which does have the local server in a list there). I have tried changing whether or not SQL Management Studio has open connections to the local database server (same with the remote SQL Mgmt Studios) and no matter what I change, it seems to not affect the end result of simply having SQL Management Studio open on the server machine pegs the CPU.

-Shane

|||

Use the PRoces explorer tool from SYSINTERNALS to see what is running in that aspect on the machine, http://www.microsoft.com/technet/sysinternals/utilities/ProcessExplorer.mspx & http://www.microsoft.com/technet/sysinternals/default.mspx fyi.

I'm not sure how it works on the VMware if at all, otherwise you can try to use the Virtual Server enviornment in this case to reproduce the scneario.

|||

I have attempted this in the past without much luck (I am assuming I am only interested in the processes that are listed as children or grandchildren to the services.exe node in the treeview). Aside from many svchost.exe processes, here is what I see on mine (remember, I'm running Vista and this is with SQL Mgmt Studio local on the db server):

WmiPrvSE.exe audiodg.exe dwm.exe 2x taskeng.exe SLsvc.exe spoolsv.exe isafe.exe (CA antivirus) mdm.exe sqlbrowser.exe sqlwriter.exe vetmsg.exe (CA antivirus) SearchIndexer.exe aspnet_state.exe ccprovsp.exe (CA antivirus) lsass.exe lsm.exe|||

Just to update this thread, I have still not found a solution to this problem. I am pretty much required to either use SQL Management Studio on another machine or give up 1/3 of my CPU - neither are always good solutions, especially when I want to run a timing trial over night and still bring my laptop home.

-Shane

|||I have the same issue - Vista, SQL Management Studio with SP1 spikes the CPU in services.exe with frequent UuidCreate calls during spike. If I start management studio with no registered servers, no CPU spikes, but as soon as I register a server or connect to a server one in object explorer, spikes start. New queries do not cause spikes, so this appears to be a caused by workbench monitoring of service status.
|||It definitely has to do with WMI / counters. When I suspend the wmiprvse process the spikes stop. For something less drastic, I tried disabling various SQL counters using exctrlst but no change. Any ideas on how to disable the WMI module causing this under vista?
|||I've the same problem... does anybody have solution ?

NodeX
|||

I have not found a solution to this problem. I continue to avoid using SQL Management Studio on that box as a work-around. Worst-case scenario is that I do run it on that PC and I assume half of my CPU is unavailable until I close the app.

|||I am testing SQL 2005 Enterprise (build 3159) on an active/passive cluster and the same things occurs. If I were to so much as open the Management Studio with the system registered from a client workstation, the cyclical CPU spikes commence immediately following the engine discovery process to display the green arrow; I don't even need to 'connect' to the server.|||.....furthermore I am using XP SP2, not Vista for the client workstations....|||

Your post indicating that you're running SQL Server Enterprise made me want to run a test but with SQL Express rather than a full-blown version, just in case that helps somebody nail this down. Well, my environment for that test ended up being messed up (multiple SQL installs on the same box) so those results weren't quite useful. BUT, what may be very useful that I just happened to accidentally discover was that even with ALL of my SQL Server services turned off, this CPU strain continued to exist. And as the previous posted mentioned, no connections need to be made. As soon as you see the little green "play" arrow or the little red "stop" box appear for the local server instance, then at that time the CPU starts getting pegged on a periodic basis (the period seems pretty consistent by looking at Task Manager over a period of time).

I hope this helps somebody find this!

-Jax

|||Any solution ? Same problem with W2K3 R2 SP2, SQL 2005 Enterprise SP2, Virtual Server 2005 R2. All updates installed.|||

I still have no solution.

|||I have this problem running SQL Management Studio on a Windows 2003 server.

Constant CPU usage on server when using SQL Server 2005 Management Studio

I have recently installed SQL Server 2005 (Developer Ed) + SP1 onto a VMWare based Windows 2003 + SP1 server.

SQL Server works fine when connecting to it using Mangement Studio on Windows XP.

However, I have noticed strange CPU usage on the server which seems to be caused by Management Studio (either directly or indirectly).

When no-one is connecting to the server using Management Studio, the server happily ticks along with CPU usage around 1-5% range. However, as soon as someone connects to the SQL Server instance using Management Studio the CPU usage begin to go up and down constantly.

The CPU usage ranges from 5-50% and it goes up and down (fairly regularly) every few seconds. It does this even when nothing is actually being done in Management Studio. The moment Management Studio is closed, the CPU usage goes back to normal.

The processes on the server that appear to be causing the CPU spikes are services.exe and wmiprvse.exe.

On a possibly connected note (though possibly not), the Security log in the server's Event Viewer shows that there are logins occuring every minute or so (most of the logins are from my account).

Any ideas?

I have a very similar problem as well though my details are a bit different.

I have a development PC that is running Vista and has SQL Server 2005 Developer w/SP2 installed and running. When I run SQL Management Studio locally on that pc, then services.exe starts to peg my cpu. I have a dual-core CPU and it seems that this process gets about 33% of my cycles over time (have left it running for 24 hours idle and it got about 8 hrs of cpu time). However, the interesting thing is that I can run SQL Management Studio on a remote machine and I never see any hit to the CPU unless I'm really doing something that requires it.

Monitoring mine, it seems the CPU (dual-core) is kept at 50% utilized (1 core 100% or split across cores) for about a 3-4 second period and then there's a 1-2 second period where it drops off and it seems to repeat this cycle until I close SQL Management Studio. This happens when I do nothing other than open SQL Management Studio - with nothing open aside from my Summary tab, an empty Object Explorer, and my Registerd Servers window (which does have the local server in a list there). I have tried changing whether or not SQL Management Studio has open connections to the local database server (same with the remote SQL Mgmt Studios) and no matter what I change, it seems to not affect the end result of simply having SQL Management Studio open on the server machine pegs the CPU.

-Shane

|||

Use the PRoces explorer tool from SYSINTERNALS to see what is running in that aspect on the machine, http://www.microsoft.com/technet/sysinternals/utilities/ProcessExplorer.mspx & http://www.microsoft.com/technet/sysinternals/default.mspx fyi.

I'm not sure how it works on the VMware if at all, otherwise you can try to use the Virtual Server enviornment in this case to reproduce the scneario.

|||

I have attempted this in the past without much luck (I am assuming I am only interested in the processes that are listed as children or grandchildren to the services.exe node in the treeview). Aside from many svchost.exe processes, here is what I see on mine (remember, I'm running Vista and this is with SQL Mgmt Studio local on the db server):

WmiPrvSE.exe audiodg.exe dwm.exe 2x taskeng.exe SLsvc.exe spoolsv.exe isafe.exe (CA antivirus) mdm.exe sqlbrowser.exe sqlwriter.exe vetmsg.exe (CA antivirus) SearchIndexer.exe aspnet_state.exe ccprovsp.exe (CA antivirus) lsass.exe lsm.exe|||

Just to update this thread, I have still not found a solution to this problem. I am pretty much required to either use SQL Management Studio on another machine or give up 1/3 of my CPU - neither are always good solutions, especially when I want to run a timing trial over night and still bring my laptop home.

-Shane

|||I have the same issue - Vista, SQL Management Studio with SP1 spikes the CPU in services.exe with frequent UuidCreate calls during spike. If I start management studio with no registered servers, no CPU spikes, but as soon as I register a server or connect to a server one in object explorer, spikes start. New queries do not cause spikes, so this appears to be a caused by workbench monitoring of service status.
|||It definitely has to do with WMI / counters. When I suspend the wmiprvse process the spikes stop. For something less drastic, I tried disabling various SQL counters using exctrlst but no change. Any ideas on how to disable the WMI module causing this under vista?
|||I've the same problem... does anybody have solution ?

NodeX
|||

I have not found a solution to this problem. I continue to avoid using SQL Management Studio on that box as a work-around. Worst-case scenario is that I do run it on that PC and I assume half of my CPU is unavailable until I close the app.

|||I am testing SQL 2005 Enterprise (build 3159) on an active/passive cluster and the same things occurs. If I were to so much as open the Management Studio with the system registered from a client workstation, the cyclical CPU spikes commence immediately following the engine discovery process to display the green arrow; I don't even need to 'connect' to the server.|||.....furthermore I am using XP SP2, not Vista for the client workstations....|||

Your post indicating that you're running SQL Server Enterprise made me want to run a test but with SQL Express rather than a full-blown version, just in case that helps somebody nail this down. Well, my environment for that test ended up being messed up (multiple SQL installs on the same box) so those results weren't quite useful. BUT, what may be very useful that I just happened to accidentally discover was that even with ALL of my SQL Server services turned off, this CPU strain continued to exist. And as the previous posted mentioned, no connections need to be made. As soon as you see the little green "play" arrow or the little red "stop" box appear for the local server instance, then at that time the CPU starts getting pegged on a periodic basis (the period seems pretty consistent by looking at Task Manager over a period of time).

I hope this helps somebody find this!

-Jax

|||Any solution ? Same problem with W2K3 R2 SP2, SQL 2005 Enterprise SP2, Virtual Server 2005 R2. All updates installed.|||

I still have no solution.

|||I have this problem running SQL Management Studio on a Windows 2003 server.

Constant CPU usage on server when using SQL Server 2005 Management Studio

I have recently installed SQL Server 2005 (Developer Ed) + SP1 onto a VMWare based Windows 2003 + SP1 server.

SQL Server works fine when connecting to it using Mangement Studio on Windows XP.

However, I have noticed strange CPU usage on the server which seems to be caused by Management Studio (either directly or indirectly).

When no-one is connecting to the server using Management Studio, the server happily ticks along with CPU usage around 1-5% range. However, as soon as someone connects to the SQL Server instance using Management Studio the CPU usage begin to go up and down constantly.

The CPU usage ranges from 5-50% and it goes up and down (fairly regularly) every few seconds. It does this even when nothing is actually being done in Management Studio. The moment Management Studio is closed, the CPU usage goes back to normal.

The processes on the server that appear to be causing the CPU spikes are services.exe and wmiprvse.exe.

On a possibly connected note (though possibly not), the Security log in the server's Event Viewer shows that there are logins occuring every minute or so (most of the logins are from my account).

Any ideas?

I have a very similar problem as well though my details are a bit different.

I have a development PC that is running Vista and has SQL Server 2005 Developer w/SP2 installed and running. When I run SQL Management Studio locally on that pc, then services.exe starts to peg my cpu. I have a dual-core CPU and it seems that this process gets about 33% of my cycles over time (have left it running for 24 hours idle and it got about 8 hrs of cpu time). However, the interesting thing is that I can run SQL Management Studio on a remote machine and I never see any hit to the CPU unless I'm really doing something that requires it.

Monitoring mine, it seems the CPU (dual-core) is kept at 50% utilized (1 core 100% or split across cores) for about a 3-4 second period and then there's a 1-2 second period where it drops off and it seems to repeat this cycle until I close SQL Management Studio. This happens when I do nothing other than open SQL Management Studio - with nothing open aside from my Summary tab, an empty Object Explorer, and my Registerd Servers window (which does have the local server in a list there). I have tried changing whether or not SQL Management Studio has open connections to the local database server (same with the remote SQL Mgmt Studios) and no matter what I change, it seems to not affect the end result of simply having SQL Management Studio open on the server machine pegs the CPU.

-Shane

|||

Use the PRoces explorer tool from SYSINTERNALS to see what is running in that aspect on the machine, http://www.microsoft.com/technet/sysinternals/utilities/ProcessExplorer.mspx & http://www.microsoft.com/technet/sysinternals/default.mspx fyi.

I'm not sure how it works on the VMware if at all, otherwise you can try to use the Virtual Server enviornment in this case to reproduce the scneario.

|||

I have attempted this in the past without much luck (I am assuming I am only interested in the processes that are listed as children or grandchildren to the services.exe node in the treeview). Aside from many svchost.exe processes, here is what I see on mine (remember, I'm running Vista and this is with SQL Mgmt Studio local on the db server):

WmiPrvSE.exe audiodg.exe dwm.exe 2x taskeng.exe SLsvc.exe spoolsv.exe isafe.exe (CA antivirus) mdm.exe sqlbrowser.exe sqlwriter.exe vetmsg.exe (CA antivirus) SearchIndexer.exe aspnet_state.exe ccprovsp.exe (CA antivirus) lsass.exe lsm.exe|||

Just to update this thread, I have still not found a solution to this problem. I am pretty much required to either use SQL Management Studio on another machine or give up 1/3 of my CPU - neither are always good solutions, especially when I want to run a timing trial over night and still bring my laptop home.

-Shane

|||I have the same issue - Vista, SQL Management Studio with SP1 spikes the CPU in services.exe with frequent UuidCreate calls during spike. If I start management studio with no registered servers, no CPU spikes, but as soon as I register a server or connect to a server one in object explorer, spikes start. New queries do not cause spikes, so this appears to be a caused by workbench monitoring of service status.
|||It definitely has to do with WMI / counters. When I suspend the wmiprvse process the spikes stop. For something less drastic, I tried disabling various SQL counters using exctrlst but no change. Any ideas on how to disable the WMI module causing this under vista?
|||I've the same problem... does anybody have solution ?

NodeX
|||

I have not found a solution to this problem. I continue to avoid using SQL Management Studio on that box as a work-around. Worst-case scenario is that I do run it on that PC and I assume half of my CPU is unavailable until I close the app.

|||I am testing SQL 2005 Enterprise (build 3159) on an active/passive cluster and the same things occurs. If I were to so much as open the Management Studio with the system registered from a client workstation, the cyclical CPU spikes commence immediately following the engine discovery process to display the green arrow; I don't even need to 'connect' to the server.|||.....furthermore I am using XP SP2, not Vista for the client workstations....|||

Your post indicating that you're running SQL Server Enterprise made me want to run a test but with SQL Express rather than a full-blown version, just in case that helps somebody nail this down. Well, my environment for that test ended up being messed up (multiple SQL installs on the same box) so those results weren't quite useful. BUT, what may be very useful that I just happened to accidentally discover was that even with ALL of my SQL Server services turned off, this CPU strain continued to exist. And as the previous posted mentioned, no connections need to be made. As soon as you see the little green "play" arrow or the little red "stop" box appear for the local server instance, then at that time the CPU starts getting pegged on a periodic basis (the period seems pretty consistent by looking at Task Manager over a period of time).

I hope this helps somebody find this!

-Jax

|||Any solution ? Same problem with W2K3 R2 SP2, SQL 2005 Enterprise SP2, Virtual Server 2005 R2. All updates installed.|||

I still have no solution.

|||I have this problem running SQL Management Studio on a Windows 2003 server.

Constant CPU usage on server when using SQL Server 2005 Management Studio

I have recently installed SQL Server 2005 (Developer Ed) + SP1 onto a VMWare based Windows 2003 + SP1 server.

SQL Server works fine when connecting to it using Mangement Studio on Windows XP.

However, I have noticed strange CPU usage on the server which seems to be caused by Management Studio (either directly or indirectly).

When no-one is connecting to the server using Management Studio, the server happily ticks along with CPU usage around 1-5% range. However, as soon as someone connects to the SQL Server instance using Management Studio the CPU usage begin to go up and down constantly.

The CPU usage ranges from 5-50% and it goes up and down (fairly regularly) every few seconds. It does this even when nothing is actually being done in Management Studio. The moment Management Studio is closed, the CPU usage goes back to normal.

The processes on the server that appear to be causing the CPU spikes are services.exe and wmiprvse.exe.

On a possibly connected note (though possibly not), the Security log in the server's Event Viewer shows that there are logins occuring every minute or so (most of the logins are from my account).

Any ideas?

I have a very similar problem as well though my details are a bit different.

I have a development PC that is running Vista and has SQL Server 2005 Developer w/SP2 installed and running. When I run SQL Management Studio locally on that pc, then services.exe starts to peg my cpu. I have a dual-core CPU and it seems that this process gets about 33% of my cycles over time (have left it running for 24 hours idle and it got about 8 hrs of cpu time). However, the interesting thing is that I can run SQL Management Studio on a remote machine and I never see any hit to the CPU unless I'm really doing something that requires it.

Monitoring mine, it seems the CPU (dual-core) is kept at 50% utilized (1 core 100% or split across cores) for about a 3-4 second period and then there's a 1-2 second period where it drops off and it seems to repeat this cycle until I close SQL Management Studio. This happens when I do nothing other than open SQL Management Studio - with nothing open aside from my Summary tab, an empty Object Explorer, and my Registerd Servers window (which does have the local server in a list there). I have tried changing whether or not SQL Management Studio has open connections to the local database server (same with the remote SQL Mgmt Studios) and no matter what I change, it seems to not affect the end result of simply having SQL Management Studio open on the server machine pegs the CPU.

-Shane

|||

Use the PRoces explorer tool from SYSINTERNALS to see what is running in that aspect on the machine, http://www.microsoft.com/technet/sysinternals/utilities/ProcessExplorer.mspx & http://www.microsoft.com/technet/sysinternals/default.mspx fyi.

I'm not sure how it works on the VMware if at all, otherwise you can try to use the Virtual Server enviornment in this case to reproduce the scneario.

|||

I have attempted this in the past without much luck (I am assuming I am only interested in the processes that are listed as children or grandchildren to the services.exe node in the treeview). Aside from many svchost.exe processes, here is what I see on mine (remember, I'm running Vista and this is with SQL Mgmt Studio local on the db server):

WmiPrvSE.exe audiodg.exe dwm.exe 2x taskeng.exe SLsvc.exe spoolsv.exe isafe.exe (CA antivirus) mdm.exe sqlbrowser.exe sqlwriter.exe vetmsg.exe (CA antivirus) SearchIndexer.exe aspnet_state.exe ccprovsp.exe (CA antivirus) lsass.exe lsm.exe|||

Just to update this thread, I have still not found a solution to this problem. I am pretty much required to either use SQL Management Studio on another machine or give up 1/3 of my CPU - neither are always good solutions, especially when I want to run a timing trial over night and still bring my laptop home.

-Shane

|||I have the same issue - Vista, SQL Management Studio with SP1 spikes the CPU in services.exe with frequent UuidCreate calls during spike. If I start management studio with no registered servers, no CPU spikes, but as soon as I register a server or connect to a server one in object explorer, spikes start. New queries do not cause spikes, so this appears to be a caused by workbench monitoring of service status.
|||It definitely has to do with WMI / counters. When I suspend the wmiprvse process the spikes stop. For something less drastic, I tried disabling various SQL counters using exctrlst but no change. Any ideas on how to disable the WMI module causing this under vista?
|||I've the same problem... does anybody have solution ?

NodeX
|||

I have not found a solution to this problem. I continue to avoid using SQL Management Studio on that box as a work-around. Worst-case scenario is that I do run it on that PC and I assume half of my CPU is unavailable until I close the app.

|||I am testing SQL 2005 Enterprise (build 3159) on an active/passive cluster and the same things occurs. If I were to so much as open the Management Studio with the system registered from a client workstation, the cyclical CPU spikes commence immediately following the engine discovery process to display the green arrow; I don't even need to 'connect' to the server.|||.....furthermore I am using XP SP2, not Vista for the client workstations....|||

Your post indicating that you're running SQL Server Enterprise made me want to run a test but with SQL Express rather than a full-blown version, just in case that helps somebody nail this down. Well, my environment for that test ended up being messed up (multiple SQL installs on the same box) so those results weren't quite useful. BUT, what may be very useful that I just happened to accidentally discover was that even with ALL of my SQL Server services turned off, this CPU strain continued to exist. And as the previous posted mentioned, no connections need to be made. As soon as you see the little green "play" arrow or the little red "stop" box appear for the local server instance, then at that time the CPU starts getting pegged on a periodic basis (the period seems pretty consistent by looking at Task Manager over a period of time).

I hope this helps somebody find this!

-Jax

|||Any solution ? Same problem with W2K3 R2 SP2, SQL 2005 Enterprise SP2, Virtual Server 2005 R2. All updates installed.|||

I still have no solution.

|||I have this problem running SQL Management Studio on a Windows 2003 server.

Constant CPU usage on server when using SQL Server 2005 Management Studio

I have recently installed SQL Server 2005 (Developer Ed) + SP1 onto a VMWare based Windows 2003 + SP1 server.

SQL Server works fine when connecting to it using Mangement Studio on Windows XP.

However, I have noticed strange CPU usage on the server which seems to be caused by Management Studio (either directly or indirectly).

When no-one is connecting to the server using Management Studio, the server happily ticks along with CPU usage around 1-5% range. However, as soon as someone connects to the SQL Server instance using Management Studio the CPU usage begin to go up and down constantly.

The CPU usage ranges from 5-50% and it goes up and down (fairly regularly) every few seconds. It does this even when nothing is actually being done in Management Studio. The moment Management Studio is closed, the CPU usage goes back to normal.

The processes on the server that appear to be causing the CPU spikes are services.exe and wmiprvse.exe.

On a possibly connected note (though possibly not), the Security log in the server's Event Viewer shows that there are logins occuring every minute or so (most of the logins are from my account).

Any ideas?

I have a very similar problem as well though my details are a bit different.

I have a development PC that is running Vista and has SQL Server 2005 Developer w/SP2 installed and running. When I run SQL Management Studio locally on that pc, then services.exe starts to peg my cpu. I have a dual-core CPU and it seems that this process gets about 33% of my cycles over time (have left it running for 24 hours idle and it got about 8 hrs of cpu time). However, the interesting thing is that I can run SQL Management Studio on a remote machine and I never see any hit to the CPU unless I'm really doing something that requires it.

Monitoring mine, it seems the CPU (dual-core) is kept at 50% utilized (1 core 100% or split across cores) for about a 3-4 second period and then there's a 1-2 second period where it drops off and it seems to repeat this cycle until I close SQL Management Studio. This happens when I do nothing other than open SQL Management Studio - with nothing open aside from my Summary tab, an empty Object Explorer, and my Registerd Servers window (which does have the local server in a list there). I have tried changing whether or not SQL Management Studio has open connections to the local database server (same with the remote SQL Mgmt Studios) and no matter what I change, it seems to not affect the end result of simply having SQL Management Studio open on the server machine pegs the CPU.

-Shane

|||

Use the PRoces explorer tool from SYSINTERNALS to see what is running in that aspect on the machine, http://www.microsoft.com/technet/sysinternals/utilities/ProcessExplorer.mspx & http://www.microsoft.com/technet/sysinternals/default.mspx fyi.

I'm not sure how it works on the VMware if at all, otherwise you can try to use the Virtual Server enviornment in this case to reproduce the scneario.

|||

I have attempted this in the past without much luck (I am assuming I am only interested in the processes that are listed as children or grandchildren to the services.exe node in the treeview). Aside from many svchost.exe processes, here is what I see on mine (remember, I'm running Vista and this is with SQL Mgmt Studio local on the db server):

WmiPrvSE.exe audiodg.exe dwm.exe 2x taskeng.exe SLsvc.exe spoolsv.exe isafe.exe (CA antivirus) mdm.exe sqlbrowser.exe sqlwriter.exe vetmsg.exe (CA antivirus) SearchIndexer.exe aspnet_state.exe ccprovsp.exe (CA antivirus) lsass.exe lsm.exe|||

Just to update this thread, I have still not found a solution to this problem. I am pretty much required to either use SQL Management Studio on another machine or give up 1/3 of my CPU - neither are always good solutions, especially when I want to run a timing trial over night and still bring my laptop home.

-Shane

|||I have the same issue - Vista, SQL Management Studio with SP1 spikes the CPU in services.exe with frequent UuidCreate calls during spike. If I start management studio with no registered servers, no CPU spikes, but as soon as I register a server or connect to a server one in object explorer, spikes start. New queries do not cause spikes, so this appears to be a caused by workbench monitoring of service status.
|||It definitely has to do with WMI / counters. When I suspend thewmiprvse process the spikes stop. For something less drastic, I tried disabling various SQL counters using exctrlst but no change. Any ideas on how to disable the WMI module causing this under vista?
|||I've the same problem... does anybody have solution ?

NodeX
|||

I have not found a solution to this problem. I continue to avoid using SQL Management Studio on that box as a work-around. Worst-case scenario is that I do run it on that PC and I assume half of my CPU is unavailable until I close the app.

|||I am testing SQL 2005 Enterprise (build 3159) on an active/passive cluster and the same things occurs. If I were to so much as open the Management Studio with the system registered from a client workstation, the cyclical CPU spikes commence immediately following the engine discovery process to display the green arrow; I don't even need to 'connect' to the server.|||.....furthermore I am using XP SP2, not Vista for the client workstations....|||

Your post indicating that you're running SQL Server Enterprise made me want to run a test but with SQL Express rather than a full-blown version, just in case that helps somebody nail this down. Well, my environment for that test ended up being messed up (multiple SQL installs on the same box) so those results weren't quite useful. BUT, what may be very useful that I just happened to accidentally discover was that even with ALL of my SQL Server services turned off, this CPU strain continued to exist. And as the previous posted mentioned, no connections need to be made. As soon as you see the little green "play" arrow or the little red "stop" box appear for the local server instance, then at that time the CPU starts getting pegged on a periodic basis (the period seems pretty consistent by looking at Task Manager over a period of time).

I hope this helps somebody find this!

-Jax

|||Any solution ? Same problem with W2K3 R2 SP2, SQL 2005 Enterprise SP2, Virtual Server 2005 R2. All updates installed.|||

I still have no solution.

|||I have this problem running SQL Management Studio on a Windows 2003 server.

Constant CPU usage on server when using SQL Server 2005 Management Studio

I have recently installed SQL Server 2005 (Developer Ed) + SP1 onto a VMWare based Windows 2003 + SP1 server.

SQL Server works fine when connecting to it using Mangement Studio on Windows XP.

However, I have noticed strange CPU usage on the server which seems to be caused by Management Studio (either directly or indirectly).

When no-one is connecting to the server using Management Studio, the server happily ticks along with CPU usage around 1-5% range. However, as soon as someone connects to the SQL Server instance using Management Studio the CPU usage begin to go up and down constantly.

The CPU usage ranges from 5-50% and it goes up and down (fairly regularly) every few seconds. It does this even when nothing is actually being done in Management Studio. The moment Management Studio is closed, the CPU usage goes back to normal.

The processes on the server that appear to be causing the CPU spikes are services.exe and wmiprvse.exe.

On a possibly connected note (though possibly not), the Security log in the server's Event Viewer shows that there are logins occuring every minute or so (most of the logins are from my account).

Any ideas?

I have a very similar problem as well though my details are a bit different.

I have a development PC that is running Vista and has SQL Server 2005 Developer w/SP2 installed and running. When I run SQL Management Studio locally on that pc, then services.exe starts to peg my cpu. I have a dual-core CPU and it seems that this process gets about 33% of my cycles over time (have left it running for 24 hours idle and it got about 8 hrs of cpu time). However, the interesting thing is that I can run SQL Management Studio on a remote machine and I never see any hit to the CPU unless I'm really doing something that requires it.

Monitoring mine, it seems the CPU (dual-core) is kept at 50% utilized (1 core 100% or split across cores) for about a 3-4 second period and then there's a 1-2 second period where it drops off and it seems to repeat this cycle until I close SQL Management Studio. This happens when I do nothing other than open SQL Management Studio - with nothing open aside from my Summary tab, an empty Object Explorer, and my Registerd Servers window (which does have the local server in a list there). I have tried changing whether or not SQL Management Studio has open connections to the local database server (same with the remote SQL Mgmt Studios) and no matter what I change, it seems to not affect the end result of simply having SQL Management Studio open on the server machine pegs the CPU.

-Shane

|||

Use the PRoces explorer tool from SYSINTERNALS to see what is running in that aspect on the machine, http://www.microsoft.com/technet/sysinternals/utilities/ProcessExplorer.mspx & http://www.microsoft.com/technet/sysinternals/default.mspx fyi.

I'm not sure how it works on the VMware if at all, otherwise you can try to use the Virtual Server enviornment in this case to reproduce the scneario.

|||

I have attempted this in the past without much luck (I am assuming I am only interested in the processes that are listed as children or grandchildren to the services.exe node in the treeview). Aside from many svchost.exe processes, here is what I see on mine (remember, I'm running Vista and this is with SQL Mgmt Studio local on the db server):

WmiPrvSE.exe audiodg.exe dwm.exe 2x taskeng.exe SLsvc.exe spoolsv.exe isafe.exe (CA antivirus) mdm.exe sqlbrowser.exe sqlwriter.exe vetmsg.exe (CA antivirus) SearchIndexer.exe aspnet_state.exe ccprovsp.exe (CA antivirus) lsass.exe lsm.exe|||

Just to update this thread, I have still not found a solution to this problem. I am pretty much required to either use SQL Management Studio on another machine or give up 1/3 of my CPU - neither are always good solutions, especially when I want to run a timing trial over night and still bring my laptop home.

-Shane

|||I have the same issue - Vista, SQL Management Studio with SP1 spikes the CPU in services.exe with frequent UuidCreate calls during spike. If I start management studio with no registered servers, no CPU spikes, but as soon as I register a server or connect to a server one in object explorer, spikes start. New queries do not cause spikes, so this appears to be a caused by workbench monitoring of service status.
|||It definitely has to do with WMI / counters. When I suspend the wmiprvse process the spikes stop. For something less drastic, I tried disabling various SQL counters using exctrlst but no change. Any ideas on how to disable the WMI module causing this under vista?
|||I've the same problem... does anybody have solution ?

NodeX
|||

I have not found a solution to this problem. I continue to avoid using SQL Management Studio on that box as a work-around. Worst-case scenario is that I do run it on that PC and I assume half of my CPU is unavailable until I close the app.

|||I am testing SQL 2005 Enterprise (build 3159) on an active/passive cluster and the same things occurs. If I were to so much as open the Management Studio with the system registered from a client workstation, the cyclical CPU spikes commence immediately following the engine discovery process to display the green arrow; I don't even need to 'connect' to the server.|||.....furthermore I am using XP SP2, not Vista for the client workstations....|||

Your post indicating that you're running SQL Server Enterprise made me want to run a test but with SQL Express rather than a full-blown version, just in case that helps somebody nail this down. Well, my environment for that test ended up being messed up (multiple SQL installs on the same box) so those results weren't quite useful. BUT, what may be very useful that I just happened to accidentally discover was that even with ALL of my SQL Server services turned off, this CPU strain continued to exist. And as the previous posted mentioned, no connections need to be made. As soon as you see the little green "play" arrow or the little red "stop" box appear for the local server instance, then at that time the CPU starts getting pegged on a periodic basis (the period seems pretty consistent by looking at Task Manager over a period of time).

I hope this helps somebody find this!

-Jax

|||Any solution ? Same problem with W2K3 R2 SP2, SQL 2005 Enterprise SP2, Virtual Server 2005 R2. All updates installed.|||

I still have no solution.

|||I have this problem running SQL Management Studio on a Windows 2003 server.