I tried this a little while ago

The scheduler had the job owner listed as "self", which didn't work, even though I'm the owner/admin of the database. So I went to the server, logged in as the server admin and set the job owner to the application's userid, which is a valid logon to the database that I created just to be used by the program. I also tried it in the form "networkname\\userid". Both were unsuccessful. Changed the owner to "sa" and gave it a shot. No luck. Set it as "BUILTIN\\Administrator" and tried again. Same result.

This is really pissing me off. It's really irritating knowing that the code works inside the stored procedure, and that I can right-click on the sp in Query Analyzer and run it without a hassle. It also sucks seeing the job history log saying that the job completed successfully but not telling me why the sp is either not being executed or not completing successfully.

I could try putting your code right into the job step and see what happens. Right now I've got nothing to lose.

...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,209 by lincoln 5/30/06 3:50:57 PM Reply	I tried this a little while ago select * from sysobjects where name like 'TEMP%' and (datediff(minute,crdate,getdate()) > 30) just to see if I would get back the name of the table I created earlier today. I did. I'll give your code a test tomorrow morning. Thanks! 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 #257,249 by ChrisR 5/30/06 11:59:10 PM Reply	BTW, it is a dangerous script... ...in that you can mass delete tables this way. (which is part of the reason I didn't feel like debugging it). So I'd definitely wrap it in a transaction the first few times you try it. :-) Also, I didn't specify the table owner within the select statement. That info is there in one of the system tables, though you may need to dig a bit. If the name pattern tells you everything you need to know, and you don't care about ownership, then the above should come pretty close to working.
Post #257,268 by lincoln 5/31/06 10:46:57 AM Reply	The test was successful I ran it as a stored procedure in my test database, and it worked fine. Then I copied the stored proc over to production and set it up as a scheduled job under SQL Server Agent. (I had also created some "TEMP" tables there yesterday.) Set it up as a once-a-day job running at 8 AM. Made sure that the SQLServerAgent service was running on the box, and success. Thanks big time. 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 #257,424 by lincoln 6/1/06 9:58:25 AM Reply	Boy did that ever prove me wrong Your code worked perfectly as a stored procedure. However, the job that I set up under SQLServerAgent ran but failed this morning. As expected, M$'s quality software wrote nothing to the SQL Server events logfile, so I don't know why the job failed. Would it have written a record to some type of Windows log on the server? 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 #257,430 by ChrisR 6/1/06 10:20:26 AM Reply	Should be in the Job Task history... ...accessible from Enterprise Manager (or Management Studio, or whatever they happen to be calling it this week). My suspicion would be that the job might have been set up for an account that doesn't have permission to drop tables.
Post #257,440 by lincoln 6/1/06 12:38:22 PM Reply	here's the latest update I've tweaked the job and now it runs successfully. However, it still doesn't drop the 'TEMP' tables. I've read the log, but all the error message says is that "The process could not be created for step 1 of job <32 char hex number> (reason: The system cannot find the file specified)" The only step in the job is: "EXEC <database>.<dbo>.<storedprocedurename>". From Query Analyzer, I can right click on the stored proc, and select "Script Object to New Window As -> Execute" and run it from the new window ... and it works. Could it be because SQLServerAgent is running under the system administrator on the server, and these tables are created programatically under a specific user's rights? I thought that the server's system administrator login is equivalent to the database owner's login, or at least to the 'SA' login. 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 #257,473 by ChrisR 6/1/06 2:59:00 PM Reply	Assuming you're on SS2k Fire up Enterprise Manager, drill down to Management/SQL Server Agent/Jobs. Right click on the Job in question. Select the "View Job History" and then check the Show step details. The see what account you're running under, double click the job name in question (as opposed to the right click above), and it should display who the owner of the job task is.
Post #257,481 by lincoln 6/1/06 4:49:34 PM Reply	We're running SQL Server 2000 I've tried the job several times this afternoon - each time SQLServerAgent says that the job succeeded, but the tables are still there. The job's Owner is the userid I created just for the application to use. The tables are created as <userid>.<TEMPname> instead of dbo.<TEMPname>, and in my stored procedure I have \nWHILE (@@FETCH_STATUS != -1)\n BEGIN\n EXECUTE('DROP TABLE <userid>.' + @TableName)\n FETCH NEXT FROM #csrTable INTO @TableName\n END\n Its Job Category is "Database Maintenance". Its Step Type is set as "Transact-SQL Script (TSQL)" - I originally tried it as "Operating System Command (CmdExec) which didn't work. 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 #257,485 by ChrisR 6/1/06 5:06:34 PM Reply	Sent an email. Still not clear on who the Owner of the Scheduled Task is - usually SQLServer is daft and assigns your identity as the person to run as, whereas most Scheduled Tasks really need to run under the System Administrator account. Also, I'm not clear on whether there is a dedicated user that is used for this temporary tables, or how you go about assigning it within '<userid=>.' since that's not really valid substitution in T-SQL (at least not that I'm aware of).
Post #257,505 by lincoln 6/1/06 9:59:10 PM Reply	got your email The scheduler had the job owner listed as "self", which didn't work, even though I'm the owner/admin of the database. So I went to the server, logged in as the server admin and set the job owner to the application's userid, which is a valid logon to the database that I created just to be used by the program. I also tried it in the form "networkname\\userid". Both were unsuccessful. Changed the owner to "sa" and gave it a shot. No luck. Set it as "BUILTIN\\Administrator" and tried again. Same result. This is really pissing me off. It's really irritating knowing that the code works inside the stored procedure, and that I can right-click on the sp in Query Analyzer and run it without a hassle. It also sucks seeing the job history log saying that the job completed successfully but not telling me why the sp is either not being executed or not completing successfully. I could try putting your code right into the job step and see what happens. Right now I've got nothing to lose. 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 #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!