4

I am migrating a jet database to MS SQL Server. The application is well established and contains tens of thousands of lines of code and possibly well over a 100 thousand line of code.

When using adodb recordsets (in vb.net) to query a jet database and joining tables that have columns with the same name (select a.foo, b.foo), you get TableName.Column returned for those fields that have the same name.
But when querying a sql server (MS SQL), you get the ColumnNames only so if you have two tables a and b and with each having a column with the name foo, you would get two columns both named "foo" where as with the jet database you get a.foo and b.foo

I am fully aware that you can use "as" in your select statement to alias the column name like (select a.foo as a_foo, b.foo as b_foo) but I don't want to do that - I don't have time to go through the thousand or so of select statements in the app.

So I am asking if anyone knows of a way that SQL would be able to behave like a jet database and return the TableName.ColumnName for fields with similar name,

or if someone knows for sure that mssql can only return ColumnName without TableName, then which database engine can do what Jet database does?

By the way, the Jet database does that only when using a direct connection string syntax, otherwise it would behave the same way as SQL Server if accessing the data via ODBC.

starball
  • 20,030
  • 7
  • 43
  • 238
abdulla
  • 41
  • 1
  • 1
    I know you don't want to do it, but whilst you're waiting for an answer, you may as well get going on working through your code and making it *work* with the majority of SQL database systems, rather than hoping there's a way to get SQL Server or some other SQL product to act in exactly the same (I dare say) broken way that Jet does. – Damien_The_Unbeliever May 01 '18 at 06:15
  • @Damien_The_Unbeliever Maybe I misread, but isn't it also possible to get column names via something like JDBC? Maybe there is an API which can do this for VB.net. – Tim Biegeleisen May 01 '18 at 06:20
  • 5
    There is no way to get SQL Server to do that natively. Column names only ever are column names and don't include the table names, while rowsets are rowsets and don't link back to their underlying tables, even in metadata (only in query plans). You are honestly better off actually going through the "thousands or so" statements and change `SELECT [...] a.foo` to `a.foo as [a.foo]` systematically with a clever regex, or implementing code that does and transform the query before it executes. The queries would need changing; the consumers wouldn't. – Jeroen Mostert May 05 '18 at 09:01

0 Answers0