Answer Questions

• sally_de Help with Distinct SQL Statement

  I'm trying to return a select statement with distinct values. I have 3 tables: Documents, DocumentAuthors and the linking table DocumentAuthorsRel. Since a document can have multiple authors, the DocumentAuthorsRel table holds the DocumentID and DocumentAuthorID values. I simply want to run a query which displays a list of all the document titles (no document title should be repeated, i only want to show the title once, distinct) held in the documents table, and with the document title, show the last names of a documents author(s). This is the statement Im using which is returning duplicated document titles (as a result of a document having multiple authors - found in the DocumentAuthorsRel table) SELECT Documents.DocumentID ...Show All

• jss3426 What to exclude for virus scan for analysis services 2005

  For 2000 I found this document: http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/anservog.mspx Which said: Important You should disable the Indexing Service to avoid locking problems and possible corruption during processing. You should also configure any anti-virus software on the machine so it does not scan the Analysis Services Data folder or the Temporary file folder. But I can't find anything on 2005... I usually exclude Data directory (you can see it in the server properties in SSMS - normally it is %Program Files%\Microsoft SQL Server\MSSQL.2\OLAP\Data). If you explicitly set Temp directory - you will need to exclude it too, but if not - than Data directory is enough. If you explicitly s ...Show All

• Kent Waldrop Fe08 Help with CURSOR

  We have a tree structure containing section names. Each node is a section name and each section can have subsections. I have to copy the tree structure but need to maintain the parent-child relationship established within the id / parent_id fields. How do i acheive this For example i have the tree Section 1 |-Section 1.1 Section 2 |-Section 2.1 The "Section" table contains 3 fields: id, parent_id, and caption. ID is the identity of the section record and parent_id contains NULL or the ID of this record's parent to create a child. So "Section 1" (id=1, parent_id=null), "Section 2" (id=2, parent_id=null), "Section 1.1" (id=3, parent_id=1), "Section 2.1" (id=4,parent_id=2). I would like to ...Show All

• HappyTomato Analyzing a Query in SQL Server

  I have to duplicate a bunch of reports that were produced by a VB6 app. Now I'm using C#, ASP.net and Crystal Reports via VS2003. Each executes a stored procedure in SQL Server (before and now). For each report I have a Stored Procedure and a View. The View's SQL code is identical to the Stored Procedure except that the two input parameters (startDate and endDate) are removed because Views don't allow parameters. Some of the reports work perfectly right off the bat. But others are timing out. My initial test of the timing out is to just display the View. If it fails then I know that the the report engine will fail too. So now I'm trying out the SQL Query Analyzer tool to execute the code in one of the Views. It's now at 29 minutes and stil ...Show All

• giddy SQL Server 2005 Express Toolkit & Reporting Services

  Hi, I had previously installed SQL Server 2005 Express edition (and later, SQl Server Management studio express). Recently, I wanted to try out 'Reporting services' and downloaded 'SQL server 2005 express Toolkit'. After installation, I have a new program group 'Visual studio 2005' . I started that with 'New Report Project'. I tried to attach an existing database (to be precise, in 'connection string' part) , but it is giving error saying 'An error has occured while establishing connection to Server. When connecting to SQL Server 2005 ( ) ,..... may be caused the fact that under default setting SQL Server does not allow remote connections ' I tried my existing SQL Server 2005 express edition Surface Area Configuration and it is configure ...Show All

• AndrewBadera relate two tables based on an 'active' column

  So I have a question and I will give a related example. Say I have a list of records in a table: Product Cost Active hat 1.00 false coat 2.00 true I have a DataGridView that shows purchases from a purchase table: Date, ProductID, Buyer Is there a way to have a DataGridViewComboBoxColumn show only active items so that a user can only select active items in the puchases datagridview, but when they click a purchase from long ago when an item was active they have the ability to pick that old(unactive) item or a new one for an update. Basically if I just have the ComboBoxColumn bound to a dataset with just active items, the datagrid will fail to load cause old purchases are not in ...Show All

• George2 Package Configurations not so portable?

  I'm still struggling with finding a sound approach to creating configurations that are truly portable.  For instance if you use XML files to store configuration information, the path to the XML file that stores the configuration is baked into the package.  This would require the same file system structure on all machines that could host the package. Store in SQL server - one key thing that is valuable to configure is Data Sources - but how can you configure a data source at runtime by storing it as a config in SQLServer, when the data source itself is used to specify the server that stores the config information Environment variables or registry entries seem to be the most practicle because they truly are machine specific. ...Show All

• Taylor Meek SSIS Boolean Questions

  Please write YES/NO against point number in your answer i.e. for example: 1) YES, 2) YES, 3) NO, 4) YES etc. We have source SQL Server 200 databases in one instance and target SQL Server 2005 in another instance both being on different windows 2000 server systems. Target OLAP DB design is derived from source OLTP. Most master & transaction tables are as is but some target tables are a result of merging source tables. We need to do SSIS packages based migration using data flow task based on column mapping & queries using Business Intelligence Development Studio (BIDS). There are no transformations required. 1) Can I group packages under project into sub-groups i.e. super packages 2) Can I execute a set of packag ...Show All

• Edward1 Error when installing

  Iv'e searched other forums and websites for this, but I cant solve it. When I try to install SQL 2000 I get an error about Low Registry Quota. Any idea on how to solve this Didnt work :'( Yeah, Iv'e tried that one already. Ill try it again. Thanks :) Doing a quick search, I found this suggestion which helped a few people fix this problem. Can you check if this might be your problem Thanks, Sam <snip> I have never ever had the following error message before during the installation of SQL Server Developer Edition 2000 on a client running Windows XP Pro SP2 using an account with administrator privileges: "An error occ ...Show All

• Chris D Jones Transaction Consistency In Sql server 2005 sp2.

  (The previous edition of this post has some written errors which can make what I say unclear .Now those written errors are correctd.) [cross-column & cross-row consistency] Transaction Consistency In Sql server 2005 sp2. How are cross-column and cross-row consistency in SQL SERVER 2000/2005 Now there is a table named T,and its structure is as follow: T(value , twice_value), which means [twice_value] is twice of [value]. I filled it with some rows ,then it looks as follow: -------------------------- value |twice_value -------------------------- 1 |2 32 |64 78 |156 -------------------------- Then I run the statement: ---------------------------------- Update T  set  [value] = [value] * 2 ,   ...Show All

• sankumar How to rank an union of two result sets?

  I am new to SQL Server Integration Services. I need to help here. If there is only one result set, I could get rank info by putting DENSE_RANK expression inside of my select. My case is that I have two data source. After union of them, I need rank the result set and save result set plus rank info into the destination. I tried to use Derived Column component by using expression DENSE_RANK() over ( order by columnName ). But it does not like it. Does anyone know how to achieve this Thanks! Dense_rank is not a SSIS function. If you want to use the DB engine, you could write the 2 sets of data to a staging table and then use an additional dataflow with a query that does the rank over the staging table. Another option would ...Show All

• Mathieu Cupryk Error: A deadlock was detected while trying to lock variable "x" for read access. A lock could not be acquired after 16 atte

  I simply made my script task (or any other task) fail In my package error handler i have a Exec SQL task - for Stored Proc SP statement is set in following expression (works fine in design time): "EXEC [dbo].[us_sp_Insert_STG_FEED_EVENT_LOG] @FEED_ID= " + (DT_WSTR,10) @[User::FEED_ID] + ", @FEED_EVENT_LOG_TYPE_ID = 3, @STARTED_ON = '"+(DT_WSTR,30)@[System::StartTime] +"', @ENDED_ON = NULL, @message = 'Package failed. ErrorCode: "+(DT_WSTR,10)@[System::ErrorCode]+" ErrorMsg: "+@[System::ErrorDescription]+"', @FILES_PROCESSED = '" + @[User::t_ProcessedFiles] + "', @PKG_EXECUTION_ID = '" + @[System::ExecutionInstanceGUID] + "'" From progress: Error: The Script returned a fa ...Show All

• Kmbhat Problem with Microsoft .NET Data Provider for MySAP Business Suite

  Hello everyone, I've my SQL Server 2005 working properly on Windows 2003 R2 64bit. At this time I want to transfer data from SAP R/3 46C. I've had install the provider "Microsoft .NET Data Provider for MySAP Business Suite" but when I try to import data from sap using "import/export wizard" but the datasource of mysqp does not appear in the list. But if I run Visual Studio 2005 and try to create a datasource the data provider appears. What I did wrong I don't undestand. Please can anyone give me an idea Best regards nmarmol Hi Nmarmol, This looks like a tools issue to mee---I'm moving this thread to a more appropriate forum. Cheers, -Isaac ...Show All

• PastillaReturn Same Event Handler for all the tasks in the Package

  Hi, We want to develop an error handling process that will log the errors into multiple destinations (eventlog, text files or sql database) depending upon a variable set in the package. Also we want that this errror handling process should be initiated by all the tasks in the package on error. Is this possible Can the same event handler be called from multiple tasks in the package Also in the event handler can we call another package which actually does the error handling. This way we have only one place to change our error handling process in case required. Thanks in advance for your help. $wapnil Ok. now we can have one error handler for the entire package. Thanks! If we plan to extend t ...Show All

• jeffers Merge Replication Mysteriously Deleting Rows

  I am running a simple merge replication in SQL Server 2000. I have one database that is the publisher, and a second database that is the subscriber. When I add a new row to the subscriber it will replicate to the publisher as expected. However, the new row at the subscriber will then be deleted without explanation. The row will remain at the publisher though. Does anyone know why it is doing this I was talking about horizontal filtering. Are you filtering using HOSTNAME, or SUSER_SNAME, or some other static filtering Some of the tables on the publisher have more columns than the respective tables on the subscriber. So there is vertical filtering. For example, a ...Show All