Virtual Table and MS SQL Server

[Phjr]

Hi, can't yet define a virtual table connecting to a MS Sql Server DB
Defined in Windows a SQLDEV1 ODBC Data Source using "ODBC Driver 11 for SQL Server"
Tested successfully SQLDEV1 data source in App Builder console ODBC tab. Tested with user 'sa' password 'sa'
created virtual table with statement
create virtualtable persons connstr 'SQLDEV1' as select * from dbo.persons (did'nt find how to provide user & password)
dir

Tables in Database: msdev
Name Path Type
------------------- ------------------------------------------------------------ --------------------
+ persons C:\Lianja\data\msdev\persons.dbf VirtualTable
| connstr 'SQLDEV1'
| alias 'PERSONS'
| as select * from dbo.persons

and, as expected,
use persons failed
Thu 2. May 15:07:31 2013
**** Lianja error ****
use persons
^
File 'PERSONS' does not exist

what am i missing?
Regards

[barrymavin]

You need to look at the connection string options available for that ODBC driver. Each one is different. Typically you can add uid=ca;pwd=ca; as part of your connection string or something to that effect.

[barrymavin]

Alternatively the ODBC manager should have somewhere to specify it. But as I say every driver is different.

[Phjr]

the following worked
msconnstr='Driver={ODBC Driver 11 for SQL Server};Server=10.231.93.64,1434;Database=dev1;Uid =sa;Pwd=sa'

create virtualtable persons connstr msconnstr as select * from dbo.persons

Thanks

[barrymavin]

Yes its always good to remember that ODBC connection strings are data source dependent. So what you can do is use {...} macro substitution in the connection string if you want to switch between different data sources for the same virtual table.