connect more than one database in a query

How can I access tables from different databases in the same server in a
query or stored procedure.
Also how to access databases existing in different database servers.
Thanks fro your help.
Retna
Hi,
To access the tables in the databases in same server you can use the below
syntax.
select * from dbname.tableowner.table_name
To access the table in in differenet server u need to create linked server.
AFter that you could use
select * from linkedservername.dbname.tableowner.table_name
See the below article on how to create linked server and execute DML
comamnds.
http://www.microsoft.com/India/msdn/articles/166.aspx
Thanks
Hari
MCDBA
"Retna" <Retna@.discussions.microsoft.com> wrote in message
news:04CD4FE1-0A0F-466C-9EBC-0AEBE55440A8@.microsoft.com...
> How can I access tables from different databases in the same server in a
> query or stored procedure.
> Also how to access databases existing in different database servers.
> Thanks fro your help.
> Retna
|||Thanks Hari for your valuable and timely reply .
Retna
"Hari Prasad" wrote:

> Hi,
> To access the tables in the databases in same server you can use the below
> syntax.
> select * from dbname.tableowner.table_name
> To access the table in in differenet server u need to create linked server.
> AFter that you could use
> select * from linkedservername.dbname.tableowner.table_name
> See the below article on how to create linked server and execute DML
> comamnds.
> http://www.microsoft.com/India/msdn/articles/166.aspx
> Thanks
> Hari
> MCDBA
>
> "Retna" <Retna@.discussions.microsoft.com> wrote in message
> news:04CD4FE1-0A0F-466C-9EBC-0AEBE55440A8@.microsoft.com...
>
>

connect more than one database in a query

How can I access tables from different databases in the same server in a
query or stored procedure.
Also how to access databases existing in different database servers.
Thanks fro your help.
RetnaHi,
To access the tables in the databases in same server you can use the below
syntax.
select * from dbname.tableowner.table_name
To access the table in in differenet server u need to create linked server.
AFter that you could use
select * from linkedservername.dbname.tableowner.table_name
See the below article on how to create linked server and execute DML
comamnds.
http://www.microsoft.com/India/msdn/articles/166.aspx
Thanks
Hari
MCDBA
"Retna" <Retna@.discussions.microsoft.com> wrote in message
news:04CD4FE1-0A0F-466C-9EBC-0AEBE55440A8@.microsoft.com...
> How can I access tables from different databases in the same server in a
> query or stored procedure.
> Also how to access databases existing in different database servers.
> Thanks fro your help.
> Retna|||Thanks Hari for your valuable and timely reply .
Retna
"Hari Prasad" wrote:
> Hi,
> To access the tables in the databases in same server you can use the below
> syntax.
> select * from dbname.tableowner.table_name
> To access the table in in differenet server u need to create linked server.
> AFter that you could use
> select * from linkedservername.dbname.tableowner.table_name
> See the below article on how to create linked server and execute DML
> comamnds.
> http://www.microsoft.com/India/msdn/articles/166.aspx
> Thanks
> Hari
> MCDBA
>
> "Retna" <Retna@.discussions.microsoft.com> wrote in message
> news:04CD4FE1-0A0F-466C-9EBC-0AEBE55440A8@.microsoft.com...
> > How can I access tables from different databases in the same server in a
> > query or stored procedure.
> > Also how to access databases existing in different database servers.
> >
> > Thanks fro your help.
> >
> > Retna
>
>

Connect from VB6.0 via ODBCDirect to SQL Server

Hi All,
I have an existing application with frontend in VB 6.0 and backend in MS Access. I am trying to convert the application to SQL Server 2000 (standard) backend. At the moment the frontend uses ODBCDirect to communicate with MS Access backend. I upsized the MS Access database to SQL Server 2000 using MS Access upsizing wizard. (I know you need to use DTS, but I tried that and it did not copy the data across citing the error "The Server could not load DCOM", hence I used the upsizing wizard).
The backend now set up in SQL Server, I presume all I need to do is change the connection string in VB6.0, which I did, but I have not been able to make a successful connection yet. The following is the connection string I am using:

"ODBC;DSN=;Server=sql-server;Database=cabinetSQL;UID=dbo;PWD=;"

The SQL Server has been configured for windows authentication and the new database that I created due to upsizing, in the User section, it lists "dbo" as user name and login name is "domain/myloginname". So in the user ID I am using "dbo". I got some hints about how to connecte via ODBCDirect to SQL Server using the required workspace, as follows:

Set dbw = CreateWorkspace("pomsws", "admin", "", dbUseODBC)
Workspaces.Append dbw

Set pomsc = dbw.OpenConnection("pompom", dbDriverNoPrompt, _
true, con_string)

where con_string is the above mentioned connection string.

Can anybody let me know what I am doing wrong and provide any suggestions?

Thanks to all for your time and efforts.

Regards:
PrathmeshAn update on my question. I created a seperate small application just to test for the connectivity. The following is the code:

Dim strConnectionAdmin As String
Dim objConnAdmin As Connection
Dim CollegeRs As Recordset
Dim strsql As String
Dim i As Integer
Dim mdbodbcdirect As Database
Dim dbw As Workspace
Dim pomsc As Connection

strConnectionAdmin = "driver={SQL Server};Server=sql-server;Database=cabinetSQL;uid=;pwd=;"

Set dbw = CreateWorkspace("pomsws", "", "", dbUseODBC)
Workspaces.Append dbw

'On Error GoTo handler
Set pomsc = dbw.OpenConnection("pompom", dbDriverNoPrompt, _
False, strConnectionAdmin)

MsgBox "Connection success"

End Sub

This gives me an error "Invalid Argument" and points to the "Set pomsc" statement.

If I change the connection string to:
strConnectionAdmin = "ODBC;DSN=' ';driver={SQL Server};Server=sql-server;Database=cabinetSQL;uid=;pwd=;"

then it gives me an error for "ODBC-Call failed".

I am not sure where I am going wrong. Please help.

Regards:
Prathmesh