error in parameters when using stored procedure...

hi, all
I met a problem, I use OleDB to connect database, and using stored procedure with 3 parameters to getting data, including 2 datetime parameters named DateFrom and DateTo.

Everything is fine in my desktop, but in my customer's side, there is an error-"DateFrom is not a parameter for procedure XXXX", I don't know why is this happened, is it related to the date format thing? I'm not sure what the enviroment is in my customer's side, does anyone ever met this error?

Many thanks!!!

Might be a format issue. They might be entering the date as 11-14-06. It might be interpreting it as a string datatype.

Adamus

|||

Adamus Turner wrote:

Might be a format issue. They might be entering the date as 11-14-06. It might be interpreting it as a string datatype.

Adamus

but, i notice that in the report screenshot which my customer sent me, the DateFrom and DateTo parameter had been converted to the standard format "9/1/2006 12:00:00 AM" and "11/1/2006 12:00:00 AM", and because I set these two parameters to DateTime in report, if the user input the invalid string, it shouldn't be passed anyway.

thanks.

|||

Then the problem is in the SQL sp_

I'm curious, did you test the sp_ by using EXEC in query analyzer/management studio or through a front end form?

Please post the sp_ instantiation along with the parameter declarations.

Adamus

|||

Adamus Turner wrote:

Then the problem is in the SQL sp_

I'm curious, did you test the sp_ by using EXEC in query analyzer/management studio or through a front end form?

Please post the sp_ instantiation along with the parameter declarations.

Adamus

Yes, I've tried and it works. the stored procedure is,,,

CREATE PROCEDURE [dbo].[usp_XXXX]
(
@.DateFrom datetime,
@.DateTo datetime,
@.ParentItem varchar(50)
)
AS
......

thank you.

|||

I see. Well I'm trying to remember where I've experience this issue. I believe it was in ASP.net where I used ADO.net to set the command text to execute the sp_ but set the command type incorrectly (i.e. cmdquery not cmdsp)

This is probably the case if you did not design the front end form. The client designed the form to execute a non-parameterized query such as a View. So when they try to pass parameters, it says, "Your command type doesn't support parameterized execution. Please use the correct command type." ...which in your case would be a stored procedure not plain T-SQL.

You might want to consult the ASP.net forum or ADO.net for a resolution to your problem.

Adamus

|||

Thank you, Adamus!

I got the reason, it is the connection type's problem, when using OLE DB, everything is ok...

Error in Locale when passing datetime parameter?

Hello!
I am using RS supplied with SQl Server 2005. I have one report and two
subreports inside it. One of the subreports is embedded and another one can
be navigated using hypertext link. All reports have one datetime parameter
which is entered by user in the main report and passed to subreports. But
when in the main report I enter 1-st of november 2006 in subreports it
becomes 10-th of january 2006. All reports have language = default. When
debugging report (F5) everything is ok. The problem comes out when publishing
report to report server.
Any suggestions/advises are welcome.What is the build number of the Report Server?
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Alexander Korol" <AlexanderKorol@.discussions.microsoft.com> wrote in
message news:63910111-34C6-4B1C-A34E-E52FCFA0CE00@.microsoft.com...
> Hello!
> I am using RS supplied with SQl Server 2005. I have one report and two
> subreports inside it. One of the subreports is embedded and another one
> can
> be navigated using hypertext link. All reports have one datetime parameter
> which is entered by user in the main report and passed to subreports. But
> when in the main report I enter 1-st of november 2006 in subreports it
> becomes 10-th of january 2006. All reports have language = default. When
> debugging report (F5) everything is ok. The problem comes out when
> publishing
> report to report server.
> Any suggestions/advises are welcome.|||Hello Lev
Can not say for sure - I looked bin folder - version of most libraries is
9.00.2047.00
others are have 9.00.1399.00.
"Lev Semenets [MSFT]" wrote:
> What is the build number of the Report Server?
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Alexander Korol" <AlexanderKorol@.discussions.microsoft.com> wrote in
> message news:63910111-34C6-4B1C-A34E-E52FCFA0CE00@.microsoft.com...
> > Hello!
> >
> > I am using RS supplied with SQl Server 2005. I have one report and two
> > subreports inside it. One of the subreports is embedded and another one
> > can
> > be navigated using hypertext link. All reports have one datetime parameter
> > which is entered by user in the main report and passed to subreports. But
> > when in the main report I enter 1-st of november 2006 in subreports it
> > becomes 10-th of january 2006. All reports have language = default. When
> > debugging report (F5) everything is ok. The problem comes out when
> > publishing
> > report to report server.
> >
> > Any suggestions/advises are welcome.
>
>|||Lets figure out details. Could you please reply via e-mail?
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Alexander Korol" <AlexanderKorol@.discussions.microsoft.com> wrote in
message news:13DAE456-72AB-4F13-999F-E23165227726@.microsoft.com...
> Hello Lev
> Can not say for sure - I looked bin folder - version of most libraries is
> 9.00.2047.00
> others are have 9.00.1399.00.
> "Lev Semenets [MSFT]" wrote:
>> What is the build number of the Report Server?
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "Alexander Korol" <AlexanderKorol@.discussions.microsoft.com> wrote in
>> message news:63910111-34C6-4B1C-A34E-E52FCFA0CE00@.microsoft.com...
>> > Hello!
>> >
>> > I am using RS supplied with SQl Server 2005. I have one report and two
>> > subreports inside it. One of the subreports is embedded and another one
>> > can
>> > be navigated using hypertext link. All reports have one datetime
>> > parameter
>> > which is entered by user in the main report and passed to subreports.
>> > But
>> > when in the main report I enter 1-st of november 2006 in subreports it
>> > becomes 10-th of january 2006. All reports have language = default.
>> > When
>> > debugging report (F5) everything is ok. The problem comes out when
>> > publishing
>> > report to report server.
>> >
>> > Any suggestions/advises are welcome.
>>

Error in datetime validation

xml including a datetime attribute is failing to validate against a simple schema held in an XML schema collection

The same xml validates correctly against the same schema in .Net

Is this a bug? (same behaviour is seen with dates)

Repro follows:

CREATE XML SCHEMA COLLECTION MyCollection AS '
<xs:schema xmlns="http://www.w3.org/2001/XMLSchema" targetNamespace="http://ns" xmlns:ns="http://ns" xmlns:xs="http://www.w3.org/2001/XMLSchema" version="1.0">
<xs:complexType name="TestType">
<xs:attribute name="id" type="xs:string" use="required"> </xs:attribute>
<xs:attribute name="startDate" type="xs:dateTime" ></xs:attribute>
</xs:complexType>
<xs:element name="TestList" type="ns:TestType">
</xs:element>
</xs:schema>';
go
declare @.xmlvar xml(MyCollection)
set @.xmlvar=
'<TestList xmlns="http://ns" id="D882BA19-81FA-4F99-845D-D8AE57BD0699" startDate="2006-01-02T00:00:00" ></TestList>'

Msg 6926, Level 16, State 1, Line 2
XML Validation: Invalid simple type value: '2006-01-02T00:00:00'. Location: /*:TestList[1]/@.*:startDate

I have just found that the SQL implementation of XML requires the time zone - e.g. 'Z' suffix - but that the .Net implementation does not need the zone.

I now no longer have a problem as I will use the Z suffix - but it would be useful to know whether system.xml or SQL2005 are currently exhibiting the intended behaviour (assuming a target of consistency between the two)

|||

Your observation is correct that the server's schema processor requires the timezone specification - it normalizes the value to UTC and does not preserve the timezone.

The server uses its own XML schema validator, which is different from those in System.Xml and MSXML. As such, any server-side error is generated bu the server's XML schema processor. To isolate whether the error is given by the client or the server, invoke the validation at the server.

Hope this helps.

Thank you,

Shankar

Program Manager

Microsoft SQL Server

|||

Thanks Shankar - As I said in my last post, this is not a problem for me now. However, this is the second time that I have encountered tighter validation in the SQL engine than in system.xml (Previous thread was "XML Schema extension behaving differently between SQL2005 and VB.Net 2.0 ")

This can cause problems when developers do early development and testing saving to XML files. It means that when database integration takes place a different set of problems can occur - which would be completely avoidable if the validation was consistent between system.xml and the SQL engine.

Is there a goal to achieve consistency soon?

If not then it would certainly be useful to have sight of the known differences - I understand from Denis Ruckebusch in the previous thread that a list is being compiled

Thanks

|||

We are looking at relaxing some of the implementation restrictions in the server side validation based on feedback like yours and by removing some of the obstacles that required us to introduce the restrictions in the first place.

Best regards

Michael

|||

Hi Michael

That's encouraging - but for current development it would be very useful to have visibility of the known list of differences

Thanks

Error in convert function

I use convert to work with Hijri functions
when i write
select convert (datetime,'29-10-1426',131)
the result is correct
2005-12-01 00:00:00.000

when I increase the date by one
select convert (datetime,'30-10-1426',131)
I get
Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Why and how i solve itAs far as I know, the month 10 in Hijri calendar only has 29 days, so the second statement is supposed to fail. The next day to convert (datetime,'30-10-1426',131) is

select convert (datetime,'1-11-1426',131)

Regards,
Jun|||

The 10 month in Hijri calender is 30 days so
In Hijri calender any month can be 29 or 30 days
thanks