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
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 RelatedHi 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
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,
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
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 RelatedHey 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.
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
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
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.
Can anyone help out with this problem?
Thanks - John
Hi Experts,
I am extracting data from SQL Server 2005 to flat file destination. I am using SQL Command to specify the data selection query. One of my query uses Replicate function to derive a column value. When I execute this package it fails with the error "Data conversion failed. The data conversion for column "value" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page".
The reason for the problem is that, it is taking the InputColumnWidth of the flat file destination as 8000 and I specified the OutputColumnWidth as 4.
If I change the OutputColumnWidth to 8000, it is working without any error but resulting in the column width of 8000.
I tried using DerivedColumn Transformation's Type cast and DataConversion Transformation but still I am getting the same error in the respective Transformation components.
Can anyone suggest how to solve this issue.
Returning "completed" when status = 1 and "not completed when status = 0
View 3 Replies View RelatedHi 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
SQL7, sp3, NT4...
Unless I grant a developer "system admin" role, they are unable to see whether or not any job *not* owned by themselves is running?
EX: Developer A can't see whether Developer B's job is executing? Why? I've granted them both db_owner in msdb, and all permissions possible in msdb. Is there an extended sp I can look for?
Thanks,
Hi Gurus,
can any one help me to get the job status of a sql job.
My requirement is as under
ordinary user (not SA) will query pass job name as argument to stored procedure and sp has to give me the job status.
Thanks in advance
I have a table with columns c1, c2, c3, c4.if all nulls or blanks. Status = 0if c1 assigned but no c2, c3, and c4, then status = 1if c2 assigned but no c3 and c4, then status = 2if c3 .. then ..if c4 .. then ..I want to have one SQL to get the status like (ignored checking forblanks here for demo)SELECT Status = (if not c4 is null then 4else not c3 is null then 3else not c2 is null then 2else not c1 is null then 1else 0)FROM mytable.Thought of using CASE ... WHEN ... but it is only on one colum.Any better idea.ThanksJohn
View 1 Replies View RelatedIn SQL 2000, go to Management, than Jobs, you will see a list of all jobs and their running status. I would like to programmatically display the same on my web page. I can call sysjobs table to get the jobs and use sysjobschedules to determine whether it's been scheduled or not. Then use sysjobhistory to obtain their running history. But how and where do I get the Next Run Date information? Is this calculated or stored somewhere?Can anyone give me some help on how to work with sysjobs, sysjobschedules, and sysjobhistory tables? All I am trying to do is to build a web-based app that does the same thing as the Enterprise Manager is doing - create and schedule a job, and monitor the job status.Thanks a lot!Penn
View 1 Replies View RelatedHi all - am having trouble working out what would be the best way to do this (most efficient)I have written an order, despatch and accounts system. To get a list of orders ready for despatch, I have written a query which calculates the amount of items ordered against the amount of items despatched from that order. If there are any outstanding items, then this order is in the list of orders which are outstanding for despatch.
I am having trouble working out how to go about creating a list of orders ready do be invoiced... Orders that are ready to be invoiced can be completely despatched orders or part despatched orders. That is to say that if an order is only half despatched, my client still wants to be able to raise an invoice against that part of the order. I envisage creating a query which checks my orderline table to see if the item has been invoiced and creating the list from there. But as the application is used more and more, this query will take longer to run? ANy ideas?
Thanks.
Steve.
Hello,
Can anyone guide me to monitor whether Production Sql server is up and running and would e-mail or page me if by chance it has stopped.
Any help is appreciated.
Thanks.
Priti
is there any way to make the status of a package execution to be success without caring about failure of certain steps in the package?
View 1 Replies View RelatedHi,
Is there any way to update the status bar in Query Analyzer.
Suppose I am running a script which updates data to a file. I want to know the status showing the percentage it completed.
Thanks
:confused:
I just started working with MS SQL. I'm attempting to find the equivalent of the MySQL commands STATUS and SHOW STATUS.
Essentially, I want to connect to the database from a .net app, check the status, such as how many connections, table locks, errors, etc... and display that information on my application.
Any recommendations?
I have a question I hope someone can help me with.
My situation:
I have a single-step job in SQL Server, which runs a stored procedure, A. This stored procedure invokes another stored procedure, B. In B, one of the statements is a 'BACKUP DATABASE' command, and a database is backed up to a file.
The job is started by an application. Once started, the application then uses the SQL-DMO property CurrentRunStatus to periodically check the status of the job. When the property returns the value SQLDMOJobExecution_Idle (indicating the job has completed), the application code then continues processing, and attempts to access the .dat file produced by the 'BACKUP DATABASE' command.
My problem:
On occasion, the application will hit the problem where either the backup file cannot be located, or the file is still being locked by another process (Error=The process cannot access the file because it is being used by another process).
Is anyone able to shed some light on this?
I assume that the job will only return a completed status after:
(a) both A and B have completed execution, and
(b) the BACKUP operation has completed
Is it possible that even though SQL Server indicates the job has finished, that the BACKUP operation still hasn't completely ended?
Thanks for any help,
Andrew
Hi
My boss and I have recently started designing a new database, and we've got into a bit of a debate about creating status tables (e.g. order statuses).
It was my intial instinct to create a separate table for each kind of status (e.g. an OrderStatuses table, a JobStatuses table, etc), however my boss wanted to create one table containing all the statuses, with an extra field for defining what kind of status it is.
I was just wondering what others thought was the best approach.
Thanks
Tom
Hi Gurus,
While iam connecting to servers through management studio iam facing
iam seeing like few servers are connected with Green colour (like>)and few are connected with circle as white.Frm these how can i decide the server status.
Is there any comand to view server status ?
Thanks,
ServerTeam
Hello All,
Can someone help me with this query?
I have the following table and data structure
Table1: Orders
Field1: OrderID
Table2: OrderStatus
Field1: OrderStatusID
FIeld2: OrderID
Field3: StatusID
Field4: InsertDate
Sample Data:
Table1:
11
12
Table2:
1,11,1,6/1/2008
2,12,1,6/1/2008
3,11,2,6/2/2008
4,12,2,6/2/2008
5,11,3,6/3/2008
6,11,4,6/4/2008
Wanted Results:
11,4,6/4/2008
12,2,6/2/2008
How can i exclude those with more than one status? i tried using rows but it seems doesn't work.
ID status
1 new
1 new
1 close
2 new
2 close
3 close
3 close
4 new
4 close
the result should appear as:
ID status
2 new
2 close
4 new
4 close
I have a table of status_Master where the details are maintained by street name with status but I need the result of total count by each status against the street name.
Status_Master
Sl NoStreetNameStatus
1 Delhi Pending
2 Delhi Working
3 Delhi Working
4 Mumbai Pending
5 Mumbai Pending
6 Delhi Working
7 Delhi Problem
8 Mumbai Working
9 Mumbai Problem
50 . .
100 . .
200 . .
Result as below
StreetNameWorkingPendingProblem
Delhi 3 1 1
Mumbai 1 2 1
table1 has 3 fields:
bookNumber
123
234
345
status -- status defaul value is 0
partnerNumber
1
2
3
table 2 has 2 fields
bookNumber
234
345
567
789
partnerNumber
1
2
3
4
A stored procedure named remove_books, which remove books if the conditions are true.
If stored procedure is executed. That means, it removes the bookNumbers from table2 if the conditions are true. Lets say it removes 234 from table2
then
do update in table1 status = 1 for BookNumber 234.
I need to know how can i see the replication status. I have 4 servers with ms sql 200 standard server and hundreds of commnad to replicate. Sometime is important to know how mush of this commnads is replicated.
Thank You.
T-SQL for checking which jobs are currently running ?
And
what happen if index rebuild job overlap with shrink log file job
Is database wont allow to insert or update for other application
Thanks in Advance
Dina Satam