tag:blogger.com,1999:blog-18952801445945980792024-03-19T18:04:58.369+05:00SQL Server Help LineOne of the two SQL Server Speakers of Professional Association for SQL Server in SQLPASS Lahore General Conference. (http://lahore.sqlpass.org/Home.aspx?EventID=5615)
9+ Years of Database Development & Administration experience with Medical Billing, Start up & Financial Companies
Microsoft® Certified Solutions Expert: Data Management and Analytics (SQL Server 2012/2014)
Microsoft® Certified Solutions Expert: Data Platform (SQL Server 2012/2014)Musab Umairhttp://www.blogger.com/profile/00709484447531505692noreply@blogger.comBlogger27125tag:blogger.com,1999:blog-1895280144594598079.post-70183063415735781572016-12-13T01:11:00.001+05:002017-01-20T12:18:05.527+05:00Microsoft SQL Server Non-Contained Object Migration Deployment Procedure using Powershell<div dir="ltr" style="text-align: left;" trbidi="on">
<div class="separator" style="clear: both; text-align: center;">
</div>
<span style="font-family: "verdana" , sans-serif;"><br /></span>
<span style="font-family: "verdana" , sans-serif;"><i>As a DBA we all face the challenge of migration databases from one servers to another or even the whole servers at some times. The Database Migration process is not always a simple Backup and Restore process so we might need a huge amount of effort if we have to migrate objects which are not included in the native backups for a specified database, these objects are called Non-Contained Objects. </i></span><br />
<span style="font-family: "verdana" , sans-serif;"><br /></span>
<span style="font-family: "verdana" , sans-serif;">I have written a detailed article <a href="http://www.sqlshack.com/microsoft-sql-server-non-contained-object-migration-deployment-procedure-using-powershell/" target="_blank">here</a>. </span><br />
<span style="font-family: "verdana" , sans-serif;"><br /></span>
<span style="font-family: "verdana" , sans-serif;">Enjoy ! </span></div>
Musab Umairhttp://www.blogger.com/profile/00709484447531505692noreply@blogger.com0tag:blogger.com,1999:blog-1895280144594598079.post-34543311663176980222016-01-19T12:09:00.001+05:002016-01-19T12:13:02.880+05:00SQL Server 2016 Express LocalDB<div dir="ltr" style="text-align: left;" trbidi="on">
<div style="border: 0px; box-sizing: border-box; font-stretch: inherit; line-height: 32px; margin-bottom: 32px; outline: 0px; padding: 0px; text-align: justify; vertical-align: baseline;">
<span style="background-color: white;"><span style="background-color: white; font-family: "verdana" , sans-serif;">As Microsoft SQL Server 2016 comes with a lot of new and exciting features for SQL Server Database Administrators, the developers do not need to stay away. </span></span><br />
<span style="background-color: white;"><span style="background-color: white; font-family: "verdana" , sans-serif;">Microsoft SQL Server 2016 comes with the exciting new LocalDB feature.</span><span style="background-color: white; font-family: "verdana" , sans-serif;">Its light, easy to install and no management required. </span></span><span style="background-color: white; font-family: "verdana" , sans-serif;">So, easy to use with limitations as of SQL Express but no management required.</span><br />
<span style="background-color: white; font-family: "verdana" , sans-serif;">Microsoft SQL Server 2016 Express </span><strong style="border: 0px; box-sizing: border-box; font-family: verdana, sans-serif; font-stretch: inherit; font-style: inherit; font-variant: inherit; line-height: inherit; margin: 0px; outline: 0px; padding: 0px; vertical-align: baseline;">LocalDB</strong><span style="background-color: white; font-family: "verdana" , sans-serif;"> is an execution mode of SQL Server Express targeted to program developers. </span><strong style="border: 0px; box-sizing: border-box; font-family: verdana, sans-serif; font-stretch: inherit; font-style: inherit; font-variant: inherit; line-height: inherit; margin: 0px; outline: 0px; padding: 0px; vertical-align: baseline;">LocalDB</strong><span style="background-color: white; font-family: "verdana" , sans-serif;"> installation copies a minimal set of files necessary to start the SQL Server Database Engine. Once </span><strong style="border: 0px; box-sizing: border-box; font-family: verdana, sans-serif; font-stretch: inherit; font-style: inherit; font-variant: inherit; line-height: inherit; margin: 0px; outline: 0px; padding: 0px; vertical-align: baseline;">LocalDB</strong><span style="background-color: white; font-family: "verdana" , sans-serif;"> is installed, developers initiate a connection by using a special connection string. When connecting, the necessary SQL Server infrastructure is automatically created and started, enabling the application to use the database without complex or time consuming configuration tasks. Developer Tools can provide developers with a SQL Server Database Engine that lets them write and test Transact-SQL code without having to manage a full server instance of SQL Server. An instance of SQL Server Express </span><strong style="border: 0px; box-sizing: border-box; font-family: verdana, sans-serif; font-stretch: inherit; font-style: inherit; font-variant: inherit; line-height: inherit; margin: 0px; outline: 0px; padding: 0px; vertical-align: baseline;">LocalDB</strong><span style="background-color: white; font-family: "verdana" , sans-serif;"> is managed by using the</span><span style="border: 0px; box-sizing: border-box; font-family: "verdana" , sans-serif; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; outline: 0px; padding: 0px; vertical-align: baseline;">SqlLocalDB.exe</span><span style="background-color: white; font-family: "verdana" , sans-serif;"> utility. SQL Server Express </span><strong style="border: 0px; box-sizing: border-box; font-family: verdana, sans-serif; font-stretch: inherit; font-style: inherit; font-variant: inherit; line-height: inherit; margin: 0px; outline: 0px; padding: 0px; vertical-align: baseline;">LocalDB</strong><span style="background-color: white; font-family: "verdana" , sans-serif;"> should be used in place of the SQL Server Express user instance feature which is deprecated.</span></div>
<div style="border: 0px; box-sizing: border-box; font-stretch: inherit; line-height: 32px; margin-bottom: 32px; outline: 0px; padding: 0px; text-align: justify; vertical-align: baseline;">
<span style="background-color: white; font-family: "verdana" , sans-serif;">Reference : <a href="https://msdn.microsoft.com/en-us/library/hh510202.aspx?f=255&MSPPError=-2147217396" rel="nofollow" style="border-image-outset: initial; border-image-repeat: initial; border-image-slice: initial; border-image-source: initial; border-image-width: initial; border: 0px; box-sizing: border-box; font-stretch: inherit; margin: 0px; outline: none; padding: 0px; text-decoration: none; vertical-align: baseline;" target="_blank">https://msdn.microsoft.com/en-us/library/hh510202.aspx?f=255&MSPPError=-2147217396</a></span></div>
</div>
Musab Umairhttp://www.blogger.com/profile/00709484447531505692noreply@blogger.com0tag:blogger.com,1999:blog-1895280144594598079.post-83227261329469207752015-06-29T14:44:00.001+05:002015-06-29T14:55:11.927+05:00Generate Publication Stored Procedure in SQL Server 2014<div dir="ltr" style="text-align: left;" trbidi="on">
<span style="font-family: Verdana, sans-serif;">In Transaction Replication at times the Subscription Stored Procedures (inset, update or deleted) get out of sync with Publication. </span><br />
<span style="font-family: Verdana, sans-serif;">To get around that problem you need to generate the script of Publication Stored Procedure. </span><br />
<span style="font-family: Verdana, sans-serif;">And execute (alter) them on Subscription Databases. </span><br />
<span style="font-family: Verdana, sans-serif;"><br /></span>
<span style="font-family: Verdana, sans-serif;"><br /></span>
<span style="font-family: Verdana, sans-serif;">First You need to update the output limit of you SQL Server Management Studio. </span><br />
<span style="font-family: Verdana, sans-serif;">As follow : </span><br />
<span style="font-family: Verdana, sans-serif;"><br /></span>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh-H04HqgqtOGBm9EjGEf3dLgpmmyBpdde-gOv57oNxXyXXX5w7lO8_rUzvCBom5I7iZx2th0cuPe1dkLlR4eMw_RwKBpHuvi3HRFiTRKS3H5cM5WM9qvUKSlMlELE-aKPsrQbEo2OqcEQ/s1600/SQL_Server_Query_Results_Limit.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="185" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh-H04HqgqtOGBm9EjGEf3dLgpmmyBpdde-gOv57oNxXyXXX5w7lO8_rUzvCBom5I7iZx2th0cuPe1dkLlR4eMw_RwKBpHuvi3HRFiTRKS3H5cM5WM9qvUKSlMlELE-aKPsrQbEo2OqcEQ/s320/SQL_Server_Query_Results_Limit.png" width="320" /></a></div>
<span style="font-family: Verdana, sans-serif;"><br /></span>
<span style="font-family: Verdana, sans-serif;"><br /></span>
<span style="font-family: Verdana, sans-serif;">Secondly , Change the output to Text as follow : </span><br />
<span style="font-family: Verdana, sans-serif;"><br /></span>
<span style="font-family: Verdana, sans-serif;"><br /></span>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiKk_TcK8kT7uQgrqI042awmV53PMBqkH2h9RGGTzEoS7JT7cVg-JlevmIeABLkJRQH6wKW1yeWWB8yIIh6wfBhaU6IzmQCAfz60f4aPjzvuvhTTDUXa4B6TvgxuGp9qpu8acMyw2pxGbE/s1600/Result_to_Text_.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="61" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiKk_TcK8kT7uQgrqI042awmV53PMBqkH2h9RGGTzEoS7JT7cVg-JlevmIeABLkJRQH6wKW1yeWWB8yIIh6wfBhaU6IzmQCAfz60f4aPjzvuvhTTDUXa4B6TvgxuGp9qpu8acMyw2pxGbE/s320/Result_to_Text_.png" width="320" /></a></div>
<span style="font-family: Verdana, sans-serif;"><br /></span>
<span style="font-family: Verdana, sans-serif;"><br /></span>
<span style="font-family: Verdana, sans-serif;">Then Execute the stored procedure on your published databases and copy the output and execute it on your subscriber database.</span><br />
<span style="font-family: Verdana, sans-serif;"><br /></span>
<span style="font-family: Verdana, sans-serif;"><br /></span>
<span style="font-family: Verdana, sans-serif;"><i>use PublicationDB</i></span><br />
<span style="font-family: Verdana, sans-serif;"><i>GO</i></span><br />
<i><br /></i>
<span style="font-family: Verdana, sans-serif;"><i>EXEC sp_scriptpublicationcustomprocs 'PublicationName'</i></span><br />
<span style="font-family: Verdana, sans-serif;"><br /></span>
<br />
<span style="font-family: Verdana, sans-serif;"><br /></span>
<span style="font-family: Verdana, sans-serif;">There is also a utility by Code Project : <a href="http://www.codeproject.com/Tips/771755/Stored-Procedure-Generator-For-SQL-Server" target="_blank">here</a></span><br />
<br />
<br />
<br /></div>
Musab Umairhttp://www.blogger.com/profile/00709484447531505692noreply@blogger.com0tag:blogger.com,1999:blog-1895280144594598079.post-23143238800044446062013-10-28T14:02:00.002+05:002013-10-28T14:02:28.654+05:00Get current running Job Schedule Id or Name in SQL Server <div dir="ltr" style="text-align: left;" trbidi="on">
<span style="font-family: Verdana, sans-serif;"><br /></span>
<span style="font-family: Verdana, sans-serif;">Here is a simple TSQL code that gives you the Schedule Name & ID which invoked he current job execution. </span><br />
<span style="font-family: Verdana, sans-serif;"><br /></span>
<span style="font-family: Verdana, sans-serif;"><br /></span>
<span style="font-family: Verdana, sans-serif;"><br /></span>
<span style="font-family: Verdana, sans-serif;">DECLARE @Schedule_Name_ID nVARCHAR(1000)</span><br />
<span style="font-family: Verdana, sans-serif;">DECLARE @Job_ID nVARCHAR(100)</span><br />
<span style="font-family: Verdana, sans-serif;">DECLARE @State<span class="Apple-tab-span" style="white-space: pre;"> </span>INT</span><br />
<span style="font-family: Verdana, sans-serif;">DECLARE @Job_Name nVARCHAR(1000)</span><br />
<span style="font-family: Verdana, sans-serif;"><br /></span>
<span style="font-family: Verdana, sans-serif;">SET<span class="Apple-tab-span" style="white-space: pre;"> </span>@Job_Name = 'Your Job Name Goes Here'</span><br />
<span style="font-family: Verdana, sans-serif;"><br /></span>
<span style="font-family: Verdana, sans-serif;">SELECT<span class="Apple-tab-span" style="white-space: pre;"> </span>@Job_ID = job_id</span><br />
<span style="font-family: Verdana, sans-serif;">FROM<span class="Apple-tab-span" style="white-space: pre;"> </span>msdb.dbo.SysJobs</span><br />
<span style="font-family: Verdana, sans-serif;">WHERE<span class="Apple-tab-span" style="white-space: pre;"> </span>name = @Job_Name</span><br />
<span style="font-family: Verdana, sans-serif;"><br /></span>
<span style="font-family: Verdana, sans-serif;">IF(@Job_ID IS NOT NULL)</span><br />
<span style="font-family: Verdana, sans-serif;">BEGIN</span><br />
<span style="font-family: Verdana, sans-serif;"><br /></span>
<span style="font-family: Verdana, sans-serif;"><br /></span>
<span style="font-family: Verdana, sans-serif;"><span class="Apple-tab-span" style="white-space: pre;"> </span>CREATE TABLE #job_current_state</span><br />
<span style="font-family: Verdana, sans-serif;"><span class="Apple-tab-span" style="white-space: pre;"> </span>(</span><br />
<span style="font-family: Verdana, sans-serif;"><span class="Apple-tab-span" style="white-space: pre;"> </span> Job_ID UNIQUEIDENTIFIER,</span><br />
<span style="font-family: Verdana, sans-serif;"><span class="Apple-tab-span" style="white-space: pre;"> </span> Last_Run_Date INT,</span><br />
<span style="font-family: Verdana, sans-serif;"><span class="Apple-tab-span" style="white-space: pre;"> </span> Last_Run_Time INT,</span><br />
<span style="font-family: Verdana, sans-serif;"><span class="Apple-tab-span" style="white-space: pre;"> </span> Next_Run_Date INT,</span><br />
<span style="font-family: Verdana, sans-serif;"><span class="Apple-tab-span" style="white-space: pre;"> </span> Next_Run_Time INT,</span><br />
<span style="font-family: Verdana, sans-serif;"><span class="Apple-tab-span" style="white-space: pre;"> </span> Next_Run_Schedule_ID INT,</span><br />
<span style="font-family: Verdana, sans-serif;"><span class="Apple-tab-span" style="white-space: pre;"> </span> Requested_To_Run INT,</span><br />
<span style="font-family: Verdana, sans-serif;"><span class="Apple-tab-span" style="white-space: pre;"> </span> Request_Source INT,</span><br />
<span style="font-family: Verdana, sans-serif;"><span class="Apple-tab-span" style="white-space: pre;"> </span> Request_Source_ID VARCHAR(100),</span><br />
<span style="font-family: Verdana, sans-serif;"><span class="Apple-tab-span" style="white-space: pre;"> </span> Running INT,</span><br />
<span style="font-family: Verdana, sans-serif;"><span class="Apple-tab-span" style="white-space: pre;"> </span> Current_Step INT,</span><br />
<span style="font-family: Verdana, sans-serif;"><span class="Apple-tab-span" style="white-space: pre;"> </span> Current_Retry_Attempt INT,</span><br />
<span style="font-family: Verdana, sans-serif;"><span class="Apple-tab-span" style="white-space: pre;"> </span> State INT</span><br />
<span style="font-family: Verdana, sans-serif;"><span class="Apple-tab-span" style="white-space: pre;"> </span>)</span><br />
<span style="font-family: Verdana, sans-serif;"><span class="Apple-tab-span" style="white-space: pre;"> </span>INSERT INTO #job_current_state</span><br />
<span style="font-family: Verdana, sans-serif;"><span class="Apple-tab-span" style="white-space: pre;"> </span>EXEC master.dbo.xp_sqlagent_enum_jobs 1, garbage</span><br />
<span style="font-family: Verdana, sans-serif;"><span class="Apple-tab-span" style="white-space: pre;"> </span> </span><br />
<span style="font-family: Verdana, sans-serif;"><span class="Apple-tab-span" style="white-space: pre;"> </span>SELECT @Schedule_Name_ID = Request_Source_ID<span class="Apple-tab-span" style="white-space: pre;"> </span>,@State = State</span><br />
<span style="font-family: Verdana, sans-serif;"><span class="Apple-tab-span" style="white-space: pre;"> </span>FROM #job_current_state</span><br />
<span style="font-family: Verdana, sans-serif;"><span class="Apple-tab-span" style="white-space: pre;"> </span>where<span class="Apple-tab-span" style="white-space: pre;"> </span>job_id = @Job_ID</span><br />
<span style="font-family: Verdana, sans-serif;"><br /></span>
<span style="font-family: Verdana, sans-serif;"><span class="Apple-tab-span" style="white-space: pre;"> </span>SELECT<span class="Apple-tab-span" style="white-space: pre;"> </span>ISNULL(@Schedule_Name_ID,'NA') Schedule_Name_ID</span><br />
<span style="font-family: Verdana, sans-serif;"><span class="Apple-tab-span" style="white-space: pre;"> </span>,CASE @State WHEN 1 THEN 'Running' ELSE 'Stopped' END State</span><br />
<span style="font-family: Verdana, sans-serif;"><br /></span>
<span style="font-family: Verdana, sans-serif;"><span class="Apple-tab-span" style="white-space: pre;"> </span>DROP TABLE #job_current_state</span><br />
<span style="font-family: Verdana, sans-serif;"><br /></span>
<span style="font-family: Verdana, sans-serif;">END</span><br />
<span style="font-family: Verdana, sans-serif;"><br /></span>
<span style="font-family: Verdana, sans-serif;">ELSE</span><br />
<span style="font-family: Verdana, sans-serif;"><br /></span>
<span style="font-family: Verdana, sans-serif;">BEGIN</span><br />
<span style="font-family: Verdana, sans-serif;"><br /></span>
<span style="font-family: Verdana, sans-serif;"><span class="Apple-tab-span" style="white-space: pre;"> </span>SELECT<span class="Apple-tab-span" style="white-space: pre;"> </span>'Job does not exist' output</span><br />
<span style="font-family: Verdana, sans-serif;"><br /></span>
<span style="font-family: Verdana, sans-serif;">END </span></div>
Musab Umairhttp://www.blogger.com/profile/00709484447531505692noreply@blogger.com0tag:blogger.com,1999:blog-1895280144594598079.post-2696715201926063232012-07-05T10:41:00.000+05:002012-07-05T10:41:53.316+05:00Execute a command for a batch in Windows 2008 Server<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
<h1 class="title" style="color: #2a2a2a; font-family: 'Segoe UI', Verdana, Arial; font-size: 20px; font-weight: normal; margin: 0px 0px 20px;">
ForFiles</h1>
<div>
This is a command in windows 20008 for applying specified command on each file for a batch. </div>
<div>
For example if want to delete files older than a specified time period then this command will help me accomplishing this task. </div>
<div>
This is a very useful and easy to understand command. </div>
<div>
Command for an example is given below : </div>
<div>
<br /></div>
<div>
The mentioned below command will delete files older than 15 days in the folder "D:\YOURFOLDERNAME" and any sub directories in it. </div>
<div>
<br /></div>
<div>
<br /></div>
<div>
forfiles /p d:\yourfoldername /s /m *.txt /c "cmd /c del @file" /d -15</div>
<div>
<br /></div>
<div>
<br /></div>
<div>
forfiles --command name</div>
<div>
/p -- specifies that next argument is path </div>
<div>
/s -- specifies that sub directories should be scanned</div>
<div>
/m -- file type or search criteria </div>
<div>
/c -- actual command which will be executed for each file</div>
<div>
"cmd /c" and "@file" are default values for the argument /c</div>
<div>
"cmd /c yourcommand @file"</div>
<div>
/d --specifies number of days </div>
<div>
<br /></div>
<div>
<br /></div>
<div>
Further details can referenced on mentioned below technet link. </div>
<div>
<a href="http://technet.microsoft.com/en-us/library/cc753551(v=ws.10)">http://technet.microsoft.com/en-us/library/cc753551(v=ws.10)</a>
</div>
<div>
<br /></div>
<div>
Thanks to <a href="http://technet.microsoft.com/">TechNet</a>.</div>
<div>
<br /></div>
<div>
<br /></div>
<div>
<br /></div>
<div>
<br /></div>
<div>
<br /></div>
</div>Musab Umairhttp://www.blogger.com/profile/00709484447531505692noreply@blogger.com0tag:blogger.com,1999:blog-1895280144594598079.post-71460366274217903242012-06-11T10:24:00.000+05:002012-06-11T10:24:22.640+05:00Atlantis Schema Inspector - by "Atlantis Interactive"<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
<div style="text-align: justify;">
<span style="font-family: Verdana, sans-serif;">Another handy tool to compare schema in SQL Server.</span></div>
<div style="text-align: justify;">
<span style="font-family: Verdana, sans-serif;"><br /></span></div>
<div style="text-align: justify;">
<span style="font-family: Verdana, sans-serif;">SQL Server Schema Comparison and Scripting Tool.</span></div>
<div style="text-align: justify;">
<span style="font-family: Verdana, sans-serif;"><br /></span></div>
<div style="text-align: justify;">
<span style="font-family: Verdana, sans-serif;">Atlantis Schema Inspector is an incredibly fast, flexible and complete (and free) SQL Server synchronization and SQL Script tool - use it to get your deployments right first time, every time.</span></div>
<div style="text-align: justify;">
<span style="font-family: Verdana, sans-serif;"><br /></span></div>
<div style="text-align: justify;">
<span style="font-family: Verdana, sans-serif;">Schema Inspector offers an easy and fast way to compare and synchronize schema elements of SQL Server databases whether live, test, snapshots or partial databases. Any changes can be checked speedily and easily without errors. It can save your development team lots of time and help you achieve your project delivery accurately and on time. Schema Inspector works in tandem with our Data Inspector and both combine with our SQL Everywhere to make a uniquely integrated “one stop” solution for all SQL developers and administrators alike.</span></div>
<div style="text-align: justify;">
<span style="font-family: Verdana, sans-serif;"><br /></span></div>
<div style="text-align: justify;">
<span style="font-family: Verdana, sans-serif;">You can confidently use Schema Inspector and Data Inspector to save hours of development time whether developing new SQL Server databases or migrating old systems over to new. The savings in development time will make a significant contribution to the ensuring your project will deliver solutions to your users on-time and on-budget. SQL Script can be a notoriously difficult way in which to perform SQL Server synchronization - Schema Inspector relieves the difficulties associated with your deployments.</span></div>
<div style="text-align: justify;">
<span style="font-family: Verdana, sans-serif;"><br /></span></div>
<div style="text-align: justify;">
<span style="font-family: Verdana, sans-serif;">Thanks to </span><span style="text-align: left;"><span style="font-family: Verdana, sans-serif;">Atlantis Interactive UK Ltd.</span></span></div>
<div style="text-align: justify;">
<span style="text-align: left;"><span style="font-family: Verdana, sans-serif;">Download from the link mentioned below : -</span></span></div>
<div style="text-align: justify;">
<span style="text-align: left;"><span style="font-family: Verdana, sans-serif;"><a href="http://www.atlantis-interactive.co.uk/products/schemainspector/default.aspx">http://www.atlantis-interactive.co.uk/products/schemainspector/default.aspx</a></span></span></div>
<div style="text-align: justify;">
<span style="text-align: left;"><span style="font-family: Verdana, sans-serif;"><br /></span></span></div>
<div style="text-align: justify;">
<span style="text-align: left;"><span style="font-family: Verdana, sans-serif;">Please consider donating them. </span></span></div>
<div style="text-align: left;">
<span style="font-family: Verdana, sans-serif;"><br /></span></div>
</div>Musab Umairhttp://www.blogger.com/profile/00709484447531505692noreply@blogger.com0tag:blogger.com,1999:blog-1895280144594598079.post-23662735872535545412012-06-11T09:57:00.002+05:002012-06-11T09:57:57.253+05:00SQL Load Generator by CodePlex<div dir="ltr" style="text-align: left;" trbidi="on">
<div style="text-align: justify;">
<span style="font-family: Verdana, sans-serif;">A very handy tool to generate a good load, a must have tool. </span></div>
<div style="text-align: justify;">
<span style="font-family: Verdana, sans-serif;"><br /></span></div>
<div style="text-align: justify;">
<span style="font-family: Verdana, sans-serif;">SQL Load Generator is used to run multiple concurrent queries against SQL Server. The user can choose the number of concurrent queries to run, provide different queries, choose SQL or domain accounts, and provide application name settings. SLG was developed using C# 3.5.</span></div>
<div style="text-align: justify;">
<span style="font-family: Verdana, sans-serif;"><br /></span></div>
<div style="text-align: justify;">
<span style="font-family: Verdana, sans-serif;">Here’s a summary of the features:</span></div>
<div style="text-align: justify;">
<span style="font-family: Verdana, sans-serif;"><br /></span></div>
<br />
<div style="text-align: justify;">
<span style="font-family: Verdana, sans-serif;">Runs multiple queries against SQL Server. You can add as many as you like.</span></div>
<div style="text-align: justify;">
<span style="font-family: Verdana, sans-serif;">Each query can be either a SQL User or Domain User.</span></div>
<div style="text-align: justify;">
<span style="font-family: Verdana, sans-serif;">You can specify an Application name for the connection.</span></div>
<div style="text-align: justify;">
<span style="font-family: Verdana, sans-serif;">You can specify the new of concurrent threads to use for each query.</span></div>
<div style="text-align: justify;">
<span style="font-family: Verdana, sans-serif;">You can start all queries, stop all queries, remove all queries.</span></div>
<div style="text-align: justify;">
<span style="font-family: Verdana, sans-serif;">There is logging (you can toggle on and off… it isn’t precisely thread safe, and can cause crashes when there are lots of failures on multiple threads) for failed queries.</span></div>
<div style="text-align: justify;">
<span style="font-family: Verdana, sans-serif;">You can set all the defaults on a per user basis, and persist them.</span></div>
<div style="text-align: justify;">
<span style="font-family: Verdana, sans-serif;">Each query has a # of Runs and a # of Fails counter. You can use the ‘Reset Counters’ feature to reset the total counts (not the per query counts).</span></div>
<div style="text-align: justify;">
<span style="font-family: Verdana, sans-serif;"><br /></span></div>
<div style="text-align: justify;">
<span style="font-family: Verdana, sans-serif;">You can save your settings via the ‘Options’ menu. You can add default items to the different dropdowns, provide default query settings, change the log locations, etc. You can also modify the stock connection string… though keep in mind some of the settings (particularly ‘pooling=false’ will affect the way the application works… namely, the connections to SQL Server won’t be closed). </span></div>
<div style="text-align: justify;">
<span style="font-family: Verdana, sans-serif;"><br /></span></div>
<div style="text-align: justify;">
<span style="font-family: Verdana, sans-serif;"><br /></span></div>
<br />
<div style="text-align: justify;">
<span style="font-family: Verdana, sans-serif;">Download from the link mentioned below : </span></div>
<div style="text-align: justify;">
<span style="font-family: Verdana, sans-serif;"><a href="http://sqlloadgenerator.codeplex.com/">http://sqlloadgenerator.codeplex.com/</a></span></div>
<div style="text-align: justify;">
<span style="font-family: Verdana, sans-serif;"><br /></span></div>
<div style="text-align: justify;">
<span style="font-family: Verdana, sans-serif;"><br /></span></div>
<div style="text-align: justify;">
<span style="font-family: Verdana, sans-serif;"><br /></span></div>
<br />
<div style="text-align: justify;">
<span style="font-family: Verdana, sans-serif;">Thanks to Pinal Dev who tweeted this. </span></div>
<div style="text-align: justify;">
<span style="font-family: Verdana, sans-serif;"><a href="http://blog.sqlauthority.com/">http://blog.sqlauthority.com</a></span></div>
<div style="text-align: justify;">
<span style="font-family: Verdana, sans-serif;"><a href="http://sqlloadgenerator.codeplex.com/">http://sqlloadgenerator.codeplex.com/</a></span></div>
<div style="text-align: justify;">
<span style="font-family: Verdana, sans-serif;"><br /></span></div>
<div style="text-align: justify;">
<span style="font-family: Verdana, sans-serif;"><br /></span></div>
<div style="text-align: justify;">
<br /></div>
</div>Musab Umairhttp://www.blogger.com/profile/00709484447531505692noreply@blogger.com0tag:blogger.com,1999:blog-1895280144594598079.post-21560078308144393012011-11-23T10:51:00.001+05:002011-11-23T10:55:19.150+05:00Calculate difference of Time only between two dates while discarding difference of dates in SQL Server/ extract time and calculate difference between two dates in SQL Server<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
DECLARE @DateTimeOne DATETIME<br />
DECLARE @DateTimeTwo DATETIME<br />
<br />
SET<span class="Apple-tab-span" style="white-space: pre;"> </span>@DateTimeOne = '1900-01-01 10:41:09.250'<br />
SET<span class="Apple-tab-span" style="white-space: pre;"> </span>@DateTimeTwo = GETDATE()<br />
<br />
<br />
--hour<span class="Apple-tab-span" style="white-space: pre;"> </span>=<span class="Apple-tab-span" style="white-space: pre;"> </span>hh<br />
--minute<span class="Apple-tab-span" style="white-space: pre;"> </span>=<span class="Apple-tab-span" style="white-space: pre;"> </span>mi, n<br />
--second<span class="Apple-tab-span" style="white-space: pre;"> </span>=<span class="Apple-tab-span" style="white-space: pre;"> </span>ss, s<br />
--millisecond<span class="Apple-tab-span" style="white-space: pre;"> </span>=<span class="Apple-tab-span" style="white-space: pre;"> </span>ms<br />
<br />
<br />
SELECT DATEDIFF(mi,SUBSTRING(CONVERT(VARCHAR,@DateTimeOne, 121), 12, 12)<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>,SUBSTRING(CONVERT(VARCHAR,@DateTimeTwo, 121), 12, 12) ) MinuteDifferenceinDate</div>Musab Umairhttp://www.blogger.com/profile/00709484447531505692noreply@blogger.com0tag:blogger.com,1999:blog-1895280144594598079.post-386107550049031962011-06-24T17:25:00.002+05:002011-06-25T08:36:00.022+05:00Working with Email Address in SQL Server ( Validate Email Address )<div dir="ltr" style="text-align: left;" trbidi="on">at times working with email addresses in sql server becomes difficult so<br />
here is a UDF you can use to validate the email address<br />
<br />
It will validate mentioned below list<br />
<br />
No Spaces are allowed<br />
email cant start with '@'<br />
email cant end at '.'<br />
@must be in email and only once<br />
domain name should be at the end and must be at least two characters<br />
email cant habe patterns like '..' or '.@'<br />
<div><br />
</div><div><br />
</div><div><br />
</div><div><br />
</div><div><br />
</div><div><br />
</div><br />
<br />
CREATE FUNCTION [dbo].[validateEmailAddress]<br />
(<br />
@EmailAddress nVARCHAR(4000)<br />
)<br />
RETURNS TINYINT AS<br />
BEGIN<br />
<br />
<br />
DECLARE<span class="Apple-tab-span" style="white-space: pre;"> </span>@Result TINYINT<br />
SELECT<span class="Apple-tab-span" style="white-space: pre;"> </span>@Result =<br />
CASE WHEN<br />
CHARINDEX(' ',LTRIM(RTRIM(@EmailAddress))) = 0<br />
AND <span class="Apple-tab-span" style="white-space: pre;"> </span>LEFT(LTRIM(@EmailAddress),1) <> '@'<br />
AND <span class="Apple-tab-span" style="white-space: pre;"> </span>RIGHT(RTRIM(@EmailAddress),1) <> '.'<br />
AND <span class="Apple-tab-span" style="white-space: pre;"> </span>CHARINDEX('.',@EmailAddress,CHARINDEX('@',@EmailAddress)) - CHARINDEX('@',@EmailAddress) > 1<br />
AND <span class="Apple-tab-span" style="white-space: pre;"> </span>LEN(LTRIM(RTRIM(@EmailAddress))) - LEN(REPLACE(LTRIM(RTRIM(@EmailAddress)),'@','')) = 1<br />
AND <span class="Apple-tab-span" style="white-space: pre;"> </span>CHARINDEX('.',REVERSE(LTRIM(RTRIM(@EmailAddress)))) >= 3<br />
AND <span class="Apple-tab-span" style="white-space: pre;"> </span>(CHARINDEX('.@',@EmailAddress) = 0 AND CHARINDEX('..',@EmailAddress) = 0)<br />
THEN 1 ELSE 0 END<br />
<br />
RETURN @Result<br />
<br />
END<br />
<div><br />
</div><div><br />
</div><div><br />
</div><div>Example:- </div><div><br />
</div><div>it will return 1 for correct email address and 0 for incorrect </div><div>:</div><div>SELECT dbo.validateEmailAddress('myemail@mydomain.com') -- Correct</div><div><br />
</div><div>SELECT dbo.validateEmailAddress('@myemail@m domain.com') -- Incorrect </div><div><br />
</div><div><br />
</div></div>Musab Umairhttp://www.blogger.com/profile/00709484447531505692noreply@blogger.com0tag:blogger.com,1999:blog-1895280144594598079.post-85160654569250253032011-01-11T15:29:00.000+05:002011-01-11T15:29:36.813+05:00Best Practices for using DATETIME column in sql server 2005 and 2008<div class="MsoNormal"><span class="Apple-style-span" style="font-family: inherit;"><b>Best Practices for using DATETIME column in sql server 2005 and 2008</b><o:p></o:p></span></div><div class="MsoNormal"><span class="Apple-style-span" style="font-family: inherit;"><br />
</span></div><div class="MsoNormal"><span class="Apple-style-span" style="font-family: inherit;">In production environment we have seen that there are certain times when you make a good query by applying best practices but you don’t get good results at the time of sorting.<o:p></o:p></span></div><div class="MsoNormal"><span class="Apple-style-span" style="font-family: inherit;">This might happen because of DATETIME columns which are mostly used for sorting purpose and indexes for these columns do not give you appropriate results. <o:p></o:p></span></div><div class="MsoNormal"><span class="Apple-style-span" style="font-family: inherit;">Mentioned below is tip for using DATETIME column which can enhance the speed of your query while maintaining same number of indexes.<o:p></o:p></span></div><div class="MsoNormal"><span class="Apple-style-span" style="font-family: inherit;">This technique requires mentioned below changes in your table: - </span></div><div class="MsoNormal"></div><ol><li>One Persisted Computed Column (BIGINT) which will store DATETIME in integer format by removing special symbols from DATETIME</li>
<li>This computed column will be added in the covering index</li>
<li>Query will use this column instead of DATETIME column</li>
</ol><span class="Apple-style-span" style="font-family: inherit;"></span><br />
<span class="Apple-style-span" style="font-family: inherit;"><span style="line-height: 115%;"><br clear="all" style="mso-special-character: line-break; page-break-before: always;" /> </span> </span><div class="MsoNormal"><br />
</div><div class="MsoNormal"><span class="Apple-style-span" style="font-family: inherit;">Example : <o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span style="color: green;"><span class="Apple-style-span" style="font-family: inherit;">/* Create Sample Table */<o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span class="Apple-style-span" style="font-family: inherit;"><span style="color: blue;">CREATE</span> <span style="color: blue;">TABLE</span> [dbo]<span style="color: grey;">.</span>[MyTable]<span style="color: grey;">(<o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span class="Apple-style-span" style="font-family: inherit;"> [C1] [int] <span style="color: blue;">IDENTITY</span><span style="color: grey;">(</span>1<span style="color: grey;">,</span>1<span style="color: grey;">)</span> <span style="color: grey;">NOT</span> <span style="color: grey;">NULL,<o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span class="Apple-style-span" style="font-family: inherit;"> [C2] [DATETIME] <span style="color: grey;">NULL,<o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span class="Apple-style-span" style="font-family: inherit;"> <span style="color: blue;">CONSTRAINT</span> [PK_MyTable] <span style="color: blue;">PRIMARY</span> <span style="color: blue;">KEY</span> <span style="color: blue;">CLUSTERED</span> <o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span style="color: grey;"><span class="Apple-style-span" style="font-family: inherit;">(<o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span class="Apple-style-span" style="font-family: inherit;"> [C1] <span style="color: blue;">ASC<o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span class="Apple-style-span" style="font-family: inherit;"><span style="color: grey;">)</span><span style="color: blue;">WITH </span><span style="color: grey;">(</span><span style="color: blue;">PAD_INDEX</span> <span style="color: grey;">=</span> <span style="color: blue;">OFF</span><span style="color: grey;">,</span> <span style="color: blue;">STATISTICS_NORECOMPUTE</span> <span style="color: grey;">=</span> <span style="color: blue;">OFF</span><span style="color: grey;">,</span> <span style="color: blue;">IGNORE_DUP_KEY</span> <span style="color: grey;">=</span> <span style="color: blue;">OFF</span><span style="color: grey;">,</span> <span style="color: blue;">ALLOW_ROW_LOCKS</span> <span style="color: grey;">=</span> <span style="color: blue;">ON</span><span style="color: grey;">,</span> <span style="color: blue;">ALLOW_PAGE_LOCKS</span> <span style="color: grey;">=</span> <span style="color: blue;">ON</span><span style="color: grey;">)</span> <span style="color: blue;">ON</span> [PRIMARY]<o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span class="Apple-style-span" style="font-family: inherit;"><span style="color: grey;">)</span> <span style="color: blue;">ON</span> [PRIMARY]<o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span style="color: blue;"><span class="Apple-style-span" style="font-family: inherit;">GO<o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><br />
</div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span style="color: green;"><span class="Apple-style-span" style="font-family: inherit;">/* Data Insert */<o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span class="Apple-style-span" style="font-family: inherit;"><span style="color: blue;">SET</span> <span style="color: blue;">IDENTITY_INSERT</span> [dbo]<span style="color: grey;">.</span>[MyTable] <span style="color: blue;">ON<o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span class="Apple-style-span" style="font-family: inherit;"><span style="color: blue;">INSERT</span> [dbo]<span style="color: grey;">.</span>[MyTable]<span style="color: blue;"> </span><span style="color: grey;">(</span>[C1]<span style="color: grey;">,</span> [C2]<span style="color: grey;">)</span> <span style="color: blue;">VALUES </span><span style="color: grey;">(</span>1<span style="color: grey;">,</span> <span style="color: magenta;">CAST</span><span style="color: grey;">(</span>0x00009E5E00000000 <span style="color: blue;">AS</span> <span style="color: blue;">DATETIME</span><span style="color: grey;">))<o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span class="Apple-style-span" style="font-family: inherit;"><span style="color: blue;">INSERT</span> [dbo]<span style="color: grey;">.</span>[MyTable]<span style="color: blue;"> </span><span style="color: grey;">(</span>[C1]<span style="color: grey;">,</span> [C2]<span style="color: grey;">)</span> <span style="color: blue;">VALUES </span><span style="color: grey;">(</span>2<span style="color: grey;">,</span> <span style="color: magenta;">CAST</span><span style="color: grey;">(</span>0x00009E5F00000000 <span style="color: blue;">AS</span> <span style="color: blue;">DATETIME</span><span style="color: grey;">))<o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span class="Apple-style-span" style="font-family: inherit;"><span style="color: blue;">INSERT</span> [dbo]<span style="color: grey;">.</span>[MyTable]<span style="color: blue;"> </span><span style="color: grey;">(</span>[C1]<span style="color: grey;">,</span> [C2]<span style="color: grey;">)</span> <span style="color: blue;">VALUES </span><span style="color: grey;">(</span>3<span style="color: grey;">,</span> <span style="color: magenta;">CAST</span><span style="color: grey;">(</span>0x00009E6000000000 <span style="color: blue;">AS</span> <span style="color: blue;">DATETIME</span><span style="color: grey;">))<o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span class="Apple-style-span" style="font-family: inherit;"><span style="color: blue;">INSERT</span> [dbo]<span style="color: grey;">.</span>[MyTable]<span style="color: blue;"> </span><span style="color: grey;">(</span>[C1]<span style="color: grey;">,</span> [C2]<span style="color: grey;">)</span> <span style="color: blue;">VALUES </span><span style="color: grey;">(</span>4<span style="color: grey;">,</span> <span style="color: magenta;">CAST</span><span style="color: grey;">(</span>0x00009E6100000000 <span style="color: blue;">AS</span> <span style="color: blue;">DATETIME</span><span style="color: grey;">))<o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span class="Apple-style-span" style="font-family: inherit;"><span style="color: blue;">INSERT</span> [dbo]<span style="color: grey;">.</span>[MyTable]<span style="color: blue;"> </span><span style="color: grey;">(</span>[C1]<span style="color: grey;">,</span> [C2]<span style="color: grey;">)</span> <span style="color: blue;">VALUES </span><span style="color: grey;">(</span>5<span style="color: grey;">,</span> <span style="color: magenta;">CAST</span><span style="color: grey;">(</span>0x00009E7D00000000 <span style="color: blue;">AS</span> <span style="color: blue;">DATETIME</span><span style="color: grey;">))<o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span class="Apple-style-span" style="font-family: inherit;"><span style="color: blue;">INSERT</span> [dbo]<span style="color: grey;">.</span>[MyTable]<span style="color: blue;"> </span><span style="color: grey;">(</span>[C1]<span style="color: grey;">,</span> [C2]<span style="color: grey;">)</span> <span style="color: blue;">VALUES </span><span style="color: grey;">(</span>6<span style="color: grey;">,</span> <span style="color: magenta;">CAST</span><span style="color: grey;">(</span>0x00009E7E00000000 <span style="color: blue;">AS</span> <span style="color: blue;">DATETIME</span><span style="color: grey;">))<o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span class="Apple-style-span" style="font-family: inherit;"><span style="color: blue;">SET</span> <span style="color: blue;">IDENTITY_INSERT</span> [dbo]<span style="color: grey;">.</span>[MyTable] <span style="color: blue;">OFF<o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><br />
</div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span style="color: green;"><span class="Apple-style-span" style="font-family: inherit;">/* Query */<o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span class="Apple-style-span" style="font-family: inherit;"><span style="color: blue;">DECLARE</span> @D1 <span style="color: blue;">DATETIME<o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span class="Apple-style-span" style="font-family: inherit;"><span style="color: blue;">DECLARE</span> @D2 <span style="color: blue;">DATETIME<o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span class="Apple-style-span" style="font-family: inherit;"><span style="color: blue;">SET</span> @D1 <span style="color: grey;">=</span> <span style="color: magenta;">GETDATE</span><span style="color: grey;">()-</span>10<o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span class="Apple-style-span" style="font-family: inherit;"><span style="color: blue;">SET</span> @D2 <span style="color: grey;">=</span> <span style="color: magenta;">GETDATE</span><span style="color: grey;">()<o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span class="Apple-style-span" style="font-family: inherit;"><span style="color: blue;">SELECT</span> C1<o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span class="Apple-style-span" style="font-family: inherit;"> <span style="color: grey;">,</span>C2<o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span class="Apple-style-span" style="font-family: inherit;"><span style="color: blue;">FROM</span> MyTable<o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span class="Apple-style-span" style="font-family: inherit;"><span style="color: blue;">WHERE</span> C2 <span style="color: grey;">BETWEEN</span> @D1 <span style="color: grey;">AND</span> @D2<o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><br />
</div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span style="color: green;"><span class="Apple-style-span" style="font-family: inherit;">/* Change in Table Add New Column that will store DATETIME in Integer Format */<o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span class="Apple-style-span" style="font-family: inherit;"><span style="color: blue;">ALTER</span> <span style="color: blue;">TABLE</span> MyTable<o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span class="Apple-style-span" style="font-family: inherit;"><span style="color: blue;">ADD</span> [C3] <span style="color: blue;">AS </span><span style="color: grey;">(</span><span style="color: magenta;">CONVERT</span><span style="color: grey;">(</span>[bigint]<span style="color: grey;">,</span><span style="color: magenta;">CONVERT</span><span style="color: grey;">(</span>[varchar]<span style="color: grey;">,</span>[C2]<span style="color: grey;">,(</span>112<span style="color: grey;">))+</span><span style="color: magenta;">replace</span><span style="color: grey;">(</span><span style="color: magenta;">CONVERT</span><span style="color: grey;">(</span>[varchar]<span style="color: grey;">,</span>[C2]<span style="color: grey;">,(</span>114<span style="color: grey;">)),</span><span style="color: red;">':'</span><span style="color: grey;">,</span><span style="color: red;">''</span><span style="color: grey;">),</span>0<span style="color: grey;">))</span> <span style="color: blue;">PERSISTED<o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><br />
</div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span style="color: green;"><span class="Apple-style-span" style="font-family: inherit;">/* Existing Index for query */<o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span class="Apple-style-span" style="font-family: inherit;"><span style="color: blue;">CREATE</span> <span style="color: blue;">NONCLUSTERED</span> <span style="color: blue;">INDEX</span> [Index_For_C2] <span style="color: blue;">ON</span> MyTable<o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span style="color: grey;"><span class="Apple-style-span" style="font-family: inherit;">(<o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span class="Apple-style-span" style="font-family: inherit;"> [C2] <span style="color: blue;">ASC</span><span style="color: grey;">,<o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span class="Apple-style-span" style="font-family: inherit;"> [C1] <span style="color: blue;">ASC<o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span class="Apple-style-span" style="font-family: inherit;"><span style="color: grey;">)</span><span style="color: blue;">WITH </span><span style="color: grey;">(</span><span style="color: blue;">PAD_INDEX</span> <span style="color: grey;">=</span> <span style="color: blue;">OFF</span><span style="color: grey;">,</span> <span style="color: blue;">STATISTICS_NORECOMPUTE</span> <span style="color: grey;">=</span> <span style="color: blue;">OFF</span><span style="color: grey;">,</span> <span style="color: blue;">SORT_IN_TEMPDB</span> <span style="color: grey;">=</span> <span style="color: blue;">OFF</span><span style="color: grey;">,<o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span class="Apple-style-span" style="font-family: inherit;"> <span style="color: blue;">IGNORE_DUP_KEY</span> <span style="color: grey;">=</span> <span style="color: blue;">OFF</span><span style="color: grey;">,</span> <span style="color: blue;">DROP_EXISTING</span> <span style="color: grey;">=</span> <span style="color: blue;">OFF</span><span style="color: grey;">,</span> <span style="color: blue;">ONLINE</span> <span style="color: grey;">=</span> <span style="color: blue;">OFF</span><span style="color: grey;">,</span> <span style="color: blue;">ALLOW_ROW_LOCKS</span> <span style="color: grey;">=</span> <span style="color: blue;">ON</span><span style="color: grey;">,</span> <span style="color: blue;">ALLOW_PAGE_LOCKS</span> <span style="color: grey;">=</span> <span style="color: blue;">ON</span><span style="color: grey;">)</span> <span style="color: blue;">ON</span> [PRIMARY]<o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span style="color: blue;"><span class="Apple-style-span" style="font-family: inherit;">GO<o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><br />
</div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span style="color: green;"><span class="Apple-style-span" style="font-family: inherit;">/* Add New Column to Existing Index for query */<o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span class="Apple-style-span" style="font-family: inherit;"><span style="color: blue;">CREATE</span> <span style="color: blue;">NONCLUSTERED</span> <span style="color: blue;">INDEX</span> [Index_For_C2] <span style="color: blue;">ON</span> MyTable<o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span style="color: grey;"><span class="Apple-style-span" style="font-family: inherit;">(<o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span class="Apple-style-span" style="font-family: inherit;"> [C3] <span style="color: blue;">ASC</span><span style="color: grey;">,<o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span class="Apple-style-span" style="font-family: inherit;"> [C2] <span style="color: blue;">ASC</span><span style="color: grey;">,<o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span class="Apple-style-span" style="font-family: inherit;"> [C1] <span style="color: blue;">ASC<o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span class="Apple-style-span" style="font-family: inherit;"><span style="color: grey;">)</span><span style="color: blue;">WITH </span><span style="color: grey;">(</span><span style="color: blue;">STATISTICS_NORECOMPUTE</span> <span style="color: grey;">=</span> <span style="color: blue;">OFF</span><span style="color: grey;">,</span> <span style="color: blue;">SORT_IN_TEMPDB</span> <span style="color: grey;">=</span> <span style="color: blue;">OFF</span><span style="color: grey;">,</span> <span style="color: blue;">IGNORE_DUP_KEY</span> <span style="color: grey;">=</span> <span style="color: blue;">OFF</span><span style="color: grey;">,</span> <o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span class="Apple-style-span" style="font-family: inherit;"><span style="color: blue;">DROP_EXISTING</span> <span style="color: grey;">=</span> <span style="color: blue;">ON</span><span style="color: grey;">,</span> <span style="color: blue;">ONLINE</span> <span style="color: grey;">=</span> <span style="color: blue;">OFF</span><span style="color: grey;">,</span> <span style="color: blue;">ALLOW_ROW_LOCKS</span> <span style="color: grey;">=</span> <span style="color: blue;">ON</span><span style="color: grey;">,</span> <span style="color: blue;">ALLOW_PAGE_LOCKS</span> <span style="color: grey;">=</span> <span style="color: blue;">ON</span><span style="color: grey;">)</span> <span style="color: blue;">ON</span> [PRIMARY]<o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span style="color: blue;"><span class="Apple-style-span" style="font-family: inherit;">GO<o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><br />
</div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span style="color: green;"><span class="Apple-style-span" style="font-family: inherit;">/*Updated Query using Integer Column instead of DATETIME having same result set*/<o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span class="Apple-style-span" style="font-family: inherit;"><span style="color: blue;">DECLARE</span> @D1 <span style="color: blue;">BIGINT<o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span class="Apple-style-span" style="font-family: inherit;"><span style="color: blue;">DECLARE</span> @D2 <span style="color: blue;">BIGINT<o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span class="Apple-style-span" style="font-family: inherit;"><span style="color: blue;">SET</span> @D1 <span style="color: grey;">=</span><span style="color: blue;"> </span><span style="color: grey;">(</span><span style="color: magenta;">CONVERT</span><span style="color: grey;">(</span>[bigint]<span style="color: grey;">,</span><span style="color: magenta;">CONVERT</span><span style="color: grey;">(</span>[varchar]<span style="color: grey;">,</span><span style="color: magenta;">GETDATE</span><span style="color: grey;">()-</span>10<span style="color: grey;">,(</span>112<span style="color: grey;">))+</span><span style="color: magenta;">replace</span><span style="color: grey;">(</span><span style="color: magenta;">CONVERT</span><span style="color: grey;">(</span>[varchar]<span style="color: grey;">,</span><span style="color: magenta;">GETDATE</span><span style="color: grey;">()-</span>10<span style="color: grey;">,(</span>114<span style="color: grey;">)),</span><span style="color: red;">':'</span><span style="color: grey;">,</span><span style="color: red;">''</span><span style="color: grey;">),</span>0<span style="color: grey;">))<o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span class="Apple-style-span" style="font-family: inherit;"><span style="color: blue;">SET</span> @D2 <span style="color: grey;">=</span><span style="color: blue;"> </span><span style="color: grey;">(</span><span style="color: magenta;">CONVERT</span><span style="color: grey;">(</span>[bigint]<span style="color: grey;">,</span><span style="color: magenta;">CONVERT</span><span style="color: grey;">(</span>[varchar]<span style="color: grey;">,</span><span style="color: magenta;">GETDATE</span><span style="color: grey;">(),(</span>112<span style="color: grey;">))+</span><span style="color: magenta;">replace</span><span style="color: grey;">(</span><span style="color: magenta;">CONVERT</span><span style="color: grey;">(</span>[varchar]<span style="color: grey;">,</span><span style="color: magenta;">GETDATE</span><span style="color: grey;">(),(</span>114<span style="color: grey;">)),</span><span style="color: red;">':'</span><span style="color: grey;">,</span><span style="color: red;">''</span><span style="color: grey;">),</span>0<span style="color: grey;">))<o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span class="Apple-style-span" style="font-family: inherit;"><span style="color: blue;">SELECT</span> C1<o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span class="Apple-style-span" style="font-family: inherit;"> <span style="color: grey;">,</span>C2<o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span class="Apple-style-span" style="font-family: inherit;"><span style="color: blue;">FROM</span> MyTable<o:p></o:p></span></div><div class="MsoNormal"><span class="Apple-style-span" style="font-family: inherit;"><span style="color: blue; line-height: 115%;">WHERE</span><span style="line-height: 115%;"> C3 <span style="color: grey;">BETWEEN</span> @D1 <span style="color: grey;">AND</span> @D2<o:p></o:p></span></span></div><div class="MsoNormal"><br />
</div><div class="MsoNormal"><br />
</div><div class="MsoNormal"><span class="Apple-style-span" style="font-family: inherit;">The above query will now use the new updated index and will be much faster as it will get records by using an integer column instead of DATETIME column. </span><o:p></o:p></div>Musab Umairhttp://www.blogger.com/profile/00709484447531505692noreply@blogger.com0tag:blogger.com,1999:blog-1895280144594598079.post-2897924899005904942010-11-03T10:33:00.006+05:002010-12-21T21:35:37.165+05:00SQL Server to MySQL Conversion using MySQL Migration Toolkit.<p>A simple and powerful tool for migrating data from SQL Server to MySQL.</p><p>It can import/export your data directly to database server and you can also generate Create and Insert scripts.</p><p>A useful tool by MySQL team.</p><p>You can download the tool by signing up on mentioned below site and download this tool<a name='more'></a></p> <a href="http://dev.mysql.com/downloads/gui-tools/5.0.html">http://dev.mysql.com/downloads/gui-tools/5.0.html</a><br /><br />mysql-gui-tools-5.0-r17-win32.msiMusab Umairhttp://www.blogger.com/profile/00709484447531505692noreply@blogger.com0tag:blogger.com,1999:blog-1895280144594598079.post-21261162141654399462009-12-28T01:11:00.006+05:002010-12-21T21:39:21.808+05:00winupdate86.exe is2010.exe Internet Security 2010 its a fake antivirus and how to remove it<div>Type gpedit.msc in RUN </div><div><br /></div><div>Goto=> User Configurations </div><div>Goto=> Administrative Templates</div><div>Goto=> System</div><div>Goto=> Ctrl+Alt+Del Options</div><div>Double Click Remove Task Bar Manager </div><div>Click on DISABLE</div><div>Press OK </div><div><br /></div><div>Your Task bar will be enables</div><div>now look for mentioned below exes and KILL process tree</div><div><a name='more'></a></div><div>winupdate86.exe </div><div>iso2010.exe</div><div><br /></div><div>now Type MSCONFIG in RUN</div><div>Goto=> Startup</div><div>Un check </div><div>Internet Security 2010 and winupdate86.exe </div><div><br /></div><div>now it will require a re-login ... preferably a restart and you will be free from this curse.</div>Musab Umairhttp://www.blogger.com/profile/00709484447531505692noreply@blogger.com1tag:blogger.com,1999:blog-1895280144594598079.post-40210547107745297402009-11-26T10:41:00.006+05:002010-12-21T21:40:33.516+05:00Export Microsoft SQL Server data to SQLITEAt time you need to interact with SQLITE and normally there are tools which are not free but i have found a tool which is very useful when you need to export data from Microsoft SQL Server to SQLITE.<br /><br />You need to have a login for the mentioned below site, signup and download the utility.<br /><br />This tool was made by Liron Levi.<br /><a name='more'></a><br />This useful tool and can be downloaded from the link given below:<br /><br /><a href="http://www.codeproject.com/KB/database/convsqlservertosqlite.aspx" target="_blank">http://www.codeproject.com/KB/database/convsqlservertosqlite.aspx</a>Musab Umairhttp://www.blogger.com/profile/00709484447531505692noreply@blogger.com0tag:blogger.com,1999:blog-1895280144594598079.post-39063339299552508322009-09-10T10:22:00.008+06:002010-12-21T21:41:03.122+05:00Import MYSQL Data to Microsoft SQL Server 2005<div>At times you my need to interact with mysql database and the method given below is very easy for Microsoft SQL Server guys to do stuff with mysql database.</div><div><br /></div><div>1) You need to have a mysql instance configured.</div><div>2) Install the MySQL ODBC Driver for Microsoft SQL Server</div><div>3) Add MySQL Instance as a linked server to Microsoft SQL Server by issuing the mentioned below command.</div><div>4) Access and Import you MySQL Data right from Microsoft SQL Server Management Studio<br /><a name='more'></a></div><div>Driver Link:</div><a href="http://dev.mysql.com/downloads/connector/odbc/5.1.html">http://dev.mysql.com/downloads/connector/odbc/5.1.html</a><div><div>Add MySQL as Linked Server:</div><div><br /></div><div>EXEC master.dbo.sp_addlinkedserver</div><div>@server = N'MYSQL',</div><div>@srvproduct = N'MySQL',</div><div>@provider = N'MSDASQL',</div><div>@provstr = N'DRIVER={MySQL ODBC 5.1 Driver};</div><div>SERVER = localhost;</div><div>DATABASE = datadb;</div><div>USER = root;</div><div>OPTION = 3'</div><div><br /></div><div>Issue an Import Statement</div><div>SELECT *</div><div>INTO TableName</div><div>FROM OPENQUERY(MySQL, 'SELECT * FROM datadb.TableName')</div><div><br /></div><div>Now you will have a table with name specified as "TableName" in you Microsoft SQL Server Database with Schema and Data from MySQL Database.</div></div>Musab Umairhttp://www.blogger.com/profile/00709484447531505692noreply@blogger.com0tag:blogger.com,1999:blog-1895280144594598079.post-47147515593352251522009-07-28T12:42:00.008+06:002010-12-21T21:41:38.399+05:00Claim Unused space from table after Deletion of rows of after droppping columns<div>If you deleted rows and space used is same as previous then first thing you need to do is Rebuild Clustered Index.</div><div>It will release most of the unused space.</div><div><br /></div><div>Secondly if you have dropped any variable length column then you can run the mentioned below command.</div><div>DBCC CLEANTABLE (AdventureWorks,"Production.Document", 0)</div><a name='more'></a><br /><a href="http://msdn.microsoft.com/en-us/library/ms174418.aspx">http://msdn.microsoft.com/en-us/library/ms174418.aspx</a>Musab Umairhttp://www.blogger.com/profile/00709484447531505692noreply@blogger.com0tag:blogger.com,1999:blog-1895280144594598079.post-36975606048896338422009-05-27T10:55:00.004+06:002010-12-21T21:42:22.163+05:00Search Suffix using Full Text Search in SQL Server 2005A solution to search Suffix is given below as its a limitation in FTS that FTS cannot search suffix.<br /><br />Its not recommended and not efficient as it requires another column but its a solution which can help you out in some cases.<br /><br /><br />1. Add a column in your table which stores the reverse of the string<br />like<br />SET NewColumnName = REVERSE(ColumnName)<br /><a name='more'></a><br />2. CREATE PROCEDURE sps_searchSuffix(@searchString varchar(8000)) AS<br /><br />SET @searchString = REVERSE(@searchString)<br /><br />DECLARE @Q nVARCHAR(MAX)<br />SET @Q = 'SELECT * FROM TableName WHERE CONTAINS (ColumnName,''"'+@searchString+'*"'''+')'<br />EXEC SP_EXECUTESQL @Q<br /><br /><br />3. And call it like this if you want to search "garding" and you have a data like "regarding"<br /><br />DECLARE @ST VARCHAR(500)<br />SET @ST = 'garding'<br />PRINT @ST<br />EXEC sps_searchSuffix @STMusab Umairhttp://www.blogger.com/profile/00709484447531505692noreply@blogger.com0tag:blogger.com,1999:blog-1895280144594598079.post-391333730061158382009-05-05T14:44:00.000+06:002009-05-05T14:45:18.080+06:00Identify missing indexes in sql server 2005SELECT DISTINCT DB_NAME(Database_ID) [Database]<br /> ,OBJECT_NAME(Object_ID) [Table]<br /> ,Equality_Columns<br /> ,Included_Columns <br />FROM sys.dm_db_missing_index_details mid<br />WHERE Database_ID = DB_ID()<br />ORDER BY 2Musab Umairhttp://www.blogger.com/profile/00709484447531505692noreply@blogger.com2tag:blogger.com,1999:blog-1895280144594598079.post-42942333334776905262009-04-06T13:01:00.001+05:002010-12-21T21:42:59.032+05:00Computed Columns in SQL Server 2005<p>A computed column is computed from an expression that can use other columns in the same table. The expression can be a noncomputed column name, constant, function, variable, and any combination of these connected by one or more operators. The expression cannot be a subquery. </p> <p>For example, in the <strong>AdventureWorks</strong> sample database, the <strong>TotalDue</strong> column of the <strong>Sales.SalesOrderHeader</strong> table has the definition: <strong>TotalDue</strong> AS <strong>Subtotal</strong> + <strong>TaxAmt</strong> + <strong>Freight</strong>.<br /></p><p><a name='more'></a></p><p>Unless otherwise specified, computed columns are virtual columns that are not physically stored in the table. Their values are recalculated every time they are referenced in a query. The SQL Server 2005 Database Engine uses the PERSISTED keyword in the CREATE TABLE and ALTER TABLE statements to physically store computed columns in the table. Their values are updated when any columns that are part of their calculation change. By marking a computed column as PERSISTED, you can create an index on a computed column that is deterministic but not precise. Additionally, if a computed column references a CLR function, the Database Engine cannot verify whether the function is truly deterministic. In this case, the computed column must be PERSISTED so that indexes can be created on it. For more information, see <a id="ctl00_rs1_mainContentContainer_ctl01" onclick="javascript:Track('ctl00_rs1_mainContentContainer_ctl00|ctl00_rs1_mainContentContainer_ctl01',this);" href="http://msdn.microsoft.com/en-us/library/ms189292%28SQL.90%29.aspx">Creating Indexes on Computed Columns</a>.</p><p><br /></p><p style="font-weight: bold;">Example:</p><p><br /></p><p>SET ANSI_NULLS ON<br />GO<br />SET QUOTED_IDENTIFIER ON<br />GO<br />SET ANSI_PADDING ON<br />GO<br />CREATE TABLE [dbo].[Test](<br /> [id] [int] NULL,<br /> [Name] [varchar](max) NULL,<br /> <span style="font-weight: bold;"> [CSVName] AS (checksum([Name])) PERSISTED</span><br />) ON [PRIMARY]<br /><br />GO<br />SET ANSI_PADDING OFF</p><p><br /></p><p>the Checksum value for Name will be stored in the CSVName column and it will be updated every time there is a change in Name column.</p><p><br /></p><p>Reference :<br /><a href="http://msdn.microsoft.com/en-us/library/ms191250%28SQL.90%29.aspx">http://msdn.microsoft.com/en-us/library/ms191250(SQL.90).aspx</a><br /></p>Musab Umairhttp://www.blogger.com/profile/00709484447531505692noreply@blogger.com0tag:blogger.com,1999:blog-1895280144594598079.post-36347632437823010662009-03-27T17:37:00.002+05:002010-12-21T21:43:41.613+05:00Users get blocked when access same table while getting Unique IDs--Table which will have the Unique IDs<div>CREATE TABLE dbo.CounterTable </div><div>(</div><div>Counter int IDENTITY(1,1) NOT NULL</div><div>)<br />GO<br />--Procedure which Inserts unique IDs in table and returns the New ID </div><div><a name='more'></a></div><div>CREATE PROCEDURE dbo.GetNextCounter </div><div>AS </div><div>BEGIN TRAN </div><div>DECLARE @Counter BIGINT </div><div><br /></div><div>INSERT INTO dbo.CounterTable DEFAULT VALUES </div><div>SET @Counter = SCOPE_IDENTITY() </div><div>COMMIT </div><div>RETURN @Counter </div><div><br /></div><div>GO </div><div><br /></div><div>--Now Use the @Counter to give each user the Unique ID</div><div><br /></div><div> DECLARE @Counter int </div><div>EXEC @Counter = dbo.GetNextCounter </div><div>SELECT @Counter</div>Musab Umairhttp://www.blogger.com/profile/00709484447531505692noreply@blogger.com0tag:blogger.com,1999:blog-1895280144594598079.post-79239513697559439012009-03-18T15:13:00.004+05:002010-12-21T21:44:14.740+05:00Simple cursor Example in Microsoft SQL ServerDECLARE @String nVARCHAR(MAX)<br />DECLARE @getInputBuffer CURSOR<br />SET @getInputBuffer = CURSOR FOR<br />SELECT Text FROM Table<br /><br />OPEN @getInputBuffer<br /><br />FETCH NEXT<br />FROM @getInputBuffer INTO @String<br /><br />WHILE @@FETCH_STATUS = 0<br /><a name='more'></a><br />BEGIN<br /><br />PRINT @String<br />FETCH NEXT<br />FROM @getInputBuffer INTO @String<br /><br />END<br /><br />CLOSE @getInputBuffer<br />DEALLOCATE @getInputBufferMusab Umairhttp://www.blogger.com/profile/00709484447531505692noreply@blogger.com0tag:blogger.com,1999:blog-1895280144594598079.post-91225250363979488842009-03-18T15:06:00.000+05:002009-03-18T15:08:16.978+05:00Get foreign keys of a table in SQL Server<div><div>SELECT<span class="Apple-tab-span" style="white-space:pre"> </span> f.name AS ForeignKey</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>,OBJECT_NAME(f.parent_object_id) AS TableName</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>,COL_NAME(fc.parent_object_id</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>,fc.parent_column_id) AS ColumnName</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>,OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>,COL_NAME(fc.referenced_object_id</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>,fc.referenced_column_id) AS ReferenceColumnName</div><div>FROM<span class="Apple-tab-span" style="white-space:pre"> </span>sys.foreign_keys AS f</div><div>INNER JOIN sys.foreign_key_columns AS fc</div><div><span class="Apple-tab-span" style="white-space:pre"> </span> ON f.OBJECT_ID = fc.constraint_object_id</div><div>WHERE<span class="Apple-tab-span" style="white-space:pre"> </span>OBJECT_NAME (f.referenced_object_id) = 'TableName'</div><div>ORDER BY 2</div></div>Musab Umairhttp://www.blogger.com/profile/00709484447531505692noreply@blogger.com0tag:blogger.com,1999:blog-1895280144594598079.post-85561954135492585702009-03-18T15:02:00.002+05:002010-01-04T22:13:13.750+05:00Find /Remove New Line or Carriage Return Character in String or Text in SQL Server<div>1) </div><div><br /></div><div>DECLARE @NewLine char(2)</div><div>SET @NewLine=char(13)+char(10)</div><div><br /></div><div>SELECT<span class="Apple-tab-span" style="white-space:pre"> </span>*</div><div>FROM<span class="Apple-tab-span" style="white-space:pre"> </span>TABLENAME</div><div>WHERE<span class="Apple-tab-span" style="white-space:pre"> </span>CHARINDEX(@NewLine,ColumnName) > 0<br /></div><div><br /></div><div><br /></div><div><div>2) </div><div><br /></div><div>DECLARE @NewLine char(2)</div><div>SET @NewLine=char(13)</div><div><br /></div><div>SELECT<span class="Apple-tab-span" style="white-space: pre; "> </span>*</div><div>FROM<span class="Apple-tab-span" style="white-space: pre; "> </span>TABLENAME</div><div>WHERE<span class="Apple-tab-span" style="white-space: pre; "> </span>CHARINDEX(@NewLine,ColumnName) > 0<br /></div><div><br /></div><div><br /></div><div><br /></div><div><div>3) </div><div><br /></div><div>DECLARE @NewLine char(2)</div><div>SET @NewLine=char(10)</div><div><br /></div><div>SELECT<span class="Apple-tab-span" style="white-space: pre; "> </span>*</div><div>FROM<span class="Apple-tab-span" style="white-space: pre; "> </span>TABLENAME</div><div>WHERE<span class="Apple-tab-span" style="white-space: pre; "> </span>CHARINDEX(@NewLine,ColumnName) > 0<br /></div><div><br /></div></div></div>Musab Umairhttp://www.blogger.com/profile/00709484447531505692noreply@blogger.com3tag:blogger.com,1999:blog-1895280144594598079.post-22946571345423390762009-03-18T14:51:00.000+05:002009-03-18T14:55:28.993+05:00Delete Duplicate Records Using Common Tabel Expression<div><br /></div><div>there are many ways to delete the duplicates in a table but sometimes the group by doesnt seem to work so you can try out the new feature of Microsoft SQL Server "Common Table Expression"</div><div><br /></div><div>With Dups as </div><div>(</div><div>SELECT row_number() over (</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>partition by ColumnName </div><div><span class="Apple-tab-span" style="white-space:pre"> </span>order by ColumnName ) as RowNum</div><div>FROM Table</div><div>)</div><div>DELETE </div><div>FROM Dups </div><div>WHERE rownum > 1</div><div><br /></div><div><br /></div><div><br /></div>Musab Umairhttp://www.blogger.com/profile/00709484447531505692noreply@blogger.com0tag:blogger.com,1999:blog-1895280144594598079.post-53373190994825792662009-01-13T12:50:00.000+05:002009-03-18T15:26:42.349+05:00Solution for Large volume of backup storage on tape with high performance and security<span class="Apple-style-span" style="font-size: medium;">Linear Tape-Open (or LTO) is a magnetic tape data storage technology originally developed in the late 1990s as an open standards alternative to the proprietary magnetic tape formats that were available at the time. Seagate, Hewlett-Packard, and IBM initiated the LTO Consortium, which directs development and manages licensing and certification of media and mechanism manufacturers. The standard form-factor of LTO technology goes by the name "Ultrium", the original version of which was released in 2000 and could hold 100 GB of data in a single cartridge. The most recent version was released in 2007 and can hold 800 GB in the same size cartridge<br /><br /></span><a href="http://h10010.www1.hp.com/wwpc/us/en/en/WF05a/12169-304612-3446236-3446236-3446236-3454484.html"><span class="Apple-style-span" style="font-size: medium;">http://h10010.www1.hp.com/wwpc/us/en/en/WF05a/12169-304612-3446236-3446236-3446236-3454484.html</span></a><span class="Apple-style-span" style="font-size: medium;"><br /><br /></span><a href="http://en.wikipedia.org/wiki/Linear_Tape-Open"><span class="Apple-style-span" style="font-size: medium;">http://en.wikipedia.org/wiki/Linear_Tape-Open</span></a>Musab Umairhttp://www.blogger.com/profile/00709484447531505692noreply@blogger.com1tag:blogger.com,1999:blog-1895280144594598079.post-29410489623530011902008-12-26T16:00:00.000+05:002009-03-18T15:28:13.391+05:00Manage web sessions in database with .net 2.0<span style="font-size:85%;"><span><span class="Apple-style-span" style="font-size: medium;">This is the utility placed in the .net framwork v2.0 folder within the windows directory.</span></span><span class="Apple-style-span" style="font-size: medium;"><br /><br />just run the command and you will get a script to make a db and it will automatically create the required tables and procedured in it.<br />Just run the command and execute the sql script generated by the command on the target server.<br /><br /></span><span><span class="Apple-style-span" style="font-size: medium;">aspnet_regsql.exe</span></span><span class="Apple-style-span" style="font-size: medium;"><br /></span><span><span class="Apple-style-span" style="font-size: medium;"> -S servername</span></span><span class="Apple-style-span" style="font-size: medium;"><br /></span><span><span class="Apple-style-span" style="font-size: medium;">-U username</span></span><span class="Apple-style-span" style="font-size: medium;"><br /></span><span><span class="Apple-style-span" style="font-size: medium;">-P password</span></span><span class="Apple-style-span" style="font-size: medium;"><br /></span><span><span class="Apple-style-span" style="font-size: medium;">-ssaddd </span></span><span class="Apple-style-span" style="font-size: medium;"><br /></span><span><span class="Apple-style-span" style="font-size: medium;">/*just add -ssaddd parameter to tell sql that you are going to add the functionalities */</span></span><span class="Apple-style-span" style="font-size: medium;"><br /></span><span><span class="Apple-style-span" style="font-size: medium;">-sqlexportonly c:\sqlscript.txt </span></span><span class="Apple-style-span" style="font-size: medium;"><br /></span><span><span class="Apple-style-span" style="font-size: medium;">/* this will generate the script to the specified path */</span></span><span class="Apple-style-span" style="font-size: medium;"><br /></span><span><span class="Apple-style-span" style="font-size: medium;">-sstype p</span></span><span class="Apple-style-span" style="font-size: medium;"><br /></span><span><span class="Apple-style-span" style="font-size: medium;">/* in sstype paramerter specifying P will mention that you are enforcing persistant database*/</span></span><span class="Apple-style-span" style="font-size: medium;"><br /><br /></span><span><span class="Apple-style-span" style="font-size: medium;">command will be like<br /><br /><br /></span></span><span style=""><span class="Apple-style-span" style="font-size: medium;"><span class="Apple-style-span" style="font-style: italic;">aspnet_regsql.exe -S myserver -U username -P password -ssaddd -sqlexportonly c:\sqlscript.txt -sstype p</span></span></span><span class="Apple-style-span" style="font-size: medium;"><br /><br />There are some useful articles which you must read to get some detailed knowledge.<br /><br /><br /></span><span style=""><span class="Apple-style-span" style="font-size: medium;">Reference Links :</span></span><span class="Apple-style-span" style="font-size: medium;"><br /><a href="http://www.developer.com/db/article.php/10920_3595766_3">http://www.developer.com/db/article.php/10920_3595766_3</a><br /></span><span style="font-family:verdana;"><span class="Apple-style-span" style="font-family: georgia;"><span class="Apple-style-span" style="font-size: medium;"><a href="http://msdn.microsoft.com/en-us/library/ms229862(VS.80).aspx">http://msdn.microsoft.com/en-us/library/ms229862(VS.80).aspx</a><br /><a href="http://msdn.microsoft.com/en-us/library/h6bb9cz9(VS.71).aspx">http://msdn.microsoft.com/en-us/library/h6bb9cz9(VS.71).aspx</a><br /></span></span><br /></span></span>Musab Umairhttp://www.blogger.com/profile/00709484447531505692noreply@blogger.com0