OLTP Best Practices

Are there any best practices for high volume OLTP systems (20M trans / day)?
Such as sp_updatestats, index defrag, file group alignment, online backup,
etc...
Any advice is greatly appreciated.
Thanks
Bryan DoveI am looking for some general best practices. Such as how often should
sp_updatestats be run, how often should the t-log be backed up, etc... We
are having a debate over what is necessary, versus what is overkill for many
of the maintenance-type processes.
Thanks
Bryan Dove
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uooxAywgDHA.2456@.TK2MSFTNGP12.phx.gbl...
> They are all good practices for most OLTP systems but how, when etc
depends
> a lot on each individual app's needs. Maybe this is a good place to
start.
>
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/operate/opsguide/default.asp
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Bryan Dove" <bryan.dove@.nospam.ndchealth.com> wrote in message
> news:eLkA51ugDHA.3324@.TK2MSFTNGP11.phx.gbl...
> > Are there any best practices for high volume OLTP systems (20M trans /
> day)?
> > Such as sp_updatestats, index defrag, file group alignment, online
backup,
> > etc...
> >
> > Any advice is greatly appreciated.
> >
> > Thanks
> >
> > Bryan Dove
> >
> >
>|||Hi Bryan,
I think how often the transaction log backup should be run is dependent on
your actual requirements. For example, if you have the following backup
plan:
Time Event
8:00 A.M. Back up database
Noon Back up transaction log
4:00 P.M. Back up transaction log
6:00 P.M. Back up database
8:00 P.M. Back up transaction log
10:00 P.M. Failure occurs
Then we may lose the data between 8PM and 10PM.
In "Analyzing Availability and Recovery Requirements" topic in SQL Server
2000 Books Online, there are some basic questions to help you analyze your
availability and recovery requirements:
What are your availability requirements? What portion of each day must the
database be online?
What is the financial cost of downtime to your business?
If you experience media failure, such as a failing disk drive, what is the
acceptable downtime?
In case of a disaster, such as the loss of a server in a fire, what is the
acceptable downtime?
How important is it to never lose a change?
How easy would it be to re-create lost data?
Does your organization employ system or database administrators?
Who will be responsible for performing backup and recovery operations, and
how will they be trained?
Here are some questions to help you choose the tools, techniques, and
hardware appropriate for your site:
How large is each database?
How often does the data in each database change?
Are some tables modified more often than others?
What are your critical database production periods?
When does the database experience heavy use, resulting in frequent inserts
and updates?
Is transaction log space consumption likely to be a problem due to heavy
update activity?
Is your database subject to periodic bulk data loading?
Is your database subject to risky updates or application errors that may
not be detected immediately?
Is your database server part of a SQL Server 2000 failover cluster for high
availability?
Is your database in a multi-server environment with centralized
administration?
"Planning for Disaster Recovery"
<http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/a
d_bkprst_7kvb.asp>
Designing a Backup and Restore Strategy
<http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/a
d_bkprst_63eh.asp>
- Support WebCast: SQL Server 2000 Database Recovery: Backup and Restore
<http://support.microsoft.com/default.aspx?scid=/servicedesks/webcasts/wc120
500/wcblurb120500.asp>
- INF: Disaster Recovery Planning for SQL Server
<http://support.microsoft.com/?kbid=169039>
<http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtech
nol/sql/reskit/sql2000/part4/c1561.asp>
In addition, making some experiments and comparisons would be necessary in
a production system, as it is more effective.
Bill Cheng
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
--
| From: "Bryan Dove" <bryan.dove@.nospam.ndchealth.com>
| References: <eLkA51ugDHA.3324@.TK2MSFTNGP11.phx.gbl>
<uooxAywgDHA.2456@.TK2MSFTNGP12.phx.gbl>
| Subject: Re: OLTP Best Practices
| Date: Fri, 26 Sep 2003 09:28:03 -0400
| Lines: 46
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.3790.0
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Message-ID: <#yiyZIDhDHA.2080@.TK2MSFTNGP12.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: 12.5.227.202
| Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP12.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:308507
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| I am looking for some general best practices. Such as how often should
| sp_updatestats be run, how often should the t-log be backed up, etc... We
| are having a debate over what is necessary, versus what is overkill for
many
| of the maintenance-type processes.
|
| Thanks
|
| Bryan Dove
|
|
|
| "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
| news:uooxAywgDHA.2456@.TK2MSFTNGP12.phx.gbl...
| > They are all good practices for most OLTP systems but how, when etc
| depends
| > a lot on each individual app's needs. Maybe this is a good place to
| start.
| >
| >
|
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechn
ol/sql/maintain/operate/opsguide/default.asp
| >
| > --
| >
| > Andrew J. Kelly
| > SQL Server MVP
| >
| >
| > "Bryan Dove" <bryan.dove@.nospam.ndchealth.com> wrote in message
| > news:eLkA51ugDHA.3324@.TK2MSFTNGP11.phx.gbl...
| > > Are there any best practices for high volume OLTP systems (20M trans /
| > day)?
| > > Such as sp_updatestats, index defrag, file group alignment, online
| backup,
| > > etc...
| > >
| > > Any advice is greatly appreciated.
| > >
| > > Thanks
| > >
| > > Bryan Dove
| > >
| > >
| >
| >
|
|
||||Good advice on the tlog backups.
What about the sp_updatestats. How often should that take place? Our
database is an OLTP, third normal form, about 20,000,000 trans per day.
Thanks
Bryan Dove
""Bill Cheng [MSFT]"" <billchng@.online.microsoft.com> wrote in message
news:ADWtU5IhDHA.2272@.cpmsftngxa06.phx.gbl...
> Hi Bryan,
> I think how often the transaction log backup should be run is dependent on
> your actual requirements. For example, if you have the following backup
> plan:
> Time Event
> 8:00 A.M. Back up database
> Noon Back up transaction log
> 4:00 P.M. Back up transaction log
> 6:00 P.M. Back up database
> 8:00 P.M. Back up transaction log
> 10:00 P.M. Failure occurs
> Then we may lose the data between 8PM and 10PM.
> In "Analyzing Availability and Recovery Requirements" topic in SQL Server
> 2000 Books Online, there are some basic questions to help you analyze your
> availability and recovery requirements:
> What are your availability requirements? What portion of each day must the
> database be online?
>
> What is the financial cost of downtime to your business?
>
> If you experience media failure, such as a failing disk drive, what is the
> acceptable downtime?
>
> In case of a disaster, such as the loss of a server in a fire, what is the
> acceptable downtime?
>
> How important is it to never lose a change?
>
> How easy would it be to re-create lost data?
>
> Does your organization employ system or database administrators?
>
> Who will be responsible for performing backup and recovery operations, and
> how will they be trained?
> Here are some questions to help you choose the tools, techniques, and
> hardware appropriate for your site:
> How large is each database?
>
> How often does the data in each database change?
>
> Are some tables modified more often than others?
>
> What are your critical database production periods?
>
> When does the database experience heavy use, resulting in frequent inserts
> and updates?
>
> Is transaction log space consumption likely to be a problem due to heavy
> update activity?
>
> Is your database subject to periodic bulk data loading?
>
> Is your database subject to risky updates or application errors that may
> not be detected immediately?
>
> Is your database server part of a SQL Server 2000 failover cluster for
high
> availability?
>
> Is your database in a multi-server environment with centralized
> administration?
>
> "Planning for Disaster Recovery"
>
<http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/a
> d_bkprst_7kvb.asp>
> Designing a Backup and Restore Strategy
>
<http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/a
> d_bkprst_63eh.asp>
> - Support WebCast: SQL Server 2000 Database Recovery: Backup and Restore
>
<http://support.microsoft.com/default.aspx?scid=/servicedesks/webcasts/wc120
> 500/wcblurb120500.asp>
> - INF: Disaster Recovery Planning for SQL Server
> <http://support.microsoft.com/?kbid=169039>
>
<http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtech
> nol/sql/reskit/sql2000/part4/c1561.asp>
> In addition, making some experiments and comparisons would be necessary in
> a production system, as it is more effective.
>
> Bill Cheng
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> --
> | From: "Bryan Dove" <bryan.dove@.nospam.ndchealth.com>
> | References: <eLkA51ugDHA.3324@.TK2MSFTNGP11.phx.gbl>
> <uooxAywgDHA.2456@.TK2MSFTNGP12.phx.gbl>
> | Subject: Re: OLTP Best Practices
> | Date: Fri, 26 Sep 2003 09:28:03 -0400
> | Lines: 46
> | X-Priority: 3
> | X-MSMail-Priority: Normal
> | X-Newsreader: Microsoft Outlook Express 6.00.3790.0
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | Message-ID: <#yiyZIDhDHA.2080@.TK2MSFTNGP12.phx.gbl>
> | Newsgroups: microsoft.public.sqlserver.server
> | NNTP-Posting-Host: 12.5.227.202
> | Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP12.phx.gbl
> | Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:308507
> | X-Tomcat-NG: microsoft.public.sqlserver.server
> |
> | I am looking for some general best practices. Such as how often should
> | sp_updatestats be run, how often should the t-log be backed up, etc...
We
> | are having a debate over what is necessary, versus what is overkill for
> many
> | of the maintenance-type processes.
> |
> | Thanks
> |
> | Bryan Dove
> |
> |
> |
> | "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> | news:uooxAywgDHA.2456@.TK2MSFTNGP12.phx.gbl...
> | > They are all good practices for most OLTP systems but how, when etc
> | depends
> | > a lot on each individual app's needs. Maybe this is a good place to
> | start.
> | >
> | >
> |
>
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechn
> ol/sql/maintain/operate/opsguide/default.asp
> | >
> | > --
> | >
> | > Andrew J. Kelly
> | > SQL Server MVP
> | >
> | >
> | > "Bryan Dove" <bryan.dove@.nospam.ndchealth.com> wrote in message
> | > news:eLkA51ugDHA.3324@.TK2MSFTNGP11.phx.gbl...
> | > > Are there any best practices for high volume OLTP systems (20M trans
/
> | > day)?
> | > > Such as sp_updatestats, index defrag, file group alignment, online
> | backup,
> | > > etc...
> | > >
> | > > Any advice is greatly appreciated.
> | > >
> | > > Thanks
> | > >
> | > > Bryan Dove
> | > >
> | > >
> | >
> | >
> |
> |
> |
>|||Hi Bryan,
sp_updatestats runs UPDATE STATISTICS against all user-defined tables in
the current database. In addition, the statistics update may cause stored
procedures that access the table data to be re-compiled in SQL Server. It
will reflect the up-to-date data distribution of the database data and may
let SQL Server choose better execution plans. However, re-compilation of
stored procedures can impact SQL Server performance very much.
According to my experience, if you find your stored procedures performance
degrade after long time of running and recompiling it can resolve the
problem, then it indicates that SQL Server may need the up-to-date
statistics. Then we can run sp_updatestats periodically (usually daily
update is fine.)
This posting is provided "AS IS" with no warranties, and confers no rights.
Regards,
Bill Cheng
Microsoft Support Engineer
--
| From: "Bryan Dove" <bryan.dove@.nospam.ndchealth.com>
| References: <eLkA51ugDHA.3324@.TK2MSFTNGP11.phx.gbl>
<uooxAywgDHA.2456@.TK2MSFTNGP12.phx.gbl>
<#yiyZIDhDHA.2080@.TK2MSFTNGP12.phx.gbl>
<ADWtU5IhDHA.2272@.cpmsftngxa06.phx.gbl>
| Subject: Re: OLTP Best Practices
| Date: Sun, 28 Sep 2003 19:26:10 -0400
| Lines: 201
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.3790.0
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Message-ID: <OWK$ofhhDHA.616@.TK2MSFTNGP11.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: pcp648536pcs.mkethn01.fl.comcast.net 68.35.203.37
| Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP11.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:308722
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| Good advice on the tlog backups.
|
| What about the sp_updatestats. How often should that take place? Our
| database is an OLTP, third normal form, about 20,000,000 trans per day.
|
| Thanks
|
| Bryan Dove
|
| ""Bill Cheng [MSFT]"" <billchng@.online.microsoft.com> wrote in message
| news:ADWtU5IhDHA.2272@.cpmsftngxa06.phx.gbl...
| > Hi Bryan,
| >
| > I think how often the transaction log backup should be run is dependent
on
| > your actual requirements. For example, if you have the following backup
| > plan:
| > Time Event
| > 8:00 A.M. Back up database
| > Noon Back up transaction log
| > 4:00 P.M. Back up transaction log
| > 6:00 P.M. Back up database
| > 8:00 P.M. Back up transaction log
| > 10:00 P.M. Failure occurs
| >
| > Then we may lose the data between 8PM and 10PM.
| >
| > In "Analyzing Availability and Recovery Requirements" topic in SQL
Server
| > 2000 Books Online, there are some basic questions to help you analyze
your
| > availability and recovery requirements:
| >
| > What are your availability requirements? What portion of each day must
the
| > database be online?
| >
| >
| > What is the financial cost of downtime to your business?
| >
| >
| > If you experience media failure, such as a failing disk drive, what is
the
| > acceptable downtime?
| >
| >
| > In case of a disaster, such as the loss of a server in a fire, what is
the
| > acceptable downtime?
| >
| >
| > How important is it to never lose a change?
| >
| >
| > How easy would it be to re-create lost data?
| >
| >
| > Does your organization employ system or database administrators?
| >
| >
| > Who will be responsible for performing backup and recovery operations,
and
| > how will they be trained?
| > Here are some questions to help you choose the tools, techniques, and
| > hardware appropriate for your site:
| >
| > How large is each database?
| >
| >
| > How often does the data in each database change?
| >
| >
| > Are some tables modified more often than others?
| >
| >
| > What are your critical database production periods?
| >
| >
| > When does the database experience heavy use, resulting in frequent
inserts
| > and updates?
| >
| >
| > Is transaction log space consumption likely to be a problem due to heavy
| > update activity?
| >
| >
| > Is your database subject to periodic bulk data loading?
| >
| >
| > Is your database subject to risky updates or application errors that may
| > not be detected immediately?
| >
| >
| > Is your database server part of a SQL Server 2000 failover cluster for
| high
| > availability?
| >
| >
| > Is your database in a multi-server environment with centralized
| > administration?
| >
| >
| >
| > "Planning for Disaster Recovery"
| >
|
<http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/a
| > d_bkprst_7kvb.asp>
| >
| > Designing a Backup and Restore Strategy
| >
|
<http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/a
| > d_bkprst_63eh.asp>
| >
| > - Support WebCast: SQL Server 2000 Database Recovery: Backup and Restore
| >
|
<http://support.microsoft.com/default.aspx?scid=/servicedesks/webcasts/wc120
| > 500/wcblurb120500.asp>
| >
| > - INF: Disaster Recovery Planning for SQL Server
| > <http://support.microsoft.com/?kbid=169039>
| >
| >
|
<http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtech
| > nol/sql/reskit/sql2000/part4/c1561.asp>
| >
| > In addition, making some experiments and comparisons would be necessary
in
| > a production system, as it is more effective.
| >
| >
| > Bill Cheng
| > Microsoft Online Partner Support
| >
| > Get Secure! - www.microsoft.com/security
| > This posting is provided "as is" with no warranties and confers no
rights.
| > --
| > | From: "Bryan Dove" <bryan.dove@.nospam.ndchealth.com>
| > | References: <eLkA51ugDHA.3324@.TK2MSFTNGP11.phx.gbl>
| > <uooxAywgDHA.2456@.TK2MSFTNGP12.phx.gbl>
| > | Subject: Re: OLTP Best Practices
| > | Date: Fri, 26 Sep 2003 09:28:03 -0400
| > | Lines: 46
| > | X-Priority: 3
| > | X-MSMail-Priority: Normal
| > | X-Newsreader: Microsoft Outlook Express 6.00.3790.0
| > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| > | Message-ID: <#yiyZIDhDHA.2080@.TK2MSFTNGP12.phx.gbl>
| > | Newsgroups: microsoft.public.sqlserver.server
| > | NNTP-Posting-Host: 12.5.227.202
| > | Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP12.phx.gbl
| > | Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:308507
| > | X-Tomcat-NG: microsoft.public.sqlserver.server
| > |
| > | I am looking for some general best practices. Such as how often should
| > | sp_updatestats be run, how often should the t-log be backed up, etc...
| We
| > | are having a debate over what is necessary, versus what is overkill
for
| > many
| > | of the maintenance-type processes.
| > |
| > | Thanks
| > |
| > | Bryan Dove
| > |
| > |
| > |
| > | "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
| > | news:uooxAywgDHA.2456@.TK2MSFTNGP12.phx.gbl...
| > | > They are all good practices for most OLTP systems but how, when etc
| > | depends
| > | > a lot on each individual app's needs. Maybe this is a good place to
| > | start.
| > | >
| > | >
| > |
| >
|
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechn
| > ol/sql/maintain/operate/opsguide/default.asp
| > | >
| > | > --
| > | >
| > | > Andrew J. Kelly
| > | > SQL Server MVP
| > | >
| > | >
| > | > "Bryan Dove" <bryan.dove@.nospam.ndchealth.com> wrote in message
| > | > news:eLkA51ugDHA.3324@.TK2MSFTNGP11.phx.gbl...
| > | > > Are there any best practices for high volume OLTP systems (20M
trans
| /
| > | > day)?
| > | > > Such as sp_updatestats, index defrag, file group alignment, online
| > | backup,
| > | > > etc...
| > | > >
| > | > > Any advice is greatly appreciated.
| > | > >
| > | > > Thanks
| > | > >
| > | > > Bryan Dove
| > | > >
| > | > >
| > | >
| > | >
| > |
| > |
| > |
| >
|
|
|

OleDB not returning a empty cursor.

I am using SQLServer 2000 on multiple Windows operating systems and
the application accessing the database are also on multiple Windows
OSs namely Windows 2000 server, Windows 2003 Server, Windows 2000 and
Windows XP. The issue is only noticed when the database resides on
windows 2003 box. The application uses a single connection to call 4
stored procedures sequentially. The first 3 stored procedures do not
return any cursor (just parameter info). But the 4th stored proc is
expected to return a cursor with 0 or more records. When this app is
executed against a SQL server residing on a 2000 server, it returns a
rowset with no rows but all the metadata information is available
(cursor field names).
But in case of Windows 2003 box, when the stored proc has no records
to return (empty cursor), the rowset is set to nil. Basically as part
of the OleDb interface I am calling ICommand
HRESULT Execute (
IUnknown *pUnkOuter,
REFIID riid,
DBPARAMS *pParams,
DBROWCOUNT *pcRowsAffected,
IUnknown **ppRowset);\
In the 2000 server, the IUnknown is a pointer after the execution, but
in 2003, the value is nil. Does anyone know if the SQLServer provider
has been modified to return a nil in case of a empty cursor or is this
a bug? Also if anyone knows of any work arounds or fixes, I would
greatly appreciate it if you could share it with me.
The 2003 box has MDAC 2.8 RTM, while the rest of the boxes have MDAC
2.7x.
Thanks,
SubraSubra (subramanyan.ramanathan@.gmail.com) writes:
> I am using SQLServer 2000 on multiple Windows operating systems and
> the application accessing the database are also on multiple Windows
> OSs namely Windows 2000 server, Windows 2003 Server, Windows 2000 and
> Windows XP. The issue is only noticed when the database resides on
> windows 2003 box. The application uses a single connection to call 4
> stored procedures sequentially. The first 3 stored procedures do not
> return any cursor (just parameter info). But the 4th stored proc is
> expected to return a cursor with 0 or more records. When this app is
> executed against a SQL server residing on a 2000 server, it returns a
> rowset with no rows but all the metadata information is available
> (cursor field names).
> But in case of Windows 2003 box, when the stored proc has no records
> to return (empty cursor), the rowset is set to nil. Basically as part
> of the OleDb interface I am calling ICommand
> HRESULT Execute (
> IUnknown *pUnkOuter,
> REFIID riid,
> DBPARAMS *pParams,
> DBROWCOUNT *pcRowsAffected,
> IUnknown **ppRowset);\
> In the 2000 server, the IUnknown is a pointer after the execution, but
> in 2003, the value is nil. Does anyone know if the SQLServer provider
> has been modified to return a nil in case of a empty cursor or is this
> a bug? Also if anyone knows of any work arounds or fixes, I would
> greatly appreciate it if you could share it with me.
It is not really clear to me. Do you get this problem when you connect
to the SQL Server residing on the Windows 2003 box, no matter which
operating system the client is on?
If that is the case, I can't see that MDAC versions has anything to
do with it, but there is something on the server, that is causing the
NULL pointer.
A few more questions:
o If the stored procedure returns data, do you get a pointer in this
case?
o What is the return code of ICommand::Execute?
o What do you pass for REFIID?
o You talk about cursor. Is that a really true server-side cursor, or
is it just a result set that you get back?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thank you for the response.
Yes, the issue happens when I connect to a SQL server residing on a
Windows 2003 box, regardless of the O/s the app is on (have tried
running the app on Windows 2003 and Windows XP).
o If the stored procedure returns data, do you get a pointer in this
case? - Yes, I do get a pointer when a recordset is present.
o What is the return code of ICommand::Execute? - The return code is
0, which means successful
o What do you pass for REFIID? - IID_IUnknown: TGUID =
'{00000000-0000-0000-C000-000000000046}';
o You talk about cursor. Is that a really true server-side cursor,
or
is it just a result set that you get back? - It is just a
resultset.
Thank you.
Subra.
Erland Sommarskog <esquel@.sommarskog.se> wrote in message news:<Xns9599EFD3E715FYazorman@.127
.0.0.1>...
> Subra (subramanyan.ramanathan@.gmail.com) writes:
> It is not really clear to me. Do you get this problem when you connect
> to the SQL Server residing on the Windows 2003 box, no matter which
> operating system the client is on?
> If that is the case, I can't see that MDAC versions has anything to
> do with it, but there is something on the server, that is causing the
> NULL pointer.
> A few more questions:
> o If the stored procedure returns data, do you get a pointer in this
> case?
> o What is the return code of ICommand::Execute?
> o What do you pass for REFIID?
> o You talk about cursor. Is that a really true server-side cursor, or
> is it just a result set that you get back?|||Subra (subramanyan.ramanathan@.gmail.com) writes:
> Thank you for the response.
> Yes, the issue happens when I connect to a SQL server residing on a
> Windows 2003 box, regardless of the O/s the app is on (have tried
> running the app on Windows 2003 and Windows XP).
> o If the stored procedure returns data, do you get a pointer in this
> case? - Yes, I do get a pointer when a recordset is present.
> o What is the return code of ICommand::Execute? - The return code is
> 0, which means successful
> o What do you pass for REFIID? - IID_IUnknown: TGUID =
> '{00000000-0000-0000-C000-000000000046}';
> o You talk about cursor. Is that a really true server-side cursor,
> or
> is it just a result set that you get back? - It is just a
> resultset.
What strikes me as odd is the use if IID_IUnknown. Normally you would
use IID_IRowset or IID_IMultipleResults. I don't know if this has anything
to do with it.
But since the error is independent of which OS the client is on, I am
more inclined to think that there is a difference between the code
running on the two SQL Servers, or the their configuration. I can't
see that the MDAC version should matter.
Could you post the code for the stored procedure? Preferably take it from
the Win2003 database. Also the C++ code from where you create the
command up to the point of execution helps. There are a few variations
with prepared statements, calling syntax etc. It is difficult to recreate
you scenario, without knowing what you are doing.
--
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,
I managed to figure out the issue just recently. The reason for the
issue is that in the stored proc that had the issue, I am using some
temp tables to return data. Supposedly in the newer version of SQL
Server OLEDB provider (SQLOLEDB) they return a result of each
statement that gets executed in the stored proc. This and the
combination of having temp tables in the stored proc results in some
errors and hence no cursor is returned. So the recommended fix for
this issue is to have the setting "SET NOCOUNT ON". I am attaching a
link to the article on the microsoft website.
http://support.microsoft.com/defaul...kb;en-us;235340
Thank you for your assistance.
Regards,
Subra.
Erland Sommarskog <esquel@.sommarskog.se> wrote in message news:<Xns959CD7F71B5EFYazorman@.127
.0.0.1>...
> Subra (subramanyan.ramanathan@.gmail.com) writes:
> What strikes me as odd is the use if IID_IUnknown. Normally you would
> use IID_IRowset or IID_IMultipleResults. I don't know if this has anything
> to do with it.
> But since the error is independent of which OS the client is on, I am
> more inclined to think that there is a difference between the code
> running on the two SQL Servers, or the their configuration. I can't
> see that the MDAC version should matter.
> Could you post the code for the stored procedure? Preferably take it from
> the Win2003 database. Also the C++ code from where you create the
> command up to the point of execution helps. There are a few variations
> with prepared statements, calling syntax etc. It is difficult to recreate
> you scenario, without knowing what you are doing.|||Subra (subramanyan.ramanathan@.gmail.com) writes:
> I managed to figure out the issue just recently. The reason for the
> issue is that in the stored proc that had the issue, I am using some
> temp tables to return data. Supposedly in the newer version of SQL
> Server OLEDB provider (SQLOLEDB) they return a result of each
> statement that gets executed in the stored proc. This and the
> combination of having temp tables in the stored proc results in some
> errors and hence no cursor is returned. So the recommended fix for
> this issue is to have the setting "SET NOCOUNT ON". I am attaching a
> link to the article on the microsoft website.
> http://support.microsoft.com/defaul...kb;en-us;235340
Glad to hear that you were able to resovle the issue!
There is not really any change in the basic behaviour. By default SQL
Server returns a "rows affected" message for each INSERT, DELETE and UPDATE
statement. With most client libraries you get this as count without a result
set. If your code does not handle this, and only looks for the first result
set, all you get is the first "rows affected" message, but no rowset pointer
or the equivalent.
In many cases, these rowcounts are of little interest, so submitting SET
NOCOUNT ON, kills two birds with two stones: you get the data you are
looking for in the first result set, and you improve performance, since
you reduce network traffic.
My recommendation, though, is to use IMultipleResults and get all result
sets, anyway. This makes the code more robust, not the least with regards
to catching errors and PRINT messages.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

OleDB not returning a empty cursor.

I am using SQLServer 2000 on multiple Windows operating systems and
the application accessing the database are also on multiple Windows
OSs namely Windows 2000 server, Windows 2003 Server, Windows 2000 and
Windows XP. The issue is only noticed when the database resides on
windows 2003 box. The application uses a single connection to call 4
stored procedures sequentially. The first 3 stored procedures do not
return any cursor (just parameter info). But the 4th stored proc is
expected to return a cursor with 0 or more records. When this app is
executed against a SQL server residing on a 2000 server, it returns a
rowset with no rows but all the metadata information is available
(cursor field names).
But in case of Windows 2003 box, when the stored proc has no records
to return (empty cursor), the rowset is set to nil. Basically as part
of the OleDb interface I am calling ICommand
HRESULT Execute (
IUnknown *pUnkOuter,
REFIID riid,
DBPARAMS *pParams,
DBROWCOUNT *pcRowsAffected,
IUnknown **ppRowset);\
In the 2000 server, the IUnknown is a pointer after the execution, but
in 2003, the value is nil. Does anyone know if the SQLServer provider
has been modified to return a nil in case of a empty cursor or is this
a bug? Also if anyone knows of any work arounds or fixes, I would
greatly appreciate it if you could share it with me.
The 2003 box has MDAC 2.8 RTM, while the rest of the boxes have MDAC
2.7x.
Thanks,
Subra
Subra (subramanyan.ramanathan@.gmail.com) writes:
> I am using SQLServer 2000 on multiple Windows operating systems and
> the application accessing the database are also on multiple Windows
> OSs namely Windows 2000 server, Windows 2003 Server, Windows 2000 and
> Windows XP. The issue is only noticed when the database resides on
> windows 2003 box. The application uses a single connection to call 4
> stored procedures sequentially. The first 3 stored procedures do not
> return any cursor (just parameter info). But the 4th stored proc is
> expected to return a cursor with 0 or more records. When this app is
> executed against a SQL server residing on a 2000 server, it returns a
> rowset with no rows but all the metadata information is available
> (cursor field names).
> But in case of Windows 2003 box, when the stored proc has no records
> to return (empty cursor), the rowset is set to nil. Basically as part
> of the OleDb interface I am calling ICommand
> HRESULT Execute (
> IUnknown *pUnkOuter,
> REFIID riid,
> DBPARAMS *pParams,
> DBROWCOUNT *pcRowsAffected,
> IUnknown **ppRowset);\
> In the 2000 server, the IUnknown is a pointer after the execution, but
> in 2003, the value is nil. Does anyone know if the SQLServer provider
> has been modified to return a nil in case of a empty cursor or is this
> a bug? Also if anyone knows of any work arounds or fixes, I would
> greatly appreciate it if you could share it with me.
It is not really clear to me. Do you get this problem when you connect
to the SQL Server residing on the Windows 2003 box, no matter which
operating system the client is on?
If that is the case, I can't see that MDAC versions has anything to
do with it, but there is something on the server, that is causing the
NULL pointer.
A few more questions:
o If the stored procedure returns data, do you get a pointer in this
case?
o What is the return code of ICommand::Execute?
o What do you pass for REFIID?
o You talk about cursor. Is that a really true server-side cursor, or
is it just a result set that you get back?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
|||Thank you for the response.
Yes, the issue happens when I connect to a SQL server residing on a
Windows 2003 box, regardless of the O/s the app is on (have tried
running the app on Windows 2003 and Windows XP).
o If the stored procedure returns data, do you get a pointer in this
case? - Yes, I do get a pointer when a recordset is present.
o What is the return code of ICommand::Execute? - The return code is
0, which means successful
o What do you pass for REFIID? - IID_IUnknown: TGUID =
'{00000000-0000-0000-C000-000000000046}';
o You talk about cursor. Is that a really true server-side cursor,
or
is it just a result set that you get back? - It is just a
resultset.
Thank you.
Subra.
Erland Sommarskog <esquel@.sommarskog.se> wrote in message news:<Xns9599EFD3E715FYazorman@.127.0.0.1>...
> Subra (subramanyan.ramanathan@.gmail.com) writes:
> It is not really clear to me. Do you get this problem when you connect
> to the SQL Server residing on the Windows 2003 box, no matter which
> operating system the client is on?
> If that is the case, I can't see that MDAC versions has anything to
> do with it, but there is something on the server, that is causing the
> NULL pointer.
> A few more questions:
> o If the stored procedure returns data, do you get a pointer in this
> case?
> o What is the return code of ICommand::Execute?
> o What do you pass for REFIID?
> o You talk about cursor. Is that a really true server-side cursor, or
> is it just a result set that you get back?
|||Subra (subramanyan.ramanathan@.gmail.com) writes:
> Thank you for the response.
> Yes, the issue happens when I connect to a SQL server residing on a
> Windows 2003 box, regardless of the O/s the app is on (have tried
> running the app on Windows 2003 and Windows XP).
> o If the stored procedure returns data, do you get a pointer in this
> case? - Yes, I do get a pointer when a recordset is present.
> o What is the return code of ICommand::Execute? - The return code is
> 0, which means successful
> o What do you pass for REFIID? - IID_IUnknown: TGUID =
> '{00000000-0000-0000-C000-000000000046}';
> o You talk about cursor. Is that a really true server-side cursor,
> or
> is it just a result set that you get back? - It is just a
> resultset.
What strikes me as odd is the use if IID_IUnknown. Normally you would
use IID_IRowset or IID_IMultipleResults. I don't know if this has anything
to do with it.
But since the error is independent of which OS the client is on, I am
more inclined to think that there is a difference between the code
running on the two SQL Servers, or the their configuration. I can't
see that the MDAC version should matter.
Could you post the code for the stored procedure? Preferably take it from
the Win2003 database. Also the C++ code from where you create the
command up to the point of execution helps. There are a few variations
with prepared statements, calling syntax etc. It is difficult to recreate
you scenario, without knowing what you are doing.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
|||Erland,
I managed to figure out the issue just recently. The reason for the
issue is that in the stored proc that had the issue, I am using some
temp tables to return data. Supposedly in the newer version of SQL
Server OLEDB provider (SQLOLEDB) they return a result of each
statement that gets executed in the stored proc. This and the
combination of having temp tables in the stored proc results in some
errors and hence no cursor is returned. So the recommended fix for
this issue is to have the setting "SET NOCOUNT ON". I am attaching a
link to the article on the microsoft website.
http://support.microsoft.com/default...b;en-us;235340
Thank you for your assistance.
Regards,
Subra.
Erland Sommarskog <esquel@.sommarskog.se> wrote in message news:<Xns959CD7F71B5EFYazorman@.127.0.0.1>...
> Subra (subramanyan.ramanathan@.gmail.com) writes:
> What strikes me as odd is the use if IID_IUnknown. Normally you would
> use IID_IRowset or IID_IMultipleResults. I don't know if this has anything
> to do with it.
> But since the error is independent of which OS the client is on, I am
> more inclined to think that there is a difference between the code
> running on the two SQL Servers, or the their configuration. I can't
> see that the MDAC version should matter.
> Could you post the code for the stored procedure? Preferably take it from
> the Win2003 database. Also the C++ code from where you create the
> command up to the point of execution helps. There are a few variations
> with prepared statements, calling syntax etc. It is difficult to recreate
> you scenario, without knowing what you are doing.
|||Subra (subramanyan.ramanathan@.gmail.com) writes:
> I managed to figure out the issue just recently. The reason for the
> issue is that in the stored proc that had the issue, I am using some
> temp tables to return data. Supposedly in the newer version of SQL
> Server OLEDB provider (SQLOLEDB) they return a result of each
> statement that gets executed in the stored proc. This and the
> combination of having temp tables in the stored proc results in some
> errors and hence no cursor is returned. So the recommended fix for
> this issue is to have the setting "SET NOCOUNT ON". I am attaching a
> link to the article on the microsoft website.
> http://support.microsoft.com/default...b;en-us;235340
Glad to hear that you were able to resovle the issue!
There is not really any change in the basic behaviour. By default SQL
Server returns a "rows affected" message for each INSERT, DELETE and UPDATE
statement. With most client libraries you get this as count without a result
set. If your code does not handle this, and only looks for the first result
set, all you get is the first "rows affected" message, but no rowset pointer
or the equivalent.
In many cases, these rowcounts are of little interest, so submitting SET
NOCOUNT ON, kills two birds with two stones: you get the data you are
looking for in the first result set, and you improve performance, since
you reduce network traffic.
My recommendation, though, is to use IMultipleResults and get all result
sets, anyway. This makes the code more robust, not the least with regards
to catching errors and PRINT messages.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

OLE DB error trace Non-interface error.

Hi ,
When I try running the below sql on MSDE i am getting this error, Can some
one pls help.
It works fine on other systems where SQL Server 2000 is installed.
Select * into Brand FROM OPENROWSET('MSDASQL.1','test1';'sa';'pat','select
* from [BRAND]')
This is the detail error msg when when i set TRACEON (7300, 3604).
Server: Msg 7330, Level 16, State 2, Line 1
Could not fetch a row from OLE DB provider 'MSDASQL.1'.
OLE DB error trace [Non-interface error: OLE DB provider MSDASQL.1 returned
an incorrect value for DBPROP_CONCATNULLBEHAVIOR which should be either
DBPROPVAL_CB_NULL or DBPROPVAL_CB_NON_NULL].
OLE DB error trace [OLE/DB Provider 'MSDASQL.1' IRowset::GetNextRows
returned 0x80040155].
MSDE version
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Desktop Engine on Windows NT 5.2 (Build 3790: )
SQl Server Driver Version : 2000.85.1022.00
Thanks & Regards,
Ashish.Ashish P K wrote:
> Hi ,
> When I try running the below sql on MSDE i am getting this error,
> Can some one pls help.
> It works fine on other systems where SQL Server 2000 is installed.
>
> Select * into Brand FROM
> OPENROWSET('MSDASQL.1','test1';'sa';'pat','select * from [BRAND]')
Why not use the native SQL Server provider? SQLOLEDB instead of MSDASQL.
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Hi,
When I tried with SQLOLEDB it worked, but I need to use DSN connection
with the OPENROWSET, So I think , need to use MSDASQL only.
Sql Server (MSDE) is on windows 2003 machine..whether this can cause any
problem?
Regards,
Ashish.
"Bob Barrows [MVP]" <reb01501@.NOyahoo.SPAMcom> wrote in message
news:%23Bq1SfRDFHA.3120@.TK2MSFTNGP12.phx.gbl...
> Ashish P K wrote:
> Why not use the native SQL Server provider? SQLOLEDB instead of MSDASQL.
>
> Bob Barrows
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>|||Ashish P K wrote:
> Hi,
> When I tried with SQLOLEDB it worked, but I need to use DSN
> connection with the OPENROWSET,
Why?

> So I think , need to use MSDASQL only.
> Sql Server (MSDE) is on windows 2003 machine..whether this can cause
> any problem?
MSDASQL has be deprecated for at least 3 versions of MDAC. New functionality
that has been added to SQLOLEDB in those three revisions may not be provided
by MSDASQL.
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

old version of query analyzser for 2000

I have a SQL server 2000 systems that is likely going to be upgraded in the near future. However I need to look at the old data and system to recontruct the business cases that went in to the development. I'm trying to find an old copy of the query analyzer for SQL 2000 while I'm waiting for access to terminal services on the box. I've tried looking on Microsoft site but I'm unable to retrieve the download. Does anybody know where I can get a copy?

Hi,

You can use the Sql Management Express which works like a Query Analyzer + Enterprise Manager. It is for SQL2005 though you can still connect to SQL200 databases.

http://msdn.microsoft.com/vstudio/express/sql/

note: Find the download for SQL Server Management Studio Express

Or you can use a third party product like this:

http://www.sqlmanager.net/en/products/mssql/manager/download

cheers,

Paul June A. Domag

|||

Thanks,