Get 2 Decimal Places in Avg

I am running the query below but the results show only the integer portion.
I know the results should be values like "2.33" instead of "2". How do I get
the correct result format?
Wayne
========================================
SELECT judgeevalsbyunits.JudgeName, judgeevalsbyunits.JudgeCaption,
Count(judgeevalsbyunits.UnitID) As "Count of Evals",
Avg(judgeevalsbyunits.RegScoring) As "Reg Scoring Avg",
Avg(judgeevalsbyunits.WCScoring) As "WC Scoring Avg",
Avg(judgeevalsbyunits.TapeDialog) As "Tape Dialog Avg",
Avg(judgeevalsbyunits.Conduct) As "Conduct Avg"
FROM judgeevalsbyunits
INNER JOIN judges2eval ON judgeevalsbyunits.JudgeID=judges2eval.ID
Group By judgeevalsbyunits.JudgeName, judgeevalsbyunits.JudgeCaptionIf the source columns are only integer values, you only get those back, if
you want to get a decimal value back, you have to convert the source columns
to the appropiate format, eg. AVG(CONVERT(decimal(8,2),YourColumn))
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Wayne Wengert" <wayneSKIPSPAM@.wengert.org> schrieb im Newsbeitrag
news:%2354kWYrZFHA.3364@.TK2MSFTNGP09.phx.gbl...
>I am running the query below but the results show only the integer portion.
>I know the results should be values like "2.33" instead of "2". How do I
>get the correct result format?
> Wayne
> ========================================
> SELECT judgeevalsbyunits.JudgeName, judgeevalsbyunits.JudgeCaption,
> Count(judgeevalsbyunits.UnitID) As "Count of Evals",
> Avg(judgeevalsbyunits.RegScoring) As "Reg Scoring Avg",
> Avg(judgeevalsbyunits.WCScoring) As "WC Scoring Avg",
> Avg(judgeevalsbyunits.TapeDialog) As "Tape Dialog Avg",
> Avg(judgeevalsbyunits.Conduct) As "Conduct Avg"
> FROM judgeevalsbyunits
> INNER JOIN judges2eval ON judgeevalsbyunits.JudgeID=judges2eval.ID
> Group By judgeevalsbyunits.JudgeName, judgeevalsbyunits.JudgeCaption
>|||Try,
...
Avg(judgeevalsbyunits.RegScoring * 1.0) As "Reg Scoring Avg",
...
The average value of an integer column is an integer value. Cast the value
to decimal to get a decimal avg.
Example:
select
avg(c1 * 1.0)
from
(
select cast(1 as int)
union all
select cast(2 as int)
) t1(c1)
select
avg(c1)
from
(
select cast(1 as int)
union all
select cast(2 as int)
) t1(c1)
go
AMB
"Wayne Wengert" wrote:

> I am running the query below but the results show only the integer portion
.
> I know the results should be values like "2.33" instead of "2". How do I g
et
> the correct result format?
> Wayne
> ========================================
> SELECT judgeevalsbyunits.JudgeName, judgeevalsbyunits.JudgeCaption,
> Count(judgeevalsbyunits.UnitID) As "Count of Evals",
> Avg(judgeevalsbyunits.RegScoring) As "Reg Scoring Avg",
> Avg(judgeevalsbyunits.WCScoring) As "WC Scoring Avg",
> Avg(judgeevalsbyunits.TapeDialog) As "Tape Dialog Avg",
> Avg(judgeevalsbyunits.Conduct) As "Conduct Avg"
> FROM judgeevalsbyunits
> INNER JOIN judges2eval ON judgeevalsbyunits.JudgeID=judges2eval.ID
> Group By judgeevalsbyunits.JudgeName, judgeevalsbyunits.JudgeCaption
>
>|||convert the fields into the decimal format like
AVG(CAST(judgeevalsbyunits.RegScoring) AS FLOAT) or AS DECIMAL(x,x)
cheers
"Wayne Wengert" <wayneSKIPSPAM@.wengert.org> wrote in message
news:%2354kWYrZFHA.3364@.TK2MSFTNGP09.phx.gbl...
>I am running the query below but the results show only the integer portion.
>I know the results should be values like "2.33" instead of "2". How do I
>get the correct result format?
> Wayne
> ========================================
> SELECT judgeevalsbyunits.JudgeName, judgeevalsbyunits.JudgeCaption,
> Count(judgeevalsbyunits.UnitID) As "Count of Evals",
> Avg(judgeevalsbyunits.RegScoring) As "Reg Scoring Avg",
> Avg(judgeevalsbyunits.WCScoring) As "WC Scoring Avg",
> Avg(judgeevalsbyunits.TapeDialog) As "Tape Dialog Avg",
> Avg(judgeevalsbyunits.Conduct) As "Conduct Avg"
> FROM judgeevalsbyunits
> INNER JOIN judges2eval ON judgeevalsbyunits.JudgeID=judges2eval.ID
> Group By judgeevalsbyunits.JudgeName, judgeevalsbyunits.JudgeCaption
>|||Thanks guys. That is exactly what I needed.
Wayne
"keber" <k@.c.com> wrote in message
news:O6fKoirZFHA.3152@.TK2MSFTNGP14.phx.gbl...
> convert the fields into the decimal format like
> AVG(CAST(judgeevalsbyunits.RegScoring) AS FLOAT) or AS DECIMAL(x,x)
> cheers
> "Wayne Wengert" <wayneSKIPSPAM@.wengert.org> wrote in message
> news:%2354kWYrZFHA.3364@.TK2MSFTNGP09.phx.gbl...
>

get @@identity from several records

Hi,

I was wondering if you can help.

I am running an sql query that creates several new records based on a select query. In other words duplicating some existing records in a table.

What I need to do is after I have created these new records update a field in each of them.

Any ideas how I can retrieve all of their ids at insertion and then use them to update a field in each of them?

Ideally I would like to use @.@.identity and datareaders with vb.net.

Cheers

Mark :)

You need to put all the TSQL into a stored procedure. After each insert use SCOPE_IDENTITY() to get the id of the record just inserted.

|||Decorate your table with a new column, let's call it "batchid". It could be a uniqueidentifier.

DECLARE @.batchid uniqueidentifier

SELECT @.batchid=newid()

--The insert

INSERT INTO table (column1,column2 ..... , batchid)
SELECT column1,column2......, @.batchid FROM table WHERE...

--Retrieve the new id:s

SELECT id FROM table WHERE batchid=@.batchid

But it's quite likely that you can insert the correct data directly, instead of having to update it afterwards.

INSERT INTO table (column1,column2 ..... , column15)
SELECT column1,column2......, @.thenewvalueforcolumn15 FROM table WHERE...|||

Although I like the solution of gunteman, I'm just wondering why you can't update the field in the INSERT statement?

|||

Hi,

Thanks for your help. But i would really like to update the field in the insert statement.

Any suggestions?

Cheers

Mark :)

|||

I described it above. If you want to set the new value directly, just include it in your insert statement.

INSERT INTO table (column1,column2 ..... , column15)
SELECT column1,column2......, @.thenewvalueforcolumn15 FROM table WHERE...

If you show us your INSERT and UPDATE statement, we could help you combine them.

Get 'DB Error 10083' with SQL Server 2000

Setup: SQL Server 2000 sp3 on a Windows 2000 machine, transactions running through an ODBC connection running SQLSRV32.dll version 2000.86.1830.00

Transactions: Being generated via a Citrix server (MetaFrame Presentation Server, Enterprise Edition 4.0(Build 2198), Service Pack 2005.04.... This sits on top of an install of Windows 2003 terminal server installaion.

Error: Appears to only be happening on a ADD(Insert) ....

Any thoughts on this would be a big help.

Dave

Is there any more to the error message? I can't find reference to error 10083|||

Here is the exact message I received...

Db Error: 10083

"Cursor statement generated no results.

No changes made to database.

Insert Into Test (testid, testdesc,sequence,createdt,creatorid,lastmodifieddt,modifierid,testinstances,allowinstances,activityid,worklisttable,qutoqc) Values ('999','Test Test',0,'10-31-2006 9:22:30.253','SysAdmin','10-31-2006 9:22:30.253','SysAdmin',1,'N',0,'N','N')

The above highlighted text was the exact message...

Thanks-David

|||

This is not a SQL Server error message. You need to check your data access layer. Here are few guesses on what the problem is:

1. You are issueing insert statement but expecting a result set to be returned. SQL Server will return some result if SET NOCOUNT OFF (in which it will be number of rows affected) or if the insert trigger returns results. In other cases, if SET NOCOUNT ON is set you will not get any results for the insert statement

2. You are doing the wrong thing from the data access layer and should use some direct execute mechanism that doesn't look for results from the command.

|||

Thanks for giving me some direction on this issue! I will do some testing to how I can resolve this issue in a good way.

David

|||

I have same this error message in some computer

I use MSDE with 2 clients. found this problem 1 client.

The error message show with this query

Db Error: 10083

"Cursor statement generated no results.

No changes made to database.

INSERT INTO EMSide ( SideID, SideCode, SideName, SideNameEng ) VALUES ( 1000, '001', 'test', '' )

This is query from another computer:

Begin Transaction

go

declare @.P1 int

set @.P1=180150003

declare @.P2 int

set @.P2=2

declare @.P3 int

set @.P3=-1

exec sp_cursoropen @.cursor = @.P1 output, @.stmt = 'SELECT sidecode ', @.scrollopt = @.P2 output, @.ccopt = 4, @.rows = @.P3 output

select @.P1, @.P2, @.P3

go

exec sp_cursorfetch @.cursor = 180150003, @.fetchtype = 1, @.rownumber = 0, @.nrows = 2

go

exec sp_cursorclose @.cursor = 180150003

go

Select prefix From SMId where name ='aum/AUM/emside'

go

Select max ( prefix ) From SMID Where Type =1 And charindex ( 'emside' , name ) > 0

go

Begin Transaction

go

INSERT INTO SMID ( Name , Prefix , Type ) VALUES ( 'aum/AUM/emside' , 1000 , 1 )

go

Commit Transaction

go

declare @.P1 int

set @.P1=180150004

declare @.P2 int

set @.P2=8

declare @.P3 int

set @.P3=1

exec sp_cursoropen @.cursor = @.P1 output, @.stmt = 'SELECT max(sidei', @.scrollopt = @.P2 output, @.ccopt = 4, @.rows = @.P3 output

select @.P1, @.P2, @.P3

go

exec sp_cursorfetch @.cursor = 180150004, @.fetchtype = 1, @.rownumber = 0, @.nrows = 1

go

exec sp_cursorclose @.cursor = 180150004

go

declare @.P1 int

set @.P1=180150005

declare @.P2 int

set @.P2=2

declare @.P3 int

set @.P3=-1

exec sp_cursoropen @.cursor = @.P1 output, @.stmt = 'SELECT sideid FR', @.scrollopt = @.P2 output, @.ccopt = 4, @.rows = @.P3 output

select @.P1, @.P2, @.P3

go

exec sp_cursorfetch @.cursor = 180150005, @.fetchtype = 1, @.rownumber = 0, @.nrows = 2

go

exec sp_cursorclose @.cursor = 180150005

go

INSERT INTO EMSide ( SideID, SideCode, SideName, SideNameEng ) VALUES ( 1000, '001', 'test', '' )

go

Select InsertFlag From SMaudit_option Where brchid =1

go

SELECT GETDATE( ) As cf_server_time

go

Select prefix From SMId where name ='aum/AUM/smAudit'

go

declare @.P1 int

set @.P1=180150006

declare @.P2 int

set @.P2=8

declare @.P3 int

set @.P3=1

exec sp_cursoropen @.cursor = @.P1 output, @.stmt = 'SELECT max(audit', @.scrollopt = @.P2 output, @.ccopt = 4, @.rows = @.P3 output

select @.P1, @.P2, @.P3

go

exec sp_cursorfetch @.cursor = 180150006, @.fetchtype = 1, @.rownumber = 0, @.nrows = 1

go

exec sp_cursorclose @.cursor = 180150006

go

declare @.P1 int

set @.P1=180150007

declare @.P2 int

set @.P2=2

declare @.P3 int

set @.P3=-1

exec sp_cursoropen @.cursor = @.P1 output, @.stmt = 'SELECT audit_id ', @.scrollopt = @.P2 output, @.ccopt = 4, @.rows = @.P3 output

select @.P1, @.P2, @.P3

go

exec sp_cursorfetch @.cursor = 180150007, @.fetchtype = 1, @.rownumber = 0, @.nrows = 2

go

exec sp_cursorclose @.cursor = 180150007

go

INSERT INTO SMAudit ( audit_id, audit_system, audit_screen, audit_datetime, audit_username, audit_action, audit_docuno, audit_columnid, brchid, audit_computername, audit_refid ) VALUES ( 139002, 1, 6, '5-3-2007 18:36:24.453', 'prosoft', 'I', '001', 1000, 1, 'AUM', 139002 )

go

Commit Transaction

go

|||

This's profiler from another computer with same form.

Begin Transaction
go
sp_tables @.table_name = 'emside'
go
sp_special_columns @.table_name = 'emside'
go
select emside.SideID FROM emside where 0 = 1
go
select timestamp from emside where 0 = 1
go
select * from emside where 0 = 1
go
SELECT sidecode FROM emside where 0 = 1
go
set rowcount 2
go
SELECT sidecode , emside.SideID , emside.SideID, emside.SideCode, emside.SideName, emside.SideNameEng, emside.Remark FROM emside WHERE (sidecode like '%AAA%' AND (LTRIM(sidecode) = 'AAA' OR RTRIM(sidecode) = 'AAA')) order by emside.SideID
go
set rowcount 0
go
Select prefix From SMId where name ='accdomain\sql2000/MORAGOTD/emside'
go
Select max ( prefix ) From SMID Where Type =1 And charindex ( 'emside' , name ) > 0
go
Begin Transaction
go
INSERT INTO SMID ( Name , Prefix , Type ) VALUES ( 'accdomain\sql2000/MORAGOTD/emside' , 2000 , 1 )
go
Commit Transaction
go
sp_tables @.table_name = 'emside'
go
sp_special_columns @.table_name = 'emside'
go
select emside.SideID FROM emside where 0 = 1
go
select timestamp from emside where 0 = 1
go
select * from emside where 0 = 1
go
SELECT sideid FROM emside where 0 = 1
go
set rowcount 2
go
SELECT sideid , emside.SideID , emside.SideID, emside.SideCode, emside.SideName, emside.SideNameEng, emside.Remark FROM emside WHERE sideid = 2000 order by emside.SideID
go
set rowcount 0
go
INSERT INTO EMSide ( SideID, SideCode, SideName, SideNameEng ) VALUES ( 2000, 'AAA', 'AAA', '' )
go
Rollback Transaction
go

|||

Did you ever find a resolution to this? We are getting this error in an application we are upgrading. The upgraded version uses SQL Server 2000 and the old version uses SQL Server 7. We are only seeing the error on some of our machines but not all. On two different machines where we have SQL Server 2000 client utilities installed we do not get the error. On another machine where I have SQL Server 7 client utilities or no client utilities exist we do get the error. I think there is a driver found in the 2000 client utilities that resolves the issue, but can't quite put my finger on which one it is. The application vendor seems to think it is our machine configuration.

Thanks,

I found a solution: Check your version of the ntwdblib.dll driver. Version 2000.80.194.0 fixed the issue.

Geographic Cluster

I have one site which is running W2K with one IIS server with 100
Websites and one SQL2000 server, we are running with 6 meg of
bandwidth at our main site and 1.5 meg of bandwith at our remote
site.in case of a disaster at my main site, i want my remote site to
start taking web requests. I am looking into Microsofts Geographic
Cluster option, how would I go about setting up this option? can it
all be done with Microsofts products, if so how? if not what third
party software do I need? I have not used clustering before, so I
might have left something out, if more info is needed, please let me
know.Fist, MS has no geographic cluster option. Unless you are
referring to a Majority Node Set cluster, which is only in
Windows Server 2003 (which isn't good for SQL and I'll
explain in a minute), all geographic cluster solutions are
third-party based. You should read KB article
http://support.microsoft.com/default.aspx?scid=kb;en-
us;280743.
MNS clusters are not good for SQL Server because in a
clustered situation, you still have a shared disk
requirement.
Other options you may want to consider are things like NLB
for your web servers and log shipping to create the
standby DB at your other site.
>--Original Message--
>I have one site which is running W2K with one IIS server
with 100
>Websites and one SQL2000 server, we are running with 6
meg of
>bandwidth at our main site and 1.5 meg of bandwith at
our remote
>site.in case of a disaster at my main site, i want my
remote site to
>start taking web requests. I am looking into Microsofts
Geographic
>Cluster option, how would I go about setting up this
option? can it
>all be done with Microsofts products, if so how? if not
what third
>party software do I need? I have not used clustering
before, so I
>might have left something out, if more info is needed,
please let me
>know.
>.
>|||thanks for the quick response.
On Wed, 10 Dec 2003 08:31:20 -0800, "Allan Hirt"
<allanh@.NOSPAMavanade.com> wrote:
>Fist, MS has no geographic cluster option. Unless you are
>referring to a Majority Node Set cluster, which is only in
>Windows Server 2003 (which isn't good for SQL and I'll
>explain in a minute), all geographic cluster solutions are
>third-party based. You should read KB article
>http://support.microsoft.com/default.aspx?scid=kb;en-
>us;280743.
>MNS clusters are not good for SQL Server because in a
>clustered situation, you still have a shared disk
>requirement.
>Other options you may want to consider are things like NLB
>for your web servers and log shipping to create the
>standby DB at your other site.
>
>>--Original Message--
>>I have one site which is running W2K with one IIS server
>with 100
>>Websites and one SQL2000 server, we are running with 6
>meg of
>>bandwidth at our main site and 1.5 meg of bandwith at
>our remote
>>site.in case of a disaster at my main site, i want my
>remote site to
>>start taking web requests. I am looking into Microsofts
>Geographic
>>Cluster option, how would I go about setting up this
>option? can it
>>all be done with Microsofts products, if so how? if not
>what third
>>party software do I need? I have not used clustering
>before, so I
>>might have left something out, if more info is needed,
>please let me
>>know.
>>.|||Allen,
do you know what third party packages are out there for handling
geographic clustering?
On Wed, 10 Dec 2003 08:31:20 -0800, "Allan Hirt"
<allanh@.NOSPAMavanade.com> wrote:
>Fist, MS has no geographic cluster option. Unless you are
>referring to a Majority Node Set cluster, which is only in
>Windows Server 2003 (which isn't good for SQL and I'll
>explain in a minute), all geographic cluster solutions are
>third-party based. You should read KB article
>http://support.microsoft.com/default.aspx?scid=kb;en-
>us;280743.
>MNS clusters are not good for SQL Server because in a
>clustered situation, you still have a shared disk
>requirement.
>Other options you may want to consider are things like NLB
>for your web servers and log shipping to create the
>standby DB at your other site.
>
>>--Original Message--
>>I have one site which is running W2K with one IIS server
>with 100
>>Websites and one SQL2000 server, we are running with 6
>meg of
>>bandwidth at our main site and 1.5 meg of bandwith at
>our remote
>>site.in case of a disaster at my main site, i want my
>remote site to
>>start taking web requests. I am looking into Microsofts
>Geographic
>>Cluster option, how would I go about setting up this
>option? can it
>>all be done with Microsofts products, if so how? if not
>what third
>>party software do I need? I have not used clustering
>before, so I
>>might have left something out, if more info is needed,
>please let me
>>know.
>>.|||http://www.nsisoftware.com/pro/geocluster/
"Pat" <htech@.hotmail.com> wrote in message
news:t1ihtv4b04b6ten1u43ohha02a7vmsrc19@.4ax.com...
>
> Allen,
> do you know what third party packages are out there for handling
> geographic clustering?
> On Wed, 10 Dec 2003 08:31:20 -0800, "Allan Hirt"
> <allanh@.NOSPAMavanade.com> wrote:
> >Fist, MS has no geographic cluster option. Unless you are
> >referring to a Majority Node Set cluster, which is only in
> >Windows Server 2003 (which isn't good for SQL and I'll
> >explain in a minute), all geographic cluster solutions are
> >third-party based. You should read KB article
> >http://support.microsoft.com/default.aspx?scid=kb;en-
> >us;280743.
> >
> >MNS clusters are not good for SQL Server because in a
> >clustered situation, you still have a shared disk
> >requirement.
> >
> >Other options you may want to consider are things like NLB
> >for your web servers and log shipping to create the
> >standby DB at your other site.
> >
> >
> >>--Original Message--
> >>I have one site which is running W2K with one IIS server
> >with 100
> >>Websites and one SQL2000 server, we are running with 6
> >meg of
> >>bandwidth at our main site and 1.5 meg of bandwith at
> >our remote
> >>site.in case of a disaster at my main site, i want my
> >remote site to
> >>start taking web requests. I am looking into Microsofts
> >Geographic
> >>Cluster option, how would I go about setting up this
> >option? can it
> >>all be done with Microsofts products, if so how? if not
> >what third
> >>party software do I need? I have not used clustering
> >before, so I
> >>might have left something out, if more info is needed,
> >please let me
> >>know.
> >>.
> >>
>|||Thank You.
On Thu, 11 Dec 2003 16:19:38 -0500, "Eric Sabine"
<mopar41@.____h_o_t_m_a_i_l_._ScPoAmM> wrote:
>http://www.nsisoftware.com/pro/geocluster/
>
>"Pat" <htech@.hotmail.com> wrote in message
>news:t1ihtv4b04b6ten1u43ohha02a7vmsrc19@.4ax.com...
>>
>> Allen,
>> do you know what third party packages are out there for handling
>> geographic clustering?
>> On Wed, 10 Dec 2003 08:31:20 -0800, "Allan Hirt"
>> <allanh@.NOSPAMavanade.com> wrote:
>> >Fist, MS has no geographic cluster option. Unless you are
>> >referring to a Majority Node Set cluster, which is only in
>> >Windows Server 2003 (which isn't good for SQL and I'll
>> >explain in a minute), all geographic cluster solutions are
>> >third-party based. You should read KB article
>> >http://support.microsoft.com/default.aspx?scid=kb;en-
>> >us;280743.
>> >
>> >MNS clusters are not good for SQL Server because in a
>> >clustered situation, you still have a shared disk
>> >requirement.
>> >
>> >Other options you may want to consider are things like NLB
>> >for your web servers and log shipping to create the
>> >standby DB at your other site.
>> >
>> >
>> >>--Original Message--
>> >>I have one site which is running W2K with one IIS server
>> >with 100
>> >>Websites and one SQL2000 server, we are running with 6
>> >meg of
>> >>bandwidth at our main site and 1.5 meg of bandwith at
>> >our remote
>> >>site.in case of a disaster at my main site, i want my
>> >remote site to
>> >>start taking web requests. I am looking into Microsofts
>> >Geographic
>> >>Cluster option, how would I go about setting up this
>> >option? can it
>> >>all be done with Microsofts products, if so how? if not
>> >what third
>> >>party software do I need? I have not used clustering
>> >before, so I
>> >>might have left something out, if more info is needed,
>> >please let me
>> >>know.
>> >>.
>> >>
>sql

Generating and directing new rows in PostExecute.

I want to construct a dataset based on all rows passed in from a source and, upon running through all of them, push each dataset record to an output.

Unfortunately, upon calling the AddRow method, I get an "object reference not set to an instance of an object" error. Am I not allowed to create new rows in PostExecute?

Bill,

Can you post your code, indicate which line the error occurs on, and tell us how you have configured the outputs of the component.

-Jamie

|||It errors on reference to a row buffer.

With dsMaterialBuffer
.AddRow()
'Code for row values
End With

I've added the following to check if it's actually defined within PostExecute;
If dsMaterialBuffer Is Nothing Then MsgBox("dsMaterialBuffer is nothing.") 'My row buffer

dsMaterialBuffer, which is the row buffer I'm using, evaluates to "Nothing" and that is where my object reference errors are coming from.

Is it possible that the row buffers are being thrown out once PostExecute hits?

The outputs, by the way, are non-synchronous with standard columns.|||

What is the name of the output as defined in the inputs and outputs tab of the script component editor?

-Jamie

|||dsMaterial|||

Hmm, strange. if you want, drop me an email via here: http://blogs.conchango.com/jamiethomson/contact.aspx and I'll reply so you can send me the package. I can take a look and see if anything jumps out.

If you can build the package so that I am able to run it as well (i.e. not reliant on external data sources, just use a script source component instead) then that'd help.

-Jamie

Generating and directing new rows in PostExecute.

I want to construct a dataset based on all rows passed in from a source and, upon running through all of them, push each dataset record to an output.

Unfortunately, upon calling the AddRow method, I get an "object reference not set to an instance of an object" error. Am I not allowed to create new rows in PostExecute?

Bill,

Can you post your code, indicate which line the error occurs on, and tell us how you have configured the outputs of the component.

-Jamie

|||It errors on reference to a row buffer.

With dsMaterialBuffer
.AddRow()
'Code for row values
End With

I've added the following to check if it's actually defined within PostExecute;
If dsMaterialBuffer Is Nothing Then MsgBox("dsMaterialBuffer is nothing.") 'My row buffer

dsMaterialBuffer, which is the row buffer I'm using, evaluates to "Nothing" and that is where my object reference errors are coming from.

Is it possible that the row buffers are being thrown out once PostExecute hits?

The outputs, by the way, are non-synchronous with standard columns.|||

What is the name of the output as defined in the inputs and outputs tab of the script component editor?

-Jamie

|||dsMaterial|||

Hmm, strange. if you want, drop me an email via here: http://blogs.conchango.com/jamiethomson/contact.aspx and I'll reply so you can send me the package. I can take a look and see if anything jumps out.

If you can build the package so that I am able to run it as well (i.e. not reliant on external data sources, just use a script source component instead) then that'd help.

-Jamie

Generalized perfomance problems with long running queries

Hi,
My problems with SQL Server 2000 SP4 multiplies. Long running queries are
blocking each other (even themselves); i.e.:
SPID:58 blocked by 58 locktype: PAGEIOLATCH_SH
I've discovered a lot of I/O contention due to PAGEIOLATCH_SH lock type. I'm
going to review the queries and database options in order to optimize them
(new indexing, bulk inserting, constraints disabling, frequent checkpointing
,
simple model restore, etc), but... is there a "brute force" solution for
this? (not increasing hardware resources, please; a 4 CPU Dell computer with
4 Gigs of RAM and a SCSI RAID ought to be enough to handle a few avg. 5 Mil.
rows tables, I believe).
Anyone can help, please?
Luis FernándezOn 25.08.2006 17:19, Luis Fernndez wrote:
> Hi,
> My problems with SQL Server 2000 SP4 multiplies. Long running queries are
> blocking each other (even themselves); i.e.:
> SPID:58 blocked by 58 locktype: PAGEIOLATCH_SH
> I've discovered a lot of I/O contention due to PAGEIOLATCH_SH lock type. I
'm
> going to review the queries and database options in order to optimize them
> (new indexing, bulk inserting, constraints disabling, frequent checkpointi
ng,
> simple model restore, etc), but... is there a "brute force" solution for
> this? (not increasing hardware resources, please; a 4 CPU Dell computer wi
th
> 4 Gigs of RAM and a SCSI RAID ought to be enough to handle a few avg. 5 Mi
l.
> rows tables, I believe).
The first thing I'd probably check is memory settings, just to make sure
your 2k doesn't hit a 128MB limit.
I'd do a profiler trace of a typical workload (a day, a week whatever)
which includes execution plans. Then check execution plans of long
runners. You can even feed the trace to the ITW but look carefully at
recommendations.
Kind regards
robert|||have you try to reduce your maxdop option?
(maximum CPU used in parrallel queries)
this reduce the blocking behavior, but also slow down the queries.
review your queries and try to create pre-aggregated tables.
try to force the maxdop option per query. you can also force SQL Server in
these queries to not change the join order
by default SQL Server optimize the queries and choose the best plan; but
this plan is not always the best choice.
so you can manually choose how the joins will be used (hash join or merge
join...) and in which order (join 2 tables first, then the third you want
etc...)
can you describe your disk subsystem?
during the execution of these queries, how is your disk activity?
"Luis Fernndez" <LuisFernndez@.discussions.microsoft.com> wrote in message
news:714859AD-B6BF-40F0-853C-C935D1129C9D@.microsoft.com...
> Hi,
> My problems with SQL Server 2000 SP4 multiplies. Long running queries are
> blocking each other (even themselves); i.e.:
> SPID:58 blocked by 58 locktype: PAGEIOLATCH_SH
> I've discovered a lot of I/O contention due to PAGEIOLATCH_SH lock type.
> I'm
> going to review the queries and database options in order to optimize them
> (new indexing, bulk inserting, constraints disabling, frequent
> checkpointing,
> simple model restore, etc), but... is there a "brute force" solution for
> this? (not increasing hardware resources, please; a 4 CPU Dell computer
> with
> 4 Gigs of RAM and a SCSI RAID ought to be enough to handle a few avg. 5
> Mil.
> rows tables, I believe).
> Anyone can help, please?
> Luis Fernndez