Context Connection And MultipleActiveResultSets ...Can Have Both At The Same Time?
Jul 15, 2006
Hi,
When I enable MultipleActiveResultSets in the "context connection" (SqlConnection), I get an error:
System.InvalidOperationException: The only additional connection string keyword that may be used when requesting the context connection is the Type System Version keyword.
Can we have MARS in the "context connection"?
note: I'm doing this to support multiple open datareaders in a CLR stored procedure.
Thanks!
Andy
View 5 Replies
ADVERTISEMENT
Feb 24, 2006
Hello Guys,
I need some some clarifications on how Context connections and transactions inter operate in CLR.
The context connection allows for ADO objects to be " running in the same transaction space". So the association to the current transaction is implied. So as long as I set for example my SqlCommand to use the context connection I am going to be running under the same transaction.
SqlConnection sqlConn = new SqlConnection("context connection=true");
SqlCommand sqlComm = new SqlCommand("EXEC myCommand", sqlConn);
I guess my ambiguity comes from the fact that the Transaction is not specifically specified.
In addition what happens upon a trigger that for example watches and insert on a table? If the insert occurs under a transaction, I would assume that I will be also picking up that transaction in the CRL Trigger, thus the whole operation would seem atomic.
Thank you,
Lubomir
View 1 Replies
View Related
Oct 6, 2006
I have created an assembly which I load into SQL 2005. However, if I set my connection string = context connection = true... I will get an error saying something like this feature could not be used in this context... So I changed my function to insert each row.... Now the issue I have is the transfer takes 4X as long.... Before I made the change I was using the bulkcopy by specifying the actual connection string....but I also had to specify the password in the string...and since I wanted to get way from this specification...I attempted the context route. So...is there any other way of using the bulkcopy feature or something like it using the context connection?
Private Shared Function BulkDataTransfer2(ByVal _tblName As String, ByRef _dt As DataTable, ByRef emailLog As String) As Boolean
Dim success As Boolean = False
emailLog = emailLog & System.DateTime.Now.ToString & " - bulk transfer2 - " & _tblName & vbCrLf
Dim insertStr As String = "INSERT INTO " & _tblName & "("
Dim values As String = ") Values("
Dim drow As DataRow = Nothing
Dim dCol As DataColumn = Nothing
'add the column names
For Each dCol In _dt.Columns
insertStr = insertStr & dCol.ColumnName.ToString & ", "
values = values & "@" & dCol.ColumnName.ToString & ", "
Next
'remove the last comma & form the final string
insertStr = insertStr.Substring(0, insertStr.Length - 2)
values = values.Substring(0, values.Length - 2)
insertStr = insertStr & values & ")"
Dim connStr As String = "context connection = true"
Dim conn As New SqlConnection(connStr)
Dim cmd As SqlCommand = Nothing
Using conn
Try
conn.Open()
For Each drow In _dt.Rows
cmd = New SqlCommand(insertStr, conn)
For Each dCol In _dt.Columns
cmd.Parameters.AddWithValue("@" & dCol.ColumnName.ToString, drow.Item(dCol.ColumnName.ToString))
Next
SqlContext.Pipe.ExecuteAndSend(cmd)
Next
success = True
Catch ex As Exception
emailLog = emailLog & System.DateTime.Now.ToString & " " & ex.ToString & vbCrLf
success = False
Finally
Try
conn.Close()
conn.Dispose()
Catch ex As Exception
success = False
End Try
End Try
End Using
Return success
End Function
View 4 Replies
View Related
Dec 18, 2007
I have a CLR stored procedure written in C#. It use context connection to connect into the SQL Server database. Since the stored procedure is handling a large amount of records, it cost a long time to complete the process. And I will got exception that indicate the connection timeout.
So can I set the value of ConnectionTimeout more large and how to set it?
Thanks!
View 3 Replies
View Related
Aug 1, 2006
Dear all,
I am trying to use SCOPE_IDENTITY() on the CLR Context Connection since it is limited to insertions in a more narrow scope than @@IDENTITY.
The connection string in the .NET Assembly is:
Using connection As New SqlConnection("context connection=true;"),
Onwards, I insert a new row to a table with an int(4) identity column, but the following returns zero (0):
Using command2 As New SqlCommand("SCOPE_IDENTITY() ", connection) Try NewTagID = CInt(command2.ExecuteScalar().ToString)
However, the following code returns the actual identity value:
Using command2 As New SqlCommand("SELECT @@IDENTITY", connection)
Try
NewTagID = CInt(command2.ExecuteScalar().ToString)
Why doesn't the SCOPE_IDENTITY() work on the context connection? In the meantime, I assume that @@IDENTITY would be the better option.
Thankful in advance for advice.
View 5 Replies
View Related
Feb 6, 2008
Hello,
I'm using a context connect inside a CLR stored procedure. The CommandTimeout property has no functionality when used with a context connection. See remarks in this url:
http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.commandtimeout.aspx
This is a problem for me because I'm at times call a link server that has been known to wait indefinitely. I've tried using a regular conneciton that supports command timeouts within my CLR stored procedure but I get a permissions error (Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0,0, Culture=neutral failed.)
How can I prevent my CLR stored procedure from waiting indefinitely?
Thanks.
View 2 Replies
View Related
Feb 7, 2008
Hi I'm trying to have 2 datareaders open at the same time with MultipleActiveResultSets=True but still getting an error saying There is already an open DataReader associated with this Command which must be closed first. Do you see what I might be doing wrong? Thanks.
<configuration> <connectionStrings> <add connectionString="user id=username;data source=servername;persist security info=True;initial catalog=corporate_mel;password=password;MultipleActiveResultSets=True;" name="corporate_mel" providerName="System.Data.SqlClient" />
Dim strConnection As String Dim sqlConn As SqlConnection strConnection = System.Configuration.ConfigurationManager.ConnectionStrings("corporate_mel").ConnectionString sqlConn = New SqlConnection(strConnection) sqlConn.Open()
This is the actual code where I'm trying to use 2 datareaders:
' Retrieve Data from database based on selections chosen in ListBox
Dim cmdCommittee As New SqlCommand("Select * from committees_tbl where committee_id in" & _
"(" & strCommitteesRemoveLast & ") order by committee_name", sqlConn)
' setup a datareader
Dim drCommittee As SqlDataReader = cmdCommittee.ExecuteReader()
' Loop through datareader and insert rows
' into the xref_person_committees_tbl
While drCommittee.Read()
Dim strCommitteeName As String = drCommittee("committee_name") 'retrieve committee_name from datareader
' Create a sql string
Dim strAddCommittee As String = String.Empty
strAddCommittee = "Insert into xref_person_committees_tbl (committee_name) values ('" & strCommitteeName & "')"
'Response.Write(strAddCommittee & "<br>")
' Create a sql command to process the insert
Dim sqlAddCommittee As New SqlCommand(strAddCommittee, sqlConn)
Dim drNewCommittee As SqlDataReader = sqlAddCommittee.ExecuteReader()
End While
View 1 Replies
View Related
Mar 3, 2014
I would like to know if we can have a generic "customer database account" to connect to the database (from a PHP layer) and then pass through the current web logged in user name for auditing purposes.
In the oracle world I would be using the setClientIdentifier function on any connection returned from a pool, but I cannot see anything obvious (to me) on the APIs to support this.
Our backup plan, because we only access table data through stored procedures, is to extend the API to have the username passed through - but this is a little ugly and less than transparent.
View 6 Replies
View Related
Nov 11, 2007
hi,
While trying to connect to SQLSever 2000 or SQLSever 2005 from a vb.net application ,
a user is getting the error "Cannot generate SSPI context" while other users
can still connect to the server.
and moreover, this user were able to connect before.
and if he restarts his computer, then try to connect after a lap of time, it will connect.
but if he waits and opens other applications,and then tries to open the application,it will not work.
so how to resolve this problem?
Regards,
View 3 Replies
View Related
Mar 15, 2008
Hi
I have a DLL deployed in SQL2005 which uses Thread pool. Since DLL exists in DB we can directly access it using context connection = true.
In my case the following works fine, if i execute the same out side Threadpool
SqlCommand cmd = new SqlCommand("select 1...", connection);
SqlContext.Pipe.ExecuteAndSend(cmd);
When i execute inside threadpool, either by passing opened connection object, or tried to open connection to "context connection = true" i am getting following error.
System.InvalidOperationException: The requested operation requires a Sql Server execution thread. The current thread was started by user code or other non-Sql Server engine code. at Microsoft.SqlServer.Server.SmiContextFactory.GetCurrentContext() at Microsoft.SqlServer.Server.SqlContext.get_CurrentContext() at Microsoft.SqlServer.Server.SqlContext.get_Pipe() at hiqidty4.hiqidty4.Identify_Search(Object identifyin)
Let me know if i am missing any more here
Thanks in advance
View 1 Replies
View Related
Oct 26, 2015
I have 2 servers. On one server i install sql server 2014 and its working fine, i am able to get connected with sql with domain account.
From second machine i want to connect my sql server, for that i create udl file when i give sql server address and click refresh getting following error: Connection failed cannot generate SSPI context
Firewall is off, created fire rule too, i am able to get ping first machine by ip and servername but still getting issue.
View 3 Replies
View Related
Dec 3, 2005
It makes no difference if I'm working with Enterprise Manager, QueryAnalyzer, Access, self written app with OleDb or Visual studio 2003builtin DB manager.Everything is fine but aufter some time - about 30 minutes I getmessage "cannot create SSPI context"After rebooting my machine I can work for another few minutes.Configuration:NB01 is my notebook with Windows XP ProSQL01 is another Windows XP Pro machine with SQL Server 2000 desktopDC01 is the domain controllerall machins within the same domain DOMAIN01The user is a domain admin and has every rightI'v seen some postings for this problem but not one useful idea how toresolve.As it runs perfectly for some time I don't thint it is a DNS problem orsomething like this.As there is no useful error message it must be some kind of bug.Has anybody resolved such a problem?
View 3 Replies
View Related
Feb 10, 2008
We have a nagging issue here in our application side, which I was trying to troubleshoot and reach no where. Can I have your valuable inputs for resolving/understanding the root cause of the issue?
3709-The connection cannot be used to perform this operation. It is either closed or invalid in this context
This error is not coming regularly (twice it happened and after the program is running successfully with out any problem). Problem having exe is running every 2 minutes interval.
Most of the sites saying this is something related to code written in the application but if that is the case why this error is not happening continuously? The problem having exe is running past 4 months with 2 minutes interval and suddenly thrown this error.
I found one MS site describing about this error but not able to find any fixes for this issue (http://support.microsoft.com/kb/839428). We are on the process of upgrading the operating system with SP2; will this help us to resolve this issue? Please advice.
Details
1. Windows 2003 with SP1
2. MDAC 2.8
3. SQL 2005 with SP1
4. VB Application.
View 1 Replies
View Related
Jul 26, 2006
How would one go about using an ODBC connection with SSIS. I'm trying to ETL some Sybase ASE data, but I get the error when I try it:
"cannot acquire a managed connection from the run-time connection manager"
This wasn't any help:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=169777&SiteID=1
View 1 Replies
View Related
May 22, 2007
Hi,
Can someone help me on this
When I run simple select query "Select * from Aadata" it works but when I filter with date it doesn't work please see below error:
Error Source : .Net SqlClient data provider
Error Messge: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding
I fixed connection properties:
Connection timeout = 30000
Execution timeout = 30000
Thanks in advance
AA
AA
View 1 Replies
View Related
May 22, 2007
Hi,
Can someone help me on this
When I run simple select query "Select * from Aadata" it works but when I filter with date it doesn't work please see below error:
Error Source : .Net SqlClient data provider
Error Messge: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding
I fixed connection properties:
Connection timeout = 30000
Execution timeout = 30000
Thanks in advance
AA
AA
View 20 Replies
View Related
Sep 5, 2007
Hello, All!
Good Afternoon!
I'm having a strange situation in my SQL SErver 2000 SP3a with 4 Processors, 4GB of RAM and high-end disk sub-system.
I migrate a specific database from another instance with SQL SErver 2000 SP3a too to this new server with more hardware resource than another one.
The application that access this DataBase stablish a connection with SQL SErver using TCP/IP, everything works fine. So, if I let the connection "sleeping", or better, if I let the connection idle for amount of time, I need to stablish the connection to ther SQL SErver again.
I hadn't the same situation in the old instance as I having. I think that it's happening because the other instance was dedicated to this application and that new Instance don't.
In this new Instance I have many other connections simultaneously, about 200 processes.
Anybody knows what's going on? Are there any settings to change this time-out configuration just for a specific connections?
Thanks
Best Regards
View 5 Replies
View Related
May 25, 2004
I am sure I am using the same connection string for all connection.open, and closing all connections as soon as possible… but
When I try to debug my web app with vs, it runs for some time then I get:
The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached exception
However, if I just access the application normally form the url (not using vs.net) it runs flawlessly.
Has some one had the same problem? i can't debug help!!
View 3 Replies
View Related
Apr 2, 2008
Hi,
I have Mirroring setup for 3 databases without a witness.
The database mirroring monitor shows me that both Principal and Mirror are "Synchronized".
SSMS also shows that the both instances are "Synchronized".
However, the following error message appears couple of times in the event viewer:
The mirroring connection to "TCP://abc.xyz.com:5022" has timed out for database "DB" after 10 seconds without a response. Check the service and network connections.
Also came across this article.
http://support.microsoft.com/kb/947462
Should i be worried?
thanks
View 9 Replies
View Related
Jun 3, 2008
Hi all
My application having connection string which having Server Name --Erlier it is working fine.
Now IP got chaged Server name is same but now Iam unable to connect server.
Please help me regarding.
View 4 Replies
View Related
Nov 30, 2006
Hi Friends,
Is it possible to create single sql connection and running several transaction at the same time? If so, can you explain?
Thanks.
View 2 Replies
View Related
Apr 2, 2004
every time we develop new dts and move it to production we have to MANUALY
modify data source.
I can submit data source as input parameter to dts and using global variable assign it connection data source.
Could it be done at run time ?
Example
if dts start on dev1 server all
connections points on dev server
if dts starts on prod server all
connection point on prod
Thank you
Alex
View 2 Replies
View Related
Jul 23, 2005
When I login using QA to my SQL Server database, it takes 15-20 secondsto establish a connection and open a query window. Drilling into adatabase via Enterprise Manager is similar. Once the connection isestablished, the server runs plenty fast however.Can someone tell me why it could take a long time for a connection tobe established?This behavior occurs when I am local on the box.Thanks,John
View 1 Replies
View Related
Feb 22, 2008
Hi Everyone,
I have been requested to create an application for a client that will use a database of my choice. I have considered using sql compact 3.5 but i feel it might be best to go ahead and start with express until the client wants to upgrade to workgroup or higher. So the problem i have is that the connection always takes 12 to 25 seconds to connect on every first connection. I guess this is because it creates an instance of the server each time. So my question: is there a way to have it ready all the time besides leaving the application open? I have it attached through management studio express on my server computer. And i need to be able to support several users reading and modifying records.
Thank you
Jeff
View 6 Replies
View Related
Dec 7, 2007
I am using MS JDBC driver 2005 1.2 and in-house written connection pooling that was working fine for a number of years. This combination worked OK with SQLserver 2000. But recently we switched to SQLServer 2005 (x64) on WIN Server 2003 Standard x64 eddition. Everything seems work OK during business hours, however, after business hours when there are lesser users and connection stays idle for sometime, I am getting the following error:
com.microsoft.sqlserver.jdbc.SQLServerException: The connection is closed.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.checkClosed(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.setAutoCommit(Unknown Source)
at com.nms.serverframework.dbaccess.OnlineTransactionManager.freeConnection(OnlineTransactionManager.java:420)
at com.nms.serverframework.dbaccess.OnlineTransactionManager.releaseConnection(OnlineTransactionManager.java:707)
at com.nms.serverframework.dbaccess.OnlineTransactionManager.releaseConnection(OnlineTransactionManager.java:688)
at com.nms.serverframework.dbaccess.OnlineTransactionManager.finalize(OnlineTransactionManager.java:399)
at java.lang.ref.Finalizer.invokeFinalizeMethod(Native Method)
at java.lang.ref.Finalizer.runFinalizer(Finalizer.java:83)
at java.lang.ref.Finalizer.access$100(Finalizer.java:14)
Any help would be appreciated
thanks in advance
View 6 Replies
View Related
Mar 30, 2007
Dear friends,
everytime i used to open the package has a sql server configuration file saved and also has loggin enabled, with password for sensitive data storage given, it shows some errors and all the time the errors are with the created connection.
yes, i have given the right password at the time of opening it.
any idea?
thanks,
View 9 Replies
View Related
Jan 15, 2007
I want to learn access connection gives how many user to connect at the same time.
http://www.narcv.com/
View 1 Replies
View Related
Jan 28, 2006
Is there a way to dynamically create a connection manager @ run time? I would like to do this from a data set of connection strings so I can link them into a union all component.
View 1 Replies
View Related
Dec 12, 2013
I'm trying to use SSIS in MSSQL 2012 to extract data from MAS90 database.The connection string is tested to be working, because I can extract successfully using the same one in Excel.I follow the wizard of import data in management studio, but after selecting the tables and mapping all those things, when clicking finish, the management studio always freezes.
View 6 Replies
View Related
Jul 17, 2006
Hi All,
I have C# application which will create normally 200 pool connection with SQL server. Previously i was using the SQL Server 2000.I don't have any issue. Recently i have upgraded the Database to SQL Server 2005 with SP1. After that problem Started. I was getting the Timoout problem and due to this my application getting crahsed.
To give about the Detail about my SQL sever 2005 Database: i have six merge Subscribers and three Transactional Publishers. I don know that "is the Replication giving all the time Out issue for my C# application?".
And i was using the below Connection String :
Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Password=sa;Data Source=192.11.11.11.
so by default i beleive it is 30 seconds as the Time Out. I don know why within this period there is no response from SQL server 2005. My application Server and Database Server are in the Same network with 100Mbps.
What is the wrong here ? Is it SQL server 2005 with the Replication gives the problem to me ?
Can anyone help me. I assumed that SQL server 2005 will work without issues and we have implemented in prod. now in prod i am getting this Error.
Thanks for your help.
View 7 Replies
View Related
Mar 17, 2006
Hi,
I installed SQL server 2005 express edition on my windows server and I had no problem with connecting it remotely through Management Studio, then I removed it and installed a full edition SQL server 2005. That's when it's gone all wrong.
Now I can't remotely connect the server through Management Studio. The error I get is the same as some of the people in this forum has which is:
"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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)"
The problem is that I have all protocols enabled and even when I disable firewall and unassign all the ip filters still no go.
I looked at te machine.config file at my server and the connection string bit goes like this:
<connectionStrings>
<add name="LocalSqlServer" connectionString="data source=.SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true" providerName="System.Data.SqlClient"/>
</connectionStrings>
Can anybody tell me wether you think the problem lies here where it says SQLEXPRESS where I actually have Sql Server 2005 installed.
I've been in and out of every documentation regarding this but they all tell me to configure the protocols and nothing else which doesn't really help.
I have tried running .Net Sql server wizard on the server but it didn't connect probably for the same reasons why I cant connect remotely.
I would really appreciate any help
Thanks
View 32 Replies
View Related
Apr 12, 2007
I have a simple package, which reads a flat file source, does some transformation, and outputs to a flat file destination. The package runs as a SQL Agent job, so I have the flat file source and OLEDB connection ticked and configured on the data sources tab.
What I would like to do is get hold of the flat file source connection string property from inside the package at run time, and use it to set the flat file destination connection string using property expressions.
The easy option is to set the destination in the agent job, but I'd like to add a date/time stamp to the destination filename.
Is this possible?
Thanks,
Greg.
View 1 Replies
View Related
Mar 31, 2006
Hi All,
I am trying to import data from flat file to sql server 2005 DB table using SSIS. There are 4 different text files in the input folder. I am using for loop to iterate reading and importing 4 times. In order to do this I have set "Connection String" property of a connection manager to a package level variable using expression. There is a script which supplies the source file name and assigns to this variable.
The package works fine. The data gets imported successfully into the destination table. However, when I close the package, reopen it and then run, the Data Flow task fails with the error "Can not open the source file". If I enter a valid file name in the Flat File Source task and run the package it works again. As soon as I close the file/package, reopen and then run, the data flow task fails with the same error.
How do I make this working. I am planning to schedule a job which will execute the package programatically. In that case no user intevention is possible.
I would appreciate your help on this. I can provide further details if required.
-SGK
View 13 Replies
View Related
