Multi-value parameter default values not working

I have a parameter that gets it's available values from a dataset.

I use this exact same data to populate the default values.

When I run the report, the available values get populated; however the default values are not being selected.

This works on other parameters on the same report. However, on this parameter, it is not working.

I have tried ltrim/rtrim (Been burned with that before when the field type is a char)

I change the data field in the query, without changing the parameter setup, and it works...

Here is my query:

Code Snippet

Select distinct
CityName

from dimHotel dH (NOLOCK)
Inner join factHotel fH (NOLOCK)
on dh.HotelKey = fH.HotelKey

Where dH.CityName <> ''
and dH.CityName is not null
and fH.ClientKey in (@.ClientID)

Order by CityName

The parameter is setup correctly, and matches the setup of another parameter on the same report that is working fine.

I have tried deleting the parameter and re-adding it. This did not work.

I also deleted and re-added the query. No luck.

Any ideas?

Thanks!!

Thanks

Ok, this is weird...

If I change the query to:

Code Snippet

Select distinct
Replace(CityName,'','') as CityName

It works fine.

If I do a lower or upper, it works fine.

Convert or l/rtrim do not work.

Anyone have any ideas?

At this point I am thinking that there is a character in one of the fields that shouldnt be in there... I am looking at that now.

BobP

|||

Ok, it's an intermittent problem.

Sometimes the replace works, sometimes it doesn't.

Still trying to figure out why...

Does anyone know if there are any characters that could be in the data field, and prevent the default parameters from populating?

Thanks

|||

This same query populates a multi value drop down on a totally different report and it does not work there, either.

I am thinking it is data related.

Can anyone think of a reason why the multi select drop down would be populated, but the defaults not be set?

There are only 360 rows, so it's not a row number issue.

Any ideas would be very welcomed.

Thanks

BobP

Multi-value parameter default values not working

I have a parameter that gets it's available values from a dataset.

I use this exact same data to populate the default values.

When I run the report, the available values get populated; however the default values are not being selected.

This works on other parameters on the same report. However, on this parameter, it is not working.

I have tried ltrim/rtrim (Been burned with that before when the field type is a char)

I change the data field in the query, without changing the parameter setup, and it works...

Here is my query:

Code Snippet

Select distinct
CityName

from dimHotel dH (NOLOCK)
Inner join factHotel fH (NOLOCK)
on dh.HotelKey = fH.HotelKey

Where dH.CityName <> ''
and dH.CityName is not null
and fH.ClientKey in (@.ClientID)

Order by CityName

The parameter is setup correctly, and matches the setup of another parameter on the same report that is working fine.

I have tried deleting the parameter and re-adding it. This did not work.

I also deleted and re-added the query. No luck.

Any ideas?

Thanks!!

Thanks

Ok, this is weird...

If I change the query to:

Code Snippet

Select distinct
Replace(CityName,'','') as CityName

It works fine.

If I do a lower or upper, it works fine.

Convert or l/rtrim do not work.

Anyone have any ideas?

At this point I am thinking that there is a character in one of the fields that shouldnt be in there... I am looking at that now.

BobP

|||

Ok, it's an intermittent problem.

Sometimes the replace works, sometimes it doesn't.

Still trying to figure out why...

Does anyone know if there are any characters that could be in the data field, and prevent the default parameters from populating?

Thanks

|||

This same query populates a multi value drop down on a totally different report and it does not work there, either.

I am thinking it is data related.

Can anyone think of a reason why the multi select drop down would be populated, but the defaults not be set?

There are only 360 rows, so it's not a row number issue.

Any ideas would be very welcomed.

Thanks

BobP

sql

Multithreaded run of SSIS packages

Is it safe to run several SSIS packages in parallel in a multithreaded application? The package objects are not accessed from multiple threads: every thread is handling its own package (see below).

void task_inside_thread()
{
System::String ^s = "...";
Microsoft::SqlServer::Dts::Runtime::Package p;
p.LoadFromXML(s, nullptr);

// callback handler
MyEventsClass ^ eventsClass = gcnew MyEventsClass();

DTSExecResult result = p.Execute(nullptr, nullptr, eventsClass, nullptr, nullptr);
return;

}

Is there any relevant documentation with respect to this issue?

Thanks,

Bogdan

Yes, this should be safe, packages are independent of each other.|||

I worried more about the fact the package instances might use global objects for registration and for signalling their current status.

Thanks for your answer.

Bogdan

|||Yes, they sometime do. I have to re-phrase it - they behave as independent objects, taking care of locking global state when needed.

Multi-table UDF not returning all rows

I've been tearing my hair out over this UDF. The code works within a
stored procedure and also run ad-hoc against the database, but does not
run properly within my UDF. We've been using the SP, but I do need a
UDF instead now.

All users, including branch office, sub-companies and companies and so
on up the lines are in the same table. I need a function which returns
a row for each level, eventually getting to the master company all the
way at the top, but this UDF acts as though it can't enter the loop and
only inserts the @.userID and @.branchID rows. I have played with the
WHILE condition to no avail.

Any ideas on what I am missing?

(Running against SQL Server 2000)
----------------

ALTER FUNCTION udfUplineGetCompany (@.userID int)

RETURNS @.upline table (companyID int, companyname varchar(100), info
varchar(100))
AS
BEGIN

DECLARE @.branchID int
DECLARE @.companyID int
DECLARE @.tempID int

--Insert the original user data
INSERT INTO @.upline
SELECT tblusersid, companyname, 'userID'
FROM tblusers
WHERE tblusersid = @.userid

SELECT @.branchID = tblUsers.tblUsersID
FROM tblUsers
INNER JOIN tblUsersUsersLnk
ON tblUsers.tblUsersID = tblUsersUsersLnk.tblUsersID_Parent
WHERE tblUsersUsersLnk.tblUsersID_Child = @.userid

--Up one level
INSERT INTO @.upline
SELECT tblusersid, companyname, 'branchID'
FROM tblusers
WHERE tblusersid = @.branchid

SET @.tempID = @.branchID

WHILE @.@.ROWCOUNT <> 0
BEGIN
SELECT @.companyID = tblUsers.tblUsersID
FROM tblUsers
INNER JOIN tblUsersUsersLnk
ON tblUsers.tblUsersID = tblUsersUsersLnk.tblUsersID_Parent
WHERE tblUsersUsersLnk.tblUsersID_Child = @.tempID
AND tblUsersId <> 6

--Insert a row for each level up
INSERT INTO @.upline
SELECT tblusersid, companyname, 'companyID'
FROM tblusers
WHERE tblusersid = @.companyID

SET @.tempID = @.companyID

END

RETURN

ENDWell, if I try to run in as an ad-hoc query, it doesn't run properly at
all.

The use of @.@.rowcount is bound to go wrong. Also, the INSERT into the
table variable will be executed regardless of a match (or miss) of the
parent selection.

You should check the @.@.rowcount immediately after the "SELECT @.CompanyID
= ..." statement. If you do not want to handle the @.@.rowcount result at
that point, then make sure you move the @.@.rowcount status to a variable
immediately after the SELECT statement.

Gert-Jan

P.S. If you have proper primary key/foreign keys in place, then joining
to the tblUsers table in the SELECT statement is unnecessary.

nicolec@.octitle.com wrote:
> I've been tearing my hair out over this UDF. The code works within a
> stored procedure and also run ad-hoc against the database, but does not
> run properly within my UDF. We've been using the SP, but I do need a
> UDF instead now.
> All users, including branch office, sub-companies and companies and so
> on up the lines are in the same table. I need a function which returns
> a row for each level, eventually getting to the master company all the
> way at the top, but this UDF acts as though it can't enter the loop and
> only inserts the @.userID and @.branchID rows. I have played with the
> WHILE condition to no avail.
> Any ideas on what I am missing?
> (Running against SQL Server 2000)
> ----------------
> ALTER FUNCTION udfUplineGetCompany (@.userID int)
> RETURNS @.upline table (companyID int, companyname varchar(100), info
> varchar(100))
> AS
> BEGIN
> DECLARE @.branchID int
> DECLARE @.companyID int
> DECLARE @.tempID int
> --Insert the original user data
> INSERT INTO @.upline
> SELECT tblusersid, companyname, 'userID'
> FROM tblusers
> WHERE tblusersid = @.userid
> SELECT @.branchID = tblUsers.tblUsersID
> FROM tblUsers
> INNER JOIN tblUsersUsersLnk
> ON tblUsers.tblUsersID = tblUsersUsersLnk.tblUsersID_Parent
> WHERE tblUsersUsersLnk.tblUsersID_Child = @.userid
> --Up one level
> INSERT INTO @.upline
> SELECT tblusersid, companyname, 'branchID'
> FROM tblusers
> WHERE tblusersid = @.branchid
> SET @.tempID = @.branchID
> WHILE @.@.ROWCOUNT <> 0
> BEGIN
> SELECT @.companyID = tblUsers.tblUsersID
> FROM tblUsers
> INNER JOIN tblUsersUsersLnk
> ON tblUsers.tblUsersID = tblUsersUsersLnk.tblUsersID_Parent
> WHERE tblUsersUsersLnk.tblUsersID_Child = @.tempID
> AND tblUsersId <> 6
> --Insert a row for each level up
> INSERT INTO @.upline
> SELECT tblusersid, companyname, 'companyID'
> FROM tblusers
> WHERE tblusersid = @.companyID
> SET @.tempID = @.companyID
> END
> RETURN
> END|||Sometimes you look at something SO long you don't see the obvious...
thanks for the pointer, that worked perfect. For future newsgroup
reference, the new function text is at the bottom.

>P.S. If you have proper primary key/foreign keys in place, then joining
to the tblUsers table in the SELECT statement is unnecessary.

No keys anywhere in this database and it's driving me nuts. Every join
must be explicit.

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

ALTER FUNCTION udfUplineGetCompany (@.userID int)

RETURNS @.upline table (companyID int, companyname varchar(100), info
varchar(100), rows int)
AS
BEGIN

DECLARE @.branchID int
DECLARE @.companyID int
DECLARE @.tempID int
DECLARE @.rows int

SET @.rows = 1

INSERT INTO @.upline
SELECT tblusersid, companyname, 'userID', @.rows
FROM tblusers
WHERE tblusersid = @.userid

SELECT @.branchID = tblUsers.tblUsersID
FROM tblUsers
INNER JOIN tblUsersUsersLnk
ON tblUsers.tblUsersID = tblUsersUsersLnk.tblUsersID_Parent
WHERE tblUsersUsersLnk.tblUsersID_Child = @.userid

INSERT INTO @.upline
SELECT tblusersid, companyname, 'branchID', @.rows
FROM tblusers
WHERE tblusersid = @.branchid

SET @.tempID = @.branchID

WHILE @.rows = 1
BEGIN
SELECT @.companyID = tblUsers.tblUsersID
FROM tblUsers
INNER JOIN tblUsersUsersLnk
ON tblUsers.tblUsersID = tblUsersUsersLnk.tblUsersID_Parent
WHERE tblUsersUsersLnk.tblUsersID_Child = @.tempID
AND tblUsersId <> 6

SET @.rows = @.@.rowcount

INSERT INTO @.upline
SELECT tblusersid, companyname, 'companyID', @.rows
FROM tblusers
WHERE tblusersid = @.companyID
AND @.rows = 1

SET @.tempID = @.companyID

END

RETURN

END

Multi-table joins & tempdb growth / query performance

Hi
When I run a LARGE query (20 tables, some have 4 million rows), it
takes hours to run and fills up 35GB on tempdb.
Could someone tell me, when joining these tables, does SQL server join
the entire table into tempdb or only the columns that are needed, i.e.
:
* returned in output
* used as part of the join
* used in a later join
I'm trying to reduce the time AND the impact on tempdb space, and I
think I can do this by doing the following:
Say (simple example) TableA joins to TableB which joins to TableC
I want to return the fields: A.1, B.1, C.1
I have to join A to B on A.2 = B.2
I have to join B to C on B.3 = C.2
Table B has 10 columns (B.4, B.5, B.6, B.7, etc...)
Does SQL merge (join) into tempdb the following after the first join:
A.1, B.1, B.3 ' Or does it merge & store the entire table?
If it does the entire table, I think I can speed things up & save time
by doing:
SELECT B.1, B.2, B.3
INTO TableB_temp
FROM TableB
Then join the query on field 2 & 3, but now tempdb can ONLY put a max
of 3 fields from TableB_Temp into storage during query processing...
saving space & hopefully (read/write) time'
Or does SQL do this automatically?
Thanks
Sean"Sean" <plugwalsh@.yahoo.com> wrote in message
news:3698af3c.0405250724.523e4c7a@.posting.google.com...
> Hi
> When I run a LARGE query (20 tables, some have 4 million rows), it
> takes hours to run and fills up 35GB on tempdb.
> Could someone tell me, when joining these tables, does SQL server join
> the entire table into tempdb or only the columns that are needed, i.e.
> :
> * returned in output
> * used as part of the join
> * used in a later join
> I'm trying to reduce the time AND the impact on tempdb space, and I
> think I can do this by doing the following:
> Say (simple example) TableA joins to TableB which joins to TableC
> I want to return the fields: A.1, B.1, C.1
> I have to join A to B on A.2 = B.2
> I have to join B to C on B.3 = C.2
> Table B has 10 columns (B.4, B.5, B.6, B.7, etc...)
> Does SQL merge (join) into tempdb the following after the first join:
> A.1, B.1, B.3 ' Or does it merge & store the entire table?
> If it does the entire table, I think I can speed things up & save time
> by doing:
> SELECT B.1, B.2, B.3
> INTO TableB_temp
> FROM TableB
> Then join the query on field 2 & 3, but now tempdb can ONLY put a max
> of 3 fields from TableB_Temp into storage during query processing...
> saving space & hopefully (read/write) time'
> Or does SQL do this automatically?
A merge join requires that the columns that it is joining them on are both
sorted. If it is using the tempdb then it is having to sort them into order
first, then merging them. It would speed things up if you indexed the
columns (the Primary Key will already be indexed, but not necessarily the
Foreign Key). As an index is already sorted, then the mergedb should not be
required, and the whole thing should run much faster
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.690 / Virus Database: 451 - Release Date: 22/05/2004sql

Multi-table joins & tempdb growth / query performance

Hi
When I run a LARGE query (20 tables, some have 4 million rows), it
takes hours to run and fills up 35GB on tempdb.
Could someone tell me, when joining these tables, does SQL server join
the entire table into tempdb or only the columns that are needed, i.e.
:
* returned in output
* used as part of the join
* used in a later join
I'm trying to reduce the time AND the impact on tempdb space, and I
think I can do this by doing the following:
Say (simple example) TableA joins to TableB which joins to TableC
I want to return the fields: A.1, B.1, C.1
I have to join A to B on A.2 = B.2
I have to join B to C on B.3 = C.2
Table B has 10 columns (B.4, B.5, B.6, B.7, etc...)
Does SQL merge (join) into tempdb the following after the first join:
A.1, B.1, B.3 ? Or does it merge & store the entire table?
If it does the entire table, I think I can speed things up & save time
by doing:
SELECT B.1, B.2, B.3
INTO TableB_temp
FROM TableB
Then join the query on field 2 & 3, but now tempdb can ONLY put a max
of 3 fields from TableB_Temp into storage during query processing...
saving space & hopefully (read/write) time?
Or does SQL do this automatically?
Thanks
Sean
"Sean" <plugwalsh@.yahoo.com> wrote in message
news:3698af3c.0405250724.523e4c7a@.posting.google.c om...
> Hi
> When I run a LARGE query (20 tables, some have 4 million rows), it
> takes hours to run and fills up 35GB on tempdb.
> Could someone tell me, when joining these tables, does SQL server join
> the entire table into tempdb or only the columns that are needed, i.e.
> :
> * returned in output
> * used as part of the join
> * used in a later join
> I'm trying to reduce the time AND the impact on tempdb space, and I
> think I can do this by doing the following:
> Say (simple example) TableA joins to TableB which joins to TableC
> I want to return the fields: A.1, B.1, C.1
> I have to join A to B on A.2 = B.2
> I have to join B to C on B.3 = C.2
> Table B has 10 columns (B.4, B.5, B.6, B.7, etc...)
> Does SQL merge (join) into tempdb the following after the first join:
> A.1, B.1, B.3 ? Or does it merge & store the entire table?
> If it does the entire table, I think I can speed things up & save time
> by doing:
> SELECT B.1, B.2, B.3
> INTO TableB_temp
> FROM TableB
> Then join the query on field 2 & 3, but now tempdb can ONLY put a max
> of 3 fields from TableB_Temp into storage during query processing...
> saving space & hopefully (read/write) time?
> Or does SQL do this automatically?
A merge join requires that the columns that it is joining them on are both
sorted. If it is using the tempdb then it is having to sort them into order
first, then merging them. It would speed things up if you indexed the
columns (the Primary Key will already be indexed, but not necessarily the
Foreign Key). As an index is already sorted, then the mergedb should not be
required, and the whole thing should run much faster
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.690 / Virus Database: 451 - Release Date: 22/05/2004

Multi-table joins & tempdb growth / query performance

Hi
When I run a LARGE query (20 tables, some have 4 million rows), it
takes hours to run and fills up 35GB on tempdb.
Could someone tell me, when joining these tables, does SQL server join
the entire table into tempdb or only the columns that are needed, i.e.
:
* returned in output
* used as part of the join
* used in a later join
I'm trying to reduce the time AND the impact on tempdb space, and I
think I can do this by doing the following:
Say (simple example) TableA joins to TableB which joins to TableC
I want to return the fields: A.1, B.1, C.1
I have to join A to B on A.2 = B.2
I have to join B to C on B.3 = C.2
Table B has 10 columns (B.4, B.5, B.6, B.7, etc...)
Does SQL merge (join) into tempdb the following after the first join:
A.1, B.1, B.3 ' Or does it merge & store the entire table?
If it does the entire table, I think I can speed things up & save time
by doing:
SELECT B.1, B.2, B.3
INTO TableB_temp
FROM TableB
Then join the query on field 2 & 3, but now tempdb can ONLY put a max
of 3 fields from TableB_Temp into storage during query processing...
saving space & hopefully (read/write) time'
Or does SQL do this automatically?
Thanks
Sean"Sean" <plugwalsh@.yahoo.com> wrote in message
news:3698af3c.0405250724.523e4c7a@.posting.google.com...
> Hi
> When I run a LARGE query (20 tables, some have 4 million rows), it
> takes hours to run and fills up 35GB on tempdb.
> Could someone tell me, when joining these tables, does SQL server join
> the entire table into tempdb or only the columns that are needed, i.e.
> :
> * returned in output
> * used as part of the join
> * used in a later join
> I'm trying to reduce the time AND the impact on tempdb space, and I
> think I can do this by doing the following:
> Say (simple example) TableA joins to TableB which joins to TableC
> I want to return the fields: A.1, B.1, C.1
> I have to join A to B on A.2 = B.2
> I have to join B to C on B.3 = C.2
> Table B has 10 columns (B.4, B.5, B.6, B.7, etc...)
> Does SQL merge (join) into tempdb the following after the first join:
> A.1, B.1, B.3 ' Or does it merge & store the entire table?
> If it does the entire table, I think I can speed things up & save time
> by doing:
> SELECT B.1, B.2, B.3
> INTO TableB_temp
> FROM TableB
> Then join the query on field 2 & 3, but now tempdb can ONLY put a max
> of 3 fields from TableB_Temp into storage during query processing...
> saving space & hopefully (read/write) time'
> Or does SQL do this automatically?
A merge join requires that the columns that it is joining them on are both
sorted. If it is using the tempdb then it is having to sort them into order
first, then merging them. It would speed things up if you indexed the
columns (the Primary Key will already be indexed, but not necessarily the
Foreign Key). As an index is already sorted, then the mergedb should not be
required, and the whole thing should run much faster
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.690 / Virus Database: 451 - Release Date: 22/05/2004

Multi-step SSIS Job Hangs

I have two SSIS packages that I want to run in one SQL Agent job as two individual steps. The two packages run fine when they are in separate jobs. However, when I run the job conaining both SSIS packages (under the same proxy), the first SSIS package starts, but hangs in the middle.

I then tried setting the DelayValidation flag to True as suggested for a similar issue in another thread from this forum. After changing the DelayValidation flag to True for all containers and tasks on the second SSIS package, the first SSIS package ran completely through sucessfully, but the job continued executing for hours and the second SSIS package never started. I finally killed the job.

Any ideas as to what is the problem here? I have logged to the event viewer and see that the first package completes sucessfully. They run successfully in separate jobs, but I can not get them to run together within the same job without hanging.

Any help is appreciated,

Paulette

What task is the package hanging on?

Do the packages share any external resources? Perhaps there is resource locking occurring.

-Jamie

|||

Thanks Jamie. Well, I know Step 1 (the first SSIS package) finished based on an event viewer message 'Package "ExtractPkg" finished Successfully.', but that was the last event to be logged. No event was logged stating that the second package started. It seems that the job is hanging while trying to start the second package, so I cannot see a task that either package is hanging on.

As far as shared resources... The packages do not share a "Data Source" in the solution, however they do have individual connections that point to the same SQL server and database.

Also, they both share audit and error handler "child" packages that they call from their event handlers. DelayValidation for the Audit and Error handler packages is currently set to false. Could the sharing of these audit and error handler packages be causing a problem? We planned on removing the audit and error handler packages from future versions since we've discovered the automatic logging provided in SSIS is sufficient for our needs.

- Paulette

|||

My first avenue of attack would be to disable/remove the eventhandlers to see if the problem goes away. That sounds like it could be the problem.

By the way, executing a package from each eventhandler is a huge overhead as I have talked about here: http://blogs.conchango.com/jamiethomson/archive/2005/03/01/1100.aspx

-Jamie

|||

Thank you Jamie! When I removed the event handlers the job ran perfectly!

- Paulette

Multiprocessors

What are my options for the number of multiprocessors that SQL Server 2000
will run on?
1 - 2 - 4 - 8 - 16?
Is there at some point where throwing more processors at SQL Server won't
make a difference?
Hi,
That depends on the OS version and SQl server version you are using.
SQL server 2000 enterprise edition on a Windows 2000 data center edition
will support 32 Processors (CPU).
See the details in topic "maximum capacity specifications" in books online.
Thanks
Hari
MCDBA
"William Gower" <w_gower@.hotmail.com> wrote in message
news:Ofl9m9QeEHA.2384@.TK2MSFTNGP09.phx.gbl...
> What are my options for the number of multiprocessors that SQL Server 2000
> will run on?
> 1 - 2 - 4 - 8 - 16?
> Is there at some point where throwing more processors at SQL Server won't
> make a difference?
>
|||Hi William
Re:
>Is there at some point where throwing more processors at SQL Server won't
make a difference?<
Conceptually not - connections are affinitised (though not hard-wired)
against CPUs by SQL Server's internal scheduling mechanisms, so assuming you
have many more user connections than CPUs, there is generally a benefit to
be gained from the extra CPUs.
In practical terms however, there is a law of diminishing returns in
continually adding CPUs. Where apex of that curve lies depends on many
scenario specifics.
HTH
Regards,
Greg Linwood
SQL Server MVP
"William Gower" <w_gower@.hotmail.com> wrote in message
news:Ofl9m9QeEHA.2384@.TK2MSFTNGP09.phx.gbl...
> What are my options for the number of multiprocessors that SQL Server 2000
> will run on?
> 1 - 2 - 4 - 8 - 16?
> Is there at some point where throwing more processors at SQL Server won't
> make a difference?
>
|||They're only affinitized for single threaded queries.
"Greg Linwood" <g_linwoodQhotmail.com> wrote in message
news:uqV3$0VeEHA.3632@.TK2MSFTNGP09.phx.gbl...
> Hi William
> Re:
> make a difference?<
> Conceptually not - connections are affinitised (though not hard-wired)
> against CPUs by SQL Server's internal scheduling mechanisms, so assuming
you[vbcol=seagreen]
> have many more user connections than CPUs, there is generally a benefit to
> be gained from the extra CPUs.
> In practical terms however, there is a law of diminishing returns in
> continually adding CPUs. Where apex of that curve lies depends on many
> scenario specifics.
> HTH
> Regards,
> Greg Linwood
> SQL Server MVP
> "William Gower" <w_gower@.hotmail.com> wrote in message
> news:Ofl9m9QeEHA.2384@.TK2MSFTNGP09.phx.gbl...
2000[vbcol=seagreen]
won't
>
|||Connection objects (spids) are indeed affinitised to cpus by virtue of the
fact that they're owned by ums schedulers which are themselves affinitised.
Worker threads which satisfy parallelism (multi-threaded queries) or other
multi-threaded work are another thing all together - they certainly are not
affinitised.
Regards,
Greg Linwood
SQL Server MVP
"Kevin" <ReplyTo@.Newsgroups.only> wrote in message
news:ePxBvbdeEHA.556@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> They're only affinitized for single threaded queries.
> "Greg Linwood" <g_linwoodQhotmail.com> wrote in message
> news:uqV3$0VeEHA.3632@.TK2MSFTNGP09.phx.gbl...
won't[vbcol=seagreen]
> you
to
> 2000
> won't
>

Multiprocessors

What are my options for the number of multiprocessors that SQL Server 2000
will run on?
1 - 2 - 4 - 8 - 16?
Is there at some point where throwing more processors at SQL Server won't
make a difference?Hi,
That depends on the OS version and SQl server version you are using.
SQL server 2000 enterprise edition on a Windows 2000 data center edition
will support 32 Processors (CPU).
See the details in topic "maximum capacity specifications" in books online.
Thanks
Hari
MCDBA
"William Gower" <w_gower@.hotmail.com> wrote in message
news:Ofl9m9QeEHA.2384@.TK2MSFTNGP09.phx.gbl...
> What are my options for the number of multiprocessors that SQL Server 2000
> will run on?
> 1 - 2 - 4 - 8 - 16?
> Is there at some point where throwing more processors at SQL Server won't
> make a difference?
>|||Hi William
Re:
>Is there at some point where throwing more processors at SQL Server won't
make a difference?<
Conceptually not - connections are affinitised (though not hard-wired)
against CPUs by SQL Server's internal scheduling mechanisms, so assuming you
have many more user connections than CPUs, there is generally a benefit to
be gained from the extra CPUs.
In practical terms however, there is a law of diminishing returns in
continually adding CPUs. Where apex of that curve lies depends on many
scenario specifics.
HTH
Regards,
Greg Linwood
SQL Server MVP
"William Gower" <w_gower@.hotmail.com> wrote in message
news:Ofl9m9QeEHA.2384@.TK2MSFTNGP09.phx.gbl...
> What are my options for the number of multiprocessors that SQL Server 2000
> will run on?
> 1 - 2 - 4 - 8 - 16?
> Is there at some point where throwing more processors at SQL Server won't
> make a difference?
>|||They're only affinitized for single threaded queries.
"Greg Linwood" <g_linwoodQhotmail.com> wrote in message
news:uqV3$0VeEHA.3632@.TK2MSFTNGP09.phx.gbl...
> Hi William
> Re:
> make a difference?<
> Conceptually not - connections are affinitised (though not hard-wired)
> against CPUs by SQL Server's internal scheduling mechanisms, so assuming
you
> have many more user connections than CPUs, there is generally a benefit to
> be gained from the extra CPUs.
> In practical terms however, there is a law of diminishing returns in
> continually adding CPUs. Where apex of that curve lies depends on many
> scenario specifics.
> HTH
> Regards,
> Greg Linwood
> SQL Server MVP
> "William Gower" <w_gower@.hotmail.com> wrote in message
> news:Ofl9m9QeEHA.2384@.TK2MSFTNGP09.phx.gbl...
2000[vbcol=seagreen]
won't[vbcol=seagreen]
>|||Connection objects (spids) are indeed affinitised to cpus by virtue of the
fact that they're owned by ums schedulers which are themselves affinitised.
Worker threads which satisfy parallelism (multi-threaded queries) or other
multi-threaded work are another thing all together - they certainly are not
affinitised.
Regards,
Greg Linwood
SQL Server MVP
"Kevin" <ReplyTo@.Newsgroups.only> wrote in message
news:ePxBvbdeEHA.556@.tk2msftngp13.phx.gbl...
> They're only affinitized for single threaded queries.
> "Greg Linwood" <g_linwoodQhotmail.com> wrote in message
> news:uqV3$0VeEHA.3632@.TK2MSFTNGP09.phx.gbl...
won't[vbcol=seagreen]
> you
to[vbcol=seagreen]
> 2000
> won't
>

Multiprocessors

What are my options for the number of multiprocessors that SQL Server 2000
will run on?
1 - 2 - 4 - 8 - 16?
Is there at some point where throwing more processors at SQL Server won't
make a difference?Hi,
That depends on the OS version and SQl server version you are using.
SQL server 2000 enterprise edition on a Windows 2000 data center edition
will support 32 Processors (CPU).
See the details in topic "maximum capacity specifications" in books online.
Thanks
Hari
MCDBA
"William Gower" <w_gower@.hotmail.com> wrote in message
news:Ofl9m9QeEHA.2384@.TK2MSFTNGP09.phx.gbl...
> What are my options for the number of multiprocessors that SQL Server 2000
> will run on?
> 1 - 2 - 4 - 8 - 16?
> Is there at some point where throwing more processors at SQL Server won't
> make a difference?
>|||Hi William
Re:
>Is there at some point where throwing more processors at SQL Server won't
make a difference?<
Conceptually not - connections are affinitised (though not hard-wired)
against CPUs by SQL Server's internal scheduling mechanisms, so assuming you
have many more user connections than CPUs, there is generally a benefit to
be gained from the extra CPUs.
In practical terms however, there is a law of diminishing returns in
continually adding CPUs. Where apex of that curve lies depends on many
scenario specifics.
HTH
Regards,
Greg Linwood
SQL Server MVP
"William Gower" <w_gower@.hotmail.com> wrote in message
news:Ofl9m9QeEHA.2384@.TK2MSFTNGP09.phx.gbl...
> What are my options for the number of multiprocessors that SQL Server 2000
> will run on?
> 1 - 2 - 4 - 8 - 16?
> Is there at some point where throwing more processors at SQL Server won't
> make a difference?
>|||They're only affinitized for single threaded queries.
"Greg Linwood" <g_linwoodQhotmail.com> wrote in message
news:uqV3$0VeEHA.3632@.TK2MSFTNGP09.phx.gbl...
> Hi William
> Re:
> >Is there at some point where throwing more processors at SQL Server won't
> make a difference?<
> Conceptually not - connections are affinitised (though not hard-wired)
> against CPUs by SQL Server's internal scheduling mechanisms, so assuming
you
> have many more user connections than CPUs, there is generally a benefit to
> be gained from the extra CPUs.
> In practical terms however, there is a law of diminishing returns in
> continually adding CPUs. Where apex of that curve lies depends on many
> scenario specifics.
> HTH
> Regards,
> Greg Linwood
> SQL Server MVP
> "William Gower" <w_gower@.hotmail.com> wrote in message
> news:Ofl9m9QeEHA.2384@.TK2MSFTNGP09.phx.gbl...
> > What are my options for the number of multiprocessors that SQL Server
2000
> > will run on?
> >
> > 1 - 2 - 4 - 8 - 16?
> >
> > Is there at some point where throwing more processors at SQL Server
won't
> > make a difference?
> >
> >
>|||Connection objects (spids) are indeed affinitised to cpus by virtue of the
fact that they're owned by ums schedulers which are themselves affinitised.
Worker threads which satisfy parallelism (multi-threaded queries) or other
multi-threaded work are another thing all together - they certainly are not
affinitised.
Regards,
Greg Linwood
SQL Server MVP
"Kevin" <ReplyTo@.Newsgroups.only> wrote in message
news:ePxBvbdeEHA.556@.tk2msftngp13.phx.gbl...
> They're only affinitized for single threaded queries.
> "Greg Linwood" <g_linwoodQhotmail.com> wrote in message
> news:uqV3$0VeEHA.3632@.TK2MSFTNGP09.phx.gbl...
> > Hi William
> >
> > Re:
> > >Is there at some point where throwing more processors at SQL Server
won't
> > make a difference?<
> >
> > Conceptually not - connections are affinitised (though not hard-wired)
> > against CPUs by SQL Server's internal scheduling mechanisms, so assuming
> you
> > have many more user connections than CPUs, there is generally a benefit
to
> > be gained from the extra CPUs.
> >
> > In practical terms however, there is a law of diminishing returns in
> > continually adding CPUs. Where apex of that curve lies depends on many
> > scenario specifics.
> >
> > HTH
> >
> > Regards,
> > Greg Linwood
> > SQL Server MVP
> >
> > "William Gower" <w_gower@.hotmail.com> wrote in message
> > news:Ofl9m9QeEHA.2384@.TK2MSFTNGP09.phx.gbl...
> > > What are my options for the number of multiprocessors that SQL Server
> 2000
> > > will run on?
> > >
> > > 1 - 2 - 4 - 8 - 16?
> > >
> > > Is there at some point where throwing more processors at SQL Server
> won't
> > > make a difference?
> > >
> > >
> >
> >
>

Multipls cursors help

Hello,
I am trying to create multiple cursors and update 2 separate tables. But the
data tables seem to be locking after I run them. Is my logic off? My code is
below -
set xact_abort on
declare @.user_id int, @.topic_name varchar(20), @.key_seq_num int
declare GetUserID cursor for
select [user_id] from users where [user_id] not in (select [user_id] from
userdata)
open GetUserID
fetch next from GetUserID into @.user_id
while @.@.fetch_status = 0
begin
begin tran
--new cursor
declare GetUserData cursor for
select topic_name from userdata where [user_id] = 327
open GetUserData
fetch next from GetUserData into @.topic_name
while @.@.fetch_status = 0
begin
begin tran
--new cursor
declare GetNextID cursor for
select key_seq_num from nextkey where key_name = 'userdata_user_data_id'
open GetNextID
fetch next from GetNextID into @.key_seq_num
while @.@.fetch_status = 0
begin
begin tran
insert into UserData(user_data_id, [user_id], topic_name, update_date,
update_user, create_date, create_user)
select @.key_seq_num, @.user_id, @.topic_name, getdate(), 'Default',
getdate(), 'Default'
update nextkey set key_seq_num = @.key_seq_num + 1
from nextkey where key_name = 'userdata_user_data_id'
commit tran
print @.key_seq_num
fetch next from GetNextID into @.key_seq_num
end
close GetNextID
deallocate GetNextID
fetch next from GetUserData into @.topic_name
end
close GetUserData
deallocate GetUserData
fetch next from GetUserID into @.user_id
end
close GetUserID
deallocate GetUserIDAnil,
Can you explain what are you trying to accomplish?
AMB
"Anil" wrote:

> Hello,
> I am trying to create multiple cursors and update 2 separate tables. But t
he
> data tables seem to be locking after I run them. Is my logic off? My code
is
> below -
> set xact_abort on
> declare @.user_id int, @.topic_name varchar(20), @.key_seq_num int
> declare GetUserID cursor for
> select [user_id] from users where [user_id] not in (select [user_id] from
> userdata)
> open GetUserID
> fetch next from GetUserID into @.user_id
> while @.@.fetch_status = 0
> begin
> begin tran
> --new cursor
> declare GetUserData cursor for
> select topic_name from userdata where [user_id] = 327
> open GetUserData
> fetch next from GetUserData into @.topic_name
> while @.@.fetch_status = 0
> begin
> begin tran
> --new cursor
> declare GetNextID cursor for
> select key_seq_num from nextkey where key_name = 'userdata_user_data_id'
> open GetNextID
> fetch next from GetNextID into @.key_seq_num
> while @.@.fetch_status = 0
> begin
> begin tran
> insert into UserData(user_data_id, [user_id], topic_name, update_date,
> update_user, create_date, create_user)
> select @.key_seq_num, @.user_id, @.topic_name, getdate(), 'Default',
> getdate(), 'Default'
> update nextkey set key_seq_num = @.key_seq_num + 1
> from nextkey where key_name = 'userdata_user_data_id'
> commit tran
> print @.key_seq_num
> fetch next from GetNextID into @.key_seq_num
> end
> close GetNextID
> deallocate GetNextID
> fetch next from GetUserData into @.topic_name
> end
> close GetUserData
> deallocate GetUserData
> fetch next from GetUserID into @.user_id
> end
> close GetUserID
> deallocate GetUserID|||I am trying to take all the user id's from cursor 1, some value from cursor
2
and 3 and update them into a table. Cursor 3 has an id that needs to be
incremented each time as it is not an auto number. The way I have laid out m
y
cursors, is that the correct way?
Thanks.
Anil
"Alejandro Mesa" wrote:
> Anil,
> Can you explain what are you trying to accomplish?
>
> AMB
>
> "Anil" wrote:
>|||Anil,
Can you explain it without referencing the cursors?
AMB
"Anil" wrote:
> I am trying to take all the user id's from cursor 1, some value from curso
r 2
> and 3 and update them into a table. Cursor 3 has an id that needs to be
> incremented each time as it is not an auto number. The way I have laid out
my
> cursors, is that the correct way?
> Thanks.
> Anil
> "Alejandro Mesa" wrote:
>|||Please provide DDL and sample data.
http://www.aspfaq.com/etiquette.asp?id=5006
AMB
"Alejandro Mesa" wrote:
> Anil,
> Can you explain it without referencing the cursors?
>
> AMB
>
> "Anil" wrote:
>|||Is the syntax correct for multiple cursors? Is it possible to do what I am
trying to do with cursors?
"Alejandro Mesa" wrote:
> Anil,
> Can you explain what are you trying to accomplish?
>
> AMB
>
> "Anil" wrote:
>|||Is the syntax correct? Is it possible to do what I am trying to do?
"Alejandro Mesa" wrote:
> Anil,
> Can you explain what are you trying to accomplish?
>
> AMB
>
> "Anil" wrote:
>|||Hi Anil,
Looks like the tables which you are accessing is getting locked since you do
all your stuff within a transaction.
You can do 2 things to see if the issue is happening because of a lock
1. Remove all transaction (begin/commit tran) and see if your code actually
works.
2. If your code works after step 1 then use WITH (NOLOCK) clause in your
select statements.
Regards,
Joe.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:47D3D1AF-4E14-4C0B-80B9-66ADCCF91254@.microsoft.com...
> Please provide DDL and sample data.
> http://www.aspfaq.com/etiquette.asp?id=5006
>
> AMB
> "Alejandro Mesa" wrote:
>
cursor 2
be
out my
tables. But the
My code is
[user_id] from
'userdata_user_data_id'
update_date,|||Hey Joe,
Thanks for the help. Worked like a charm!!
Anil
"Joe" wrote:

> Hi Anil,
> Looks like the tables which you are accessing is getting locked since you
do
> all your stuff within a transaction.
> You can do 2 things to see if the issue is happening because of a lock
> 1. Remove all transaction (begin/commit tran) and see if your code actuall
y
> works.
> 2. If your code works after step 1 then use WITH (NOLOCK) clause in your
> select statements.
>
> Regards,
> Joe.
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in messag
e
> news:47D3D1AF-4E14-4C0B-80B9-66ADCCF91254@.microsoft.com...
> cursor 2
> be
> out my
> tables. But the
> My code is
> [user_id] from
> 'userdata_user_data_id'
> update_date,
>
>|||Hey Joe,
Thanks for the help. Worked like a charm!!
Anil
"Joe" wrote:

> Hi Anil,
> Looks like the tables which you are accessing is getting locked since you
do
> all your stuff within a transaction.
> You can do 2 things to see if the issue is happening because of a lock
> 1. Remove all transaction (begin/commit tran) and see if your code actuall
y
> works.
> 2. If your code works after step 1 then use WITH (NOLOCK) clause in your
> select statements.
>
> Regards,
> Joe.
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in messag
e
> news:47D3D1AF-4E14-4C0B-80B9-66ADCCF91254@.microsoft.com...
> cursor 2
> be
> out my
> tables. But the
> My code is
> [user_id] from
> 'userdata_user_data_id'
> update_date,
>
>sql

multiplication by row without a cursor? Myth or Madness?

Hi all...I have a friend who has a problem I'm trying to help with (no...REALLY...it's a FRIEND, not ME!!! *LOL*)

I haven't run across the need...but in a nutshell...we have a table with many rows of data, one column in each row needs to be multiplied by all other same-columns in the table's other rows.

For example...
MyKey int, MyFloat float(53)

I want to multiply Myfloat by all other Myfloat columns in the table.

Similar to SUM(MyFloat) but something like PRODUCT(MyFloat).

Is there a aggregate kept in a basement closet somewhere, or a way to perform this operation rather than using a cursor to do it:

An example of my table:
1 3.2
2 4.1
3 7.1

if I could do a PRODUCT(MyFloat) I would want the result to be (3.2 X 4.1 X 7.1) or 93.152

Do I have to do this with a cursor?

Thanks!
PaulGot it...
DECLARE @.MyInt int
SET @.MyInt = 1
SELECT @.MyInt = @.MyInt * MyFloat from MyTable where MyKey = <whatever>
print @.Myint

Yeah, I know...it's an overflow waiting to happen...but still...

Thanks for reading...and sorry to talk to myself yet again...I guess my Mom was right when she told me that I'm just a waste of bandwidth... :(|||What, did nobody pay attention during High School math classes?

select POWER(10, sum(log10(MyFloat))) from MyTable|||I have a harder time believing he has a freind...

:D

Just joking of course...|||Whee!!! The forum is working again!!! (I HEARD that, Brett...and a DOG and 3 tortoises still count as friends!)

Thanks for the suggestion, Blindman...I guess all that time and money was wasted in my math classes! :) Ya know, somehow, I just KNEW I shoulda paid more attention in my LOG class :D

However...(hey, you knew this was coming) that doesn't work, of course, for the negative numbers I've got in my "product" table...

I know a select can be written to check the sign of each number included in the calculation, but before I go and burn an excessive number of brain cells (or search keystrokes) anyone have a handy-dandy code block avialable for that part of the calculation?

It will, I am reasonably certain, require merely counting the number of negative numbers in the calculation and an ABS, like:
select POWER(10, sum(log10(ABS(MyFloat)))) from MyTable
Then somehow applying the sign ( ans * -1 for odd number of negative MyFloat entries in the calculation) to the result, but I am presently at a loss for how to apply the sign aspect of the problem.

Off to the races...

Thanks again!
Paul|||I imagine it would be as simple as:

select POWER(10, sum(log10(ABS(MyFloat))))*sign(MyFloat) from MyTable

My only problem with it is that if you have a 0 anywhere in the column, you could end up with problems, too. I think for simplicity sake, you should go with your first query. Of course, SQL 2005 is bound to disable Blindman's query that it was based on, since it has proven itself to be way too useful.|||OK, from what I have been able to ascertain (yep, just bought a new dictionary, thankyouverymuch! ;) ) the proposed addition of SIGN(MyFloat) won't work because that isn't (at least as written) an aggregate, so the compiler complains about it being invalid in the select list 'cause there is no GROUP BY nor is it in an aggregate.

Neither can it be added into the POWER(xxx) expression - because, again, POWER() apparently maintains a strong and lasting hatred of negative numbers - preferring to relegate them to nothingness rather than deal with them in a play-nice-in-the-sandbox way. But I digress...

What I needed was a way to apply the sign AFTER the PRODUCT-like operation.

What I found was several web hits that had logic for PRODUCT-type implementations in SQL that were NEAR what I wanted...and so...without further delay, here is my final product:

SELECT (EXP (SUM (LOG (CASE WHEN MyFloat = 0.00
THEN NULL
ELSE ABS(MyFloat) END))))
* (CASE WHEN (SUM (CASE WHEN SIGN(MyFloat) = -1
THEN 1
ELSE 0 END)) % 2 = 1
THEN -1.00
ELSE 1.00 END) AS Prod
FROM MyTable

The first CASE handles the situation of having NULL or Zero values in the table (which I simply want to ignore).

The second CASE applies the sign to the result, by keeping track of the NUMBER of negative values, and then multiplying the EXP(SUM(LOG())) overall result by -1 if the COUNT of negative values is odd, or by 1 if the COUNT is even.

I wish I could take credit for this, but alas, just for a workable b@.st@.rdization of the work of other folks ;)

I still am bothered by the complexity...I mean, if the calculation changes a year from now, I KNOW they are gonna send that Jr. Programmer to ask me about what the HEY I was thinking, or else just have him/her make changes without knowing what it does to start with (though I'm SURE that never happens ;) ).

The whole thing is further complicated because I (err...my "friend", I mean) have to perform some other calculations on the value returned from the above select...and if I was to use my original method, I'd have to make two passes through the table. That may be preferable to the relative complexity of this select...but I'll try it and see if my peers shoot me down in the code review ;)

Thanks guys!!! Great soundboarding!

Oh, and it REALLY started out to be the question of a co-worker working on the same project as I...it became mine when it took me too long to get back to her with a "I would do this..." email... :(|||Accounts for negative values and nulls:

------------------
set nocount on
declare @.PRODUCT table (Product varchar(30), Value float)

insert into @.Product (Product, Value) Values ('Coffee', -4.4)
insert into @.Product (Product, Value) Values ('Coffee', -5.5)
insert into @.Product (Product, Value) Values ('Coffee', 6.6)
insert into @.Product (Product, Value) Values ('Coffee', 7.7)
insert into @.Product (Product, Value) Values ('Tea', -44)
insert into @.Product (Product, Value) Values ('Tea', 55)
insert into @.Product (Product, Value) Values ('Tea', 66)
insert into @.Product (Product, Value) Values ('Tea', 77)
insert into @.Product (Product, Value) Values ('Water', -44)
insert into @.Product (Product, Value) Values ('Water', 55)
insert into @.Product (Product, Value) Values ('Water', 66)
insert into @.Product (Product, Value) Values ('Water', 77)
insert into @.Product (Product, Value) Values ('Water', 0)

select Product, EXP(sum(log(abs(nullif(Value, 0))))) *
(1+2*(cast(sum(sign(Value)-1)/2 as int) % 2)) * min(abs(sign(value)))
from @.PRODUCT
group by Product
------------------
This is truly mental mssql-bation...|||Hi, this is Paul's boss...thanks for exploding his head...

Now..WHO is going to cleanup this mess? ;)|||Well, if you'll check out my title I AM the Electronic Janitor.

Now where did I put my Electro-mop...?

Multiple Triggers Error: Nesting Level Exceeded (Limit 32). SQL 2005

I am running sql 2005 here is my issue I have 4 triggers on a table 2 inserts, and 2 updates, I can run the two inserts and only 1 update at one time or I get the Nesting Level 32 error. Each item was tested seperately and works fine also the group of 3 does exactly as it is suppose to, but the 2 update always triggers conflict with each other please if anyone has a suggestion im all ears.

Update Race:

IFNotUPDATE(Edited)

UPDATE Patient

SET Race =(SELECTCASE Racecode

WHEN'API'THEN'Asian, Pacific Islander'

WHEN'BH'THEN'Black, Hispanic'

WHEN'BNH'THEN'Black, Non-Hispanic'

WHEN'HIS'THEN'Hispanic'

WHEN'OTH'THEN'Other, Black & White'

WHEN'WH'THEN'White, Hispanic'

WHEN'WNH'THEN'White, Non-Hispanic'

END)

WHERE id# IN(SELECT id# FROM inserted)

END

Update Religion:

IF Not UPDATE (Edited)

Update Patient

SET Religion = (SELECT CASE Religioncode

WHEN 'C' THEN 'Catholic'

WHEN 'J' THEN 'Jewish'

WHEN 'O' THEN 'Other'

WHEN 'P' THEN 'Protestant'

WHEN 'U' THEN 'Unknown'

END)

WHERE id# IN (SELECT id# FROM Inserted)

END

why not combining them into one update.

IF Not UPDATE (Edited)
begin
UPDATE Patient
SET Race = (SELECT CASE Racecode
WHEN 'API' THEN 'Asian, Pacific Islander'
WHEN 'BH' THEN 'Black, Hispanic'
WHEN 'BNH' THEN 'Black, Non-Hispanic'
WHEN 'HIS' THEN 'Hispanic'
WHEN 'OTH' THEN 'Other, Black & White'
WHEN 'WH' THEN 'White, Hispanic'
WHEN 'WNH' THEN 'White, Non-Hispanic'
END)
,Religion = (SELECT CASE Religioncode
WHEN 'C' THEN 'Catholic'
WHEN 'J' THEN 'Jewish'
WHEN 'O' THEN 'Other'
WHEN 'P' THEN 'Protestant'
WHEN 'U' THEN 'Unknown'
END)
WHERE id# IN (SELECT id# FROM inserted)
end|||I am not really good at setting up my code thanks very much I have some classes coming up. That did the trick. Thanks again!!!

multiple triggers

How can I set it so one trigger runs before another?
I have two triggers on a table and I need for one to run before the other.
Also is there a good book I can get that will explain writing triggers?Am Mon, 5 Jun 2006 09:15:02 -0700 schrieb Cynthia:

> How can I set it so one trigger runs before another?
> I have two triggers on a table and I need for one to run before the other.
> Also is there a good book I can get that will explain writing triggers?
You can only define which should be the first and which the last, but you
have only two triggers, so this is no problem. Here the description:
http://msdn2.microsoft.com/en-us/library/ms186762.aspx
bye, Helmut|||See if this helps:
Specifying First and Last Triggers
http://msdn2.microsoft.com/en-us/library/ms189568.aspx
AMB
"Cynthia" wrote:

> How can I set it so one trigger runs before another?
> I have two triggers on a table and I need for one to run before the other.
> Also is there a good book I can get that will explain writing triggers?

Multiple text updates to a single row

I have a table A that has related records in table B. I need to run an update to concatonate certian values in table B into a single value in table A.

Since an UPDATE can't update the same row twice, is there any way I can do this other than use a Cursor?

No need to use cursor. simple update should be enough, but you need to watch out for the data types though. You should be able to use somthing like this.

Code Snippet

declare @.a table (x int, y varchar(200))

declare @.b table (x int, i int, j char(2), k bit)

insert @.a select 1, NULL

insert @.a select 2, NULL

insert @.a select 3, NULL

insert @.a select 4, NULL

insert @.a select 5, NULL

insert @.b select 1, 55, 'AB', 0

insert @.b select 2, 66, 'CD', 1

insert @.b select 3, 77, 'EF', 1

insert @.b select 4, 88, 'GH', 0

insert @.b select 5, 99, 'IJ', 1

update a

set a.y = cast (b.i as varchar(10)) + b.j + cast (b.k as varchar(1))

from @.a a join @.b b

on a.x = b.x

select * from @.a

|||

Thank you for the response. Unfortunately, this is not my situation. I have multiple related records in table B that relate back to single records in table A. I'll update your code example to reflect the problem I have.

Code Snippet

declare @.a table (x int, y varchar(200))

declare @.b table (x int, j char(2))

insert @.a select 1, NULL

insert @.a select 2, NULL

insert @.b select 1, 'AB'

insert @.b select 1, 'CD'

insert @.b select 1, 'EF'

insert @.b select 2, 'ZY'

insert @.b select 2, 'RX'

--The Following select will fail to return

--concatonated values in a.y because the

--UPDATE can't update the same row value

--twice in the same UPDATE statement.

update a

set a.y = ISNULL(a.y,'') + b.j

from @.a a join @.b b

on a.x = b.x

--column y only holds the first value for

--group 1 and group 2

select * from @.a

|||

I am sure, there will be better ways than this code, you can do something like this.

Code Snippet

declare @.a table (x int, y varchar(200))

declare @.b table (x int, j char(2))

declare @.temp table (x int)

declare @.x int

declare @.y varchar(200)

insert @.a select 1, NULL

insert @.a select 2, NULL

insert @.b select 1, 'AB'

insert @.b select 1, 'CD'

insert @.b select 1, 'EF'

insert @.b select 2, 'ZY'

insert @.b select 2, 'RX'

insert @.temp

select distinct x from @.a

while exists (select 1 from @.temp)

begin

select top 1 @.x = x from @.temp

select @.y = ISNULL(@.y, '') + j from @.b b where b.x = @.x

select @.y as 'the value'

update a

set y = ISNULL(y, '') + @.y

from @.a a

where a.x = @.x

delete from @.temp where x = @.x

set @.y = ''

end

select * from @.a

|||

Here is an old post that should help. Basically, you create a udf to concatenate the values.

http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/81da308504ac0e90

|||

Is this just for display? Or are you going to be storing the values like this? Best policy would be to use the UI to display the rows as you want. For display, you can use the techniques on the following page:

http://databases.aspfaq.com/general/how-do-i-concatenate-strings-from-a-column-into-a-single-row.html

But if you are doing this to store the values like this, it is a bad idea. Having each value in a different row is the best policy always. Far easier in SQL to build up a value than it is tear it apart.

set nocount on
declare @.a table (x int, y varchar(200))
declare @.b table (x int, j char(2))
insert @.a select 1, NULL
insert @.a select 2, NULL
insert @.b select 1, 'AB'
insert @.b select 1, 'CD'
insert @.b select 1, 'EF'
insert @.b select 2, 'ZY'
insert @.b select 2, 'RX'

update a
set y = LEFT(o.list, LEN(o.list)-1)
FROM @.a a
CROSS APPLY
(
SELECT
CONVERT(VARCHAR(12), j) + ',' AS [text()]
FROM
@.b s
WHERE
s.x = a.x
ORDER BY
x
FOR XML PATH('')
) o (list)

select *
from @.a

Returns:

x y

-- -

1 AB,CD,EF

2 ZY,RX

|||

First, thanks to Sankar and oj for your assistance.

Louis, your solution is what I was looking for. I do need to store the value, but it is for a very specific reason. I am finishing a table and stored procedure solution to delivering an open ended number of report subscription records to an SSIS package to output between 1 and 76 reports to PDF with a potentially different set of input parameters and values per client, per report.

Using the code above, I will be able to populate a single table structure with all report subscription records regardless of input parameters. I will concatenate the parameters as you illustrated above as "&parm1=val1&parm2=val2" while the next record may have "&parm5=value5" only. It allows me to run all filtered reports based on scheduling values (i.e. daily, weekly, monthly) while accounting for the differences in reports and client requirements.

I have not spent enough time exploring the XML functionality in SQL Server, and it bit me this time.

Thanks again for the help.

Hugh

|||

oj,

On second glance I used your UDF route. Made it easy to integrate into an existing sql insert without adding any more code to my stored procedure.

Thanks.

Multiple text updates to a single row

I have a table A that has related records in table B. I need to run an update to concatonate certian values in table B into a single value in table A.

Since an UPDATE can't update the same row twice, is there any way I can do this other than use a Cursor?

No need to use cursor. simple update should be enough, but you need to watch out for the data types though. You should be able to use somthing like this.

Code Snippet

declare @.a table (x int, y varchar(200))

declare @.b table (x int, i int, j char(2), k bit)

insert @.a select 1, NULL

insert @.a select 2, NULL

insert @.a select 3, NULL

insert @.a select 4, NULL

insert @.a select 5, NULL

insert @.b select 1, 55, 'AB', 0

insert @.b select 2, 66, 'CD', 1

insert @.b select 3, 77, 'EF', 1

insert @.b select 4, 88, 'GH', 0

insert @.b select 5, 99, 'IJ', 1

update a

set a.y = cast (b.i as varchar(10)) + b.j + cast (b.k as varchar(1))

from @.a a join @.b b

on a.x = b.x

select * from @.a

|||

Thank you for the response. Unfortunately, this is not my situation. I have multiple related records in table B that relate back to single records in table A. I'll update your code example to reflect the problem I have.

Code Snippet

declare @.a table (x int, y varchar(200))

declare @.b table (x int, j char(2))

insert @.a select 1, NULL

insert @.a select 2, NULL

insert @.b select 1, 'AB'

insert @.b select 1, 'CD'

insert @.b select 1, 'EF'

insert @.b select 2, 'ZY'

insert @.b select 2, 'RX'

--The Following select will fail to return

--concatonated values in a.y because the

--UPDATE can't update the same row value

--twice in the same UPDATE statement.

update a

set a.y = ISNULL(a.y,'') + b.j

from @.a a join @.b b

on a.x = b.x

--column y only holds the first value for

--group 1 and group 2

select * from @.a

|||

I am sure, there will be better ways than this code, you can do something like this.

Code Snippet

declare @.a table (x int, y varchar(200))

declare @.b table (x int, j char(2))

declare @.temp table (x int)

declare @.x int

declare @.y varchar(200)

insert @.a select 1, NULL

insert @.a select 2, NULL

insert @.b select 1, 'AB'

insert @.b select 1, 'CD'

insert @.b select 1, 'EF'

insert @.b select 2, 'ZY'

insert @.b select 2, 'RX'

insert @.temp

select distinct x from @.a

while exists (select 1 from @.temp)

begin

select top 1 @.x = x from @.temp

select @.y = ISNULL(@.y, '') + j from @.b b where b.x = @.x

select @.y as 'the value'

update a

set y = ISNULL(y, '') + @.y

from @.a a

where a.x = @.x

delete from @.temp where x = @.x

set @.y = ''

end

select * from @.a

|||

Here is an old post that should help. Basically, you create a udf to concatenate the values.

http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/81da308504ac0e90

|||

Is this just for display? Or are you going to be storing the values like this? Best policy would be to use the UI to display the rows as you want. For display, you can use the techniques on the following page:

http://databases.aspfaq.com/general/how-do-i-concatenate-strings-from-a-column-into-a-single-row.html

But if you are doing this to store the values like this, it is a bad idea. Having each value in a different row is the best policy always. Far easier in SQL to build up a value than it is tear it apart.

set nocount on
declare @.a table (x int, y varchar(200))
declare @.b table (x int, j char(2))
insert @.a select 1, NULL
insert @.a select 2, NULL
insert @.b select 1, 'AB'
insert @.b select 1, 'CD'
insert @.b select 1, 'EF'
insert @.b select 2, 'ZY'
insert @.b select 2, 'RX'

update a
set y = LEFT(o.list, LEN(o.list)-1)
FROM @.a a
CROSS APPLY
(
SELECT
CONVERT(VARCHAR(12), j) + ',' AS [text()]
FROM
@.b s
WHERE
s.x = a.x
ORDER BY
x
FOR XML PATH('')
) o (list)

select *
from @.a

Returns:

x y

-- -

1 AB,CD,EF

2 ZY,RX

|||

First, thanks to Sankar and oj for your assistance.

Louis, your solution is what I was looking for. I do need to store the value, but it is for a very specific reason. I am finishing a table and stored procedure solution to delivering an open ended number of report subscription records to an SSIS package to output between 1 and 76 reports to PDF with a potentially different set of input parameters and values per client, per report.

Using the code above, I will be able to populate a single table structure with all report subscription records regardless of input parameters. I will concatenate the parameters as you illustrated above as "&parm1=val1&parm2=val2" while the next record may have "&parm5=value5" only. It allows me to run all filtered reports based on scheduling values (i.e. daily, weekly, monthly) while accounting for the differences in reports and client requirements.

I have not spent enough time exploring the XML functionality in SQL Server, and it bit me this time.

Thanks again for the help.

Hugh

|||

oj,

On second glance I used your UDF route. Made it easy to integrate into an existing sql insert without adding any more code to my stored procedure.

Thanks.

Multiple Tables

Hi,
I am using a SProc to run a report, is it possible to have 2 Tables in one
report which will display different columns from the dataset ?
ThanksYes. You can use the same dataset multiple times. For instance, you could
apply different filters to it, have a table and have a graph etc. What you
cannot do is have the sproc return two resultsets.
Also, you can have multiple datasets as well.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Vishal" <vrajput77@.hotmail.com> wrote in message
news:Od3rYTACGHA.740@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I am using a SProc to run a report, is it possible to have 2 Tables in one
> report which will display different columns from the dataset ?
> Thanks
>

Multiple SuccessValues for ExecuteProcess task?

I have an execute process task to run Red-Gate's SqlDataCompare synchronization. The normal exit code is 0 indicating successful synchronization. However, if the tables are already identical and require no synchronization the process exit code is 63 and the task fails. I do not want the task to fail if the tables are identical, but it seems I can only specify a single value in the task's SuccessValue property. I tried separating values with a comma, e.g. 0,63. Any suggestions?

You could use the ForceExecutionResult property to ensure the task succeeds.

Donald

|||Thanks Donald. I was avoiding having to force the result since I want the task to fail if the exit code is other than 0 or 63. Do you think in a future version we might be able to specify multiple success values? (I entered it in connect.microsoft.com/SQLServer/Feedback).

multiple SQL commands at once

I'm running asp.net 2.0 and acessing MSSQL 2K. I am trying to run a query in which I need to set up variables first. I tried the following group of commands as shown, passing it to the SqlDataReader object, but it failed.. does anyone know how i can pass multiple SQL commands?

MainQuery = "declare @.MinGrades as Table(GradeID Bigint) " & _
" INSERT @.MinGrades SELECT MIN(CreditGrades.SplitID) AS Expr1" & _
" FROM CreditGrades INNER JOIN" & _
" CreditGradeSplits ON CreditGradeSplits.CreditGradeSplitID = CreditGrades.SplitID " & _
" WHERE (CreditGrades.x0x30 = - 1 OR " & _
" CreditGrades.x0x30 >= 0) AND (CreditGrades.x1x30 = - 1 OR " & _
" CreditGrades.x1x30 >= 0) AND (CreditGrades.x2x30 = - 1 OR " & _
" CreditGrades.x2x30 >= 0) AND (CreditGrades.x3x30 = - 1 OR " & _
" CreditGrades.x3xNOD >= 0) " & _
" GROUP BY CreditGradeSplits.CreditGradeGroupID" & _
" Select * from @.MinGrades "Looks good to me. What error are you getting? Please copy/paste the stack trace if possible as well.|||

Looks good to me. What error are you getting? Please copy/paste the stack trace if possible as well.

I suspect you realize that you don't really need to do that query in 3 commands, and can combine them into a single select.