Connection to a database -- sql server connect

Albano Alves
5/9/2005 12:00:00 AM

Hi,

I am trying to find out how to connect to a database, inside a stored
procedure...including identification (Username and passsword)?

I tried the CONNECT TO statement, but when I try to use it, I get the error:

[ODBC SQL Server Driver]Syntax Error or Access violation

I am using the connect to statemente this way:

Connect to {pc12.DB} User us.abc

DB= Databasename;
pc12= Servername;
us= Username;
abc= password

Can somebody tell me what is wrong with the way I use the connect statement
or tell me a better way to connect to databases?

Thanks

Jacco Schalkwijk
5/10/2005 12:00:00 AM

CONNECT TO is IIRC only supported in Embedded SQL for C, not in stored
procedures and T-SQL.

If you are trying to connect to a database on the same server, you can just
access it by prefixing the tables etc with the database name and the owner
name in the form of:
SELECT some_column FROM other_database.dbo.some_table

If the database is on a different server, you can set up a linked server
(see BOL), and than add the server name to the select:
SELECT some_column FROM other_server.other_database.dbo.some_table

--
Jacco Schalkwijk
SQL Server MVP

[quoted text, click to view]

sql server connect : Connection to a database