Windows 2008 and SQL 2012 - Can't send Database Mail via Office 365 account

I struggled to figure out how to send e-mails from SQL Server 2012 Database Mail on Windows Server 2008. Yeah Windows Server 2008 is very old but I had a client who needed this done! I had previously configured SQL 2019 Database Mail on a Windows 2022 Server I it was easy. I just configured it in SQL Database Mail and it worked. But in Windows Server 2008 you have to add the following registry entries. Maybe not ALL of these are required but after a few days I was not about to experiment anymore. Restarting the SQL Agent allowed the SQL Database Mail to work via smtp.office365.com, on Port 587 with the SSL checked! I hope someone finds this useful someday!

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\.NETFramework\v4.0.30319]
"SchUseStrongCrypto"=dword:00000001
"SystemDefaultTlsVersions"=dword:00000001
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\.NETFramework\v2.0.50727]
"SystemDefaultTlsVersions"=dword:00000001
"SchUseStrongCrypto"=dword:00000001
[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\.NETFramework\v2.0.50727]
"SystemDefaultTlsVersions"=dword:00000001
"SchUseStrongCrypto"=dword:00000001
[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\.NETFramework\v4.0.30319]
"SystemDefaultTlsVersions"=dword:00000001
"SchUseStrongCrypto"=dword:00000001
[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\.NETFramework\v4.5.50709]
"SystemDefaultTlsVersions"=dword:00000001
"SchUseStrongCrypto"=dword:00000001
[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\
Protocols\SSL 2.0\Client]
"DisabledByDefault"=dword:00000001

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\
SecurityProviders\SCHANNEL\
Protocols\SSL 3.0\Client]
"Enabled"=dword:00000001
"DisabledByDefault"=dword:00000000

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\
SCHANNEL\Protocols\SSL 3.0\Server]
"Enabled"=dword:00000001
"DisabledByDefault"=dword:00000000

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\
Protocols\TLS 1.2\Client]
"Enabled"=dword:00000001
"DisabledByDefault"=dword:00000000

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\
Protocols\TLS 1.2\Server]
"DisabledByDefault"=dword:00000000
"Enabled"=dword:00000001






 

Maintenance Plan Execute option is greyed out

When I go to SQL Server Management Studio -> Management -> Maintenance Plans -> Right click on the maintenance plan that I want to execute but the option is greyed out.

  • I have checked and SQL Server Integration Services is running.
  • I can execute other maintenance plans.
  • I have even tried modifying this plan without an issue but Execute stays greyed out.
  • There is no other instance of the plan running at the moment.
  • I have also disconnected from the SQL Server and connected back but that didn't help either.
  • SQL Agent is running.

Solution:

Turns out it was an execution window that was buried. It doesn't show as an "open window" when you look at the taskbar at the SSMS icon, so it's deceptive. ALT-TAB your way through the windows and you should find it.

The open windows was:

From here: sql server - Maintenance Plan Execute option is greyed out - Stack Overflow

MS Access Error 3283 Primary key already exists

Source: FIXED: Error 3283 Refresh Links (isladogs.co.uk) - and - RefreshLink fails with "Primary Key already exists" (accessforever.org) -  Thanks!

I came across error 3283 - Primary key already exists after the MS Office system had been updated to version 2312 Build 16.0.17126.20190 for Monthly Enterprise  Channel.

The error happens when calling RefreshLink as shown in the code sample below. Disclaimer: This worked for me, but your solution might be different. The solution "for now" is to use On Error Resume Next and deal with the error afterwards as the table does in fact relink just fine. Microsoft say that a fix will not be released until March 12th 2024 in version 2401. So you will need to implement this work around until it's resolved.

