Answer Questions

• WillySnowMan SQL Chargeback

  Does anyone know of good chargeback software for use with SQL 2000 & 2005 We have two shared SQL servers with 120+ databases from various departments but cant find any off-the-shelf software which will allow us to produce MIS reports for mangement and charge back. I am aware of a Microsoft document however, this doesn't work too well and is too complicated for our maagement to use. Might check this http://techfinder.vnunet.com/vnunet/search/tabkeyword/services/Server+Chargeback+Software/1/index.jsp as search return these results. HTH ...Show All

• aeonblaire Need to Subtract the result of two stored procedures

  I am trying to take the results from two stored procedure and subtract them to get credit from two columns. The stored procedures look like create PROCEDURE mw AS select * FROM pla p where p.st in ('ds', 'sd') and (iPlayerID = '5345') compute sum(p.amount) RETURN both procedure have the same syntax. I am not able to use AS in to insert the values into a variable. Is there a wizard to use Got it Steve Scheberle Yes it was an oversight to have the variables as different. Thank you for your time Steve Scheberle Ink: My suggestion would be to transform your stored procedure into a function so th ...Show All

• Nancy_Issa How to use iif in Filter expression

  I am want to do a count distinct of an ID where fieldInt is not null in a table. This is how I am doing it... In Layout view, click the table, then right click Properties on Filters tab, for filter I do the following: In Expression column: = Fields.fieldInt.Value In Operator column: = != In Value: 'null' But this is not working. How do I solve this problem. You can write this in the expression = iif( Fields.Fieldint.value is nothing, 0, CountDistinct(Fields.Fieldint.value)) This should get you your count distinct where values are not null. Hammer try this. IsDBNull(Fields.fieldInt.Value ) = false ...Show All

• pajamo CTE and XQuery

  I'ev been playing around with CTE's and trying to query a table that has the following structure tbl Projects{ id (int), Project (xml) } sample data within the project column is id = 100 Project = '<Projects> <SubProjects> <id>150</id> <id>160</id> <id>170</id> </SubProjects> </Projects>' basically i'm trying to set up a recursive query to get all sub projects for a given project.. this is PART of a bigger query .. WITH MySubProjects(SubProjects) AS ( SELECT TblProjects.*, P.SubProjectIDs.value('(.)[1]','int') AS SubProjects FROM RB_Projects TblProjects CROSS APPLY Project.nodes('/Projects/SubProjects/id') AS P(SubProjectIDs) where id = 100 ) but i keep getti ...Show All

• Saania Urgent Sub Report Help Needed

  I have an urgent need to solve this issue. I have a report that has two sets of six sub reports. Here is what I have: Main Report Subreport1A Subreport2A Subreport3A Subreport4A Subreport5A Subreport6A Subreport1B Subreport2B Subreport3B Subreport4B Subreport5B Subreport6B I want to have a page break between the first set and the second set. The last subreport of the first set, Subreport6A, has a pagebreak at end =  true, so it should break. The problem is that when I export to Excel I am getting one sheet for the 12 subreports.   Does anyone know of another way to insert a pagebreak between subreports Still working on this... Does a ...Show All

• Kathirvel How to export report to word file ?

  Hi all, After publish a report to Report Server, how can we export that report to word file I used to try Office Writer tool. This tool allows to export report to word file but the report must be created in Office Writer, not report that is created in Reporting Designer of Reporting Service. Is there any way to export report to word file Dear Jens, Could you explain more clearly I must tell you it is not easy for Word. In fact, it was very hard, but we did it. We wrote a rendering extension that exports to DOC, RTF and WordprocessingML. We managed to do that because we have a technology - Aspose.Words for .NET is a component for server-side document processing. It is a mature product, over 3 years, ...Show All

• Ben Vanik Tsql script to connect to another database

  Hi. I am in database db1. in the middle of a stored procedure i want to connect to database @db2 (it is a variable) and i want to execute some commands in it. How is it possible to do that in t-sql Thanks You can't use Use in a stored procedure. You could use it in dynamic sql, but not in a procedure. A concern here is varying a database name by parameter. Do you really have enough databases to warrant using it as a parameter I have heard of folks that do have this many databases, and if you do, then use dynamic SQL. Otherwise it is far better for performance, maintainability, etc, to reference objects directly. sp_addlinkedserver @server='_TestServer',@srvproduct=' ', @provid ...Show All

• VBwant2B store time

  Is there any posibility to store only time and not date in sql server Even if there is no data type to support it, is there any round about way of doing it There are many approaches for that, storing time in a datetime column (with the date of 1/1/1900) storing the seconds / minutes after midnights (in the appropiate granularity as you need it) storing time as a string Depends on how you want to work with the data. HTH, Jens Suessmeyer. --- http://www.sqlserver2005.de --- If you are using 2005 a .NET UDT could help too. There is an example of a time UDT in the resource kit. ...Show All

