Showing posts with label solution. Show all posts
Showing posts with label solution. Show all posts

Sunday, March 25, 2012

Config error

I get this error. Does any body came across this and found some solution?

Compiler Error Message: CS0433: The type 'Microsoft.Web.UI.ScriptManager' exists in both 'c:\WINDOWS.000\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\websites_hrdbms\3acda2bb\a63d28c5\assembly\dl3\32deaeca\005606e1_3759c601\Microsoft.Web.Atlas.DLL' and 'c:\WINDOWS.000\assembly\GAC_MSIL\Microsoft.Web.Extensions\1.0.61025.0__31bf3856ad364e35\Microsoft.Web.Extensions.dll'

I have created the application with ATLAS July CTP . currently upgraded to ASP.NET AJAX BETA 1. I face the above problem

Ramanan wrote:

I get this error. Does any body came across this and found some solution?

Compiler Error Message: CS0433: The type 'Microsoft.Web.UI.ScriptManager' exists in both 'c:\WINDOWS.000\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\websites_hrdbms\3acda2bb\a63d28c5\assembly\dl3\32deaeca\005606e1_3759c601\Microsoft.Web.Atlas.DLL' and 'c:\WINDOWS.000\assembly\GAC_MSIL\Microsoft.Web.Extensions\1.0.61025.0__31bf3856ad364e35\Microsoft.Web.Extensions.dll'

I have created the application with ATLAS July CTP . currently upgraded to ASP.NET AJAX BETA 1. I face the above problem

Wrong forum.

Monday, March 19, 2012

Conditional Trigger

I'm hoping someone here can help me with this situation...I've looked everywhere and cannot find a solution.

I have two separate databases. Table structures are different. A record is inserted into Table 1 of Database A. Then, when Field 1 in Table 1 is updated, I want my trigger to fire and insert certain values (selected into "inserted") to be inserted into Database B, Table 1.

I have created a trigger that will do just that. Here's my problem:

I need to ensure the trigger only fires if two conditions are met. If the value in Field 1 is updated to one specific value, and if the type of transaction (housed in a separate field) is one of several specific types (linked to the query from a separate table).

Otherwise, I want the trigger to be ignored completely.

Below is my current code. Can anyone here assist?
Thanks in advance for any help,
Dirk

CREATE TRIGGER [ELOGXWALK] ON [dbo].[Appointments]
AFTER UPDATE
AS

SET XACT_ABORT ON

IF UPDATE(Status)
BEGIN

declare @.recnum int
declare @.dateofenc datetime
declare @.timein varchar (5)
declare @.Code varchar (6)
declare @.Descr varchar (25)
declare @.clinic varchar (6)
declare @.status varchar (5)
declare @.Appointment_ID int
declare @.Encounter_Number int
declare @.nextnum int

select @.recnum = fldrec_num, @.dateofEnc = left(appointment_datetime, 11), @.timein=right('0'+convert(varchar,time_in), 4),
@.Code = Code, @.Descr = Descr, @.clinic = 'ONSITE', @.Status = status, @.Appointment_ID = Appointment_ID,
@.Encounter_Number = Encounter_Number from inserted
left join Crosswalk.dbo.EncounterLink on TypeID = Appointment_Type_ID
left join patients on patients.patient_id = inserted.patient_id
left join contacts on contacts.contact_id = patients.contact_id
left join DBOCC.MCH_OHM.XWALK.EMPLOYEE on FLDSSN = contacts.ssn
where Appointment_Type_ID in (select TypeID from Crosswalk.dbo.EncounterLink)
and status = 'A'

set @.nextnum = (select fldnextnum from DBOCC.MCH_OHM.XWALK.tblrecnumseq where fldtablename = 'ELOG' )
update DBOCC.MCH_OHM.XWALK.tblrecnumseq
set fldnextnum = (select fldnextnum from DBOCC.MCH_OHM.XWALK.tblrecnumseq where fldtablename = 'ELOG') + 1
where fldtablename = 'ELOG'

insert into DBOCC.mch_ohm.XWALK.ELOG (FLDREC_NUM, FLDEMPLOYEE, FLDDATE, FLDTIME, FLDTYPE, FLDDESCR, FLDCLINIC, FLDRECNUM, FLDENCNUMBR)
values (@.nextnum, @.recnum, @.dateofenc, @.timein, @.code, @.Descr, @.clinic, @.appointment_id, @.Encounter_Number)

END

IF UPDATE (Encounter_Number)
BEGIN

select @.Appointment_ID = Appointment_ID, @.Encounter_Number = Encounter_Number from inserted
left join DBOCC.MCH_OHM.XWALK.ELOG on FLDRECNUM = inserted.Appointment_ID

update DBOCC.MCH_OHM.XWALK.ELOG
set FLDENCNUMBR = @.Encounter_Number
where FLDRECNUM = @.Appointment_ID

ENDcan you provide more specifics on the two conditions?|||First condition. The TypeID referenced in the script must be any one of about 10 different values. These are stored in a separate table (EncounterLink) along with the corresponding value I want to load into Database B. If the TypeID does not equal one of the values in the EncounterLink table, I want the trigger to disregard everything.

Second condition. The Status field should only fire the trigger if a certain value is entered. In this case, only the value "A" should fire the trigger. In the future I will probably want one or two different trigger functions based on different values.

Dirk|||great! your current design will work if one row is updated but will fail (in some fashion) if 2+ rows are updated. Do you want to handle multiple row updates? also, do you want the Encounter_number updated only if the TypeID and Status are okay or are these seperate events?|||I should not have to deal with multiple row updates, because the front end application will only do a single row at a time anyway (clicking a button in the app triggers the change).

I've actually made progress by specifically referencing "inserted" in the where statement of my select and adding an "If @.@.rowcount > 0" and "Begin" command just prior to the set statement. It seems to be working properly all around, but any streamlining suggestions will be more than welcome, as I am not really a DBA - my company justs asks that I play one during working hours!

Dirk|||it sounds like you are on your way...

I would have taken a diffrent approach so the trigger could handle multiple row updates but the bottom line is that you understand the trigger and it works. I would suggest two things though,

First
----------------------------
set @.nextnum = (select fldnextnum from DBOCC.MCH_OHM.XWALK.tblrecnumseq where fldtablename = 'ELOG' )
update DBOCC.MCH_OHM.XWALK.tblrecnumseq set fldnextnum = @.nextnum + 1 where fldtablename = 'ELOG'

or even

begin transaction
update DBOCC.MCH_OHM.XWALK.tblrecnumseq set fldnextnum = fldnextnum + 1 where fldtablename = 'ELOG'
select @.nextnum = (fldnextnum - 1) from DBOCC.MCH_OHM.XWALK.tblrecnumseq where fldtablename = 'ELOG'
commit transaction

And
----------------------------
IF UPDATE (Encounter_Number) BEGIN
update DBOCC.MCH_OHM.XWALK.ELOG
set FLDENCNUMBR = inserted.Encounter_Number
from inserted
join DBOCC.MCH_OHM.XWALK.ELOG on inserted.Appointment_ID = DBOCC.MCH_OHM.XWALK.ELOG.FLDRECNUM
end
----------------------------

these are minor things but (IMHO) you should save every step you can when writting triggers.|||Thanks for the tips! Both make sense to me, but the second one does not work...

Server: Msg 117, Level 15, State 2, Procedure ELOGXWALK, Line 49
The number name 'DBOCC.MCH_OHM.XWALK.ELOG' contains more than the maximum number of prefixes. The maximum is 3.

Dirk|||ARG!!! I do that sometimes.
I don't use liked servers that much but I think is should be something like

IF UPDATE (Encounter_Number) BEGIN
update DBOCC.MCH_OHM.XWALK.ELOG
set FLDENCNUMBR = inserted.Encounter_Number
from inserted
join OPENQUERY(DBOCC, select FLDRECNUM from MCH_OHM.XWALK.ELOG) elog on inserted.Appointment_ID = elog.FLDRECNUM
end

What you have is just fine though.

Thursday, March 8, 2012

Conditional MDX ?

I've got a solution where I need to apply some adjustment to a measure for a given scope:

Scope ([Organization].[Organization].&[13], [Product].[Division Temperature].&[Frozen], [Measures].[FOB]); This = [Measures].[FOB] - 1.25; End Scope;

This works fine, but given that this solution uses role based security to limit access to specific dimension data, there are some roles that don't have access to the [Organization].[Organization].&[13] and the MDX fails parsing.

Is there any way to make a conditional MDX that I can use to only apply the given MDX SCOPE when the role has access to the dimensions used in the statement ?

Trond

I guess it is safe to ignore the entire SCOPE for the users who have no access to Organization 13. One quick way to do it, is to change the ScriptErrorHandlingMode to IgnoreAll (although this will start ignoring all other errors). More details here: http://www.sqljunkies.com/WebLog/mosha/archive/2005/05/02/13315.aspx

HTH,

Mosha (http://www.mosha.com/msolap)

|||

Note that ScriptErrorHandling has some limitations (in RTM and SP1) whereby certain kinds of script errors are not ignored. This will be fixed in SP2.

|||

Thank you for the input - ScriptErrorHandling did indeed work but I ended up with a different solution in the end.

I guess I was so into using SCOPE that I didn't see any other tool/way to do it. Once I took a new look at it, I ended up with not using SCOPE at all, but rather a CASE:

Case When [Organization].[Organization].CurrentMember.Name = "Norway" And [Product].[Division Temperature].CurrentMember.Name = "Frozen" Then ([Measures].[Net Sales] / [Measures].[Volume]) - 1.25 Else ([Measures].[Net Sales] / [Measures].[Volume]) End

Memo to self: Read up on MDX before the next SQL 2005 project !

|||

Another approach that appears to work (at least in Adventure Works) is to first test the member using IsError(), like:

Create Set CurrentCube.[OrgSet] As iif(IsError(StrToMember("[Organization].[Organization].&[13]")),
{}, {[Organization].[Organization].&[13]}); IF [OrgSet].Count > 0
THEN ([Measures].[FOB], [Product].[Division Temperature].&[Frozen],
[OrgSet]) = [Measures].[FOB] - 1.25 END IF; |||

Please note, however, that this solution is far less efficient then the one using SCOPE. Likewise, Deepak's suggestion of using IF/END IF is also less efficient.

Probably you would get the best performance by combining Deepak's idea of building named set with SCOPEs, i.e.

CREATE SET orgs = ...;

SCOPE (orgs);

...;

END SCOPE;

|||But I couldn't figure out how to build the set, such that the assignment wouldn't update any cells when the targeted member is not accessible to the role/user. The empty set that I substituted for the secured dimension seemed not to narrow the scope, so all accessible members got updated...|||Deepak, the empty set in the SCOPE translates to the empty subcube, and therefore no cells are affected by the assignmets inside this SCOPE. Therefore, since for the user with dimension security the named set will end up empty - it will behave as that portion of MDX Script didn't exist.|||

Mosha, thanks for clarifying that - looks like my version didn't work because I also included the measure in the scope, like:

>>

Scope({[Measures].[FOB]}, [OrgSet]);

This = [Measures].[FOB] - 1.25; End Scope; >> Instead, if only the Org set is specified in the scope, then the empty set defines an empty subcube. So '({[Measures].[FOB]}, {})' isn't empty, but '({})' is:
>> Scope([OrgSet]); ([Measures].[FOB]) = [Measures].[FOB] - 1.25; End Scope; /* OR */

Scope([Measures].[Order Quantity]);

Scope([OrgSet]);

this = [Measures].[Order Quantity] * 2;

End Scope;

End Scope;

>>

Conditional Line Breaks

There has to be a simple solution to this, but I can't find one. I have several fields dropped onto a report which display the relevant company address for that customer. But, some of the addresses don't have the full itinary, some may not have state or country where other may have. How can I implement a conditional statement which will remove remove the line break if the content is present. Or vice versa if no content is there, how can I not display that field?

Thanks in advanceIn the suppress event write this

If IsNull({filed}) then True;