September 1, 2008 at 8:06 am
I took the SP which is prefixing with
GO
SET ANSI_NULLS OFF
GO
in ms sql server 2000.
i executed the same in ms sql server 2005.
But SP got was changed to
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
Because of this i am getting the Different result.
How to resolve this?
September 2, 2008 at 3:35 am
SET ANSI_NULLS Specifies SQL-92 compliant behavior of the Equals (=) and Not Equal to (<>) comparison operators when used with null values.
Can change the results if the column(s) used in where clause have NULL values.
Check it here...
http://doc.ddart.net/mssql/sql70/set-set_5.htm">
http://doc.ddart.net/mssql/sql70/set-set_5.htm
Atif Sheikh
September 3, 2008 at 10:13 pm
Hi,
i could not able to open the link which u sent.
How can i change and handle the Null Values in MS SQL Server 2005 if
SET ANSI_NULLS ON?
There is no option to make it OFF---SET ANSI_NULLS OFF in MS SQL Server 2005 ?.
i am having around 200 SPs with SET ANSI_NULLS OFF in MS SQL Server 2000.I need to migrate all SPs to MS SQL Server 2005.IF i do so,
all SPs defaulted to SET ANSI_NULLS ON in MS SQL Server 2005 beacuse of this getting different result set.
What can be done?
Whether we can try to analyse how to make SET ANSI_NULLS OFF
or How to handle the null values in MS SQL Server 2005?
Can direct me if anything is possible?
i am pasting the SPs which is in MS SQL Server 2000
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE dp_GetTest
(
@title varchar(255),
@keyword varchar(255),
@develop int,
@publish_date varchar(10),
@page_index int,
@page_size int,
@rec_count int = 0 OUTPUT,
@status_code int = 0 OUTPUT,
@error_msg varchar(500) = NULL OUTPUT
)
AS
DECLARE
@strQuery nvarchar(2048),
@strCondition nvarchar(2048),
@strCountQuery nvarchar(2000),
@news_mon int,
@news_year int,
@fromDate datetime,
@first_rec int,
@last_rec int,
@andFlag bit
SET @first_rec = (@page_index - 1) * @page_size
SET @last_rec = (@page_index * @page_size + 1)
SET @status_code = @@Error
SET @error_msg = "Success"
SET @andFlag = 0
IF @title <> NULL AND @title <> ''
BEGIN
SET @strCondition = " WHERE [fasttrack].[Headline] like '" + RTRIM(CAST(@title AS VARCHAR))+ "%' "
SET @andFlag = 1
END
IF @keyword <> NULL AND @keyword <> ''
BEGIN
IF @andFlag = 0
BEGIN
SET @strCondition = " WHERE [fasttrack].[Article] like '" + RTRIM(CAST(@keyword AS VARCHAR))+ "%' "
SET @andFlag = 1
END
ELSE
BEGIN
SET @strCondition = @strCondition + " AND [fasttrack].[Article] like '" + RTRIM(CAST(@keyword AS VARCHAR))+ "%' "
SET @andFlag = 1
END
END
IF @develop <> 0
BEGIN
SELECT @fromDate = DATEADD(DAY,-CONVERT(INT,@develop,3),GetDate())
IF @andFlag =0
BEGIN
SET @strCondition = " WHERE PublishDate >= '" + CONVERT(VARCHAR, @fromDate, 120) + "' "
SET @andFlag = 1
END
ELSE
BEGIN
SET @strCondition = @strCondition + " AND PublishDate >= '" + CONVERT(VARCHAR, @fromDate, 120) + "' "
SET @andFlag = 1
END
END
ELSE
BEGIN
IF @publish_date <> NULL AND @publish_date <> ''
BEGIN
SELECT @news_mon = DATEPART(MONTH,CONVERT(DATETIME,@publish_date,112))
SELECT @news_year = DATEPART(YEAR,CONVERT(DATETIME,@publish_date,112))
IF @andFlag = 0
BEGIN
SET @strCondition = " WHERE DATEPART(month, PublishDate) = " + CAST(@news_mon AS VARCHAR)
SET @strCondition = @strCondition + " AND DATEPART(year, PublishDate) = " + CAST(@news_year AS VARCHAR)
SET @andFlag = 1
END
ELSE
BEGIN
SET @strCondition = @strCondition + " AND DATEPART(month, PublishDate) = " + CAST(@news_mon AS VARCHAR)
SET @strCondition = @strCondition + " AND DATEPART(year, PublishDate) = " + CAST(@news_year AS VARCHAR)
SET @andFlag = 1
END
END
END
PRINT @strCondition
IF @status_code = 0
BEGIN
SET ROWCOUNT 0
BEGIN TRANSACTION GetfasttrackTran
IF exists (
SELECT *
FROM dbo.sysobjects
WHERE id = object_id(N'[dbo].[#Temp_fasttrack]')
)
DROP TABLE [dbo].[#Temp_fasttrack]
CREATE TABLE #Temp_fasttrack
(
Temp_ID int IDENTITY,
Counter float,
PublishDate smalldatetime,
Headline nvarchar(255),
)
SET @status_code = @@Error
IF @status_code = 0
BEGIN
SET @strQuery = "INSERT INTO #Temp_fasttrack (Counter, PublishDate, Headline) " +
"SELECT Counter, PublishDate, Headline " +
"FROM fasttrack " + @strCondition +
" ORDER BY fasttrack.PublishDate DESC"
EXECUTE sp_executesql @strQuery
SET @rec_count = @@ROWCOUNT
SET @status_code = @@Error
END
IF @status_code = 0
BEGIN
SELECT Counter,convert(char(12), PublishDate, 3),Headline
FROM #Temp_fasttrack
WHERE Temp_ID > @first_rec
AND Temp_ID < @last_rec
SET @status_code = @@Error
END
IF exists (
SELECT *
FROM dbo.sysobjects
WHERE id = object_id(N'[dbo].[#Temp_fasttrack]')
)
DROP TABLE [dbo].[#Temp_fasttrack]
IF @status_code <> 0
ROLLBACK TRANSACTION GetfasttrackTran
ELSE
COMMIT TRANSACTION GetfasttrackTran
END
IF @status_code <> 0
SELECT @error_msg = description from master..sysmessages WHERE error = @status_code
RETURN @@Error
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
After Migration SPs which to MS SQL Server 2005
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
CREATE PROCEDURE dp_GetTest
(
@title varchar(255),
@keyword varchar(255),
@develop int,
@publish_date varchar(10),
@page_index int,
@page_size int,
@rec_count int = 0 OUTPUT,
@status_code int = 0 OUTPUT,
@error_msg varchar(500) = NULL OUTPUT
)
AS
DECLARE
@strQuery nvarchar(2048),
@strCondition nvarchar(2048),
@strCountQuery nvarchar(2000),
@news_mon int,
@news_year int,
@fromDate datetime,
@first_rec int,
@last_rec int,
@andFlag bit
SET @first_rec = (@page_index - 1) * @page_size
SET @last_rec = (@page_index * @page_size + 1)
SET @status_code = @@Error
SET @error_msg = "Success"
SET @andFlag = 0
IF @title <> NULL AND @title <> ''
BEGIN
SET @strCondition = " WHERE [fasttrack].[Headline] like '" + RTRIM(CAST(@title AS VARCHAR))+ "%' "
SET @andFlag = 1
END
IF @keyword <> NULL AND @keyword <> ''
BEGIN
IF @andFlag = 0
BEGIN
SET @strCondition = " WHERE [fasttrack].[Article] like '" + RTRIM(CAST(@keyword AS VARCHAR))+ "%' "
SET @andFlag = 1
END
ELSE
BEGIN
SET @strCondition = @strCondition + " AND [fasttrack].[Article] like '" + RTRIM(CAST(@keyword AS VARCHAR))+ "%' "
SET @andFlag = 1
END
END
IF @develop <> 0
BEGIN
SELECT @fromDate = DATEADD(DAY,-CONVERT(INT,@develop,3),GetDate())
IF @andFlag =0
BEGIN
SET @strCondition = " WHERE PublishDate >= '" + CONVERT(VARCHAR, @fromDate, 120) + "' "
SET @andFlag = 1
END
ELSE
BEGIN
SET @strCondition = @strCondition + " AND PublishDate >= '" + CONVERT(VARCHAR, @fromDate, 120) + "' "
SET @andFlag = 1
END
END
ELSE
BEGIN
IF @publish_date <> NULL AND @publish_date <> ''
BEGIN
SELECT @news_mon = DATEPART(MONTH,CONVERT(DATETIME,@publish_date,112))
SELECT @news_year = DATEPART(YEAR,CONVERT(DATETIME,@publish_date,112))
IF @andFlag = 0
BEGIN
SET @strCondition = " WHERE DATEPART(month, PublishDate) = " + CAST(@news_mon AS VARCHAR)
SET @strCondition = @strCondition + " AND DATEPART(year, PublishDate) = " + CAST(@news_year AS VARCHAR)
SET @andFlag = 1
END
ELSE
BEGIN
SET @strCondition = @strCondition + " AND DATEPART(month, PublishDate) = " + CAST(@news_mon AS VARCHAR)
SET @strCondition = @strCondition + " AND DATEPART(year, PublishDate) = " + CAST(@news_year AS VARCHAR)
SET @andFlag = 1
END
END
END
PRINT @strCondition
IF @status_code = 0
BEGIN
SET ROWCOUNT 0
BEGIN TRANSACTION GetfasttrackTran
IF exists (
SELECT *
FROM dbo.sysobjects
WHERE id = object_id(N'[dbo].[#Temp_fasttrack]')
)
DROP TABLE [dbo].[#Temp_fasttrack]
CREATE TABLE #Temp_fasttrack
(
Temp_ID int IDENTITY,
Counter float,
PublishDate smalldatetime,
Headline nvarchar(255),
)
SET @status_code = @@Error
IF @status_code = 0
BEGIN
SET @strQuery = "INSERT INTO #Temp_fasttrack (Counter, PublishDate, Headline) " +
"SELECT Counter, PublishDate, Headline " +
"FROM fasttrack " + @strCondition +
" ORDER BY fasttrack.PublishDate DESC"
EXECUTE sp_executesql @strQuery
SET @rec_count = @@ROWCOUNT
SET @status_code = @@Error
END
IF @status_code = 0
BEGIN
SELECT Counter,convert(char(12), PublishDate, 3),Headline
FROM #Temp_fasttrack
WHERE Temp_ID > @first_rec
AND Temp_ID < @last_rec
SET @status_code = @@Error
END
IF exists (
SELECT *
FROM dbo.sysobjects
WHERE id = object_id(N'[dbo].[#Temp_fasttrack]')
)
DROP TABLE [dbo].[#Temp_fasttrack]
IF @status_code <> 0
ROLLBACK TRANSACTION GetfasttrackTran
ELSE
COMMIT TRANSACTION GetfasttrackTran
END
IF @status_code <> 0
SELECT @error_msg = description from master..sysmessages WHERE error = @status_code
RETURN @@Error
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply