Datatype issue sql 2005 to sql 2000

i have a website written in asp.net c#. it was using a sql 2005 db but due to requirements we have ensure it's compatible with a sql 2000 db. everything on the site converted ok except for one page that is using a stored procedure to read in a marquee. the @Url_FK variable is no longer being passed to the page via the stored proc. any assistance would be helpful. thanks!

The only difference in the table design is 2005 uses nvarchar(max) and 2000 uses varchar(8000) for the TOD_Text field.

sto开发者_如何学Cred proc code 2005:

USE [CSF]
GO
/****** Object:  StoredProcedure [dbo].[sp_test]    Script Date: 12/06/2010 11:56:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[sp_test]
(
    @Url_FK int
)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
SELECT     TOP (1) TOD_Text, Url_FK, TOD_Date
FROM         CSF_TOD
WHERE     (Url_FK = @Url_FK) AND (TOD_Date <= GETDATE())
ORDER BY TOD_Date DESC
END

stored proc code 2000:

-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[sp_test]
(
    @Url_FK int
)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
SELECT     TOP 1 TOD_Text, Url_FK, TOD_Date
FROM         CSF_TOD
WHERE     (Url_FK = @Url_FK) AND (TOD_Date <= GETDATE())
ORDER BY TOD_Date DESC
END

GO

---- code in aspx page calling stored proc

   <asp:SqlDataSource ID="sds_TOD" runat="server" ConnectionString="<%$ ConnectionStrings:TESTConnectionString %>" SelectCommand="sp_TEST" SelectCommandType="StoredProcedure">
    <SelectParameters>
    <asp:Parameter DefaultValue="1" Name="Url_FK" Type="Int32" />

Try this:

SELECT     TOP 1 cast(TOD_Text as varchar(8000)) as TOD_Text, Url_FK, TOD_Date 
FROM         CSF_TOD 
WHERE     (Url_FK = @Url_FK) AND (TOD_Date <= GETDATE()) 
ORDER BY TOD_Date DESC 
END 

In your database the column TOD_Text may be having data greater than 8000 characters in CSF_TOD table. Check that, if yes then remove those rows and try again with sql 2000.

Based on your comment, you'll need to change the varchar(8000) field to a text column in sql 2000. A row cannot exceed 8060 bytes, unless you use the text column.