MS SQL Update - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

MS SQL Update

Author

Topic

Knarf180
Starting Member

42 Posts

Posted - 2004-05-28 : 13:39:01

Hello, I recently upgraded from MS SQL 97 to MS SQL 2000 and one of my stored procedures is no longer functional. It seems that the line "Procedure_ID LIKE @Benefit_Sub_Type + '%'" in the below procedure is always returning false causing the procedure to return no rows. Another odd thing about this problem is that is only occurs when the procedure is executed through Visual Basic. If its done though the Query Analyzer everything works fine. VB is using the ADO reference "Microsoft ActiveX Data Objects 2.7 Libarary" Is this possibly too out of date for SQL 2000? Any information would be great, I have posted the SP below.

CREATE Procedure sp_Get_Beneficiary_Claim_Details
@Primary_SSN varchar(11),
@Suffix int,
@Benefit_ID varchar (1),
@Benefit_SubType var_char(4),
@Tooth_ID varchar(10),
@Status varchar(10)
AS
SELECT
*
FROM
Claims_Header H, Claims_Detail D
WHERE
H.YYYY = D.YYYY
AND
H.Julian_Day = D.Julian_Day
AND
H.Claim_Sequence = D.Claim_Sequence
AND
Procedure_ID LIKE @Benefit_Sub_Type + '%'
AND
Primary_SSN = @Primary_SSN
AND
Suffix = @Suffix
AND
Benefit_ID LIKE @Benefit_ID + '%'
AND
Tooth_ID LIKE @Tooth_ID + '%'
AND
D.Status like @Status + '%' -- POC 10/4/00
ORDER BY
D.Service_Date DESC
RETURN @@ERROR

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-05-28 : 13:43:51

Not sure if you just typed this wrong, but this line won't work:

@Benefit_SubType var_char(4),

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Knarf180
Starting Member

42 Posts

Posted - 2004-05-28 : 13:46:43

Sorry, yeah, pasted the wrong thing. The actual line is @Benefit_Sub_Type var_char(4) I was in the middle of messing with it.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-05-28 : 13:50:40

ok. ummm, it still won't work. YOu don't have var_char(4).

You ahve varchar(4) or char(4).

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-28 : 13:50:53

I am confused by your comment that you recently upgraded from MS SQL 97 to MS SQL 2000. There is no MS SQL 97. Are you referring to Access 97? If so, then are you referring to Access 2000?

Tara

Knarf180
Starting Member

42 Posts

Posted - 2004-05-28 : 13:52:54

Bah.. It was SQL 7.0 upgraded to 2000

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2004-05-28 : 13:53:17

He's referring to SQL Server 7

_________________________________________________________
The cradle of civilisation will defeat the new monsters of the world.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-28 : 13:54:10

Ok.

I would run a trace in SQL Profiler to determine what the VB app is passing to the stored procedure. I suspect that the parameter isn't being passed correctly since it works fine in Query Analyzer.

Tara

Knarf180
Starting Member

42 Posts

Posted - 2004-05-28 : 13:54:18

I fixed it back to what it originally was... Sorry for the confusion Derrick.

CREATE Procedure sp_Get_Beneficiary_Claim_Details
@Primary_SSN varchar(11),
@Suffix int,
@Benefit_ID varchar (1),
@Benefit_Sub_Type varchar(4),
@Tooth_ID varchar(10),
@Status varchar(10)
AS
SELECT
*
FROM
Claims_Header H, Claims_Detail D
WHERE
H.YYYY = D.YYYY
AND
H.Julian_Day = D.Julian_Day
AND
H.Claim_Sequence = D.Claim_Sequence
AND
Procedure_ID LIKE @Benefit_Sub_Type + '%'
AND
Primary_SSN = @Primary_SSN
AND
Suffix = @Suffix
AND
Benefit_ID LIKE @Benefit_ID + '%'
AND
Tooth_ID LIKE @Tooth_ID + '%'
AND
D.Status like @Status + '%' -- POC 10/4/00
ORDER BY
D.Service_Date DESC
RETURN @@ERROR
GO

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2004-05-28 : 13:59:34

It's bad enough working in the pensions industry. Now you have something which should really work, but it doesn't.

I feel for you knarf

_________________________________________________________
The cradle of civilisation will defeat the new monsters of the world.

Knarf180
Starting Member

42 Posts

Posted - 2004-05-28 : 14:00:26

tduggan, THANK YOU! I ran profiler and noticed that it had blank spaces since nothing was being passed to that optional field. Added an RTRIM() to the line and everything works fine now. Woot!

Knarf180
Starting Member

42 Posts

Posted - 2004-05-28 : 14:02:45

Now the question is.. Is there a global variable which I can set to tell the server to auto trim if the variable is blank?

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-05-28 : 14:10:23

No there is not. You need to handle it in the application or proc each time.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources