Answer Questions

• Jutsin Leung Expanding All SubQuery Nodes In Reports

  Hi Everybody, I've got a report that builds out top level data and adds the plus symbol (+) for collapse/expand next to each of the rows for me to display a detail level tied to that record. The problem is when we do an export or print, it only applies to the top level instead of all of the detailed information. Is there a way to automatically expand all tree-nodes in a report for these subqueries Thanks in advance. Best Regards, Justin Gentle bump... Anybody know how to do this I just want to be able to expand all child nodes of a record, either automatically or programatically... Thanks! Hiya, You can tell the report to always expand, or, as I ...Show All

• tribal Suppress blank lines problem

  I am having difficulty suppressing empty groups from rendering blank lines. For instance, take a simple left outer join to group detailed sales by region. The report has a regions group and the detail line. Every region that made no sales would print one blank line. If I add a subgroup between the region and the detail say to group by product within region then I get two blank lines for each no sale region. If I add another detail line then I get three blank lines etc. Has anyone else come across this and solved it Thanks. What i usually do is check the Len(FieldName) of one of the fields that would usually have data if the row is visible. If the Len(FieldName) = 0, that means visible is False. ...Show All

• David Parreira An attempt has been made to use a data extension OLEDB that is not registered for this report server

  I am using SQL Server 2005 Express along with the other Express reporting tools (VS Express). I am trying to report against an oracle database. I can design and preview the report however when I attempt to 'deploy' the report to a report server that is on the same machine I am developing on, I get the above error. Is this because I am running an Express version of SQL Server or is this an error Thanks, Sam Yes, the Express Edition only allows connections to local data sources with a limited set of data extensions. Not all types of data sources are supported in the Express Edition. -- Robert ...Show All

• IceAngel89 Put select statement in SSIS variable

  Is it possible to add a variable in SSIS like name of variable: myVar Scope: Data Flow Task Data Type: String Value:SELECT hello FROM blah WHERE (azerty = @[User::pda]) AND (qwerty = @[User::phone]) @[User::pda] and @[User::phone] are also variables in SSIS just like the myVar I made I know I'm doing something wrong with the data type because it's stores the whole select statement as a string Help Worf Worf wrote: Is it possible to add a variable in SSIS like name of variable: myVar Scope: Data Flow Task Data Type: String Value:SELECT hello FROM blah WHERE (azerty = @[User::pda]) AND (qwerty = @[User::phone]) @[User::pda] and @[User::phone] are also variables in SSIS just l ...Show All

• -Cyclone- How to not send subscription report if no data

  I have a report that may or may not have data. I have a subscription setup for this report. The subscription has a defined To list. I would like to NOT send the report if there is no data on the report. I did this in SQL 2000 by raising an error from the stored procedure if there was no data. However, that approach does not seem to work with 2005. Is there another way to accomplish this I can't seem to make this work in SSRS 2000. I have a static list of recipients, I can preview it in the IDE, but I get errors when trying to test it as "run once" subscriptions. So, essentially, I am trying to get yesterday's reports pushed out to the subscribers if there is data. Any tips you can provide w ...Show All

• ttad convert year from mm/dd/yyyy to mm/dd/yy

  I am working with a chart. I want to display the year on the X axis as mm/dd/yy. I have tried entering yy, etc into the format code field and have had no luck. Any comments are appreciated. Reporting Services uses similar formatting commands to Excel. You should be able to use MM/dd/yy. If that doesn't work, another way would be to create a function that would return the values in a string format and parse the string. Something like: public function getDate(dt as string) as string string dt = left(dt,6) & right(dt,2) return dt end function cheers, Andrew Thanks! I was able to get this to work CONVERT(varchar(12), field_name, 10) AS Date ...Show All

• bkohler Reporting Services privileges

  How do I need to setup IIS and SQL Server to get reporting services to work When I navigate to http://<my server>/reports, I get a generic error page. PLease help! More info:  When I click "Set SharePoint administration group" from the SharePoint Central Administration page, I get the following: " Cannot connect to the configuration database. For tips on troubleshooting this error, search for article 823287 in the Microsoft Knowledge Base at http://support.microsoft.com " The info in this KB article did not help. ...Show All

• artu SQL Server 2005 OLAP Cube on SQL Server 2000?

  Hello, I have designed a OLAP-Cube with the SQL Server 2005 Analysis Services. Is there a way to publish this Cube on a SQL Server 2000 with Analysis Services Or is there a way to connect the Server 2005 through the 2000 Server Thanks a lot! I do not think this is possible. SSAS2005 is a new release of Microsofts OLAP technology. They do not work in the same way. You can only migrate cubes from SSAS2000 to SSAS2005. HTH /Thomas Ivarsson Are you trying to deploy as an 2000 olap cube I am trying to find out if SSIS can process a 2000 olap cube, but your request would be better if it worked. I do not know, but let me know if you find out! Dave ...Show All

• TomasLeung Beginer Problem

  I have to generate flat files from SQL Server tables. Its a backup package for few tables which will be run daily.  I want the names of the destination flat files to be automatically generated at run time. I created an expression for the Flat file destination connection and appended the date and the time to the flat file name.  For today my file name is 'table02-12-2007.txt'. for tomorrow it should be 'table02-13-2007'. When I evaluate the expression it shows the correct value. But when i run the package, I receive an error indicating -  Destination file is not found. Error is because the destination file is not present at runtime and I was hoping that the package will be cr ...Show All

• Perley sql

  1 1 100 2 1 NULL 3 1 NULL 1 2 200 2 2 NULL 3 2 NULL 1 3 300 2 3 NULL 3 3 NULL to this. 1 1 100 2 1 100 3 1 100 1 2 200 2 2 200 3 2 200 1 3 300 2 3 300 3 3 300 hi, i need sql to do this. how suppose a ,b ,c SELECT t1.a , t1.b , t2.c FROM some_table t1 JOIN ( -- returns the max row within gr ...Show All

• nickisacrazypirate Trouble connecting with bcp

  Hi, I'm trying to use the bcp utility to create a format file to import data from a flat file. However, bcp cannot connect to the local instance of SQLEXPRESS. I am able to connect using both Visual Studio 2005 and SSMSE. Here is what I get: snip C:\>bcp SQLEXPRESS.SAMS1DB2.Organization_01A format nul -c -f Organization_01A.fmt -T SQLState = 08001, NativeError = 10061 Error = [Microsoft][SQL Native Client]TCP Provider: No connection could be made because the target machine actively refused it. SQLState = HYT00, NativeError = 0 Error = [Microsoft][SQL Native Client]Login timeout expired SQLState = 08001, NativeError = 10061 Error = [Microsoft][SQL Native Client]An error has occurred while establishing a connect ...Show All

• danmor Synchronous script component: get last row

  HI, I need to know whether I am on the last row or not in my script component. If this is the case, I would alter a column in the row that indicates me that I am processing the last row. Is there a way to do it I tried with process input but when the EndOfRowSet() indicates me that the last row is porcessed, I cannot alter the row in the buffer. Thank you, Ccote   ccote wrote: HI, I need to know whether I am on the last row or not in my script component. If this is the case, I would alter a column in the row that indicates me that I am processing the last row. Is there a way to do it I tried with process input but when the EndOfRowSet() indicates me that the last row is porcessed, I c ...Show All

• Luis Esteban Valencia MCP Web How should I name my history tables ?

  Hi, I want to backup an important table every week in creating some history tables. I would like to create a Dts job or script to create every week a table with the day and month in its name. ( like : [important_table_09-07] , [important_table_09-14],... ) Any idea Thanks. EXEC(@Command) SP_EXECUTESQL @COMMAND HTH, Jens K. Suessmeyer. --- http://www.sqlserver2005.de --- I would only use the convention TableName_YYYYMMDD. As I guess that they will be not used that often, you should consider creating them on another filegroup and even schema (if you use SQL 2k5) than the productiv one to ease up the Backup of the objects. HTH, Jens K. Suessmeyer. --- http://www.sqlserver2005.de - ...Show All

• TiamatVenger selecting from a SP

  Hello How can I select from an output of SP . I have created to SPs, say myProc and myproc2 to test this: CREATE PROCEDURE dbo.myProc AS begin select * from STOCKS end GO CREATE PROCEDURE dbo.myproc2 as begin select sto_id from myProc end GO When I run myproc2, I get an error: "Invalid object name 'myProc'." If myProc produces a table, so why can't I select from it insert the result of stored procedure into table or temporary table of the same structure declare @temp table (sto_id int , name varchar (20 ) ) insert @temp exec myProc select sto_id from @temp Hi, You might consider using Functions in stead of stored procedures. These can be included in the Fro ...Show All

• srlito Getdate() overflowing datetime

  I have a weird error that just started showing up.  This process has run many times before and just today it started erroring. The error I get is: There was an error with input column "dtInsertTime" ( 242 ) on input "OLE DB Destination Input" ( 146 ). The column status returned was : "Conversion failed because the data value overflowed the specified type." . The weird thing is that column is added to the data flow via a derived column just before the destination and its set to GETDATE().  The destination column for that field is datetime not null with the same name.  I have litterally hundreds of packages that do the same thing (add a column set to getdate() of type dbtimestamp going into sql 2005 column wit ...Show All