Creating primary key on a view

i'm connecting a view to microsoft mappoint using a .UDL. when i link the
data i need to select the primary key so i choose the customer number, but
there are a few shops that have to shops but one customer number so neither
go in to the map because they are duplicates. so i was wondering if i could
create a primary key in the view that is sort of like the autonumber in
access. so each shop would be given a number when the view is called and tha
t
could be my primary key. the number wouldn't even have to match up with the
same number every time i would just need it to get every shop into mappoint.
if anyone has any suggestions i would really appreciate it. thank you.
jb
Message posted via http://www.webservertalk.comjbennett via webservertalk.com wrote:
> i'm connecting a view to microsoft mappoint using a .UDL. when i link the
> data i need to select the primary key so i choose the customer number, but
> there are a few shops that have to shops but one customer number so neithe
r
> go in to the map because they are duplicates. so i was wondering if i coul
d
> create a primary key in the view that is sort of like the autonumber in
> access. so each shop would be given a number when the view is called and t
hat
> could be my primary key. the number wouldn't even have to match up with th
e
> same number every time i would just need it to get every shop into mappoin
t.
> if anyone has any suggestions i would really appreciate it. thank you.
> jb
> --
> Message posted via http://www.webservertalk.com
Adding an "auto-number" (the similar feature is called IDENTITY in SQL
Server) doesn't seem to make much sense for the situation you
described. For future reference, it's a reliable rule of thumb that
doing anything "like Access" in SQL Server is often going to be a big
mistake ;-)
Instead, create a table called Shops. Shops obviously isn't the same as
Customers.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||>
>Instead, create a table called Shops. Shops obviously isn't the same as
>Customers.
Thank you for your reply, i'll keep in mind not to try stuff in SQL that i
can do in access:) we already have a customer table. the problem is that we
ship ups and some of our shops have po boxes for address so we need a ship t
o
address which is stored in another table. so i created a view that pulls in
the customer info with the shipping address. after i did that i came across
a
problem that some shops had 2 stores under one customer number creating a
duplicate primary key, because in mappoint i had to use custno as the primar
y
key. we have shops added wly and inactivated wly so i would have to
update a table all the time, that is why i used a view. if there is a way to
create a table that automatically updates that would be great. thanks again
for all your help.
jb
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200606/1

Creating Parent Child Hierarchies

Hi
I have the following in my cube
Location Dimension containing LocationID and LocationName attributes.
Customer Dimension containing CustomerID,CustomerName....,LocationID
The LocationID in the Location Dimension and Customer Dimension are related to each other.
I want to create a hierarchy with the LocationName in the First Level and the CustomerName in the second level. How to I accomplish this? As i said I already have a relationship defined between the two tables.

Thanks and Regards
Guruprasad Karnik

This is not a recursive hierarchy from what you are describing.

You can find a recursive hierarchy in the employee table in the Adventure works sample data base that is a part of the SQL server 2005 installation. You use parent-child hierarchies with this type of primary key-foreign key relationship in the same table.

If you download the performance guide for SSAS2005 (see the first post in this forum) and have a look at page 82 and further(Referenced relationsships), you will find a lot of helpful information.

HTH

Thomas Ivarsson

Creating new tables in aspnetdb.mdf

