Its been a looooong day ! Need help

Ok .. here's another problem I am facing ...
Its been a long day and my mind is really not working

I have three tables lets say

tab1
---
boss
month
Bonus

tab2
---
boss
employee

Each Boss is given some bonus amount ... he has to distribute it evenly among his employees. data for each boss monthly bonus is availible.

But what i need is amount per employee per day (and remember amount being recieved will vary upon bonus amount with boss aND NO OF day in month). I need to generate a tabe for the whole year containing records

taboutput
----
boss
employee
date
amount



Any Ideas ??Is the bonus distributed evenly to all the boss's employees regardless of the number of days they were employed?|||Thats another story ... its just a problematic representation of what i ve to do ...

A better analogy would be a target for the territory, and i need a daily volume for salespersons working in that territory ...

Lets say The data is given at Territorywise , Month level for the year and i need to split it at Salesperson, Day level for the year

So the problem now stands as

Tab1
--
Territory
Month
Target

Tab2
---
Territory
Sales_Person

Result needed as
TabOutput
-----
Territory
Sales_person
Date
Target

Its like i should be able to get the target till date for the salesperson for any date specified.|||Yes, but its important to know whether the number of salespeople in a territory can change from month to month, and where this information is stored. I suspect the reasons this is giving you trouble are
1) You are dead beat tired
and
2) You don't have all the information required to solve the problem

I worked in a sales organization for three years developing territory and compensation models for sale people. Their territories would change several times each year.|||Originally posted by blindman
Yes, but its important to know whether the number of salespeople in a territory can change from month to month, and where this information is stored. I suspect the reasons this is giving you trouble are
1) You are dead beat tired
and
2) You don't have all the information required to solve the problem

I worked in a sales organization for three years developing territory and compensation models for sale people. Their territories would change several times each year.

It was reason no 1... did have to take 7 cups of coffee to keep myself awake during the night ... Thanks .. have solved the problem for now :)|||Up all night, and then still went to (stayed at) work?

I hope you're hourly...|||Hmm .. would love to do hourly ... but my bad luck .. am on a fixed salary :(sql

Issues in setDate of PreparedStatement

Hi,
I have recently started using the JDBC MS-SQL driver. I am facing a
problem with the setdate() function of the prepared statement. if we are
using the setDate function of the prepared statment then the date gets
updated with a timestamp.
i.e. pObjpreparedStmtIns.setDate(2, pdCurrentDate);
will add the date in the system as 2004-12-27 :12:00:000
instead of 2004-12-27 :00:00:000.
pdCurrentDate is java.sql.Date() set to todays date.
has anyone come acros this problem.
Thank you
"Shirish Nair" <Shirish Nair@.discussions.microsoft.com> schrieb im
Newsbeitrag news:4BAE3584-2220-4DF3-B66C-A75F3CAB3DF8@.microsoft.com...
> Hi,
> I have recently started using the JDBC MS-SQL driver. I am facing a
> problem with the setdate() function of the prepared statement. if we
are
> using the setDate function of the prepared statment then the date gets
> updated with a timestamp.
> i.e. pObjpreparedStmtIns.setDate(2, pdCurrentDate);
> will add the date in the system as 2004-12-27 :12:00:000
> instead of 2004-12-27 :00:00:000.
> pdCurrentDate is java.sql.Date() set to todays date.
> has anyone come acros this problem.
Two things come to mind:
- it's just a display problem (12am ~ 0:00 ~ 24:00)
- it's a time zone offset thingy (are you +/- 12h?)
- you could also try to use java.sql.Timestamp
Regards
robert

Issue with SSIS Package Configurations in SqlServer

Hello everyone,

I am working on a SSIS project and I am facing an issue for getting the configuration settings of the package, once it is deployed and executed from SQL Server agent.

The package uses two configuration types: (listed bellow in the order they are appeared in the configuration editor)

Config1 - Xml configuration file - for storing the database connection string.

Config2 - SQL Server - for storing some user defined variables. It uses the same database as specified in Config1.

Everything works fine and the package uses the database configuration values as defined in Config2, if I execute it from Visual Studio,

However, the package doesn’t get the configuration settings from the database when I try to execute it as a SQL Agent job.

There aren’t any errors and the package executes all tasks successfully, using the connection object Config1 (the same we use to get the config parameters from the database) and the default values of the user defined variables.

It works ok, if I change Config2 to be of type XML configuration file.

There could be two problems:

1. SQL server agent doesn’t read the configuration from the database and I am not quite sure how to set this. In Agent/ Job step properties screen/ Configurations tab I can only browse for a config file. I can also use the command window and /CONFIGFILE option to specify xml file, but how to use it in a case of a database configuration? Is there a /CONFIGDATABSE option or /CONFIGFILE works with database connection as well. I tried with /CONFIGFILE and database connection, but it doesn’t seem to work.

2. SQL server agent doesn’t get the configurations in the specified order. In my case,

it could try to read Config2 first, but at that moment it doesn’t have the database connection from Config1 and it fails. Again, I am not sure how to set the sequence.

Thanks in advance for your comments.

ITHave you specified a full path for Config1?|||

Yes, I specified the full path for Config1. I used the configuration tab to select the file and in the command line window it shows the full path.

The package itself uses the connection from Config1 for the data flow tasks and they work without any issues.

|||

Hi,

What kind of user defined variables are you fetching from the database?

Can you try storing them in package level variables by using a Script Task?

Regards,

B@.ns

|||

I think you can discard option 2. The package configurations should be processed in the order in which they are stored.

It sounds like a permissions problem with the SQl Server Agent account.

Have you tried profiling (SQL Profiler) the package execution to see if it is generating any SQL errors trying to read the configuration table? If the package fails to read the configuration, it only raises a warning, not an error, so it is not always obvious when a configuration fails to load.

|||

I couldn’t find any errors or warnings during the package execution.

Moreover the same connection is used for all data flow tasks inside the package and the SSIS logging (it uses SSIS log provider for SQL) and all of them work without any issues.

As a workaround I can add a SQL script to read those variables from the database, but this will duplicate the configuration functionality.

The idea was to use the existing SSIS database configuration feature instead of building a similar one from scratch.

|||Are you changig the path of the Conf1 in Agent, different than one you have when building the package?|||I.T.

See if you this blog spot can help you identifying the issue:

http://rafael-salas.blogspot.com/2007/01/ssis-package-configurations-using-sql.html

It uses an environment variable instead of a file|||

Hi Rafael,

I checked your blog (actually this was one of the first materials I found when I started exploring the issue a week ago), but in our case we have some system restrictions for using environment variables.

In your example you mentioned also that you used it successfully with XML file.

I would like to ask you if you were to deploy it and execute it as a SQL agent job?

Thanks,

IT

|||Yes, I did, and as far as I remember it worked ok. The only problems I can remember are stupid things like the XML file was not accessible for the account running the SS Agent; not updating the connection string properly withing the XML file (hence trying to connect to the wrong instance), etc. But most of the times, the package logging will tell you if something is wrong with any of the configurations and even shows the order in which the configurations were applied.|||

Rafael,

I would like to ask you if you remember how you set the sql configuration in the SQL Agent job. Did you do something special for this?

I couldn’t see any available options to set explicitly the configuration as SQL server (the UI allows only to browse and select config file).

The package logging is set to log all events and it doesn’t show any errors during the execution.

You mentioned also that the package logging can tell the order of the configuration, but I couldn’t see that kind of information. Is there a special custom event for this?

Thanks,

IT

|||

I just ran some tests using XML and Table configurations (in that order) and works fine in BIDS and as an agent job.

I didn't have to do any special thing in the job step (CmdExec); just to a command like:

DTEXEC /FILE "{path}\Configurations demo 2.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EWCDI

But what I could notice is that the messages about package configurations are not shown in te progress tab; I recently updated to SP2, and I wonder if that has been changed. I dind't even received any warning when I deliberated removed the xml config file...not even in the package logging|||

I. T. wrote:

I couldn’t find any errors or warnings during the package execution.

This is normal behaviour in my experience. I didn't get any warnings or errors either.

Issue with SSIS Package Configurations in SqlServer

Hello everyone,

I am working on a SSIS project and I am facing an issue for getting the configuration settings of the package, once it is deployed and executed from SQL Server agent.

The package uses two configuration types: (listed bellow in the order they are appeared in the configuration editor)

Config1 - Xml configuration file - for storing the database connection string.

Config2 - SQL Server - for storing some user defined variables. It uses the same database as specified in Config1.

Everything works fine and the package uses the database configuration values as defined in Config2, if I execute it from Visual Studio,

However, the package doesn’t get the configuration settings from the database when I try to execute it as a SQL Agent job.

There aren’t any errors and the package executes all tasks successfully, using the connection object Config1 (the same we use to get the config parameters from the database) and the default values of the user defined variables.

It works ok, if I change Config2 to be of type XML configuration file.

There could be two problems:

1. SQL server agent doesn’t read the configuration from the database and I am not quite sure how to set this. In Agent/ Job step properties screen/ Configurations tab I can only browse for a config file. I can also use the command window and /CONFIGFILE option to specify xml file, but how to use it in a case of a database configuration? Is there a /CONFIGDATABSE option or /CONFIGFILE works with database connection as well. I tried with /CONFIGFILE and database connection, but it doesn’t seem to work.

2. SQL server agent doesn’t get the configurations in the specified order. In my case,

it could try to read Config2 first, but at that moment it doesn’t have the database connection from Config1 and it fails. Again, I am not sure how to set the sequence.

Thanks in advance for your comments.

ITHave you specified a full path for Config1?|||

Yes, I specified the full path for Config1. I used the configuration tab to select the file and in the command line window it shows the full path.

The package itself uses the connection from Config1 for the data flow tasks and they work without any issues.

|||

Hi,

What kind of user defined variables are you fetching from the database?

Can you try storing them in package level variables by using a Script Task?

Regards,

B@.ns

|||

I think you can discard option 2. The package configurations should be processed in the order in which they are stored.

It sounds like a permissions problem with the SQl Server Agent account.

Have you tried profiling (SQL Profiler) the package execution to see if it is generating any SQL errors trying to read the configuration table? If the package fails to read the configuration, it only raises a warning, not an error, so it is not always obvious when a configuration fails to load.

|||

I couldn’t find any errors or warnings during the package execution.

Moreover the same connection is used for all data flow tasks inside the package and the SSIS logging (it uses SSIS log provider for SQL) and all of them work without any issues.

As a workaround I can add a SQL script to read those variables from the database, but this will duplicate the configuration functionality.

The idea was to use the existing SSIS database configuration feature instead of building a similar one from scratch.

|||Are you changig the path of the Conf1 in Agent, different than one you have when building the package?|||I.T.

See if you this blog spot can help you identifying the issue:

http://rafael-salas.blogspot.com/2007/01/ssis-package-configurations-using-sql.html

It uses an environment variable instead of a file|||

Hi Rafael,

I checked your blog (actually this was one of the first materials I found when I started exploring the issue a week ago), but in our case we have some system restrictions for using environment variables.

In your example you mentioned also that you used it successfully with XML file.

I would like to ask you if you were to deploy it and execute it as a SQL agent job?

Thanks,

IT

|||Yes, I did, and as far as I remember it worked ok. The only problems I can remember are stupid things like the XML file was not accessible for the account running the SS Agent; not updating the connection string properly withing the XML file (hence trying to connect to the wrong instance), etc. But most of the times, the package logging will tell you if something is wrong with any of the configurations and even shows the order in which the configurations were applied.|||

Rafael,

I would like to ask you if you remember how you set the sql configuration in the SQL Agent job. Did you do something special for this?

I couldn’t see any available options to set explicitly the configuration as SQL server (the UI allows only to browse and select config file).

The package logging is set to log all events and it doesn’t show any errors during the execution.

You mentioned also that the package logging can tell the order of the configuration, but I couldn’t see that kind of information. Is there a special custom event for this?

Thanks,

IT

|||

I just ran some tests using XML and Table configurations (in that order) and works fine in BIDS and as an agent job.

I didn't have to do any special thing in the job step (CmdExec); just to a command like:

DTEXEC /FILE "{path}\Configurations demo 2.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EWCDI

But what I could notice is that the messages about package configurations are not shown in te progress tab; I recently updated to SP2, and I wonder if that has been changed. I dind't even received any warning when I deliberated removed the xml config file...not even in the package logging|||

I. T. wrote:

I couldn’t find any errors or warnings during the package execution.

This is normal behaviour in my experience. I didn't get any warnings or errors either.

Issue with SSIS Package Configurations in SqlServer

Hello everyone,

I am working on a SSIS project and I am facing an issue for getting the configuration settings of the package, once it is deployed and executed from SQL Server agent.

The package uses two configuration types: (listed bellow in the order they are appeared in the configuration editor)

Config1 - Xml configuration file - for storing the database connection string.

Config2 - SQL Server - for storing some user defined variables. It uses the same database as specified in Config1.

Everything works fine and the package uses the database configuration values as defined in Config2, if I execute it from Visual Studio,

However, the package doesn’t get the configuration settings from the database when I try to execute it as a SQL Agent job.

There aren’t any errors and the package executes all tasks successfully, using the connection object Config1 (the same we use to get the config parameters from the database) and the default values of the user defined variables.

It works ok, if I change Config2 to be of type XML configuration file.

There could be two problems:

1. SQL server agent doesn’t read the configuration from the database and I am not quite sure how to set this. In Agent/ Job step properties screen/ Configurations tab I can only browse for a config file. I can also use the command window and /CONFIGFILE option to specify xml file, but how to use it in a case of a database configuration? Is there a /CONFIGDATABSE option or /CONFIGFILE works with database connection as well. I tried with /CONFIGFILE and database connection, but it doesn’t seem to work.

2. SQL server agent doesn’t get the configurations in the specified order. In my case,

it could try to read Config2 first, but at that moment it doesn’t have the database connection from Config1 and it fails. Again, I am not sure how to set the sequence.

Thanks in advance for your comments.

ITHave you specified a full path for Config1?|||

Yes, I specified the full path for Config1. I used the configuration tab to select the file and in the command line window it shows the full path.

The package itself uses the connection from Config1 for the data flow tasks and they work without any issues.

|||

Hi,

What kind of user defined variables are you fetching from the database?

Can you try storing them in package level variables by using a Script Task?

Regards,

B@.ns

|||

I think you can discard option 2. The package configurations should be processed in the order in which they are stored.

It sounds like a permissions problem with the SQl Server Agent account.

Have you tried profiling (SQL Profiler) the package execution to see if it is generating any SQL errors trying to read the configuration table? If the package fails to read the configuration, it only raises a warning, not an error, so it is not always obvious when a configuration fails to load.

|||

I couldn’t find any errors or warnings during the package execution.

Moreover the same connection is used for all data flow tasks inside the package and the SSIS logging (it uses SSIS log provider for SQL) and all of them work without any issues.

As a workaround I can add a SQL script to read those variables from the database, but this will duplicate the configuration functionality.

The idea was to use the existing SSIS database configuration feature instead of building a similar one from scratch.

|||Are you changig the path of the Conf1 in Agent, different than one you have when building the package?|||I.T.

See if you this blog spot can help you identifying the issue:

http://rafael-salas.blogspot.com/2007/01/ssis-package-configurations-using-sql.html

It uses an environment variable instead of a file|||

Hi Rafael,

I checked your blog (actually this was one of the first materials I found when I started exploring the issue a week ago), but in our case we have some system restrictions for using environment variables.

In your example you mentioned also that you used it successfully with XML file.

I would like to ask you if you were to deploy it and execute it as a SQL agent job?

Thanks,

IT

|||Yes, I did, and as far as I remember it worked ok. The only problems I can remember are stupid things like the XML file was not accessible for the account running the SS Agent; not updating the connection string properly withing the XML file (hence trying to connect to the wrong instance), etc. But most of the times, the package logging will tell you if something is wrong with any of the configurations and even shows the order in which the configurations were applied.|||

Rafael,

I would like to ask you if you remember how you set the sql configuration in the SQL Agent job. Did you do something special for this?

I couldn’t see any available options to set explicitly the configuration as SQL server (the UI allows only to browse and select config file).

The package logging is set to log all events and it doesn’t show any errors during the execution.

You mentioned also that the package logging can tell the order of the configuration, but I couldn’t see that kind of information. Is there a special custom event for this?

Thanks,

IT

|||

I just ran some tests using XML and Table configurations (in that order) and works fine in BIDS and as an agent job.

I didn't have to do any special thing in the job step (CmdExec); just to a command like:

DTEXEC /FILE "{path}\Configurations demo 2.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EWCDI

But what I could notice is that the messages about package configurations are not shown in te progress tab; I recently updated to SP2, and I wonder if that has been changed. I dind't even received any warning when I deliberated removed the xml config file...not even in the package logging|||

I. T. wrote:

I couldn’t find any errors or warnings during the package execution.

This is normal behaviour in my experience. I didn't get any warnings or errors either.

Issue with SSIS Package Configurations in SqlServer

Hello everyone,

I am working on a SSIS project and I am facing an issue for getting the configuration settings of the package, once it is deployed and executed from SQL Server agent.

The package uses two configuration types: (listed bellow in the order they are appeared in the configuration editor)

Config1 - Xml configuration file - for storing the database connection string.

Config2 - SQL Server - for storing some user defined variables. It uses the same database as specified in Config1.

Everything works fine and the package uses the database configuration values as defined in Config2, if I execute it from Visual Studio,

However, the package doesn’t get the configuration settings from the database when I try to execute it as a SQL Agent job.

There aren’t any errors and the package executes all tasks successfully, using the connection object Config1 (the same we use to get the config parameters from the database) and the default values of the user defined variables.

It works ok, if I change Config2 to be of type XML configuration file.

There could be two problems:

1. SQL server agent doesn’t read the configuration from the database and I am not quite sure how to set this. In Agent/ Job step properties screen/ Configurations tab I can only browse for a config file. I can also use the command window and /CONFIGFILE option to specify xml file, but how to use it in a case of a database configuration? Is there a /CONFIGDATABSE option or /CONFIGFILE works with database connection as well. I tried with /CONFIGFILE and database connection, but it doesn’t seem to work.

2. SQL server agent doesn’t get the configurations in the specified order. In my case,

it could try to read Config2 first, but at that moment it doesn’t have the database connection from Config1 and it fails. Again, I am not sure how to set the sequence.

Thanks in advance for your comments.

ITHave you specified a full path for Config1?|||

Yes, I specified the full path for Config1. I used the configuration tab to select the file and in the command line window it shows the full path.

The package itself uses the connection from Config1 for the data flow tasks and they work without any issues.

|||

Hi,

What kind of user defined variables are you fetching from the database?

Can you try storing them in package level variables by using a Script Task?

Regards,

B@.ns

|||

I think you can discard option 2. The package configurations should be processed in the order in which they are stored.

It sounds like a permissions problem with the SQl Server Agent account.

Have you tried profiling (SQL Profiler) the package execution to see if it is generating any SQL errors trying to read the configuration table? If the package fails to read the configuration, it only raises a warning, not an error, so it is not always obvious when a configuration fails to load.

