connection pooling

Remove the "fix minor problems" option of the integrity part.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jason" <jasonlewis@.hotmail.com> wrote in message news:OHtJLbEkGHA.2200@.TK2MSFTNGP05.phx.gbl
..
> Hi,
> Is there a way to do maintenance like integrity checks if there is still
> a (sleeping)connection to a database? My maintenance jobs where you need
> to be in single user mode fails. In our multi-tier environment we use an
> applicationserver which uses connection pooling and a databaseserver
> (SQL2K).
> I've looked at dbcc opentran, but that doesn't work for me. The solution
> i'm looking for is to check if there are any connections for a
> particular database. If so, i want to disconnect it, but leave it in a
> state so that the applicationserver doesn't have to restart it's
> services (this is a manual proces).Tibor Karaszi wrote:
> Remove the "fix minor problems" option of the integrity part.
>
Hi Tibor,
If i do that, how do i know if there are any errors to be fixed? I've
read that in sql 2005 that option is removed.|||he purpose of DBCC CHECKDB is to *find* errors. The command will return erro
r messages which will
cause the job to fail which you can be alerted for.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jason" <jasonlewis@.hotmail.com> wrote in message news:uYBZsQFkGHA.1296@.TK2MSFTNGP05.phx.gbl
..
> Tibor Karaszi wrote:
> Hi Tibor,
> If i do that, how do i know if there are any errors to be fixed? I've read
that in sql 2005 that
> option is removed.
>|||Hi,
Is there a way to do maintenance like integrity checks if there is still
a (sleeping)connection to a database? My maintenance jobs where you need
to be in single user mode fails. In our multi-tier environment we use an
applicationserver which uses connection pooling and a databaseserver
(SQL2K).
I've looked at dbcc opentran, but that doesn't work for me. The solution
i'm looking for is to check if there are any connections for a
particular database. If so, i want to disconnect it, but leave it in a
state so that the applicationserver doesn't have to restart it's
services (this is a manual proces).|||Remove the "fix minor problems" option of the integrity part.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jason" <jasonlewis@.hotmail.com> wrote in message news:OHtJLbEkGHA.2200@.TK2MSFTNGP05.phx.gbl
..
> Hi,
> Is there a way to do maintenance like integrity checks if there is still
> a (sleeping)connection to a database? My maintenance jobs where you need
> to be in single user mode fails. In our multi-tier environment we use an
> applicationserver which uses connection pooling and a databaseserver
> (SQL2K).
> I've looked at dbcc opentran, but that doesn't work for me. The solution
> i'm looking for is to check if there are any connections for a
> particular database. If so, i want to disconnect it, but leave it in a
> state so that the applicationserver doesn't have to restart it's
> services (this is a manual proces).|||Tibor Karaszi wrote:
> Remove the "fix minor problems" option of the integrity part.
>
Hi Tibor,
If i do that, how do i know if there are any errors to be fixed? I've
read that in sql 2005 that option is removed.|||he purpose of DBCC CHECKDB is to *find* errors. The command will return erro
r messages which will
cause the job to fail which you can be alerted for.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jason" <jasonlewis@.hotmail.com> wrote in message news:uYBZsQFkGHA.1296@.TK2MSFTNGP05.phx.gbl
..
> Tibor Karaszi wrote:
> Hi Tibor,
> If i do that, how do i know if there are any errors to be fixed? I've read
that in sql 2005 that
> option is removed.
>

Connection Issue to SQL Express form VB Express

Anyone know how to fix this error:

System.Data.SqlClient.SqlException was unhandled
Class=16
ErrorCode=-2146232060
LineNumber=65536
Message="Directory lookup for the file "C:\Documents and Settings\zimmerman_e\My Documents\Visual Studio 2005\Projects\MGM_REPORTS\MGM_REPORTS\ImportFile.mdf" failed with the operating system error 5(Access is denied.).
Could not attach file 'C:\Documents and Settings\zimmerman_e\My Documents\Visual Studio 2005\Projects\MGM_REPORTS\MGM_REPORTS\ImportFile.mdf' as database 'dbname'."
Number=5133
Procedure=""
Server=".\SQLExpress"
Source=".Net SqlClient Data Provider"
State=1
StackTrace:
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at MGM_REPORTS.DbConnect.InsertSQL(String[] Values) in C:\Documents and Settings\zimmerman_e\My Documents\Visual Studio 2005\Projects\MGM_REPORTS\MGM_REPORTS\DbConnect.vb:line 149
at MGM_REPORTS.DbConnect.btnConnectDB_Click(Object sender, EventArgs e) in C:\Documents and Settings\zimmerman_e\My Documents\Visual Studio 2005\Projects\MGM_REPORTS\MGM_REPORTS\DbConnect.vb:line 21
at System.Windows.Forms.Control.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ButtonBase.WndProc(Message& m)
at System.Windows.Forms.Button.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.Run(Form mainForm)
at MGM_REPORTS.DbConnect.Main() in C:\Documents and Settings\zimmerman_e\My Documents\Visual Studio 2005\Projects\MGM_REPORTS\MGM_REPORTS\DbConnect.vb:line 158
at System.AppDomain.nExecuteAssembly(Assembly assembly, String[] args)
at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()

Coudl you paste your connection string here ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Hi Jens, I think I got the connection to work but now I don't think its updating my database. Here is the entire code...

Imports System.Data

Imports System.Data.SqlClient

Public Class DbConnect

Dim objConnection As New SqlClient.SqlConnection("Data Source=.\SQLExpress;Integrated Security=true;AttachDbFilename=|DataDirectory|\ImportFile.mdf;User Instance=true;")

Dim objDataAdapter As New SqlDataAdapter()

Dim objDataSet As New DataSet()

Private Sub btnConnectDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnConnectDB.Click

'parse fixed width file

Using Reader As New Microsoft.VisualBasic.FileIO.TextFieldParser("\\netapp2\rs-common-d\AAA-MGM_990500024\Payrolls\Test.DAT")

Reader.TextFieldType = Microsoft.VisualBasic.FileIO.FieldType.FixedWidth

Reader.SetFieldWidths(9, 9, 35, 10, 35, 10, 10, 10, 10, 2, 8, 8, 5, 5, _

5, 8, 8, 30, 30, 3, 35, 2, 9, 9, 4, 10, 10, 3, 3, 10, 10, 10, 10, 10, _

1, 4, 5, 1, 1, 10, 4, 9, 15, 7, 1, 5, 7, 8, 2, 1, 2, 1, 8, 1, -1)

While Not Reader.EndOfData

Try

Dim Fields() As String = Reader.ReadFields

InsertSQL(Fields)

Catch ex As Microsoft.VisualBasic.FileIO.MalformedLineException

MsgBox("Line " & ex.Message & _

" is not valid and will be skipped.")

Exit While

End Try

End While

End Using

End Sub

Private Sub InsertSQL(ByVal Values() As String)

Dim cmd As New SqlClient.SqlCommand()

cmd.CommandText = "INSERT INTO MgmPayroll(PlanId,SSN,FirstName,MiddleName,LastName,Elec,Comp,GrossComp,Loans,Filler,PayrollDate,TermDate,Filler2,DivisionCode,Department,DOH,DOB,Address1,Address2,CountryCode,City,State,Zip,EmpNum,Location,Match,PayRate,PayFreq,Filler3,Elec2,PriorYearComp,YTDComp,YTDEligComp,Loan2,UnionMember,UnionCode,StatusCode,Filler4,MGMExecs,PhoneNum,LocationNew,EmpNumNew,LoanId,YTDHours,PayrollFreq,PensionCode,PeriodHours,RehireDate,Filler5,FilingStatus,Exemptions,OriginalStatus,SenorityDate,MaritalStatus,Roth)Values(@.PlanId,@.SSN,@.FirstName,@.MiddleName,@.LastName,@.Elec,@.Comp,@.GrossComp,@.Loans,@.Filler,@.PayrollDate,@.TermDate,@.Filler2,@.DivisionCode,@.Department,@.DOH,@.DOB,@.Address1,@.Address2,@.CountryCode,@.City,@.State,@.Zip,@.EmpNum,@.Location,@.Match,@.PayRate,@.PayFreq,@.Filler3,@.Elec2,@.PriorYearComp,@.YTDComp,@.YTDEligComp,@.Loan2,@.UnionMember,@.UnionCode,@.StatusCode,@.Filler4,@.MGMExecs,@.PhoneNum,@.LocationNew,@.EmpNumNew,@.LoanId,@.YTDHours,@.PayrollFreq,@.PensionCode,@.PeriodHours,@.RehireDate,@.Filler5,@.FilingStatus,@.Exemptions,@.OriginalStatus,@.SenorityDate,@.MaritalStatus,@.Roth)"

