.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
Easy Web
Imran Ghani
Post New Web Links

Cursor problem with Stored Procedure and PHP

Posted By:      Posted Date: September 15, 2010    Points: 0   Category :Sql Server
 
I'm struggling with accessing the result set of a stored procedure using the SQL Server Driver for PHP 1.1. I've already got a few of them running, but this one is special in that it has an XML input parameter which is parsed into a table variable. This table variable is then used within a select statement. When I execute the SP within Management Studio, it runs fine and returns exactly one result set with multiple rows. But when I execute it from PHP, I get the error message "Executing SQL directly; no cursor. ". I've tried every cursor type out there, the only one that doesn't fail is SQLSRV_CURSOR_FORWARD, but with that I'm not getting any results at all. The original SP is enormous, so I have stripped it down as far as I could so it still throws the error. If I remove the bold part, my PHP code executes fine.   USE [testsite] GO SET  ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[getCrossSellingItems]     @InputIds XML AS     SET NOCOUNT ON ;     DECLARE @tblInputIds TABLE     (         Id INT     );     INSERT INTO @tblInputIds (Id)     (         SELECT         T.Item.value('@Id', 'INT') FROM @InputIds.nodes('/Root/Item') AS T(


View Complete Post


More Related Resource Links

problem for using webservice (sql parameter stored Procedure)

  
i want to put my database layer in the web service. This works fine BUT there is a problem when i am access these methods GetSPData(Byval arg As SqlComman, argCmdText as string, ByVal argSqlConnection as SqlConnection) As DataTable I want to acces the aforesaid method in my Desktop application and i have to send SqlCommand as parament. When i try to acces this (GetSPData) method the follow exception occured "There was an error generating the XML document." Now the problem is to converting DataTable in xmlDocument Or xml node (i dont know about it very much). Can anyone tell me How to send SqlCommand as parament ?? How to convert DataTable into XmlDocument?? When i try to test this method the following Error occurs " test form is only available for methods with primitive types as parameters. " The following are my Web Service Method code in which i try to Get Data Using Stored Procedure and then convert DataTable into XmlDocument and return. Dim XmlDoc As New XmlDocument Dim ds As New DataSet Dim oDBLibrary As New DataBaseLibrary.DatabaseClass Dim dt As DataTable = oDBLibrary.GetSPData(objCommand1, cmdText, msqlConnection) ds.Tables.Add(dt) Dim sw As New StringWriter ds.Tables(0).WriteXml(sw) sw.Close() Dim tableXml As String = sw.ToString XmlDoc.Load(tableXml) Return XmlDoc  Now can anyone tell me what is wrong with that c

Problem with Oracle Stored Procedure/Web Form(C#)

  

Hi!

I receive the following message when trying to insert a record into a table via Web Form(C#):

Error: Invalid operation. Connection closed.

The button event, when clicked, should connect to the database and then call the stored procedure and
insert a new record according to the text input on the Web Form. Hence, returning the new flight id (TID).

Note: Some entries can be null.

Frontend-Code:

{code}

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Add_Flight.aspx.cs" Inherits="Add_Flight" %>   <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">   <html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server"> <title></title> <style type="text/css"> .style1 { width: 89%; } .style2 { height: 23px; } .style3 { width: 360px; } .style4 { width: 137px; } .style5 { height: 23px; width: 137px; } .style6 { width: 95px; } .style7 { height: 23px; width: 95px; } .style8 { width: 200px; } .style9 { width: 129px; } </style> </head> <body> <form id="form1" runat="server"> <div> <table cellspacing="3" class="styl

Pass Multi-Value parameters to Stored Procedure problem

  

Hello

I am trying to pass multi-value parameters to stored procedure to filter data, but seems it does not work.

Stored procedure:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[test]

@StartDate DateTime,
@EndDate DateTime,
@FirstName varchar(8000),
@LastName varchar(8000),
@Location varchar(8000),

AS
BEGIN
SET NOCOUNT ON;
 
WITH cte AS
(
SELECT [Item No_],[Sales Staff],[Location Code],[Date],
[Price],[Quantity],[Item Category Code],[Product Group Code]FROM [Trans Sales Entry]
),
cte2 as
(
SELECT [ID],[First Name],[Last Name] FROM Sta

GridView delete using stored procedure problem (strange)

  

Hi!!
I'm getting an error and I can't understand why is this happening...
I have an stored procedure which receives a parameter to delete the database row corresponding to that key-parameter. In the GridView_RowCommand I'm setting up the datasource in the e.CommandName == "Delete" option     

this.SqlDataSourceGridView.DeleteCommandType = SqlDataSourceCommandType.StoredProcedure;
this.SqlDataSourceGridView.DeleteCommand = "spDeleteGridView";

Then in the GridView_RowDeleting event, I'm preparing datasourceDeleteParameters with the Key that I want to delete in my database and call the         

this.SqlDataSourceGridView.Delete ( );

The row is deleted in the database but, and here is the problem, there is something that before it got error and throws me that:

El procedure or funcion 'spDeleteGridView' waits the parameter '@PARAM', that what not expected.

I must add the information that debugging GridView_RowDeleting it ends normally and calls the GridView_RowDeleted...

Any help??

Thank you!!!


Problem with stored procedure.

  

Hi All,

am using sql server 2005,

am writing a stored procedure to which i need to pass some parameters. (like company name,minimum revenue,maximum revenue,min emplotees,max employees etc..)

in the stored procedure i need to check the parameter value

ex: i need to check if the max revenue is containing the ">" or not


But am unable to write the stored procedure with the above requirement.It is giving error


CREATE PROCEDURE CompanyAdvSrch
(
--am passing all these values to the stored procedure
    
    @companyName    VARCHAR(250),
    @CurrentPage    VARCHAR(50),
    @type            VARCHAR(50),
    @country        VARCHAR(50),
    @state            VARCHAR(50),
    @city            VARCHAR(50),
    @industry        VARCHAR(50),
    @minrevenue        VARCHAR(50),
    @minemp            VARCHAR(

My Sql Stored procedure problem

  

Dear all,

          I am in need to import the Data from .DAT file to my database.when I tried with LOAD DATA INFILE statement ,it's working fine.But my ultimate aim is to do the same with stored procedure  ,it fails and gives an error message 


Script line: 4 LOAD DATA is not allowed in stored procedures


Script line: 4 LOAD DATA is not allowed in stored procedures


This is my Stored procedure


DELIMITER $
DROP PROCEDURE IF EXISTS `paylink`.`Test` $
CREATE PROCEDURE `paylink`.`Test` ()
BEGIN
Load Data INfile 'E:\Collect.dat' into table paylink.te

Custom paging sorting stored procedure problem

  

hi.

I want to make my gridview enable for custom paging and sorting. I started to follow a tutorial and write the Stored procedure.

But my stored procedure seems not to be working. here it is:

ALTER PROCEDURE [dbo].[USP_getStudentsEmail]
(
    @sortExpression nvarchar(100),
    @startRowIndex int,
    @maximumRows int,
    @receiver nvarchar(38)
)
AS


IF LEN(@sortExpression) = 0
    SET @sortExpression = 'SendDate'


DECLARE @sql nvarchar(4000)
SET @sql = 'Select *
From
(
	Select m.Body,m.Subject,m.SendDate,m.SenderProfile,m.Receiver AS Receiver,m.ID,s.FirstName,ROW_NUMBER() OVER (ORDER BY '+ @sortExpression + ') AS RowRank 
	from Message m INNER JOIN StudentProfile s ON m.Sender=s.UserId
) AS OrderedEmails
            WHERE     m.Receiver= '+ CONVERT(nvarchar(38), @receiver) + 'AND RowRank > ' + CONVERT(nvarchar(10), @startRowIndex) +
                ' AND RowRank <= (' + CONVERT(nvarchar(10), @startRowIndex) + ' + '
                + CONVERT(nvarchar(10), @maximumRows) +')'


EXEC sp_executesql @sql


I tried to run this by the following line

exec USP_getStudentsEmail 'SendDate',1,1,'5ed19f91-eeb7-4c6a-b564-98c8dd83e18c'

but it says  

Incorrect syntax near 'd19f91'-

can anybody help

Problem with decryption data in stored procedure if local date on my machine was changed

  

Hi people!

Can anybody help me with my problem?

I have one machine (Server 2003 x64 SP2, SQL Server Standard Edition (64-bit) version 9.00.4053.00). Also I have a stored procedure in database that doing a validation of users, and contains the next code:

OPEN SYMMETRIC KEY PasswordFieldSymmetricKey
DECRYPTION BY CERTIFICATE PasswordFieldCertificate;
  SELECT
  [UserId],
  [AccountNumber],
  [Login],
  CONVERT(varchar, DecryptByKey(EncryptedPassword)) AS 'Password'
  FROM [User]
  WHERE ([Login] = @Login)
        AND (CONVERT(varchar, DecryptByKey(EncryptedPassword)) = @Password);
CLOSE SYMMETRIC KEY PasswordFieldSymmetricKey;

All works correctly but if i change the "Date and Time Properties" on my machine i receive the next error

(0 row(s) affected)
Msg 15315, Level 16, State 1, Procedure user_login, Line 111
The key 'PasswordFieldSymmetricKey' is not open. Please open the key before using it.

If i set the current date it will work correctly again.
One strange thing:
if execute the query "SELECT * FROM sys.symmetric_keys" i have the next date of creation for my symmetric keys

2010-11-04 11:18:49.300

And if i set a date less then date of

problem inserting using a stored procedure and identity column.

  

I recently moved a .net site from one machine to another, now for some reason one of the stored procedures is throwing an exception when attempting to insert! 

Exception Details: System.Data.SqlClient.SqlException: An explicit value for the identity column in table 'dbo.tbl_Events' can only be specified when a column list is used and IDENTITY_INSERT is ON

BTW, the column in question does have the identity set to Yes in management studio

I was using originally SQL 2005, now its on SQLexpress 2008

stored procedure:

ALTER

@EventID

PROCEDURE [dbo].[proc_EventsAddEdit]int,

@EventName

varchar(200),<

Execute stored procedure on linked DB2 server from MS SQL 2008 SP1 64 bit problem

  
Hi everybody. I am trying to execute stired procedure on linked DB2 server from MS SQL 2008 x64
I installed IBM Access client x64 and on provider tab showed up 3 providers IBMDASQL,IBMDA400,IBMDARLA
I installed linked server as shown on this two links:

1)http://www.mcpressonline.com/database/db2/db2-integration-with-sql-server-2005-part-i-linked-server-enhancements.html
2)http://stackoverflow.com/questions/329051/execute-db2-iseries-stored-procedure-from-a-sql-2005-linked-server

I installed linked server using all this 3 providers

sp_addlinkedserver
    @server=N'DB2',
    @srvproduct=N'DB2 UDB for iSeries',
    @provider=N'IBMDASQL',-- provider for example
    @datasrc=N'ASTEST', -- mydatasource
    @catalog='S65CF29B'
go
exec sp_addlinkedsrvlogin DB2,false,null,'telebank','password'

I can run procedure from the extended stored procedure, but when i try to execute it as shown on the linkes above i get the following error:
Could not execute statement on remote server 'DB2'.


declare
  @branch    as varchar(4),
  @cli   as varchar(6),
  @suffix    as varchar(3),
  @date1     a

Problem in Stored procedure when inserting data through openxml

  

I have stored proc where i insert some value from openxml but it is not inserting that xml data. Below is my stored proc.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[SP_MKTG_SERVICE_INSERT_NEW]
@ID AS INT,
@STATUS AS NVARCHAR(20),
@USERNAME AS NVARCHAR(50),
@DATEOFVISIT AS DATETIME,
@SUMMARY AS NVARCHAR(500),
@HOMESERVICE AS NVARCHAR(5),
@EXPIRYDATE AS DATETIME,
@SERVICELIST XML
AS
 SET NOCOUNT ON

BEGIN TRAN
IF EXISTS(SELECT * FROM NEW_DTL_SERVICE WHERE PARTNERID=@ID)
BEGIN
	DELETE FROM NEW_DTL_SERVICE WHERE PARTNERID=@ID
END

IF (@@ERROR <> 0)
BEGIN
	ROLLBACK TRAN
	RETURN
END
		DECLARE @IDOC INT
		EXEC SP_XML_PREPAREDOCUMENT @IDOC OUTPUT,@SERVICELIST
		INSERT INTO NEW_DTL_SERVICE
		(PARTNERID,[SERVICENAME],[TYPE],DISCOUNT,COMMISSION,CONDITION)
		SELECT @ID,SERVICENAME,TYPE,DISCOUNT,COMMISSION,CONDITION
		FROM OPENXML(@IDOC,'/NEWDATASET/TABLE1',2)
		WITH (SERVICENAME NVARCHAR(50),TYPE NVARCHAR(50),DISCOUNT NVARCHAR(50),COMMISSION NVARCHAR(50),CONDITION NVARCHAR(500))
		EXEC SP_XML_REMOVEDOCUMENT @IDOC
IF (@@ERROR <> 0)
BEGIN
	ROLLBACK TRAN
	RETURN
END
EXEC TIEDUPPARTNER @STATUS,@ID

IF (@@ERROR <> 0)
BEGIN
	ROLLBACK TRAN
	RETURN
END
COMMIT TRAN


and following is my xml which i generate to insert.

Send Email from SQL Server Express Using a CLR Stored Procedure

  
Problem
One of the nice things about SQL Server is the ability to send email using T-SQL. The downside is that this functionality does not exist in SQL Server Express. In this tip I will show you how to build a basic CLR stored procedure to send email messages from SQL Server Express, although this same technique could be used for any version of SQL Server.

Solution
If you have not yet built a CLR stored procedure, please refer to this tip for what needs to be done for the initial setup.

Inserting rows via stored procedure and under certain conditions

  

I'm using Dynamic Data with Entity Framework in VS2010.

Let's say my table has these fields:

MyTable
ID (PK)
PersonID (FK)
LocationID (FK)
TotalItemsSold

Hypothetical scenario (it's easier for me to explain this way, so just bear with me for now)... But let's say each row in the table represents "how many items were sold by such-and-such employee at such-and-such location," where location and person are foreign keys which are referencing other tables.  Basically, there should be no more than a ONE row which has a particular combination of Person and Location.  Makes sense?

So, when inserting new rows using my Dynamic Data app, the insert form displays editable fields for Person (dropdown), Location (dropdown), and Items Sold (textbox).  How do I prevent users from inserting another row into the table containing an already-existing combination of Person and Location?   How do I displaying useful feedback to them in the event that they DO attempt to do this?

I have several thoughts about this, but since I'm new to Dynamic Data, I'm not sure which way to go.  For example:

Option 1:  Use "cascading dropdowns" approach in the insert form and only pull in the "allowed" combinations of the two dropd

Creating A Stored Procedure Which Searches Team Names

  

Hi

I'm have on my web page a text search box which I want users to type in there favourite football team and this will display a gridview of the teams with the replica shirts I offer.

This is where I thought about creating a stored procedure to carry out this task.

I looked online for ideas but I not found anything as yet.

If anyone done anything similar to my request please let me know.



Sort by gridview SortExpression parameter via Stored Procedure

  

I have a gridview that calls data via a stored procedure.  I am unable to enable the gridview columns to be sortable. I need to set the parameter in the Stored Procedure, can someone help me with this?

Here is my gridview:

<asp:GridView ID="AllUsersGrid" runat="server" AutoGenerateColumns="False" DataKeyNames="UserName"
                        GridLines="Vertical" Width="900px" DataSourceID="SqlDataSource1" AllowSorting="True"
                        SelectedRowStyle-Height="30px" CellPadding="4" BackColor="White">
                        <Columns>
                            <asp:TemplateField HeaderText="Full Name" SortExpression="lastname">
                                <ItemTemplate>
                                    <asp:Label ID="DisplayName" runat="server" Text='<%# Eval("firstname").ToString() & " " & Eval("lastname").ToString() %>' />
                                </ItemTemplate>
                            </asp:TemplateField>
                            <asp:BoundField HeaderText="User Name" DataField="UserName" />
                            <

Stored procedure generator?

  

Hi, I am looking for a stored procedure generator with full source code(C#) compatible with Visual studio 2010. I want to create my custom stored procedure code. Please send some link. Regards, ap.


Create stored procedure from asp.net

  

Hi,

we are creating a custom report tool, which could be used for generate the report as per end user's needs. In that we are providing an option as user could create a query and procedure as well.

In sql server we can use "EXEC" function for execute dynamic query.

Could anyone help me for create the dynamic query in Oracle?

I just tried with "execute immediate", which would throws error as 

"insufficient privileges".

Please help me.

 

 


Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend