Spectrum Technology Platform

 View Only
  • 1.  Creating a connection to SQL Server (MSSQL) from Spectrum

    Posted 02-26-2022 05:39
    I have reviewed the doc but can't work out the connection string.

    The Tab link to the mssql has the following - how do I translate this to the spectrum connection?

    "\DATALINK" = ""
    "\DATALINK\ConnectionString" = "DSN=Raw_Tab;Description=Raw Tab Data;UID=Administrator;Trusted_Connection=Yes;DATABASE=Raw_Tab"
    "\DATALINK\Query" = "select ""Row_ID"", ""Parent_ID"", ""Pipe_Asset_Number"", ""Trench_Asset_Number"", ""SRM_Asset_Number"", ""Size"", 

    I have the JDBC driver as
    com.microsoft.sqlserver.jdbc.SQLServerDriver

    and the connection string as
    jdbc:sqlserver://$111.111.111.111:$1443;databaseName=$MSSQL;encrypt=${encryption};​TrustServerCertificate=true

    are the {} just to indicate what should be put in? I have tried with and without them and also localhost instead of the ip.

    Thanks,

    ------------------------------
    George Corea
    Mangoesmapping
    MOOMIN QLD
    ------------------------------


  • 2.  RE: Creating a connection to SQL Server (MSSQL) from Spectrum

    Employee
    Posted 02-28-2022 01:50
    Hi George

    You need to check the ODBC data source named Raw_Tab.
    This data source will hold the details you need such as server name and database name.

    ------------------------------
    Peter Horsbøll Møller
    Principal Presales Consultant | Distinguished Engineer
    Precisely | Trust in Data
    ------------------------------



  • 3.  RE: Creating a connection to SQL Server (MSSQL) from Spectrum

    Posted 02-28-2022 02:13
    Edited by George Corea 02-28-2022 02:13
    Thanks Peter.

    It has

    Data Source Name: Raw_Tab
    Data Source Description: Raw Tab Data
    Server: VMI1\SQLEXPRESS
    Database: Raw_Tab
    Language: (Default)
    Translate Character Data: Yes
    Log Long Running Queries: No
    Log Driver Statistics: No
    Use Regional Settings: No
    Prepared Statements Option: Drop temporary procedures on disconnect
    Use Failover Server: No
    Use ANSI Quoted Identifiers: Yes
    Use ANSI Null, Paddings and Warnings: Yes
    Data Encryption: No

    How do I structure this into the format that's required. I have tried it in multiple ways and nothing has worked.

    Regards,

    ------------------------------
    George Corea
    Mangoesmapping
    MOOMIN QLD
    ------------------------------



  • 4.  RE: Creating a connection to SQL Server (MSSQL) from Spectrum

    Employee
    Posted 02-28-2022 03:15
    Hi

    Have you tried this connection string:
    jdbc:sqlserver://VMI1\SQLEXPRESS:1443;databaseName=Raw_Tab

    I wonder if you can or should support Windows Authentication? If not, you need to enter the username and password for a database user with the right permissions.

    If Windows Authentication is an option, you may have to include an extra parameter in the connection string:
    jdbc:sqlserver://VMI1\SQLEXPRESS:1443;databaseName=Raw_Tab;Trusted_Connection=True

    ------------------------------
    Peter Horsbøll Møller
    Principal Presales Consultant | Distinguished Engineer
    Precisely | Trust in Data
    ------------------------------



  • 5.  RE: Creating a connection to SQL Server (MSSQL) from Spectrum

    Employee
    Posted 03-02-2022 03:30
    Hi George,
    Spectrum Spatial only can connect to SQL Server through the TCP/IP protocol. Unfortunately MS SQL Server installs with the TCP/IP protocol disabled.
    To enable the TCP/IP protocol run the "SQL Server Configuration Manager"
    • Expand "SQL Server Network Configuration"
    • Click "Protocols for {NAME OF SQL SERVER}"
    • One the right panel double click TCP/IP
    • Select Enabled => Yes
    By default SQL Server will listen on port 1433

    Regards to Peter's last reply, "Trusted_Connection" is not supported with Spectrum. So you must give a username and password to the NamedConnection in Spectrum Spatial.

    Hope that helps

    Andreas

    ------------------------------
    Andreas Homa
    Precisely Software Inc.
    Troy NY
    ------------------------------