tag:blogger.com,1999:blog-36821778396387289272024-03-10T13:54:43.579+05:30SQLMS SQL and .net DevelopmentAnonymoushttp://www.blogger.com/profile/09453699184523060639noreply@blogger.comBlogger778125tag:blogger.com,1999:blog-3682177839638728927.post-73983760745291761972017-07-24T11:35:00.000+05:302017-07-24T11:35:39.383+05:30An instance with the same name is already installed on this computer. To proceed with SQL Server setup, provide a unique instance name.<div dir="ltr" style="text-align: left;" trbidi="on">
<span style="font-family: "arial" , "helvetica" , sans-serif;">You may get below error while installing SQL Server.</span><br />
<span style="font-family: "arial" , "helvetica" , sans-serif;"><br /></span>
<span style="font-family: "arial" , "helvetica" , sans-serif;">An instance with the same name is already installed on this computer. To proceed with SQL Server setup, provide a unique instance name.</span><br />
<span style="font-family: "arial" , "helvetica" , sans-serif;"><br /></span>
<span style="font-family: "arial" , "helvetica" , sans-serif;">The main reason of above error is:</span><br />
<span style="font-family: "arial" , "helvetica" , sans-serif;"><br /></span>
<span style="font-family: "arial" , "helvetica" , sans-serif;">The named Instance, you are trying to install is already present.</span><br />
<span style="font-family: "arial" , "helvetica" , sans-serif;">The existing instance of SQL Server 2008 is a unique named instance. However, you try to install SQL Server 2005 as the default instance.</span><br />
<span style="font-family: "arial" , "helvetica" , sans-serif;">The existing instance of SQL Server 2008 is a named instance, and you use the same name when you try to install SQL Server 2005 as a named instance.</span><br />
<span style="font-family: "arial" , "helvetica" , sans-serif;"><br /></span>
<span style="font-family: "arial" , "helvetica" , sans-serif;">Follow below steps to solve the error (Suppose you want to create instance name SQLExpress:</span><br />
<span style="font-family: "arial" , "helvetica" , sans-serif;">Click Start menu, select Run and type regedit</span><br />
<span style="font-family: "arial" , "helvetica" , sans-serif;">Navigate to</span><br />
<span style="font-family: "arial" , "helvetica" , sans-serif;">HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\</span><br />
<span style="font-family: "arial" , "helvetica" , sans-serif;"> Find “MSSQL$SQLExpress” , and delete</span><br />
<span style="font-family: "arial" , "helvetica" , sans-serif;">Go to HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server</span><br />
<span style="font-family: "arial" , "helvetica" , sans-serif;"> Remove "SQLExpress" from the REG_MULTI_SZ value named InstalledInstance</span><br />
<span style="font-family: "arial" , "helvetica" , sans-serif;"> Delete the subhive named MSSQL.1</span><br />
<span style="font-family: "arial" , "helvetica" , sans-serif;"> Delete the subhive named SQLExpress</span><br />
<span style="font-family: "arial" , "helvetica" , sans-serif;">Go to HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\Instance Names\SQL</span><br />
<span style="font-family: "arial" , "helvetica" , sans-serif;"> Delete the value named "SQLExpress"</span><br />
<span style="font-family: "arial" , "helvetica" , sans-serif;">Go to SQL Server installation drive Delete the folder %ProgramFiles%\Microsoft SQL Server\MSSQL.1</span><br />
<span style="font-family: "arial" , "helvetica" , sans-serif;">Restart the PC.</span><br />
<span style="font-family: "arial" , "helvetica" , sans-serif;"><br /></span>
<span style="font-family: "arial" , "helvetica" , sans-serif;">Use either Windows installer cleanup utility to manually remove all traces of MSSQL in your PC.</span><br />
<span style="font-family: "arial" , "helvetica" , sans-serif;"><br /></span>
<span style="font-family: "arial" , "helvetica" , sans-serif;">Re-run SQL installation</span><br />
<span style="font-family: "arial" , "helvetica" , sans-serif;"><br /></span>
<span style="font-family: "arial" , "helvetica" , sans-serif;">Alternatively, you can try installing SQL Server with different instance name.</span><br />
<div>
<br /></div>
</div>
Anonymoushttp://www.blogger.com/profile/09453699184523060639noreply@blogger.com4tag:blogger.com,1999:blog-3682177839638728927.post-27779943577665683062017-04-17T17:50:00.000+05:302017-04-17T17:50:11.863+05:30A non recoverable I/O error occurred on file while taking backup.<div dir="ltr" style="text-align: left;" trbidi="on">
<span style="font-family: "arial" , "helvetica" , sans-serif;">You may get this error while taking backup of databse. </span><br />
<span style="font-family: "arial" , "helvetica" , sans-serif;">The reason for this error is insufficient disk space.</span><br />
<span style="font-family: "arial" , "helvetica" , sans-serif;">The solution is either free up some space or change the backup folder.</span><br />
<div>
<br /></div>
</div>
Anonymoushttp://www.blogger.com/profile/09453699184523060639noreply@blogger.com0tag:blogger.com,1999:blog-3682177839638728927.post-29895351899181629302016-12-02T12:46:00.000+05:302017-07-24T11:35:11.629+05:30The media family on device '' is incorrectly formed. SQL Server cannot process this media family. RESTORE FILELIST is terminating abnormally.<div dir="ltr" style="text-align: left;" trbidi="on">
<span style="font-family: "arial" , "helvetica" , sans-serif;"><b>Error:</b></span><br />
<span style="font-family: "arial" , "helvetica" , sans-serif;"><br /></span>
<span style="font-family: "arial" , "helvetica" , sans-serif;">The media family on device ‘<FILENAME.BAK>’ is incorrectly formed. SQL Server cannot process this media family. RESTORE FILELIST is terminating abnormally.</span><br />
<span style="font-family: "arial" , "helvetica" , sans-serif;"><br /></span>
<span style="font-family: "arial" , "helvetica" , sans-serif;"><b>Solution:</b></span><br />
<span style="font-family: "arial" , "helvetica" , sans-serif;"><br /></span>
<span style="font-family: "arial" , "helvetica" , sans-serif;">Ensure that backup file is good and copied properly.</span><br />
<span style="font-family: "arial" , "helvetica" , sans-serif;">You can not restore higher version databse Backup in lower version SQL Server.</span></div>
Anonymoushttp://www.blogger.com/profile/09453699184523060639noreply@blogger.com0tag:blogger.com,1999:blog-3682177839638728927.post-87852007279832366992016-10-21T14:53:00.000+05:302016-10-21T14:53:31.889+05:30Error occurred during installation: Error 1618 installing Microsoft SQL Server setup Support files.<div dir="ltr" style="text-align: left;" trbidi="on">
<span style="font-family: "arial" , "helvetica" , sans-serif;">You may get below error during SQL server installation:</span><br />
<span style="font-family: "arial" , "helvetica" , sans-serif;"><br /></span>
<span style="font-family: "arial" , "helvetica" , sans-serif;">Error occurred during installation: Error 1618 installing Microsoft SQL Server setup Support files.</span><br />
<span style="font-family: "arial" , "helvetica" , sans-serif;"><br /></span>
<span style="font-family: "arial" , "helvetica" , sans-serif;">Follow below steps to solve the error:</span><br />
<br />
<ol style="text-align: left;">
<li><span style="font-family: "arial" , "helvetica" , sans-serif;">Restart The system and try installing.</span></li>
<li><span style="font-family: "arial" , "helvetica" , sans-serif;">Start - Run - Type: gpedit.msc - OK - Navigate to</span></li>
</ol>
<br />
<span style="font-family: "arial" , "helvetica" , sans-serif;">Computer Configuration\Windows Settings\Security Settings\Local Policies\User Rights Assignment\Debug programs</span><br />
<span style="font-family: "arial" , "helvetica" , sans-serif;">Add administrator user.</span></div>
Anonymoushttp://www.blogger.com/profile/09453699184523060639noreply@blogger.com2tag:blogger.com,1999:blog-3682177839638728927.post-81704844604619692302016-05-30T09:33:00.000+05:302016-05-30T09:33:24.807+05:30Login failed for user ‘domain\username’. Reason: Server is in single user mode. Only one administrator can connect at this time.<div dir="ltr" style="text-align: left;" trbidi="on">
<span style="font-family: "arial" , "helvetica" , sans-serif;">You may get below error some time after improper shutdown of system.</span><br />
<span style="font-family: "arial" , "helvetica" , sans-serif;"><br /></span>
<span style="font-family: "arial" , "helvetica" , sans-serif;">Login failed for user ‘domain\username’. Reason: Server is in single user mode. Only one administrator can connect at this time.</span><br />
<span style="font-family: "arial" , "helvetica" , sans-serif;"><br /></span>
<span style="font-family: "arial" , "helvetica" , sans-serif;">Restart SQL server and Browser and problem will be solved.</span></div>
Anonymoushttp://www.blogger.com/profile/09453699184523060639noreply@blogger.com0tag:blogger.com,1999:blog-3682177839638728927.post-8169857595997120002016-02-18T19:02:00.000+05:302016-02-18T19:02:04.106+05:30Query to get IP address of SQL Server<div dir="ltr" style="text-align: left;" trbidi="on">
<span style="font-family: "arial" , "helvetica" , sans-serif;">Use below query to get IP address of SQL Server:</span><br />
<span style="font-family: "arial" , "helvetica" , sans-serif;"><br /></span>
<span style="font-family: "arial" , "helvetica" , sans-serif;"><b>DECLARE @IP varchar(40)</b></span><br />
<span style="font-family: "arial" , "helvetica" , sans-serif;"><b>DECLARE @IPLine varchar(200)</b></span><br />
<span style="font-family: "arial" , "helvetica" , sans-serif;"><b>DECLARE @Pos int</b></span><br />
<span style="font-family: "arial" , "helvetica" , sans-serif;"><b><br /></b></span>
<span style="font-family: "arial" , "helvetica" , sans-serif;"><b>SET NoCount ON</b></span><br />
<span style="font-family: "arial" , "helvetica" , sans-serif;"><b>SET @IP = NULL</b></span><br />
<span style="font-family: "arial" , "helvetica" , sans-serif;"><b><br /></b></span>
<span style="font-family: "arial" , "helvetica" , sans-serif;"><b>Create Table #Temp (IPLine VarChar(200))</b></span><br />
<span style="font-family: "arial" , "helvetica" , sans-serif;"><b>INSERT #temp EXEC master..xp_cmdshell 'IPconfig'</b></span><br />
<span style="font-family: "arial" , "helvetica" , sans-serif;"><b><br /></b></span>
<span style="font-family: "arial" , "helvetica" , sans-serif;"><b>SELECT TOP 1 @IPLine = IPLine FROM #Temp</b></span><br />
<span style="font-family: "arial" , "helvetica" , sans-serif;"><b>WHERE Upper (IPLine) LIKE '%IP ADDRESS%' OR Upper (IPLine) LIKE '%IPV4 ADDRESS%' </b></span><br />
<span style="font-family: "arial" , "helvetica" , sans-serif;"><b> IF (ISNULL (@IPLine,'***') != '***')</b></span><br />
<span style="font-family: "arial" , "helvetica" , sans-serif;"><b> BEGIN </b></span><br />
<span style="font-family: "arial" , "helvetica" , sans-serif;"><b> SET @Pos = CharIndex (':',@IPLine,1);</b></span><br />
<span style="font-family: "arial" , "helvetica" , sans-serif;"><b> SET @IP = LTrim(RTrim(SubString (@IPLine , @Pos + 1 ,len (@IPLine) - @Pos)))</b></span><br />
<span style="font-family: "arial" , "helvetica" , sans-serif;"><b> END </b></span><br />
<span style="font-family: "arial" , "helvetica" , sans-serif;"><b>PRINT @IP</b></span><br />
<span style="font-family: "arial" , "helvetica" , sans-serif;"><b>DROP TABLE #temp</b></span><br />
<span style="font-family: "arial" , "helvetica" , sans-serif;"><b>SET NoCount OFF</b></span><br />
<div>
<br /></div>
<span style="font-family: "arial" , "helvetica" , sans-serif;">If you get below error after executing above query:</span><br />
<span style="font-family: "arial" , "helvetica" , sans-serif;"><br /></span>
<b><span style="font-family: "arial" , "helvetica" , sans-serif;">Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1</span></b><br />
<b><span style="font-family: "arial" , "helvetica" , sans-serif;">SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', see "Surface Area Configuration" in SQL Server Books Online.</span></b><br />
<b><span style="font-family: "arial" , "helvetica" , sans-serif;"><br /></span></b>
<span style="font-family: "arial" , "helvetica" , sans-serif;">Than you had to enable <b>xp_cmdshell </b>in surface area configuration. and than rerun the above query.</span><br />
<br /></div>
Anonymoushttp://www.blogger.com/profile/09453699184523060639noreply@blogger.com0tag:blogger.com,1999:blog-3682177839638728927.post-4465588847482812282016-02-08T09:53:00.000+05:302016-02-08T09:53:15.774+05:30How to show backup or restore progress to user in a progressbar?<div dir="ltr" style="text-align: left;" trbidi="on">
<span style="font-family: "arial" , "helvetica" , sans-serif;">You can show backup or restore progress to user in a progress bar if you had installed </span><br />
<span style="font-family: "arial" , "helvetica" , sans-serif;"><br /></span>
<span style="font-family: "arial" , "helvetica" , sans-serif;"><b>sp_who2k5 </b></span><br />
<span style="font-family: "arial" , "helvetica" , sans-serif;"><br /></span>
<span style="font-family: "arial" , "helvetica" , sans-serif;">into your master database. You have to run the below command:</span><br />
<span style="font-family: "arial" , "helvetica" , sans-serif;"><b><br /></b></span>
<span style="font-family: "arial" , "helvetica" , sans-serif;"><b>sp_who2k5 1, 1</b></span><br />
<span style="font-family: "arial" , "helvetica" , sans-serif;"><br /></span>
<span style="font-family: "arial" , "helvetica" , sans-serif;">The result will include all active transaction. Search the backup in the requestCommand field. The aptly named percentComplete field will give you the progress of the backup. </span><br />
<div>
<br /></div>
</div>
Anonymoushttp://www.blogger.com/profile/09453699184523060639noreply@blogger.com1tag:blogger.com,1999:blog-3682177839638728927.post-41338343600328631332016-02-01T09:58:00.000+05:302016-02-01T09:58:00.484+05:30The SQL Server system configuration checker cannot be executed due to WMI configuration on the machine.<div dir="ltr" style="text-align: left;" trbidi="on">
<span style="font-family: "arial" , "helvetica" , sans-serif;">Last week I got below error in clients system:</span><br />
<span style="font-family: "arial" , "helvetica" , sans-serif;"><br /></span> <span style="font-family: "arial" , "helvetica" , sans-serif;">The SQL Server system configuration checker cannot be executed due to WMI configuration on the machine. Error 2147749896</span><br />
<span style="font-family: "arial" , "helvetica" , sans-serif;"><br /></span> <span style="font-family: "arial" , "helvetica" , sans-serif;">The main cause of the problem are:</span><br />
<span style="font-family: "arial" , "helvetica" , sans-serif;"><br /></span> <span style="font-family: "arial" , "helvetica" , sans-serif;">Unfinished SQL Server components may be present.</span><br />
<span style="font-family: "arial" , "helvetica" , sans-serif;">Components or files related to WMI service might be unregistered or missing.</span><br />
<span style="font-family: "arial" , "helvetica" , sans-serif;">Microsoft Data Access Components may not be installed.</span><br />
<span style="font-family: "arial" , "helvetica" , sans-serif;">The problem was that the WMI repository is corrupted. Reinstall the WMI.</span><br />
<span style="font-family: "arial" , "helvetica" , sans-serif;"><br /></span> <span style="font-family: "arial" , "helvetica" , sans-serif;">Solution:</span><br />
<span style="font-family: "arial" , "helvetica" , sans-serif;">Check the version of MDAC you have installed. SQL Server will require MDAC 2.8 or higher. To check MDAC:</span><br />
<span style="font-family: "arial" , "helvetica" , sans-serif;">Click Start / Run. Type regedit. Click OK.</span><br />
<span style="font-family: "arial" , "helvetica" , sans-serif;">Navigate to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\DataAccess</span><br />
<span style="font-family: "arial" , "helvetica" , sans-serif;">Note the Registry Key for Version. If less than 2.8.xxxx,</span><br />
<span style="font-family: "arial" , "helvetica" , sans-serif;">download and install the latest MDAC 2.8 version.</span><br />
<div>
<br /></div>
</div>
Anonymoushttp://www.blogger.com/profile/09453699184523060639noreply@blogger.com0tag:blogger.com,1999:blog-3682177839638728927.post-44435715193862264962016-01-11T10:30:00.000+05:302016-01-11T10:30:27.262+05:30Error: 64, connection was successfully established with the server, but then an error occurred during the pre-login handshake.<div dir="ltr" style="text-align: left;" trbidi="on">
<span style="font-family: Arial, Helvetica, sans-serif;">I had got below error in my friend's system:</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="background-color: white; color: #141412; line-height: 24px;"><span style="font-family: Arial, Helvetica, sans-serif;">A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 – The specified network name is no longer available.) (Microsoft SQL Server, Error: 64)</span></span><br />
<span style="background-color: white; color: #141412; line-height: 24px;"><span style="font-family: Arial, Helvetica, sans-serif;"><br /></span></span>
<span style="background-color: white; color: #141412; line-height: 24px;"><span style="font-family: Arial, Helvetica, sans-serif;">I googled this error and got mixed results and solutions but none solved my problem.</span></span><br />
<span style="background-color: white; color: #141412; line-height: 24px;"><span style="font-family: Arial, Helvetica, sans-serif;"><br /></span></span>
<br />
<div style="background-color: white; box-sizing: border-box; color: #141412; line-height: 24px; margin-bottom: 24px;">
<span style="font-family: Arial, Helvetica, sans-serif;">After some more googling I found below solution that worked for me.</span></div>
<div style="background-color: white; box-sizing: border-box; color: #141412; line-height: 24px; margin-bottom: 24px;">
<span style="font-family: Arial, Helvetica, sans-serif;">The solution is:</span></div>
<div style="background-color: white; box-sizing: border-box; color: #141412; line-height: 24px; margin-bottom: 24px;">
<span style="font-family: Arial, Helvetica, sans-serif;">Go to the MSSQL Configuration Manager and expand the SQL Network Configuration and click on the PROTOCOLS node. Right click on TCP/IP and open up the PROPERTIES panel and Select the IP ADDRESS tab.</span></div>
<div style="background-color: white; box-sizing: border-box; color: #141412; line-height: 24px; margin-bottom: 24px;">
<span style="font-family: Arial, Helvetica, sans-serif;">Check the IP ADDRESS field's value are correct and match the system it is running on.</span></div>
<div style="background-color: white; box-sizing: border-box; color: #141412; line-height: 24px; margin-bottom: 24px;">
<span style="font-family: Arial, Helvetica, sans-serif;">Restart the service, and the problem is solved.</span></div>
<div style="background-color: white; box-sizing: border-box; color: #141412; line-height: 24px; margin-bottom: 24px;">
<span style="font-family: Arial, Helvetica, sans-serif;">Make sure you fill in the TCP PORT, even if you are using the default 1433.</span></div>
</div>
Anonymoushttp://www.blogger.com/profile/09453699184523060639noreply@blogger.com11tag:blogger.com,1999:blog-3682177839638728927.post-8497770427694075352016-01-04T10:06:00.000+05:302016-01-04T10:06:36.166+05:30Error occurred during database creation<div dir="ltr" style="text-align: left;" trbidi="on">
<span style="font-family: Arial, Helvetica, sans-serif;">Sometime when you create a new database by using CREATE Database statement in SSMS (SQL Server Management Studio), you may got below error message and you are not able to create Database:</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;"><b>Server: Msg 5105, Level 16, State 2, Line 1</b></span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><b>Device activation error. The physical file name '' may be incorrect.</b></span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><b><br /></b></span>
<span style="font-family: Arial, Helvetica, sans-serif;"><b>Server: Msg 1802, Level 16, State 1, Line 1</b></span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><b>CREATE DATABASE failed. Some file names listed could not be created. Check previous errors. </b></span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">The main reason of above error is path of folder store in the registry values does not exist. When the path of folder is incorrect then MS SQL server throws above error messages.</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">You can fix the error by using SSMS. SSMS is able to set the default directory value for Data and log Files. Follow below steps to change the default value: </span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<br />
<ul style="text-align: left;">
<li><span style="font-family: Arial, Helvetica, sans-serif;">Start SQL server Management Studio.</span></li>
<li><span style="font-family: Arial, Helvetica, sans-serif;">In Management Studio, right click on your instance and select properties</span></li>
<li><span style="font-family: Arial, Helvetica, sans-serif;">In properties, click on the database setting option.</span></li>
<li><span style="font-family: Arial, Helvetica, sans-serif;">Now go to the new database default location section</span></li>
<li><span style="font-family: Arial, Helvetica, sans-serif;">Correct folder path from default directory box and default log directory box</span></li>
<li><span style="font-family: Arial, Helvetica, sans-serif;">Press OK</span></li>
<li><span style="font-family: Arial, Helvetica, sans-serif;">Stop the instance of MS SQL database</span></li>
<li><span style="font-family: Arial, Helvetica, sans-serif;">Restart the instance of MS SQL server database.</span></li>
</ul>
<div>
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span></div>
</div>
Anonymoushttp://www.blogger.com/profile/09453699184523060639noreply@blogger.com0tag:blogger.com,1999:blog-3682177839638728927.post-14610842057562040882015-11-02T09:58:00.000+05:302015-11-02T09:58:14.090+05:30List database objects modified in the last ‘X’ days<div dir="ltr" style="text-align: left;" trbidi="on">
<span style="font-family: Arial, Helvetica, sans-serif;">Use below query to get the list of databases modified in last 'X' days:</span><br />
<b><span style="font-family: Arial, Helvetica, sans-serif;"><br /></span></b>
<b><span style="font-family: Arial, Helvetica, sans-serif;">USE <database_Name>; </span></b><br />
<b><span style="font-family: Arial, Helvetica, sans-serif;">GO </span></b><br />
<b><span style="font-family: Arial, Helvetica, sans-serif;">SELECT Name AS object_Name, </span></b><br />
<b><span style="font-family: Arial, Helvetica, sans-serif;"> Create_Date, Modify_Date, </span></b><br />
<b><span style="font-family: Arial, Helvetica, sans-serif;"> Type_Desc,</span></b><br />
<b><span style="font-family: Arial, Helvetica, sans-serif;"> SCHEMA_Name(Schema_ID) AS Schema_Name </span></b><br />
<b><span style="font-family: Arial, Helvetica, sans-serif;">FROM sys.Objects </span></b><br />
<b><span style="font-family: Arial, Helvetica, sans-serif;">WHERE Modify_Date > GETDate() - <X_Days> </span></b><br />
<b><span style="font-family: Arial, Helvetica, sans-serif;">ORDER BY Modify_Date; </span></b><br />
<b><span style="font-family: Arial, Helvetica, sans-serif;">GO </span></b><br />
<b><span style="font-family: Arial, Helvetica, sans-serif;"><br /></span></b>
<span style="font-family: Arial, Helvetica, sans-serif;">Notes: Replace <database_Name> with Database Name and <X_Days> with No of days.</span></div>
Anonymoushttp://www.blogger.com/profile/09453699184523060639noreply@blogger.com0tag:blogger.com,1999:blog-3682177839638728927.post-53882764270605687772015-09-14T09:41:00.000+05:302015-09-14T09:41:24.338+05:30Weekend count between two dates<div dir="ltr" style="text-align: left;" trbidi="on">
<span style="font-family: Arial, Helvetica, sans-serif;">Use below query to find the weekend count between two dates:</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<b><span style="font-family: Arial, Helvetica, sans-serif;">DECLARE @DateFrom DateTime, @DateTo DateTime, @Total int, @Number int, @Counter int</span></b><br />
<b><span style="font-family: Arial, Helvetica, sans-serif;">SELECT @DateTo = GetDate(), @DateFrom = GetDate() - 22, @Total = DateDiff(dd, @DateFrom, @DateTo), @Number = 1, @Counter = 0</span></b><br />
<b><span style="font-family: Arial, Helvetica, sans-serif;">WHILE (@Number <= @Total)</span></b><br />
<b><span style="font-family: Arial, Helvetica, sans-serif;">BEGIN</span></b><br />
<b><span style="font-family: Arial, Helvetica, sans-serif;">IF DatePart(dw, @DateFrom) = 1 OR DatePart(dw, @DateFrom) = 7</span></b><br />
<b><span style="font-family: Arial, Helvetica, sans-serif;">BEGIN</span></b><br />
<b><span style="font-family: Arial, Helvetica, sans-serif;">SET @Counter = @Counter +1</span></b><br />
<b><span style="font-family: Arial, Helvetica, sans-serif;">END</span></b><br />
<b><span style="font-family: Arial, Helvetica, sans-serif;">SET @DateFrom = @DateFrom+1</span></b><br />
<b><span style="font-family: Arial, Helvetica, sans-serif;">SET @Number = @Number + 1</span></b><br />
<b><span style="font-family: Arial, Helvetica, sans-serif;">END</span></b><br />
<b><span style="font-family: Arial, Helvetica, sans-serif;">PRINT @Counter</span></b><br />
<b><span style="font-family: Arial, Helvetica, sans-serif;"><br /></span></b></div>
Anonymoushttp://www.blogger.com/profile/09453699184523060639noreply@blogger.com0tag:blogger.com,1999:blog-3682177839638728927.post-58556559191466558212015-08-24T09:46:00.000+05:302015-08-24T09:46:16.995+05:30Find LOB Columns Script<div dir="ltr" style="text-align: left;" trbidi="on">
<div style="background-color: white; padding: 0px 0px 15px;">
<span style="font-family: Arial, Helvetica, sans-serif;"><span style="line-height: 20px;">Sometime you want to identify the tables which could not reindex online. For this you had to scan all tables in SQL Server database and list the columns which are large objects (VarChar(MAX), NVarChar(MAX), XML, VarBinary, Text, NText, Image).</span></span></div>
<div style="background-color: white; padding: 0px 0px 15px;">
<span style="line-height: 20px;"><span style="font-family: Arial, Helvetica, sans-serif;">There are various method to get above information:</span></span></div>
<div style="background-color: white; padding: 0px 0px 15px;">
<span style="font-family: Arial, Helvetica, sans-serif;"><span style="line-height: 20px;"><b><u>Method 1</u></b></span></span></div>
<div style="background-color: white; padding: 0px 0px 15px;">
<b style="font-family: Arial, Helvetica, sans-serif; line-height: 20px;">SELECT * FROM Information_Schema.Columns</b></div>
<div style="background-color: white; padding: 0px 0px 15px;">
<span style="font-family: Arial, Helvetica, sans-serif;"><span style="line-height: 20px;"><b>WHERE Table_Name IN </b></span></span></div>
<div style="background-color: white; padding: 0px 0px 15px;">
<span style="font-family: Arial, Helvetica, sans-serif;"><span style="line-height: 20px;"><b> (SELECT Table_Name </b></span></span></div>
<div style="background-color: white; padding: 0px 0px 15px;">
<span style="font-family: Arial, Helvetica, sans-serif;"><span style="line-height: 20px;"><b> FROM Information_Schema.Tables </b></span></span></div>
<div style="background-color: white; padding: 0px 0px 15px;">
<span style="font-family: Arial, Helvetica, sans-serif;"><span style="line-height: 20px;"><b> WHERE Table_Type = 'Base Table')</b></span></span></div>
<div style="background-color: white; padding: 0px 0px 15px;">
<span style="font-family: Arial, Helvetica, sans-serif;"><span style="line-height: 20px;"><b>AND DATA_TYPE IN ('VarChar', 'NVarChar', 'VarBinary', 'Text', 'NText', 'Image', 'XML')</b></span></span></div>
<div style="background-color: white; padding: 0px 0px 15px;">
<span style="font-family: Arial, Helvetica, sans-serif;"><span style="line-height: 20px;"><b>AND Character_Maximum_Length = -1</b></span></span></div>
<div style="background-color: white; padding: 0px 0px 15px;">
<span style="font-family: Arial, Helvetica, sans-serif;"><span style="line-height: 20px;"><b>ORDER BY Table_Name</b></span></span></div>
<div style="background-color: white; padding: 0px 0px 15px;">
<span style="font-family: Arial, Helvetica, sans-serif;"><span style="line-height: 20px;"><b><u><br /></u></b></span></span></div>
<div style="background-color: white; padding: 0px 0px 15px;">
<span style="font-family: Arial, Helvetica, sans-serif;"><span style="line-height: 20px;"><b><u>Method 2</u></b></span></span></div>
<div style="background-color: white; padding: 0px 0px 15px;">
<b style="font-family: Arial, Helvetica, sans-serif; line-height: 20px;">SELECT </b></div>
<div style="background-color: white; padding: 0px 0px 15px;">
<span style="font-family: Arial, Helvetica, sans-serif;"><span style="line-height: 20px;"><b> C.Object_ID, </b></span></span></div>
<div style="background-color: white; padding: 0px 0px 15px;">
<span style="font-family: Arial, Helvetica, sans-serif;"><span style="line-height: 20px;"><b> Object_Name(C.Object_ID) AS [Object Name], </b></span></span></div>
<div style="background-color: white; padding: 0px 0px 15px;">
<span style="font-family: Arial, Helvetica, sans-serif;"><span style="line-height: 20px;"><b> C.Name AS [Column Name], </b></span></span></div>
<div style="background-color: white; padding: 0px 0px 15px;">
<span style="font-family: Arial, Helvetica, sans-serif;"><span style="line-height: 20px;"><b> T.Name AS [Column Type]</b></span></span></div>
<div style="background-color: white; padding: 0px 0px 15px;">
<span style="font-family: Arial, Helvetica, sans-serif;"><span style="line-height: 20px;"><b>FROM Sys.Columns C</b></span></span></div>
<div style="background-color: white; padding: 0px 0px 15px;">
<span style="font-family: Arial, Helvetica, sans-serif;"><span style="line-height: 20px;"><b>INNER JOIN Sys.Types T ON C.System_Type_ID = T.System_Type_ID </b></span></span></div>
<div style="background-color: white; padding: 0px 0px 15px;">
<span style="font-family: Arial, Helvetica, sans-serif;"><span style="line-height: 20px;"><b>WHERE C.Object_ID IN (SELECT Object_ID FROM Sys.Objects WHERE Type_Desc = 'User_Table')</b></span></span></div>
<div style="background-color: white; padding: 0px 0px 15px;">
<span style="font-family: Arial, Helvetica, sans-serif;"><span style="line-height: 20px;"><b>AND C.max_length = -1</b></span></span></div>
<div style="background-color: white; padding: 0px 0px 15px;">
<span style="font-family: Arial, Helvetica, sans-serif;"><span style="line-height: 20px;"><b>AND T.Name IN ('VarChar', 'NVarChar', 'VarBinary', 'Text', 'NText', 'Image', 'XML')</b></span></span></div>
<div>
<br /></div>
</div>
Anonymoushttp://www.blogger.com/profile/09453699184523060639noreply@blogger.com0tag:blogger.com,1999:blog-3682177839638728927.post-87002949000638629032015-08-17T09:51:00.000+05:302015-08-17T09:51:18.428+05:30Unable to modify table.<div dir="ltr" style="text-align: left;" trbidi="on">
<span style="background-color: white;"><span style="font-family: Arial, Helvetica, sans-serif;">I was trying to modify a table of approx 5 million records setting a field on the table to not allow null values. However, while saving changes in table, I got below error:</span></span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<b><span style="font-family: Arial, Helvetica, sans-serif;"><span style="background-color: white;">'Account ' table</span><br style="background-color: white;" /><span style="background-color: white;">- Unable to modify table. </span><br style="background-color: white;" /><span style="background-color: white;">Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.</span></span></b><br />
<b><span style="background-color: white;"><span style="font-family: Arial, Helvetica, sans-serif;"><br /></span></span></b>
<span style="background-color: white;"><span style="font-family: Arial, Helvetica, sans-serif;">After that I click ok button and I got below error:</span></span><br />
<span style="background-color: white;"><span style="font-family: Arial, Helvetica, sans-serif;"><br /></span></span>
<span style="background-color: white;"><b><span style="font-family: Arial, Helvetica, sans-serif;">User cancelled out of save dialog.</span></b></span><br />
<span style="background-color: white;"><b><span style="font-family: Arial, Helvetica, sans-serif;"><br /></span></b></span>
<span style="background-color: white;"><span style="font-family: Arial, Helvetica, sans-serif;">This error is coming because table designer have a default timeout value of 30 secs. And as I am saving a table with huge records, it is taking time more than 30 secs, so above error coming.</span></span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="background-color: white;"><span style="font-family: Arial, Helvetica, sans-serif;">So the solution is to increase the timeout value of designer, (the maximum value for timeout is 65535).</span></span><br />
<span style="background-color: white;"><span style="font-family: Arial, Helvetica, sans-serif;">Click on tools menu - Options - Expand designers. Now here change the timeout value in "Table and Database designers" on the right side.</span></span><br />
<br style="background-color: white; font-family: verdana, arial, helvetica;" /></div>
Anonymoushttp://www.blogger.com/profile/09453699184523060639noreply@blogger.com0tag:blogger.com,1999:blog-3682177839638728927.post-44984133306434195212015-07-20T11:20:00.000+05:302015-07-20T11:20:50.561+05:30Record Retrieval Error Connection Read<div dir="ltr" style="text-align: left;" trbidi="on">
<span style="font-family: Arial, Helvetica, sans-serif;">You may receive below error while running third party application with backend SQL Server.</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<b><span style="font-family: Arial, Helvetica, sans-serif;"><span style="background-color: white;">Record Retrieval Error</span><br style="background-color: white;" /><span style="background-color: white;">Error: (Connection Read(recv()).(01000)) attempting to access a record from the MERCAN file. Returning to previous window.</span></span></b><br />
<b><span style="background-color: white;"><span style="font-family: Arial, Helvetica, sans-serif;"><br /></span></span></b>
<span style="background-color: white; font-family: Arial, Helvetica, sans-serif;">The application is showing this error because it lost the network connectivity with the SQL Server instance for a short period of time.</span><br />
<span style="background-color: white;"><span style="font-family: Arial, Helvetica, sans-serif;"><br /></span></span>
<span style="background-color: white;"><span style="font-family: Arial, Helvetica, sans-serif;">So, restart the application once the network issue is solved.</span></span><br />
<br /></div>
Anonymoushttp://www.blogger.com/profile/09453699184523060639noreply@blogger.com0tag:blogger.com,1999:blog-3682177839638728927.post-60581752945069650492015-06-29T09:54:00.000+05:302015-06-29T09:54:18.674+05:30Attach Database Failed<div dir="ltr" style="text-align: left;" trbidi="on">
<span style="font-family: Arial, Helvetica, sans-serif;">Sometime you may get below error while attaching a database:</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;"><b><span style="background-color: white;">Attach database failed for Server 'SERVERNAME\SQLEXPRESS'. (Microsoft.SqlServer.Express.Smo)</span><br style="background-color: white;" /><br style="background-color: white;" /><span style="background-color: white;">Additional information:</span><br style="background-color: white;" /><span style="background-color: white;">An exception occurred while executing a Transact-SQL statement or batch.</span><br style="background-color: white;" /><span style="background-color: white;">(Microsoft.SqlServer.Express.ConnectionInfo)</span><br style="background-color: white;" /><br style="background-color: white;" /><span style="background-color: white;">Unable to open the physical file "filename.mdf". Operating system error 5: "5(error not found)".(Microsoft SQL Server, Error: 5120)</span></b></span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><b><span style="background-color: white;"><br /></span></b></span>
<span style="background-color: white; font-family: verdana, arial, helvetica;">To solve above error, </span><span style="background-color: white; font-family: verdana, arial, helvetica;">make sure that the SQL Server Service account has modify permissions over the physical file on the hard drive that you are trying to attach to a SQL Server instance, and modify permissions also over the folder that contains the file.</span></div>
Anonymoushttp://www.blogger.com/profile/09453699184523060639noreply@blogger.com0tag:blogger.com,1999:blog-3682177839638728927.post-78136972369833662982015-06-22T09:57:00.000+05:302015-06-22T09:57:01.349+05:30Query to get details of permissions on Database objects<div dir="ltr" style="text-align: left;" trbidi="on">
<span style="font-family: Arial, Helvetica, sans-serif;">Use below query to get details of permissions on Database objects</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<b><span style="font-family: Arial, Helvetica, sans-serif;">SELECT ulogin.name AS [User Name],</span></b><br />
<b><span style="font-family: Arial, Helvetica, sans-serif;">CASE princ.type WHEN 'U' THEN 'Windows User' WHEN 'G' THEN 'Windows Group' WHEN 'S' THEN 'SQL User' END AS [User Type],</span></b><br />
<b><span style="font-family: Arial, Helvetica, sans-serif;">princ.name AS [Database User Name], perm.permission_name AS [Permission Type], perm.state_desc AS [Permission State],</span></b><br />
<b><span style="font-family: Arial, Helvetica, sans-serif;">CASE perm.class WHEN 1 THEN obj.type_desc ELSE perm.[class_desc] END AS [Object Type],</span></b><br />
<b><span style="font-family: Arial, Helvetica, sans-serif;">CASE perm.class WHEN 1 THEN OBJECT_NAME(perm.major_id) WHEN 3 THEN schem.[name] WHEN 4 THEN imp.[name] END AS [Object Name],</span></b><br />
<b><span style="font-family: Arial, Helvetica, sans-serif;">col.name AS [Column Name]</span></b><br />
<b><span style="font-family: Arial, Helvetica, sans-serif;">FROM sys.database_principals princ LEFT JOIN sys.server_principals ulogin on princ.sid = ulogin.sid LEFT JOIN sys.database_permissions perm ON perm.grantee_principal_id = princ.principal_id LEFT JOIN sys.database_principals imp ON imp.principal_id = perm.major_id LEFT JOIN sys.columns col ON col.object_id = perm.major_id AND col.column_id = perm.minor_id LEFT JOIN sys.schemas schem ON schem.schema_id = perm.major_id LEFT JOIN sys.objects obj ON perm.major_id = obj.object_id</span></b><br />
<b><span style="font-family: Arial, Helvetica, sans-serif;">WHERE princ.[type] IN ('S','U','G') AND princ.[name] NOT IN ('sys', 'INFORMATION_SCHEMA')</span></b></div>
Anonymoushttp://www.blogger.com/profile/09453699184523060639noreply@blogger.com0tag:blogger.com,1999:blog-3682177839638728927.post-74575737178204722162015-06-15T09:48:00.000+05:302015-06-15T09:48:35.481+05:30Log backup to replace maintenance plan<div dir="ltr" style="text-align: left;" trbidi="on">
<span style="font-family: Arial, Helvetica, sans-serif;">Use below script to restore maintenance plan from log backup:</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<b><span style="font-family: Arial, Helvetica, sans-serif;">USE [Master]</span></b><br />
<b><span style="font-family: Arial, Helvetica, sans-serif;">GO</span></b><br />
<b><span style="font-family: Arial, Helvetica, sans-serif;">DECLARE @BackupExtention nVarChar(4); SET @BackupExtention = '.trn'</span></b><br />
<b><span style="font-family: Arial, Helvetica, sans-serif;">DECLARE @DB nVarChar(128); SET @DB = ''</span></b><br />
<b><span style="font-family: Arial, Helvetica, sans-serif;">DECLARE @BackupDir nVarChar(138);</span></b><br />
<br />
<b><span style="font-family: Arial, Helvetica, sans-serif;">--loop through each databASe in full recovery mode</span></b><br />
<b><span style="font-family: Arial, Helvetica, sans-serif;">WHILE @DB < ( SELECT MAX(Name) FROM sysDatabases WHERE DATABASEPROPERTYEX(Name,'RECOVERY') = 'FULL' )</span></b><br />
<b><span style="font-family: Arial, Helvetica, sans-serif;">BEGIN</span></b><br />
<b><span style="font-family: Arial, Helvetica, sans-serif;"><span class="Apple-tab-span" style="white-space: pre;"> </span>SELECT @DB = MIN (Name) FROM sysDatabases WHERE DATABASEPROPERTYEX(Name,'RECOVERY') = 'FULL' AND Name > @DB</span></b><br />
<b><span style="font-family: Arial, Helvetica, sans-serif;"><span class="Apple-tab-span" style="white-space: pre;"> </span>DECLARE @DatabaseName nVarChar(128); SET @DatabaseName = @DB --SET @DatabaseName = 'TFSWarehouse'</span></b><br />
<b><span style="font-family: Arial, Helvetica, sans-serif;"><span class="Apple-tab-span" style="white-space: pre;"> </span>DECLARE @DateTimestamp nVarChar(14); SET @DateTimestamp = '_' + CONVERT(VarChar, GetDate(), 112) + '_' + replace(CONVERT(VarChar, GetDate(), 108),':','')</span></b><br />
<b><span style="font-family: Arial, Helvetica, sans-serif;"><span class="Apple-tab-span" style="white-space: pre;"> </span>DECLARE @RemoteBackupPath nVarChar(260);</span></b><br />
<b><span style="font-family: Arial, Helvetica, sans-serif;"><span class="Apple-tab-span" style="white-space: pre;"> </span>SET @BackupDir = N'D:\Backup\' + @DatabaseName</span></b><br />
<b><span style="font-family: Arial, Helvetica, sans-serif;"><span class="Apple-tab-span" style="white-space: pre;"> </span>SET @RemoteBackupPath = @BackupDir + N'\' + @DatabaseName + @DateTimestamp + @BackupExtention</span></b><br />
<b><span style="font-family: Arial, Helvetica, sans-serif;"><span class="Apple-tab-span" style="white-space: pre;"> </span>DECLARE @BackupDevice nVarChar(128); SET @BackupDevice = N'local_' + @DatabaseName + @BackupExtention</span></b><br />
<b><span style="font-family: Arial, Helvetica, sans-serif;"><span class="Apple-tab-span" style="white-space: pre;"> </span>--SELECT @DatabaseName, @BackupDevice, @RemoteBackupPath</span></b><br />
<b><span style="font-family: Arial, Helvetica, sans-serif;"><span class="Apple-tab-span" style="white-space: pre;"> </span>--drop AND recreate the backup device</span></b><br />
<b><span style="font-family: Arial, Helvetica, sans-serif;"><span class="Apple-tab-span" style="white-space: pre;"> </span>IF Exists(SELECT NULL FROM sys.Backup_Devices WHERE Name = @BackupDevice)</span></b><br />
<b><span style="font-family: Arial, Helvetica, sans-serif;"><span class="Apple-tab-span" style="white-space: pre;"> </span> EXEC Master..sp_DropDevice @LogicalName = @BackupDevice</span></b><br />
<b><span style="font-family: Arial, Helvetica, sans-serif;"><span class="Apple-tab-span" style="white-space: pre;"> </span>EXEC Master.dbo.sp_AddumpDevice @DevType = N'disk', @LogicalName = @BackupDevice, @PhysicalName = @RemoteBackupPath</span></b><br />
<b><span style="font-family: Arial, Helvetica, sans-serif;"><span class="Apple-tab-span" style="white-space: pre;"> </span>--create the subdirectory if not already preset</span></b><br />
<b><span style="font-family: Arial, Helvetica, sans-serif;"><span class="Apple-tab-span" style="white-space: pre;"> </span>EXECUTE Master.dbo.xp_Create_SubDir @BackupDir</span></b><br />
<b><span style="font-family: Arial, Helvetica, sans-serif;"><span class="Apple-tab-span" style="white-space: pre;"> </span>--execute the backup</span></b><br />
<b><span style="font-family: Arial, Helvetica, sans-serif;"><span class="Apple-tab-span" style="white-space: pre;"> </span>BACKUP LOG @DatabaseName TO DISK = @RemoteBackupPath WITH NOFORMAT, NOINIT, Name = @BackupDevice, SKIP, REWIND, NOUNLOAD, STATS = 10</span></b><br />
<b><span style="font-family: Arial, Helvetica, sans-serif;">END</span></b><br />
<b><span style="font-family: Arial, Helvetica, sans-serif;">GO</span></b><br />
<br /></div>
Anonymoushttp://www.blogger.com/profile/09453699184523060639noreply@blogger.com0tag:blogger.com,1999:blog-3682177839638728927.post-31214281842454557492015-06-01T09:46:00.000+05:302015-06-01T09:46:33.258+05:30Unable to open DTS package in SQL 2008 Management Studio<div dir="ltr" style="text-align: left;" trbidi="on">
<span style="font-family: Arial, Helvetica, sans-serif;"><span style="background-color: white; text-align: justify; text-indent: 26.66666603088379px;">Sometime when we try to import a SQL Server 2000 DTS package into SQL Server 2008 R2 using SSMS, the following error </span><span style="background-color: white; text-align: justify; text-indent: 26.66666603088379px;">appears</span><span style="background-color: white; text-align: justify; text-indent: 26.66666603088379px;">:</span></span><br />
<span style="background-color: white; text-align: justify; text-indent: 26.66666603088379px;"><span style="font-family: Arial, Helvetica, sans-serif;"><br /></span></span>
<span style="background-color: white; text-align: justify; text-indent: 26.66666603088379px;"><span style="font-family: Arial, Helvetica, sans-serif;"><b>Managing DTS packages in SQL Server Management Studio requires the SQL Server 2000 Data Transformation Services (DTS) runtime. To install the DTS runtime, on your SQL Server 2008 R2 installation media, locate and run the Windows Installer package for SQL Server 2005 Backward Compatibility (SQLServer2005_BC*.msi). You can also download the DTS runtime from the SQL Server Feature Pack page on the Microsoft Download Center. (Microsoft.SqlServer.DtsObjectExplorerUI)</b></span></span><br />
<span style="background-color: white; text-align: justify; text-indent: 26.66666603088379px;"><span style="font-family: Arial, Helvetica, sans-serif;"><br /></span></span>
<span style="font-family: Arial, Helvetica, sans-serif;"><span style="background-color: white; text-align: justify; text-indent: 26.66666603088379px;">As suggested, the “Microsoft SQL Server 2005 Backward compatibility” package must be installed. It can be found on this location: </span><a href="http://www.microsoft.com/en-us/download/details.aspx?id=3522">http://www.microsoft.com/en-us/download/details.aspx?id=3522</a></span><br />
<span style="background-color: white; text-align: justify; text-indent: 26.66666603088379px;"><span style="font-family: Arial, Helvetica, sans-serif;"><br /></span></span>
<span style="font-family: Arial, Helvetica, sans-serif;"><span style="background-color: white; text-align: justify; text-indent: 26.66666603088379px;">After the installation of “Microsoft SQL Server 2005 Backward compatibility” package the DTS package is imported successfully. </span><span style="background-color: white; text-align: justify; text-indent: 26.66666603088379px;">Now when we try to open the DTS package, the following error appears:</span></span><br />
<span style="background-color: white; text-align: justify; text-indent: 26.66666603088379px;"><span style="font-family: Arial, Helvetica, sans-serif;"><br /></span></span>
<span style="background-color: white; text-align: justify; text-indent: 26.66666603088379px;"><span style="font-family: Arial, Helvetica, sans-serif;"><b>SQL Server 2000 DTS Designer components are required to edit DTS packages. Install the special Web download, “SQL Server 2000 DTS Designer Components” to use this feature. (Microsoft.SqlServer.DtsObjectExplorerUI)</b></span></span><br />
<span style="background-color: white; text-align: justify; text-indent: 26.66666603088379px;"><span style="font-family: Arial, Helvetica, sans-serif;"><br /></span></span>
<span style="font-family: Arial, Helvetica, sans-serif;"><span style="background-color: white; text-align: justify; text-indent: 26.66666603088379px;">To solve this error the “Sql Server 2000 DTS Designer Components” package must be installed. It can be found on this location: </span><a href="http://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=11988">http://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=11988</a></span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="background-color: white; text-align: justify; text-indent: 26.66666603088379px;"><span style="font-family: Arial, Helvetica, sans-serif;">After the installation try to open again the DTS package in SSMS. </span></span><br />
<span style="background-color: white; text-align: justify; text-indent: 26.66666603088379px;"><span style="font-family: Arial, Helvetica, sans-serif;"><br /></span></span>
<span style="background-color: white; text-align: justify; text-indent: 26.66666603088379px;"><span style="font-family: Arial, Helvetica, sans-serif;"><b>SQL Server 2000 DTS Designer components are required to edit DTS packages. Install the special Web download, “SQL Server 2000 DTS Designer Components” to use this feature. (Microsoft.SqlServer.DtsObjectExplorerUI)</b></span></span><br />
<span style="background-color: white; text-align: justify; text-indent: 26.66666603088379px;"><span style="font-family: Arial, Helvetica, sans-serif;"><br /></span></span>
<span style="font-family: Arial, Helvetica, sans-serif;"><span style="background-color: white; text-align: justify; text-indent: 26.66666603088379px;">If the same error appears, copy the DLL and RLL files as described in below Microsoft link: </span><a href="http://msdn.microsoft.com/en-us/library/ms143755.aspx">http://msdn.microsoft.com/en-us/library/ms143755.aspx</a></span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">To ensure that the DTS designer can be used in SQL Server Management Studio</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">1. Copy the files, SEMSFC.DLL, SQLGUI.DLL, and SQLSVC.DLL, from the %ProgramFiles%\Microsoft SQL Server\80\Tools\Binn folder to the %ProgramFiles%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE folder.</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">2. Copy the files, SEMSFC.RLL, SQLGUI.RLL, and SQLSVC.RLL, from the %ProgramFiles%\Microsoft SQL Server\80\Tools\Binn\Resources\%lang_id% folder to the %ProgramFiles%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Resources\%lang_id% folder.</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">For example, for U.S. English, the lang_id subfolder will be "1033".</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;">To ensure that the DTS designer can be used in Business Intelligence Development Studio</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">1. Copy the files, SEMSFC.DLL, SQLGUI.DLL, and SQLSVC.DLL, from the %ProgramFiles%\Microsoft SQL Server\80\Tools\Binn folder to the %ProgramFiles%\Microsoft Visual Studio 9.0\Common7\IDE folder.</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">2. Copy the files, SEMSFC.RLL, SQLGUI.RLL, and SQLSVC.RLL, from the %ProgramFiles%\Microsoft SQL Server\80\Tools\Binn\Resources folder to the %ProgramFiles%\Microsoft Visual Studio 9.0\Common7\IDE\Resources\%lang_id% folder. For example, for U.S. English, the lang_id subfolder will be "1033".</span></div>
Anonymoushttp://www.blogger.com/profile/09453699184523060639noreply@blogger.com0tag:blogger.com,1999:blog-3682177839638728927.post-57469155941496666842015-05-11T11:19:00.001+05:302015-05-11T11:19:39.487+05:30List Table with Identity Column<div dir="ltr" style="text-align: left;" trbidi="on">
<span style="font-family: Arial, Helvetica, sans-serif;">You can use below queries to get the list of all the tables with Identity column:</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<b><span style="font-family: Arial, Helvetica, sans-serif;">SELECT OBJECT_NAME(ID) AS [Table], Name AS [Column]</span></b><br />
<b><span style="font-family: Arial, Helvetica, sans-serif;">FROM syscolumns WHERE COLUMNPROPERTY(ID, Name, 'IsIdentity') = 1</span></b><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">Or use can use below query also</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<b><span style="font-family: Arial, Helvetica, sans-serif;">SELECT OBJECT_NAME(ID) AS [Table], Name AS [Column]</span></b><br />
<b><span style="font-family: Arial, Helvetica, sans-serif;">FROM syscolumns WHERE STATUS = 0x80</span></b><br />
<br />
<span style="font-family: Arial, Helvetica, sans-serif;">You can use blow query to check whether the table has Identity column:</span><br />
<br />
<b>SELECT Name AS [Table], OBJECTPROPERTY(ID, 'TableHasIdentity') AS [Has_Identity]</b><br />
<b>FROM SysObjects WHERE xType = 'U'</b></div>
Anonymoushttp://www.blogger.com/profile/09453699184523060639noreply@blogger.com0tag:blogger.com,1999:blog-3682177839638728927.post-69755684199006951712015-05-04T09:37:00.001+05:302015-05-04T09:37:18.573+05:30Cannot alter the table 'Table_Name' because it is being published for replication.<div dir="ltr" style="text-align: left;" trbidi="on">
<span style="font-family: Arial, Helvetica, sans-serif;">Today I was altering Primary key columns datatype in a table and got below error in Transaction replication.</span><br />
<div>
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span></div>
<span style="font-family: Arial, Helvetica, sans-serif;"><b>Msg 4929, Level 16, State 1, Line 1</b></span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><b>Cannot alter the table 'Table_Name' because it is being published for replication.</b></span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">I had taken following steps to solve the problem</span><br />
<ul style="text-align: left;">
<li><span style="font-family: Arial, Helvetica, sans-serif;">Remove table from the replication.</span></li>
<li><span style="font-family: Arial, Helvetica, sans-serif;">Change the datatype of Primary key column.</span></li>
<li><span style="font-family: Arial, Helvetica, sans-serif;">Add again add table to the replication and reinitialize the publication.</span></li>
</ul>
</div>
Anonymoushttp://www.blogger.com/profile/09453699184523060639noreply@blogger.com0tag:blogger.com,1999:blog-3682177839638728927.post-70348788329933544132015-04-27T09:50:00.000+05:302015-04-27T09:50:29.556+05:30Backup not starting for Database with Full Text Catalog<div dir="ltr" style="text-align: left;" trbidi="on">
<div style="background-color: white; color: #333333; line-height: 19.200000762939453px; margin-bottom: 10px;">
<span style="font-family: Arial, Helvetica, sans-serif;">Today when I was taking backup of a database got below error:</span></div>
<div style="background-color: white; color: #333333; line-height: 19.200000762939453px; margin-bottom: 10px;">
<span style="line-height: inherit;"><b><span style="font-family: Arial, Helvetica, sans-serif;">Failed to change the status to RESUME for full-text catalog “Test_FullTextCatalog” in database “Test”. Error: 0×80043607(An internal interface is being used after the corresponding catalog has been shutdown. The operation will be aborted.).</span></b></span></div>
<div style="background-color: white; color: #333333; line-height: 19.200000762939453px; margin-bottom: 10px;">
<span style="font-family: Arial, Helvetica, sans-serif;">When I checked, backup keeps pending on 0% without any progress. In SQL server error log, I also found error related to Full text catalog that SQL server is facing issue in setting Full test catalog status. These is some issue with FTS service due to which when backup ask FTS service to change Full text catalog status, it failed. SQL server backup change status between PAUSE & RESUME before & after backup. </span></div>
<div style="background-color: white; color: #333333; line-height: 19.200000762939453px; margin-bottom: 10px;">
<span style="font-family: Arial, Helvetica, sans-serif;">Than I started Full text Service to resolve the error. And than try to backup and now it worked successfully.</span></div>
</div>
Anonymoushttp://www.blogger.com/profile/09453699184523060639noreply@blogger.com0tag:blogger.com,1999:blog-3682177839638728927.post-55307027039437126962015-04-13T09:39:00.000+05:302015-04-13T09:39:45.645+05:30Start, Stop and Disable the job in SQL Server 2008<div dir="ltr" style="text-align: left;" trbidi="on">
<span style="font-family: Arial, Helvetica, sans-serif;">1) Use below procedure to start the Job in SQL</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">Exec <b>msdb.dbo.sp_start_job @job_name = N'Job_Name'</b></span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">Example:</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;">Exec msdb.dbo.sp_start_job @job_name = N'</span><span style="font-family: Arial, Helvetica, sans-serif;">Job_Name</span><span style="font-family: Arial, Helvetica, sans-serif;">'</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<br />
<div style="text-align: left;">
<span style="font-family: Arial, Helvetica, sans-serif;">OR</span></div>
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">Exec msdb.dbo.sp_start_job N'Job name'</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">2) </span><span style="font-family: Arial, Helvetica, sans-serif;">Use below procedure to stop the Job in SQL</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">Exec <b>msdb.dbo.sp_stop_job @job_name = '</b></span><b><span style="font-family: Arial, Helvetica, sans-serif;">Job_Name</span><span style="font-family: Arial, Helvetica, sans-serif;">'</span></b><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">Example:</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;">Exec msdb.dbo.sp_stop_job @job_name = N'</span><span style="font-family: Arial, Helvetica, sans-serif;">Job_Name</span><span style="font-family: Arial, Helvetica, sans-serif;">'</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">OR</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">Exec msdb.dbo.sp_stop_job N'Job name'</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">3) </span><span style="font-family: Arial, Helvetica, sans-serif;">Use below procedure to enable or disable the Job in SQL</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">To enable the Job:</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<b><span style="font-family: Arial, Helvetica, sans-serif;">EXEC msdb.dbo.sp_update_job @job_name = N'</span><span style="font-family: Arial, Helvetica, sans-serif;">Job_Name</span><span style="font-family: Arial, Helvetica, sans-serif;">', @enabled = 1</span></b><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">To disable the Job:</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<b><span style="font-family: Arial, Helvetica, sans-serif;">EXEC msdb.dbo.sp_update_job @job_name = N'</span><span style="font-family: Arial, Helvetica, sans-serif;">Job_Name</span><span style="font-family: Arial, Helvetica, sans-serif;">', @enabled = 0</span></b></div>
Anonymoushttp://www.blogger.com/profile/09453699184523060639noreply@blogger.com0tag:blogger.com,1999:blog-3682177839638728927.post-29347636023084794722015-03-30T10:04:00.000+05:302015-03-30T10:04:54.732+05:30Clear recent server list from SSMS<div dir="ltr" style="text-align: left;" trbidi="on">
<span style="font-family: Arial, Helvetica, sans-serif;">Today someone has asked me a question "How to clear the Most Recently Used (MRU) server names from the connect screen in SSMS"? </span><br />
<span style="font-family: Arial, Helvetica, sans-serif;">There is no feature in SSMS to clear the MRU from the Connect to Server screen. </span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">When I launched SSMS 2008 the Connect screen, It has a lot servers listed in it. This information is stored in a file "<b>SQLStudio.bin</b>". This file is located in the below folder.</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><b>C:\Documents and Settings\[UserName]\Application Data\Microsoft\Microsoft SQL Server\100\Tools\Shell</b></span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">Now to clear the Server Names from the connect screen, First close the SSMS and than delete above file. If SSMS is left open while deleting file than again you will get the old Server name list. This is because SSMS update above file just before closing completely.</span><br />
<div>
<br /></div>
</div>
Anonymoushttp://www.blogger.com/profile/09453699184523060639noreply@blogger.com0tag:blogger.com,1999:blog-3682177839638728927.post-1348306658431396912015-03-23T10:02:00.000+05:302015-03-23T10:02:12.382+05:30Queries waiting for memory<div dir="ltr" style="text-align: left;" trbidi="on">
<span style="font-family: Arial, Helvetica, sans-serif;">You can use below query to find out the queries that require a memory grant to execute or have acquired a memory grant. </span><br />
<span style="font-family: Arial, Helvetica, sans-serif;">The queries that do not have to wait on a memory grant will not appear in the result. </span><br />
<span style="font-family: Arial, Helvetica, sans-serif;">If this query returns a lot of rows than it could be a indication of internal memory pressure. This will help you to identify the queries which are requsting larger memory grants. There are various reason for this. The quiry might be poorly written. That may require some index for optimization. You should run this query periodically and check for the resource hungry queries. The user who runs below query must have View SErver STate permission on the server.</span><br />
<br />
<span style="font-family: Arial, Helvetica, sans-serif;"><b>SELECT DB_NAME(ST.DBID) AS [Database Name], MG.Requested_Memory_KB AS [Requested memoty in KB], MG.Ideal_Memory_KB AS [Ideal Memory in KB], MG.Request_Time AS [Request Time], MG.Grant_Time AS [Grant Time], MG.Query_Cost AS [Query Cost], MG.DOP, ST.[TEXT], QP.Query_Plan AS [Query Plan]</b></span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><b>FROM SYS.DM_EXEC_QUERY_MEMORY_GRANTS AS MG CROSS APPLY SYS.DM_EXEC_SQL_TEXT(PLAN_HANDLE) AS ST CROSS APPLY SYS.DM_EXEC_QUERY_PLAN(MG.PLAN_HANDLE) AS QP </b></span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><b>ORDER BY MG.REQUESTED_MEMORY_KB DESC</b></span><br />
<br /></div>
Anonymoushttp://www.blogger.com/profile/09453699184523060639noreply@blogger.com0