Question about OLEDB providers

Hi all,
We have an ASP application that uses a mssql2k db and was migrated from
mssql7.
It is using SQLOLEDB provider to connect to db. Recently there was an
escalation
about some performance issues. While trying to debug some long running
queries,
I came across some weird results for the problem queries - they behaved quit
e
differently when executed from Query Analyzer and when called from the app -
e.g.,
lock escalation, timeouts, etc etc. We might have some issues with the app
design...
however, I had a qn related to OLEDB provider for SQL Server and OLEDB
provider
for ODBC - just to make sure that there are no gotchas here...
1. Which of the above two drivers are used internally by Query
Analyzer/Enterprise
Manager etc?
2. Is OLEDB provider for ODBC recommended at all (MS says it is always
better to
use the native provider for SQL Server) over native provider?
TIA
paraa"Client Network Utility" (cliconfg.exe) shows what network libraries you are
using and OLEDB (for all SEM, QA and your ASP apps) on your client side. The
serverside is same regardless (cannot distinguish whether client is using
ODBC or OLEDB) and simply listens for Tabular Data Stream (TDS) traffic.
Changing from v7 to -2000 improves the optimiser stategies and degree of
parallelism [assuming you have latest Service Packs to eliminate bugs].
Mostly locking problems arise due to user contention [apps holding exclusive
locks and long-running transactions]. Suggest you use Profiler to capture
TSQL generated by apps and then replay from [several sessions of] QA to show
they are equivalent.
You can use Profiler to report on Locks(Escalation) if you suspect foul
play. Can always use SEM CurrentActivity or sp_lock or blocker_blockee (in
RK) to see whats happening engine-side
HTH
Dick
"paraa" wrote:

> Hi all,
> We have an ASP application that uses a mssql2k db and was migrated from
> mssql7.
> It is using SQLOLEDB provider to connect to db. Recently there was an
> escalation
> about some performance issues. While trying to debug some long running
> queries,
> I came across some weird results for the problem queries - they behaved qu
ite
> differently when executed from Query Analyzer and when called from the app
-
> e.g.,
> lock escalation, timeouts, etc etc. We might have some issues with the app
> design...
> however, I had a qn related to OLEDB provider for SQL Server and OLEDB
> provider
> for ODBC - just to make sure that there are no gotchas here...
> 1. Which of the above two drivers are used internally by Query
> Analyzer/Enterprise
> Manager etc?
> 2. Is OLEDB provider for ODBC recommended at all (MS says it is always
> better to
> use the native provider for SQL Server) over native provider?
> TIA
> paraa
>|||thx a bunch pal, your info helps
paraa
"Dick in UK" wrote:
> "Client Network Utility" (cliconfg.exe) shows what network libraries you a
re
> using and OLEDB (for all SEM, QA and your ASP apps) on your client side. T
he
> serverside is same regardless (cannot distinguish whether client is using
> ODBC or OLEDB) and simply listens for Tabular Data Stream (TDS) traffic.
> Changing from v7 to -2000 improves the optimiser stategies and degree of
> parallelism [assuming you have latest Service Packs to eliminate bugs].
> Mostly locking problems arise due to user contention [apps holding exclusi
ve
> locks and long-running transactions]. Suggest you use Profiler to capture
> TSQL generated by apps and then replay from [several sessions of] QA to sh
ow
> they are equivalent.
> You can use Profiler to report on Locks(Escalation) if you suspect foul
> play. Can always use SEM CurrentActivity or sp_lock or blocker_blockee (in
> RK) to see whats happening engine-side
> HTH
> Dick
> "paraa" wrote:
>

Question about OLE DM provider and OLE DB provider?

Hi, all here,

I am having a question about OLE DB provider and OLE DM provider. What exactly do OLE DB (Ojbect Linked Embedded Database) provider and OLE DM (Object Linked Embedded Data Mining ) provider mean ? Would any expert here give me some explanations about how it works or giving me some example like code for it? Also, what is the difference between OLE DB and OLE DM?

With best regards,

It's actually the OLE DB for DM provider. OLE DB is a standard specifying interfaces to data. OLE DB for Data Mining is a specification extending OLE DB for use with data mining, and includes what schema rowsets are necessary and the language (DMX) used to query mining models. Information on both are in MSDN.|||Hi, Jamie, thank you very much for your guidance.

Question about Delete and Latency

