Entire 2005 Database deleted

The Database Name is showing up but there are 0 items contained in it. ldf
and mdf files deleted. Not deleted by Users. Event log shows a problem
reading a backup tape.Hi
Refresh SSMS anf the database will probably gone. Do you have a backup of
the database?
"CF" <CF@.discussions.microsoft.com> wrote in message
news:DDE9AD7E-FA19-403E-BE39-21AAD41726E7@.microsoft.com...
> The Database Name is showing up but there are 0 items contained in it.
> ldf
> and mdf files deleted. Not deleted by Users. Event log shows a problem
> reading a backup tape.

Entire 2005 Database deleted

The Database Name is showing up but there are 0 items contained in it. ldf
and mdf files deleted. Not deleted by Users. Event log shows a problem
reading a backup tape.
Hi
Refresh SSMS anf the database will probably gone. Do you have a backup of
the database?
"CF" <CF@.discussions.microsoft.com> wrote in message
news:DDE9AD7E-FA19-403E-BE39-21AAD41726E7@.microsoft.com...
> The Database Name is showing up but there are 0 items contained in it.
> ldf
> and mdf files deleted. Not deleted by Users. Event log shows a problem
> reading a backup tape.

Entire 2005 Database deleted

The Database Name is showing up but there are 0 items contained in it. ldf
and mdf files deleted. Not deleted by Users. Event log shows a problem
reading a backup tape.Hi
Refresh SSMS anf the database will probably gone. Do you have a backup of
the database?
"CF" <CF@.discussions.microsoft.com> wrote in message
news:DDE9AD7E-FA19-403E-BE39-21AAD41726E7@.microsoft.com...
> The Database Name is showing up but there are 0 items contained in it.
> ldf
> and mdf files deleted. Not deleted by Users. Event log shows a problem
> reading a backup tape.

Enterprise Mananger Info not sticking

My PC is running Windows XP.
When I registered SQL Server in the Enterprise Manager,
the information of the registered server is gone (No
items) after I log off.
I have found some registry entries under
HKCU\Software\Microsoft\Microsoft SQL Server\80
\Tools\SQLEW\Registered Servers X\SQL Server Group
that seem to deal with Enterprise Manager.
Any ideas ?
JohnnyHi,
It seems you do not have previlages in writing into the registry. Try
registering with a Admin OS user. If it is able to do then , ask your
system administrater to give prev (Write) to the below registry key .
HKLM\Software\microsoft\MSSQLSERVER (Root key itself)
Thanks
Hari
MCDBA
"Johnny" <jtao@.ssc.nsw.gov.au> wrote in message
news:f81601c43e31$9ca40110$a101280a@.phx.gbl...
> My PC is running Windows XP.
> When I registered SQL Server in the Enterprise Manager,
> the information of the registered server is gone (No
> items) after I log off.
> I have found some registry entries under
> HKCU\Software\Microsoft\Microsoft SQL Server\80
> \Tools\SQLEW\Registered Servers X\SQL Server Group
> that seem to deal with Enterprise Manager.
> Any ideas ?
> Johnnysql

Enterprise Manager over a VPN gets No items

I have an environment that has 18 separate SQL server machines. When
connected to the LAN I can connect to each and expand the databases tab no
problem. When I try the same over a VPN I find that three of my servers will
connect but on expanding the databases I get a long pause then 'No items'.
I've checked around and found some references to the same problem (SQL
server mag site) but no resolutions. I'm running XP pro and my SQL is sp3.
The three servers in question have only TCP and named pipes in the net lib,
they are all on Win2K one is on sp3, one on sp2 and one is 'out of the box'.
The VPN has ports 1433 and 1434 udp and tcp allowed.

Does anyone have any suggestions as to what is causing this, its got to be
something simple but I've been looking at this so long now I cant see the
wood for the trees.

Help appreciated.There is a least one known issue that can produce this:

http://support.microsoft.com/default.aspx?scid=kb;[LN];Q315523

Here are some comments from an MS engineer on it:

http://groups.google.ch/groups?hl=e...%40cpmsftngxa06

There are several Google groups hits on this, by the way:

http://groups.google.com/groups?hl=...ems&sa=N&tab=wg

Simon

"Simon" <spamplin@.hss.com> wrote in message
news:bdpe27$k71$1@.hercules.btinternet.com...
> I have an environment that has 18 separate SQL server machines. When
> connected to the LAN I can connect to each and expand the databases tab no
> problem. When I try the same over a VPN I find that three of my servers
will
> connect but on expanding the databases I get a long pause then 'No items'.
> I've checked around and found some references to the same problem (SQL
> server mag site) but no resolutions. I'm running XP pro and my SQL is sp3.
> The three servers in question have only TCP and named pipes in the net
lib,
> they are all on Win2K one is on sp3, one on sp2 and one is 'out of the
box'.
> The VPN has ports 1433 and 1434 udp and tcp allowed.
> Does anyone have any suggestions as to what is causing this, its got to be
> something simple but I've been looking at this so long now I cant see the
> wood for the trees.
> Help appreciated.

Enterprise Manager Databases folder shows "No items"

