Difference Between SET and SELECT

SET and SELECT statements are similar in many ways but what are the major differences between them?

This post explains the main differences between set and select statements.

Select Statement:
[sql]
DECLARE @i INT
SELECT @i = 10
[/sql]

SET Statement:
[sql]
DECLARE @i INT
SET @i = 10
[/sql]

1) SET is ANSI Standard for value assignment to variables but SELECT is not an ANSI Standard for variable assignment.

2) SET can be used to assign value to one variable at a time but select can be used to assign values to multiple variables in a single select statement.

The below query using SELECT is valid:
[sql]
DECLARE @i INT,
@j INT,
@k INT

SELECT @i = 10,@j = 20,@k = 30
[/sql]
Output:
Command(s) completed successfully.

The below query using SET is not valid:
[sql]
DECLARE @i INT,
@j INT,
@k INT

SET @i = 10,@j = 20,@k = 30
[/sql]
It gives error:

Msg 102, Level 15, State 1, Line 5
Incorrect syntax near ‘,’.

3) When an output of a query is used to assign values to a variable then SET Statement would fail and give an error if multiple rows are returned by the query but the select statement would assign the last result of the query to the the variable.

The below query using select will execute successfully:
[sql]
DECLARE @i INT

SELECT @i = n FROM (VALUES (10),(20),(30)) AS List(n)

SELECT @i
[/sql]
Output:
———–
30

(1 row(s) affected)

The below query using set will fail:
[sql]
DECLARE @i INT

SET @i = (SELECT n FROM (VALUES (10),(20),(30)) AS List(n))
[/sql]

Error:

Msg 512, Level 16, State 1, Line 5
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, , >= or when the subquery is used as an expression.

4) If the variable is initially assigned a value following is the behavior of variable assignment using set and select.
Set – Assigns null if the query does not return any rows.
Select – Retains the initially assigned value and does not assign null if the query does not return any rows.

The output of the below statement will be 1
[sql]
DECLARE @i INT

SET @i = 1

SELECT @i = n FROM (VALUES (10),(20),(30)) AS List(n) WHERE 1=2

SELECT @i
[/sql]

Output:

———–
1

(1 row(s) affected)

The output of the below statement will be NULL
[sql]
DECLARE @i INT

SET @i = 1

SET @i = (SELECT n FROM (VALUES (10),(20),(30)) AS List(n) WHERE 1=2)

SELECT @i
[/sql]

Output:
———–
NULL

(1 row(s) affected)

5) Select has better performance over set when used for assigning values to multiple variables at the same time.

Assigning values to multiple variables using Select:
[sql]
DECLARE @i INT,
@j INT,
@k INT

SELECT @i = 10,@j = 20,@k = 30
[/sql]

Assigning values to multiple variables using SET:
[sql]
DECLARE @i INT,
@j INT,
@k INT

SET @i = 10
SET @j = 20
SET @k = 30
[/sql]

6) There are many other differences with respect to syntax and uses of SET and Select.
This post is mainly tuned for value assignment to variables using SET and Select.

enable xp_cmdshell on SQL Server

How to enable xp_cmdshell on SQL Server 2005/2008?

If xp_cmdshell is disabled on SQL Server you get the following error:
Query:
[sql]
EXEC xp_cmdshell ‘dir’
[/sql]


SQL Server blocked access to procedure ‘sys.xp_cmdshell’ of component ‘xp_cmdshell’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘xp_cmdshell’ by using sp_configure. For more information about enabling ‘xp_cmdshell’, see “Surface Area Configuration” in SQL Server Books Online.

In order to enable this SQL Server has to be configured to allow access to procedure xp_cmdshell.

Following are the steps for that:

[sql]
EXEC sp_configure ‘show advanced options’, 1
reconfigure
GO
[/sql]

This enables to show advanced options by sp_configure.

Now if you execute sp_configure
[sql]
EXEC sp_configure
[/sql]

The following will be the output in the results window:

