connect to another server from a stored proc

I have a stored proc that receives the connection string detials like servername, dbname, tablename,userid, pwd as parameters. how do i connect to that particular database on that server with the userid and pwd details ?

thanks
D.found my answer..used OPENDATASOURCE.

thanks for anyone that tried.

Connect to another db on the same server

Hi. How does one programmatically do a SELECT on another database on the
same server? I've got a stored proc in "EducationData" database that needs
to call a SELECT on table "tblStudents" on the "Directory" database. This is
on SQL Server 2000.
Also, I've installed the Books Online from here:
http://www.microsoft.com/downloads/...ng=en#filelist,
but it's asking to do a repair on the current installation and still not
showing up on my start menu. Am I looking in the right place?
Thanks for your help.dw wrote:
> Hi. How does one programmatically do a SELECT on another database on the
> same server? I've got a stored proc in "EducationData" database that needs
> to call a SELECT on table "tblStudents" on the "Directory" database. This
is
> on SQL Server 2000.
> Also, I've installed the Books Online from here:
> http://www.microsoft.com/downloads/...ng=en#filelist,
> but it's asking to do a repair on the current installation and still not
> showing up on my start menu. Am I looking in the right place?
> Thanks for your help.
Try to do "Select * from directory..tblstudents"|||Use master -- You don't have to do this, I'm just showing you that you're
not in the pubs or Northwind database
GO
Select * from SERVERNAME.pubs.dbo.titles
select * from SERVERNAME.Northwind.dbo.Products
"dw" <cougarmana_NOSPAM_@.uncw.edu> wrote in message
news:%23jhZrotmGHA.5076@.TK2MSFTNGP02.phx.gbl...
> Hi. How does one programmatically do a SELECT on another database on the
> same server? I've got a stored proc in "EducationData" database that needs
> to call a SELECT on table "tblStudents" on the "Directory" database. This
is
> on SQL Server 2000.
> Also, I've installed the Books Online from here:
>
http://www.microsoft.com/downloads/...ng=en#filelist,
> but it's asking to do a repair on the current installation and still not
> showing up on my start menu. Am I looking in the right place?
> Thanks for your help.
>|||Thanks, sloan and Maninder :)
"Maninder" <msdhanjal@.gmail.com> wrote in message
news:1151517486.289024.101700@.j72g2000cwa.googlegroups.com...
> dw wrote:
> Try to do "Select * from directory..tblstudents"
>

Confused by sp_rename.

