String error 2

[pavithra123]

Hi

I am using SQL server v 7.

when I execute the following

declare @cmd varchar (255)
declare @dt varcahr (30)
set @dt =getdate()
select @ cmd = 'isql -Q &quot;select count(*) from <db>.dbo.<tablename>&quot; -od:\zip\'+@dt'.txt'
exec master.dbo.xp_cmdshell @ cmd

I get the error
error at line 4
incorrect syntax '.txt'
I know that i am missing a string some where ( but where )
I did not want to put this in the forum ( looks silly ) after wasting considerable amount of time I am desparately asking for help.

Best regards
Prem.

 

[LFCfan]

try this

select @ cmd = 'isql -Q &quot;select count(*) from <db>.dbo.<tablename>&quot; -od:\zip\'+@dt [red]+[/red]'.txt'

hope it helps
Maria

 

[MeanGreen]

You are missing your &quot;+&quot; after the @dt. Try this:

declare @cmd varchar (255)
declare @dt varchar (30)
set @dt =getdate()
select @cmd = 'isql -Q &quot;select count(*) from <db>.dbo.<tablename>&quot; -od:\zip\'+ @dt + '.txt'
exec master.dbo.xp_cmdshell @ cmd

Hope this helps.

 

[MaffewW]

Is it because you've got a space between the @ and cmd on 2 occassions or is that a typo?

 

[pavithra123]

Hi All I tried the two combinations given by maria and meangreen this is what is the output

select @cmd = 'isql -Q &quot;select count(*) from <db>.dbo.<tablename>&quot; -od:\zip\'+ @dt + '.txt'


trust me i tried the same thing as it is typed above.

the output is a follows
output -------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------

-------------------------------------------------------------
isql: unknown option 8
usage: isql [-U login id] [-P password]
[-S server] [-H hostname] [-E trusted connection]
[-d use database name] [-l login timeout] [-t query timeout]
[-h headers] [-s colseparator] [-w columnwidth]
[-a packetsize] [-e echo input] [-x max text size]
[-L list servers] [-c cmdend]
[-q &quot;cmdline query&quot;] [-Q &quot;cmdline query&quot; and exit]
[-n remove numbering] [-m errorlevel]
[-r msgs to stderr]
[-i inputfile] [-o outputfile]
[-p print statistics] [-b On error batch abort]
[-O use Old ISQL behavior disables the following]
<EOF> batch processing
Auto console width scaling
Wide messages
default errorlevel is -1 vs 1
[-? show syntax summary (this screen)]

(18 row(s) affected)

 

[JamesLean]

PRINT the value of @cmd after you have built the string and check what you are actually trying to execute.

I think you'll find the problem is coming from converting getdate() into a string value and then trying to use it as a filename.

 

[JamesLean]

When setting the value of date, try the following:

DECLARE @dt varchar(8)
SET @dt = CONVERT(varchar(8), getdate(), 112)

This will give you a filename in the form yyyymmdd with no spaces.

 

[MeanGreen]

This will work for you. The filename you are trying to create has invalid characters for the filename. Also, you need to specify the Server, and Username with Password (or Trusted connection).

declare @cmd varchar (255)
declare @dt varchar (30)
set @dt ='F' + convert(varchar(10),getdate(),112) + '_' + replace(convert(varchar(8),getdate(),114),':','')
select @cmd = 'isql -Q &quot;select count(*) from <database>.dbo.<table>&quot; -od:\zip\'+ @dt + '.txt -S<server> -U<user> -P<password>'
select @cmd
exec master.dbo.xp_cmdshell @cmd

Hope this helps.

 

[pavithra123]

Hi Jameslean,

thanks for ur first input and I have sorted it out the final thing looks like this.

declare @cmd varchar(255)
declare @dt varchar(10),@dt2 varchar(10),@dt3 varchar(10)
set @dt = cast(datepart(day ,getdate()) as varchar)
set @dt2 = cast(datepart(month ,getdate()) as varchar)
set @dt3 = cast(datepart(year ,getdate()) as varchar)
<qgsm>.dbo.GSMAuditTrail&quot; -od:\zip\'+ @dt + '.txt'
select @cmd = 'isql -Q &quot;select count(*) from qgsm.dbo.GSMAuditTrail&quot; -od:\zip\'+@dt+''+@dt2+''+@dt3+'.txt'
--print @cmd
exec master.dbo.xp_cmdshell @cmd

Meangreen also concurs about it.

The problem was due to date conversion and also due to a space before the .txt ( damn it ).

thanks again guys. keep up the good work. see u in next thread.

Best regards
Prem