Extract images from database by serverku

As we learned for the import the images into the database from folder in the last post. One more thing is also interesting to learn here as how can we extract images from database.

SP_CONFIGURE  'Ole Automation Procedures',1
GO
RECONFIGURE
GO

SELECT * FROM [IMAGEINSERT]
GO

DECLARE @Image VARBINARY(MAX)
SELECT @Image = (SELECT [Image] FROM [IMAGEINSERT] )
DECLARE @ObjectImage INT
EXEC sp_OACreate 'ADODB.Stream', @ObjectImage OUTPUT
EXEC sp_OASetProperty @ObjectImage, 'Type', 1
EXEC sp_OAMethod @ObjectImage, 'Open'
EXEC sp_OAMethod @ObjectImage, 'Write', NULL, @Image
EXEC sp_OAMethod @ObjectImage, 'SaveToFile', NULL, 'D:\image\ImageExport.jpg', 2
EXEC sp_OAMethod @ObjectImage, 'Close'
EXEC sp_OADestroy @ObjectImage

Insert Image into database from Folder using T-SQL in SQL Server by serverku

For the some project workaround I experienced with insert, some of the images into a database for need, You can do it with OPENROWSET,

CREATE TABLE [dbo].[ImageInsert](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Image] IMAGE -- or VARBINARY(MAX)
) ON [PRIMARY]
GO

INSERT INTO [dbo].[ImageInsert]([Image])
SELECT * FROM
OPENROWSET(BULK N'D:\image\ImageInsert.jpg', SINGLE_BLOB) AS Images
GO

Disable Guest User in all databases in SQL Server by serverku

As a database security, we should disable guest account from databases, You can do it with revoke access from that user.

USE yourDataBaseName
GO 
REVOKE CONNECT FROM GUEST;
GO

Sharing/Unsharing remote server's folder with remote stored procedure execution in SQL Server by serverku

Recently, when I was working with database backup plans and scheduling it on another server as this shared folder of the server should not be available to anyone after the backup process completion. I have created below script with stored procedure on remote server and run the stored procedure remotely from local server to share and unshare the remote server's folder. Please note xp_cmdshell and Ad Hoc Distributed Queries should be enabled on the server.

USE MASTER
GO

-- Creating shared 
EXEC XP_CMDSHELL 'NET SHARE SharedFolder=E:\DatabaseBackups
     /GRANT:Everyone,Full
     /REMARK:"Database backups perform on another server"'    
GO

-- Removing shared
EXEC XP_CMDSHELL 'NET SHARE SharedFolder  /delete"'
GO

Run query against all the databases without MSFOREACHDB and WHILE/CURSOR by serverku

I have learned one more thing today, for small queries when we need to run it for all the databases of instance, then we are mostly using MSFOREACHDB and WHILE loop or CURSOR to get the data. But with COALESCE I can I do with very small code here, which collect count of the objects for each database.

DECLARE @ObjectSQL NVARCHAR(MAX)
SET @ObjectSQL = ''
SELECT @ObjectSQL = COALESCE(@ObjectSQL,'') + CHAR(13) + CHAR(10) 
    + 'SELECT ' + QUOTENAME([Name],'''') + ' as DbName, 
    COUNT(1) AS CntObject 
    FROM ' + QUOTENAME([Name],'') + '.DBO.SYSOBJECTS;'  
FROM SYS.DATABASES 
PRINT (@ObjectSQL)
EXECUTE (@ObjectSQL)

Hope you like it.

Insert default values all columns of table by serverku

We can insert all the default values in tables without specifying values while insert in table, how? See here we have tables defined all columns by default,

CREATE TABLE defaultval(
    id int default 0, 
    name varchar(10) default 'test', 
    cdate datetime default getdate()
);

INSERT defaultval DEFAULT VALUES;

Did you ever get used to it?

SQL Server High Availability - Manual Failover by serverku

Availability As we have seen SQL Server High Availability configuration and listener since same, I would like to go ahead for manual failover test in SQL Server 2012. So I would suggest to go through related previous posts.

1. Configure Listener for high availability in SQL Server
2. Implement High Availability in SQL Server - How to

Moving to test manual failover of SQL Server High Availability and following are the steps. Here Server 2 is a primary replica and Server1 is a secondary replica. Now we have to switch over the primary role from Server2 to Server1.

Step 1 : Connect primary replica availability group. Go to Availability Group and right clink on that and click on Failover.

Step 2 :Select instance, which you want to make a primary replica as shown in the image.

Step 3 : Connect SQL server instance, which you selected in earlier steps.

Step 4 : Click on Next and You can see the current primary replica and new primary replica.

Step 5 : Finally success on next step.

Step 6 : Last step to confirm primary replica, so connect with listener name and confirm as shown in the image.

This is just a manual failover test of Availability Group and hope you enjoyed it. In the next post we will see automated failover of Availability Group.

The EXECUTE permission was denied on the object ‘sp_start_job’, database ‘msdb’, schema ‘dbo’. - Error in SQL Server by serverku

For a security reason, I have created some users to have a permit to execute scheduled jobs only. So, given some required permissions in the msdb database, Even though users are not able to execute scheduled jobs in SQL Server. Below are the agents database roles are given for msdb database.

Even have above database roles in msdb database to execute scheduled jobs, users received following error when tried to run a job.

The EXECUTE permission was denied on the object ‘sp_start_job’, database ‘msdb’, schema ‘dbo’.

After finding a solution with online reference, found a script to check required permissions of users. Below is a script used to check for same.

USE msdb
GO

SELECT 
 PR.NAME, 
 DP.PERMISSION_NAME, 
 DP.STATE_DESC
FROM SYS.DATABASE_PERMISSIONS DP
 JOIN MSDB.SYS.OBJECTS O 
  ON DP.MAJOR_ID = O.OBJECT_ID
JOIN SYS.DATABASE_PRINCIPALS PR
 ON DP.GRANTEE_PRINCIPAL_ID = PR.PRINCIPAL_ID
WHERE O.NAME = 'SP_START_JOB'
GO

Finally, I saw EXECUTE permission was denied on SQLAgentUserRole and TargetServersRole roles over sp_start_job system stored procedure in msdb database. I granted it and it works finally. I would like you to share your experience of such relevant errors.

MERGE statement - a new tsql feature of SQL Server 2008 by serverku

It will a lengthy and complex coding if we need to perform insert, update and delete statement individually. Instead of writing separate statements for the insert, update and delete operation, we have one more option which can be very helpful in this matter.

Yes, that feature is "Merge" statement and supported in SQL server 2008 or later version. Merge is allow multiple DML operation to perform. That must be ended by semicolon. Let's see the example using Merge statement.

-- Creating Database

CREATE DATABASE MergeDatabase

GO

USE MergeDatabase

GO

-- Creating tables used for merged operation

IF ( Object_id('UsingTable') > 0 )
  DROP TABLE UsingTable

GO

CREATE TABLE UsingTable
  (
     RefId INT IDENTITY(1, 1),
     name  VARCHAR(100)
  )

GO

IF ( Object_id('TargetTable') > 0 )
  DROP TABLE TargetTable

GO

CREATE TABLE TargetTable
  (
     ChildId INT,
     val     INT
  )

GO

-- Inserting records in both tables

INSERT INTO UsingTable(name)
VALUES      ('Target-1'),
            ('Target-2'),
            ('Target-3'),
            ('Target-4'),
            ('Target-5')

GO

INSERT INTO TargetTable(ChildId,val)
VALUES      (1,1),
            (2,2),
            (3,3),
            (6,6) 

GO

Let us see how Merge statement works.

1. Merge statement with WHEN MATCHED clause and updating records,>

MERGE TargetTable 
USING UsingTable
ON (RefId = ChildId)

WHEN MATCHED THEN
UPDATE set val = val + 5 ;

2. Merge statement with WHEN MATCHED clause and deleting records,

MERGE TargetTable
USING UsingTable
ON (RefId = ChildId)

WHEN MATCHED AND ChildId = 3 THEN
DELETE ;

3. Merge statement with WHEN NOT MATCHED BY TARGET clause and inserting records,

MERGE TargetTable
USING UsingTable
ON (RefId = ChildId)

WHEN NOT MATCHED BY TARGET THEN
INSERT(childId,val)
VALUES(4,4)
;

4. Merge statement with WHEN NOT MATCHED BY SOURCE clause and deleting records,

MERGE TargetTable
USING UsingTable
ON (RefId = ChildId)

WHEN NOT MATCHED BY SOURCE
THEN DELETE;

5. All together at once,

MERGE TargetTable
USING UsingTable
ON (RefId = ChildId)


WHEN MATCHED AND ChildId = 3 THEN
DELETE 

WHEN MATCHED THEN
UPDATE set val = val + 5

WHEN NOT MATCHED BY TARGET THEN
INSERT(childId,val)
VALUES(4,4)

WHEN NOT MATCHED BY SOURCE
THEN DELETE;

Let's see the result set of TargetTable before and after the Merge statement used.

Before Merge statement ran,

After Merge statement ran,

6. Using OUTPUT with Merge statement,

MERGE TargetTable
USING UsingTable
ON (RefId = ChildId)


WHEN MATCHED AND ChildId = 3 THEN
DELETE 

WHEN MATCHED THEN
UPDATE set val = val + 5

WHEN NOT MATCHED BY TARGET THEN
INSERT(childId,val)
VALUES(4,4)

WHEN NOT MATCHED BY SOURCE
THEN DELETE

OUTPUT

$action,
INSERTED.ChildId,
INSERTED.Val,
DELETED.childId,
DELETED.val
;

Hope you have already started to use Merge statement.

Access to the remote server is denied because no login-mapping exists - SQL Server Error by serverku

Recently, when I was working with security and changed some level of access and permission of some logins\users, I received an error while accessing data through linked servers with some logins which was working earlier. An error is reported as below.

Msg 7416, Level 16, State 2, Line 1
Access to the remote server is denied because no login-mapping exists.

After finding solution following, it worked. Here is a some change of linked server and below is a script to used for same. Just adding a logins to linked server which has an issue to access it.

Use master
GO

EXEC master.dbo.sp_addlinkedserver 
 @server = N'LinkedServerName', 
 @provider=N'SQLNCLI',
 @srvproduct = 'MS SQL Server', 
 @provstr=N'SERVER=ServerName\InstanceName;User ID=myUser'


EXEC master.dbo.sp_addlinkedsrvlogin 
 @rmtsrvname = N'LinkedServerName', 
 @locallogin = NULL , 
 @useself = N'False', 
 @rmtuser = N'myUser', 
 @rmtpassword = N'*****'
GO

Here is the just script and change your user name in place of ‘myUser’ and appropriate server\instance name. Please share your comments if you received such errors and workaround for same.

Script to find Assembly registered in SQL Server by serverku

Sometime we need to know registered an assembly in our SQL Server instance and Today I would like to share a script to find the same. Here is a script for same.

USE <DBName>
GO

SELECT  
 a.name as AssemblyName, 
 af.name as AssemblyPath, 
 a.create_date as CreateDate,
 am.assembly_class as AssemblyClass,
 am.assembly_method as AssemblyMethod,
 a.is_user_defined as IsUserDefined
FROM sys.assemblies AS a
INNER JOIN sys.assembly_files AS af
 ON a.assembly_id = af.assembly_id
LEFT JOIN sys.assembly_modules am
 ON a.assembly_id = am.assembly_id
GO

This script return assembly name, path, created date and object associated with it. I would like to you share, if any, other information can be received for registered assembly.

Can not find the types 'x', because it does not exist or you do not have permission - SQL Server by serverku

Recently, when I was working with security of SQL Server, I faced one permission issue of custom data type and received an error below,

msg 15151, Level 16, State 1, Procedure xxx, Line 9
Cannot find the types 'x', because it does not exist or you do not have permission

This error received while executing stored procedure and this custom data type used in a stored procedure. The user has executed and appropriate permission of base tables, even it raised an error. Finally, I came to a solution and given permission below,

Use <YourDBName>
GO
GRANT VIEE DEFINITION ON TYPE :: DBO.X TO <User Name>
GRANT EXECUTE ON TYPE :: DBO.X TO <User Name>
GRANT CONTROL ON TYPE :: DBO.X TO <User Name>
GO

This is just what I faced and the solution applied to resolve it. Please share your comments if you ran into a custom data type issue.

Fix Orphaned Users in SQL Server by serverku

You may aware of the orphaned users and it is experienced when restore a database backup to another server with logins which means the database user restored in a system which does not have associated valid logins. After restoring a database backup we can fix those orphaned users with sp_change_users_login. Let us see a small example to fix orphaned database users.

-- At source server
USE [master]
GO
-- Creating a test login
CREATE LOGIN [testlogin] WITH PASSWORD=N'testlogin', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [DemoDB]
GO
-- Creating a database user for login testlogin created 
CREATE USER [testlogin] FOR LOGIN [testlogin]
GO
-- Assigning a database role to testlogin user created
ALTER ROLE [db_datareader] ADD MEMBER [testlogin]
GO

USE master
GO
-- Taking a database backup at source
BACKUP DATABASE [DemoDB] 
TO DISK = 'D:\DemoDB_full_20130913.bak'


-- At destination server
USE Master
GO
-- Restoring a database backup at source
RESTORE DATABASE [DemoDB] 
FROM DISK = 'D:\DemoDB_full_20130913.bak'
WITH replace,
MOVE 'DemoDB' TO 'D:\Data\DemoDB_data.mdf',
MOVE 'DemoDB_log' TO 'D:\Log\DemoDB_log.ldf'


USE DemoDB
GO
EXEC sp_change_users_login 'report'
GO

/*
UserName UserSID
----------------------------------------------
testlogin 0xFD13E649B5EF75469A22D598C8E0790D
*/

-- Mapping a user to new sql login
USE DemoDB
GO
CREATE LOGIN testlogin WITH PASSWORD = 'testlogin'
GO
EXEC sp_change_users_login 'Update_One','testlogin','testlogin'
GO

-- Automatically mapping a user to login, creating a new login if not exists
USE DemoDB
GO
EXEC sp_change_users_login 'Auto_Fix','testlogin',NULL,'testlogin'
GO

/* Output
-- If login does not exists
Barring a conflict, the row for user 'testlogin' will be fixed by updating its link to a new login.
The number of orphaned users fixed by updating users was 0.
The number of orphaned users fixed by adding new logins and then updating users was 1.

-- If login already exists
The row for user 'testlogin' will be fixed by updating its login link to a login already in existence.
The number of orphaned users fixed by updating users was 1.
The number of orphaned users fixed by adding new logins and then updating users was 0.
*/

You can fix all users at one shot using the following script,

USE [DemoDB]
GO

CREATE TABLE #OrphanedUsers
    (
         Id    INT IDENTITY(1, 1),
         UserName VARCHAR(250)
    )

DECLARE @i INT, 
  @Total     INT,
        @User VARCHAR(250)

INSERT INTO #OrphanedUsers (UserName)
SELECT DISTINCT [Name]
FROM   [Sysusers]
WHERE  Islogin = 1
       AND [Name] NOT IN 
  ( 'guest', 'sa', 'dbo', 'public',
          'sys', 'INFORMATION_SCHEMA' )

SET @Total = @@ROWCOUNT
SET @i = 1

WHILE ( @i <= @Total)
  BEGIN
      SELECT @User = UserName
      FROM   #OrphanedUsers
      WHERE  Id = @i

      EXEC sp_change_users_login 'Auto_Fix', @User, NULL, @User

      SET @i = @i + 1
  END

DROP TABLE #OrphanedUsers

Hope you enjoyed this small example and would like to you share ideas for same.
Stay tuned for more!

Find active open transactions from a SQL Server Instance by serverku

I would like to share the script to find open active transactions and sessions at database level, here you can find the same which capture for active transactions initiated by users only. This will result for all the databases, but you can make a database filter as well. This result is most relatively as DBCC OPENTRAN check in a particular database.

USE master
GO

SELECT 
       est.session_id as [Session ID],
       est.transaction_id as [Transaction ID],
       tas.name as [Transaction Name],
       tds.database_id as [Database ID]
FROM sys.dm_tran_active_transactions tas 
INNER JOIN sys.dm_tran_database_transactions tds 
       ON (tas.transaction_id = tds.transaction_id )
INNER JOIN sys.dm_tran_session_transactions est 
       ON (est.transaction_id=tas.transaction_id)
       WHERE est.is_user_transaction = 1 -- user
       AND tas.transaction_state = 2 -- active
       AND tas.transaction_begin_time IS NOT NULL
GO    

You can see the conditions made in this script and the values for the same are as following,

is_user_transaction

• 1 = The transaction was initiated by a user request.
• 0 = System transaction

transaction_state

• 0 = The transaction has not been completely initialized yet.
• 1 = The transaction has been initialized but has not started.
• 2 = The transaction is active.
• 3 = The transaction has ended. This is used for read-only transactions.
• 4 = The commit process has been initiated on the distributed transaction. This is for distributed transactions only. The distributed transaction is still active but further processing cannot take place.
• 5 = The transaction is in a prepared state and waiting resolution.
• 6 = The transaction has been committed.
• 7 = The transaction is being rolled back.
• 8 = The transaction has been rolled back.is_user_transaction

Synonyms in SQL Server by serverku

Before starting at depth, I just want to define Synonyms in SQL server. As per online,  it is an alternate name of any other database object and provide a layer of abstraction which refers to ad base object for local as well remote server. Which can be created for tables, stored procedures, views, linked server and for some others likes to list here. Let me give some examples as how it can be created and used,

-- Creating base table
CREATE TABLE dbo.results
(
 StudentId INT,
 Subject varchar(20),
 Scrore int
)

-- Creating a schema
CREATE SCHEMA VW
GO

-- Creating a view 
CREATE VIEW vw.results 
AS 
  SELECT StudentId,
   subject, 
         scrore 
  FROM   dbo.results 
GO 

-- Creating a Synonym for local object 
CREATE synonym dbo.snm_result 
 FOR demodb.vw.results 
GO 

-- Viewing data from Synonym 
SELECT * 
FROM   dbo.snm_result 
GO 

-- Creating a schema
CREATE SCHEMA lkd 
GO 

-- Creating a Synonym for remote object
CREATE synonym lkd.remotelinked 
 FOR RemoteServer1.demodb.dbo.Students 
GO 

-- Using in another script
SELECT rm.strundname, 
       r.subject,
    r.scrore 
FROM   vw.results r 
       INNER JOIN lkd.remotelinked rm 
               ON ( rm.studentid = r.studentid ) 
GO

This helps to use it in many places once it's created as we seen in the above example. You may have more idea of its usage. Your comments will be appreciated.

How to add filtered table in replication - SQL Server by serverku

I wrote some of the articles related to replication e.g. adding tables, stored procedures, views and functions in transactional replication. Now I am sharing one another script to add a filtered table in the transactions table. In script I have applied filters on CreatedDate column SampleTable table. It helps to fit the need of only required rows at subscriber and reduce the overhead of data transfer.

Script :

USE [PublisherDB]
GO

-- Adding the transactional articles
EXEC sp_addarticle 
 @publication = N'FilteredTables', 
 @article = N'SampleTable', 
 @source_owner = N'dbo', 
 @source_object = N'SampleTable', 
 @type = N'logbased', 
 @description = N'', 
 @creation_script = N'', 
 @pre_creation_cmd = N'drop', 
 @schema_option = 0x000000000803509F, 
 @identityrangemanagementoption = N'manual', 
 @destination_table = N'SampleTable', 
 @destination_owner = N'dbo', 
 @status = 24, 
 @vertical_partition = N'false', 
 @ins_cmd = N'CALL [sp_MSins_dboSampleTable]', 
 @del_cmd = N'CALL [sp_MSdel_dboSampleTable]', 
 @upd_cmd = N'SCALL [sp_MSupd_dboSampleTable]', 
 @filter_clause = N'[CreatedDate] between ''2014-04-01 00:00:00.000'' and ''2014-04-15 00:00:00.000'''

-- Adding the article filter
EXEC sp_articlefilter 
 @publication = N'FilteredTables', 
 @article = N'SampleTable', 
 @filter_name = N'FLTR_SampleTable_1__51', 
 @filter_clause = N'[CreatedDate] between ''2014-04-01 00:00:00.000'' and ''2014-04-15 00:00:00.000''', 
 @force_invalidate_snapshot = 1, 
 @force_reinit_subscription = 1

-- Adding the article synchronization object
EXEC sp_articleview 
 @publication = N'FilteredTables', 
 @article = N'SampleTable', 
 @view_name = N'SYNC_SampleTable_1__51', 
 @filter_clause = N'[CreatedDate]  between ''2014-04-01 00:00:00.000'' and ''2014-04-15 00:00:00.000''', 
 @force_invalidate_snapshot = 1, 
 @force_reinit_subscription = 1
GO

UI :

Hope you like this post. Have a great day!

Cannot find the symmetric key 'x', because it does not exist or you do not have permission - Error in SQL Server by serverku

Recently I ran into an issue while executing stored procedure and issue was with permission of symmetric keys. Here I am sharing an error and fix of that issue. Following is the script run against the user database in which we got a problem. Here SymmetricKeyCert is a certificate name and SymmetricKeyTest is a symmetric key name in the example.

Error :
Cannot find the symmetric key 'x', because it does not exist or you do not have permission.

Fix : 

USE <DB Name>
GO

-- SELECT * FROM sys.certificates
-- Find associated certificate name 
-- Grant permission to SYMMETRIC KEY and ON CERTIFICATE 

GRANT CONTROL ON CERTIFICATE :: SymmetricKeyCert TO [UserTest]
GO
GRANT REFERENCES ON SYMMETRIC KEY::SymmetricKeyTest TO [UserTest]
GO
GRANT VIEW DEFINITION ON SYMMETRIC KEY::SymmetricKeyTest TO [UserTest]
GO

This is a what I got a solution and I would like to request to share some such type of permission related issues and workaround for same, may it helps to readers. Have a great day.

Script to generate ADD COLUMN statements for existing table - SQL Server by serverku

As we have seen some posts about replication, today I want to share a script which generates a tsql statement to add columns in an existing table. It helps when we need to add columns in an existing table from source and need to add in the destination. So let me share a query here,

SELECT 
'ALTER TABLE '+QUOTENAME(TABLE_NAME)
+' ADD '+QUOTENAME(COLUMN_NAME)+' ' 
+ QUOTENAME(DATA_TYPE)
+ CASE 
  WHEN DATA_TYPE LIKE '%CHAR%' THEN '('+REPLACE(CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(50)),'-1','MAX') +')' 
  ELSE '' 
 END 
+ CASE 
  WHEN DATA_TYPE IN ('NUMERIC','DECIMAL') THEN '('+CAST(NUMERIC_PRECISION AS VARCHAR(50)) + ',' + CAST(NUMERIC_SCALE AS VARCHAR(50)) + ')' 
  ELSE '' 
 END 
 + CASE 
  WHEN IS_NULLABLE = 'YES' THEN ' NULL' 
  ELSE ' NOT NULL' 
 END 
 + CASE 
  WHEN COLUMN_DEFAULT IS NULL THEN '' 
  ELSE ' DEFAULT '+ COLUMN_DEFAULT 
 END
 FROM INFORMATION_SCHEMA.COLUMNS  
 WHERE TABLE_NAME = '<TableName>' AND TABLE_SCHEMA = '<SchemaName>'
 GO

Note that this is a script about to generates add columns statements without primary key, foreign key ,check constraint and identity. Hope you like this and i request you to share any correction, enhanced script or any different script with full details for the same.

List running Schedule jobs with elapsed time in SQL Server by serverku

A few months ago, I wrote some articles about SQL Server scheduled jobs and details for same which are listed below

1. When exactly scheduled jobs started and completed
2. Find running scheduled jobs in SQL Server - Alternate way
3. Another way to list out running scheduled jobs

Now I am sharing another one script which belongs to above relevant articles with capture all details of executing scheduled jobs with elapsed time. Below is a script which collects all user scheduled jobs which are running for 30 minutes,

USE msdb
GO

SELECT 
 job.Originating_Server as ServerName, 
 job.Name as JobName, 
 activity.run_requested_Date as StartDate,
 DATEDIFF(MINUTE, activity.run_requested_Date, GETDATE()) as ElapsedTime_Minute
FROM dbo.sysjobs_view job WITH (NOLOCK)
INNER JOIN dbo.sysjobactivity activity WITH (NOLOCK)
 ON (job.job_id = activity.job_id)
WHERE run_Requested_date is not null and stop_execution_date is null
AND activity.run_requested_Date > DATEADD(DD,0,DATEDIFF(DD,0,GETDATE()))
AND job.category_id = 0
AND DATEDIFF(MINUTE, activity.run_requested_Date, GETDATE()) >= 30
GO

I would like you to share other ways to capture the same information. Stay tuned for more.

Example of SQL CLR in SQL Server by serverku

After writing some previous posts of replication and mirroring, today i am writing about SQL CLR (common language runtime) server user defined function. As per msdn it is a SQL user-defined function by adding a User-Defined Function to a SQL Server project. After successful deployment, the user-defined function can be called and executed. So let us implement it with one example.

1. Enable clr in SQL Server.

EXEC sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO

2.  This is a sample C# code which have a logic to show up data.

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure()]
    public static void HelloWorld_CS(
        SqlDateTime ParaDateTime, SqlString ParaVarchar, SqlInt64 ParaInt)
    {

        using (SqlConnection conn = new SqlConnection("context connection=true"))
        {
            
            conn.Open();

            SqlCommand command = new SqlCommand("SELECT @ParaVarchar + ' Hello World', @ParaInt + 1, dateadd(dd,1,@ParaDateTime)" , conn);
            command.Parameters.AddWithValue("@ParaVarchar", ParaVarchar );
            command.Parameters.AddWithValue("@ParaInt", ParaInt);
            command.Parameters.AddWithValue("@ParaDateTime", ParaDateTime);

            SqlContext.Pipe.ExecuteAndSend(command);

            conn.Close();
        }
    }
}

3. Register a DLL.

4. Create a assembly with registered DLL.

CREATE ASSEMBLY HelloWorldCLR 
FROM 'C:\SQLCLR\HelloWorldTest.dll'
WITH PERMISSION_SET=SAFE;
GO 

5. Create a stored procedure which calls an assembly.

CREATE PROCEDURE Proc_CLR_HelloWorld
(
 @DateTest DATETIME,
 @VarcharTest NVARCHAR(100),
 @IntTest BIGINT
)
AS 
EXTERNAL NAME
    HelloWorldCLR.StoredProcedures.HelloWorld_CS
GO 

6. Finally, run that stored procedure with parameters.

EXEC Proc_CLR_HelloWorld 
 @DateTest ='01/01/2014',
 @VarcharTest = 'Test',
 @IntTest = 1
GO

You might work with SQL CLR and I would like to share your inputs and ideas the way you are using SQL CLR. Hope you like this post.

Script to Database Mirroring in SQL Server by serverku

Apart from replication articles which I have written earlier, today i want to share for database mirroring configure with windows authentication without automatic failover. So let me share steps and required scripts for the same.

1. Make sure principal database has FULL recovery model.

2. Make sure Windows User on which mirror configured has enough access of both servers and databases.

3. Make sure TCPIP port open in firewall for both SQL Server instances on both server accordingly if firewall enabled.

4. Make sure database service running on windows user for which mirroring endpoint will be created.

5. Take a full backup of principal database and restore at partner server with norecovery.

6. Take a transaction backup of principal database and restore at partner server with norecovery and make sure all log backups restored with norecovery created after full backup of principal database.

7. Create a mirroring endpoint.

USE master
go

CREATE ENDPOINT [DatabaseMirroring]
AUTHORIZATION [<DomainName>\<UserName>]
STATE=STARTED
AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
FOR DATA_MIRRORING (ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE
, ENCRYPTION = DISABLED)

8.  Get mirroring role and status details for confirmation.

USE master
go

SELECT 
 state_desc,
 type_desc 
FROM sys.database_mirroring_endpoints

9. Make sure 5022 port or whatever port used should be open in firewall if firewall enabled.

10. Run following command at partner server.

USE master
go

ALTER DATABASE <DatabaseName>
SET PARTNER ='TCP://<PrincipalHostName>.<DomainName>.local:5022'

11. Run following command at principal server.

USE master
go

ALTER DATABASE <DatabaseName>
SET PARTNER ='TCP://<PartnerHostName>.<DomainName>.local:5022'

12.Run following command in one of server if want to change high performance or high safety as per need and if it is supported.

USE master
go

-- OFF : High performance
-- FULL : High Safety
ALTER DATABASE <DatabaseName>
SET SAFETY OFF 

Hope you like this post and might be useful to you.

Move Publisher database to another drive without affecting replication - SQL Server by serverku

A month ago, I wrote some articles related to replication configuration which are following,

Skip distributor agent error in sql server transactional replication - How to
Configuration Replication failure and retry alert-SQL Server

Hope you read and liked it. Now moving one another article and it is about to move the publisher database to another drive in the same server without breaking whole replication. So let me share some steps and continue with same as below,

1. Go to Replication Monitor from Replication tab under default instance the option with right click.

2. Make sure all undistributed command processed and should be 0 for all subscriber.

3. Go to agent tab (to disable)
         a. Go to snapshot agent option in combo box, right click and stop agent (to disable) or start agent (to enable) if it is running.
         b. Go to Log reader agent option in combo box, right click and stop agent (to disable) or start agent (to enable) if it is running.  
         c. Go to Queue reader agent option in combo box, right click and stop agent (to disable) or start agent (to enable) if it is running.

4. Making database restricted and offline it.

 ALTER DATABASE PublisherDB SET restricted_user with rollback immediate;
 ALTER DATABASE PublisherDB SET OFFLINE;

5. Mapping database with new file location.

ALTER DATABASE PublisherDB MODIFY FILE ( NAME = pdb_Data , FILENAME = 'D:\data\pdb_data.mdf' );
ALTER DATABASE PublisherDB MODIFY FILE ( NAME = pdb_log , FILENAME = 'D:\data\pdb_log.ldf' );

6. Stop SQL Database service and copy database files to new mapped location.

7. Making database online and make available with multi user.

ALTER DATABASE PublisherDB SET ONLINE;
ALTER DATABASE PublisherDB SET multi_user;

8. Repeat step 3 with “Go to agent tab (to enable)” and finish this task.

Please note these scripts run against publisher server. These are just steps and hope you found it useful. Thanks for reading.

Add new articles in existing publications without Reinitialize All Subscriptions - SQL Server Replication by serverku

The last time we saw a script to add tables, stored procedures, functions and indexed views in a publication and hope you may like that post. Let us continue here one more addition to post and see how can we add a new article in existing publication without reinitialize all subscriptions in transaction replication. Following are the steps which drive to finish this post,
 
Step  1 :
First add articles through the scripts provided in an earlier post, Here for samples we will add tables in existing publication and run on publisher database.

USE PublisherDB
GO
EXEC sp_addarticle 
 @publication = N'PublicationName', 
 @article = N'TableName', 
 @source_owner = N'SchemaName', 
 @source_object = N'TableName', 
 @type = N'logbased', 
 @description = N'', 
 @creation_script = N'', 
 @pre_creation_cmd = N'drop', 
 @schema_option = 0x00000000080350DF, 
 @identityrangemanagementoption = N'manual', 
 @destination_table = N'TableName', 
 @destination_owner = N'SchemaName', 
 @status = 24, 
 @vertical_partition = N'false', 
 @ins_cmd = N'CALL [sp_MSins_SchemaNameTableName]', 
 @del_cmd = N'CALL [sp_MSdel_SchemaNameTableName]', 
 @upd_cmd = N'SCALL [sp_MSupd_SchemaNameTableName]',
 @force_invalidate_snapshot = 1
GO

Step 2 :
After running above script in publisher database, run following script in the publisher database too.

USE PublisherDB
GO
EXEC sp_refreshsubscriptions '<Publication Name>'
GO

Step 3 :
Final completion both above steps we will just start snapshot agent for that publication from Replication Monitor.

Go to Replication Monitor
Select publication,
Move to Agents tab,
Right click on snapshot agent and start agent. 

You will see number of added articles in last action message there after completion of snapshot agent. This is just I want to share with you and maybe help you a lot. Thanks for reading this post and may you like to.

Script to add articles in publication - SQL Server transactional replication by serverku

Recently I wrote a post which having a script to get articles details added in replication for all publications. Today I am sharing a script to add articles in the existing publication of transactional replication, which we can also add through user interface of publication property. Here I am sharing a query to add tables, views, stored procedures, indexed views and functions.

1. Table :

USE PublisherDB
GO
EXEC sp_addarticle 
 @publication = N'PublicationName', 
 @article = N'TableName', 
 @source_owner = N'SchemaName', 
 @source_object = N'TableName', 
 @type = N'logbased', 
 @description = N'', 
 @creation_script = N'', 
 @pre_creation_cmd = N'drop', 
 @schema_option = 0x00000000080350DF, 
 @identityrangemanagementoption = N'manual', 
 @destination_table = N'TableName', 
 @destination_owner = N'SchemaName', 
 @status = 24, 
 @vertical_partition = N'false', 
 @ins_cmd = N'CALL [sp_MSins_SchemaNameTableName]', 
 @del_cmd = N'CALL [sp_MSdel_SchemaNameTableName]', 
 @upd_cmd = N'SCALL [sp_MSupd_SchemaNameTableName]',
 @force_invalidate_snapshot = 1
GO

2. View :

USE PublisherDB
GO
EXEC sp_addarticle 
 @publication = N'PublicationName', 
 @article = N'ViewName', 
 @source_owner = N'schemaName', 
 @source_object = N'ViewName', 
 @type = N'view schema only', 
 @description = N'', 
 @creation_script = N'', 
 @pre_creation_cmd = N'drop', 
 @schema_option = 0x0000000008000001, 
 @destination_table = N'ViewName', 
 @destination_owner = N'schemaName', 
 @status = 16
GO

3. Stored Procedure :

USE PublisherDB
GO
EXEC sp_addarticle 
 @publication = N'PublicationName', 
 @article = N'ProcedureName', 
 @source_owner = N'SchemaName', 
 @source_object = N'ProcedureName', 
 @type = N'proc schema only', 
 @description = N'', 
 @creation_script = N'', 
 @pre_creation_cmd = N'drop', 
 @schema_option = 0x0000000008000001, 
 @destination_table = N'ProcedureName', 
 @destination_owner = N'SchemaName', 
 @status = 16
GO

4. Indexed View:

USE PublisherDB
GO
EXEC sp_addarticle 
 @publication = N'PublicationName', 
 @article = N'Indexed View Name', 
 @source_owner = N'SchemaName', 
 @source_object = N'Indexed View Name', 
 @type = N'indexed view schema only', 
 @description = N'', 
 @creation_script = N'', 
 @pre_creation_cmd = N'drop', 
 @schema_option = 0x0000000008000001, 
 @destination_table = N'Indexed View Name', 
 @destination_owner = N'SchemaName', 
 @status = 16
GO

5. Function :

USE PublisherDB
GO
exec sp_addarticle 
 @publication = N'PublicationName', 
 @article = N'FunctionName', 
 @source_owner = N'SchemaName', 
 @source_object = N'FunctionName', 
 @type = N'func schema only', 
 @description = N'', 
 @creation_script = N'', 
 @pre_creation_cmd = N'drop', 
 @schema_option = 0x0000000008000001, 
 @destination_table = N'FunctionName', 
 @destination_owner = N'SchemaName', 
 @status = 16
GO

Please note these queries run against publisher server and database. I will add one more post next to add articles in existing publications without initialize whole subscription. So stay tuned for more!

Script to get articles detail - SQL Server Replication by serverku

Before a some months, I shared a one script which is relevant to replication as below, Please have a read here. Sometime we need to have information of articles for reports or to check the article exist or not, in which publication it have and for which subscribers. So this kind of details we can achieve with below script which is run against in publication database,

USE PublisherDB
GO

SELECT sub.srvname, -- Subscriber Server name 
       sub.dest_db, -- Subscriber Database Name
       pub.name, -- Publication name
       art.name, -- Article name
       art.dest_table, -- Published Object name
       art.dest_owner -- Published Schema name
FROM   sysextendedarticlesview art 
       INNER JOIN syspublications pub 
               ON ( art.pubid = pub.pubid ) 
       INNER JOIN syssubscriptions sub 
               ON ( sub.artid = art.artid ) 

GO

Hope this script may be very useful to you and you enjoyed a lot. Have a great day and stay tuned for more!

Perform database backup using SQLCMD utility in SQL Server by serverku

I just went through the SQLCMD command line utility to perform database backup in SQL server where it does not have SQL agent. Below is the command which I placed in a batch file and schedule with windows task scheduler.

    Sqlcmd -UUserName -PPassword -SServerName -dDatabaseName -q"DECLARE @backupFilePath varchar(100); SET @backupFilePath = 'D:\BackupPath\DBName\'+'DBName_'+REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR,GETDATE(),120),'-',''),':',''),' ','_') + '.bak'; Backup database DBName to disk = @backupFilePath "
    

Above command perform a full database backup, but it does not quit from utility after performing it. So I have changed with slight change and placed -Q in place of -q for cmdline query. Otherwise next schedule will be skipped as of current schedule never come out from sqlcmd utility and keep running

    Sqlcmd -UUserName -PPassword -SServerName -dDatabaseName -Q"DECLARE @backupFilePath varchar(100); SET @backupFilePath = 'D:\BackupPath\DBName\'+'DBName_'+REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR,GETDATE(),120),'-',''),':',''),' ','_') + '.bak'; Backup database DBName to disk = @backupFilePath "
    

This will perform backup and quit from the sqlcmd utility.

Delete files using Ole Automation Procedures in SQL Server by serverku

To delete the files using Ole Automation Procedures in SQL Server we need to first enable 'Ole Automation Procedures' using sp_configure as following,

exec sp_configure 'Ole Automation Procedures', 1
go
reconfigure
go

After enabling it, we can delete the files with Ole Automation Procedures which using FSO (File System Object) from SQL Server and we need to pass method name 'DeleteFile' in the tsql script. You can delete all the files or specific files as well.

DECLARE @Result int
DECLARE @FSO_Token int
EXEC @Result = sp_OACreate 'Scripting.FileSystemObject', @FSO_Token OUTPUT
EXEC @Result = sp_OAMethod @FSO_Token, 'DeleteFile', NULL, 'D:\TestFolder\*.txt'
EXEC @Result = sp_OADestroy @FSO_Token

Find SSIS package details scheduled in jobs - SQL Server by serverku

I would like to share the script to find out the scheduled jobs which call SSIS packages in SQL Server.

USE MSDB
GO
SELECT 
 sj.job_id as JobId,
 sj.name as JobName,
 sjs.step_name as StepName,
 sjs.Command as Command
FROM sysjobs sj 
INNER JOIN sysjobsteps sjs
 ON(sj.job_id = sjs.job_id)
 WHERE sjs.subsystem = 'SSIS'
GO

The script return job name and the SSIS package full path as a command like "/FILE " here SSIS package full path" /CHECKPOINTING OFF /REPORTING E"

Download all files through FTP in SQL Server by serverku

We have completed the reading of earlier methods to receive single file and receive file dynamically through the FTP in SSIS. Hope you like those posts. Previously we have downloaded a file which has specific extension with format of current date. The same thing and criteria will be repeated again, but one thing is different and it's not downloading a single file and not the specific current date format. It's a download of multiple files which are having an extension with current year like you can see in below shot.

1. Here you can see the files from FTP, among them we will download files meet with the criteria. You can see target files.

2. Drag and drop FTP Task as shown on screen

3. Choose new FTP connection option for to connect FTP and apply FTP configuration as shown below.

4. Here we want to download files, so we need to go ahead with 'Receive' operation.

5. Configure Local Folder for file to be downloaded.

6. Create one variable for the Remote Path for the FTP and assign the value of the FTP directory from where the file will be received.

7. Here we haven't given static value of Remote Path and file name, but the qualified name will be created dynamically. So apply expression for the Remote Path option like following.

8. Finally, execute the created SSIS package and you will see the files received in the local folder. You can see the target files received.

What is the next? Stay tuned for the next posts.

The database owner SID recorded in the master database differs from the database owner SID recorded in database by serverku

Before week one, day I received a lot of error in the SQL Server error log which causing log file full add I checked and found the following error continuously occurred for various service broker queues.

The activated proc '<SP name>' running on queue '<Queue Name>' output the following:  'The database owner SID recorded in the master database differs from the database owner SID recorded in database '<DB Name>'. You should correct this situation by resetting the owner of database '<DB Name>' using the ALTER AUTHORIZATION statement.'

As per error, it is needed for resetting the owner of the database. So first found the owner of the database and apply following command with using 'sa' in my case.

ALTER AUTHORIZATION ON DATABASE::[DB Name] to SA
GO

Finally, it resolved and came from errors in SQL server error log. I would like to invite to share such errors and solutions here, which may be a help to all readers.

Download multiple files through the FTP in SQL Server - SSIS by serverku

We have completed the reading of earlier methods to receive single file and receive file dynamically through the FTP in SSIS. Hope you like those posts. Previously we have downloaded a file which has specific extension with format of current date.

The same thing and criteria will be repeated again, but one thing is different and it's not downloading a single file and not the specific current date format. It's a download of multiple files which are having an extension with current year like you can see in below shot.

1. Here you can see the files from FTP, among them we will download files meet with the criteria. You can see target files.

2. Drag and drop FTP Task as shown on screen.

3. Choose new FTP connection option for to connect FTP and apply FTP configuration as shown below.

4. Here we want to download files, so we need to go ahead with 'Receive' operation.

5. Configure Local Folder for file to be downloaded.

6. Create one variable for the Remote Path for the FTP and assign the value of the FTP directory from where the file will be received.

7. Here we haven't given static value of Remote Path and file name, but the qualified name will be created dynamically. So apply expression for the Remote Path option like following.

8. Finally, execute the created ssis package and you will see the files received in the local folder. You can see the target files received.

What is the next? Stay tuned for the next posts.

Download file having specific extension in SQL Server - SSIS by serverku

Earlier we have seen some of the methods to download single file from FTP using tsql scripts and SSIS. So it's the same with single file, but a little bit different as we will download the file which have specific extension. Like we will have to receive the files which have extension of specific format of current date.

1. Here you can see the files from FTP, among them we will download one file. You can see target file.

2. Drag and drop FTP Task as shown in screen.

3. Choose new FTP connection option for to connect FTP and apply FTP configuration as shown below.

4. Here we want to download files, so we need to go ahead with 'Receive' operation.

5. Configure Local Folder for file to be downloaded.

6. Create one variable for the Remote Path for the FTP and assign the value of the FTP directory from where the file will be received.

7. Here we haven't given static value of Remote Path and file name, but the qualified name will be created dynamically. So apply expression for the Remote Path option like following.

8. Finally, execute the created ssis package and you will see the file received in the local folder. You can see the target file received in local directory.

Just it. Please share your ideas and comments here. Hope you enjoyed this post. Stay tuned for more posts.

Change transfer mode from ASCII to BINARY for files upload via FTP - SQL Server by serverku

Recently I have shared a script to upload files via FTP in SQL Server, And I also mentioned that it will use ASCII transfer mode. Here I want to revise that script which will use BINARY transfer mode instead of ASCII. Please check out below are updated script with the addition of some code.

DECLARE 
@FTPServer varchar(128) ,
@FTPUser varchar(128) ,
@FTPPWD varchar(128) ,
@FTPPath varchar(128) ,
@FTPFileName varchar(128) ,
@SourcePath varchar(128) ,
@SourceFile varchar(128) ,
@workdir varchar(128),
@workfilename varchar(128),
@cmd varchar(1000)

SELECT @workfilename = 'ftpcmd.txt'

SET @workdir = 'C:\FTP\'
SET @FTPServer = 'Server Name'
SET @FTPUser = 'FTP User Name'
SET @FTPPWD = 'FTP User Password'
SET @SourceFile = 'Source File Path'
SET @SourceFile = 'Source File Name'
SET @FTPPath = 'File Path'
SET @FTPFileName = 'File Name'

