Question regarding storing session with set context_info

Currently our need is to track the data that is changed by the user and record it as a part of the Audit Log.

We have accomplished this need ny making use ofset context_infoand also by passing the UserId from all the Layers and finally log the user name using the set context_info in the stored proc.

What we thought of was using the trigger for each update,delete records for the table.So by setting the context in teh stored proc we could access the UserId in the trigger and able to record the changes made by Tagged userId.

What we want to make sure before making this as our permananet solution is

(1) Reliability - In a web application (N Tier) how would the context not get out of scope.

2) any Performannce related issues, you guys can think of.

3) This Context used in the database -- would that be the part of the connection used from the UI, meaning the if a request is made from UI to the Database . would the context be alive till the connection doesn't go back to the connection pool.

Thanks

Sweety.

No answers yet.

Any idea wether this approach would be good or not.

Need your Input

Thanks

Question regarding stored procs

Hello,

It might be a basic question, but there it goes:

I have a store procedure that first checks if the record exists and, if
it doesn't, inserts the new record.

Is the scenario below possible?

(thread1) USER1 -> check if record "abc" exists
(thread2) USER2 -> check if record "abc" exists
(thread1) USER1 -> "abc" doesn't exist
(thread2) USER2 -> "abc" doesn't exist
(thread1) USER1 -> add "abc" as new record
(thread2) USER2 -> add "abc as new record (OPS, this is an error,
because "abc" already exists, it was just inserted by USER1)

I am wondering if this kind of concurrent, multi-threaded access
happens with stored procedures.

If yes, can I execute a procedure from start to finish without any
other simultaneous procedure interrupting?

I appreciate any information about this.

Leonardo.Hi Leonardo,

The answer to your first question is yes; if you do not establish a
transaction (with the appropriate locking mechanism), then you can have
concurrency issues. However, you can establish a transaction and set
the isolation level to a higher restriction to avoid this scenario.

Check the SQL Server Books OnLine for transactions and transaction
isolation levels; that should get you started.

HTH,
Stu|||(leodippolito@.gmail.com) writes:
> It might be a basic question, but there it goes:
> I have a store procedure that first checks if the record exists and, if
> it doesn't, inserts the new record.
> Is the scenario below possible?
> (thread1) USER1 -> check if record "abc" exists
> (thread2) USER2 -> check if record "abc" exists
> (thread1) USER1 -> "abc" doesn't exist
> (thread2) USER2 -> "abc" doesn't exist
> (thread1) USER1 -> add "abc" as new record
> (thread2) USER2 -> add "abc as new record (OPS, this is an error,
> because "abc" already exists, it was just inserted by USER1)

Yes.

> I am wondering if this kind of concurrent, multi-threaded access
> happens with stored procedures.
> If yes, can I execute a procedure from start to finish without any
> other simultaneous procedure interrupting?

You would need to enclose the IF EXISTS + SELECT in a transaction.
Furthermore, you must make sure that the isolation level is serializable.
The defuault isolation level in SQL Server is READ COMMITTED, which
means that once the EXISTS check has passed, locks are released.

The best solution is to add the table hint "WITH (UPDLOCK)" in the
EXISTS query. This would make USER2 in this example to be blocked
already at this point. If you just use SET TRANSACTION ISOLATION
LEVEL, the two processes will deadlock.

One way to test issues like this, is to insert a WAITFOR in the code,
and then run from separate windows in Query Analyzer.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland, why do you say " If you just use SET TRANSACTION ISOLATION
LEVEL, the two processes will deadlock." ?

Suppose T1 executes with SERIALIZABLE and T2 tries to execute the same
proc at the same time ... Well, as far as I understand, T2 will wait
for T1 to finish and then go on.. right? Why exactly is " WITH
(UPDLOCK) " necessary?

If it's not a problem for you, could you provide me a safe (deadlock
free) example of insert procedure that would check the existance of the
record before inserting?

Thanks for all.

Leonardo.|||(leodippolito@.gmail.com) writes:
> Erland, why do you say " If you just use SET TRANSACTION ISOLATION
> LEVEL, the two processes will deadlock." ?
> Suppose T1 executes with SERIALIZABLE and T2 tries to execute the same
> proc at the same time ... Well, as far as I understand, T2 will wait
> for T1 to finish and then go on.. right? Why exactly is " WITH
> (UPDLOCK) " necessary?

Because with plain serializable this happens:

T1 performs NOT EXISTS check, and retains a shared lock
T2 performs NOT EXISTS check, and retains a shared lock
T1 tries to insert, but is blocked by T2
T2 tries to insert, but is blocked by T1
=> Deadlock

UPDLOCK is a shared lock, so it does not block other readers. However,
only one process have an UPDLOCK on a resource, so T2 would be blocked
until T1 has committed. And when T2 goes ahead, T2 finds that the rows
is already there, and does not try to insert.

> If it's not a problem for you, could you provide me a safe (deadlock
> free) example of insert procedure that would check the existance of the
> record before inserting?

Hey, that's what I leave as an exercise to the reader. :-) Seriously,
I encourage you to try these things by running from separate windows
in Query Analyzer, and try the various possibilities, to see what deadlocks,
what gives errors and what works smoothly. This is a good lab exercise
to get an understanding of things. What is problematic is to emulate
the concurrency, but some WAITFOR statements are usually good enough.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

question on upper bound primary key of type int

I have several tables in a deployed database in which the primary key is of type int, and autoincrements by 1 each time a record is added. My question is, since ints are 32-bit, what happens when its value reaches 4,294,967,296? I know that seems like an extrememly large amount of records, but when we imported the data into the database it started at key value 1,000,000. I don't know how to make it use lower numbers which are currently not being used (numbers below 1,000,000), and I am worried I will have problems when I reach the upper bound. What kind of problems could this cause? Should I change the primary key's type?

Thanks!

the upper bound is somewhere around 2.1 bill. Yes when you reach that limit your application will fail. You cannot insert any new data. You could put some alert in place to identify or predict when the storm is coming. you could create a job that gets the MAX(ID) every week and you can monitor the growth of the table. Once the ID reaches closer to 2 bil you can increase your frequency of monitoring. To fix it, you need modify the column and change it to BigInt. Please do not even bother to try ALTER TABLE...ALTER COLUMN...the server will hang.|||Ok, I'll just modify the primary key type. Thanks for the response.sql

question on temporary tables

I have a stored procedure that creates a temporary table, populates it
with one record, and then returns that record to an ASP. I've read
that by prefixing the the tablename with a #, that only the connection
that created it can access it.
An IIS server will be making the connection and uses the same
connection string for all users. Even though the connection string is
the same, and the temporary table name is the same, will the temporary
table created only be available to the specific connection that created
it? I'm concerned that if multiple users make a request to that stored
procedure, they may get someone else's data.
thanks,
JoeIf you are populating the table with just one row, then use a table variable
instead.
AMB
"joseph.fanelli@.vba.va.gov" wrote:

> I have a stored procedure that creates a temporary table, populates it
> with one record, and then returns that record to an ASP. I've read
> that by prefixing the the tablename with a #, that only the connection
> that created it can access it.
> An IIS server will be making the connection and uses the same
> connection string for all users. Even though the connection string is
> the same, and the temporary table name is the same, will the temporary
> table created only be available to the specific connection that created
> it? I'm concerned that if multiple users make a request to that stored
> procedure, they may get someone else's data.
> thanks,
> Joe
>|||If you are only populating with one row then chances are you can do this
without a temp table. But to answer your question no others can not see
this. But you might want to do as Alejandro says and use a table variable
instead.
Andrew J. Kelly SQL MVP
<joseph.fanelli@.vba.va.gov> wrote in message
news:1113844980.325249.265010@.l41g2000cwc.googlegroups.com...
>I have a stored procedure that creates a temporary table, populates it
> with one record, and then returns that record to an ASP. I've read
> that by prefixing the the tablename with a #, that only the connection
> that created it can access it.
> An IIS server will be making the connection and uses the same
> connection string for all users. Even though the connection string is
> the same, and the temporary table name is the same, will the temporary
> table created only be available to the specific connection that created
> it? I'm concerned that if multiple users make a request to that stored
> procedure, they may get someone else's data.
> thanks,
> Joe
>|||Why even use a temporary table?
Declare variables, populate them, then return a recorset:
select @.variable1 as ..., @.variable2 as ... @.variable3 as ... etc.
<joseph.fanelli@.vba.va.gov> wrote in message
news:1113844980.325249.265010@.l41g2000cwc.googlegroups.com...
>I have a stored procedure that creates a temporary table, populates it
> with one record, and then returns that record to an ASP. I've read
> that by prefixing the the tablename with a #, that only the connection
> that created it can access it.
> An IIS server will be making the connection and uses the same
> connection string for all users. Even though the connection string is
> the same, and the temporary table name is the same, will the temporary
> table created only be available to the specific connection that created
> it? I'm concerned that if multiple users make a request to that stored
> procedure, they may get someone else's data.
> thanks,
> Joe
>|||Andrew,
I did not think about it, but yes, better to use an output parameter. What
about a connection taken from the pool, if the sp does not drop the temporar
y
table explicitly, and uses this code to create it:
if object_id('tempdb..#temptbl') is null
create table #temptbl ...
...
then next time the sp is executed using the same connection, a row inserted
by previous execution can be selected, correct?
AMB
"Andrew J. Kelly" wrote:

> If you are only populating with one row then chances are you can do this
> without a temp table. But to answer your question no others can not see
> this. But you might want to do as Alejandro says and use a table variable
> instead.
> --
> Andrew J. Kelly SQL MVP
>
> <joseph.fanelli@.vba.va.gov> wrote in message
> news:1113844980.325249.265010@.l41g2000cwc.googlegroups.com...
>
>|||If they are using connection pooling and follow the proper procedures a
sp_resetconnection is called that will clean up any such things and put the
connection settings back to the proper settings. Normally a temp table
created in a sp will go out of scope when the sp is completed as well. It
is only if they created the temp table outside of a sp that it will hang
around.
Andrew J. Kelly SQL MVP
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:8C7B0F8B-12B7-49F0-818B-F39C2415C8FE@.microsoft.com...
> Andrew,
> I did not think about it, but yes, better to use an output parameter. What
> about a connection taken from the pool, if the sp does not drop the
> temporary
> table explicitly, and uses this code to create it:
> if object_id('tempdb..#temptbl') is null
> create table #temptbl ...
> ...
> then next time the sp is executed using the same connection, a row
> inserted
> by previous execution can be selected, correct?
>
> AMB
> "Andrew J. Kelly" wrote:
>|||Andrew,
You are right, the temporary table goes out of scope when the sp has
finished. I wonder what kind of cleaning is done by sp_resetconnection, I ca
n
not find anything about it in the BOL.
Thanks,
AMB
"Andrew J. Kelly" wrote:

> If they are using connection pooling and follow the proper procedures a
> sp_resetconnection is called that will clean up any such things and put th
e
> connection settings back to the proper settings. Normally a temp table
> created in a sp will go out of scope when the sp is completed as well. It
> is only if they created the temp table outside of a sp that it will hang
> around.
> --
> Andrew J. Kelly SQL MVP
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in messag
e
> news:8C7B0F8B-12B7-49F0-818B-F39C2415C8FE@.microsoft.com...
>
>

question on temporary tables

I have a stored procedure that creates a temporary table, populates it
with one record, and then returns that record to an ASP. I've read
that by prefixing the the tablename with a #, that only the connection
that created it can access it.
An IIS server will be making the connection and uses the same
connection string for all users. Even though the connection string is
the same, and the temporary table name is the same, will the temporary
table created only be available to the specific connection that created
it? I'm concerned that if multiple users make a request to that stored
procedure, they may get someone else's data.
thanks,
Joe
The #tablename temp table exists only for the connection that calls the
proc. You are safe to call the same proc and the same table name as many
times as necessary without fear. Do not however use ##{tablename}. This will
cause a global temp table to be created and is referenceable by other
connections until the connection that created it is dropped.
--Rick Butler
"joseph.fanelli@.vba.va.gov" wrote:

> I have a stored procedure that creates a temporary table, populates it
> with one record, and then returns that record to an ASP. I've read
> that by prefixing the the tablename with a #, that only the connection
> that created it can access it.
> An IIS server will be making the connection and uses the same
> connection string for all users. Even though the connection string is
> the same, and the temporary table name is the same, will the temporary
> table created only be available to the specific connection that created
> it? I'm concerned that if multiple users make a request to that stored
> procedure, they may get someone else's data.
> thanks,
> Joe
>
|||Why not just select that one row and avoid creating a temp table? Improper
use of temporary objects in stored procedures can cause recompiles and
affect SQL performance. See
http://support.microsoft.com/default...b;en-us;243586 for details.
Adrian
"Rick" <Rick@.discussions.microsoft.com> wrote in message
news:BC656DA7-7BB2-4527-94D5-BCD53D702DE4@.microsoft.com...[vbcol=seagreen]
> The #tablename temp table exists only for the connection that calls the
> proc. You are safe to call the same proc and the same table name as many
> times as necessary without fear. Do not however use ##{tablename}. This
> will
> cause a global temp table to be created and is referenceable by other
> connections until the connection that created it is dropped.
> --Rick Butler
> "joseph.fanelli@.vba.va.gov" wrote:

question on temporary tables

I have a stored procedure that creates a temporary table, populates it
with one record, and then returns that record to an ASP. I've read
that by prefixing the the tablename with a #, that only the connection
that created it can access it.
An IIS server will be making the connection and uses the same
connection string for all users. Even though the connection string is
the same, and the temporary table name is the same, will the temporary
table created only be available to the specific connection that created
it? I'm concerned that if multiple users make a request to that stored
procedure, they may get someone else's data.
thanks,
Joe
Connections are always separate from each other, regardless of whether the
client connects using the same login information or not. Your temporary
tables will not collide or be visible to other connections.
Geoff N. Hiten
Microsoft SQL Server MVP
<joseph.fanelli@.vba.va.gov> wrote in message
news:1113844860.949837.190280@.z14g2000cwz.googlegr oups.com...
>I have a stored procedure that creates a temporary table, populates it
> with one record, and then returns that record to an ASP. I've read
> that by prefixing the the tablename with a #, that only the connection
> that created it can access it.
> An IIS server will be making the connection and uses the same
> connection string for all users. Even though the connection string is
> the same, and the temporary table name is the same, will the temporary
> table created only be available to the specific connection that created
> it? I'm concerned that if multiple users make a request to that stored
> procedure, they may get someone else's data.
> thanks,
> Joe
>
sql

question on temporary tables

I have a stored procedure that creates a temporary table, populates it
with one record, and then returns that record to an ASP. I've read
that by prefixing the the tablename with a #, that only the connection
that created it can access it.
An IIS server will be making the connection and uses the same
connection string for all users. Even though the connection string is
the same, and the temporary table name is the same, will the temporary
table created only be available to the specific connection that created
it? I'm concerned that if multiple users make a request to that stored
procedure, they may get someone else's data.
thanks,
JoeConnections are always separate from each other, regardless of whether the
client connects using the same login information or not. Your temporary
tables will not collide or be visible to other connections.
Geoff N. Hiten
Microsoft SQL Server MVP
<joseph.fanelli@.vba.va.gov> wrote in message
news:1113844860.949837.190280@.z14g2000cwz.googlegroups.com...
>I have a stored procedure that creates a temporary table, populates it
> with one record, and then returns that record to an ASP. I've read
> that by prefixing the the tablename with a #, that only the connection
> that created it can access it.
> An IIS server will be making the connection and uses the same
> connection string for all users. Even though the connection string is
> the same, and the temporary table name is the same, will the temporary
> table created only be available to the specific connection that created
> it? I'm concerned that if multiple users make a request to that stored
> procedure, they may get someone else's data.
> thanks,
> Joe
>

