Data-Driven Subscription to Excel with labeled worksheets Question

SQL Server Reporting Services

Index ‹ SQL Server

- Previous
  - 1
    - odd behaviour when I load a file into a table Package doesn't fail. How odd! Just doesn't insert them. Tag: Data-Driven Subscription to Excel with labeled worksheets Question
  - 2
    - High CPU utilization in SQL Server 2005 SP2 (CTP) More information: We made the forced parameterization change and now in addition to the above wait event, the SOS_SCHEDULER_YIELD is the another prominent wait event that shows up. Nothing else is going on this machine other than that ostress execution which looks like this: ostress -S xyz -E -d tzxyz -mstress -r1 -iC:\stress.sql -oC:\Stress_Out.txt -eC:\Stress_Err.txt -q -T30 -n100 And the Stress.sql just has a stored procedure call in it. Even if we take the call out and replace it with something as simple as : declare @i int set @i = 1 select * from table_x where col1 = @i we still see the same high CPU issues. The @i value qualifies for less than 5% of the records in the table. Please advise how to resolve this issue or if this is a known issue in SP2 that will get fixed by release time-frame. This should be very easily reproducible in any environment - all our three environments (32-bit as well as 2 64-bit) exhibit the same behavior. Tag: Data-Driven Subscription to Excel with labeled worksheets Question
  - 3
    - A third dynamic assembly loading problem ;) Thanks Niels! But the dependency tree for the assemblies I like to reference is kind of big and guess I have to catalog all of those as well (that is the assemblies that the assembly I like to reference depend on). Some of them contains unmanaged code as well but I guess as long as I set the unsafe permission mode on those I should be able to catalog those as well. Right Tag: Data-Driven Subscription to Excel with labeled worksheets Question
  - 4
    - T SQL LIKE Concerning your question, I think you need to use IN in case of LIKE. SELECT * FROM tblOne WHERE num IN (12345986, 86355634, 67375436) Greetz, Geert Geert Verhoeven Consultant @ Ausy Belgium My Personal Blog Tag: Data-Driven Subscription to Excel with labeled worksheets Question
  - 5
    - Microsoft search service kills SQL 2000 server Thanks. Sorry for the misplacement. Tag: Data-Driven Subscription to Excel with labeled worksheets Question
  - 6
    - Opening sql files opens new instance of SQL Management Studio Hi Folks, I'm sure this has been answered before but all my searches for this problem don't find what I need. It's a quirk but driving me nuts. If I have sql management studio open and I view a sql file from Vault, or double-click it in explorer, it always opens a new instance of mgmt studio. I open 5 sql files, 5 instances of mgmt studio. How do I tell my system to reuse mgmt studio to open sql files if it's open Thanks in Advance, -David Tag: Data-Driven Subscription to Excel with labeled worksheets Question
  - 7
    - check if there is an existing SQL server installation can you do this.. without actually connecting in the sql server Tag: Data-Driven Subscription to Excel with labeled worksheets Question
  - 8
    - Precedence Constraints and Sequence Containers Thanks Dave! I saw that but wasn't sure how that worked. Tag: Data-Driven Subscription to Excel with labeled worksheets Question
  - 9
    - Top N Sum and Sum for Others Thanks for your reply, but the final solution is to figure out the [Value in top 5] expression! Tag: Data-Driven Subscription to Excel with labeled worksheets Question
  - 10
    - Identity column value increments by 2 rather than 1 on insert Here are some simple test scripts to create the service broker logic: -- Configure Queue CREATE MESSAGE TYPE TestMessage VALIDATION = NONE CREATE CONTRACT TestContract ( TestMessage SENT BY INITIATOR ) CREATE QUEUE QueueTest WITH STATUS = ON , ACTIVATION ( PROCEDURE_NAME = PTest , MAX_QUEUE_READERS = 1 , EXECUTE AS 'dbo' ) CREATE SERVICE ServiceTest ON QUEUE QueueTest ( TestContract ) -- Send procedure CREATE PROCEDURE [dbo] . [PTestLogMessage] @msg_details varchar ( 390 ) AS BEGIN DECLARE @handle UNIQUEIDENTIFIER BEGIN DIALOG CONVERSATION @handle FROM SERVICE ServiceTest TO SERVICE 'ServiceTest' ON CONTRACT TestContract WITH ENCRYPTION = OFF ; SEND ON CONVERSATION @handle MESSAGE TYPE TestMessage ( @msg_details ) END -- Activation procedure CREATE PROCEDURE [dbo] . [PTest] AS DECLARE @msg VARCHAR ( 390 ) BEGIN RECEIVE TOP ( 1 ) @msg = message_body FROM dbo . QueueTest -- This insert results in the identity column value incrementing by 2 INSERT INTO t_test ( msg ) VALUES ( @msg ) END -- table CREATE TABLE [dbo] . [t_test] ( [id] [int] IDENTITY ( 1 , 1 ) NOT NULL, [msg] [varchar] (390 ) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, CONSTRAINT [PK_t_test] PRIMARY KEY CLUSTERED ( [id] ASC ) WITH ( IGNORE_DUP_KEY = OFF ) ON [PRIMARY] ) ON [PRIMARY] Tag: Data-Driven Subscription to Excel with labeled worksheets Question
  - 11
    - Getting Access Denied To FileName Error When Using the Execute Sql Task (With File Connection) into a Foreach Loop Container. Hi ALL, Getting Access Denied To FileName Error When Using the Execute Sql Task (With File Connection) into a Foreach Loop Container. Please Note : I have a folder containing .sql files. I have to dynamically loop through the files and send them as a File connection Folder to the Execute Sql Task. When I run this Package I am getting the follwoing error : [Execute SQL Task] Error: An error occurred with the following error message: "Access to the path 'C:\Projects\Fuzzy Lookup DataFlow Example\Scripts' is denied.". Also I have logged in to the machine as Administrator and to Sql Server with sa. Please help. Thanks. Regards, Salil Tag: Data-Driven Subscription to Excel with labeled worksheets Question
  - 12
    - Need help with a sp Hi Geert and thanks, In your case I want the return value id value1 value2 1 Apple Banana 1 Apple Melon 1 Grape null I am building an application which in the first window chooses a value (apples) and then depending on that value gets to choose other values (value2 bananas, melons). Then I want to fetch all things that matches apples and if they have a value2 these should be fetched first and in that case the null value2 should be ignored. Why I have a table entry 1 apple null What if the user deletes the rows with id= 1 with a value2, that fruit configuration should still exist! Please help. L Tag: Data-Driven Subscription to Excel with labeled worksheets Question
  - 13
    - How can I generate INSERT statements from Management Studio hi Mohammed, in addition to Jens, you can have a look at http://www.karaszi.com/sqlserver/info_generate_script.asp , where "several" option are listed.. for your convenience, you can perhaps try a free prj of mine, amInsert, available for free at the link following my sign.. regards Tag: Data-Driven Subscription to Excel with labeled worksheets Question
  - 14
    - how to design database for chinese and japanese characters vrkanaka wrote: I need a small confirmation regarding storing the Chinese and Japanese characters in sql server. Can we store Chinese and Japanese characters on a same database with Chinese Collation Or else we need to store it separately with respective collations. I tried to store both characters on db with Chinese collation it works but I am not so sure if it is right way to do so. Please confirm on this as we are doing research stage to build website in Chinese and japanese. Thanks in advance. The Chinese and Japanese alphabets are more than 2000 characters so you need to find the version of Chinese and Japanese and use the correct code page and collations for each language with NVarchar or NVarchar(max). Hope this helps. http://msdn2.microsoft.com/en-us/library/ms144250.aspx http://msdn2.microsoft.com/en-us/library/ms180175.aspx Tag: Data-Driven Subscription to Excel with labeled worksheets Question
  - 15
    - Get the error in dos cmd line Instead of overwriting the error log file, I would just append the error to the file instead. Code Snippet ECHO OFF OSQL -S %sqlServer% -E -b -n -i test.sql > %1 IF ERRORLEVEL 1 GOTO error ECHO "" > %1 EXIT :error @ECHO An error occured in [%errorLevel%] echo [%errorLevel%] >> %1 This way you either have a blank file, or you have a file with an error message and errorLevel code. Tag: Data-Driven Subscription to Excel with labeled worksheets Question
  - 16
    - SQL Server roles & ASP.NET 2.0 Forms Authentication Roles Apologies if my post does not fit into this forum. I initially tried the SQL Server Data Access forum but I now think my question is more security related. Is it possible for a web user who has been successfully authenticated with forms authentication to be authorised to use a SQL Server 2000 role depending on a particular ASP.NET 2.0 role that they have been authorised to use I understand that that I can assign a SQL Server 2000 role to the ASPNET or NETWORK SERVICE account but this will grant access to anonymous web users to the database role. I can ensure that I only call stored procedures which access sensitive data in web pages that are in restricted by ASP.NET roles. However, it would be nice to also restrict stored procedures via the ASP.NET 2.0 Forms Authentication roles. If this is not possible have you got any bright ideas how I could restrict access to stored procedures who are anonymous web users. Many thanks, Mark Tag: Data-Driven Subscription to Excel with labeled worksheets Question
- Next
  - 1
    - SSAS Best Practice Well just in general practice, we don't just serve up a data without something in between the data and the consumer of the data. This allows us to control business rules (i.e. what can be seen, operated on, and validated). But, having the UI consume the cube directly seems ok, since most of these operations can be controlled inside the cube. And since that is the case, connecting directly to it would be optimal, and sense performance is the overall purpose for using it, wouldn't it be ideal to take out as many moving parts as possible I guess the question is, what is the best practice, how do most people connect to a SSAS cube from the client application. Is this usually done directly or is it common to build another layer that serves up the cube, or queries the cube and serves up the data Again keeping optimal performance in mind. I know this question is hard to answer without more information regarding the system we are building, but I am just looking for a "rule of thumb" type of answer. It seems that connecting to the cube directly would be the best answer, but why would someone not want to Calculations, security, formatting, what can be viewed etc ... can all be handled directly in the cube itself. Lonnie Tag: Data-Driven Subscription to Excel with labeled worksheets Question
  - 2
    - distribution cleanup cannot clean up snapshot folder I did have to qualify the domain, as in DOMAIN\sqlrepadmin to get it to work. We have same login for 2 different domains. What an amazingly simple and elegant solution. I feel silly that I did not think of this. Thank you Hilary. Tag: Data-Driven Subscription to Excel with labeled worksheets Question
  - 3
    - define/set parameter values in Management Studio? If you have a query w/ a parameter in it (copied from reporting services, but now working with management studio), how do you declare the parameter and set its value in sql server management studio I've had to unfortunately find all occurrences of the parameters and replace their values each time I want to change them when executing the mdx. Tag: Data-Driven Subscription to Excel with labeled worksheets Question
  - 4
    - Best practices - currency elements The XML is prepared by an outside source, in fact it comes from an EDI message. I'm wondering whether - more like just how - to raise it with them as an improvement they should make. Thanks. Josh Tag: Data-Driven Subscription to Excel with labeled worksheets Question
  - 5
    - SQL Command Datareader Create another connection to the other database, read the data you need and generate the sql dynamically... I don't think that you can do this on the same reader since a reader always has one connection ... Tag: Data-Driven Subscription to Excel with labeled worksheets Question
  - 6
    - suser_sid returns value for nonexistent login The CREATE USER script fails with the same error. The issue is not a permissions issue. The login being used to do all of this is in the security administrator role, and I also tried setting the system administrator role and the problem remained. As I said, the problem is that suser_sid returns a value even though the login is not in sysusers. Your server_principals query also returned no data. I ran sp_addlogin and checked sysusers and server_principals and it was there. I ran sp_droplogin and it was no longer in sysusers or server_principals yet suser_sid still returnes a value. -- Paul Tag: Data-Driven Subscription to Excel with labeled worksheets Question
  - 7
    - Count the occurance of a character in a string Freakin' brilliant. Tag: Data-Driven Subscription to Excel with labeled worksheets Question
  - 8
    - What technique to use to update production table from staging table Would replication help here Just curious, as I don't know the answer to that.... Tag: Data-Driven Subscription to Excel with labeled worksheets Question
  - 9
    - truncation error message I have and I still get the error message if there is more then 50 characters in the field. [Flat File Source [1]] Error: Data conversion failed. The data conversion for column "NAME" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.". [Flat File Source [1]] Error: The "output column "NAME" (26)" failed because truncation occurred, and the truncation row disposition on "output column "NAME" (26)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component. [DTS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Flat File Source" (1) returned error code 0xC0202092. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure. now the length for this field is set to 50 in the derived colum field but I'm still getting the above errors Tag: Data-Driven Subscription to Excel with labeled worksheets Question
  - 10
    - Error in executing stored proceduNot a valid identifier First off, I'm not sure why you're constructing a dynamic select inside your procedure...the procedure should be the select statement, using any input parameters you defined. But to solve the problem, you need to change exec @sqlstr to exec(@sqlstr) I'd rewrite the entire piece of code... Tag: Data-Driven Subscription to Excel with labeled worksheets Question
  - 11
    - OWC scans all partitions (SSAS 2005) Hello, I have a cube with three partitions. Each partition holds data for the years 2005,2006 and 2007 respectively. In our web application we are creating OWC Pivot Table and putting "date dimension" in the filter area automatically. Altough only "2007-01-01" is included in filter, when we trace the query from profiler we saw that all partitions are read. Is there anyone encountered the same thing By the way, storage modes for all partitions are MOLAP. Nilgun Celikok Tag: Data-Driven Subscription to Excel with labeled worksheets Question
  - 12
    - SQL Server Mobile Here is a simple code example using Northwind.sdf Dim cn As New SqlServerCe.SqlCeConnection("datasource=northwind.sdf") Dim cmdOrders As SqlServerCe.SqlCeCommand Dim cmdOrderDetails As SqlServerCe.SqlCeCommand ' required for relationship Dim columnParent As DataColumn Dim columnChild As DataColumn Dim relation As DataRelation ' create dataset with 2 tables cn.Open() cmdOrders = cn.CreateCommand cmdOrders.CommandText = "SELECT * from orders" Dim daOrders As New SqlServerCe.SqlCeDataAdapter(cmdOrders) Dim dtOrders As New Data.DataTable("Orders") daOrders.Fill(dtOrders) cmdOrderDetails = cn.CreateCommand cmdOrderDetails.CommandText = "SELECT * from orderdetails" Dim dtOrderDetails As New Data.DataTable("OrderDetails") Dim daOrderDetails As New SqlServerCe.SqlCeDataAdapter(cmdOrderDetails) daOrderDetails.Fill(dtOrderDetails) Dim ds As New DataSet ds.Tables.Add(dtOrders) ds.Tables.Add(dtOrderDetails) 'now add relationship with constraints columnParent = dtOrders.Columns("OrderID") columnChild = dtOrderDetails.Columns("OrderID") relation = New DataRelation("orders", columnParent, columnChild, True) Tag: Data-Driven Subscription to Excel with labeled worksheets Question
  - 13
    - Conditional Bulk insert Hi I am doing bulk insert as follows. The @lastUpdate, @filePath, @formatFile comes as a parametes to stored proc INSERT INTO Categories SELECT CategoryId, @LastUpdate FROM OPENROWSET ( BULK @filePath , FORMATFILE = @formatFile, FIRSTROW = 2 ) AS a This works fine for me. But my new requirement is that i shouldn't insert the CategoryId if it exists How can we have conditional bulk insert i am using Bulk insert as the file might have millions of category Ids. Please provide your inputs that executes much faster Best Regards, ~Mohan Babu Tag: Data-Driven Subscription to Excel with labeled worksheets Question
  - 14
    - XML Configuration Issue Hi I don't have any Script tasks in the package. The package is made up of a Transfer Database task, and many Execute SQL and Data Flow tasks (OLE DB Source & Destination and Derived Columns tasks). The package is basically used to to warehouse the data in our system. The variables that throw the errors (about 12 in total) are the ones being set through the XML Configuration. These are concerned with setting DataSources & the Source/Destination settings in the Transfer DB task. The ones that are set through .NET code do not throw the error. Hope this makes things clearer. Thanks for the help Tag: Data-Driven Subscription to Excel with labeled worksheets Question
  - 15
    - Management Studio and Enterprise Manager on Same Machine? DotNetFun wrote: Yes, it's OK, I've done it. But it's not recommended for a production machine. Uninstalling either may cause either to not function properly. Thanks for your reply. I went ahead and tried to install the SQL 2000 client tools on my laptop (which has the SQL 2005 installation). It was the only option I had. Anyway, it installed fine, but gave me the following error when I started EM: mmc.exe - Entry Point Not Found --------------------------- The procedure entry point ProcessExecute@@YAXPAUHWND__@@PBG1@Z could not be located in the dynamic link library SEMSFC.dll. --------------------------- I get a couple of those and then a seeminly unending supply of: --------------------------- SQL Server Enterprise Manager --------------------------- Connection to application object failed. Ensure that no program modules have been deleted. --------------------------- So, it looks like it's not going to work for me. Bummer. I'll just have to Remote Desktop to another machine when I need to. Thanks, though! Tag: Data-Driven Subscription to Excel with labeled worksheets Question
  - 16
    - How to do this in SSIS? John, thanks for your reply. >You could use a Derived Column transformation to do this, but what is it that you want to do with the values I want to store the value as an output column. For this example: ColumnA ------------------------------------------ abc=123|xyz=6789|temp=99 I will need ColumnABC value = 123, ColumnXYZ value = 6789, and ColumnTemp value = 99. For the column name, I don't care about that, all I need is the value which is the value after the equal sign up to the |. If it is do-able thru Derived Column, can you please provide the syntax Thank you very much. Tag: Data-Driven Subscription to Excel with labeled worksheets Question

BrandonR

I've created a data-driven subscription that works great and delivers an Excel file to a share. The SSRS report contains several reports/tables on one page and when displayed to Excel it creates separate tabs due to the page breaks, which is wanted.

Is it possible to provide a name to each of the tabs/worksheets that are created in the Excel file By default, Excel will name them as Sheet1, Sheet2, Sheet3, etc.

Thanks!

Re: Data-Driven Subscription to Excel with labeled worksheets Question

Scott Thornburg

A few months ago there was a thread on this:

http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=1713177&SiteID=1

which notes that out-of-the-box, SSRS does not support named tabs when multiple pages are output to Excel.

That said, Lisa Nicholls recently provided a link at the end of the thread to her blog that discusses how to achieve your goal using XML output and XSLT. You might give this a try to see if it works for you.

http://spacefold.com/lisa/post/Changing-the-Sheet-names-in-SQL-Server-RS-Excel-QnD-XSLT.aspx

Scott

Re: Data-Driven Subscription to Excel with labeled worksheets Question

Philippe Cand

My be after MS has integrated SoftArtisan's ExcelWriter technology I keep an eye on that.

Re: Data-Driven Subscription to Excel with labeled worksheets Question

BrandonR

Thanks Scott...appreciate the info. I'll give it a try.

-Brandon