IWETHEY v. 0.3.0 | TODO
1,095 registered users | 0 active users | 0 LpH | Statistics
Login | Create New User
IWETHEY Banner

Welcome to IWETHEY!

New 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]
New 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).
New 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]
New 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


Expand Edited by ChrisR June 2, 2006, 12:11:51 PM EDT
New 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]
New 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.
     deleting user-created tables via stored proc - (lincoln) - (14)
         Shouldn't be too hard - (ChrisR) - (13)
             I tried this a little while ago - (lincoln) - (12)
                 BTW, it is a dangerous script... - (ChrisR) - (11)
                     The test was successful - (lincoln) - (10)
                         Boy did that ever prove me wrong - (lincoln) - (9)
                             Should be in the Job Task history... - (ChrisR)
                             here's the latest update - (lincoln) - (7)
                                 Assuming you're on SS2k - (ChrisR) - (6)
                                     We're running SQL Server 2000 - (lincoln) - (5)
                                         Sent an email. - (ChrisR) - (4)
                                             got your email - (lincoln) - (3)
                                                 It can rather hard to peek inside a stored proc... - (ChrisR) - (2)
                                                     End result - (lincoln) - (1)
                                                         Rings a bell - (broomberg)

There are plenty of wrong people who just don't rate correction.
139 ms