Query/Report processing time

I am having a frustrating problem with a report. It is pulling data from a
stored procedure, and I have tested it through query analyzer and i get
performance times of 4-5 seconds. However, when I select this same sproc
through Reporting Services during report building time, and hit the "!" to
pull the data, punch in the exact same parameters, it is taking upward of
30-40 seconds to pull the same data. What is the deal? It makes no rational
sense.How much data are we talking about? In QA, enable Client Statistics and what are the # of rows
affected and # of bytes sent?
Also, which query designer are you using? Does it have 4 panes and show tables or is it the 2-pane
textbox/grid designer? QA and the designers all use different underlying mechanisms for querying
the database. The VDT (4-pane) uses OLE-DB and the GQD (2-pane) uses the SQL Managed provider.
--
Thanks.
Donovan R. Smith
Software Test Lead
This posting is provided "AS IS" with no warranties, and confers no rights.
"isamu" <isamu@.discussions.microsoft.com> wrote in message
news:A7571FD3-FE20-4BE7-A2B2-6E11C0ACCBE0@.microsoft.com...
> Well, that is just it. It's NOT during the actual report processing that I
> am having this problem, it is when I am loading the dataset under the "Data"
> tab. When I hit the "!" and enter in my params, the thing is taking forever.
> But when I run the same sproc in Query Analyzer, it is taking 4-5 seconds.
> So, this has nothing to do with actual report processing, it is something it
> is doing when it is grabbing the data that is f'ed up (if you know what I
> mean). Hence, when I deploy this thing (although the report is really
> simple) since it is taking it forever to grab data that should take a few
> seconds, it is taking like 45 seconds to render this report. It is a real
> pain in the ass.
> "Andrew Byrne" wrote:
> > My guess is that in design time you are running the query through the .NET
> > IDE so I presume there is a bigger memory footprint required.
> >
> > That lag experienced is probably due to RS not caching the report between
> > runs, something which was improved in SP1.
> >
> > Again, I would say .NET is involved if you experience lag the first time (in
> > a session) you run a report - the CLR needs to load assemblies, everything is
> > running under IIS etc.
> >
> > Don't get me wrong - I agree that RS could be a lot faster - just merely
> > trying to come up with plausible reasons for the reduction in speed !
> >
> > "Dan Fell" wrote:
> >
> > >
> > > I too suffer from similar problems. I think the problem seems to lie in the
> > > rendering part of the reporting. The queries themselves take relatively
> > > little time but the report can take ages to appear on screen.
> > >
> > > Not only that but it can be quite erratic. There a lag when the Report
> > > Manager's pages and reports are first rendered and then they seem to render
> > > much quicker afterwards. Is this normal?
> > >
> > > DF
> > >
> > > "isamu" wrote:
> > >
> > > > I am having a frustrating problem with a report. It is pulling data from a
> > > > stored procedure, and I have tested it through query analyzer and i get
> > > > performance times of 4-5 seconds. However, when I select this same sproc
> > > > through Reporting Services during report building time, and hit the "!" to
> > > > pull the data, punch in the exact same parameters, it is taking upward of
> > > > 30-40 seconds to pull the same data. What is the deal? It makes no rational
> > > > sense.|||I have experienced the same thing, even though I am designing/running
directly on the server hosting RS.
It seems to be intermittent. I have had a report take 2-3 minutes
everytime. This could go on for hours, even after a reboot. Then, out of
nowhere it takes 3 seconds. FYI: The same query always took about 1
second through query analyzer. This seems to be an RS rendering issue...
Dan
___________________________
"isamu" <isamu@.discussions.microsoft.com> wrote in message
news:36026EB9-5AD5-4355-9C15-B7573A5DAB56@.microsoft.com...
> Argh ... well, this is quite odd, after working on this for about 2 hours
> yesterday and even running it a few times today, it appears to have fixed
> itself ... I am not sure, but perhaps this was some sort of Network issue
at
> my client's site. Thanks a lot for your time, and sorry to trouble you.
I
> guess if this issue ever comes up again, have the network guys check their
> latencies.
> "Donovan R. Smith [MSFT]" wrote:
> > How much data are we talking about? In QA, enable Client Statistics and
what are the # of rows
> > affected and # of bytes sent?
> >
> > Also, which query designer are you using? Does it have 4 panes and show
tables or is it the 2-pane
> > textbox/grid designer? QA and the designers all use different
underlying mechanisms for querying
> > the database. The VDT (4-pane) uses OLE-DB and the GQD (2-pane) uses
the SQL Managed provider.
> >
> > --
> > Thanks.
> >
> > Donovan R. Smith
> > Software Test Lead
> >
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> > "isamu" <isamu@.discussions.microsoft.com> wrote in message
> > news:A7571FD3-FE20-4BE7-A2B2-6E11C0ACCBE0@.microsoft.com...
> > > Well, that is just it. It's NOT during the actual report processing
that I
> > > am having this problem, it is when I am loading the dataset under the
"Data"
> > > tab. When I hit the "!" and enter in my params, the thing is taking
forever.
> > > But when I run the same sproc in Query Analyzer, it is taking 4-5
seconds.
> > > So, this has nothing to do with actual report processing, it is
something it
> > > is doing when it is grabbing the data that is f'ed up (if you know
what I
> > > mean). Hence, when I deploy this thing (although the report is really
> > > simple) since it is taking it forever to grab data that should take a
few
> > > seconds, it is taking like 45 seconds to render this report. It is a
real
> > > pain in the ass.
> > >
> > > "Andrew Byrne" wrote:
> > >
> > > > My guess is that in design time you are running the query through
the .NET
> > > > IDE so I presume there is a bigger memory footprint required.
> > > >
> > > > That lag experienced is probably due to RS not caching the report
between
> > > > runs, something which was improved in SP1.
> > > >
> > > > Again, I would say .NET is involved if you experience lag the first
time (in
> > > > a session) you run a report - the CLR needs to load assemblies,
everything is
> > > > running under IIS etc.
> > > >
> > > > Don't get me wrong - I agree that RS could be a lot faster - just
merely
> > > > trying to come up with plausible reasons for the reduction in speed
!
> > > >
> > > > "Dan Fell" wrote:
> > > >
> > > > >
> > > > > I too suffer from similar problems. I think the problem seems to
lie in the
> > > > > rendering part of the reporting. The queries themselves take
relatively
> > > > > little time but the report can take ages to appear on screen.
> > > > >
> > > > > Not only that but it can be quite erratic. There a lag when the
Report
> > > > > Manager's pages and reports are first rendered and then they seem
to render
> > > > > much quicker afterwards. Is this normal?
> > > > >
> > > > > DF
> > > > >
> > > > > "isamu" wrote:
> > > > >
> > > > > > I am having a frustrating problem with a report. It is pulling
data from a
> > > > > > stored procedure, and I have tested it through query analyzer
and i get
> > > > > > performance times of 4-5 seconds. However, when I select this
same sproc
> > > > > > through Reporting Services during report building time, and hit
the "!" to
> > > > > > pull the data, punch in the exact same parameters, it is taking
upward of
> > > > > > 30-40 seconds to pull the same data. What is the deal? It
makes no rational
> > > > > > sense.
> >
> >
> >

Query wizard

in sql server 2000, table view, i can right click any table and invoke query
select wizard. At any time I can select spetial icon on toolbar and change
type of the query: select, insert from, insert into, update, delete. I
missed this type of functionality in sql server 2005 and 2008
How about this?
Open up your SSMS and connect to your SQL Server instance.
Go and find your database and go to one of the tables. Right click on it and
go to Script Table as and so on...
Is this are you looking for?
Ekrem ?nsoy
"Aleks Kleyn" <AleksKleyn@.discussions.microsoft.com> wrote in message
news:29928A48-A418-49FE-8A08-835E9B1DD913@.microsoft.com...
> in sql server 2000, table view, i can right click any table and invoke
> query
> select wizard. At any time I can select spetial icon on toolbar and change
> type of the query: select, insert from, insert into, update, delete. I
> missed this type of functionality in sql server 2005 and 2008

Query wizard

in sql server 2000, table view, i can right click any table and invoke query
select wizard. At any time I can select spetial icon on toolbar and change
type of the query: select, insert from, insert into, update, delete. I
missed this type of functionality in sql server 2005 and 2008How about this?
Open up your SSMS and connect to your SQL Server instance.
Go and find your database and go to one of the tables. Right click on it and
go to Script Table as and so on...
Is this are you looking for?
--
Ekrem Ã?nsoy
"Aleks Kleyn" <AleksKleyn@.discussions.microsoft.com> wrote in message
news:29928A48-A418-49FE-8A08-835E9B1DD913@.microsoft.com...
> in sql server 2000, table view, i can right click any table and invoke
> query
> select wizard. At any time I can select spetial icon on toolbar and change
> type of the query: select, insert from, insert into, update, delete. I
> missed this type of functionality in sql server 2005 and 2008

Query wizard

in sql server 2000, table view, i can right click any table and invoke query
select wizard. At any time I can select spetial icon on toolbar and change
type of the query: select, insert from, insert into, update, delete. I
missed this type of functionality in sql server 2005 and 2008How about this?
Open up your SSMS and connect to your SQL Server instance.
Go and find your database and go to one of the tables. Right click on it and
go to Script Table as and so on...
Is this are you looking for?
Ekrem ?nsoy
"Aleks Kleyn" <AleksKleyn@.discussions.microsoft.com> wrote in message
news:29928A48-A418-49FE-8A08-835E9B1DD913@.microsoft.com...
> in sql server 2000, table view, i can right click any table and invoke
> query
> select wizard. At any time I can select spetial icon on toolbar and change
> type of the query: select, insert from, insert into, update, delete. I
> missed this type of functionality in sql server 2005 and 2008

query with time period spanning two days

I would like to run queries with data that sometimes span two days. The queries require start and end dates as well as start and end times. The following code works fine if the start time is less than the end time:

select * from tst01 where convert(varchar, [DateTime],126) between '2005-09-15' and

'2006-01-27' and convert(varchar, [DateTime],114) between '09:00:00' and

'17:00:00' order by [DateTime]

However, if I try to run a query where the start time is greater than the end time (e.g., start time 5:00pm on one day until 9:00am the next day), the query returns an empty table.

select * from tst01 where convert(varchar, [DateTime],126) between '2005-09-15' and

'2006-01-27' and convert(varchar, [DateTime],114) between '17:00:00' and

'09:00:00' order by [DateTime]

I need a way to indicate that the start and end times span two days. Can anybody help with this?

I think I found the answer:

select * from tst01 where convert(varchar, [DateTime],126) between '2005-09-15' and

'2006-01-27' and ((convert(varchar, [DateTime],114) between '17:00:00' and

'23:59:59') or (convert(varchar, [DateTime],114) between '00:00:00' and

'09:00:00')) order by [DateTime]

|||

I'm not sure exactly what you're after, but you should investigate the DATEADD() or DATEDIFF() functions. They will handle the day differences for you.

CREATE TABLE #T1 (StartTime datetime, EndTime datetime)

INSERT INTO #T1

VALUES ('2000-01-01 8:00', '2000-01-01 8:05')

INSERT INTO #T1

VALUES ('2000-01-02 8:00', '2000-01-01 7:59')

SELECT DATEDIFF(hh, EndTime, StartTime)

FROM #T1

You may have to play with either of these functions if you want the full year, month, day, etc., but you just string them together to get that.

|||

I have numerous tables which contain data recorded over a 24hr period for several years. The tables each contain a column called [DateTime] (for historic reasons), as well as several other columns containing data recorded during those datetimes (in each record).

At times, it is necessary to examine data recorded during non-business hours (e.g., 5:00pm to 9:00am the following day). Hence, the start time (17:00:00 ) is greater than the end time (09:00:00) which falls on the following date (see code in posts above). The code in my second posting while a bit of a kludge seems to work. I'm not sure how using dateadd or datediff would help. But, thanks anyway.

|||I see. DATEADD() or DATEDIFF() will handle the day span problem for you, since they are aware (as shown in the example) that a day has passed between two hour or minute comparisons. You can use that logic to find and process the rows that have that condition.

Query with MAX Date

I have the following situation:
The name of a product can change with time. These changes are stored in a
table with 3 columns: Product_id, Date and ProductName with Product_id and
Date forming the Primary Key.
I want to run a query that returns the product_id and each product's latest
name. For the life of me, I can't get my head rould what such a query would
look like. I can get a query that gives me just the product_id and the Max
of Date with a group by on the Product_id but when I introduce the
ProductName, it returns ALL names.
I have a gut feeling that there may be a subquery involved, or am I barking
up the wrong tree?
Any help much appreciated.
PeteYou can either use a subquery:
SELECT Product_id, ProductName
FROM Your_table t1
WHERE Date = (SELECT MAX(Date) FROM Your_table t2 WHERE t1.Product_id =
t2.Product_id)
or a derived table:
SELECT t1.Product_id, t1.ProductName
FROM Your_table t1
INNER JOIN
(SELECT Product_id, MAX(Date) AS max_date
FROM Your_table
GROUP BY Product_id) t2
ON t1.Product_id = t2.Product_id
AND t1.Date = t2.max_date
Jacco Schalkwijk
SQL Server MVP
"Italian Pete" <ItalianPete@.discussions.microsoft.com> wrote in message
news:4847B525-302C-4354-99EE-8165C56B60D9@.microsoft.com...
>I have the following situation:
> The name of a product can change with time. These changes are stored in a
> table with 3 columns: Product_id, Date and ProductName with Product_id and
> Date forming the Primary Key.
> I want to run a query that returns the product_id and each product's
> latest
> name. For the life of me, I can't get my head rould what such a query
> would
> look like. I can get a query that gives me just the product_id and the
> Max
> of Date with a group by on the Product_id but when I introduce the
> ProductName, it returns ALL names.
> I have a gut feeling that there may be a subquery involved, or am I
> barking
> up the wrong tree?
> Any help much appreciated.
> Pete|||First, please change your column name 'Date' to something more meaningful
and something that doesn't use a reserved word. Also, you should be
consistent in your column naming. Why does Product_id have an underscore,
but ProductName not? Finally, in the future, please post DDL, sample data,
and desired results. See http://www.aspfaq.com/5006
In the meantime, you can try this:
SELECT o.Product_id, i.MaxDate, o.ProductName
FROM Products o
INNER JOIN
(
SELECT Product_id, MaxDate = MAX([Date])
FROM Products
GROUP BY Product_id
) i
ON o.Product_id = i.Product_id
AND o.[Date] = i.MaxDate
http://www.aspfaq.com/
(Reverse address to reply.)
"Italian Pete" <ItalianPete@.discussions.microsoft.com> wrote in message
news:4847B525-302C-4354-99EE-8165C56B60D9@.microsoft.com...
> I have the following situation:
> The name of a product can change with time. These changes are stored in a
> table with 3 columns: Product_id, Date and ProductName with Product_id and
> Date forming the Primary Key.
> I want to run a query that returns the product_id and each product's
latest
> name. For the life of me, I can't get my head rould what such a query
would
> look like. I can get a query that gives me just the product_id and the
Max
> of Date with a group by on the Product_id but when I introduce the
> ProductName, it returns ALL names.
> I have a gut feeling that there may be a subquery involved, or am I
barking
> up the wrong tree?
> Any help much appreciated.
> Pete|||Here is a solution based on guesswork:
SELECT t1.product_id, t1.product_name
FROM tbl t1
WHERE ( SELECT MAX( t2.dtcol )
FROM tbl t2
WHERE t2.product_id = t1.product_id ) = t1.dtcol ;
If this is not what you are looking for, refer to www.aspfaq.com/5006 and
provide required information.
Anith

Query using IN and LIKE at the same time

The following query does not compile:
SELECT * FROM Food WHERE FoodName IN LIKE ('%Beer%', '%apple%')
OK, so it might it been a silly attempt, but seriously... I need to return
all the foods that have the words 'Beer' Or 'Apple' anywhere in the name
including words like 'Rootbeer'. The reason why I am trying to use the IN is
because the ('%Beer%', '%apple%') will end up being replaced with a sub
query.
Thanks.SELECT * FROM Food AS f
WHERE EXISTS(
SELECT * FROM [sub_query_table] AS t
WHERE CHARINDEX(t.[field], f.[field]) > 0
)
Probably not very efficient, but will get what you want.
"Gecko" wrote:

> The following query does not compile:
> SELECT * FROM Food WHERE FoodName IN LIKE ('%Beer%', '%apple%')
> OK, so it might it been a silly attempt, but seriously... I need to return
> all the foods that have the words 'Beer' Or 'Apple' anywhere in the name
> including words like 'Rootbeer'. The reason why I am trying to use the IN
is
> because the ('%Beer%', '%apple%') will end up being replaced with a sub
> query.
> Thanks.
>
>|||There are several ways of doing this:
SELECT *
FROM Food
WHERE FoodName LIKE '%Beer%'
OR FoodName LIKE '%apple%' ;
-- or
SELECT *
FROM Food
INNER JOIN ( SELECT 'Beer' UNION
SELECT 'apple' ) D ( p )
ON FoodName LIKE '%' + p + '%' ;
Anith|||Thanks KH,
Just curios, would there be a way of doing the same things but instead of
returning a record if the record contains either '%Beer%' OR '%Apple%', I
would like to return the record only if it contains both '%Beer%' AND
'%Apple%' (notice how the first one is OR and the other AND)
"KH" <KH@.discussions.microsoft.com> wrote in message
news:06AAF7A2-7878-49DF-AD46-B7A1837B5CF6@.microsoft.com...
> SELECT * FROM Food AS f
> WHERE EXISTS(
> SELECT * FROM [sub_query_table] AS t
> WHERE CHARINDEX(t.[field], f.[field]) > 0
> )
> Probably not very efficient, but will get what you want.
>
> "Gecko" wrote:
>|||These might give you what you're looking for:
select * from Food as f
where 0 < all (
select charindex(t.[field],f.[field])
from subquerytable as t
)
or
select * from Food as f
where not exists (
select * from subquerytable
where charindex(t.[field],f.[field]) = 0
)
You might also look into SQL Server's full-text indexing.
Steve Kass
Drew University
Gecko wrote:

>Thanks KH,
>Just curios, would there be a way of doing the same things but instead of
>returning a record if the record contains either '%Beer%' OR '%Apple%', I
>would like to return the record only if it contains both '%Beer%' AND
>'%Apple%' (notice how the first one is OR and the other AND)
>
>"KH" <KH@.discussions.microsoft.com> wrote in message
>news:06AAF7A2-7878-49DF-AD46-B7A1837B5CF6@.microsoft.com...
>
>
>|||I think what you want is the ability to load tables with criteria and
not have to use dynamic SQL:
skill = Java AND (skill = Perl OR skill = PHP)
becomes the disjunctive canonical form:
(Java AND Perl) OR (Java AND PHP)
which we load into this table:
CREATE TABLE Query
(and_grp INTEGER NOT NULL,
skill CHAR(4) NOT NULL,
PRIMARY KEY (and_grp, skill));
INSERT INTO Query VALUES (1, 'Java');
INSERT INTO Query VALUES (1, 'Perl');
INSERT INTO Query VALUES (2, 'Java');
INSERT INTO Query VALUES (2, 'PHP');
Assume we have a table of job candidates:
CREATE TABLE Candidates
(candidate_name CHAR(15) NOT NULL,
skill CHAR(4) NOT NULL,
PRIMARY KEY (candidate_name, skill));
INSERT INTO Candidates VALUES ('John', 'Java'); --winner
INSERT INTO Candidates VALUES ('John', 'Perl');
INSERT INTO Candidates VALUES ('Mary', 'Java'); --winner
INSERT INTO Candidates VALUES ('Mary', 'PHP');
INSERT INTO Candidates VALUES ('Larry', 'Perl'); --winner
INSERT INTO Candidates VALUES ('Larry', 'PHP');
INSERT INTO Candidates VALUES ('Moe', 'Perl'); --winner
INSERT INTO Candidates VALUES ('Moe', 'PHP');
INSERT INTO Candidates VALUES ('Moe', 'Java');
INSERT INTO Candidates VALUES ('Celko', 'Java'); -- loser
INSERT INTO Candidates VALUES ('Celko', 'Algol');
INSERT INTO Candidates VALUES ('Smith', 'APL'); -- loser
INSERT INTO Candidates VALUES ('Smith', 'Algol');
The query is simple now:
SELECT DISTINCT C1.candidate_name
FROM Candidates AS C1, Query AS Q1
WHERE C1.skill = Q1.skill
GROUP BY Q1.and_grp, C1.candidate_name
HAVING COUNT(C1.skill)
= (SELECT COUNT(*)
FROM Query AS Q2
WHERE Q1.and_grp = Q2.and_grp);
You can retain the COUNT() information to rank candidates. For example
Moe meets both qualifications, while other candidates meet only one of
the two. You can Google "canonical disjunctive form" for more details.
This is a form of relatioanl division.

Query Total Response Time

How can I know the total time (in miliseconds) for my SQL Server to answer a
query. I want to issue a query using an Query Analyzer and Measure the
Servers total response time. Thanks in advance!
On Wed, 2 Mar 2005 23:51:02 -0800, Borikoy wrote:

>How can I know the total time (in miliseconds) for my SQL Server to answer a
>query. I want to issue a query using an Query Analyzer and Measure the
>Servers total response time. Thanks in advance!
Hi Borikoy,
The simplest form is
SELECT CURRENT_TIMESTAMP
-- Insert your query here
SELECT CURRENT_TIMESTAMP
Slightly more advanced would be
DECLARE @.start datetime, @.end datetime
SET @.start = CURRENT_TIMESTAMP
-- Insert your query here
SET @.end = CURRENT_TIMESTAMP
SELECT DATEDIFF(ms, @.start, @.end)
In both cases, the time used to push the rows to the client is included.
For a clean measurement of time taken to process the query, I usually
change all SELECT queries to SELECT INTO queries (so that the ooutput
goes in a temp table instead of over a possibly slow network, or is
slowed by display processing).
Another way to get information about time taken is:
SET STATISTICS TIME ON
-- Insert your query here
SET STATISTICS TIME OFF
Check out the description of SET STATISTICS TIME in Books Online. And
you might wish to read up on SET STATISTICS IO as well.
Whatever way you use, you won't get accurate comparisons if you run
queries with data in the cache. Often, the first execution of a query
takes longer because data needs to be brought into cache from hard disk;
subsequent invocations go lots faster because the data is still in
cache, so the slow hard disk access is not necessary anymore. To clear
out the data cache and the procedure cache as well (so you won't reuse
old execution plans), run the following statements before doing your
tests:
CHECKPOINT -- Write dirty pages to disk
DBCC DROPCLEANBUFFERS -- Flush all non-dirty pages from cache
DBCC FREEPROCCACHE -- Remove all cached execution plans
Running the above statements on a production database will severely
impact the performance of your running processes - but I assume that you
don't intend to run performance tests on a live production database
anyway!
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
sql

Query to tranform rows to column

Hi

Your help for the following query will be highly apprecaited. I've
wasted alot of time on it. Data definition is at the bottom.

Thanks

-----------

Business need: User selects answers for few questions. Answers are
presented in the form of radio buttons.

Questions Table:

===================

QuestionID QuestionName

1 1.Rate your organization

2 10.Opportunity in your group

3 117.Effectiveness

------------

Answers Table:

==============

AnswerId AnswerName QuestionID

1 1.Best 1

2 2.Average 1

3 3.Wrose 1

4 1.Hardly Any 2

5 2.not too much 2

6 3.Average 2

7 4.Great Deal 2

8 1.Strong 3

9 2.Minor 3

10 3.Nothing 3

------------------

Lets say User ABC answered below:

For Question 1, she chose '2.Average'

For Question 2: she left blank

FOr Quesiton 3: she chose '2.Minor'

And for user XYZ:

For Question 1, she let blank

For Question 2: she left blank

FOr Quesiton 3: she chose '3.Nothing'

Then the Results table will look like this:

Results Table:

===============

Resultsid QuestionID AnswerId User

1 1 2 ABC

2 3 9 ABC

3 3 10 XYZ

----------------

Desired Output:

===============

User 1 10 117
-- -- -- --

ABC 2 2

XYZ 3

Explanation:

--> Show ALL distinct users on rows

--> show ALL distinct QuestionName on the columns BUT only display the
number before '.'

--> Show the chosen answer (answername) for each user but only display
the number before '.'

--------------------

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Questions]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)

drop table [dbo].[Questions]

GO

CREATE TABLE [dbo].[Questions] (

[QuestionId] [int] IDENTITY (1, 1) NOT NULL ,

[QuestionName] [nvarchar] (1000) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL

) ON [PRIMARY]

GO

----------

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Answers]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)

drop table [dbo].[Answers]

GO

CREATE TABLE [dbo].[Answers] (

[AnswerId] [int] IDENTITY (1, 1) NOT NULL ,

[AnswerName] [nvarchar] (150) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,

[QuestionId] [int] NULL

) ON [PRIMARY]

GO

-----------

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Results]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)

drop table [dbo].[Results]

GO

CREATE TABLE [dbo].[Results] (

[ResultId] [int] IDENTITY (1, 1) NOT NULL ,

[QuestionId] [int] NULL ,

[AnswerId] [int] NULL ,

[UserId] [nchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL

) ON [PRIMARY]

GO

--------------

INSERT Answers (

AnswerName ,

QuestionId

)

VALUES (

'1.Best' ,

1

)

go

INSERT Answers (

AnswerName ,

QuestionId

)

VALUES (

'2.Average' ,

1

)

go

INSERT Answers (

AnswerName ,

QuestionId

)

VALUES (

'3.Wrose' ,

1

)

go

INSERT Answers (

AnswerName ,

QuestionId

)

VALUES (

'1.Hardly Any' ,

2

)

go

INSERT Answers (

AnswerName ,

QuestionId

)

VALUES (

'2.not too much' ,

2

)

go

INSERT Answers (

AnswerName ,

QuestionId

)

VALUES (

'3.Average' ,

2

)

go

INSERT Answers (

AnswerName ,

QuestionId

)

VALUES (

'4.Great Deal' ,

2

)

go

INSERT Answers (

AnswerName ,

QuestionId

)

VALUES (

'1.Strong' ,

3

)

go

INSERT Answers (

AnswerName ,

QuestionId

)

VALUES (

'2.Minor' ,

3

)

go

INSERT Answers (

AnswerName ,

QuestionId

)

VALUES (

'3.Nothing' ,

3

)

go

----------

INSERT Questions (

QuestionName

)

VALUES (

'1.Rate your organization'

)

go

INSERT Questions (

QuestionName

)

VALUES (

'10.Opportunity in your group'

)

go

INSERT Questions (

QuestionName

)

VALUES (

'117.Effectiveness'

)

go

-----------

INSERT Results (

QuestionId ,

AnswerId ,

UserId

)

VALUES (

1 ,

2 ,

'ABC'

)

go

INSERT Results (

QuestionId ,

AnswerId ,

UserId

)

VALUES (

3 ,

9 ,

'ABC'

)

go

INSERT Results (

QuestionId ,

AnswerId ,

UserId

)

VALUES (

3 ,

10 ,

'XYZ'

)

goI could think of two ways of doing it:

select users.userid,
(select answerid from results where results.userid =
users.userid and questionid = 1) as answer1,
(select answerid from results where results.userid =
users.userid and questionid = 2) as answer2,
...
from (select distinct userid from results) as users
order by users.userid

or

select users.userid,
answer1.answerid as answer1,
answer2.answerid as answer2,
...
from (select distinct userid from results) as users
left outer join results answer1
on users.userid = answer1.userid
and answer1.answerid = 1
left outer join results answer2
on users.userid = answer2.userid
and answer1.answerid = 2
...
order by users.userid

--
David Rowland
Do you know what people are doing on your database server?
Find out with dbmonitor!
http://dbmonitor.tripod.com|||[posted and mailed, please reply in news]

(muzamil@.hotmail.com) writes:
> Your help for the following query will be highly apprecaited. I've
> wasted alot of time on it. Data definition is at the bottom.
>...
> Desired Output:
>===============
> User 1 10 117
> -- -- -- --
> ABC 2 2
> XYZ 3

SQL is not really well suited for type query, as the columns depend
on the data. You don't say so, but I assume that there can be
any number of questions. The usual recommendation for this sort of
crosstab queries is RAC, see http://www.rac4sql.net.

But since you provided scripts and tables, and everything else I usually
nag about, I felt compelled to provide a solution. This uses dynamic SQL.
For more information on dynamic SQL, see
http://www.sommarskog.se/dynamic_sql.html.

DECLARE @.selectlist varchar(8000),
@.selfjoin varchar(8000),
@.alias varchar(10),
@.elias varchar(10),
@.idstr varchar(10),
@.qnostr varchar(10)

-- Map id:s to the numbers before the dot.
CREATE TABLE #idmap(id int NOT NULL PRIMARY KEY,
qno smallint NOT NULL UNIQUE)

-- Help table to get all distinct users that appear.
CREATE TABLE #users (userid nchar(40) NOT NULL PRIMARY KEY)

-- Maps answers ids to numbers before the dot.
CREATE TABLE #answermap(id int NOT NULL PRIMARY KEY,
ansno smallint NOT NULL)

-- Fill Idmap
INSERT #idmap(id, qno)
SELECT QuestionId,
convert(smallint,
substring(QuestionName, 1, charindex('.', QuestionName) - 1))
FROM Questions

-- Fill answermap
INSERT #answermap(id, ansno)
SELECT AnswerId,
convert(smallint,
substring(AnswerName, 1, charindex('.', AnswerName) - 1))
FROM Answers

-- And get all users.
INSERT #users (userid)
SELECT DISTINCT UserId FROM Results

-- We now start to build a dynamic SQL statement. We build in two parts:
-- the SELECT list and the FROM clause.
SELECT @.selectlist = 'SELECT u.userid ',
@.selfjoin = 'FROM #users u '

-- We have to iterate of the #idmap table to build it. For the query
-- we need the id as a string, and the question number must be in [],
-- since it will be used as an identifier.
DECLARE build_cur INSENSITIVE CURSOR FOR
SELECT ltrim(str(id)), '[' + ltrim(str(qno)) + ']'
FROM #idmap
ORDER BY qno
OPEN build_cur

WHILE 1 = 1
BEGIN
FETCH build_cur INTO @.idstr, @.qnostr
IF @.@.fetch_status <> 0
BREAK

-- We will need two alias for the Results and #answermap table,
-- which will appear once for each question.
SELECT @.alias = 'a' + @.idstr, @.elias = 'e' + @.idstr

-- Add this question's answers to the SELECT list.
SELECT @.selectlist = @.selectlist + ', ' + @.qnostr + ' = ' +
@.elias + '.ansno '

-- And augment the FROM clause with this question. We left-join to
-- Results, since answer may be NULL. We need to look up answer in
-- the answer-map to get the real id.
SELECT @.selfjoin = @.selfjoin + char(10) +
'LEFT JOIN (Results ' + @.alias + ' JOIN #answermap ' + @.elias +
' ON ' + @.alias + '.AnswerId = ' + @.elias + '.id) ' +
' ON u.userid = ' + @.alias + '.UserId AND ' +
@.alias + '.QuestionId = ' + @.idstr
END
DEALLOCATE build_cur

-- Debug prints that show the actual query
PRINT @.selectlist
PRINT @.selfjoin

-- Run the beast!
EXEC (@.selectlist + @.selfjoin + ' ORDER BY u.userid')
go
DROP TABLE #users, #idmap, #answermap

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

query to search a column

Hi,
I know the column name but I don't know in which table it is existing. It
will take a lot of time if I search table by table..
Is there a way/query to find in which table a particular column is existing?
please let me know if there is one..
Thanks
Chandraselect * from information_schema.columns
where column_name='MyColumn'
"Chandra" <Chandra@.discussions.microsoft.com> wrote in message
news:270985E4-CB80-43DB-866C-411F0C207B5C@.microsoft.com...
> Hi,
> I know the column name but I don't know in which table it is existing. It
> will take a lot of time if I search table by table..
> Is there a way/query to find in which table a particular column is
existing?
> please let me know if there is one..
> Thanks
> Chandra
>|||Chandra
You can you the INFORMATION_SCHEMA.COLUMNS view as demonstrated below,
replacing NameOfColumnToSearchFor with the name of the column you are
searching for. This view contains one row for each column accessible to the
current user in the current database.
SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE column_name = 'NameOfColumnToSearchFor'
- Peter Ward
WARDY IT Solutions
"Chandra" wrote:

> Hi,
> I know the column name but I don't know in which table it is existing. It
> will take a lot of time if I search table by table..
> Is there a way/query to find in which table a particular column is existin
g?
> please let me know if there is one..
> Thanks
> Chandra
>|||Chandra
this is one of the ways.
select table_name from INFORMATION_SCHEMA.COLUMNS where column_name
=<columnname>
But careful when you have more than one table having same column name
Regards
R.D
"Chandra" wrote:

> Hi,
> I know the column name but I don't know in which table it is existing. It
> will take a lot of time if I search table by table..
> Is there a way/query to find in which table a particular column is existin
g?
> please let me know if there is one..
> Thanks
> Chandra
>|||Thanks Moshe!!! It solves my problem.
"Moshe" wrote:

> select * from information_schema.columns
> where column_name='MyColumn'
> "Chandra" <Chandra@.discussions.microsoft.com> wrote in message
> news:270985E4-CB80-43DB-866C-411F0C207B5C@.microsoft.com...
> existing?
>
>

Query to rerun missed jobs after a backup and restore.

Does anyone know of a Query I can run after restoring a db that will go
though and rerun all the jobs that were missed between the time of the
final differential pulled and the time the differential was restored. I
have about 50 jobs and it's really annoying to go through each job
and run it manually.
SQL 2000 SP4
Thanks BTWWell If no one knows of a procedure, then I will have to create one,
but I will need help with the logic.
Right now it looks like all the information I require is stored in two
tables sp_help_job & sp_help_jobschedule this can be joined on the
job_id and will provide the last run date and time as well as the
intervals.
What I Am looking at is to rerun all jobs that occurred in the past,
that need to be run again because they were missed during time of the
last differential and the final restore.
The Logic:
I know the current time, and I know when the next job is to be run. So
what I need to do is first get the current time, and compare it to the
last executed time. If the job interval is 15 minutes or less, don't
run the job. If the job time is greater the specified duration, run the
job.
I hope that makes sense.
-Matt-|||Let me see if I understand your probem. Are you asking what jobs would have
executed in a certain time window in the past, assuming all the schedules
have stayed the same? I suppose you can write some logic that would
duplicate our schedule calculation, but it's probably worth trying to
understand better how come those jobs have been missed. Did you shut down
agent during the time window when you did backup/restore?
Ciprian Gerea
SDE, SqlServer
This posting is provided "AS IS" with no warranties, and confers no rights.
"Matthew" <MKruer@.gmail.com> wrote in message
news:1144951366.394182.197090@.i40g2000cwc.googlegroups.com...
> Well If no one knows of a procedure, then I will have to create one,
> but I will need help with the logic.
> Right now it looks like all the information I require is stored in two
> tables sp_help_job & sp_help_jobschedule this can be joined on the
> job_id and will provide the last run date and time as well as the
> intervals.
> What I Am looking at is to rerun all jobs that occurred in the past,
> that need to be run again because they were missed during time of the
> last differential and the final restore.
> The Logic:
> I know the current time, and I know when the next job is to be run. So
> what I need to do is first get the current time, and compare it to the
> last executed time. If the job interval is 15 minutes or less, don't
> run the job. If the job time is greater the specified duration, run the
> job.
> I hope that makes sense.
> -Matt-
>|||Thanks for the reply.
The reason why the jobs were missed is simple. The databases in
question are being replicated in a live environment. So we are making a
backup of the database, restore that copy to a different system, and
then do a differential on the database. The problem lays in the doing
the restore for the differential. During a differential restore there
is a period of time (depending on the size) that jobs can run and not
be on the diff, hence there was a change made on one database and not
the other one. This is where the job would need to be run. These
databases are running 24/7 with tens to hundreds of gigs of data, and I
can literally say that delaying a diff for more then 2 days, its faster
to pull a new full, there is just that much data being moved/updated.
Somewhat off topic. I really hate the way the DB stores the date and
time. This would be so much easier if the date/time was a single
integer.|||BTW I think I have all the information I need using this Join
Statement.

code:
SELECT dbo.sysjobs.job_id, dbo.sysjobs.name, dbo.sysjobs.enabled,
dbo.sysjobservers.last_run_outcome,
dbo.sysjobservers.last_outcome_message,
dbo.sysjobservers.last_run_date,
dbo.sysjobservers.last_run_time, dbo.sysjobservers.last_run_duration,
dbo.sysjobschedules.next_run_date,
dbo.sysjobschedules.next_run_time,
dbo.sysjobschedules.freq_recurrence_factor,
dbo.sysjobschedules.freq_type,
dbo.sysjobschedules.freq_interval,
dbo.sysjobschedules.freq_subday_type,
dbo.sysjobschedules.freq_subday_interval
FROM dbo.sysjobs INNER JOIN
dbo.sysjobschedules ON dbo.sysjobs.job_id =
dbo.sysjobschedules.job_id INNER JOIN
dbo.sysjobservers ON dbo.sysjobschedules.job_id =
dbo.sysjobservers.job_id
Where last_run_outcome != '1' and last_run_outcome != '5' and
dbo.sysjobs.enabled = '1'
Order by dbo.sysjobs.name

Query to find SQL activity at specific time?

Ok, please pardon my complete ignorance; but I am extremely SQL challenged. I manage the server right now and I am the closest thing to a DBA at the moment with this server.

Here is the problem. I have a job that runs every hour and until last week it was running without any problems. Now, once a day it hangs, and I end up having to restart sql to get it to be functional again. I guess I could just kill the spid, but regardless, it's fails.

It doesn't happen at the same time each day. I thought it was from the backups doing a SQL backup job, but I eliminated that possibility. I can't find what is causing the problem, because the SQL logs just stop at the time that this happens. It's like some other job/query is running and taking all the sql server resources. But I can never catch it when it's happening and I have no event logs or SQL logs to go on.

Is there any query that I can run to find what happened around the specific time the job failed to run? I just need to find what process did what when this job stopped working.

Again, I apologize if my lack of SQL knowledge is making me ask dumb questions, but I am really lost.

Thanks in advanced!Without specifics, all you can get is general answers. Go Google SQL Server Black Box and see if that will help you.|||You can use SQL Profiler to run a SQL trace to a system file on the server or a network drive, or to a table on a different SQL instance. That way, you can choose what you want to look at, including users and spid's along with a lot of other data. It's completely customizable. You can also set the size so that it doesn't grow too large and then you can look at the data after the other server has crashed.

It's also important to be sure that you have enough processor, memory and disk space as the first and foremost problem. I've come into a lot of systems that were having "hanging" issues and the servers were bare minimum or below what they needed to be running.

I hope this helps.|||Thank you! I think that will do it. Now I have something to go on.

Query to count holidays

Hi,
I'm working on a helpdesk project and I require the calculation of the holidays.
I need to get the time difference of the assigned date and the solved date of the helpdesk tickets considering the week-end holidays and statutory holidays. Is there any possible way to do this. I need something similar to the NetworkDays function in excel.
Thanks.
Madhavi.I did something similar in a previous life. I created a master calendar table with columns for the date plus flag (bit) columns for weekends and holidays (and a third flag as I recall called working day). I then created a user-defined function which would take two dates as an input and return the number of "work" days elapsed between the two.

Perhaps not elegant, but it did work.

The master calendar table was also useful for reporting purposes. In your case, you might want a front-end interface to edit the calendar and identify which days are working versus non-working.

Regards,

hmscott

Hi,
I'm working on a helpdesk project and I require the calculation of the holidays.
I need to get the time difference of the assigned date and the solved date of the helpdesk tickets considering the week-end holidays and statutory holidays. Is there any possible way to do this. I need something similar to the NetworkDays function in excel.
Thanks.
Madhavi.|||Only way since holidays are uniqu to countries...

works well though

Query to Backup Current Database

Hi

Is there any query to backup and restore a database?In my application User has to Backup the current database and restore them at any time.I just need query which backup the database in a specified location and restore it.

Thanks

See BACKUP and RESTORE on Books Online..|||

In the simplest forms:

BACKUP DATABASE myDb TO DISK = 'X:\myDb.BAK'

RESTORE DATABASE myDb FROM DISK = 'X:\myDb.BAK'

There's a little bit more to it though, so please refer to BOL (Books On Line) for additional info on backup and restore, so you can decide upon the exact syntax to use.

/Kenneth

|||

Thanks Mr.Kenneth.Plz suggest some online books or give link regarding backup and restore...Plz

Finally thanks a lot Mr.Kenneth.

|||thanks Manivannan Suggest some online books or links|||when they mention BOL( Books Online), they are referring to Microsoft's Online Documentation for SQL Server 2005.

the homepage for it is here

the download for the english(may 2007) version is here

Query Timout

Hi All,
I am fairly new to SQLServer, although I have used other RDBMS software
extensively.
Is there a simple way to restrict the amount of time a query runs for?
I have inherited a system that allows users to run very large queries and
it is killing performance. I want to time the query out after a given
number of second.
TIA
PeterPeter,
serverside you could look at setting the query governor cost limit using
sp_configure (see BOL). It will disallow 'costly' queries from starting so
isn't exactly what you are referring to but you might still find it useful.
On the client side, the sqlcommand object (or its equivalent in your
programming language) should have a query timeout property.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in news:ODiQnZ$fGHA.5104
@.TK2MSFTNGP04.phx.gbl:
>
Thanks Paul,
I'll use this as a work around. We do not have the source for the
application unfortunately.
Peter

Query Timout

Hi All,
I am fairly new to SQLServer, although I have used other RDBMS software
extensively.
Is there a simple way to restrict the amount of time a query runs for?
I have inherited a system that allows users to run very large queries and
it is killing performance. I want to time the query out after a given
number of second.
TIA
PeterPeter,
serverside you could look at setting the query governor cost limit using
sp_configure (see BOL). It will disallow 'costly' queries from starting so
isn't exactly what you are referring to but you might still find it useful.
On the client side, the sqlcommand object (or its equivalent in your
programming language) should have a query timeout property.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in news:ODiQnZ$fGHA.5104
@.TK2MSFTNGP04.phx.gbl:

>
Thanks Paul,
I'll use this as a work around. We do not have the source for the
application unfortunately.
Peter

Query Timeout Issue with .NET

Hello all and thank you for your time.
I have an application with reports taking over 30 seconds to run. This
is producing the
"Timeout expired. The timeout period elapsed prior to completion of the
operation or the server is not responding." error. Is there a way to
increase the timeout expired default of 30 seconds until I have
resources to optimize or rewrite the querys? There are over 120
reports and querys!
I've tried:
<httpRuntime executionTimeout=3D"1800"/>
<add key=3D"XYZ"
value=3D" ;Server=3Dserver01;UID=3Dsa;PASSWORD=3Dp
assword;DATABASE=3Ddatabas=
e;Connecti=ADon
Timeout=3D 40000;"/>=20
I'd really appreciate any help.=20
SunshineI don't believe there is a connection string keyword that controls command
timeouts. You need to set the command CommandTimeout property to the
desired value.
Hope this helps.
Dan Guzman
SQL Server MVP
<sunshinevaldes@.yahoo.com> wrote in message
news:1145276070.473701.247630@.i40g2000cwc.googlegroups.com...
Hello all and thank you for your time.
I have an application with reports taking over 30 seconds to run. This
is producing the
"Timeout expired. The timeout period elapsed prior to completion of the
operation or the server is not responding." error. Is there a way to
increase the timeout expired default of 30 seconds until I have
resources to optimize or rewrite the querys? There are over 120
reports and querys!
I've tried:
<httpRuntime executionTimeout="1800"/>
<add key="XYZ"
value=" ;Server=server01;UID=sa;PASSWORD=passwor
d;DATABASE=database;Connecti_
on
Timeout= 40000;"/>
I'd really appreciate any help.
Sunshine|||Wouldn't I have to do the CommandTimeout for every call to the server?
If so that would be over a hundred so I was hoping there was something
in the web.config I could tweak. I did set the query timeout on the
server to 0 but that didn't help either.
Sunshine|||the connection timeout in your connection string indicates the timeout
for the connection to be formed, not for the actual query to complete.
This means that if for some reason it was having trouble connecting to
the database server, it would wait that long before reporting an error.
As Dan said you will need to set your CommandTimeout property in your
SQL command (I hope you've centralised your data access code or you're
in for a lot of code changes). But you should also make sure you remove
that absurd level of timeout from your connection string.
Cheers
Will|||sunshinevaldes@.yahoo.com wrote:
> Hello all and thank you for your time.
> I have an application with reports taking over 30 seconds to run.
> This
> is producing the
> "Timeout expired. The timeout period elapsed prior to completion of
> the
> operation or the server is not responding." error. Is there a way to
> increase the timeout expired default of 30 seconds until I have
> resources to optimize or rewrite the querys? There are over 120
> reports and querys!
>
> I've tried:
> <httpRuntime executionTimeout="1800"/>
> <add key="XYZ"
> value=" ;Server=server01;UID=sa;PASSWORD=passwor
d;DATABASE=database;Connect
i_on
> Timeout= 40000;"/>
>
> I'd really appreciate any help.
>
This is slightly off-topic here:
Come to this group if you want help in reducing the time it takes for your
queries to run (query optimization).
For help with client applications utilizing your queries, go to the
newsgroup that is focussed on your client application: in this case,
microsoft.public.dotnet.framework.aspnet.
My recommendation is to optimize your queries so that they don't take so
long to run. My suggestion for the interim would be to use IIS Manager to
increase the Script Timeout setting for your application. You can increase
the setting on a per-page basis by using the Server.ScriptTimeout property.
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||you would indeed have to set it for every call to the server. Hence my
comment about centralising data access code.
It is not possible to set this in the web.config.|||Unfortunately, the data access code is not centralized. This
application was written by consultants who say they can't fix the timed
out issue. I was hoping there was an easy fix until I can fix their
queryies.
Thanks for all your responses.
Sunshine|||Bob,
I went to microsoft.public.dotnet.framework.aspnet first but after
almost of w with no replies I came here and have had great replies
within the same day.
Can you tell me how the increase the Script Timeout in the IIS Manager?
Sunshine|||(sunshinevaldes@.yahoo.com) writes:
> Unfortunately, the data access code is not centralized. This
> application was written by consultants who say they can't fix the timed
> out issue.
Hm, if they don't enough about data-access programming to address
timeout issues properly, who says that you should be able to fix
their invoices? :-)

> I was hoping there was an easy fix until I can fix their queryies.
On SQL Server level there isn't, because this completely idiotic
default timeout of 30 seconds is set in the client API, and all SQL Server
sees is the cancellation request.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Query Timeout

Hello,
Our system has experienced time out issues with on of the SQL Severs. We
have an ASP.NET application that calls a stored proc, which returns a result
set. This stored proc all of a sudden started to time out on Friday after
noon. I was able to execute the same procedure in Query Analyzer without any
problems. The timeout for My ASP.NET is 30 seconds and the query was
executing the Query Analyzer in less than 30 seconds.
The solution I found out for this is to update statistics on one of the
databases. The stored proc has a join with a table in different database
sitting on the same server. When I updated the statistics of this database
the ASP.NET app started behave normally. This time I did not run the update
stats, instead I was thinking to do more research this morning. To my
surprise, the time out issue went away and ASP.NET is working absolutely
fine. What I found is that the SQL server might have been rebooted this
weekend.
Now I am scratching my head and trying to figure out what could be the
problem. What is it'
I appreciate any comments.
ThanksIf you have SQL Server configured to auto update stats, it may have done an
update itself which improved the stats and allowed it to create a good query
plan again.
My experience has been that auto update is not reliable enough, so we
schedule update statistics ... with fullscan against all tables across the
course of a week.
--
Scott Nichol
<Srini> wrote in message news:eRWM0U7pDHA.1488@.TK2MSFTNGP12.phx.gbl...
> Hello,
>
> Our system has experienced time out issues with on of the SQL Severs. We
> have an ASP.NET application that calls a stored proc, which returns a
result
> set. This stored proc all of a sudden started to time out on Friday after
> noon. I was able to execute the same procedure in Query Analyzer without
any
> problems. The timeout for My ASP.NET is 30 seconds and the query was
> executing the Query Analyzer in less than 30 seconds.
>
> The solution I found out for this is to update statistics on one of the
> databases. The stored proc has a join with a table in different database
> sitting on the same server. When I updated the statistics of this database
> the ASP.NET app started behave normally. This time I did not run the
update
> stats, instead I was thinking to do more research this morning. To my
> surprise, the time out issue went away and ASP.NET is working absolutely
> fine. What I found is that the SQL server might have been rebooted this
> weekend.
>
> Now I am scratching my head and trying to figure out what could be the
> problem. What is it'
> I appreciate any comments.
>
> Thanks
>

Query Timeout

I have a query I run in MSDE that will give me a timeout error message. It
reads:
"Timeout expired. The time out period elapsed prior to completion of the
operation or the server is not responding."
I know the server is responding, because the query seems to run anyway,
though I'm not sure the results are accurate. Is there any way to extend the
timeout period so this does not happen. The query running is adding
numerical records to one table based on criteria in the query and values in
another table.
What application are you using to query the database? If it's something your
wrote in-house and you are using ADO then set the CommandTimeout property of
the connection to 0 (zero). Some ADO libraries default to 30 seconds for a
timeout.
Jim
"Richard" <Richard@.discussions.microsoft.com> wrote in message
news:B8EFC32E-54F0-4CBB-A2F9-36699FA895B6@.microsoft.com...
> I have a query I run in MSDE that will give me a timeout error message.
It
> reads:
> "Timeout expired. The time out period elapsed prior to completion of the
> operation or the server is not responding."
> I know the server is responding, because the query seems to run anyway,
> though I'm not sure the results are accurate. Is there any way to extend
the
> timeout period so this does not happen. The query running is adding
> numerical records to one table based on criteria in the query and values
in
> another table.

Query Time Outs

Hi All.

I have some rather large SQL Server 2000 databases (around 60GB).

I have set up jobs to re-index the tables and update statistics every
sunday. This worked will for a few months. Now after a day or two of
using it the connections to it keep timing out. If i start the jobs
manually, all is well for two days or so.

Surely there can be a better solution to this ?

TIA.
Ryan,.Are the timeouts happening on the tables or on the queries run against
the tables ? The tables shouldn't time out. The queries could well
time out. Try running your queries with the execution plan showing. It
may give you some indication of what is causing the delays.

Also, do you get the same results on each PC and the server ? Try
running the query on the server if possible to see if it is server
based or networking. Networking issues may be causing a bottleneck
which then slows you down. Worth looking at if possible.

The trick will be narrowing this down to where the problem actually
lies, not just where it shows up.

budgie@.doormat.za.org (Ryan Budge) wrote in message news:<8b601867.0311200322.281b643d@.posting.google.com>...
> Hi All.
> I have some rather large SQL Server 2000 databases (around 60GB).
> I have set up jobs to re-index the tables and update statistics every
> sunday. This worked will for a few months. Now after a day or two of
> using it the connections to it keep timing out. If i start the jobs
> manually, all is well for two days or so.
> Surely there can be a better solution to this ?
> TIA.
> Ryan,.|||Hi.

ryanofford@.hotmail.com (Ryan) wrote in message news:<7802b79d.0311200631.7f7fdf6a@.posting.google.com>...
> Are the timeouts happening on the tables or on the queries run against
> the tables ? The tables shouldn't time out. The queries could well
> time out. Try running your queries with the execution plan showing. It
> may give you some indication of what is causing the delays.

OK. Will do.

> Also, do you get the same results on each PC and the server ? Try
> running the query on the server if possible to see if it is server
> based or networking. Networking issues may be causing a bottleneck
> which then slows you down. Worth looking at if possible.

I have tested the application on the server and on client PCs. It
does not seem to make a difference. Timeouts and slow performance is
on both...

> The trick will be narrowing this down to where the problem actually
> lies, not just where it shows up.

Yep... Im sure. :-).

I remeber googling around or reading some of the books online and
reading about the sample percentage when updating statics and indexes.
Is it possible to re-index a table with a greater percentage to
enable the job to only run once a week ?

What kinda percentage is safe to use ?

Thanks for the suggestions.
Ryan.