Incorrect syntax near 'c:'

Question

this a sample of test SP i was created befor

when i run it "Incorrect syntax near 'c:'."

create PROCEDURE [dbo].[TextImpotedSP2]

AS

set @path = 'c:temp\TextImpoted0.txt'

SET NOCOUNT ON;

DECLARE @bulk_cmd NVARCHAR(max);

SET @bulk_cmd = 
'BULK 
INSERT TextImpoted  FROM '+ @path1 + ';  WITH (FIELDTERMINATOR = ' + ';'   ',ROWTERMINATOR = ' + '\n'+ ' )';


EXEC sp_executesql @bulk_cmd


--*************************

any one have any idea?

Answer 1

There seem to be several issues. First, the variable path is never declared, and it's also incorrectly referred to as path1 which needs to be changed. Second, some of the values in the bulk_cmd string needs to be quoted.

A corrected version that should work:

CREATE PROCEDURE [dbo].[TextImpotedSP2]
AS
DECLARE @path NVARCHAR(max)
SET @path = 'c:\temp\TextImpoted0.txt'

SET NOCOUNT ON;
DECLARE @bulk_cmd NVARCHAR(max);

SET @bulk_cmd = '
BULK INSERT TextImpoted 
FROM '''+ @path + ''' 
WITH 
    (
       FIELDTERMINATOR = ' + ''';''' +  ',
       ROWTERMINATOR = ' + '''\n'''+ ' 
    )';

--PRINT @bulk_cmd
EXEC sp_executesql @bulk_cmd

Also, the path string 'c:temp\TextImpoted0.txt' should probably be 'c:\temp\TextImpoted0.txt' as you might get unexpected results if you use a relative path.

Edit: added the solution to the follow-up question that were posted as an answer...

CREATE PROCEDURE [dbo].[TextImpoted01]
AS
SET NOCOUNT ON;

DECLARE @SQL NVARCHAR(100) 
SELECT TOP (1) @SQL = total  FROM DealCounter

DECLARE @path NVARCHAR(max)
SET @path = 'c:\temp\TextImpoted'+ @SQL + '.txt'    

DECLARE @bulk_cmd NVARCHAR(max);    
SET @bulk_cmd = '
BULK INSERT TextImpoted 
FROM '''+ @path + ''' 
WITH 
    (
       FIELDTERMINATOR = ' + ''';''' +  ',
       ROWTERMINATOR = ' + '''\n'''+ ' 
    )';

--PRINT @bulk_cmd
EXEC sp_executesql @bulk_cmd

Answer 2

change

set @path = 'c:temp\TextImpoted0.txt'

to

set @path = 'c:\temp\TextImpoted0.txt'

the \ is important