I’m developing a small app utilizing SQL Server 2008 for some testing.
Along with the local database, I’m designing the app to query another
database, which is another SQL Server db linked to my local MSSQL
installation.
When querying this second, linked database, I must use the fully
qualified name:
[host].[dbname].[schema].[table]
But SQL Server will throw up an error if I try to run a query where
the fully qualified name prefixes a table name (but it is still required
in the FROM clause).
In my Rails app, I set up a model aliased to the fully qualified name:
class MyData< ActiveRecord::Base
set_table_name(“SERVER.linkedDB.dbo.MyData”)
end
Now, ActiveRecord appends the aliased fully-qualified name to each
column name when I run a query, which is a no-no for SQL. So right now,
my Rails queries look like this:
SELECT SERVER.linkedDB.dbo.MyData.* FROM SERVER.linkedDB.dbo.MyData
Which fails. A working query would look like this:
SELECT MyData.* FROM SERVER.linkedDB.dbo.MyData
or even
SELECT t.* FROM SERVER.linkedDB.dbo.MyData As t
So my question is, is there a way to configure my model to append only
table names as prefixes when referencing columns, or perhaps force an
“AS” clause, while still setting the FROM clause to the fully qualified
name?
I also understand there is the option of using the find_by_sql method to
work around this, which I’ve tried and works just fine, but just for
learning purposes I was wondering if this could be done in a more
natural way in AR.
Thanks!