Sql-server – check if linked server table column exists

linked-serverms accesssql-server-2008

I have several access databases that I'm copying over to the sql server, connecting to as a linked server, and then pulling data from each night.

I need to be able to identify if the current access db the linked server is pointing too has a particular column. If not I'll need to create the column before copying the next access db over.

Is it possible to check if an access column exists through a linked server connection?

Best Answer

You'll have to make a connection first using VBA and then check with sys.columns

SELECT COUNT(*)
FROM sys.columns c
WHERE c.object_id = OBJECT_ID('MyTable') AND c.name = 'ExpectedCol'

Or use COLUMNPROPERTY

That is, check the metadata separately trying to query the column

Related Solutions

Sql-server – Microsoft SQL server 2008 and linked servers

Using loop back linked servers isn't the greatest idea. This can lead to some interesting performance problems once you start doing joins between local tables and "remote" tables as you are going through a linked server so suddenly instead of doing a normal join you may end up transferring the entire table across the linked server (which means possibly reading it off of the disk), stuffing it into tempdb and joining to a hash table which doesn't have any indexes on it.

Don't setup linked servers unless you actually need them. You can get some very powerful servers these days for not much money (I'm currently working on a server which has 32 cores and 256 Gigs of RAM) so scaling a server up isn't all that hard. If the application actually needs to be scaled out across multiple servers you'll need someone who is used to doing very complex tuning to help out to make sure that you aren't shooting yourself in the foot in the process (which isn't hard to do).

Once you do get to needing linked servers, there's going to be so much stuff that needs to be done to move the database and test everything going through and fixing the code isn't going to seem all that bad.

Sql-server – How to connect to a database which is a linked server via ODBC

You have created a linked server, not a linked database, so there is no list of databases to select from. You need to either use a "four-part name" or use the OPENQUERY() function.

Four-part names describe an object on another server. Usually a four-part name would look like "servername.databasename.schemaname.tablename", but for Oracle you apparently need to leave the database name out. A query using a four-part name for Oracle might look like this:

SELECT name FROM OracleSvr..joe.titles WHERE id = 101

where "OracleSvr" is the name of the linked server.

Alternatively, here is an example of a statement that uses OPENQUERY. It should do the same thing, some people find this style easier to read.

SELECT * FROM OPENQUERY (OracleSvr, 'SELECT name FROM joe.titles WHERE id = 101')

OPENQUERY is documented here.

No matter which style of query you choose, you would connect via your Python program to the SQL Server and then run the query.

You can do this via ODBC, you don't need to specify the database name when setting up the connection, you do it at query time as described above.

Best Answer

Related Solutions

Sql-server – Microsoft SQL server 2008 and linked servers

Sql-server – How to connect to a database which is a linked server via ODBC

Related Question