hello
i have a problem when i try to begin transaction in sql
this is the massege
"SqlConnection does not support parallel transactions."
but the connection dosent have another transAction
what can i do
thanks
This is my code in vb.net with Sql transactionI am using insertcommand and update command for executing the sqlqueryin consecutive transactions as follows.How can I achive parallel transactions in sql------------------start of code---------------------trybID = Convert.ToInt32(Session("batchID")) strSQL = "" strSQL = "Insert into sessiondelayed (batchid,ActualEndDate) values (" & bID & ",'" & Format(d1, "MM/dd/yyyy") & "')"
sqlCon = New System.Data.SqlClient.SqlConnection(ConfigurationSettings.AppSettings("conString"))
Dim s1 As String = sqlCon.ConnectionString.ToString sqlDaEndDate = New System.Data.SqlClient.SqlDataAdapter("Select * from sessiondelayed", sqlCon) dsEndDate = New DataSet sqlDaEndDate.Fill(dsEndDate)
dbcommandBuilder = New SqlClient.SqlCommandBuilder(sqlDaEndDate)
'sqlCon.BeginTransaction() 'sqlDaEndDate.InsertCommand.Transaction = tr If sqlCon.State = ConnectionState.Closed Then sqlCon.Open() End If sqlDaEndDate.InsertCommand = sqlCon.CreateCommand() tr = sqlCon.BeginTransaction(IsolationLevel.ReadCommitted) sqlDaEndDate.InsertCommand.Connection = sqlCon sqlDaEndDate.InsertCommand.Transaction = tr sqlDaEndDate.InsertCommand.CommandText = strSQL sqlDaEndDate.InsertCommand.CommandType = CommandType.Text
sqlDaEndDate.InsertCommand.ExecuteNonQuery() tr.Commit() sqlDaEndDate.Update(dsEndDate) sqlCon.Close() End If Catch es As Exception
Dim s2 As String = es.Message If sqlCon.State = ConnectionState.Closed Then sqlCon.Open() End If strSQL = " update SessionDelayed set ActualEndDate= '" & Format(d1, "MM/dd/yyyy") & "' where batchid=" & bID & "" sqlDaEndDate.UpdateCommand = sqlCon.CreateCommand() tr1 = sqlCon.BeginTransaction(IsolationLevel.ReadCommitted) sqlDaEndDate.UpdateCommand.Connection = sqlCon sqlDaEndDate.UpdateCommand.Transaction = tr1 sqlDaEndDate.UpdateCommand.CommandText = strSQL sqlDaEndDate.UpdateCommand.CommandType = CommandType.Text sqlDaEndDate.UpdateCommand.ExecuteNonQuery() tr1.Commit() sqlDaEndDate.Update(dsEndDate) sqlCon.Close()
End Try
'-------------End----------------
Hi,All.
I'm writing test cases on C# for a few methods that make changes in database.To prevent making changes I used BeginTransaction-Rollback,everything was good.But this doesn't work if tested method has BeginTransaction-Rollback code itself.An error appears in NUnit: System.InvalidOperationException : SqlConnection does not support parallel transactions.
Do smb know how to solve the problem?
*******************************tc below********************
[Test]
[Category("Access")]
public void UpdateGroupUserTable(/*Int32 userID, GroupList dataset*/)
{
r.BeginTransaction();
try
{
r.UpdateGroupUserTable(userId,gl);
}
finally
{
r.Rollback();
}
*******************************tested method below********************
public void UpdateGroupUserTable(Int32 userID, GroupList dataset)
{
CheckDisposed();
UpdateCommand.CommandType = CommandType.Text;
UpdateCommand.Parameters.Clear();
UpdateCommand.Parameters.Add("@USERID", userID);
BeginTransaction();
try
{
Adapter.Update(dataset.acl_group);
Commit();
}
catch (SqlException)
{
Rollback();
throw;
}
}
Hi,
I've tried to enclose a few database operations in a TransactionScope block but it looks like SQL Server CE RM does ignores ambiental transaction.
Here is the code:
static void TestTxn() {
// Command to insert an integer in a table with a single integer column
string cmdPassText = "INSERT TESTTABLE (INTFIELD) VALUES(1)";
// Command to force field type mismatch exception
string cmdFailText = "INSERT TESTTABLE (INTFIELD) VALUES('Foo')";
using (TransactionScope scope = new TransactionScope()) {
using (SqlCeConnection conn = new SqlCeConnection("DataSource = 'Test.sdf'")) {
try {
conn.Open();
SqlCeCommand cmdPass = new SqlCeCommand(cmdPassText, conn);
returnValue = cmdPass.ExecuteNonQuery();
SqlCeCommand cmdFail = new SqlCeCommand(cmdFailText, conn);
returnValue = cmdFail.ExecuteNonQuery();
}
catch (Exception ex){
Console.WriteLine("Command failed");
Console.WriteLine("Exception Message: {0}", ex.Message);
}
}
scope.Complete();
}
}
After first command suceeds and seccond command failes table still has one affected row after transaction.
Am I doing something wrong or System.Transactions.Transaction is not supported with SQL Server CE RM?
Thanks,
Aleksandar
I want to use transactions to protect my tasks. I have ten tasks that need to be completed in a single package. If any of the tasks fail - I need the entire process to rollback.
I have 5 execute sql tasks to truncate groups of tables in my destination database.
After each of the single truncate tasks - I use data flow tasks to copy the data from the source to the destination db.
Both dbs are on the same server.
I am basically copying the entire db - with the exception of a few tables.
package.transactionOption = required ,
and all tasks transactionOptions are set to supported.
Several questions. It seems to hang on the first data flow task - the output window stops, etc.
If I set package.transactionOption = supported - The package will execute.
If I create an error on the thrid truncate task - all previous tasks will complete - the package will fail on step 3 and the truncation for this step is not commited.
I am partially there. Can anyone help point me in the right direction? I need the entire process to commit if all tasks are successful or rollback if any fail.
thank-you in advance
Kim
I'm trying to run an SSIS package. The package runs on an SQL 2005 server on Win2k3 server. The package tries to connect to another win2k3 server with sql 2000 to retrieve some data. However, I recieve the errormessage shown in the topic.
I found info about modifying the MSDTC security settings under "component services" and did so. I made sure everything was allowed. However, the result was the same. Does anyone have any other idéa about what could cause this problem?
PS. The package works fine if I set up both databases on the same physical machine...
Regards Andreas
Reader Community
I've just started hosting my newly created Microsoft Visual Web Developer 2005 Express Edition web site. Unfortunately the Login group membership functions will not function correctly. Having contacted the web service hosting provider, They replied: "We do not support SQL express2005. The only way to use the extra functions of ASP.NET2 such as group membership is if it is using an SQL 2000 database to connect to. "
Is it possible to design web sites with Microsoft Visual Web Developer 2005 Express Edition that store membership details on an SQL 2000 database?
I've just paid £88 approx. $140 for a years subscription, have I chosen the wrong web service hosting provider?
Should I have designed the web site with a better web site design software tool that also makes designing membership login functionality easy, just as Microsoft Visual Web developer 2005 express edition?
Look forward to all comments?
Regards
Philip
Hi there,
I have decided to move all my transaction handling from asp.net to stored procedures in a SQL Server 2000 database. I know the database is capable of rolling back the transactions just like myTransaction.Rollback() in asp.net. But what about exceptions? In asp.net, I am used to doing the following:
<code>Try 'execute commands myTransaction.Commit()Catch ex As Exception Response.Write(ex.Message) myTransaction.Rollback()End Try</code>Will the database inform me of any exceptions (and their messages)? Do I need to put anything explicit in my stored procedure other than rollback transaction?
Any help is greatly appreciated
Hi All,
I am working on SQL Server 7.0. Every weekend we go for reindexing of some tables. I want to know if it is possible to run the re-indexing of tables in parallel so that I can save time.
Our database is of size 80GB and one table is around 22GB. Rebuilding of index on this table takes a lot of time and we are unable to index the other tables.
Any solutions/suggestions are mostly appreciated.
Regards,
Mitra
Hi
View 5 Replies View Relatedhi,
we currently use the Database Maintenance Plan to do backups for our SQL Server 2000 databases.
I notice that the database are backed up one after the other.
I would like to know how to run the backups in parallel rather than sequentially.
To do this, is there any dependency on the number of CPUs?
regards
Andrew
I just thought I would share this info:
I created the package to download 4 ftp files at once.
I set the MaxConcurrentExecutables for the SSIS package to 4. So in BIDS in downloads 4 files at the same time.
However, when I started the job I noticed that only 3 files were downloaded at the time (looking at temp files in download directory)
Solution:
Sure enough after digging around for awhile - in Step properties for SSIS package - there is execution tab - and "Maximum Concurrent Executables" was -1 (which for some reason defaults to 3 concurrent processes even on our dual CPU server) - so after chanign that value to 4 - tada - all 4 files in parallel
Public Conn As New SqlConnection("Data Source=localhost;Initial Catalog=tblUsers;UID=XXXX;pwd=XXXX")Public Conn As New SqlConnection("Data Source=XX.XXX.XX.XXX;Initial Catalog=tblUsers;UID=XXXX;pwd=XXXX")
This two commands are in my application. I switch between the two so I can test my application.
When I put my application on the server, I use the localhost SQLCONNECTION. The other is commented out. However, the application continues to connect to the server with the address that is commented out.
THIS IS VERY FRUSTRATING... HELP!!!!
Hi, Someone can explain to me what happend in this situation...i have this and want to call a procedure with connection parameters protected void Button1_Click(object sender, EventArgs e)
{
SqlConnection edo = new SqlConnection();
edo.ConnectionString = "server=myserver; uid=admin; pwd=; " +
"database=name1";
SqlCommand com = edo.CreateCommand();
com.CommandType = CommandType.Text;
My_proc(edo, com);
}
protected void My_proc(SqlConnection edo , SqlCommand com)
{
try
{edo.Open();
}
catch{}
finally{ edo.close();}
}
Well what happend with the SqlCommand and SqlConnection when return to Button_Click() Are they alive? Can be used in Buton_Click like parameters for another procedure? or i have to create it again?Thx in advance..
please help me !!!?I have a problem with sqlconnection definition when I add the sqlconnection from toolbox :like this: Imports System.Data.SqlClientPartial Class _Default Inherits System.Web.UI.Page Private Sub InitializeComponent() Me.sqlConnection1 = New System.Data.SqlClient.SqlConnection Me.sqlConnection1.ConnectionString = "Data Source=server;Initial Catalog=masterstd;User ID=sa" Me.sqlConnection1.FireInfoMessageEventOnUserErrors = False End Sub Private WithEvents sqlConnection1 As System.Data.SqlClient.SqlConnection Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load If Not IsPostBack Then Dim cmduniversity As SqlCommand Dim dtruniversity As SqlDataReader cmduniversity = New SqlCommand("select * from university", sqlConnection1) sqlConnection1.Open() dtruniversity = cmduniversity.ExecuteReader dtruniversity.Close() sqlConnection1.Close() End If End SubEnd Classin the sqlconnection.open the nullrefrence exception occures butwhen i define myself this exception dosn't occure.like this: Imports System.Data.SqlClientPartial Class _Default Inherits System.Web.UI.Page Private Sub InitializeComponent() End Sub Private WithEvents sqlConnection1 As System.Data.SqlClient.SqlConnection Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load If Not IsPostBack Then sqlConnection1 = New SqlConnection sqlConnection1.ConnectionString = "Data Source=server;Initial Catalog=masterstd;User ID=sa" sqlConnection1.FireInfoMessageEventOnUserErrors = False Dim cmduniversity As SqlCommand Dim dtruniversity As SqlDataReader cmduniversity = New SqlCommand("select * from university", sqlConnection1) sqlConnection1.Open() dtruniversity = cmduniversity.ExecuteReader dtruniversity.Close() sqlConnection1.Close() End If End Subwhere is the problem?
View 2 Replies View Related Can someone help me out? I am trying to establish a SQL server connection in my C# code. My C# code crasheds though. Can someone tell me what Iam doing wrong? Here is the C# code:
SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["TheDividerConnectionString"]);
Here is the connection string as defined in the web.config file:
<connectionStrings> <add name="TheDividerConnectionString" connectionString="Data Source=BVCOMPUTERSQLEXPRESS;Initial Catalog=TheDivider;Integrated Security=True" providerName="System.Data.SqlClient" /> </connectionStrings>
is their any code which can check my project for any connections which are open and never closed.
frm 1.1,sql server 2000
Would anyone ahve any ideas why when i debug this code, it stops and freezes at cn.open(); A raw sql query against the DB works in this format.
int top=10;SqlConnection cn = new SqlConnection("Data Source=blah;Initial Catalog=blah;Integrated Security=blah");
SqlCommand cmd = new SqlCommand("SELECT DISTINCT TOP (@nrows) [CustNu] FROM [Customer] WHERE [CustNu] like @term", cn);cmd.Parameters.AddWithValue("nrows", top);cmd.Parameters.AddWithValue("term", prefixText + "%");
List <string> suggestions = new List<string>();
cn.Open();
HiI would like to have some help for my problem.I'm trying to retreive the connectionString "MyConnectionString" from app.config using method 1 or 2 to get a connection to my DB (without any success.) --> Partial codepublic SqlConnection GetConnection() { // reference to System.Configuration Library is done 1 SqlConnection Connection = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString);--> thrown NullReferenceException on Conn.Open(); 2 SqlConnection Connection = new SqlConnection(ConfigurationSettings.AppSettings["MyConnectionString"]);--> thrown InvalidOperationException ConnectionString not initialised return Connection; } public Contest GetContestFromDB() { SqlConnection Conn = GetConnection(); Conn.Open(); ->> thrown Exception 1 or 2...Here my app.config :<?xml version="1.0" encoding="utf-8"?><configuration> <appSettings> <clear/> <add key="MyConnectionString" value="Server=.SQLExpress;AttachDbFilename=D:Documents and Settings0xMesdocumentsApp_DataShopperDB.MDF;Database=ShopperDB.MDF;Trusted_Connection=Yes;"></add> </appSettings> <connectionStrings> <clear/> --> using <clear/> to avoid to load from machine.config <add name="MyConnectionString" connectionString="Server=.SQLExpress;AttachDbFilename=D:Documents and Settings0xMes documentsApp_DataShopperDB.MDF;Database=ShopperDB.MDF;Trusted_Connection=Yes;"></add> </connectionStrings></configuration> I'm using Visual Studio 2008 with SQLServer 2005 Express Embedded and WindowsXP SP2 .Have you got any idea ?I'm not able to sleep anymore...And I've test this code in ASP.net with the same ConnectionString in the web.config file and it works !!I'm lost.By the way, thx if ou get an answer ww4ss
View 2 Replies View RelatedHi
I have the next simple code, what do i have to change to match it to SQL?
The code:
Sub Page_Load (Source As Object, E as EventArgs)
dim strConn as string =("Provider=" & "Microsoft.Jet.OLEDB.4.0;" & _
"Data Source =C:webspacesegolforferaforfera.co.ildbguestbook.mdb")
Dim MySQL as string = "SELECT Name, EMail, URL, Comment FROM Guestbook"
Dim MyConn as New OleDBConnection (strConn)
Dim Cmd as New OleDBCommand (MySQL, MyConn)
MyConn.Open ()
rptGuestbook.DataSource = Cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection)
rptGuestbook.DataBind()
End Sub
Thank's and have a good day.
Is there any way to run a stored procedure in parallel to another one? i.e. I have a stored procedure that sends an email. I then scan a table and send any unsent emails. I do not want the second part to slow the response to the user.
View 2 Replies View RelatedI'd like to run 4 stored procedures in parallel on 4 different processors on our server.
Does anyone know the syntax to do that? (Could not find it on the web so far).
Anything else I need to do in addition to the specific syntax (i.e. any specific properties to set in Enterprise Manager)?
Your help is greatly appreciated!
Lana
One of the developers here wants to run a PB script consisting of 11
inserts to 11 respective tables at the same time. This is on a development
box.
Currently, we are running on MSSQL 6.5 ,sp4. We have very general
parameters set, nothing special. We have max async io set to the default
The inserts run serially now. What I need to do is run them in parallel.
My questions are:
1) How do I do this -- run in parallel?
2) What parameters do I need to set?
3)What else would I need to do?
Any information you can provide will be greatly appeciated. Thanks.
David Spaisman
Assuming I have a line, is there a function I can call to create a parallel line at a given distance away.i.e - with the below I would want to draw a parallel line to the one output.
DECLARE @line geometry = 'LINESTRING(1 1, 2 2, 3 3, 4 4)'
SELECT @line
Hi ,I need to place the results of two different queries in the same resulttable parallel to each other.So if the result of the first query is1 122 343 45and the second query is1 342 443 98the results should be displayed as1 12 342 34 443 45 98If a union is done for both the queries , we get the results in rows.How can the above be done.Thanks in advance,vivekian
View 7 Replies View RelatedI have several packages within secuence containers and into one main dtsx package with a checkpoint configuration and when I run it some succeed and some don´t. The problem is that when I rerun it checkpoint doesn´t seem to work ´cause some of the successful packages are rerun as well (and not skipped as it should be...) In other words, the process does not begin on the point of failure..
Seems to be that packages that finish after the failure point (and succeed) are not registered in the checkpoint file, then when I rerun the main package these succeeded packages are rerun too....
Any ideas??
Hello,
I have created a project to do de-dupification of addresses.
I understand that Fuzzy Grouping will take less time if it has lesser data volume to process.
My source feed file is sometimes huge. So I am splitting the input into multiple branches based on
the first letter of the city. There are 7 branches in the process.
Source File Feed
|
Split data into 7 groups
|
------------------------------------------------------------------------------------------------------------------------------------------
| | | | | | |
FzGrpg FzGrpg FzGrpg FzGrpg FzGrpg FzGrpg FzGrpg
| | | | | | |
Split Split Split Split Split Split Split
| | | | | | |
------------- -------------- -------------- -------------- -------------- -------------- --------------
| | | | | | | | | | | | | |
<- - - - - - - Write the Canonicals and Dupes from each of these splits into database - - - - - - - - ->
When I designed this I was hoping that each of the Fuzzy Grouping tasks will execute in parallel.
But in reality they are processing one after the other.
Is there anyway to make them execute in parallel?
Appreciate your help.
Thanks
KM
I have a database for each of my customers and want to connect to a database depending on who is logged in.
all my users have their database name in the user profile.
how can i use the database name in the user profile to change the initial catalog to connect to aaother database?
thanks
Hi there, I'm not sure if the way I handle SqlConnection in my apps is the most performant one.So my apps use db heavily and there are loads of classes with methods like 1 using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["myConn"].ConnectionString))
2
3 {conn.Open();
4 // more code here
5 }
6
7
As I use using the connection is closed and the object is disposed when leaving the code block.I know that the connection is taken from a pool and when closed it returns to the pool as though I'm not sure if it is better to have one "global" SqlConnection objectand to use that object for all classes or most of them. What about the ConfigurationManager, doesn't it slow down the performance as well when I'm accessing the ConnectionString value thousands of times?I hope someone can point out the issues.Many thanks in advance, Limbic
I need some information on sqlconnnection object. I see it referenced in some exercises, and also displayed in the toolbox on some articles, but I'm running .net 2.0 and I have no sqlconnection in my toolbox, but more importantly, I can not seem to drop the data connection from my database/server explorer to my form to create a sqlconn object. When I highlight the data connection (in my case it's machinenamesqlexpress.pubs.dbo) and try to drag it - it immediately goes to a NO icon (that is a circle with a slash thru it) and I can not drop it onto my form. I can see the database with no problems in explorer.
I have been able to create a sqlconnection programatically:
Dim sqldatasource1 As SqlConnection = New SqlConnection()
sqldatasource1.ConnectionString = "Data Source=localhostsqlexpress;Initial Catalog=pubs;Integrated Security=True"
but how can I create it on my form?? I would appreciate any help on this matter.
(p.s. I am using VWD 2005 and SQLExpress)
I have created an aspx page that uses the SqlConnection class to pull data from Microsoft CRM’s database, and we have placed this aspx inside CRM’s directory on the server so that it uses the same integrated security that CRM uses. This works fine on our test environment but on our live environment the Sql connection fails with the exception: "Not associated with a trusted SQL Server connection"
This is probably because our test CRM server has the database on the same machine as the web server, but the live environment has the database and the web server on separate machines.
So my question is how can I create an aspx page that takes the integrated security credentials that the user used to log into the website and uses them to access data from a database on a seperate machine?
Hi,When using the following controls....System.Data.SqlClient.SqlConnection System.Data.SqlClient.SqlCommandIf I want to change my SQL command and execute the query once again what cleanup do I need to do first?Do I need close and dispose the SqlConnection?Do I need to dispose the SqlCommand?Can I use the SqlConnection for more than one SqlCommand?Thanks,Scott
View 5 Replies View RelatedI am working on a set of webforms that insert user data into a set of db tables.
I set up a test of an approach using northwind and I'm having trouble getting the insert to work. When I open the form, input the name and phone, and submit there is no error, but no record inserted into the Shippers table.
You can see one of my approaches in the ASPX code. I don't like having to do the select in order to do the insert -- so that's commented off.
I'm stuck. Thoughts about what I'm missing appreciated...
Ray
ASPX code.
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default66a.aspx.cs" Inherits="pages_audit_Default66a" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server"><title>Untitled Page</title>
</head><body>
<form id="form1" runat="server">
CompanyName:
<asp:textbox id="txtCompanyName" runat="server" /><br />
Phone:<asp:textbox id="txtPhone" runat="server" /><br />
<br />
<asp:button id="btnSubmit" runat="server" text="Submit" onclick="btnSubmit_Click" />
<br />
<br />
<br />
<br />
<asp:Label ID="awesomelbl" runat="server" Text="Label"></asp:Label><br />
<br />
<!--
<asp:sqldatasource id="SqlDataSource1" runat="server" connectionstring="<%$ ConnectionStrings:NorthwindConnectionString %>"
insertcommand="INSERT INTO Shippers(CompanyName, Phone) VALUES (@CompanyName, @Phone)" ProviderName="System.Data.SqlClient" SelectCommand="SELECT * FROM [Shippers]">
<insertparameters>
<asp:controlparameter controlid="txtCompanyName" name="CompanyName" />
<asp:controlparameter controlid="txtPhone" name="Phone" />
</insertparameters>
</asp:sqldatasource>
--></form>
</body>
</html>
c Sharp code
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.Sql;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
public partial class pages_audit_Default66a : System.Web.UI.Page
{protected void Page_Load(object sender, EventArgs e)
{
}protected void btnSubmit_Click(object sender, EventArgs e)
{SqlConnection con = new SqlConnection("Data Source=Chilibowl;Trusted_Connection=yes;DataBase=Northwind");
SqlCommand cmd = new SqlCommand("INSERT INTO [Shippers] ([CompanyName], [Phone]) VALUES (@CompanyName, @Phone)");SqlParameter cnameparam = new SqlParameter("@CompanyName", txtCompanyName.Text); SqlParameter phnparam = new SqlParameter("@Phone", txtPhone.Text);
cmd.Parameters.Add(cnameparam);
cmd.Parameters.Add(phnparam);
try
{
con.Open();if (cmd.ExecuteNonQuery() > 0)awesomelbl.Text = "successful insert";
}
catch
{
//handel
}
finally
{
con.Close();
}
}
}