Sql-server – Data type and Operator type Errors

sql server

i am following learn sql the hard way tutorial, in tutorial tutor suggest to use sqllite 3 but im using MSSQL 2008 and i have a problem, tutor ask from me to find something using AND or OR operators, her is the query i wrote;

select * from pet where name like 'Flu%' and age = 1000

but i receive:

Msg 206, Level 16, State 2, Line 13 Operand type clash: text is
incompatible with smallint

i try this one

select * from pet where name like 'Flu%' and age = '1000'

i receive :

Msg 402, Level 16, State 1, Line 13 The data types text and varchar
are incompatible in the equal to operator.

and when i convert age to varchar it works;

select * from pet where name like 'Flu%' and convert (varchar, age) = 1000
select * from pet where name like 'Flu%' and convert (varchar, age) = '1000'

it returns the correct answer

Here is the table design (as tutor wants);
id=int, name=text, breed=text, age=text, dead=int

why it doesnt work before converting to varchar?

Looking forward to your reply.

Kinds.

Best Answer

You got problem because sqllite and sql server datatype are different.

In the specific, you use a text datatype. This is bad, in sql server, for many reasons:

Text will be removed in future versions of sql server: https://msdn.microsoft.com/it-it/library/ms187993.aspx
You cannot compare with the equal operator
Always choose appropriate data type. Store a number in a text field will lead you to problems.

So, I suggest you to build your table with appropriate datatypes. In your case name must be a varchar and age an integer (if it is ok).

Also you must not learn starting on a book for a specific rdbms and use another one, or you will get a lot of problems like this.

Related Solutions

Sql-server – Query to convert the count to million or thousands

This type of formatting is generally best done in your application if possible.

The problem is that the case expression returns a result based on the highest datatype precedence of any branch.

So you would need to cast the final COUNT branch of your CASE to VARCHAR too as int has higher precedence than varchar.

Also you should probably add year into your order by except if you actually want to order Jan 2014 and Jan 2015 together followed by Feb and so on.

Though I might well be minded to take that concatenated string out of the GROUP BY too and rewrite it (Along the way fixing a couple of other issues with integer division and inconsistent boundary conditions) as.

WITH T(mmmyyyy, FORMATTED_COUNT, Yr, Mnth)
     AS (SELECT DATENAME(MONTH, MIN(CREATEDTS)) + ', ' + DATENAME(YEAR, MIN(CREATEDTS)) AS Month,
                CASE
                  WHEN COUNT(*) BETWEEN 1000 AND 999999
                    THEN ( CONVERT (VARCHAR(10), ( CAST(ROUND(COUNT(*) / 1000.0, 1) AS NUMERIC(4, 1)) )) ) + 'k'
                  WHEN COUNT(*) >= 1000000
                    THEN ( CONVERT (VARCHAR(10), ( CAST(ROUND(COUNT(*) / 1000000.0, 2) AS NUMERIC(6, 2)) )) ) + 'm'
                  ELSE CONVERT (VARCHAR(10), COUNT(*))
                END,
                YEAR(CREATEDTS),
                MONTH(CREATEDTS)
         FROM   USAGEDATA
         GROUP  BY YEAR(CREATEDTS),
                   MONTH(CREATEDTS))
SELECT mmmyyyy,
       FORMATTED_COUNT
FROM   T
ORDER  BY Yr,
          Mnth;

Sql-server – Is it possible to bulk insert data into a table that has columns encrypted with Always Encrypted

Bulk Insert operations in the manner you are describing are not supported for encrypted column via SSMS.

Please refer to this (https://blogs.msdn.microsoft.com/sqlsecurity/2015/07/28/encrypting-existing-data-with-always-encrypted/) article to migrate your existing data to Always Encrypted

Also, please note that doing bulk inserts through a C# (.NET 4.6.1+ client) app is supported.

You can do this in c# using SqlBulkCopy specifically using SqlBulkCopy.WriteToServer(IDataReader) Method. I am assuming you are trying to load data from csv file to a table with encrypted column (say encryptedTable). I would do the following

Create a new table (say unencryptedTable, for security purposes, you might consider creating this table in a local sql server instance) with the same schema without any column encryption.
Load the csv data into unencryptedTable, using the method that you described in the question
Do select * from unencryptedTable to load the data in a SqlDataReader then use SqlBulkCopy to load it to the encryptedTable using SqlBulkCopy.WriteToServer(IDataReader) Method

If you have additional questions regarding this, please post questions in the comment section and I will try my best to address them :)

Best Answer

Related Solutions

Sql-server – Query to convert the count to million or thousands

Sql-server – Is it possible to bulk insert data into a table that has columns encrypted with Always Encrypted

Related Question