:::: MENU ::::

Posts Categorized / SET Options

  • Aug 28 / 2013
  • 0
dbDigger, SET Options, SQL Server Error messages

Error message when creating assembly in a SQL Server database

I was required to transfer SQL Server assembly along with the other objects from one SQL Server instance to another. I used Transfer SQL Server Objects which is a handy control of SSIS. During the test process i get following error as a result of trying to create assembly in target database.

[Transfer SQL Server Objects Task] Error: Execution failed with the following error: “ERROR : errorCode=-1073548784 description=Executing the query “CREATE ASSEMBLY [%]
AUTHORIZATION [dbo]
FRO…” failed with the following error: “CREATE ASSEMBLY for assembly ‘%’ failed because assembly ‘%’ is not authorized for PERMISSION_SET = EXTERNAL_ACCESS.  The assembly is authorized when either of the following is true: the database owner (DBO) has EXTERNAL ACCESS ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with EXTERNAL ACCESS ASSEMBLY permission.”. Possible failure reasons: Problems with the query, “ResultSet” property not set correctly, parameters not set correctly, or connection not established correctly.helpFile= helpContext=0 idofInterfaceWithError={C81DFC5A-3B22-4DA3-BD3B-10BF861A7F9C}”.

This error is produced because before creating the assembly we have to mark the database as trustworthy. By default trustworthy mode is disabled for SQL Server databases. Trustworthy mode tells the server that this database contains controlled creation of objects and there is no chance that some one will create malicious objects in it or some one will manipulate it by attach/detach process. So if you are satisfied with above assumptions that you are going to give to your server then use following command in subject database and set it as a trustworthy database

ALTER DATABASE [DBNameHere] SET TRUSTWORTHY ON
GO

After this step we are now able to create and refer assemblies in SQL Server database and error exists no more.

  • Feb 07 / 2009
  • 0
dbDigger, SET Options, T-SQL Tips and Tricks

Return only meta data through Set FMTONLY

Recently i read an interesting question on SQLServerCentral.com, with main point that what function we can use to get just meta data instead of associated data from a T-SQL statement. It is interesting. After submitting my reply (which was accidentally correct), i read the provided BOL link and came to know that SET FMTONLY on or Off, we may getjust meta data instead of associated data through a T-SQL command. For example consider the impact of using this function

 
Use AdventureWorks  
 GO   
 SET FMTONLY ON  
 GO   
 SELECT * FROM production.Document  
 GO  
 SET FMTONLY off   
 GO  

SET FMTONLY ON
And instead of retreiving whole data, i just got name of columns from my script. You may use it on SQL Srever 2000, SQL Server 2005 and SQL Server 2008. You require membership of Public role to use this function.

  • Aug 06 / 2008
  • 4
dbDigger, Logins and Users, SET Options, SSMS tips and tricks, T-SQL Scripts

Set SQL Server database to single user mode or multi user mode

Often we require to set the database in to single user mode. And after specified operation we switch to multi user mode. Here is a script to perform the switch operation.
For changing AdventureWorks database to single user mode

 
ALTER DATABASE adventureworks  
 SET SINGLE_USER  
 GO  

and for again to multi user mode

 
ALTER DATABASE adventureworks  
 SET MULTI_USER  
 GO  

Also you have to consider that if some users are connected to that database then their connections will be dropped without any warning. And you can not set the system databases master, tempDB, msDB to single user mode. For that purpose you have to start your SQL Server in single user mode.
You can also set a database to single user mode through GUI. For SSMS i will go through for steps

  • Right click on database to be set user mode
  • Go to Options in left panel
  • In right panel you will found ‘Restrict Access’ option
  • Choose single user mode
  • Click OK

Set single user mode through SSMS

Same procedure would be repeated to set the database back to multiple user mode

Consult us to explore the Databases. Contact us