Directly editing data in results pane with SQL 2005 Management Studio

In SQL 2000 Enterprise Manager, one was able to edit and commit data on-the-fly directly from the results pane. Action->Open Table->Query with the SQL Pane shown gives you an interface similar to Query Analyzer. One could write a complex select statement with where clauses and joins, and the results pane would show the resulting data. The data is editable and instantly updated. We are now planning to migrate to SQL 2005, and so will need the same capabilities that were availiable from its predecessor. I believe there to be an option/configuration setting or panel in Management Studio that would expose this functionality. I have SQL 2005 Standard Client Tools installed with the SQL 2005 Express Engine, as well as SQL 2000 Client Tools.
The question is this: How do you edit data in the results grid in SQL 2005 Management Studio as you would in SQL 2000 Enterprise Manager when you Open Table via Query (Action->Open Table->Query), and what option/configuration setting or panel would you need to enable/show to make this a permanent feature? In other words, what is the equivalent of Action->Open Table->Query in Management Studio? And how do you make it the default setting?

Can this be done in SQL 2005? Or only in SQL 2000?|||You can use the Open Table functionality to modify the data in a table or schema-bound view. To do this, right click on a table (or view) and select Open Table (or Open View).|||I'm looking for Open Table via Query, not just All Rows.|||bump|||

Open a SQL Editor window, type some text, highlight the text, and select Design Query in Editor. This will bring up a modal dialog containing a Query Designer with your SQL.

Is this what you were looking for?

|||

I too have spent hours trying to figure out how to do tasks that were easy in SQL 2000 EM. If I open a table, it retrieves all rows, not something I want to do with a remote server.

I'd like to query a table, and be able to add or delete records, as well as edit data. SQL Management Studio seems to be lacking this feature of Enterprise Manager and Query Analyzer.

If I save a query to an .sql file, I can't add new records. There's got to be a better way.

|||

Can you safely use SQL 2000 client tools (Enterprise Manager/Query Analyzer) with SQL Server 2005? Is that a viable alternative to Management Studio?

|||

I searched for a possible solution and the only one is using SQL 2000 Enterprise Manager. If you want to use Management Studio this is bad, because we were using this feature for querying one or two records and changing some simple data. Now we have to write update queries, be aware of rowcount, take care of transactions, etc. Maybe this is the way MS wants people to do.

Also our DB admins will not be happy when developers use "Open Table" in Management Studio and query all data in tables which has millions of rows.

|||

I created a small table with just a few records. I open the table, and then paste my SQL query into the SQL pane, so I can query any table I want. I'll probably create a few more small tables so that I can use them for opening multiple queries at the same time.

This gets me by for now. I suspect Microsoft wants me to develope my own application or web pages to manage data, or use MS Access. It just seems that Management Studio should do the basics, and do them well. Wordwrap and pressing the INS key to toggle insert mode in the results pane don't work either, by design i've been told, which is unbelievable. I guess as a server product it's not designed to be as user friendly as I expect.

|||

I can't get the "open table" command to work. Was I suppose to set this function up during install or is it something within SSMS?

|||In SQL Management Studio, just right click a table.|||

Topm,

When I tried doing this I got this error,

"Object reference not set to an instance of an object. (SQLEditors)"

This doesn't tell me anything.

|||I was running SSMS remotely, apparently it doesn't like this. When I remote to the server then run SSMS it works fine.|||

The 'Open Table' command opens a grid with a * 'new row' at the end.
When I enter some data there it says always:

No row was updated.
The data in row 1 was not commited.
Error Source: Microsoft.VisualStudio.DataTools.
Error Message: The update row has changed or been deleted since data was last retrieved.
Correct the errors and retry or press ESC to cancel the change(s).

If I link that table from an Access database I can enter new data as it should be.

What is wrong here?

|||I have a related problem. In the query designer of Server Management Studio I can't enable the results option in the Pane. So I have to close the query designer to execute it. Does anyone knows how to enable the results pane in query designer?

Directly editing data in results pane with SQL 2005 Management Studio

In SQL 2000 Enterprise Manager, one was able to edit and commit data on-the-fly directly from the results pane. Action->Open Table->Query with the SQL Pane shown gives you an interface similar to Query Analyzer. One could write a complex select statement with where clauses and joins, and the results pane would show the resulting data. The data is editable and instantly updated. We are now planning to migrate to SQL 2005, and so will need the same capabilities that were availiable from its predecessor. I believe there to be an option/configuration setting or panel in Management Studio that would expose this functionality. I have SQL 2005 Standard Client Tools installed with the SQL 2005 Express Engine, as well as SQL 2000 Client Tools.
The question is this: How do you edit data in the results grid in SQL 2005 Management Studio as you would in SQL 2000 Enterprise Manager when you Open Table via Query (Action->Open Table->Query), and what option/configuration setting or panel would you need to enable/show to make this a permanent feature? In other words, what is the equivalent of Action->Open Table->Query in Management Studio? And how do you make it the default setting?

Can this be done in SQL 2005? Or only in SQL 2000?|||You can use the Open Table functionality to modify the data in a table or schema-bound view. To do this, right click on a table (or view) and select Open Table (or Open View).|||I'm looking for Open Table via Query, not just All Rows.|||bump|||

Open a SQL Editor window, type some text, highlight the text, and select Design Query in Editor. This will bring up a modal dialog containing a Query Designer with your SQL.

Is this what you were looking for?

|||

I too have spent hours trying to figure out how to do tasks that were easy in SQL 2000 EM. If I open a table, it retrieves all rows, not something I want to do with a remote server.

