Can't Delete Maintenance Report Files (txt) in SQL/2005

SQL Server Database Engine

Index ‹ SQL Server
- Archive
  - ISV
  - Office
  - Visual C#
  - Windows Live
  - IE
  - Smart Device
  - Game
  - VS 2008
  - Architecture
  - Windows Forms
  - Dotnet
  - Visual Studio
  - SharePoint
  - VS Express
  - Windows Vista
  - SQL Server
  - VC++
  - VFP
  - VB

- Previous
  - 1
    - Execute a stored procedure which calls SSIS packages using a proxy Account for Non sysadmins in sql server 2005 Hi all, I have a problem while i create a proxy account.The situation is like this...There is a user who has an login in to the server.He has a stored procedure which calls some on the SSIS packages and XP_cmdshell...so this stored procedure basically load some data in to the tables .So for the login in order to execute the stored procedure as he is not a Sys admin I have created a proxy account in my account as Iam an SA and then in the proxies and in principals I selected his login name and this way I have created a credential and a proxy account. Now the problem is if he logins with his id and password and try to execute the stored procedure it gives an error message Server: Msg 15153, Level 16, State 1, Procedure xp_cmdshell, Line 1 The xp_cmdshell proxy account information cannot be retrieved or is invalid. Verify that the '##xp_cmdshell_proxy_account##' credential exists and contains valid information. ....so this mean the login is not able to see the proxy account.So what I did is I created a job and then in the job owner tab I have selected his login and then created a step with the type operating system (CmdExec) as I need to just execute the stored procedure and used the proxy account that I have created. so I gave the command -- exec <stored procedure> --. But this job fails and gives the error message as [298] SQLServer Error: 536, Invalid length parameter passed to the SUBSTRING function. [SQLSTATE 42000].... So now ....first My question is am I doing in a right way....if its right then why Iam not able to execute the stored procedure. If there is any other way through which I can execute the stored procedure using a proxy account for the logins who are not sys admins....please do let me know..... Thanks Raja.V Tag: Can't Delete Maintenance Report Files (txt) in SQL/2005
  - 2
    - using SQl stament, need help hi, 1 have table i d V1 V2 1 50 20 2 60 30 3 70 40 4 45 25 5 55 35 6 65 15 7 75 35 8 90 60 9 45 32 so, i want to query with my purpose i have new table i d V1 V2 1 -- -- 2 -- -- 3 70 20 //70 is max of 5 records from 1 to 5 //20 is min of 5 records from 1 to 5 4 70 15 //70 is max of 5 records from 2to 6 //15 is min of 5 records from 2to 6 5 75 15 6 90 15 7 90 15 8 -- -- 9 -- -- how can i solve it using sql stament thanks Tag: Can't Delete Maintenance Report Files (txt) in SQL/2005
  - 3
    - Named transactions? If a sproc is written to be executed both independently and as a nested sproc when both the inner and outer sprocs are transactions, is it a good practice to use named trans Tha advantage is that inner named rollback statement do not rollback the outer tran. Maintains encapsulation that is so dear to procedural code. One disadvantage can be a naming nightmare, because it seems that all the tran names, the nested and outer ones are, kind of, "in scope" (at least the system is aware of them especially when from the outer tran) and there is not a sophisticated naming system with namespaces etc, or the editing tool to warn against conflicts at design time. So what's a girl to do Carl Tag: Can't Delete Maintenance Report Files (txt) in SQL/2005
  - 4
    - DTS Package Scheduled Job Result Issue I am using SQL Server 2000. I created Package1, Package2, and Package3. If I ran from scheduled job Package2 and it failed, the result in Job History is Failed. If I ran from scheduled job Package1 that runs Package2 and then Package3 and Package2 failed, the result in Job History on Package1 is Successful. Why How can I have the result Failed so I can get the failure notification. Thanks, DanYeung Tag: Can't Delete Maintenance Report Files (txt) in SQL/2005
  - 5
    - Groupings in Matrix Layout? Hello, I am trying to come up with a Matrix report using RS 2005 that looks like this one below. I have row data: question, columns data: Ethnicity and Gender, detail data: Gender average response and Ethnicity average reponse. I utlilized Report Wizard to create a report in a Matrix format , however I wasn't able to have Gender and Ethnicity column on the same line. The report wizard groups columns so Gender is a subset of Ethnicity or Ethnicity is a subset of Gender. How can is solve this issuee so that Gender and Ethnicity data is presented in a Matrix format and Gender and Ethnicity are not part of each other. Thank you! F M Asian Multi-cultural Non-resident Unknown White Black Hispanic Native American a. Worked on a paper or project that required integrating ideas or information from various sources 3.95 3.99 3.54 4.50 3.28 4.20 4.18 4.04 3.92 3.90 b. Used library resources 4.26 4.09 4.12 4.33 4.10 4.33 4.26 4.22 4.10 4.10 c. Prepared multiple drafts of a paper or assignment before turning it in 3.97 3.76 3.80 4.50 3.58 3.86 3.95 4.09 4.00 4.00 Tag: Can't Delete Maintenance Report Files (txt) in SQL/2005
  - 6
    - Check remotely if Reporting Services exist is there a way to check the reporting services remotely.. using the reporting services url Tag: Can't Delete Maintenance Report Files (txt) in SQL/2005
  - 7
    - When you create a cube, does it have constant connection to the DB after the cube is created If it's not a constant connection, how often the cube gets updated from the database Tag: Can't Delete Maintenance Report Files (txt) in SQL/2005
  - 8
    - Executing an SSIS package from TSQL without using xp_cmdshell? How can I execute an SSIS package from TSQL without using xp_cmdshell I have a web-app which calls some SQL which executes my SSIS package (a DTSX file, but stored in the server). But the security policy for my application won't permit me use to xp_cmdshell. I want to do this:- DECLARE @returncode int EXEC @returncode = xp_cmdshell 'dtexec /sq pkgOne"' Is there another way for executing a Package without going to the command line (e.g. is there some other system stored proc) Thanks Tag: Can't Delete Maintenance Report Files (txt) in SQL/2005
  - 9
    - left or right joins Hi All, This may be a stupid question. But I just want to make sure i'm going in right direction. I wrote the following query. My purpose is to retrieve all the members who submitted loan applications. I just want to make sure my query is right Can anyone veryfy this query Do I have to use loanApplication table first instead of members. Also Do I need to use right outer joins instead of left outer joins Code Block SELECT Member.CUMemberId, LoanApplication.SubmittedOn, Member.LastName, Member.FirstName, Member.MiddleName, LoanApplication.Amount, LoanApplication.Decision, LoanApplication.Term, Rate.InterestRate, LoanApplication.Status, Member.CuStatus FROM Member INNER JOIN MemberLogon ON Member.Id = MemberLogon.MemberFK INNER JOIN LoanApplication ON Member.LastLoanApplicationFK = LoanApplication.Id AND Member.Id = LoanApplication.MemberFK LEFT OUTER JOIN Account ON Member.Id = Account.MemberFK AND LoanApplication.LoanFK = Account.Id LEFT OUTER JOIN Rate ON LoanApplication.RateFK = Rate.Id WHERE (LoanApplication.Status = 'Submitted') Thanks Tag: Can't Delete Maintenance Report Files (txt) in SQL/2005
  - 10
    - Filters for dataset from Parameter - How to ignore if the parameter is NULL ? Hi, I am starting to use reporting services and I created a report that takes 4 parameters for a Filter on the dataset. The idea is the create snapshot nightly and then display the report filtered based on parameters to users. I'd like that the filter be ignored for a parameter if the parameter is passed as NULL, Right now I defined it this way : Left =Fields!RegionCode.Value Operator = Right =IIF(IsNothing(Parameters!RegionCode.Value),Fields!RegionCode.Value,Parameters!RegionCode.Value) I do this for the 4 parameters that are passed, if they are null, make then equals to the field. I was wondering if there is a way to ignore the whole parameter all together, I would guess it'll be faster to execute. Thanks Tag: Can't Delete Maintenance Report Files (txt) in SQL/2005
  - 11
    - SINGLE TABLE INNER JOIN I have the following entries in a single table CompanyId ServiceId HALC-IRN-001 5 HALC-IRN-001 6 SDFS-PAK-001 6 TEST-IRN-001 5 TEST-IRN-001 6 I am trying to get those companyIds that have serviceId 5 and 6 both at the same time. I just cant seem to figure out how I will get this result. The result should have companies HALC-IRN-001 and TEST-IRN-001 but not SDFS-PAK-001. Ofcourse in the real time scenario, there will be more companies and a variable number of services that will have to be checked. Tag: Can't Delete Maintenance Report Files (txt) in SQL/2005
  - 12
    - AS hangs when running a complex mdx querie Hi all, I am having a problem with Analysis Services. Currently we are developing an analysis application that enables a user to view up to 5 year survival for patients. I have written the following MDX to perform the calculated measure: Code Snippet /* The CALCULATE command controls the aggregation of leaf cells in the cube. If the CALCULATE command is deleted or modified, the data within the cube is affected. You should edit this command only if you manually specify how the cube is aggregated. */ CALCULATE; //************************************************************************************** //************************************************************************************** //************************************************************************************** // SURVIVAL // Censor flag = 1 if censored, 0 if noncensored (or true failure), // NULL if there is data error (death date <= diagnosis date) CREATE MEMBER CURRENTCUBE.[MEASURES].[N Noncensored Deaths] AS [Measures].[N Total Deaths] - [Measures].[N Censored Deaths], FORMAT_STRING = "#", VISIBLE = 0 ; CREATE MEMBER CURRENTCUBE.[MEASURES].[Number at risk] AS 0, VISIBLE = 1; SCOPE ([Measures].[Number at risk]); ([Survival Time].Members(1): Null) = ( Case /* At the first survival time (defined as Null, i.e. 1st row with "Blank" time, when there is no previous month; PrevMember is empty): Number at risk is simply total number of valid death dates (both censored and uncensored) in the dataset, which is the total number of patients with censor flag values of either 0 or 1 (not null), i.e. Number at risk at t == 0 (date of diagnosis). NOTE: Death dates are right-censored at 01 June 2006 - people who haven't died by this date are censored and assigned a death date of June 1, 2006. The censor flag is set to null only when death date is same as or EARLIER than date of diagnosis, i.e. excludes patients diagnosed during autopsy. */ When IsEmpty( [Survival Time].CurrentMember.PrevMember ) Then ( Root( [Survival Time] ), [N Total Deaths] ) /* This is the survival censor count at the top ("root") level of survival time, */ Else // At subsequent survival times ( t > 0, censor <> null ), Number at risk is: ( // Number at risk from previous month, // minus total number of deaths (both censored and uncensored) // during current month ( [Survival Time].CurrentMember.PrevMember, [Number at risk] ) - [N Total Deaths] ) End ); END SCOPE; //----------------------------------------------------------------------------------- CREATE MEMBER CURRENTCUBE.[MEASURES].[Survival] AS 100, FORMAT_STRING = '0.0', VISIBLE = 1; SCOPE ([Measures].[Survival]); ([Survival Time].Members(1): Null) = ( Case // At the first survival time (defined as Null, i.e. 1st row with "Blank" time): When IsEmpty( [Survival Time].CurrentMember.PrevMember ) Then ( ( ( ( Root([Survival Time]),[N Total Deaths] ) - [N Total Deaths] ) / ( (Root([Survival Time]), [N Total Deaths] ) - ([Survival Time],[N Censored Deaths]) ) )*100 ) Else // At subsequent survival times ( t > 0, censor <> null ): ( Case // If everyone's dead (at right end of time(x) axis) When [Number at risk] = 0 // Then use survival from previous month (PrevMember) Then "Censored" //( [Survival Time].CurrentMember.PrevMember, [Survival] ) Else ( ( // Number at risk from previous month, minus true failures (noncensored deaths), // divided by Number at risk from previous month ... ( ( ([Survival Time].CurrentMember.PrevMember, [Number at risk] ) - ( [Survival Time].CurrentMember,[N Noncensored Deaths]) ) / ([Survival Time].CurrentMember.PrevMember, [Number at risk] ) ) // then multiplied by survival from previous month * ([Survival Time].CurrentMember.PrevMember, [Survival]) ) ) End ) End ); END SCOPE; When I put the Calculated Measure then the survival time dimension in the x axis in the pivot table, it works fine. But when I put a dimension in the filter and choose multiple items (not all and not only one) it tries to build and run the query but seems to hang. Has anyone else experienced this sort of thing before Would it be something to do with the MDX I have been struggling with this for a while and not been able to work it out. ANY suggestions would be fantastic. If you need further information, let me know. Steve Tag: Can't Delete Maintenance Report Files (txt) in SQL/2005
  - 13
    - stored procedure based custom conflict resolver truncates data I created a stored procedure based custom conflict resolver in SQL 2005, I return the winning result set and also save that result set to a test table to compare the values. The values saved to the test table are correct but some of the values saved as the conflict winter are truncated. Example a char(3) filed is updated at the subscriber as ��111�� and updated at the publisher as ��222��, in my custom conflict resolver if I use the value from the subscriber the conflict resolver updates the field as ��11 ��, if I use the publisher value the conflict resolver updates the field as ��22 ��. Now the same records is saved to the test table correctly as either ��111�� or ��222�� depending on the logic I used. So the result set has the correct values, its after the custom conflict resolver is called where the values is somehow truncated. Has anybody run into this before and what steps can I take to avoid this. Thank you, Pauly C Tag: Can't Delete Maintenance Report Files (txt) in SQL/2005
  - 14
    - backup job sometimes fails Hi We have a nightly job on a production server which backs up all databases (about 90 on the server) Sometimes it'll only backup some of them, yet it reports success. Our proc to manage the process is below: Any ideas on what we need to do to make it reliable ----------------------------------------------------- DECLARE @DB_Name varchar(32) DECLARE @Backup_Path varchar(255) DECLARE @Backup_Name varchar(255) DECLARE DB_Cursor CURSOR FOR SELECT NAME FROM sysdatabases OPEN DB_Cursor FETCH NEXT FROM DB_Cursor INTO @DB_Name WHILE @@FETCH_STATUS = 0 BEGIN IF @DB_Name <> 'tempdb' AND @DB_Name <> 'model' BEGIN print '--------------------------------<< ' + @db_name + ' >>--------------------------------' SET @Backup_Path = N'd:\sql2005backups\nightly\' + @DB_Name + 'Daily' + '.bak' SET @Backup_Name = @DB_Name + N' backup' BACKUP DATABASE @DB_Name TO DISK = @Backup_Path WITH INIT print '' END FETCH NEXT FROM DB_Cursor INTO @DB_Name END Print "Finished backing up the databases" CLOSE DB_cursor DEALLOCATE DB_cursor Tag: Can't Delete Maintenance Report Files (txt) in SQL/2005
  - 15
    - Query to Oracle via linked server hangs Basic description: We have developed a solution that sends data from SQL Server to an Oracle server as the result of a stored procedure called by a job that runs every minute. While this development worked fine in our test environment, after moving it to production it ran successfully the first minute, but the second minute the stored procedure hung, and the process could not be killed. In order to stop the process I had to stop both the SQL Agent and the MSDTC services. Our SQL Server box: SQL Server 2000 Standard Edition SP4 Windows 2003 Server R2 SP1 Our Oracle box: Test: Oracle 9.2.0.6 Production: Oracle 9.2.0.4 To setup the SQL box, I did the following: 1) Install Oracle Client Tools version 10.2.0.1 2) Restart Server 3) Modify the registry as follows: [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOCI] "OracleXaLib"="oraclient10.dll" "OracleSqlLib"="orasql10.dll" "OracleOciLib"="oci.dll" 4) Modified the PATH variable so that all references to SQL Server appear in front of Oracle path references 5) Added the linked server via sp_addlinkedserver '<tns name>','Oracle','MSDAORA','<tns name>' 6) Added linked server logins via sp_addlinkedsrvlogin '<tns name>','False','<SQL user>','<Oracle User name>','<password on oracle>' 7) Changed the registry for MSDTC to match this: [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC] "AllowOnlySecureRpcCalls"=dword:00000000 "FallbackToUnsecureRPCIfNecessary"=dword:00000001 "TurnOffRpcSecurity"=dword:00000001 [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\Security] "NetworkDtcAccess"=dword:00000001 "NetworkDtcAccessAdmin"=dword:00000001 "NetworkDtcAccessClients"=dword:00000001 "NetworkDtcAccessTransactions"=dword:00000001 "NetworkDtcAccessTip"=dword:00000001 "XaTransactions"=dword:00000001 "DomainControllerState"=dword:00000000 "AccountName"="NT Authority\\NetworkService" "NetworkDtcAccessOutbound"=dword:00000001 "NetworkDtcAccessInbound"=dword:00000001 8) Stopped and restarted services in the following order: 1) MSDTC Stop 2) SQL Server Stop 3) MSDTC Start 4) SQL Server Start The stored procedure: In a single transaction, the stored procedure compares a production table against a logging table. If a record exists in the production table that is not in the logging table, a record is inserted into logging table, and a record sent to Oracle via an INSERT INTO OPENQUERY('INSTANCE','SELECT Column1, column2, column3,... FROM SCHEMA.TABLE') SELECT column1,column2,column FROM SQLTable This stored procedure has worked just fine for us in test, to either the test or production Oracle boxes, but it now fails, and hangs, in production to either the test or production Oracle boxes. Additionally, I can run the following query via Query Analyzer from our test box to both the test Oracle and production Oracle and it runs successfully (this is NOT used in our stored procedure code, but is presented here as an indication that I think there is something wrong with the settings on our production SQL box): SELECT * FROM OPENQUERY('INSTANCE','SELECT * FROM SCHEMA.TABLE') When I run this same query via Query Analyzer on our Production SQL box, to either the test Oracle or production Oracle, it hangs, and I have to kill the process, and restart the MSDTC service. Other queries that hang are: SELECT * FROM SERVER..SCHEMA.TABLE Additionally, I noticed that when I used this method to kill the process I would see errors like the following in the Application Event Log on the SQL box: The XA Transaction Manager attempted to perform recovery with the XA resource manager. The XA resource manager reported that recovery was unsuccessful. DSN = MTxOCI.Dll. Since I figured this was an aborted transaction still residing in the MSDTC log file, I would stop the MSDTC service, delete the MSDTC log file, reset the MSDTC log, and then restart the MSDTC service in order to prevent this error from occurring. Not ALL queries from the production SQL box to production and test Oracle boxes fail. I can get results returned for this query: SELECT COLUMN1, COLUMN2 FROM SERVER..SCHEMA.TABLE I've been scouring the internet for about a week now, and I've run out of ideas on what to check on the production SQL box. Any suggestions would be greatly appreciated. Tim Tag: Can't Delete Maintenance Report Files (txt) in SQL/2005
  - 16
    - Change data type of variable Is there anyway to change the data type of a variable while in an expression My problem is I am trying to compare a variable w/ a string data type to a variable w/ an object data type. I would change the data type of the variable from object to string but if I do that my sql task fails when it tries to write a value to that variable. The variable w/ the object data type is the result of an openquery sql stmnt. So I guess there are two ways around my problem. 1. Change data type of variable while in an expression..ie flow constriant or 2. Change data type of vraiable from object to string and still get the openquery result to work. Any help Thanks !! Tag: Can't Delete Maintenance Report Files (txt) in SQL/2005
