Format difference of data in sql_variant datatype.

srikmr

Hi,
I am having a column by name 'Description' in my table with the data type 'sql_variant'.
Our application is storing some data in that column, but it sometimes stores a Date value too.

In database1, the column value was having '2007/05/17 10:11:12:11'
In database2, the column value was having '2007-05-17 10:11:12:11'

I tried to run the SqlDataCompare with the two databases.
I expected that it shoud show show same, but it was showing as different.
It was specifiying that the difference is in my 'Description' column.

I guess there might be some option if the datatype were a DateTime, but in my case it is a Sql_variant.
Below are my questions:
1) How can i specify to make it consider both the data as same where the Dataype is sql_variant. (also if the datatype is DateTime. I just want to know about this too).
2) Could you also let me know to achieve the above requirement using your API too as i need this in my coding.

Thanks for your time.
Regards
Srikmr

Chris Spencer

Hi Srikmr

I have investigated this and I can only recreate your problem when I insert the data into the sql_variant column without a convert/cast.
INSERT INTO DateTimeInVariant (description) VALUES ('2007-05-17 10:11:12:11')
GO
Without the cast it seems that SQL Server and SQL Data Compare believe the base data-type of the sql_variant column to be nvarchar. Therefore the data is detected as being different

If a CONVERT is used...
INSERT INTO DateTimeInVariant (description) VALUES (CONVERT(DATETIME,'2007-05-17 10:11:12:11'))
GO
...the base data-type is detected as being datetime and the data is reported as identical.

SQL Data Compare should be able to compare DATETIME data of any format.

I will get back to you regarding point 2 and the API coding.

Best Regards
Chris

srikmr

Thanks Chris for your prompt response.
I shall be waiting for your your answers for my my other question (question2).
Thanks
Srikmr

srikmr

Here is the proble we are facing.

As i said in my earlier post we are having a sql_variant data type and it stores a DateTime value sometimes.

DATABASE 1 (DB1):

---

In table1 of DB1 we have few rows which have a date time value in this column (say the column name be DESCRIPTION which is of data type Sql_variant).

When i used the SELECT statement in Sql-Server Mgmt Studio i get the out put as below:
Query is: SELECT ID, DESCRIPTION from TABLE1
Result is like: 1, 2006-03-07 11:44:02.000

I can see that DESCRIPTION data is stored in the DB1 with a value of 2006-03-07 11:44:02.000

DATABASE2 (DB2):

---

In Table1 of DB2, we have a similar row.
I queried again for the data in Sql-Server Mgmt Studio, and i got the same result.
Query is: SELECT ID, DESCRIPTION from TABLE1
Result is like: 1, 2006-03-07 11:44:02.000

here too,
I can see that DESCRIPTION date is stored in the DB2 with a value of 2006-03-07 11:44:02.000

NOW I RUN THE SQLCompare against these two and get the result as they are not same.

I have tried it using the UI and also the toolkit.

Here is the output:
svValue 3/7/2006 11:44:02 AM <> 2006-03-07 11:44:02:000


The strange thing what i observed is:
1) The SqlDataCompare was showing the value from DB1 as
3/7/2006 11:44:02 AM instead of showing 2006-03-07 11:44:02.000

2) Surprisingly, The data from DB2 was showing as it is. i.e 2006-03-07 11:44:02.000 (which is as expected).

I wonder why SqlDataCompare is behaving in a different way while showing the data from DB1 and DB2.
If it were showing the same was as in DB1 then then its OK, but what i wonder is:
The same data is being shown differently from two similar rows of 2 databases.

Can you plese suggest a solution to overcome this problem. We need this to be solved very soon for our project.

Regards
Srikmr

Chris Spencer

Hi Srikmr

I think we need to find out the base types of the two cells that are being reported as different by SQL Data Compare. The quickest way to do this is to hover the mouse over the relevant cells in the Object Differences panel of the data compare UI. It should show a tooltip with the text 'sql_variant: datetime' or potentially 'sql_variant: nvarchar'.

Alternatively you can find the base type using the following t-sql (edited as necessary):

SELECT SQL_VARIANT_PROPERTY(DESCRIPTION,'BaseType') AS 'Base Type'
FROM TABLE1
WHERE DESCRIPTION = '2006-03-07 11:44:02.000'
-- WHERE ID = 1

If the base_type is datetime for both DB1 and DB2 then we will need to investigate some other ideas.

Regards
Chris