Multiple Ado Connections
Feb 19, 2004
Hi, I have a VB application running on SQL Enterprise Edition. Physically there are only 20 users loging in to the software at one time. However i can see more that 12 SPIDs for each user. The developers have been opening a new connection for each form in the application i guess. Is it normal or is there any specific requirement that foces them to do so? Shall i need to change the default WORKER-THREADS option on the server for peformance? Suggestions please!!!
Regards.
View 7 Replies
ADVERTISEMENT
May 23, 2007
Hello
Is there nay way that it is possible to connect to another database within a trigger€¦
My scenario is that an update in a table in database X triggers several updates in a table in Database Y. The databases are on the same SQL Server
Thanks for any ideas, solutions or hints you can give me..!!!
View 5 Replies
View Related
Apr 13, 2006
Hi All,
I am trying to open multiple connections in a Managed Trigger but encoutering an error as :
System.Data.SqlClient.SqlException: Transaction context in use by another session.
Below is the sample code:
public partial class Triggers
{
[Microsoft.SqlServer.Server.SqlTrigger (Name="TrgInsertContract", Target="Contracts", Event="FOR INSERT")]
public static void TrgInsertContract()
{
SqlTriggerContext triggContext = SqlContext.TriggerContext;
SqlConnection connection = new SqlConnection("context connection = true");
connection.Open();
SqlCommand command = connection.CreateCommand();
command.CommandText = "SELECT * from " + "inserted WHERE Active=1";
SqlDataReader reader;
reader = command.ExecuteReader(CommandBehavior.CloseConnection);
connection.Close();
SqlConnection connection1 = new SqlConnection("Initial Catalog=TestDB;Data Source=SHAIKDEV;User ID=sa;password=****");
connection1.Open();
}
Any help please ?????
Thanks...
View 5 Replies
View Related
Nov 3, 2005
Here's what I'm trying to accomplish:
View 5 Replies
View Related
Feb 21, 2007
I have a SQLExpress Database that I can connect to from a C# application and seperately from an application via 'OBDC' with NO problems. Both applications are running on the Local PC running XPpro.
However, I cannot connect to the same database from both applications at the same time!
Is there a way around this ?, is it a restriction of the Express version ?
View 3 Replies
View Related
Jan 3, 2008
I am using ODBC to connect to SQL Server. The documentation says that ODBC transactions are managed on the connection level and cannot span connections.
Does this mean that two instances of my code using transactions (each with its own process and a single connection) in the same machine accessing the same database will not play nice together (violate transaction rules)?
Thanks
View 3 Replies
View Related
Feb 4, 2015
If I have 100 ODBC clients all submitting the following SQL at the same time how do I know that the SQL inside the conditional only gets executed once. It appear SQL Server is preventing it from happening but was curious how. Or do I need to add something to prevent it from happening.
IF NOT EXISTS (SELECT name FROM sysobjects WHERE type='U' AND name = 'mytable)
BEGIN
CREATE table mytable (task int NOT NULL, first_name varchar(128), last_name varchar(128))
END
View 1 Replies
View Related
May 3, 2008
basically, is it inefficient to open and close a data connection everytime data needs to be retrieved or is there a way for a user to use the same conenction over multiple pages orfor multiple users to share one global data connection I just wanted to know this before I built a site where data could be constantly being pulled or put into the database.It would be easier to just keep opening and closing the connection since it just means pasting in the small chunk of code I use to do that where I need it. I hear speak of connections sometimes not closing etc and wonder if that is an issue then if you are opening and closing too many of them. I am using SqlConnection SqlCommand and SqlDatareader , my code works, just wondering if my approach lacked scalability before I find out too late and have to rewrite everything . Jim
View 11 Replies
View Related
May 17, 2005
Hi there,
Here we have got a
asp.net application that was developed when database was
sitting on SQL server 6.5. Now client has moved all of their databases
to SQL server 2000. When the database was on 6.5 the previous
development team has used oledb connections all over. As the databases
have been moved to SQL server 2000 now i am in process of changing the
database connection part. As part of the process i have a login
authorization code.
Private Function Authenticate(ByVal username As String, ByVal password As String, ByRef results As NorisSetupLib.AuthorizationResult) As Boolean
Dim conn As IDbConnection = GetConnection()
Try
Dim cmd As IDbCommand = conn.CreateCommand()
Dim sql As String = "EDSConfirmUpdate" '"EDSConfirmUpdate""PswdConfirmation"
'Dim cmd As SqlCommand = New SqlCommand("sql", conn)
cmd.CommandText = sql
cmd.CommandType = CommandType.StoredProcedure
NorisHelpers.DBHelpers.AddParam(cmd, "@logon", username)
NorisHelpers.DBHelpers.AddParam(cmd, "@password", password)
conn.Open()
'Get string for return values
Dim ReturnValue As String = cmd.ExecuteScalar.ToString
'Split string into array
Dim Values() As String = ReturnValue.Split(";~".ToCharArray)
'If the return code is CONTINUE, all is well. Otherwise, collect the
'reason why the result failed and let the user know
If Values(0) = "CONTINUE" Then
Return True
Else
results.Result = Values(0)
'Make sure there is a message being returned
If Values.Length > 1 Then
results.Message = Values(2)
End If
Return False
End If
Catch ex As Exception
Throw ex
Finally
If (Not conn Is Nothing AndAlso conn.State = ConnectionState.Open) Then
conn.Close()
End If
End Try
End Function
''' -----------------------------------------------------------------------------
''' <summary>
''' Getting the Connection from the config file
''' </summary>
''' <returns>A connection object</returns>
''' <remarks>
''' This is the same for all of the data classes.
''' Reads a specific
connection string from the web.config file for the service, creates a
connection object and returns it as an IDbConnection.
''' </remarks>
''' -----------------------------------------------------------------------------
Private Function GetConnection() As IDbConnection
'Dim conn As IDbConnection = New System.Data.OleDb.OleDbConnection
Dim conn As IDbConnection = New System.Data.SqlClient.SqlConnection
conn.ConnectionString = NorisHelpers.DBHelpers.GetConnectionString(NorisHelpers.DBHelpers.COMMON)
Return conn
End Function
in the above GetConnection() method i
have commented out the .net dataprovider for oledb and changed it to
.net dataprovider for SQLconnection. this function works fine. But in
the authenticate method above at the line
Dim ReturnValue As String = cmd.ExecuteScalar.ToString
for some reason its throwing the below error.
Run-time exception thrown : System.Data.SqlClient.SqlException - @password is not a parameter for procedure EDSConfirmUpdate.
If i comment out the
Dim conn As IDbConnection = New System.Data.SqlClient.SqlConnection
and uncomment the .net oledb provider,
Dim conn As IDbConnection = New System.Data.OleDb.OleDbConnection
then it works fine.
I also have changed the webconfig file as below.
<!--<add
key="Common" value='User ID=**secret**;pwd=**secret**;Data
Source="ESMALLDB2K";Initial Catalog=cj_common;Auto
Translate=True;Persist Security Info=False;Provider="SQLOLEDB.1";'
/>-->
<add key="Common" value='User ID=**secret**;pwd=**secret**;Data Source="ESMALLDB2K";Initial Catalog=cj_common;' />
Please help. Thanks in advance.
View 4 Replies
View Related
Jul 20, 2005
Just a quick question about connection management. My application willnever need more than 1 or 2 connections about at any given time. Also, I donot expect many users to be connected at any given time. For efficiency, Iwould like to keep connections alive throughout the lifetime of the objectsrequiring them, rather than opening a new connection, executing code andthen closing it again. What is the most efficient way of doing this?Should I perform the open/close or just one open when I create the objectand a close when I dispose of it?
View 1 Replies
View Related
Aug 10, 2015
Here is my requirement, How to handle using SSIS.
My flatfile will have multiple columns like :
IDÂ key1Â key2Â key3Â key 4
I have SP which accept 3 parameters ID, Key, Date
NOTE: Key is the coulm name from the Excel. So my sp call look like
sp_insert ID, Key1, date
sp_insert ID, Key2,date
sp_insert ID, Key3,date
View 7 Replies
View Related
Apr 15, 2014
I am facing a problem in writing the stored procedure for multiple search criteria.
I am trying to write the query in the Procedure as follows
Select * from Car
where Price=@Price1 or Price=@price2 or Price=@price=3
and
where Manufacture=@Manufacture1 or Manufacture=@Manufacture2 or Manufacture=@Manufacture3
and
where Model=@Model1 or Model=@Model2 or Model=@Model3
and
where City=@City1 or City=@City2 or City=@City3
I am Not sure of the query but am trying to get the list of cars that are to be filtered based on the user input.
View 4 Replies
View Related
Mar 3, 2008
Please can anyone help me for the following?
I want to merge multiple rows (eg. 3rows) into a single row with multip columns.
for eg:
data
Date Shift Reading
01-MAR-08 1 879.880
01-MAR-08 2 854.858
01-MAR-08 3 833.836
02-MAR-08 1 809.810
02-MAR-08 2 785.784
02-MAR-08 3 761.760
i want output for the above as:
Date Shift1 Shift2 Shift3
01-MAR-08 879.880 854.858 833.836
02-MAR-08 809.810 785.784 761.760
Please help me.
View 8 Replies
View Related
Mar 30, 2007
I am trying to create a report using Reporting Services.
My problem right now is that the way the table is constructed, I am trying to pull 3 seperate values i.e. One is the number of Hours, One is the type of work, and the 3rd is the Grade, out of one column and place them in 3 seperate columns in the report.
I can currently get one value but how to get the information I need to be able to use in my reports.
So far what I've been working with SQL Reporting Services 2005 I love it and have made several reports, but this one has got me stumped.
Any help would be appreciated.
Thanks.
I might not have made my problem quite clear enough. My table has one column labeled value. The value in that table is linked through an ID field to another table where the ID's are broken down to one ID =Number of Hours, One ID = Grade and One ID= type of work.
What I'm trying to do is when using these ID's and seperate the value related to those ID's into 3 seperate columns in a query for using in Reporting Services to create the report
As you can see, I'm attempting to change the name of the same column 3 times to reflect the correct information and then link them all to the person, where one person might have several entries in the other fields.
As you can see I can change the names individually in queries and pull the information seperately, it's when roll them altogether is where I'm running into my problem
Thanks for the suggestions that were made, I apoligize for not making the problem clearer.
Here is a copy of what I'm attempting to accomplish. I didn't have it with me last night when posting.
--Pulls the Service Opportunity
SELECT cs.value AS "Service Opportunity"
FROM Cstudent cs
INNER JOIN cattribute ca ON ca.attributeid = cs.attributeid
WHERE ca.name = 'Service Opportunity'
--Pulls the Number of Hours
SELECT cs.value AS 'Number of Hours'
FROM Cstudent cs
INNER JOIN cattribute ca ON ca.attributeid =cs.attributeid
WHERE ca.name ='Num of Hours'
--Pulls the Person Grade Level
SELECT cs.value AS 'Grade'
FROM Cstudent cs
INNER JOIN cattribute ca ON ca.attributeid =cs.attributeid
WHERE ca.name ='Grade'
--Pulls the Person Number, First and Last Name and Grade Level
SELECT s.personnumber, s.lastname, s.firstname, cs.value as "Grade"
FROM student s
INNER JOIN cperson cs ON cs.personid = s.personid
INNER JOIN cattribute ca ON ca.attributeid = cs.attributeid
WHERE cs.value =(SELECT cs.value AS 'Grade'
WHERE ca.attributeid = cs.attributeid AND ca.name='Grade')
View 11 Replies
View Related
Apr 21, 2015
I have a table with single row like below
 _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
Column0 | Column1 | Column2 | Column3 | Column4|
 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Value0Â Â Â | Value1Â Â Â | Value2Â Â Â Â | Value3Â Â Â |Â Value4Â |
Am looking for a query to convert above table data to multiple rows having column name and its value in each row as shown below
_ _ _ _ _ _ _ _
Column0 | Value0
 _ _ _ _ _ _ _ _
Column1 | Value1
 _ _ _ _ _ _ _ _
Column2 | Value2
 _ _ _ _ _ _ _ _
Column3 | Value3
 _ _ _ _ _ _ _ _
Column4 | Value4
 _ _ _ _ _ _ _ _
View 6 Replies
View Related
Jun 16, 2015
I have a requirement where in i have around 15 different flat files , filenames are fixed but folder path can be changed(i think i should use a variable for folder path). These 15 files data should go to their respective tables in the database.
Whether I need to create separate data flow task for each file or separate package? In addition to these, example : while importing product data into product table, if product ID already exists, we need to ignore it and upload only the new records.
View 4 Replies
View Related
Dec 27, 2007
Hi I have some simple questions of for MS SQL. Say I have a database with a table Called Company. In this table I have Employee's with these columns(EmployeeID<PK>, FirstName, LastName) Now I am inserting some data like this.EmployeeID FirstName, LastName------------------------------------------------------- 1 Bob Smith2 Joe Mitter Now Say I have this situation(this will look weird for this example but for another examples and the stuff I am working on it make sense). Say I have a new Employee Called Jessica White. Jessica must be the second record. So I need to insert it between the 1st and 2nd record. Like I said I am very noob at doing this stuff so I am not sure how I would do it. I first was going to my table and then went to modify. This brought up a blank grid with all my column names. I then started to add all my data in. I found out later to make my life easier I need to enter in certain spots null rows in it to help with formating(otherwise I would have had to figure out a way to do make null rows with c# code). I then went back to this grid and I tried to add a record before the data just like you if you used excel. I quickly found out you can not do that. This resulted me having to recreate the table and reput the all the data back in(this told me I was doing something very very wrong). I was then thinking of writing it with a query and I been playing around with it and still running into problems like say I had a table called test with a coloum called id<PK> and testss(yes bad names just playing around though).So if I did something like this:INSERT INTO testVALUES(2)INSERT INTO testVALUES(1)SELECT * FROM testNow I am trying to figure out how to insert something between rows 1 and 2. Would I have to drop the table or delete all the data and then run a saved copy of my script with the added change or what?My second question is what is a good site that has examples of all the commands(I don't want to read a book on it I have had a oracle sql course and well it was just boring and I did not learn too much.). I think the best way for me to learn is just continue doing my site and picking it up on the file and when I got a problem read about it and ask on the forums. Thanks
View 11 Replies
View Related
Jul 13, 2001
Very strange event. Installed new machine into NT 4 network, which has a PDC and a BDC. We have copies of all SQL databases on both the PDC and the BDC. I installed SQL 7 on new workstation. Throught Control Panel/ODBC connections I can reach the SQL files on bith the PDC and BDC. Howver when I try to use Enterprise Manager, I can connect to the tables on the PDC, but not the BDC. SQL lists both machines in the server group, but only allows me to access tables on PDC! Here is the Event information that I recived on the BDC:
The computer IVR1 tried to connect to the server NTBACKUPSERVER using the trust relationship established by the MASSCOM domain. However, the computer lost the correct security identifier (SID) when the domain was reconfigured.
How do I fix this?
View 2 Replies
View Related
Apr 14, 2000
HI all,
I have a problem here. I am having two computers both are loaded windows NT 4.0 AND SERVICE PACK 4 AND ALSO I INSTALLED SQL SERVER 7.0.
Now i want to connect those two servers , so pls anyone suggest me how i have to do.I connected both servers thru HUB.
Pls suggest me..
thank u..
--ram
View 1 Replies
View Related
Oct 9, 1998
I figure anyone who has done VB and SQL Server might have run into this.
>
> I am set up as a DBO on SQL Server with access to db1 and db2. My default
> is set to db2. From my local machine, I can log through VB(DSN-less) in
> to
> either one without a problem. BUT, if I go to another machine and try to
> log in through VB(DSN-less) to db1, it rejects my login. Here is my
> connect
> string :
>
> UID=xxxxxxx;PWD=pedro;DATABASE=db2;DRIVER={SQL
> Server};SERVER=Athens;DSN=``;
>
> The only thing that changes when I log into db1 is the database parameter-
> "DATABASE=db1".
>
> Am I doing something wrong here? Is there something that needs to be
> setup
> differently in SQL Server? Do I need to put something else into the
> connect
> string?
>
> This happens for the other developers as well, not just me.
>
> Any help would be GREATLY appreciated.
View 2 Replies
View Related
Apr 12, 2001
I am looking for some ideas on the following DTS challenge: I need to import the data from an Access 97 database into a set of tables(about 25). The location of the database can be anywhere on the users drive(s) and I will need to get the location of the database from a registry setting and use it to define the connection.
Thanks
View 1 Replies
View Related
Jun 11, 2004
We have a case where a user is using Excel sheet to connect the database on SQL2K and generally his connection runs in the upper 150 in numbers. He generally gets more than 150 connections open.
I know that it's not a good thing, but is there a way to limit it or even kill it after it reaches max?
What are the disadvantages of opening too many connections?
View 1 Replies
View Related
Sep 7, 2006
I'm getting the classic message "The timeout period elapsed prior to obtaining a connection from the pool" etc when connecting to my SQL Server 2005 Express from a .Net application.
Then I try connecting, simultaneously, from a simple ASP.net thing I wrote just for testing and this works fine. So, then the connection pool can't be full, can it? Or, does each application have its own pool??
View 6 Replies
View Related
Apr 16, 2004
Ok, yeah, I'm being lazy...
Any comments?
View 8 Replies
View Related
Feb 9, 2006
Hi
I have heard that SQL Server can support upto 10,000 users,
What if my application , connects to the sql & does not do antything then , why does the SQL server slow down with just 700 users.
I mean just the connection is established & NOTHING further is done.
Thanks
View 6 Replies
View Related
Nov 29, 2007
hi,
have been looking for ages for somewhere that might be able to answer my question so thought i'd try here.
anyhows, my place of work have goldmine 6.7 running on a server. the problem is that some of the client machines seem to be taking 2 connections to the server when logged into goldmine and some only 1.
when looking at the open sessions it shows a second for some users accessing pipesqlquery. all users show this but only some show it as a second connection.
please any ideas
View 5 Replies
View Related
Dec 21, 2007
Is there any type of performance gain to using DSN connections? Or any real advantage to using them over DSN-Less connections?
--
If I get used to envying others...
Those things about my self I pride will slowly fade away.
-Stellvia
View 5 Replies
View Related
Jul 23, 2005
As most of you are aware, when you close a VB.NET connectrion to a SQLserver, the connection doesn't actually drop right then and there.From my tests, using VB.NET 2003 and SQL2K, the connection doesn'ttimeout and drop off for 6 1/2 minutes. How can one force thisconnection to immediately drop off with code?
View 12 Replies
View Related
Oct 25, 2005
HiI am a dba for sql server recently we are facing problems with the no ofconnections.we have a database called ( x ) every day almost million users is using thatdatabse after some time the cpu showing 100 percent utilization an errorthrowing like odbc error connection so in that case no one is able to connectto the server.when we pause it then restart the server for some time it looksgood then again it will go to 100 % utilization.but there is no blockingoccuringif anyone knows this problem please helpyour help would be appreciated.Thanks--Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forum...eneral/200510/1
View 4 Replies
View Related
Dec 26, 2006
Hi I would like to know about connections of sa, where SQL store thenumber of connections
View 1 Replies
View Related
Mar 28, 2007
im writing a service that will be on a app serverthis is one of many service's to process Recordsets from Sql Server...1 service may have 20 Processes to Completeexamplemain class PreProcess()method CleanNames();method updateNames();methodr ValidateContracts();Various things must be completedSuedo Example belowMy question isin the main class make my connection and keep it for all sub membersor connect in each memberA. This Way opens connection...closes connectmethod cleanNames()1 get connection2 get recordset3. Close Connection4 process Recordset5. Open Connection4 Commit Changes6. Close Connection()finished cleanames()ormethod cleanNames()1 get connection2 get recordset3 process Recordset4 Commit Changes5. Close Connection()finished cleanames()orthis final way is in the parent Class or Calling Functionsends in the connections holds it open till the service iscompleted of all jobs its Suppose to dothen releases the connectionmethod cleanNames(oconn) connection as parameter1 get recordset2 process Recordset3 Commit Changesfinished cleanames()ThanksDaveP
View 1 Replies
View Related
Jul 7, 2007
I have a c# winapp and use SqlHelper to CRUD operation wint an sql server 2005 database, every time mi data access layer opens an connections, I do Close() and Dispose() after the transaction, and I execute SP_WHO and the database server shows me that my winapp has generated a lot of connections, I dont understand why since i clean every connection, Also, I m not using pooling since my connection string. Has someone has faced this before??
Best Regards
Joseph
View 5 Replies
View Related
Jan 29, 2007
I have .net 2.0 framework and SQL V 8.0 on my web server.
I am trying to create a DSN and I am not given the OLEDB provider for SQL as an option for my list of providers. Is there a download that will add this ? What do I do to get it????
View 3 Replies
View Related