Emailing Report

SQL Server Integration Services

Index ‹ SQL Server

- Previous
  - 1
    - Full text prefix search for phrase with noise word? I have data (in SQL Server 2005 SP2) that looks like: ID, Name 1, elbonia university of medical science 2, elb univ med sci 3, elbonia university, med sci 4, elbonia has universities, blah blah blah medium sized scientists And a query that looks like: SELECT ID FROM tbl WHERE CONTAINS(Name, '"elb univ med sci*"') I want it to get 1-3 and not 4, but it just gets 2 and 3. I think the noise word 'of' is throwing if off but if I try: SELECT ID FROM tbl WHERE CONTAINS(Name, '"elb univ of med sci*"') I get nothing! WTF Does a noise word break prefix searches The only message is " (0 row(s) affected)" SELECT ID FROM tbl WHERE CONTAINS(Name, '"elbonia university of medical science"') returns 1 but add the * for stemming: SELECT ID FROM tbl WHERE CONTAINS(Name, '"elbonia university of medical science*"') and it also returns nothing. Thanks for looking! -Andrew Tag: Emailing Report
  - 2
    - SQL Server Express error when using NUnit Well, is the database server attached or user-attached. Seems that another process has still its hand on the database. Perhpas you might also could provide your detailed way on how you experiencing the problem. I am not quite sure if I got your point when that happened. Jens K. Suessmeyer --- http://www.sqlserver2005.de --- Tag: Emailing Report
  - 3
    - Best practice to copy a lot of data between databases Hi, I have a database on sqlserver 2000 which contains some tables which receive Create/Read/Update statements from applications, and +-5 tables which contain a sort of read-only data: the applications are only reading from these tables. Sometimes these 5 tables need to be updated with new data. Currently I am doing this as follows: Execute a long-running operation (e.g. 1 week), which will add new data, against a Test-database (which is a restore from a backup of the original database) Some people do some checks to see if the new data in the Test-database is correct If (2) is OK, the only thing which needs to be done is copying the data of the 5 tables in testdatabase to the 5 tables of the production-database. For (3), I currently use a DTS-package which consists of a "Copy SQL Server Objects"-task. This task is configured to copy the 5 tables (objects) from testdatabase to productiondatabase. The data in these 5 tables is around 20GB, and this task takes a lot of time compared to a backup/restore of the same size of data. I already tried to speed it up by creating different filegroups and wanted to restore only 1 filegroup but you can read here that making a filegroup backup on a testdatabase and trying to restore it on a production database won't work. Is there some other way to speed this up Is my current way of working good practice Thx! Tag: Emailing Report
  - 4
    - DTS old style and Dynamic Properties Tasks You set global variables by clicking package > properties and then selecting the 'global variables' tab. Then you refer to this variable in your dynamic properties task. I've also seen packages that use an activeX script task to read the values for global variables from a sql server table. Unfortunely, I no longer have a code example of how to do it that way. I guess what is the reason that name of the ini file has to change Couldn't you just change the values inside of the ini file Also, perhaps it's time just to rewrite the packages in SSIS. Tag: Emailing Report
  - 5
    - Help plz - need query to get the following kind of output And who knows that you're talking about 2000. select t1 . columnA , ( select min ( columnA ) from myTable t2 where t2 . columnA > t1 . columnA ) - t1 . columnA as columnB from myTable t1 order by t1 . columnA Tag: Emailing Report
  - 6
    - RAISERROR does not cause SQL task to fail - why? Thanks for the replies. Phil, to answer your question, the error message is displayed on the Messages tab in Management Studio. I'm including two versions of the sproc as well as the invocation code I used to run it within Management Studio. I'm successfuly capturing the error info in the CATCH block and passing it back to SSIS. The point of having the RAISERROR at the end of the CATCH is to make SSIS fail the task. This is the sproc with no CATCH: Code Snippet -- This is the stripped procedure with no CATCH block. The OUTPUT parms do nothing. -- This sproc will cause an SQL task to fail. ALTER PROCEDURE [dbo].[sp_ThrowException] @ERROR_NUMBER INT OUTPUT, @ERROR_MESSAGE NVARCHAR(4000) OUTPUT, @ERROR_SEVERITY INT OUTPUT, @ERROR_STATE INT OUTPUT, @ERROR_PROCEDURE NVARCHAR(126) OUTPUT, @ERROR_LINE INT OUTPUT, @FormattedMessage NVARCHAR(4000) OUTPUT AS BEGIN SET NOCOUNT ON -- this forces a "divide by zero" exception SELECT 1 / 0 AS 'TRY' FROM view_OilRigs_REPORT R END This is the sproc as I really want it. It has the RAISERROR in the CATCH block. Code Snippet -- This is the desired procedure with the CATCH block. The error information is being returned -- to the SQL task through the OUTPUT parms. -- This sproc will allow an SQL task to succeed. ALTER PROCEDURE [dbo].[sp_ThrowException] @ERROR_NUMBER INT OUTPUT, @ERROR_MESSAGE NVARCHAR(4000) OUTPUT, @ERROR_SEVERITY INT OUTPUT, @ERROR_STATE INT OUTPUT, @ERROR_PROCEDURE NVARCHAR(126) OUTPUT, @ERROR_LINE INT OUTPUT, @FormattedMessage NVARCHAR(4000) OUTPUT AS BEGIN TRY -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON -- this forces a "divide by zero" exception SELECT 1 / 0 AS 'TRY' FROM view_OilRigs_REPORT R END TRY BEGIN CATCH SELECT @ERROR_NUMBER = ERROR_NUMBER(), @ERROR_MESSAGE = ERROR_MESSAGE(), @ERROR_SEVERITY = ERROR_SEVERITY(), @ERROR_STATE = ERROR_STATE(), @ERROR_PROCEDURE = ERROR_PROCEDURE(), @ERROR_LINE = ERROR_LINE(), -- this renders the error message in command line format @FormattedMessage = 'Msg ' + CAST(ERROR_NUMBER() AS NVARCHAR(20)) + ', ' + 'Level ' + CAST(ERROR_SEVERITY() AS NVARCHAR(20)) + ', ' + 'State ' + CAST(ERROR_STATE() AS NVARCHAR(20)) + ', ' + 'Procedure ' + ERROR_PROCEDURE() + ', ' + 'Line ' + CAST(ERROR_LINE() AS NVARCHAR(20)) + ', ' + 'Message: ' + ERROR_MESSAGE() -- Use RAISERROR inside the CATCH block to return error -- information about the original error that caused -- execution to jump to the CATCH block. RAISERROR ( @ERROR_MESSAGE, -- Message text @ERROR_SEVERITY, -- Severity @ERROR_STATE -- State ) END CATCH I use this code to run the sproc in Management Studio. Code Snippet -- I use this to run the sproc within Management Studio. SET NOCOUNT ON; DECLARE @local_ERROR_NUMBER INT DECLARE @local_ERROR_MESSAGE NVARCHAR(4000) DECLARE @local_ERROR_SEVERITY INT DECLARE @local_ERROR_STATE INT DECLARE @local_ERROR_PROCEDURE NVARCHAR(126) DECLARE @local_ERROR_LINE INT DECLARE @local_FormattedMessage NVARCHAR(4000) EXEC [dbo].[sp_ThrowException] @ERROR_NUMBER = @local_ERROR_NUMBER OUTPUT, @ERROR_MESSAGE = @local_ERROR_MESSAGE OUTPUT, @ERROR_SEVERITY = @local_ERROR_SEVERITY OUTPUT, @ERROR_STATE = @local_ERROR_STATE OUTPUT, @ERROR_PROCEDURE = @local_ERROR_PROCEDURE OUTPUT, @ERROR_LINE = @local_ERROR_LINE OUTPUT, @FormattedMessage = @local_FormattedMessage OUTPUT SELECT @local_ERROR_NUMBER AS ERROR_NUMBER, @local_ERROR_MESSAGE AS ERROR_MESSAGE, @local_ERROR_SEVERITY AS ERROR_SEVERITY, @local_ERROR_STATE AS ERROR_STATE, @local_ERROR_PROCEDURE AS ERROR_PROCEDURE, @local_ERROR_LINE AS ERROR_LINE, @local_FormattedMessage AS FormattedMessage Thanks for looking at this. BCB Tag: Emailing Report
  - 7
    - Moving Average using Select Statement or Cursor based? Hi Kent, Is this solution based on an ordered update If so, then it is not a reliable one. For this kind of calculation (ordered calculations), a cursor based solution uses to perform better than a set one. That is the reason why Itzik Ben-Gan and Sujata Mehta sent an enhancement request to Microsoft, to improve the OVER clause. Here is the link to the document, It is worth to read it. Inside the document, you will find the links to the entries of the suggestions in Microsoft connect, I encourage everyone that read the document, please, to vote. OVER Clause and Orderes Calculations - Feature Enhancements Request by Itzik Ben-Gan and Sujata Mehta (first link after Whitepapers) http://www.sql.co.il/books/insidetsql2005/resources.htm Here Is the cursor based solution, notice that the table will be scanned just once, contrary to the set based one. Code Snippet use tempdb go create table dbo . item_txns ( id integer , date datetime , type varchar ( 10 ), qtyin integer , cost_out_amt decimal ( 12 , 2 ) ) go declare @avg table ( id integer , date datetime , type varchar ( 10 ), qtyin integer , cost_out_amt decimal ( 12 , 2 ), seq integer primary key , moving_cost decimal ( 12 , 2 ), moving_qty integer ) insert into dbo . item_txns select 1 , '01/01/2007' , 'PURCHASE' , 10 , 1000 union all select 2 , '01/01/2007' , 'PURCHAES' , 5 , 1100 union all select 3 , '01/01/2007' , 'SALES' ,- 5 ,null union all select 4 , '02/01/2007' , 'Purchase' , 20 , 9000 union all select 5 , '02/01/2007' , 'SALES' ,- 10 ,null union all select 5 , '02/01/2007' , 'purchase' , 50 , 8000 union all select 6 , '03/01/2007' , 'Sales' ,- 10 ,null union all select 7 , '01/01/2007' , 'Purchase' , 20 , 12000 declare @id integer declare @date datetime declare @type varchar ( 10 ) declare @qtyin integer declare @cost_out_amt decimal ( 12 , 2 ) declare @Seq int declare @movingCost decimal ( 12 , 2 ) declare @movingQty integer declare c cursor fast_forward read_only for select id , date , [type] , qtyin , cost_out_amt from item_txns order by date , id , [type] , qtyin set @Seq = 0 set @movingCost = 0 set @movingQty = 0 open c while 1 = 1 begin fetch next from c into @id , @date , @type , @qtyin , @cost_out_amt if @@error != 0 or @@fetch_status != 0 break -- please, use better error handle set @Seq = @Seq + 1 set @cost_out_amt = coalesce ( @cost_out_amt , @qtyin * isnull (( @movingCost / nullif ( @movingQty , 0 )), 0 )) set @movingCost = @movingCost + coalesce ( @cost_out_amt , @qtyin * isnull (( @movingCost / nullif ( @movingQty , 0 )), 0 )) set @movingQty = @movingQty + isnull ( @qtyin , 0 ) insert into @avg ( id , date , type , qtyin , cost_out_amt , seq , moving_cost , moving_qty ) values ( @id , @date , @type , @qtyin , @cost_out_amt , @Seq , @movingCost , @movingQty ) end close c deallocate c select * from @avg order by Seq drop table dbo . item_txns go AMB Tag: Emailing Report
  - 8
    - Grouping Hi, I have a stored procedure which is returning results in the following format: No.of substances Count 1 29 2 89 3 876 .. .. 15 56 16 89 Now i need to display like this: No.of substances Count % 1 20 2 89 .. .. >=9 8766 Total All the substances which are >=9 have display no.of substances =>9 and Count is sum of all counts which have the no.of substances>=9 How to achieve this Thanks in advance Tag: Emailing Report
  - 9
    - Problem with "phantom rows" A conditional split component just uses expressions to test rows. If the expression evaluates to true, then the row goes down that output path. Since you are working with the row as one big column, here's a sample config: OUTPUT NAME: BalanceRows Expression: FINDSTRING("OPENING BALANCE",[Column],1) > 0 || FINDSTRING("ENDING BALANCE",[Column],1) > 0 Then, back in the data flow, just grab the green arrow and hook it to the next component in line. It will prompt you for which output you want to use. Select the "BalanceRows" output. Tag: Emailing Report
  - 10
    - SQL Express Help Files Where do I find the Help files for SQL Express. When I click on help it says, help is not available. Tag: Emailing Report
  - 11
    - Design question - Foreign key indexes Alejandro / Edward - Thank you very much for your quick responses - both very good points. Edward - to clarify, we are using service broker which receives xml messages from our source system, which uses stored procs to update our data warehouse. During peak times we receive hundreds of thousands of messages each day, and each one will cause updates / inserts / deletes in our dimension and fact tables. Hence my consideration about index changes slowing things down. When we process, we are doing a full process, so Alejandro's response about doing full table scans is a very realistic possibility. The only way to truly tell what is going on and if there can be some performance boost is to run the index tuning wizard - a very good suggestion. I will check it out! Thanks again, John p.s. Alejandro - you would be suprised what the Enterprise Reporting solution has evolved into - I believe Robert Skoglund gave you guys a nice presentation a few months back... Tag: Emailing Report
  - 12
    - ADO Error: an insufficient number of arguments.... Well, actually the answer is in your question. It did not need a parameter in the past while it was a view so the other views were using it. After a while (according to your text) you turned it in to a function and add a parameter but you did not change your old views which uses this ex-view to use a parameter. You still call this function from your views without a parameter and that's why SQL Server yells for a parameter. Tag: Emailing Report
  - 13
    - Replication looking for errant sysserver links Do "select * from sysservers" to see if you have any rogue entries in there. You can remove the incorrect ones via sp_dropserver. Tag: Emailing Report
  - 14
    - When to reindex? We have a fairly large database (320 tables, 70GB+) on SQL 2000 where reindexing using 'dbcc dbreindex' currently takes almost 3 hours. We are reindexing all tables and dbreindex takes up 80-90% of the system cpu making any other job slow. All indexes are setup with a fill factor of 70%. As the database grows we would like to minimize the time spend on reindexing. One approach could be to use the output from 'dbcc showcontig' for deciding on on whether to reindex a table or not. E.g. use 'ExtentFragmentation > 10%' as the criteria for starting reindexing on a particular table. Is this a suitable approach or are there any other better parameters to consider Rgds Bertrand Tag: Emailing Report
  - 15
    - Calling CLR stored procedure from within a CLR table-valued function giving errors We are trying to create a TVF that executes a CLR Stored Procedure we wrote to use the results from the SP and transform them for the purposes of returning to the user as a table. Code Snippet [ SqlFunction ( FillRowMethodName = "FillRow" , TableDefinition = "CustomerID nvarchar(MAX)" , SystemDataAccess = SystemDataAccessKind .Read, DataAccess = DataAccessKind .Read, IsDeterministic= false )] public static IEnumerable GetWishlist () { using ( SqlConnection conn = new SqlConnection ( "Context Connection=true" )) { List < string > myList = new List < string > (); conn.Open (); SqlCommand command = conn.CreateCommand (); command.CommandText = "GetObject" ; command.Parameters.AddWithValue ( "@map" , "Item" ); command.CommandType = System.Data. CommandType .StoredProcedure; using ( SqlDataReader reader = command.ExecuteReader ( System.Data. CommandBehavior .SingleRow )) { if (reader.Read ()) { myList.Add ( reader[0] as string ); } } return ( IEnumerable )myList; } } When command.ExecuteReader is called, I am getting an "Object not defined" error. However, the stored procedure can be used in SQL Management Studio just fine. Code Snippet EXEC GetObject 'Item' Is there some sorf of trick I am missing Thank you! Tag: Emailing Report
  - 16
    - Scheduling an automatic backup Thanks for your reply.iam new to the database side,what i need is i want script for automatic database backup and send me mail for every day after the backup is done succesfully or not, in sql server 2005 management studio.did you able to help me pls........... Tag: Emailing Report
- Next
  - 1
    - Documents in VLDB Ike, Could you provide with two more pieces of information: 1) The query that you use to retrieve the data 2) Is this 24 seconds the time it takes on the first or the subsequent tries Thanks, Humberto Acevedo SQL server team Tag: Emailing Report
  - 2
    - Scheduled Backup suddenly started giving errors Inside the maintanence plan check the connection manager whether you are connecting to the local server. Make sure you have right alias created and try a test connection from connection manager itself. Tag: Emailing Report
  - 3
    - OLEDB Access PB Hi I definite a source OLEDB MS Access and my fields of the type ��Text�� are seen in type of field DT_WSTR (Unicode) instead of DT_STR. I do not include/understand why and like then, I must integrate them in fields varchar and not nVarchar, I do not find the solution thank for your solution Tag: Emailing Report
  - 4
    - Maximum number of users (licensing) I need a small SQL server for up to 10 users. If I understand the EULAs and FAQs correctly, there is no limit on the number of users imposed by the SQL Server 2005 Express license. If so, then the number of users is only limited by the OS's license so all I need is SQL Server 2005 Express (free), Windows XP Pro (10 users limit) and no CALs for either Windows or SQL Server. I'd be thankful if someone could verify this. Tag: Emailing Report
  - 5
    - Scheduling sqlcmd script in sql agent In the Job Step Properties window, in the [General], click on [Type]. There you can select [Operation System ...] Put your script there. ( SQLCmd runs SQL commands in the OS environment, CMDExec runs OS commands in the SQL Server environment -including SQLCmd.exe. ) However, if you 'should' be able to directly run SQLCmd script as a Job step. If you posted the command line, we may be able to better advise you about the most efficient method to accomplish your task. Tag: Emailing Report
  - 6
    - measure group not showing any data inspite the facttable having data Hi, I could successfuly deploy and process the cube. But when I go to the 'Browse' tab of the cube and drag and drop a few dimensions and measures I don't see any data. I even used simple MDX queries and even that returns 'NULL' facts. I have set the 'unknown' member as visible in the time dimension because some of the dates in the fact tables are null. Any thoughts on this would be greatly appreciated. Thanks. Tag: Emailing Report
  - 7
    - How do i change collation? If you want to change the default collation for the server, the easiest way may be to uninstall SQL Server, and re-install SQL Server, selecting the proper collation. Of course, that depends upon your current usage, number of databases, etc. You can set default collations for databases, but if there are tables in the databases, you will have to manually change each table. (New tables will take the default collation.) Tag: Emailing Report
  - 8
    - connecting to sql server from mobile device on the emulator or on the device Tag: Emailing Report
  - 9
    - Scheduling report delivery for the first WORKING day in the month? Has anyone figured out a good way of doing this The only suggestions I have seen along these lines involve scheduling the job every day and then manually editing the SQL Agent job that is created. However the problem with this is that it completely removes the ability for non programmers to set the schedule themselves using the web interface. Any ideas Tag: Emailing Report
  - 10
    - Pivot Table Bug with AdventureWorks Is it possible that you have the Country attribute from the Geography dimension selected with the State-Province attribute from the Customer dimension (or vice versa) Both dimensions contain geography attributes. While they are named the same, they represent attributes in different dimensions. Thus, they are related only through the fact table. That means that filtering of one will not filter values from the other (although the filtering does affect the fact data as expected). Dave Fackler Tag: Emailing Report
  - 11
    - Pivot Select As you noticed, If your 'control table' has rows all of your Fiscal Years, you retreive all Fiscal Years. The only way it would work without a WHERE clause is if the 'control table' has only one row of data for the current FiscalYear. If you want only one fiscal Year, then you will have to specify which one. The query processor can't read your mind. You could still 'automate' the process, for example, assuming your FiscalYear begins July 1: WHERE e.FiscalYear >= cast( cast( ( year( getdate() ) - 1) AS char(4)) + '0701' AS datetime ) AND e.FiscalYear < cast( cast( ( year( getdate() )) AS char(4)) + '0701' AS datetime ) Will always derive the Fiscal year for the current date. Tag: Emailing Report
  - 12
    - Difference of compact edition and mobile edition Hello I recently downloaded the sql mobile edition, and then I found out about the compact edition. I understand that these are relational lite version of sql server that can run on mobile devices. However, what is the difference between the CF and mobile edition Why should I choice one over the other I have just heard about sql everywhere, seems there are all these edtions and how would you choice the right tool for the right job Many thanks if someone could explain further, Steve Tag: Emailing Report
  - 13
    - how sql server handles nvarchar Hi i'd like to know about varchar , when sql store data as nvarchar it store the actual length besides 2 bytes but the question is if there is another field does sql server store the next field besids nvarchar so there is no seprated bytes and is this exist and i update data of nvarchar does sql sqerver shifted the reamin data exist in the same track please send me the answering Tag: Emailing Report
  - 14
    - Error Message when restoring Database I am receiving the following error message when I am trying to restore a database in SQL Express 2005. Has anyone ever seen it before microsoft.sqlserver.express.connectionInfo Tag: Emailing Report
  - 15
    - SSRS Report Parameters �� Either use this statement below to put in a stored proc and call it from SSRS or write it a in the Report Designer data tab query desinger as a data set. I used the northwind as an example so ignore my column and table data and plug yours in except for the where clause . SELECT [Order Details].UnitPrice, [Order Details].Quantity, [Order Details].Discount, Products.ProductName, Suppliers.CompanyName AS Supplier, Orders.OrderDate, Suppliers.City, Suppliers.Region, Suppliers.Country FROM Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID INNER JOIN Products ON [Order Details].ProductID = Products.ProductID INNER JOIN Suppliers ON Products.SupplierID = Suppliers.SupplierID WHERE (Orders.OrderDate BETWEEN @StartDate AND @EndDate) ORDER BY Orders.OrderDate �� Make sure you know what the range is in the dates and the format it should create little maps by the preview boxes. �� Next Click the Edit Selected Data button and click on the parameters tab and make sure it reads @StartDate [ Parameters!StartDate.Value @EndDate [ Parameters!EndDate.Value �� Then go to Report --> Parameters make sure you have BeginDate and EndDate both set t to datetime and prompt them however you want them to display don't check anything under the next area of textboxes. Under Available queries check non-queried and under default, values select none. �� Create a header and place a textbox in there with the following expression to validate the dates you are select will be displayed so we know its pulling the right range. You can delete it after. Inside that text type the following expression: -what ever data you want to type insided the quotes won't matter it will just display as regualar text. = "For sales between " & Parameters!BeginDate.Value & " and " & Parameters!EndDate.Value Tag: Emailing Report
  - 16
    - Email task - no error - no mail Dhanasu wrote: I am trying to explore the email task in SSIS. I have a task which has only the email task. When I run its not throwing any error instead it says successfuly run but I am not recieving mail. I dont know how to trace this, could someone help me on this Perhaps it's getting caught up in a spam filter. Or the SMTP server isn't letting that e-mail through. Have you talked to the server admin Tag: Emailing Report

shah_hs

I need to export a view into excel and mail to my manager daily morning. Is there a way i can automate this process. I know how to convert view to excel but i need a better solution to automate this process with emailing to my mgr. Please suggest a better approach of doing this.

Thanks,

Top

Re: Emailing Report

Phil Brammer

The send mail task accepts the use of file attachments.

You are using SSIS, right

Top

Re: Emailing Report

jwelch

Or you might want to consider Reporting Services for this.

Top

Re: Emailing Report

shah_hs

Thats true but how; Firstly i need to convert view to excel and then send excel sheet as attachment in email. Could you pls. explain how i could do this. I am a newbie.

Thanks

Top

Re: Emailing Report

jwelch

If you are going to use SSIS, create a new package, add a data flow task with an OLEDB source that retrieves the view. Add an Excel destination to the data flow to capture the results. Back in the control flow, add a Send Mail task to send the email.

If you want to do it in Reporting Services, post your question in the RS forum (http://forums.microsoft.com/MSDN/ShowForum.aspx ForumID=82&SiteID=1) and I'll answer it there.

Top