SQLDMO ExecuteWithResultsAndMessages2 Method

Question

SQLDMO ExecuteWithResultsAndMessages2 Method

govind.sharma

SSC Enthusiast

Points: 111
More actions
July 28, 2008 at 11:49 pm

#122686

I Used SQLServer 2000/2005 with VB 6.0. I used SQLDMO in My Application.
I try to Execute a batch script with SQLServer2.ExecuteWithResultsAndMessages2 method. but every time i got error message "incorrect syntax near 'GO'"
Full Code is:
Dim sSqlServer As New SQLDMO.SQLServer2
Dim sDatabase As SQLDMO.Database2
Dim qResult As SQLDMO.QueryResults2
Dim sMsg As String
Dim sCommand As String
Call sSqlServer.Connect("Server", "UID", "PWD")
Set sDatabase = sSqlServer.Databases("MyDB")
sCommand = "/*MyScript.SQL*/" & vbCrLf
sCommand = sCommand & "SET NOCOUNT ON" & vbCrLf
sCommand = sCommand & "SELECT * FROM MyTable" & vbCrLf
sCommand = sCommand & "Print Cast(@@RowCount As VarChar) + ' Record(s) Found'" & vbCrLf
sCommand = sCommand & "SET NOCOUNT OFF" & vbCrLf
sCommand = sCommand & " GO"
Set qResult = sDatabase.ExecuteWithResultsAndMessages2(sCommand, sMsg, Len(sCommand))
Msgbox sMsg
Msgbox qResult.Rows
Pls any one solve my problem.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

GO is not a T-SQL command. It's a client tool statement that signals the end of a batch. You don't need it here.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

govind.sharma SSC Enthusiast Points: 111 More actions · Answer 2

Thnaks for your suggestion. But often I used multiple batches in one Script file. Such as

sCommand = "/*MyScript.SQL*/" & vbCrLf

sCommand = "SELECT * FROM MyTable" & vbCrLf

sCommand = sCommand & "Print Cast(@@RowCount As VarChar) + ' Record(s) Found'" & vbCrLf

sCommand = sCommand & " GO”

sCommand = sCommand & "SELECT * INTO MyTable2 FROM MyTable" & vbCrLf

sCommand = sCommand & "Print Cast(@@RowCount As VarChar) + ' Record(s) Inserted in MyTable2 " & vbCrLf

sCommand = sCommand & " GO”

sCommand = sCommand & "SELECT * INTO MyTable3 FROM MyTable" & vbCrLf

sCommand = sCommand & "Print Cast(@@RowCount As VarChar) + ' Record(s) Inserted in MyTable3" & vbCrLf

sCommand = sCommand & " GO”

I have many script file which contain Go Statement. So I cant ignore it.

Pls tell me what is the means of sSqlServer.CommandTerminator

Venkat Manohar SSC Enthusiast Points: 193 More actions · Answer 3

I think your problem can be solved if you try as per below article.

You should declare qResult as string.

http://support.microsoft.com/kb/279514

I had been trying the same script from DTS packages. since we use VBSCript in it, we can't even declare it as string. Still trying to find out a solution.

My aim is to create a job against so many servers. More servers may get added to the list. Still trying to figure out how to solve my problem.