RE: stored proc to access multiple databases

From: Alejandro Mesa (AlejandroMesa_at_discussions.microsoft.com)
Date: 01/12/05 

Date: Wed, 12 Jan 2005 10:51:02 -0800

> The problem I have is I cannot figure out how to insert a return character
> to make that work.

Not really.

Example:

use northwind
go

declare @sql varchar(8000)

set @sql = 'use [pubs] select * from dbo.authors'

exec(@sql)

AMB

"Andy" wrote:

> I will try and explain my situation the best I can. Here it goes.
>
> Due to the size of our data we have it partitioned into separate databases
> for each year. Every so often we update the view structure and want to keep
> the structure the same across all databases. I have a procedure created to
> use dynamic SQL and alter the views. The problem is when I try to go across
> databases. I would like to create one procedure that would alter all of the
> views on all of the databases. So say I run the procedure from the 2001
> database. I would like it to alter the views on the 2001 database as well as
> 2002, 2003 and 2004. I thought it be as easy as using a use (database name)
> statement, but you cannot use use statements in a procedure. So I tried
> using dynamic SQL. Then your statement needs to be
> use database name
> go
>
> The problem I have is I cannot figure out how to insert a return character
> to make that work. If anybody can offer suggestions I would really
> appreciate it.
> Thanks!!!!

Relevant Pages

  • Re: Combo box auto populate problem with CAPS
    ... They all behave this way. ... In every one of my databases. ... character in upper case, inserted into the middle of the string of the found ... Do *not* post my real address in the newsgroup -- I don't want to be buried in spam and viruses. ...
    (microsoft.public.access.formscoding)
  • Fixing corrupted table
    ... Have a group of individual Access 2000 databases (one for each client and, ... There is some type of unprintable character (small box, ... a CR or an LF) where each offset occurs. ...
    (microsoft.public.access.modulesdaovba)
  • Re: Using _ as a character and not as a wildcard
    ... The databases have been prefixed with 'pl_'. ... > I can see in BOL that '_' is seen as a wildcard that can substitute any ... > single character, but how do I tell my query to use '_' as a part of the ... character after the defined escape character as a literal, ...
    (microsoft.public.sqlserver.server)
  • stored proc to access multiple databases
    ... Due to the size of our data we have it partitioned into separate databases ... use dynamic SQL and alter the views. ... I would like to create one procedure that would alter all of the ...
    (microsoft.public.sqlserver.programming)
  • Re: T-SQL question
    ... I'm guessing you want to run through a number of databases and perform some ... Typically when I want to do that I use dynamic SQL, ... dbname out of the cursor, then place the USE statement and the other ... declare @cmd varchar ...
    (microsoft.public.sqlserver.server)