problem with c# stored procedure calling a class

I wrote a stored procedure in c# that calls a class. It deployed fine but when I try to execute it, I get the error...

Msg 6522, Level 16, State 1, Procedure UpdateJobAdSearch, Line 0
A .NET Framework error occurred during execution of user defined routine or aggregate 'UpdateJobAdSearch':
System.Security.SecurityException: Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
System.Security.SecurityException:
at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)
at System.Security.PermissionSet.Demand()
at System.Data.Common.DbConnectionOptions.DemandPermission()
at System.Data.SqlClient.SqlConnection.PermissionDemand()
at System.Data.SqlClient.SqlConnectionFactory.PermissionDemand(DbConnection outerConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at System.Data.Common.DbDataAdapter.FillInternal(DataSet 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 JobAd.WhaleJobAd.CreateSearchableRecord()
at JobAd.WhaleJobAd.CreateSearchableRecord(String JobId)
at StoredProcedures.UpdateJobAdSearch(String JobAdId)

I read an article on MSDN that didn't directly relate, but mentioned the same error message. It suggested using...

System.Data.SqlClient.SqlClientPermission pSql = new SqlClientPermission(System.Security.Permissions.PermissionState.Unrestricted);
pSql.Assert();

I tried that, but I get the same error.

I finally found the answer to this. I needed to set the projects properties > database permission level to external. However, I don't understand why. I've scaled back my code, pulled it out the class so everything executes in the stored procedures method call and all it does it create 2 connections to the local database and pull info from 6 tables and put that info into 1. It's not trying to access anything outside the database, so why does it have to be set to external? In order to get that to work, I had to then alter the database and turn trustworthy on which is scary because of the security holes that opens up.|||

cakewalkr7:

I've scaled back my code, pulled it out the class so everything executes in the stored procedures method call and all it does it create 2 connections to the local database and pull info from 6 tables and put that info into 1.

I think maybe connections opened by the class are considered as access to external resource. Did you try to open a connection in this way?

SqlConnection conn = new SqlConnection("context connection = true");

sql

problem with bigints in SS 2000

Anybody know what this error means:
"Server: Msg 3624, Level 20, State 1, Line 1
Location: p:\sql\ntdbms\storeng\drs\include\record.inl:1447
Expression: m_SizeRec > 0 && m_SizeRec <= MAXDATAROW
SPID: 111
Process ID: 2028
Connection Broken"
I have a RESTORED database with about 10 large tables. A simple select
would return a couple of thousand of rows and then suddenly bomb out with
the above error. Interestingly the problem occurs only on the tables that
contain bigint columns, so I'm not sure if it's related. If I run "dbcc
checktable" on these table it returns lots of errors. I'm pretty sure the
database is not corrupt - it happens on any number of machines, plus the
database works fine at its original location. When I try to repair the
restored tables using "checktable" I can only do so with
"repair_allow_data_loss", not a good option of course. Also I have to
remove any indexes I have on the bigint columns. Any ideas?
Thanks
Have you tried using checktable with Repair_Rebuild. If that does not work then I would try deleting the indexes and rechecking then.
Jeff
MCDBA, MCSE+I
|||Hi,
Thanks for the reply. Yes, I have tried that, but it doesn't work - it only
works with "repair_allow_data_loss". Most of the errors are "consistency"
errors. It seems to me the problem lies with the bigint fields, but I'm not
sure why. I'm pretty sure the problem is not related to the indexes,
because only two of the problem tables have indexes. If I drop the indexes,
some of the errors (like "missing parents") go away, but the "consistency"
ones remain.
Tnx,
Pieter
"Jeff Duncan" <jduncan@.gtefcu.org> wrote in message
news:100C38FB-3B9B-43F6-8700-DECF9E014C74@.microsoft.com...
> Have you tried using checktable with Repair_Rebuild. If that does not
work then I would try deleting the indexes and rechecking then.
> Jeff
> MCDBA, MCSE+I

problem with bigints in SS 2000

Anybody know what this error means:
"Server: Msg 3624, Level 20, State 1, Line 1
Location: p:\sql\ntdbms\storeng\drs\include\record.inl:1447
Expression: m_SizeRec > 0 && m_SizeRec <= MAXDATAROW
SPID: 111
Process ID: 2028
Connection Broken"
I have a RESTORED database with about 10 large tables. A simple select
would return a couple of thousand of rows and then suddenly bomb out with
the above error. Interestingly the problem occurs only on the tables that
contain bigint columns, so I'm not sure if it's related. If I run "dbcc
checktable" on these table it returns lots of errors. I'm pretty sure the
database is not corrupt - it happens on any number of machines, plus the
database works fine at its original location. When I try to repair the
restored tables using "checktable" I can only do so with
"repair_allow_data_loss", not a good option of course. Also I have to
remove any indexes I have on the bigint columns. Any ideas?
ThanksHi,
Thanks for the reply. Yes, I have tried that, but it doesn't work - it only
works with "repair_allow_data_loss". Most of the errors are "consistency"
errors. It seems to me the problem lies with the bigint fields, but I'm not
sure why. I'm pretty sure the problem is not related to the indexes,
because only two of the problem tables have indexes. If I drop the indexes,
some of the errors (like "missing parents") go away, but the "consistency"
ones remain.
Tnx,
Pieter
"Jeff Duncan" <jduncan@.gtefcu.org> wrote in message
news:100C38FB-3B9B-43F6-8700-DECF9E014C74@.microsoft.com...
> Have you tried using checktable with Repair_Rebuild. If that does not
work then I would try deleting the indexes and rechecking then.
> Jeff
> MCDBA, MCSE+I

problem with bigints in SS 2000

Anybody know what this error means:
"Server: Msg 3624, Level 20, State 1, Line 1
Location: p:\sql\ntdbms\storeng\drs\include\record
.inl:1447
Expression: m_SizeRec > 0 && m_SizeRec <= MAXDATAROW
SPID: 111
Process ID: 2028
Connection Broken"
I have a RESTORED database with about 10 large tables. A simple select
would return a couple of thousand of rows and then suddenly bomb out with
the above error. Interestingly the problem occurs only on the tables that
contain bigint columns, so I'm not sure if it's related. If I run "dbcc
checktable" on these table it returns lots of errors. I'm pretty sure the
database is not corrupt - it happens on any number of machines, plus the
database works fine at its original location. When I try to repair the
restored tables using "checktable" I can only do so with
"repair_allow_data_loss", not a good option of course. Also I have to
remove any indexes I have on the bigint columns. Any ideas?
ThanksHave you tried using checktable with Repair_Rebuild. If that does not work
then I would try deleting the indexes and rechecking then.
Jeff
MCDBA, MCSE+I|||Hi,
Thanks for the reply. Yes, I have tried that, but it doesn't work - it only
works with "repair_allow_data_loss". Most of the errors are "consistency"
errors. It seems to me the problem lies with the bigint fields, but I'm not
sure why. I'm pretty sure the problem is not related to the indexes,
because only two of the problem tables have indexes. If I drop the indexes,
some of the errors (like "missing parents") go away, but the "consistency"
ones remain.
Tnx,
Pieter
"Jeff Duncan" <jduncan@.gtefcu.org> wrote in message
news:100C38FB-3B9B-43F6-8700-DECF9E014C74@.microsoft.com...
> Have you tried using checktable with Repair_Rebuild. If that does not
work then I would try deleting the indexes and rechecking then.
> Jeff
> MCDBA, MCSE+I

Problem while sending mail

Hello,
When i send a mail through the SQL Server mailing stem i sometimes get the
error
Server: Msg 18025, Level 16, State 1, Line 0
xp_sendmail: failed with mail error 0x80070005
Any particular reason for that and how can i solve that?
Thanks in advance
Regards
Ishan
Check out this url
http://support.microsoft.com/default...b;en-us;820220
The SQL Server Home Page is really good for finding out the causes of these
errors.
Cheers,
John - DBA
"Ishan Bhalla" wrote:

> Hello,
> When i send a mail through the SQL Server mailing stem i sometimes get the
> error
> Server: Msg 18025, Level 16, State 1, Line 0
> xp_sendmail: failed with mail error 0x80070005
> Any particular reason for that and how can i solve that?
> Thanks in advance
> Regards
> Ishan
|||"Ishan Bhalla" <IshanBhalla@.discussions.microsoft.com> wrote in message
news:0E2B65EE-8317-4092-9FCE-6954F5204BF7@.microsoft.com...
> When i send a mail through the SQL Server mailing stem i sometimes get
> the
> error
> Server: Msg 18025, Level 16, State 1, Line 0
> xp_sendmail: failed with mail error 0x80070005
Has it worked already or it never worked before?
Regards, Wojtek
|||It used to work before!
It stopped working after we upgraded the mailbox to an Exchange 2003 mailbox.
Regards
Ishan
"Wojtek Garwol" wrote:

> "Ishan Bhalla" <IshanBhalla@.discussions.microsoft.com> wrote in message
> news:0E2B65EE-8317-4092-9FCE-6954F5204BF7@.microsoft.com...
> Has it worked already or it never worked before?
> Regards, Wojtek
>
>

Problem while sending mail

Hello,
When i send a mail through the SQL Server mailing stem i sometimes get the
error
Server: Msg 18025, Level 16, State 1, Line 0
xp_sendmail: failed with mail error 0x80070005
Any particular reason for that and how can i solve that?
Thanks in advance
Regards
IshanCheck out this url
http://support.microsoft.com/defaul...kb;en-us;820220
The SQL Server Home Page is really good for finding out the causes of these
errors.
Cheers,
--
John - DBA
"Ishan Bhalla" wrote:

> Hello,
> When i send a mail through the SQL Server mailing stem i sometimes get the
> error
> Server: Msg 18025, Level 16, State 1, Line 0
> xp_sendmail: failed with mail error 0x80070005
> Any particular reason for that and how can i solve that?
> Thanks in advance
> Regards
> Ishan|||"Ishan Bhalla" <IshanBhalla@.discussions.microsoft.com> wrote in message
news:0E2B65EE-8317-4092-9FCE-6954F5204BF7@.microsoft.com...
> When i send a mail through the SQL Server mailing stem i sometimes get
> the
> error
> Server: Msg 18025, Level 16, State 1, Line 0
> xp_sendmail: failed with mail error 0x80070005
Has it worked already or it never worked before?
Regards, Wojtek|||It used to work before!
It stopped working after we upgraded the mailbox to an Exchange 2003 mailbox
.
Regards
Ishan
"Wojtek Garwol" wrote:

> "Ishan Bhalla" <IshanBhalla@.discussions.microsoft.com> wrote in message
> news:0E2B65EE-8317-4092-9FCE-6954F5204BF7@.microsoft.com...
> Has it worked already or it never worked before?
> Regards, Wojtek
>
>

Problem while sending mail

Hello,
When i send a mail through the SQL Server mailing stem i sometimes get the
error
Server: Msg 18025, Level 16, State 1, Line 0
xp_sendmail: failed with mail error 0x80070005
Any particular reason for that and how can i solve that?
Thanks in advance
Regards
IshanCheck out this url
http://support.microsoft.com/default.aspx?scid=kb;en-us;820220
The SQL Server Home Page is really good for finding out the causes of these
errors.
Cheers,
--
John - DBA
"Ishan Bhalla" wrote:
> Hello,
> When i send a mail through the SQL Server mailing stem i sometimes get the
> error
> Server: Msg 18025, Level 16, State 1, Line 0
> xp_sendmail: failed with mail error 0x80070005
> Any particular reason for that and how can i solve that?
> Thanks in advance
> Regards
> Ishan|||"Ishan Bhalla" <IshanBhalla@.discussions.microsoft.com> wrote in message
news:0E2B65EE-8317-4092-9FCE-6954F5204BF7@.microsoft.com...
> When i send a mail through the SQL Server mailing stem i sometimes get
> the
> error
> Server: Msg 18025, Level 16, State 1, Line 0
> xp_sendmail: failed with mail error 0x80070005
Has it worked already or it never worked before?
Regards, Wojtek|||It used to work before!
It stopped working after we upgraded the mailbox to an Exchange 2003 mailbox.
Regards
Ishan
"Wojtek Garwol" wrote:
> "Ishan Bhalla" <IshanBhalla@.discussions.microsoft.com> wrote in message
> news:0E2B65EE-8317-4092-9FCE-6954F5204BF7@.microsoft.com...
> > When i send a mail through the SQL Server mailing stem i sometimes get
> > the
> > error
> >
> > Server: Msg 18025, Level 16, State 1, Line 0
> > xp_sendmail: failed with mail error 0x80070005
> Has it worked already or it never worked before?
> Regards, Wojtek
>
>

Problem when connecting from remote location to SQL Server 2k SP 3

This is when using SA account trying the SQL Query
Analyzer. I get the error Msg 17, Level 16, State 1. I
can locally connect fine on the server itself using SQL
Query Analyzer. I'm using SQL Authentication.
When trying to connect I get:
Unable to connect to server (name of server)
Server: Msg 17, Level 16, State 1
[Microsoft][ODBC SQL Server Driver][DBNETLIB] SQL Server
does not exist or access denied.
I verified that the port is open on the firewall pointing
to the server. Also, this just started happening and was
working fine before. I've reset the SA password and
verified that I am putting in either the DNS entry or IP
address and get the same error. Any help diagnosing this
is greatly appreciated!
Hi Luke,
Try running the following command using ISQL.exe
ISQL -Stcp:ServerNameHere -Usa -Ppassword -Q"select @.@.version"
Look for the OS Error returned. This will help you troubleshoot the
problem.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
|||When I run that command, I get the following:
DB-Library: Unable to connect: SQL Server is unavailable
or does not exist. Una
ble to connect: SQL Server does not exist or network
access denied.
Net-Library error 10061: ConnectionOpen (Connect()).
This is when running on the server. I can't connect
remotely of course.
|||Hi Luke,
The actual error is : 10061:
This means that the server is not listening on the port.
Restart MSSQLServer and verify that SQL is listening.
Check the NT Application logs and SQL Errorlogs. For SQL 2000 we print
this in the errorlog.
Also you can verify this by running netstat -an at the command prompt.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Problem when connecting from remote location to SQL Server 2k SP 3

This is when using SA account trying the SQL Query
Analyzer. I get the error Msg 17, Level 16, State 1. I
can locally connect fine on the server itself using SQL
Query Analyzer. I'm using SQL Authentication.
When trying to connect I get:
Unable to connect to server (name of server)
Server: Msg 17, Level 16, State 1
[Microsoft][ODBC SQL Server Driver][DBNETLIB] SQL Server
does not exist or access denied.
I verified that the port is open on the firewall pointing
to the server. Also, this just started happening and was
working fine before. I've reset the SA password and
verified that I am putting in either the DNS entry or IP
address and get the same error. Any help diagnosing this
is greatly appreciated!Hi Luke,
Try running the following command using ISQL.exe
ISQL -Stcp:ServerNameHere -Usa -Ppassword -Q"select @.@.version"
Look for the OS Error returned. This will help you troubleshoot the
problem.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||When I run that command, I get the following:
DB-Library: Unable to connect: SQL Server is unavailable
or does not exist. Una
ble to connect: SQL Server does not exist or network
access denied.
Net-Library error 10061: ConnectionOpen (Connect()).
This is when running on the server. I can't connect
remotely of course.|||Hi Luke,
The actual error is : 10061:
This means that the server is not listening on the port.
Restart MSSQLServer and verify that SQL is listening.
Check the NT Application logs and SQL Errorlogs. For SQL 2000 we print
this in the errorlog.
Also you can verify this by running netstat -an at the command prompt.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Problem Using Alias

I need a select that sums the same column, but with different criteria.
I also need to "roll up" the sums into a "higher level" sum based on
some other tables. This is an accounting database, where subaccounts
roll up into a single account (i.e. subaccounts 101 and 102 roll up
into account 100).
I'm using an alias, but not getting correct results. I believe I'm
ending up with a cartesian product, but not sure of the "fix". Note: I
cannot change the table design, this is an existing accounting database
created by a different application.
Here's a simplified version of the query with tables following that:
SELECT Account.Name, Sum(A.Amount) as Actual, Sum(B.Amount) as Budget
FROM Account, SubAccount, Trans A, Trans B
WHERE
Account.AccountNum = SubAccount.AccountNum and
Trans.SubAccountNum = SubAccount.SubAccountNum and
A.Trans.Type = 'A' and B.Trans.Type = 'B'
GROUP BY Account.Name
What's wrong with my SQL above?
Here are my tables with sample data:
Table: Account
AccountNum Name
--
100 Fuel
200 Tires
Table: SubAccount
AccountNum SubAccountNum Name
---
100 101 Diesel
100 102 Gasoline
200 200 Winter Tire
200 201 All Season Tire
Table: Trans (transactions)
SubAccountNum Amount Type (A-Actual, B-Budget)
---
101 10 A
102 20 A
200 30 A
201 40 A
101 50 B
102 60 B
200 70 B
201 80 B

>From the data above, I need to end up with output from my Select as
follows:
Name Actual Budget
--
Fuel 30 110
Tires 70 150> FROM Account, SubAccount, Trans A, Trans B
Please use ANSI inner joins. It will make your query easier to read and
will allow you to

> Trans.SubAccountNum = SubAccount.SubAccountNum and
> A.Trans.Type = 'A' and B.Trans.Type = 'B'
You're saying, instead of calling Trans "Trans", let's call it "A". What do
you expect A.Trans to mean? Didn't you mean
A.SubAccountNum = SubAccount.SubAccountNum
AND
A.Type = 'A'
AND B.Type = 'B'
I have a better way to formulate the query however, because of my first
point, I have no idea whether two copies of the Trans table are required,
how they each relate to Account and SubAccount, and how the sums should be
generated. If you provide more details I'd be more than happy to write you
a more readable query. Please see: http://www.aspfaq.com/5006|||As you said, I should be using joins. The code below did the trick.
Thanks for your input.
SELECT Account.Name,
Sum(Case Trans.Type When 'A' Then Trans.Amount Else 0 End) as Actual,
Sum(Case Trans.Type When 'B' Then Trans.Amount Else 0 End) as Budget
FROM Account
INNER JOIN SubAccount
ON Account.AccountNum = SubAccount.AccountNum
INNER JOIN Trans
ON Trans.SubAccountNum = SubAccount.SubAccountNum|||> Please use ANSI inner joins. It will make your query easier to read and
> will allow you to
finish sentences! What I was going to elaborate on about is that you can
separate join and filter criteria. Looks like you've already got a handle
on it in this case.|||CREATE TABLE Accounts (AccountNum int NOT NULL, [name] varchar(25), PRIMARY
KEY (AccountNum))
GO
CREATE TABLE SubAccounts (AccountNum int REFERENCES Accounts(AccountNum),
SubAccountNum int, [name] varchar(25))
GO
INSERT INTO Accounts VALUES (100, 'Fuel')
INSERT INTO Accounts VALUES (200, 'Tires')
INSERT INTO SubAccounts VALUES (100,101,'Diesel')
INSERT INTO SubAccounts VALUES (100,102,'Gasoline')
INSERT INTO SubAccounts VALUES (200,200,'Winter Tire')
INSERT INTO SubAccounts VALUES (200,201,'All Season Tire')
CREATE TABLE Trans
(SubAccountNum int, Amount int, Type char(1))
GO
INSERT INTO test4 VALUES (101,10,'A')
INSERT INTO test4 VALUES (102,20,'A')
INSERT INTO test4 VALUES (200,30,'A')
INSERT INTO test4 VALUES (201,40,'A')
INSERT INTO test4 VALUES (101,50,'B')
INSERT INTO test4 VALUES (102,60,'B')
INSERT INTO test4 VALUES (200,70,'B')
INSERT INTO test4 VALUES (201,80,'B')
SELECT a.[name], SUM(t1.TotalActual) AS "Total Actual",
SUM(t2.TotalBudgeted) AS "Total Budgeted"
FROM Accounts a INNER JOIN SubAccounts sa ON a.AccountNum=sa.AccountNum
INNER JOIN (SELECT SubAccountNum, SUM(Amount) AS "TotalActual" FROM Trans
WHERE Type='A'
GROUP BY SubAccountNum) t1 ON t1.SubAccountNum=sa.SubAccountNum
INNER JOIN (SELECT SubAccountNum, SUM(Amount) AS "TotalBudgeted" FROM Trans
WHERE Type='B'
GROUP BY SubAccountNum) t2 ON t2.SubAccountNum=sa.SubAccountNum
GROUP BY a.[name]
"joeacunzo@.yahoo.com" wrote:

> I need a select that sums the same column, but with different criteria.
> I also need to "roll up" the sums into a "higher level" sum based on
> some other tables. This is an accounting database, where subaccounts
> roll up into a single account (i.e. subaccounts 101 and 102 roll up
> into account 100).
> I'm using an alias, but not getting correct results. I believe I'm
> ending up with a cartesian product, but not sure of the "fix". Note: I
> cannot change the table design, this is an existing accounting database
> created by a different application.
> Here's a simplified version of the query with tables following that:
> SELECT Account.Name, Sum(A.Amount) as Actual, Sum(B.Amount) as Budget
> FROM Account, SubAccount, Trans A, Trans B
> WHERE
> Account.AccountNum = SubAccount.AccountNum and
> Trans.SubAccountNum = SubAccount.SubAccountNum and
> A.Trans.Type = 'A' and B.Trans.Type = 'B'
> GROUP BY Account.Name
> What's wrong with my SQL above?
> Here are my tables with sample data:
> Table: Account
> AccountNum Name
> --
> 100 Fuel
> 200 Tires
> Table: SubAccount
> AccountNum SubAccountNum Name
> ---
> 100 101 Diesel
> 100 102 Gasoline
> 200 200 Winter Tire
> 200 201 All Season Tire
> Table: Trans (transactions)
> SubAccountNum Amount Type (A-Actual, B-Budget)
> ---
> 101 10 A
> 102 20 A
> 200 30 A
> 201 40 A
> 101 50 B
> 102 60 B
> 200 70 B
> 201 80 B
>
> follows:
> Name Actual Budget
> --
> Fuel 30 110
> Tires 70 150
>