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>

Consistency error using DBCC CheckDB

Hi,
when I run DBCC CHECKDB against one of my call databases it tells me there
are two consistency errors and 0 allocation errors. The minimum repair
option is REPAIR_REBUILD.
So I have my db in single user mode and I execute:
dbcc checkdb ('Leigh_Call2', REPAIR_REBUILD) WITH ALL_ERRORMSGS
this returns:
Server: Msg 1505, Level 16, State 1, Line 1
CREATE UNIQUE INDEX terminated because a duplicate key was found for index
ID 1. Most significant primary key is '17847'.
DBCC results for 'Leigh_Call2'.
....
DBCC results for 'CallDataItemEnumeratedStaging'.
There are 201627 rows in 2000 pages for object
'CallDataItemEnumeratedStaging'.
Server: Msg 8936, Level 16, State 1, Line 1
Table error: Object ID 1106102981, index ID 1. B-tree chain linkage
mismatch. (1:21981)->next = (1:21983), but (1:21983)->Prev = (1:22065).
Server: Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 1106102981, index ID 1. Page (1:22065) is missing a
reference from previous page (1:21981). Possible chain linkage problem.
....
DBCC results for 'CallEventStaging'.
Could not repair this error.
Repairing this error requires other errors to be corrected first.
There are 178358 rows in 1487 pages for object 'CallEventStaging'.
CHECKDB found 0 allocation errors and 2 consistency errors in table
'CallEventStaging' (object ID 1106102981).
....
CHECKDB found 0 allocation errors and 2 consistency errors in database
'Leigh_Call2'.
repair_rebuild is the minimum repair level for the errors found by DBCC
CHECKDB (Leigh_Call2 repair_rebuild).
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
The statement has been terminated.
This seems to be the error portion of the results returned.
Each time I run it the name of the table with the problem changes.
Is this pointing to perhaps some physical corruption on the disks?
Info, views and opinions appreciated.
Martin
The repair is trying to rebuild the index - obviously that's failing because
of the duplicate key violation.
My guess as to what's causing dbcc to report these issues are stale reads
from your disk controller and there's an active workload on the database
that's causing page splits in various tables. I bet if you shut everything
down and power it back up then a checkdb will come back clean for the
database. If so, you need to run diagnostics on your IO subsystem and make
sure all the firmware is up to date.
Thanks
Paul Randal
Principal Lead Program Manager
Core Storage Engine, Microsoft SQL Server Team
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
"Martin Selway" <martin.selway@.csdss.com> wrote in message
news:uxahkybOHHA.3900@.TK2MSFTNGP06.phx.gbl...
> Hi,
> when I run DBCC CHECKDB against one of my call databases it tells me there
> are two consistency errors and 0 allocation errors. The minimum repair
> option is REPAIR_REBUILD.
> So I have my db in single user mode and I execute:
> dbcc checkdb ('Leigh_Call2', REPAIR_REBUILD) WITH ALL_ERRORMSGS
> this returns:
> Server: Msg 1505, Level 16, State 1, Line 1
> CREATE UNIQUE INDEX terminated because a duplicate key was found for index
> ID 1. Most significant primary key is '17847'.
> DBCC results for 'Leigh_Call2'.
> ...
> DBCC results for 'CallDataItemEnumeratedStaging'.
> There are 201627 rows in 2000 pages for object
> 'CallDataItemEnumeratedStaging'.
> Server: Msg 8936, Level 16, State 1, Line 1
> Table error: Object ID 1106102981, index ID 1. B-tree chain linkage
> mismatch. (1:21981)->next = (1:21983), but (1:21983)->Prev = (1:22065).
> Server: Msg 8978, Level 16, State 1, Line 1
> Table error: Object ID 1106102981, index ID 1. Page (1:22065) is missing a
> reference from previous page (1:21981). Possible chain linkage problem.
> ...
> DBCC results for 'CallEventStaging'.
> Could not repair this error.
> Repairing this error requires other errors to be corrected first.
> There are 178358 rows in 1487 pages for object 'CallEventStaging'.
> CHECKDB found 0 allocation errors and 2 consistency errors in table
> 'CallEventStaging' (object ID 1106102981).
> ...
> CHECKDB found 0 allocation errors and 2 consistency errors in database
> 'Leigh_Call2'.
> repair_rebuild is the minimum repair level for the errors found by DBCC
> CHECKDB (Leigh_Call2 repair_rebuild).
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> The statement has been terminated.
> This seems to be the error portion of the results returned.
> Each time I run it the name of the table with the problem changes.
> Is this pointing to perhaps some physical corruption on the disks?
> Info, views and opinions appreciated.
> Martin
>
sqlsql

Consistency error using DBCC CheckDB

Hi,
when I run DBCC CHECKDB against one of my call databases it tells me there
are two consistency errors and 0 allocation errors. The minimum repair
option is REPAIR_REBUILD.
So I have my db in single user mode and I execute:
dbcc checkdb ('Leigh_Call2', REPAIR_REBUILD) WITH ALL_ERRORMSGS
this returns:
Server: Msg 1505, Level 16, State 1, Line 1
CREATE UNIQUE INDEX terminated because a duplicate key was found for index
ID 1. Most significant primary key is '17847'.
DBCC results for 'Leigh_Call2'.
...
DBCC results for 'CallDataItemEnumeratedStaging'.
There are 201627 rows in 2000 pages for object
'CallDataItemEnumeratedStaging'.
Server: Msg 8936, Level 16, State 1, Line 1
Table error: Object ID 1106102981, index ID 1. B-tree chain linkage
mismatch. (1:21981)->next = (1:21983), but (1:21983)->Prev = (1:22065).
Server: Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 1106102981, index ID 1. Page (1:22065) is missing a
reference from previous page (1:21981). Possible chain linkage problem.
...
DBCC results for 'CallEventStaging'.
Could not repair this error.
Repairing this error requires other errors to be corrected first.
There are 178358 rows in 1487 pages for object 'CallEventStaging'.
CHECKDB found 0 allocation errors and 2 consistency errors in table
'CallEventStaging' (object ID 1106102981).
...
CHECKDB found 0 allocation errors and 2 consistency errors in database
'Leigh_Call2'.
repair_rebuild is the minimum repair level for the errors found by DBCC
CHECKDB (Leigh_Call2 repair_rebuild).
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
The statement has been terminated.
This seems to be the error portion of the results returned.
Each time I run it the name of the table with the problem changes.
Is this pointing to perhaps some physical corruption on the disks?
Info, views and opinions appreciated.
MartinThe repair is trying to rebuild the index - obviously that's failing because
of the duplicate key violation.
My guess as to what's causing dbcc to report these issues are stale reads
from your disk controller and there's an active workload on the database
that's causing page splits in various tables. I bet if you shut everything
down and power it back up then a checkdb will come back clean for the
database. If so, you need to run diagnostics on your IO subsystem and make
sure all the firmware is up to date.
Thanks
--
Paul Randal
Principal Lead Program Manager
Core Storage Engine, Microsoft SQL Server Team
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
"Martin Selway" <martin.selway@.csdss.com> wrote in message
news:uxahkybOHHA.3900@.TK2MSFTNGP06.phx.gbl...
> Hi,
> when I run DBCC CHECKDB against one of my call databases it tells me there
> are two consistency errors and 0 allocation errors. The minimum repair
> option is REPAIR_REBUILD.
> So I have my db in single user mode and I execute:
> dbcc checkdb ('Leigh_Call2', REPAIR_REBUILD) WITH ALL_ERRORMSGS
> this returns:
> Server: Msg 1505, Level 16, State 1, Line 1
> CREATE UNIQUE INDEX terminated because a duplicate key was found for index
> ID 1. Most significant primary key is '17847'.
> DBCC results for 'Leigh_Call2'.
> ...
> DBCC results for 'CallDataItemEnumeratedStaging'.
> There are 201627 rows in 2000 pages for object
> 'CallDataItemEnumeratedStaging'.
> Server: Msg 8936, Level 16, State 1, Line 1
> Table error: Object ID 1106102981, index ID 1. B-tree chain linkage
> mismatch. (1:21981)->next = (1:21983), but (1:21983)->Prev = (1:22065).
> Server: Msg 8978, Level 16, State 1, Line 1
> Table error: Object ID 1106102981, index ID 1. Page (1:22065) is missing a
> reference from previous page (1:21981). Possible chain linkage problem.
> ...
> DBCC results for 'CallEventStaging'.
> Could not repair this error.
> Repairing this error requires other errors to be corrected first.
> There are 178358 rows in 1487 pages for object 'CallEventStaging'.
> CHECKDB found 0 allocation errors and 2 consistency errors in table
> 'CallEventStaging' (object ID 1106102981).
> ...
> CHECKDB found 0 allocation errors and 2 consistency errors in database
> 'Leigh_Call2'.
> repair_rebuild is the minimum repair level for the errors found by DBCC
> CHECKDB (Leigh_Call2 repair_rebuild).
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> The statement has been terminated.
> This seems to be the error portion of the results returned.
> Each time I run it the name of the table with the problem changes.
> Is this pointing to perhaps some physical corruption on the disks?
> Info, views and opinions appreciated.
> Martin
>

Consistency error using DBCC CheckDB

Hi,
when I run DBCC CHECKDB against one of my call databases it tells me there
are two consistency errors and 0 allocation errors. The minimum repair
option is REPAIR_REBUILD.
So I have my db in single user mode and I execute:
dbcc checkdb ('Leigh_Call2', REPAIR_REBUILD) WITH ALL_ERRORMSGS
this returns:
Server: Msg 1505, Level 16, State 1, Line 1
CREATE UNIQUE INDEX terminated because a duplicate key was found for index
ID 1. Most significant primary key is '17847'.
DBCC results for 'Leigh_Call2'.
...
DBCC results for 'CallDataItemEnumeratedStaging'.
There are 201627 rows in 2000 pages for object
'CallDataItemEnumeratedStaging'.
Server: Msg 8936, Level 16, State 1, Line 1
Table error: Object ID 1106102981, index ID 1. B-tree chain linkage
mismatch. (1:21981)->next = (1:21983), but (1:21983)->Prev = (1:22065).
Server: Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 1106102981, index ID 1. Page (1:22065) is missing a
reference from previous page (1:21981). Possible chain linkage problem.
...
DBCC results for 'CallEventStaging'.
Could not repair this error.
Repairing this error requires other errors to be corrected first.
There are 178358 rows in 1487 pages for object 'CallEventStaging'.
CHECKDB found 0 allocation errors and 2 consistency errors in table
'CallEventStaging' (object ID 1106102981).
...
CHECKDB found 0 allocation errors and 2 consistency errors in database
'Leigh_Call2'.
repair_rebuild is the minimum repair level for the errors found by DBCC
CHECKDB (Leigh_Call2 repair_rebuild).
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
The statement has been terminated.
This seems to be the error portion of the results returned.
Each time I run it the name of the table with the problem changes.
Is this pointing to perhaps some physical corruption on the disks?
Info, views and opinions appreciated.
MartinThe repair is trying to rebuild the index - obviously that's failing because
of the duplicate key violation.
My guess as to what's causing dbcc to report these issues are stale reads
from your disk controller and there's an active workload on the database
that's causing page splits in various tables. I bet if you shut everything
down and power it back up then a checkdb will come back clean for the
database. If so, you need to run diagnostics on your IO subsystem and make
sure all the firmware is up to date.
Thanks
Paul Randal
Principal Lead Program Manager
Core Storage Engine, Microsoft SQL Server Team
http://blogs.msdn.com/sqlserverstor...ne/default.aspx
"Martin Selway" <martin.selway@.csdss.com> wrote in message
news:uxahkybOHHA.3900@.TK2MSFTNGP06.phx.gbl...
> Hi,
> when I run DBCC CHECKDB against one of my call databases it tells me there
> are two consistency errors and 0 allocation errors. The minimum repair
> option is REPAIR_REBUILD.
> So I have my db in single user mode and I execute:
> dbcc checkdb ('Leigh_Call2', REPAIR_REBUILD) WITH ALL_ERRORMSGS
> this returns:
> Server: Msg 1505, Level 16, State 1, Line 1
> CREATE UNIQUE INDEX terminated because a duplicate key was found for index
> ID 1. Most significant primary key is '17847'.
> DBCC results for 'Leigh_Call2'.
> ...
> DBCC results for 'CallDataItemEnumeratedStaging'.
> There are 201627 rows in 2000 pages for object
> 'CallDataItemEnumeratedStaging'.
> Server: Msg 8936, Level 16, State 1, Line 1
> Table error: Object ID 1106102981, index ID 1. B-tree chain linkage
> mismatch. (1:21981)->next = (1:21983), but (1:21983)->Prev = (1:22065).
> Server: Msg 8978, Level 16, State 1, Line 1
> Table error: Object ID 1106102981, index ID 1. Page (1:22065) is missing a
> reference from previous page (1:21981). Possible chain linkage problem.
> ...
> DBCC results for 'CallEventStaging'.
> Could not repair this error.
> Repairing this error requires other errors to be corrected first.
> There are 178358 rows in 1487 pages for object 'CallEventStaging'.
> CHECKDB found 0 allocation errors and 2 consistency errors in table
> 'CallEventStaging' (object ID 1106102981).
> ...
> CHECKDB found 0 allocation errors and 2 consistency errors in database
> 'Leigh_Call2'.
> repair_rebuild is the minimum repair level for the errors found by DBCC
> CHECKDB (Leigh_Call2 repair_rebuild).
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> The statement has been terminated.
> This seems to be the error portion of the results returned.
> Each time I run it the name of the table with the problem changes.
> Is this pointing to perhaps some physical corruption on the disks?
> Info, views and opinions appreciated.
> Martin
>

Connectivity error on a table

I am using sgl2000 server ent. edition. I could not view the table on a
database.I am able to view the structure of the same. When I try to run a
query with the same table it says an error "[Microsoft][ODBC SQL Ser
ver
Driver]& #91;DBNETLIB]ConnectionOpen(WrapperRead(
))"
I don't know what is to be done? But last one year i am using the same table
without any issues. please any one could resolve this issue.Hi
Check the SQL Event log.
You might get a surprise and find that SQL has killed your SPID due to
corruption on that table.
Regards
Mike
"Shanthi" wrote:

> I am using sgl2000 server ent. edition. I could not view the table on a
> database.I am able to view the structure of the same. When I try to run a
> query with the same table it says an error "[Microsoft][ODBC SQL S
erver
> Driver]& #91;DBNETLIB]ConnectionOpen(WrapperRead(
))"
> I don't know what is to be done? But last one year i am using the same tab
le
> without any issues. please any one could resolve this issue.|||I have checked both sql log and event log and nothing found as you told.
Please let me know if any thing is to be done.
Shanthi
"Mike Epprecht (SQL MVP)" ?? ????:
[vbcol=seagreen]
> Hi
> Check the SQL Event log.
> You might get a surprise and find that SQL has killed your SPID due to
> corruption on that table.
> Regards
> Mike
> "Shanthi" wrote:
>

Connectivity error on a table

I am using sgl2000 server ent. edition. I could not view the table on a
database.I am able to view the structure of the same. When I try to run a
query with the same table it says an error "[Microsoft][ODBC SQL Server
Driver][DBNETLIB]ConnectionOpen(WrapperRead())"
I don't know what is to be done? But last one year i am using the same table
without any issues. please any one could resolve this issue.
Hi
Check the SQL Event log.
You might get a surprise and find that SQL has killed your SPID due to
corruption on that table.
Regards
Mike
"Shanthi" wrote:

> I am using sgl2000 server ent. edition. I could not view the table on a
> database.I am able to view the structure of the same. When I try to run a
> query with the same table it says an error "[Microsoft][ODBC SQL Server
> Driver][DBNETLIB]ConnectionOpen(WrapperRead())"
> I don't know what is to be done? But last one year i am using the same table
> without any issues. please any one could resolve this issue.
|||I have checked both sql log and event log and nothing found as you told.
Please let me know if any thing is to be done.
Shanthi
"Mike Epprecht (SQL MVP)" ?? ????:
[vbcol=seagreen]
> Hi
> Check the SQL Event log.
> You might get a surprise and find that SQL has killed your SPID due to
> corruption on that table.
> Regards
> Mike
> "Shanthi" wrote:

Connectivity

Hi
Im very new to SQL server so forgive me if this is a basic
question. Im running SQL server on a windows 2000 machine
and when I run Analysis Services it picks up the server
(my local computer name) perfectly. However, when I try
and register the server in Enterprise Manager it cant find
it, doesnt make sense to me. Could it be because I am
logged into our network and not logged on the computer as
an administrator and therefore dont have admin rights on
the PC.
Thanks
John
What error do you get when yo try to register the SQL Server?
Rand
This posting is provided "as is" with no warranties and confers no rights.
|||Try using the ip address of your computer instead of the computer name and
see if it connects...How were you trying to connect, using sql server
authentication or Windows authentication? Try to connect using sql server
authentication. If this is a fresh install, sa user will probably have a
blank password, use the sa account to try to connect and see if it does.
HTH.
=)
Ricky Artigas
"John" <woody77_80@.hotmail.com> wrote in message
news:1037901c43f40$9e5e3f10$a601280a@.phx.gbl...
> Hi
> Im very new to SQL server so forgive me if this is a basic
> question. Im running SQL server on a windows 2000 machine
> and when I run Analysis Services it picks up the server
> (my local computer name) perfectly. However, when I try
> and register the server in Enterprise Manager it cant find
> it, doesnt make sense to me. Could it be because I am
> logged into our network and not logged on the computer as
> an administrator and therefore dont have admin rights on
> the PC.
> Thanks
> John
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.688 / Virus Database: 449 - Release Date: 5/18/2004
|||Hi john
the problem you have due to two things
1)please load the sql server service pack 3 or 4
it solve your problem.
you can download from microsoft site
second one may be please reinstall and look the sql80 folder.if after uninstall it may be occur in the comp then remove it.again load it
..
biswajit
|||Hi
Thanks for the responses, still not working. I tried
setting it up as LOCAL and got the error
message, "specified SQL server not found (ConnectionOpen
(Connect). Same message when using the computer name or IP
address. Using SQL server pack 4.
Thanks
John

>--Original Message--
>What error do you get when yo try to register the SQL
Server?
>Rand
>This posting is provided "as is" with no warranties and
confers no rights.
>.
>
|||If this is new instance is a named instance it will not communicate on
port 1433 as unamed (default) instances do. Use start>run>
Cliconfig.exe and set the instance up with an alias to be able to set
a fixed port number. Other tools installed previously may look for the
1433 port.
On Fri, 21 May 2004 07:33:44 -0700, "John" <woody77_80@.hotmail.com>
wrote:

>Hi
>Im very new to SQL server so forgive me if this is a basic
>question. Im running SQL server on a windows 2000 machine
>and when I run Analysis Services it picks up the server
>(my local computer name) perfectly. However, when I try
>and register the server in Enterprise Manager it cant find
>it, doesnt make sense to me. Could it be because I am
>logged into our network and not logged on the computer as
>an administrator and therefore dont have admin rights on
>the PC.
>Thanks
>John
sqlsql

ConnectionRead(recv()) error help

I keep getting this error while I run my application. I used to run the application but it was taking to long so I decided to add some stored procedures to it and this error started happening.

Microsoft OLE DB Provider for ODBC Drivers
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionRead (recv()).

Does anyone know what this error is referring to?

JoseWhat does your connection string look like in your application? And is this an ASP or ASP.NET application?

Terri

Connection to Analysis Services 2005 using .NET 2.0

