Linked server import problem- Convert FLOAT to Varchar or char or even to INT data type?
I have received a question via email asking for a solution, though solution is simple thought I can blog it.
We have a simple linked server connection to import data from a provider.When querying a comma delimited file, why does sql server try to interpret data types? How can I make all fields Varchar or Char?
We are using SQL Server 2005 and linked server provider is Product Name: Jet 4.0 and String format is text. The requirement is to parse the phone number field into 2 fields such as seperating area code and number values.
Now the problem is when we run a SELECT query against that Linked Server, SQL Server is trying to pull them in as FLOAT data type. We are unable to convert the FLOAT to varchar or char or even to INT data type. How to solve this issue.
The solution is simple for such data conversion from FLOAT to Varchar use STR function to convert and then extract the relevant values.