kiran

I don't get the code of sqldatareader for connection with datagrid? Coudl you explain this in more details ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||True, that helps and fyi http://www.datadirect.com/developer/net/sqlserver_code/index.ssp

Kinda urgent Connection error messages

Websphere application for nurses license renewal application that hits our SQL Server 2000.
This was the first error that we decided to stop and start the application:
Unable to load SQL Server Data Driver driver.
Unable to create database connection.
[Microsoft][SQLServer 2000 Driver for JDBC]Error establishing socket.
com.obs.data.DataObjectException: [Microsoft][SQLServer 2000 Driver for JDBC]Err or establishing socket.
This is the send stream of errors after we had thought we had solved the problem. This is kinda urgent because there are nurses out there waiting to get their licenses renewed and mad as heck:
[Microsoft][SQLServer 2000 Driver for JDBC]Connection reset by peer: Connection reset by peer
java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Connection res et by peer: Connection reset by peer
at com.microsoft.jdbc.base.BaseExceptions.createExcep tion(Unknown
Source
)
at com.microsoft.jdbc.base.BaseExceptions.getExceptio n(Unknown
Source)
at com.microsoft.jdbc.base.BaseExceptions.getExceptio n(Unknown
Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.proces sReply(Unknown
Sour
ce)
at com.microsoft.jdbc.sqlserver.SQLServerImplStatemen t.getNextResultType
(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.commonTransi tionToState(Unknown
Source)
at com.microsoft.jdbc.base.BaseStatement.postImplExec ute(Unknown
Source)
at com.microsoft.jdbc.base.BaseStatement.commonExecut e(Unknown
Source)
at com.microsoft.jdbc.base.BaseStatement.executeUpdat eInternal(Unknown S
ource)
at com.microsoft.jdbc.base.BaseStatement.executeUpdat e(Unknown
Source)
************************************************** ********************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
Hi John. Check the DBMS error log. I believe the DBMS is in trouble. The driver is just
finding out that the socket connection it sues to the DBMS is dieing.
Joe Weinstein at BEA
jdnospam@.hevanet.com wrote:

> Websphere application for nurses license renewal application that hits our SQL Server 2000.
> This was the first error that we decided to stop and start the application:
> Unable to load SQL Server Data Driver driver.
> Unable to create database connection.
> [Microsoft][SQLServer 2000 Driver for JDBC]Error establishing socket.
> com.obs.data.DataObjectException: [Microsoft][SQLServer 2000 Driver for JDBC]Err or establishing socket.
>
> This is the send stream of errors after we had thought we had solved the problem. This is kinda urgent because there are nurses out there waiting to get their licenses renewed and mad as heck:
> [Microsoft][SQLServer 2000 Driver for JDBC]Connection reset by peer: Connection reset by peer
> java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Connection res et by peer: Connection reset by peer
> at com.microsoft.jdbc.base.BaseExceptions.createExcep tion(Unknown
> Source
> )
> at com.microsoft.jdbc.base.BaseExceptions.getExceptio n(Unknown
> Source)
> at com.microsoft.jdbc.base.BaseExceptions.getExceptio n(Unknown
> Source)
> at com.microsoft.jdbc.sqlserver.tds.TDSRequest.proces sReply(Unknown
> Sour
> ce)
> at com.microsoft.jdbc.sqlserver.SQLServerImplStatemen t.getNextResultType
> (Unknown Source)
> at com.microsoft.jdbc.base.BaseStatement.commonTransi tionToState(Unknown
> Source)
> at com.microsoft.jdbc.base.BaseStatement.postImplExec ute(Unknown
> Source)
> at com.microsoft.jdbc.base.BaseStatement.commonExecut e(Unknown
> Source)
> at com.microsoft.jdbc.base.BaseStatement.executeUpdat eInternal(Unknown S
> ource)
> at com.microsoft.jdbc.base.BaseStatement.executeUpdat e(Unknown
> Source)
>
> ************************************************** ********************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
|||Hi John. Check the DBMS error log. I believe the DBMS is in trouble. The driver is just
finding out that the socket connection it sues to the DBMS is dieing.
Joe Weinstein at BEA
jdnospam@.hevanet.com wrote:

> Websphere application for nurses license renewal application that hits our SQL Server 2000.
> This was the first error that we decided to stop and start the application:
> Unable to load SQL Server Data Driver driver.
> Unable to create database connection.
> [Microsoft][SQLServer 2000 Driver for JDBC]Error establishing socket.
> com.obs.data.DataObjectException: [Microsoft][SQLServer 2000 Driver for JDBC]Err or establishing socket.
>
> This is the send stream of errors after we had thought we had solved the problem. This is kinda urgent because there are nurses out there waiting to get their licenses renewed and mad as heck:
> [Microsoft][SQLServer 2000 Driver for JDBC]Connection reset by peer: Connection reset by peer
> java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Connection res et by peer: Connection reset by peer
> at com.microsoft.jdbc.base.BaseExceptions.createExcep tion(Unknown
> Source
> )
> at com.microsoft.jdbc.base.BaseExceptions.getExceptio n(Unknown
> Source)
> at com.microsoft.jdbc.base.BaseExceptions.getExceptio n(Unknown
> Source)
> at com.microsoft.jdbc.sqlserver.tds.TDSRequest.proces sReply(Unknown
> Sour
> ce)
> at com.microsoft.jdbc.sqlserver.SQLServerImplStatemen t.getNextResultType
> (Unknown Source)
> at com.microsoft.jdbc.base.BaseStatement.commonTransi tionToState(Unknown
> Source)
> at com.microsoft.jdbc.base.BaseStatement.postImplExec ute(Unknown
> Source)
> at com.microsoft.jdbc.base.BaseStatement.commonExecut e(Unknown
> Source)
> at com.microsoft.jdbc.base.BaseStatement.executeUpdat eInternal(Unknown S
> ource)
> at com.microsoft.jdbc.base.BaseStatement.executeUpdat e(Unknown
> Source)
>
> ************************************************** ********************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
|||Joe,
There are a ton of locks in SQL Server under Locks/Process ID that reference the web renewal update table, but we know no one is using it.
Most of the properties of these locks show this:
sp_executesql;1
or this:
BeginTrans
So you are probably right, but I don't know where to look for/which error log. Do I use Enterprise Manager or get on the SQL Server box and look at Win 2000 error log?
What would you do to clean up these locks?
John
************************************************** ********************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
|||jdnospam@.hevanet.com wrote:

> Joe,
> There are a ton of locks in SQL Server under Locks/Process ID that
> reference the web renewal update table, but we know no one is using it.
Are you doing XA transactions? If so, in-doubt transactions may be holding
locks. Your DBA should be able to associate the locks with the connection
that holds them. Are you managing transactions yourself or are they managed
by websphere?
You may have to cycle websphere to truly kill all the connections it's
made. If that doesn't clear it I'll be surprised, and you might have to cycle
the DBMS. I can tell you that nothing you've shown implicates the driver at
all. If you were using WebLogic, I'd have lots of tools to help you...

> Most of the properties of these locks show this:
> sp_executesql;1
That implies use of PreparedStatements.
> or this:
> BeginTrans
That sounds like maybe bad application code. How are these transactions started?
You should never be sending "BEGIN TRAN" SQL. You should be only using setAutoCommit()
and commit()/rollback() JDBC calls.

> So you are probably right, but I don't know where to look for/which error log. Do I use Enterprise Manager or get on the SQL Server box and look at Win 2000 error log?
> What would you do to clean up these locks?
As above. First thing is to cycle the client (websphere).
Joe Weinstein at BEA

> John
> ************************************************** ********************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
|||Hi Joe, As mentioned by John A. Davis, we are getting the following 3
different error message at a random fashion
[Microsoft][SQLServer 2000 Driver for JDBC]Error establishing socket
[Microsoft][SQLServer 2000 Driver for JDBC]Connection reset
[Microsoft][SQLServer 2000 Driver for JDBC]Connection reset by peer: socket
write error
Our prodcut is web based Java application, our application is configured
under Tomcat5 in one machine and the MS-SQL Server 2000 is running on another
machine, both the machines are in the same network in intranet. Right now we
are using MS-JDBC Driver SP2. The above listed errors occurring atlest 2 to 3
times a day, otherwise the application works fine. I am still wondering why
these error occurring in intranet based setup? any help on this appreciated.
"Joe Weinstein" wrote:

> Hi John. Check the DBMS error log. I believe the DBMS is in trouble. The driver is just
> finding out that the socket connection it sues to the DBMS is dieing.
> Joe Weinstein at BEA
> jdnospam@.hevanet.com wrote:
>
>
|||Saran wrote:

> Hi Joe, As mentioned by John A. Davis, we are getting the following 3
> different error message at a random fashion
> [Microsoft][SQLServer 2000 Driver for JDBC]Error establishing socket
> [Microsoft][SQLServer 2000 Driver for JDBC]Connection reset
> [Microsoft][SQLServer 2000 Driver for JDBC]Connection reset by peer: socket
> write error
> Our prodcut is web based Java application, our application is configured
> under Tomcat5 in one machine and the MS-SQL Server 2000 is running on another
> machine, both the machines are in the same network in intranet. Right now we
> are using MS-JDBC Driver SP2. The above listed errors occurring atlest 2 to 3
> times a day, otherwise the application works fine. I am still wondering why
> these error occurring in intranet based setup? any help on this appreciated.
Hi. I wish I could help you more, but I can tell you that from the JDBC
driver perspective, these are all unexpected problems the driver is suffering
when the raw TCP socket it is using to talk to the DBMS either dies or fails
to be created. I guarantee that the driver is making the socket the same way
each time, so every creation should work the same way, and once it's open,
it should just work until it's closed. This is a problem beyond the application
and driver. Do you hold JDBC connections open indefinitely, sometimes without using
them for a long time? Does the problem seem to occur right after a long idle
period? Maybe the DBMS has an idle connection timeout, and is closing your
connections out from under you. Also, if you do have a limit on the number of
DBMS connections you are allowed, how are you controlling the number of connections
you try to make.
Joe Weinstein at BEA
[vbcol=seagreen]
> "Joe Weinstein" wrote:
>
|||We have adapted connection pool concept from the article below, to manage
connection objects.
http://java.sun.com/developer/online...k/conpool.html
We made a log file to see how many connections objects are maintained in
given time, most of the i noticed there will be only one connection object in
the pool to server, very occassionally i could see 2 to 3 connection object
in the pool.
does the above pooling concept may have problem?
"Joe Weinstein" wrote:

>
> Saran wrote:
>
> Hi. I wish I could help you more, but I can tell you that from the JDBC
> driver perspective, these are all unexpected problems the driver is suffering
> when the raw TCP socket it is using to talk to the DBMS either dies or fails
> to be created. I guarantee that the driver is making the socket the same way
> each time, so every creation should work the same way, and once it's open,
> it should just work until it's closed. This is a problem beyond the application
> and driver. Do you hold JDBC connections open indefinitely, sometimes without using
> them for a long time? Does the problem seem to occur right after a long idle
> period? Maybe the DBMS has an idle connection timeout, and is closing your
> connections out from under you. Also, if you do have a limit on the number of
> DBMS connections you are allowed, how are you controlling the number of connections
> you try to make.
> Joe Weinstein at BEA
>
>
|||Saran wrote:

> We have adapted connection pool concept from the article below, to manage
> connection objects.
> http://java.sun.com/developer/online...k/conpool.html
> We made a log file to see how many connections objects are maintained in
> given time, most of the i noticed there will be only one connection object in
> the pool to server, very occassionally i could see 2 to 3 connection object
> in the pool.
> does the above pooling concept may have problem?
Hi. The *concept* has no problem, but the implementation may. If it is not threadsafe,
you may well have one thread closing a connection while another is using it, in which
case you could have any sort of odd failure... but, not, these symptoms are all below
the driver or pool or application. Pooling is good. I would verify if the problems
that come up are when you use a connection that's been idle in the pool for a long
time. You could easily add a checker in the pool, which does a quick "select 1" during
your call to the pool for a connection. If the test passes, the connection is as
good as you can ensure. If the query fails, the pool can replace it and give you a
new one. BEA's WebLogic connection pools do a lot of this pretesting and protection...
Joe Weinstein at BEA
[vbcol=seagreen]
> "Joe Weinstein" wrote:
>
|||All the calls to the pool are syncronized as well pool itself checks if a
connection object is NOT in use before it returns to request.
As you suggested i can run a simple query before the pool returns a
connection to a request. You said "select 1", is it a valid query to validate
a connection object or just a sample query that i need to convert to "SELECT
Column1 FROM Table1".
"Joe Weinstein" wrote:

>
> Saran wrote:
>
> Hi. The *concept* has no problem, but the implementation may. If it is not threadsafe,
> you may well have one thread closing a connection while another is using it, in which
> case you could have any sort of odd failure... but, not, these symptoms are all below
> the driver or pool or application. Pooling is good. I would verify if the problems
> that come up are when you use a connection that's been idle in the pool for a long
> time. You could easily add a checker in the pool, which does a quick "select 1" during
> your call to the pool for a connection. If the test passes, the connection is as
> good as you can ensure. If the query fails, the pool can replace it and give you a
> new one. BEA's WebLogic connection pools do a lot of this pretesting and protection...
> Joe Weinstein at BEA
>
>

Kind of Important. Connection String

I am having a problem getting into SQL 2005.
My connection string is as follows:

using System.Data.OleDb;

OleDbConnection conn = new OleDbConnection(@."Provider=SqlOleDb;Data Source=Orange;Initial Catalog=Test;");
//tried with taking out comma after Test as well.

I have tried a lot of different permutations here and I cannot get this to work.Don't post a question about an error without posting the error message.|||Is there a good reason you are using OleDB? SQLClient is optimised for SQL Server.

http://www.connectionstrings.com/sql

Killing an active connection

Is there any stored procedure to kill an active database connection?
Thanks
use sp_who to find out what sessions are running.
use kill <spid> to kill the session you want.
|||Sorry forgot to mention - if it is a long running transaction, you need
to be aware. As if you kill that, it may even take LONGER to finish
off and you cannot kill a rollback transaction.
|||Thanks so much!
"MSLam" <MelodySLam@.googlemail.com> escreveu na mensagem
news:1143580695.100118.311230@.e56g2000cwe.googlegr oups.com...
> Sorry forgot to mention - if it is a long running transaction, you need
> to be aware. As if you kill that, it may even take LONGER to finish
> off and you cannot kill a rollback transaction.
>

Killing an active connection

Is there any stored procedure to kill an active database connection?
Thanksuse sp_who to find out what sessions are running.
use kill <spid> to kill the session you want.|||Sorry forgot to mention - if it is a long running transaction, you need
to be aware. As if you kill that, it may even take LONGER to finish
off and you cannot kill a rollback transaction.|||Thanks so much!
"MSLam" <MelodySLam@.googlemail.com> escreveu na mensagem
news:1143580695.100118.311230@.e56g2000cwe.googlegroups.com...
> Sorry forgot to mention - if it is a long running transaction, you need
> to be aware. As if you kill that, it may even take LONGER to finish
> off and you cannot kill a rollback transaction.
>sql

Killing an active connection

Is there any stored procedure to kill an active database connection?
Thanksuse sp_who to find out what sessions are running.
use kill <spid> to kill the session you want.|||Sorry forgot to mention - if it is a long running transaction, you need
to be aware. As if you kill that, it may even take LONGER to finish
off and you cannot kill a rollback transaction.|||Thanks so much!
"MSLam" <MelodySLam@.googlemail.com> escreveu na mensagem
news:1143580695.100118.311230@.e56g2000cwe.googlegroups.com...
> Sorry forgot to mention - if it is a long running transaction, you need
> to be aware. As if you kill that, it may even take LONGER to finish
> off and you cannot kill a rollback transaction.
>

Killing a hung process..

A user connection which was killed went into RUNNABLE status with KILLED/ROL
LBACK cmd when we monitored with SP_WHO2.It looks like the process was hung
and no activity in CPU or I/O.How do we get rid of that process completely w
ithout restarting sql serve
r?Hi,
Normally when you kill a runnable process which does any (Update / Insert /
delete) it does a ROLLBACK to ensure that things are back as old. After
rollback that process will be removed from the process list automatically.
Thanks
Hari
MCDBA
"Mano" <anonymous@.discussions.microsoft.com> wrote in message
news:AAF19D66-EE57-4C0D-9F84-F5841FF53459@.microsoft.com...

quote:

---

> A user connection which was killed went into RUNNABLE status with

---

KILLED/ROLLBACK cmd when we monitored with SP_WHO2.It looks like the process
was hung and no activity in CPU or I/O.How do we get rid of that process
completely without restarting sql server?

Killing a hung process..

A user connection which was killed went into RUNNABLE status with KILLED/ROLLBACK cmd when we monitored with SP_WHO2.It looks like the process was hung and no activity in CPU or I/O.How do we get rid of that process completely without restarting sql server?Hi,
Normally when you kill a runnable process which does any (Update / Insert /
delete) it does a ROLLBACK to ensure that things are back as old. After
rollback that process will be removed from the process list automatically.
Thanks
Hari
MCDBA
"Mano" <anonymous@.discussions.microsoft.com> wrote in message
news:AAF19D66-EE57-4C0D-9F84-F5841FF53459@.microsoft.com...
> A user connection which was killed went into RUNNABLE status with
KILLED/ROLLBACK cmd when we monitored with SP_WHO2.It looks like the process
was hung and no activity in CPU or I/O.How do we get rid of that process
completely without restarting sql server?

Killing a hung process

A user connection which was killed went into RUNNABLE status with KILLED/ROLLBACK cmd when we monitored with SP_WHO2.It looks like the process was hung and no activity in CPU or I/O.How do we get rid of that process completely without restarting the sql server?A spid can get in a state where it can't be killed. In fact some would say
that KILL should be renamed WOUND :-)
One of the more common reasons that a process can'r be killed is if it
called an extended proc or launches an externall process that has some way
hung. Examples include xp_sendmail or xp_cmdshell. This will hang and if you
kill it it will still stay there in sysprocesses until you restart the
server. You can run KILL spid WITH STATUSONLY to get a report of how far
along the rollback is but for these unkillable spids it will report 100%
complete but never be able to kill the spid because it launched an external
process. The only way to get rid of these spids is to restart the SQL
Service however, if they are not holding any locks or keeping a transaction
open, they don't tend to do a lot of harm
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Mano" <anonymous@.discussions.microsoft.com> wrote in message
news:D32E89EF-6D00-423D-81D5-027F5390ACD2@.microsoft.com...
> A user connection which was killed went into RUNNABLE status with
KILLED/ROLLBACK cmd when we monitored with SP_WHO2.It looks like the process
was hung and no activity in CPU or I/O.How do we get rid of that process
completely without restarting the sql server?sql

Killing a hung process

A user connection which was killed went into RUNNABLE status with KILLED/ROL
LBACK cmd when we monitored with SP_WHO2.It looks like the process was hung
and no activity in CPU or I/O.How do we get rid of that process completely w
ithout restarting the sql s
erver?A spid can get in a state where it can't be killed. In fact some would say
that KILL should be renamed WOUND :-)
One of the more common reasons that a process can'r be killed is if it
called an extended proc or launches an externall process that has some way
hung. Examples include xp_sendmail or xp_cmdshell. This will hang and if you
kill it it will still stay there in sysprocesses until you restart the
server. You can run KILL spid WITH STATUSONLY to get a report of how far
along the rollback is but for these unkillable spids it will report 100%
complete but never be able to kill the spid because it launched an external
process. The only way to get rid of these spids is to restart the SQL
Service however, if they are not holding any locks or keeping a transaction
open, they don't tend to do a lot of harm
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Mano" <anonymous@.discussions.microsoft.com> wrote in message
news:D32E89EF-6D00-423D-81D5-027F5390ACD2@.microsoft.com...

quote:

---

> A user connection which was killed went into RUNNABLE status with

---

KILLED/ROLLBACK cmd when we monitored with SP_WHO2.It looks like the process
was hung and no activity in CPU or I/O.How do we get rid of that process
completely without restarting the sql server?

Kill user connection from database

I am trying out the detaching of the database and found that there are active user connection to it. The connection has lead to the failed in detaching the database. Can I know how do I kill that user connection? Thanks.

Quote:

Originally Posted by Edwintanst

I am trying out the detaching of the database and found that there are active user connection to it. The connection has lead to the failed in detaching the database. Can I know how do I kill that user connection? Thanks.

Go to Enterprise Manager, expand "Management" in the left pane, expand "Current Activity", then "Locks/Object". Click on your object, then in the right pane double-click the lock, then "Kill process".|||Thank you. I will give it a try.

Kill connection

Hello,
I do maintenance on the Back end
and have like 10 - 20 connections open...specialized scrips i run and they
dont need to be stored proc's

is there a way to kill the connection when the script is finished...from my
client side....
not just disconnect...cause server still has the pool of the connection...i
want to kill the pool'd connection also

thanks
Dave PDaveP (analizer1@.yahoo.com) writes:

Quote:

Originally Posted by

I do maintenance on the Back end
and have like 10 - 20 connections open...specialized scrips i run and they
dont need to be stored proc's
>
is there a way to kill the connection when the script is finished...from
my client side.... not just disconnect...cause server still has the
pool of the connection...i want to kill the pool'd connection also

I'm not sure that I follow. You have a maintenance job that runs from
a client that opens multiple connections?

When the client exits, all pooled connections will go away, since the
pools lives in the process space of the client, not of SQL Server.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Keyword not supported: driver

y am i having keywork not supported? is there anything wrong with my connection string ??

==========web config================

<appSettings>
<add key="db" value="icms" />
<add key="db_user" value="sqladmin" />
<add key="db_server" value="server" />
<add key="db_pwd" value="12345" />
<add key="session_timeout" value="600" />
</appSettings
==========DB.vb==============

Dim myDB As New SqlConnection
Dim myCMD As New SqlCommand
Public Sub New()
Dim db_server = AppSettings("db_server")
Dim db = AppSettings("db")
Dim db_user = AppSettings("db_user")
Dim db_pwd = AppSettings("db_pwd")
Dim DBConnection As String = "DRIVER={SQL Server};" & _
"SERVER=" & db_server & ";" & _
"DATABASE=" & db & ";" & _
"UID=" & db_user & ";" & _
"PWD=" & db_pwd & ";" & _
"OPTION=3;"
myDB.ConnectionString = DBConnection
myCMD.Connection = myDB
End Sub
=============================You're using the native SQL objects so it won't like you trying to tell it to use another driver. Plus I doubt some of the other ODBC style entries you have there are valid either.|||I changed the main differences
which i use System.Data.SQLClient, SQLConnection, SQLCommand, and SQLDataReader (instead of System.Data.Odbc, OdbcConnection, OdbcCommand, and OdbcDataReader)

i changed the entire project.. but it doesnt seems to work...

"You're using the native SQL objects so it won't like you trying to tell it to use another driver. Plus I doubt some of the other ODBC style entries you have there are valid either"

i dont quite get what u mean... can u please kindly elaborate ??
im very new to this sqlserver thingy..

thanks alot!|||The connect string you have there is for ODBC. You are not using ODBC you are using the low down and dirty SQL objects - and so you should if you're talking to MS SQL. However, when you use the SQL objects you need to use a different connection string. E.g. 'Password' instead of 'PWD'. If you look at the help for SQLConnection you'll see what it wants.|||ok i have changed it to sql connection.. now i have this strange error which i totally dont understand.. can someone help me?
thanks alot

==================Error================
An error has occurred.

Please report to us the error message
System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near 'LIMIT'. at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior) at icms.DB.q(String myStr) in C:\icms\DB.vb:line 25

=================DB.vb=====================
Imports System.Configuration.ConfigurationSettings
Imports System.Web.HttpContext
Imports System.Data.sqlclient

Public Class DB

Dim myDB As New sqlconnection
Dim myCMD As New sqlCommand

Public Sub New()

Dim db = AppSettings("db")
Dim db_user = AppSettings("db_user")
Dim db_pwd = AppSettings("db_pwd")
Dim db_datasource = AppSettings("db_datasource")
Dim DBConnection As String = "DATA Source=" & db_datasource & ";" & _
"Initial Catalog=" & db & ";" & _
"User ID=" & db_user & ";" & _
"Password=" & db_pwd & ";"
myDB.ConnectionString = DBConnection
myCMD.Connection = myDB

End Sub

Public Function q(ByVal myStr As String) As SqlDataReader
myCMD.CommandText = myStr
Try
myDB.Open()
q = myCMD.ExecuteReader(Data.CommandBehavior.CloseConnection)
Catch ex As Exception
Err(ex.ToString)
End Try

End Function
Public Sub c(ByVal mySTR As String)

Try
myCMD.Connection.Open()
myCMD.CommandText = mySTR
myCMD.ExecuteNonQuery()
myCMD.Connection.Close()
Catch ex As Exception
Err(ex.ToString)
End Try

End Sub
Private Sub Err(ByVal strError As String)
Current.Response.Write("<h1>An error has occurred.</h1><br>" & vbCrLf)
Current.Response.Write("Please report to us the error message<br>" & vbCrLf)
Current.Response.Write(strError)
Current.Response.Flush()
Current.Response.End()
End Sub
End Class
============Web Config====================

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<appSettings>
<add key="db" value="icms" />
<add key="db_user" value="sqladmin" />
<add key="db_pwd" value="12345" />
<add key="db_datasource" value="server" />
<add key="session_timeout" value="600" />
</appSettings
<system.web
<compilation defaultLanguage="vb" debug="true" />
<trace enabled="true"/>
<customErrors mode="RemoteOnly" /
</system.web
</configuration|||What SQL Command are you issuing? Oh and I didn't know you can put a space between "Data Source", learn something new everyday!|||Here is the connection string constructing described:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemDataSqlClientSqlConnectionClassConnectionStringTopic.asp

Keep one connection open for log

I am using SQL 7. If I open one connection for long, I notice that as I keep
running more SQL statement my I/O and CPU Usage keep growing. Even though I
am done with the connection not running any statement I still see those
resouces being used.
Is it a bug in SQL 7 or it happens with 2000 also. Is it a memory leak ?
Please help.
Hi
Look up 'Connection Pooling' in BOL.
The MDAC driver keeps the connection open by default 120 seconds, and if
another query, to the same saver, using the same credentials comes along on
the same client machine., it just re-uses the existing connection.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Astros" <Astros@.discussions.microsoft.com> wrote in message
news:64E68C08-BACE-44A0-9301-2476AA954CD3@.microsoft.com...
> I am using SQL 7. If I open one connection for long, I notice that as I
keep
> running more SQL statement my I/O and CPU Usage keep growing. Even though
I
> am done with the connection not running any statement I still see those
> resouces being used.
> Is it a bug in SQL 7 or it happens with 2000 also. Is it a memory leak ?
> Please help.
|||I do not agree with you at all. For SQL 7 it is not true. I saw connections
stays there as long as I do not discoonect.
You have not answered any thing of my question. My bad luck is no body else
going to answer this question.
"Astros" wrote:

> I am using SQL 7. If I open one connection for long, I notice that as I keep
> running more SQL statement my I/O and CPU Usage keep growing. Even though I
> am done with the connection not running any statement I still see those
> resouces being used.
> Is it a bug in SQL 7 or it happens with 2000 also. Is it a memory leak ?
> Please help.
|||Hi
Well, then give us more information. Give us outputs of sp_who2 over
intervals and run profiler at the same time to see what is being submitted
to SQL Server. You might find that there are requests being submitted. There
is no known bug where counters increase themselves for no reason.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Astros" <Astros@.discussions.microsoft.com> wrote in message
news:52DB2055-CAE1-42CE-A106-C55E1422D918@.microsoft.com...
> I do not agree with you at all. For SQL 7 it is not true. I saw
connections
> stays there as long as I do not discoonect.
> You have not answered any thing of my question. My bad luck is no body
else[vbcol=seagreen]
> going to answer this question.
> "Astros" wrote:
keep[vbcol=seagreen]
though I[vbcol=seagreen]
|||Hi Mike,
I don't mean to be rude the other day. My application using one connection
and doing same kind of activities again and again. Several hundred time doing
same insert for a different record. It is not cursor as far as SQL server
concern. From within the application it is repeating.
Another situation is: I open query analyser and start using it for different
type of select or updat etc. Using same connection. I see that counter for
I/O and CPU keep increasing. Not necessarily I am using more resouce
consuming SQL but I never see those resouceses being released. Unless I killl
the connection.
Aziz
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> Well, then give us more information. Give us outputs of sp_who2 over
> intervals and run profiler at the same time to see what is being submitted
> to SQL Server. You might find that there are requests being submitted. There
> is no known bug where counters increase themselves for no reason.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Astros" <Astros@.discussions.microsoft.com> wrote in message
> news:52DB2055-CAE1-42CE-A106-C55E1422D918@.microsoft.com...
> connections
> else
> keep
> though I
>
>

