SQLEXEC statement fails

[carolx]

What is wrong with this statement:

lnResult = SQLEXEC(oGv.pnConnhandle, 'EXEC ClientSecurity.UsersCloneSP','userz')

The stored procedure is qualified by a database schema name. Do I have to further qualify the stored procedure?

EXEC ClientSecurity.UsersCloneSP executes successfully in management studio query window.

 

[Olaf Doschke]

Find out yourself:

If lnResult<0
   AERROR(laError)
   ? laError(2)
   Set step on
Endif

Possible reasons I can think of only seeing your code: There are non optional parameters of this stored proc you need to pass in. You don't have access privileges as the user you connected to the server.

The error message will tell you more.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[carolx]

I added the database name to the stored procedure and it is now working.

lnResult = SQLEXEC(oGv.pnConnhandle, 'EXEC Hrmsdata.ClientSecurity.UsersCloneSP','userz')

 

[Olaf Doschke]

If you add [tt]Database=Hrmsdata[/tt] to your connection string and you don't need to specify the database name in queries or EXEC calls. There also is a default schema for a user, which normally is dbo, that way the dbo prefix is usually optional and you can just use the core object name (table procedure name, view name, etc).

There's a little bit to learn about T-SQL names. But with the correct connection string, this also will work. And by the way, connecting to some specific database still makes it possible to change that. T-SQL [tt]USE database[/tt] can be used. Unlike VFPs USE this switches the database, so it compares to VFPs SET DATABASE.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name