Create store precedure and job

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Create store precedure and job

Author

Topic

lydia
Starting Member

34 Posts

Posted - 2012-08-29 : 03:56:13

Hi all ! How are you so far?Hope you all doing good :)
I need your help again with Store procedure.

I have two table :
Table 1
------------
calling
called
time
duration
cost
balance

Data in table1 I was inserted from Table 2 by query as below:

insert into log.dbo.table1
select calling,called,time,
duration,charge,balance
from xxx.dbo.table2
where left(timestamp,8)='20120807'
and serviceflow = '1'

Normally, i insert by manual everyday.Please help guide me how to create store procedure and job schedule for run auto :)

Thanks in advance

shaggy
Posting Yak Master

248 Posts

Posted - 2012-08-29 : 05:18:28

reply back with what have you tried till now?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-29 : 12:50:18

sounds like unpivot. look for syntax of UNPIVOT statement in books online and make a start

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

lydia
Starting Member

34 Posts

Posted - 2012-08-30 : 05:08:34

CREATE TABLE [dbo].[table1](
[calling] [varchar](20) NULL,
[called] [varchar](20) NULL,
[time] [varchar](14) NULL,
[duration] [float] NULL,
[cost] [float] NULL,
[balance] [float] NULL
) ON [PRIMARY]

GO

**create procedure

ALTER PROCEDURE [dbo].[insert_call_detail]

AS
BEGIN

insert into log.dbo.table1
select calling,called,Time,
duration,Charge,Balance
from xxx.dbo.table2 where time='20120801'
and serviceflow = '1'

END

***execute call_logdb.dbo.insert_call_detail

(0 row(s) affected)

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2012-08-30 : 05:18:34

USE [msdb]
GO

/****** Object: Job [ArchiveData] Script Date: 08/30/2012 05:15:44 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 08/30/2012 05:15:44 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'ArchiveData',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'NT AUTHORITY\SYSTEM',
--@owner_login_name=N'IdeaWorks\aasim.abdullah', --CHANGE OWNER LOGIN NAME ACCORDINGLY
@job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Step1_ArchiveData] Script Date: 08/30/2012 05:15:44 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Step1_ArchiveData',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0,
@subsystem=N'TSQL',
@command=N'insert into log.dbo.table1
select calling,called,Time,
duration,Charge,Balance
from xxx.dbo.table2
where CONVERT(VARCHAR(50),timestamps,106)=CONVERT(VARCHAR(50),GETDATE(),106)
and serviceflow = ''1''

',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Sch_ArchiveData',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20120830,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959,
@schedule_uid=N'66c8c473-d3f2-45c1-9f8d-47819450eb36'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

--------------------------
http://connectsql.blogspot.com/

lydia
Starting Member

34 Posts

Posted - 2012-08-30 : 06:29:01

is it sample?

lydia
Starting Member

34 Posts

Posted - 2012-08-30 : 06:37:40

Could you please help me to Create Procedure only?
Coz, sample above i can not understand :(

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2012-08-30 : 08:18:39

why you need stored procedure. Through your existing script i guess, you need to archive your current date data. So i placed required tsql in a job and created a job script.

--------------------------
http://connectsql.blogspot.com/

shaggy
Posting Yak Master

248 Posts

Posted - 2012-08-30 : 09:09:33

why need to run as job ?
create trigger instead

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-30 : 10:14:22

quote:
Originally posted by shaggy

why need to run as job ?
create trigger instead

why do you need trigger for archiving? for that a job with less frequency is enough rather than firing it each time upon DML action. If it was auditing/CDC then trigger do make sense.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

lydia
Starting Member

34 Posts

Posted - 2012-09-03 : 00:55:16

hi ! i need store procedure, because i need to create job by use store procedure name. I don't want to past the query into job.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-03 : 15:34:33

quote:
Originally posted by lydia

hi ! i need store procedure, because i need to create job by use store procedure name. I don't want to past the query into job.

ok stored procedure is fine
what i suggest was to call from job rather than using a trigger. In any case stored procedure makes sense over adhoc query

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

lydia
Starting Member

34 Posts

Posted - 2012-09-03 : 23:01:11

hi ! Lion . Thanks so much for your help, just let you know i used SQL Server 2008

lydia
Starting Member

34 Posts

Posted - 2012-09-05 : 23:12:48

lydia
Starting Member

34 Posts

Posted - 2012-09-05 : 23:14:35

Anyone help me please :)

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2012-09-06 : 06:01:28

you have hard coded time in the procedure,check if you have data for that value.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-08 : 17:29:17

quote:
Originally posted by lydia

Anyone help me please :)

try changing it like below


CREATE TABLE [dbo].[table1](
[calling] [varchar](20) NULL,
[called] [varchar](20) NULL,
[time] [varchar](14) NULL,
[duration] [float] NULL,
[cost] [float] NULL,
[balance] [float] NULL
) ON [PRIMARY]

GO

**create procedure

ALTER PROCEDURE [dbo].[insert_call_detail]

AS
BEGIN

insert into log.dbo.table1
select calling,called,Time,
duration,Charge,Balance
from xxx.dbo.table2 where time>='20120801'
and time < '20120802'
and serviceflow = '1'

END

if it has also time part

i dont understand why you've field as varchar when it stores date and time values. Always try to use proper datatype for fields

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

lydia
Starting Member

34 Posts

Posted - 2012-09-09 : 23:06:03

Hi visakh ! i should use datatype as dateime for store date and time value,right?
Coluld you suggestion? I would like to create procedure for insert data to table auto by function getdate().

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-09 : 23:13:22

quote:
Originally posted by lydia

Hi visakh ! i should use datatype as dateime for store date and time value,right?
Coluld you suggestion? I would like to create procedure for insert data to table auto by function getdate().

yep exactly

make datatype as datetime

sorry didnt get second question. whats the issue you're facing with use of GETDATE() function?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

lydia
Starting Member

34 Posts

Posted - 2012-09-10 : 04:56:36

Thanks for your kind answer visakh.For the second question i would like to add daily data without fixed the date.I mean the date will depend on my computer.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-10 : 10:11:45

ok. for that you just need to replace the date field with GETDATE()

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources