SQL Alert: databases

Showing posts with label databases. Show all posts

Sunday, March 25, 2012

Accessing Different Databases on Same Server with Dot Notation.

This is a multi-part message in MIME format.
--=_NextPart_000_000B_01C58C65.624CE800
Content-Type: multipart/alternative;
boundary="--=_NextPart_001_000C_01C58C65.624CE800"
--=_NextPart_001_000C_01C58C65.624CE800
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
BlankHello,
I'm using Query Analyzer and I'm trying to select from a table on = another database...
i.e..I'm on DB2 in query analyzer and this is what my query looks like
Select *
FROM DB1..Test1
But I'm getting an error
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'DB1.'.
This query worked on my local machine... Could it be a set-up issue on = a different server?
Thanks for any help,
Matt
--=_NextPart_001_000C_01C58C65.624CE800
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

Blank
BODY {
MARGIN-TOP: 25px; FONT-SIZE: 10pt; MARGIN-LEFT: 25px; COLOR: #000000; = FONT-FAMILY: Arial, Helvetica
}
P.msoNormal {
MARGIN-TOP: 0px; FONT-SIZE: 10pt; MARGIN-LEFT: 0px; COLOR: #ffffcc; = FONT-FAMILY: Helvetica, "Times New Roman"
}
LI.msoNormal {
MARGIN-TOP: 0px; FONT-SIZE: 10pt; MARGIN-LEFT: 0px; COLOR: #ffffcc; = FONT-FAMILY: Helvetica, "Times New Roman"
}
Hello,
I'm using Query Analyzer and I'm trying to select from a = table on another database...

i.e..I'm on DB2 in query analyzer and this is what my query looks like

Select *
FROM DB1..Test1

But I'm getting an error

Server: Msg 170, Level 15, State 1, Line 2Line 2: Incorrect = syntax near 'DB1.'.

This query worked on my local machine... Could it be a = set-up issue on a different server?

Thanks for any help,
Matt
--=_NextPart_001_000C_01C58C65.624CE800--
--=_NextPart_000_000B_01C58C65.624CE800
Content-Type: image/gif;
name="Blank Bkgrd.gif"
Content-Transfer-Encoding: base64
Content-ID: <000601c58c86$e95b7ac0$bf01010a@.ssimedcorp.local.com>
R0lGODlhLQAtAID/AP////f39ywAAAAALQAtAEACcAxup8vtvxKQsFon6d02898pGkgiYoCm6sq2
7iqWcmzOsmeXeA7uPJd5CYdD2g9oPF58ygqz+XhCG9JpJGmlYrPXGlfr/Yo/VW45e7amp2tou/lW
xo/zX513z+Vt+1n/tiX2pxP4NUhy2FM4xtjIUQAAOw==
--=_NextPart_000_000B_01C58C65.624CE800--This is a multi-part message in MIME format.
--=_NextPart_000_0047_01C58C61.410F5F50
Content-Type: multipart/alternative;
boundary="--=_NextPart_001_0048_01C58C61.410F5F50"
--=_NextPart_001_0048_01C58C61.410F5F50
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
BlankTry specifying the object owner:
Select *
FROM DB1.dbo.Test1
-- Kevin Hill
President 3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your =questions
"Matthew Beirn" <mbeirn@.ssimed.com> wrote in message =news:%23IGqqbIjFHA.3704@.TK2MSFTNGP10.phx.gbl...
Hello,
I'm using Query Analyzer and I'm trying to select from a table on =another database...
i.e..I'm on DB2 in query analyzer and this is what my query looks like
Select *
FROM DB1..Test1
But I'm getting an error
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'DB1.'.
This query worked on my local machine... Could it be a set-up issue =on a different server?
Thanks for any help,
Matt
--=_NextPart_001_0048_01C58C61.410F5F50
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

--=_NextPart_001_0048_01C58C61.410F5F50--
--=_NextPart_000_0047_01C58C61.410F5F50
Content-Type: image/gif;
name="Blank Bkgrd.gif"
Content-Transfer-Encoding: base64
Content-ID: <004201c58c8b$29df4cd0$be0a050a@.newbridgeis.com>
R0lGODlhLQAtAID/AP////f39ywAAAAALQAtAEACcAxup8vtvxKQsFon6d02898pGkgiYoCm6sq2
7iqWcmzOsmeXeA7uPJd5CYdD2g9oPF58ygqz+XhCG9JpJGmlYrPXGlfr/Yo/VW45e7amp2tou/lW
xo/zX513z+Vt+1n/tiX2pxP4NUhy2FM4xtjIUQAAOw==--=_NextPart_000_0047_01C58C61.410F5F50--|||This is a multi-part message in MIME format.
--=_NextPart_000_000D_01C58C6B.0831D500
Content-Type: multipart/alternative;
boundary="--=_NextPart_001_000E_01C58C6B.0831D500"
--=_NextPart_001_000E_01C58C6B.0831D500
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
BlankThanks for the help Kevin,
I actually tried that with no luck whatsoever..... Thanks for the =insight, I appreciate the feedback.
Matt
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message =news:%23r5pqsIjFHA.3300@.TK2MSFTNGP15.phx.gbl...
Try specifying the object owner:
Select *
FROM DB1.dbo.Test1
-- Kevin Hill
President 3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area =DBAs.
www.experts-exchange.com - experts compete for points to answer your =questions
"Matthew Beirn" <mbeirn@.ssimed.com> wrote in message =news:%23IGqqbIjFHA.3704@.TK2MSFTNGP10.phx.gbl...
Hello,
I'm using Query Analyzer and I'm trying to select from a table on =another database...
i.e..I'm on DB2 in query analyzer and this is what my query looks =like
Select *
FROM DB1..Test1
But I'm getting an error
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'DB1.'.
This query worked on my local machine... Could it be a set-up =issue on a different server?
Thanks for any help,
Matt
--=_NextPart_001_000E_01C58C6B.0831D500
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

--=_NextPart_001_000E_01C58C6B.0831D500--
--=_NextPart_000_000D_01C58C6B.0831D500
Content-Type: image/gif;
name="Blank Bkgrd.gif"
Content-Transfer-Encoding: base64
Content-ID: <000801c58c8c$8c800f40$bf01010a@.ssimedcorp.local.com>
R0lGODlhLQAtAID/AP////f39ywAAAAALQAtAEACcAxup8vtvxKQsFon6d02898pGkgiYoCm6sq2
7iqWcmzOsmeXeA7uPJd5CYdD2g9oPF58ygqz+XhCG9JpJGmlYrPXGlfr/Yo/VW45e7amp2tou/lW
xo/zX513z+Vt+1n/tiX2pxP4NUhy2FM4xtjIUQAAOw==--=_NextPart_000_000D_01C58C6B.0831D500--|||This is a multi-part message in MIME format.
--=_NextPart_000_0031_01C58C6C.45DE4B80
Content-Type: multipart/alternative;
boundary="--=_NextPart_001_0032_01C58C6C.45DE4B80"
--=_NextPart_001_0032_01C58C6C.45DE4B80
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
BlankAny Other Suggestions?......
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message =news:%23r5pqsIjFHA.3300@.TK2MSFTNGP15.phx.gbl...
Try specifying the object owner:
Select *
FROM DB1.dbo.Test1
-- Kevin Hill
President 3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area =DBAs.
www.experts-exchange.com - experts compete for points to answer your =questions
"Matthew Beirn" <mbeirn@.ssimed.com> wrote in message =news:%23IGqqbIjFHA.3704@.TK2MSFTNGP10.phx.gbl...
Hello,
I'm using Query Analyzer and I'm trying to select from a table on =another database...
i.e..I'm on DB2 in query analyzer and this is what my query looks =like
Select *
FROM DB1..Test1
But I'm getting an error
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'DB1.'.
This query worked on my local machine... Could it be a set-up =issue on a different server?
Thanks for any help,
Matt
--=_NextPart_001_0032_01C58C6C.45DE4B80
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

--=_NextPart_001_0032_01C58C6C.45DE4B80--
--=_NextPart_000_0031_01C58C6C.45DE4B80
Content-Type: image/gif;
name="Blank Bkgrd.gif"
Content-Transfer-Encoding: base64
Content-ID: <002c01c58c8d$cc2f81b0$bf01010a@.ssimedcorp.local.com>
R0lGODlhLQAtAID/AP////f39ywAAAAALQAtAEACcAxup8vtvxKQsFon6d02898pGkgiYoCm6sq2
7iqWcmzOsmeXeA7uPJd5CYdD2g9oPF58ygqz+XhCG9JpJGmlYrPXGlfr/Yo/VW45e7amp2tou/lW
xo/zX513z+Vt+1n/tiX2pxP4NUhy2FM4xtjIUQAAOw==--=_NextPart_000_0031_01C58C6C.45DE4B80--|||This is a multi-part message in MIME format.
--=_NextPart_000_0092_01C58C65.02A65D50
Content-Type: multipart/alternative;
boundary="--=_NextPart_001_0093_01C58C65.02A65D50"
--=_NextPart_001_0093_01C58C65.02A65D50
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
BlankNone. I pasted your code into my QA and it parses correctly (as I =assumed). When I ran it, I got the expected:
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'DB1..Test1'.
-- Kevin Hill
President 3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your =questions
"Matthew Beirn" <mbeirn@.ssimed.com> wrote in message =news:OgBOO3IjFHA.1148@.TK2MSFTNGP12.phx.gbl...
Any Other Suggestions?......
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message =news:%23r5pqsIjFHA.3300@.TK2MSFTNGP15.phx.gbl...
Try specifying the object owner:
Select *
FROM DB1.dbo.Test1
-- Kevin Hill
President 3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area =DBAs.
www.experts-exchange.com - experts compete for points to answer your =questions
"Matthew Beirn" <mbeirn@.ssimed.com> wrote in message =news:%23IGqqbIjFHA.3704@.TK2MSFTNGP10.phx.gbl...
Hello,
I'm using Query Analyzer and I'm trying to select from a table =on another database...
i.e..I'm on DB2 in query analyzer and this is what my query looks =like
Select *
FROM DB1..Test1
But I'm getting an error
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'DB1.'.
This query worked on my local machine... Could it be a set-up =issue on a different server?
Thanks for any help,
Matt
--=_NextPart_001_0093_01C58C65.02A65D50
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

Blank
BODY {
MARGIN-TOP: 25px; FONT-SIZE: 10pt; MARGIN-LEFT: 25px; COLOR: #000000; =FONT-FAMILY: Arial, Helvetica
}
P.msoNormal {
MARGIN-TOP: 0px; FONT-SIZE: 10pt; MARGIN-LEFT: 0px; COLOR: #ffffcc; =FONT-FAMILY: Helvetica, "Times New Roman"
}
LI.msoNormal {
MARGIN-TOP: 0px; FONT-SIZE: 10pt; MARGIN-LEFT: 0px; COLOR: #ffffcc; =FONT-FAMILY: Helvetica, "Times New Roman"
}
None. I pasted your code into my QA and it parses correctly =(as I assumed). When I ran it, I got the expected:
Server: Msg 208, Level 16, State 1, Line 1Invalid object name 'DB1..Test1'.
-- Kevin HillPresident 3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm">www.3nf-inc.com/NewsGroups=.htm
http://www.DallasDBAs.com/forum">www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
http://www.experts-exchange.com">www.experts-exchange.com - experts compete for points to answer your questions
"Matthew Beirn" wrote in =message news:OgBOO3IjFHA.1148=@.TK2MSFTNGP12.phx.gbl...
Any Other Suggestions?......
"Kevin3NF" wrote in message news:%23r5pqsIjFHA.=3300@.TK2MSFTNGP15.phx.gbl...
Try specifying the object owner:

Select *
FROM DB1.dbo.Test1
-- Kevin HillPresident 3NF Consulting

http://www.3nf-inc.com/NewsGroups.htm">www.3nf-inc.com/NewsGroups=.htm

http://www.DallasDBAs.com/forum">www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.

http://www.experts-exchange.com">www.experts-exchange.com - experts compete for points to answer your questions

"Matthew Beirn" wrote =in message news:%23IGqqbIjFHA.=3704@.TK2MSFTNGP10.phx.gbl...
Hello,
I'm using Query Analyzer and I'm trying to select from =a table on another database...

i.e..I'm on DB2 in query analyzer and this is what my query =looks like

Select *
FROM DB1..Test1

But I'm getting an error

Server: Msg 170, Level 15, State 1, Line 2Line 2: =Incorrect syntax near 'DB1.'.

This query worked on my local machine... Could it =be a set-up issue on a different server?

Thanks for any help,
=Matt

--=_NextPart_001_0093_01C58C65.02A65D50--
--=_NextPart_000_0092_01C58C65.02A65D50
Content-Type: image/gif;
name="Blank Bkgrd.gif"
Content-Transfer-Encoding: base64
Content-ID: <008d01c58c8e$eb764ad0$be0a050a@.newbridgeis.com>
R0lGODlhLQAtAID/AP////f39ywAAAAALQAtAEACcAxup8vtvxKQsFon6d02898pGkgiYoCm6sq2
7iqWcmzOsmeXeA7uPJd5CYdD2g9oPF58ygqz+XhCG9JpJGmlYrPXGlfr/Yo/VW45e7amp2tou/lW
xo/zX513z+Vt+1n/tiX2pxP4NUhy2FM4xtjIUQAAOw==--=_NextPart_000_0092_01C58C65.02A65D50--|||If your database name were not valid (or table name, for that matter), the
error you should get is:
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'DB1..Test1'.
Therefore, that leads me to believe that there is more to your query than
you posted.|||Now you post a different error message than you posted earlier. "Invalid object name" simply states
that there is no table or view named "Test1" inside the DB1 database.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
news:%23fCPs7IjFHA.2156@.TK2MSFTNGP14.phx.gbl...
BlankNone. I pasted your code into my QA and it parses correctly (as I assumed). When I ran it, I
got the expected:
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'DB1..Test1'.
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your questions
"Matthew Beirn" <mbeirn@.ssimed.com> wrote in message news:OgBOO3IjFHA.1148@.TK2MSFTNGP12.phx.gbl...
Any Other Suggestions?......
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
news:%23r5pqsIjFHA.3300@.TK2MSFTNGP15.phx.gbl...
Try specifying the object owner:
Select *
FROM DB1.dbo.Test1
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your questions
"Matthew Beirn" <mbeirn@.ssimed.com> wrote in message
news:%23IGqqbIjFHA.3704@.TK2MSFTNGP10.phx.gbl...
Hello,
I'm using Query Analyzer and I'm trying to select from a table on another database...
i.e..I'm on DB2 in query analyzer and this is what my query looks like
Select *
FROM DB1..Test1
But I'm getting an error
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'DB1.'.
This query worked on my local machine... Could it be a set-up issue on a different server?
Thanks for any help,
Matt|||Yes there was... My database name was a number.
I had to enclose in quotations...
Thanks for your help
"Scott Morris" <bogus@.bogus.com> wrote in message
news:%23Xs4C9IjFHA.3012@.TK2MSFTNGP12.phx.gbl...
> If your database name were not valid (or table name, for that matter), the
> error you should get is:
> Server: Msg 208, Level 16, State 1, Line 1
> Invalid object name 'DB1..Test1'.
> Therefore, that leads me to believe that there is more to your query than
> you posted.
>|||That was me, not the OP...;-)
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e9vm%23DJjFHA.3704@.TK2MSFTNGP10.phx.gbl...
> Now you post a different error message than you posted earlier. "Invalid
> object name" simply states that there is no table or view named "Test1"
> inside the DB1 database.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
> news:%23fCPs7IjFHA.2156@.TK2MSFTNGP14.phx.gbl...
> BlankNone. I pasted your code into my QA and it parses correctly (as I
> assumed). When I ran it, I got the expected:
> Server: Msg 208, Level 16, State 1, Line 1
> Invalid object name 'DB1..Test1'.
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
> www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
> www.experts-exchange.com - experts compete for points to answer your
> questions
>
> "Matthew Beirn" <mbeirn@.ssimed.com> wrote in message
> news:OgBOO3IjFHA.1148@.TK2MSFTNGP12.phx.gbl...
> Any Other Suggestions?......
> "Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
> news:%23r5pqsIjFHA.3300@.TK2MSFTNGP15.phx.gbl...
> Try specifying the object owner:
> Select *
> FROM DB1.dbo.Test1
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
> www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
> www.experts-exchange.com - experts compete for points to answer your
> questions
>
> "Matthew Beirn" <mbeirn@.ssimed.com> wrote in message
> news:%23IGqqbIjFHA.3704@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I'm using Query Analyzer and I'm trying to select from a table on
> another database...
> i.e..I'm on DB2 in query analyzer and this is what my query looks
> like
> Select *
> FROM DB1..Test1
> But I'm getting an error
> Server: Msg 170, Level 15, State 1, Line 2
> Line 2: Incorrect syntax near 'DB1.'.
> This query worked on my local machine... Could it be a set-up issue
> on a different server?
> Thanks for any help,
> Matt|||Ahh, sloppy me... :-)
Thanks for catching that, Kevin.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
news:%23YgOUYJjFHA.1044@.tk2msftngp13.phx.gbl...
> That was me, not the OP...;-)
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
> www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
> www.experts-exchange.com - experts compete for points to answer your questions
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:e9vm%23DJjFHA.3704@.TK2MSFTNGP10.phx.gbl...
>> Now you post a different error message than you posted earlier. "Invalid object name" simply
>> states that there is no table or view named "Test1" inside the DB1 database.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
>> news:%23fCPs7IjFHA.2156@.TK2MSFTNGP14.phx.gbl...
>> BlankNone. I pasted your code into my QA and it parses correctly (as I assumed). When I ran it,
>> I got the expected:
>> Server: Msg 208, Level 16, State 1, Line 1
>> Invalid object name 'DB1..Test1'.
>> --
>> Kevin Hill
>> President
>> 3NF Consulting
>> www.3nf-inc.com/NewsGroups.htm
>> www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
>> www.experts-exchange.com - experts compete for points to answer your questions
>>
>> "Matthew Beirn" <mbeirn@.ssimed.com> wrote in message
>> news:OgBOO3IjFHA.1148@.TK2MSFTNGP12.phx.gbl...
>> Any Other Suggestions?......
>> "Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
>> news:%23r5pqsIjFHA.3300@.TK2MSFTNGP15.phx.gbl...
>> Try specifying the object owner:
>> Select *
>> FROM DB1.dbo.Test1
>> --
>> Kevin Hill
>> President
>> 3NF Consulting
>> www.3nf-inc.com/NewsGroups.htm
>> www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
>> www.experts-exchange.com - experts compete for points to answer your questions
>>
>> "Matthew Beirn" <mbeirn@.ssimed.com> wrote in message
>> news:%23IGqqbIjFHA.3704@.TK2MSFTNGP10.phx.gbl...
>> Hello,
>> I'm using Query Analyzer and I'm trying to select from a table on another database...
>> i.e..I'm on DB2 in query analyzer and this is what my query looks like
>> Select *
>> FROM DB1..Test1
>> But I'm getting an error
>> Server: Msg 170, Level 15, State 1, Line 2
>> Line 2: Incorrect syntax near 'DB1.'.
>> This query worked on my local machine... Could it be a set-up issue on a different server?
>> Thanks for any help,
>> Matt
>

Accessing Different Databases on Same Server with Dot Notation.

BlankHello,
I'm using Query Analyzer and I'm trying to select from a table on another database...
i.e..I'm on DB2 in query analyzer and this is what my query looks like
Select *
FROM DB1..Test1
But I'm getting an error
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'DB1.'.
This query worked on my local machine... Could it be a set-up issue on a different server?
Thanks for any help,
Matt
BlankTry specifying the object owner:
Select *
FROM DB1.dbo.Test1
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your questions
"Matthew Beirn" <mbeirn@.ssimed.com> wrote in message news:%23IGqqbIjFHA.3704@.TK2MSFTNGP10.phx.gbl...
Hello,
I'm using Query Analyzer and I'm trying to select from a table on another database...
i.e..I'm on DB2 in query analyzer and this is what my query looks like
Select *
FROM DB1..Test1
But I'm getting an error
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'DB1.'.
This query worked on my local machine... Could it be a set-up issue on a different server?
Thanks for any help,
Matt
|||BlankThanks for the help Kevin,
I actually tried that with no luck whatsoever..... Thanks for the insight, I appreciate the feedback.
Matt
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message news:%23r5pqsIjFHA.3300@.TK2MSFTNGP15.phx.gbl...
Try specifying the object owner:
Select *
FROM DB1.dbo.Test1
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your questions
"Matthew Beirn" <mbeirn@.ssimed.com> wrote in message news:%23IGqqbIjFHA.3704@.TK2MSFTNGP10.phx.gbl...
Hello,
I'm using Query Analyzer and I'm trying to select from a table on another database...
i.e..I'm on DB2 in query analyzer and this is what my query looks like
Select *
FROM DB1..Test1
But I'm getting an error
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'DB1.'.
This query worked on my local machine... Could it be a set-up issue on a different server?
Thanks for any help,
Matt
|||BlankAny Other Suggestions?......
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message news:%23r5pqsIjFHA.3300@.TK2MSFTNGP15.phx.gbl...
Try specifying the object owner:
Select *
FROM DB1.dbo.Test1
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your questions
"Matthew Beirn" <mbeirn@.ssimed.com> wrote in message news:%23IGqqbIjFHA.3704@.TK2MSFTNGP10.phx.gbl...
Hello,
I'm using Query Analyzer and I'm trying to select from a table on another database...
i.e..I'm on DB2 in query analyzer and this is what my query looks like
Select *
FROM DB1..Test1
But I'm getting an error
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'DB1.'.
This query worked on my local machine... Could it be a set-up issue on a different server?
Thanks for any help,
Matt
|||BlankNone. I pasted your code into my QA and it parses correctly (as I assumed). When I ran it, I got the expected:
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'DB1..Test1'.
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your questions
"Matthew Beirn" <mbeirn@.ssimed.com> wrote in message news:OgBOO3IjFHA.1148@.TK2MSFTNGP12.phx.gbl...
Any Other Suggestions?......
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message news:%23r5pqsIjFHA.3300@.TK2MSFTNGP15.phx.gbl...
Try specifying the object owner:
Select *
FROM DB1.dbo.Test1
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your questions
"Matthew Beirn" <mbeirn@.ssimed.com> wrote in message news:%23IGqqbIjFHA.3704@.TK2MSFTNGP10.phx.gbl...
Hello,
I'm using Query Analyzer and I'm trying to select from a table on another database...
i.e..I'm on DB2 in query analyzer and this is what my query looks like
Select *
FROM DB1..Test1
But I'm getting an error
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'DB1.'.
This query worked on my local machine... Could it be a set-up issue on a different server?
Thanks for any help,
Matt
|||If your database name were not valid (or table name, for that matter), the
error you should get is:
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'DB1..Test1'.
Therefore, that leads me to believe that there is more to your query than
you posted.
|||Now you post a different error message than you posted earlier. "Invalid object name" simply states
that there is no table or view named "Test1" inside the DB1 database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
news:%23fCPs7IjFHA.2156@.TK2MSFTNGP14.phx.gbl...
BlankNone. I pasted your code into my QA and it parses correctly (as I assumed). When I ran it, I
got the expected:
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'DB1..Test1'.
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your questions
"Matthew Beirn" <mbeirn@.ssimed.com> wrote in message news:OgBOO3IjFHA.1148@.TK2MSFTNGP12.phx.gbl...
Any Other Suggestions?......
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
news:%23r5pqsIjFHA.3300@.TK2MSFTNGP15.phx.gbl...
Try specifying the object owner:
Select *
FROM DB1.dbo.Test1
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your questions
"Matthew Beirn" <mbeirn@.ssimed.com> wrote in message
news:%23IGqqbIjFHA.3704@.TK2MSFTNGP10.phx.gbl...
Hello,
I'm using Query Analyzer and I'm trying to select from a table on another database...
i.e..I'm on DB2 in query analyzer and this is what my query looks like
Select *
FROM DB1..Test1
But I'm getting an error
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'DB1.'.
This query worked on my local machine... Could it be a set-up issue on a different server?
Thanks for any help,
Matt
|||Yes there was... My database name was a number.
I had to enclose in quotations...
Thanks for your help
"Scott Morris" <bogus@.bogus.com> wrote in message
news:%23Xs4C9IjFHA.3012@.TK2MSFTNGP12.phx.gbl...
> If your database name were not valid (or table name, for that matter), the
> error you should get is:
> Server: Msg 208, Level 16, State 1, Line 1
> Invalid object name 'DB1..Test1'.
> Therefore, that leads me to believe that there is more to your query than
> you posted.
>
|||That was me, not the OP...;-)
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e9vm%23DJjFHA.3704@.TK2MSFTNGP10.phx.gbl...
> Now you post a different error message than you posted earlier. "Invalid
> object name" simply states that there is no table or view named "Test1"
> inside the DB1 database.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
> news:%23fCPs7IjFHA.2156@.TK2MSFTNGP14.phx.gbl...
> BlankNone. I pasted your code into my QA and it parses correctly (as I
> assumed). When I ran it, I got the expected:
> Server: Msg 208, Level 16, State 1, Line 1
> Invalid object name 'DB1..Test1'.
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
> www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
> www.experts-exchange.com - experts compete for points to answer your
> questions
>
> "Matthew Beirn" <mbeirn@.ssimed.com> wrote in message
> news:OgBOO3IjFHA.1148@.TK2MSFTNGP12.phx.gbl...
> Any Other Suggestions?......
> "Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
> news:%23r5pqsIjFHA.3300@.TK2MSFTNGP15.phx.gbl...
> Try specifying the object owner:
> Select *
> FROM DB1.dbo.Test1
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
> www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
> www.experts-exchange.com - experts compete for points to answer your
> questions
>
> "Matthew Beirn" <mbeirn@.ssimed.com> wrote in message
> news:%23IGqqbIjFHA.3704@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I'm using Query Analyzer and I'm trying to select from a table on
> another database...
> i.e..I'm on DB2 in query analyzer and this is what my query looks
> like
> Select *
> FROM DB1..Test1
> But I'm getting an error
> Server: Msg 170, Level 15, State 1, Line 2
> Line 2: Incorrect syntax near 'DB1.'.
> This query worked on my local machine... Could it be a set-up issue
> on a different server?
> Thanks for any help,
> Matt
|||Ahh, sloppy me... :-)
Thanks for catching that, Kevin.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
news:%23YgOUYJjFHA.1044@.tk2msftngp13.phx.gbl...
> That was me, not the OP...;-)
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
> www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
> www.experts-exchange.com - experts compete for points to answer your questions
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:e9vm%23DJjFHA.3704@.TK2MSFTNGP10.phx.gbl...
>

Accessing Different Databases on Same Server with Dot Notation.

BlankHello,
I'm using Query Analyzer and I'm trying to select from a table on another da
tabase...
i.e..I'm on DB2 in query analyzer and this is what my query looks like
Select *
FROM DB1..Test1
But I'm getting an error
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'DB1.'.
This query worked on my local machine... Could it be a set-up issue on a d
ifferent server?
Thanks for any help,
MattBlankTry specifying the object owner:
Select *
FROM DB1.dbo.Test1
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your questio
ns
"Matthew Beirn" <mbeirn@.ssimed.com> wrote in message news:%23IGqqbIjFHA.3704
@.TK2MSFTNGP10.phx.gbl...
Hello,
I'm using Query Analyzer and I'm trying to select from a table on another da
tabase...
i.e..I'm on DB2 in query analyzer and this is what my query looks like
Select *
FROM DB1..Test1
But I'm getting an error
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'DB1.'.
This query worked on my local machine... Could it be a set-up issue on a d
ifferent server?
Thanks for any help,
Matt|||BlankThanks for the help Kevin,
I actually tried that with no luck whatsoever..... Thanks for the insight,
I appreciate the feedback.
Matt
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message news:%23r
5pqsIjFHA.3300@.TK2MSFTNGP15.phx.gbl...
Try specifying the object owner:
Select *
FROM DB1.dbo.Test1
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your questio
ns
"Matthew Beirn" <mbeirn@.ssimed.com> wrote in message news:%23IGqqbIjFHA.3704
@.TK2MSFTNGP10.phx.gbl...
Hello,
I'm using Query Analyzer and I'm trying to select from a table on another da
tabase...
i.e..I'm on DB2 in query analyzer and this is what my query looks like
Select *
FROM DB1..Test1
But I'm getting an error
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'DB1.'.
This query worked on my local machine... Could it be a set-up issue on a d
ifferent server?
Thanks for any help,
Matt|||BlankAny Other Suggestions?......
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message news:%23r
5pqsIjFHA.3300@.TK2MSFTNGP15.phx.gbl...
Try specifying the object owner:
Select *
FROM DB1.dbo.Test1
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your questio
ns
"Matthew Beirn" <mbeirn@.ssimed.com> wrote in message news:%23IGqqbIjFHA.3704
@.TK2MSFTNGP10.phx.gbl...
Hello,
I'm using Query Analyzer and I'm trying to select from a table on another da
tabase...
i.e..I'm on DB2 in query analyzer and this is what my query looks like
Select *
FROM DB1..Test1
But I'm getting an error
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'DB1.'.
This query worked on my local machine... Could it be a set-up issue on a d
ifferent server?
Thanks for any help,
Matt|||BlankNone. I pasted your code into my QA and it parses correctly (as I assu
med). When I ran it, I got the expected:
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'DB1..Test1'.
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your questio
ns
"Matthew Beirn" <mbeirn@.ssimed.com> wrote in message news:OgBOO3IjFHA.1148@.T
K2MSFTNGP12.phx.gbl...
Any Other Suggestions?......
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message news:%23r
5pqsIjFHA.3300@.TK2MSFTNGP15.phx.gbl...
Try specifying the object owner:
Select *
FROM DB1.dbo.Test1
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your questio
ns
"Matthew Beirn" <mbeirn@.ssimed.com> wrote in message news:%23IGqqbIjFHA.3704
@.TK2MSFTNGP10.phx.gbl...
Hello,
I'm using Query Analyzer and I'm trying to select from a table on another da
tabase...
i.e..I'm on DB2 in query analyzer and this is what my query looks like
Select *
FROM DB1..Test1
But I'm getting an error
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'DB1.'.
This query worked on my local machine... Could it be a set-up issue on a d
ifferent server?
Thanks for any help,
Matt|||If your database name were not valid (or table name, for that matter), the
error you should get is:
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'DB1..Test1'.
Therefore, that leads me to believe that there is more to your query than
you posted.|||Now you post a different error message than you posted earlier. "Invalid obj
ect name" simply states
that there is no table or view named "Test1" inside the DB1 database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
news:%23fCPs7IjFHA.2156@.TK2MSFTNGP14.phx.gbl...
BlankNone. I pasted your code into my QA and it parses correctly (as I assu
med). When I ran it, I
got the expected:
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'DB1..Test1'.
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your questio
ns
"Matthew Beirn" <mbeirn@.ssimed.com> wrote in message news:OgBOO3IjFHA.1148@.T
K2MSFTNGP12.phx.gbl...
Any Other Suggestions?......
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
news:%23r5pqsIjFHA.3300@.TK2MSFTNGP15.phx.gbl...
Try specifying the object owner:
Select *
FROM DB1.dbo.Test1
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your questio
ns
"Matthew Beirn" <mbeirn@.ssimed.com> wrote in message
news:%23IGqqbIjFHA.3704@.TK2MSFTNGP10.phx.gbl...
Hello,
I'm using Query Analyzer and I'm trying to select from a table on another da
tabase...
i.e..I'm on DB2 in query analyzer and this is what my query looks like
Select *
FROM DB1..Test1
But I'm getting an error
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'DB1.'.
This query worked on my local machine... Could it be a set-up issue on a d
ifferent server?
Thanks for any help,
Matt|||Yes there was... My database name was a number.
I had to enclose in quotations...
Thanks for your help
"Scott Morris" <bogus@.bogus.com> wrote in message
news:%23Xs4C9IjFHA.3012@.TK2MSFTNGP12.phx.gbl...
> If your database name were not valid (or table name, for that matter), the
> error you should get is:
> Server: Msg 208, Level 16, State 1, Line 1
> Invalid object name 'DB1..Test1'.
> Therefore, that leads me to believe that there is more to your query than
> you posted.
>|||That was me, not the OP...;-)
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e9vm%23DJjFHA.3704@.TK2MSFTNGP10.phx.gbl...
> Now you post a different error message than you posted earlier. "Invalid
> object name" simply states that there is no table or view named "Test1"
> inside the DB1 database.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
> news:%23fCPs7IjFHA.2156@.TK2MSFTNGP14.phx.gbl...
> BlankNone. I pasted your code into my QA and it parses correctly (as I
> assumed). When I ran it, I got the expected:
> Server: Msg 208, Level 16, State 1, Line 1
> Invalid object name 'DB1..Test1'.
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
> www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
> www.experts-exchange.com - experts compete for points to answer your
> questions
>
> "Matthew Beirn" <mbeirn@.ssimed.com> wrote in message
> news:OgBOO3IjFHA.1148@.TK2MSFTNGP12.phx.gbl...
> Any Other Suggestions?......
> "Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
> news:%23r5pqsIjFHA.3300@.TK2MSFTNGP15.phx.gbl...
> Try specifying the object owner:
> Select *
> FROM DB1.dbo.Test1
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
> www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
> www.experts-exchange.com - experts compete for points to answer your
> questions
>
> "Matthew Beirn" <mbeirn@.ssimed.com> wrote in message
> news:%23IGqqbIjFHA.3704@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I'm using Query Analyzer and I'm trying to select from a table on
> another database...
> i.e..I'm on DB2 in query analyzer and this is what my query looks
> like
> Select *
> FROM DB1..Test1
> But I'm getting an error
> Server: Msg 170, Level 15, State 1, Line 2
> Line 2: Incorrect syntax near 'DB1.'.
> This query worked on my local machine... Could it be a set-up issue
> on a different server?
> Thanks for any help,
> Matt|||Ahh, sloppy me... :-)
Thanks for catching that, Kevin.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
news:%23YgOUYJjFHA.1044@.tk2msftngp13.phx.gbl...
> That was me, not the OP...;-)
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
> www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
> www.experts-exchange.com - experts compete for points to answer your quest
ions
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:e9vm%23DJjFHA.3704@.TK2MSFTNGP10.phx.gbl...
>

Accessing Database on another server

Hi All,
I have application where databases/tables spread between two
servers(different building, wireless connection). During data entries from
front-end, the application must access the tables on both servers
interactively.
The question is :
- is Linked Server is the only way to access tables on another server ?
- How is the performance of accessing tables on local server compared to
accessing tables on linked server ?
Thanks for your comments,
KristTristant,
"tristant" <krislioe@.cbn.net.id> wrote in message news:OpzPgQRnDHA.1408@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> I have application where databases/tables spread between two
> servers(different building, wireless connection). During data entries from
> front-end, the application must access the tables on both servers
> interactively.
> The question is :
> - is Linked Server is the only way to access tables on another server ?
No. You can replicate the data from one server to the other, or use
openrowset for an adhoc connection
> - How is the performance of accessing tables on local server compared to
> accessing tables on linked server ?
As you would expect - much worse.
Especially with WiFi, where you don't get all the bandwidth claimed anyway
and should be using VPN or other overhead to keep the link safe.
That said, you are not normally puting a whole lot of data through the link, so it
is still perfectly useable, my preference would be to have a client connection to
only one server, and use replication. But you don't say anything about updates,
or data volumes so it's a bit hard to comment.
Regards
AJ

accessing data in different database

Hello Everyone,

Is it possible to write a query that can access data in differnt database so I have "trainee" and "training" databases. In want to access one training table in trainee database through a query. How can I do this.

Thanks.

Hi,

Yes it is possible just use database and scheme name in table name for example:

Database.scheme.tableName

select * from [trainee].[dbo].[training]

select * from [training].[dbo].[training]

both select statement should work from both databases. Be sure that user which run query have rights to access tables you use in your queries in both databases. It is also good to note in code that procedure use another database which has to exists.

Thanks

JPazgier

Tuesday, March 20, 2012

Accessing 2 Sql databases concurrently ?

Hi,
I was wondering how, if possible you are able to access 2
databases on 2 different sql servers (7, 2000)
concurrently?
Please do not multi-post. I answered your question in .server.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Chad" <anonymous@.discussions.microsoft.com> wrote in message
news:1f9c01c485fa$9cfb2740$a301280a@.phx.gbl...
Hi,
I was wondering how, if possible you are able to access 2
databases on 2 different sql servers (7, 2000)
concurrently?

Accessing 2 databases on the SQL server through ASP.net

Hi everyone,

First of all I'd like to say I'm new to the forums, and in fact new to ASP.net.

I've had a couple of applications running in VB.net for the last year or so that I now am looking to move on and into ASP.net (main reason is to do with implementation to remote sites in foreign countries).

I'm currently following through the "Working with data and ASP.net 2.0" walkthrough on this site, which I must say has been a great help.

I'll start with the background stuff first Smile

I have my main database on an SQL server. I also have 2 other databases on there - Customer and Employee. The reason for these are that they are shared amongst my VB applications and I kept them as seperate databases so that one database is all that needs to be administered and maintained, and these changes are then seen on both apps (takes away redundant data as well).

In my main database, main table, is a field called CustomerID. This value relates to the CustomerID in the Customer table. In my VB app all I do is have the user select a customer name, and then the customer ID value is copied to the CustomerID field of my main table when inserting / updating a row. This is simply accomplished with 2 SQL connections and 2 dataAdapters. I also use 2 dataSets - one for Customer, one for WorkRequests (the main database)...

I use ComponentOne stuff so that my grid will show the CustomerName instead of the CustomerID etc, and the same applies when I use my Employee database - I run many dataAdapters to this for things such as Account Manager, Technologist, Creator, UpdatedBy etc etc.

Now, I'm new to VS 2005 - my apps were created in VS 2003 and .net 1.1. Can I easily re-create my apps in ASP.net? My primary goal at the moment is to make sure I can load a row, and display the "lookup" values instead of the "integer" value. As my tables are in seperate databases, I would like to know the best way of accomplishing this, if it is of course possible.

Help and advice much appriciated.

Kind Regards,

Luke

SQL supports 4-section object name, in?the?format?of?server.database.owner_name.object_name,?so?just?add?database?part?when?you?want?to?refer?to?object?in?other?database.?For?more?information,?please?refer?to:?Using Identifiers as Object Names|||

Thank you very much, you reminded me about that, I think I looked into it ages ago but then put it to the back of my mind.

To display my CustomerName field by linking to the CustomerID field I'm using;

SELECT npd_dwr.dbo.dwr.dwrnumber, npd_dwr.dbo.dwr.customerid, customerdb.dbo.customer.customername
FROM npd_dwr.dbo.dwr
INNER JOIN customerdb.dbo.customer ON npd_dwr.dbo.dwr.customerid = customerdb.dbo.customer.customerid

Thanks again,

Regards

Sunday, March 11, 2012

Access to SQL Server via WCF works only part time

We have 2 databases ( Guider and Talker ) and we have a WCF service that is logged in with a domain identity.

In our SQL Server we have the service ID added to the Data Server Logins and both Guider and Talker are given access to the user.

When we access Guider we have no problems getting data.

When we access Talker we have a login failure:

Cannot open database 'Talker' requested by the login. The login failed.

The thing that gets me is that the user is created at the Server level, in both Databases, and at the server level both databases are checked for the user. master has been set as the default database for the user.

Basically, as far as I can see Talker and Guider are configured identically! So I cannot figure out why I cannot login to the second database!

Is there a specific setting I'm missing somewhere to grant login access to the user? I'm using

Management Studio Express to manage the database.

My guess here is that the default database defined for the login that fails (Acorn\CommunicationServices) is not configured properly. Another possibility may be a typo when specifying the DB name in the client.

One test you can try to verify if the principal can really access the DB is:

*Connect as a sysadmin

* run “EXECUTE AS LOGIN = ‘login_name’ “ to impersonate the principal

* USE [Talker]

I hope this helps,

-Raul Garcia

SDE/T

SQL Server Engine

Access to SQL Server via WCF works only part time

We have 2 databases ( Guider and Talker ) and we have a WCF service that is logged in with a domain identity.

In our SQL Server we have the service ID added to the Data Server Logins and both Guider and Talker are given access to the user.

When we access Guider we have no problems getting data.

When we access Talker we have a login failure:

Cannot open database 'Talker' requested by the login. The login failed.

Basically, as far as I can see Talker and Guider are configured identically! So I cannot figure out why I cannot login to the second database!

Is there a specific setting I'm missing somewhere to grant login access to the user? I'm using

Management Studio Express to manage the database.

One test you can try to verify if the principal can really access the DB is:

*Connect as a sysadmin

* run “EXECUTE AS LOGIN = ‘login_name’ “ to impersonate the principal

* USE [Talker]

I hope this helps,

-Raul Garcia

SDE/T

SQL Server Engine

Thursday, March 8, 2012

Access to SQL server migration

I am in the process of migrating 40 access databases to SQL Server. I am migrating the tables to the backend. All of these databases are Identical in structure and purpose. The goal is to centralize all of the databases backends into one Sql server database, but the data needs to stay seperated by each location. So I added a location field to each table in the database; so that each locations data will be seperated by location. How do I go about filtering out the the data so that each location can only update, delete, an view their own data? (I dont mind a long answer.)

Hi,

If the data is seperated into different tables based on regions, then you can control access to that table using group based priviledges and add the appropriate users to that group. However, it sounds like the data is interspersed to various tables and you're using a column to keep track of the location for each row. In this case, I'm not sure what the best way to control access to the data at the row level. I'll move this thread to the security forum -- Maybe someone there has an idea.

Il-Sung.

|||

Have a look at the following thread:

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

Thanks
Laurentiu

Access to SQL replication

I have a customer who has Access databases at many sites across the country
who wants the
data replicated nightly to a centralized MS-SQL Server using broadband
connection (Cable, DSL, etc.)
I have very limited knowledge of best practices to accomplish this reliably
but know their current solution is not at all reliable. Right now they are
using some Java app that exports the Access data to CSV, then emails the
files. The emails are then manually taken, copied then imported. Very
cumbersome process and VERY unreliable. There's gotta be a better way!
I want to recomend that they contract an expert to set this up for them and
then they can maintain it. However because this isn't my area, I can't
really
say for certian what type of person they should look for or how it should be
done.
Any ideas from a 40,000 foot perspective?
Thanks in advance.
you can use merge replication for this. Each access database would enabled
as a subscriber to a single merge publication on your host SQL Server.
You will have to do some partitioning or filtering to ensure that there are
no conflicts.
"Ben" <Sorry@.TooManyViruses.com> wrote in message
news:dSvic.37377$lS2.27451@.twister.rdc-kc.rr.com...
> I have a customer who has Access databases at many sites across the
country
> who wants the
> data replicated nightly to a centralized MS-SQL Server using broadband
> connection (Cable, DSL, etc.)
> I have very limited knowledge of best practices to accomplish this
reliably
> but know their current solution is not at all reliable. Right now they are
> using some Java app that exports the Access data to CSV, then emails the
> files. The emails are then manually taken, copied then imported. Very
> cumbersome process and VERY unreliable. There's gotta be a better way!
> I want to recomend that they contract an expert to set this up for them
and
> then they can maintain it. However because this isn't my area, I can't
> really
> say for certian what type of person they should look for or how it should
be
> done.
> Any ideas from a 40,000 foot perspective?
> Thanks in advance.
>

Access to SQL Migration

At work we are migrating several Access databases to SQL Server 2005 standard edition. These Access databases have no user interface forms. They do however have a series of macros that execute make table queries.

I'm trying to determine what are options are for replacing the make table queries in the macros. My first thought was to do the work in a SQL Server stored prodecure. I am asking for advice on what our options are. Is a stored procedure the way to go? Any suggestions on how to do it?

Thanks.

Stored Procedures and Views are probably your best bet. Have you tried using the SQL Server Migration Assistant for Access?|||Thanks for your answer Euan.

Do you know if stored procedures can be scheduled to run? I believe SQL Server has some sort of job scheduler.

Thanks.|||Yes, you can schedule SQL Server stored procedures to run as a job. You can specify the owner of the job, the frequency and the task done by the job - in this case, run a stored procedure|||Thanks bass_player.|||One thing to note, there is no scheduler as such in Express, you need to use the windows scheduler and then have it call sqlcmd (command line script utility) to perform this

Access to SQL conversion!

Hi Everyone,

To start off, can I just say that I know very little about SQL databases but I have a MS access database which we are outgrowing and I think we will need to make some changes. I was wondering if some one could offer some advice. There are many programs available for upsizing to SQL but I presume these are for the back end only, is this correct? Is upsizing a bad idea or a very difficult one?

I was also wondering about keeping my Access front end and connecting it to a SQL back end, is this a good idea?

Any alternative suggestions would be appreciated.

Regards

Nora.It depends on a lot of things...

It depends how the app is written...more than likely your form controls are all open record sets...

What's your data access model?

That's make access front end to sql server crawl...

How normalized is your data, if you have to rewite it'd be good to have a good data model...

How much data are we talking about? How big is Access in mb?

How many users?

Do you know how much sql server costs?|||How 'bout Visual Basic on a SQL Server back end? We are converting, albeit at a snails pace, our processes using Access backend to SQL Server. This is a different(another job) than the other issue I posted on re: Read Only in SQL Server Stored procedures. Here at least, I have full privileges, even updates on table than my a.m. job. Is there a good tutorial page on VB with SQL Server? I know both to some extent though my VB is rusty.

ddave|||I have upgraded Access apps to SQL Server a few times and been surprised how much the built-in Upgrade Wizard can do correctly for you. I'd give it a try. Note that you don't want to convert to an Access 'Project' file if you need to have front-end tables as well as a back-end database. You can't use front-end tables in the .adp files.

Depending on the complexity of your relationships in the DB you can get almost everything converted over with the Wizard. Some basic testing will show you where things flopped. You won't have too much trouble figuring out how to re-create things in SQL Server--it's very user-friendly.

If you will keep your front end in Access I highly recommend the enormous Access 2000 Developer's Handbook, Volume 2: Enterprise Edition (Litwin, Getz & Gilbert) Hopefully they have a 2002 version out by now but the 2000 version saved my you-know-what many times and gives great info on how to work with SQL Server from Access.

We have several large production apps running in Access to SQL Server. In terms of SQL Server and Access playing nicely, they generally do. Using the upsizing wizard will help ensure that the back end is set up to talk to Access properly.

Good Luck.

Tuesday, March 6, 2012

Access to different databases in different domains

Hi everybody,

there are several SQL-Server 2000 databases within a company located
on different servers in different domains. On every database you can
find the same table X.

I want to merge these tables X (UNION query) and print the result with
a Crystal Report.

Unfortunately I only have little knowledge on security, domains,
distributed applications.

Thanks for help.Have you checked Linked Servers?

Jack Vamvas
___________________________________
Need an IT job? http://www.itjobfeed.com
"Josef Meierhofer" <jmeierhofer@.gmx.comwrote in message
news:hga623l6c1v4fbghk7rbmp7omuaplp6tn0@.4ax.com...

Quote:

Originally Posted by

Hi everybody,
>
there are several SQL-Server 2000 databases within a company located
on different servers in different domains. On every database you can
find the same table X.
>
I want to merge these tables X (UNION query) and print the result with
a Crystal Report.
>
Unfortunately I only have little knowledge on security, domains,
distributed applications.
>
Thanks for help.
>
>

|||Josef Meierhofer (jmeierhofer@.gmx.com) writes:

Quote:

Originally Posted by

there are several SQL-Server 2000 databases within a company located
on different servers in different domains. On every database you can
find the same table X.
>
I want to merge these tables X (UNION query) and print the result with
a Crystal Report.
>
Unfortunately I only have little knowledge on security, domains,
distributed applications.

The query as such is not tricky:

SELECT ...
FROM SERVER1.db.dbo.tbl
WHERE ...
UNION ALL
SELECT ...
FROM SERVER2.db.dbo.tbl
WHERE ...
UNION ALL
...

The problem may be with setting up the linked servers. You use
sp_addlinkedserver and sp_addlinkedsrvlogin. Supposedly there is a DBA
around with whom you can discuss how to do this.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Access through MS Access ADP for everyone?

Hello,
We have recently installed SQL enterprise manager. I was testing the securi
ty to the databases when I realized that if I login to XP under a standard d
omain user account, open MS Access, create an ADP project, then under connec
tions, pick the sql server, I can see and pick any of the databases and then
I can open and view any of the tables in any of the databases. How can thi
s be possible? We are using NT authentication. This user has no account in
SQL and is just a domain user.It's allowed somehow with the security you have implemented
on the server, in the databases. It's not clear what version
or edition of SQL you are running, where the SQL Server
instance is installed - on the network and you are accessing
this over a network? It's not clear what operating system
the SQL Server is running on, is the SQL Server in a domain
or is this actually a workgroup? Did you change any of the
default security settings? Who are members of Local Admins
where SQL Server is installed? What is the status of the
guest account in Windows where SQL is installed?
What databases are you actually accessing and opening
tables? Are these system databases?
-Sue
On Tue, 11 Oct 2005 21:19:32 -0400, "Jack"
<jackhnospam@.jackandjay.com> wrote:

>Hello,
>We have recently installed SQL enterprise manager. I was testing the security to t
he databases when I realized that if I login to XP under a standard domain user acco
unt, open MS Access, create an ADP project, then under connections, pick the sql ser
ver
, I can see and pick any of the databases and then I can open and view any o
f the tables in any of the databases. How can this be possible? We are usi
ng NT authentication. This user has no account in SQL and is just a domain
user.

Saturday, February 25, 2012

Access rights to two mssql dbs via password protected role

I dont know how to arrange situation when application enduser needs to access data in two databases of mssql server concurently in those circumstances that access rights to the data should be restricted by password protected role (whose password is not known to the end user).

Detailed description of problem:

So far there was an application, that manipulated its data, saved in mssql server's database. End user authenticates to application by his (mssql server's) login name and password. The application authenticates the user by connecting to the database with the given name/password credentials, and then the application sets application role with hardcoded name/password. Thus application role sets the access rights for consequent end user's requests, delivered via application to the database server.

The goal is that end user cannot manipulate application database data when connects to the database by other means (e. g. via SQL server Manager), because he does not know the application role's password.

Now suppose that there are two applications (A1, A2), both using the same model for access restrictions. Each of them has its own database (A1DB, A2DB) and its own application role (A1R residing in A1DB, A2R residing in A2DB). End user (login) X can manipulate A1DB data when connects via A1, and A2DB data when connects via A2, and NO data when connects by other means.

Finally suppose that some subset of A2 data (let's say one table) is useful to see also via A1 application. There is no problem to add to A1DB view, that shows data from A2DB table together with A1DB tables. But when the user is connected via A1, he cannot see the data, because query on A1 view fails (user has not access rights on A2 data).

The access rights for A1 enduser cannot be set by no means i know because:

1) I cannot set the rights via public (guest) access because in that case they will be accessible to any users connected by any third party products, which is supposed to be security hole.

2) I cannot set the rights via dbuser or dbrole privileges, because they will not work when connected via A1 application (setting the app role suppresses the db privileges)

3) I cannot set the rights via application role because two application roles cannot be set concurrently.

4) I cannot abandon using application roles mechanism and use database roles mechanism, because db roles cannot be protected by independent password (not known to the enduser).

Please can anybody review my problem and either find the mistake in my approach, or propose other solution? So far I suppose the problem is my ignorance, because I am not great mssql expert.

I hope I can explain why approles will not work on your particular scenario. Approles are principals defined within a database, and they have absolutely no presence on the server or on other databases. Even if the 2 approles are named the same and have the same passwords, because they are defined in different databases they are not the same principals. The fact that after establishing an approle it is possible to go to a different DB as “guest” account (given that guest access is allowed) is a consequence of the previous SQL Server security model and that we have to maintain it for backwards compatibility reasons.

One potential solution for your problem would be to use the new impersonation mechanisms in SQL Server 2005. For example, you can create a module (i.e. a SP or a multistatement function) marked with EXECUTE AS (disable the login mapped to this user to prevent direct connections) with enough permission to allow that the impersonated context on A1DB can access the data on A2DB. Because you will be accessing cross-database data, you will need to either sign your module (recommended) or set the TRUSTWORTHY bit option ON on A1DB (source DB).

I would recommend the following references to understand this new impersonation model better:

· EXECUTE AS Clause http://msdn2.microsoft.com/en-us/library/ms188354.aspx

· Using EXECUTE AS in Modules http://msdn2.microsoft.com/en-us/library/ms178106.aspx

· Understanding Context Switching http://msdn2.microsoft.com/en-us/library/ms191296.aspx

· Understanding Execution Context http://msdn2.microsoft.com/en-us/library/ms187096.aspx

I also recommend reading Laurentiu’s blog as well as my own blog

· Laurentiu Cristofor’s blog http://blogs.msdn.com/lcris/

· Raul Garcia’s blog http://blogs.msdn.com/raulga/

I hope this information will be useful. Let us know if you have further questions.

-Raul Garcia

SDE/T

SQL Server Engine

|||

Thanks to Mr. Garcia

Thank you very much for your answer. It seems you have solved my problem. In fact the focus of my problem was not in (not)understanding of role mechanism in mssql server. The problem was, that i was not able to convince my managers, that the concept of roles in mssql server does not allow simple setting of access rigthts to objects in two databases via password protected roles. They did not believed me, because were used to platforms, on which it is not problem at all. After reviewing your explanation it seems, that my managers will give me more time for solving our inter-application-communication problem via more complicated way - the new impersonating mechanism.