advice on hard drive set up for 2005 sql server 64 bit

Hi
I built a server and I need some advice on reconfiguring the hard drives for
better sql server performance. Here are my current specs
2 Xeon 5160 Dual Core Hypertheaded Processors
8 gig of fd ram
Windows 2003 Server 64 bit
Sql Server 2005 64 bit
2 500 gig Sata II hard drives set up as Raid 1 at the hard ware level. They
are partitioned as 15 gig for the OS and the rest for my development
environment.
In my development testing I am working with databases starting at 30 gig and
can run upwards of 100 gig. After initial set up and the first round of
testing I quickly learned that I needed to move my tempDB onto the larger
partition. I also learned that I should move it on to a fast disk that isn't
part of the raid because it isn't necessary to backup. Additionally I read
that there is no need to put the system swap file on the raid and for better
performance it should be on a separate faster disk.
This is how I am thinking of reconfiguring the system
2 36 gig 10,000 rpm SATA I drives mirrored under raid 1 used solely for the
OS
1 174 gig 10,000 rpm SATA I drive used for the tempDB & system swap space
and the remaining 2 500 gig drives again set up on raid 1 for my development
database & environments.
does that sound better than my original set up ? if not I am open to
suggestions.
Also if I did move my OS to the new 36 gig would I need to do a complete
reinstall or could I do a back up and restore? I think it would be possible
using partition magic or a similiar tool.
thanks alot
Sounds improved. You may also want to put tempdb on two filegroups, one on
36GB and one on 174GB drives. I think the optimum for 2005 is 1 FG per CPU
(not counting hyperthreads)
TheSQLGuru
President
Indicium Resources, Inc.
"Coaster" <Coaster@.Coaster.net> wrote in message
news:OKqVNMxpHHA.960@.TK2MSFTNGP03.phx.gbl...
> Hi
> I built a server and I need some advice on reconfiguring the hard drives
> for better sql server performance. Here are my current specs
> 2 Xeon 5160 Dual Core Hypertheaded Processors
> 8 gig of fd ram
> Windows 2003 Server 64 bit
> Sql Server 2005 64 bit
> 2 500 gig Sata II hard drives set up as Raid 1 at the hard ware level.
> They are partitioned as 15 gig for the OS and the rest for my development
> environment.
> In my development testing I am working with databases starting at 30 gig
> and can run upwards of 100 gig. After initial set up and the first round
> of testing I quickly learned that I needed to move my tempDB onto the
> larger partition. I also learned that I should move it on to a fast disk
> that isn't part of the raid because it isn't necessary to backup.
> Additionally I read that there is no need to put the system swap file on
> the raid and for better performance it should be on a separate faster
> disk.
> This is how I am thinking of reconfiguring the system
> 2 36 gig 10,000 rpm SATA I drives mirrored under raid 1 used solely for
> the OS
> 1 174 gig 10,000 rpm SATA I drive used for the tempDB & system swap space
> and the remaining 2 500 gig drives again set up on raid 1 for my
> development database & environments.
> does that sound better than my original set up ? if not I am open to
> suggestions.
> Also if I did move my OS to the new 36 gig would I need to do a complete
> reinstall or could I do a back up and restore? I think it would be
> possible using partition magic or a similiar tool.
> thanks alot
>
|||I can guess that would be true, each cpu having it's own tempdb space to
use.
Thanks I'll do that
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:uC3biexpHHA.1144@.TK2MSFTNGP02.phx.gbl...
> Sounds improved. You may also want to put tempdb on two filegroups, one
> on 36GB and one on 174GB drives. I think the optimum for 2005 is 1 FG per
> CPU (not counting hyperthreads)
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Coaster" <Coaster@.Coaster.net> wrote in message
> news:OKqVNMxpHHA.960@.TK2MSFTNGP03.phx.gbl...
>

advice on hard drive set up for 2005 sql server 64 bit