SELECT @cmd = 'echo ' + 'open ' + @FTPServer + ' > ' + @workdir + @workfilename
EXEC master..xp_cmdshell @cmd
SELECT @cmd = 'echo ' + @FTPUser + '>> ' + @workdir + @workfilename
EXEC master..xp_cmdshell @cmd
SELECT @cmd = 'echo ' + @FTPPWD + '>> ' + @workdir + @workfilename
EXEC master..xp_cmdshell @cmd
----------------- Added new command -----------------------
-- Change trnasfer mode from default to BINARY
SELECT @cmd = 'echo BIN' + '>> ' + @workdir + @workfilename                 
EXEC master..xp_cmdshell @cmd
----------------- End Added --------------------------------
SELECT @cmd = 'echo ' + 'put ' + @SourcePath + @SourceFile + ' ' + @FTPPath + @FTPFileName + ' >> ' + @workdir + @workfilename
EXEC master..xp_cmdshell @cmd
SELECT @cmd = 'echo ' + 'quit' + ' >> ' + @workdir + @workfilename
EXEC master..xp_cmdshell @cmd
SELECT @cmd = 'ftp -s:' + @workdir + @workfilename
-- Executing final step
EXEC master..xp_cmdshell @cmd
GO

This reference is taken from here. Hope you like this.

Upload multiple files from source folder via FTP in SQL Server by serverku

Here is the script to upload all files from source folder using FTP. In the script I just added command to set source folder path and destination folder path, used mput command to put all files instead of individually.

DECLARE 
@FTPServer varchar(128) ,
@FTPUser varchar(128) ,
@FTPPWD varchar(128) ,
@FTPPath varchar(128) ,
@FTPFileName varchar(128) ,
@SourcePath varchar(128) ,
@SourceFile varchar(128) ,
@workdir varchar(128),
@workfilename varchar(128),
@cmd varchar(1000)

SELECT @workfilename = 'ftpcmd.txt'

SET @workdir = 'C:\FTP\'
SET @FTPServer = 'Server Name'
SET @FTPUser = 'FTP User Name'
SET @FTPPWD = 'FTP User Password'
SET @SourcePath = 'Source File Path'
SET @FTPPath = 'File Path'
-- We don't need file name as moving all files
-- SET @SourceFile = 'Source File Name'
-- SET @FTPFileName = 'File Name'

SELECT @cmd = 'echo ' + 'open ' + @FTPServer + ' > ' + @workdir + @workfilename
EXEC master..xp_cmdshell @cmd
SELECT @cmd = 'echo ' + @FTPUser + '>> ' + @workdir + @workfilename
EXEC master..xp_cmdshell @cmd
SELECT @cmd = 'echo ' + @FTPPWD + '>> ' + @workdir + @workfilename
EXEC master..xp_cmdshell @cmd

-- Changing trnasfer mode from default to BINARY
SELECT @cmd = 'echo BIN' + '>> ' + @workdir + @workfilename                 
EXEC master..xp_cmdshell @cmd

----------------- Start Change --------------------------------------------
-- Setting destinaton ftp path 
SELECT @cmd = 'echo cd '+ @FTPPath  + '>> ' + @workdir + @workfilename                 
EXEC master..xp_cmdshell @cmd 
              
-- Setting source path               
SELECT @cmd = 'echo lcd '+ @SourcePath  + '>> ' + @workdir + @workfilename                 
EXEC master..xp_cmdshell @cmd 

-- SELECT @cmd = 'echo ' + 'put ' + @SourcePath + @SourceFile + ' ' + @FTPPath + @FTPFileName + ' >> ' + @workdir + @workfilename
-- Putting all files from source folder path to destination folder path
SELECT @cmd = 'echo ' + 'mput *.bak >> ' + @workdir + @workfilename                  
EXEC master..xp_cmdshell @cmd 
----------------- End Change ----------------------------------------------

SELECT @cmd = 'echo ' + 'quit' + ' >> ' + @workdir + @workfilename
EXEC master..xp_cmdshell @cmd
SELECT @cmd = 'ftp -i -s:' + @workdir + @workfilename
-- Executing final step
EXEC master..xp_cmdshell @cmd
GO

This reference is taken from here. Thank you for reading this post. Stay tuned for more.

Script to download file via FTP in SQL Server by serverku

I want to share the script to download files via FTP in SQL Server, which need work directory to create an FTP command file to execute. This script will use ASCII transfer mode as a default to download files.

DECLARE
 @FTPServer varchar(128) ,
 @FTPUser varchar(128) ,
 @FTPPWD  varchar(128) ,
 @FTPPath varchar(128) ,
 @FTPFileName varchar(128) ,
 @SourcePath varchar(128) ,
 @SourceFile varchar(128) ,
 @workdir varchar(128),
 @cmd varchar(1000),
 @workfilename varchar(128)

SET @FTPServer = 'FTP Server Name'
SET @FTPUser = 'FTP User Name'
SET @FTPPWD = 'Password'
SET @FTPPath = '/subfolder1/subfolder2/' -- or '' if not having subfolders
SET @FTPFileName = 'FTP File Name'
SET @SourcePath = 'Local Path for download'
SET @SourceFile = 'Local File Name to be saved as'
SET @workdir = 'C:\FTP\'
SET @workfilename = 'ftpcmd.txt'
 
-- Writing steps to working file
SELECT  @cmd = 'echo '+ 'open ' + @FTPServer+ ' > ' + @workdir + @workfilename
EXEC master..xp_cmdshell @cmd
SELECT  @cmd = 'echo '+ @FTPUser+ '>> ' + @workdir + @workfilename
EXEC master..xp_cmdshell @cmd
SELECT  @cmd = 'echo '+ @FTPPWD+ '>> ' + @workdir + @workfilename
EXEC master..xp_cmdshell @cmd
SELECT  @cmd = 'echo '+ 'get ' + @FTPPath + @FTPFileName + ' ' + @SourcePath + @SourceFile+ ' >> ' + @workdir + @workfilename
EXEC master..xp_cmdshell @cmd
SELECT  @cmd = 'echo '+ 'quit'+ ' >> ' + @workdir + @workfilename
-- Executing steps from working file
EXEC master..xp_cmdshell @cmd
SELECT  @cmd = 'ftp -s:' + @workdir + @workfilename
-- Executing final step
EXEC master..xp_cmdshell @cmd

This reference is taken from here.

Download single file from FTP in SQL Server - SSIS by serverku

Here I am sharing the same thing, but it will be using SSIS instead of tsql script. Here I just want to show you a demo to download single file from an FTP. So let us move to the current post and follow the steps for same,

1. Here you can see the files from FTP, among them we will download one file.

2. Drag and drop FTP Task as shown in screen.

3. Choose new FTP connection option for to connect FTP and apply FTP configuration as shown below.

4. Here we want to download files, so we need to go ahead with 'Receive' operation.

5. Configure Local Folder for file to be downloaded.

6. From Remote Path option, select one of the file from FTP which is going to be received in local folder which we have configured in the previous step.

7. Finally, execute the created ssis package and you will see the file received in the local folder.

Hope you enjoyed this post. Stay tuned for more posts. Stay tuned for more!