Fill Factor

The fill-factor option is provided for fine-tuning index data storage and performance. When an index is created or rebuilt, the fill-factor value determines the percentage of space on each leaf-level page to be filled with data, reserving the remainder on each page as free space for future growth. For example, specifying a fill-factor value of 80 means that 20 percent of each leaf-level page will be left empty, providing space for index expansion as data is added to the underlying table. The empty space is reserved between the index rows rather than at the end of the index.
The fill-factor value is a percentage from 1 to 100, and the server-wide default is 0 which means that the leaf-level pages are filled to capacity.

Fill-factor values 0 and 100 are the same in all respects.

You can use the CREATE INDEX or ALTER INDEX statements to set the fill-factor value for individual indexes. To modify the server-wide default value, use the sp_configure system stored procedure. To view the fill-factor value of one or more indexes, use sys.indexes.
The fill-factor setting applies only when the index is created, or rebuilt. The SQL Server Database Engine does not dynamically keep the specified percentage of empty space in the pages. Trying to maintain the extra space on the data pages would defeat the purpose of fill factor because the Database Engine would have to perform page splits to maintain the percentage of free space specified by the fill factor on each page as data is entered.


Page Splits

A correctly chosen fill-factor value can reduce potential page splits by providing enough space for index expansion as data is added to the underlying table.When a new row is added to a full index page, the Database Engine moves approximately half the rows to a new page to make room for the new row. This reorganization is known as a page split. A page split makes room for new records, but can take time to perform and is a resource intensive operation. Also, it can cause fragmentation that causes increased I/O operations. When frequent page splits occur, the index can be rebuilt by using a new or existing fill-factor value to redistribute the data. 


Although a low, nonzero fill-factor value may reduce the requirement to split pages as the index grows, the index will require more storage space and can decrease read performance. Even for an application oriented for many insert and update operations, the number of database reads typically outnumber database writes by a factor of 5 to 10. Therefore, specifying a fill factor other than the default can decrease database read performance by an amount inversely proportional to the fill-factor setting. For example, a fill-factor value of 50 can cause database read performance to decrease by two times. Read performance is decreased because the index contains more pages, therefore increasing the disk IO operations required to retrieve the data.

Adding Data to the End of the Table

A nonzero fill factor other than 0 or 100 can be good for performance if the new data is evenly distributed throughout the table. However, if all the data is added to the end of the table, the empty space in the index pages will not be filled. For example, if the index key column is an IDENTITY column, the key for new rows is always increasing and the index rows are logically added to the end of the index. If existing rows will be updated with data that lengthens the size of the rows, use a fill factor of less than 100. The extra bytes on each page will help to minimize page splits caused by extra length

Function to Get age

Here is a function to compute the age of a individual, by passing Birth Date and As On Date.

CREATE FUNCTION [dbo].[fn_GetAge]

(

@DOB DATETIME ,

@AsOfDate DATETIME

)

RETURNS INT

AS

BEGIN

DECLARE @Age INT

IF @DOB >= @AsOfDate

RETURN 0

SET @Age = DATEDIFF(yy, @DOB, @AsOfDate)

IF MONTH(@DOB) > MONTH(@AsOfDate)

OR ( MONTH(@DOB) = MONTH(@AsOfDate)

AND DAY(@DOB) > DAY(@AsOfDate)

)

SET @Age = @Age – 1

RETURN @Age

END

How to Execute this Function:-

SELECT [dbo].[fn_GetAge] (’02/04/1983′,’03/09/2011′)

Comparing 2 Results Sets to see if they are identical

SELECT CASE WHEN COUNT(*) = 0 THEN 'Same' ELSE 'Different' END
FROM (
        (
        SELECT * FROM Table1 
        EXCEPT
        SELECT * FROM Table2
        ) 
        UNION 
        (
        SELECT * FROM Table2
        EXCEPT
        SELECT * FROM Table1 
        )
) AS TempTable

This query gets all the rows that are in Table 1 but not Table 2, then UNIONS all rows that are in Table 2 but not Table 1.  If there's zero rows for both, the result sets must be the same.

