Generating Custom Messages to User

Hello everyone,

i'm using a foreach loop container to read a group of excel files and pass their information to a Sql Server database. The process runs well but sometimes there could be some excel files that may not be processed correctly so i'm using transacctions to continue to process on the other files, But i'd like to generate a message everytime an excel file is or not processed. I thought that i could generate a flat file to do it, but is there any other way to accomplish this? I'm also generating a log file (on xml format), but It seems too much information for an end-user.

any suggestions?

regards.

You can generate messages that automatically get captured by your log provider. Is this what you want to do?

-Jamie

|||Hello Jamie

I'm actually using a log provider, so i get the log of the progress.

My solution was to get into the log (which is an xml output file) two kind of messages: the first one, an script that Logs a new "Sucess file" message and another script task which says "Failure process file". So at the end of the tasks i need to run, i get just one of the script tasks to log into my file.

But is there any other way to do it?.

regards

Generating a Group of Reports

We are thinking of a possible requirement for our custom application (which
will use Reporting Services), but we are unsure if it is possible.
Say you have 20 individual reports. And, these 20 reports are split up into
4 report groups (5 reports per report group). This is more of a logical
grouping (or maybe the report groups represent folders on the report
server). Anyhow, is it possible to generate the entire report group (thus
generating all 5 reports in the report group at one time) instead of having
to generate each individual report? Does Reporting Services support such an
idea?
Thanks.Yeah, I have a similar question(s).
Is it possible to have 5 separate reports (RDLs) and when they are executed
have the output of each report concatenated into 1 long report (probably
with a page break between each report)?
Or, does one have to loop through each report, calling it via the URL
method, and displaying each report in a separate browser instance (thus 5
separate windows opened to see all five reports)?
Or'?
Any help is appreciated. Thanks in advance.
"Dex" <dex@.yahoo.com> wrote in message
news:%23dePSvpmEHA.3396@.tk2msftngp13.phx.gbl...
> We are thinking of a possible requirement for our custom application
(which
> will use Reporting Services), but we are unsure if it is possible.
> Say you have 20 individual reports. And, these 20 reports are split up
into
> 4 report groups (5 reports per report group). This is more of a logical
> grouping (or maybe the report groups represent folders on the report
> server). Anyhow, is it possible to generate the entire report group (thus
> generating all 5 reports in the report group at one time) instead of
having
> to generate each individual report? Does Reporting Services support such
an
> idea?
> Thanks.
>

Generate custom primary key fields

Hi all,

I've recently dabbled into the world of Stored procedures and Triggers but am having some issues trying to implement some functionality.

What I want to accomplish is to either create a SP or Trigger (I'm still trying to figure out the prime differece between them) that takes values in, concatenates them, adds an increment and then saves it into the assigned column.

Say for example I have a company name, and a region
Name:'Traders Inc"
Region:KEN (Kenya).

What I want my sp to do is take the first four letters of the name, add a dash, concat the region and add an increment starting from 001 thereby giving me TRAD-KEN-001. Incase I put in another entry, it should do the same and start it at 001 as well. Only when the first two variables are the same should the number be incremented. I.e if we have another entry
Name: Tradine Jewelers",
Region: Kenya;

It should calculate and give me a value TRAD-KEN-002. This is what I want my sp to do.

I can experiment and get the concats etc to work but my confusion kicks in on how I integrate 'If' statements and how SQL actually manipulates vinputted values and where (and how) I get the value of the last increment.

Values will be passed in from my Business Logic Layer but I am totally lost after that i.e. Should I use a SP or Trigger, how do I get the values into one column, how do I integrate the IF statemnets to get the result that I am getting. The only part I understand is the Insert but thats about it.

Any help on flow, functions and commands would be wonderful with where the GO and EXECs should go in and how it will store variables temporarily.

Thanks in Advance.

I'd just like someone to set me off in the right direction.

Thanks.

|||

Here is one idea, perhaps still 'half baked', so take it as a seed, not a fully grown solution.

Column for Name
Column for Region
Column for Sequence

Column for Key Value (Concatenated fields); this column will have a UNIQUE INDEX -which will serve 'almost' as good as a Primary Key -but will allow the initial INSERT to succeed even with NULL values.

Use a Trigger (the difference between a Trigger and a Stored Procedure is that a Stored Procedure executes when called, whereas a Trigger automatically executes upon change to data in a table.)

Create an FOR INSERT Trigger. When a new row is inserted, search for the max(Sequence) WHERE MyTable.Name = inserted.Name AND MyTable.Region = Inserted.Region. Add one to that value, put it in the Sequence Column, and then concatenate the three columns together to make your key value. Store in the Key value column. (It may be necessary to use an INSTEAD OF Trigger rather than a FOR INSERT Trigger.)

I hope that this helps point you in a direction that will work for you.

|||Hi,

Assuming the table structure is like
create table table1 (AreaName varchar(100), Region varchar(100), CodeGenerated varchar(100))

You can use something like this or you can convert it into trigger.

CREATE PROCEDURE InsertData
@.AreaName VARCHAR(100),
@.Region VARCHAR(100)
AS
BEGIN
DECLARE @.CodeGenerated VARCHAR(100), @.Counter varchar(10)

SET @.CodeGenerated = substring(@.Areaname, 1, 4) + '-' + substring(@.Region,1,3) + '-'

SELECT @.Counter = ISNULL(MAX(REPLACE(CodeGenerated,@.CodeGenerated, '')),0)+1 FROM Table1
WHERE AreaName = @.AreaName AND Region = @.region

SET @.CodeGenerated = UPPER(@.CodeGenerated) + REPLICATE('0',3-len(@.Counter)) + convert(VARCHAR,@.Counter)

INSERT table1 VALUES(@.AreaName, @.Region, @.CodeGenerated)
END

-- Code to execute stored procedure
EXEC InsertData @.AreaName = 'Traders Inc', @.Region = 'KEN'

SELECT * FROM table1

--Neeraj--|||

Awesome. Thanks alot for your responses.

Neeraj, by looking at the code and my newbie databases status, you just saved me 1 or 2 days of trial and error with this one.Code works perfect. Thanks a million!

I need to get my hands on some good sql books. Any book recommendations or links which I can start off with?

|||Hi kundalani,

Good to know that your problem is solved.

As per forums guidelines you should close the topic if it is able to solve your problem :)

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