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

Offisite DB Connection Failure

I am trying to write a simple program for test purpose with VB .Net 2005 and SQL Server Express 2005. My server is not even in the same state as me, but I have the following on the server:

Server 2003 Web Edition SP1
SQL Server Express 2005

I am writing in the following:
Visual Basic 2005 Pro Edition

I have read where you can setup Express to allow TCP/IP connection, and I have done what it stated HERE (http://www.datamasker.com/SSE2005_NetworkCfg.htm). However, I am still not able to connect to the DB on the remote server.

I go to:
Tools --> Connect to Server --> Type IP and get the connection failed to connec to server "xxx.xxx.xxx.xxx"
I even tried:
Tools --> Connect to Server --> Type IP\SQLEXPRESS and get the connection failed to connec to server "xxx.xxx.xxx.xxx"

Has anyone been able to do this, or am I wasting my time? If I can get the SQL Server to do as requested then I am going to purchase and work with it, however, I want to test out before I spend the $$$$$ to begin the full project. If anyone can be of assistance here I would appreciate it.

Thanks in advance.Can you access the server on a remote machine on the same location as the server is (that is, in the same domain, at least without going through a firewall) ? It might have something to do with TCP/IP port 1433 that is not opened in the firewall ... and believe me, you don't want that !!!
Is your SQL server directly accessible from the Internet with IP address xxx.xxx.xxx.xxx ? Or should some routing be done because it's behind a firewall ? I would check your network admin for some assistence ...

Gr,
Yveausql

odd issue with NOT IN statement

Some info

We recently moved a database to SQL2005 (start of year) a program I wrote is having a small issue(not rejecting unmatched orders) and I narrowed it to one command statement (that I tried directly in the management studio)

I have two databases on the server (a live and a test DB) on the test DB (same tables and views as live but the data is a bit different) side there is no issue, while on the live side it is not working.

the command is

update wip set haserr=[order xref] where [order xref] not in (select [order xref] from m_orders)

on the test database if I add in the wip table a bad order number [order xref] it won't find it and it flags the haserr column and a reject statement is created for that order.

On the live database the haserr is not changed and so the filling of the order is not rejected when it needs to. It looks to be an issue with the "not in" command

PS at first I thought it was an update issue, but if I do

select * form wip where [order xref] not in (select [order xref] from m_orders)

edit: did some more testing

update wip set haserr=[order xref] where [order xref] in (select [order xref] from m_orders)

works

update wip set haserr=[order xref] where [order xref] not in (select [order xref] from m_orders)

does not

I get null

My knee-jerk reaction to this is to change NOT IN syntax into NOT EXISTS syntax.

update wip

set haserr=[order xref]

from wip a

inner join m_orders b

on a.[order xref] = b.[order xref]

There are issues with NOT IN when your field is null that can cause the results to be different than the way you might intuitively expect. When the result is a NULL it cannot be determined whether the record is IN or NOT IN the set because the NULL means unknown. There have been previous threads that discuss this; I will try to find a couple.

Here are a couple of posts:

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

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

|||

It is bcs of your null value on m_orders table..

(Ex)

Select * From MYTable Where Id in (1,3,4,null)

Will return 1,3,4

Select * From MyTable Where Id not in (1,3,4,null)

will not return any value

Why?

The not in (1,3,4,null) = not (Id =1 or Id =3 or Id=4 or Id=null)

= id <> 1 and Id <> 3 and Id <> 4 And id <> null

= True And True And True And NULL

= True And NULL

= FALSE

ie, ALWAYS FALSE

The in (1,3,4) = (Id =1 or Id =3 or Id=4 or Id=null)

= True or False Or null

= True Or NULL

= True

So change your query as follow as,

update wip set haserr=[order xref] where [order xref] not in (select [order xref] from m_orders Where [order xref] is NOT NULL)

|||NULL fails it, NULL values cant be compared.........sql

odd issue with IN statement

Some info

We recently moved a database to SQL2005 (start of year) a program I wrote is having a small issue(not rejecting unmatched orders) and I narrowed it to one command statement (that I tried directly in the management studio)

I have two databases on the server (a live and a test DB) on the test DB (same tables and views as live but the data is a bit different) side there is no issue, while on the live side it is not working.

the command is

update wip set haserr=[order xref] where [order xref] not in (select [order xref] from m_orders)

on the test database if I add in the wip table a bad order number [order xref] it won't find it and it flags the haserr column and a reject statement is created for that order.

On the live database the haserr is not changed and so the filling of the order is not rejected when it needs to. It looks to be an issue with the "not in" command

PS at first I thought it was an update issue, but if I do

select * form wip where [order xref] not in (select [order xref] from m_orders)

edit: did some more testing

update wip set haserr=[order xref] where [order xref] in (select [order xref] from m_orders)

works

update wip set haserr=[order xref] where [order xref] not in (select [order xref] from m_orders)

does not

I get null

My knee-jerk reaction to this is to change NOT IN syntax into NOT EXISTS syntax.

update wip

set haserr=[order xref]

from wip a

inner join m_orders b

on a.[order xref] = b.[order xref]

There are issues with NOT IN when your field is null that can cause the results to be different than the way you might intuitively expect. When the result is a NULL it cannot be determined whether the record is IN or NOT IN the set because the NULL means unknown. There have been previous threads that discuss this; I will try to find a couple.

Here are a couple of posts:

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

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

|||

It is bcs of your null value on m_orders table..

(Ex)

Select * From MYTable Where Id in (1,3,4,null)

Will return 1,3,4

Select * From MyTable Where Id not in (1,3,4,null)

will not return any value

Why?

The not in (1,3,4,null) = not (Id =1 or Id =3 or Id=4 or Id=null)

= id <> 1 and Id <> 3 and Id <> 4 And id <> null

= True And True And True And NULL

= True And NULL

= FALSE

ie, ALWAYS FALSE

The in (1,3,4) = (Id =1 or Id =3 or Id=4 or Id=null)

= True or False Or null

= True Or NULL

= True

So change your query as follow as,

update wip set haserr=[order xref] where [order xref] not in (select [order xref] from m_orders Where [order xref] is NOT NULL)

|||NULL fails it, NULL values cant be compared.........

ODBC: Call Failed 3146

Hello,
I have a vb program that connects to odbc to an sqlexpress server. When
i try to write data to the db, i get this message:
Release Error: Script #1 (Ascent Capture Database) [3146 Index Table
(dbo.DIGIDOS): ODBC: de oproep is mislukt.]
"de oproep is mislukt" is dutch for "Call failed"
It is an on/off problem, sometimes it works perfectly, but when i try
for the second time i get the error message again. Also found a
suggestion on the internet to walk through the errors of the
dbengine.error but this gave me nothing more then the shown error.
I am using windows xp and a local SQLexpress 2005 server. I am using
the sql server odbc driver 2000.85.1117.00 or should i been using the
sql native client version 2005.90.1399.00 ?
Can someone explain to me what i must do to correct this problem. ?
Thnx,
Johan
The Netherlands
ODBC call failed can be anything so it's not real fun to
troubleshoot. I think the easiest way though is to turn on
ODBC tracing on the client until you get the error. Having
ODBC tracing on will bog down the client so you want to try
to get the error soon and turn tracing off after that. Once
you hit the error, you can go through the ODBC trace log to
find the specific call that had the error.
You turn on ODBC tracing from the ODBC Data Source
Administrator. Go to the tracing tab and click on Start
Tracing now. Note where the log is going to - you can change
the location if you want. You turn off the tracing from the
same place. Just make sure you turn it back off.
-Sue
On 24 Sep 2006 02:54:21 -0700, "Johan"
<hello_2you@.hotmail.com> wrote:

>Hello,
>I have a vb program that connects to odbc to an sqlexpress server. When
>i try to write data to the db, i get this message:
>Release Error: Script #1 (Ascent Capture Database) [3146 Index Table
>(dbo.DIGIDOS): ODBC: de oproep is mislukt.]
>"de oproep is mislukt" is dutch for "Call failed"
>It is an on/off problem, sometimes it works perfectly, but when i try
>for the second time i get the error message again. Also found a
>suggestion on the internet to walk through the errors of the
>dbengine.error but this gave me nothing more then the shown error.
>I am using windows xp and a local SQLexpress 2005 server. I am using
>the sql server odbc driver 2000.85.1117.00 or should i been using the
>sql native client version 2005.90.1399.00 ?
>Can someone explain to me what i must do to correct this problem. ?
>Thnx,
>Johan
>The Netherlands
|||Thank you very much, the problem is solved. Did not know about the
tracer, but when i used it, it gave me just the info i needed. It
seemed i had a wrong unique index ;)
Fixed it and now everything runs smoothly !
Sue Hoegemeier wrote:[vbcol=seagreen]
> ODBC call failed can be anything so it's not real fun to
> troubleshoot. I think the easiest way though is to turn on
> ODBC tracing on the client until you get the error. Having
> ODBC tracing on will bog down the client so you want to try
> to get the error soon and turn tracing off after that. Once
> you hit the error, you can go through the ODBC trace log to
> find the specific call that had the error.
> You turn on ODBC tracing from the ODBC Data Source
> Administrator. Go to the tracing tab and click on Start
> Tracing now. Note where the log is going to - you can change
> the location if you want. You turn off the tracing from the
> same place. Just make sure you turn it back off.
> -Sue
> On 24 Sep 2006 02:54:21 -0700, "Johan"
> <hello_2you@.hotmail.com> wrote:
|||Glad it's fixed - thanks for posting back!
-Sue
On 25 Sep 2006 01:40:45 -0700, "Johan"
<hello_2you@.hotmail.com> wrote:
[vbcol=seagreen]
>Thank you very much, the problem is solved. Did not know about the
>tracer, but when i used it, it gave me just the info i needed. It
>seemed i had a wrong unique index ;)
>Fixed it and now everything runs smoothly !
>
>Sue Hoegemeier wrote:

ODBC: Call Failed 3146

Hello,
I have a vb program that connects to odbc to an sqlexpress server. When
i try to write data to the db, i get this message:
Release Error: Script #1 (Ascent Capture Database) [3146 Index Table
(dbo.DIGIDOS): ODBC: de oproep is mislukt.]
"de oproep is mislukt" is dutch for "Call failed"
It is an on/off problem, sometimes it works perfectly, but when i try
for the second time i get the error message again. Also found a
suggestion on the internet to walk through the errors of the
dbengine.error but this gave me nothing more then the shown error.
I am using Windows XP and a local SQLexpress 2005 server. I am using
the sql server odbc driver 2000.85.1117.00 or should i been using the
sql native client version 2005.90.1399.00 ?
Can someone explain to me what i must do to correct this problem. ?
Thnx,
Johan
The NetherlandsODBC call failed can be anything so it's not real fun to
troubleshoot. I think the easiest way though is to turn on
ODBC tracing on the client until you get the error. Having
ODBC tracing on will bog down the client so you want to try
to get the error soon and turn tracing off after that. Once
you hit the error, you can go through the ODBC trace log to
find the specific call that had the error.
You turn on ODBC tracing from the ODBC Data Source
Administrator. Go to the tracing tab and click on Start
Tracing now. Note where the log is going to - you can change
the location if you want. You turn off the tracing from the
same place. Just make sure you turn it back off.
-Sue
On 24 Sep 2006 02:54:21 -0700, "Johan"
<hello_2you@.hotmail.com> wrote:

>Hello,
>I have a vb program that connects to odbc to an sqlexpress server. When
>i try to write data to the db, i get this message:
>Release Error: Script #1 (Ascent Capture Database) [3146 Index Table
>(dbo.DIGIDOS): ODBC: de oproep is mislukt.]
>"de oproep is mislukt" is dutch for "Call failed"
>It is an on/off problem, sometimes it works perfectly, but when i try
>for the second time i get the error message again. Also found a
>suggestion on the internet to walk through the errors of the
>dbengine.error but this gave me nothing more then the shown error.
>I am using Windows XP and a local SQLexpress 2005 server. I am using
>the sql server odbc driver 2000.85.1117.00 or should i been using the
>sql native client version 2005.90.1399.00 ?
>Can someone explain to me what i must do to correct this problem. ?
>Thnx,
>Johan
>The Netherlands|||Thank you very much, the problem is solved. Did not know about the
tracer, but when i used it, it gave me just the info i needed. It
seemed i had a wrong unique index ;)
Fixed it and now everything runs smoothly !
Sue Hoegemeier wrote:[vbcol=seagreen]
> ODBC call failed can be anything so it's not real fun to
> troubleshoot. I think the easiest way though is to turn on
> ODBC tracing on the client until you get the error. Having
> ODBC tracing on will bog down the client so you want to try
> to get the error soon and turn tracing off after that. Once
> you hit the error, you can go through the ODBC trace log to
> find the specific call that had the error.
> You turn on ODBC tracing from the ODBC Data Source
> Administrator. Go to the tracing tab and click on Start
> Tracing now. Note where the log is going to - you can change
> the location if you want. You turn off the tracing from the
> same place. Just make sure you turn it back off.
> -Sue
> On 24 Sep 2006 02:54:21 -0700, "Johan"
> <hello_2you@.hotmail.com> wrote:
>|||Glad it's fixed - thanks for posting back!
-Sue
On 25 Sep 2006 01:40:45 -0700, "Johan"
<hello_2you@.hotmail.com> wrote:
[vbcol=seagreen]
>Thank you very much, the problem is solved. Did not know about the
>tracer, but when i used it, it gave me just the info i needed. It
>seemed i had a wrong unique index ;)
>Fixed it and now everything runs smoothly !
>
>Sue Hoegemeier wrote:

