Job Activity Monitor

Hi,

I have a problem with Job Activity Monitor.

I have a SSIS which copies data from a table to a flat file. The connection string of this file is variable and the file is reused if not exists and is created if exists.

When I run the SSIS manually from my microsoft visual studio it works properly. However, when I run this SSIS from the Job Activity Monitor, I get the following error:

Message
Executed as user: REDCAM\adminsql2k5. Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 8:41:10 AM Error: 2007-09-10 08:48:04.99 Code: 0xC020200E Source: Crear Historico Historico [1] Description: Cannot open the datafile "\\srvnfile\HISTORICOS\CAJEROS\OFI3210C01\OFI3210C01_2007-7X.txt". End Error Error: 2007-09-10 08:48:04.99 Code: 0xC004701A Source: Crear Historico DTS.Pipeline Description: component "Historico" (1) failed the pre-execute phase and returned error code 0xC020200E. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 8:41:10 AM Finished: 8:48:53 AM Elapsed: 462.234 seconds. The package execution failed. The step failed.

Any idea?

Thanks.

Does the SQL Server service account have access to \\srvnfile\HISTORICOS?|||

Yes the SQL Server service account have access to \\srvnfile\HISTORICOS.

|||

Did you find the resolution to this issue?

|||Had the same problem - found this ans in sql server central.

Are you running SQL Server agent using a local account on the SQL box? If so, you'll have problems copying the file out to a network share. If this is the case, try restarting SQL Server Agent using a domain account that has write access to that share. (Tim)

That wasn't my problem. I used a mapped drive. (my perspective) Don't do that.

Job Activity Monitor

Hi,

I have a problem with Job Activity Monitor.

I have a SSIS which copies data from a table to a flat file. The connection string of this file is variable and the file is reused if not exists and is created if exists.

When I run the SSIS manually from my microsoft visual studio it works properly. However, when I run this SSIS from the Job Activity Monitor, I get the following error:

Message
Executed as user: REDCAM\adminsql2k5. Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 8:41:10 AM Error: 2007-09-10 08:48:04.99 Code: 0xC020200E Source: Crear Historico Historico [1] Description: Cannot open the datafile "\\srvnfile\HISTORICOS\CAJEROS\OFI3210C01\OFI3210C01_2007-7X.txt". End Error Error: 2007-09-10 08:48:04.99 Code: 0xC004701A Source: Crear Historico DTS.Pipeline Description: component "Historico" (1) failed the pre-execute phase and returned error code 0xC020200E. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 8:41:10 AM Finished: 8:48:53 AM Elapsed: 462.234 seconds. The package execution failed. The step failed.

Any idea?

Thanks.

Does the SQL Server service account have access to \\srvnfile\HISTORICOS?|||

Yes the SQL Server service account have access to \\srvnfile\HISTORICOS.

|||

Did you find the resolution to this issue?

jdbc refuses connection with 'user not associated with a trusted SQL Server connection' messag

When using jdbc with IntegratedSecurity, I run into this problem when the machine is not part of a domain & gets its IP address via dhcp. Is this expected behavior or a bug in the jdbc driver.

The SQLServer and client application are installed on the same machine and a local admin is logged in, running the client app.

If I change one of the two parameters mentioned above, the connection can be established leading me to believe this may be intentional for security issues. Am I correct?

To clarify, "not part of domain" means you disconnected from the domain or you machine is part of workgroup? Do you mean that "IP address works but server name dosen't or otherwise around.|||That is correct. If I have the scenario where the machine is part of a workgroup and I am using dhcp, the connection gets refused. If I either add the machine to a domain or assign a static IP address to the NIC), the connection succeeds. So, what I meant by 'using dhcp' is that the NIC is configured to use dhcp in the TCP/IP settings of the Network Connection properties. I have not tried an environment where one NIC is dhcp and another is static.

Also, if I join a domain, the connection will succeed with the NIC configured for dhcp.

Hopefully that is clearer.

|||

To help us understand the problem and narrow down where the problem might be, please provide us with the SQL Server version and the JDBC driver version?

Please also provide the JDBC connection URL (minus any sensitive data).

Can you verify that another driver (say SNAC / MDAC) properly connects to SQL Server in the same configuration to make sure it is a JDBC driver issue versus some machine configuration issue.

If your SQL Server is SQL Server 2005, you can find the "sqlcmd.exe" tool in the "Microsoft SQL Server\90\Tools\Binn\" install directory. You can connect with Integrated Authentication to the local SQL Server using sqlcmd.exe with the following syntax: sqlcmd -Hlocalhost -E

Alternatively, if you have "osql.exe" you can perform the same test "osql -Hlocalhost -E". If you do not have access to either of these tools, you can also use a "Universal Data Link" file. Please see http://msdn2.microsoft.com/en-us/library/e38h511e(vs.71).aspx and http://support.microsoft.com/kb/274536 for additional information.

This will help narrow down if there is an issue with machine configuration or JDBC driver.

Jimmy

|||SQL Version: SQL Server 2005 Express Edition (tried both SP1 and SP2)
JDBC Version: 1.1

Since I am using SQL Server 2005, I did run both sqlcmd and osql and was able to execute the following statement

SELECT convert(varchar(15), SERVERPROPERTY('productversion')), convert(varchar(5), SERVERPROPERTY('productlevel')), convert(varchar(20), SERVERPROPERTY('edition'))

I got a row back with the version information I expected so it looks like I was able to connect. In addition, SQL Server Management Studio is able to run and connect with Windows Authentication.

Here is the code that I am trying to execute. The intention of this snippet it to connect to SQL Server so that I can create a database. The user running the application had admin rights.

Code Snippet

try
(
String address = "1.1.1.1";
String port = "1433";
String db_name = "MyAppDB";
Connection my_connection;
String url;
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
url = "jdbc:sqlserver://" + address + ":" + port + ";integratedSecurity=true;";
my_connection = DriverManager.getConnection(url);

Statement create_stmt = conn.createStatement();
create_stmt.execute("CREATE DATABASE " + db_name);
}
catch (SQLException e)
{
//WriteErrorMsg("Error establishing JDBC connection (" + e.getErrorCode() + ") " + e.getMessage());
//return OPERATION_ERROR;
}
catch (ClassNotFoundException e)
{
//WriteErrorMsg("Class error creating " + db_name + ": " + e.getMessage());
//return EXCEPTION_CAUGHT;
}

Let me know if there is any other info I can get you.
Thanks for the help
john
|||

In the code snippet provided you are attempting to connect to an IP address of 1.1.1.1 and port 1433. If your machine has DHCP enabled, does it actually get 1.1.1.1 as the IP address?

If you only plan to run the client application on the same machine as SQL Server, then I would recommend either specifying "localhost" as the server name or the actual server name in the connection URL.

example: url = "jdbcqlserver://localhost:1433;integratedSecurity=true;"

Also, typically SQL Server Express is not installed to use the fixed port 1433, did you configure the instance to the fixed 1433 port?

If not, I would recommend enabling the SQL Browser service and update the connection URL to something like:

url = "jdbcqlserver://localhost;instanceName=SQLEXPRESS;integratedSecurity=true;"

HTH,

Jimmy

|||That was not the real address. The snippet is part of function where the address is passed in as a paramenter. It would either be set to an address(ie 10.47.20.46) or localhost. I just put that in as more of placeholder. Bad choice of documenting, sorry for any confusion. The SQL server may or may not be on the same machine. In the case where SQL is local on another machine, I would not use integrated security and provide credentials for SQL authentication.

Yes, SQL is configured to configured for port 1433 in this case.

thanks

jdbc refuses connection with ''user not associated with a trusted SQL Server connection'' me

When using jdbc with IntegratedSecurity, I run into this problem when the machine is not part of a domain & gets its IP address via dhcp. Is this expected behavior or a bug in the jdbc driver.

The SQLServer and client application are installed on the same machine and a local admin is logged in, running the client app.

