July 28, 2008 at 11:49 pm
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.
July 29, 2008 at 4:14 am
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
July 29, 2008 at 4:24 am
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
July 29, 2008 at 1:11 pm
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.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy