Answer Questions

• jameyer 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

• THE RAZI restarting SSAS nightly improves query performance

  I don't have any hard numbers to share, but in the tests I've done, restarting the SSAS service nightly appears to improve query performance. Here's the reason I say that: 1. If I leave the SSAS service running for a number of days, timing an expensive MDX query will show it being slightly slower (say 4%) than immediately after I restart the service and run the same query. (In both the before and after test, I'm running the query several times in a row so that it can do all the caching it wants.) 2. If I leave SSAS service running for a long time with out restart (say, a month or two), it slowly consumes more and more memory even though we're doing a full process of the cube every night. (I would have figured that doing a full proces ...Show All

• Ralf_from_Europe JAVA and Stored Procedures

  Hello, I want to call a stored procedure (SQL SErver 2000) from JAVA. The SP contains a temporary Table. At the end of the SP there is a Select like "Select * from #tmp". Now I get no Resultset in my JAVA - Application. When i do a normal Select on an Table I get a Resultset. I need the temp. Table in this SP. How can I get the Result in JAVA. It is urgent for me. Thanks in advance.   You should be able to get the results in the normal way. You might find that you have additional results -- but not result sets -- that preceed the result set. You might also find that by issuing Set NoCount On in the stored procedure, that you will not get the additional results. ...Show All

• Satyajit Filters and CountRows function

  Greetings, I have a fairly simple report that displays one table (table1). The table has a filter that I created by opening the properties of the table and entering =Parameters!abs_comparison.Value in the Value field of the Filters tab. The filter operates on the =Fields!Absolute_Comparison.Value field. The report displays the row count from the dataset (=CountRows("mfrep_rqw"), but this value doesn't change when the filter is applied. I'd also like to display the number of filtered rows, but I can't figure out how to do it. Any ideas Thanks, Mike Hayes Can you add the filter to your SQL statement in a where clause instead of in the properties of the table Then, if you need the full ...Show All

• C. Hunter How to print labels like label 1 of 1 and label 2 of 3....

  I have a report that will be a label and I need to print N number of labels depending on a certain number returned from the query. Let's say that number returned is 3. So, on my first label I want to print 1 of 1 and on the second label, 2 of 3 and the third, 3 of 3. All other information on the label will be the same for all 3 labels. All information used is from the query. One of the fields accessed through the query holds the number I need to use to do this. Is there a way to do that through the report designer using vs.net Is there a setting for this or is it a SQL thing I need to put in the query some how. Or am I completely off track Any help would be greatly apprciated. thanks If you kno ...Show All

• Nirupa Sync will work only 1 time.

  I am really stuck on this, if anyone has some insight into this problem any help would appreciated... I'll try to explain what is happening the best I can: We have a server running Windows Advanced Server 2000 (SP4) w/ SQL server 2000 (SP3) (from now on Server A). I have a publication on this machine with dynamic filters (Changing the HOST_NAME()). The publication is sending the snapshots to another machine (desktop machine). The Mobile agent is in the same machine as the snapshots. The mobile application is syncing fine when hitting Server A. The sync is done Asynchronously. Then we have Server B. Running Windows Server 2003 (SP1) w/ SQL 2000 (SP4), same publication w/ dynamic filter however the snapshots and the mobile agent a ...Show All

• AlexVallat Error while Browsing Cube

  We have a cube with only one partition.While trying to browse the cube it is showing the following error. OLE DB error: OLE DB or ODBC error: You do not have permission to run 'SP_TRACE_CREATE'. ;42000 My user name is in the admin group and I am able to Procecc the cube successfully. Please Help. You should set your storage mode to MOLAP to disable proactive caching. You also go through all of your partitions to make sure the storage mode is set to MOLAP. Edward. -- This posting is provided "AS IS" with no warranties, and confers no rights. Have you enabled Proactive Caching on your partition Edward. -- This posting is provided "AS IS" with no warranties, and ...Show All

• Blast process analysis server objects from web via ASP.NET + C#

  Hi! We are running several Analysis Services Cubes on a Database Server (SQL 2005) machine. Another machine is our web server (IIS, ASPX) which displays cube content and allows drill down to several levels. Everything works fine and we are looking forward to optimizing the whole data management process. At the moment we try to develop DTS packeges (SQL 2005). Is it possible to initiate a cube processing (without DTS in the background) from eg an administration section in the web page TIA and kind regards from Vienna JohnnyR Hi, I have created an analysis services project using SQL Server 2005, in which I have included a cube, dimension, data source and data view. Now using ...Show All

• Jeff Weber Data Migration Oracle 7.16 to SQL Server 2000

  Hi, I have been asked to look at optimising a Data Migration Strategy for one of our clients. I am quite new to Data Migration (I'm actually a system designer!). The migration is from a legacy Oracle 7.16 Db to a SQL Server 2000 environment. The legacy Db has some large tables - 30 Million+ rows Table/column Mapping is 1:1 At present we are using DTS to import into the SQL environment. My question is: Is there any way to leverage the Oracle 7.16 export to speed up the migration process Any advice would be appreciated. Many thanks Mike this forum is for sql server 2005 integration services...not sql server 2000 data transformation services. i suggest that you post your quest ...Show All

• Manmeet Panigrahi Concatenating Fields With NULL Values

  Hey everyone, This is probably a very simple question, but I am just stumped. I am storing different name parts in different fields, but I need to create a view that will pull all of those fields together for reports, dropdowns, etc. Here is my current SELECT statement: SELECT m . FName + SPACE ( 1 ) + m . MName + SPACE ( 1 ) + m . LName + ', ' + m . Credentials AS Name , m . JobTitle , m . Company , m . Department , m . Address , m . City + ', ' + m . State + ' ' + m . Zipcode AS CSZ , m . WorkPhone , m . FAX , m . Email , c . Chapter , m . Active , s . Sector , i . Industry FROM tblMembers m LEFT OUTER JOIN tblChapters c ON ...Show All

• BoN12578 Problem with querry performance locally vs. network

  Hi, I work on speed-up our company SQL Server 2000. I reconstructed one querry and test it on client computer (other than server). There was no profit. I was surprised :(. I supposed several times speed-up. Next I run old and new querry directly on our SQL Server machine. The result was proper - new (after modifications) querry takes 2,5 times less time than previous. Ok, question: why there wasn't profit of modification from client computer I thougth that reason is slow network. So I copy from client computer file of weight 80MB to server. This takes 10seconds. Result of my querry is 35MB and it takes 17 seconds on client computer and 6 seconds directly from server. Best regards, Walter Luszczyk ...Show All

• meighlough The return of Problem Assigning Value to Package Variable From Data Flow Script Component

  I have a Data Flow Script Component(Destination Type) and in the properties I have a read/write variable called User::giRowCount User::giRowCount is populated by a Row Count Component previously in the Data Flow. After reading http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=97494&SiteID=1 it is very clear that you can actually only use  variables in the PostExecute of a Data Flow Script Component or you will get an error "Microsoft.SqlServer.Dts.Pipeline.ReadWriteVariablesNotAvailableException: The collection of variables locked for read and write access is not available outside of PostExecute." What I need to do is actually create a file in the PreExecute and write the number of records = User::giRowCount as&nbs ...Show All

• Will8754 Can't register SSIS server in SMS...need permissions help.

  I have a user who can't register the SSIS server in SMS.  What permissions do I need to give him to allow this ... without making him sysadmin of course Yes, I found this online. Unfortunately, after all this I'm still getting an error. Could not get proxy data for proxy_id = 1 When I set up the credential, what kind of account should the "identity" be SQL or Windows What kind of permission should the "identity" have Check out the article by Kirk Haselden @ http://sqljunkies.com/WebLog/knight_reign/archive/2006/01/05/17769.aspx Thanks, Loonysan ...Show All

• stswordman page number & records number

  1. how to show page number & total page number in report body 2. how to show total records number thanks for reply, but errors found. for #1: [rsPageNumberInBody] The Value expression for the textbox ‘textbox1’ refers to the global variable PageNumber or TotalPages. These global variables can be used only in the page header and page footer. for #2: [rsInvalidAggregateScope] The Value expression for the textbox ‘textbox2’ has a scope parameter that is not valid for an aggregate function. The scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a data set. pls advise, thanks ...Show All

• yvz Question for designing a SQL Job Admin

  I want to design a login named "JobAdmin" who can add/modify/delete any SQL Agent Jobs but can't access my "db1" database. The jobs would execute some stored procedures in db1, and it could be a user "db1dbo" with db_owner role in db1 to execute the stored procedures. How could I let JobAdmin impersonate db1dbo to run these jobs Thanks in advance. I've tried these fixed database roles in msdb before, but how could JobAdmin impersonate db1dbo to execute the stored procedures in db1 (JobAdmin doesn't have access right to db1) In SQL 2005 you have few SQL Server agent role in msdb database using which u can achieve u r requirement I think SQLAgentUserRole can solve u r problem Read SQL Serv ...Show All