question on temporary tables

I have a stored procedure that creates a temporary table, populates it
with one record, and then returns that record to an ASP. I've read
that by prefixing the the tablename with a #, that only the connection
that created it can access it.
An IIS server will be making the connection and uses the same
connection string for all users. Even though the connection string is
the same, and the temporary table name is the same, will the temporary
table created only be available to the specific connection that created
it? I'm concerned that if multiple users make a request to that stored
procedure, they may get someone else's data.
thanks,
JoeThe #tablename temp table exists only for the connection that calls the
proc. You are safe to call the same proc and the same table name as many
times as necessary without fear. Do not however use ##{tablename}. This will
cause a global temp table to be created and is referenceable by other
connections until the connection that created it is dropped.
--Rick Butler
"joseph.fanelli@.vba.va.gov" wrote:
> I have a stored procedure that creates a temporary table, populates it
> with one record, and then returns that record to an ASP. I've read
> that by prefixing the the tablename with a #, that only the connection
> that created it can access it.
> An IIS server will be making the connection and uses the same
> connection string for all users. Even though the connection string is
> the same, and the temporary table name is the same, will the temporary
> table created only be available to the specific connection that created
> it? I'm concerned that if multiple users make a request to that stored
> procedure, they may get someone else's data.
> thanks,
> Joe
>|||Why not just select that one row and avoid creating a temp table? Improper
use of temporary objects in stored procedures can cause recompiles and
affect SQL performance. See
http://support.microsoft.com/default.aspx?scid=kb;en-us;243586 for details.
Adrian
"Rick" <Rick@.discussions.microsoft.com> wrote in message
news:BC656DA7-7BB2-4527-94D5-BCD53D702DE4@.microsoft.com...
> The #tablename temp table exists only for the connection that calls the
> proc. You are safe to call the same proc and the same table name as many
> times as necessary without fear. Do not however use ##{tablename}. This
> will
> cause a global temp table to be created and is referenceable by other
> connections until the connection that created it is dropped.
> --Rick Butler
> "joseph.fanelli@.vba.va.gov" wrote:
>> I have a stored procedure that creates a temporary table, populates it
>> with one record, and then returns that record to an ASP. I've read
>> that by prefixing the the tablename with a #, that only the connection
>> that created it can access it.
>> An IIS server will be making the connection and uses the same
>> connection string for all users. Even though the connection string is
>> the same, and the temporary table name is the same, will the temporary
>> table created only be available to the specific connection that created
>> it? I'm concerned that if multiple users make a request to that stored
>> procedure, they may get someone else's data.
>> thanks,
>> Joe
>>

question on temporary tables

I have a stored procedure that creates a temporary table, populates it
with one record, and then returns that record to an ASP. I've read
that by prefixing the the tablename with a #, that only the connection
that created it can access it.
An IIS server will be making the connection and uses the same
connection string for all users. Even though the connection string is
the same, and the temporary table name is the same, will the temporary
table created only be available to the specific connection that created
it? I'm concerned that if multiple users make a request to that stored
procedure, they may get someone else's data.
thanks,
JoeThe #tablename temp table exists only for the connection that calls the
proc. You are safe to call the same proc and the same table name as many
times as necessary without fear. Do not however use ##{tablename}. This
will
cause a global temp table to be created and is referenceable by other
connections until the connection that created it is dropped.
--Rick Butler
"joseph.fanelli@.vba.va.gov" wrote:

> I have a stored procedure that creates a temporary table, populates it
> with one record, and then returns that record to an ASP. I've read
> that by prefixing the the tablename with a #, that only the connection
> that created it can access it.
> An IIS server will be making the connection and uses the same
> connection string for all users. Even though the connection string is
> the same, and the temporary table name is the same, will the temporary
> table created only be available to the specific connection that created
> it? I'm concerned that if multiple users make a request to that stored
> procedure, they may get someone else's data.
> thanks,
> Joe
>|||Why not just select that one row and avoid creating a temp table? Improper
use of temporary objects in stored procedures can cause recompiles and
affect SQL performance. See
http://support.microsoft.com/defaul...kb;en-us;243586 for details.
Adrian
"Rick" <Rick@.discussions.microsoft.com> wrote in message
news:BC656DA7-7BB2-4527-94D5-BCD53D702DE4@.microsoft.com...[vbcol=seagreen]
> The #tablename temp table exists only for the connection that calls the
> proc. You are safe to call the same proc and the same table name as many
> times as necessary without fear. Do not however use ##{tablename}. Th
is
> will
> cause a global temp table to be created and is referenceable by other
> connections until the connection that created it is dropped.
> --Rick Butler
> "joseph.fanelli@.vba.va.gov" wrote:
>

question on temporary tables

I have a stored procedure that creates a temporary table, populates it
with one record, and then returns that record to an ASP. I've read
that by prefixing the the tablename with a #, that only the connection
that created it can access it.
An IIS server will be making the connection and uses the same
connection string for all users. Even though the connection string is
the same, and the temporary table name is the same, will the temporary
table created only be available to the specific connection that created
it? I'm concerned that if multiple users make a request to that stored
procedure, they may get someone else's data.
thanks,
JoeConnections are always separate from each other, regardless of whether the
client connects using the same login information or not. Your temporary
tables will not collide or be visible to other connections.
Geoff N. Hiten
Microsoft SQL Server MVP
<joseph.fanelli@.vba.va.gov> wrote in message
news:1113844860.949837.190280@.z14g2000cwz.googlegroups.com...
>I have a stored procedure that creates a temporary table, populates it
> with one record, and then returns that record to an ASP. I've read
> that by prefixing the the tablename with a #, that only the connection
> that created it can access it.
> An IIS server will be making the connection and uses the same
> connection string for all users. Even though the connection string is
> the same, and the temporary table name is the same, will the temporary
> table created only be available to the specific connection that created
> it? I'm concerned that if multiple users make a request to that stored
> procedure, they may get someone else's data.
> thanks,
> Joe
>

Question on refining query

I tried to search for google but i wasn't exactly how to phase it. My tables has a column "DATE", i am populating a listbox using a record set and the generic query "Select DATE From Table1". I want to display only the Uniqu dates, but that i mean if 08/31/04 shows up a 100 times I only want to show it once in that listbox. I have tried altering the values from the recordset and storing them in a collection and looking to see if they reappear but i keep getting errors. So I figured that there had to be an easier way of just redefining the orinal query just to get those dates and then populating the listbox because much more trivial.

any help would be appreciatedFigured it out, just had to add distinct into the query

thanks

question on inserting a record on sql server with identity column as key

Hi, All:

Please help. I use sql server as back end and Access 2003 as front end
(everything is DAO).

A table on SQL server has an identity column as the key.

We have trouble on adding records to this table using the following SQL.

strSQL = "INSERT INTO myTableOnSQLServer (A, B, C, D, E) SELECT A, B, C, D,
E FROM myTableonAccessLocal"
db.execute strSQL

The schema of the table "myTableOnSQLServer" and the schema of the table
"myTableonAccessLocal" are all the same except that the "myTableOnSQLServer"
has an identity column (ID). The key of the "myTableOnSQLServer" is "ID" and
the table "myTableonAccessLocal" does not have a key.

When we try to run the query, it gives errors indicating the key is violated
or missing.

Should I figure out the autonumber for it first and then add to the SQL
server table?

Many thanks,

