SQl Server CLR
-
Hi all,
I have this procedure in Vb.net.
Public Sub getFinYearMonth()
EncoreConnect()
Dim cmd As New SqlCommand("select FinYear, FinMonth from InvControl")
cmd.CommandType = CommandType.Text
cmd.Connection = connEncore
Dim dr As SqlDataReader = cmd.ExecuteReader
Dim b As Boolean = dr.Read
FinYear = CInt(dr.Item("FinYear").ToString)
FinMonth = CInt(dr.Item("FinMonth").ToString)
dr.Close()
cmd.Dispose()
EncoreConnect()
End Sub
I need to declare this in Vb.net in order to call in sql server.
What is missing?
-
This was removed by the editor as SPAM
-
This was removed by the editor as SPAM
-
Hi Stewart, you are rigth. It is an example only.
-
Now I have got this code in C#.net in my Class library:
[SqlProcedure]public static void doCostChange(string username, string password, string company, string companypassword)
{
string Uid = "";
try
{
utilitiesclass s = new utilitiesclass();
Uid = s.Logon(username, password, company, companypassword, 0, 0, 0, "");
SqlContext.Pipe.Send(Uid.ToString());
}
catch (Exception ex)
{
SqlContext.Pipe.Send(ex.Message.ToString());
}
SqlCommand cmd = new SqlCommand("getItemsForCostChange");
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = conn;
cmd.Parameters.Add(new SqlParameter("@Year", SqlDbType.Int));
cmd.Parameters.Add(new SqlParameter("@Month", SqlDbType.Int));
cmd.Parameters.Add(new SqlParameter("@date", SqlDbType.Date));
cmd.Parameters[0].Value = 2012;
cmd.Parameters[1].Value = 12;
cmd.Parameters[2].Value = "2012-12-02";
SqlDataReader dr;
dr = cmd.ExecuteReader();
Boolean b = dr.Read();
while (b == true)
{
if (dr.GetString(1).Equals("ZK"))
{
CostChange("DK", dr.GetString(0), (double)dr.GetSqlDouble(7),Uid);
}
else
if (dr.GetString(1).Equals("ZW"))
{
CostChange("UK", dr.GetString(0), (double)dr.GetSqlDouble(7),Uid);
}
CostChange(dr.GetString(1), dr.GetString(0), (double)dr.GetSqlDouble(7),Uid);
b = dr.Read();
}
SqlContext.Pipe.Send("fim");
dr.Close();
cmd.Dispose();
}
I have already created assembly and sp in sql server for the fucntion bellow.
I am getting the message:
The settings property 'EngClearing_UtilitiesReference_utilitiesclass' was not found.
Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
Note that I am invoking a webservice method trougth the utilitiesclass class.
What is hapenig here.
Regards
-
This was removed by the editor as SPAM
-
I have used SAFE
-
This was removed by the editor as SPAM
-
Now I am getting only the messages:
The settings property 'EngClearing_UtilitiesReference_utilitiesclass' was not found.
-
This was removed by the editor as SPAM
-
Stewart "Arturius" Campbell (7/5/2012)
Has the method / dll that is being used correctly referenced and / or deployed? special care must oftimes be taken when other dll's are being referenced.that's where i'm a little weak, myself.
if the class is actually inside the project you are deploying, I've never had a problem.
but if it's a different DDLL, i think you have to add it to the target server first?
with a command like this?
--Register the NetFramework System.DirectoryServices assembly
CREATE ASSEMBLY [System.DirectoryServices]AUTHORIZATION [dbo]FROM 'C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.DirectoryServices.dll'
WITH PERMISSION_SET = UNSAFE
GO
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Actualy the class utilitiesclass belongs to a web service that i am referencing on my class library.
I have tested the call locally and it is working, but in sql server show that message.
On my Project Setting in c# i have got the property with name: EngClearing_UtilitiesReference_utilitiesclass,
Type:Web Service URL and Value: http://10.121.194.166/SysproWebServices/utilities.asmx
-
Actually I am referencing a web service with the settings:
Name:EngClearing_UtilitiesReference_utilitiesclass
Type:Web Service URL
Value: http://10.121.194.166/SysproWebServices/utilities.asmx
In .net work fine by when i create assembly in sql server 2008 gave me that message.
I have no Ideia what is missing.
Regards
-
Actually i am calling a web service method that i am referenced using the settings:
Name:EngClearing_UtilitiesReference_utilitiesclass
Type:http://10.121.194.166/SysproWebServices/utilities.asmx
Value: Web Service URL
On .net works fine by when i create assembly In SQL Server 2008 give me that message.
I have no Ideia what is going on.
Regards
-
I'm not sure either;
in my CLR where i'm using a web service, i have an added web reference;
i'm hitting the web service directly;
i'm guessing that for you, it's really indirect, since the other class is calling the web service.
is the utilities class a seperate dll, or is it a class within your project itself?
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply