Win32Exception (0x80004005): The wait operation timed out

Win32Exception (0x80004005),Win32Exception (0x80004005) The wait operation timed out, The wait operation timed out,SQL Timeout expired
I was running an .NET console application that upon initial load pulls a list of items from a SQL server via a stored procedure. Within few seconds of loading the application, received the below error message:

Exception::System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out::Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.::.Net SqlClient Data Provider

Cause:
The problem was that the stored procedure took ~37 seconds to complete, which is slightly greater than the default timeout for a query to execute - 30 seconds. I figured this by executing the stored procedure manually in SQL Server Management Studio.

Resolution:
We need to set the CommandTimeout (in seconds) so that it is long enough for the command to complete its execution.

added the below line before filling the data adapter.

SqlCommand.CommandTimeout = 60; //60 seconds that is long enough for the stored procedure to complete.

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value

SQL Errors and Solutions,The conversion of a varchar data type to a datetime data type resulted in an out-of-range value,SQL,SQL tips

SQL Server Error Message:

 The conversion of a varchar data type to a datetime data type resulted in an out-of-range value

This error occurs when the varchar value does not form a valid date.

Error Message:

Server: Msg 242, Level 16, State 3, Line 1 The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Causes:

This error occurs when trying to convert a string date value into a DATETIME data type but the date value contains an invalid date. The individual parts of the date value (day, month and year) are all numeric but together they don’t form a valid date.


To illustrate, the following SELECT statements (all based on US date format MM/DD/YYYY)will generate the error:

SELECT CAST('02/29/2006' AS DATETIME) -- 2006 Not a Leap Year   

SELECT CAST('06/31/2006' AS DATETIME) -- June only has 30 Days   

SELECT CAST('13/31/2006' AS DATETIME) -- There are only 12 Months

SELECT CAST('01/01/1600' AS DATETIME) -- Year is Before 1753


Another way the error may be encountered is when the format of the date string does not conform to the format expected by SQL Server as set in the SET DATEFORMAT command. For example, in United

To illustrate, if the date format expected by SQL Server is in the MM-DD-YYYY (US date format) format, the following statement will generate the error:

SELECT CAST('31-01-2006' AS DATETIME)

Solution/Workaround:

To avoid this error from happening, you can check first to determine if a certain date in a string format is valid using the ISDATE function. The ISDATE function determines if a certain expression is a valid date. So if you have a table where one of the columns contains date values but the column is defined as VARCHAR data type, you can do the following query to identify the invalid dates:

SELECT * FROM [dbo].[Orders]
WHERE ISDATE([OrderDate]) = 1

Once the invalid dates have been identified, you can have them fixed manually then you can use the CAST function to convert the date values into DATETIME data type:

SELECT CAST([OrderDate] AS DATETIME) AS [Order Date]
FROM [dbo].[Orders]

Another way to do this without having to update the table and simply return a NULL value for the invalid dates is to use a CASE condition:

SELECT CASE ISDATE([OrderDate]) WHEN 0
THEN CAST([OrderDate] AS DATETIME)
ELSE CAST(NULL AS DATETIME) END AS [Order Date]
FROM [dbo].[Orders]

Unable to find the requested .Net Framework Data Provider. It may not be installed.

SQL Errors and Solutions,.NET Errors and Solutions,Unable to find the requested .Net Framework Data Provider. It may not be installed.,.NET

I got this error "Unable to find the requested .Net Framework Data Provider. It may not be installed." when I try to retrieve the data from SQL Server.

Reason:


Whoo!!

I had wrongly mentioned the Data provider name in my dll.

I had mentioned as

Conndll.ProviderName = "System.Data.Sqlclient" 'c in client should have been upper case letter

instead of

Conndll.ProviderName = "System.Data.SqlClient"

Invalid object name 'INFORMATION_SCHEMA.tables' - SQL Error

Invalid object name 'INFORMATION_SCHEMA.tables',SQL Errors and Solutions,SQL

The error Invalid object name 'INFORMATION_SCHEMA.tables' was thrown when I executed the  following query,

Select * from INFORMATION_SCHEMA.tables

for a particular database.

Reason: The particular database was case sensitive.

Solution:
I re-ran the same query with upper-case letters and it worked fine.

Select * from INFORMATION_SCHEMA.TABLES - did the trick for me.

SqlBulkCopy Error: The given value of type SqlDecimal from the data source cannot be converted to type decimal of the specified target column.

 

Error Description:

The given value of type SqlDecimal from the data source cannot be converted to type decimal of the specified target column.

Reason:

The error occurs during sqlbulkcopy when the destination table contains the Decimal column with same precision and scale. (E.g., The  table in SQLServer has column TestColumn  Decimal (3,3) )

SELECT (cast(0.000 as decimal(3,3))) this will run fine in SQL, but will fail in bulk copy.

Possible Solutions:

Increase the precision size.

I had the same problem when I worked on my data migration project.

The work around was to increase the precision size by 1 if both the precision and scale are same for the Decimal Column type.

Example:

TestColumn Decimal (3,3) will fail in sql bulk copy.

But

TestColumn Decimal (3, 4) will work fine in sql bulk copy.

17892 Logon failed for login due to trigger execution. Changed database context to ‘master’.

The error Error : 17892 Logon failed for login due to trigger execution. Changed database context to ‘master’." usually happens when try to login to the system after dropping the database.


Possible Reason

1. Drop the database after creating the trigger without dropping the trigger and try to login.

Let me explain the scenario with script example.

Example:

Consider a database MyTempDB.

[CODE]

use myTempDB
GO

--Create a table Called MytempTable

CREATE TABLE myTempTable
(val1 VARCHAR(100),
val2 VARCHAR(100))

GO

--Create Trigger

CREATE TRIGGER Trg_Temp

ON ALL SERVER FOR LOGON

AS

BEGIN

INSERT INTO myTempDB.dbo.myTempTable ('XXX','xxx')

END
GO

[/CODE]

Dropping of myTempDB database


[CODE]

---Dropping of myTempDB database

USE master
GO
DROP DATABASE myTempDB --will throw Logon failed for login due to trigger execution

GO

[/CODE]


After executing the above code, we will get the error

Error : 17892 Logon failed for login due to trigger execution. Changed database context to ‘master’."

and we cannot login to the database.


Possible Solutions

1. Must use drop trigger before dropping the database

Example:

[CODE]

---Dropping of myTempDB database

USE master
GO

--Drop thhe Trigger

DROP TRIGGER Trg_Temp ON ALL SERVER

--Drop the database
DROP DATABASE myTempDB

GO

[/CODE]

2. If we don't give the Drop Trigger, we cannot login to the sql server database.

3. The fix is to use DAC (Using a Dedicated Administrator Connection to Kill Currently Running Query.)

4. Using windows authentication do the following

a. Connect to sql server using windows authentication

b. Goto command prompt

c. Type the following

C:\Documents and Settings\Manager> sqlcmd -S LocalHost -d master -A

DROP TRIGGER Trg_Temp ON ALL SERVER
GO

The -d option will enable us to directly login to the database

'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0.0, Culture=neutral

The error 'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed generally occurs when try to run the stored procedure.

This error often occurs when try to run the Stored Procedure from the web application.

Possible Reason

1. Not enough security permission for the stored Procedure

2. Anything that access database from SP requires at least the
WSS_Medium security policy in the web.config file.

3. If the security message is from the web part, it's usually the trust element in the web.config file.


How to Fix?

1. Open wss_mediumtrust.config & wss_minimaltrust.config files

2. The normal path is (C:\Program Files\Common Files\Microsoft Shared\Web Server
Extensions\12\config\)

3. We can find the exact path in web.config file


4. Find in the wss_mediumtrust.config File:





5. Copy and paste it in to the node of
wss_minimaltrust.config file.


6. In the PermissionSet section of this configuration file, add the
following:

Find in wss_mediumtrust.config:

Copy and paste it in to the a node of
wss_minimaltrust.config.


7. Goto Control Panel - Administrative Tools

8. Goto .NET Framework configuration settings

9.a) Expand the runtime security policy.

b) expand machine

c) expand code groups

d) right click all_code

e) Click on properties

f) Click on permission tab

g) Set Modified nothing to Full Trust

SQL Error: Disallowed implicit conversion from data type varchar to data type money

The Error Disallowed implicit conversion from data type varchar to data type money usually occurs when try to assign String Value to Money Data Type of Sql Server.


Possible Reasons:


1. Assign String(Varchar) Value to Sql Server Column value which is of Money data type.

2. Especially in Insert and Update Queries

Assume Account Table in Sql Server contains a Field Amount which is of Type "Money".


Example1:

Insert Query

