Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Error: Msg 7357, Level 16, State 1, Line 1 Cannot process the object " USE DBAdmin EXEC SP_SPACEUSED 'DatabaseSizeHistory'". The OLE DB provider "SQLNCLI" for... Expand / Collapse
Author
Message
Posted Monday, October 27, 2008 2:40 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 10:55 AM
Points: 84, Visits: 100
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




Post #592446
Posted Tuesday, February 18, 2014 4:36 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 1:13 PM
Points: 381, Visits: 539
I am seeing the same when declaring a variable table to use with a cte in openrowset. Have you made any progress on this?

There is an exception to every rule, except this one...
Post #1542795
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse