Could Stored Procedure or Trigger choose another database?

Hello all, I need some advice or clue here.
I want to crate a stored procedure or trigger from database A that will
change of have effect on another database B. Is this could be done? I
try to use sql syntax "use B;" in stored procedure that created on
database A, but it said that use syntax couldn't be used in stored
procedure."Lemune" <alfredosilitonga@.gmail.com> wrote in message
news:1147850181.634882.15120@.i39g2000cwa.googlegroups.com...
> Hello all, I need some advice or clue here.
> I want to crate a stored procedure or trigger from database A that will
> change of have effect on another database B. Is this could be done? I
> try to use sql syntax "use B;" in stored procedure that created on
> database A, but it said that use syntax couldn't be used in stored
> procedure.
>
Do you mean something like
create procedure FOO as
(
select * from bar
)
where bar is in a different database?
If so, 3-part names
select * from dbname.owner.table
so
create procedure FOO as
(
select * from baz.dbo.bar
)|||Lemune,
yes you can change the datbase context inside a stored procedure even
thought the "Use" keyword is not allowed.
One way is creating a string variable and build up the syntax you need,
when execute it through sp_executesql.
Declare @.myQuery nvarchar(2000)
set @.myQuery = ' USE ' + @.dbname + CHAR(13) + 'SELECT * from myTable'
Exec sp_executesql @.myQuery
Another option is symply using the 3 part name:
Select * from myDb.dbo.myTable
If you try to loop through all datbases on a server have a look at
sp_MSforeachDB. It gives you an easy way to run the same command on all
databases.
Markus|||Thanks for your information and MarkusB too, I try booth your method
and it work well.
But I have another problem, assume that I want to create stored
procedure that work from A.dbo.Incomming to check a value from
B.dbo.Data and than the result value is send to A.dbo.Outgoing in one
stored procedure. I'm using MS Sql Server 7. I also have read that in
MS SQL 2003 have stored functions that return value.|||I have another question, how stored procedure in SQL Server 7 could
return value (like function)?|||Inside the proc, you do:
RETURN -101
And the calling code, of TSQL:
DECLARE @.retval int
EXEC @.retval = myproc
Return codes are int only and should be used to communicate status (success
or fail). You can also
return values through output parameters:
CREATE PROC myproc @.parm varchar(5) OUT
AS
SET @.parm = 'Hello'
GO
DECLARE @.x varchar(5)
EXEC myproc @.parm = @.x OUT
Both return codes and out parameters can both be retrieved from your program
ming API (ADO.NET, for
example) as well.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Lemune" <alfredosilitonga@.gmail.com> wrote in message
news:1147948815.225334.130080@.i40g2000cwc.googlegroups.com...
>I have another question, how stored procedure in SQL Server 7 could
> return value (like function)?
>|||Thank you very much.|||I have another question again :-). Could Stored Procedure or Trigger
choose another database that is on difference engine? For example, I
have database A on engine or instance name Server1 that has a stored
procedure Stored_Proc_1 and in this stored procedure I want to access
some table on database B on engine Server2. I have username and
password on Server2.

Could Stored Procedure or Trigger choose another database?

Hello all, I need some advice or clue here.
I want to crate a stored procedure or trigger from database A that will
change of have effect on another database B. Is this could be done? I
try to use sql syntax "use B;" in stored procedure that created on
database A, but it said that use syntax couldn't be used in stored
procedure."Lemune" <alfredosilitonga@.gmail.com> wrote in message
news:1147850181.634882.15120@.i39g2000cwa.googlegroups.com...
> Hello all, I need some advice or clue here.
> I want to crate a stored procedure or trigger from database A that will
> change of have effect on another database B. Is this could be done? I
> try to use sql syntax "use B;" in stored procedure that created on
> database A, but it said that use syntax couldn't be used in stored
> procedure.
>
Do you mean something like
create procedure FOO as
(
select * from bar
)
where bar is in a different database?
If so, 3-part names
select * from dbname.owner.table
so
create procedure FOO as
(
select * from baz.dbo.bar
)|||Lemune,
yes you can change the datbase context inside a stored procedure even
thought the "Use" keyword is not allowed.
One way is creating a string variable and build up the syntax you need,
when execute it through sp_executesql.
Declare @.myQuery nvarchar(2000)
set @.myQuery = ' USE ' + @.dbname + CHAR(13) + 'SELECT * from myTable'
Exec sp_executesql @.myQuery
Another option is symply using the 3 part name:
Select * from myDb.dbo.myTable
If you try to loop through all datbases on a server have a look at
sp_MSforeachDB. It gives you an easy way to run the same command on all
databases.
Markus|||Thanks for your information and MarkusB too, I try booth your method
and it work well.
But I have another problem, assume that I want to create stored
procedure that work from A.dbo.Incomming to check a value from
B.dbo.Data and than the result value is send to A.dbo.Outgoing in one
stored procedure. I'm using MS Sql Server 7. I also have read that in
MS SQL 2003 have stored functions that return value.|||I have another question, how stored procedure in SQL Server 7 could
return value (like function)?|||Inside the proc, you do:
RETURN -101
And the calling code, of TSQL:
DECLARE @.retval int
EXEC @.retval = myproc
Return codes are int only and should be used to communicate status (success or fail). You can also
return values through output parameters:
CREATE PROC myproc @.parm varchar(5) OUT
AS
SET @.parm = 'Hello'
GO
DECLARE @.x varchar(5)
EXEC myproc @.parm = @.x OUT
Both return codes and out parameters can both be retrieved from your programming API (ADO.NET, for
example) as well.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Lemune" <alfredosilitonga@.gmail.com> wrote in message
news:1147948815.225334.130080@.i40g2000cwc.googlegroups.com...
>I have another question, how stored procedure in SQL Server 7 could
> return value (like function)?
>|||Thank you very much.|||I have another question again :-). Could Stored Procedure or Trigger
choose another database that is on difference engine? For example, I
have database A on engine or instance name Server1 that has a stored
procedure Stored_Proc_1 and in this stored procedure I want to access
some table on database B on engine Server2. I have username and
password on Server2.

Could Stored Procedure or Trigger choose another database on difference engine?

Could Stored Procedure or Trigger
choose another database that is on difference engine? For example, I
have database A on engine or instance name Server1 that has a stored
procedure Stored_Proc_1 and in this stored procedure I want to access
some table on database B on engine Server2. I have username and
password on Server2.
I know that we can access different database on the same engine or
instance name, using to methods:
1. Using 3-part names ex: on the sp we say Select * from
myDb.dbo.myTable
2. Creating a string variable and build up the syntax that needed, ex:
Declare @.myQuery nvarchar(2000)
set @.myQuery = ' USE ' + @.dbname + CHAR(13) + 'SELECT * from
myTable'
Exec sp_executesql @.myQueryHi Lemune,
You can configure a linked server and then use 4 part name to access objects
on a different server.
Server_Name.DB_Name.Schema.Object
Note that there is a lot more overhead involved in this type of query
(distributed query) than accessing objects in DBs on the same server
instance.
Look in Books On Line for "Configuring linked servers"
HTH
Ami
"Lemune" <alfredosilitonga@.gmail.com> wrote in message
news:1148281973.632093.46050@.i39g2000cwa.googlegroups.com...
> Could Stored Procedure or Trigger
> choose another database that is on difference engine? For example, I
> have database A on engine or instance name Server1 that has a stored
> procedure Stored_Proc_1 and in this stored procedure I want to access
> some table on database B on engine Server2. I have username and
> password on Server2.
> I know that we can access different database on the same engine or
> instance name, using to methods:
> 1. Using 3-part names ex: on the sp we say Select * from
> myDb.dbo.myTable
> 2. Creating a string variable and build up the syntax that needed, ex:
> Declare @.myQuery nvarchar(2000)
> set @.myQuery = ' USE ' + @.dbname + CHAR(13) + 'SELECT * from
> myTable'
> Exec sp_executesql @.myQuery
>|||This problem occur because I develop a program for sms services. My
program use database to store incoming massage and out massage, If the
massage is having the right keyword it will do some task. And the task
is involved with another database. If according to myself I would
rather make my database on my client database (just adding my table to
my client database) or maybe adding my database to my client database
engine. But for some reason, my client refuses that method. So, I have
so make stored procedure on my program database engine that will
connect to my client database. I use this method so that I don't have
to change my program code each time my client use that rule :-).