Re: Migrating from MS SQL server - Mailing list pgsql-general
From | Alex Bolenok |
---|---|
Subject | Re: Migrating from MS SQL server |
Date | |
Msg-id | 008201bff0c8$fd0ce770$df02a8c0@artey.ru Whole thread Raw |
In response to | Migrating from MS SQL server (Andreas Tille <tillea@rki.de>) |
Responses |
Re: Migrating from MS SQL server
Re: Migrating from MS SQL server |
List | pgsql-general |
> Thanks for your hint. I tried hardly to follow your suggestion but really > failed to set up the DSN right. (It might be that I use not the right > terms in the following because I use a German Win-NT :-(.) > When I tried to activate a subscriber for the distributor and publisher > and try to install a "New Subscriber" I get a list of 5 possibilities: > > FoodMart (MS Access-Driver) > LocalServer (SQL Server) > MQUIS (SQL Server) > Visual FoxPro Database > Visual FoxPro Tables > > I used the ODBC-Datasource-Administrator to insert the PostgreSQL-ODBC > driver but I couldn't manage it to be included in the list above. (In fact > it worked for MS-Access so I think the ODBC driver works in general because > I was able to read and write into the PostgreSQL tables with Access.) > What kind of DSN I have to configure which way. There are > User-DSN (in German Benutzer-DSN) > System-DSN > File-DSN (in German Datei-DSN) > which is the right one (I configured all of them for read/write access > hopefully) and how to do the exact configuration. You should use System DSN (so that SQL Server, that runs under either LOCALSYSTEM or .\SQLExecutive account, can see it). Add a DSN, then configure a Subscriber (use ODBC subscriber). It must appear in your subscribers list. (I cannot repoduce all the actions needed, because I have no MS SQL Server installed handy, but it _did_ work on my Windows 2000 Server / MS SQL Server 7.0 (full version) / PostgreSQL 6.5.4 / RedHat 6.1 configuration). If you fail to set up an ODBC Subscriber, you may still try to set up an OLE DB Subsriber. First create a linked server (use OLE DB for ODBC) and insert either DSN or provider string into the corresponding field. Then if you select this linked server in the Enterprise Manager, you will see the tables that reside in the database you selected. It means that the linked server is set up properly. Then you should create a new OLE DB Subscriber and when asked select the linked server you have created. It must work too. BTW, I experienced some problems with transactional replications to PostgreSQL 6.5.4 on both OLE DB Subscriber (not all type definitions were created correctly) and ODBC Subscriber (replication failed to repeat the transations if the connection was broken once). Hope it is fixed in 7.0. > In how fare are SQL stored procedures different? Quite. All the triggers are fired FOR EACH STATEMENT in MS SQL (as for now, PostgreSQL does not support it). PostgreSQL does not support nested transactions, cursors in stored procedures (use FOR loops instead of cursors), etc. You cannot get system names from variables in PostgreSQL (so if you use something like CREATE TABLE @mytable in your triggers or procedures, you will have to write an external procedure to achieve this result). PostgreSQL does not support output variables in functions, and uses RETURN result, that is commonly used to report an error in MS SQL procedures. In general, SQL of PostgreSQL is MUCH poorer that one of MS SQL (it does not support OUTER JOINS, you cannot perfom runtime formed queries, etc.). And so on, and so on... So, even your procedures are simple, you will have to rewrite them all manually anyway, at least to fit in PostgreSQL variable naming convention ($1 for the first variable, etc.) Alex Bolenok.
pgsql-general by date: