SQL Server 2008 R2: Query MS Access from SQL Server

Question

Want to retrieve records from Access through linked server in SQL Server and need to convert/cast the column with VARCHAR for some constraint.

My attempt:

SELECT Cola, Colb
FROM MSAccess_LinkedServer...TableName  
WHERE CAST([Cola] AS VARCHAR) = 'A123'

Unable to get the result from above query.

But when I remove CAST then I will get the result, but I want to know how to put the cast/convert.

Please try to provide a minimal reproducible example. What do you mean with Unable to get the result? Are there any errors? — Erik A
– Erik A, Commented May 8, 2018 at 8:28
There is no error, just getting column name's but no records. — MAK
– MAK, Commented May 8, 2018 at 9:32

Gustav · Accepted Answer · 2018-05-08 08:12:53Z

1

No casting should be needed for text. How about simply:

WHERE [Cola] = 'A123'

answered May 8, 2018 at 8:12

Gustav

56.3k7 gold badges33 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Columns in the WHERE clause are not fixed and so values, we need to convert them.

Field names are fixed. If you wish to modify which fields are used in a Where clause, you must rewrite the SQL.