openning cursor inside trigger works in sql2000 but not in 2005

Hello everyone,
I have a delete trigger on table, inside which there is cursor opened using
a dynamically generated query.
this worked fine on sql 2000, but on 2005, I get the following error:
Msg 16958
Could not complete cursor operation because the set options have changed
since the cursor was declared.
for testing, I replaced the generated query by a static one and it worked
fine.
any Ideas ?
here is the code:
select * into #TabTmp from deleted
set @.req = 'declare CUR1 cursor for select ' + @.Cle + ' from #TabTmp'
execute(@.req)
OPEN CUR1
FETCH CUR into @.val
the error is generated on the "OPEN CUR1" statement.
I did a DBCC USEROPTIONS before and after the "execute" statement but there
was no change.
thanks in advance.
It sounds like you've hit this bug:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=247905
-Sue
On Mon, 14 May 2007 12:16:02 -0700, r_samir
<rsamir@.discussions.microsoft.com> wrote:

>Hello everyone,
>I have a delete trigger on table, inside which there is cursor opened using
>a dynamically generated query.
>this worked fine on sql 2000, but on 2005, I get the following error:
>Msg 16958
>Could not complete cursor operation because the set options have changed
>since the cursor was declared.
>for testing, I replaced the generated query by a static one and it worked
>fine.
>any Ideas ?
>here is the code:
>----
>select * into #TabTmp from deleted
>set @.req = 'declare CUR1 cursor for select ' + @.Cle + ' from #TabTmp'
>execute(@.req)
>OPEN CUR1
>FETCH CUR into @.val
>---
>the error is generated on the "OPEN CUR1" statement.
>I did a DBCC USEROPTIONS before and after the "execute" statement but there
>was no change.
>thanks in advance.
|||Maybe it's not the same bug - I missed the part where you
said you replaced the dynamic SQL and it worked.
So if that worked and then cursors inside triggers aren't
necessarily the best idea, if dynamic sql isn't necessarily
the best idea, then maybe it's better to just redo the logic
and change the code for the trigger?
-Sue
On Mon, 14 May 2007 12:16:02 -0700, r_samir
<rsamir@.discussions.microsoft.com> wrote:

>Hello everyone,
>I have a delete trigger on table, inside which there is cursor opened using
>a dynamically generated query.
>this worked fine on sql 2000, but on 2005, I get the following error:
>Msg 16958
>Could not complete cursor operation because the set options have changed
>since the cursor was declared.
>for testing, I replaced the generated query by a static one and it worked
>fine.
>any Ideas ?
>here is the code:
>----
>select * into #TabTmp from deleted
>set @.req = 'declare CUR1 cursor for select ' + @.Cle + ' from #TabTmp'
>execute(@.req)
>OPEN CUR1
>FETCH CUR into @.val
>---
>the error is generated on the "OPEN CUR1" statement.
>I did a DBCC USEROPTIONS before and after the "execute" statement but there
>was no change.
>thanks in advance.
|||"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:cp6i43p2dgq16qcic4d36dodp6pl3pmq3q@.4ax.com...
> Maybe it's not the same bug - I missed the part where you
> said you replaced the dynamic SQL and it worked.
> So if that worked and then cursors inside triggers aren't
> necessarily the best idea, if dynamic sql isn't necessarily
> the best idea, then maybe it's better to just redo the logic
> and change the code for the trigger?
A bug that should guide the user to a more logical
and socially accepted solution. Kewl spin-a-rama.
Why waste such talent on such a niche audience.
It's the government that really rewards such
artistic sophistry -
|||thanks for your prompt response
redoing the logic is feasable but would take some time, we are currently
investigating this way.
but is there a way to verify whether we fall into that bug or not ?
"Sue Hoegemeier" wrote:

> Maybe it's not the same bug - I missed the part where you
> said you replaced the dynamic SQL and it worked.
> So if that worked and then cursors inside triggers aren't
> necessarily the best idea, if dynamic sql isn't necessarily
> the best idea, then maybe it's better to just redo the logic
> and change the code for the trigger?
> -Sue
> On Mon, 14 May 2007 12:16:02 -0700, r_samir
> <rsamir@.discussions.microsoft.com> wrote:
>
>

openning cursor inside trigger works in sql2000 but not in 2005

