Inserts to tables as one or two users.

SQL 7.0 SP4
Background: C++ program accesses SQL via ODBC doing
massive inserts to two different tables. The data goes
either to one table or the other, but not both.
Problem: Is it more efficient to have the program access
the database as one or two SQL users?
1. user DOG inserts to table XXX "and" table YYY.
or
2. user DOG inserts to table XXX,
user CAT inserts to table YYY?
Thanks for your help,
DonUnless you need to load them in parallel there isn't any reason to use two
users. Adding connections has a slight overhead that you probably don't
need for this type operation.
--
Andrew J. Kelly
SQL Server MVP
"Don" <ddachner@.yahoo.com> wrote in message
news:0bf401c38126$d4174120$a001280a@.phx.gbl...
> SQL 7.0 SP4
> Background: C++ program accesses SQL via ODBC doing
> massive inserts to two different tables. The data goes
> either to one table or the other, but not both.
> Problem: Is it more efficient to have the program access
> the database as one or two SQL users?
> 1. user DOG inserts to table XXX "and" table YYY.
> or
> 2. user DOG inserts to table XXX,
> user CAT inserts to table YYY?
> Thanks for your help,
> Don|||no, it wouldn't make any difference in performance, it
will only increase work for you to manage permissions to
two users.
Also, consider using stored procedures rather than direct
insert statements. this will give slight performance gain,
and better management of sql code.
>--Original Message--
>SQL 7.0 SP4
>Background: C++ program accesses SQL via ODBC doing
>massive inserts to two different tables. The data goes
>either to one table or the other, but not both.
>Problem: Is it more efficient to have the program access
>the database as one or two SQL users?
>1. user DOG inserts to table XXX "and" table YYY.
>or
>2. user DOG inserts to table XXX,
> user CAT inserts to table YYY?
>Thanks for your help,
>Don
>.
>

insertion into sql server table failed

Hi ,
At SQL server's end , i have put my windows id as a readonly for that
database.
however from MS Access when i specify the ODBC i used the sa id & pwd
from Ms Access , i linked to the SQL server db using ODBC connection ,when i
tried to insert some data into SQL server i was not allowed
why is that so as i was using sa pwd from the odbc connection
Could anyone kindly advise ?
tks & rdgsDon't think it has much to do with the security context -
more likely related to how the tables are linked or an ODBC
issue depending on the details of the error you receive.
Make sure you have defined a primary key or unique
identifier for the tables you are linking.
-Sue
On Tue, 6 Sep 2005 18:10:03 -0700, "maxzsim"
<maxzsim@.discussions.microsoft.com> wrote:
>Hi ,
> At SQL server's end , i have put my windows id as a readonly for that
>database.
> however from MS Access when i specify the ODBC i used the sa id & pwd
>from Ms Access , i linked to the SQL server db using ODBC connection ,when i
>tried to insert some data into SQL server i was not allowed
> why is that so as i was using sa pwd from the odbc connection
> Could anyone kindly advise ?
>tks & rdgs|||Hi
From the local table in Ms Access ,i have added a primary and tried
inserting it into the tbl in SQL Server in Production but i have got a key
violation error
However, when i insert into a local SQL Server it's ok
tks & rdgs
"Sue Hoegemeier" wrote:
> Don't think it has much to do with the security context -
> more likely related to how the tables are linked or an ODBC
> issue depending on the details of the error you receive.
> Make sure you have defined a primary key or unique
> identifier for the tables you are linking.
> -Sue
> On Tue, 6 Sep 2005 18:10:03 -0700, "maxzsim"
> <maxzsim@.discussions.microsoft.com> wrote:
> >Hi ,
> >
> > At SQL server's end , i have put my windows id as a readonly for that
> >database.
> >
> > however from MS Access when i specify the ODBC i used the sa id & pwd
> >
> >from Ms Access , i linked to the SQL server db using ODBC connection ,when i
> >tried to insert some data into SQL server i was not allowed
> >
> > why is that so as i was using sa pwd from the odbc connection
> >
> > Could anyone kindly advise ?
> >
> >tks & rdgs
>

insertion into sql server table failed

Hi ,
At SQL server's end , i have put my windows id as a readonly for that
database.
however from MS Access when i specify the ODBC i used the sa id & pwd
from Ms Access , i linked to the SQL server db using ODBC connection ,when i
tried to insert some data into SQL server i was not allowed
why is that so as i was using sa pwd from the odbc connection
Could anyone kindly advise ?
tks & rdgsDon't think it has much to do with the security context -
more likely related to how the tables are linked or an ODBC
issue depending on the details of the error you receive.
Make sure you have defined a primary key or unique
identifier for the tables you are linking.
-Sue
On Tue, 6 Sep 2005 18:10:03 -0700, "maxzsim"
<maxzsim@.discussions.microsoft.com> wrote:

>Hi ,
> At SQL server's end , i have put my windows id as a readonly for that
>database.
> however from MS Access when i specify the ODBC i used the sa id & pwd
>from Ms Access , i linked to the SQL server db using ODBC connection ,when
i
>tried to insert some data into SQL server i was not allowed
> why is that so as i was using sa pwd from the odbc connection
> Could anyone kindly advise ?
>tks & rdgs|||Hi
From the local table in Ms Access ,i have added a primary and tried
inserting it into the tbl in SQL Server in Production but i have got a key
violation error
However, when i insert into a local SQL Server it's ok
tks & rdgs
"Sue Hoegemeier" wrote:

> Don't think it has much to do with the security context -
> more likely related to how the tables are linked or an ODBC
> issue depending on the details of the error you receive.
> Make sure you have defined a primary key or unique
> identifier for the tables you are linking.
> -Sue
> On Tue, 6 Sep 2005 18:10:03 -0700, "maxzsim"
> <maxzsim@.discussions.microsoft.com> wrote:
>
>

insertion into sql server table failed

Hi ,
At SQL server's end , i have put my windows id as a readonly for that
database.
however from MS Access when i specify the ODBC i used the sa id & pwd
from Ms Access , i linked to the SQL server db using ODBC connection ,when i
tried to insert some data into SQL server i was not allowed
why is that so as i was using sa pwd from the odbc connection
Could anyone kindly advise ?
tks & rdgs
Don't think it has much to do with the security context -
more likely related to how the tables are linked or an ODBC
issue depending on the details of the error you receive.
Make sure you have defined a primary key or unique
identifier for the tables you are linking.
-Sue
On Tue, 6 Sep 2005 18:10:03 -0700, "maxzsim"
<maxzsim@.discussions.microsoft.com> wrote:

>Hi ,
> At SQL server's end , i have put my windows id as a readonly for that
>database.
> however from MS Access when i specify the ODBC i used the sa id & pwd
>from Ms Access , i linked to the SQL server db using ODBC connection ,when i
>tried to insert some data into SQL server i was not allowed
> why is that so as i was using sa pwd from the odbc connection
> Could anyone kindly advise ?
>tks & rdgs
|||Hi
From the local table in Ms Access ,i have added a primary and tried
inserting it into the tbl in SQL Server in Production but i have got a key
violation error
However, when i insert into a local SQL Server it's ok
tks & rdgs
"Sue Hoegemeier" wrote:

> Don't think it has much to do with the security context -
> more likely related to how the tables are linked or an ODBC
> issue depending on the details of the error you receive.
> Make sure you have defined a primary key or unique
> identifier for the tables you are linking.
> -Sue
> On Tue, 6 Sep 2005 18:10:03 -0700, "maxzsim"
> <maxzsim@.discussions.microsoft.com> wrote:
>
>