Query question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Query question

Author

Topic

Pr0FiT
Starting Member

7 Posts

Posted - 2008-01-07 : 15:51:01

First off I am not very knowledgeable on sql queries so please forgive me if this is a stupid mistake. What I'm trying to do is insert new rows into a table that have a specific key (the key column in this case is named "contact_uuid" w/ a data type of binary(16)). Here is the query I'm using to do the insert:

INSERT INTO dbo.usp_contact (contact_uuid) VALUES (convert(binary(16),'0xFE7527485C73AC4787890D9FA138AA45'))

This completes with no errors. I then did a select query to make sure the insert was sucesfull, it returns a result but has the wrong id:

SELECT contact_uuid FROM usp_contact WHERE contact_uuid = (convert(binary(16),'0xFE7527485C73AC4787890D9FA138AA45'))

returns: 0x30784645373532373438354337334143
when I want it to return: 0xFE7527485C73AC4787890D9FA138AA45

I don't know if one or both of my queries is incorrect. Any advice?

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-01-07 : 16:03:44

create table #tmp (
a binary(16)
)

insert into #tmp
select 0xFE7527485C73AC4787890D9FA138AA45

select * from #tmp

drop table #tmp

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

Pr0FiT
Starting Member

7 Posts

Posted - 2008-01-07 : 16:53:45

Thanks but that doesn't quite accomplish what I'm looking for. These values have to be inserted into the usp_contact table (which I cannot drop).

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-01-07 : 17:22:41

By wrapping your binary with single quotes you have cast it as varchar data which you are then converting into binary(16), so you are no longer storing your original binary data but instead the binary equivalent to the string '0xFE7527485C73AC4787890D9FA138AA45'.

Try this statement:


INSERT INTO dbo.usp_contact (contact_uuid) VALUES (0xFE7527485C73AC4787890D9FA138AA45)

Pr0FiT
Starting Member

7 Posts

Posted - 2008-01-07 : 17:53:48

That did it! Thanks so much for you help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources