How to ignore timepart when using LookUp with date.

Hi

I want to lookup the datekeys from my datedimension. However it does not work because matching for same dates does not work because of different timeparts. The dates in my lookup-table have a 00:00:00 timepart, but the dates in my input table have a non-zero timepart. How can I ignore the timeparts or make the timeparts zero?

Regards,
HenkI found a workaround:

add a derived column component before and cast to DT_DBDATE first and than back to DT_DBTIMESTAMP to get rid of the timepart: (DT_DBTIMESTAMP)(DT_DBDATE)DateColumn.

I am still interested in a solution that doesn't require an extra component.

Henk|||Can you not use T-SQL to change the data at source?

-Jamie|||Yes that's true, but the sports of SSIS is in doing it without coding T-SQL

How to ignore sp_runwebtask errors?

Hi,
I have some tables in my database that, upon modification, write a file
to disk with data. I have create the triggers that handle this, using
the sp_makewebtask stored procedure.
For these triggers to work, I needed to login from my application with
integrated security. This is not a problem and works fine.
Sometimes I also need to do some maintenance on the tables, and for
this purpose I cannot use integrated security (because I connect
through vpn an am not on the actual machine). When i then change
something on the tables I get an error: "SQL Web Assistant: Could not
open the output file".
The fact that if I'm doing maintenance, the file is not created, is not
a problem, so I hope that one of the following solutions is possible:
- Build error handling into the trigger that simply ignores the error
- Detect in the trigger that the current user is not a windows user,
and then skip the creation of the file
- something else.
What could be a solution for my problem?
BTW We are using SQL 2000 on Windows 2003 Server.
Regards, FelixFelix,

> - Detect in the trigger that the current user is not a windows user,
> and then skip the creation of the file
You can use SUSER_SNAME() to determine this.
IF SUSER_SNAME() = 'MySQLServerLogin' ...
Robert
"felix planjer" <fplanjer@.gmail.com> wrote in message
news:1143541278.508476.286150@.e56g2000cwe.googlegroups.com...
> Hi,
> I have some tables in my database that, upon modification, write a file
> to disk with data. I have create the triggers that handle this, using
> the sp_makewebtask stored procedure.
> For these triggers to work, I needed to login from my application with
> integrated security. This is not a problem and works fine.
> Sometimes I also need to do some maintenance on the tables, and for
> this purpose I cannot use integrated security (because I connect
> through vpn an am not on the actual machine). When i then change
> something on the tables I get an error: "SQL Web Assistant: Could not
> open the output file".
> The fact that if I'm doing maintenance, the file is not created, is not
> a problem, so I hope that one of the following solutions is possible:
> - Build error handling into the trigger that simply ignores the error
> - Detect in the trigger that the current user is not a windows user,
> and then skip the creation of the file
> - something else.
> What could be a solution for my problem?
> BTW We are using SQL 2000 on Windows 2003 Server.
> Regards, Felix
>

How to ignore error and continue trasactional replication

Hi,
I have a trasactional replication setup in SQL 2000 with SP3. Accidentally,
I deleted a row in the suscriber table. When I deleted the same row in the
publisher database, I have error in the replication monitor "the row was not
found at the Subcriber when applying the replicated command".
Please let me know how do I get rid of this error and continue replication.
I don't want to reinitialize the subscriber again because the table is big
and takes lot of time.
I appreciate your help!!
Thanks
Chinna.
gonzo - cowboy- to hell with database consistency approach - right click on
your failed agent, select agent profiles, and then select the continue on
data consistency profile.
more cautious approach. enable logging for your distribution agent, and
restart it. Find out the row which you deleted. Get this row from teh
publisher and manually construst the insert statement and put it back in the
subscriber. Restart your distribution agent.
check out this kb article for more info on how to do the logging.
http://support.microsoft.com/default...&Product=sql2k
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Chinna Kondaveeti" <Chinna Kondaveeti@.discussions.microsoft.com> wrote in
message news:4A2DEA01-1131-41EF-B432-6C26E923EBFA@.microsoft.com...
> Hi,
> I have a trasactional replication setup in SQL 2000 with SP3.
Accidentally,
> I deleted a row in the suscriber table. When I deleted the same row in the
> publisher database, I have error in the replication monitor "the row was
not
> found at the Subcriber when applying the replicated command".
> Please let me know how do I get rid of this error and continue
replication.
> I don't want to reinitialize the subscriber again because the table is big
> and takes lot of time.
> I appreciate your help!!
> Thanks
> Chinna.
>
|||Thanks to everyone who helped me. It worked!!. I have changed back to default.
I appreciate your help!!
Thanks
Chinna.
"Hilary Cotter" wrote:

> gonzo - cowboy- to hell with database consistency approach - right click on
> your failed agent, select agent profiles, and then select the continue on
> data consistency profile.
> more cautious approach. enable logging for your distribution agent, and
> restart it. Find out the row which you deleted. Get this row from teh
> publisher and manually construst the insert statement and put it back in the
> subscriber. Restart your distribution agent.
> check out this kb article for more info on how to do the logging.
> http://support.microsoft.com/default...&Product=sql2k
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Chinna Kondaveeti" <Chinna Kondaveeti@.discussions.microsoft.com> wrote in
> message news:4A2DEA01-1131-41EF-B432-6C26E923EBFA@.microsoft.com...
> Accidentally,
> not
> replication.
>
>

How to ignore a dimension in MDX?

So my fact table looks like this:

SurveyID ( --> Survey Dimesion)
GroupID( --> Group Dimension)
StatusID ( --> Status Dimension ... e.g. Complete, InProgress, New)
GroupCount (the actual measure)

How would I create a calculated member that would return the "overall" count for a specific group in a survey (e.g. ignore Status dimension) regardless if the user is slicing the data by Status or not?

Thanks again - wgpubs

Try this ("ignoring" the status dimension - your exact naming may differ):

CREATE MEMBER CURRENTCUBE.MyCount AS

'([Status].[StatusID].[All StatusID],[Measures].[GroupCount])';

|||tried this ... but if I include the ResponseStatus dimension in my slicer it still filters on ResponseStatus.

In my slicer I got survey, group and response status ... the kind of report I'm trying to generate in SSRS needs to looks something like this:

Survey Group Count OverallCount

... where Count should use the ResponseStatus dimension (so that users can filter out certain statuses and what not thru a RS parameter) while OverallCount should not.|||My suggestion should work, if I understand you correctly. Please post your exact MDX query.|||Yah I thought it would too ... but no go. here is the query:

CREATE MEMBER CURRENTCUBE.[MEASURES].[Overall Group Count]
AS ([Response Status].[Response Status].[All], [Measures].[Group Count]),
VISIBLE = 1 ;