Database Access from Console application

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Database Access from Console application

Author

Topic

hanney
Starting Member

5 Posts

Posted - 2006-05-29 : 10:38:26

Hi

I'm having big problems with a database access since I transferred to a new server.

I moved a database, web application and console applications from one server to a brand new "out of the box" server and now my console apps don't work anymore. Note everything was working fine before the move.Previously I had my console apps on one server and the database on another. Since the move the console apps only work
for SA user but obviously I don't want to use that.

I'm using sql server authentication.

After I moved the database by backing it up and restoring it on the new server. I created a new user on the new server and gave dbo permissions on the database.

By the way my web application which uses the same user name and password as in console applications is working. I also have dts packages running using dtsexec accessing the database with the same user name and password and they work fine.

MDAC 2.8 SP2 on windows server 2003 spi

C:\Program Files\Microsoft SQL Server\80\Tools\Binn>ODBCPING.EXE -S xxx.xxx.xxx.xxx
-U myusername -P mypassword

CONNECTED TO SQL SERVER

ODBC SQL Server Driver Version: 03.86.1830

SQL Server Version: Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

If the connection is available then why is the adapter.fill method failing?

I tried it using an sql statement and that doesn't work either.

The problem isn't database specific as I did a test .bat on Northwind sample database and got same 'general network error'

Here's the error:

apps\Exports>exporter.bat

Unhandled Exception: System.Data.SqlClient.SqlException: General network error.
Check your network documentation.
at System.Data.SqlClient.ConnectionPool.CreateConnection()
at System.Data.SqlClient.ConnectionPool.UserCreateRequest()
at System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransactio
n)
at System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConn
ectionString options, Boolean& isInTransaction)
at System.Data.SqlClient.SqlConnection.Open()
at System.Data.Common.DbDataAdapter.QuietOpen(IDbConnection connection, Conne
ctionState& originalState)
at System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32 startR
ecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior be
havior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord,
Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)

at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable)

//Testing using SELECT statement as command text

D\Test>Test.bat

D:\Test>TestDBAccess.exe "server=xxx.xxx.xxx.xxx;uid=xxxx;pwd=xxx;
database=Northwind;"

Unhandled Exception: System.Data.SqlClient.SqlException: General network error.
Check your network documentation.
at System.Data.SqlClient.ConnectionPool.CreateConnection()
at System.Data.SqlClient.ConnectionPool.UserCreateRequest()
at System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransactio
n)
at System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConn
ectionString options, Boolean& isInTransaction)
at System.Data.SqlClient.SqlConnection.Open()

D:\Test>

Any ideas/help much appreciated!

nr
SQLTeam MVY

12543 Posts

Posted - 2006-05-29 : 20:55:31

If it works for sa but not for the other login then I suspect it's something due to the permissions. Try using the profiler to see what the sql ststement being executed is then run it in query analyser as the same user and see if it gives an error.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

hanney
Starting Member

5 Posts

Posted - 2006-05-30 : 06:20:24

Hi

Thanks for reply.

I can run the Stored Procedures used in console application from Query analyser when logged in with the username/password that
I'm attempting to use for the console applications.

I think that the problem might lie with the authentication method. I want to use sql server authentication.
Is it the case that the username SA forces sql server authentication whereas other usernames are failing because windows authenication is being triggered?

I saw this related post after I'd sent my first post.

http://forums.databasejournal.com/showthread.php?threadid=35072

I've checked Registered SQL Server Properties in Enterprise Manager -> Using sql server replication

Under Users in Enterprise Manager Database access is 'permit' for my user.

Any other suggestions?

Thanks again

hanney
Starting Member

5 Posts

Posted - 2006-05-31 : 05:14:10

Re: previous post.

I meant to say using sql server authentication NOT sql server replication!

Anyone got any ideas why my console apps work for SA and no other user?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources