February 12, 2008 at 2:46 pm
Your insert is completing but the execution of the stored procedure is failing. Do as Jonnie suggested and run the procedre seperately. This way we can isolate the problem.
February 12, 2008 at 2:48 pm
How can I load the consid into the Repromote Proc?
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
February 12, 2008 at 2:52 pm
can you display what your conids look like in string format?
February 12, 2008 at 2:56 pm
So if you execute the stored procedure it works correctly but if you try to insert the results into a table it fails?
February 12, 2008 at 2:59 pm
I have copied the consultantIDs into a table, how would you suggest that I get them in to the string?
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
February 12, 2008 at 3:02 pm
Adam Haines (2/12/2008)
So if you execute the stored procedure it works correctly but if you try to insert the results into a table it fails?
Not quite, it works by itself it is correct but when I try to load the temprary table in my main proc it fails.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
February 12, 2008 at 3:06 pm
You can get your results into a string by using the same xml parsers logic.
DECLARE @ConIDs VARCHAR(MAX)
SET @ConIDs =
(SELECT DISTINCT RTRIM(ConsultantID) + '|'
FROM #mytable
FOR XML PATH(''))
select @conids
You can then copy/paste this on the site. It should be 28 names right?
Your error is consitent with insert columns are not consistent with the table definition.
February 12, 2008 at 3:07 pm
alorenzini (2/12/2008)
I haven't change anything in the Repromote proc, this is code as it now:ALTER PROCEDURE [consultantreports].[uspS_DownlineRepromotions2]
@CONSULTANTID VARCHAR(MAX)
AS
SET NOCOUNT ON
DECLARE @x XML
SET @x = ' '
Where do you assign the @CONSULTANTID parameter to the @x variable?...I miss the fancy REPLACE clause...or didn't you copy that part?
February 12, 2008 at 3:13 pm
I must have left it out:
ALTER PROCEDURE [consultantreports].[uspS_DownlineRepromotions2]
@CONSULTANTID VARCHAR(MAX)
AS
SET NOCOUNT ON
DECLARE @x XML
SET @x = ' '
CREATE TABLE #C
(ConsultantID CHAR(20)
,AchieveLevel CHAR(2)
,AchieveTitle CHAR(50)
,PeriodEndDate DATETIME
,RepFlag VarChar(2))
INSERT INTO #C
SELECT a.ConsultantID
,a.AchieveLevel
,a.AchieveTitle
,a.PeriodEndDate
, a.Repflag
FROM Volume a
INNER JOIN (SELECT x.i.value('.', 'VARCHAR(7)') AS [ConsultantID]
FROM @x.nodes('//i') x(i) ) as b
on a.ConsultantID = b.ConsultantID
WHERE b.ConsultantID IS NOT NULL
SELECT * FROM
(SELECT DISTINCT ConB.ConsultantID
, ConB.AchieveLevel
, ConB.AchieveTitle
, ConB.PeriodEndDate
, ConB.RepFlag
FROM #C ConA
INNER JOIN #C ConB ON ConA.ConsultantID = ConB.ConsultantID
AND ConA.AchieveLevel = ConB.AchieveLevel AND ConA.PeriodEndDate < ConB.PeriodEndDate) as CON
WHERE EXISTS (SELECT * FROM #C b
WHERE b.ConsultantID = CON.ConsultantID
AND b.AchieveLevel < CON.AchieveLevel
AND b.PeriodEndDate < CON.PeriodEndDate
AND NOT EXISTS (SELECT * FROM #C c
WHERE c.ConsultantID = CON.ConsultantID
AND c.PeriodEndDate > b.PeriodEndDate
AND c.PeriodEndDate < CON.PeriodEndDate))
ORDER BY PeriodEndDate
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
February 12, 2008 at 3:14 pm
I see the problem I think its the site stripping of the XML code in the proc. But rest assured its in the proc.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
February 12, 2008 at 3:20 pm
I created the string and ran the following command and it works:
EXEC [consultantreports].uspS_DownlineRepromotions2 '0004073|0007039|0017122|0024661|0025122|0032877|0036461|0038602|0040483|0042212|0052266|0053254|0054365|0056485|0056593|0058200|0059601|0060183|0060551|0061561|0061798|0062514|0063969|0065526|0067565|0067785|0067892|0069314|'
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
February 12, 2008 at 3:29 pm
But when I run this:
DECLARE @ConIDs VARCHAR(MAX)
SET @ConIDs =
(SELECT DISTINCT RTRIM(ConsultantID) + '|'
FROM contemp
FOR XML PATH(''))
--select @conids
EXEC uspS_DownlineRepromotions2 @ConIDs
I get empty recordset back.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
February 12, 2008 at 3:36 pm
If you run this what do you get
SELECT DISTINCT RTRIM(ConsultantID) + '|'
FROM contemp
February 12, 2008 at 3:40 pm
I get:
0004073|
0007039|
0017122|
0024661|
0025122|
0032877|
0036461|
0038602|
0040483|
0042212|
0052266|
0053254|
0054365|
0056485|
0056593|
0058200|
0059601|
0060183|
0060551|
0061561|
0061798|
0062514|
0063969|
0065526|
0067565|
0067785|
0067892|
0069314|
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
February 12, 2008 at 3:45 pm
This means that if you run the stored procedure against any one of those number it should return 0 results, which means that the consultant has never been repromoted.
Can you confirm?
Viewing 15 posts - 76 through 90 (of 124 total)
You must be logged in to reply to this topic. Login to reply