本人是菜鸟需要做多站点共享 我在g.cn里面找了好多关于多站点共享的 其中有个关于存储seesion存储过程小弟很是疑问
alter PROCEDURE [dbo].[AspSessionprco]
@appName char(280),
@appId int OUTPUT
AS
SET @appName = LOWER(@appName)
SET @appId = NULL
SELECT @appId = AppId
FROM ASPStateTempApplications
WHERE AppName = @appName
IF @appId IS NULL BEGIN
BEGIN TRAN
SELECT @appId = AppId
FROM dbo.ASPStateTempApplications WITH (TABLOCKX)
WHERE AppName = @appName
IF @appId IS NULL
BEGIN
EXEC GetHashCode @appName, @appId OUTPUT
INSERT dbo.ASPStateTempApplications([appId],[appName])
VALUES
(@appId, @appName)
IF @@ERROR = 2627
BEGIN
DECLARE @dupApp char(280)
SELECT @dupApp = RTRIM(AppName)
FROM dbo.ASPStateTempApplications
WHERE AppId = @appId
RAISERROR('SQL session state fatal error: hash-code collision between applications ''%s'' and ''%s''. Please rename the 1st application to resolve the problem.',
18, 1, @appName, @dupApp)
END
END
COMMIT
END
RETURN 0
有2个问题
EXEC GetHashCode @appName, @appId OUTPUT
这个里面的 GetHashCode 存储过程是系统自带的吗？如果是为什么我的sqlserver2005里面美玉哦，如果不是请大虾告诉我这个该怎么写，小弟万分感谢！！！。
[quote]对于 GetHashCode 存储过程
EXEC GetHashCode @appName, @appId OUTPUT
这个块只是外部调用 ，他的appName类型和 [dbo].[AspSessionprco] 存储过程中的 @appName char(280),
类型 没有影响吧[/quote]
没有影响，只要GetHashCode中的长度比AspSessionprco的长就不会有问题了。
[code="sql"]CREATE PROCEDURE GetHashCode
@input char(280),
@hash INT OUTPUT
AS
/*
This sproc is based on this C# hash function:
int GetHashCode(string s)
{
int hash = 5381;
int len = s.Length;
for (int i = 0; i < len; i++) {
int c = Convert.ToInt32(s[i]);
hash = ((hash << 5) + hash) ^ c;
}
return hash;
}
However, SQL 7 doesn't provide a 32-bit integer
type that allows rollover of bits, we have to
divide our 32bit integer into the upper and lower
16 bits to do our calculation.
*/
DECLARE @hi_16bit INT
DECLARE @lo_16bit INT
DECLARE @hi_t INT
DECLARE @lo_t INT
DECLARE @len INT
DECLARE @i INT
DECLARE @c INT
DECLARE @carry INT
SET @hi_16bit = 0
SET @lo_16bit = 5381
SET @len = DATALENGTH(@input)
SET @i = 1
WHILE (@i <= @len)
BEGIN
SET @c = ASCII(SUBSTRING(@input, @i, 1))
/* Formula:
hash = ((hash << 5) + hash) ^ c */
/* hash << 5 /
SET @hi_t = @hi_16bit * 32 / high 16bits << 5 /
SET @hi_t = @hi_t & 0xFFFF / zero out overflow */
SET @lo_t = @lo_16bit * 32 /* low 16bits << 5 */
SET @carry = @lo_16bit & 0x1F0000 /* move low 16bits carryover to hi 16bits /
SET @carry = @carry / 0x10000 / >> 16 /
SET @hi_t = @hi_t + @carry
SET @hi_t = @hi_t & 0xFFFF / zero out overflow */
/* + hash /
SET @lo_16bit = @lo_16bit + @lo_t
SET @hi_16bit = @hi_16bit + @hi_t + (@lo_16bit / 0x10000)
/ delay clearing the overflow */
/* ^c */
SET @lo_16bit = @lo_16bit ^ @c
/* Now clear the overflow bits */
SET @hi_16bit = @hi_16bit & 0xFFFF
SET @lo_16bit = @lo_16bit & 0xFFFF
SET @i = @i + 1
END
/* Do a sign extension of the hi-16bit if needed */
IF (@hi_16bit & 0x8000 <> 0)
SET @hi_16bit = 0xFFFF0000 | @hi_16bit
/* Merge hi and lo 16bit back together /
SET @hi_16bit = @hi_16bit * 0x10000 / << 16 */
SET @hash = @hi_16bit | @lo_16bit
RETURN 0
GO
[/code]
刚才没执行过，不好意思。现在执行过了，这个可以的。
[quote]我想知道为什么 @appName tAppName,
@appId int OUTPUT
的方式不能创建。我在网上看到的都是这样写的代码。 能帮我分析下吗。谢谢[/quote]
因为网上的，在执行这个之前，先执行了：
[code="sql"]
EXECUTE sp_addtype tAppName, 'VARCHAR(280)', 'NOT NULL'
GO
[/code]