For Each tblDef In tblDefs
	If tblDef.Connect <> "" Then
		If InStr(1, tblDef.Connect, "ODBC") > 0 Then
			'SQL Server Database
			If (tblDef.Connect <> strSQLConnectionString) Or blnForceReconnect Then
				tblDef.Connect = strSQLConnectionString
								   
				' Error 3283 when refreshing links using code caused by MS Office Update 2312 Build 16.0.1716.20190
				' A fix will be applied by Microsoft on March 12th 2024 Office Updates for build 2401.
				On Error Resume Next
				tblDef.RefreshLink
			
				' This is required if the SQL View is to be update-able
				If tblDef.Name = "vw_Example" Then
					CurrentDb.Execute "CREATE UNIQUE INDEX __uniqueindex ON " & tblDef.Name & " (ID ASC)"
				End If
														
				Err.Clear
				On Error GoTo ErrorHandler
				
			End If
		End If
		
	End If
Next tblDef

Thanks to Isladogs on Access and Access Forever for this post.

Microsoft 365 Updates

If you have any Microsoft 365 App issues then check this site to see if there have been any updates https://learn.microsoft.com/en-us/officeupdates/update-history-microsoft365-apps-by-date

More than likely there has been an update and you might have to roll back the update to the last version or 2 previous versions ago.

One recent example was a January 4th, 2024 update (20126) that broke MS Access when tables are connected to SQL Server. The connection string was missing a semi-colon. An update was released on January 9th, 2024 (20132), but it still did not fix the issue (You can open a linked SQL table but you can't go to the last record.) so users had to go back to the December 12th, 2023 (20108) update!

How do you import a large MS SQL .sql file?

If you have a very large file that was created by using Generate Script (Data Only) via SQL Server Management Studio (SSMS) you will probably not be able to load it in SSMS to run it. The solution is to use the Command Prompt as follows.

sqlcmd -S <Server>\<Instance> -i <Full Path of .sql File> -I

The -I at the end is used when there is Quoted Identifiers, which is just about all the time!

Other Command Line Parameters that might be of use.

Sqlcmd            [-U login id]          [-P password]
  [-S server]            [-H hostname]          [-E trusted connection]
  [-d use database name] [-l login timeout]     [-t query timeout] 
  [-h headers]           [-s colseparator]      [-w screen width]
  [-a packetsize]        [-e echo input]        [-I Enable Quoted Identifiers]
  [-c cmdend]            [-L[c] list servers[clean output]]
  [-q "cmdline query"]   [-Q "cmdline query" and exit] 
  [-m errorlevel]        [-V severitylevel]     [-W remove trailing spaces]
  [-u unicode output]    [-r[0|1] msgs to stderr]
  [-i inputfile]         [-o outputfile]        [-z new password]
  [-f  | i:[,o:]] [-Z new password and exit] 
  [-k[1|2] remove[replace] control characters]
  [-y variable length type display width]
  [-Y fixed length type display width]
  [-p[1] print statistics[colon format]]
  [-R use client regional setting]
  [-b On error batch abort]
  [-v var = "value"...]  [-A dedicated admin connection]
  [-X[1] disable commands, startup script, environment variables [and exit]]
  [-x disable variable substitution]
  [-? show syntax summary] 

MS Access Query/Recordset with Parameters

This is how to use a query in code with parameters for a Control as well as manipulate it for a Recordset. Note that you need to leave the Controls Row Source blank so that you don't see the Parameter prompts!

    Dim qryDef As DAO.QueryDef
    Set qryDef = CurrentDb.QueryDefs("qryQueryWithParameters")
    qryDef.Parameters("[ParameterA]") = intAID
    qryDef.Parameters("[ParameterB]") = intBID
    Set Me.lstListControl.Recordset = qryDef.OpenRecordset
    Me.lstListControl.Requery

You can declare the Parameters in the Query itself. I did this using square brackets around them and did the same in the query Criteria as well. This way the query does not think you're using literal Text as it will try and put "double quotes" around them if you don't use [square brackets]!

You can also manipulate the same query in a Recordset in order to iterate the row if you need to do that!

    Dim rstQueryWithParameters As DAO.Recordset
    Set rstQueryWithParameters = Me.lstListControl.Recordset
    
    Do Until rstQueryWithParameters.EOF
        ' Write iterative Code!
        rstQueryWithParameters.MoveNext
    Loop