• Tamim Sadikali Receive all messages on queue

  Hi i am trying to create a batch process then commit for all messages on the queue. The problem i am having is when i run my query (As below) I only receive the first message and the corresponding end dialog for the message although i have 2000 records sitting in the queue. It is my understanding that receive without any criteria i.e top(1) or where clause should select everything of the queue. I tried receive top(100) expecting 100 records but still only got 2 back. any help appreciated. WAITFOR ( RECEIVE queuing_order , conversation_handle , message_type_name , message_body FROM [RMIS_COMMS_Queue] INTO @tableMessages ), TIMEOUT 2000 ; I'm using triggers t ...Show All

• bryanedds Event Handler Refresh

  I am current using Event Handlers in an SSIS package, but when adding a new EventHandler type eg. OnPreExecute, to an executable within the package, I am unable to see this new EventHandler defined in the hierarchical listing on the left hand combo box (named Executables). Can't seem to find a Refresh option anywhere. I've only been able to see these newly created EventHandlers in the hierarchical listing by closing and re-opening the package. Is this a 'feature' or am I missing a Refresh option somewhere. Thanks. Check the scope of the eventhandler you have created. Is it possible you are looking in the wrong place in the Package Explorer -Jamie Hi P R W, I'm not positive, but ...Show All

• gabrielh LastNonEmpty vs MDX Script solve order

  Hi, I'm using the Account Intelligence and some account members use the aggregation function LastNonEmpty. Witch evaluates before the LastNonEmpty function or my MDX Script that I put in the calculation inside the cube If my MDX Script change some values only in the the leaves of the time dimension in one account (taht uses LastNonEmpty function). What will be evaluate first My MDX Script and after the LastNonEmpty function or the oposite Handerson LastNonEmpty is applied to the members above the leaves of Time dimension by aggregation the leaves through LastNonEmpty. Therefore if you will apply some calculation to the leaves of Time - it will be done before the LastNonEmpty aggregation. ...Show All

• BilalShouman How to get Top 3 records in the group level?

  Hi all, I have a query which is grouped by a field called R_ID, which gives me a list of records for each R_ID. So here is the thing, I want to get only top 3 records for each R_ID. I tried using 'select top 3....', but the result is not what I wanted. It doesn't consider the grouping. Is there any way to achieve this task Please let me know if some body has any idea. Thx. Your getting a partial result because of these line: ( SELECT top 100 R_ID , text_data , If You change it to ( SELECT top 100 PERCENT R_ID , text_data , You should get all the rows.... ms sql server data: idno Email Category 1 a@a.com c 2 b@b.co ...Show All

• Joshizzle Test Role.

  I whant to test role with my own application or Excel 2007, but not with Visual Studio. Can I set the role to use in the connection string How Hi Vladimir, The way it worked in Execl 2003 with a Pivot Table is that you would modify the connection string in the OQY file located in C:\Documents and Settings\dkbotze\Application Data\Microsoft\Queries. You can modify the *.OQY file and put the ROLES= command in there. The other way of testing this out is if you have a test account you can go to the EXCEL.EXE and right click on it and choose Run As, and enter the test account. David Botzenhart In addition, in SQL2005 you can also set EffectiveUserName=REDMOND\billg and impersonate specific us ...Show All

• Sami Karaeen vertically merge cells containing duplicate data

  Hi,   I am constructing a report with the Report Designer in which I want to create a form of visual grouping by hiding duplicate data.   My data is this:   foo 2006-01-01 12 foo 2006-01-01 32 foo 2006-02-02 345 bar 2006-01-02 1534 bar 2006-03-03 345 bar 2006-03-03 234     This is how I can do it with hiding duplicate data:   foo 2006-01-01 12 32 2006-02-02 345 bar 2006-01-02 1534 2006-03-03 345 234     And this is how I want it to be:   foo 2006-01-01 12 32 2 ...Show All

• robinjam How to scheule job to run last day of the month

  What are the settings to get a sql server job to always run the last day of the month This should be quite easy! Thanks! SQL2000 if so, on the schedule tab of the job, edit the schedule, or create a new one... on the schedule type, select recurring, then click the change button on the lower right hand side. In the Occurs section, change to monthly. Then in the monthly section that you will see to the right select the lower radio button and change the first drop down list box to "Last", the second to "Day" of every 1 month. Set the time, and away you go... Thanks! This would make a nice addition to EM Help! ...Show All