xp_cmdshell disabled
xp_cmdshell disabled

Run the following query to enable this:
[sql]
EXEC sp_configure ‘xp_cmdshell’,1
reconfigure
GO
[/sql]

Now you will be able to execute xp_cmdshell.

More about using sp_configure can be found here

The media family on device is incorrectly formed. SQL Server cannot process this media family Error: 3241

When you try to restore a backup of the database you get the following error:


The media family on device ” is incorrectly formed. SQL Server cannot process this media family.
RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3241)

This error occurs due to the following problems:

Fix:

  • If you feel backup could be corrupted take the backup again
  • If you are using FTP then use it in binary mode to copy the backup file into local drive
  • If the versions are different then update the SQL Server on the system into which the backup has to be restored
  • Verify the database backup to check what the issue is.
    Following post helps you verify your database backup: Verify SQL Server Database Backup
  • More about using restore verify only this option in SQL Server can be found here

Enable OPENROWSET

When you try to execute queries involving OPENROWSET on SQL Server you get the following error:


SQL Server blocked access to STATEMENT ‘OpenRowset/OpenDatasource’ of component ‘Ad Hoc Distributed Queries’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘Ad Hoc Distributed Queries’ by using sp_configure. For more information about enabling ‘Ad Hoc Distributed Queries’, see “Surface Area Configuration” in SQL Server Books Online.

This occurs basically when Ad Hoc Distributed Queries is disabled.

So how to enable this:

Simple by just changing the configuration of SQL Server using sp_configure
More about using sp_configure can be found here

Following are the steps to enable Ad Hoc Distributed Queries on SQL Server:

Open SQL Server Management Studio (SSMS)

Create a new query on master database
Run the following query:
[sql]
sp_configure ‘show advanced options’,1
reconfigure
[/sql]

This enables sp_configure to show advanced options

Now run sp_configure

As shown in the screenshot below you can see ‘Ad Hoc Distributed Queries’ having config_value of 0

Ad Hoc Distributed Queries disabled
Ad Hoc Distributed Queries disabled

To enable this run the below query

[sql]
sp_configure ‘Ad Hoc Distributed Queries’,1
reconfigure
[/sql]

Now if you run sp_configure you will find it enabled as shown in the screen shot below

Ad Hoc Distributed Queries Enabled
Ad Hoc Distributed Queries Enabled

If you run the query with OPENROWSET now you do not get the same error again.

Enjoyed enabling Ad Hoc Distributed Queries? I enjoyed it 🙂

CREATE PROXY – STEP BY STEP – IN DETAIL

How to create proxy in SQL Server?

Berore reading this post if you want to understand what a proxy is then you can go through the following post here

So now coming to creation of proxy there are basically two ways:

First method would be using the SSMS(SQL SERVER MANAGEMENT STUDIO) – Object Explorer
Second method would be using CREATE PROXY Query.

First I would explain how to create proxy using Create proxy query and later in the post I would show how to create proxy using SSMS Object Explorer.

Create Proxy using SQL QUERY:

Syntax for Creating proxy:
[sql]
sp_add_proxy @proxy_name = <<name_of_the_proxy>> ,
@description = <<description_of_proxy>> ,
@credential_name = <<name_of_the_credential>> ,
@credential_id = <<credential_id>> ,
@enabled = <<1 or 0>> ,
@proxy_id = <<proxy_id >> OUTPUT
[/sql]

So in the steps to create proxy here first we need to create a credential.
Further information about creating credentials can be found here

First let us create a credential,
lets call it cred_jhon,
So let us suppose the username and passowrd be jhon and pass000

Query to create credential:
[sql]
USE
[master]
GO
CREATE CREDENTIAL [cred_jhon] WITH IDENTITY = N’jhon’, SECRET = N’pass000′
GO
[/sql]

Now that credential is created we can create an agent proxy now.

Query to create proxy:

[sql]
declare @proxy_id int
exec sp_add_proxy @proxy_name = ‘proxy1’,
@description = ‘simple proxy’,
@credential_name = ‘cred_jhon’, –The credential that we created above.
@enabled = 1, –its enabled
@proxy_id = @proxy_id OUTPUT

SELECT @proxy_id
[/sql]

procedure sp_add_proxy has to be run on msdb database.
execute permissions on sp_add_proxy are given to the members of sysadmin role.

The above query creates a proxy using credential cred_jhon.
Now that the proxy is created but this will be un assigned as we did not map it to any subsystem.
More info about subsystems can be found here

To map this proxy to a subsystem the procedure sp_grant_proxy_to_subsystem has to be used.

[sql]

EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N’proxy1′, @subsystem_id=11
GO

[/sql]

The above command maps this proxy to SSIS Subsystem.

Following are the subsystemid’s:

subsystem_id Sub System
2 Microsoft ActiveX Script
3 Operating System
4 Replication Snapshot Agent
5 Replication Log Reader Agent
6 Replication Distribution Agent
7 Replication Merge Agent
8 Replication Queue Reader Agent
9 Analysis Services Command
10 Analysis Services Query
11 SSIS package execution
12 PowerShell Script

Creating Proxy Using Object Explorer – SSMS:

Following are the steps to create proxy using SSMS:
Open SQL SERVER MANAGEMENT STUDIO

Connect to SQL server in object explorer

Connect to sql server in object explorer
Connect to sql server in object explorer

In object explorer expand SQL Server Agent >> Proxies
Note: If SQL Server Agent is disabled then check if the agent service is started in services.msc

Expand SQL Server Agent and Proxies
Expand SQL Server Agent and Proxies

RightClick on Proxies and click on New Proxy

Right Click On Proxies And Click On New Proxy
Right Click On Proxies And Click On New Proxy

Now Enter the proxy name

Enter Proxy Name
Enter Proxy Name

Then cllick on the button beside “Credential Name” box (highlighted button in above image)

Then Select Credential window appears

Select Credential Window
Select Credential Window

Now click on Browse and select the credential that you want to use for proxy and click on OK

Browse for Credentials
Browse for Credentials

Information about creating credentials can be found here

Now click on Ok in Select Credential window
In the “Active to the following subsystems” tab select the subsystems for the proxy

Select Sub Systems
Select Sub System

Then click on OK to create the Proxy

In order to view the newly created proxy, expand the subsystem that is mapped to the Proxy.

expand the subsystem that is mapped to the Proxy
expand the subsystem that is mapped to the Proxy

Now you have created a proxy in sql server in two ways.

Hope this post helped you!!!!

SUBSYSTEMS OF PROXIES – SQL SERVER

SUBSYSTEMS OF SQL SERVER PROXIES

Following are the different subsystems of SQL Server Proxies:

  • ActiveX Script
  • Operating System (cmdExec)
  • Replication Distributor
  • Replication Merge
  • Replication Queue Reader
  • Replication Snapshot
  • Replication Transaction-Log Reader
  • SQL Server Analysis Services Command
  • SQL Services Analysis Services Query
  • SQL Server Integration Services Package
  • Power Shell

Why so many subsystems?

SQL Server Proxies are mainly used to impersonate different users authentication for a job step.
So if we open the a job step we find the same subsystems shown above as types.

Below are the screen shots that explain in more detail.

New Proxy – Sub Categories:

New Proxy
New Proxy - Sub Categories

New Job Step – Types:

New Job Step
New Job Step - Types

Hence the Subcategories in Proxies are related to Job types in Jobs.

ALTER CREDENTIAL SYNTAX – SQL SERVER

ALTER CREDENTIAL SYNTAX

SYNTAX:
[sql]
ALTER CREDENTIAL credentialname WITH IDENTITY = ‘identityname’
, SECRET = ‘secret’
[/sql]

The IDENTITY and SECRET for a credential can be changed using ALTER CREDENTIAL.

Example of Alter Credential:
[sql]
USE [master]
GO
ALTER CREDENTIAL [credential1] WITH IDENTITY = N’username1′, SECRET = N’password1′
GO
[/sql]

USING SQL SERVER MANAGEMENT STUDIO (SSMS) TO ALTER CREDENTIAL:

  • Open SSMS and connect to the server in object explorer.
  • Expand server and Security
  • In Security Expand

    Alter Credential
    Double Click on the credential name
  • DoubleClick on the Credential name
  • Credential properties dailog will pop up

    Credential Properties
    Credential Properties
  • Now change the credential properties such as IDENTITY, Password and click on Ok

CREATE CREDENTIAL SYNTAX – SQL SERVER

CREATE CREDENTIAL SYNTAX:

Syntax for creating credentials:
[sql]
CREATE CREDENTIAL credentialname WITH IDENTITY = ‘identity_name’
, SECRET = ‘secret’
FOR CRYPTOGRAPHIC PROVIDER cryptographic_provider_name
[/sql]

credentialname – Name of the credential, this is used for refereceing the credential
IDENTITY – This is basically the username
SECRET – Password of the identity/username.
CRYPTOGRAPHIC PROVIDER – Name of Enterprise Key Management Provider.

SECRET and cryptographic_provider_name are optional in the above syntax.

Example:
[sql]
USE [master]
GO
CREATE CREDENTIAL [credential1] WITH IDENTITY = N’username’, SECRET = N’password’
GO
[/sql]

CRYPTOGRAPHIC PROVIDER can be created and used with credentials.

Syntax for creating CRYPTOGRAPHIC PROVIDER:
[sql]
CREATE CRYPTOGRAPHIC PROVIDER provider_name
FROM FILE = ‘path of the dll’
[/sql]

The dll provided above should implement SQL Server Extensible Key Management interface.

CREATING CREDENTIAL USING SQL SERVER MANAGEMENT STUDIO:

  • Open SSMS and connect to the server in object explorer.
  • Expand server and Security
  • In Security Expand

    Create Credential
    Expand Security
  • Right Click on “Credentials” and click on “New Credential…”

    Click on new credential
    Click On New Credential
  • Now a New Credential Window will pop up

    New Credential Window
    New Credential Window
  • Now Enter values for Credential Name, Identity, Password.
    Password and Confirm password must match.
  • Now click on OK.

Thats all.. Now you are done creating a sql server credential.. 🙂

WHAT IS CREDENTIAL – SQL SERVER

WHAT IS SQL SERVER CREDENTIAL?

SQL Server credential stores the authentication information which is required to access a resource.

Credentials store the following details:

  • Credential Name – Name of the credential, this is used for refereceing the credential
  • Identity – This is basically the username
  • Password – Password of the identity/username.
  • Encryption Provider – Authentication details can be encrypted using this option.

Basic Example:
[sql]
USE [master]
GO
CREATE CREDENTIAL [credential1] WITH IDENTITY = N’username’, SECRET = N’password’
GO
[/sql]

In the above example Credential1 is created with identity :username and password : password

The created credentials can be used in agent proxies

WHAT IS PROXY – SQL SERVER

SQL SERVER PROXY

SQL Server Proxy is a named holder of SQL Server Credentials.
SQL Server Proxies are basically used to definte the security context of a job step.
These proxies provide the job with the security credentials that were defined by the proxy.
The sql server agent first imporsinates the security credentials of the proxy and then runs the job step.

For example if there are two sql servers, server A and the other server B.
There is a requirement where in one step in the agent job on server A has to access the data from one of the tables in server B.
Let us suppose X has read permissions on server B.
Now in order to give the Job step permission on server B the following steps are performed on server A:

  • New Credential for X is created (i.e Identity and password are saved)
  • New Proxy is created using the credential created above.
  • The proxy is mapped with the subsystem as required.(this depends on what the job step does)
  • The job step is modified to use this proxy.