- Next
  - 1
    - Multiple Matrix report Hi I'm trying to create a report with a multi-matrix setup like below. I have an account table linked to a charge table and a Item table with different Type information. Does anybody know what the basic theory is to go about setting this up Charge Information Type Information ----------------------------- -------------------------- Account# Charge/Unit Handling/Unit Type Type1 --------- ------------ ------------- --------- --------1111 1.00 6.00 Auto 10 Manual 5 Electronic 6 Tag: Can't Delete Maintenance Report Files (txt) in SQL/2005
  - 2
    - Retrieving Hierarchy Captions from a Cellset I'm trying to retrieve the hierarchy caption(s) for a given axis in a cellset, but whenever I try to retrieve this: cellset.Axes(0).Set.Hierarchies(0).Name I get an exception like this: System.ArgumentException was unhandled Message="The '[Date].[Calendar Quarter]' object was not found.\r\nParameter name: uniqueName" Source="Microsoft.AnalysisServices.AdomdClient" ParamName="uniqueName" StackTrace: at Microsoft.AnalysisServices.AdomdClient.CubeDef.InternalGetSchemaObject(SchemaObjectType schemaObjectType, String uniqueName, Boolean retryUniqueName) at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.GetObjectData(SchemaObjectType schemaObjectType, String cubeName, String uniqueName) at Microsoft.AnalysisServices.AdomdClient.AdomdUtils.PopulateSymetry(IAdomdBaseObject iBaseObject) at Microsoft.AnalysisServices.AdomdClient.Hierarchy.get_Name() I've tried setting the MEMBER_NAME dimension property but that doesn't work for this case. Is there another dimension property that I could use Thanks, Terry Tag: Can't Delete Maintenance Report Files (txt) in SQL/2005
  - 3
    - Adding columns to a Matrix report that don't belong to the matrix columns groups Can we do this Adding more columns in a matrix report that don��t belong to the columns drilldown dimensions�� That is, for example, having the following report: Product Family Product Country City Number of units sold Then I would add some ratios, that is, Units Sold/Months (sold per month) and other that is the average for Product Family (Units Sold/Number of Product Family), for putting an example�� some columns should be precalculated prior to the report so do not get into it, the real problem I don��t see how to solve is adding one or two columns for showing these calculated column that doesn��t depend on the column groups but they do for the rows groups�� Any guidance on that The only way I am seeing by now is to set it as two different reports, and that is not what my client wants�� Many thanks, Jose Tag: Can't Delete Maintenance Report Files (txt) in SQL/2005
  - 4
    - DB2 Connect and SSIS | Error in OLE DB Source Task Hi All, I am trying to connect to DB2 database via OLE DB connection manager in SSIS. But when I enter the SQL Query and press OK it gives following error "Error at Data Flow Task - Header Load [OlE DB Source - Header_Load[1]]": An OLE DB Error has occured. Error Code: 0x80040E21 Additional Information: Exception from HRESULT: 0xC0202009(Microsoft.SqlServer.DTSPipelineWrap)" I followed following steps: - 1. I created OLE DB provider and tested the connection, it was successful(with give username and password) 2. Created query in Build query as following and tried executing it. It worked! Query used was SELECT SRC_ID, ORG_ID FROM DB123.DEAL_HEADER 3. But when, in OLE DB Source Provider Task, when I press preview, It thorws the above error! Kindly let me know, Because I am stuck at that point. Thanks Sid Tag: Can't Delete Maintenance Report Files (txt) in SQL/2005
  - 5
    - no database available tracdat application has been running for 2yrs plus and today when we tried to get in we get the 'no database available' message. We have other sqlserver databases on the same server and we have not trouble accessing them. I was able to run a query against the database. What causes the 'no database available message ' Tag: Can't Delete Maintenance Report Files (txt) in SQL/2005
  - 6
    - Process Add by AMO Hi, I want to write a programm which adds entries to an sql table and then process the dimension with processadd to only add this single row. I have checked the samples and internet forums - but could not find out howto do.. anyhow I miss the link between a querybinding and the process command of the dimension... I thought it would be something like... Code Snippet /* connect to lap server */ olapClient.Connect( "DataSource=" + OLAPServerName); /* get the Database */ Database olapDB = olapClient.Databases.GetByName(DatabaseName); /* get the Dimension */ Dimension olapDim = olapDB.Dimensions.GetByName(DimensionName); /* process the dimension and add the items i have created before */ olapDim.Process( ProcessType .ProcessAdd, new QueryBinding (DataSourceName, "select * from dbo.Slot where Slot='" + ResultID + "'" )); but this only raises an error (" Errors in the metadata manager. The object reference is not valid. It does not match the structure of the metadata class hierarchy.") How is this done correctly I want to use AMO to not write the xmla command statically - if there are further changes - but the dimension is build up to 100% of the single table dbo.Slot. An example or a link would be helpfull. HANNES Tag: Can't Delete Maintenance Report Files (txt) in SQL/2005
  - 7
    - Report server Windows server is not running The Report Server Windows service 'ReportServer$RCREPORTSERVER' is not running. The service must be running to use Report Server. (rsReportServerServiceUnavailable I get this error when i tried to access report manager today. What causes this to happen because this is the second time in 6 months this has happened. The first time , i restarted the server, should i go that route again Or is there something else i should be doing. Tag: Can't Delete Maintenance Report Files (txt) in SQL/2005
  - 8
    - Slow Queries: Big Dimension on Axis and Measure Using Root() Here's an odd perfomance problem related to Root(). I have two queries that are very quick. Query A: SELECT {[Measures].[Sales]} ON 0, NON EMPTY CROSSJOIN ([Product].[Product Code]. Members ,[Product].[Product Name]. Members ) on 1 FROM [Sales Activity for Projections] WHERE ([Time].[Time F].[Year].[Y 2005], [Product].[Product Type Name].[Special Products], [Customer].[Customer Code].[All]) [returns 25000 rows] and Query B: SELECT {[Measures].[Sales]} ON 0, NON EMPTY CROSSJOIN ([Product].[Product Code]. Members ,[Product].[Product Name]. Members ) on 1 FROM [Sales Activity for Projections] WHERE ([Time].[Time F].[Year].[Y 2005], [Product].[Product Type Name].[Special Products], [Customer].[Customer Code].[HOPA]) [returns 3000 rows]. Now I create another query below that conceptually should perform the same as either of the above (or maybe the sum execution time of both). However, this query below never returns. All I am trying to do is get the sales by product to all customers other than customer code [HOPA]. The effective result should be Query A - Query B. Is this not a valid way to use Root() Is there a better way to create the "Sales (Other Customers)" functionality SELECT {[Measures].[Sales (Other Customers)]} ON 0, NON EMPTY CROSSJOIN ([Product].[Product Code]. Members ,[Product].[Product Name]. Members ) on 1 FROM [Sales Activity for Projections] WHERE ([Time].[Time F].[Year].[Y 2005], [Product].[Product Type Name].[Special Products], [Customer].[Customer Code].[HOPA]) Note that the following calculated member is used above: CREATE MEMBER CURRENTCUBE.[MEASURES].[Sales (Other Customers)] AS + (Root([Customer]), [Measures].[Sales]) - ([Measures].[Sales]), VISIBLE = 1 ; Tag: Can't Delete Maintenance Report Files (txt) in SQL/2005
  - 9
    - Mirroring over internet Can SQL Server do mirroring over Https protocol over internet Tag: Can't Delete Maintenance Report Files (txt) in SQL/2005
  - 10
    - Reportviewer DateParameters- javascript Object required? asp.net Content page? I've got a report that works great from vs.net, however when I deploy and attempt to run from reportviewer over asp.net, the date parameters do not take. they reset to default and don't impact that report. Also the calendar icon, produces a javascript error "OBJECT REQUIRED" on the below line. function GetObjectPosition(obj) { var totalTop = 0; var totalLeft = 0; while (obj != document.body) { // Add up the position totalTop += obj.offsetTop; totalLeft += obj.offsetLeft; //ERROR HAPPENING HERE. // Prepare for next iteration obj = obj.offsetParent; My reportviewer is inside a content page. My client is ie 7.0 thanks for any help or information. Tag: Can't Delete Maintenance Report Files (txt) in SQL/2005
  - 11
    - How do I get bold and normal formatted text in a single field? In SSRS I am trying to get a textbox value to hold text with a mixture of formatting, along the lines of "name (country)" where the "name" part is bold and the "(country)" is normally formated. Can anyone help Thanks in advance. Tag: Can't Delete Maintenance Report Files (txt) in SQL/2005
  - 12
    - Display Dimensions in Folders Hello, I created a cube, and when I show the dimensions using the PivotTable control and Excel 2007, I would like to be able to show the dimensions groupped in Folder as we can do with attributes, and measures. Does SQL Server 2005 analysis services support that or some suggest how could i do it I hope you can help me.... Thank you! Tag: Can't Delete Maintenance Report Files (txt) in SQL/2005
  - 13
    - "Select all" as default for a multi-value parameter which source is non-query Hi, I have report parameter and its values are static lik (ABC, DEF, GHI ) etc. I want to select all as a default for this parameter. How I can do this Tag: Can't Delete Maintenance Report Files (txt) in SQL/2005
  - 14
    - SSIS error - Data Flow Task LocaleID 0 not installed on this system I get the following error when trying to import (have tried flat files, Excel & Access). I've searched but have not found a resolution. Any help is appreciated. Jeff Validating (Error) Messages Error 0xc00470b6: Data Flow Task: The LocaleID 0 is not installed on this system. (SQL Server Import and Export Wizard) Error 0xc004706b: Data Flow Task: "component "Source - Test1" (1)" failed validation and returned validation status "VS_ISBROKEN". (SQL Server Import and Export Wizard) Error 0xc004700c: Data Flow Task: One or more component failed validation. (SQL Server Import and Export Wizard) Error 0xc0024107: Data Flow Task: There were errors during task validation. (SQL Server Import and Export Wizard) Tag: Can't Delete Maintenance Report Files (txt) in SQL/2005
  - 15
    - How to get the database name dynamically? I need an sql statement that dynamically matches a database name in a stored proc. Here is my attempt Select Table_name From Information_schema . Tables Where Table_type = 'BASE TABLE' and Objectproperty ( Object_id ( Table_name ), 'IsMsShipped' ) = 0 and table_name like 'Item%' and (DATABASENAME = ) Can someone help me out Thanks. Tag: Can't Delete Maintenance Report Files (txt) in SQL/2005
  - 16
    - Export to Excel Custom Format Hi, I have a problem when exporting a report to Excel. The problem is with the custom formatting. The report has a field named amount with its format property = C (on the properties window of the textbox in the report designer). When the user exports the report everything seems ok, calculations and so on... but the problem is when from another workbook a cell makes a reference to the cell amount of the exported report. The exported report, has this format [$-1010409]$#,##0.00;($#,##0.00) on the amount cell. In fact every format type of the report designer, begins with [$-1010409]. To reproduce this error: Make a simple rdl with a textbox format C. Export it to excel. Create a new workbook and make a cell reference to the exported report formated textbox cell (='\\Computer\Folder[ExportedReport.xls]Sheet1'!$E$15). Close the exported report and the new workbook, open the new workbook (not the exported one) and update the reference. Results in a #Ref error. Tnx of your time and effort. Sorry for my bad english. G Tag: Can't Delete Maintenance Report Files (txt) in SQL/2005

TDMcCallie

I have several maintenance plans setup and working in SQL/2005 (sp2 9.0.3042) on several servers; however, the .txt maint plan output files will not delete on any of the servers. The backups run fine, the old database and transaction log backup files delete fine (.bak and .trn); but, the .txt files won't go away unless I delete them by hand. I tried copy/paste the scripted command (xp_delete_file) and it runs with no errors but does not delete the old .txt files. ( This server was upgraded from SQL/2000 -

EXECUTE master.dbo.xp_delete_file 1,N'E:\Program Files\Microsoft SQL Server\MSSQL\LOG\',N'txt',N'2007-07-09T10:59:38'

)

I do NOT specify a period (.) in the file extension dialog box. Any ideas before I setup a .cmd batch file and schedule it instead

Thanks in Advance,

DeWayne

Re: Can't Delete Maintenance Report Files (txt) in SQL/2005

Deepak Rangarajan

did you create a maintenance clean up task and specify the .txt file extension to delete old text files but xp_delete_file is corresponding T_SQL of the cleanup task............if you hevent created the cleanup task just try it out and get back

Re: Can't Delete Maintenance Report Files (txt) in SQL/2005

TDMcCallie

Yes! That's where I got the xp_delete_file script that I quoted in my original post...as it does not delete any .txt files.

Thanks

Re: Can't Delete Maintenance Report Files (txt) in SQL/2005

rmiao

Same problem here. Is there any hotfix to address this issue

Re: Can't Delete Maintenance Report Files (txt) in SQL/2005

Davve

Same here - I've tried on several SQL 2005 servers and none of them delete the .txt files. Any ideas

Re: Can't Delete Maintenance Report Files (txt) in SQL/2005

ColSchmoll

Same for me, anyone from MS like to comment on this

Re: Can't Delete Maintenance Report Files (txt) in SQL/2005

Steve Wolford

There is a support article on the subject, http://support.microsoft.com/kb/938085. The problem appears to be the first line of text in the report files. If it says "NEW COMPONENT OUTPUT", then xp_delete_file will not delete it. The stated workaround is to remove this text. That does work, but it's probably simpler to just manually delete the file. Hope a fix comes out soon.