Field not being updated within Stored Procedure

I have this stored procedure that loops through a table and updates a
couple of fields. For some reason one of the fields is not being
updated. If I run the same code from query analyzer, it works fine.
Let me know if anyone can figure out why @.lastscandate would ever be
NULL. If it is null it should be equal to @.maildate. The senerio that
seems to fail is when no records are returned from the select statement
to fill in @.lastscandate. This should then active the next if
statement and set the @.lastscandate equal to the @.maildate. MailDate
is always filled in in the database and LastScanDate will be NULL.

Thanks for your help.

DECLARE c1 CURSOR LOCAL FOR
SELECT m.id, m.acctno, m.ordid, m.cycle FROM master m WITH (nolock)
WHERE m.printstatus IN ('ST', 'ML') AND (m.batchid IS NULL OR m.batchid
= 0) AND (m.maildate ='' OR m.maildate IS NULL)
AND NOT EXISTS(SELECT * FROM packagemaster p WITH (nolock)
WHERE m.acctno = p.acctno AND m.ordid = p.ordid AND m.cycle = p.cycle
AND p.status NOT IN ('BM', 'PM'))

OPEN c1
FETCH FROM c1 INTO @.mid, @.acctno, @.ordid, @.cycle

WHILE @.@.fetch_status = 0
BEGIN

--Get MailDate from Manifest - if NULL then use GetDate
set @.maildate = NULL
SELECT @.maildate = MAX(whenmailed) FROM manifest WITH (nolock)
WHERE acctno = @.acctno AND ordid = @.ordid AND cycle = @.cycle
if @.maildate is NULL
set @.maildate = getdate()

--Get Last Scan Date from Transactions - if NULL then use MailDate
set @.lastscandate = NULL
select @.lastscandate=max(actiondate) from transactions where
acctno=@.acctno and ordid=@.ordid and cycle=@.cycle and actionid=303
if @.lastscandate is NULL
set @.lastscandate = @.maildate

BEGIN TRANSACTION
UPDATE master SET printstatus = 'ML', maildate = @.maildate,
lastscandate=@.lastscandate
WHERE id = @.mid

INSERT INTO transactions (initials, actionid, machinelogin, acctno,
ordid, cycle, program) VALUES ('RLT', 55, 'Mars', @.acctno, @.ordid,
@.cycle, 'Update Mail Dates')
COMMIT TRANSACTION

FETCH NEXT FROM c1 INTO @.mid, @.acctno, @.ordid, @.cycle

END

CLOSE c1[posted and mailed, please reply in news]

AS400 Guru (hazen@.candid.com) writes:
> I have this stored procedure that loops through a table and updates a
> couple of fields. For some reason one of the fields is not being
> updated. If I run the same code from query analyzer, it works fine.
> Let me know if anyone can figure out why @.lastscandate would ever be
> NULL. If it is null it should be equal to @.maildate. The senerio that
> seems to fail is when no records are returned from the select statement
> to fill in @.lastscandate. This should then active the next if
> statement and set the @.lastscandate equal to the @.maildate. MailDate
> is always filled in in the database and LastScanDate will be NULL.

I don't immediately see why, but I don't have the tables, so it's
difficult to debug. Since you insert into transactions and read from
it, in the same cursor, there could be some funny things.

However, I would suggest that you should rewrite as a one UPDATE
statment and one INSERT Statement. For simplicty I use a temp table
though:

INSERT #temp (...)
SELECT m.id, m.acctno, m.ordid, m.cycle
FROM master m WITH (nolock)
WHERE m.printstatus IN ('ST', 'ML')
AND (m.batchid IS NULL OR m.batchid >= 0)
AND (m.maildate ='' OR m.maildate IS NULL)
AND NOT EXISTS(SELECT * FROM packagemaster p WITH (nolock)
WHERE m.acctno = p.acctno
AND m.ordid = p.ordid
AND m.cycle = p.cycle
AND p.status NOT IN ('BM', 'PM'))

UPDATE master
SET printstatus = 'ML',
maildate = coalesce(mf.whenmailed, getdate(),
lastscandate = coalesce(tr.actiondate, mf.whenmailed, getdate())
FROM #temp t
JOIN master ma ON t.mid = ma.mid
JOIN (SELECT accno, ordid, cycle, whenmailed = MAX(whenmailed)
FROM manifest
GROUP BY accno, ordid, cycle) mf ON mf.ordid = t.ordid
AND mf.accntno = t.acctno
AND mf.cycle = t.cycle
JOIN (SELECT accno, ordid, cycle, actiondate = MAX(actiondate)
FROM transactions
WHERE actionid = 303
GROUP BY accno, ordid, cycle) tr ON tr.ordid = t.ordid
AND tr.accntno = t.acctno
AND tr.cycle = t.cycle

INSERT INTO transactions (initials, actionid, machinelogin, acctno,
ordid, cycle, program)
SELECT 'RLT', 55, 'Mars', acctno, ordid, cycle, 'Update Mail Dates'
FROM #temp

COMMIT TRANSACTION

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

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

Field name as a parameter to a stored procedure?

Hi,
Is it possible to pass a field name as a parameter to a stored procedure?
The user wants to select either Dollar or Euro in the UI. The field I want
to be variable is a float & is used in a simple calculation...
(a.A_Tot_Rev_For / e.USDollar)
So I want e.USDollar to be able to change to e.Euro for example when my
report needs euro values.
thanks in advance,
jpi think a UDF is a better option

Field name as a Parameter

Hi,

I'm trying to figure out how to pass a field name into a procedure so the procedure can selectively find out the value of different fields. The DB table I'm interested in has multiple bool fields in it and the field name is the parameter I want to pass into the procedure. With the example code I list below, there is only one row that exists in the table so the end result should be dependent upon the value in the field. The calling procedure is "attempting" to pass in the name of the field, and the called procedure should use the paramter passed in as the field to return in the table. If the field returned in the called procedure is true, the called procedure returns "0" to the calling procedure, otherwise it returns 1. The syntax I have doesn't seem to work in that the called procedure always returns true from the field and the calling procedure always gets 0 back from the called procedure.

In the calling procedure, I'm doing a select just to find out if a 0 or 1 was returned, but this will not be the final version of the code, its just for test purposes.

Any help on what I'm doing wrong greatly appriciated.

- Bruce

ALTER PROCEDURE [dbo].[caller]

AS

BEGIN

SET NOCOUNT ON;

DECLARE @.include int,

@.Committee bit

execute @.include = dbo.callee @.Committee

IF @.include = 0

BEGIN

select * from ProvCompensation

END

ELSE

BEGIN

select * from ProvCommittee

END

END

ALTER PROCEDURE [dbo].[callee]

(@.fName bit)

AS

BEGIN

DECLARE @.aBool bit

SET NOCOUNT ON;

select @.aBool = @.fName from ProvisionsPlanSum where PlanId = -99

IF @.aBool = 'true'

BEGIN

RETURN (0)

END

ELSE

BEGIN

RETURN (1)

END

END

Generally speaking, parameterizing a column (or table for that matter) is not considered the "right" way to build SQL Server objects. But, if you really need to, you can use dynamic sql, and just execute it like:

declare @.aBool bit
declare @.query nvarchar(max)
declare @.fName varchar(10)
set @.fName = 'someCol'

set @.query = 'select @.aBool = ' + @.fName + ' from ProvisionsPlanSum where PlanId = -99'

exec sp_executeSQL
@.query, N'@.aBool bit output', @.aBool= @.aBool output

select @.aBool

Not 100% sure if this is perfect. I do know that this will work, if you need an example:

declare @.objectId int

exec sp_executeSQL
N'select @.objectId = max(object_id) from sys.objects',
N'@.objectId int output', @.objectId=@.objectId output
select @.objectId

|||

This will also work :)

declare @.aBool bit
declare @.query nvarchar(1000)
declare @.fName varchar(30)
set @.fName = '1; DROP TABLE Test12; --'

set @.query = 'select @.aBool = ' + @.fName + ' from ProvisionsPlanSum where PlanId = -99'

EXEC sp_executeSQL
@.query, N'@.aBool bit output', @.aBool= @.aBool OUTPUT

SELECT @.aBool

|||

Hey guys,

Thanks for the help. I'll give it a go sometime today.

... worked like poop through a goose, thanks guys!

- Bruce

Field list is empty when calling stored procedure

Hello!
I am having problems retrieving list of fields when using stored
procedure as Dataset Source. I noticed that Reporting Services can retrieve
field list if stored procedure is trivial (simple select etc.). In my case,
I am insrting into temporary table and after some manipulations return
resultset. SQL Trace show that RS tries to execute SET FMTONLY ON <sp_name>
SET FMTONLY OFF when reading list of the fiels. This returns empty result
for my stored procedure.
I can not move any further without field list being populated properly. I
can try to replace stored procedure with the view etc. but I would really
like to know if someone experienced similar problems.
Any help is greately appreciated,
IgorIgor,
I have experienced that same behavior. The stored procedure will run but
I will not get a field list. I received the error that told me to hit
REFRESH to get the fields list ... I returned to the data tab, ran the
procedure and hit the refresh button ... then when I returned to layout tab
... the fields list was there. Otherwise I have read in books that you have
to type in your own fields list which to me was not a very acceptable answer
as my fields list was quite extensive. Hope that helps you!
MJ
"imarchenko" wrote:
> Hello!
> I am having problems retrieving list of fields when using stored
> procedure as Dataset Source. I noticed that Reporting Services can retrieve
> field list if stored procedure is trivial (simple select etc.). In my case,
> I am insrting into temporary table and after some manipulations return
> resultset. SQL Trace show that RS tries to execute SET FMTONLY ON <sp_name>
> SET FMTONLY OFF when reading list of the fiels. This returns empty result
> for my stored procedure.
> I can not move any further without field list being populated properly. I
> can try to replace stored procedure with the view etc. but I would really
> like to know if someone experienced similar problems.
> Any help is greately appreciated,
> Igor
>
>

Field List from a Stored procedure

I am writing a utility that creates Java code to access a database. I am looking for a way to get a list of fields and types that are returned by an sproc. Is there any easy way to get this from the master? Do you need to parse the SQL? This list would be like what Visual Studio.NET shows, or interdev if I remember correctly.

Thanks,

Larrynot sure what exactly you looking for. try sp_helptext sproc_name|||I am looking for a way to get a list of the columns that will be in the recordset returned by a stored procedure. I need column name and type information.

sp_helptext seems to return the full contents of the sproc, which would require that I parse this, and any linked procedures, to get the info I am looking for. This is what I am trying to avoid.|||Do your procedures do SELECT only? You can use sp_depends and retrieve the structures of all dependent tables, but this will also include fields that your procedure does not include. It'll be easier to go after views.|||The procedures I am interested in do selects only. But they pull from other procedures and views.|||I am looking for something like that as well. So far I am using sp_sproc_columns to fetch the list of params from a SQL Server stored procedure.

There's nothing that I've found that will return a vertical list of outputs though. Ultimately I would like to display the fields returned by the sproc and their data types. Hopefully I won't have to re-invent the wheel by having to parse the sproc text.

I'll post back if I find anything else.

Thanks,

Doug

Fetching data from a Flat file in SQL Server2000

Posted - 11/03/2006 : 08:00:37 AM
----
Hi,
I am new to SQL Server 2000.
I have to write a stored procedure that fetches some of the fields from
two flat files.
Here is my requirement...
There is a flat file by name "File1", Here i have to Fetch all the
Document ids (first 20 characters) from this file.
Then join this file using trim(Document id) with the below file's
Document id (column 17 to 28) to get the following fields:
"File2" - fetch the following fields (by position)
Memberid (column 899 to 908)
Received Date (column 35 to 42)
Verifier (column 51 to 55)
Join the above result set with the following
condition(Enrollkeys.carriermemid = ResultSet.Memberid) to get the
following fields:
(where result set is the result obtained by joining the flat files)
Sent (loistatus.loisentdate)
Unit (eligibilityorg.univid)
where Enrollkeys, loistatus, eligibilityorg are the tables in the
datase..
Actually I solved this issue using DTS package, but my DBA is not
allowing me to use DTS package.
He is telling me to Try using Stored Procedure to access the flat files
and getting the values.
And also I should not Use any execute or DDL statement inside the
Stored procedure.
So how can i proceed..
Please reply ASAP..One solution is that you can use openrowset for accessing directly the flat
file in your select query itself.
Amarnath
"Praveen" wrote:
> Posted - 11/03/2006 : 08:00:37 AM
>
> ----
> Hi,
> I am new to SQL Server 2000.
> I have to write a stored procedure that fetches some of the fields from
> two flat files.
> Here is my requirement...
> There is a flat file by name "File1", Here i have to Fetch all the
> Document ids (first 20 characters) from this file.
> Then join this file using trim(Document id) with the below file's
> Document id (column 17 to 28) to get the following fields:
> "File2" - fetch the following fields (by position)
> Memberid (column 899 to 908)
> Received Date (column 35 to 42)
> Verifier (column 51 to 55)
> Join the above result set with the following
> condition(Enrollkeys.carriermemid = ResultSet.Memberid) to get the
> following fields:
> (where result set is the result obtained by joining the flat files)
> Sent (loistatus.loisentdate)
> Unit (eligibilityorg.univid)
> where Enrollkeys, loistatus, eligibilityorg are the tables in the
> datase..
> Actually I solved this issue using DTS package, but my DBA is not
> allowing me to use DTS package.
> He is telling me to Try using Stored Procedure to access the flat files
> and getting the values.
> And also I should not Use any execute or DDL statement inside the
> Stored procedure.
> So how can i proceed..
> Please reply ASAP..
>

Fetch question

i am having an endless loop after the first record... anything i missed?

ALTER PROCEDURE IMPGrpEscalationX

AS

Declare @.IMID int
Declare @.IMID2 int
Declare @.FS1 int
Declare @.FS2 int

Declare crFirst cursor local for
select IMID from ImplementationGroup where IMSTatus = 'Y'
open crFirst
fetch next from crFirst
into
@.IMID

begin
Declare crSecond cursor local for
select IMID from Employees_ImplementationGroup where Employees_ImplementationGroup.IMID = @.IMID
open crSecond
fetch next from crSecond
into
@.IMID2
set @.FS2 = @.@.fetch_status
if not exists(select IMID from Employees_ImplementationGroup where IMID = @.IMID2 and IMType = 'T')
Begin
DECLARE @.MsgText varchar(700)
DECLARE @.IMGRPNAME varchar(50)
Set @.IMGRPNAME = (select IMGrpname from ImplementationGroup where IMID = @.IMID2)
--SET @.MsgText = 'This implementation group has been without a last resort implementer for the past 24 hours. Please click here to assign a last resort implementer: http://xxxx.com/admin/implementationgrp_emps.aspx?imid=' + @.IMID2 + '&imgrpname=' + @.IMGrpName

EXEC master.dbo.xp_sendmail
@.recipients = cccc@.ccc.com',
@.Message = @.IMGRPNAME,
@.Subject = 'needs attention'

end
while @.FS2 = 0
fetch next from crSecond
into
@.IMID2
end
set @.FS1 = @.@.fetch_status
while @.FS1 = 0

FETCH NEXT FROM crFirst
into
@.IMID

close crFirst
deallocate crFirst
close crSecond
deallocate crSecondi fixed this one... second fetch was outside the scope...sql

Fetch Question

I have a stored procedure that inserts one row into a table. From a
SELECT statement I would like to call the SP on each row in the
results. Is setting up a cursor and using fetch statements the best
way (or even the only way) to do this?Hi

It is probably the safest way to do it, but without knowing exactly what you
are trying to do it is hard to suggest alternatives.

John

"Jason" <JayCallas@.hotmail.com> wrote in message
news:f01a7c89.0310010701.261cbff2@.posting.google.c om...
> I have a stored procedure that inserts one row into a table. From a
> SELECT statement I would like to call the SP on each row in the
> results. Is setting up a cursor and using fetch statements the best
> way (or even the only way) to do this?

Fetch Loop stored procedure

What is wrong with this stored procedure? This should work right?

Create PROCEDURE UpdRequestRecordFwd
@.oldITIDint ,
@.newITID int
AS
Declare @.RRID int
Declare @.APID int
Declare crReqRec cursor for
select RRID from RequestRecords where ITID = @.oldITID
open crReqRec
fetch next from crReqRec
into
@.RRID
while @.@.fetch_status = 0
Begin
Update RequestRecords
set ITID = @.newITID
where RRID = @.RRID
FETCH NEXT FROM crReqRec
into
@.RRID
end
close crReqRec
deallocate crReqRec
GO

(1) what is the error you get ? its hard toguess unless you give us complete info
(2) for what you are doing above you dont seem to need a cursor.

Update RequestRecords set ITID = @.newITID where RRID = @.RRID

would work just fine without any cursor..unless you are doing other stuff in between.

either way, you need to give more details.

hth|||(1) I am not getting any error. It's just won't update
(2) I am have multiple RRID's (unique primary key) with one ITID. I want all the RRID's with this ITID to be replaced with the new ITID returned.

I removed the fetch as you suggested... still doesn't work..

Create PROCEDURE UpdRequestRecordFwd

@.oldITIDint ,
@.newITID int

AS
Declare @.RRID int

set @.RRID = (select RRID from RequestRecords where ITID = @.oldITID and RRStatus = 'IA' and APID is null)

Update RequestRecords
set ITID = @.newITID
where RRID = @.RRID

Go|||if you just need to update ITID's then how do the RRID's come into picture...
see if this helps :

Create PROCEDURE UpdRequestRecordFwd
@.oldITID int ,
@.newITID int
AS
Update RequestRecords set ITID = @.newITID where ITID = @.oldITID
Go

hth|||I do appreciate your help but it was my mistake, permission to execute was not checked. Thank you for your time.

Fetch cursor help

Let's say i have 5 unique RRID's, column APID and ITID

RRID - APID - ITID
1 13 700
2 13 700
3 13 700
4 14 700
5 15 700

If I run the stored procedure below, I get the results above however, I want my result to be

RRID - APID - ITID
1 13 700
2 13 700
3 13 700
4 14 701
5 15 702

I want my cursor to loop at the same APID then assign one ITID then move to the next APID and so on...

Any help is highly appreciated...

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
ALTER PROCEDURE InsNewEmployeeImpTaskP2
@.REID int,
@.LOID int,
@.RetValintoutput
AS
Declare @.RRID int
Declare @.APID int
Declare @.intREID varchar(20)
Declare @.intIMID varchar(20)
Declare crReqRec cursor for
select RRID from RequestRecords where REID = @.REID and RRSTatus = 'AC' and APID is not null
open crReqRec
fetch next from crReqRec
into
@.RRID
set @.APID = (select APID from RequestRecords where REID = @.REID and RRID = @.RRID)
set @.intIMID = (SELECT ImplementationGroup.IMID
FROM ImplementationGroup_Location INNER JOIN
 ImplementationGroup ON ImplementationGroup_Location.IMID = ImplementationGroup.IMID INNER JOIN
 Applications_ImplementationGroup ON ImplementationGroup.IMID = Applications_ImplementationGroup.IMID where APID = @.APID and ImplementationGroup_Location.LOID = @.LOID )
insert into ImplementationTasks
(
IMID,
ITStatus,
ITStatusDate
)
VALUES
(
@.intIMID,
'2',
GetDate()
)
SET @.RetVal = @.@.Identity
while @.@.fetch_status = 0
Begin
Update RequestRecords
set ITID = @.RETVal, RRStatus = 'IA'
where REID = @.REID and RRID = @.RRID
FETCH NEXT FROM crReqRec
into
@.RRID
end
close crReqRec
deallocate crReqRec
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

This is the newer version but still getting the same results. PLEASE HELP...

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
ALTER PROCEDURE InsNewEmployeeImpTaskP2
@.REID int,
@.LOID int,
@.RetValintoutput

AS

Declare @.RRID int
Declare @.APID int
Declare @.APID2 int
Declare @.FS1 int
Declare @.FS2 int
Declare @.intREID varchar(20)
Declare @.intIMID varchar(20)

Declare crReqRec cursor local for
select RRID from RequestRecords where REID = @.REID and RRSTatus = 'AC' and APID is not null

open crReqRec
fetch next from crReqRec into @.RRID
set @.FS1 = @.@.fetch_status

Declare crAPID cursor local for
select APID from RequestRecords where REID = @.REID and RRID = @.RRID
open crAPID
fetch next from crAPID into @.APID2
set @.FS2 = @.@.fetch_status
set @.APID2 = (select APID from RequestRecords where APID = @.APID)
set @.intIMID = (SELECT ImplementationGroup.IMID FROM ImplementationGroup_Location INNER JOIN
ImplementationGroup ON ImplementationGroup_Location.IMID = ImplementationGroup.IMID INNER JOIN
Applications_ImplementationGroup ON ImplementationGroup.IMID = Applications_ImplementationGroup.IMID where APID = @.APID2 and ImplementationGroup_Location.LOID = @.LOID )

insert into ImplementationTasks
(IMID, ITStatus,ITStatusDate) VALUES (@.intIMID,'2',GetDate())
SET @.RetVal = @.@.Identity

while @.FS2 = 0
while @.FS1 = 0

Begin
Update RequestRecords
set ITID = @.RETVal, RRStatus = 'IA'
where REID = @.REID and RRID = @.RRID

fetch next from crAPID into @.APID2
FETCH NEXT FROM crReqRec into @.RRID
end

close crReqRec
deallocate crReqRec
close crAPID
deallocate crAPID

--EXEC TrigRetReqRecIDP2 @.REID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GOsql

Feeding selections into a stored procedure

Hello All,

I'm not really sure where to post this as I'm not quite sure how to approach the problem; either whether it's an SQL problem or shoudl be addressed on the form.

Anyway, on an .aspx page (using VB.NET) I have a drop down box and two text boxes. The user first selects a centre from the drop down and then enters a start and end date in the two text boxes. The user then clicks on a button which lists the results of a SQL server stored procedure using the specified parameters in a datagrid. All this is fine and works.

Users however have requested an 'All centers' option in the drop down, which if selected, essentially means that instead of listing the results for a single centre, all centres are listed for the start and end dates specified.

This is where I'm having problems. How do I feed this into my stored procedure? Is it a change in the procedure or something I need to do on the form?

Any help appreciated.

Mo

P.S. stored procedure look like this:

------

@.centreid int,
@.startdate varchar(20),
@.enddate varchar(20)

AS

SELECT centreid, datecreated, centrename

FROM tblcentres

WHERE (DataLength(@.startdate) = 0 OR CentreID = @.CentreID)
AND (DataLength(@.startdate) = 0 OR datecreated >= @.startdate)
AND (DataLength(@.enddate) = 0 OR datecreated <= @.enddate)

I would pass a 0 or some other number like 9999 which is not in the list of centerId's and do a check in the stored proc. If its the 0 or 9999 you passed remove that from the where condition. Something like this:

IF @.centerId = 0SELECT centreid, datecreated, centrenameFROM tblcentresWHERE (DataLength(@.startdate) = 0OR datecreated >= @.startdate)AND (DataLength(@.enddate) = 0OR datecreated <= @.enddate)ELSE SELECT centreid, datecreated, centrenameFROM tblcentresWHERE (DataLength(@.startdate) = 0OR CentreID = @.CentreID)AND (DataLength(@.startdate) = 0OR datecreated >= @.startdate)AND (DataLength(@.enddate) = 0OR datecreated <= @.enddate)

|||

Is this part right:

WHERE (DataLength(@.startdate) = 0 OR CentreID = @.CentreID)?

Seems like you had the right idea for startdate/enddate, but you didn't implement it correctly for CentreID. You could change it to:

WHERE (@.CentreID=-1 OR CentreID = @.CentreID) then have the "All Centres" value -1. Or you can set the options value to a blank, have the sqldatasource convert blanks to nulls, and check for IS NULL. Any of those work.

I personally prefer to use the blank/null approach as most of the dropdown values I use are values from an identity column, and the field can not possibly be blank (It's marked not null). If it's not from an identity column, and is some kind of varchar field, then I'll simply use "All" as the value, and if someone put that into the database and they want to specifically search for it, they are sol.

|||Good catch Motley. I just cut n paste'ed the OP's query to give him/her an idea of how to approach the problem.|||Thanks to both of you for the advice and also for correcting the errors in my SQL.

Your suggestion(s) worked perfectly!

Mo

feeding results from one SP to anohter

Hi,
I need an SP to get the resultset output of another SP.
ie
create Procedure A
as
begin
select * from Area
end
Now in procedure B, how can I get these results into a cursor form processin
g?
Also, now to through another slant on it, what if Procedure A is in a remote
database, does the same logic apply?
Thanks,
Steve
(similar to a previous post today - sorry)One way to do would be to use global temporary tables (## prefix).
But this is more like a procedural programming approach which is more
suitable for client side as opposed to database side.
Could you please elaborate more on what you are trying to do.
Here is a sample code.
HTH...
use pubs
go
-- ========================================
=====
-- Create procedure basic template
-- ========================================
=====
-- creating the store procedure
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'mytestprocA'
AND type = 'P')
DROP PROCEDURE dbo.mytestprocA
GO
CREATE PROCEDURE dbo.mytestprocA
AS
drop table ##global_temp
select top 5 * into ##global_temp from authors
GO
-- ========================================
=====
-- example to execute the store procedure
-- ========================================
=====
EXECUTE dbo.mytestprocA
GO
use pubs
go
-- ========================================
=====
-- Create procedure basic template
-- ========================================
=====
-- creating the store procedure
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'mytestprocB'
AND type = 'P')
DROP PROCEDURE dbo.mytestprocB
GO
CREATE PROCEDURE dbo.mytestprocB
AS
DECLARE temp_Cursor CURSOR FOR
select * from ##global_temp
OPEN temp_Cursor
FETCH NEXT FROM temp_Cursor
WHILE @.@.FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM temp_Cursor
END
CLOSE temp_Cursor
DEALLOCATE temp_Cursor
GO
-- ========================================
=====
-- example to execute the store procedure
-- ========================================
=====
EXECUTE dbo.mytestprocB
GO
http://zulfiqar.typepad.com
BSEE, MCP
"Steve" wrote:

> Hi,
> I need an SP to get the resultset output of another SP.
> ie
> create Procedure A
> as
> begin
> select * from Area
> end
> Now in procedure B, how can I get these results into a cursor form process
ing?
> Also, now to through another slant on it, what if Procedure A is in a remo
te
> database, does the same logic apply?
> Thanks,
> Steve
> (similar to a previous post today - sorry)
>|||Best way to do this is change proc A to a function.
2nd best:
insert #table
Exec A
I would also ask what you are using the cursor for, but that is only because
cursors are evil :) Seriously most every use for a cursor has a far easier
to manage set based solution, and that is what we are here to help you with.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Steve" <Steve@.discussions.microsoft.com> wrote in message
news:8BA898FD-43B9-401C-83DB-BD314A2EAA19@.microsoft.com...
> Hi,
> I need an SP to get the resultset output of another SP.
> ie
> create Procedure A
> as
> begin
> select * from Area
> end
> Now in procedure B, how can I get these results into a cursor form
> processing?
> Also, now to through another slant on it, what if Procedure A is in a
> remote
> database, does the same logic apply?
> Thanks,
> Steve
> (similar to a previous post today - sorry)
>

Feed stored procedure with SELECT resultset

I have two SQL Server stored procedures, PROC1 and PROC2. PROC1 has
about 50 input parameters. PROC2 is the main procedure that does some
data modifications and afterwards calls PROC1 using an EXECUTE
statement.

The input parameter values for PROC1 are stored in a table in my
database. What I like to do is passing those values to PROC1 using a
SELECT statement. Currently, all 50 parameters are read and stored in
a variable, and afterwards they are passed to PROC1 using:

EXEC spPROC1 @.var1, @.var2, @.var3, ... , @.var50

Since it is a lot of code declaring and assigning 50 variables, I was
wondering if there is a possibility to run a statement like:

EXEC spPROC1 (SELECT * FROM myTable WHERE id = 2)

Any help on this is greatly appreciated!On 21 Oct 2004 07:19:02 -0700, Dieter Gasser wrote:

> I have two SQL Server stored procedures, PROC1 and PROC2. PROC1 has
> about 50 input parameters. PROC2 is the main procedure that does some
> data modifications and afterwards calls PROC1 using an EXECUTE
> statement.
> The input parameter values for PROC1 are stored in a table in my
> database. What I like to do is passing those values to PROC1 using a
> SELECT statement. Currently, all 50 parameters are read and stored in
> a variable, and afterwards they are passed to PROC1 using:
> EXEC spPROC1 @.var1, @.var2, @.var3, ... , @.var50
> Since it is a lot of code declaring and assigning 50 variables, I was
> wondering if there is a possibility to run a statement like:
> EXEC spPROC1 (SELECT * FROM myTable WHERE id = 2)
> Any help on this is greatly appreciated!

You could build up a dynamic SQL statement and execute it that way, but I
think it would be vastly better if your spPROC1 read its inputs to be in a
table, rather than as parameters.

fatal exception c0000005

My database mysteriously took a nosedive yesterday. When I attempt to
debug any stored procedure inside any database, I have the following
happend:
Debug Window footer message indicates Waiting for Input. The system
stalls for 3-5 seconds and returns this message:
SqlDumpExceptionHandler: Process %i generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
The process number varies.
I have followed the information outlined in this MS article but this
has not resolved the problem.
http://support.microsoft.com/default.aspx?scid=kb;en-us;270061
What version and SP is your SQL Server?
Also, how many applications are currently running in the same machine?
"etm1109@.gmail.com" wrote:

> My database mysteriously took a nosedive yesterday. When I attempt to
> debug any stored procedure inside any database, I have the following
> happend:
> Debug Window footer message indicates Waiting for Input. The system
> stalls for 3-5 seconds and returns this message:
> SqlDumpExceptionHandler: Process %i generated fatal exception c0000005
> EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
> The process number varies.
> I have followed the information outlined in this MS article but this
> has not resolved the problem.
> http://support.microsoft.com/default.aspx?scid=kb;en-us;270061
>

fatal exception c0000005

My database mysteriously took a nosedive yesterday. When I attempt to
debug any stored procedure inside any database, I have the following
happend:
Debug Window footer message indicates Waiting for Input. The system
stalls for 3-5 seconds and returns this message:
SqlDumpExceptionHandler: Process %i generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
The process number varies.
I have followed the information outlined in this MS article but this
has not resolved the problem.
http://support.microsoft.com/defaul...kb;en-us;270061What version and SP is your SQL Server?
Also, how many applications are currently running in the same machine?
"etm1109@.gmail.com" wrote:

> My database mysteriously took a nosedive yesterday. When I attempt to
> debug any stored procedure inside any database, I have the following
> happend:
> Debug Window footer message indicates Waiting for Input. The system
> stalls for 3-5 seconds and returns this message:
> SqlDumpExceptionHandler: Process %i generated fatal exception c0000005
> EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
> The process number varies.
> I have followed the information outlined in this MS article but this
> has not resolved the problem.
> http://support.microsoft.com/defaul...kb;en-us;270061
>

fastest way to insert range/sequence

I'd like to use a stored procedure to insert large amounts of records
into a table. My field A should be filled with a given range of
numbers. I do the following ... but I'm sure there is a better
(faster) way:

select @.start = max(A) from tbl where B = 'test1' and C = 'test2'
while @.start <= 500000
begin
insert into tbl (A, B, C)
values (@.start, 'test1', test2')
set @.start = @.start +1
end

another question is, how to prevent that another user inserts the same
numbers into the field A?

Thanks a lot for any help!
ratu"ratu" <postit@.hispeed.ch> wrote in message
news:e6e93102.0407070830.67d763c5@.posting.google.c om...
> I'd like to use a stored procedure to insert large amounts of records
> into a table. My field A should be filled with a given range of
> numbers. I do the following ... but I'm sure there is a better
> (faster) way:
> select @.start = max(A) from tbl where B = 'test1' and C = 'test2'
> while @.start <= 500000
> begin
> insert into tbl (A, B, C)
> values (@.start, 'test1', test2')
> set @.start = @.start +1
> end
> another question is, how to prevent that another user inserts the same
> numbers into the field A?
> Thanks a lot for any help!
> ratu

One possible solution is an auxiliary table of numbers, or a UDF as
described in this post:

http://groups.google.com/groups?q=i...sftngp13&rnum=1

You could then do something like this:

insert into tbl (A, B, C)
select n, 'test1', 'test2'
from dbo.fn_nums(@.start, 500000)

As for preventing duplicate entries, you can use a primary key or unique
constraint to prevent duplicates, depending on your data model. If
necessary, you can also use a check constraint to ensure that the table will
only accept a certain range of numbers.

Simon|||On 7 Jul 2004 09:30:03 -0700, ratu wrote:

> I'd like to use a stored procedure to insert large amounts of records
> into a table. My field A should be filled with a given range of
> numbers. I do the following ... but I'm sure there is a better
> (faster) way:
> select @.start = max(A) from tbl where B = 'test1' and C = 'test2'
> while @.start <= 500000
> begin
> insert into tbl (A, B, C)
> values (@.start, 'test1', test2')
> set @.start = @.start +1
> end

Here's the DIGITS view-based version:

CREATE VIEW DIGITS (D) AS
SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9;

INSERT INTO tbl (A,B,C)
SELECT D3.D * 1000 + D2.D * 100 + D1.D as [ValA],
'test1' AS [ValB], 'test2' AS [ValC])
FROM DIGITS AS [D1], DIGITS AS [D2], DIGITS AS [D3]
WHERE D3.D * 1000 + D2.D * 100 + D1.D between @.LowVal AND @.HiVal

> another question is, how to prevent that another user inserts the same
> numbers into the field A?

Create a unique index on field A. The other user will receive an error.|||"Ross Presser" <rpresser@.imtek.com> wrote in message
news:jaaqazxjdyer.1b96bwdbr1qod.dlg@.40tude.net...
> On 7 Jul 2004 09:30:03 -0700, ratu wrote:
> > I'd like to use a stored procedure to insert large amounts of records
> > into a table. My field A should be filled with a given range of
> > numbers. I do the following ... but I'm sure there is a better
> > (faster) way:
> > select @.start = max(A) from tbl where B = 'test1' and C = 'test2'
> > while @.start <= 500000
> > begin
> > insert into tbl (A, B, C)
> > values (@.start, 'test1', test2')
> > set @.start = @.start +1
> > end
> Here's the DIGITS view-based version:
> CREATE VIEW DIGITS (D) AS
> SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
> UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9;

If there's no need to eliminate duplicates in the union operation, as here,
use UNION ALL instead. This particular case is trivial but in other
cases the performance improvement can be noticeable.

> INSERT INTO tbl (A,B,C)
> SELECT D3.D * 1000 + D2.D * 100 + D1.D as [ValA],
> 'test1' AS [ValB], 'test2' AS [ValC])
> FROM DIGITS AS [D1], DIGITS AS [D2], DIGITS AS [D3]
> WHERE D3.D * 1000 + D2.D * 100 + D1.D between @.LowVal AND @.HiVal

You're missing the 10s place here.

Of course, one can fill a table with all integers possibly needed and query
for ranges. Alternatively, one can define a view to calculate the range by
applying constraints to each place value in turn, that is, the ones, tens, hundreds,
etc., as opposed to applying a constraint to each final candidate integer. The
former being more of a branch and bound approach while the latter is
generate and test.

CREATE VIEW Digits (d)
AS
SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL
SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL
SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL
SELECT 9

-- Return a range [@.lower, @.upper]
CREATE FUNCTION NonnegativeIntegerRange
(@.lower INT, @.upper INT)
RETURNS TABLE
AS
RETURN(
SELECT Ones.d + 10 * Tens.d + 100 * Hundreds.d + 1000 * Thousands.d +
10000 * TenThousands.d AS n
FROM Digits AS TenThousands
INNER JOIN
Digits AS Thousands
ON TenThousands.d BETWEEN @.lower/10000 AND @.upper/10000 AND
(TenThousands.d <> @.upper/10000 OR
Thousands.d <= (@.upper%10000)/1000) AND
(TenThousands.d <> @.lower/10000 OR
Thousands.d >= (@.lower%10000)/1000)
INNER JOIN
Digits AS Hundreds
ON (TenThousands.d <> @.upper/10000 OR
Thousands.d <> (@.upper%10000)/1000 OR
Hundreds.d <= (@.upper%1000)/100) AND
(TenThousands.d <> @.lower/10000 OR
Thousands.d <> (@.lower%10000)/1000 OR
Hundreds.d >= (@.lower%1000)/100)
INNER JOIN
Digits AS Tens
ON (TenThousands.d <> @.upper/10000 OR
Thousands.d <> (@.upper%10000)/1000 OR
Hundreds.d <> (@.upper%1000)/100 OR
Tens.d <= (@.upper%100)/10) AND
(TenThousands.d <> @.lower/10000 OR
Thousands.d <> (@.lower%10000)/1000 OR
Hundreds.d <> (@.lower%1000)/100 OR
Tens.d >= (@.lower%100)/10)
INNER JOIN
Digits AS Ones
ON (TenThousands.d <> @.upper/10000 OR
Thousands.d <> (@.upper%10000)/1000 OR
Hundreds.d <> (@.upper%1000)/100 OR
Tens.d <> (@.upper%100)/10 OR
Ones.d <= @.upper%10) AND
(TenThousands.d <> @.lower/10000 OR
Thousands.d <> (@.lower%10000)/1000 OR
Hundreds.d <> (@.lower%1000)/100 OR
Tens.d <> (@.lower%100)/10 OR
Ones.d >= @.lower%10)
)

SELECT n
FROM NonnegativeIntegerRange(20000, 20009)
ORDER BY n

n
20000
20001
20002
20003
20004
20005
20006
20007
20008
20009

--
JAG|||On Fri, 09 Jul 2004 03:18:31 GMT, John Gilson wrote:

> If there's no need to eliminate duplicates in the union operation, as here,
> use UNION ALL instead. This particular case is trivial but in other
> cases the performance improvement can be noticeable.

Thanks. I noticed you using UNION ALL before, but never understood it,
because I never looked up the explanation of UNION ALL.

> You're missing the 10s place here.

D'oh!

> Of course, one can fill a table with all integers possibly needed and query
> for ranges. Alternatively, one can define a view to calculate the range by
> applying constraints to each place value in turn, that is, the ones, tens, hundreds,
> etc., as opposed to applying a constraint to each final candidate integer. The
> former being more of a branch and bound approach while the latter is
> generate and test.

Thanks for your clearly expressed improvement of my half-baked ideas. :)

I still have two very active SQL 6.5 servers, so I tend not to think of UDF
solutions.|||"Ross Presser" <rpresser@.imtek.com> wrote in message
news:1x4qrf3kzc0t0$.1wjptvh6fhq50.dlg@.40tude.net.. .
> On Fri, 09 Jul 2004 03:18:31 GMT, John Gilson wrote:
> > If there's no need to eliminate duplicates in the union operation, as here,
> > use UNION ALL instead. This particular case is trivial but in other
> > cases the performance improvement can be noticeable.
> Thanks. I noticed you using UNION ALL before, but never understood it,
> because I never looked up the explanation of UNION ALL.

It's worth knowing when you're taking the union of significant result sets.
It's like knowing when and when not to use DISTINCT. In this case
it obviously isn't an issue other than reinforcing good practice.

> > You're missing the 10s place here.
> D'oh!

It's even easier to do when you go into the millions and beyond.

> > Of course, one can fill a table with all integers possibly needed and query
> > for ranges. Alternatively, one can define a view to calculate the range by
> > applying constraints to each place value in turn, that is, the ones, tens, hundreds,
> > etc., as opposed to applying a constraint to each final candidate integer. The
> > former being more of a branch and bound approach while the latter is
> > generate and test.
> Thanks for your clearly expressed improvement of my half-baked ideas. :)

Your generate-and-test approach is an obvious and completely reasonable
solution. The branch-and-bound approach, run on SQL Server 2000, does
seem, in cursory testing, to be over twice as fast. Though it is more verbose
and perhaps less immediately clear.

> I still have two very active SQL 6.5 servers, so I tend not to think of UDF
> solutions.

The UDF here is just for named packaging. Could've simply provided the
SELECT using variables for the range bounds.

--
JAG

faster page update using SQL Server data

Hi,

What is the fastest way to get informations from a SQL BD (using stored procedure) in a WEB Page ?

This web page need to be updated EVERY SECONDE !
Javacript / OleDB / SQLConnection / ... ?

I plan to use a a usercontrol containing the informations, am I right ?

thank you for your help,You can get HTML code directly using the Web Tasks functionality in SQL Server ... I think this is the easiest and the fastest way to get web pages directly from SQL Server ...

Refer to SQL Server BOL for further info ...

Faster execution of stored procedure

Hello
I ask which approach gives better speed results. To put next query in one
stored procedure, or to split in two:
//ONE
IF(@.FindType=1) /*Find Full word*/
BEGIN
SELECT f_English FROM t_Dictionary
WHERE f_NonEnglish = @.parWord
END
ELSE /*Partial find*/
BEGIN
SELECT f_English FROM t_Dictionary
WHERE f_NonEnglish LIKE (@.parWord+'%')
END
or split it in two independent stored procedures
//first:
SELECT f_English FROM t_Dictionary
WHERE f_NonEnglish = @.parWord
//and second
SELECT f_English FROM t_Dictionary
WHERE f_NonEnglish LIKE (@.parWord+'%')
I need speed!
Thanks!Hi
Makes no difference. Make sure f_NonEnglish is indexed.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"MilanB" <MilanB@.discussions.microsoft.com> wrote in message
news:084BC180-BBE6-4AF6-B307-29966653D4BF@.microsoft.com...
> Hello
> I ask which approach gives better speed results. To put next query in one
> stored procedure, or to split in two:
> //ONE
> IF(@.FindType=1) /*Find Full word*/
> BEGIN
> SELECT f_English FROM t_Dictionary
> WHERE f_NonEnglish = @.parWord
> END
> ELSE /*Partial find*/
> BEGIN
> SELECT f_English FROM t_Dictionary
> WHERE f_NonEnglish LIKE (@.parWord+'%')
> END
> or split it in two independent stored procedures
> //first:
> SELECT f_English FROM t_Dictionary
> WHERE f_NonEnglish = @.parWord
> //and second
> SELECT f_English FROM t_Dictionary
> WHERE f_NonEnglish LIKE (@.parWord+'%')
>
> I need speed!
> Thanks!|||I have seen many times the parameter in where clause makes query slow
in stroe procedure and functions.
So first check speed of you query in query analyzer and then in store
procedures.
If query run fast in query anlyzer than in store proc then execute your
query as dynamic sql in store proc.
Make sure proper index are there.
Regards
Amish|||I think that Dynamic SQL is best avoided at all costs. If you find that
your query is running slow in a stored proc, but quick in Query analyser,
first check your indexes (as already suggested), then look at the code of
the query to see if it's really as optimal as it can be, don't forget that
sometimes splitting a query into two can have a dramatic performance
improvement. Finally, use with recompile on the procedure.
Regards
Colin Dawson
www.cjdawson.com
"amish" <shahamishm@.gmail.com> wrote in message
news:1135428805.710381.226300@.g14g2000cwa.googlegroups.com...
>I have seen many times the parameter in where clause makes query slow
> in stroe procedure and functions.
> So first check speed of you query in query analyzer and then in store
> procedures.
> If query run fast in query anlyzer than in store proc then execute your
> query as dynamic sql in store proc.
> Make sure proper index are there.
> Regards
> Amish
>|||Yes Colin,
my one store proc which was having about 1000 lines was taking about 5
minutes to complete.
But then I split it into 4 different store proc and then the time they
all took to complete was only 1 minute.
Always try to make your procedure as short as possible.
Regards|||amish (shahamishm@.gmail.com) writes:
> my one store proc which was having about 1000 lines was taking about 5
> minutes to complete.
> But then I split it into 4 different store proc and then the time they
> all took to complete was only 1 minute.
> Always try to make your procedure as short as possible.
Nah, being guilty of procedures that are even longer than 1000 lines,
I cannot agree. As with so many other things in the database world, the
answer is "It depends".
Most of our long procedures are updating procedures that encapsulates quite
a bit of business logic. (We are strong adherents of the idea that the
business logic should be where the data is.)
But if you have a procedure which goes like:
IF @.cond1 IS NOT NULL AND @.cond2 IS NULL
SELECT ...
FROM tbl
WHERE col1 = @.cond1
ELSE IF @.cond2 IS NOT NULL
SELECT ...
FROM tbl
WHERE col2 = @.cond2
And even better has things like
IF @.todate IS NULL
SELECT @.todate = getdate()
Then there is reason to split up the code into several procedures. The
reason for this is parameter sniffing. SQL Server builds the query
plan for a stored procedure each time there is no plan for it in cache.
It looks at the parameter values for that particular call, and uses
these as guidance. This means if that if you have lots of branches
with various conditions, all will get their plan from those input values.
But the search on ProductID may not get an optimal plan, if @.productid
is NULL. And in the case an input parameter is replaced with a default
values, as in the @.todate example, the parameter sniffing is not good at
all.
So for these reasons, it may be a good idea to have a main procedure
that only looks at parameter values and then calls various sub-
procedures that all have their specific queries. Or you just build
dynamic SQL instead, if you security policy permits that. As the
number of input conditions increases this becomes about the only manageable
possibility.
Also see my article http://www.sommarskog.se/dyn-search.html.
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|||Thank you all for answering.
Regards
Milan|||I also agree that there's no set rule on this. Keeping your procedures
short is a good rule of thumb, but there are always exceptions.
My personal view is to be more strict about a keeping to a single execution
path within a stored proc. There's no reason why a stored proc cannot call
a seperate stored procedure. So you can build some complicated business
logic into several much simpler stored procedures. With the CLR integration
in SQL2005 this will become the standard way of building some pretty
complicated solutions.
The most important thing is to always try to write code which is recompiled
infrequently, whilst executing at the maximum potential of the database. It
is also important to make sure that any stored procedure uses as few
resources as possible to obtain maximum scalibility. Since developers are
always generating bespoke procedures, there cannot be any hard and fast
rules, because the moment that one is set there will be a situation which
defies the rule.
Regards
Colin Dawson
www.cjdawson.com
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9736AB4BE5DACYazorman@.127.0.0.1...
> amish (shahamishm@.gmail.com) writes:
> Nah, being guilty of procedures that are even longer than 1000 lines,
> I cannot agree. As with so many other things in the database world, the
> answer is "It depends".
> Most of our long procedures are updating procedures that encapsulates
> quite
> a bit of business logic. (We are strong adherents of the idea that the
> business logic should be where the data is.)
> But if you have a procedure which goes like:
> IF @.cond1 IS NOT NULL AND @.cond2 IS NULL
> SELECT ...
> FROM tbl
> WHERE col1 = @.cond1
> ELSE IF @.cond2 IS NOT NULL
> SELECT ...
> FROM tbl
> WHERE col2 = @.cond2
> And even better has things like
> IF @.todate IS NULL
> SELECT @.todate = getdate()
> Then there is reason to split up the code into several procedures. The
> reason for this is parameter sniffing. SQL Server builds the query
> plan for a stored procedure each time there is no plan for it in cache.
> It looks at the parameter values for that particular call, and uses
> these as guidance. This means if that if you have lots of branches
> with various conditions, all will get their plan from those input values.
> But the search on ProductID may not get an optimal plan, if @.productid
> is NULL. And in the case an input parameter is replaced with a default
> values, as in the @.todate example, the parameter sniffing is not good at
> all.
> So for these reasons, it may be a good idea to have a main procedure
> that only looks at parameter values and then calls various sub-
> procedures that all have their specific queries. Or you just build
> dynamic SQL instead, if you security policy permits that. As the
> number of input conditions increases this becomes about the only
> manageable
> possibility.
> Also see my article http://www.sommarskog.se/dyn-search.html.
>
>
> --
> 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|||Colin Dawson (newsgroups@.cjdawson.com) writes:
> My personal view is to be more strict about a keeping to a single
> execution path within a stored proc. There's no reason why a stored
> proc cannot call a seperate stored procedure. So you can build some
> complicated business logic into several much simpler stored procedures.
There is however one problem: T-SQL does not lend itself extremely well
to this practice. If you split up logic between procedures, you need
parameters, and T-SQL procedures indeed have them. But only scalar
parameters, and in a database you rather work with tables.
There are ways to share data through tables between stored procedures,
(see http://www.sommarskog.se/share_data.html for a discussion), but
no method is entirely satisfying. Particularly in SQL 2000, you easily
end up with recompilations that can be costly.

> With the CLR integration in SQL2005 this will become the standard way of
> building some pretty complicated solutions.
Depends on what your business logic does. For our part, I don't see that
we will make any massive move to the CLR. T-SQL is still the best for
handling data. Which at least our business logic mainly is about.
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|||>>Or you just build
dynamic SQL instead, if you security policy permits that. As the
number of input conditions increases this becomes about the only
manageable
possibility.
<<
Erland,
I almost completely agree to what you are saying, but sometimes dynamic
SQL is the best option even if there are only two parameters:
select sum(amount), count(*) from some_table where some_date
between @.date_from and @.date_to
If the index on some_date in non-clustered, and the table is big
enough, it's better to let the optimizer choose between table scan and
bookmark lookups every time the query runs

faster box but slower database

Hi,
Got two SQL servers (production,dev) and I noticed recently that my
production servers is much slower when running one import stored
procedure. The stored proc only uses tables variables a lot and only
performs selects on actual tables. The specs are:
Productions server:
SQL 2000 v. 8002039 (SP4)
2x Dual Core Intel Xeon 3,2GH
1GB Memory
RAID 1
Windows 2003 Server
Development server:
SQL 2000 v. 800194 (no service pack)
1x Dual Core Intel Pentium 4 3GHz
Windows XP Professional
The stored procedures takes 20 sec running on the dev box but 2.4 min
on the production box.
All indexes and tables are the same in both databases. I tried
updating the stats, rebuild indexes, recompile the stored proc, free
the proc cache but without any luck.
Any help would be very much appricated.
KristjanOne thing I forgot to mention. The execution tree found in the
profiler differ for the same query in diffrent databases.|||You didn't mention how much ram is on the dev box.
Also how many rows and what is the DB size? 1GB of ram on a production SQL
box with that kind of CPU seems way low to me.
Mike
"kgb" <kristjan.gudni.bjarnason@.gmail.com> wrote in message
news:1138125661.913476.224860@.g14g2000cwa.googlegroups.com...
> Hi,
> Got two SQL servers (production,dev) and I noticed recently that my
> production servers is much slower when running one import stored
> procedure. The stored proc only uses tables variables a lot and only
> performs selects on actual tables. The specs are:
> Productions server:
> SQL 2000 v. 8002039 (SP4)
> 2x Dual Core Intel Xeon 3,2GH
> 1GB Memory
> RAID 1
> Windows 2003 Server
> Development server:
> SQL 2000 v. 800194 (no service pack)
> 1x Dual Core Intel Pentium 4 3GHz
> Windows XP Professional
> The stored procedures takes 20 sec running on the dev box but 2.4 min
> on the production box.
> All indexes and tables are the same in both databases. I tried
> updating the stats, rebuild indexes, recompile the stored proc, free
> the proc cache but without any luck.
> Any help would be very much appricated.
> Kristjan
>|||The dev box also got 1 GB ram. The databases are almost the same size
because the app is not writing very much on runtime.
The stored proc spends most time on single table containing about
600,000 rows (both dev and production server). By the way, the
profiler tells me the production servers is doing ~10 times more reads
and spending ~10 more CPU than the dev server.
The total databsize is about 0,5 GB but maybe 1/4 of it is accessed
realtime.|||What different you found in Execution plan?
Try to run query on Prod. using option (maxdop 1).
If you have joins check that fields on both sides of the join have same
datatypes.
Regards
Amish Shah|||Tried the maxdop 1 option, no big difference.
The datatypes were ok.
Installed the database on my laptop to reproduce this. With no service
pack the stored proc ran fine (30 secs). With SP4 it ran for minutes.
Seems like updated server optimizer is not doing a good job with my
stored proc. Collecting some more data which I will post to this
thread soon.|||There have been a number of changes from GOLD and SP1 to SP4. A number of
these fixes result in different query plans being generated. It is not a far
comparison of performance between GOLD and SP4. Your GOLD version is also a
lot less secure in that it does not protect against SLAMMER.
Chris
"kgb" <kristjan.gudni.bjarnason@.gmail.com> wrote in message
news:1138274581.095820.120850@.o13g2000cwo.googlegroups.com...
> Tried the maxdop 1 option, no big difference.
> The datatypes were ok.
> Installed the database on my laptop to reproduce this. With no service
> pack the stored proc ran fine (30 secs). With SP4 it ran for minutes.
> Seems like updated server optimizer is not doing a good job with my
> stored proc. Collecting some more data which I will post to this
> thread soon.
>|||This may sound silly, but have you opened the sproc on the SP4 box and
recompiled it on SP4. I have seen a few odd things from time to time when
sprocs were compiled on a lower SPack DB then restored to a differnt SPack
server. Views too.
At least you would give the SP4 query optimizer a chance to take a look at
the sproc to build a new plan to store with the sproc.
Mike
"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
news:uu3f3HpIGHA.532@.TK2MSFTNGP15.phx.gbl...
> There have been a number of changes from GOLD and SP1 to SP4. A number of
> these fixes result in different query plans being generated. It is not a
> far comparison of performance between GOLD and SP4. Your GOLD version is
> also a lot less secure in that it does not protect against SLAMMER.
> Chris
> "kgb" <kristjan.gudni.bjarnason@.gmail.com> wrote in message
> news:1138274581.095820.120850@.o13g2000cwo.googlegroups.com...
>> Tried the maxdop 1 option, no big difference.
>> The datatypes were ok.
>> Installed the database on my laptop to reproduce this. With no service
>> pack the stored proc ran fine (30 secs). With SP4 it ran for minutes.
>> Seems like updated server optimizer is not doing a good job with my
>> stored proc. Collecting some more data which I will post to this
>> thread soon.
>|||can you post the DDL and query you are running?|||Currently changing the query to "fit" the SP4 optimizer and think I
almost there, meaning the duration of the query is dropping to what I
expected. The changes sofar involved rewriting of several joins. I
get back to the thread in few hours or less - hopfully with result of
success. Thanks for all your replies. Its amazing to find the support
available on this group.|||Below you find the exec tree for the query, both versions. Being
novice mssql programmer I am not sure what part of the tree is taking
most time. The execution plan makes not much sense to me - the figures
are cost estimated rows seem all wrong.
the execution tree from the profilier is like this for the SP4 box.
Execution Tree
--
Table Insert(OBJECT:(@.f3), SET:(@.f3.[outbound]=RaiseIfNull(1),
@.f3.[groundDur_2]=[t2].[TransitTime],
@.f3.[groundDur_1]=[t1].[TransitTime], @.f3.[duration_3]=[f3].[Duration],
@.f3.[duration_2]=[f2].[Duration], @.f3.[duration_1]=[f1].[Duration],
@.f3.[FlightDate_3]=[f3].[FlightDate],
@.f3.[f1_FlightID]=[f2].[FlightID], @.f3.[flightset_3]=[f3].[flightset],
@.f3.[FlightID_3]=[f3].[FlightID],
@.f3.[fromairport_3]=[f3].[fromairport],
@.f3.[toairport_3]=[f3].[toairport], @.f3.[eta_3]=[f3].[eta],
@.f3.[std_3]=[f3].[std], @.f3.[FlightDate_2]=RaiseIfNull([@.out]),
@.f3.[f0_ID]=[f1].[ID], @.f3.[f0_FlightID]=[f1].[FlightID],
@.f3.[flightset_2]=[f2].[FlightSet], @.f3.[FlightID_2]=[f2].[FlightID],
@.f3.[fromairport_2]=[f2].[FromAirport],
@.f3.[toairport_2]=[f2].[ToAirport], @.f3.[eta_2]=[f2].[ETA],
@.f3.[std_2]=[f2].[STD], @.f3.[flightdate_1]=[f1].[FlightDate],
@.f3.[flightset_1]=[f1].[flightset], @.f3.[FlightID_1]=[f1].[FlightID],
@.f3.[fromairport_1]=[f1].[fromairport],
@.f3.[toairport_1]=[f1].[toairport], @.f3.[eta_1]=[f1].[eta],
@.f3.[std_1]=[f1].[std], @.f3.[ID]=RaiseIfNull([Expr1009]),
@.f3.[FlightNumber_1]=[f1].[FlightNumber],
@.f3.[AirlineCode_1]=[f1].[AirlineCode],
@.f3.[FlightNumber_2]=[f2].[FlightNumber],
@.f3.[AirlineCode_2]=[f2].[AirlineCode],
@.f3.[FlightNumber_3]=[f3].[FlightNumber],
@.f3.[AirlineCode_3]=[f3].[AirlineCode]))
|--Top(ROWCOUNT est 0)
|--Compute Scalar(DEFINE:([Expr1009]=getidentity(1295343679, 2,
'@.f3')))
|--Filter(WHERE:([f3].[std]>[f2].[ETA]+Convert([@.transitDuration])+isnull([t2].[TransitTime],
0)))
|--Nested Loops(Left Outer Join, OUTER REFERENCES:([f2].[ArrTerm],
[f2].[ToAirport], [f3].[depTerm], [f3].[fromairport]))
|--Filter(WHERE:([f2].[STD]>[f1].[eta]+Convert([@.transitDuration])+isnull([t1].[TransitTime],
0)))
| |--Nested Loops(Left Outer Join, OUTER REFERENCES:([f1].[arrTerm],
[f1].[toairport], [f2].[DepTerm], [f2].[FromAirport]))
| |--Nested Loops(Inner Join,
WHERE:([f1].[toairport]<>[a].[AirportCode]))
| | |--Nested Loops(Inner Join,
WHERE:([f2].[ToAirport]<>[b].[AirportCode]))
| | | |--Hash Match(Inner Join,
HASH:([f1].[toaptjoin])=([f2].[FromAptJoin]),
RESIDUAL:([f1].[fromairport]<>[f3].[fromairport] AND
((([f1].[eta]-[f1].[std])*[f3].[dayafter]>0 AND
([f1].[eta]-[f1].[std])*[d].[dayafter]>0) OR ([f1].[eta]>[f1].[std] AND
([f2].[ETA]-[f2].[STD])*[f3].[dayafter]>0))))
| | | | |--Table Scan(OBJECT:(@.f1 AS [f1]),
WHERE:([f1].[outbound]=1))
| | | | |--Bookmark Lookup(BOOKMARK:([Bmk1002]),
OBJECT:([dohop].[dbo].[Flight1] AS [f2]))
| | | | |--Nested Loops(Inner Join, OUTER
REFERENCES:([d].[date], [d].[weekday], [f3].[fromaptjoin]))
| | | | |--Nested Loops(Inner Join)
| | | | | |--Table Scan(OBJECT:(@.flast AS
[f3]), WHERE:([f3].[outbound]=1))
| | | | | |--Table
Scan(OBJECT:(@.weekdays_out AS [d]))
| | | | |--Index
Seek(OBJECT:([dohop].[dbo].[Flight1].[IX3_Flight1] AS [f2]),
SEEK:([f2].[ToAptJoin]=[f3].[fromaptjoin]),
WHERE:((([f2].[ValidFrom]<=[d].[date] AND [f2].[ValidTo]>=[d].[date])
AND [f2].[Status]=0) AND (Convert([f2].[Weekdays])&[d].[weekday])>0)
ORDERED FORWARD)
| | | |--Table Scan(OBJECT:(@.arrivalList AS [b]))
| | |--Table Scan(OBJECT:(@.arrivalList AS [a]))
| |--Clustered Index
Seek(OBJECT:([dohop].[dbo].[Transit].[PK_Transit] AS [t1]),
SEEK:([t1].[Apt1]=[f1].[toairport]),
WHERE:([t1].[Apt2]=[f2].[FromAirport] AND (([t1].[Term1]=' ' AND
[t1].[Term2]=' ') OR ([f1].[arrTerm]=[t1].[Term1] AND
[f2].[DepTerm]=[t1].[Term2]))) ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:([dohop].[dbo].[Transit].[PK_Transit] AS
[t2]), SEEK:([t2].[Apt1]=[f2].[ToAirport]),
WHERE:([t2].[Apt2]=[f3].[fromairport] AND (([t2].[Term1]=' ' AND
[t2].[Term2]=' ') OR ([f2].[ArrTerm]=[t2].[Term1] AND
[f3].[depTerm]=[t2].[Term2]))) ORDERED FORWARD)
and for the GOLD box its like:
Execution Tree
--
Table Insert(OBJECT:(@.f3), SET:(@.f3.[outbound]=RaiseIfNull(1),
@.f3.[groundDur_2]=[t2].[TransitTime],
@.f3.[groundDur_1]=[t1].[TransitTime], @.f3.[duration_3]=[f3].[Duration],
@.f3.[duration_2]=[f2].[Duration], @.f3.[duration_1]=[f1].[Duration],
@.f3.[FlightDate_3]=[f3].[FlightDate],
@.f3.[f1_FlightID]=[f2].[FlightID], @.f3.[flightset_3]=[f3].[flightset],
@.f3.[FlightID_3]=[f3].[FlightID],
@.f3.[fromairport_3]=[f3].[fromairport],
@.f3.[toairport_3]=[f3].[toairport], @.f3.[eta_3]=[f3].[eta],
@.f3.[std_3]=[f3].[std], @.f3.[FlightDate_2]=RaiseIfNull([@.out]),
@.f3.[f0_ID]=[f1].[ID], @.f3.[f0_FlightID]=[f1].[FlightID],
@.f3.[flightset_2]=[f2].[FlightSet], @.f3.[FlightID_2]=[f2].[FlightID],
@.f3.[fromairport_2]=[f2].[FromAirport],
@.f3.[toairport_2]=[f2].[ToAirport], @.f3.[eta_2]=[f2].[ETA],
@.f3.[std_2]=[f2].[STD], @.f3.[flightdate_1]=[f1].[FlightDate],
@.f3.[flightset_1]=[f1].[flightset], @.f3.[FlightID_1]=[f1].[FlightID],
@.f3.[fromairport_1]=[f1].[fromairport],
@.f3.[toairport_1]=[f1].[toairport], @.f3.[eta_1]=[f1].[eta],
@.f3.[std_1]=[f1].[std], @.f3.[ID]=RaiseIfNull([Expr1011]),
@.f3.[FlightNumber_1]=[f1].[FlightNumber],
@.f3.[AirlineCode_1]=[f1].[AirlineCode],
@.f3.[FlightNumber_2]=[f2].[FlightNumber],
@.f3.[AirlineCode_2]=[f2].[AirlineCode],
@.f3.[FlightNumber_3]=[f3].[FlightNumber],
@.f3.[AirlineCode_3]=[f3].[AirlineCode]))
|--Top(ROWCOUNT est 0)
|--Compute Scalar(DEFINE:([Expr1011]=getidentity(1329804195, 2,
'@.f3')))
|--Filter(WHERE:([f3].[std]>[f2].[ETA]+Convert([@.transitDuration])+isnull([t2].[TransitTime],
0)))
|--Nested Loops(Left Outer Join, OUTER REFERENCES:([f2].[ArrTerm],
[f2].[ToAirport], [f3].[depTerm], [f3].[fromairport]))
|--Filter(WHERE:([f2].[STD]>[f1].[eta]+Convert([@.transitDuration])+isnull([t1].[TransitTime],
0)))
| |--Nested Loops(Left Outer Join, OUTER REFERENCES:([f1].[arrTerm],
[f1].[toairport], [f2].[DepTerm], [f2].[FromAirport]))
| |--Nested Loops(Left Anti Semi Join,
WHERE:(@.arrivalList.[AirportCode]=NULL OR
[f2].[ToAirport]=@.arrivalList.[AirportCode]))
| | |--Nested Loops(Left Anti Semi Join,
WHERE:(@.arrivalList.[AirportCode]=NULL OR
[f1].[toairport]=@.arrivalList.[AirportCode]))
| | | |--Nested Loops(Inner Join,
WHERE:(((((([f1].[eta]-[f1].[std])*[f3].[dayafter]>0 AND
([f1].[eta]-[f1].[std])*[d].[dayafter]>0) OR ([f1].[eta]>[f1].[std] AND
([f2].[ETA]-[f2].[STD])*[f3].[dayafter]>0)) AND
Convert([f2].[Weekdays])&[d].[weekday]>0) AND
[f2].[ValidFrom]<=[d].[date]) AND [f2].[ValidTo]>=[d].[date]))
| | | | |--Bookmark Lookup(BOOKMARK:([Bmk1001]),
OBJECT:([dohop].[dbo].[Flight1] AS [f2]))
| | | | | |--Nested Loops(Inner Join, OUTER
REFERENCES:([f1].[toaptjoin], [f3].[fromaptjoin]))
| | | | | |--Nested Loops(Inner Join,
WHERE:([f1].[fromairport]<>[f3].[fromairport]))
| | | | | | |--Table Scan(OBJECT:(@.f1 AS
[f1]), WHERE:([f1].[outbound]=1))
| | | | | | |--Table Scan(OBJECT:(@.flast AS
[f3]), WHERE:([f3].[outbound]=1))
| | | | | |--Index
Seek(OBJECT:([dohop].[dbo].[Flight1].[IX3_Flight1] AS [f2]),
SEEK:([f2].[ToAptJoin]=[f3].[fromaptjoin] AND
[f2].[FromAptJoin]=[f1].[toaptjoin] AND [f2].[Status]=0) ORDERED
FORWARD)
| | | | |--Table Scan(OBJECT:(@.weekdays_out AS [d]))
| | | |--Table Scan(OBJECT:(@.arrivalList))
| | |--Table Scan(OBJECT:(@.arrivalList))
| |--Clustered Index
Seek(OBJECT:([dohop].[dbo].[Transit].[PK_Transit] AS [t1]),
SEEK:([t1].[Apt1]=[f1].[toairport]),
WHERE:([t1].[Apt2]=[f2].[FromAirport] AND (([t1].[Term1]=' ' AND
[t1].[Term2]=' ') OR ([f1].[arrTerm]=[t1].[Term1] AND
[f2].[DepTerm]=[t1].[Term2]))) ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:([dohop].[dbo].[Transit].[PK_Transit] AS
[t2]), SEEK:([t2].[Apt1]=[f2].[ToAirport]),
WHERE:([t2].[Apt2]=[f3].[fromairport] AND (([t2].[Term1]=' ' AND
[t2].[Term2]=' ') OR ([f2].[ArrTerm]=[t2].[Term1] AND
[f3].[depTerm]=[t2].[Term2]))) ORDERED FORWARD)|||after reading this
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=58294&whichpage=1
thread I think I will move to SP3.

False error when trying to return data in datagrid

VB.NET 2003 / SQLS2K

The Stored Procedure returns records within Query Analyzer.
But when the Stored Procedure is called by ADO.NET ~ it produced the
following error message.

--------
Exception Message: Failed to enable constraints. One or more rows
contain values violating non-null, unique, or foreign-key constraints.
--------

--------
Exception Source: System.Data
--------

If I click OK past the error messages I will get data filling the
datagrid. However not as I would like to see it.

Even though it returns the proper data rows and includes all the
columns I asked for, it also returns plenty of columns I didn't ask for
(all the columns of the main table) and all those columns are filled
with "null"

In addition each row header contains a red exclaimation mark whch when
hovered over reads;

"Column 'cmEditedBy' does not allow DBNull.Values."

An interesting thing about this column 'cmEditedBy' is that there is
noting wrong with it and all rows for that column contain data.

I believe this error is a mistake! But it probably indicates some other
problem. How should I track its cause?

M O R E ...
Below is the code in the data layer, the stored procedure, and the data
returned within query analyzer.

\\
'DataAdapter
Friend daView041CmptCyln As New SqlDataAdapter

'SqlCommand
Private daView041CmptCyln_CmdSel As New SqlCommand

'Add the command
daView041CmptCyln.SelectCommand = daView041CmptCyln_CmdSel

'Select
With daView041CmptCyln_CmdSel
.CommandType = CommandType.StoredProcedure
.CommandText = "usp_View_041Cmpt_ByJobCyln"
.Connection = sqlConn
With daView041CmptCyln_CmdSel.Parameters
.Add(New SqlParameter("@.RETURN_VALUE", SqlDbType.Int, _
4, ParameterDirection.ReturnValue, False, CType(0,
Byte), _
CType(0, Byte), "", DataRowVersion.Current, Nothing))
'Criteria
.Add("@.fkJob", SqlDbType.Text).Value = _
"48c64a55-874d-40d0-addc-7245f5d9c118"
'.Add("@.fkJob", SqlDbType.Text).Value = f050View.jobID
End With
End With
//

\\
ALTER PROCEDURE usp_View_041Cmpt_ByJobCyln
(@.fkJob char(36))
AS SET NOCOUNT ON;

SELECT
JobNumber,
DeviceName,
ComponentName,

Description,
Quan,
Bore,
Stroke,
Rod,
Seconds,
CylPSI,
PosA,
PosB,
PosC,
PosD,
PosE,
HomeIsRet,
RetIsRetrac,
POChecks,
Regulated,
FlowControl,
PortSize,
LoadMass

FROM tbl040cmpt
INNER JOIN tbl030Devi ON fkDevice = pkDeviceId
INNER JOIN tbl020Proc ON fkProcess = pkProcessId
INNER JOIN tbl010Job ON fkJob = pkjobId
INNER JOIN lkp202ComponentType ON fkComponenttype = pkComponentTypeId
INNER JOIN lkp201DeviceType ON fkDeviceType = pkDeviceTypeId
INNER JOIN lkp101PortSize on cmSmallint05 = pkPortSizeId

WHERE
(fkJob = @.fkJob)
--fkJob = '48c64a55-874d-40d0-addc-7245f5d9c118'
AND fkComponentType = 2

GO
//

(note - columns are wrapped)
\\
F1111Clip DriverCylinderClip Driver_2 - Top -
Cylinder91.2502.250.8752.250NULL01101110011/8 NPTNULL
F1111Punch MechCylinderPunch Mech_1 -
Cylinder_222.1002.0001.0001.234NULL11000110011/8
NPTNULL
F1111Clip
DriverCylinderBottom92.1002.0001.0001.000NULL11010110011/4
NPTNULL
F1111Punch MechCylinderPunch Mech_1 -
Cylinder_122.1002.0001.0001.000NULL01000110011/8
NPTNULL
F1111DegateCylinderDegate 1 -
Cylinder21.1882.500.8751.000NULL11000110011/8 NPTNULL
F1111Clip DriverCylinderClip Driver 1 -
Bottom11.1801.250.8751.000NULL00011110011/4 NPTNULL
//dbuchanan (dbuchanan52@.hotmail.com) writes:
> VB.NET 2003 / SQLS2K
> The Stored Procedure returns records within Query Analyzer.
> But when the Stored Procedure is called by ADO.NET ~ it produced the
> following error message.
> --------
> Exception Message: Failed to enable constraints. One or more rows
> contain values violating non-null, unique, or foreign-key constraints.
> --------

Nah, it sounds as if that message is produced by .Net Framework. The
stored procedure pleads innocense.

> Even though it returns the proper data rows and includes all the
> columns I asked for, it also returns plenty of columns I didn't ask for
> (all the columns of the main table) and all those columns are filled
> with "null"
> In addition each row header contains a red exclaimation mark whch when
> hovered over reads;
> "Column 'cmEditedBy' does not allow DBNull.Values."

Well, that column is not in the result set, so obviously when you try
to populated the DataSet, NULL values is all you get. And apparently
they are not permitted.

I don't have that much experience of ADO .Net, but it sounds to me that
you have run some wizard that has constructed your dataset, and you then
have not been careful which columns to include. (Personally, if I were
to work with data sets, I would probably construct them manually.) Or is
there some thought behind of including columns that are not reported by the
query?

While not relevant to your problem, permit me to point an issue of style
with your query: you table includes six tables, no column is prefixed
with any alias (or the table name). This makes it very difficult for
anyone who looks at query to tell which table, the columns are coming
from. This also mean that if the DBA adds, say, "Description" to one
more table, the procedure will no longer compile because that column name
is now ambiguous.

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

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