Results 1 to 5 of 5

Thread: Virtual Table and MS SQL Server

  1. #1
    Member
    Join Date
    Apr 2013
    Posts
    88

    Virtual Table and MS SQL Server

    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

  2. #2
    Lianja Development Team barrymavin's Avatar
    Join Date
    Feb 2012
    Location
    UK, USA, Thailand
    Posts
    5,771
    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.
    Principal developer of Lianja, Recital and other products

    Follow me on:

    Twitter: http://twitter.com/lianjaInc
    Facebook: http://www.facebook.com/LianjaInc
    LinkedIn: http://www.linkedin.com/in/barrymavin

  3. #3
    Lianja Development Team barrymavin's Avatar
    Join Date
    Feb 2012
    Location
    UK, USA, Thailand
    Posts
    5,771
    Alternatively the ODBC manager should have somewhere to specify it. But as I say every driver is different.
    Principal developer of Lianja, Recital and other products

    Follow me on:

    Twitter: http://twitter.com/lianjaInc
    Facebook: http://www.facebook.com/LianjaInc
    LinkedIn: http://www.linkedin.com/in/barrymavin

  4. #4
    Member
    Join Date
    Apr 2013
    Posts
    88
    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

  5. #5
    Lianja Development Team barrymavin's Avatar
    Join Date
    Feb 2012
    Location
    UK, USA, Thailand
    Posts
    5,771
    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.
    Principal developer of Lianja, Recital and other products

    Follow me on:

    Twitter: http://twitter.com/lianjaInc
    Facebook: http://www.facebook.com/LianjaInc
    LinkedIn: http://www.linkedin.com/in/barrymavin

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Journey into the Cloud
Join us