I am in the process of returning a machine running SQL Server back to
our provider. However, I don't want them to retrieve any of our data
stored in the DB. So I have the following 2 options
a) delete the rows from tables
b) remove the MDB file
Which of the options is better?
If I just delete the rows, will the SQL Server delete them from the
MDB file immediately?
If I remove the MDB file, can anyone put it back?
For instance, the Exchange Server use SQL Server and deleting a
mailbox will retain the data for about 14 days. Is there a similar
provision in SQL Server that retains the data. I don't want our
provider to retrieve any of the data.
Many thanks for reading and looking forward to repliesOn 18.05.2007 15:40, soup_or_power@.yahoo.com wrote:
> I am in the process of returning a machine running SQL Server back to
> our provider. However, I don't want them to retrieve any of our data
> stored in the DB. So I have the following 2 options
> a) delete the rows from tables
> b) remove the MDB file
> Which of the options is better?
> If I just delete the rows, will the SQL Server delete them from the
> MDB file immediately?
> If I remove the MDB file, can anyone put it back?
> For instance, the Exchange Server use SQL Server and deleting a
> mailbox will retain the data for about 14 days. Is there a similar
> provision in SQL Server that retains the data. I don't want our
> provider to retrieve any of the data.
> Many thanks for reading and looking forward to replies
Depends in what state you have to give the machine back. If you don't
need to care for OS then the most thorough and simple is probably to
boot the machine using Knoppix or a similar CD/DVD distro and use dd
if=/dev/zero of=/dev/hda (for all disks) to erase all your hard disks.
Other than that there are special tools for safely erasing data, i.e.
you could overwrite your mdf and ldf files with zeros after you
deactivated your DB and before you drop the DB.
Kind regards
robert|||On May 18, 9:04 am, Robert Klemme <shortcut...@.googlemail.com> wrote:
> On 18.05.2007 15:40, soup_or_po...@.yahoo.com wrote:
>
>
> > I am in the process of returning a machine running SQL Server back to
> > our provider. However, I don't want them to retrieve any of our data
> > stored in the DB. So I have the following 2 options
> > a) delete the rows from tables
> > b) remove the MDB file
> > Which of the options is better?
> > If I just delete the rows, will the SQL Server delete them from the
> > MDB file immediately?
> > If I remove the MDB file, can anyone put it back?
> > For instance, the Exchange Server use SQL Server and deleting a
> > mailbox will retain the data for about 14 days. Is there a similar
> > provision in SQL Server that retains the data. I don't want our
> > provider to retrieve any of the data.
> > Many thanks for reading and looking forward to replies
> Depends in what state you have to give the machine back. If you don't
> need to care for OS then the most thorough and simple is probably to
> boot the machine using Knoppix or a similar CD/DVD distro and use dd
> if=/dev/zero of=/dev/hda (for all disks) to erase all your hard disks.
> Other than that there are special tools for safely erasing data, i.e.
> you could overwrite your mdf and ldf files with zeros after you
> deactivated your DB and before you drop the DB.
> Kind regards
> robert- Hide quoted text -
> - Show quoted text -
Hi Robert
Many thanks for your reply. Could you name the tools for safely
erasing data? Also how can I "deactive" the DB?
Regards|||On 18.05.2007 16:22, soup_or_power@.yahoo.com wrote:
> On May 18, 9:04 am, Robert Klemme <shortcut...@.googlemail.com> wrote:
>> On 18.05.2007 15:40, soup_or_po...@.yahoo.com wrote:
>>
>>
>> I am in the process of returning a machine running SQL Server back to
>> our provider. However, I don't want them to retrieve any of our data
>> stored in the DB. So I have the following 2 options
>> a) delete the rows from tables
>> b) remove the MDB file
>> Which of the options is better?
>> If I just delete the rows, will the SQL Server delete them from the
>> MDB file immediately?
>> If I remove the MDB file, can anyone put it back?
>> For instance, the Exchange Server use SQL Server and deleting a
>> mailbox will retain the data for about 14 days. Is there a similar
>> provision in SQL Server that retains the data. I don't want our
>> provider to retrieve any of the data.
>> Many thanks for reading and looking forward to replies
>> Depends in what state you have to give the machine back. If you don't
>> need to care for OS then the most thorough and simple is probably to
>> boot the machine using Knoppix or a similar CD/DVD distro and use dd
>> if=/dev/zero of=/dev/hda (for all disks) to erase all your hard disks.
>> Other than that there are special tools for safely erasing data, i.e.
>> you could overwrite your mdf and ldf files with zeros after you
>> deactivated your DB and before you drop the DB.
>> Kind regards
>> robert- Hide quoted text -
>> - Show quoted text -
> Hi Robert
> Many thanks for your reply. Could you name the tools for safely
> erasing data?
I don't have names. You will have to look for yourself. Sorry.
> Also how can I "deactive" the DB?
EM -> select DB -> all tasks -> detach database
robert|||"Robert Klemme" <shortcutter@.googlemail.com> wrote in message
news:5b5s08F2g0fupU1@.mid.individual.net...
> On 18.05.2007 16:22, soup_or_power@.yahoo.com wrote:
>> On May 18, 9:04 am, Robert Klemme <shortcut...@.googlemail.com> wrote:
>> On 18.05.2007 15:40, soup_or_po...@.yahoo.com wrote:
>>
>>
>> I am in the process of returning a machine running SQL Server back to
>> our provider. However, I don't want them to retrieve any of our data
>> stored in the DB. So I have the following 2 options
>> a) delete the rows from tables
>> b) remove the MDB file
>> Which of the options is better?
>> If I just delete the rows, will the SQL Server delete them from the
>> MDB file immediately?
>> If I remove the MDB file, can anyone put it back?
>> For instance, the Exchange Server use SQL Server and deleting a
>> mailbox will retain the data for about 14 days. Is there a similar
>> provision in SQL Server that retains the data. I don't want our
>> provider to retrieve any of the data.
>> Many thanks for reading and looking forward to replies
>> Depends in what state you have to give the machine back. If you don't
>> need to care for OS then the most thorough and simple is probably to
>> boot the machine using Knoppix or a similar CD/DVD distro and use dd
>> if=/dev/zero of=/dev/hda (for all disks) to erase all your hard disks.
>> Other than that there are special tools for safely erasing data, i.e.
>> you could overwrite your mdf and ldf files with zeros after you
>> deactivated your DB and before you drop the DB.
>> Kind regards
>> robert- Hide quoted text -
>> - Show quoted text -
>> Hi Robert
>> Many thanks for your reply. Could you name the tools for safely
>> erasing data?
> I don't have names. You will have to look for yourself. Sorry.
>> Also how can I "deactive" the DB?
> EM -> select DB -> all tasks -> detach database
>
Note that will leave the MDF and LDF files still on the server.
he's better of DELETING the database (assuming he can't format the drive or
something like that.)
> robert
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||On 19.05.2007 16:08, Greg D. Moore (Strider) wrote:
> "Robert Klemme" <shortcutter@.googlemail.com> wrote in message
> news:5b5s08F2g0fupU1@.mid.individual.net...
>> On 18.05.2007 16:22, soup_or_power@.yahoo.com wrote:
>> On May 18, 9:04 am, Robert Klemme <shortcut...@.googlemail.com> wrote:
>> On 18.05.2007 15:40, soup_or_po...@.yahoo.com wrote:
>>
>>
>> I am in the process of returning a machine running SQL Server back to
>> our provider. However, I don't want them to retrieve any of our data
>> stored in the DB. So I have the following 2 options
>> a) delete the rows from tables
>> b) remove the MDB file
>> Which of the options is better?
>> If I just delete the rows, will the SQL Server delete them from the
>> MDB file immediately?
>> If I remove the MDB file, can anyone put it back?
>> For instance, the Exchange Server use SQL Server and deleting a
>> mailbox will retain the data for about 14 days. Is there a similar
>> provision in SQL Server that retains the data. I don't want our
>> provider to retrieve any of the data.
>> Many thanks for reading and looking forward to replies
>> Depends in what state you have to give the machine back. If you don't
>> need to care for OS then the most thorough and simple is probably to
>> boot the machine using Knoppix or a similar CD/DVD distro and use dd
>> if=/dev/zero of=/dev/hda (for all disks) to erase all your hard disks.
>> Other than that there are special tools for safely erasing data, i.e.
>> you could overwrite your mdf and ldf files with zeros after you
>> deactivated your DB and before you drop the DB.
>> Kind regards
>> robert- Hide quoted text -
>> - Show quoted text -
>> Hi Robert
>> Many thanks for your reply. Could you name the tools for safely
>> erasing data?
>> I don't have names. You will have to look for yourself. Sorry.
>> Also how can I "deactive" the DB?
>> EM -> select DB -> all tasks -> detach database
> Note that will leave the MDF and LDF files still on the server.
> he's better of DELETING the database (assuming he can't format the drive or
> something like that.)
Yes, I know. That was just the explanation of *one* of the steps (see
my earlier posting).
robert

Question about Delete and Latency

I am in the process of returning a machine running SQL Server back to
our provider. However, I don't want them to retrieve any of our data
stored in the DB. So I have the following 2 options
a) delete the rows from tables
b) remove the MDB file
Which of the options is better?
If I just delete the rows, will the SQL Server delete them from the
MDB file immediately?
If I remove the MDB file, can anyone put it back?
For instance, the Exchange Server use SQL Server and deleting a
mailbox will retain the data for about 14 days. Is there a similar
provision in SQL Server that retains the data. I don't want our
provider to retrieve any of the data.
Many thanks for reading and looking forward to repliesOn 18.05.2007 15:40, soup_or_power@.yahoo.com wrote:
> I am in the process of returning a machine running SQL Server back to
> our provider. However, I don't want them to retrieve any of our data
> stored in the DB. So I have the following 2 options
> a) delete the rows from tables
> b) remove the MDB file
> Which of the options is better?
> If I just delete the rows, will the SQL Server delete them from the
> MDB file immediately?
> If I remove the MDB file, can anyone put it back?
> For instance, the Exchange Server use SQL Server and deleting a
> mailbox will retain the data for about 14 days. Is there a similar
> provision in SQL Server that retains the data. I don't want our
> provider to retrieve any of the data.
> Many thanks for reading and looking forward to replies
Depends in what state you have to give the machine back. If you don't
need to care for OS then the most thorough and simple is probably to
boot the machine using Knoppix or a similar CD/DVD distro and use dd
if=/dev/zero of=/dev/hda (for all disks) to erase all your hard disks.
Other than that there are special tools for safely erasing data, i.e.
you could overwrite your mdf and ldf files with zeros after you
deactivated your DB and before you drop the DB.
Kind regards
robert|||On May 18, 9:04 am, Robert Klemme <shortcut...@.googlemail.com> wrote:
> On 18.05.2007 15:40, soup_or_po...@.yahoo.com wrote:
>
>
>
>
>
>
>
>
> Depends in what state you have to give the machine back. If you don't
> need to care for OS then the most thorough and simple is probably to
> boot the machine using Knoppix or a similar CD/DVD distro and use dd
> if=/dev/zero of=/dev/hda (for all disks) to erase all your hard disks.
> Other than that there are special tools for safely erasing data, i.e.
> you could overwrite your mdf and ldf files with zeros after you
> deactivated your DB and before you drop the DB.
> Kind regards
> robert- Hide quoted text -
> - Show quoted text -
Hi Robert
Many thanks for your reply. Could you name the tools for safely
erasing data? Also how can I "deactive" the DB?
Regards|||On 18.05.2007 16:22, soup_or_power@.yahoo.com wrote:
> On May 18, 9:04 am, Robert Klemme <shortcut...@.googlemail.com> wrote:
> Hi Robert
> Many thanks for your reply. Could you name the tools for safely
> erasing data?
I don't have names. You will have to look for yourself. Sorry.

> Also how can I "deactive" the DB?
EM -> select DB -> all tasks -> detach database
robert|||"Robert Klemme" <shortcutter@.googlemail.com> wrote in message
news:5b5s08F2g0fupU1@.mid.individual.net...
> On 18.05.2007 16:22, soup_or_power@.yahoo.com wrote:
> I don't have names. You will have to look for yourself. Sorry.
>
> EM -> select DB -> all tasks -> detach database
>
Note that will leave the MDF and LDF files still on the server.
he's better of DELETING the database (assuming he can't format the drive or
something like that.)

> robert
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||On 19.05.2007 16:08, Greg D. Moore (Strider) wrote:
> "Robert Klemme" <shortcutter@.googlemail.com> wrote in message
> news:5b5s08F2g0fupU1@.mid.individual.net...
> Note that will leave the MDF and LDF files still on the server.
> he's better of DELETING the database (assuming he can't format the drive o
r
> something like that.)
Yes, I know. That was just the explanation of *one* of the steps (see
my earlier posting).
robert

Question about Delete and Latency

I am in the process of returning a machine running SQL Server back to
our provider. However, I don't want them to retrieve any of our data
stored in the DB. So I have the following 2 options
a) delete the rows from tables
b) remove the MDB file
Which of the options is better?
If I just delete the rows, will the SQL Server delete them from the
MDB file immediately?
If I remove the MDB file, can anyone put it back?
For instance, the Exchange Server use SQL Server and deleting a
mailbox will retain the data for about 14 days. Is there a similar
provision in SQL Server that retains the data. I don't want our
provider to retrieve any of the data.
Many thanks for reading and looking forward to replies
On 18.05.2007 15:40, soup_or_power@.yahoo.com wrote:
> I am in the process of returning a machine running SQL Server back to
> our provider. However, I don't want them to retrieve any of our data
> stored in the DB. So I have the following 2 options
> a) delete the rows from tables
> b) remove the MDB file
> Which of the options is better?
> If I just delete the rows, will the SQL Server delete them from the
> MDB file immediately?
> If I remove the MDB file, can anyone put it back?
> For instance, the Exchange Server use SQL Server and deleting a
> mailbox will retain the data for about 14 days. Is there a similar
> provision in SQL Server that retains the data. I don't want our
> provider to retrieve any of the data.
> Many thanks for reading and looking forward to replies
Depends in what state you have to give the machine back. If you don't
need to care for OS then the most thorough and simple is probably to
boot the machine using Knoppix or a similar CD/DVD distro and use dd
if=/dev/zero of=/dev/hda (for all disks) to erase all your hard disks.
Other than that there are special tools for safely erasing data, i.e.
you could overwrite your mdf and ldf files with zeros after you
deactivated your DB and before you drop the DB.
Kind regards
robert