Database

Re: Find tables used in stored procedures

5-Jan-10 9:21

You're welcome Smile | :)

There are no 'real' options to get the multiple result-tables into a temptable under Sql 2000 that I'm aware of. In Sql2005, you could try using a CLR sproc.

As a hack, perhaps you could execute a DOS-command from SQL? If you can execute isql.exe, then you can pipe the result of the command to a textfile. It would get real messy if you try to parse the resulting textfile using SQL, so it might be easier to execute a small console application that parses it and writes it to the database.

I are Troll Suspicious | :suss:

Corporal Agarn5-Jan-10 9:38

Corporal Agarn

5-Jan-10 9:38

Thank you, I will look into the "DOS" solution tomorrow. Sometime in the next three months we are upgrading to 2008.

Oracle 10g server is not starting on system startup

A k ch4-Jan-10 23:11

A k ch

4-Jan-10 23:11

Hi frd's

I am using windows server 2003 and oracle 10g Express Edition.
Unlike SQL server oracle 10g service is not starting at system start up
Can anybody suggest me a solution for this.

Thanks & Regards,
Anil Chelasani

Re: Oracle 10g server is not starting on system startup

Eddy Vluggen5-Jan-10 7:37

Re: GROUP BY Clause... [modified]

5-Jan-10 7:37

Can you check the path "ORACLE_BASE\ORACLE_HOME\database"? There could be an error-log for ORADIM there.

I are Troll Suspicious | :suss:

GROUP BY Clause...

Illegal Operation4-Jan-10 15:28

Illegal Operation

4-Jan-10 15:28

Hi!

I have a table that contains a Date and Minutes. I want to create a query to group the entries by Date so that instead of seeing more than one entry on a day, you will only see one entry date with the total minutes?

Can anyone shed some light on this issue?

Thank you!!

Illegal Operation

Re: GROUP BY Clause...

Niladri_Biswas4-Jan-10 15:56

Niladri_Biswas

4-Jan-10 15:56

Try this

Sample Data

declare @t table(dt date, tm int)
insert into @t 
	select '2010-01-05',15 union all 
	select '2010-01-05',16 union all 
	select '2010-01-05',18 union all 
	select '2010-01-05',36 union all 
	select '2010-01-05',59 union all 
	select '2010-01-06',01 union all 
	select '2010-01-06',20 union all 
	select '2010-01-07',30 union all 
	select '2009-12-21',34 union all 
	select '2009-12-22',12

Query

select dt,CntMins  = COUNT(tm),TotalMins = SUM(tm) from @t
group by dt
--order by SUM(tm) desc --[Incase you want to see the result in Descending Order]

Output

dt	CntMins	TotalMins
2009-12-21	1	34
2009-12-22	1	12
2010-01-05	5	144
2010-01-06	2	21
2010-01-07	1	30

Niladri Biswas

Mycroft Holmes4-Jan-10 19:07

4-Jan-10 19:07

Group by only the date part of the datetime, modified being the datetime field

GROUP BY CONVERT(DATETIME,Modified,103)

[edit] the above does not work, if you are usiong 2008 the following will work using the new DATE data type.

SELECT COUNT(*)
FROM EquityCounter
GROUP BY CONVERT(DATE,Modified)

If you are using 2005 I would suggest chopping up the string daettime like so:

GROUP BY CONVERT(DATETIME,LEFT(CONVERT(VARCHAR(50),Modified),11))

[/edit]

Still gotta be better than a temp table!

Never underestimate the power of human stupidity
RAH

modified on Tuesday, January 5, 2010 1:16 AM

Re: GROUP BY Clause...

J4amieC4-Jan-10 21:52

J4amieC

4-Jan-10 21:52

Mycroft Holmes wrote:
Still gotta be better than a temp table!

The temp table in the answer above was only to provide example data methinks!

Re: GROUP BY Clause...

Mycroft Holmes4-Jan-10 22:42

General Performance Question

4-Jan-10 22:42

Me didn't look at the details of the answer, just looked at the complexity and shook the head. I don't think Nilandri answered the question which was to group by the date only in a datetime field that has the time component in it.

Never underestimate the power of human stupidity
RAH

#realJSOP4-Jan-10 11:22

4-Jan-10 11:22

How bad would a stored proc have to be that could not return more than 3000 records before timing out, regardless of how big your timeout value was? We're talking SQL Server 2005 running on an Itanium server...

.45 ACP - because shooting twice is just silly
-----
"Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997
-----
"The staggering layers of obscenity in your statement make it a work of art on so many levels." - J. Jystad, 2001

Eddy Vluggen4-Jan-10 12:15

4-Jan-10 12:15

John Simmons / outlaw programmer wrote:
regardless of how big your timeout value was?

Even with a demanding query you'd have a hard time with timing out "regardless how big the value". I'd go for an endless loop in a cursor.

Can you count the cursors, temporary tables and the udf's? $Shucks | :-\$

I are Troll Suspicious | :suss:

#realJSOP4-Jan-10 12:21

4-Jan-10 12:21

Well, it does eventually come back (that's how they know about the 3000 record limit). An endless loop wouldn't come back at all...

Eddy Vluggen4-Jan-10 13:22

4-Jan-10 13:22

The latest SQL Management Studio has a cool query-analyzer that even proposes indexes. It might help in tracking down the worst offenders - that way you don't have to plow through all of them.

What's it doing? "Just" some TSQL, or is it calling a webservice and writing the results to a networkdrive?

--edit--
What MyCroft said, and I'm still curious to what it's doing Smile | :)

I are Troll Suspicious | :suss:

#realJSOP4-Jan-10 14:32

4-Jan-10 14:32

I dunno - the boss says it times out, shrugs his shoulders, and we all laugh a little at the problem.

Mycroft Holmes4-Jan-10 13:13

4-Jan-10 13:13

John Simmons / outlaw programmer wrote:
How bad would a stored proc have to be

Really crappy, I've seen this where SSMS performs the query in seconds and the UI takes ages. Is this the case?

Have you looked at the execution plan, it usually suggests indexes if there are glaring requirements. Otherwise just look for the most expensive operation and try and optimise it.

Never underestimate the power of human stupidity
RAH

Mycroft Holmes4-Jan-10 19:01

4-Jan-10 19:01

Ok so is this the same proc you are bitching about in the Lounge - and you need to ask the question.

Never underestimate the power of human stupidity
RAH

#realJSOP4-Jan-10 23:25

4-Jan-10 23:25

No (at least I don't think so). I'm not bitching about it either. Here, I asked a question. In the Liunge, I merely stated my amazement at somethig regarding the job.

Mycroft Holmes5-Jan-10 0:12

5-Jan-10 0:12

So how did the execution plan investigation go?

I just noted the coincidence of the enquiry here and the Lounge entry and wondered if the very slow proc was 1500 lines of unformatted tsql which would probably account for the slow response Big Grin | :-D

Never underestimate the power of human stupidity
RAH

David Skelly5-Jan-10 2:10

David Skelly

5-Jan-10 2:10

I'm not entirely sure what sort of response you're looking for here. How bad would a stored proc have to be? Well, I guess that depends on what it does. If it's a simple select, then it would have to be pretty bad. If it's a massive stored proc that does a zillion different things and only gets run once a year then I guess that it might not be badly written, it might just be slow and time-consuming, and you'll either have to live with it or find a different approach (don't do it all in one stored proc, break it down into manageable chunks, something like that).

I don't know that anyone will really be able to give you much advice beyond what you've already got from the others, certainly not without knowing more detail about the stored proc.

#realJSOP6-Jan-10 0:35

6-Jan-10 0:35

I don't really know any of the details. The only things I know are that "somewhere" during the process of doing whatever they do in the database, it times out and they can only get about 3000 records before it does. I was just looking for a starting point as to where to look. What makes it harder to be more specific is that they don't know what part of the process is timing out. Like I said, they just say it times out, they shrug, and everyone (except me) just kinda smiles about it.

The front-end is written in VB, so that should give you an idea of what I'm dealing with.

David Skelly6-Jan-10 2:24

David Skelly

6-Jan-10 2:24

It can be very hard to track these things down. We once had a stored proc that ran a query, returned some data, the front end then set a flag and saved the data back again. In development it completed within seconds. In live production use, it took 15 minutes.

When we looked into it, it turned out that the stored proc was actually running very quickly. So was the front-end processing to set the flag. All the time was being taken transferring the data across the network, because in the live database each row included a massive XML document that took forever to marshall off the database server, move it over the network and then marshall it all back for the front-end to receive. When the developer first tested it, the XML documents he used were teeny-tiny little things so it all ran quickly and no-one spotted the problem.

We rewrote the application so that the update was done in the stored proc without returning anything to the front end, and it finished in less than 10 seconds. When we released it to the users, we had people phoning us up saying, "I don't think this did anything, it was too quick..."

Tim Carmichael5-Jan-10 6:31

Tim Carmichael

5-Jan-10 6:31

Is it a stored procedure issue or a data issue?
We have a vendor supplied system where 90+ percent of the data is in one table.
We also have a user requested report that requires us to perform an inner join on said table with itself.

Tim

#realJSOP6-Jan-10 0:36