SQLCA.DBMS = "SNC SQL Native Client(OLE DB)" issues with PB11.5 and MSSqlServer 2005

[cfauvel]

Note: this works find for PB9.0.2 using
dbms= OLE DB
DBPARM =
"PROVIDER='SQLOLEDB',DATASOURCE='MyLovelyServer',PROVIDERSTRING='database=asideductions'"

and works fine for PB11.5 using
dbms= OLE DB
DBPARM =
"PROVIDER='SQLOLEDB',DATASOURCE='MyLovelyServer',PROVIDERSTRING='database=asideductions'"
and
dbms= OLE DB
DBPARM =
"PROVIDER='SQLNCLI',DATASOURCE='MyLovelyServer',PROVIDERSTRING='database=asideductions'"

here is the setup
in database ASIDEDUCTIONS I have a view that selects 2 columns from
database/table ASIBIG6.dbo.bto_clients, the view is named the same as the
table.

one of my DDDW is built off of that view by a simple select statement,
select Client_id, Client_name from bto_clients.

If I connect via
DBMS = "SNC SQL Native Client(OLE DB)"
ServerName = "MyLovelyServer"
DBParm = "Database='asideductions'
within the IDE and call up that DDDW it retrieves just fine

If I run the app via running man, using the same paramters in my INI file,
the datawindow/window that has that dddw barks with the error
DB Error Code : 208
errortext: Select error: sqlstate = 42502
Microsoft SQL Native Client
Invalid objectname 'bto_clients'

the view has SELECT rights to public, the table that it refers to has SELECT
rights to public, the userid I'm using to connect has access to both
databases. I'm stumped. Anybody has ideas?

Either way I'll probably just use

dbms= OLE DB
DBPARM =
"PROVIDER='SQLNCLI',DATASOURCE='MyLovelyServer',PROVIDERSTRING='database=asideductions'"

[Scott Morris]

> If I run the app via running man, using the same paramters in my INI file,
> the datawindow/window that has that dddw barks with the error
> DB Error Code : 208
> errortext: Select error: sqlstate = 42502
> Microsoft SQL Native Client
> Invalid objectname 'bto_clients'
>
> the view has SELECT rights to public, the table that it refers to has
> SELECT rights to public, the userid I'm using to connect has access to
> both databases. I'm stumped. Anybody has ideas?

Most likely explanation is a schema issue - i.e., bto_clients is not in the
default schema of the user. I would use the Profiler to look at the exact
statement that fails - could be some system procedure that PB uses to verify
binding. You should also verify that your view fully qualifies the table
name.

[cfauvel]

Hey Scott my connectivity expert to MSSQLServer

But I found it and low and behold PB/MSsqlserver wasn't lying about a non
existent bto_clients

The connection string for the app's INI file originally looked like

DBMS = "SNC SQL Native Client(OLE DB)"

UserId=
DatabasePassword=
LogId=
LogPassword
AutoCommit=Yes
ServerName = "MyLovelyServer"
DBParm = Database=asideductions

Upon conencting to the database, since the dbparm wasn't formed correctly it
was connecting me to my default database ASIBIG6, where indeed the view,
bto_clients, doesn't exists, the table bto_client does.

changed the dbaparm to

DBMS = "SNC SQL Native Client(OLE DB)"

UserId=
DatabasePassword=
LogId=
LogPassword
AutoCommit=Yes

ServerName = "MyLovelyServer"
DBParm = "Database='asideductions'"

and all is well

The INI file has just what is shown above, but just before I connect I add
some more parms, it ends up looking like

DBMS = "SNC SQL Native Client(OLE DB)"

UserId=
DatabasePassword=
LogId=""
LogPassword
AutoCommit=True
Database="asideductions"
ServerName = "MyLovelyServer"
DBPARM = "Database='asideductions'; Appname= 'ASI DEDLOG
V11',PacketSize=8192,HOST ='MyPC',ProviderString='MARS
Connection=False',ConnectString='UID=Myself;PWD=qwerty' "

I stumbled on it by running sp_who2, looking to see if I had a malformed
dbparm, by looking at the host name and appname which I set explicitly,
there on the result set I noticed the app was connected to ASIBIG6 rather
than ASIDEDUCTIONS...

Is there any real advantage of using

DBMS = "SNC SQL Native Client(OLE DB)"

over
DBMS = "OLE DB"
DBParm = "PROVIDER='SQLNCLI'....

Thanks for getting me digging deeper

"Scott Morris" <bo...@bogus.com> wrote in message
news:49245895$1@forums-1-dub...

[Scott Morris]

> Is there any real advantage of using
> DBMS = "SNC SQL Native Client(OLE DB)"
> over
> DBMS = "OLE DB"
> DBParm = "PROVIDER='SQLNCLI'....
>
> Thanks for getting me digging deeper

I have no real idea which is better. Since MS has chosen another new
direction (native client), my guess is that SNC is the safer choice for MS
SQL Server.

[Chris Pollach]

From what I have heard, SNC is M$'s preferred direction at this
point-in-time.

"Scott Morris" <bo...@bogus.com> wrote in message

news:492ae3ca@forums-1-dub...

[M. Searer]

FWIW, I had found a few problems with SNC with PB 11.1.
Switching to OLE DB with SQLNCLI provider was the workaround.

"cfauvel" <CFa...@yahoo.com> wrote in message news:4924e878$1@forums-1-dub...

> Hey Scott my connectivity expert to MSSQLServer
>

> Is there any real advantage of using

[Chris Fauvel]

have you tried adding

ProviderString='MARS Connection=False'

when conencting to SNC? that fixed me up. I kept getting trans count errors
everywhere before that...in 11.2 and 11.5

"M. Searer" <nos...@nospam.com> wrote in message
news:492b0021$1@forums-1-dub...

[M. Searer]

no. I can't imagine a MARS setting cause problems such as:

- datawindow identity setting and SNC causes application crash. SNC automatically sets columns as identity even when they aren't.
- SNC bug renames db computed column
- Using the SNC with static bind set to 1 and connecting to a SQL 2005 database, PB does not retreive the SQL text datatype values
if the TEXT column was defined as 4099 in size. If the text column size is reset by messing with the sql, then it gets defined as
32766 in size and then the SNC does retrieve the text correctly.

"Chris Fauvel" <Chris....@gte.net> wrote in message news:492c1c7c@forums-1-dub...