Tag: SQL server

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.

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/

Excel LinkServer not working in SQL Server

I have created Excel linked server in SQL Server;

EXEC sp_addlinkedserver
   @server = 'ExcelServer',
   @srvproduct = 'ACE 12.0',
   @provider = 'Microsoft.ACE.OLEDB.12.0',
   @datasrc = 'C:\TempFolder\Sample1LinkedServer.xlsx',
   @provstr = 'Excel 12.0; HDR=Yes';

When i try to test this linked server, i get this error;

What permissions? SQL Server is running under NT service account by default. To fix this issue, i did this;

Go To Server Objects -> Linked Servers -> Providers.

Open Microsoft.ACE.OLEDB.12.0 by right click and properties. Check “Allow inprocess”.

Do the same for Microsoft.ACE.OLEDB.14.0.

The pain will go away.

32 bit and 64 bit office side by side

Download this;

https://www.microsoft.com/en-us/download/details.aspx?id=54920

Open command prompt, navigate to exe and run this;

accessdatabaseengine.exe /quiet

This will help you to install 32 bit office driver along 64 bit.

To validate, try running SQL Agent job in 32 bit. If there is no error, your installation of 32 bit is successful. SQL Agent SSIS jobs by default run in 64 bit.

Installing on SQL Server

Install 64-bit version on SQL Server.

Reference

https://knowledge.autodesk.com/support/autocad/learn-explore/caas/sfdcarticles/sfdcarticles/How-to-install-64-bit-Microsoft-Database-Drivers-alongside-32-bit-Microsoft-Office.html

enabling / disabling xp_cmdshell

I am not getting into the discussion of pros and cons of xp_cmdshell. This command helps you to run broad level features inside your SQL server. You can run OS level commands, SSIS packages etc.

Here is how you can enable, disable and use.

To check, if xp_cmdshell is enabled and available to use;

SELECT CONVERT(INT, ISNULL(value, value_in_use)) AS config_value
FROM sys.configurations
WHERE name = 'xp_cmdshell';

Turn on xp_cmdshell

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE

Turn off xp_cmdshell

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 0;
RECONFIGURE

Here is a simple use case;

EXEC xp_cmdshell 'dir c:\'

Here is how you can enable it, use it and disable;

DECLARE @originalSetting INTEGER = 0;
SELECT @originalSetting = CONVERT(INT, ISNULL(value, value_in_use))
FROM  sys.configurations
WHERE  name = 'xp_cmdshell' ;
 
IF @originalSetting = 0
BEGIN
    EXEC sp_configure 'show advanced options', 1;
    RECONFIGURE;
    EXEC sp_configure 'xp_cmdshell', 1;
    RECONFIGURE;
END
 
EXEC xp_cmdshell 'dir c:\';
 
IF @originalSetting = 0
BEGIN
    EXEC sp_configure 'show advanced options', 1;
    RECONFIGURE;
    EXEC sp_configure 'xp_cmdshell', 0;
    RECONFIGURE;
END

You can create a stored procedure and use;

CREATE PROCEDURE sp_SuperCmdShell
(
    @commandToRun VARCHAR(1000)
)
AS
BEGIN
    -- check to see if xp_cmdshell is enabled, if its not
    --   then enable it and run the command then set it back
    --   to not enabled.
    DECLARE @originalSetting INTEGER = 0;
    SELECT @originalSetting = CONVERT(INT, ISNULL(value, value_in_use))
    FROM  sys.configurations
    WHERE  name = 'xp_cmdshell' ;
 
    IF @originalSetting = 0
    BEGIN
        EXEC sp_configure 'show advanced options', 1;
        RECONFIGURE;
        EXEC sp_configure 'xp_cmdshell', 1;
        RECONFIGURE;
    END
 
    EXEC xp_cmdshell @commandToRun;
 
    IF @originalSetting = 0
    BEGIN
        EXEC sp_configure 'show advanced options', 1;
        RECONFIGURE;
        EXEC sp_configure 'xp_cmdshell', 0;
        RECONFIGURE;
    END
 
END
GO
exec #sp_SuperCmdShell 'dir c:\';

This is not a new feature. It came out with early release of SQL server. If you see the usage command, they are OS level command. With the super privileged access any body can run a “format c:\” OS command and leave your database server in unrecoverable state. My suggestion would be to use it with least privileged proxy account.