Aggregation Types

SQL Server Analysis Services

Index ‹ SQL Server

- Previous
  - 1
    - Calling a package at Runtime I am working on a package that will sense a text file in a folder. From this text file I will be extracting the file name which will tell me what task I need to run. How can I create a package that, depending upon what the file name is, call a specific package. To be more clear, I will have 3 different flat files that is each in a standard form. Their names will be unique. To transform their data will require different ETL logic. I will be using a loop to constantly check to see if a file has been put into the directory. I just need to know how I can direct the input file to the proper ETL task. Tag: Aggregation Types
  - 2
    - define a default value ?? hallo i am on the very beginning with sql.s, how can i define a default value for the table field like its possible in access, for example: the if the user not gonna enter a val so there is a default val predefined in the table field, for example i would like to predefine the date now on the smalldate field.... thanks Tag: Aggregation Types
  - 3
    - Irritating issue in Reporting Services 2005 SP2. We don't face this problem in SP1. When we have a matrix and want to freeze some row-groups(columns), it does so. But when we drag the horizontal scroll bar of IE, the alignment of the row is completely screwed up. The text in the columns which are set to float, either shifts up or down. I guess this has to do with the wraping of the text in the row group cells. This is a great irritant and is stopping us from moving to SP2. Please let me know if this has been fixed or there is a workaround. Thanks Tag: Aggregation Types
  - 4
    - Accuracy of Host column on SS 2000 Enterprise Manager Current Activity Process Info Screen Yesterday and today, the Process Info screen in Enterprise Manager indicates that the "Host", a single workstation on our network, is generating around 50 connections to the database. This particular application is well-behaved and typically generates only 2 to 5 connections per instance. Furthermore, we cannot find this particular workstation/host on our network because we retired it off the network some time ago. Anyone know of the accuracy of the Hosts column on this Enterprise Manager screen We're suspecting that the network address column is correct but the Hosts column is incorrect. There are about 10 different network addresses pertaining to this particular Host (1-5 connections per network address). Tag: Aggregation Types
  - 5
    - Way to find registered classes/functions for the Assemblies added in Analysis Service. Hi, Is there some way through which we can find the classes and function registered with Analysis Services, when we add Assembly code for Stored procedures Thanks, Santosh. Tag: Aggregation Types
  - 6
    - Problem with AuthenticationExtension Hi, I got SSRS2005 with FormsAuthentication working for a while now without any problems. I decided to create some kind of 'Account Enabled' functionality and therefore I modified my AuthenticationExtension.cs. But what happened, the new functionality does not work. After adding some logging stuff to this file nothing changed. So it looks as if AuthenticationExtension.css is not being used at all in my security extension. I verified this by removing the file from the project and still...no changes! Other files in my security extension module are being used however. When I make a change to AuthenticationUtilities I can see it in my results. Does anyone know how this can happen Why is my extended IAuthenticationExtension not been used Thanks, Hans Tag: Aggregation Types
  - 7
    - Help with finding last record! Thanks guys, Will try these over the weekend when things quiten down here. No Xmas slowdown for us... Tag: Aggregation Types
  - 8
    - SAC Command Line utility doesn't start SQLBrowser Service I've exported surface area configuration settings from a server and tried importing them onto another server. The remote connection settings are imported but the SQLBrowser service remains Disabled. Am I doing something wrong The command line command I ran (in the C:\Program Files\Microsoft SQL Server\90\Shared directory) is "sac.exe in sacconfig.xml". The following is my config file: < xml version="1.0" encoding="utf-8" > <CommandArguments xmlns:xsi=" http://www.w3.org/2001/XMLSchema-instance " xmlns:xsd=" http://www.w3.org/2001/XMLSchema "> <Components> <anyType xsi:type="Component"> <Type>SqlServer</Type> <InternalType>DatabaseEngine</InternalType> <Instances> <anyType xsi:type="Instance"> <Name>SQLEXPRESS</Name> <Service> <Name>MSSQL$SQLEXPRESS</Name> <StartMode>Auto</StartMode> <ServiceState>Running</ServiceState> </Service> <Features> <anyType xsi:type="Pair"> <Key>AdhocRemoteQueries</Key> <Value xsi:type="xsd:int">0</Value> </anyType> <anyType xsi:type="Pair"> <Key>CLRIntegration</Key> <Value xsi:type="xsd:int">0</Value> </anyType> <anyType xsi:type="Pair"> <Key>DedicatedAdministrator</Key> <Value xsi:type="xsd:int">0</Value> </anyType> <anyType xsi:type="Pair"> <Key>DatabaseMail</Key> <Value xsi:type="xsd:int">0</Value> </anyType> <anyType xsi:type="Pair"> <Key>HTTPAccess</Key> <Value xsi:type="ArrayOfAnyType" /> </anyType> <anyType xsi:type="Pair"> <Key>OLEAutomation</Key> <Value xsi:type="xsd:int">0</Value> </anyType> <anyType xsi:type="Pair"> <Key>ServiceBroker</Key> <Value xsi:type="ArrayOfAnyType" /> </anyType> <anyType xsi:type="Pair"> <Key>SQLMail</Key> <Value xsi:type="xsd:int">0</Value> </anyType> <anyType xsi:type="Pair"> <Key>WebAssistant</Key> <Value xsi:type="xsd:int">0</Value> </anyType> <anyType xsi:type="Pair"> <Key>XP_cmdshell</Key> <Value xsi:type="xsd:int">0</Value> </anyType> </Features> <Protocol> <anyType xsi:type="Pair"> <Key>NamedPipes</Key> <Value xsi:type="xsd:boolean">true</Value> </anyType> <anyType xsi:type="Pair"> <Key>TCPIP</Key> <Value xsi:type="xsd:boolean">true</Value> </anyType> </Protocol> </anyType> </Instances> </anyType> <anyType xsi:type="Component"> <Type>SqlBrowser</Type> <InternalType>SqlBrowserService</InternalType> <Service> <Name>SQLBrowser</Name> <StartMode>Auto</StartMode> <ServiceState>Running</ServiceState> </Service> </anyType> </Components> <Version>9.0.242.0</Version> </CommandArguments> Tag: Aggregation Types
  - 9
    - Which Data Flow task to use? I have a table which has been loaded from various source feeds. The SourceId relates to the source name and the SourceCompanyId is the sources primary key for the company. I am basically trying to create one row with all the SourceCompanyIds in my column headers. What data flow tasks would be necessary in SSIS The structure of the final table is: CREATE TABLE [dbo] . [Company] ( [CompanyId] [int] IDENTITY ( 1 , 1 ) NOT NULL, [CompanyName] [varchar] ( 75 ), [CIK] [varchar] ( 10 ), [Ticker] [varchar] ( 10 ), [Source1CompanyId] [int] NULL, [Source2CompanyId] [int] NULL, [Source3CompanyId] [int] NULL, [Source4CompanyId] [int] NULL, [Source5CompanyId] [int] NULL, [Source6CompanyId] [int] NULL, CONSTRAINT [PK_Company] PRIMARY KEY CLUSTERED ( [CompanyId] ASC ) WITH ( IGNORE_DUP_KEY = OFF ) ON [PRIMARY] ) ON [PRIMARY] ================================= The table in which contains all the company data CREATE TABLE [dbo] . [SourceCompany] ( [SourceId] [int] NOT NULL, [SourceCompanyId] [varchar] ( 10 ) , [SourceCompanyName] [varchar] ( 75 ) , [CIK] [varchar] ( 10 ) , [Ticker] [varchar] ( 10 ) , CONSTRAINT [PK_SourceCompany] PRIMARY KEY CLUSTERED ( [SourceId] ASC , [SourceCompanyId] ASC ) WITH ( IGNORE_DUP_KEY = OFF ) ON [PRIMARY] ) ON [PRIMARY] Tag: Aggregation Types
  - 10
    - Reporting Services 2000 Installation Problems. Hello every1. I have been trying to install Reporting Services 2000 on a Windows Server 2003 machine with SQL Server 2000 and Framework 2.x for a couple of days now. When i run Reporting Services setup it tells me that Asp.Net is not installed or is not running, i know this could be caused becouse i have Asp.Net 2.x running instead of 1.x, but what ever i do, changing all IIS settings and website settings so the server uses Asp.Net 1.x the problem still exists, is there any solution besides a reinstallition and installing RS2000 be4 the Framework 2.0 Tag: Aggregation Types
  - 11
    - 64-bit Separate Install?? One 'quick and dirty' way to confirm that your server is running all 64bit applications is to examine the services applet ([Control Panel], [AdministrativeTools], [Services]). If you see WOW in the list, some application is running in 32 bit. Tag: Aggregation Types
  - 12
    - Is there 1 default instance per OS Server or per SQL Server? On a Server 2k3 x64, I've installed SQL Server 2000 x32 SP4 and SQL Server 2005 x64. When installing both SQL Server 2000 and 2005 on the same server, does each SQL Server have it's own "default instance" or there can only be one "default instance" per server TIA, Gary. Tag: Aggregation Types
  - 13
    - SSMS Help Hi, I'm not sure this is the right discussion room that I'm looking for my problem. In fact, the problem is very simple though I don't know how to deal with it. I accidentatly clicked some buttons on my SSMS IDE. And, my IDE seems bad. I uncheck 'Dockable' click box of SSMS's Object Explorer. And, is there anybody who know how to put that back to the original format Thanks. Myo Zaw Tag: Aggregation Types
  - 14
    - Creating an Windows Rdl on the fly using .net programming You are welcome and I hope that the Report Build is what you are looking for. Tag: Aggregation Types
  - 15
    - Datamigration to MS CRM PedroCGD wrote: Dear Smilebey, Send me the CREATE Table statment of your tables in source and destination to pedro.perfeito@cgd.pt . I will try to help you, and give you the better option to resolve it! Thanks! Actually, Pedro, that's what we do here in the forums... We try to help individuals here so that others can see the results. Tag: Aggregation Types
  - 16
    - SQL server replication connection problem Hi I have a problem when trying to setup replication between two SQL servers on different networks. My publication server is SQL Server 2005 Standard Edition. My subscription server is SQL server 2005 Express Edition I have setup the publication no problem but am now trying to create my subscriber. However, when trying to connect to the server, I receive the following error: SQL Server replication requires the actual server name to make a connection to the server. Connections through a server alias, IP address, or any other alternate name are not supported. Specify the actual server name, 'W2M-RDC-01'. (Microsoft.SqlServer. However, because the servers are not on the same network, I cannot use the server name. Why does it not allow you to connect to an IP Is there a way to create an alias I have read that you can but am unsure how to do this. Do I need to change the hosts file If so, where can I find this and what exactly do I need to change Kind Regards Dan Rostron Tag: Aggregation Types
- Next
  - 1
    - the procedure 'sp_addrolemember' cannot be executed within a transaction I use sp_addrolemember inside a DML trigger and every thing goes well in my computer; I can insert or update into my table and sp_addrolemember is executed successfully, but my customer get this error every time he tries to insert or update into the table. "the procedure 'sp_addrolemember' cannot be executed within a transaction" Can anybody help me solve this funny error. Tag: Aggregation Types
  - 2
    - Get Culture for Multilanguage in Reporting Service Hi Nick. Thanks for your response and suggestion. I read the link that you provide and I recheck my config file. Duh! I make mistake in the config file. No wonder my report display #Error because it cannot do some access that it want. Just like what you said. Now, I'm working on getting the right culture to pass in my code. Anyway, Thanks a lot. Tag: Aggregation Types
  - 3
    - using case statement to determine Order by field and direction (asc or desc) when using Row_number Thanks Mani and OJ. I started writing a solution and this helped me confirm that I need to break down into several case statements this way. Also yeah I need it to be compatible with various datatypes while sorting. Thanks again! Tag: Aggregation Types
  - 4
    - Reporting Services 2005 with SQL 2000 Database Hi friends, i have some questions about Reporting Services 2005: Can i use RS 2005 with a SQL 2000 Database Can i create a report with RS 2005 (with a SQL 2K Database) a show it in a webform What do i need to install (in the webserver and in the Database server) in order to start designing reports in RS 2005 against a SQL 2K DB Thanks in advance. Regards Eduardo Tag: Aggregation Types
  - 5
    - Deleting data bloats log file First up the warning about ntext and fulltext search. Full Text change tracking does not support updates made via writetext or updatetext which is probably why you are getting that error. As you are using SQL2005 ( ) i'd suggest changing the column type to nvarchar(max). Text, Ntext and image have been superceded but are supported for backward compatability. As for the size of the database. When you delete data, the size of the datafile will not shrink but the pages within that will be marked as free. Generally speaking its a good idea to have your datafile at a size bigger than the current database size as this allows for growth of data without the (expensive) operation of growing the datafile. In saying this, you are able to shrink the data file in a similar way to the transaction log. HTH! Tag: Aggregation Types
  - 6
    - Could not find "Standby" in sysservers. Run "sp_addlinkedserver" to add the server to sysservers" You're welcome. Please do close the post. Cheers. Tag: Aggregation Types
  - 7
    - running ssis package with ssis run time compoenents and sql server 2000... Hi Jamie, thanks. I have similar concerns. Do you have any links or infromation pages that we can read through and provide it to our DBAs. Thanks, $wapnil Tag: Aggregation Types
  - 8
    - export table to excel I'm sure that you could write a .NET web application. Such web applications written by my coworkers typically call stored procedures that contain the desired SELECT statement. As such, the stored procedure returns a table. If multiple SELECT statements are in the stored procedure, it returns multiple tables. You may need additional software to convert the SELECT output to an Excel spreadsheet. Perhaps you can do that with SSIS. You might consider asking that in the SSIS forum. What software can you expect users to have on their PCs Tag: Aggregation Types
  - 9
    - CTE behaviour in SQL 2005 I were trying to achive paging through using a CTE etc, but ran into the following weither thing happening. The CTE allows me to use avariable as the ORder By field, although the CTE do not care at all what is in there Have any one seen this or maybe can explain this USE AdventureWorks ; GO DECLARE @SortExpression Varchar ( 50 ) Set @SortExpression = 'SalesPersonID ASC' ; WITH Sales_CTE ( RowNumber , SalesPersonID , NumberOfOrders , MaxDate ) AS ( SELECT ROW_NUMBER () OVER ( Order by @SortExpression ) RowNumber , SalesPersonID , COUNT (*), MAX ( OrderDate ) FROM Sales . SalesOrderHeader GROUP BY SalesPersonID ) Select * From Sales_CTE ; WITH Sales_CTE1 ( RowNumber , SalesPersonID , NumberOfOrders , MaxDate ) AS ( SELECT ROW_NUMBER () OVER ( Order by SalesPersonID ASC ) RowNumber , SalesPersonID , COUNT (*), MAX ( OrderDate ) FROM Sales . SalesOrderHeader GROUP BY SalesPersonID ) Select * From Sales_CTE1 Tag: Aggregation Types
  - 10
    - Dynamically determine Linked Servers I run into this problem all the time. You need to create a table (temp is fine) before you run your dynamic SQL. Then alter/use your dynamic SQL to populate the table. Then return the table in your function. HTH Dan Tag: Aggregation Types
  - 11
    - SQL Problems I am having following error while execute the command given, can you pls assist Thank you. 'BULK' is not recognized as an internal or external command, operable program or batch file. Tag: Aggregation Types
  - 12
    - Multiple web viewers and AsyncRendering true I have multiple viewers on a web page and initialy would like AsyncRendering = true on all viewers, so I can have the loading message. Once the user drill-through, I hide all other viewers and change to AsyncRendering = false, so the autosizing will work. But, with AsyncRendering = true (in code behind): private void SetDominateViewerProperties(ReportViewer rptViewer) { rptViewer.AsyncRendering = false; rptViewer.ShowToolBar = true; rptViewer.ZoomPercent = 100; rptViewer.Visible = true; } private void SetDefaultViewerProperties(ReportViewer rptViewer) { rptViewer.AsyncRendering = true; rptViewer.ShowToolBar = true; rptViewer.ZoomPercent = 100; rptViewer.Visible = true; } I have only one viewer showing, even though, I can see in debug that all viewers are set to visible. In SetDefaultViewerProperties , I change rendering to false, I get multiple viewers. Are there combinations of property values I need to set Tag: Aggregation Types
  - 13
    - UnknownSqlServerError Ah, I think I understand what you are wanting to do now. You want to be able to use the URL to call the webmethod directly, instead of sending a full blown SOAP request. Sorry, the SQL Server 2005 Native XML Web Services feature only support proper SOAP request as specified by the SOAP 1.1 spec. The only thing you can retrieve using on the URL is the WSDL document, as you found out using http://localhost/sql wsdl To call the webmethods you've specified on the endpoint, you will need to send a HTTP POST request with the proper SOAP message body. Please refer to the following MSDN topic for additional information about the exact SOAP message structure supported: http://msdn2.microsoft.com/en-us/library/ms190796.aspx For a sample on writing a SOAP client application using .Net Frameworks, please refer to http://msdn2.microsoft.com/en-us/library/ms190173.aspx HTH, Jimmy Tag: Aggregation Types
  - 14
    - SQL Server 2005 Express Edition with Advanced Services Service Pack 2 - Error 65B It would help to have a look at the install log file, they are located in the setup log folder at C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG. If this is the update comming from Microsoft Update, you'll want to look in the Hotfix folder and check out summary.txt. Mike Tag: Aggregation Types
  - 15
    - ssis cannot pull columns from stored procedure? hi, i have the following stored proc which returns a resultset at the end, i have an SSIS package that calls this stored proc and outputs the result to a file. However, the package fails because it cannot pull the columns for the schema because of the return lines in the middle of the stored proc. If i remove the it works fine, pulls hte columns as normal. but if i leave them in ssis cannot get the columns. what can i do to get around this ALTER PROCEDURE [dbo] . [uspCreateBrightPointFile] AS SET nocount ON IF EXISTS ( SELECT TOP 1 * FROM brightpointfile ) TRUNCATE TABLE brightpointfile -- Get the order information from the database where vendorconfirmationID = 0 INSERT INTO brightpointfile SELECT o . orderid , o . requestid , '295193' AS araccountnumber , o . orderdate , 'PRIORITY' AS shipmethod , 'Asurion Dobson' AS billname , 'PO Box 110808' AS billaddress1 , 'Attn Account Receivable' AS billaddress2 , ' ' AS billaddress3 , 'Nashville' AS billcity , 'TN' AS billstate , '37222' AS billzip , c . fullname AS shipname , Replicate ( ' ' , 100 ) AS shipaddress1 , Replicate ( ' ' , 100 ) AS shipaddress2 , ' ' AS shipaddress3 , Replicate ( ' ' , 40 ) AS shipcity , ' ' AS shipstate , Replicate ( ' ' , 10 ) AS shipzip , '1' AS linenumber , ve . sku AS itemcode , o . quantity AS qty , r . typeid , r . customerid , 0 AS addressfound FROM [order] o WITH ( NoLock ) JOIN request r WITH ( NoLock ) ON o . requestid = r . requestid JOIN customer c WITH ( NoLock ) ON r . customerid = c . customerid JOIN ( SELECT [subequipid] , [subid] , [clientequipid] , [serialno] , [statusid] , [startdate] , [enddate] , [createdate] , [createuserid] FROM subequip s1 WITH ( NoLock ) WHERE s1 . statusid = 1 AND s1 . startdate = ( SELECT MAX ( s2 . startdate ) FROM subequip s2 WITH ( NoLock ) WHERE s2 . statusid = 1 AND s2 . subid = s1 . subid )) se ON r . subid = se . subid JOIN vendorequip ve WITH ( NoLock ) ON se . clientequipid = ve . clientequipid JOIN clientequip ce WITH ( NoLock ) ON ce . clientequipid = se . clientequipid WHERE vendorconfirmationid IS NULL -- order was never sent to CellStar AND ve . typeid IN ( 1 ) -- only pull direct fulfillment (not store fulfillment) AND ve . vendorid IN ( 2 ) -- only pull vendor = CellStar Insurance AND o . orderdate > '2007-08-01' -- only pull orders after 08/01/2007 --IF @@ERROR <> 0 --RETURN 1 --First use the address types of 2 UPDATE brightpointfile WITH ( ROWLOCK ) SET shipaddress1 = b . address , shipaddress2 = b . address2 , shipcity = b . city , shipstate = b . stateid , shipzip = b . zipcode , addressfound = 1 FROM customeraddress a WITH ( NOLOCK ), address b WITH ( NOLOCK ) WHERE a . customerid = brightpointfile . customerid AND b . addressid = a . addressid AND a . typeid = 2 --IF @@ERROR <> 0 --RETURN 2 --Update the rest where the address type is 1 and there is no type 2 UPDATE brightpointfile WITH ( ROWLOCK ) SET shipaddress1 = b . address , shipaddress2 = b . address2 , shipcity = b . city , shipstate = b . stateid , shipzip = b . zipcode FROM customeraddress a WITH ( NOLOCK ), address b WITH ( NOLOCK ) WHERE a . customerid = brightpointfile . customerid AND b . addressid = a . addressid AND a . typeid = 1 AND brightpointfile . addressfound = 0 --IF @@ERROR <> 0 --RETURN 3 --Select all the records from the temp table, plus union in 2 extra records required by the client SELECT * FROM ( SELECT orderid , requestid , araccountnumber , orderdate , shipmethod , billname , billaddress1 , billaddress2 , billaddress3 , billcity , billstate , billzip , shipname , shipaddress1 , shipaddress2 , shipaddress3 , shipcity , shipstate , shipzip , 1 AS linenumber , itemcode , qty , 0 AS additional FROM Brightpointfile WITH ( nolock ) UNION ALL SELECT orderid , requestid , araccountnumber , orderdate , shipmethod , billname , billaddress1 , billaddress2 , billaddress3 , billcity , billstate , billzip , shipname , shipaddress1 , shipaddress2 , shipaddress3 , shipcity , shipstate , shipzip , brightpointdefaultsku . linenumber , brightpointdefaultsku . sku , 1 , 1 AS additional FROM Brightpointdefaultsku WITH ( nolock ), Brightpointfile WITH ( nolock )) a ORDER BY orderid , additional IF @@ERROR <> 0 RETURN 1 RETURN 0 Tag: Aggregation Types
  - 16
    - Problem with function Hello, I am unable to resolve an error message for following user defined function. Scope of this function: This functions generates a unique identifier no from a table and returns it to caller. SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE FUNCTION SystemNoShiva1 ( -- Add the parameters for the function here ) RETURNS int AS begin DECLARE @st2 numeric(18,0) DECLARE @sys01 numeric(18,0) declare sys cursor for Select Max(SystemNo) From TISSystemMst1 open sys Fetch sys into @sys01 begin --DECLARE @st2 numeric(18,0) if (@@fetch_status = 0) -- IF @@ROWCOUNT <> 0 close sys Deallocate sys select @sys01 = @sys01 + 1 --update TISSystemMst1 set systemno = (@sys01) insert into TISSystemMst1 values(@sys01) select @st2 = @sys01 end RETURN @st2 end Messeg I receive is Msg 128, Level 15, State 1, Line 17 The name "sys" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted. How can I clear above error message Nilkanth Desai Tag: Aggregation Types

Shyam Sundar

Can anyone briefly describe all aggregation types available in SQL Server Analysis Services

Thanks,

Shyam

Top

Re: Aggregation Types

Thomas Ivarsson

Hello! Can this blog post help you: http://blogs.conchango.com/christianwade/archive/2005/04/07/1252.aspx

HTH

Thomas Ivarsson

Top

Re: Aggregation Types

Shyam Sundar

Hi Thomas,

Thanks for the link.

That kind of answered my question in a brief way.

Thanks,

Shyam

Top