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
What is the best way of reliably closing a cursor inside a BEGIN CATCH block? The simple problem statement is: I want to check if the cursor is open, then close it. I'm trying to use CURSOR_STATUS function and it seems to return a -3 (both when the cursor is open and not open.). Is this a bug or am I missing something?
I have removed all of my code and only provided what is necessary to repro the problem.
====================================================================================================
CREATE PROCEDURE dbo.repro_PROC
(
@condition1 varchar(10),
@condition2 varchar(10)
)
AS
BEGIN
BEGIN TRY
SET NOCOUNT ON;
SET XACT_ABORT ON;
DECLARE @dbName varchar(64), @dbid int;
DECLARE @FileID int;
DECLARE @Physical_Name varchar(512);
IF (@condition1 = 'ERROR')
RAISERROR ('ERROR: This error happens before any cursor is declared or is open', 16, 1);
DECLARE dbNameCursor CURSOR FOR SELECT Name, database_id FROM master.sys.databases ORDER BY Name;
OPEN dbNameCursor;
FETCH NEXT FROM dbNameCursor INTO @dbName, @dbid;
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE fileCursor CURSOR FOR SELECT File_ID, Physical_Name FROM master.sys.master_files
WHERE database_id = @dbid
ORDER BY File_ID;
OPEN fileCursor;
FETCH NEXT FROM fileCursor INTO @FileID, @Physical_Name;
WHILE @@FETCH_STATUS = 0
BEGIN
-- some condition which causes an exception
IF (@condition2 = 'ERROR')
RAISERROR ('ERROR: This error happens after both cursors are open', 16, 1);
FETCH NEXT FROM fileCursor INTO @FileID, @Physical_Name;
END
CLOSE fileCursor;
DEALLOCATE fileCursor;
FETCH NEXT FROM dbNameCursor INTO @dbName, @dbid;
END
CLOSE dbNameCursor;
DEALLOCATE dbNameCursor;
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
DECLARE @fileCursorStatus INT;
DECLARE @dbNameCursorStatus INT;
SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
SET @fileCursorStatus = CURSOR_STATUS('local', 'fileCursor');
SET @dbNameCursorStatus = CURSOR_STATUS('local', 'dbNameCursor');
PRINT 'Cursor_Status (fileCursor) = ' + convert(varchar, @fileCursorStatus);
PRINT 'Cursor_Status (dbNameCursor) = ' + convert(varchar, @dbNameCursorStatus);
IF (@fileCursorStatus >= 0)
BEGIN
PRINT 'Closing fileCursor';
CLOSE fileCursor;
DEALLOCATE fileCursor;
END
IF (@dbNameCursorStatus >= 0)
BEGIN
PRINT 'Closing dbNameCursor';
CLOSE dbNameCursor;
DEALLOCATE dbNameCursor;
END
-- info about the original error
RAISERROR (@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
);
END CATCH;
END;
====================================================================================================
EXEC dbo.repro_PROC @condition1='ERROR', @condition2='NO-ERROR'
Output:
Cursor_Status (fileCursor) = -3
Cursor_Status (dbNameCursor) = -3
Msg 50000, Level 16, State 1, Procedure repro_PROC, Line 88
ERROR: This error happens before any cursor is declared or is open
-------------------------------------------------------------------------
EXEC dbo.repro_PROC @condition1='NO-ERROR', @condition2='ERROR'
Output:
Cursor_Status (fileCursor) = -3
Cursor_Status (dbNameCursor) = -3
Msg 50000, Level 16, State 1, Procedure repro_PROC, Line 89
ERROR: This error happens after both cursors are open
-------------------------------------------------------------------------
EXEC dbo.repro_PROC @condition1='NO-ERROR', @condition2='ERROR'
Output:
Cursor_Status (fileCursor) = -3
Cursor_Status (dbNameCursor) = -3
Msg 50000, Level 16, State 1, Procedure repro_PROC, Line 89
A cursor with the name 'dbNameCursor' already exists. <=== THIS IS A PROBLEM
=================================================================================================
If I try to close the cursor when the status = -3, then sometimes it closes successfully, some other times, it errors "Cursor NOT open" etc.
Replace the <<job name>> with the actual job name
---Last n RUN status of the job------
SELECT TOP 20
SJ.name 'JOB Name'
,'Run date : ' +
REPLACE(CONVERT(varchar,convert(datetime,convert(varchar,run_date)),102),'.','-')+' '+
SUBSTRING(RIGHT('000000'+CONVERT(varchar,run_time),6),1,2)+':'+SUBSTRING(RIGHT('000000'+CONVERT(varchar,run_time),6),3,2)+':'+SUBSTRING(RIGHT('000000'+CONVERT(varchar,run_time),6),5,2) 'Start Date Time'
,SUBSTRING(RIGHT('000000'+CONVERT(varchar,run_duration),6),1,2)+':'+SUBSTRING(RIGHT('000000'+CONVERT(varchar,run_duration),6),3,2)+':'+SUBSTRING(RIGHT('000000'+CONVERT(varchar,run_duration),6),5,2) 'Duration'
,CASE run_status WHEN 1 THEN '1-SUCCESS' WHEN 0 THEN '0-FAILED' ELSE CONVERT(varchar,run_status) END AS 'Status'
,Step_id
,[Message]
,[Server]
FROM MSDB..SysJobHistory SJH
RIGHT JOIN MSDB..SysJobs SJ
ON SJ.Job_Id = SJH.job_id
WHERE SJ.name LIKE '%<<job name>>%'
AND Step_ID = 0 --Comments this line if you want to see the status of each step of the job
ORDER BY run_date DESC, run_time DESC, step_ID DESC
---Last n RUN status of the job---------
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 RelatedI tried all the INFORMATION_SCHEMA on SQL 2000 andI see that the system tables hold pretty much everything I aminterested in: Objects names (columns, functions, stored procedures, ...)stored procedure statements in syscomments table.My questions are:If you script your whole database everything you end up havingin the text sql scripts, are those also located in the system tables?That means i could simply read those system tables to get any informationI would normally look in the sql script files?Can i quickly generate a SQL statement of all the indexes on my database?I read many places that Microsoftsays not to modify anything in those tables and not query them since theirstructure might change in future SQL versions.Is it safe to use and rely the system tables?I basically want to do at least fetching of information i want from thesystem tables rather than the SQL script files.I also want to know if it's pretty safe for me to make changes in thesetables.Can i rename an object name for example an Index name, a Stored Procedurename?Can i add a new column in the syscolumns table for a user table?Thank you
View 4 Replies View RelatedI have 3 database say
db1
db2
db3
I need to setup a script to read all the table names in the database above and then query the database to find the list of Stored Procedure using each table.(SQL Server)
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 RelatedI 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,
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.
Hi All,
Using SQL Server 2005 w/ SP2 I have a simple select query with a parameterized WHERE clause that in Management Studio runs nearly instantly, everytime. Using the same login, when I run the same query using SQL RS the results (only 6 rows) takes upwards of 5 minutes. If I look at the Activity Monitor, it shows the process as suspended and indicates a CXPACKET. Everything I have read on the forums about this indicate it is an issue with not having SP2 , which I do. Or a 64 bit version, I have 32. Or for large bulk updates, I am doing a very simple select. Does anyone have any clue on this?
regards
Bill
Hi everyone,
while executing sp_who sub directory, I see a column which makes me totally confused. This column is status column.
I understood nothing about its usage. So would you please inform me ?
Thanks
This probably isn€™t a T-SQL question but couldn€™t find an appropriate place in the forum for it.
I€™m building a statistics page for our customers and i want to show how long the SQL server has been up since the last restart, I€™ve got the windows uptime from elsewhere but wondering if there€™s a way to get the SQL Server uptime.
Thanks
Chris
Dwebs Ltd
I need a way to determine if a query is still running on the database server. Is there a way to do this?
If the query is still running after 2 hours I need to send a message. How can I query the database for the status of a query?
Hi.
How can i check the job status using SQL Query?
I have a database that tracks events as they occur. The events are used to track the completion of a series of tasks. The events have a event type id and a result. The type id is used to track a specific task, and there is a result for it.
so the table looks something like:
DATETIME, EVENT_ID, RESULT_CODE
Since the same task can be performed many times a day, I am only interested in getting a snapshot of all event types at 11:59:59 PM. If an Task of type A had a sucess event then failed event on the same day, I would count it as failed.
Additionally, I if an Task succeeded on say 5/18, an the same task did not occur on 5/19, I would want to count it as a success because that was the last known state from the 18th.
I need to produce a report showing our progress over time (I would guess plotting a line between 0 and 100%), around tasks that have passed, failed, and have not been completed. (no completion record exists)
As far as I can tell, I need to get the last known result, per event type, per day.
I know how to do this in TSQL, but my team is making a big bet in Analysis Server and I'm at a loss as to how I should tackle this.
Any help would be appreciated.
Hi There
View 1 Replies View Related
Hi,
In command line how do i get status of Package Execution?
Thanks