Hi
I built a server and I need some advice on reconfiguring the hard drives for
better sql server performance. Here are my current specs
2 Xeon 5160 Dual Core Hypertheaded Processors
8 gig of fd ram
Windows 2003 Server 64 bit
Sql Server 2005 64 bit
2 500 gig Sata II hard drives set up as Raid 1 at the hard ware level. They
are partitioned as 15 gig for the OS and the rest for my development
environment.
In my development testing I am working with databases starting at 30 gig and
can run upwards of 100 gig. After initial set up and the first round of
testing I quickly learned that I needed to move my tempDB onto the larger
partition. I also learned that I should move it on to a fast disk that isn't
part of the raid because it isn't necessary to backup. Additionally I read
that there is no need to put the system swap file on the raid and for better
performance it should be on a separate faster disk.
This is how I am thinking of reconfiguring the system
2 36 gig 10,000 rpm SATA I drives mirrored under raid 1 used solely for the
OS
1 174 gig 10,000 rpm SATA I drive used for the tempDB & system swap space
and the remaining 2 500 gig drives again set up on raid 1 for my development
database & environments.
does that sound better than my original set up ? if not I am open to
suggestions.
Also if I did move my OS to the new 36 gig would I need to do a complete
reinstall or could I do a back up and restore? I think it would be possible
using partition magic or a similiar tool.
thanks alotSounds improved. You may also want to put tempdb on two filegroups, one on
36GB and one on 174GB drives. I think the optimum for 2005 is 1 FG per CPU
(not counting hyperthreads)
TheSQLGuru
President
Indicium Resources, Inc.
"Coaster" <Coaster@.Coaster.net> wrote in message
news:OKqVNMxpHHA.960@.TK2MSFTNGP03.phx.gbl...
> Hi
> I built a server and I need some advice on reconfiguring the hard drives
> for better sql server performance. Here are my current specs
> 2 Xeon 5160 Dual Core Hypertheaded Processors
> 8 gig of fd ram
> Windows 2003 Server 64 bit
> Sql Server 2005 64 bit
> 2 500 gig Sata II hard drives set up as Raid 1 at the hard ware level.
> They are partitioned as 15 gig for the OS and the rest for my development
> environment.
> In my development testing I am working with databases starting at 30 gig
> and can run upwards of 100 gig. After initial set up and the first round
> of testing I quickly learned that I needed to move my tempDB onto the
> larger partition. I also learned that I should move it on to a fast disk
> that isn't part of the raid because it isn't necessary to backup.
> Additionally I read that there is no need to put the system swap file on
> the raid and for better performance it should be on a separate faster
> disk.
> This is how I am thinking of reconfiguring the system
> 2 36 gig 10,000 rpm SATA I drives mirrored under raid 1 used solely for
> the OS
> 1 174 gig 10,000 rpm SATA I drive used for the tempDB & system swap space
> and the remaining 2 500 gig drives again set up on raid 1 for my
> development database & environments.
> does that sound better than my original set up ? if not I am open to
> suggestions.
> Also if I did move my OS to the new 36 gig would I need to do a complete
> reinstall or could I do a back up and restore? I think it would be
> possible using partition magic or a similiar tool.
> thanks alot
>|||I can guess that would be true, each cpu having it's own tempdb space to
use.
Thanks I'll do that
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:uC3biexpHHA.1144@.TK2MSFTNGP02.phx.gbl...
> Sounds improved. You may also want to put tempdb on two filegroups, one
> on 36GB and one on 174GB drives. I think the optimum for 2005 is 1 FG per
> CPU (not counting hyperthreads)
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Coaster" <Coaster@.Coaster.net> wrote in message
> news:OKqVNMxpHHA.960@.TK2MSFTNGP03.phx.gbl...
>sql

Advice needed: DW and ETL tools

Hi!
I'm involved in building a medium sized ware house with MS SQLServer 2000=20
as the backend. I've been searching for some good tools for SQLServer to=20
help me with the demanding tasks of planning and building the database and =
(preferrably the same tool) making the mappings and data transformations=20
when populating the database, but still I've not come across any that has=20
satisfied my needs. Oracle has a great tool for this called Oracle=20
Warehouse Builder, but haven't seen anything like it for MS databases.=20
Therefore I'm asking: Does anyone know about a tool that can be used in=20
planning, deploying and maintaining a data warehouse built on a MS=20
SQLServer 2000? Doesn't matter if it has a price tag on it or not
S=F6ren Hakola
FinlandI think that anyone that uses SQL Server to deploy a Data Warehouse... uses
SQL Server 2000 capabilities only. And it's cheeper )
- Diagram: create logic models with direct link to the tables
- Tables: Info repository
- Data Transformation Services (DTS): ETL, data transformation (populate the
tables)
- Jobs: schedule DTS for automatic ETL
- Analysis Services: If you desire to work with a MOLAP tool
Other tools:
Data Stage (Unix and Windows, all databases) - Top tool, expensive
Informatica (Unix and Windows, all databases) - Top tool, expensive|||Thanks for the response. I'll check those out!
Greetings
S.sql

advice needed on synchronization of data

Hi
I need some advice on the best approach to solve the following problem:-
One important note: The data in the table is self referencing (hierarchical)
1. There will be a server running SQL server 2000 enterprise edition and
serveral lap tops that will be running MSDE
2. The laptops will be docked and querying against the server DB.
3. At some point the laptops will be undocked from the server and will be
taken out into the field.
- First the user will select certain records and then undock the laptop
- At this point the laptops MSDE DB is wiped and the selected records
will be inserted (parent and child records)
- The laptop is now running in undocked mode and queries will happen
against the local MSDE
4. Some time in the future the laptops will then be docked back to the
server
- Any new records in MSDE will be put into the server DB (parent and
child records)
- Any deleted records will be removed from the server DB (parent and
child records)
- Any updates will happen on the server DB (note that there may be some
user intervention to allow for choice of whether the update should happen or
not)
- Now the laptop will be docked and querying against the server DB
What is the best approach to solve this problem?
There is third party software such as
http://www.red-gate.com/sql/summary.htm
which looks promising has any one used such tools successfully for this
model?
Any help will be very much appreciatedHi
This sounds like merge replication could be an option, you may want to read
http://support.microsoft.com/defaul...b;en-us;Q324992
Note the licencing issue:
"MSDE 2000 does not require any licensing when you use it in a stand-alone
mode, but when you use it as part of a replication topology that involves
regular SQL Server editions, if the server is in Per Seat mode as opposed to
a Per Processor mode, each MSDE 2000 Subscriber must have a client-access
license (CAL)."
John
"Richard Wilde" wrote:

> Hi
> I need some advice on the best approach to solve the following problem:-
> One important note: The data in the table is self referencing (hierarchica
l)
> 1. There will be a server running SQL server 2000 enterprise edition and
> serveral lap tops that will be running MSDE
> 2. The laptops will be docked and querying against the server DB.
> 3. At some point the laptops will be undocked from the server and will be
> taken out into the field.
> - First the user will select certain records and then undock the lapto
p
> - At this point the laptops MSDE DB is wiped and the selected records
> will be inserted (parent and child records)
> - The laptop is now running in undocked mode and queries will happen
> against the local MSDE
> 4. Some time in the future the laptops will then be docked back to the
> server
> - Any new records in MSDE will be put into the server DB (parent and
> child records)
> - Any deleted records will be removed from the server DB (parent and
> child records)
> - Any updates will happen on the server DB (note that there may be som
e
> user intervention to allow for choice of whether the update should happen
or
> not)
> - Now the laptop will be docked and querying against the server DB
> What is the best approach to solve this problem?
> There is third party software such as
> http://www.red-gate.com/sql/summary.htm
> which looks promising has any one used such tools successfully for this
> model?
> Any help will be very much appreciated
>
>
>|||Thanks for that esp. for the licence issue
Has anyone out there used such tools as the one below to achieve similar
results?

Advanced query question

Hi
I have a tre table setup in order to support dynamic attributs of
users:
user table:
| *userid* | *username*
| 1 | joe
| 2 | james
attributetype table:
| *attributetypeid* | *attributename* |
| 1 | height
| 2 | weight
userattribute table:
| *userattributeid* | *userid* | attributetypeid* |*text*
| 1 | 1 | 1 | 80
| 2 | 1 | 2 | 180
| 1 | 2 | 1 | 90
| 2 | 2 | 2 | 160
(the userid and attributetypeid are fk to the user and attributetype
tables.)
Now i would like to create a sql statement that returns something like:
| *userid* | *username* | *height* | *weight*
| 1 | joe | 80 | 180
| 1 | joe | 90 | 160
The should support that I add an additional attributetype, say "IQ"
and then it should return something like:
| *userid* | *username* | *height* | *weight* | *IQ*
| 1 | joe | 80 | 180 | null
| 1 | joe | 90 | 160 | null
I hope someone knows the answer to this or can at least provide
a different solution.
Regards,
Rasmus Oudal EdbergSelect UAT.UserId, U.UserName
, Min(Case When UAT.AttributeTypeId = 1 Then UAT.Text End) As Height
, Min(Case When UAT.AttributeTypeId = 2 Then UAT.Text End) As Weight
From Users As U
Join UserAttributes As UAT
On U.UserId = UAT.UserId
Group By UAT.UserId, U.UserName
When You add AttributeTypeId = 3 for IQ you would need to adjust the query l
ike
so:
Select UAT.UserId, U.UserName
, Min(Case When UAT.AttributeTypeId = 1 Then UAT.Text End) As Height
, Min(Case When UAT.AttributeTypeId = 2 Then UAT.Text End) As Weight
, Min(Case When UAT.AttributeTypeId = 3 Then UAT.Text End) As IQ
From Users As U
Join UserAttributes As UAT
On U.UserId = UAT.UserId
Group By UAT.UserId, U.UserName
There is no way to dynamically add columns to this crosstab type result with
out
using dynamic SQL or an outside reporting tool.
Thomas
"Rasmus Oudal Edberg studentz.dk>" <rasmus@.<nospam> wrote in message
news:OKjdW%23PTFHA.2336@.TK2MSFTNGP12.phx.gbl...
> Hi
> I have a tre table setup in order to support dynamic attributs of
> users:
> user table:
> | *userid* | *username*
> | 1 | joe
> | 2 | james
> attributetype table:
> | *attributetypeid* | *attributename* |
> | 1 | height
> | 2 | weight
> userattribute table:
> | *userattributeid* | *userid* | attributetypeid* |*text*
> | 1 | 1 | 1 | 80
> | 2 | 1 | 2 | 180
> | 1 | 2 | 1 | 90
> | 2 | 2 | 2 | 160
> (the userid and attributetypeid are fk to the user and attributetype table
s.)
> Now i would like to create a sql statement that returns something like:
> | *userid* | *username* | *height* | *weight*
> | 1 | joe | 80 | 180
> | 1 | joe | 90 | 160
> The should support that I add an additional attributetype, say "IQ"
> and then it should return something like:
> | *userid* | *username* | *height* | *weight* | *IQ*
> | 1 | joe | 80 | 180 | null
> | 1 | joe | 90 | 160 | null
> I hope someone knows the answer to this or can at least provide
> a different solution.
>
> Regards,
> Rasmus Oudal Edberg
>|||what you're looking for is xtab.
HOW TO: Rotate a Table in SQL Server
http://support.microsoft.com/defaul...b;EN-US;q175574
Dynamic Crosstab Queries
http://www.sqlmag.com/Articles/Inde...ArticleID=15608
xp_ags_crosstabExtended Stored Procedure
http://www.ag-software.com/xp_ags_crosstab.asp
and our RAC crosstab and pivoting utility
http://www.rac4sql.net
-oj
"Rasmus Oudal Edberg studentz.dk>" <rasmus@.<nospam> wrote in message
news:OKjdW%23PTFHA.2336@.TK2MSFTNGP12.phx.gbl...
> Hi
> I have a tre table setup in order to support dynamic attributs of
> users:
> user table:
> | *userid* | *username*
> | 1 | joe
> | 2 | james
> attributetype table:
> | *attributetypeid* | *attributename* |
> | 1 | height
> | 2 | weight
> userattribute table:
> | *userattributeid* | *userid* | attributetypeid* |*text*
> | 1 | 1 | 1 | 80
> | 2 | 1 | 2 | 180
> | 1 | 2 | 1 | 90
> | 2 | 2 | 2 | 160
> (the userid and attributetypeid are fk to the user and attributetype
> tables.)
> Now i would like to create a sql statement that returns something like:
> | *userid* | *username* | *height* | *weight*
> | 1 | joe | 80 | 180
> | 1 | joe | 90 | 160
> The should support that I add an additional attributetype, say "IQ"
> and then it should return something like:
> | *userid* | *username* | *height* | *weight* | *IQ*
> | 1 | joe | 80 | 180 | null
> | 1 | joe | 90 | 160 | null
> I hope someone knows the answer to this or can at least provide
> a different solution.
>
> Regards,
> Rasmus Oudal Edberg
>|||You are mixing dat and metadata in the schema. This is a serious
design flaw. Get a book on basic data modeling before you go any
further.|||On Fri, 29 Apr 2005 17:38:49 -0700, --CELKO-- wrote:

> You are mixing dat and metadata in the schema. This is a serious
> design flaw. Get a book on basic data modeling before you go any
> further.
Sounds like a correct observation to me.
However going through some of my DB books has
not given me an answer to an alternative datamodel
that would support the dynamic attributes I described
in my problem.
If however someone could point me in the right
direction that would help me a lot.
/Rasmus|||Hi oj,
Could you give me an example of this query using your rac4sql.
/Rasmus
"oj" <nospam_ojngo@.home.com> wrote in message
news:eNUOtYQTFHA.2996@.TK2MSFTNGP15.phx.gbl...
> what you're looking for is xtab.
> HOW TO: Rotate a Table in SQL Server
> http://support.microsoft.com/defaul...b;EN-US;q175574
> Dynamic Crosstab Queries
> http://www.sqlmag.com/Articles/Inde...ArticleID=15608
> xp_ags_crosstabExtended Stored Procedure
> http://www.ag-software.com/xp_ags_crosstab.asp
> and our RAC crosstab and pivoting utility
> http://www.rac4sql.net
> --
> -oj
>
> "Rasmus Oudal Edberg studentz.dk>" <rasmus@.<nospam> wrote in message
> news:OKjdW%23PTFHA.2336@.TK2MSFTNGP12.phx.gbl...
>|||Here is an example.
create table #users(userid int, username sysname)
insert #users select 1,'joe' union all select 2,'james'
create table #attributetypes(attributetypeid int,attributename sysname)
insert #attributetypes select 1,'height' union all select 2,'weight'
create table #userattributes(userattributeid int,userid int,attributetypeid
int,[text] int)
insert #userattributes select 1,1,1,80 union all select 2,1,2,180 union all
select 3,2,1,90 union all select 4,2,2,160
go
exec rac
@.transform='max(ua.[text])',
@.rows='u.userid & u.username',
@.pvtcol='a.attributename',
@.from='#userattributes ua join #users u on ua.userid=u.userid join
#attributetypes a on ua.attributetypeid=a.attributetypeid'
,@.shell='n'
-- ,@.row_totals='n'
-- ,@.grand_totals='n'
-- ,@.printagg='n'
,@.defaults1='y'
go
insert #attributetypes select 3,'iq'
insert #userattributes select 5,1,3,100 union all select 6,2,3,129
go
exec rac
@.transform='max(ua.[text])',
@.rows='u.userid & u.username',
@.pvtcol='a.attributename',
@.from='#userattributes ua join #users u on ua.userid=u.userid join
#attributetypes a on ua.attributetypeid=a.attributetypeid'
,@.shell='n'
-- ,@.row_totals='n'
-- ,@.grand_totals='n'
-- ,@.printagg='n'
,@.defaults1='y'
go
drop table #userattributes,#users,#attributetypes
go
--RESULT--
userid username height weight
-- -- -- --
1 joe 80 180
2 james 90 160
(1 row(s) affected)
(2 row(s) affected)
userid username height iq weight
-- -- -- -- --
1 joe 80 100 180
2 james 90 129 160
-oj
"Rasmus Oudal Edberg studentz.dk>" <rasmus@.<nospam> wrote in message
news:%23OKxKIdTFHA.2124@.TK2MSFTNGP14.phx.gbl...
> Hi oj,
> Could you give me an example of this query using your rac4sql.
> /Rasmus
>|||>> an alternative data model that would support the an alternative
datamodel that would support the dynamic attributes I described in my
problem. <<
There are no "dynamic attributes" in a properly designed RDBMS. The
idea of data modeling is that you know what the world looks like before
you build the model. You enforce the rules, define the entities and
their attributes. You do not let an unknown user, present or future,
create anything in the model on the fly. Your users are users, not
magicians that can make elephants fall out of the sky.|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1114959059.283784.264640@.g14g2000cwa.googlegroups.com...
> Your users are users, not
> magicians that can make elephants fall out of the sky.
>
Which would have scared the heck out of Hannibal as well as the Romans, BTW.

ADODB Command (Stored Procedure)

Hi!

I already sent this to the ACCESS newsgroup. But since I do not know really
which side is really causing the problem, I have decided to send this
inquiry
to this newsgroup also, if I may.

Below is the environment of the application:
a. MS Access 2003 application running on Windows XP
b. SQL Server 2000 - backend running MS Server 2003 OS

Below is the code that is giving me an error:

Dim com As ADODB.Command

Set com = New ADODB.Command

With com
.ActiveConnection = "DSN=YES2;DATABASE=YES100SQLC;"
.CommandText = "sp_Recalculate"
.CommandType = adCmdStoredProc
.Parameters.Refresh
.Parameters("@.ItemNumber") = ItemNum
.Execute ' This is where it hangs up...

TotalItems = .Parameters("@.TotalInStock")
TotalCost = .Parameters("@.TotalCost")

End With

Set com = Nothing

and the store procedure is:

CREATE PROCEDURE DBO.sp_Recalculate
@.ItemNumber nvarchar(50),
@.TotalInStock int = 0,
@.TotalCost money = 0
AS

BEGIN
SET @.TotalInStock = (
SELECT Sum([Quantity in Stock])
FROM [Inventory Products]
WHERE [Item Number] = @.ItemNumber)

SET @.TotalCost = (
SELECT Sum([Cost] * [Quantity in Stock])
FROM [Inventory Products]
WHERE [Item Number] = @.ItemNumber)

END

When the process goes to the ".Execute" line, it hangs up for a long time
then gives me an error message "Everflow". I have been trying to solve
this issue but do not have an idea for now of the cause.

Below is my finding:
a. When I run the stored procedure in the SQL analyzer, it works just fine.
I placed a SELECT statement to view the result of the stored procedure.
It gives the correct values.

Can anyone have ideas or similar problems?

Thanks.Ben (pillars4@.sbcglobal.net) writes:

Quote:

Originally Posted by

.Execute ' This is where it hangs up...
>
TotalItems = .Parameters("@.TotalInStock")
TotalCost = .Parameters("@.TotalCost")
>
End With
>
Set com = Nothing
>
and the store procedure is:
>
CREATE PROCEDURE DBO.sp_Recalculate

Do not use the sp_ prefix in your procedures. This prefix is reserved
for system procedures, and SQL Server will first look for these in master.
I don't think this explains why your process hangs, but I nevertheless
wanted to point it out.

Quote:

Originally Posted by

@.ItemNumber nvarchar(50),
@.TotalInStock int = 0,
@.TotalCost money = 0
AS

Judging from the code, the parameters @.TotalInStock and @.TotalCost
should be declared as OUTPUT. Right now your procedure is only a no-op.

Also, I can't see in you code that you create these parameters when you
call the procedure. You need to do that; you cannot just refer the
parameters after the call.

Quote:

Originally Posted by

BEGIN
SET @.TotalInStock = (
SELECT Sum([Quantity in Stock])
FROM [Inventory Products]
WHERE [Item Number] = @.ItemNumber)
>
SET @.TotalCost = (
SELECT Sum([Cost] * [Quantity in Stock])
FROM [Inventory Products]
WHERE [Item Number] = @.ItemNumber)
>
END

Rewrite as

SELECT @.TotalInStock = Sum([Quantity in Stock]),
@.TotalCost = Sum([Cost] * [Quantity in Stock])
FROM [Inventory Products]
WHERE [Item Number] = @.ItemNumber

That will slash the execution time in half.

Quote:

Originally Posted by

When the process goes to the ".Execute" line, it hangs up for a long time
then gives me an error message "Everflow". I have been trying to solve
this issue but do not have an idea for now of the cause.

I guess you mean "Overflow"? That sounds like VB message to me, which
would indicate that you are using the wrong data type for TotalItems.
(Check that you did not mistakenly declare it as Integer.) Then again,
it does not seem that you would get anything back from the procedure
at all. But maybe that is the problem? You get some unintialised junk?

Quote:

Originally Posted by

Below is my finding:
a. When I run the stored procedure in the SQL analyzer, it works just
fine.

And it completes in how long time?

--
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|||I timed the execution: 1 minute. Then it gives me the overflow error.

I double checked the code especially the declarations, and they seem to
okay. The "TotalItems" var in the calling method uses "long" as its data
type. The "@.TotalInStock" OUTPUT var in the stored procedure is declared as
"int". They seem to be okay.

Is there an issue between the number of open connections to the database?

"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns9945EF2273D83Yazorman@.127.0.0.1...

Quote:

Originally Posted by

Ben (pillars4@.sbcglobal.net) writes:

Quote:

Originally Posted by

> .Execute ' This is where it hangs up...
>>
> TotalItems = .Parameters("@.TotalInStock")
> TotalCost = .Parameters("@.TotalCost")
>>
> End With
>>
> Set com = Nothing
>>
>and the store procedure is:
>>
>CREATE PROCEDURE DBO.sp_Recalculate

>
Do not use the sp_ prefix in your procedures. This prefix is reserved
for system procedures, and SQL Server will first look for these in master.
I don't think this explains why your process hangs, but I nevertheless
wanted to point it out.
>

Quote:

Originally Posted by

> @.ItemNumber nvarchar(50),
> @.TotalInStock int = 0,
> @.TotalCost money = 0
>AS

>
Judging from the code, the parameters @.TotalInStock and @.TotalCost
should be declared as OUTPUT. Right now your procedure is only a no-op.
>
Also, I can't see in you code that you create these parameters when you
call the procedure. You need to do that; you cannot just refer the
parameters after the call.
>

Quote:

Originally Posted by

>BEGIN
> SET @.TotalInStock = (
> SELECT Sum([Quantity in Stock])
> FROM [Inventory Products]
> WHERE [Item Number] = @.ItemNumber)
>>
> SET @.TotalCost = (
> SELECT Sum([Cost] * [Quantity in Stock])
> FROM [Inventory Products]
> WHERE [Item Number] = @.ItemNumber)
>>
>END

>
Rewrite as
>
SELECT @.TotalInStock = Sum([Quantity in Stock]),
@.TotalCost = Sum([Cost] * [Quantity in Stock])
FROM [Inventory Products]
WHERE [Item Number] = @.ItemNumber
>
That will slash the execution time in half.
>

Quote:

Originally Posted by

>When the process goes to the ".Execute" line, it hangs up for a long time
>then gives me an error message "Everflow". I have been trying to solve
>this issue but do not have an idea for now of the cause.

>
I guess you mean "Overflow"? That sounds like VB message to me, which
would indicate that you are using the wrong data type for TotalItems.
(Check that you did not mistakenly declare it as Integer.) Then again,
it does not seem that you would get anything back from the procedure
at all. But maybe that is the problem? You get some unintialised junk?
>

Quote:

Originally Posted by

>Below is my finding:
>a. When I run the stored procedure in the SQL analyzer, it works just
>fine.

>
And it completes in how long time?
>
>
>
>
--
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

|||Ben (pillars4@.sbcglobal.net) writes:

Quote:

Originally Posted by

I timed the execution: 1 minute. Then it gives me the overflow error.

Does it run for one minute in QA as well?

Quote:

Originally Posted by

I double checked the code especially the declarations, and they seem to
okay. The "TotalItems" var in the calling method uses "long" as its
data type. The "@.TotalInStock" OUTPUT var in the stored procedure is
declared as "int". They seem to be okay.

