It can rather hard to peek inside a stored proc...

...especially one that is called from the job task scheduler. Best I can recommend is to emulate a print statement type of thing - but since PRINT isn't output you have to mimic it though tables. Specifically, create a dummy table that has a text column. Then do an insert from that table within the stored proc - inserting text that has some meaning as to what the text processing is actually doing.

In this case, I'd insert the names of the tables that match the criteria, since my first suspicion would be that the query on the systables is not returning any rows (thus shortcircuiting the rest of the logic). If the tables appear to be selected properly, then output the text that is used in the execute statement to see if it is properly formed sql (since that'd be my second suspicion).

Though this form of debugging may seem crude, it's miles above some of the debugging in real-time apps, where you're only form of getting some feedback may be a single LED that you have to make give you some information about what's going on inside.

[Edit Note: Of the top of my head, something along the lines of]

\n-- create a table to get some feedback\nCREATE TABLE PrimitiveDebugger(PrintTime DATETIME, PrintText TEXT)\nGO\n\n-- then, as before in the stored proc, get a pulse from within\n\n-- find if the select population is correct\nINSERT INTO PrimitiveDebugger\nSELECT GETDATE(), Name\nFROM sysobjects tab\nWHERE (tab.type = 'U')\n  AND (DATEDIFF(hh, tab.crdate, GETDATE()) > 6)\n  AND (tab.name LIKE('Temp%'))\n\nDECLARE @TableName VARCHAR(255)\nDECLARE #csrTable CURSOR FAST_FORWARD READ_ONLY FOR\n   SELECT Name\n   FROM sysobjects tab\n   WHERE (tab.type = 'U')\n     AND (DATEDIFF(hh, tab.crdate, GETDATE()) > 6)\n     AND (tab.name LIKE('Temp%'))\nOPEN #csrTable\nFETCH NEXT FROM #csrTable INTO @TableName\nWHILE (@@FETCH_STATUS != -1)\n   BEGIN\n      INSERT INTO PrimitiveDebugger VALUES(GETDATE(), 'DROP TABLE ' + @TableName)\n      --EXECUTE('DROP TABLE ' + @TableName)\n      FETCH NEXT FROM #csrTable INTO @TableName\n   END\nCLOSE #csrTable\nDEALLOCATE #csrTable\n\nGO\n\n-- from query analyzer after the task runs\nSELECT * FROM PrimitiveDebugger ORDER BY PrintTime\nGO\n

Post #257,563 by ChrisR 6/2/06 11:59:40 AM 6/2/06 12:11:51 PM Reply	It can rather hard to peek inside a stored proc... ...especially one that is called from the job task scheduler. Best I can recommend is to emulate a print statement type of thing - but since PRINT isn't output you have to mimic it though tables. Specifically, create a dummy table that has a text column. Then do an insert from that table within the stored proc - inserting text that has some meaning as to what the text processing is actually doing. In this case, I'd insert the names of the tables that match the criteria, since my first suspicion would be that the query on the systables is not returning any rows (thus shortcircuiting the rest of the logic). If the tables appear to be selected properly, then output the text that is used in the execute statement to see if it is properly formed sql (since that'd be my second suspicion). Though this form of debugging may seem crude, it's miles above some of the debugging in real-time apps, where you're only form of getting some feedback may be a single LED that you have to make give you some information about what's going on inside. [Edit Note: Of the top of my head, something along the lines of] \n-- create a table to get some feedback\nCREATE TABLE PrimitiveDebugger(PrintTime DATETIME, PrintText TEXT)\nGO\n\n-- then, as before in the stored proc, get a pulse from within\n\n-- find if the select population is correct\nINSERT INTO PrimitiveDebugger\nSELECT GETDATE(), Name\nFROM sysobjects tab\nWHERE (tab.type = 'U')\n AND (DATEDIFF(hh, tab.crdate, GETDATE()) > 6)\n AND (tab.name LIKE('Temp%'))\n\nDECLARE @TableName VARCHAR(255)\nDECLARE #csrTable CURSOR FAST_FORWARD READ_ONLY FOR\n SELECT Name\n FROM sysobjects tab\n WHERE (tab.type = 'U')\n AND (DATEDIFF(hh, tab.crdate, GETDATE()) > 6)\n AND (tab.name LIKE('Temp%'))\nOPEN #csrTable\nFETCH NEXT FROM #csrTable INTO @TableName\nWHILE (@@FETCH_STATUS != -1)\n BEGIN\n INSERT INTO PrimitiveDebugger VALUES(GETDATE(), 'DROP TABLE ' + @TableName)\n --EXECUTE('DROP TABLE ' + @TableName)\n FETCH NEXT FROM #csrTable INTO @TableName\n END\nCLOSE #csrTable\nDEALLOCATE #csrTable\n\nGO\n\n-- from query analyzer after the task runs\nSELECT * FROM PrimitiveDebugger ORDER BY PrintTime\nGO\n Edited by ChrisR June 2, 2006, 12:11:51 PM EDT Expand All History
Post #258,115 by lincoln 6/8/06 9:30:31 AM Reply	End result Went to the server room, deleted the job, then recreated it. Set the job owner as "sa", set the Run As id to be "dbo", set up all of the parameters, and now it works. Even though I had done all that before after having it not work after being created on my desktop in Enterprise Manager. And I'm the database owner. Just another headache caused by SQL Server 2000. Unnecessarily. lincoln "Chicago to my mind was the only place to be. ... I above all liked the city because it was filled with people all a-bustle, and the clatter of hooves and carriages, and with delivery wagons and drays and peddlers and the boom and clank of freight trains. And when those black clouds came sailing in from the west, pouring thunderstorms upon us so that you couldn't hear the cries or curses of humankind, I liked that best of all. Chicago could stand up to the worst God had to offer. I understood why it was built--a place for trade, of course, with railroads and ships and so on, but mostly to give all of us a magnitude of defiance that is not provided by one house on the plains. And the plains is where those storms come from." -- E.L. Doctorow Never apply a Star Trek solution to a Babylon 5 problem. I am not merely a "consumer" or a "taxpayer". I am a *Citizen* of the United States. [link\|mailto:bconnors@ev1.net\|contact me]
Post #258,127 by broomberg 6/8/06 10:15:41 AM Reply	Rings a bell Remote job settup would fail due to the context being in at the desktop. Wen't though a lot of that crap setting up SQL/Server jobs.

Welcome to IWETHEY!