Answer Questions

• clint 2 How to eliminate duplicate data

  I have a table with 68 columns. If all the columns hold the same value except for one which is a datetime column I want to delete all but one of the duplicate rows. Preferably the latest one but that is not important. Can someone show me how to accomplish this Assuming that your data isn't too big, you can use a technique like this: drop table removeDups go create table removeDups( column1 int, column2 int, column3 int, column4 int, column5 int, column6 int, column7 int, column8 int, column9 int, datevalue datetime) insert removeDups select 1,1,1,1,1,1,1,1,1,getdate() waitfor delay '00:00:01' insert removeDups select 1,1,1,1,1,1,1,1,1,getdate() waitfor delay '00:00:01' insert removeDups select 1,1,1,1,1,1,1,1,1,getdate() wait ...Show All

• LezWilliams Prior Year YTD Calculation

  Hi all, I am having trouble getting a calculated measure 'Prior year YTD' to calculate properly. I have 2 Time hierarchies, here is the MDX for my YTD calculation and my 'Prior Year' Calculations, they both work fine across both time dimensions. YTD Calculation: Create MEMBER CURRENTCUBE .[Time Calculations].[Time Calculations].[YTD] AS Case When [Time].[LCBO Calendar]. currentmember . level . Ordinal =0 then Aggregate ({ [Time Calculations].[current time] } * PeriodsToDate ([Time].[Fiscal Calendar].[Fiscal Year],[Time].[Fiscal Calendar]. CurrentMember )) else Aggregate ({ [Time Calculations].[current time] } * PeriodsToDate ([Time].[LCBO Calendar].[LCBO Year],[Time].[LCBO Calendar]. CurrentMember )) e ...Show All

• TMF Strange goings-on in BIDS-->Perspectives

  I've just seen the following when opening the "Perspectives" tab of my cube in BIDS:   http://blogs.conchango.com/photos/conchango_bloggers/images/4183/original.aspx   Which doesn't exactly look that good! I tried to paste contents of my .cube file but there was just too much of it and I wasn't able to post successfully! I don't think that matetrs though seeing as I closed down my cube and reopened it and everything was OK.   Any ideas I thought it might be worth reporting anyway!   -Jamie   I have seen this a couple of times too... There didn't seem to be a pattern to explain the error. If this repros for you, please file a bug on http://connect.microsoft.com/SQLSer ...Show All

• JWTK Can't I dynamically read from any file? - Connection Manager does not exist

  I have a Parent package and a child package. I have hard coded the value for the User::FilePath variable in the parent package. I am mapping this variable to the value of the same variable in the Child Package. I created a directory and sql file: "C:\temp\sqlb.sql". I have verified that the path variable value is passed to the child package by using a Script task with a messgbox call. How do I define an execute sql task to execute sql file: @[User::FilePath] & "sqlb.sql". I'm using this expression for the SqlStatementSource property. I have entered the OLEDB server information and specified the SQLSourceType = fileconnection. However I get the error: [Execute SQL Task] Error: Connection manager &qu ...Show All

• Dennis van.der Stelt SQLserver Agent Service Access issue

  Even though I resolve the the issue but still wondering why this happens, here is my story. We use Domain account for SQLServer as well SQLAgent (one account only). This domain account part different Active Directory groups and one Actie Directory group is also part local of BuiltIn\Administrators group. For security we revoke peron from BuiltIn\Administrators and add service account using this statment EXEC master..sp_addsrvrolemember 'Domain\ServiceAccountl', 'sysadmin' After doing all steps whne I bounce the box sqlserver service was ok but sqlagent service didn't start and Error was "Account doesn't the access". When I open the account access I see third buton "Through group membership" I change ...Show All

• Synced buckets or quartile

  Hi all, Dreaded moment has come:) I need to produce a report against MSAS 2005 with quartiles buckets, meaning that besides actual value of the measure, it needs to display following: Value 1st 2nd 3rd 4th X Min - Max Min - Max Min - Max Min - Max ------------------------------------------------------------------- 80 85 - 95 75 - 85 55 - 75 12 - 55 where 1st is range of values of top 25% performing entities and so forth. It's supposed to be sliceable by any hierarchy in the cube. I tried to use TopPercent 25%, but never could get it work in calculated member. Any idea, comment, reference highly appreciated. Thanks the measure is not additive. ...Show All

• Paul Oulton SSIS Debugging Errors

  Hi All, I am having a couple of problems with an SSIS package. The first is whenever I add a Data Viewer to one of my data flow tasks it crashes Visual Studio. This happens every single time. I have tried rebooting, but that didn't help. The second error I am getting is whenever I call ComponentMetaData.GetErrorDescription(Row.ErrorCode) from within a data flow script task it generates a "Catastrophic Failure" and indicates that it occurred when calling the GetErrorDescription method. I am not sure if something is corrupted or what the issue is. Does anyone have any insight Thanks, Justin if you can consistently reproduce your issues, then i suggest that you submit a bug report here: ...Show All

• MicMit AMO: Hanging on Partition.Update

  I am using AMO to manage partitions. I am trying to create a partition then use the Update method to create the new partition.. snippet of code below:    65    //create the new partition    66                      Partition newPartition = mg.Partitions.Add(partitionName, partitionName);    67                      newPartition.StorageMode = StorageMode .Molap;    68                      newPartition.Source = new Query ...Show All

• redhot2006 Job notification

  Hi! I had the following problem: I had a job that should run at last December 31th, but the server machine was shut down the friday before. Is there anyway I could be notified that this job didn't run because the machine was shut down Thank you! Hi, there is no built-in functionality for this, you would have to add an additional step writing to a logging table and check this peridiocally and send out mails (or whatever you want to do for notifications) or send out mails at successfull job execution and monitor this for missing mails (which will indicate the failed execution). HTH, Jens K. Suessmeyer. --- http://www.sqlserver2005.de --- ...Show All

• knvdssr How to display ranges in output?

  Hi All, I have the following table: Name Age A 15 B 18 C 22 D 27 E 33 F 42 I need to have the following output: Range Count 0-10 0 11-20 2 21-30 2 31-40 1 41-50 1 Could anyone let me know how to achieve this in SQL Server 2005 Thanks, Varun Hi, there is a more generic way, like: SELECT COUNT (*), CAST (( age / 10 )* 10 AS VARCHAR ( 5 )) + '-' + CAST (( age / 10 + 1 )* 10 AS VARCHAR ( 5 )) FROM #t GROUP BY CAST (( age / 10 )* 10 AS VARCHAR ( 5 )) + '-' + CAST (( age / 10 + 1 )* 10 AS VARCHAR ( 5 )) HTH, jens Suessmeyer. --- http://www.sqlserver2005.de --- ...Show All

• Subhas Merge Replication-Apply BCP files

  How can i apply a BCP files generate by Distributor in the Suscribers. I don’t know wich parameters BCP.EXE need. Thanks Can you explain why you need to manually apply them Is the merge agent not applying them for you I only need to know how can apply manually the BCP files on the susbcribers. Any body knows what parameters i have to use to do this. Thanks manually applying the bcp files is not recommended. If you're having problems, then let us know what they so we can walk you through a solution. What part is slow, applying the snapshot to the subscribers Are these large tables Are you using filtering, and ...Show All

• Rattlerr DTS drawbacks

  I am trying to compile a case of why DTS packages need to be migrated to SSIS packages. Is there any document, whitepaper which can provide me drawbacks in DTS Thanks, Vijay. Vijay Thirugnanam wrote: I am aware of new features in SSIS like control-flow, data-flow demarcation, new transforms like SCD etc. But knowing some drawbacks in DTS like performance, scalability, support for XML can help. If there is any inputs on specific drawbacks in DTS as compared to SSIS, it will be helpful. SSIS performs and scales better than DTS by an order of magnitude. As opposed to SSIS, DTS very much relies on the staging of data in order to transform it into something suitable for populating a dat ...Show All

• Blair Allen Stark random error 513 "insert or update conflicts with previous create rule"

  Hello, I use ODBC driver to perform SQLServer commands from C/C++ application. An "INSERT INTO <table> (<column>) VALUES (NULL)" command has a random behavior in a SQL2000 Server running on WindowsXP. The <column> in this command has 2 definitions about the NULL value : - the NULL is accepted in the table definition, with <column> ut_oui_non NULL". - the NULL is rejected in the type definition, with EXEC sp_addtype ut_oui_non, 'char(1)', 'NOT NULL' and a rule to check values with '0' or '1' 1/ The column definition in any explorer show the NULL from table deffinition 2/ The "INSERT INTO" is completed in SQL Query tool, used on Windows2000 and WindowsXP computers, connect ...Show All

• Kailai Linked Server with Oracle

  I installed the Oracle client on my computer, and I can connect to oracle databases using SqlPlus, however when I try setting up the linked server I get the following error after I try executing a query. OLE DB provider 'MSDAORA' reported an error.  [OLE/DB provider returned message: ORA-12560: TNS:protocol adapter error ] OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize returned 0x80004005:   ]. This looks more like a protocol adapter error. This indicates that Oracle client does not know what instance to connect to or what TNS alias to use. Try fixing the tnsname.ora file so that it points to the correct instance me to getting the same problem so u got the s ...Show All

• Vijay Chegu Load images into Sql Server 2005

  Hi There I have not had much luck finding info in BOL for tsql or SSIS that tells one how to load images on the file system into sql server 2005. All i have really been able to find is that IMAGE data type will not be used in future and that one should use a varbinary(max) data type. I am thinking of using a for each file loop in SSIS, but then how do i load the images (.tif) into a sql server database table Perhaps i need to use a sql task with the filepath , or an active x script. Anyway if anyone knows how i can load images from the file system into sql server 2005, please let me know. Thanx Hi Donald Would a for each file loop not work better, as a source file or table is not re ...Show All