I have a small SQL script that rotates through all databases on the
server and executes a Stored Procedure in each of them. Here are the
steps:
1: The first step is to get name of databases in to a cursor.
2: Build dynamic SQL to change database to database in variable - this
step fails
3: Execute SP
4: Move to next database.
Is it not possible to change current database using Dynamic SQL? Can
someone help me here please?
Here is the code I am using:
----- EXECUTE SQL Statement on all Databases
DECLARE
@DatabaseName varchar(100)
, @SQL varchar(500)
DECLARE DBNameCursor CURSOR FOR
SELECT
[Name] AS DatabaseName
FROM
master.dbo.sysd atabases
ORDER BY
DatabaseName
OPEN DBNameCursor
FETCH NEXT FROM DBNameCursor
INTO @DatabaseName
WHILE @@FETCH_STATUS = 0
BEGIN
----- Change to Database
SELECT
@SQL = 'USE ' + @DatabaseName
EXEC(@SQL)
----- SQL Statement to be run
EXEC dbo.SPName
FETCH NEXT FROM DBNameCursor
INTO @DatabaseName
END
CLOSE DBNameCursor
DEALLOCATE DBNameCursor
----------------------
Thanks in advance!
Vishal Sinha 5 7216
On Feb 7, 7:02 am, "SQLJunkie" <vsinh...@gmail .comwrote:
I have a small SQL script that rotates through all databases on the
server and executes a Stored Procedure in each of them. Here are the
steps:
1: The first step is to get name of databases in to a cursor.
2: Build dynamic SQL to change database to database in variable - this
step fails
3: Execute SP
4: Move to next database.
Is it not possible to change current database using Dynamic SQL? Can
someone help me here please?
Here is the code I am using:
----- EXECUTE SQL Statement on all Databases
DECLARE
@DatabaseName varchar(100)
, @SQL varchar(500)
DECLARE DBNameCursor CURSOR FOR
SELECT
[Name] AS DatabaseName
FROM
master.dbo.sysd atabases
ORDER BY
DatabaseName
OPEN DBNameCursor
FETCH NEXT FROM DBNameCursor
INTO @DatabaseName
WHILE @@FETCH_STATUS = 0
BEGIN
----- Change to Database
SELECT
@SQL = 'USE ' + @DatabaseName
EXEC(@SQL)
----- SQL Statement to be run
EXEC dbo.SPName
FETCH NEXT FROM DBNameCursor
INTO @DatabaseName
END
CLOSE DBNameCursor
DEALLOCATE DBNameCursor
----------------------
Thanks in advance!
Vishal Sinha
If I recall correctly EXEC starts a new thread that won't know about
the preceeding USE statement.
My favorite dynamic SQL site: http://www.sommarskog.se/dyn-search.html
SQLJunkie (vs******@gmail .com) writes:
I have a small SQL script that rotates through all databases on the
server and executes a Stored Procedure in each of them. Here are the
steps:
1: The first step is to get name of databases in to a cursor.
2: Build dynamic SQL to change database to database in variable - this
step fails
3: Execute SP
4: Move to next database.
Is it not possible to change current database using Dynamic SQL? Can
someone help me here please?
Yes, but the effect of the USE lasts only for the duration of the of the
dynamic SQL.
SELECT
@SQL = 'USE ' + @DatabaseName
EXEC(@SQL)
----- SQL Statement to be run
EXEC dbo.SPName
If all you want to do is to run a stored procedure in each database,
this is the easiest way to do:
SELECT @SPname = @DatabaseName + '.dbo.SPName'
EXEC @SPname
You may also be interested in sp_MSforeachdb:
EXEC sp_MSforeachdb 'EXEC ?.dbo.SPNAme'
This procedure is undocumented and unsupported, but it's nevertheless
fairly popular.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se
Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx
Thanks everyone for the replies. The following example is fine for
generic SPs:
SELECT @SPname = @DatabaseName + '.dbo.SPName'
EXEC @SPname
But what if I have to run SPs like SP_updatestats etc? then it will
not work.
Thanks!
On Feb 7, 5:35 pm, Erland Sommarskog <esq...@sommars kog.sewrote:
SQLJunkie (vsinh...@gmail .com) writes:
I have a small SQL script that rotates through all databases on the
server and executes a Stored Procedure in each of them. Here are the
steps:
1: The first step is to get name of databases in to a cursor.
2: Build dynamic SQL to change database to database in variable - this
step fails
3: Execute SP
4: Move to next database.
Is it not possible to change current database using Dynamic SQL? Can
someone help me here please?
Yes, but the effect of the USE lasts only for the duration of the of the
dynamic SQL.
SELECT
@SQL = 'USE ' + @DatabaseName
EXEC(@SQL)
----- SQL Statement to be run
EXEC dbo.SPName
If all you want to do is to run a stored procedure in each database,
this is the easiest way to do:
SELECT @SPname = @DatabaseName + '.dbo.SPName'
EXEC @SPname
You may also be interested in sp_MSforeachdb:
EXEC sp_MSforeachdb 'EXEC ?.dbo.SPNAme'
This procedure is undocumented and unsupported, but it's nevertheless
fairly popular.
--
Erland Sommarskog, SQL Server MVP, esq...@sommarsk og.se
Books Online for SQL Server 2005 athttp://www.microsoft.c om/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.c om/sql/prodinfo/previousversion s/books.mspx
SQLJunkie (vs******@gmail .com) writes:
Thanks everyone for the replies. The following example is fine for
generic SPs:
SELECT @SPname = @DatabaseName + '.dbo.SPName'
EXEC @SPname
But what if I have to run SPs like SP_updatestats etc? then it will
not work.
Au contraire, it will work just fine! If you say:
EXEC mydb..sp_system procedure
the system procedure will execute in the context of mydb.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se
Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx
Thanks - it worked!
Momentary lapse of reason :)
On Feb 8, 5:39 pm, Erland Sommarskog <esq...@sommars kog.sewrote:
SQLJunkie (vsinh...@gmail .com) writes:
Thanks everyone for the replies. The following example is fine for
generic SPs:
SELECT @SPname = @DatabaseName + '.dbo.SPName'
EXEC @SPname
But what if I have to run SPs like SP_updatestats etc? then it will
not work.
Au contraire, it will work just fine! If you say:
EXEC mydb..sp_system procedure
the system procedure will execute in the context of mydb.
--
Erland Sommarskog, SQL Server MVP, esq...@sommarsk og.se
Books Online for SQL Server 2005 athttp://www.microsoft.c om/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.c om/sql/prodinfo/previousversion s/books.mspx
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Chris Hodapp |
last post by:
I have seen messages posted about this before, and there is a clear
reference to it in the manual, but I have been unable to find a
solution.
I'm on Slackware 9.1, kernel 2.6.0-test11, using Python 2.3.1 and
GCC 3.2.3 (both installed by default with Slackware) and SWIG 1.3.19,
compiled from source code.
I messed around a little on my own and couldn't get things to work
right,
so I copied the examples from the book, nearly verbatim but...
|
by: Roel Wuyts |
last post by:
CALL FOR CONTRIBUTIONS
International Workshop on Revival of Dynamic Languages
http://pico.vub.ac.be/~wdmeuter/RDL04/index.html
(at OOPSLA2004, Vancouver, British Columbia, Canada, October 24-28, 200)
Organization committee:
Roel Wuyts (primary contact - roel.wuyts@ulb.ac.be), Gilad Bracha,
Wolfgang De Meuter, Stéphane Ducasse and Oscar Nierstrasz.
|
by: MikeY |
last post by:
Hi Everyone,
I am working in C#, windows forms.My question is this. All my button dynamic
controls properties are present and accounted for except for the"FlatStyle"
properties. I can't seem to figure out, if there is a way of using
polymorphic way (if that is a word) of doing this particular property. A
sample of my code is as follows:
DynamicControls.ButtonControl(this,btnSearchByName, new Point(5, 75), new
Size(95, 20),...
|
by: Ender |
last post by:
I have an application that I would like third party developers to be
able to create Plug-ins that will be dynamically loaded into our
application to extend functionality.
I have utilized the "Let Users Add Functionality to Your .NET
Applications with Macros and Plug-Ins" article at MSDN for the dynamic
loading of DLLs
http://msdn.microsoft.com/msdnmag/issues/03/10/Plug-Ins/default.aspx
|
by: Tyler Carver |
last post by:
I am trying to use some dynamic controls that are built and then added
to tables. The problem that I am having is the timing of when I can
populate the controls and have the state remain after a postback. The
main question would be this:
Why does this work for maintaining state after a postback for dynamic
controls:
myText = new Label();
myText.ID = "myText";
| |
by: Leo J. Hart IV |
last post by:
OK, here's another question for the experts:
I am building a multi-step (3 steps actually) form using a panel for
each step and hiding/displaying the appropriate panel/panels depending
on which step you're on. This all works fine, but I ran into some
trouble when I started creating controls dynamically in my code-behind
file.
Each panel contains a table which is filled with various radio buttons,
text fields and the such which are...
|
by: Diffident |
last post by:
Hello All,
I am trying to add dynamic controls onto my page and here is how I am doing
that.
I have a page which has a button called as "AddMoreControls" and in this
button's event handler I am creating controls dynamically and adding them to
a panel on the page. For example, if the button is clicked once, the page is
posted back and the controls are added properly. However, if I click the
"AddMoreControls" for the second time the...
|
by: Tom |
last post by:
I am developing my pages on my development machine
and then copying to the production server.
I am not pre-compiling, I am using the 'dynamic compile'
feature. This is working fine except that everytime
I change a page on the production server I need to restart
IIS to effect the change.
If I don't restart IIS, it tries to recompile but gets and
error ( 'cannot execute a program......vbc.exe......).
Am I missing something which allows...
|
by: Ghada Al-Mashaqbeh via DotNetMonster.com |
last post by:
Hi all,
I am facing a problem in dynamic code generation at run time, the problem
occurs when the dynmaic code use global data exist within the original
application.
Lets say that my application is called "Dynamic Code", so the name space in
my project is "Dynamic Code", I have created "Form1.cs" where my code exist.
Form1 class contain a global class "xarray" with static memeber "x" which is
an array, I want the added dynamic code by...
|
by: pbd22 |
last post by:
Hi.
This is just a disaster management question.
I am using XMLHTTP for the dynamic loading of content in a very
crucial area of my web site. Same as an IFrame, but using XMLHTTP and
a DIV. I got the core of the javascript from here:
http://www.dynamicdrive.com/dynamicindex17/ajaxcontent.htm
I noticed in the demo that sometimes the content takes a long
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| |
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |