Multi-Value Parameter

Hi
I have created a very simple report in SSRS, which counts the number of
Supplier transactions.
I have added a Multi-Value parameter with two values, (I or C) to filter
either Invoices or Credits or both.
If I query Invoice only, I get the correct result.
If I query Credit only, I get the correct result.
If I select all, the result is the same as Invoice. i.e. it is not adding
Invoice and Credit together.
Can anyone please help?
Thanks
ShodmanSo how you have given in your query to "select all" ?
Amarnath
"Shodman" wrote:
> Hi
> I have created a very simple report in SSRS, which counts the number of
> Supplier transactions.
> I have added a Multi-Value parameter with two values, (I or C) to filter
> either Invoices or Credits or both.
> If I query Invoice only, I get the correct result.
> If I query Credit only, I get the correct result.
> If I select all, the result is the same as Invoice. i.e. it is not adding
> Invoice and Credit together.
> Can anyone please help?
> Thanks
> Shodman|||Please note that I am not using any syntax or trying to write code - I am not
that clever!!
From the layout tab, I am adding a parameter through Report > Report
Parameters, and selecting Multi-Value. Values = I and C, (non-queried).
When I Preview the report, the parameter shows "select all" as on option.
Thanks for your time
Shodman
"Amarnath" wrote:
> So how you have given in your query to "select all" ?
> Amarnath
> "Shodman" wrote:
> > Hi
> >
> > I have created a very simple report in SSRS, which counts the number of
> > Supplier transactions.
> >
> > I have added a Multi-Value parameter with two values, (I or C) to filter
> > either Invoices or Credits or both.
> >
> > If I query Invoice only, I get the correct result.
> > If I query Credit only, I get the correct result.
> >
> > If I select all, the result is the same as Invoice. i.e. it is not adding
> > Invoice and Credit together.
> >
> > Can anyone please help?
> >
> > Thanks
> > Shodman|||Hey I am asking whether you have given any if condition in your data tab,
otherwise how you intend to display and select the correct query to display
your results. In otherwords you need to tell RS that when you select "Select
All" what should be done to display the correct results.
Amarnath
"Shodman" wrote:
> Please note that I am not using any syntax or trying to write code - I am not
> that clever!!
> From the layout tab, I am adding a parameter through Report > Report
> Parameters, and selecting Multi-Value. Values = I and C, (non-queried).
> When I Preview the report, the parameter shows "select all" as on option.
> Thanks for your time
> Shodman
>
> "Amarnath" wrote:
> > So how you have given in your query to "select all" ?
> >
> > Amarnath
> >
> > "Shodman" wrote:
> >
> > > Hi
> > >
> > > I have created a very simple report in SSRS, which counts the number of
> > > Supplier transactions.
> > >
> > > I have added a Multi-Value parameter with two values, (I or C) to filter
> > > either Invoices or Credits or both.
> > >
> > > If I query Invoice only, I get the correct result.
> > > If I query Credit only, I get the correct result.
> > >
> > > If I select all, the result is the same as Invoice. i.e. it is not adding
> > > Invoice and Credit together.
> > >
> > > Can anyone please help?
> > >
> > > Thanks
> > > Shodman|||I believe that Amarnath is saying that you have to provide some code in your
query to tell the database what to do when the "Select All" option is
selected.
So for example:
Select c.customer_name, p.product_type
From customer c, product p
Where c.customer_id = p.customer_id
and (p.product_type IN(@.param_product_type))
So in your report parameters you check the Multi-Value check box for the
"param_product_type" parameter. So in the above code, if you select 1 type,
the SQL query will return the correct type and if you select ALL, then the
query will return ALL product types.
Hope this helps.
Rob Cuscaden
"Amarnath" wrote:
> Hey I am asking whether you have given any if condition in your data tab,
> otherwise how you intend to display and select the correct query to display
> your results. In otherwords you need to tell RS that when you select "Select
> All" what should be done to display the correct results.
> Amarnath
> "Shodman" wrote:
> > Please note that I am not using any syntax or trying to write code - I am not
> > that clever!!
> >
> > From the layout tab, I am adding a parameter through Report > Report
> > Parameters, and selecting Multi-Value. Values = I and C, (non-queried).
> >
> > When I Preview the report, the parameter shows "select all" as on option.
> >
> > Thanks for your time
> > Shodman
> >
> >
> > "Amarnath" wrote:
> >
> > > So how you have given in your query to "select all" ?
> > >
> > > Amarnath
> > >
> > > "Shodman" wrote:
> > >
> > > > Hi
> > > >
> > > > I have created a very simple report in SSRS, which counts the number of
> > > > Supplier transactions.
> > > >
> > > > I have added a Multi-Value parameter with two values, (I or C) to filter
> > > > either Invoices or Credits or both.
> > > >
> > > > If I query Invoice only, I get the correct result.
> > > > If I query Credit only, I get the correct result.
> > > >
> > > > If I select all, the result is the same as Invoice. i.e. it is not adding
> > > > Invoice and Credit together.
> > > >
> > > > Can anyone please help?
> > > >
> > > > Thanks
> > > > Shodman|||You are right, I am asking exactly the same.
Amarnath
"Rob" wrote:
> I believe that Amarnath is saying that you have to provide some code in your
> query to tell the database what to do when the "Select All" option is
> selected.
> So for example:
> Select c.customer_name, p.product_type
> From customer c, product p
> Where c.customer_id = p.customer_id
> and (p.product_type IN(@.param_product_type))
> So in your report parameters you check the Multi-Value check box for the
> "param_product_type" parameter. So in the above code, if you select 1 type,
> the SQL query will return the correct type and if you select ALL, then the
> query will return ALL product types.
> Hope this helps.
> Rob Cuscaden
>
> "Amarnath" wrote:
> > Hey I am asking whether you have given any if condition in your data tab,
> > otherwise how you intend to display and select the correct query to display
> > your results. In otherwords you need to tell RS that when you select "Select
> > All" what should be done to display the correct results.
> >
> > Amarnath
> >
> > "Shodman" wrote:
> >
> > > Please note that I am not using any syntax or trying to write code - I am not
> > > that clever!!
> > >
> > > From the layout tab, I am adding a parameter through Report > Report
> > > Parameters, and selecting Multi-Value. Values = I and C, (non-queried).
> > >
> > > When I Preview the report, the parameter shows "select all" as on option.
> > >
> > > Thanks for your time
> > > Shodman
> > >
> > >
> > > "Amarnath" wrote:
> > >
> > > > So how you have given in your query to "select all" ?
> > > >
> > > > Amarnath
> > > >
> > > > "Shodman" wrote:
> > > >
> > > > > Hi
> > > > >
> > > > > I have created a very simple report in SSRS, which counts the number of
> > > > > Supplier transactions.
> > > > >
> > > > > I have added a Multi-Value parameter with two values, (I or C) to filter
> > > > > either Invoices or Credits or both.
> > > > >
> > > > > If I query Invoice only, I get the correct result.
> > > > > If I query Credit only, I get the correct result.
> > > > >
> > > > > If I select all, the result is the same as Invoice. i.e. it is not adding
> > > > > Invoice and Credit together.
> > > > >
> > > > > Can anyone please help?
> > > > >
> > > > > Thanks
> > > > > Shodman

Multiple transactions not working in package

I have a package with two sequence containers, each containing two SQL tasks and a data flow task, executed in that order. I want to encapsulate the data flow task in a transaction but not the SQL tasks. I have the TransactionOption property set to 'required' on the data flow tasks and 'supported' on the SQL tasks and the sequence containers. When I run the package I get a distributed transaction error on the first SQL task of the second sequence container:

"[Execute SQL Task] Error: Executing the query "TRUNCATE TABLE DistTransTbl2" failed with the following error: "Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly."

The only way I can get the package to succeed is to set the TransactionOption = 'required' on the sequence containers and 'supported' on all subordinate tasks. This is not what I want, however. Any ideas?

Thanks,

Eric

Eric,

Can you please send me your package?

So far I couldn’t repro the problem.

|||

Eric, have you tried using a DELETE FROM statement instead of a truncate statement. Truncate doesn't log, so it's very fast, but is probably the reason you're blocking on your second task that's attempting to insert into the table.

K

|||

Hi

I am getting the EXACT same problem, but I am not using any TRUNCATE statements or similar.

Actually, I find that the problem only seems to exist when I try to enlist a Data Flow task in a transaction.

If I have 2 sequence containers, each containing an Execute SQL Task, everything works fine. If I try to replace one of these with a Data Flow Task, then I get the same error.

Does anyone have a solution to this?

|||I could not reproduce the issues reported.

Multiple transactions not working in package

I have a package with two sequence containers, each containing two SQL tasks and a data flow task, executed in that order. I want to encapsulate the data flow task in a transaction but not the SQL tasks. I have the TransactionOption property set to 'required' on the data flow tasks and 'supported' on the SQL tasks and the sequence containers. When I run the package I get a distributed transaction error on the first SQL task of the second sequence container:

"[Execute SQL Task] Error: Executing the query "TRUNCATE TABLE DistTransTbl2" failed with the following error: "Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly."

The only way I can get the package to succeed is to set the TransactionOption = 'required' on the sequence containers and 'supported' on all subordinate tasks. This is not what I want, however. Any ideas?

Thanks,

Eric

Eric,

Can you please send me your package?

So far I couldn’t repro the problem.

|||

Eric, have you tried using a DELETE FROM statement instead of a truncate statement. Truncate doesn't log, so it's very fast, but is probably the reason you're blocking on your second task that's attempting to insert into the table.

K

|||

Hi

I am getting the EXACT same problem, but I am not using any TRUNCATE statements or similar.

Actually, I find that the problem only seems to exist when I try to enlist a Data Flow task in a transaction.

If I have 2 sequence containers, each containing an Execute SQL Task, everything works fine. If I try to replace one of these with a Data Flow Task, then I get the same error.

Does anyone have a solution to this?

|||I could not reproduce the issues reported.

multiple transaction ?

I have a question regarding multiple transactions. I
would like to create a main stored procedure that calls
several other stored procedures. I would like to use
transactions when I write all of my SP's but am unsure
how it would works. I am running into problems when a
rollback occurs when i have multiple transactions. Is
there a standard way of setting up a commit/rollback
procedure in all SP's? I always use the following but it
does not seem to work correctly.
BEGIN TRANSACTION
IF @.ErrorCount = 0
COMMIT TRANSATION
ELSE
ROLLBACK TRANSACTION
Do i need something in here to look at the transaction
count?
Any help would be appreciated.
Thanks
B.A.I have to assume that @.ErrorCount is something of your doing so I will also
assume it is getting this correctly. When you issue a ROLLBACK, regardless
of where it's issued, all teh open transactions will be rolled back. So you
want to test to see if @.@.TRANCOUNT is greater than 0 before issuing the
rollbck otherwise you will get an error.
Andy
"B.A. Baracus" <JCoxEUP@.hotmail.com> wrote in message
news:014c01c34f12$4d4b6c20$a501280a@.phx.gbl...
> I have a question regarding multiple transactions. I
> would like to create a main stored procedure that calls
> several other stored procedures. I would like to use
> transactions when I write all of my SP's but am unsure
> how it would works. I am running into problems when a
> rollback occurs when i have multiple transactions. Is
> there a standard way of setting up a commit/rollback
> procedure in all SP's? I always use the following but it
> does not seem to work correctly.
> BEGIN TRANSACTION
> IF @.ErrorCount = 0
> COMMIT TRANSATION
> ELSE
> ROLLBACK TRANSACTION
> Do i need something in here to look at the transaction
> count?
> Any help would be appreciated.
> Thanks
> B.A.
>

Multiple Trans Log Restores

Is there a way to restore multiple transactions logs when
you don't know the name of the logs files (i.e. the last 8
characters of the log file changes)?
Thanks,
JoshYou could either use xp_cmdshell and DIR into a temp table and work your code from there, or base
your restore on the backup history tables in msdb (assuming that the backup was taken on the same
machine). Both options requires a bit of coding.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Joshua Lakin" <LakinJ@.NOSPAMPLEASE.bsci.com> wrote in message
news:00a501c3c58f$8474d3f0$a401280a@.phx.gbl...
> Is there a way to restore multiple transactions logs when
> you don't know the name of the logs files (i.e. the last 8
> characters of the log file changes)?
> Thanks,
> Josh

Multiple Stored Procedures and Transactions

I have a simple question: Can I have two or more stored procedures that begin transactions but the last stored procedure will commit all transactions if there was no error?

In other words, I have a stored procedure that performs some legacy stuff. I do not want to rewrite that logice, execpt I am putting it into a Stored Procedure since it currently is embedded SQL.

To support newer functionality, I am writing data to additional new tables. However, I do not want the *new* things to occur if there is an error. This is easy enough, I dont call the Stored Procedure for the new functionality if there was an error. However, if there was not an error and the newer stored procedure is called AND there is an error in the new stored procedure, I want to ROLLBACK the changes from the proceeding stored procedures.

To my understanding, I can name transactions but that is only to indicate in the logs what transactions have failed.

I thought about not using transactions for any of the individual stored procedures and calling them from a main stored procedure. The main stored procedure will have a BEGIN TRY and an END TRY (I am using SQL Server 2005) and at the top (right after the try) I will have a BEGIN TRANSACTION. In the BEGIN CATCH I will have a ROLLBACK and at the end I will have a COMMIT. If any of the stored procedures fail at any point it will cause the catch to occur thus rolling back all of my transactions. This would be the easiest way but I still need to deal with the question of, "What happens if any single stored procedure is called?" I guess I could have try and catches in each individual stored procedure for that.

I just want to make sure I am not overlooking something simple. I think I have the right idea by doing it this way I mentioned above. Because this is critical billing processing I just want to have some reassurance that this would work.

Thank

Why don't you add a column to the legacy output to indicate errors. That way nothing is stopped, but you can determine at what point errors start and output to the user, or react accordingly.

|||

Thank you for your response but the problem with that is I want things to stop, I dont want the data in both tables to be out of sync. The only reason we are supporting the older table is the web application is quite huge, very very large and at this point, rewriting legacy code (using classic ASP) to use our new tables would be time consuming. Additionaly, we have several apps wrote in Visual Basic 6 that would need to be rewrote as well to support our newer system. I am developing a new Payment Processing System in C# that writes to the database, and caputres additional data that is useful instead of waiting for end of month. The old system is very very inefficient but until we have the resources to go back and recode it, End of Month Reports still run off of it. These jobs take 2 hours to run (Yeah lots of data) However, the purpose of the new system is to capture some of that data that wasn't avaialble until the end of month. It does this by writing the data that one department needs concering payments and adjustments at the time that action is performed.

However, we cannot have any one table having a different value than the other. If I do it the way you stated then if there is an error, our historical end of month data will be different than data in our new table.

From my understanding, if I wrap everything in a TRY CATCH block in sql server and if any one statment or procedure fails and I am raising the error, the Outermost TRY CATCH BLOCK will also fail, thus braching off to its CATCH BLOCK and inside that CATCH Block I have a rollback. From what I have read, the outermost COMMIT and ROLLBACK are what matters and if it ROLLSBACK then everything rolls back.. I just want to make sure I am understanding this correctly.

Thanks!

John

|||

jgilmore:

Can I have two or more stored procedures that begin transactions but the last stored procedure will commit all transactions if there was no error?

Yes. In fact, that's the default behavior of "nested transactions"

Seehttp://www.codeproject.com/KB/database/sqlservertransactions.aspx and scroll down to "nested transactions" but here's the nutshell answer. Assuming proc1 executes proc2, which contains a begin tran/commit, as long as proc1 wraps the call to proc2 in another begin tran/commit/rollback, a rollback done as part of the outer transaction will in fact roll back the transaction done by proc2, even if proc2's transaction has commited.

For many more articles, just google "sql server nested transaction"

|||

Thank you dbland07666!

I wanted to ensure that this worked as I expected it to.

John

Multiple sources and one target

I'm thinking aboug using Service Broker to aggregate transactions from 12 remote SQL Servers to a central SQL Server. Service Broker is new to me, so I don't know what the proper setup would be.

We have five different kinds of transactions that can be sent from any of the remote servers. These remote servers truly are remote, connecting to the central server over a WAN. We don't want problems with one server to interfere with any other server. We also don't want problems with one kind of transaction to interfere with the processing of any other kind of transaction.

If were only talking about one remote server, I'm guessing I'd want to create 5 different services - one for each kind of transaction. Does that mean I'd also need to create 5 different routes on both the remote server and the central server?

And how would I scale this out to a dozen remote servers? Can I stick with 5 services on the central server, or would I need to define 5 different services for each remote server, so 60 total? And would that be 60 different routes defined on the central server? Based on my limited knowledge of routes and how they're tied to services, I think that would mean 60 routes, but maybe I'm missing something.

Please let me know. Thanks.

Service Broker seems like an excellent choice for the scenario that you have described. It provides isolation mechansims to ensure that two unrelated business transactions can run concurrently.

The primitive for communication in Service Broker is a conversation, which is a reliable, durable session for exchange of messages. Two-party conversations are known as dialogs and they contain two endpoints -- an initiator and a target. Services are nothing but logical entities that can initiate or be the target for a conversation. They are addressable (via routes) and securable (via certificates). But one service can have multiple conversation endpoints to deal with concurrent conversations.

Given that, in general you would create a 'Service' to handle all conversations for some business function which needs to have a distinct identity and location. If you decide to move the service to another server or issue a new certificate, you will affect all conversations targetting or initiating from that service. So if you feel that the 5 business transactions are all distinct and may in the future be performed at different locations, you are better off with 5 services. If that seems impossible or highly unlikely, stick with a single service.

Conversations can be strongly typed by defining formal contracts that specify what type of messages may be sent by each endpoint. Since a service can expose multiple contracts, it can accept conversations belonging to different contracts. In your scenario, you might have different contracts for each of the 5 different business transaction types.

A database can certainly support more than just 60 routes, so don't feel that the number of routes is going to be a performance bottleneck. If you think it might be a managability nightmare, you could consider investing into something we call "Broker Configuration Notification". If you enable this in your database and a new conversation cannot find a route, Service Broker will send a special "Missing Route" message to a service of your choice. You could then implement this "route resolution" service to lookup the route from a directory and create the route in your local database and Service Broker will automatically try to deliver the message of former conversation.

One thing to be aware of in a WAN environment is that Service Broker requires both instances of SQL Server to be able to connect to each other over TCP/IP. That means that if either party is behind a firewall/NAT, the appropriate ports need to be opened for two-way communication.

Hope that helps,
Rushi

|||

Thanks for the information!

David