0

I have followed the steps listed in this answer from another thread but when I try to click Next > on the destination screen I get the error:

TITLE: SQL Server Import and Export Wizard
------------------------------

The operation could not be completed.

------------------------------
ADDITIONAL INFORMATION:

ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

I have:

  1. Installed PostgreSQL ODBC Driver on the source server
  2. Added in ODBC Data Source Administrator (32-bit) a PostgreSQL Unicode driver User DSN (also tried a System DSN) source named postgres (settings are all correct as the Test runs fine)
  3. Inside SSMS right-clicked on the database I want to export from and selected Tasks -> Export Data
  4. Chose SQL Server Native Client on the source screen and entered the credentials for SQL Authentication
  5. Chose .NET Framework Data Provider for ODBC as the destination driver
  6. Entered the below connection string
  7. Hit Next > and the error pops up

My connection string is: Dsn=postgres;Driver={PostgreSQL Unicode};server=<servername>;port=<port>;database=postgres;uid=<user>;pwd=<pass> (the things between <> are actually filled out, just redacted for post).

Just in case it matters, I am trying to export from my SQL database into my PostgreSQL database.

Here are versions for everything:

  • SQL Server Management Studio: v17.5 (About window shows this and below value)
  • Microsoft SQL Server Management Studio: 14.0.17224.0
  • Source Database: SQL Server 11.0.2100.60
  • Source Server: Windows Server 2012 R2
  • Source ODBC: 32-bit
  • Destination Database: pgAdmin 3.0
  • Destination Server: Windows Server 2012 R2
WolfieeifloW
  • 605
  • 1
  • 8
  • 28
  • (1) The "error in [your] title" doesn't appear to be complete. Please put the full error into the body of your question. (2) I suggest using a System DSN rather than a User DSN, and given that you *have* such a DSN, reducing your connection string to `Dsn=postgres;uid=;pwd=` as all the other attributes should be in that DSN... – TallTed Feb 27 '19 at 18:56
  • @TallTed I have added in the complete error. I tried a System DSN on the source server and reducing the connection string (`Dsn=postgres;uid=;pwd=`) but the same error is occuring. If it matters, with that shortened string the `Source -> Driver` box is empty/missing. – WolfieeifloW Feb 27 '19 at 20:05
  • 1
    (1) The DSN specified by `NamedConnectionString`→`Dsn` includes the `Driver` setting, so there's no need for `Source`→`Driver`. (2) Are all components current? You've provided no version details -- and they are often relevant. (3) I'm wondering if there's a bitness issue. See articles [1](http://wikis.openlinksw.com/UdaWikiWeb/Win32vs64OdbcAdmin) and [2](http://wikis.openlinksw.com/UdaWikiWeb/MsSqlOn64bitWin). – TallTed Feb 27 '19 at 20:55
  • @TallTed I've added all version information I can think of to OP. Please let me know if there's anything else that I've left out that's important. – WolfieeifloW Feb 27 '19 at 21:31
  • (1) Are *all* components, including Windows, 32-bit? Or only the "source ODBC" (by which I think you mean the ODBC driver)? (2) I'm fairly sure your "destination database" is not pgAdmin 3.0, but some version of the PostgreSQL DBMS; please give that version. (3) Is SSMS on the same or a different machine than SQL Server 2012? (4) Note that your SQL Server 2012 is [the original, unpatched, release version](http://sqlserverversions.blogspot.com/#sql2012), and you will likely benefit from applying [Service Pack 4](https://www.microsoft.com/en-us/download/details.aspx?id=56040). – TallTed Feb 27 '19 at 22:15
  • @TallTed The OS' are 64-bit. The DSN are 32-bit. When I open my pgAdmin console it says "pgAdmin 4 Version 3.0". SSMS is on a server with Server 2012 (hosting source DB). The destination is on a different server, also running Server 2012. Upgrading the servers aren't in my pay grade, unfortunately. – WolfieeifloW Mar 01 '19 at 14:49
  • (1) Just as SSMS is not SQL Server, pgAdmin is not PostgreSQL. Try executing `SELECT version();` through pgAdmin, or through your ODBC DSN (and maybe put output into your question). (2) Is SQL Server 2012 64-bit or 32-bit? Check output of `SELECT @@version;` (maybe put it into your question) and again, see [this article](http://wikis.openlinksw.com/UdaWikiWeb/MsSqlOn64bitWin). (3) I'm pretty sure you just need to install the 64-bit ODBC Driver for PostgreSQL, and configure a System DSN for it. (For cleanest environment, I suggest dropping the PostgreSQL User DSN(s) you've created.) – TallTed Mar 01 '19 at 17:20

0 Answers0