Opening SQL Server Emterprise Maneger takes long time

Connectin to server goes quik but when listing databases
or expanding other sub level it takes about 1-3 min.
Anyone that has an ide why? (When making select in
databases this takes normal long time)Make sure that the autoclose database option isn't set for the databases, and that you don't have ODBC tracing turned
on.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Fredrik" <fredrik.ehrenholm@.proserva.com> wrote in message news:05fd01c36569$61b4b4b0$a501280a@.phx.gbl...
> Connectin to server goes quik but when listing databases
> or expanding other sub level it takes about 1-3 min.
> Anyone that has an ide why? (When making select in
> databases this takes normal long time)sql

Open Source Tool: Data Compare

Hi all,

Does anyone know any open source tool out there for these purposes?

1. Compare data rowcounts from two databases of the same structure.(On different servers).

2. Compare the structures of two databases.(On different servers).

3. Tool a developer can run on his/her desktop to capture a log of the database that his application is hitting.

Again all, I am new to the database world. Thank you for all assistance.For table structure compare tool there is http://sourceforge.net/projects/dabcos/

It was not finished the last time I used it.

Tim S|||We developed Database Comparing tool for our internal needs. This tool is mostly for programmers to support development/testing/production databases in different environments. Please take a look at http://www.dataassistance.com/Product_data_analysis.html
any feedback will be very welcome.

www.dataassistance.com|||For table structure compare tool there is http://sourceforge.net/projects/dabcos/

It was not finished the last time I used it.

Tim S

I look for this tools for a long time! Very useful tools for me!
Thanks!|||Indeed and I've used the reference from this site many times... a must for a Developer.

Open empty environment

What happened to the Query Designer? It's a modal window that doesn't allow you to drag tables from other databases into the query. SQL 2000's verion of Query Designer was so much better than 2005. What happened? Are the developers at Microsoft completely retarded?

Also, why do I have to log into my database servers everytime I open "Server Management Studio"? Who thought up that nice feature? Probably the same brillant folks that redesigned Query Designer. Why would you want to have a forced login procedure for every database everytime the program is opened. Once again Microsoft must only hire retarded developers. Give yourselves a pat on the back!

Seriously, I think the SQL 2005 team owns stock in a MySQL tools firm.

Please, someone needs to wake up in Redmond - resolve these serious concerns in a service pack.

Dear Angry SQL 2005 User,

First, you tone is complete unnecessary. It is possible to get your point across without insulting people. This is not a threat but a promise: if your tone continues to be abusive I will delete your postings and have your account shutdown. The forums are designed to provide a place for fruitful discussion between community members – it is not a place for abuse.

Second, I don’t understand your comments about the query window being modal. The query window in Management Studio (SSMS) is a document window which, by definition, is non-modal. Also, you can drag-and-drop tables and views from Object Explorer to the query window surface. Can you provide more details on why you think it’s modal and what specifically you’re looking for in drag-and-drop.

Third, you can suppress the login dialog at start-up by changing your user configuration. The default configuration is to “Open Object Explorer at startup”. Opening Object Explorer requires a connection to a server. If you want to just startup the environment without being prompted for a connection and then choose a server to connect to from Registered Servers do the following:

1) Launch SSMS

2) Go to Tools -> Options

3) Select “Environment” – it should be selected by default

4) Change the “At startup:” option to “Open empty environment”

This option will restore your window settings but will not pop the connection dialog. For example, I have a bunch of servers registered that show up in the Registered Servers tool window. When I launch SSMS with the “Open empty environment” option, SSMS loads without the connection dialog and the Registered Servers is populated. I can then select a server to connect to at which time I get the connection dialog.

I hope this information helps.

Dan

|||

Dan,

Thanks for the quick reply.

Your reply is probably referring to a different part of SSMS, I was referring to the GUI tool used for creating queries automatically without actually handcoding SQL code. In SQL 2000 the tool is very flexible and easy to use. To make sure we are talking about the same thing - In SQL 2000 just right click on a table and select 'open table' and then 'query'. This opens up the GUI query generator. In SQL 2005 to open the new GUI tool called Query Designer you select 'query' on the toolbar and then 'design query in editor'. This was the tool I was referring to, not the sql script window where you hand type code.

The Query Designer in SQL 2005 indeed locks the SQL 2005 screen (I'm not sure if it's called modal, however, in SQL 2000 the screen was not locked). Also, I'm using the production version of SQL 2005.

The queries we write span databases, therefore, the ability to drag a table from another database is crucial. Also, our queries are extremely complex and without the SQL 2000 technology we will have to write views to 'see' these other databases in the Query Designer. This is not a big deal, it's just that SQL 2005 is less flexible in many aspects. Since we are paying good money for the upgrade I think someone in Redmond should know that a few changes would make many folks happy.

|||

Angry SQL 2005 User,

Okay we're on the same page now wrt the query designer. Let me do a little more research on the subject and post back.

Dan

p.s. thanks for toning it down!

|||

Angry SQL 2005 User,

Here's what you need to do. In Management Studio select a table, right-click and select "Open Table". This will open up the table showing the contents of the table in a document window. Now, in the doc window right-click and choose the "Pane" option. Here you can turn on the Diagram, Criteria, and SQL panes. Now you can drag-and-drop tables and views from different DBs on to the surface. You can also turn on these panes from the "Query Designer" toolbar.

I believe this gets you what you want. If not let me know.

Dan

|||

Dan,

Thank you for the quick response.

Everyone at work is happy now!

|||

Hey Dan,

Thanks for setting that guy straight.

My problem, I followed exactly what you outlined for the Query Designer, However, I cannot drag and drop table on the the Diagram Pane, I just received a "null" sign when dragging the table on the pane. Is there an option somewhere to allow drag and drop. I did this successfully with the previous version (2000).

Thanks in advance,

Jim

|||

Hi Dan, good day.

I did what you say to stop prompting each time I open a saved query, but it does't work at all, SSMS keeps prompting to connect each time I open a query, this did't happen in the query analyser, why is this?

Thanks and regards, happy holydays

|||

Alfonso, My instructions were to stop SSMS from prompting when it's launched. When you open a new query you must either already have a connection established or SSMS will prompt you for the connection information. There is no concept of a delayed connection when authoring queries.

Cheers,
Dan

|||

Hi Jim, That's very odd. Let me ask a few folks around here what the problem could be. Your installation my be messed up. It may take me until next week to get an answer as lots of folks are off this week.

Cheers,
Dan

|||

To clarify a bit on what Dan said there are a few different ways query windows can operate, we attempted to do the "right thing" but you can always undo what we did if it isn't what you wanted.

If you ask for a new query window via File->New->Database Query (or any other), or the toolbar button to the RIGHT of the "New Query" button, we ALWAYS prompt for a connection, you can press cancel and you will get a query window that is disconnected. We will not prompt for a connection until you attempt to execute that query or ask to connect explicitly via the Query->Connection->Connect option, or toolbar button under the New Query button.

If you ask for a new query window via File->New->Query With Current Connection, or the New Query button, or Ctrl+N, then we create a new query window with whatever current connection context you have. If the focus is in a Query Window we will create a new one with the same connection (this is the same behavior as QA). If you have focus in Object Explorer or Registered servers on a server, we will create a query window connected to that server.

Finally if you ask for a new query window with the current connection (as above) but there is NO current context: no query window, focus on a disconnected query window, focus on the server type node in the registered servers window then we will prompt you for a connect and in this case, if you press cancel, the new query window generation is stopped.

If we end up connecting you to a server you didn't want or not connecting you at all you can click on the disconnect toolbar or the re-connect toolbar (or their associated menu's under Query->Connections) and get the query window to the right server.

Finally there are some context menu's to be aware of. From a registered server you can right click and go into the "Connect" menu and select Query Window. This will open a query window connected to this server (regardless if it is connected in Object Explorer).

From a connected server in OE you can right click and use the New Query menu option to open a query connected to that server. If you select a database in the Object Explorer and do a New Query from there, then you will be connected to that server with the selected database as the current database.

These same concepts apply to an Analysis Server connection just as they do to a Relational Server connection.

Play around with it and hopefully it will allow you to achieve what you would like, when you would like it.

|||

By the "null" sign I assume you mean the circle with the cross through it... yes?

If this is the case you can get this if you are trying to drag tables onto the diagram that are from a different database then where the diagram is stored. We don't have cross-database diagrams.

Does this help?

|||

Bruce,

I have been trying to do "open table" ever since I installed sql server 2005 in Decemeber. However everytime I select a database, select a table and right click and select "open table" I get the error "Object reference not set to an instance of an object". Next I would like to be able to edit the data directly in the results pane once this works.

Reading in different forums I am not sure this is still an option.

Also I have loaded the Adventure Works database and tried to "Open Table" and I get the same error above.

I am frustrated because there are so many little things missing or not working in "sql server 2005" that I regret even installing it. In sql server 2000 I could select an access database and import it and the tables appeared. In sql server 2005 I get multiple errors. I finally resorted to writing my own code to copy the access database into sql server 2005.

Please let me know any help would be appreciated.

|||

David, I'm sorry to hear you are having so many troubles. It definately sounds like something is definately wrong with your installation. The open table should just work and you can edit the results just like in Sql 2000. In fact this is exactly the same component that was used in Enterprise Manager and the open table feature there so the funcationality should be almost identical. I know there were some changes in this component but nothing major to my knoweldge (it isn't built by my team so I can't comment with absolute confidence here). I would highly recommend you contact Microsoft Support as it is unlikely something I could get figured out via e-mails.

I'd like to get more information as to what "little things are missing." As we continue to release new service packs for Sql we want to make sure and address as many of these types of things as we can.

One thing that we've also found is that because it is a bit different often the same functionality is available elsewhere in the tool and we just did a bad job of making it discoverable and once you know where it is, you'll find less things missing and they won't be hard to find... as you will have found them.

If you are willing to give me a list if you can make sure to articulate "how" you went about doing the things you miss. For example in your statement above you mention that you used to "import an access database" and then "the tables appeared." This is a little unclear. Do you mean you used the Import Data Wizard to import the tables and then those tables were in the Sql Server and you could work with them or are you talking about something else?

Enterprise Manager, Query Analyzer and SSMS are all very large tools and the "obvious" things we all do aren't necessarily obvious to somebody else because they do things slightly different. So the more information you can give us the better.

I would also recomend that you go to the feedback site and either vote on existing feedback entries or add new ones if you don't see what you find lacking.

http://lab.msdn.microsoft.com/productfeedback/default.aspx

---

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

|||

Bruce,

Thanks for the reply I appreciate it. The "open table" definitely does not work I now know you can edit data as in the old install and have seen someone else do it. when I right click and select new query and enter the query with a table name I get an error saying the object is not recognized. If I qualify the table name with the database name then it works. I tried to import an access data base and could not get it working. so I wrote the code and to do the import myself. When I delete tables tables or devices I have to hit the F5 key to refresh the view for the names to disappear. These are little things that worked in sql server 2000 that somehow don't in 2005.

I have backed up my databases and am going to unistall and reinstall and see if that gets rid of the open table issue.

Thanks

Open empty environment

What happened to the Query Designer? It's a modal window that doesn't allow you to drag tables from other databases into the query. SQL 2000's verion of Query Designer was so much better than 2005. What happened? Are the developers at Microsoft completely retarded?

Also, why do I have to log into my database servers everytime I open "Server Management Studio"? Who thought up that nice feature? Probably the same brillant folks that redesigned Query Designer. Why would you want to have a forced login procedure for every database everytime the program is opened. Once again Microsoft must only hire retarded developers. Give yourselves a pat on the back!

Seriously, I think the SQL 2005 team owns stock in a MySQL tools firm.

Please, someone needs to wake up in Redmond - resolve these serious concerns in a service pack.

Dear Angry SQL 2005 User,

First, you tone is complete unnecessary. It is possible to get your point across without insulting people. This is not a threat but a promise: if your tone continues to be abusive I will delete your postings and have your account shutdown. The forums are designed to provide a place for fruitful discussion between community members – it is not a place for abuse.

Second, I don’t understand your comments about the query window being modal. The query window in Management Studio (SSMS) is a document window which, by definition, is non-modal. Also, you can drag-and-drop tables and views from Object Explorer to the query window surface. Can you provide more details on why you think it’s modal and what specifically you’re looking for in drag-and-drop.

Third, you can suppress the login dialog at start-up by changing your user configuration. The default configuration is to “Open Object Explorer at startup”. Opening Object Explorer requires a connection to a server. If you want to just startup the environment without being prompted for a connection and then choose a server to connect to from Registered Servers do the following:

1) Launch SSMS

2) Go to Tools -> Options

3) Select “Environment” – it should be selected by default

4) Change the “At startup:” option to “Open empty environment”

This option will restore your window settings but will not pop the connection dialog. For example, I have a bunch of servers registered that show up in the Registered Servers tool window. When I launch SSMS with the “Open empty environment” option, SSMS loads without the connection dialog and the Registered Servers is populated. I can then select a server to connect to at which time I get the connection dialog.

I hope this information helps.

Dan

|||

Dan,

Thanks for the quick reply.

Your reply is probably referring to a different part of SSMS, I was referring to the GUI tool used for creating queries automatically without actually handcoding SQL code. In SQL 2000 the tool is very flexible and easy to use. To make sure we are talking about the same thing - In SQL 2000 just right click on a table and select 'open table' and then 'query'. This opens up the GUI query generator. In SQL 2005 to open the new GUI tool called Query Designer you select 'query' on the toolbar and then 'design query in editor'. This was the tool I was referring to, not the sql script window where you hand type code.

The Query Designer in SQL 2005 indeed locks the SQL 2005 screen (I'm not sure if it's called modal, however, in SQL 2000 the screen was not locked). Also, I'm using the production version of SQL 2005.

The queries we write span databases, therefore, the ability to drag a table from another database is crucial. Also, our queries are extremely complex and without the SQL 2000 technology we will have to write views to 'see' these other databases in the Query Designer. This is not a big deal, it's just that SQL 2005 is less flexible in many aspects. Since we are paying good money for the upgrade I think someone in Redmond should know that a few changes would make many folks happy.

|||

Angry SQL 2005 User,

Okay we're on the same page now wrt the query designer. Let me do a little more research on the subject and post back.

Dan

p.s. thanks for toning it down!

|||

Angry SQL 2005 User,

Here's what you need to do. In Management Studio select a table, right-click and select "Open Table". This will open up the table showing the contents of the table in a document window. Now, in the doc window right-click and choose the "Pane" option. Here you can turn on the Diagram, Criteria, and SQL panes. Now you can drag-and-drop tables and views from different DBs on to the surface. You can also turn on these panes from the "Query Designer" toolbar.

I believe this gets you what you want. If not let me know.

Dan

|||

Dan,

Thank you for the quick response.

Everyone at work is happy now!

|||

Hey Dan,

Thanks for setting that guy straight.

My problem, I followed exactly what you outlined for the Query Designer, However, I cannot drag and drop table on the the Diagram Pane, I just received a "null" sign when dragging the table on the pane. Is there an option somewhere to allow drag and drop. I did this successfully with the previous version (2000).

Thanks in advance,

Jim

|||

Hi Dan, good day.

I did what you say to stop prompting each time I open a saved query, but it does't work at all, SSMS keeps prompting to connect each time I open a query, this did't happen in the query analyser, why is this?

Thanks and regards, happy holydays

|||

Alfonso, My instructions were to stop SSMS from prompting when it's launched. When you open a new query you must either already have a connection established or SSMS will prompt you for the connection information. There is no concept of a delayed connection when authoring queries.

Cheers,
Dan

|||

Hi Jim, That's very odd. Let me ask a few folks around here what the problem could be. Your installation my be messed up. It may take me until next week to get an answer as lots of folks are off this week.

Cheers,
Dan

|||

To clarify a bit on what Dan said there are a few different ways query windows can operate, we attempted to do the "right thing" but you can always undo what we did if it isn't what you wanted.

If you ask for a new query window via File->New->Database Query (or any other), or the toolbar button to the RIGHT of the "New Query" button, we ALWAYS prompt for a connection, you can press cancel and you will get a query window that is disconnected. We will not prompt for a connection until you attempt to execute that query or ask to connect explicitly via the Query->Connection->Connect option, or toolbar button under the New Query button.

If you ask for a new query window via File->New->Query With Current Connection, or the New Query button, or Ctrl+N, then we create a new query window with whatever current connection context you have. If the focus is in a Query Window we will create a new one with the same connection (this is the same behavior as QA). If you have focus in Object Explorer or Registered servers on a server, we will create a query window connected to that server.

Finally if you ask for a new query window with the current connection (as above) but there is NO current context: no query window, focus on a disconnected query window, focus on the server type node in the registered servers window then we will prompt you for a connect and in this case, if you press cancel, the new query window generation is stopped.

If we end up connecting you to a server you didn't want or not connecting you at all you can click on the disconnect toolbar or the re-connect toolbar (or their associated menu's under Query->Connections) and get the query window to the right server.

Finally there are some context menu's to be aware of. From a registered server you can right click and go into the "Connect" menu and select Query Window. This will open a query window connected to this server (regardless if it is connected in Object Explorer).

From a connected server in OE you can right click and use the New Query menu option to open a query connected to that server. If you select a database in the Object Explorer and do a New Query from there, then you will be connected to that server with the selected database as the current database.

These same concepts apply to an Analysis Server connection just as they do to a Relational Server connection.

Play around with it and hopefully it will allow you to achieve what you would like, when you would like it.

|||

By the "null" sign I assume you mean the circle with the cross through it... yes?

If this is the case you can get this if you are trying to drag tables onto the diagram that are from a different database then where the diagram is stored. We don't have cross-database diagrams.

Does this help?

|||

Bruce,

I have been trying to do "open table" ever since I installed sql server 2005 in Decemeber. However everytime I select a database, select a table and right click and select "open table" I get the error "Object reference not set to an instance of an object". Next I would like to be able to edit the data directly in the results pane once this works.

Reading in different forums I am not sure this is still an option.

Also I have loaded the Adventure Works database and tried to "Open Table" and I get the same error above.

I am frustrated because there are so many little things missing or not working in "sql server 2005" that I regret even installing it. In sql server 2000 I could select an access database and import it and the tables appeared. In sql server 2005 I get multiple errors. I finally resorted to writing my own code to copy the access database into sql server 2005.

Please let me know any help would be appreciated.

|||

David, I'm sorry to hear you are having so many troubles. It definately sounds like something is definately wrong with your installation. The open table should just work and you can edit the results just like in Sql 2000. In fact this is exactly the same component that was used in Enterprise Manager and the open table feature there so the funcationality should be almost identical. I know there were some changes in this component but nothing major to my knoweldge (it isn't built by my team so I can't comment with absolute confidence here). I would highly recommend you contact Microsoft Support as it is unlikely something I could get figured out via e-mails.

I'd like to get more information as to what "little things are missing." As we continue to release new service packs for Sql we want to make sure and address as many of these types of things as we can.

One thing that we've also found is that because it is a bit different often the same functionality is available elsewhere in the tool and we just did a bad job of making it discoverable and once you know where it is, you'll find less things missing and they won't be hard to find... as you will have found them.

If you are willing to give me a list if you can make sure to articulate "how" you went about doing the things you miss. For example in your statement above you mention that you used to "import an access database" and then "the tables appeared." This is a little unclear. Do you mean you used the Import Data Wizard to import the tables and then those tables were in the Sql Server and you could work with them or are you talking about something else?

Enterprise Manager, Query Analyzer and SSMS are all very large tools and the "obvious" things we all do aren't necessarily obvious to somebody else because they do things slightly different. So the more information you can give us the better.

I would also recomend that you go to the feedback site and either vote on existing feedback entries or add new ones if you don't see what you find lacking.

http://lab.msdn.microsoft.com/productfeedback/default.aspx

---

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

|||

Bruce,

Thanks for the reply I appreciate it. The "open table" definitely does not work I now know you can edit data as in the old install and have seen someone else do it. when I right click and select new query and enter the query with a table name I get an error saying the object is not recognized. If I qualify the table name with the database name then it works. I tried to import an access data base and could not get it working. so I wrote the code and to do the import myself. When I delete tables tables or devices I have to hit the F5 key to refresh the view for the names to disappear. These are little things that worked in sql server 2000 that somehow don't in 2005.

I have backed up my databases and am going to unistall and reinstall and see if that gets rid of the open table issue.

Thanks

Open empty environment

What happened to the Query Designer? It's a modal window that doesn't allow you to drag tables from other databases into the query. SQL 2000's verion of Query Designer was so much better than 2005. What happened? Are the developers at Microsoft completely retarded?

Also, why do I have to log into my database servers everytime I open "Server Management Studio"? Who thought up that nice feature? Probably the same brillant folks that redesigned Query Designer. Why would you want to have a forced login procedure for every database everytime the program is opened. Once again Microsoft must only hire retarded developers. Give yourselves a pat on the back!

Seriously, I think the SQL 2005 team owns stock in a MySQL tools firm.

Please, someone needs to wake up in Redmond - resolve these serious concerns in a service pack.

Dear Angry SQL 2005 User,

First, you tone is complete unnecessary. It is possible to get your point across without insulting people. This is not a threat but a promise: if your tone continues to be abusive I will delete your postings and have your account shutdown. The forums are designed to provide a place for fruitful discussion between community members – it is not a place for abuse.

Second, I don’t understand your comments about the query window being modal. The query window in Management Studio (SSMS) is a document window which, by definition, is non-modal. Also, you can drag-and-drop tables and views from Object Explorer to the query window surface. Can you provide more details on why you think it’s modal and what specifically you’re looking for in drag-and-drop.

Third, you can suppress the login dialog at start-up by changing your user configuration. The default configuration is to “Open Object Explorer at startup”. Opening Object Explorer requires a connection to a server. If you want to just startup the environment without being prompted for a connection and then choose a server to connect to from Registered Servers do the following:

1) Launch SSMS

2) Go to Tools -> Options

3) Select “Environment” – it should be selected by default

4) Change the “At startup:” option to “Open empty environment”

This option will restore your window settings but will not pop the connection dialog. For example, I have a bunch of servers registered that show up in the Registered Servers tool window. When I launch SSMS with the “Open empty environment” option, SSMS loads without the connection dialog and the Registered Servers is populated. I can then select a server to connect to at which time I get the connection dialog.

I hope this information helps.

Dan

|||

Dan,

Thanks for the quick reply.

Your reply is probably referring to a different part of SSMS, I was referring to the GUI tool used for creating queries automatically without actually handcoding SQL code. In SQL 2000 the tool is very flexible and easy to use. To make sure we are talking about the same thing - In SQL 2000 just right click on a table and select 'open table' and then 'query'. This opens up the GUI query generator. In SQL 2005 to open the new GUI tool called Query Designer you select 'query' on the toolbar and then 'design query in editor'. This was the tool I was referring to, not the sql script window where you hand type code.

The Query Designer in SQL 2005 indeed locks the SQL 2005 screen (I'm not sure if it's called modal, however, in SQL 2000 the screen was not locked). Also, I'm using the production version of SQL 2005.

The queries we write span databases, therefore, the ability to drag a table from another database is crucial. Also, our queries are extremely complex and without the SQL 2000 technology we will have to write views to 'see' these other databases in the Query Designer. This is not a big deal, it's just that SQL 2005 is less flexible in many aspects. Since we are paying good money for the upgrade I think someone in Redmond should know that a few changes would make many folks happy.

|||

Angry SQL 2005 User,

Okay we're on the same page now wrt the query designer. Let me do a little more research on the subject and post back.

Dan

p.s. thanks for toning it down!

|||

Angry SQL 2005 User,

Here's what you need to do. In Management Studio select a table, right-click and select "Open Table". This will open up the table showing the contents of the table in a document window. Now, in the doc window right-click and choose the "Pane" option. Here you can turn on the Diagram, Criteria, and SQL panes. Now you can drag-and-drop tables and views from different DBs on to the surface. You can also turn on these panes from the "Query Designer" toolbar.

I believe this gets you what you want. If not let me know.

Dan

|||

Dan,

Thank you for the quick response.

Everyone at work is happy now!

|||

Hey Dan,

Thanks for setting that guy straight.

My problem, I followed exactly what you outlined for the Query Designer, However, I cannot drag and drop table on the the Diagram Pane, I just received a "null" sign when dragging the table on the pane. Is there an option somewhere to allow drag and drop. I did this successfully with the previous version (2000).

Thanks in advance,

Jim

|||

Hi Dan, good day.

I did what you say to stop prompting each time I open a saved query, but it does't work at all, SSMS keeps prompting to connect each time I open a query, this did't happen in the query analyser, why is this?

Thanks and regards, happy holydays

|||

Alfonso, My instructions were to stop SSMS from prompting when it's launched. When you open a new query you must either already have a connection established or SSMS will prompt you for the connection information. There is no concept of a delayed connection when authoring queries.

Cheers,
Dan

|||

Hi Jim, That's very odd. Let me ask a few folks around here what the problem could be. Your installation my be messed up. It may take me until next week to get an answer as lots of folks are off this week.

Cheers,
Dan

|||

To clarify a bit on what Dan said there are a few different ways query windows can operate, we attempted to do the "right thing" but you can always undo what we did if it isn't what you wanted.

If you ask for a new query window via File->New->Database Query (or any other), or the toolbar button to the RIGHT of the "New Query" button, we ALWAYS prompt for a connection, you can press cancel and you will get a query window that is disconnected. We will not prompt for a connection until you attempt to execute that query or ask to connect explicitly via the Query->Connection->Connect option, or toolbar button under the New Query button.

If you ask for a new query window via File->New->Query With Current Connection, or the New Query button, or Ctrl+N, then we create a new query window with whatever current connection context you have. If the focus is in a Query Window we will create a new one with the same connection (this is the same behavior as QA). If you have focus in Object Explorer or Registered servers on a server, we will create a query window connected to that server.

Finally if you ask for a new query window with the current connection (as above) but there is NO current context: no query window, focus on a disconnected query window, focus on the server type node in the registered servers window then we will prompt you for a connect and in this case, if you press cancel, the new query window generation is stopped.

If we end up connecting you to a server you didn't want or not connecting you at all you can click on the disconnect toolbar or the re-connect toolbar (or their associated menu's under Query->Connections) and get the query window to the right server.

Finally there are some context menu's to be aware of. From a registered server you can right click and go into the "Connect" menu and select Query Window. This will open a query window connected to this server (regardless if it is connected in Object Explorer).

From a connected server in OE you can right click and use the New Query menu option to open a query connected to that server. If you select a database in the Object Explorer and do a New Query from there, then you will be connected to that server with the selected database as the current database.

These same concepts apply to an Analysis Server connection just as they do to a Relational Server connection.

Play around with it and hopefully it will allow you to achieve what you would like, when you would like it.

|||

By the "null" sign I assume you mean the circle with the cross through it... yes?

If this is the case you can get this if you are trying to drag tables onto the diagram that are from a different database then where the diagram is stored. We don't have cross-database diagrams.

Does this help?

|||

Bruce,

I have been trying to do "open table" ever since I installed sql server 2005 in Decemeber. However everytime I select a database, select a table and right click and select "open table" I get the error "Object reference not set to an instance of an object". Next I would like to be able to edit the data directly in the results pane once this works.

Reading in different forums I am not sure this is still an option.

Also I have loaded the Adventure Works database and tried to "Open Table" and I get the same error above.

I am frustrated because there are so many little things missing or not working in "sql server 2005" that I regret even installing it. In sql server 2000 I could select an access database and import it and the tables appeared. In sql server 2005 I get multiple errors. I finally resorted to writing my own code to copy the access database into sql server 2005.

Please let me know any help would be appreciated.

|||

David, I'm sorry to hear you are having so many troubles. It definately sounds like something is definately wrong with your installation. The open table should just work and you can edit the results just like in Sql 2000. In fact this is exactly the same component that was used in Enterprise Manager and the open table feature there so the funcationality should be almost identical. I know there were some changes in this component but nothing major to my knoweldge (it isn't built by my team so I can't comment with absolute confidence here). I would highly recommend you contact Microsoft Support as it is unlikely something I could get figured out via e-mails.

I'd like to get more information as to what "little things are missing." As we continue to release new service packs for Sql we want to make sure and address as many of these types of things as we can.

One thing that we've also found is that because it is a bit different often the same functionality is available elsewhere in the tool and we just did a bad job of making it discoverable and once you know where it is, you'll find less things missing and they won't be hard to find... as you will have found them.

If you are willing to give me a list if you can make sure to articulate "how" you went about doing the things you miss. For example in your statement above you mention that you used to "import an access database" and then "the tables appeared." This is a little unclear. Do you mean you used the Import Data Wizard to import the tables and then those tables were in the Sql Server and you could work with them or are you talking about something else?

Enterprise Manager, Query Analyzer and SSMS are all very large tools and the "obvious" things we all do aren't necessarily obvious to somebody else because they do things slightly different. So the more information you can give us the better.

I would also recomend that you go to the feedback site and either vote on existing feedback entries or add new ones if you don't see what you find lacking.

http://lab.msdn.microsoft.com/productfeedback/default.aspx

---

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

|||

Bruce,

Thanks for the reply I appreciate it. The "open table" definitely does not work I now know you can edit data as in the old install and have seen someone else do it. when I right click and select new query and enter the query with a table name I get an error saying the object is not recognized. If I qualify the table name with the database name then it works. I tried to import an access data base and could not get it working. so I wrote the code and to do the import myself. When I delete tables tables or devices I have to hit the F5 key to refresh the view for the names to disappear. These are little things that worked in sql server 2000 that somehow don't in 2005.

I have backed up my databases and am going to unistall and reinstall and see if that gets rid of the open table issue.

Thanks

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>

only show days in datetime

hi
I want to search for documents created between different dates. Therefore I want to store a date in the format yyyy-mm-dd, but the databases store it as yyyy-mm-dd 00:00:00.000
how do I get rid of the hours and minutes?
Helenayou cannot get rid of them, they are part of the datatype

store your date values without a time component, and then when you search for a date range, you'll be fine

the only way people get into trouble is when they store values like getdate() which include an actual time

Only seeing dbs that have access to

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...
>
|||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...
>

Online Database Transfer

Hi.
I need to periodically copy my 3 databases to another SQL Server (both
SQL Server 2005).
I want to use "Copy Database Wizard" (mode SMO) or DTS Package
"Transfer database task" (online)
(I think booth use the same engines mechanism).
I have to use SMO/online method because my servers don't have a
network share-point.
Each of my databases has more then 100 tables (no more than 200).
During executing CDW job or DTS package I get an error:
"OnError,PC_NAME,ACCOUNT_NAME,PACKAGE_NAME,{5568F7 82-2BCD-4446-
BFF0-0287199B830E},{B5740EDC-D526-4B6E-BE13-E3FCCFFAA52C},
2007-02-26 22:50:08,2007-02-26 22:50:08,0,0x,ERROR :
errorCode=-1073548784 description=
Executing the query "IF EXISTS (SELECT name FROM sys.databases WHERE
name = N'DEST_TABLE_NAME')
DROP DATABASE [DEST_TABLE_NAME]
" failed with the following error: "Cannot drop database
"DEST_TABLE_NAME" because it is currently in use.".
Possible failure reasons: Problems with the query, "ResultSet"
property not set correctly, parameters not set correctly,
or connection not established correctly.
helpFile= helpContext=0 idofInterfaceWithError={8BDFE893-
E9D8-4D23-9739-DA807BCDC2AC}"
Every time the same error.
I did some research and I noticed that this error appears only if a
database has more than 49 tables.
I made a TEST databases with 49 empty and identical tables and the
process succeed. After I added another one table
(50th) then process failed.
Additionally I noticed that this error appears only on MS SLQ Server
2005 with SP1 or SP2. I was testing it on SLQ Server without Service
Pack
and I didn't get any error. (On the version without SP there is small
bug - tables loose a identity column info)
I'm waiting for any advices or suggestion ...
Greetings
gioodo
Hello,
The destination database must be already availble in the destinatgion server
and it should be used by some one. Best methodology
to copy the database to one server to another is:-
1. Backup the database using BACKUP DATABASE command
2. Copy the Backup (.BAK) file to destination
3.Set the destination database to Sinle user using ALTER DATABASE<dbname>
set single_user with rollback immediate
4. Restore the database using RESTORE DATABASE WITH MOVE options
5. Set the database to Multi user using ALTER DATABASE <Dbname> set
Multi_user
Thanks
Hari
<gioodo@.gmail.com> wrote in message
news:1172533698.435434.55890@.k78g2000cwa.googlegro ups.com...
> Hi.
> I need to periodically copy my 3 databases to another SQL Server (both
> SQL Server 2005).
> I want to use "Copy Database Wizard" (mode SMO) or DTS Package
> "Transfer database task" (online)
> (I think booth use the same engines mechanism).
> I have to use SMO/online method because my servers don't have a
> network share-point.
> Each of my databases has more then 100 tables (no more than 200).
> During executing CDW job or DTS package I get an error:
> "OnError,PC_NAME,ACCOUNT_NAME,PACKAGE_NAME,{5568F7 82-2BCD-4446-
> BFF0-0287199B830E},{B5740EDC-D526-4B6E-BE13-E3FCCFFAA52C},
> 2007-02-26 22:50:08,2007-02-26 22:50:08,0,0x,ERROR :
> errorCode=-1073548784 description=
> Executing the query "IF EXISTS (SELECT name FROM sys.databases WHERE
> name = N'DEST_TABLE_NAME')
> DROP DATABASE [DEST_TABLE_NAME]
> " failed with the following error: "Cannot drop database
> "DEST_TABLE_NAME" because it is currently in use.".
> Possible failure reasons: Problems with the query, "ResultSet"
> property not set correctly, parameters not set correctly,
> or connection not established correctly.
> helpFile= helpContext=0 idofInterfaceWithError={8BDFE893-
> E9D8-4D23-9739-DA807BCDC2AC}"
> Every time the same error.
> I did some research and I noticed that this error appears only if a
> database has more than 49 tables.
> I made a TEST databases with 49 empty and identical tables and the
> process succeed. After I added another one table
> (50th) then process failed.
> Additionally I noticed that this error appears only on MS SLQ Server
> 2005 with SP1 or SP2. I was testing it on SLQ Server without Service
> Pack
> and I didn't get any error. (On the version without SP there is small
> bug - tables loose a identity column info)
> I'm waiting for any advices or suggestion ...
> Greetings
> gioodo
>

Online Database Transfer

Hi.
I need to periodically copy my 3 databases to another SQL Server (both
SQL Server 2005).
I want to use "Copy Database Wizard" (mode SMO) or DTS Package
"Transfer database task" (online)
(I think booth use the same engines mechanism).
I have to use SMO/online method because my servers don't have a
network share-point.
Each of my databases has more then 100 tables (no more than 200).
During executing CDW job or DTS package I get an error:
" OnError,PC_NAME,ACCOUNT_NAME,PACKAGE_NAM
E,{5568F782-2BCD-4446-
BFF0-0287199B830E},{B5740EDC-D526-4B6E-BE13-E3FCCFFAA52C},
2007-02-26 22:50:08,2007-02-26 22:50:08,0,0x,ERROR :
errorCode=-1073548784 description=
Executing the query "IF EXISTS (SELECT name FROM sys.databases WHERE
name = N'DEST_TABLE_NAME')
DROP DATABASE [DEST_TABLE_NAME]
" failed with the following error: "Cannot drop database
"DEST_TABLE_NAME" because it is currently in use.".
Possible failure reasons: Problems with the query, "ResultSet"
property not set correctly, parameters not set correctly,
or connection not established correctly.
helpFile= helpContext=0 idofInterfaceWithError={8BDFE893-
E9D8-4D23-9739-DA807BCDC2AC}"
Every time the same error.
I did some research and I noticed that this error appears only if a
database has more than 49 tables.
I made a TEST databases with 49 empty and identical tables and the
process succeed. After I added another one table
(50th) then process failed.
Additionally I noticed that this error appears only on MS SLQ Server
2005 with SP1 or SP2. I was testing it on SLQ Server without Service
Pack
and I didn't get any error. (On the version without SP there is small
bug - tables loose a identity column info)
I'm waiting for any advices or suggestion ...
Greetings
gioodoHello,
The destination database must be already availble in the destinatgion server
and it should be used by some one. Best methodology
to copy the database to one server to another is:-
1. Backup the database using BACKUP DATABASE command
2. Copy the Backup (.BAK) file to destination
3.Set the destination database to Sinle user using ALTER DATABASE<dbname>
set single_user with rollback immediate
4. Restore the database using RESTORE DATABASE WITH MOVE options
5. Set the database to Multi user using ALTER DATABASE <Dbname> set
Multi_user
Thanks
Hari
<gioodo@.gmail.com> wrote in message
news:1172533698.435434.55890@.k78g2000cwa.googlegroups.com...
> Hi.
> I need to periodically copy my 3 databases to another SQL Server (both
> SQL Server 2005).
> I want to use "Copy Database Wizard" (mode SMO) or DTS Package
> "Transfer database task" (online)
> (I think booth use the same engines mechanism).
> I have to use SMO/online method because my servers don't have a
> network share-point.
> Each of my databases has more then 100 tables (no more than 200).
> During executing CDW job or DTS package I get an error:
> " OnError,PC_NAME,ACCOUNT_NAME,PACKAGE_NAM
E,{5568F782-2BCD-4446-
> BFF0-0287199B830E},{B5740EDC-D526-4B6E-BE13-E3FCCFFAA52C},
> 2007-02-26 22:50:08,2007-02-26 22:50:08,0,0x,ERROR :
> errorCode=-1073548784 description=
> Executing the query "IF EXISTS (SELECT name FROM sys.databases WHERE
> name = N'DEST_TABLE_NAME')
> DROP DATABASE [DEST_TABLE_NAME]
> " failed with the following error: "Cannot drop database
> "DEST_TABLE_NAME" because it is currently in use.".
> Possible failure reasons: Problems with the query, "ResultSet"
> property not set correctly, parameters not set correctly,
> or connection not established correctly.
> helpFile= helpContext=0 idofInterfaceWithError={8BDFE893-
> E9D8-4D23-9739-DA807BCDC2AC}"
> Every time the same error.
> I did some research and I noticed that this error appears only if a
> database has more than 49 tables.
> I made a TEST databases with 49 empty and identical tables and the
> process succeed. After I added another one table
> (50th) then process failed.
> Additionally I noticed that this error appears only on MS SLQ Server
> 2005 with SP1 or SP2. I was testing it on SLQ Server without Service
> Pack
> and I didn't get any error. (On the version without SP there is small
> bug - tables loose a identity column info)
> I'm waiting for any advices or suggestion ...
> Greetings
> gioodo
>

Online Database Transfer

Hi.
I need to periodically copy my 3 databases to another SQL Server (both
SQL Server 2005).
I want to use "Copy Database Wizard" (mode SMO) or DTS Package
"Transfer database task" (online)
(I think booth use the same engines mechanism).
I have to use SMO/online method because my servers don't have a
network share-point.
Each of my databases has more then 100 tables (no more than 200).
During executing CDW job or DTS package I get an error:
"OnError,PC_NAME,ACCOUNT_NAME,PACKAGE_NAME,{5568F782-2BCD-4446-
BFF0-0287199B830E},{B5740EDC-D526-4B6E-BE13-E3FCCFFAA52C},
2007-02-26 22:50:08,2007-02-26 22:50:08,0,0x,ERROR :
errorCode=-1073548784 description= Executing the query "IF EXISTS (SELECT name FROM sys.databases WHERE
name = N'DEST_TABLE_NAME')
DROP DATABASE [DEST_TABLE_NAME]
" failed with the following error: "Cannot drop database
"DEST_TABLE_NAME" because it is currently in use.".
Possible failure reasons: Problems with the query, "ResultSet"
property not set correctly, parameters not set correctly,
or connection not established correctly.
helpFile= helpContext=0 idofInterfaceWithError={8BDFE893-
E9D8-4D23-9739-DA807BCDC2AC}"
Every time the same error.
I did some research and I noticed that this error appears only if a
database has more than 49 tables.
I made a TEST databases with 49 empty and identical tables and the
process succeed. After I added another one table
(50th) then process failed.
Additionally I noticed that this error appears only on MS SLQ Server
2005 with SP1 or SP2. I was testing it on SLQ Server without Service
Pack
and I didn't get any error. (On the version without SP there is small
bug - tables loose a identity column info)
I'm waiting for any advices or suggestion ...
Greetings
gioodoHello,
The destination database must be already availble in the destinatgion server
and it should be used by some one. Best methodology
to copy the database to one server to another is:-
1. Backup the database using BACKUP DATABASE command
2. Copy the Backup (.BAK) file to destination
3.Set the destination database to Sinle user using ALTER DATABASE<dbname>
set single_user with rollback immediate
4. Restore the database using RESTORE DATABASE WITH MOVE options
5. Set the database to Multi user using ALTER DATABASE <Dbname> set
Multi_user
Thanks
Hari
<gioodo@.gmail.com> wrote in message
news:1172533698.435434.55890@.k78g2000cwa.googlegroups.com...
> Hi.
> I need to periodically copy my 3 databases to another SQL Server (both
> SQL Server 2005).
> I want to use "Copy Database Wizard" (mode SMO) or DTS Package
> "Transfer database task" (online)
> (I think booth use the same engines mechanism).
> I have to use SMO/online method because my servers don't have a
> network share-point.
> Each of my databases has more then 100 tables (no more than 200).
> During executing CDW job or DTS package I get an error:
> "OnError,PC_NAME,ACCOUNT_NAME,PACKAGE_NAME,{5568F782-2BCD-4446-
> BFF0-0287199B830E},{B5740EDC-D526-4B6E-BE13-E3FCCFFAA52C},
> 2007-02-26 22:50:08,2007-02-26 22:50:08,0,0x,ERROR :
> errorCode=-1073548784 description=> Executing the query "IF EXISTS (SELECT name FROM sys.databases WHERE
> name = N'DEST_TABLE_NAME')
> DROP DATABASE [DEST_TABLE_NAME]
> " failed with the following error: "Cannot drop database
> "DEST_TABLE_NAME" because it is currently in use.".
> Possible failure reasons: Problems with the query, "ResultSet"
> property not set correctly, parameters not set correctly,
> or connection not established correctly.
> helpFile= helpContext=0 idofInterfaceWithError={8BDFE893-
> E9D8-4D23-9739-DA807BCDC2AC}"
> Every time the same error.
> I did some research and I noticed that this error appears only if a
> database has more than 49 tables.
> I made a TEST databases with 49 empty and identical tables and the
> process succeed. After I added another one table
> (50th) then process failed.
> Additionally I noticed that this error appears only on MS SLQ Server
> 2005 with SP1 or SP2. I was testing it on SLQ Server without Service
> Pack
> and I didn't get any error. (On the version without SP there is small
> bug - tables loose a identity column info)
> I'm waiting for any advices or suggestion ...
> Greetings
> gioodo
>