Re: Insert output of sp_helpdb {dbname} in only one table
From: Kalen Delaney (replies_at_public_newsgroups.com)
Date: 02/24/05
- Next message: Sophie Guo [MSFT]: "RE: Which Groups and what are effective permissions based on multiple groups?"
- Previous message: Kalen Delaney: "Re: Renaming logical file name"
- In reply to: CC&JM: "RE: Insert output of sp_helpdb {dbname} in only one table"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 23 Feb 2005 22:32:29 -0800
To insert the output of a stored procedure into a table, the requirement is
that the procedure only return one result set. So sp_helpdb <dbname> does
not qualify.
You can modify the code of sp_helpdb to write your own procedure, and insert
into a table within that new procedure.
--
HTH
----------------
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"CC&JM" <CCJM@discussions.microsoft.com> wrote in message
news:8F0140FA-EC6F-489A-845B-F8FEC74D89A5@microsoft.com...
> Thanks Mike but the question was if i execute the sp_helpdb followed by
> the
> database name the output returns two different blocks of information and i
> cant insert these two different blocks into the same table.
> If i only want to use sp_helpdb...perfect
>
> create table hdb
> (
> name nvarchar(24),
> db_size nvarchar(13),
> owner nvarchar(24),
> dbid smallint,
> created char(11),
> status varchar(340),
> compatibility_level tinyint,
> )
> insert into hdb exec sp_helpdb
> select * from hdb
>
> But if i want to insert sp_helpdb database_name into the table i supose
> that
> i need to create the other fields with the table to insert the other block
> of
> information, but its shown to me an error:
>
> ex:
>
> create table hdb
> (
> name nvarchar(24),
> db_size nvarchar(13),
> owner nvarchar(24),
> dbid smallint,
> created char(11),
> status varchar(340),
> compatibility_level tinyint,
> name2 nchar(128), -- i put name2 because name already exists
> fileid smallint,
> [file name] nchar(260),
> filegroup nvarchar(128),
> size nvarchar(18),
> maxsize nvarchar(18),
> growth nvarchar(18),
> usage varchar(9)
> )
>
> insert into hdb exec sp_helpdb database_name
>
> select * from hdb
> ERROR:
> Server: Msg 213, Level 16, State 7, Procedure sp_helpdb, Line 175
> Insert Error: Column name or number of supplied values does not match
> table
> definition.
>
> I dont know how can i do this.
> Thanks and best regards
>
>
> "Mike Epprecht (SQL MVP)" wrote:
>
>> Hi
>>
>> Please don't post the same question within an hour in the same group.
>>
>> Create a Temporary Table, and then do an INSERT INTO, using EXECUTE.
>> Check
>> BOL for all the output fields that sp_HelpDB will return as it vaires
>> based
>> on parameters:
>>
>> CREATE TABLE #DB
>> (
>> Col1,
>> ..
>> )
>>
>> INSERT INTO #DB
>> EXECUTE ('sp_HelpDB')
>>
>> SELECT * FROM #DB
>>
>> Regards
>> Mike
>>
>> "CC&JM" wrote:
>>
>> > Hi,
>> >
>> > The output of sp_helpdb {database name} is return in two blocks.
>> > It is possible to join this outpu into only one table?
>> >
>> > Thanks,
>> > Regards
- Next message: Sophie Guo [MSFT]: "RE: Which Groups and what are effective permissions based on multiple groups?"
- Previous message: Kalen Delaney: "Re: Renaming logical file name"
- In reply to: CC&JM: "RE: Insert output of sp_helpdb {dbname} in only one table"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
