I have a stand alone win2k3 report server(i.e. SQL is not on the box).
RS SP1 is installed
I have a SQLRS report developed in visual studio (also loaded on RS
box) that runs MDX against an analysis services cube. I am using the
OLE DB provider for OLAP services 8.0. (Note that we had to run
ptsfull.exe to get the pivot table service and 8.0 provider to be
available)
I can successfully preview the report in visual studio.
When I upload the report to Report Manger, the report bombs with...
An error has occurred during report processing. (rsProcessingAborted)
Get Online Help
Cannot create a connection to data source 'AAHP'.
(rsErrorOpeningConnection) Get Online Help
Database '<source name>' does not exist.
Am I missing something on the report server?
Thanks!Jim wrote:
> When I upload the report to Report Manger, the report bombs with...
> An error has occurred during report processing. (rsProcessingAborted)
> Get Online Help
> Cannot create a connection to data source 'AAHP'.
> (rsErrorOpeningConnection) Get Online Help
> Database '<source name>' does not exist.
> Am I missing something on the report server?
The report bombs while uploading?
Do you tried to open the AS-DB in the analysis-manager on the reportserver?
regards
Frank|||Sorry, no. The report bombs when I try to run it via report manager. I
can open the AS-DB and cube in Analysis Manger (which is Not on the
report server). I have a role setup that should allow the stored
credentials to successfully authenticate.|||Jim wrote:
> Sorry, no. The report bombs when I try to run it via report manager. I
> can open the AS-DB and cube in Analysis Manger (which is Not on the
> report server). I have a role setup that should allow the stored
> credentials to successfully authenticate.
Jim,
lets have a look into the eventlog of the server where the analysis cube
resides.
Source MSSQLSERVEROLAPSERVER.
If there errors with something like "NT-Authority/Anonymous" your
security-settings on the report-server are not valid (especially the
settings in internet service manager)
If there aren't any errors it become more difficult...Do you run ptsfull on
reportserver either?
regards
Frank|||Thanks for your replies.
No errors unfortunately.
I ran ptsfull on the reportserver.
As I mentioned I can connect to the AS server via Visual Studio. As
soon as I deploy the report (using a stored connection or shared
connection) to report manager it won't run. I'm running MDAC 2.8 on the
report server.|||Jim wrote:
> As I mentioned I can connect to the AS server via Visual Studio.
Yes..i know. You can connect to the AS from YOUR workstation and your
reportserver can't as you wrote.
The problem should be between reportserver and AS-server but wich problem? I
dont know!
regards
frank|||Just to clarify...
Visual Studio - where I am developing the reports - is on the report
server.|||Jim,
I am having same problems and was wondering if you found solution of this
one. Thanks.
"Jim" wrote:
> Just to clarify...
> Visual Studio - where I am developing the reports - is on the report
> server.
>
Showing posts with label visual. Show all posts
Showing posts with label visual. Show all posts
Friday, March 30, 2012
OLAP Analysis Services: Errors in Encryption Library
Hi!
I am trying to process a cube in analysis services of visual studio
2005, with sql server 2005. The error is:
"Errors in the encryption library: Failed to encrypt sensitive data.
Possibly the encryption key is inaccessible because of improper service
account change."
Thanks in advance,
--Dimitris Doukas
You might have changed the account for your Analysis Server service.
To solve this problem you will have to stop Analysis Server. Remove all the files from the Data folder ( located under installation folder) and then start Analysis Server again.
Create a backup for your existing databases before doing that in case you cannot re-process them.
Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights
It worked. Thanks a lot.
--Dimitris
Wednesday, March 28, 2012
Ok, I give up...
Been working in Visual Studio 2005 and got to the point where
I needed to build a native program that interfaces with SQL Server
via the bulk copy API. So, I d/l'ed SQL Server 2005, installed it
and tried a simple example from MSDN from the bcp_bind
documentation.
Fails because it is unable to find "sqlfront.h." I search the web.
Best suggestion I can find is to install the "Platform SDK" Installed
that. Installed everything I could find from the SQL Server 2005
distribution CDs. "sqlfront.h" is not in
"Visual Studio 8/VC/PlatformSDK/include or
"VIsual Studio 8/VC/include", where I would have thought they might
end up.
Found a reference that indicated there may be some kind of SQL
Server SDK that I might need to download, but so far I've been unable
to find one. Other suggestions indicate it may be included in the
Platform SDK, but I already tried that, no cigar...
Did a search of the whole computer. It found a copy of sqlfront.h,
one in "Microsoft Visual Studio/VC98/include"-- which is one from
VS 6.0, as I also have VS 6.0 on this system as that is what our
current development uses, we're trying to move some things to
.NET, and that's the only one it found.. Can I just use the includes
from VS 6.0? Are these the latest ones and that's why I've been
unable to find ones for VS 2005? Did Platform SDK install it in
the wrong tree? If so, how do I correct for that? Can VS 2005
link with the associated library files from VS 6.0? How are you
*supposed* to get these darn files so the stupid example will
compile?
I'm on XP SP2 BTW, if that is of any relevance.
Uninstalling VS 6.0 is not an option, that's what all our current
development is using. Installing VS 2005 on another PC is
not an option, I'm the only developer in this particular office and
they aren't going to buy me a new PC to do this. Deciding NOT
to transition to .NET and VS 2005 is an option however, and the
one I'm currently giving the most serious consideration. Uninstalling
VS 2005 and going to VS 2003 or something could be an option,
but it would be nice to know if it would actually fix the problem.
Reverting to an eariler SQL Server version could be an option as
well, but again, it'd be nice to know that it would actually fix the
problem.
I also noted that doing searches for stuff on MSDN does not
make it very clear WHICH version of whatever (VS, VC++,
SQL Server, etc.) the information applies to. Often I would find
pages and wonder if it applies to the .NET version or is leftover
from some previous incarnation of the product and possibly no
longer applies to the .NET version. Best I can tell, you can't
restrict searches by product version (if I'm wrong, please enlighten
me). What a nightmare-- I've just spent 2 days trying to get the
simplest of example programs to compile a native program on
VS 2005. Plenty of other people seem to have inquired about
the location of sqlfront.h though, I notice, yet so far I've not found
a response that was helpful to me.
Has ANYONE been able to compile a native VC++ program that
talks to SQL Server on the 2005 versions of VS & SQL Server?
Was it my mistake because I happened to choose to try
the "latest" versions of these products just as they came out,
and thought I could actually do something useful with them?
If you've been able to make it work, where does your sqlfront.h file
live on your system? Any idea how it got there?
Sync"sqlfront.h" and all the other include files and libraries you'll
need will typically be found in
C:\Program Files\Microsoft SQL Server\80\Tools\DevTools
During SQL Server installation make sure you check the "Development
Tool" option.
I've quite happily built native C++ programs that use
bcp_bind/bcp_sendrow/bcp_batch etc.
using Visual Studio .NET 2003|||Somone else just informed me of the DevTools... I have an 80\Tools
directory and a 90\Tools directory but no DevTools under either. I'm
sure I would have checked any "development" options in the install, as
that is all I'm doing-- this is not a production system it's purely a
development system. I'm currently searching for "sqlfront.*" on the
installation media, after I finish that I'll try the Sql Server
installation again and see if I can find something I missed...
So far, explorer search hasn't found sqlfront.* on either of the two VS
2005 CDs or the
SQL Server 2005 Developer Edition CDs, and it apparently does search
the .cab
files. Silly me though, perhaps I was assuming that such an include
file would be
on the DEVELOPER edition of SQL Server, maybe I should try the standard
edition...
Thanks for the info,
Sync|||I checked-- in Workstation Components, books online and development
tools, it shows everything in Client Compoments including Software
Development Kit as being installed.
Database Services, Analysis Services, Integration Services,
Notification Services show not installed. However, if I select them to
be installed and continue, it ultimately tells me everything is already
installed and so there's nothing for it to do.
I'm thinking I need to blow away the entire SQL Server installation and
reinstall absolutely EVERYTHING and see where that gets me. I did
install it in pieces somewhat originally, because I didn't know where
they had hidden the darn development tools-- apparently in
"documentation" which I usually think is huge and mostly already
available online, so I didn't install it. And, installing it later
seems to have
left it half uninstalled and thinking it's all installed...
Sync|||AAAAAAARRRRRRRGGGGGGHHHHHH!!!!
Looks like they merely renamed the include files and didn't tell me
anywhere-- the
bcp_bind example was for SQL Server 2000, and it hasn't been updated.
Apparently, "sqlfront.h" and "sqldb.h" no longer exist. The old bulk
copy functions
are now ODBC based and use the include "sqlncli.h" and an odbc handle
instead of
a DBPROCESS structure. There was an article in the books online
entitled
"Converting from DB Library to ODBC Bulk Copy functions" that explains
the
difference, which I never found because I didn't know about it and
doesn't mention
the differences in the includes, which is what breaks when you try the
example found
on the MSDN library (which is for SQL Server 2000).
So, I'm going to work on converting the example to an ODBC version
based on the
articles I've found.....
Syncsql
I needed to build a native program that interfaces with SQL Server
via the bulk copy API. So, I d/l'ed SQL Server 2005, installed it
and tried a simple example from MSDN from the bcp_bind
documentation.
Fails because it is unable to find "sqlfront.h." I search the web.
Best suggestion I can find is to install the "Platform SDK" Installed
that. Installed everything I could find from the SQL Server 2005
distribution CDs. "sqlfront.h" is not in
"Visual Studio 8/VC/PlatformSDK/include or
"VIsual Studio 8/VC/include", where I would have thought they might
end up.
Found a reference that indicated there may be some kind of SQL
Server SDK that I might need to download, but so far I've been unable
to find one. Other suggestions indicate it may be included in the
Platform SDK, but I already tried that, no cigar...
Did a search of the whole computer. It found a copy of sqlfront.h,
one in "Microsoft Visual Studio/VC98/include"-- which is one from
VS 6.0, as I also have VS 6.0 on this system as that is what our
current development uses, we're trying to move some things to
.NET, and that's the only one it found.. Can I just use the includes
from VS 6.0? Are these the latest ones and that's why I've been
unable to find ones for VS 2005? Did Platform SDK install it in
the wrong tree? If so, how do I correct for that? Can VS 2005
link with the associated library files from VS 6.0? How are you
*supposed* to get these darn files so the stupid example will
compile?
I'm on XP SP2 BTW, if that is of any relevance.
Uninstalling VS 6.0 is not an option, that's what all our current
development is using. Installing VS 2005 on another PC is
not an option, I'm the only developer in this particular office and
they aren't going to buy me a new PC to do this. Deciding NOT
to transition to .NET and VS 2005 is an option however, and the
one I'm currently giving the most serious consideration. Uninstalling
VS 2005 and going to VS 2003 or something could be an option,
but it would be nice to know if it would actually fix the problem.
Reverting to an eariler SQL Server version could be an option as
well, but again, it'd be nice to know that it would actually fix the
problem.
I also noted that doing searches for stuff on MSDN does not
make it very clear WHICH version of whatever (VS, VC++,
SQL Server, etc.) the information applies to. Often I would find
pages and wonder if it applies to the .NET version or is leftover
from some previous incarnation of the product and possibly no
longer applies to the .NET version. Best I can tell, you can't
restrict searches by product version (if I'm wrong, please enlighten
me). What a nightmare-- I've just spent 2 days trying to get the
simplest of example programs to compile a native program on
VS 2005. Plenty of other people seem to have inquired about
the location of sqlfront.h though, I notice, yet so far I've not found
a response that was helpful to me.
Has ANYONE been able to compile a native VC++ program that
talks to SQL Server on the 2005 versions of VS & SQL Server?
Was it my mistake because I happened to choose to try
the "latest" versions of these products just as they came out,
and thought I could actually do something useful with them?
If you've been able to make it work, where does your sqlfront.h file
live on your system? Any idea how it got there?
Sync"sqlfront.h" and all the other include files and libraries you'll
need will typically be found in
C:\Program Files\Microsoft SQL Server\80\Tools\DevTools
During SQL Server installation make sure you check the "Development
Tool" option.
I've quite happily built native C++ programs that use
bcp_bind/bcp_sendrow/bcp_batch etc.
using Visual Studio .NET 2003|||Somone else just informed me of the DevTools... I have an 80\Tools
directory and a 90\Tools directory but no DevTools under either. I'm
sure I would have checked any "development" options in the install, as
that is all I'm doing-- this is not a production system it's purely a
development system. I'm currently searching for "sqlfront.*" on the
installation media, after I finish that I'll try the Sql Server
installation again and see if I can find something I missed...
So far, explorer search hasn't found sqlfront.* on either of the two VS
2005 CDs or the
SQL Server 2005 Developer Edition CDs, and it apparently does search
the .cab
files. Silly me though, perhaps I was assuming that such an include
file would be
on the DEVELOPER edition of SQL Server, maybe I should try the standard
edition...
Thanks for the info,
Sync|||I checked-- in Workstation Components, books online and development
tools, it shows everything in Client Compoments including Software
Development Kit as being installed.
Database Services, Analysis Services, Integration Services,
Notification Services show not installed. However, if I select them to
be installed and continue, it ultimately tells me everything is already
installed and so there's nothing for it to do.
I'm thinking I need to blow away the entire SQL Server installation and
reinstall absolutely EVERYTHING and see where that gets me. I did
install it in pieces somewhat originally, because I didn't know where
they had hidden the darn development tools-- apparently in
"documentation" which I usually think is huge and mostly already
available online, so I didn't install it. And, installing it later
seems to have
left it half uninstalled and thinking it's all installed...
Sync|||AAAAAAARRRRRRRGGGGGGHHHHHH!!!!
Looks like they merely renamed the include files and didn't tell me
anywhere-- the
bcp_bind example was for SQL Server 2000, and it hasn't been updated.
Apparently, "sqlfront.h" and "sqldb.h" no longer exist. The old bulk
copy functions
are now ODBC based and use the include "sqlncli.h" and an odbc handle
instead of
a DBPROCESS structure. There was an article in the books online
entitled
"Converting from DB Library to ODBC Bulk Copy functions" that explains
the
difference, which I never found because I didn't know about it and
doesn't mention
the differences in the includes, which is what breaks when you try the
example found
on the MSDN library (which is for SQL Server 2000).
So, I'm going to work on converting the example to an ODBC version
based on the
articles I've found.....
Syncsql
Tuesday, March 20, 2012
Odd error message in Microsoft Query
I am using an online ODBC Database.
The fun thing is that I'm running into a circumstance where one SQL statement that works in Visual Studio accessing the same database works, but then I try to use the same statement in Microsoft Query (running to Excel) and it refuses to admit its a valid statement.
The SQL statement I'm using is this.
SELECT MLNumber, StreetNumber, StreetName, StreetDirection, ListingOfficeMLSID, Status, Bedrooms, Bathrooms, City
FROM "data: Property:RESI"
WHERE (ListingOfficeMLSID = '550000020' OR
ListingOfficeMLSID = '550001760') AND (MLNumber > 1) AND (City = "Boiling Spring Lakes")
This works fine inside of VS 2005.
The same statement used within Microsoft Query returns this error message.
SQL SYNTAX ERROR - Unexpected char: '?'
Working with the people that work with that database regularly, they say that I need to write a METADATA-LOOKUPTYPE call that should tell me how the City name 'Boiling Spring Lakes' is actually formatted.
I, of course, have no clue how to do that. But my thought is, if it works in VS2005 and returns a valid result, why does the same SQL statement return an error message through Excel?
HELP!
Thanks in advance for your time.
Are you sure that the problem is exactly with the City=... clause?
Does the query work if you remove it?
|||You need to replace the double quotes around [Boiling Spring Lakes] with single quotes.
SQL Server needs single quotes to delimit a string. Visual Basic uses double quotes.
|||Actually it depends on how quoted_identifier is set.
If you execute SET QUOTED_IDENTIFIER OFF you can use double quotes for literals afterwards. If quoted_identifier is on the double quotes signify identifiers. The default is usually ON.
|||Yes, the query works just fine in Excel without 'City' in the WHERE statement.
The Double Quotes were what enabled the query to work properly within Visual Studios. It was also suggested that I use SQDQ CITY NAME DQSQ ('"Oak Island"') as a possible work around. That didn't work, but just the double quotes did. Again, that worked in VS2005.
I need it to work in Microsoft Query as well so I can use the same data to create a form with both Database items and manual entered items not found on the Database.
Anyway to create a form like that within VS2005? Just to remain in house, not to deploy to the web.
Hmm, I'll give the 'Quoted Identifier' a shot. I'll dig through Microsoft Query to find it. Or where would I execute that?
|||Bump. Anyone found a workaround for this? Or can direct me to where and how I merge database-fed forms with manual entered forms within Visual Studios?|||I'm still confused about the single/double quotes issues.
You indicate using both single quotes and double quotes in the WHERE clause, and that is very suspicious to me.
WHERE (ListingOfficeMLSID = '550000020' OR
ListingOfficeMLSID = '550001760') AND (MLNumber > 1) AND (City = "Boiling Spring Lakes")
Please verify that the query DOES NOT work if the double quotes around "Boiling Spring Lakes" are replaced with single quotes.
|||The single and double quote issue. Sorry I forgot to post that the answer worked.
The single and double quotes from above were used as reccomended to sorta force the SQL to read Boiling Spring Lakes as a complete string. That works just fine in .NET 2.0 and VS 2005.
However, MS Query doesn't translate it the same way. Turned out that the problem wasn't on my end, it was on the database side of it and faulty handling of the shortdesc and longdesc.
thank you all for your time and trouble.
Jack
Odd error message in Microsoft Query
I am using an online ODBC Database.
The fun thing is that I'm running into a circumstance where one SQL statement that works in Visual Studio accessing the same database works, but then I try to use the same statement in Microsoft Query (running to Excel) and it refuses to admit its a valid statement.
The SQL statement I'm using is this.
SELECT MLNumber, StreetNumber, StreetName, StreetDirection, ListingOfficeMLSID, Status, Bedrooms, Bathrooms, City
FROM "data: Property:RESI"
WHERE (ListingOfficeMLSID = '550000020' OR
ListingOfficeMLSID = '550001760') AND (MLNumber > 1) AND (City = "Boiling Spring Lakes")
This works fine inside of VS 2005.
The same statement used within Microsoft Query returns this error message.
SQL SYNTAX ERROR - Unexpected char: '?'
Working with the people that work with that database regularly, they say that I need to write a METADATA-LOOKUPTYPE call that should tell me how the City name 'Boiling Spring Lakes' is actually formatted.
I, of course, have no clue how to do that. But my thought is, if it works in VS2005 and returns a valid result, why does the same SQL statement return an error message through Excel?
HELP!
Thanks in advance for your time.
Are you sure that the problem is exactly with the City=... clause?
Does the query work if you remove it?
|||You need to replace the double quotes around [Boiling Spring Lakes] with single quotes.
SQL Server needs single quotes to delimit a string. Visual Basic uses double quotes.
|||Actually it depends on how quoted_identifier is set.
If you execute SET QUOTED_IDENTIFIER OFF you can use double quotes for literals afterwards. If quoted_identifier is on the double quotes signify identifiers. The default is usually ON.
|||Yes, the query works just fine in Excel without 'City' in the WHERE statement.
The Double Quotes were what enabled the query to work properly within Visual Studios. It was also suggested that I use SQDQ CITY NAME DQSQ ('"Oak Island"') as a possible work around. That didn't work, but just the double quotes did. Again, that worked in VS2005.
I need it to work in Microsoft Query as well so I can use the same data to create a form with both Database items and manual entered items not found on the Database.
Anyway to create a form like that within VS2005? Just to remain in house, not to deploy to the web.
Hmm, I'll give the 'Quoted Identifier' a shot. I'll dig through Microsoft Query to find it. Or where would I execute that?
|||Bump. Anyone found a workaround for this? Or can direct me to where and how I merge database-fed forms with manual entered forms within Visual Studios?|||I'm still confused about the single/double quotes issues.
You indicate using both single quotes and double quotes in the WHERE clause, and that is very suspicious to me.
WHERE (ListingOfficeMLSID = '550000020' OR
ListingOfficeMLSID = '550001760') AND (MLNumber > 1) AND (City = "Boiling Spring Lakes")
Please verify that the query DOES NOT work if the double quotes around "Boiling Spring Lakes" are replaced with single quotes.
|||The single and double quote issue. Sorry I forgot to post that the answer worked.
The single and double quotes from above were used as reccomended to sorta force the SQL to read Boiling Spring Lakes as a complete string. That works just fine in .NET 2.0 and VS 2005.
However, MS Query doesn't translate it the same way. Turned out that the problem wasn't on my end, it was on the database side of it and faulty handling of the shortdesc and longdesc.
thank you all for your time and trouble.
Jack
Odd characters added to T-SQL Automatically
I am using Reporting Services 2000 and therefore VS 2003 and am having a problem editing the T-SQL query in the data view of Visual Studio.
Basically if I manually edit the T-SQL query or touch anything in the designer view of the query I get odd characters added in the T-SQL and the query no longer works. I have tried re-installing VS2003 and RS 2000 but it's still happening.
FYI I also have SQLServer 2005 and VS 2005 on my machine as all applications we develop are in .Net 2.0 and 3.0. We only use VS2003 for RS.
Does anyone have a clue how to fix this? Thanks
the characters being added are highlighted below:
... tablename left outer join (select ...) aliasname
WITH ?刓 ? 凶掱凸?凴?凳?刓 ? 凶?凴 凴 凴?刓 凲 ? 凶莊凴?凲?刓?刓 ? 凶?凶?凸?凴?凲 ? 凶?凳?刓?Ф?? 膋?
on tablename.column = aliasname.column ....
If I manually delete the highlighted section it puts it straight back in. I now can't edit any of our reports at all as its doing this to all of them. Help!Monday, February 20, 2012
ODBC LIKE clause does not work - SQL Server *BUG*
Hi All:
I think there is a problem with the LIKE clause in a
SELECT statement. I am using the latest SQL Server 2000
(ver 8.00.760) and Visual C/C++ 6.00 (with SP3) on
Windows XP.
I created an ODBC connection and tied into a database
containing a customer table. The following piece of code
returns a record count of 0 (zero) when, in fact, the
record count should be over 2000.
=========================
#include <afxwin.h>
#include <stdio.h>
#include <sql.h>
#include <sqlext.h>
#define DATABASE_CLAUSE "MPOS_SQLSERVER"
#define USERNAME_CLAUSE ""
#define PASSWORD_CLAUSE ""
#define SELECT_CLAUSE "SELECT COUNT(*) from Customers
WHERE LastName LIKE ? "
#define LIKE_CLAUSE "A%"
void main( void )
{
HENV hEnv = SQL_NULL_HENV;
HDBC hDbc = SQL_NULL_HDBC;
HSTMT hStmt = SQL_NULL_HSTMT;
long lValue = 0;
SQLINTEGER sqlNull = 0;
SQLINTEGER sqlStrLen = SQL_NTS;
SQLUINTEGER sqlColumnLen = strlen( LIKE_CLAUSE );
SQLINTEGER sqlBufferLen = strlen( LIKE_CLAUSE );
SQLINTEGER sqlValue = 0;
if ( ! SQL_SUCCEEDED( SQLAllocEnv( &hEnv )))
printf( "Error in SQLAllocEnv()\n" );
else if ( ! SQL_SUCCEEDED( SQLAllocConnect( hEnv,
&hDbc )))
printf( "Error in SQLAllocConnect()\n" );
else if ( ! SQL_SUCCEEDED( SQLConnect( hDbc,
(SQLCHAR *)
DATABASE_CLAUSE, SQL_NTS,
(SQLCHAR *)
USERNAME_CLAUSE, SQL_NTS,
(SQLCHAR *)
PASSWORD_CLAUSE, SQL_NTS )))
printf( "Error in SQLConnect()\n" );
else if ( ! SQL_SUCCEEDED( SQLAllocStmt( hDbc,
&hStmt )))
printf( "Error in SQLAllocStmt()\n" );
else if ( ! SQL_SUCCEEDED( SQLPrepare( hStmt,
(SQLCHAR *)
SELECT_CLAUSE,
SQL_NTS )))
printf( "Error in SQLPrepare()\n" );
else if ( ! SQL_SUCCEEDED( SQLBindParameter( hStmt,
1,
SQL_PARAM_INPUT,
SQL_C_CHAR,
SQL_CHAR,
0,
0,
LIKE_CLAUSE,
sqlBufferLen,
&sqlStrLen )))
printf( "Error in SQLBindParameter()\n" );
else if ( ! SQL_SUCCEEDED( SQLExecute( hStmt )))
printf( "Error in SQLExecute()\n" );
else if ( ! SQL_SUCCEEDED( SQLFetch( hStmt )))
printf( "Error in SQLFetch()\n" );
else if ( ! SQL_SUCCEEDED( SQLGetData( hStmt,
1,
SQL_C_LONG,
&sqlValue,
sizeof(
sqlValue ),
&sqlNull )))
printf( "Error in SQLGetData()\n" );
else
printf( "sqlValue/sizeof( sqlValue )/sqlNull = <%
ld>/<%ld>/<%ld>\n", sqlValue, sizeof( sqlValue ),
sqlNull );
}
=========================
When using the Query Analyzer and Enterprise Manager it
works. This is because, in the above code, I use the
SQLBindParameter() function to do the binding.
I've tried using the SQL Trace facility but it only shows
me the SQL statement being executed but does not indicate
any translations with the binded parameter.
Any suggestions or solutions would be greatly appreciated.
Regards,
AngeloWhen using SQL Trace, what is the text content of the query being executed?
Brannon Jones
Developer - MDAC
This posting is provided "as is" with no warranties and confers no rights.
"Angelo Kalpakis" <angelo@.mrsonline.ca> wrote in message
news:06b101c3c712$93afb310$a401280a@.phx.gbl...
from the log file...
=======================
rawodbc f38-f34 ENTER SQLPrepare
HSTMT 00391FE0
UCHAR * 0x00402104 [ -
3] "SELECT COUNT(*) from Customers WHERE LastName LIKE ?
\ 0"
SDWORD -3
=======================
If you'd like to see the entire log file, I can post that
too.
query being executed?
confers no rights.
rc = SQLBindParameter(hstmt,
1,
SQL_PARAM_INPUT,
SQL_C_CHAR,
SQL_CHAR,
2,
0,
"C%",
2,
NULL);
If I change the call to be like your example (passing 0 for the cbColDef
parameter) then I get an error trying to bind the parameter. What ODBC
version does your app set? 3.0 or earlier?
Try specifying the length of the string in the cbColDef parameter as well.
Brannon Jones
Developer - MDAC
This posting is provided "as is" with no warranties and confers no rights.
"Angelo Kalpakis" <angelo@.mrsonline.ca> wrote in message
news:0d9401c3d658$1c963ce0$a001280a@.phx.gbl...[QUOTE]
> When using the SQL Trace, the following is the snippet
> from the log file...
> =======================
> rawodbc f38-f34 ENTER SQLPrepare
> HSTMT 00391FE0
> UCHAR * 0x00402104 [ -
> 3] "SELECT COUNT(*) from Customers WHERE LastName LIKE ?
> \ 0"
> SDWORD -3
> =======================
> If you'd like to see the entire log file, I can post that
> too.
>
> query being executed?
> confers no rights.
> code
> SQL_CHAR,
> <%
> shows
> indicate
> appreciated.|||Thank you, thank you, thank you...
I am using ODBC version 2.x. The fix is the specification
of the 2 as the ColumnSize parameter.
It's funny how things work differently with Access and
SQLServer eventhough they are from the same manufacturer.
Thanks again.
for the cbColDef
parameter. What ODBC
parameter as well.
confers no rights.
different groups here at Microsoft).
If you are using the SQL driver in 2.x mode, then it will allow you to
specify a zero-length precision, but obviously the behavior is not what you
expected. If you are using the SQL driver in 3.0 mode, then it will error
out if you specify a zero-length precision.
When binding parameters, you should always give the precision. In this
case, if you give a precision of zero, then we think the parameter (on the
server-side) has a length of zero, and so we truncate whatever data you give
us. Not the best behavior, but that's how it works. The precision (in
conjunction with the SQL data type) describes the type on the server.
Brannon Jones
Developer - MDAC
This posting is provided "as is" with no warranties and confers no rights.
"Angelo Kalpakis" <angelo@.mrsonline.ca> wrote in message
news:0bc501c3d9e7$2fd1d7a0$a401280a@.phx.gbl...[QUOTE]
> Thank you, thank you, thank you...
> I am using ODBC version 2.x. The fix is the specification
> of the 2 as the ColumnSize parameter.
> It's funny how things work differently with Access and
> SQLServer eventhough they are from the same manufacturer.
> Thanks again.
>
> for the cbColDef
> parameter. What ODBC
> parameter as well.
> confers no rights.
> LIKE ?
> that
> and
> message
> 2000
> database
> the
> Customers
> LIKE_CLAUSE );
> hEnv,
> *)
> *)
> *)
> *)
> SQL_NTS )))
> hStmt,
> SQL_C_LONG,
> &sqlValue,
> &sqlNull )))
> sqlValue )/sqlNull =
> Manager it
I think there is a problem with the LIKE clause in a
SELECT statement. I am using the latest SQL Server 2000
(ver 8.00.760) and Visual C/C++ 6.00 (with SP3) on
Windows XP.
I created an ODBC connection and tied into a database
containing a customer table. The following piece of code
returns a record count of 0 (zero) when, in fact, the
record count should be over 2000.
=========================
#include <afxwin.h>
#include <stdio.h>
#include <sql.h>
#include <sqlext.h>
#define DATABASE_CLAUSE "MPOS_SQLSERVER"
#define USERNAME_CLAUSE ""
#define PASSWORD_CLAUSE ""
#define SELECT_CLAUSE "SELECT COUNT(*) from Customers
WHERE LastName LIKE ? "
#define LIKE_CLAUSE "A%"
void main( void )
{
HENV hEnv = SQL_NULL_HENV;
HDBC hDbc = SQL_NULL_HDBC;
HSTMT hStmt = SQL_NULL_HSTMT;
long lValue = 0;
SQLINTEGER sqlNull = 0;
SQLINTEGER sqlStrLen = SQL_NTS;
SQLUINTEGER sqlColumnLen = strlen( LIKE_CLAUSE );
SQLINTEGER sqlBufferLen = strlen( LIKE_CLAUSE );
SQLINTEGER sqlValue = 0;
if ( ! SQL_SUCCEEDED( SQLAllocEnv( &hEnv )))
printf( "Error in SQLAllocEnv()\n" );
else if ( ! SQL_SUCCEEDED( SQLAllocConnect( hEnv,
&hDbc )))
printf( "Error in SQLAllocConnect()\n" );
else if ( ! SQL_SUCCEEDED( SQLConnect( hDbc,
(SQLCHAR *)
DATABASE_CLAUSE, SQL_NTS,
(SQLCHAR *)
USERNAME_CLAUSE, SQL_NTS,
(SQLCHAR *)
PASSWORD_CLAUSE, SQL_NTS )))
printf( "Error in SQLConnect()\n" );
else if ( ! SQL_SUCCEEDED( SQLAllocStmt( hDbc,
&hStmt )))
printf( "Error in SQLAllocStmt()\n" );
else if ( ! SQL_SUCCEEDED( SQLPrepare( hStmt,
(SQLCHAR *)
SELECT_CLAUSE,
SQL_NTS )))
printf( "Error in SQLPrepare()\n" );
else if ( ! SQL_SUCCEEDED( SQLBindParameter( hStmt,
1,
SQL_PARAM_INPUT,
SQL_C_CHAR,
SQL_CHAR,
0,
0,
LIKE_CLAUSE,
sqlBufferLen,
&sqlStrLen )))
printf( "Error in SQLBindParameter()\n" );
else if ( ! SQL_SUCCEEDED( SQLExecute( hStmt )))
printf( "Error in SQLExecute()\n" );
else if ( ! SQL_SUCCEEDED( SQLFetch( hStmt )))
printf( "Error in SQLFetch()\n" );
else if ( ! SQL_SUCCEEDED( SQLGetData( hStmt,
1,
SQL_C_LONG,
&sqlValue,
sizeof(
sqlValue ),
&sqlNull )))
printf( "Error in SQLGetData()\n" );
else
printf( "sqlValue/sizeof( sqlValue )/sqlNull = <%
ld>/<%ld>/<%ld>\n", sqlValue, sizeof( sqlValue ),
sqlNull );
}
=========================
When using the Query Analyzer and Enterprise Manager it
works. This is because, in the above code, I use the
SQLBindParameter() function to do the binding.
I've tried using the SQL Trace facility but it only shows
me the SQL statement being executed but does not indicate
any translations with the binded parameter.
Any suggestions or solutions would be greatly appreciated.
Regards,
AngeloWhen using SQL Trace, what is the text content of the query being executed?
Brannon Jones
Developer - MDAC
This posting is provided "as is" with no warranties and confers no rights.
"Angelo Kalpakis" <angelo@.mrsonline.ca> wrote in message
news:06b101c3c712$93afb310$a401280a@.phx.gbl...
quote:|||When using the SQL Trace, the following is the snippet
> Hi All:
> I think there is a problem with the LIKE clause in a
> SELECT statement. I am using the latest SQL Server 2000
> (ver 8.00.760) and Visual C/C++ 6.00 (with SP3) on
> Windows XP.
> I created an ODBC connection and tied into a database
> containing a customer table. The following piece of code
> returns a record count of 0 (zero) when, in fact, the
> record count should be over 2000.
> =========================
> #include <afxwin.h>
> #include <stdio.h>
> #include <sql.h>
> #include <sqlext.h>
> #define DATABASE_CLAUSE "MPOS_SQLSERVER"
> #define USERNAME_CLAUSE ""
> #define PASSWORD_CLAUSE ""
> #define SELECT_CLAUSE "SELECT COUNT(*) from Customers
> WHERE LastName LIKE ? "
> #define LIKE_CLAUSE "A%"
> void main( void )
> {
> HENV hEnv = SQL_NULL_HENV;
> HDBC hDbc = SQL_NULL_HDBC;
> HSTMT hStmt = SQL_NULL_HSTMT;
> long lValue = 0;
> SQLINTEGER sqlNull = 0;
> SQLINTEGER sqlStrLen = SQL_NTS;
> SQLUINTEGER sqlColumnLen = strlen( LIKE_CLAUSE );
> SQLINTEGER sqlBufferLen = strlen( LIKE_CLAUSE );
> SQLINTEGER sqlValue = 0;
> if ( ! SQL_SUCCEEDED( SQLAllocEnv( &hEnv )))
> printf( "Error in SQLAllocEnv()\n" );
> else if ( ! SQL_SUCCEEDED( SQLAllocConnect( hEnv,
> &hDbc )))
> printf( "Error in SQLAllocConnect()\n" );
> else if ( ! SQL_SUCCEEDED( SQLConnect( hDbc,
> (SQLCHAR *)
> DATABASE_CLAUSE, SQL_NTS,
> (SQLCHAR *)
> USERNAME_CLAUSE, SQL_NTS,
> (SQLCHAR *)
> PASSWORD_CLAUSE, SQL_NTS )))
> printf( "Error in SQLConnect()\n" );
> else if ( ! SQL_SUCCEEDED( SQLAllocStmt( hDbc,
> &hStmt )))
> printf( "Error in SQLAllocStmt()\n" );
> else if ( ! SQL_SUCCEEDED( SQLPrepare( hStmt,
> (SQLCHAR *)
> SELECT_CLAUSE,
> SQL_NTS )))
> printf( "Error in SQLPrepare()\n" );
> else if ( ! SQL_SUCCEEDED( SQLBindParameter( hStmt,
> 1,
> SQL_PARAM_INPUT,
> SQL_C_CHAR,
> SQL_CHAR,
> 0,
> 0,
> LIKE_CLAUSE,
> sqlBufferLen,
> &sqlStrLen )))
> printf( "Error in SQLBindParameter()\n" );
> else if ( ! SQL_SUCCEEDED( SQLExecute( hStmt )))
> printf( "Error in SQLExecute()\n" );
> else if ( ! SQL_SUCCEEDED( SQLFetch( hStmt )))
> printf( "Error in SQLFetch()\n" );
> else if ( ! SQL_SUCCEEDED( SQLGetData( hStmt,
> 1,
> SQL_C_LONG,
> &sqlValue,
> sizeof(
> sqlValue ),
> &sqlNull )))
> printf( "Error in SQLGetData()\n" );
> else
> printf( "sqlValue/sizeof( sqlValue )/sqlNull = <%
> ld>/<%ld>/<%ld>\n", sqlValue, sizeof( sqlValue ),
> sqlNull );
> }
>
> =========================
> When using the Query Analyzer and Enterprise Manager it
> works. This is because, in the above code, I use the
> SQLBindParameter() function to do the binding.
> I've tried using the SQL Trace facility but it only shows
> me the SQL statement being executed but does not indicate
> any translations with the binded parameter.
> Any suggestions or solutions would be greatly appreciated.
> Regards,
> Angelo
from the log file...
=======================
rawodbc f38-f34 ENTER SQLPrepare
HSTMT 00391FE0
UCHAR * 0x00402104 [ -
3] "SELECT COUNT(*) from Customers WHERE LastName LIKE ?
\ 0"
SDWORD -3
=======================
If you'd like to see the entire log file, I can post that
too.
quote:
>--Original Message--
>When using SQL Trace, what is the text content of the
query being executed?
quote:
>--
>Brannon Jones
>Developer - MDAC
>This posting is provided "as is" with no warranties and
confers no rights.
quote:|||I tried the following and it works fine:
>
>"Angelo Kalpakis" <angelo@.mrsonline.ca> wrote in message
>news:06b101c3c712$93afb310$a401280a@.phx.gbl...
code[QUOTE]
SQL_CHAR,[QUOTE]
<%[QUOTE]
shows[QUOTE]
indicate[QUOTE]
appreciated.[QUOTE]
>
>.
>
rc = SQLBindParameter(hstmt,
1,
SQL_PARAM_INPUT,
SQL_C_CHAR,
SQL_CHAR,
2,
0,
"C%",
2,
NULL);
If I change the call to be like your example (passing 0 for the cbColDef
parameter) then I get an error trying to bind the parameter. What ODBC
version does your app set? 3.0 or earlier?
Try specifying the length of the string in the cbColDef parameter as well.
Brannon Jones
Developer - MDAC
This posting is provided "as is" with no warranties and confers no rights.
"Angelo Kalpakis" <angelo@.mrsonline.ca> wrote in message
news:0d9401c3d658$1c963ce0$a001280a@.phx.gbl...[QUOTE]
> When using the SQL Trace, the following is the snippet
> from the log file...
> =======================
> rawodbc f38-f34 ENTER SQLPrepare
> HSTMT 00391FE0
> UCHAR * 0x00402104 [ -
> 3] "SELECT COUNT(*) from Customers WHERE LastName LIKE ?
> \ 0"
> SDWORD -3
> =======================
> If you'd like to see the entire log file, I can post that
> too.
>
> query being executed?
> confers no rights.
> code
> SQL_CHAR,
> <%
> shows
> indicate
> appreciated.|||Thank you, thank you, thank you...
I am using ODBC version 2.x. The fix is the specification
of the 2 as the ColumnSize parameter.
It's funny how things work differently with Access and
SQLServer eventhough they are from the same manufacturer.
Thanks again.
quote:
>--Original Message--
>I tried the following and it works fine:
> rc = SQLBindParameter(hstmt,
> 1,
> SQL_PARAM_INPUT,
> SQL_C_CHAR,
> SQL_CHAR,
> 2,
> 0,
> "C%",
> 2,
> NULL);
>If I change the call to be like your example (passing 0
for the cbColDef
quote:
>parameter) then I get an error trying to bind the
parameter. What ODBC
quote:
>version does your app set? 3.0 or earlier?
>Try specifying the length of the string in the cbColDef
parameter as well.
quote:
>--
>Brannon Jones
>Developer - MDAC
>This posting is provided "as is" with no warranties and
confers no rights.
quote:|||Access and SQL Server are two completely different products (produced by
>
>"Angelo Kalpakis" <angelo@.mrsonline.ca> wrote in message
>news:0d9401c3d658$1c963ce0$a001280a@.phx.gbl...
LIKE ?[QUOTE]
that[QUOTE]
and[QUOTE]
message[QUOTE]
2000[QUOTE]
database[QUOTE]
the[QUOTE]
Customers[QUOTE]
LIKE_CLAUSE );[QUOTE]
hEnv,[QUOTE]
*)[QUOTE]
*)[QUOTE]
*)[QUOTE]
*)[QUOTE]
SQL_NTS )))[QUOTE]
hStmt,[QUOTE]
SQL_C_LONG,[QUOTE]
&sqlValue,[QUOTE]
&sqlNull )))[QUOTE]
sqlValue )/sqlNull =[QUOTE]
Manager it[QUOTE]
>
>.
>
different groups here at Microsoft).
If you are using the SQL driver in 2.x mode, then it will allow you to
specify a zero-length precision, but obviously the behavior is not what you
expected. If you are using the SQL driver in 3.0 mode, then it will error
out if you specify a zero-length precision.
When binding parameters, you should always give the precision. In this
case, if you give a precision of zero, then we think the parameter (on the
server-side) has a length of zero, and so we truncate whatever data you give
us. Not the best behavior, but that's how it works. The precision (in
conjunction with the SQL data type) describes the type on the server.
Brannon Jones
Developer - MDAC
This posting is provided "as is" with no warranties and confers no rights.
"Angelo Kalpakis" <angelo@.mrsonline.ca> wrote in message
news:0bc501c3d9e7$2fd1d7a0$a401280a@.phx.gbl...[QUOTE]
> Thank you, thank you, thank you...
> I am using ODBC version 2.x. The fix is the specification
> of the 2 as the ColumnSize parameter.
> It's funny how things work differently with Access and
> SQLServer eventhough they are from the same manufacturer.
> Thanks again.
>
> for the cbColDef
> parameter. What ODBC
> parameter as well.
> confers no rights.
> LIKE ?
> that
> and
> message
> 2000
> database
> the
> Customers
> LIKE_CLAUSE );
> hEnv,
> *)
> *)
> *)
> *)
> SQL_NTS )))
> hStmt,
> SQL_C_LONG,
> &sqlValue,
> &sqlNull )))
> sqlValue )/sqlNull =
> Manager it
Subscribe to:
Posts (Atom)