Query 2 tables - each in a different database

Is there any query code that will allow joining tables that are in different databases running on the same server? If yes, how is the connection to each handled?

That is no problem.

SELECT a.Field, b.Field
FROM dbo.myTable a
JOIN OtherDatabase.dbo.myTable b ON a.myKey = b.myKey

|||

The select code is clear but the connection to the two databases is not when using ADO. Is one connection object used somehow or are the databases linked?

|||

WeslyB is right.

But your user has to have select rights on the other database.

You don't have to handle the rights in ADO.

Regards

Query 2 table in different databases

Is there any query code that will allow joining tables that are in different databases running on the same server? If yes, how is the connection to each handled?

You need to use a 3-part name to reference the table from one database to another. The 3-part name consists of <database>.<schema>.<object>. The connection is only to the server and you can access any database to which you have permissions. You can either switch database context using USE <database> statement or use the 3-part name to reference objects.

QA green button grayed out

I'm running QA on my local PC and going through an ODBC
connection, I've been able to connect to SQL servers on
our network and execute TSQL statements.
Suddenly, last week, the green button is grayed out. No
matter what server. Everything else looks ok. All
features in EM seem to work. In QA, I can connect to the
correct DB and that seems ok. I can type in a command.
But, no green execute button!
I'm running SQL 2000 on my PC. I'm attaching to SQL 2000
and SQL 7.0 servers. The local OS is XP.
Any ideas?
Thanks,
DonHi
And does Ctrl+E work?
John
"Don" <ddachner@.hotmail.com> wrote in message
news:00ad01c3cfd7$463e4470$a501280a@.phx.gbl...
> I'm running QA on my local PC and going through an ODBC
> connection, I've been able to connect to SQL servers on
> our network and execute TSQL statements.
> Suddenly, last week, the green button is grayed out. No
> matter what server. Everything else looks ok. All
> features in EM seem to work. In QA, I can connect to the
> correct DB and that seems ok. I can type in a command.
> But, no green execute button!
> I'm running SQL 2000 on my PC. I'm attaching to SQL 2000
> and SQL 7.0 servers. The local OS is XP.
> Any ideas?
> Thanks,
> Don
>

Q3: Trusted SQL Logins

I can not connect to my Local SQL Server 2005 in C# code,

it throws the error,

The user is not associated with a trusted SQL Server connection.

i tried to change the mode to Mixed Mode, but i don't see it, I see only Windows and SQL Server Auth mode,

i tried to change to SQL Server Mode, but none of user log will work, it says, this login is not in trusted connection,

so i would make any login associated with trusted account.
Thanks,

I have covered how to choose both Windows and SQL authentication in SQL Server in the thread below but if you are getting this error in a web application then the problem is not SQL Server but IIS. In IIS Deny anonymous users and in your Web.Config enable impersonation. Hope this helps.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1894423&SiteID=1

|||

"Mixed mode" = "SQL Server and Windows authentication mode"

Maybe you are trying to login using a SQL Login when your authentication is Windows only? Perhaps you need to change your connections string:

Windows Auth

integrated secruity=true

SQL Auth

integrated security=false; uid=user;pwd=pword

HTH!

|||

Thanks for your reply, but i followed the steps, when I change it to SQLServer Authentication mode, it ask for log info, I put SA account, but it gives this error,

SA is not associated with Trusted Account.

Also I don't see the Security page as well as Mixed Mode option when i go to Server properties.
(On the Security page, under Server authentication, select the new server authentication mode, and then click OK)

Any idea,

Thanks,|||

Where are you trying to change the authentication? Open SSMS, right click your servername in Object Explorer, click properties. Down the left hand side you should have a list of pages including the security one where you need to make these changes.

Is this not what you see? Are you using Management Studio? What version of SQL Server is it?

|||You are not in the correct place because you have only two options Window or Both SQL Server and Windows is the only other option so right click on the name of your server in object explorer and go to properties then security. But you still need to enable the System Admin account because during installation if you choose Windows authentication SQL Server disables the SA account. Hope this helps.|||Thanks,

i found it and changed it but still i get an error when I try to connect to Database Engine using that sa account that i enabled it,

1) A connection was succesfully established with the server, but then an error occured during the login process. (provider: shared memory Provider, error: 0 - No process is on the other end of the pipe)(MSSql Server , Error:233)

2) from asp.net code, i get error like , sa is not in trusted connection list.

any idea,

Thanks,|||

I think you'll need to get yourself back to the SQL Configuration Manager and perhaps enable named pipes? I did a quick search on google for: "No process is on the other end of the pipe" and there were a fair few resources. Have a look there and let us know how you get on.

Good luck!

|||

No you need to enable the SA account before you try to use it and error 233 is covered in the link below and you need to reboot the box after you make all these changes. I have also seen error 233 related to certificates not installed in SQL Server. Hope this helps.

http://blogs.msdn.com/sql_protocols/archive/2006/07/26/678596.aspx

Q2: Trusted SQL Logins

I can not connect to my Local SQL Server 2005 in C# code,

it throws the error,

The user is not associated with a trusted SQL Server connection.

i tried to change the mode to Mixed Mode, but i don't see it, I see only Windows and SQL Server Auth mode,

i tried to change to SQL Server Mode, but none of user log will work, it says, this login is not in trusted connection,

so i would make any login associated with trusted account.
Thanks,

I have covered how to choose both Windows and SQL authentication in SQL Server in the thread below but if you are getting this error in a web application then the problem is not SQL Server but IIS. In IIS Deny anonymous users and in your Web.Config enable impersonation. Hope this helps.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1894423&SiteID=1

|||

"Mixed mode" = "SQL Server and Windows authentication mode"

Maybe you are trying to login using a SQL Login when your authentication is Windows only? Perhaps you need to change your connections string:

Windows Auth

integrated secruity=true

SQL Auth

integrated security=false; uid=user;pwd=pword

HTH!

|||

Thanks for your reply, but i followed the steps, when I change it to SQLServer Authentication mode, it ask for log info, I put SA account, but it gives this error,

SA is not associated with Trusted Account.

Also I don't see the Security page as well as Mixed Mode option when i go to Server properties.
(On the Security page, under Server authentication, select the new server authentication mode, and then click OK)

Any idea,

Thanks,|||

Where are you trying to change the authentication? Open SSMS, right click your servername in Object Explorer, click properties. Down the left hand side you should have a list of pages including the security one where you need to make these changes.

Is this not what you see? Are you using Management Studio? What version of SQL Server is it?

|||You are not in the correct place because you have only two options Window or Both SQL Server and Windows is the only other option so right click on the name of your server in object explorer and go to properties then security. But you still need to enable the System Admin account because during installation if you choose Windows authentication SQL Server disables the SA account. Hope this helps.|||Thanks,

i found it and changed it but still i get an error when I try to connect to Database Engine using that sa account that i enabled it,

1) A connection was succesfully established with the server, but then an error occured during the login process. (provider: shared memory Provider, error: 0 - No process is on the other end of the pipe)(MSSql Server , Error:233)

2) from asp.net code, i get error like , sa is not in trusted connection list.

any idea,

Thanks,|||

I think you'll need to get yourself back to the SQL Configuration Manager and perhaps enable named pipes? I did a quick search on google for: "No process is on the other end of the pipe" and there were a fair few resources. Have a look there and let us know how you get on.

Good luck!

|||

No you need to enable the SA account before you try to use it and error 233 is covered in the link below and you need to reboot the box after you make all these changes. I have also seen error 233 related to certificates not installed in SQL Server. Hope this helps.

http://blogs.msdn.com/sql_protocols/archive/2006/07/26/678596.aspx

Q186726 Error 80004005 Occurs When Retrieving Data from SQL Server

Does anyone know if this article is valid for connection between servers.
I'm getting an intermittent
|308|80004005|[Microsoft][ODBC_SQL_Server_Driver]Timeout_expired when a
website running on a windows 2000sp3 tries to write data to sql 2000 server
running on windows 2003 server. Could this be the cause? Is there anything
else that could be causing this problem?
Hi
Run sp_who2 and look for blocking processes when this occurs. Intermittent
issues like this point to row blocking issues in the database
One process might update a row, hold the locks, whilst a second process
tries to do the same. The 2nd process waits until process 1 is finished and
usually after 90 seconds, the MDAC stack gives up and returns the error.
Regards
Mike
"shalafi" wrote:

> Does anyone know if this article is valid for connection between servers.
> I'm getting an intermittent
> |308|80004005|[Microsoft][ODBC_SQL_Server_Driver]Timeout_expired when a
> website running on a windows 2000sp3 tries to write data to sql 2000 server
> running on windows 2003 server. Could this be the cause? Is there anything
> else that could be causing this problem?

Q186726 Error 80004005 Occurs When Retrieving Data from SQL Server

Does anyone know if this article is valid for connection between servers.
I'm getting an intermittent
|308|80004005|[Microsoft]& #91;ODBC_SQL_Server_Driver]Timeout_expir
ed whe
n a
website running on a windows 2000sp3 tries to write data to sql 2000 server
running on windows 2003 server. Could this be the cause? Is there anything
else that could be causing this problem?Hi
Run sp_who2 and look for blocking processes when this occurs. Intermittent
issues like this point to row blocking issues in the database
One process might update a row, hold the locks, whilst a second process
tries to do the same. The 2nd process waits until process 1 is finished and
usually after 90 seconds, the MDAC stack gives up and returns the error.
Regards
Mike
"shalafi" wrote:

> Does anyone know if this article is valid for connection between servers.
> I'm getting an intermittent
> |308|80004005|[Microsoft]& #91;ODBC_SQL_Server_Driver]Timeout_expir
ed w
hen a
> website running on a windows 2000sp3 tries to write data to sql 2000 serve
r
> running on windows 2003 server. Could this be the cause? Is there anythin
g
> else that could be causing this problem?

Q: username authenticated

Hello,
In SQL, suser_sname() gives me connection user name (I am using a single
user to create all connections), I authenticate users through a login form
aginst a SQL login table. Is there any way I can get a username authenticated
in an SQL query?
Thanks,Jim,
SELECT USER_NAME() will return the database user name. Is this what you are
asking for?
HTH
Jerry
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:04775C04-27FA-42CD-A766-BFF215EED008@.microsoft.com...
> Hello,
> In SQL, suser_sname() gives me connection user name (I am using a single
> user to create all connections), I authenticate users through a login form
> aginst a SQL login table. Is there any way I can get a username
> authenticated
> in an SQL query?
> Thanks,
>|||Hi Jerry,
Thanks for reply. That returns dbo.
I need the username that I used to login in my ASP.Net application, not dbo
or not connection user. It is the used authenticated through a form
authentication. Is there any way I can get the user name in my query for a
View.
"Jerry Spivey" wrote:
> Jim,
> SELECT USER_NAME() will return the database user name. Is this what you are
> asking for?
> HTH
> Jerry
> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:04775C04-27FA-42CD-A766-BFF215EED008@.microsoft.com...
> > Hello,
> >
> > In SQL, suser_sname() gives me connection user name (I am using a single
> > user to create all connections), I authenticate users through a login form
> > aginst a SQL login table. Is there any way I can get a username
> > authenticated
> > in an SQL query?
> >
> > Thanks,
> >
>
>|||Hmmm...unless the value is written to a table somewhere as part of your
application, I'm not aware of a way to determine that via T-SQL.
HTH
Jerry
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:8E72154A-20ED-4E6B-B03D-4B6819CD4FEB@.microsoft.com...
> Hi Jerry,
> Thanks for reply. That returns dbo.
> I need the username that I used to login in my ASP.Net application, not
> dbo
> or not connection user. It is the used authenticated through a form
> authentication. Is there any way I can get the user name in my query for a
> View.
> "Jerry Spivey" wrote:
>> Jim,
>> SELECT USER_NAME() will return the database user name. Is this what you
>> are
>> asking for?
>> HTH
>> Jerry
>> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
>> news:04775C04-27FA-42CD-A766-BFF215EED008@.microsoft.com...
>> > Hello,
>> >
>> > In SQL, suser_sname() gives me connection user name (I am using a
>> > single
>> > user to create all connections), I authenticate users through a login
>> > form
>> > aginst a SQL login table. Is there any way I can get a username
>> > authenticated
>> > in an SQL query?
>> >
>> > Thanks,
>> >
>>