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.
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.
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?
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.
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).
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).
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.