Answer Questions

• Fernando Tubio ForEach Loop - Testing for when Enumerator is Empty

  I have a SSIS package this set to run at a specific time each day. If there are no files for the ForEach tool to work upon...while it doesn't 'fail'...I would like to test for the condition that the enumerator was empty...so that I could send an email message reminding someone to followup and investigate. What would be the best way to test for that condition Cordell, Feedback can be submitted to http://connect.microsoft.com . Glad I could help, Patrik Cordell, You could add a counter variable to the package, use a Script Task within the ForEach loop to increment the variable, and then use an expression on a precendence constraint following the ...Show All

• Abara6 Creating Parent Child Hierarchies

  Hi I have the following in my cube Location Dimension containing LocationID and LocationName attributes. Customer Dimension containing CustomerID,CustomerName....,LocationID The LocationID in the Location Dimension and Customer Dimension are related to each other. I want to create a hierarchy with the LocationName in the First Level and the CustomerName in the second level. How to I accomplish this As i said I already have a relationship defined between the two tables. Thanks and Regards Guruprasad Karnik This is not a recursive hierarchy from what you are describing. You can find a recursive hierarchy in the employee table in the Adventure works sample data base that is a part of the SQL server 200 ...Show All

• db-C Cannot open user default database. Login failed

  Hi, I have installed SQL Server 2005 Express edition and when i try to run the web site as http://127.0.0.1/SiteName/Login.aspx . I am using default SQL Provider. Can anyoone please help me i was struck here from last two days. System.Data.SqlClient.SqlException: Cannot open user default database. Login failed. Login failed for user 'XXXXXX\ASPNET'. Regards, Pradeep. Hi, ok we are getting closer. The error is related to the fact that you want to redirect to a database where is not granted any access to. Automatic redirection of Users is done in either of the two ways. You have a connection string where no "Inital Catalog" keyword is specified, then the user will be re ...Show All

• Milzit Differences between 2000 and 2005

  Just installed 2005 Enterprise Edition on an XP Media computer... I am blown away because nothing looks familiar to me. Anyone else feel lost Where is the best site for specific changes from 1 version to another... it appears that Enterprise Manager is gone, Query Analyzer is gone, my brain is gone thx Kat Hi Joey, Do you mean that I shouldn't be using the Enterprise Edition I need to learn the reporting services that aren't in the Express Edition. I'm not sure I understand your answer. thx, Kat Kat, Just have a look into the following product info article which will help you in knowing What's New in SQL Server 2005. http://www.microsoft.com/sql/prodinfo/overview/whats-new-in-sqlserver2005.mspx ...Show All

• KingKoo redundant definitions of row...WHY?

  insert new row for car private static void InsertNewCar( SqlDataAdapter dAdpater) { // Gather info about new car. Console .Write( "Enter CarID: " ); int newCarID = 0; try { newCarID = int .Parse( Console .ReadLine()); } catch { Console .WriteLine( "Bad input! Canceling request" ); return ; } Console .Write( "Enter Make: " ); string newCarMake = Console .ReadLine(); Console .Write( "Enter Color: " ); string newCarColor = Console .ReadLine(); Console .Write( "Enter PetName: " ); string newCarPetName = Console .ReadLine(); // Format SQL Insert and plug into DataAdapter. // ................ ...Show All

• someone030 Paged Result Sets

  What is the recommended mechanism for selecting paged results from SQL. Presently I pass various params including the request Max Items Per Page and the requested page. The I execute the query as a count with the search params. Then comes the paging logic, which validates the page number against the request page and number of hits etc. Then a temp table and record variables are created for the results. Then I run the query again with a cursor and select the appropriate Items into the temp table based on the paging values (First Item and Last Item). Then I return the temp table & some additional return params with the Total Hits etc. The Stored procedure is accessed via an ADO.Net client and the system.data.IDBReade ...Show All

• Anne Carpenter How to use Distinct in XML Column

  We have used XML Column in our Table. I want to select a distict values from the xml colunm. is there any way to use distinct Regards Vasanth Thangasamy You can use the nodes method to extract the values you are interesting in and then use DISTINCT in the projection. For example: declare @x xml set @x = N'<you xml here/>' select distinct ref . a . value ( 'text()[1]' , 'nvarchar(25)' ) as name from @x . nodes ( '/xml/Directors/Dir/Name' ) ref ( a ) If, on the other hand, you intend to use the distinct values inside of XQuery, you can use the function fn:distinct-values: select @x . query ( 'fn:distinct-values(/xml/Directors/Dir/Name)' ) Regards, Galex ...Show All

• Steve Abel SQLAgent forces a Cmd Window

  I am installing SP4 on a previously installed SQL2000 Ent running on a Win2000 Enterprise SP1. When I start SQLAgent, a CMD window with no statements opens up. If I close the CMD window, the SQLAgent stops. So, to keep SQLAgent running I have to minimize the CMD window and leave it up all the time. Is there any way I can run the SQLAgent without having the CMD window open all the time I am clicking on Start in the SQL2000 Service Manager when SQLAgent is showing as the service to start. You think the exe for SQLAgent is not registered as a service Is there any way I can fix it Did you try to start the service via the service console What did you start, the service or the executable SQ ...Show All

• krwell Multi-step SSIS Job Hangs

  I have two SSIS packages that I want to run in one SQL Agent job as two individual steps. The two packages run fine when they are in separate jobs. However, when I run the job conaining both SSIS packages (under the same proxy), the first SSIS package starts, but hangs in the middle. I then tried setting the DelayValidation flag to True as suggested for a similar issue in another thread from this forum. After changing the DelayValidation flag to True for all containers and tasks on the second SSIS package, the first SSIS package ran completely through sucessfully, but the job continued executing for hours and the second SSIS package never started. I finally killed the job. Any ideas as to what is the problem here I have logged to th ...Show All

• Abongs Slow Running Insert After Changing OLE DB Destination Data Access Mode

  Hello, In a Data Flow Task, I have an insert that occurs into a SQL Server 2000 table from a fixed width flat file. The SQL Server table that the data goes into is accessed through an OLE DB connection manager that uses the Native OLE DB\Microsoft OLE DB Provider for SQL Server. In the OLE DB Destination, I changed the access mode from Table or View - fast load to Table or View because I needed to implement OLE DB Destination Error Output. The Error output goes to a SQL Server 2000 table that uses the same connection manager. The OLE DB Destination Editor Error Output 'Error' option is configured to 'Redirect' the row. 'Set this value to selected cells' is set to 'Fail component'. Was changing the access mode the simple reason ...Show All

• LittleSettler Time Dimension skips DAY

  Hello I'm using a field from a fact table with the following format: 2006-08-30 11:52:56.200 . The fact table is automatically generated from the Windows Relog utility which converts performance counters and pumps them in a CountersData table (my fact table). The funny thing is that this field has a nvarchar data type although it's a date. I can't change the datatype as it is generated by Relog. I tried SSIS with substring functions to create Year, Month, Day, Hour, Minute and Seconds fields as derived colums from the string so I could use them in a Time dimension. I'm able to create a time dimension using the dimension wizard but the outcome is always: Year, Month, Hour, Minute, Second. The question is: where is the DAY I'm su ...Show All

• cssjm SQL Server CE Replication: Integrity error, but not at all

  Error information: NativeError: 28549 HRESULT: -2147217873 Message: OrdersHeader,Delete,{0503BF00-BB05-11C6-8000-36BC4ADEE342} Description: The row update or insert cannot be reapplied due to an integrity violation. Server side: SQL Server 2000 or MSDE Client side: SQL Server CE or SQL Mobile This error is usually caused because of an error on the filters. But not in this case, i think. I assume it's not a filter error because if I delete the SDF and run the sync it works. The error occurs when I delete some rows in the server (parent( OrdersHeader ) and child( OrderLines ) tables) and the PocketPC stills have this rows. On the next synchronization It seems that replication process tries to delete the OrdersHeader rows before the OrderLin ...Show All

• gsell Invalid Descriptor Index error

  I'm testing db to db transactional replication on a box ( all on the same box ) and the distribution agent fails with the above error. I know it's something to do with the physical server as this test works on other servers fine. SQL2k Ent sp4 on w2k3 ent sp1. ( clustered ) Server and Agent accounts are in local admins, tried push and pull, named and anonymous. Replication also fails if I use the default snapshot location. I suspect policy restrictions ( maybe on the sql service accounts ) Any pointers would be helpful - there are no errors other than above, sadly. Here is the results I am getting. I am sorry to say this didn't help me much. I also have someone checking a possible issue with the x ...Show All

• aaks What's In A SQL Transaction Log File?

  My LDF file started out at 504K and pretty much maintained it while my dB grew. It became apparent that I needed to delete some records - perhaps no more than 100. Also, I had to do this several times because of poor coding. Well, long story short - the LDF jump to 450MB. The dB also went up an extra 30MB or so, but even though I added many records, the dB didn't grow - obviously, lots of extra space remain unused. Question is, since the data is in the dB and the delete records contained errors... I could possiblely delete the transaction logs - after all, I don't need to deleted transaction records do I Also, is there a free LDF log viewer I'm in the middle of transactions... check back ...Show All

• Quentin Mayberry T-SQL Code help

  Hi, When I execute the following procedure, SQL Server creates an error called 'must declare @table variable'. So I did not overcome this error. Would you please help me CREATE PROCEDURE erase @column nvarchar(25), @erasable nvarchar (25), @table nvarchar(25) AS delete from @table where @column=@erasable Please don't write these type of generic stored procedures. Even if it is possible to do this using dynamic SQL there are lot of issues that you should be aware of. Below are some of the reasons: 1. Dynamic SQL has security implications 2. Improperly written dynamic SQL can result in SQL injection attacks and compromise your database 3. You need to give permissions on the objects used in dynam ic SQL directly to end ...Show All