Scripting

I have a button script running to list some info from AP Invoice History Header. It works in Std, but not Premium. What are we missing with the ODBC settings or permissions? We have the ODBC setup & testing successfully on the app server. The ODBC has a name of SOTAMAS90, the user is setup in Sage as ""SCRIPT"" with password of ""script, & the company code in both ODBC & the script is 001. The Sage service is running as a domain admin. It gets to where it needs to open the connection & errors out: Dim Input Input = AP_Invoice_bus_VendorNo strConnectionString = ""DSN=SOTAMAS90;UID=SCRIPT|001;PWD=script"" Set connection = CreateObject(""ADODB.Connection"") connection.open strConnectionString Set RS = CreateObject(""ADODB.RecordSet"") strSQL = ""SELECT * FROM AP_InvoiceHistoryHeader WHERE AP_InvoiceHistoryHeader.VendorNo = '"" & Input & ""'"" RS.Open strSQL, connection ----------------------------------------------------------------- Error: OLE Error Number : -2147217843. Description : [Microsoft][SQL Server Native Client 11.0][SQL Server]Login failed for user 'SCRIPT|001'. Language : VBScript Script Line : 41 Script Column : 0

There is no SOTAMAS90 in premium Change your connection to talk directly to SQL

I don't have any experience trying to connect to Premium via odbc and the SOTAMAS90 DSN but if I had to guess, it looks like you are trying to use the UID and PWD flags that are used for the ProvideX driver and the driver actually behind the DSN is the MS SQL server driver. I don't have an answer on the correct way to go about it but I imagine you need to change the connection string or use additional properties/methods to set the login info for the ado connection object.

strConnectionString = ""dsn="" & DSN & "";UID="" & UserName & "";PWD="" & Password & "";Database=MAS_"" & CompanyCode UserName and Password are SQL credentials, not the Sage login details. Or you can set up your DSN to use Windows authentication instead. (We usually use a MAS_### DSN with integrated, and default database selected within the DSN... with each user given read only access to the MAS_* databases they need to see... in which case all you need to use in your connection string is the DSN).

If you set up a DSN then you have to set it up and every computer. Direct connection to SQL is the easier way - no workstation setup needed. See https://www.connectionstrings.com/sql-server-native-client-11-0-oledb-provider/ for examples.

(Most of this kind of script for us is run from the server, or by a specific user... good point @PhilMcIntosh ).