prblem with dynamic query-urgent | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

prblem with dynamic query-urgent

Hi, I have a problem in writing a dynamic query that pulls data into sql server 2000 from oracle server.here is the query.In this query i got the values of @tue1 and @tue2 from a stored proc which is in sql server.I want to find the data which is in between that @tue1 and @tue8 dynamically. But i am getting error.."Syntax error converting datetime from character string."…even if u se cast function to convert those dates into varchar it says.."ORA-01858 a non-numeric character found where a digit was expected "….please let me know what ia m supposed to do. declare @str varhar(1000)
select @str=’
select C.COURSE_START_DATE_WIU, C.Proj_Cont_WIU, C.Proj_Cont_WIU_Groups from openquery (onlinedatamart,
”
SELECT
SA.COURSE_OFF_START_DATE AS COURSE_START_DATE_WIU,
count(distinct P.IRN)AS Proj_Cont_WIU,
count(distinct SA.Group_id)AS Proj_Cont_WIU_Groups,
””AXIA”” AS Company FROM
DM80.STUDENT_ATTENDED SA,
DM80.PERSON P
WHERE
P.IRN=SA.IRN
AND P.IRN NOT IN (SELECT P.IRN
FROM
DM80.LEAD L,
DM80.PERSON P
WHERE
P.IRN=L.IRN
AND L.ANT_START_DATE BETWEEN ””’ + @tue1 + ””’
AND ””’ + @tue8 + ””’)
AND SA.COURSE_OFF_START_DATE BETWEEN ””01-FEB-2006”” AND ””31-MAR-2006””
GROUP BY SA.COURSE_OFF_START_DATE
”) as C’
exec (@str)
Let me make sure I understand this properly. You are trying to retrieve saved data in ORACLE, and insert it into a SQL Server Database Table? If this is correct, I am assuming that you are trying to execute T-SQL inside an Oracle environment? Don’t think that’s possible in your case. You might have to go about it a different way to save your data. Is there a linked server on your SQL Machine allowing you to view the Oracle DB? I think you are getting an error on the Oracle side on your retrieve portion. .."ORA-01858 a non-numeric character found where a digit was expected "…. is an Oracle error message, and to convert the data, you have to use the to_char function.
Hi, I am running this query on sql server side and oracle is linked to this server. You are right..that is a oracle error message and the error is that it couldn’t convert the datetime value into varchar ar run time…so i changed code a bit by declaring @tue1 and @tue8 as varchar in the begining and changed the way the date format so that it cud fit in oracle by using
the below code and finally it worked…..
@tue1 = upper(replace(convert(varchar(11),@FirstTue,106), ‘ ‘,’-‘)) Thanks a lot for taking time in helping me with the query. Smiles,
Vani.
What values did you assign to the variables @tue1 and @tue8? Madhivanan Failing to plan is Planning to fail
Hi, I called a stored proc to get those values in sql server 2000.But the date format which sql returns is different from oracle…that is the reason i changed the format as per oracle dateformats and it worked. Thanks a lot for responsding,
vani.
]]>