db time conversion: statements

Showing posts with label statements. Show all posts

Monday, March 12, 2012

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

Saturday, February 25, 2012

q; Linked server from SQL2005 to SQL2000

Hi
I use the following SQL statements to create a link server. RemoteServerName
is an SQL2000 and I am executing this in another machine which is SQL2005.
Though the link server is created successfully, I am not able to se the
tables under it so could not query anything.
--
USE [master]
EXEC master.dbo.sp_addlinkedserver @.server = N'RemoteServerName\InstanceName', @.srvproduct=N'SQL Server'
EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
@.optname=N'collation compatible', @.optvalue=N'false'
EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
@.optname=N'data access', @.optvalue=N'true'
EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
@.optname=N'rpc',@.optvalue=N'false'
EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
@.optname=N'rpc out', @.optvalue=N'false'
EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
@.optname=N'connect timeout', @.optvalue=N'0'
EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
@.optname=N'collation name', @.optvalue=null
EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
@.optname=N'query timeout', @.optvalue=N'0'
EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
@.optname=N'use remote collation', @.optvalue=N'true'
EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname = N'RemoteServerName\InstanceName', @.locallogin = NULL , @.useself = N'False',
@.rmtuser = N'remoteUser', @.rmtpassword = N'remotePassword'Jim
First of all , have you enabled remote connections on SQL Server 2005
server?
I did just testing on my machine and it works fine.
USE [master]
GO
EXEC master.dbo.sp_addlinkedserver @.server = N'ServerName',
@.srvproduct=N'SQL Server'
GO
EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'collation
compatible', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'data
access', @.optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'rpc',
@.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'rpc out',
@.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'connect
timeout', @.optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'collation
name', @.optvalue=null
GO
EXEC master.dbo.sp_serveroption @.server=N'NT_SQL_4', @.optname=N'query
timeout', @.optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'use remote
collation', @.optvalue=N'true'
GO
USE [master]
GO
EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname = N'ServerName',
@.locallogin = NULL , @.useself = N'False', @.rmtuser = N'blb', @.rmtpassword =N'blblbl'
GO
--usage
select * from ServerName.dtabasename.dbo.tablename
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:81C56D0D-3727-46C5-889F-6AB39E88CCFA@.microsoft.com...
> Hi
> I use the following SQL statements to create a link server.
> RemoteServerName
> is an SQL2000 and I am executing this in another machine which is SQL2005.
> Though the link server is created successfully, I am not able to se the
> tables under it so could not query anything.
> --
> USE [master]
> EXEC master.dbo.sp_addlinkedserver @.server => N'RemoteServerName\InstanceName', @.srvproduct=N'SQL Server'
> EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> @.optname=N'collation compatible', @.optvalue=N'false'
> EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> @.optname=N'data access', @.optvalue=N'true'
> EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> @.optname=N'rpc',@.optvalue=N'false'
> EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> @.optname=N'rpc out', @.optvalue=N'false'
> EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> @.optname=N'connect timeout', @.optvalue=N'0'
> EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> @.optname=N'collation name', @.optvalue=null
> EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> @.optname=N'query timeout', @.optvalue=N'0'
> EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> @.optname=N'use remote collation', @.optvalue=N'true'
> EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname => N'RemoteServerName\InstanceName', @.locallogin = NULL , @.useself => N'False',
> @.rmtuser = N'remoteUser', @.rmtpassword = N'remotePassword'
>|||Hi Uri,
How do I enable remote connection in SQL2000?
"Uri Dimant" wrote:
> Jim
> First of all , have you enabled remote connections on SQL Server 2005
> server?
> I did just testing on my machine and it works fine.
> USE [master]
> GO
> EXEC master.dbo.sp_addlinkedserver @.server = N'ServerName',
> @.srvproduct=N'SQL Server'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'collation
> compatible', @.optvalue=N'false'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'data
> access', @.optvalue=N'true'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'rpc',
> @.optvalue=N'false'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'rpc out',
> @.optvalue=N'false'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'connect
> timeout', @.optvalue=N'0'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'collation
> name', @.optvalue=null
> GO
> EXEC master.dbo.sp_serveroption @.server=N'NT_SQL_4', @.optname=N'query
> timeout', @.optvalue=N'0'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'use remote
> collation', @.optvalue=N'true'
> GO
> USE [master]
> GO
> EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname = N'ServerName',
> @.locallogin = NULL , @.useself = N'False', @.rmtuser = N'blb', @.rmtpassword => N'blblbl'
> GO
>
> --usage
> select * from ServerName.dtabasename.dbo.tablename
>
>
> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:81C56D0D-3727-46C5-889F-6AB39E88CCFA@.microsoft.com...
> >
> > Hi
> > I use the following SQL statements to create a link server.
> > RemoteServerName
> > is an SQL2000 and I am executing this in another machine which is SQL2005.
> >
> > Though the link server is created successfully, I am not able to se the
> > tables under it so could not query anything.
> >
> > --
> >
> > USE [master]
> > EXEC master.dbo.sp_addlinkedserver @.server => > N'RemoteServerName\InstanceName', @.srvproduct=N'SQL Server'
> > EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> > @.optname=N'collation compatible', @.optvalue=N'false'
> > EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> > @.optname=N'data access', @.optvalue=N'true'
> > EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> > @.optname=N'rpc',@.optvalue=N'false'
> > EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> > @.optname=N'rpc out', @.optvalue=N'false'
> > EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> > @.optname=N'connect timeout', @.optvalue=N'0'
> > EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> > @.optname=N'collation name', @.optvalue=null
> > EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> > @.optname=N'query timeout', @.optvalue=N'0'
> > EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> > @.optname=N'use remote collation', @.optvalue=N'true'
> > EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname => > N'RemoteServerName\InstanceName', @.locallogin = NULL , @.useself => > N'False',
> > @.rmtuser = N'remoteUser', @.rmtpassword = N'remotePassword'
> >
>
>|||Jim
Co to Surface Area Configuration for Services and Connections , then
navigate to Remote Connection and check Local and Remote Connections
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:CEE13CAB-BD2D-4307-98B4-E5F223D6E30F@.microsoft.com...
> Hi Uri,
> How do I enable remote connection in SQL2000?
> "Uri Dimant" wrote:
>> Jim
>> First of all , have you enabled remote connections on SQL Server 2005
>> server?
>> I did just testing on my machine and it works fine.
>> USE [master]
>> GO
>> EXEC master.dbo.sp_addlinkedserver @.server = N'ServerName',
>> @.srvproduct=N'SQL Server'
>> GO
>> EXEC master.dbo.sp_serveroption @.server=N'ServerName',
>> @.optname=N'collation
>> compatible', @.optvalue=N'false'
>> GO
>> EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'data
>> access', @.optvalue=N'true'
>> GO
>> EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'rpc',
>> @.optvalue=N'false'
>> GO
>> EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'rpc
>> out',
>> @.optvalue=N'false'
>> GO
>> EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'connect
>> timeout', @.optvalue=N'0'
>> GO
>> EXEC master.dbo.sp_serveroption @.server=N'ServerName',
>> @.optname=N'collation
>> name', @.optvalue=null
>> GO
>> EXEC master.dbo.sp_serveroption @.server=N'NT_SQL_4', @.optname=N'query
>> timeout', @.optvalue=N'0'
>> GO
>> EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'use
>> remote
>> collation', @.optvalue=N'true'
>> GO
>> USE [master]
>> GO
>> EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname = N'ServerName',
>> @.locallogin = NULL , @.useself = N'False', @.rmtuser = N'blb', @.rmtpassword
>> =>> N'blblbl'
>> GO
>>
>> --usage
>> select * from ServerName.dtabasename.dbo.tablename
>>
>>
>> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
>> news:81C56D0D-3727-46C5-889F-6AB39E88CCFA@.microsoft.com...
>> >
>> > Hi
>> > I use the following SQL statements to create a link server.
>> > RemoteServerName
>> > is an SQL2000 and I am executing this in another machine which is
>> > SQL2005.
>> >
>> > Though the link server is created successfully, I am not able to se the
>> > tables under it so could not query anything.
>> >
>> > --
>> >
>> > USE [master]
>> > EXEC master.dbo.sp_addlinkedserver @.server =>> > N'RemoteServerName\InstanceName', @.srvproduct=N'SQL Server'
>> > EXEC master.dbo.sp_serveroption
>> > @.server=N'RemoteServerName\InstanceName',
>> > @.optname=N'collation compatible', @.optvalue=N'false'
>> > EXEC master.dbo.sp_serveroption
>> > @.server=N'RemoteServerName\InstanceName',
>> > @.optname=N'data access', @.optvalue=N'true'
>> > EXEC master.dbo.sp_serveroption
>> > @.server=N'RemoteServerName\InstanceName',
>> > @.optname=N'rpc',@.optvalue=N'false'
>> > EXEC master.dbo.sp_serveroption
>> > @.server=N'RemoteServerName\InstanceName',
>> > @.optname=N'rpc out', @.optvalue=N'false'
>> > EXEC master.dbo.sp_serveroption
>> > @.server=N'RemoteServerName\InstanceName',
>> > @.optname=N'connect timeout', @.optvalue=N'0'
>> > EXEC master.dbo.sp_serveroption
>> > @.server=N'RemoteServerName\InstanceName',
>> > @.optname=N'collation name', @.optvalue=null
>> > EXEC master.dbo.sp_serveroption
>> > @.server=N'RemoteServerName\InstanceName',
>> > @.optname=N'query timeout', @.optvalue=N'0'
>> > EXEC master.dbo.sp_serveroption
>> > @.server=N'RemoteServerName\InstanceName',
>> > @.optname=N'use remote collation', @.optvalue=N'true'
>> > EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname =>> > N'RemoteServerName\InstanceName', @.locallogin = NULL , @.useself =>> > N'False',
>> > @.rmtuser = N'remoteUser', @.rmtpassword = N'remotePassword'
>> >
>>

q; Linked server from SQL2005 to SQL2000

Monday, February 20, 2012

Q: report statements

Hello,
We have a dental practice that uses SQL server as a database and a practice
management system. I need develop a report by using Reporting Services to be
able to print statements per patient. Is there any reports already developed
for healthcare companies so that I can check and see what exactly I need to
include and how to do that?
Thanks,doesn't have specific health care reports, but maybe you might get some ideas.
http://www.microsoft.com/sql/reporting/downloads/default.asp
"JIM.H." wrote:
> Hello,
> We have a dental practice that uses SQL server as a database and a practice
> management system. I need develop a report by using Reporting Services to be
> able to print statements per patient. Is there any reports already developed
> for healthcare companies so that I can check and see what exactly I need to
> include and how to do that?
> Thanks,
>

Monday, March 12, 2012

Qualifing table names with dbo

Saturday, February 25, 2012

q; Linked server from SQL2005 to SQL2000

q; Linked server from SQL2005 to SQL2000

q; Linked server from SQL2005 to SQL2000

Monday, February 20, 2012

Q: report statements

db time conversion

Blog Archive

About Me