Hi,
Uses: Sql server 2000, asp.net 1.1;
I've the following Stored Procedure:
<SQL Code>
CREATE PROCEDURE PABX_sp_MyPABX_ShowExtWatch
@.FromDate DATETIME,
@.ToDate DATETIME,
@.PortalID INT = 1,
@.UserID INT
AS
SELECT PABX_Ext_Watch.ExtNo As ExtNo, COUNT(*) AS Calls, SUM(PABX_Calls.Rate) AS NetAmt
FROM PABX_Ext_Watch
INNER JOIN PABX_Calls
ON PABX_Ext_Watch.ExtNo = PABX_Calls.ExtNo
INNER JOIN PABX_PortalUsers
ON PABX_Ext_Watch.PortalUserID = PABX_PortalUsers.PortalUserID
AND PABX_Calls.PortalID = PABX_PortalUsers.PortalID
WHERE PABX_Calls.sTime >= CONVERT(DATETIME, @.FromDate, 102)
AND PABX_Calls.eTime <= CONVERT(DATETIME, @.ToDate, 102)
AND PABX_PortalUsers.uID = @.UserID
AND PABX_PortalUsers.PortalID = @.PortalID
GROUP BY PABX_Ext_Watch.ExtNo
GO
</SQL Code>
However if I execute this stored procedure in asp.net I get the following error, despite the stored procedure executed successfully in query analyzer:
Line 1: Incorrect Syntax Near ......
When I check the sql profiler the stored procedure is been executed in this way:
exec sp_executesql N'PABX_sp_MYPABX_ShowExtWatch', N'@.FromDate datetime,@.ToDate datetime,@.UserID int,@.PortalID int', @.FromDate = 'Sep 1 2006 12:00:00:000AM', @.ToDate = 'Sep 30 2006 12:00:00:000AM', @.UserID = 207, @.PortalID = 2
I couldn't figure it out though sql server management console and query analyzer showed no signs of errors. I sense that the sql is not being executed correctly with letters as "N" in the sql profiler.
Could anyone help me out on this?
Thanks & Regards,
Try and execute the procedure like this
execute PABX_sp_MYPABX_ShowExtWatch @.FromDate = 'Sep 1 2006 12:00:00:000AM', @.ToDate = 'Sep 30 2006 12:00:00:000AM', @.UserID = 207, @.PortalID = 2
|||You might try converting(smalldatetime,@.FromDate,102) instead of datetime.|||Hi,
Thanks for both of your responses, but the problem still remains even after trying out your suggestions. This is the way I have wrote the code in asp.net:
<code>
PublicFunction ShowExtWatchList(ByVal UserID AsInteger, ByVal FromDate As DateTime, ByVal Todate As DateTime) As SqlDataReader
Dim oDr As SqlDataReader
Dim oCmd AsNew SqlCommand("PABX_sp_MyPABX_ShowExtWatch", oComConn)
With oCmd
.Parameters.Add(New SqlParameter("@.FromDate", SqlDbType.DateTime)).Value = FromDate
.Parameters.Add(New SqlParameter("@.ToDate", SqlDbType.DateTime)).Value = Todate
.Parameters.Add(New SqlParameter("@.UserID", SqlDbType.Int)).Value = UserID
.Parameters.Add(New SqlParameter("@.PortalID", SqlDbType.Int)).Value = _PortalID
EndWith
If oComConn.State = ConnectionState.Open Then
oComConn.Close()
EndIf
oComConn.Open()
oDr = oCmd.ExecuteReader(CommandBehavior.CloseConnection)
If oDr.HasRows Then
Return oDr
Else
ReturnNothing
EndIf
EndFunction
</code>
However one thing really kicks me. I checked that those stored procedures which didnt give errors (another) and successfully executed without any problems showed in the profiler in this pattern (for example):
exec PABX_sp_MyPABX_DepHead_Summary @.FromDate = 'Sep 1 2006 12:00:00:000AM', @.ToDate = 'Sep 30 2006 12:00:00:000AM', @.DepHeadID = 207, @.PortalID = 1
but the the one which gives error showed in the profiler as:
exec sp_executesql N'PABX_sp_MyPABX_ShowExtWatch', N'@.FromDate datetime,@.ToDate datetime,@.UserID int,@.PortalID int', @.FromDate = 'Sep 1 2006 12:00:00:000AM', @.ToDate = 'Sep 30 2006 12:00:00:000AM', @.UserID = 207, @.PortalID = 1
Look at the 2 different executions of SPs. I think ,at some point, sql server doesn't recognise this as a SP and doesn't executes in some other way instead of the usuall way or asp.net sends in the wrong way to execute....yet the T-sqls are correct. Is this a bug?
|||Hi,
I figured it out what was the cause. It's in Asp.net itself and not in Sql server neither tsql. I've missed out this piece of code which caused the entire problem:
"SqlCommand.CommandType = CommandType.StoredProcedure"
