I must restart my SQL Server - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

I must restart my SQL Server

Author

Topic

rchibani
Starting Member

10 Posts

Posted - 2003-04-17 : 07:08:14

I'm using a SQLSerevr and VB program on WinXP. If I run the program for the first time after I have started the SQL Server, the results are good. But if I do-it another time the results are strange and are different from time to another until I restart the SQL Server.

Any Idea please

Thanks

Edited by - rchibani on 04/18/2003 10:08:37

samsekar
Constraint Violating Yak Guru

437 Posts

Posted - 2003-04-17 : 08:09:34

Can you please post your script?
EDIT: SQL Script

Sekar
~~~~
Success is not a destination that you ever reach. Success is the quality of your journey.

Edited by - samsekar on 04/17/2003 08:12:18

rchibani
Starting Member

10 Posts

Posted - 2003-04-17 : 08:46:47

I'm using a Visual Basic Program. ADODB Recordset and Command Objects. if you want I can send you my program

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-04-17 : 08:52:34

Post the portion where you create a recordset execute a command and close the recordset. Also post the stored procedure if that's what the command is executing and the ddl and dml required to set up a test.

One more thing describe what the intent of the code is and how many users and the frequency of use.

rchibani
Starting Member

10 Posts

Posted - 2003-04-17 : 10:19:32

Do you think that i must reinstall the SQL Server

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-17 : 10:50:18

how about just turn the box off after every request and restart it

Brett

8-)

rchibani
Starting Member

10 Posts

Posted - 2003-04-17 : 12:12:52

I have tested my program under another SQL Server instance. the same problem persist. Why?

Can any one help me please ??

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2003-04-17 : 12:14:41

because obviously your application is flawed either through design or code ... post the relevant SQL/ADO commands that are being performed, and like they said, the intent of the application...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-04-17 : 12:23:34

rchibani,

In order for any of us to help, we need to see your application (probably just the part that Valter Borges mentioned). Without that, we can not be of any help since it really could be anything that is causing your problem. So post the code and someone will get back to you shortly.

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-17 : 12:44:44

I still like the flipping the switch on and off all day long solution...

Yes and where has Valter been?

Brett

8-)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-04-17 : 12:56:16

quote:

I still like the flipping the switch on and off all day long solution...

I like that solution too.

I doubt rchibani likes that solution though, so hopefully he'll post his code.

Tara

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-04-17 : 18:34:41

Valter has been busy in Oracle, SAP, Weblogic, Java, and JSP land.

Have no fear SQL Server, Visual Studio, C++, C#, ASP still my choice of development.

However these days one must do horrible, horrible things to make a living.

rchibani
Starting Member

10 Posts

Posted - 2003-04-18 : 09:30:45

excuse me all !

I have found the stockedprocedure that causes the problem. So if I execute this procedure many times the results of my program are different from the second time of execution until I restart the Server

here is the procedure :

CREATE PROCEDURE [dbo].[ConstLegExpTrie] AS

truncate table LegExpTrie

insert into LegExpTrie
SELECT CCieTrans, NumVol, IndDup, CIataTypVol,
DtExeVol, CAerEmb, CAerDeb, DtDepRea, DtDecRea,
DtAttRea, DtArrBolcRea, CCiePropAv, CIataVerAv,
VerPhAv, VerVenAv, MatAv
From LegExportType
ORDER BY CCieTrans, NumVol, IndDup, DtDepRea
GO

I have delete the call for this procedure from my program and he seems good

the table LegExportType have no Indexes

So Can any one help Me

Thanks & excuse me

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-04-18 : 15:53:36

Why are you doing an order by on an insert?

quote:

the results of my program are different from the second time

Does your program expect some sort of order of the data from the table your inserting into. If so can you explain what you're trying to do.

Edited by - ValterBorges on 04/18/2003 15:57:57

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-04-18 : 16:03:23

Also, why are you truncating the table prior to the insert? Shouldn't this be done in a batch procedure instead?

BTW, applications should use DELETE FROM instead of TRUNCATE so that the operation is logged and can be recovered.

Tara

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-04-18 : 17:20:22

There are cases when you need only subsets of the data to be transported to other database or systems where logging is not necessary and truncate would be the way to go because it saves alot of time if there is lots of data.

However if you're just returning this data I agree there should be no reason for the trucate or to use a separate table unless your application expects the data in that specific table. In that case would it be so hard to modify the app?

rchibani
Starting Member

10 Posts

Posted - 2003-04-21 : 11:06:40

My application is expecting data sorted by "CCieTrans, NumVol, IndDup, DtDepRea" from the "LegExportTrie" table. So I'm selecting data from the "LegExportType" table and inserting this data into the "LegExportTrie" table. But first I need to delete all the rows in this table "LegExportTrie". So I use the truncate table instruction.

Do you think that I Should not use the truncate table instruction and use delete instruction instead

rchibani
Starting Member

10 Posts

Posted - 2003-04-21 : 12:11:15

Unfortunatly I have used "delete from LegExpTrie" instead of truncate table but it didn't work. my program's results are wrong from the first time of executing. I have used a radical solution : is to drop and re-create the "LegExpTrie" table

. Like this I m sure that the table is empty. So I can Select sorted data from "LegExportType" table and insert it into "LegExpTrie" table. this solution works for me and seems verry acceptable

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-04-21 : 12:11:17

Well as Valter mentioned, you can use TRUNCATE TABLE in environments where you do not care about your transactions being logged. So it's up to you really, but I always go with DELETE FROM except in when doing T-SQL in Query Analyzer in non-production environments. DELETE FROM is slower, but in production the slowness is made up by the ability to undo the transaction.

Tara

Edited by - tduggan on 04/21/2003 12:11:43

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-04-21 : 12:16:37

quote:

Unfortunatly I have used "delete from LegExpTrie" instead of truncate table but it didn't work. my program's results are wrong from the first time of executing.

What part didn't work? The result should be the same. DROP/CREATE is not the way to go, TRUNCATE TABLE is a better method than DROP/CREATE, however DELETE FROM is the best method in production environments (IMHO of course).

Tara

rchibani
Starting Member

10 Posts

Posted - 2003-04-21 : 12:48:13

Yes the results are the same but when I execute this batch procedure with Truncate or delete from .the rest of my program doesn't work (different results from time to another)only for the first time until i restart the Sqlserver this is why I have used Drop/create

Public Sub Routing()
Dim Routing As New ADODB.Recordset

Dim Temp As Date
Dim DtArrBolcRea As Date
Dim Cmp As Integer
Dim i, j As Byte
Dim EscA As String
Dim cnx As String

//Here my batch procedure to Truncate and load the LegExpTrie table as shown in the bellow messages

cnx = cn.ConnectionString
Adodc1.ConnectionString = cnx
Adodc2.ConnectionString = cnx

Adodc1.RecordSource = "dbo.LegExpTrie"
Adodc2.RecordSource = "dbo.LegExpTrie"

Adodc1.Refresh
Adodc2.Refresh

Adodc1.Recordset.MoveFirst
Adodc2.Recordset.MoveFirst

Form1.ProgressBar1.Value = 0
Form1.ProgressBar1.Max = Adodc1.Recordset.RecordCount

Routing.Open "truncate table dbo.Routing", cn, adOpenDynamic, adLockPessimistic
Routing.Open "dbo.Routing", cn, adOpenDynamic, adLockPessimistic

Unload Attente

Do While Not Adodc1.Recordset.EOF

Routing.AddNew
Routing("CCieTrans") = Adodc1.Recordset("CCieTrans").Value
Routing("NumVol") = Adodc1.Recordset("NumVol")
Routing("IndDup") = Adodc1.Recordset("IndDup")
Routing("DtExeVol") = Adodc1.Recordset("DtExeVol")
Routing("CIataTypVol") = Adodc1.Recordset("CIataTypVol")
Routing("CCiePropAv") = Adodc1.Recordset("CCiePropAv")
Routing("CIataVerAv") = Adodc1.Recordset("CIataVerAv")
Routing("MatAv") = Adodc1.Recordset("MatAv")
Routing("VerPhAv") = Adodc1.Recordset("VerPhAv")
Routing("VerVenAv") = Adodc1.Recordset("VerVenAv")
Routing("CEsc1") = Adodc1.Recordset("CAerEmb")
Routing("CEsc2") = Adodc1.Recordset("CAerDeb")
EscA = Adodc1.Recordset("CAerDeb")
Routing("DtDepRea") = Adodc1.Recordset("DtDepRea")
Routing("DtArrBolcRea") = Adodc1.Recordset("DtArrBolcRea")
DtArrBolcRea = Adodc1.Recordset("DtArrBolcRea")
Routing("HDVAirborn") = Format(Adodc1.Recordset("DtAttRea") - Adodc1.Recordset("DtDecRea"), "hh:mm")
Routing("HDVBlock") = Format(Adodc1.Recordset("DtArrBolcRea") - Adodc1.Recordset("DtDepRea"), "hh:mm")
DoEvents

If (Adodc1.Recordset.AbsolutePosition <> Adodc1.Recordset.RecordCount) Then

j = 3
For i = Adodc1.Recordset.AbsolutePosition + 1 To Adodc1.Recordset.AbsolutePosition + 3

If Adodc2.Recordset.EOF Then Exit For

Adodc2.Recordset.AbsolutePosition = i

If Not (Adodc1.Recordset("CCieTrans") = Adodc2.Recordset("CCieTrans") And _
Adodc1.Recordset("NumVol") = Adodc2.Recordset("NumVol") And _
Adodc1.Recordset("IndDup") = Adodc2.Recordset("IndDup") And _
Adodc1.Recordset("DtExeVol") = Adodc2.Recordset("DtExeVol") And _
Adodc1.Recordset("CIataTypVol") = Adodc2.Recordset("CIataTypVol") And _
EscA = Adodc2.Recordset("CAerEmb") And _
DtArrBolcRea <= Adodc2.Recordset("DtDepRea")) Then

Exit For

End If
Routing("CEsc" & j) = Adodc2.Recordset("CAerDeb")
EscA = Adodc2.Recordset("CAerDeb")
Routing("DtArrBolcRea") = Adodc2.Recordset("DtArrBolcRea")
DtArrBolcRea = Adodc2.Recordset("DtArrBolcRea")
Temp = Format(Adodc2.Recordset("DtAttRea") - Adodc2.Recordset("DtDecRea"), "hh:mm")
Routing("HDVAirborn") = Routing("HDVAirborn") + Temp
Temp = Format(Adodc2.Recordset("DtArrBolcRea") - Adodc2.Recordset("DtDepRea"), "hh:mm")
Routing("HDVBlock") = Routing("HDVBlock") + Temp
DoEvents
Form1.ProgressBar1.Value = Form1.ProgressBar1.Value + 1
j = j + 1

Next
Adodc1.Recordset.AbsolutePosition = Adodc2.Recordset.AbsolutePosition
Routing.Update
Form1.ProgressBar1.Value = Form1.ProgressBar1.Value + 1
Else
Routing.Update
Form1.ProgressBar1.Value = Form1.ProgressBar1.Value + 1
Exit Do
End If

Loop

Adodc1.Recordset.Close
Adodc2.Recordset.Close
Form1.ProgressBar1.Value = 0
End Sub

Edited by - rchibani on 04/21/2003 12:49:52

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources