bit ASE version.
*
*
* Revision History
* ================
* Version 1.0 17-Dec-2000 First version
* Version 1.1 Oct-2002 Handle identity_gap=NULL (thanks to Alan Cooper),
* and some small improvements for installation
* Version 1.2 Apr-2003 Improved installation
*
*
* Copyright Note & Disclaimer :
* =============================
* This software is provided "as is"; there is no warranty of any kind.
* While this software is believed to work accurately, it may not work
* correctly and/or reliably in a production environment. In no event shall
* Rob Verschoor and/or Sypron B.V. be liable for any damages resulting
* from the use of this software.
* You are allowed to use this software free of charge for your own
* professional, non-commercial purposes.
* You are not allowed to sell or bundle this software or use it for any
* other commercial purpose without prior written permission from
* Rob Verschoor/Sypron B.V.
* You may (re)distribute only unaltered copies of this software, which
* must include this copyright note, as well as the copyright note in
* the header of each stored procedure.
*
* Note: All trademarks are acknowledged.
*
* Please send any comments, bugs, suggestions etc. to the below email
* address.
*
* Copyright (c) 2000-2002 Rob Verschoor/Sypron B.V.
* P.O.Box 10695
* 2501 HR Den Haag
* The Netherlands
*
* Email: rob@sypron.nl
* WWW : http://www.sypron.nl/
*----------------------------------------------------------------------------
*/
set nocount on
go
set flushmessage on
go
use sybsystemprocs
go
-- we need to be at ASE 12.0 or later; if not, abort this script
if isnull(object_id("master.dbo.sysqueryplans"),99) >= 99
begin
print ""
print ""
print "********************************************"
print "********************************************"
print " This script is for ASE 12.0 or later."
print " Please install SP_IDENTITY.119.SQL instead."
print "********************************************"
print "********************************************"
print " "
print " "
print ""
set background on -- terminate this script now
end
go
print ""
print "Installing 'sp_identity'..."
print ""
go
if object_id("sp_identity_help") <> NULL
begin
drop proc sp_identity_help
end
go
create proc sp_identity_help
/* Copyright (c) 2000-2002 Rob Verschoor/Sypron B.V. */
as
begin
print " "
print " Usage: sp_identity table_name, ""hex-string-from-OAM-page"", new-identity-value "
print " Notes: "
print " - specifying only parameter 1 retrieves the current identity value for"
print " that table from the OAM page as a hexadecimal string."
print " - specifying this hexadecimal string as the second parameter (in quotes)"
print " will decode the hex value to a numeric value."
print " - specifying a numeric value for the third parameter will set that"
print " value as the new identity value for this table."
print " - parameters 2 and 3 cannot be specified together: one of them must be NULL."
print " - ""dbcc traceon(3604)"" must be run before using option 2 or 3"
print " "
print " Copyright (c) 2000-2002 Rob Verschoor/Sypron B.V."
print " See http://www.sypron.nl/idfix.html for background information & updates."
print " "
end
go
grant execute on sp_identity_help to public
dump tran sybsystemprocs with truncate_only
go
if object_id("sp_identity") <> NULL
begin
drop proc sp_identity
end
go
create proc sp_identity
/* Copyright (c) 2000-2002 Rob Verschoor/Sypron B.V. */
@p0 varchar(50) = NULL, -- table name
@p1 varchar(50) = NULL, -- hex value to decode
@p2 numeric(38) = NULL -- new value to set
as
begin
set nocount on
declare @n numeric(38), @j numeric(38), @n256 numeric(3), @n10 numeric(38)
declare @i int, @max int, @idlen int, @idlenb int, @ib int, @len int
declare @p0_id int, @b int, @b1 binary(1), @lsb int
declare @idgap int, @idburn int, @maxidgap numeric(38), @max1 int, @max2 int
declare @idburnpct numeric(5,2), @v int, @ntab int
declare @c2 char(2), @doampg int, @indid int, @dbname varchar(32)
declare @vc50 varchar(50), @colname varchar(32), @vb16 varbinary(16)
select @dbname = db_name()
-- check version
select @v = 0
if exists (select * from sysobjects
where name = "sysqueryplans" and type = "S")
begin
select @v = 12
end
if @p0 = '?'
begin
exec sp_identity_help
return 0
end
-- numeric values
select @n256 = 256
select @n10 = 10
-- id burning set factor
select @idburn = value from master.dbo.syscurconfigs where config=141
select @idburnpct = convert(numeric(5,2), @idburn * 0.00001)
-- figure out msb/lsb
select @lsb = 0
if substring(convert(binary(4), 1), 1 ,1) = 0x01 select @lsb = 1
if substring(convert(binary(4), 1), 2 ,1) = 0x01 select @lsb = 2
if substring(convert(binary(4), 1), 上一页 [1] [2] [3] [4] [5] 下一页 |
