Archive

Archive for June, 2015

SQL Error – Execution of user code in the .NET Framework is disabled. Enable “clr enabled” configuration option

June 26, 2015 Leave a comment

 
Error Message:

Executed as user: DOMAIN\Account. Execution of user code in the .NET Framework is disabled. Enable “clr enabled” configuration option. [SQLSTATE 42000] (Error 6263). The step failed.
 

–> Resolution:

-- show advanced options
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO

-- enable clr enabled
EXEC sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO

-- check if it has been changed
EXEC sp_configure 'clr enabled'
GO

-- hide advanced options
EXEC sp_configure 'show advanced options', 0
GO
RECONFIGURE
GO
Advertisement
Categories: SQL Errors Tags:

SQL Error – SQL Server blocked access to procedure ‘dbo.sp_send_dbmail’ of component ‘Database Mail XPs’

June 25, 2015 2 comments

 
Error Message:

Executed as user: DOMAIN\Account. SQL Server blocked access to procedure ‘dbo.sp_send_dbmail’ of component ‘Database Mail XPs’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘Database Mail XPs’ by using sp_configure. For more information about enabling ‘Database Mail XPs’, search for ‘Database Mail XPs’ in SQL Server Books Online. [SQLSTATE 42000] (Error 50000). The step failed.
 

–> Resolution:

-- show advanced options
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO

-- enable Database Mail XPs
EXEC sp_configure 'Database Mail XPs', 1
GO
RECONFIGURE
GO

-- check if it has been changed
EXEC sp_configure 'Database Mail XPs'
GO

-- hide advanced options
EXEC sp_configure 'show advanced options', 0
GO
RECONFIGURE
GO
Categories: SQL Errors Tags:

new CTP 2.1 update for SQL Server 2016 available for download

June 25, 2015 Leave a comment

Today Microsoft announced an update of the recently released Community Technology Preview (CTP) 2 version of SQL Server 2016, the CTP 2.1.
 

[Register and Download the CTP 2.1 Evaluation version (180 days) here]

 

Direct download link:
– SQLServer2016CTP2.1-x64-ENU.box
– SQLServer2016CTP2.1-x64-ENU.exe
 

As per the Microsoft [SQL Server Blog], now people do not have to wait for several months for the release of next CTP versions. Now customers can experience the Rapid Preview models and bits for their dev & test purpose.
 

–> This rapid release includes some improvements and fixes to the new features added in SQL Server 2016 release, and as follows:

1. Stretch Database – Demo video

2. Query Store

3. Temporal Data, added support for computed columns and Period columns with HIDDEN flag – Demo video.

4. Columnstore Index, improved seek/scan performance – Demo video.
 

Check the [SQL Server blog] for all these updates in detail.
 


 

Check & Like my FB Page

Categories: SQL Server 2016 Tags: ,

SQL Error – SQL Server blocked access to procedure ‘sys.xp_cmdshell’ of component ‘xp_cmdshell’

June 24, 2015 Leave a comment

 
Error Message:

Executed as user: DOMAIN\Account. 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’, search for ‘xp_cmdshell’ in SQL Server Books Online. [SQLSTATE 42000] (Error 50000). The step failed.
 

–> Resolution:

-- show advanced options
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO

-- enable xp_cmdshell
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO

-- check if it has been changed
EXEC sp_configure 'xp_cmdshell'
GO

-- hide advanced options
EXEC sp_configure 'show advanced options', 0
GO
RECONFIGURE
GO
Categories: SQL Errors Tags:

Using GO statement within a Stored Procedure – MSDN TSQL forum

June 19, 2015 Leave a comment

–> Question:

I want to include GO statements to execute a large stored procedure in batches, how can I do that?

I have an SQL statement that is broken down into various sections, as it drops a temp table, then populates it, then generates another temp table based on the first one, then drops the first temp table and so on…….it enables me to execute all the code in one go.

I end each statement/block with a GO clause, to ensure it finishes running before the next peice of code.

However, when I try to incorprate this into a stored procedure, I cannot use the GO command. If I remove all the GO lines, the sp will run, but can I be confident that it’s running how it should, and that the lines of code near the end will not begin to execute before the lines above it have?

I’ve used a semicolon at the end of each statement in the sp?
 

–> My Answer:

First of all “GO” is not a SQL statement or command, this is just a keyword that your SSMS understands, and you can even change it to anything, check here.

Secondly “GO” is a batch separator that you use in SQL Queries, and Stored Procedure is itself a batch. If you put GO within a Stored Procedure, it will be an incomplete SP, and the rest part after GO will not be a part of the SP. More on MSDN about GO, link.

Now, why do you want to use GO within an SP?

If the SP is large, then try creating multiple SP and execute them in sequence.
 

–> Another Answer by Erland:

You cannot call script files from stored procedures. As I said two years ago, SQL Server is a server application – and SQLCMD is a client.

It’s a little confusing because you seem to use DML to mean DDL. (DML is Data Manipulation Language, that is INSERT, UPDATE, DELETE and also SELECT according to some, while DDL is Data Definition Language, that is CREATE/ALTER TABLE etc.)

Yes, if you want to interleave DDL and DML, you will need to make sure that the DML is not compiled until the DDL it depends on has been executed. Three solutions:

1) Separate batches. (Don’t forget to check @@trancount in the beginning of each batch.)=
2) But most commands in EXEC().
3) Have a program (in C# or whatever) to run the whole thing.

My preference is for the latter.
 

Ref Link and this.

Categories: SQL Server Questions Tags: , ,