Shulou(Shulou.com)05/31 Report--

This article mainly shows you "how to grab the largest tmstmp", the content is easy to understand, clear, hope to help you solve your doubts, the following let the editor lead you to study and learn "how to grab the largest tmstmp" this article.

Alter procedure dba.GetMaxTMstmp

@ tbname nvarchar

As

Declare @ Max_TMstmp bigint

Declare @ sql nvarchar 4000

Declare @ sql2 nvarchar 4000

Declare @ I int=0

-- set @ tbname='bond'

-- print @ tbname

Begin

-- print @ tbname

Set @ sql2='select @ i=count (1) from sys.columns col join sys.tables tbl on col.object_id=tbl.object_id where tbl.name='+''''+@tbname+''''+' and col.name='+''''+'TMSTAMP'+''''

Exec sp_executesql @ sql2, Narcissui int out', @ I out

-- print @ I

If isnull (@ iPermine 0) 0

Begin

Set @ sql= 'insert into [DBCenter]. [dbo]. [viewMaxTMtamp] ([table_name], [max_tmstamp]) select' +''+ @ tbname+''''+', isnull (cast (max (TMSTAMP) as bigint), 0) from'+ @ tbname

-- print @ sql

EXEC (@ sql)

End

End

Go

=

Create procedure dba.GetMaxTMstmp_job

As

Declare @ tblname varchar

Declare mycursor cursor for select top 10 name from sys.tables where type='U' order by name

-- Open the cursor

Open mycursor

Take the data from the cursor and assign it to the two variables we just declared

Fetch next from mycursor into @ tblname

-- determine the status of the cursor

-- 0 fetch statement succeeded

-1 the fetch statement failed or the row is not in the result set

-2 the extracted row does not exist

While (@ @ fetch_status=0)

Begin

Print @ tblname

Exec dba.GetMaxTMstmp @ tblname

Fetch next from mycursor into @ tblname

End

-- close the cursor

Close mycursor

-- undo the cursor

DEALLOCATE mycursor

=

USE [msdb]

GO

DECLARE @ jobId BINARY (16)

EXEC msdb.dbo.sp_add_job @ job_name=N'dba.GetMaxTMstmp_job'

@ enabled=1

@ notify_level_eventlog=0

@ notify_level_email=2

@ notify_level_netsend=2

@ notify_level_page=2

@ delete_level=0

@ category_name=N' [Uncategorized (Local)]'

@ owner_login_name=N'sa', @ job_id = @ jobId OUTPUT

Select @ jobId

GO

EXEC msdb.dbo.sp_add_jobserver @ job_name=N'dba.GetMaxTMstmp_job', @ server_name = Nashi DATAYESDB'

GO

USE [msdb]

GO

EXEC msdb.dbo.sp_add_jobstep @ job_name=N'dba.GetMaxTMstmp_job', @ step_name=N'dba.GetMaxTMstmp_job_daily'

@ step_id=1

@ cmdexec_success_code=0

@ on_success_action=1

@ on_fail_action=2

@ retry_attempts=0

@ retry_interval=0

@ os_run_priority=0, @ subsystem=N'TSQL'

@ command=N'dba.GetMaxTMstmp_job'

@ database_name=N'datayesdb'

@ flags=0

GO

USE [msdb]

GO

EXEC msdb.dbo.sp_update_job @ job_name=N'dba.GetMaxTMstmp_job'

@ enabled=1

@ start_step_id=1

@ notify_level_eventlog=0

@ notify_level_email=2

@ notify_level_netsend=2

@ notify_level_page=2

@ delete_level=0

@ description=N''

@ category_name=N' [Uncategorized (Local)]'

@ owner_login_name=N'sa'

@ notify_email_operator_name=N''

@ notify_netsend_operator_name=N''

@ notify_page_operator_name=N''

GO

=

USE [msdb]

GO

DECLARE @ schedule_id int

EXEC msdb.dbo.sp_add_jobschedule @ job_id=N'd61c0c64-31bc-4e1c-98e7-140b345f0f48, @ name=N'GetMaxTMstmp_job_daily'

@ enabled=1

@ freq_type=4

@ freq_interval=1

@ freq_subday_type=1

@ freq_subday_interval=0

@ freq_relative_interval=0

@ freq_recurrence_factor=1

@ active_start_date=20170412

@ active_end_date=99991231

@ active_start_time=0

@ active_end_time=235959, @ schedule_id = @ schedule_id OUTPUT

Select @ schedule_id

GO

The above is all the contents of the article "how to grab the biggest tmstmp". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.