And the data type for TotalCost is?

Quote:

Originally Posted by

Is there an issue between the number of open connections to the database?

No, that has nothing to do with it.

There were a couple of more issues with your code that I pointed out,
but you did not comment these. The code you posted will not work for
reasons I've already detailed.

It may be that you did not post the actual code, but just scribbled down a
sketch and introduced a few errors along the way. But in that case, I don't
know what you are doing, so I cannot say more than I've already said.

--
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|||I made th necessary changes. Below are the current scripts for both the
calling method and the
store procedure. Variable "ItemNum" is being passed as a string parameter to
the recalculate
method.

'**************************** This is the script in the calling method
*************************
Dim TotalCost As Currency, TotalItems As Long, AvgCost As Currency
Dim CurDB As Database, Inv As Recordset, InvP As Recordset, SQLStmt As
String, SQLStmt2 As String

Dim com As ADODB.Command

Set com = New ADODB.Command

With com
.ActiveConnection = "DSN=YES2;DATABASE=YES100SQLC;"
.CommandText = "sp_Recalculate"
.CommandType = adCmdStoredProc
.Parameters.Refresh
.Parameters("@.ItemNumber") = ItemNum
.Execute

TotalItems = .Parameters("@.TotalInStock")
TotalCost = .Parameters("@.TotalCost")
End With

Set com = Nothing

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

'*************************** This is the current script in the stored
procedure *******************
CREATE PROCEDURE DBO.sp_Recalculate
@.ItemNumber nvarchar(50),
@.TotalInStock int = 0 OUTPUT,
@.TotalCost money = 0 OUTPUT
AS
SELECT @.TotalInStock = Sum([Quantity in Stock]),
@.TotalCost = Sum([Cost] * [Quantity in Stock])
FROM [Inventory Products]
WHERE [Item Number] = @.ItemNumber
GO

"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns994667A846B71Yazorman@.127.0.0.1...

Quote:

Originally Posted by

Ben (pillars4@.sbcglobal.net) writes:

Quote:

Originally Posted by

>I timed the execution: 1 minute. Then it gives me the overflow error.

>
Does it run for one minute in QA as well?
>

Quote:

Originally Posted by

>I double checked the code especially the declarations, and they seem to
>okay. The "TotalItems" var in the calling method uses "long" as its
>data type. The "@.TotalInStock" OUTPUT var in the stored procedure is
>declared as "int". They seem to be okay.

>
And the data type for TotalCost is?
>

Quote:

Originally Posted by

>Is there an issue between the number of open connections to the database?

>
No, that has nothing to do with it.
>
There were a couple of more issues with your code that I pointed out,
but you did not comment these. The code you posted will not work for
reasons I've already detailed.
>
It may be that you did not post the actual code, but just scribbled down a
sketch and introduced a few errors along the way. But in that case, I
don't
know what you are doing, so I cannot say more than I've already said.
>
>
--
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

|||I also changed the stored procedure name to "procRecalculate".

"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns994667A846B71Yazorman@.127.0.0.1...

Quote:

Originally Posted by

Ben (pillars4@.sbcglobal.net) writes:

Quote:

Originally Posted by

>I timed the execution: 1 minute. Then it gives me the overflow error.

>
Does it run for one minute in QA as well?
>

Quote:

Originally Posted by

>I double checked the code especially the declarations, and they seem to
>okay. The "TotalItems" var in the calling method uses "long" as its
>data type. The "@.TotalInStock" OUTPUT var in the stored procedure is
>declared as "int". They seem to be okay.

>
And the data type for TotalCost is?
>

Quote:

Originally Posted by

>Is there an issue between the number of open connections to the database?

>
No, that has nothing to do with it.
>
There were a couple of more issues with your code that I pointed out,
but you did not comment these. The code you posted will not work for
reasons I've already detailed.
>
It may be that you did not post the actual code, but just scribbled down a
sketch and introduced a few errors along the way. But in that case, I
don't
know what you are doing, so I cannot say more than I've already said.
>
>
--
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

|||Ben (pillars4@.sbcglobal.net) writes:

Quote:

Originally Posted by

I made th necessary changes. Below are the current scripts for both the
calling method and the store procedure. Variable "ItemNum" is being
passed as a string parameter to the recalculate method.

Do you still get the overflow error, or does it work alright now?

--
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|||This is the current scripts of the application:

Dim com As ADODB.Command
Dim MyItemNumber As String, MyTotalInStock As Long, MyTotalCost As
Currency

Set com = New ADODB.Command

MyItemNumber = ItemNum
MyTotalInStock = 0
MyTotalCost = 0

With com
.ActiveConnection = "DSN=YES2;DATABASE=YES100SQLC;"
.CommandText = "procRecalculate"
.CommandType = adCmdStoredProc

