Statement goes missing after GO!

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

SQL Server 2000 Forums

SQL Server Development (2000)

Statement goes missing after GO!

Author

Topic

matt_calhoon
Posting Yak Master

235 Posts

Posted - 2003-02-22 : 19:38:42

Hi there,

I have the following stored procedure which I created in Enterprise manager. After clicking on OK to create the procedure - if I open it again the sql statement after the first GO is deleted. If I get rid of the GO it works fine.

CREATE PROCEDURE p_UPDATE_TBLLEADERBOARD AS

TRUNCATE TABLE TBLLEADERBOARD
GO

INSERT INTO tblLEADERBOARD

(RANK, MARGIN, TOTALWINNINGTIPS, MEMBERID, FIRSTNAME, SURNAME, NICKNAME, TITLE, URL, COMPANYID)

SELECT Rank = COUNT (*),
S1.MARGIN, S1.TOTALWINNINGTIPS, S1.memberid, S1.Firstname, S1.Surname, S1.NICKNAME, S1.TITLE, S1.URL, S1.companyid
FROM vw_MARGINS_AND_TIPS AS S1, (SELECT DISTINCT * FROM (SELECT MARGIN, TOTALWINNINGTIPS FROM vw_MARGINS_AND_TIPS)
AS S3) AS S2
WHERE S2.TOTALWINNINGTIPS > S1.TOTALWINNINGTIPS OR (S2.TOTALWINNINGTIPS = S1.TOTALWINNINGTIPS AND S2.MARGIN <= S1.MARGIN)
GROUP BY S1.MARGIN, S1.TOTALWINNINGTIPS, S1.memberid, S1.Firstname, S1.Surname, S1.NICKNAME, S1.TITLE, S1.URL, S1.companyid
GO

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2003-02-22 : 19:55:16

GO isn't a TSQL command. It is used to split up batches in Query Analyzer.

When EM sees the go, it terminates the Create Procedure batch.

Damian

matt_calhoon
Posting Yak Master

235 Posts

Posted - 2003-02-22 : 20:52:11

Thanks for your reply Damian,

Another question - this procedure takes 7 seconds to run in query analyser but when executed from an .asp page it takes 50 seconds! I have tried different ways to call such as:

Dim objCommand
Set objCommand = CreateObject("ADODB.Command")
With objCommand
.CommandText = "p_UPDATE_TBLLEADERBOARD"
.CommandType = adCmdStoredProc
.ActiveConnection = conn
.Execute
End With
set objCommand = nothing

or just the usual short way:

conn.execute("p_UPDATE_TBLLEADERBOARD")

but both take just as long. Surely the time taken should be much closer to the time taken in query analyser?

matt_calhoon
Posting Yak Master

235 Posts

Posted - 2003-02-22 : 21:10:43

ignore my last post - sql profiler gave me clues to exactly what I was doing wrong (My execs statements where in a loop I didnt notice)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources