June 11, 2008 at 12:17 pm
Michael (6/11/2008)
exec sp_MSforeachtable@command1 = 'DELETE FROM ? ',
@whereand = '(TSTAMP < ''2007-01-01 00:00:00'')'
Results the same:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'TSTAMP'.
That's probably because there is no TSTAMP column in the SYSOBJECTS table. The @WHEREAND argument is used to select the Tables affected by sp_MSForeachtable, not for which rows in those tables are affected.
Try this instead:
exec sp_MSforeachtable
@command1 = 'DELETE FROM ?
Where (TSTAMP < ''2007-01-01 00:00:00'')'
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 11, 2008 at 12:22 pm
Michael (6/11/2008)
exec sp_MSforeachtable@command1 = 'DELETE FROM ? ',
@whereand = '(TSTAMP < ''2007-01-01 00:00:00'')'
Results the same:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'TSTAMP'.
hmm - something funky about the @whereand parameter. It's been a while since I played with this undocumented stored proc, so I'm stuck.
You could simply combine it into Command1 and it should fire though.
as in:
exec sp_MSforeachtable
@command1 = 'DELETE FROM ? WHERE (TSTAMP < ''2007-01-01 00:00:00'')'
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 11, 2008 at 12:29 pm
Jinx.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 11, 2008 at 2:34 pm
How much of the total size of your DB do you expect to be removing? It is worth seconding Noel's post. You've stated that you have 2 GB of space left, right? Is your transaction log on the same disk? If so, you'll want to begin/commit transaction with each table delete so you'r t-log does not chew up your remaining 2GB. One large transaction deleting that much data can grow your log by the 2 GB you have left so be carefull.
Also, you may know this or not, but deleting the rows will not reduce the physical size of your MDF file. You'll have to shrink it if you plan on reclaiming the drive space.
June 11, 2008 at 2:46 pm
hehe...I see Barry has better recollection than I as to why the @whereand parameter doesn't work. And - he types faster too!:D
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 11, 2008 at 3:27 pm
Wow! That worked great. Thanks. I think I tried everyting except putting the Delete From & Where as Command1. I am going to run some tests on to see how the log file reacts. Thanks again.
June 11, 2008 at 4:53 pm
Glad we could (finally) help.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 11, 2008 at 4:54 pm
Matt Miller (6/11/2008)
hehe...I see Barry has better recollection than I as to why the @whereand parameter doesn't work. And - he types faster too!:D
I have to confess that I only suspected it at first, I googled the rest. (I love living in the future!)
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply