Select * into table with a stored procedure?

Question

Post reply

Select * into table with a stored procedure?

mick miller

SSChasing Mays

Points: 631
More actions
June 17, 2009 at 11:50 pm

#210747

Hello
How I can write the result into a table (for the first run to create the table) like that?
select * into zzdbfiles.dbfiles
from (EXEC sp_MSforeachdb @command1 = ''select name, physical_name, state_desc, size, max_size, growth from ?.sys.database_files)
second and more runs:
insert into table zzdbfiles.dbfiles
from (EXEC sp_MSforeachdb @command1 = ''select name, physical_name, state_desc, size, max_size, growth from ?.sys.database_files)
the SQL-statements are not correct!
thanks for your support
miller

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

CREATE TABLE zzdbfiles.dbfiles (

....

)

INSERT INTO zzdbfiles.dbfiles

EXEC sp_MSforeachdb @command1 = 'select name, physical_name, state_desc, size, max_size, growth from ?.sys.database_files'

You have to explicitly create the table first. Select ... Into cannot use a proc as the source.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Allister Reid SSCrazy Points: 2666 More actions · Answer 2

You might find it useful to be able to check if table exist or not?

if so you can use something like:

IF OBJECT_ID('zzdbfiles.dbfiles') IS NULL

CREATE zzdbfiles.dbfiles ...

mick miller SSChasing Mays Points: 631 More actions · Answer 3

Hello Gail

I created the table.

When I start a new query about the registered servers, then I become this errors:

INSERT INTO DatabaseReport.dbo.Database_File_Info

EXEC sp_MSforeachdb @command1 = 'select name, physical_name, state_desc, size, max_size, growth from ?.sys.database_files'

SRVDW(dom\sadb): Msg 208, Level 16, State 1, Line 1

Ungültiger Objektname 'DatabaseReport.dbo.Database_File_Info'.

srvdw1(dom\sadb): Msg 208, Level 16, State 1, Line 1

Invalid object name 'DatabaseReport.dbo.Database_File_Info'.

srvdentw(dom\sadb): Msg 208, Level 16, State 1, Line 1

Ungültiger Objektname 'DatabaseReport.dbo.Database_File_Info'.

srwebfactory(dom\sadb): Msg 208, Level 16, State 1, Line 1

Invalid object name 'DatabaseReport.dbo.Database_File_Info'.

srvscom(dom\sadb): Msg 208, Level 16, State 1, Line 1

Invalid object name 'DatabaseReport.dbo.Database_File_Info'.

All querys runs from the same server.

I don't created separate linked-servers.

Thanks for your help

miller

Lowell SSC Guru Points: 323518 More actions · Answer 4

it might be that you need identify the columns to insert into:

INSERT INTO DatabaseReport.dbo.Database_File_Info(col1,col2...)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

mick miller (6/19/2009)
SRVDW(dom\sadb): Msg 208, Level 16, State 1, Line 1
Ungültiger Objektname 'DatabaseReport.dbo.Database_File_Info'.

Double check that the table is where it's supposed to be and has the right name. That looks like the standard 'table doesn't exist' message.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

mick miller SSChasing Mays Points: 631 More actions · Answer 6

INSERT INTO [srsql1].[DatabaseReport].[dbo].[Database_File_Info]

EXEC sp_MSforeachdb @command1 =

'select name, physical_name, state_desc,

size*8/1024, max_size, growth*8/1024,

convert(datetime,(convert(int,(convert(float,getdat())))))

from ?.sys.database_files'

I will run this query on the server srsql1 which has a servergroup in registered servers.

The query is ok, because it runs directly on the server correct and made some record.

Why it doesn't run in Servergroup?

See the errormessages in the attachement.

I made linked-servers specially.

All SQL-Server agents are running with the same domain-account.

What is wrong?

Thanks for the support!!!!!

miller

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 7

As first for all the non German guys and gals. From attached error message:

Der Server 'srsql1' wurde in sys.servers nicht gefunden. Pr?fen Sie, ob der richtige Servername angegeben wurde. F?hren Sie bei Bedarf die gespeicherte Prozedur sp_addlinkedserver aus, um den Server zu sys.servers hinzuzuf?gen.
srvdw1(dom\sadb): Msg 7202, Level 11, State 2, Line 1
Could not find server 'srsql1' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.
srwebfactory(dom\sadb): Msg 7202, Level 11, State 2, Line 1

First error message is equal to second but German.

@mick

If you want to insert those information from all your servers into one server you have to register this server on your other servers as linked server.

Flo