Console apps work for SA but no other user

Hi

My console applications work forSA and no other user. I can run the Stored procedures used in the console application from Query analyser when logged in with username/password that I am attempting to use for console applications. I am using SQL server authenication. User access permissions look ok in Enterprise Manager. Access is permit for my user.

Any suggestions?

Thanks

Permissions in SQL are much more than just access permit. You should also grant EXECUTE permssion for a stored procedure to a user if you want the user to execute the stored procedure; or you can create a role and add the user as member, then grant proper permissions to the role. To understand permissions related concepts in SQL, you can start from here:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_03_8q2b.asp

sqlsql

console apps only work if user is SA

Hi

When I try to use a user other then SA my console apps don't work.

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.

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

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 a text 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!

Hi,

would be cool if you could show us your code.Sometimes people hardcode certain properties (I confess that I did that on my own one time :-) ) which will lead to an error where usally there shouldn′t be an error (especially in console apps where connection properties are passed via arguments which isn′t testable while debugging within VS)

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Hi,

Here's the essentials of the code (without some try catch statements).

Thanks.

KH

.bat file

::arg 0 Connection String
::arg 1 Export Path //just a path where I want the output file to go

Exporter.exe "server=xxx.xxx.xxx.xxx;uid=username;pwd=password;database=DATABASENAME;" "d:\\xxx\\xxx\\ExportOut\\"

Source code

using System;

using System.Data;
using System.Security;
using System.Security.Permissions;
using System.Security.Policy;
using System.Configuration;
using System.Data.SqlClient;
using System.IO;

namespace Exporter
{
class Export
{

private static String ConnectionString;

private static String ExportPath;

private static StreamWriter ExportLog;

[STAThread]
static void Main(string[] args)
{

ConnectionString = args[0];

ExportPath = args[1];

String DateString = System.DateTime.Today.Day.ToString() + "_" + System.DateTime.Today.Month.ToString();

ExportLog = new StreamWriter(ExportPath+"Export_Log_"+DateString+".txt");

DoExport();

ExportLog.Close();

}

private static void DoExport()
{

ExportLog.WriteLine("Beginning export");
GetData());

}

private static void GetData()
{

SqlCommand SelectCommand = new SqlCommand();

SelectCommand.CommandType=(System.Data.CommandType.StoredProcedure);

SelectCommand.CommandText="GetCSVOutput";

SqlConnection Conn = new SqlConnection(ConnectionString);

SelectCommand.Connection=Conn;

SqlDataAdapter ReportAdapter = new SqlDataAdapter();

FaultReportAdapter.SelectCommand=SelectCommand;

DataSet ReportData = new DataSet();

ReportAdapter.Fill(ReportData,"Report");

if (ReportData.Tables["Report"].Rows.Count == 0)
{
Conn.Close();
ExportLog.WriteLine("No reports to export");
return false;

}
else
{
Conn.Close();
return MakeFile(ReportData);

}

}

private static void MakeFile(DataSet FaultReportData)
{

/* Just prints out the results of Stored procedure to file and closes file */

}

|||Hi,

beside that the FaultReportDapater doesn′t exists (but I guess this is just a typo) you can try disabling the connection pool to see if it is based on this with adding the keywords "Pooling=False" to the connecting string.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||

Hi,

Yes that was just a typo. With pooling set to false I still get the same error.

Thanks

KH

|||

What is the call stack of the exception if you disable pooling?

|||

Here's the call stack with pooling set to false.

D:\apps\Export>exporter.bat

D:\content\apps>Exporter.exe "server=xxx.xxx.xxx.xxx;uid=xxxx;pwd=;database=xxxx;pooling=False" "d:\\Content\\xxxx\\ExportOut\\"

Unhandled Exception: System.Data.SqlClient.SqlException: General network error.
Check your network documentation.
at System.Data.SqlClient.SqlInternalConnection.OpenAndLogin()
at System.Data.SqlClient.SqlInternalConnection..ctor(SqlConnection connection
, SqlConnectionString connectionOptions)
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)
at Exporter.Export.GetData() in
\\xxxx\dotnet_dll_production
\xxxx\xxxxx\export.cs:line 536
at Exporter.Export.DoExport() in
\\xxxx\dotnet_dll_production\xxxx\xxxx\export.cs:line 143
at Exporter.Export.Main(String[] args) in \\xxxx\dotnet_dll_produc
tion\xxxx\xxxx\export.cs:line 95

D:\apps\Export>

console application for retrieving a large amount of data

i need to retrieve a large amount of data from the sql server database and make changes to one field and put the data back using a console application. how do i do it?Well, there are lots of ways, probably the easiest being to retrievethe data as a dataset, make the changes, and update the database.
But let's start with this. Why do you need to retrieve a large amountof data to only change one field? Do you mean one field in all or mostof the records, or one field in one record?
If you're doing an update that affects many rows and don't really needto pull the data off the server, you might be able to use a SQL UPDATEstatement that updates the data on the database server, saving thenetwork traffic of pulling it down to the client.
Tell us more about what you want to do and we'll try to help.
Don
|||well, i have to retrieve 3 million records from the sql server database. i need to encrypt one field present in all records and put that encrypted field back into the db.
thanks.|||Okay. Is this a one-time thing or will you be doing it regularly? If aone-time thing then you probably don't care too much about performanceor being inefficient with regards to memory usage. So I would probablypull down the data into a dataset, probably in ranges of data usingsome field that is reasonably well-distributed, make the changes, andthen update the database.
For example, if the data had a last name field, you could do it forlast names that begin with A to E, then F to M, and so on. Or whateverranges make sense. And bring down ONLY the data you need, presumablythe one field with the data to be encrypted, and perhaps the secondfield that is the destination for the encrypted data. Or does theencrypted data go into a different table? Then you'll need to generatethe insert statements or use a second data table in the data set.
This is going to be horribly inefficient, however, so you won't want togo this route if this is anything but a one-time thing. If it'ssomething you'll need to do regularly, I'd try to find a way to do thisentierly on the server. In that case you could write a stored procedurethat uses OLE Automation (the sp_OA* system stored procedures) to dothe encryption. Since that uses COM it's going to have its ownperformance issues, but at least you're not slepping three million rowsof data to the client across the network.
Depending on exactly how you need to do this, there are plenty of other ways to get it done.
Don