.Parameters.Append .CreateParameter("ItemNumber", adVarChar,
adParamInput, MyItemNumber)
.Parameters.Append .CreateParameter("TotalInStock", adInteger,
adParamOutput, MyTotalInStock)
.Parameters.Append .CreateParameter("TotalCost", adCurrency,
adParamOutput, MyTotalCost)
.Execute

End With

Set com = Nothing

If IsNull(MyTotalInStock) Then MyTotalInStock = 0
If IsNull(MyTotalCost) Then MyTotalCost = 0

TotalItems = MyTotalInStock
TotalCost = MyTotalCost

=============================================
CREATE PROCEDURE DBO.procRecalculate
@.ItemNumber nvarchar(50),
@.TotalInStock int = 0 OUTPUT,
@.TotalCost money = 0 OUTPUT
AS
SET NOCOUNT ON

SELECT @.TotalInStock = Sum(Cast([Quantity in Stock] As int)),
@.TotalCost = Sum(Cast([Cost] * [Quantity in Stock] As money))
FROM [Inventory Products]
WHERE [Item Number] = @.ItemNumber

SET NOCOUNT OFF
GO

Unfortunately, I still get the same error. But this time, I get it in a
second.

"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns9946E6B747AD9Yazorman@.127.0.0.1...

Quote:

Originally Posted by

Ben (pillars4@.sbcglobal.net) writes:

Quote:

Originally Posted by

>I made th necessary changes. Below are the current scripts for both the
>calling method and the store procedure. Variable "ItemNum" is being
>passed as a string parameter to the recalculate method.

>
Do you still get the overflow error, or does it work alright now?
>
>
>
--
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

|||Ben (pillars4@.sbcglobal.net) writes:

Quote:

Originally Posted by

This is the current scripts of the application:
>...
.Parameters.Append .CreateParameter("ItemNumber", adVarChar,
>adParamInput, MyItemNumber)
.Parameters.Append .CreateParameter("TotalInStock", adInteger,
adParamOutput, MyTotalInStock)
.Parameters.Append .CreateParameter("TotalCost", adCurrency,
adParamOutput, MyTotalCost)

Here is an error: .CreateParameter takes five parameter of which the
fourth is the size, and the fifth is the value. Thus you need to insert
an extra comma after adParamInput, adParamOutput.

Quote:

Originally Posted by

Unfortunately, I still get the same error. But this time, I get it in a
second.

Well, at least some progress. :-)
--
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

ADO Error Code[80004005] (Selected collating sequence not supported by the operating

Hi
I am using ADO (mo.dll) to create MS ACCESS database in version
2000. It was working fine. when I tried to create database in version
2002 I got error Code[80004005] (Selected collating sequence not
supported by the operating system) i.e. unspecified error. I don't know
where is the problem.
What I did? I created blank database and then then I used ADO to create
tables with SQL.
If any body have any clue Please let me know
Thnaks
DAVEDave,
Might want to post this to one of the ACCESS NGs or one of the ADO NGs.
HTH
Jerry
"Dave" <dbsan@.yahoo.com> wrote in message
news:1129740108.529321.93180@.f14g2000cwb.googlegroups.com...
> Hi
> I am using ADO (mo.dll) to create MS ACCESS database in version
> 2000. It was working fine. when I tried to create database in version
> 2002 I got error Code[80004005] (Selected collating sequence not
> supported by the operating system) i.e. unspecified error. I don't know
> where is the problem.
> What I did? I created blank database and then then I used ADO to create
> tables with SQL.
> If any body have any clue Please let me know
> Thnaks
> DAVE
>

ADO doesnt connect without network!

Hi!

I wrote an application which connects to a SQL Server / MSDE 2000 DB through ADO components. Now the application doesn't start if I don't have an active network connection; as far as I try to connect, the error I get is:

[DBNETLIB][ConnectionOpen (Connect()).].......

Everything is ok if I simply plug the network cable and let the PC connect to the TCP/IP network.

Hope someone can help me!
Thanks

Umberto...Of course the DB is located on the same PC than the application!

Umberto|||If I write

Data Source=PC_NAME

instead of

Data Source=127.0.0.1

it works fine! Now the question is: WHY??

Umberto|||Is it MSDE or SQL Server Std ?

If MSDE, have you installed Netlib ? It is lhe parameter
DISABLENETWORKPROTOCOL=0
in setup.ini or command line parameters|||Finally I solved the problem by writing:

....;Data Source=(local);....

in the connection string!

Thanks
Umberto

ado datasets

hi

i am using older ado datasets in a borland 6 program.

i need to retrieve the data per record count....

example is that i need all 1000 records but i only want to retrieve them 100 at a time....

how will i accomplish this?

This may give you some usable ideas.

Paging Queries
www.aspfaq.com/2120