working on linked server with openquery + sp_msforeachdb

• paksyeet

  SSCommitted

  Points: 1596

  More actions

  January 28, 2009 at 2:02 am

  #71851

  Hi,

  I have this scripts which works well on local server, now, my problem is, i want to run it on a link server..

  i was trying to use openquery but i cant make it work..

  i did also try it as a stored proc by it only returns 1database instead of getting all database.

  EXEC sp_MSForEachDB 'Use ? SELECT

  @@servername as ServerName,

  ''?'' AS DatabaseName,

  CAST(sysfiles.size * 8 / 1024.0 AS DECIMAL(18,2)) AS FileSize,

  sysfiles.name AS LogicalFileName, sysfiles.filename AS PhysicalFileName,

  CAST(sysfiles.size * 8 / 1024.0 - CAST(FILEPROPERTY(sysfiles.name,

  ''SpaceUsed'' ) AS int)/128.0 AS DECIMAL(15,2)) AS FreeSpaceMB,

  CAST(100 * (CAST (((sysfiles.size/128.0 -CAST(FILEPROPERTY(sysfiles.name,

  ''SpaceUsed'' ) AS int)/128.0)/(sysfiles.size/128.0))

  AS decimal(4,2))) AS varchar(8)) + '' %'' AS FreeSpacePct,

  GETDATE() as PollDate FROM dbo.sysfiles'

  i want it to look something like this: (but is not working)

  select * from openquery(LINKEDSERVER,'set fmtonly off;EXEC sp_MSForEachDB ''Use ? SELECT

  @@servername as ServerName,

  ''?'' AS DatabaseName,

  CAST(sysfiles.size * 8 / 1024.0 AS DECIMAL(18,2)) AS FileSize,

  sysfiles.name AS LogicalFileName, sysfiles.filename AS PhysicalFileName,

  CAST(sysfiles.size * 8 / 1024.0 - CAST(FILEPROPERTY(sysfiles.name,

  ''SpaceUsed'' ) AS int)/128.0 AS DECIMAL(15,2)) AS FreeSpaceMB,

  CAST(100 * (CAST (((sysfiles.size/128.0 -CAST(FILEPROPERTY(sysfiles.name,

  ''SpaceUsed'' ) AS int)/128.0)/(sysfiles.size/128.0))

  AS decimal(4,2))) AS varchar(8)) + '' %'' AS FreeSpacePct,

  GETDATE() as PollDate

  FROM dbo.sysfiles''')

  or maybe like this(but returns only one database)

  select * from openquery(LINKEDSERVER,'set fmtonly off;sp_thecodeabove')

  your help is higly apreciated..

  thanks..

• paksyeet

  SSCommitted

  Points: 1596

  More actions

  January 28, 2009 at 4:03 am

  #933689

  I think it is more on " ' " apostrope or single qoute.. i just cant find the combination and its driving me crazy..

  here is one of my best try but still i have error..

  -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

  script:

  select * from openquery(LINKEDSERVER,'set fmtonly off; EXEC sp_MSForEachDB ''Use ? SELECT @@servername as ServerName,

  ''?'' AS DatabaseName,

  CAST(sysfiles.size * 8 / 1024.0 AS DECIMAL(18,2)) AS FileSize,

  sysfiles.name AS LogicalFileName, sysfiles.filename AS PhysicalFileName,

  CAST(sysfiles.size * 8 / 1024.0 - CAST(FILEPROPERTY(sysfiles.name,

  ''SpaceUsed'') AS int)/128.0 AS DECIMAL(15,2)) AS FreeSpaceMB,

  CAST(100 * (CAST (((sysfiles.size/128.0 -CAST(FILEPROPERTY(sysfiles.name,

  ''SpaceUsed'' ) AS int)/128.0)/(sysfiles.size/128.0))

  AS decimal(4,2))) AS varchar(8)) + '' %'' AS FreeSpacePct,

  GETDATE() as PollDate

  FROM dbo.sysfiles''')

  -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

  error : (i dont even know why there is '@P1')

  Server: Msg 8180, Level 16, State 1, Line 1

  Statement(s) could not be prepared.

  Server: Msg 170, Level 15, State 1, Line 1

  Line 2: Incorrect syntax near '@P1'.

  thanks for your help..