Hello,
I'm trying to use ASP.NET 2.0 to connect to a 2005 Analysis services database.
My script run on a IIS 6 web site under MS Server 2003 SP1.
For my test, the Analysis services database and the web site are on the same
server. My web site is configured as "Allowed anymous access" with the user
acount "IUSR_ServerName". The authenticated access is define to "Integrated
windows authentification".
When I execute my web page, the connection can't be established with de
database, and the following error is raised :
"
OpenEither the user, MyDomain\MyServer$, does not have access to the
MyDatabase database, or the database does not exist.
at
Microsoft.AnalysisServices.AdomdClient.AdomdConnec tion.XmlaClientProvider.Microsoft.AnalysisServices .AdomdClient.IExecuteProvider.ExecuteTabular(Comma ndBehavior
behavior, ICommandContentProvider contentProvider, AdomdPropertyCollection
commandProperties, IDataParameterCollection parameters) at
Microsoft.AnalysisServices.AdomdClient.AdomdComman d.ExecuteReader(CommandBehavior
behavior) at
Microsoft.AnalysisServices.AdomdClient.AdomdComman d.System.Data.IDbCommand.ExecuteReader(CommandBeha vior
behavior) at System.Data.Common.DbDataAdapter.FillInternal(Data Set dataset,
DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable,
IDbCommand command, CommandBehavior behavior) 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) at
_Default.getDataset(String p_mdxQuery)
"
This is my connection string : "Provider=MSOLAP;Data Source=MyServerIP;User
ID=MyID; pwd=MyPwd;Initial Catalog=MyCatalogue;Client Cache Size=25;Auto
Synch Period=10000".
Is there anybody who have a suggestion why the connection can not be
established ? or is anyone have a sample solution to access to an analysis
services database through a web site ?
The User ID and Pwd parameters on the connection string are only used
when connecting via http, so the are being ignored, Your ASP.NET site
will be trying to access SSAS using the identity defined in the
application pool (which by the look of it is using the default local
service account)
Your options as I see them are.
1) Change the identity of the application pool in IIS 6 to use a domain
account that has access to the cubes.
2) Configure SSAS for HTTP connections so that you can use the User ID
and Pwd parameters on the connection string
3) Set up SSAS to allow anonymous connections.
I have put these options in order from what I consider "best" to "worst"
from a security & performance standpoint. However this is just my
opinion, there may be factors in your particular situation that I am not
aware of that may cause you to prefer one of the options that I have
ranked lower.
Regards
Darren Gosbell [MVP]
Blog: http://geekswithblogs.net/darrengosbell
*** Sent via Developersdex http://www.codecomments.com ***

Connection to Analysis Services 2005 using .NET 2.0

Hello,
I'm trying to use ASP.NET 2.0 to connect to a 2005 Analysis services databas
e.
My script run on a IIS 6 web site under MS Server 2003 SP1.
For my test, the Analysis services database and the web site are on the same
server. My web site is configured as "Allowed anymous access" with the user
acount "IUSR_ServerName". The authenticated access is define to "Integrated
windows authentification".
When I execute my web page, the connection can't be established with de
database, and the following error is raised :
"
OpenEither the user, MyDomain\MyServer$, does not have access to the
MyDatabase database, or the database does not exist.
at
Microsoft.AnalysisServices.AdomdClient.AdomdConnection.XmlaClientProvider.Mi
crosoft.AnalysisServices.AdomdClient.IExecuteProvider.ExecuteTabular(Command
Behavior
behavior, ICommandContentProvider contentProvider, AdomdPropertyCollection
commandProperties, IDataParameterCollection parameters) at
Microsoft.AnalysisServices.AdomdClient.AdomdCommand.ExecuteReader(CommandBeh
avior
behavior) at
Microsoft.AnalysisServices.AdomdClient.AdomdCommand.System.Data.IDbCommand.E
xecuteReader(CommandBehavior
behavior) at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset,
DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcT
able,
IDbCommand command, CommandBehavior behavior) 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) at
_Default.getDataset(String p_mdxQuery)
"
This is my connection string : "Provider=MSOLAP;Data Source=MyServerIP;User
ID=MyID; pwd=MyPwd;Initial Catalog=MyCatalogue;Client Cache Size=25;Auto
Synch Period=10000".
Is there anybody who have a suggestion why the connection can not be
established ? or is anyone have a sample solution to access to an analysis
services database through a web site ?The User ID and Pwd parameters on the connection string are only used
when connecting via http, so the are being ignored, Your ASP.NET site
will be trying to access SSAS using the identity defined in the
application pool (which by the look of it is using the default local
service account)
Your options as I see them are.
1) Change the identity of the application pool in IIS 6 to use a domain
account that has access to the cubes.
2) Configure SSAS for HTTP connections so that you can use the User ID
and Pwd parameters on the connection string
3) Set up SSAS to allow anonymous connections.
I have put these options in order from what I consider "best" to "worst"
from a security & performance standpoint. However this is just my
opinion, there may be factors in your particular situation that I am not
aware of that may cause you to prefer one of the options that I have
ranked lower.
Regards
Darren Gosbell [MVP]
Blog: http://geekswithblogs.net/darrengosbell
*** Sent via Developersdex http://www.codecomments.com ***

Connection string problem

I'm converting a simple Access application to ASP.NET with MSDE to run on our Intranet. The app has just one page with 2 SqlDataSource controls. Initially I configured the data source to connect directly to the .mdb file. Once I got the code running, I used the "Upsize Wizard" in Access to convert the data to a SQL database using the MSDE instance running on my web server. I then re-configured the SqlDataSources to connect to the MSDE database.

I am able to step all the way through the configuration wizard, and when I test the query, the correct data is returned. However, when I run the app (from within Visual Web Developer Express) I get the following exception when I bind a drop-down list to one of the SqlDataSources:

An OLE DB Provider was not specified in the ConnectionString.  An example would be, 'Provider=SQLOLEDB;'.

The connection string generated by the Wizard in my web.config is this:

<

addname="ConnectionString"connectionString="Data Source=BCFWEB01;Initial Catalog=InOut;User ID=XXX;Password=XXXXXXXXX"providerName="System.Data.SqlClient" />

Any idea why its complaining that I don't have an OLE DB provider, and why the query works fine from the Configuration Wizard but not when I run the page?

Thanks,

what is your code to bind your datasource? This errors mostly occurs when you are using OleDb connections in your code, and you have an Sql connection in your web.config file.|||Problem went away when I deleted & recreated the data source. I no longer have the code that didn't work :) but I suspect that's what the issue was. Thanks for the help!

Connection string not valid !

I've build a website with asp.net and on my local machine it run very we. but when I store my website on a server, I have a error : ...error: 25 - Connection String not valid...

This is my connection string on my local machine : "add name="csIyp" connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|iyapason.mdf;User Instance=true" providerName="System.Data.SqlClient" "

And the connection string on my webserver : "add name="csIyp" connectionString="data source= .\MSSQLSERVER;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|iyapason.mdf;User Instance=true" providerName="System.Data.SqlClient" "

So, what can I do to solve this probleme.

Thanks !

Does your web server have an actual SQL Server Express database engine installed, or SQL Server 2005 of some kind (as in, not Express). If the later, attaching DB on the fly like that string does might not work.|||There is a SQL Server 2005 engine installed on my webserver and i think that the instance name is "MSSQLSERVER". so can I have a sample connection string?|||

Hi siebobby,

Base on my understanding, we will not install SQL Server Express on the web server. So I think MSSQLSERVER is not an Express Edition. If my supposition is incorrect, please feel free to point out.

AttachDBFilename is used to specify the database to attach to the user instance. And User instances only work in the Express Edition of SQL Server 2005. So you must change the connect string for new SQL Server, the string may likes below:

connectionString="Data Source=.\MSSQLSERVER;Initial Catalog=iyapason;Integrated Security=True"

Be sure attaching iyapason.mdf in the MSSQLSERVER first.

Connection String for SQL 2005 Express

Hi,
My C# app uses SQL Server 2005 Express edition.
I store my connection string in a config file.
My application will run on numerous machines and therefore the machine name
will be different each time. What I would like is a generic connection strin
g
so that I don't have to change it each time I install my app on a different
machine.
I therefore want to avoid specifing the machine name in the connection
string as shown below :-
"Data Source=MachineName;Initial Catalog=pubs;Integrated Security=SSPI;"
Is there any way to make this connection string generic so that it can stay
the same between different machines?
Thanks
MaccaIf you run your application on the same machine as the database, . (period)
works as a server name. So if your install SQL Express with the default
instance name, the server name is .\SQLEXPRESS
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Macca" <Macca@.discussions.microsoft.com> wrote in message
news:B0A6D9FF-6715-4BBD-83BB-0C8B1F9A7098@.microsoft.com...
> Hi,
> My C# app uses SQL Server 2005 Express edition.
> I store my connection string in a config file.
> My application will run on numerous machines and therefore the machine
> name
> will be different each time. What I would like is a generic connection
> string
> so that I don't have to change it each time I install my app on a
> different
> machine.
> I therefore want to avoid specifing the machine name in the connection
> string as shown below :-
> "Data Source=MachineName;Initial Catalog=pubs;Integrated Security=SSPI;"
> Is there any way to make this connection string generic so that it can
> stay
> the same between different machines?
> Thanks
> Macca

Connection String for selling Web Applications?

If I'm wanting to sell my Web Applications to clients that run using SQL Server, is it best to use a UserID and Password in the connection string, or would using Windows Integrated Security (SSPI) do? The reason I'm wondering is because I wouldn't know my customer's UserID and Password when they purchased one of my web apps, I would have to recompile the app with the new connection string (I'm not storing it in the web.config file)..

What is the best way to handle this on a global level, so that it would work on any system?

for example, would having the following connection string be a good idea for a production site?

string strMyConnectionString = " Data Source = (local); Integrated Security = SSPI; Initital Catalog = MyDBSource; ";It would really be better to allow it to be specified in the Web.Config. What happens if the database is not located on the same machine?|||Why are you not storing it in the web.config file ?|||I didn't store it in the web.config file for security reasons. Would the web.config be the best way to go? Should I encrypt the string?|||Yes and Yes

Connection string for PDA

Hello,

I am using VS 2005 and developing PDA device application to run on SQL CE. The PDA has Windows CE 5.0 OS.

Now I am using the following code for connection to database: I got this code from some website on Internet but do not remember which one:

Try
Dim FullAppName As String = [Assembly].GetCallingAssembly().GetName.CodeBase
Dim FullAppPath As String = Path.GetDirectoryName(FullAppName)
MessageBox.Show(FullAppPath)

Dim FullFileName As String = Path.Combine(FullAppPath, "Test123.sdf")
Dim cn As New SqlCeConnection(String.Format("Data Source={0}", FullFileName))
MessageBox.Show(FullFileName)
cn.Open()
MessageBox.Show("Connection Success")

Catch ex As Exception
MessageBox.Show(ex.ToString)
End Try
Here the problem is that
1. FullAppPath returns \Windows. But the database is in application folder i.e. \Program Files\DeviceSampleApp. Then why does it return \Windows.

2. So I copied the test123.sdf file in \Windows folder. But cn.Open goes to catch block. This means there is some error but surprisingly ex.ToString displays blank messagebox. What could be the issue?

3. I also changed the connection string as:
Dim cn As New SqlCeConnection("Data Source = \Program Files\DeviceSampleApp\Test123.sdf") and the Test123.sdf correctly gets deployed to this folder \Program Files\DeviceSampleApp\ which also has the EXE. Still it gives error that it cannot find the database Test123.sdf in the path \Program Files\DeviceSampleApp\ but I checked and it is there. What could be the issue?

Regards,
MI

For proper SQL CE error handling, see http://msdn2.microsoft.com/en-us/library/ms174079.aspx

For how to determine "App.Path" in .NET CF, see this: http://msdn2.microsoft.com/en-us/library/aa457089.aspx

|||

GetCallingAssembly() would work if this code is in the DLL which is called from your EXE. If this code is in the EXE itself use GetExecutingAssemly() instead.

connection sql server with asp.net

hello all....i'm got a little problem here...i hope youu
guys can help me...
ok...first, i'm using asp.net for my web application. when
i run it in my own server, it's ok...got nothing problem
with connection to my database(in the same server)
but...when i'm bring it to my web server with a same
application and a same database, it has a problem...i've
got an error:
Login failed for user '(null)'. Reason: Not associated
with a trusted SQL Server connection
aspx page working properly...but i think it's problem with
my connection to database..i declare my connection stringg
at global.asax.vb like this:
Public Const dbstring As String = "integrated security =
sspi; initial catalog = sola; data source = server10"
can you guys help me......
Zalizan,
are you impersonating the user, and / or using anonymous access?
If you are using impersonation and anonymous, then the IIS anonymous user
from the new box will need adding as a login to SQL Server.
If you aren't using impersonation and have anonymous, then the
IISComputer/ASPNET login will need adding.
Alternatively you could use SQL Server logins, especially if you are on
different domains.
HTH,
Paul Ibison
|||for your information sir, i'm new with Sql Server, and i
think i have added IIS anonymous user at SQL in Web
server. so can you give me a better way to add login to
user. And how i want to know that if i use anonymous
access or not

>--Original Message--
>Zalizan,
>are you impersonating the user, and / or using anonymous
access?
>If you are using impersonation and anonymous, then the
IIS anonymous user
>from the new box will need adding as a login to SQL
Server.
>If you aren't using impersonation and have anonymous,
then the
>IISComputer/ASPNET login will need adding.
>Alternatively you could use SQL Server logins, especially
if you are on
>different domains.
>HTH,
>Paul Ibison
>
>.
>
|||Zalizan,
if you open Administrative Tools, Internet Service Manager, open up your
website, right-click and go to properties. On directory security, click
edit, and the top check box will show if you have enabled anonymous. It is
enabled by default. As far as asp.net is concerned, unless you are using
impersonation (not on by default) you will be using a windows user called
ASPNET if your connectionstring selects trusted security. The "ASPNET" user
account is created in Windows by Microsoft .NET Framework. It is an
automatic machine account created to limit access rights of .NET
applications. In your case I believe this account should be added as a login
to SQL Server.
To be sure of using the correct connection string syntax, you could create a
UDL file. To get the udl file to open, create a blank textfile named as
xxx.udl. Then double click it to go through the wizard. Inside the udl file
(if you open using notepad) will be the connection string in the correct
format. The connection object is set using this string as follows:
SQL:
cn.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;User
ID=name;Password=password;Initial Catalog=database;Data Source=servername"
Trusted:
cn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist
Security Info=False;
Initial Catalog=database;Data Source=servername"
hope this helps,
Paul Ibison

connection sql server with asp.net

hello all....i'm got a little problem here...i hope youu
guys can help me...
ok...first, i'm using asp.net for my web application. when
i run it in my own server, it's ok...got nothing problem
with connection to my database(in the same server)
but...when i'm bring it to my web server with a same
application and a same database, it has a problem...i've
got an error:
Login failed for user '(null)'. Reason: Not associated
with a trusted SQL Server connection
aspx page working properly...but i think it's problem with
my connection to database..i declare my connection stringg
at global.asax.vb like this:
Public Const dbstring As String = "integrated security =
sspi; initial catalog = sola; data source = server10"
can you guys help me......Zalizan,
are you impersonating the user, and / or using anonymous access?
If you are using impersonation and anonymous, then the IIS anonymous user
from the new box will need adding as a login to SQL Server.
If you aren't using impersonation and have anonymous, then the
IISComputer/ASPNET login will need adding.
Alternatively you could use SQL Server logins, especially if you are on
different domains.
HTH,
Paul Ibison|||for your information sir, i'm new with Sql Server, and i
think i have added IIS anonymous user at SQL in Web
server. so can you give me a better way to add login to
user. And how i want to know that if i use anonymous
access or not

>--Original Message--
>Zalizan,
>are you impersonating the user, and / or using anonymous
access?
>If you are using impersonation and anonymous, then the
IIS anonymous user
>from the new box will need adding as a login to SQL
Server.
>If you aren't using impersonation and have anonymous,
then the
>IISComputer/ASPNET login will need adding.
>Alternatively you could use SQL Server logins, especially
if you are on
>different domains.
>HTH,
>Paul Ibison
>
>.
>|||Zalizan,
if you open Administrative Tools, Internet Service Manager, open up your
website, right-click and go to properties. On directory security, click
edit, and the top check box will show if you have enabled anonymous. It is
enabled by default. As far as asp.net is concerned, unless you are using
impersonation (not on by default) you will be using a windows user called
ASPNET if your connectionstring selects trusted security. The "ASPNET" user
account is created in Windows by Microsoft .NET Framework. It is an
automatic machine account created to limit access rights of .NET
applications. In your case I believe this account should be added as a login
to SQL Server.
To be sure of using the correct connection string syntax, you could create a
UDL file. To get the udl file to open, create a blank textfile named as
xxx.udl. Then double click it to go through the wizard. Inside the udl file
(if you open using notepad) will be the connection string in the correct
format. The connection object is set using this string as follows:
SQL:
cn.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;User
ID=name;Password=password;Initial Catalog=database;Data Source=servername"
Trusted:
cn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist
Security Info=False;
Initial Catalog=database;Data Source=servername"
hope this helps,
Paul Ibison

Connection specific variable?

Hi,
i have several triggers which under certain circumstances i dont want to
run. I would like to be able to set a variable against the sql connection
which i can test for within my trigger and then prevent running if certain
conditions apply. Is this possible? without using connection specific
tables? is it even possible with them?
--
--
TimBHi Tim,
Can you explain why you don't want the triggers to run? If it is for a
maintenance or administration task you can turn the triggers off with ALTER
TABLE <table name> DISABLE TRIGGER ALL, and enable them later with ALTER
TABLE <table name> ENABLE TRIGGER ALL. Note that this will disable the
triggers for all users, not just the current connection, and that you need
to be a member of the sysadmin, db_ddladmin or db_owner roles to use it.
If you want to do it in a different scenario, triggers might not be the
right solution and you can be better off putting your code in a stored
procedure. Can you give some more information in that case?
--
Jacco Schalkwijk
SQL Server MVP
"timb" <timb@.test.com> wrote in message
news:eVUlJnM4DHA.2804@.TK2MSFTNGP11.phx.gbl...
> Hi,
> i have several triggers which under certain circumstances i dont want
to
> run. I would like to be able to set a variable against the sql connection
> which i can test for within my trigger and then prevent running if certain
> conditions apply. Is this possible? without using connection specific
> tables? is it even possible with them?
> --
> --
> TimB
>|||There are certain routines within my vb app which need to skip certain
sections of a trigger as they cater for the code elsewhere.
I have tried to use a temporatry table however how do i check that the table
exists befreo selecting from it. i.e. to prevent an error being raised when
the table is not present.
I have also found that you can create a @.@. variable but cannot currently get
it to persist (if i am using it correctly) and there is no help about
creating these.
Is it possible to create a connection specific variable?
thanks in advance
TimB
--
--
TimB[490134]
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:OA0TQHN4DHA.2348@.TK2MSFTNGP09.phx.gbl...
> Hi Tim,
> Can you explain why you don't want the triggers to run? If it is for a
> maintenance or administration task you can turn the triggers off with
ALTER
> TABLE <table name> DISABLE TRIGGER ALL, and enable them later with ALTER
> TABLE <table name> ENABLE TRIGGER ALL. Note that this will disable the
> triggers for all users, not just the current connection, and that you need
> to be a member of the sysadmin, db_ddladmin or db_owner roles to use it.
> If you want to do it in a different scenario, triggers might not be the
> right solution and you can be better off putting your code in a stored
> procedure. Can you give some more information in that case?
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "timb" <timb@.test.com> wrote in message
> news:eVUlJnM4DHA.2804@.TK2MSFTNGP11.phx.gbl...
> > Hi,
> > i have several triggers which under certain circumstances i dont
want
> to
> > run. I would like to be able to set a variable against the sql
connection
> > which i can test for within my trigger and then prevent running if
certain
> > conditions apply. Is this possible? without using connection specific
> > tables? is it even possible with them?
> >
> > --
> > --
> > TimB
> >
> >
>|||Not that I recommend the following solution, but we implemented it to avoid
(temporarily) having to rewrite a rather large chunk of the application:
CREATE TABLE dbo.session_role (
spid int NOT NULL ,
role_id int NOT NULL ,
CONSTRAINT PK_session_context PRIMARY KEY CLUSTERED ( spid ),
CONSTRAINT FK_session_context_role FOREIGN KEY ( role_id ) REFERENCES
dbo.role_definitions ( id )
)
END
GO
CREATE PROCEDURE SetSessionRole @.nRoleID INT
AS
SET NOCOUNT ON
IF EXISTS(SELECT * FROM session_role WHERE spid = @.@.spid)
UPDATE session_role
SET role_id = @.nRoleID
WHERE spid = @.@.spid
ELSE
INSERT INTO session_role (spid, role_id)
VALUES (@.@.spid, @.nRoleID)
GO
You can now check in stored procedures, views and triggers for the role_id
with SELECT role_id FROM session_role WHERE spid = @.@.spid.
As I said before, this is not a good structural solution, but deadlines were
looming etc. Rewrite your application code at the earliest opportunity
possible.
Jacco Schalkwijk
SQL Server MVP
"timb" <timb@.test.com> wrote in message
news:%23bTatMN4DHA.2440@.TK2MSFTNGP09.phx.gbl...
> There are certain routines within my vb app which need to skip certain
> sections of a trigger as they cater for the code elsewhere.
> I have tried to use a temporatry table however how do i check that the
table
> exists befreo selecting from it. i.e. to prevent an error being raised
when
> the table is not present.
> I have also found that you can create a @.@. variable but cannot currently
get
> it to persist (if i am using it correctly) and there is no help about
> creating these.
> Is it possible to create a connection specific variable?
> thanks in advance
> TimB
> --
> --
> TimB[490134]
> "Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
> news:OA0TQHN4DHA.2348@.TK2MSFTNGP09.phx.gbl...
> > Hi Tim,
> >
> > Can you explain why you don't want the triggers to run? If it is for a
> > maintenance or administration task you can turn the triggers off with
> ALTER
> > TABLE <table name> DISABLE TRIGGER ALL, and enable them later with ALTER
> > TABLE <table name> ENABLE TRIGGER ALL. Note that this will disable the
> > triggers for all users, not just the current connection, and that you
need
> > to be a member of the sysadmin, db_ddladmin or db_owner roles to use it.
> >
> > If you want to do it in a different scenario, triggers might not be the
> > right solution and you can be better off putting your code in a stored
> > procedure. Can you give some more information in that case?
> >
> > --
> > Jacco Schalkwijk
> > SQL Server MVP
> >
> >
> > "timb" <timb@.test.com> wrote in message
> > news:eVUlJnM4DHA.2804@.TK2MSFTNGP11.phx.gbl...
> > > Hi,
> > > i have several triggers which under certain circumstances i dont
> want
> > to
> > > run. I would like to be able to set a variable against the sql
> connection
> > > which i can test for within my trigger and then prevent running if
> certain
> > > conditions apply. Is this possible? without using connection specific
> > > tables? is it even possible with them?
> > >
> > > --
> > > --
> > > TimB
> > >
> > >
> >
> >
>|||Hi Jacco,
thanks for the help. I was thinking of something similar however
using temporary tables i.e. #
what i was going to do was create the temporary table in the area where the
first transaction occurs and then delete the table or other such logic in
the specific part of the trigger which may be called as a result of the
first transaction.
Only thing is how do i check for the presence of this temporary table?
i.e.
Create Table #Stocktake
(
stocktake bit
)
insert into #stocktake values (1)
if exists (select * from tempdb.dbo.sysobjects where id =object_id(N'[dbo].[#stocktake]')) print 'table exists'
i cannot get the statement to print and i want to trap the error which will
occur in the trigger when it is called from transactions which dont have the
code to create the temporary table.
thanks in advance
TimB
--
TimB[490134]
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:%23CmZHiN4DHA.1948@.TK2MSFTNGP12.phx.gbl...
> Not that I recommend the following solution, but we implemented it to
avoid
> (temporarily) having to rewrite a rather large chunk of the application:
> CREATE TABLE dbo.session_role (
> spid int NOT NULL ,
> role_id int NOT NULL ,
> CONSTRAINT PK_session_context PRIMARY KEY CLUSTERED ( spid ),
> CONSTRAINT FK_session_context_role FOREIGN KEY ( role_id ) REFERENCES
> dbo.role_definitions ( id )
> )
> END
> GO
> CREATE PROCEDURE SetSessionRole @.nRoleID INT
> AS
> SET NOCOUNT ON
> IF EXISTS(SELECT * FROM session_role WHERE spid = @.@.spid)
> UPDATE session_role
> SET role_id = @.nRoleID
> WHERE spid = @.@.spid
> ELSE
> INSERT INTO session_role (spid, role_id)
> VALUES (@.@.spid, @.nRoleID)
> GO
> You can now check in stored procedures, views and triggers for the role_id
> with SELECT role_id FROM session_role WHERE spid = @.@.spid.
> As I said before, this is not a good structural solution, but deadlines
were
> looming etc. Rewrite your application code at the earliest opportunity
> possible.
>
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "timb" <timb@.test.com> wrote in message
> news:%23bTatMN4DHA.2440@.TK2MSFTNGP09.phx.gbl...
> > There are certain routines within my vb app which need to skip certain
> > sections of a trigger as they cater for the code elsewhere.
> >
> > I have tried to use a temporatry table however how do i check that the
> table
> > exists befreo selecting from it. i.e. to prevent an error being raised
> when
> > the table is not present.
> >
> > I have also found that you can create a @.@. variable but cannot currently
> get
> > it to persist (if i am using it correctly) and there is no help about
> > creating these.
> >
> > Is it possible to create a connection specific variable?
> >
> > thanks in advance
> >
> > TimB
> >
> > --
> > --
> > TimB[490134]
> > "Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
> > news:OA0TQHN4DHA.2348@.TK2MSFTNGP09.phx.gbl...
> > > Hi Tim,
> > >
> > > Can you explain why you don't want the triggers to run? If it is for a
> > > maintenance or administration task you can turn the triggers off with
> > ALTER
> > > TABLE <table name> DISABLE TRIGGER ALL, and enable them later with
ALTER
> > > TABLE <table name> ENABLE TRIGGER ALL. Note that this will disable the
> > > triggers for all users, not just the current connection, and that you
> need
> > > to be a member of the sysadmin, db_ddladmin or db_owner roles to use
it.
> > >
> > > If you want to do it in a different scenario, triggers might not be
the
> > > right solution and you can be better off putting your code in a stored
> > > procedure. Can you give some more information in that case?
> > >
> > > --
> > > Jacco Schalkwijk
> > > SQL Server MVP
> > >
> > >
> > > "timb" <timb@.test.com> wrote in message
> > > news:eVUlJnM4DHA.2804@.TK2MSFTNGP11.phx.gbl...
> > > > Hi,
> > > > i have several triggers which under certain circumstances i dont
> > want
> > > to
> > > > run. I would like to be able to set a variable against the sql
> > connection
> > > > which i can test for within my trigger and then prevent running if
> > certain
> > > > conditions apply. Is this possible? without using connection
specific
> > > > tables? is it even possible with them?
> > > >
> > > > --
> > > > --
> > > > TimB
> > > >
> > > >
> > >
> > >
> >
> >
>|||> Only thing is how do i check for the presence of this temporary table?
Try:
IF OBJECT_ID('tempdb..#Stocktake') IS NOT NULL
PRINT 'table exists'
--
Hope this helps.
Dan Guzman
SQL Server MVP
"timb" <timb@.test.com> wrote in message
news:O8NWaqP4DHA.3596@.TK2MSFTNGP11.phx.gbl...
> Hi Jacco,
> thanks for the help. I was thinking of something similar however
> using temporary tables i.e. #
> what i was going to do was create the temporary table in the area where
the
> first transaction occurs and then delete the table or other such logic in
> the specific part of the trigger which may be called as a result of the
> first transaction.
> Only thing is how do i check for the presence of this temporary table?
> i.e.
>
> Create Table #Stocktake
> (
> stocktake bit
> )
> insert into #stocktake values (1)
>
> if exists (select * from tempdb.dbo.sysobjects where id => object_id(N'[dbo].[#stocktake]')) print 'table exists'
>
> i cannot get the statement to print and i want to trap the error which
will
> occur in the trigger when it is called from transactions which dont have
the
> code to create the temporary table.
>
> thanks in advance
> TimB
>
> --
> --
> TimB[490134]
> "Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
> news:%23CmZHiN4DHA.1948@.TK2MSFTNGP12.phx.gbl...
> > Not that I recommend the following solution, but we implemented it to
> avoid
> > (temporarily) having to rewrite a rather large chunk of the application:
> >
> > CREATE TABLE dbo.session_role (
> > spid int NOT NULL ,
> > role_id int NOT NULL ,
> > CONSTRAINT PK_session_context PRIMARY KEY CLUSTERED ( spid ),
> > CONSTRAINT FK_session_context_role FOREIGN KEY ( role_id ) REFERENCES
> > dbo.role_definitions ( id )
> > )
> > END
> > GO
> > CREATE PROCEDURE SetSessionRole @.nRoleID INT
> > AS
> > SET NOCOUNT ON
> >
> > IF EXISTS(SELECT * FROM session_role WHERE spid = @.@.spid)
> > UPDATE session_role
> > SET role_id = @.nRoleID
> > WHERE spid = @.@.spid
> > ELSE
> > INSERT INTO session_role (spid, role_id)
> > VALUES (@.@.spid, @.nRoleID)
> >
> > GO
> >
> > You can now check in stored procedures, views and triggers for the
role_id
> > with SELECT role_id FROM session_role WHERE spid = @.@.spid.
> >
> > As I said before, this is not a good structural solution, but deadlines
> were
> > looming etc. Rewrite your application code at the earliest opportunity
> > possible.
> >
> >
> > --
> > Jacco Schalkwijk
> > SQL Server MVP
> >
> >
> > "timb" <timb@.test.com> wrote in message
> > news:%23bTatMN4DHA.2440@.TK2MSFTNGP09.phx.gbl...
> > > There are certain routines within my vb app which need to skip certain
> > > sections of a trigger as they cater for the code elsewhere.
> > >
> > > I have tried to use a temporatry table however how do i check that the
> > table
> > > exists befreo selecting from it. i.e. to prevent an error being
raised
> > when
> > > the table is not present.
> > >
> > > I have also found that you can create a @.@. variable but cannot
currently
> > get
> > > it to persist (if i am using it correctly) and there is no help about
> > > creating these.
> > >
> > > Is it possible to create a connection specific variable?
> > >
> > > thanks in advance
> > >
> > > TimB
> > >
> > > --
> > > --
> > > TimB[490134]
> > > "Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
> > > news:OA0TQHN4DHA.2348@.TK2MSFTNGP09.phx.gbl...
> > > > Hi Tim,
> > > >
> > > > Can you explain why you don't want the triggers to run? If it is for
a
> > > > maintenance or administration task you can turn the triggers off
with
> > > ALTER
> > > > TABLE <table name> DISABLE TRIGGER ALL, and enable them later with
> ALTER
> > > > TABLE <table name> ENABLE TRIGGER ALL. Note that this will disable
the
> > > > triggers for all users, not just the current connection, and that
you
> > need
> > > > to be a member of the sysadmin, db_ddladmin or db_owner roles to use
> it.
> > > >
> > > > If you want to do it in a different scenario, triggers might not be
> the
> > > > right solution and you can be better off putting your code in a
stored
> > > > procedure. Can you give some more information in that case?
> > > >
> > > > --
> > > > Jacco Schalkwijk
> > > > SQL Server MVP
> > > >
> > > >
> > > > "timb" <timb@.test.com> wrote in message
> > > > news:eVUlJnM4DHA.2804@.TK2MSFTNGP11.phx.gbl...
> > > > > Hi,
> > > > > i have several triggers which under certain circumstances i
dont
> > > want
> > > > to
> > > > > run. I would like to be able to set a variable against the sql
> > > connection
> > > > > which i can test for within my trigger and then prevent running if
> > > certain
> > > > > conditions apply. Is this possible? without using connection
> specific
> > > > > tables? is it even possible with them?
> > > > >
> > > > > --
> > > > > --
> > > > > TimB
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>

Connection question about using impersonate in webpage

Hi,
I create a webservice and impersonate a specific user only when you run a
connection to sql server to insert the data.
My connection string is as follows:
Integrated Security=SSPI;Persist Security Info=false;Initial
Catalog=cmcMail;Data Source=MASDD1;Network Library=dbmssocn;Packet Size=1024;
I got the error during debugging which is "Error: 18456, Severity: 14,
State: 11" . It means that "Valid login but server access failure"
After running some testings, I have a few questions about the inpersonate
issue:
1. If I user anohter domain user, who has access right to server and
database even to execute the store procedure as same as the specific user.
The data is inserted without problem. Why one can work and another cann't?
2. I tried to user the user is not my database user but can access another
database on the same SQL server.
It didn't raise error, and the data is insert on the table. Why this user
without premission can execute the store procedure.
Thanks
"BMW" wrote:

> Hi,
> I create a webservice and impersonate a specific user only when you run a
> connection to sql server to insert the data.
> My connection string is as follows:
> Integrated Security=SSPI;Persist Security Info=false;Initial
> Catalog=Mail;Data Source=MAS;Network Library=dbmssocn;Packet Size=1024;
> I got the error during debugging which is "Error: 18456, Severity: 14,
> State: 11" . It means that "Valid login but server access failure"
>
> After running some testings, I have a few questions about the inpersonate
> issue:
> 1. If I user anohter domain user, who has access right to server and
> database even to execute the store procedure as same as the specific user.
> The data is inserted without problem. Why one can work and another cann't?
> 2. I tried to user the user is not my database user but can access another
> database on the same SQL server.
> It didn't raise error, and the data is insert on the table. Why this user
> without premission can execute the store procedure.
> Thanks
>