ODBC Timeout Error

Hi All,

I have one problem in connecting to SQL Server DSN thru Microsoft ODBC. The program was working properly and suddenly from few days, getting "-21472117871 [Microsoft][ODBC SQL Server Driver] Timeout Expired". And no new MS patches or SP installed recently.

I am using this in vbscript with the database connection with execute statement. Sometimes it takes 20-40 secs to get the resultset or sometimes timesout. What could be reason ? Is the database size, memory, transaction log size ?

For Example : Set adoAcctsRst = comDatabaseConnection.Execute("Services.dbo.AISSP_GetAccts '" & strUser & "' " )

Services is the Database, and AISSP_ is the stored procedure to execute with the input parameter as UserName. comDataBaseconnection is the command to connect to DSN.

Its getting timedout in this statement. What could be the solution ? Is it at the SQL Database or network connection ?

Appreciate for ur immedaite reply as this is urgent !!
Thanks in advanceWell it could be that the load on the DB is too much, it could be that the table needs a decent index on for the search criteria it could be load on the network.

You could also include your timeout on your connection when you connect to the database...

I would try the others first though,.. starting with the index...|||Do you have a baseline of what this stored procedure should take to run ? Have you run the stored procedure in query analyzer (how long does it take) ? If it taks a long time, copy the stored procedure content in qa and see if the duration is the same. If you had a recent jump/decline in the number of records that this stored procedure normally handles, this could also cause these problems.

ODBC SQL SERVER .. data would be Truncated!

Hey everyone ..

i have a website going .. .. ofcourse you use the Ws-FTP to upload the
photos to the page .. and i have to use SQL program to insert the file
names and values into the Database ..

i have been doing this for the longest time .. ill show u an
example below ..

