to connect to database through SQL

[#1]

Hello

I would like to connect to the database through an SQL query.

I have written the JSL script

dt = Open Database (
                          "DRIVER = {SQL Server};
                          SERVER = HAISQLC162\HAISQLC162,3187
                          UID = hw_tests_viewer
                           PWD = ********
                           DATABASE = HW_Chip_Tests;"
,
"SELECT *

FROM [HW_Chip_Tests].[viewer].[chip_tests_row_data_3m_v]
where VID = 'S232NNC300578' "
);

but received error note:  Data source name not found and no default driver specified

what I did wrong?

Dennisbur_0-1711971995870.png

[#2 · Accepted solution]

To me it looks like the connection string is in wrong format. Usually they look like something like this

Driver={SQL Server};Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;

how it exactly looks like depends on your database, your driver, settings on PC and so on.

You can also try to create the connection using JMPs query builder and extract the connection string from there (it will Use New SQL Query instead of Open Database which I would also suggest you use as that is the method which is supposed to be supported in the future of JMP). The connection string it creates will be a bit different as it might prefix it with ODBC

[#3]

Not sure if it's easier but you can also try File > Database > Open Table.  You might need to create an ODBC user source.  Once you open a table from SQL Server, you can find the connection string under the red or green triangle (depends on which version of JMP you are using).

[#4]

Any chance to show step by step how to get the connection string?

[#5]

Hi @DualBison1041 , yes there is a way to get the connection string, you can generate a JMP Database Query by:

1. File => Database => Query Builder (or in JMP 19 File=> Connect to => ODBC => Query Builder
2. select the connection you want (e.g. ODBC: new connection, computer data source ...)
3. Next
4. Build query
5. then e.g. save modify script to script window will show you the connection string

BR