Results 1 to 5 of 5
  1. #1
    Join Date
    May 2009
    Posts
    7

    Unanswered: Postgres To SQL Server Conversion

    Hi there,

    Does anyone have experience in moving from Postgres to SQL Server?

    I have found a decent tool to convert the schema but I'm floored by the amount of time it is going to take to change the SQL front end syntax.

    e.g. things like string concatenation differences, "limit x" being "top x" etc

    Does such a clever tool or driver exist?

    Thanks for any replies.

    Kevin./

  2. #2
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    With pg functions that you use quite a bit, it may be worth your while to write equivalent SQL Server 'wrapper' function/SPs with the same interface parameters.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  3. #3
    Join Date
    May 2009
    Posts
    7
    Thanks, we're planning to replace the sequence functions with custom written SQL functions that return numbers from a params table - better than identity fields and should mean no code changes.

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by FlindersStation
    Thanks, we're planning to replace the sequence functions with custom written SQL functions that return numbers from a params table - better than identity fields and should mean no code changes.
    The problem with that approach is, that it's either not correct or not scalable (because you will need to lock the params table)

    Don't forget that the default locking handling in MS SQL Server is *very* different to the way Postgres works. If you want a similar behaviour where readers do not block writers you have to go for SQL Server 2005 (or later) and change the database to "snapshot isolation". I think that's the name, but I'm not 100% sure, never done a downgrade to SQL Server

  5. #5
    Join Date
    May 2009
    Posts
    7
    I'm planning to have something like a tablename column and a lastident column in the params table and have one row for each table where I need a sequence.

    It's not a very intensive data storing application so there won't be an issue with readers blocking writers.

    I'd say that was preferable to doing a "select max" before insert and worth a big time saver in rewriting the front end code - there are real commercial time pressures on this job

    If you have other suggestions then please feel free, I'm interested.

Posting Permissions

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