cmd.Connection = objConnection

Dim p1 As New SqlClient.SqlParameter("@.PlanId", Values(0))

Dim p2 As New SqlClient.SqlParameter("@.SSN", Values(1))

Dim p3 As New SqlClient.SqlParameter("@.FirstName", Values(2))

Dim p4 As New SqlClient.SqlParameter("@.MiddleName", Values(3))

Dim p5 As New SqlClient.SqlParameter("@.LastName", Values(4))

Dim p6 As New SqlClient.SqlParameter("@.Elec", Values(5))

Dim p7 As New SqlClient.SqlParameter("@.Comp", Values(6))

Dim p8 As New SqlClient.SqlParameter("@.GrossComp", Values(7))

Dim p9 As New SqlClient.SqlParameter("@.Loans", Values(8))

Dim p10 As New SqlClient.SqlParameter("@.Filler", Values(9))

Dim p11 As New SqlClient.SqlParameter("@.PayrollDate", Values(10))

Dim p12 As New SqlClient.SqlParameter("@.TermDate", Values(11))

Dim p13 As New SqlClient.SqlParameter("@.Filler2", Values(12))

Dim p14 As New SqlClient.SqlParameter("@.DivisionCode", Values(13))

Dim p15 As New SqlClient.SqlParameter("@.Department", Values(14))

Dim p16 As New SqlClient.SqlParameter("@.DOH", Values(15))

Dim p17 As New SqlClient.SqlParameter("@.DOB", Values(16))

Dim p18 As New SqlClient.SqlParameter("@.Address1", Values(17))

Dim p19 As New SqlClient.SqlParameter("@.Address2", Values(18))

Dim p20 As New SqlClient.SqlParameter("@.CountryCode", Values(19))

Dim p21 As New SqlClient.SqlParameter("@.City", Values(20))

Dim p22 As New SqlClient.SqlParameter("@.State", Values(21))

Dim p23 As New SqlClient.SqlParameter("@.Zip", Values(22))

Dim p24 As New SqlClient.SqlParameter("@.EmpNum", Values(23))

Dim p25 As New SqlClient.SqlParameter("@.Location", Values(24))

Dim p26 As New SqlClient.SqlParameter("@.Match", Values(25))

Dim p27 As New SqlClient.SqlParameter("@.PayRate", Values(26))

Dim p28 As New SqlClient.SqlParameter("@.PayFreq", Values(27))

Dim p29 As New SqlClient.SqlParameter("@.Filler3", Values(28))

Dim p30 As New SqlClient.SqlParameter("@.Elec2", Values(29))

Dim p31 As New SqlClient.SqlParameter("@.PriorYearComp", Values(30))

Dim p32 As New SqlClient.SqlParameter("@.YTDComp", Values(31))

Dim p33 As New SqlClient.SqlParameter("@.YTDEligComp", Values(32))

Dim p34 As New SqlClient.SqlParameter("@.Loan2", Values(33))

Dim p35 As New SqlClient.SqlParameter("@.UnionMember", Values(34))

Dim p36 As New SqlClient.SqlParameter("@.UnionCode", Values(35))

Dim p37 As New SqlClient.SqlParameter("@.StatusCode", Values(36))

Dim p38 As New SqlClient.SqlParameter("@.Filler4", Values(37))

Dim p39 As New SqlClient.SqlParameter("@.MGMExecs", Values(38))

Dim p40 As New SqlClient.SqlParameter("@.PhoneNum", Values(39))

Dim p41 As New SqlClient.SqlParameter("@.LocationNew", Values(40))

Dim p42 As New SqlClient.SqlParameter("@.EmpNumNew", Values(41))

Dim p43 As New SqlClient.SqlParameter("@.LoanId", Values(42))

Dim p44 As New SqlClient.SqlParameter("@.YTDHours", Values(43))

Dim p45 As New SqlClient.SqlParameter("@.PayrollFreq", Values(44))

Dim p46 As New SqlClient.SqlParameter("@.PensionCode", Values(45))

