DBA - Service Request Form

Hi,
I'm looking to implement a paper-based service request form for all DBA
related activities i.e., adding logins, users, tables etc... granting
permissions, dropping objects, etc... Does anyone have a link to an online
form that I can start with or have an in-house form (.doc) that I can use to
build my form?
No need to recreate the wheel if it has already been done.
Thanks in advance.
Jerry
Hi Jerry,
I made a long ago with excel file for the form. I have to find it from my
doc somewhere but it may give you a basic idea where you can start with.
I can attach it to you, let me know what email address you would like me to
send.
-- Sharing is a good thing...
Sunny
"Jerry Spivey" wrote:

> Hi,
> I'm looking to implement a paper-based service request form for all DBA
> related activities i.e., adding logins, users, tables etc... granting
> permissions, dropping objects, etc... Does anyone have a link to an online
> form that I can start with or have an in-house form (.doc) that I can use to
> build my form?
> No need to recreate the wheel if it has already been done.
> Thanks in advance.
> Jerry
>
>
|||Thanks. The address for this account will work fine.
Jerry
"SangHunJung" <SangHunJung@.discussions.microsoft.com> wrote in message
news:E6ACFE30-A74E-4120-B09C-9CEF3380A41D@.microsoft.com...[vbcol=seagreen]
> Hi Jerry,
> I made a long ago with excel file for the form. I have to find it from my
> doc somewhere but it may give you a basic idea where you can start with.
> I can attach it to you, let me know what email address you would like me
> to
> send.
> -- Sharing is a good thing...
> Sunny
> "Jerry Spivey" wrote:

DBA - Service Request Form

Hi,
I'm looking to implement a paper-based service request form for all DBA
related activities i.e., adding logins, users, tables etc... granting
permissions, dropping objects, etc... Does anyone have a link to an online
form that I can start with or have an in-house form (.doc) that I can use to
build my form?
No need to recreate the wheel if it has already been done.
Thanks in advance.
JerryHi Jerry,
I made a long ago with excel file for the form. I have to find it from my
doc somewhere but it may give you a basic idea where you can start with.
I can attach it to you, let me know what email address you would like me to
send.
-- Sharing is a good thing...
Sunny
"Jerry Spivey" wrote:

> Hi,
> I'm looking to implement a paper-based service request form for all DBA
> related activities i.e., adding logins, users, tables etc... granting
> permissions, dropping objects, etc... Does anyone have a link to an onlin
e
> form that I can start with or have an in-house form (.doc) that I can use
to
> build my form?
> No need to recreate the wheel if it has already been done.
> Thanks in advance.
> Jerry
>
>|||Thanks. The address for this account will work fine.
Jerry
"SangHunJung" <SangHunJung@.discussions.microsoft.com> wrote in message
news:E6ACFE30-A74E-4120-B09C-9CEF3380A41D@.microsoft.com...[vbcol=seagreen]
> Hi Jerry,
> I made a long ago with excel file for the form. I have to find it from my
> doc somewhere but it may give you a basic idea where you can start with.
> I can attach it to you, let me know what email address you would like me
> to
> send.
> -- Sharing is a good thing...
> Sunny
> "Jerry Spivey" wrote:
>

DBA - Service Request Form

Hi,
I'm looking to implement a paper-based service request form for all DBA
related activities i.e., adding logins, users, tables etc... granting
permissions, dropping objects, etc... Does anyone have a link to an online
form that I can start with or have an in-house form (.doc) that I can use to
build my form?
No need to recreate the wheel if it has already been done.
Thanks in advance.
JerryHi Jerry,
I made a long ago with excel file for the form. I have to find it from my
doc somewhere but it may give you a basic idea where you can start with.
I can attach it to you, let me know what email address you would like me to
send.
-- Sharing is a good thing...
Sunny
"Jerry Spivey" wrote:
> Hi,
> I'm looking to implement a paper-based service request form for all DBA
> related activities i.e., adding logins, users, tables etc... granting
> permissions, dropping objects, etc... Does anyone have a link to an online
> form that I can start with or have an in-house form (.doc) that I can use to
> build my form?
> No need to recreate the wheel if it has already been done.
> Thanks in advance.
> Jerry
>
>|||Thanks. The address for this account will work fine.
Jerry
"SangHunJung" <SangHunJung@.discussions.microsoft.com> wrote in message
news:E6ACFE30-A74E-4120-B09C-9CEF3380A41D@.microsoft.com...
> Hi Jerry,
> I made a long ago with excel file for the form. I have to find it from my
> doc somewhere but it may give you a basic idea where you can start with.
> I can attach it to you, let me know what email address you would like me
> to
> send.
> -- Sharing is a good thing...
> Sunny
> "Jerry Spivey" wrote:
>> Hi,
>> I'm looking to implement a paper-based service request form for all DBA
>> related activities i.e., adding logins, users, tables etc... granting
>> permissions, dropping objects, etc... Does anyone have a link to an
>> online
>> form that I can start with or have an in-house form (.doc) that I can use
>> to
>> build my form?
>> No need to recreate the wheel if it has already been done.
>> Thanks in advance.
>> Jerry
>>

DB2 to SQL Server 2005 Problem

I am trying to copy some tables from DB2 to SQL Server 2005. DB2 database is on IBM iSeries server (AS400). Code page of all tables on AS400 is IBM-037. My Windows code page is 1250. Provider for data source is -IBM DB2 UDB for iSeries IBMDA400 OleDb provider-.

I get a following warning:

Setting Source Connection (Warning)
Messages
* Warning 0x80202066: Source - ACCMST [1]: Cannot retrieve the column code page info from the OLE DB provider. If the component supports the "DefaultCodePage" property, the code page from that property will be used. Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page from the component's locale ID will be used.
(SQL Server Import and Export Wizard)

and data can not be copied.

I tryed to set source code page for source destination to 37, but it does't work.

Please help.

I am getting the same problem , a temporary workaround for me was to edit the SSIS transform and select float for packed decimal fields and integer for the other fields that could not have a data type assigned. once i did that the data copied. But this is an obvious problem for IBM to work on... I will pester our iSeries system admins to keep up and see if an IBM APAR ( program fix ) is released and post that info here.

DB2 to SQL Server 2005 Problem

I am trying to copy some tables from DB2 to SQL Server 2005. DB2 database is on IBM iSeries server (AS400). Code page of all tables on AS400 is IBM-037. My Windows code page is 1250. Provider for data source is -IBM DB2 UDB for iSeries IBMDA400 OleDb provider-.

I get a following warning:

Setting Source Connection (Warning)
Messages
* Warning 0x80202066: Source - ACCMST [1]: Cannot retrieve the column code page info from the OLE DB provider. If the component supports the "DefaultCodePage" property, the code page from that property will be used. Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page from the component's locale ID will be used.
(SQL Server Import and Export Wizard)

and data can not be copied.

I tryed to set source code page for source destination to 37, but it does't work.

Please help.

I am getting the same problem , a temporary workaround for me was to edit the SSIS transform and select float for packed decimal fields and integer for the other fields that could not have a data type assigned. once i did that the data copied. But this is an obvious problem for IBM to work on... I will pester our iSeries system admins to keep up and see if an IBM APAR ( program fix ) is released and post that info here.sql

db1.dbo.table to db2.other.table

Hi,
I need help/guidance setting up replication across dbs.
db1 has the same tables as db2. tables have the same fields.
db1 has "dbo.table" schema, db2 has "other.table" schema.
I setup simple replication and db1.dbo.tables are being added to db2 as
db2.dbo.tables instead of just refreshing data on db2.other.tables...
Any help would be greatly appreciated.
http://www.dbazine.com/sql/sql-articles/cotter1
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<Doug M> wrote in message
news:1058678B-8239-423C-B68D-C17EC9E882A5@.microsoft.com...
> Hi,
> I need help/guidance setting up replication across dbs.
> db1 has the same tables as db2. tables have the same fields.
> db1 has "dbo.table" schema, db2 has "other.table" schema.
> I setup simple replication and db1.dbo.tables are being added to db2 as
> db2.dbo.tables instead of just refreshing data on db2.other.tables...
> Any help would be greatly appreciated.
>

db_owner to all tables

is there a command that can change a login role to db_owner in all the
tables, or do i have to use
{
USE table_name
EXEC sp_adduser 'login name'
EXEC sp_addrolemember 'db_owner', 'login name'
}
for each of the tables ?

thanksHi

Rather than adding the login to the db_owner role, why not changed the
object ownership using sp_changeobjectowner?

e.g. to get a list of commands

DECLARE @.username sysname
SET @.username = 'ABC'
select 'EXEC sp_changeobjectowner ''' + u.name + '.' + o.name + ''',
''dbo''' from sysobjects o
JOIN sysusers u on o.uid = u.uid
where u.name = @.username
and o.type = 'U'

You could change this to a cursor and run each statement with EXEC. You will
need to watch out of dependencies and also make sure the correct owner
prefix is used wherever it is referenced.

If you want to change the owner or the database use sp_changedbowner.

The USE statement is for databases not tables.

John

<liorhal@.gmail.com> wrote in message
news:1116165893.813043.270140@.g44g2000cwa.googlegr oups.com...
> is there a command that can change a login role to db_owner in all the
> tables, or do i have to use
> {
> USE table_name
> EXEC sp_adduser 'login name'
> EXEC sp_addrolemember 'db_owner', 'login name'
> }
> for each of the tables ?
> thanks

db_datareader for new tables

I have added someone to the db_datareader role and that is fine. However, if I add a new table to the database, they do not have 'Select' permission for the new table. Is this by design? Is there a way around this to give users read permission on all tables current and future? Thanks for any answers to this question.I think the role db_datareader is a db-wide role, by default it applies to all tables/views in the database, including the newly created ones. You do not need to set the 'select'-permission.|||A bit of a longshot, but run

sp_helprotect [table name]

and see if someone has denied access to the table for some reason.|||According to Microsoft (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da_3xns.asp) a member of db_datareader can read every table, past, present, and future.

-PatP|||Explicity denying access to a table will trump db_datareader, but I don't think anything else does.|||in an unrelated note
how about creating views for all those users.
hmmmmmm?

views have many advantages over direct table access.

they add a security layer between the user and the table
they mask database complexity
they can increase read performance
and they can give you a layer between the root object and the user so object name changes can occur without recompillation of the application.


just a thought.|||I know what you mean, Curt. The db_datareader role makes it awful hard to add things like a salary table to your database, too ;-).

But then, when was the last time someone actually thought about security, anyway. I mean without the DBA storming over to his cube?|||i wont give a database to a developer until i explain the importance of views and stored procedures.

DB Tables Missing - Log Someplace?

Does MSSQL keep track of anything, anywhere related to tables being deleted
(when, by whom, etc.) from a DB if no type of auditing was setup?Hi
Yes, in the database transaction log. You can use 3rd party tools like LogPi
and Log Explorer to look at the transaction logs. this is assuming you have
full logging on and dump your transaction logs on a regular basis.
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/
"Absolutely" <abs@.spam.never> wrote in message
news:11aepite0idaacc@.corp.supernews.com...
> Does MSSQL keep track of anything, anywhere related to tables being
> deleted (when, by whom, etc.) from a DB if no type of auditing was setup?
>sql

DB Statistics

In the past I have seen a graphic display of space allocation of various db
objects (tables, indexes, etc...). I don't remember if it was sql server 7
or 2000.
Now I have installed sql server 2000 administrative client software. With
it I am accessing sql server 7 db. However, I can't find this graphic
representation.
Would anyone know how to make space allocation graphics appear it appear? or
perhaps point me to performance tables which contain this data?
Thanks in advance
In Enterprise Manager, right click on the database, select
View and then select Taskpad View.
The graphed view of the space for the database will be on
the bottom of the General tab page in Taskpad view.
-Sue
On Tue, 9 Nov 2004 13:10:13 -0800, "RG"
<RG@.discussions.microsoft.com> wrote:

>In the past I have seen a graphic display of space allocation of various db
>objects (tables, indexes, etc...). I don't remember if it was sql server 7
>or 2000.
>Now I have installed sql server 2000 administrative client software. With
>it I am accessing sql server 7 db. However, I can't find this graphic
>representation.
>Would anyone know how to make space allocation graphics appear it appear? or
>perhaps point me to performance tables which contain this data?
>Thanks in advance

DB Statistics

In the past I have seen a graphic display of space allocation of various db
objects (tables, indexes, etc...). I don't remember if it was sql server 7
or 2000.
Now I have installed sql server 2000 administrative client software. With
it I am accessing sql server 7 db. However, I can't find this graphic
representation.
Would anyone know how to make space allocation graphics appear it appear? or
perhaps point me to performance tables which contain this data?
Thanks in advanceIn Enterprise Manager, right click on the database, select
View and then select Taskpad View.
The graphed view of the space for the database will be on
the bottom of the General tab page in Taskpad view.
-Sue
On Tue, 9 Nov 2004 13:10:13 -0800, "RG"
<RG@.discussions.microsoft.com> wrote:

>In the past I have seen a graphic display of space allocation of various db
>objects (tables, indexes, etc...). I don't remember if it was sql server 7
>or 2000.
>Now I have installed sql server 2000 administrative client software. With
>it I am accessing sql server 7 db. However, I can't find this graphic
>representation.
>Would anyone know how to make space allocation graphics appear it appear? o
r
>perhaps point me to performance tables which contain this data?
>Thanks in advance

DB Statistics

In the past I have seen a graphic display of space allocation of various db
objects (tables, indexes, etc...). I don't remember if it was sql server 7
or 2000.
Now I have installed sql server 2000 administrative client software. With
it I am accessing sql server 7 db. However, I can't find this graphic
representation.
Would anyone know how to make space allocation graphics appear it appear? or
perhaps point me to performance tables which contain this data?
Thanks in advanceIn Enterprise Manager, right click on the database, select
View and then select Taskpad View.
The graphed view of the space for the database will be on
the bottom of the General tab page in Taskpad view.
-Sue
On Tue, 9 Nov 2004 13:10:13 -0800, "RG"
<RG@.discussions.microsoft.com> wrote:
>In the past I have seen a graphic display of space allocation of various db
>objects (tables, indexes, etc...). I don't remember if it was sql server 7
>or 2000.
>Now I have installed sql server 2000 administrative client software. With
>it I am accessing sql server 7 db. However, I can't find this graphic
>representation.
>Would anyone know how to make space allocation graphics appear it appear? or
>perhaps point me to performance tables which contain this data?
>Thanks in advance

DB Script

hello I need to script my Db, tables and storedprocedures. so i can create another same DB

Dim con As New SqlConnection(DBClass.Config.DBString)
Dim cmdName As String = File.OpenText("pro.sql").ReadToEnd()
Dim cmd As New SqlCommand(cmdName, con)
cmd.CommandType = CommandType.Text
con.Open()
Try
cmd.ExecuteNonQuery()
MessageBox.Show("DB Created")
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try

con.Close()

Any suggestions plzzzzzzzzzzz

you can right click on your DB and generate scripts for all objects in your DB. If you want to do it programmatically run the profiler and see what mgmt studio is doing to generate the scripts. You can do the same from your application.|||If SMO is used (liek in SSMS) There is some more information needed than this which is queried fromt he SQl Server. I would suggest using the SMO namespace for creating your script. SMO is the successor of DMO and has a dependency walker to keep track of dependent objects.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

if ur using sqlserver 2000 make use of the sql DMO functions...simple functions to achive wat u want... examples at C:\Program Files\Microsoft SQL Server\80\Tools\Devtools\Samples\Sqldmo

similarly for SS 2005 though DMO are supported(just for backward compatibility,wont be there in future editions), u can use SMO,which offers many additional functions...

|||Can you give me some more details about that plz|||have you looked in the folder in the post?

There should be a demo for you to look at which should give you more info as you requested.|||

I have no problem creating Data Base, I just want to have SQLScript(tables and procedures), this file called db.sql
Then I want to use this code, why Im having problem creating the procedures?

Dim con As New SqlConnection(DBClass.Config.DBString)
Dim cmdName As String = File.OpenText("db.sql").ReadToEnd()
Dim cmd As New SqlCommand(cmdName, con)
cmd.CommandType = CommandType.Text
con.Open()
Try
cmd.ExecuteNonQuery()
MessageBox.Show("DB Created")
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try

con.Close()

|||hmmm,

we seem to be duplicating ourselves as you have an open topic here; http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1006948&SiteID=1

In this thread I provide you with a code example, the example lets you read a script in a StreamReader, then execute this script on a database.

Please be careful you dont upset people posting the same questions in multiple topics

DB roles

I am creating a new user. I would like to give read only access just for the tables in a database. I had assigned only public and db_Datareader roles to this user. With these roles the user could able to see the script of the SPs and also the DTS packages. Also with the above roles the user could able to create new DTS packages and SPs. Is it possible to deny the user to look at the sps and ability to open the DTS packages created by some other users.

What I need to do is create a role with just table data read access so that they could just select the data only nothing more than that.
Also another role with dataread and ability to create the DTS packages from other servers by accessing this data. Anotherthing we need is With this role the users could create Database schema.

This is an urgent request. Please advise me ASAP.

ThanksHi,
Anyone could please give the solution for this. I did not get any responses since yesterday.
Thanks|||If I read the last couple of sentences correctly, you need tow roles:

Role 1:
Read-only access to tables in database
No ability to create DTS packages
No ability to read SPs

Role 2:
Read-only access to tables in database
Can Create DTS packages
Can create own DB Schema (?)

I believe that the ability to create DTS packages is a privilege granted to the Public role in the msdb database (which is an unpleasant default in my opinion). You can remove the privilege by revoking execute on the following SP to Public:

sp_add_DTSPackage

You might also consider the following:

sp_enum_DTSPackages

You will then have to create a custom role with the privileges "added back" to fulfill your second requirement. I wasn't certain by what you meant with the need to "create own db schema". Did you mean the ability to create tables?

Regards,

hmscott|||Yes. I Need Role1 as follows

Role 1:
Read-only access to tables in database
No ability to create or Read DTS packages
No ability to create or Read SPs

Role 2:
Read-only access to tables in database

Can Create DTS packages from other servers means to import data onto ther user's server from the tables from my server.

Can create own DB Schema Means if we give just read only access to the tables only on my server, could they be able to get create ER diagrams using ERWIN or some other tool?

I could not find either of these two procedure (sp_add_DTSPackage,sp_enum_DTSPackages) in BOL.

What I understood is if we have to revoke the execute permission on sp_add_DTSPackage the syntax would be
Revoke EXEC ON sp_add_DTSPackage TO RoleName
Is this right syntax.

Do we have to revoke for the public role or should I delete the public role (which will be assigned to a new user as a default) from the new user I created. If I delete this role will it have an effect on any other permissions the user has.
I am not sure what sp_enum_DTSPackages does.

Also I need to deny the permission to open the existing DTS packages by the user and modify them. What would be the solution.

I did not get what you meant by "You will then have to create a custom role with the privileges "added back" to fulfill your second requirement"

I am thinking of creating several custom roles like report user role, Developer role etc.
When we create new users, always public role will be assigned as default. Is is necessary to have this role for every user or we can delete this role.

I think I put too many questions. Please advise me.

Thanks|||Help from anyone??|||With packages you got it right, except replace REVOKE with DENY.

As per modifying SPs, I think you may have to DENY on SYSOBJECTS, unless you play with SYSCOMMENTS and such (can't test it now, Yukon is everywhere ;))|||How about if I delete the public role for the new user(which will be assigned as a default). Then the user can't add any DTS pages. I just want to make sure whether this will have any effects.

Thanks|||You can't. PUBLIC is (as you mentioned correctly) a default role that every user has to belong to.|||select choosen database properties from Enterprises manager go to permissions tab..there you can define create permissions for users/roles for table/view/store proc etc..

DB Role Security

Hello,
My mananger has asked me to see if it possible to allow
developers the rights to modify table structures only, and
not create, delete tables, and not create, modify and
delete views store procedures.
I am going to tell him "No" as I will need to give the
developers dlladmin access rights which allows full object
modification rights.
What do people think ?
PeterYou are partially right - you can grant the "Create Table" permission only,
which includes Alter and Drop Table permissions, but does not include
permissions to create, alter or drop views, functions and procedures.
--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Peter" <nospam@.thisemailaddress.co.uk> wrote in message
news:0b9301c36253$57729300$a601280a@.phx.gbl...
> Hello,
> My mananger has asked me to see if it possible to allow
> developers the rights to modify table structures only, and
> not create, delete tables, and not create, modify and
> delete views store procedures.
> I am going to tell him "No" as I will need to give the
> developers dlladmin access rights which allows full object
> modification rights.
> What do people think ?
> Peter|||Thanks Dejan,
Where do I set that option ?
Thanks
Peter
>--Original Message--
>You are partially right - you can grant the "Create
Table" permission only,
>which includes Alter and Drop Table permissions, but does
not include
>permissions to create, alter or drop views, functions and
procedures.
>--
>Dejan Sarka, SQL Server MVP
>FAQ from Neil & others at: http://www.sqlserverfaq.com
>Please reply only to the newsgroups.
>PASS - the definitive, global community
>for SQL Server professionals - http://www.sqlpass.org
>"Peter" <nospam@.thisemailaddress.co.uk> wrote in message
>news:0b9301c36253$57729300$a601280a@.phx.gbl...
>> Hello,
>> My mananger has asked me to see if it possible to allow
>> developers the rights to modify table structures only,
and
>> not create, delete tables, and not create, modify and
>> delete views store procedures.
>> I am going to tell him "No" as I will need to give the
>> developers dlladmin access rights which allows full
object
>> modification rights.
>> What do people think ?
>> Peter
>
>.
>|||Not sure on this, but may be possible to make developers
data reader and data writer (if relevant) and give explicit
GRANT ALTER TABLE TO <username>
"Peter" <nospam@.thisemailaddress.co.uk> wrote in message
news:0b9301c36253$57729300$a601280a@.phx.gbl...
> Hello,
> My mananger has asked me to see if it possible to allow
> developers the rights to modify table structures only, and
> not create, delete tables, and not create, modify and
> delete views store procedures.
> I am going to tell him "No" as I will need to give the
> developers dlladmin access rights which allows full object
> modification rights.
> What do people think ?
> Petersql

DB Replication or Table Replication via triggers?

Hello everyone,

I am involved in a scenario where there is a huge (SQL Server 2005)
production database containing tables that are updated multiple times
per second. End-user reports need to be generated against the data in
this database, and so the powers-that-be came to the conclusion that a
reporting database is necessary in order to offload report processing
from production; of course, this means that data will have to be
replicated to the reporting database. However, we do not need all of
the data in the production database, and perhaps a filtering criteria
can be established where only certain rows are replicated over to the
reporting database as they're inserted (and possibly updated/deleted).
The current though process is that the programmers designing the
queries/reports will know exactly what data they need from production
and be able to modify the replication criteria as needed. For example,
programmer A might write a report where the data he needs can be
expressed in a simple replication criteria for table T where column X
= "WOOD" and column Y = "MAHOGANY". Programmer B might come along a
month later and write a report whose relies on the same table T where
column X = "METAL" and column Z in (12, 24, 36). Programmer B will
have to modify Programmer A's replication criteria in such a way as to
accomodate both reports, in this case something like "Copy rows from
table T where (col X = "WOOD" and col Y = "MAHOGANY") or (col X =
"METAL" and col Z in (12, 24, 36))". The example I gave is really
trivial of course but is sufficient to give you an idea of what the
current thought-process is.

I assume that this is a requirement that many of you may have
encountered in the past and I am wondering what solutions you were
able to come up with. Personally, I believe that the above method is
prone to error (in this case the use of triggers to specify
replication criteria) and I'd much rather use replication services to
copy tables in their entirety. However, this does not seem to be an
option in my case due to the sheer size of certain tables. Is there
anything out there that performs replication based on complex
programmer defined criteria? Are triggers a viable alternative? Any
alternative out-of-the-box solutions?

Any feedback would be appreciated.

Regards!

AnthonyI'd recommend transactional replication with a nosync initialization. This
is where the initial setup on the reporting server is achieved by using a
restore of the database and after that, only subsequent changes are sent
down. If you are using SQL Server 2005, greater concurrency can be achieved
by using the read committed snapshot isolation level.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||Hello Paul,

Thank you for your response. I will look into SQL Server 2005's
replication and static row level filtering; are there any books or web-
sites you might recommend? I will need to be able to set up and modify
the row-filter criteria programatically, and the reviews @. Barnes and
Noble on "Pro SQL Server 2005 Replication" are pretty dismal.

Regards,

Anthony

On Apr 17, 3:09 pm, "Paul Ibison" <Paul.Ibi...@.Pygmalion.Comwrote:

Quote:

Originally Posted by

I'd recommend transactional replication with a nosync initialization. This
is where the initial setup on the reporting server is achieved by using a
restore of the database and after that, only subsequent changes are sent
down. If you are using SQL Server 2005, greater concurrency can be achieved
by using the read committed snapshot isolation level.
Cheers,
Paul Ibison SQL Server MVP,www.replicationanswers.com

|||Have a look at Hilary's book for snapshot and transactional, but if you're
after more merge info and don't like the Pro book then it's really BOL that
you need and then doing some scenarios for yourself to gain experience. As
for websites, I have some useful info on the site below and there are other
articles out on the various SQL Server sites you can get by googling, but
nothing I think specific to your requirements.
BTW this doesn't really lend itself to modifying the filters dynamically. At
least this is not as straightforward as you might think. Normally the
partitions are well designed to start with. If you want something more
dynamic, then I'd not filter at all in replication and I'd use filters on
the client application instead.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||Hello Paul,

On Apr 17, 6:26 pm, "Paul Ibison" <Paul.Ibi...@.Pygmalion.Comwrote:

Quote:

Originally Posted by

Have a look at Hilary's book for snapshot and transactional, but if you're
after more merge info and don't like the Pro book then it's really BOL that
you need and then doing some scenarios for yourself to gain experience. As
for websites, I have some useful info on the site below and there are other
articles out on the various SQL Server sites you can get by googling, but
nothing I think specific to your requirements.

Will do.

Quote:

Originally Posted by

BTW this doesn't really lend itself to modifying the filters dynamically. At
least this is not as straightforward as you might think. Normally the
partitions are well designed to start with. If you want something more
dynamic, then I'd not filter at all in replication and I'd use filters on
the client application instead.
Cheers,
Paul Ibison SQL Server MVP,www.replicationanswers.com

Ahhh... then that's a problem, I'd definitely need the ability to be
able to programatically and dynamically change the filtering criteria
as the need arises, in this case every time a new report is requested
that needs a subset of data not being captured by the replication
process. You would think that this is such a common scenario... Also,
filtering on the client side is not an option either since that would
mean that all of the data would get replicated to the reporting db. I
could have sworn that I read in msdn that the filters could be changed
via stored procs though... I'll have to look that up.

Thanks for your help Paul!

Anthony|||Anthony Paul wrote:

Quote:

Originally Posted by

I am involved in a scenario where there is a huge (SQL Server 2005)
production database containing tables that are updated multiple times
per second. End-user reports need to be generated against the data in
this database, and so the powers-that-be came to the conclusion that a
reporting database is necessary in order to offload report processing
from production; of course, this means that data will have to be
replicated to the reporting database. However, we do not need all of
the data in the production database, and perhaps a filtering criteria
can be established where only certain rows are replicated over to the
reporting database as they're inserted (and possibly updated/deleted).
The current though process is that the programmers designing the
queries/reports will know exactly what data they need from production
and be able to modify the replication criteria as needed. For example,
programmer A might write a report where the data he needs can be
expressed in a simple replication criteria for table T where column X
= "WOOD" and column Y = "MAHOGANY". Programmer B might come along a
month later and write a report whose relies on the same table T where
column X = "METAL" and column Z in (12, 24, 36). Programmer B will
have to modify Programmer A's replication criteria in such a way as to
accomodate both reports, in this case something like "Copy rows from
table T where (col X = "WOOD" and col Y = "MAHOGANY") or (col X =
"METAL" and col Z in (12, 24, 36))". The example I gave is really
trivial of course but is sufficient to give you an idea of what the
current thought-process is.
>
I assume that this is a requirement that many of you may have
encountered in the past and I am wondering what solutions you were
able to come up with. Personally, I believe that the above method is
prone to error (in this case the use of triggers to specify
replication criteria) and I'd much rather use replication services to
copy tables in their entirety. However, this does not seem to be an
option in my case due to the sheer size of certain tables. Is there
anything out there that performs replication based on complex
programmer defined criteria? Are triggers a viable alternative? Any
alternative out-of-the-box solutions?

Is it possible to create views, then configure things so that just
those views are replicated as tables on the second server?|||Hello Ed,

That's a very good idea, if it turns out that the filter isn't
dynamically configurable then perhaps using a view as a filter and
replicating the view can compensate since views can be modified at any
time. However, I doubt that replication can be done on a view rather
than on a table. I'll have to check it out!

Regards,

Anthony

On Apr 17, 9:46 pm, Ed Murphy <emurph...@.socal.rr.comwrote:

Quote:

Originally Posted by

Anthony Paul wrote:

Quote:

Originally Posted by

I am involved in a scenario where there is a huge (SQL Server 2005)
production database containing tables that are updated multiple times
per second. End-user reports need to be generated against the data in
this database, and so the powers-that-be came to the conclusion that a
reporting database is necessary in order to offload report processing
from production; of course, this means that data will have to be
replicated to the reporting database. However, we do not need all of
the data in the production database, and perhaps a filtering criteria
can be established where only certain rows are replicated over to the
reporting database as they're inserted (and possibly updated/deleted).
The current though process is that the programmers designing the
queries/reports will know exactly what data they need from production
and be able to modify the replication criteria as needed. For example,
programmer A might write a report where the data he needs can be
expressed in a simple replication criteria for table T where column X
= "WOOD" and column Y = "MAHOGANY". Programmer B might come along a
month later and write a report whose relies on the same table T where
column X = "METAL" and column Z in (12, 24, 36). Programmer B will
have to modify Programmer A's replication criteria in such a way as to
accomodate both reports, in this case something like "Copy rows from
table T where (col X = "WOOD" and col Y = "MAHOGANY") or (col X =
"METAL" and col Z in (12, 24, 36))". The example I gave is really
trivial of course but is sufficient to give you an idea of what the
current thought-process is.

>

Quote:

Originally Posted by

I assume that this is a requirement that many of you may have
encountered in the past and I am wondering what solutions you were
able to come up with. Personally, I believe that the above method is
prone to error (in this case the use of triggers to specify
replication criteria) and I'd much rather use replication services to
copy tables in their entirety. However, this does not seem to be an
option in my case due to the sheer size of certain tables. Is there
anything out there that performs replication based on complex
programmer defined criteria? Are triggers a viable alternative? Any
alternative out-of-the-box solutions?

>
Is it possible to create views, then configure things so that just
those views are replicated as tables on the second server?- Hide quoted text -
>
- Show quoted text -

|||I just finished looking up using indexed views versus a filter for
replication and it turns out that the view is much slower than a
filter (about 3x as slow) because the log reader has to log each
transaction twice, once for the view and once for the table. In my
case performance is of utmost concern so the overhead involved in this
is not something they can live with. Sigh...

Quote:

Originally Posted by

Quote:

Originally Posted by

Is it possible to create views, then configure things so that just
those views are replicated as tables on the second server?- Hide quoted text -

>

Quote:

Originally Posted by

- Show quoted text -- Hide quoted text -

>
- Show quoted text -

|||On Apr 18, 12:24 am, Anthony Paul <anthonypa...@.gmail.comwrote:

Quote:

Originally Posted by

Hello Paul,
>
On Apr 17, 6:26 pm, "Paul Ibison" <Paul.Ibi...@.Pygmalion.Comwrote:
>

Quote:

Originally Posted by

Have a look at Hilary's book for snapshot and transactional, but if you're
after more merge info and don't like the Pro book then it's really BOL that
you need and then doing some scenarios for yourself to gain experience. As
for websites, I have some useful info on the site below and there are other
articles out on the various SQL Server sites you can get by googling, but
nothing I think specific to your requirements.

>
Will do.
>

Quote:

Originally Posted by

BTW this doesn't really lend itself to modifying the filters dynamically. At
least this is not as straightforward as you might think. Normally the
partitions are well designed to start with. If you want something more
dynamic, then I'd not filter at all in replication and I'd use filters on
the client application instead.
Cheers,
Paul Ibison SQL Server MVP,www.replicationanswers.com

>
Ahhh... then that's a problem, I'd definitely need the ability to be
able to programatically and dynamically change the filtering criteria
as the need arises, in this case every time a new report is requested
that needs a subset of data not being captured by the replication
process. You would think that this is such a common scenario... Also,
filtering on the client side is not an option either since that would
mean that all of the data would get replicated to the reporting db. I
could have sworn that I read in msdn that the filters could be changed
via stored procs though... I'll have to look that up.
>
Thanks for your help Paul!
>
Anthony

This may sound like a stupid question, but are you sure replicating
the whole database isn't an option? I know you've described the
database as huge, but one mans huge is another mans insignificant (or
the other way around).

It just sounds like you're putting in a lot of work when you may be
able to keep it simple. Apologies if this is a path you've already
worn smooth, just wondering what has made you sure that bog standard
replication isn't the way to go.

Damien|||Hello Damien,

I would *love* to have a full replication going and not have to worry
about the added complexity of creating dynamic filters or triggers,
but the powers that be simply do not consider it an option. Since I'm
not the one that makes the decisions, I can only go by whatever
options are available. They want ONLY a subset of data to be captured,
nothing more. That would be fine with me if the filter was static (ie.
not subject to change every time a new report is requested) but given
the requirements I am in the same camp as you that a full replication
would be best.

Regards,

Anthony

Quote:

Originally Posted by

This may sound like a stupid question, but are you sure replicating
the whole database isn't an option? I know you've described the
database as huge, but one mans huge is another mans insignificant (or
the other way around).
>
It just sounds like you're putting in a lot of work when you may be
able to keep it simple. Apologies if this is a path you've already
worn smooth, just wondering what has made you sure that bog standard
replication isn't the way to go.
>
Damien- Hide quoted text -
>
- Show quoted text -

|||Anthony Paul wrote:

Quote:

Originally Posted by

I would *love* to have a full replication going and not have to worry
about the added complexity of creating dynamic filters or triggers,
but the powers that be simply do not consider it an option. Since I'm
not the one that makes the decisions, I can only go by whatever
options are available. They want ONLY a subset of data to be captured,
nothing more. That would be fine with me if the filter was static (ie.
not subject to change every time a new report is requested) but given
the requirements I am in the same camp as you that a full replication
would be best.

TPTB may start considering it an option if you give them a cost
analysis, depending on whether their previous motivation was "wouldn't
it be nice if" (yes, but) or "we think this is cheaper" (no it isn't)
or "this is required for security reasons" (ugh, okay) or whatever.|||Anthony Paul (anthonypaulo@.gmail.com) writes:

Quote:

Originally Posted by

I would *love* to have a full replication going and not have to worry
about the added complexity of creating dynamic filters or triggers,
but the powers that be simply do not consider it an option. Since I'm
not the one that makes the decisions, I can only go by whatever
options are available. They want ONLY a subset of data to be captured,
nothing more. That would be fine with me if the filter was static (ie.
not subject to change every time a new report is requested) but given
the requirements I am in the same camp as you that a full replication
would be best.

From my meager experience of replication, it seems clear that the database
has to be really huge - several terabytes - to make a dynamic filtering
defensible from a cost perspective. It would be difficult to develop,
difficult to maintain and manage.

The only serious option I see to full replication is a static subset.
That is define what will be supported in replication V1. If a new reqiure-
ment that is not covered, it would have to wait to V2. The idea would
of course to only strip really big stuff with low proability to be included.

And this is what you should tell the powers that be: replicating the entire
database will be far less expensive than changing what is replicated
dynamically.

--
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

DB Replication or Table Replication via triggers?

Hello everyone,
I am involved in a scenario where there is a huge (SQL Server 2005)
production database containing tables that are updated multiple times
per second. End-user reports need to be generated against the data in
this database, and so the powers-that-be came to the conclusion that a
reporting database is necessary in order to offload report processing
from production; of course, this means that data will have to be
replicated to the reporting database. However, we do not need all of
the data in the production database, and perhaps a filtering criteria
can be established where only certain rows are replicated over to the
reporting database as they're inserted (and possibly updated/deleted).
The current though process is that the programmers designing the
queries/reports will know exactly what data they need from production
and be able to modify the replication criteria as needed. For example,
programmer A might write a report where the data he needs can be
expressed in a simple replication criteria for table T where column X
= "WOOD" and column Y = "MAHOGANY". Programmer B might come along a
month later and write a report whose relies on the same table T where
column X = "METAL" and column Z in (12, 24, 36). Programmer B will
have to modify Programmer A's replication criteria in such a way as to
accomodate both reports, in this case something like "Copy rows from
table T where (col X = "WOOD" and col Y = "MAHOGANY") or (col X =
"METAL" and col Z in (12, 24, 36))". The example I gave is really
trivial of course but is sufficient to give you an idea of what the
current thought-process is.
I assume that this is a requirement that many of you may have
encountered in the past and I am wondering what solutions you were
able to come up with. Personally, I believe that the above method is
prone to error (in this case the use of triggers to specify
replication criteria) and I'd much rather use replication services to
copy tables in their entirety. However, this does not seem to be an
option in my case due to the sheer size of certain tables. Is there
anything out there that performs replication based on complex
programmer defined criteria? Are triggers a viable alternative? Any
alternative out-of-the-box solutions?
Any feedback would be appreciated.
Regards!
Anthony
Hello Paul,
Thank you for your response. I will look into SQL Server 2005's
replication and static row level filtering; are there any books or web-
sites you might recommend? I will need to be able to set up and modify
the row-filter criteria programatically, and the reviews @. Barnes and
Noble on "Pro SQL Server 2005 Replication" are pretty dismal.
Regards,
Anthony
On Apr 17, 3:09 pm, "Paul Ibison" <Paul.Ibi...@.Pygmalion.Com> wrote:
> I'd recommend transactional replication with a nosync initialization. This
> is where the initial setup on the reporting server is achieved by using a
> restore of the database and after that, only subsequent changes are sent
> down. If you are using SQL Server 2005, greater concurrency can be achieved
> by using the read committed snapshot isolation level.
> Cheers,
> Paul Ibison SQL Server MVP,www.replicationanswers.com
|||Hello Paul,
On Apr 17, 6:26 pm, "Paul Ibison" <Paul.Ibi...@.Pygmalion.Com> wrote:
> Have a look at Hilary's book for snapshot and transactional, but if you're
> after more merge info and don't like the Pro book then it's really BOL that
> you need and then doing some scenarios for yourself to gain experience. As
> for websites, I have some useful info on the site below and there are other
> articles out on the various SQL Server sites you can get by googling, but
> nothing I think specific to your requirements.
Will do.

> BTW this doesn't really lend itself to modifying the filters dynamically. At
> least this is not as straightforward as you might think. Normally the
> partitions are well designed to start with. If you want something more
> dynamic, then I'd not filter at all in replication and I'd use filters on
> the client application instead.
> Cheers,
> Paul Ibison SQL Server MVP,www.replicationanswers.com
Ahhh... then that's a problem, I'd definitely need the ability to be
able to programatically and dynamically change the filtering criteria
as the need arises, in this case every time a new report is requested
that needs a subset of data not being captured by the replication
process. You would think that this is such a common scenario... Also,
filtering on the client side is not an option either since that would
mean that all of the data would get replicated to the reporting db. I
could have sworn that I read in msdn that the filters could be changed
via stored procs though... I'll have to look that up.
Thanks for your help Paul!
Anthony
|||Anthony Paul wrote:

> I am involved in a scenario where there is a huge (SQL Server 2005)
> production database containing tables that are updated multiple times
> per second. End-user reports need to be generated against the data in
> this database, and so the powers-that-be came to the conclusion that a
> reporting database is necessary in order to offload report processing
> from production; of course, this means that data will have to be
> replicated to the reporting database. However, we do not need all of
> the data in the production database, and perhaps a filtering criteria
> can be established where only certain rows are replicated over to the
> reporting database as they're inserted (and possibly updated/deleted).
> The current though process is that the programmers designing the
> queries/reports will know exactly what data they need from production
> and be able to modify the replication criteria as needed. For example,
> programmer A might write a report where the data he needs can be
> expressed in a simple replication criteria for table T where column X
> = "WOOD" and column Y = "MAHOGANY". Programmer B might come along a
> month later and write a report whose relies on the same table T where
> column X = "METAL" and column Z in (12, 24, 36). Programmer B will
> have to modify Programmer A's replication criteria in such a way as to
> accomodate both reports, in this case something like "Copy rows from
> table T where (col X = "WOOD" and col Y = "MAHOGANY") or (col X =
> "METAL" and col Z in (12, 24, 36))". The example I gave is really
> trivial of course but is sufficient to give you an idea of what the
> current thought-process is.
> I assume that this is a requirement that many of you may have
> encountered in the past and I am wondering what solutions you were
> able to come up with. Personally, I believe that the above method is
> prone to error (in this case the use of triggers to specify
> replication criteria) and I'd much rather use replication services to
> copy tables in their entirety. However, this does not seem to be an
> option in my case due to the sheer size of certain tables. Is there
> anything out there that performs replication based on complex
> programmer defined criteria? Are triggers a viable alternative? Any
> alternative out-of-the-box solutions?
Is it possible to create views, then configure things so that just
those views are replicated as tables on the second server?
|||Hello Ed,
That's a very good idea, if it turns out that the filter isn't
dynamically configurable then perhaps using a view as a filter and
replicating the view can compensate since views can be modified at any
time. However, I doubt that replication can be done on a view rather
than on a table. I'll have to check it out!
Regards,
Anthony
On Apr 17, 9:46 pm, Ed Murphy <emurph...@.socal.rr.com> wrote:
> Anthony Paul wrote:
>
> Is it possible to create views, then configure things so that just
> those views are replicated as tables on the second server... Hide quoted text -
> - Show quoted text -
|||I just finished looking up using indexed views versus a filter for
replication and it turns out that the view is much slower than a
filter (about 3x as slow) because the log reader has to log each
transaction twice, once for the view and once for the table. In my
case performance is of utmost concern so the overhead involved in this
is not something they can live with. Sigh...

>
> - Show quoted text -
|||On Apr 18, 12:24 am, Anthony Paul <anthonypa...@.gmail.com> wrote:
> Hello Paul,
> On Apr 17, 6:26 pm, "Paul Ibison" <Paul.Ibi...@.Pygmalion.Com> wrote:
>
> Will do.
>
> Ahhh... then that's a problem, I'd definitely need the ability to be
> able to programatically and dynamically change the filtering criteria
> as the need arises, in this case every time a new report is requested
> that needs a subset of data not being captured by the replication
> process. You would think that this is such a common scenario... Also,
> filtering on the client side is not an option either since that would
> mean that all of the data would get replicated to the reporting db. I
> could have sworn that I read in msdn that the filters could be changed
> via stored procs though... I'll have to look that up.
> Thanks for your help Paul!
> Anthony
This may sound like a stupid question, but are you sure replicating
the whole database isn't an option? I know you've described the
database as huge, but one mans huge is another mans insignificant (or
the other way around).
It just sounds like you're putting in a lot of work when you may be
able to keep it simple. Apologies if this is a path you've already
worn smooth, just wondering what has made you sure that bog standard
replication isn't the way to go.
Damien
|||Hello Damien,
I would *love* to have a full replication going and not have to worry
about the added complexity of creating dynamic filters or triggers,
but the powers that be simply do not consider it an option. Since I'm
not the one that makes the decisions, I can only go by whatever
options are available. They want ONLY a subset of data to be captured,
nothing more. That would be fine with me if the filter was static (ie.
not subject to change every time a new report is requested) but given
the requirements I am in the same camp as you that a full replication
would be best.
Regards,
Anthony

> This may sound like a stupid question, but are you sure replicating
> the whole database isn't an option? I know you've described the
> database as huge, but one mans huge is another mans insignificant (or
> the other way around).
> It just sounds like you're putting in a lot of work when you may be
> able to keep it simple. Apologies if this is a path you've already
> worn smooth, just wondering what has made you sure that bog standard
> replication isn't the way to go.
> Damien- Hide quoted text -
> - Show quoted text -
|||Anthony Paul wrote:

> I would *love* to have a full replication going and not have to worry
> about the added complexity of creating dynamic filters or triggers,
> but the powers that be simply do not consider it an option. Since I'm
> not the one that makes the decisions, I can only go by whatever
> options are available. They want ONLY a subset of data to be captured,
> nothing more. That would be fine with me if the filter was static (ie.
> not subject to change every time a new report is requested) but given
> the requirements I am in the same camp as you that a full replication
> would be best.
TPTB may start considering it an option if you give them a cost
analysis, depending on whether their previous motivation was "wouldn't
it be nice if" (yes, but) or "we think this is cheaper" (no it isn't)
or "this is required for security reasons" (ugh, okay) or whatever.
|||Anthony Paul (anthonypaulo@.gmail.com) writes:
> I would *love* to have a full replication going and not have to worry
> about the added complexity of creating dynamic filters or triggers,
> but the powers that be simply do not consider it an option. Since I'm
> not the one that makes the decisions, I can only go by whatever
> options are available. They want ONLY a subset of data to be captured,
> nothing more. That would be fine with me if the filter was static (ie.
> not subject to change every time a new report is requested) but given
> the requirements I am in the same camp as you that a full replication
> would be best.
From my meager experience of replication, it seems clear that the database
has to be really huge - several terabytes - to make a dynamic filtering
defensible from a cost perspective. It would be difficult to develop,
difficult to maintain and manage.
The only serious option I see to full replication is a static subset.
That is define what will be supported in replication V1. If a new reqiure-
ment that is not covered, it would have to wait to V2. The idea would
of course to only strip really big stuff with low proability to be included.
And this is what you should tell the powers that be: replicating the entire
database will be far less expensive than changing what is replicated
dynamically.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

DB Primary Key question

I am using the ASP.Net 2.0 Membership tables and would like to add my own User_Profile table and connect it back to the aspnet_Users table.

Usually I always have the Primary Key be the same as the table name for example my User_Profiles table would have a UserProfileID, my Languages table would have a languageID.

In this case I found this sample

The first - which affords the greatest flexibility, but requires the most upfront effort - is to create a custom data store for this information. If you are using the SqlMembershipProvider, this would mean creating an additional database table that had as a primary key the UserId value from the aspnet_Users table and columns for each of the additional user properties. In the online messageboard example, the table might be called forums_UserProfile and have columns like UserId (a primary key and a foreign key back to aspnet_Users.UserId), HomepageUrl, Signature, and IMAddress.

where it suggests I just use the UserID as the Primary Key in my User_Profiles table. Looking at my database design I could use the UserID in many places as my Primary Key instead of having a Primary Key which relates to the table name and then UserID as just a column in the table.

Are there any pros and cons? Which one is better database design? What is best practice?

Thanks,

Newbie

Hi,

UserID is the promary key in Users table (like aspnet_Users) and a FK in related tables (like UserProfile etc). Since each profile can be identified by the userID only, there is nothing wrong in having it as the PK of the same table. Why would you like to have a surrogate key for such a table (when it doesnt really need one). IMHO, this is fine.

Vivek

DB Primary Key question

I am using the ASP.Net 2.0 Membership tables and would like to add my own User_Profile table and connect it back to the aspnet_Users table.

Usually I always have the Primary Key be the same as the table name for example my User_Profiles table would have a UserProfileID, my Languages table would have a languageID.

In this case I found this sample

The first - which affords the greatest flexibility, but requires the most upfront effort - is to create a custom data store for this information. If you are using the SqlMembershipProvider, this would mean creating an additional database table that had as a primary key the UserId value from the aspnet_Users table and columns for each of the additional user properties. In the online messageboard example, the table might be called forums_UserProfile and have columns like UserId (a primary key and a foreign key back to aspnet_Users.UserId), HomepageUrl, Signature, and IMAddress.

where it suggests I just use the UserID as the Primary Key in my User_Profiles table. Looking at my database design I could use the UserID in many places as my Primary Key instead of having a Primary Key which relates to the table name and then UserID as just a column in the table.

Are there any pros and cons? Which one is better database design? What is best practice?

Thanks,

Newbie

This approach -- using the same name in the related table as in the

primary -- is appropriate when the related table is a subset of the

objects in the primary table, rather than a linking table between two or more primary entities, and has a

one-to-one relationship to the primary table. Usually the related

table is created to avoid nulls in a core table.

A product

inventory might be an example. It makes sense to have a unique

product_id for each product manufactured by a company. This would of

course be the primary key for the master "products" table. You would

not want to have information specific to a particular line of producs

in this table, though. Say the comapny makes tools, and one of their

lines is sockets. Fields such as socket_type [e.g. hex, star],

drive_size, or socket_size would be irrelevant to many other tools, but

quite necessary to the sockets line. Therefore, you might have a data

structure like:

tools (
product_id char(12) primary key,
tool_class varchar(64),
price decimal,
...
);

sockets (
product_id char(12) primary key references tools(product_id),
socket_type smallint,
drive_size decimal,
socket_size decimal,
...
);

This

works because all sockets are tools, though many tools are not

sockets. Likewise, I would choose product_id here, rather than say

tool_id, for flexibility. The company may also carry other lines of

products for which tool_class would be an inappropriate field. They

are still all products, though.

Hope this helps,

Joseph

db owner

My customer upsized his Access database tables. Then we linked the Access
front end to the SQL server. But instead of seeing the tables named
dbo_Customer and dbo_Orders we are seeing timothyl_Customer and
timothyl_Orders.
Obviously, he was not a member of the system administrators when he created
the tables, so he is the owner. But now my stored procedures, etc., which
refer to dbo.Customer do not work!
How can I fix this so that I can get back to dbo.filename?COnsider using SP_Changeobjectowner to change the owner of the objects.
--
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"menacher" <menacher@.discussions.microsoft.com> schrieb im Newsbeitrag
news:DCB21288-FAC2-40FA-9F56-9D9A4AC818FF@.microsoft.com...
> My customer upsized his Access database tables. Then we linked the Access
> front end to the SQL server. But instead of seeing the tables named
> dbo_Customer and dbo_Orders we are seeing timothyl_Customer and
> timothyl_Orders.
> Obviously, he was not a member of the system administrators when he
> created
> the tables, so he is the owner. But now my stored procedures, etc., which
> refer to dbo.Customer do not work!
> How can I fix this so that I can get back to dbo.filename?|||Would you agree that the syntax should read
exec sp_changeobjectowner 'timothyl.customer', 'sa'
I assume that the owner 'sa' (the built in administrator) is the proper
owner?
Viele dank.
"Jens Sü�meyer" wrote:
> COnsider using SP_Changeobjectowner to change the owner of the objects.
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "menacher" <menacher@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:DCB21288-FAC2-40FA-9F56-9D9A4AC818FF@.microsoft.com...
> > My customer upsized his Access database tables. Then we linked the Access
> > front end to the SQL server. But instead of seeing the tables named
> > dbo_Customer and dbo_Orders we are seeing timothyl_Customer and
> > timothyl_Orders.
> >
> > Obviously, he was not a member of the system administrators when he
> > created
> > the tables, so he is the owner. But now my stored procedures, etc., which
> > refer to dbo.Customer do not work!
> >
> > How can I fix this so that I can get back to dbo.filename?
>
>|||(Viele dank. --> Cool, but its "Vielen Dank", just for your personal
knowledge no to be a peapicker ;-) )
No, it should read exec sp_changeobjectowner 'timothyl.customer', 'dbo'
--
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"menacher" <menacher@.discussions.microsoft.com> schrieb im Newsbeitrag
news:8F5E8126-09DC-4E3E-AA30-5C8EE0ABDDC5@.microsoft.com...
> Would you agree that the syntax should read
> exec sp_changeobjectowner 'timothyl.customer', 'sa'
> I assume that the owner 'sa' (the built in administrator) is the proper
> owner?
> Viele dank.
> "Jens Süßmeyer" wrote:
>> COnsider using SP_Changeobjectowner to change the owner of the objects.
>> --
>> HTH, Jens Suessmeyer.
>> --
>> http://www.sqlserver2005.de
>> --
>> "menacher" <menacher@.discussions.microsoft.com> schrieb im Newsbeitrag
>> news:DCB21288-FAC2-40FA-9F56-9D9A4AC818FF@.microsoft.com...
>> > My customer upsized his Access database tables. Then we linked the
>> > Access
>> > front end to the SQL server. But instead of seeing the tables named
>> > dbo_Customer and dbo_Orders we are seeing timothyl_Customer and
>> > timothyl_Orders.
>> >
>> > Obviously, he was not a member of the system administrators when he
>> > created
>> > the tables, so he is the owner. But now my stored procedures, etc.,
>> > which
>> > refer to dbo.Customer do not work!
>> >
>> > How can I fix this so that I can get back to dbo.filename?
>>