Msg 102, Level 15, State 1, Line 1

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Msg 102, Level 15, State 1, Line 1

Author

Topic

reading2009
Starting Member

22 Posts

Posted - 2010-10-14 : 09:11:52

Hi when i execute the below stored proc, i get the error;
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '.'.

please advice where is the syntax error.


ALTER procedure [dbo].[spOutputFinalFile]
as

--set nocount on
declare @devicename varchar(50)
declare @daterange varchar(50)
Declare @SQL VarChar(7000)
Declare @SQL1 VarChar(7000)
Declare @vary1 varchar(7000)
Declare @final1 varchar(7000)
Declare @SQL2 VarChar(7000)
Declare @vary2 varchar(7000)
Declare @final2 varchar(7000)
Declare @vary3 VarChar(7000)
Declare @SQL3 VarChar(7000)
declare @TodayDate nvarchar(10)
declare @TodayMonth nvarchar(10)
declare @TodayYear nvarchar(10)
declare @TodayHour nvarchar(10)
declare @TodayMinute nvarchar(10)
declare @TodaySecond nvarchar (10)
declare @NewFileName nvarchar(1000)

SELECT @TodayDate = Right('0' + DATEPART (dd,GETDATE()),2) 
SELECT @TodayDate = Right('00', 2 - Len(@TodayDate)) + @TodayDate 
SELECT @TodayMonth = DATEPART(mm,GETDATE()) 
SELECT @TodayMonth = Right('00', 2 - Len(@TodayMonth)) + @TodayMonth 
SELECT @TodayYear = DATEPART (yyyy,GETDATE()) 
SELECT @TodayHour = DATEPART(hh,GETDATE())
SELECT @TodayHour = Right('00', 2 - LEN (@TodayHour)) + @TodayHour 
SELECT @TodayMinute = DATEPART(mi,GETDATE()) 
SELECT @TodayMinute = Right('00', 2 - LEN (@TodayMinute)) + @TodayMinute 
SELECT @TodaySecond = DATEPART(ss,GETDATE())
SELECT @TodaySecond = Right('00', 2 - LEN (@TodaySecond)) + @TodaySecond
SELECT @NewFileName = @TodayMonth+'_'+@TodayDate+'_'+@TodayYear+'_'+@TodayHour+@TodayMinute+@TodaySecond
select @SQL2 = 'bcp dbname.dbo.employeetable'
select @SQL2 = @SQL2 + ' "c:\program files\FinalOutput \explore_' 
select @SQL2 = @SQL2 + @NewFileName
select @SQL2 = @SQL2 + '.txt" -c -t, -T' 
--SELECT @SQL2
exec (@SQL2)

Sachin.Nand

2937 Posts

Posted - 2010-10-14 : 09:18:41

What is the o/p of SELECT @SQL2?

PBUH

reading2009
Starting Member

22 Posts

Posted - 2010-10-14 : 09:22:20

[code]
bcp dbname.dbo.employeetable "c:\program files\FinalOutput \explore_10_14_2010_091950.txt" -c -t, -T
[/code]

quote:
Originally posted by Sachin.Nand

What is the o/p of SELECT @SQL2?

PBUH

reading2009
Starting Member

22 Posts

Posted - 2010-10-14 : 09:52:45

anyone, any assistence here?

quote:
Originally posted by reading2009

bcp dbname.dbo.employeetable "c:\program files\FinalOutput \explore_10_14_2010_091950.txt" -c -t, -T

quote:
Originally posted by Sachin.Nand

What is the o/p of SELECT @SQL2?

PBUH

X002548
Not Just a Number

15586 Posts

Posted - 2010-10-14 : 10:15:11

I don't have a problem

Your bcp is malformed though

You need in, out, or queryout



DECLARE    @devicename	varchar(50)
		,  @daterange	varchar(50)
		,  @SQL			varchar(7000)
		,  @SQL1		varchar(7000)
		,  @vary1		varchar(7000)
		,  @final1		varchar(7000)
		,  @SQL2		varchar(7000)
		,  @vary2		varchar(7000)
		,  @final2		varchar(7000)
		,  @vary3		varchar(7000)
		,  @SQL3		varchar(7000)
		,  @TodayDate	nvarchar(10)
		,  @TodayMonth	nvarchar(10)
		,  @TodayYear	nvarchar(10)
		,  @TodayHour	nvarchar(10)
		,  @TodayMinute nvarchar(10)
		,  @TodaySecond nvarchar (10)
		,  @NewFileName nvarchar(1000)

SELECT @TodayDate = Right('0' + DATEPART (dd,GETDATE()),2) 
SELECT @TodayDate = Right('00', 2 - Len(@TodayDate)) + @TodayDate 
SELECT @TodayMonth = DATEPART(mm,GETDATE()) 
SELECT @TodayMonth = Right('00', 2 - Len(@TodayMonth)) + @TodayMonth 
SELECT @TodayYear = DATEPART (yyyy,GETDATE()) 
SELECT @TodayHour = DATEPART(hh,GETDATE())
SELECT @TodayHour = Right('00', 2 - LEN (@TodayHour)) + @TodayHour 
SELECT @TodayMinute = DATEPART(mi,GETDATE()) 
SELECT @TodayMinute = Right('00', 2 - LEN (@TodayMinute)) + @TodayMinute 
SELECT @TodaySecond = DATEPART(ss,GETDATE())
SELECT @TodaySecond = Right('00', 2 - LEN (@TodaySecond)) + @TodaySecond


SELECT @NewFileName = @TodayMonth+'_'+@TodayDate+'_'+@TodayYear+'_'+@TodayHour+@TodayMinute+@TodaySecond
select @SQL2 = 'bcp dbname.dbo.employeetable'
select @SQL2 = @SQL2 + ' "c:\program files\FinalOutput \explore_' 
select @SQL2 = @SQL2 + @NewFileName
select @SQL2 = @SQL2 + '.txt" -c -t, -T' 
SELECT @SQL2

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

reading2009
Starting Member

22 Posts

Posted - 2010-10-14 : 10:34:45

I tried to out right after the database name. still the same error. i took the sql statement and ran it from cmd promt and it got the output just fine. but from the stored proc, i am not able to execute @sql2.

any guidence will be much appriciated.


select @SQL2 = 'bcp dbname.dbo.employeetable out'
select @SQL2 = @SQL2 + ' "c:\program files\FinalOutput \explore_' 
select @SQL2 = @SQL2 + @NewFileName
select @SQL2 = @SQL2 + '.txt" -c -t, -T' 
SELECT @SQL2

quote:

Originally posted by X002548

I don't have a problem

Your bcp is malformed though

You need in, out, or queryout



DECLARE    @devicename	varchar(50)
		,  @daterange	varchar(50)
		,  @SQL			varchar(7000)
		,  @SQL1		varchar(7000)
		,  @vary1		varchar(7000)
		,  @final1		varchar(7000)
		,  @SQL2		varchar(7000)
		,  @vary2		varchar(7000)
		,  @final2		varchar(7000)
		,  @vary3		varchar(7000)
		,  @SQL3		varchar(7000)
		,  @TodayDate	nvarchar(10)
		,  @TodayMonth	nvarchar(10)
		,  @TodayYear	nvarchar(10)
		,  @TodayHour	nvarchar(10)
		,  @TodayMinute nvarchar(10)
		,  @TodaySecond nvarchar (10)
		,  @NewFileName nvarchar(1000)

SELECT @TodayDate = Right('0' + DATEPART (dd,GETDATE()),2) 
SELECT @TodayDate = Right('00', 2 - Len(@TodayDate)) + @TodayDate 
SELECT @TodayMonth = DATEPART(mm,GETDATE()) 
SELECT @TodayMonth = Right('00', 2 - Len(@TodayMonth)) + @TodayMonth 
SELECT @TodayYear = DATEPART (yyyy,GETDATE()) 
SELECT @TodayHour = DATEPART(hh,GETDATE())
SELECT @TodayHour = Right('00', 2 - LEN (@TodayHour)) + @TodayHour 
SELECT @TodayMinute = DATEPART(mi,GETDATE()) 
SELECT @TodayMinute = Right('00', 2 - LEN (@TodayMinute)) + @TodayMinute 
SELECT @TodaySecond = DATEPART(ss,GETDATE())
SELECT @TodaySecond = Right('00', 2 - LEN (@TodaySecond)) + @TodaySecond


SELECT @NewFileName = @TodayMonth+'_'+@TodayDate+'_'+@TodayYear+'_'+@TodayHour+@TodayMinute+@TodaySecond
select @SQL2 = 'bcp dbname.dbo.employeetable'
select @SQL2 = @SQL2 + ' "c:\program files\FinalOutput \explore_' 
select @SQL2 = @SQL2 + @NewFileName
select @SQL2 = @SQL2 + '.txt" -c -t, -T' 
SELECT @SQL2

reading2009
Starting Member

22 Posts

Posted - 2010-10-14 : 11:14:08

ANY one any help here?

reading2009
Starting Member

22 Posts

Posted - 2010-10-14 : 12:34:00

this solved my problem -
EXEC master..xp_cmdshell @SQL1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources