Right after DTS, query doesn't work anymore?!

rweide · January 20th, 2005, 02:29 PM

Hi, all,

Please confirm if this is true.

I clear the destination table before I run DTS package from an ASP page, then after I complete the DTS, I check the destination table's row count to see if the DTS has completed successfully.

However, this execution of the simple SQL query inside the ASP doesn't seem to be working anymore after the run of DTS?!

I use the same query before the table clearing and DTS execution, and it returns the correct row count.

Does this happen to you?

bmains · January 20th, 2005, 04:17 PM

No, are you uninitializing the package and closing all of the DTS objects after you are done with them? Are you sure the DTS package is done?

Brian

rweide · January 20th, 2005, 04:26 PM

This is the code I use:

Code:

    Set objDTSPackage = CreateObject("DTS.Package")
    objDTSPackage.LoadFromSQLServer "server", "", "", 256,,,,"PkgName" 
    objDTSPackage.FailOnError = true
    objDTSPackage.Execute
    objDTSPackage.UnInitialize 
    Set objDTSPackage = Nothing

I've tried to use server-side code to do this previously, but since it involved IIS permission issue that I can't touch at the moment, I switched to use client-side VBScript. And it does seem to complete the DTS package - at least when I use Enterprise Manager to check the row count, it seems to be correct.
However, if I execute the same SQL query or stored procedure to check row count in ASP page, it doesn't give me any error, but it just gives me 0 row count - even though there're records in the table.

bmains · January 21st, 2005, 09:11 AM

OK, with ASP are you returning the rowcount as an output parameter or as a select statement, in your procedure? I'm not quite sure why that may be.

Brian

rweide · January 21st, 2005, 09:27 AM

After the DTS execution, I use "SELECT COUNT(*) row FROM tbl_Destination" currently. But I also tried @@rowcount and a couple of different variations - thinking maybe I missed something.

Since I also do "Delete from tbl_Destination" BEFORE the DTS to clear the table, so I tried do the delete AFTER DTS to see if the same stored procedure works.

So basically this is what's happening at the moment - Delete and checking row counts on destination table all work fine before the DTS execution, but the same stored procedure or query don't give error and don't give results either after I run DTS.

It's like the destination table has not been released?!

bmains · January 21st, 2005, 03:23 PM

Does it actually complete loading the page, or does the page time out also? If it completes loading the page, the package isn't getting hung up. Weird.

Brian

rweide · January 21st, 2005, 04:11 PM

All the queries or stored procedure executed after DTS execution seem to run through without a problem (i.e. no error comes back), it just doesn't provide any result.

It's very weird, I guess this is either very hard to resolve or very easy - due to some stupid mistake on my part - I just hope it's the latter.