binary data

[Anastasia]

We have a dll that sends a hexadecimal data (const. length) to MS SQL Server database. It's declared as String in VB, the db column data type is binary.

Here is the SQL String that has been executed successfully in Query Analyzer:

"declare @MyHAX varchar(32)
select @MyHAX='0x323637453545433736314531343046374239 4545413443473230343544320000'
insert MyTABLE (MyCOLUMN)
values (convert (binary(32),@MyHAX))"

When I am trying to do the same thing in the insert stored procedure, I get an error message: "Disallowed implicit conversion from data type varchar to data type binary, table 'MyDB.dbo.MyTABLE', column 'MyCOLUMN'. Use the convert function to run this query."

Does anyone know how can I insert my binary data?

[Jim W]

I just tried it. Worked fine. Why don't you copy your stored procedure, or the relevant parts of it, to a post here. It may be that you don't have a convert statement quite right.

Btw, also include the query you use to call the procedure.


------------
Anastasia at 2/8/01 10:24:48 AM


We have a dll that sends a hexadecimal data (const. length) to MS SQL Server database. It's declared as String in VB, the db column data type is binary.

Here is the SQL String that has been executed successfully in Query Analyzer:

"declare @MyHAX varchar(32)
select @MyHAX='0x323637453545433736314531343046374239 4545413443473230343544320000'
insert MyTABLE (MyCOLUMN)
values (convert (binary(32),@MyHAX))"

When I am trying to do the same thing in the insert stored procedure, I get an error message: "Disallowed implicit conversion from data type varchar to data type binary, table 'MyDB.dbo.MyTABLE', column 'MyCOLUMN'. Use the convert function to run this query."

Does anyone know how can I insert my binary data?

[Anastasia]

Thank you, Jim
Here is the stored proc that I was able to compile:
"
CREATE proc i_Sessions
@SessionID binary(32),
@IPaddress char(15),
@UserID int=null,
@Accessed datetime=null,
@PageID int=null
as
--check if SessionID already exists in the table
if not exists(select * from Sessions where SessionID=@SessionID)
begin
--begin insert transaction
begin tran
declare @Error int, @RowsInserted int
insert Sessions
(IPaddress,
UserID,
Accessed,
PageID,
SessionID)
values (@IPAddress,
@UserID,
@Accessed,
@PageID,
@SessionID)

select @RowsInserted=@@Rowcount, @Error=@@Error
if @RowsInserted=0 or @Error!=0
begin
rollback tran
raiserror ('Error Occurred. No rows were inserted',16,1)
return -101
end
commit tran
end
"

here is the SQL string to call the proc from our dll:
"exec i_Sessions @SessionID='5D202D37EC91C82725BFCBAEAAEBX' , @IPAddress='123.12.12'"

Here is the error message:
"Implicit conversion from data type varchar to binary is not allowed. Use the CONVERT function to run this query."

[Anastasia]

Here is how I tried to fix it:
"CREATE proc i_Sessions
@SessionID varchar(32),
.....................
.....................
insert Sessions
(IPaddress,
UserID,
Accessed,
PageID,
SessionID)
values (@IPAddress,
@UserID,
@Accessed,
@PageID,
convert(binary,@SessionID))
......................"

Here is the error message:
"Disallowed implicit conversion from data type varchar to data type varbinary, table 'webdata.dbo.Sessions', column 'SessionID'. Use the CONVERT function to run this query."



------------
Jim W at 2/8/01 10:50:29 AM

I just tried it. Worked fine. Why don't you copy your stored procedure, or the relevant parts of it, to a post here. It may be that you don't have a convert statement quite right.

Btw, also include the query you use to call the procedure.


------------
Anastasia at 2/8/01 10:24:48 AM


We have a dll that sends a hexadecimal data (const. length) to MS SQL Server database. It's declared as String in VB, the db column data type is binary.

Here is the SQL String that has been executed successfully in Query Analyzer:

"declare @MyHAX varchar(32)
select @MyHAX='0x323637453545433736314531343046374239 4545413443473230343544320000'
insert MyTABLE (MyCOLUMN)
values (convert (binary(32),@MyHAX))"

When I am trying to do the same thing in the insert stored procedure, I get an error message: "Disallowed implicit conversion from data type varchar to data type binary, table 'MyDB.dbo.MyTABLE', column 'MyCOLUMN'. Use the convert function to run this query."

Does anyone know how can I insert my binary data?

[Anastasia]

Hmmm... The compiled version of the proc works if the SQL string looks like that:
"exec i_Sessions 0x323637453545433736314531343046374239454541344347 3230343544320000, '127.0.0.1',null,null,null"

Is there any way to specify the parameter name in the SQL string though?

[Jim Zhong]

Use sp_executesql which let you embed the variable in the string.


Jim


------------
Anastasia at 2/8/01 11:36:47 AM

Hmmm... The compiled version of the proc works if the SQL string looks like that:
"exec i_Sessions 0x323637453545433736314531343046374239454541344347 3230343544320000, '127.0.0.1',null,null,null"

Is there any way to specify the parameter name in the SQL string though?