Dim p47 As New SqlClient.SqlParameter("@.PeriodHours", Values(46))

Dim p48 As New SqlClient.SqlParameter("@.RehireDate", Values(47))

Dim p49 As New SqlClient.SqlParameter("@.Filler5", Values(48))

Dim p50 As New SqlClient.SqlParameter("@.FilingStatus", Values(49))

Dim p51 As New SqlClient.SqlParameter("@.Exemptions", Values(50))

Dim p52 As New SqlClient.SqlParameter("@.OriginalStatus", Values(51))

Dim p53 As New SqlClient.SqlParameter("@.SenorityDate", Values(52))

Dim p54 As New SqlClient.SqlParameter("@.MaritalStatus", Values(53))

Dim p55 As New SqlClient.SqlParameter("@.Roth", Values(54))

cmd.Parameters.Add(p1)

cmd.Parameters.Add(p2)

cmd.Parameters.Add(p3)

cmd.Parameters.Add(p4)

cmd.Parameters.Add(p5)

cmd.Parameters.Add(p6)

cmd.Parameters.Add(p7)

cmd.Parameters.Add(p8)

cmd.Parameters.Add(p9)

cmd.Parameters.Add(p10)

cmd.Parameters.Add(p11)

cmd.Parameters.Add(p12)

cmd.Parameters.Add(p13)

cmd.Parameters.Add(p14)

cmd.Parameters.Add(p15)

cmd.Parameters.Add(p16)

cmd.Parameters.Add(p17)

cmd.Parameters.Add(p18)

cmd.Parameters.Add(p19)

cmd.Parameters.Add(p20)

cmd.Parameters.Add(p21)

cmd.Parameters.Add(p22)

cmd.Parameters.Add(p23)

cmd.Parameters.Add(p24)

cmd.Parameters.Add(p25)

cmd.Parameters.Add(p26)

cmd.Parameters.Add(p27)

cmd.Parameters.Add(p28)

cmd.Parameters.Add(p29)

cmd.Parameters.Add(p30)

cmd.Parameters.Add(p31)

cmd.Parameters.Add(p32)

cmd.Parameters.Add(p33)

cmd.Parameters.Add(p34)

cmd.Parameters.Add(p35)

cmd.Parameters.Add(p36)

cmd.Parameters.Add(p37)

cmd.Parameters.Add(p38)

cmd.Parameters.Add(p39)

cmd.Parameters.Add(p40)

cmd.Parameters.Add(p41)

cmd.Parameters.Add(p42)

cmd.Parameters.Add(p43)

cmd.Parameters.Add(p44)

cmd.Parameters.Add(p45)

cmd.Parameters.Add(p46)

cmd.Parameters.Add(p47)

cmd.Parameters.Add(p48)

cmd.Parameters.Add(p49)

cmd.Parameters.Add(p50)

cmd.Parameters.Add(p51)

cmd.Parameters.Add(p52)

cmd.Parameters.Add(p53)

cmd.Parameters.Add(p54)

cmd.Parameters.Add(p55)

objConnection.Open()

cmd.ExecuteNonQuery()

objConnection.Close()

End Sub

<STAThread()> _

Shared Sub Main()

Application.EnableVisualStyles()

Application.Run(New DBConnect)

End Sub

End Class

|||Use proper exception handling around the database call to encapsulate and catch the error message.

Try
objConnection.Open()

cmd.ExecuteNonQuery()

Catch e As Exception
Response.Write(e.Message.toString)
End Try

(Hope this is right as I am not a VB guy :-) )

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Connecting Win 2000 Server to a Win 98 machine

I am stumped. I am trying to fix this for a client. My client is running a Win 2000 Server with MSDE. She is networked to a Win 98SE machine.
I can ping both computers, and I can use a UDL file to test connection on the Server and it works, but I try to use the UDL file on the Win 98 machine to connect to the DB on the Server and I get a error message:

DBNET LIB (Connection Open) Speicfy Server Not Found.

I have checked the Cliconfig on the 98 box and the port number is the same as the Server. What should I do? Does MSDE and Win 98 only work on a system to system basis? Or does she just need to update to a Win XP or 2000 Pro.You may want to check the MDAC level on the Win98 machine. I think anything before MDAC 2.5 was unable to find instances of SQL 2000, which is how MSDE usually installs.