Answer Questions

• Dietz Insert / Update in master table and also save a history of changed records : Using data flow/simple sql queries

  Hi, My scenario: I have a master securities table which has 7 fields. As a part of the daily process I am uploading flat files into database tables. The flat files contains the master(static) security data as well as the analytics(transaction) data. I need to 1) separate the master (static) data from the flat files, 2) check whether that data is present in the master table, if not then insert that data into the master table 3) If data present then move that existing record to an history table and then update the main master table. All the 7 fields need to be checked to uniquely identify a single record in the master table. How can this be done Whether we can us a combination of data flow items or write a sql procedure ...Show All

• Peter McEvoy Using stored procedure as OLE DB source with parameters from package variables

  Hi Guys, (I have searched this forum extensively, but still can't find the solution to this problem) Here it is: I have step in my ETL process that gets facts from another database. Here is how I set it up: 1) I have to package variables called User::startDate and User::endDate of data type datetime 2) Two separate Execute SQL Tasks populate those variables with appropriate dates (this works fine) 3) Then I have a Data Flow Task with OLE DB source that uses a call to a sproc of the form "exec ETL_GetMyData @startDate = , @endDate = " with parameters mapped accordingly (0 -> User::startDate, 1 -> User::endDate) When I run this I get an error 0xC0207014: "The SQL command requires a parameter nam ...Show All

• MichaelL Remote connection refused when creating ODBC connection to SQL Server 2005

  When I create a new odbc connection to a SQL server 2005 Db I get a failure telling me dat de SQL server does not allow remote connections. How can I allow the server to allow this. Any help appreciated regards I would recommend looking at the following post, hopefully it already contains the answer to your issue: http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=297514&SiteID=1 If the information here is not enough to fix your problem, let us know, we will be glad to help. Thanks, -Raul Garcia SDE/T SQL Server Engine ...Show All

• MJC2006 SQL Queries for an Access database

  Hi, I'm working on an electronic register which people use to sign in and out of a building. It uses 3 tables, one to store personal information (Name & Visitor Number), one to store what time they come in, and one to store what time they leave. Visitor Number is the primary key in the Visitor_Info table, and is used as a foreign key in the other 2 tables. I've been asked to put in an 'emergency print' function where, when you click the print button, it compares the data in the time in database and the time out database and prints a list of people still in the building. So, what I'm trying to do is create a SQL query that will give me this information. Having never used SQL before, it's a little tricky. I've spent the last few days loo ...Show All

• Tomys Parameter in calculated member of MDX datasource

  Can you put a parameter in a calculated member I have tried: SUM({NULL:@ToDate},[Time Charged] )  -- Obviously doesn't work AND SUM({NULL:' + STRTOMEMBER(@ToDate, CONSTRAINED) + '},[Time Charged] ) The latter sort of works but I get an error saying that the @ToDate parameter has not been declared... but it has been because it is being used later in the mdx statement with success.  This leads me to believe that parameters are only injected for the select statement and not anything else. Is this true   Are parameters restricted to only where the designer lets you put them Based on this post I tried the following: SUM({NULL:STRTOMEMBER(@ToDate, CONSTRAINED)}, [Time Charged] ) However I received the following e ...Show All

• Ronan PB 2006 Association model prediction not using itemsets

  I have a market basket model using associations. It generated several dozen itemsets. However when I attempt to run a singleton prediction like this: select ( Predict (Orderproduct3q, INCLUDE_STATISTICS ,10)) as [Recommendation] From [Case All] NATURAL PREDICTION JOIN ( SELECT ( SELECT '16407' AS [Pname])) AS t1 the resulting predictions don't take the itemsets into account. Instead, the predictions consist of the ranked products in the training set, ordered by frequency. This appears to happen regardless of the precise query specified within the "natural prediction join". What's going on here and how do I generate a singleton prediction which makes use of the itemsets ...Show All

• GoodMorningSky Error copying data to a remote sql server 2005 database.

  When copying data to a remote SQL2K5 destination from a SQL2K5 source database, both using mixed sql server security mode, my job generates the following error: [Transfer SQL Server Objects Task] Error: Execution failed with the following error: "Cannot apply value null to property Login: Value cannot be null..". This occurs after the destination database tables have been truncated and replacement data from the source would begin to copy. The same process can be successfully completed from the Management studio with a simple data export process. However, when I run the saved package again from the BI interface, I get this error. My search engine searches have yielded numerous hits of others having the same proble ...Show All

• AArben Show Details cannot be executed when multiple items are selected in a report filter field.

  Hi All, I used Excel 2007 to connect to one of my OLAP cubes. I dragged one of my dimension to the Report Filter and select several items in this dimension. When I right-clicked on the Values and Select "Show Details", I gotten an error message "Show Details cannot be executed when multiple items are selected in a report filter field. Select a single item for each field in the report filter area before performing a drill through." How are I get drillthrough report if I wanted to select multiple item within a dimension filter Is there any workaround Thanks Hi ralph, Thanks for this posting, very useful, and the instructions make it quite easy to use...I had one question, it doesn ...Show All

• Jade Skaggs Create Directory with a File System Task

  Hi! I'm having a bit of a problem implementing a File System Task to Create a directory and would appreciate some help if possible. I want to create a date directory so I can move files to once they are imported successfully. The date portion for the directory comes from the import file whose name is variable and in the format of PerfLog_<yyyymmdd>.aud. So, in essence, if I am processing a file named Perflog_20060913.aud, when I am done processing it I want to create a directory c:\myprog\20060913 and move my processed file there. Can anyone help me Please. There is one additional problem I'm having with this.  My filename variable is perflog_*.aud, because it goes through multip ...Show All

• SaimaSalim Adding member on a Role

  Hi all, I'm wondering if I can add a member on my role inside an SP. something like this. -------- EXEC sp_addrolemember 'RoleName', 'UserName' but i can't use the above statemnt inside a stored proc. I'm getting this error msg Msg 15002, Level 16, State 1, Procedure sp_addrolemember, Line 19 The procedure 'sp_addrolemember' cannot be executed within a transaction. Is there any other alternative way to add a member to a role inside an SP Thank you so much and have a nice day to all My bad it is not inside a [stored proc] it is inside a [TRIGGER after INSERT] You can commit the transaction inside the trigger before you execute sp_ ...Show All

• Cactus77 PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE

  I tried to create a sp on one of the databases on my lap top and got this: Pls help i need it bad Msg 10314, Level 16, State 11, Procedure ap_Hello, Line 5 An error occurred in the Microsoft .NET Framework while trying to load assembly id 65695. The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE. Run the query again, or check documentation to see how to solve the assembly trust issues. For more information about this error: System.IO.FileLoadException: Could not load file or assembly 'vbtriggers, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null' or one of its dependencies. An error relating to security occurred. (Exception from HRESULT: 0x80131 ...Show All

• Tom_Liu Black server reports after installing SP2

  After installing SQL Server 2005 SP2 all of our server reports are being rendered with a black background and white text, which needless to say is reversed of how they were being drawn before installing SP2. This only happens when the report viewer is displaying the report in “Print Layout” mode. Normal mode shows the report as expected. Has anyone else had this problem or suggest a solution Thanks in advance. I am having the same problem: Reports in Print Layout are rendered in a black background user hotfix : Replace the "Microsoft.ReportingServices.ImageRendering.dll" from SP2 with "Microsoft.ReportingServices.ImageRendering.dll" from SP ...Show All

• Steveisok Currency Conversion SSAS 2005

  I would like to have calculated members in a currency dimension that will apply an exchange rate to all applicable measures based on what the user selects. All measures in the fact table are stored in one currency (USD) but need to be reported in a variety of currencies. In AS 2000 this was accomplished via a lookup cube. I have read the documentation on books on line regarding currency conversion with the Business Intelligence Wizard in SSAS 2005 and this seems overly complicated and will not work based on our needs (since our data is stored in one currency and only the most recent exchange rate is important, no date dimension is necessary). I have a fact table with with currency code and converstion rate. Since all of our measures are ...Show All

• XNA Rockstar ADO with SQLXMLOLEDB

  A command is created on a connection with SQLXMLOLEDB provider(SQLXML4.0). 1. Where do you find information for the [cmd.dialect] 2. With you can provide an InputFile and get an outputFile. What are the formats of these files 3. What are the appropraite argumetns for the cmd's execute method Can you provide a link for SQLXML4.0 download Appreciate even part answers. Thanks. Jayaram Krishnaswamy my web site: http://www.angelfire.com/vt2/hodentek I blog at: http://hodentek.blogspot.com The inputFile is really a template file. The structure of thsi file seems to have changed from version 3.0 to version 4.0. If the inputFile(template) sent a SQL statement, it shound be executed on the server and th ...Show All

• Uwe Heinkel Formatting dates on a chart when sourced from SSAS2k5

  I am creating a few charts in SSRS that are sourced from a cube in AS. On the x-axis is time, which is what I assume a date-like field in the cube. On the y-axis is the measure. My problem is that I cannot seem to get the dates to format on the chart - regardless of the format code I use, the dates appear in long format, like "Thursday, November 23 2006". Are the dates from SSAS just string values Based on your query in the MDX query generator, the date time value is provided as a string to Reporting Services. That's why format codes won't work. You could look into either changing your MDX query to get the value as DateTime object (which means you have to hand-write the MDX) or you could try ...Show All