传入的表格数据流(TDS)远程过程调用(RPC)协议流不正确.RPC名称无效

Question

我正在处理一个存储过程，如果存在日期，则获取记录else else last 90天数据过程正常工作，但是如果我通过代码发送输入，我正在获取传入的表格数据流(TDS(远程过程调用(RPC(协议流不正确。

这是我的过程：

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[get_audit_list_for_sites_list_enh]
    (@site_list VARCHAR(MAX),
     @ip_audit_dttm_from DATETIME = NULL,
     @ip_audit_dttm_to DATETIME = NULL)
/**************************************************************************************************
* Name           : get_accepted_terms_da_user
*
* Description    : This Procedure is to get the user activity, user, admin etc details for auditing
*
* Author         : Sandeep (rajsa06)
*
* Date Written   : 11th Jan 2017
*
***************************************************************************************************
* Called By
* None.
*
* Objects                          S         I        U       D       E
* dbo.user_info                    X
* dbo.user_audit                   X
*
* Called Objects
* None.
**************************************************************************************************/
AS 
BEGIN
    IF (@ip_audit_dttm_from IS NULL AND  @ip_audit_dttm_to IS NULL)
    BEGIN
        DECLARE @t_sites TABLE (site_id INTEGER NOT NULL PRIMARY KEY)
        INSERT INTO @t_sites (site_id)
            SELECT integer_id
            FROM dbo.dba_tfn_split_commaseparated_int_string(@site_list)
        SELECT 
            a.site_id AS siteId,
            a.activity AS activity,
            a.activity_desc AS activityDesc,
            a.audit_dttm AS auditDate,
            b.first_name AS userFirstName,
            b.last_name AS userLastName,
            b.login_name AS userEmail,
            CONCAT(c.first_name,' ', c.last_name) AS adminName
        FROM   
            (SELECT * 
             FROM dbo.user_audit 
             WHERE site_id IN (SELECT site_id FROM @t_sites) 
               AND audit_dttm >= DATEADD(day, -90, GETDATE())) a
        INNER JOIN 
            dbo.user_info b ON a.user_id = b.user_id
        INNER JOIN 
            dbo.user_info c ON a.da_user_id = c.user_id 
        ORDER BY 
            auditDate DESC
    END
    ELSE
    BEGIN
        INSERT INTO @t_sites (site_id)
            SELECT integer_id
            FROM dbo.dba_tfn_split_commaseparated_int_string(@site_list)
        SELECT  
            a.site_id AS siteId, a.activity AS activity,
            a.activity_desc AS activityDesc, a.audit_dttm AS auditDate,
            b.first_name AS userFirstName, b.last_name AS userLastName,
            b.login_name AS userEmail,
            CONCAT(c.first_name,' ', c.last_name) AS adminName
        FROM   
            (SELECT * FROM dbo.user_audit 
             WHERE site_id IN (SELECT site_id FROM @t_sites) 
               AND audit_dttm >= @ip_audit_dttm_from 
               AND audit_dttm <= @ip_audit_dttm_to) a
        INNER JOIN 
            dbo.user_info b ON a.user_id = b.user_id
        INNER JOIN 
            dbo.user_info c ON a.da_user_id = c.user_id 
        ORDER BY 
            auditDate DESC
    END
END

这是我的node.js代码。

new MsSqlWrapper()
          .runStoredProcedure({
             storedProcedureName:
                constants.storedProcedures.get_audit_list_for_sites_list_enh,
            inputs: [
              {
                name: 'site_list',
                value: siteIdList.data.details
              },
              {
                name: 'ip_audit_dttm_from',
                value: fromDate
              },
              {
                name: 'ip_audit_dttm_to',
                value: toDate
              }
            ]
          })  

和我的输入：

"input": {
            "userId": "336931",
            "connectSiteId": "999999",
            "fromDate": "2009-10-21 08:20:39.477",
            "toDate": "2009-12-21 08:20:39.477"
          }

Answer 1

我有与此相同的错误消息，因为我更改了使用SQL'text'而不是"存储过程"的电话，因此错误的命令类型是可能导致此故障的另一件事。

SqlCommand cmd = new SqlCommand(query, connection);
cmd.CommandType = CommandType.Text;