Why Is Sp_helpdb And Sysdatabases Different
Jul 23, 2005
I have a strange question and hope someone can shed some light on the
problem.
First let me give you my scenario. We are using doubletake to replicate our
database server files between the local site and off site. I will name
these servers srv1a (local) and srv1b (remote). It now time to move to new
servers. These servers will be srv2a (local) and 2b (remote).
Currently srv1a replicates to srv1b. The sqlserver service is off on srv1b.
It is only replicating the database files. Works great.
Now the problem. srv1a and srv1b have all the logs and data files on the
d: drive. On the new servers (2a and 2b) we will place the log files on
e: and the data files on f:. This is the case for all databases except
the system databases, which will reside all ldf and mdf on f:.
In order to move to the new servers, as a test we began replicating to srv1a
to svr2a. It placed all the system dbs on srv2a d: drive and all other
files in the new structure (e: and f:). There is some work that you must
do to move the master, msdb, model, and temp to the new location (change the
startup parameters and detaching and reattaching some other databases). All
this works great and we have everything up and running.
As part of a sanity check, I ran the following statements
select name, filename from master..sysdatabases where name = 'master'
GO
exec sp_helpdb master.
GO
results are
name filename
master d:Microsoft SQL
ServerMSSQLdatamaster.mdf
(1 row(s) affected)
name db_size owner dbid created
master 22.00 MB sa 1 Aug 6 2000 Status=ONLINE,
name fileid filename
filegroup size maxsize growth usage
master 1 F:Microsoft SQL Server DataMSSQLDatamaster.mdf
Primary 19072 KB Unlimited 10% data only
mastlog 2 F:Microsoft SQL Server DataMSSQLDatamastlog.ldf
NULL 3456 KB Unlimited 10% log only
If you notice that sysdatabases table is showing the master files on D: but
sp_help is showing on F: F: is the correct location. Also my start
parameters is
-dF:Microsoft SQL Server DataMSSQLDatamaster.mdf
-lF:Microsoft SQL Server DataMSSQLDatamastlog.ldf
-eF:Microsoft SQL Server DataMSSQLDataERRORLOG
Any ideas why the two are not showing the same thing? Everything is working
fine and all other databases (sp_helpdb) is correct and sp_helpfile master
is showing the same thing as sp_helpdb master. Since I though sp_helpdb was
querying sysdatabases, I am confused as to why this is happening.
Any help would be greatly appreciated. Sorry for the long post.
View 2 Replies
ADVERTISEMENT
Jul 9, 2002
Has anyone every seen this error message? If so, what caused it?
Server: Msg 515, Level 16, State 2, Procedure sp_helpdb, Line 53
Cannot insert the value NULL into column '', table ''; column does not allow nulls. INSERT fails.
The statement has been terminated.
Thanks
View 2 Replies
View Related
Feb 16, 2005
i use sp_helpdb on one of my databases everyday to estimate growth.
3 days in a row now i get the same results how could this be every day there are about 30000 record that gets inserted to my tables inside the db. should the information changed since it is growing in size ?
View 4 Replies
View Related
Feb 18, 2005
Well i have been using sp_helpdb 'mydatabasename' to get the info for 3 consecutive days now and it returns the same result every day in KB for data files and in MB overall database size. Wich is kinda impossible since this is a warehouse and there are about 30000 records insert every night what is going on. plz help
View 1 Replies
View Related
Dec 6, 2005
Hi,
I have MOM 2005 installed to monitor all our sql servers. In one of the sql server on a specific db, I get error alerts when running the sp_helpdb error.
The error output is
"Server: Msg 515, Level 16, State 2, Procedure sp_helpdb, Line 53
Cannot insert the value NULL into column '', table ''; column does not allow nulls. INSERT fails.
The statement has been terminated."
I checked the sp_helpdb stored procedure and here is where it fails:
insert into #spdbdesc (dbname, owner, created, dbid, cmptlevel)
select name, suser_sname(sid), convert(nvarchar(11), crdate), dbid, cmptlevel from master.dbo.sysdatabases
where (@dbname is null or name = @dbname)
I understand this is a temporary table created by the stored procedure to insert all the db info.
I ran a query as follows:
select name, suser_sname(sid), crdate, dbid, cmptlevel
from master.dbo.sysdatabases
The output is
DB Name User Name Cr date db id cmpt
master sa2000-08-06 01:29:12.250180
model sa2000-08-06 01:40:52.437380
msdb sa2000-08-06 01:40:56.810480
RMSummaryNULL2002-06-13 16:40:32.203880
tempdb sa2005-11-29 19:10:48.450280
The problem is in RMSummary database and the "dbo" login does not
have a username.
How do I add a User Name "sa" to the DB Owner "dbo" for the RMSummary database?
I did try the following things with no success:
1. Try to delete the 'dbo' user in 'RMSummary' database so that
I can add a new login name as 'dbo' and username is 'sa'.
Error message is 'dbo' owns some objects and does not get deleted.
2. I tried sp_changedbowner but it does not work either.
Any help on this quickly is much appreciated.
Thanks
Murali
View 7 Replies
View Related
Apr 16, 2004
Hey,
I want to do a 'select db_size from sp_helpdb where name = @DB;'
The problem is that SQL doesn't interpret sp_helpdb as a table :-(
How can I query the result of this stored procedure?
Thx
View 1 Replies
View Related
Sep 25, 2005
Hi friends,
I want to select only filename column of sp_helpdb's output.But I don't know how should I do this? Other way is doing a direct select from sysfiles or sysdatabases but as you know microsoft doesn't recommand this way. Also I can select that filed in my application level but it is strange for me: Of course sql server should has a proper way to do a select from output curosr of this procedure,Doesn't it?
-Thanks in advance
View 4 Replies
View Related
Nov 15, 2005
When I run sp_helpdb against the master (or any other DB for that matter) I get the following error:
Server: Msg 515, Level 16, State 2, Procedure sp_helpdb, Line 53
Cannot insert the value NULL into column 'owner', table 'tempdb.dbo.#spdbdesc_ 0010001A6EF'; column does not allow nulls. INSERT fails. The statement has been terminated.
I have about 20 DBs on a SQL Server 2000 Standard Edition Instance with sp3a .
There have been no changes made to the Server or the SQL Server Instance in quite some time. Can anyone tell me how I should proceed??????
View 6 Replies
View Related
Jul 23, 2005
Hello.How to format field "create" (Jan 12 2005) in sp_helpdbprocedure to sth like yyyy-mm-dd (2005-01-12) in SQL?bye...--__ __|__\ | || |_// / \ \_// FreeBSD: The Power To Serve|__// |__|| | \ \__// / \ +------------------------------++[ http://bukox.prv.pl ][ http://total.bukox.dmkproject.pl ]+
View 1 Replies
View Related
Aug 28, 2007
Hi ALL
I have a server with 50 databases all the same with different data but the same structure, is there a way to update a table in multiple databases using the sysdatabases to get the relevant database names from?
I imagine it would have to be a cursor to do this? I checked thru the Search feature but could not find any info on this topic is it possible?
Cheers
Phil
View 5 Replies
View Related
Jul 20, 2005
Hi,One of our customers claims that the sp_helpdb truncates database namelarger than 24 characters, the release is 7.00.1094, did any oneencounter such a problem ???
View 2 Replies
View Related
Aug 16, 2002
Hi folks,
In SQL 7 when you query
select name, crdate from sysdatabases where name like 'xxx3%'
You get a reply with the rows of matching databases in sysdatabases. But in SQL 2000 the same command throws an error saying that the object sysdatabases is wrong. Here is the modified command
select name, crdate from master.dbo.sysdatabases where name like 'xxx3%'
Now my problem is I can include the latest piece of code and go further. But my older version of product will have this problem and will fail. Is there anyway that I can dothis on SQL 2000 to be compatible with my old product.
regards,
mahesh,
View 2 Replies
View Related
Sep 26, 2002
I need to know the meaning of the status bit in sysdatabases because BOL only reports some of them.
For SQL 7.0 SP1 I have some database with a status bit equal to 0 (zero) and equal to 12.
For SQL 2000 SP2 I have some database with a status bit equal to 0 (zero),24,20 and 1073741840.
So I would really appreciate if someone can explain the meaning of these status bits.
Thank you very much.
Franco
View 2 Replies
View Related
Mar 4, 2002
Hi evrybody,
For some reason unbeknownst to me the column 'suid' in the sysdatabases table has a value of <NULL>. I need to change that, however that can't be done directly (column can't be updated) and using stored proc to change the dbowner has no effect on that value, although it is related to the dbowner.
Any ideas will be welcomed, because I am at a dead end now.
Thanx
View 2 Replies
View Related
Feb 7, 2004
Hey there, could someone please help me!!
I had created a database in sql server but since then installed windows xp pro and now when i go through the microsoft sql server dsn configuration the database is not valid and therefore i cannot change the default database as it says it doesn't exist.
So then when i type in command prompt use 'db' it says it doesn't exist but when i try to create 'db' it says that the database already exists.
I get the error message, could not locate entry in sysdatabases for 'cti' (database name).
When i look in mysql - data - along with master etc there is my cti.mdf and log files??
I'm stuck???
Is it that the files just aren't registering through sql server???
Please help!
View 2 Replies
View Related
Oct 27, 1999
This is kind of a long story. I needed to copy a db from server "a" to server "b". I copied across our network, so SQL Server didn't know about the files. Then I wanted to use the device file(s) for the "new" db. So I created another directory with the same name and overlaid the empty device file with the one I copied from server a. Of course, when I opened EM it showed the db as "suspect". So I wanted to flip the status flag in sysdatabases to '0' so SQL server would think it was OK, and would make it available. But you also have to 'allow ad-hoc updates', which I did, but each time I stop and start sql server it stil shows the db as "suspect".
Here's the code :
update sysdatabases
set status = 0
where name = 'RAS'
Does anyone out there know what I can do to fix this db?
TIA! Bob S.
View 1 Replies
View Related
Aug 7, 2002
Any idea what this error is?
error 911: could not locate entry in sysdatabases for database 'msdb'. No entry found with that name
Thanks.
Sa
View 1 Replies
View Related
Apr 5, 2007
Hi all,
Have every night the following error in eventlog:
Event Type: Error
Event Source: SQLWRITER
Event Category: None
Event ID: 24581
Date: 05.04.2007
Time: 03:01:35
User: N/A
Computer: H0xxxx
Description:
Sqllib error: sysdatabases in SQL server instance C0xxxx is empty.
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Data:
0000: 53 51 4c 53 4e 41 50 43 SQLSNAPC
0008: 32 34 35 35 00 00 00 00 2455....
0010: 53 51 4c 53 4e 41 50 43 SQLSNAPC
0018: 32 34 31 39 00 00 00 00 2419....
Any help is appreciated.
TIA
acki
View 3 Replies
View Related
Oct 18, 2007
I am getting the exception - System.Data.SqlClient.SqlException: Could not locate entry in sysdatabases for database. Does anyone has any idea, how to resolve this?
Thanks
View 2 Replies
View Related
Jul 20, 2005
Hi Everyone,All the very best for 2004!!i need urgent help with this problem, the users are about to skin mealive!!we have an access front end with linked to sql server 2k tables.when a user tries to insert a record into one of the tables it"randomly" returns a generic ODBC error and fails to save. on otheroccasions the same record will save.a trace was applied and the following select seemed to appear rightafter the insert statement whenever it failed:select substring('NY',status/1024&1+1,1) from master..sysdatabaseswhere name=DB_NAME()i had a look at other articles in the groups re this select statement,but could not find a clear answer.i have tried the insert statements as both SQL pass throughs and justplain docmd.runsql'scan someone help me with the following:* what is the purpose of the select?* what other investigations can i do to get more info on why thisshould be happening?* how can i stop it?the table i am doing the inserts into is showing as have a numericdata type field in sqlserver, but the linked table shows this numericfield as text - could this be the problem?? this field is not used inthe insert statement.i could not find any references in the MS knowledge base.any and all help would very gratefully received.Edwinah63
View 2 Replies
View Related
Sep 14, 2007
Yesterday, literally I was banging my head to the wall, kept getting this error:
A Connection could not be established with the information provided. Hit OK to change your connection settings.
Invalid object name 'sysdatabases'.
I uninstall and re-install back the reporting services couple times and I kept getting the same error.
I have 2 instances of SQL Server on the same Server:
SQL Server 2000
SQL Server 2005
The 2 instances work side by side, I was wondering if this causing the error.
Can anyone help out with this problem?
Thanks - John
View 9 Replies
View Related
Jan 14, 2008
Hi all,
From the http://msdn.microsoft.com/en-us/library/bb384469.aspx (Walkthrough: Creating Stored Procedures for the Northwind Customers Table, I copied the following sql code:
--UpdateSPforNWcustomersTable.sql--
USE NORTHWIND
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'SelectCustomers' AND user_name(uid) = 'dbo')
DROP PROCEDURE dbo.[SelectCustomers]
GO
CREATE PROCEDURE dbo.[SelectCustomers]
AS
SET NOCOUNT ON;
SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax FROM dbo.Customers
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'InsertCustomers' AND user_name(uid) = 'dbo')
DROP PROCEDURE dbo.InsertCustomers
GO
CREATE PROCEDURE dbo.InsertCustomers
(
@CustomerID nchar(5),
@CompanyName nvarchar(40),
@ContactName nvarchar(30),
@ContactTitle nvarchar(30),
@Address nvarchar(60),
@City nvarchar(15),
@Region nvarchar(15),
@PostalCode nvarchar(10),
@Country nvarchar(15),
@Phone nvarchar(24),
@Fax nvarchar(24)
)
AS
SET NOCOUNT OFF;
INSERT INTO [dbo].[Customers] ([CustomerID], [CompanyName], [ContactName], [ContactTitle], [Address], [City], [Region], [PostalCode], [Country], [Phone], [Fax]) VALUES (@CustomerID, @CompanyName, @ContactName, @ContactTitle, @Address, @City, @Region, @PostalCode, @Country, @Phone, @Fax);
SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax FROM Customers WHERE (CustomerID = @CustomerID)
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'UpdateCustomers' AND user_name(uid) = 'dbo')
DROP PROCEDURE dbo.UpdateCustomers
GO
CREATE PROCEDURE dbo.UpdateCustomers
(
@CustomerID nchar(5),
@CompanyName nvarchar(40),
@ContactName nvarchar(30),
@ContactTitle nvarchar(30),
@Address nvarchar(60),
@City nvarchar(15),
@Region nvarchar(15),
@PostalCode nvarchar(10),
@Country nvarchar(15),
@Phone nvarchar(24),
@Fax nvarchar(24),
@Original_CustomerID nchar(5)
)
AS
SET NOCOUNT OFF;
UPDATE [dbo].[Customers] SET [CustomerID] = @CustomerID, [CompanyName] = @CompanyName, [ContactName] = @ContactName, [ContactTitle] = @ContactTitle, [Address] = @Address, [City] = @City, [Region] = @Region, [PostalCode] = @PostalCode, [Country] = @Country, [Phone] = @Phone, [Fax] = @Fax WHERE (([CustomerID] = @Original_CustomerID));
SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax FROM Customers WHERE (CustomerID = @CustomerID)
GO
====================================================================================
I executed the above code in my SQL Server Management Studio Express (SSMSE) and I got the following error messages:
Msg 911, Level 16, State 1, Line 1
Could not locate entry in sysdatabases for database 'NORTHWIND'. No entry found with that name.
Make sure that the name is entered correctly.
===============================================================================================================
I know I recreated the NORTHWIND Database from a different Database before and I did not do anything for the entry in sysdatabases. How can I change the entry in sysdatabases for database 'NORTHWIND' now? Please help and advise.
Thanks in advance,
Scott Chang
View 5 Replies
View Related
