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

Consistent Deadlock, need to identify the rogue PAGE lock

I'm having trouble tracking down a deadlock in a large multi-threaded application. I have been able to gather some information, but not quite enough to identify the two queries at fault.

I collected the following by turning on the -T1204 and -T3605 parameters:

Deadlock encountered .... Printing deadlock information
2006-04-13 09:13:48.70 spid3
2006-04-13 09:13:48.70 spid3 Wait-for graph
2006-04-13 09:13:48.70 spid3
2006-04-13 09:13:48.70 spid3 Node:1
2006-04-13 09:13:48.70 spid3 KEY: 10:1993058136:1 (aa0069f30857) CleanCnt:3 Mode: U Flags: 0x0
2006-04-13 09:13:48.70 spid3 Grant List 1::
2006-04-13 09:13:48.71 spid3 Owner:0x1a0d5b80 Mode: U Flg:0x0 Ref:1 Life:00000000 SPID:61 ECID:0
2006-04-13 09:13:48.71 spid3 SPID: 61 ECID: 0 Statement Type: UPDATE Line #: 1
2006-04-13 09:13:48.71 spid3 Input Buf: RPC Event: sp_execute;1
2006-04-13 09:13:48.71 spid3 Requested By:
2006-04-13 09:13:48.71 spid3 ResType:LockOwner Stype:'OR' Mode: U SPID:53 ECID:0 Ec:(0x1B4E54F0) Value:0x1a0d5ae0 Cost:(0/BA0)
2006-04-13 09:13:48.71 spid3
2006-04-13 09:13:48.71 spid3 Node:2
2006-04-13 09:13:48.71 spid3 PAG: 10:4:14898 CleanCnt:2 Mode: U Flags: 0x2
2006-04-13 09:13:48.71 spid3 Grant List 0::
2006-04-13 09:13:48.71 spid3 Owner:0x1a0c6bc0 Mode: U Flg:0x0 Ref:1 Life:00000000 SPID:58 ECID:0
2006-04-13 09:13:48.71 spid3 SPID: 58 ECID: 0 Statement Type: UPDATE Line #: 1
2006-04-13 09:13:48.71 spid3 Input Buf: RPC Event: sp_execute;1
2006-04-13 09:13:48.71 spid3 Requested By:
2006-04-13 09:13:48.71 spid3 ResType:LockOwner Stype:'OR' Mode: IU SPID:61 ECID:0 Ec:(0x1C2B54F0) Value:0x1a0d5440 Cost:(0/B98)
2006-04-13 09:13:48.71 spid3
2006-04-13 09:13:48.71 spid3 Node:3
2006-04-13 09:13:48.71 spid3 PAG: 10:4:14899 CleanCnt:2 Mode: IX Flags: 0x2
2006-04-13 09:13:48.71 spid3 Grant List 1::
2006-04-13 09:13:48.71 spid3 Owner:0x1a0c6e80 Mode: IX Flg:0x0 Ref:2 Life:02000000 SPID:55 ECID:0
2006-04-13 09:13:48.71 spid3 SPID: 55 ECID: 0 Statement Type: UPDATE Line #: 1
2006-04-13 09:13:48.71 spid3 Input Buf: RPC Event: sp_execute;1
2006-04-13 09:13:48.71 spid3 Requested By:
2006-04-13 09:13:48.71 spid3 ResType:LockOwner Stype:'OR' Mode: U SPID:58 ECID:0 Ec:(0x1C0114F0) Value:0x1a0d5a20 Cost:(0/B98)
2006-04-13 09:13:48.71 spid3
2006-04-13 09:13:48.71 spid3 Node:4
2006-04-13 09:13:48.71 spid3 KEY: 10:1993058136:1 (aa0069f30857) CleanCnt:3 Mode: U Flags: 0x0
2006-04-13 09:13:48.71 spid3 Wait List:
2006-04-13 09:13:48.71 spid3 Owner:0x1a0d5ae0 Mode: U Flg:0x0 Ref:1 Life:00000000 SPID:53 ECID:0
2006-04-13 09:13:48.71 spid3 SPID: 53 ECID: 0 Statement Type: UPDATE Line #: 1
2006-04-13 09:13:48.71 spid3 Input Buf: RPC Event: sp_execute;1
2006-04-13 09:13:48.71 spid3 Requested By:
2006-04-13 09:13:48.71 spid3 ResType:LockOwner Stype:'OR' Mode: U SPID:55 ECID:0 Ec:(0x1BFB94F0) Value:0x1a0c6fc0 Cost:(0/BA0)
2006-04-13 09:13:48.71 spid3 Victim Resource Owner:
2006-04-13 09:13:48.71 spid3 ResType:LockOwner Stype:'OR' Mode: U SPID:58 ECID:0 Ec:(0x1C0114F0) Value:0x1a0d5a20 Cost:(0/B98)

I then ran the following queries: -

> select name from sysobjects where id=1993058136;
result: ACCOUNTARRANGEMENTSCHEDULES

> select name from sysindexes where indid=1 and id=1993058136;
result: PK_ACCOUNTARRANGEMENTSCHEDULES

> sp_help PK_ACCOUNTARRANGEMENTSCHEDULES
result: PK_ACCOUNTARRANGEMENTSCHEDULES , dbo , primary key cns , 2005-11-01 13:51:01.650

It looks like there is sql server selected a row lock for the ACCOUNTARRANGEMENTSCHEDULES table (sounds good), but for the UPDATE being done by nodes 2 and 3 a page lock is being used. Is there any way I can get more info on what the statement is or what table it is updating?
I found some more useful info, using the following command: -

dbcc traceon (3604)
dbcc page(10, 4, 14898,2)
dbcc traceoff (3604)

I received the following (amongst other things): -
m_objId = 1993058136

Which would indicate the page lock is on the same table (ACCOUNTARRANGEMENTSCHEDULES).|||

The page that the deadlock occurs on has little to do with what led to the deadlock.

This is the deadlock scenario:

Spid 61 -> owns KEY: 10:1993058136:1 (aa0069f30857)
-> requesting 10:4:14898
Spid 58 -> owns 10:4:14898
-> Requesting 10:4:14899
Spid 55 -> Owns 10:4:14899
-> requesting 10:1993058136:1 (aa0069f30857)

So spid 61 used an index to find a row it was interested in and the needed additional data off page 10:4:14898.

Spid 55 appears to have found a row it intends to update on page 10:4:14899, and now needs a key lock to perform the necessary index maintenance.

Spid 58 appears to be scanning as it is requesting the page with the next ID, which likely just happens to be the next page in the list.

All 3 of these spids are exectuting some kind of prepared statement that does an update, as evidenced by the "sp_execute" statement in the input buffer.

To get to the bottom of this you're going to need to look at the exectution plan of these statements. Use profiler and gather the showplan all, sp:stmtstarting, sp:stmtcompleted, rpc starting and rpc completed events. If the plan shows (as I suspect it will) something like CLUSTERED INDEX SCAN, then you need to think about proper indexes to support these statements.

|||Unfortunately, whenever I use the profiler (even if I only turn on Deadlock and Deadlock Chain events) it seems to change the timings enough that the deadlock is not produced.

I don't know how I can change the indexes. The table has a primary key, which is a composite of two columns. Since a primary key exists, why is a page lock being used? The only thing that I can think that could be causing it, is the fact that an updateable jdbc ResultSet is being used by one of the queries. So it's not strictly an UPDATE, it's a SELECT (that selects only 1 row) then an updateRow.

This deadlock only appeared when introducing sp4. With sp3, no deadlocks occur. Has the lock selection algorithm changed?
|||The lock selection algorithm has not changed. It's possible that you have a different plan now. Even if you don't hit the deadlock the plans themselves can be instructive. If you see Scan's in the plan you want to understand why. Of course a Scan is going to cause each page in an index to be read and locked. The fewer pages that are locked the fewer chances there are of encountering a blocking, or deadlocking scenario.

Considerations when delete records from table.

Hello, I'm developing application which monitors network packets. The monitoring data are saved into table. Monitoring table maintains the data for fixed quantum time,for example during one 1 hour. So, every minute before or after insert new data, I delete the time-expired data. I doubt that the endless delete operation would results in some problems(increasing index,etc..).

Is this mechanism safe to the dbms?

Aren't there round-robin(?) style table?

thats wat OLTP is for.... just keep the update statistics to auto (its default) , if ur using indexes.....also there may be fragmentation issues ...but thats a DBA activity/Db maintainance...

Connetion VS2003 C# Web Service to Sql Server 2005 Express

Hi,

I'm reaching the end of my tether here. I'm writing a Web Service application for my MCAD course in VS2003 which will connect to a SQL Server 2005 Express instance to access data. I can connect to the instance through the management suite, and through Visual Studio 2003. For some reason, when I try and manually connect through the web service, I get a page cannot be displayed error. When debugging, it falls over at the connection code. Here's what I have:

<appSettings>

<add key="ConnectionString" value="Integrated Security=SSPI;Initial Catalog=OfficeSupplies;Data Source=SGODRICH\SQLEXPRESS;"/>

</appSettings>

in web.config, and in my application:

// Create a connection to SQL Server Express

connection = new SqlConnection(ConnectionInfo);

// Upload Customer data to DataTable

dataadapter1 = new SqlDataAdapter("select * from Customers", connection);

dataadapter1.MissingSchemaAction = MissingSchemaAction.AddWithKey;

Customer_Data = new DataTable("Customers");

dataadapter1.Fill(Customer_Data);

The line in red is where the application falls over and I get a page cannot be displayed error. Any ideas why my application can't connect yet I can through both the manager and the IDE? I have enabled TCP, named pipes and shared memory. I have TCP dynamic port of 1053, I can connect using SQLCMD -S.\SQLEXPRESS from the command prompt (I get a 1> when I do this). I also have both services up and running. Anyone have any idea?

If the error happens in the red line, then your connection string is wrong. The error occurs in the constructor of the sqlconnection class not during connection which would be the .Open() method.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de|||Ooops. My apologies. I guess working so close to a problem blinds you to its issues. Its working fine now. Well, for now...

connectivity issues after SP2

Recently I upgraded a prod db server and since then users are experiencing
connectivity issues. The application errors out with :
Description [DBNETLIB] [ConnectionWrite (send()).] General network error.
Check your network documentation.
They try to ingore the error and sometimes work but not always.
No changes on the application side. The server is 64b Windows Server 2003 EE
with SQL 2005 EE 64b. installed. I upgraded both windows and sql to their SP2
..
I do have a test env. and there everithing went smoothly and no problems
have been reported.
No errors in the logs (both windows and sql server). A warning that network
card driver is outdated (Broadcom 1G). No network issues
The server is usually heavy loaded 8-5 and these issues start early
afternoon and once started happening they dont go away until users are done
for the day.
I am afraid of db corruption and all I can think about is to roll off the
service packs (both windows and sql)
Please help
Ellie
Can you show us connection string?
"Ellie" <Ellie@.discussions.microsoft.com> wrote in message
news:6C9C0F3B-C118-4B71-9F40-119D874EE783@.microsoft.com...
> Recently I upgraded a prod db server and since then users are experiencing
> connectivity issues. The application errors out with :
> Description [DBNETLIB] [ConnectionWrite (send()).] General network error.
> Check your network documentation.
> They try to ingore the error and sometimes work but not always.
> No changes on the application side. The server is 64b Windows Server 2003
> EE
> with SQL 2005 EE 64b. installed. I upgraded both windows and sql to their
> SP2
> .
> I do have a test env. and there everithing went smoothly and no problems
> have been reported.
> No errors in the logs (both windows and sql server). A warning that
> network
> card driver is outdated (Broadcom 1G). No network issues
> The server is usually heavy loaded 8-5 and these issues start early
> afternoon and once started happening they dont go away until users are
> done
> for the day.
> I am afraid of db corruption and all I can think about is to roll off the
> service packs (both windows and sql)
> Please help
|||> I am afraid of db corruption
No, I doubt it. You would be seeing different symptoms than connectivity
issues.
Have you considered paying attention to the network card driver issue? And
are you sure this isn't a network issue that happens to have started at or
around the same time you installed the service packs? Maybe there is some
process that is now running in the early afternoon that is hogging bandwidth
and making the network unreliable?

connectivity issues after SP2

Recently I upgraded a prod db server and since then users are experiencing
connectivity issues. The application errors out with :
Description [DBNETLIB] [ConnectionWrite (send()).] General network error.
Check your network documentation.
They try to ingore the error and sometimes work but not always.
No changes on the application side. The server is 64b Windows Server 2003 EE
with SQL 2005 EE 64b. installed. I upgraded both windows and sql to their SP2
.
I do have a test env. and there everithing went smoothly and no problems
have been reported.
No errors in the logs (both windows and sql server). A warning that network
card driver is outdated (Broadcom 1G). No network issues
The server is usually heavy loaded 8-5 and these issues start early
afternoon and once started happening they dont go away until users are done
for the day.
I am afraid of db corruption and all I can think about is to roll off the
service packs (both windows and sql)
Please helpEllie
Can you show us connection string?
"Ellie" <Ellie@.discussions.microsoft.com> wrote in message
news:6C9C0F3B-C118-4B71-9F40-119D874EE783@.microsoft.com...
> Recently I upgraded a prod db server and since then users are experiencing
> connectivity issues. The application errors out with :
> Description [DBNETLIB] [ConnectionWrite (send()).] General network error.
> Check your network documentation.
> They try to ingore the error and sometimes work but not always.
> No changes on the application side. The server is 64b Windows Server 2003
> EE
> with SQL 2005 EE 64b. installed. I upgraded both windows and sql to their
> SP2
> .
> I do have a test env. and there everithing went smoothly and no problems
> have been reported.
> No errors in the logs (both windows and sql server). A warning that
> network
> card driver is outdated (Broadcom 1G). No network issues
> The server is usually heavy loaded 8-5 and these issues start early
> afternoon and once started happening they dont go away until users are
> done
> for the day.
> I am afraid of db corruption and all I can think about is to roll off the
> service packs (both windows and sql)
> Please help|||> I am afraid of db corruption
No, I doubt it. You would be seeing different symptoms than connectivity
issues.
Have you considered paying attention to the network card driver issue? And
are you sure this isn't a network issue that happens to have started at or
around the same time you installed the service packs? Maybe there is some
process that is now running in the early afternoon that is hogging bandwidth
and making the network unreliable?

Connectivity Issue between XP, SQL Server 2000 & SPSS

Hi,
I am trying to build an application with Windows XP, SPSS & SQL
Server 2000. I am using SQL Server built in stored procedure 'xp_cmdshell'
with sa login to run batch file which starts SPSS. However 'xp_cmdshell'
keeps on running & nothing happens.
I tried the same thing on Windows 2003 server. It ran after I installed
.Net frame work 2.0 I did the same thing on Windows XP but it doesn't help.
I wonder what I have to do make it run on XP
Any help would be greatly appreciated.
Regards
IT Dev.Hi
"IT Developer" wrote:
> Hi,
> I am trying to build an application with Windows XP, SPSS & SQL
> Server 2000. I am using SQL Server built in stored procedure 'xp_cmdshell'
> with sa login to run batch file which starts SPSS. However 'xp_cmdshell'
> keeps on running & nothing happens.
> I tried the same thing on Windows 2003 server. It ran after I installed
> .Net frame work 2.0 I did the same thing on Windows XP but it doesn't help.
> I wonder what I have to do make it run on XP
> Any help would be greatly appreciated.
>
> Regards
>
> IT Dev.
>
I am not familiar with SPSS, but if you are trying to start a windows
application that requres user input from xp_cmdshell then you should try a
different method. I assume SPSS is on the SQL Server itself?
John

Connectivity Issue between XP, SQL Server 2000 & SPSS

Hi,
I am trying to build an application with Windows XP, SPSS & SQL
Server 2000. I am using SQL Server built in stored procedure 'xp_cmdshell'
with sa login to run batch file which starts SPSS. However 'xp_cmdshell'
keeps on running & nothing happens.
I tried the same thing on Windows 2003 server. It ran after I installed
..Net frame work 2.0 I did the same thing on Windows XP but it doesn't help.
I wonder what I have to do make it run on XP
Any help would be greatly appreciated.
Regards
IT Dev.
Hi
"IT Developer" wrote:

> Hi,
> I am trying to build an application with Windows XP, SPSS & SQL
> Server 2000. I am using SQL Server built in stored procedure 'xp_cmdshell'
> with sa login to run batch file which starts SPSS. However 'xp_cmdshell'
> keeps on running & nothing happens.
> I tried the same thing on Windows 2003 server. It ran after I installed
> .Net frame work 2.0 I did the same thing on Windows XP but it doesn't help.
> I wonder what I have to do make it run on XP
> Any help would be greatly appreciated.
>
> Regards
>
> IT Dev.
>
I am not familiar with SPSS, but if you are trying to start a windows
application that requres user input from xp_cmdshell then you should try a
different method. I assume SPSS is on the SQL Server itself?
John

ConnectionWrite(WrapperWrite()) Error

When the VB application attempts to connect to our SQL Server 2000 sp3 db, the following error is logged:

[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionWrite (WrapperWrite())

Does anyone know what is causing this? I haven't found any helpful info anywhere.

Thanks,

JulieI think this is just ADOs way of saying it can't find the server, or SQL isn't running - check your ODBC connection?|||Have you looked at this Knowledge Base Article:

FIX: RPC Clients Unable to Login to SQL Server with Windows Authentication (http://support.microsoft.com/default.aspx?sd=msdn&scid=kb;en-us;311111)|||Yes, I have read this article, and determined that this doesn't apply to my situation since the ODBC connection is using TCP/IP Network Library (as opposed to Multiprotocol/RPC) and the server is NOT configured to use "Windows only" authentication.

Kind of odd since this vb app has been running against the same db on the same server for almost 2 years now and this is the first time I've seen this error message.

Connectionstring W/O password

I have an asp.net 1.1 application that has to access a SQL Server DB on
another server. In the past we have simply put UID and PWD in the
connectionstring and everything works well.
We now have a client that insists we not include pwd in the connection
string. Although this is easy to do when the DB is on the same server,
using NT Authority, I don't know how to do it when the db is on another
server.
Someone told me to "set up identical service accounts" but I don't know what
that means. I know it's easy to encrypt a connection string in 2.0 but this
is a 1.1 app.
Can someone tell me how this is best done?
Thanks,
TTina
Are both servers in the same domain?
"Tina" <TinaMSeaburn@.nospamexcite.com> wrote in message
news:OhAc%23u0NIHA.2140@.TK2MSFTNGP03.phx.gbl...
>I have an asp.net 1.1 application that has to access a SQL Server DB on
>another server. In the past we have simply put UID and PWD in the
>connectionstring and everything works well.
> We now have a client that insists we not include pwd in the connection
> string. Although this is easy to do when the DB is on the same server,
> using NT Authority, I don't know how to do it when the db is on another
> server.
> Someone told me to "set up identical service accounts" but I don't know
> what that means. I know it's easy to encrypt a connection string in 2.0
> but this is a 1.1 app.
> Can someone tell me how this is best done?
> Thanks,
> T
>|||I already answered this question below. If you open different topics then we
can not reach to the happy end.
If you are in a domain then you'll not need to create identical accounts on
both servers, it would be enough to create a Login in your SQL Server
Instance for the account you want to log in to your SQL Server and use this
domain account for your ASP app in its conn str.
If those servers are not in the same domain but in the same network then try
my suggestion which is under your previous topic.
Ekrem nsoy
"Tina" <TinaMSeaburn@.nospamexcite.com> wrote in message
news:OhAc%23u0NIHA.2140@.TK2MSFTNGP03.phx.gbl...
>I have an asp.net 1.1 application that has to access a SQL Server DB on
>another server. In the past we have simply put UID and PWD in the
>connectionstring and everything works well.
> We now have a client that insists we not include pwd in the connection
> string. Although this is easy to do when the DB is on the same server,
> using NT Authority, I don't know how to do it when the db is on another
> server.
> Someone told me to "set up identical service accounts" but I don't know
> what that means. I know it's easy to encrypt a connection string in 2.0
> but this is a 1.1 app.
> Can someone tell me how this is best done?
> Thanks,
> T
>|||no. they are in different workgroups. Why do you ask? I could probably put
them in a domain system and in the same domain.
T
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uiGWf60NIHA.6060@.TK2MSFTNGP05.phx.gbl...
> Tina
> Are both servers in the same domain?
> "Tina" <TinaMSeaburn@.nospamexcite.com> wrote in message
> news:OhAc%23u0NIHA.2140@.TK2MSFTNGP03.phx.gbl...
>|||If they are , this login should exist in Active Directory and to be seen
from both machine
You can create a login let me say ASP_USER (on both machine) to access
both machines.
"Tina" <TinaMSeaburn@.nospamexcite.com> wrote in message
news:uTzaEX1NIHA.4832@.TK2MSFTNGP04.phx.gbl...
> no. they are in different workgroups. Why do you ask? I could probably
> put them in a domain system and in the same domain.
> T
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uiGWf60NIHA.6060@.TK2MSFTNGP05.phx.gbl...
>|||They don't use active directory
T
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uoYZ8b1NIHA.3852@.TK2MSFTNGP06.phx.gbl...
> If they are , this login should exist in Active Directory and to be
> seen from both machine
> You can create a login let me say ASP_USER (on both machine) to access
> both machines.
> "Tina" <TinaMSeaburn@.nospamexcite.com> wrote in message
> news:uTzaEX1NIHA.4832@.TK2MSFTNGP04.phx.gbl...
>

Connectionstring W/O password

I have an asp.net 1.1 application that has to access a SQL Server DB on
another server. In the past we have simply put UID and PWD in the
connectionstring and everything works well.
We now have a client that insists we not include pwd in the connection
string. Although this is easy to do when the DB is on the same server,
using NT Authority, I don't know how to do it when the db is on another
server.
Someone told me to "set up identical service accounts" but I don't know what
that means. I know it's easy to encrypt a connection string in 2.0 but this
is a 1.1 app.
Can someone tell me how this is best done?
Thanks,
TTina
Are both servers in the same domain?
"Tina" <TinaMSeaburn@.nospamexcite.com> wrote in message
news:OhAc%23u0NIHA.2140@.TK2MSFTNGP03.phx.gbl...
>I have an asp.net 1.1 application that has to access a SQL Server DB on
>another server. In the past we have simply put UID and PWD in the
>connectionstring and everything works well.
> We now have a client that insists we not include pwd in the connection
> string. Although this is easy to do when the DB is on the same server,
> using NT Authority, I don't know how to do it when the db is on another
> server.
> Someone told me to "set up identical service accounts" but I don't know
> what that means. I know it's easy to encrypt a connection string in 2.0
> but this is a 1.1 app.
> Can someone tell me how this is best done?
> Thanks,
> T
>|||I already answered this question below. If you open different topics then we
can not reach to the happy end.
If you are in a domain then you'll not need to create identical accounts on
both servers, it would be enough to create a Login in your SQL Server
Instance for the account you want to log in to your SQL Server and use this
domain account for your ASP app in its conn str.
If those servers are not in the same domain but in the same network then try
my suggestion which is under your previous topic.
--
Ekrem Önsoy
"Tina" <TinaMSeaburn@.nospamexcite.com> wrote in message
news:OhAc%23u0NIHA.2140@.TK2MSFTNGP03.phx.gbl...
>I have an asp.net 1.1 application that has to access a SQL Server DB on
>another server. In the past we have simply put UID and PWD in the
>connectionstring and everything works well.
> We now have a client that insists we not include pwd in the connection
> string. Although this is easy to do when the DB is on the same server,
> using NT Authority, I don't know how to do it when the db is on another
> server.
> Someone told me to "set up identical service accounts" but I don't know
> what that means. I know it's easy to encrypt a connection string in 2.0
> but this is a 1.1 app.
> Can someone tell me how this is best done?
> Thanks,
> T
>|||no. they are in different workgroups. Why do you ask? I could probably put
them in a domain system and in the same domain.
T
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uiGWf60NIHA.6060@.TK2MSFTNGP05.phx.gbl...
> Tina
> Are both servers in the same domain?
> "Tina" <TinaMSeaburn@.nospamexcite.com> wrote in message
> news:OhAc%23u0NIHA.2140@.TK2MSFTNGP03.phx.gbl...
>>I have an asp.net 1.1 application that has to access a SQL Server DB on
>>another server. In the past we have simply put UID and PWD in the
>>connectionstring and everything works well.
>> We now have a client that insists we not include pwd in the connection
>> string. Although this is easy to do when the DB is on the same server,
>> using NT Authority, I don't know how to do it when the db is on another
>> server.
>> Someone told me to "set up identical service accounts" but I don't know
>> what that means. I know it's easy to encrypt a connection string in 2.0
>> but this is a 1.1 app.
>> Can someone tell me how this is best done?
>> Thanks,
>> T
>|||If they are , this login should exist in Active Directory and to be seen
from both machine
You can create a login let me say ASP_USER (on both machine) to access
both machines.
"Tina" <TinaMSeaburn@.nospamexcite.com> wrote in message
news:uTzaEX1NIHA.4832@.TK2MSFTNGP04.phx.gbl...
> no. they are in different workgroups. Why do you ask? I could probably
> put them in a domain system and in the same domain.
> T
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uiGWf60NIHA.6060@.TK2MSFTNGP05.phx.gbl...
>> Tina
>> Are both servers in the same domain?
>> "Tina" <TinaMSeaburn@.nospamexcite.com> wrote in message
>> news:OhAc%23u0NIHA.2140@.TK2MSFTNGP03.phx.gbl...
>>I have an asp.net 1.1 application that has to access a SQL Server DB on
>>another server. In the past we have simply put UID and PWD in the
>>connectionstring and everything works well.
>> We now have a client that insists we not include pwd in the connection
>> string. Although this is easy to do when the DB is on the same server,
>> using NT Authority, I don't know how to do it when the db is on another
>> server.
>> Someone told me to "set up identical service accounts" but I don't know
>> what that means. I know it's easy to encrypt a connection string in 2.0
>> but this is a 1.1 app.
>> Can someone tell me how this is best done?
>> Thanks,
>> T
>>
>|||They don't use active directory
T
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uoYZ8b1NIHA.3852@.TK2MSFTNGP06.phx.gbl...
> If they are , this login should exist in Active Directory and to be
> seen from both machine
> You can create a login let me say ASP_USER (on both machine) to access
> both machines.
> "Tina" <TinaMSeaburn@.nospamexcite.com> wrote in message
> news:uTzaEX1NIHA.4832@.TK2MSFTNGP04.phx.gbl...
>> no. they are in different workgroups. Why do you ask? I could probably
>> put them in a domain system and in the same domain.
>> T
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:uiGWf60NIHA.6060@.TK2MSFTNGP05.phx.gbl...
>> Tina
>> Are both servers in the same domain?
>> "Tina" <TinaMSeaburn@.nospamexcite.com> wrote in message
>> news:OhAc%23u0NIHA.2140@.TK2MSFTNGP03.phx.gbl...
>>I have an asp.net 1.1 application that has to access a SQL Server DB on
>>another server. In the past we have simply put UID and PWD in the
>>connectionstring and everything works well.
>> We now have a client that insists we not include pwd in the connection
>> string. Although this is easy to do when the DB is on the same server,
>> using NT Authority, I don't know how to do it when the db is on another
>> server.
>> Someone told me to "set up identical service accounts" but I don't know
>> what that means. I know it's easy to encrypt a connection string in
>> 2.0 but this is a 1.1 app.
>> Can someone tell me how this is best done?
>> Thanks,
>> T
>>
>>
>

Connectionstring W/O password

I have an asp.net 1.1 application that has to access a SQL Server DB on
another server. In the past we have simply put UID and PWD in the
connectionstring and everything works well.
We now have a client that insists we not include pwd in the connection
string. Although this is easy to do when the DB is on the same server,
using NT Authority, I don't know how to do it when the db is on another
server.
Someone told me to "set up identical service accounts" but I don't know what
that means. I know it's easy to encrypt a connection string in 2.0 but this
is a 1.1 app.
Can someone tell me how this is best done?
Thanks,
T
Tina
Are both servers in the same domain?
"Tina" <TinaMSeaburn@.nospamexcite.com> wrote in message
news:OhAc%23u0NIHA.2140@.TK2MSFTNGP03.phx.gbl...
>I have an asp.net 1.1 application that has to access a SQL Server DB on
>another server. In the past we have simply put UID and PWD in the
>connectionstring and everything works well.
> We now have a client that insists we not include pwd in the connection
> string. Although this is easy to do when the DB is on the same server,
> using NT Authority, I don't know how to do it when the db is on another
> server.
> Someone told me to "set up identical service accounts" but I don't know
> what that means. I know it's easy to encrypt a connection string in 2.0
> but this is a 1.1 app.
> Can someone tell me how this is best done?
> Thanks,
> T
>
|||I already answered this question below. If you open different topics then we
can not reach to the happy end.
If you are in a domain then you'll not need to create identical accounts on
both servers, it would be enough to create a Login in your SQL Server
Instance for the account you want to log in to your SQL Server and use this
domain account for your ASP app in its conn str.
If those servers are not in the same domain but in the same network then try
my suggestion which is under your previous topic.
Ekrem nsoy
"Tina" <TinaMSeaburn@.nospamexcite.com> wrote in message
news:OhAc%23u0NIHA.2140@.TK2MSFTNGP03.phx.gbl...
>I have an asp.net 1.1 application that has to access a SQL Server DB on
>another server. In the past we have simply put UID and PWD in the
>connectionstring and everything works well.
> We now have a client that insists we not include pwd in the connection
> string. Although this is easy to do when the DB is on the same server,
> using NT Authority, I don't know how to do it when the db is on another
> server.
> Someone told me to "set up identical service accounts" but I don't know
> what that means. I know it's easy to encrypt a connection string in 2.0
> but this is a 1.1 app.
> Can someone tell me how this is best done?
> Thanks,
> T
>
|||no. they are in different workgroups. Why do you ask? I could probably put
them in a domain system and in the same domain.
T
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uiGWf60NIHA.6060@.TK2MSFTNGP05.phx.gbl...
> Tina
> Are both servers in the same domain?
> "Tina" <TinaMSeaburn@.nospamexcite.com> wrote in message
> news:OhAc%23u0NIHA.2140@.TK2MSFTNGP03.phx.gbl...
>
|||If they are , this login should exist in Active Directory and to be seen
from both machine
You can create a login let me say ASP_USER (on both machine) to access
both machines.
"Tina" <TinaMSeaburn@.nospamexcite.com> wrote in message
news:uTzaEX1NIHA.4832@.TK2MSFTNGP04.phx.gbl...
> no. they are in different workgroups. Why do you ask? I could probably
> put them in a domain system and in the same domain.
> T
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uiGWf60NIHA.6060@.TK2MSFTNGP05.phx.gbl...
>
|||They don't use active directory
T
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uoYZ8b1NIHA.3852@.TK2MSFTNGP06.phx.gbl...
> If they are , this login should exist in Active Directory and to be
> seen from both machine
> You can create a login let me say ASP_USER (on both machine) to access
> both machines.
> "Tina" <TinaMSeaburn@.nospamexcite.com> wrote in message
> news:uTzaEX1NIHA.4832@.TK2MSFTNGP04.phx.gbl...
>

ConnectionString using SQL Server

Hello all,

I tried several connectionstrings in web.config file shown below and got this error "Application Error" (ErrorPage.htm).

<add name="ClubSiteDB" connectionString="Data Source=myClubs;Initial Catalog=Clubs;User Id=xxxx;Password=xxxx;" providerName="System.Data.SqlClient"/>
<add name="ClubSiteDB" connectionString="Driver={SQL Server};Server=localhost;Database=clubs;Uid=xxxx;Pwd=xxxx;" providerName="System.Data.SqlClient"/>

I created SQL Server 2000 database called clubs and ODBC DSN called myClubs at web hosting. It worked at local machine with no problem, but transfer from local machine to web hosting is something I'm not familiar with. Please advice. Thanks.

EagleGolf

check out www.connectionstrings.com|||

Hi!,

U have to specify the server name rather than 'localhost', if u r using trusted connection then it should be:
Database=Test;Server=Machine1;Trusted_Connection=Yes,
If not then specify the Userid and password:
server=Machine1;database=test;uid=xxx;pwd=xxx

Hope this will help
Regards

|||

The server name would be the url connected to the database. Or just the name like .\SQLEXPRESS? I use your method and it didn't work like it should.

|||

Hi,

Are u using SQL Express? If yes, then check this URL:

http://www.eggheadcafe.com/articles/20060719.asp

Hope this will help.

Regards

|||

I finally figured it out. I had the server name wrong and now it is corrected. Thanks for all of your help. Have a nice day.

Connections...

Dear All,
SQL Server 2000, Service Pack 3a
We have a problem with locking where as soon as something deadlocks the
application fails.
There are two solutions that spring to mind, either totally re-writing the
Application or setting the SET LOCK_TIMEOUT command.
Now currently we cannot do the re-write as the developers don't have time (I
was out voted) so we are looking to the Set Lock_Timeout, but here is the
problem.
The Connections used are set by JBOSS and our developers cannot find a way
of setting the SET LOCK_TIMEOUT to the connection through JBOSS.
What I was wondering then was is there a way through SQL, i.e. Whenever a
connection is created it automatically does a SET LOCK_TIMEOUT ?
If not could someone go through how a connection is actually created?, so I
can attempt to put a trigger somewhere.
I would totally agree that this is a 'hack' fix and does not solve the
underlying problem but they want a quick fix :(
JJulie,
changing the Lock timeout won't solve you're problem. The Lock timeout
only defines how long a query wait for a resource until a exlusive lock
is released. By default a query waits indefinetely until the lock is
released.
Deadlocks are different. SQL Server detects if a deadlock occurs and
the kills one process, the deadlock victim. This happens very fast, so
the time the resource is locked would be shorter than your timeout.
You need to catch the error 1205 and the handle it in your application.
I don't think there's a alternative for that.
Markus

Connections Timing Out

I am running an application on MS SQL2000 SP4. I have 5 clients connecting to
the SQL server. Almost every night between 5pm and 6:30pm, I lose the
connection to the server from the clients and I get a message about my
database possibly being compromised. Everything locks up and I must reboot
the client.
Has any one experienced this?
Fini2549
Check to see if there are any scheduled jobs running at that time. Sounds
like someone is running a DBCC CHECKDB and placing the db in single user
mode.
Andrew J. Kelly SQL MVP
"Fini2549" <Fini2549@.discussions.microsoft.com> wrote in message
news:9990B03B-CE13-4CEB-9EF8-E4FFB68E0A10@.microsoft.com...
>I am running an application on MS SQL2000 SP4. I have 5 clients connecting
>to
> the SQL server. Almost every night between 5pm and 6:30pm, I lose the
> connection to the server from the clients and I get a message about my
> database possibly being compromised. Everything locks up and I must reboot
> the client.
> Has any one experienced this?
> Fini2549

Connections Limit?

Hi,

I have an application with a DAL that has an interface with SQL Server.
The application has 400 users that open the web forms.
My question is:
Is there a limit of the parallel connections that can be opened? Or the IIS is managing all the access to the DB? Should I worry about the performance Or it's normal behaviour for ASP.NET applications?

Thanks::The application has 400 users that open the web forms.

No, it does not. It has 25 users concurrently per processor, maximum. Unless you play around with the settings, this is the number of workther threads ASP.NET utilizes.

::Or the IIS is managing all the access to the DB?

It is, but you musth avea programming error, because the number should never go higher than 25 per processor.

You are not, by chance, not closing the connections?sqlsql

connections how many to use

hi,

i have an application for which i m using a connection which stays throughout the life of the application. this is a normal sqloledb connection.

but now i stumbled across datashape which i have to use for this complex query.

now the problem is that i will have to use another connection, as the provider is different.

what shud i do? 2 connections 4 a single user? i will need both the connections as while saving i need the shape connection again
and for browsing will need another.

thnxDoesn't sound like you have a choice.
Shouldn't be a problem unless you have msde or personal edition.|||but then with the number of users growing wouldnt that slow down things.
i also want to make sure performance isnt affected.

unless i solve this query: http://dbforums.com/t715674.html, i will have to use datashape.|||What type of application are we talking about? When using ASP(.NET) or VB(.NET) there is a thing called connection-pooling, and what it basically does is "cache" your connection to the database so each time you create it it doesn't really get created, it just gets re-enabled. This depends on one single thing; that the connectionstring is the same throughout your application. If you have 10 users using one connstring an 1 user using another both connections have to physically be created each time that 1 user has made a pagehit.

So what I'm basically saying is: avoid different connectionstrings if you can.|||well i m using the same connection for all users, m using VB 6 sp5, have defined a connectionstring in a bas module and that same string is used to connect so i can use pooling there.

But the problem is that the application requires me to connect to the d/b using MSDATASHAPE for fetching records, now i need to connect to the d/b to validate certain things on the fly like lookup and search.

can i use the msdatashape connection to use any other provider for executing normal sql queries?|||You might have troublt getting connection pooling to have any affect if the connections are made from the client.
If you have a remote DB access layer then it would - but then you probably wouldn't be holding connections.|||uh you should drop the shape thing and look into sql server analysis services.

with this, you can render an mdx statement in a view-- and you can have it take any shape you want.|||i didnt knw that.
do u knw any place where i can begin that?

also the problem with shape is that i have to then use different recordsets for viewing/editing etc for each child recordset.
thats not good at all is it?

Connections

Hi Folks,
I have a question. I've developed a desktop application. But i can't decide
sql server connection strategy. The application uses by 15-20 Windows client
and it connects to same sql server.
What am i do ?
1- I can open connection start of the program and close when program is shut
down.
2- I can open connection when i need a connection and i can close
connection when i executed sql statements.
Do you have any idea?
Thanks.Hi
In general two-tier applications tend to use a single connection for the
application runtime, web applications tend to use connection pooling and
close the connection. Your application seems to be best suited to the single
connection strategy. See my other reply for some links about these.
John
"Erencan SADIRODLU" wrote:

> Hi Folks,
> I have a question. I've developed a desktop application. But i can't decid
e
> sql server connection strategy. The application uses by 15-20 Windows clie
nt
> and it connects to same sql server.
> What am i do ?
> 1- I can open connection start of the program and close when program is sh
ut
> down.
> 2- I can open connection when i need a connection and i can close
> connection when i executed sql statements.
> Do you have any idea?
> Thanks.
>
>|||John Bell wrote:[vbcol=seagreen]
> Hi
> In general two-tier applications tend to use a single connection for the
> application runtime, web applications tend to use connection pooling and
> close the connection. Your application seems to be best suited to the sing
le
> connection strategy. See my other reply for some links about these.
> John
> "Erencan SADIRODLU" wrote:
>
Thanks John for all answers.