Hi,
I am currently using ORACLE 9i but I am trying to move to SQL server.
Below is my code, I am getting very strenge error, I can retrieve all the
data from database, but when I do .Edit(), the code crashes. Eventhough, I
am opening the recordset dynaset, it still does not let me edit the
recordset. I have SQL server(8.00.194), MDAC(2.71.9030.0), ODBC32.dll(
version 3.520.9030.0). Is my ODBC drive old?
******************my code*********************************
m_compInStream = new CComponentsInStream(Database);
m_compInStream->Open(CRecordset::dynaset,"select * from
tblComponentsInStream where FLUIDSTREAMSID = '12' ORDER BY
COMPONENTSID",CRecordset::none);
m_compInStream->Edit(); //Crashes here...
************************************************** ***********
Your SQL Server version is the SQL 8.0 RTM version. I beleive the latest is
SP4, so you could try a newer one. Try
http://www.microsoft.com/downloads/r...etext=sql+serv
er&DisplayLang=en
You could also try doing a search on http://msdn.microsoft.com/ using
keywords "ODBC MFC". Perhaps there is some difference between Oracle and
SQL that doesn't allow this query.
| From: "Bhavin Patel" <bpatel@.epcon.com>
| Subject: ODBC driver for SQL server using mfc
| Date: Tue, 13 Sep 2005 00:18:07 -0500
| Lines: 18
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
| Message-ID: <uPipLKCuFHA.1028@.TK2MSFTNGP12.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.odbc
| NNTP-Posting-Host: h86.74.29.71.ip.alltel.net 71.29.74.86
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFT NGP12.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.odbc:2700
| X-Tomcat-NG: microsoft.public.sqlserver.odbc
|
| Hi,
| I am currently using ORACLE 9i but I am trying to move to SQL server.
| Below is my code, I am getting very strenge error, I can retrieve all the
| data from database, but when I do .Edit(), the code crashes. Eventhough, I
| am opening the recordset dynaset, it still does not let me edit the
| recordset. I have SQL server(8.00.194), MDAC(2.71.9030.0), ODBC32.dll(
| version 3.520.9030.0). Is my ODBC drive old?
|
| ******************my code*********************************
| m_compInStream = new CComponentsInStream(Database);
| m_compInStream->Open(CRecordset::dynaset,"select * from
| tblComponentsInStream where FLUIDSTREAMSID = '12' ORDER BY
| COMPONENTSID",CRecordset::none);
| m_compInStream->Edit(); //Crashes here...
| ************************************************** ***********
|
|
|
|
Showing posts with label mfc. Show all posts
Showing posts with label mfc. Show all posts
Wednesday, March 28, 2012
ODBC driver for SQL server using mfc
Hi,
I am currently using ORACLE 9i but I am trying to move to SQL server.
Below is my code, I am getting very strenge error, I can retrieve all the
data from database, but when I do .Edit(), the code crashes. Eventhough, I
am opening the recordset dynaset, it still does not let me edit the
recordset. I have SQL server(8.00.194), MDAC(2.71.9030.0), ODBC32.dll(
version 3.520.9030.0). Is my ODBC drive old?
******************my code*********************************
m_compInStream = new CComponentsInStream(Database);
m_compInStream->Open(CRecordset::dynaset,"select * from
tblComponentsInStream where FLUIDSTREAMSID = '12' ORDER BY
COMPONENTSID",CRecordset::none);
m_compInStream->Edit(); //Crashes here...
****************************************
*********************Your SQL Server version is the SQL 8.0 RTM version. I beleive the latest is
SP4, so you could try a newer one. Try
http://www.microsoft.com/downloads/...eetext=sql+serv
er&DisplayLang=en
You could also try doing a search on http://msdn.microsoft.com/ using
keywords "ODBC MFC". Perhaps there is some difference between Oracle and
SQL that doesn't allow this query.
| From: "Bhavin Patel" <bpatel@.epcon.com>
| Subject: ODBC driver for SQL server using mfc
| Date: Tue, 13 Sep 2005 00:18:07 -0500
| Lines: 18
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
| Message-ID: <uPipLKCuFHA.1028@.TK2MSFTNGP12.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.odbc
| NNTP-Posting-Host: h86.74.29.71.ip.alltel.net 71.29.74.86
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP12.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.odbc:2700
| X-Tomcat-NG: microsoft.public.sqlserver.odbc
|
| Hi,
| I am currently using ORACLE 9i but I am trying to move to SQL server.
| Below is my code, I am getting very strenge error, I can retrieve all the
| data from database, but when I do .Edit(), the code crashes. Eventhough, I
| am opening the recordset dynaset, it still does not let me edit the
| recordset. I have SQL server(8.00.194), MDAC(2.71.9030.0), ODBC32.dll(
| version 3.520.9030.0). Is my ODBC drive old?
|
| ******************my code*********************************
| m_compInStream = new CComponentsInStream(Database);
| m_compInStream->Open(CRecordset::dynaset,"select * from
| tblComponentsInStream where FLUIDSTREAMSID = '12' ORDER BY
| COMPONENTSID",CRecordset::none);
| m_compInStream->Edit(); //Crashes here...
| ****************************************
*********************
|
|
|
|sql
I am currently using ORACLE 9i but I am trying to move to SQL server.
Below is my code, I am getting very strenge error, I can retrieve all the
data from database, but when I do .Edit(), the code crashes. Eventhough, I
am opening the recordset dynaset, it still does not let me edit the
recordset. I have SQL server(8.00.194), MDAC(2.71.9030.0), ODBC32.dll(
version 3.520.9030.0). Is my ODBC drive old?
******************my code*********************************
m_compInStream = new CComponentsInStream(Database);
m_compInStream->Open(CRecordset::dynaset,"select * from
tblComponentsInStream where FLUIDSTREAMSID = '12' ORDER BY
COMPONENTSID",CRecordset::none);
m_compInStream->Edit(); //Crashes here...
****************************************
*********************Your SQL Server version is the SQL 8.0 RTM version. I beleive the latest is
SP4, so you could try a newer one. Try
http://www.microsoft.com/downloads/...eetext=sql+serv
er&DisplayLang=en
You could also try doing a search on http://msdn.microsoft.com/ using
keywords "ODBC MFC". Perhaps there is some difference between Oracle and
SQL that doesn't allow this query.
| From: "Bhavin Patel" <bpatel@.epcon.com>
| Subject: ODBC driver for SQL server using mfc
| Date: Tue, 13 Sep 2005 00:18:07 -0500
| Lines: 18
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
| Message-ID: <uPipLKCuFHA.1028@.TK2MSFTNGP12.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.odbc
| NNTP-Posting-Host: h86.74.29.71.ip.alltel.net 71.29.74.86
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP12.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.odbc:2700
| X-Tomcat-NG: microsoft.public.sqlserver.odbc
|
| Hi,
| I am currently using ORACLE 9i but I am trying to move to SQL server.
| Below is my code, I am getting very strenge error, I can retrieve all the
| data from database, but when I do .Edit(), the code crashes. Eventhough, I
| am opening the recordset dynaset, it still does not let me edit the
| recordset. I have SQL server(8.00.194), MDAC(2.71.9030.0), ODBC32.dll(
| version 3.520.9030.0). Is my ODBC drive old?
|
| ******************my code*********************************
| m_compInStream = new CComponentsInStream(Database);
| m_compInStream->Open(CRecordset::dynaset,"select * from
| tblComponentsInStream where FLUIDSTREAMSID = '12' ORDER BY
| COMPONENTSID",CRecordset::none);
| m_compInStream->Edit(); //Crashes here...
| ****************************************
*********************
|
|
|
|sql
ODBC Driver (for SQL Server 2005) unable to retrive CRecorset''s fields values
I am using MFC ODBC classes in my VC++ application for database accesss (on a SQL Server 2005 Database).
After opening a Recordset I have to use the CRecordset::GetFieldValue( LPCTSTR lpszName, CDBVariant& varValue, short nFieldType = DEFAULT_FIELD_TYPE ) method of the CRecordset object to obtain field values. The above method works fine with ODBC drivers for Jet but when I use ODBC for SQL Server it throws the following exception.
After opening a Recordset I have to use the CRecordset::GetFieldValue( LPCTSTR lpszName, CDBVariant& varValue, short nFieldType = DEFAULT_FIELD_TYPE ) method of the CRecordset object to obtain field values. The above method works fine with ODBC drivers for Jet but when I use ODBC for SQL Server it throws the following exception.
State:S1002,Native:0,Origin:[Microsoft][
ODBC SQL Server Driver] Invalid
Descriptor Index
I don't understand why the Microsoft SQL Server ODBC Driver is throwing this
exception.
Please help me!
Michael.
I am having the same exact problem! Does anyone have any input?
Jon
|||Me too.|||I solved my problem. My situdation is: I use MFC wizard to generate the ODBC code and all the columns have been bound. I got the same error as your guys when I call GetFieldValue(). But since all columns have been bound, don't call GetFieldValue(), instead, directly access the member variable of the derived recordset class. Then problem disappeard.
ODBC Driver (for SQL Server 2005) unable to retrive CRecorset''s fields values
I am using MFC ODBC classes in my VC++ application for database accesss (on a SQL Server 2005 Database).
After opening a Recordset I have to use the CRecordset::GetFieldValue( LPCTSTR lpszName, CDBVariant& varValue, short nFieldType = DEFAULT_FIELD_TYPE ) method of the CRecordset object to obtain field values. The above method works fine with ODBC drivers for Jet but when I use ODBC for SQL Server it throws the following exception.
After opening a Recordset I have to use the CRecordset::GetFieldValue( LPCTSTR lpszName, CDBVariant& varValue, short nFieldType = DEFAULT_FIELD_TYPE ) method of the CRecordset object to obtain field values. The above method works fine with ODBC drivers for Jet but when I use ODBC for SQL Server it throws the following exception.
State:S1002,Native:0,Origin:[Microsoft][
ODBC SQL Server Driver] Invalid
Descriptor Index
I don't understand why the Microsoft SQL Server ODBC Driver is throwing this
exception.
Please help me!
Michael.
I am having the same exact problem! Does anyone have any input?
Jon
|||Me too.|||I solved my problem. My situdation is: I use MFC wizard to generate the ODBC code and all the columns have been bound. I got the same error as your guys when I call GetFieldValue(). But since all columns have been bound, don't call GetFieldValue(), instead, directly access the member variable of the derived recordset class. Then problem disappeard.
ODBC Driver (for SQL Server 2005) unable to retrive CRecorset's fields values
I am using MFC ODBC classes in my VC++ application for database accesss (on a SQL Server 2005 Database).
After opening a Recordset I have to use the CRecordset::GetFieldValue( LPCTSTR lpszName, CDBVariant& varValue, short nFieldType = DEFAULT_FIELD_TYPE ) method of the CRecordset object to obtain field values. The above method works fine with ODBC drivers for Jet but when I use ODBC for SQL Server it throws the following exception.
After opening a Recordset I have to use the CRecordset::GetFieldValue( LPCTSTR lpszName, CDBVariant& varValue, short nFieldType = DEFAULT_FIELD_TYPE ) method of the CRecordset object to obtain field values. The above method works fine with ODBC drivers for Jet but when I use ODBC for SQL Server it throws the following exception.
State:S1002,Native:0,Origin:[Microsoft][
ODBC SQL Server Driver] Invalid
Descriptor Index
I don't understand why the Microsoft SQL Server ODBC Driver is throwing this
exception.
Please help me!
Michael.
I am having the same exact problem! Does anyone have any input?
Jon
|||Me too.|||
I solved my problem. My situdation is: I use MFC wizard to generate the ODBC code and all the columns have been bound. I got the same error as your guys when I call GetFieldValue(). But since all columns have been bound, don't call GetFieldValue(), instead, directly access the member variable of the derived recordset class. Then problem disappeard.
sql
ODBC Driver (for SQL Server 2005) unable to retrive CRecorset's fields values
I am using MFC ODBC classes in my VC++ application for database accesss (on a SQL Server 2005 Database).
After opening a Recordset I have to use the CRecordset::GetFieldValue( LPCTSTR lpszName, CDBVariant& varValue, short nFieldType = DEFAULT_FIELD_TYPE ) method of the CRecordset object to obtain field values. The above method works fine with ODBC drivers for Jet but when I use ODBC for SQL Server it throws the following exception.
After opening a Recordset I have to use the CRecordset::GetFieldValue( LPCTSTR lpszName, CDBVariant& varValue, short nFieldType = DEFAULT_FIELD_TYPE ) method of the CRecordset object to obtain field values. The above method works fine with ODBC drivers for Jet but when I use ODBC for SQL Server it throws the following exception.
State:S1002,Native:0,Origin:[Microsoft][
ODBC SQL Server Driver] Invalid
Descriptor Index
I don't understand why the Microsoft SQL Server ODBC Driver is throwing this
exception.
Please help me!
Michael.
I am having the same exact problem! Does anyone have any input?
Jon
Monday, March 19, 2012
ODBC and SQL 2000
We have a MFC app that uses ODBC to connect to the DB. Now let me get down
to the problem. We had a client which keeps saying Invalid object name
'Docs_Main'. And we have another client which works fine. Good ole SQL
profiler time to figure out what's going on. So we run our query and we get
this for the working client:
declare @.P1 int
set @.P1=84
exec sp_prepexec @.P1 output, NULL, N'SELECT
"Item_ID","NumVal","TextVal","TextMemo" FROM "Docs_Main" WHERE Item_ID like
''0B05B2A4-91CC-11d1-9FE9-00C0F00A2A2E'''
select @.P1
And for the client not working we get
declare @.P1 int
set @.P1=NULL
exec sp_prepexec @.P1 output, NULL, N'SELECT
"Item_ID","NumVal","TextVal","TextMemo" FROM "Docs_Main" WHERE Item_ID like
''0B05B2A4-91CC-11d1-9FE9-00C0F00A2A2E'''
select @.P1
The only difference appears to be in the set @.P1 = x statement. If that's
set to NULL does that cause problems, and why is that being set to NULL.
The program it's coming from is the same one.
Lance JohnsonWhy should the program explicitly set an output parameter in either case?
You would think it is an output parameter because you're interested in
seeing the output, not because you want to control the input.
"Lance Johnson" <ljohnson@.docs.com> wrote in message
news:eKS5FpMnDHA.644@.TK2MSFTNGP11.phx.gbl...
> We have a MFC app that uses ODBC to connect to the DB. Now let me get
down
> to the problem. We had a client which keeps saying Invalid object name
> 'Docs_Main'. And we have another client which works fine. Good ole SQL
> profiler time to figure out what's going on. So we run our query and we
get
> this for the working client:
> declare @.P1 int
> set @.P1=84
> exec sp_prepexec @.P1 output, NULL, N'SELECT
> "Item_ID","NumVal","TextVal","TextMemo" FROM "Docs_Main" WHERE Item_ID
like
> ''0B05B2A4-91CC-11d1-9FE9-00C0F00A2A2E'''
> select @.P1
>
> And for the client not working we get
> declare @.P1 int
> set @.P1=NULL
> exec sp_prepexec @.P1 output, NULL, N'SELECT
> "Item_ID","NumVal","TextVal","TextMemo" FROM "Docs_Main" WHERE Item_ID
like
> ''0B05B2A4-91CC-11d1-9FE9-00C0F00A2A2E'''
> select @.P1
>
> The only difference appears to be in the set @.P1 = x statement. If that's
> set to NULL does that cause problems, and why is that being set to NULL.
> The program it's coming from is the same one.
>
> Lance Johnson
>|||Well I don't really know what that @.P1 is for, but shouldn't it be the same
for all clients, meaning if one client has a number, another client would
have a number and not NULL. I've looked around for info on these prepare
statements so I could learn what's happening, but these aren't easily
findable and there's no mention in sql books online of them.
Lance Johnson
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:egz6PuMnDHA.3288@.tk2msftngp13.phx.gbl...
> Why should the program explicitly set an output parameter in either case?
> You would think it is an output parameter because you're interested in
> seeing the output, not because you want to control the input.
>
>
> "Lance Johnson" <ljohnson@.docs.com> wrote in message
> news:eKS5FpMnDHA.644@.TK2MSFTNGP11.phx.gbl...
> > We have a MFC app that uses ODBC to connect to the DB. Now let me get
> down
> > to the problem. We had a client which keeps saying Invalid object name
> > 'Docs_Main'. And we have another client which works fine. Good ole SQL
> > profiler time to figure out what's going on. So we run our query and we
> get
> > this for the working client:
> >
> > declare @.P1 int
> > set @.P1=84
> > exec sp_prepexec @.P1 output, NULL, N'SELECT
> > "Item_ID","NumVal","TextVal","TextMemo" FROM "Docs_Main" WHERE Item_ID
> like
> > ''0B05B2A4-91CC-11d1-9FE9-00C0F00A2A2E'''
> > select @.P1
> >
> >
> >
> > And for the client not working we get
> >
> > declare @.P1 int
> > set @.P1=NULL
> > exec sp_prepexec @.P1 output, NULL, N'SELECT
> > "Item_ID","NumVal","TextVal","TextMemo" FROM "Docs_Main" WHERE Item_ID
> like
> > ''0B05B2A4-91CC-11d1-9FE9-00C0F00A2A2E'''
> > select @.P1
> >
> >
> >
> > The only difference appears to be in the set @.P1 = x statement. If
that's
> > set to NULL does that cause problems, and why is that being set to NULL.
> > The program it's coming from is the same one.
> >
> >
> >
> > Lance Johnson
> >
> >
>|||> Well I don't really know what that @.P1 is for, but shouldn't it be the
same
> for all clients, meaning if one client has a number, another client would
> have a number and not NULL.
I really don't know enough about your design to agree with you.
> I've looked around for info on these prepare
> statements so I could learn what's happening, but these aren't easily
> findable and there's no mention in sql books online of them.
You could always google;
http://groups.google.com/groups?hl=en&lr=&ie=ISO-8859-1&q=sp_prepexec&btnG=Google+Search|||Hi Lance,
Thank you for using MSDN Newsgroup! It's my pleasure to assist you with your issue.
From your conservation with Aaron, I understand that you would like to know what the stored
procedure sp_prepexec is and why it cannot appear with the same value on the same
parameter @.p1. Moreover, you want to avoid this and then make both your clients work fine.
Have I fully understood you, Lance? If there is anything I misunderstood, please feel free to
post it in the newsgroup to let me know.
In fact, SP_PREPEXEC is an internal Extended Stored Procedures (XP) that provides server
support for combining an execution plan preparation and executing it into one call. If you set
"Prepared" property in Application, the application might send this command into SQL Server.
From your description, however, I'm unsure of your SQL Server and Service Pack' s version,
whether or not you used some local temp tables and the data types in your table. Detailed
information that you can provide will make things clear and help us move closer to the causes
and resolutions.
However, I'm still eager to assist you with this issue according to my experience on the
SP_PREPEXEC.
First of all, to SQL Server's 7.0, I recommend you apply SP4 that fixed the "Preparing a
Statement That References a Missing Object Incorrectly" problem. You can download this
service pack via:
http://www.microsoft.com/sql/downloads/sp4.asp
Additionally, if you use some local temp tables, the Prepare will fail since we cannot perform
the SELECT statement because the object doesn't exist yet. In this case, you should use
global temp table or a permanent table.
If you use SQL 2000 (with MDAC 2.6 or higher), based on my experience, it will start using
sp_prepexec instead of sp_prepare & sp_execute to implement SQLPrepare and
SQLExecute commands from ODBC, which was done to eliminate round trips to the server
whenever possible.
This is not documented anywhere as this is an implementation done at the server level and
should be transparent to the user. If Prepare first and Execute later were somehow forced, the
Prepare would fail in SQL 2000 as well. In this case, please make sure that your server is able
to Prepare and Execute AT THE SAME TIME.
Lance, does this answer your question? Please feel free to post in the group if this solves your
problem or if you would like further assistance.
Best regards,
Billy Yao
Microsoft Online Partner Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||We are currently using SQL 2000 with SP3a. We are not using temp tables.
The tables are regular tables. Let me know if you need more info in this
area. Following is the code that we are using. Some of it is not
explicitly obvious and this is not my code, so I'm just passing it along.
Let me know what else you might need to know.
BOOL CSW_UsersCtrl::DoWeTurnOnSecurity()
{
BOOL SecurityOn = FALSE;
try
{
CSOAPwareSettings theDB(g_DB_USERS);
theDB.m_strFilter = "Item_ID like '";
theDB.m_strFilter += DOCS_SECURITY_COUNT_ID;
theDB.m_strFilter += "'";
try{
theDB.Open();
}catch(CException* e){
DisplayErrorMessage("DoWeTurnOnSecurity #1",e);
return FALSE;
}
...more code but it's failing above
}
So the g_DB_Users is just the access to our database where the table exists.
And the error is coming from the catch statement so an error is being
generated from the call to open theDB.
Lance Johnson
"Billy Yao [MSFT]" <v-binyao@.online.microsoft.com> wrote in message
news:HwZRGjTnDHA.1804@.cpmsftngxa06.phx.gbl...
> Hi Lance,
> Thank you for using MSDN Newsgroup! It's my pleasure to assist you with
your issue.
> From your conservation with Aaron, I understand that you would like to
know what the stored
> procedure sp_prepexec is and why it cannot appear with the same value on
the same
> parameter @.p1. Moreover, you want to avoid this and then make both your
clients work fine.
> Have I fully understood you, Lance? If there is anything I misunderstood,
please feel free to
> post it in the newsgroup to let me know.
> In fact, SP_PREPEXEC is an internal Extended Stored Procedures (XP) that
provides server
> support for combining an execution plan preparation and executing it into
one call. If you set
> "Prepared" property in Application, the application might send this
command into SQL Server.
> From your description, however, I'm unsure of your SQL Server and Service
Pack' s version,
> whether or not you used some local temp tables and the data types in your
table. Detailed
> information that you can provide will make things clear and help us move
closer to the causes
> and resolutions.
> However, I'm still eager to assist you with this issue according to my
experience on the
> SP_PREPEXEC.
> First of all, to SQL Server's 7.0, I recommend you apply SP4 that fixed
the "Preparing a
> Statement That References a Missing Object Incorrectly" problem. You can
download this
> service pack via:
> http://www.microsoft.com/sql/downloads/sp4.asp
> Additionally, if you use some local temp tables, the Prepare will fail
since we cannot perform
> the SELECT statement because the object doesn't exist yet. In this case,
you should use
> global temp table or a permanent table.
> If you use SQL 2000 (with MDAC 2.6 or higher), based on my experience, it
will start using
> sp_prepexec instead of sp_prepare & sp_execute to implement SQLPrepare and
> SQLExecute commands from ODBC, which was done to eliminate round trips to
the server
> whenever possible.
> This is not documented anywhere as this is an implementation done at the
server level and
> should be transparent to the user. If Prepare first and Execute later were
somehow forced, the
> Prepare would fail in SQL 2000 as well. In this case, please make sure
that your server is able
> to Prepare and Execute AT THE SAME TIME.
> Lance, does this answer your question? Please feel free to post in the
group if this solves your
> problem or if you would like further assistance.
>
> Best regards,
>
> Billy Yao
> Microsoft Online Partner Support
> ----
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only. Thanks.
>|||Hello Lance,
Thank you for your feedback! Now it's clear to me that you are using SQL Server 2000 SP3a,
not using any temp tables and there are different users accessing to your DB.
Based on my knowledge, the issue is not related to the code you are using. I agree with you
we can pass it along. In my opinion, it's also not a server side issue.
As I mentioned before, SP_PREPEXEC first PREPARE for the execution plan (can be re-used
later) and then perform the EXECUTE in the future. All the parameters passing processed are
on the client.
That means the provider on the client produces the code (you trace in the original message)
and sends it to the server provider. Therefore, the problem may be located in provider
mismatch between client and server.
According to my experience, you should first check both clients' Microsoft Data Access
Components (MDAC) version to see if it matched the server's MDAC version. I think the
working client can be suited, but it's hard to make sure if the failing client's MDAC is also ok.
Here I provide you a tool from Microsoft to check the MDAC's version for your convenience:
http://www.microsoft.com/downloads/details.aspx?FamilyId=8F0A8DF6-4A21-4B43-BF53-
14332EF092C9&displaylang=en
Please download it and I provide you step-by-step directions to help check the clients' MDAC
version:
1. Extract the executable file to the WORKING client you want to check
2. In the extracted folder, click cc.exe to run the MDAC check tool
3. In the "Component Check Dialog", select "perform analysis of machine and automatically
determine the release version". It's the default selected option.
4. Click OK on the Dialog and let the tool to check the client's MDAC version
5. Extract the executable file to the FAILING client you want to check and then follow the 2-4
steps above to check another MDAC version on your failing client.
After that, you can compare the version to see if the two clients had different MDAC.
To workaround the issue, I provide you with the following suggestion:
1. Download the working client's MDAC and apply it to the failing client to see if the problem
can be resolved
2. On the other hand, note that your server is SQL Server 2000 SP3, you can also apply MDAC
2.7 SP1 Refresh on your client to meet the provider match.
3. For downloading the MDAC, please reference the following address for detailed
information:
http://msdn.microsoft.com/library/default.asp?url=/downloads/list/dataaccess.asp
Lance, please apply my suggestion above and let me know if it helps you resolve your
problem. If there is anything more I can assist you with, please feel free to post it in the group.
Thank you for using MSDN newsgroup!
Best regards,
Billy Yao
Microsoft Online Partner Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||This was actually one of our first suspicions and we
installed MDAC 2.8 on this machine to make sure it was up
to date. I proceeded to run component checker on this
machine and all was OK it seemed. One more thing to help
in understanding this. The machine this runs on has MSDE
installed and it does the same thing when working with
our DB on that local MSDE or another MSDE/SQL from
another computer. So it's not between 1 particular
server, but for any MSDE server. Thanks for the
information you're provided so far. I hope we can
somehow figure out what's causing this.
Lance Johnson
>--Original Message--
>Hello Lance,
>Thank you for your feedback! Now it's clear to me that
you are using SQL Server 2000 SP3a,
>not using any temp tables and there are different users
accessing to your DB.
>Based on my knowledge, the issue is not related to the
code you are using. I agree with you
>we can pass it along. In my opinion, it's also not a
server side issue.
>As I mentioned before, SP_PREPEXEC first PREPARE for the
execution plan (can be re-used
>later) and then perform the EXECUTE in the future. All
the parameters passing processed are
>on the client.
>That means the provider on the client produces the code
(you trace in the original message)
>and sends it to the server provider. Therefore, the
problem may be located in provider
>mismatch between client and server.
>According to my experience, you should first check both
clients' Microsoft Data Access
>Components (MDAC) version to see if it matched the
server's MDAC version. I think the
>working client can be suited, but it's hard to make sure
if the failing client's MDAC is also ok.
>Here I provide you a tool from Microsoft to check the
MDAC's version for your convenience:
>http://www.microsoft.com/downloads/details.aspx?
FamilyId=8F0A8DF6-4A21-4B43-BF53-
>14332EF092C9&displaylang=en
>Please download it and I provide you step-by-step
directions to help check the clients' MDAC
>version:
>1. Extract the executable file to the WORKING client you
want to check
>2. In the extracted folder, click cc.exe to run the MDAC
check tool
>3. In the "Component Check Dialog", select "perform
analysis of machine and automatically
>determine the release version". It's the default
selected option.
>4. Click OK on the Dialog and let the tool to check the
client's MDAC version
>5. Extract the executable file to the FAILING client you
want to check and then follow the 2-4
>steps above to check another MDAC version on your
failing client.
>After that, you can compare the version to see if the
two clients had different MDAC.
>To workaround the issue, I provide you with the
following suggestion:
>1. Download the working client's MDAC and apply it to
the failing client to see if the problem
>can be resolved
>2. On the other hand, note that your server is SQL
Server 2000 SP3, you can also apply MDAC
>2.7 SP1 Refresh on your client to meet the provider
match.
>3. For downloading the MDAC, please reference the
following address for detailed
>information:
>http://msdn.microsoft.com/library/default.asp?
url=/downloads/list/dataaccess.asp
>Lance, please apply my suggestion above and let me know
if it helps you resolve your
>problem. If there is anything more I can assist you
with, please feel free to post it in the group.
>Thank you for using MSDN newsgroup!
>
>Best regards,
>Billy Yao
>Microsoft Online Partner Support
>----
>Get Secure! - www.microsoft.com/security
>This posting is provided "as is" with no warranties and
confers no rights.
>Please reply to newsgroups only. Thanks.
>
>.
>|||Hi Lance,
Thank you for your update!
From your description, I know that you have already installed MDAC 2.8 on this machine.
However, I'm unsure what here "this machine" represents for. Does it mean the machine your
server and DB located, or mean the machine your problematic client located?
Looking through your original message in this post, I notice that there were two clients
connecting to the server and fetch the data from your DB. One worked fine and the other failed
with "Invalid object name 'Docs_Main' " error message.
As we discussed before, the provider issue might be not on the server side machine but on
the client side machine. Therefore, I provided the MDAC check tool for your convenience to
distinguish the MDAC version between these two clients. If the versions are different, this may
be the cause that one was successful and the other was failed.
It was recommended that you applied suitable (not latest) MDAC on the problematic client
side machine. This "suitable MDAC" means you can apply "the MDAC version on the working
client" to the failing client.
Another workaround you can perform is to force sp_unprepare explicitly. In this way,
SP_PREPEXEC will not prepare the execution plan and execute the script directly.
Lance, does this answer your question? Please feel free to post in the group if this solves your
problem or if you would like further assistance.
Best regards,
Billy Yao
Microsoft Online Partner Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Sorry. Let me clarify what I mean by that machine. Our problematic client
(let's call it Laptop) has the MDAC 2.8 and we have MSDE installed on it.
It was having this problem, so we tried connecting it to another machine's
MSDE (let's call it Desktop). The same result occurred. So on Desktop we
connected to the 2 different MSDE's and it worked on both. So if the client
is connecting to a local MSDE the MDAC shouldn't (I believe) be getting in
the way. We connected to another machine's SQL just so we could run SQL
Profiler and get more info. Thanks much.
Lance Johnson
"Billy Yao [MSFT]" <v-binyao@.online.microsoft.com> wrote in message
news:XIC4jsjnDHA.2012@.cpmsftngxa06.phx.gbl...
> Hi Lance,
> Thank you for your update!
> From your description, I know that you have already installed MDAC 2.8 on
this machine.
> However, I'm unsure what here "this machine" represents for. Does it mean
the machine your
> server and DB located, or mean the machine your problematic client
located?
> Looking through your original message in this post, I notice that there
were two clients
> connecting to the server and fetch the data from your DB. One worked fine
and the other failed
> with "Invalid object name 'Docs_Main' " error message.
> As we discussed before, the provider issue might be not on the server side
machine but on
> the client side machine. Therefore, I provided the MDAC check tool for
your convenience to
> distinguish the MDAC version between these two clients. If the versions
are different, this may
> be the cause that one was successful and the other was failed.
> It was recommended that you applied suitable (not latest) MDAC on the
problematic client
> side machine. This "suitable MDAC" means you can apply "the MDAC version
on the working
> client" to the failing client.
> Another workaround you can perform is to force sp_unprepare explicitly. In
this way,
> SP_PREPEXEC will not prepare the execution plan and execute the script
directly.
> Lance, does this answer your question? Please feel free to post in the
group if this solves your
> problem or if you would like further assistance.
>
> Best regards,
>
> Billy Yao
> Microsoft Online Partner Support
> ----
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only. Thanks.
>|||Hi Lance,
Thank you for your detailed explanation.
Now it's clear that the problem is addressed on the client provider- Laptop as all the clients
could connect the MSDEs except for this problematic one.
Although you apply the MDAC 2.8 on the problematic client, the symptom may also occur with
unexpected causes. The better way to work around this issue is to apply the same MDAC
version of the WORKING client to the f problematic client (Laptop), which ensures the Laptop's
provider to use the working MDAC that can connect to the server successfully.
Please re-check your working client's MDAC version and download THIS MDAC from:
http://msdn.microsoft.com/library/default.asp?url=/downloads/list/dataaccess.asp
Then uninstall the MDAC 2.8 on the problematic client (Laptop), and install the working client's
MDAC you've downloaded. Moreover, make sure the problematic client's
configuration/environment (mainly related to provider) is the same one as the working client.
Enable them identical if possible.
To uninstall the MDAC and reconfigure it, please refer to the following article:
307255 INFO: Component Checker: Diagnose Problems and Reconfigure MDAC
http://support.microsoft.com/?id=307255
Another workaround I mentioned in the previous message is to force sp_unprepare explicitly.
In this way, SP_PREPEXEC will not prepare the execution plan and perform execution directly.
Lance, please help narrow down the problem and apply my recommendation above to see if
this solves your problem. If there is anything more I can assist you with, please feel free to post
in the group.
Best regards,
Billy Yao
Microsoft Online Partner Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Hey Lance,
For more information, you should close all applications that use ODBC in order for the MDAC
installation to succeed, as all later version of ODBC components will be installed when
applying new MDAC. Applications that use ODBC include Microsoft Internet Information Server
(IIS), Microsoft Systems Management Server, Microsoft Access, and Oracle database
applications.
The following articles help you troubleshoot the MDAC setup and list various versions of
MDAC for the SQL Server ODBC driver:
232060 HOWTO: MDAC Setup Troubleshooting Guide
http://support.microsoft.com/?id=232060
219293 INFO: ODBC, SQL Server, and Jet Versions Shipped with MDAC
http://support.microsoft.com/?id=219293
Please feel free to post in the group if this solves your problem or if you would like further
assistance.
Best regards,
Billy Yao
Microsoft Online Partner Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||I can't seem to uninstall the current MDAC. This must refer to an older
Component Checker functionality because when I run my current one with that
/d flag it doesn't let me reconfigure or anything. Since I couldn't do
that, I installer 2.8 MDAC on one of my other machines and it seems to work
fine with my application. I'm a little bit hazy on this sp_unprepare stuff.
Do you have any links about this. We have never had to force this before so
we're not accustomed to doing this.
Lance
"Billy Yao [MSFT]" <v-binyao@.online.microsoft.com> wrote in message
news:PcqgbEqnDHA.2088@.cpmsftngxa06.phx.gbl...
> Hi Lance,
> Thank you for your detailed explanation.
> Now it's clear that the problem is addressed on the client provider-
Laptop as all the clients
> could connect the MSDEs except for this problematic one.
> Although you apply the MDAC 2.8 on the problematic client, the symptom may
also occur with
> unexpected causes. The better way to work around this issue is to apply
the same MDAC
> version of the WORKING client to the f problematic client (Laptop), which
ensures the Laptop's
> provider to use the working MDAC that can connect to the server
successfully.
> Please re-check your working client's MDAC version and download THIS MDAC
from:
>
http://msdn.microsoft.com/library/default.asp?url=/downloads/list/dataaccess.asp
> Then uninstall the MDAC 2.8 on the problematic client (Laptop), and
install the working client's
> MDAC you've downloaded. Moreover, make sure the problematic client's
> configuration/environment (mainly related to provider) is the same one as
the working client.
> Enable them identical if possible.
> To uninstall the MDAC and reconfigure it, please refer to the following
article:
> 307255 INFO: Component Checker: Diagnose Problems and Reconfigure MDAC
> http://support.microsoft.com/?id=307255
> Another workaround I mentioned in the previous message is to force
sp_unprepare explicitly.
> In this way, SP_PREPEXEC will not prepare the execution plan and perform
execution directly.
> Lance, please help narrow down the problem and apply my recommendation
above to see if
> this solves your problem. If there is anything more I can assist you with,
please feel free to post
> in the group.
>
> Best regards,
> Billy Yao
> Microsoft Online Partner Support
> ----
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only. Thanks.
>|||Hi Lance
Thank you for your update. Your feedback proves again that the cause of the problem is on
the problematic machine, not on any other machines.
The MDAC Component Checker worked fine on my side and could uninstall MDAC 2.8
according to KB 307255. It's really strange that it didn't let you reconfigure the version. Well, as
MDAC 2.8 did work fine on another machine, I suggest you re-install the same MDAC 2.8 on
the machine. The reason why we should do this is the pervious MDAC 2.8 may not be applied
properly on the client. Please re-apply it.
As to sp_unprepare, it is an internal Extended Stored Procedures (XPROC) like sp_prepexec,
which the latter provides server support for combining an execution plan preparation and
executing it into one call.
There is no outer resource you can refer to because it's interal. Here I provide you a simple
sample to picture how to apply sp_unprepare explicitly:
/************************* Using sp_prepare/sp_execute
Note applications would not call these directly, but would only get these when opening a
default recordset (client cursor): forward only, read only, rowset size = 1 They must also use
SQLPrepare/SQLExecute (ODBC) or ICommandText::Prepare/ICommandText::Execute
(OLEDB)
If the calls are not prepared, the query will simply be executed normally.
***************************/
use pubs
go
declare @.P1 int set @.P1=NULL exec sp_prepare @.P1 output, N'@.P1 varchar(8)', N'select *
from titles where title_id = @.P1' exec sp_execute @.P1, 'MC2222' exec sp_unprepare @.P1
go
Please run the script above on the problematic client machine and the server machine to see
if it can work fine. If it works fine, you can apply this explicit sp_prepare, sp_execute and
sp_unprepare instead of sp_prepexec.
Please feel free to post in the group if this solves your problem or if you would like further
assistance.
Best regards,
Billy Yao
Microsoft Online Partner Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Re-post the untidy sample
======================
/*************************
Using sp_prepare/sp_execute
Note applications would not call these directly, but would only get these
when opening a default recordset (client cursor): forward only, read only,
rowset size = 1
They must also use SQLPrepare/SQLExecute (ODBC) or
ICommandText::Prepare/ICommandText::Execute
(OLEDB)
If the calls are not prepared, the query will simply be executed normally.
***************************/
use pubs
go
declare @.P1 int
set @.P1=NULL
exec sp_prepare @.P1 output, N'@.P1 varchar(8)', N'select * from titles where
title_id = @.P1'
exec sp_execute @.P1, 'MC2222'
exec sp_unprepare @.P1
go|||According to this link on component checker
Special Considerations for Windows 2000, Windows Me, and Later Versions of
Windows
You can use Component Checker on Windows 2000, Windows Me, and later
versions of Windows to identify the version of MDAC and identify problems
with the installation. However, the Reconfigure MDAC components menu option
does not appear when you run Component Checker on these versions of Windows,
even if you use the /d switch. Because MDAC is installed as part of the core
functionality in Windows 2000, Windows Me, and later versions of Windows, it
is impossible to remove MDAC from the operating system. It is also
impossible to reinstall the current version of MDAC without reinstalling the
entire operating system.
So since I'm running XP Pro on this laptop, I can't do this reconfigure. I
have re-run the MDAC installation and it hasn't seemed to help.
One more note. I couldn't get the query you specified to work but I was
able to get it to work using the sp_prepexec. So what does that mean. ODBC
is throwing the error and we have no idea why? The app is actually
connecting to another db before this one and that seems to work fine. So
where do we go from here. We really can't practically redo all of our
queries as you specified since we have a ton of those and this is only
happening on one client's machine.
Thanks for the info you have provided so far.
Lance
"Billy Yao [MSFT]" <v-binyao@.online.microsoft.com> wrote in message
news:8XXFcB7nDHA.2624@.cpmsftngxa06.phx.gbl...
> Hi Lance
> Thank you for your update. Your feedback proves again that the cause of
the problem is on
> the problematic machine, not on any other machines.
> The MDAC Component Checker worked fine on my side and could uninstall MDAC
2.8
> according to KB 307255. It's really strange that it didn't let you
reconfigure the version. Well, as
> MDAC 2.8 did work fine on another machine, I suggest you re-install the
same MDAC 2.8 on
> the machine. The reason why we should do this is the pervious MDAC 2.8 may
not be applied
> properly on the client. Please re-apply it.
> As to sp_unprepare, it is an internal Extended Stored Procedures (XPROC)
like sp_prepexec,
> which the latter provides server support for combining an execution plan
preparation and
> executing it into one call.
> There is no outer resource you can refer to because it's interal. Here I
provide you a simple
> sample to picture how to apply sp_unprepare explicitly:
> /************************* Using sp_prepare/sp_execute
> Note applications would not call these directly, but would only get these
when opening a
> default recordset (client cursor): forward only, read only, rowset size =1 They must also use
> SQLPrepare/SQLExecute (ODBC) or
ICommandText::Prepare/ICommandText::Execute
> (OLEDB)
> If the calls are not prepared, the query will simply be executed normally.
> ***************************/
> use pubs
> go
> declare @.P1 int set @.P1=NULL exec sp_prepare @.P1 output, N'@.P1
varchar(8)', N'select *
> from titles where title_id = @.P1' exec sp_execute @.P1, 'MC2222' exec
sp_unprepare @.P1
> go
> Please run the script above on the problematic client machine and the
server machine to see
> if it can work fine. If it works fine, you can apply this explicit
sp_prepare, sp_execute and
> sp_unprepare instead of sp_prepexec.
>
> Please feel free to post in the group if this solves your problem or if
you would like further
> assistance.
>
> Best regards,
> Billy Yao
> Microsoft Online Partner Support
> ----
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only. Thanks.
>
>|||I found my problem. We use system DSNs. However there was an errant entry
in the registry for User DSNs with that same DSN name. This entry did not
show up in User DSNs tab for ODBC so I didn't know it existed. There wasn't
much to it except the default DB, which was set to master. That'll teach me
to not look in profiler at what db is being queried.
Lance Johnson
"Lance Johnson" <ljohnson@.docs.com> wrote in message
news:uJxL659nDHA.2068@.TK2MSFTNGP09.phx.gbl...
> According to this link on component checker
> Special Considerations for Windows 2000, Windows Me, and Later Versions of
> Windows
> You can use Component Checker on Windows 2000, Windows Me, and later
> versions of Windows to identify the version of MDAC and identify problems
> with the installation. However, the Reconfigure MDAC components menu
option
> does not appear when you run Component Checker on these versions of
Windows,
> even if you use the /d switch. Because MDAC is installed as part of the
core
> functionality in Windows 2000, Windows Me, and later versions of Windows,
it
> is impossible to remove MDAC from the operating system. It is also
> impossible to reinstall the current version of MDAC without reinstalling
the
> entire operating system.
> So since I'm running XP Pro on this laptop, I can't do this reconfigure.
I
> have re-run the MDAC installation and it hasn't seemed to help.
> One more note. I couldn't get the query you specified to work but I was
> able to get it to work using the sp_prepexec. So what does that mean.
ODBC
> is throwing the error and we have no idea why? The app is actually
> connecting to another db before this one and that seems to work fine. So
> where do we go from here. We really can't practically redo all of our
> queries as you specified since we have a ton of those and this is only
> happening on one client's machine.
> Thanks for the info you have provided so far.
> Lance
>
> "Billy Yao [MSFT]" <v-binyao@.online.microsoft.com> wrote in message
> news:8XXFcB7nDHA.2624@.cpmsftngxa06.phx.gbl...
> > Hi Lance
> >
> > Thank you for your update. Your feedback proves again that the cause of
> the problem is on
> > the problematic machine, not on any other machines.
> >
> > The MDAC Component Checker worked fine on my side and could uninstall
MDAC
> 2.8
> > according to KB 307255. It's really strange that it didn't let you
> reconfigure the version. Well, as
> > MDAC 2.8 did work fine on another machine, I suggest you re-install the
> same MDAC 2.8 on
> > the machine. The reason why we should do this is the pervious MDAC 2.8
may
> not be applied
> > properly on the client. Please re-apply it.
> >
> > As to sp_unprepare, it is an internal Extended Stored Procedures (XPROC)
> like sp_prepexec,
> > which the latter provides server support for combining an execution plan
> preparation and
> > executing it into one call.
> >
> > There is no outer resource you can refer to because it's interal. Here I
> provide you a simple
> > sample to picture how to apply sp_unprepare explicitly:
> >
> > /************************* Using sp_prepare/sp_execute
> > Note applications would not call these directly, but would only get
these
> when opening a
> > default recordset (client cursor): forward only, read only, rowset size
=> 1 They must also use
> > SQLPrepare/SQLExecute (ODBC) or
> ICommandText::Prepare/ICommandText::Execute
> > (OLEDB)
> > If the calls are not prepared, the query will simply be executed
normally.
> > ***************************/
> > use pubs
> > go
> >
> > declare @.P1 int set @.P1=NULL exec sp_prepare @.P1 output, N'@.P1
> varchar(8)', N'select *
> > from titles where title_id = @.P1' exec sp_execute @.P1, 'MC2222' exec
> sp_unprepare @.P1
> > go
> > Please run the script above on the problematic client machine and the
> server machine to see
> > if it can work fine. If it works fine, you can apply this explicit
> sp_prepare, sp_execute and
> > sp_unprepare instead of sp_prepexec.
> >
> >
> > Please feel free to post in the group if this solves your problem or if
> you would like further
> > assistance.
> >
> >
> > Best regards,
> >
> > Billy Yao
> > Microsoft Online Partner Support
> > ----
> > Get Secure! - www.microsoft.com/security
> > This posting is provided "as is" with no warranties and confers no
rights.
> > Please reply to newsgroups only. Thanks.
> >
> >
> >
> >
>|||Thank you Lance for your feedback.
DSNs settings are actually out of my consideration as it seemed from the trace of profile that
the problem appeared with provider issue. The same operation resulted in different value
passing to the same parameter @.p1 and this script was sent by client provider to server
provider.
I'm glad that you yourself find the cause and solve the problem. We learnt a lot from this
specific issue. Thank you for participating newgroup and sharing us so many information and
your own experience on the issue.
Best regards,
Billy Yao
Microsoft Online Partner Support
to the problem. We had a client which keeps saying Invalid object name
'Docs_Main'. And we have another client which works fine. Good ole SQL
profiler time to figure out what's going on. So we run our query and we get
this for the working client:
declare @.P1 int
set @.P1=84
exec sp_prepexec @.P1 output, NULL, N'SELECT
"Item_ID","NumVal","TextVal","TextMemo" FROM "Docs_Main" WHERE Item_ID like
''0B05B2A4-91CC-11d1-9FE9-00C0F00A2A2E'''
select @.P1
And for the client not working we get
declare @.P1 int
set @.P1=NULL
exec sp_prepexec @.P1 output, NULL, N'SELECT
"Item_ID","NumVal","TextVal","TextMemo" FROM "Docs_Main" WHERE Item_ID like
''0B05B2A4-91CC-11d1-9FE9-00C0F00A2A2E'''
select @.P1
The only difference appears to be in the set @.P1 = x statement. If that's
set to NULL does that cause problems, and why is that being set to NULL.
The program it's coming from is the same one.
Lance JohnsonWhy should the program explicitly set an output parameter in either case?
You would think it is an output parameter because you're interested in
seeing the output, not because you want to control the input.
"Lance Johnson" <ljohnson@.docs.com> wrote in message
news:eKS5FpMnDHA.644@.TK2MSFTNGP11.phx.gbl...
> We have a MFC app that uses ODBC to connect to the DB. Now let me get
down
> to the problem. We had a client which keeps saying Invalid object name
> 'Docs_Main'. And we have another client which works fine. Good ole SQL
> profiler time to figure out what's going on. So we run our query and we
get
> this for the working client:
> declare @.P1 int
> set @.P1=84
> exec sp_prepexec @.P1 output, NULL, N'SELECT
> "Item_ID","NumVal","TextVal","TextMemo" FROM "Docs_Main" WHERE Item_ID
like
> ''0B05B2A4-91CC-11d1-9FE9-00C0F00A2A2E'''
> select @.P1
>
> And for the client not working we get
> declare @.P1 int
> set @.P1=NULL
> exec sp_prepexec @.P1 output, NULL, N'SELECT
> "Item_ID","NumVal","TextVal","TextMemo" FROM "Docs_Main" WHERE Item_ID
like
> ''0B05B2A4-91CC-11d1-9FE9-00C0F00A2A2E'''
> select @.P1
>
> The only difference appears to be in the set @.P1 = x statement. If that's
> set to NULL does that cause problems, and why is that being set to NULL.
> The program it's coming from is the same one.
>
> Lance Johnson
>|||Well I don't really know what that @.P1 is for, but shouldn't it be the same
for all clients, meaning if one client has a number, another client would
have a number and not NULL. I've looked around for info on these prepare
statements so I could learn what's happening, but these aren't easily
findable and there's no mention in sql books online of them.
Lance Johnson
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:egz6PuMnDHA.3288@.tk2msftngp13.phx.gbl...
> Why should the program explicitly set an output parameter in either case?
> You would think it is an output parameter because you're interested in
> seeing the output, not because you want to control the input.
>
>
> "Lance Johnson" <ljohnson@.docs.com> wrote in message
> news:eKS5FpMnDHA.644@.TK2MSFTNGP11.phx.gbl...
> > We have a MFC app that uses ODBC to connect to the DB. Now let me get
> down
> > to the problem. We had a client which keeps saying Invalid object name
> > 'Docs_Main'. And we have another client which works fine. Good ole SQL
> > profiler time to figure out what's going on. So we run our query and we
> get
> > this for the working client:
> >
> > declare @.P1 int
> > set @.P1=84
> > exec sp_prepexec @.P1 output, NULL, N'SELECT
> > "Item_ID","NumVal","TextVal","TextMemo" FROM "Docs_Main" WHERE Item_ID
> like
> > ''0B05B2A4-91CC-11d1-9FE9-00C0F00A2A2E'''
> > select @.P1
> >
> >
> >
> > And for the client not working we get
> >
> > declare @.P1 int
> > set @.P1=NULL
> > exec sp_prepexec @.P1 output, NULL, N'SELECT
> > "Item_ID","NumVal","TextVal","TextMemo" FROM "Docs_Main" WHERE Item_ID
> like
> > ''0B05B2A4-91CC-11d1-9FE9-00C0F00A2A2E'''
> > select @.P1
> >
> >
> >
> > The only difference appears to be in the set @.P1 = x statement. If
that's
> > set to NULL does that cause problems, and why is that being set to NULL.
> > The program it's coming from is the same one.
> >
> >
> >
> > Lance Johnson
> >
> >
>|||> Well I don't really know what that @.P1 is for, but shouldn't it be the
same
> for all clients, meaning if one client has a number, another client would
> have a number and not NULL.
I really don't know enough about your design to agree with you.
> I've looked around for info on these prepare
> statements so I could learn what's happening, but these aren't easily
> findable and there's no mention in sql books online of them.
You could always google;
http://groups.google.com/groups?hl=en&lr=&ie=ISO-8859-1&q=sp_prepexec&btnG=Google+Search|||Hi Lance,
Thank you for using MSDN Newsgroup! It's my pleasure to assist you with your issue.
From your conservation with Aaron, I understand that you would like to know what the stored
procedure sp_prepexec is and why it cannot appear with the same value on the same
parameter @.p1. Moreover, you want to avoid this and then make both your clients work fine.
Have I fully understood you, Lance? If there is anything I misunderstood, please feel free to
post it in the newsgroup to let me know.
In fact, SP_PREPEXEC is an internal Extended Stored Procedures (XP) that provides server
support for combining an execution plan preparation and executing it into one call. If you set
"Prepared" property in Application, the application might send this command into SQL Server.
From your description, however, I'm unsure of your SQL Server and Service Pack' s version,
whether or not you used some local temp tables and the data types in your table. Detailed
information that you can provide will make things clear and help us move closer to the causes
and resolutions.
However, I'm still eager to assist you with this issue according to my experience on the
SP_PREPEXEC.
First of all, to SQL Server's 7.0, I recommend you apply SP4 that fixed the "Preparing a
Statement That References a Missing Object Incorrectly" problem. You can download this
service pack via:
http://www.microsoft.com/sql/downloads/sp4.asp
Additionally, if you use some local temp tables, the Prepare will fail since we cannot perform
the SELECT statement because the object doesn't exist yet. In this case, you should use
global temp table or a permanent table.
If you use SQL 2000 (with MDAC 2.6 or higher), based on my experience, it will start using
sp_prepexec instead of sp_prepare & sp_execute to implement SQLPrepare and
SQLExecute commands from ODBC, which was done to eliminate round trips to the server
whenever possible.
This is not documented anywhere as this is an implementation done at the server level and
should be transparent to the user. If Prepare first and Execute later were somehow forced, the
Prepare would fail in SQL 2000 as well. In this case, please make sure that your server is able
to Prepare and Execute AT THE SAME TIME.
Lance, does this answer your question? Please feel free to post in the group if this solves your
problem or if you would like further assistance.
Best regards,
Billy Yao
Microsoft Online Partner Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||We are currently using SQL 2000 with SP3a. We are not using temp tables.
The tables are regular tables. Let me know if you need more info in this
area. Following is the code that we are using. Some of it is not
explicitly obvious and this is not my code, so I'm just passing it along.
Let me know what else you might need to know.
BOOL CSW_UsersCtrl::DoWeTurnOnSecurity()
{
BOOL SecurityOn = FALSE;
try
{
CSOAPwareSettings theDB(g_DB_USERS);
theDB.m_strFilter = "Item_ID like '";
theDB.m_strFilter += DOCS_SECURITY_COUNT_ID;
theDB.m_strFilter += "'";
try{
theDB.Open();
}catch(CException* e){
DisplayErrorMessage("DoWeTurnOnSecurity #1",e);
return FALSE;
}
...more code but it's failing above
}
So the g_DB_Users is just the access to our database where the table exists.
And the error is coming from the catch statement so an error is being
generated from the call to open theDB.
Lance Johnson
"Billy Yao [MSFT]" <v-binyao@.online.microsoft.com> wrote in message
news:HwZRGjTnDHA.1804@.cpmsftngxa06.phx.gbl...
> Hi Lance,
> Thank you for using MSDN Newsgroup! It's my pleasure to assist you with
your issue.
> From your conservation with Aaron, I understand that you would like to
know what the stored
> procedure sp_prepexec is and why it cannot appear with the same value on
the same
> parameter @.p1. Moreover, you want to avoid this and then make both your
clients work fine.
> Have I fully understood you, Lance? If there is anything I misunderstood,
please feel free to
> post it in the newsgroup to let me know.
> In fact, SP_PREPEXEC is an internal Extended Stored Procedures (XP) that
provides server
> support for combining an execution plan preparation and executing it into
one call. If you set
> "Prepared" property in Application, the application might send this
command into SQL Server.
> From your description, however, I'm unsure of your SQL Server and Service
Pack' s version,
> whether or not you used some local temp tables and the data types in your
table. Detailed
> information that you can provide will make things clear and help us move
closer to the causes
> and resolutions.
> However, I'm still eager to assist you with this issue according to my
experience on the
> SP_PREPEXEC.
> First of all, to SQL Server's 7.0, I recommend you apply SP4 that fixed
the "Preparing a
> Statement That References a Missing Object Incorrectly" problem. You can
download this
> service pack via:
> http://www.microsoft.com/sql/downloads/sp4.asp
> Additionally, if you use some local temp tables, the Prepare will fail
since we cannot perform
> the SELECT statement because the object doesn't exist yet. In this case,
you should use
> global temp table or a permanent table.
> If you use SQL 2000 (with MDAC 2.6 or higher), based on my experience, it
will start using
> sp_prepexec instead of sp_prepare & sp_execute to implement SQLPrepare and
> SQLExecute commands from ODBC, which was done to eliminate round trips to
the server
> whenever possible.
> This is not documented anywhere as this is an implementation done at the
server level and
> should be transparent to the user. If Prepare first and Execute later were
somehow forced, the
> Prepare would fail in SQL 2000 as well. In this case, please make sure
that your server is able
> to Prepare and Execute AT THE SAME TIME.
> Lance, does this answer your question? Please feel free to post in the
group if this solves your
> problem or if you would like further assistance.
>
> Best regards,
>
> Billy Yao
> Microsoft Online Partner Support
> ----
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only. Thanks.
>|||Hello Lance,
Thank you for your feedback! Now it's clear to me that you are using SQL Server 2000 SP3a,
not using any temp tables and there are different users accessing to your DB.
Based on my knowledge, the issue is not related to the code you are using. I agree with you
we can pass it along. In my opinion, it's also not a server side issue.
As I mentioned before, SP_PREPEXEC first PREPARE for the execution plan (can be re-used
later) and then perform the EXECUTE in the future. All the parameters passing processed are
on the client.
That means the provider on the client produces the code (you trace in the original message)
and sends it to the server provider. Therefore, the problem may be located in provider
mismatch between client and server.
According to my experience, you should first check both clients' Microsoft Data Access
Components (MDAC) version to see if it matched the server's MDAC version. I think the
working client can be suited, but it's hard to make sure if the failing client's MDAC is also ok.
Here I provide you a tool from Microsoft to check the MDAC's version for your convenience:
http://www.microsoft.com/downloads/details.aspx?FamilyId=8F0A8DF6-4A21-4B43-BF53-
14332EF092C9&displaylang=en
Please download it and I provide you step-by-step directions to help check the clients' MDAC
version:
1. Extract the executable file to the WORKING client you want to check
2. In the extracted folder, click cc.exe to run the MDAC check tool
3. In the "Component Check Dialog", select "perform analysis of machine and automatically
determine the release version". It's the default selected option.
4. Click OK on the Dialog and let the tool to check the client's MDAC version
5. Extract the executable file to the FAILING client you want to check and then follow the 2-4
steps above to check another MDAC version on your failing client.
After that, you can compare the version to see if the two clients had different MDAC.
To workaround the issue, I provide you with the following suggestion:
1. Download the working client's MDAC and apply it to the failing client to see if the problem
can be resolved
2. On the other hand, note that your server is SQL Server 2000 SP3, you can also apply MDAC
2.7 SP1 Refresh on your client to meet the provider match.
3. For downloading the MDAC, please reference the following address for detailed
information:
http://msdn.microsoft.com/library/default.asp?url=/downloads/list/dataaccess.asp
Lance, please apply my suggestion above and let me know if it helps you resolve your
problem. If there is anything more I can assist you with, please feel free to post it in the group.
Thank you for using MSDN newsgroup!
Best regards,
Billy Yao
Microsoft Online Partner Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||This was actually one of our first suspicions and we
installed MDAC 2.8 on this machine to make sure it was up
to date. I proceeded to run component checker on this
machine and all was OK it seemed. One more thing to help
in understanding this. The machine this runs on has MSDE
installed and it does the same thing when working with
our DB on that local MSDE or another MSDE/SQL from
another computer. So it's not between 1 particular
server, but for any MSDE server. Thanks for the
information you're provided so far. I hope we can
somehow figure out what's causing this.
Lance Johnson
>--Original Message--
>Hello Lance,
>Thank you for your feedback! Now it's clear to me that
you are using SQL Server 2000 SP3a,
>not using any temp tables and there are different users
accessing to your DB.
>Based on my knowledge, the issue is not related to the
code you are using. I agree with you
>we can pass it along. In my opinion, it's also not a
server side issue.
>As I mentioned before, SP_PREPEXEC first PREPARE for the
execution plan (can be re-used
>later) and then perform the EXECUTE in the future. All
the parameters passing processed are
>on the client.
>That means the provider on the client produces the code
(you trace in the original message)
>and sends it to the server provider. Therefore, the
problem may be located in provider
>mismatch between client and server.
>According to my experience, you should first check both
clients' Microsoft Data Access
>Components (MDAC) version to see if it matched the
server's MDAC version. I think the
>working client can be suited, but it's hard to make sure
if the failing client's MDAC is also ok.
>Here I provide you a tool from Microsoft to check the
MDAC's version for your convenience:
>http://www.microsoft.com/downloads/details.aspx?
FamilyId=8F0A8DF6-4A21-4B43-BF53-
>14332EF092C9&displaylang=en
>Please download it and I provide you step-by-step
directions to help check the clients' MDAC
>version:
>1. Extract the executable file to the WORKING client you
want to check
>2. In the extracted folder, click cc.exe to run the MDAC
check tool
>3. In the "Component Check Dialog", select "perform
analysis of machine and automatically
>determine the release version". It's the default
selected option.
>4. Click OK on the Dialog and let the tool to check the
client's MDAC version
>5. Extract the executable file to the FAILING client you
want to check and then follow the 2-4
>steps above to check another MDAC version on your
failing client.
>After that, you can compare the version to see if the
two clients had different MDAC.
>To workaround the issue, I provide you with the
following suggestion:
>1. Download the working client's MDAC and apply it to
the failing client to see if the problem
>can be resolved
>2. On the other hand, note that your server is SQL
Server 2000 SP3, you can also apply MDAC
>2.7 SP1 Refresh on your client to meet the provider
match.
>3. For downloading the MDAC, please reference the
following address for detailed
>information:
>http://msdn.microsoft.com/library/default.asp?
url=/downloads/list/dataaccess.asp
>Lance, please apply my suggestion above and let me know
if it helps you resolve your
>problem. If there is anything more I can assist you
with, please feel free to post it in the group.
>Thank you for using MSDN newsgroup!
>
>Best regards,
>Billy Yao
>Microsoft Online Partner Support
>----
>Get Secure! - www.microsoft.com/security
>This posting is provided "as is" with no warranties and
confers no rights.
>Please reply to newsgroups only. Thanks.
>
>.
>|||Hi Lance,
Thank you for your update!
From your description, I know that you have already installed MDAC 2.8 on this machine.
However, I'm unsure what here "this machine" represents for. Does it mean the machine your
server and DB located, or mean the machine your problematic client located?
Looking through your original message in this post, I notice that there were two clients
connecting to the server and fetch the data from your DB. One worked fine and the other failed
with "Invalid object name 'Docs_Main' " error message.
As we discussed before, the provider issue might be not on the server side machine but on
the client side machine. Therefore, I provided the MDAC check tool for your convenience to
distinguish the MDAC version between these two clients. If the versions are different, this may
be the cause that one was successful and the other was failed.
It was recommended that you applied suitable (not latest) MDAC on the problematic client
side machine. This "suitable MDAC" means you can apply "the MDAC version on the working
client" to the failing client.
Another workaround you can perform is to force sp_unprepare explicitly. In this way,
SP_PREPEXEC will not prepare the execution plan and execute the script directly.
Lance, does this answer your question? Please feel free to post in the group if this solves your
problem or if you would like further assistance.
Best regards,
Billy Yao
Microsoft Online Partner Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Sorry. Let me clarify what I mean by that machine. Our problematic client
(let's call it Laptop) has the MDAC 2.8 and we have MSDE installed on it.
It was having this problem, so we tried connecting it to another machine's
MSDE (let's call it Desktop). The same result occurred. So on Desktop we
connected to the 2 different MSDE's and it worked on both. So if the client
is connecting to a local MSDE the MDAC shouldn't (I believe) be getting in
the way. We connected to another machine's SQL just so we could run SQL
Profiler and get more info. Thanks much.
Lance Johnson
"Billy Yao [MSFT]" <v-binyao@.online.microsoft.com> wrote in message
news:XIC4jsjnDHA.2012@.cpmsftngxa06.phx.gbl...
> Hi Lance,
> Thank you for your update!
> From your description, I know that you have already installed MDAC 2.8 on
this machine.
> However, I'm unsure what here "this machine" represents for. Does it mean
the machine your
> server and DB located, or mean the machine your problematic client
located?
> Looking through your original message in this post, I notice that there
were two clients
> connecting to the server and fetch the data from your DB. One worked fine
and the other failed
> with "Invalid object name 'Docs_Main' " error message.
> As we discussed before, the provider issue might be not on the server side
machine but on
> the client side machine. Therefore, I provided the MDAC check tool for
your convenience to
> distinguish the MDAC version between these two clients. If the versions
are different, this may
> be the cause that one was successful and the other was failed.
> It was recommended that you applied suitable (not latest) MDAC on the
problematic client
> side machine. This "suitable MDAC" means you can apply "the MDAC version
on the working
> client" to the failing client.
> Another workaround you can perform is to force sp_unprepare explicitly. In
this way,
> SP_PREPEXEC will not prepare the execution plan and execute the script
directly.
> Lance, does this answer your question? Please feel free to post in the
group if this solves your
> problem or if you would like further assistance.
>
> Best regards,
>
> Billy Yao
> Microsoft Online Partner Support
> ----
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only. Thanks.
>|||Hi Lance,
Thank you for your detailed explanation.
Now it's clear that the problem is addressed on the client provider- Laptop as all the clients
could connect the MSDEs except for this problematic one.
Although you apply the MDAC 2.8 on the problematic client, the symptom may also occur with
unexpected causes. The better way to work around this issue is to apply the same MDAC
version of the WORKING client to the f problematic client (Laptop), which ensures the Laptop's
provider to use the working MDAC that can connect to the server successfully.
Please re-check your working client's MDAC version and download THIS MDAC from:
http://msdn.microsoft.com/library/default.asp?url=/downloads/list/dataaccess.asp
Then uninstall the MDAC 2.8 on the problematic client (Laptop), and install the working client's
MDAC you've downloaded. Moreover, make sure the problematic client's
configuration/environment (mainly related to provider) is the same one as the working client.
Enable them identical if possible.
To uninstall the MDAC and reconfigure it, please refer to the following article:
307255 INFO: Component Checker: Diagnose Problems and Reconfigure MDAC
http://support.microsoft.com/?id=307255
Another workaround I mentioned in the previous message is to force sp_unprepare explicitly.
In this way, SP_PREPEXEC will not prepare the execution plan and perform execution directly.
Lance, please help narrow down the problem and apply my recommendation above to see if
this solves your problem. If there is anything more I can assist you with, please feel free to post
in the group.
Best regards,
Billy Yao
Microsoft Online Partner Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Hey Lance,
For more information, you should close all applications that use ODBC in order for the MDAC
installation to succeed, as all later version of ODBC components will be installed when
applying new MDAC. Applications that use ODBC include Microsoft Internet Information Server
(IIS), Microsoft Systems Management Server, Microsoft Access, and Oracle database
applications.
The following articles help you troubleshoot the MDAC setup and list various versions of
MDAC for the SQL Server ODBC driver:
232060 HOWTO: MDAC Setup Troubleshooting Guide
http://support.microsoft.com/?id=232060
219293 INFO: ODBC, SQL Server, and Jet Versions Shipped with MDAC
http://support.microsoft.com/?id=219293
Please feel free to post in the group if this solves your problem or if you would like further
assistance.
Best regards,
Billy Yao
Microsoft Online Partner Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||I can't seem to uninstall the current MDAC. This must refer to an older
Component Checker functionality because when I run my current one with that
/d flag it doesn't let me reconfigure or anything. Since I couldn't do
that, I installer 2.8 MDAC on one of my other machines and it seems to work
fine with my application. I'm a little bit hazy on this sp_unprepare stuff.
Do you have any links about this. We have never had to force this before so
we're not accustomed to doing this.
Lance
"Billy Yao [MSFT]" <v-binyao@.online.microsoft.com> wrote in message
news:PcqgbEqnDHA.2088@.cpmsftngxa06.phx.gbl...
> Hi Lance,
> Thank you for your detailed explanation.
> Now it's clear that the problem is addressed on the client provider-
Laptop as all the clients
> could connect the MSDEs except for this problematic one.
> Although you apply the MDAC 2.8 on the problematic client, the symptom may
also occur with
> unexpected causes. The better way to work around this issue is to apply
the same MDAC
> version of the WORKING client to the f problematic client (Laptop), which
ensures the Laptop's
> provider to use the working MDAC that can connect to the server
successfully.
> Please re-check your working client's MDAC version and download THIS MDAC
from:
>
http://msdn.microsoft.com/library/default.asp?url=/downloads/list/dataaccess.asp
> Then uninstall the MDAC 2.8 on the problematic client (Laptop), and
install the working client's
> MDAC you've downloaded. Moreover, make sure the problematic client's
> configuration/environment (mainly related to provider) is the same one as
the working client.
> Enable them identical if possible.
> To uninstall the MDAC and reconfigure it, please refer to the following
article:
> 307255 INFO: Component Checker: Diagnose Problems and Reconfigure MDAC
> http://support.microsoft.com/?id=307255
> Another workaround I mentioned in the previous message is to force
sp_unprepare explicitly.
> In this way, SP_PREPEXEC will not prepare the execution plan and perform
execution directly.
> Lance, please help narrow down the problem and apply my recommendation
above to see if
> this solves your problem. If there is anything more I can assist you with,
please feel free to post
> in the group.
>
> Best regards,
> Billy Yao
> Microsoft Online Partner Support
> ----
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only. Thanks.
>|||Hi Lance
Thank you for your update. Your feedback proves again that the cause of the problem is on
the problematic machine, not on any other machines.
The MDAC Component Checker worked fine on my side and could uninstall MDAC 2.8
according to KB 307255. It's really strange that it didn't let you reconfigure the version. Well, as
MDAC 2.8 did work fine on another machine, I suggest you re-install the same MDAC 2.8 on
the machine. The reason why we should do this is the pervious MDAC 2.8 may not be applied
properly on the client. Please re-apply it.
As to sp_unprepare, it is an internal Extended Stored Procedures (XPROC) like sp_prepexec,
which the latter provides server support for combining an execution plan preparation and
executing it into one call.
There is no outer resource you can refer to because it's interal. Here I provide you a simple
sample to picture how to apply sp_unprepare explicitly:
/************************* Using sp_prepare/sp_execute
Note applications would not call these directly, but would only get these when opening a
default recordset (client cursor): forward only, read only, rowset size = 1 They must also use
SQLPrepare/SQLExecute (ODBC) or ICommandText::Prepare/ICommandText::Execute
(OLEDB)
If the calls are not prepared, the query will simply be executed normally.
***************************/
use pubs
go
declare @.P1 int set @.P1=NULL exec sp_prepare @.P1 output, N'@.P1 varchar(8)', N'select *
from titles where title_id = @.P1' exec sp_execute @.P1, 'MC2222' exec sp_unprepare @.P1
go
Please run the script above on the problematic client machine and the server machine to see
if it can work fine. If it works fine, you can apply this explicit sp_prepare, sp_execute and
sp_unprepare instead of sp_prepexec.
Please feel free to post in the group if this solves your problem or if you would like further
assistance.
Best regards,
Billy Yao
Microsoft Online Partner Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Re-post the untidy sample
======================
/*************************
Using sp_prepare/sp_execute
Note applications would not call these directly, but would only get these
when opening a default recordset (client cursor): forward only, read only,
rowset size = 1
They must also use SQLPrepare/SQLExecute (ODBC) or
ICommandText::Prepare/ICommandText::Execute
(OLEDB)
If the calls are not prepared, the query will simply be executed normally.
***************************/
use pubs
go
declare @.P1 int
set @.P1=NULL
exec sp_prepare @.P1 output, N'@.P1 varchar(8)', N'select * from titles where
title_id = @.P1'
exec sp_execute @.P1, 'MC2222'
exec sp_unprepare @.P1
go|||According to this link on component checker
Special Considerations for Windows 2000, Windows Me, and Later Versions of
Windows
You can use Component Checker on Windows 2000, Windows Me, and later
versions of Windows to identify the version of MDAC and identify problems
with the installation. However, the Reconfigure MDAC components menu option
does not appear when you run Component Checker on these versions of Windows,
even if you use the /d switch. Because MDAC is installed as part of the core
functionality in Windows 2000, Windows Me, and later versions of Windows, it
is impossible to remove MDAC from the operating system. It is also
impossible to reinstall the current version of MDAC without reinstalling the
entire operating system.
So since I'm running XP Pro on this laptop, I can't do this reconfigure. I
have re-run the MDAC installation and it hasn't seemed to help.
One more note. I couldn't get the query you specified to work but I was
able to get it to work using the sp_prepexec. So what does that mean. ODBC
is throwing the error and we have no idea why? The app is actually
connecting to another db before this one and that seems to work fine. So
where do we go from here. We really can't practically redo all of our
queries as you specified since we have a ton of those and this is only
happening on one client's machine.
Thanks for the info you have provided so far.
Lance
"Billy Yao [MSFT]" <v-binyao@.online.microsoft.com> wrote in message
news:8XXFcB7nDHA.2624@.cpmsftngxa06.phx.gbl...
> Hi Lance
> Thank you for your update. Your feedback proves again that the cause of
the problem is on
> the problematic machine, not on any other machines.
> The MDAC Component Checker worked fine on my side and could uninstall MDAC
2.8
> according to KB 307255. It's really strange that it didn't let you
reconfigure the version. Well, as
> MDAC 2.8 did work fine on another machine, I suggest you re-install the
same MDAC 2.8 on
> the machine. The reason why we should do this is the pervious MDAC 2.8 may
not be applied
> properly on the client. Please re-apply it.
> As to sp_unprepare, it is an internal Extended Stored Procedures (XPROC)
like sp_prepexec,
> which the latter provides server support for combining an execution plan
preparation and
> executing it into one call.
> There is no outer resource you can refer to because it's interal. Here I
provide you a simple
> sample to picture how to apply sp_unprepare explicitly:
> /************************* Using sp_prepare/sp_execute
> Note applications would not call these directly, but would only get these
when opening a
> default recordset (client cursor): forward only, read only, rowset size =1 They must also use
> SQLPrepare/SQLExecute (ODBC) or
ICommandText::Prepare/ICommandText::Execute
> (OLEDB)
> If the calls are not prepared, the query will simply be executed normally.
> ***************************/
> use pubs
> go
> declare @.P1 int set @.P1=NULL exec sp_prepare @.P1 output, N'@.P1
varchar(8)', N'select *
> from titles where title_id = @.P1' exec sp_execute @.P1, 'MC2222' exec
sp_unprepare @.P1
> go
> Please run the script above on the problematic client machine and the
server machine to see
> if it can work fine. If it works fine, you can apply this explicit
sp_prepare, sp_execute and
> sp_unprepare instead of sp_prepexec.
>
> Please feel free to post in the group if this solves your problem or if
you would like further
> assistance.
>
> Best regards,
> Billy Yao
> Microsoft Online Partner Support
> ----
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only. Thanks.
>
>|||I found my problem. We use system DSNs. However there was an errant entry
in the registry for User DSNs with that same DSN name. This entry did not
show up in User DSNs tab for ODBC so I didn't know it existed. There wasn't
much to it except the default DB, which was set to master. That'll teach me
to not look in profiler at what db is being queried.
Lance Johnson
"Lance Johnson" <ljohnson@.docs.com> wrote in message
news:uJxL659nDHA.2068@.TK2MSFTNGP09.phx.gbl...
> According to this link on component checker
> Special Considerations for Windows 2000, Windows Me, and Later Versions of
> Windows
> You can use Component Checker on Windows 2000, Windows Me, and later
> versions of Windows to identify the version of MDAC and identify problems
> with the installation. However, the Reconfigure MDAC components menu
option
> does not appear when you run Component Checker on these versions of
Windows,
> even if you use the /d switch. Because MDAC is installed as part of the
core
> functionality in Windows 2000, Windows Me, and later versions of Windows,
it
> is impossible to remove MDAC from the operating system. It is also
> impossible to reinstall the current version of MDAC without reinstalling
the
> entire operating system.
> So since I'm running XP Pro on this laptop, I can't do this reconfigure.
I
> have re-run the MDAC installation and it hasn't seemed to help.
> One more note. I couldn't get the query you specified to work but I was
> able to get it to work using the sp_prepexec. So what does that mean.
ODBC
> is throwing the error and we have no idea why? The app is actually
> connecting to another db before this one and that seems to work fine. So
> where do we go from here. We really can't practically redo all of our
> queries as you specified since we have a ton of those and this is only
> happening on one client's machine.
> Thanks for the info you have provided so far.
> Lance
>
> "Billy Yao [MSFT]" <v-binyao@.online.microsoft.com> wrote in message
> news:8XXFcB7nDHA.2624@.cpmsftngxa06.phx.gbl...
> > Hi Lance
> >
> > Thank you for your update. Your feedback proves again that the cause of
> the problem is on
> > the problematic machine, not on any other machines.
> >
> > The MDAC Component Checker worked fine on my side and could uninstall
MDAC
> 2.8
> > according to KB 307255. It's really strange that it didn't let you
> reconfigure the version. Well, as
> > MDAC 2.8 did work fine on another machine, I suggest you re-install the
> same MDAC 2.8 on
> > the machine. The reason why we should do this is the pervious MDAC 2.8
may
> not be applied
> > properly on the client. Please re-apply it.
> >
> > As to sp_unprepare, it is an internal Extended Stored Procedures (XPROC)
> like sp_prepexec,
> > which the latter provides server support for combining an execution plan
> preparation and
> > executing it into one call.
> >
> > There is no outer resource you can refer to because it's interal. Here I
> provide you a simple
> > sample to picture how to apply sp_unprepare explicitly:
> >
> > /************************* Using sp_prepare/sp_execute
> > Note applications would not call these directly, but would only get
these
> when opening a
> > default recordset (client cursor): forward only, read only, rowset size
=> 1 They must also use
> > SQLPrepare/SQLExecute (ODBC) or
> ICommandText::Prepare/ICommandText::Execute
> > (OLEDB)
> > If the calls are not prepared, the query will simply be executed
normally.
> > ***************************/
> > use pubs
> > go
> >
> > declare @.P1 int set @.P1=NULL exec sp_prepare @.P1 output, N'@.P1
> varchar(8)', N'select *
> > from titles where title_id = @.P1' exec sp_execute @.P1, 'MC2222' exec
> sp_unprepare @.P1
> > go
> > Please run the script above on the problematic client machine and the
> server machine to see
> > if it can work fine. If it works fine, you can apply this explicit
> sp_prepare, sp_execute and
> > sp_unprepare instead of sp_prepexec.
> >
> >
> > Please feel free to post in the group if this solves your problem or if
> you would like further
> > assistance.
> >
> >
> > Best regards,
> >
> > Billy Yao
> > Microsoft Online Partner Support
> > ----
> > Get Secure! - www.microsoft.com/security
> > This posting is provided "as is" with no warranties and confers no
rights.
> > Please reply to newsgroups only. Thanks.
> >
> >
> >
> >
>|||Thank you Lance for your feedback.
DSNs settings are actually out of my consideration as it seemed from the trace of profile that
the problem appeared with provider issue. The same operation resulted in different value
passing to the same parameter @.p1 and this script was sent by client provider to server
provider.
I'm glad that you yourself find the cause and solve the problem. We learnt a lot from this
specific issue. Thank you for participating newgroup and sharing us so many information and
your own experience on the issue.
Best regards,
Billy Yao
Microsoft Online Partner Support
Monday, March 12, 2012
ODBC - SQL Server 200 - MFC - Visual C++.NET (2003)
Hello!
My problem is:
In Sql Server I made a table "Nir" with 3 columns:
Nr_crt - numeric
Val_TVA - numeric
Val_Tot - numeric
In SQL Server the command:
SELECT SUM(Val_TVA) AS Val_TVA, SUM(Val_Tot) AS Val_Tot FROM Nir - returned
me a result with 2 sums... OK!
My question is:
In Visual C++ - MFC I don't know how can to execute this command and to get
the result return by this comand (may be with a VARIANT - pvRecord I don't
know)
How to access the result return by this command''
I use CDatabase and CRecordset.
Thank You very much!Create a CRecordset, then execute the SQL statement on it. Then you have
can use GetFieldValue on the recordset grab the results in CDBVariants.
"Florin" <Florin@.discussions.microsoft.com> wrote in message
news:382C3711-E222-4173-BF8A-81EBD7E3508A@.microsoft.com...
> Hello!
> My problem is:
> In Sql Server I made a table "Nir" with 3 columns:
> Nr_crt - numeric
> Val_TVA - numeric
> Val_Tot - numeric
>
> In SQL Server the command:
> SELECT SUM(Val_TVA) AS Val_TVA, SUM(Val_Tot) AS Val_Tot FROM Nir -
> returned
> me a result with 2 sums... OK!
>
> My question is:
> In Visual C++ - MFC I don't know how can to execute this command and to
> get
> the result return by this comand (may be with a VARIANT - pvRecord I don't
> know)
> How to access the result return by this command''
> I use CDatabase and CRecordset.
>
> Thank You very much!
>
>
My problem is:
In Sql Server I made a table "Nir" with 3 columns:
Nr_crt - numeric
Val_TVA - numeric
Val_Tot - numeric
In SQL Server the command:
SELECT SUM(Val_TVA) AS Val_TVA, SUM(Val_Tot) AS Val_Tot FROM Nir - returned
me a result with 2 sums... OK!
My question is:
In Visual C++ - MFC I don't know how can to execute this command and to get
the result return by this comand (may be with a VARIANT - pvRecord I don't
know)
How to access the result return by this command''
I use CDatabase and CRecordset.
Thank You very much!Create a CRecordset, then execute the SQL statement on it. Then you have
can use GetFieldValue on the recordset grab the results in CDBVariants.
"Florin" <Florin@.discussions.microsoft.com> wrote in message
news:382C3711-E222-4173-BF8A-81EBD7E3508A@.microsoft.com...
> Hello!
> My problem is:
> In Sql Server I made a table "Nir" with 3 columns:
> Nr_crt - numeric
> Val_TVA - numeric
> Val_Tot - numeric
>
> In SQL Server the command:
> SELECT SUM(Val_TVA) AS Val_TVA, SUM(Val_Tot) AS Val_Tot FROM Nir -
> returned
> me a result with 2 sums... OK!
>
> My question is:
> In Visual C++ - MFC I don't know how can to execute this command and to
> get
> the result return by this comand (may be with a VARIANT - pvRecord I don't
> know)
> How to access the result return by this command''
> I use CDatabase and CRecordset.
>
> Thank You very much!
>
>
ODBC - SQL Server 200 - MFC - Visual C++.NET (2003)
Hello!
My problem is:
In Sql Server I made a table "Nir" with 3 columns:
Nr_crt - numeric
Val_TVA - numeric
Val_Tot - numeric
In SQL Server the command:
SELECT SUM(Val_TVA) AS Val_TVA, SUM(Val_Tot) AS Val_Tot FROM Nir - returned
me a result with 2 sums... OK!
My question is:
In Visual C++ - MFC I don't know how can to execute this command and to get
the result return by this comand (may be with a VARIANT - pvRecord I don't
know)
How to access the result return by this command??
I use CDatabase and CRecordset.
Thank You very much!
Create a CRecordset, then execute the SQL statement on it. Then you have
can use GetFieldValue on the recordset grab the results in CDBVariants.
"Florin" <Florin@.discussions.microsoft.com> wrote in message
news:382C3711-E222-4173-BF8A-81EBD7E3508A@.microsoft.com...
> Hello!
> My problem is:
> In Sql Server I made a table "Nir" with 3 columns:
> Nr_crt - numeric
> Val_TVA - numeric
> Val_Tot - numeric
>
> In SQL Server the command:
> SELECT SUM(Val_TVA) AS Val_TVA, SUM(Val_Tot) AS Val_Tot FROM Nir -
> returned
> me a result with 2 sums... OK!
>
> My question is:
> In Visual C++ - MFC I don't know how can to execute this command and to
> get
> the result return by this comand (may be with a VARIANT - pvRecord I don't
> know)
> How to access the result return by this command??
> I use CDatabase and CRecordset.
>
> Thank You very much!
>
>
My problem is:
In Sql Server I made a table "Nir" with 3 columns:
Nr_crt - numeric
Val_TVA - numeric
Val_Tot - numeric
In SQL Server the command:
SELECT SUM(Val_TVA) AS Val_TVA, SUM(Val_Tot) AS Val_Tot FROM Nir - returned
me a result with 2 sums... OK!
My question is:
In Visual C++ - MFC I don't know how can to execute this command and to get
the result return by this comand (may be with a VARIANT - pvRecord I don't
know)
How to access the result return by this command??
I use CDatabase and CRecordset.
Thank You very much!
Create a CRecordset, then execute the SQL statement on it. Then you have
can use GetFieldValue on the recordset grab the results in CDBVariants.
"Florin" <Florin@.discussions.microsoft.com> wrote in message
news:382C3711-E222-4173-BF8A-81EBD7E3508A@.microsoft.com...
> Hello!
> My problem is:
> In Sql Server I made a table "Nir" with 3 columns:
> Nr_crt - numeric
> Val_TVA - numeric
> Val_Tot - numeric
>
> In SQL Server the command:
> SELECT SUM(Val_TVA) AS Val_TVA, SUM(Val_Tot) AS Val_Tot FROM Nir -
> returned
> me a result with 2 sums... OK!
>
> My question is:
> In Visual C++ - MFC I don't know how can to execute this command and to
> get
> the result return by this comand (may be with a VARIANT - pvRecord I don't
> know)
> How to access the result return by this command??
> I use CDatabase and CRecordset.
>
> Thank You very much!
>
>
Subscribe to:
Posts (Atom)