Month's end

SQL Server Analysis Services

Index ‹ SQL Server

- Previous
  - 1
    - Cannot Login VB express and SQL express are on the same desktop. I have an application in VB that works fine.The issue I have is if I go into SQL Management to work on the DBand then close SQL Management Express. When I go back to execute my VB application I get a message like this " Login failed for "user name" file is in use by another application". If I reboot my system and then run the VB application it works OK. I've tried doing a disconnect before I exit SQL Management but this had no affect on the problem. Is there something I need to do when I exit SQL Management Express so I can immediatley run my VB applicaton without having to reboot Tag: Month's end
  - 2
    - SQL Server dropping comma in German language All, I've a VB6 application that writes data to a SQL Server database using ADODB in the VB6 code. My market has been the United States. My application is written for the English language. I've a new user in Germany. Unfortunately, every number that contains a decimal is now stored in the SQL Server database with the decimal dropped. For example, 29.15 gets stored as 2915. I've done some searching and found the culprit is probably the OS Regional Language setting. The Germany user is using German. So it seems ADODB is expressing "29.15" as "29,15", and SQL Server is having a fit when ADODB is trying to write to the database. My client is reluctant to changing all their user profile language settings to German (or the other option, changing their German language setting decimal symbol option to "."). They fear other applications may be affected. Can anyone suggest a solution on my software end Does ADODB have some options of which I'm unaware that will ignore the regional language setting and express decimal-containing numbers simply as "." Or is there a setting in SQL Server to compensate I appreciate all and any help. Thanx! Tag: Month's end
  - 3
    - Maximum length of an expression Is there a maximum number of characters in an expression thx Helen Tag: Month's end
  - 4
    - AMO Role Creation Hi, I have just created a role using AMO. I'm supposed to see this role in the Analysis Services Project once I open it, right But I cannot. I tried executing again my codes, and it prompted me an error, which means that it was created successfully. Error: Another 'Role' object has the 'TestRole' name. Why can I not see that in the AS Project By here is my code: Svr = New Server Svr.Connect( "Data Source=localhost" ) DB = Svr.Databases( "AS17" ) Cub = DB.Cubes( "Test" ) UserRole = DB.Roles.Add( "TestRole" ) UserRole.Members.Add( New RoleMember( "xa\chemps" )) UserRole.Update() thanks a lot! cherriesh Tag: Month's end
  - 5
    - Proclarity analytic Views without using sharepoint Hi All, May I know there is any way to show the proclarity analytic views or reports in a web portal without using sharepoint server If there is no way, can u brief me how to create proclarity analytic views and how to show those views in web portal ( browser) by using sharepoint server What kind and what version of SSAS , proclarity , and Sharepoint do I need to use I have no experience in BI tools and Data Warehouse. I am very interested in that field. Moe Min Tag: Month's end
  - 6
    - static variables in a SQLCLR stored proc seem to get reused from execution to execution of the sp after moving off VS debugger and into management studio to exercise our SQLCLR sp, we notice that the 2nd execution gets an error suggesting that our static SqlCommand object is getting reused from the 1st execution (of the sp under mgt studio). If this is expected behavior, we have no problem limiting our statics to only completely reusable objects but would first like to know if this is expected Is the fact that debugger doesnt show this behavior also expected Tag: Month's end
  - 7
    - Add list as parent for rectangle in rdl Hi! I have a report designed with all the tables in the rectangles on the rdl. Right now, I need add "list" control on the top of those rectagles(including all the tables) so all the rectangles have list as parent I don't want to start over to just for adding the list. I was trying to edit report xml file to add the "list" on the top of rectangles but the compiler complains. The rectangle property has "parent" field ,which is "body" right now. How can I make it's parent as "list" This field is grey out so I can not change it in the property window. Any idea Thanks. Tag: Month's end
  - 8
    - Printing in Reporting Services hangs When you start printing, open up the sql profiler on the sql box and see whats going on in there. Is there any particular stored procedure which is executing over and over again, as if in a loop. Tag: Month's end
  - 9
    - MICR Font Issue When Exporting to PDF Microsoft tech support says the font must be a True Type font. Also, for some reason, the fonts seem not to work at 12pt or above. 10 and 11 pt seems to work fine. Tag: Month's end
  - 10
    - Stored Procedure adding new row If the string is a constant for every row then define a computed column or put the expression in a view instead. There is no reason to store the value if it can be computed at runtime. If you need to search on this computed value then you can index using computed column for example. Tag: Month's end
  - 11
    - Member Property length limitation I have a member property in a shared dimension MSAS 2000. The length of column from which this member property fetches value is nvarchar(2000). But the OLAP cube is reading only 255 characters ONLY. Is this a common limitation If yes, what is the alternative to increase its length to grater than 255 Any help will be greatly appreciated. Thanks. Tag: Month's end
  - 12
    - SQL Server 2005 Evaluation Install Error Looking at the Advanced tab, environment variables, I see that they appear to be in order. I see the "User Variables" for my ID lists the TEMP & TMP files to C:\Documents and Settings\ my ID \Local Settings\Temp. They seem to be in order. Tag: Month's end
  - 13
    - Custom Control Flow Item Issue Karunakaran - Thanks..that definitely shed some light on the topic! I thought it was being run on the server via RPC, with status messages being returned back... I do not have any SSIS Service on my machine, so will install it. (I do have SQLServer express, but am not using it). However , just as a test, I removed the 'FileWatcher' control, then re-imported the package to the server, and it is now running (validation's all completed, and it has "Start"ed), and I see my recordcount increasing, so it is working. How is this possible if I don't have SSIS installed Tag: Month's end
  - 14
    - Move distribution data/log files to another drive I haven't found the definitive answer on how or if this can be done without removing replication. I'm thinking ALTER DATABASE modify_file is the way to go. Anybody know if this will work or a better way to go about it Thanks Tag: Month's end
  - 15
    - Data Source and Connection Manager jh0483 wrote: But for me there is a big advantage : if you have many packages sharing the same connections and you have to change the connection, then you just have to change it in one place. Have you ever used or at least looked into configurations They allow you to manage connections through a single point, across multiple packlages and even solutions. They also have the huge benefit of working at runtime, and are a very valuable tool when it comes to deploying your packages. Use both if you want, but without configurations you will have to make changes as packages get deployed between environments. Data sources do not solve that for you in any effecitive manner. Configurations do, and for my money work just as well when devloping packages. Tag: Month's end
  - 16
    - Query Execution failed for dataset error / the syntax for " " is incorrect Hi, Am totally new to MDX Queries. I got this error while previewing a report that we had made. It goes like this -- Query execution failed for dataset 'some_dataset'. Parser: the syntax for 'some_character' is incorrect. What was figured out was this error shows up only for those values in a particular dropdown list which are multiword. Eg : My Company Technology and not for those values which a re single word. Eg : Company what could be the possible problem and the solution to that. Tag: Month's end
- Next
  - 1
    - Backup File Name Dear all, On scheduling the online backup for SQL server 2005 with SQL server management studio, how the backup filename can be automatically change for everyday. I mean the backup filename be automatically generated every day. Thanks Imran Hasware Tag: Month's end
  - 2
    - CONTAINS and WHERE Clause Combination taking too long Hi, I have a table with 3 columns and 20 million records. first 2 columns have VARCHAR(4) data type and third column is VARCHAR(5000). I put 3rd column under FULLTEXT and implement a normal INDEX on 1st column. Now when i try to search SELECT TOP 20 col1, col3 FROM tbl WHERE col1 = '1234' AND CONTAINS(col3,'"market*"') I am facing following problems 1- It hang for like 1 minute and give 2 records, whereas if i remove col1='1234' from where clause it take less than 1 second. 2- Some time it show criteria is too complex, although i am only requesting a single word in col3. I am noob in FULL-TEXT but i have done all research in books, microsoft forum and Google and not getting any information. Please assist. Tag: Month's end
  - 3
    - stored procedures, compile or not Thanks everyone. After further research I discovered I was opening the age old dynamic sql vs stored procs battle. I think I have the information necessary to proceed. My problem is passing 8 or 10 parameters and the logic necessary if a parameter is null, but that is an issue I can deal with. I just didn't want to go to all the trouble of rewriting a hundred dynamic sql scripts into stored procs if there was no benefit to be derived. Thanks again. Tag: Month's end
  - 4
    - Add connection error: 40 Hi i just reinstalled windows, and installed visual studio .net 2005 and the Sql server. But when im trying to add connection, select my computer at Server name, type a databasename then pressing OK i get this message: An error has occured while estabilishing a connection to the server. When connection to SQL server 2005, this faliure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Names pipes provider, error: 40 - Could not open a connection to SQL Server) Does anyone know what todo herman Tag: Month's end
  - 5
    - is it possible to connect to SQL Express without network? I have notebook, client server application and sql express sp2 on this notebook. Also, I know domain user login/password, but I don't know any local user name/password and I don't know any sql server user name/password. Domain user is not a local administrator on this notebook. When notebook connected to the office LAN I am working good with my app-> my sql express as a domain/user. I'd like to work at home too. I can logon to the system (XP SP2) using domain account when notebook has no network connection. (probably OS caches something) Is it possible to work with sql express using domain login when no network connection PS. old version of this software used MSDE 2000 and it was possible(!). Thank you. Tag: Month's end
  - 6
    - Cannot completely install/remove SQL Server Express I cannot use, remove, or reinstall Microsoft SQL Server 2005. Although Microsoft SQL Server 2005 and Microsoft SQL Server Setup Support Files appear in ControlPanel-ARP, when I attempt to remove Microsoft SQL Server 2005, the workstation component remains. If I try to download and reinstall Microsoft SQL Server Express from the Microsoft website, the download does not install completely as described below. My current situation is much better than the previous incompatibility or configuration problems I had been experiencing between Microsoft Office Small Buisness Accounting 2006, Microsoft Office Outlook 2003, Outlook Business Contact Manager, Microsoft SQL Server 2005 (express edition installed in conjunction with Visual Basic 2005 express), and Microsoft SQL Server Desktop Engine (installed in conjunction with Small Business Accounting 2006). The problems and error messages previously included: 1) A consistent bootup error (Net service configuration error...The specified service does not exist as an installed service); 2) The inability of Visual Basic to connect to an instance of SQL server (An error has occurred while establishing a connection to the server...); 3) a SQL server configuration manager error: Cannot connect to WMI provider. You do not have permission or the server is unreachable. Note that you can only manage SQL Server 2005 servers with SQL server configuration manager. Invalid namespace [0x8004100e]; and 4) SQL server management studio express error: Cannot connect to <computername>\SQLEXPRESS... An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1). These problems have been partially solved by: 1) completely removing Small Business Accounting 2006, Outlook Business Contact Manager Update, and MSDE (which appear to be codependent); and 2) removing and reinstalling Visual Basic 2005. However SQL Server still cannot be completely removed or reinstalled and therefore Visual Basic cannot connect to SQL datafiles. When I most recently tried to remove SQL server 2005 using ARP, the installer reported that only the workstation component remains (Version: 9.00.1399.06 Edition: Express Edition Patch level: 9.00.1399.06), but is not removed by the ARP process. When I most recently tried to install SQL server 2005, the Setup Support Files install correctly, the System Configuration Check passes, but setup fails for MSXML6, SQL Server Database Services, and WorkStation components Books Online (detail log SQLSetup0068 file). According to Add/Remove programs, the following applications are installed: Microsoft Visual Basic 2005 Express Edition ENU Microsoft SQL Server Native Client Microsoft SQL Server Setup Support Files (English) Microsoft SQL Server VSS Writer Microsoft SQL Server 2005 Various MSXML versions Any help in un/reinstalling or configuring Microsoft SQL Server 2005 for use with Microsoft Visual Basic 2005 Express Edition would be dearly appreciated. Thanks. Tag: Month's end
  - 7
    - Transaction log backup error Hello, I have this backup scheduled for "All databases" on server, which is around 300 at this point. The application is set up that way that it creates a new database as needed (dynamically). Do not ask me why - I hate this design... So, I think what happens is since my full backup of all databases are scheduled to run only once at night, and tran logs every hour, when new database is created during the day, there is no full backup for it, that is why tran logs backup fails. Becuase after the failure, if I run full backup again, then tran log runs just fine afterwards. I am new to MS SQL Server, I am mostly working with Sybase IQ. Do you know if I can "trigger" full backup every time when new database created to avoid tran lof failure Thank you very much Tag: Month's end
  - 8
    - Email as csv using Database Mail Hi everyone, I'm having problems sending my query results as a csv file. Ideally I would be able to send my results from a sql job every night as a csv but I'm having problems doing it. My code is shown below Code Block EXEC msdb.dbo.sp_send_dbmail @profile_name = 'admin' ,@recipients = m@m.com ,@subject = 'Notification Created Today' ,@attach_query_result_as_file = 1 ,@body_format = 'text' ,@query =' select a1 . c# , cli , fe , type , dateReq , p , ( select convert ( varchar ( 50 ), convert ( money , amount ), 1 )), ( select convert ( varchar ( 50 ), convert ( money , vat ), 1 )),( select convert ( varchar ( 50 ), convert ( money , total ), 1 )), cReq from Disb_1 a1 inner join Details on a1 . c# = Details . c# where datediff ( day , getdate (), dateReq )= 0 and dateReq <= getdate ()' I've tried many different things and been through the parameters of sp_send_dbmail but I just cant find anything. Also, how can I use column names for those money conversions At the moment it just says 'no column name' Thanks, Steve Tag: Month's end
  - 9
    - convert timestamp to datetime I'm use this statement. CONVERT ( CHAR ( 19 ), GETDATE (), 20 ) Tag: Month's end
  - 10
    - Unable to make changes to disconnected recordset Its been almost 10 years since I have had to do work with good-old RecordSet objects... I am filling a RecordSet with data returned from a SQL server via a stored procedure. I then set the ActiveConnection property to Nothing in order to disconnect it so I can make changes to it. But when I try to set the value on a given row I get back a "Multiple-step operation generated errors. Check each status value" error message. My understanding is that this is indicative of trying to use the wrong datatype. I have verified that the type is correct (I am dealing with integers) so I am at a loss for what the problem could be. Here is my code: Set rsPackages = CreateObject("ADODB.RecordSet") rsPackages.CursorLocation = adUseClient rsPackages.LockType = adLockBatchOptimistic rsPackages.Open "EXECUTE stp_FetchPackageData", myConn rsPackages.ActiveConnection = Nothing Response.Write("Value: " & rsPackages("TotalCount")) ' returns 0 Response.Write("Data Type : " & rsPackages.Fields("TotalCount").Type) ' returns 3 = adInteger rsPackages("TotalCount") = 1 ' throws multi-step error Oddly enough, when I look at the Attribute property of the field I get back a value of 112. When you break it down I think that value indicates the row value is read-only (Could that be my problem Just a really bad/unhelpful error message ) But if I try to change it I get back a message saying cannot be done since RecordSet is already open. Thanks, Jason Tag: Month's end
  - 11
    - What is wrong with my codes to connect VB.net to sql server 2005 Express database? My previous 'suggestion' was missing the keyword 'AS' in front of string causing the error. Reviewing your original code: Dim DB_Location = " c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Trial1.mdf " (REMOVE -NOT NEEDED) Dim ConnStr as String = "Server=localhost; DataBase= Trial1; Integrated Security =SSPI" Dim sqlConnection As New SqlConnection(ConnStr) 'Use database Dim strSQL As String = "SELECT FROM Trial1 WHERE col2 = 'trial' " Dim cmd As New SqlCommand(strSQL, SQLConnection ) I suggest ConnStr is more understandable than MyConnection , since it is a connnection string and NOT a connection object. Tag: Month's end
  - 12
    - I am new this SSIS, Please help me. I suggest you download tutorial from www.microsoft.com/ download s/ details.aspx FamilyID=B1145E7A-A4E3-4D14-B1E7-D1D823B6A447&displaylang=en. It's great for the begining. Tag: Month's end
  - 13
    - No Parameter Pane when Reporting in 2005 against a 2000 AS Cube I have this strange issue. VS2005 SSRS2005 SSAS2000 cube based on data from SQL 2005 database When i connect to the SSAS cube and create a dataset in a SSRS 2005 report i do not get a parameter pane. Also, if i right click on a dimension & select "Add to filter" i get the following error message "Object reference not set to an instance of an object" It's not my installation of SQL 2005, as I can connect to an SSAS2005 cube and get the filter/parameter pane and do not get the error message. Parameters work fine. Have i come across a known issue when reporting on SQL 2005 versus a SQL 2000 AS Cube... or can someone shed some light on the issue for me.... If someone can tell me how to display screenshots I can add them to a post Thanks Will Tag: Month's end
  - 14
    - Rolling back transactions for multiple Stored PRocs I have a quick question. I need to execute some stored procedures in certain steps... all performing some inserts/updates What i need is, a mechanism, by which i can roll back to the previous state if i encounter an exception (either in the app or SP) so, if the my first two SP execute fine, perform their functionality like insert/update, and the third one fails... how can i roll back to the initial state in ASP.NET. would appreciate any info. or redirection to the the location where i can look up some info on that. AJ Tag: Month's end
  - 15
    - Framework 2.0 changes - TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host. Hi Fredrik, It appears that Sqlclient grabs a connection from the pool that has already been closed because of a network error and reports an error when it tries to write to the socket. I can't understand why the 1.1 framework would be less susceptible to these network errors than the 2.0 framework. Would you be able to take a client-side trace that captures the error and send it to me along with the server's error log You can email me the files at ilsung@microsoft.com . The instructions for setting up the trace are here: http://msdn.microsoft.com/library/default.asp url=/library/en-us/dnadonet/html/tracingdataaccess.asp . Also, if you know of a way for us to repro this, that would be great. Thanks, Il-Sung Lee Program Manager, SQL Server Protocols Microsoft Corp. This posting is provided "AS IS" with no warranties, and confers no rights. Tag: Month's end
  - 16
    - Problem With Common Table Expression (CTE) I constructed the following but I get an error results trying to run it. What am I doing wrong Error: Msg 530, Level 16, State 1, Line 1 The statement terminated. The maximum recursion 2 has been exhausted before statement completion. WITH AllPNBom ( [Parent Part Number] , [Component Part Number] , ItemId , ParentId , Part_Class , HLevel ) AS ( -- Anchor member definition SELECT [Parent Part Number] , [Component Part Number] , ItemId , ParentId , Part_Class , 0 AS HLevel FROM AllBoms WHERE [Parent Part Number] = '305-0345-001' UNION ALL -- Recursive member definition SELECT e . [Parent Part Number] , e . [Component Part Number] , e . ItemId , e . ParentId , e . Part_Class , apb . HLevel + 1 as HLevel FROM AllBoms e INNER JOIN AllPNBom apb ON e . ParentId = apb . ItemID ) -- Statement that executes the CTE SELECT * from AllPNBom order by HLevel , [Component Part Number] Option ( maxrecursion 2 ) Tag: Month's end

Constantijn Enders

Hi,

I am working on a cube that contains month standings and month totals. This works fine if I select one month.

It goes wrong when I do a year to date selection because it sums up the month standings.

How can I make a cube that does sum up the month total but only returns the last month standings.

With regards,

Constantijn Enders

Re: Month's end

Deepak Puri

Hi Constantijn,

If you configure the "month standings" measure (I don't know its details) with the aggregate function: LastNonEmpty, and you use Aggregate() rather than Sum() in your "year to date" calculation, does that work

http://msdn2.microsoft.com/en-us/library/ms175623.aspx

>>

SQL Server 2005 Books Online

Configuring Measure Properties

Measures have properties that enable you to define how the measures function and to control how the measures appear to users.

...

Property	Definition
AggregateFunction	Determines how measures are aggregated. For more information, see Aggregation Functions.

LastNonEmpty

Semiadditive

Retrieves the value of the last non-empty child member.

>>

Re: Month's end

Mosha Pasumansky

Or, depending on your data, you may want to use more optimal LastChild aggregation function.