connect from ASP app: solve

Showing posts with label solve. Show all posts

Wednesday, March 7, 2012

Cant create new database -> no disk space (os error 112)

Hello everybody

Had some problems yesterday with a full transaction log. Was able to solve it by following microsofts knowledge base article n 272318.

However, today I'm trying to create a new DB using the enterprise manager and get this error:

d:\databases\HERCULES_1_Data.MDF: Operating system error 112(Es steht nicht genug Speicherplatz auf dem Datentrger zur Verfgung.) encountered.

The free disk space is > 60GB. That's by far enough... I only need 1-2 GB.

Any ideas what's wrong here?

Thanks anyone in advance for some guideance.

RenaudAre you sure both the log and the data files were being created on D? How much space is left on your C drive?|||Yes, both files on D:\ but not created at all of course, because of the insufficient disk space error message.

However, on C:\ there's 1.6 GB left. But IMO this shouldn't matter and not be related to the error message, right?|||How are you trying to create this database? By a script, or by Enterprise Manager?|||I'm using the enterprise manager|||How big is your model database? It should be about 1 or 2 MB in size.|||According to the database properties, general tab, the model database is 1.25MB in size with 0.27MB of free space.|||huh... exec xp_fixeddrives tells me:

On C, 1395MB free space
On D, 1MB free space

How can I fix it?|||... and EXEC xp_cmdshell 'dir d:\' gives me back:

1.298.432 Bytes free

Opening a command line (DOS box) and entering "dir d:\" gives me back:

64.280.199.168 Bytes free

?!?!?|||FOUND IT!! There were disk quotas activated on our SBS 2003 Server. Didn't realize that before. Seems to be a default setting when creating new user accounts on Windows. Our SQL Server runs under its own windows account. Wasn't aware of this, now I know better!

Thanks a lot for your help.

Renaud|||Thank you for reporting back the fix to a problem the rest of us may very well encounter.

Sunday, February 19, 2012

Cant connect to SQL Server 2000

I can't connect my jsp file to SQL Server 2000. Below is my code and the error result that i got.. Please help me to solve it..I have installed the driver and write them in the classpath..

<%@.page import="java.sql.*"%>
<%
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
try {
// Open Database connection
Connection con = DriverManager.getConnection("jdbc:microsoft:sqlserver://sansan:1433;databasename= coffeepassion","sa","sa");

// Query Database (all queries use the same connection)
String sql = "SELECT EmpID, EmpPassword FROM EmpLogin";
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("sql");

// Print header and each record returned
out.println("List of items in the database:");
while(rs.next())
out.println(rs.getString("EmpID") + " " +rs.getString("EmpPassword"));
// Finished using the database instances
rs.close();
stmt.close();
con.close();
}

catch (Exception e) {
out.println(e.toString()); // Error message to display
}
%>

Error Result:

java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Cannot open database requested in login ' coffeepassion'. Login fails.I don't have a lot of experience with jdbc, but here goes nothing:

1. Should there be a space in between "database=" and "coffeepassion"?
2. Can you make a connection from the web server to the database server using a .udl file (make a file, call it anything.udl, start it and use another driver type to connect)?\
3. Are you sure you have the right password?

One other thought; please consider using a different account to connect to SQL server. Once you've started down the development path using the sa account, it gets very difficult to make the decision to change to using a different account. Using sa leaves you exposed to all sorts of mischief.

Regards,

hmscott

I can't connect my jsp file to SQL Server 2000. Below is my code and the error result that i got.. Please help me to solve it..I have installed the driver and write them in the classpath..

<%@.page import="java.sql.*"%>
<%
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
try {
// Open Database connection
Connection con = DriverManager.getConnection("jdbc:microsoft:sqlserver://sansan:1433;databasename= coffeepassion","sa","sa");

// Query Database (all queries use the same connection)
String sql = "SELECT EmpID, EmpPassword FROM EmpLogin";
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("sql");

// Print header and each record returned
out.println("List of items in the database:");
while(rs.next())
out.println(rs.getString("EmpID") + " " +rs.getString("EmpPassword"));
// Finished using the database instances
rs.close();
stmt.close();
con.close();
}

catch (Exception e) {
out.println(e.toString()); // Error message to display
}
%>

Error Result:

java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Cannot open database requested in login ' coffeepassion'. Login fails.|||dear hmscott,

i've remove the space in the 'databasename= coffeepassion'..and get another error:

java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Error establishing socket.

Sunday, February 12, 2012

Can't connect to Express Edition

I have a huge connectivity issue that I can't seem to solve.

We're using SQL Express Edition as a local data store that is participating as a subscriber to SQL Server Enterprise Edition. Perfectly valid and supported.

Now the issue. I have the Express Edition on one machine connected just fine, but the other one absolutely refuses. Here is what I did for the one that connected correctly:

1. Enable remote connections for the Express Edition instance and restart the instance

2. Added the SQL Agent service account on the server to the SQLServer2005MSSQLUser$machine$SQLEXPRESS group on the local machine (yes, I want it to have that level of authority)

3. Connected to the machine with Express Edition to the network via VPN.

4. On the server, opened Management Studio and connected to the Express Edition on the machine to verify connectivity

Everything works.

On the machine that refuses, I've duplicated the steps above and it absolutely refuses to connect. I have verified that remote connections are enabled. I have tried connecting via both name as well as IP, just to rule out DNS resolution - neither works. I don't have any security issues. I can go all the way to mapping the c$ share on the machine with the Express Edition directly from the desktop of the server. I can browse for SQL Servers on the network and the machine shows up in the list, so I know that it is being recognized as having an instance of SQL Server. I can't find a single, solitary reason why it should throw a login failure. The message that I'm getting is as follows:

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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (.Net SqlClient Data Provider)

Does anyone have any ideas?

Yes, before anyone asks, the SQL Browser service is running.|||

Hi Michael,

Have you configured firewall exceptions on the machine where you're having the problem? You need to make excepitons for both SQL Server and SQL Browser.

Mike

|||Yes, we have firewall exceptions configured. I even went to the step of shutting down the firewall completely to verify that wasn't a factor in the connectivity issues.|||

Just to verify...

Can you connect to SQL Express from the local computer?|||

1. Yes, I can connect to Express from the local computer

2. Yes, I have tried a remote connection from another machine and get the same error message

3. Yes, the name is spelled correct including the backslash.

More info from the troubleshooting checklist:

Client: Windows XP Pro SP2 with hotfixes and SQL Server Express Edition

1. Connection string for the local app: I'll add to this when I can get it pulled from our custom application.

Connection string for the remote app is SQL Server Management Studio.

2. The local client connects without any problems. The remote client (Management Studio) throws the following error message:

3. The client application in #2 is local to the SQL Server Express instance. The connection from SQL Server Management Studio is remote.

4. Yes, I can ping - the client from the server and the server from the client - both directions using both IP address as well as name

5. I can telnet from the client to the server as well as from the server to the client. I can map an admin share on the server from the client and map an admin share on client from the server.

6. The database provider for the local application is ADO.NET 2.0 inside a custom application developed in C# with Visual Studio 2005. Remotely, it is Management Studio.

7. All machines are in the same domain. I log into the console on all machines as a local administrator, builtin admins is still a member of the sysadmin group in SQL Server, so I do not have any security (authentication) issues in the way.

8. Shared Memory, Named Pipes, and TCP IP are all enabled. Remote connections are enabled using TCP IP only.

9. There are no server aliases configured

10. No, encryption is not forced. Communication is unencrypted at this point.

Server side:

1. SQL Server 2005 9.00.1399.06

2. Enterprise Edition

3. Shared Memory and TCP IP are enabled. Named Pipes and VIA are disabled

4. Yes, the server starts successfully on both the server and the client

5. Yes, the SQL Browser service is started on both the server and the client

6. SQL Server Express Edition is running under Network Service. SQL Server Enterprise Edition is running under a domain account. I am connecting to the Enterprise Edition on the server using my Windows credentials which are a member of domain admins. I have tried both my Windows credentials as well as a SQL Server standard login to connect via Management Studio from the server to the Express Edition on the client and neither works, giving the same error message specified above.

7. Yes firewall exceptions have been made. I've even taken the step of disabling the firewall on both sides to rule it out.

8. Yes, firewall exceptions have been made for both SQL Server as well as SQL Server Browser. TCP 1433 and UDP 1434 have exceptions in the firewall on both sides. And I've even disabled the firewall on the client and routed around it on the server to rule out a firewall issue. (I can connect remotely to SQL Server Express Edition on any machine that is running the Express Edition and also other instances of SQL Server. I can NOT connect remotely to any machine that has JUST SQL Server Express Edition installed on it.)

Platform:

Client - Windows XP Pro SP2 + hotfixes

Server - Windows 2003 R2 SP1 + hotfixes

Misc.

No certificates

I have been through every blog that I could find. Every KB article, website, and scoured mutliple newsgroups and mailing lists. I have been through every troubleshooting checklist that I could find anywhere. None of them work. I spent 4 solid days working on this connectivity issue. I know I have everything configured correctly for firewall, browser, protocols, security, and I am not misspelling anything. I have been working with SQL Server 2005 since before it hit Beta 1 (I wrote a whitepaper for the Beta 1 Readinesss Kit, reviewed several others, and also worked on the Relational Engine labs that Microsoft used during the Technical Airlift and several other internal/external launches.) I know how to troubleshoot an issue and I'm going to do everything possible and rule out everything I possibly can before I ever post asking for help. I am also a moderator on these forums for SQL Server for a reason.

|||

Hi Michael,

It seems like you're doing everything right, I'm a bit baffled. I have only one thought...

8. Yes, firewall exceptions have been made for both SQL Server as well as SQL Server Browser. TCP 1433 and UDP 1434 have exceptions in the firewall on both sides. And I've even disabled the firewall on the client and routed around it on the server to rule out a firewall issue. (I can connect remotely to SQL Server Express Edition on any machine that is running the Express Edition and also other instances of SQL Server. I can NOT connect remotely to any machine that has JUST SQL Server Express Edition installed on it.)

You mentioned that you have created an exception for 1433, but SQL Express uses dynamic TCP by default so it's possible, even likely that it is not using 1433. I generally open an exception for the applicatin itself rather than the port by adding sqlservr.exe to the list, you might want to try that.

Lest you think I didn't read your whole post, I did notice that you completely turned off the firewall at one point, which should have overcome any issue related to dynamic ports, but like I said, it seems like you're doing everything right. I'm grasping at straws here. I'll forward this thread onto a few other folks tomorrow to see if they have any better ideas.

Mike

|||Well, I'm even more baffled at this point. I rebooted this laptop for the umpteenth time and now it connects without any problems at all. I still have one more to work on, but have to wait until he connects up to our VPN again.|||One step forward and one step back. I have one of the machines that all of a sudden decided to connect. I still have another machine that refuses to connect. All of the information previously posted in this thread applies to the machine that still refuses to connect.|||

So, it gets even weirder. To make this clear, let me go back to ground zero and give you the entire configuration that I'm dealing with.

Server - SQL Server 2005 Enterprise Edition

The server is a publisher and the SQL Server Agent is running under a domain account named sqlservice. For example purposes, my domain account is mhotek and is configured as a domain admin. I have configured a merge publication on the server and configured the security for the snapshot and merge agents to impersonate the SQL Server Agent Account (sqlservice). (Yes, I want it set this way very specifically at this point to elminate moving parts.)

Client - SQL Server Express Edition (doesn't matter if it is Gold or SP1 same thing happens)

On the client I have turned on remote connections and restarted the service. I have started the SQL Browser service. I have added domain\sqlservice to the SQLServer2005MSSQLUser$client$SQLExpress group.

Now the innteresting observations:

1. I logon to the Server with my credentials and User1 logs on to Client. I open SSMS on the server and can NOT connect to client\SQLEXPRESS and get the error message of:

TITLE: Connect to Server

Cannot connect to client\SQLEXPRESS.

ADDITIONAL INFORMATION:

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=-1&LinkId=20476

BUTTONS:

I also can NOT create a subscription to this machine, because of the same error message above. i.e. it can not find it.

2. I log User1 off the client and log on using my credentials (mhotek). All of a sudden, I can use SSMS on the server to connect to client\SQLExpress. I can create a subscription. The merge agent (running under sqlservice) can push the snapshot and replicate any changes.

3. I log mhotek off the client and log on User1. All of a sudden, I can not connect to client\SQLExpress via SSMS from the server and the replication engine can not connect.

In short, if I log on to the client machine that has SQL Express on it, all connectivity and functions work. If anyone else logs on to the client machine with SQL Express on it, no one can connect to it remotely via any mechanism. The weirdest part of all of this is that the security credentials for replication are NOT my credentials, but are instead the credentials of sqlservice. However, replication does not work unless I am logged on to the client with my credentials (mhotek).

The client is Windows XP Pro SP2 and the server is Windows 2003 Enterprise with all service packs and hot fixes applied. Client is SQL Server 2005 Express Edition Gold (1399) and the server is SQL Server 2005 Enterprise Edition Gold (1399). (Yes, I've tried this with SP1 on both sides and get exactly the same results.) This is NOT a user instance of SQL Express Edition.

Any ideas, because I'm completely baffled and can't even dream up what might be causing this.

|||We can close this one out. It turned out to be two things halting the SQL Server connections. The first was an incorrect WINS scope. The second issue was that DNS registration was not occuring.