Happy Coding ...

The ALTER DATABASE command allows a database administrator to modify SQL Server databases and their files and filegroups. This includes permitting the changing of database configuration options. Why Read Only ? When you need to ensure that the data is a database is not modified by any users or automated processes, it is useful to set the database into a read-only mode. Once read-only, the data can be read normally but any attempts to create, updated or delete table rows is disallowed. This makes the read-only mode ideal when preparing for data migration, performing data integrity checking or when the data is only required for historical reporting purposes. Make Database Read Only USE  [master] GO ALTER DATABASE  [TESTDB]  SET  READ_ONLY  WITH  NO_WAIT GO Make Database Read/Write USE  [master] GO ALTER DATABASE  [TESTDB]  SET  READ_WRITE  WITH  NO_WAIT GO In case you get the following error message make the database single user: Msg 5070, Level 16, Stat

It is often necessary to change the database to single user mode, especially if you are a DBA. A simple example would be to change the collation settings or any DB settings. The single user will allow only one  user ( usually DBA) to access the database. Hence it will be easy to make changes without the worry of deadlocks or any other type of contention for DB and also without affecting the users. It is very easy to change the database to Single user mode; in fact, it is just an execution of the script away. Use the script below to change the mode. ALTER  DATABASE  < >   SET SINGLE_USER    WITH  NO_WAIT The  NO_WAIT  clause will set it to single user mode as soon as you execute the query.  An alternate  to this is by using the  system  stored procedure  sp_dboption EXEC     SP_DBOPTION    << Data base  Name>>, ‘SINGLE USER’  ,False You need to however set the database  back to multi user mode for other users to use it. Without that, it won’t be access

ASP.NET MVC 4 can be installed side by side with ASP.NET MVC 3 on the same computer, which gives you flexibility in choosing when to upgrade an ASP.NET MVC 3 application to ASP.NET MVC 4. The simplest way to upgrade is to create a new ASP.NET MVC 4 project and copy all the views, controllers, code, and content files from the existing MVC 3 project to the new project and then to update the assembly references in the new project to match the old project. If you have made changes to the Web.config file in the MVC 3 project, you must also merge those changes into the Web.config file in the MVC 4 project. To manually upgrade an existing ASP.NET MVC 3 application to version 4, do the following: In all Web.config files in the project (there is one in the root of the project, one in the Views folder, and one in the Views folder for each area in your project), replace every instance of the following text: System . Web . Mvc , Version = 3.0 . 0.0 System . Web . WebPages , Version

There are four levels in the naming convention for any SQL Server object [ServerName.[DatabaseName.[SchemaName.]]]ObjectName Schema Name (or Ownership) --the object created is assigned to a schema rather than an owner. Whereas an owner related to one particular login, a schema can now be shared across multiple logins, and one login can have rights to multiple schemas --For object not belonging to default schema state, use the schema name of your object. The Default Schema: dbo::::::::::::::::::::::::::::: --for user of a database MySchema(login name) a table my.table created will have a ownerqualified object name would be MySchema.MyTable. So to access this table we need to use the name MySchema.MyTable (as this is created by a user) --for database owner fred, a table created as myTable , ownerqualified object name would be dbo.MyTable. ****as dbo also happens to be the default owner, any user could just refer to the table as MyTable. --sa (sysadmin role)will always have f

--To get a list of all databases select name from master.dbo.sysdatabases Order by name -- To get a list of users and role name select b.name as USERName, c.name as RoleName from DatabaseName.dbo.sysmembers a join DatabaseName.dbo.sysusers b on a.memberuid = b.uid join DatabaseName.dbo.sysusers c on a.groupuid = c.uid -- list of username and their roles SELECT UserName, Max(CASE RoleName WHEN 'db_owner' THEN 'Yes' ELSE 'No' END) AS db_owner, Max(CASE RoleName WHEN 'db_accessadmin ' THEN 'Yes' ELSE 'No' END) AS db_accessadmin , Max(CASE RoleName WHEN 'db_securityadmin' THEN 'Yes' ELSE 'No' END) AS db_securityadmin, Max(CASE RoleName WHEN 'db_ddladmin' THEN 'Yes' ELSE 'No' END) AS db_ddladmin, Max(CASE RoleName WHEN 'db_datareader' THEN 'Yes' ELSE 'No' END) AS db_datareader, Max(CASE RoleName WHEN 'db_datawriter' THEN 'Yes

Register a Server View - Registered Servers (Ctrl + Alt + G) will display the Registered Servers window Click on Local Server Groups to get the list of Registered servers To add a server to the list - right click and select 'New Server Registration' Enter the server name and authentication details and click on test. Save the server. If you have saved the credentials double clicking on the server name will connect to the machine automatically Object Explorer Right click on the server name and click on Object Explorer the window will be opened. Now you can access the database from here Error List window It displays all the errors encountered on that query page Template Explorer This is a very useful window. All the templates which may be useful to you are accessible here . SQL Server Profiler A very useful tool in SQL

Getting Started with TFS 2010 http://blogs.msdn.com/b/jasonz/archive/2009/10/21/tutorial-getting-started-with-tfs-in-vs2010.aspx Test Automation with Microsoft Visual Studio 2010: Coded UI Tests and Lab Management:    http://channel9.msdn.com/Events/TechEd/NorthAmerica/2011/DEV309 Subversion: http://www.codeproject.com/KB/dotnet/SourceControl_VSNET.aspx CLR Stored Procedures - sys.assemblies: http://www.codeproject.com/KB/cs/CLR_Stored_Procedure.aspx Ajax: http://www.asp.net/ajaxLibrary/AjaxControlToolkitSampleSite/Default.aspx With the experts: http://www.virtualtechdays.com/ SQL Server 2008 Report Builder: Video:  http://msdn.microsoft.com/en-us/library/dd299411(v=SQL.100).aspx Article: http://www.simple-talk.com/sql/reporting-services/beginning-sql-server-2005-reporting-services-part-1/ http://msdn.microsoft.com/en-us/sqlserver/aa336316.aspx Export Test Cases to Excel from TFS http://exporttfs2excel.codeplex.com/releases/view/70526 Details Exported: Work

For a list of open connections for a specific database you can run the following command: select spid from master..sysprocesses where dbid = db_id('Works') and spid <> @@spid Kill all open connections to a specific database: DECLARE @DatabaseName nvarchar(50) DECLARE @SPId int SET @DatabaseName = N'Works' DECLARE my_cursor CURSOR FAST_FORWARD FOR SELECT SPId FROM MASTER..SysProcesses WHERE DBId = DB_ID(@DatabaseName) AND SPId <> @@SPId OPEN my_cursor FETCH NEXT FROM my_cursor INTO @SPId WHILE @@FETCH_STATUS = 0 BEGIN KILL @SPId FETCH NEXT FROM my_cursor INTO @SPId END CLOSE my_cursor DEALLOCATE my_cursor

To read the XML file in SQL we need to use sp_xml_preparedocument and : Syntax sp_xml_preparedocument hdoc OUTPUT -- Is the handle to the newly created document. hdoc is an integer. [ , xmltext ] -- original XML document. [ , xpath_namespaces ] Example DECLARE @hdoc INT, @params_xml XML = ' ' EXEC sp_xml_preparedocument @hdoc OUTPUT, @params_xml The above command, reads the XML text provided as input, parses the text by using the MSXML parser sp_xml_preparedocument returns a handle that can be used to access the newly created internal representation of the XML document. This handle is valid for the duration of the session or until the handle is invalidated by executing sp_xml_removedocument. A parsed document is stored in the internal cache of SQL Server. The MSXML parser uses one-eighth the total memory available for SQL Server. To avoid running out of memory, run sp_xml_removedocument to free up the memory. PUT XML into variables OPENXML provides a rowset view over an XML d

Step1: Get the Servers Run the following command to get the list of all linked servers. sp_linkedservers Note: sp_helpserver can also be used to list the available servers Step 2: Add the server you want to connect to [This is important, because most people mess up here] To add a linked server we will use the following command sp_addlinkedserver EXEC sp_addlinkedserver @server=N'S1_instance1', @srvproduct=N'', @provider=N'SQLNCLI', @datasrc=N'S1\instance1'; Step 3: Query the Active Directory DECLARE @Application TABLE (cn varchar(50)); DECLARE @ApplicationCN varchar(50); DECLARE @SQLString nvarchar(MAX); DECLARE @ApplicationName varchar(20)= 'yy' -- name of the container DECLARE @Role varchar(20) = 'xxx' DECLARE @Domain nvarchar(20) = 'a.com' -- if this is a.com SET @SQLString='SELECT cn FROM OPENQUERY(ADSI,''SELECT cn FROM ''''LDAP://' +@Domain +''''

Sometimes you have situation, where in you want to figure out which function a particular word / object is used in.  You can use the following query for this purpose. This query is useful if you want to search in which function a particular table is being used.    SELECT   OBJECT_NAME(id), TEXT    FROM     syscomments    WHERE    [text] LIKE '%proc_name%'   --          AND OBJECTPROPERTY(id, 'IsProcedure') = 1 You can also use this query for the purpose    SELECT *    FROM   sysobjects     WHERE name LIKE '%email%'

Ever came across a situation where you know a column name but don't remember the table name. This is the third incident that i forgot the table name and so thought of penning it down. How I Solved It? I will just share the queries that will help you get this done. I am sure that would be more than enough. Query 1:                              SELECT  *                              FROM    sys.columns                              WHERE   name LIKE   '%column_name%' This query will give you list of object_id and names that has the column name in it Using the object id we can find the object. Query 2:                              SELECT  *                              FROM    sys.objects                              WHERE   object_id IN  (..., ... , obejct ids from query 1..., ...) This query will give you the object name and type.  The object of type U are user tables. These tables are list of tables that have the column that we we were looking for

While accessing the active directory (AD) and authorization manager (AZMAN) , If you get “   Insufficient access rights to perform the operation. (Exception from HRESULT: 0x80072098)  “ message check the    account that is being used to get the LDAP query from AD .  ERROR DETAILS Exception Details:  System.Runtime.InteropServices.COMException: Insufficient access rights to perform the operation. (Exception from HRESULT: 0x80072098) Source Error: Line 154:    'Session("FullName") = System.Security.Principal.WindowsIdentity.GetCurrent.Name.ToString() Line 155: Line 156:    If Not User.IsInRole("Role1") Then Line 157:          Response.Redirect("./Login.aspx") Line 158:    End If  Stack Trace : .... SOLVE IT Steps to do check the app pool rights: Click on the website name that you are having problem with in IIS  In the right panel you will see 'Basic Settings'. Click It. Select the specific pool option and enter the name of the ac

Basics: To insert explicit value in the identity column of a table we need to set identity_insert On. Syntax for this is SET IDENTITY_INSERT tablename ON Now execute all your insert / update queries Make sure you set the identity_insert to Off. Syntax for this is SET IDENTITY_INSERT tablename OFF Did you know: Identity insert is session sensitive . If you have an identity_insert On for another table in that session you will get the message " IDENTITY_INSERT is already ON for table 'xxx'. Cannot perform SET operation for table 'tablename'." To test this you can open another session and try executing the same statement again. You will not get the error message this time. Option 2: Step 1: Go to the Table name in the Object Explorer and click on Design. Step 2: In column Properties, go to 'Identity Specification' and select 'Is identity' as 'No' Step 3: Click on Save. Now try the insert/update command. Still does not 

To get a list of TRIGGERS in SQL database use : SELECT      S2.[name] TableName     , S1.[name] TriggerName     , CASE      WHEN S1.deltrig > 0         THEN 'Delete'     WHEN S1.instrig > 0          THEN 'Insert'     WHEN S1.updtrig > 0          THEN 'Update'     END 'TriggerType'  FROM sysobjects S1 JOIN sysobjects S2  ON S1.parent_obj = S2.[id]  WHERE S1.xtype='TR' Want more Detail? Use : SELECT      S2.[name] TableName     , S1.[name] TriggerName     , CASE          WHEN S2.deltrig = s1.id              THEN 'Delete'          WHEN S2.instrig = s1.id              THEN 'Insert'          WHEN S2.updtrig = s1.id              THEN 'Update'      END 'TriggerType'      , 'S1'     ,s1.*     ,'S2'     ,s2.*  FROM sysobjects S1  JOIN sysobjects S2  ON S1.parent_obj = S2.[id]  WHERE S1.xtype='TR'

In a FOR XML clause, you specify one of these modes: RAW - generates a single <row> element per row in the rowset that is returned by the SELECT statement AUTO - generates nesting in the resulting XML by using heuristics based on the way the SELECT statement is specified EXPLICIT -  provides the most flexibility in generating the XML PATH - a simpler alternative to writing EXPLICIT mode queries Examples for RAW:    Query 1:  Basic                       SELECT doc_id ,                                      media_id                       FROM DOC                       FOR XML RAW Result 1:                        <row doc_id="114" media_id="-117" />                       <row doc_id="115" media_id="-113" /> Query 2:  Using Customized name instead of "row" in the result                       SELECT doc_id ,                                      media_id                       FROM DOC                   

Check to see if a proc exists before dropping it if exists (select name from sysobjects where type = 'P' and name  = 'XX')    drop proc XX go

Reading a value from the XML file and displaying it in SQL DECLARE @MyXML XML SET @MyXML = ' ' 'print convert(nvarchar(500), @MyXML) SELECT a.b.value('a[1]/BibCodingSearch[1]/@CaseID','varchar(10)') AS CaseID, a.b.value('a[1]/BibCodingSearch[1]/@BibCoding_Id','varchar(10)') AS BibCoding_Id, a.b.value('a[1]/BibCodingSearch[1]/@Value','varchar(10)') AS Value, a.b.value('a[1]/BibCodingSearch[1]/@Operator','varchar(10)') AS Operator FROM @MyXML.nodes('xml') a(b) UNION SELECT a.b.value('a[1]/BibCodingSearch[2]/@CaseID','varchar(10)') AS CaseID, a.b.value('a[1]/BibCodingSearch[2]/@BibCoding_Id','varchar(10)') AS CaseIDBibCoding_Id, a.b.value('a[1]/BibCodingSearch[2]/@Value','varchar(10)') AS Value, a.b.value('a[1]/BibCodingSearch[2]/@Operator','varchar(10)') AS Operator FROM @MyXML.nodes('xml') a(b)

Ctrl + C and Ctrl + V is the easiest thing that a person can do in computers ,  but I had the most difficult copy scripts to run. (Well ... I better have an good explanation for wasting so much time in copying stuff over ;P) I had to copy files from one server to another over different domains  ... phew. Thank God that it is over. Writing a simple copy did not help, I used XCOPY. I thought why not share my experience with all, and what better place to share than this. To write a copy script one must keep few things in mind... Keep it simple             xcopy source [destination]                       Destination is not mandatory , if you don't give it then it will copy to the current folder from where you are running the script. Keep every thing in Quotes             xcopy "source" "[destination]"                          Make sure that your source and destination are in quotation marks. That will save you from the horror of not knowing what went wrong

To create a New full text catalog follow the following steps 1. Go to Database => Storage => Full Text Catalog 2. Right clcik and select 'New Full Text Catalog' 3. Enter the name in 'Full Text Catalog Name' (Leave Accent Sesitivity as default). Click OK 4. Right click on the Catalog (you just created) and click on Properties 5. Go to Table/View tab and select the table/view that needs to be assigned to the catalog 6. Select the eligible column and the language for word breaker 7. Let the Track Changes be 'Automatic' . Click on 'Ok' For more info on Full Text visit   http://www.simple-talk.com/sql/learn-sql-server/understanding-full-text-indexing-in-sql-server/

SQL Server programmers can choose between two functions in SQL Server for converting expressions from one type to another. In many cases there will be a need within a stored procedure or other routine to convert data from, say, a datetime type to a varchar type; CONVERT and CAST are used for such things. Because SQL Server provides both functions, there may be some confusion about which is best to use and under what circumstances. CONVERT is specific to SQL Server , and allows for a greater breadth of flexibility when converting between date and time values, fractional numbers, and monetary signifiers. CAST is the more ANSI-standard of the two functions, meaning that while it's more portable (i.e., a function that uses CAST can be used in other database applications more or less as-is), it's also less powerful. CAST is also required when converting between decimal and numeric values to preserve the number of decimal places in the original expression. For those reasons, it

I heard my PM saying today "Their are two types of Idiots. One who will take multiple trips to the database to compare value. Others who will take the values in memory and compare then their". We have a situation. We need to take values from one table and check if it exists in the second table. Depending on weather it exists in the second table or not, we need to display with / without highlight it in the datagrid. There is one catch. The query is written by the user in one of the application screen and the user has no idea that the 2nd condition table needs to be applied. I know you would debate that we need to create a screen, so that the user will select what is required instead of writing the query. I know... but we need this asap. Screen will be in the next release. Let me elaborate: Consider that we have table A and table B. Both these tables have ID column. The user writes (on the application screen) SELECT * FROM A   I can check that this ID does not exist in