I remember in SQL 2000 it was possible to execute queries serially, and not quite release the locks from before, thus locking the db (well, the queries anyway. SQL server is pretty robust
( ... )
The issue is: query #6 works fine. Query #6 only breaks when there are more than 11 queries in the procedure, and only for one location. The query itself is absurdly simple, along the lines of: SELECT SUM(foo) FROM table WHERE location=X and account#=Y and month = 12 and year = 2008- no joins or anything that complex. Not that many records either.
When I run just query 6, it comes back quickly with the right result. When I run queries 1-10, query 6 comes back with a result. When I run queries 1-11, or even 1-5,7-12, it hangs at the sixth one executed, so long as the sixth one in the sequence is hitting Oracle Financials.
I'm trying to figure out how to get a TSql procedure to break things up into different batches, see if that helps. Maybe wrapping up some extra begins and ends or something.
Is it possible to reorder it, such that 6 gets executed after 7 or before 5? My thought is then, study the crap out of 5... something there might be screwing things up.
How do ALL of them run together (sequentially) at once, when it's just pure sql in sql query analyzer? IE, no dynamic sql?
If they're not dynamic SQL, they're not running in SQL server. The whole job of my TSQL is to build queries and ship them off to other servers using OPENQUERY.
I see what you're saying though- I'd have to write some code to get it to output the code that it's running; I might do that, but I think I've found a lead.
So it's not the query that's the problem, but BUILDING the query? If that's the case another long shot is an (in)compatible data type. ie, you'd THINK char and varchar should work together, but they're not for some reason... You could look at the schema itself too.
I know, I know, a billion places to look... but at least you have a lead! I'll be curious what the final answer is... Keep me posted.
do *any* queries previous to #6 go to Oracle Financials?
Is there any chance that there's something really weird going on at a lower level? The last time I saw something that looked similar to this (identical database queries intermittently failing from one location only) , it was solved when the networking department toggled a router setting involving how the router handled fragmented packets.
Reply
When I run just query 6, it comes back quickly with the right result. When I run queries 1-10, query 6 comes back with a result. When I run queries 1-11, or even 1-5,7-12, it hangs at the sixth one executed, so long as the sixth one in the sequence is hitting Oracle Financials.
I'm trying to figure out how to get a TSql procedure to break things up into different batches, see if that helps. Maybe wrapping up some extra begins and ends or something.
Reply
How do ALL of them run together (sequentially) at once, when it's just pure sql in sql query analyzer? IE, no dynamic sql?
Reply
I see what you're saying though- I'd have to write some code to get it to output the code that it's running; I might do that, but I think I've found a lead.
Reply
I know, I know, a billion places to look... but at least you have a lead! I'll be curious what the final answer is... Keep me posted.
Reply
Is there any chance that there's something really weird going on at a lower level? The last time I saw something that looked similar to this (identical database queries intermittently failing from one location only) , it was solved when the networking department toggled a router setting involving how the router handled fragmented packets.
Reply
Leave a comment