Sql-server – Field Conversions With Linked Servers

linked-serverpostgresqlsql-server-2008-r2

I have a postgres database linked to SQL Server. I have found you can use OpenQuery to query postgres from the Sql Server side, but one caveat I have found is that when creating views all fields are converted to char despite the actual data type from postgres. For example, if you look at my query below, userid is an int, and userlogindate & userlastlogindate are date fields in postgres but when the view is created in Sql Server if I expand the plus sign beside the view, and expand the plus sign beside columns, all of the columns are listed as char()?

What do I need to do in order for the Sql Server view to keep the same field type as what postgres has them set-up to be? Cast/Convert each one?

SELECT      *
FROM            OPENQUERY(LinkedServer, 
                     'Select 
                      userid As "userid"
                      ,userlogin As "userlogin"
                      ,userlogindate As "userlogindate"
                      ,userlastlogindate As "Lastlogindate"
                      From syscatalog')

Best Answer

I found the solution to this. Below is how I achieved such:

SELECT      
CAST(userid As Int) As userid
,CAST(userlogin As varchar(500)) As userlogin
,CAST(userlogindate as date) As userlogindate
,CAST(userlastlogindate as date) As lastlogindate
FROM OPENQUERY(LinkedServer, 
                 'Select 
                  userid As "userid"
                  ,userlogin As "userlogin"
                  ,userlogindate As "userlogindate"
                  ,userlastlogindate As "Lastlogindate"
                  From syscatalog')
Related Question