Hello,
Im writing an application which calls stored procedure in MSSQL 2008. This stored procedure inserts line into the table and returns the line number that is generated by SQL.

INSERT INTO ...
SELECT @@IDENTITY AS [PID]

If I run it from management studio, there is no problem, the line is added and right number of line is returned. But if run the same command from VB6 using a ADODB.Connection object, execution of the query fails with the error 'Incorrect syntax near 'GO''
Im using code that is generated by SQL management studio for the stored procedure and for calling it too.

For the line separation Im using the constant vbCrLf. At first look the queries are the same, what can be wrong?

__________________
JVV

If you are calling a SP, what are you using vbCrLf for???

__________________
HTH

Gonzalo

================================================== =========
Read this if you want to know how to get a correct reply for your question.
(Took that from Doug signature and he Took that from Peter profile)
================================================== =========
My programs achieved a new certification :
WORKS ON MY MACHINE
================================================== =========
I know that CVS was evil, and now i got the proof.
================================================== =========

In execution of SP there is two GO - generated by Management studio, GO must be on single line, or not? I will post the code after one hour, when I will arrive at home.

__________________
JVV

There is code of SP

USE [TrapDB]
GO
/****** Object: StoredProcedure [dbo].[LBLPRT_AddPrintJob] Script Date: 05/11/2009 16:46:31 ******/
SETANSI_NULLSON
GO
SETQUOTED_IDENTIFIERON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTERPROCEDURE [dbo].[LBLPRT_AddPrintJob]
-- Add the parameters for the stored procedure here
@PrintServer nvarchar(255),
@PrinterName nvarchar(255),
@PrintedBy nvarchar(255),
@PrintedFrom nvarchar(255),
@PrintIssued nvarchar(255),
@PrintData binary(4096)
AS
BEGIN
INSERTINTO LabelPrints(PrintServer,PrinterName,PrintedBy,PrintedFrom,PrintIssued,PrintData)
VALUES (@PrintServer,@PrinterName,@PrintedBy,@PrintedFrom,@PrintIssued,@PrintData)
SELECT@@IDENTITYAS [PID]
END

and the code in VB that I use to call the sp:

Dim cnn As New ADODB.Connection
cnn.Open Text2 'Driver={SQL Server}; Server=FORNAX\SQLEXPRESS; Database=TrapDB; Trusted_Connection=yes

Set WshNetwork = CreateObject("WScript.Network")
ComputerName = WshNetwork.ComputerName
UserName = WshNetwork.UserName

query = "USE [TrapDB] " + vbCrLf
query = query + "GO " + vbCrLf + vbCrLf
query = query + "DECLARE @return_value int " + vbCrLf
query = query + "DECLARE @Data Binary(4096) " + vbCrLf + vbCrLf
query = query + "SET @Data =CONVERT(binary(4096)," + Validate("ahoj") + ") " + vbCrLf + vbCrLf
query = query + "EXEC @return_value = [dbo].[LBLPRT_AddPrintJob] " + vbCrLf
query = query + " @PrintServer = N" + Validate(Text3) + "," + vbCrLf
query = query + " @PrinterName = N" + Validate(Text4) + ", " + vbCrLf
query = query + " @PrintedBy = N" + Validate(UserName) + ", " + vbCrLf
query = query + " @PrintedFrom = N" + Validate(ComputerName) + ", " + vbCrLf
query = query + " @PrintIssued = N" + Validate(Format(Now, "YYYY-MM-DD hh:mm:ss")) + ", " + vbCrLf
query = query + " @PrintData = @Data " + vbCrLf
query = query + "GO " + vbCrLf

Set rstemp = cnn.Execute(query)
Debug.Print rstemp(0)

__________________
JVV

Why don't you call directly the SP is beyond my understand, but try with vbnewline instead of vbcrlf...

__________________
HTH

Gonzalo

================================================== =========
Read this if you want to know how to get a correct reply for your question.
(Took that from Doug signature and he Took that from Peter profile)
================================================== =========
My programs achieved a new certification :
WORKS ON MY MACHINE
================================================== =========
I know that CVS was evil, and now i got the proof.
================================================== =========

how can I call it directly? vbnewline has the same effect as vbcrlf...

__________________
JVV

use a replacement instead of a new line?

Use chr(13) & chr(10) then if you need to read it later just Replace chr(13) & chr(10) with "<br />" or vbNewLine or vbCrLf

that way you don't need to worry what the vb code will affect

__________________
Jason Hall

Follow me on Twitter @jhall2013

Im not sure about that I understand you... I know that I not need to have all the breaks that I have, but Im mad from this problem and I want to have the same code that works in management studio...

__________________
JVV

Sorry for dumb question about direct call of SP, I was get it later :-) Thanks, I will try it...

__________________
JVV

I should have read your code better... why are you even using a line break you don't need one?

query = "USE [TrapDB] "
query = query + "GO "
query = query + "DECLARE @return_value int "
query = query + "DECLARE @Data Binary(4096) "
query = query + "SET @Data =CONVERT(binary(4096)," + Validate("ahoj") + ") "
query = query + "EXEC @return_value = [dbo].[LBLPRT_AddPrintJob] "
query = query + " @PrintServer = N" + Validate(Text3) + ","
query = query + " @PrinterName = N" + Validate(Text4) + ", "
query = query + " @PrintedBy = N" + Validate(UserName) + ", "
query = query + " @PrintedFrom = N" + Validate(ComputerName) + ", "
query = query + " @PrintIssued = N" + Validate(Format(Now, "YYYY-MM-DD hh:mm:ss")) + ", "
query = query + " @PrintData = @Data "
query = query + "GO "


This looks like C# so i'm not sure if you need ; after each line since I use vb.net.

__________________
Jason Hall

Follow me on Twitter @jhall2013