Executing Procedure (OLEDB Session Object)

I'm executing a very simple Sotred Procedure from VB6 and I'm getting this error:

"Requested operation requires an OLE DB Session object, which is not supported by the current provider."

I executed it from Query Analyzer and Worked perfectly!

I hope you can help me with this now! :(How are you trying to execute the sp in vb6 - please post your code - including connection string ... ?sql

Execute Stored Procedure via Execute SQL Task

Do you know of a bug in the June CTP of SSIS where you cannot, using an Execute SQL Task, execute a stored procedure with parameters via an OLE DB connection? For example, one combination I tried was in the SQL Statement, I have:

EXEC dbo.DimBuild ?,?

And in the parameter mapping, I added two date user variables, both with parameter name ‘?’

--

I also tried

EXEC dbo.DimBuild sd, ed

And in the parameter mapping, I added two date user variables, one with a parameter name ‘sd’ and the other ‘ed’

--

I tried many other combinations as well. The error I would get would say “parameter name unrecognized”. [Execute SQL Task] Error: Executing the query "dbo.DimBuild sd, ed" failed with the following error: "Parameter name is unrecognized.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Is there something wrong with my syntax? Interestingly, I tried executing the stored procedure using an ADO.Net connection, with similar parameter mappings, and it worked just fine.

Thanks,

- Joel

I was banging my head against the wall trying to figure this out also. I finally created a simple DTS package with an Execute SQL task running a parameterized query. When I migrated it to SSIS, I found the parameter name was 0.

I modified the package I was working on to use parameter names 0 and 1 (with "exec sproc ?, ?" as the query) and it succeeded.

Don't you just love the great documentation?|||I know the documentation lacks on this. In this post http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=57637 Kirk has given us indication which Parameters can be used with which connection manager etc.

Execute Stored Proc from OLE DB Destination

is is posible to execute a stored proc (with parameters) from an OLE DB Destination ?

reason we are trying this is cos

our current setup is an OLE DB Command doing the first database update and then passing over to an OLE DB Destination that does the second update. There is error handling coming off the OLE DB Command to a Script component that passes to an OLE DB Destination.

we are having a problem getting the error reporting working from the OLE DB Command - the updates work fine - but not getting any updates of the error database when there is an error.

have got the error reporting fine on the OLE DB Destination.

if we can execute the stored proc from the OLE DB Destination, we will then do both updates via one stored procedure executed by the OLE DB Destination.

thx

m

n.b. think the updating of the error logs from the OLE DB Command used to work - but cant get it to work now ?!!!!?

No, you can't. Why not use another OLE DB command?|||

cos we cant get the error logging to work from the OLE DB Command

and we want to know if any records dont make it

m

|||Well, why can't you get error logging to work? Any, ahem, error messages you can provide us with?|||

phil

thx for this

we couldn't get it to work and could not understand why (followed and re-followed all of the instructions)

today

new member on project, new PC, new SQL installation (SP1), works fine ?

so

am uninstalling SQL from my machine and re-installing it (back to what it was - SP1) - and will see

m

Execute SQL Task: UDF not taking parameters

Hi,

I have an Execute SQL Task in my SSIS Package.
Now, this Execute SQL Task has the following query (Connection Type is OLE DB):

Code Snippet

SELECT dbo.udf_CommonDateTime_Get (GetDate(), ?) As User_Datetime

I want 2 things from this Task:
1) It should take the 2nd argument to the UDF from a variable.
2) It should store the value returned by this SELECT statement into another variable.

So, I go ahead and modify the Parameter Mapping for the Task. Here I add the Input variable name, Data type and I give the Parameter Name as 0.

I also modify the Result Set for the Task. Here, I specify the Result Name as User_Datetime and give the appropriate Variable Name.

I am getting an error here and I believe it is due to the input parameter. The UDF is not getting the 2nd argument correctly.

