J# using JDBC for SQL Server

code:

/* Establish a test connection to remote SQLServer in J# using JDBC */
package JSharpConnTest;
import java.sql.*;
public class JSharpConnTest
{
private static ResultSet rs;
private static Connection conn = null;
private static Statement stmt = null;
private static final String sSubprotocol = "jdbc:microsoft:sqlserver://";
private static final String sServerName = "CLUSTERTEST";
private static final String sPortNumber = "1433";
private static final String sDBName = "ClientLetterWorkRequests";
private static final String sUserName = "sa";
private static final String sPassword = "1111";
private static final String sSQLQuery = "SELECT * FROM CLDatabases";
private static final String sURL = sSubprotocol + sServerName +
":" + sPortNumber + ";
databaseName=" + sDBName + ";
";
public static void main(String[] args)
{
getConnection();
}
public static void getConnection()
{
System.out.println( "Connecting to.. " + sURL );
try
{
//Register the driver
// Microsoft SQL Server 2000 Driver for JDBC
// --problem locating driver?
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
// Pass connection URL
conn = DriverManager.getConnection(sURL, sUserName, sPassword);
// Query database
stmt = conn.createStatement();
rs = stmt.executeQuery(sSQLQuery);
// Test data retrieval
while (rs.next());
{
String colOne = rs.getString("DATABASEKEY");
String colTwo = rs.getString("SERVERTYPE");
System.out.println(colOne + " " + colTwo);
}
}
catch (java.lang.ClassNotFoundException ex)
{
System.err.println("\nClassNotFoundException: " + ex.getMessage());
}
catch (SQLException ex)
{
System.err.println("\nSQLException: " + ex.getMessage());
}
catch (Exception ex)
{
System.err.println("\nException: " + ex.getMessage());
}
finally
{
// Release resources
try
{
if (conn != null)
conn.close();
if (stmt != null)
stmt.close();
}
catch (Exception ex)
{
System.err.println("\nException: " + ex.getMessage());
}
}
} // end getConnection()
} // end JSharpConnTest

This statement:
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
Causes the following exception:
[java.lang.ClassNotFoundException]{
"com.microsoft.jdbc.sqlserver.SQLServerDriver"}java.lang.ClassNotFoundExcep
tion
I installed Microsoft SQL Server 2000 Driver for JDBC
I believe the problem is with locating the driver, unlike Java, J# doesn't
use classpath enviromental variable. I am not sure how to add the driver
reference in J#.Hi
Why use JDBC when ADO.NET can do everything for you?
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"RobertStout" <
RobertStout@.discussions.microsoft.com>
wrote in message
news:4FB06586-4C07-4886-A9E5-444A85DA4F31@.microsoft.com...
>

code:

>
 /* Establish a test connection to remote SQLServer in J# using JDBC */
>
>
 package JSharpConnTest;
>
 import java.sql.*;
>
>
 public class JSharpConnTest
>
 {
>
 private static ResultSet rs;
>
 private static Connection conn = null;
>
 private static Statement stmt = null;
>
 private static final String sSubprotocol = "jdbc:microsoft:sqlserver://";
>
 private static final String sServerName = "CLUSTERTEST";
>
 private static final String sPortNumber = "1433";
>
 private static final String sDBName = "ClientLetterWorkRequests";
>
 private static final String sUserName = "sa";
>
 private static final String sPassword = "1111";
>
 private static final String sSQLQuery = "SELECT * FROM CLDatabases";
>
 private static final String sURL = sSubprotocol + sServerName +
>
 ":" + sPortNumber + ";
databaseName=" + sDBName + ";
";
>
>
 public static void main(String[] args)
>
 {
>
 getConnection();
>
 }
>
>
 public static void getConnection()
>
 {
>
 System.out.println( "Connecting to.. " + sURL );
>
>
 try
>
 {
>
 //Register the driver
>
 // Microsoft SQL Server 2000 Driver for JDBC
>
 // --problem locating driver?
>
 Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
>
>
 // Pass connection URL
>
 conn = DriverManager.getConnection(sURL, sUserName, sPassword);
>
>
 // Query database
>
 stmt = conn.createStatement();
>
 rs = stmt.executeQuery(sSQLQuery);
>
>
 // Test data retrieval
>
 while (rs.next());
>
 {
>
 String colOne = rs.getString("DATABASEKEY");
>
 String colTwo = rs.getString("SERVERTYPE");
>
 System.out.println(colOne + " " + colTwo);
>
 }
>
 }
>
 catch (java.lang.ClassNotFoundException ex)
>
 {
>
 System.err.println("\nClassNotFoundException: " + ex.getMessage());
>
 }
>
 catch (SQLException ex)
>
 {
>
 System.err.println("\nSQLException: " + ex.getMessage());
>
 }
>
 catch (Exception ex)
>
 {
>
 System.err.println("\nException: " + ex.getMessage());
>
 }
>
 finally
>
 {
>
 // Release resources
>
 try
>
 {
>
 if (conn != null)
>
 conn.close();
>
 if (stmt != null)
>
 stmt.close();
>
 }
>
 catch (Exception ex)
>
 {
>
 System.err.println("\nException: " + ex.getMessage());
>
 }
>
 }
>
>
>
 } // end getConnection()
>
 } // end JSharpConnTest
