Msg 102，第15级，状态1，第1行错误语法，在'D:'附近

I trying to call a stored procedure:

我尝试调用一个存储过程:

CREATE PROCEDURE [dbo].[uspImportDepartment2]
    @filePath nvarchar(255),
    @formatPath varchar(255),
    @countInsert Int OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @sqlstmt nvarchar(255)
    DECLARE @results table (result xml)

    --Build the Dynamic SQL Statement to get the data from the xml file
    SET @sqlstmt= N'SELECT * FROM OPENROWSET ( BULK ' + @filePath + ', FORMATFILE=''' + @formatPath + ''', FIRSTROW=1, MAXERRORS=0)AS xmlData'

    -- Insert the results of the dynamic SQL Statement into the temporary table variable.
    INSERT INTO @results EXEC (@sqlstmt)

    select @countInsert = count(*) from @results

    --DECLARE @xmlDoc XML
    --SELECT @xmlDoc = result FROM @results
END
GO

And execution code:

和执行代码:

DECLARE @count1 int

EXEC [dbo].[uspImportDepartment2] @filePath = 'D:\test_1.txt',
           @formatPath = 'D:\test_1_c.fmt', @countInsert = @count1 OUTPUT

SELECT @count1 as AAAA

But it throws an error:

但它犯了一个错误:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'D:'.

Msg 102，第15级，状态1，第1行错误的语法，在'D:'附近。

Can somebody tell me what the problem is?

谁能告诉我是什么问题吗?

Thanks!

谢谢!

2 个解决方案

#1

Quotes are missing , write as:

缺少引号，写为:

    SET @sqlstmt= N'SELECT xmlData.* FROM OPENROWSET 
    (BULK ''' + @filePath +''',
     FORMATFILE=''' + @formatPath + ''',
     FIRSTROW=1, MAXERRORS=0
    )AS xmlData'

You can learn more about the syntax of OPENROWSET function from here: http://msdn.microsoft.com/en-IN/library/ms190312(v=sql.100).aspx

您可以从这里了解OPENROWSET函数的语法:http://msdn.microsoft.com/en-IN/library/ms190312(v=sql.100).aspx

#2

I had the same issue. I think that you need a space before "AS"

我也有同样的问题。我认为在"AS"之前你需要一个空格

SET @sqlstmt= N'SELECT * FROM OPENROWSET ( BULK ' + @filePath + ', FORMATFILE=''' + @formatPath + ''', FIRSTROW=1, MAXERRORS=0) AS xmlData'

#1