tazarvmmr
سه شنبه 05 مهر 1390, 04:19 صبح
سلام به همه
یک sp که در sql2005 نوشته شده و در خود محیط sql درست اجرا میشه، در محیط دلفی وقتی از کامپوننت TADOStoredProc استفاده میکنم خطای Syntax Error or Access violation میده ولی همین sp وقتی با کامپوننت TSQLStoredProc فراخوانی میشه درست کار میکنه!
کسی این مشکل رو داشته؟
این هم کد فراخوانی sp در دلفی:
DMMain.sp_UserProjects.Close;
DMMain.sp_UserProjects.Prepared := true;
DMMain.sp_UserProjects.Parameters.ParamByName('@Us erCode').Value := CRMUser.UserCode;
DMMain.sp_UserProjects.Parameters.ParamByName('@Ke yword').Value := null;
DMMain.sp_UserProjects.Open;
frmMain.UserProjects1.Visible := true;
اینم خود sp:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[sp_GetUserAllProjects-np]
@UserCode int ,
@Keyword nvarchar(100) = null
As
if @keyword is null set @keyword='';
SELECT RequestedProjects.RequestProjectName, RequestedProjects.Id AS RequestId, RequestedProjects.Allowed, RequestedProjects.RequestDate,
RequestedProjects.AllowDate, ProjectNames.ProjectStartDate, ProjectIndexes.IndexCaption, ProjectSteps.ProjectStepCaption, ProjectReasons.ReasonCaption,
Users.UserName AS NextUserName, ProjectNames.Id AS ProjectId, ProjectNames.ProjectNextUserCode AS NextUserCode,
RequestedProjects.ProjectIsWaitingToConfirm, RequestedProjects.ProjectIsStartedByUser, Contacts.ContactDate AS ProjectLastContactDate
FROM ProjectSteps RIGHT OUTER JOIN
Contacts RIGHT OUTER JOIN
ProjectNames ON Contacts.Id = ProjectNames.ProjectLastContactId LEFT OUTER JOIN
Users ON ProjectNames.ProjectNextUserCode = Users.Id LEFT OUTER JOIN
ProjectReasons ON ProjectNames.ProjectReasonId = ProjectReasons.Id ON ProjectSteps.Id = ProjectNames.ProjectStepId LEFT OUTER JOIN
ProjectIndexes ON ProjectNames.ProjectIndexId = ProjectIndexes.Id RIGHT OUTER JOIN
RequestedProjects ON ProjectNames.RequestId = RequestedProjects.Id
WHERE ((ProjectNames.ProjectNextUserCode = CONVERT(nvarchar(10), @UserCode) )or (RequestedProjects.RequestedUserCode = CONVERT(nvarchar(10), @UserCode) )) AND (RequestedProjects.ProjectIsWaitingToConfirm = 0) AND (RequestedProjects.Allowed = 1) AND
(RequestedProjects.ProjectIsStartedByUser = 1) AND (Replace(RequestedProjects.RequestProjectName, ' ', '') LIKE N'%'+ Replace(@keyWord , ' ', '') + '%')
return
یک sp که در sql2005 نوشته شده و در خود محیط sql درست اجرا میشه، در محیط دلفی وقتی از کامپوننت TADOStoredProc استفاده میکنم خطای Syntax Error or Access violation میده ولی همین sp وقتی با کامپوننت TSQLStoredProc فراخوانی میشه درست کار میکنه!
کسی این مشکل رو داشته؟
این هم کد فراخوانی sp در دلفی:
DMMain.sp_UserProjects.Close;
DMMain.sp_UserProjects.Prepared := true;
DMMain.sp_UserProjects.Parameters.ParamByName('@Us erCode').Value := CRMUser.UserCode;
DMMain.sp_UserProjects.Parameters.ParamByName('@Ke yword').Value := null;
DMMain.sp_UserProjects.Open;
frmMain.UserProjects1.Visible := true;
اینم خود sp:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[sp_GetUserAllProjects-np]
@UserCode int ,
@Keyword nvarchar(100) = null
As
if @keyword is null set @keyword='';
SELECT RequestedProjects.RequestProjectName, RequestedProjects.Id AS RequestId, RequestedProjects.Allowed, RequestedProjects.RequestDate,
RequestedProjects.AllowDate, ProjectNames.ProjectStartDate, ProjectIndexes.IndexCaption, ProjectSteps.ProjectStepCaption, ProjectReasons.ReasonCaption,
Users.UserName AS NextUserName, ProjectNames.Id AS ProjectId, ProjectNames.ProjectNextUserCode AS NextUserCode,
RequestedProjects.ProjectIsWaitingToConfirm, RequestedProjects.ProjectIsStartedByUser, Contacts.ContactDate AS ProjectLastContactDate
FROM ProjectSteps RIGHT OUTER JOIN
Contacts RIGHT OUTER JOIN
ProjectNames ON Contacts.Id = ProjectNames.ProjectLastContactId LEFT OUTER JOIN
Users ON ProjectNames.ProjectNextUserCode = Users.Id LEFT OUTER JOIN
ProjectReasons ON ProjectNames.ProjectReasonId = ProjectReasons.Id ON ProjectSteps.Id = ProjectNames.ProjectStepId LEFT OUTER JOIN
ProjectIndexes ON ProjectNames.ProjectIndexId = ProjectIndexes.Id RIGHT OUTER JOIN
RequestedProjects ON ProjectNames.RequestId = RequestedProjects.Id
WHERE ((ProjectNames.ProjectNextUserCode = CONVERT(nvarchar(10), @UserCode) )or (RequestedProjects.RequestedUserCode = CONVERT(nvarchar(10), @UserCode) )) AND (RequestedProjects.ProjectIsWaitingToConfirm = 0) AND (RequestedProjects.Allowed = 1) AND
(RequestedProjects.ProjectIsStartedByUser = 1) AND (Replace(RequestedProjects.RequestProjectName, ' ', '') LIKE N'%'+ Replace(@keyWord , ' ', '') + '%')
return