I am new to sql server, mostly work with an other product build by a company
in california. We can call it "company O"
We had a application written for our company. The version of SQL Server is
2000. They created all the objects owned by dbo. When I asked why, I was
told this is what Microsoft recommends. My question is why should the
objects be owned by dbo and not use the a model like
schema/username.objectname
Can some one explain why use the dbo as the owner of the objects in a
database vrs have a schema and then grant permissions on the objects in the
schema?In SQL Server 2000, schemas don't work that way. Having everything owned by
dbo makes it much easier to manage. SQL Server 2005 properly implements
schemas such that what you're talking about does make sense.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"jbwest77" <jbwest77@.discussions.microsoft.com> wrote in message
news:1C71EE4D-A6F0-42BE-A48E-693830152596@.microsoft.com...
>I am new to sql server, mostly work with an other product build by a
>company
> in california. We can call it "company O"
> We had a application written for our company. The version of SQL Server
> is
> 2000. They created all the objects owned by dbo. When I asked why, I was
> told this is what Microsoft recommends. My question is why should the
> objects be owned by dbo and not use the a model like
> schema/username.objectname
> Can some one explain why use the dbo as the owner of the objects in a
> database vrs have a schema and then grant permissions on the objects in
> the
> schema?|||On Mon, 21 Nov 2005 15:48:01 -0800, "jbwest77"
<jbwest77@.discussions.microsoft.com> wrote:
>I am new to sql server, mostly work with an other product build by a company
>in california. We can call it "company O"
>We had a application written for our company. The version of SQL Server is
>2000. They created all the objects owned by dbo. When I asked why, I was
>told this is what Microsoft recommends. My question is why should the
>objects be owned by dbo and not use the a model like
>schema/username.objectname
>Can some one explain why use the dbo as the owner of the objects in a
>database vrs have a schema and then grant permissions on the objects in the
>schema?
It's just always been that way, and various tools and defaults are
very awkward if you try to use ownership other than dbo.
If things are finally better in Yukon, then we'll just have to break
old habits!
So, I'd say that at least on this issue, so far I wouldn't hold it
against your developers.
J.|||In SQL Server 2000 you can implement the schema/username.objectname that you
mentioned, more exactly the username. You can create a user, like finance,
and give ownership of the objects to that user. We have several applications
like that and we do not have any problem.
If you want to implement schemas Oracle-style then we have SQL Server 2005.
Ben Nevarez
"jbwest77" <jbwest77@.discussions.microsoft.com> wrote in message
news:1C71EE4D-A6F0-42BE-A48E-693830152596@.microsoft.com...
>I am new to sql server, mostly work with an other product build by a
>company
> in california. We can call it "company O"
> We had a application written for our company. The version of SQL Server
> is
> 2000. They created all the objects owned by dbo. When I asked why, I was
> told this is what Microsoft recommends. My question is why should the
> objects be owned by dbo and not use the a model like
> schema/username.objectname
> Can some one explain why use the dbo as the owner of the objects in a
> database vrs have a schema and then grant permissions on the objects in
> the
> schema?|||Thanks for the input.
"Ben Nevarez" wrote:
> In SQL Server 2000 you can implement the schema/username.objectname that you
> mentioned, more exactly the username. You can create a user, like finance,
> and give ownership of the objects to that user. We have several applications
> like that and we do not have any problem.
> If you want to implement schemas Oracle-style then we have SQL Server 2005.
> Ben Nevarez
>
> "jbwest77" <jbwest77@.discussions.microsoft.com> wrote in message
> news:1C71EE4D-A6F0-42BE-A48E-693830152596@.microsoft.com...
> >I am new to sql server, mostly work with an other product build by a
> >company
> > in california. We can call it "company O"
> >
> > We had a application written for our company. The version of SQL Server
> > is
> > 2000. They created all the objects owned by dbo. When I asked why, I was
> > told this is what Microsoft recommends. My question is why should the
> > objects be owned by dbo and not use the a model like
> > schema/username.objectname
> > Can some one explain why use the dbo as the owner of the objects in a
> > database vrs have a schema and then grant permissions on the objects in
> > the
> > schema?
>
>
Friday, March 30, 2012
New to SQL server
Hi I am new to SQL Server and have a few general
questions.
1. Can anyone suggest a good book to learn how ti use SQL
Server... I am proficient with relational databases and
SQL (Not transact)
2. I would like to create a database using SQL server as
the backend and Access as the front end. If I house all
of my Queries in the Access front end and simply join the
tables to my SQL Server tables will I still be able to
take adavantage of the improvements in processing speed
and size of the SQL Server database or will I need to
change all of my Access queries to SQL views to be able to
do this?
Thanks for your help,
ChadTo get a good understanding of all aspects of SQL Server, try "Teach
yourself Microsoft SQL Server 2000 in 21 days".
For a deeper understanding of SQL Server architecture, go for "Inside
Microsoft SQL Server 2000":
http://vyaskn.tripod.com/inside_microsoft_sql_server_2000.htm
Links to these books and others are available at:
http://vyaskn.tripod.com/sqlbooks.htm
Regarding your other question, if your data is stored in SQL Server, and
accessed via queries from Access, you should be able to take advantage of
SQL Servers speed and robustness.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
What hardware is your SQL Server running on?
http://vyaskn.tripod.com/poll.htm
"Chad" <ChadLucy@.msn.com> wrote in message
news:021501c38dca$91af4980$a401280a@.phx.gbl...
Hi I am new to SQL Server and have a few general
questions.
1. Can anyone suggest a good book to learn how ti use SQL
Server... I am proficient with relational databases and
SQL (Not transact)
2. I would like to create a database using SQL server as
the backend and Access as the front end. If I house all
of my Queries in the Access front end and simply join the
tables to my SQL Server tables will I still be able to
take adavantage of the improvements in processing speed
and size of the SQL Server database or will I need to
change all of my Access queries to SQL views to be able to
do this?
Thanks for your help,
Chad|||To get the best throughput from MSAccess you need to set up the MSAccess
project correctly as a SQL Server project. If you use the older project
style and "attached" tables, JET is used behind the scenes and performance
will be much worse. (I did MSAccess work a long time ago, and when they
introduced SQL projects it was a very new concept. Perhaps my advice today
is already common knowedge.) Anyway, the new style projects pass SQL
commands directly to SQL Server, and you can access database views as well
as base tables. I believe there is even a way to "wire up" the MSAccess
forms so that insert/update/delete commands can call stored procedures on
the server; a huge feature.
James|||Hello,
I have found two books to be extremely helpful -
particularly with Transact Sql:
-SQL Server 2k Stored Procedure Programming (Sunderic &
Woodhead)
-Adv T-SQL for SQL Server 2k (Ben-Gan and Moreau)
>--Original Message--
>Hi I am new to SQL Server and have a few general
>questions.
>1. Can anyone suggest a good book to learn how ti use
SQL
>Server... I am proficient with relational databases and
>SQL (Not transact)
>2. I would like to create a database using SQL server as
>the backend and Access as the front end. If I house all
>of my Queries in the Access front end and simply join the
>tables to my SQL Server tables will I still be able to
>take adavantage of the improvements in processing speed
>and size of the SQL Server database or will I need to
>change all of my Access queries to SQL views to be able
to
>do this?
>Thanks for your help,
>Chad
>.
>sql
questions.
1. Can anyone suggest a good book to learn how ti use SQL
Server... I am proficient with relational databases and
SQL (Not transact)
2. I would like to create a database using SQL server as
the backend and Access as the front end. If I house all
of my Queries in the Access front end and simply join the
tables to my SQL Server tables will I still be able to
take adavantage of the improvements in processing speed
and size of the SQL Server database or will I need to
change all of my Access queries to SQL views to be able to
do this?
Thanks for your help,
ChadTo get a good understanding of all aspects of SQL Server, try "Teach
yourself Microsoft SQL Server 2000 in 21 days".
For a deeper understanding of SQL Server architecture, go for "Inside
Microsoft SQL Server 2000":
http://vyaskn.tripod.com/inside_microsoft_sql_server_2000.htm
Links to these books and others are available at:
http://vyaskn.tripod.com/sqlbooks.htm
Regarding your other question, if your data is stored in SQL Server, and
accessed via queries from Access, you should be able to take advantage of
SQL Servers speed and robustness.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
What hardware is your SQL Server running on?
http://vyaskn.tripod.com/poll.htm
"Chad" <ChadLucy@.msn.com> wrote in message
news:021501c38dca$91af4980$a401280a@.phx.gbl...
Hi I am new to SQL Server and have a few general
questions.
1. Can anyone suggest a good book to learn how ti use SQL
Server... I am proficient with relational databases and
SQL (Not transact)
2. I would like to create a database using SQL server as
the backend and Access as the front end. If I house all
of my Queries in the Access front end and simply join the
tables to my SQL Server tables will I still be able to
take adavantage of the improvements in processing speed
and size of the SQL Server database or will I need to
change all of my Access queries to SQL views to be able to
do this?
Thanks for your help,
Chad|||To get the best throughput from MSAccess you need to set up the MSAccess
project correctly as a SQL Server project. If you use the older project
style and "attached" tables, JET is used behind the scenes and performance
will be much worse. (I did MSAccess work a long time ago, and when they
introduced SQL projects it was a very new concept. Perhaps my advice today
is already common knowedge.) Anyway, the new style projects pass SQL
commands directly to SQL Server, and you can access database views as well
as base tables. I believe there is even a way to "wire up" the MSAccess
forms so that insert/update/delete commands can call stored procedures on
the server; a huge feature.
James|||Hello,
I have found two books to be extremely helpful -
particularly with Transact Sql:
-SQL Server 2k Stored Procedure Programming (Sunderic &
Woodhead)
-Adv T-SQL for SQL Server 2k (Ben-Gan and Moreau)
>--Original Message--
>Hi I am new to SQL Server and have a few general
>questions.
>1. Can anyone suggest a good book to learn how ti use
SQL
>Server... I am proficient with relational databases and
>SQL (Not transact)
>2. I would like to create a database using SQL server as
>the backend and Access as the front end. If I house all
>of my Queries in the Access front end and simply join the
>tables to my SQL Server tables will I still be able to
>take adavantage of the improvements in processing speed
>and size of the SQL Server database or will I need to
>change all of my Access queries to SQL views to be able
to
>do this?
>Thanks for your help,
>Chad
>.
>sql
new to sql please help
i use access 2000 as my front end. i had a form named main with a couple of unbound fields on it . i used to put in last name and first name and then in a query i would put "like [forms]![main]![text0] & "*"
and also "like [forms]![main]![text2] & "*"
the lookup patient button would open my patient form based on the query results of what i typed in on the main form . ex: i type in riley it would give me my patient form with all the riley's in it.
how do i get my access form to open from information i am looking for in a query now that my whole database is back on a sql server.
by the way i upsized to a adp file no longer using mdb. please if possible explain in detail how to do this as i am having trouble with the syntax of sql. i understand i cant use "*" as a wildcard now but % instead.
thanks in advance.Hoe are you connecting to SQL Sever?
You shouldn't have to change your code...did you create linked tables?|||im assuming they not linked tables since it is now a access project i dont see the little globe when i used to use link tables.
i think everything is useing passthorugh queries now all my tables are on the sql server. just the forms and macros are on the frontend now.
my code i had to change was something like
dim db as dao.database
dim rs as dao.recordset
i had to change that to
dim conn as adodb.connection
dim rs as new adodb.recordset
and quite a few other things
if i left them linked the queries should have stayed working but im trying to get away from passing things through the jet engine and using the full power of sql .|||this was my old code to make a chartnumber based on the first and last name fields. this is the one that didnt work .
Public Function chartlookup()
Dim db As DAO.Database <--believe to be the problem
Dim rs As DAO.Recordset <-- belive to be the problem
Dim SQL As String
Dim NewNum As Integer
Dim NeWChartNum As String
SQL = "Select max(Cint(Right([chartnumber],6))) As RecNum From tblpatientinfo WHERE UCase(Left([chartnumber],5)) = '" & UCase(Left([Forms]![fpatient]![lname], 3)) & UCase(Left([Forms]![fpatient]![fname], 2)) & "'"
Set db = CurrentDb() <-- -problem
Set rs = db.OpenRecordset(SQL) <--problem
If IsNull([Forms]![fpatient]![chartnumber]) = False Then GoTo 400
If rs.EOF = False Then
If IsNull(rs!RecNum) = False Then
NewNum = rs!RecNum + 1
Else
NewNum = 1
End If
End If
'If NewNum = 1 Then
[Forms]![fpatient]![chartnumber] = UCase(Left([Forms]![fpatient]![lname], 3)) & UCase(Left([Forms]![fpatient]![fname], 2)) & Format(NewNum, "000000")
'End If|||Well this is a TOTAL guess...
If you're doing passthrus, you'll need SQL Server syntax..
(Why not just call stored procedures if you're making such a radical change?)
anyway...
SQL = "SELECT (MAX(CONVERT(int,(RIGHT(chartnumber))))
FROM tblpatientinfo
WHERE UPPER(LEFT(chartnumber,5)) = " _
& "'" UCase(Left([Forms]![fpatient]![lname], 3)) & UCase(Left([Forms]![fpatient]![fname], 2)) & "'"
my front end skills have gotten rusty...
You might want to also look to post here:
http://www.sqlteam.com/forums/forum.asp?FORUM_ID=3|||i did get that code to work but my main real problem is how do i get user sitting at my main form where he has to enter a patients lst name and first then when he hits the lookup patient button it opens my patient record with only the patient he typed in from the previous form . this was easy on access just added the expression into the query . but sql doesnt seem to support forms based queries. so what i really need is what does sql use instead of forms based queries to prompt a user for what they want to fill a record with.
if i use select lname from tblpatientinfo where lname="riley"
my form will only give me patients with last name riley
but how do i get it to ask what name we are looking for i guess would be the real question here. do i make a stored procedure to prompt for the information and base the form on the stored procedure or does my from on open have to request what im looking for. i may not be useing the correct wording so please be patient but sql is a whole new world to me.
i tried useing a filter on my form but if i put the stored procedure in the filter it doesnt recognize the stored procedure name only access query names it seems.
and also "like [forms]![main]![text2] & "*"
the lookup patient button would open my patient form based on the query results of what i typed in on the main form . ex: i type in riley it would give me my patient form with all the riley's in it.
how do i get my access form to open from information i am looking for in a query now that my whole database is back on a sql server.
by the way i upsized to a adp file no longer using mdb. please if possible explain in detail how to do this as i am having trouble with the syntax of sql. i understand i cant use "*" as a wildcard now but % instead.
thanks in advance.Hoe are you connecting to SQL Sever?
You shouldn't have to change your code...did you create linked tables?|||im assuming they not linked tables since it is now a access project i dont see the little globe when i used to use link tables.
i think everything is useing passthorugh queries now all my tables are on the sql server. just the forms and macros are on the frontend now.
my code i had to change was something like
dim db as dao.database
dim rs as dao.recordset
i had to change that to
dim conn as adodb.connection
dim rs as new adodb.recordset
and quite a few other things
if i left them linked the queries should have stayed working but im trying to get away from passing things through the jet engine and using the full power of sql .|||this was my old code to make a chartnumber based on the first and last name fields. this is the one that didnt work .
Public Function chartlookup()
Dim db As DAO.Database <--believe to be the problem
Dim rs As DAO.Recordset <-- belive to be the problem
Dim SQL As String
Dim NewNum As Integer
Dim NeWChartNum As String
SQL = "Select max(Cint(Right([chartnumber],6))) As RecNum From tblpatientinfo WHERE UCase(Left([chartnumber],5)) = '" & UCase(Left([Forms]![fpatient]![lname], 3)) & UCase(Left([Forms]![fpatient]![fname], 2)) & "'"
Set db = CurrentDb() <-- -problem
Set rs = db.OpenRecordset(SQL) <--problem
If IsNull([Forms]![fpatient]![chartnumber]) = False Then GoTo 400
If rs.EOF = False Then
If IsNull(rs!RecNum) = False Then
NewNum = rs!RecNum + 1
Else
NewNum = 1
End If
End If
'If NewNum = 1 Then
[Forms]![fpatient]![chartnumber] = UCase(Left([Forms]![fpatient]![lname], 3)) & UCase(Left([Forms]![fpatient]![fname], 2)) & Format(NewNum, "000000")
'End If|||Well this is a TOTAL guess...
If you're doing passthrus, you'll need SQL Server syntax..
(Why not just call stored procedures if you're making such a radical change?)
anyway...
SQL = "SELECT (MAX(CONVERT(int,(RIGHT(chartnumber))))
FROM tblpatientinfo
WHERE UPPER(LEFT(chartnumber,5)) = " _
& "'" UCase(Left([Forms]![fpatient]![lname], 3)) & UCase(Left([Forms]![fpatient]![fname], 2)) & "'"
my front end skills have gotten rusty...
You might want to also look to post here:
http://www.sqlteam.com/forums/forum.asp?FORUM_ID=3|||i did get that code to work but my main real problem is how do i get user sitting at my main form where he has to enter a patients lst name and first then when he hits the lookup patient button it opens my patient record with only the patient he typed in from the previous form . this was easy on access just added the expression into the query . but sql doesnt seem to support forms based queries. so what i really need is what does sql use instead of forms based queries to prompt a user for what they want to fill a record with.
if i use select lname from tblpatientinfo where lname="riley"
my form will only give me patients with last name riley
but how do i get it to ask what name we are looking for i guess would be the real question here. do i make a stored procedure to prompt for the information and base the form on the stored procedure or does my from on open have to request what im looking for. i may not be useing the correct wording so please be patient but sql is a whole new world to me.
i tried useing a filter on my form but if i put the stored procedure in the filter it doesnt recognize the stored procedure name only access query names it seems.
New to SQL but want's to learn!
Hi,
I was wandering if anyone could please recommend a good book to learn the basics of SQL 2000? I have made connections etc to a sql db through Visual Studio but would really like to read up on the subject and get a complete grounding on the subject. And also prepare me for the exam 70-229.
Many thanks,
James
Hi
If you need to learn the exam for SQL, you are best buying the Microsoft suite of books for that exam but I can tell you this, it is not easy AT ALL.
To learn SQL, its best looked at examples, reading books again and trial and error.
C-sharp corner website is a good place, but also posting here is a good idea too and I am sure many people will help you out.
There is alot of ground to cover, so start with the basics and move on upwards.
New to SQL and Web development
I have been in systems for many years on the admin side, and have had experience working with developers, however I am attempting to start the learning process for creating a data store with a web based UI. I have an understanding of the theories of what
is possible, but I don't know where to start. I have experience with HTML coding and static pages, but nothing that interfaces with a back end.
What are your suggestions of where to start.
This is a great site with lots of good answers to many questions:
http://www.aspfaq.com/
You will find good examples and information out on =
http://www.asp101.com/
--=20
Keith
"Otts73" <sh_tmail@.charter.net> wrote in message =
news:EFF6B423-F1FA-41D1-9139-3929979CCB3F@.microsoft.com...
> I have been in systems for many years on the admin side, and have had =
experience working with developers, however I am attempting to start the =
learning process for creating a data store with a web based UI. I have =
an understanding of the theories of what is possible, but I don't know =
where to start. I have experience with HTML coding and static pages, =
but nothing that interfaces with a back end.
>=20
> What are your suggestions of where to start.
|||As well as Keith's references, you might like to look at
http://www.w3schools.com/ado/default.asp
Regards,
Paul Ibison
is possible, but I don't know where to start. I have experience with HTML coding and static pages, but nothing that interfaces with a back end.
What are your suggestions of where to start.
This is a great site with lots of good answers to many questions:
http://www.aspfaq.com/
You will find good examples and information out on =
http://www.asp101.com/
--=20
Keith
"Otts73" <sh_tmail@.charter.net> wrote in message =
news:EFF6B423-F1FA-41D1-9139-3929979CCB3F@.microsoft.com...
> I have been in systems for many years on the admin side, and have had =
experience working with developers, however I am attempting to start the =
learning process for creating a data store with a web based UI. I have =
an understanding of the theories of what is possible, but I don't know =
where to start. I have experience with HTML coding and static pages, =
but nothing that interfaces with a back end.
>=20
> What are your suggestions of where to start.
|||As well as Keith's references, you might like to look at
http://www.w3schools.com/ado/default.asp
Regards,
Paul Ibison
New to SQL and Web development
I have been in systems for many years on the admin side, and have had experi
ence working with developers, however I am attempting to start the learning
process for creating a data store with a web based UI. I have an understand
ing of the theories of what
is possible, but I don't know where to start. I have experience with HTML c
oding and static pages, but nothing that interfaces with a back end.
What are your suggestions of where to start.This is a great site with lots of good answers to many questions:
http://www.aspfaq.com/
You will find good examples and information out on =
http://www.asp101.com/
--=20
Keith
"Otts73" <sh_tmail@.charter.net> wrote in message =
news:EFF6B423-F1FA-41D1-9139-3929979CCB3F@.microsoft.com...
> I have been in systems for many years on the admin side, and have had =
experience working with developers, however I am attempting to start the =
learning process for creating a data store with a web based UI. I have =
an understanding of the theories of what is possible, but I don't know =
where to start. I have experience with HTML coding and static pages, =
but nothing that interfaces with a back end.
>=20
> What are your suggestions of where to start.|||As well as Keith's references, you might like to look at
http://www.w3schools.com/ado/default.asp
Regards,
Paul Ibison
ence working with developers, however I am attempting to start the learning
process for creating a data store with a web based UI. I have an understand
ing of the theories of what
is possible, but I don't know where to start. I have experience with HTML c
oding and static pages, but nothing that interfaces with a back end.
What are your suggestions of where to start.This is a great site with lots of good answers to many questions:
http://www.aspfaq.com/
You will find good examples and information out on =
http://www.asp101.com/
--=20
Keith
"Otts73" <sh_tmail@.charter.net> wrote in message =
news:EFF6B423-F1FA-41D1-9139-3929979CCB3F@.microsoft.com...
> I have been in systems for many years on the admin side, and have had =
experience working with developers, however I am attempting to start the =
learning process for creating a data store with a web based UI. I have =
an understanding of the theories of what is possible, but I don't know =
where to start. I have experience with HTML coding and static pages, =
but nothing that interfaces with a back end.
>=20
> What are your suggestions of where to start.|||As well as Keith's references, you might like to look at
http://www.w3schools.com/ado/default.asp
Regards,
Paul Ibison
New to SQL and Web development
I have been in systems for many years on the admin side, and have had experience working with developers, however I am attempting to start the learning process for creating a data store with a web based UI. I have an understanding of the theories of what is possible, but I don't know where to start. I have experience with HTML coding and static pages, but nothing that interfaces with a back end
What are your suggestions of where to start.This is a great site with lots of good answers to many questions:
http://www.aspfaq.com/
You will find good examples and information out on =http://www.asp101.com/
-- Keith
"Otts73" <sh_tmail@.charter.net> wrote in message =news:EFF6B423-F1FA-41D1-9139-3929979CCB3F@.microsoft.com...
> I have been in systems for many years on the admin side, and have had =experience working with developers, however I am attempting to start the =learning process for creating a data store with a web based UI. I have =an understanding of the theories of what is possible, but I don't know =where to start. I have experience with HTML coding and static pages, =but nothing that interfaces with a back end.
> > What are your suggestions of where to start.|||As well as Keith's references, you might like to look at
http://www.w3schools.com/ado/default.asp
Regards,
Paul Ibisonsql
What are your suggestions of where to start.This is a great site with lots of good answers to many questions:
http://www.aspfaq.com/
You will find good examples and information out on =http://www.asp101.com/
-- Keith
"Otts73" <sh_tmail@.charter.net> wrote in message =news:EFF6B423-F1FA-41D1-9139-3929979CCB3F@.microsoft.com...
> I have been in systems for many years on the admin side, and have had =experience working with developers, however I am attempting to start the =learning process for creating a data store with a web based UI. I have =an understanding of the theories of what is possible, but I don't know =where to start. I have experience with HTML coding and static pages, =but nothing that interfaces with a back end.
> > What are your suggestions of where to start.|||As well as Keith's references, you might like to look at
http://www.w3schools.com/ado/default.asp
Regards,
Paul Ibisonsql
Subscribe to:
Posts (Atom)