strSQL = "Insert INTO AccountTable(Amount) Values (" & txtamount.text & ")"

MyConn.Execute(strSQL)


Example2:

Update Query

strSQL = "UPDATE AccountTable SET Amount = " & txtamount.text

MyConn.Execute(strSQL)



Solutions:


Need to Convert the Value of Varchar Type or String Type to Money.


How to convert Varchar to Money Type?

Need to Use CONVERT Function and Single Quotes.

Modified Query:

strSQL = "Insert INTO AccountTable(Amount) Values (CONVERT(money,'" & txtamount.text & "'))"

strSQL = "UPDATE AccountTable SET Amount = (CONVERT(money,'" & txtamount.text & "'))"


Important:

(Note the single-quote just after the word money, and the single-quote and end paren just prior to the final comma.)

Windows Update; Error Code 8000FFFF

While trying to update latest SQL Server Book Online(SQL Server 2008), the error

"Windows Update; Error Code 8000FFFF" will be throwing.


Work Around


1. Click on Windows button and search ‘regedit’.

2. Right click on the regedit and “Run as Administrator”
Go to HKEY_LOCAL_MACHINE >> COMPONENTS and look for keys described in steps 4, 5 and 6.

3. In the absence of any key go to the next step

4. Find key PendingXmlIdentifier and delete it.

5. Find key NextQueueEntryIndex and delete it.

6. Find key AdvancedInstallersNeedResolving and delete it.

7. Restart the computer.

Once restarted now attempt Windows Updates; it should work fine now.

There is already an object named ‘#temp’ in the database

Generally the error

There is already an object named ‘#temp’ in the database occurs when we try to create/use temp table in our query.


Reasons:

The Temp table with the name already exists already in the database.


Solution:

1. Check Whether the table with the name temp already exising in the database.

2. If so drop the table and then create the temp table.

Example


IF EXISTS (
SELECT *
FROM sys.tables
WHERE name LIKE '#temp%')
DROP TABLE #temp
CREATE TABLE #temp(ID INT, FileName varchar(500) )


Note:

Must use LIKE '#temp%' .

We should not use WHERE name ='#temp'


3. In Sql server 2005 0r 2008 we can use try catch block

begin try
DROP TABLE #temp
end try

begin catch

end catch

CRETAE TABLE #temp (ID INT, FileName varchar(500)



4. We should not be run the above code in Temp Database.

provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server

Generally the Error

"provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server" comes

When we cannot connect to the SQl Server. There are various reasons for this.


Possible Solutions:

1. Check Whether the SQL Server is running or not.
Because the trial to connect to the stopped server may also cause this problem.

2. Check the name of the SQl Server.
For example, if we try to connect to SQL server using .NET application, for some SQL Server, the name of the server "localhost" works, but for some server, we may need to mention "(local)".

3. Check Whether the SQL Browser Service is running

4. Check Whether the TCP/IP is enabled for SQL Server Configuration

* Expand SQL Server Configuration Manager
* Enable TCP/IP

5. Firewall Settings

You may need to add the port of Sql server to the Firewall if it is other than the default port(1433).

Go to Control Panel | Windows Firewall | Change Settings | Exceptions | Add Port

6. Enable the Remote Connection

Goto SQL Server Management Studio.
Right Click on Server node
Click On Properties
Check the Allow Remote connections to Server Check Box


7. Enable Named Pipes

- Open the "SQL Server Configuration Manager" (under Configuration Tools)
- Expand the "SQL Server 2005 Network Configuration"
- Select the "Protocols for "
- Set the Named Pipes To Enabled

8. Create Exception of Sqlbrowser.exe

18486 Login failed for user ’sa’ because the account is currently locked out

The error Msg: 18486 Login failed for user ’sa’ because the account is currently locked out will throw only when the account is locked.

When the account is getting locked?

When the number of unsuccessful login attempts exceeds certain time, the login account will be disabled by the System.

WorkAround:

1. Disable the locking policy of the system. But it may affect the security level of the system as it will allow unlimited number of unsuccessful login attempts.

2. ALTER LOGIN "sa" With Password = 'YourPassword' UNLOCK;

-it will unlock the Account.

3. The best practice would be create a new system account with the same rights as "sa" and disable "Sa" login.


Same Errors and solution available under http://www.dotnetspider.com/resources/29472-SQL-Error-Login-failed-for-user-sa.aspx