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.
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
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?
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
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
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.
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!
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.
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
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.
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.
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.
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?
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')
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.
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.
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??
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!
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"); **********************************************************************************************************
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
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 .
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
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??????
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?
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.
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?
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.