Amount in Word Function

CREATE FUNCTION dbo.Amt_In_Words
(
@Amt BIGINT,
@Paisa INT
)
RETURNS VarChar(4000)
BEGIN
DECLARE @Table1 TABLE (SlNo INT IDENTITY(1, 1), AText NVarChar(50))
DECLARE @Table2 TABLE (SlNo INT IDENTITY(1, 1), AText NVarChar(50))
INSERT INTO @Table1 Select 'One'
INSERT INTO @Table1 Select 'Two'
INSERT INTO @Table1 Select 'Three'
INSERT INTO @Table1 Select 'Four'
INSERT INTO @Table1 Select 'Five'
INSERT INTO @Table1 Select 'Six'
INSERT INTO @Table1 Select 'Seven'
INSERT INTO @Table1 Select 'Eight'
INSERT INTO @Table1 Select 'Nine'
INSERT INTO @Table1 Select 'Ten'
INSERT INTO @Table1 Select 'Eleven'
INSERT INTO @Table1 Select 'Twelve'
INSERT INTO @Table1 Select 'Thirteen'
INSERT INTO @Table1 Select 'Fourteen'
INSERT INTO @Table1 Select 'Fifteen'
INSERT INTO @Table1 Select 'Sixteen'
INSERT INTO @Table1 Select 'Seventeen'
INSERT INTO @Table1 Select 'Eighteen'
INSERT INTO @Table1 Select 'Nineteen'
INSERT INTO @Table1 Select 'Twenty'
--Now, insert the multiples
INSERT INTO @Table2 Select 'Ten'
INSERT INTO @Table2 Select 'Twenty'
INSERT INTO @Table2 Select 'Thirty'
INSERT INTO @Table2 Select 'Forty'
INSERT INTO @Table2 Select 'Fifty'
INSERT INTO @Table2 Select 'Sixty'
INSERT INTO @Table2 Select 'Seventy'
INSERT INTO @Table2 Select 'Eighty'
INSERT INTO @Table2 Select 'Ninety'
DECLARE @strWord VarChar(300)
Select @strWord = ''
DECLARE @iAmt1 BIGINT, @iAmt2 BIGINT
DECLARE @strWord1 VarChar(4000)


IF @Amt < 10000000000000 AND @Amt >= 100000000000
BEGIN
SET @iAmt1 = @Amt
Select @Amt = ( @Amt % 100000000000 )
SET @iAmt1 = ( @iAmt1 - @Amt ) / 100000000000
SET @strWord1 = ''


IF @iAmt1 < 100 AND @iAmt1 > 20 
BEGIN
SET @iAmt2 = @iAmt1
SET @iAmt1 = ( @iAmt1 % 10)
SET @iAmt2 = ( @iAmt2 - @iAmt1 ) / 10
SET @strWord1 = ( Select @strWord1 + AText FROM @Table2 WHERE SlNo = @iAmt2 )
END


IF @iAmt1 <= 20 AND @iAmt1 <> 0 
BEGIN
SET @strWord1 = ( Select @strWord1 + AText FROM @Table1 WHERE SlNo = @iAmt1 )
END
SET @strWord = @strWord + @strWord1 + ' Kharab '
END


IF @Amt < 100000000000 AND @Amt >= 1000000000 
BEGIN
SET @iAmt1 = @Amt
Select @Amt = ( @Amt % 1000000000 )
SET @iAmt1 = ( @iAmt1 - @Amt ) / 1000000000
SET @strWord1 = ''


IF @iAmt1 < 100 AND @iAmt1 > 20 
BEGIN
SET @iAmt2 = @iAmt1
SET @iAmt1 = ( @iAmt1 % 10)
SET @iAmt2 = ( @iAmt2 - @iAmt1 ) / 10
SET @strWord1 = ( Select @strWord1 + AText FROM @Table2 WHERE SlNo = @iAmt2 )
END


IF @iAmt1 <= 20 AND @iAmt1 <> 0 
BEGIN
SET @strWord1 = ( Select @strWord1 + AText FROM @Table1 WHERE SlNo = @iAmt1 )
END
SET @strWord = @strWord + @strWord1 + ' Arab '
END


IF @Amt < 1000000000 AND @Amt >= 10000000 
BEGIN
SET @iAmt1 = @Amt
Select @Amt = ( @Amt % 10000000 )
SET @iAmt1 = ( @iAmt1 - @Amt ) / 10000000
SET @strWord1 = ''


IF @iAmt1 < 100 AND @iAmt1 > 20 
BEGIN
SET @iAmt2 = @iAmt1
SET @iAmt1 = ( @iAmt1 % 10)
SET @iAmt2 = ( @iAmt2 - @iAmt1 ) / 10
SET @strWord1 = ( Select @strWord1 + AText FROM @Table2 WHERE SlNo = @iAmt2 )
END


IF @iAmt1 <= 20 AND @iAmt1 <> 0 
BEGIN
SET @strWord1 = ( Select @strWord1 + AText FROM @Table1 WHERE SlNo = @iAmt1 )
END
SET @strWord = @strWord + @strWord1 + ' Crore '
END


IF @Amt < 10000000 AND @Amt >= 100000 
BEGIN
SET @iAmt1 = @Amt
Select @Amt = ( @Amt % 100000 )
SET @iAmt1 = ( @iAmt1 - @Amt ) / 100000
SET @strWord1 = ''


IF @iAmt1 < 100 AND @iAmt1 > 20 
BEGIN
SET @iAmt2 = @iAmt1
SET @iAmt1 = ( @iAmt1 % 10)
SET @iAmt2 = ( @iAmt2 - @iAmt1 ) / 10
SET @strWord1 = ( Select @strWord1 + AText FROM @Table2 WHERE SlNo = @iAmt2 )
END


IF @iAmt1 <= 20 AND @iAmt1 <> 0 
BEGIN
SET @strWord1 = ( Select @strWord1 + AText FROM @Table1 WHERE SlNo = @iAmt1 )
END
SET @strWord = @strWord + @strWord1 + ' Lac '
END


IF @Amt < 100000 AND @Amt >= 1000 
BEGIN
SET @iAmt1 = @Amt
SET @Amt = ( @Amt % 1000 )
SET @iAmt1 = ( @iAmt1 - @Amt ) / 1000
SET @strWord1 = ''
IF @iAmt1 < 100 AND @iAmt1 > 20 
BEGIN
SET @iAmt2 = @iAmt1
SET @iAmt1 = ( @iAmt1 % 10 )
SET @iAmt2 = ( @iAmt2 - @iAmt1 ) / 10
SET @strWord1 = ( Select @strWord1 + AText + ' ' FROM @Table2 WHERE SlNo = @iAmt2 )
END
IF @iAmt1 <= 20 AND @iAmt1 <> 0 
BEGIN
SET @strWord1 = ( Select @strWord1 + AText +' ' FROM @Table1 WHERE SlNo = @iAmt1 )
END
SET @strWord = @strWord + @strWord1 + ' Thousand '
END


IF @Amt < 1000 AND @Amt > = 100 
BEGIN
SET @iAmt1 = @Amt
SET @Amt = ( @Amt % 100 )
SET @iAmt1 = ( @iAmt1 - @Amt ) / 100
SET @strWord = ( Select @strWord + ' ' +AText + ' Hundred ' FROM @Table1 WHERE SlNo = @iAmt1)




END
IF @Amt < 100 AND @Amt > 20 
BEGIN
SET @iAmt1 = @Amt
SET @Amt = ( @Amt % 10 )
SET @iAmt1 = ( @iAmt1 - @Amt ) / 10
SET @strWord = ( Select @strWord + AText + ' ' FROM @Table2 WHERE SlNo = @iAmt1 )
END
IF @Amt <= 20 AND @Amt >= 1 
BEGIN
SET @strWord = ( Select @strWord + AText +' ' FROM @Table1 WHERE SlNo = @Amt )
END
DECLARE @strWordP VarChar(300)
SET @strWordP = ''
IF @Paisa <> 0 BEGIN
IF @Paisa < 100 AND @Paisa > 20 
BEGIN
DECLARE @Paisa_01 VarChar(300)
SET @Paisa_01 = @Paisa
SET @Paisa = ( @Paisa % 10 )
SET @Paisa_01 = ( @Paisa_01 - @Paisa ) / 10
SET @strWordP = ( Select @strWordP + AText FROM @Table2 WHERE SlNo = @Paisa_01 )
END


IF @Paisa <= 20 AND @Paisa >= 1 
BEGIN
SET @strWordP = ( Select @strWordP + AText FROM @Table1 WHERE SlNo = @Paisa )
END
SET @strWord = @strWord + 'And ' + @strWordP + ' Paisa'


END
RETURN (REPLACE(@strWord, ' ', ' '))
END
GO


--Example
Select dbo.Amt_In_Words(6543211235412, 55)

Steps to clear the server list in SSMS

Generally we connect to several SQL Server instances. Once connected successfully, this server history is shown in the drop down list as shown below. In some cases we might have similar names for servers as such as using P for production, D for Development, U for UAT and T for Test environments. Because of this there may be a chance that we connect to the wrong server. Also, there may be times when we connect to servers infrequently or a server may go away, but the name is still in the list. For these types of scenarios we want to clear the server list and only show servers we really need to work with, but unfortunately there is not an option within SSMS.

Solution

Unfortunately there is not a simple solution to clear the history from within SSMS, but following the below steps for SQL 2005 and SQL 2008 you can clear the history.
Step 1
Make sure that the "SQL Server Management Studio" is not opened.
Step 2
Open a run box as shown below and enter:

For SQL Server 2005
%APPDATA%\Microsoft\Microsoft SQL Server\90\Tools\Shell\

For SQL Server 2008
%APPDATA%\Microsoft\Microsoft SQL Server\100\Tools\Shell\

Step 3
The above command will open the application data folder of your logged in account and then do the following.
SQL Server 2005
Search for file mru.dat and you can either rename or delete this file.
SQL Server 2008
Search for file SqlStudio.bin and you can either rename or delete this file.
Note that if you use Run As… to open SSMS, you have to change the profile name in the path to match the Windows account and delete the appropriate file.

Step 4
Once you renamed or deleted the mru.dat or SqlStudio.bin file and start SSMS againg you will not see any recent servers in the list.

For SQL 2008, note that once you delete the SqlStudio.bin file, you will lose any configured SSMS settings, so you will have to reconfigure the settings. Do this go to Tools -> Options and re-configure as needed.

Ref: http://www.mssqltips.com

Microsoft SQL Server 2008 x64 Error: 15401

I recently came across this error within an Server 2008 Active Directory Environment where I attempted to add a domain user account to Microsoft SQL Server 2008 x64.

As you may see on the internet, the problem is in fact related to SID, but in my situation it was not the SSID of the user, but of the actual server. The instance of Server 2008 x64 is running in VMWare Workstation 7 and was created by cloning another VM. This, of course, makes them identical, such that changing the computer name only results in what ‘appears’ to be disparite systems. In a workgroup this shouldn’t be an issue, but once Active Directory is introduced, it gets wacky. AD relies on SID’s, and thus, acts unpredictably when 2 machines / users / groups have the same SID. This is further exagerated when SQL Server is involved, since it creates and / or stores its own SID for the user.

Here are the steps I followed to solve this issue:

1. Remove the server running SQL Server from the domain. Reboot.
2. Verify the AD Computer Account has been deleted for the system (on a domain controller).
3. Run newSID.exe (found here) and change the SID of the server running SQL Server to a new random SID. Reboot.
4. Add server back to the domain and verify the account is created within AD.

Once I did all that, the user added with ease.

Ref: steve-oh.com

MS SQL "connection refused" Errors and running MS SQL on a MacBook Pro

I did a fresh install of Windows with ColdFusion 7.02 and MS SQL 2000 (SP2?) under Parallels on my MacBook Pro the other day. After I had installed everything I tried to set up a DSN in ColdFusion but I kept getting "connection refused" errors, no matter what I did. I did a lot of googling to find a solution and found lots of work arounds including setting up a ODBC data-source. None of these were satisfying, I mean, you SHOULD be able to connect to a database right? That's the whole point! So that I don't do the same mistake again I thought I would blog it. The solution is actually rather simple, you just have to installMicrosoft SQL Server 2000 Service Pack 3a. That totally solves the problem. (I haven't checked with upgrading to Service Pack 4 yet.) On a related note, I was able to connect to the Microsoft SQL Server running under Parallels from OS X. Pretty neat!

Ref: markdrew.co.uk

Error : 1326 Cannot connect to Database Server Error: 40 – Could not open a connection to SQL Server

If you are receiving following error:

TITLE: Connect to Server
——————————
Cannot connect to Database Server.
——————————
ADDITIONAL INFORMATION:
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 1326)

I suggest that you read previous article first as that describes how to fix the problem and has worked for many cases.

SQL SERVER – Fix : Error : 40 – could not open a connection to SQL server

Now if SQL Server can be connected perfectly from local system but can not be connected from remote system, in that case firewall of the server where SQL Server is installed can be issue.

Follow instructions of this article to fix the issue.

Go to control panel >> Firewall Settings >> Add SQL Server’s Port to Exception List.

Now try to connect to SQL Server again. It will allow you to connect to server successfully.

Reference : http://blog.SQLAuthority.com

CommandTimout Property Problem with Connection Object

The Connection object of the ActiveX Data Objects supports the CommandTimeout property. This property is documented to support a value of 0, which provides for an infinite timeout. This functionality is not available in ActiveX Data Objects 1.0.

The CommandTimeout property functions properly for all values other than 0. If you wish an infinite timeout, you will need to use this parameter in conjunction with the Command object instead. The following example demonstrates how to set the Command object's timeout:

Set MyConn = Server.CreateObject("ADODB.Connection")
MyConn.ConnectionTimeout = 0
MyConn.CommandTimeout = 0
MyConn.Open ConStr, UserId, PassWord
Set cmdTemp = Server.CreateObject("ADODB.Command")
cmdTemp.CommandTimeout = 0
NOTE: The CommandTimeout property on an ADO connection object has no effect on the CommandTimeout property setting of the command object on the same connection. Essentially, the command object's CommandTimeout property does not inherit the value of the connection object's CommandTimeout value.

In order for cmdTemp.CommandTimeout = 0 to work properly, you must use the command object to execute the query.

The following code attempts to set the timeout to 0. When set to this value, the connection object should provide for an indefinite amount of time before timing out. It does not.

Set cn = Server.CreateObject("ADODB.Connection")
cn.CommandTimeout = 0
cn.Open "datasourcename", "userid", "password"
cn.Execute("SQL statement that would cause a timeout")

SELECT LIMIT (from, to)

Microsoft SQL hasn't something similar to LIMIT(from, to) as MySQL has. From Below Query we can get that.

DECLARE @rowsperpage INT  
DECLARE @start INT  
SET @start = 10002  
SET @rowsperpage = 5  
SELECT *  FROM ( SELECT row_number() OVER (ORDER BY id desc) AS rownum, * FROM   my_member ) AS A  WHERE A.rownum BETWEEN (@start) AND (@start + @rowsperpage - 1)  


Another example:

DECLARE @rowsperpage INT  

DECLARE @start INT  

SET @start = 10002  

SET @rowsperpage = 5  

SELECT *   FROM (SELECT ROW_NUMBER()  OVER (ORDER BY id DESC) AS Row, *   FROM my_member  ) AS LogWithRowNumbers   WHERE Row >= @start AND Row <=(@start + @rowsperpage - 1)    

How to Fix “Error: 823” in Microsoft SQL Server

Are you getting system-level errors while accessing your Microsoft SQL Server database? Are you unable to perform any I/O (Input/Output) operation on your database? Well, the problem generally occurs due to either conflicts of Windows API used by SQL Server or SQL Server database corruption. It leaves your database in an unusable state and leads to severe data loss situations. In order to get your mission critical data back in such cases, you must go for SQL database recovery solutions.

For instance, you may run across an error message similar to the following one in Windows Application Event Log or Microsoft SQL Server ERRORLOG:

"2010-03-06 22:41:19.55 spid58 Error: 823, Severity: 24, State: 2.
2010-03-06 22:41:19.55 spid58 The operating system returned error 38(Reached the end of the file.) to SQL Server during a read at offset 0x000000a72c0000 in file 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\my_db.mdf'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online."

When a program encounters the above error message while modifying or querying the data, error is returned to that program and SQL Server database connected is closed.

Root of the problem:

The problem is caused by Windows APIs, used by Microsoft SQL Server. These APIs (such as WriteFile, ReadFile, WriteFileGather, and ReadFileScatter) are used to perform Input/Output operations. Upon completion of I/O operations, MS SQL Server examines for errors associated with API calls. When API calls betrays with OS error, you come across this behavior of SQL Server. The problem may also occur due to severe corruption to Microsoft SQL Server database.

Solution:

Go through the below steps to recover SQL server database from corruption:
Review suspect pages table.

Check database consistency.

Review Windows Application Event Log to find operating system and file system related errors.

Restore database from current backup.

If the above methods fail to perform SQL Server recovery, you are required to opt for advanced third-party applications.

Error coming in Client system while running query

One day when tried to run a query in Client system, I encountered below mentioned error message :"An error occurred while executing batch. Error message is: The file exists."

The same error message is showing even if I click on any database and trying to expand its property. I think that there might be corruption with some dll files and that re installing the Shared components will resolve the issue. When I try to uninstall the Shared component using either Add/Remove Programs or %program files%\Microsoft SQL server\100\setup bootstrap\release\x64\setuparp.exe ( for 32 bit, it will be under x32) to uninstall the components, the un-installation failed with the below mentioned error message on the GUI window :

Message: One or more command-line switches were invalid.
When I drill drill down and look for the detail message I see the below message :

2010-05-05 15:33:43 Slp: Installing WMI Provider
2010-05-05 15:33:43 Slp: Sco: Attempting to create base registry key HKEY_LOCAL_MACHINE, machine
2010-05-05 15:33:43 Slp: Sco: Attempting to open registry subkey SOFTWARE\Microsoft\Microsoft SQL Server\100
2010-05-05 15:33:43 Slp: Sco: Attempting to get registry value SharedCode
2010-05-05 15:33:43 Slp: SharedDirPath is C:\Program Files\Microsoft SQL Server\100\Shared\
2010-05-05 15:33:43 Slp: Attempting to install MOF/MFL file for WMI Provider C:\Program Files\Microsoft SQL Server\100\Shared\sqlmgmproviderxpsp2up.mof
2010-05-05 15:33:43 Slp: Sco: Attempting to install MOF file
2010-05-05 15:33:43 Slp: Running: C:\Windows\system32\WBEM\mofcomp.exe "C:\Program Files\Microsoft SQL Server\100\Shared\sqlmgmproviderxpsp2up.mof"
2010-05-05 15:33:43 Slp: Microsoft (R) MOF Compiler Version 6.1.7600.16385
2010-05-05 15:33:43 Slp: Copyright (c) Microsoft Corp. 1997-2006. All rights reserved.
2010-05-05 15:33:43 Slp: Parsing MOF file: C:\Program Files\Microsoft SQL Server\100\Shared\sqlmgmproviderxpsp2up.mof
2010-05-05 15:33:47 Slp: C:\Program Files\Microsoft SQL Server\100\Shared\sqlmgmproviderxpsp2up.mof (1): error SYNTAX 0X8004402f: Error creating temporary file
2010-05-05 15:33:47 Slp:
2010-05-05 15:33:47 Slp: Compiler returned error 0x8004402f
2010-05-05 15:33:47 Slp: Sco: Compile operation for mof file C:\Program Files\Microsoft SQL Server\100\Shared\sqlmgmproviderxpsp2up.mof failed. Exit code 2
2010-05-05 15:33:47 Slp: Configuration action failed for feature CommonFiles during timing ConfigNonRC and scenario ConfigNonRC.
2010-05-05 15:33:47 Slp: One or more command-line switches were invalid.
2010-05-05 15:33:47 Slp: Configuration action failed for feature CommonFiles during timing ConfigNonRC and scenario ConfigNonRC.
2010-05-05 15:33:47 Slp: Microsoft.SqlServer.Configuration.Sco.ScoException: One or more command-line switches were invalid.
2010-05-05 15:33:47 Slp: at Microsoft.SqlServer.Configuration.Sco.WmiProvider.InstallMof()
2010-05-05 15:33:47 Slp: at Microsoft.SqlServer.Configuration.Slp.SlpConfigurationPrivate.Install_ConfigWMIProvider(Dictionary`2 actionData)
2010-05-05 15:33:47 Slp: at Microsoft.SqlServer.Configuration.Slp.SlpConfigurationPrivate.Install_PostMSI(Dictionary`2 actionData, PublicConfigurationBase spcb)
2010-05-05 15:33:47 Slp: at Microsoft.SqlServer.Configuration.Slp.SlpConfigurationPrivate.InstallImpl(ConfigActionTiming timing, Dictionary`2 actionData, PublicConfigurationBase spcb)
2010-05-05 15:33:47 Slp: at Microsoft.SqlServer.Configuration.Slp.SlpConfigurationPrivate.Repair(ConfigActionTiming timing, Dictionary`2 actionData, PublicConfigurationBase spcb)
2010-05-05 15:33:47 Slp: at Microsoft.SqlServer.Configuration.SqlConfigBase.PrivateConfigurationBase.Execute(ConfigActionScenario scenario, ConfigActionTiming timing, Dictionary`2 actionData, PublicConfigurationBase spcbCurrent)
2010-05-05 15:33:47 Slp: at Microsoft.SqlServer.Configuration.SqlConfigBase.SlpConfigAction.ExecuteAction(String actionId)
2010-05-05 15:33:47 Slp: at Microsoft.SqlServer.Configuration.SqlConfigBase.SlpConfigAction.Execute(String actionId, TextWriter errorStream)
2010-05-05 15:33:47 Slp: Exception: Microsoft.SqlServer.Configuration.Sco.ScoException.
2010-05-05 15:33:47 Slp: Source: Microsoft.SqlServer.Configuration.Sco.
2010-05-05 15:33:47 Slp: Message: One or more command-line switches were invalid..
2010-05-05 15:33:47 Slp: Watson Bucket 1
Original Parameter Values

The error message is self explanatory and says that Client system is failing to create temporary files. This can occur if WMI is unable to access the TEMP and/or TMP location, either because of permissions or because the path is invalid.
I checked the environment variable and changed it to "C:\Windows\Temp".
Then again I ran the un-installation and this time it went on fine. Then I re installed the client component and now I was able to get the output of the results. After some time when I try to register a new server it was failing with the below mentioned error message.

“Unhandled exception has occurred in a component in your application. If you click Continue, the application will ignore this error and attempt to continue.” Key not valid for use in specified state.

Now I Followed the below 2 step :

1. Close SSMS;

2. Go to the folder %appdata%\Microsoft\Microsoft SQL Server\100\Tools\ and check if there is a Shell folder, if so, rename it to Shell_Old;

Each time SSMS starts it creates this folder and will use old files. Hence by renaming the folder I was re-creating all the required components.

Now all the problem were solved.

Error 916

I came across a problem when using Microsoft SQL Server Management Studio 2008 to connect and manage a database which is housed on my shared host’s SQL Server 2005, the problem was that I couldn’t actually access my database, as I clicked the “Database” node within “Object Explorer” I received the message:

The server principal “username” is not able to access the database “databasename” under the current security context. (Microsoft SQL Server, Error: 916)

Anyway, after an hour, I decided that I would try and access my database within Microsoft Visual Studio 2008, using the “Server Explorer”. I set myself up with a connection to the database server, and hooray, I could connect and access everything. That left me wondering why it wasn’t working in the Management Studio, so I spent some more time trawling the net for an answer (since I’m hardly a SQL Server guru!). I eventually found something to help me out…

When I clicked on the “Databases” node within SQL Server Management Studio, the software would (in the background) execute an T-SQL query to retrieve a list of databases along with additional information about those databases, one of those pieces of information is collation, which I didn’t have permission to do for every database, only my own.

Fix:

1. In Object Explorer, click Databases
2. Display “Object Explorer Details” (F7) or “View –> Object Explorer Details”
3. Right click the column headers and deselect “collation”
4. Refresh Databases, and hopefully solved

RESTORE DATABASE fails while trying to restore password protected backup set from SSMS

I am getting the below error while restoring the Database backup from SSMS-UI.

TITLE: Microsoft SQL Server Management Studio

------------------------------
Specified cast is not valid. (SqlManagerUI)
------------------------------

Here’s the detailed error:
Program Location:
at Microsoft.SqlServer.Management.SqlManagerUI.SqlRestoreDatabaseGeneral.PopulateGridWithBackupSetsFromDevices()
at Microsoft.SqlServer.Management.SqlManagerUI.SqlRestoreDatabaseGeneral.GetBackupSetsFromDevices()
at Microsoft.SqlServer.Management.SqlManagerUI.SqlRestoreDatabaseGeneral.textDeviceSelected_TextChanged(Object sender, EventArgs e)
at System.Windows.Forms.Control.OnTextChanged(EventArgs e)
at System.Windows.Forms.TextBoxBase.OnTextChanged(EventArgs e)
at System.Windows.Forms.Control.set_Text(String value)
at System.Windows.Forms.TextBoxBase.set_Text(String value)
at System.Windows.Forms.TextBox.set_Text(String value)
at Microsoft.SqlServer.Management.SqlManagerUI.SqlRestoreDatabaseGeneral.buttonSelectDevice_Click(Object sender, EventArgs e)
at System.Windows.Forms.Control.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ButtonBase.WndProc(Message& m)
at System.Windows.Forms.Button.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
Now, to resolve the error I go through the following procedure.
1st I ran the below command.

----RESTORE HEADERONLY----

RESTORE HEADERONLY FROM DISK = 'D:\tempdb\pubsdb.bak'
GO

Now it is clearly showing that the backup set is PASSWORD PROTECTED¹. For more details, go through the following link >> Backup Set Password Protection

Now I tried to restore the database using below SQL Query.

----RESTORE DATABASE USING T-SQL----

RESTORE DATABASE [New_DB] FROM DISK = N'C:\temp\New_DB.bak'
WITH FILE = 1, MOVE N'Pubs' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\New_DB.mdf',
MOVE N'Pubs_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\New_DB.ldf',
PASSWORD = '<Type Password here>'
GO

Extract File Name from File Path

How to extract the file name from a file path where the number of sub folders or name of folder is not fixed. In simple terms you do not know the position where the file name starts.

Solution 1:

DECLARE @fileName as varchar(255)

SET @fileName = 'D:\FolderA\FolderB\FileName.txt'

PRINT @fileName

-- D:\FolderA\FolderB\FileName.txt

 -- REVERSE is used to get the last "\" of the string

SET @fileName = RIGHT(@fileName, CHARINDEX('\', REVERSE(@fileName))-1)

PRINT @fileName

-- FileName.txt

Solution 2:

SELECT REVERSE(SUBSTRING(REVERSE(@fpath), 0, CHARINDEX('\', REVERSE(@fpath), 1)))

Solution 3:

This UDF will extract the file name from a full path name

Function FunctionGetFileName(FullPath As String)

Dim StrFind As String

        Do Until Left(StrFind, 1) = "\"

               iCount = iCount + 1

               StrFind = Right(FullPath, iCount)

                       If iCount = Len(FullPath) Then Exit Do

        Loop

        FunctionGetFileName = Right(StrFind, Len(StrFind) - 1)

End Function

To use this UDF push Alt+F11 and go Insert>Module and paste in the code. Push Alt+Q and save. The Function will appear under "User Defined" in the Paste Function dialog box (Shift+F3).
Enter the function like this.

=FunctionGetFileName(A1)

Where A1 contains you file and path name.