Correct connection string for ancient ASP sites with MS SQL.

There are cool site with a lot of connection string variants https://www.connectionstrings.com/sql-server/, but nobody understanding in what case need what connection string. Therefore I want to answer to this question, correct connection string for ancient ASP with Ms SQL is only two. First one you can see on the screen below.

The second is Integrated Security=SSPI instead login and password. Both version need to configured ODBC Data Sources (64-bit) with provider ODBC Driver 17/18 for SQL Server from this page https://learn.microsoft.com/en-us/sql/connect/odbc/download-odbc-driver-for-sql-server?view=sql-server-ver16.

Pay atention, that MS has a lot of various provider for MS SQL https://learn.microsoft.com/en-us/sql/connect/oledb/oledb-driver-for-sql-server?view=sql-server-ver16, main is:

• Microsoft OLE DB Provider for SQL Server (SQLOLEDB)
• SQL Server Native Client (SNAC)
• Microsoft OLE DB Driver for SQL Server (MSOLEDBSQL)

And there are a lot of various third party provider for MS SQL server, for example https://fsprojects.github.io/SQLProvider/, but non of them has compatible with ancient ASP.

Also pay attention, than MS SQL with ancient ASP working more stable without DBNETLIB, Named Pipes and Shared Memory connection type to MS SQL usually allow to avoid various troubles.

On other side, version of MS SQL usually no matter for ancient ASP, for various project I use any MS SQL version - from SQL 7 and SQL 2000 to SQL 2022.

And this is practical example to invoke SQL procedure by ADO https://www.w3schools.com/asp/met_comm_createparameter.asp

   1:                      If Trim(Request.QueryString("subsid"))<>"" then

   2:                      Dim Del,Prm1

   3:                      Set Del = Server.CreateObject("ADODB.Command")

   4:                      With Del

   5:                          .ActiveConnection = Cnn

   6:                          .Commandtext = "DelSuscriber"

   7:                          .CommandType = adCmdStoredProc

   8:                          'Name, Type, Direction, Size, Value

   9:                          .Parameters.Append .CreateParameter("ID", 3, adParamInput, 4 ,Trim(Request.QueryString("subsid")))

  10:                      End with

  11:                      Del.Execute

  12:                         Set Del = Nothing

Comments ( )