Msg 102, Level 15, State 1, Line 1

Question

Post reply

Msg 102, Level 15, State 1, Line 1

a475668

SSC Veteran

Points: 205
More actions
October 14, 2010 at 10:07 am

#388006

Hi Can anyone help me.
I get this erro:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '.'.
when i execute the below code.
ALTER procedure [spOutputFinalFile] as
set nocount on
Declare @sql VarChar(7000)
Declare @SQL1 VarChar(7000)
select @sql = 'bcp dbname..[tablename_2010] out'
select @sql = @sql + ' "c:\Program files\FinalOutput_DONOTDELETE\company_Ppl_'
select @SQL1 = @sql + '.xls" -c -t, -T'

Viewing 6 posts - 1 through 5 (of 5 total)

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

scott.pletcher SSCertifiable Points: 5958 More actions · Answer 1

To run bcp -- or any other .exe from SQL -- you need to have an environment that the .exe can run in.

For example, you could do something like this:

EXEC xp_cmdshell @bcp_cmd

Scott Pletcher, SQL Server MVP 2008-2010

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

a475668 (10/14/2010)
Hi Can anyone help me.
I get this erro:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '.'.
when i execute the below code.
ALTER procedure [spOutputFinalFile] as
set nocount on
Declare @sql VarChar(7000)
Declare @SQL1 VarChar(7000)
select @sql = 'bcp dbname..[tablename_2010] out'
select @sql = @sql + ' "c:\Program files\FinalOutput_DONOTDELETE\company_Ppl_'
select @SQL1 = @sql + '.xls" -c -t, -T'

Running this:

Declare @sql VarChar(7000)

Declare @SQL1 VarChar(7000)

select @sql = 'bcp dbname..[tablename_2010] out'

select @sql = @sql + ' "c:\Program files\FinalOutput_DONOTDELETE\company_Ppl_'

select @SQL1 = @sql + '.xls" -c -t, -T'

print @SQL1

-- bcp dbname..[tablename_2010] out "c:\Program files\FinalOutput_DONOTDELETE\company_Ppl_.xls" -c -t, -T

doesn't generate an error. As Scott has pointed out, how are you executing the string?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

a475668 SSC Veteran Points: 205 More actions · Answer 3

When I do a select @SQL2 - i see the sql statement. Whe i paste the same sql statement in command promt, it runs fine and generates the outfile file the location specified.

the error comes only when i try to execute this via the stored proc.

a475668 SSC Veteran Points: 205 More actions · Answer 4

I got it...you got it...exec exshell worked....great1 thanks!

scott.pletcher SSCertifiable Points: 5958 More actions · Answer 5

Correction, I forgot one thing:

EXEC master.dbo.xp_cmdshell @bcp_cmd

That way you can run it from any db.

Scott Pletcher, SQL Server MVP 2008-2010