how to setup LOCKS in ATL OELDB Consumer?

in sql server, we can use TABLOCK to lock a table, but how can i do this in ATL, without using a SP

i worry if two user perform db.Update() at the same time and refer to the same row, there may cause a problem.

i fond this line in the OLEDB ref.

Locking can occur with any of the methods that interact with rows on the data source. These include IRowsetLocate::GetRowsAt, IRowsetLocate::GetRowsByBookmark, IRowsetUpdate::Update, and others. These operations take locks and can wait for locks to be released by other transactions.

does this mean the locks are prefomed automatically ?

and, in the deferred update mode, if two user insert the same row into the DB, say, both of them insert 2 rows, 1 is the same, and the others are different, what would happen? what will the server return?

Locks are managed on the server side by the database engine.

Different cursor types (static, keyset or dynamic) and isolation levels can affect the server locking behavior. On the client, you can control cursor types and isolation level through OLEDB.

So the answer to the question regarding inserting the same row depends on the cursor type and iso level etc. If the table has an index that does not allow duplicate key, the 2nd insert could even fail. Basically, server will ensure transactional integrity is maintained for the data.

|||

so i learned the isolation levels, but what is a cursor type in OLEDB? i only heard that in ADO, do they have the same mean? and how can i set the cursor type in OLEDB

|||Cursor definition is the same across all API's. For ADO and ODBC, you can request a particular cursor type directly. OLEDB does this a bit differently, you request the behaviors of the cursor such as updatability, scrollabilty etc through the corresponding OLEDB properties, and OLEDB will find the most suitable cursor for you under the cover.

How to setup a repeated update to a table in SQL?

Hi:

I am fairly new to SQL Server 2005 and before now, I have only had to restore databases, and connect to tables via ODBC connection in a reference (read only) setup. Today, I have a very small project to set up using the server.

I have a userlogon.csv file that the network stores on a file server in a hidden share \logon$. It has 4 columns, UserID, Computer, Date, Time.

I was able to create a database called UserLogon and import the file as it was today. I want to create a scheduled update so the server would go to this file perhaps 4 times a day (or more) and grab any new logins that have appended itself to this CSV file.

So, as a newbie with a 1,900 page SQL Server 2005 unleashed manual at my side, could someone outline what the steps are in general I should follow to set this up?

I have the process laid out in my mind, but I don't know how to translate in into a scheduled task of the SQL Server :

1. Create DB and import the table (done)

2. create a stored procedure that connects to the CSV file and evaluates date and time stamps then appends any new records into the SQL db table. (appending records would be achieved by using the INSERT and WHERE statements?)

3. Schedule a job to perform this task on a routine basis.

It appears that the file connection portion of this set up is defined outside the evaluation and append record procedure? (not in the same stored procedure). Perhaps I tie the whole process together using the Job Manager, selecting the file settings, and then the stored procedure to be performed on the file.?

I hope I have been descriptive enough to ask if someone could outline the modules/features/processes involved so I can read up on them and figure them out using the book.

Thank you in advance.

David

David:

It sounds to me like you need a good book on the subject of "Database Design". I would suggest Pro SQL Server 2005 Database Design and Optimization by MVP Louis Davidson.

Maybe since you are just getting started a better choice for a first book would be Data Modeling Essentials by Graeme Simison

Kent

How to set transactions timeout?

Is there a way for a client to tell a server to rollback a transaction after a certain time elapses?

The scenario: I execute an update statement in a RepeatableRead transaction, but the client may lose network connectivity before the Commit is issued. If this occurs after ExecuteNonQuery, then the process on the sql server is holding a large amount of locks, and the server takes several minutes before it releases those locks.

Meanwhile, other transactions are attempting to run, but are getting selected as the deadlock victims because they are waiting on resources that the disconnected client had locked. This really backs things up.

I have found some settings that can be done on the server side that will decrease the time it takes for a transaction WAITING on resources to timeout. I.E. this just makes those that are being blocked timeout faster. But it is the blockING process that I want rolledback sooner. It doesn't get selected as the deadlock victim because it has all the resources it needs. It is not waiting on any resources, but instead the server is simply waiting for another query or a commit, which it never gets because the client lost network connectivity. The command timeout doesn't seem to have an affect, because the command itself completes.

I tested this by having a client display a modal dialog box just before the commit statement, and then I unplugged the network cable. I then used activity monitor on the server to view the locks being held. I refreshed it several times, and I believe at least 5 minutes pasted before the locks were released.

In the production environment, users will be losing connectivity very often. I'm sure someone will suggest a different architecture where the database logic is server side, and data is sent to/from clients in another manner so that this situation can be handled by the server side app. Right now that is not an option. I am running the database in 2000, but sql server 2005 with 2000 compatibility mode is also possible.

Thanks in advance.

If you are using distributed transactions, the timeout can be configured via Control Panel->Administrative Tools->Component Services.

See also http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=772404&SiteID=1

|||I'm not using distributed transactions unless that's the default for ADO.NET, but I'll read up on them and see if maybe that's what I should use. Thanks.

How to set transactions timeout?

Is there a way for a client to tell a server to rollback a transaction after a certain time elapses?

The scenario: I execute an update statement in a RepeatableRead transaction, but the client may lose network connectivity before the Commit is issued. If this occurs after ExecuteNonQuery, then the process on the sql server is holding a large amount of locks, and the server takes several minutes before it releases those locks.

Meanwhile, other transactions are attempting to run, but are getting selected as the deadlock victims because they are waiting on resources that the disconnected client had locked. This really backs things up.

I have found some settings that can be done on the server side that will decrease the time it takes for a transaction WAITING on resources to timeout. I.E. this just makes those that are being blocked timeout faster. But it is the blockING process that I want rolledback sooner. It doesn't get selected as the deadlock victim because it has all the resources it needs. It is not waiting on any resources, but instead the server is simply waiting for another query or a commit, which it never gets because the client lost network connectivity. The command timeout doesn't seem to have an affect, because the command itself completes.

I tested this by having a client display a modal dialog box just before the commit statement, and then I unplugged the network cable. I then used activity monitor on the server to view the locks being held. I refreshed it several times, and I believe at least 5 minutes pasted before the locks were released.

In the production environment, users will be losing connectivity very often. I'm sure someone will suggest a different architecture where the database logic is server side, and data is sent to/from clients in another manner so that this situation can be handled by the server side app. Right now that is not an option. I am running the database in 2000, but sql server 2005 with 2000 compatibility mode is also possible.

Thanks in advance.

If you are using distributed transactions, the timeout can be configured via Control Panel->Administrative Tools->Component Services.

See also http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=772404&SiteID=1

|||I'm not using distributed transactions unless that's the default for ADO.NET, but I'll read up on them and see if maybe that's what I should use. Thanks.

how to send mail using trigger

Hello
I have created a update grigger. I want to send a mail whenever is trigger
is fired. I tried using xp_sendmail, but it required mapi account to be
configured in server. I want to avoid this situation. Can I use cdo code in
trigger?
regards
Ashish
"Ashish kanoongo" <ashishkanoongo@.hotmail.com> wrote in message
news:uuyGHXVoHHA.4032@.TK2MSFTNGP02.phx.gbl...
> Hello
> I have created a update grigger. I want to send a mail whenever is trigger
> is fired. I tried using xp_sendmail, but it required mapi account to be
> configured in server. I want to avoid this situation. Can I use cdo code
> in trigger?
I believe so. But I wouldn't recommend it.
It'll really mess up performance.
You're better off having the trigger enter a row into a table and then
having a scheduled task go through once every X minutes sending emails.

> regards
> Ashish
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||Well for SQL Server 2000 I agree, but as the Mail Service is now
enabled with SQL Server Service Broker in SQL Server 2005 you *could*
consider doing this within a trigger.
Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||Try xp_smtp_sendmail.
TheSQLGuru
President
Indicium Resources, Inc.
"Ashish kanoongo" <ashishkanoongo@.hotmail.com> wrote in message
news:uuyGHXVoHHA.4032@.TK2MSFTNGP02.phx.gbl...
> Hello
> I have created a update grigger. I want to send a mail whenever is trigger
> is fired. I tried using xp_sendmail, but it required mapi account to be
> configured in server. I want to avoid this situation. Can I use cdo code
> in trigger?
> regards
> Ashish
>
|||DOnt use xp_smtp_sendmail in a trigger, this will start a mail
session within the current transaction, leading to the problem that
the transaction will be staled during the mail sending process as it
does not behave asynchronously in comparison to Service Broker enabled
DBMail.
Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||Hello All
I tried following solution, it is working for me. I like to know pros/cons
on this if any.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=20649
Ashish
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1180409187.775983.273450@.u30g2000hsc.googlegr oups.com...
DOnt use xp_smtp_sendmail in a trigger, this will start a mail
session within the current transaction, leading to the problem that
the transaction will be staled during the mail sending process as it
does not behave asynchronously in comparison to Service Broker enabled
DBMail.
Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||I already pointed out the cons, the same applies for this solution as
it might stale your application and the transaction with it. I
addition, the process might cancel the transaction if you execute it
within a trigger, depending on the error level coming back, The con
against a solution with a mail table and a scheduled delivery is that
it would not be send immediately, but you could implement something
like a retry whereas you cannot do this in the above procedure used
within a trigger. The best thing is to use the Service broker enabled
mail service if you are using SQL Server 2005. If you are using SQL
Server 2000 you should use a mail table which queues up mails and
sends them periodically.
Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||Sorry, I didn't see the user state he was using SQL Server 2005. Oh, wait.
.. . he didn't! :-)
TheSQLGuru
President
Indicium Resources, Inc.
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1180409187.775983.273450@.u30g2000hsc.googlegr oups.com...
DOnt use xp_smtp_sendmail in a trigger, this will start a mail
session within the current transaction, leading to the problem that
the transaction will be staled during the mail sending process as it
does not behave asynchronously in comparison to Service Broker enabled
DBMail.
Jens K. Suessmeyer.
http://www.sqlserver2005.de

how to send mail using trigger

Hello
I have created a update grigger. I want to send a mail whenever is trigger
is fired. I tried using xp_sendmail, but it required mapi account to be
configured in server. I want to avoid this situation. Can I use cdo code in
trigger?
regards
Ashish"Ashish kanoongo" <ashishkanoongo@.hotmail.com> wrote in message
news:uuyGHXVoHHA.4032@.TK2MSFTNGP02.phx.gbl...
> Hello
> I have created a update grigger. I want to send a mail whenever is trigger
> is fired. I tried using xp_sendmail, but it required mapi account to be
> configured in server. I want to avoid this situation. Can I use cdo code
> in trigger?
I believe so. But I wouldn't recommend it.
It'll really mess up performance.
You're better off having the trigger enter a row into a table and then
having a scheduled task go through once every X minutes sending emails.

> regards
> Ashish
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Well for SQL Server 2000 I agree, but as the Mail Service is now
enabled with SQL Server Service Broker in SQL Server 2005 you *could*
consider doing this within a trigger.
Jens K. Suessmeyer.
http://www.sqlserver2005.de
--|||Try xp_smtp_sendmail.
TheSQLGuru
President
Indicium Resources, Inc.
"Ashish kanoongo" <ashishkanoongo@.hotmail.com> wrote in message
news:uuyGHXVoHHA.4032@.TK2MSFTNGP02.phx.gbl...
> Hello
> I have created a update grigger. I want to send a mail whenever is trigger
> is fired. I tried using xp_sendmail, but it required mapi account to be
> configured in server. I want to avoid this situation. Can I use cdo code
> in trigger?
> regards
> Ashish
>|||DOn=B4t use xp_smtp_sendmail in a trigger, this will start a mail
session within the current transaction, leading to the problem that
the transaction will be staled during the mail sending process as it
does not behave asynchronously in comparison to Service Broker enabled
DBMail.
Jens K. Suessmeyer.
http://www.sqlserver2005.de
--|||Hello All
I tried following solution, it is working for me. I like to know pros/cons
on this if any.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=20649
Ashish
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1180409187.775983.273450@.u30g2000hsc.googlegroups.com...
DOnt use xp_smtp_sendmail in a trigger, this will start a mail
session within the current transaction, leading to the problem that
the transaction will be staled during the mail sending process as it
does not behave asynchronously in comparison to Service Broker enabled
DBMail.
Jens K. Suessmeyer.
http://www.sqlserver2005.de
--|||I already pointed out the cons, the same applies for this solution as
it might stale your application and the transaction with it. I
addition, the process might cancel the transaction if you execute it
within a trigger, depending on the error level coming back, The con
against a solution with a mail table and a scheduled delivery is that
it would not be send immediately, but you could implement something
like a retry whereas you cannot do this in the above procedure used
within a trigger. The best thing is to use the Service broker enabled
mail service if you are using SQL Server 2005. If you are using SQL
Server 2000 you should use a mail table which queues up mails and
sends them periodically.
Jens K. Suessmeyer.
http://www.sqlserver2005.de
--|||Sorry, I didn't see the user state he was using SQL Server 2005. Oh, wait.
. . he didn't! :-)
TheSQLGuru
President
Indicium Resources, Inc.
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1180409187.775983.273450@.u30g2000hsc.googlegroups.com...
DOnt use xp_smtp_sendmail in a trigger, this will start a mail
session within the current transaction, leading to the problem that
the transaction will be staled during the mail sending process as it
does not behave asynchronously in comparison to Service Broker enabled
DBMail.
Jens K. Suessmeyer.
http://www.sqlserver2005.de
--