Insert INTO PHoto (FOLDER, PIC_NAME, TAKEN_FROM, TAKEN_DATE, MONTH_YEAR,
DISPLAY_FOLDER, MONTH_YEAR_ID) VALUES('PHOTO BLAH BLAH BLAH .. ..
basically all the values .. the name and dates etc.

been doin it for the longest time .. excute the line and it will give me
an OK .. and my photo will be up on the website! ..

i tried uploading some files today .. and i get this weird message!

[microsoft][ODBC SQL server Driver][Sql server]String or binary data
would be truncated.

and now i can't put anythin into the database cause everytime i try to
excute and put those files up .. it gives me that error!

can anyone help plz?

--
Posted via http://dbforums.comArabian143 (dani@.windsorsocial.com) writes:
> Insert INTO PHoto (FOLDER, PIC_NAME, TAKEN_FROM, TAKEN_DATE, MONTH_YEAR,
> DISPLAY_FOLDER, MONTH_YEAR_ID) VALUES('PHOTO BLAH BLAH BLAH .. ..
> basically all the values .. the name and dates etc.
>...
> [microsoft][ODBC SQL server Driver][Sql server]String or binary data
> would be truncated.

This means that some of the values you are trying to insert is longer
than the column has foom for. For instance, assume that FOLDER is
char(20), but you try to insert 'C:\some-folder\some-sub-folder\files'
which is more than 20 chars.

If you don't care about truncation, include the statement SET ANSI_WARNINGS
OFF, and SQL will truncate in silence and not raise an error.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||thanx alot for responding ..

do i set the line next to the lines that im executing .. or beside them
.. or do i just execute the ANSI_warning thing by it self ..

when do i use this command and how many times do i have to use it!

thanx ..

im sorry im kind of new to this so i need exact details

This means that some of the values you are trying to insert is longer

than the column has foom for. For instance, assume that FOLDER is

char(20), but you try to insert 'C:\some-folder\some-sub-folder\files'

which is more than 20 chars.

If you don't care about truncation, include the statement SET
ANSI_WARNINGS

OFF, and SQL will truncate in silence and not raise an error.

--

Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

--
Posted via http://dbforums.com|||hi again ..

i tried that line..

is said ok ..after i executed it ..

but i still got the same error after i tried to send a file to
the database!

im so lost man!

HELP!!!!!!!!!!!!!!!

This means that some of the values you are trying to insert is longer

than the column has foom for. For instance, assume that FOLDER is

char(20), but you try to insert 'C:\some-folder\some-sub-folder\files'

which is more than 20 chars.

If you don't care about truncation, include the statement SET
ANSI_WARNINGS

OFF, and SQL will truncate in silence and not raise an error.

--

Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

--
Posted via http://dbforums.com|||nevermind i got it working

thing had too many god damn values

it was a big value .. but i had to narrow it down

but u gave me the idea and i thank u ..

bye for now

--
Posted via http://dbforums.com

ODBC security problem

Hi, I've a Access 2003 program with linked tables to a sqlexpress configured
with mixed mode security.
From clients XP no problem.
From clients 2000 MSACCESS don't get the right configuration from ODBC
source, because try to conect with Windows Authentication Mode security, but
the source is configurated with mixed mode. If I refresh the linked tables
and change the password, works fine, but when I close MSACCESS and open
again, fail.
Anybody knows where's the bug? In ODBC driver? MSACCESS?You could be hitting an MDAC bug where the authentication
mode setting is ignored and windows authentication is always
used. Refer to:
FIX: SQL Server ODBC Driver Ignores Authentication Setting
http://support.microsoft.com/?id=279526
You can use component checker to verify the version and MDAC
installation on any of the clients. You can download
component checker and MDAC versions from this site:
http://msdn.microsoft.com/data/ref/mdac/downloads/
-Sue
On Fri, 18 Aug 2006 11:27:01 -0700, Negri
<Negri@.discussions.microsoft.com> wrote:

>Hi, I've a Access 2003 program with linked tables to a sqlexpress configure
d
>with mixed mode security.
>From clients XP no problem.
>From clients 2000 MSACCESS don't get the right configuration from ODBC
>source, because try to conect with Windows Authentication Mode security, bu
t
>the source is configurated with mixed mode. If I refresh the linked tables
>and change the password, works fine, but when I close MSACCESS and open
>again, fail.
>Anybody knows where's the bug? In ODBC driver? MSACCESS?|||Thanks Sue, I'll try it next monday and tell you.
"Sue Hoegemeier" wrote:

> You could be hitting an MDAC bug where the authentication
> mode setting is ignored and windows authentication is always
> used. Refer to:
> FIX: SQL Server ODBC Driver Ignores Authentication Setting
> http://support.microsoft.com/?id=279526
> You can use component checker to verify the version and MDAC
> installation on any of the clients. You can download
> component checker and MDAC versions from this site:
> http://msdn.microsoft.com/data/ref/mdac/downloads/
> -Sue
> On Fri, 18 Aug 2006 11:27:01 -0700, Negri
> <Negri@.discussions.microsoft.com> wrote:
>
>

ODBC Program & Heterogeneous queries problem

Best All,

What I am doing is following:
1. I created a stored procedure "testit" which select records from another data source in another SQL Server, like:

------------
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
go

create procedure testit
as begin
select * from [AnotherSQLServer].bugtest.dbo.mssent
end

grant execute on testit to public
------------

2. I can execute this stored procedure in SQL Query Analyser with success.

3. BUT: now I write a ODBC program trying to call this stored procedure, then it give me following "famous" error:
42000 [Microsoft][ODBC SQL Server Driver][SQL Server]Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query.

The ODBC program looks like this:
--------
strcpy((char *) sqltext, "{call testit}");
rc = SQLPrepare(hstmt, (unsigned char *) sqltext, SQL_NTS);
rc = SQLExecute(hstmt);
--------

My Question:
1. Does ODBC API support Heterogeneous queries ?
2. If well, how to solve above problem?

Thanks a lot,

MelThe solution can be found here:

http://dbforums.com/t905850.html

use some thing like this (before calling SQLConnect() ):
SQLSetConnectAttr( hdbc,SQL_COPT_SS_ANSI_NPW,(void *) SQL_AD_ON, 0);

ODBC Newbie needs help

Hello all,
I have a Surviellance Incident reporting program with a SQL *Server
2000
backend and I want to have a Access 2000 front end. My boss *wants me
to
use ODBC to connect to the SQL backend. I set up the ODBC da*ta source

but I have no idea how to connect to it using ODBC. The lite*rature
i've
read is not clear. Do I need to write code for this or is it* a
setting
in the Access 2000 front end? My Access 2000 front end will *include
forms for users to enter data, reports based on stored prodc*edures.
Just getting me started a huge help. Thank youOn 2/1/05 12:07 PM, in article
1107276885.472781.237470@.z14g2000cwz.googlegroups. com, "ndn_24_7"
<ndn_24_7@.yahoo.com> wrote:

> Hello all,
> I have a Surviellance Incident reporting program with a SQL *Server
> 2000
> backend and I want to have a Access 2000 front end. My boss *wants me
> to
> use ODBC to connect to the SQL backend. I set up the ODBC da*ta source
> but I have no idea how to connect to it using ODBC. The lite*rature
> i've
> read is not clear. Do I need to write code for this or is it* a
> setting
> in the Access 2000 front end? My Access 2000 front end will *include
> forms for users to enter data, reports based on stored prodc*edures.
> Just getting me started a huge help. Thank you

Simply link to the tables within Access 2000 and use them as if they were
local.

If I remember right: Right-click on the area where the tables would be
listed and select Link Tables. When the dialog pops up select Files of type:
ODBC Database. If you do not have a DSN created now would be the time to do
it. Select the DSN. Select the tables to include and a series of dialogs
will appear that you will identify primary key fields.

If your SQL database ever changes (schema) you will need to update te links
in Access by using the Linked Table Manager.

-Greg

ODBC LIKE clause

I'm trying to execute the following query within a Visual
C++ program...
SELECT LastName from Employees WHERE LastName LIKE ?
In the C++ program, I use the SQLBindParameter() to
associate to the ?. The SQLBindParameter() variable used
contains 'A%' (without the single quote).
This is a very simple statement, but constantly returns
no data.
I run this exact statement within a MS Access database
connection, and it returns 4 records. The MS Access wild
card is also 'A%'. I think there is a bug in the SQL
Server ODBC, but I cannot confirm this.
Using the SQL Trace doesn't tell me the translations
being performed within the ODBC driver to confirm a bug.
Help...please...
Regards,
AngeloHi Angelo,
The SQL Trace (you are using SQL 7 ?) will tell you the command the SQL
Servers sees. What does that look like?
Also have you tried 'A*' or have you tired
SELECT LastName from Employees WHERE LastName LIKE '?'
Just some ideas, I hope you fix the issue.
I hope this helps
regards
Greg O MCSD
http://www.ag-software.com/ags_scribe_index.asp. SQL Scribe Documentation
Builder, the quickest way to document your database
http://www.ag-software.com/ags_SSEPE_index.asp. AGS SQL Server Extended
Property Extended properties manager for SQL 2000
http://www.ag-software.com/IconExtractionProgram.asp. Free icon extraction
program
http://www.ag-software.com. Free programming tools
"Angelo" <anonymous@.discussions.microsoft.com> wrote in message
news:04ee01c3b861$09b06700$a301280a@.phx.gbl...

quote:

---

> I'm trying to execute the following query within a Visual
> C++ program...
> SELECT LastName from Employees WHERE LastName LIKE ?
> In the C++ program, I use the SQLBindParameter() to
> associate to the ?. The SQLBindParameter() variable used
> contains 'A%' (without the single quote).
> This is a very simple statement, but constantly returns
> no data.
> I run this exact statement within a MS Access database
> connection, and it returns 4 records. The MS Access wild
> card is also 'A%'. I think there is a bug in the SQL
> Server ODBC, but I cannot confirm this.
> Using the SQL Trace doesn't tell me the translations
> being performed within the ODBC driver to confirm a bug.
> Help...please...
> Regards,
> Angelo

---

|||Thanks for the reply...
I'm using SQL 8. I will try the TRACE again. I've tried
your other suggestions but still get the same results.
Later,

quote:

---

>--Original Message--
>Hi Angelo,
>The SQL Trace (you are using SQL 7 ?) will tell you the

---

command the SQL

quote:

---

>Servers sees. What does that look like?
>Also have you tried 'A*' or have you tired
>SELECT LastName from Employees WHERE LastName LIKE '?'
>Just some ideas, I hope you fix the issue.
>
>--
>I hope this helps
>regards
>Greg O MCSD
>http://www.ag-software.com/ags_scribe_index.asp. SQL

---

Scribe Documentation

quote:

---

>Builder, the quickest way to document your database
>http://www.ag-software.com/ags_SSEPE_index.asp. AGS SQL

---

Server Extended

quote:

---

>Property Extended properties manager for SQL 2000
>http://www.ag-software.com/IconExtractionProgram.asp.

---

Free icon extraction

quote:

---

>program
>http://www.ag-software.com. Free programming tools
>
>"Angelo" <anonymous@.discussions.microsoft.com> wrote in

---

message

quote:

---

>news:04ee01c3b861$09b06700$a301280a@.phx.gbl...
Visual[QUOTE]
used[QUOTE]
wild[QUOTE]
bug.[QUOTE]
>
>.
>

---

|||Could you post all the parameters for SQLBindParameter() method. I am
pretty confident that 'A%' is the correct syntax
Pete
Angelo wrote:

quote:

---

>I'm trying to execute the following query within a Visual
>C++ program...
>SELECT LastName from Employees WHERE LastName LIKE ?
>In the C++ program, I use the SQLBindParameter() to
>associate to the ?. The SQLBindParameter() variable used
>contains 'A%' (without the single quote).
>This is a very simple statement, but constantly returns
>no data.
>I run this exact statement within a MS Access database
>connection, and it returns 4 records. The MS Access wild
>card is also 'A%'. I think there is a bug in the SQL
>Server ODBC, but I cannot confirm this.
>Using the SQL Trace doesn't tell me the translations
>being performed within the ODBC driver to confirm a bug.
>Help...please...
>Regards,
>Angelo
>

---

You don't pay to get spam, why pay to clean it?
Visit http://www.spammarshall.com to create an account for free
<http://www.spammarshall.com>|||I'm probably saying something pretty dumb right now.. but how are you using
ODBC?
Why can you not simply (assuming you're using MS Visual C++ with MFC) do:
CString sql;
sql.Format(_T("SELECT LastName from Employees WHERE LastName LIKE %s",
_T("A%%");
rs.Open(sql...)
Or something like that.. ?
"Angelo" <anonymous@.discussions.microsoft.com> wrote in message
news:04ee01c3b861$09b06700$a301280a@.phx.gbl...

quote:

---

> I'm trying to execute the following query within a Visual
> C++ program...
> SELECT LastName from Employees WHERE LastName LIKE ?
> In the C++ program, I use the SQLBindParameter() to
> associate to the ?. The SQLBindParameter() variable used
> contains 'A%' (without the single quote).
> This is a very simple statement, but constantly returns
> no data.
> I run this exact statement within a MS Access database
> connection, and it returns 4 records. The MS Access wild
> card is also 'A%'. I think there is a bug in the SQL
> Server ODBC, but I cannot confirm this.
> Using the SQL Trace doesn't tell me the translations
> being performed within the ODBC driver to confirm a bug.
> Help...please...
> Regards,
> Angelo

---

|||Here is a sample application that results in a zero
records found eventhrough there are a number of records
within the table.
================================
#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 );
}
================================
I am using the latest SQL Server 8 with Visual C/C++ 6.00.
Everything has the latest software patches including the
DB and compilers.
Regards,
Angelo

quote:

---

>--Original Message--
>Could you post all the parameters for SQLBindParameter()

---

method. I am

quote:

---

>pretty confident that 'A%' is the correct syntax
>Pete
>Angelo wrote:
>
Visual[QUOTE]
used[QUOTE]
wild[QUOTE]
>--
>You don't pay to get spam, why pay to clean it?
>Visit http://www.spammarshall.com to create an account

---

for free

quote:

---

><http://www.spammarshall.com>
>
>

---