Data conversion error

Hi all

Thanx for all your enthusiastic participation yesterday. I've got a new question on data conversion related to the same problem i asked about yesterday.

I have a SQL query as follows which generates the following error when it is executed against the database using sql query analyser and also through an ASP.NET application.

Error (Query Analyser):
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

So it's basically this line
(Convert(datetime, Request.closeDate, 103) > Convert (datetime, '8/25/2003', 103))
that is causing the problem

SELECT Request.requestID,
Users.clientCode,
Job.jobID,
Job.allocatedTime,
Job.spentTime,
Request.state,
Job.deadline

FROM Request,
Users,
Job,
Staff

WHERE (
Users.userID = Request.userID AND
Job.staff = Staff.staffID AND
Job.request = Request.requestID AND
(
(Job.staffProgress != 1 AND Request.state = 'In Progress') OR
(Convert(datetime, Request.closeDate, 103) > Convert (datetime, '8/25/2003', 103))
) AND
Request.state = 43
)
ORDER BY
Job.deadline,
Job.allocatedTime,
Request.priority

The weird thing is that this query works fine on our live server but fails when i try to execute it on the local machine. I think the live server is running SQL Server Service Pack 3, and the one on my local machine, i couldn't find out for some reason.

Any suggestions would be greatly appreciated

Cheers

Jamesthe same piece of sql query above also generated the following error.

Syntax error converting the varchar value 'In Progress' to a column of data type int.

The datatype of Request.state is varchar(20).|||I should mention all the associated data types in the WHERE block.

Job.staff (decimal)
Staff.staffID (decimal)
Job.request (decimal)
Request.requestID (decimal)

Job.staffProgress (float)
Request.state (varchar(20))
Request.closeDate (datetime)

Please help

Thanx

James|||Originally posted by nano_electronix
The datatype of Request.state is varchar(20).

Look again for your data type of Request.State! In your WHERE clause, you are comparing Request.State both with 'In Progress' and with 43. One of these are wrong, and your error message is clearly stating, that the data type of Request.State is INT!

Data "falling" out of filter failing to remove related records at subscriber

Summarised: Filtered record, changed at Publisher to take out of filter for Subscriber, at Subscriber record removed but related records dont go, bad.. Alternatively, Filtered record, changed at Subscriber to take out of filter, Replication removes all related records from Subscriber db when merge repl runs, good... :-|
Why the difference?
Have Row Filters & Join Filters.
Details: Ive set up Merge replication, with several articles with Row/Join Filters.
When I change a record on the Publication that exists at the Subscriber (that has been filtered/sync etc) that record gets communicated to the Subscriber, which is fine.
However, when I change a record on the server so that the record is no longer valid for that Subscriber because it falls outside the filter - the delete goes to the Subscriber, but its related records on the Subscriber do not get removed.
This is even more confusing, because if I change the record at the Subscriber, so that it falls outside the filter, it gets removed along with its related record.
Ive checked the logic & relations/joins which all appear to be valid. The fact that the Subscriber removes related records when it changes the record suggests that the Joins are valid. Doesnt it?
Is there an option/flag to force server changes to validate related records when sync happens?
Any help much appreciated.
Mr Le.Anybody, anybody? ;-)
Ive checked other posts in here and there appeared to be a couple of other people who had a similar issue, im following those threads to see if they offer a solution or hint.
Thanks for reading.|||Mr Le,
please can you post up a little more info. Are you adding rows on the
subscriber? Are these rows being propagated to the publisher but then not
removed on the subscriber as you'd expect due to the filter? If this is the
case I know of 2 posibilities:
(1) you've done a bulk insert without firing triggers on the subscriber.
(2) you have a filter like 1=2 and have added records while the merge agent
is running.
In each case you can:
run SP_ADDTABLETOCONTENTS then synchronize
run SP_MERGEDUMMYUPDATE for a single row then synchronize
HTH,
Paul Ibison
|||Paul,
Thanks for replying, its much appreciated, hopefully I can add a bit more info to clarify whats happening.
The Subscriber is a Pull-Subscription.
There are Row/Join filters defined for the Publication.
The initial Snapshot took a subset of records from the Publisher to the Subscriber, that were in-line with the filters defined for that Publication.
Test 1
1. The subscriber receives the Snapshot, and all expected records are added.
2. An update to a row at the Publisher is propogated to the Subscriber, where it is deleted because it no longer satisfies the Filters.
* Related records are not removed from the Subscriber though. This is the Problem.
* So we tried to see what would happen if we changed the record at the Subscriber instead of the Publication.
Test 2
1. We reinitialized the Subscriber, the initial Snapshot was then applied to the Subscriber. All valid records go across.
2. This time, we changed the row at the Subscriber, and this change meant that the record no longer satisfied the Filter. Merge Agent ran, and deleted the record on the Subscriber, except this time it also deleted all related records, which is what we expected to happen in the first place.
These "deletes" were not propogated back to the Publisher, which again is what we expected.
* No Bulk updates/inserts took place, other than those caused by the initial Snapshot.
* No records were added whilst the Merge Agent was running.
Hope ive described this well enough.
If you need more detail of the row filters were using and/or join filters, let me know.
Thanks again for your help.|||Mr Le,
thanks for the detailed description. Please can you post up a schema of the
tables involved and details of the joins/filters involved, and I'll
reproduce it here.
Cheers,
Paul

Cyrstal Report

Hi,

Nittu here...i am at enty level of crystal reports...i am into support team.

i am facing a problem in most of the reports related to grp by.

viz : i have a table called ward charges...

wardid wardname charges
1 ac room 1950
2 room #1 1400
3 room #2 1400

i need to calculate charges for patient admitted in room#1...
in report since they have groped by wardid...always
1950 is fetched for calculating the ward charges
i want 1400 instead

ward charges = charges * no. of days

same probm i facing in many other places due to grp by..

plz help me hw to sort it out.

from,
nittuIf you are grouping by wardid, then create a formula and place that formula in your group header.

Formula: ({Charges * no.ofdays}) \\ Place this in wardid group header
This will give the calculation for each wardid.

Hope that is what you are after.
GJ

CXPACKET error related to MOM process

While MOM processes are running at some point, a process goes into deadlock and uses up all existing CPUs.

Sysprocesses shows this it opened up 4 threads and program name is Microsoft? Reliability Analysis Service.

Profiler doesn't show which command it was trying to execute, but last notable command which has started was MRAS_pcLoad EXECUTE @.i_Return_Code = sp_getapplock @.Resource = N'MOM.Datawarehousing.DTSPackageGenerator.exe', @.LockMode = N'Exclusive', @.LockOwner = N'Session', @.LockTimeout =

Can you please help us, what could be the problem. It has been running fine till couple of days back.

--Prabhu

are you running sql2k or sql2k5? Try lowering the "degree of parallelism" (sp_configure 'max degree of parallelism', #) or using Maxdop query hint (maxdop=1) to relief the problem.

Consider updating to latest service pack if you haven't done so.

http://support.microsoft.com/kb/293232

|||

It's SQL 2K, we are running on latest service pack, version 8.00.2187.

I decreased the degree of parallelism to 3 processors, instead of 4 (MAX, in this case), now all these 3 are pegged. I couldn't use the qry hint, as I mentioned it is running DTS executable.

--Prabhu

|||

If max degree parallelism is set to either zero (0) or greater than one (1), you'd still encounter parallelism issue. Try setting it to one (1) to see if it helps.

|||

In that case, not more than one processor would be used for parallelism, meaning no parallelism for qry execution. Right?

We would like to make use of all existing processors for qry parallelism, too.

--Prabhu

|||

That's correct. If you set 'max degree parallelism' you set it for the entire server. Thus, every query will be affected by this.

The only other option is to use query hint maxdop which affects only that query, but you've already said you can't change that.

CustomReportItem Element in an RDL

I have 3 related questions, thanks ahead of time for any help...
1 - Does anyone know of any documentation and/or examples on how to use the
CustomReportItem Element in an RDL doc? MS has a PDF available about the
RDL specification, RDLDEC03.PDF, but it is not very clear on how to use it.
2 - Is there a way to write your own custom Report Item that could be added
to the toolbox?
3- Specifically, when a report is rendered as HTML, we want to add a
checkbox and a bit of JavaScript to allow users to select/deselect report
records. Is there another way besides 1 or 2?
Thanks again!#1, #2:
The currently available RDL spec describes the CustomReportItem element as
defined in RS 2000. The specification is getting changed/extended in RS
2005. We are planning on making a comprehensive sample for implementing a
design time control (report designer) and a runtime control (report server)
available when RS 2005 gets released.
#3:
This is only possible if you write your own custom rendering extension (on
top of RS 2000 or RS 2005). You cannot do your own Javascript just through a
CustomReportItem processing/runtime control.
Robert M. Bruckner
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Scott LaFave" <slafave@.trendmls.com> wrote in message
news:eDKv2%23$TFHA.2812@.tk2msftngp13.phx.gbl...
>I have 3 related questions, thanks ahead of time for any help...
> 1 - Does anyone know of any documentation and/or examples on how to use
> the CustomReportItem Element in an RDL doc? MS has a PDF available about
> the RDL specification, RDLDEC03.PDF, but it is not very clear on how to
> use it.
> 2 - Is there a way to write your own custom Report Item that could be
> added to the toolbox?
>
> 3- Specifically, when a report is rendered as HTML, we want to add a
> checkbox and a bit of JavaScript to allow users to select/deselect report
> records. Is there another way besides 1 or 2?
> Thanks again!
>|||hi,
About #2, is there a step by step RS 2000 example code ?
Thanks.
> #1, #2:
> The currently available RDL spec describes the CustomReportItem element as
> defined in RS 2000. The specification is getting changed/extended in RS
> 2005. We are planning on making a comprehensive sample for implementing a
> design time control (report designer) and a runtime control (report
server)
> available when RS 2005 gets released.
> #3:
> This is only possible if you write your own custom rendering extension (on
> top of RS 2000 or RS 2005). You cannot do your own Javascript just through
a
> CustomReportItem processing/runtime control.
>
> --
> Robert M. Bruckner
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "Scott LaFave" <slafave@.trendmls.com> wrote in message
> news:eDKv2%23$TFHA.2812@.tk2msftngp13.phx.gbl...
> >I have 3 related questions, thanks ahead of time for any help...
> >
> > 1 - Does anyone know of any documentation and/or examples on how to use
> > the CustomReportItem Element in an RDL doc? MS has a PDF available
about
> > the RDL specification, RDLDEC03.PDF, but it is not very clear on how to
> > use it.
> >
> > 2 - Is there a way to write your own custom Report Item that could be
> > added to the toolbox?
> >
> >
> > 3- Specifically, when a report is rendered as HTML, we want to add a
> > checkbox and a bit of JavaScript to allow users to select/deselect
report
> > records. Is there another way besides 1 or 2?
> >
> > Thanks again!
> >
> >
>