CODEWITHSUNDEEP
sqlt-sql
user1469499
user1469499

Reputation:

nvarchar is not a recognized cursor option

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.

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].

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.

Upvotes: 23

Views: 33014

Answers (2)

paparazzo
paparazzo

Reputation: 45096

If [Password] is a column in TempUsers it might mean this.

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)

Upvotes: 2

adino
adino

Reputation: 1330

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

Upvotes: 48

Related Questions