I am confused by the rules for the sp_rename stored proc and it's
interaction with other statements . It seems that is provide owner.name
for the second parameter (instead of just name), the SELECT statement
afterwards simply will tell me that the table does not exist. Am I
missing something really simple?
For instance, the following works fine:
sp_rename N'[dbo].[tmp_sfx_tbl_003_01]', N'tbl_003_01'
select * from tbl_003_01
However, the statements below do not work:
sp_rename 'tmp_sfx_tbl_003_01', '[dbo].[tbl_003_01]'
select * from tbl_003_01 --returns Invalid object name 'dbo.tbl_003_01'.
ThanksSp_rename does not allow for owner/schema qualified naming for new name. Thi
s would allow you to
change owner/schema with sp_rename, so it will interpret that part as the ne
w name. What you did was
to rename the table to:
[dbo].[a1]
Above is the name without any delimiters or owner/schema. So, to use it, hav
ing delimiters and
owner/schema, you use:
[dbo].[[dbo]].[a1]]]
To see what happend, try below:
Use tempdb
CREATE TABLE dbo.a(c1 int)
GO
EXEC sp_rename a, '[dbo].[a1]'
GO
SELECT name FROM sysobjects WHERE name LIKE '%a1%'
SELECT * FROM [dbo].[[dbo]].[a1]]]
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Frank Rizzo" <none@.none.net> wrote in message news:%232VAclflHHA.4188@.TK2MSFTNGP02.phx.gbl.
.
>I am confused by the rules for the sp_rename stored proc and it's interacti
on with other statements
>. It seems that is provide owner.name for the second parameter (instead of
just name), the SELECT
>statement afterwards simply will tell me that the table does not exist. Am
I missing something
>really simple?
> For instance, the following works fine:
> sp_rename N'[dbo].[tmp_sfx_tbl_003_01]', N'tbl_003_01'
> select * from tbl_003_01
> However, the statements below do not work:
> sp_rename 'tmp_sfx_tbl_003_01', '[dbo].[tbl_003_01]'
> select * from tbl_003_01 --returns Invalid object name 'dbo.tbl_003_01'.
>
> Thanks|||Tibor Karaszi wrote:
> Sp_rename does not allow for owner/schema qualified naming for new name.
> This would allow you to change owner/schema with sp_rename, so it will
> interpret that part as the new name. What you did was to rename the
> table to:
> [dbo].[a1]
> Above is the name without any delimiters or owner/schema. So, to use it,
> having delimiters and owner/schema, you use:
> [dbo].[[dbo]].[a1]]]
>
> To see what happend, try below:
>
> Use tempdb
> CREATE TABLE dbo.a(c1 int)
> GO
> EXEC sp_rename a, '[dbo].[a1]'
> GO
> SELECT name FROM sysobjects WHERE name LIKE '%a1%'
> SELECT * FROM [dbo].[[dbo]].[a1]]]
So how would I assign ownership as a part of the rename? Say I want to
rename dbo.temp_a1 to rizzo.a1? Or rizzo.temp_a1 to rizzo.a1?
Thanks|||Look up "alter schema ... transfer" in BOL.
"Frank Rizzo" <none@.none.com> wrote in message
news:ejlnomklHHA.4772@.TK2MSFTNGP05.phx.gbl...
> Tibor Karaszi wrote:
>
> So how would I assign ownership as a part of the rename? Say I want to
> rename dbo.temp_a1 to rizzo.a1? Or rizzo.temp_a1 to rizzo.a1?
> Thanks|||> So how would I assign ownership as a part of the rename? Say I want to rename dbo.temp_a1
to
> rizzo.a1? Or rizzo.temp_a1 to rizzo.a1?
You don't. You do it in two separate operations.
Chancing object owner in 2000 is done using sp_changeobjectowner. In 2005, t
he command depends on
whether you want to change schema or owner.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Frank Rizzo" <none@.none.com> wrote in message news:ejlnomklHHA.4772@.TK2MSFTNGP05.phx.gbl...

> Tibor Karaszi wrote:
>
> So how would I assign ownership as a part of the rename? Say I want to re
name dbo.temp_a1 to
> rizzo.a1? Or rizzo.temp_a1 to rizzo.a1?
> Thanks

Confused by sp_rename.

I am confused by the rules for the sp_rename stored proc and it's
interaction with other statements . It seems that is provide owner.name
for the second parameter (instead of just name), the SELECT statement
afterwards simply will tell me that the table does not exist. Am I
missing something really simple?
For instance, the following works fine:
sp_rename N'[dbo].[tmp_sfx_tbl_003_01]', N'tbl_003_01'
select * from tbl_003_01
However, the statements below do not work:
sp_rename 'tmp_sfx_tbl_003_01', '[dbo].[tbl_003_01]'
select * from tbl_003_01 --returns Invalid object name 'dbo.tbl_003_01'.
Thanks
Sp_rename does not allow for owner/schema qualified naming for new name. This would allow you to
change owner/schema with sp_rename, so it will interpret that part as the new name. What you did was
to rename the table to:
[dbo].[a1]
Above is the name without any delimiters or owner/schema. So, to use it, having delimiters and
owner/schema, you use:
[dbo].[[dbo]].[a1]]]
To see what happend, try below:
Use tempdb
CREATE TABLE dbo.a(c1 int)
GO
EXEC sp_rename a, '[dbo].[a1]'
GO
SELECT name FROM sysobjects WHERE name LIKE '%a1%'
SELECT * FROM [dbo].[[dbo]].[a1]]]
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Frank Rizzo" <none@.none.net> wrote in message news:%232VAclflHHA.4188@.TK2MSFTNGP02.phx.gbl...
>I am confused by the rules for the sp_rename stored proc and it's interaction with other statements
>. It seems that is provide owner.name for the second parameter (instead of just name), the SELECT
>statement afterwards simply will tell me that the table does not exist. Am I missing something
>really simple?
> For instance, the following works fine:
> sp_rename N'[dbo].[tmp_sfx_tbl_003_01]', N'tbl_003_01'
> select * from tbl_003_01
> However, the statements below do not work:
> sp_rename 'tmp_sfx_tbl_003_01', '[dbo].[tbl_003_01]'
> select * from tbl_003_01 --returns Invalid object name 'dbo.tbl_003_01'.
>
> Thanks
|||Tibor Karaszi wrote:
> Sp_rename does not allow for owner/schema qualified naming for new name.
> This would allow you to change owner/schema with sp_rename, so it will
> interpret that part as the new name. What you did was to rename the
> table to:
> [dbo].[a1]
> Above is the name without any delimiters or owner/schema. So, to use it,
> having delimiters and owner/schema, you use:
> [dbo].[[dbo]].[a1]]]
>
> To see what happend, try below:
>
> Use tempdb
> CREATE TABLE dbo.a(c1 int)
> GO
> EXEC sp_rename a, '[dbo].[a1]'
> GO
> SELECT name FROM sysobjects WHERE name LIKE '%a1%'
> SELECT * FROM [dbo].[[dbo]].[a1]]]
So how would I assign ownership as a part of the rename? Say I want to
rename dbo.temp_a1 to rizzo.a1? Or rizzo.temp_a1 to rizzo.a1?
Thanks
|||Look up "alter schema ... transfer" in BOL.
"Frank Rizzo" <none@.none.com> wrote in message
news:ejlnomklHHA.4772@.TK2MSFTNGP05.phx.gbl...
> Tibor Karaszi wrote:
>
> So how would I assign ownership as a part of the rename? Say I want to
> rename dbo.temp_a1 to rizzo.a1? Or rizzo.temp_a1 to rizzo.a1?
> Thanks
|||> So how would I assign ownership as a part of the rename? Say I want to rename dbo.temp_a1 to
> rizzo.a1? Or rizzo.temp_a1 to rizzo.a1?
You don't. You do it in two separate operations.
Chancing object owner in 2000 is done using sp_changeobjectowner. In 2005, the command depends on
whether you want to change schema or owner.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Frank Rizzo" <none@.none.com> wrote in message news:ejlnomklHHA.4772@.TK2MSFTNGP05.phx.gbl...
> Tibor Karaszi wrote:
>
> So how would I assign ownership as a part of the rename? Say I want to rename dbo.temp_a1 to
> rizzo.a1? Or rizzo.temp_a1 to rizzo.a1?
> Thanks

Confused by sp_rename.

I am confused by the rules for the sp_rename stored proc and it's
interaction with other statements . It seems that is provide owner.name
for the second parameter (instead of just name), the SELECT statement
afterwards simply will tell me that the table does not exist. Am I
missing something really simple?
For instance, the following works fine:
sp_rename N'[dbo].[tmp_sfx_tbl_003_01]', N'tbl_003_01'
select * from tbl_003_01
However, the statements below do not work:
sp_rename 'tmp_sfx_tbl_003_01', '[dbo].[tbl_003_01]'
select * from tbl_003_01 --returns Invalid object name 'dbo.tbl_003_01'.
ThanksSp_rename does not allow for owner/schema qualified naming for new name. This would allow you to
change owner/schema with sp_rename, so it will interpret that part as the new name. What you did was
to rename the table to:
[dbo].[a1]
Above is the name without any delimiters or owner/schema. So, to use it, having delimiters and
owner/schema, you use:
[dbo].[[dbo]].[a1]]]
To see what happend, try below:
Use tempdb
CREATE TABLE dbo.a(c1 int)
GO
EXEC sp_rename a, '[dbo].[a1]'
GO
SELECT name FROM sysobjects WHERE name LIKE '%a1%'
SELECT * FROM [dbo].[[dbo]].[a1]]]
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Frank Rizzo" <none@.none.net> wrote in message news:%232VAclflHHA.4188@.TK2MSFTNGP02.phx.gbl...
>I am confused by the rules for the sp_rename stored proc and it's interaction with other statements
>. It seems that is provide owner.name for the second parameter (instead of just name), the SELECT
>statement afterwards simply will tell me that the table does not exist. Am I missing something
>really simple?
> For instance, the following works fine:
> sp_rename N'[dbo].[tmp_sfx_tbl_003_01]', N'tbl_003_01'
> select * from tbl_003_01
> However, the statements below do not work:
> sp_rename 'tmp_sfx_tbl_003_01', '[dbo].[tbl_003_01]'
> select * from tbl_003_01 --returns Invalid object name 'dbo.tbl_003_01'.
>
> Thanks|||Tibor Karaszi wrote:
> Sp_rename does not allow for owner/schema qualified naming for new name.
> This would allow you to change owner/schema with sp_rename, so it will
> interpret that part as the new name. What you did was to rename the
> table to:
> [dbo].[a1]
> Above is the name without any delimiters or owner/schema. So, to use it,
> having delimiters and owner/schema, you use:
> [dbo].[[dbo]].[a1]]]
>
> To see what happend, try below:
>
> Use tempdb
> CREATE TABLE dbo.a(c1 int)
> GO
> EXEC sp_rename a, '[dbo].[a1]'
> GO
> SELECT name FROM sysobjects WHERE name LIKE '%a1%'
> SELECT * FROM [dbo].[[dbo]].[a1]]]
So how would I assign ownership as a part of the rename? Say I want to
rename dbo.temp_a1 to rizzo.a1? Or rizzo.temp_a1 to rizzo.a1?
Thanks|||Look up "alter schema ... transfer" in BOL.
"Frank Rizzo" <none@.none.com> wrote in message
news:ejlnomklHHA.4772@.TK2MSFTNGP05.phx.gbl...
> Tibor Karaszi wrote:
>> Sp_rename does not allow for owner/schema qualified naming for new name.
>> This would allow you to change owner/schema with sp_rename, so it will
>> interpret that part as the new name. What you did was to rename the table
>> to:
>> [dbo].[a1]
>> Above is the name without any delimiters or owner/schema. So, to use it,
>> having delimiters and owner/schema, you use:
>> [dbo].[[dbo]].[a1]]]
>>
>> To see what happend, try below:
>>
>> Use tempdb
>> CREATE TABLE dbo.a(c1 int)
>> GO
>> EXEC sp_rename a, '[dbo].[a1]'
>> GO
>> SELECT name FROM sysobjects WHERE name LIKE '%a1%'
>> SELECT * FROM [dbo].[[dbo]].[a1]]]
>
> So how would I assign ownership as a part of the rename? Say I want to
> rename dbo.temp_a1 to rizzo.a1? Or rizzo.temp_a1 to rizzo.a1?
> Thanks|||> So how would I assign ownership as a part of the rename? Say I want to rename dbo.temp_a1 to
> rizzo.a1? Or rizzo.temp_a1 to rizzo.a1?
You don't. You do it in two separate operations.
Chancing object owner in 2000 is done using sp_changeobjectowner. In 2005, the command depends on
whether you want to change schema or owner.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Frank Rizzo" <none@.none.com> wrote in message news:ejlnomklHHA.4772@.TK2MSFTNGP05.phx.gbl...
> Tibor Karaszi wrote:
>> Sp_rename does not allow for owner/schema qualified naming for new name. This would allow you to
>> change owner/schema with sp_rename, so it will interpret that part as the new name. What you did
>> was to rename the table to:
>> [dbo].[a1]
>> Above is the name without any delimiters or owner/schema. So, to use it, having delimiters and
>> owner/schema, you use:
>> [dbo].[[dbo]].[a1]]]
>>
>> To see what happend, try below:
>>
>> Use tempdb
>> CREATE TABLE dbo.a(c1 int)
>> GO
>> EXEC sp_rename a, '[dbo].[a1]'
>> GO
>> SELECT name FROM sysobjects WHERE name LIKE '%a1%'
>> SELECT * FROM [dbo].[[dbo]].[a1]]]
>
> So how would I assign ownership as a part of the rename? Say I want to rename dbo.temp_a1 to
> rizzo.a1? Or rizzo.temp_a1 to rizzo.a1?
> Thanks