开发者

Making Microsoft Access Tables in SQL Server Identical on different setups

I have just noticed that one of the view开发者_StackOverflow中文版s 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


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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