We're running a Win2k Adv Server MS Cluster using two servers with SQL
instances called SQL1 and SQL2 running on them. For some reason, we can't
manage SQL2 via Enterprise Manager recently since the databases folder is now
empty. The databases are there and I can see them via Query Analyzer, but I
just can't manage them via EM. "Management" folder is also empty, but if I
refresh (over and over) I sometimes can access the "SQL Server Agent" items.
Only changes that were made to the servers was to install some security
patches, but none that related to SQL server. Other SQL servers with the
same patches work fine. SQL1 instance can be managed with EM just fine.
I suspect something is wrong with SQL2 instance, but don't know what. It's
not related to the servers since I can move the instance between cluster
nodes and EM still cannot see databases on SQL2. SQL1 moves between nodes
fine and it's databases can be seen via EM.
Not sure if this is a clue, but when I right click on "Databases" and select
"New database", I get this error:
Microsoft SQL-DMO (ODBC SQLState: 22003)
Error 220: Arithmetic overflow error for that data type smallint, value=32826.
Arithmetic overflow error for data type smallint, value=32826.
The statement has been terminated.
I click OK, then I get the standard database properties dialog to create a
database...
Thanks in advance to any help or suggestions.
Regards,
-Daniel
My guess is a problem with DMO. Perhaps re-applying the SQL Server service pack will fix it?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Daniel" <Daniel@.tamc@.discussions.microsoft.com> wrote in message
news:0CB90BA3-08E0-4FA4-9177-6942CDC083BE@.microsoft.com...
> We're running a Win2k Adv Server MS Cluster using two servers with SQL
> instances called SQL1 and SQL2 running on them. For some reason, we can't
> manage SQL2 via Enterprise Manager recently since the databases folder is now
> empty. The databases are there and I can see them via Query Analyzer, but I
> just can't manage them via EM. "Management" folder is also empty, but if I
> refresh (over and over) I sometimes can access the "SQL Server Agent" items.
> Only changes that were made to the servers was to install some security
> patches, but none that related to SQL server. Other SQL servers with the
> same patches work fine. SQL1 instance can be managed with EM just fine.
> I suspect something is wrong with SQL2 instance, but don't know what. It's
> not related to the servers since I can move the instance between cluster
> nodes and EM still cannot see databases on SQL2. SQL1 moves between nodes
> fine and it's databases can be seen via EM.
> Not sure if this is a clue, but when I right click on "Databases" and select
> "New database", I get this error:
> Microsoft SQL-DMO (ODBC SQLState: 22003)
> Error 220: Arithmetic overflow error for that data type smallint, value=32826.
> Arithmetic overflow error for data type smallint, value=32826.
> The statement has been terminated.
> I click OK, then I get the standard database properties dialog to create a
> database...
> Thanks in advance to any help or suggestions.
> Regards,
> -Daniel
>
|||I am seeing these exact symptoms. Cluster, 1 server is showing "No items" in the database folder. Please advise.
************************************************** ********************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
|||I just ran across this problem today, and what I had to do was drop any databases that weren't in use (offline). I hope this helps anyone else googling for this problem!
-- hbz|||for googlers
no need to drop anything - it's enough to restart that databases whose status causing overflow errors
USE master
GO
DECLARE @.db_name varchar(500)
DECLARE dbn_cursor CURSOR FOR
SELECT [name] FROM sysdatabases;
OPEN dbn_cursor
FETCH NEXT FROM dbn_cursor
INTO @.db_name
WHILE @.@.FETCH_STATUS = 0
BEGIN
select * from sysdatabases where name = @.db_name
IF @.@.ERROR <> 0
BEGIN
declare @.off int
print 'Restarting database '+@.db_name
exec @.off = sp_dboption @.db_name, 'offline', 'TRUE'
IF @.off <> 0
print 'Taking offline failed! Need manual workaround!'
else
begin
declare @.on int
exec @.on = sp_dboption @.db_name, 'offline', 'FALSE'
IF @.on <> 0
print 'Bringing online failed! Need manual workaround!'
else
print 'Restart successfull!'
end
END
FETCH NEXT FROM dbn_cursor
INTO @.db_name
END
CLOSE dbn_cursor
DEALLOCATE dbn_cursor
GO|||I had exactly the same issue, 2x clustered SQL 2000 SP3 boxen, "no items" in
EM DB view, should be 50 or so. Failed over to other node, still no joy.
ran the script & it worked. (edited it to ID the database(s) having problems
first, found it was an old one, then ran full script to restart said DB)
Thank you Alan.
Cheers
Tim
|||maybe you need to re-attach them?
I'd use query analzer and check out master.dbo.sysdatabases and see if that
tells you anything
"Tim" <tim.cox@.noterinaceous.com> wrote in message
news:d8e3b0fef0a846079c0e261264455587@.ureader.com. ..
> I had exactly the same issue, 2x clustered SQL 2000 SP3 boxen, "no items"
in
> EM DB view, should be 50 or so. Failed over to other node, still no joy.
> ran the script & it worked. (edited it to ID the database(s) having
problems
> first, found it was an old one, then ran full script to restart said DB)
> Thank you Alan.
> Cheers
> Tim

Enterprise Manager Databases

