tag:blogger.com,1999:blog-73675710596088098802024-02-19T14:23:58.113-05:00Sugesh's BLOGThis BLOG is used to share my knowledge on SQL Server.SugeshKumar Rajendranhttp://www.blogger.com/profile/10383062145145324874noreply@blogger.comBlogger91125tag:blogger.com,1999:blog-7367571059608809880.post-47424319516845481282011-01-16T11:58:00.004-05:002011-01-16T12:04:38.649-05:00Get Details for Month for any given DateThe given script below will give details for a given date as below.<br /><br />1. First Day of the month along with the Weekday<br />2. Weekday of the Given date<br />3. Last day of the month along with the Weekday<br /><br /><em><strong>Script:</strong></em><br /><br />DECLARE @date DATETIME<br />SELECT @date = GETDATE() --INPUT YOUR DATE HERE<br />SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@date)-1),@date),101) AS DateofMonth,datename(dw,CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@date)-1) ,@date),101)) as Week_Day,<br />'First Day of Month for Date Given' AS DayofMonth<br />UNION<br />SELECT CONVERT(VARCHAR(25),GETDATE(),101) AS DateofMonth, DAtename(dw,CONVERT(VARCHAR(25),GETDATE(),101)) as Week_Day,'Date Given' AS DayofMonth<br />UNION<br />SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@date))),DATEADD(mm,1,@date)),101) ,DAtename(dw,CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@date))) ,DATEADD(mm,1,@date)),101)) as Week_Day,<br />'Last Day of Month for Date Given'<br /><br /><em><strong>Result:</strong></em><br /><br />DateofMonth Week_Day DayofMonth<br />01/01/2011 Saturday First Day of Month for Date Given<br />01/16/2011 Sunday Date Given<br />01/31/2011 Monday Last Day of Month for Date GivenSugeshKumar Rajendranhttp://www.blogger.com/profile/10383062145145324874noreply@blogger.com0tag:blogger.com,1999:blog-7367571059608809880.post-48190307950150551462011-01-16T10:52:00.003-05:002011-01-16T11:01:43.280-05:00Back to BloggingLife's changes happen so sooner than expected. It was the same period last year that I decided that I should minimize my blogging and professional network to spend more personal time as I anticipated busy personal and professional work.<br /><br />It's this year that I know that I can spend few hours every week without affecting my personal or professional life, thanks to my understanding family and friends who would like to see me back to those good days. The difference was simple, <strong><em>attitude.</em></strong><br /><br />"I am too tired spending time learning, reading and writing. why not take sometime off and feel the personal life?, this was the one for last year and for this year it is "I have a understanding family, friends and people around me to help and keep me happy through my personal life, why not spend some time for the community?"<br /><br />This difference made things easier for me to start writing back. You will see more and more blogging, discussions and posts from me. Keep me posted if you need anything specific.<br /><br />At this moment, I would love to say sincere thanks to my understanding PARENTS and WIFE. Encouragement comes from nowhere, it comes from your heart, these are people who live there and that pushes me to move forward quicker.SugeshKumar Rajendranhttp://www.blogger.com/profile/10383062145145324874noreply@blogger.com0tag:blogger.com,1999:blog-7367571059608809880.post-2972450272451514992010-11-08T18:35:00.002-05:002010-11-08T18:39:34.276-05:00If these wouldn't have happenedTen things that had changed my life dramatically.<br /><br />1. A back injury when I was in 11th grade that kept my away from playing Volley Ball. If I would have continued playing, who knows I would have end up screwing my academics. With help of the injury, I made a better score in 12th Grade and got admitted to one of the best engineering colleges in Tamilnadu.<br /><br />2. A playful college life that left me among millions of people searching for IT job. But, it was this place that gave me friends for life time.<br /><br />3. My first job in Covansys, I was moved to Covansys Bangalore along with the entire batch of Freshers and placed in PeopleSoft project. Thanks to the management for putting me in Infrastructure Admin role. My first job was kind of Windows Admin/PeopleSoft DBA.<br /><br />4. Things changed again forcing move to Quit Covansys and move to Chennai. Got a job in Hexaware, Chennai as PeopleSoft/SQL Server DBA in the internal systems Project. A role that I will remember for lifetime, it was here that I nurtured from an amateur Professional to an expert.<br /><br />5. An unwanted argument that led me to decide on quitting Hexaware. Thanks to Lord, if this would not have happened I would have still been with Hexaware and would not have been what I am now.<br /><br />6. Life @ CITIGROUP, a short-lived one but changed the most of my professional career. I have dreamt of being a Microsoft Valuable Professional, it was here that I made friendship with Vidhyasagar and Deepak, both of them are current MVPs to bring something out of us to become one of India's renowned SQL Server MVPs.<br /><br />7. Help from my Schoolmate Vijay, who guided me to get H1B visa to USA. Again, a lifetime dream that was accomplished with some hurdles here and there.<br /><br />8. My current job in ThomsonReuters. I am glad to work with expertise SQL Server team and with help of people around me here, I can assure that I would one day realize my dream of seeing me as Vice-President of Operations, IT for some company around this little world.<br /><br />9. One that often disturbs me now and then, an unexpected surgery to my father that took him out of being in his usual activeness and one that keeps us bothering now and then.<br /><br />10. Long-awaited one, my marriage as the famous saying goes "In the opinion of the world, marriage ends all, as it does in a comedy. The truth is precisely the opposite: it begins all". We are planning to make the beginning. At the end of road, we wish to hear "Better Half" related to us and Yes, It's "We Wish"SugeshKumar Rajendranhttp://www.blogger.com/profile/10383062145145324874noreply@blogger.com0tag:blogger.com,1999:blog-7367571059608809880.post-26113504097279054962010-07-18T20:58:00.001-04:002010-07-18T21:01:20.042-04:00Thank You ALL for making SQL Data Camp @ Chennai a GRAND SUCCESSOrganizing an event is a tough job and making it a great success is toughest job. I hope, Chennai SQL Server User Group did what it makes to say SQL data camp a GRAND SUCCESS,<br />At this moment being the chairperson of the organizing committee, I would like to thank people who were involved in bringing success to the first ever day long SQL Server event in India.<br /><br /><strong>Speakers:<br /></strong>I would like to thank all the speakers for accepting our invitation to deliver a session.<br /><br /><strong>Pinal</strong>, travelling from Malaysia to Hyderabad and to Chennai only to make for this event and delivered two excellent sessions on indexing.<br /><br /><strong>Madhu, Sudhir Mahendru and Basker Krishnamoorthy(members of Data Architecture Center of Expertise (DACoE) of Cognizant Technology Solutions)</strong> - This team was impressive in making clear to audience why DATA DESIGNING is so important to any application development lifecycle.<br /><br /><strong>Venkatesan Prabhu</strong>, I have to make a special mention about him. He made great changes to his vacation calendar just to make for this event and was pleasant to have him travelling from Australia at the right time.<br /><br /><strong>Raymond</strong>, from Doubletake software presented one of the most exciting sessions on high availability with Doubletake. I have never seen an UG giving one full session to a sponsor and we are proud of making our sponsor a part of our team.<br /><br /><strong>Sponsors:</strong><br />If speakers were driving the event throughout the day, it was our sponsors you were with us from the day we planned till this last moment bringing energy and enthusiasm among us. The following are the persons who drove us through success.<br /><br /><strong>Blythe Morrow, Sujatha Metha, Sanj Gaddam of PASS.<br />Doubletake Software<br />Redgate software<br />Sql-articles.com<br />Jacob Sebastian of BeyondRelational.com<br /></strong><br /><strong>My User Group Colleagues - Vidhyasagar and Deepak:</strong><br />These both, I have no words to thank them; I am New York and was instructing them to do what needs to be done. And these guys have accomplished more than what I asked them. They have always been part of my community journey and this one is next stepping stone for us. We together have set a new milestone of conducting an event for SQL Server and we will make sure we will better this one in future events.<br /><br /><strong>User Group Members:<br /></strong>This group has been one of the most active SQL server user groups around India and the members have been most energetic lot. They are the one who bring us enthusiasm and energy for us to take steps forward.<br /><br />Personally, I was so proud to hear from Pinal, Vidhyasagar and Deepak that there so many attendees wanting to meet and chat with me. I apologize for not turning to be there. But I will make sure that I will meet everyone when I am in India for vacation.<br /><br /><strong>Nagesh, Selva and Senthil:<br /></strong>These three need a special mention here. They have no association with either this User Group or SQL Server but were kindhearted enough to help us from designing the Logos, website, arranging things, coordinating with vendors, loaning their personal car for shuttle and what not. Thank You guys. Without you we would have had tough time managing things around us.<br /><br />I would once again take this opportunity to Thank everyone for making this event a GRAND SUCCESS.<br /><br />If I would have missed someone, please apologize me.<br /><br />Thank you All.SugeshKumar Rajendranhttp://www.blogger.com/profile/10383062145145324874noreply@blogger.com2tag:blogger.com,1999:blog-7367571059608809880.post-61065224012249256892010-01-20T02:48:00.002-05:002010-01-20T02:50:04.469-05:00SQL Server 2008 R2 by MAY 2010Microsoft has announced the SQL Server 2008 R2 will be released to manufacturing in May 2010. The official statement is in the link below.<div><br /></div><div><a href="http://blogs.technet.com/dataplatforminsider/archive/2010/01/19/sql-server-2008-r2-gets-an-official-date.aspx">http://blogs.technet.com/dataplatforminsider/archive/2010/01/19/sql-server-2008-r2-gets-an-official-date.aspx</a></div>SugeshKumar Rajendranhttp://www.blogger.com/profile/10383062145145324874noreply@blogger.com0tag:blogger.com,1999:blog-7367571059608809880.post-69313077171290957322009-11-28T18:26:00.002-05:002009-11-28T18:31:43.008-05:00Consolidating our blogs into one websiteFolks,<div><br /></div><div>Me, Vidhyasagar and Deepak rangarajan, we have decided that we would be posting our all articles, reviews, scripts and suggestions in <a href="http://www.sql-articles.com">SQL-ARTICLES</a> website moving forward. I will be moving all articles from the blog to the website. The blog will be active for next few weeks before I finally call for SHUTDOWN.</div><div><br /></div><div>I would be happy to redirect all my users to our websites, Please find useful links below.</div><div><br /></div><div>SQL-ARTICLES - http://www.sql-articles.com</div><div>Forums - http://www.sql-articles.com/forums</div><div>Suggestions - http://www.sql-articles.com/suggestions</div><div><br /></div><div>Thanks for being a reader of my blog and request you to visit our site. Your constant encouragement would help us grow more efficiently.</div><div><br /></div><div>Thanks</div><div>Sugeshkumar Rajendran</div><div>SQL Server MVP</div>SugeshKumar Rajendranhttp://www.blogger.com/profile/10383062145145324874noreply@blogger.com1tag:blogger.com,1999:blog-7367571059608809880.post-34213205424991594412009-10-10T19:59:00.002-04:002009-10-11T10:37:06.726-04:00The Unhealthy Argument<p class="MsoNormal">This post of mine is 100 percent a personal opinion and was written not to hurt anyone.</p> <p class="MsoNormal"><span class="Apple-tab-span" style="white-space:pre"> </span>Off late, I have been hearing or knowing incidents which have involved 2 or more persons going into an argument on deciding which technology is the best is it WINDOWS or UNIX, ORACLE or SQL SERVER, .Net or JAVA, IE or Chrome, etc. There are few incidents that came to my notice which I would like to share with you all.</p> <p class="MsoNormal">1. This was between friends of mine who worked in my previous company; they both work in the same team providing support to one of their clients. One is an ORACLE DBA and other is SQL Server DBA. They have argued hardly to decide which Database systems is the best and have ended up criticizing Larry Ellison and Bill Gates.</p> <p class="MsoNormal">2. The next is between the web browsers, I have read a numerous articles in the blogs and other websites saying IE is the best or Chrome is the best or Firefox is the best, etc. The same has happened between my known persons where they have ended using abusive languages. I am sorry to say this, but when people lose temper that is the end result.</p> <p class="MsoNormal">3. Again, between my old colleagues, about WINDOWS or UNIX, each of them have had strong points to prove that they work on the latest technology of the server side Operating systems but failed to convince others. Thank God, NO blows or words were exchanged in the incident.</p> <p class="MsoNormal"><span class="Apple-tab-span" style="white-space:pre"> </span>Now, let me come to say this, each technology or application is designed for a purpose and has its own pros and cons with it. Thanks to my patience in the recent past that I have not had such an Unhealthy Argument like this. In my opinion, a Database or an Operating system is chosen with the needs of the application or the system to be designed. It comes as a decision on that has to be compatible with the end application to be designed. The architect who decides on what needs to be used is not a biased person, he/she knows that he has to be committed to his company's core business values and chooses one deciding on it.</p> <p class="MsoNormal"><span class="Apple-tab-span" style="white-space:pre"> </span>So, people going forth I request each of you not to have a discussion or argument to decide which the best is. We all work on the latest technologies of what we have now and should be proud that most of us are redefining technology that has got us in the past. </p> <p class="MsoNormal"><span class="Apple-tab-span" style="white-space:pre"> </span>Again, this is a personal opinion and was written not to hurt anyone. Thanks for taking time to read this blog. Your comments on the same are welcome.</p>SugeshKumar Rajendranhttp://www.blogger.com/profile/10383062145145324874noreply@blogger.com2tag:blogger.com,1999:blog-7367571059608809880.post-3383130743529114112009-10-01T10:43:00.002-04:002009-10-01T10:44:40.969-04:00Back to BloggingIt has been almost 40 days since my last blog. I am back to work and people can expect more SQL Server related stuff on this blog again. If you need any specific interests, Please do send me a note so that the same can be put as an article.<div><br /></div><div>Many thanks for reading.</div>SugeshKumar Rajendranhttp://www.blogger.com/profile/10383062145145324874noreply@blogger.com1tag:blogger.com,1999:blog-7367571059608809880.post-76506791910551301472009-08-18T01:38:00.002-04:002009-08-18T01:40:53.283-04:00Out for VacationFor those who are trying to mail me or looking out reasons finding why I am not online in any of the messangers. The answer is here. I will be out for vacation till mid of september. I will check mails few times a week but no more than that. If i don't write back to you, Please wait. Many thanks.SugeshKumar Rajendranhttp://www.blogger.com/profile/10383062145145324874noreply@blogger.com0tag:blogger.com,1999:blog-7367571059608809880.post-74165312584535042712009-08-13T07:14:00.002-04:002009-08-13T07:21:16.681-04:00Virtual TechDays: FREE Technical Training Opportunity<p class="MsoNormal">Microsoft is Offering Free SQL Server and other products Techinical training in India. This is an online event. Please use the link below to register.</p><p class="MsoNormal"><a href="https://login.live.com/wlogin.srf?appid=000000004C002E22&alg=wsignin1.0">Click Here to Register</a></p><p class="MsoNormal"><o:p></o:p></p><p class="MsoNormal"><o:p></o:p></p><p class="MsoNormal"><o:p></o:p></p><p class="MsoNormal"><o:p></o:p></p><p class="MsoNormal"><o:p></o:p></p><p class="MsoNormal"><o:p></o:p></p><p class="MsoNormal"><o:p></o:p></p>SugeshKumar Rajendranhttp://www.blogger.com/profile/10383062145145324874noreply@blogger.com0tag:blogger.com,1999:blog-7367571059608809880.post-713817043502888322009-08-04T06:46:00.002-04:002009-08-04T06:49:27.574-04:00Chennai SQL Server User Group MeetingWe held our first SQL Server User Group meeting successfully. The deatils of what happened on the event and the related presentations can be downloaded from the link below. Please feel free to touch base with me if you are interested to be part of this wonderful user group.<div><br /></div><div><a href="http://cssug.blogspot.com/2009/07/chennai-sql-server-user-group-meeting.html">http://cssug.blogspot.com/2009/07/chennai-sql-server-user-group-meeting.html</a></div>SugeshKumar Rajendranhttp://www.blogger.com/profile/10383062145145324874noreply@blogger.com0tag:blogger.com,1999:blog-7367571059608809880.post-92194588043052630272009-07-01T06:50:00.003-04:002009-07-01T06:57:00.403-04:00Awarded MVP AgainI am Happy to say that I have been Awarded MVP for this year too. Adding the TAG MVP to my name had always distinguished me from other technologists near me. It has been a pleasant experience and happy to be on those same lines again for another year. And it's Thanking time again for those who have made a difference in my life and for those who have been with me during my tough days this year.<br /><br /><span style="font-weight:bold;">Krishna Gopalan</span> - KG, I wish to be near you to share the joy this moment. I can still hear you saying "Sugesh, I know you are the right person to do this job", whenever I heard that gave me confidence to enrich my skills as a DBA helping me to achieve near expertise.<br /><br /><span style="font-weight:bold;">Stephen Seth, Vidhya Sagar and Deepak</span> - It is with these guys that I grew up enriching my SQL Server skills. Without them, I would still be a DBA not a MVP.<br /><br />I would like to thank my friends <span style="font-weight:bold;">Dalavai Shanmugha Sundaram, Mr&Mrs. Stephen Seth, Mr&Mrs. Arun Kamaraj, Chakravarthi Elangovan and Poornima rangaswamy</span> for being with me during this tough days of personal and professional career.<br /><br />A special mention of thanks to <span style="font-weight:bold;">Rangaswamy and Rani Thilagam Rangaswamy</span> for shouldering my responsibilities in India when I stay in USA.<br /><br />And <span style="font-weight:bold;">my parents and sister</span>, I have no words to thank them. Without thier support and encouragement I would not have got this again.SugeshKumar Rajendranhttp://www.blogger.com/profile/10383062145145324874noreply@blogger.com2tag:blogger.com,1999:blog-7367571059608809880.post-470459215785539632009-06-10T17:57:00.002-04:002009-06-10T20:32:05.291-04:00Creating System Objects in SQL ServerYou can create an object in SQL Server and convert it to a system object just by marking the object as given below.<br /><br />sp_MS_marksystemobject 'object_name'SugeshKumar Rajendranhttp://www.blogger.com/profile/10383062145145324874noreply@blogger.com0tag:blogger.com,1999:blog-7367571059608809880.post-20992514250496544532009-05-09T08:40:00.000-04:002009-05-09T08:40:10.169-04:00Are You Certifiable???"www.areyoucertifiable.com"SugeshKumar Rajendranhttp://www.blogger.com/profile/10383062145145324874noreply@blogger.com0tag:blogger.com,1999:blog-7367571059608809880.post-29898481891220095182009-04-21T11:17:00.002-04:002009-04-21T11:18:16.116-04:00Drive Details using TSQL<div>SET NOCOUNT ON</div><div><br /></div><div>DECLARE @res INT, </div><div><span class="Apple-tab-span" style="white-space:pre"> </span>@objdets INT,</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>@drive INT, </div><div><span class="Apple-tab-span" style="white-space:pre"> </span>@getdrive VARCHAR(13), </div><div><span class="Apple-tab-span" style="white-space:pre"> </span>@totalsize VARCHAR(50), </div><div><span class="Apple-tab-span" style="white-space:pre"> </span>@freespace VARCHAR(50), </div><div><span class="Apple-tab-span" style="white-space:pre"> </span>@DriveLetter char(1)</div><div><br /></div><div>CREATE TABLE ##Drivespacedetails</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>(</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>DriveLetter CHAR(1),</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>TotalSpace bigint,</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>FreeSpace bigint,</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>)</div><div><br /></div><div>INSERT INTO ##Drivespacedetails(DriveLetter,FreeSpace)<span class="Apple-tab-span" style="white-space:pre"> </span>EXEC master.dbo.xp_fixeddrives</div><div><br /></div><div>DECLARE drivedetails CURSOR<span class="Apple-tab-span" style="white-space:pre"> </span>FOR SELECT DriveLetter FROM ##Drivespacedetails</div><div><br /></div><div>OPEN drivedetails</div><div><br /></div><div>FETCH NEXT FROM drivedetails INTO @DriveLetter</div><div>WHILE (@@fetch_status <> -1)</div><div>BEGIN</div><div><span class="Apple-tab-span" style="white-space:pre"> </span> SET @getdrive = 'GetDrive("' + @DriveLetter + '")' </div><div> EXEC @res = sp_OACreate 'Scripting.FileSystemObject', @objdets OUTPUT </div><div> IF @res = 0 </div><div> EXEC @res = sp_OAMethod @objdets, @getdrive, @drive OUTPUT </div><div> IF @res = 0 </div><div> EXEC @res = sp_OAGetProperty @drive,'TotalSize', @totalsize OUTPUT </div><div><span class="Apple-tab-span" style="white-space:pre"> </span> IF @res = 0 </div><div> EXEC @res = sp_OAGetProperty @drive,'FreeSpace', @freespace OUTPUT </div><div> IF @res <> 0 </div><div> EXEC sp_OADestroy @drive </div><div><span class="Apple-tab-span" style="white-space:pre"> </span> EXEC sp_OADestroy @objdets </div><div><span class="Apple-tab-span" style="white-space:pre"> </span> SET @totalsize = (CONVERT(BIGINT,@totalsize) / 1048576 )</div><div><span class="Apple-tab-span" style="white-space:pre"> </span> SET @freespace = (CONVERT(BIGINT,@freespace) / 1048576 )</div><div><br /></div><div> update ##Drivespacedetails set TotalSpace=@totalsize,FreeSpace=@freespace where DriveLetter=@DriveLetter</div><div><span class="Apple-tab-span" style="white-space:pre"> </span></div><div><span class="Apple-tab-span" style="white-space:pre"> </span> FETCH NEXT FROM drivedetails INTO @DriveLetter</div><div>END</div><div><br /></div><div>CLOSE drivedetails</div><div>DEALLOCATE drivedetails</div><div><br /></div><div>SELECT @@servername as ServerName,DriveLetter,FreeSpace AS [FreeSpace MB], (TotalSpace - FreeSpace) AS [UsedSpace MB], TotalSpace AS [TotalSpace MB], ((CONVERT(NUMERIC(9,0),FreeSpace) / CONVERT(NUMERIC(9,0),TotalSpace)) * 100) AS [Percentage Free]</div><div>FROM ##Drivespacedetails ORDER BY [DriveLetter] ASC<span class="Apple-tab-span" style="white-space:pre"> </span></div><div><br /></div><div>DROP TABLE ##Drivespacedetails</div><div><br /></div>SugeshKumar Rajendranhttp://www.blogger.com/profile/10383062145145324874noreply@blogger.com2tag:blogger.com,1999:blog-7367571059608809880.post-14904025120499446662009-04-16T10:04:00.000-04:002009-04-16T10:06:07.586-04:00Determine which objects exist in a particular filegroup<div>SELECT o.[name], o.[type], i.[name], i.[index_id], f.[name]</div><div>FROM sys.indexes i</div><div>INNER JOIN sys.filegroups f</div><div>ON i.data_space_id = f.data_space_id</div><div>INNER JOIN sys.all_objects o</div><div>ON i.[object_id] = o.[object_id]</div><div>WHERE i.data_space_id = f.data_space_id --* New FileGroup*</div><div>GO</div>SugeshKumar Rajendranhttp://www.blogger.com/profile/10383062145145324874noreply@blogger.com0tag:blogger.com,1999:blog-7367571059608809880.post-58597193758859547622009-04-14T10:13:00.000-04:002009-04-14T10:14:01.724-04:00How to find startup procedures in sqlSELECT ROUTINE_NAMEFROM MASTER.INFORMATION_SCHEMA.ROUTINESWHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),'ExecIsStartup') = 1<br /><br />USE MASTER<br />GO<br />SELECT VALUE, VALUE_IN_USE, DESCRIPTION FROM SYS.CONFIGURATIONS WHERE NAME = 'scan for startup procs'GOSugeshKumar Rajendranhttp://www.blogger.com/profile/10383062145145324874noreply@blogger.com0tag:blogger.com,1999:blog-7367571059608809880.post-38262895399985205882009-04-08T11:44:00.003-04:002009-04-08T11:45:51.969-04:00SQL Server 2008 SP1 releasedSQL Server 2008 SP1 has been released to user community. Please find the download details given below.<div><br /></div><div><a href="http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=66ab3dbb-bf3e-4f46-9559-ccc6a4f9dc19">http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=66ab3dbb-bf3e-4f46-9559-ccc6a4f9dc19</a><br /></div>SugeshKumar Rajendranhttp://www.blogger.com/profile/10383062145145324874noreply@blogger.com0tag:blogger.com,1999:blog-7367571059608809880.post-267502538414520342009-04-08T09:59:00.002-04:002009-04-08T10:00:07.075-04:00Drop Indexes in a Database<div>set nocount on</div><div>go</div><div>DECLARE @sql VARCHAR(2000),@DB VARCHAR(100),@count int,@TableName varchar(200),</div><div>@Index_Name varchar(200),@ConstraintName varchar(200)</div><div>SET @DB = db_name()</div><div>if exists(select * from msdb..sysobjects where name =</div><div>'DropIndexes_AllTables')</div><div>drop table msdb.dbo.DropIndexes_AllTables</div><div>CREATE TABLE msdb.dbo.DropIndexes_AllTables(tablename</div><div>varchar(200),ConstraintName varchar(200),Index_Name varchar(200),type</div><div>varchar(10))</div><div>SELECT @SQL = 'USE '+@db+char(13)+'SELECT object_name(parent_object_id)</div><div>TableName,sobj.Name ConstraintName,sidx.name Index_Name,type FROM sys.objects</div><div>sobj</div><div>join '+@db+'.dbo.sysindexes sidx on sobj.parent_object_id = sidx.id</div><div>where indid > 0 and type in</div><div>(''u'',''PK'',''UQ'') and</div><div>sidx.name not like ''_WA_sys_%'''</div><div>print @sql</div><div>insert into msdb.dbo.DropIndexes_AllTables</div><div>exec(@sql)</div><div>create table #temp_baby(ConstraintName varchar(200),TableName varchar(150))</div><div>select ConstraintName,TableName, Index_Name,Type from</div><div>msdb.dbo.DropIndexes_AllTables</div><div><br /></div><div>insert into #temp_baby(ConstraintName,TableName)</div><div>select distinct ConstraintName,TableName from msdb.dbo.DropIndexes_AllTables</div><div>select @count = count(*) from #temp_baby</div><div>while(@count > 0)</div><div>begin</div><div>select top 1 @ConstraintName = ConstraintName,@tablename = tablename from</div><div>#temp_baby</div><div>select @sql = 'USE '+@db+char(13)+'alter table '+@tablename+' drop</div><div>constraint '+@ConstraintName+''</div><div>print @sql</div><div>delete from #temp_baby where ConstraintName = @ConstraintName and tablename =</div><div>@tablename</div><div>select @count = count(*) from #temp_baby</div><div>end</div><div>insert into #temp_baby(ConstraintName,TableName)</div><div>select distinct ConstraintName,TableName from msdb.dbo.DropIndexes_AllTables</div><div>print '------'</div><div>delete from msdb.dbo.DropIndexes_AllTables where Index_Name in (select</div><div>ConstraintName from #temp_baby)</div><div>select @count = count(*) from msdb.dbo.DropIndexes_AllTables</div><div>while(@count > 0)</div><div>Begin</div><div>select top 1 @tablename = TableName,@Index_name = Index_Name from</div><div>msdb.dbo.DropIndexes_AllTables order by tablename</div><div>select @sql = 'USE '+@DB+';drop index</div><div>'+@tablename+'.'+@Index_name+''</div><div>print @sql</div><div>--print @tablename, @Index_name</div><div>delete from msdb.dbo.DropIndexes_AllTables where TableName = @tablename and</div><div>Index_Name = @Index_name</div><div>select @count = count(*) from msdb.dbo.DropIndexes_AllTables</div><div>end</div><div>drop table #temp_baby</div>SugeshKumar Rajendranhttp://www.blogger.com/profile/10383062145145324874noreply@blogger.com0tag:blogger.com,1999:blog-7367571059608809880.post-32206343085373104992009-04-07T09:59:00.003-04:002009-04-07T10:01:58.261-04:00Change Data CaptureThis article introduces to a new feature in SQL Server 2008, Change Data Capture. Change Data Capture records insert, update and delete activity in your table and the details of these changes are available in a format that can be easily consumed. The database must be enabled to Capture Changes to it. A member of sysadmin fixed role can enable the change. Once that is done, members of db_owner can enable tables for which change needs to be monitored. When the first table in the database is enabled, a capture process gathers changed data from the transaction log and inserts the change information in the associated change table. Additional metadata about each transaction is inserted in a metadata table that allows the captured changes to be placed on a conventional timeline.<br /><br /><span style="font-weight:bold;">Step to Enable and Configure Change Data Capture:</span><br />1. Create a database SQL Server 2008<br />create database CDC_TEST<br />go <br />2. Enable CDC in the database created.<br />USE SQL2008CDC<br />GO<br />EXEC sp_cdc_enable_db<br />GO<br />You can verify if CDC is enabled using the query below for the database.<br />select [name] as DBName,is_cdc_enabled from sys.databases where db_name(database_id)=DB_NAME()<br />3. You will be a new schema CDC and few system tables created under the database. Change Data Capture requires exclusive use of cdc user and schema.<br />4. Create a table to enable CDC<br />USE CDC_TEST<br />GO<br />CREATE TABLE dbo.CDC_TEST(<br />ID int Primary Key NOT NULL,<br />Name nvarchar(100) NOT NULL,<br />Email nvarchar(100) NOT NULL)<br />5. SQL Server agent must be started for CDC.<br />6. Enable CD for the table where you need to capture the change.<br />EXEC sp_cdc_enable_table 'dbo', 'cdc_test',<br />@role_name = NULL, @supports_net_changes =1<br />You can verify if CDC is enabled using the query below for the table.<br />use SQL2008CDC<br />go<br />select [name], is_tracked_by_cdc from sys.tables<br />7. Once CDC is enabled for a table, you will find a _CT table created by itself.<br />8. Now we will verify how CDC works by running a insert into the table CDC_TEST.<br />use SQL2008CDC<br />go<br />INSERT INTO dbo.cdc_test<br />values (1, N'sugeshkumar', N'sugeshkr@gmail.com')<br />9. Now update the row that we have inserted in the previous step.<br />use SQL2008CDC<br />go<br />update dbo.cdc_test set name='sugesh' where id = 1<br />10. The before image and after image of the row can be found on the table that was created with the name _CT.<br />The column _$operation has the value for the DML operation done, 1 is for delete, 2 for insert, 3 for before image, 4 for after image<br /><img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiP4lu1h1hKH6slCBveyctoXmmMTNGOsri200jadHYtKyq8zCWkrWJoh7oKig77W9uzpOxBo5iaYd2mrfuaMrMeRgAd9f_ROZbTrC8lHiLeAUrirulIOGu5JlUzTSTQgi9wamnbHiz2yio/s200/untitled.JPG" style="cursor:pointer; cursor:hand;width: 200px; height: 34px;" border="0" alt="" id="BLOGGER_PHOTO_ID_5321949125610810786" /><br />You have seen how to enable and configure CDC for a database and table. The list below gives you all available Stored Procedures, DMVs, Functions and table list that is used by Change Data Capture.<br /><br /><span class="Apple-style-span" style="font-weight: bold;">Stored Procedures</span><br />sys.sp_cdc_add_job (Transact-SQL)<br />sys.sp_cdc_generate_wrapper_function (Transact-SQL)<br />sys.sp_cdc_change_job (Transact-SQL)<br />sys.sp_cdc_get_captured_columns (Transact-SQL)<br />sys.sp_cdc_cleanup_change_table (Transact-SQL)<br />sys.sp_cdc_get_ddl_history (Transact-SQL)<br />sys.sp_cdc_disable_db (Transact-SQL)<br />sys.sp_cdc_help_change_data_capture (Transact-SQL)<br />sys.sp_cdc_disable_table (Transact-SQL)<br />sys.sp_cdc_help_jobs (Transact-SQL)<br />sys.sp_cdc_drop_job (Transact-SQL)<br />sys.sp_cdc_scan (Transact-SQL)<br />sys.sp_cdc_enable_db (Transact-SQL)<br />sys.sp_cdc_start_job (Transact-SQL)<br />sys.sp_cdc_enable_table (Transact-SQL)<br />sys.sp_cdc_stop_job (Transact-SQL)<br /><br /><span class="Apple-style-span" style="font-weight: bold;">Dynamic Management Views</span><br />sys.dm_cdc_log_scan_sessions (Transact-SQL)<br />sys.dm_cdc_errors (Transact-SQL)<br />sys.dm_repl_traninfo (Transact-SQL)<br /><br /><span class="Apple-style-span" style="font-weight: bold;">Tables</span><br />cdc.<capture_instance>_CT<br />Returns one row for each change made to a captured column in the associated source table.<br /><br />cdc.captured_columns<br />Returns one row for each column tracked in a capture instance.<br /><br />cdc.change_tables<br />Returns one row for each change table in the database.<br /><br />cdc.ddl_history<br />Returns one row for each data definition language (DDL) change made to tables that are enabled for change data capture.<br /><br />cdc.lsn_time_mapping<br />Returns one row for each transaction having rows in a change table. This table is used to map between log sequence number (LSN) commit values and the time the transaction committed.<br /><br />cdc.index_columns<br />Returns one row for each index column associated with a change table.<br /><br />dbo.cdc_jobs (Transact-SQL)<br />Returns the configuration parameters for change data capture agent jobs<br /><br /><span class="Apple-style-span" style="font-weight: bold;">Functions</span><br />cdc.fn_cdc_get_all_changes_<capture_instance> (Transact-SQL)<br />sys.fn_cdc_has_column_changed (Transact-SQL)<br />cdc.fn_cdc_get_net_changes_<capture_instance> (Transact-SQL)<br />sys.fn_cdc_increment_lsn (Transact-SQL)<br />sys.fn_cdc_decrement_lsn (Transact-SQL)<br />sys.fn_cdc_is_bit_set (Transact-SQL)<br />sys.fn_cdc_get_column_ordinal (Transact-SQL)<br />sys.fn_cdc_map_lsn_to_time (Transact-SQL)<br />sys.fn_cdc_get_max_lsn (Transact-SQL)<br />sys.fn_cdc_map_time_to_lsn (Transact-SQL)<br />sys.fn_cdc_get_min_lsn (Transact-SQL)<br /><br /></capture_instance></capture_instance></capture_instance>SugeshKumar Rajendranhttp://www.blogger.com/profile/10383062145145324874noreply@blogger.com0tag:blogger.com,1999:blog-7367571059608809880.post-22318962324634374862009-04-06T10:07:00.002-04:002009-04-06T10:08:21.440-04:00Generate Create Index Scripts for a Database-- Get all existing indexes, but NOT the primary keys<br />DECLARE cIX CURSOR FOR<br /> SELECT OBJECT_NAME(SI.Object_ID), SI.Object_ID, SI.Name, SI.Index_ID<br /> FROM Sys.Indexes SI <br /> LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC ON SI.Name = TC.CONSTRAINT_NAME AND OBJECT_NAME(SI.Object_ID) = TC.TABLE_NAME<br /> WHERE TC.CONSTRAINT_NAME IS NULL<br /> AND OBJECTPROPERTY(SI.Object_ID, 'IsUserTable') = 1<br /> ORDER BY OBJECT_NAME(SI.Object_ID), SI.Index_ID<br /><br />DECLARE @IxTable SYSNAME<br />DECLARE @IxTableID INT<br />DECLARE @IxName SYSNAME<br />DECLARE @IxID INT<br /><br />-- Loop through all indexes<br />OPEN cIX<br />FETCH NEXT FROM cIX INTO @IxTable, @IxTableID, @IxName, @IxID<br />WHILE (@@FETCH_STATUS = 0)<br />BEGIN<br /> DECLARE @IXSQL NVARCHAR(4000) SET @IXSQL = ''<br /> SET @IXSQL = 'CREATE '<br /><br /> -- Check if the index is unique<br /> IF (INDEXPROPERTY(@IxTableID, @IxName, 'IsUnique') = 1)<br /> SET @IXSQL = @IXSQL + 'UNIQUE '<br /> -- Check if the index is clustered<br /> IF (INDEXPROPERTY(@IxTableID, @IxName, 'IsClustered') = 1)<br /> SET @IXSQL = @IXSQL + 'CLUSTERED '<br /><br /> SET @IXSQL = @IXSQL + 'INDEX ' + @IxName + ' ON ' + @IxTable + '('<br /><br /> -- Get all columns of the index<br /> DECLARE cIxColumn CURSOR FOR <br /> SELECT SC.Name<br /> FROM Sys.Index_Columns IC<br /> JOIN Sys.Columns SC ON IC.Object_ID = SC.Object_ID AND IC.Column_ID = SC.Column_ID<br /> WHERE IC.Object_ID = @IxTableID AND Index_ID = @IxID<br /> ORDER BY IC.Index_Column_ID<br /><br /> DECLARE @IxColumn SYSNAME<br /> DECLARE @IxFirstColumn BIT SET @IxFirstColumn = 1<br /><br /> -- Loop throug all columns of the index and append them to the CREATE statement<br /> OPEN cIxColumn<br /> FETCH NEXT FROM cIxColumn INTO @IxColumn<br /> WHILE (@@FETCH_STATUS = 0)<br /> BEGIN<br /> IF (@IxFirstColumn = 1)<br /> SET @IxFirstColumn = 0<br /> ELSE<br /> SET @IXSQL = @IXSQL + ', '<br /><br /> SET @IXSQL = @IXSQL + @IxColumn<br /><br /> FETCH NEXT FROM cIxColumn INTO @IxColumn<br /> END<br /> CLOSE cIxColumn<br /> DEALLOCATE cIxColumn<br /><br /> SET @IXSQL = @IXSQL + ')'<br /> -- Print out the CREATE statement for the index<br /> PRINT @IXSQL<br /><br /> FETCH NEXT FROM cIX INTO @IxTable, @IxTableID, @IxName, @IxID<br />END<br /><br />CLOSE cIX<br />DEALLOCATE cIX<br /><br /><br />-- Get all existing primary keys<br />DECLARE cPK CURSOR FOR<br /> SELECT TABLE_NAME, CONSTRAINT_NAME <br /> FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS<br /> ORDER BY TABLE_NAME<br /><br />DECLARE @PkTable SYSNAME<br />DECLARE @PkName SYSNAME<br /><br />-- Loop through all the primary keys<br />OPEN cPK<br />FETCH NEXT FROM cPK INTO @PkTable, @PkName<br />WHILE (@@FETCH_STATUS = 0)<br />BEGIN<br /> DECLARE @PKSQL NVARCHAR(4000) SET @PKSQL = ''<br /> SET @PKSQL = 'ALTER TABLE ' + @PkTable + ' ADD CONSTRAINT ' + @PkName + ' PRIMARY KEY CLUSTERED ('<br /><br /> -- Get all columns for the current primary key<br /> DECLARE cPKColumn CURSOR FOR<br /> SELECT COLUMN_NAME <br /> FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE<br /> WHERE TABLE_NAME = @PkTable AND CONSTRAINT_NAME = @PkName<br /> ORDER BY ORDINAL_POSITION<br /> OPEN cPKColumn<br /><br /> DECLARE @PkColumn SYSNAME<br /> DECLARE @PkFirstColumn BIT SET @PkFirstColumn = 1<br /> -- Loop through all columns and append the sql statement<br /> FETCH NEXT FROM cPKColumn INTO @PkColumn<br /> WHILE (@@FETCH_STATUS = 0)<br /> BEGIN<br /> IF (@PkFirstColumn = 1)<br /> SET @PkFirstColumn = 0<br /> ELSE<br /> SET @PKSQL = @PKSQL + ', '<br /><br /> SET @PKSQL = @PKSQL + @PkColumn<br /><br /> FETCH NEXT FROM cPKColumn INTO @PkColumn<br /> END<br /> CLOSE cPKColumn<br /> DEALLOCATE cPKColumn<br /><br /> SET @PKSQL = @PKSQL + ')'<br /> -- Print the primary key statement<br /> PRINT @PKSQL<br /><br /> FETCH NEXT FROM cPK INTO @PkTable, @PkName<br />END<br />CLOSE cPK<br />DEALLOCATE cPKSugeshKumar Rajendranhttp://www.blogger.com/profile/10383062145145324874noreply@blogger.com0tag:blogger.com,1999:blog-7367571059608809880.post-29808078809943628002009-04-03T11:25:00.003-04:002009-04-03T11:28:55.186-04:00Common SQL Server Security Issues and SolutionsThis article is from Paul S Randal on securing SQL Server. This is a nice article on <span class="Apple-style-span" style="font-style: italic;">how to secure SQL Server. </span><div><span class="Apple-style-span" style="font-style: italic;"><br /></span></div><div><span class="Apple-style-span" style="font-style: italic;"><a href="http://technet.microsoft.com/en-us/magazine/2009.05.sql.aspx">http://technet.microsoft.com/en-us/magazine/2009.05.sql.aspx </a><br /></span></div>SugeshKumar Rajendranhttp://www.blogger.com/profile/10383062145145324874noreply@blogger.com0tag:blogger.com,1999:blog-7367571059608809880.post-3911523956243178902009-04-02T17:11:00.035-04:002009-04-03T14:57:02.801-04:00Show/Hide divs and swap images with SkinID using Javascript<div>The following example shows 2 ways of using javascript to show/hide divs (or any elements) on the click event of an image and also to swap the image that uses a skin id. Since skins are applied on the server side, you cannot change SkinID on client side using javascript. Though this is a crude way of doing it, it worked for developing my website since this functionality is used on only one page. I am sure there are more better and easier ways to do it, but these examples can be used in a simple website that uses asp.net or HTML.<br /><br /><strong style="font-weight: bold;"><u>Example 1: To Show only one div at a time.</u></strong><br /><br /><u><strong>ASP.Net Code</strong></u><br /><pre class="postCode"><div id="divContent"><br /><br /><h2><a href="#" id="lnk1" onclick="return toggle('div1')">What is Test?</a></h2><br /><div id="div1" style="display:none"><br /> <p>Test Test Test Test Test Test Test<br /> Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test<br /> Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test<br /> Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test<br /> Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test<br /> Test Test Test Test Test</p><br /> <p>Test Test Test Test Test Test Test Test Test Test Test Test Test<br /> Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test<br /> Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test<br /> Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test<br /> Test Test Test Test Test Test Test Test Test Test Test Test Test Test</p><br /></div><br /><br /><h2><a href="#" id="lnk8" onclick="return toggle('div8')">What is Sample?</a></h2><br /><div id="div8" style="display:none"><br /> <p>Sample Sample Sample Sample Sample Sample Sample Sample Sample<br /> Sample Sample Sample Sample Sample Sample Sample Sample Sample Sample<br /> Sample Sample Sample Sample Sample Sample Sample Sample Sample Sample<br /> Sample Sample Sample Sample Sample Sample Sample Sample Sample Sample<br /> Sample Sample Sample Sample Sample Sample Sample Sample Sample Sample<br /> Sample Sample Sample Sample Sample Sample Sample Sample Sample Sample<br /> Sample Sample Sample Sample Sample Sample Sample Sample Sample Sample<br /> Sample Sample Sample Sample Sample Sample Sample Sample Sample Sample<br /> Sample Sample Sample Sample Sample Sample Sample Sample Sample Sample<br /> Sample Sample Sample Sample Sample Sample Sample Sample Sample Sample<br /> Sample Sample Sample Sample Sample Sample Sample Sample Sample Sample<br /> Sample Sample Sample Sample Sample Sample Sample Sample Sample Sample<br /> Sample Sample Sample Sample Sample Sample Sample Sample Sample </p><br /></div><br /></div><br /><br /></pre><u><strong>JavaScript Code:</strong></u><br /><pre class="postCode"><br />function toggle(divID)<br />{<br /> var divToShow = document.getElementById(divID); <br /> var divUL = document.getElementById('dvfaq');<br /> var divs = divUL.getElementsByTagName('DIV');<br /> for(var i = 0; i < divs.length; i++)<br /> {<br /> divs[i].style.display = 'none';<br /> if(divToShow.style.display == 'none')<br /> {<br /> divToShow.style.display = 'block';<br /> }<br /> }<br /> return false;<br />}<br /></pre><br /><strong style="font-weight: bold;"><u>Example 2: To Show multiple divs at a time and click to open and close.</u></strong><br /><br /><u><strong>ASP.Net Code</strong></u><br /><pre class="postCode"><br /><a id="lnk0" href="#" onclick="return toggle('div0', 'lnk0')"><br />Q: What is Test? <asp:Image ID="imgToggle" SkinID="ArrowClose"<br />style="display: inline" runat="server" /><asp:Image ID="Image27"<br />SkinID="ArrowOpen" style="display: none" runat="server" /></a><br /><br /><br /><br /><div id="div0" style="display:none"><br /><p>A: Test Test Test Test Test Test Test Test Test Test Test Test Test<br />Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test<br />Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test<br />Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test<br />Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test<br />Test Test Test Test Test Test </p><br /></div><br /><br /><a id="lnk1" href="#" onclick="return toggle('div1', 'lnk1')"><br />Q: What is Sample? <asp:Image ID="Image1" SkinID="ArrowClose"<br />style="display: inline" runat="server" /><asp:Image ID="Image28"<br />SkinID="ArrowOpen" style="display: none" runat="server" /></a><br /><br /><br /><br /><div id="div1" style="display:none"><br /><p> Sample Sample Sample Sample Sample Sample Sample Sample Sample<br />Sample Sample Sample Sample Sample Sample Sample Sample Sample Sample Sample<br />Sample Sample Sample Sample Sample Sample Sample Sample Sample Sample Sample<br />Sample Sample Sample Sample Sample Sample Sample Sample Sample Sample Sample<br />Sample Sample Sample Sample Sample Sample Sample<br />Sample Sample Sample Sample </p><br /></div><br /></pre><br /><strong><u>JavaScript Code:</u></strong><br /><pre class="postCode"><br />function toggle(divID, imgID)<br />{<br />var divToShow = document.getElementById(divID);<br />if(divToShow.style.display == 'none')<br />{<br /> divToShow.style.display = 'block';<br />}<br />else if(divToShow.style.display == 'block')<br />{<br /> divToShow.style.display = 'none';<br />}<br /><br />var imgToShow = document.getElementById(imgID).getElementsByTagName('img')[0];<br />var imgToHide = document.getElementById(imgID).getElementsByTagName('img')[1];<br /><br />if(imgToShow.style.display == 'none')<br />{ <br />imgToShow.style.display = 'inline';<br />imgToHide.style.display = 'none';<br />}<br />else if(imgToShow.style.display == 'inline')<br />{ <br />imgToShow.style.display = 'none'; <br />imgToHide.style.display = 'inline';<br />}<br />return false;<br />}<br /></pre><br /><u><strong>Skin Code:</strong></u><br /><pre class="postCode"><br /><asp:Image runat="server" SkinID="ArrowClose" ImageUrl="images/arrowClose.gif"<br />width="9" height="9" /><br /><br /><asp:Image runat="server" SkinID="ArrowOpen" ImageUrl="images/arrowOpen.gif"<br />width="9" height="9" /><br /></pre></div>Unknownnoreply@blogger.com1tag:blogger.com,1999:blog-7367571059608809880.post-5882799663348612182009-04-01T11:07:00.004-04:002009-04-03T15:09:57.032-04:00Migrating Access Database to SQL Server<span xmlns=""><span style="font-family:Comic Sans MS;">I recently received a call from one of my friend asking me, what is the easiest way to migrate a database from ACCESS to SQL Server? I was curious on answering his question and just went for a search in internet to find it was much simple than what I expected. This article describes the steps to migrate a Access database to SQL Server.<br /></span><p><span style="font-family:Comic Sans MS;">To Migrate Access to SQL Server database, download the tool <a href="http://www.microsoft.com/downloads/details.aspx?FamilyId=D842F8B4-C914-4AC7-B2F3-D25FFF4E24FB&displaylang=en">SQL Server Migration Assistant for Access</a> </span></p><p><span style="font-family:Comic Sans MS;">Install <a href="http://www.microsoft.com/downloads/details.aspx?FamilyId=D842F8B4-C914-4AC7-B2F3-D25FFF4E24FB&displaylang=en">SQL Server Migration Assistant for Access</a><span style="color:black;"> on local computer</span> </span></p><p><span style="font-family:Comic Sans MS;"><span style="color:black;">You need to have the access database closed while migrating to SQL Server.</span> </span></p><p><span style="font-family:Comic Sans MS;">Goto Start->;Programs->;Microsoft SQL Server Migration Assistant 2008 for Access->;Microsoft SQL Server Migration Assistant 2008 for Access from the computer where it's installed. You will see the welcome screen.<br /></span><img style="TEXT-ALIGN: center; MARGIN: 0px auto 10px; WIDTH: 200px; DISPLAY: block; HEIGHT: 132px; CURSOR: hand" id="BLOGGER_PHOTO_ID_5319741876109255922" border="0" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjdkcCd3c-sughijBU3K7RY3-ZDTv_nhW_Fez7TQeZREJVtzV6ht2THCv0zbzhSzTMoT5gIrxu6x3gCgmdY6FJ4dVXAVCQrNdPI87kz4oFRhIcVBi9iw7f7tAEz4bV-Nl4ByYLbS_sRVv0/s200/1.JPG" /></p><p><span style="font-family:Comic Sans MS;">Click Next, Create a new project by entering the Name of the project and it's location. </span><img style="TEXT-ALIGN: center; MARGIN: 0px auto 10px; WIDTH: 200px; DISPLAY: block; HEIGHT: 132px; CURSOR: hand" id="BLOGGER_PHOTO_ID_5319741916993695474" border="0" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgf8kKOKkBR9by4eL8McX9sEGSp1060iv6qKtzvs0HfUOtLRhXVliI2w0iD1IFzqFyyMsFJDPuo_0YAm9UoyZPPjk25vjSqv93xIV3VGFS60Yu1qqjbBYONg6jb_kegG_qjIdE2fL_TxQE/s200/2.JPG" /><span style="font-family:Comic Sans MS;">Click Next, You will be taken to a screen to add the Access database. Click on Add Databases button to specify the folder where your access database resides.<br /></span><img style="TEXT-ALIGN: center; MARGIN: 0px auto 10px; WIDTH: 200px; DISPLAY: block; HEIGHT: 132px; CURSOR: hand" id="BLOGGER_PHOTO_ID_5319741951990107426" border="0" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEinJP1lZqt2L_DPSlFZFYpxd8yyGL-awSstNEOLcy-qAwo_3Y8OukayFT8yM3BbTCc05rbGiZH82CX9-9nOvEefVW7_v4hwUQwBz2vQR1RqreeNMm3P2DTnszqxzAoOgntZXTHom0pz6VQ/s200/3.JPG" /><span style="font-family:Comic Sans MS;">Click Next, Here you will have option to migrate only data and/or the queries too. Here, we are migrating both data and the queries.<br /></span><img style="TEXT-ALIGN: center; MARGIN: 0px auto 10px; WIDTH: 200px; DISPLAY: block; HEIGHT: 132px; CURSOR: hand" id="BLOGGER_PHOTO_ID_5319741986762955602" border="0" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh8-ZWX4dYfFeQlMewmGvkT2soDDma74RibaUcxKzhjOa720TPT5nFxfz2rVN3vVYY4q0NgyIREndqpltlyCPI9bwoDtS2u-UJ_3iU503b7zG00JnxQZ2u6Qz9t0ag3vEIZv1AigxyfgPM/s200/4.JPG" /></p><p><span style="font-family:Comic Sans MS;">Click Next, You will see the SQL Server connection Screen. Here specify the SQL Server instance where you want to move your Access database along with the destination Database name and credentials.<br /></span></p></span><img style="TEXT-ALIGN: center; MARGIN: 0px auto 10px; WIDTH: 200px; DISPLAY: block; HEIGHT: 132px; CURSOR: hand" id="BLOGGER_PHOTO_ID_5319742021879408498" border="0" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh1LbCjP-rXoG4BxMWwlhgVXsdIyaoGClkgYnf1M-rqX4PX6ch2sVYa8wWxXcmEMKafwfhbJOa46O9UPEncrqU1zp9w6KOZipnKCpQA4uuHtya-acdS4OTPXbYm5vJ9NolPa_9Rsj1Zbx4/s200/5.JPG" /> <p><span style="font-family:Comic Sans MS;">Click Next to Link tables. If you want to use your existing Access applications with SQL Server, you can link your original Access tables to the migrated SQL Server tables. Linking modifies your Access database so that your queries, forms, reports, and data access pages use the data in the SQL Server database instead of the data in your Access database. </span><br /></p><img style="TEXT-ALIGN: center; MARGIN: 0px auto 10px; WIDTH: 200px; DISPLAY: block; HEIGHT: 132px; CURSOR: hand" id="BLOGGER_PHOTO_ID_5319742055258506258" border="0" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj_23xqj4ZNSS0tsrFi8Lw-SeaGIkIQefR8pqq7UBbINmV5njMy3qM1_9ylccbYZQa1zV1HuLa4aUjvyWiajV25CLfLWglYgLo2wEmTJP71nwi3qxMnOYLioM8JThn1ohC5szt0SobohmI/s200/6.JPG" /><span style="font-family:Comic Sans MS;">Click Next to start Migration.<br /></span><p><img style="TEXT-ALIGN: center; MARGIN: 0px auto 10px; WIDTH: 200px; DISPLAY: block; HEIGHT: 132px; CURSOR: hand" id="BLOGGER_PHOTO_ID_5319742102056529378" border="0" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgHSlNW-gRPZIjz-5mmw0B8KZc9o22xAK4OY-9x3lvBzI-4qDcG9TKqR9t5jY0zR5E7hfQy1chcrKPuXQF4s-iJAjKfHaWz65YhllcI6Gi5bnG_0oRQv2ESuw32iAYomXDaYOKCOvCsyZw/s200/7.JPG" /> </p><p><span style="font-family:Comic Sans MS;">You will see the objects and queries to be migrated. Here If there are any errors or discrepencies will also notified. </span><br /><img style="TEXT-ALIGN: center; MARGIN: 0px auto 10px; WIDTH: 200px; DISPLAY: block; HEIGHT: 128px; CURSOR: hand" id="BLOGGER_PHOTO_ID_5319742148625319906" border="0" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhSlM9dlh-DIV6rfHiKaDfMjbY0i2bVK4kmyze-kklO0fFkfrKSByGijEWptI1JQW5vanuSzzv2-Oq0t86MF3VwVP_lVeZH3yroDs306ZmItp1G7LBuL4A51UPTls7iwW-NSTDxk1Fjt2o/s200/8.JPG" /><span style="font-family:Comic Sans MS;">Click Ok to complete migration. </span></p><p></p><p><img style="TEXT-ALIGN: center; MARGIN: 0px auto 10px; WIDTH: 200px; DISPLAY: block; HEIGHT: 132px; CURSOR: hand" id="BLOGGER_PHOTO_ID_5319742201246185250" border="0" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjeeqA5Bf_3czN5_uhiPF1NATmbCO_er6MQw4KxNKhb57b5qNcv5ke2xabIoz2wet1DGth60tU8RYtF40UjUrdHDHcsKq127NAX_PVmS5iPf5BTrtaB-jyC37vE2tcteH7dm1g0kKBz_4U/s200/9.JPG" /> </p><p><span style="font-family:Comic Sans MS;">Now, you can open your SQL Server database to see that the Access tables and Queries are migrated. The Queries will be migrated as Views and you can use them as you did in the Access tables. The migration task is simple than expected. Now the next task that you will learn from me on this is HOW TO MIGRATE ACCESS REPORTS TO SQL SERVER? </span></p><p></p>SugeshKumar Rajendranhttp://www.blogger.com/profile/10383062145145324874noreply@blogger.com1tag:blogger.com,1999:blog-7367571059608809880.post-12337301908365921312009-03-28T12:31:00.002-04:002009-03-28T12:33:28.462-04:00Database Size Info<span xmlns=""><p>declare @dbname sysname<br /></p><p>set nocount on<br /></p><p>if @dbname is not null and @dbname not in (select name from sysdatabases (nolock))<br /></p><p> begin<br /></p><p> raiserror('Incorrect database name. ', 16, 1)<br /></p><p> end<br /></p><p>create table #datafilestats<br /></p><p>( dbname varchar(25),<br /></p><p> flag bit default 0,<br /></p><p> Fileid tinyint,<br /></p><p> [FileGroup] tinyint,<br /></p><p> TotalExtents dec (8, 1),<br /></p><p> UsedExtents dec (8, 1),<br /></p><p> [Name] varchar(50),<br /></p><p> [FileName] sysname )<br /></p><p>declare @string sysname, @dbname1 sysname<br /></p><p>set @string = ''<br /></p><p>if @dbname is not null<br /></p><p> set @dbname1 = @dbname<br /></p><p>else<br /></p><p> set @dbname1 = ''<br /></p><p>set @dbname = ''<br /></p><p>while 1=1<br /></p><p> begin<br /></p><p> select top 1 @dbname = name from master..sysdatabases where name > @dbname --order by name asc <br /></p><p> if @@rowcount = 0<br /></p><p> break<br /></p><p> set @string = 'use ' + @dbname + ' DBCC SHOWFILESTATS with no_infomsgs'<br /></p><p> insert into #datafilestats (Fileid, [FileGroup] , TotalExtents , UsedExtents , [Name] , [FileName]) exec (@string)<br /></p><p> update #datafilestats set dbname = @dbname, flag = 1 where flag = 0<br /></p><p> update #datafilestats set TotalExtents = (select sum(TotalExtents)*8*8192.0/1048576.0 from #datafilestats where dbname = @dbname)<br /></p><p> where flag = 1 and Fileid = 1 and FileGroup = 1 and dbname = @dbname<br /></p><p> update #datafilestats set UsedExtents = (select sum(UsedExtents)*8*8192.0/1048576.0 from #datafilestats where dbname = @dbname)<br /></p><p> where flag = 1 and Fileid = 1 and FileGroup = 1 and dbname = @dbname<br /></p><p> end<br /></p><p>create table #sizeinfo <br /></p><p>( db_name varchar(30) not null primary key clustered,<br /></p><p> total dec (7, 1),<br /></p><p> data dec (7, 1),<br /></p><p> data_used dec (7, 1),<br /></p><p> [data (%)] dec (7, 1),<br /></p><p> data_free dec (7, 1),<br /></p><p> [data_free (%)] dec (7, 1),<br /></p><p> log dec (7, 1),<br /></p><p> log_used dec (7, 1),<br /></p><p> [log (%)] dec (7, 1),<br /></p><p> log_free dec (7, 1),<br /></p><p> [log_free (%)] dec (7, 1),<br /></p><p> status dec (7, 1) ) <br /></p><p>insert #sizeinfo (db_name, log, [log (%)], status ) exec ('dbcc sqlperf(logspace) with no_infomsgs')<br /></p><p>update #sizeinfo set<br /></p><p> data = d.TotalExtents from #datafilestats d join #sizeinfo s on d.dbname = s.db_name where d.flag = 1 and d.Fileid = 1 and d.FileGroup = 1<br /></p><p>update #sizeinfo set<br /></p><p> data_used = d.UsedExtents from #datafilestats d join #sizeinfo s on d.dbname = s.db_name where d.flag = 1 and d.Fileid = 1 and d.FileGroup = 1<br /></p><p>update #sizeinfo set<br /></p><p> total = (data + log)<br /></p><p>update #sizeinfo set<br /></p><p> [data (%)] = (data_used * 100.0 / data)<br /></p><p>update #sizeinfo set<br /></p><p> data_free = (data - data_used)<br /></p><p>update #sizeinfo set<br /></p><p> [data_free (%)] = (100 - [data (%)])<br /></p><p>update #sizeinfo set<br /></p><p> log_used = (log * [log (%)] / 100.0)<br /></p><p>update #sizeinfo set<br /></p><p> log_free = (log - log_used)<br /></p><p>update #sizeinfo set<br /></p><p> [log_free (%)] = (log_free * 100.0 / log)<br /></p><p>print ''<br /></p><p>if @dbname1 = ''<br /></p><p>begin<br /></p><p> print 'Database size report on ' + @@servername + ' as of ' + convert(varchar(30), getdate(), 100) + char(10)<br /></p><p> select db_name,<br /></p><p> total,<br /></p><p> data,<br /></p><p> data_used,<br /></p><p> [data (%)],<br /></p><p> data_free,<br /></p><p> [data_free (%)],<br /></p><p> log,<br /></p><p> log_used,<br /></p><p> [log (%)],<br /></p><p> log_free,<br /></p><p> [log_free (%)]<br /></p><p> from #sizeinfo order by db_name asc compute sum(total) <br /></p><p>end<br /></p><p>else<br /></p><p>begin<br /></p><p>print 'Database size report on ' + @@servername + '.' + @dbname1 + ' as of ' + convert(varchar(30), getdate(), 100) + char(10)<br /></p><p> select db_name,<br /></p><p> total,<br /></p><p> data,<br /></p><p> data_used,<br /></p><p> [data (%)],<br /></p><p> data_free,<br /></p><p> [data_free (%)],<br /></p><p> log,<br /></p><p> log_used,<br /></p><p> [log (%)],<br /></p><p> log_free,<br /></p><p> [log_free (%)]<br /></p><p> from #sizeinfo where db_name = @dbname1 <br /></p><p>end<br /></p><p>go<br /></p><p>drop table #datafilestats <br /></p><p>drop table #sizeinfo</p></span>SugeshKumar Rajendranhttp://www.blogger.com/profile/10383062145145324874noreply@blogger.com0