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 |
|
Jay99
468 Posts |
 Posted - 2002-04-01 : 16:03:44
|
I am running...exec @HRESULT = master..sp_OACreate 'Scripting.FileSystemObject', @objFSO OUT ...to instantiate a FileSystemObject so that I can expose the FileExists method.Later on down the execution path, I am running....exec master..sp_executesql @sql ... to dynamically bulk insert from the file (the one I was just checking for with the fso) into a table.These two kids don't seem to be playing nice with each other, and I don't have a firm grasp on why. Run together in the same batch I get...quote:
Server: Msg 7302, Level 16, State 1, Line 1Could not create an instance of OLE DB provider 'STREAM'.
...from the sp_executesql line. If I comment out the object creation, the bulk insert happens just fine. I am destroying the object after I have checked for the file. I am wondering if I need to set the context parm on sp_AOCreate, but I don't fully understand the implications as described in BOL.Jay<O> |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-04-01 : 16:09:18
|
| This doesn't exactly address the problem (I don't know what's wrong anyway), but if you just need to test for a file's existance, use xp_fileexist. |
 |
|
|
Jay99
468 Posts |
Posted - 2002-04-01 : 16:23:14
|
quote:
This doesn't exactly address the problem (I don't know what's wrong anyway), but if you just need to test for a file's existance, use xp_fileexist.
yeah, I have a couple good reasons why I am not using xp_fileexist. It is undocumented and therefore the client feels that it not safe. That alone wouldn't really stop me. Second, even if I compile my proc with 'set nocount on' the xp_fileexist call prints a '(1 row(s) affected)'. Which is no big deal, except I am running the proc from an Execute SQL Task in a SQL Server 7(sp3) DTS Package and it will only fail the task on an error if the error message is the first thing returned. Believe it or not, the '(1 row(s) affected)' caused the task to report success even on failure . . . Jay<O> |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-04-01 : 16:58:33
|
| That's weird, I just tried the SET NOCOUNT ON and it didn't include the "1 row(s) affected" message. The nice thing about this is that you will ALWAYS get a row using xp_fileexist. If you use INSERT...EXEC xp_fileexist you can put the results into a table and check later. Then you can try something like this:SET NOCOUNT ONCREATE TABLE FilesExist ([File Exists] int NOT Null, [File is a Directory] int NOT NULL, [Parent Directory Exists] int NOT NULL)--only need to run this onceDELETE FROM FilesExistINSERT INTO FilesExist EXEC xp_fileexists 'filename.ext'IF NOT EXISTS (SELECT * FROM FilesExist WHERE [File Exists]=1)BEGINRAISERROR 'File does not exist', 16, 1ENDIt may not work in a DTS job, but you never know. Just in case the sp_OA thing doesn't work out. |
|
|
|
Jay99
468 Posts |
Posted - 2002-04-02 : 08:58:15
|
quote:
That's weird, I just tried the SET NOCOUNT ON and it didn't include the "1 row(s) affected" message.
Odd...on my installation, running xp_fileexist spits out the row count regardless of 'nocount'. The INSERT...EXEC supresses the rowcount...Thanks, I think that will work for me.Jay<O> |
|
|
|
|
|
|
|
|
sp_OACreate