|||

I couldn’t find any errors or warnings during the package execution.

Moreover the same connection is used for all data flow tasks inside the package and the SSIS logging (it uses SSIS log provider for SQL) and all of them work without any issues.

As a workaround I can add a SQL script to read those variables from the database, but this will duplicate the configuration functionality.

The idea was to use the existing SSIS database configuration feature instead of building a similar one from scratch.

|||Are you changig the path of the Conf1 in Agent, different than one you have when building the package?|||I.T.

See if you this blog spot can help you identifying the issue:

http://rafael-salas.blogspot.com/2007/01/ssis-package-configurations-using-sql.html

It uses an environment variable instead of a file|||

Hi Rafael,

I checked your blog (actually this was one of the first materials I found when I started exploring the issue a week ago), but in our case we have some system restrictions for using environment variables.

In your example you mentioned also that you used it successfully with XML file.

I would like to ask you if you were to deploy it and execute it as a SQL agent job?

Thanks,

IT

|||Yes, I did, and as far as I remember it worked ok. The only problems I can remember are stupid things like the XML file was not accessible for the account running the SS Agent; not updating the connection string properly withing the XML file (hence trying to connect to the wrong instance), etc. But most of the times, the package logging will tell you if something is wrong with any of the configurations and even shows the order in which the configurations were applied.|||

Rafael,

I would like to ask you if you remember how you set the sql configuration in the SQL Agent job. Did you do something special for this?

I couldn’t see any available options to set explicitly the configuration as SQL server (the UI allows only to browse and select config file).

The package logging is set to log all events and it doesn’t show any errors during the execution.

You mentioned also that the package logging can tell the order of the configuration, but I couldn’t see that kind of information. Is there a special custom event for this?

Thanks,

IT

|||

I just ran some tests using XML and Table configurations (in that order) and works fine in BIDS and as an agent job.

I didn't have to do any special thing in the job step (CmdExec); just to a command like:

DTEXEC /FILE "{path}\Configurations demo 2.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EWCDI

But what I could notice is that the messages about package configurations are not shown in te progress tab; I recently updated to SP2, and I wonder if that has been changed. I dind't even received any warning when I deliberated removed the xml config file...not even in the package logging|||

I. T. wrote:

I couldn’t find any errors or warnings during the package execution.

This is normal behaviour in my experience. I didn't get any warnings or errors either.

Issue with connection pool using JDBC Driver on tomcat 5.0

Hi,

I am facing problem in configuring and using connection pooling on tomcat 5.0. I am using JDBC Driver v.1.1 for SQL Server 2005.

Can anybody point me to appropriate samle code or guideline?

Thanks & Regards,

Bhavin P Panchal

Hi Bhavin,

Please elaborate on what you are having trouble with. Is there any error messages/exceptions raised? If so, please post the error message so we can assist you.

Thanks,

Jimmy

|||

I was getting Invalid Username "sa" for SQL server 2005. I am looking forward for step by step guide on How to configure Connection Pool with latest version of sqljdbc.jar with tomcat 5.0

Thanks & Regards,

Bhavin

|||

It sounds like you would like to connect from tomcat to SQL Server using SQL Server user authentication instead of Windows Integrated authentication. Can you confirm that you can use the "SQL Server Management Studio" (SSMS) application to connect to the SQL Server using the "sa" user account?

If SSMS also reports an error, you can use SSMS to connect using Windows Integrated authentication and modify the configuration to support SQL Server user authentication. Right click on the SQL Server and open the server properties. Under the "Security" option, you can change "Server Authentication" mode to "SQL Server and Windows Authentication mode". You will need to restart SQL Server.

If you can connect use SSMS to SQL Server with the "sa" account, then please provide the connection string in which you are trying to connect with.

For connection pooling, please refer to the following topic http://msdn2.microsoft.com/en-us/library/ms378721.aspx for additional information regarding Microsoft SQL Server 2005 JDBC driver's support.

HTH,

Jimmy

|||

Nope.. I am not using integrated security option to do connection pooling. This username and password are specified in context.xml file of web application developed in java/jsp.

However, thanks for your valuable help.

regards,

Bhavin

|||

Hi Bhavin,

Did you resolve this yet? If so, can you post your sample context.xml file for everyone's benefit.

Thanks,

Jaaved

Issue with connection pool using JDBC Driver on tomcat 5.0

Hi,

I am facing problem in configuring and using connection pooling on tomcat 5.0. I am using JDBC Driver v.1.1 for SQL Server 2005.

Can anybody point me to appropriate samle code or guideline?

Thanks & Regards,

Bhavin P Panchal

Hi Bhavin,

Please elaborate on what you are having trouble with. Is there any error messages/exceptions raised? If so, please post the error message so we can assist you.

Thanks,

Jimmy

|||

I was getting Invalid Username "sa" for SQL server 2005. I am looking forward for step by step guide on How to configure Connection Pool with latest version of sqljdbc.jar with tomcat 5.0

Thanks & Regards,

Bhavin

|||

It sounds like you would like to connect from tomcat to SQL Server using SQL Server user authentication instead of Windows Integrated authentication. Can you confirm that you can use the "SQL Server Management Studio" (SSMS) application to connect to the SQL Server using the "sa" user account?

If SSMS also reports an error, you can use SSMS to connect using Windows Integrated authentication and modify the configuration to support SQL Server user authentication. Right click on the SQL Server and open the server properties. Under the "Security" option, you can change "Server Authentication" mode to "SQL Server and Windows Authentication mode". You will need to restart SQL Server.

If you can connect use SSMS to SQL Server with the "sa" account, then please provide the connection string in which you are trying to connect with.

For connection pooling, please refer to the following topic http://msdn2.microsoft.com/en-us/library/ms378721.aspx for additional information regarding Microsoft SQL Server 2005 JDBC driver's support.

HTH,

Jimmy

|||

Nope.. I am not using integrated security option to do connection pooling. This username and password are specified in context.xml file of web application developed in java/jsp.

However, thanks for your valuable help.

regards,

Bhavin

|||

Hi Bhavin,

Did you resolve this yet? If so, can you post your sample context.xml file for everyone's benefit.

Thanks,

Jaaved

Issue with checkpoints and Event handlers

Hi,

We are currently facing an issue in ensuring restartability of an SSIS package. The scenario is explained below.

Context:

The SSIS Package has two Data Flow tasks.The Data Flow task named DFT1 is the predecessor for DFT2 and chained with OnSuccess precedence constraint.

OnPreExecute and OnPostExecute event handlers have been implemented for DFT1. Each task in both event handlers as well as DFT1 and DFT2 have FailPackageOnFailure set to True.

Scenario1: Task in OnPreExecute of DFT1 fails.

DFT1 is attempted and succeeded.

OnPostExecute of DFT1 was not attempted.

DFT2 was not attempted.

Checkpoint file was created; however, no entries were made.

When restarted, execution started from first step in Control flow.

Scenario2: Task in OnPostExecute of DFT1 fails.

DFT1 and its OnPreExecute Event were executed.

DFT2 was not attempted.

Checkpoint file was created and entries were made.Entries had DTS:result as 0 for OnPreExecute and DFT1 tasks.

When restarted, DFT2 was executed.OnPostExecute event, which failed during previous execution, was not attempted.

Each task in the package, whether it is in Control flow or as part of an event handler is crucial for seamless execution. But apparently, as explained above, there is no reliability on the event handlers in case of failures. Has anyone encountered similar scenario? Is this behavior as per design of the runtime engine?

Thanks, in advance,

Regards,

Rajesh

Rajesh Sridharan wrote:

Hi,

We are currently facing an issue in ensuring restartability of an SSIS package. The scenario is explained below.

Context:

The SSIS Package has two Data Flow tasks. The Data Flow task named DFT1 is the predecessor for DFT2 and chained with OnSuccess precedence constraint.

OnPreExecute and OnPostExecute event handlers have been implemented for DFT1. Each task in both event handlers as well as DFT1 and DFT2 have FailPackageOnFailure set to True.

Scenario1: Task in OnPreExecute of DFT1 fails.

DFT1 is attempted and succeeded.

OnPostExecute of DFT1 was not attempted.

DFT2 was not attempted.

Checkpoint file was created; however, no entries were made.

When restarted, execution started from first step in Control flow.

Scenario2: Task in OnPostExecute of DFT1 fails.

DFT1 and its OnPreExecute Event were executed.

DFT2 was not attempted.

Checkpoint file was created and entries were made. Entries had DTS:result as 0 for OnPreExecute and DFT1 tasks.

When restarted, DFT2 was executed. OnPostExecute event, which failed during previous execution, was not attempted.

Each task in the package, whether it is in Control flow or as part of an event handler is crucial for seamless execution. But apparently, as explained above, there is no reliability on the event handlers in case of failures. Has anyone encountered similar scenario? Is this behavior as per design of the runtime engine?

Thanks, in advance,

Regards,

Rajesh

This is a complicated scenario but I think this is as expected. The only tasks for which the checkpoint is written to are those in the control-flow. In scenario 1 DFT1 has not completed therefore execution will start from there on the second execution. In scenario 2 DFT2 HAS completed therfore execution will start from DFT2 on the second execution.

Tasks in the eventhandler do not affect checkpoint files, now should they. If you have "things" happening in your package that are critical to the execution of the package then they should be in the control-flow.

-Jamie

|||

Thank you, Jamie. Appreciate your response.

Rajesh

Issue while exporting large reports to Excel

I am facing a situation where I am receiving the "Index out of bounds" error while trying to export my reports to excel.

This happens only for big reports which have say more then 10k rows.
However the same reports are easily exported in another m/c, which makes me wonder if it has something to do with the IIS settings.
I am running Windows Server 2003 with IIS 6.0 in my machine.

The difference was SP1 for SQL Server.|||

Would you please email me a copy of your .rdl and .rdl.data file as well as the stack trace from the server log file?

Thanks, Donovan.

Issue while exporting large reports to Excel

I am facing a situation where I am receiving the "Index out of bounds" error while trying to export my reports to excel.

This happens only for big reports which have say more then 10k rows.
However the same reports are easily exported in another m/c, which makes me wonder if it has something to do with the IIS settings.
I am running Windows Server 2003 with IIS 6.0 in my machine.

The difference was SP1 for SQL Server.|||

Would you please email me a copy of your .rdl and .rdl.data file as well as the stack trace from the server log file?

Thanks, Donovan.

Issue in Transaction

Dear All,

I'm very much new to SQL and right now working with basics before moving into complex SPs.

I'm facing issues in executing a simple transaction in sql. The following is the code:

"

BEGINTRANSACTION

INSERT INTO MyDatabase.dbo.Order (OrderID) values('1234')

INSERTINTO MyDatabase.dbo.MyTable (Age)values('we')

IF(@.@.ERROR<> 0)

BEGIN

PRINT('Exception Raised')

ROLLBACKTRANSACTION

END

COMMITTRANSACTION

"

The age column is of int data type and I'm trying to rollback transaction by passing char value to that column. On executing the above query, it does not go to rollback transaction part whereas it gives the error

"

Msg 245, Level 16, State 1, Line 4

Conversion failed when converting the varchar value 'we' to data type int.

"

Kindly request you all to help me in this regard.

Most conversion errors will fire a batch abort error, which will stop the batch from executing. If you are using SQL Server 2005, you can use the new error functionality which allows you to catch statement as well as batch abort errors.

Jens K. Suessmeyer.

http://www.sqlserver2005.de