Spectrum Technology Platform

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
------------------------------

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
------------------------------

Original Message:
Sent: 02-26-2022 05:39
From: George Corea
Subject: Creating a connection to SQL Server (MSSQL) from Spectrum

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
------------------------------

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
------------------------------

Original Message:
Sent: 02-28-2022 01:50
From: Peter Møller
Subject: Creating a connection to SQL Server (MSSQL) from Spectrum

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

Original Message:
Sent: 02-26-2022 05:39
From: George Corea
Subject: Creating a connection to SQL Server (MSSQL) from Spectrum

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
------------------------------

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
------------------------------

Original Message:
Sent: 02-28-2022 02:12
From: George Corea
Subject: Creating a connection to SQL Server (MSSQL) from Spectrum

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

Original Message:
Sent: 02-28-2022 01:50
From: Peter Møller
Subject: Creating a connection to SQL Server (MSSQL) from Spectrum

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

Original Message:
Sent: 02-26-2022 05:39
From: George Corea
Subject: Creating a connection to SQL Server (MSSQL) from Spectrum

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
------------------------------

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
------------------------------

Original Message:
Sent: 02-28-2022 03:15
From: Peter Møller
Subject: Creating a connection to SQL Server (MSSQL) from Spectrum

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

Original Message:
Sent: 02-28-2022 02:12
From: George Corea
Subject: Creating a connection to SQL Server (MSSQL) from Spectrum

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

Original Message:
Sent: 02-28-2022 01:50
From: Peter Møller
Subject: Creating a connection to SQL Server (MSSQL) from Spectrum

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

Original Message:
Sent: 02-26-2022 05:39
From: George Corea
Subject: Creating a connection to SQL Server (MSSQL) from Spectrum

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
------------------------------