Hello, upon our recent upgrade to SQL Server 2005, there are three issues that have plagued me that I cannot find a workaround for, despite much forum scouring.
1. I have a DTS package that I migrated to SSIS. The migration seemed to go smoothly, and I can successfully run the SSIS package. However, when I try to create an SSIS job from it, the job fails to run. I merely get a "The step failed" error message. I've seen related posts, none with fixes.
2. DELETE FROM table_a WHERE id IN (SELECT id FROM table_b). A simple command that would take seconds to run in SQL 2000 now takes almost an hour. I've heard reports of other people experience insanely long run times with subqueries. I've also experienced it with some subselects, which I was forced to re-write. This statement exists in a stored procedure that used to run every half hour... which is now impossible.
3. EXEC sp_a '4/21/2006', '4/27/2006' works fine. The same command (either using the dates explicitly or assigning to variables) does not work when called from a scheduled stored procedure. It does not fail nor produce an error message, but simply does nothing. It worked fine in SQL 2000.
Any suggestions or help from anyone would be greatly appreciated. These problems have been extremely frustrating. I was hoping SP1 might help, if they were bugs, but I installed it last night with no improvements.
Please help!
? Hi, Re your Question 1: I assume by "SSIS job" you mean a SQL Server Agent job. Is that correct? If you create a simple package from scratch in SSIS are you able to successfully run that package on a schedule? Andrew Watt [MVP] <wargy2@.discussions.microsoft.com> wrote in message news:9f5c4d0b-217b-417f-a49f-cdf4847d0a84@.discussions.microsoft.com... Hello, upon our recent upgrade to SQL Server 2005, there are three issues that have plagued me that I cannot find a workaround for, despite much forum scouring. 1. I have a DTS package that I migrated to SSIS. The migration seemed to go smoothly, and I can successfully run the SSIS package. However, when I try to create an SSIS job from it, the job fails to run. I've seen related posts, none with fixes. 2. DELETE FROM table_a WHERE id IN (SELECT id FROM table_b). A simple command that would take seconds to run in SQL 2000 now takes almost an hour. I've heard reports of other people experience insanely long run times with subqueries. I've also experienced it with some subselects, which I was forced to re-write. This statement exists in a stored procedure that used to run every half hour... which is now impossible. 3. EXEC sp_a '4/21/2006', '4/27/2006' works fine. The same command (either using the dates explicitly or assigning to variables) does not work when called from a schedule stored procedure. It does not fail nor produce and error message, but simply does nothing. It worked fine in SQL 2000. Any suggestions or help from anyone would be greatly appreciated. These problems have been extremely frustrating. I was hoping SP1 might help, if they were bugs, but I installed it last night with no improvements. Please help!|||Hello, thanks for the reply!
Yes, I do mean a SQL Server Agent job with a type of "SSIS". Furthermore, yes, I was able to create a job from a simple SSIS package and run it (it doesn't necessarily have to be scheduled to create the problem).
Obviously this indicates that there's something going on in my SSIS package, but what I'm wondering is why there'd be any difference in running it manually versus as the only step in a job? Does anyone know what SQL does differently so that maybe I could locate the source of the problem. It's especially difficult and frustrating when it doesn't give you any more feedback than "The step failed".
|||Last try... these have really got me stuck, and frustrated. Any body with any help or suggestions? An MVP?|||2. Do you have plans for 2000 and 2005 to compare against?3. Is SQL Agent running? Have you run profiler traces to verify the procedure is being run and have you looked at the agent logs.|||
1. If you have your package stored in the file system, the SQL Server Agent account needs to have the right permission on the dbtx file. Check this, if e.g. execute permission is set on the package.dtsx file on Windows level
2. Did you run update statistics on the table or do you have auto update statistics enabled on the database?
3. there were changes in SP1 which affected date formats espacially in packages and you can find either here in this forum some hints or in the Integration services forum
Nobsay
|||Thank you for your suggestions!!
2. Yes, I compared plans, and they're nearly identical... only vary by a few percentage. If, in SQL 2005, it estimated accurately, it would be 99.9999999999999999% cost for the one table scan step, and nearly nothing for the rest.
3. Yes, SQL Agent is running. The logs do not show any errors related to that problem, I don't believe. Even if it did fail due to an error, wouldn't it do the same when run in a query, outside of this stored procedure?
Thanks again.
|||Thank you for your suggestions as well!
1. It is not in the file system, it is in MSDB. Are there any permissions neccesary for this, I can't seem to find any?
2. No, I do not have any statistics enabled.
3. I manually enter the dates as '5/1/2006', for example... and that's when it DOES work. It does not work when I assign getdate() to a variable. I would assume/hope SQL returns usable formats in it's own getdate function. Also, if I run the same commands, even using getdate(), manually in a query, it works fine. It just does not work in the stored procedure it's being called from (note: it is not inside any kind of loop, if statement, etc.). This is not in any integration services project.
Again, thank you for your suggestions as well. I'd really like to figure these problems out, as they seem so extremely simple.
|||2. Can we see the plans? What are # of rows involved?
3. When run from Agent, the proc can fail because it may be connecting as a different user. Have you tried use profiler to see what agent is running against SQL Server?
|||Again, thank you for the reply.
2. There are about 117,000 rows involved. The plan is mainly just a table scan, which says costs about 82%. I can try to post it here, but I think the problem has more to do with lack of optimization of subqueries in SQL Server 2005 which I've read about.
3. It's running as the SQL Agent Service Account, and there are no special permission on the stored proc.
I'm leaning more and more towards bugs in SQL, though I appreciate all your feedback.
|||1. I had similiar issues with storing packages in MSDB and in the integration services forum I found the hint to store packages in file system rather than in the MSDB due to problems with security ;-) You will find a lot of posts there regarding permission settings and packages in jobs. Just search the forum there, even MS people propose to store packages not in the MSDB due to those problems.
|||1. I have tried storing it in the file system with no luck; but, since that sounds like it is the preferred way, I will try keeping in the file system and playing with the permissions. Thank you!
No comments:
Post a Comment