SQL Server - data types on a migration, SQL question

Asked By Janis

08-Mar-10 07:19 PM

I am creating a table in SQL that  I am migrating data to from an ODBC
datasource.  I have to convert the data types in the external source to SQL
Server data types.
The fields have dates in this format, 3/8/2010.  I am assuming a datetime
data type is correct?
Do I have to put quotes around dates or anything?

Another question is can you put pictures in SQL Server, for example, jpeg's?
Would you use the sql_variant data type?

I want to remove the "identity" property on a field.  It is not a key field.
It is a regular field.
I did it accidentally in SQL Server.
I try in the properties to change the key field property to false but the
property by just clicking on the object in sQL Server Mgmt studio.

thanks,

08-Mar-10 09:12 PM

Datetimes are stored internally as 2 4 byte integers and not as a character.
The tool or driver you use to import has to be able to read the source and
convert it to a datetime.  For images you should look at varbinary(max) or
the filestream dataypes.  You need to drop and re add the column to remove
an identity. This is usually done behind the scenes with SSMS by creating a
new table, copying the data, dropping the original and renaming the new one
to the old name.

--

Andrew J. Kelly   SQL MVP
Solid Quality Mentors

08-Mar-10 11:02 PM

That is weird I just altered the date fields to varchar for now.  I can
change it in sql server later.  The object explorer does not reflect the data
type change but if you look in properties it
is changed.  Why is that?