Send Request To Stored Procedure From A Method And Receive The Resposne Back To The Method
May 10, 2007
Hi,
I am trying to write a method which needs to call a stored procedure and then needs to get the response of the stored procedure back to the variable i declared in the method.
private string GetFromCode(string strWebVersionFromCode, string strWebVersionString)
{
//call stored procedure
}
strWebVersionFromCode = GetFromCode(strFromCode, "web_version"); // is the var which will store the response.
how should I do this?
Please assist.
I am getting SQLException when I connect one of SQL Server 2000 Integrated Security host. I would like to know how to correct this problem.
An exception occurred during the DBComms.receive method. Operation:An existing connection was forcibly closed by the remote host. Context:(1) [Thread[main,5,main], IO:adc97, Dbc:8460d]. PktNum:1. TotalReceived:173. PktSize:4,096.
I am getting SQLException when I connect SQL Server 2005. I would like to know how to correct this problem. I get this error randomly. Never seenit before. Sometimes I get this error one day later from application begins, sometimes two days later, sometimes 3 day later.
DBCP borrowObject failed: com.microsoft.sqlserver.jdbc.SQLServerException: An exception occurred during the DBComms.receive method. Operation:Connection reset. Context841) [Thread[http-8080-Processor18,5,main], IO:457d2, Dbc:1a642]. PktNum:0. TotalReceived:0. PktSize:4,096. org.apache.commons.dbcp.DbcpException: com.microsoft.sqlserver.jdbc.SQLServerException: An exception occurred during the DBComms.receive method. Operation:Connection reset. Context841) [Thread[http-8080-Processor18,5,main], IO:457d2, Dbc:1a642]. PktNum:0. TotalReceived:0. PktSize:4,096. at org.apache.commons.dbcp.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:85) at org.apache.commons.dbcp.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:184) at org.apache.commons.pool.impl.GenericObjectPool.borrowObject(Unknown Source) at org.apache.commons.dbcp.AbandonedObjectPool.borrowObject(AbandonedObjectPool.java:117) at org.apache.commons.dbcp.PoolingDataSource.getConnection(PoolingDataSource.java:110) at org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:312) at org.apache.struts.util.GenericDataSource.getConnection(GenericDataSource.java:276) at com.y.util.DbAccess.getConnection(DbAccess.java:29) at com.y.biz.UserMgr.leftDays(UserMgr.java:404) at com.y.action.LoginAction.login(LoginAction.java:194) at sun.reflect.GeneratedMethodAccessor303.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:324) at org.apache.struts.actions.DispatchAction.dispatchMethod(DispatchAction.java:280) at org.apache.struts.actions.DispatchAction.execute(DispatchAction.java:220) at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:446) at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:266) at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1292) at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:510) at javax.servlet.http.HttpServlet.service(HttpServlet.java:709) at javax.servlet.http.HttpServlet.service(HttpServlet.java:802) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:237) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:214) at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104) at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520) at org.apache.catalina.core.StandardContextValve.invokeInternal(StandardContextValve.java:198) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:152) at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104) at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:137) at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:118) at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:102) at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109) at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104) at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520) at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:929) at org.apache.coyote.tomcat5.CoyoteAdapter.service(CoyoteAdapter.java:160) at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:799) at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:705) at org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:577) at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684) at java.lang.Thread.run(Thread.java:534) Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: An exception occurred during the DBComms.receive method. Operation:Connection reset. Context841) [Thread[http-8080-Processor18,5,main], IO:457d2, Dbc:1a642]. PktNum:0. TotalReceived:0. PktSize:4,096. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(Unknown Source) at com.microsoft.sqlserver.jdbc.DBComms.receive(Unknown Source) at com.microsoft.sqlserver.jdbc.IOBuffer.sendCommand(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerConnection.changeSettings(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerConnection.logon(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(Unknown Source) at org.apache.commons.dbcp.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:83) ... 44 more
Web Server: CPU 2*1.66G, 4GB ram, 146GB Windows 2003 Server JDK 1.4.2 Tomcat 5.0 Driver Name: Microsoft SQL Server 2005 JDBC Driver
JDBC Driver Version 1.0 Use struts connection pool in application.
Database Server: CPU 2*1.66G, 4GB ram, 4 x 146GB Windows 2003 Server SQL Server 2005, Product Version: 9.00.1399
I am new to BIZTALK 2006; Please help me get started as below>> I have XML Request (send) and XML Response (receive) data; What are the steps needed for this to be accomplished in BizTalk to create webservice for this? THX
I am tring to use a stored procedure to run a function POST some data through HTTP, but failed. Here are the codes I used. Would appreciate if anyone can help me about that. Thank you very much.
Database Stored Procedure:
CREATE PROCEDURE [dbo].[USP_HTTP_POST] @URL NCHAR(255), @CONTENT NCHAR(1024), @Result bit OUTPUT AS BEGIN /***** Call Function to POST *****/ set @Result = NULL EXECUTE @Result = [dbo].[udfHttpPost] @URL, @Content END
Database Function:
CREATE FUNCTION [dbo].[udfHttpPost] (@Url [nchar](255), @Content [nchar](1024)) RETURNS [bit] WITH EXECUTE AS CALLER AS EXTERNAL NAME [SqlServices].[SqlServices.HttpServices].[HttpPost]
VC# Assembly Code
using System; using System.Configuration; using System.Collections.Generic; using System.Text; using System.Net; using System.Security; using System.Security.Permissions; using System.IO; using Microsoft.SqlServer.Server;
namespace SqlServices { public class HttpServices { [SqlFunction(DataAccess = DataAccessKind.None)] public static bool HttpPost(string Url, string Content) { Url = Url.TrimEnd(); Content = Content.TrimEnd(); string rawOutput = "";
//Get Access Right to Web WebPermission p = new WebPermission(NetworkAccess.Connect,Url); p.Assert();
StreamWriter PostWriter = new StreamWriter(Req.GetRequestStream()); PostWriter.Write(Content); PostWriter.Close();
WebResponse Resp = Req.GetResponse(); StreamReader sr = new StreamReader(Resp.GetResponseStream()); rawOutput = sr.ReadToEnd(); sr.Close(); return true; } } }
System Exception:
A .NET Framework error occurred during execution of user defined routine or aggregate 'udfHttpPost': System.Security.SecurityException: Request for the permission of type 'System.Security.Permissions.SecurityPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed. System.Security.SecurityException: at System.Security.CodeAccessSecurityEngine.CheckNReturnSO(PermissionToken permToken, CodeAccessPermission demand, StackCrawlMark& stackMark, Int32 unrestrictedOverride, Int32 create) at System.Security.CodeAccessSecurityEngine.Assert(CodeAccessPermission cap, StackCrawlMark& stackMark) at System.Security.CodeAccessPermission.Assert() at SqlServices.HttpServices.HttpPost(String Url, String Content)
Hi all,I want to create a c# method that wrap a sql stored procedure and is mantained updated in c#.Ex: Stored Procedure on db:insTableA(par varchar(255)....I want to create a class in c# like this:public class insTableA { public object Execute(String par) { // call execution of stored insTableA ................ } } Using VS 2005 e Sql Server 2005you know a method to do this and to mantain (automatically!) the c# class update when I change the stored procedure signature (ex: if change the number or datatype of parameters)??? Thanks....Luca
"The request failed with HTTP status 405: Method Not Allowed" That Error i have all the time i connect my Windows Server Sp1, MSSQL,IIS 6.0 computer by adress http://localhost/Reports when i`m trying to access the ReportServer by http://localhost/ReportServer it give me blank page with : localhost/ReportServer - /
Microsoft SQL Server Reporting Services Version 9.00.2047.00
I don`t know what to do.... i try ALL, Giving Privileges to all user, Starting Report Server Service under Administrator, Giving Privileges in DCOM utility in Event Viewer.... not any errors ALL services are working IIS Working i can connect to Web Sites which are under IIS,i tryed change Pool Identety==usless i spend 2 week trying to solve it That error killing me pleace could any one help me.... pleace send an email :/ any advice ;( in Reporting Services Log is only one message:
Code Block w3wp!ui!1!11/6/2007-19:25:23:: e ERROR: The request failed with HTTP status 405: Method Not Allowed. w3wp!ui!1!11/6/2007-19:25:24:: e ERROR: HTTP status code --> 500 -------Details-------- System.Net.WebException: The request failed with HTTP status 405: Method Not Allowed. at Microsoft.SqlServer.ReportingServices2005.RSConnection.GetSecureMethods() at Microsoft.ReportingServices.UI.Global.RSWebServiceWrapper.GetSecureMethods() at Microsoft.SqlServer.ReportingServices2005.RSConnection.IsSecureMethod(String methodname) at Microsoft.SqlServer.ReportingServices2005.RSConnection.ValidateConnection() at Microsoft.ReportingServices.UI.ReportingPage.EnsureHttpsLevel(HttpsLevel level) at Microsoft.ReportingServices.UI.ReportingPage.ReportingPage_Init(Object sender, EventArgs args) at System.EventHandler.Invoke(Object sender, EventArgs e) at System.Web.UI.Control.OnInit(EventArgs e) at System.Web.UI.Page.OnInit(EventArgs e) at System.Web.UI.Control.InitRecursive(Control namingContainer) at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) w3wp!ui!1!11/6/2007-19:25:25:: e ERROR: Exception in ShowErrorPage: System.Threading.ThreadAbortException: Thread was being aborted. at System.Threading.Thread.AbortInternal() at System.Threading.Thread.Abort(Object stateInfo) at System.Web.HttpResponse.End() at System.Web.HttpServerUtility.Transfer(String path, Boolean preserveForm) at Microsoft.ReportingServices.UI.ReportingPage.ShowErrorPage(String errMsg) at at System.Threading.Thread.AbortInternal() at System.Threading.Thread.Abort(Object stateInfo) at System.Web.HttpResponse.End() at System.Web.HttpServerUtility.Transfer(String path, Boolean preserveForm) at Microsoft.ReportingServices.UI.ReportingPage.ShowErrorPage(String errMsg)
One of my MSSQL instances show me : The report server is not responding. Verify that the report server is running and can be accessed from this computer. But all services are working
Hi all, I am using C# for ASP.NEt 2003. I would like to know if there is any easy method to update a database with about 100 fields in it. At present, I pass all the values of the controls on the web form to the stored procedure as parameters like :- myCommand.Parameters.Add("@CustomerID", SqlDbType.Int).Value = txtCustomerID.text Like this, I add all 100 parameters. Is there any easy method to do it using a class or any other methods? Thanking you in advance, Tomy
Hi, I just have a Dataset with my tables and thats it I have a grid view with several datas on it no problem to get the data or insert but as soon as I try to delete or update some records the local machine through the same error Unable to find nongeneric method... I've try to create an Update query into my table adapters but still not working with this one Also, try to remove the original_{0} and got the same error... Please help if anyone has a solution
How can i read in my C# client application values returned from sqlpipe send(string)/send(reader method from CLR stored procedure.....following is example of a stored procedure.I want to read datetime string in my C# client
I am wondering if we could back up the databases to any place outside of the local server system? As I found, we can only back up the database to the local server system, so we have needs to share databases on network places. Is there any method to back up the database on network place rather than first of all I have to back up the database on a local server system, then copy it to the network place, that just sounds really inconvenient.
Thanks a lot in advance for your help and I am looking forward to hearing from you shortly.
I understand that SQL Service Broker will RECEIVE messages in the same order of SEND, so long as the messages are on the same conversation.
I would like to accomplish queue-level ordering instead of (or in addition to) conversation-level ordering.
There is a significant business case for this level of ordering. Consider an order processing system which is specified to fulfill orders in the sequence they are received. The reason for the ordering is as follows. Suppose the process(es) that RECEIVEs from the queue is down for several hours and the messages back up in the queue. Various customers place orders throughout this period of time. If more orders are placed than there are quantity for an item, the customers who placed their orders earliest in the day ought to be the ones that receive the merchandise and the later orders should be placed on backorder.
Limited experimentation showed that SQL Server totally disregards the order in which the messages were sent (on different conversations to the same queue).
The potential solution to use the same conversation has some drawbacks:
1. Difficult to do error handling because of the way error handling works in conversations.
2. It is not possible to RECEIVE using multiple threads. (Yes, RECEIVEing on multiple threads also would reorder the messages, but the reorderings would be localized in time so this would be tolerable by a lot of applications. In other words, orders from 1:00 AM would not be mixed with orders from 9:00 AM.)
Can anyone see a good solution to this in the current version?
If there are no good solutions, it would be great if Microsoft considers adding a feature where you can declare an ordering requirement when you CREATE QUEUE. Even support for an "approximate" ordering (messages can rearrange +/- several seconds) would be helpful, but the current way which seems to totally randomize the message order is not good for certain kinds of significant applications.
hi alli have a web application on a web server.and another windows application on another computer.there are databases on each of them.how can i send and receive data between these two applications?please reply me as soon as possibleregards
Hi All,I have to write a stored procedure which will send/Receive text filefrom/to a server by using FTP.Is anybody have done anything on it? or know about it.If yes, I would like to know about it. (also provide the storedprocedure code if possible)Thanks in Adv.T.S.Negi
I am new to SSIS. I need to read the files in a remote server folder and ftp them to the other remote server folder.
I know , I have to set up FTP task and FTP task must use a variable to provide the path information. But I am not sure how I can do this. Anyone suggest me with the script task to populate the variable and steps involved in this.
I am using SQL2005.There si a field called"EXPDATE". I need a query that shows the table info, if the date that is exist on "EXPDATE" is greater than today. In summary How to write a code that if EXPDATE> "today (I do not know what to put instead of today)" then show the contents of date
Hi i am trying to insert the value of my Request.Querystring into my stored procedure, but i am having trouble with it, how would i insert the id as a parameter which is expected from the stored procedure this is what i have doen so far;string strID = Request.QueryString["id"]; SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["streamConnectionString"].ConnectionString);SqlCommand comm = new SqlCommand("stream_PersonnelDetails", conn);comm.CommandType = CommandType.StoredProcedure; conn.Open();SqlDataReader reader = comm.ExecuteReader(CommandBehavior.CloseConnection); DataList1.DataSource = reader; DataList1.DataBind(); conn.Close();
Hi,Say I have a stored procedure which does two INSERT operation.How can I check if the first INSERT succeeded in order to know if Ishould carry on and do the second one ?Regards
I want to know if it is possible to create and execute a http request in SQL Server. I want to run the request in the database as alot of the data in the url would be retrieved from the database.
I want to create a stored procedure that runs the request. The stored procedure must be called from a trigger on a table.
The reason I want to go this way is because it would be alot faster that to get the data from the database and then execute the http request in the application. Another reason that I want it done on the database side is because the internet connection of my client is not as fast as the one I am using for development so I don't want the pages to load to many times and get the data between the server and the client computer through as fast as possible.
is this the best and fastest method to connect to a database for DataGrid databind?
--------- Dim MyConnection As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("ConnectionStringSQL")) Dim MyCommand As SqlCommand = New SqlCommand("sp_BuddiesPendingSelect1", MyConnection) MyCommand.CommandType = CommandType.StoredProcedure MyCommand.Parameters.Add(New SqlParameter("@UserID", intUserID))
MyConnection.Open() Dim dr As SqlDataReader = MyCommand.ExecuteReader()
DataList1.DataSource = dr DataList1.DataBind()
dr.Close() MyConnection.Close()
----------------------- or should i use a DataAdapter and fill a DataSet and then attach the DataSet to the DataGrid?
I have been using VB6 for a long time now and had no problems using ADODB.Recordset.I had a module to which I would send my recordset (byref) and the SQL command (byval) and use the resultant recordset for adding, modifying or deleting records.How can I do the same with VS2005? This is a major problem as there are dataadapters, grids, datareaders etc.Does someone have a simple method to get the recordset so that I can modify the record using the code (eg: .Addnew/.Delete/.Update) and Close the connection?Please note that I do not need to display anything and have to run 40/50 AddModDel for every click of the program.Any help is greately appreciated. Thanks in Advance.
If you not familiar with it, the <a href="http://www.stephenforte.net/owdasblog/permalink.aspx?guid=2b0532fc-4318-4ac0-a405-15d6d813eeb8">Rozenshtein Method</a> uses SQL to create a crosstab. The concept is a stroke of genius but I'm having trouble getting it to work on one of production databases.
I successfully used the Northwind example explained at Stephen Forte's site (see the link above)...but no luck on my real world problems.
I can get the date statements to resolve to 0 correctly, but when I try to aggregate the data - the statements are turning into 1 multiplying the aggregate data for each cell...which fills in the same data across the entire row.
For example (the columns represent the time period), GROUP T1 T2 T3 T4 group1 9 9 9 9 group2 3 3 3 3 group3 5 5 5 5
My sql code is: SELECT dbo.tblHassBatch.ProdLine, COUNT((dbo.tblHassUUT.UnitID)*(1-ABS(SIGN(datediff(dd,dbo.tblHassBatch.StartTime,ge tdate())-0)))) AS Today, COUNT((dbo.tblHassUUT.UnitID)*(1-ABS(SIGN(datediff(ww,dbo.tblHassBatch.StartTime,ge tdate())-0)))) AS [This Week], COUNT((dbo.tblHassUUT.UnitID)*(1-ABS(SIGN(datediff(mm,dbo.tblHassBatch.StartTime,ge tdate())-0)))) AS [This Month], COUNT((dbo.tblHassUUT.UnitID)*(1-ABS(SIGN(datediff(mm,dbo.tblHassBatch.StartTime,ge tdate())-1)))) AS [Last Month] FROM dbo.tblHassBatch INNER JOIN dbo.tblHassUUT ON dbo.tblHassBatch.BatchID = dbo.tblHassUUT.BatchID GROUP BY dbo.tblHassBatch.ProdLine
Am I overlooking something here? I'm pulling my hair out b/c if this works it's really going to provide a great solution for a project I'm working on...but I can't seem to figure it out.
I'm not really strong in SQL. My goal is to compare the beginning mileage of a vehicle record with it's previous ending mileage reading. I have something that works, but it feels clunky. I wonder if there is a better method, ie a join. Here's what I have:
SELECT A.Trolley_num, A.Date, A.Speedo_start, A.Speedo_end, (SELECT B.Speedo_end FROM Daily_Trolley AS B WHERE B.Trolley_num = A.Trolley_num AND B.Date = (SELECT Max(Date) FROM Daily_Trolley AS C WHERE C.Trolley_num = A.Trolley_num And C.Date < '1/23/2005')) AS PrevSpeedoEnd FROM Daily_Trolley AS A WHERE A.Date='1/23/2005'
ps: I inherited this db; I'm aware that "Date" should not have been used as a field name.
'TOP 1' or 'DISTINCT' or 'MAX' Any sugestions on which is better to use if I need to select a record that has the highest value - could be a INT or sometimes a DATETIME.
Can someone please tell me how to fix this following error in SQL 2005 When trying to create a maintenance plan, Method Not Found:'Void Microsoft.SqlServer.Management.DatabaseMaintenance.TaskUIYtils..ctro()' (Mircosoft.SqlServer.MaintenancePlanTasksUI)
Is there an efficient scripting method to update the connection string for ALL reports that reside on a reporting/web server? "(automating the process, rather than having to change the data source for each individual report that resides on that server)".
I have never used replication before and I have been asked to considerit in a project I am currently working on.I have created an application for a sales team which is loaded on theirmachines, it uses ms sql as its data source and connects via theinternet back to the central server in the office.Problem is this has shown to be too slow causing time out errormessages and so on. I have been told to research the possibility ofreplication, but am unclear what type of replication to use or where tostart.Any assistance would be appreciated.Regards,Ben
Hi,Given 2 tables:Table1:id Auto,int,PrimKeytable2id inttxt nvarchar50Table2:id Auto,int,PrimKeyorder intThe scenario: Table2.order defines how the table1.txt is should be ordered.Table1.table2id contains the id of the order. This cannot be changed :(How do I select all Table1.txt values but ordered by their correspondingvalues of the table2.order field?--Thx,PipHans---Outgoing mail is certified Virus Free.Checked by AVG anti-virus system (http://www.grisoft.com).Version: 6.0.516 / Virus Database: 313 - Release Date: 01-09-2003
HiSQL Server 7.0 using stored procedures.I need to do a SELECT Top but I always need to find the record countfor the entire SELECT statement.ID Name-- ----1 Abraham20 Barrington32 Callaway54 Dennis58 EmmettIf I do a SELECT TOP 3, I'll get the required records but get arecordcount of 3.I cant use the ROWCOUNT method either becuase the recordcount willalso be 3.I can see two different options for getting back the information Ineed:1 - Run the select statement twice, first to retrieve the recordcountand then with top to get the recordset2 - Use a temp table to get the entire recordset and recordcount, andthen extract the Top N recordsI'd like to know if anyone has any other suggestions. Currently, I'mtempted to go for the second option....ThanksSam