Hello everyone,
I have a delete trigger on table, inside which there is cursor opened using
a dynamically generated query.
this worked fine on sql 2000, but on 2005, I get the following error:
Msg 16958
Could not complete cursor operation because the set options have changed
since the cursor was declared.
for testing, I replaced the generated query by a static one and it worked
fine.
any Ideas ?
here is the code:
----
select * into #TabTmp from deleted
set @.req = 'declare CUR1 cursor for select ' + @.Cle + ' from #TabTmp'
execute(@.req)
OPEN CUR1
FETCH CUR into @.val
---
the error is generated on the "OPEN CUR1" statement.
I did a DBCC USEROPTIONS before and after the "execute" statement but there
was no change.
thanks in advance.It sounds like you've hit this bug:
https://connect.microsoft.com/SQLSe...=2479
05
-Sue
On Mon, 14 May 2007 12:16:02 -0700, r_samir
<rsamir@.discussions.microsoft.com> wrote:

>Hello everyone,
>I have a delete trigger on table, inside which there is cursor opened using
>a dynamically generated query.
>this worked fine on sql 2000, but on 2005, I get the following error:
>Msg 16958
>Could not complete cursor operation because the set options have changed
>since the cursor was declared.
>for testing, I replaced the generated query by a static one and it worked
>fine.
>any Ideas ?
>here is the code:
>----
>select * into #TabTmp from deleted
>set @.req = 'declare CUR1 cursor for select ' + @.Cle + ' from #TabTmp'
>execute(@.req)
>OPEN CUR1
>FETCH CUR into @.val
>---
>the error is generated on the "OPEN CUR1" statement.
>I did a DBCC USEROPTIONS before and after the "execute" statement but ther
e
>was no change.
>thanks in advance.|||Maybe it's not the same bug - I missed the part where you
said you replaced the dynamic SQL and it worked.
So if that worked and then cursors inside triggers aren't
necessarily the best idea, if dynamic sql isn't necessarily
the best idea, then maybe it's better to just redo the logic
and change the code for the trigger?
-Sue
On Mon, 14 May 2007 12:16:02 -0700, r_samir
<rsamir@.discussions.microsoft.com> wrote:

>Hello everyone,
>I have a delete trigger on table, inside which there is cursor opened using
>a dynamically generated query.
>this worked fine on sql 2000, but on 2005, I get the following error:
>Msg 16958
>Could not complete cursor operation because the set options have changed
>since the cursor was declared.
>for testing, I replaced the generated query by a static one and it worked
>fine.
>any Ideas ?
>here is the code:
>----
>select * into #TabTmp from deleted
>set @.req = 'declare CUR1 cursor for select ' + @.Cle + ' from #TabTmp'
>execute(@.req)
>OPEN CUR1
>FETCH CUR into @.val
>---
>the error is generated on the "OPEN CUR1" statement.
>I did a DBCC USEROPTIONS before and after the "execute" statement but ther
e
>was no change.
>thanks in advance.|||"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:cp6i43p2dgq16qcic4d36dodp6pl3pmq3q@.
4ax.com...
> Maybe it's not the same bug - I missed the part where you
> said you replaced the dynamic SQL and it worked.
> So if that worked and then cursors inside triggers aren't
> necessarily the best idea, if dynamic sql isn't necessarily
> the best idea, then maybe it's better to just redo the logic
> and change the code for the trigger?
A bug that should guide the user to a more logical
and socially accepted solution. Kewl spin-a-rama.
Why waste such talent on such a niche audience.
It's the government that really rewards such
artistic sophistry -|||thanks for your prompt response
redoing the logic is feasable but would take some time, we are currently
investigating this way.
but is there a way to verify whether we fall into that bug or not ?
"Sue Hoegemeier" wrote:

> Maybe it's not the same bug - I missed the part where you
> said you replaced the dynamic SQL and it worked.
> So if that worked and then cursors inside triggers aren't
> necessarily the best idea, if dynamic sql isn't necessarily
> the best idea, then maybe it's better to just redo the logic
> and change the code for the trigger?
> -Sue
> On Mon, 14 May 2007 12:16:02 -0700, r_samir
> <rsamir@.discussions.microsoft.com> wrote:
>
>

openning cursor inside trigger works in sql2000 but not in 2005

Hello everyone,
I have a delete trigger on table, inside which there is cursor opened using
a dynamically generated query.
this worked fine on sql 2000, but on 2005, I get the following error:
Msg 16958
Could not complete cursor operation because the set options have changed
since the cursor was declared.
for testing, I replaced the generated query by a static one and it worked
fine.
any Ideas ?
here is the code:
----
select * into #TabTmp from deleted
set @.req = 'declare CUR1 cursor for select ' + @.Cle + ' from #TabTmp'
execute(@.req)
OPEN CUR1
FETCH CUR into @.val
---
the error is generated on the "OPEN CUR1" statement.
I did a DBCC USEROPTIONS before and after the "execute" statement but there
was no change.
thanks in advance.It sounds like you've hit this bug:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=247905
-Sue
On Mon, 14 May 2007 12:16:02 -0700, r_samir
<rsamir@.discussions.microsoft.com> wrote:
>Hello everyone,
>I have a delete trigger on table, inside which there is cursor opened using
>a dynamically generated query.
>this worked fine on sql 2000, but on 2005, I get the following error:
>Msg 16958
>Could not complete cursor operation because the set options have changed
>since the cursor was declared.
>for testing, I replaced the generated query by a static one and it worked
>fine.
>any Ideas ?
>here is the code:
>----
>select * into #TabTmp from deleted
>set @.req = 'declare CUR1 cursor for select ' + @.Cle + ' from #TabTmp'
>execute(@.req)
>OPEN CUR1
>FETCH CUR into @.val
>---
>the error is generated on the "OPEN CUR1" statement.
>I did a DBCC USEROPTIONS before and after the "execute" statement but there
>was no change.
>thanks in advance.|||Maybe it's not the same bug - I missed the part where you
said you replaced the dynamic SQL and it worked.
So if that worked and then cursors inside triggers aren't
necessarily the best idea, if dynamic sql isn't necessarily
the best idea, then maybe it's better to just redo the logic
and change the code for the trigger?
-Sue
On Mon, 14 May 2007 12:16:02 -0700, r_samir
<rsamir@.discussions.microsoft.com> wrote:
>Hello everyone,
>I have a delete trigger on table, inside which there is cursor opened using
>a dynamically generated query.
>this worked fine on sql 2000, but on 2005, I get the following error:
>Msg 16958
>Could not complete cursor operation because the set options have changed
>since the cursor was declared.
>for testing, I replaced the generated query by a static one and it worked
>fine.
>any Ideas ?
>here is the code:
>----
>select * into #TabTmp from deleted
>set @.req = 'declare CUR1 cursor for select ' + @.Cle + ' from #TabTmp'
>execute(@.req)
>OPEN CUR1
>FETCH CUR into @.val
>---
>the error is generated on the "OPEN CUR1" statement.
>I did a DBCC USEROPTIONS before and after the "execute" statement but there
>was no change.
>thanks in advance.|||"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:cp6i43p2dgq16qcic4d36dodp6pl3pmq3q@.4ax.com...
> Maybe it's not the same bug - I missed the part where you
> said you replaced the dynamic SQL and it worked.
> So if that worked and then cursors inside triggers aren't
> necessarily the best idea, if dynamic sql isn't necessarily
> the best idea, then maybe it's better to just redo the logic
> and change the code for the trigger?
A bug that should guide the user to a more logical
and socially accepted solution. Kewl spin-a-rama.
Why waste such talent on such a niche audience.
It's the government that really rewards such
artistic sophistry -:)|||thanks for your prompt response
redoing the logic is feasable but would take some time, we are currently
investigating this way.
but is there a way to verify whether we fall into that bug or not ?
"Sue Hoegemeier" wrote:
> Maybe it's not the same bug - I missed the part where you
> said you replaced the dynamic SQL and it worked.
> So if that worked and then cursors inside triggers aren't
> necessarily the best idea, if dynamic sql isn't necessarily
> the best idea, then maybe it's better to just redo the logic
> and change the code for the trigger?
> -Sue
> On Mon, 14 May 2007 12:16:02 -0700, r_samir
> <rsamir@.discussions.microsoft.com> wrote:
> >Hello everyone,
> >
> >I have a delete trigger on table, inside which there is cursor opened using
> >a dynamically generated query.
> >this worked fine on sql 2000, but on 2005, I get the following error:
> >Msg 16958
> >Could not complete cursor operation because the set options have changed
> >since the cursor was declared.
> >
> >for testing, I replaced the generated query by a static one and it worked
> >fine.
> >
> >any Ideas ?
> >
> >here is the code:
> >
> >----
> >select * into #TabTmp from deleted
> >
> >set @.req = 'declare CUR1 cursor for select ' + @.Cle + ' from #TabTmp'
> >
> >execute(@.req)
> >
> >OPEN CUR1
> >
> >FETCH CUR into @.val
> >---
> >
> >the error is generated on the "OPEN CUR1" statement.
> >I did a DBCC USEROPTIONS before and after the "execute" statement but there
> >was no change.
> >
> >thanks in advance.
>

OPENDATASOURCE Problems (permission)

Hi all,
I am running SQL2000 SP4 and want to run the command:
SELECT *
FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data
Source="f:\jackpot\jackpot.mdb"; User ID=Admin;Password=')..."master table
with members"
logged in as "sa" it is fine but when another user no "sa" runs I get:
Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.4.0' has been denied.
You must access this provider through a linked server.
Can anyone help me? The user that will run that is not "sa"
thanks
JulioJulio
http://support.microsoft.com/kb/327489
"Julio Mattos" <JulioMattos@.discussions.microsoft.com> wrote in message
news:732AD50A-E369-49E4-AE60-A5256075C37B@.microsoft.com...
> Hi all,
> I am running SQL2000 SP4 and want to run the command:
> SELECT *
> FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data
> Source="f:\jackpot\jackpot.mdb"; User ID=Admin;Password=')..."master table
> with members"
> logged in as "sa" it is fine but when another user no "sa" runs I get:
> Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.4.0' has been
> denied.
> You must access this provider through a linked server.
> Can anyone help me? The user that will run that is not "sa"
> thanks
> Julio|||Hi Uri,
thanks for that, I created a new registry entry (DisallowAdHocAccess) under
'Microsoft Jet 4.0 OLE DB Provider' set to 0 and it's working fine. The key
is the DisallowAdHocAccess for each provider.
thanks
Julio M
"Uri Dimant" wrote:
> Julio
> http://support.microsoft.com/kb/327489
>
> "Julio Mattos" <JulioMattos@.discussions.microsoft.com> wrote in message
> news:732AD50A-E369-49E4-AE60-A5256075C37B@.microsoft.com...
> > Hi all,
> >
> > I am running SQL2000 SP4 and want to run the command:
> >
> > SELECT *
> > FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data
> > Source="f:\jackpot\jackpot.mdb"; User ID=Admin;Password=')..."master table
> > with members"
> >
> > logged in as "sa" it is fine but when another user no "sa" runs I get:
> >
> > Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.4.0' has been
> > denied.
> > You must access this provider through a linked server.
> >
> > Can anyone help me? The user that will run that is not "sa"
> >
> > thanks
> > Julio
>
>

OPENDATASOURCE Problems (permission)

Hi all,
I am running SQL2000 SP4 and want to run the command:
SELECT *
FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data
Source="f:\jackpot\jackpot.mdb"; User ID=Admin;Password=')..."master table
with members"
logged in as "sa" it is fine but when another user no "sa" runs I get:
Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.4.0' has been denied.
You must access this provider through a linked server.
Can anyone help me? The user that will run that is not "sa"
thanks
Julio
Julio
http://support.microsoft.com/kb/327489
"Julio Mattos" <JulioMattos@.discussions.microsoft.com> wrote in message
news:732AD50A-E369-49E4-AE60-A5256075C37B@.microsoft.com...
> Hi all,
> I am running SQL2000 SP4 and want to run the command:
> SELECT *
> FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data
> Source="f:\jackpot\jackpot.mdb"; User ID=Admin;Password=')..."master table
> with members"
> logged in as "sa" it is fine but when another user no "sa" runs I get:
> Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.4.0' has been
> denied.
> You must access this provider through a linked server.
> Can anyone help me? The user that will run that is not "sa"
> thanks
> Julio
|||Hi Uri,
thanks for that, I created a new registry entry (DisallowAdHocAccess) under
'Microsoft Jet 4.0 OLE DB Provider' set to 0 and it's working fine. The key
is the DisallowAdHocAccess for each provider.
thanks
Julio M
"Uri Dimant" wrote:

> Julio
> http://support.microsoft.com/kb/327489
>
> "Julio Mattos" <JulioMattos@.discussions.microsoft.com> wrote in message
> news:732AD50A-E369-49E4-AE60-A5256075C37B@.microsoft.com...
>
>

OPENDATASOURCE Problems (permission)

Hi all,
I am running SQL2000 SP4 and want to run the command:
SELECT *
FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data
Source="f:\jackpot\jackpot.mdb"; User ID=Admin;Password=')..."master table
with members"
logged in as "sa" it is fine but when another user no "sa" runs I get:
Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.4.0' has been denied.
You must access this provider through a linked server.
Can anyone help me? The user that will run that is not "sa"
thanks
JulioJulio
http://support.microsoft.com/kb/327489
"Julio Mattos" <JulioMattos@.discussions.microsoft.com> wrote in message
news:732AD50A-E369-49E4-AE60-A5256075C37B@.microsoft.com...
> Hi all,
> I am running SQL2000 SP4 and want to run the command:
> SELECT *
> FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data
> Source="f:\jackpot\jackpot.mdb"; User ID=Admin;Password=')..."master table
> with members"
> logged in as "sa" it is fine but when another user no "sa" runs I get:
> Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.4.0' has been
> denied.
> You must access this provider through a linked server.
> Can anyone help me? The user that will run that is not "sa"
> thanks
> Julio|||Hi Uri,
thanks for that, I created a new registry entry (DisallowAdHocAccess) under
'Microsoft Jet 4.0 OLE DB Provider' set to 0 and it's working fine. The key
is the DisallowAdHocAccess for each provider.
thanks
Julio M
"Uri Dimant" wrote:

> Julio
> http://support.microsoft.com/kb/327489
>
> "Julio Mattos" <JulioMattos@.discussions.microsoft.com> wrote in message
> news:732AD50A-E369-49E4-AE60-A5256075C37B@.microsoft.com...
>
>

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>

Online index defrag i SQL 7 ?

I have used dbcc indexdefrag i sql2000 but are there one in sql 7.0 which
are doing the same thing online ? DBCC dbreindex is not online or ?
I don't want the operation to interfer with the users.
Sincerely
\\Jonas BI'm sorry, Jonas, but INDEXDEFRAG was introduced in SQL2K. Not much we can do about that, there
isn't anything similar in 7.0.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jonte@.bson.se" <jonasberthelsson@.hotmail.com> wrote in message
news:uG%232EVEhEHA.3520@.TK2MSFTNGP10.phx.gbl...
> I have used dbcc indexdefrag i sql2000 but are there one in sql 7.0 which
> are doing the same thing online ? DBCC dbreindex is not online or ?
> I don't want the operation to interfer with the users.
> Sincerely
> \\Jonas B
>

Online index defrag i SQL 7 ?

I have used dbcc indexdefrag i sql2000 but are there one in sql 7.0 which
are doing the same thing online ? DBCC dbreindex is not online or ?
I don't want the operation to interfer with the users.
Sincerely
\\Jonas B
I'm sorry, Jonas, but INDEXDEFRAG was introduced in SQL2K. Not much we can do about that, there
isn't anything similar in 7.0.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jonte@.bson.se" <jonasberthelsson@.hotmail.com> wrote in message
news:uG%232EVEhEHA.3520@.TK2MSFTNGP10.phx.gbl...
> I have used dbcc indexdefrag i sql2000 but are there one in sql 7.0 which
> are doing the same thing online ? DBCC dbreindex is not online or ?
> I don't want the operation to interfer with the users.
> Sincerely
> \\Jonas B
>

Online index defrag i SQL 7 ?

I have used dbcc indexdefrag i sql2000 but are there one in sql 7.0 which
are doing the same thing online ? DBCC dbreindex is not online or ?
I don't want the operation to interfer with the users.
Sincerely
\\Jonas BI'm sorry, Jonas, but INDEXDEFRAG was introduced in SQL2K. Not much we can d
o about that, there
isn't anything similar in 7.0.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jonte@.bson.se" <jonasberthelsson@.hotmail.com> wrote in message
news:uG%232EVEhEHA.3520@.TK2MSFTNGP10.phx.gbl...
> I have used dbcc indexdefrag i sql2000 but are there one in sql 7.0 which
> are doing the same thing online ? DBCC dbreindex is not online or ?
> I don't want the operation to interfer with the users.
> Sincerely
> \\Jonas B
>