On 2009-05-31, patrick imbault <pat (AT) free (DOT) fr> wrote:
Point of order technical support is for helping you, news groups
are for discussing things. If you want someone who is suposed to
help you then you should make some kind of arangement for technical
support, this generally involves paying money for the privilage of
bothereing someone with your problems.

marc

"patrick imbault" <pat (AT) free (DOT) fr> writes:

Given that you didn't know the answer, isn't assuming in which domain
the problem is simplest sort of putting the cart before the horse?
I've only had experience linking MS SQL databases to other MS SQL
databases, but aren't inter-database connections typically
database-server specific?

Now, there may be someone in the PostgreSQL arena that has experience
in this particular combination, but I have to admit to also feeling
when reading your first note that it was more a question for an MS SQL
forum. The pointers in the response may have been slightly terse, but
I actually think they pointed in the right direction.

In other words, isn't your question likely more for an expert on the
MS SQL side than on the PostgreSQL side? Especially if you are using
(as implied by your original note) platform mechanisms such as OleDB
or ODBC to make the connection? From the PostgreSQL side it's likely
to appear just as a normal client connection in that case.

To be honest, I actually wasn't aware an MS SQL database server could
proxy connections over an arbitrary ODBC link (I do recall seeing
Oracle do it though), but it could be a recent feature. The last time
I did cross-server linking (with MS SQL 2K I think) it was implemented
as an MS SQL proprietary RPC link, which I doubt would support
PostgreSQL as a back-end. Or maybe we just used that mechanism since
both servers were MS SQL.

Regardless however, I suspect you'll have more luck with MS resources
since it sounds like the bulk of the configuration (and the specifics
of implementation, capabilities, restrictions, etc..) will all lie
on the MS SQL side of the equation.

-- David

"patrick imbault" <pat (AT) free (DOT) fr> writes:

My guess is there's not much benefit of the OLE DB provider over ODBC
for this purpose, unless MS SQL supports OLE DB better. So I'd just
use whichever one you can get to work first.

I'd think that most of the OLE DB differences over ODBC affect
application programmers more, in terms of interface, whereas in this
case I'd assume that MS SQL is just issuing raw SQL queries to the
linked database on behalf of the master query. So while I might see
preferring OLE DB for application use - especially if in an
environment where ADO is preferred, for example - I would probably
start with ODBC for inter-database since it's been around longer and
is lower level, so may have fewer gotchas in an implementation, though
I have no specific knowledge to back up that assumption :-)

Thanks - more specifics are better, since this eliminates basic setup
issues and focuses on a specific issue interacting PostgreSQL as the
server. It would be even better if you included actual error messages
or precise descriptions of what the "problem" is in the phrase "The
problem is to map ...".

Including this level of detail in your original question might have
helped guide everyone towards more relevant responses right from the
beginning. Although to be honest, it still sounds like your issues
may be best addressed in the groups related to the ODBC driver and OLE
DB provider, since they will have the most expertise with the actual
drivers you are trying to use, and your problem appears related to how
they map PostgreSQL data types to the client of their interface.

Not sure if the other back-ends that you have working are using text
fields or if they stick with the more standard char/varchar, but
perhaps that is part of the difference in behavior. Technically, text
isn't a SQL standard type, though most engines have some form of it
nowadays, so it's probably at some risk of misinterpretation when
translating between systems.

Two quick thoughts. First, I believe the stock PostgreSQL ODBC driver
(not sure about the OLE DB provider) will translate text fields into a
"long varchar" ODBC data type, since the unlimited text field size can
exceed the limits of a plain varchar. You'd think that would map to
MS SQL's own text, but maybe that's an issue. If you clear that
option in the ODBC data source advanced options it should come across
as a plain varchar, but potentially could be subject to more
truncation on the MS SQL side depending on how it handles maximum
limits for a varchar field.

Alternatively, the default ODBC configuration is set to return the
maximum possible field size if queried for the size of a varchar/text
field. In my (8.3) ODBC driver, it appears configured to return 8190
as the maximum. That ought to be just fine for MS SQL, but MS SQL 2k5
does have 8000 as a varchar limit - maybe there's a crossover
somewhere where the text/longvarchar gets interpreted as a varchar
with too long a length? (You can lower that in the driver config)

I know that I've certainly accessed schemas with tables with text
fields without problem through the ODBC driver - most of my issues
centered around booleans if anything. It seems like it ought to be
interoperable with MS SQL if the other engines work.

If you haven't, I'd also investigate enabling logging of the ODBC
driver across some of the activities that cause errors, as well as
statement logging on the PostgreSQL server side if possible. It can
be voluminous (especially the ODBC SQL log), but might help identify
just what part of the transaction is behind whatever faulty behavior
you are seeing.

-- David