System.InvalidOperationException thrown by SQL server 2005

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

when i have write the 7000 row
stored in the dssetparameter dataset
my connection string
string conString ="Data Source = .\\SQLEXPRESS;AttachDbFileName = C:\Documents and Settings\admin\Desktop\testing\testing.mdf;Integrated Security =true;Connect Timeout=1000";

if (dssetparameter != null || dssetparameter.Tables[0].Rows.Count != 0)
{
foreach (DataRow row in dssetparameter.Tables[0].Rows)
{
selectquery += " select '" + row["itemID"] + "' as unit_param_id ,'" + row["paramvalue"] +"' as param_value,'" + row["date_logged"] + '" as date_logged ,'" + row["date_logged1"] + '" as OPC-date,'" + row["Quality";] + "' as param_quality Union ALL" + Environment.NewLine;
}
if (selectquery != "";)
{
selectquery = selectquery.Remove(selectquery.LastIndexOf("Union ALL"));
insertquery += "insert into" + tablename + " " + Environment.NewLine;
insertquery += "unit_param_id,param_value,date_logged, OPC-date,param_quality)" + Environment.NewLine;
insertquery += " " + selectquery + "";
if (con2.State == ConnectionState.Closed)
con2.Open();
cmd2 = new OdbcCommand(insertquery, con2);
cmd2.CommandTimeout = 120;
cmd2.ExecuteNonQuery();
if (con2.State == ConnectionState.Open)
con2.Close();
}

using this query the below error thrown by application

timeout expired

[System.InvalidOperationException] = {Function evaluation disabled because a previous function evaluation timed out. You must continue execution to reenable function evaluation.}

ExecuteNonQuery requires an open and available Connection. The connections current state is closed.

thanks in advance

Posted 20-Jun-11 19:07pm

vrushali katkade

Add a Solution

5 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

2irfanshaikh · Answer 1 · 2011-06-20T20:16:00

Solution 1

Can you please remove Union All and Use only Union.

I think it is throwing timeout expired exception because of Union All.

Posted 20-Jun-11 20:16pm

2irfanshaikh

Comments

vrushali katkade 21-Jun-11 2:33am

can you please tell me the how to increase the execution speed of above query

nit_singh · Answer 2 · 2011-06-20T20:44:00

Solution 2

Increase the CommandTimeOut value.

You can convert the table to XML format and operat XML sql operation on the xml to insert the data.

Look on the below article

Inserting XML formatted data[^] and http://www.sql-server-performance.com/2007/xml-data-2005/[^]

Posted 20-Jun-11 20:44pm

nit_singh

Comments

vrushali katkade 21-Jun-11 3:07am

i get the all data in the dataTable ( reading from the another SQL table)then i will be stored into the XML then fired the QUERY it will take the more time than above query

Liju Sankar · Answer 3 · 2011-06-21T03:39:00

Solution 3

To increase the execution speed of your query,
please try to use BULK INSERT (Transact-SQL)[MSDN - BULK INSERT]

you can also try How to use BULK INSERT

Posted 21-Jun-11 3:39am

Liju Sankar

Updated 21-Jun-11 3:40am

v3

phil.o · Answer 4 · 2011-06-21T05:50:00

Hi,

In your connection string, you're escaping the backslash for the SQL instance (\\).
But you didn't escape backslashes of the path of the .mdf file.
Try this connection string and see if it's giving a different result :

string conString ="Data Source = .\\SQLEXPRESS;AttachDbFileName = C:\\Documents and Settings\\admin\\Desktop\\testing\\testing.mdf;Integrated Security = true;Connect Timeout=1000";

You can also avoid the need of escaping backslashes by setting an @ on top of your string declaration. Thus :

string conString =@"Data Source = .\SQLEXPRESS;AttachDbFileName = C:\Documents and Settings\admin\Desktop\testing\testing.mdf;Integrated Security = true;Connect Timeout=1000";

Hope this helps.

Edit : you could also avoid to refer to the file itself by providing the database name directly. Thus :

string conString =@"Data Source = .\SQLEXPRESS;Initial Catalog = testing;Integrated Security = true;Connect Timeout=1000";

2irfanshaikh · Answer 5 · 2011-06-21T21:43:00

You convert your data in XML format
and then try this line of code this will solve your purpose

SQL

declare @i int
 
exec sp_xml_preparedocument @i output, 
'<mydata>
  <test xmlid="3" xmldata="blah blah blah" />
  <test xmlid="1" xmldata="blah" />
</mydata>'
 
insert into test 
select xmlID, xmlData 
from OpenXml(@i, 'mydata/test')
with (xmlID int, xmlData nvarchar(30))
where xmlID not in (select xmlID from test)
 
update test
set test.xmlData = ox.xmlData
from OpenXml(@i, 'mydata/test')
with (xmlID int, xmlData nvarchar(30)) ox
where test.xmlID = ox.xmlID
 
exec sp_xml_removedocument @i

Reference How To: Insert and Update with OpenXML[^]