NEW SEQUENCE NUMBER DAILY?
-
Go ask your DBA to create a SQL Agent job, schedule it for midnight daily and ask him to add that single line of code in the job step (and specify the correct database)
If your environment is properly set up you will not have permission to go and create random jobs. Even if you do, the DBA needs to know, jobs are things they check.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Gail gave you all the code you need. Now you have to create a job with a T-SQL task. Do you have any other DBAs on the team that can walk you through these steps?
Also, Books Online is your best friend. If you don't have a copy on your PC, Google it and download it. You'll be glad you did since you can search terms like "create job" or "index".
I do recommend adding a clustered index to your table to make your life easier.
CREATE CLUSTERED INDEX CIDK_Calls_PKDisplay ON CALLS (PKDisplay)
WITH (FILLFACTOR = 80,
PAD_INDEX = ON,
DROP_EXISTING = ON);
Before you do this, research indexes so you know exactly what this code does and why I'm telling you to do it.
-
Thanks again guys, you all have given me some additional homework and learning. I have no DBA, I am trying to create a database for use at work to eliminate excessive paperwork, and more digital record keeping. We looked at using ms access, however the liability of an accdb going corrupt and limits of database size was not very appealing. So i decided to play around with SQL Server, to see if I could develop a database. It has definitely been a learning curve, however I am up for the challenge.
-
Open up Books Online and read up on SQL Agent and jobs.
While you're there, read up about backups and integrity checks. SQL databases can go corrupt too, and backups are essential.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Reading now....:smooooth:
GilaMonster (3/28/2011)
Open up Books Online and read up on SQL Agent and jobs.While you're there, read up about backups and integrity checks. SQL databases can go corrupt too, and backups are essential.
-
DMS11X (3/28/2011)
Thanks again guys, you all have given me some additional homework and learning. I have no DBA, I am trying to create a database for use at work to eliminate excessive paperwork, and more digital record keeping. We looked at using ms access, however the liability of an accdb going corrupt and limits of database size was not very appealing. So i decided to play around with SQL Server, to see if I could develop a database. It has definitely been a learning curve, however I am up for the challenge.In that case, I'd like to recommend not using such a sequence number. There are better ways to account for daily activities. But, before I can explain, my recommendation would be to read the following article... it's absolutely geared towards folks thrust into the world of databases.
http://www.sqlservercentral.com/articles/Database+Design/72054/
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Oh, and by the way, what you have there is not a good table design. If it were me, I'd drop that persisted column, drop the identity column, then in the query that retrieves data calculate the description based on the date and using a ROW_NUMBER function.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
What do you guys think?
USE [msdb]GO
/****** Object: Job [CYCLE NUMBER] Script Date: 03/28/2011 08:01:05 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 03/28/2011 08:01:05 ******/
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'CYCLE NUMBER',
@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'XXXXXXXXX', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [RESET NUMBER] Script Date: 03/28/2011 08:01:06 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'RESET NUMBER',
@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'DBCC CHECKIDENT (CALLS, RESEED, 0)',
@database_name=N'SCADD',
@database_user_name=N'dbo',
@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'DAILY 0000HRS',
@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=20110328,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959,
@schedule_uid=N'bbdb13e5-b3b9-4c4b-bdb6-bc73fde75f6e'
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
-
Looks like it'll work, however see my and Jeff's previous posts.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
I recommend adding logging to the SQL Job.
Edit Step 1 and highlight Advanced on the left. It'll change from the code screen to the advanced screen.
In the Output File box, put the path for the text file you'll be logging to. We usually use "D:\SQLJobLogs\MyJob.txt". The SQLJobLogs folder is just a common folder we created to keep the logs so we don't have to search the file system for them.
Then choose "Include step output in history" for the most detailed logging. If you do this, without choosing the append option, each time the job runs it will overwrite the current log. If you append, then the information will just be added to the current log. This can get overwhelming if you have a job with a lot of steps that runs frequently.
The benefits to having a job log are many and varied. You can get a lot of information from doing this little addition to your jobs.
Also, like Gail, I don't recommend using a Persisted column for something like what you're doing. Since you're designing the database from scratch, try to design it so you won't have to fix problems later on.
I'm glad we've been able to help you find your starting place. Feel free to ask more questions.
-
DMS11X (3/28/2011)
What do you guys think?USE [msdb]GO
/****** Object: Job [CYCLE NUMBER] Script Date: 03/28/2011 08:01:05 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 03/28/2011 08:01:05 ******/
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'CYCLE NUMBER',
@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'XXXXXXXXX', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [RESET NUMBER] Script Date: 03/28/2011 08:01:06 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'RESET NUMBER',
@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'DBCC CHECKIDENT (CALLS, RESEED, 0)',
@database_name=N'SCADD',
@database_user_name=N'dbo',
@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'DAILY 0000HRS',
@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=20110328,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959,
@schedule_uid=N'bbdb13e5-b3b9-4c4b-bdb6-bc73fde75f6e'
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
Absolutely no reflection on you... you did great especially for a newbie. I just think it's absolutely hilarious that MS generated code has one of the worst practices you can have in code... GOTO. 😛
As a side bar, though, I wouldn't allow this type of sequencing into any of my DBs even if it was a requirement by the business unit. I'd have them find another way. It may be good enough (not really) for a low usage DB with few, if any, hits near midnight but on heavy hit systems, there always the chance for someone to add a row in the several milliseconds which can occur between midnight and the actual time the DBCC command puts a lock on the table to reseed the IDENTITY.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff Moden (3/28/2011)
As a side bar, though, I wouldn't allow this type of sequencing into any of my DBs even if it was a requirement by the business unit. I'd have them find another way. It may be good enough (not really) for a low usage DB with few, if any, hits near midnight but on heavy hit systems, there always the chance for someone to add a row in the several milliseconds which can occur between midnight and the actual time the DBCC command puts a lock on the table to reseed the IDENTITY.
To rephrase what Jeff is saying in simpler terms, there's a tiny gap of opportunity between the start of the day and when the job completes when a record can be inserted which breaks your requirement, ie:
[font="Courier New"]47 2011-03-28 CL-03282011-47
48 2011-03-29 CL-03292011-48
1 2011-03-29 CL-03292011-1
[/font]
I would agree with the other posters that the requirement that this value be stored in the database is not correct. What you are effectively having to do is "hard code" a calculated value, and that will give you problems later. As options, you could obtain the value in the SELECT statement, or perhaps define a VIEW.
Steve.
-
Fal (3/28/2011)
Jeff Moden (3/28/2011)
As a side bar, though, I wouldn't allow this type of sequencing into any of my DBs even if it was a requirement by the business unit. I'd have them find another way. It may be good enough (not really) for a low usage DB with few, if any, hits near midnight but on heavy hit systems, there always the chance for someone to add a row in the several milliseconds which can occur between midnight and the actual time the DBCC command puts a lock on the table to reseed the IDENTITY.
To rephrase what Jeff is saying in simpler terms, there's a tiny gap of opportunity between the start of the day and when the job completes when a record can be inserted which breaks your requirement, ie:
[font="Courier New"]47 2011-03-28 CL-03282011-47
48 2011-03-29 CL-03292011-48
1 2011-03-29 CL-03292011-1
[/font]
I would agree with the other posters that the requirement that this value be stored in the database is not correct. What you are effectively having to do is "hard code" a calculated value, and that will give you problems later. As options, you could obtain the value in the SELECT statement, or perhaps define a VIEW.
Steve.
'Zactly! 🙂
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I GOT IT
THE TABLE:
CREATE TABLE [dbo].[CFS]([SequenceId] [int] IDENTITY(1,1) NOT NULL,
[SequenceDate] [date] NOT NULL,
[SequenceNumber] AS ('CFS-'+[dbo].[GetNextSequence]([SequenceDate],[SequenceId])),
[TEXT] [nchar](10) NULL,
CONSTRAINT [PK_CFS] PRIMARY KEY CLUSTERED
(
[SequenceId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[CFS] ADD CONSTRAINT [DF_CFS_SequenceDate] DEFAULT (getdate()) FOR [SequenceDate]
GO
THE FUNCTION:
CREATE FUNCTION [dbo].[GetNextSequence] (@sequenceDate DATE,
@sequenceId BIGINT
) RETURNS VARCHAR(17)
AS
BEGIN
DECLARE @date VARCHAR(8)
SET @date = CONVERT(VARCHAR, @sequenceDate, 112)
DECLARE @number BIGINT
SELECT
@number = COALESCE(MAX(aux.SequenceId) - MIN(aux.SequenceId) + 2, 1)
FROM
CFS aux
WHERE
aux.SequenceDate = @sequenceDate
AND aux.SequenceId < @sequenceId
DECLARE @result VARCHAR(17)
SET @result = @date + '-' + RIGHT('00' + CAST(@number AS VARCHAR(8)), 8)
RETURN @result
END
RESULT:
Can someone mark this thread as solved? So if anyone else needs help...
-
Hmmm. Not sure you're quite there yet. What you have gives you:
CFS-20110401-008
CFS-20110401-009
CFS-20110401-0010
CFS-20110401-0011
CFS-20110401-0012
So, if every number you enter needs to be prefixed with '00' then ignore this comment, otherwise you need to cater for double-digit numbers ('0') and triple-digit numbers ('') and what you do if @number > 999 I'm not sure coz you'll loose your formatting.
Btw, what purpose does SequenceNumber satisfy? Why does it need to be in that particular format?
Steve.
Viewing 15 posts - 16 through 30 (of 33 total)
You must be logged in to reply to this topic. Login to reply