Opening up SQL 2005 Management studio generates Winmgmt error

I have been getting the following winmgmt error in my application event log
when I connect to a clustered SQL 2005 server using SQL Management Studio.
Has anyone ever encountered this and does anyone know how to stop this?
Event filter with query "select * from __InstanceModificationEvent within 10
where TargetInstance isa 'Win32_Service'" could not be (re)activated in
namespace "//./root/Microsoft/SqlServer/ComputerManagement" because of error
0x80041010. Events may not be delivered through this filter until the
problem is corrected.
Thanks
I have the same error. It appears mmc cannot connect to wmi class.
"Steven" wrote:

> I have been getting the following winmgmt error in my application event log
> when I connect to a clustered SQL 2005 server using SQL Management Studio.
> Has anyone ever encountered this and does anyone know how to stop this?
> Event filter with query "select * from __InstanceModificationEvent within 10
> where TargetInstance isa 'Win32_Service'" could not be (re)activated in
> namespace "//./root/Microsoft/SqlServer/ComputerManagement" because of error
> 0x80041010. Events may not be delivered through this filter until the
> problem is corrected.
> Thanks
>
>
>
>
>

Opening up SQL 2005 Management studio generates Winmgmt error

I have been getting the following winmgmt error in my application event log
when I connect to a clustered SQL 2005 server using SQL Management Studio.
Has anyone ever encountered this and does anyone know how to stop this?
Event filter with query "select * from __InstanceModificationEvent within 10
where TargetInstance isa 'Win32_Service'" could not be (re)activated in
namespace "//./root/Microsoft/SqlServer/ComputerManagement" because of error
0x80041010. Events may not be delivered through this filter until the
problem is corrected.
ThanksI have the same error. It appears mmc cannot connect to wmi class.
"Steven" wrote:
> I have been getting the following winmgmt error in my application event log
> when I connect to a clustered SQL 2005 server using SQL Management Studio.
> Has anyone ever encountered this and does anyone know how to stop this?
> Event filter with query "select * from __InstanceModificationEvent within 10
> where TargetInstance isa 'Win32_Service'" could not be (re)activated in
> namespace "//./root/Microsoft/SqlServer/ComputerManagement" because of error
> 0x80041010. Events may not be delivered through this filter until the
> problem is corrected.
> Thanks
>
>
>
>
>sql

Opening up SQL 2005 Management studio generates Winmgmt error

I have been getting the following winmgmt error in my application event log
when I connect to a clustered SQL 2005 server using SQL Management Studio.
Has anyone ever encountered this and does anyone know how to stop this?
Event filter with query "select * from __InstanceModificationEvent within 10
where TargetInstance ISA 'Win32_Service'" could not be (re)activated in
namespace "//./root/Microsoft/SqlServer/ComputerManagement" because of error
0x80041010. Events may not be delivered through this filter until the
problem is corrected.
ThanksI have the same error. It appears mmc cannot connect to wmi class.
"Steven" wrote:

> I have been getting the following winmgmt error in my application event lo
g
> when I connect to a clustered SQL 2005 server using SQL Management Studio.
> Has anyone ever encountered this and does anyone know how to stop this?
> Event filter with query "select * from __InstanceModificationEvent within
10
> where TargetInstance ISA 'Win32_Service'" could not be (re)activated in
> namespace "//./root/Microsoft/SqlServer/ComputerManagement" because of err
or
> 0x80041010. Events may not be delivered through this filter until the
> problem is corrected.
> Thanks
>
>
>
>
>

Opening up SQL 2005 Management studio generates Winmgmt erro

I have been getting the following winmgmt error in my application event log
when I connect to a clustered SQL 2005 server using SQL Management Studio.
Has anyone ever encountered this and does anyone know how to stop this?

Event filter with query "select * from __InstanceModificationEvent within 10
where TargetInstance isa 'Win32_Service'" could not be (re)activated in
namespace "//./root/Microsoft/SqlServer/ComputerManagement" because of error
0x80041010. Events may not be delivered through this filter until the
problem is corrected.

ThanksI have the same problem any help would be appreciated :D
|||

We get the same "select *" error message and found that it was probably resulting from a unisys sentinel script; practically the exact verbage could be found in several such .vbs scripts. Certainly some event is triggered and the sentinel software attempts to run one of the scripts and fails. The failure may be due to the sentinel software trying to perform a sql2000 task on a sql2005 server. We're still hunting for an exact cause though.

I hope that this points you in the right direction for your particular case.

Opening up SQL 2005 Management studio generates Winmgmt erro

I have been getting the following winmgmt error in my application event log
when I connect to a clustered SQL 2005 server using SQL Management Studio.
Has anyone ever encountered this and does anyone know how to stop this?

Event filter with query "select * from __InstanceModificationEvent within 10
where TargetInstance isa 'Win32_Service'" could not be (re)activated in
namespace "//./root/Microsoft/SqlServer/ComputerManagement" because of error
0x80041010. Events may not be delivered through this filter until the
problem is corrected.

ThanksI have the same problem any help would be appreciated :D|||

We get the same "select *" error message and found that it was probably resulting from a unisys sentinel script; practically the exact verbage could be found in several such .vbs scripts. Certainly some event is triggered and the sentinel software attempts to run one of the scripts and fails. The failure may be due to the sentinel software trying to perform a sql2000 task on a sql2005 server. We're still hunting for an exact cause though.

I hope that this points you in the right direction for your particular case.

Opening Table in Management Studio is very slow

Dear Gurus

When I open a large table (say more than 1,000,000 Rows) in the SSMS by right clicking on the table name, it takes a very big time to fully open the table.More than 20 minutes for 1,000,000 records on a local instance.

SQL Server 2000 EM was extremely faster. Does any one knows a work around?

I need to be able to view and edit the data in SSMS.

Thanks in advance.

Parviz

I do not suggest ever opening a large table like this and editing the data through management studio. It is better to write queries to view the data as needed, and dml statements to update the data as needed.
Tim

Opening SQL Server Management Studio for Query only

I finally noticed today that if I open a SQL script file on 2005 installation, it opens the query window ONLY in SQL Server Management Studio. Otherwise, no object explorer, ect.

Since MS will not provide independent Query Analyzer as in SQL 2000 and earlier - that I provide to power end users, I want to be able to give them call to "SqlWb.exe" to open only with query window.

I checked program opions (SqlWb.exe /?) with no answer.

Does anyone know how to open SQL Server Management Studio for Query window only?

This is a way we can give our power clients the "Query Analyzer" type toolset for SQL 2005 without them using full SQL 2005 Management Studio.

Create an empty SQL script file on your "H", etc. drive. Create new SHORTCUT on desktop using below string (edit if your path for EXE or SQL file is different)

"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\SqlWb.exe" H:\Empty_SQL.sql -nosplash

Opening SQL Server Express files with SQL Server Management Studio (2005)

I am trying to open a SS2005 Express file with the full version of SQL Server Management Studio but it will not let me. I cannot install SQL Server Express Management Studio as they cannot co-exist on the same machine!

I can open the file in Visual Studio but itb will not allow me to change table names or move tables etc to another SQL Express database.

Is there an addin tool that will allow me to work on these files with SQL 2005 Studio or any other tool that will allow me to change table names and move tables/data between SQL Express files?

You cannot open database files in Management Studio.

You have to attach the database to your SQL Express in order to connect to it with Management Studio.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

i think what you are refering is User Instance feature of SQL Server Express. This feature is only available in Express edition. So you may change the connection string which connecting to otherversion.

attach the mdf and ldf file using sp_attach_db and then you can view the database in Management Studio

Madhu

Opening sql files opens new instance of SQL Management Studio

Hi Folks,

I'm sure this has been answered before but all my searches for this problem don't find what I need. It's a quirk but driving me nuts.

If I have sql management studio open and I view a sql file from Vault, or double-click it in explorer, it always opens a new instance of mgmt studio. I open 5 sql files, 5 instances of mgmt studio.

How do I tell my system to reuse mgmt studio to open sql files if it's open?

Thanks in Advance,

-David

What version of SQL Server Management Studio do you have?

9.00.1399 = RTM
9.00.2047 = SP1
9.00.3042 = SP2

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/

Opening package stored in MSDB

I created a package using the Import\Export wizard and saved it to the local Sql server. Under Management Studio I can see the package, can run in, import it, export it BUT how am I supposed to edit (modify the thing) it?

TIA,

barkingdog

You can make simple edits like changing the property, enabling / disabling task etc by exporting the SSIS package as dtsx files in file system and opening it in SQL Server Management Studio.

To perform more advanced functionalities -like adding new tasks / flows - you need to open and edit the dtsx package in Visual Studio / SQL Server Business Intelligence Development Studio.

Thanks,
Loonysan

|||

Loonysan,

What you described strikes me as totally counter-intuitive. Under sql 2000 after I create and save a simple package, I can double-click it in Enteprse manager (the package opens), and proceed to work on it. End of story.

If I undestood what you said, I have to export my sql 2005 package (say, stored in sql servr) to a dtsx file and then import that into BIDS. This seems overly complicated to me.

Thanks,

Barkingdog.

|||

In SQL 2000 one edited and managed DTS packages in the same environment. This was perhaps ok when packages were simple utilites for the DBA. But in SQL 2005, SSIS Packages are much richer objects and have a full developer environment to support them, as well as a better management environment.

So in 2005 we have BIDS for developing (editing) and Management Studio for administration of deployed packages.

Just like a code project, one typically develops in a specialised environment and then deploys to the server. The developers code (package) is stored where the developer wants it - perhaps in version control. The deployed version lives on the server.

Modifying the version on the server is not good practice. One should modify the version kept in the development environment and deploy the new version to the server.

Some have discovered that it is possible to open a package in the management studio, but as pointed out, this is a most limited experience as it does not have the full range of development features. It may be useful for a quick fix to a property, but even then I would not recommend it.

Is this more complex than in 2000? Perhaps, but practices will should be better for it.

Donald

|||How do you find the SSIS package to save as a file once it is in MSDB? When I go to SSIS, the package itself doesn't show. I'm using Instances, and it will only open for the default install|||

Megan,

You need to go on to the server and edit the file C:\Program Files\Microsoft SQL Server\90\DTS\Binn\MsDtsSrvr.ini.xml

Change <ServerName>.</ServerName> to <ServerName>(your server)\(instance)</ServerName>

Then restart SSIS.

I agree with you that this is counter-intuitive. I am so disappointed with all of SQL Server 2005. Everything has become harder to use, and it's plagued with bugs and problems. I don't know what MS was thinking.

|||

AndyHzzzz wrote:

Megan,

You need to go on to the server and edit the file C:\Program Files\Microsoft SQL Server\90\DTS\Binn\MsDtsSrvr.ini.xml

Change <ServerName>.</ServerName> to <ServerName>(your server)\(instance)</ServerName>

Then restart SSIS.

I agree with you that this is counter-intuitive. I am so disappointed with all of SQL Server 2005. Everything has become harder to use, and it's plagued with bugs and problems. I don't know what MS was thinking.

Andy,
Did you read Donald's post? *That* is what Microsoft was thinking and I agree with it. SSIS is more of an enterprise development tool, not the toy that DTS was and hence it needs to follow a more rigid methodology.|||

Phil,

I did read Donald's post, and I understand what he is saying. However, that doesn't excuse the fact that I can create a package in the Import/Export wizard, configure it, run it, re-run it, re-configure it, etc., all from within that wizard, but if I ever close that wizard, I have to go to a completely different environment to work with that package again. That, to me, is bad interface design.

Furthermore, consider the actual steps in all of this. Today I needed to import 4 tables from Access to SQL Server. These were the steps involved (excluding cursing and searching Books Online):

Started Import/Export wizard

Attempted import of 4 Access tables using default options

Got errors regarding data conversion, clicked 'Back' to the mappings dialog, changed datatypes (Repeat)

Successfully imported the data.

Closed the Wizard.

Later, realized that I wanted to change some datatypes of the target fields.

Looked for my package in Management Studio. Got an error when selecting MSDB store

Modified MsDtsSrvr.ini.xml file on server. Re-started Integration Services (good thing I have admin privileges)

Found my package, exported to file, opened in Visual Studio.

Attempted to modify the SQL statement "Preparation SQL Task", where the target tables are created.

Got an error telling me that "CREATE TABLE" statements are not allowed, and cannot be parsed (the wizard created this package, remember. Also, this SQL statement was full of embedded carriage returns, so I could only edit the first CREATE TABLE statement anyway).

Copied the CREATE TABLE statements into a text file, modified the task to read the SQL from that file.

Changed the datatypes in the text file.

Ran the package. Success.

What did I gain from this new methodology? Nothing. I was not trying to develop an enterprise application, I just wanted to import 4 tables! This story is typical of my experiences with 2005. Simple tasks have become adventures through seemingly untested tools.

Furthermore, even if I did need to develop the sort of app that SSIS is geared toward, I don't trust it. I have run into too many weird problems with this whole release.

Andy

Opening package stored in MSDB

I created a package using the Import\Export wizard and saved it to the local Sql server. Under Management Studio I can see the package, can run in, import it, export it BUT how am I supposed to edit (modify the thing) it?

TIA,

barkingdog

You can make simple edits like changing the property, enabling / disabling task etc by exporting the SSIS package as dtsx files in file system and opening it in SQL Server Management Studio.

To perform more advanced functionalities -like adding new tasks / flows - you need to open and edit the dtsx package in Visual Studio / SQL Server Business Intelligence Development Studio.

Thanks,
Loonysan

|||

Loonysan,

What you described strikes me as totally counter-intuitive. Under sql 2000 after I create and save a simple package, I can double-click it in Enteprse manager (the package opens), and proceed to work on it. End of story.

If I undestood what you said, I have to export my sql 2005 package (say, stored in sql servr) to a dtsx file and then import that into BIDS. This seems overly complicated to me.

Thanks,

Barkingdog.

|||

In SQL 2000 one edited and managed DTS packages in the same environment. This was perhaps ok when packages were simple utilites for the DBA. But in SQL 2005, SSIS Packages are much richer objects and have a full developer environment to support them, as well as a better management environment.

So in 2005 we have BIDS for developing (editing) and Management Studio for administration of deployed packages.

Just like a code project, one typically develops in a specialised environment and then deploys to the server. The developers code (package) is stored where the developer wants it - perhaps in version control. The deployed version lives on the server.

Modifying the version on the server is not good practice. One should modify the version kept in the development environment and deploy the new version to the server.

Some have discovered that it is possible to open a package in the management studio, but as pointed out, this is a most limited experience as it does not have the full range of development features. It may be useful for a quick fix to a property, but even then I would not recommend it.

Is this more complex than in 2000? Perhaps, but practices will should be better for it.

Donald

|||How do you find the SSIS package to save as a file once it is in MSDB? When I go to SSIS, the package itself doesn't show. I'm using Instances, and it will only open for the default install|||

Megan,

You need to go on to the server and edit the file C:\Program Files\Microsoft SQL Server\90\DTS\Binn\MsDtsSrvr.ini.xml

Change <ServerName>.</ServerName> to <ServerName>(your server)\(instance)</ServerName>

Then restart SSIS.

I agree with you that this is counter-intuitive. I am so disappointed with all of SQL Server 2005. Everything has become harder to use, and it's plagued with bugs and problems. I don't know what MS was thinking.

|||

AndyHzzzz wrote:

Megan,

You need to go on to the server and edit the file C:\Program Files\Microsoft SQL Server\90\DTS\Binn\MsDtsSrvr.ini.xml

Change <ServerName>.</ServerName> to <ServerName>(your server)\(instance)</ServerName>

Then restart SSIS.

I agree with you that this is counter-intuitive. I am so disappointed with all of SQL Server 2005. Everything has become harder to use, and it's plagued with bugs and problems. I don't know what MS was thinking.

Andy,
Did you read Donald's post? *That* is what Microsoft was thinking and I agree with it. SSIS is more of an enterprise development tool, not the toy that DTS was and hence it needs to follow a more rigid methodology.|||

Phil,

I did read Donald's post, and I understand what he is saying. However, that doesn't excuse the fact that I can create a package in the Import/Export wizard, configure it, run it, re-run it, re-configure it, etc., all from within that wizard, but if I ever close that wizard, I have to go to a completely different environment to work with that package again. That, to me, is bad interface design.

Furthermore, consider the actual steps in all of this. Today I needed to import 4 tables from Access to SQL Server. These were the steps involved (excluding cursing and searching Books Online):

Started Import/Export wizard

Attempted import of 4 Access tables using default options

Got errors regarding data conversion, clicked 'Back' to the mappings dialog, changed datatypes (Repeat)

Successfully imported the data.

Closed the Wizard.

Later, realized that I wanted to change some datatypes of the target fields.

Looked for my package in Management Studio. Got an error when selecting MSDB store

Modified MsDtsSrvr.ini.xml file on server. Re-started Integration Services (good thing I have admin privileges)

Found my package, exported to file, opened in Visual Studio.

Attempted to modify the SQL statement "Preparation SQL Task", where the target tables are created.

Got an error telling me that "CREATE TABLE" statements are not allowed, and cannot be parsed (the wizard created this package, remember. Also, this SQL statement was full of embedded carriage returns, so I could only edit the first CREATE TABLE statement anyway).

Copied the CREATE TABLE statements into a text file, modified the task to read the SQL from that file.

Changed the datatypes in the text file.

Ran the package. Success.

What did I gain from this new methodology? Nothing. I was not trying to develop an enterprise application, I just wanted to import 4 tables! This story is typical of my experiences with 2005. Simple tasks have become adventures through seemingly untested tools.

Furthermore, even if I did need to develop the sort of app that SSIS is geared toward, I don't trust it. I have run into too many weird problems with this whole release.

Andy

sql

Opening package stored in MSDB

I created a package using the Import\Export wizard and saved it to the local Sql server. Under Management Studio I can see the package, can run in, import it, export it BUT how am I supposed to edit (modify the thing) it?

TIA,

barkingdog

You can make simple edits like changing the property, enabling / disabling task etc by exporting the SSIS package as dtsx files in file system and opening it in SQL Server Management Studio.

To perform more advanced functionalities -like adding new tasks / flows - you need to open and edit the dtsx package in Visual Studio / SQL Server Business Intelligence Development Studio.

Thanks,
Loonysan

|||

Loonysan,

What you described strikes me as totally counter-intuitive. Under sql 2000 after I create and save a simple package, I can double-click it in Enteprse manager (the package opens), and proceed to work on it. End of story.

If I undestood what you said, I have to export my sql 2005 package (say, stored in sql servr) to a dtsx file and then import that into BIDS. This seems overly complicated to me.

Thanks,

Barkingdog.

|||

In SQL 2000 one edited and managed DTS packages in the same environment. This was perhaps ok when packages were simple utilites for the DBA. But in SQL 2005, SSIS Packages are much richer objects and have a full developer environment to support them, as well as a better management environment.

So in 2005 we have BIDS for developing (editing) and Management Studio for administration of deployed packages.

Just like a code project, one typically develops in a specialised environment and then deploys to the server. The developers code (package) is stored where the developer wants it - perhaps in version control. The deployed version lives on the server.

Modifying the version on the server is not good practice. One should modify the version kept in the development environment and deploy the new version to the server.

Some have discovered that it is possible to open a package in the management studio, but as pointed out, this is a most limited experience as it does not have the full range of development features. It may be useful for a quick fix to a property, but even then I would not recommend it.

Is this more complex than in 2000? Perhaps, but practices will should be better for it.

Donald

|||How do you find the SSIS package to save as a file once it is in MSDB? When I go to SSIS, the package itself doesn't show. I'm using Instances, and it will only open for the default install|||

Megan,

You need to go on to the server and edit the file C:\Program Files\Microsoft SQL Server\90\DTS\Binn\MsDtsSrvr.ini.xml

Change <ServerName>.</ServerName> to <ServerName>(your server)\(instance)</ServerName>

Then restart SSIS.

I agree with you that this is counter-intuitive. I am so disappointed with all of SQL Server 2005. Everything has become harder to use, and it's plagued with bugs and problems. I don't know what MS was thinking.

|||

AndyHzzzz wrote:

Megan,

You need to go on to the server and edit the file C:\Program Files\Microsoft SQL Server\90\DTS\Binn\MsDtsSrvr.ini.xml

Change <ServerName>.</ServerName> to <ServerName>(your server)\(instance)</ServerName>

Then restart SSIS.

I agree with you that this is counter-intuitive. I am so disappointed with all of SQL Server 2005. Everything has become harder to use, and it's plagued with bugs and problems. I don't know what MS was thinking.

Andy,
Did you read Donald's post? *That* is what Microsoft was thinking and I agree with it. SSIS is more of an enterprise development tool, not the toy that DTS was and hence it needs to follow a more rigid methodology.|||

Phil,

I did read Donald's post, and I understand what he is saying. However, that doesn't excuse the fact that I can create a package in the Import/Export wizard, configure it, run it, re-run it, re-configure it, etc., all from within that wizard, but if I ever close that wizard, I have to go to a completely different environment to work with that package again. That, to me, is bad interface design.

Furthermore, consider the actual steps in all of this. Today I needed to import 4 tables from Access to SQL Server. These were the steps involved (excluding cursing and searching Books Online):

Started Import/Export wizard

Attempted import of 4 Access tables using default options

Got errors regarding data conversion, clicked 'Back' to the mappings dialog, changed datatypes (Repeat)

Successfully imported the data.

Closed the Wizard.

Later, realized that I wanted to change some datatypes of the target fields.

Looked for my package in Management Studio. Got an error when selecting MSDB store

Modified MsDtsSrvr.ini.xml file on server. Re-started Integration Services (good thing I have admin privileges)

Found my package, exported to file, opened in Visual Studio.

Attempted to modify the SQL statement "Preparation SQL Task", where the target tables are created.

Got an error telling me that "CREATE TABLE" statements are not allowed, and cannot be parsed (the wizard created this package, remember. Also, this SQL statement was full of embedded carriage returns, so I could only edit the first CREATE TABLE statement anyway).

Copied the CREATE TABLE statements into a text file, modified the task to read the SQL from that file.

Changed the datatypes in the text file.

Ran the package. Success.

What did I gain from this new methodology? Nothing. I was not trying to develop an enterprise application, I just wanted to import 4 tables! This story is typical of my experiences with 2005. Simple tasks have become adventures through seemingly untested tools.

Furthermore, even if I did need to develop the sort of app that SSIS is geared toward, I don't trust it. I have run into too many weird problems with this whole release.

Andy

Open View from SQL Server Management Studio

Why the order is lost when I Select Open View'
My query really includes an ORDER clause and the Execute query was fine.Hi
From BOL:
When ORDER BY is used in the definition of a view, inline function, derived
table, or subquery, the clause is used only to determine the rows returned b
y
the TOP clause. The ORDER BY clause does not guarantee ordered results when
these constructs are queried, unless ORDER BY is also specified in the query
itself.
You don't say if you are using SP1 or not, there are issues with ORDER BY
when in SQL 2000 compatibility mode.
John
"515331Jack3490" wrote:

> Why the order is lost when I Select Open View'
> My query really includes an ORDER clause and the Execute query was fine.
>
>sql

Open View from SQL Server Management Studio

Why the order is lost when I Select Open View'
My query really includes an ORDER clause and the Execute query was fine.Hi
From BOL:
When ORDER BY is used in the definition of a view, inline function, derived
table, or subquery, the clause is used only to determine the rows returned by
the TOP clause. The ORDER BY clause does not guarantee ordered results when
these constructs are queried, unless ORDER BY is also specified in the query
itself.
You don't say if you are using SP1 or not, there are issues with ORDER BY
when in SQL 2000 compatibility mode.
John
"515331Jack3490" wrote:
> Why the order is lost when I Select Open View'
> My query really includes an ORDER clause and the Execute query was fine.
>
>

Open table + NULLs

In SQL Management studio, if I right-click on a table and click "Open table", it brings up the table, similar to return all rows in 2000. But if I go to the last row, and edit data in a cell and the word "NULL" is that cell because there is a Null value there, it actually keeps the word null in there as in "NULLnewdata".

Am I doing something wrong?My first suggestion would be to use an UPDATE statement to modify data, not the resultset. After that, make sure you have highlighted the word "NULL" before you start typing... wrote in message news:1305c385-434d-4f39-8ee2-993fecab5184@.discussions.microsoft.com...
> In SQL Management studio, if I right-click on a table and click "Open
> table", it brings up the table, similar to return all rows in 2000. But
> if I go to the last row, and edit data in a cell and the word "NULL" is
> that cell because there is a Null value there, it actually keeps the
> word null in there as in "NULLnewdata". >
> Am I doing something wrong?
>|||

Those are nice work arounds, but it seems to me like this is not the behavior that should be happening in the UI.

|||Well, I think part of the problem is your method. When I highlight a NULL value in Management Studio, it highlights the word. When I start typing, it replaces the word altogether. I can only reproduce your behavior if I highlight the cell, then click again. And I think had I happened upon that behavior naturally, I would have just remembered to not do it again. Sure, it's not the most intuitive thing in the world. But based on the number of people who are going to do it, my gut tells me that it will stay that way. You should still be using DML statements for modifying data, rather than relying on a GUI that is imperfect. Just because I can ride my tricycle to work doesn't make it the best choice. A wrote in message news:c9dd504e-ee88-4d2e-a773-24ae795050a5@.discussions.microsoft.com...
> Those are nice work arounds, but it seems to me like this is not the
> behavior that should be happening in the UI. >
>|||"Correct" method or not, I am just confirming that this is a bug and someone else is having the same issue, and it was not because I have an old build or something.
(They did have 5 years to test it) :)|||To be quite honest, I'm not sure how much usability testing went into these parts of the tools... most of them are holdovers from Enterprise Manager, with a different look and feel. I was going to say prettier but I think the jury is still out on that one. wrote in message news:bc733a59-8d05-452c-ab75-9b9e0d570fa7@.discussions.microsoft.com...
> "Correct" method or not, I am just confirming that this is a bug and
> someone else is having the same issue, and it was not because I have an
> old build or something.
> (They did have 5 years to test it) :)
>|||

I don't think there is a code defect here. I think it's just the way the grid metaphor works. We tried to make sure our grid controls work the same as other Microsoft grids so that whatever learning people have invested in other Microsoft products applies to SQL Server and Visual Studio. The same thing you're describing here happens to me occasionally in other grid-centric products, such as the grid in Microsoft Excel. I've just had to learn to make sure the cell contents are completely highlighted before I start typing to replace them.

In the case of the open table editor, once you change the cell contents, the open table editor replaces the DBNull value in the data set with whatever the cell contains. The open table editor doesn't know what you mean to have in the cell, so if you edit the cell contents to say "NULLnewdata", then that's what it changes the field data to.

|||

I see that this thread is ancient (in internet time!) but I have a highly similar question: I want to go the other way. That is, I want to NULLify a cell in the grid. Deleting the cell contents doesn't work (unless it's a char field) and typing 'NULL' doesn't work either (incorrect data type, or I get the string 'NULL' stored in the field). Is there any way to have a true NULL 'inserted' into a cell, through the grid control? Or can this only be done through DML?

Thanks,

Tom

|||

Tom,

I don't think you can enter NULL values into a cell by typing something. However, there is a trick. You can either find another NULL value in the grid or go to the very last row of the grid where a bunch of NULLs are listed. Then just hit CTRL+C to copy the value and CTRL+V to paste it to the cell you want to add NULL to.

Thanks!

|||Three things.
1.) Ctrl-0 (read control-zero) = NULL
2.) Saying that that's the expected behavior is a cheap Cop-out. Enterprise Manager 2000 did it just fine. There is NO REASON that the "new" "improved" product can't clear out a cell with a null value as soon as it has the focus. I know it's Friday, but c`mon.
3.) One thing I've noticed is that Management Studio was obviously written *by* and *for* Visual Studio .NET programmers. All other developers (VB6, ASP, C, C++, SQL, MS Access...) got screwed. The one thing enterprise manager 2000 was good at was building queries. It did a good job. SQL Mgmt. Studio sucks at that. It does a horrible job. It takes me 20-30% longer to write queries in the new software. Let me run enterprise manager against SQL Server 2005 to build queries and I'll be a happy man.

Open table + NULLs

In SQL Management studio, if I right-click on a table and click "Open table", it brings up the table, similar to return all rows in 2000. But if I go to the last row, and edit data in a cell and the word "NULL" is that cell because there is a Null value there, it actually keeps the word null in there as in "NULLnewdata".

Am I doing something wrong?My first suggestion would be to use an UPDATE statement to modify data, not

the resultset.

After that, make sure you have highlighted the word "NULL" before you start

typing...

wrote in message

news:1305c385-434d-4f39-8ee2-993fecab5184@.discussions.microsoft.com...

> In SQL Management studio, if I right-click on a table and click "Open

> table", it brings up the table, similar to return all rows in 2000. But

> if I go to the last row, and edit data in a cell and the word "NULL" is

> that cell because there is a Null value there, it actually keeps the

> word null in there as in "NULLnewdata".

>

> Am I doing something wrong?

>|||

Those are nice work arounds, but it seems to me like this is not the behavior that should be happening in the UI.

|||Well, I think part of the problem is your method. When I highlight a NULL

value in Management Studio, it highlights the word. When I start typing, it

replaces the word altogether. I can only reproduce your behavior if I

highlight the cell, then click again. And I think had I happened upon that

behavior naturally, I would have just remembered to not do it again.

Sure, it's not the most intuitive thing in the world. But based on the

number of people who are going to do it, my gut tells me that it will stay

that way.

You should still be using DML statements for modifying data, rather than

relying on a GUI that is imperfect. Just because I can ride my tricycle to

work doesn't make it the best choice.

A

wrote in message

news:c9dd504e-ee88-4d2e-a773-24ae795050a5@.discussions.microsoft.com...

> Those are nice work arounds, but it seems to me like this is not the

> behavior that should be happening in the UI.

>

>|||"Correct" method or not, I am just confirming that this is a bug and someone else is having the same issue, and it was not because I have an old build or something.
(They did have 5 years to test it) :)|||To be quite honest, I'm not sure how much usability testing went into these

parts of the tools... most of them are holdovers from Enterprise Manager,

with a different look and feel. I was going to say prettier but I think the

jury is still out on that one.

wrote in message

news:bc733a59-8d05-452c-ab75-9b9e0d570fa7@.discussions.microsoft.com...

> "Correct" method or not, I am just confirming that this is a bug and

> someone else is having the same issue, and it was not because I have an

> old build or something.

> (They did have 5 years to test it) :)

>|||

I don't think there is a code defect here. I think it's just the way the grid metaphor works. We tried to make sure our grid controls work the same as other Microsoft grids so that whatever learning people have invested in other Microsoft products applies to SQL Server and Visual Studio. The same thing you're describing here happens to me occasionally in other grid-centric products, such as the grid in Microsoft Excel. I've just had to learn to make sure the cell contents are completely highlighted before I start typing to replace them.

In the case of the open table editor, once you change the cell contents, the open table editor replaces the DBNull value in the data set with whatever the cell contains. The open table editor doesn't know what you mean to have in the cell, so if you edit the cell contents to say "NULLnewdata", then that's what it changes the field data to.

|||

I see that this thread is ancient (in internet time!) but I have a highly similar question: I want to go the other way. That is, I want to NULLify a cell in the grid. Deleting the cell contents doesn't work (unless it's a char field) and typing 'NULL' doesn't work either (incorrect data type, or I get the string 'NULL' stored in the field). Is there any way to have a true NULL 'inserted' into a cell, through the grid control? Or can this only be done through DML?

Thanks,

Tom

|||

Tom,

I don't think you can enter NULL values into a cell by typing something. However, there is a trick. You can either find another NULL value in the grid or go to the very last row of the grid where a bunch of NULLs are listed. Then just hit CTRL+C to copy the value and CTRL+V to paste it to the cell you want to add NULL to.

Thanks!

|||Three things.

1.) Ctrl-0 (read control-zero) = NULL

2.) Saying that that's the expected behavior is a cheap Cop-out.

Enterprise Manager 2000 did it just fine. There is NO REASON that

the "new" "improved" product can't clear out a cell with a null value

as soon as it has the focus. I know it's Friday, but c`mon.

3.) One thing I've noticed is that Management Studio was obviously

written *by* and *for* Visual Studio .NET programmers. All other

developers (VB6, ASP, C, C++, SQL, MS Access...) got screwed. The

one thing enterprise manager 2000 was good at was building

queries. It did a good job. SQL Mgmt. Studio sucks at

that. It does a horrible job. It takes me 20-30% longer to

write queries in the new software. Let me run enterprise manager

against SQL Server 2005 to build queries and I'll be a happy man.sql

Open SQL file using SQL 2005 Management Studio

Everytime when I open .SQL file using SQL 2005 management Studio, it ask me
about connection info. Is there any way to use the previous one like SQL
2000? For example, first time when I open .SQL file, I put connection info,
and if I open another .SQL file, it will default to the previous connection
info without pop up the "Connect to Server" screen.
Thanks
Yong
This behavior has been changed in SQL Server 2005 Service Pack 2. You can
checkout the December CTP: http://www.microsoft.com/sql/ctp.mspx
FAQ on SP2:
http://blogs.msdn.com/sqlrem/archive/2007/01/12/SP2-and-BPA-FAQ.aspx
Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/
"Yong" <Yong@.discussions.microsoft.com> wrote in message
news:1710CE37-0664-4A98-B0A8-CD84D16EECCD@.microsoft.com...
> Everytime when I open .SQL file using SQL 2005 management Studio, it ask
> me
> about connection info. Is there any way to use the previous one like SQL
> 2000? For example, first time when I open .SQL file, I put connection
> info,
> and if I open another .SQL file, it will default to the previous
> connection
> info without pop up the "Connect to Server" screen.
> Thanks
> Yong
|||Thanks
"Paul A. Mestemaker II [MSFT]" wrote:

> This behavior has been changed in SQL Server 2005 Service Pack 2. You can
> checkout the December CTP: http://www.microsoft.com/sql/ctp.mspx
> FAQ on SP2:
> http://blogs.msdn.com/sqlrem/archive/2007/01/12/SP2-and-BPA-FAQ.aspx
> Paul A. Mestemaker II
> Program Manager
> Microsoft SQL Server Manageability
> http://blogs.msdn.com/sqlrem/
> "Yong" <Yong@.discussions.microsoft.com> wrote in message
> news:1710CE37-0664-4A98-B0A8-CD84D16EECCD@.microsoft.com...
>

open cube to browse takes very long time

I broke up my cube into 24 partitions. There are about 630M total fact rows in that cube.

When I open the cube to browse in BIDS or SQL Management Studio it takes very long time to open (I think 30 minutes).

Profiler does not show that it's running a query, but messages like this keep appearing throughout the time it's opening to browse:

Progress Report Begin, 14- Query, Started reading data from the 'p0' partition.
Progress Report End, 14- Query, Finished reading data from the 'p0' partition.
Progress Report Begin, 14- Query, Started reading data from the 'p10' partition.
Progress Report End, 14- Query, Finished reading data from the 'p10' partition.

and goes on like that....

Looks like you have quite a bit data in your cube. Try designing aggregations for your cube to speed any operations.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||Thank you, yes, adding more aggregations helped opening the cube faster.

A follow up question - based on cube performance in the future is there a way to
Find long running AS queries in trace|||

For that setup a quiery log (http://www.microsoft.com/technet/prodtechnol/sql/2005/technologies/config_ssas_querylog.mspx)

And then use "Usage-Based optimization" wizard to tune your aggregations for longest running queries.

HTH

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||At starting of browse, the profiler shows this query:

select filter([Measures].members, [Measures].currentmember.member_caption = [Measures].currentmember.member_caption) on columns from [MYCUBE]

After during that query, I see that from each partition SSAS reads from the same aggregation:

(this repeats for each partition)
Finished reading data from the 'Aggregation 3f' aggregation.

This aggregation includes one attribute each from 2 dimensions out of 19 total dimensions.

The query I provided above does not query by any particular dimension.
So my question is, can I create a single specific aggregation in my design which will always ensure that cube is opened for browsing without a huge delay?

(or is it safest to just copy this specific aggregation and make sure it's always included).

The reason I ask is that I want to reduce the number of aggregations in my design to improve processing time, but I don't want to remove aggregations that might be needed to open the cube quickly.|||

While we're on this subject, does anyone from Microsoft know why the cube browser decides to retrieve all the measures with their cell values? Why not just:

select {} on columns,
[Measures].members on rows
from [Adventure Works]

That way I don't think it will actually retrieve the cell value, just the measure names.

|||

While we're on this subject, does anyone from Microsoft know why the cube browser decides to retrieve all the measures?

This is old limitation/design choice of OWC which is used by cube browser. It is unfortunate - I agree.

|||can anyone tell if I can create just one aggregation to make sure the browser can open cube quickly enough?

what kind of aggregation is ideal for that?|||Yes - you can create a single aggregation which will include only non-aggregatable attributes and exclude all aggregatable attributes (assuming you didn't define any default members hitting aggregatable attributes - if you did - you will need to include them as well).|||Hi Mosha,

Yes

- you can create a single aggregation which will include only

non-aggregatable attributes and exclude all aggregatable attributes

What do you mean by a single aggregation? Are aggregatable attributes has the same meaning with aggregation usage?

FYI, I have the similar problem with performance issues. I have a cube with 40 dimensions. One of then has over than 5 millions members. I create aggregation based on attributes commonly used by users (I set the AggregationUsage for the attributes that are usually queried to be Unrestricted). I also make several partitions per month and each partition has about 2,5 million records. When I create aggregation design it created only 12%. I use MOLAP storage mode.

At the first I processed the data for 2 months and it went well. But after I processed the data for 2 years, the performance dropped. When I want to browse the cube, it takes so long. When I checked in the performance monitor, the I/O read was 100% but the memory that the SSAS use was only 2-3 GB (FYI, the server is 64 bit with memory 8GB).

I hope you or somebody else can help me...

Thanks in advance,
A. Imamuddin|||Ashari,

have you tried "Usage-Based Aggregation design". It is supposed to create aggregations based on the actual stored queries and their performance.

open cube to browse takes very long time

I broke up my cube into 24 partitions. There are about 630M total fact rows in that cube.

When I open the cube to browse in BIDS or SQL Management Studio it takes very long time to open (I think 30 minutes).

Profiler does not show that it's running a query, but messages like this keep appearing throughout the time it's opening to browse:

Progress Report Begin, 14- Query, Started reading data from the 'p0' partition.
Progress Report End, 14- Query, Finished reading data from the 'p0' partition.
Progress Report Begin, 14- Query, Started reading data from the 'p10' partition.
Progress Report End, 14- Query, Finished reading data from the 'p10' partition.

and goes on like that....

Looks like you have quite a bit data in your cube. Try designing aggregations for your cube to speed any operations.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||Thank you, yes, adding more aggregations helped opening the cube faster.

A follow up question - based on cube performance in the future is there a way to
Find long running AS queries in trace|||

For that setup a quiery log (http://www.microsoft.com/technet/prodtechnol/sql/2005/technologies/config_ssas_querylog.mspx)

And then use "Usage-Based optimization" wizard to tune your aggregations for longest running queries.

HTH

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||At starting of browse, the profiler shows this query:

select filter([Measures].members, [Measures].currentmember.member_caption = [Measures].currentmember.member_caption) on columns from [MYCUBE]

After during that query, I see that from each partition SSAS reads from the same aggregation:

(this repeats for each partition)
Finished reading data from the 'Aggregation 3f' aggregation.

This aggregation includes one attribute each from 2 dimensions out of 19 total dimensions.

The query I provided above does not query by any particular dimension.
So my question is, can I create a single specific aggregation in my design which will always ensure that cube is opened for browsing without a huge delay?

(or is it safest to just copy this specific aggregation and make sure it's always included).

The reason I ask is that I want to reduce the number of aggregations in my design to improve processing time, but I don't want to remove aggregations that might be needed to open the cube quickly.|||

While we're on this subject, does anyone from Microsoft know why the cube browser decides to retrieve all the measures with their cell values? Why not just:

select {} on columns,
[Measures].members on rows
from [Adventure Works]

That way I don't think it will actually retrieve the cell value, just the measure names.

|||

While we're on this subject, does anyone from Microsoft know why the cube browser decides to retrieve all the measures?

This is old limitation/design choice of OWC which is used by cube browser. It is unfortunate - I agree.

|||can anyone tell if I can create just one aggregation to make sure the browser can open cube quickly enough?

what kind of aggregation is ideal for that?|||Yes - you can create a single aggregation which will include only non-aggregatable attributes and exclude all aggregatable attributes (assuming you didn't define any default members hitting aggregatable attributes - if you did - you will need to include them as well).|||Hi Mosha,

Yes

- you can create a single aggregation which will include only

non-aggregatable attributes and exclude all aggregatable attributes

What do you mean by a single aggregation? Are aggregatable attributes has the same meaning with aggregation usage?

FYI, I have the similar problem with performance issues. I have a cube with 40 dimensions. One of then has over than 5 millions members. I create aggregation based on attributes commonly used by users (I set the AggregationUsage for the attributes that are usually queried to be Unrestricted). I also make several partitions per month and each partition has about 2,5 million records. When I create aggregation design it created only 12%. I use MOLAP storage mode.

At the first I processed the data for 2 months and it went well. But after I processed the data for 2 years, the performance dropped. When I want to browse the cube, it takes so long. When I checked in the performance monitor, the I/O read was 100% but the memory that the SSAS use was only 2-3 GB (FYI, the server is 64 bit with memory 8GB).

I hope you or somebody else can help me...

Thanks in advance,
A. Imamuddin|||Ashari,

have you tried "Usage-Based Aggregation design". It is supposed to create aggregations based on the actual stored queries and their performance.

open and shortcuts for management studio at the same place

Hi im pretty new to sql server management studio. I want to know if its possible to create shortcuts to certain database or even tables. I connect to a sql server hosting company that hundreds of databases which take mins to load the list and i need to scroll down and find my database. Filter doesnt work for database names. Is it possible to create a shortcut for the same db same view same login. Thanks

what u can do is

(a) go to the properties of the SQLManagement ShortCUt on u r desktop

(b) u will have something like this : "C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\SqlWb.exe"

(c) just add -S <ServerName> -d <databaseName> -U <UserName> -P <Password> to it.

for me its looks like : "C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\SqlWb.exe" -S tEST-ABC -d gpx -U sa -P sa

i hope i have answered u r question

Madhu

|||

That shortcut opens the query window against that database for that server.

There is no direct way to achieve but you can set SSMS to 'open in empty environment' and then you can select the object explorer to connect to desired server.