SQL Server 64-bit Linked Server woes   Checkout our PostGIS in Action book 2nd Edition. First chapter is a free download

Postgres OnLine Journal

Blog View    Periodical View   PostGIS in Action   About the Authors   Consulting



Monday, February 21. 2011


SQL Server 64-bit Linked Server woes

Printer Friendly

We were setting up another SQL Server 2005 64-bit where we needed a linked server connection to our PostgreSQL 9.0 server. This is something we've done before so not new and something we documented in Setting up PostgreSQL as a Linked Server in Microsoft SQL Server 64-bit. What was different this time is that we decided to use the latest version of the new PostgreSQL 64-bit drivers now available main PostgreSQL site http://www.postgresql.org/ftp/odbc/versions/msi/. Sadly these did not work for us. They seemed to work fine in our MS Access 2010 64-bit install, but when used via SQL Server, SQL Server would choke with a message:

Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider "MSDASQL"

If you tried to do a query with them. You can however see all the tables via the linked server tab.

The query runs on PostgreSQL fine and we can see it in the logs. The odbc log looks fine, so seems to be an issue when its coming back to SQL Server. So for the time being until we figure out if its an issue on our end or something wrong with the published 64-bit ODBC drivers, we'll be using these http://code.google.com/p/visionmap/wiki/psqlODBC

64-bit Official PostgreSQL drivers

The odbc 64 bit installs both a ANSI and Unicode. We tried both and both gave same errors

Slapo provided a solution which seemed to solve the problem. The trick is to use MSDASQL.1 for the provider instead of MSDASQL.
EXEC master.dbo.sp_addlinkedserver @server = N'PGSERVER', @srvproduct=N'PostgreSQL',
@provider=N'MSDASQL', @provstr=N'Driver=PostgreSQL ANSI(x64);uid=pguser;port=5432;Server=localhost;database=mydb;pwd=pgpassword'
EXEC master.dbo.sp_addlinkedserver @server = N'PGSERVER', @srvproduct=N'PostgreSQL',
@provider=N'MSDASQL.1', @provstr=N'Driver=PostgreSQL ANSI(x64);uid=pguser;port=5432;Server=localhost;database=mydb;pwd=pgpassword'

EXEC master.dbo.sp_addlinkedserver @server = N'PGSERVER', @srvproduct=N'PostgreSQL',
@provider=N'MSDASQL', @provstr=N'Driver=PostgreSQL Unicode(x64);uid=pguser;port=5432;Server=localhost;database=mydb;pwd=pgpassword'

EXEC master.dbo.sp_addlinkedserver @server = N'PGSERVER', @srvproduct=N'PostgreSQL',
@provider=N'MSDASQL.1', @provstr=N'Driver=PostgreSQL Unicode(x64);uid=pguser;port=5432;Server=localhost;database=mydb;pwd=pgpassword'

Vision Map 64-bit drivers

The visionmap on just installs one so registration looks like (this one works fine)

EXEC master.dbo.sp_addlinkedserver @server = N'PGSERVER', @srvproduct=N'PostgreSQL',
@provider=N'MSDASQL', @provstr=N'Driver=PostgreSQL 64-bit ODBC Drivers;uid=pguser;port=5432;Server=localhost;database=mydb;pwd=pgpassword'

Our connection strings -- using the visionmap one look like:

-- The visionmap on just installs one so registration looks like (this one works fine)
EXEC master.dbo.sp_addlinkedserver @server = N'PGSERVER', @srvproduct=N'PostgreSQL',
@provider=N'MSDASQL', @provstr=N'Driver=PostgreSQL 64-bit ODBC Drivers;uid=pguser;port=5432;Server=localhost;database=mydb;pwd=pgpassword'

Test query

Our query looked something like below, but any query we used gave similar error with the PostgreSQL site 64-bit drivers.

SELECT a.*
FROM OPENQUERY(PGSERVER, 'SELECT table_name FROM information_schema.tables') As a;
Posted by Leo Hsu and Regina Obe in 9.0, basics, beginner, sql server at 19:10 | Comments (12)

Trackbacks
Trackback specific URI for this entry

PingBack
Weblog: www.postgresonline.com
Tracked: Apr 18, 23:00


Comments
Display comments as (Linear | Threaded)

OLEDB might be a better option on the SQL Server end. Have you tried http://www.pgoledb.com/ ?
#1 MattK on 2011-02-21 20:03
Matt,

I really like the pgoledb product. I'm sorry we haven't had a chance to describe it though we were meaning to way back. We'll try to detail it in our next product showcase and what features you get that you don't get with the ODBC drivers.

For this particular project I think it might be overkill (in sense of pricing not ease of use etc.), because it is something that will be used for one query and one query alone and may need to be deployed in many places. Although this may change as the project progresses.
#1.1 Regina on 2011-02-22 09:04
You might want to try MSDASQL.1 instead of just MSDASQL. I think it's what helped me when I had this error:
'Cannot get the column information from OLE DB provider "MSDASQL"'.

Now we have a different problem, although that's probably due to the way SQL Server works with ODBC and OLE DB drivers - if a query that uses a linked server fails, all queries in SSIS jobs that use that or another linked server also fail and only a restart of the server instance helps. This happens on SQL Server 2005 Standard SP4.
Have you encountered this issue?
#2 Slapo (Homepage) on 2011-02-23 07:22
Slapo,

Were you using the ODBC driver on the Postgres website. I didn't see MSDASQL.1 listed under my Linked Servers/Providers tab. Is that something you have to install separately.

Nope haven't encountered that issue but don't use SSIS anymore mostly because we found it was easier to do our data transfers with a webscript since we didn't have to contend with pushing the SSIS package back into SQL Server and it was a bit more portable too and we could then just use .NET drivers and manage everything with our own custom table of jobs and even tie directly into our applications for users to manage.
#2.1 Regina on 2011-02-26 10:46
Yes, the one from PostgreSQL website should be the one. You won't see MSDASQL.1 among the Providers, you just use @provider=N'MSDASQL.1' in your query. It's why it took me more than a day to notice it in one query I got to when searching. I suspect it's just alternative access method to the same API, but I could be wrong.

We use SSIS mostly because of the Job agent (I think, but I wasn't the one who made the decision to use SSIS). I'd prefer to have our own fairly simple system, but that's unlikely to happen unless I'd create it (which I hope to do later this year, along with plenty of other things).
#2.1.1 Slapo (Homepage) on 2011-02-26 15:17
Slapo,

Thanks that seemed to do the trick. I'll update the blog entry to reflect that. Thanks a lot.
#2.1.1.1 Regina on 2011-02-27 00:08
You're welcome :)

It's odd that "MSDASQL" often works but sometimes "MSDASQL.1" is necessary. I haven't found out yet what could be the reason for this, but as long as it works, it doesn't matter too much.
#2.1.1.1.1 Slapo (Homepage) on 2011-02-28 04:58
Slapo,

As far as Job agent goes, we've been pretty happy with PgAgent and migrating jobs as we upgrade servers is pretty simple since all the jobs are nicely segmented in the pgAgent schema (usually installed in the postgres database).

http://www.postgresonline.com/journal/archives/19-Setting-up-PgAgent-and-Doing-Scheduled-Backups.html

Then we use that to kick off our own .net or batch scripts.

We also use it for backup (including backing up the SQL Server and MySQL databases we have). I was going to amend that script because our new approach rather than hard-coding the databases is to create a folder structure on the network
to house each database and then the script will create a backup of the database if the folder exists. Much easier for management since our developers no longer need to change the script to have their database backed up.
#2.1.1.2 Regina on 2011-03-03 07:41
Thanks Regina,

it looks pretty handy and the arguments in the article are quite reasonable, so we'll consider it and evaluate it when the time comes.

Thanks again. :)
#2.1.1.2.1 Slapo (Homepage) on 2011-03-03 12:42
Hi,

I came across a very similar sounding problem and I think I may have found a solution. Essentially you have to un-check the "Allow inprocess" option in the MSDASQL provider.

The details can be found here: http://www.bisql.net/2011/02/sql_pg_linked/

Regards, Ash.
#3 Ashley Burton (Homepage) on 2011-02-28 09:56
Cheers, that seems to be it. :D
#3.1 Slapo (Homepage) on 2011-03-03 05:33
Good work around. In our case, we had similar problem as you mentioned. Running the Instcat.sql worked just fine. All our 2000 servers are 32-bit and 2005/2008 servers are 64-bit.
#4 Peter Smith (Homepage) on 2012-02-07 04:52

The author does not allow comments to this entry

Entry's Links

Quicksearch

Calendar

Back November '24
Mon Tue Wed Thu Fri Sat Sun
        1 2 3
4 5 6 7 8 9 10
11 12 13 14 15 16 17
18 19 20 21 22 23 24
25 26 27 28 29 30  

Categories

All categories

Archives

Subscribe

  • XML

Blog Administration

Open login screen