Solved Impossible to call sp_setapprole through OLE DB to MS SQL Server.

forum.gupta.bugreport (2005-2010)
Vladimir Gloushenkov

Impossible to call sp_setapprole through OLE DB to MS SQL Server.

Post by Vladimir Gloushenkov » 17 May 2005, 23:03

 Posted by:  Vladimir Gloushenkov 

Product=Team Developer
Version=2005
PTF=none
O/S=Windows XP
Occurs=Run Time
Details=Impossible to call sp_setapprole through OLE DB to MS SQL Server.
Nest level is too high...

More details :

! This code works (ODBC TD2005)
Set SqlDatabase = 'HKPG'
Set SqlUser = 'RoleTest'
Set SqlPassword = 'RoleTest'
Call SqlConnect( hSql )
Call SqlPrepareAndExecute( hSql, "sp_setapprole 'TestAppRole','123' " )

! This code works (OLEDB - Visual Studio)
OleDbConnection conn = new OleDbConnection("Provider=SQLOLEDB;User
ID=RoleTest;Pwd=RoleTest;Data Source=test2003sql;Initial Catalog=hkpg;");
conn.Open();
OleDbCommand comm = new OleDbCommand("sp_setapprole
'TestAppRole','123'",conn);
comm.ExecuteNonQuery();

! This code does not work (OLEDB TD2005)
Set SqlUDL = 'Provider=SQLOLEDB;User ID=RoleTest;Pwd=RoleTest;Data
Source=test2003sql;Initial Catalog=hkpg;'
Call SqlCreateSession( hSession, SqlUDL )
Call SqlCreateStatement( hSession, hSql )
Call SqlPrepareAndExecute( hSql, "sp_setapprole 'TestAppRole','123' " )
! SqlPrepareAndExecute fails with : Err No: 15422 :Application roles can
only be activated at the ad hoc level.;

Nothing of the following helped :
Playing with "Use Procedure for Prepare=0" or "OLE DB Services=0" or "Use
Client Cursor=1/0" in connection string.
Trying to set SqlSetSessionParameter( Session_Handle, Number, Number,
String ) with DBPROP_INIT_OLEDBSERVICES or DBPROP_CLIENTCURSOR - complains
about invalid property id.

Vladimir Gloushenkov

Impossible to call sp_setapprole through OLE DB to MS SQL Server. Nest level is too high...

Post by Vladimir Gloushenkov » 06 Jun 2005, 21:46

 Posted by:  Vladimir Gloushenkov 

There is a way around it!!! : You can use the ODBC syntax !:

Call SqlPrepareAndExecute ( hSql, "{ Call sp_setapprole('JMGAppRole',
'support') }")

User avatar
Steve Leighton
Site Admin
Site Admin
New Zealand
Posts: 183
Joined: 05 Mar 2017, 20:57
Location: Tauranga, New Zealand <--> Stroud, England

Re: Impossible to call sp_setapprole through OLE DB to MS SQL Server. Nest level is too high...

Post by Steve Leighton » 06 Mar 2020, 05:42

+1

2020 --> TD 7.3 --> OLE Db --> SQLServer. Can confirm this is still the case. ODBC syntax works for OLE DB. Nothing else does.
Greetings from New Zealand
Steve Leighton

Bankside Systems Ltd.
UK ♦ Australia ♦ New Zealand

www.banksidesystems.co.uk

Image

Return to “gupta.bugreport”

Who is online

Users browsing this forum: [Ccbot] and 0 guests