ISQL shell to insert stored procedure

[MDA]

Hi all,

I am trying to create a stored procedure during the install of a small VB app I have created. The Stored procedure uses T-SQL so I cannot create it through ADO, etc.

I was thinking that if I put the stored procedure in a TEXT file then execute by calling ISQL during the VB install. One hitch I know of is that the user will not be on the server, nor have Query analyzer on their machine.

Is this possible??? How does one typically create stored procedures like this. Note: the DB is already created, I just want to add a stored procedure.

Any help on how to call ISQL.exe and insert the contents of a text file (containing the SP) is greatly appreciated.

Thanks in advance for any help on this.

Mike

 

[cdukes]

There is no reason why you can't create the stored procedure using ADO. Use the Connection object connected to the database and execute the text of the stored procedure inclucing the CREATE PROCEDURE etc.

objConn.execue "Create Procedure XXX....."

The only thing you must not do is include the GO at the bottom of the stored procedure as ADO deos not like it. If you need to drop it first, you will need to do this in more than one step.

You can use the execute on the connection object to run almost any T-SQL including Creating databases etc.

Hope this helps,

Chris Dukes

 

[MDA]

Thanks, If you could elaborate a little more I can get this darn thing to work... I am getting errors that ADO does not understand the syntax.. this is my VB...
'************
Dim Cnxn As ADODB.Connection
Dim cmdChange As ADODB.Command
Dim strSQLChange As String
Dim strCnxn As String
Dim a, fs
' Define two SQL statement

'get file info
Dim fso As New FileSystemObject, fill As File, txtFile, ts As TextStream
Set txtFile = fspenTextFile(App.Path & "\" & TRANSFER, 1)
Set fill = fso.GetFile(App.Path & "\" & TRANSFER)
Set ts = fill.OpenAsTextStream(ForReading)

'Read script file into string
strSQLChange = ts.ReadAll

' Open connection
strCnxn = "DSN=" & frmODBCLogon.cboDSNList.Text & ";UID=" & frmODBCLogon.txtUID.Text & ";PWD=" & frmODBCLogon.txtPWD.Text & ";"

Set Cnxn = New ADODB.Connection
Cnxn.Open strCnxn

' Create command object
Set cmdChange = New ADODB.Command
Set cmdChange.ActiveConnection = Cnxn
cmdChange.CommandText = strSQLChange

'execute

cmdChange.Execute
'************
AND the SCRIPT (TEXT FILE)looks something like this:

'********
CREATE PROCEDURE sp_data_trans @newTT varchar(2), @oldTT varchar(2), @startPE varchar(4), @endPE varchar(4), @journum integer, @jourdesc varchar(255), @NewTC varchar(2)
AS DECLARE curr_budver_cursor SCROLL CURSOR
FOR SELECT sotrId,sotrStatus,jourId,trctId,trtpId,pdefId,curtId,accoId,unitId,unitIdCorr,unitIdRep,dim1Id,dim1IdCorr,dim2Id,dim3Id,dim4Id,curcId,curcId2,sotrAmount,sotrAmount2,sotrDCFlag,sotrRef,sotrDate,sotrSign,eusrId,regDate,sotrTxt,sotrTxt2,sotrTxt3,sotrTxt4,formId FROM sotr
WHERE trtpId=(select trtpId from trtp where trtpCode=@oldTT) AND
pdefid IN (select pdefid from pdef where (pdefPerno>=@startPE AND pdefPerno<=@endPE))
SET NOCOUNT ON
DECLARE
@sotrId integer,
@sotrStatus integer,
@jourId integer,
@trctId integer,
@trtpId integer,
@pdefId integer,
@curtId integer,
@acco............FETCH..INSERT INTO..bla bla You get the idea

*****************

I get stuck at the section referring to the cursor.. &quot;Incorrect syntax near curr_budver_cursor ......&quot;

It was my understanding that T-SQL is only understood using ISQL - (query Analyzer)??

Any additional help is greatly appreciated!!!!

Thanks for your time...
Best regards,
Mike

 

[MDA]

I got it... Disregard my last message.. thanks for the help...

In case some else out there is not sure how to do this... I did the following...
---------
Const TRANSFER = &quot;SCRIPT.TXT&quot;
Dim Cn As New ADODB.Connection
Dim Cmd As New ADODB.Command
Dim strConn As String
strConn = &quot;DSN=&quot; &amp; frmODBCLogon.cboDSNList.Text &amp; &quot;;UID=&quot; &amp; frmODBCLogon.txtUID.Text &amp; &quot;;PWD=&quot; &amp; frmODBCLogon.txtPWD.Text &amp; &quot;;&quot;

Cn.Open strConn


'get file info
Dim fso As New FileSystemObject, fill As File, txtFile, ts As TextStream
Set txtFile = fspenTextFile(App.Path &amp; &quot;\&quot; &amp; TRANSFER, 1)
Set fill = fso.GetFile(App.Path &amp; &quot;\&quot; &amp; TRANSFER)
Set ts = fill.OpenAsTextStream(ForReading)

'execute
Cn.Execute ts.ReadAll
-------------

Seems to work for me..

Thanks,

Mike