Database

Herman<T>.Instance9-Aug-11 2:29

Herman<T>.Instance

9-Aug-11 2:29

use google: http://sqlzoo.net/howto/source/z.dir/tip241027/i02create.xml[^]

In Word you can only store 2 bytes. That is why I use Writer.

cuong-nm9-Aug-11 16:30

cuong-nm

9-Aug-11 16:30

Sorry, do you understand my question?

You must run by step:

- Create table with partition by hash

- Create PK on that table

or

- Create table with PK and partition by hash

will have same error

SQL0270N Function not supported Reason code = "1")

MCP.NET, MCAD.NET, MCSD.NET

Wendelius12-Aug-11 7:01

Wendelius

12-Aug-11 7:01

As far as I know you cannot create a primary key without including all the columns in distribute list. So basically you either have to:
- include ID in the distribute list
- define the primary key using IDTEN and YEAR and ID

The need to optimize rises from a bad design.My articles[^]

Cube processing confirmation?

cuong-nm14-Aug-11 17:19

cuong-nm

14-Aug-11 17:19

ok, thanks

MCP.NET, MCAD.NET, MCSD.NET

current19996-Aug-11 1:01

current1999

6-Aug-11 1:01

Hi,
After processing your ETL package which loads data into the cube, how do you ensure that your cube has been processed?
Current

Re: Cube processing confirmation?

Mycroft Holmes6-Aug-11 2:52

Mycroft Holmes

6-Aug-11 2:52

Surely the last step in your ETL package is to call the processing method of the cube! (seems obvious but I don't deal with cubes so I may be completely wrong).

Never underestimate the power of human stupidity
RAH

Re: Cube processing confirmation?

S Douglas11-Aug-11 9:20

S Douglas

11-Aug-11 9:20

current1999 wrote:
After processing your ETL package which loads data into the cube, how do you
ensure that your cube has been processed?

Well it depends on what you are looking for in confirmation.

1: Confirmation of last successful processing
1.1: If the SQL Server agent is processing your cubes, then you can set the agent up to send out an email on completion. This will tell you if the process completed successfully or failed. The only caveat is if you run the job as a SQL Server Analysis command, then the agent wont know if the command failed or was successful. Just make sure to run your commands as a SSIS package and there wont be any issues.

1.2: In addition to agent notifications I use a modified version of the Microsoft SSAS example, AMO Display Object Names. I modified it to read the structure and send me an email with last processing date / times of the cubes and dimensions.
Info: http://technet.microsoft.com/en-us/library/ms160876(SQL.90).aspx

Had to remove some of the code as it contained company info in it, but here is the juxed of it

   try
    {
        //--------------------------------------------------------------------------------
        // For each database on the server, we'll display its name and other objects names.
        //--------------------------------------------------------------------------------
        foreach (Database db in server.Databases)
        {
            Console.WriteLine("{0}", db.Name);

            // Display Dimensions
            Console.WriteLine("\tDimensions:");
            foreach (Dimension dimension in db.Dimensions)
                Console.WriteLine("\t\t{0}", dimension.Name);

            // Display Cubes
            Console.WriteLine("\tCubes:");
            foreach (Cube cube in db.Cubes)
            {
                Console.WriteLine("\t\t{0}", cube.Name);

                // Display Measure Groups
                Console.WriteLine("\t\tMeasure Groups:");
                foreach (MeasureGroup group in cube.MeasureGroups)
                {/**/
                    Console.WriteLine("\t\t\tGroup Name: \t{0}", group.Name);
                    Console.WriteLine("\t\t\t\tSource: \t{0}", group.Source);
                    Console.WriteLine("\t\t\t\tCreated: \t{0}", group.CreatedTimestamp);
                    Console.WriteLine("\t\t\t\tLast Proc: \t{0} ", group.LastProcessed);
                    Console.WriteLine("\t\t\t\tEstimatedSize: \t{0} ", group.EstimatedSize);
                    Console.WriteLine("\t\t\t\tStorageLocation: \t{0} ", group.StorageLocation);
                    string sState = "None";
                    switch (group.State)
                    {
                        case AnalysisState.Processed:
                            sState = "Processed";
                            break;
                        case AnalysisState.PartiallyProcessed:
                            sState = "PartiallyProcessed";
                            break;
                        case AnalysisState.Unprocessed:
                            sState = "Unprocessed";
                            break;
                    }
                    Console.WriteLine("\t\t\t State: \t{0}", sState);
                    Console.WriteLine("\t\t\t\t Type: \t{0}", group.Type);

                    foreach (Measure meas in group.Measures)
                    {
                        Console.WriteLine("\t\t\t\t\t Name: \t{0}", meas.Name);
                        Console.WriteLine("\t\t\t\t\t\t Expression: \t{0}", meas.MeasureExpression);
                        Console.WriteLine("\t\t\t\t\t\t Aggregate: \t{0}", meas.AggregateFunction);
                        Console.WriteLine("\t\t\t\t\t\t DisplayFolder: \t{0}", meas.DisplayFolder);
                        Console.WriteLine("\t\t\t\t\t\t Source: \t{0}", meas.Source);
                        Console.WriteLine("\t\t\t\t\t\t DisplayFolder: \t{0} ", meas.DisplayFolder);
                        //Console.WriteLine("\t\t\t\t\t{0}", meas.);
                    }
                   foreach (MdxScript mdx in cube.MdxScripts)
                   {

                            string MeasureName = mdx.Name.Replace("[MEASURES].[", "");
                            MeasureName = MeasureName.Replace("]", "");

                            Console.WriteLine("\t\t\t\t\t Name: \t{0}", MeasureName);
                            Console.WriteLine("\t\t\t\t\t\t DisplayFolder: \t{0} ", mdx.Annotations);

                    }
                }


            }

            // Display Mining Structures
            Console.WriteLine("\tMining Structures:");
            foreach (MiningStructure miningStructure in db.MiningStructures)
                Console.WriteLine("\t\t{0}", miningStructure.Name);
        }
    }
    finally
    {
        server.Disconnect();
        server.Dispose();
    }
}
catch (AmoException e)
{
    Console.WriteLine(e.ToString());
}

2: Validating the data, that’s a whole task all unto it self. I used to do this with a SSIS package that would run a MDX query and send the results from that. However, I found that just making sure everything processed correct really solved this issues we where having.

Common sense is admitting there is cause and effect and that you can exert some control over what you understand.

Displaying SSRS Reports

Aptiva Dave4-Aug-11 6:28

Aptiva Dave

4-Aug-11 6:28

hello all. I've got a ssrs 2008 question that has been bugging me for a couple of months. I have built a site for my HR department that takes in applications. HR takes the information and needs to send it to Directors and Supervisors in an application-style form. I was planning on using SSRS mainly because I can just render the application in a pdf by just using the url, which HR could just email to whomever they want to. This would all work fine if all of our HR employees were on the same domain, but they are not and the system admins for some of the domains don't want to build a trust to us, so I can't just tie their domain account to the security of the application report (some of our sites may not even have people on domains).

What I was looking for was a way to have an account on the server itself be the one to run the report so that the user wasn't prompted for login credentials when they looked up an application.

Mycroft Holmes4-Aug-11 14:38

Mycroft Holmes

4-Aug-11 14:38

If you are using the server component of SSRS then I'm pretty sure you cannot allow anonymous connections, valid credentials are (should) always required by an internal server.

However there is no reason you can't host the reports in a separate aspx application using the internal app credentials to contact the server.

You could also change the reports to RDLC and embed them in an aspx application where the app fetches the data and supplies the viewer the RDLC file and the dataset, this requires the aspx app to have database credentials but not the end user.

Both solutions are terribly insecure and you need to carefully consider the business implications.

Never underestimate the power of human stupidity
RAH

Pradeep Shukla8-Aug-11 8:33

8-Aug-11 8:33

This is a tricky situation, all these different domains have to build some kind trust or all the users who would be accessing these reports can be moved to a different domain altogether which you can use in your application. In that case you can give access to the users of this New domain.

There is not shortcut here which can resolve this, either you use trusted users or use a proxy user to fetch reports for all the users consuming these reports.

Hope it helps...

With kudos,

Pradeep

Aptiva Dave8-Aug-11 8:50

Aptiva Dave

8-Aug-11 8:50

Using a proxy user, how would I do that? Could I use a service account on the server to run those reports?

Pradeep Shukla8-Aug-11 9:25

8-Aug-11 9:25

Yes...that's how you can achieve it. Meanwhile I got one more option in case if you are using SQL Server 2008 R2, you can create a subscriber database for the users who would access these reports and link all of them with your reports...this is something you can try....I would post some more details once I get more insight on this....

http://msdn.microsoft.com/en-us/library/ms169673.aspx[^]

with kudos,
Pradeep

Re: Displaying SSRS Reports [modified]

Aptiva Dave8-Aug-11 9:26

Aptiva Dave

8-Aug-11 9:26

that crossed my mind, but would that work if I'm using form auth?

Pradeep Shukla8-Aug-11 9:50

8-Aug-11 9:50

Yes it would...no issues with that..

ASP.NET - Forms authentication user impersonation[^]

there are plenty of other ways too to impersonate...

With kudos,
Pradeep

modified on Monday, August 8, 2011 3:59 PM

Pradeep Shukla8-Aug-11 10:01

8-Aug-11 10:01

I was not able to edit the existing reply...here is sample code:

Use this in the web.config:

<forms name="Testproject"
path="/"
loginUrl="~/Login.aspx"
protection="All"
timeout="20"
requireSSL="false"
slidingExpiration="true"
defaultUrl="Home.aspx"
cookieless="UseDeviceProfile"
enableCrossAppRedirects="false" />

</authentication>
<authorization>
<deny users="?"/>
</authorization>
<identity impersonate="true" userName="domain\user" password="xyz@123"/>

with kudos,
Pradeep

Chris Meech8-Aug-11 10:07

Chris Meech

8-Aug-11 10:07

No offense, but most production systems that I've ever worked on would consider embedding a password in plain text in a file, a security violation and not allow it. Even if that file is on a server with access permissions. Smile | :)

Chris Meech
I am Canadian. [heard in a local bar]

In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]

posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]

Pradeep Shukla8-Aug-11 10:29