My questions:
1) Has the Execute SQL Task been designed to handle UDFs like this. If not, then where am I going wrong?
2) What is the work-around for this? I need to pass a parameter (variable) to the UDF.

Thanks in advance.

Regards,
B@.ns

The error message is:

Code Snippet

Execute SQL Task: Executing the query "SELECT dbo.udf_Common_DateTime_Get (GetDate(), ?)

As User_Datetime" failed with the following error: "Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Task failed: Set UserDateTime

As a workaround, you could you create a new variable to store your sql statement. Set the 'EvaluateAsExpression' property of the variable to 'true'. Then set the expression like this...

"SELECT dbo.udf_CommonDateTime_Get (GetDate()," + user::VariableNameHere + ") As User_Datetime"

Then in 'SourceVariable' property of the execute sql statement to 'Variable' and then choose the variable name.

|||

Hi Martin,

Thank you for the reply. At least it gives me some hope

Unfortunately, I am getting this error:

Code Snippet

The expression for variable "varQuery" failed evaluation. There was an error in the expression.

If I remove the user::VariableNameHere part, it works fine...

Any ideas?

Thanks again.

Regards,

B@.ns

|||

You replaced user::VariableNameHere with the actual name of your variable correct?

|||

Martin,

It worked!!

Thank you so much!!

I had to do this:

Code Snippet

"SELECT dbo.udf_CommonDateTime_Get(GETDATE(), " + (DT_WSTR, 1) @.[User::VariableName] + ") As UserDateTime"

The only thing worries me is that @.[User::VariableName] can be NULL.

I will have to handle that.

Thanks again.

Regard,

B@.ns

EXECUTE permission denied

I'm running an ASP based report. It's always worked, but for some
reason now I get the following:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E09)
[Microsoft][ODBC SQL Server Driver][SQL Server]EXECUTE permission
denied on object 'IFMSpVisitbyStatus', database 'Paradigm', owner
'dbo'.
Any ideas?
nick,
The error is pretty specific. Apparently the login used by your ASP report
still has access to the database, but not to that stored procedure. It
could be that someone changed the rights being granted the report user. You
can check what rights it still has by:
EXEC sp_helprotect @.username = 'YourReportUserAccount'
However, a more likely suspect is that a new version of the stored procedure
was created , but the rights were not regranted. This is a problem when a
stored procedure is dropped and recreated. You can check by:
select name, crdate
from sysobjects
where name = 'IFMSpVisitbyStatus'
If that is the case, some one will need to:
GRANT EXECUTE ON IFMSpVisitbyStatus TO YourReportUserAccount
Also, if that is the problem then the process for deploying updated SQL
Server objects apparently needs to be tightened up a bit to ensure that
rights are preserved or regranted.
RLF
"nick" <cipher7836@.gmail.com> wrote in message
news:73eed503-cf29-422f-8d03-f168b1c723ca@.f63g2000hsf.googlegroups.com...
> I'm running an ASP based report. It's always worked, but for some
> reason now I get the following:
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E09)
> [Microsoft][ODBC SQL Server Driver][SQL Server]EXECUTE permission
> denied on object 'IFMSpVisitbyStatus', database 'Paradigm', owner
> 'dbo'.
> Any ideas?
|||On Mar 14, 10:35Xam, "Russell Fields" <russellfie...@.nomail.com>
wrote:
> nick,
> The error is pretty specific. XApparently the login used by your ASP report
> still has access to the database, but not to that stored procedure. XIt
> could be that someone changed the rights being granted the report user. You
> can check what rights it still has by:
> EXEC sp_helprotect @.username = 'YourReportUserAccount'
> However, a more likely suspect is that a new version of the stored procedure
> was created , but the rights were not regranted. XThis is a problem whena
> stored procedure is dropped and recreated. XYou can check by:
> select name, crdate
> from sysobjects
> where name = 'IFMSpVisitbyStatus'
> If that is the case, some one will need to:
> GRANT EXECUTE ON IFMSpVisitbyStatus TO YourReportUserAccount
> Also, if that is the problem then the process for deploying updated SQL
> Server objects apparently needs to be tightened up a bit to ensure that
> rights are preserved or regranted.
> RLF
> "nick" <cipher7...@.gmail.com> wrote in message
> news:73eed503-cf29-422f-8d03-f168b1c723ca@.f63g2000hsf.googlegroups.com...
>
>
> - Show quoted text -
Thanks for the information! Sad to say, but I know next to nothing
about SQL. I just wanted to help the user run an already created
report.

EXECUTE permission denied

I'm running an ASP based report. It's always worked, but for some
reason now I get the following:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E09)
[Microsoft][ODBC SQL Server Driver][SQL Server]EXECUTE permission
denied on object 'IFMSpVisitbyStatus', database 'Paradigm', owner
'dbo'.
Any ideas'nick,
The error is pretty specific. Apparently the login used by your ASP report
still has access to the database, but not to that stored procedure. It
could be that someone changed the rights being granted the report user. You
can check what rights it still has by:
EXEC sp_helprotect @.username = 'YourReportUserAccount'
However, a more likely suspect is that a new version of the stored procedure
was created , but the rights were not regranted. This is a problem when a
stored procedure is dropped and recreated. You can check by:
select name, crdate
from sysobjects
where name = 'IFMSpVisitbyStatus'
If that is the case, some one will need to:
GRANT EXECUTE ON IFMSpVisitbyStatus TO YourReportUserAccount
Also, if that is the problem then the process for deploying updated SQL
Server objects apparently needs to be tightened up a bit to ensure that
rights are preserved or regranted.
RLF
"nick" <cipher7836@.gmail.com> wrote in message
news:73eed503-cf29-422f-8d03-f168b1c723ca@.f63g2000hsf.googlegroups.com...
> I'm running an ASP based report. It's always worked, but for some
> reason now I get the following:
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E09)
> [Microsoft][ODBC SQL Server Driver][SQL Server]EXECUTE permission
> denied on object 'IFMSpVisitbyStatus', database 'Paradigm', owner
> 'dbo'.
> Any ideas'|||On Mar 14, 10:35=A0am, "Russell Fields" <russellfie...@.nomail.com>
wrote:
> nick,
> The error is pretty specific. =A0Apparently the login used by your ASP rep=ort
> still has access to the database, but not to that stored procedure. =A0It
> could be that someone changed the rights being granted the report user. Yo=u
> can check what rights it still has by:
> EXEC sp_helprotect @.username =3D 'YourReportUserAccount'
> However, a more likely suspect is that a new version of the stored procedu=re
> was created , but the rights were not regranted. =A0This is a problem when= a
> stored procedure is dropped and recreated. =A0You can check by:
> select name, crdate
> from sysobjects
> where name =3D 'IFMSpVisitbyStatus'
> If that is the case, some one will need to:
> GRANT EXECUTE ON IFMSpVisitbyStatus TO YourReportUserAccount
> Also, if that is the problem then the process for deploying updated SQL
> Server objects apparently needs to be tightened up a bit to ensure that
> rights are preserved or regranted.
> RLF
> "nick" <cipher7...@.gmail.com> wrote in message
> news:73eed503-cf29-422f-8d03-f168b1c723ca@.f63g2000hsf.googlegroups.com...
>
> > I'm running an ASP based report. It's always worked, but for some
> > reason now I get the following:
> > Microsoft OLE DB Provider for ODBC Drivers (0x80040E09)
> > [Microsoft][ODBC SQL Server Driver][SQL Server]EXECUTE permission
> > denied on object 'IFMSpVisitbyStatus', database 'Paradigm', owner
> > 'dbo'.
> > Any ideas?... Hide quoted text -
> - Show quoted text -
Thanks for the information! Sad to say, but I know next to nothing
about SQL. I just wanted to help the user run an already created
report.