Query 2 databases

Hi all,
Does anyone know how can I select data across 2 databases in SQL ? I want
to do something like this:
Select A.SentDate from dbo.DBA.TblA A, dbo.DBB.TblB B
Where B.FileName = '01012005.txt'
And A.AKey = B.BKey
But it doesn't work and give me an error "Invalid object name dbo.DBA.TblA",
guessing could be syntax error (?)... any idea?
Thanks !!
K.K>It should be
Database.owner.objectname as in
DBA.dbo.TblA
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"K.K." <someone@.microsoft.com> wrote in message
news:%23F$cuW6IFHA.4060@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> Does anyone know how can I select data across 2 databases in SQL ? I want
> to do something like this:
> Select A.SentDate from dbo.DBA.TblA A, dbo.DBB.TblB B
> Where B.FileName = '01012005.txt'
> And A.AKey = B.BKey
> But it doesn't work and give me an error "Invalid object name
> dbo.DBA.TblA", guessing could be syntax error (?)... any idea?
> Thanks !!
> K.K>
>
>

Query "hightest value" contents of each "folder"?

Hi,

Can anyone tell me how to select the "most recent" date values from a
grouped query? Consider the following:

CREATE TABLE [dbo].[TestQuery] ( [ID] [int] NOT NULL , [ID_Parent] [int] NOT
NULL , [Date] [datetime] NOT NULL ) ON [PRIMARY]

This is a simplified adjacency list. What I want to do is find the highest
valued item by date for each sub-tree. In other words, the single highest
date item when This.ID_Parent = That.ID_Parent. I think I first need to
group by ID_Parent, then select the TOP 1 from this query, but how to
aggregate them so I get the TOP 1 for each ID_Parent?

Thanks for any help you can give me,

RobinAnother way:

SELECT T1.id_parent, MAX(T1.id) AS id, T1.date
FROM TestQuery AS T1
WHERE T1.date =
(SELECT MAX(date)
FROM TestQuery AS T2
WHERE T1.id_parent = T2.id_parent)
GROUP BY T1.id_parent, T1.date

--
David Portas
SQL Server MVP
--

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Try this:

SELECT T1.id_parent, MAX(T1.id) AS id, T1.date
FROM TestQuery AS T1
LEFT JOIN TestQuery AS T2
ON T1.id_parent = T2.id_parent
AND T1.date < T2.date
WHERE T2.date IS NULL
GROUP BY T1.id_parent, T1.date

--
David Portas
SQL Server MVP
--

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||here is a related article:
subject: select first record from a group ordered by 3 columns
date: Nov 27 2002, 5:01 am

http://groups-beta.google.com/group...23b16be2b945c0f

David Portas wrote:
> Another way:
> SELECT T1.id_parent, MAX(T1.id) AS id, T1.date
> FROM TestQuery AS T1
> WHERE T1.date =
> (SELECT MAX(date)
> FROM TestQuery AS T2
> WHERE T1.id_parent = T2.id_parent)
> GROUP BY T1.id_parent, T1.date
> --
> David Portas
> SQL Server MVP
> --
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

qualifying Stored Procedure with owner. Why is it important/

I've been reading some posts that indicate that it is best to invoke a
Stored Procedure with the owner name. Example
exec dbo.MyStoredProc
instead of
exec MyStoredProc
I never really understood why this makes a difference.
I would appreciate it if someone could shed some light on this
Thanks in advanceWell, what about if some user 'user1' have created a procedure which is
owned by 'user1'
If you do "Exec UserProc" you will get an error, you need to use "Exec
user1.UserProc" generally it is advisable to qualify owner name before
database objects.
Things are changed in SQL 2005 where you have to qualify schema name
instead of user name for database objects
chirag shah|||in my database all objects are owned by dbo. I have read that there is a
performance impact by not qualifying the owner of the SP (even in a case
like mine where all objects are owned by dbo)
"c_shah" <shah.chirag@.netzero.net> wrote in message
news:1142951587.510508.143150@.g10g2000cwb.googlegroups.com...
> Well, what about if some user 'user1' have created a procedure which is
> owned by 'user1'
> If you do "Exec UserProc" you will get an error, you need to use "Exec
> user1.UserProc" generally it is advisable to qualify owner name before
> database objects.
> Things are changed in SQL 2005 where you have to qualify schema name
> instead of user name for database objects
> chirag shah
>|||"TJT" <TJT@.nospam.com> wrote in message
news:u0kJDZPTGHA.4864@.TK2MSFTNGP12.phx.gbl...
> in my database all objects are owned by dbo. I have read that there is a
> performance impact by not qualifying the owner of the SP (even in a case
> like mine where all objects are owned by dbo)
>
http://support.microsoft.com/default.aspx?scid=kb;en-us;263889
David|||see
http://www.sqlservercentral.com/columnists/chedgate/worstpracticenotqualifyingobjectswiththeowner.asp
for a discussion of why objects should always be qualified.
"TJT" wrote:
> in my database all objects are owned by dbo. I have read that there is a
> performance impact by not qualifying the owner of the SP (even in a case
> like mine where all objects are owned by dbo)
>
> "c_shah" <shah.chirag@.netzero.net> wrote in message
> news:1142951587.510508.143150@.g10g2000cwb.googlegroups.com...
> > Well, what about if some user 'user1' have created a procedure which is
> > owned by 'user1'
> > If you do "Exec UserProc" you will get an error, you need to use "Exec
> > user1.UserProc" generally it is advisable to qualify owner name before
> > database objects.
> >
> > Things are changed in SQL 2005 where you have to qualify schema name
> > instead of user name for database objects
> >
> > chirag shah
> >
>
>

Qualifing table names with dbo

if dbo exists then the sql server resolves the object immeidately
otherwise it needs to search the databases one by one in the server.
Basically its a performance issue related.
HTH
Rajesh Peddireddy.
"Jim Abel" wrote:

> I see different examples of query statements some with the table names
> preceeded by dbo.tablename and others with only the table name. Does the
dbo
> do anything at all, especially if the user that request the query to execu
te
> is not the dbo simply a user with read only permissions?
>"Rajesh" <Rajesh@.discussions.microsoft.com> wrote in message
news:3931CB08-2AE8-4D0C-AC07-E5037BB45D92@.microsoft.com...
> if dbo exists then the sql server resolves the object immeidately
> otherwise it needs to search the databases one by one in the server.
> Basically its a performance issue related.
No it's not. SQL Server will never have to resolve the object name outside
of the database, and when it does this happens on at query comiliation time.
Once the query is compiled it should be reused, so this is not a performance
issue.
Prefixing with DBO is unnecesary and should be avoided in the case where all
the objects in a database reside in the DBO schema. In addition prefixing
is necessary to create schema-bound views and functions.
David|||David Browne (davidbaxterbrowne no potted meat@.hotmail.com) writes:
> Prefixing with DBO is unnecesary and should be avoided in the case where
> all the objects in a database reside in the DBO schema. In addition
> prefixing is necessary to create schema-bound views and functions.
I have had some discussions with the SQL Server team on that one...
In SQL 2000, if a plain user issues a SELECT statement or calls a
stored procedure with qualifying the name with dbo, there is a cost.
Say that user fred issues:
SELECT ... FROM tbl
EXEC some_sp
SQL Server will first have to check whether there is a fred.tbl or a
fred.some_sp. There is a cost for this.
In SQL 2005, this may be different. This is because in SQL 2005 owner
and schema are different. In SQL 2000 fred's default schema is "fred"
by necessity. In SQL 2005, fred may have dbo as his default schema, and
in this case, it should not matter whether you say tbl or dbo.tbl.
However, this depends on how the user fred was created. If the database
was carried over from SQL 2000, or ir the user was created with
sp_adduser out of habit, it will be as on SQL 2000.
It's a different issue inside of a stored procedure, and this is where I
don't agree with some of the SQL Server folks. In a stored procedure
owned by dbo, "SELECT ... FROM tbl" is unambiguous, and adding "dbo."
is just noise for the human reader. However, the SQL Server team
claims there still is a cost in this case. I tempted to say that in
such case this is a bug, but I have not looked at the actual code.
In any case, Microsoft's recommendation is that you should always
specify dbo. also inside stored procedures.
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

Qualifing table names with dbo

I see different examples of query statements some with the table names
preceeded by dbo.tablename and others with only the table name. Does the db
o
do anything at all, especially if the user that request the query to execute
is not the dbo simply a user with read only permissions?Jim Abel (JimAbel@.discussions.microsoft.com) writes:
> I see different examples of query statements some with the table names
> preceeded by dbo.tablename and others with only the table name. Does
> the dbo do anything at all, especially if the user that request the
> query to execute is not the dbo simply a user with read only
> permissions?
Objects in a database can be referred to with three-part names on the
form catalog.schema.object.
In SQL Server "catalog" is the same as "database". Up to SQL 2000 "schema"
was the same as owner, but in SQL 2005 owner and schema have been separated.
A schema can be thought of as a namespace, so that different groups that
work in the same database can use different namespaces, so they don't
get name clashes.
In SQL 2000, each user has a default schema which is equal to the user
name - obvious since schema and owner is the same. If the user "fred" says
"SELECT * FROM tbl", SQL Server first looks up his default schema, so
if there is a table fred.tbl, this is what he will get. If there is not,
SQL Server moves to the default schema for the database, which is dbo.
By prefixing with dbo, "SELECT * FROM dbl.tbl", fred makes an unmabiguous
reference.
In practice, all objects in a database are owned by dbo in 99% of the
database world-wide, so dbo becomes a bit redudant. But as I discussed in
another post today, there can be a performance benefit, if you
specify the schema.
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|||I think he means that dbo as the default database owner does not need to be
used, but should be for performance reasons.
--
thanks (as always)
some day i''m gona pay this forum back for all the help i''m getting
kes
"Jim Abel" wrote:
> I'm still unclear
> Please elaborate on what you mean by "if dbo exists" do yo mean if there i
s
> a dbo assigned to the database specifically as on of the users? and if one
> exists then how does it help if the query is requested by say the asp.net
> user that only has read access to the tables in the query?
> "Rajesh" wrote:
>|||Would adding dbo to db on a single server really make a difference in
performance?
(2000 or 2005)
thanks
kes
--
thanks (as always)
some day i''m gona pay this forum back for all the help i''m getting
kes
"Rajesh" wrote:
> if dbo exists then the sql server resolves the object immeidately
> otherwise it needs to search the databases one by one in the server.
> Basically its a performance issue related.
> HTH
> Rajesh Peddireddy.
>
> "Jim Abel" wrote:
>|||I am very new to SQL Server but I thought dbo (DataBase Owner) always exists
,
it cannot be deleted.
Quote from MSDN:
========================================
======
The dbo user cannot be deleted and is always present in every database.
========================================
======
However, when to use it...I find very confusing.
T
"Rajesh" wrote:
> if dbo exists then the sql server resolves the object immeidately
> otherwise it needs to search the databases one by one in the server.
> Basically its a performance issue related.
> HTH
> Rajesh Peddireddy.
>
> "Jim Abel" wrote:
>|||I'm still unclear
Please elaborate on what you mean by "if dbo exists" do yo mean if there is
a dbo assigned to the database specifically as on of the users? and if one
exists then how does it help if the query is requested by say the asp.net
user that only has read access to the tables in the query?
"Rajesh" wrote:
> if dbo exists then the sql server resolves the object immeidately
> otherwise it needs to search the databases one by one in the server.
> Basically its a performance issue related.
> HTH
> Rajesh Peddireddy.
>
> "Jim Abel" wrote:
>|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9760AC1EB409EYazorman@.127.0.0.1...
> David Browne (davidbaxterbrowne no potted meat@.hotmail.com) writes:
> I have had some discussions with the SQL Server team on that one...
> In SQL 2000, if a plain user issues a SELECT statement or calls a
> stored procedure with qualifying the name with dbo, there is a cost.
But the order of magnitute of this cost for it to be considered a
"performance issue", or to make dbo prefixing a performance "best practice".
David|||If the sp's are called by multiple users concurrently or many times a second
the lack of owner qualification can have a huge performance impact. There is
no question this is more than just a best practice. It has definite
implications if not done.
Andrew J. Kelly SQL MVP
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23RIY3lcKGHA.1676@.TK2MSFTNGP09.phx.gbl...
> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
> news:Xns9760AC1EB409EYazorman@.127.0.0.1...
> But the order of magnitute of this cost for it to be considered a
> "performance issue", or to make dbo prefixing a performance "best
> practice".
> David
>|||David Browne (davidbaxterbrowne no potted meat@.hotmail.com) writes:
> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
> news:Xns9760AC1EB409EYazorman@.127.0.0.1...
> But the order of magnitute of this cost for it to be considered a
> "performance issue", or to make dbo prefixing a performance "best
> practice".
I should have included a reference to this KB article:
http://support.microsoft.com/kb/q263889/.
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|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23NQ3YDdKGHA.500@.TK2MSFTNGP15.phx.gbl...
> If the sp's are called by multiple users concurrently or many times a
> second the lack of owner qualification can have a huge performance impact.
> There is no question this is more than just a best practice. It has
> definite implications if not done.
>
What scenario are you claiming has a "huge performance impact"? Referencing
objects inside stored procedures? The objects are only resolved at query
parse/compile time, not on every call. IMO, basic best practices wrt shared
SQL eliminate any significant impact with object name resolution.
David

Qualified table names

I have a quick question on how to qualify table names as it relates to
"dbo" vs. user names. Suppose that I am a user named "dwuser1", and
that I need to create a table named "dw_stage_1". Do I use dbo as in
"dbo.dw_stage_1" or do I use "dwuser1.dw_stage_1" for the qualified
table name? Are both OK? If so, what would be the implications of
each?php newbie (newtophp2000@.yahoo.com) writes:
> I have a quick question on how to qualify table names as it relates to
> "dbo" vs. user names. Suppose that I am a user named "dwuser1", and
> that I need to create a table named "dw_stage_1". Do I use dbo as in
> "dbo.dw_stage_1" or do I use "dwuser1.dw_stage_1" for the qualified
> table name? Are both OK? If so, what would be the implications of
> each?

If you are user dwuser1, and you have CREATE TABLE permissions, and
you say:

CREATE TABLE dw_stage_1 (a int NOT NULL)

The full qualification for that table will be dwuser1.dw_stage_1. You
cannot refer to the table as dbo.dw_stage_1. As dwuser1 you can refer
to the table as dw_stage_1 and dwuser1.dw_stage_1. All other users,
including dbo, must refer to ut as dwuser1.dw_stage_1.

If you then log in as sa or any other login that map to dbo and say

CREATE TABLE dw_stage_1 (a int NOT NULL)

again, you have now created to dw_stage_1. As dbo you can refer to the
table as dbo.dw_stage_1 or dw_stage_1 only, and so can all other users
except for dwuser1, who must use dbo.dw_stage_1, since his only table
is ahead in the seatch path.

Best practice recommended my Microsoft is to refer to the table as
dbo.dw_stage_1. This is particularly important for loose SQL statements,
since SQL Server then can skip the search for user.dw_stage_1. They
say that this is also good in stored procedure, but in my opinion,
the dbo. becomes a four-letter line noice in a procedure that is owned
by dbo.

As for when to use objects not owned by dbo - beats me. I say, keep it
simple and only use dbo.

(In SQL 2005 where users and schema are separated, it's another story.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

q; the owner of the table

Hello,
I need to user full name for the table as seen below.
SELECT @.RowCount = COUNT(*)
FROM T1 c INNER JOIN [MyInstance].MyDB.dbo.T2 b ON c.T1_ID=b.T2_ID
T2 may be re-created by different users, how can I get this working for all
users if the creator is not dbo?JIM
You will have to identify an user and then creating dynamic sql if I
understood you properly.
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:295BC91E-3F82-4EB8-BFAF-71DCC335FAC5@.microsoft.com...
> Hello,
> I need to user full name for the table as seen below.
> SELECT @.RowCount = COUNT(*)
> FROM T1 c INNER JOIN [MyInstance].MyDB.dbo.T2 b ON c.T1_ID=b.T2_ID
> T2 may be re-created by different users, how can I get this working for
> all
> users if the creator is not dbo?
>|||Thanks for the reply, I am trying to select the data whoever creates it, if
the owner is not dbo, my query is not working, owner may be many users since
people drop it an recreate it from time to time.
"Uri Dimant" wrote:

> JIM
> You will have to identify an user and then creating dynamic sql if I
> understood you properly.
>
>
>
> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:295BC91E-3F82-4EB8-BFAF-71DCC335FAC5@.microsoft.com...
>
>|||JIM
So specify
CREATE TABLE user.TableName (col INT)
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:8EA06ACD-F357-4434-92B2-25B5A47006B3@.microsoft.com...[vbcol=seagreen]
> Thanks for the reply, I am trying to select the data whoever creates it,
> if
> the owner is not dbo, my query is not working, owner may be many users
> since
> people drop it an recreate it from time to time.
> "Uri Dimant" wrote:
>|||JIM.H. wrote:
> Thanks for the reply, I am trying to select the data whoever creates it, i
f
> the owner is not dbo, my query is not working, owner may be many users sin
ce
> people drop it an recreate it from time to time.
> "Uri Dimant" wrote:
Having a table that is repeatedly dropped/created by your users seems
like a bad idea. Why not create a permanent table, owned by dbo, and
have your users TRUNCATE it instead of dropping/creating it? Or better
yet, explain why the need to drop/create exists, and perhaps we can
offer a better idea?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:8EA06ACD-F357-4434-92B2-25B5A47006B3@.microsoft.com...[vbcol=seagreen]
> Thanks for the reply, I am trying to select the data whoever creates it,
> if
> the owner is not dbo, my query is not working, owner may be many users
> since
> people drop it an recreate it from time to time.
> "Uri Dimant" wrote:
>|||Thanks for the reply. I agree however that is the part of the code I do not
have control over, I just need to figure out a way select it based on the
different owner.
"Tracy McKibben" wrote:

> JIM.H. wrote:
> Having a table that is repeatedly dropped/created by your users seems
> like a bad idea. Why not create a permanent table, owned by dbo, and
> have your users TRUNCATE it instead of dropping/creating it? Or better
> yet, explain why the need to drop/create exists, and perhaps we can
> offer a better idea?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>

q; the owner of the table

Hello,
I need to user full name for the table as seen below.
SELECT @.RowCount = COUNT(*)
FROM T1 c INNER JOIN [MyInstance].MyDB.dbo.T2 b ON c.T1_ID=b.T2_ID
T2 may be re-created by different users, how can I get this working for all
users if the creator is not dbo?JIM
You will have to identify an user and then creating dynamic sql if I
understood you properly.
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:295BC91E-3F82-4EB8-BFAF-71DCC335FAC5@.microsoft.com...
> Hello,
> I need to user full name for the table as seen below.
> SELECT @.RowCount = COUNT(*)
> FROM T1 c INNER JOIN [MyInstance].MyDB.dbo.T2 b ON c.T1_ID=b.T2_ID
> T2 may be re-created by different users, how can I get this working for
> all
> users if the creator is not dbo?
>|||Thanks for the reply, I am trying to select the data whoever creates it, if
the owner is not dbo, my query is not working, owner may be many users since
people drop it an recreate it from time to time.
"Uri Dimant" wrote:
> JIM
> You will have to identify an user and then creating dynamic sql if I
> understood you properly.
>
>
>
> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:295BC91E-3F82-4EB8-BFAF-71DCC335FAC5@.microsoft.com...
> > Hello,
> > I need to user full name for the table as seen below.
> >
> > SELECT @.RowCount = COUNT(*)
> > FROM T1 c INNER JOIN [MyInstance].MyDB.dbo.T2 b ON c.T1_ID=b.T2_ID
> >
> > T2 may be re-created by different users, how can I get this working for
> > all
> > users if the creator is not dbo?
> >
>
>|||JIM
So specify
CREATE TABLE user.TableName (col INT)
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:8EA06ACD-F357-4434-92B2-25B5A47006B3@.microsoft.com...
> Thanks for the reply, I am trying to select the data whoever creates it,
> if
> the owner is not dbo, my query is not working, owner may be many users
> since
> people drop it an recreate it from time to time.
> "Uri Dimant" wrote:
>> JIM
>> You will have to identify an user and then creating dynamic sql if I
>> understood you properly.
>>
>>
>>
>> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
>> news:295BC91E-3F82-4EB8-BFAF-71DCC335FAC5@.microsoft.com...
>> > Hello,
>> > I need to user full name for the table as seen below.
>> >
>> > SELECT @.RowCount = COUNT(*)
>> > FROM T1 c INNER JOIN [MyInstance].MyDB.dbo.T2 b ON c.T1_ID=b.T2_ID
>> >
>> > T2 may be re-created by different users, how can I get this working for
>> > all
>> > users if the creator is not dbo?
>> >
>>|||JIM.H. wrote:
> Thanks for the reply, I am trying to select the data whoever creates it, if
> the owner is not dbo, my query is not working, owner may be many users since
> people drop it an recreate it from time to time.
> "Uri Dimant" wrote:
Having a table that is repeatedly dropped/created by your users seems
like a bad idea. Why not create a permanent table, owned by dbo, and
have your users TRUNCATE it instead of dropping/creating it? Or better
yet, explain why the need to drop/create exists, and perhaps we can
offer a better idea?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:8EA06ACD-F357-4434-92B2-25B5A47006B3@.microsoft.com...
> Thanks for the reply, I am trying to select the data whoever creates it,
> if
> the owner is not dbo, my query is not working, owner may be many users
> since
> people drop it an recreate it from time to time.
> "Uri Dimant" wrote:
>> JIM
>> You will have to identify an user and then creating dynamic sql if I
>> understood you properly.
>>
>>
>>
>> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
>> news:295BC91E-3F82-4EB8-BFAF-71DCC335FAC5@.microsoft.com...
>> > Hello,
>> > I need to user full name for the table as seen below.
>> >
>> > SELECT @.RowCount = COUNT(*)
>> > FROM T1 c INNER JOIN [MyInstance].MyDB.dbo.T2 b ON c.T1_ID=b.T2_ID
>> >
>> > T2 may be re-created by different users, how can I get this working for
>> > all
>> > users if the creator is not dbo?
>> >
>>|||Thanks for the reply. I agree however that is the part of the code I do not
have control over, I just need to figure out a way select it based on the
different owner.
"Tracy McKibben" wrote:
> JIM.H. wrote:
> > Thanks for the reply, I am trying to select the data whoever creates it, if
> > the owner is not dbo, my query is not working, owner may be many users since
> > people drop it an recreate it from time to time.
> >
> > "Uri Dimant" wrote:
> Having a table that is repeatedly dropped/created by your users seems
> like a bad idea. Why not create a permanent table, owned by dbo, and
> have your users TRUNCATE it instead of dropping/creating it? Or better
> yet, explain why the need to drop/create exists, and perhaps we can
> offer a better idea?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>

Q: SQL , what is wrong?

Hello,
SELECT dbo.tSp.pID, dbo.tLo.oS
FROM dbo.tSp INNER JOIN
dbo.tLo ON dbo.tSp.SpID = dbo.tLo.SpID
WHERE (dbo.tLo.oS = N'[MyText]')
This works without Where and I see MyText available in oS column. Why does
it not bring anything when Where is there?
Thanks,Ok. I removed [, works fine.
"JIM.H." wrote:
> Hello,
> SELECT dbo.tSp.pID, dbo.tLo.oS
> FROM dbo.tSp INNER JOIN
> dbo.tLo ON dbo.tSp.SpID = dbo.tLo.SpID
> WHERE (dbo.tLo.oS = N'[MyText]')
> This works without Where and I see MyText available in oS column. Why does
> it not bring anything when Where is there?
> Thanks,
>|||Just as a follow up the brackets are to use a obejectname in SQL Server,
useful for those developers who use spaces or special reserved words for
their objects (Yeah I know, there are some out there who always do this
:-) )
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:34E4754D-ECA2-42B7-B36A-BA2F42A841FC@.microsoft.com...
> Ok. I removed [, works fine.
> "JIM.H." wrote:
>> Hello,
>> SELECT dbo.tSp.pID, dbo.tLo.oS
>> FROM dbo.tSp INNER JOIN
>> dbo.tLo ON dbo.tSp.SpID = dbo.tLo.SpID
>> WHERE (dbo.tLo.oS = N'[MyText]')
>> This works without Where and I see MyText available in oS column. Why
>> does
>> it not bring anything when Where is there?
>> Thanks,|||SELECT dbo.tSp.pID, dbo.tLo.oS
FROM dbo.tSp
INNER JOIN
dbo.tLo
ON dbo.tSp.SpID = dbo.tLo.SpID
AND dbo.tLo.oS = N'MyText'
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:C1220918-D358-4387-9268-94679031B403@.microsoft.com...
> Hello,
> SELECT dbo.tSp.pID, dbo.tLo.oS
> FROM dbo.tSp INNER JOIN
> dbo.tLo ON dbo.tSp.SpID = dbo.tLo.SpID
> WHERE (dbo.tLo.oS = N'[MyText]')
> This works without Where and I see MyText available in oS column. Why does
> it not bring anything when Where is there?
> Thanks,
>

Q: SQL , what is wrong?

Hello,
SELECT dbo.tSp.pID, dbo.tLo.oS
FROM dbo.tSp INNER JOIN
dbo.tLo ON dbo.tSp.SpID = dbo.tLo.SpID
WHERE (dbo.tLo.oS = N'[MyText]')
This works without Where and I see MyText available in oS column. Why does
it not bring anything when Where is there?
Thanks,Ok. I removed [, works fine.
"JIM.H." wrote:

> Hello,
> SELECT dbo.tSp.pID, dbo.tLo.oS
> FROM dbo.tSp INNER JOIN
> dbo.tLo ON dbo.tSp.SpID = dbo.tLo.SpID
> WHERE (dbo.tLo.oS = N'[MyText]')
> This works without Where and I see MyText available in oS column. Why does
> it not bring anything when Where is there?
> Thanks,
>|||Just as a follow up the brackets are to use a obejectname in SQL Server,
useful for those developers who use spaces or special reserved words for
their objects (Yeah I know, there are some out there who always do this
:-) )
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:34E4754D-ECA2-42B7-B36A-BA2F42A841FC@.microsoft.com...[vbcol=seagreen]
> Ok. I removed [, works fine.
> "JIM.H." wrote:
>

Q: SQL , what is wrong?

Hello,
SELECT dbo.tSp.pID, dbo.tLo.oS
FROM dbo.tSp INNER JOIN
dbo.tLo ON dbo.tSp.SpID = dbo.tLo.SpID
WHERE (dbo.tLo.oS = N'[MyText]')
This works without Where and I see MyText available in oS column. Why does
it not bring anything when Where is there?
Thanks,
Ok. I removed [, works fine.
"JIM.H." wrote:

> Hello,
> SELECT dbo.tSp.pID, dbo.tLo.oS
> FROM dbo.tSp INNER JOIN
> dbo.tLo ON dbo.tSp.SpID = dbo.tLo.SpID
> WHERE (dbo.tLo.oS = N'[MyText]')
> This works without Where and I see MyText available in oS column. Why does
> it not bring anything when Where is there?
> Thanks,
>
|||Just as a follow up the brackets are to use a obejectname in SQL Server,
useful for those developers who use spaces or special reserved words for
their objects (Yeah I know, there are some out there who always do this
:-) )
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:34E4754D-ECA2-42B7-B36A-BA2F42A841FC@.microsoft.com...[vbcol=seagreen]
> Ok. I removed [, works fine.
> "JIM.H." wrote: