[TRANSACT]Cant switch from master to another database

Hello,

I encounter a problem with a small portion of sqlcode. I try to go on
database using "use dbname" but i always stay in master. I execute
script with the sa user.

declare @.dbnamesysname
declare @.ret_codeint

DECLARE db_cursor CURSOR FOR
select
name
from
master..sysdatabases
where
name not in ('master', 'model', 'tempdb', 'pubs', 'Northwind')

-- Open cursor
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @.dbname

WHILE @.@.FETCH_STATUS = 0
BEGIN
execute ('use ' + @.dbname)
execute ('select db_name()')

Thank's for help,
Pierrot.USE is scoped only within the EXEC statement so you'll have to combine
the two:

EXEC ('USE ' + @.dbname+' SELECT DB_NAME()')

If this is for non-production code you might consider using the
following undocumented proc instead of the cursor:

EXEC sp_msforeachdb 'USE ? SELECT DB_NAME()'

This still implements a cursor behind the scenes but keeps some of the
complexity out of your own code.

--
David Portas
SQL Server MVP
--|||execute('use ' + @.dbname + ';select db_name()')
HTH

[TRANSACT] Can't switch from database to another

Hello,
I encounter a problem with a small portion of sqlcode. I
try to go on database using "use dbname" but i always stay
in master. I execute script with the sa user.
declare @.dbnamesysname
declare @.ret_codeint
DECLARE db_cursor CURSOR FOR
select
name
from
master..sysdatabases
where
name not in ('master', 'model', 'tempdb', 'pubs',
'Northwind')
-- Open cursor
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @.dbname
WHILE @.@.FETCH_STATUS = 0
BEGIN
execute ('use ' + @.dbname)
execute ('select db_name()')
Thank's for help,
Pierrot.
AFAIK, executing just open up a session to excute something, when executing
the next statement you are in another Session, try to combine these two
together with
EXECUTE ('Use Northwind;Select DB_NAME()')
HTH, Jens Smeyer.
http://www.sqlserver2005.de
"pierrot" <anonymous@.discussions.microsoft.com> schrieb im Newsbeitrag
news:104e01c5419c$afcb5850$a401280a@.phx.gbl...
> Hello,
> I encounter a problem with a small portion of sqlcode. I
> try to go on database using "use dbname" but i always stay
> in master. I execute script with the sa user.
> declare @.dbname sysname
> declare @.ret_code int
> DECLARE db_cursor CURSOR FOR
> select
> name
> from
> master..sysdatabases
> where
> name not in ('master', 'model', 'tempdb', 'pubs',
> 'Northwind')
> -- Open cursor
> OPEN db_cursor
> FETCH NEXT FROM db_cursor INTO @.dbname
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> execute ('use ' + @.dbname)
> execute ('select db_name()')
> Thank's for help,
> Pierrot.
|||As Jens indicates each EXECUTE is its own session, so to do this in an
execute you must place all of the statements together...
Depending on what you are trying to do you could also use the 3part name
Declare @.str varchar(100)
Select @.str = 'select * from ' + @.dbname + '.dbo.thetable'
execute (@.str)
for System Sps you could also do
Declare @.str varchar(100)
Select @.str = 'Execute ' + @.dbname + '.dbo.sp_spaceused'
execute (@.str)
If you wish to do something for each database try sp_foreachdatabase ( I
think is the name of the sp.)
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"pierrot" <anonymous@.discussions.microsoft.com> wrote in message
news:104e01c5419c$afcb5850$a401280a@.phx.gbl...
> Hello,
> I encounter a problem with a small portion of sqlcode. I
> try to go on database using "use dbname" but i always stay
> in master. I execute script with the sa user.
> declare @.dbname sysname
> declare @.ret_code int
> DECLARE db_cursor CURSOR FOR
> select
> name
> from
> master..sysdatabases
> where
> name not in ('master', 'model', 'tempdb', 'pubs',
> 'Northwind')
> -- Open cursor
> OPEN db_cursor
> FETCH NEXT FROM db_cursor INTO @.dbname
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> execute ('use ' + @.dbname)
> execute ('select db_name()')
> Thank's for help,
> Pierrot.

[TRANSACT] Can't switch from database to another

Hello,
I encounter a problem with a small portion of sqlcode. I
try to go on database using "use dbname" but i always stay
in master. I execute script with the sa user.
declare @.dbname sysname
declare @.ret_code int
DECLARE db_cursor CURSOR FOR
select
name
from
master..sysdatabases
where
name not in ('master', 'model', 'tempdb', 'pubs',
'Northwind')
-- Open cursor
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @.dbname
WHILE @.@.FETCH_STATUS = 0
BEGIN
execute ('use ' + @.dbname)
execute ('select db_name()')
Thank's for help,
Pierrot.AFAIK, executing just open up a session to excute something, when executing
the next statement you are in another Session, try to combine these two
together with
EXECUTE ('Use Northwind;Select DB_NAME()')
HTH, Jens Süßmeyer.
--
http://www.sqlserver2005.de
--
"pierrot" <anonymous@.discussions.microsoft.com> schrieb im Newsbeitrag
news:104e01c5419c$afcb5850$a401280a@.phx.gbl...
> Hello,
> I encounter a problem with a small portion of sqlcode. I
> try to go on database using "use dbname" but i always stay
> in master. I execute script with the sa user.
> declare @.dbname sysname
> declare @.ret_code int
> DECLARE db_cursor CURSOR FOR
> select
> name
> from
> master..sysdatabases
> where
> name not in ('master', 'model', 'tempdb', 'pubs',
> 'Northwind')
> -- Open cursor
> OPEN db_cursor
> FETCH NEXT FROM db_cursor INTO @.dbname
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> execute ('use ' + @.dbname)
> execute ('select db_name()')
> Thank's for help,
> Pierrot.|||As Jens indicates each EXECUTE is its own session, so to do this in an
execute you must place all of the statements together...
Depending on what you are trying to do you could also use the 3part name
Declare @.str varchar(100)
Select @.str = 'select * from ' + @.dbname + '.dbo.thetable'
execute (@.str)
for System Sps you could also do
Declare @.str varchar(100)
Select @.str = 'Execute ' + @.dbname + '.dbo.sp_spaceused'
execute (@.str)
If you wish to do something for each database try sp_foreachdatabase ( I
think is the name of the sp.)
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"pierrot" <anonymous@.discussions.microsoft.com> wrote in message
news:104e01c5419c$afcb5850$a401280a@.phx.gbl...
> Hello,
> I encounter a problem with a small portion of sqlcode. I
> try to go on database using "use dbname" but i always stay
> in master. I execute script with the sa user.
> declare @.dbname sysname
> declare @.ret_code int
> DECLARE db_cursor CURSOR FOR
> select
> name
> from
> master..sysdatabases
> where
> name not in ('master', 'model', 'tempdb', 'pubs',
> 'Northwind')
> -- Open cursor
> OPEN db_cursor
> FETCH NEXT FROM db_cursor INTO @.dbname
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> execute ('use ' + @.dbname)
> execute ('select db_name()')
> Thank's for help,
> Pierrot.

[TRANSACT] Can't switch from database to another

Hello,
I encounter a problem with a small portion of sqlcode. I
try to go on database using "use dbname" but i always stay
in master. I execute script with the sa user.
declare @.dbname sysname
declare @.ret_code int
DECLARE db_cursor CURSOR FOR
select
name
from
master..sysdatabases
where
name not in ('master', 'model', 'tempdb', 'pubs',
'Northwind')
-- Open cursor
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @.dbname
WHILE @.@.FETCH_STATUS = 0
BEGIN
execute ('use ' + @.dbname)
execute ('select db_name()')
Thank's for help,
Pierrot.AFAIK, executing just open up a session to excute something, when executing
the next statement you are in another Session, try to combine these two
together with
EXECUTE ('Use Northwind;Select DB_NAME()')
HTH, Jens Smeyer.
http://www.sqlserver2005.de
--
"pierrot" <anonymous@.discussions.microsoft.com> schrieb im Newsbeitrag
news:104e01c5419c$afcb5850$a401280a@.phx.gbl...
> Hello,
> I encounter a problem with a small portion of sqlcode. I
> try to go on database using "use dbname" but i always stay
> in master. I execute script with the sa user.
> declare @.dbname sysname
> declare @.ret_code int
> DECLARE db_cursor CURSOR FOR
> select
> name
> from
> master..sysdatabases
> where
> name not in ('master', 'model', 'tempdb', 'pubs',
> 'Northwind')
> -- Open cursor
> OPEN db_cursor
> FETCH NEXT FROM db_cursor INTO @.dbname
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> execute ('use ' + @.dbname)
> execute ('select db_name()')
> Thank's for help,
> Pierrot.|||As Jens indicates each EXECUTE is its own session, so to do this in an
execute you must place all of the statements together...
Depending on what you are trying to do you could also use the 3part name
Declare @.str varchar(100)
Select @.str = 'select * from ' + @.dbname + '.dbo.thetable'
execute (@.str)
for System Sps you could also do
Declare @.str varchar(100)
Select @.str = 'Execute ' + @.dbname + '.dbo.sp_spaceused'
execute (@.str)
If you wish to do something for each database try sp_foreachdatabase ( I
think is the name of the sp.)
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"pierrot" <anonymous@.discussions.microsoft.com> wrote in message
news:104e01c5419c$afcb5850$a401280a@.phx.gbl...
> Hello,
> I encounter a problem with a small portion of sqlcode. I
> try to go on database using "use dbname" but i always stay
> in master. I execute script with the sa user.
> declare @.dbname sysname
> declare @.ret_code int
> DECLARE db_cursor CURSOR FOR
> select
> name
> from
> master..sysdatabases
> where
> name not in ('master', 'model', 'tempdb', 'pubs',
> 'Northwind')
> -- Open cursor
> OPEN db_cursor
> FETCH NEXT FROM db_cursor INTO @.dbname
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> execute ('use ' + @.dbname)
> execute ('select db_name()')
> Thank's for help,
> Pierrot.sql

[Match any Portion] - Possible in MSSQL?

I asked this question in the mysql forums, but I am also interested in any info regarding MSSQL. I've seen databases with search systems with the functionality I seek below. I just have no idea how they are doing it. Thank you.

We have an mysql inventory database. We want to be able to put in 4984.600 and choose "match any portion" and it finds 4984600 which is in our database.

Does Mysql have a "match any portion" search function? In this case LIKE didn't work which we tried already.

Any ideas? or are we stuck with using MSSQL. We know this will work with MSSQL.

Thank you very much. This is a huge problem for us.

JimHuh?

You want to match 44984600 to 4984.600? Are these strings or numeric data?

It makes no sense to match these "on any part". That is like saying you want to match "Betty" to "Rob", since they both contain the letter "B".

Perhaps you should explain more clearly, and include the code that you have tried.|||Hi there,

Here you can see in action what I am talking about.

First, go to this website http://www.bell-electrical.com. On the left side there is an "Inventory Search" section. Enter Part no. 4984.600 ( You don't have to enter a manufacturer, or check any radio boxes. ).

The website will now redirect to another website and display your result. It will contain the part number 4984600 without the decimal. That particular website is using an ASP script with an MSSQL backend. This is why I say it works with MSSQL. Whether it is the ASP script or something else using sql, I am not exactly sure, but they are doing something to make this work.

I am basically looking to implement something like this using php and mysql, but am unsure if it's possible. I have noticed that most big php scripts including vbulletin for example don't have this function either.

The particular company that is using this search system, is not known for custom programming. Rather they usually use basic asp and mssql systems. This also leads me to believe that it's probably not all that complex.

Any ideas on how they might be achieving this would be great. I have also heard that in MS Access there is actually some parameter that you can use to switch from a "Match" search to a "Match Portion" search. I am wondering if there is something like this in MSSQL as well.

Anyway, hopefully this reply will help everyone understand what I am trying to accomplish.

Thanks again for all the replies.|||I don't think you want to "match portion". It looks like you want to strip all non-numeric (or non-alphanumeric) characters from the string prior to doing your search. And then maybe use the LIKE operator to search as a substring of existing.

TSQL has a nice selection of character string manipulation functions you can use, depending upon the details of your situation. Lookup the REPLACE function, for instance.