calling sp with datetime parameter from ole db

Hi folks,
I have a huge problem calling a stored proc with a datetime parameter using
ole db (CDynamicParameterAccessor). I used the sample from MSDN (HOWTO: Exec
ute Stored Procedure Using CDynamicParmeterAccesor) as a starting point and
modified the sp "sp_getStudent" so that it gets another parameter @.d datetim
e. But now I'm not able to execute this proc any more: rs.open() returns 0x8
0040e21 (DB_E_ERRORSOCCURRED). What's wrong?!?
Kind regards
Thomas
P.S. Any other kind of third parameter (int, float, ...) works well but date
time (smalldatetime) doesn'tHi Thomas
Your errror seems to be:
DB_E_DATAFIELD_OVERFLOW 80040E21 OLE DB Specific:
The field is too small to accept the amount of data you attempted to add.
Try inserting or pasting less data.
Check the values of the parameters you are passing and (possibly) if you are
mixing the values/parameters up (it is better to add the parameters in the
order in which they are declared!)
John
"Thomas Hein" wrote:

> Hi folks,
> I have a huge problem calling a stored proc with a datetime parameter using ole db
(CDynamicParameterAccessor). I used the sample from MSDN (HOWTO: Execute Stored Pro
cedure Using CDynamicParmeterAccesor) as a starting point and modified the sp "sp_ge
tSt
udent" so that it gets another parameter @.d datetime. But now I'm not able to execute this
proc any more: rs.open() returns 0x80040e21 (DB_E_ERRORSOCCURRED). What's wrong?!?rkred">
> Kind regards
> Thomas
> P.S. Any other kind of third parameter (int, float, ...) works well but da
tetime (smalldatetime) doesn't
>|||The error number refers to an overflow error - since the target column
is smalldatetime, I'd check the date value being put in and make sure
it's a valid smalldatetime (which is quite different than datetime).
valid smalldatetime values are Jan 1, 1900 to June 6, 2079.
Thomas Hein wrote:
> Hi folks,
> I have a huge problem calling a stored proc with a datetime parameter
> using ole db (CDynamicParameterAccessor). I used the sample from MSDN
> (HOWTO: Execute Stored Procedure Using CDynamicParmeterAccesor) as a
> starting point and modified the sp "sp_getStudent" so that it gets
> another parameter @.d datetime. But now I'm not able to execute this proc
> any more: rs.open() returns 0x80040e21 (DB_E_ERRORSOCCURRED). What's
> wrong?!?
> Kind regards
> Thomas
> P.S. Any other kind of third parameter (int, float, ...) works well but
> datetime (smalldatetime) doesn't
>|||Hi John, hi Trey,
thank's for the info - the problem is that I do not make any parameter-bindi
ng on my own, I just call
rs.Create(session, "exec sp_getstudent ?, ? out, ? out");
rs.Prepare();
rs.BindParameters(&rs.m_hParameterAccessor, rs.m_spCommand, &pDummy);
the same way as I do without the datetime parameter, but the next call to
rs.Open(NULL, NULL, false);
returns the error stated below...
Kind regards
Thomas
"Thomas Hein" <thomas.hein@.%nospam%inform-ac.com> schrieb im Newsbeitrag new
s:uledgRAAGHA.2092@.TK2MSFTNGP10.phx.gbl...
Hi folks,
I have a huge problem calling a stored proc with a datetime parameter using
ole db (CDynamicParameterAccessor). I used the sample from MSDN (HOWTO: Exec
ute Stored Procedure Using CDynamicParmeterAccesor) as a starting point and
modified the sp "sp_getStudent" so that it gets another parameter @.d datetim
e. But now I'm not able to execute this proc any more: rs.open() returns 0x8
0040e21 (DB_E_ERRORSOCCURRED). What's wrong?!?
Kind regards
Thomas
P.S. Any other kind of third parameter (int, float, ...) works well but date
time (smalldatetime) doesn't|||Hi
I assume that you are setting the first parameter as in the example code?
Does SQL profiler show anything?
John
"Thomas Hein" wrote:

> Hi John, hi Trey,
> thank's for the info - the problem is that I do not make any parameter-bin
ding on my own, I just call
> rs.Create(session, "exec sp_getstudent ?, ? out, ? out");
> rs.Prepare();
> rs.BindParameters(&rs.m_hParameterAccessor, rs.m_spCommand, &pDummy);
> the same way as I do without the datetime parameter, but the next call to
> rs.Open(NULL, NULL, false);
> returns the error stated below...
> Kind regards
> Thomas
> "Thomas Hein" <thomas.hein@.%nospam%inform-ac.com> schrieb im Newsbeitrag
news:uledgRAAGHA.2092@.TK2MSFTNGP10.phx.gbl...
> Hi folks,
> I have a huge problem calling a stored proc with a datetime parameter using ole
db (CDynamicParameterAccessor). I used the sample from MSDN (HOWTO: Execute Stored P
rocedure Using CDynamicParmeterAccesor) as a starting point and modified the sp "sp_
get
Student" so that it gets another parameter @.d datetime. But now I'm not able to execute thi
s proc any more: rs.open() returns 0x80040e21 (DB_E_ERRORSOCCURRED). What's wrong?!?darkred">
> Kind regards
> Thomas
> P.S. Any other kind of third parameter (int, float, ...) works well but
datetime (smalldatetime) doesn't
>|||I concur with John - you'll need to run Profiler to see what's acutally
being passed to sql server.
Since you're getting the parameter values from the UI, I even more
confident that you're getting an invalid smalldatetime value.
Thomas Hein wrote:
> Hi John, hi Trey,
> thank's for the info - the problem is that I do not make any
> parameter-binding on my own, I just call
> rs.Create(session, "exec sp_getstudent ?, ? out, ? out");
> rs.Prepare();
> rs.BindParameters(&rs.m_hParameterAccessor, rs.m_spCommand, &pDummy);
> the same way as I do without the datetime parameter, but the next call to
> rs.Open(NULL, NULL, false);
> returns the error stated below...
> Kind regards
> Thomas
>
> "Thomas Hein" <thomas.hein@.%nospam%inform-ac.com
> <mailto:thomas.hein@.%nospam%inform-ac.com>> schrieb im Newsbeitrag
> news:uledgRAAGHA.2092@.TK2MSFTNGP10.phx.gbl...
> Hi folks,
> I have a huge problem calling a stored proc with a datetime
> parameter using ole db (CDynamicParameterAccessor). I used the
> sample from MSDN (HOWTO: Execute Stored Procedure Using
> CDynamicParmeterAccesor) as a starting point and modified the sp
> "sp_getStudent" so that it gets another parameter @.d datetime. But
> now I'm not able to execute this proc any more: rs.open() returns
> 0x80040e21 (DB_E_ERRORSOCCURRED). What's wrong?!?
> Kind regards
> Thomas
> P.S. Any other kind of third parameter (int, float, ...) works well
> but datetime (smalldatetime) doesn't
>

Calling a sp from a UDF

Dear folks,
Is it possible? We are getting a error message from QA.
Let me know any thought/idea/link about this.
Regards,
Enric"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:372F7226-3FF0-42D2-A446-9B87691685BD@.microsoft.com...
> Dear folks,
> Is it possible? We are getting a error message from QA.
> Let me know any thought/idea/link about this.
> Regards,
> Enric|||"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:372F7226-3FF0-42D2-A446-9B87691685BD@.microsoft.com...
> Dear folks,
> Is it possible? We are getting a error message from QA.
> Let me know any thought/idea/link about this.
> Regards,
> Enric
From the BOL
The types of statements that are valid in a function include:
a.. DECLARE statements can be used to define data variables and cursors
that are local to the function.
b.. Assignments of values to objects local to the function, such as using
SET to assign values to scalar and table local variables.
c.. Cursor operations that reference local cursors that are declared,
opened, closed, and deallocated in the function. FETCH statements that
return data to the client are not allowed. Only FETCH statements that assign
values to local variables using the INTO clause are allowed.
d.. Control-of-flow statements.
e.. SELECT statements containing select lists with expressions that assign
values to variables that are local to the function.
f.. UPDATE, INSERT, and DELETE statements modifying table variables that
are local to the function.
g.. EXECUTE statements calling an extended stored procedure.
So it looks like you can call extended sprocs, but not local sprocs.
Rick Sawtell
MCT, MCSD, MCDBA|||Only extended procedures can be executed from user-defined functions. Can't
you include the logic from your procedure in the UDF?
ML
http://milambda.blogspot.com/|||"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:372F7226-3FF0-42D2-A446-9B87691685BD@.microsoft.com...
> Dear folks,
> Is it possible? We are getting a error message from QA.
> Let me know any thought/idea/link about this.
> Regards,
> Enric
From BOL:
The following statements are allowed in the body of a multi-statement
function. Statements not in this list are not allowed in the body of a
function: ...
Assignment statements.
Control-of-Flow statements.
DECLARE statements defining data variables and cursors that are local to the
function.
SELECT statements containing select lists with expressions that assign
values to variables that are local to the function.
Cursor operations referencing local cursors that are declared, opened,
closed, and deallocated in the function. Only FETCH statements that assign
values to local variables using the INTO clause are allowed; FETCH
statements that return data to the client are not allowed.
UPDATE, INSERT, and DELETE statements modifying table variables that are
local to the function.
EXECUTE statements calling an extended stored procedure.
Also from BOL:
User-defined functions cannot be used to perform a set of actions that
modify the global database state.|||No this is not possible.
HTH, jens Suessmeyer.|||Have not tried it, but couldn't you call a clr sproc from a clr UDF?
William Stacey [MVP]
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:372F7226-3FF0-42D2-A446-9B87691685BD@.microsoft.com...
| Dear folks,
|
| Is it possible? We are getting a error message from QA.
| Let me know any thought/idea/link about this.
|
| Regards,
|
| Enric