CLR Stored Procedure Calling MS CRM Webservice Failing With Socket Error

Jun 18, 2007



Hi!



I am developing an integration solution for MS CRM.



The basic idea is to have a CLR stored procedure that draws data from a SQL database, transforms the data, and then adds it to MS CRM via the webservice.



When executing the stored proc, it randomly fails (although at approximately the same time, everytime).



This is the error message:



Msg 6522, Level 16, State 1, Procedure add_CCU_information, Line 0
A .NET Framework error occurred during execution of user defined routine or aggregate 'add_CCU_information':
System.Net.WebException: Unable to connect to the remote server ---> System.Net.Sockets.SocketException: Only one usage of each socket address (protocol/network address/port) is normally permitted
System.Net.Sockets.SocketException:
at System.Net.Sockets.Socket.DoConnect(EndPoint endPointSnapshot, SocketAddress socketAddress)
at System.Net.Sockets.Socket.InternalConnect(EndPoint remoteEP)
at System.Net.ServicePoint.ConnectSocketInternal(Boolean connectFailure, Socket s4, Socket s6, Socket& socket, IPAddress& address, ConnectSocketState state, IAsyncResult asyncResult, Int32 timeout, Exception& exception)
System.Net.WebException:
at System.Web.Services.Protocols.WebClientProtocol.GetWebResponse(WebRequest request)
at System.Web.Services.Protocols.HttpWebClientProtocol.GetWebResponse(WebRequest request)
at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)
at CRM_Integration.CRM_Service.CrmService.Execute(Request Request)
at StoredProcedures.add_CCU_information()



If someone could please give some advice, I would really appreciate it.



Regards,



Du Toit

View 2 Replies


ADVERTISEMENT

Calling Webservice From A T-SQl Stored Procedure

Oct 6, 2006

Hi,



I need to call a web service (consume a webservice)from a T-SQL stored procedure. Is there a way to do this. If not is there a way to make a simple http request, something like a utl_http in oracle.



At the moment iam using a MSSOAP30.SOAPCLIENT object created using sp_OACreate to make this call. However this means that the soap toolkit be installed on the pc on which SQL server is installed. I was hoping to find a completely independent way.



Also when i call sp_OACreate where does sqlserver 2005 look to find that object. Iam thinking of putting the MSSOAP30.dll on that machine, if all else fails.





Ahmad

View 1 Replies View Related

Error When Calling Stored Procedure

Nov 12, 2006

I am trying to execute a store procedure from ASP/VB but it fails with the message:
Incorrect syntax near 'InitProject'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near 'InitProject'.Source Error:



Line 24: cmd.Parameters("@ProjectId").Value = 3
Line 25: cn.Open()
Line 26: cmd.ExecuteNonQuery()
Line 27: cn.Close()
Line 28: End Sub
Here is my code:
        'Execute the InitProject stored procedure        'Create the connection from the string in the web.config file        Dim cn As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("SMARTConnectionString").ConnectionString)        'I want to execute InitProject stored procedure        Dim cmd As SqlCommand = New SqlCommand("InitProject", cn)        'With the parameter @ProjectId = 3        cmd.Parameters.Add(New SqlParameter("@ProjectId", Data.SqlDbType.Int))        cmd.Parameters("@ProjectId").Direction = Data.ParameterDirection.Input        cmd.Parameters("@ProjectId").Value = 3        cn.Open()        'But this fails        cmd.ExecuteNonQuery()        cn.Close()
And my stored procedure is defined as:
[dbo].[InitProject] @ProjectId int -- Add the parameters for the stored procedure hereASBEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON;
    -- Insert statements for procedure hereinsert into MATERIAL ( PROJECT_ID, SECTION_ID, CATEGORY_ID, ROOM_ID, ITEM_ID )select @ProjectId, SECTION_ID, CATEGORY_ID, ROOM_ID, ITEM_ID from MATERIAL_TEMPLATEEND
 The store procedure works fine when I do
exec InitProject 3
in sql query.

View 4 Replies View Related

Error In Calling Stored Procedure

Apr 3, 2007

I want to call a stored procedure in ASP.Net 2.0. I've already made sure that the SP contains no error by the Query Analyzer.However, when I try to run this in the ASP.Net application, error occured.This error message is {"Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding."} Calling the SP via a Class1 Public Function ExecuteStoredProcedure(ByVal Name As String, ByVal Para() As String, ByVal Value() As String)
2 If Para.Length <> Value.Length Then Exit Function
3 SqlCmd = New SqlCommand
4 SqlCmd.Connection = SqlConn
5 SqlCmd.CommandText = Name
6 SqlCmd.CommandType = CommandType.StoredProcedure
7 For i As Integer = 0 To Para.Length - 1
8 'SqlCmd.Parameters.AddWithValue(Para(i), Value(i))
9 Dim p As New SqlParameter(Para(i), SqlDbType.NVarChar)
10 p.Direction = ParameterDirection.Input
11 p.Value = Value(i)
12 SqlCmd.Parameters.Add(p)
13 Next
14 SqlCmd.ExecuteNonQuery()
15 End Function
 The Code behind file 1 Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click
