Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

SyntaxFromSQL fails when not sysadmin

3 posts in DataWindow Last posting was on 2008-09-25 15:35:53.0Z
NUtshell Posted on 2008-09-25 09:05:40.0Z
Sender: 2b5c.48d8b84a.1804289383@sybase.com
From: nutshell
Newsgroups: sybase.public.powerbuilder.datawindow
Subject: SyntaxFromSQL fails when not sysadmin
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <48db5464.20ef.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 25 Sep 2008 02:05:40 -0700
X-Trace: forums-1-dub 1222333540 10.22.241.41 (25 Sep 2008 02:05:40 -0700)
X-Original-Trace: 25 Sep 2008 02:05:40 -0700, 10.22.241.41
Lines: 31
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:87845
Article PK: 417101

Hi all!

This is a new thread on my problems with SyntaxFromSQL as I
now have better understanding of things involved.

- PB 11.3
- SQL Server 2005
- Integrated logon
- OLE DB
- Active directory is used in LAN

Situation:
Using SyntaxFromSQL() I dynamically create a dw that needs
to be updatable. This works fine on one server, and fails on
an other. Table and attributes are identical on both
machines. The machine/server where it works I'm a sysadmin.

When PB creates the datawindow it uses SQL Server system
tables to get columns and the PK column. This is done via a
call to sp_oledb_ro_usrname(), which in turn calls
user_name(). user_name() returns 'dbo' when I'm sysadmin and
my user-name when I'm not. The returned name is then used by
PB as SchemaName for consecutive calls other SQL Server
procedures in order to get the table key column. Now using
my username as schema name fails as no such schema exists.

This is confusing...why is PB using username as schema name?
Can I "tell" PB to use 'dbo' and nothing else?

Thanks
/Nut


Scott Morris Posted on 2008-09-25 14:11:11.0Z
From: "Scott Morris" <bogus@bogus.com>
Newsgroups: sybase.public.powerbuilder.datawindow
References: <48db5464.20ef.1681692777@sybase.com>
Subject: Re: SyntaxFromSQL fails when not sysadmin
Lines: 28
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5512
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <48db9bff$1@forums-1-dub>
Date: 25 Sep 2008 07:11:11 -0700
X-Trace: forums-1-dub 1222351871 10.22.241.152 (25 Sep 2008 07:11:11 -0700)
X-Original-Trace: 25 Sep 2008 07:11:11 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:87847
Article PK: 417103


>
> When PB creates the datawindow it uses SQL Server system
> tables to get columns and the PK column. This is done via a
> call to sp_oledb_ro_usrname(), which in turn calls
> user_name(). user_name() returns 'dbo' when I'm sysadmin and
> my user-name when I'm not. The returned name is then used by
> PB as SchemaName for consecutive calls other SQL Server
> procedures in order to get the table key column. Now using
> my username as schema name fails as no such schema exists.
>
> This is confusing...why is PB using username as schema name?
> Can I "tell" PB to use 'dbo' and nothing else?

I don't believe that this is a PB issue. The first thing you should
consider is to fully qualify your object (table in this case) names - this
is considered a best practice now. That alone may be sufficient to resolve
the issue. Since you don't fully qualify the table name, you are relying on
multiple layers of logic to resolve it for you. If this is something you
want to rely on, then I'm guessing that the users (who will be performing
such functions) need to directly associated with the appropriate schema
(dbo) for those cases. You associate users with schemas using the create
user / alter user tsql statements.

You should take some time and review the sql server 2005 documentation
regarding schemas and users and how those are more explicitly separated in
2005 (vs. 2000).


"Jerry Siegel [TeamSybase]" <jNOsSPAMsiegel Posted on 2008-09-25 15:35:53.0Z
From: "Jerry Siegel [TeamSybase]" <jNOsSPAMsiegel@yahoo!.com>
Newsgroups: sybase.public.powerbuilder.datawindow
References: <48db5464.20ef.1681692777@sybase.com> <48db9bff$1@forums-1-dub>
Subject: Re: SyntaxFromSQL fails when not sysadmin
Lines: 37
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5512
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <48dbafd9$1@forums-1-dub>
Date: 25 Sep 2008 08:35:53 -0700
X-Trace: forums-1-dub 1222356953 10.22.241.152 (25 Sep 2008 08:35:53 -0700)
X-Original-Trace: 25 Sep 2008 08:35:53 -0700, vip152.sybase.com
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:87850
Article PK: 417106

In applcations I have worked on, the practice was to create DW with no owner
prefixed to the table name, by connecting the IDE using a profile with the
owner ID on the development server. The actual table was then resolved with
public synonyms on the integration test and production servers. That was
Oracle, I don't know if you can do that on MSSQL.

"Scott Morris" <bogus@bogus.com> wrote in message
news:48db9bff$1@forums-1-dub...
> >
>> When PB creates the datawindow it uses SQL Server system
>> tables to get columns and the PK column. This is done via a
>> call to sp_oledb_ro_usrname(), which in turn calls
>> user_name(). user_name() returns 'dbo' when I'm sysadmin and
>> my user-name when I'm not. The returned name is then used by
>> PB as SchemaName for consecutive calls other SQL Server
>> procedures in order to get the table key column. Now using
>> my username as schema name fails as no such schema exists.
>>
>> This is confusing...why is PB using username as schema name?
>> Can I "tell" PB to use 'dbo' and nothing else?
>
> I don't believe that this is a PB issue. The first thing you should
> consider is to fully qualify your object (table in this case) names - this
> is considered a best practice now. That alone may be sufficient to
> resolve the issue. Since you don't fully qualify the table name, you are
> relying on multiple layers of logic to resolve it for you. If this is
> something you want to rely on, then I'm guessing that the users (who will
> be performing such functions) need to directly associated with the
> appropriate schema (dbo) for those cases. You associate users with
> schemas using the create user / alter user tsql statements.
>
> You should take some time and review the sql server 2005 documentation
> regarding schemas and users and how those are more explicitly separated in
> 2005 (vs. 2000).
>