Drop Table Gets skipped in a query batch

I have a situation in SQL Svr 2k that I can't explain. I have a query batch that runs in a SQL step in a DTS package. All steps execute except the Drop Table statements. Here is a section of the code;

************************************************** ****
INSERT INTO Oral_VitDhistory ( MeNumber, DrugType, Territory, Quantity, SalesAmt, DataType, RXDate )
SELECT tbl_Oral_VitDHistory.MENumber, tbl_Oral_VitDHistory.DrugType, tbl_Oral_VitDHistory.Territory, tbl_Oral_VitDHistory.Quantity, tbl_Oral_VitDHistory.SalesAmt, tbl_Oral_VitDHistory.DataType, tbl_Oral_VitDHistory.RXDate
FROM Data_Warehouse..tbl_Oral_VitDHistory WHERE tbl_Oral_VitDHistory.Territory Is Not Null

drop table Data_Warehouse..NDC24month_Cost
drop table Data_Warehouse..NDC24month_Count

/* Process New Rx */

select MeNumber, zip, RXTypecode, DrugTYpe, NRX1 into NDC24month_Cost from Data_Warehouse..NDC24month where RxTypecode = '$ B'

Insert into tbl_Oral_VitDHistory(MeNumber, DrugType, Quantity, SalesAmt) select a.MeNumber,
a.DrugType, a.NRX1 as Quantity, b.NRX1 as SalesAmt from Data_Warehouse..NDC24month_Count as a, NDC24month_Cost
as b where a.Menumber = b.MeNumber and a.DrugType = b.DrugType
************************************************** *******

The drop table statements appear to get skipped and the code fails because the column names should have been changed as the dropped tables should have been recreated with a different column name.

This used to work fine. Then suddenly it stopped working. No changes to the SQL installation from when it worked to when it stopped. The batch also fails in the QA if cut and pasted and ran from there.

I ran accross this once before in a Stored Proc with the Drop table statement and had to put the statement in a different procedure.

Anybody ever run accross this? I have searched the MS KB to no avail.

Thanks in advance to the genius who can help a guy out!Howdy,

I have come across this before - usually the best way to get around it is to break the code into separate sprocs & then call each in turn from a main sproc. Even using BEGIN TRAN...COMMIT TRAN wont help....it seems to get a bee in its bonnet and thats it......

Also, if its in DTS package etc , break up the code into logical chunks with end of batch "GO" commands. This will mean separate sprocs if you run it in one sproc currently.

Cheers,

SG.|||Thanks for your help!

Thats about where I thought I would have to go with it.
You confirmed my suspicions!

Have a great one!

Originally posted by sqlguy7777
Howdy,

I have come across this before - usually the best way to get around it is to break the code into separate sprocs & then call each in turn from a main sproc. Even using BEGIN TRAN...COMMIT TRAN wont help....it seems to get a bee in its bonnet and thats it......

Also, if its in DTS package etc , break up the code into logical chunks with end of batch "GO" commands. This will mean separate sprocs if you run it in one sproc currently.

Cheers,

SG.

DROP DATABASE problem

I am facing a trouble with DROP DATABASE command. Let me explain it this way:
Actually I am building an installation package which executes a series of SQL scripts to do the database changes on the target machine. Steps are briefly listed below:
1 - Create a database with simple CREATE DATABASE command
2 - Restore the database created above with RESTORE DATABASE command (with replace option)
3 - Configure other objects such as logins etc etc.
Now, everything works fine as long as all the scripts execute correctly.

However, I have seen instances when RESTORE DATABASE fails with "timeout expired" error. Well, I overcame this problem by simply increasing the timeout limit. But in that course what I observed is that when a restore database command "fails", then deleting the database immediately after that (as a part of rollback process) does not delete the database files (i.e., I can't see them thru management studio but..... physical *.mdf and *.ldf files still remain) Normally, when I use DROP DATABASE command with a database in "ONLINE" state these files are perfectly deleted, but when the database is in a "RESTORING" state, the physical files aren't getting deleted...
I don't know what's going wrong. Any ideas?
Any help will be greatly appreciated

Can't you just add a check for the files in your setup package, and if it's in a rollback state, just delete those files if found..?

/Kenneth

|||I can certainly do that. But I am keeping that as the last resort. Mainly because, that will involve querying the registry to get the instance names, then the data folders for those instances and then going to that location to check for the existence of mdf files. This would do my job, but then I beleive this is not the standard way to do that. Such processes may go out-of-sync very easily.

I was just trying to figure out why these files aren't deleted when database is in a restoring state. Or is it possible to correct this in any way?|||

I'm having a bit of trouble reproducing this...

Assuming SQL Server 2k 8.00.760 Dev Ed, and assuming 'Loading' is the same as your 'Restoring' state...

If I restore a db with norecovery option, it's in Loading state.
When I do a DROP DATABASE on that db, the files are deleted also.... as they should

It seems to be working ok from this end...

/Kenneth

|||Kenneth, I am not sure about "Loading" state but I was able to reproduce this error on two machines running instances of SQL Server 2005.
Method I used to reproduce the "restoring" state was writing a program (in my case C#) which connects to the DB and executes the "restore database" command with extremely low command timeout (i tried with 1 and 3 seconds, since database backup file's sixe is around 300 MB so it certainly takes more than that and a timeout occurs). So, when timeout occurs, the database is left in "restoring" state.... then I use the "drop database" command.. database entries are deleted but files remain. Probably, with "Loading" state this problem is not occurring. I am still not able to figure out what's wrong. Could you try reproducing that with database in "restoring" state?|||

I haven't had the time to do any actual testing, but if I was to speculate a bit, I believe that this is the intended behaviour..

If I understand it correctly, the 'problem' is after a 'failed' restore - that is, the restore process has been abnormally terminated for some reason..?
I would then assume that the state of the db would then be considered as 'offline'.

If that is indeed the case, then this quote from BOL:

Dropping a database deletes the database from an instance of SQL Server and deletes the physical disk files used by the database. If the database or any one of its files are offline when it is dropped, the disk files are not deleted. These files can be deleted manually by using Windows Explorer.

...my guess is that the above is what you're experiencing.
It does 'feel' reasonable, I think.

/Kenneth

|||

I think that the process you outlined for locating the database files (querying the registry etc...) may be a little over-complex.

As long as the database is attached to the instance then you can query the master.sys.master_files Catalog View (or master.dbo.sysaltfiles in SQL Server 2000) to determine the locations of the database's files prior to dropping the database and deleting the files (via xp_cmdshell).

Chris

|||Kenneth, In that case the only option I have is to ensure myself that the database files are deleted after every failed restore.
Chris, I just found out the method you described to find out the db filename. In fact, am new to SQL Server and so less aware of admin things.
Anyway, Thanks to both of you for all the time and help extended to me.

DROP DATABASE problem

I am facing a trouble with DROP DATABASE command. Let me explain it this way:
Actually I am building an installation package which executes a series of SQL scripts to do the database changes on the target machine. Steps are briefly listed below:
1 - Create a database with simple CREATE DATABASE command
2 - Restore the database created above with RESTORE DATABASE command (with replace option)
3 - Configure other objects such as logins etc etc.
Now, everything works fine as long as all the scripts execute correctly.

However, I have seen instances when RESTORE DATABASE fails with "timeout expired" error. Well, I overcame this problem by simply increasing the timeout limit. But in that course what I observed is that when a restore database command "fails", then deleting the database immediately after that (as a part of rollback process) does not delete the database files (i.e., I can't see them thru management studio but..... physical *.mdf and *.ldf files still remain) Normally, when I use DROP DATABASE command with a database in "ONLINE" state these files are perfectly deleted, but when the database is in a "RESTORING" state, the physical files aren't getting deleted...
I don't know what's going wrong. Any ideas?
Any help will be greatly appreciated

Can't you just add a check for the files in your setup package, and if it's in a rollback state, just delete those files if found..?

/Kenneth

|||I can certainly do that. But I am keeping that as the last resort. Mainly because, that will involve querying the registry to get the instance names, then the data folders for those instances and then going to that location to check for the existence of mdf files. This would do my job, but then I beleive this is not the standard way to do that. Such processes may go out-of-sync very easily.

I was just trying to figure out why these files aren't deleted when database is in a restoring state. Or is it possible to correct this in any way?|||

I'm having a bit of trouble reproducing this...

Assuming SQL Server 2k 8.00.760 Dev Ed, and assuming 'Loading' is the same as your 'Restoring' state...

If I restore a db with norecovery option, it's in Loading state.
When I do a DROP DATABASE on that db, the files are deleted also.... as they should

It seems to be working ok from this end...

/Kenneth

|||Kenneth, I am not sure about "Loading" state but I was able to reproduce this error on two machines running instances of SQL Server 2005.
Method I used to reproduce the "restoring" state was writing a program (in my case C#) which connects to the DB and executes the "restore database" command with extremely low command timeout (i tried with 1 and 3 seconds, since database backup file's sixe is around 300 MB so it certainly takes more than that and a timeout occurs). So, when timeout occurs, the database is left in "restoring" state.... then I use the "drop database" command.. database entries are deleted but files remain. Probably, with "Loading" state this problem is not occurring. I am still not able to figure out what's wrong. Could you try reproducing that with database in "restoring" state?|||

I haven't had the time to do any actual testing, but if I was to speculate a bit, I believe that this is the intended behaviour..

If I understand it correctly, the 'problem' is after a 'failed' restore - that is, the restore process has been abnormally terminated for some reason..?
I would then assume that the state of the db would then be considered as 'offline'.

If that is indeed the case, then this quote from BOL:

Dropping a database deletes the database from an instance of SQL Server and deletes the physical disk files used by the database. If the database or any one of its files are offline when it is dropped, the disk files are not deleted. These files can be deleted manually by using Windows Explorer.

...my guess is that the above is what you're experiencing.
It does 'feel' reasonable, I think.

/Kenneth

|||

I think that the process you outlined for locating the database files (querying the registry etc...) may be a little over-complex.

As long as the database is attached to the instance then you can query the master.sys.master_files Catalog View (or master.dbo.sysaltfiles in SQL Server 2000) to determine the locations of the database's files prior to dropping the database and deleting the files (via xp_cmdshell).

Chris

|||Kenneth, In that case the only option I have is to ensure myself that the database files are deleted after every failed restore.
Chris, I just found out the method you described to find out the db filename. In fact, am new to SQL Server and so less aware of admin things.
Anyway, Thanks to both of you for all the time and help extended to me.

Drillthrough Filters

Hi,
Can anyone explain me how to work with the filter tab on Cube drill through
options?
I added an extra table and specified the joining clause in this tab. It
works fine for the first time; but after saving the cube it gives invalid
alias or alias cannot be located. I tried putting the same alias name for the
table but it doesn’t work.
Thanks in advance.
Regards,
Yash
Run SQL Profiler to see what the exact SQL statement being generated looks
like.
My guess is that you have an edit issue in your filter statement.
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
This posting is provided "AS IS" with no warranties, and confers no rights.
"Yash" <Yash@.discussions.microsoft.com> wrote in message
news:F34694EB-D644-4671-AF0D-2C1896CDDDEE@.microsoft.com...
> Hi,
> Can anyone explain me how to work with the filter tab on Cube drill
through
> options?
> I added an extra table and specified the joining clause in this tab. It
> works fine for the first time; but after saving the cube it gives invalid
> alias or alias cannot be located. I tried putting the same alias name for
the
> table but it doesn't work.
> Thanks in advance.
> --
> Regards,
> Yash
>

Drillthrough Filters

Hi,
Can anyone explain me how to work with the filter tab on Cube drill through
options?
I added an extra table and specified the joining clause in this tab. It
works fine for the first time; but after saving the cube it gives invalid
alias or alias cannot be located. I tried putting the same alias name for th
e
table but it doesn’t work.
Thanks in advance.
--
Regards,
YashRun SQL Profiler to see what the exact SQL statement being generated looks
like.
My guess is that you have an edit issue in your filter statement.
--
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Yash" <Yash@.discussions.microsoft.com> wrote in message
news:F34694EB-D644-4671-AF0D-2C1896CDDDEE@.microsoft.com...
> Hi,
> Can anyone explain me how to work with the filter tab on Cube drill
through
> options?
> I added an extra table and specified the joining clause in this tab. It
> works fine for the first time; but after saving the cube it gives invalid
> alias or alias cannot be located. I tried putting the same alias name for
the
> table but it doesn't work.
> Thanks in advance.
> --
> Regards,
> Yash
>