Happy Coding ...

-- TO REMOVE ALL UNWANTED TERMS FROM THE STRING AND DISPLAY CHINESE , RUSSIAN AND ENGLISH, DUTCH, GERMAN....  CHARACTERS   CREATE PROCEDURE   [dbo] . [XXX]           @UserName nvarchar ( 32 )         AS         BEGIN           -- SET NOCOUNT ON added to prevent extra result sets from           -- interfering with SELECT statements.           SET NOCOUNT ON ;            Declare @strText as nvarchar ( 500 )        select @strText = Replace ( SearchText , N'^' , N'' )        from TABLENAME   where UserName = @UserName                 ...

A  little girl was talking to her teacher about whales.   The teacher said it was physically impossible for a whale to swallow  a human because even though it was a very large mammal its  throat was very small. The little girl stated that Jonah was swallowed by a whale. Irritated, the teacher reiterated that a whale could not swallow  a human; it was physically impossible.  The little girl said, 'When I get to heaven I will ask Jonah.' The teacher asked, 'What if Jonah went to hell?' The little girl replied, 'Then you ask him.'     A  Kindergarten teacher was observing her classroom of children  while they were drawing. She would occasionally walk around  to see each child's work. As she got to one little girl who was working diligently,  she asked what the drawing was. The girl replied, 'I'm drawing God.'   The teacher paused and said,  'But no one knows what God looks like.' Without missing a beat, o...

This example shows how to manage all exceptions that haven't been caught in the try-catch sections (in Windows Forms application). The  UnhandledException event  handles uncaught exceptions thrown from the main UI thread. The  ThreadException event  handles uncaught exceptions thrown from non-UI threads. [C#] static void Main() { Application .EnableVisualStyles(); Application .SetCompatibleTextRenderingDefault( false ); Application . ThreadException += new ThreadExceptionEventHandler (Application_ThreadException); AppDomain .CurrentDomain. UnhandledException += new UnhandledExceptionEventHandler (CurrentDomain_UnhandledException); Application .Run( new Form1 ()); } static void Application_ThreadException( object sender, ThreadExceptionEventArgs e) { MessageBox .Show(e.Exception.Message, "Unhandled Thread Exception" ); // here you can log the exception ... } static void CurrentDomain_UnhandledException( object sender, UnhandledExce...

Adding a linked Server using the GUI There are two ways to add another SQL Server as a linked server.  Using the first method, you need to specify the actual server name as the “linked server name”.  What this means is that everytime you want to reference the linked server in code, you will use the remote server’s name.  This may not be beneficial because if the linked server’s name changes, then you will have to also change all the code that references the linked server.  I like to avoid this method even though it is easier to initially setup.  The rest of the steps will guide you through setting up a linked server with a custom name: To add a linked server using SSMS (SQL Server Management Studio), open the server you want to create a link from in object explorer. In SSMS, Expand Server Objects -> Linked Servers -> (Right click on the Linked Server Folder and select “New Linked Server”) Add New Linked Server The “New Linked Server” Dialog a...

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 ...

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...

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 . WebPag...

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 ha...

--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 E...

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%'