When I try to look at my databases in Enterprise Manager it says (no items)
under the databases heading. I have tried re-registering the server and I
have tried it on several systems including from the server. I am connecting
with administrative access to the server.
The databases seem fine, I can see them all through query analyser and
through a web interface.
Anyone out there know how to get my database list back?
Thanksdo you see any kind of error in the errorlog such as exceptions? If so,
did you remove guest account, there might be a problem.
http://support.microsoft.com/default.aspx?scid=kb;en-us;315523
Yih-Yoon Lee
My blog http://www.mssql-tools.com/blog
E-mail: yihyoon.online@.gmail.com
/* remove .online to send me e-mail */
Dirk Marshall wrote:
> When I try to look at my databases in Enterprise Manager it says (no items)
> under the databases heading. I have tried re-registering the server and I
> have tried it on several systems including from the server. I am connecting
> with administrative access to the server.
> The databases seem fine, I can see them all through query analyser and
> through a web interface.
> Anyone out there know how to get my database list back?
> Thanks|||I have a similar issue. I'm not getting any errors...the databases, jobs
are not showing up either on my Enterpise Manager on my laptop or on the
server itself. However, I can see them in Query Analyizer, BUT when I try to
edit a stored proc, I get some wierd error:
[Microsoft][ODBC SQL Server Driver][SQL Server]Arithmetic overflow error for
data type smallint, value = 32867.
[Microsoft][ODBC SQL Server Driver][SQL Server]Arithmetic overflow error for
data type smallint, value = 32867.
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
terminated.
Bummer...
"Yih-Yoon Lee" wrote:
> do you see any kind of error in the errorlog such as exceptions? If so,
> did you remove guest account, there might be a problem.
> http://support.microsoft.com/default.aspx?scid=kb;en-us;315523
> Yih-Yoon Lee
> My blog http://www.mssql-tools.com/blog
> E-mail: yihyoon.online@.gmail.com
> /* remove .online to send me e-mail */
> Dirk Marshall wrote:
> > When I try to look at my databases in Enterprise Manager it says (no items)
> > under the databases heading. I have tried re-registering the server and I
> > have tried it on several systems including from the server. I am connecting
> > with administrative access to the server.
> >
> > The databases seem fine, I can see them all through query analyser and
> > through a web interface.
> >
> > Anyone out there know how to get my database list back?
> >
> > Thanks
>|||I get the same thing as well. Only change I made recently was the install
some recent MS security patches, but none that related to SQL server. Have
you found out anything new on this?
"TheMuffinMan" wrote:
> I have a similar issue. I'm not getting any errors...the databases, jobs
> are not showing up either on my Enterpise Manager on my laptop or on the
> server itself. However, I can see them in Query Analyizer, BUT when I try to
> edit a stored proc, I get some wierd error:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Arithmetic overflow error for
> data type smallint, value = 32867.
> [Microsoft][ODBC SQL Server Driver][SQL Server]Arithmetic overflow error for
> data type smallint, value = 32867.
> [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
> terminated.
> Bummer...
> "Yih-Yoon Lee" wrote:
> > do you see any kind of error in the errorlog such as exceptions? If so,
> > did you remove guest account, there might be a problem.
> >
> > http://support.microsoft.com/default.aspx?scid=kb;en-us;315523
> >
> > Yih-Yoon Lee
> > My blog http://www.mssql-tools.com/blog
> > E-mail: yihyoon.online@.gmail.com
> > /* remove .online to send me e-mail */
> >
> > Dirk Marshall wrote:
> > > When I try to look at my databases in Enterprise Manager it says (no items)
> > > under the databases heading. I have tried re-registering the server and I
> > > have tried it on several systems including from the server. I am connecting
> > > with administrative access to the server.
> > >
> > > The databases seem fine, I can see them all through query analyser and
> > > through a web interface.
> > >
> > > Anyone out there know how to get my database list back?
> > >
> > > Thanks
> >|||I am also getting this same exact thing. Please tell me if you have found
any resolution to this. I looked at the KB article mentioned about and it
does not look like the GUEST or DBO accounts have been removed from any of
the databases. I am at a loss. thanks for any help...
"Daniel@.tamc" wrote:
> I get the same thing as well. Only change I made recently was the install
> some recent MS security patches, but none that related to SQL server. Have
> you found out anything new on this?
> "TheMuffinMan" wrote:
> > I have a similar issue. I'm not getting any errors...the databases, jobs
> > are not showing up either on my Enterpise Manager on my laptop or on the
> > server itself. However, I can see them in Query Analyizer, BUT when I try to
> > edit a stored proc, I get some wierd error:
> > [Microsoft][ODBC SQL Server Driver][SQL Server]Arithmetic overflow error for
> > data type smallint, value = 32867.
> > [Microsoft][ODBC SQL Server Driver][SQL Server]Arithmetic overflow error for
> > data type smallint, value = 32867.
> > [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
> > terminated.
> >
> > Bummer...
> >
> > "Yih-Yoon Lee" wrote:
> >
> > > do you see any kind of error in the errorlog such as exceptions? If so,
> > > did you remove guest account, there might be a problem.
> > >
> > > http://support.microsoft.com/default.aspx?scid=kb;en-us;315523
> > >
> > > Yih-Yoon Lee
> > > My blog http://www.mssql-tools.com/blog
> > > E-mail: yihyoon.online@.gmail.com
> > > /* remove .online to send me e-mail */
> > >
> > > Dirk Marshall wrote:
> > > > When I try to look at my databases in Enterprise Manager it says (no items)
> > > > under the databases heading. I have tried re-registering the server and I
> > > > have tried it on several systems including from the server. I am connecting
> > > > with administrative access to the server.
> > > >
> > > > The databases seem fine, I can see them all through query analyser and
> > > > through a web interface.
> > > >
> > > > Anyone out there know how to get my database list back?
> > > >
> > > > Thanks
> > >|||This the response I got from TheMuffinMan:
Yes, Daniel...what happens is you have an offline database and it somehow
got the ID changed on it. You can make it "Online", or remove it via
query...or run an update on the sysdatabases table. I forgot the exact
command, so let me try to find it. Until then, try to run with the above and
let me know.
I used Query Analyzer to delete the offline databases, then everything came
back.
Hope that helps,
-Daniel
"KZeeh" wrote:
> I am also getting this same exact thing. Please tell me if you have found
> any resolution to this. I looked at the KB article mentioned about and it
> does not look like the GUEST or DBO accounts have been removed from any of
> the databases. I am at a loss. thanks for any help...
> "Daniel@.tamc" wrote:
> > I get the same thing as well. Only change I made recently was the install
> > some recent MS security patches, but none that related to SQL server. Have
> > you found out anything new on this?
> >
> > "TheMuffinMan" wrote:
> >
> > > I have a similar issue. I'm not getting any errors...the databases, jobs
> > > are not showing up either on my Enterpise Manager on my laptop or on the
> > > server itself. However, I can see them in Query Analyizer, BUT when I try to
> > > edit a stored proc, I get some wierd error:
> > > [Microsoft][ODBC SQL Server Driver][SQL Server]Arithmetic overflow error for
> > > data type smallint, value = 32867.
> > > [Microsoft][ODBC SQL Server Driver][SQL Server]Arithmetic overflow error for
> > > data type smallint, value = 32867.
> > > [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
> > > terminated.
> > >
> > > Bummer...
> > >
> > > "Yih-Yoon Lee" wrote:
> > >
> > > > do you see any kind of error in the errorlog such as exceptions? If so,
> > > > did you remove guest account, there might be a problem.
> > > >
> > > > http://support.microsoft.com/default.aspx?scid=kb;en-us;315523
> > > >
> > > > Yih-Yoon Lee
> > > > My blog http://www.mssql-tools.com/blog
> > > > E-mail: yihyoon.online@.gmail.com
> > > > /* remove .online to send me e-mail */
> > > >
> > > > Dirk Marshall wrote:
> > > > > When I try to look at my databases in Enterprise Manager it says (no items)
> > > > > under the databases heading. I have tried re-registering the server and I
> > > > > have tried it on several systems including from the server. I am connecting
> > > > > with administrative access to the server.
> > > > >
> > > > > The databases seem fine, I can see them all through query analyser and
> > > > > through a web interface.
> > > > >
> > > > > Anyone out there know how to get my database list back?
> > > > >
> > > > > Thanks
> > > >|||OK, but how do I determine if a database is offline? I don't know where to
see that. Thanks.
"Daniel@.tamc" wrote:
> This the response I got from TheMuffinMan:
> Yes, Daniel...what happens is you have an offline database and it somehow
> got the ID changed on it. You can make it "Online", or remove it via
> query...or run an update on the sysdatabases table. I forgot the exact
> command, so let me try to find it. Until then, try to run with the above and
> let me know.
> I used Query Analyzer to delete the offline databases, then everything came
> back.
> Hope that helps,
> -Daniel
> "KZeeh" wrote:
> > I am also getting this same exact thing. Please tell me if you have found
> > any resolution to this. I looked at the KB article mentioned about and it
> > does not look like the GUEST or DBO accounts have been removed from any of
> > the databases. I am at a loss. thanks for any help...
> >
> > "Daniel@.tamc" wrote:
> >
> > > I get the same thing as well. Only change I made recently was the install
> > > some recent MS security patches, but none that related to SQL server. Have
> > > you found out anything new on this?
> > >
> > > "TheMuffinMan" wrote:
> > >
> > > > I have a similar issue. I'm not getting any errors...the databases, jobs
> > > > are not showing up either on my Enterpise Manager on my laptop or on the
> > > > server itself. However, I can see them in Query Analyizer, BUT when I try to
> > > > edit a stored proc, I get some wierd error:
> > > > [Microsoft][ODBC SQL Server Driver][SQL Server]Arithmetic overflow error for
> > > > data type smallint, value = 32867.
> > > > [Microsoft][ODBC SQL Server Driver][SQL Server]Arithmetic overflow error for
> > > > data type smallint, value = 32867.
> > > > [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
> > > > terminated.
> > > >
> > > > Bummer...
> > > >
> > > > "Yih-Yoon Lee" wrote:
> > > >
> > > > > do you see any kind of error in the errorlog such as exceptions? If so,
> > > > > did you remove guest account, there might be a problem.
> > > > >
> > > > > http://support.microsoft.com/default.aspx?scid=kb;en-us;315523
> > > > >
> > > > > Yih-Yoon Lee
> > > > > My blog http://www.mssql-tools.com/blog
> > > > > E-mail: yihyoon.online@.gmail.com
> > > > > /* remove .online to send me e-mail */
> > > > >
> > > > > Dirk Marshall wrote:
> > > > > > When I try to look at my databases in Enterprise Manager it says (no items)
> > > > > > under the databases heading. I have tried re-registering the server and I
> > > > > > have tried it on several systems including from the server. I am connecting
> > > > > > with administrative access to the server.
> > > > > >
> > > > > > The databases seem fine, I can see them all through query analyser and
> > > > > > through a web interface.
> > > > > >
> > > > > > Anyone out there know how to get my database list back?
> > > > > >
> > > > > > Thanks
> > > > >|||I used query analyzer to query MASTER database, sysdatabases table.
See this for more info:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sys-d_5xrn.asp
Status field bits=512 indicates offline.
"KZeeh" wrote:
> OK, but how do I determine if a database is offline? I don't know where to
> see that. Thanks.
> "Daniel@.tamc" wrote:
> > This the response I got from TheMuffinMan:
> > Yes, Daniel...what happens is you have an offline database and it somehow
> > got the ID changed on it. You can make it "Online", or remove it via
> > query...or run an update on the sysdatabases table. I forgot the exact
> > command, so let me try to find it. Until then, try to run with the above and
> > let me know.
> >
> > I used Query Analyzer to delete the offline databases, then everything came
> > back.
> > Hope that helps,
> > -Daniel
> >
> > "KZeeh" wrote:
> >
> > > I am also getting this same exact thing. Please tell me if you have found
> > > any resolution to this. I looked at the KB article mentioned about and it
> > > does not look like the GUEST or DBO accounts have been removed from any of
> > > the databases. I am at a loss. thanks for any help...
> > >
> > > "Daniel@.tamc" wrote:
> > >
> > > > I get the same thing as well. Only change I made recently was the install
> > > > some recent MS security patches, but none that related to SQL server. Have
> > > > you found out anything new on this?
> > > >
> > > > "TheMuffinMan" wrote:
> > > >
> > > > > I have a similar issue. I'm not getting any errors...the databases, jobs
> > > > > are not showing up either on my Enterpise Manager on my laptop or on the
> > > > > server itself. However, I can see them in Query Analyizer, BUT when I try to
> > > > > edit a stored proc, I get some wierd error:
> > > > > [Microsoft][ODBC SQL Server Driver][SQL Server]Arithmetic overflow error for
> > > > > data type smallint, value = 32867.
> > > > > [Microsoft][ODBC SQL Server Driver][SQL Server]Arithmetic overflow error for
> > > > > data type smallint, value = 32867.
> > > > > [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
> > > > > terminated.
> > > > >
> > > > > Bummer...
> > > > >
> > > > > "Yih-Yoon Lee" wrote:
> > > > >
> > > > > > do you see any kind of error in the errorlog such as exceptions? If so,
> > > > > > did you remove guest account, there might be a problem.
> > > > > >
> > > > > > http://support.microsoft.com/default.aspx?scid=kb;en-us;315523
> > > > > >
> > > > > > Yih-Yoon Lee
> > > > > > My blog http://www.mssql-tools.com/blog
> > > > > > E-mail: yihyoon.online@.gmail.com
> > > > > > /* remove .online to send me e-mail */
> > > > > >
> > > > > > Dirk Marshall wrote:
> > > > > > > When I try to look at my databases in Enterprise Manager it says (no items)
> > > > > > > under the databases heading. I have tried re-registering the server and I
> > > > > > > have tried it on several systems including from the server. I am connecting
> > > > > > > with administrative access to the server.
> > > > > > >
> > > > > > > The databases seem fine, I can see them all through query analyser and
> > > > > > > through a web interface.
> > > > > > >
> > > > > > > Anyone out there know how to get my database list back?
> > > > > > >
> > > > > > > Thanks
> > > > > >|||Of course, none of mine have a status of 512, it is a combination of bits.
I've got a lot of 1552's, which I think is 1024(readonly) + 512(offline) +
16(torn page detection). Then I also have a lot of 28s too. I am just not
sure what to "reset" these numbers to. Is there a more comprehensive list
anywhere about resetting the status? Thanks so much for helping me out with
this. I couldn't find any info anywhere on this...
"Daniel@.tamc" wrote:
> I used query analyzer to query MASTER database, sysdatabases table.
> See this for more info:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sys-d_5xrn.asp
> Status field bits=512 indicates offline.
> "KZeeh" wrote:
> > OK, but how do I determine if a database is offline? I don't know where to
> > see that. Thanks.
> >
> > "Daniel@.tamc" wrote:
> >
> > > This the response I got from TheMuffinMan:
> > > Yes, Daniel...what happens is you have an offline database and it somehow
> > > got the ID changed on it. You can make it "Online", or remove it via
> > > query...or run an update on the sysdatabases table. I forgot the exact
> > > command, so let me try to find it. Until then, try to run with the above and
> > > let me know.
> > >
> > > I used Query Analyzer to delete the offline databases, then everything came
> > > back.
> > > Hope that helps,
> > > -Daniel
> > >
> > > "KZeeh" wrote:
> > >
> > > > I am also getting this same exact thing. Please tell me if you have found
> > > > any resolution to this. I looked at the KB article mentioned about and it
> > > > does not look like the GUEST or DBO accounts have been removed from any of
> > > > the databases. I am at a loss. thanks for any help...
> > > >
> > > > "Daniel@.tamc" wrote:
> > > >
> > > > > I get the same thing as well. Only change I made recently was the install
> > > > > some recent MS security patches, but none that related to SQL server. Have
> > > > > you found out anything new on this?
> > > > >
> > > > > "TheMuffinMan" wrote:
> > > > >
> > > > > > I have a similar issue. I'm not getting any errors...the databases, jobs
> > > > > > are not showing up either on my Enterpise Manager on my laptop or on the
> > > > > > server itself. However, I can see them in Query Analyizer, BUT when I try to
> > > > > > edit a stored proc, I get some wierd error:
> > > > > > [Microsoft][ODBC SQL Server Driver][SQL Server]Arithmetic overflow error for
> > > > > > data type smallint, value = 32867.
> > > > > > [Microsoft][ODBC SQL Server Driver][SQL Server]Arithmetic overflow error for
> > > > > > data type smallint, value = 32867.
> > > > > > [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
> > > > > > terminated.
> > > > > >
> > > > > > Bummer...
> > > > > >
> > > > > > "Yih-Yoon Lee" wrote:
> > > > > >
> > > > > > > do you see any kind of error in the errorlog such as exceptions? If so,
> > > > > > > did you remove guest account, there might be a problem.
> > > > > > >
> > > > > > > http://support.microsoft.com/default.aspx?scid=kb;en-us;315523
> > > > > > >
> > > > > > > Yih-Yoon Lee
> > > > > > > My blog http://www.mssql-tools.com/blog
> > > > > > > E-mail: yihyoon.online@.gmail.com
> > > > > > > /* remove .online to send me e-mail */
> > > > > > >
> > > > > > > Dirk Marshall wrote:
> > > > > > > > When I try to look at my databases in Enterprise Manager it says (no items)
> > > > > > > > under the databases heading. I have tried re-registering the server and I
> > > > > > > > have tried it on several systems including from the server. I am connecting
> > > > > > > > with administrative access to the server.
> > > > > > > >
> > > > > > > > The databases seem fine, I can see them all through query analyser and
> > > > > > > > through a web interface.
> > > > > > > >
> > > > > > > > Anyone out there know how to get my database list back?
> > > > > > > >
> > > > > > > > Thanks
> > > > > > >|||SELECT name, DATABASEPROPERTY(name, 'IsOffLine') AS OffLine
FROM sysdatabases
1 means offline.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"KZeeh" <KZeeh@.discussions.microsoft.com> wrote in message
news:727DEAE3-1F12-4A71-A12B-BA38908CA919@.microsoft.com...
> OK, but how do I determine if a database is offline? I don't know where to
> see that. Thanks.
> "Daniel@.tamc" wrote:
>> This the response I got from TheMuffinMan:
>> Yes, Daniel...what happens is you have an offline database and it somehow
>> got the ID changed on it. You can make it "Online", or remove it via
>> query...or run an update on the sysdatabases table. I forgot the exact
>> command, so let me try to find it. Until then, try to run with the above and
>> let me know.
>> I used Query Analyzer to delete the offline databases, then everything came
>> back.
>> Hope that helps,
>> -Daniel
>> "KZeeh" wrote:
>> > I am also getting this same exact thing. Please tell me if you have found
>> > any resolution to this. I looked at the KB article mentioned about and it
>> > does not look like the GUEST or DBO accounts have been removed from any of
>> > the databases. I am at a loss. thanks for any help...
>> >
>> > "Daniel@.tamc" wrote:
>> >
>> > > I get the same thing as well. Only change I made recently was the install
>> > > some recent MS security patches, but none that related to SQL server. Have
>> > > you found out anything new on this?
>> > >
>> > > "TheMuffinMan" wrote:
>> > >
>> > > > I have a similar issue. I'm not getting any errors...the databases, jobs
>> > > > are not showing up either on my Enterpise Manager on my laptop or on the
>> > > > server itself. However, I can see them in Query Analyizer, BUT when I try to
>> > > > edit a stored proc, I get some wierd error:
>> > > > [Microsoft][ODBC SQL Server Driver][SQL Server]Arithmetic overflow error for
>> > > > data type smallint, value = 32867.
>> > > > [Microsoft][ODBC SQL Server Driver][SQL Server]Arithmetic overflow error for
>> > > > data type smallint, value = 32867.
>> > > > [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
>> > > > terminated.
>> > > >
>> > > > Bummer...
>> > > >
>> > > > "Yih-Yoon Lee" wrote:
>> > > >
>> > > > > do you see any kind of error in the errorlog such as exceptions? If so,
>> > > > > did you remove guest account, there might be a problem.
>> > > > >
>> > > > > http://support.microsoft.com/default.aspx?scid=kb;en-us;315523
>> > > > >
>> > > > > Yih-Yoon Lee
>> > > > > My blog http://www.mssql-tools.com/blog
>> > > > > E-mail: yihyoon.online@.gmail.com
>> > > > > /* remove .online to send me e-mail */
>> > > > >
>> > > > > Dirk Marshall wrote:
>> > > > > > When I try to look at my databases in Enterprise Manager it says (no items)
>> > > > > > under the databases heading. I have tried re-registering the server and I
>> > > > > > have tried it on several systems including from the server. I am connecting
>> > > > > > with administrative access to the server.
>> > > > > >
>> > > > > > The databases seem fine, I can see them all through query analyser and
>> > > > > > through a web interface.
>> > > > > >
>> > > > > > Anyone out there know how to get my database list back?
>> > > > > >
>> > > > > > Thanks
>> > > > >|||OK, great. I ran that query and see that some of them are status=1, which
means they are offline. Can I just update the table and put them back
online' I am not the DBA, but no one seemed to be troubleshooting the
problem, so I decided to look into it. What are the ramifications if I do
this? Thanks for any help. I just want to determine if this is truly the
problem.
"Tibor Karaszi" wrote:
> SELECT name, DATABASEPROPERTY(name, 'IsOffLine') AS OffLine
> FROM sysdatabases
> 1 means offline.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "KZeeh" <KZeeh@.discussions.microsoft.com> wrote in message
> news:727DEAE3-1F12-4A71-A12B-BA38908CA919@.microsoft.com...
> > OK, but how do I determine if a database is offline? I don't know where to
> > see that. Thanks.
> >
> > "Daniel@.tamc" wrote:
> >
> >> This the response I got from TheMuffinMan:
> >> Yes, Daniel...what happens is you have an offline database and it somehow
> >> got the ID changed on it. You can make it "Online", or remove it via
> >> query...or run an update on the sysdatabases table. I forgot the exact
> >> command, so let me try to find it. Until then, try to run with the above and
> >> let me know.
> >>
> >> I used Query Analyzer to delete the offline databases, then everything came
> >> back.
> >> Hope that helps,
> >> -Daniel
> >>
> >> "KZeeh" wrote:
> >>
> >> > I am also getting this same exact thing. Please tell me if you have found
> >> > any resolution to this. I looked at the KB article mentioned about and it
> >> > does not look like the GUEST or DBO accounts have been removed from any of
> >> > the databases. I am at a loss. thanks for any help...
> >> >
> >> > "Daniel@.tamc" wrote:
> >> >
> >> > > I get the same thing as well. Only change I made recently was the install
> >> > > some recent MS security patches, but none that related to SQL server. Have
> >> > > you found out anything new on this?
> >> > >
> >> > > "TheMuffinMan" wrote:
> >> > >
> >> > > > I have a similar issue. I'm not getting any errors...the databases, jobs
> >> > > > are not showing up either on my Enterpise Manager on my laptop or on the
> >> > > > server itself. However, I can see them in Query Analyizer, BUT when I try to
> >> > > > edit a stored proc, I get some wierd error:
> >> > > > [Microsoft][ODBC SQL Server Driver][SQL Server]Arithmetic overflow error for
> >> > > > data type smallint, value = 32867.
> >> > > > [Microsoft][ODBC SQL Server Driver][SQL Server]Arithmetic overflow error for
> >> > > > data type smallint, value = 32867.
> >> > > > [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
> >> > > > terminated.
> >> > > >
> >> > > > Bummer...
> >> > > >
> >> > > > "Yih-Yoon Lee" wrote:
> >> > > >
> >> > > > > do you see any kind of error in the errorlog such as exceptions? If so,
> >> > > > > did you remove guest account, there might be a problem.
> >> > > > >
> >> > > > > http://support.microsoft.com/default.aspx?scid=kb;en-us;315523
> >> > > > >
> >> > > > > Yih-Yoon Lee
> >> > > > > My blog http://www.mssql-tools.com/blog
> >> > > > > E-mail: yihyoon.online@.gmail.com
> >> > > > > /* remove .online to send me e-mail */
> >> > > > >
> >> > > > > Dirk Marshall wrote:
> >> > > > > > When I try to look at my databases in Enterprise Manager it says (no items)
> >> > > > > > under the databases heading. I have tried re-registering the server and I
> >> > > > > > have tried it on several systems including from the server. I am connecting
> >> > > > > > with administrative access to the server.
> >> > > > > >
> >> > > > > > The databases seem fine, I can see them all through query analyser and
> >> > > > > > through a web interface.
> >> > > > > >
> >> > > > > > Anyone out there know how to get my database list back?
> >> > > > > >
> >> > > > > > Thanks
> >> > > > >
>
>|||> OK, great. I ran that query and see that some of them are status=1, which
> means they are offline. Can I just update the table and put them back
> online'
No, for each database which is offline, you need to execute a statement to bring it online.
ALTER DATABASE dbname SET ONLINE
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"KZeeh" <KZeeh@.discussions.microsoft.com> wrote in message
news:165F8B69-7E7D-4135-B265-77F5A4233E78@.microsoft.com...
> OK, great. I ran that query and see that some of them are status=1, which
> means they are offline. Can I just update the table and put them back
> online' I am not the DBA, but no one seemed to be troubleshooting the
> problem, so I decided to look into it. What are the ramifications if I do
> this? Thanks for any help. I just want to determine if this is truly the
> problem.
> "Tibor Karaszi" wrote:
>> SELECT name, DATABASEPROPERTY(name, 'IsOffLine') AS OffLine
>> FROM sysdatabases
>> 1 means offline.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> http://www.sqlug.se/
>>
>> "KZeeh" <KZeeh@.discussions.microsoft.com> wrote in message
>> news:727DEAE3-1F12-4A71-A12B-BA38908CA919@.microsoft.com...
>> > OK, but how do I determine if a database is offline? I don't know where to
>> > see that. Thanks.
>> >
>> > "Daniel@.tamc" wrote:
>> >
>> >> This the response I got from TheMuffinMan:
>> >> Yes, Daniel...what happens is you have an offline database and it somehow
>> >> got the ID changed on it. You can make it "Online", or remove it via
>> >> query...or run an update on the sysdatabases table. I forgot the exact
>> >> command, so let me try to find it. Until then, try to run with the above and
>> >> let me know.
>> >>
>> >> I used Query Analyzer to delete the offline databases, then everything came
>> >> back.
>> >> Hope that helps,
>> >> -Daniel
>> >>
>> >> "KZeeh" wrote:
>> >>
>> >> > I am also getting this same exact thing. Please tell me if you have found
>> >> > any resolution to this. I looked at the KB article mentioned about and it
>> >> > does not look like the GUEST or DBO accounts have been removed from any of
>> >> > the databases. I am at a loss. thanks for any help...
>> >> >
>> >> > "Daniel@.tamc" wrote:
>> >> >
>> >> > > I get the same thing as well. Only change I made recently was the install
>> >> > > some recent MS security patches, but none that related to SQL server. Have
>> >> > > you found out anything new on this?
>> >> > >
>> >> > > "TheMuffinMan" wrote:
>> >> > >
>> >> > > > I have a similar issue. I'm not getting any errors...the databases, jobs
>> >> > > > are not showing up either on my Enterpise Manager on my laptop or on the
>> >> > > > server itself. However, I can see them in Query Analyizer, BUT when I try to
>> >> > > > edit a stored proc, I get some wierd error:
>> >> > > > [Microsoft][ODBC SQL Server Driver][SQL Server]Arithmetic overflow error for
>> >> > > > data type smallint, value = 32867.
>> >> > > > [Microsoft][ODBC SQL Server Driver][SQL Server]Arithmetic overflow error for
>> >> > > > data type smallint, value = 32867.
>> >> > > > [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
>> >> > > > terminated.
>> >> > > >
>> >> > > > Bummer...
>> >> > > >
>> >> > > > "Yih-Yoon Lee" wrote:
>> >> > > >
>> >> > > > > do you see any kind of error in the errorlog such as exceptions? If so,
>> >> > > > > did you remove guest account, there might be a problem.
>> >> > > > >
>> >> > > > > http://support.microsoft.com/default.aspx?scid=kb;en-us;315523
>> >> > > > >
>> >> > > > > Yih-Yoon Lee
>> >> > > > > My blog http://www.mssql-tools.com/blog
>> >> > > > > E-mail: yihyoon.online@.gmail.com
>> >> > > > > /* remove .online to send me e-mail */
>> >> > > > >
>> >> > > > > Dirk Marshall wrote:
>> >> > > > > > When I try to look at my databases in Enterprise Manager it says (no items)
>> >> > > > > > under the databases heading. I have tried re-registering the server and I
>> >> > > > > > have tried it on several systems including from the server. I am connecting
>> >> > > > > > with administrative access to the server.
>> >> > > > > >
>> >> > > > > > The databases seem fine, I can see them all through query analyser and
>> >> > > > > > through a web interface.
>> >> > > > > >
>> >> > > > > > Anyone out there know how to get my database list back?
>> >> > > > > >
>> >> > > > > > Thanks
>> >> > > > >
>>|||Press CTRL + refresh (like in IE)
It works for me. Hope it helps. :)
Herman
"Dirk Marshall" wrote:
> When I try to look at my databases in Enterprise Manager it says (no items)
> under the databases heading. I have tried re-registering the server and I
> have tried it on several systems including from the server. I am connecting
> with administrative access to the server.
> The databases seem fine, I can see them all through query analyser and
> through a web interface.
> Anyone out there know how to get my database list back?
> Thanks

Enterprise Manager Databases

When I try to look at my databases in Enterprise Manager it says (no items)
under the databases heading. I have tried re-registering the server and I
have tried it on several systems including from the server. I am connecting
with administrative access to the server.
The databases seem fine, I can see them all through query analyser and
through a web interface.
Anyone out there know how to get my database list back?
Thanks
do you see any kind of error in the errorlog such as exceptions? If so,
did you remove guest account, there might be a problem.
http://support.microsoft.com/default...b;en-us;315523
Yih-Yoon Lee
My blog http://www.mssql-tools.com/blog
E-mail: yihyoon.online@.gmail.com
/* remove .online to send me e-mail */
Dirk Marshall wrote:
> When I try to look at my databases in Enterprise Manager it says (no items)
> under the databases heading. I have tried re-registering the server and I
> have tried it on several systems including from the server. I am connecting
> with administrative access to the server.
> The databases seem fine, I can see them all through query analyser and
> through a web interface.
> Anyone out there know how to get my database list back?
> Thanks
|||I have a similar issue. I'm not getting any errors...the databases, jobs
are not showing up either on my Enterpise Manager on my laptop or on the
server itself. However, I can see them in Query Analyizer, BUT when I try to
edit a stored proc, I get some wierd error:
[Microsoft][ODBC SQL Server Driver][SQL Server]Arithmetic overflow error for
data type smallint, value = 32867.
[Microsoft][ODBC SQL Server Driver][SQL Server]Arithmetic overflow error for
data type smallint, value = 32867.
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
terminated.
Bummer...
"Yih-Yoon Lee" wrote:

> do you see any kind of error in the errorlog such as exceptions? If so,
> did you remove guest account, there might be a problem.
> http://support.microsoft.com/default...b;en-us;315523
> Yih-Yoon Lee
> My blog http://www.mssql-tools.com/blog
> E-mail: yihyoon.online@.gmail.com
> /* remove .online to send me e-mail */
> Dirk Marshall wrote:
>
|||I get the same thing as well. Only change I made recently was the install
some recent MS security patches, but none that related to SQL server. Have
you found out anything new on this?
"TheMuffinMan" wrote:
[vbcol=seagreen]
> I have a similar issue. I'm not getting any errors...the databases, jobs
> are not showing up either on my Enterpise Manager on my laptop or on the
> server itself. However, I can see them in Query Analyizer, BUT when I try to
> edit a stored proc, I get some wierd error:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Arithmetic overflow error for
> data type smallint, value = 32867.
> [Microsoft][ODBC SQL Server Driver][SQL Server]Arithmetic overflow error for
> data type smallint, value = 32867.
> [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
> terminated.
> Bummer...
> "Yih-Yoon Lee" wrote:
|||I am also getting this same exact thing. Please tell me if you have found
any resolution to this. I looked at the KB article mentioned about and it
does not look like the GUEST or DBO accounts have been removed from any of
the databases. I am at a loss. thanks for any help...
"Daniel@.tamc" wrote:
[vbcol=seagreen]
> I get the same thing as well. Only change I made recently was the install
> some recent MS security patches, but none that related to SQL server. Have
> you found out anything new on this?
> "TheMuffinMan" wrote:
|||This the response I got from TheMuffinMan:
Yes, Daniel...what happens is you have an offline database and it somehow
got the ID changed on it. You can make it "Online", or remove it via
query...or run an update on the sysdatabases table. I forgot the exact
command, so let me try to find it. Until then, try to run with the above and
let me know.
I used Query Analyzer to delete the offline databases, then everything came
back.
Hope that helps,
-Daniel
"KZeeh" wrote:
[vbcol=seagreen]
> I am also getting this same exact thing. Please tell me if you have found
> any resolution to this. I looked at the KB article mentioned about and it
> does not look like the GUEST or DBO accounts have been removed from any of
> the databases. I am at a loss. thanks for any help...
> "Daniel@.tamc" wrote:
|||OK, but how do I determine if a database is offline? I don't know where to
see that. Thanks.
"Daniel@.tamc" wrote:
[vbcol=seagreen]
> This the response I got from TheMuffinMan:
> Yes, Daniel...what happens is you have an offline database and it somehow
> got the ID changed on it. You can make it "Online", or remove it via
> query...or run an update on the sysdatabases table. I forgot the exact
> command, so let me try to find it. Until then, try to run with the above and
> let me know.
> I used Query Analyzer to delete the offline databases, then everything came
> back.
> Hope that helps,
> -Daniel
> "KZeeh" wrote:
|||I used query analyzer to query MASTER database, sysdatabases table.
See this for more info:
http://msdn.microsoft.com/library/de...sys-d_5xrn.asp
Status field bits=512 indicates offline.
"KZeeh" wrote:
[vbcol=seagreen]
> OK, but how do I determine if a database is offline? I don't know where to
> see that. Thanks.
> "Daniel@.tamc" wrote:
|||Of course, none of mine have a status of 512, it is a combination of bits.
I've got a lot of 1552's, which I think is 1024(readonly) + 512(offline) +
16(torn page detection). Then I also have a lot of 28s too. I am just not
sure what to "reset" these numbers to. Is there a more comprehensive list
anywhere about resetting the status? Thanks so much for helping me out with
this. I couldn't find any info anywhere on this...
"Daniel@.tamc" wrote:
[vbcol=seagreen]
> I used query analyzer to query MASTER database, sysdatabases table.
> See this for more info:
> http://msdn.microsoft.com/library/de...sys-d_5xrn.asp
> Status field bits=512 indicates offline.
> "KZeeh" wrote:
|||SELECT name, DATABASEPROPERTY(name, 'IsOffLine') AS OffLine
FROM sysdatabases
1 means offline.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"KZeeh" <KZeeh@.discussions.microsoft.com> wrote in message
news:727DEAE3-1F12-4A71-A12B-BA38908CA919@.microsoft.com...[vbcol=seagreen]
> OK, but how do I determine if a database is offline? I don't know where to
> see that. Thanks.
> "Daniel@.tamc" wrote:

Enterprise Manager Databases

When I try to look at my databases in Enterprise Manager it says (no items)
under the databases heading. I have tried re-registering the server and I
have tried it on several systems including from the server. I am connecting
with administrative access to the server.
The databases seem fine, I can see them all through query analyser and
through a web interface.
Anyone out there know how to get my database list back?
Thanksdo you see any kind of error in the errorlog such as exceptions? If so,
did you remove guest account, there might be a problem.
http://support.microsoft.com/defaul...kb;en-us;315523
Yih-Yoon Lee
My blog http://www.mssql-tools.com/blog
E-mail: yihyoon.online@.gmail.com
/* remove .online to send me e-mail */
Dirk Marshall wrote:
> When I try to look at my databases in Enterprise Manager it says (no items
)
> under the databases heading. I have tried re-registering the server and I
> have tried it on several systems including from the server. I am connecti
ng
> with administrative access to the server.
> The databases seem fine, I can see them all through query analyser and
> through a web interface.
> Anyone out there know how to get my database list back?
> Thanks|||I have a similar issue. I'm not getting any errors...the databases, jobs
are not showing up either on my Enterpise Manager on my laptop or on the
server itself. However, I can see them in Query Analyizer, BUT when I try t
o
edit a stored proc, I get some wierd error:
[Microsoft][ODBC SQL Server Driver][SQL Server]Arithmetic overfl
ow error for
data type smallint, value = 32867.
[Microsoft][ODBC SQL Server Driver][SQL Server]Arithmetic overfl
ow error for
data type smallint, value = 32867.
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has
been
terminated.
Bummer...
"Yih-Yoon Lee" wrote:

> do you see any kind of error in the errorlog such as exceptions? If so,
> did you remove guest account, there might be a problem.
> http://support.microsoft.com/defaul...kb;en-us;315523
> Yih-Yoon Lee
> My blog http://www.mssql-tools.com/blog
> E-mail: yihyoon.online@.gmail.com
> /* remove .online to send me e-mail */
> Dirk Marshall wrote:
>|||I get the same thing as well. Only change I made recently was the install
some recent MS security patches, but none that related to SQL server. Have
you found out anything new on this?
"TheMuffinMan" wrote:
[vbcol=seagreen]
> I have a similar issue. I'm not getting any errors...the databases, jobs
> are not showing up either on my Enterpise Manager on my laptop or on the
> server itself. However, I can see them in Query Analyizer, BUT when I try
to
> edit a stored proc, I get some wierd error:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Arithmetic over
flow error for
> data type smallint, value = 32867.
> [Microsoft][ODBC SQL Server Driver][SQL Server]Arithmetic over
flow error for
> data type smallint, value = 32867.
> [Microsoft][ODBC SQL Server Driver][SQL Server]The statement h
as been
> terminated.
> Bummer...
> "Yih-Yoon Lee" wrote:
>|||I am also getting this same exact thing. Please tell me if you have found
any resolution to this. I looked at the KB article mentioned about and it
does not look like the GUEST or DBO accounts have been removed from any of
the databases. I am at a loss. thanks for any help...
"Daniel@.tamc" wrote:
[vbcol=seagreen]
> I get the same thing as well. Only change I made recently was the install
> some recent MS security patches, but none that related to SQL server. Hav
e
> you found out anything new on this?
> "TheMuffinMan" wrote:
>|||This the response I got from TheMuffinMan:
Yes, Daniel...what happens is you have an offline database and it somehow
got the ID changed on it. You can make it "Online", or remove it via
query...or run an update on the sysdatabases table. I forgot the exact
command, so let me try to find it. Until then, try to run with the above an
d
let me know.
I used Query Analyzer to delete the offline databases, then everything came
back.
Hope that helps,
-Daniel
"KZeeh" wrote:
[vbcol=seagreen]
> I am also getting this same exact thing. Please tell me if you have found
> any resolution to this. I looked at the KB article mentioned about and it
> does not look like the GUEST or DBO accounts have been removed from any of
> the databases. I am at a loss. thanks for any help...
> "Daniel@.tamc" wrote:
>|||OK, but how do I determine if a database is offline? I don't know where to
see that. Thanks.
"Daniel@.tamc" wrote:
[vbcol=seagreen]
> This the response I got from TheMuffinMan:
> Yes, Daniel...what happens is you have an offline database and it somehow
> got the ID changed on it. You can make it "Online", or remove it via
> query...or run an update on the sysdatabases table. I forgot the exact
> command, so let me try to find it. Until then, try to run with the above
and
> let me know.
> I used Query Analyzer to delete the offline databases, then everything cam
e
> back.
> Hope that helps,
> -Daniel
> "KZeeh" wrote:
>|||I used query analyzer to query MASTER database, sysdatabases table.
See this for more info:
http://msdn.microsoft.com/library/d...br />
5xrn.asp
Status field bits=512 indicates offline.
"KZeeh" wrote:
[vbcol=seagreen]
> OK, but how do I determine if a database is offline? I don't know where t
o
> see that. Thanks.
> "Daniel@.tamc" wrote:
>|||Of course, none of mine have a status of 512, it is a combination of bits.
I've got a lot of 1552's, which I think is 1024(readonly) + 512(offline) +
16(torn page detection). Then I also have a lot of 28s too. I am just not
sure what to "reset" these numbers to. Is there a more comprehensive list
anywhere about resetting the status? Thanks so much for helping me out with
this. I couldn't find any info anywhere on this...
"Daniel@.tamc" wrote:
[vbcol=seagreen]
> I used query analyzer to query MASTER database, sysdatabases table.
> See this for more info:
> http://msdn.microsoft.com/library/d... />
d_5xrn.asp
> Status field bits=512 indicates offline.
> "KZeeh" wrote:
>|||SELECT name, DATABASEPROPERTY(name, 'IsOffLine') AS OffLine
FROM sysdatabases
1 means offline.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"KZeeh" <KZeeh@.discussions.microsoft.com> wrote in message
news:727DEAE3-1F12-4A71-A12B-BA38908CA919@.microsoft.com...[vbcol=seagreen]
> OK, but how do I determine if a database is offline? I don't know where t
o
> see that. Thanks.
> "Daniel@.tamc" wrote:
>

Enterprise manager - databases - "No items"

Hi all,
I'm using Enterprise Manager to connect to several remote SQL Server
Databases (in different locations).
It works fine for all servers except the one that shows "No items" when I
click on "databases". After "refresh" it successfully receives list of
databases but after a few hours...
When I right click on databases and select "Export data" then I can select
my database from the list but when I specify "Copy objects and data between
SQL server databases" I receive somthing like :
"User 'aaa' is not valid user in database
'some_database_but_different_then_I_have_selected' ".
When I try to create new DTS Package that should "copy SQL Server objects
task" I receive similar error.
I can work with Query Analyzer but don't know how to make backup copy of the
database to my local database since I have only db_owner rights (I'm not
member of sysadmin server role).
I have send the following article
http://support.microsoft.com/default...b;en-us;315523 to my
database povider but he says they have everything set properly...
Can anybody help me, please?
Thank You
George
Hi George,
From your description, I understand that, as a remote client, your SQL
Server Enterprise Manager would occasionally not display the your database.
When refreshing it, the database would display but in a long time. Have I
fully understood you? If there is anything I misunderstood, please feel
free to let me know.
Based on my understanding, the Server has a significant number of databases
and a few of them were marked as autoclose. This meant when user connected
using SQL Server Enterprise Manager, the database would need to be started
before the queries to enumerate tables and data could be run. This delay
was causing the queries to fail and resulted in the databases to not be
displayed. Refreshing allowed the databases to be started while the query
was resubmitted.
To resolve the issue, you should contact your database provider to remove
the autoclose settings form all of your databases. He could do in this way
EXEC sp_MSforeachdb @.command1="sp_dboption '?','autoclose',false"
Autoclose option is not recommended for production systems since it can be
a significant performance impact.
It the above doesn't resovle the issue, You shoud have a look at the size
of tempdb. You could try using one of the methods documented in the
following article to shrink tempdb back to it's original size
307487 HOW TO: Shrink the Tempdb Database in SQL Server
http://support.microsoft.com/?id=307487
Would you please tell me that how large is tempdb currently, and how large
was it when SQL Server Enterprise Manager experienced the failure to
enumerate the databases? Was the autogrowth option turned off for tempdb
previously?
If the above two doesn't resolve your issue, would you please run the
following queries in your Query Analyzer when the problem happens and show
me the results?
select name, DATABASEPROPERTY(name, N'IsDetached'),(case when
DATABASEPROPERTY(name, N'IsShutdown') is null then -1 else
DATABASEPROPERTY(name, N'IsShutdown') end), DATABASEPROPERTY(name,
N'IsSuspect'),
DATABASEPROPERTY(name, N'IsOffline'), DATABASEPROPERTY(name, N'IsInLoad'),
(case when DATABASEPROPERTY(name, N'IsInRecovery') is null then -1 else
DATABASEPROPERTY(name, N'IsInRecovery') end), (case when
DATABASEPROPERTY(name,
N'IsNotRecovered') is null then -1 else
DATABASEPROPERTY(name, N'IsNotRecovered') end), DATABASEPROPERTY(name,
N'IsEmergencyMode'),
DATABASEPROPERTY(name, N'IsInStandBy'), has_dbaccess(name), status,
category,
status2
from master.dbo.sysdatabases
Moreover, detailed information about autoclose option of database could be
found at
Avoiding the Autoclose and Autoshrink Options
http://www.microsoft.com/sql/techinf.../autoclose.asp
AutoClose Property
http://msdn.microsoft.com/library/de...us/sqldmo/dmor
ef_p_a_5cl5.asp
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Support
************************************************** *********
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks.
|||Hi George,
I wanted to post a quick note to see whether you have apply my suggestion
and your Enterprise manager works fine. If you would like additional
assistance or information regarding this particular issue. We appreciate
your patience and look forward to hearing from you!
Sincerely yours,
Michael Cheng
Microsoft Online Support
************************************************** *********
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks.
|||Hi Michael,
sorry for delay. I have send Your information to my database provider. He
has promissed that he checks it in few days. Now I have tried to connect to
my database and IT WORKS!!! I can see databases, I can work with my
database, I have succesfully run my DTS packages too and everything is
without delay.
Thank You very much for Your help!!!
Best regards
George
""Michael, Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message
news:z3vX9N2KEHA.3088@.cpmsftngxa10.phx.gbl...
> Hi George,
> I wanted to post a quick note to see whether you have apply my suggestion
> and your Enterprise manager works fine. If you would like additional
> assistance or information regarding this particular issue. We appreciate
> your patience and look forward to hearing from you!
> Sincerely yours,
> Michael Cheng
> Microsoft Online Support
> ************************************************** *********
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks.
>

Enterprise Manager

SQL 2000, Sp3
No items is displayed in the Enterprise Manager. All systems are
functioning properly, the EM is simply no longer useful. Any suggestions or
thoughts are appreciated.
Well, for starters, you can achieve most things (and better, as a bonus)
using query analyzer.
You could also try re-installing client tools (or installing client tools
for SQL Server 2005, if you have them available).
Might be helpful to see a screen shot of what "no items is displayed"
means... is it possible the user you are connecting with has changed
permissions (and you are trying to see a list of tables, for example)? Have
you tried re-registering the server, and/or connecting using windows
authentication with a local or domain administrator?
"Mark Castelli" <mcastelli98@.yahoo.com> wrote in message
news:%23QM1jUJyHHA.4264@.TK2MSFTNGP05.phx.gbl...
> SQL 2000, Sp3
> No items is displayed in the Enterprise Manager. All systems are
> functioning properly, the EM is simply no longer useful. Any suggestions
> or thoughts are appreciated.
>

Enterprise Manager

SQL 2000, Sp3
No items is displayed in the Enterprise Manager. All systems are
functioning properly, the EM is simply no longer useful. Any suggestions or
thoughts are appreciated.Well, for starters, you can achieve most things (and better, as a bonus)
using query analyzer.
You could also try re-installing client tools (or installing client tools
for SQL Server 2005, if you have them available).
Might be helpful to see a screen shot of what "no items is displayed"
means... is it possible the user you are connecting with has changed
permissions (and you are trying to see a list of tables, for example)? Have
you tried re-registering the server, and/or connecting using windows
authentication with a local or domain administrator?
"Mark Castelli" <mcastelli98@.yahoo.com> wrote in message
news:%23QM1jUJyHHA.4264@.TK2MSFTNGP05.phx.gbl...
> SQL 2000, Sp3
> No items is displayed in the Enterprise Manager. All systems are
> functioning properly, the EM is simply no longer useful. Any suggestions
> or thoughts are appreciated.
>

Enterprise Manager

SQL 2000, Sp3
No items is displayed in the Enterprise Manager. All systems are
functioning properly, the EM is simply no longer useful. Any suggestions or
thoughts are appreciated.Well, for starters, you can achieve most things (and better, as a bonus)
using query analyzer.
You could also try re-installing client tools (or installing client tools
for SQL Server 2005, if you have them available).
Might be helpful to see a screen shot of what "no items is displayed"
means... is it possible the user you are connecting with has changed
permissions (and you are trying to see a list of tables, for example)? Have
you tried re-registering the server, and/or connecting using windows
authentication with a local or domain administrator?
"Mark Castelli" <mcastelli98@.yahoo.com> wrote in message
news:%23QM1jUJyHHA.4264@.TK2MSFTNGP05.phx.gbl...
> SQL 2000, Sp3
> No items is displayed in the Enterprise Manager. All systems are
> functioning properly, the EM is simply no longer useful. Any suggestions
> or thoughts are appreciated.
>