>

>
>
This statement:
>
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
>
>
Causes the following exception:
>

[java.lang.ClassNotFoundException]{
"com.microsoft.jdbc.sqlserver.SQLServerDr
iver"}java.lang.ClassNotFoundException
>
>
I installed Microsoft SQL Server 2000 Driver for JDBC
>
I believe the problem is with locating the driver, unlike Java, J# doesn't
>
use classpath enviromental variable. I am not sure how to add the driver
>
reference in J#.|||If I could use ADO.NET I would of been done with the entire app a long time
ago.
Sadly, I have to use JDBC.
Can you help?
Thanks
-Rob
I would much rather use ADO.NET but I can't, I have to use JDBC.
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> Why use JDBC when ADO.NET can do everything for you?
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "RobertStout" <RobertStout@.discussions.microsoft.com> wrote in message
> news:4FB06586-4C07-4886-A9E5-444A85DA4F31@.microsoft.com...
> [java.lang.ClassNotFoundException]{"com.microsoft.jdbc.sqlserver.SQLServerDr
> iver"}java.lang.ClassNotFoundException
>
>sql

Issues with moving backups via XCopy

When we take backups, we xcopy them from a folder (on a remote server) containing all of the backups for the databases on that server accross a network onto a central server, into a folder for that server/database. However, should one of the backups we have moved accross the network and onto the central server get deleted or moved, the XCopy does not attempt to move the copy from the remote server back onto the central server the next time we run the procedure...so is there a setting that essentially states, "sync up the source and destination, in order to make the destination match the host"? and what causes XCopy to ignore the files it previously moved into the destination? Is it a property on the file? Any advice?

thanks in advance!

SQL Server 2000

Normally, XCOPY just copies all specified files to the destination. It is not a sophisticated sync mechanism.

If you can give us the exact XCOPY command you're using, perhaps a different setting of the flags would accomplish what you need it to.

|||can I suggest you look at robocopy, this is, imho, a far superior copy utility that I've been using since way back.|||

I just thought I would add my experiences with robocopy. I have been fighting the ability to give it a path containing spaces. Typically I would accomplish this feat by placing quotes around each path but this didn't seem to be working for robocopy.

I have a habit of ending all my paths with a "\" and discovered that robocopy interperets a \" as an escaped quote. The program works great and functions exactly how I would expect if I simply remove the trailing backslash.

I found this functionality to be very strange considering I have never experienced the problem with any other microsoft command line utilities. Not only that but a quote isn't even a valid character in a folder or file so it isn't possible that the path would contain them.

Issues with moving backups via XCopy

When we take backups, we xcopy them from a folder (on a remote server) containing all of the backups for the databases on that server accross a network onto a central server, into a folder for that server/database. However, should one of the backups we have moved accross the network and onto the central server get deleted or moved, the XCopy does not attempt to move the copy from the remote server back onto the central server the next time we run the procedure...so is there a setting that essentially states, "sync up the source and destination, in order to make the destination match the host"? and what causes XCopy to ignore the files it previously moved into the destination? Is it a property on the file? Any advice?

thanks in advance!

SQL Server 2000

Normally, XCOPY just copies all specified files to the destination. It is not a sophisticated sync mechanism.

If you can give us the exact XCOPY command you're using, perhaps a different setting of the flags would accomplish what you need it to.

|||can I suggest you look at robocopy, this is, imho, a far superior copy utility that I've been using since way back.|||

I just thought I would add my experiences with robocopy. I have been fighting the ability to give it a path containing spaces. Typically I would accomplish this feat by placing quotes around each path but this didn't seem to be working for robocopy.

I have a habit of ending all my paths with a "\" and discovered that robocopy interperets a \" as an escaped quote. The program works great and functions exactly how I would expect if I simply remove the trailing backslash.

I found this functionality to be very strange considering I have never experienced the problem with any other microsoft command line utilities. Not only that but a quote isn't even a valid character in a folder or file so it isn't possible that the path would contain them.

Issue with a remote report server

Hey guys,

I have three servers, assume ServerA,ServerB, and ServerC. ServerA is where the database(datasource) is located. ServerB is used as a report server and Server C is my local machine where I design reports. So obviously I deployed the reports from my local machine(server C) to the remote report server(server B). Deployement was successful but I got the following error when I run the report from the report server. You might think that the report is not pointing to the right data source, I checked that several times and it is pointing to the right data source.

An error has occurred during report processing. (rsProcessingAborted)

Cannot create a connection to data source 'dsCallB'. (rsErrorOpeningConnection)

For more information about this error navigate to the report server on the local server machine, or enable remote errors

However, I also observed that when I deployed the same reports on the local machine report server(server C), it run with out any problem. So it seems that the problem is related with the remote server(server B) not the data source.

Does anybody has a solution for this.

Thx

Normally it is one of two things when I encounter this message:

1) The report is not connected to the datasource on ServerB (as you mentioned).

2) The DataSource Connection String on ServerB has a misspelling somewhere. (If ServerA is Case Sensitive Collation, check upper/lower case).

If not those, make sure ServerB can connect to the database on ServerA through some other means (ODBC, etc). Good luck.

|||

Well, the quick and easy solution is to change the configuration for the connection already deployed to ServerB. Since it's working from your local PC/ServerC, so just use your ID and password to config the connection on ServerB. But if ServerB is really a production server, it's better to create a separate logon, either a Windows account or a SQL account with the access to ServerA. Make sure to set the password to never expire. Then use this account to config the connection on ServerB. HTH.

|||I don't think I need to config the connection on ServerB, because, there are other several reports deployed and run with out any problem. FYI the existing reports was actually deployed by some body else. This problem happened for the new reports that I developed. It is very weired situation.|||

The problem has to be with the connection from ServerB to the data source, otherwise you wouldn't be able to run it from your local machine.

Are you using a shared data source?

Go to the properties of the report in Report Manager, and select 'Data Sources' from the left. Select 'A custom data source' and verify Microsoft SQL Server is listed in your 'Connection type'. The 'Connection string' should look like this: Data Source=ServerA;Initial Catalog=DB_Name. Select 'Credentials stored securely in the report server' and type your username (domain\user) and your password. Then check the box 'Use as Windows credentials when connecting to the data source' and hit Apply at the bottom.

If you do this from ServerB (RS Server), then you should get an informative error message if/when you get the error. Do you have access to log directly into ServerB?

Jarret

|||

Hey Jarret,

First, I really appreciate your follow up to assist me in solving this issue.

Actually, I am using a shared data source to generate the reports. As a matter of fact, the existing reports also uses a shared data source and run with out any problem. I also tried what you said up here, however, I got the same error message. Anyhow, I just wanna cross check one thing with you. If I select A custom data source, which server's username and password do I have to apply, ServerA(datasource), ServerB(report server), or ServerC(Local machine)?

Thx.

|||

I really don't mind. Actually, I'm sorry that I haven't been able to get you a solution yet.

If you select 'A custom data source', then the connection string will have to point to your database server (ServerA) and you'll have to supply either a SQL account or a windows account (domain\user) that has permission to read the data.

If this still doesn't work, I would start a new report without using the shared data source and just store the connection information into the report itself.

Jarret

|||

The problem maybe because the shared datasource you are using may be in a different folder on your report manager whereas you would have specified a different targetdatasourcefolder (Report project properties) for your report. So, it is trying to create that data source at runtime and not able to do it because you dont have enough permissions.

Shyam

|||

I'm very disappointed with this annoying error message. I experienced the same kind of problem in some other project and I was able to solve it with no time. One thing which makes me angry is that the existing shared data sources are working fine, however the data source that I created doesn't work. I wasted a lot of time on this. I don't know what to do next. May be go for a vacation LOL.

Anyhow I will let you know if I am able to find something new. Please keep on posting me if you have any idea.

Thx.

|||

Have you read my post and any progress?

Shyam

|||

Hi Shyam,

I checked that too. On my project properties, the TargetDataSourceFolder is 'Data Sources'. On the report manager, the data sources are created in the same folder name, i.e. 'Data Sources'. I couldn't see any problem.

I even created a new data source (which is not a shared data source) on the report server level and pointed it to my report, but still getting the same error message

|||

Maybe the following links will be of some help to you:

http://msdn2.microsoft.com/en-us/library/ms365166.aspx

http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=288600&SiteID=17

Shyam

Issue using the JDBC driver to connect to a remote server

Hi All,

We have a customer in Japan who's using our tool to connect to their SQL Server. Our application runs on a Linux machine and uses the MSSQL JDBC driver for connectivity.

We're getting errors back that are not-decipherable because, I presume, they're in Japanese, and then our logs don't log them in Unicode, so they get flattened out as question marks. The errors come out looking something like this:

Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]? 'MSSQL'

?

SQL State: HY000

SQL Code: 4060

[Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]? 'sa' ?

SQL State: 28000

SQL Code: 18456

[Microsoft][SQLServer 2000 Driver for JDBC]An error occured while attempting to

log onto the database.

SQL State: 08001

SQL Code: 0

I think we could troubleshoot the problem if we could only read the errors coming back. I know in the ODBC world there are options like "Change the language of SQL Server system messages" and "Perform transaltion for character data" in the ODBC control panel. I couldn't find any similar options available in the JDBC driver however. Do they exist?

Even if they don't, just scanning the messages makes it pretty clear that there's an authentication/permissions issue at work here. We have another app using the same credentials that works fine though - so if there are any known issues about using JDBC to authenticate in a multi-lingual environment, that would be helpful too. Our tcpdumps seem to suggest that the JDBC driver is using multi-byte strings when communicating with the server, however the "working" application is using single-byte encoding. Could that be why we're not authenticating? How is that controlled? Note that at this point I'm just talking about authentication, not the codepages/encodings of the data in the database - right now I'm just worried about logging in.

Thanks!
Tom

I do not think app using single byte encoding should make any difference when logging in. You really should get the error string in English to understand what is going on.

|||

Error 4060 is "Cannot open database "..." requested by the login. The login failed.", 18456 is "Login failed for user ...".

I am moving to the "SQL Server Security" forum for further follow-up.

Issue connecting to a remote SQL Server 2000

I get the following error message in quotes. I have a web application written in ASP.NET 2.0 through which I am trying to connect to a remote SQL Server 2000. My operating system is Windows Vista .

I looked at many different sites to find a solution for this but without any luck. I am sure someone would have seen this issue here and I expect them to shed some light.

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server).

The connection string is located in my web.config file which looks like

<add name="AMSConn" connectionString="Data Source=xxxx;
Initial Catalog=testdb;User ID=sa;Password=xxxx" providerName="System.Data.SqlClient" />

I want to get this issue resolved and I need you guys help.

Please let me know if you need any more information.

Thanks

Bharat

Hello my friend,

First try to access the database from the actual database server using the username and password within your connection string. If this fails, you are using the wrong username and password.

If this works, then it is a remote issue. Try and ping the database server from the computer running your application and see if you get a response or request timed out.

Kind regards

Scotty

|||

Hi Scotty,

First, thanks for the reply. I already tried your first suggestion. It's working fine in the database server with the same credentials. So, not a problem with username and password.

But, when I tried Pinging the server as you indicated I am getting the request timed out error. I don't know what is the reason. I don't have any problems logging into the machine using remote desktop.

Any more suggestions.

Thanks

Bharat

|||

hi Scotty,I have also same problem.

I can ping to remote sql server and can connect with sql query analyser with same credentials.

But can't connect from my aspx application.

isqlw connection problems

I'm trying to connect to remote sql servers. I can connect to and work with
the servers in EM. But, when I click on "Tools|SQL Query Analyzer", it times
out trying to connect with the error messages:
[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not
exist or
access denied.
Followed by:
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen(Conn
ect())
Why can't I connect with Query Analyzer, but I can with Enterprise Manager?
How do I resolve this?
I have no problems using Query Analyzer and connecting to my local sql
servers.
I have no problems using Query Analyzer to connect to local and remote sql
servers on my other computer, just with this one computer.
Thanks,
Wayne SheffieldFYI: I did check out http://support.microsoft.com/?kbid=328306, but nothing
there helped out.
Thanks,
Wayne
"Wayne Sheffield" <wayne_sheffield@.hotmail.com> wrote in message
news:OB2MF3caEHA.2812@.TK2MSFTNGP11.phx.gbl...
> I'm trying to connect to remote sql servers. I can connect to and work
with
> the servers in EM. But, when I click on "Tools|SQL Query Analyzer", it
times
> out trying to connect with the error messages:
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does n
ot exist or
> access denied.
> Followed by:
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen(Co
nnect())
> Why can't I connect with Query Analyzer, but I can with Enterprise
Manager?
> How do I resolve this?
> I have no problems using Query Analyzer and connecting to my local sql
> servers.
> I have no problems using Query Analyzer to connect to local and remote sql
> servers on my other computer, just with this one computer.
> Thanks,
> Wayne Sheffield
>|||Are you using the same account as EM uses?
Andrew J. Kelly SQL MVP
"Wayne Sheffield" <wayne_sheffield@.hotmail.com> wrote in message
news:OB2MF3caEHA.2812@.TK2MSFTNGP11.phx.gbl...
> I'm trying to connect to remote sql servers. I can connect to and work
with
> the servers in EM. But, when I click on "Tools|SQL Query Analyzer", it
times
> out trying to connect with the error messages:
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does n
ot exist or
> access denied.
> Followed by:
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen(Co
nnect())
> Why can't I connect with Query Analyzer, but I can with Enterprise
Manager?
> How do I resolve this?
> I have no problems using Query Analyzer and connecting to my local sql
> servers.
> I have no problems using Query Analyzer to connect to local and remote sql
> servers on my other computer, just with this one computer.
> Thanks,
> Wayne Sheffield
>|||Yes. When you open QA this way, it uses the EM account settings.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uiF7z8daEHA.2840@.TK2MSFTNGP11.phx.gbl...
> Are you using the same account as EM uses?
> --
> Andrew J. Kelly SQL MVP
>
> "Wayne Sheffield" <wayne_sheffield@.hotmail.com> wrote in message
> news:OB2MF3caEHA.2812@.TK2MSFTNGP11.phx.gbl...
> with
> times
or[vbcol=seagreen]
> Manager?
sql[vbcol=seagreen]
>

isqlw connection problems

I'm trying to connect to remote sql servers. I can connect to and work with
the servers in EM. But, when I click on "Tools|SQL Query Analyzer", it times
out trying to connect with the error messages:
[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or
access denied.
Followed by:
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen(Connect())
Why can't I connect with Query Analyzer, but I can with Enterprise Manager?
How do I resolve this?
I have no problems using Query Analyzer and connecting to my local sql
servers.
I have no problems using Query Analyzer to connect to local and remote sql
servers on my other computer, just with this one computer.
Thanks,
Wayne Sheffield
FYI: I did check out http://support.microsoft.com/?kbid=328306, but nothing
there helped out.
Thanks,
Wayne
"Wayne Sheffield" <wayne_sheffield@.hotmail.com> wrote in message
news:OB2MF3caEHA.2812@.TK2MSFTNGP11.phx.gbl...
> I'm trying to connect to remote sql servers. I can connect to and work
with
> the servers in EM. But, when I click on "Tools|SQL Query Analyzer", it
times
> out trying to connect with the error messages:
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or
> access denied.
> Followed by:
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen(Connect())
> Why can't I connect with Query Analyzer, but I can with Enterprise
Manager?
> How do I resolve this?
> I have no problems using Query Analyzer and connecting to my local sql
> servers.
> I have no problems using Query Analyzer to connect to local and remote sql
> servers on my other computer, just with this one computer.
> Thanks,
> Wayne Sheffield
>
|||Are you using the same account as EM uses?
Andrew J. Kelly SQL MVP
"Wayne Sheffield" <wayne_sheffield@.hotmail.com> wrote in message
news:OB2MF3caEHA.2812@.TK2MSFTNGP11.phx.gbl...
> I'm trying to connect to remote sql servers. I can connect to and work
with
> the servers in EM. But, when I click on "Tools|SQL Query Analyzer", it
times
> out trying to connect with the error messages:
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or
> access denied.
> Followed by:
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen(Connect())
> Why can't I connect with Query Analyzer, but I can with Enterprise
Manager?
> How do I resolve this?
> I have no problems using Query Analyzer and connecting to my local sql
> servers.
> I have no problems using Query Analyzer to connect to local and remote sql
> servers on my other computer, just with this one computer.
> Thanks,
> Wayne Sheffield
>
|||Yes. When you open QA this way, it uses the EM account settings.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uiF7z8daEHA.2840@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> Are you using the same account as EM uses?
> --
> Andrew J. Kelly SQL MVP
>
> "Wayne Sheffield" <wayne_sheffield@.hotmail.com> wrote in message
> news:OB2MF3caEHA.2812@.TK2MSFTNGP11.phx.gbl...
> with
> times
or[vbcol=seagreen]
> Manager?
sql
>