2 If txtFromDate.Text = "" OrElse txtToDate.Text = "" Then
3 trMsg.Visible = True
4 Exit Sub
5 End If
6
7 Dim ts As New Thread(AddressOf GenerateReport)
8 ts.Start()
9
10 MultiView1.ActiveViewIndex = 1
11 lblConfirmationMessage.Text = "Report generating in progress." & vbNewLine & "You can continue using other functions."
12 End Sub
13
14 Protected Sub GenerateReport()
15 Dim userName As String = CType(Session("User"), clsUser).UserName
16 Dim from As String = txtFromDate.Text
17
18 Dim conn As New clsConnector
19 conn.OpenConnection()
20 'conn.Insert("EXEC gen_report '" + txtReportName.Text + "','" + txtDescription.Text + "','" + userName + "','" + txtFromDate.Text + "','" + txtToDate.Text + "';")
21 conn.ExecuteStoredProcedure("gen_report", _
22 New String() {"@name", "@remark", "@by", "@fromdate", "@todate"}, _
23 New String() {txtReportName.Text, txtDescription.Text, userName, txtFromDate.Text, txtToDate.Text})
24 conn.CloseConnection()
25 End Sub
26
  Thanks!

View 10 Replies View Related

Error (214) When Calling Stored Procedure From Job

Nov 2, 2000

I have some very simple SQL statements in stored procedures. How simple? Try this:
CREATE PROCEDURE [Insert_Inv_LevelA] AS
truncate table BI_Inv_LevelA
insert into BI_Inv_LevelA
select * from Input_Inv_LevelA

I can run the script fine in Query analyser both remotely and from the server console.
When I schedule the script to execute as a step in a job in SQL Server Agent, it falls over and returns the following error (in the step details of the job history):
Cannot convert parameter '@handle' to int data type expected by procedure. [SQLSTATE 42000] (Error 214). The step failed.

Every called SQL script returns an identical error, yet they all parse and run fine in the query analyser.
The syntax of the step is: sp_execute spInsert_Inv_LevelA
This is run as T-SQL on the target database as user dbo (or "(Self)" - the same error occurs either way).

Has anyone come across this before or have any clues? Technet and the MS site say nothing about it that I can find.

Cheers,

Mark.

View 3 Replies View Related

ASP Error Calling Stored Procedure

Mar 8, 2007

Hi,

I have an ASP program calling a stored procedure. Users enter their data, click submit, and the ASP program calls the SP which inserts the data into the SQL table. Initially it did this without checking for duplicate recs but now I have added code to block dup recs in the stored procedure. The SP is blocking the dups but when it returns to the ASP program I get the error:

ADODB.Recordset error '800a0cc1'
Item cannot be found in the collection corresponding to the requested name or ordinal.

There was no change of code in the ASP code but it is pointing to a line in that code.
Not sure what is causing this,
Thanks,
J.

View 8 Replies View Related

Error While Calling Oracle Stored Procedure!

Mar 19, 2008

Hi pals,

I am facing problems while calling Oracle stored procedure which has no parameters.

I have used Microsoft OLE DB provider for Oracle.
I have taken one Execute SQL task and set the SQLStatement as
call procedurename


I also set the IsStoredProcedure property to True for Execute SQL task.

Is there any synatx problem?

I am getting an error saying

Error: 0xC002F210 at Call Sp, Execute SQL Task: Executing the query "(call sp_procname)" failed with the following error: "ORA-00928: missing SELECT keyword.


I removed the curly braces for the SQL statement and again ran the Package, i am getting the below error

Executing the query "call sp_procname" failed with the following error: "ORA-06576: not a valid function or procedure name

Can anyone help me out on this regard!
Thanks in advance

View 3 Replies View Related

Error While Calling Oracle Stored Procedure From SSIS !

Mar 19, 2008

Hi pals,

I am facing problems while calling Oracle stored procedure which has no parameters.

I have used Microsoft OLE DB provider for Oracle.
I have taken one Execute SQL task and set the SQLStatement as
call procedurename


I also set the IsStoredProcedure property to True for Execute SQL task.

Is there any synatx problem?

I am getting an error saying

Error: 0xC002F210 at Call Sp, Execute SQL Task: Executing the query "(call sp_procname)" failed with the following error: "ORA-00928: missing SELECT keyword.


I removed the curly braces for the SQL statement and again ran the Package, i am getting the below error

Executing the query "call sp_procname" failed with the following error: "ORA-06576: not a valid function or procedure name

Can anyone help me out on this regard!
Thanks in advance.

View 2 Replies View Related

Access Webservice From A Stored Procedure.

Apr 1, 2008

Hi everybody,
How can I access a webservice from inside a stored procedure? any help is greatly appriciated.

View 3 Replies View Related

CLR Stored Procedure Consume Webservice

Nov 8, 2006

Can you have a CRL stored procedure call a webservice that returns a dataset?

View 7 Replies View Related

How To Debug Local Webservice's Stored Procedure From Asp.net

Aug 17, 2007

hi
    i have developed asp.net application which calls  web service of localsystem. it also contains logic for interaction with database. we are also creating connection string through coding not from web.config.  i want to debug stored procedure which is called by webmethod. then pls suggess me how can i debug this stored procedure from asp.net which is called  in local webservice???
  if any one have solve pls help...
atul

View 1 Replies View Related

Calling A Stored Procedure From ADO.NET 2.0-VB 2005 Express: Working With SELECT Statements In The Stored Procedure-4 Errors?

Mar 3, 2008

Hi all,

I have 2 sets of sql code in my SQL Server Management Stidio Express (SSMSE):

(1) /////--spTopSixAnalytes.sql--///

USE ssmsExpressDB

GO

CREATE Procedure [dbo].[spTopSixAnalytes]

AS

SET ROWCOUNT 6

SELECT Labtests.Result AS TopSixAnalytes, LabTests.Unit, LabTests.AnalyteName

FROM LabTests

ORDER BY LabTests.Result DESC

GO


(2) /////--spTopSixAnalytesEXEC.sql--//////////////


USE ssmsExpressDB

GO
EXEC spTopSixAnalytes
GO

I executed them and got the following results in SSMSE:
TopSixAnalytes Unit AnalyteName
1 222.10 ug/Kg Acetone
2 220.30 ug/Kg Acetone
3 211.90 ug/Kg Acetone
4 140.30 ug/L Acetone
5 120.70 ug/L Acetone
6 90.70 ug/L Acetone
/////////////////////////////////////////////////////////////////////////////////////////////
Now, I try to use this Stored Procedure in my ADO.NET-VB 2005 Express programming:
//////////////////--spTopSixAnalytes.vb--///////////

Public Class Form1

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim sqlConnection As SqlConnection = New SqlConnection("Data Source = .SQLEXPRESS; Integrated Security = SSPI; Initial Catalog = ssmsExpressDB;")

Dim sqlDataAdapter As SqlDataAdapter = New SqlDataAdaptor("[spTopSixAnalytes]", sqlConnection)

sqlDataAdapter.SelectCommand.Command.Type = CommandType.StoredProcedure

'Pass the name of the DataSet through the overloaded contructor

'of the DataSet class.

Dim dataSet As DataSet ("ssmsExpressDB")

sqlConnection.Open()

sqlDataAdapter.Fill(DataSet)

sqlConnection.Close()

End Sub

End Class
///////////////////////////////////////////////////////////////////////////////////////////

I executed the above code and I got the following 4 errors:
Error #1: Type 'SqlConnection' is not defined (in Form1.vb)
Error #2: Type 'SqlDataAdapter' is not defined (in Form1.vb)
Error #3: Array bounds cannot appear in type specifiers (in Form1.vb)
Error #4: 'DataSet' is not a type and cannot be used as an expression (in Form1)

Please help and advise.

Thanks in advance,
Scott Chang

More Information for you to know:
I have the "ssmsExpressDB" database in the Database Expolorer of VB 2005 Express. But I do not know how to get the SqlConnection and the SqlDataAdapter into the Form1. I do not know how to get the Fill Method implemented properly.
I try to learn "Working with SELECT Statement in a Stored Procedure" for printing the 6 rows that are selected - they are not parameterized.




View 11 Replies View Related

Soap Message Exchange Between .NET Webservice And Stored Procedure

Aug 16, 2007

Hello,

is message exchange between a .NET Webservice and a SQL stored procedure possible?
And if, could you please explain me how? Or give me a tip where i can get more informations
and maybe samples?
thanks for your help

regards
pamelia

View 30 Replies View Related

Calling A Stored Procedure Inside Another Stored Procedure (or Nested Stored Procedures)

Nov 1, 2007

Hi all - I'm trying to optimized my stored procedures to be a bit easier to maintain, and am sure this is possible, not am very unclear on the syntax to doing this correctly.  For example, I have a simple stored procedure that takes a string as a parameter, and returns its resolved index that corresponds to a record in my database. ie
exec dbo.DeriveStatusID 'Created'
returns an int value as 1
(performed by "SELECT statusID FROM statusList WHERE statusName= 'Created') 
but I also have a second stored procedure that needs to make reference to this procedure first, in order to resolve an id - ie:
exec dbo.AddProduct_Insert 'widget1'
which currently performs:SET @statusID = (SELECT statusID FROM statusList WHERE statusName='Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
I want to simply the insert to perform (in one sproc):
SET @statusID = EXEC deriveStatusID ('Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
This works fine if I call this stored procedure in code first, then pass it to the second stored procedure, but NOT if it is reference in the second stored procedure directly (I end up with an empty value for @statusID in this example).
My actual "Insert" stored procedures are far more complicated, but I am working towards lightening the business logic in my application ( it shouldn't have to pre-vet the data prior to executing a valid insert). 
Hopefully this makes some sense - it doesn't seem right to me that this is impossible, and am fairly sure I'm just missing some simple syntax - can anyone assist?
 

View 1 Replies View Related

Calling Webservice From CLR

Aug 30, 2007

I have a webservice that I would like to call from SQL Server 2005. I have done alot of testing and am pretty familiar with how CLR works and how to create assemblies in SQL. My problem is that I don't know much about .net and am not sure what kind of project I need to create in Visual Studios to accomplish calling the webservice. I've tried googling and have not found much that is of use to me.

Any help would be appreciated

Aaron

View 1 Replies View Related

Calling Webservice From A Trigger

Jun 26, 2007

Is it possible to call/fire a method in a webservice (.asmx) from a trigger in MS SQL 2005? I would like to send out a notification to all the admins whenever a new row is inserted into a table in our db. If possible, can someone show me an example of how to?

View 1 Replies View Related

Exception While Calling The Webservice From CLR

Sep 7, 2007


Hi,

I created a method in the webservice which will take productid as input parameter and return the product number, productname, and vendor account number and vendor name. I was able to run the web service successfully. And also created the assemblies and sp using these assembly.

At the final execution i am getting some security exception

The following is the exception I am getting€¦.



CREATE PROCEDURE GetProductVendorDetails(@ProductID int)

AS

EXTERNAL NAME GetProductVendorAssembly.StoredProcedures.CallWebService

GO


EXECUTE GetProductVendorDetails 2

Msg 6522, Level 16, State 1, Procedure GetProductVendorDetails, Line 0
A .NET Framework error occurred during execution of user-defined routine or aggregate "GetProductVendorDetails":
System.InvalidOperationException: There is an error in XML document (1, 281). ---> System.Security.SecurityException: That assembly does not allow partially trusted callers.
System.Security.SecurityException:
at System.Security.CodeAccessSecurityEngine.ThrowSecurityException(Assembly asm, PermissionSet granted, PermissionSet refused, RuntimeMethodHandle rmh, SecurityAction action, Object demand, IPermission permThatFailed)
at Microsoft.Xml.Serialization.GeneratedAssembly.XmlSerializationReader1.Read2_ProductVendorInfo(Boolean isNullable, Boolean checkType)
at Microsoft.Xml.Serialization.GeneratedAssembly.XmlSerializationReader1.Read9_Item()
at Microsoft.Xml.Serialization.GeneratedAssembly.ArrayOfObjectSerializer5.Deserialize(XmlSerializationReader reader)
at System.Xml.Serialization.XmlSerializer.Deserialize(XmlReader xmlReader, String encodingStyle, XmlDeserializationEvents events)
System.InvalidOperationException:
at System.Xml.Serialization.XmlSerializer.Deserialize(XmlReader xmlReader, String encodingStyle, XmlDeserializationEvents events)
at System.Xml.Serialization.XmlSerializer.Deserialize(XmlReader xmlReader, String encodingStyle)
at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall)
at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)
at CLRWebServiceProject.LocalWebService.ProductVendorInfoService.GetProductVendorDetails(Int32 ProductID)
at StoredProcedures.CallWebService(Int32 ProductID)
.


My Web Service Method €¦.


[WebMethod]
private void GetProductDetails(int ProductID)
{


// String ProductVendorDetail="";
//Set the connection string for the database
string connectionstring = "Server=PC013584;Database=AdventureWorks;User=Raj;Password=password";
//Create Connection and open it
SqlConnection conn = new SqlConnection(connectionstring);
conn.Open();
//Create the command object
SqlCommand comm = new SqlCommand();
comm.Connection = conn;
comm.CommandText = "SELECT P.ProductID as ProductID,P.Name as ProductName,P.ProductNumber as ProductNumber,V.AccountNumber as VendorAccountNumber,V.Name VendorName"
+ " FROM Production.Product P "
+ " INNER JOIN Purchasing.ProductVendor PV ON (PV.ProductID = P.ProductID) "
+ " INNER JOIN Purchasing.Vendor V ON(V.VendorID = PV.VendorID) "
+ " WHERE P.ProductID =" + ProductID.ToString();


SqlDataReader thisReader = comm.ExecuteReader();
while (thisReader.Read())
{
//Console.WriteLine(myReader["Column1"].ToString());
//Console.WriteLine(myReader["Column2"].ToString());
pvinfo.ProductID = Int32.Parse(thisReader["ProductID"].ToString());
pvinfo.ProductName = thisReader["ProductName"].ToString();
pvinfo.ProductNumber = thisReader["ProductNumber"].ToString();
pvinfo.VendorAccountNumber = thisReader["VendorAccountNumber"].ToString();
pvinfo.VendorName = thisReader["VendorName"].ToString(); ;
}

thisReader.Close();
conn.Close();

}

[WebMethod]
public ProductVendorInfo GetProductVendorDetails(int ProductID)
{

GetProductDetails(ProductID);

ProductVendorInfo pvi = new ProductVendorInfo();
pvi.ProductID = pvinfo.ProductID;
pvi.ProductName = pvinfo.ProductName;
pvi.ProductNumber = pvinfo.ProductNumber;
pvi.VendorAccountNumber = pvinfo.VendorAccountNumber;
pvi.VendorName = pvinfo.VendorName;

return pvi;



}

My CLR Procedure code is as follows€¦.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using CLRWebServiceProject.LocalWebService;

public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void CallWebService(int ProductID)
{
// Put your code here


ProductVendorInfoService S = new ProductVendorInfoService();
S.UseDefaultCredentials = true;

ProductVendorInfo pvi = new ProductVendorInfo();

pvi = S.GetProductVendorDetails(ProductID);


String PN = pvi.ProductName;
String PNum = pvi.ProductNumber;
String VANum = pvi.VendorAccountNumber;
String VN = pvi.VendorName;

using (SqlConnection cn = new SqlConnection("context connection=true"))
{
string query = "INSERT INTO dbo.ProductVendorDetail(ProdcutID,ProductName,ProductNumber,VendorAcccountNumber,VendorName)"
+" VALUES ('"+ProductID+","+PN+","+PNum+","+VANum+","+VN+"')";

using (SqlCommand insertCommand = new SqlCommand(query, cn))
{
cn.Open();
insertCommand.ExecuteNonQuery();
cn.Close();
}
}


}


};


Can you help what exactly this error relates/ pointing to? Am i doing any mistake while creating the procedure?




Thanks
Raj

View 9 Replies View Related

Calling External Webservice Using Certificate

Apr 28, 2006

I'm trying to call an external webservice which use https and certificate. I load certificate into MSIE and then made *.cer file. The problem is that if I compile simple console program which use this certificate - than all is OK. But when I compile simple rutine for SQL server and trying to run it - an exception is issued after this line:

service.ClientCertificates.Add(X509Certificate.CreateFromCertFile(certFile));

Exception is:

Request for the permission of type 'System.Security.Permissions.FileIOPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.


Do you have any advices?

MiSu

View 1 Replies View Related

Stored Procedure Failing

Nov 24, 2004

This stored procedure is failing with an error of
Cannot resolve collation conflict for equal to operation.
CREATE PROCEDURE myProcedure
@Customer_Name varchar(56)='',@Billing_Platform int=NULL,@Channel varchar(32)=NULL,@Sector varchar(5)=NULL,
@Segment_ID varchar(2000)=NULL,@Entity varchar(2000)=NULL,@Account_Number varchar(2000)=NULL,@Sale_ID varchar(2000)=NULL,
@Invoice_Debt decimal(17)=NULL,@Invoice_Days int=NULL,@Account_Debt decimal(17)=NULL
AS

SET NOCOUNT ON;

declare @substr varchar(50),@multAccount bit, @Pos int
set @substr = @Customer_Name + '%'
set @Pos = 0
set @multAccount=0

If Len(@Account_Number) > 0 --comma seperated list of accounts
Begin
set @multAccount = 1 --Flag to store the fact that there are Account selections
End

CREATE TABLE #TempAccount
(
AccountNo varchar(20) COLLATE Latin1_General_CI_AS NOT NULL
)

If @multAccount=1--strip out the segments from the comma seperated input string into a temporary table
Begin

declare @AccountNo varchar(20)
set @Account_Number = Ltrim(Rtrim(@Account_Number))+ ','
set @Pos = Charindex(',', @Account_Number, 1)

If Replace(@Account_Number, ',', '') <> ''
Begin
While @Pos > 0
Begin
set @AccountNo = Ltrim(Rtrim(Left(@Account_Number, @Pos - 1)))
If @AccountNo <> ''
Begin
INSERT INTO #TempAccount (AccountNo) VALUES (CAST(@AccountNo As varchar(20)))

End
set @Account_Number = Right(@Account_Number, Len(@Account_Number) - @Pos)
set @Pos = Charindex(',', @Account_Number, 1)

End
End
End

SELECT ENTITY_NM, ENTITY_ID, SUM(RANGE01) AS RANGE01, SUM(RANGE02) AS RANGE02, SUM(RANGE03) AS RANGE03, SUM(RANGE04) AS RANGE04, SUM(RANGE05) AS RANGE05, SUM(RANGE06) AS RANGE06, SUM(DEBT90) AS DEBT90 ,MIN(MINDEBT) AS MINDEBT ,MAX(MAXDEBT) AS MAXDEBT ,MIN(MINOVER) AS MINOVER ,MAX(MAXOVER) AS MAXOVER ,SUM(ACCDEBT) AS ACCDEBT
FROM myTable
WHERE ENTITY_NM like @substr
AND SYS_ID = COALESCE(@Billing_Platform,SYS_ID)
AND CHNL_ID = COALESCE(@Channel,CHNL_ID)
AND SECT_ID = COALESCE(@Sector,SECT_ID)
AND ENTITY_DEBT >= COALESCE(@Invoice_Debt,ENTITY_DEBT)
AND MAXOVER >= COALESCE(@Invoice_Days,MAXOVER)
AND ACCDEBT >= COALESCE(@Account_Debt,ACCDEBT)
AND ((@multAccount=1 AND (ACC_NO IN (select AccountNo from #TempAccount))) OR (@multAccount=0 AND ACC_NO Like '%'))
GROUP BY ENTITY_ID, ENTITY_NM
ORDER BY DEBT90 DESC
GO
Can anyone tell me what could be wrong?
This has worked before and it appears that a server setting may have changed (although I don't know what).

View 3 Replies View Related

(Could Not Find Stored Procedure ''.) When Calling A User Defined Procedure

Feb 4, 2008

Hi,I'm tring to call a stored procedure i'v made from a DNN module, via .net control.When I try to execute this sql statement: EXEC my_proc_name 'prm_1', 'prm_2', ... the system displays this error: Could not find stored procedure ''. (including the trailings [".] chars :)I've tried to run the EXEC statement from SqlServerManagement Studio, and seems to works fine, but sometimes it displays the same error. So i've added the dbname and dbowner as prefix to my procedure name in the exec statement and then in SqlSrv ManStudio ALWAYS works, but in dnn it NEVER worked... Why? I think it could be a db permission problem but i'm not able to fix this trouble, since i'm not a db specialist and i don't know which contraint could give this problem. Also i've set to the ASPNET user the execute permissions for my procedure... nothing changes :( Shoud someone could help me? Note that I'm using a SqlDataSource object running the statement with the select() method (and by setting the appropriate SelectCommandType = SqlDataSourceCommandType.StoredProcedure ) and I'm using the 2005 sql server express Thank in advance,(/d    

View 3 Replies View Related

Integration Services :: Calling Webservice In SSIS Through Script Task

Jun 29, 2015

I am searching for a solution for Calling or consume a web service in SSIS through Script task. I have gone through so many links but i am able to find the exact solution. I am getting so many references, though i am unable to crack it.

My requirement is i need to call a web service URL through script task which is having a client certificate. When we are trying to connect to the URL it will ask for the certificate authentication. After calling this URL we will get a WSDL file from the web service, We need to consume that WSDL file and we need to identify the methods inside this WSDL and need to write the data available in this WSDL to the data base tables.

How can we call that web service URL( With certificate) through script task and how can we read the WSDL file and How we can load the data into DB table.

View 8 Replies View Related

Calling Stored Procedure Fromanother Stored Procedure

Oct 10, 2006

Hi,I am getting error when I try to call a stored procedure from another. I would appreciate if someone could give some example.My first Stored Procedure has the following input output parameters:ALTER PROCEDURE dbo.FixedCharges @InvoiceNo int,@InvoiceDate smalldatetime,@TotalOut decimal(8,2) outputAS .... I have tried using the following statement to call it from another stored procedure within the same SQLExpress database. It is giving me error near CALL.CALL FixedCharges (@InvoiceNo,@InvoiceDate,@TotalOut )Many thanks in advanceJames

View 16 Replies View Related

Calling A Stored Procedure Or Function From Another Stored Procedure

Mar 2, 2007

Hello people,

When I am trying to call a function I made from a stored procedure of my creation as well I am getting:

Running [dbo].[DeleteSetByTime].

Cannot find either column "dbo" or the user-defined function or aggregate "dbo.TTLValue", or the name is ambiguous.

No rows affected.

(0 row(s) returned)

@RETURN_VALUE =

Finished running [dbo].[DeleteSetByTime].

This is my function:

ALTER FUNCTION dbo.TTLValue

(

)

RETURNS TABLE

AS

RETURN SELECT Settings.TTL FROM Settings WHERE Enabled='true'

This is my stored procedure:

ALTER PROCEDURE dbo.DeleteSetByTime

AS

BEGIN



SET NOCOUNT ON



DECLARE @TTL int

SET @TTL = dbo.TTLValue()



DELETE FROM SetValues WHERE CreatedTime > dateadd(minute, @TTL, CreatedTime)

END

CreatedTime is a datetime column and TTL is an integer column.

I tried calling it by dbo.TTLValue(), dbo.MyDatabase.TTLValue(), [dbo].[MyDatabase].[TTLValue]() and TTLValue(). The last returned an error when saving it "'TTLValue' is not a recognized built-in function name". Can anybody tell me how to call this function from my stored procedure? Also, if anybody knows of a good book or site with tutorials on how to become a pro in T-SQL I will appreciate it.

Your help is much appreciated.

View 6 Replies View Related

Calling A Stored Procedure

Mar 23, 2007

Hi, i've had this query method:
34           public void AddDagVerslagCategorie(int logID, HistoriekDetail historiekDetail)35           {36               SqlConnection oConn = new SqlConnection(_connectionString);37               string strSql = "Insert into LogDetail (LogID, CategorieID, Inhoud)";38               strSql += "values(@logID, @categorieID, @inhoud)";39               SqlCommand oCmd = new SqlCommand(strSql, oConn);40               oCmd.Parameters.Add(new SqlParameter("@logID", SqlDbType.Int)).Value = logID;41               oCmd.Parameters.Add(new SqlParameter("@categorieID", SqlDbType.Int)).Value = historiekDetail.CategorieID;42               oCmd.Parameters.Add(new SqlParameter("@inhoud", SqlDbType.VarChar, 100)).Value = historiekDetail.Inhoud;43   44               try45               {46                   oConn.Open();47                   int rowsAffected = oCmd.ExecuteNonQuery();48                   if (rowsAffected == 0) throw new ApplicationException("Fout toevoegen historiek detail");49                   oCmd.CommandText = "select @@IDENTITY";50                   oCmd.Parameters.Clear();51                   historiekDetail.HistoriekDetailID = (int)(decimal)oCmd.ExecuteScalar();52               }53               catch (Exception ex)54               {55                   throw new ApplicationException("Fout toevoegen historiek detail: " + ex.Message);56               }57               finally58               {59                   if (oConn.State == ConnectionState.Open) oConn.Close();60               }61           }
which i've converted to a stored procedure: 1 ALTER PROCEDURE [dbo].[insert_DagVerslagDetail]
2 -- Add the parameters for the stored procedure here
3 @dagverslagdetailID int,
4 @logID int,
5 @categorieID int,
6 @inhoud varchar(100)
7 AS
8 BEGIN
9 -- SET NOCOUNT ON added to prevent extra result sets from
10 -- interfering with SELECT statements.
11 SET NOCOUNT ON;
12 SET @dagverslagdetailID = SCOPE_IDENTITY()
13
14 -- Insert statements for procedure here
15 BEGIN TRANSACTION
16 INSERT LogDetail (LogID, CategorieID, Inhoud)
17 VALUES(@logID, @categorieID, @inhoud)
18 COMMIT TRANSACTION
19 END
  
 
Now i would like to call that stored procedure in my previous method, so i've changed it to this:
 1 public void AddDagVerslagCategorie(int logID, HistoriekDetail historiekDetail)
2 {
3 SqlConnection oConn = new SqlConnection(_connectionString);
4 string strSql = "insert_DagVerslagDetail";
5 strSql += "values(@logID, @categorieID, @inhoud)";
6 SqlCommand oCmd = new SqlCommand(strSql, oConn);
7 oCmd.CommandType = CommandType.StoredProcedure;
8 oCmd.Parameters.Add(new SqlParameter("@logID", SqlDbType.Int)).Value = logID;
9 oCmd.Parameters.Add(new SqlParameter("@categorieID", SqlDbType.Int)).Value = historiekDetail.CategorieID;
10 oCmd.Parameters.Add(new SqlParameter("@inhoud", SqlDbType.VarChar, 100)).Value = historiekDetail.Inhoud;
11
12 try
13 {
14 oConn.Open();
15 int rowsAffected = oCmd.ExecuteNonQuery();
16 if (rowsAffected == 0) throw new ApplicationException("Fout toevoegen historiek detail");
17 oCmd.CommandText = "select @@IDENTITY";
18 oCmd.Parameters.Clear();
19 historiekDetail.HistoriekDetailID = (int)(decimal)oCmd.ExecuteScalar();
20 }
21 catch (Exception ex)
22 {
23 throw new ApplicationException("Fout toevoegen historiek detail: " + ex.Message);
24 }
25 finally
26 {
27 if (oConn.State == ConnectionState.Open) oConn.Close();
28 }
29 }

 
Do i still need the lines 17                   oCmd.CommandText = "select @@IDENTITY";
                                 19                   historiekDetail.HistoriekDetailID = (int)(decimal)oCmd.ExecuteScalar();
Because i've declared the identity in my stored procedure

View 1 Replies View Related

Out Vs. Ref When Calling Stored Procedure

Apr 8, 2008

Hi, I have a stored procedure, and it is expecting an output. If I declared the passing varaible as ref, it compiles fine, but it is not returning any value. If I pass the varaible as out, and add the paramater
MyComm.Parameters.Add(new SqlParameter("@ReturnValue", returnValue));  it gives the following error. 
Compiler Error Message: CS0269: Use of unassigned out parameter 'quoteID'.
 And if I don't supply the previous statement, the following error occurs.
System.Data.SqlClient.SqlException: Procedure 'CreateData' expects parameter '@ReturnValue', which was not supplied.
How Can I fix this? thanks.

View 2 Replies View Related

Calling A DTS Stored Procedure From ASP.NET

Dec 15, 2003

I have a stored procedure that calls a DTS package to grab a text file that has been uploaded to the server and merge it with a table on the database. The DTS package works woderfully in SQL, as does the the file upload. The problem arrises when I create a stored procedure to run the DTS package. I know that you have to shell out and do a command line on the SQL server (and I think that I got the syntax correct) but its calling the Stored Procedure in the ASP.NET app that is causing me hardship. Here is the code that I have so far:

Stored Procedure:

CREATE PROCEDURE spSampleData AS exec master..xp_cmdshell 'dtsrun /SZEUSsqlServer113 /NdtsPackage /UuserID /Ppassword'
GO


VB to run DTS:

Dim myCommand As SqlCommand
myCommand.CommandType = CommandType.StoredProcedure
myCommand.CommandText = "spSampleData"
myCommand.ExecuteNonQuery()


I'm not sure what I am doing wrong but any help would be great.

Thanks!

View 3 Replies View Related

Calling Stored Procedure

Feb 6, 2004

I am trying to set up a call to a Stored Procedure to do an Insert. Here is my code snippet:

<%@ Page Language="VB" %>
<%@ import Namespace="System" %>
<%@ import Namespace="System.Data.SqlClient" %>
.
.
.
Dim loConn as New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
Dim cmdInsert as New SQLCommand("AdminUser_Insert", loConn)
cmdInsert.CommandType = CommandType.StoredProcedure

Dim InsertForm As New SqlDataAdapter()
InsertForm.InsertCommand = cmdInsert

cmdInsert.Parameters.Add(New SqlParameter("@RETURN_VALUE", SqlDbType.bigint, 8, "Account_Number"))
cmdInsert.Parameters("@RETURN_VALUE").Direction = ParameterDirection.ReturnValue
cmdInsert.Parameters.Add(New SqlParameter("@UserID", SqlDbType.varchar, 50, "UserID"))
cmdInsert.Parameters("@UserID").Value = Request("UserID")
cmdInsert.Parameters.Add(New SqlParameter("@Password", SqlDbType.varchar, 32, "Password"))
cmdInsert.Parameters("@Password").Value = Request("Password")
cmdInsert.Parameters.Add(New SqlParameter("@First_Name", SqlDbType.varchar, 32, "First_Name"))
cmdInsert.Parameters("@First_Name").Value = Request("FirstName")
cmdInsert.Parameters.Add(New SqlParameter("@Middle_Name", SqlDbType.varchar, 32, "Middle_Name"))
cmdInsert.Parameters("@Middle_Name").Value = Request("MiddleName")
cmdInsert.Parameters.Add(New SqlParameter("@Last_Name", SqlDbType.varchar, 32, "Last_Name"))
cmdInsert.Parameters("@Last_Name").Value = Request("LastName")

loConn.Open()
command.ExecuteNonQuery()
loConn.Close()


I get the following error:

Compilation Error
Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately.

Compiler Error Message: BC30451: Name 'CommandType' is not declared.


This error happens on the line: cmdInsert.CommandType = CommandType.StoredProcedure

Any help would be appreciated,
Greg

View 2 Replies View Related

Calling DTS From Stored Procedure

May 22, 2000

Hello everybody,

How can I call DTS from stored procedure, any help pls.

PS: I appreciate if you please give me an example.

Thank you....

View 2 Replies View Related

Calling A Job From A Stored Procedure

Jan 19, 2001

I'm trying to call a job from a stored procedure.
To do so, I've found that sp_start_job is doing just that.
My problem is that the sp_start_job is not in my master
database stored procedures and I don't know how to add it.
I'm working with SQL Server 7 Enterprise without the SP.

Am I looking for the right thing ? (sp_start_job)
Where can I find it ?

View 1 Replies View Related

Calling Stored Procedure

Nov 3, 2005

Hey, I have a parent SP, and within that parent I want to call a a child what is the code to call that child procedure? or teh easiest way to make that happen?

View 1 Replies View Related

Calling A VB Exe From A Stored Procedure

Jan 30, 2004

Can someone tell me a straightforward way to call a VB app (that accepts command line arguments) from a stored procedure.

I have got it to work by using xp_cmdshell, but in practice, the security constraints here prevent using this. Our DBAs don't want to set the proxy account required for a non-sysadmin user to eexecute xp_cmdshell.

I know that writing an extended SP invoking a C++ dll would be the cleanest solution. However I don't have the knowledge to do that.

Any ideas?

Thanks

View 1 Replies View Related

Calling A Stored Procedure From A Dll

Apr 7, 2004

When I call a stored procedure from a dll written in Builder C++, it gets blocked. But if I call the same SP from the main program, it works fine. but I need to call SP from the dll. What's the problem?
Thanks...

View 1 Replies View Related

Calling Stored Procedure From Another Sp

Apr 29, 2008

Hi,

I am new to SQL and new to stored procedures!

What I am trying to do is call one stored procedure from another stored procedure. Very simple piece of code but can not get it to work correctly.....

This is the calling stored procedure: sp_TechRiskMandatory. It is calling a stored procedure called sp_Test.

What is happening is that it is executing the line before the "EXECUTE" command and never getting to this line.

If I put the "EXECUTE" command first it will execute this line and not get to the next.

My code is returning out of the stored procedure before finishing executing the remainder of the code....

CREATE PROCEDURE [sp_TechRisk_Mandatory]

@Conclusion varchar(100),
@TechRisk varchar(100)

AS
If (@Conclusion = 'Application/Changed') and ((@TechRisk = " ") or (@TechRisk = "N/A"))

Begin
Select "AsxErrorMessage" = "Technical Risk must be specified"
EXECUTE sp_Test

End
Else
Begin
Select "Looks Good" = " "
EXECUTE sp_Test
End
GO

This is the code for the sp_Test:

CREATE PROCEDURE sp_Test

AS

BEGIN
Select "AsxErrorMessage" = "Test"

END
GO

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved