Hi, Gurus
I am creating scripts that will allow to check each dbs table spaces once a week. I can not use linked server because we have more than 200 SQL Servers and more than 600 DBs. However I populate table with server name and db name and login info. Therefore I will looping through this table to get server and db info and pull each db's table space and insert into table for future growth prediction.
I've got below message.
Msg 7357, Level 16, State 1, Line 1
Cannot process the object " USE DBAdmin EXEC SP_SPACEUSED 'DatabaseSizeHistory'". The OLE DB provider "SQLNCLI" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.
Codes are:
DECLARE @DB AS VARCHAR(MAX),@String AS VARCHAR(MAX),
@ServerName AS VARCHAR(MAX),@Password AS VARCHAR(MAX),@TableName AS VARCHAR(MAX),
@ID int
SET@ID=1
SET@ServerName='servername'
SET@Password='password'
SET@DB='DBAdmin'
SET@TableName='DatabaseSizeHistory'
SET@String='INSERT INTO dbo.TableSizeHistory(DBandTableID,CheckDateTime,TableRow,DataSpaceUsed,IndexSpaceUsed) Select'+''''+Convert(varchar(max),@ID)+''''+' AS DBandTableID, GetDate() AS CheckDateTime, a.* From OPENROWSET(''SQLOLEDB'',''' + @ServerName + '''; ''SA''; ''' + @Password + ''', '' USE '+@DB+' EXEC SP_SPACEUSED '''''+@TableName+''''''') as a'
EXEC (@String)
Some of parameters are used for others.
Basically when I try to run below query from correct server
USE DBAdmin EXEC SP_SPACEUSED 'DatabaseSizeHistory'
That script runs fine.
Any idea?
Thx in advance
Jay