On Wed, 27 Sep 2006 02:26:54 +0800, mark wrote:

>I wrote a store procedure like this:
>
>declare @dbname nvarchar(50)
>declare db_cursor cursor for
>select name as database_name from master.dbo.sysdatabases
> where has_dbaccess(name) = 1 and (name like '[0-9]%')
>
>open db_cursor
>
>FETCH NEXT FROM db_cursor INTO @dbname
>
>WHILE @@FETCH_STATUS = 0
>BEGIN
> use @dbname
> ...
>
> FETCH NEXT FROM db_cursor INTO @dbname
>END
>...
>
>
>I cannot compile the commands. How to modify?

> On Wed, 27 Sep 2006 02:26:54 +0800, mark wrote:
>
> >I wrote a store procedure like this:
> >
> >declare @dbname nvarchar(50)
> >declare db_cursor cursor for
> >select name as database_name from master.dbo.sysdatabases
> > where has_dbaccess(name) = 1 and (name like '[0-9]%')
> >
> >open db_cursor
> >
> >FETCH NEXT FROM db_cursor INTO @dbname
> >
> >WHILE @@FETCH_STATUS = 0
> >BEGIN
> > use @dbname
> > ...
> >
> > FETCH NEXT FROM db_cursor INTO @dbname
> >END
> >...
> >
> >
> >I cannot compile the commands. How to modify?
>
> Hi Mark,
>
> You can't use a variable after USE.
>
> One alternative woould be to use dynamic SQL. Since the effect of USE is
> limited to the scope, you'd have to put the complete functionality in
> the dynamic SQL. Beware of the many pitfalls of dynamic SQL, as
> described by Erland: http://www.sommarskog.se/dynamic_sql.html
>
> Another alternative is to use the undocumented system stored procedure
> sp_MSforeachdb. Use google to find usage examples.
>
> --
> Hugo Kornelis, SQL Server MVP

"mark" <markiiii@sina.com> wrote in message
news:eafH4Ek4GHA.1196@TK2MSFTNGP02.phx.gbl...
> Thank you very much. It's very helpful.
>
>
> "Hugo Kornelis" <hugo@perFact.REMOVETHIS.info.INVALID> ????
> news:ak1jh2h4drkjdt9squ3gadnhg1u4jjosc7@4ax.com...
>> On Wed, 27 Sep 2006 02:26:54 +0800, mark wrote:
>>
>> >I wrote a store procedure like this:
>> >
>> >declare @dbname nvarchar(50)
>> >declare db_cursor cursor for
>> >select name as database_name from master.dbo.sysdatabases
>> > where has_dbaccess(name) = 1 and (name like '[0-9]%')
>> >
>> >open db_cursor
>> >
>> >FETCH NEXT FROM db_cursor INTO @dbname
>> >
>> >WHILE @@FETCH_STATUS = 0
>> >BEGIN
>> > use @dbname
>> > ...
>> >
>> > FETCH NEXT FROM db_cursor INTO @dbname
>> >END
>> >...
>> >
>> >
>> >I cannot compile the commands. How to modify?
>>
>> Hi Mark,
>>
>> You can't use a variable after USE.
>>
>> One alternative woould be to use dynamic SQL. Since the effect of USE is
>> limited to the scope, you'd have to put the complete functionality in
>> the dynamic SQL. Beware of the many pitfalls of dynamic SQL, as
>> described by Erland: http://www.sommarskog.se/dynamic_sql.html
>>
>> Another alternative is to use the undocumented system stored procedure
>> sp_MSforeachdb. Use google to find usage examples.
>>
>> --
>> Hugo Kornelis, SQL Server MVP
>
>