1

I have a config table with same fields "SMTP_Server". I want to update the fields with new value in each database on the server.

sp_foreachdb updates last table (in last database). Why?

please help

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
surenv2003
  • 119
  • 2
  • 4
  • 13
  • How are you specifying what table you want to update? I'm not sure how you can write an update statement that doesn't explicitly state the table you are updating. – JNK Jan 28 '11 at 13:33

1 Answers1

3

This sp doesn't seem to be "officially" supported and sometimes has problems...

See: Making a more reliable and flexible sp_msforeachdb at http://www.mssqltips.com/tip.asp?tip=2201&home

...I have discovered instances where, under heavy load and/or with a large number of databases, the procedure can actually skip multiple catalogs with no error or warning message. Since this situation is not easily reproducible, and since Microsoft typically has no interest in fixing unsupported objects, this may be happening in your environment right now. ...

John K.
  • 5,426
  • 1
  • 21
  • 20
  • It is undocumented but you can view the definition yourself. From a quick glance I can't see why it would do this `SELECT OBJECT_DEFINITION(OBJECT_ID('sys.sp_MSforeachdb')) as [processing-instruction(x)] FOR XML PATH` – Martin Smith Jan 28 '11 at 13:40
  • Martin...Are you looking at that line inside the sp? What I see in the sp is a "fairly" straightforward process of getting all the dbs and executing the sql against each. – John K. Jan 28 '11 at 13:49
  • Not sure what you mean exactly, what line inside the sp? – Martin Smith Jan 28 '11 at 14:03
  • Martin...Sorry, I had just scanned over your comment and didn't realize that was what you were using to view the Stored Procedure. You're right, though... I don't see why it would fail either... must have something to do with the internal processing with MS. – John K. Jan 28 '11 at 14:07