Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2012
    Posts
    187

    Unanswered: Views From Linked Server Postgres

    I am running MS SQL Server 2008 and have a linked server connection to postgres, I use OpenQuery() to query and return my results to create the view. The only issue I have is that all fields in the view are created as char() as opposed to keeping there source field type. Is there a way I can alter my create view statement so that the fields in my view keep the same data type in the view?

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,794
    Provided Answers: 11
    You could convert the datatypes on the SQL Server side:
    Code:
    select convert(int, id) as ID,
      convert(datetime, createdate) as createdate
    from openquery (postgresserver, 'select query)

  3. #3
    Join Date
    Feb 2012
    Posts
    187
    Quote Originally Posted by MCrowley View Post
    You could convert the datatypes on the SQL Server side:
    Code:
    select convert(int, id) as ID,
      convert(datetime, createdate) as createdate
    from openquery (postgresserver, 'select query)
    Thank you for that insight. I have tried this syntax, but I get an error of
    Code:
    The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
    Here is example syntax where I have verified that the field is an int and a date in postgres

    Code:
    select cast(replicaNum as Int) as RN, cast(shipdate As datetime) As Shipdate
    from openquery (postgresserver, 'select replicaNum, shipdate from replicasales')

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,794
    Provided Answers: 11
    Well, what is the out of range value? Does Postgres allow dates before 1760? Or dates like 0000-00-00? What should these dates look like in SQL Server?

  5. #5
    Join Date
    Feb 2012
    Posts
    187
    I think I see what the culprit is. One of the dates is set as "0212-07-06" which would be before 1760, how can I account for any errors like such in SQL Server?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •