funnyheavenly

Connect to postgres with native provider. Visual Studio Languages. If you just want to be able to browse a PostgreSQL database in Server Explorer, then you will need install the PostgreSQL ODBC driver, and connect via the.NET Framework Data Provider for ODBC. Download PostgreSQL OLE DB Native Provider 64-bit (Developer's License) free. FEATURES> * Database conversion with DTS Wizard (MSSQL 2000) or SSIS.

• Postgresql Native Provider (pgnp)
• Postgresql Native Provider
• Postgresql Native Ole Db Provider (pgnp)

• Eugenia Bryant
• 2 years ago
• Views:

Transcription

1 2014 PGNP Native OLE DB Provider for PostgreSQL, Greenplum, Redshift Developer s Manual This document contains description of various PGNP features, use cases and programming techniques. It is written for professional developers. Konstantin Izmailov Copyright All Rights Reserved. 5/9/2014

2 This document is property of PGNP team. Neither a part nor the entire document can be reproduced, changed, distributed or published without prior written permission of owners. Please contact us if you have any questions: Page 2

3 Table of Contents 1 Introduction Installation and Product Activation Provider s Features Database Transformation Transforming MS SQL 2000 database into a PostgreSQL database using DTSWizard Transforming MS SQL 2005, 2008, 2012 database into a PostgreSQL database using DTSWizard Transactions Local Transactions Distributed Transactions PostgreSQL Nested Transactions Linked Servers Create Linked Server using SQL Server Wizard Create Linked Server using SQL Server Stored Procedures Viewing and changing Linked Server RPC status Running Linked Server in a separate process (out-of-proc) Replication with SQL Server Configuring Publisher, Subscribers and Distributor Creating publication Create Snapshot Adding Subscribers Synchronize Replication with SQL Server 2005/2008/ Configure SQL Server as Distributor Configure the publisher to use a specified distribution database Create Linked Server Create the snapshot publication Create the snapshot subscription Deleting subscription and publication Create publication for transactional replication Create subscription for transactional replication Generating reports in SQL Server Reporting Services Two phase commit protocol (2PC) Page 3

4 3.7.1 Configuring DTC Starting DTC Service Enabling prepared transactions in PostgreSQL Troubleshooting issues with 2PC FastLoad feature Configuring OLE DB connection in BIDS Configuring Source and Destination The Query Optimizer Simple query substitution Template based substitution Exact Match scenario: optimizing ROLAP cube Optimizing metadata retrieval Programming with the Provider Connection String Main String parameters Extended Properties Parameter BULK_METHOD Parameters for Redshift Data type mapping between PostgreSQL and OLE DB Internal Stored Procedures Get License Information Refresh Metadata Cache Check license Publish comment into PGNP Profiler log Known Issues Appendix A. Utilities CreateIndex DropIndex PGNP Profiler (1.3.x and later) User interface explained Main actions in the profiler Filter PGNPUpdate (1.4.x and later) Working in Normal mode Page 4

5 5.4.2 Working in Activation mode Appendix B. Samples C# Samples C++ Samples Delphi 7 Samples Page 5

6 1 Introduction The PGNP Native OLE DB Provider exposes powerful low-level OLEDB interfaces to Windows applications connecting to PostgreSQL, Greenplum and Redshift databases. The provider can help you achieve performance and flexibility that are not available via either ODBC driver or.net Provider: Rich metadata (advanced schema and cursors) Databases transformation support Linked Servers Replication Database Reverse Engineering Bulk import The Developer s Manual describes the Provider functionality and gives examples of the Provider usage. It is intended for use by software developers, system administrators and users of the OLE DB applications. The supported operating systems are: Windows 2000 with MDAC 2.8 SP1, Windows XP, Windows Server 2003, Windows Server 2008, Windows Server 2012, Vista, Windows 7, Windows 8; both 32-bit and 64-bit. The PGNP Provider works with the following versions of PostgreSQL database: PostgreSQL 8.0 and later, Greenplum 3.0 and later, EnterpriseDB Advanced Server 8.3 and later, Redshift. It may work on earlier versions of the corresponding databases but we have not tested those configurations, or Provider might not support them. The Postgres and Greenplum Providers are available in two editions: Desktop Edition (DE) and Server Edition (SE). Following table summarizes differences: Postgres DE Intended for use from desktop applications connecting to Postgres databases only. This edition may not be as fast and as scalable as SE on the very large rowsets (several million rows). Postgres SE Intended for use from servers (IIS, SSAS, SSIS, SSRS, linked servers, etc.) connecting to Postgres databases only. Optimized for extremely large rowsets, supports two phase commit protocol (DTC enlistment), and provides better integration with SSIS. Greenplum DE Intended for use from desktop applications connecting either to Postgres or Greenplum. Handles distribution policies, able to work with Greenplums forward-only cursors, utilizes gpload. Greenplum SE Intended for use from servers (IIS, SSAS, SSIS,SSRS, linked servers, etc.) connecting to Postgres and Greenplum databases. Optimized for extremely large rowsets, supports two phase commit protocol (DTC enlistment), and provides better integration with SSIS. Redshift Intended for use from any applications connecting to Redshift. Supports cursors and FastLoad via S3.The Redshift Provider can be installed side by side with either Postgres or Greenplum OLE DB providers. Note: Server Edition (SE) has all the features of Desktop Edition (DE), and adds more features as shown in the table above. Page 6

7 2 Installation and Product Activation To install the PGNP OLEDB Provider launch the installation module. The Module name may vary depending on the provider variant/edition: Module name PGNP-Postgres-DE exe PGNP-Postgres-SE exe PGNP-Greenplum-DE exe PGNP-Greenplum-SE exe PGNP-Redshift-SE exe Description OLEDB Provider for Postgres, Desktop Edition OLEDB Provider for Postgres, Server Edition OLEDB Provider for Greenplum, Desktop Edition OLEDB Provider for Greenplum, Server Edition OLEDB Provider for Redshift Note: Evaluation module name includes word trial, e.g.: PGNP-Postgres-DE-Trial exe. First page of the installation application is shown below. Click Next through the wizard pages. Please read the License Agreement carefully. Click Next. Specify the installation folder. By default, the Postgres and Greenplum providers are installed into C:Program Files (x86)intellisoftpgnp folder, and the Redshift into C:Program Files (x86)intellisoftrsnp folder. Page 7

8 Enter the Product Key (License Key). The installation module will automatically activate the product on the computer. This step requires an Internet connection. If Internet connection is not available, or you prefer to activate the product later, select the check box on the bottom of the page (Register the product later using PGNPUpdate utility). Please refer to PGNP Update utility guide below, specifically to section Working in Activation Mode. Note: If the provider was previously activated on the computer the installation will not require entering the product key. In that case the above page is not displayed, and installation proceeds to the next step. Click Next in following dialogs. Page 8

9 Note. To determine the installed version of the PGNP provider run appwiz.cpl from a command line (or go to Start->Control Panel->Programs and Features/Add or Remove Programs). Line starting with PGNP OLEDB Provider contains version and build number, e.g Another method is to view PGNP.DLL or PGNP64.DLL Properties in Windows Explorer: Page 9

10 The provider version can also be determined programmatically via call to pgnp_getlicenceinfo. Page 10

11 3 Provider s Features This chapter contains information about various features and use cases of the Provider. 3.1 Database Transformation MS DTS/SSIS tools allow copying databases between various OLE DB sources. PGNP Provider can be used to transform a non-postgresql database into a PostgreSQL database and vice versa Transforming MS SQL 2000 database into a PostgreSQL database using DTSWizard 1. Make sure MS SQL Server 2000 is running and source database available. 2. Create an empty destination database in PostgreSQL. 3. Launch DTSWizard.exe ('C:Program FilesMicrosoft SQL Server80ToolsBinndtswiz.exe'). 4. Select source database. Click Next. Page 11

12 5. Select PostgreSQL Native Provider from the Destination combo-list, click Properties button and specify connection parameters of the destination database. Click Next. Page 12

13 6. Select Lower Case Schema to convert tables and columns names into lower case (recommended): 7. Click Next button on the Specify Table Copy or Query page. Page 13

14 8. Select tables and views you want to be copied to destination database. Click Next. 9. Choose Run Immediately and click Next. Click Finish. 10. Inspect result of conversion. To convert indexes use CreateIndex utility. See more information in Utilities chapter of the Manual Transforming MS SQL 2005, 2008, 2012 database into a PostgreSQL database using DTSWizard The database transformation procedure between SQL Server 2005, 2008, 2012 and Postgres, Greenplum, Redshift is similar to the one described above. Launch SQL Server Import and Export Wizard, and specify OLEDB source and destination. Page 14

15 3.2 Transactions The PGNP provider supports standard OLE DB transactions (Local transactions), COM+ Distributed transactions (starting version 1.3.0), PostgreSQL nested transactions via direct OLEDB calls or SQL commands, and savepoints Local Transactions Transactions are a convenient mechanism to achieve all or nothing effect when multiple changes are made in a database. Transactions can be used via ADO, ADO.NET or OLE DB calls to Connection object. Example 1. Using transactions in Delphi (ADO). Example 2. Using transactions in C++ (OLE DB). Example 3. Using transactions in C# (ADO.NET). OleDbConnection con1 = new OleDbConnection(sb.ToString()); con1.open(); // begin main transaction OleDbTransaction trans = con1.begintransaction(); // insert a new record OleDbCommand cmd_insert = new OleDbCommand('insert into contact(fname,lname) values (:fname,:lname)', con1, trans); cmd_insert.parameters.addwithvalue(':fname', 'Joe'); cmd_insert.parameters.addwithvalue(':lname', 'Blah'); Debug.Assert(1 cmd_insert.executenonquery()); // begin nested transaction OleDbTransaction nested_trans1 = trans.begin(); // begin nested transaction inside the nested one OleDbTransaction nested_trans2 = nested_trans1.begin(); // remove recently inserted record in the nested transaction level 2 OleDbCommand cmd_delete = new OleDbCommand('delete from contact where lname=?', con1, nested_trans2); cmd_delete.parameters.addwithvalue('?', 'Blah'); Debug.Assert(1 cmd_delete.executenonquery()); // rollback nested_trans2.rollback(); // check that the record was not removed from the nested transaction level 2 OleDbCommand cmd_check = new OleDbCommand('select count(*) from contact where lname=?', con1, nested_trans); cmd_check.parameters.addwithvalue('?', 'Blah'); Debug.Assert(1 Convert.ToInt32(cmd_check.ExecuteScalar())); // remove the record cmd_delete.transaction = nested_trans; Debug.Assert(1 cmd_delete.executenonquery()); // commit the changes nested_trans.commit(); // check that the record was actually removed cmd_check.transaction = trans; Debug.Assert(0 Convert.ToInt32(cmd_check.ExecuteScalar())); trans.commit(); con1.close(); Starting version PGNP supports nested transactions. Note, since ADO does not support nested transactions, you can use them from either OLE DB or ADO.NET (see C# Sample 26). To enable nested transactions specify NESTED_TRANS=ON in Extended Properties parameter of the connection string. Page 15

16 PGNP provider triggers transactions related events on the Connection object: BeginTransComplete, CommitTransComplete, RollbackTransComlete. Example 4. Using transaction events in Delphi. TADOConnection fconn;... // begin transaction callback procedure PGNPBeginTransComplete( Connection: TADOConnection; TransactionLevel: Integer; const Error: Error; var EventStatus: TEventStatus);... // specify isolation level fconn.isolatiolevel := ilreadcommitted; // subscribe for transaction event(s) fconn.onbegintranscomplete := PGNPBeginTransComplete; // start transaction fconn.begintrans;... // some useful work... if Res then fconn.commit else fconn.rollback; // note: PGNPBeginTransComplete callback is called from provider // note: OnCommitComplete callback is called // note: OnRollback callback is called Distributed Transactions Distributed transactions allow coordinating database transactions among multiple databases or transactional systems. PGNP Provider supports transactions enlistment in Distributed Transactions Coordinator (DTC) and handles events from the DTC. Note: distributed transactions support added to PGNP and later. One of the scenarios that require distributed transactions is when you need to pull data from two dependent constantly changing tables in SSIS, so that when it s imported on MSSQL side the data is in consistent state. You can configure the Integration Services project with a Control Flow property Transaction Option equal to Require. The following C# example demonstrates distributed transactions use: //.Net 2.0 and later supports distributed transactions via TransactionsScope object. // The object uses distributed transaction if there is more than one connection open in its context, // otherwise a local transaction is used. using System; using System.Data.OleDb; using System.Diagnostics; using System.Text; using System.Transactions; namespace DistribTrans class Program static void Main(string[] args) StringBuilder sb = new StringBuilder(); sb.append('provider=pgnp.1;'); sb.append('persist Security Info=True;'); sb.append('data Source=localhost;'); // PostgreSQL server; sb.append('initial Catalog=postgres;'); // Database name sb.append('user ID=postgres;'); // User name sb.append('password=12345;'); // User password sb.append('extended Properties='PORT=5432;'); // TransactionScrope automatically links local transactions to the distributed one. // Here are two local transactions and one distributed. Page 16

17 using (TransactionScope scope = new TransactionScope()) OleDbConnection con1 = new OleDbConnection(sb.ToString()); con1.open(); // A local transaction is started automatically // Insert first record OleDbCommand cmd_insert = new OleDbCommand('insert into country (country,currency) values (:country,:currency)', con1); cmd_insert.parameters.addwithvalue('country', 'Russia'); cmd_insert.parameters.addwithvalue('currency', 'Rouble'); Debug.Assert(1 cmd_insert.executenonquery()); OleDbConnection con2 = new OleDbConnection(sb.ToString()); con2.open(); // A local transaction is started automatically // Insert second record cmd_insert.connection = con2; cmd_insert.parameters['country'].value = 'Latvia'; cmd_insert.parameters['currency'].value = 'Lat'; Debug.Assert(1 cmd_insert.executenonquery()); // Commit distributed transaction. // Commit() will be called for all local transactions. scope.complete(); PostgreSQL Nested Transactions ADO does not support nested transactions. However, PostgreSQL nested transactions can be used by calling OLEDB interfaces or executing START TRANSACTION, SAVEPOINT and other SQL commands. To enable nested transaction support in PGNP provider add NESTED_TRANS=ON parameter to Extended Properties of a connection string. Note: nested transactions support added to PGNP and later. The PGNP provider recognizes transaction related SQL commands and tracks internal transaction state automatically. The following C# example demonstrates nested transactions and isolation levels use: // // Transaction isolation level defines visibility of changes among different parallel transactions. // // The isolation level can be set with System.Data.IsolationLevel parameter in call to // OleDbConnection.BeginTransaction(). // using System; using System.Collections.Generic; using System.Data.OleDb; using System.Text; using System.Diagnostics; namespace NestedTrans class Program static void Main(string[] args) StringBuilder sb = new StringBuilder(); sb.append('provider=pgnp.1;'); sb.append('persist Security Info=True;'); sb.append('data Source=localhost;'); // PostgreSQL server; sb.append('initial Catalog=postgres;'); // Database name sb.append('user ID=postgres;'); // User name sb.append('password=12345;'); // User password sb.append('extended Properties='NESTED_TRANS=ON;'); // Enable nested transactions OleDbConnection con1 = new OleDbConnection(sb.ToString()); con1.open(); // Start main transaction Page 17

18 OleDbTransaction trans = con1.begintransaction(); trans); // Add new record OleDbCommand cmd_insert = new OleDbCommand('insert into country (country,currency) values (:country,:currency)', con1, cmd_insert.parameters.addwithvalue(':country', 'Russia'); cmd_insert.parameters.addwithvalue(':currency', 'Ruble'); Debug.Assert(1 cmd_insert.executenonquery()); // Start first nested transaction OleDbTransaction internal_transaction = trans.begin(); // Start second nested transaction OleDbTransaction internal_transaction2 = internal_transaction.begin(); // Delete the record in second nested transaction OleDbCommand cmd_delete = new OleDbCommand('delete from country where country=?', con1, internal_transaction2); cmd_delete.parameters.addwithvalue('?', 'Russia'); Debug.Assert(1 cmd_delete.executenonquery()); // Rollback second nested transaction internal_transaction2.rollback(); // Check that the record was actually not removed OleDbCommand cmd_check = new OleDbCommand('select count(*) from country where country=?', con1, internal_transaction); cmd_check.parameters.addwithvalue('?', 'Russia'); Debug.Assert(1 Convert.ToInt32(cmd_check.ExecuteScalar())); // Delete the record in first nested transaction cmd_delete.transaction = internal_transaction; Debug.Assert(1 cmd_delete.executenonquery()); // Commit changes internal_transaction.commit(); // Check in main transaction that the record was removed cmd_check.transaction = trans; Debug.Assert(0 Convert.ToInt32(cmd_check.ExecuteScalar())); trans.commit(); con1.close(); 3.3 Linked Servers Linked Server is a feature of MS SQL Server that allows access to non-sql Server databases through the SQL Server. Note, only SQL Server Developer and Enterprise support Linked servers. SQL Server Express edition has several limitations that do not allow configuring or using Linked Servers Create Linked Server using SQL Server Wizard Before creating or using Linked Servers make sure that Allow inprocess option is checked. Right click on Linked Servers/Provider/PGNP and select Properties menu item. Page 18

19 To create a linked server, launch Enterprise Manager 2000 or Management Studio 2005/2008/2012. Right click on Security/Linked Servers or Server Objects/Linked Servers and select New Linked Server menu item. Enter the Linked server name and choose PostgreSQL Native Provider in Other Data Sources /Provider combo box. Enter your product name, Postgres server, Extended Properties of the connection string and database name as shown: Page 19

20 In Security tab select Be made using this security context radio button and enter credentials to access Postgres database. Click OK. The Linked Server is created. To test the linked server, expand Linked Servers/MYLINKEDSVR/Catalogs/postgres: You can query or change data using SQL commands like: Page 20

21 SELECT * FROM MYLINKEDSVR. postgres. public. contacts SELECT * FROM MYLINKEDSVR... contacts SELECT * FROM openquery( MYLINKEDSVR, SELECT * FROM contacts ) INSERT * INTO MYLINKEDSVR... contacts VALUES( John, Smith, 1988) SELECT 1 FROM openquery( MYLINKEDSVR, UPDATE contacts SET dob=2002 WHERE dob=02 ) Note, openquery syntax allows using of PostgreSQL-specific statements. Refer to MSDN documentation for more details. Please remember using double quotes if your PostgreSQL schema is in mixed case Create Linked Server using SQL Server Stored Procedures Ensure that PGNP provider is configured to run as 'In Process DLL': EXEC master.dbo.sp_msset_oledb_prop N'PGNP.1', N'AllowInProcess', 1 EXEC master.dbo.sp_msset_oledb_prop N'PGNP.1', N'DynamicParameters', 1 Execute following SQL statements to create a linked server with name 'PGNP_SAMPLES': varchar(max); varchar(max); varchar(max); varchar(max); varchar(max); varchar(max) -- postgres database info = N'PGNP_SAMPLES'; = N'PGNP'; = N'PGNP'; = N'localhost'; = 'PORT=5432;CNV_SPECIAL_FLTVAL=ON;'; = N'linkedtest'; /* DROP LINKED SERVER */ IF EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id!= 0 AND srv.name EXEC /* CREATE LINKED SERVER */ EXEC = = = = = /* set up Extended properties of the Linked Server */ EXEC master.dbo.sp_addlinkedsrvlogin '12345' EXEC EXEC EXEC remote Viewing and changing Linked Server RPC status Some SQL commands sent to a linked server require RPC configuration. For example, following command: Page 21

22 EXEC ('CREATE TABLE contact(id INT NOT NULL, LNAME VARCHAR)') AT PGNP_SAMPLES May return error: Server 'PGNP_SAMPLES' is not configured for RPC. Linked server configuration can be viewed with following command: exec sp_helpserver 'PGNP_SAMPLES' To configure the linked server execute following commands: @optvalue='true' Running Linked Server in a separate process (out-of-proc) Some IT departments have policies that do not allow running third party component within MSSQL Server process. The advantage is that possible memory leaks and crashes in the PGNP provider won't affect SQL Server process since PGNP provider runs in a separate surrogate process (dllhost.exe). Here are steps to configure out-of-proc : 1. Register a surrogate process for PGNP provider to make it visible in DCOMCNFG utility. For this either run OleView.exe utility (part of Visual Studio or SDK) or apply registry script (see below). In OleView expand 'All Objects' in the treeview on the left and select 'PostgreSQL Native Provider' object. Go to 'Implementation' tab and check 'Use Surrogate Process' option. In the edit box below specify path to dllhost.exe, for example: C:WINDOWSsystem32dllhost.exe. Then select 'Registry' tab and you will see that DllSurrogate named value was added under AppId key. Following scripts can be executed instead of running OleView. For 64bit PGNP provider: Windows Registry Editor Version 5.00 [HKEY_CLASSES_ROOTAppID3170DFF a0-A1B3-D ] 'DllSurrogate'='C:WindowsSysWow64dllhost.exe' FilesPGNPPGNP64.dll' 'ThreadingModel'='Both' [HKEY_LOCAL_MACHINESOFTWAREClassesAppID3170DFF a0-A1B3-D ] 'DllSurrogate'='C:WindowsSysWow64dllhost.exe' FilesPGNPPGNP64.dll' 'ThreadingModel'='Both' For 32bit PGNP provider on 64bit Windows: Windows Registry Editor Version 5.00 [HKEY_CLASSES_ROOTWow6432NodeAppID3170DFF a0-A1B3-D ] 'DllSurrogate'='C:WindowsSysWow64dllhost.exe' Files (x86)pgnppgnp.dll' 'ThreadingModel'='Both' [HKEY_LOCAL_MACHINESOFTWAREWow6432NodeClassesAppID3170DFF a0-A1B3-D ] 'DllSurrogate'='C:WindowsSysWow64dllhost.exe' Files (x86)pgnppgnp.dll' 'ThreadingModel'='Both' For 32bit PGNP provider on 32bit Windows: Windows Registry Editor Version 5.00 Page 22

23 [HKEY_CLASSES_ROOTAppID3170DFF a0-A1B3-D ] 'DllSurrogate'='C:WindowsSystem32dllhost.exe' FilesPGNPPGNP.dll' 'ThreadingModel'='Both' [HKEY_LOCAL_MACHINESOFTWAREClassesAppID3170DFF a0-A1B3-D ] 'DllSurrogate'='C:WindowsSystem32dllhost.exe' FilesPGNPPGNP.dll' 'ThreadingModel'='Both' 2. Run DCOMCNFG.exe utility. Expand 'Component Services', 'Computers', 'My Computer' and select 'DCOM Config' folder. In the right pane find application named '3170DFF a0-A1B3-D ', right-click and select Properties. Configure Security and Identity in the corresponding tabs. Default values could work in many cases. Close the utility. 3. In SQL Server Management Studio uncheck 'allow inprocess' option in PGNP provider properties. The changes are immediate and usually do not require restart of SQL Server or the computer. Alternatively the following stored procedure can be executed: EXEC master.dbo.sp_msset_oledb_prop N'PGNP.1', N'AllowInProcess', 0 Perform a query for the Linked Server. If issue occurs see Events Viewer for errors information (e.g. run eventvwr.msc from command prompt). If a test query succeeded you can use Process Explorer ( to find which surrogate process hosts the PGNP provider DLL. 3.4 Replication with SQL Server 2000 To set up replication use a login account that is a member of SQL Server's Process Administrators or higher authority server role, e.g. sa user Configuring Publisher, Subscribers and Distributor Start SQL Server Enterprise Manager and click Configure Publishing, Subscribers and Distribution menu item under main menu Tools->Replication. Make your server a local distributor (CUDA is a computer name): Page 23

24 Specify location for snapshots: Accept default settings on the next page for simplicity (or choose custom setting). The server is configured as Distributor: Creating publication Right click on pubs database, select menu item New->Publication and choose pubs : Choose Snapshot or Transactional publication type ( Merge is not supported yet ): Page 24

25 Choose Heterogeneous data sources : Choose all tables for replication: Page 25

26 Click on the ellipses for every table and review properties (optional): Click Next button Create Publication Wizard dialog until: Page 26

27 3.4.3 Create Snapshot Right click on <Server>ReplicationPublicationspubsPublication:pubs and select Properties menu item. Then select Status tab: Click Run Agent Now button to create the snap shot files Adding Subscribers Create a new PostgreSQL Database: Page 27

28 Click Configure Publishing, Subscribers and Distribution menu item under main menu Tools->Replication and select Subscribers tab. Click New button and select OLEDB data source : Then choose the subscriber (for instructions on how to create linked server see Chapter Linked Servers in this manual). Here are screenshots of the Linked Server configuration: This is dialog for adding a subscriber: Page 28

29 Click OK button in Publisher and Distributor Properties dialog: Right click on <Server>/Replication/Publications/pubsPublication:pubs and click Push New Subscription menu item to run the New Subscription wizard. Choose the newly created subscriber and the default destination for the Heterogeneous Subscribers: Page 29

30 Then set Distribution Agent schedule: Choose Yes, initialize the schema and data option and Start immediately check box: Page 30

31 Click Next and Finish: Synchronize Right click on the PGPUBS:(default destination) item and select Start Synchronization menu item: Page 31

32 Page 32

33 3.5 Replication with SQL Server 2005/2008/2012 MS SQL Servers 2005/2008/2012, unlike SQL Server 2000, do not have the user friendly graphical interface for replication configuration. However, the replication can be configured via SQL stored procedures in SSMS Configure SQL Server as Distributor In the following script replace 1) <server-name> with your SQL Server name, e.g. CUDAINST5 computer name and optional SQL instance name; 2) <path> with a folder path where replication files can be created, e.g. E:MSSQLreplicate create the path manually before executing the script; 3) <user> with your SQL server user, e.g. sa; 4) <password> with the SQL user s password, e.g. sapwd. use master GO exec = = N'<password>' GO exec = = = = = = = = 1 GO Configure the publisher to use a specified distribution database use [distribution] if (not exists (select * from sysobjects where name = 'UIProperties' and type = 'U ')) create table UIProperties(id int) if (exists (select * from ::fn_listextendedproperty('snapshotfolder', 'user', 'dbo', 'table', 'UIProperties', null, null))) EXEC sp_updateextendedproperty N'SnapshotFolder', N'<path>', 'user', dbo, 'table', 'UIProperties' else EXEC sp_addextendedproperty N'SnapshotFolder', N'<path>', 'user', dbo, 'table', 'UIProperties' GO exec = = = = = = = = = N'MSSQLSERVER' GO Create Linked Server Create an empty PostgreSQL database using psql or PGAdmin and a Linked Server in SSMS (refer to section for details). Please double check that Allow inprocess box is checked in Server ObjectsLinked ServersProvidersPGNPProperties dialog (access it via treeview pane on the left in the SSMS) Create the snapshot publication In the following script replace 1) <sql-database> with your SQL database name; 2) <SQLHOST> with SQL Server host name (user friendly name), e.g. CUDA_INST5; use [<sql-database>] exec = = = N'true' GO exec = N'pgnpsnap1', Page 33

34 @description = N'Snapshot publication of database 'distribution' from Publisher = = = = = = = = = = = = = = = = = = = = = 1 GO exec = = = = = = = = = = = = = = 1 GO For each table <table-name> execute following command: exec = = = = = = = = = = = = = N'false' GO Ensure that non-sql Server Subscribers are supported: exec sp_changepublication N'pgnpsnap1', N'enabled_for_het_sub', = = Create the snapshot subscription This final step adds a new scheduled agent job. Please replace 1) <postgres-user> with PostgreSQL user name, e.g. postgres ; 2) <postgres-pwd> with PostgreSQL user s password, e.g ; 3) <postgres-host> with computer name running PostgreSQL Server; 4) <extended-properties> with any PGNP extended properties, e.g. LOWERCASESCHEMA=OFF;SEARCH_PATH=vinci;PORT=5432;, for more details see Connection String; 5) <postgres-database> with PostgreSQL database name created on Create Linked Server step above. exec = = = = = = = N'read only', -- type 3 is MUST for subscribers not oracle and ibmdb2 i.e. = 3 exec = = = exec = = = exec = = = exec = = = exec = = = = N'all', Page 34

35 exec = = = exec = = = exec = = = = = = = = = = = = = = = = = = = = = = = N'Distributor' GO For more information read To create first snapshot and start replication, open Replication Monitor (right-click on ReplicationLaunch Replication Monitor), then select pgnpsnap1 publisher on the left, right-click Snapshot Agent line in Warnings and Agents tab and click Start Agent : Deleting subscription and publication Following commands can be used for deleting previously created subscription and/or publication: exec = = = N'pgnpsubr'; exec = N'pgnpsnap1' Page 35

36 The steps above described snapshot replication configuration. To configure transactional replication perform following steps instead of steps and Create publication for transactional replication In the following script replace 1) <sql-database> with your SQL database name; 2) <SQLHOST> with SQL Server host name (user friendly name), e.g. CUDA_INST5; use [<sql-database>] exec = = = N'true' GO exec = = N'Transactional publication of database 'distribution' from Publisher = = = = = = = = = = = = = = = = = = = = = = = = N'false' GO exec = = = = = = = = = = = = = = 1 GO For each table <table-name> execute following command: exec = = = = = = = = = = = = = N'false' GO Ensure that non-sql Server Subscribers are supported: exec sp_changepublication N'pgnptrans1', N'enabled_for_het_sub', = = Create subscription for transactional replication This final step adds a new scheduled agent job. Please replace 1) <postgres-user> with PostgreSQL user name, e.g. postgres ; 2) <postgres-pwd> with PostgreSQL user s password, e.g ; 3) <postgres-host> with computer name running PostgreSQL Server; 4) <extended-properties> with any PGNP extended properties, e.g. LOWERCASESCHEMA=OFF;SEARCH_PATH=vinci;PORT=5432;, for more details see Connection String; 5) <postgres-database> with PostgreSQL database name created on Create Linked Server step above. exec = = = = = = = N'read only', Page 36

37 @subscriber_type = 3 exec = = = exec = = = exec = = = exec = = = exec = = = exec = = = exec = = = exec = = = = = = = = = = = = = = = = = = = = = = = N'Distributor' GO Page 37

38 3.6 Generating reports in SQL Server Reporting Services Navigate to the Report Manager in the Internet Explorer using a URL, e.g. (where SQL11 is the name of the SLQ Server database instance; it could be different). Launch the Report Builder, and create New Report using Table or Matrix Wizard. Create new OLE Data Source, and specify connection parameters to the Postgres database. For the Dataset use following command: ='select * from [DimEmployee] where [Title] in (' & JOIN(Parameters!TitlePrm.Value, ',') & ') ' Choose columns to populate in the report. Then create the report parameter as shown below: Note: The OLE DB standard says that multiple parameters can be used only with commands that to not return rowsets. This is the reason why we must use IN clause and the JOIN function to pass multiple parameters. Otherwise Page 38

39 SSRS will display the error: Cannot add multi value query parameter 'prm' for dataset 'DataSet1' because it is not supported by the data extension. (rserroraddingmultivaluequeryparameter). On the Available Values page specify some hard coded values: Run the report with all parameter values: Page 39

40 3.7 Two phase commit protocol (2PC) PGNP Provider implements support for 2PC. You will need to configure and run Distributed Transaction Coordinator Configuring DTC From a command prompt execute following command: dcomcnfg. Expand Component Services node and right-click on Local DTC node, select properties as shown below: Navigate to Security tab and make sure that Enable XA Transactions checkbox is selected. Network DTC Access, Allow Inbound and Allow Outbound must be selected as well. Please read MSDN articles for more details on configuring DTC. Click OK in the Local DTC Properties dialog and restart DTC service (see the next paragraph) Starting DTC Service DTC can be started either from Services snap-in (services.msc command) or using commands net stop msdtc and net start msdtc. If you planning to use 2PC regularly then consider configuring DTC Service for automatic startup in the Services snap-in Enabling prepared transactions in PostgreSQL Some later versions of PostgreSQL have prepared transactions disabled by default. To enable the prepared transactions, edit postgresql.conf file as described below. Open the postgresql.conf file in editor and find line with max_prepared_transactions parameter (if missing, new line can be added). Uncomment the line by removing # symbol in front and set the parameter equal to maximum allowed number of connections or more, e.g. max_prepared_transactions = 100; # zero disables the feature Restart PostgreSQL Server Troubleshooting issues with 2PC Often issues with 2PC are caused by misconfiguration, or failure in registration. Sometimes reinstalling provider, and repeating the configuration steps from this chapter can resolve the issues. Running the PGNP Profiler may provide additional error information that might help in troubleshooting. Page 40

41 If the following error is returned: MSDTC XARMCreate error, please check if the following registry keys exist: HKLMSoftwareWow6432NodeMicrosoftMSDTCXADLLPGNP.DLL=<path> HKLMSoftwareMicrosoftMSDTCXADLLPGNP64.DLL=<path> The key is created automatically when the provider is enlisting in a distributed transaction for the first time. The provider may not be able to create the key depending on the parent process security context. In this case, either manually create the keys, or set proper security permissions on the registry folder, and restart the distributed transaction. For example, grant Network Service account permissions for full access to HKLMSoftwareMicrosoftMSDTCXADLL, if DTC runs under the Network Service account. 3.8 FastLoad feature The original implementation of the PGNP provider allowed using BULK_METHOD=COPY;BULK_INSERT=1000; for fast data import into the Postgres database. Starting with a new method is available based on implementation of IRowsetFastLoad interface (Server Edition only). Using the FastLoad is a recommended way of loading data into the Postgres database. This chapter describes an example of table loading from SQL Server into Postgres using FastLoad Configuring OLE DB connection in BIDS Launch Business Intelligence Development Studio (BIDS), and then create a new Integration Services Project. Click the right mouse button in Connection Managers tab, and select New OLE DB Connection menu item. Click New button, and then select PostgreSQL Native Provider (BI Alias) in the Connection Manager dialog. Note that (BI Alias) must be selected for the FastLoad feature to work. Page 41

42 Regular PGNP Provider name used in a connection string is PGNP. But the BI Alias name is SQLOLEDBPGNP. This is needed because of undocumented Microsoft implementation which enables various features only to providers with names started with SQLOLEDB. Also create a connection to a SQL Server database with source data (using SQL Server Native Client ) Configuring Source and Destination Add Data Flow Task, and configure OLE DB Source by choosing a table in SQL Server database. Then add OLE DB Destination, connect the green arrow from the Source to the Destination, and edit the Destination. In the OLE DB Destination Editor choose Postgres connection, and the Data access mode as shown: Proceed by configuring column mapping. Then execute the package, and check the result of the rows copying. Note, that PGNP Profiler will show something like the following: Page 42

43 3.9 The Query Optimizer The Query Optimizer is a built-in mechanism to transform SQL queries in the PGNP Provider. It can be used to troubleshoot performance issues in large databases without a need to change SQL in an application. The Optimizer is closely integrated with PGNP Profiler (see 5.3 in Appendix A. Utilities). User can define rules for replacing SQL queries on the fly. The rules can use Exact Match, or Template Match modes for optimizing the queries. In Exact Match mode, an application query is replaced with the optimized query only when the application query is the same as one specified in a rule. In Template Match mode, an application query is replaced with the optimized query when the application query matches a query in a rule so that any numerical constants or literals ignored. The Optimizer requires a special table to store configuration. It can be created manually by executing the statements shown below, or let the PGNP Profiler automatically create it (recommended). Commands for enabling Optimizer in Postgres and Greenplum: CREATE TABLE pgnp_optimizer ( optimizer_id serial PRIMARY KEY, hashtype int2, enabled character(1), original text, hash int4, final text, category varchar(32), modified timestamp ); UPDATE PGNP_OPTIMIZER SET ENABLED='Y' WHERE HASH=0; INSERT INTO PGNP_OPTIMIZER(HASHTYPE, ENABLED, HASH, CATEGORY, MODIFIED) SELECT 0, 'Y', 0, 'System', now() WHERE NOT EXISTS (SELECT 1 FROM PGNP_OPTIMIZER WHERE HASH=0); Commands for enabling Optimizer in Redshift: CREATE TABLE PGNP_OPTIMIZER ( OPTIMIZER_ID int IDENTITY(1,1) PRIMARY KEY, HASHTYPE int2 NOT NULL, ENABLED char, ORIGINAL text, HASH int4 NOT NULL, FINAL text, CATEGORY varchar(32), MODIFIED timestamp ); DELETE FROM PGNP_OPTIMIZER WHERE HASH=0; INSERT INTO PGNP_OPTIMIZER(HASHTYPE, ENABLED, HASH, CATEGORY, MODIFIED) VALUES (0, 'N', 0, 'System', ' :00:00'); In PGNP Profiler select menu View -> Options, and click ellipsis in the Query Optimizer s Connection string: Page 43

44 In the Data Link Properties dialog, configure the connection to a database where queries optimization should be performed. Click OK in the Options dialog, and the pgnp_optimizer table will be created automatically. To disable Query Optimizer uncheck the Enable option in the dialog. This chapter describes examples of using the Query Optimizer Simple query substitution Execute the following query SELECT 2*3, locate corresponding trace entry in the PGNP Profiler, click right mouse button on the entry, and select Optimize Query menu item: In the Optimizer dialog replace the original statement with SELECT 4+5, keep Exact Match method, and optionally enter any description as shown below: Page 44

45 Click OK, execute the original statement again (SELECT 2*3), and locate new trace entry in the PGNP Profiler. Notice, that result is now equal to 9, and the Profiler shows how the statement was substituted: Template based substitution Execute the following query SELECT id+30 AS id, 'Tax', 0.81 FROM (SELECT 1 AS id) AS x, locate corresponding trace entry in the PGNP Profiler, right click mouse on the entry, and select Optimize Query menu item. In the Optimizer dialog replace original statement with SELECT $4+$1 AS id, $2, $3, set Template Match method, and optionally enter some description as shown below: Page 45

46 In the Template Match mode integer, floating point and string constants from original query will be passed into parameters of the template. Execute original statement, or a statement with different constants, and locate new trace entry in the PGNP Profiler. The Profiler shows how the statement was substituted: As shown on the picture above, a query with different constants was executed: SELECT x.id+1000 AS id, 'Fee', FROM (SELECT 5 AS id) AS x Exact Match scenario: optimizing ROLAP cube When developing and running a SQL Server Analysis Services (SSAS) project we do not have direct control over the generated statements. However, some of the statements may not be using all power of a Postgres/Greenplum database. The performance issues can be addressed by optimizing (substituting) the statements in the OLEDB Provider. Run the ROLAP cube and locate slowest query in the PGNP Profiler s trace: Page 46

47 Copy the Client SQL from the details view. Here is a simplified representation of the slow query: SELECT SUM(...),... FROM( SELECT... FROM FACT LEFT OUTER JOIN CONCEPT...) AS dbo_fact, CONCEPT AS dbo_concept WHERE (dbo_fact.pk=dbo_concept.pk) AND (dbo_fact.key1=?) GROUP BY... Create materialized view as shown below. Notice, the parameterized conditions (marked with yellow background) were removed from the query. CREATE TABLE view_fact AS SELECT SUM(...),... FROM( SELECT... FROM FACT LEFT OUTER JOIN CONCEPT...) AS dbo_fact, CONCEPT AS dbo_concept WHERE (dbo_fact.pk=dbo_concept.pk) GROUP BY... DISTRIBUTED BY (key1) Right click mouse on the slow query, and select Optimize Query menu item. In the Optimizer dialog replace Optimized Statement with: SELECT * FROM view_fact WHERE (key1=$1) Keep the Exact Match method; click OK in the Optimizer dialog. Then run the cube again, locate the corresponding trace entry in the Profiler, and see that execution is now significantly faster. In our test with 400 million rows fact table, acceleration was more than Optimizing metadata retrieval It is possible to optimize not only client queries but the provider generated metadata retrieval queries as well. This could be used to accelerate metadata retrieval in very complex databases containing hundreds of schemas with thousands tables, where pg_catalog size exceeds 10GB. Run application, and locate slow schema query in PGNP Profiler s trace: Copy Executed SQL from the Details view, and create a materialized view as shown below: Page 47

48 CREATE TABLE view_columns AS select * from (select T.schemaname as 'TABLE_SCHEMA', T.tablename as 'TABLE_NAME', A.attname as 'COLUMN_NAME', A.attnum as 'ORDINAL_POSITION', A.atthasdef as 'COLUMN_HASDEFAULT', A.atttypid as 'DATA_TYPE', TY.typname as 'TYPNAME', A.attnotnull as 'NOT_NULL', A.attlen as 'FIELD_LENGTH', A.atttypmod as 'FIELD_MOD', D.adsrc as 'COLUMN_DEFAULT' from pg_attribute A inner join pg_class C on (A.attrelid=C.oid) inner join pg_tables T on (C.relname=T.tablename) inner join pg_namespace NS on (NS.oid=C.relnamespace and NS.nspname=T.schemaname) inner join pg_type TY on (TY.oid=A.atttypid) left outer join pg_attrdef D on (D.adrelid=C.oid and D.adnum=A.attnum) where A.attnum>0 and A.attisdropped='f' union all select T.schemaname as 'TABLE_SCHEMA', T.viewname as 'TABLE_NAME', A.attname as 'COLUMN_NAME', A.attnum as 'ORDINAL_POSITION', A.atthasdef as 'COLUMN_HASDEFAULT', A.atttypid as 'DATA_TYPE', TY.typname as 'TYPNAME', A.attnotnull as 'NOT_NULL', A.attlen as 'FIELD_LENGTH', A.atttypmod as 'FIELD_MOD', D.adsrc as 'COLUMN_DEFAULT' from pg_attribute A inner join pg_class C on (A.attrelid=C.oid) inner join pg_views T on (C.relname=T.viewname) inner join pg_namespace NS on (NS.oid=C.relnamespace and NS.nspname=T.schemaname) inner join pg_type TY on (TY.oid=A.atttypid) left outer join pg_attrdef D on (D.adrelid=C.oid and D.adnum=A.attnum) where A.attnum>0 and A.attisdropped='f') s where 'TABLE_SCHEMA'=[ ] and 'TABLE_NAME'=[ ] order by 'TABLE_SCHEMA', 'TABLE_NAME', 'ORDINAL_POSITION' Note: conditions marked with red color should be removed. Right click mouse on the entry, select Optimize Query menu item, and replace statement as shown below: Re-run the application, and locate the schema query. Here is result of the statement substitution: Page 48

49 Page 49

50 4 Programming with the Provider 4.1 Connection String PGNP connection string consists of a list of name=value pairs separated by semicolon, e.g.: Provider=PGNP.1;User ID=postdba;Password=montreal; Initial Catalog=aloha;Data Source=bbox; Extended Properties= PORT=5432;COMMAND_TIMEOUT=900; ; Main String parameters Parameter Type Version Required Default Description Provider String All Yes Provider name. Always equals to PGNP.1 User ID String All Yes Database user name Password String All Yes Database password Initial Catalog String All Yes Database name* Data Source String All Yes PostgreSQL server host name or IP address Connect Timeout Integer No 15 Connect timeout in seconds. Zero is used for indefinite timeout. Extended Properties String All No Extended parameters list in double quotes. See details below. * Note: A special reserved value $NO_CATALOG can be used for the Initial Catalog. It allows creating OLEDB session without connecting to a database. Only two stored procedures are working in the NO_CATALOG mode: pgnp_getlicenseinfo and pgnp_checklicense Extended Properties Parameter Type Version Required Default Description Port Integer All No 5432 PostgreSQL server port. CURSOR Boolean No ON When set to ON the provider uses cursors to query and change data. Main advantage of using cursors is lower memory consumption on client side. However, since cursors work only inside transaction (that can be automatically created by the provider), it may result in undesirable side-effects. LowerCaseSchema Boolean All No OFF If ON, automatically convert all schema into lower case, e.g. Create table MiXed () will be transformed into Create table mixed () This parameter is used mostly during Database Transformation of a case insensitive database, such as MS SQL, into PostgreSQL. SYNTAX String No Pass- Through Reserved. Specifies which SQL syntax the Provider should expect as input: Pass-Through for PostgreSQL compatible syntax; T-SQL MS SQL Server 2005 compatible syntax; or Oracle Oracle 10g2 compatible syntax. The Provider converts input statements into PostgreSQL compatible statements. SEARCH_PATH String and later No $user,public Specifies the order in which schemas are searched when an object (table, data type, function, etc.) is referenced by a simple name with no schema component. See description of search_path parameter in for more details. Example of when the Page 50

51 use of this parameters can be useful is a DTS package. FORCEUTF8 Boolean All No OFF When OFF uses client encoding reported by PostgreSQL. When ON forces client encoding to be UTF8 regardless of encoding reported by PostgreSQL (PGNP compatibility mode). This is an important parameter for databases and client applications which using international symbols. CNV_SPECIAL_FLTVAL Boolean No OFF When OFF no conversion of special floating point values (INFINITY, NaN, etc.) is performed when reading Real and Float fields from database. When ON the following conversion is performed: * Infinity is converted into MAX float e+38F or double e+308; * -Infinity is converted into negative max float or double; * NaN is converted into zero. We recommend turning this option ON when working with Linked servers. NESTED_TRANS Boolean --- No OFF When OFF nested transactions are not allowed. When ON allow nested transactions. This was implemented by request of some customers in and currently disabled in all versions. In 1.3.x nested transactions are allowed by default. SSL String No Prefer SSL parameters. Allowed values: Require, Prefer, Disable, or Allow. BULK_INSERT Integer No 1 Specifies the number of rows for bulk insert (e.g. during DTS/SSIS data import). Valid range is See BULK_METHOD below for details on how this parameter is used. Normally the larger the number the less roundtrips performed for data insertion. BULK_METHOD String No Values Bulk insert method: COPY, PIPECOPY, GPLOAD and VALUES. For more details read paragraph below. COMMAND_TIMEOUT Integer No 30 Number of seconds for command timeout. If a command execution takes more than the specified duration, then the command is automatically cancelled and error is returned. Please specify 0 (zero) for the infinite wait. TEXT_AS_LONGVARCHAR Boolean No ON If this parameter is set to OFF then Provider returns Text BLOBs as Strings. This parameter can be used when migrating PostgreSQL ODBC applications to PGNP. ACCEPT_CERT String No <none> MD5 hexadecimal sum of the PostgreSQL certificate. This parameter can be used to avoid server certificate validation dialog and basically tells Provider to accept the specific certificate temporarily for the current session. ZERO_TS_FRACTION Boolean No OFF When the parameter is set to ON, the Provider zeroes timestamp fraction in rowset fields. This addresses Datetime overflow issue when transforming databases in SSIS. SQLSERVER Integer No 2005 Setting this parameter to 2005, 2008 or 2010 exposes provider s type system differently to support database transformations between PostgreSQL and corresponding versions of SQL Server. For example, when set to 2005 PostgreSQL types date and time are handled as SQL Server s datetime; when 2008 support for new SQL Server types datetime2, time2, interval, datetimeoffset is added. Page 51

52 MIN_FETCH_ROWS Integer No 100 Specifies how many rows should be fetched in PostgreSQL Server Side cursor. Valid range is If an invalid value is specified, then default value is used. It is recommended to use value of 2000 or larger for tables with millions of rows (to reduce number of round-trips for fetching the data). HTTP_PORT Integer Greenplum only NIC_ADDR String Greenplum only No 8081 The HTTP port on which gpfdist.exe will serve files. No <none> List of IP addresses if the computer has multiple NICs, e.g. NIC_ADDR= , , DISTINCT_VALUES String Greenplum only FORCE_SORTED_LOV String Greenplum only No DISTINCT When non-default value is used (GROUPBY) the provider adds to queries with the DISTINCT clause the following clauses: GROUP BY 1,2. This parameter may be used for performance. No NO When non-default value is used (YES) the provider adds to queries with the DISTINCT clause the following clauses: ORDER BY 1,2. Works only with DISTINCT_VALUES=GROUPBY. This parameter may be used for performance. EVENTS String No ALL When default value is used (ALL), OLEDB Provider subscribes to all events, including metadata and optimizer hints change events. To disable subscribing to any events use NONE. Other values are reserved for future use. Provider triggers events each time metadata or optimizer hints change occurs despite of the parameter. AWS_ACCESS_KEY String Redshift No Amazon S3 Access Key. AWS_SECRET_KEY String Redshift No Amazon S3 Secret Key. AWS_BUCKET String Redshift No Amazon S3 Bucket Name. AWS_PROXY_ADDRESS String Redshift No Proxy name to access Amazon S3. AWS_PROXY_USER String Redshift No Proxy user to access Amazon S3. AWS_PROXY_PASSWORD String Redshift No Proxy password to access Amazon S3. OPTIMIZER Boolean No OFF Turns the PGNP Optimizer ON or OFF Parameter BULK_METHOD When set to VALUES (default) then BULK_INSERT specifies number of value sets in the SQL command: INSERT INTO table (col1, col2, coln) VALUES ($1, $2, $N), ($N+1, $N+2, $2N), ($mn+1, $mn+2, $mn+n) When set to COPY then BULK_INSERT specifies number of rows buffered for COPY command. When set to PIPECOPY, then multiple INSERT statements are combined into COPY command. This is special mode designed for fast transactional replication. When set to GPLOAD then a Greenplum s loader utility is used (gpload/gpfdist). GPLOAD parameter is used only by the Greenplum edition of the PGNP provider. Page 52

53 BULK_METHOD is an obsolete way of fast importing data into database. We recommend using FastLoad for fast data import. However, PIPECOPY may be only an option when fast transactional replication is needed Parameters for Redshift The FastLoad and BulkLoad algorithms in Redshift OLEDB provider are using the COPY command and Amazon S3. The connection string parameters (AWS_*) must be populated for the FastLoad to work properly. Proxy usage is optional. Temporary security tokens are currently not supported. Please refer Amazon online documentation on the description of the parameters: Data type mapping between PostgreSQL and OLE DB The PGNP OLE DB Provider supports the following data type mappings between PostgreSQL data types and OLE DB data types. Provider Data Type PostgreSQL Data Type OLE DB Data Type(s) OLE DB Type Name(s) int2 int2 DBTYPE_I2 DBTYPE_I2 smallint smallint int4 int4 DBTYPE_I4 DBTYPE_I4 int int integer integer float4 float4 DBTYPE_R4 DBTYPE_R4 real real float(p), p<25 float(p), p<25 float8 float8 DBTYPE_R8 DBTYPE_R8 double precision double precision float(p), p>=25 float(p), p>=25 money money DBTYPE_CY DBTYPE_CY bool bool DBTYPE_BOOL DBTYPE_BOOL boolean boolean bit tinyint byte int2 DBTYPE_UI1 DBTYPE_UI1 int8 int8 DBTYPE_I8 DBTYPE_I8 bigint bigint uuid uuid DBTYPE_GUID DBTYPE_GUID binary varbinary image bytea DBTYPE_BYTES 'DBTYPE_BINARY' 'DBTYPE_VARBINARY' 'DBTYPE_LONGVARBINARY' and DBCOLUMNFLAGS_ISLONG or DBPARAMFLAGS_ISLONG char char DBTYPE_STR DBTYPE_CHAR varchar varchar DBTYPE_STR DBTYPE_VARCHAR text text DBTYPE_STR and DBCOLUMNFLAGS_ISLONG or DBPARAMFLAGS_ISLONG DBTYPE_CHAR DBTYPE_VARCHAR DBTYPE_LONGVARCHAR and Page 53

54 DBCOLUMNFLAGS_ISLONG or DBPARAMFLAGS_ISLONG ntext text DBTYPE_WSTR and DBCOLUMNFLAGS_ISLONG or DBPARAMFLAGS_ISLONG DBTYPE_CHAR DBTYPE_VARCHAR DBTYPE_LONGVARCHAR and DBCOLUMNFLAGS_ISLONG or DBPARAMFLAGS_ISLONG nvarchar varchar DBTYPE_WSTR DBTYPE_VARCHAR nchar char DBTYPE_WSTR DBTYPE_CHAR numeric numeric DBTYPE_NUMERIC DBTYPE_NUMERIC decimal decimal timestamp timestamp DBTYPE_DBTIMESTAMP DBTYPE_DBTIMESTAMP xml xml DBTYPE_XML DBTYPE_XML Provider Data Type column specifies data types that PGNP provider exposes to a user. These types are recognized in CREATE and ALTER statements and substituted on-the-fly with corresponding PostgreSQL Data Type. These types are used by MS Data Transformation Services Wizard database conversion. Page 54

55 4.3 Internal Stored Procedures The purpose of the provider s internal stored procedures is to supply user application with additional services. Current implementation includes procedures: to obtain the provider license information, to refresh metadata cache, check license, publish comments into PGNP log, etc. In future more stored procedures are going to be written and even support for custom third-party procedures can be added. Almost all stored procedures work even when no connection is open to database (i.e. Initial Catalog=$NO_CATALOG) Get License Information The procedure pgnp_getlicenseinfo() can be used to obtain information about current provider owner and license type. It can be called as any PostgreSQL procedure (see C# sample 16): pgnp_getlicenseinfo() The resulting recordset has the following columns: Ordinal Column name Type Description 1 LICENSEDTO String Shows user name or company who purchased the product 2 TYPE String License type, i.e. Single User License or a License Pack 3 PURCHASEDATE Date Date of the product purchase 4 VERSION String Version of the product, e.g UPDATEDATE Date If this is an update of the product, then shows date, otherwise is NULL 6 SPECIAL String Unique identifier that uniquely identifies the product build Refresh Metadata Cache The procedure pgnp_refreshmetadata(<reserved>, <name>) can be used to request refreshing of internal metadata cache in all provider instances connected to the same database from any computer. This could be useful when database schema has changed (a table or a procedure were altered). The procedure has two input string parameters. First parameter is reserved for the future and should always be specified as empty string. Second parameter can contain a table or a procedure name. Here are examples of valid calls: pgnp_refreshmetadata(, employee ) pgnp_refreshmetadata(, myproc1 ) -- refresh info about employee table in the cache -- refresh info about myproc1 procedure in the cache The procedure does not return any data Check license The procedure pngp_checklicense() is similar to pgnp_getlicenseinfo(), and can be used to perform simple check if the license is valid (for diagnostics purposes). Both procedures can operate in a special mode when there is no connection to database made. Initial Catalog parameter of the connection string can be set to the reserved value $NO_CATALOG to open a session without connecting to a database. Pgnp_checklicense() returns a row with two columns: integer error code, and description. Zero in first column means success, and description contains either Success, or Error description Publish comment into PGNP Profiler log The procedure pgnp_comment can be used by applications to insert comments into PGNP Profiler s log. Also, PGNP Provider can be configured to populate the internal execution trace into the Profiler s log. The procedure accepts two parameters: integer level, and string message. Level specifies how important is the comment (1-most important, 2-medium, 3-least important). PGNP Profiler can be configured to listen to no comments, or comments with levels Page 55

56 1, 2 or 3 (all comments). The procedure returns not result, and there is no way to know if it succeeded. If PGNP Profiler is not running, or configured to listen for SQL Statements Only, the procedure does nothing. Here is example of calling the function via linked server in SSMS: EXEC ('pgnp_comment(1, 'test message 123')') AT PGSVR 4.4 Known Issues Here is the list of known issues in version 1.4, build 3206: 1. When ETL converting single characters fields from SQL Server to Postgres the following error is reported: Failed to convert between Unicode and non-unicode data for column. 2. Kerberos authentication is not supported. 3. Neither tcp_keep_alive, nor failover partner is supported. 4. Bit strings are not supported. Page 56

57 5 Appendix A. Utilities 5.1 CreateIndex Purpose of the CopyIndex utility is copying of indexes from source DB to a destination DB. Common use case: it can be used after DTS/SSIS wizard or program package copied data between databases to append information about indexes. USAGE: CopyIndex.exe -s <.udl File> -d <.udl File> -a <string> [-i <string>] [-t <string>] [--] [-v] [-h] -s <.udl File>, --srcudl <.udl File> (required) (value required) A source database.udl file. -d <.udl File>, --dstudl <.udl File> (required) (value required) A destination database.udl file. -a <string>, --schema <string> (required) (value required) Schema restriction. -i <string>, --index <string> (value required) Optional index name restriction. -t <string>, --table <string> (value required) Optional table name restriction. --, --ignore_rest Ignores the rest of the labeled arguments following this flag. -v, --version -h, --help Displays usage information and exits. Example 1. Copy all indexes from MSDB (MSSQL, dbo) to PGDB (Postgres, dbo). CopyIndex.exe -s MSDB.udl -d PGDB.udl -a dbo Example 2. Copy all indexes related to Employees table. Employees table should exist in source and destination DBs. CopyIndex.exe -s MSDB.udl -d PGDB.udl -a dbo -t Employees Example 3. Copy PK_Employees indexe in Employees table. Employees table should exist in source and destination DBs. CopyIndex.exe -s MSDB.udl -d PGDB.udl -a dbo -t Employees -i PK_Employees See CreateIndex.cpp source code below: // CopyIndex.cpp : Defines the entry point for the console application. // #include 'stdafx.h' #include 'IndexDesc.h' #include 'VersionInfo.h' Page 57

58 #include <tclap/cmdline.h> using namespace TCLAP; typedef map<wstring, CIndexDesc> MapIndexes; typedef pair<wstring, CIndexDesc> PairIndexes; void DisplayErrorInfo() CDBErrorInfo errors; ULONG errorcount; HRESULT hr = errors.geterrorrecords(&errorcount); if (FAILED(hr)) cerr << '** ERROR: hr = ' << hex << hr << endl; return; for (ULONG i = 0; i < errorcount; i++) _bstr_t description; errors.getallerrorinfo(i, GetSystemDefaultLCID(), description.getaddress()); cerr << '** ' << description << endl; #define SET_ORDINAL(Rowset, ColName, Ordinal) /*const*/ DBORDINAL Ordinal; if (!Rowset.GetOrdinal(ColName, &Ordinal)) _ftprintf(stderr, _T('** ERROR: Column %s ordinal not found in INDEXES scheman'), ColName); return E_FAIL; int _tmain(int argc, _TCHAR* argv[]) HRESULT hr = CoInitialize(NULL); if (FAILED(hr)) cerr << '** ERROR: CoInitialize returned ' << hex << hr << endl; return hr; TCHAR filename[max_path]; GetModuleFileName(NULL, filename, MAX_PATH); VersionInfo verinfo(filename); _bstr_t srcudl, dstudl, bstrschema, bstrindex, bstrtable; try CmdLine cmdline(verinfo.filedescription(), ' ', verinfo.productversion_str()); ValueArg<_tstring> tablearg(_t('t'), _T('table'), _T('Optional table name restriction.'), false, _T('), _T('string'), cmdline); ValueArg<_tstring> indexarg(_t('i'), _T('index'), _T('Optional index name restriction.'), false, _T('), _T('string'), cmdline); ValueArg<_tstring> schemaarg(_t('a'), _T('schema'), _T('Schema restriction.'), true, _T('), _T('string'), cmdline); ValueArg<_tstring> dudlfilearg(_t('d'), _T('dstudl'), _T('A destination database.udl file.'), true, _T('), _T('.udl File'), cmdline); ValueArg<_tstring> sudlfilearg(_t('s'), _T('srcudl'), _T('A source database.udl file.'), true, _T('), _T('.udl File'), cmdline); cmdline.parse(argc, argv); srcudl = sudlfilearg.getvalue().c_str(); dstudl = dudlfilearg.getvalue().c_str(); bstrschema = schemaarg.getvalue().c_str(); bstrindex = indexarg.getvalue().c_str(); bstrtable = tablearg.getvalue().c_str(); catch (ArgException &e) cerr << endl << '** ERROR: ' << 'Invalid Arguments: ' << endl << e.error().c_str() << endl; return 1; CDataSource CSession srcdatasource, dstdatasource; srcsession, dstsession; // STEP 1. Load DBSCHEMA_INDEXES from source Provider. cout << 'Loading DBSCHEMA_INDEXES from source database...' << endl; hr = srcdatasource.openfromfilename(srcudl); if (FAILED(hr)) //OpenFromInitializationString(srcConnString); Page 58

59 DisplayErrorInfo(); return hr; hr = srcsession.open(srcdatasource); if (FAILED(hr)) DisplayErrorInfo(); return hr; CComPtr<IDBSchemaRowset> spschemarowset; hr = srcsession.m_spopenrowset->queryinterface( uuidof(idbschemarowset), (void**)&spschemarowset); if (FAILED(hr)) cerr << '** ERROR: QueryInterface(IDBSchemaRowset) returned ' << hex << hr << endl; return hr; VARIANT vnorestriction; vnorestriction.vt = VT_EMPTY; VARIANT vtableschemarestriction; vtableschemarestriction.vt = VT_EMPTY; if (bstrschema.length() > 0) vtableschemarestriction.vt = VT_BSTR; vtableschemarestriction.bstrval = SysAllocString(bstrSchema.GetBSTR()); VARIANT vindexnamerestriction; vindexnamerestriction.vt = VT_EMPTY; if (bstrindex.length() > 0) vindexnamerestriction.vt = VT_BSTR; vindexnamerestriction.bstrval = SysAllocString(bstrIndex.GetBSTR()); VARIANT vtablenamerestriction; vtablenamerestriction.vt = VT_EMPTY; if (bstrtable.length() > 0) vtablenamerestriction.vt = VT_BSTR; vtablenamerestriction.bstrval = SysAllocString(bstrTable.GetBSTR()); VARIANT rgrestrictions[] = vnorestriction, vtableschemarestriction, vindexnamerestriction, vnorestriction, vtablenamerestriction ; CAccessorRowset<CDynamicAccessor, CBulkRowset> prs; hr = spschemarowset->getrowset(null, DBSCHEMA_INDEXES, sizeof(rgrestrictions)/sizeof(rgrestrictions[0]), rgrestrictions, IID_IRowset, 0, NULL, (IUnknown**)&pRS.m_spRowset); if (FAILED(hr)) DisplayErrorInfo(); return hr; hr = prs.bind(); hr = prs.movefirst(); SET_ORDINAL(pRS, _T('TABLE_SCHEMA'), itableschema); SET_ORDINAL(pRS, _T('TABLE_NAME'), itablename); SET_ORDINAL(pRS, _T('INDEX_NAME'), iindexname); SET_ORDINAL(pRS, _T('PRIMARY_KEY'), iprimarykey); SET_ORDINAL(pRS, _T('UNIQUE'), iunique); SET_ORDINAL(pRS, _T('CLUSTERED'), iclustered); SET_ORDINAL(pRS, _T('ORDINAL_POSITION'), iorderedposition); SET_ORDINAL(pRS, _T('COLUMN_NAME'), icolumnname); SET_ORDINAL(pRS, _T('INTEGRATED'), iintegrated); map<wstring, CIndexDesc> mapindexes; // map index name into index description while (S_OK hr) LPCTSTR tableschema = (LPCTSTR)pRS.GetValue(iTableSchema); LPCTSTR tablename = (LPCTSTR)pRS.GetValue(iTableName); LPCTSTR indexname = (LPCTSTR)pRS.GetValue(iIndexName); bool primarykey = *(bool*)prs.getvalue(iprimarykey); bool unique = *(bool*)prs.getvalue(iunique); bool clustered = *(bool*)prs.getvalue(iclustered); LPCTSTR columnname = (LPCTSTR)pRS.GetValue(iColumnName); MapIndexes::iterator it = mapindexes.find(indexname); CIndexDesc& index = (it!= mapindexes.end())? it->second : mapindexes.insert(pairindexes(indexname, CIndexDesc(tableName, primarykey, unique, clustered))).first->second; Page 59

60 index.addcolumn(columnname); hr = prs.movenext(); prs.close(); cout << ' Indexes loaded count: ' << mapindexes.size() << endl; // STEP 2. Access IIndexDefinition in destination Provider. cout << 'Querying IIndexDefinition from destination Provider...' << endl; hr = dstdatasource.openfromfilename(dstudl); if (FAILED(hr)) DisplayErrorInfo(); return hr; //OpenFromInitializationString(dstConnString); hr = dstsession.open(dstdatasource); if (FAILED(hr)) DisplayErrorInfo(); return hr; CComPtr<IIndexDefinition> spindexdefinition; hr = dstsession.m_spopenrowset->queryinterface( uuidof(iindexdefinition), (void**)&spindexdefinition); if (FAILED(hr)) cerr << '** ERROR: QueryInterface(IIndexDefinition) failed hr=' << hr << endl; return hr; // STEP 3. Integrity check. // STEP 4. Create indexes. cout << 'Creating indexes...' << endl; bool bignoreallerrors = false; int nsuccess = 0; for (MapIndexes::iterator it = mapindexes.begin(); it!= mapindexes.end(); it++) CIndexDesc& index = it->second; vector<wstring>& lcolumns = index.getcolumns(); DBID TableName; DBID IndexName; TableName.eKind = DBKIND_NAME; TableName.uName.pwszName = (LPOLESTR) index.gettablename(); IndexName.eKind = DBKIND_NAME; IndexName.uName.pwszName = (LPOLESTR) it->first.c_str(); DBPROP indexdbprop[4]; DBPROPSET indexdbpropset[1]; DBINDEXCOLUMNDESC* rgindexcolumndescs = (DBINDEXCOLUMNDESC*)_alloca(lColumns.size() * sizeof(dbindexcolumndesc)); DBID* dbidcolumns = (DBID*)_alloca(lColumns.size() * sizeof(dbid)); // Enforce index properties. indexdbprop[0].dwpropertyid indexdbprop[0].dwoptions indexdbprop[0].vvalue.vt indexdbprop[0].vvalue.lval indexdbprop[0].colid indexdbprop[1].dwpropertyid indexdbprop[1].dwoptions indexdbprop[1].vvalue.vt indexdbprop[1].vvalue.lval indexdbprop[1].colid indexdbprop[2].dwpropertyid indexdbprop[2].dwoptions indexdbprop[2].vvalue.vt indexdbprop[2].vvalue.lval indexdbprop[2].colid indexdbprop[3].dwpropertyid indexdbprop[3].dwoptions indexdbprop[3].vvalue.vt indexdbprop[3].vvalue.lval indexdbprop[3].colid = DBPROP_INDEX_NULLS; = DBPROPOPTIONS_REQUIRED; = VT_I4; = DBPROPVAL_IN_DISALLOWNULL; = DB_NULLID; = DBPROP_INDEX_PRIMARYKEY; = DBPROPOPTIONS_REQUIRED; = VT_BOOL; = index.getprimary()? VARIANT_TRUE : VARIANT_FALSE; = DB_NULLID; = DBPROP_INDEX_CLUSTERED; = DBPROPOPTIONS_REQUIRED; = VT_BOOL; = index.getclustered()? VARIANT_TRUE : VARIANT_FALSE; = DB_NULLID; = DBPROP_INDEX_UNIQUE; = DBPROPOPTIONS_REQUIRED; = VT_BOOL; = index.getunique()? VARIANT_TRUE : VARIANT_FALSE; = DB_NULLID; Page 60

61 // Initialize the property set. indexdbpropset[0].guidpropertyset = DBPROPSET_INDEX; indexdbpropset[0].rgproperties = indexdbprop; indexdbpropset[0].cproperties = sizeof(indexdbprop)/sizeof(indexdbprop[0]); // Set up DBINDEXCOLUMNDESC structures to define the columns in the // index and the ordering for each column within that index. for (unsigned i=0; i < lcolumns.size(); i++) rgindexcolumndescs[i].eindexcolorder = DBINDEX_COL_ORDER_ASC; rgindexcolumndescs[i].pcolumnid = &dbidcolumns[i]; // Specify the column names for the composite index on // LastName and FirstName. dbidcolumns[i].ekind = DBKIND_NAME; dbidcolumns[i].uname.pwszname = (LPOLESTR) lcolumns[i].c_str(); wcout << L' Table: ' << TableName.uName.pwszName << L't Index: ' << IndexName.uName.pwszName << endl; // Create a two-column composite index named full_name_index over the // LastName and FirstName columns in the Employees table. hr = spindexdefinition->createindex(&tablename, &IndexName, (DBORDINAL)lColumns.size(), rgindexcolumndescs, sizeof(indexdbpropset)/sizeof(indexdbpropset[0]), indexdbpropset, NULL); if (FAILED(hr)) DisplayErrorInfo(); if (!bignoreallerrors) cerr << 'Terminate the indexes creation process? Enter 'Y' to terminate, ' << endl << ' 'N' to continue, 'I' to ignore all further errors [Y]: '; string sinput; std::getline(cin, sinput, 'n'); if (sinput.length() 0) break; else nsuccess++; bignoreallerrors = sinput[0] 'I' sinput[0] 'i'; if (!bignoreallerrors &&!(sinput[0] 'N' sinput[0] 'n')) break; CoUninitialize(); cout << 'Successfully created ' << nsuccess << ' indexes. Not being able to create: ' << mapindexes.size() - nsuccess << '.' << endl; return 0; Page 61

62 5.2 DropIndex Purpose of the DropIndex utility is deleting indexes in DB. Common use case: it can be used to undo changes made by CopyIndex utility. USAGE: DropIndex.exe [-c] -u <.udl File> -a <string> [-i] <string>] [-t <string>] [--] [-v] [-h] Where: -c, --confirmation Ask confirmation before dropping an index. Recommended. -u <.udl File>, --udl <.udl File> (required) (value required) A database.udl file. -a <string>, --schema <string> (required) (value required) Schema restriction. -i <string>, --index <string> (value required) Optional index name restriction. -t <string>, --table <string> (value required) Optional table name restriction. --, --ignore_rest Ignores the rest of the labeled arguments following this flag. -v, --version Displays version information and exits. -h, --help Displays usage information and exits. Example 1. Delete all indexes in a PGDB (Postgres, dbo). DropIndex.exe -u PGDB.udl -a dbo Example 2. Delete PK_Employee index in Employees table. DropIndex.exe -u PGDB.udl -a dbo -t Employees -i PK_Employee See DropIndex.cpp source code below. // DropIndex.cpp : Defines the entry point for the console application. // #include 'stdafx.h' #include 'VersionInfo.h' #include <tclap/cmdline.h> using namespace TCLAP; void DisplayErrorInfo() CDBErrorInfo errors; ULONG errorcount; HRESULT hr = errors.geterrorrecords(&errorcount); if (FAILED(hr)) cerr << '** ERROR: hr = ' << hex << hr << endl; return; Page 62

63 for (ULONG i = 0; i < errorcount; i++) _bstr_t description; errors.getallerrorinfo(i, GetSystemDefaultLCID(), description.getaddress()); cerr << '** ' << description << endl; #define SET_ORDINAL(Rowset, ColName, Ordinal) /*const*/ DBORDINAL Ordinal; if (!Rowset.GetOrdinal(ColName, &Ordinal)) _ftprintf(stderr, _T('** ERROR: Column %s ordinal not found in INDEXES scheman'), ColName); return E_FAIL; struct CIndexStruc CIndexStruc(wstring i_stable, wstring i_sindex) : stable(i_stable), sindex(i_sindex) wstring stable; wstring sindex; ; int _tmain(int argc, _TCHAR* argv[]) HRESULT hr = CoInitialize(NULL); if (FAILED(hr)) cerr << '** ERROR: CoInitialize returned ' << hex << hr << endl; return hr; TCHAR filename[max_path]; GetModuleFileName(NULL, filename, MAX_PATH); VersionInfo verinfo(filename); _bstr_t audl, bstrschema, bstrindex, bstrtable; try CmdLine cmdline(verinfo.filedescription(), ' ', verinfo.productversion_str()); ValueArg<_tstring> tablearg(_t('t'), _T('table'), _T('Optional table name restriction.'), false, _T('), _T('string'), cmdline); ValueArg<_tstring> indexarg(_t('i'), _T('index'), _T('Optional index name restriction.'), false, _T('), _T('string'), cmdline); ValueArg<_tstring> schemaarg(_t('a'), _T('schema'), _T('Schema restriction.'), true, _T('), _T('string'), cmdline); ValueArg<_tstring> udlfilearg(_t('u'), _T('udl'), _T('A database.udl file.'), true, _T('), _T('.udl File'), cmdline); SwitchArg sinteractive(_t('c'), _T('confirmation'), _T('Ask confirmation before dropping an index. Recommended.'), false, cmdline); cmdline.parse(argc, argv); audl = udlfilearg.getvalue().c_str(); bstrschema = schemaarg.getvalue().c_str(); bstrindex = indexarg.getvalue().c_str(); bstrtable = tablearg.getvalue().c_str(); catch (ArgException &e) cerr << endl << '** ERROR: ' << 'Invalid Arguments: ' << endl << e.error().c_str() << endl; return 1; CDataSource CSession datasource; session; // STEP 1. Load DBSCHEMA_INDEXES from source Provider. cout << 'Loading DBSCHEMA_INDEXES from database...' << endl; hr = datasource.openfromfilename(audl); if (FAILED(hr)) DisplayErrorInfo(); return hr; hr = session.open(datasource); if (FAILED(hr)) DisplayErrorInfo(); Page 63

64 return hr; CComPtr<IDBSchemaRowset> spschemarowset; hr = session.m_spopenrowset->queryinterface( uuidof(idbschemarowset), (void**)&spschemarowset); if (FAILED(hr)) cerr << '** ERROR: QueryInterface(IDBSchemaRowset) returned ' << hex << hr << endl; return hr; VARIANT vnorestriction; vnorestriction.vt = VT_EMPTY; VARIANT vtableschemarestriction; vtableschemarestriction.vt = VT_EMPTY; if (bstrschema.length() > 0) vtableschemarestriction.vt = VT_BSTR; vtableschemarestriction.bstrval = SysAllocString(bstrSchema.GetBSTR()); VARIANT vindexnamerestriction; vindexnamerestriction.vt = VT_EMPTY; if (bstrindex.length() > 0) vindexnamerestriction.vt = VT_BSTR; vindexnamerestriction.bstrval = SysAllocString(bstrIndex.GetBSTR()); VARIANT vtablenamerestriction; vtablenamerestriction.vt = VT_EMPTY; if (bstrtable.length() > 0) vtablenamerestriction.vt = VT_BSTR; vtablenamerestriction.bstrval = SysAllocString(bstrTable.GetBSTR()); VARIANT rgrestrictions[] = vnorestriction, vtableschemarestriction, vindexnamerestriction, vnorestriction, vtablenamerestriction ; CAccessorRowset<CDynamicAccessor, CBulkRowset> prs; hr = spschemarowset->getrowset(null, DBSCHEMA_INDEXES, sizeof(rgrestrictions)/sizeof(rgrestrictions[0]), rgrestrictions, IID_IRowset, 0, NULL, (IUnknown**)&pRS.m_spRowset); if (FAILED(hr)) DisplayErrorInfo(); return hr; hr = prs.bind(); hr = prs.movefirst(); SET_ORDINAL(pRS, _T('TABLE_NAME'), itablename); SET_ORDINAL(pRS, _T('INDEX_NAME'), iindexname); SET_ORDINAL(pRS, _T('ORDINAL_POSITION'), iorderposition); list<cindexstruc> lstindexes; // map index name into index description while (S_OK hr) LPCTSTR tablename = (LPCTSTR)pRS.GetValue(iTableName); LPCTSTR indexname = (LPCTSTR)pRS.GetValue(iIndexName); int orderpos = *(int*)prs.getvalue(iorderposition); lstindexes.push_back(cindexstruc(tablename, indexname)); hr = prs.movenext(); prs.close(); cout << ' Indexes found: ' << lstindexes.size() << endl; // STEP 2. Access IIndexDefinition in destination Provider. cout << 'Querying IIndexDefinition from target Provider...' << endl; CComPtr<IIndexDefinition> spindexdefinition; hr = session.m_spopenrowset->queryinterface( uuidof(iindexdefinition), (void**)&spindexdefinition); if (FAILED(hr)) cerr << '** ERROR: QueryInterface(IIndexDefinition) failed hr=' << hr << endl; return hr; // STEP 3. Drop indexes. Page 64

65 cout << 'Dropping indexes...' << endl; bool bignoreallerrors = false; int nsuccess = 0; for (list<cindexstruc>::iterator it = lstindexes.begin(); it!= lstindexes.end(); it++) DBID TableName; DBID IndexName; TableName.eKind = DBKIND_NAME; TableName.uName.pwszName = (LPOLESTR) it->stable.c_str(); IndexName.eKind = DBKIND_NAME; IndexName.uName.pwszName = (LPOLESTR) it->sindex.c_str(); wcout << L' Table: ' << TableName.uName.pwszName << L't Index: ' << IndexName.uName.pwszName << endl; // Create a two-column composite index named full_name_index over the // LastName and FirstName columns in the Employees table. hr = spindexdefinition->dropindex(&tablename, &IndexName); if (FAILED(hr)) DisplayErrorInfo(); if (!bignoreallerrors) cerr << 'Terminate the indexes deletion process? Enter 'Y' to terminate, ' << endl << ' 'N' to continue, 'I' to ignore all further errors [Y]: '; string sinput; std::getline(cin, sinput, 'n'); if (sinput.length() 0) break; else nsuccess++; bignoreallerrors = sinput[0] 'I' sinput[0] 'i'; if (!bignoreallerrors &&!(sinput[0] 'N' sinput[0] 'n')) break; CoUninitialize(); cout << 'Successfully dropped ' << nsuccess << ' indexes. Not being able to drop: ' << lstindexes.size() - nsuccess << '.' << endl; return 0; Page 65

66 5.3 PGNP Profiler (1.3.x and later) The profiler utility allows collecting SQL statements with performance counters and internal execution trace from the PGNP provider. Applications can also publish events into the Profiler s log using pgnp_comment stored procedure. The SQL trace and counters information can be filtered, stored to an external file, and loaded later. Any application or service (32- and 64-bit) that uses PGNP provider can be profiled User interface explained There are four main panels in the profiler window: Applications/Logs panel contains list of computer hosts ( ), applications ( ) and log files ( ). Filter panel allows user to enter text criteria for filtering out the SQL trace. Messages panel display columns with statement time, SQL text, profiler counters, etc. Columns contain following information: AbsTime and RelTime is first column that displays either absolute or relative time. User can click button on the toolbar to switch to AbsTime or button to switch to RelTime; SQLType displays SQL type, i.e.: USER SYSTEM NOTIFY ERROR xxx_schema statements sent by the user application to PGNP provider provider generated statements schema change notifications sent to or received from Postgres error condition in the provider statement generated by the provider to support corresponding OLEDB schema Page 66

67 ClientSQL displays either SQL from user application or generated by the provider; Parse, Prepare, Execute, GetRows time in milliseconds spent by provider to parse, prepare, execute statement and to read rows from database server; Rows number of rows read or affected during execution of the SQL; Database, User database and user name for the connection; PID process ID of the application that issued the message; SessId logical OLEDB session ID, it can be used to distinguish between connections made from the same application; CmdId logical OLEDB command ID, reserved for future, can be used to distinguish different commands in the same session; CursorMode displays Forward Only for ADO client cursor or Can scroll backwards for ADO server cursor; Application application name and PID, i.e. origin of the message; CmdType statement type, e.g. SELECT, UPDATE, INSERT, DELETE, CREATE xxx, ALTER, SET, SHOW, DROP xxx, PROCEDURE, INTERNALPROC, START, COMMIT, ROLLBACK, NOTIFY, COPY, etc. Details panel displays detailed SQL text for the selected in Messages panel messages. For a selected message it can display one of or both Client SQL and Executed SQL. Client SQL displays the statement text sent by a user application. Executed SQL displays statement sent by PGNP provider to database. Client SQL and Executed SQL can be different. Latter displays parameter values, e.g.: Client SQL UPDATE tbl SET modified=?, article=? WHERE id=? AND created=? Executed SQL update tbl set modified=[dbtype_dbtimestamp, :00:00.000],article=[DBTYPE_WSTR, Nexus12 ] where id=[dbtype_i4,2000] and created=[dbtype_dbtimestamp, :00:00.000] Main actions in the profiler Action How-to Start profiling an application Select an application in Application/Logs panel and click button in the toolbar. Start profiling all applications on a host computer Select a host in Application/Logs panel and click button in the toolbar. Stop/pause profiling an application Select an application in Application/Logs panel and click button in the toolbar. Stop/pause profiling all applications on a host computer Select a host in Application/Logs panel and click button in the toolbar. Remove application from the list An application can be removed from the list only if it terminated, i.e. has following icon:. Click Clear button ( ) in the toolbar. Start or stop filtering Press on filter button in the toolbar ( ). Switch from absolute to relative time and back Click on button-indicator (to switch to relative time) or on (to switch to absolute time) Filter Filter pane accepts numeric, string and Boolean expressions on variables of a message. The variables names are the same as column names in Messages panel, i.e. clientsql, execute, etc. (see the columns list and descriptions above). Page 67

68 There are several preconfigured filters available by right mouse click in the Filter panel: ClientSQL Like display SQL messages according to the regular expression, e.g. clientsql ilike select(.)+ Show Errors display error messages only, i.e. SQLType = ERROR Hide System hide any system, i.e. the provider generated messages, i.e. SQLType <> SYSTEM ExecuteTime > 1ms display statements with execution time over 1 millisecond, i.e. execute > 1.0 Schema Alterations display only schema alteration messages such as DROP TABLE tbl, i.e. (ClientSQL ilike 'Alter(.)+' ClientSQL ilike 'Create(.)+' ClientSQL ilike 'Drop(.)+') && SQLType!= ERROR Stored Procedures and Notifications display only stored procedures calls and notifications sent and received, i.e CMDType PROCEDURE CMDType INTERNALPROC CMDType NOTIFY Following operators can be used in the expressions: Operator Description + Add variables or numeric constants - Subtract variables or numeric constants, or negate a value * Multiply variables or numeric constants / Divide variables or numeric constants > Greater than >= Greater than or equal to < Less than <= Less than or equal to!= Not equal <> Not equal = Equal Equal! Logical NOT ( ) Sub-expressions grouping && Logical AND Logical OR like Case sensitive regular expression LIKE ilike Case insensitive regular expression LIKE not like Case sensitive regular expression NOT LIKE not ilike Case insensitive regular expression NOT LIKE Page 68

69 Like/ILike operators are based on boost regular expressions evaluation engine. For more details read article: ( Perl Regular Expression Syntax ). Following constants can be used in expressions when comparing a variable to a constant: Variable SQLType CMDType CursorMode Constant USER SYSTEM NOTIFY ERROR COLUMNS_SCHEMA INDEXES_SCHEMA TABLES_SCHEMA CATALOGS_SCHEMA FOREIGN_KEYS_SCHEMA PRIMARY_KEYS_SCHEMA PROCEDURE_COLUMNS_SCHEMA PROCEDURE_PARAMETERS_SCHEMA PROCEDURES_SCHEMA SELECT INSERT UPDATE DELETE CREATE DATABASE CREATE TABLE CREATE VIEW CREATE INDEX CREATE FUNCTION ALTER SET SHOW DROP DATABASE DROP TABLE DROP VIEW DROP INDEX DROP FUNCTION PROCEDURE INTERNALPROC START TRANSACTION COMMIT ROLLBACK NOTIFY COPY Forward only Can scroll backwards Page 69

70 5.4 PGNPUpdate (1.4.x and later) The purpose of PGNPUpdate application is help users to automate the product activation and updates. It may function in either one of the two modes that are automatically determined on the application startup. PGNPUpdate launches in Normal mode when the product is activated and is fully functional. It launches in Activation mode when the product requires activation Working in Normal mode The application displays registration information on startup as shown below: Click Next to get the list of available updates: The updates list is sorted by the publication date so that newest updates are listed first. User can install a specific update by clicking on the link, e.g , or can install the latest available update by just clicking on the Next button. Click Yes in the confirmation box: Page 70

71 Then the update progress and the completion result are shown: Working in Activation mode The application allows the user to manually activate the product in situations when Internet access is restricted from the computer. This assumes that user can copy text files inside LAN and send or receive s from the same or the other computer. On startup, it offers user either to generate an activation request, or to activate the product by previously received activation response: After user entered Product Key and clicked Next button, the Activation Request screen will be shown: Page 71

72 User may the request to the specified address, and restart the application in order to finish the manual activation process. After user received the Activation Response by , he can enter it in the following page: Next page displays result of the activation: Page 72

FollowMe. FollowMe. Microsoft SQL Server Replication Guide. Revision. Date 1.0 05/06/2006. Page 1 of 12

Microsoft SQL Server Replication Guide Revision Date 1.0 05/06/2006 Page 1 of 12 Microsoft SQL Server - Service Logon Rights To ensure the SQL replication works correctly you should first ensure the MSSQLSERVER

More information

DBMoto 6.5 Setup Guide for SQL Server Transactional Replications

DBMoto 6.5 Setup Guide for SQL Server Transactional Replications Copyright This document is copyrighted and protected by worldwide copyright laws and treaty provisions. No portion of this documentation

More information

SQL Server Replication Guide

SQL Server Replication Guide Rev: 2013-08-08 Sitecore CMS 6.3 and Later SQL Server Replication Guide Table of Contents Chapter 1 SQL Server Replication Guide... 3 1.1 SQL Server Replication Overview...

More information

FUSION Installation Guide

FUSION Installation Guide Version 1.0 Page 1 of 74 Content 1.0 Introduction... 3 2.0 FUSION Server Software Installation... 3 3.0 FUSION Client Software Installation... 10 4.0 FUSION NIM Software Installation...

More information

Sage Intelligence Financial Reporting for Sage ERP X3 Version 6.5 Installation Guide

Sage Intelligence Financial Reporting for Sage ERP X3 Version 6.5 Installation Guide Table of Contents TABLE OF CONTENTS... 3 1.0 INTRODUCTION... 1 1.1 HOW TO USE THIS GUIDE... 1 1.2 TOPIC SUMMARY...

More information

ODBC Driver Version 4 Manual

ODBC Driver Version 4 Manual Revision Date 12/05/2007 HanDBase is a Registered Trademark of DDH Software, Inc. All information contained in this manual and all software applications mentioned in this manual

More information

for Networks Installation Guide for the application on the server July 2014 (GUIDE 2) Lucid Rapid Version 6.05-N and later

for Networks Installation Guide for the application on the server July 2014 (GUIDE 2) Lucid Rapid Version 6.05-N and later Copyright 2014, Lucid Innovations Limited. All Rights Reserved Lucid Research

More information

Project management integrated into Outlook

Project management integrated into Outlook InLoox PM 7.x off-line operation An InLoox Whitepaper Published: October 2011 Copyright: 2011 InLoox GmbH. You can find up-to-date information at http://www.inloox.com

More information

Video Administration Backup and Restore Procedures

CHAPTER 12 Video Administration Backup and Restore Procedures This chapter provides procedures for backing up and restoring the Video Administration database and configuration files. See the following

More information

for Networks Installation Guide for the application on the server August 2014 (GUIDE 2) Lucid Exact Version 1.7-N and later

for Networks Installation Guide for the application on the server August 2014 (GUIDE 2) Lucid Exact Version 1.7-N and later Copyright 2014, Lucid Innovations Limited. All Rights Reserved Lucid Research

More information

for Networks Installation Guide for the application on a server September 2015 (GUIDE 2) Memory Booster version 1.3-N and later

for Networks Installation Guide for the application on a server September 2015 (GUIDE 2) Memory Booster version 1.3-N and later Copyright 2015, Lucid Innovations Limited. All Rights Reserved Lucid Research

More information

Server Based Repositories

Enterprise Architect User Guide Series Server Based Repositories Author: Sparx Systems Date: 6/10/2016 Version: 1.0 CREATED WITH Table of Contents Server Based Repositories 3 Create a Project in a MySQL

More information

WebSpy Vantage Ultimate 2.2 Web Module Administrators Guide

WebSpy Vantage Ultimate 2.2 Web Module Administrators Guide This document is intended to help you get started using WebSpy Vantage Ultimate and the Web Module. For more detailed information, please see

More information

TrueEdit Remote Connection Brief

MicroPress Server Configuration Guide for Remote Applications Date Issued: February 3, 2009 Document Number: 45082597 TrueEdit Remote Connection Brief Background TrueEdit Remote (TER) is actually the same

More information

Connectivity Pack for Microsoft Guide

HP Vertica Analytic Database Software Version: 7.0.x Document Release Date: 2/20/2015 Legal Notices Warranty The only warranties for HP products and services are set forth in the express warranty statements

More information

Table of Contents. FleetSoft Installation Guide

FleetSoft Installation Guide Table of Contents FleetSoft Installation Guide... 1 Minimum System Requirements... 2 Installation Notes... 3 Frequently Asked Questions... 4 Deployment Overview... 6 Automating

More information

EVENT LOG MANAGEMENT...

Event Log Management EVENT LOG MANAGEMENT... 1 Overview... 1 Application Event Logs... 3 Security Event Logs... 3 System Event Logs... 3 Other Event Logs... 4 Windows Update Event Logs... 6 Syslog... 6

Postgresql Native Provider (pgnp)

More information

Database migration using Wizard, Studio and Commander. Based on migration from Oracle to PostgreSQL (Greenplum)

Step by step guide. Database migration using Wizard, Studio and Commander. Based on migration from Oracle to PostgreSQL (Greenplum) Version 1.0 Copyright 1999-2012 Ispirer Systems Ltd. Ispirer and SQLWays

More information

Using the SQL Server Linked Server Capability

Using the SQL Server Linked Server Capability SQL Server s Linked Server feature enables fast and easy integration of SQL Server data and non SQL Server data, directly in the SQL Server engine itself.

More information

ODBC Client Driver Help. 2015 Kepware, Inc.

2015 Kepware, Inc. 2 Table of Contents Table of Contents 2 4 Overview 4 External Dependencies 4 Driver Setup 5 Data Source Settings 5 Data Source Setup 6 Data Source Access Methods 13 Fixed Table 14 Table

More information

RDS Migration Tool Customer FAQ Updated 7/23/2015

RDS Migration Tool Customer FAQ Updated 7/23/2015 Amazon Web Services is now offering the Amazon RDS Migration Tool a powerful utility for migrating data with minimal downtime from on-premise and EC2-based

More information

Microsoft SQL Replication

Microsoft SQL Replication v1 28-January-2016 Revision: Release Publication Information 2016 Imagine Communications Corp. Proprietary and Confidential. Imagine Communications considers this document and

More information

INTEGRATING MICROSOFT DYNAMICS CRM WITH SIMEGO DS3

INTEGRATING MICROSOFT DYNAMICS CRM WITH SIMEGO DS3 Often the most compelling way to introduce yourself to a software product is to try deliver value as soon as possible. Simego DS3 is designed to get you

More information

Jet Data Manager 2012 User Guide

Jet Data Manager 2012 User Guide Welcome This documentation provides descriptions of the concepts and features of the Jet Data Manager and how to use with them. With the Jet Data Manager you can transform

More information

WhatsUp Gold v16.2 Database Migration and Management Guide

WhatsUp Gold v16.2 Database Migration and Management Guide Contents CHAPTER 1 How to use this guide CHAPTER 2 Migrating the WhatsUp Gold Microsoft SQL Server 2008 R2 Express Edition database to Microsoft

More information

Tharo Systems, Inc. 2866 Nationwide Parkway P.O. Box 798 Brunswick, OH 44212 USA Tel: 330.273.4408 Fax: 330.225.0099

Introduction EASYLABEL 6 has several new features for saving the history of label formats. This history can include information about when label formats were edited and printed. In order to save this history,

More information

Setting Up ALERE with Client/Server Data

Setting Up ALERE with Client/Server Data TIW Technology, Inc. November 2014 ALERE is a registered trademark of TIW Technology, Inc. The following are registered trademarks or trademarks: FoxPro, SQL Server,

More information

Chapter 4 Accessing Data

Chapter 4: Accessing Data 73 Chapter 4 Accessing Data The entire purpose of reporting is to make sense of data. Therefore, it is important to know how to access data locked away in the database. In this

More information

Chapter-15 -------------------------------------------- Replication in SQL Server

Important Terminologies: What is Replication? Replication is the process where data is copied between databases on the same server or different servers connected by LANs, WANs, or the Internet. Microsoft

More information

User Manual. Onsight Management Suite Version 5.1. Another Innovation by Librestream

User Manual Onsight Management Suite Version 5.1 Another Innovation by Librestream Doc #: 400075-06 May 2012 Information in this document is subject to change without notice. Reproduction in any manner

More information

Upgrading from MSDE to SQL Server 2005 Express Edition with Advanced Services SP2

Upgrading from MSDE to SQL Server 2005 Express Edition with Advanced Services SP2 Installation and Configuration Introduction This document will walk you step by step in removing MSDE and the setup and

More information

GUARD1 PLUS SE Administrator's Manual

GUARD1 PLUS SE Administrator's Manual Version 4.4 30700 Bainbridge Road Solon, Ohio 44139 Phone 216-595-0890 Fax 216-595-0991 info@guard1.com www.guard1.com i 2010 TimeKeeping Systems, Inc. GUARD1 PLUS

More information

National Fire Incident Reporting System (NFIRS 5.0) NFIRS Data Entry/Validation Tool Users Guide

National Fire Incident Reporting System (NFIRS 5.0) NFIRS Data Entry/Validation Tool Users Guide NFIRS 5.0 Software Version 5.3 Prepared for: Directorate of Preparedness and Response (FEMA) Prepared by:

More information

How to move a SQL database from one server to another

How to move a SQL database from one server to another Guide is applicable to these products: * Lucid CoPS, Lucid Rapid, LASS 8-11, LASS 11-15, LADS Plus and Lucid Ability (v6.0x-n) * Lucid Exact v1.xx-n

More information

Linked Servers. Functionality and Performance Considerations for Linked Servers

Linked Servers Linked servers provide SQL Server with access to data from remote data sources. Depending on the remote data source, you can issue queries, perform data modifications, and execute remote

More information

National Fire Incident Reporting System (NFIRS 5.0) Configuration Tool User's Guide

National Fire Incident Reporting System (NFIRS 5.0) Configuration Tool User's Guide NFIRS 5.0 Software Version 5.6 1/7/2009 Department of Homeland Security Federal Emergency Management Agency United States

More information

Aradial Installation Guide

Aradial Technologies Ltd. Information in this document is subject to change without notice. Companies, names, and data used in examples herein are fictitious unless otherwise noted. No part of this document

More information

RSA Security Analytics

RSA Security Analytics Event Source Log Configuration Guide Microsoft SQL Server Last Modified: Thursday, July 30, 2015 Event Source Product Information: Vendor: Microsoft Event Source: SQL Server Versions:

More information

Getting to Know the Tools

Getting to Know the Tools CHAPTER 3 IN THIS CHAPTER Using SQL Server Management Studio One main limitation of the SQL CE 2.0 database released in 2002 was the need to manage the database either using a

More information

e-business Suite Server Install Guide

e-business Suite Server Install Guide Version 1.0 April 2011 Avelo FS Holdings Limited 2011 *These accreditations belong to Avelo FS Limited **This accreditation belongs to Avelo FS Limited and Avelo Portal

More information

How to properly backup and restore FactoryTalk AssetCentre data in the MSSQL database

Protect Your FactoryTalk AssetCentre Data Backing up and Restoring FactoryTalk AssetCentre with Microsoft SQL Server 2005 How to properly backup and restore FactoryTalk AssetCentre data in the MSSQL database

More information

Exporting Binary Data from IBM DB2 for i to Microsoft SQL Server

Exporting Binary Data from IBM DB2 for i to Microsoft SQL Server Copyright This document is copyrighted and protected by worldwide copyright laws and treaty provisions. No portion of this documentation

More information

STATISTICA VERSION 9 STATISTICA ENTERPRISE INSTALLATION INSTRUCTIONS FOR USE WITH TERMINAL SERVER

Notes: STATISTICA VERSION 9 STATISTICA ENTERPRISE INSTALLATION INSTRUCTIONS FOR USE WITH TERMINAL SERVER 1. These instructions focus on installation on Windows Terminal Server (WTS), but are applicable

More information

User Guide. Version 3.2. Copyright 2002-2009 Snow Software AB. All rights reserved.

Version 3.2 User Guide Copyright 2002-2009 Snow Software AB. All rights reserved. This manual and computer program is protected by copyright law and international treaties. Unauthorized reproduction or

More information

Postgres Plus xdb Replication Server with Multi-Master User s Guide

Postgres Plus xdb Replication Server with Multi-Master User s Guide Postgres Plus xdb Replication Server with Multi-Master build 57 August 22, 2012 , Version 5.0 by EnterpriseDB Corporation Copyright 2012

More information

WhatsUp Gold v16.1 Database Migration and Management Guide Learn how to migrate a WhatsUp Gold database from Microsoft SQL Server 2008 R2 Express

WhatsUp Gold v16.1 Database Migration and Management Guide Learn how to migrate a WhatsUp Gold database from Microsoft SQL Server 2008 R2 Express Edition to Microsoft SQL Server 2005, 2008, or 2008 R2

More information

SECURE MOBILE ACCESS MODULE USER GUIDE EFT 2013

SECURE MOBILE ACCESS MODULE USER GUIDE EFT 2013 GlobalSCAPE, Inc. (GSB) Address: 4500 Lockhill-Selma Road, Suite 150 San Antonio, TX (USA) 78249 Sales: (210) 308-8267 Sales (Toll Free): (800) 290-5054

More information

Ekran System Help File

Ekran System Help File Table of Contents About... 9 What s New... 10 System Requirements... 11 Updating Ekran to version 4.1... 13 Program Structure... 14 Getting Started... 15 Deployment Process... 15

More information

@ptitude Observer. Installation Manual. Part No. 32170700 Revision G

Part No. 32170700 Revision G Installation Manual Copyright 2012 by SKF Reliability Systems All rights reserved. Aurorum 30, 977 75 Lulea Sweden Telephone: +46 (0) 31 337 10 00, Fax: +46 (0) 920 134 40

More information

Integrating Relational Data with Netezza s TwinFin Data Warehouse and Analytic Appliance

Integrating Relational Data with Netezza s TwinFin Data Warehouse and Analytic Appliance Copyright This document is copyrighted and protected by worldwide copyright laws and treaty provisions. No portion

More information

GP REPORTS VIEWER USER GUIDE

GP Reports Viewer Dynamics GP Reporting Made Easy GP REPORTS VIEWER USER GUIDE For Dynamics GP Version 2015 (Build 5) Dynamics GP Version 2013 (Build 14) Dynamics GP Version 2010 (Build 65) Last updated

More information

Installation Guide Revision 1.0.

Sage Intelligence Financial Reporting for Sage ERP X3 Installation Guide Revision 1.0. The software described in this document is protected by copyright, And may not be copied on any medium except as specifically

More information

STATISTICA VERSION 12 STATISTICA ENTERPRISE SMALL BUSINESS INSTALLATION INSTRUCTIONS

STATISTICA VERSION 12 STATISTICA ENTERPRISE SMALL BUSINESS INSTALLATION INSTRUCTIONS Notes 1. The installation of STATISTICA Enterprise Small Business entails two parts: a) a server installation, and b)

More information

STATISTICA VERSION 10 STATISTICA ENTERPRISE SERVER INSTALLATION INSTRUCTIONS

Notes: STATISTICA VERSION 10 STATISTICA ENTERPRISE SERVER INSTALLATION INSTRUCTIONS 1. The installation of the STATISTICA Enterprise Server entails two parts: a) a server installation, and b) workstation

More information

WhatsUp Gold v16.1 Installation and Configuration Guide

WhatsUp Gold v16.1 Installation and Configuration Guide Contents Installing and Configuring Ipswitch WhatsUp Gold v16.1 using WhatsUp Setup Installing WhatsUp Gold using WhatsUp Setup... 1 Security guidelines

More information

Veritas Cluster Server Database Agent for Microsoft SQL Configuration Guide

Veritas Cluster Server Database Agent for Microsoft SQL Configuration Guide Windows 2000, Windows Server 2003 5.0 11293743 Veritas Cluster Server Database Agent for Microsoft SQL Configuration Guide Copyright

More information

SAP Business Objects Business Intelligence platform Document Version: 4.1 Support Package 7 2015-11-24. Data Federation Administration Tool Guide

SAP Business Objects Business Intelligence platform Document Version: 4.1 Support Package 7 2015-11-24 Data Federation Administration Tool Guide Content 1 What's new in the.... 5 2 Introduction to administration

More information

Upgrading to Document Manager 2.7

Upgrading to Document Manager 2.7 22 July 2013 Trademarks Document Manager and Document Manager Administration are trademarks of Document Logistix Ltd. TokOpen, TokAdmin, TokImport and TokExRef are registered

More information

Portions of this product were created using LEADTOOLS 1991-2009 LEAD Technologies, Inc. ALL RIGHTS RESERVED.

Installation Guide Lenel OnGuard 2009 Installation Guide, product version 6.3. This guide is item number DOC-110, revision 1.038, May 2009 Copyright 1992-2009 Lenel Systems International, Inc. Information

More information

Spector 360 Deployment Guide. Version 7.3 January 3, 2012

Spector 360 Deployment Guide Version 7.3 January 3, 2012 Table of Contents Deploy to All Computers... 48 Step 1: Deploy the Servers... 5 Recorder Requirements... 52 Requirements... 5 Control Center Server

More information

WhatsUp Gold v16.2 Installation and Configuration Guide

WhatsUp Gold v16.2 Installation and Configuration Guide Contents Installing and Configuring Ipswitch WhatsUp Gold v16.2 using WhatsUp Setup Installing WhatsUp Gold using WhatsUp Setup... 1 Security guidelines

More information

HP IMC Firewall Manager

HP IMC Firewall Manager Configuration Guide Part number: 5998-2267 Document version: 6PW102-20120420 Legal and notice information Copyright 2012 Hewlett-Packard Development Company, L.P. No part of this

More information

BUILDER 3.0 Installation Guide with Microsoft SQL Server 2005 Express Edition January 2008

BUILDER 3.0 Installation Guide with Microsoft SQL Server 2005 Express Edition January 2008 BUILDER 3.0 1 Table of Contents Chapter 1: Installation Overview... 3 Introduction... 3 Minimum Requirements...

More information

Issue Tracking Anywhere Installation Guide

TM Issue Tracking Anywhere Installation Guide The leading developer of version control and issue tracking software Table of Contents Introduction...3 Installation Guide...3 Installation Prerequisites...3

More information

Moving the TRITON Reporting Databases

Moving the TRITON Reporting Databases Topic 50530 Web, Data, and Email Security Versions 7.7.x, 7.8.x Updated 06-Nov-2013 If you need to move your Microsoft SQL Server database to a new location (directory,

More information

WINDOWS PROCESSES AND SERVICES

OBJECTIVES: Services o task manager o services.msc Process o task manager o process monitor Task Scheduler Event viewer Regedit Services: A Windows service is a computer program that operates in the background.

More information

WhatsUp Gold v16.3 Installation and Configuration Guide

WhatsUp Gold v16.3 Installation and Configuration Guide Contents Installing and Configuring WhatsUp Gold using WhatsUp Setup Installation Overview... 1 Overview... 1 Security considerations... 2 Standard

More information

Vault Project - Plant Database Replication. Contents. Software Requirements: AutoCAD Plant 3D 2016 and AutoCAD P&ID 2016

Vault Project - Plant Database Replication This document describes how to replicate the plant database for a vault project between WAN connected locations. By replicating both the vault and the plant database

More information

GO!NotifyLink. Database Maintenance. GO!NotifyLink Database Maintenance 1

GO!NotifyLink Database Maintenance GO!NotifyLink Database Maintenance 1 Table of Contents Database Maintenance 3 Database Cleanup... 3 Database Backups... 3 Database Configuration... 4 The Procedure via

More information

User's Guide - ODBC based Scanning in Enterprise Recon

User's Guide - ODBC based Scanning in Enterprise Recon 1. Contents 1. Contents 2. General Information 3. System Overview 4. System Requirements and Special Notes 5. Supported Databases 6. How does it work?

More information

How to Replicate BillQuick 2003 database on SQL Server 2000.

How to Replicate BillQuick 2003 database on SQL Server 2000. This article provides a step-by-step procedure for replicating the BillQuick 2003 database using Microsoft SQL server 2000 and allowing it to

More information

Installation Instruction STATISTICA Enterprise Small Business

Installation Instruction STATISTICA Enterprise Small Business Notes: ❶ The installation of STATISTICA Enterprise Small Business entails two parts: a) a server installation, and b) workstation installations

More information

Support Document: Microsoft SQL Server - LiveVault 7.6X

Contents Preparing to create a Microsoft SQL backup policy... 2 Adjusting the SQL max worker threads option... 2 Preparing for Log truncation... 3 Best Practices... 3 Microsoft SQL Server 2005, 2008, or

More information

Installing OneStop Reporting Products

Installing OneStop Reporting Products Contents 1 Introduction 2 Product Overview 3 System Requirements 4 Deployment 5 Installation 6 Appendix 2010 OneStop Reporting http://www.onestopreporting.com support@onestopreporting.com

More information

Outlook Profile Setup Guide Exchange 2010 Quick Start and Detailed Instructions

HOSTING Administrator Control Panel / Quick Reference Guide Page 1 of 9 Outlook Profile Setup Guide Exchange 2010 Quick Start and Detailed Instructions Exchange 2010 Outlook Profile Setup Page 2 of 9 Exchange

More information

Spector 360 Deployment Guide. Version 7

Spector 360 Deployment Guide Version 7 December 11, 2009 Table of Contents Deployment Guide...1 Spector 360 DeploymentGuide... 1 Installing Spector 360... 3 Installing Spector 360 Servers (Details)...

More information

3 Setting up Databases on a Microsoft SQL 7.0 Server

3 Setting up Databases on a Microsoft SQL 7.0 Server Overview of the Installation Process To set up GoldMine properly, you must follow a sequence of steps to install GoldMine s program files, and the other

More information

Remote Management System

RMS Copyright and Distribution Notice November 2009 Copyright 2009 ARTROMICK International, Inc. ALL RIGHTS RESERVED. Published 2009. Printed in the United States of America WARNING: ANY UNAUTHORIZED

More information

Release 2.1 of SAS Add-In for Microsoft Office Bringing Microsoft PowerPoint into the Mix ABSTRACT INTRODUCTION Data Access

Release 2.1 of SAS Add-In for Microsoft Office Bringing Microsoft PowerPoint into the Mix Jennifer Clegg, SAS Institute Inc., Cary, NC Eric Hill, SAS Institute Inc., Cary, NC ABSTRACT Release 2.1 of SAS

More information

Preparing to Install SQL Server 2005

Preparing to Install SQL Server 2005 Depending on your requirements, Microsoft SQL Server 2005 Setup installs a new instance of SQL Server. The following topics address important considerations for the

Postgresql Native Provider

More information

Informatica Cloud & Redshift Getting Started User Guide

Informatica Cloud & Redshift Getting Started User Guide 2014 Informatica Corporation. No part of this document may be reproduced or transmitted in any form, by any means (electronic, photocopying, recording

More information

IBM Sterling Control Center

IBM Sterling Control Center System Administration Guide Version 5.3 This edition applies to the 5.3 Version of IBM Sterling Control Center and to all subsequent releases and modifications until otherwise

More information

To install Multifront you need to have familiarity with Internet Information Services (IIS), Microsoft.NET Framework and SQL Server 2008.

Znode Multifront - Installation Guide Version 6.2 1 System Requirements To install Multifront you need to have familiarity with Internet Information Services (IIS), Microsoft.NET Framework and SQL Server

More information

Tutorial: How to Use SQL Server Management Studio from Home

Tutorial: How to Use SQL Server Management Studio from Home Steps: 1. Assess the Environment 2. Set up the Environment 3. Download Microsoft SQL Server Express Edition 4. Install Microsoft SQL Server Express

More information

Migrating MSDE to Microsoft SQL 2008 R2 Express

How To Updated: 11/11/2011 2011 Shelby Systems, Inc. All Rights Reserved Other brand and product names are trademarks or registered trademarks of the respective holders. If you are still on MSDE 2000,

More information

Forms Printer User Guide

Forms Printer User Guide Version 10.51 for Dynamics GP 10 Forms Printer Build Version: 10.51.102 System Requirements Microsoft Dynamics GP 10 SP2 or greater Microsoft SQL Server 2005 or Higher Reporting

More information

Application Note 116: Gauntlet System High Availability Using Replication

Customer Service: 425-487-1515 Technical Support: 425-951-3390 Fax: 425-487-2288 Email: info@teltone.com support@teltone.com Website: www.teltone.com Application Note 116: Gauntlet System High Availability

More information

INTRODUCTION: SQL SERVER ACCESS / LOGIN ACCOUNT INFO:

INTRODUCTION: You can extract data (i.e. the total cost report) directly from the Truck Tracker SQL Server database by using a 3 rd party data tools such as Excel or Crystal Reports. Basically any software

More information

Bitrix Site Manager ASP.NET. Installation Guide

Bitrix Site Manager ASP.NET Installation Guide Contents Introduction... 4 Chapter 1. Checking for IIS Installation... 5 Chapter 2. Using An Archive File to Install Bitrix Site Manager ASP.NET... 7 Preliminary

More information

1.0. ChromLab Software User Management Edition User Guide Version 1.0

1.0 ChromLab Software User Management Edition User Guide Version 1.0 ChromLab Software User Management Edition User Guide Version 1.0 Bio-Rad Technical Support Department The Bio-Rad Technical Support

More information

Results CRM 2012 User Manual

Results CRM 2012 User Manual A Guide to Using Results CRM Standard, Results CRM Plus, & Results CRM Business Suite Table of Contents Installation Instructions... 1 Single User & Evaluation Installation

More information

SAP BusinessObjects Business Intelligence platform Document Version: 4.0 Support Package Live Office User Guide

SAP BusinessObjects Business Intelligence platform Document Version: 4.0 Support Package 8-2013-10-31 Table of Contents 1 About this document...5 1.1 Who should read this document....5 1.2 Document history....5

More information

Setting up an MS SQL Server for IGSS

Setting up an MS SQL Server for IGSS Table of Contents Table of Contents...1 Introduction... 2 The Microsoft SQL Server database...2 Setting up an MS SQL Server...3 Installing the MS SQL Server software...3

More information

ADOBE READER AND ACROBAT

ADOBE READER AND ACROBAT IFILTER CONFIGURATION Table of Contents Table of Contents... 1 Overview of PDF ifilter 11 for 64-bit platforms... 3 Installation... 3 Installing Adobe PDF IFilter... 3 Setting

More information

USING STUFFIT DELUXE THE STUFFIT START PAGE CREATING ARCHIVES (COMPRESSED FILES)

USING STUFFIT DELUXE StuffIt Deluxe provides many ways for you to create zipped file or archives. The benefit of using the New Archive Wizard is that it provides a way to access some of the more powerful

More information

Accessing Your Database with JMP 10 JMP Discovery Conference 2012 Brian Corcoran SAS Institute

Accessing Your Database with JMP 10 JMP Discovery Conference 2012 Brian Corcoran SAS Institute JMP provides a variety of mechanisms for interfacing to other products and getting data into JMP. The connection

Postgresql Native Ole Db Provider (pgnp)

More information

ILTA HANDS ON Securing Windows 7

Securing Windows 7 8/23/2011 Table of Contents About this lab... 3 About the Laboratory Environment... 4 Lab 1: Restricting Users... 5 Exercise 1. Verify the default rights of users... 5 Exercise 2. Adding

More information

Getting Started with Telerik Data Access. Contents

Contents Overview... 3 Product Installation... 3 Building a Domain Model... 5 Database-First (Reverse) Mapping... 5 Creating the Project... 6 Creating Entities From the Database Schema... 7 Model-First

More information

Advanced Event Viewer Manual

Advanced Event Viewer Manual Document version: 2.2944.01 Download Advanced Event Viewer at: http://www.advancedeventviewer.com Page 1 Introduction Advanced Event Viewer is an award winning application

More information

HP A-IMC Firewall Manager

HP A-IMC Firewall Manager Configuration Guide Part number: 5998-2267 Document version: 6PW101-20110805 Legal and notice information Copyright 2011 Hewlett-Packard Development Company, L.P. No part of this

More information