Authentication problem

Hi,
First of all I would like to thank you in advance for all your time given to
my problem.
I'll try to explain our problem.
In our network we have several servers all with W2K SERVER.
SERVER 1 is DCP
SERVER 2 is DC
SERVER 3 is running SQL server standalone
SERVER 4 is running SQL server getting data from SERVER 2
SERVER 4 has several users defined as LOCAL and is supposed to get all AD
users but (and here is the problem) it doesn't.What I've found is that
SERVER 4 takes AD users from SERVER 3 and this one has some users missing
(all of them created after an specific date). The big problem is that the
users "most wanted" can't access OLAP cubes and I can't find a way to give
SERVER 4 those users.
Before that "day" SERVER 3 was a DC and now it isn't, so the AD information
is not replied to this server. Is there any way for SQL SERVER (or SERVER 4)
to get the whole AD information directly from SERVER 1 (or 2) without
promoting SERVER 4 to DC.
Thanks again
Alvaro Ruiz
URENDE, S.A.
alvaro.ruiz@.urende.es
Note: I'm not a expert in this so, I beg your pardon if I wrote something
that is not correct or if I have a wrong idea of how this works.Hi
Shutdown Server 3, Reboot Server 4 and see if you are getting the full list.
How was Server 3 turned into a stand alone server,after is was a DC? DCPROMO
or just a re-install without running DCPROMO first? If it was a re-install,
AD will still show it as a DC.
Regards
Mike
"Alvaro Ruiz" wrote:

> Hi,
> First of all I would like to thank you in advance for all your time given
to
> my problem.
> I'll try to explain our problem.
> In our network we have several servers all with W2K SERVER.
> SERVER 1 is DCP
> SERVER 2 is DC
> SERVER 3 is running SQL server standalone
> SERVER 4 is running SQL server getting data from SERVER 2
> SERVER 4 has several users defined as LOCAL and is supposed to get all AD
> users but (and here is the problem) it doesn't.What I've found is that
> SERVER 4 takes AD users from SERVER 3 and this one has some users missing
> (all of them created after an specific date). The big problem is that the
> users "most wanted" can't access OLAP cubes and I can't find a way to give
> SERVER 4 those users.
> Before that "day" SERVER 3 was a DC and now it isn't, so the AD informatio
n
> is not replied to this server. Is there any way for SQL SERVER (or SERVER
4)
> to get the whole AD information directly from SERVER 1 (or 2) without
> promoting SERVER 4 to DC.
> Thanks again
> Alvaro Ruiz
> URENDE, S.A.
> alvaro.ruiz@.urende.es
>
> Note: I'm not a expert in this so, I beg your pardon if I wrote something
> that is not correct or if I have a wrong idea of how this works.
>
>sql

Auditing Data Changes

I am using the SCD Wizard and it is working nicely. Can someone point be to an article/tuorial that would explain how you could create an "audit trail" on the items that may have been changed (type I and II)?

Basically, what I want to be able to do is run a query that tells me what data may have changed. I figured I would have to create an auditkey field in my table which would then link the key to the change detail?

The idea behind type 1 changes is that the changes AREN'T tracked. That's the point of type-1 SCDs. Type 2s ARE tracked by their very nature, so reporting on them should be easy. Type 2 records that have changed have "expired" records either indicated by some current flag or by some end date field.|||

Hi Jrp210,

I know of no articles or white papers on this, although some probably exist.

In my mind, this is ETL metadata. In larger ETL, I currently build a database that tracks row counts for the source data and then for different types of changes (including no change). It provides a the means for a quick "sanity-check" at the end of each load: I add up the rows coming out of the SCD Wizard outputs and see if this total matches the rows going in.

If I'm understanding you, you would like to take this to another level and store enough information to identify the rows that have changed, along with which type of change was made.

To tackle this scenario, I would probably use a small field appended to each destination dimension and fact table - tinyint would probably do the trick. If you make this field Null-able, I would start by updating this field - setting it to Null - before each load. For instance, let's say you have a table called dbo.DimStuff that looks like this:

StuffSK int identity(1,1),

StuffBK varchar(50),

StuffName varchar(50)

You could add another field:

StuffChangeType tinyint NULL

When your SSIS package starts, you could include an ExecuteSQL Task with the following statement:

UPDATE dbo.DimStuff

SET StuffChangeType = NULL

This would set all the StffChangeType values to Null prior to executing the load. Then you could add a Derived Column to each output of the Slowly Changing Dimension Wizard that adds a column named StuffChangeType and assigns an appropriate value (maybe 1 for updates, 2 for historical, 3 for new, etc.)

This would add change type data for each row changed - and let you know which rows were not changed.

You could then execute a query like:

SELECT StuffBK, StuffChangeType

FROM dbo.DimStuff

This would give you a picture of the business key and change types. You could qualify with a where clause:

WHERE StuffChangeType IsNotNULL

to only get changes (and change types) since the last load.

You could also do a nice summary with a query like:

SELECT StuffChangeType,Count(*)AS'ChangeTypeCount'

FROM dbo.DimStuff

WHERE StuffChangeType IsNotNULL

GROUPBY StuffChangeType

Hope This Helps,

Andy

|||But Andy, if you want to track Type 1 changes, make them Type 2. When using Type 2 SCDs, use begin and end dates to identify current and historical rows. The auditing is done for you by the nature of SCDs. That's the point of them in the first place. If you want to keep track of which LOAD processed the change, add another column, SessionKey, or something like that.|||

Hi Phil,

I think he's asking for something different from SessionID, and for something more than just Type 2 tracking. To me, it sounds as if he has a business requirement to update certain data - or perhaps lacks a business requirement to track changes on that data - and still wants to track the fact that it changed. I don't think SCDs cover that, and I think the solution I offered does. It's not the only way, but I think it will accomplish what he's after.

If not, perhaps it will help someone else in the future!

Thanks,

Andy

|||Along the path you're thinking Andy, the Kimball Webcast on the first page of this forum (towards the top) talks about auditing. In there, an audit entry is recorded and an identity value is returned that is used within the applicable pieces within SSIS. This value would correspond to the "session" of this load, for this package, etc... Talks about row counts, and other things. Very valuable.|||

Hi Phil,

Yep - that's good stuff.

I don't think that's what the original question was about, but it's definitely good stuff.

Thanks!

Andy

|||

Thanks for the responses. The SCD does do what it is intended to do but I probably should clarify a bit.

Perhaps an example will be the best way to explain.

For the sake of the example I have the following fields in my table:

CompanyKey

CompanyName

CompanyTicker

StartDate

EndDate

The SCD is setup to create historical changes for the name and ticker. This is done with StartDate and EndDate fields. Phil, you are right that I will have a date in the EndDate field if a change comes through for a CompanyName or CompanyTicker. But what I don't know unless I compare the two rows is what exactly changed. Now this is fairly easy to eyeball given this simple scenario. But in my real life scenario I have many fields that could have changed and its not easy to tell which one(s) did. I would like to create a way to determine which of the fields did change.

Hope this makes sense.

Attempted to divide by zero

Hello.
I'm having a little bit of a problem and i have no clue what's going on.
May be somebody can explain me how the following expression could generate the
"Attempted to divide by zero" error. I would really appreciate good advice.

Briefly about report itself - one dataset, on single table with three groups
this is a thid one. Grouping works just fine, but SUM/SUM in a footer of group #3
gives an error. Datatypes: JTD_Hours is decimal(18,2), JTD_Dollars is money.
None of the fields is NULL, all nulls converted to Zeros on dataset level.
Dataset created as result of stored procedure.

Here it is:
=IIf( Sum(Fields!JTD_Hours.Value,"table1_CostCode") = 0, 0, Sum(Fields!JTD_Dollars.Value,"table1_CostCode")/Sum(Fields!JTD_Hours.Value,"table1_CostCode"))

Thanks,
Konstantin

IIF is a function call which evaluates all arguments before it executes. Hence, given your expression a division by zero is possible.

Try the following expression instead:
=IIf( Sum(Fields!JTD_Hours.Value,"table1_CostCode") = 0, 0, Sum(Fields!JTD_Dollars.Value,"table1_CostCode") / iif(Sum(Fields!JTD_Hours.Value,"table1_CostCode") = 0, 1, Sum(Fields!JTD_Hours.Value,"table1_CostCode")))

In general, you want a pattern like this to avoid division by zero:

=iif(B=0, 0, A / iif(B=0, 1, B))

-- Robert

|||

Robert,
I appreciate your response.
Your tip really helped and now i see why it didn't work.
but i would have to admit that it's kind of wrong way how IIf works but it could be just me.
Anyhow, many thanks for you advice.

Konstantin

P.S.
It seems to me make more sence to create a custom function in a code section something like XdivY(x, y, whenYIsZero) so i can reuse this code over and over again.

|||IIF is a function call like any other function call in the VB runtime library. Function arguments always get evaluated before the function body is executed. That's how all programming environments work. It would be nice to support the IF - ELSE - ENDIF statements, but the VB runtime library has only support for function calls. Given that limitation, adding a custom function like you suggested is a good approach if you need this kind of division throughout the report.

-- Robert|||

The previous posts helped me a lot. I am new to coding and have the same problem but when I am trying to divide the totals on for a group. This is the code that is currently being used.

=Iif(ReportItems!Total_GALLONS1.Value > 0,ReportItems!Total_REVENUE1.Value/ReportItems!Total_GALLONS1.Value, 0)

Any help would be appreciated.

Thanks!

|||

You should do exectly the same as in second post here by Robert Bruckner MSFT

Your statement should be like

=Iif(ReportItems!Total_GALLONS1.Value > 0,ReportItems!Total_REVENUE1.Value/IIF(ReportItems!Total_GALLONS1.Value=0,1,ReportItems!Total_GALLONS1.Value), 0)

Attempted to divide by zero

Hello.
I'm having a little bit of a problem and i have no clue what's going on.
May be somebody can explain me how the following expression could generate the
"Attempted to divide by zero" error. I would really appreciate good advice.

Briefly about report itself - one dataset, on single table with three groups
this is a thid one. Grouping works just fine, but SUM/SUM in a footer of group #3
gives an error. Datatypes: JTD_Hours is decimal(18,2), JTD_Dollars is money.
None of the fields is NULL, all nulls converted to Zeros on dataset level.
Dataset created as result of stored procedure.

Here it is:
=IIf( Sum(Fields!JTD_Hours.Value,"table1_CostCode") = 0, 0, Sum(Fields!JTD_Dollars.Value,"table1_CostCode")/Sum(Fields!JTD_Hours.Value,"table1_CostCode"))

Thanks,
Konstantin

IIF is a function call which evaluates all arguments before it executes. Hence, given your expression a division by zero is possible.

Try the following expression instead:
=IIf( Sum(Fields!JTD_Hours.Value,"table1_CostCode") = 0, 0, Sum(Fields!JTD_Dollars.Value,"table1_CostCode") / iif(Sum(Fields!JTD_Hours.Value,"table1_CostCode") = 0, 1, Sum(Fields!JTD_Hours.Value,"table1_CostCode")))

In general, you want a pattern like this to avoid division by zero:

=iif(B=0, 0, A / iif(B=0, 1, B))

-- Robert

|||

Robert,
I appreciate your response.
Your tip really helped and now i see why it didn't work.
but i would have to admit that it's kind of wrong way how IIf works but it could be just me.
Anyhow, many thanks for you advice.

Konstantin

P.S.
It seems to me make more sence to create a custom function in a code section something like XdivY(x, y, whenYIsZero) so i can reuse this code over and over again.

|||IIF is a function call like any other function call in the VB runtime library. Function arguments always get evaluated before the function body is executed. That's how all programming environments work. It would be nice to support the IF - ELSE - ENDIF statements, but the VB runtime library has only support for function calls. Given that limitation, adding a custom function like you suggested is a good approach if you need this kind of division throughout the report.

-- Robert|||

The previous posts helped me a lot. I am new to coding and have the same problem but when I am trying to divide the totals on for a group. This is the code that is currently being used.

=Iif(ReportItems!Total_GALLONS1.Value > 0,ReportItems!Total_REVENUE1.Value/ReportItems!Total_GALLONS1.Value, 0)

Any help would be appreciated.

Thanks!

|||

You should do exectly the same as in second post here by Robert Bruckner MSFT

Your statement should be like

=Iif(ReportItems!Total_GALLONS1.Value > 0,ReportItems!Total_REVENUE1.Value/IIF(ReportItems!Total_GALLONS1.Value=0,1,ReportItems!Total_GALLONS1.Value), 0)

attaching a database to sql express

I'm trying to attach a database to sql express with the following code from the management tool. I keep getting an error message. An someone explain what I'm doing wrong.

Msg 102, Level 15, State 1, Line 9

Incorrect syntax near '<'.

IF NOT EXISTS(

SELECT *

FROM sys.databases

WHERE name = N'<northwind.mdb, I4V0Y6\SQLEXPRESS, northwind>'

)

CREATE DATABASE <database_name, sysname, your_database_name>

ON PRIMARY (FILENAME = '<c:\my documents\my webs\myweb3,,C:\Program files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\northwind.MDF>')

FOR ATTACH

GO

Ira

You have to substitute the value in the <> signs with your actual values. SO it should evaluate to something like:

CREATE DATABASE Archive
ON (FILENAME = 'C:\Program files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\northwind.MDF')
FOR ATTACH

The BOL has more samples about that.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de