TSQL Unit Testing

TSQL Unit Testing

http://tsqlt.org/

Error :Attempted to access an unloaded appdomain

Attempted to access an unloaded appdomain. (Exception from HRESULT: 0x80131014)

This occurs when we try to open two different connections in the same  Transaction Scope . Use Transaction Scope.Suppress for one of the connection if you are not performing any transaction in one of the connection.


But if you are making transactions in both of the connections as a single one ... i need to study further about this ... probably you have to open a new transaction under the main one .. will verify later 

Generating Enums from Database for Lookup Tables

These are the two links i found for this problem.



http://erraticdev.blogspot.com/2011/01/generate-enum-of-database-lookup-table.html


http://idisposable.co.uk/2010/03/using-t4-to-generate-enums-from-database-lookup-tables/#viewSource





 I implemented the second one as i found it easier and more flexible in naming the enums differently from the database look-ups.I struggled with this for a few hours as i did not one thing . i.e When we add t4 template to VS make sure that it  in the properties of the t4 file the custom tool is set to "TextTemplatingFileGenerator". The default one is the text processing .. so i could not see the enum file .. Finally after some time i could identify this.




This is really nice because you find your code more readable while not loosing flexibility of look-ups. Every time you have a new look-up you have to regenerate the t4 template and build your project.


Thanks to people who wrote these templates :). It made my life easier.

Passing Table Valued Parameters to SQL Server Stored Procedures

Passing Table Valued Parameters to SQL Server Stored Procedures

Can also be used to pass arrays to SQL Server
1) Improves performance
2) Easy to Handle
3) Get Rids of  Changing Comma Separated values to table in SQL.

1) Works only with SQL Server 2008 and above

http://msdn.microsoft.com/en-us/library/bb675163.aspx
http://www.dotnetspeaks.com/DisplayArticle.aspx?ID=47

Convert to Camel Case -- SSRS

StrConv(text,StrConv.ProperCase)

SQL Server restore

SQL Server - Take Back up  and restore and execute to fix the orphaned users. When u restore u dont find the user in msdb. so u have to execute this  .. (not sure abt this )

exec sp_change_users_login 'auto_fix','UserName',NULL,'Password'

SSRS Select All MultiSelect

http://arcanecode.com/2010/07/14/sql-server-2008-reporting-services-and-the-select-all-parameter-issue/

Auditing in SQL Server 2008

http://weblogs.asp.net/jgalloway/archive/2008/01/27/adding-simple-trigger-based-auditing-to-your-sql-server-database.aspx.

http://www.simple-talk.com/sql/database-administration/pop-rivetts-sql-server-faq-no.5-pop-on-the-audit-trail/

http://msdn.microsoft.com/en-us/library/bb522489.aspx

SSMS Tools Pack Download

SSMS Tools Pack Download

Wonderful Add-in for Sql Server Management Studio

Query to get retreive a query in a session

DECLARE @sqltext VARBINARY(128)
SELECT @sqltext = sql_handle
FROM sys.sysprocesses
WHERE spid = (sessionid)
print @sqltext
SELECT TEXT
FROM sys.dm_exec_sql_text(@sqltext)
GO

list of the tables from the sql server database

SELECT *
FROM sys.tables
GO

The script above provides various information from create date to file stream, and many other important information. If you need all those information, that script is the one for you. However, if you do not need all those information, I suggest that you run the following script:

EXEC sys.sp_tables
GO

The script above will give all the tables in the table with schema name and qualifiers. Additionally, this will return all the system catalog views together with other views. This Stored Procedure returns all the tables first in the result set, followed by views.

Even though Stored Procedure returns more numbers of rows, it still performs better than the sys.table query.

List of all the Views from Database in SQL Server

SELECT SCHEMA_NAME(schema_id) AS schema_name
,name AS view_name
,OBJECTPROPERTYEX(OBJECT_ID,'IsIndexed') AS IsIndexed
,OBJECTPROPERTYEX(OBJECT_ID,'IsIndexable') AS IsIndexable
--,*
FROM sys.views;

Get Column names from SQL Server Database

use database_name
Sp_help table_name

This stored procedure gives all the details of column, their types, any indexes, any constraints, any identity columns and some good information for that particular table.

Second method:

select column_name ‘Column Name’, data_type ‘Data Type’, character_maximum_length ‘Maximum Length’ from information_schema.columns where table_name = ‘table_name’