September 22, 2016 at 10:34 am
Good Afternoon.
I'm trying to write a "quick and dirty" package in VS2010 to provide me space usage for a database.
This is the query I'm now running manually in SSMS:
use WAREHOUSE
go
exec sp_spaceused
go
dbcc sqlperf(logspace)
go
select getdate()
go
In my Data Flow Task / OLE DB Source / SQL Command text, running just exec sp_spaceused throws an error.
Replacing the query with a simple "select * from SampleTable" the task works fine.
? Any ideas why sp_spaceused will not work ?
Thank-you
September 22, 2016 at 10:40 am
jwiesman (9/22/2016)
Good Afternoon.I'm trying to write a "quick and dirty" package in VS2010 to provide me space usage for a database.
This is the query I'm now running manually in SSMS:
use WAREHOUSE
go
exec sp_spaceused
go
dbcc sqlperf(logspace)
go
select getdate()
go
In my Data Flow Task / OLE DB Source / SQL Command text, running just exec sp_spaceused throws an error.
Replacing the query with a simple "select * from SampleTable" the task works fine.
? Any ideas why sp_spaceused will not work ?
Thank-you
Because it returns multiple result sets.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
September 22, 2016 at 11:09 am
Depending on what version of SQL you are using you can use the 'oneresultset = 1' parameter.
That being said I do believe that is a fairly recent change. You can just break down the procedure your self, strip it down and create a new procedure to only return what you need.
September 22, 2016 at 11:49 am
Thank-you all.
When I just run
"exec sp_spaceused"
This is the error I get:
TITLE: Package Validation Error
------------------------------
Package Validation Error
------------------------------
ADDITIONAL INFORMATION:
Error at Data Flow Task [OLE DB Source [69]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "The metadata could not be determined because the statement 'select
database_name = db_name(),
database_size = ltrim(str((convert (dec (15,2),@dbsize) + c' in procedure 'sp_spaceused' is not compatible with the statement 'SELECT
name = OBJECT_NAME (@id),
rows = convert (char(20), @rowCount),
reserved = LTRIM (S' in procedure 'sp_spaceused'.".
Error at Data Flow Task [OLE DB Source [69]]: Unable to retrieve column information from the data source. Make sure your target table in the database is available.
Error at Data Flow Task [SSIS.Pipeline]: "OLE DB Source" failed validation and returned validation status "VS_ISBROKEN".
Error at Data Flow Task [SSIS.Pipeline]: One or more component failed validation.
Error at Data Flow Task: There were errors during task validation.
(Microsoft.DataTransformationServices.VsIntegration)
------------------------------
BUTTONS:
OK
------------------------------
This one I just do not get.
John
September 22, 2016 at 1:26 pm
Phil,
Your last comment confuses me.
I gave you my environment (Data Flow Task / OLE DB Source / SQL Command text)
I gave you the code I ran ( exec sp_paceused)
I gave you the results (TITLE: Package Validation Error
------------------------------
Package Validation Error
------------------------------
ADDITIONAL INFORMATION:
Error at Data Flow Task [OLE DB Source [69]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "The metadata could not be determined because the statement 'select
database_name = db_name(),
database_size = ltrim(str((convert (dec (15,2),@dbsize) + c' in procedure 'sp_spaceused' is not compatible with the statement 'SELECT
name = OBJECT_NAME (@id),
rows = convert (char(20), @rowCount),
reserved = LTRIM (S' in procedure 'sp_spaceused'.".
Error at Data Flow Task [OLE DB Source [69]]: Unable to retrieve column information from the data source. Make sure your target table in the database is available.
)
? What additional information do you need ?
John
September 22, 2016 at 1:36 pm
jwiesman (9/22/2016)
Phil,Your last comment confuses me.
I gave you my environment (Data Flow Task / OLE DB Source / SQL Command text)
I gave you the code I ran ( exec sp_paceused)
I gave you the results (TITLE: Package Validation Error
------------------------------
Package Validation Error
------------------------------
ADDITIONAL INFORMATION:
Error at Data Flow Task [OLE DB Source [69]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "The metadata could not be determined because the statement 'select
database_name = db_name(),
database_size = ltrim(str((convert (dec (15,2),@dbsize) + c' in procedure 'sp_spaceused' is not compatible with the statement 'SELECT
name = OBJECT_NAME (@id),
rows = convert (char(20), @rowCount),
reserved = LTRIM (S' in procedure 'sp_spaceused'.".
Error at Data Flow Task [OLE DB Source [69]]: Unable to retrieve column information from the data source. Make sure your target table in the database is available.
)
? What additional information do you need ?
John
You are trying to use the results of a stored procedure which generates more than one resultset as a data source.
You also seem to have ignored the suggestion that you use this instead:
EXEC sys.sp_spaceused @oneresultset = 1
Which does return a single resultset.
I believe that multiple result sets are the reason for the problems you are experiencing, but you are persisting in trying to use them, so I gave you a link that describes how to do it.
--Edit: fix quoted section
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
September 22, 2016 at 3:16 pm
Phil,
Hi.
First off, thank-you for helping.
Your 2nd to the last reply got truncated..... There was no link... it was just:
You might find this helpful.
------------------------------------------------------------------------
Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.
When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
I tried your suggestion:
EXEC sys.sp_spaceused @oneresultset = 1
Unfortunately, I still get the same error message.
I'm using Visual Studio 2010 against a 2012 SQL Server instance
I reviewed your posts. I do not see a link (or any link for that matter) with regards to @oneresultset
John
September 22, 2016 at 3:26 pm
jwiesman (9/22/2016)
Phil,Your 2nd to the last reply got truncated..... There was no link... it was just:
You might find this helpful.
Click on the word THIS in that line.
Good article called "How to Use a Multi-Result Set Stored Procedure in SSIS"
September 22, 2016 at 5:02 pm
jwiesman (9/22/2016)
Phil,Hi.
First off, thank-you for helping.
Your 2nd to the last reply got truncated..... There was no link... it was just:
You might find this helpful.
------------------------------------------------------------------------
Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.
When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
I tried your suggestion:
EXEC sys.sp_spaceused @oneresultset = 1
Unfortunately, I still get the same error message.
I'm using Visual Studio 2010 against a 2012 SQL Server instance
I reviewed your posts. I do not see a link (or any link for that matter) with regards to @oneresultset
John
OK, try this instead. Worked for me on 2016 anyway.
EXEC sys.sp_spaceused @oneresultset = 1
with result sets
(
(
database_name sysname,
database_size varchar(50),
unallocated_space varchar(50),
reserved varchar(50),
data varchar(50),
index_size varchar(50),
unused varchar(50)
)
)
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply