7.0 to 2000 Migration - 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)

7.0 to 2000 Migration

Author

Topic

Knarf180
Starting Member

42 Posts

Posted - 2004-06-08 : 09:34:19

I'm back to attempting to trouble shoot my 7.0 to 2000 migration. My only problem is the difference in the way stored procedures is executed. Watching with the profiler I can see that in SQL 7 the variables are not padded and in SQL 2000 they are. Even when I turn padding off in SQL 2000 it seems that the Named Pipes routine turns them back on. What confuses me even more is that padding is enabled in SQL 7 and everything works just fine there.

SQL 7 from profiler:
sp_Get_Beneficiary_Claim_Detai '000-00-0000', 102, 'D', '', '', ''

SQL 2000 from profiler:
exec sp_Get_Beneficiary_Claim_Details '000-00-0000', 102, 'D', ' ', '', ''

As you can see the 4th argument becomes padded since it remainned null durning the SP execution.

Any ideas on what could be causing this problem? I would really like to be able to get the SQL2000 EXEC to act the same as the SQL 7.

This is the stored procedure, it is executed by a VB app:

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) -- POC 10/4/00
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
Primary_SSN = @Primary_SSN
AND
Suffix = @Suffix
AND
Benefit_ID LIKE @Benefit_ID + '%'
AND
Procedure_ID LiKE @Benefit_Sub_Type + '%'
AND
Tooth_ID LIKE @Tooth_ID + '%'
AND
D.Status like @Status + '%' -- POC 10/4/00
ORDER BY
D.Service_Date DESC
RETURN @@ERROR

Knarf180
Starting Member

42 Posts

Posted - 2004-06-08 : 11:53:04

One other thing that I dont understand is that the 4th variable is a varchar(4), and the 5th is a varchar(10), yet only the 4th variable is having the problem. It just doesnt make sence.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources