Stored procedure works in Query Analyser, not ADP

  • Thread starter Thread starter Bill Schanks
  • Start date Start date
B

Bill Schanks

I have the following Sproc that works in QA, but in my ADP as a report
recordsource I get 'provider command for child rowset does not produce
a rowset'.

Now this works if I take out the insert into RequestTracking.

ALTER Procedure [dbo].[usp_OpenClosed_CallRpts_48Hour_Updates]
@CallID int = Null,
@Status smallint = Null,
@SubStatus smallint = Null,
@DateOpened_Fr datetime = Null,
@DateOpened_To datetime = Null,
@DateResolved_Fr datetime = Null,
@DateResolved_To datetime = Null,
@ResolvedByEmployee smallint = Null,
@OpenedBy smallint = Null,
@AssignedTo smallint = Null,
@ContactMethod smallint = Null,
@ClientID smallint = Null,
@ContactPrefID varchar(7) = Null,
@BusLineName varchar(40) = Null,
@ContactName varchar(40) = Null,
@ActionType smallint = Null,
@Dept smallint = Null,
@SystemID smallint = Null,
@FollowupRequired bit = Null
AS

--=== Strip the time element from dates (just to be safe) by
converting
-- to whole days and back to a date. Usually faster than CONVERT.
-- 0 is a date (01/01/1900 00:00:00.000)

IF @DateOpened_Fr IS NOT NULL
SELECT @DateOpened_Fr = DATEADD(dd,DATEDIFF(dd,0,@DateOpened_Fr),0)

IF @DateOpened_To IS NOT NULL
SELECT @DateOpened_To = DATEADD(dd,DATEDIFF(dd,0,@DateOpened_To),0)

IF @DateResolved_Fr IS NOT NULL
SELECT @DateResolved_Fr = DATEADD(dd,DATEDIFF(dd,0,@DateOpened_Fr),0)

IF @DateResolved_To IS NOT NULL
SELECT @DateResolved_To = DATEADD(dd,DATEDIFF(dd,0,@DateOpened_To),0)

--=== Track Request
INSERT INTO dbo.RequestTracking
(RanByUser, RanProc, RanDateTime, CallID, Status, SubStatus,
DateOpened_Fr,
DateOpened_To, DateResolved_Fr, DateResolved_To, ResolvedByEmployee,
OpenedBy,
AssignedTo, ContactMethod, ClientID, ContactPrefID, BusLineName,
ContactName,
ActionType, Dept, SystemID, FollowupRequired)
SELECT
User, OBJECT_NAME(@@PROCID), getdate(), @CallID, @Status, @SubStatus,
@DateOpened_Fr,
@DateOpened_To, @DateResolved_Fr, @DateResolved_To,
@ResolvedByEmployee, @OpenedBy,
@AssignedTo, @ContactMethod, @ClientID, @ContactPrefID, @BusLineName,
@ContactName, @ActionType, @Dept, @SystemID, @FollowupRequired

--=== Return the results
SELECT
<<Snip>>
 
Add the commande SET NOCOUNT ON at the beginning, before the first Select
statement.
 
Just noticed that you have mixed DateOpened and DateResolved:

IF @DateResolved_Fr IS NOT NULL
SELECT @DateResolved_Fr = DATEADD(dd,DATEDIFF(dd,0,@DateOpened_Fr),0)

IF @DateResolved_To IS NOT NULL
SELECT @DateResolved_To = DATEADD(dd,DATEDIFF(dd,0,@DateOpened_To),0)

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


Bill Schanks said:
Thanks, that did the trick.
Click to expand...
 
Back
Top