将 SQL Server 2008 游标变量转换为 SQL Azure
我正在尝试将 NetSqlAzMan 的数据库移植到 Azure。我遇到了一些存储过程的问题。
SET @member_cur = CURSOR STATIC FORWARD_ONLY FOR SELECT * FROM @RESULT
OPEN @member_cur
结果出现错误消息:
消息 16948,级别 16,状态 4
程序 netsqlazman_GetApplicationGroupSidMembers, 118路
变量“@member_cur”是 不是游标变量,但它被使用 在光标变量所在的地方 预计。
存储过程脚本是通过以 SQLAzure 格式导出空 NetSQLAzMan 数据库来创建的。在 SQL Azure 中处理游标变量有什么技巧吗?我没有看到太多这方面的文档。
如果有帮助的话,这是存储过程。请原谅啰嗦。该错误引用了存储过程的底部。
CREATE PROCEDURE [dbo].[netsqlazman_GetApplicationGroupSidMembers]
@ISMEMBER [bit],
@GROUPOBJECTSID [varbinary](85),
@NETSQLAZMANMODE [bit],
@LDAPPATH [nvarchar](4000),
@member_cur [int] OUTPUT
WITH EXECUTE AS CALLER
AS
DECLARE @RESULT TABLE (objectSid VARBINARY(85))
DECLARE @GROUPID INT
DECLARE @GROUPTYPE TINYINT
DECLARE @LDAPQUERY nvarchar(4000)
DECLARE @sub_members_cur CURSOR
DECLARE @OBJECTSID VARBINARY(85)
SELECT @GROUPID = ApplicationGroupId, @GROUPTYPE = GroupType, @LDAPQUERY = LDapQuery FROM [netsqlazman_ApplicationGroupsTable] WHERE objectSid = @GROUPOBJECTSID
IF @GROUPTYPE = 0 -- BASIC
BEGIN
--memo: WhereDefined can be:0 - Store; 1 - Application; 2 - LDAP; 3 - Local; 4 - Database
-- Windows SIDs
INSERT INTO @RESULT (objectSid)
SELECT objectSid
FROM dbo.[netsqlazman_ApplicationGroupMembersTable]
WHERE
ApplicationGroupId = @GROUPID AND IsMember = @ISMEMBER AND
((@NETSQLAZMANMODE = 0 AND (WhereDefined = 2 OR WhereDefined = 4)) OR (@NETSQLAZMANMODE = 1 AND WhereDefined BETWEEN 2 AND 4))
-- Store Groups Members
DECLARE @MemberObjectSid VARBINARY(85)
DECLARE @MemberType bit
DECLARE @NotMemberType bit
DECLARE nested_Store_groups_cur CURSOR LOCAL FAST_FORWARD FOR
SELECT objectSid, IsMember FROM dbo.[netsqlazman_ApplicationGroupMembersTable] WHERE ApplicationGroupId = @GROUPID AND WhereDefined = 0
OPEN nested_Store_groups_cur
FETCH NEXT FROM nested_Store_groups_cur INTO @MemberObjectSid, @MemberType
WHILE @@FETCH_STATUS = 0
BEGIN
-- recursive call
IF @ISMEMBER = 1
BEGIN
IF @MemberType = 0
SET @NotMemberType = 0
ELSE
SET @NotMemberType = 1
END
ELSE
BEGIN
IF @MemberType = 0
SET @NotMemberType = 1
ELSE
SET @NotMemberType = 0
END
EXEC dbo.[netsqlazman_GetStoreGroupSidMembers] @NotMemberType, @MemberObjectSid, @NETSQLAZMANMODE, @LDAPPATH, @sub_members_cur OUTPUT
FETCH NEXT FROM @sub_members_cur INTO @OBJECTSID
WHILE @@FETCH_STATUS=0
BEGIN
INSERT INTO @RESULT VALUES (@OBJECTSID)
FETCH NEXT FROM @sub_members_cur INTO @OBJECTSID
END
CLOSE @sub_members_cur
DEALLOCATE @sub_members_cur
FETCH NEXT FROM nested_Store_groups_cur INTO @MemberObjectSid, @MemberType
END
CLOSE nested_Store_groups_cur
DEALLOCATE nested_Store_groups_cur
-- Application Groups Members
DECLARE nested_Application_groups_cur CURSOR LOCAL FAST_FORWARD FOR
SELECT objectSid, IsMember FROM dbo.[netsqlazman_ApplicationGroupMembersTable] WHERE ApplicationGroupId = @GROUPID AND WhereDefined = 1
OPEN nested_Application_groups_cur
FETCH NEXT FROM nested_Application_groups_cur INTO @MemberObjectSid, @MemberType
WHILE @@FETCH_STATUS = 0
BEGIN
-- recursive call
IF @ISMEMBER = 1
BEGIN
IF @MemberType = 0
SET @NotMemberType = 0
ELSE
SET @NotMemberType = 1
END
ELSE
BEGIN
IF @MemberType = 0
SET @NotMemberType = 1
ELSE
SET @NotMemberType = 0
END
EXEC dbo.[netsqlazman_GetApplicationGroupSidMembers] @NotMemberType, @MemberObjectSid, @NETSQLAZMANMODE, @LDAPPATH, @sub_members_cur OUTPUT
FETCH NEXT FROM @sub_members_cur INTO @OBJECTSID
WHILE @@FETCH_STATUS=0
BEGIN
INSERT INTO @RESULT VALUES (@OBJECTSID)
FETCH NEXT FROM @sub_members_cur INTO @OBJECTSID
END
CLOSE @sub_members_cur
DEALLOCATE @sub_members_cur
FETCH NEXT FROM nested_Application_groups_cur INTO @MemberObjectSid, @MemberType
END
CLOSE nested_Application_groups_cur
DEALLOCATE nested_Application_groups_cur
END
ELSE IF @GROUPTYPE = 1 AND @ISMEMBER = 1 -- LDAP QUERY
BEGIN
EXEC dbo.[netsqlazman_ExecuteLDAPQuery] @LDAPPATH, @LDAPQUERY, @sub_members_cur OUTPUT
FETCH NEXT FROM @sub_members_cur INTO @OBJECTSID
WHILE @@FETCH_STATUS=0
BEGIN
INSERT INTO @RESULT (objectSid) VALUES (@OBJECTSID)
FETCH NEXT FROM @sub_members_cur INTO @OBJECTSID
END
CLOSE @sub_members_cur
DEALLOCATE @sub_members_cur
END
SET @member_cur = CURSOR STATIC FORWARD_ONLY FOR SELECT * FROM @RESULT
OPEN @member_cur
GO
I'm attempting to port the database for NetSqlAzMan to Azure. I'm running into a problem with a few of the stored procedures.
SET @member_cur = CURSOR STATIC FORWARD_ONLY FOR SELECT * FROM @RESULT
OPEN @member_cur
results in error message:
Msg 16948, Level 16, State 4
Procedure
netsqlazman_GetApplicationGroupSidMembers,
Line 118
The variable '@member_cur' is
not a cursor variable, but it is used
in a place where a cursor variable is
expected.
The store procedure script was created by exporting an empty NetSQLAzMan database in SQLAzure format. Any tips for handling cursor variables in SQL Azure? I don't see much for documentation on this.
Here is the stored procedure if that helps. Please pardon the verbosity. The error references the bottom of the stored procedure.
CREATE PROCEDURE [dbo].[netsqlazman_GetApplicationGroupSidMembers]
@ISMEMBER [bit],
@GROUPOBJECTSID [varbinary](85),
@NETSQLAZMANMODE [bit],
@LDAPPATH [nvarchar](4000),
@member_cur [int] OUTPUT
WITH EXECUTE AS CALLER
AS
DECLARE @RESULT TABLE (objectSid VARBINARY(85))
DECLARE @GROUPID INT
DECLARE @GROUPTYPE TINYINT
DECLARE @LDAPQUERY nvarchar(4000)
DECLARE @sub_members_cur CURSOR
DECLARE @OBJECTSID VARBINARY(85)
SELECT @GROUPID = ApplicationGroupId, @GROUPTYPE = GroupType, @LDAPQUERY = LDapQuery FROM [netsqlazman_ApplicationGroupsTable] WHERE objectSid = @GROUPOBJECTSID
IF @GROUPTYPE = 0 -- BASIC
BEGIN
--memo: WhereDefined can be:0 - Store; 1 - Application; 2 - LDAP; 3 - Local; 4 - Database
-- Windows SIDs
INSERT INTO @RESULT (objectSid)
SELECT objectSid
FROM dbo.[netsqlazman_ApplicationGroupMembersTable]
WHERE
ApplicationGroupId = @GROUPID AND IsMember = @ISMEMBER AND
((@NETSQLAZMANMODE = 0 AND (WhereDefined = 2 OR WhereDefined = 4)) OR (@NETSQLAZMANMODE = 1 AND WhereDefined BETWEEN 2 AND 4))
-- Store Groups Members
DECLARE @MemberObjectSid VARBINARY(85)
DECLARE @MemberType bit
DECLARE @NotMemberType bit
DECLARE nested_Store_groups_cur CURSOR LOCAL FAST_FORWARD FOR
SELECT objectSid, IsMember FROM dbo.[netsqlazman_ApplicationGroupMembersTable] WHERE ApplicationGroupId = @GROUPID AND WhereDefined = 0
OPEN nested_Store_groups_cur
FETCH NEXT FROM nested_Store_groups_cur INTO @MemberObjectSid, @MemberType
WHILE @@FETCH_STATUS = 0
BEGIN
-- recursive call
IF @ISMEMBER = 1
BEGIN
IF @MemberType = 0
SET @NotMemberType = 0
ELSE
SET @NotMemberType = 1
END
ELSE
BEGIN
IF @MemberType = 0
SET @NotMemberType = 1
ELSE
SET @NotMemberType = 0
END
EXEC dbo.[netsqlazman_GetStoreGroupSidMembers] @NotMemberType, @MemberObjectSid, @NETSQLAZMANMODE, @LDAPPATH, @sub_members_cur OUTPUT
FETCH NEXT FROM @sub_members_cur INTO @OBJECTSID
WHILE @@FETCH_STATUS=0
BEGIN
INSERT INTO @RESULT VALUES (@OBJECTSID)
FETCH NEXT FROM @sub_members_cur INTO @OBJECTSID
END
CLOSE @sub_members_cur
DEALLOCATE @sub_members_cur
FETCH NEXT FROM nested_Store_groups_cur INTO @MemberObjectSid, @MemberType
END
CLOSE nested_Store_groups_cur
DEALLOCATE nested_Store_groups_cur
-- Application Groups Members
DECLARE nested_Application_groups_cur CURSOR LOCAL FAST_FORWARD FOR
SELECT objectSid, IsMember FROM dbo.[netsqlazman_ApplicationGroupMembersTable] WHERE ApplicationGroupId = @GROUPID AND WhereDefined = 1
OPEN nested_Application_groups_cur
FETCH NEXT FROM nested_Application_groups_cur INTO @MemberObjectSid, @MemberType
WHILE @@FETCH_STATUS = 0
BEGIN
-- recursive call
IF @ISMEMBER = 1
BEGIN
IF @MemberType = 0
SET @NotMemberType = 0
ELSE
SET @NotMemberType = 1
END
ELSE
BEGIN
IF @MemberType = 0
SET @NotMemberType = 1
ELSE
SET @NotMemberType = 0
END
EXEC dbo.[netsqlazman_GetApplicationGroupSidMembers] @NotMemberType, @MemberObjectSid, @NETSQLAZMANMODE, @LDAPPATH, @sub_members_cur OUTPUT
FETCH NEXT FROM @sub_members_cur INTO @OBJECTSID
WHILE @@FETCH_STATUS=0
BEGIN
INSERT INTO @RESULT VALUES (@OBJECTSID)
FETCH NEXT FROM @sub_members_cur INTO @OBJECTSID
END
CLOSE @sub_members_cur
DEALLOCATE @sub_members_cur
FETCH NEXT FROM nested_Application_groups_cur INTO @MemberObjectSid, @MemberType
END
CLOSE nested_Application_groups_cur
DEALLOCATE nested_Application_groups_cur
END
ELSE IF @GROUPTYPE = 1 AND @ISMEMBER = 1 -- LDAP QUERY
BEGIN
EXEC dbo.[netsqlazman_ExecuteLDAPQuery] @LDAPPATH, @LDAPQUERY, @sub_members_cur OUTPUT
FETCH NEXT FROM @sub_members_cur INTO @OBJECTSID
WHILE @@FETCH_STATUS=0
BEGIN
INSERT INTO @RESULT (objectSid) VALUES (@OBJECTSID)
FETCH NEXT FROM @sub_members_cur INTO @OBJECTSID
END
CLOSE @sub_members_cur
DEALLOCATE @sub_members_cur
END
SET @member_cur = CURSOR STATIC FORWARD_ONLY FOR SELECT * FROM @RESULT
OPEN @member_cur
GO
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
我认为这个问题并不是与游标有关,而是与输出变量的声明有关。如果查看存储过程的开头,您会看到以下内容:
其中
@member_cur
被定义为整数。然后,您尝试将其分配为具有游标的值,并且它正确地抱怨。令我惊讶的是,这是从非 Azure SQL Server 生成的。无论哪种方式,SQL Azure 似乎都不支持此功能,因此要么更改输出参数的类型,要么在不同的变量中打开游标并将 @member_cur 指定为该值。I don't think this problem is with cursors so much but more with the declaration of the output variable. If you look at the start of the stored procedure you have this:
Where
@member_cur
is defined as being an integer. You're then trying to assign it to have the value of a cursor and it is rightly complaining. What I'm surprised about is that this is what was generated from a non-Azure SQL Server. Either way it looks like SQL Azure doesn't support this, so either change the type of the output parameter, or open your cursor in a different variable and assign @member_cur to be that value.