SQL nvarchar 不是可识别的游标选项
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13406930/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
nvarchar is not a recognized cursor option
提问by adino
I am running some sql that take the info from a temporary table and puts it into the permanent table. I got it from a step by step guide written 3 years ago, and the person who wrote it is long gone. it states to use this sql here.
我正在运行一些 sql，它从临时表中获取信息并将其放入永久表中。我是从 3 年前写的分步指南中得到的，写它的人早已不在了。它声明在此处使用此 sql。
declare @Password nvarchar(100);
set @Password ='rewards';
if not exists(select 1 from sys.openkeys where key_name = 'Sym_UserPassData'
and database_name = db_name()) OPEN SYMMETRIC KEY Sym_UserPassData DECRYPTION BY
CERTIFICATE UserPassTables with password='asbpass71509new';
INSERT INTO [User] (Username, [Password], AllowChange, ForceChange, FullName,
SalesRep, OpenLink, UserProfileID, LastUpdatedBy,UserEmail)
(SELECT Username,EncryptByKey(Key_GUID('Sym_UserPassData'),@Password),
AllowChange, ForceChange, FullName, SalesRep, [OpenLink ], UserProfileID,
LastUpdateBy, UserEmail FROM TempUsers)
and then after that it says the following
然后它说以下
If the password is unique for each row, take the set @Password = ‘Password'; off and replace the @Password with [Password].
如果每行的密码都是唯一的，则取set @Password = 'Password'; 关闭并将@Password 替换为 [Password]。
so at first I just changed the 2nd line so it said
所以起初我只是改变了第二行所以它说
declare @Password nvarchar(100);
set [Password]
...
But that gave me an error with the password column so then i changed it to:
但这给了我密码列的错误，所以我将其更改为：
declare [Password] nvarchar(100);
set [Password]
if not exists(select 1 from sys.openkeys where key_name = 'Sym_UserPassData'
and database_name = db_name()) OPEN SYMMETRIC KEY Sym_UserPassData DECRYPTION BY
CERTIFICATE UserPassTables with password='asbpass71509new';
INSERT INTO [User] (Username, [Password], AllowChange, ForceChange, FullName,
SalesRep, OpenLink, UserProfileID, LastUpdatedBy,UserEmail)
(SELECT Username,EncryptByKey(Key_GUID('Sym_UserPassData'),[Password]),
AllowChange, ForceChange, FullName, SalesRep, [OpenLink ], UserProfileID,
LastUpdateBy, UserEmail FROM TempUsers)
and that is what gave me the error:
这就是给我错误的原因：
nvarchar is not a recognized cursor option
Does anyone know what I am missing? If i can provide any other info I will do my best to do so.
有谁知道我错过了什么？如果我能提供任何其他信息，我会尽力提供。
Thank you to anyone who is able to help with this.
感谢任何能够帮助解决此问题的人。
回答by adino
You have to declare variables with the @sign before the names. So this is correct:
您必须@在名称前用符号声明变量。所以这是正确的：
declare @Password nvarchar(100);
set @Password ='rewards';
This is NOT correct:
这是不正确的：
declare [Password] nvarchar(100);
set [Password] ='rewards';
I think the problem is with your variable declaration. See this article: http://www.databasejournal.com/features/mssql/article.php/3087431/T-SQL-Programming-Part-1---Defining-Variables-and-IFELSE-logic.htm
我认为问题在于您的变量声明。见这篇文章：http: //www.databasejournal.com/features/mssql/article.php/3087431/T-SQL-Programming-Part-1---Defining-Variables-and-IFELSE-logic.htm
回答by paparazzo
If [Password] is a column in TempUsers it might mean this.
如果 [Password] 是 TempUsers 中的一列，则可能是这个意思。
if not exists(select 1 from sys.openkeys where key_name = 'Sym_UserPassData'
and database_name = db_name()) OPEN SYMMETRIC KEY Sym_UserPassData DECRYPTION BY
CERTIFICATE UserPassTables with password='asbpass71509new';
INSERT INTO [User] (Username, [Password], AllowChange, ForceChange, FullName,
SalesRep, OpenLink, UserProfileID, LastUpdatedBy,UserEmail)
(SELECT Username,EncryptByKey(Key_GUID('Sym_UserPassData'),[Password]),
AllowChange, ForceChange, FullName, SalesRep, [OpenLink ], UserProfileID,
LastUpdateBy, UserEmail FROM TempUsers)
