Master.dbo.sysdatabases
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
ADVERTISEMENT
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
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
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
Jul 23, 2005
I have a strange question and hope someone can shed some light on theproblem.First let me give you my scenario. We are using doubletake to replicate ourdatabase server files between the local site and off site. I will namethese servers srv1a (local) and srv1b (remote). It now time to move to newservers. 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 thed: drive. On the new servers (2a and 2b) we will place the log files one: and the data files on f:. This is the case for all databases exceptthe 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 srv1ato svr2a. It placed all the system dbs on srv2a d: drive and all otherfiles in the new structure (e: and f:). There is some work that you mustdo to move the master, msdb, model, and temp to the new location (change thestartup parameters and detaching and reattaching some other databases). Allthis works great and we have everything up and running.As part of a sanity check, I ran the following statementsselect name, filename from master..sysdatabases where name = 'master'GOexec sp_helpdb master.GOresults arename filenamemaster d:Microsoft SQLServerMSSQLdatamaster.mdf(1 row(s) affected)name db_size owner dbid createdmaster 22.00 MB sa 1 Aug 6 2000 Status=ONLINE,name fileid filenamefilegroup size maxsize growth usagemaster 1 F:Microsoft SQL Server DataMSSQLDatamaster.mdfPrimary 19072 KB Unlimited 10% data onlymastlog 2 F:Microsoft SQL Server DataMSSQLDatamastlog.ldfNULL 3456 KB Unlimited 10% log onlyIf you notice that sysdatabases table is showing the master files on D: butsp_help is showing on F: F: is the correct location. Also my startparameters is-dF:Microsoft SQL Server DataMSSQLDatamaster.mdf-lF:Microsoft SQL Server DataMSSQLDatamastlog.ldf-eF:Microsoft SQL Server DataMSSQLDataERRORLOGAny ideas why the two are not showing the same thing? Everything is workingfine and all other databases (sp_helpdb) is correct and sp_helpfile masteris showing the same thing as sp_helpdb master. Since I though sp_helpdb wasquerying sysdatabases, I am confused as to why this is happening.Any help would be greatly appreciated. Sorry for the long post.
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
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
Jun 19, 2007
I got a File with sales orders and their details.
Step 1. First I am filtering the Sales Order information and inserting it in my Sales Orders table.
Step 2.Then I am filtering the details from the sales Order and inserting them in the respective table.
My Problem is that the Sales Order File does not contain the Sales Order key (ID), this is generated by the SQL Server. How can get it in order to use it in the second step? I need it because it is a foreign key in the details table.
Any Idea?
View 4 Replies
View Related
Mar 25, 2008
Hello,
I'm new to SQL and need help with a query. Not sure if this is the right place.
I have 2 tables, one MASTER and one DETAIL.
The MASTER table has a masterID, name and the DETAIL table has a detailID, masterID, and value columns.
I want to return a populated MASTER table with entries based on the DETAIL.value.
SELECT MASTER.*
FROM MASTER
WHERE DETAIL.value > 3
This is a simplified version of my problem. I can't figure out how to set the relationship between MASTER.masterID and DETAIL.masterID. If I do an INNER JOIN, the number of results are based on the number of DETAIL entries. I only want one entry per MASTER entry.
Hope this makes sense.
How can I do this?
GrkEngineer
View 9 Replies
View Related
Nov 2, 2015
We already integrated different client data to MDS with MS Excel plugin, now we want to push back updated or new added record to source database. is it possible do using MDS? Do we have any background sync process to which automatically sync data to and from subscriber and MDS?
View 4 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
Jun 14, 2001
What would the reasons when some of user tables are in master db/user table section as well as those tables are in user db/user table section listed.
Thanks,
Glen
View 1 Replies
View Related
May 20, 1999
Hi All,
We will be transfering a big application onto another server. Is it possible to copy the Syslogins with passwords onto the new server from the old one it resides. I do not want to restore Master onto the new server because the Server name will be different.
View 2 Replies
View Related
Sep 15, 2006
if i want to create a database, using:
CREATE DATABASE name
does it need the USE MASTER before the line? a database MUST be created using Master??
i ask this because in an example i need to create a database, so i use the next code:
CREATE DATABASE Banco
ON
(NAME='P_Banco',FILENAME='C:Documents and SettingsVictorEscritorioBancoP_Banco.mdf')
LOG ON
(NAME='P_Banco_Log',FILENAME='C:Documents and SettingsVictorEscritorioBancoP_Banco_Log.ldf')
USE Banco <-- i wrote this, because i tought i could create tables and stuff directly in my previously declared database, but it sends this error:
"Could not locate entry in sysdatabases for database 'Banco'. No entry found with that name. Make sure that the name is entered correctly."
anyway, i could do it, but first creating the database, and after that, in a separate code creating the tables with USE Banco, but my question is..why is that? or i miss something in my sql code??
View 3 Replies
View Related
Jan 23, 2008
Hi Y'all,
What are the advantages of placing stored procedures in the master database? Can i always use all the database-names on the instance?
Thanks!
View 5 Replies
View Related
Feb 4, 2008
Hi all,
I am having trouble modifying the select statement(s) below to return me only unique rows instead of duplicated rows. I give up. Can someone please jump in and give it a shot? Please help out. Thanks.
blumonde
************************************************************************************************sb.Append("SELECT TOP (@PageSize) TopicID, TopicName, Message ");
sb.Append("FROM (Select TOP (@CurrentPage*@PageSize) ROW_NUMBER() OVER (ORDER BY ftp.DateEntered DESC) AS Row, ftp.TopicID, ftp.TopicName, ft.Message ");sb.Append("FROM forumTOPIC ftp, forumTHREAD ft, forumCategory cat, forumTechnology tn ");
sb.Append("WHERE tn.TechnologyID = cat.toTechnologyID AND cat.CategoryID = ftp.toCategoryID AND tn.TechnologyName LIKE @KeyForum AND ftp.TopicID = ft.toTopicID AND FREETEXT(ft.Message, @KeyWord) ");sb.Append("GROUP BY ftp.TopicID, ftp.TopicName, ftp.DateEntered, ft.Message ");
sb.Append("ORDER BY ftp.DateEntered DESC) as t1 ");sb.Append("WHERE Row BETWEEN (@CurrentPage-1)*@PageSize+1 AND (@CurrentPage*@PageSize) ");
sb.Append("ORDER BY t1.Row ASC");
**********************************************************************************************************
View 4 Replies
View Related
Feb 7, 2002
I had installed a third-party software on my server and that corrupted my Master DB. Over the weekend when we bought SQL server down, we could not bring it back up. I had to re-install SQL server and then restore all my databases from the backup. Is there a better way of doing this???
Curiously
Someone who calls himself a DBA
View 4 Replies
View Related
Feb 21, 2002
Hi Friends ...
I need your advice and help on the following problem . Say for example I have a SQL 7.0 Server A with 30 databases and i am in the process of transfering these databases to another system which is SQL Server B with 7.0 again .
Although the Databases can be copied by various means , can i restore Master database from the Server A to Server B so that my Server B will have all the database and login information as that of Server A.
As Master database is system database , it won't allow to be dropped and hence you cannot restore .
Is there any other way to restore Master database from One SQL server 7.0 to another with the same SQL version .
Any kind of assistance will be of great help to me . Many thanks in advance .
Kind Regards
Sneha
View 4 Replies
View Related
Apr 12, 2002
There is a table in the master DB where the sourcode of the SP's is saved, dies anyone knows the table's name?
Regards,
View 1 Replies
View Related
Jul 9, 2001
Hi,
I have a question regarding master database.I know if a master database is
corrupt we need to rebuild the master.One of the guys suggested us to
maintain a copy of master data file and log file in diffrent directory and
when master database is corrupt we can copy the files to the actual location
and restart the sql server and this should fix the issue and by this method we can save the time of rebuilding the master database.I don't know if this works.Can anyone please tell me if this works and also disadvantages if any with this method?
thanks
Mohan
View 5 Replies
View Related
Jul 18, 2001
Various books read suggest that the Master
database be backed up when it is changed.'
What activities does a DBA perform (or perhaps)
a user) will cause the Master database to
change?
I am aware that information about the addition
of new databases is kept in the Master.
TIA Gary
View 1 Replies
View Related
Aug 15, 2001
Hi,
how long a rebuildm.exe takes?
TIA
View 1 Replies
View Related
Oct 9, 2001
When I try to restore master, I keep getting an error message, while it is in the process of restoring, that says the connection has been broken. I assume it means the connection for single user mode. Any ideas on how to fix this??????
View 2 Replies
View Related
May 15, 2000
I tried to import a stored procedure from one Master database to another Master database, but it won't allow me. I then tried to copy and paste that system stored procedure on isql, but it still won't allow me.
Why I can do a DTS on other databases, except on Master database?
Is there any way around it?
Thanks for any help.
View 1 Replies
View Related
Nov 4, 2000
hi, What are the symptoms that the master database is currepted?
If I do not know the databases sort order, code page, and Unicode collation and I had a master database crash, it is important during the rebuild process to maintain same configuration,
Is there a way to run a script which tells me the configuration set up of the master database. so I know ahead of time what configurations Ican use during the rebuilding process.
Ahmed
View 1 Replies
View Related
Oct 26, 2000
hi, what are the reasons for rebuilding master database and what are the steps to complete this task
Thanks
Ahmed
View 1 Replies
View Related
Jan 28, 2000
Does anyone have anything that can automate the restoration of the master database in MS SQL 7? I need to do it on a daily basis on a back up server. I also need to do the 2 production databases and the msdb and model as well. Does anyone have any suggestions to keep me from reinventing the wheel?
Thanks,
Tim Davis
View 1 Replies
View Related
Jan 16, 2001
Hi All,
I am currently creating a SQLServer 7 server. This server will be used to host customer databases that I will restore on to the server. However, I want to prevent these customers accessing any other databases on the server, apart from their own. By removing the public database role from each customer database, and granting them very limited rights (basically exec rights on their own Stored Procs)on their own db, I plan to limit them to their own db. However, my problem is this:
As you cannot remove the public role from the master db, a user could easily exec the following in a stored proc to read from the master:
Select * from master..sysusers
How do I prevent the users from accessing the master in this fashion.
Will removing every permission from the public role in master be enough?
Will removing every permission from the public role in master have any other side effects?
Will removing the public role from other user dbs be enough to secure them?
Any suggestions/pointers would be appreciated.
Gary.
View 3 Replies
View Related
