CODEWITHSUNDEEP
sqlsql-server-2008-r2
TanmoyDB
TanmoyDB

Reputation: 89

Why am i getting this error when am creating a table?

I am creating a new table, with various data type, one them in binary(n). When I execute the statement, I end up with the following

Implicit conversion from data type varchar to binary is not allowed, use CONVERT function.

My question: why do I get this error, when there is no data to convert, I am just creating a table?

Create Table EMP.DETAILS
       (
       ID INT NOT NULL,
       TYPE INT,
       Created datetime2,
       Key_No varchar(5),
       Batch_IN INT,
       UN_ID BINARY(1) not null default '',
       Source INT,
       SITE CHAR(1)
       )

When I execute the above statement, I get the error mentioned above, But it directs me to the CREATE TABLE line of the code.

Upvotes: 0

Views: 67

Answers (1)

valverij
valverij

Reputation: 4941

The error says "Implicit conversion from data type varchar to binary is not allowed". You are trying to assign a default varchar character to a binary here: 

UN_ID BINARY(1) not null default ''

If you absolutely need a default value for a binary column, you can set it directly as something like 0x00:

UN_ID BINARY(1) not null default 0x00

That will basically set your default to 0. You can also set it to an integer value:

UN_ID BINARY(1) not null default 0

And finally, if you absolutely need to to be an empty string, you can find the binary representation of '' with the following SELECT:

SELECT CONVERT(binary, '')

I'm pretty sure it's just 0, though.

Depending on what that column is being used for, though, it might be better suited as a tinyint, char(1), or something similar, rather than a binary(1).

Upvotes: 3

Related Questions