how to send mail using trigger

Hello
I have created a update grigger. I want to send a mail whenever is trigger
is fired. I tried using xp_sendmail, but it required mapi account to be
configured in server. I want to avoid this situation. Can I use cdo code in
trigger?
regards
Ashish"Ashish kanoongo" <ashishkanoongo@.hotmail.com> wrote in message
news:uuyGHXVoHHA.4032@.TK2MSFTNGP02.phx.gbl...
> Hello
> I have created a update grigger. I want to send a mail whenever is trigger
> is fired. I tried using xp_sendmail, but it required mapi account to be
> configured in server. I want to avoid this situation. Can I use cdo code
> in trigger?
I believe so. But I wouldn't recommend it.
It'll really mess up performance.
You're better off having the trigger enter a row into a table and then
having a scheduled task go through once every X minutes sending emails.
> regards
> Ashish
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Well for SQL Server 2000 I agree, but as the Mail Service is now
enabled with SQL Server Service Broker in SQL Server 2005 you *could*
consider doing this within a trigger.
Jens K. Suessmeyer.
--
http://www.sqlserver2005.de
--|||Try xp_smtp_sendmail.
--
TheSQLGuru
President
Indicium Resources, Inc.
"Ashish kanoongo" <ashishkanoongo@.hotmail.com> wrote in message
news:uuyGHXVoHHA.4032@.TK2MSFTNGP02.phx.gbl...
> Hello
> I have created a update grigger. I want to send a mail whenever is trigger
> is fired. I tried using xp_sendmail, but it required mapi account to be
> configured in server. I want to avoid this situation. Can I use cdo code
> in trigger?
> regards
> Ashish
>|||DOn=B4t use xp_smtp_sendmail in a trigger, this will start a mail
session within the current transaction, leading to the problem that
the transaction will be staled during the mail sending process as it
does not behave asynchronously in comparison to Service Broker enabled
DBMail.
Jens K. Suessmeyer.
--
http://www.sqlserver2005.de
--|||Hello All
I tried following solution, it is working for me. I like to know pros/cons
on this if any.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=20649
Ashish
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1180409187.775983.273450@.u30g2000hsc.googlegroups.com...
DOn´t use xp_smtp_sendmail in a trigger, this will start a mail
session within the current transaction, leading to the problem that
the transaction will be staled during the mail sending process as it
does not behave asynchronously in comparison to Service Broker enabled
DBMail.
Jens K. Suessmeyer.
--
http://www.sqlserver2005.de
--|||I already pointed out the cons, the same applies for this solution as
it might stale your application and the transaction with it. I
addition, the process might cancel the transaction if you execute it
within a trigger, depending on the error level coming back, The con
against a solution with a mail table and a scheduled delivery is that
it would not be send immediately, but you could implement something
like a retry whereas you cannot do this in the above procedure used
within a trigger. The best thing is to use the Service broker enabled
mail service if you are using SQL Server 2005. If you are using SQL
Server 2000 you should use a mail table which queues up mails and
sends them periodically.
Jens K. Suessmeyer.
--
http://www.sqlserver2005.de
--|||Sorry, I didn't see the user state he was using SQL Server 2005. Oh, wait.
. . he didn't! :-)
--
TheSQLGuru
President
Indicium Resources, Inc.
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1180409187.775983.273450@.u30g2000hsc.googlegroups.com...
DOn´t use xp_smtp_sendmail in a trigger, this will start a mail
session within the current transaction, leading to the problem that
the transaction will be staled during the mail sending process as it
does not behave asynchronously in comparison to Service Broker enabled
DBMail.
Jens K. Suessmeyer.
--
http://www.sqlserver2005.de
--