If I change one of the two parameters mentioned above, the connection can be established leading me to believe this may be intentional for security issues. Am I correct?

To clarify, "not part of domain" means you disconnected from the domain or you machine is part of workgroup? Do you mean that "IP address works but server name dosen't or otherwise around.|||That is correct. If I have the scenario where the machine is part of a workgroup and I am using dhcp, the connection gets refused. If I either add the machine to a domain or assign a static IP address to the NIC), the connection succeeds. So, what I meant by 'using dhcp' is that the NIC is configured to use dhcp in the TCP/IP settings of the Network Connection properties. I have not tried an environment where one NIC is dhcp and another is static.

Also, if I join a domain, the connection will succeed with the NIC configured for dhcp.

Hopefully that is clearer.

|||

To help us understand the problem and narrow down where the problem might be, please provide us with the SQL Server version and the JDBC driver version?

Please also provide the JDBC connection URL (minus any sensitive data).

Can you verify that another driver (say SNAC / MDAC) properly connects to SQL Server in the same configuration to make sure it is a JDBC driver issue versus some machine configuration issue.

If your SQL Server is SQL Server 2005, you can find the "sqlcmd.exe" tool in the "Microsoft SQL Server\90\Tools\Binn\" install directory. You can connect with Integrated Authentication to the local SQL Server using sqlcmd.exe with the following syntax: sqlcmd -Hlocalhost -E

Alternatively, if you have "osql.exe" you can perform the same test "osql -Hlocalhost -E". If you do not have access to either of these tools, you can also use a "Universal Data Link" file. Please see http://msdn2.microsoft.com/en-us/library/e38h511e(vs.71).aspx and http://support.microsoft.com/kb/274536 for additional information.

This will help narrow down if there is an issue with machine configuration or JDBC driver.

Jimmy

|||SQL Version: SQL Server 2005 Express Edition (tried both SP1 and SP2)
JDBC Version: 1.1

Since I am using SQL Server 2005, I did run both sqlcmd and osql and was able to execute the following statement

SELECT convert(varchar(15), SERVERPROPERTY('productversion')), convert(varchar(5), SERVERPROPERTY('productlevel')), convert(varchar(20), SERVERPROPERTY('edition'))

I got a row back with the version information I expected so it looks like I was able to connect. In addition, SQL Server Management Studio is able to run and connect with Windows Authentication.

Here is the code that I am trying to execute. The intention of this snippet it to connect to SQL Server so that I can create a database. The user running the application had admin rights.

Code Snippet

try
(
String address = "1.1.1.1";
String port = "1433";
String db_name = "MyAppDB";
Connection my_connection;
String url;
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
url = "jdbc:sqlserver://" + address + ":" + port + ";integratedSecurity=true;";
my_connection = DriverManager.getConnection(url);

Statement create_stmt = conn.createStatement();
create_stmt.execute("CREATE DATABASE " + db_name);
}
catch (SQLException e)
{
//WriteErrorMsg("Error establishing JDBC connection (" + e.getErrorCode() + ") " + e.getMessage());
//return OPERATION_ERROR;
}
catch (ClassNotFoundException e)
{
//WriteErrorMsg("Class error creating " + db_name + ": " + e.getMessage());
//return EXCEPTION_CAUGHT;
}

Let me know if there is any other info I can get you.
Thanks for the help
john
|||

In the code snippet provided you are attempting to connect to an IP address of 1.1.1.1 and port 1433. If your machine has DHCP enabled, does it actually get 1.1.1.1 as the IP address?

If you only plan to run the client application on the same machine as SQL Server, then I would recommend either specifying "localhost" as the server name or the actual server name in the connection URL.

example: url = "jdbcqlserver://localhost:1433;integratedSecurity=true;"

Also, typically SQL Server Express is not installed to use the fixed port 1433, did you configure the instance to the fixed 1433 port?

If not, I would recommend enabling the SQL Browser service and update the connection URL to something like:

url = "jdbcqlserver://localhost;instanceName=SQLEXPRESS;integratedSecurity=true;"

HTH,

Jimmy

|||That was not the real address. The snippet is part of function where the address is passed in as a paramenter. It would either be set to an address(ie 10.47.20.46) or localhost. I just put that in as more of placeholder. Bad choice of documenting, sorry for any confusion. The SQL server may or may not be on the same machine. In the case where SQL is local on another machine, I would not use integrated security and provide credentials for SQL authentication.

Yes, SQL is configured to configured for port 1433 in this case.

thanks

JDBC or ODBC?

I am in the interesting position of having to choose between using an ODBC or
JDBC connection for a high traffic site. We can develop on either
environment though I prefer the Java based one. Database connectivity to our
SQL 2000 server is our number one priority. Does anybody know which offers
the greatest performance and stability or at the very least point me in the
right direction?
PJ wrote:
> I am in the interesting position of having to choose between using an ODBC or
> JDBC connection for a high traffic site. We can develop on either
> environment though I prefer the Java based one. Database connectivity to our
> SQL 2000 server is our number one priority. Does anybody know which offers
> the greatest performance and stability or at the very least point me in the
> right direction?
For stability and support, you want JDBC. Performance should be a wash at least,
and unless you have reliability, you usually don't have anything. The JDBC-ODBC
bridge is notably buggy and unsupported. JDBC performance will come from pooling
and re-using open connections and similarly caching Prepared statements, and
managing any transactional work to single user-invokes (Ie: not holding locks
until a user application sends a second message). Primarily, performance will
come by doing as much of the DBMS-related work in the DBMS where the data is.
Write and use big fancy stored procedures. Always do all you can to avoid
shipping huge wads of raw data out of the DBMS to be processed.
Good luck,
Joe Weinstein at BEA Systems
|||Thanks for the reply!
To clarify, we are choosing between a server that sits on top of Windows
2000 Server that can use ODBC directly with SQL 2000, or a new version of the
same server that sits on top of a Java platform and uses JDBC (Type 4).
Either way we will not be using a bridge. Our concern is the performance
implication directly related to the database access interface. How does JDBC
compare with ODBC in this situation? Are there any reliability or
performance implications we need to consider? Thanks!
|||PJ wrote:

> Thanks for the reply!
> To clarify, we are choosing between a server that sits on top of Windows
> 2000 Server that can use ODBC directly with SQL 2000, or a new version of the
> same server that sits on top of a Java platform and uses JDBC (Type 4).
> Either way we will not be using a bridge. Our concern is the performance
> implication directly related to the database access interface. How does JDBC
> compare with ODBC in this situation? Are there any reliability or
> performance implications we need to consider? Thanks!
OK, then I don't have the data. The server vendor may... Many other factors
may contribute to which product is overall better. There may be a stability
reason for the vendor making a Java version (besides the ease of making one
such for all platforms).
joe
|||I agree with Joe in the most part. From my years of experience with db
systems and apis, the API/interface used to access the database is usually
not the performance bottleneck. The biggest problem is usually the devs
not understanding the most efficient means of doing what they need to do
with the target db. For example they select * from table instead of using
where clause to limit records, etc...
But if it came down to it, I bet I could beat slightly performance of JDBC
code with ODBC code (with alot of extra work). The reason for this is ODBC
is accessable from C++ directly, and you can pass buffers directly from C++
to ODBC API without any conversion. So the data goes straight from my C++
buffer to the driver to the wire and back. With JDBC I have to call some
setter or getter and then this has to translate from UNICODE Java string to
bytes before sending over wire, etc...
But at the end of the day, I will spent 4 weeks writing a bunch of C++ code
to get a few extra milliseconds of perf. I could have written the whole app
in JDBC in a few days, etc... The same argument goes for use VB6 or VB.NET
to SQL, it's very easy to write the code, the perf is not that much worse
than C++, why not use it, etc...
So as the saying goes, don't optimize too early. (G)
Matt Neerincx [MSFT]
This posting is provided "AS IS", with no warranties, and confers no rights.
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.
"Joe Weinstein" <joeNOSPAM@.bea.com> wrote in message
news:%23eHmr2YrFHA.1032@.TK2MSFTNGP12.phx.gbl...
>
> PJ wrote:
>
> OK, then I don't have the data. The server vendor may... Many other
> factors
> may contribute to which product is overall better. There may be a
> stability
> reason for the vendor making a Java version (besides the ease of making
> one
> such for all platforms).
> joe
>

JDBC Driver for SQL 2000 - Pls help

One of the applications "Epistemic" which my client is using needs a JDBC 3.0
Compliant driver to connection to out SQL Server 2000 instance
Questions
1. Is this driver available in installation of client software for SQL
Server 2000, if not where can i get it
2. Like for ODBC we set up a profile in ODBC administrator, what do we do
for JDBC
3. Will accessing SQL 2000 through JDBC drive be any slower than using OLEDB
which is the default connectivity software for SQL Server 2000
Thanks
Hi
The Microsoft driver is a type 4 driver and downloadable from
http://www.microsoft.com/downloads/d...displaylang=en
the included help and readme files show how it should be used.
For other options look at
http://servlet.java.sun.com/products...ers/index.html
You may also want to check the posts in microsoft.public.sqlserver.jdbcdriver.
John
"Sanjay" wrote:

> One of the applications "Epistemic" which my client is using needs a JDBC 3.0
> Compliant driver to connection to out SQL Server 2000 instance
> Questions
> 1. Is this driver available in installation of client software for SQL
> Server 2000, if not where can i get it
> 2. Like for ODBC we set up a profile in ODBC administrator, what do we do
> for JDBC
> 3. Will accessing SQL 2000 through JDBC drive be any slower than using OLEDB
> which is the default connectivity software for SQL Server 2000
> Thanks
>
|||Sanjay wrote:
> One of the applications "Epistemic" which my client is using needs a
JDBC 3.0
> Compliant driver to connection to out SQL Server 2000 instance
> Questions
> 1. Is this driver available in installation of client software for
SQL
> Server 2000, if not where can i get it
The Microsoft driver is only a JDBC 2.0 implementation and it has a
number of problems, ranging from stability to performance, in which you
seem to be interested. E.g. if you need transaction support (which you
probably do) the MS driver will use server side cursors and will make a
request for each row you retrieve on a SELECT.

> 2. Like for ODBC we set up a profile in ODBC administrator, what do
we do
> for JDBC
Nothing, just drop the jar in the classpath and configure the URL in
your application.

> 3. Will accessing SQL 2000 through JDBC drive be any slower than
using OLEDB
> which is the default connectivity software for SQL Server 2000
With the MS driver, probably so. Try jTDS (
http://jtds.sourceforge.net/ ) instead. It's a JDBC 3.0 implementation,
it's way more stable than the MS driver and as fast as OLEDB.
Alin,
The jTDS Project.
Disclaimer: I am a jTDS developer so my views might not be totally
objective.

JDBC Driver for SQL 2000 - Pls help

One of the applications "Epistemic" which my client is using needs a JDBC 3.0
Compliant driver to connection to out SQL Server 2000 instance
Questions
1. Is this driver available in installation of client software for SQL
Server 2000, if not where can i get it
2. Like for ODBC we set up a profile in ODBC administrator, what do we do
for JDBC
3. Will accessing SQL 2000 through JDBC drive be any slower than using OLEDB
which is the default connectivity software for SQL Server 2000
ThanksHi
The Microsoft driver is a type 4 driver and downloadable from
http://www.microsoft.com/downloads/details.aspx?FamilyID=07287B11-0502-461A-B138-2AA54BFDC03A&displaylang=en
the included help and readme files show how it should be used.
For other options look at
http://servlet.java.sun.com/products/jdbc/drivers/index.html
You may also want to check the posts in microsoft.public.sqlserver.jdbcdriver.
John
"Sanjay" wrote:
> One of the applications "Epistemic" which my client is using needs a JDBC 3.0
> Compliant driver to connection to out SQL Server 2000 instance
> Questions
> 1. Is this driver available in installation of client software for SQL
> Server 2000, if not where can i get it
> 2. Like for ODBC we set up a profile in ODBC administrator, what do we do
> for JDBC
> 3. Will accessing SQL 2000 through JDBC drive be any slower than using OLEDB
> which is the default connectivity software for SQL Server 2000
> Thanks
>|||Sanjay wrote:
> One of the applications "Epistemic" which my client is using needs a
JDBC 3.0
> Compliant driver to connection to out SQL Server 2000 instance
> Questions
> 1. Is this driver available in installation of client software for
SQL
> Server 2000, if not where can i get it
The Microsoft driver is only a JDBC 2.0 implementation and it has a
number of problems, ranging from stability to performance, in which you
seem to be interested. E.g. if you need transaction support (which you
probably do) the MS driver will use server side cursors and will make a
request for each row you retrieve on a SELECT.
> 2. Like for ODBC we set up a profile in ODBC administrator, what do
we do
> for JDBC
Nothing, just drop the jar in the classpath and configure the URL in
your application.
> 3. Will accessing SQL 2000 through JDBC drive be any slower than
using OLEDB
> which is the default connectivity software for SQL Server 2000
With the MS driver, probably so. Try jTDS (
http://jtds.sourceforge.net/ ) instead. It's a JDBC 3.0 implementation,
it's way more stable than the MS driver and as fast as OLEDB.
Alin,
The jTDS Project.
Disclaimer: I am a jTDS developer so my views might not be totally
objective.

JDBC Driver for SQL 2000 - Pls help

One of the applications "Epistemic" which my client is using needs a JDBC 3.
0
Compliant driver to connection to out SQL Server 2000 instance
Questions
1. Is this driver available in installation of client software for SQL
Server 2000, if not where can i get it
2. Like for ODBC we set up a profile in ODBC administrator, what do we do
for JDBC
3. Will accessing SQL 2000 through JDBC drive be any slower than using OLEDB
which is the default connectivity software for SQL Server 2000
ThanksHi
The Microsoft driver is a type 4 driver and downloadable from
http://www.microsoft.com/downloads/...&displaylang=en
the included help and readme files show how it should be used.
For other options look at
http://servlet.java.sun.com/product...vers/index.html
You may also want to check the posts in microsoft.public.sqlserver.jdbcdrive
r.
John
"Sanjay" wrote:

> One of the applications "Epistemic" which my client is using needs a JDBC
3.0
> Compliant driver to connection to out SQL Server 2000 instance
> Questions
> 1. Is this driver available in installation of client software for SQL
> Server 2000, if not where can i get it
> 2. Like for ODBC we set up a profile in ODBC administrator, what do we do
> for JDBC
> 3. Will accessing SQL 2000 through JDBC drive be any slower than using OLE
DB
> which is the default connectivity software for SQL Server 2000
> Thanks
>|||Sanjay wrote:
> One of the applications "Epistemic" which my client is using needs a
JDBC 3.0
> Compliant driver to connection to out SQL Server 2000 instance
> Questions
> 1. Is this driver available in installation of client software for
SQL
> Server 2000, if not where can i get it
The Microsoft driver is only a JDBC 2.0 implementation and it has a
number of problems, ranging from stability to performance, in which you
seem to be interested. E.g. if you need transaction support (which you
probably do) the MS driver will use server side cursors and will make a
request for each row you retrieve on a SELECT.

> 2. Like for ODBC we set up a profile in ODBC administrator, what do
we do
> for JDBC
Nothing, just drop the jar in the classpath and configure the URL in
your application.

> 3. Will accessing SQL 2000 through JDBC drive be any slower than
using OLEDB
> which is the default connectivity software for SQL Server 2000
With the MS driver, probably so. Try jTDS (
http://jtds.sourceforge.net/ ) instead. It's a JDBC 3.0 implementation,
it's way more stable than the MS driver and as fast as OLEDB.
Alin,
The jTDS Project.
Disclaimer: I am a jTDS developer so my views might not be totally
objective.