December 17, 2008 at 8:30 pm
Hi There, I have a order interfacing web app that connects to SQL backend.
I performed a sql 2000 to 2005 upgrade on a stand alone server and I began getting extra quotes with my calls to Store-procedures that use dates:
Here is the call from SQL Profiler:(look at the startdt and enddt)
declare @p5 varchar(8000)
set @p5=''
exec OCRCMGetInvalidProductId @ProductId='',@StoreId='3-3024100',@StartDt=''2008-11-16 00:00:00:000'',@EndDt=''2008-12-27
00:00:00:000'',@output=@p5 output
select @p5
if I move the web app to point to a SQL 2000 instance on a different physical server it works...
declare @p5 varchar(8000)
set @p5=''
exec OCRCMGetInvalidProductId @ProductId='',@StoreId='3-3024100',@StartDt='2008-11-16 00:00:00:000',@EndDt='2008-12-27
00:00:00:000',@output=@p5 output
select @p5
HELP...
Is there a configurable I am missing??
THANKS!!
December 17, 2008 at 8:56 pm
What type of web app is this? (i.e. ASP, .NET, JAVA, PHP...)
If it's reasonable, can you post the code that calls that SP and your configuration settings for the web app?
The Redneck DBA
December 18, 2008 at 6:40 am
I don;t think the problem is in the webcofig but in your procedure. can you post the code of your OCRCMGetInvalidProductId procedure?
[font="Verdana"]Markus Bohse[/font]
December 18, 2008 at 6:41 am
Also try executing the code from profiler in SSMS and see what error you get.
[font="Verdana"]Markus Bohse[/font]
December 18, 2008 at 6:45 am
set ANSI_NULLS ON
set QUOTED_IDENTIFIER OFF
go
ALTER PROCEDURE [dbo].[OCRCMGetInvalidProductId]
(
@ProductId varchar(4000)='',
@StartDt datetime,
@EndDt datetime,
@StoreId varchar(36),
@output varchar(8000) output
)
AS
IF(LEN(@ProductId) > 0)
SELECT @output = coalesce(@output + ',' , '') + col
FROM
(
SELECT DISTINCT col
FROM
fn_OC_ProduceListFromString(@ProductId)
WHERE col NOT IN
(
SELECT DISTINCT p.ProductID
FROM tblProductAuthorization pa JOIN tblProduct p ON pa.ProductID = p.ProductId
JOIN fn_OC_ProduceListFromString(@ProductId) pl ON p.ProductID=pl.col
WHERE pa.StoreId =@StoreId and p.ActiveFlg=1
AND
(
(@StartDt >= pa.EffectiveDt AND @StartDt <= pa.ExpirationDt)
OR
(@EndDt >= pa.EffectiveDt AND @EndDt <= pa.ExpirationDt)
)
)
) t1
ELSE
SELECT @output = '';
December 18, 2008 at 2:02 pm
When you run the sp that is logged by profiler it fails due to the double quotes.
In SQL 2000 though no double quotes show up? only single quotes as it should be
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply