sql rowcount: records

Showing posts with label records. Show all posts

Tuesday, March 20, 2012

[Table Partitioning] What is the best method?

Hello,

I have a Sql Server 2005 database with many tables, each with millions of records within them.

They all have a Receive Date field, with records going back 10 years or so.

What would be the best way to partition it? I was thinking of partitioning them by years, but that would give me 10+ partitions -- would that be alot of overhead?

~Lewhy do you want to partition them in the first place? Just because they have millions of rows?

Are these tables transactional, or do you only read from them? If you are only reading, then you can add appropriate indexes without worrying about hurting insert/update/delete perf (because you don't do any inserts/updates/deletes) and don't partition them at all.

In my job I work with tables in sql server that have 100's of millions of rows without problems (not transactional though) - the important thing is to make sure the indexes are correct. also you need decent disks of course. :)|||Hello,

I have a Sql Server 2005 database with many tables, each with millions of records within them.

They all have a Receive Date field, with records going back 10 years or so.

What would be the best way to partition it? I was thinking of partitioning them by years, but that would give me 10+ partitions -- would that be alot of overhead?

~Le

I have only tested functions/ schemes in 2005, but from my testing and reading I havent seen anything that would indicate 10 would be too many. Test it out. You need to realize that this whole concept/ feature is new to 2005, so you are blazing the path for the rest of us to follow. ;)

Sunday, March 11, 2012

[Shared Memory]ConnectionCheckForData error on delete command.

Hello,
I ave a strange error with SQLServer 2000 SP3.
I made a table that contains over 62'000 records. When I delete over
300 records, I have this error:
[Microsoft][ODBC SQL Server Driver][Shared
Memory]ConnectionCheckForData (CheckforData()).
Serveur : Msg 11, Niveau 16, État 1, Ligne 0
Structure of my table:
create table ActiveTimeForProcessing (
ACTIVETIMEFORPROCESSING_PK int identity,
InstanciedClassName int not null,
ActiveTime int not null,
DateFrom datetime not null,
DateTo datetime not null,
constraint PK_ACTIVETIMEFORPROCESSING primary key
(ACTIVETIMEFORPROCESSING_PK)
)
go
alter table ActiveTimeForProcessing
add constraint ActiveTimeForProcessingHasActiveTime foreign key
(ActiveTime)
references ActiveTime (ACTIVETIME_PK)
go
CREATE INDEX [IX_ActiveTimeForProcessing_DateFrom] ON
[dbo].[ActiveTimeForProcessing]([DateFrom]) ON [PRIMARY]
GO
CREATE INDEX [IX_ActiveTimeForProcessing_DateTo] ON
[dbo].[ActiveTimeForProcessing]([DateTo]) ON [PRIMARY]
GO
Something very strange is that on an other server, it work fine...
Thanks to help me.
PascalHi,
Try
1. Run DBCC CHECKDB against the database to make sure it is clean.
2. Check the errorlog for any error that match the time that you get the
error.
Sincerely,
Yih-Yoon Lee
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.|||Thanks a lot, but I tryed you purpose...
Finally I found sollution... Service Pack 3a......... ... ...
Thanks one more.
Pascal
""Jinxin"" <yihyoonl@.online.microsoft.com> a écrit dans le message de news:
ZRYO4j$PDHA.2696@.cpmsftngxa09.phx.gbl...
> Hi,
> Try
> 1. Run DBCC CHECKDB against the database to make sure it is clean.
> 2. Check the errorlog for any error that match the time that you get the
> error.
> Sincerely,
> Yih-Yoon Lee
> Microsoft, SQL Server
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
>

Getting only multiple records for all customers

Hi,

How can I get desired data results from Sample Data given using SQL statement? Preferably without using Cursors and simple as possible.

Sample Data:

OrderID CustomerID ProductID
2 2 2
3 3 null
4 3 2
5 2 2
6 4 3
7 5 4
8 2 10
11 3 5
13 5 8
14 6 9

Desired Results:

OrderID CustomerID ProductID
2 2 2
4 3 2
5 2 2
7 5 4
8 2 10
11 3 5
13 5 8

It is not easy to 'guess' what criteria you have used.

Obviously, eliminating OrderID 3 is because nothing was ordered.

But why have OrderIDs 6 and 14 been left out?

|||

The following query will work for You..(multiple records for all customers - customers who have multiple records)

Select * from Orders Where
CustomerId in (Select CustomerId from Orders Group By CustomerId Having Count(ProductID) > 1)
and ProductId is NOT NULL

|||

Thanks for reply, Arnie.

Thanks for the answer, ManiD.

Re: Accessing the underlying sql query from the rdl file

Our client uses the report builder to generate reports for collection of employees. We would like to use the employee records in this report to perform some additional processing (such as the list of employees gets assigned to a particular group).

Programmatically, I can retrieve a byte stream from rs.GetReportDefinition( "\myClientEmpReport") and deserialize the stream into Report object (as define by ReportDefinition.xsd).

I can then manually drill down and retrieve the SematicQuery xml from the commandText field.

The problem is how to convert the SemanticQuery format into a T-Sql query that I can run against the view that the report model is based off of.

Is this possible?

Thanks,

Arjay

P.S. Running SQL 2005 Reporting Services, VC# 2005, ASP.Net 2.0.

After attempting to autogenerate serialization classes on the SemanticQuery xml with Xsd and XsdObjectGen, I ended up hand coding some classes that handled recursion. From there, I was able to regenerate the sql query by walking through the filter and grouping sections. While this isn't a generic solution, it works well for my needs because the model I need this for is a single datasource derived from a view.

As a feature request, it sure would be nice to be able get the query string from the reporting engine directly.

Multiple Records Subquery..

Hi everybody,

I like to display the records for AccountNo = 221 from table records shown below, how would I do this? I am display this results in a crystal report. What is sql statement to do this? Thanks.

Sql Statement: (this statement is not allowed)
Select AccountNo, RecordID, (Select Description From Table1 Where RecordID In (Select RecordID From Table2 Where Date < '04/05/2006')) As Description, Amount From Table2 Where Date < '04/05/2006'

Desired Result:

AccountNo RecordID Description Amount
221 20 Whew 290.00
221 21 Hi There Good Morning 728.00

Table 1

RecordID Description
20 Whew
21 Hi
21 There
21 Good Morning

Table 2
Date AccountNo RecordID Amount
04/02/2006 220 19 80.0
04/03/2006 221 20 290.0
04/04/2006 221 21 728.0
04/06/2006 223 23 200.3
04/07/2006 225 25 2893.20

den2005

select t2.AccountNo, t1.RecordId, t2.Description, t1.Amount

from Table1 t1 inner join Table2 t2 on t1.RecordId = t2.RecordId

where t2.AccountNo = 221 and t2.Date < '4/5/2006'

|||

Hi,

Thanks for replying, I f I do that teh results would be,

AccountNo RecordID Description Amount
221 20 Whew 290.00
221 21 Hi 728.00

221 21 There 728.00

221 21 Good Morning 728.00

But I want to display shown below,

AccountNo RecordID Description Amount
221 20 Whew 290.00
221 21 Hi There Good Morning 728.00

Besides that is my first statement using inner join...

den2005

|||How do you determine that it is "Hi There Good Morning" and not "Good Morning Hi There" and not "There Hi Good Morning" etc.

Is there another column that will determine the required order ?

Do take not that, records are stored in database in no particular order. When returning the records, you have to determine the sequence by using ORDER BY.

Getting Unique entire row records based on one unique column..

Hi,

I like to get all records(all columns) for any employee who has more than 2 unique SuggestedTraining..As simple as possible and execution (performance optimized) speed is as fast as possible.

Data:

Table structure below

Assessment Table

AssessmentID EmployeeID CourseID SuggestedTraining Date ....more columns
1 4 1 'Training 1' <date>
2 2 2 'Training 2' <date>
3 1 4 'Training 1' <date>
4 2 1 'Training 2' <date>
5 4 2 'Training 1' <date>
6 3 3 '' <date>
7 2 5 'Training 3' <date>
8 5 7 'Training 2' <date>
9 7 4 'Training 2' <date>
10 5 3 'Training 1' <date>
11 5 6 'Training 3' <date>

Desired Result:

AssessmentID EmployeeID CourseID SuggestedTraining Date
4 2 1 'Training 2' <date>
7 2 5 'Training 3' <date>
8 5 7 'Training 2' <date>
10 5 3 'Training 1' <date>
11 5 6 'Training 3' <date>

Try the example below.

Chris

Code Snippet

DECLARE @.AssessmentTable TABLE

(

AssessmentID INT,

EmployeeID INT,

CourseID INT,

SuggestedTraining VARCHAR(20)

)

INSERT INTO @.AssessmentTable(AssessmentID, EmployeeID, CourseID, SuggestedTraining)

SELECT 1, 4, 1, 'Training 1' UNION ALL

SELECT 2, 2, 2, 'Training 2' UNION ALL

SELECT 3, 1, 4, 'Training 1' UNION ALL

SELECT 4, 2, 1, 'Training 2' UNION ALL

SELECT 5, 4, 2, 'Training 1' UNION ALL

SELECT 6, 3, 3, '' UNION ALL

SELECT 7, 2, 5, 'Training 3' UNION ALL

SELECT 8, 5, 7, 'Training 2' UNION ALL

SELECT 9, 7, 4, 'Training 2' UNION ALL

SELECT 10, 5, 3, 'Training 1' UNION ALL

SELECT 11, 5, 6, 'Training 3'

SELECT at.AssessmentID,

at.EmployeeID,

at.CourseID,

at.SuggestedTraining

FROM @.AssessmentTable at

INNER JOIN (SELECT atg.EmployeeID

FROM @.AssessmentTable atg

GROUP BY atg.EmployeeID

HAVING COUNT(DISTINCT atg.SuggestedTraining) >= 2) t

ON t.EmployeeID = at.EmployeeID

|||

Hi Chris,

I cannot do the insertion because simply I do not know the data at runtime,

Code Snippet

INSERT INTO @.AssessmentTable(AssessmentID, EmployeeID, CourseID, SuggestedTraining)

SELECT 1, 4, 1, 'Training 1' UNION ALL

SELECT 2, 2, 2, 'Training 2' UNION ALL

SELECT 3, 1, 4, 'Training 1' UNION ALL

SELECT 4, 2, 1, 'Training 2' UNION ALL

SELECT 5, 4, 2, 'Training 1' UNION ALL

SELECT 6, 3, 3, '' UNION ALL

SELECT 7, 2, 5, 'Training 3' UNION ALL

SELECT 8, 5, 7, 'Training 2' UNION ALL

SELECT 9, 7, 4, 'Training 2' UNION ALL

SELECT 10, 5, 3, 'Training 1' UNION ALL

SELECT 11, 5, 6, 'Training 3'

The desired result is to get records (all columns) of table based on Employees having more than 2 Suggested Training, empty Suggested Training and Duplicated Training are excluded...

|||

It is just sample data Chris offered to demonstrate his suggested solution.

Run all of the code as Chris posted to verify if the solution works for you.

According to your specifications, the Date is immaterial for the solution. If that is NOT correct, then refine your desired outcome so we can refine the suggested solutions.

|||

Hi,

My question how do I replace this part of script to be adaptable to actual data.

Code Snippet

SELECT 1, 4, 1, 'Training 1' UNION ALL

SELECT 2, 2, 2, 'Training 2' UNION ALL

SELECT 3, 1, 4, 'Training 1' UNION ALL

SELECT 4, 2, 1, 'Training 2' UNION ALL

SELECT 5, 4, 2, 'Training 1' UNION ALL

SELECT 6, 3, 3, '' UNION ALL

SELECT 7, 2, 5, 'Training 3' UNION ALL

SELECT 8, 5, 7, 'Training 2' UNION ALL

SELECT 9, 7, 4, 'Training 2' UNION ALL

SELECT 10, 5, 3, 'Training 1' UNION ALL

SELECT 11, 5, 6, 'Training 3'

Try to replace entire code it with this,

Code Snippet

DECLARE @.AssessmentTable TABLE

(

AssessmentID INT,

EmployeeID INT,

CatalogueCourseID INT,

SuggestedTraining nVARCHAR(500),

CompetencyModelID int

)

Insert Into @.AssessmentTable(AssessmentID, EmployeeID, CatalogueCourseID, SuggestedTraining, CompetencyModelID)

Select AssessmentID, EmployeeID, CatalogueCourseID, SuggestedTraining, CompetencyModelID From Assessment

Where IsActive =1 AND SuggestedTraining <> ''

SELECT at.AssessmentID,at.EmployeeID,at.CatalogueCourseID,at.SuggestedTraining FROM @.AssessmentTable at

INNER JOIN (SELECT atg.EmployeeID FROM @.AssessmentTable atg GROUP BY atg.EmployeeID HAVING COUNT(DISTINCT atg.SuggestedTraining) >= 2) t

ON t.EmployeeID = at.EmployeeID

Actual Result:

AssessmentID EmployeeID CatalogueCourseID SuggestedTraining
9 9 8 Training Course 2
30 9 9 Training Course 3
42 9 9 Training Course 3 << Duplicate
43 9 7 Training Course 1
47 9 7 Training Course 1 << Duplicate
54 9 5 Training
48 12 7 Training Course 1
40 12 7 Training Course 1 << Duplicate
46 12 9 Training Course 3
..... More data below....

Still Desired Result is not achieved...

|||

As Arnie pointed out in a response to your previous post, you only need to use the following:

Code Snippet

SELECT at.AssessmentID,

at.EmployeeID,

at.CourseID,

at.SuggestedTraining

FROM @.AssessmentTable at

INNER JOIN (SELECT atg.EmployeeID

FROM @.AssessmentTable atg

GROUP BY atg.EmployeeID

HAVING COUNT(DISTINCT atg.SuggestedTraining) >= 2) t

ON t.EmployeeID = at.EmployeeID

The CREATE TABLE / INSERT statements were included in my original post solely to show that the concept works. All you need to do is take the above sample and replace '@.AssessmentTable' with your actual table name.

Chris

|||

Hi Chris,

I tried this code slightly modified it to exclude empty SuggestedTraining, and still there are duplicate Suggested Training in the results. How can I remove duplicated SuggestedTraining in the result set. Thanks.

Code Snippet

SELECT at.AssessmentID, at.EmployeeID, at.CatalogueCourseID, at.SuggestedTraining FROM Assessment at

INNER JOIN (SELECT atg.EmployeeID FROM Assessment atg Where SuggestedTraining <> ''

GROUP BY atg.EmployeeID HAVING COUNT(DISTINCT atg.SuggestedTraining) >= 2) t

ON t.EmployeeID = at.EmployeeID

|||

For the duplicates, which AssessmentID would you want to return - the maximum or the minimum? [therein lies a clue to the solution... ]

Chris

|||

Well,

Maximum is the choice, right now date is not considered, how would the sql statement would be if both maximum and date (latest) is considered?

|||

Try the code below.

Chris

Code Snippet

SELECT MAX(at.AssessmentID),

at.EmployeeID,

at.CourseID,

at.SuggestedTraining

--, MAX(at.DateColumn)

FROM @.AssessmentTable at

INNER JOIN (

SELECT atg.EmployeeID

FROM @.AssessmentTable atg

GROUP BY atg.EmployeeID

HAVING COUNT(DISTINCT atg.SuggestedTraining) >= 2

) t ON t.EmployeeID = at.EmployeeID

GROUP BY at.EmployeeID,

at.CourseID,

at.SuggestedTraining

|||

I run the sql script you provided and here is the result:

AssessmentID EmployeeID CourseID SuggestedTraining

26 4 8 Training Course 1
8 4 8 Training Course 2
58 8 NULL <<-- Should Not be included
6 8 16 Training Course 1 <<-- Should Not be included
57 9 NULL <<-- Should Not be included
54 9 5 Training
47 9 7 Training Course 1
9 9 8 Training Course 2
42 9 9 Training Course 3
55 12 NULL <<-- Should Not be included
1 12 3 Training Course 2
48 12 7 Training Course 1
46 12 9 Training Course 3
22 13 NULL <<-- Should Not be included
13 13 NULL TEST TRAIN
41 13 7 Training Course 1
31 13 8 Training Course 2
45 13 9 Training Course 3
16 26 NULL <<-- Should Not be included
18 26 7 Training Course 1 <<-- Should Not be included
53 82 7 Training Course 1
38 82 16 Test Training Course
28 150 7 Training Course 1
27 150 9 Training Course 3
33 179 3 Training Course 2
37 179 16 Test Training Course
36 180 NULL <<-- Should Not be included
35 180 8 Training Course 2 <<-- Should Not be included

I filter the empty Suggested Training using this modified sql script:

Code Snippet

SELECT MAX(at.AssessmentID), at.EmployeeID, at.CatalogueCourseID, at.SuggestedTraining FROM Assessment at

INNER JOIN (SELECT atg.EmployeeID FROM Assessment atg GROUP BY atg.EmployeeID HAVING COUNT

(DISTINCT atg.SuggestedTraining) >= 2) t ON t.EmployeeID = at.EmployeeID Where at.SuggestedTraining <> ''

GROUP BY at.EmployeeID, at.CatalogueCourseID,at.SuggestedTraining

And still did not get desired result...

AssessmentID EmployeeID CourseID SuggestedTraining

26 4 8 Training Course 1
8 4 8 Training Course 2
6 8 16 Training Course 1 <<-- Should Not be included
54 9 5 Training
47 9 7 Training Course 1
9 9 8 Training Course 2
42 9 9 Training Course 3
1 12 3 Training Course 2
48 12 7 Training Course 1
46 12 9 Training Course 3
13 13 NULL TEST TRAIN
41 13 7 Training Course 1
31 13 8 Training Course 2
45 13 9 Training Course 3
18 26 7 Training Course 1 <<-- Should Not be included
53 82 7 Training Course 1
38 82 16 Test Training Course
28 150 7 Training Course 1
27 150 9 Training Course 3
33 179 3 Training Course 2
37 179 16 Test Training Course
35 180 8 Training Course 2 <<-- Should Not be included

Here is Whole Data for Assessment table:

AssessmentID EmployeeID CourseID SuggestedTraining

1 12 3 Training Course 2
3 59 7 Training Course 1
6 8 16 Training Course 1
7 10 3
8 4 8 Training Course 2
9 9 8 Training Course 2
10 132 7 Training Course 1
11 25 NULL Training Course 1
12 12 7 Training Course 1
13 13 NULL TEST TRAIN
14 54 7 Training Course 1
15 28 NULL
16 26 NULL
17 29 NULL
18 26 7 Training Course 1
19 6 NULL
20 84 7 Training Course 1
21 139 7 Training Course 1
22 13 NULL
23 82 7 Training Course 1
24 127 7 Training Course 1
25 64 7 Training Course 1
26 4 8 Training Course 1
27 150 9 Training Course 3
28 150 7 Training Course 1
29 12 7 Training Course 1
30 9 9 Training Course 3
31 13 8 Training Course 2
32 177 NULL
33 179 3 Training Course 2
34 180 8 Training Course 2
35 180 8 Training Course 2
36 180 NULL
37 179 16 Test Training Course
38 82 16 Test Training Course
39 9 7 Training Course 1
40 12 7 Training Course 1
41 13 7 Training Course 1
42 9 9 Training Course 3
43 9 7 Training Course 1
44 13 9 Training Course 3
45 13 9 Training Course 3
46 12 9 Training Course 3
47 9 7 Training Course 1
48 12 7 Training Course 1
49 87 7 Training Course 1
50 9 NULL
51 72 7 Training Course 1
52 83 9 Training Course 1
53 82 7 Training Course 1
54 9 5 Training
55 12 NULL
56 7 NULL
57 9 NULL
58 8 NULL

|||

I've re-arranged the filters in the query to filter out the rows that you don't need - see below. This now returns the results as specified in your previous post.

Chris

Code Snippet

DECLARE @.AssessmentTable TABLE

(

AssessmentID INT,

EmployeeID INT,

CourseID INT,

SuggestedTraining VARCHAR(20)

)

INSERT INTO @.AssessmentTable (AssessmentID, EmployeeID, CourseID, SuggestedTraining)

SELECT 1, 12, 3, 'Training Course 2' UNION ALL

SELECT 3, 59, 7, 'Training Course 1' UNION ALL

SELECT 6, 8, 16, 'Training Course 1' UNION ALL

SELECT 7, 10, 3, '' UNION ALL

SELECT 8, 4, 8, 'Training Course 2' UNION ALL

SELECT 9, 9, 8, 'Training Course 2' UNION ALL

SELECT 10, 132, 7, 'Training Course 1' UNION ALL

SELECT 11, 25, NULL, 'Training Course 1' UNION ALL

SELECT 12, 12, 7, 'Training Course 1' UNION ALL

SELECT 13, 13, NULL, 'TEST TRAIN' UNION ALL

SELECT 14, 54, 7, 'Training Course 1' UNION ALL

SELECT 15, 28, NULL, '' UNION ALL

SELECT 16, 26, NULL, '' UNION ALL

SELECT 17, 29, NULL, '' UNION ALL

SELECT 18, 26, 7, 'Training Course 1' UNION ALL

SELECT 19, 6, NULL, '' UNION ALL

SELECT 20, 84, 7, 'Training Course 1' UNION ALL

SELECT 21, 139, 7, 'Training Course 1' UNION ALL

SELECT 22, 13, NULL, '' UNION ALL

SELECT 23, 82, 7, 'Training Course 1' UNION ALL

SELECT 24, 127, 7, 'Training Course 1' UNION ALL

SELECT 25, 64, 7, 'Training Course 1' UNION ALL

SELECT 26, 4, 8, 'Training Course 1' UNION ALL

SELECT 27, 150, 9, 'Training Course 3' UNION ALL

SELECT 28, 150, 7, 'Training Course 1' UNION ALL

SELECT 29, 12, 7, 'Training Course 1' UNION ALL

SELECT 30, 9, 9, 'Training Course 3' UNION ALL

SELECT 31, 13, 8, 'Training Course 2' UNION ALL

SELECT 32, 177, NULL, '' UNION ALL

SELECT 33, 179, 3, 'Training Course 2' UNION ALL

SELECT 34, 180, 8, 'Training Course 2' UNION ALL

SELECT 35, 180, 8, 'Training Course 2' UNION ALL

SELECT 36, 180, NULL, '' UNION ALL

SELECT 37, 179, 16, 'Test Training Course' UNION ALL

SELECT 38, 82, 16, 'Test Training Course' UNION ALL

SELECT 39, 9, 7, 'Training Course 1' UNION ALL

SELECT 40, 12, 7, 'Training Course 1' UNION ALL

SELECT 41, 13, 7, 'Training Course 1' UNION ALL

SELECT 42, 9, 9, 'Training Course 3' UNION ALL

SELECT 43, 9, 7, 'Training Course 1' UNION ALL

SELECT 44, 13, 9, 'Training Course 3' UNION ALL

SELECT 45, 13, 9, 'Training Course 3' UNION ALL

SELECT 46, 12, 9, 'Training Course 3' UNION ALL

SELECT 47, 9, 7, 'Training Course 1' UNION ALL

SELECT 48, 12, 7, 'Training Course 1' UNION ALL

SELECT 49, 87, 7, 'Training Course 1' UNION ALL

SELECT 50, 9, NULL, '' UNION ALL

SELECT 51, 72, 7, 'Training Course 1' UNION ALL

SELECT 52, 83, 9, 'Training Course 1' UNION ALL

SELECT 53, 82, 7, 'Training Course 1' UNION ALL

SELECT 54, 9, 5, 'Training' UNION ALL

SELECT 55, 12, NULL, '' UNION ALL

SELECT 56, 7, NULL, '' UNION ALL

SELECT 57, 9, NULL, '' UNION ALL

SELECT 58, 8, NULL, ''

SELECT MAX(at.AssessmentID) AS AssessmentID,

at.EmployeeID,

at.CourseID,

at.SuggestedTraining

--, MAX(at.DateColumn)

FROM @.AssessmentTable at

INNER JOIN (

SELECT atg.EmployeeID

FROM @.AssessmentTable atg

WHERE atg.SuggestedTraining <> ''

GROUP BY atg.EmployeeID

HAVING COUNT(DISTINCT atg.SuggestedTraining) >= 2

) t ON t.EmployeeID = at.EmployeeID

WHERE at.SuggestedTraining <> ''

GROUP BY at.EmployeeID,

at.CourseID,

at.SuggestedTraining

ORDER BY 1

|||Thanks Chris, right now, based on data I have, this problem is resolved, when another problem arises...I post it here...I have marked your last reply as answer...

Tuesday, March 6, 2012

[Microsoft][ODBC SQL Server Driver]Timeout expired

Hi all !!
I get the error: "[Microsoft][ODBC SQL Server Driver]Timeout expired
"
when I run a delete statement for a table to delete records which run
into 100s. The SQL is 2000 enterprise edition SP4 on a win 2000 server.
Can the timeout be changed so that the command can go through'
Thanks in advance,
VishalTimeout in this case is a client setting. How you set it depends on what
client you are using. If you are using Query Analyzer, you can go
Tools->Options->Connections, and modify the query time-out there.
Linchi
"bajaj.vishal@.gmail.com" wrote:

> Hi all !!
> I get the error: "[Microsoft][ODBC SQL Server Driver]Timeout expir
ed"
> when I run a delete statement for a table to delete records which run
> into 100s. The SQL is 2000 enterprise edition SP4 on a win 2000 server.
>
> Can the timeout be changed so that the command can go through'
> Thanks in advance,
> Vishal
>

[Microsoft][ODBC SQL Server Driver]Timeout expired

Hi all !!
I get the error: "[Microsoft][ODBC SQL Server Driver]Timeout expired"
when I run a delete statement for a table to delete records which run
into 100s. The SQL is 2000 enterprise edition SP4 on a win 2000 server.
Can the timeout be changed so that the command can go through'
Thanks in advance,
VishalTimeout in this case is a client setting. How you set it depends on what
client you are using. If you are using Query Analyzer, you can go
Tools->Options->Connections, and modify the query time-out there.
Linchi
"bajaj.vishal@.gmail.com" wrote:
> Hi all !!
> I get the error: "[Microsoft][ODBC SQL Server Driver]Timeout expired"
> when I run a delete statement for a table to delete records which run
> into 100s. The SQL is 2000 enterprise edition SP4 on a win 2000 server.
>
> Can the timeout be changed so that the command can go through'
> Thanks in advance,
> Vishal
>

Saturday, February 25, 2012

[Microsoft][ODBC SQL Server Driver] Timeout expired

I am getting this error when I try and look at the Top 1000 records of a view
that I have created, in Enterprise Manager. If I copy the SQL for the View
into Query Analyzer, it returns results fine (in approx. 2 minutes). In the
Enterprise Manager Properties Advanced window, I have set Query timeout to
both 0 (never times out?) and 60000 (100 minutes?) to no avail. Any
suggestions would be greatly appreciated.What is the exact error number and message? There are several types of
timeouts. If you are not specifying the correct parameter, it will continue
to timeout on that type.
Sincerely,
Anthony Thomas
"Gary W. Hinkel" <GaryWHinkel@.discussions.microsoft.com> wrote in message
news:E43E35ED-809B-4DF4-8869-0EE3A49F9EEF@.microsoft.com...
I am getting this error when I try and look at the Top 1000 records of a
view
that I have created, in Enterprise Manager. If I copy the SQL for the View
into Query Analyzer, it returns results fine (in approx. 2 minutes). In the
Enterprise Manager Properties Advanced window, I have set Query timeout to
both 0 (never times out?) and 60000 (100 minutes?) to no avail. Any
suggestions would be greatly appreciated.|||Unfortunately, there is no error number, and the error message is the subject
line of this post. Also, I am not specifying any parameters at all, as this
is happening in Enterrpise Manager, Views, right-click on view, Open View,
Return Top.
"AnthonyThomas" wrote:
> What is the exact error number and message? There are several types of
> timeouts. If you are not specifying the correct parameter, it will continue
> to timeout on that type.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Gary W. Hinkel" <GaryWHinkel@.discussions.microsoft.com> wrote in message
> news:E43E35ED-809B-4DF4-8869-0EE3A49F9EEF@.microsoft.com...
> I am getting this error when I try and look at the Top 1000 records of a
> view
> that I have created, in Enterprise Manager. If I copy the SQL for the View
> into Query Analyzer, it returns results fine (in approx. 2 minutes). In the
> Enterprise Manager Properties Advanced window, I have set Query timeout to
> both 0 (never times out?) and 60000 (100 minutes?) to no avail. Any
> suggestions would be greatly appreciated.
>
>|||If you've changed the SQLEM, Tools, Options, Advanced Tab, Query Timeout
value to 0 and you are still getting the message, then you are probably being
blocked by current processes and are getting a LOCK TIMEOUT (which is really
a Lock Wait timeout). You do not have the ability to modify the Lock Timout
setting through EM; however, you can through Query Analyzer.
The Properties, Advanced Window does not exist for SQLEM. There is a
Properties, Connections, Remote Connections area for the currently selected
database, but that property is a timeout setting for RPC queries, not SQLEM
DMO.
Sincerely,
Anthony Thomas
"Gary W. Hinkel" wrote:
> Unfortunately, there is no error number, and the error message is the subject
> line of this post. Also, I am not specifying any parameters at all, as this
> is happening in Enterrpise Manager, Views, right-click on view, Open View,
> Return Top.
> "AnthonyThomas" wrote:
> > What is the exact error number and message? There are several types of
> > timeouts. If you are not specifying the correct parameter, it will continue
> > to timeout on that type.
> >
> > Sincerely,
> >
> >
> > Anthony Thomas
> >
> >
> > --
> >
> > "Gary W. Hinkel" <GaryWHinkel@.discussions.microsoft.com> wrote in message
> > news:E43E35ED-809B-4DF4-8869-0EE3A49F9EEF@.microsoft.com...
> > I am getting this error when I try and look at the Top 1000 records of a
> > view
> > that I have created, in Enterprise Manager. If I copy the SQL for the View
> > into Query Analyzer, it returns results fine (in approx. 2 minutes). In the
> > Enterprise Manager Properties Advanced window, I have set Query timeout to
> > both 0 (never times out?) and 60000 (100 minutes?) to no avail. Any
> > suggestions would be greatly appreciated.
> >
> >
> >|||Thanks for all your assisstance Anthony. This has cleared things up for me
as far as SQLEM is concerned (ie: I won't use it to display views, I'll use
QA instead).
I had initially thought that if I could resolve the timeout issue in SQLEM,
then the same issues in getting data into MapPoint, Excel, etc. through an
ODBC connection could be resolved as well.
Now at least, I can focus my efforts on getting the View data into
MapPoint/Excel etc.
Thanks again Anthony.
Sincerely,
Gary W. Hinkel
"AnthonyThomas" wrote:
> If you've changed the SQLEM, Tools, Options, Advanced Tab, Query Timeout
> value to 0 and you are still getting the message, then you are probably being
> blocked by current processes and are getting a LOCK TIMEOUT (which is really
> a Lock Wait timeout). You do not have the ability to modify the Lock Timout
> setting through EM; however, you can through Query Analyzer.
> The Properties, Advanced Window does not exist for SQLEM. There is a
> Properties, Connections, Remote Connections area for the currently selected
> database, but that property is a timeout setting for RPC queries, not SQLEM
> DMO.
> Sincerely,
>
> Anthony Thomas
>
> "Gary W. Hinkel" wrote:
> > Unfortunately, there is no error number, and the error message is the subject
> > line of this post. Also, I am not specifying any parameters at all, as this
> > is happening in Enterrpise Manager, Views, right-click on view, Open View,
> > Return Top.
> >
> > "AnthonyThomas" wrote:
> >
> > > What is the exact error number and message? There are several types of
> > > timeouts. If you are not specifying the correct parameter, it will continue
> > > to timeout on that type.
> > >
> > > Sincerely,
> > >
> > >
> > > Anthony Thomas
> > >
> > >
> > > --
> > >
> > > "Gary W. Hinkel" <GaryWHinkel@.discussions.microsoft.com> wrote in message
> > > news:E43E35ED-809B-4DF4-8869-0EE3A49F9EEF@.microsoft.com...
> > > I am getting this error when I try and look at the Top 1000 records of a
> > > view
> > > that I have created, in Enterprise Manager. If I copy the SQL for the View
> > > into Query Analyzer, it returns results fine (in approx. 2 minutes). In the
> > > Enterprise Manager Properties Advanced window, I have set Query timeout to
> > > both 0 (never times out?) and 60000 (100 minutes?) to no avail. Any
> > > suggestions would be greatly appreciated.
> > >
> > >
> > >

Friday, February 24, 2012

[How do I...?] append data without logging?

Hello. We are using SQL SErver 2000.
We are moving appending data from one table to another table. Many millions of records.
We don't have alot of extra log space. How would I do this without it writing to the log files?
`LeYou can't turn off loging completley. Or at least in 2k you couldn't years ago my boss insisted you could and I hounded MS for proof that there was no way to completley shut it off.

I don't know how you are getting this data in but perhaps a combination of set implicit transactions on, and commits after a specific number of rows would be of help. I do a lot of this in Oracle.

Monday, February 13, 2012

[*-)] About Auto Increment Field

Hi,

I have one Auto increment field in the table.

I have a problem that suppose I have 10 Records in the table, Now If someone delete record no 3 and 5 then it has 8 records.

But The field will give 11 no to new record I want it first fill 3 and 5 then give 11 to the new record.

Is it possible with the auto increment field in sql server 2005.

No it's not possible with autoincrement fields. You would have to create your own primary key field, and query the table to get the lowest available number everytime you performed an insert. I would never recommend doing this. It's a potential nightmare, and I can't think of one single good reason for doing it. All you need is a unique identifier for each row. It doesn't matter if it's not nice and pretty (in terms of a sequential series), so long as it works.

Tuesday, March 20, 2012

[Table Partitioning] What is the best method?

Sunday, March 11, 2012

[Shared Memory]ConnectionCheckForData error on delete command.

Getting only multiple records for all customers

Re: Accessing the underlying sql query from the rdl file

Multiple Records Subquery..

Getting Unique entire row records based on one unique column..

Tuesday, March 6, 2012

[Microsoft][ODBC SQL Server Driver]Timeout expired

[Microsoft][ODBC SQL Server Driver]Timeout expired

Saturday, February 25, 2012

[Microsoft][ODBC SQL Server Driver] Timeout expired

[Microsoft][ODBC SQL Server Driver] Timeout expired

[Microsoft][ODBC SQL Server Driver] Timeout expired

Friday, February 24, 2012

[How do I...?] append data without logging?

Monday, February 13, 2012

[*-)] About Auto Increment Field

sql rowcount

Blog Archive

About Me