Keep one connection open for log

I am using SQL 7. If I open one connection for long, I notice that as I keep
running more SQL statement my I/O and CPU Usage keep growing. Even though I
am done with the connection not running any statement I still see those
resouces being used.
Is it a bug in SQL 7 or it happens with 2000 also. Is it a memory leak ?
Please help.Hi
Look up 'Connection Pooling' in BOL.
The MDAC driver keeps the connection open by default 120 seconds, and if
another query, to the same saver, using the same credentials comes along on
the same client machine., it just re-uses the existing connection.
Regards
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Astros" <Astros@.discussions.microsoft.com> wrote in message
news:64E68C08-BACE-44A0-9301-2476AA954CD3@.microsoft.com...
> I am using SQL 7. If I open one connection for long, I notice that as I
keep
> running more SQL statement my I/O and CPU Usage keep growing. Even though
I
> am done with the connection not running any statement I still see those
> resouces being used.
> Is it a bug in SQL 7 or it happens with 2000 also. Is it a memory leak ?
> Please help.|||I do not agree with you at all. For SQL 7 it is not true. I saw connections
stays there as long as I do not discoonect.
You have not answered any thing of my question. My bad luck is no body else
going to answer this question.
"Astros" wrote:
> I am using SQL 7. If I open one connection for long, I notice that as I keep
> running more SQL statement my I/O and CPU Usage keep growing. Even though I
> am done with the connection not running any statement I still see those
> resouces being used.
> Is it a bug in SQL 7 or it happens with 2000 also. Is it a memory leak ?
> Please help.|||Hi
Well, then give us more information. Give us outputs of sp_who2 over
intervals and run profiler at the same time to see what is being submitted
to SQL Server. You might find that there are requests being submitted. There
is no known bug where counters increase themselves for no reason.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Astros" <Astros@.discussions.microsoft.com> wrote in message
news:52DB2055-CAE1-42CE-A106-C55E1422D918@.microsoft.com...
> I do not agree with you at all. For SQL 7 it is not true. I saw
connections
> stays there as long as I do not discoonect.
> You have not answered any thing of my question. My bad luck is no body
else
> going to answer this question.
> "Astros" wrote:
> > I am using SQL 7. If I open one connection for long, I notice that as I
keep
> > running more SQL statement my I/O and CPU Usage keep growing. Even
though I
> > am done with the connection not running any statement I still see those
> > resouces being used.
> >
> > Is it a bug in SQL 7 or it happens with 2000 also. Is it a memory leak ?
> >
> > Please help.|||Hi Mike,
I don't mean to be rude the other day. My application using one connection
and doing same kind of activities again and again. Several hundred time doing
same insert for a different record. It is not cursor as far as SQL server
concern. From within the application it is repeating.
Another situation is: I open query analyser and start using it for different
type of select or updat etc. Using same connection. I see that counter for
I/O and CPU keep increasing. Not necessarily I am using more resouce
consuming SQL but I never see those resouceses being released. Unless I killl
the connection.
Aziz
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> Well, then give us more information. Give us outputs of sp_who2 over
> intervals and run profiler at the same time to see what is being submitted
> to SQL Server. You might find that there are requests being submitted. There
> is no known bug where counters increase themselves for no reason.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Astros" <Astros@.discussions.microsoft.com> wrote in message
> news:52DB2055-CAE1-42CE-A106-C55E1422D918@.microsoft.com...
> > I do not agree with you at all. For SQL 7 it is not true. I saw
> connections
> > stays there as long as I do not discoonect.
> >
> > You have not answered any thing of my question. My bad luck is no body
> else
> > going to answer this question.
> >
> > "Astros" wrote:
> >
> > > I am using SQL 7. If I open one connection for long, I notice that as I
> keep
> > > running more SQL statement my I/O and CPU Usage keep growing. Even
> though I
> > > am done with the connection not running any statement I still see those
> > > resouces being used.
> > >
> > > Is it a bug in SQL 7 or it happens with 2000 also. Is it a memory leak ?
> > >
> > > Please help.
>
>

Keep one connection open for log

I am using SQL 7. If I open one connection for long, I notice that as I keep
running more SQL statement my I/O and CPU Usage keep growing. Even though I
am done with the connection not running any statement I still see those
resouces being used.
Is it a bug in SQL 7 or it happens with 2000 also. Is it a memory leak ?
Please help.Hi
Look up 'Connection Pooling' in BOL.
The MDAC driver keeps the connection open by default 120 seconds, and if
another query, to the same saver, using the same credentials comes along on
the same client machine., it just re-uses the existing connection.
Regards
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Astros" <Astros@.discussions.microsoft.com> wrote in message
news:64E68C08-BACE-44A0-9301-2476AA954CD3@.microsoft.com...
> I am using SQL 7. If I open one connection for long, I notice that as I
keep
> running more SQL statement my I/O and CPU Usage keep growing. Even though
I
> am done with the connection not running any statement I still see those
> resouces being used.
> Is it a bug in SQL 7 or it happens with 2000 also. Is it a memory leak ?
> Please help.|||I do not agree with you at all. For SQL 7 it is not true. I saw connections
stays there as long as I do not discoonect.
You have not answered any thing of my question. My bad luck is no body else
going to answer this question.
"Astros" wrote:

> I am using SQL 7. If I open one connection for long, I notice that as I ke
ep
> running more SQL statement my I/O and CPU Usage keep growing. Even though
I
> am done with the connection not running any statement I still see those
> resouces being used.
> Is it a bug in SQL 7 or it happens with 2000 also. Is it a memory leak ?
> Please help.|||Hi
Well, then give us more information. Give us outputs of sp_who2 over
intervals and run profiler at the same time to see what is being submitted
to SQL Server. You might find that there are requests being submitted. There
is no known bug where counters increase themselves for no reason.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Astros" <Astros@.discussions.microsoft.com> wrote in message
news:52DB2055-CAE1-42CE-A106-C55E1422D918@.microsoft.com...
> I do not agree with you at all. For SQL 7 it is not true. I saw
connections
> stays there as long as I do not discoonect.
> You have not answered any thing of my question. My bad luck is no body
else[vbcol=seagreen]
> going to answer this question.
> "Astros" wrote:
>
keep[vbcol=seagreen]
though I[vbcol=seagreen]|||Hi Mike,
I don't mean to be rude the other day. My application using one connection
and doing same kind of activities again and again. Several hundred time doin
g
same insert for a different record. It is not cursor as far as SQL server
concern. From within the application it is repeating.
Another situation is: I open query analyser and start using it for different
type of select or updat etc. Using same connection. I see that counter for
I/O and CPU keep increasing. Not necessarily I am using more resouce
consuming SQL but I never see those resouceses being released. Unless I kill
l
the connection.
Aziz
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> Well, then give us more information. Give us outputs of sp_who2 over
> intervals and run profiler at the same time to see what is being submitted
> to SQL Server. You might find that there are requests being submitted. The
re
> is no known bug where counters increase themselves for no reason.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Astros" <Astros@.discussions.microsoft.com> wrote in message
> news:52DB2055-CAE1-42CE-A106-C55E1422D918@.microsoft.com...
> connections
> else
> keep
> though I
>
>

Keep a few connections open all the time or open/close connections on the fly?

Just a quick question about connection management. My application will
never need more than 1 or 2 connections about at any given time. Also, I do
not expect many users to be connected at any given time. For efficiency, I
would like to keep connections alive throughout the lifetime of the objects
requiring them, rather than opening a new connection, executing code and
then 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 object
and a close when I dispose of it?Robin Tucker (r.tucker@.thermoteknix.com) writes:
> Just a quick question about connection management. My application will
> never need more than 1 or 2 connections about at any given time. Also,
> I do not expect many users to be connected at any given time. For
> efficiency, I would like to keep connections alive throughout the
> lifetime of the objects requiring them, rather than opening a new
> connection, executing code and then 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 object and a close when I dispose of it?

First of all, what client library are you using?

Some client libraries, ADO and ADO.Net employs connection pooling, so
when you officially disconnects, the client library actually keeps the
connection around for some 60 seconds, in case you like to reuse it.
Connection pooling can be quite essential in web applications.

If you are writing a VB application and you know you will never have
any users connected, I don't see anything wrong in creating a connection
and keep the connection object alive as a global variable.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp