Msg 102, Level 15, State 1, Line 16

Question

Msg 102, Level 15, State 1, Line 16

babychoo

SSC Veteran

Points: 262
More actions
November 20, 2008 at 9:23 pm

#196109

DECLARE @sql nvarchar(max);
DECLARE @FromDate char(8);
DECLARE @ToDate char(20);
DECLARE @PathFileName varchar(2000)
SET @FromDate = 20080101;
SET @ToDate = CONVERT(CHAR(20),GETDATE(),112);
SET @PathFileName = 'D:\SalesHis\SALES.'+@FromDate+'%''.txt'
IF @ToDate > @FromDate
BEGIN
SELECT @sql = 'BULK INSERT [SalesHis].[DBO].[Sales] FROM '+@PathFileName+'
WITH (FIELDTERMINATOR = '''', ROWTERMINATOR = ''' + nchar(10) + ''')';
EXEC (@SQL);
@FromDate+1;
END
Hi all i need help on it
Error Message :
Msg 102, Level 15, State 1, Line 16
Incorrect syntax near '@FromDate'.

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

The error's coming from the line after the exec, because there's a variable with no assignment that's sitting there. That said, there are some other more minor issues with the code.

Try this. Test it, I haven't because I don't have your data.

DECLARE @SQL nvarchar(max);

DECLARE @FromDate DATETIME;

DECLARE @ToDate DATETIME;

DECLARE @PathFileName varchar(2000)

SET @FromDate = CAST('20080101' AS DATETIME);

SET @ToDate = GETDATE();

SET @PathFileName = 'D:\SalesHis\SALES.'+CONVERT(CHAR(8),@FromDate,112)+'%''.txt'

IF @ToDate > @FromDate

BEGIN

SELECT @SQL = 'BULK INSERT [SalesHis].[DBO].[Sales] FROM '+@PathFileName+'

WITH (FIELDTERMINATOR = '''', ROWTERMINATOR = ''' + nchar(10) + ''')';

EXEC (@SQL);

SET @FromDate = DATEADD(dd,1,@FromDate)

END

Is that if supposed to be an if, or a while?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

DECLARE @SQL nvarchar(max);

DECLARE @FromDate char(8);

DECLARE @ToDate char(20);

DECLARE @PathFileName varchar(2000)

SET @FromDate = 20080101;

SET @ToDate = CONVERT(CHAR(20),GETDATE(),112);

SET @PathFileName = 'D:\SalesHis\SALES.'+@FromDate+'%''.txt'

IF @ToDate > @FromDate

BEGIN

SELECT @SQL = 'BULK INSERT [SalesHis].[DBO].[Sales] FROM '+@PathFileName+'

WITH (FIELDTERMINATOR = '''', ROWTERMINATOR = ''' + nchar(10) + ''')';

EXEC (@SQL);

set @FromDate = @FromDate + 1; -- but this still won't work

END

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Lynn Pettis

SSC Guru

Points: 442467

November 20, 2008 at 9:44 pm

#901431

Nevermind, Gail beat me to it.....

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 4

Lynn Pettis (11/20/2008)
Nevermind, Gail beat me to it.....

😀

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

babychoo SSC Veteran Points: 262 More actions · Answer 5

Sorry, new error message come out.......

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near 'D'.

Msg 105, Level 15, State 1, Line 3

Unclosed quotation mark after the character string ')'.

Have any idea?

babychoo SSC Veteran Points: 262 More actions · Answer 6

My Text File format is Sales.YYYYMMDDHHMMSS.txt

So my plan is running a query that can loop from 20080101 till today so i can import all the data to the database.

Please Help Urgent.................

babychoo SSC Veteran Points: 262 More actions · Answer 7

DECLARE @sql nvarchar(max);

DECLARE @FromDate char(8);

DECLARE @ToDate char(20);

DECLARE @PathFileName varchar(2000);

SET @FromDate = 20080101;

SET @ToDate = CONVERT(CHAR(20),GETDATE(),112);

SET @PathFileName = 'D:\SalesHis\SALES.'+@FromDate+'%''.txt';

WHILE (@ToDate > @FromDate)

SELECT @sql = 'BULK INSERT [SalesHis].[DBO].[Sales] FROM '+@PathFileName+'

WITH (FIELDTERMINATOR = '''', ROWTERMINATOR = ''' + nchar(10) + ''')';

IF (@ToDate > @FromDate)

EXEC (@SQL);

END

SET @FromDate = @FromDate+1; OR RETURN (@FromDate++) OR RETURN (@FromDate+1)

END

Still have error. Need Help try many way also same.

Error message

Msg 156, Level 15, State 1, Line 16

Incorrect syntax near the keyword 'SET'.

Msg 102, Level 15, State 1, Line 17

Incorrect syntax near 'END

Anyone have idea to solve my problem???:crying:

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

Two things, unfortunately I don't have time to actually write it myself as I am trying to leave for work.

You need to convert the From datetime to a character string in your SET statement. Look at CONVERT in BOL. You will also need to use a couple of replace statements with the convert to eliminate the spaces and ":" when converting the From datetime to a character string.

You also need to copy the SET statementinto your while loop after the increment of the From datatime.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

I have taken a closer look at your code, and it won't work. I just noticed the wildcard you are trying to use in the BUL INSERT pathname, and that is why it will fail.

You should look at using SSIS to import the files. You can use a FOR EACH container with a File System Task to identify each of the files that needs to be imported.