sp_xp_cmdshell_proxy_account – The provided account was invalid or the ##xp_cmdshell_proxy_account## credential could not be created Access is denied

Posted on by deviprasadg

When we try to create a xp_cmdshell proxy using SQL Server Management studio, we get error 15137.

EXEC sp_xp_cmdshell_proxy_account 'domain\account' ,'[password]'

Error:

Msg 15137, Level 16, State 1, Procedure sp_xp_cmdshell_proxy_account, Line 1 [Batch Start Line 1]
An error occurred during the execution of sp_xp_cmdshell_proxy_account. Possible reasons: the provided account was invalid or the ‘##xp_cmdshell_proxy_account##’ credential could not be created. Error code: 5(Access is denied.), Error Status: 0.

Fix:

  • Run SQL Server Management Studio as Administrator
  • Validate and make sure password is correct

Alternatives:
Create a credential

USE [master]
GO
ALTER CREDENTIAL [##xp_cmdshell_proxy_account##] WITH IDENTITY = domain\account', SECRET = N'password'
GO

Performance Tuning – Steps to make sure that you are comparing apples to apples

Posted on by deviprasadg

While working on optimizing performance of SQL Queries, its very important that the right numbers are compared.
Performance numbers might vary with each execution mostly due to SQL Server cache and buffer.

Below are the steps that can be taken to make sure that you are seeing right numbers.

– Clear the cache by running the above Script provided in this Link
– Run the query that you are trying to performance tune and note the Statistics. (a)
– Run the above script again to clear the cache.
– Now execute the tuned query and note down the Statistics. (b)

Query Statistics (a) and (b) would now be comparable as both of them are executed over the clean cache.

TSQL Script to fetch the list of tables with identity properties

Posted on by deviprasadg

Below is the TSQL Script to fetch the list of tables in a database with identity column properties
[sql]
SELECT SCHEMA_NAME(T.SCHEMA_ID) AS SCHEMA_NAME,
OBJECT_NAME(T.OBJECT_ID) AS TABLENAME,
TI.NAME AS COLUMN_NAME,
TI.SEED_VALUE,
TI.INCREMENT_VALUE,
TI.LAST_VALUE AS LAST_VALUE
FROM SYS.TABLES AS T
INNER JOIN
SYS.IDENTITY_COLUMNS AS TI
ON T.OBJECT_ID = TI.OBJECT_ID
[/sql]

TSQL Script to Clear SQL Server Cache

Posted on by deviprasadg

Below script that can be used to clear SQL Server Cache. Helpful while working on performance tuning of SQL Scripts.


CHECKPOINT;
GO
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
DBCC FREESYSTEMCACHE ('ALL')
DBCC FREESESSIONCACHE
GO

Note that this script is solely for running on Development environments, do not run in Production as it would cause issues to queries that depend on cached data.

Stretch DB – SQL Server 2016

Posted on by deviprasadg

Stretch DB feature in SQL Server 2016 helps to move a part of a SQL Server table to the azure for Archival.
It would help in situations where there is a need to archive non transactional Rows from a table, but still need the ability to Query them when needed.

Watch this video for more details.

More Info:
https://msdn.microsoft.com/en-us/library/Dn935011.aspx

A value for the parameter @retention cannot be specified when the job type is ‘capture’. Specify NULL for the parameter, or omit the parameter from the statement.

Posted on by deviprasadg

The Parameter @retention should NOT be supplied with a NOT NULL value when the value of @job_type is ‘capture’

[sql]
EXEC sp_cdc_change_job @job_type=’capture’,@maxtrans = ‘10000’,@retention=200
[/sql]

The above code gives the below error.


Msg 22995, Level 16, State 1, Procedure sp_cdc_change_job_internal, Line 39
A value for the parameter @retention cannot be specified when the job type is ‘capture’. Specify NULL for the parameter, or omit the parameter from the statement.

Resolution:

Remove @retention input parameter.

[sql]
EXEC sp_cdc_change_job @job_type=’capture’,@maxtrans = ‘10000’
[/sql]

HOW TO CONVERT TABLE DATA TO XML AND XML TO TABLE

Posted on by deviprasadg

HOW TO CONVERT TABLE DATA TO XML AND VICEVERSA.

Table to XML and XML to Table

The below code explains how to convert the data in a table to xml form and then convert the xml back into table data.

Creating sample table with data:

CREATE TABLE tmpEmployee(ID INT,NAME VARCHAR(100))
GO

INSERT INTO tmpEmployee
SELECT 1,'Devi'
union
SELECT 2,'Prasad'
GO


SELECT * FROM tmpEmployee
GO

Below code converts data and schema of table tmpEmployee into XML, Then it uses the XML to convert it back into the table:

/*Below code converts Table SCHEMA & Data to XML*/

DECLARE @TableData XML,@TableSchema XML

SELECT @TableSchema = (
select column_name,
data_type,
case(is_nullable)
when 'YES' then 'true'
else 'false'
end as is_nullable,
CHARACTER_MAXIMUM_LENGTH as Charlen
from information_schema.columns [column]
where table_name = 'tmpEmployee'
for xml auto,root('Table')
)

SELECT @TableData = (
SELECT *
FROM tmpEmployee Row
FOR XML AUTO, BINARY BASE64,root('TableData')
)

SELECT @TableSchema,@TableData

/*Below code converts XML to Table*/

if object_id('tempdb..#XMLColumns') is not null
drop table #XMLColumns

SELECT x.value('@column_name', 'sysname') AS column_name
,x.value('@data_type', 'sysname') AS data_type
,x.value('@is_nullable', 'VARCHAR(20)') AS is_nullable
,x.value('@Charlen', 'VARCHAR(20)') AS Charlen
into #XMLColumns
FROM @TableSchema.nodes('/Table/column') TempXML (x)

select * from #XMLColumns

DECLARE @SQL nVARCHAR(MAX) = 'SELECT '

SELECT @SQL = @SQL + '
x.value(''@'+column_name+''', '''+data_type+case when Charlen is null then '' else '('+Charlen+')' end + ''''+') AS ['+column_name+'],'
from #XMLColumns

SET @SQL = LEFT(@SQL, LEN(@SQL) - 1)

SELECT @SQL = @SQL + ' FROM @TableData.nodes(''/TableData/Row'') TempXML (x)'

EXEC sp_executeSQl @SQL,N'@TableData xml',@TableData=@TableData

Output:

tmpEmployee:

ID NAME
----------- ----------------------------------------------------------------------------------------------------
1 Devi
2 Prasad


(2 row(s) affected)

@TableSchema:

<Table>
<column column_name="ID" data_type="int" is_nullable="true" />
<column column_name="NAME" data_type="varchar" is_nullable="true" Charlen="100" />
</Table>

@TableData:

<TableData>
<Row ID="1" NAME="Devi" />
<Row ID="2" NAME="Prasad" />
</TableData>

Table generated from XML:

ID NAME
----------- ----------------------------------------------------------------------------------------------------
1 Devi
2 Prasad


(2 row(s) affected)

All about SQL Server Views

Posted on by deviprasadg

Advanced topics in SQL Server Views:
CHECK OPTION
ENCRYPTION
SCHEMABINDING
VIEW_METADATA

Creating Sample Data:

--DROP TABLE tempForView
CREATE TABLE tempForView(
ID INT
);
GO

INSERT INTO tempForView
SELECT 1
UNION
SELECT 2
UNION
SELECT 3;
GO

Check Option:
Check option ensures that the modification of data in the view will come up in the view again.

Example:

CREATE VIEW vw_TempWithOutCheckOption
AS
SELECT * fROM tempForView WHERE ID > 0
GO

CREATE VIEW vw_TempWithCheckOption
AS
SELECT * fROM tempForView WHERE ID > 0
WITH CHECK OPTION;
GO

Lets try to inset/Update data in view vw_TempWithOutCheckOption.

UPDATE vw_TempWithOutCheckOption SET ID = -1 WHERE ID = 1
GO

INSERT INTO vw_TempWithOutCheckOption(ID)
SELECT -2
GO

SELECT * FROM vw_TempWithOutCheckOption
GO

The above query runs and the data will be inserted/Updated in the underlying table but will not come up in the view.

Now when we try to insert update data in the view vw_TempWithCheckOption 

UPDATE vw_TempWithCheckOption SET ID = -2 WHERE ID = 2
GO

INSERT INTO vw_TempWithCheckOption(ID)
SELECT -3
GO

SELECT * FROM vw_TempWithCheckOption
GO

As we are trying to insert/Update data which would not be part of the view, we are getting the error:

Msg 550, Level 16, State 1, Line 2
The attempted insert or update failed because the target view either specifies WITH CHECK OPTION or spans a view that specifies WITH CHECK OPTION and one or more rows resulting from the operation did not qualify under the CHECK OPTION constraint.
The statement has been terminated.

Encryption:

Encryption can be used when you want to hide the definition of the View.

CREATE VIEW vw_TempWithEncryption
WITH ENCRYPTION
AS
SELECT * fROM tempForView WHERE ID > 0;
GO

CREATE VIEW vw_TempWithOutEncryption
AS
SELECT * fROM tempForView WHERE ID > 0;
GO


SELECT TEXT fROM sys.syscomments WHERE ID = OBJECT_ID('vw_TempWithEncryption')
SELECT TEXT fROM sys.syscomments WHERE ID = OBJECT_ID('vw_TempWithOutEncryption')

The definition of the view that is created with encryption is not present in sys.syscomments.

SchemaBinding:
When a view is created with schema binding then it does not allow DDL modifications to the underlying tables which affect the view definition.

CREATE VIEW vw_TempWWithSchemaBinding
WITH SCHEMABINDING
AS
SELECT ID fROM dbo.tempForView WHERE ID > 0;
GO

DROP TABLE dbo.tempForView

It does not allow to drop the table dbo.tempForView as the view is referencing it and it is created with SCHEMABINDING.


Msg 3729, Level 16, State 1, Line 1
Cannot DROP TABLE ‘dbo.tempForView’ because it is being referenced by object ‘vw_TempWWithSchemaBinding’.

VIEW_METADATA:
when the applications accessing SQL Server request browse-mode metadata SQLServer basically provides the details of the TABLES from which the columns in the views are built.
But when a view is created with this option then SQL Server does not provide the base tables of the columns in the view.

More info can be found in MSDN:
http://msdn.microsoft.com/en-us/library/ms187956.aspx