Solved

table variable within sp_msforeachdb

Posted on 2010-09-07
3
767 Views
Last Modified: 2012-05-10
the second line loses sight of the declaration in the first line. what can be done to make sure it recognizes it.

thanks
--------------------
declare @DBO table (GName varchar(80),GID int,User_in_DB varchar(80),uid int)
exec sp_msforeachdb 'use [?] insert into @DBOwners exec sp_helpuser db_backupoperator'  
0
Comment
Question by:anushahanna
3 Comments
 
LVL 26

Accepted Solution

by:
Shaun Kline earned 250 total points
ID: 33620129
Try using a temporary table instead of a table variable.
From what I recall, table variables can only be used in the current context, while the EXEC command runs in a separate session than the current.

Also, the table you have in your SQL should match your temp table's name.
0
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 250 total points
ID: 33620143
you cannot do that.because the dynamic sql in the msforeachdb is in a separate scope, it cannot "see" the declaration in that "outer" scope.you have to work with either a global temp table (##temptable) or a fixed regular table ...
0
 
LVL 6

Author Comment

by:anushahanna
ID: 33620495
Thanks for the clarification.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now