running an .sql script

[BlackDice]

I have a vfp app with a SQL Server backend. When updating a client's database, there's a lot of stored procedures to be added to the database. Instead of going through and doing them by hand, I'd like to know if there's a way to do this from vfp9. This is what I've done so far:

Procedure Runscript
Lparameters tcScriptName

Local lcSQL,lnHandle,lnReturn

lcSQL = Filetostr(tcScriptName)
lnHandle = SQLConnect("myconnection")
If lnHandle > -1
	lnReturn = SQLExec(lnHandle,lcSQL,"tempcrsr",ainfo)
ENDIF

=SQLDISCONNECT(0)
Endproc

this works with any other normal sql statement. and the string is read in correctly. But it fails when I call sqlexec(). Does anybody know of a way to do this?

BlackDice

http://www.stillwaterexpress.com

 

[bborissov]

It must execute.
What the display memory shows?

Procedure Runscript
Lparameters tcScriptName

Local lcSQL,lnHandle,lnReturn

lcSQL = Filetostr(tcScriptName)
lnHandle = SQLConnect("myconnection")
If lnHandle > -1
   lnReturn = SQLExec(lnHandle,lcSQL,"tempcrsr",ainfo)
   IF lnReturn < 0
      AERROR(laError)
      DISPLAY MEMORY LIKE laError
   ENDIF
ENDIF

=SQLDISCONNECT(0)
Endproc

Borislav Borissov

 

[Mike Lewis]

Blackdice,

What do you mean when you say "it fails when I call sqlexec()"?

Do you get an error? Does SQLEXEC() returns a negative In what way does it fail?

The chances are that the problem is in the script. You could try opening the script in Query Analyzer and running it from there. See if you get the same error.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[BlackDice]

the problem comes through as "incorrect syntax near 'GO'". I open the file up in SQL Query Analyzer and it runs fine. This script actually creates a stored procedure on the server. I generated an sql script from the stored procedure so that it can be created on the server for my database whenever it's needed. but I get this error when I try to run it like this from FoxPro

BlackDice

http://www.stillwaterexpress.com

 

[bborissov]

Can you post sql code here?

Borislav Borissov

 

[Mike Lewis]

Blackdice,

Remove the word GO. It should not be present in a script that is send remotely.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[BlackDice]

MikeLewis, should ALL instances of the word 'GO' be removed? they're necessary in the script

BlackDice

http://www.stillwaterexpress.com

 

[BlackDice]

I tried removing 'GO' from everywhere in the script, now I get an error saying 'CREATE PROCEDURE must be the first statement in a query batch.' In the script, there's a 'GO' before the call to CREATE PROCEDURE, so it sees it as if it's the first statement in the batch, which is as I feared.

BlackDice

http://www.stillwaterexpress.com

 

[bborissov]

I suppose you have something like:

TEXT TO sqlScr NOSHOW
     IF EXISTS(....) -- Chek for if the SP exists
        BEGIN
          DROP SpName
          GO
        END
      CREATE --- SP creation
ENDTEXT

If this is so just split it to two different commands:

TEXT TO sqlScr NOSHOW
     IF EXISTS(....) -- Chek for if the SP exists
        DROP SpName
ENDTEXT
SQLEXEC(sqlH, sqlScr)
TEXT TO sqlScr NOSHOW
      CREATE --- SP creation
ENDTEXT
SQLEXEC(sqlH, sqlScr)

Borislav Borissov

 

[BlackDice]

Yeah, I thought about that. But I've found 2 ways that I think are better.

1. using the extended stored procedure 'xp_cmdshell' and using that like to shellexecute() to call the osql.exe utility and sending it the correct params. (using SQL server's query analyzer)

2.Calling shellexecute() from my program and having it execute the osql.exe utitility with the correct params

I've tried both of these and they work. But I think I'll go with number 2 because this way, I can select the directory full of .sql script files, and loop through them and execute them automatically.

BlackDice

http://www.stillwaterexpress.com

 

[Mike Lewis]

Blackdice,

I'm glad you've got it working, but for future reference, it looks like it was definitely the keyword GO that was causing the problem.

This is a common mistake. GO is not part of the T-SQL language. It is a directive, and is only applicable wihtin the Query Analyzer (and certain other tools such as OSQL). If you send it to the server in any other way, it will be regarded as a syntax error.

If you has removed all instances of GO, and made sure that your CREATE PROCEDURE command was the first command in the batch, the whole thing would have worked first time.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[agoeddeke]

Blackdice,

Write a little VFP routine that flips through your SQL script and parses out the script between the GO's. Then just send each snippet of script separately and in order using SQLEXECUTE.

Andy

 

[BlackDice]

thanks for all the advice guys, but I think I still like the shellexecute() method better. I've written a nice little .exe that takes care of it and it's all done in about 10 seconds (for 143 scripts, I think that's pretty good). I did think about parsing the 'GO' out of the scripts, but I just think that would've been sloppier.

BlackDice

http://www.stillwaterexpress.com

 

[agoeddeke]

The only drawback that I can see to running OSQL.exe is that you have to run it on the server. That's why I've always preferred parsing between the GO's... you can run it on any machine that can connect to SQL Server.

Andy

 

[BlackDice]

Yeah, I thought about that, but most of the time we're on the server anyway when we're updating a client's db. Plus, I could actually run it on my machine and connect to the remote server using an ip address instead of servername.

BlackDice

http://www.stillwaterexpress.com