How To Check Whether A Procedure Is Currently Running...
Dec 13, 2007
Hi All,
I have a long running procedure (batch job) which will take quite some time to complete. I need to run a fixed number (configurable value) of copies simultaneously, i.e., either two copies or three copies at a time. (I don't want to use Service Broker for this purporse now)
For this I need to know whether this procedure is currenly being executed, and how many copies. Now I'm maintaing a flag in a table where this will get updated once the procedure starts and ends. This is not 100% reliable and need a manual check occasionally.
I would like to know, whether there is an easier method to find whether this procedure is being executed currently ?
Hope you are clear with my requirement.
SQL Server Version : 2005
Thanks in advance.
Regards
Babu
View 3 Replies
ADVERTISEMENT
May 2, 2008
If possible, in both platforms, 2000 and 2005.
---
http://www.ssisdude.blogspot.com/
View 2 Replies
View Related
Jun 4, 2008
Hello Gurus
I am using sql 2005 and one job status is executing in job monitor in 2005,How can i check since how long this job is running?
Please advice
Nitin
View 4 Replies
View Related
Aug 29, 2006
Hi,
I am skilled Oracle DBA and recently started to support MS SQL Server 2000
.
For all my Oracle DBs I run "Watch Dog" script that test if database ( server for MS SQL Server) is up and running and send me message on pager or by E-mail if it is down.
MY QUESTION IS:
How to do similar actions for MS SQL SERVER 2000?
Thanks in advance,
Michael Fradkov
View 5 Replies
View Related
Dec 30, 2002
I have a stored procedure that references another stored procedure on a linked server. I would like to check if the linked server is running prior to accessing the stored procedure. Everything I have tried returns either message 11 or 17 and terminates the stored procedure. I would like to do some cleanup before the termination. Is there any way to check this?
ben
View 2 Replies
View Related
Aug 28, 2006
Hi,
I have my SQL server installed and running on a windows 2003 server. Sometimes, the server stops unexpectedly. I want to know if there is any mechanism to alert as and when the SQL server or SQL server agent goes down.
I guess everything is a windows service. Can we have a mechanism to test this service from a script and send an alert mail when the server goes down? How can this be done from a script?
Thanks in advance
-Sudhakar
View 4 Replies
View Related
Jul 3, 2007
Hi,
I would like to now how can I check if a specific package is running now, at the moment.
the reason I need it is because I enable in a web page to run ssis package using web servise. I would like to be sure that no more than 1 person is starting the package until it completes.
Does any one has any idea?
Iram
View 6 Replies
View Related
Sep 12, 2007
Hi,
How to check if ftp/sftp server is running before actually starting the ftp/sftp taks? I tried the script task as shown in the attached link but it does not really check for ftp connection.
http://blogs.conchango.com/jamiethomson/archive/2005/10/10/SSIS-Nugget_3A00_-Verify-a-data-source-before-using-it.aspx
Thanks
View 3 Replies
View Related
Jan 21, 2004
I am about designing an access project file that connect to a SQL Server database : it could be either on the local computer or a remote workstation, this depending on where the front end is running.
Bypassing the default log on dialog box i have set my own form that users should use to connect on adp. Then using baseconnection string i connect the adp to sql server database.
My concern is how can i check from vba if the instance of SQL Server where my back end database is located is running in order to make
users knowing that due sql server service status off their connection
tentative will fail.
inform u i can check same from console management however i would to automate same controle from my adp and avoid that users have to operate such task...
View 4 Replies
View Related
Feb 29, 2008
Hello friends I want to ask that by which command I can know about how many process are currently running on the server ?
Waiting for reply.......
View 7 Replies
View Related
Apr 22, 2000
I need to start a job task from the command line. Does anyone know of the correct command(s) to do this?
I also need to check to see if a specific job task is currently executing from the command line. Does anyone know of the correct command(s) to do this?
Thanks for any assistance.
Jim
View 4 Replies
View Related
Jul 3, 2015
When i have multiple sql server instances running on one server, how to check the names of that instances with out connecting to sql server management studio.
View 12 Replies
View Related
Jul 6, 2014
Running Frequently Transaction logbackup during Integrinty check DB /optimization job will cause any issue /impact as duration will extend ...
View 2 Replies
View Related
May 31, 2006
Hey Guys,
I hope someone can help on here with this. I have this Database where techs are scheduled and dispatched to perform tasks based on skus. What I am trying to achieve is finding the first available Tech based on their schedule and the appointments table.
Example User enters today's date and 5:30 AM and the search for all available techs to perform that task
the tables ddl is
USE [Schedule]
GO
/****** Object: Table [dbo].[AllDays] Script Date: 05/31/2006 01:13:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[AllDays](
[ID] [int] NOT NULL,
[DayString] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
CONSTRAINT [PK_WorkingDays] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
USE [Schedule]
GO
/***Appointments Table where trouble is *****
/****** Object: Table [dbo].[Appointments] Script Date: 05/31/2006 01:17:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Appointments](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Customer_ID] [int] NOT NULL,
[Tech_ID] [int] NOT NULL,
[StartTime] [datetime] NOT NULL,
[EndTime] [datetime] NOT NULL,
[App_Date] [datetime] NOT NULL,
[Created_By] [int] NOT NULL,
[Date_Created] [datetime] NOT NULL,
[Sku_ID] [int] NOT NULL,
[Comment_ID] [int] NOT NULL,
CONSTRAINT [PK_TechsShifts] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
USE [Schedule]
GO
ALTER TABLE [dbo].[Appointments] WITH CHECK ADD CONSTRAINT [FK_Appointments_Comments] FOREIGN KEY([Comment_ID])
REFERENCES [dbo].[Comments] ([ID])
GO
ALTER TABLE [dbo].[Appointments] WITH CHECK ADD CONSTRAINT [FK_Appointments_Techs] FOREIGN KEY([Tech_ID])
REFERENCES [dbo].[Techs] ([ID])
USE [Schedule]
GO
/****** Object: Table [dbo].[Schedule] Script Date: 05/31/2006 01:19:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Schedule](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Tech_ID] [int] NOT NULL,
[AllDayID] [int] NOT NULL,
[ShiftStartTime] [datetime] NOT NULL,
[ShiftEndTime] [datetime] NOT NULL,
CONSTRAINT [PK_Schedule] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
USE [Schedule]
GO
ALTER TABLE [dbo].[Schedule] WITH CHECK ADD CONSTRAINT [FK_Schedule_AllDay] FOREIGN KEY([AllDayID])
REFERENCES [dbo].[AllDays] ([ID])
Plus the Techs Table which holds their ID's names etc
I have a snapshot (http://webdivisions.net/images/relation.gif) of the relationship posted here if that can help
Thanks for any input
View 9 Replies
View Related
Apr 14, 2008
Hi,
I'm using some security scanning software which looks for vulnerabilities in the database. It tells me that some stored procedure are should not be given public permission.
How do i know whether the stored procedures are being used by someone or last used on which date? Is there any way to find out?
At the same time, how do i check the permission of stored procedure on ms sql 2005? Thanks
View 3 Replies
View Related
May 2, 2004
Hello, everyone:
I have some stored procedures that run on SQL2K(SP3) /WIN2K. Sometimes I modify them using ALTER PROCEDURE statements. How to check if they are changed after ALTER statements run? Thanks.
ZYT
View 4 Replies
View Related
Sep 25, 2007
I have a store Procedure modify structTable but check syntax is error !
Please help me ?
--------------------------------------------------------------------------------------------------------------------
IF EXISTS(SELECT NAME FROM SYSOBJECTS WHERE NAME ='MODISTRCTTABLE ' AND TYPE='P')
BEGIN
DROP PROCEDURE MODISTRUCT_TABLE
END
GO
CREATE PROCEDURE MODISTRUCT_TABLE
@TABLE_NAME VARCHAR(60),
@COLUMN_NAME VARCHAR(60),
@COLUMN_TYPE VARCHAR(60),
@COLUMN_SIZE INT(10)
AS
BEGIN
IF EXISTS (SELECT @COLUMN_NAME FROM syscolumns)
BEGIN
ALTER TABLE @TABLE_NAME ALTER @COLUMN_NAME + ' ' + @COLUMN_TYPE+'('+ @COLUMN_SIZE +')'
END
ELSE
BEGIN
ALTER TABLE @TABLE_NAME ADD @COLUMN_NAME + ' ' + @COLUMN_TYPE+'('+ @COLUMN_SIZE +')'
END
END
-------------------------------------------------------------------------------------------------------------------------
CREATE PROCEDURE ENCRYPTION getString()
AS
SELECT * FORM CUSTOMER
----------------------------------------------------------------------------------------
Funtion getString() return a string is " With Encryption"?
Do I call function getString() in here ?
View 6 Replies
View Related
Aug 6, 2007
I've a stored procedure which retrieves based on different criterias. I added one more critieria - to display a column based on a range of values. The values are @OriginalMin and @OriginalMax. I declared the variables and gave the conditions. But still set ANSI_NULLS ON
set QUOTED_IDENTIFIER OFF
GO
ALTER procedure [dbo].[USP_Account_Search_Mod]@OriginalMin DECIMAL=0
,@OriginalMax DECIMAL=0
AS
DECLARE
@CRI8 VARCHAR(500)SELECT
@CRI1=''
SET @CRI8='AND OriginalBalance >=@OriginalMin AND OriginalBalance<=@OriginalMax'
SELECT @Criteria = ......+ @CRI8
When I execute this stored procedure, I get the following error message.
SELECT * FROM dbo.UDV_Tier1Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers(3)) AND Customer = '00001'AND OriginalBalance >=@OriginalMin AND OriginalBalance<=@OriginalMax UNION SELECT * FROM dbo.UDV_Tier2Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers(3)) AND Customer = '00001'AND OriginalBalance >=@OriginalMin AND OriginalBalance<=@OriginalMaxORDER BY NAME ASC
Msg 137, Level 15, State 2, Line 1
Must declare the variable '@OriginalMin'.
Msg 137, Level 15, State 2, Line 1
Must declare the variable '@OriginalMin
Could someone tell what's wrong with the procedure? For convenience, I've included only the latest critieria I added.
View 2 Replies
View Related
May 24, 2008
I have a Stored Procedure as followsUSE [MyDataBase]
GO
/****** Object: StoredProcedure [dbo].[SPLogins] Script Date: 05/24/2008 21:58:50 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
Create PROCEDURE [dbo].[SPLogins]
(
@LoginName varchar(50), @LoginD int output,
)
AS
Select @LoginID = LoginID From MyDatabase Where (LoginName Is not Null) and @LoginName = LoginName
I am trying to check for a null value in the Stored Procedure. I dont' get an error, but it doesn't catch the "null" and gives an error when there is a null value
What Is the correct wayt to go about it.
View 3 Replies
View Related
Dec 18, 2003
How to check status(valid/invalid) for all stored procedure in my DB...
View 14 Replies
View Related
Mar 14, 2007
Hi All.
Can anyone tell me what the syntax to check and see if a stored procedure exists and if it doesnt to create it is?
Thanks people.
View 8 Replies
View Related
Mar 18, 2008
I have a number of stored procedures that run one after the other. How do you code to get the success or failure so that some logic can be applied accordingly? I've heard of the TRY CATCH structure, but I new and have yet to use it. How many different ways can success or failure be handled in code?
View 4 Replies
View Related
Nov 10, 2006
Hi guys,I have written a stored procedure to check for date range, say if the user enters a value for 'city-from' , 'city-to', 'start-date' and end-date, this stored procedure should verify these 2 dates against the dates stored in the database. If these 2 dates had already existed for the cities that they input, the stored procedure should return 1 for the PIsExists parameter. Below's how I constructed the queries: 1 ALTER PROCEDURE dbo.DateCheck
2 @PID INTEGER = -1 OUTPUT,
3 @PCityFrom Char(3) = '',
4 @PCityTo Char(3) = '',
5 @PDateFrom DATETIME = '31 Dec 9999',
6 @PDateTo DATETIME = '31 Dec 9999',
7 @PIsExists BIT = 1 OUTPUT
8 AS
9
10 CREATE TABLE #TmpControlRequst
11 (
12 IDINTEGER,
13 IsExistsCHAR(1)
14 )
15 /*###Pseudo
16 1. Check the Date From and Date To
17 -- select all the value equal to parameter cityFrom and cityTo
18 -- insert the selection records into tmp table
19 --*/
20 INSERT INTO #TmpControlRequst
21 (ID, IsExists)
22 SELECT ID,
23 IsExists = CASE WHEN DateFrom <> '31 Dec 9999' AND DateTo <> '31 Dec 9999'
24 AND @PDateFrom <= DateFrom AND @PDateFrom <= DateTo
25 AND @PDateTo >= DateFrom AND @PDateTo <= DateTo THEN 1
26 WHEN DateFrom <> '31 Dec 9999' AND DateTo <> '31 Dec 9999'
27 AND @PDateFrom >= DateFrom AND @PDateFrom <= DateTo
28 AND @PDateTo >= DateFrom AND @PDateTo <= DateTo THEN 1
29 WHEN DateFrom <> '31 Dec 9999' AND DateTo <> '31 Dec 9999'
30 AND @PDateFrom >= DateFrom AND @PDateFrom <= DateTo
31 AND @PDateTo >= DateFrom AND @PDateTo >= DateTo THEN 1
32 WHEN DateFrom <> '31 Dec 9999' AND DateTo <> '31 Dec 9999'
33 AND @PDateFrom <= DateFrom AND @PDateFrom <= DateTo
34 AND @PDateTo >= DateFrom AND @PDateTo >= DateTo THEN 1
35 ELSE 0 END
36 FROM RequestTable
37 WHERE ID <> @PID
38 AND CityFrom = @PCityFrom
39 AND CityTo = @PCityTo
40
41 --======== FINAL RESULT
42 -- For tmp table:-
43 -- isExists = 1 ==> date lapse
44 -- isExists = 0 ==> date ok
45 -- if count for (isExists = 1) in tmp table is > 0 then return 1 and data not allow for posting
46 SELECT @PIsExists = CASE WHEN COUNT(*) > 0 THEN 1
47 ELSE 0 END
48 FROM #TmpControlRequst
49 WHEREIsExists = 1
50
51 SELECT @PIsExists
52 --=========
53
54 DROP TABLE #TmpControlRequst
55
56 --=========
57 RETURN(0)However, when I run this stored procedure, 'PIsExists' would always return -1. I am positive that the values that I passed in, had already existed in the database. Any idea what might be causing this problem? Thanks in advance
View 6 Replies
View Related
Sep 9, 2007
helo all...,i have create procedure can decrease totalcost from order table(database:games.dbo) with balance in bill table(database:bank.dbo). my 2 database in same server is name "boy"
i have 2 database like: bank.dbo and games.dbo
in games.dbo, have a table name is order(user_id,no_order,date,totalcost)
in bank.dbo, have a table name like is bill(no_bill,balance)
this is a list of bill table
no_bill balance
111222 200$
222444 10$
this is a list of order table
user_id no_order date totalcost
a 1 1/1/07 50$
when customer insert no_bill(111222) in page and click a button, then bill table became
no_bill balance
111222 150$
222444 10$
when customer insert no_bill(222444) in page and click a button, then message "sorry, your balance is not enough"
mystore procedure like:ALTER PROCEDURE [dbo].[pay]( @no_bill AS INT, @no_order AS int, @totalcost AS money)ASBEGIN BEGIN TRANSACTION DECLARE @balanc AS money SET @balanc= (SELECT [balance] FROM Bank.dbo.bill WHERE [no_bill] = @no_bill) UPDATE [bank.dbo.bill] SET [balance] = @balanc - @totalcost WHERE [no_bill] = @no_bill COMMIT TRANSACTIONEND it can decrease money in bank, but i want it ceck money if balance > totalcost, so balance-totalcost,if balance<totalcost,so error message"sorry, your balance not enough"is it can make in procedure?thx...
View 2 Replies
View Related
Oct 22, 2007
HI
I have a problem related Store Procedure, that i am trying to extact a value from Database (Like FirstName,LastName,Email Address) through Store Procedure and Display it in the DropDownList(Like: FirstName LastName ,(xyz@xyz.com)) , and this is working correctly.
Now i try to check the value at the same time if it is NULL value in the Database then pass EmptyString to the DropDownList Like ("" "" ,(xyz@xyz.com))
how i can do that in the store procedure.
Comments will be appreciated.
View 3 Replies
View Related
Apr 10, 2008
Hi all,How do you select and check a boolean value within a stored procedure? I don't know if this is correct or not:DECLARE @CheckStatus bitSELECT @CheckStatus = PREF_STATUS FROM tblPrefsWHERE [PREF_ID] = @PREF_IDIF (@CheckStatus IS FALSE) -- DO SOMETHINGIF (@CheckStatus IS TRUE) -- DO SOMETHING But I'm not sure if what I've used (IS FALSE/TRUE) is the correct way? "PREF_STATUS" is a bit column in my table.Thanks.
View 4 Replies
View Related
Oct 12, 2006
I have reservation database, suppose somebody reserved a resource on 10/12/2006 from 9:00am to 12pm. If anybody else want to reserve the same resource from 10am to 3pm. It will not let them reserver. I would like to check a range in store procedure. Is there has any function to check range in easy way?
Many thanks.
View 13 Replies
View Related
Mar 23, 2014
I want to know, Is it possible to check a particular stored procedure is recompiling or not?
View 7 Replies
View Related
Jul 23, 2005
HiOur SQL server has a lot of stored procedures and we want to get somecleaning up to be done. We want to delete the ones that have been notrun for like 2-3 months. How exactly will i find out which ones todelete. Enterprise manager only seesm to give the "Create Date"How exactly can I find the last called date ! I guess you could write aquery for that ! but how ???P.S I dont want to run a trace for 1 months and see what storedprocedures are not being used.
View 7 Replies
View Related
Jul 23, 2005
Hi,I would like to check if a string value exist in a string in sqlserver stored procedure, e.g.set @testString = 'this is my test document.'if (@testString contains 'test')begin.....endHow do I do this in sql server stored procedure?Thanks,June...
View 1 Replies
View Related
Jul 21, 2004
Hi, guys
I try to add some error check and transaction and rollback function on my insert stored procedure but I have an error "Error converting data type varchar to smalldatatime" if i don't use /*error check*/ code, everything went well and insert a row into contract table.
could you correct my code, if you know what is the problem?
thanks
My contract table DDL:
************************************************** ***
create table contract(
contractNum int identity(1,1) primary key,
contractDate smalldatetime not null,
tuition money not null,
studentId char(4) not null foreign key references student (studentId),
contactId int not null foreign key references contact (contactId)
);
My insert stored procedure is:
************************************************** *****
create proc sp_insert_new_contract
( @contractDate[smalldatetime],
@tuition [money],
@studentId[char](4),
@contactId[int])
as
if not exists (select studentid
from student
where studentid = @studentId)
begin
print 'studentid is not a valid id'
return -1
end
if not exists (select contactId
from contact
where contactId = @contactId)
begin
print 'contactid is not a valid id'
return -1
end
begin transaction
insert into contract
([contractDate],
[tuition],
[studentId],
[contactId])
values
(@contractDate,
@tuition,
@studentId,
@contactId)
/*Error Check */
if @@error !=0 or @@rowcount !=1
begin
rollback transaction
print ‘Insert is failed’
return -1
end
print ’New contract has been added’
commit transaction
return 0
go
View 1 Replies
View Related
Mar 21, 2004
Hi
I am trying to upload an excel file into a sql server database. I uploading the spreadsheet from an asp.net page and then running the dts froma stored procedure. But it doesn't work, I am totally lost on what I am doing wrong.
Any help would be greatly appreciated.
Asp.net code;
Dim oCmd As SqlCommand
oCmd = New SqlCommand("exportData", rtConn)
oCmd.CommandType = CommandType.StoredProcedure
rtConn.Open()
With oCmd
.CommandType = CommandType.StoredProcedure
Response.write("CommandType.StoredProcedure")
End With
Try
oCmd.ExecuteNonQuery()
Response.write("ExecuteNonQuery")
Finally
rtConn.Close()
End Try
StoredProcedure;
CREATE PROCEDURE exportData AS
Exec master..xp_cmdshell
'DTSRUN /local/DTS_ExamResults'
GO
Thanks
Rachel
View 4 Replies
View Related
Nov 3, 2006
I've set up a linked server between my SQL 2005 server and my AS400 DB2 server. I can query data successfully.
How do i call a DB2 stored procedure?
View 1 Replies
View Related
