How to confirm Access Database Engine (ACE Driver) is installed on Database Server

Follow these steps to verify Access Database Engine (ACE Driver) installation;

Double check to make sure you did install the x64 version of Microsoft Access Database Engine here – http://www.microsoft.com/download/en/details.aspx?id=13255.  Please note that only one version x64 –OR– x86 can be installed, not both.

Here is a SQL Server x64 server with the Microsoft Access Database Engine x32 installed – see it does not show up.

Here is the same server with the Microsoft Access Database Engine x64 installed – see how it shows up.

I do not know of a query to get a list of the providers – however if you look in the registry at

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\Providers and check for the key Microsoft.ACE.OLEDB.12.0

This will tell you that it is installed.  If you have a 32-bit version installed on a 64-bit box you would need to look under the Wow6432Node, that key would be HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\Providers.

You would have to make sure that both SQL Server and the ODBC/OLEDB driver are the same bitwise either both x64 or x32.

How to query last restore dates of databases

This simple way to view a single database is;

declare @DB sysname = 'MyDB';
select * from msdb.dbo.restorehistory where destination_database_name = @DB;

To view all databases;

WITH LastRestores AS
(
SELECT
    DatabaseName = [d].[name] ,
    [d].[create_date] ,
    [d].[compatibility_level] ,
    [d].[collation_name] ,
    r.*,
    RowNum = ROW_NUMBER() OVER (PARTITION BY d.Name ORDER BY r.[restore_date] DESC)
FROM master.sys.databases d
LEFT OUTER JOIN msdb.dbo.[restorehistory] r ON r.[destination_database_name] = d.Name
)
SELECT *
FROM [LastRestores]
WHERE [RowNum] = 1

Resource;

https://dba.stackexchange.com/questions/33703/how-to-query-last-restore-date-in-sql-server/33705

Copy data from one DB to second DB using Script

I would go with this template to copy data from one database to another database using scripts;

Use MyDB
GO

BEGIN TRY
	BEGIN TRANSACTION
	
	IF NOT EXISTS (SELECT  1 FROM  [dbo].[MyTable] WHERE [Id] = '801DA66B-F5F7-463E-AD56-D432E12B429E' )
	BEGIN
		Print 'INSERT / UPDATE / DELETE'
		INSERT INTO ... 
		UPDATE ....
		DELETE ....
	END

	COMMIT TRANSACTION
END TRY

BEGIN CATCH
	SELECT ERROR_NUMBER() AS ErrorNumber
	,ERROR_MESSAGE() AS ErrorMessage
	 ROLLBACK TRANSACTION
END CATCH

Basically it’s a row by row insertion where row ID is been checked for existence and operation is performed.

Copy BLOB (binary data) from one DB to second DB using script

I would go with generate script option in SQL Server Management Studio;

  1. Highlight source database that has the source table
  2. Right-click – Tasks | Generate Scripts
  3. Select source table under “Select specific database objects”. Click Next
  4. Click Advanced and under General options – Change ‘Type of data to script’ to “Data Only”
  5. Save to file.

I have tried a sample that store excel files as varbinary type. The output from scrpting process is ;

USE [MyDB]
GO
INSERT [dbo].[Document] ([DocumentId], [FileExtension], [FileName], [Doc_Content]) VALUES (N'3af2497c-a190-eb11-b1bb-000d3adde0a7', N'.xlsx', N'Sample-01.xlsx',  0x504B0304140006000800000021002FDCAF2EEC010000310D0000130008025B436F6E74656E745F54797065735D2E786D6C20A2040228A00002000000000000000000000000000000000000000000000000000000000000000000000000000000

Handling special characters in the data using SQL server

I got a situation where i need to handle special characters in incoming data, for example “USD100” or “100USD”. Here is the work around;

If we try like this, it will give back NULL value;

DECLARE @Value NVARCHAR(50) = '100USD';
SELECT TRY_CAST(@Value AS decimal);

The work around is this;

DECLARE @Value NVARCHAR(50) = '100USD';
SELECT TRY_CAST(REPLACE(REPLACE(REPLACE(@Value, 'USD', ''), '#', ''), '$', '') AS decimal);

Resources

https://docs.microsoft.com/en-us/sql/t-sql/functions/try-cast-transact-sql?view=sql-server-ver15

https://www.sqlshack.com/replace-ascii-special-characters-sql-server/