Transact SQL :: How To Create A Conditional Scheduled Procedure
Aug 19, 2015
I have a table (currently with no constraints or relationships) with two columns:
Employee_CodeEarned_Leave_Balance
The leave balance should be updated automatically on a daily basis using a formula. I understand this can be done using stored scheduled procedures. To give you a full and clear picture, this is what I need:
IF [Employee_Code] LIKE 'KUW%'
[Earned_Leave_Balance] = (Today - [Employment_Date]) * (42 / 365)
ELSE
[Earned_Leave_Balance] = (31/12/CurrentYear - [Employment_Date]) * (30 / 335)
View 6 Replies
ADVERTISEMENT
May 8, 2015
I have 3rd party application implementation I am doing, if I have stored procedure in one database, can it be copied to other database (newly created) on the same server? I will have the name of old and new database when this will be copied. I do need to automate this copy.
View 10 Replies
View Related
Sep 1, 2015
Need creating a stored procedure which creates a table.
View 3 Replies
View Related
Jun 13, 2002
I need to use FTP task in DTS ( or any other way), but the problem is, the source file name is not constant. It changes every day with a date extension appended to the file name, like PO_2002_6_12_PM.zip. How do I create a task task to grab this file daily as a scheduled task? Any help is appreciated.
thanks.
Di.
View 2 Replies
View Related
Apr 20, 2008
hi everyone
i want to execute a sql insert statement everyday at 7 AM .
how to do this ?
here is the query
insert into attendance
{
date,
accountId,
clockIn,
clockOut
}
values
{
now, // current date
accountId, // employee id
null,
null
}
thanks
View 9 Replies
View Related
Jan 7, 2008
i want to know how can i run a CREATE BACKUP stored procedure using windows 2003 scheduled task on a specefic time?
View 5 Replies
View Related
Oct 15, 2015
TSQL statement, it should select the first date of the month at 11:59 PMÂ as Firstdate and Closedate as 10 days later at 11:59 PM.
View 14 Replies
View Related
Jul 25, 2006
We just installed SQL Server 2005. I have 3 jobs within SQL Server 2000 that i would like to import into the new server howeber i have no clue on how to achieve this. In fact I dont even know how to make a new job within SQL Server 2005 at all? Can someone please give me the steps on how to do this.
BTW the old job accesses launches a .EXE application every day, once a day at 5pm and the other job launches the same applicationb but at 5am. I had to have 2 seperate jobs because I couldnt figure out how to pass a different parameter for the two launches.
Thanks.
View 3 Replies
View Related
Nov 28, 2007
Hello Experts,
I found some similar threads and guides but they didn€™t help me with my special problem.
I converted a dts package (built in SQL 2000) to SQL 2005. Right now it€™s a legacy package.
(I tried the Tool Microsoft SQL Server 2000 DTS Designer Components to open the package. It€™s going well)
I would like to build a scheduled job which runs this dts package.
In SQL 2000 you can right click on the package and create the job.
SQL created string like this:
Dtsrun ASDFHJKSF56A4DFSLAKDHFJKS65646ASDFHSF (very long sting, it€™s the ID of the dts package)
How can I make something like this in SQL 2005?
Where I can get the ID of a dts package from?
Best Regards,
Alex
p.s.
- I red the thread from Jamie Thomason and will directly mark as answer after I get a answer
- of cource I will delete my thread too if I overlooked a thread with the same issue
View 5 Replies
View Related
Nov 8, 2007
Hi Guys,
Any assistance you people can provide will be considered grateful. I€™m in search of a generic cookie cutter snippet of code that simulates the creation of an Oracle DBMS_JOB, but I need it for SQL Server 2005.
Beneath, highlighted in red, is how we create a DBMS_JOB in Oracle, but I need the snippet of code to accomplish the same objective on SQL Server 2005. In Oracle, we deploy our scheduled jobs using anonymous pl/sql which is written in black, as shown beneath. To facilitate conversion of our project scheduled task it would be great if someone can assist me in translating the code beneath written in black and red.
REM spool schedule-jobs_a.sql.log
REM declare
REM jobno NUMBER;
REM cursor job_cur is select job from user_jobs
REM where what like 'dbms_utility.analyze_schema%';
REM begin
REM
REM for job_rec in job_cur loop
REM dbms_job.remove(job_rec.job);
REM
REM end loop;
REM dbms_job.submit(jobno,what => 'dbms_utility.analyze_schema(''&&anal'',''compute'');', interval => 'TO_DATE(SUBSTR(TO_CHAR(TRUNC(SYSDATE+1)),1,10)) + 360/1440', next_date => SYSDATE);
REM dbms_output.put_line( 'Next job '||jobno );
REM exception
REM when others then raise;
REM end;
REM /
PS, I€™m not looking for instructions to create a Scheduled Job through Microsoft SQL Server Management Studio, but rather, I need T-SQL code to create a Schedule Job to run daily at some period of time and run some snippet of code.
Thanks,
Vikram
View 3 Replies
View Related
Apr 17, 2011
I have an existing MS SQL database (2008 R2). I have a very simple SQL script. I need to run this script on a daily basis and save the results as a .csv file.
I've tried creating a job in SQL Manager - that didn't work. Then I found a tutorial that had me use MS Visual Studio... couldn't get that to function either.
what program I should be using, and how to go about creating this as a task / job that can run on a scheduled basis?
View 13 Replies
View Related
Jul 14, 2015
My source table has two columns... Policynum and PolicyStartdate and data looks like..
.
Policynum             PolicyStartdate
123GÂ Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 01/01/2012Â Â Â Â
456DÂ Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 02/16/2012Â Â Â Â Â
789AÂ Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 01/21/2012
163JÂ Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 05/25/2012
Now my output should return based on 3 parameters..
First two parameters are date range... let say @fromdt and @todt
Third parameter is @policynum
Scenario-1: Enter dates in date range param and leave policynum param blank
Ex: policystartdate between '01/01/2012 and '01/31/2012'.... It returns 1st and 3rd rows from above in the output
Scenario-2: enter policy num in policynum param and don't select any dates
Ex:Â policynum ='456D'Â Â Â Â It returns 2nd row in the output
Scenario-3: Select dates in date range param and enter policynum in param
Ex: policystartdate between '01/01/2012 and '01/31/2012' and policynum
='163J'. it should return only 4th row even though dates were selected(Override date range when policynum is entered in param and just return specified policynum row in the output)
I need t-sql code to get above results.
View 12 Replies
View Related
Dec 13, 2004
Hi All,
I am trying to write a Transact-SQL statement and am having no success. I have a customers table that has some duplicate Customer Numbers in it, however the records do have a unique Reference Number. I am trying select all records that match a list of Customer Numbers but if there are more than 1 matching Customer Number I only want the one with the largest Reference Number.
My BASIC Select Statement is:
SELECT Cust_Ref_No, Customer_No, Customer_Name, Address1,
Address2, Suburb, State, Postcode, Phone
FROM Customers
WHERE Customer_No IN
(SELECT Customer_No FROM temp_Customers)
Much to my HORROR I found that what I am trying to do is simple in MS Access using the “First” Function – Such as below:
SELECT First(Cust_Ref_No) AS Cust_Ref_No, Customer_No
FROM Customers
GROUP BY Customer_No
ORDER BY First(Cust_Ref_No), Customer_No;
There appears to be no “First” Function in Transact-SQL. Is someone able to show me how to achieve the same results in Transact-SQL as I can get with MS Access (I’d HATE to think that MS Access has more functionality in querying data than SQL Server in any way at all)?
P.S. I really need to run the select statement as one step rather than splitting it up into parts.
Regards, Trog28
View 3 Replies
View Related
Aug 11, 2015
I have a table with the following fields:
ID (int, identity)
Name (nvarchar(255))
Block (nvarchar(50))
Street (nvarchar(255))
Floor (nvarchar(50))
Unit (nvarchar(50))
Address1 (nvarchar(255))
Address2 (nvarchar(255))
I want to iterate through the table and populate Address1 as [Block] [Street] #[Floor]-[Unit].If the 'Floor' field contain a number < 10 (e.g., '8'), I want to add a '0' before it (e.g., '08'). Same for Unit.How would I do this using cursors (or other recommended method)?
View 4 Replies
View Related
Sep 9, 2015
Conditional Update of a field from multiple tables..I have a target table with two fields: Date and ID..There three source tables: S1, S2, S3, each of them has three fields: Date, ID, and Score...Now I want to update the target table: put the ID into the ID field which has the highest Score from the three tables on each day.
View 13 Replies
View Related
May 18, 2004
Can someone help me find a source of information or give me an example of a scheduled strored procedure ... is it even possible?
Here's the scenario:
I have a field in one of my tables "date_due," once a day I need to check this field and send an email to the owner of any record (thier email address is also stored in this table) where "date_due" is equal to today.
Any help would be greatly appreciated ... thanks!
View 4 Replies
View Related
Dec 8, 2006
Hi folks,
In an environment where replication is not possible (enabling replication breaks a proprietary application), I need to synchronize data from tables in a production database to a reporting database.
So far I have tried creating a procedure which does this in three stages (see below). While each of the stages works when executed separately, none of them seem to execute (at least nothing is effected) when the procedure is executed.
Here's an illustration of the stages. If anyone needs sample objects and data, please let me know and I'll whip some up.
CREATE PROCEDURE usp_sync_table1
AS
-- remove deleted records from target
BEGIN
DELETE FROM rept_table1
WHERE
id_col NOT IN (SELECT id_col from table1)
END
-- update existing records that have changed
BEGIN
UPDATE u
SETu.colA=s.colA
,u.colB=s.colB
,...etc
FROM
rept_table1u
,table1s
WHERE
s.modify_date > u.modify_date
END
-- add new records
BEGIN
INSERT INTO rept_table1 (
colA
,colB
,...etc)
SELECT
colA
,colB
,...etc
FROM
table1
WHERE
id_col NOT IN (SELECT id_col FROM rept_table1)
END
That's the basic idea. Each block works fine executed alone, outside the stored procedure. The procedure executes successfully, but no records are effected. Once I can get this to work I want to schedule a job to execute the procedure at a specific interval (there will actually be several, some executing hourly and some nightly).
Thanks in advance for any suggestions!
Regards,
Daniel
View 11 Replies
View Related
Apr 24, 2008
Hi,
I am new to service broker but I heard that maybe can be useful for my needs.
I have to install my DB to many different machines with SQL Server 2005 express edition and I need a scheduled execution of a procedure (each night ay 4 AM). Due to the "express" I don't have the server agent and due to the kind of the application I can't use task scheduler in order to execute a command by sqlcmd.
So, service broker (included inside the DB instance, correct?!?) can helps me?
Thank you.
Luca
View 4 Replies
View Related
Apr 24, 2008
Hi,
I am new to service broker but I heard that maybe can be useful for my needs.
I have to install my DB to many different machines with SQL Server 2005 express edition and I need a scheduled execution of a procedure (each night ay 4 AM). Due to the "express" I don't have the server agent and due to the kind of the application I can't use task scheduler in order to execute a command by sqlcmd.
So, service broker (included inside the DB instance, correct?!?) can helps me?
Thank you..
Luca
View 5 Replies
View Related
Jul 20, 2005
Hi All,I have a stored procedure.I need to create a scheduled job using that stored procedure.I went to Enterprise Manager -->Management--> Jobs-->New JobPropertiesIn the step tab, I can select db and put the codes.Instead of writing the code there, I want to call the stored procedurein the command box.How can I do that?System: MS SQL Server 2000I would highly appreciate your help.Thanks a million in advance.Best regards,mamun
View 1 Replies
View Related
May 18, 2000
Using: SQL Server 7.0
Is there any way to use conditional SQL in the SELECT clause of a stored procedure, i.e., use an IF-THEN to SELECT either COUNT(*) or a column-list based on the value of a parameter passed to the stored procedure? (I'd like to avoid having two sets of queries with the same WHERE clause).
View 3 Replies
View Related
Mar 28, 2001
Using: SQL Server 7.0
I need to create a stored procedure for a report Web page which allows the user to specify different criteria. Is there a way to conditionally add different WHERE clauses as needed to a SQL SELECT statement?
View 5 Replies
View Related
Aug 9, 2006
hello there
I have a dropdown list with a default value of
All employees
I would like to create the Where statement only if the
value of the dropdown list is Not All Employees
Select fieldname, fieldname2, fieldname3
from Table
If @dropdownVariable is not "All employees"
Where Employee = @dropdownVariable
View 4 Replies
View Related
May 29, 2008
I am not sure if this is a correct place to post this question. i am making a simple pay bill system, require people set a schedule that pays bill, then save it into database, when the time come, it auto transfers the money, i am thinking if i can do this in a store procedure. here is the interface:From Account:To Payee:Amount:ScheduleDate: Save the schedule task View scheduled task
View 5 Replies
View Related
Nov 12, 2004
Hi there,
SQL newbie here, and thanks for any help you may able to provide.
My intention is to schedule/execute a stored procedure every morning at 12:00 a.m. that deletes all records with a column value of the day before. I.E., one of my Table columns is named POSTDAY, and could have values such as Sunday, Monday, Tuesday, etc, and on Tuesday morning, I'd like to DELETE all records with a POSTDAY value of Monday.
I think I can do this by creating and scheduling 7 different stored procedures (each with the actual DayName), but was wondering if it's possible to just have 1 accomplish the same thing, and without having to pass any parameters to it.
Thanks again.
View 4 Replies
View Related
Nov 13, 2003
I need to create a stored proc which has a conditional WHERE clause depending on the value of a passed parameter. I'm having trouble handling the condition. I'm missing something here.
CREATE PROCEDURE Milestone_Get
(@myID int, @iShowAll int)
AS
SELECT uid, name, date, registration_confirmed
FROM tbl_members
WHERE
If @iShowAll = 0
begin
(uid = @myID) AND (registration_complete = 0)
end
else
begin
(uid = @myID)
end
GO
Thanks,
david
View 2 Replies
View Related
Nov 4, 2005
I need to create a conditional if or case statement in SQL Server 2000
for a stored procedure. Basically if the value passed in is 1,2 or 3 then
it will order by either NEWID(), a text field or a datetime feild.
Not done much dynamic sql so any help would be appreciated.
Fuzzy
View 8 Replies
View Related
May 3, 2006
I want to return a subset of data from my 'items' table using a stored procedure based on a number of parameters as follows :
customer code varchar(8)
userid varchar(10)
status tinyint
where the customer code can be any valid customer code OR blank, the userid can be any valid userid or blank and the status can be 1 (item is open), 2 (item is closed) or 3 (item is open or closed, i.e. no filter on status)
Is there a better way to achieve this than the following disaster:
IF LEN(RTRIM(LTRIM(@custcode))) = 0
BEGIN
IF LEN(RTRIM(LTRIM(@userid))) = 0
BEGIN
IF @status = 1
BEGIN
-- SELECT Query here !!
END
ELSE
BEGIN
IF @status = 2
BEGIN
-- SELECT Query here !!
END
ELSE
BEGIN
-- SELECT Query here !!
END
END
ELSE
BEGIN
IF @status = 1
BEGIN
-- SELECT Query here !!
END
ELSE
IF @status = 2
BEGIN
-- SELECT Query here !!
END
ELSE
BEGIN
-- SELECT Query here !!
END
END
END
ELSE
BEGIN
IF LEN(RTRIM(LTRIM(@userid))) = 0
BEGIN
IF @status = 1
BEGIN
-- SELECT Query here !!
END
ELSE
IF @status = 2
BEGIN
-- SELECT Query here !!
END
ELSE
BEGIN
-- SELECT Query here !!
END
END
ELSE
BEGIN
IF @status = 1
BEGIN
-- SELECT Query here !!
END
ELSE
IF @status = 2
BEGIN
-- SELECT Query here !!
END
ELSE
BEGIN
-- SELECT Query here !!
END
END
END
View 6 Replies
View Related
Aug 11, 2006
Is there a simple way to do conditional totaling?
I have a table that has incremental values, so the maximum value is the current total, however on occassion the value is reset and the increment begins anew. What I need to do is get the total the maximum value each time it is reset.
ie.
Value Date
----- -----
12 1-1
14 1-2
100 1-8
35 1-10
50 1-12
5 1-15
the total would be 155 (100 + 50 + 5)
View 4 Replies
View Related
Jul 20, 2005
Hello.Looking for a smarter way to code the following. I have a storedprocedure I will be passing several variables to. Some times, some ofthe fields used in a WHERE clause will not be passed, and I would liketo avoid having to code a bunch of if statements to set the executingcode. For example, below I would only like to execute the LIKEconditions only when the variable in question is not NULL. I did atest and if the variable is set to null, obviously the select does notreturn what I'm expecting.if @switch = "B"SELECT * from ikb whereikbtitle like @ins1 andikbtitle like @ins2 andikbtitle not like @ins3 andikbbody like @ins1 andikbbody like @ins2 andikbbody not like @ins3endThanks for any help or information with this.
View 2 Replies
View Related
Feb 6, 2007
Hi all,
I created a stored procedure which perfoms a simple select like this:
CREATE PROCEDURE Reports_Category
( @loc varchar(255), @pnum varchar(255) )
AS declare @vSQL nvarchar(1000) set @vSQL = 'SELECT ' + @loc + ', ' + @pnum + ' FROM Category ' exec sp_executesql @vSQL
RETURNGO
It takes field names as parameters. What happens is that when I supply value to only one parameter, the procedure gives error as it is expecting both values. Is there a way to make these parameters work like an 'OR' so that the procedure returns a dataset even if there is only one value supllied.
Please help,
Thanks,
bullpit
View 7 Replies
View Related
Apr 23, 2005
Hi,
I'm converting some direct SQL queries into stored procedures. The
original queries have conditional parts appended depending on certain
parameters like this:
Sql = "First part"
If certain condition then
Sql &= "Second part"
Endif
How could I do this in a stored procedure? So far I have made them like this:
IF @condition > 0
Fist part Second part
ELSE
First part
You can see that I have to maintain 'First part' in two locations. What is a better way of dealing with this?
Thanks!
Noc
View 3 Replies
View Related
Apr 17, 2007
Created a stored procedure which returns Selected table from database.
I pass variables, according to conditions
For some reason it is not returning any result for any condition
Stored Procedure
ALTER PROCEDURE dbo.StoredProcedure
(
@condition varchar(20),
@ID bigint,
@date1 as datetime,
@date2 as datetime
)
AS
/* SET NOCOUNT ON */
IF @condition LIKE 'all'
SELECT CllientEventDetails.*
FROM CllientEventDetails
WHERE (ClientID = @ID)
IF @condition LIKE 'current_events'
SELECT ClientEventDetails.*
FROM ClientEventDetails
WHERE (ClientID = @ID) AND
(EventFrom <= ISNULL(@date1, EventFrom)) AND
(EventTill >= ISNULL(@date1, EventTill))
IF @condition LIKE 'past_events'
SELECT ClientEventDetails.*
FROM ClientEventDetails
WHERE (ClientID = @ID) AND
(EventTill <= ISNULL(@date1, EventTill))
IF @condition LIKE 'upcoming_events'
SELECT ClientEventDetails.*
FROM ClientEventDetails
WHERE (ClientID = @ID) AND
(EventFrom >= ISNULL(@date1, EventFrom))
IF @condition LIKE ''
SELECT CllientEventDetails.*
FROM CllientEventDetails
RETURN
Also I would like to find out if I can put only "where" clause in if condition as my select statements are constants
View 2 Replies
View Related
