Making Microsoft Access Tables in SQL Server Identical on different setups

Question

Hello all,

I have just noticed that one of the views I create from Microsoft Access in SQL Server via a linked server is interpreted differently in different machines/setups etc.

Example:

EXEC sp_addlinkedserver acc465tghv, 'OLE DB Provider for Jet', 'Microsoft.Jet.OLEDB.4.0', 'C:\tester.mdb'
EXEC sp_addlinkedsrvlogin acc465tghv, TRUE
GO
CREATE VIEW TI265 AS SELECT * FROM acc465tghv...TI0CE

When I open the access database in a different machine/computer, the date is formatted differently and I am sure other data types may be formatted/interpreted different when you open the same database (.mdb file) in different machines/computers with different versions of Access and setups etc.

The question is, how can I create a view or a table that shows the access table exactly the same in SQL server?

What options or work arounds are there?

Thanks all

Answer 1

This is pretty standard SQL Server behaviour, based on some functions being deterministic and others non-deterministic. You can read up a bit more about the various functions here.

You can also use SET DATEFORMAT to specify the format in which strings are interpreted by SQL Server.

However, in this scenario, it's pretty likely that the client machine is the thing that is formatting the date differently, based on it's Locale.