I have made a new table called 'customer' which i wish to tie into the userId of the db. I have used a db diagram to do this (there are keys on each side of the link and userId is the FK) . When i put the membership/profile view onto the form alongside the new customer table nothing displays in the customer table when i run the app., i dont even see the titles - any ideas (i'm new to this sql malarky btw - so its probably something unbelievably straightforward). Any help appreciated.

Thanks

Hi,

By default, the membership/profile view will not show your additional information.

Since you have added one table into the database, and need to show it on your view, you will need to use your own query to get the user info together with the data in customer table. This can be done either by creating a View or use JOIN statement directly.

Then, you can customize the web UI to show the additional info.

HTH. If this does not answer your question, please feel free to mark the post as Not Answered and reply. Thank you!

|||

Hi, and thanks for replying.

I sort of rather optimistically and naively thought that because my new table was using pre-defined field variables including the key field (i.e. UserId (key), UserName etc.) that the system would know that these were what I required to be displayed (through the diagram), with no further programming, except to set to set the new field variables, that I've added, to new values within the program, and that the new fields would simply display the default value until I did something with them. - in another world that might have happened. After all, what have I created the diagram for?

What I didnt realise was that the aspnet.mdf tables are created, configured, and deleted through the Stored Procedures and that I needed to do the same for my table, even though there is a diagram telling the system that this 'linking in' of the aspnet_userId and my table is what I want to happen .

As I'm using the UserId as my key field I want the table to reflect the same membership as the aspnet.mdf membership tally - so I've sneaked in one or two statements within the createuser and deleteuser Stored Procedures to put in or remove UserId values from my table as new members are created or deleted respectively. I hope that that will solve my problem - it seems to have so far. I just cant help thinking that somewhere along the way my table is not going to tally with aspnet users for some reason, due to something that I'm not aware of (yet), caused by some insertion or deletion of a UserId without the createuser or deleteuser Stored Procedures being involved.

I'm sort of at a loss as to why I've created the diagram. I could now delete it and nothing would change.

Thanks again.

Creating different instances for each customer

Hello,
We want to manage customers over a VPN, such that a customer logs into the
software, the accounting software (smart client), and checks his/her info.
We at the server want to manage the accounts with the software.
Is creating different instances for each customer a good way, or we should
create one instance and add their databases in the same instance? What are
the pros and cons?
I appreciate your comments in advance.
--
MikeCreating one instance per customer is almost always a bad idea. First of all,
a server can only support that many instances (the maximum number of SQL2000
instances supported on a computer is 16). Secondly, there is significant
overhead in maintaining an instance that would be a total waste repeated for
each customer. You would be much better off with one database per customer
within an instance. In most cases, you would be even better off with a single
database for multiple customers, and that's what a DBMS is designed for.
Thirdly, forget about the scalability and feasibility for now. The cost
factor would be prohibitive if you have many customers since you would have
to keep adding more servers.
All that said, one instance per customer can be easily justified if we are
talking about each customer being sufficient large in its data volume,
resource consumption, importance in isolation, and so on. Say if you only
have a few cusotmer accounts and each is a Fortune 500 company and each is
generating a lot of revenue for you. Heck, you may even need to dedicate a
cluster for each customer.
So we need more detail to be specific.
Linchi
"Mike9900" wrote:
> Hello,
> We want to manage customers over a VPN, such that a customer logs into the
> software, the accounting software (smart client), and checks his/her info.
> We at the server want to manage the accounts with the software.
> Is creating different instances for each customer a good way, or we should
> create one instance and add their databases in the same instance? What are
> the pros and cons?
> I appreciate your comments in advance.
> --
> Mike|||Hi Linchi,
Thanks for help.
We use SQL Server 2005. It is not possible to keep all customers in one db.
But I think each instance for a customer is a bad idea because the instance
would allocate its own memory.
But we are concerned about customer security. If all customers use the same
instance, we must keep the sa password secret. Also, our software does not
use SQL server user id to connect, instead it has a master user id in SQL
server, and the user connects to the app, then the app uses the master
password to check the user id with the db. So, if we create different user
id for each customer the app connects to SQL server with the same master user
id. So the connection string have the same userid/password, but will have
different computer name. I am wondering if this may decrease the performance.
Our customer are small and do not have huge transactions.
Mike
"Linchi Shea" wrote:
> Creating one instance per customer is almost always a bad idea. First of all,
> a server can only support that many instances (the maximum number of SQL2000
> instances supported on a computer is 16). Secondly, there is significant
> overhead in maintaining an instance that would be a total waste repeated for
> each customer. You would be much better off with one database per customer
> within an instance. In most cases, you would be even better off with a single
> database for multiple customers, and that's what a DBMS is designed for.
> Thirdly, forget about the scalability and feasibility for now. The cost
> factor would be prohibitive if you have many customers since you would have
> to keep adding more servers.
> All that said, one instance per customer can be easily justified if we are
> talking about each customer being sufficient large in its data volume,
> resource consumption, importance in isolation, and so on. Say if you only
> have a few cusotmer accounts and each is a Fortune 500 company and each is
> generating a lot of revenue for you. Heck, you may even need to dedicate a
> cluster for each customer.
> So we need more detail to be specific.
> Linchi
> "Mike9900" wrote:
> > Hello,
> >
> > We want to manage customers over a VPN, such that a customer logs into the
> > software, the accounting software (smart client), and checks his/her info.
> > We at the server want to manage the accounts with the software.
> >
> > Is creating different instances for each customer a good way, or we should
> > create one instance and add their databases in the same instance? What are
> > the pros and cons?
> >
> > I appreciate your comments in advance.
> >
> > --
> > Mike|||I assume you guys are the author of this software. If so, I don't see why it
is NOT possible to use multiple databases instead of multiple instances.
Fixing the overall architecture later on will be much more difficult than
fixing how the logins are handled now.
Linchi
"Mike9900" wrote:
> Hi Linchi,
> Thanks for help.
> We use SQL Server 2005. It is not possible to keep all customers in one db.
> But I think each instance for a customer is a bad idea because the instance
> would allocate its own memory.
> But we are concerned about customer security. If all customers use the same
> instance, we must keep the sa password secret. Also, our software does not
> use SQL server user id to connect, instead it has a master user id in SQL
> server, and the user connects to the app, then the app uses the master
> password to check the user id with the db. So, if we create different user
> id for each customer the app connects to SQL server with the same master user
> id. So the connection string have the same userid/password, but will have
> different computer name. I am wondering if this may decrease the performance.
> Our customer are small and do not have huge transactions.
>
> --
> Mike
>
> "Linchi Shea" wrote:
> > Creating one instance per customer is almost always a bad idea. First of all,
> > a server can only support that many instances (the maximum number of SQL2000
> > instances supported on a computer is 16). Secondly, there is significant
> > overhead in maintaining an instance that would be a total waste repeated for
> > each customer. You would be much better off with one database per customer
> > within an instance. In most cases, you would be even better off with a single
> > database for multiple customers, and that's what a DBMS is designed for.
> > Thirdly, forget about the scalability and feasibility for now. The cost
> > factor would be prohibitive if you have many customers since you would have
> > to keep adding more servers.
> >
> > All that said, one instance per customer can be easily justified if we are
> > talking about each customer being sufficient large in its data volume,
> > resource consumption, importance in isolation, and so on. Say if you only
> > have a few cusotmer accounts and each is a Fortune 500 company and each is
> > generating a lot of revenue for you. Heck, you may even need to dedicate a
> > cluster for each customer.
> >
> > So we need more detail to be specific.
> >
> > Linchi
> >
> > "Mike9900" wrote:
> >
> > > Hello,
> > >
> > > We want to manage customers over a VPN, such that a customer logs into the
> > > software, the accounting software (smart client), and checks his/her info.
> > > We at the server want to manage the accounts with the software.
> > >
> > > Is creating different instances for each customer a good way, or we should
> > > create one instance and add their databases in the same instance? What are
> > > the pros and cons?
> > >
> > > I appreciate your comments in advance.
> > >
> > > --
> > > Mike|||Thanks.
So my question is if the connection string differs by the computer name, in
Workstation ID of conn string, would the sql server think of it as a
different connection string in the pool.
--
Mike
"Linchi Shea" wrote:
> I assume you guys are the author of this software. If so, I don't see why it
> is NOT possible to use multiple databases instead of multiple instances.
> Fixing the overall architecture later on will be much more difficult than
> fixing how the logins are handled now.
> Linchi
> "Mike9900" wrote:
> > Hi Linchi,
> >
> > Thanks for help.
> >
> > We use SQL Server 2005. It is not possible to keep all customers in one db.
> > But I think each instance for a customer is a bad idea because the instance
> > would allocate its own memory.
> >
> > But we are concerned about customer security. If all customers use the same
> > instance, we must keep the sa password secret. Also, our software does not
> > use SQL server user id to connect, instead it has a master user id in SQL
> > server, and the user connects to the app, then the app uses the master
> > password to check the user id with the db. So, if we create different user
> > id for each customer the app connects to SQL server with the same master user
> > id. So the connection string have the same userid/password, but will have
> > different computer name. I am wondering if this may decrease the performance.
> >
> > Our customer are small and do not have huge transactions.
> >
> >
> >
> > --
> > Mike
> >
> >
> > "Linchi Shea" wrote:
> >
> > > Creating one instance per customer is almost always a bad idea. First of all,
> > > a server can only support that many instances (the maximum number of SQL2000
> > > instances supported on a computer is 16). Secondly, there is significant
> > > overhead in maintaining an instance that would be a total waste repeated for
> > > each customer. You would be much better off with one database per customer
> > > within an instance. In most cases, you would be even better off with a single
> > > database for multiple customers, and that's what a DBMS is designed for.
> > > Thirdly, forget about the scalability and feasibility for now. The cost
> > > factor would be prohibitive if you have many customers since you would have
> > > to keep adding more servers.
> > >
> > > All that said, one instance per customer can be easily justified if we are
> > > talking about each customer being sufficient large in its data volume,
> > > resource consumption, importance in isolation, and so on. Say if you only
> > > have a few cusotmer accounts and each is a Fortune 500 company and each is
> > > generating a lot of revenue for you. Heck, you may even need to dedicate a
> > > cluster for each customer.
> > >
> > > So we need more detail to be specific.
> > >
> > > Linchi
> > >
> > > "Mike9900" wrote:
> > >
> > > > Hello,
> > > >
> > > > We want to manage customers over a VPN, such that a customer logs into the
> > > > software, the accounting software (smart client), and checks his/her info.
> > > > We at the server want to manage the accounts with the software.
> > > >
> > > > Is creating different instances for each customer a good way, or we should
> > > > create one instance and add their databases in the same instance? What are
> > > > the pros and cons?
> > > >
> > > > I appreciate your comments in advance.
> > > >
> > > > --
> > > > Mike|||It is never a good idea to give out the sa password regardless of how you
implement. Hopefully your app is not running under sa rights.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Mike9900" <Mike9900@.discussions.microsoft.com> wrote in message
news:17B8417C-A9BD-44DD-8F10-68BCE2735FFC@.microsoft.com...
> Hi Linchi,
> Thanks for help.
> We use SQL Server 2005. It is not possible to keep all customers in one
> db.
> But I think each instance for a customer is a bad idea because the
> instance
> would allocate its own memory.
> But we are concerned about customer security. If all customers use the
> same
> instance, we must keep the sa password secret. Also, our software does
> not
> use SQL server user id to connect, instead it has a master user id in SQL
> server, and the user connects to the app, then the app uses the master
> password to check the user id with the db. So, if we create different
> user
> id for each customer the app connects to SQL server with the same master
> user
> id. So the connection string have the same userid/password, but will have
> different computer name. I am wondering if this may decrease the
> performance.
> Our customer are small and do not have huge transactions.
>
> --
> Mike
>
> "Linchi Shea" wrote:
>> Creating one instance per customer is almost always a bad idea. First of
>> all,
>> a server can only support that many instances (the maximum number of
>> SQL2000
>> instances supported on a computer is 16). Secondly, there is significant
>> overhead in maintaining an instance that would be a total waste repeated
>> for
>> each customer. You would be much better off with one database per
>> customer
>> within an instance. In most cases, you would be even better off with a
>> single
>> database for multiple customers, and that's what a DBMS is designed for.
>> Thirdly, forget about the scalability and feasibility for now. The cost
>> factor would be prohibitive if you have many customers since you would
>> have
>> to keep adding more servers.
>> All that said, one instance per customer can be easily justified if we
>> are
>> talking about each customer being sufficient large in its data volume,
>> resource consumption, importance in isolation, and so on. Say if you only
>> have a few cusotmer accounts and each is a Fortune 500 company and each
>> is
>> generating a lot of revenue for you. Heck, you may even need to dedicate
>> a
>> cluster for each customer.
>> So we need more detail to be specific.
>> Linchi
>> "Mike9900" wrote:
>> > Hello,
>> >
>> > We want to manage customers over a VPN, such that a customer logs into
>> > the
>> > software, the accounting software (smart client), and checks his/her
>> > info.
>> > We at the server want to manage the accounts with the software.
>> >
>> > Is creating different instances for each customer a good way, or we
>> > should
>> > create one instance and add their databases in the same instance? What
>> > are
>> > the pros and cons?
>> >
>> > I appreciate your comments in advance.
>> >
>> > --
>> > Mike|||No, our app create a different user id which has data reader/Writer rights
only and is not a db admin.
But the security for the app is defined and enforced by the application, and
not the sql server. The reason is that the app does not use stored procs,
and uses many things for the security not just security on table, such as the
right of the user to sell below certain price.
--
Mike
"Andrew J. Kelly" wrote:
> It is never a good idea to give out the sa password regardless of how you
> implement. Hopefully your app is not running under sa rights.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Mike9900" <Mike9900@.discussions.microsoft.com> wrote in message
> news:17B8417C-A9BD-44DD-8F10-68BCE2735FFC@.microsoft.com...
> > Hi Linchi,
> >
> > Thanks for help.
> >
> > We use SQL Server 2005. It is not possible to keep all customers in one
> > db.
> > But I think each instance for a customer is a bad idea because the
> > instance
> > would allocate its own memory.
> >
> > But we are concerned about customer security. If all customers use the
> > same
> > instance, we must keep the sa password secret. Also, our software does
> > not
> > use SQL server user id to connect, instead it has a master user id in SQL
> > server, and the user connects to the app, then the app uses the master
> > password to check the user id with the db. So, if we create different
> > user
> > id for each customer the app connects to SQL server with the same master
> > user
> > id. So the connection string have the same userid/password, but will have
> > different computer name. I am wondering if this may decrease the
> > performance.
> >
> > Our customer are small and do not have huge transactions.
> >
> >
> >
> > --
> > Mike
> >
> >
> > "Linchi Shea" wrote:
> >
> >> Creating one instance per customer is almost always a bad idea. First of
> >> all,
> >> a server can only support that many instances (the maximum number of
> >> SQL2000
> >> instances supported on a computer is 16). Secondly, there is significant
> >> overhead in maintaining an instance that would be a total waste repeated
> >> for
> >> each customer. You would be much better off with one database per
> >> customer
> >> within an instance. In most cases, you would be even better off with a
> >> single
> >> database for multiple customers, and that's what a DBMS is designed for.
> >> Thirdly, forget about the scalability and feasibility for now. The cost
> >> factor would be prohibitive if you have many customers since you would
> >> have
> >> to keep adding more servers.
> >>
> >> All that said, one instance per customer can be easily justified if we
> >> are
> >> talking about each customer being sufficient large in its data volume,
> >> resource consumption, importance in isolation, and so on. Say if you only
> >> have a few cusotmer accounts and each is a Fortune 500 company and each
> >> is
> >> generating a lot of revenue for you. Heck, you may even need to dedicate
> >> a
> >> cluster for each customer.
> >>
> >> So we need more detail to be specific.
> >>
> >> Linchi
> >>
> >> "Mike9900" wrote:
> >>
> >> > Hello,
> >> >
> >> > We want to manage customers over a VPN, such that a customer logs into
> >> > the
> >> > software, the accounting software (smart client), and checks his/her
> >> > info.
> >> > We at the server want to manage the accounts with the software.
> >> >
> >> > Is creating different instances for each customer a good way, or we
> >> > should
> >> > create one instance and add their databases in the same instance? What
> >> > are
> >> > the pros and cons?
> >> >
> >> > I appreciate your comments in advance.
> >> >
> >> > --
> >> > Mike
>|||To quote MS documentation: "Each connection pool is associated with a
distinct connection string. When a new connection is opened, if the
connection string is not an exact match to an existing pool, a new pool is
created."
Linchi
"Mike9900" wrote:
> Thanks.
> So my question is if the connection string differs by the computer name, in
> Workstation ID of conn string, would the sql server think of it as a
> different connection string in the pool.
> --
> Mike
>
> "Linchi Shea" wrote:
> > I assume you guys are the author of this software. If so, I don't see why it
> > is NOT possible to use multiple databases instead of multiple instances.
> > Fixing the overall architecture later on will be much more difficult than
> > fixing how the logins are handled now.
> >
> > Linchi
> >
> > "Mike9900" wrote:
> >
> > > Hi Linchi,
> > >
> > > Thanks for help.
> > >
> > > We use SQL Server 2005. It is not possible to keep all customers in one db.
> > > But I think each instance for a customer is a bad idea because the instance
> > > would allocate its own memory.
> > >
> > > But we are concerned about customer security. If all customers use the same
> > > instance, we must keep the sa password secret. Also, our software does not
> > > use SQL server user id to connect, instead it has a master user id in SQL
> > > server, and the user connects to the app, then the app uses the master
> > > password to check the user id with the db. So, if we create different user
> > > id for each customer the app connects to SQL server with the same master user
> > > id. So the connection string have the same userid/password, but will have
> > > different computer name. I am wondering if this may decrease the performance.
> > >
> > > Our customer are small and do not have huge transactions.
> > >
> > >
> > >
> > > --
> > > Mike
> > >
> > >
> > > "Linchi Shea" wrote:
> > >
> > > > Creating one instance per customer is almost always a bad idea. First of all,
> > > > a server can only support that many instances (the maximum number of SQL2000
> > > > instances supported on a computer is 16). Secondly, there is significant
> > > > overhead in maintaining an instance that would be a total waste repeated for
> > > > each customer. You would be much better off with one database per customer
> > > > within an instance. In most cases, you would be even better off with a single
> > > > database for multiple customers, and that's what a DBMS is designed for.
> > > > Thirdly, forget about the scalability and feasibility for now. The cost
> > > > factor would be prohibitive if you have many customers since you would have
> > > > to keep adding more servers.
> > > >
> > > > All that said, one instance per customer can be easily justified if we are
> > > > talking about each customer being sufficient large in its data volume,
> > > > resource consumption, importance in isolation, and so on. Say if you only
> > > > have a few cusotmer accounts and each is a Fortune 500 company and each is
> > > > generating a lot of revenue for you. Heck, you may even need to dedicate a
> > > > cluster for each customer.
> > > >
> > > > So we need more detail to be specific.
> > > >
> > > > Linchi
> > > >
> > > > "Mike9900" wrote:
> > > >
> > > > > Hello,
> > > > >
> > > > > We want to manage customers over a VPN, such that a customer logs into the
> > > > > software, the accounting software (smart client), and checks his/her info.
> > > > > We at the server want to manage the accounts with the software.
> > > > >
> > > > > Is creating different instances for each customer a good way, or we should
> > > > > create one instance and add their databases in the same instance? What are
> > > > > the pros and cons?
> > > > >
> > > > > I appreciate your comments in advance.
> > > > >
> > > > > --
> > > > > Mike|||Mike9900 wrote on Sat, 13 Oct 2007 11:12:01 -0700:
> No, our app create a different user id which has data reader/Writer
> rights only and is not a db admin.
> But the security for the app is defined and enforced by the
> application, and not the sql server. The reason is that the app does
> not use stored procs, and uses many things for the security not just
> security on table, such as the right of the user to sell below certain
> price.
> -- Mike
Why not use a combination of SQL security (so clients can only access their
own database), and application level settings.
Dan|||Server app and client app can access their own dbs, but it does not limit the
client app to access certain tables within the db; which means the server app
does not provide any access.
But in the futures we may limit the client app by a server app.
--
Mike
"Daniel Crichton" wrote:
> Mike9900 wrote on Sat, 13 Oct 2007 11:12:01 -0700:
> > No, our app create a different user id which has data reader/Writer
> > rights only and is not a db admin.
> > But the security for the app is defined and enforced by the
> > application, and not the sql server. The reason is that the app does
> > not use stored procs, and uses many things for the security not just
> > security on table, such as the right of the user to sell below certain
> > price.
> > -- Mike
> Why not use a combination of SQL security (so clients can only access their
> own database), and application level settings.
> Dan
>
>