Call sp_setapprole from vb w/ADO

[Karina Gámez]

Did u know something about working with "sp_setapprole" from vb...?

I already open the connection.. but now.. i have to search some txt files from a cd and then send it to a tmp table in sql server...

Do u know how can i do it.. or where can i find it??

Thanks a lot...

Take care..URGENTTTTTTTTTTTTTTTTTTT

[phunkydude]

Hi Karina,
Are you still having issues with the Bulk Copying? Pity you didn't use my posting on BULK INSERT, anyway...
If you're trying to do Bulk Copying then the user on the conneciton MUST BE a member of the sysadmins group (something I think an App Role isn't capable of). I'll check into this (for my own benefit also).

[Karina Gámez]

I KNOW!!! BUT MY BOSS WANTS TO USE THIS...

SO.. NOW IM TRYING TO CALL THE SP_SETAPPROLE.. AND THEN I HAVE TI INSERT ON MY TABLE MY TXT FILES FROM A CD ...

[phunkydude]

Try this out
set cmdAppRole = new ADODB.Command
cmdAppRole.CommandText = "sp_setapprole"
'set connection to previously connected and open one
cmdAppRole.ActiveConnection = m_conTXNs

set prmAppName = new ADODB.Parameter
prmAppName.Name = "rolename"
prmAppName.Direction = adParamInput
prmAppName.Value = "AgencyProcessor"
prmAppName.Type = adLongVarWChar
prmAppName.Size = 128

cmdAppRole.Parameters.Append prmAppName

set prmAppPW = new ADODB.Parameter
prmAppPW.Name = "password"
prmAppPW.Direction = adParamInput
prmAppPW.Value = "password"
prmAppPW.Type = adLongVarWChar
prmAppPW.Size = 128

cmdAppRole.Parameters.Append prmAppPW

cmdAppRole.CommandTimeout = 0
cmdAppRole.Execute , , adCmdStoredProc + adExecuteNoRecords


The T-SQL debugger ended up doing some funny stuff, so I'm not so sure that it actually works, no errors were returned though.

[phunkydude]

Try this out

set cmdAppRole = new ADODB.Command cmdAppRole.CommandText = "sp_setapprole"
'set connection to previously connected and open one
cmdAppRole.ActiveConnection = m_conTXNs
set prmAppName = new ADODB.Parameter
prmAppName.Name = "rolename"
prmAppName.Direction = adParamInput
prmAppName.Value = "Application"
prmAppName.Type = adLongVarWChar
prmAppName.Size = 128
cmdAppRole.Parameters.Append prmAppName
set prmAppPW = new ADODB.Parameter
prmAppPW.Name = "password"
prmAppPW.Direction = adParamInput
prmAppPW.Value = "password"
prmAppPW.Type = adLongVarWChar
prmAppPW.Size = 128
cmdAppRole.Parameters.Append prmAppPW
cmdAppRole.CommandTimeout = 0
cmdAppRole.Execute , , adCmdStoredProc + adExecuteNoRecords



The T-SQL debugger ended up doing some funny stuff, so I'm not so sure that it actually works, no errors were returned though.

[Karina Gámez]

hi..

i have problems with the sp_setapprole... i did what u told me... but .. doesnt work...

do u have any idea about this?

[phunkydude]

Hi Karina

where did the sp fail, did it not do what it claims to do or did it fail?

Have you created the Application Role on the specific DB in SQL Server?

[Karina Gámez]

ok..
this is the solution..


private Sub Form_Load()
Dim rs as ADODB.Recordset
set rs = new ADODB.Recordset
strSQL = "spr_MostrarTablas"
rs.Open strSQL, cn, adOpenStatic, adLockOptimistic

Do While Not rs.EOF 'Llenamos el recordset
Cbotabla.AddItem rs!table_name
rs.MoveNext
Loop
Cbotabla.ListIndex = 0
rs.Close 'Cerramos el recordset
set rs = nothing
end sub