Delete Files with a SQL Job in SQL Server 2008

Part 1: SQL Query for deleting the files

Suppose you want to delete some files from a directory. This could be one of the easiest tasks for anyone who is familiar with Windows. But what if we want to check the files periodically in some directory and if any file is found, we delete them. To do this we have some options like:

• By a windows service that keeps on checking and deleting files on a regular interval.
• By a SQL Job (Taking advantage of a SQL Server Agent Service)

Here I am going to discuss deleting files using a SQL Job. In a SQL Job, you need to use a SQL query in your SQL Job.

In the SQL query to delete the files, you need to call a predefined stored procedure "xp_cmdshell". But before calling "xp_cmdshell", we also need to enable the Command Shell of your SQL Server if it is not enabled in your system.

Here is the full SQL query that is used for deleting the files.

[Note: The topic "How to create a SQL Job in SQL Server-2008" is discussed in Part-2 of this article.]

----------------------------------- Enabling the COMMAND SHELL ------------------------------

--Script to enable the XP_CMDSHELL
-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO
----------------------------------------------------------------------------------------------

----------------------------------- Deleting the files in ------------------------------------
-- Suppose we need to delete all .txt files under "D:\temp\Testing\"

xp_cmdshell 'DEL D:\temp\Testing\*.txt'

Part 2: Creating a SQL Job
Go to SQL Server Agent and start the Server Agent service.



Right-click the "Jobs" under "SQL Server Agent" and select "New Job…"



On clicking "New Job", the following window opens. In the left pane you see the following options:

• General
• Steps
• Schedule
• Alerts
• Notification
• Targets

In the "General" page, you need to give the name of your job as "FileDeleter" and its description.



Now go to the "Steps" page and click "New" step.



The following window appears. In this step defying window, you need to give your step name (e.g. File Delete). In the command box, paste your SQL query for the file deletion. Since in this job we have only one step, so we need to set this step as a final step. This you could do by going to the "Advanced" page under the "General" page in the same window.



On clicking "Advanced" page, you will see actions for success and failure. Set "on success action" to "Quit the job reporting success" and click OK.



Now we set the schedule of our job. Click the "Schedule" page and create a new schedule by clicking "New...". The following window appears. In this window, give the name of the job schedule and set the occurring frequency as "Daily". Then we set the timing (e.g. 12:00 AM midnight) and duration for how long this SQL job should be working (e.g. Aug 8,2012 to Nov 8, 2012). After setting these parameters, click OK to finish your SQL Job Creation.



After job creation, just right-click the newly created job "FileDeleter" and start the job.

Reference:

Nice Article by  Hemant Srivastava

http://www.c-sharpcorner.com/UploadFile/0f68f2/delete-and-create-files-with-a-sql-job-in-sql-server-2008/

Creating a new Job in Sql Step by Step

This is about adding a new SQL Jobs in the Sql Server 2005.

Step 1

Make sure that the SQL Server Agent is up and running. You can see it in the taskbar icon.

If SQL Server Agent is not running, start it from the SQL Server Configuration Manger

You can also start the SQL Server Agent from the command prompt using the command netstart

net start "SQL Server Agent (<instance name>)"

e.g net start "SQL Server Agent (SQLSERVER01)"

Step 2

Connect to the database engine of SQL server using SQL Server Management Studio.

Step 3

Expand the SQL Server Agent. You will see a Jobs folder over there. Right click on jobs and choose Add New.

Step 4

A New Job popup will appear. Specify the name of the job.

Step 5

Cilck next on the "Steps" in the left menu. A sql job can contain one or more steps. A step might be simply an sql statement or a stored procedure call. Add you step here


Job step added


Cilck next on the "Schedules" in the left menu. A sql job can contain one or more schedules. A schedule is basically the time at which sql job will run it self. You can specify recurring schedules also.


Job schedule added

You sql job is ready now. However there are other thing you can use if needed like Alert, Notifications etc.

Reference:

http://geekswithblogs.net/Gaurav/archive/2011/01/10/143430.aspx





 

Create and Schedule a Job in SQL Server 2008

In this article you will learn how to create a new job and schedule that job for execution in SQL Server 2008.

What is a SQL Job? A job is a specified series of actions that SQL Server Agent performs. Use jobs to define an administrative task that can be run one or more times and monitored for success or failure. A job can run on one local server or on multiple remote servers.

Getting Started

First of all start SQL Server 2008 Management Studio.




Image 1.

For this database I am going to create a job and schedule that job.

Now click on SQL Server agent and select "Jobs" and right-click and click on "New Job".


Image 2.

As you can see there are many steps, so let's go one by one. First provide a job name and description and click "OK".

General Tab:


Image 3.

Now click on "Next". The step name is Steps and provides a name, type and select database and write a command to save in a location and click "OK".

Steps Tab:


Image 4.

Now click on the "Schedules" tab and provide name, type, frequency and click "OK".

Schedules Tab:


Image 5.

Now click on the alerts tab and click on the "Add" button and provide alert name, type, database name and click "Ok".

Alerts Tab:


Image 6.

Now click on the notifications tab and select how you want notifications.

Notifications Tab:


Image 7.

The final tab is the "Targets" tab that shows you the options of the target server, you can also select multiple target servers.

Targets Tab:


Image 8.
Reference:

Nice Article by RajKumar

http://www.c-sharpcorner.com/UploadFile/raj1979/create-and-schedule-a-job-in-sql-server-2008/

Cross farm Service Applications Integration between two separate SharePoint farms

Nice Article by Issa Ayyash

Introduction

A very powerful integration capability is available between SharePoint 2013 and SharePoint 2010/2013, where SharePoint 2010 can still consume service applications from SharePoint 2013 , in this post I will walk you through a step by step guide on how to consume managed meta data service applications resides in SharePoint 2013 from a site resides in SharePoint 2010 farm ,
you can still use the same steps to consume any other service application to achieve cross farm service application, and this post is also applicable if you want to perform the integration between two SP2013 farms.

• In this sample I assume both farms resides on the same domain.
• The steps below can still work between two SP2013 farms as well.

1 -Exchange Trust Certificates between farms

An administrator of the consuming farm (SP2010) must provide two trust certificates to the administrator of the publishing farm

• Root Certificate
• Security token service (STS) Certificate

Administrator of publishing farm (SP2013) must provide one trust certificate to the consuming farm (SP2010)

• Root Certificate

To export Certificates from Consuming Farm

a-To export the root certificate from the consuming farm
in PowerShell run the following commands:
$rootCert = (Get-SPCertificateAuthority).RootCertificate
$rootCert.Export("Cert") | Set-Content "C:\ConsumingFarmRoot.cer" -Encoding byte

b-To export the STS certificate from the consuming farm
$stsCert = (Get-SPSecurityTokenServiceConfig).LocalLoginProvider.SigningCertificate
$stsCert.Export("Cert") | Set-Content "C:\ConsumingFarmSTS.cer" -Encoding byte
under C: drive:

To export the root certificate from the publishing farm

$rootCert = (Get-SPCertificateAuthority).RootCertificate
$rootCert.Export("Cert") | Set-Content "C:\PublishingFarmRoot.cer" -Encoding byte

• Copy the root certificate and the STS certificate from the server in the consuming farm to the server in the publishing farm.
• Copy the root certificate from the server in the publishing farm to a server in the consuming farm

2-Managing trust certificates by using Central Administration

Certificates must be imported on both consuming farm and publishing farm as the following:

a-Establish trust on Publishing farm (SP2013)

• open CA -- Security -- manage trusts -- from ribbon click new ,

• Since this is a publishing farm we need to import the root certificate and STS certificate extracted from the consuming farm SP2010

a new trust will appear

b-Establish trust on Consuming farm SP2010

• CA - under security -- manage trusts - from ribbon select New

• since this is a consuming farm you will import only the root certificate of the publishing farm SP2013.

• a new trust will appear :

3-On the publishing farm, publish the service application

On the publishing farm (SP2013) on which the service application is located, an administrator must explicitly publish the service application. Service applications that are not explicitly published are available to the local farm only

• From CA -- Manage Service Application -- select the service you wish to publish (Managed metadata service application) then select publish from the ribbon

• make sure to check publish this service application to other farms

Information URL : this is an optional info where you can for instance create a page within SharePoint 2013 and place its link here, so admins will read more info about this service application
Important: Copy the Published URL , we are going to use it in the consuming farm later on
urn:schemas-microsoft-com:sharepoint:service:63f708f6c0284e4ba8f54ef14c604e4e#authority=urn:uuid:237032cbe6f84d279136cc24a6
360514&authority=https://2013-sp:32844/Topology/topology.svc

4-Set the permission to the appropriate service applications

You must give the consuming farm permission to the Application Discovery and Load Balancing Service Application on the publishing farm
After doing this, give the consuming farm permission to the published service applications that it will be consuming

• on the consuming farm , you need to get the Farm ID by running the following command:

Get-SPFarm | Select Id

copy the Consuming farm ID : 1ccfc7f5-b62a-4404-962c-359371d2cd60

• On a server in the publishing farm, open CA - manage service applications - select Application discovery service application then from ribbon select permissions

paste the consuming farm ID you extracted previously, then click check names

add the consuming farm ID and grant it full control then press OK


Tip : Or you can optionally use the PowerShell command to achieve the same to above:
$security=Get-SPTopologyServiceApplication | Get-SPServiceApplicationSecurity
$claimprovider=(Get-SPClaimProvider System).ClaimProvider
$principal=New-SPClaimsPrincipal -ClaimType "http://schemas.microsoft.com/sharepoint/2009/08/claims/farmid" -ClaimProvider $claimprovider -ClaimValue 1ccfc7f5-b62a-4404-962c-359371d2cd60
where 1ccfc7f5-b62a-4404-962c-359371d2cd60 is the <consumingfarmid> i extracted from the previous step.
Grant-SPObjectSecurity -Identity $security -Principal $principal -Rights "Full Control"
Get-SPTopologyServiceApplication | Set-SPServiceApplicationSecurity -ObjectSecurity $security

5-Do the same steps with the service application you wish to publish (managed meta data)

add the consuming farm ID and grant it full the required permissions in my case full access to term store is granted

On the consuming farm, connect to the remote service application

After the publishing farm has published the service application, an administrator of the consuming farm can connect to that service application from the consuming farm if the address of the specific service application is known
now from consuming farm (SP2010) you can connect to a service application that has been shared by the publishing farm (SP2013) by providing the address of the farm's discovery service or the address of the service application.
to do so from the consuming farm open CA - manage service application - from ribbon select connect

Place the connection info for the service application extracted from the publishing farm (managed meta data service application)
urn:schemas-microsoft-com:sharepoint:service:63f708f6c0284e4ba8f54ef14c604e4e#authority=urn:uuid:237032cbe6f84
d279136cc24a6360514&authority=https://2013-sp:32844/Topology/topology.svc
then click Ok

Important: the server time (clock) should be synchronized to be the time on both consuming and publishing farm or else a consuming farm wont be able to reach the publishing farm service due to The security timestamp is invalid between servers.
select the service application , make sure to be added to default proxy group ,then click ok





now it will appear in the list of service application of the consuming farm (SP2010)

6-Add the shared service application to a Web application proxy group on the consuming farm

An administrator must associate the new service application connection with a local Web application on the consuming farm
in the consuming farm (SP2010) open CA - under service Applications click configure service applications association make sure the new proxy is added to the default group and assigned to the desired web application

7-Validate your work

Open a site within SharePoint 2010 , create a custom list , from ribbon create column

make sure managed Metadata is selected:

scroll down , you should see now the term sets from SP2013 :)

Additional Scenarios

Note: in some scenarios you might need to allow server to server authentication , if your case is one of the below you need then to configure server to server authentication otherwise you are done, no need to perform any additional steps

• Follow a document on a Team Sites web application when a user’s personal site is located on a My Sites web application. The Team Sites web application makes a request of the My Sites web application on behalf of the user.
• Create or reply to a site feed post for a site that is located on a Team Sites web application but performed through the user’s My Site Newsfeed on the My Sites web application. The My Sites web application will make a request of the Team Sites web application on behalf of the user to write the post or the reply.
• A User Profile Service application task to repopulate the feed cache has to read from the personal site or team site. If the User Profile Service application is running in a different farm, the User Profile Service application sends a request to the My Sites web application or Team Sites web application to read the user or site feed data into the cache

if you need to extend to achieve one of the above scenarios follow the steps in this post then you will be done :) http://technet.microsoft.com/en-us/library/jj992595.aspx


Reference:

http://blogs.technet.com/b/meamcs/archive/2013/09/03/cross-farm-service-applications-integration-between-two-separate-sharepoint-farms.aspx?utm_source=feedburner&utm_medium=feed&utm_campaign=Feed%3A+yahoo%2FZfiM+(Team+blog+of+MCS+%40+Middle+East+and+Africa)