Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
seevo
Starting Member
3 Posts |
 Posted - 2002-04-30 : 16:14:39
|
| I am using a stored procedure that uses sp_oaCreate to fire a method within an activeX exe that selects records from the local sql server and dumps them to text files, zips them, and returns an ftp url to the zipped file to the caller.It appears that when multiple clients call this sp concurrently, the stored proc acutally executes the requests sequentially - i.e. the first call through is the first call processed.Can sp_oaCreate execute concurrent requests non-sequentially, or is this a limitation of sql server?There are several business rules that need to be applied to said records, so a simple zip component does not do the trick. The issue is not creating the zip files, rather creating multiple instances of the same activeX object through sp_oaCreate. We have not ruled out DTS.Edited by - seevo on 04/30/2002 16:49:33 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-04-30 : 16:43:49
|
| I'm pretty sure you can write this in a way that does not require ActiveX methods at all. The PKZIP utility runs as a command-line executable, and you can use bcp to output the files from the command line, and these can be called using xp_cmdshell, something like this:master..xp_cmdshell 'bcp pubs..authors out c:\myfile.txt -Sserver -Uusername -Ppassword'master..xp_cmdshell 'pkzip c:\thisdata.zip c:\myfile.txt'If the filename(s) are dynamic, you can have the stored procedure generate them dynamically, and use dynamic SQL to execute them:CREATE PROCEDURE ZipFiles ASDECLARE @textfile varchar(128), @zipfile varchar(128)SELECT @textfile='C:\myfile.txt', @zipfile='zipfile.zip'EXEC master..xp_cmdshell 'bcp pubs..authors out ' + @textfile + ' -Sserver -Uusername -Ppassword'EXEC master..xp_cmdshell 'pkzip C:\inetpub\ftproot\datafiles\' + @zipfile + ' ' + @textfileSELECT 'ftp://ftp.mysite.com/datafiles/' + @zipfileI imagine that you have a file name generator of somekind to create the filenames, or that you pass them to the procedure. As long as the text file and zip file do not have the same names, this can run concurrently with as many users as the system can handle. |
 |
|
|
seevo
Starting Member
3 Posts |
Posted - 2002-04-30 : 16:53:00
|
quote:
I'm pretty sure you can write this in a way that does not require ActiveX methods at all. The PKZIP utility runs as a command-line executable, and you can use bcp to output the files from the command line, and these can be called using xp_cmdshell, something like this:master..xp_cmdshell 'bcp pubs..authors out c:\myfile.txt -Sserver -Uusername -Ppassword'master..xp_cmdshell 'pkzip c:\thisdata.zip c:\myfile.txt'If the filename(s) are dynamic, you can have the stored procedure generate them dynamically, and use dynamic SQL to execute them:CREATE PROCEDURE ZipFiles ASDECLARE @textfile varchar(128), @zipfile varchar(128)SELECT @textfile='C:\myfile.txt', @zipfile='zipfile.zip'EXEC master..xp_cmdshell 'bcp pubs..authors out ' + @textfile + ' -Sserver -Uusername -Ppassword'EXEC master..xp_cmdshell 'pkzip C:\inetpub\ftproot\datafiles\' + @zipfile + ' ' + @textfileSELECT 'ftp://ftp.mysite.com/datafiles/' + @zipfileI imagine that you have a file name generator of somekind to create the filenames, or that you pass them to the procedure. As long as the text file and zip file do not have the same names, this can run concurrently with as many users as the system can handle.
Thanks for the input, your example would produce the zip files correctly. However, the data extracted from the database is run through several business rules before exporting - thus the existence of the activeX control. We have since found info leading us to believe that objects created through sp_oaCreate are single threaded!Any other input would be greatly appreciated. |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2002-04-30 : 22:12:48
|
| I wouldn't use sp_oacreate like this. It isn't really meant to stand up to lots of users hitting it.What I would suggest is setting up a table where you specify the parameters for the job. Then run, as a seperate process, a job that polls that table every minute or so for new instructions, then if it finds them, run your EXE and let it do it's work.Damian |
|
|
|
|
|
|
|
|
sp_oaCreate and concurrent execution