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 SQL servers sharing Common Disk

Is it possible to have 2 SQL 2000 (STD) servers access a common RAID
disk containing the database and work concurrently?
No.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Steve Babcock" <sbabcock@.hillhouse.ca> wrote in message
news:434E6529.9DBA7366@.hillhouse.ca...
> Is it possible to have 2 SQL 2000 (STD) servers access a common RAID
> disk containing the database and work concurrently?
>
>
|||As I suspected - I guess that SQL clustering is the way to go or wait
until SQL 2005 for mirroring
Steve
"Geoff N. Hiten" wrote:
[vbcol=seagreen]
> No.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Steve Babcock" <sbabcock@.hillhouse.ca> wrote in message
> news:434E6529.9DBA7366@.hillhouse.ca...
|||Clustering is a high availability solution, not a scale-up solution.
Mirroring allows read-only access to the target database.
SQL does not scale out, it scales up.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Steve Babcock" <sbabcock@.hillhouse.ca> wrote in message
news:434E696D.2F79952A@.hillhouse.ca...
> As I suspected - I guess that SQL clustering is the way to go or wait
> until SQL 2005 for mirroring
> Steve
> "Geoff N. Hiten" wrote:
>
|||What I need is a fail-over solution whereby if a sql server has a hardware
failure, another server can continue in its place.
Steve
"Geoff N. Hiten" wrote:
[vbcol=seagreen]
> Clustering is a high availability solution, not a scale-up solution.
> Mirroring allows read-only access to the target database.
> SQL does not scale out, it scales up.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Steve Babcock" <sbabcock@.hillhouse.ca> wrote in message
> news:434E696D.2F79952A@.hillhouse.ca...
|||You are looking for failover clustering. You use a highly-redundant disk
array such as a SAN, two or more host computers, and Windows and SQL
Software to build a failover cluster. The systems are physically
interconnected at all times, but the cluster software arbitrates ownership
so only one host computer owns a SQL server instance, and its associated
resources) at any one time.
Here is a good overview on SQL failover clustering
http://www.microsoft.com/technet/pro.../failclus.mspx
Here is a good resource on SQL Server High Availability solutions:
http://www.microsoft.com/technet/pro...y/sqlhalp.mspx
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Steve Babcock" <sbabcock@.hillhouse.ca> wrote in message
news:434F9806.D4DC58BD@.hillhouse.ca...
> What I need is a fail-over solution whereby if a sql server has a hardware
> failure, another server can continue in its place.
> Steve
> "Geoff N. Hiten" wrote:
>

Multiple SQL servers sharing Common Disk

Is it possible to have 2 SQL 2000 (STD) servers access a common RAID
disk containing the database and work concurrently?No.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Steve Babcock" <sbabcock@.hillhouse.ca> wrote in message
news:434E6529.9DBA7366@.hillhouse.ca...
> Is it possible to have 2 SQL 2000 (STD) servers access a common RAID
> disk containing the database and work concurrently?
>
>|||As I suspected - I guess that SQL clustering is the way to go or wait
until SQL 2005 for mirroring
Steve
"Geoff N. Hiten" wrote:
> No.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Steve Babcock" <sbabcock@.hillhouse.ca> wrote in message
> news:434E6529.9DBA7366@.hillhouse.ca...
> > Is it possible to have 2 SQL 2000 (STD) servers access a common RAID
> > disk containing the database and work concurrently?
> >
> >
> >|||Clustering is a high availability solution, not a scale-up solution.
Mirroring allows read-only access to the target database.
SQL does not scale out, it scales up.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Steve Babcock" <sbabcock@.hillhouse.ca> wrote in message
news:434E696D.2F79952A@.hillhouse.ca...
> As I suspected - I guess that SQL clustering is the way to go or wait
> until SQL 2005 for mirroring
> Steve
> "Geoff N. Hiten" wrote:
>> No.
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>> "Steve Babcock" <sbabcock@.hillhouse.ca> wrote in message
>> news:434E6529.9DBA7366@.hillhouse.ca...
>> > Is it possible to have 2 SQL 2000 (STD) servers access a common RAID
>> > disk containing the database and work concurrently?
>> >
>> >
>> >
>|||What I need is a fail-over solution whereby if a sql server has a hardware
failure, another server can continue in its place.
Steve
"Geoff N. Hiten" wrote:
> Clustering is a high availability solution, not a scale-up solution.
> Mirroring allows read-only access to the target database.
> SQL does not scale out, it scales up.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Steve Babcock" <sbabcock@.hillhouse.ca> wrote in message
> news:434E696D.2F79952A@.hillhouse.ca...
> > As I suspected - I guess that SQL clustering is the way to go or wait
> > until SQL 2005 for mirroring
> >
> > Steve
> >
> > "Geoff N. Hiten" wrote:
> >
> >> No.
> >>
> >> --
> >> Geoff N. Hiten
> >> Senior Database Administrator
> >> Microsoft SQL Server MVP
> >> "Steve Babcock" <sbabcock@.hillhouse.ca> wrote in message
> >> news:434E6529.9DBA7366@.hillhouse.ca...
> >> > Is it possible to have 2 SQL 2000 (STD) servers access a common RAID
> >> > disk containing the database and work concurrently?
> >> >
> >> >
> >> >
> >|||You are looking for failover clustering. You use a highly-redundant disk
array such as a SAN, two or more host computers, and Windows and SQL
Software to build a failover cluster. The systems are physically
interconnected at all times, but the cluster software arbitrates ownership
so only one host computer owns a SQL server instance, and its associated
resources) at any one time.
Here is a good overview on SQL failover clustering
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/failclus.mspx
Here is a good resource on SQL Server High Availability solutions:
http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/sqlhalp.mspx
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Steve Babcock" <sbabcock@.hillhouse.ca> wrote in message
news:434F9806.D4DC58BD@.hillhouse.ca...
> What I need is a fail-over solution whereby if a sql server has a hardware
> failure, another server can continue in its place.
> Steve
> "Geoff N. Hiten" wrote:
>> Clustering is a high availability solution, not a scale-up solution.
>> Mirroring allows read-only access to the target database.
>> SQL does not scale out, it scales up.
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>> "Steve Babcock" <sbabcock@.hillhouse.ca> wrote in message
>> news:434E696D.2F79952A@.hillhouse.ca...
>> > As I suspected - I guess that SQL clustering is the way to go or wait
>> > until SQL 2005 for mirroring
>> >
>> > Steve
>> >
>> > "Geoff N. Hiten" wrote:
>> >
>> >> No.
>> >>
>> >> --
>> >> Geoff N. Hiten
>> >> Senior Database Administrator
>> >> Microsoft SQL Server MVP
>> >> "Steve Babcock" <sbabcock@.hillhouse.ca> wrote in message
>> >> news:434E6529.9DBA7366@.hillhouse.ca...
>> >> > Is it possible to have 2 SQL 2000 (STD) servers access a common RAID
>> >> > disk containing the database and work concurrently?
>> >> >
>> >> >
>> >> >
>> >
>

Multiple SQL servers sharing Common Disk

Is it possible to have 2 SQL 2000 (STD) servers access a common RAID
disk containing the database and work concurrently?No.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Steve Babcock" <sbabcock@.hillhouse.ca> wrote in message
news:434E6529.9DBA7366@.hillhouse.ca...
> Is it possible to have 2 SQL 2000 (STD) servers access a common RAID
> disk containing the database and work concurrently?
>
>|||As I suspected - I guess that SQL clustering is the way to go or wait
until SQL 2005 for mirroring
Steve
"Geoff N. Hiten" wrote:
[vbcol=seagreen]
> No.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Steve Babcock" <sbabcock@.hillhouse.ca> wrote in message
> news:434E6529.9DBA7366@.hillhouse.ca...|||Clustering is a high availability solution, not a scale-up solution.
Mirroring allows read-only access to the target database.
SQL does not scale out, it scales up.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Steve Babcock" <sbabcock@.hillhouse.ca> wrote in message
news:434E696D.2F79952A@.hillhouse.ca...
> As I suspected - I guess that SQL clustering is the way to go or wait
> until SQL 2005 for mirroring
> Steve
> "Geoff N. Hiten" wrote:
>
>|||What I need is a fail-over solution whereby if a sql server has a hardware
failure, another server can continue in its place.
Steve
"Geoff N. Hiten" wrote:
[vbcol=seagreen]
> Clustering is a high availability solution, not a scale-up solution.
> Mirroring allows read-only access to the target database.
> SQL does not scale out, it scales up.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Steve Babcock" <sbabcock@.hillhouse.ca> wrote in message
> news:434E696D.2F79952A@.hillhouse.ca...|||You are looking for failover clustering. You use a highly-redundant disk
array such as a SAN, two or more host computers, and Windows and SQL
Software to build a failover cluster. The systems are physically
interconnected at all times, but the cluster software arbitrates ownership
so only one host computer owns a SQL server instance, and its associated
resources) at any one time.
Here is a good overview on SQL failover clustering
http://www.microsoft.com/technet/pr...n/failclus.mspx
Here is a good resource on SQL Server High Availability solutions:
http://www.microsoft.com/technet/pr...oy/sqlhalp.mspx
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Steve Babcock" <sbabcock@.hillhouse.ca> wrote in message
news:434F9806.D4DC58BD@.hillhouse.ca...
> What I need is a fail-over solution whereby if a sql server has a hardware
> failure, another server can continue in its place.
> Steve
> "Geoff N. Hiten" wrote:
>
>

Multiple results from single column

Ok this subject sounds a bit vague, but here's my question:

I have a table containing a user id column, a column with events (like log in) and a date column (mm/dd/yy hh/mm/ss).

What i would like to do is to count the number of users who have logged into my system, sorted by month.

Example: January - 20 users
Februari - 42 users
March - 13 users etc.

Can this be done with just one SQL statement? If so, can anyone give me an example query?

Thanks!

SanderHello,

what do you think about

SELECT TO_CHAR(date_field, 'MONTH'), count(*) FROM table_name
GROUP BY TO_CHAR(date_field, 'MONTH')


if you are using an Oracle database.

Hope this helps ?

Greetings
Manfred Peter
(Alligator Company)
http://www.alligatorsql.com|||Hi Manfred Peter,

I'm sorry to say i don't have Oracle, i run MSSQL7. So the statement you provided won't work (i know for sure the to_char function won't work) :rolleyes:

What i have so far is:

select count(users) from table where ops='login' and date between dateA and dateB

This will return me one number from the given timeframe. I want to create a statement that will give me the number of logins over the time period of a year for each month, so 12 numbers. I can't just copy/paste the same statement 11 times...can I :confused:

Thanks!

Sander.|||Hello Sander,

of course you can, but this means 12 times parsing the sqlstatement and reqeusting the datas from the database.
There must be an equivalent to the TO_CHAR function in MSQL Server.

You just need the function that gives only the complete month of a date value (lets say this function is called MONTH(x));

Then the statement

SELECT MONTH(date_field), COUNT(*) from table
GROUP BY MONTH(date_field)

This is the better way ... in my opinion
Search the doku for such a command :)

Let me know if you have further problems.

Manfred Peter
(Alligator Company)
http://www.alligatorsql.com