opening up odbc data source in the query query inside of the server manager

I'm trying to find the command to open up an odbc conection inside sql2005 express. I only have ues of an odbc connector, we're conection to remedy. We will eventually be using stored procedures to extract the data we need from remedy and doing additional data crunching. I'm a foxpro programmer so once I get the correct syntax for making the odbc connector I shold be ok. Also I need a really good advanced book on sql2005. The type of book that would have my odbc answer. I've spent all morning trying to find this information and was unable to.

Thanks in advance

Daniel Buchanan.

If this was the wrong forum to post this on, please move this question to the correct one. I need this answer soon.

You can set up linked servers on one of the SQL Servers and join the data.

http://msdn2.microsoft.com/en-us/library/ms188279(SQL.90).aspx

hth

BobP

Opening table in SQL Server 2005?

This ought to be easy. But I can't figure out how to do it.
Using Enterprise Manager in SQL Server 2000, I point to a table, select it,
right click and choose Open Table-> Return all rows. Now I have the table
open and I can edit to my heart's content.
How can I do the same thing in SQL Server 2005?
Hi
With Beta 2 of SQL Server 2005, that functionality is not included. It may
return in Beta 3.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"David" <David@.blackdeck.com> wrote in message
news:39F0E432-BC98-476D-8A8C-B4AAF8E76ED8@.microsoft.com...
> This ought to be easy. But I can't figure out how to do it.
> Using Enterprise Manager in SQL Server 2000, I point to a table, select
it,
> right click and choose Open Table-> Return all rows. Now I have the table
> open and I can edit to my heart's content.
> How can I do the same thing in SQL Server 2005?
|||You got to be kidding! I use that all the time! Will it definitely be in
the final release?
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> With Beta 2 of SQL Server 2005, that functionality is not included. It may
> return in Beta 3.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "David" <David@.blackdeck.com> wrote in message
> news:39F0E432-BC98-476D-8A8C-B4AAF8E76ED8@.microsoft.com...
> it,
>
>
sql

opening my clients DB locally

My client has given me a DB file (.MDF) and I need to open it in order to export it to the remote DB.
In Enterprise Manager I go to 'New Database' - create a new DB and click on the 'Data' tab to browse to my clients file. Enterprise Manager recognises the file and everything seems hunky dory. but the resulting DB seems to have no tables.
The DB file is 1600K and has some stored procedures but if I try to export the tables there is nothing. His ASP files refer to tables in the SQL as you would expect.
Am I missing something? it's the weekend so he is not available and I don't want to look tooo stupid!look sp_attach_single_file_db up in sql server books online.|||Am I missing something? it's the weekend so he is not available and I don't want to look tooo stupid!It is always good to not look too stupid to a client... That kinda throws them off their feed for a bit. ;)

As Thrasymachus pointed out, you could use sp_attach_single_file_db (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ae-az_4wrm.asp), but carefully read the restrictions caused by not having the log file... Under certain circumstances, that can be a major problem.

-PatP

Open Table with Query to modify data

In Enterprise Manager, I would right click on the table, choose Open Table and Query where I could select specific records and (most importantly) could alter data in a record by deleting the text, adding or over-typing.

In 2005 Server Management Studio I just cannot figure how to do this. I'm guessing that I need the 'Script Table as' option but then what?

I have managed to open selected data using the New Query and then Design Query in Editor, but the results only appear in a kind of view form and I cannot seem to alter any of the data entries, I get dotted lines around the selected field.

Please help, it seemed so much easier in 2000!

(1) Open the database folder for the database that contains the target table; (2) locate the target table within the database; (3) right click on the target table; (4) select the "Open Table" option; (5) the table is opened and can now be edited.

See if this also works for you.

|||

Hi

Thanks for your reply, my problem is that the table we open most often in order to edit/change fields, has over 1.5 million record rows! So we're not keen on opening the whole table. The problem with using a query is that the results can't be edited.

|||YOu can, but it is sort of hidden in the toolbox pane. Use Open table > Stop the processing of the data rows (as you do not want to wait for 1,5M rows) using the red square at the bottom of the page. Switch on the SQL Pane using the little tool in the left corner (Show SQL Pane) > Modify your query to limit the results > Execute again > You should now be able to edit the data in the results pane.

Jens K. Suessmeyer

http://www.sqlserver2005.de
|||

Hi

Have done that and it works however, with more complex queries we prefer to use the Query Editor, selecting the field names and criteria in the grid and allowing the system to write the sql. Even with seemingly simple things like specifying a date time we are having to write the Convert DateTime instruction which is time consuming and somewhat frustrating.

I have worked out a stop gap solution in that I

1. open a New Query and then click the icon for Design Query in Editor

2. use the editor to write the query, copy the generated sql

3. open the table, stop the run of all records

4. Show the SQL pane and paste the query sql into it and run in order to get results that I can edit

It's not perfect and I have the problem that if I want to go back and adjust the query, I cannot open the original selection in Query Editor as it closes down after you have used it. I have to open up another new one and start again!

You could do all this in one window in Enterprise Manager, run a query, get 'editable' results and go back to the query to adjust it if need be, again getting 'editable' results.

Why have they removed this in 2005 and is it going to be resolved because I've now found other threads from users with the same problem.

Thanks

Annie

|||Well post it as a connect suggestion on http://connect.microsoft.com/sqlserver/Feedback :-)

Jens K. Suessmeyer

http://www.sqlserver2005.de
|||

Thanks Jens

Sorry we have only just set up the 2005 server and I am new to the forum so wasn't aware of the Connect site, but I will do that now.

Thanks again for the help

Open Table with Query to modify data

In Enterprise Manager, I would right click on the table, choose Open Table and Query where I could select specific records and (most importantly) could alter data in a record by deleting the text, adding or over-typing.

In 2005 Server Management Studio I just cannot figure how to do this. I'm guessing that I need the 'Script Table as' option but then what?

I have managed to open selected data using the New Query and then Design Query in Editor, but the results only appear in a kind of view form and I cannot seem to alter any of the data entries, I get dotted lines around the selected field.

Please help, it seemed so much easier in 2000!

(1) Open the database folder for the database that contains the target table; (2) locate the target table within the database; (3) right click on the target table; (4) select the "Open Table" option; (5) the table is opened and can now be edited.

See if this also works for you.

|||

Hi

Thanks for your reply, my problem is that the table we open most often in order to edit/change fields, has over 1.5 million record rows! So we're not keen on opening the whole table. The problem with using a query is that the results can't be edited.

|||YOu can, but it is sort of hidden in the toolbox pane. Use Open table > Stop the processing of the data rows (as you do not want to wait for 1,5M rows) using the red square at the bottom of the page. Switch on the SQL Pane using the little tool in the left corner (Show SQL Pane) > Modify your query to limit the results > Execute again > You should now be able to edit the data in the results pane.

Jens K. Suessmeyer

http://www.sqlserver2005.de
|||

Hi

Have done that and it works however, with more complex queries we prefer to use the Query Editor, selecting the field names and criteria in the grid and allowing the system to write the sql. Even with seemingly simple things like specifying a date time we are having to write the Convert DateTime instruction which is time consuming and somewhat frustrating.

I have worked out a stop gap solution in that I

1. open a New Query and then click the icon for Design Query in Editor

2. use the editor to write the query, copy the generated sql

3. open the table, stop the run of all records

4. Show the SQL pane and paste the query sql into it and run in order to get results that I can edit

It's not perfect and I have the problem that if I want to go back and adjust the query, I cannot open the original selection in Query Editor as it closes down after you have used it. I have to open up another new one and start again!

You could do all this in one window in Enterprise Manager, run a query, get 'editable' results and go back to the query to adjust it if need be, again getting 'editable' results.

Why have they removed this in 2005 and is it going to be resolved because I've now found other threads from users with the same problem.

Thanks

Annie

|||Well post it as a connect suggestion on http://connect.microsoft.com/sqlserver/Feedback :-)

Jens K. Suessmeyer

http://www.sqlserver2005.de
|||

Thanks Jens

Sorry we have only just set up the 2005 server and I am new to the forum so wasn't aware of the Connect site, but I will do that now.

Thanks again for the help

Open Table Query View Only Cross Joins!URGENT

Does anyone know why when I open a table in the enterprise manager in the
query view I don't see the real table name I see table_1 and any query built
that way only produces cross joins any ideas?EM enumerates the table names so that you can refer to more than one alias
of the same table in self-joins.
EM will automatically generate an INNER JOIN in the designer if a foreign
key exists between two of the tables. If no usable foreign key exists then
the query defaults to a CROSS JOIN. You can still add or change the join
type and criteria yourself.
--
David Portas
SQL Server MVP
--|||Because Enterprise Manager tries to think for you. As David states, you can
toggle the SQL button and construct the query however you really want it.
Or, you can use Query Analyzer -- Enterprise Manager is rarely the best
choice for viewing/manipulating data.
See http://www.aspfaq.com/2455
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Chris Calhoun" <calhoun_chris@.hotmail.com> wrote in message
news:u#EzQK9qEHA.3900@.TK2MSFTNGP10.phx.gbl...
> Does anyone know why when I open a table in the enterprise manager in the
> query view I don't see the real table name I see table_1 and any query
built
> that way only produces cross joins any ideas?
>

Open Table Query View Only Cross Joins!URGENT

Does anyone know why when I open a table in the enterprise manager in the
query view I don't see the real table name I see table_1 and any query built
that way only produces cross joins any ideas?
EM enumerates the table names so that you can refer to more than one alias
of the same table in self-joins.
EM will automatically generate an INNER JOIN in the designer if a foreign
key exists between two of the tables. If no usable foreign key exists then
the query defaults to a CROSS JOIN. You can still add or change the join
type and criteria yourself.
David Portas
SQL Server MVP
|||Because Enterprise Manager tries to think for you. As David states, you can
toggle the SQL button and construct the query however you really want it.
Or, you can use Query Analyzer -- Enterprise Manager is rarely the best
choice for viewing/manipulating data.
See http://www.aspfaq.com/2455
http://www.aspfaq.com/
(Reverse address to reply.)
"Chris Calhoun" <calhoun_chris@.hotmail.com> wrote in message
news:u#EzQK9qEHA.3900@.TK2MSFTNGP10.phx.gbl...
> Does anyone know why when I open a table in the enterprise manager in the
> query view I don't see the real table name I see table_1 and any query
built
> that way only produces cross joins any ideas?
>

Open Table > can't add data into the new row of the grid

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?

Open Table > can't add data into the new row of the grid

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?

Open File Dialog Like Enterprise Menager ?

Hi, It is possible to open a file dialog directly from another pc
(windows 2000 server sp4, msde sp3) like enterprise manager do when you
try to restaure a database?
I don't want to share folder on my server, and I want to select the
backup file (located on my server) from my application (run on my
computer).
I have try with SQL-DMO but I can find anything.
Thank youThe SQL-NS API expose the dialogs that EM has. Possibly this backup dialog a
s well. Note that SQL-NS
has not been carried forward to 2005.
Another option is do-it-yourself. I have done what you try to do by submitti
ng SQL queries to
enumerate disks, folders, files etc. You'd have to use Profiler to see what
extended stored
procedures that are used by these dialogs by EM, and note that many of these
are not documented =
not supported!
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<blacketik@.gmail.com> wrote in message news:1155222848.934108.285120@.h48g2000cwc.googlegroup
s.com...
> Hi, It is possible to open a file dialog directly from another pc
> (windows 2000 server sp4, msde sp3) like enterprise manager do when you
> try to restaure a database?
> I don't want to share folder on my server, and I want to select the
> backup file (located on my server) from my application (run on my
> computer).
> I have try with SQL-DMO but I can find anything.
> Thank you
>

Open File Dialog Like Enterprise Menager ?

Hi, It is possible to open a file dialog directly from another pc
(windows 2000 server sp4, msde sp3) like enterprise manager do when you
try to restaure a database?
I don't want to share folder on my server, and I want to select the
backup file (located on my server) from my application (run on my
computer).
I have try with SQL-DMO but I can find anything.
Thank youThe SQL-NS API expose the dialogs that EM has. Possibly this backup dialog as well. Note that SQL-NS
has not been carried forward to 2005.
Another option is do-it-yourself. I have done what you try to do by submitting SQL queries to
enumerate disks, folders, files etc. You'd have to use Profiler to see what extended stored
procedures that are used by these dialogs by EM, and note that many of these are not documented =not supported!
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<blacketik@.gmail.com> wrote in message news:1155222848.934108.285120@.h48g2000cwc.googlegroups.com...
> Hi, It is possible to open a file dialog directly from another pc
> (windows 2000 server sp4, msde sp3) like enterprise manager do when you
> try to restaure a database?
> I don't want to share folder on my server, and I want to select the
> backup file (located on my server) from my application (run on my
> computer).
> I have try with SQL-DMO but I can find anything.
> Thank you
>

open enterprise manager

hi I try to open enterprise manager it cannot open it becouse of mmc.exe
problem i upgrade my windows and reinstal sql but I still have that
problem,is there any way I can open enterprise manager whitout format my
windows?
cheers
mahsa
You don't need to format you windows!!!! Simply reinstall your sqlsever 2000
aganin.
"mahsa" <mahsa@.discussions.microsoft.com> wrote in message
news:17A652BD-DC55-4864-89E7-35DE21FE0E47@.microsoft.com...
> hi I try to open enterprise manager it cannot open it becouse of mmc.exe
> problem i upgrade my windows and reinstal sql but I still have that
> problem,is there any way I can open enterprise manager whitout format my
> windows?
> cheers
> --
> mahsa

Open default DB in Enterprise Manager upon connecting to server (SQL2000)

Hello,
1. My hosting provider hosts ~500 databases on the server where my DB
resides. To open my database I use SQL Server 2000 Enterprise Manager.
While server connection timeout is short enough (about 1-2 seconds), when I
click "Databases" folder in server hierarchy, trying to enlist all
databases, it takes and extremely long period of time to set up a list.
Moreover - 60% of attempts usually fail. - Enterprise Manager hangs deadly
and its process could be terminated only by Task Manager.
My first question is: why does EM take such a timeout to display a list of
databases and why in most cases it halts?
2. To open my database and to start working with it I each time have to
perform the above steps. However I find it rather silly - to enlist all
databases first and ONLY THEN select mine. Logical approach to the process
of database connection suggests NOT to enlist all databases (I really need
only one!), but configure server (client?) that way that upon connecting to
server it automatically defaults to my DB.
My second question is:
Has SQL Server 2000 Enterprise Manager such a feature? Can I configure it
not to enlist all databases upon connecting to server, but switch directly
to mine by default?
Thank you in advance.
Vsevolod.Hi
"Vsevolod Ukrainsky" wrote:
> Hello,
> 1. My hosting provider hosts ~500 databases on the server where my DB
> resides. To open my database I use SQL Server 2000 Enterprise Manager.
> While server connection timeout is short enough (about 1-2 seconds), when I
> click "Databases" folder in server hierarchy, trying to enlist all
> databases, it takes and extremely long period of time to set up a list.
> Moreover - 60% of attempts usually fail. - Enterprise Manager hangs deadly
> and its process could be terminated only by Task Manager.
> My first question is: why does EM take such a timeout to display a list of
> databases and why in most cases it halts?
> 2. To open my database and to start working with it I each time have to
> perform the above steps. However I find it rather silly - to enlist all
> databases first and ONLY THEN select mine. Logical approach to the process
> of database connection suggests NOT to enlist all databases (I really need
> only one!), but configure server (client?) that way that upon connecting to
> server it automatically defaults to my DB.
>
Enterprise manager lists only what is there, 500 databases on a single
instance is an unusually high number of databases. Every database on that
instance will be using the same system databases, therefore potentially
causing significant blocking of other users.
You should consider asking your ISP to have multiple instances with fewer
databases rather than getting MS to change their product for something that
should not be common practice.
> My second question is:
> Has SQL Server 2000 Enterprise Manager such a feature? Can I configure it
> not to enlist all databases upon connecting to server, but switch directly
> to mine by default?
No, and at this point in the life cycle of SQL 2000 it is (IMO) unlikely to
happen but you could log a feature request at
https://connect.microsoft.com/SQLServer
> Thank you in advance.
> Vsevolod.
John|||Thank you so much for your help!
"Vsevolod Ukrainsky" <vsevolod@.ukrainsky.ru> wrote in message
news:eobGc2ALHHA.780@.TK2MSFTNGP03.phx.gbl...
> Hello,
<SKIPPED>

Open default DB in Enterprise Manager upon connecting to server (SQL2000)

Hello,
1. My hosting provider hosts ~500 databases on the server where my DB
resides. To open my database I use SQL Server 2000 Enterprise Manager.
While server connection timeout is short enough (about 1-2 seconds), when I
click "Databases" folder in server hierarchy, trying to enlist all
databases, it takes and extremely long period of time to set up a list.
Moreover - 60% of attempts usually fail. - Enterprise Manager hangs deadly
and its process could be terminated only by Task Manager.
My first question is: why does EM take such a timeout to display a list of
databases and why in most cases it halts?
2. To open my database and to start working with it I each time have to
perform the above steps. However I find it rather silly - to enlist all
databases first and ONLY THEN select mine. Logical approach to the process
of database connection suggests NOT to enlist all databases (I really need
only one!), but configure server (client?) that way that upon connecting to
server it automatically defaults to my DB.
My second question is:
Has SQL Server 2000 Enterprise Manager such a feature? Can I configure it
not to enlist all databases upon connecting to server, but switch directly
to mine by default?
Thank you in advance.
Vsevolod.
Hi
"Vsevolod Ukrainsky" wrote:

> Hello,
> 1. My hosting provider hosts ~500 databases on the server where my DB
> resides. To open my database I use SQL Server 2000 Enterprise Manager.
> While server connection timeout is short enough (about 1-2 seconds), when I
> click "Databases" folder in server hierarchy, trying to enlist all
> databases, it takes and extremely long period of time to set up a list.
> Moreover - 60% of attempts usually fail. - Enterprise Manager hangs deadly
> and its process could be terminated only by Task Manager.
> My first question is: why does EM take such a timeout to display a list of
> databases and why in most cases it halts?
> 2. To open my database and to start working with it I each time have to
> perform the above steps. However I find it rather silly - to enlist all
> databases first and ONLY THEN select mine. Logical approach to the process
> of database connection suggests NOT to enlist all databases (I really need
> only one!), but configure server (client?) that way that upon connecting to
> server it automatically defaults to my DB.
>
Enterprise manager lists only what is there, 500 databases on a single
instance is an unusually high number of databases. Every database on that
instance will be using the same system databases, therefore potentially
causing significant blocking of other users.
You should consider asking your ISP to have multiple instances with fewer
databases rather than getting MS to change their product for something that
should not be common practice.

> My second question is:
> Has SQL Server 2000 Enterprise Manager such a feature? Can I configure it
> not to enlist all databases upon connecting to server, but switch directly
> to mine by default?
No, and at this point in the life cycle of SQL 2000 it is (IMO) unlikely to
happen but you could log a feature request at
https://connect.microsoft.com/SQLServer

> Thank you in advance.
> Vsevolod.
John

Open default DB in Enterprise Manager upon connecting to server (SQL2000)

Hello,
1. My hosting provider hosts ~500 databases on the server where my DB
resides. To open my database I use SQL Server 2000 Enterprise Manager.
While server connection timeout is short enough (about 1-2 seconds), when I
click "Databases" folder in server hierarchy, trying to enlist all
databases, it takes and extremely long period of time to set up a list.
Moreover - 60% of attempts usually fail. - Enterprise Manager hangs deadly
and its process could be terminated only by Task Manager.
My first question is: why does EM take such a timeout to display a list of
databases and why in most cases it halts?
2. To open my database and to start working with it I each time have to
perform the above steps. However I find it rather silly - to enlist all
databases first and ONLY THEN select mine. Logical approach to the process
of database connection suggests NOT to enlist all databases (I really need
only one!), but configure server (client?) that way that upon connecting to
server it automatically defaults to my DB.
My second question is:
Has SQL Server 2000 Enterprise Manager such a feature? Can I configure it
not to enlist all databases upon connecting to server, but switch directly
to mine by default?
Thank you in advance.
Vsevolod.Thank you so much for your help!
"Vsevolod Ukrainsky" <vsevolod@.ukrainsky.ru> wrote in message
news:eobGc2ALHHA.780@.TK2MSFTNGP03.phx.gbl...
> Hello,
<SKIPPED>

Open a report in new window

Hi!

In Report Manager, when you click a report, Is it possible to open it in a new window?

Thanks!

Destry

There is no setting for this. You need to use right click 'Open in new Window'

-Daniel

Only seeing dbs that have access to

Hi,
I came across a script to update the sp_MSdbuseraccess SP so that when a
user logs in to SQL Server 2000 using Enterprise Manager in SQL 2000 they
only see databases that they have access to
(http://support.microsoft.com/default.aspx/kb/889696). This works fine apart
from if users are using the new SQL Management Studio tool to connect to the
SQL 2000 server. Now they can see all databases again! Has anyone overcome
this issue?
Thanks - Kate.This is most probably not possible. SSMS doesn't use stored procedures when
doing things like
querying for what databases exist (*). And there isn't anything in the 2000
engine for this. There
is in 2005, where you by default (at the engine level) only see objects that
you actually have
access to (except for databases - you see all, but this is controllable thou
gh a REVOKE statement).
(*) I just ran a profiler trace to see the SQL statement submitted when expa
nding the databases
folder or the drop-down lost for a query windows. In both cases there was a
SELECT against a system
table. This was against a 2005 instance (I don't have a setup right now wher
e I can connect SSMS
against a 2000 instance), but I very much doubt it is any different.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Kate" <Kate_w112@.hotmail.com> wrote in message news:eHC6zgc3HHA.2064@.TK2MSFTNGP03.phx.gbl..
.
> Hi,
> I came across a script to update the sp_MSdbuseraccess SP so that when a u
ser logs in to SQL
> Server 2000 using Enterprise Manager in SQL 2000 they only see databases t
hat they have access to
> (http://support.microsoft.com/default.aspx/kb/889696). This works fine apa
rt from if users are
> using the new SQL Management Studio tool to connect to the SQL 2000 server
. Now they can see all
> databases again! Has anyone overcome this issue?
> Thanks - Kate.|||Ok great - what was the SQL statement?
thanks.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Ozw%23nrd3HHA.2752@.TK2MSFTNGP06.phx.gbl...
> This is most probably not possible. SSMS doesn't use stored procedures
> when doing things like querying for what databases exist (*). And there
> isn't anything in the 2000 engine for this. There is in 2005, where you by
> default (at the engine level) only see objects that you actually have
> access to (except for databases - you see all, but this is controllable
> though a REVOKE statement).
> (*) I just ran a profiler trace to see the SQL statement submitted when
> expanding the databases folder or the drop-down lost for a query windows.
> In both cases there was a SELECT against a system table. This was against
> a 2005 instance (I don't have a setup right now where I can connect SSMS
> against a 2000 instance), but I very much doubt it is any different.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Kate" <Kate_w112@.hotmail.com> wrote in message
> news:eHC6zgc3HHA.2064@.TK2MSFTNGP03.phx.gbl...
>|||you mention a REVOKE statement for SQL 2005 installs - could you elaborate
on this?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Ozw%23nrd3HHA.2752@.TK2MSFTNGP06.phx.gbl...
> This is most probably not possible. SSMS doesn't use stored procedures
> when doing things like querying for what databases exist (*). And there
> isn't anything in the 2000 engine for this. There is in 2005, where you by
> default (at the engine level) only see objects that you actually have
> access to (except for databases - you see all, but this is controllable
> though a REVOKE statement).
> (*) I just ran a profiler trace to see the SQL statement submitted when
> expanding the databases folder or the drop-down lost for a query windows.
> In both cases there was a SELECT against a system table. This was against
> a 2005 instance (I don't have a setup right now where I can connect SSMS
> against a 2000 instance), but I very much doubt it is any different.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Kate" <Kate_w112@.hotmail.com> wrote in message
> news:eHC6zgc3HHA.2064@.TK2MSFTNGP03.phx.gbl...
>|||Basically a SELECT from sys.databases. Remember, I was running against a 200
5 instance, so you won't
see the same SELECT against a 2000 instance (you will probably see some SELE
CT from sysdatabases).
But you can easily check this yourself using the same method I did (using Pr
ofiler).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Kate" <Kate_w112@.hotmail.com> wrote in message news:eLAEWnf3HHA.464@.TK2MSFTNGP02.phx.gbl...

> Ok great - what was the SQL statement?
> thanks.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:Ozw%23nrd3HHA.2752@.TK2MSFTNGP06.phx.gbl...
>|||Check out the "VIEW ANY DATABASE" privilege. Try below, for example:
CREATE LOGIN x WITH PASSWORD = 'hjus8#."3'
EXECUTE AS LOGIN = 'x'
SELECT name FROM sys.databases --14 rows
REVERT
DENY VIEW ANY DATABASE TO x
EXECUTE AS LOGIN = 'x'
SELECT name FROM sys.databases --2 rows
REVERT
GO
DROP LOGIN x
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Kate" <Kate_w112@.hotmail.com> wrote in message news:Oj0C6nf3HHA.5796@.TK2MSFTNGP05.phx.gbl..
.
> you mention a REVOKE statement for SQL 2005 installs - could you elaborate
> on this?
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:Ozw%23nrd3HHA.2752@.TK2MSFTNGP06.phx.gbl...
>

Only seeing dbs that have access to

Hi,
I came across a script to update the sp_MSdbuseraccess SP so that when a
user logs in to SQL Server 2000 using Enterprise Manager in SQL 2000 they
only see databases that they have access to
(http://support.microsoft.com/default.aspx/kb/889696). This works fine apart
from if users are using the new SQL Management Studio tool to connect to the
SQL 2000 server. Now they can see all databases again! Has anyone overcome
this issue?
Thanks - Kate.This is most probably not possible. SSMS doesn't use stored procedures when doing things like
querying for what databases exist (*). And there isn't anything in the 2000 engine for this. There
is in 2005, where you by default (at the engine level) only see objects that you actually have
access to (except for databases - you see all, but this is controllable though a REVOKE statement).
(*) I just ran a profiler trace to see the SQL statement submitted when expanding the databases
folder or the drop-down lost for a query windows. In both cases there was a SELECT against a system
table. This was against a 2005 instance (I don't have a setup right now where I can connect SSMS
against a 2000 instance), but I very much doubt it is any different.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Kate" <Kate_w112@.hotmail.com> wrote in message news:eHC6zgc3HHA.2064@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I came across a script to update the sp_MSdbuseraccess SP so that when a user logs in to SQL
> Server 2000 using Enterprise Manager in SQL 2000 they only see databases that they have access to
> (http://support.microsoft.com/default.aspx/kb/889696). This works fine apart from if users are
> using the new SQL Management Studio tool to connect to the SQL 2000 server. Now they can see all
> databases again! Has anyone overcome this issue?
> Thanks - Kate.|||Ok great - what was the SQL statement?
thanks.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Ozw%23nrd3HHA.2752@.TK2MSFTNGP06.phx.gbl...
> This is most probably not possible. SSMS doesn't use stored procedures
> when doing things like querying for what databases exist (*). And there
> isn't anything in the 2000 engine for this. There is in 2005, where you by
> default (at the engine level) only see objects that you actually have
> access to (except for databases - you see all, but this is controllable
> though a REVOKE statement).
> (*) I just ran a profiler trace to see the SQL statement submitted when
> expanding the databases folder or the drop-down lost for a query windows.
> In both cases there was a SELECT against a system table. This was against
> a 2005 instance (I don't have a setup right now where I can connect SSMS
> against a 2000 instance), but I very much doubt it is any different.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Kate" <Kate_w112@.hotmail.com> wrote in message
> news:eHC6zgc3HHA.2064@.TK2MSFTNGP03.phx.gbl...
>> Hi,
>> I came across a script to update the sp_MSdbuseraccess SP so that when a
>> user logs in to SQL Server 2000 using Enterprise Manager in SQL 2000 they
>> only see databases that they have access to
>> (http://support.microsoft.com/default.aspx/kb/889696). This works fine
>> apart from if users are using the new SQL Management Studio tool to
>> connect to the SQL 2000 server. Now they can see all databases again! Has
>> anyone overcome this issue?
>> Thanks - Kate.
>|||you mention a REVOKE statement for SQL 2005 installs - could you elaborate
on this?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Ozw%23nrd3HHA.2752@.TK2MSFTNGP06.phx.gbl...
> This is most probably not possible. SSMS doesn't use stored procedures
> when doing things like querying for what databases exist (*). And there
> isn't anything in the 2000 engine for this. There is in 2005, where you by
> default (at the engine level) only see objects that you actually have
> access to (except for databases - you see all, but this is controllable
> though a REVOKE statement).
> (*) I just ran a profiler trace to see the SQL statement submitted when
> expanding the databases folder or the drop-down lost for a query windows.
> In both cases there was a SELECT against a system table. This was against
> a 2005 instance (I don't have a setup right now where I can connect SSMS
> against a 2000 instance), but I very much doubt it is any different.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Kate" <Kate_w112@.hotmail.com> wrote in message
> news:eHC6zgc3HHA.2064@.TK2MSFTNGP03.phx.gbl...
>> Hi,
>> I came across a script to update the sp_MSdbuseraccess SP so that when a
>> user logs in to SQL Server 2000 using Enterprise Manager in SQL 2000 they
>> only see databases that they have access to
>> (http://support.microsoft.com/default.aspx/kb/889696). This works fine
>> apart from if users are using the new SQL Management Studio tool to
>> connect to the SQL 2000 server. Now they can see all databases again! Has
>> anyone overcome this issue?
>> Thanks - Kate.
>|||Basically a SELECT from sys.databases. Remember, I was running against a 2005 instance, so you won't
see the same SELECT against a 2000 instance (you will probably see some SELECT from sysdatabases).
But you can easily check this yourself using the same method I did (using Profiler).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Kate" <Kate_w112@.hotmail.com> wrote in message news:eLAEWnf3HHA.464@.TK2MSFTNGP02.phx.gbl...
> Ok great - what was the SQL statement?
> thanks.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:Ozw%23nrd3HHA.2752@.TK2MSFTNGP06.phx.gbl...
>> This is most probably not possible. SSMS doesn't use stored procedures when doing things like
>> querying for what databases exist (*). And there isn't anything in the 2000 engine for this.
>> There is in 2005, where you by default (at the engine level) only see objects that you actually
>> have access to (except for databases - you see all, but this is controllable though a REVOKE
>> statement).
>> (*) I just ran a profiler trace to see the SQL statement submitted when expanding the databases
>> folder or the drop-down lost for a query windows. In both cases there was a SELECT against a
>> system table. This was against a 2005 instance (I don't have a setup right now where I can
>> connect SSMS against a 2000 instance), but I very much doubt it is any different.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Kate" <Kate_w112@.hotmail.com> wrote in message news:eHC6zgc3HHA.2064@.TK2MSFTNGP03.phx.gbl...
>> Hi,
>> I came across a script to update the sp_MSdbuseraccess SP so that when a user logs in to SQL
>> Server 2000 using Enterprise Manager in SQL 2000 they only see databases that they have access
>> to (http://support.microsoft.com/default.aspx/kb/889696). This works fine apart from if users
>> are using the new SQL Management Studio tool to connect to the SQL 2000 server. Now they can see
>> all databases again! Has anyone overcome this issue?
>> Thanks - Kate.
>|||Check out the "VIEW ANY DATABASE" privilege. Try below, for example:
CREATE LOGIN x WITH PASSWORD = 'hjus8#."3'
EXECUTE AS LOGIN = 'x'
SELECT name FROM sys.databases --14 rows
REVERT
DENY VIEW ANY DATABASE TO x
EXECUTE AS LOGIN = 'x'
SELECT name FROM sys.databases --2 rows
REVERT
GO
DROP LOGIN x
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Kate" <Kate_w112@.hotmail.com> wrote in message news:Oj0C6nf3HHA.5796@.TK2MSFTNGP05.phx.gbl...
> you mention a REVOKE statement for SQL 2005 installs - could you elaborate
> on this?
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:Ozw%23nrd3HHA.2752@.TK2MSFTNGP06.phx.gbl...
>> This is most probably not possible. SSMS doesn't use stored procedures
>> when doing things like querying for what databases exist (*). And there
>> isn't anything in the 2000 engine for this. There is in 2005, where you by
>> default (at the engine level) only see objects that you actually have
>> access to (except for databases - you see all, but this is controllable
>> though a REVOKE statement).
>> (*) I just ran a profiler trace to see the SQL statement submitted when
>> expanding the databases folder or the drop-down lost for a query windows.
>> In both cases there was a SELECT against a system table. This was against
>> a 2005 instance (I don't have a setup right now where I can connect SSMS
>> against a 2000 instance), but I very much doubt it is any different.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Kate" <Kate_w112@.hotmail.com> wrote in message
>> news:eHC6zgc3HHA.2064@.TK2MSFTNGP03.phx.gbl...
>> Hi,
>> I came across a script to update the sp_MSdbuseraccess SP so that when a
>> user logs in to SQL Server 2000 using Enterprise Manager in SQL 2000 they
>> only see databases that they have access to
>> (http://support.microsoft.com/default.aspx/kb/889696). This works fine
>> apart from if users are using the new SQL Management Studio tool to
>> connect to the SQL 2000 server. Now they can see all databases again! Has
>> anyone overcome this issue?
>> Thanks - Kate.
>

Only see groups in Enterprise Manager

I have to export the servers registered in Enterprise Manager and import the
servers into another 30 machines. I exported the registry of
HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL
Server\80\Tools\SQLEW\Registered Servers X
but when I import it, I only see the groups. Tried install and uninstall
enterprise manager but not helping.
thanks, larryYou may have to do it manually, and if so, the approach you're taking may
not be practical.
You could run profiler while setting up a registration, and see if it would
be possible to replicate the code that EM uses for the wizard and make it
programmable. I'm not sure if that will give you everything you need, since
some things might happen outside of the visibility of profiler.
I believe I read once that the sqldiag utility (located in the BINN folder)
will export registrations, but unless there's some undocumented argument I
haven't tried, I can only get it to store aliases (not registrations).
Do all 30 servers really need registrations to every single server? Are
they all round robin, so when you connect to manage all of them, you could
be on any node?
If not, you might consider reserving one or two "control" servers for
management, and not expecting to manage any server from any server.
If the servers just need to see each other (e.g. for queries) then it might
be better to just create aliases (if they're not DNS'able) and linked
servers (which are programmable) on all of the servers.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"LarryLiMCDBA" <larryliwork@.hotmail.com> wrote in message
news:OAm0Xqt1DHA.2972@.TK2MSFTNGP09.phx.gbl...
> I have to export the servers registered in Enterprise Manager and import
the
> servers into another 30 machines. I exported the registry of
> HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL
> Server\80\Tools\SQLEW\Registered Servers X
> but when I import it, I only see the groups. Tried install and uninstall
> enterprise manager but not helping.
> thanks, larry
>|||By the way, registrations are user-based. Are you logging into the
destination machine with the same user as the source machine? It might be
that user-specific information is encoded in the data stored in those
registry values... so if you connect to Enterprise Manager as a different
user, or with SQL Server authentication, you might not see those
registrations.
Look in the registry of the destination machine and see which user your
registrations appear under. If the .reg file ran without error, they're
probably there somewhere.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"LarryLiMCDBA" <larryliwork@.hotmail.com> wrote in message
news:OAm0Xqt1DHA.2972@.TK2MSFTNGP09.phx.gbl...
> I have to export the servers registered in Enterprise Manager and import
the
> servers into another 30 machines. I exported the registry of
> HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL
> Server\80\Tools\SQLEW\Registered Servers X
> but when I import it, I only see the groups. Tried install and uninstall
> enterprise manager but not helping.
> thanks, larry
>|||I tried both HKEY local machine and HKEY current user but couldn't make it
work. I was able to import the alias though and SQL Query Analyzer actually
works. I guess there is a encryption process somewhere for the registered
servers in EM.
tks, larry
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:%2300Ai9u1DHA.2328@.TK2MSFTNGP10.phx.gbl...
> By the way, registrations are user-based. Are you logging into the
> destination machine with the same user as the source machine? It might be
> that user-specific information is encoded in the data stored in those
> registry values... so if you connect to Enterprise Manager as a different
> user, or with SQL Server authentication, you might not see those
> registrations.
> Look in the registry of the destination machine and see which user your
> registrations appear under. If the .reg file ran without error, they're
> probably there somewhere.
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
>
> "LarryLiMCDBA" <larryliwork@.hotmail.com> wrote in message
> news:OAm0Xqt1DHA.2972@.TK2MSFTNGP09.phx.gbl...
> > I have to export the servers registered in Enterprise Manager and import
> the
> > servers into another 30 machines. I exported the registry of
> > HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL
> > Server\80\Tools\SQLEW\Registered Servers X
> >
> > but when I import it, I only see the groups. Tried install and
uninstall
> > enterprise manager but not helping.
> >
> > thanks, larry
> >
> >
>

Only see groups in Enterprise Manager

I have to export the servers registered in Enterprise Manager and import the
servers into another 30 machines. I exported the registry of
HKEY_CURRENT_USER\Software\Microsoft\Mic
rosoft SQL
Server\80\Tools\SQLEW\Registered Servers X
but when I import it, I only see the groups. Tried install and uninstall
enterprise manager but not helping.
thanks, larryYou may have to do it manually, and if so, the approach you're taking may
not be practical.
You could run profiler while setting up a registration, and see if it would
be possible to replicate the code that EM uses for the wizard and make it
programmable. I'm not sure if that will give you everything you need, since
some things might happen outside of the visibility of profiler.
I believe I read once that the sqldiag utility (located in the BINN folder)
will export registrations, but unless there's some undocumented argument I
haven't tried, I can only get it to store aliases (not registrations).
Do all 30 servers really need registrations to every single server? Are
they all round robin, so when you connect to manage all of them, you could
be on any node?
If not, you might consider reserving one or two "control" servers for
management, and not expecting to manage any server from any server.
If the servers just need to see each other (e.g. for queries) then it might
be better to just create aliases (if they're not DNS'able) and linked
servers (which are programmable) on all of the servers.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"LarryLiMCDBA" <larryliwork@.hotmail.com> wrote in message
news:OAm0Xqt1DHA.2972@.TK2MSFTNGP09.phx.gbl...

quote:

---

> I have to export the servers registered in Enterprise Manager and import

---

the

quote:

---

> servers into another 30 machines. I exported the registry of
> HKEY_CURRENT_USER\Software\Microsoft\Mic
rosoft SQL
> Server\80\Tools\SQLEW\Registered Servers X
> but when I import it, I only see the groups. Tried install and uninstall
> enterprise manager but not helping.
> thanks, larry
>

---

|||By the way, registrations are user-based. Are you logging into the
destination machine with the same user as the source machine? It might be
that user-specific information is encoded in the data stored in those
registry values... so if you connect to Enterprise Manager as a different
user, or with SQL Server authentication, you might not see those
registrations.
Look in the registry of the destination machine and see which user your
registrations appear under. If the .reg file ran without error, they're
probably there somewhere.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"LarryLiMCDBA" <larryliwork@.hotmail.com> wrote in message
news:OAm0Xqt1DHA.2972@.TK2MSFTNGP09.phx.gbl...

quote:

---

> I have to export the servers registered in Enterprise Manager and import

---

the

quote:

---

> servers into another 30 machines. I exported the registry of
> HKEY_CURRENT_USER\Software\Microsoft\Mic
rosoft SQL
> Server\80\Tools\SQLEW\Registered Servers X
> but when I import it, I only see the groups. Tried install and uninstall
> enterprise manager but not helping.
> thanks, larry
>

---

|||I tried both HKEY local machine and HKEY current user but couldn't make it
work. I was able to import the alias though and SQL Query Analyzer actually
works. I guess there is a encryption process somewhere for the registered
servers in EM.
tks, larry
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:%2300Ai9u1DHA.2328@.TK2MSFTNGP10.phx.gbl...

quote:

---

> By the way, registrations are user-based. Are you logging into the
> destination machine with the same user as the source machine? It might be
> that user-specific information is encoded in the data stored in those
> registry values... so if you connect to Enterprise Manager as a different
> user, or with SQL Server authentication, you might not see those
> registrations.
> Look in the registry of the destination machine and see which user your
> registrations appear under. If the .reg file ran without error, they're
> probably there somewhere.
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
>
> "LarryLiMCDBA" <larryliwork@.hotmail.com> wrote in message
> news:OAm0Xqt1DHA.2972@.TK2MSFTNGP09.phx.gbl...
> the
uninstall[QUOTE]
>

---