How to Execute Batch using ADO

[Narinder Makin]

I want to execute batch of follwoing statemenst on SQL server using ADO. Can I store it in a file and execute it using the command object's execute method or something else? I tried the execute method with a buffer and it issues an exception. It dosent's recognize the GO statement and issues an error on CREATE PROCEDURE statement. I seperated each of the statement with a ";" , even then it issues the error. Any suggestions?????????

I am using the follwoing code:
TESTHR(hr = pCmd.CreateInstance(__uuidof(Command)));
pCmd->ActiveConnection = pCnn;
pCmd->CommandTimeout = 15;
pCmd->CommandText = buffertext;
pCmd->Execute(NULL,NULL,adCmdText|adExecuteNoRecords );
Where "buffertext" contains the following:

DECLARE @PARAM integer
DECLARE @MYNAME varchar(255)
DECLARE @SQLSTMT nvarchar(255)
DECLARE @TOTALCOUNT integer
SELECT @TOTALCOUNT = COUNT(*) FROM SYSOBJECTS WHERE name LIKE 'CUSTDETAIL_Q_%' AND type = 'P'
SET @PARAM = 0
SET ROWCOUNT 1
WHILE (@PARAM < @TOTALCOUNT)
BEGIN
SELECT @PARAM = @PARAM + 1
SELECT @MYNAME = name FROM SYSOBJECTS WHERE name LIKE 'CUSTDETAIL_Q_%' AND type = 'P'
SET @SQLSTMT = "DROP PROCEDURE " + @MYNAME
EXEC sp_executesql @SQLSTMT
END
SET ROWCOUNT 0
GO



CREATE PROCEDURE CUSTDETAIL_Q_LOAD_0
@SELECTION varchar(255)
AS
DECLARE @MY_SELECTION varchar(255)
DECLARE @SQL_STMT nvarchar(500)
DECLARE @SELECT_CLAUSE varchar(255)
DECLARE @MY_WHERECLAUSE varchar(400)
SET @MY_SELECTION = @SELECTION
SET @SELECT_CLAUSE = 'SELECT customer_ID,FirstName,email,MiddleInit,FullName FROM customer'
SET @MY_WHERECLAUSE = ""
IF @MY_SELECTION != ""
BEGIN
IF @MY_WHERECLAUSE != ""
SELECT @MY_WHERECLAUSE = @MY_WHERECLAUSE +" AND " + @MY_SELECTION
ELSE
SELECT @MY_WHERECLAUSE = " WHERE " + @MY_SELECTION
END
SELECT @SQL_STMT = @SELECT_CLAUSE + @MY_WHERECLAUSE
EXEC sp_executesql @SQL_STMT
RETURN(@@ERROR)

GO



CREATE PROCEDURE CUSTDETAIL_Q_LOAD_1
@MAXROWS integer,
@PAGENUM integer,
@TOTALROWS integer OUTPUT
AS
DECLARE @SQL_STMT nvarchar(500)
DECLARE @MY_WHERECLAUSE varchar(400)
DECLARE @FIRST integer, @LAST integer

SET NOCOUNT ON

SET @MY_WHERECLAUSE = ""
IF @MAXROWS != -1
BEGIN
SELECT @FIRST = (@PAGENUM - 1)*@MAXROWS + 1
SELECT @LAST = @PAGENUM*@MAXROWS

CREATE TABLE #RANKORDER
(
RANK INT IDENTITY(1,1) NOT NULL PRIMARY KEY
,manufacturer_ID integer,name varchar(50)
)
SELECT @SQL_STMT = 'INSERT #RANKORDER SELECT manufacturer_ID,name FROM manufacturer' + @MY_WHERECLAUSE + " ORDER BY " + 'manufacturer_ID'
EXEC sp_executesql @SQL_STMT
IF ( @@ERROR <> 0)
RETURN(@@ERROR)
SELECT @TOTALROWS = count(*) from #RANKORDER
SELECT * FROM #RANKORDER WHERE RANK >= @FIRST AND RANK <= @LAST
DROP TABLE #RANKORDER
END

ELSE
BEGIN
SELECT @SQL_STMT = 'SELECT manufacturer_ID,name FROM manufacturer' + @MY_WHERECLAUSE
EXEC sp_executesql @SQL_STMT
IF ( @@ERROR <> 0)
RETURN(@@ERROR)
END
SET NOCOUNT OFF
RETURN(0)

GO


thanks,
Narinder Makin.