HSHongyu Sun (sun@.cae.wisc.edu) writes:
> Please help. I use sql server as back end and Access 2003 as front end
> (everything is DAO).
> A table on SQL server has an identity column as the key.
> We have trouble on adding records to this table using the following SQL.
> strSQL = "INSERT INTO myTableOnSQLServer (A, B, C, D, E) SELECT A, B, C,
D,
> E FROM myTableonAccessLocal"
> db.execute strSQL
> The schema of the table "myTableOnSQLServer" and the schema of the table
> "myTableonAccessLocal" are all the same except that the
> "myTableOnSQLServer"
> has an identity column (ID). The key of the "myTableOnSQLServer" is "ID"
> and the table "myTableonAccessLocal" does not have a key.
> When we try to run the query, it gives errors indicating the key is
> violated or missing.

Could you please post the exact error message?

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

question on inserting a record on sql server with identity column as key

Hi, All:
Please help. I use sql server as back end and Access 2003 as front end
(everything is DAO).
A table on SQL server has an identity column as the key.
We have trouble on adding records to this table using the following SQL.
strSQL = "INSERT INTO myTableOnSQLServer (A, B, C, D, E) SELECT A, B, C, D,
E FROM myTableonAccessLocal"
db.execute strSQL
The schema of the table "myTableOnSQLServer" and the schema of the table
"myTableonAccessLocal" are all the same except that the "myTableOnSQLServer"
has an identity column (ID). The key of the "myTableOnSQLServer" is "ID" and
the table "myTableonAccessLocal" does not have a key.
When we try to run the query, it gives errors indicating the key is violated
or missing.
Should I figure out the autonumber for it first and then add to the SQL
server table?
Many thanks,
HSThere are two options depending your answer to this question:
Do you want you myTableOnSQLServer table to have the same value of ID from
myTableonAccessLocal.
1. If NO. Then don't specify the ID column in your INSERT INTO statement.
2. If YES. Use SET IDENTITY_INSERT command to allows explicit values to be
inserted into the identity column of a table. Like this:
SET IDENTITY_INSERT myTableOnSQLServer ON
insert into ....
SET IDENTITY_INSERT myTableOnSQLServer OFF
Hope it helps.
"Hongyu Sun" <sun@.cae.wisc.edu> wrote in message
news:dq7fe7$f6b$1@.news.doit.wisc.edu...
> Hi, All:
> Please help. I use sql server as back end and Access 2003 as front end
> (everything is DAO).
> A table on SQL server has an identity column as the key.
> We have trouble on adding records to this table using the following SQL.
> strSQL = "INSERT INTO myTableOnSQLServer (A, B, C, D, E) SELECT A, B, C,
D,
> E FROM myTableonAccessLocal"
> db.execute strSQL
> The schema of the table "myTableOnSQLServer" and the schema of the table
> "myTableonAccessLocal" are all the same except that the
"myTableOnSQLServer"
> has an identity column (ID). The key of the "myTableOnSQLServer" is "ID"
and
> the table "myTableonAccessLocal" does not have a key.
> When we try to run the query, it gives errors indicating the key is
violated
> or missing.
> Should I figure out the autonumber for it first and then add to the SQL
> server table?
> Many thanks,
> HS
>

Question on displaying large number of record?

I have datagrid that needs to display a log table which has more than million records.

Since it it huge number, it is not possible to get dataset using "select * from log_table" to fill and to bind to datagrid.

Is there anyway to display first 100 rows on first page and show next 100 rows if use clicks on page 2?

Thank you very much in advance!

Justin

since its simply not feasible to bring that much data back from sql, you should look at performing the paging logic in sql server itself.
sql would only return one page of data at a time

here's some links to see how it can be done:

http://weblogs.asp.net/pwilson/archive/2003/10/10/31456.aspx

http://www.sqlmag.com/articles/index.cfm?articleid=40505

http://www.4guysfromrolla.com/webtech/062899-1.shtml

i'm sure there are plenty more examples floating around..

|||

You could use the TOP clause in your query

or

You could use .net assemblies with SQL server......(CREATE ASSEMBLY ...)

|||I am using Oracle but thank you anyway... I got idea...

|||

In PL/SQL you can use row_number() function, or if you don't need to sort the records, simply use the ROWNUM column.