Incorrect syntax near 'dlf6'

Question

Post reply

Incorrect syntax near 'dlf6'

EasyBoy

SSCertifiable

Points: 5301
More actions
November 1, 2010 at 3:46 am

#230606

While i am running the following scipt getting the error
Executed as user: sqlservices Incorrect syntax near 'd1f6'. [SQLSTATE 42000] (Error 102). The step failed.
It works fine on one server but not on production.
Here is the script
use Master
set nocount on
declare @DBName as varchar(100)
declare @SQL as varchar(200)
declare @key int
declare @value int
declare @cnt int
declare @tot int
-- Get Total count for progress tracking.
Select @tot = (select count(name) from Master..sysdatabases)
declare sc insensitive cursor for
--/// Query
select name from Master..sysdatabases
Order By Name
--/// Process
open sc
Set @cnt = 0
fetch next from sc into @DBName
while @@fetch_status = 0
begin
Set @cnt = @cnt + 1
select ltrim(str(@cnt)) + ' of ' + ltrim(str(@tot)), @DBName
SELECT @SQL = @DBName + '..sp_HelpFile'
EXEC(@SQL)
fetch next from sc into @DBName
end
close sc
deallocate sc
Can anyone please help me out here?
Thanks a lot

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

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

ColdCoffee SSC-Dedicated Points: 39971 More actions · Answer 1

I guess, some of your DBs are having spaces in their names...

Try changing part of your query to

SELECT @SQL = '['+@DBName+']' + '..sp_HelpFile'

changbluesky SSC Eights! Points: 807 More actions · Answer 2

can you extract the statement of the @SQL? it's helpful to trace the error.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

With the exception of the filegroup, you can get all of this info from a single query of the sys.master_files view.

No cursors necessary.

Sysdatabases should not be used any longer. It's deprecated, included only for backward compatibility with SQL 2000 and will be removed in a future version.

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

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

eg:

select

db_name(database_id) as DatabaseName, file_id, physical_name, cast(size*8 as varchar(20)) + ' KB' as size,

case max_size when -1 then 'unlimited' else CAST(cast(max_size as bigint)*8 as varchar(20)) + ' KB' end as maxsize,

case is_percent_growth

when 1 then cast(growth as varchar(3)) + ' %'

else cast(growth*8 as varchar(20)) + ' KB'

end as growth,

case [type] when 0 then 'data only' else 'log only' end as usage

from sys.master_files

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

EasyBoy SSCertifiable Points: 5301 More actions · Answer 5

Thanks Cold Coffee

It works for me.

Gail i am going try your view as well.

Greately apprecite your help