I'd like to query a table, and be able to add or delete records, as well as edit data. SQL Management Studio seems to be lacking this feature of Enterprise Manager and Query Analyzer.

If I save a query to an .sql file, I can't add new records. There's got to be a better way.

|||

Can you safely use SQL 2000 client tools (Enterprise Manager/Query Analyzer) with SQL Server 2005? Is that a viable alternative to Management Studio?

|||

I searched for a possible solution and the only one is using SQL 2000 Enterprise Manager. If you want to use Management Studio this is bad, because we were using this feature for querying one or two records and changing some simple data. Now we have to write update queries, be aware of rowcount, take care of transactions, etc. Maybe this is the way MS wants people to do.

Also our DB admins will not be happy when developers use "Open Table" in Management Studio and query all data in tables which has millions of rows.

|||

I created a small table with just a few records. I open the table, and then paste my SQL query into the SQL pane, so I can query any table I want. I'll probably create a few more small tables so that I can use them for opening multiple queries at the same time.

This gets me by for now. I suspect Microsoft wants me to develope my own application or web pages to manage data, or use MS Access. It just seems that Management Studio should do the basics, and do them well. Wordwrap and pressing the INS key to toggle insert mode in the results pane don't work either, by design i've been told, which is unbelievable. I guess as a server product it's not designed to be as user friendly as I expect.

|||

I can't get the "open table" command to work. Was I suppose to set this function up during install or is it something within SSMS?

|||In SQL Management Studio, just right click a table.|||

Topm,

When I tried doing this I got this error,

"Object reference not set to an instance of an object. (SQLEditors)"

This doesn't tell me anything.

|||I was running SSMS remotely, apparently it doesn't like this. When I remote to the server then run SSMS it works fine.|||

The 'Open Table' command opens a grid with a * 'new row' at the end.
When I enter some data there it says always:

No row was updated.
The data in row 1 was not commited.
Error Source: Microsoft.VisualStudio.DataTools.
Error Message: The update row has changed or been deleted since data was last retrieved.
Correct the errors and retry or press ESC to cancel the change(s).

If I link that table from an Access database I can enter new data as it should be.

What is wrong here?

sql

Directly editing data in results pane with SQL 2005 Management Studio

In SQL 2000 Enterprise Manager, one was able to edit and commit data on-the-fly directly from the results pane. Action->Open Table->Query with the SQL Pane shown gives you an interface similar to Query Analyzer. One could write a complex select statement with where clauses and joins, and the results pane would show the resulting data. The data is editable and instantly updated. We are now planning to migrate to SQL 2005, and so will need the same capabilities that were availiable from its predecessor. I believe there to be an option/configuration setting or panel in Management Studio that would expose this functionality. I have SQL 2005 Standard Client Tools installed with the SQL 2005 Express Engine, as well as SQL 2000 Client Tools.
The question is this: How do you edit data in the results grid in SQL 2005 Management Studio as you would in SQL 2000 Enterprise Manager when you Open Table via Query (Action->Open Table->Query), and what option/configuration setting or panel would you need to enable/show to make this a permanent feature? In other words, what is the equivalent of Action->Open Table->Query in Management Studio? And how do you make it the default setting?

Can this be done in SQL 2005? Or only in SQL 2000?|||You can use the Open Table functionality to modify the data in a table or schema-bound view. To do this, right click on a table (or view) and select Open Table (or Open View).|||I'm looking for Open Table via Query, not just All Rows.|||bump|||

Open a SQL Editor window, type some text, highlight the text, and select Design Query in Editor. This will bring up a modal dialog containing a Query Designer with your SQL.

Is this what you were looking for?

|||

I too have spent hours trying to figure out how to do tasks that were easy in SQL 2000 EM. If I open a table, it retrieves all rows, not something I want to do with a remote server.

I'd like to query a table, and be able to add or delete records, as well as edit data. SQL Management Studio seems to be lacking this feature of Enterprise Manager and Query Analyzer.

If I save a query to an .sql file, I can't add new records. There's got to be a better way.

|||

Can you safely use SQL 2000 client tools (Enterprise Manager/Query Analyzer) with SQL Server 2005? Is that a viable alternative to Management Studio?

|||

I searched for a possible solution and the only one is using SQL 2000 Enterprise Manager. If you want to use Management Studio this is bad, because we were using this feature for querying one or two records and changing some simple data. Now we have to write update queries, be aware of rowcount, take care of transactions, etc. Maybe this is the way MS wants people to do.

Also our DB admins will not be happy when developers use "Open Table" in Management Studio and query all data in tables which has millions of rows.

|||

I created a small table with just a few records. I open the table, and then paste my SQL query into the SQL pane, so I can query any table I want. I'll probably create a few more small tables so that I can use them for opening multiple queries at the same time.

This gets me by for now. I suspect Microsoft wants me to develope my own application or web pages to manage data, or use MS Access. It just seems that Management Studio should do the basics, and do them well. Wordwrap and pressing the INS key to toggle insert mode in the results pane don't work either, by design i've been told, which is unbelievable. I guess as a server product it's not designed to be as user friendly as I expect.

|||

I can't get the "open table" command to work. Was I suppose to set this function up during install or is it something within SSMS?

|||In SQL Management Studio, just right click a table.|||

Topm,

When I tried doing this I got this error,

"Object reference not set to an instance of an object. (SQLEditors)"

This doesn't tell me anything.

|||I was running SSMS remotely, apparently it doesn't like this. When I remote to the server then run SSMS it works fine.|||

The 'Open Table' command opens a grid with a * 'new row' at the end.
When I enter some data there it says always:

No row was updated.
The data in row 1 was not commited.
Error Source: Microsoft.VisualStudio.DataTools.
Error Message: The update row has changed or been deleted since data was last retrieved.
Correct the errors and retry or press ESC to cancel the change(s).

If I link that table from an Access database I can enter new data as it should be.

What is wrong here?

Directly editing data in results pane with SQL 2005 Management Studio

In SQL 2000 Enterprise Manager, one was able to edit and commit data on-the-fly directly from the results pane. Action->Open Table->Query with the SQL Pane shown gives you an interface similar to Query Analyzer. One could write a complex select statement with where clauses and joins, and the results pane would show the resulting data. The data is editable and instantly updated. We are now planning to migrate to SQL 2005, and so will need the same capabilities that were availiable from its predecessor. I believe there to be an option/configuration setting or panel in Management Studio that would expose this functionality. I have SQL 2005 Standard Client Tools installed with the SQL 2005 Express Engine, as well as SQL 2000 Client Tools.
The question is this: How do you edit data in the results grid in SQL 2005 Management Studio as you would in SQL 2000 Enterprise Manager when you Open Table via Query (Action->Open Table->Query), and what option/configuration setting or panel would you need to enable/show to make this a permanent feature? In other words, what is the equivalent of Action->Open Table->Query in Management Studio? And how do you make it the default setting?

Can this be done in SQL 2005? Or only in SQL 2000?|||You can use the Open Table functionality to modify the data in a table or schema-bound view. To do this, right click on a table (or view) and select Open Table (or Open View).|||I'm looking for Open Table via Query, not just All Rows.|||bump|||

Open a SQL Editor window, type some text, highlight the text, and select Design Query in Editor. This will bring up a modal dialog containing a Query Designer with your SQL.

Is this what you were looking for?

|||

I too have spent hours trying to figure out how to do tasks that were easy in SQL 2000 EM. If I open a table, it retrieves all rows, not something I want to do with a remote server.

I'd like to query a table, and be able to add or delete records, as well as edit data. SQL Management Studio seems to be lacking this feature of Enterprise Manager and Query Analyzer.

If I save a query to an .sql file, I can't add new records. There's got to be a better way.

|||

Can you safely use SQL 2000 client tools (Enterprise Manager/Query Analyzer) with SQL Server 2005? Is that a viable alternative to Management Studio?

|||

I searched for a possible solution and the only one is using SQL 2000 Enterprise Manager. If you want to use Management Studio this is bad, because we were using this feature for querying one or two records and changing some simple data. Now we have to write update queries, be aware of rowcount, take care of transactions, etc. Maybe this is the way MS wants people to do.

Also our DB admins will not be happy when developers use "Open Table" in Management Studio and query all data in tables which has millions of rows.

|||

I created a small table with just a few records. I open the table, and then paste my SQL query into the SQL pane, so I can query any table I want. I'll probably create a few more small tables so that I can use them for opening multiple queries at the same time.

This gets me by for now. I suspect Microsoft wants me to develope my own application or web pages to manage data, or use MS Access. It just seems that Management Studio should do the basics, and do them well. Wordwrap and pressing the INS key to toggle insert mode in the results pane don't work either, by design i've been told, which is unbelievable. I guess as a server product it's not designed to be as user friendly as I expect.

|||

I can't get the "open table" command to work. Was I suppose to set this function up during install or is it something within SSMS?

|||In SQL Management Studio, just right click a table.|||

Topm,

When I tried doing this I got this error,

"Object reference not set to an instance of an object. (SQLEditors)"

This doesn't tell me anything.

|||I was running SSMS remotely, apparently it doesn't like this. When I remote to the server then run SSMS it works fine.|||

The 'Open Table' command opens a grid with a * 'new row' at the end.
When I enter some data there it says always:

No row was updated.
The data in row 1 was not commited.
Error Source: Microsoft.VisualStudio.DataTools.
Error Message: The update row has changed or been deleted since data was last retrieved.
Correct the errors and retry or press ESC to cancel the change(s).

If I link that table from an Access database I can enter new data as it should be.

What is wrong here?

Directly editing data in results pane with SQL 2005 Management Studio

In SQL 2000 Enterprise Manager, one was able to edit and commit data on-the-fly directly from the results pane. Action->Open Table->Query with the SQL Pane shown gives you an interface similar to Query Analyzer. One could write a complex select statement with where clauses and joins, and the results pane would show the resulting data. The data is editable and instantly updated. We are now planning to migrate to SQL 2005, and so will need the same capabilities that were availiable from its predecessor. I believe there to be an option/configuration setting or panel in Management Studio that would expose this functionality. I have SQL 2005 Standard Client Tools installed with the SQL 2005 Express Engine, as well as SQL 2000 Client Tools.
The question is this: How do you edit data in the results grid in SQL 2005 Management Studio as you would in SQL 2000 Enterprise Manager when you Open Table via Query (Action->Open Table->Query), and what option/configuration setting or panel would you need to enable/show to make this a permanent feature? In other words, what is the equivalent of Action->Open Table->Query in Management Studio? And how do you make it the default setting?

Can this be done in SQL 2005? Or only in SQL 2000?|||You can use the Open Table functionality to modify the data in a table or schema-bound view. To do this, right click on a table (or view) and select Open Table (or Open View).|||I'm looking for Open Table via Query, not just All Rows.|||bump|||

Open a SQL Editor window, type some text, highlight the text, and select Design Query in Editor. This will bring up a modal dialog containing a Query Designer with your SQL.

Is this what you were looking for?

|||

I too have spent hours trying to figure out how to do tasks that were easy in SQL 2000 EM. If I open a table, it retrieves all rows, not something I want to do with a remote server.

I'd like to query a table, and be able to add or delete records, as well as edit data. SQL Management Studio seems to be lacking this feature of Enterprise Manager and Query Analyzer.

If I save a query to an .sql file, I can't add new records. There's got to be a better way.

|||

Can you safely use SQL 2000 client tools (Enterprise Manager/Query Analyzer) with SQL Server 2005? Is that a viable alternative to Management Studio?

|||

I searched for a possible solution and the only one is using SQL 2000 Enterprise Manager. If you want to use Management Studio this is bad, because we were using this feature for querying one or two records and changing some simple data. Now we have to write update queries, be aware of rowcount, take care of transactions, etc. Maybe this is the way MS wants people to do.

Also our DB admins will not be happy when developers use "Open Table" in Management Studio and query all data in tables which has millions of rows.

|||

I created a small table with just a few records. I open the table, and then paste my SQL query into the SQL pane, so I can query any table I want. I'll probably create a few more small tables so that I can use them for opening multiple queries at the same time.

This gets me by for now. I suspect Microsoft wants me to develope my own application or web pages to manage data, or use MS Access. It just seems that Management Studio should do the basics, and do them well. Wordwrap and pressing the INS key to toggle insert mode in the results pane don't work either, by design i've been told, which is unbelievable. I guess as a server product it's not designed to be as user friendly as I expect.

|||

I can't get the "open table" command to work. Was I suppose to set this function up during install or is it something within SSMS?

|||In SQL Management Studio, just right click a table.|||

Topm,

When I tried doing this I got this error,

"Object reference not set to an instance of an object. (SQLEditors)"

This doesn't tell me anything.

|||I was running SSMS remotely, apparently it doesn't like this. When I remote to the server then run SSMS it works fine.|||

The 'Open Table' command opens a grid with a * 'new row' at the end.
When I enter some data there it says always:

No row was updated.
The data in row 1 was not commited.
Error Source: Microsoft.VisualStudio.DataTools.
Error Message: The update row has changed or been deleted since data was last retrieved.
Correct the errors and retry or press ESC to cancel the change(s).

If I link that table from an Access database I can enter new data as it should be.

What is wrong here?

Directly editing data in results pane with SQL 2005 Management Studio

In SQL 2000 Enterprise Manager, one was able to edit and commit data on-the-fly directly from the results pane. Action->Open Table->Query with the SQL Pane shown gives you an interface similar to Query Analyzer. One could write a complex select statement with where clauses and joins, and the results pane would show the resulting data. The data is editable and instantly updated. We are now planning to migrate to SQL 2005, and so will need the same capabilities that were availiable from its predecessor. I believe there to be an option/configuration setting or panel in Management Studio that would expose this functionality. I have SQL 2005 Standard Client Tools installed with the SQL 2005 Express Engine, as well as SQL 2000 Client Tools.
The question is this: How do you edit data in the results grid in SQL 2005 Management Studio as you would in SQL 2000 Enterprise Manager when you Open Table via Query (Action->Open Table->Query), and what option/configuration setting or panel would you need to enable/show to make this a permanent feature? In other words, what is the equivalent of Action->Open Table->Query in Management Studio? And how do you make it the default setting?

Can this be done in SQL 2005? Or only in SQL 2000?|||You can use the Open Table functionality to modify the data in a table or schema-bound view. To do this, right click on a table (or view) and select Open Table (or Open View).|||I'm looking for Open Table via Query, not just All Rows.|||bump|||

Open a SQL Editor window, type some text, highlight the text, and select Design Query in Editor. This will bring up a modal dialog containing a Query Designer with your SQL.

Is this what you were looking for?

|||

I too have spent hours trying to figure out how to do tasks that were easy in SQL 2000 EM. If I open a table, it retrieves all rows, not something I want to do with a remote server.

I'd like to query a table, and be able to add or delete records, as well as edit data. SQL Management Studio seems to be lacking this feature of Enterprise Manager and Query Analyzer.

If I save a query to an .sql file, I can't add new records. There's got to be a better way.

|||

Can you safely use SQL 2000 client tools (Enterprise Manager/Query Analyzer) with SQL Server 2005? Is that a viable alternative to Management Studio?

|||

I searched for a possible solution and the only one is using SQL 2000 Enterprise Manager. If you want to use Management Studio this is bad, because we were using this feature for querying one or two records and changing some simple data. Now we have to write update queries, be aware of rowcount, take care of transactions, etc. Maybe this is the way MS wants people to do.

Also our DB admins will not be happy when developers use "Open Table" in Management Studio and query all data in tables which has millions of rows.

|||

I created a small table with just a few records. I open the table, and then paste my SQL query into the SQL pane, so I can query any table I want. I'll probably create a few more small tables so that I can use them for opening multiple queries at the same time.

This gets me by for now. I suspect Microsoft wants me to develope my own application or web pages to manage data, or use MS Access. It just seems that Management Studio should do the basics, and do them well. Wordwrap and pressing the INS key to toggle insert mode in the results pane don't work either, by design i've been told, which is unbelievable. I guess as a server product it's not designed to be as user friendly as I expect.

|||

I can't get the "open table" command to work. Was I suppose to set this function up during install or is it something within SSMS?

|||In SQL Management Studio, just right click a table.|||

Topm,

When I tried doing this I got this error,

"Object reference not set to an instance of an object. (SQLEditors)"

This doesn't tell me anything.

|||I was running SSMS remotely, apparently it doesn't like this. When I remote to the server then run SSMS it works fine.|||

The 'Open Table' command opens a grid with a * 'new row' at the end.
When I enter some data there it says always:

No row was updated.
The data in row 1 was not commited.
Error Source: Microsoft.VisualStudio.DataTools.
Error Message: The update row has changed or been deleted since data was last retrieved.
Correct the errors and retry or press ESC to cancel the change(s).

If I link that table from an Access database I can enter new data as it should be.

What is wrong here?

Directly editing data in results pane with SQL 2005 Management Studio

In SQL 2000 Enterprise Manager, one was able to edit and commit data on-the-fly directly from the results pane. Action->Open Table->Query with the SQL Pane shown gives you an interface similar to Query Analyzer. One could write a complex select statement with where clauses and joins, and the results pane would show the resulting data. The data is editable and instantly updated. We are now planning to migrate to SQL 2005, and so will need the same capabilities that were availiable from its predecessor. I believe there to be an option/configuration setting or panel in Management Studio that would expose this functionality. I have SQL 2005 Standard Client Tools installed with the SQL 2005 Express Engine, as well as SQL 2000 Client Tools.
The question is this: How do you edit data in the results grid in SQL 2005 Management Studio as you would in SQL 2000 Enterprise Manager when you Open Table via Query (Action->Open Table->Query), and what option/configuration setting or panel would you need to enable/show to make this a permanent feature? In other words, what is the equivalent of Action->Open Table->Query in Management Studio? And how do you make it the default setting?

Can this be done in SQL 2005? Or only in SQL 2000?|||You can use the Open Table functionality to modify the data in a table or schema-bound view. To do this, right click on a table (or view) and select Open Table (or Open View).|||I'm looking for Open Table via Query, not just All Rows.|||bump|||

Open a SQL Editor window, type some text, highlight the text, and select Design Query in Editor. This will bring up a modal dialog containing a Query Designer with your SQL.

Is this what you were looking for?

|||

I too have spent hours trying to figure out how to do tasks that were easy in SQL 2000 EM. If I open a table, it retrieves all rows, not something I want to do with a remote server.

I'd like to query a table, and be able to add or delete records, as well as edit data. SQL Management Studio seems to be lacking this feature of Enterprise Manager and Query Analyzer.

If I save a query to an .sql file, I can't add new records. There's got to be a better way.

|||

Can you safely use SQL 2000 client tools (Enterprise Manager/Query Analyzer) with SQL Server 2005? Is that a viable alternative to Management Studio?

|||

I searched for a possible solution and the only one is using SQL 2000 Enterprise Manager. If you want to use Management Studio this is bad, because we were using this feature for querying one or two records and changing some simple data. Now we have to write update queries, be aware of rowcount, take care of transactions, etc. Maybe this is the way MS wants people to do.

Also our DB admins will not be happy when developers use "Open Table" in Management Studio and query all data in tables which has millions of rows.

|||

I created a small table with just a few records. I open the table, and then paste my SQL query into the SQL pane, so I can query any table I want. I'll probably create a few more small tables so that I can use them for opening multiple queries at the same time.

This gets me by for now. I suspect Microsoft wants me to develope my own application or web pages to manage data, or use MS Access. It just seems that Management Studio should do the basics, and do them well. Wordwrap and pressing the INS key to toggle insert mode in the results pane don't work either, by design i've been told, which is unbelievable. I guess as a server product it's not designed to be as user friendly as I expect.

|||

I can't get the "open table" command to work. Was I suppose to set this function up during install or is it something within SSMS?

|||In SQL Management Studio, just right click a table.|||

Topm,

When I tried doing this I got this error,

"Object reference not set to an instance of an object. (SQLEditors)"

This doesn't tell me anything.

|||I was running SSMS remotely, apparently it doesn't like this. When I remote to the server then run SSMS it works fine.|||

The 'Open Table' command opens a grid with a * 'new row' at the end.
When I enter some data there it says always:

No row was updated.
The data in row 1 was not commited.
Error Source: Microsoft.VisualStudio.DataTools.
Error Message: The update row has changed or been deleted since data was last retrieved.
Correct the errors and retry or press ESC to cancel the change(s).

If I link that table from an Access database I can enter new data as it should be.

What is wrong here?

sql

Directly editing data in results pane with SQL 2005 Management Studio

In SQL 2000 Enterprise Manager, one was able to edit and commit data on-the-fly directly from the results pane. Action->Open Table->Query with the SQL Pane shown gives you an interface similar to Query Analyzer. One could write a complex select statement with where clauses and joins, and the results pane would show the resulting data. The data is editable and instantly updated. We are now planning to migrate to SQL 2005, and so will need the same capabilities that were availiable from its predecessor. I believe there to be an option/configuration setting or panel in Management Studio that would expose this functionality. I have SQL 2005 Standard Client Tools installed with the SQL 2005 Express Engine, as well as SQL 2000 Client Tools.
The question is this: How do you edit data in the results grid in SQL 2005 Management Studio as you would in SQL 2000 Enterprise Manager when you Open Table via Query (Action->Open Table->Query), and what option/configuration setting or panel would you need to enable/show to make this a permanent feature? In other words, what is the equivalent of Action->Open Table->Query in Management Studio? And how do you make it the default setting?

Can this be done in SQL 2005? Or only in SQL 2000?|||You can use the Open Table functionality to modify the data in a table or schema-bound view. To do this, right click on a table (or view) and select Open Table (or Open View).|||I'm looking for Open Table via Query, not just All Rows.|||bump|||

Open a SQL Editor window, type some text, highlight the text, and select Design Query in Editor. This will bring up a modal dialog containing a Query Designer with your SQL.

Is this what you were looking for?

|||

I too have spent hours trying to figure out how to do tasks that were easy in SQL 2000 EM. If I open a table, it retrieves all rows, not something I want to do with a remote server.

I'd like to query a table, and be able to add or delete records, as well as edit data. SQL Management Studio seems to be lacking this feature of Enterprise Manager and Query Analyzer.

If I save a query to an .sql file, I can't add new records. There's got to be a better way.

|||

Can you safely use SQL 2000 client tools (Enterprise Manager/Query Analyzer) with SQL Server 2005? Is that a viable alternative to Management Studio?

|||

I searched for a possible solution and the only one is using SQL 2000 Enterprise Manager. If you want to use Management Studio this is bad, because we were using this feature for querying one or two records and changing some simple data. Now we have to write update queries, be aware of rowcount, take care of transactions, etc. Maybe this is the way MS wants people to do.

Also our DB admins will not be happy when developers use "Open Table" in Management Studio and query all data in tables which has millions of rows.

|||

I created a small table with just a few records. I open the table, and then paste my SQL query into the SQL pane, so I can query any table I want. I'll probably create a few more small tables so that I can use them for opening multiple queries at the same time.

This gets me by for now. I suspect Microsoft wants me to develope my own application or web pages to manage data, or use MS Access. It just seems that Management Studio should do the basics, and do them well. Wordwrap and pressing the INS key to toggle insert mode in the results pane don't work either, by design i've been told, which is unbelievable. I guess as a server product it's not designed to be as user friendly as I expect.

|||

I can't get the "open table" command to work. Was I suppose to set this function up during install or is it something within SSMS?

|||In SQL Management Studio, just right click a table.|||

Topm,

When I tried doing this I got this error,

"Object reference not set to an instance of an object. (SQLEditors)"

This doesn't tell me anything.

|||I was running SSMS remotely, apparently it doesn't like this. When I remote to the server then run SSMS it works fine.|||

The 'Open Table' command opens a grid with a * 'new row' at the end.
When I enter some data there it says always:

No row was updated.
The data in row 1 was not commited.
Error Source: Microsoft.VisualStudio.DataTools.
Error Message: The update row has changed or been deleted since data was last retrieved.
Correct the errors and retry or press ESC to cancel the change(s).

If I link that table from an Access database I can enter new data as it should be.

What is wrong here?

Directly editing data in results pane with SQL 2005 Management Studio

In SQL 2000 Enterprise Manager, one was able to edit and commit data on-the-fly directly from the results pane. Action->Open Table->Query with the SQL Pane shown gives you an interface similar to Query Analyzer. One could write a complex select statement with where clauses and joins, and the results pane would show the resulting data. The data is editable and instantly updated. We are now planning to migrate to SQL 2005, and so will need the same capabilities that were availiable from its predecessor. I believe there to be an option/configuration setting or panel in Management Studio that would expose this functionality. I have SQL 2005 Standard Client Tools installed with the SQL 2005 Express Engine, as well as SQL 2000 Client Tools.
The question is this: How do you edit data in the results grid in SQL 2005 Management Studio as you would in SQL 2000 Enterprise Manager when you Open Table via Query (Action->Open Table->Query), and what option/configuration setting or panel would you need to enable/show to make this a permanent feature? In other words, what is the equivalent of Action->Open Table->Query in Management Studio? And how do you make it the default setting?

Can this be done in SQL 2005? Or only in SQL 2000?|||You can use the Open Table functionality to modify the data in a table or schema-bound view. To do this, right click on a table (or view) and select Open Table (or Open View).|||I'm looking for Open Table via Query, not just All Rows.|||bump|||

Open a SQL Editor window, type some text, highlight the text, and select Design Query in Editor. This will bring up a modal dialog containing a Query Designer with your SQL.

Is this what you were looking for?

|||

I too have spent hours trying to figure out how to do tasks that were easy in SQL 2000 EM. If I open a table, it retrieves all rows, not something I want to do with a remote server.

I'd like to query a table, and be able to add or delete records, as well as edit data. SQL Management Studio seems to be lacking this feature of Enterprise Manager and Query Analyzer.

If I save a query to an .sql file, I can't add new records. There's got to be a better way.

|||

Can you safely use SQL 2000 client tools (Enterprise Manager/Query Analyzer) with SQL Server 2005? Is that a viable alternative to Management Studio?

|||

I searched for a possible solution and the only one is using SQL 2000 Enterprise Manager. If you want to use Management Studio this is bad, because we were using this feature for querying one or two records and changing some simple data. Now we have to write update queries, be aware of rowcount, take care of transactions, etc. Maybe this is the way MS wants people to do.

Also our DB admins will not be happy when developers use "Open Table" in Management Studio and query all data in tables which has millions of rows.

|||

I created a small table with just a few records. I open the table, and then paste my SQL query into the SQL pane, so I can query any table I want. I'll probably create a few more small tables so that I can use them for opening multiple queries at the same time.

This gets me by for now. I suspect Microsoft wants me to develope my own application or web pages to manage data, or use MS Access. It just seems that Management Studio should do the basics, and do them well. Wordwrap and pressing the INS key to toggle insert mode in the results pane don't work either, by design i've been told, which is unbelievable. I guess as a server product it's not designed to be as user friendly as I expect.

|||

I can't get the "open table" command to work. Was I suppose to set this function up during install or is it something within SSMS?

|||In SQL Management Studio, just right click a table.|||

Topm,

When I tried doing this I got this error,

"Object reference not set to an instance of an object. (SQLEditors)"

This doesn't tell me anything.

|||I was running SSMS remotely, apparently it doesn't like this. When I remote to the server then run SSMS it works fine.|||

The 'Open Table' command opens a grid with a * 'new row' at the end.
When I enter some data there it says always:

No row was updated.
The data in row 1 was not commited.
Error Source: Microsoft.VisualStudio.DataTools.
Error Message: The update row has changed or been deleted since data was last retrieved.
Correct the errors and retry or press ESC to cancel the change(s).

If I link that table from an Access database I can enter new data as it should be.

What is wrong here?

Direct access to SQL Server 2005 from Windows Mobile

Hi everyone,

I've created a SQL Server database in Visual Studio 2005 and I am also planning to create a Windows Mobile application to access, edit and update this data.
So I heard that it is possible to directly access to SQL Server from the device using SQLClient rather than creating an SQL ServerCE.

I tried to use that method but I get a connection error. Here is the bit of code that was generated.

this._connection = new System.Data.SqlClient.SqlConnection();
this._connection.ConnectionString = "Data
Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirecto ry|\\MainRestaurantManagementdb.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True;";
}

Can anyone help me on this please as I am stuck and can't figure out how to solve it?

Quote:

Originally Posted by Hezal

Hi everyone,

I've created a SQL Server database in Visual Studio 2005 and I am also planning to create a Windows Mobile application to access, edit and update this data.
So I heard that it is possible to directly access to SQL Server from the device using SQLClient rather than creating an SQL ServerCE.

I tried to use that method but I get a connection error. Here is the bit of code that was generated.

this._connection = new System.Data.SqlClient.SqlConnection();
this._connection.ConnectionString = "Data
Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirecto ry|\\MainRestaurantManagementdb.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True;";
}

Can anyone help me on this please as I am stuck and can't figure out how to solve it?

Moved to SQL forum. This is more of an applications issue then a mobile specific issue.

Dimension Wizard hangs

When I try to create a new dimension (from visual studio 2005) using the Dimension Wizard, after selecting the related tables and clicking next, visual studio hangs. CPU hits 100%. It seems to only happen when I add one particular table to the dimension. The only thing "special" about this table is that it has a many-to-many relationship with one of the other related tables. When I removed one of the relationships the dimension created successfully. Is this the correct behaviour? Should multiple relationship cause this kind of problem?

If you were trying to include a many to many relationship within the one dimension I would guess that the wizard is scanning your tables trying to figure out which attribute is the "parent" of the other (which does not make sense for a many to many relationship).

If you take the classic many to many example of bank accounts and account owners, in SSAS 2005 these are each separate dimensions and they are related by an intermediate measure group. Maybe you need to split your dimension up.

Dimension Access for Roles in AS 2005

When creating a role in AS 2005 and specifying dimenison access (on the Dimensions tab of the role definition in BI Dev Studio), why do some dimensions have the option of "None" as access level while others only have "Read" and "Read/Write"?

For example, in looking at the Adventure Works sample solution for AS 2005, if you add a role and then go to the Dimensions tab of the role definition, you'll see that Account, Department, Organization, and Scenario only have "Read" and "Read/Write" as access options. All the others have those two as well as "None".

At first, I thought that it was because of some unique setting for those four dimensions. But I haven't found anything just by browsing their definitions. Then I thought it was perhaps due to some unique usage of those four dimensions within the cube design. They are all used with the Financial Reporting measure group, but so is the Date dimension and it has "None" as an access option.

Anyone have any ideas? Probably something simple that I'm just overlooking, but I'd like to know why I can't set any of these to "None".

Thanks!

Dave Fackler

Did you ever find an answer to this? I'm having the same issue.

Dimension Access for Roles in AS 2005

When creating a role in AS 2005 and specifying dimenison access (on the Dimensions tab of the role definition in BI Dev Studio), why do some dimensions have the option of "None" as access level while others only have "Read" and "Read/Write"?

For example, in looking at the Adventure Works sample solution for AS 2005, if you add a role and then go to the Dimensions tab of the role definition, you'll see that Account, Department, Organization, and Scenario only have "Read" and "Read/Write" as access options. All the others have those two as well as "None".

At first, I thought that it was because of some unique setting for those four dimensions. But I haven't found anything just by browsing their definitions. Then I thought it was perhaps due to some unique usage of those four dimensions within the cube design. They are all used with the Financial Reporting measure group, but so is the Date dimension and it has "None" as an access option.

Anyone have any ideas? Probably something simple that I'm just overlooking, but I'd like to know why I can't set any of these to "None".

Thanks!

Dave Fackler

Did you ever find an answer to this? I'm having the same issue.

Difficult times deploying a few packages to SQL Server and running as a job

Hi Guys!

I have created a big list of packages, some calling others. They all work fine from my computer using Visual Studio.

When I try to deploy them (building them with deployment turned on and running them either directly from Management Studio or as a job) I get the errors with the password of connection strings. From what I read so far its the encryption process that kills it.

I have tried to add a password to some packages, but it still didnt work (only when run directly on my computer in management studio after deploying to SQL Server, but not as a job).

I have tried to change ProtectionLevel to SecurityStorage, wouldnt let me save in Visual Studio (I understand it is ot allowed in VS because you are saving to File System, how the hell am I supposed to save it to anything else? why is it showing there if its not even valid?).

If anyone can please give me the steps to doing it properly, that would be awesome. I simply need to run the packages from SQL Server! thats all! I have no idea why it has to be soooo difficult :/

A step-by-step guide would be different depending on several factors. Are you planning to store your packages as file system files? Are you using package configuration? How are you going to run the packages (may be via SQL Agent job)?

Personally, I store package as file system files and use something similar to method 4 in this KB article. I hope that helps

http://support.microsoft.com/kb/918760

|||

Hi Refael!

I don't mind saving it to the file system or putting it on SQL Server, as long as it works! :)

How do you actually create this package configuration? and how do you then indicate to the data process that the password is stored there?
I am replicating from Oracle using Microsoft Oracle provider (for which I need the password) to SQL Server.

Thanks so much for your help

Guy

|||

Weird thing: I have changed one package to have a password and when I import it to 'stored packages' or run it from the file system on Management Studio on the server itself (simply copying the files from the development machine to the server) its not asking me for a password and it runs it. does this makes sense?

|||

Just search for package configurations to set connection strings...it would make your packages nicely portables.

|||

I have tried to find out (google) how to make a configuration file in xml but couldn't find much and have no idea what the format should look like.

If you could help me here I would really appriciate it.

|||

guyguy2003 wrote:

I have tried to find out (google) how to make a configuration file in xml but couldn't find much and have no idea what the format should look like.

If you could help me here I would really appriciate it.

http://msdn2.microsoft.com/en-us/library/ms141682.aspxsql

Different Restore Error

I get the following error when I try to restore a database on a SQL 2005 server
TITLE: Microsoft SQL Server Management Studio Express
--
Restore failed for Server '172.27.101.60'. (Microsoft.SqlServer.Express.Smo)
For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476
--
ADDITIONAL INFORMATION:
System.Data.SqlClient.SqlError: The backup set holds a backup of a database
other than the existing 'Mirror_Test' database.
(Microsoft.SqlServer.Express.Smo)
For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&LinkId=20476
--
BUTTONS:
OK
--
--
MG"MGeles" <michael.geles@.thomson.com> wrote in message
news:87ECF046-E857-4682-AF97-8F6425F13AD9@.microsoft.com...
> I get the following error when I try to restore a database on a SQL 2005
server
Is is possible the backup file contains multiple backups within it.
> TITLE: Microsoft SQL Server Management Studio Express
> --
> Restore failed for Server '172.27.101.60'.
(Microsoft.SqlServer.Express.Smo)
> For help, click:
>
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476
> --
> ADDITIONAL INFORMATION:
> System.Data.SqlClient.SqlError: The backup set holds a backup of a
database
> other than the existing 'Mirror_Test' database.
> (Microsoft.SqlServer.Express.Smo)
> For help, click:
>
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&LinkId=20476
> --
> BUTTONS:
> OK
> --
> --
> MG

Different Restore Error

I get the following error when I try to restore a database on a SQL 2005 ser
ver
TITLE: Microsoft SQL Server Management Studio Express
--
Restore failed for Server '172.27.101.60'. (Microsoft.SqlServer.Express.Smo
)
For help, click:
http://go.microsoft.com/fwlink?Prod...er&LinkId=20476
ADDITIONAL INFORMATION:
System.Data.SqlClient.SqlError: The backup set holds a backup of a database
other than the existing 'Mirror_Test' database.
(Microsoft.SqlServer.Express.Smo)
For help, click:
http://go.microsoft.com/fwlink?Prod...>
LinkId=20476
BUTTONS:
OK
--
MG"MGeles" <michael.geles@.thomson.com> wrote in message
news:87ECF046-E857-4682-AF97-8F6425F13AD9@.microsoft.com...
> I get the following error when I try to restore a database on a SQL 2005
server
Is is possible the backup file contains multiple backups within it.

> TITLE: Microsoft SQL Server Management Studio Express
> --
> Restore failed for Server '172.27.101.60'.
(Microsoft.SqlServer.Express.Smo)
> For help, click:
>
http://go.microsoft.com/fwlink?Prod...r />
kId=20476
> --
> ADDITIONAL INFORMATION:
> System.Data.SqlClient.SqlError: The backup set holds a backup of a
database
> other than the existing 'Mirror_Test' database.
> (Microsoft.SqlServer.Express.Smo)
> For help, click:
>
[url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&LinkId=20476[/ur
l]
> --
> BUTTONS:
> OK
> --
> --
> MG

different formats of datetime?

Can somebody explain me why or due to what I have different formats of the same datetime data (SS2005) while opening a table (SS Mangmt Studio) and quering it?

For ex., AdventureWorks database, HumanResources.Department table, ModifiedDate column
1) RighClicking on table in ObjectExplorer (MS SQL Server Management Studio) --> Open Table shows the datetime data like
01.06.1998 0:00:00
2) executing
select ModifiedDate from HumanResources.Department
shows the same as
1998-06-01 00:00:00.000

Guennadi Vanine
PS.
I am trying to resolve the problem described in
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=180740&SiteID=1

Because datetime representation completely defined by client application, not by server storage. In a database this type stored in 8 byte container and its internal format has nothing in common with what you see when you retrieve the data.

About these two different formats... the first one is a typical German or Russian national date representation. In this place SSMS takes into consideration your computer' locale settings. The second one is a default format used in a query results.|||

Hmm, I see. The first one coincides with date in my Windows XP (Russian) though I am surprised that it is typical Russian (I have never seen the dates here used with dots, then Russia is multinational and multicultural federation having various republics)

Anyway, it is useful to remeber since this format from OS is the same (and independent on default language of installed db instance) for db instances with different different languages installed. And the default language/collation cannot be reset but only fresh (re)installed

I am still bewildered what is default language for? This does not have much sense to me especially due to the problem it is causing in SSIS [1], [2]

That one I could overcome only by installing additional db instance with English(US) as default language

[1]
collation or local sensitive settings or other configuration properties
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1566456&SiteID=1&mode=1
[2]
ETL Package Problem
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=603488&SiteID=1