August 7, 2008 at 6:48 am
Hello Experts,
i just want to create a stored procedure can add new Employee,
which take 4 Parameters two of them encrypted by asymmetric key So :
Create PROCEDURE [dbo].[spAddEmp](
@Fname varchar(50),
@Lname varchar(50),
@Uname varchar(max),
@Pword varchar(max),
@GroID int,
@Lslry int,
@IsAdmin bit)
AS
BEGIN
SET NOCOUNT ON;
insert into Emp_Inf (Emp_Fname,Emp_Lname,Emp_Uname,Emp_Pword,
Gr_ID,Emp_Lslry,Is_Admin)
values (@Fname,@Lname,
EncryptByAsymKey(AsymKey_Id('AsymKey'),@Uname)),
EncryptByAsymKey(AsymKey_Id('AsymKey'),@Pword)),
@GroID,@Lslry,@IsAdmin)
END
First, when i write this insert statement as CommandText of SqlCommand and in Visual Studio 2005 by Ado it works and no Errors But when i decide to make this insert statement in A stored procedure ...... Strange Things happened (look below to know) :
when i run that query in MASTER DataBase it Created Successfully But
when i run that query in my dataBase (Attend_Sys) an error appear :
"Implicit conversion from data type varchar to varbinary is not allowed. Use the CONVERT function to run this query"
So I make that :
Create PROCEDURE [dbo].[spAddEmp](
@Fname varchar(50),
@Lname varchar(50),
@Uname varchar(max),
@Pword varchar(max),
@GroID int,
@Lslry int,
@IsAdmin bit)
AS
BEGIN
SET NOCOUNT ON;
insert into Emp_Inf (Emp_Fname,Emp_Lname,Emp_Uname,Emp_Pword,
Gr_ID,Emp_Lslry,Is_Admin)
values (@Fname,@Lname,
Convert(varchar,EncryptByAsymKey(AsymKey_Id('AsymKey'),@Uname)),
Convert(varchar,EncryptByAsymKey(AsymKey_Id('AsymKey'),@Pword)),
@GroID,@Lslry,@IsAdmin)
END
But the Same Error
SO : First why this Error , Second Why it works in MASTER and didn't work in my DataBase
YoU CaN't LoSe WhAt YoU NeVeR HaD;)
August 7, 2008 at 6:55 am
Convert(varchar
1) In the master database the column type may be correct (varbinary).
2) You are converting to varchar. You should convert to varbinary.
BTW master database should not be used to store user tables.
August 7, 2008 at 9:19 am
I'm Sure that the column type in master DataBase is Varchar(max) and it works.
and after i convert to varbinary like that :
Create PROCEDURE [dbo].[spAddEmp](
@Fname varchar(50),
@Lname varchar(50),
@Uname varchar(max),
@Pword varchar(max),
@GroID int,
@Lslry int,
@IsAdmin bit)
AS
BEGIN
SET NOCOUNT ON;
insert into Emp_Inf (Emp_Fname,Emp_Lname,Emp_Uname,Emp_Pword,
Gr_ID,Emp_Lslry,Is_Admin)
values (@Fname,@Lname,
Convert(varbinary,EncryptByAsymKey(AsymKey_Id('AsymKey'),@Uname)),
Convert(varbinary,EncryptByAsymKey(AsymKey_Id('AsymKey'),@Pword)),
@GroID,@Lslry,@IsAdmin)
END
when i run this query i figuer the same Error
note that the column type in the table itself is varchar(max) so what shall i do ??????????????
is it true to change the type of the parameters (@Uname and Pword ) to varbinary but i think this will make error because i wiil pass varchars parameters to the stored procedure when i call it.
and i want to know why the master database make it works?????????
YoU CaN't LoSe WhAt YoU NeVeR HaD;)
August 9, 2008 at 9:29 pm
and i want to know why the master database make it works?????????
Because Emp_Inf table is defined differently in MASTER than is is in your other database(s).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 10, 2008 at 5:38 am
The table is not exist in Master Tables, and if it exists what the structure of it to make the query works???
YoU CaN't LoSe WhAt YoU NeVeR HaD;)
August 10, 2008 at 6:21 am
Mscode_Pro (8/10/2008)
The table is not exist in Master Tables, and if it exists what the structure of it to make the query works???
It must exist in MASTER or else you would get an error(warning) when you try to to Create the stored procedure there.
And the difference would be that either Emp_Uname or Emp_Pword, or both are defined as Varbinary there, but not in your database.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 11, 2008 at 5:53 am
hi i execute your sp in my server first of all it gave syntax error and moreover it didnt throw any error either on Master or anu user database
---------corrected script:
Create PROCEDURE [dbo].[spAddEmp](
@Fname varchar(50),
@Lname varchar(50),
@Uname varchar(max),
@Pword varchar(max),
@GroID int,
@Lslry int,
@IsAdmin bit)
AS
BEGIN
SET NOCOUNT ON;
insert into Emp_Inf (Emp_Fname,Emp_Lname,Emp_Uname,Emp_Pword,Gr_ID,Emp_Lslry,Is_Admin)
values (@Fname,@Lname,EncryptByAsymKey(AsymKey_Id('AsymKey'),@Uname),EncryptByAsymKey(AsymKey_Id('AsymKey'),@Pword), @GroID,@Lslry,@IsAdmin)
END
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
August 11, 2008 at 6:26 am
it works man How Come?????
i did the same before but it didn't work and now it works!!!!!!!
yes the syntax error becaue i forget to delete the second paranthesis of the convert Method but before that i did what you exactly did!!!!!!!!!!!!
at all thank you my friend 🙂 and thanks to any one try to solve this problem without trying it;)
YoU CaN't LoSe WhAt YoU NeVeR HaD;)
August 11, 2008 at 6:30 am
it was pleasure to give you solution...well where do you work ,,are you working as DBA ......
i am working as DBA ...in india
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
August 11, 2008 at 6:34 am
no my friend i`m a C# programmer not DBA, if you don`t mind add me in your Tech network in yahoo or any thing as "mscode_pro@yahoo.com"
YoU CaN't LoSe WhAt YoU NeVeR HaD;)
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply