Help Stored Procedure Working But Not Doing Anything

Jul 31, 2006

Help Stored procedure working but not doing anything New Post

Quote    Reply

Please i need some help.



I am calling a stored procedure from  asp.net and there is a
cursor in the stored procedure that does some processing on serval
tables.



if i run the stored procedure on Query Analyzer it works and does what
it is suppose to do but if i run it from my asp.net/module control it
goes. acts likes it worked but it does not do what is suppose to do.

i believe the cursor in the stroed procedure does not run where is
called programmatically from the asp.net/module control page.plus it
does not throw any errors





This is the code from my control

System.Data.SqlClient.SqlParameter [] param={new
System.Data.SqlClient.SqlParameter("@periodStart",Convert.ToDateTime(startDate)),new
System.Data.SqlClient.SqlParameter("@periodStart",Convert.ToDateTime(endDate)),new
System.Data.SqlClient.SqlParameter("@addedby",UserInfo.FullName+ "
"+UserInfo.Username)};

               
string
str=System.Configuration.ConfigurationSettings.AppSettings["payrollDS"];

               
System.Data.SqlClient.SqlConnection cn=new
System.Data.SqlClient.SqlConnection(str);

               
cn.Open();              


               
//System.Data.SqlClient.SqlTransaction trans=cn.BeginTransaction();



               
SqlHelper.ExecuteScalar(cn,System.Data.CommandType.StoredProcedure,"generatePaylistTuned",param);

      





------------------------THis is the code for my storedprocedure-------------



CREATE PROCEDURE [dbo].[generatePaylistTuned]

@periodStart datetime,

@periodEnd datetime,

@addedby varchar(40)



AS



begin transaction generatePayList



DECLARE @pensioner_id int, @dateadded datetime,

 @amountpaid float,

@currentMonthlypension float,@actionType varchar(50),

@isAlive bit,@isActive bit,@message varchar(80),@NoOfLoadedPensioners int,

@NoOfDeadPensioners int,@NoOfEnrolledPensioners int,@DeactivatedPensioners int,

@reportSummary varchar(500)



set @NoOfLoadedPensioners =0



set @NoOfDeadPensioners=0

set @NoOfEnrolledPensioners=0

set @DeactivatedPensioners=0

set @actionType ="PayList Generation"



DECLARE paylist_cursor CURSOR FORWARD_ONLY READ_ONLY FOR



select p.pensionerId,p.isAlive,p.isActive,py.currentMonthlypension

from pensioner p left outer join pensionpaypoint py on  p.pensionerid=py.pensionerId



where p.isActive = 1





OPEN paylist_cursor



FETCH NEXT FROM paylist_cursor

INTO @pensioner_id,@isAlive,@isActive,@currentMonthlypension



WHILE @@FETCH_STATUS = 0

BEGIN



set @NoOfLoadedPensioners=@NoOfLoadedPensioners+1

if(@isAlive=0)

begin

update Pensioner

set isActive=0

where pensionerid=@pensioner_id

set @DeactivatedPensioners =@@ROWCOUNT+@DeactivatedPensioners

set @NoOfDeadPensioners =@@ROWCOUNT+@NoOfDeadPensioners

end

else

begin

insert into pensionpaylist(pensionerId,dateAdded,addedBy,

periodStart,periodEnd,amountPaid)

values(@pensioner_id,getDate(),@addedby, @periodStart, @periodEnd,@currentMonthlypension)

set @NoOfEnrolledPensioners =@@ROWCOUNT+ @NoOfEnrolledPensioners

end



   -- Get the next author.

   FETCH NEXT FROM paylist_cursor

   INTO  @pensioner_id,@isAlive,@isActive,@currentMonthlypension

END



CLOSE paylist_cursor

DEALLOCATE paylist_cursor



set @reportSummary ="The No. of Pensioners Loaded:
"+Convert(varchar,@NoOfLoadedPensioners)+"<BR>"+"The No. Of
Deactivated Pensioners:
"+Convert(varchar,@DeactivatedPensioners)+"<BR>"+"The No. of
Enrolled Pensioners:
"+Convert(varchar,@NoOfEnrolledPensioners)+"<BR>"+"No Of Dead
Pensioner from Pensioners Loaded: "+Convert(varchar,@NoOfDeadPensioners)

insert into reportSummary(dateAdded,hasExceptions,periodStart,periodEnd,reportSummary,actionType)

values(getDate(),0, @periodStart, @periodEnd,@reportSummary,'Pay List Generation')



 if (@@ERROR <> 0)               

          BEGIN



insert into reportSummary(dateAdded,hasExceptions,periodStart,periodEnd,reportSummary,actionType)

values(getDate(),1, @periodStart,@periodEnd,@reportSummary,'Pay List Generation')



ROLLBACK TRANSACTION  generatePayList



          END



commit Transaction generatePayList

GO

View 5 Replies


ADVERTISEMENT

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

Set Identity Not Working For Stored Procedure

Jan 31, 2008

Hi can someone tell me whats wrong with this stored procedure. All im trying to do is get the publicationID from the publication table in order to use it for an insert statement into another table. The second table PublicaitonFile has the publicaitonID as a foriegn key.
Stored procedure error: cannot insert null into column publicationID, table PublicationFile - its obviously not getting the ID.
ALTER PROCEDURE dbo.StoredProcedureUpdateDocLocField
@publicationID Int=null,@title nvarchar(MAX)=null,@filePath nvarchar(MAX)=null
ASBEGINSET NOCOUNT ON
IF EXISTS (SELECT * FROM Publication WHERE title = @title)SELECT @publicationID = (SELECT publicationID FROM Publication WHERE title = @title)SET @publicationID = @@IDENTITYEND
IF NOT EXISTS(SELECT * FROM PublicationFiles WHERE publicationID = @publicationID)BEGININSERT INTO PublicationFile (publicationID, filePath)VALUES (@publicationID, @filePath)END
 

View 5 Replies View Related

Stored Procedure - Insert Not Working

Jun 23, 2005

Having a little trouble not seeing why this insert is not happening.... --snip--  DECLARE c_studId CURSOR FOR  SELECT studentId  FROM students FOR READ ONLY   OPEN c_studId  FETCH NEXT FROM c_studId INTO @studentId  IF( @@FETCH_STATUS = 0 )  BEGIN   SET @studRec = 'Found'  END CLOSE c_studId DEALLOCATE c_studId
 BEGIN TRAN IF (@studRec <> 'Found')  BEGIN  INSERT INTO students  (studentId)  VALUES  (@studentId)    END  Well, you get the idea, and I snipped a lot of it.Why is it not inserting if the user is not found?Thanks all,Zath

View 6 Replies View Related

Simple Stored Procedure Not Working

Jul 14, 2005

I have a SP below that authenticates users, the problem I have is that activate is of type BIT and I can set it to 1 or 0.
If I set it to 0 which is disabled, the user can still login.
Therefore I want users that have activate as 1 to be able to login and users with activate as 0 not to login

 what are mine doing wrong ?

Please help


CREATE PROCEDURE DBAuthenticate

(
  @username Varchar( 100 ),
  @password Varchar( 100 )
)
As

DECLARE @ID INT
DECLARE @actualPassword Varchar( 100 )

SELECT
  @ID = IdentityCol,
  @actualPassword = password
  FROM CandidatesAccount
  WHERE username = @username and Activate = 1

IF @ID IS NOT NULL
  IF @password = @actualPassword
    RETURN @ID
  ELSE
    RETURN - 2
ELSE
  RETURN - 1
GO

View 5 Replies View Related

Stored Procedure Stops Working

Jun 1, 1999

I have a stored procedure which does a simple select joining 3 tables.

This had been working fine for weeks and then just stopped returning any rows even though data existed for it to return.

After re-compiling the procedure, it worked fine as before.

Does anyone know of any reason why a procedure would need recompiling like this?

We have been doing data restores and also dropping/recreating tables during this development. Would any of this affect a procedure?

View 3 Replies View Related

Why Isnt My Stored Procedure Working

Oct 24, 2007

Hi there below is my code for a sproc. however when i run it, it doesnt seem to create a table











USE [dw_data]
GO
/****** Object: StoredProcedure [dbo].[usp_address] Script Date: 10/24/2007 15:33:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



CREATE PROC [dbo].[usp_address]
(
@TableType INT = null

)

AS

IF @TableType is NULL OR @TableType = 1

BEGIN

IF NOT EXISTS (SELECT 1 FROM [DW_BUILD].INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'address')

CREATE TABLE [dw_build].[dbo].[address] (
[_id] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[address_1] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[address_2] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[category] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[category_userno] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[county] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[created] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[creator] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[end_date] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[forename] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[notes] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[other_inv_link] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[out_of_district] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[packed_address] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[paf_ignore] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[paf_valid] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[patient] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[patient_date] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[pct_of_res] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[postcode] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[real_end_date] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[relationship] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[relationship_userno] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[surname] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[telephone] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[title] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[town] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[updated] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[updator] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

END

IF @TableType is NULL OR @TableType = 2

BEGIN

CREATE TABLE [dw_build].[dbo].[address_cl] (
[_id] [int] NULL,
[address_1] [varchar](40) NULL,
[address_2] [varchar](40) NULL,
[category] [varchar](7) NULL,
[category_userno] [int] NULL,
[county] [varchar](40) NULL,
[created] [datetime] NULL,
[creator] [int] NULL,
[end_date] [datetime] NULL,
[forename] [varchar](40) NULL,
[notes] [varchar](max) NULL,
[other_inv_link] [int] NULL,
[out_of_district] [bit] NOT NULL,
[packed_address] [varchar](220) NULL,
[paf_ignore] [bit] NOT NULL,
[paf_valid] [bit] NOT NULL,
[patient] [int] NULL,
[patient_date] [datetime] NULL,
[pct_of_res] [int] NULL,
[postcode] [varchar](40) NULL,
[real_end_date] [datetime] NULL,
[relationship] [varchar](7) NULL,
[relationship_userno] [int] NULL,
[surname] [varchar](40) NULL,
[telephone] [varchar](40) NULL,
[title] [varchar](40) NULL,
[town] [varchar](40) NULL,
[updated] [datetime] NULL,
[updator] [int] NULL
) ON [PRIMARY]

END

View 13 Replies View Related

Stored Procedure Range Not Working

Dec 22, 2006

Hi All,I am trying to write a basic stored procedure to return a range ofvalues but admit that I am stumped. The procedure syntax used is:ALTER PROCEDURE Rpt_LegacyPurchaseOrderSp(@StartPoNumber PONumberType = 'Null',@FinishPoNumber PONumberType = 'Null')ASSET @StartPoNumber = 'PO_NUMBER_POMSTR'SET @FinishPoNumber = 'PO_NUMBER_POMSTR'SELECTIPPOMST_SID,--Start tbl_IPPOMSTPO_NUMBER_POMSTR,VENDOR_NUMBER_POMSTR,SHIP_NUMBER_POMSTR,CHANGE_ORDER_POMSTR,FOB_POINT_POMSTR,ROUTING_POMSTR,DATE_ISSUED_POMSTR,DATE_LAST_RECPT_POMSTR,CONTACT_PERSON_1_POMSTR,PREPAID_COLLECT_POMSTR,TERMS_POMSTR,AMOUNT_ESTIMATED_POMSTR,AMOUNT_RECEIVED_POMSTR,AMOUNT_PAID_POMSTR,LOCATION_CODE_POMSTR,SHIPPING_POINT_POMSTR,PRINT_IND_POMSTR,BUYER_POMSTR,SHIPMENT_POMSTR,STATUS_POMSTR,CURRENCY_POMSTR,CURRENCY_STATUS_POMSTR,AMOUNT_EST_CUR_POMSTR,AMOUNT_REC_CUR_POMSTR,AMOUNT_PAID_CUR_POMSTR,--Finish tbl_IPPOMSTIPPOITM_SID,--Start tbl_IPPOITMPO_NUMBER_POITEM,ITEM_NUMBER_POITEM,CATEGORY_POITEM,DESCRIPTION_POITEM,VENDOR_NUMBER_POITEM,DATE_ORIGINAL,DATE_RESCHEDULED,ACCOUNT_NUMBER_POITEM,STOCK_NUMBER_POITEM,JOB_NUMBER_POITEM,RELEASE_WO_POITEM,QUANTITY_ORDERED_POITEM,QUANTITY_RECVD_POITEM,UOM_POITEM,UNIT_WEIGHT_POITEM,UNIT_COST_POITEM,EXTENDED_TOTAL_POITEM,MATERIAL_NUMBER_POITEM,COMPLETE_POITEM,LOCATION_CODE_POITEM,INSPECTION_POITEM,BOM_ITEM_POITEM,COST_ACCOUNT_POITEM,CHANGE_ORDER_POITEM,TAX_CODE_POITEM,ISSUE_CODE_POITEM,QUANTITY_INSPECT_POITEM,EXC_RATE_CURR_POITEM,UNIT_COST_CURR_POITEM,EXTENDED_TOTAL_CURR_POITEM,PLANNER_POITEM,BUYER_POITEM--Finish tbl_IPPOITMIPVENDM_SID,--Start tbl_IPVENDMVENDOR_NUMBER_VENMSTR,VENDOR_NAME_VENMSTR,ADDRESS_LINE_1_VENMSTR,ADDRESS_LINE_2_VENMSTR,ADDRESS_LINE_3_VENMSTR,CITY_VENMSTR,STATE_VENMSTR,ZIP_CODE_VENMSTR,COUNTRY_VENMSTR--Finish tbl_IPVENDMFROM tbl_IPPOMSTJOIN tbl_IPPOITMON tbl_IPPOITM.PO_NUMBER_POITEM = tbl_IPPOMST.PO_NUMBER_POMSTRJOIN tbl_IPVENDMon tbl_IPVENDM.VENDOR_NUMBER_VENMSTR = tbl_IPPOMST.VENDOR_NUMBER_POMSTRWHERE tbl_IPPOMST.PO_NUMBER_POMSTR >= @StartPoNumber ANDtbl_IPPOMST.PO_NUMBER_POMSTR <= @FinishPoNumberBasically, no rows are returned for the valid (records in database)range I enter. I have been troubleshopoting the syntax. This hasinvolved commenting out references to @FinishPoNumber so in effect Ijust pass in a valid PO Number using @StartPoNumber parameter. Thisworks in terms of returning all 76545 PO records.Can anyone help me to identify why this syntax will not return a rangeof PO records that fall between @StartPoNumber and @FinishPoNumber?Any help would be greatly appreciated.Many Thanks*rohan* & Merry Christmas!

View 2 Replies View Related

Update Stored Procedure Not Working When Called From C#

Jul 11, 2007

OK, I have been raking my brains with this and no solution yet. I simply want to update a field in a table given the record Id. When I try the SQL in standalone (no sp) it works and the field gets updated. When I do it by executing the stored procedure from a query window in the Express 2005 manager it works well too. When I use the stored procedure from C# then it does not work:
 1. ExecuteNonQuery() always returns -1 2. When retrieving the @RETURN_VALUE parameter I get -2, meaning that the SP did not find a matching record.
So, with #1 there is definitely something wrong as I would expect ExecuteNonQuery to return something meaningful and with #2 definitely strange as I am able to execute the same SQL code with those parameters from the manager and get the expected results.
Here is my code (some parts left out for brevity):1 int result = 0;
2 if (!String.IsNullOrEmpty(icaoCode))
3 {
4 icaoCode = icaoCode.Trim().ToUpper();
5 try
6 {
7 SqlCommand cmd = new SqlCommand(storedProcedureName);(StoredProcedure.ChangeAirportName);
8 cmd.Parameters.Add("@Icao", SqlDbType.Char, 4).Value = newName;
9 cmd.Parameters.Add("@AirportName", SqlDbType.NVarChar, 50).Value = (String.IsNullOrEmpty(newName) ? null : newName);
10 cmd.Parameters.Add("@RETURN_VALUE", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
11 cmd.Connection = mConnection; // connection has been opened already, not shown here
12 cmd.CommandType = CommandType.StoredProcedure;
13 int retval = cmd.ExecuteNonQuery(); // returns -1 somehow even when RETURN n is != -1
14 result = (int)cmd.Parameters["@RETURN_VALUE"].Value;
15
16 }
17 catch (Exception ex)
18 {
19 result = -1;
20 }
21 }

 And this is the stored procedure invoked by the code above:1 ALTER PROCEDURE [dbo].[ChangeAirfieldName]
2 -- Add the parameters for the stored procedure here
3 @Id bigint = null,-- Airport Id, OR
4 @Icao char(4) = null,-- ICAO code
5 @AirportName nvarchar(50)
6 AS
7 BEGIN
8 -- SET NOCOUNT ON added to prevent extra result sets from
9 -- interfering with SELECT statements.
10 SET NOCOUNT ON;
11
12 -- Parameter checking
13 IF @Id IS NULL AND @Icao IS NULL
14 BEGIN
15 RETURN -1;-- Did not specify which record to change
16 END
17 -- Get Id if not known given the ICAO code
18 IF @Id IS NULL
19 BEGIN
20 SET @Id = (SELECT [Id] FROM [dbo].[Airports] WHERE [Icao] = @Icao);
21 --PRINT @id
22 IF @Id IS NULL
23 BEGIN
24 RETURN -2;-- No airport found with that ICAO Id
25 END
26 END
27 -- Update record
28 UPDATE [dbo].[Airfields] SET [Name] = @AirportName WHERE [Id] = @Id;
29 RETURN @@ROWCOUNT
30 END

 As I said when I execute standalone UPDATE works fine, but when approaching it via C# it returns -2 (did not find Id).

View 2 Replies View Related

Working With An Integer Array In A Stored Procedure

May 4, 2006

hi there,

i need a procedure that works with C# e.g.:

using (SqlCommand cmd = GetCommand("Procedure_Name"))

{

//i=an array of integer values

cmd.Parameters.Add("@array", SqlDbType.????!?!???).Value = i;

cmd.ExecuteScalar();

}

i need to write a stored procedure that takes as input an array of integers (amongst other values)

this procedure must loop through every integer in the array and INSERT a new record into a table.

i have never used T-SQL before.

Many thanks

View 3 Replies View Related

Executing A Job From A Stored Procedure Stopped Working

Apr 29, 2008



I've got a stored procedure in database A that calls the sp_start_job stored procedure in msdb as follows:

CREATE PROCEDURE xxxxx
WITH EXECUTE AS 'domainusername'

AS

EXEC msdb.dbo.sp_start_job B'jobname' ;

RETURN

The domainusername is the in the database sysadmin role and the owner of the job. To make this work originally, I had to change the msdb database to be trusted.

This worked for the past several months.

Now it doesn't work (perhaps after a reboot but not sure). The error I get is "The EXECUTE permission was denied on the object 'sp_start_job', database 'msdb', schema 'dbo'

I looked to make sure that the account had grant execute rights and it does. I tried setting it via GRANT statement and it was granted successfully yet the error still occurs. I've tried changing accounts and anything else I can think of to no avail.

Any ideas how to troubleshoot this issue. I've tried all the tricks I can think of.

Thanks - SM

View 3 Replies View Related

Stored Procedure Where Clause Not Working Properly

May 24, 2008

I am having a problem with this stored procedure. I'm using SQL Server 2005 Developer's edition and if I execute the procedure in a query window, I get no errors. Also, when the script runs from a website call there are no errors. The problem is that it doesn't return the information that is in the database. It is supposed to return the orders from Washington state between such and such dates. The orders are there in the database, so I think the where clause must be wrong.

Thanks for the help.

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CommerceLibOrdersGetWashingtonState]') AND type in (N'P', N'PC'))

BEGIN

EXEC dbo.sp_executesql @statement = N'-- =============================================

-- Author: <Author,,Name>

-- Create date: <Create Date,,>

-- Description: <Description,,>

-- =============================================

CREATE PROCEDURE [dbo].[CommerceLibOrdersGetWashingtonState]

(@ShippingStateProvince VARCHAR(50),

@ShippingCountry VARCHAR(50),

@StartDate smalldatetime,

@EndDate smalldatetime)

AS

SELECT OrderID,


DateCreated,

DateShipped,

Comments,

Status,

CustomerID,

AuthCode,

Reference,
ShippingCounty,

ShippingStateProvince,

ShippingCountry,

ShippingID,

TaxID,

ShippingAmount,

TaxAmount

FROM Orders

WHERE (DateCreated BETWEEN @StartDate AND @EndDate)

AND (ShippingStateProvince = @ShippingStateProvince)

AND (ShippingCountry = @ShippingCountry)

ORDER BY DateCreated DESC'

END

View 4 Replies View Related

Error In Stored Procedure While Working With Temp. Table

May 31, 2007

Creating a temporary table in stored procedure and using a sql query to insert the data in temp. table.I am facing the error as :
String or binary data would be truncated.The statement has been terminated.
The procedure i created is as :
ALTER PROCEDURE fetchpersondetails
AS
CREATE Table #tempperson (personID int,FirstName nvarchar(200),LastName nvarchar(250),title nvarchar(150),Profession nvarchar(200),StreetAddress nvarchar(300),
StateAddress nvarchar(200),CityAddress nvarchar(200),CountryAddress nvarchar(200),ZipAddress nvarchar(200),Telephone nvarchar(200),Mobile nvarchar(200),
Fax nvarchar(200),Email nvarchar(250),NotesPub ntext,Affiliation nvarchar(200),Category nvarchar(200))
 
Insert into #tempperson
SELECT dbo.tblperson.personID, ISNULL(dbo.tblperson.fName, N'') + ' ' + ISNULL(dbo.tblperson.mName, N'') AS FirstName, dbo.tblperson.lname AS LastName,
dbo.tblperson.honor AS Title, dbo.tblperson.title AS Profession, dbo.tblperson.street + ' ' + ISNULL(dbo.tblperson.suite, N'') AS StreetAddress,
dbo.tblperson.city AS cityaddress, dbo.tblperson.state AS stateaddress, dbo.tblperson.postalCode AS zipaddress,
dbo.tblperson.Phone1 + ',' + ISNULL(dbo.tblperson.Phone2, N'') + ',' + ISNULL(dbo.tblperson.Phone3, N'') AS Telephone,
dbo.tblperson.mobilePhone AS mobile, dbo.tblperson.officeFax + ',' + ISNULL(dbo.tblperson.altOfficeFax, N'') + ',' + ISNULL(dbo.tblperson.altOfficeFax2,
N'') AS Fax, ISNULL(dbo.tblperson.Email1, N'') + ',' + ISNULL(dbo.tblperson.Email2, N'') + ',' + ISNULL(dbo.tblperson.Email3, N'') AS Email,
dbo.tblperson.notes AS NotesPub, dbo.tblOrganizations.orgName AS Affiliation, dbo.tblOrganizations.orgCategory AS Category,
dbo.tblCountry.countryNameFull AS countryaddress
FROM dbo.tblperson INNER JOIN
dbo.tblOrganizations ON dbo.tblperson.orgID = dbo.tblOrganizations.orgID INNER JOIN
dbo.tblCountry ON dbo.tblperson.countryCode = dbo.tblCountry.ISOCode
 
please let me know the solurion of this error. 
 

View 2 Replies View Related

Stored Procedure Edit Process Not Working In VS2003 C# .Net

Aug 21, 2005

I am trying to follow an exercise for debugging a stored procedure in the .net IDE. I have had success in adding a connection to the application, until I get the point of where I should set a BreakPoint in the stored procedure "Ten Most Expensive Products" which belongs to the Northwind database. When I open Sever Explorer -->Data Connections- ->WI0001.NorthWind.dbo-->Stored Procedures and Right-click the procedure, the context menu does not give me an edit item to select to open this SP in the editor. Nor can I double-click on the stored procedure to open it either.

View 1 Replies View Related

Stored Procedure Loop Not Working, Please Advise, Code Attached

Apr 13, 2008

This is my first attempt at a loop in a stored procedure, and it is not working, but the rest of the sp works fine. Could anyone please provide me with some feedback. I am not sure if I need to execute the loop first or all the sp at once ? Thanks so much.CREATE PROCEDURE Table_1TT_1T                    (@PartNo                 varchar(20),                 @Wkorder        varchar(10),                 @Setup        datetime,                 @Line        smallint,                 @TT        integer,                 @Tester        smallint,                 @LT1        integer,                 @LT2        integer,                 @LT3        integer,                 @LT4        integer,                 @LT5        integer,                 @LT6        integer,                 @LT7        integer,                 @LT8        integer,                 @LT9        integer,                 @LT10        integer,                 @LT11        integer,                 @LT12        integer,                 @LT13        integer,                 @LT14        integer,                 @LT15        integer,                 @LT16        integer,                 @LT17        integer,                 @LT18        integer,                 @LT19        integer,                 @LT20        integer,                 @LT21        integer,                 @LT22        integer,                 @LT23        integer,                 @LT24        integer,                 @LT25        integer,                 @LT26        integer,                 @LT27        integer,                 @LT28        integer,                 @LT29        integer,                 @LT30        integer,                 @LT31        integer,                 @LT32        integer,                 @LT33        integer,                 @LT34        integer,                 @LT35        integer,                 @LT36        integer,                 @UnitFound        integer        OUT,                         @parameters_LamType         varchar(50)       OUT,                 @parameters_Shunt        real               OUT,                 @parameters_ShuType     varchar(50)       OUT,                 @parameters_Stack        real              OUT,                 @parameters_Steel          varchar(50)       OUT,                 @Partno11            varchar(20)    OUT,                 @Wkorder11            varchar(10)    OUT,                 @Partno12            varchar(20)    OUT,                 @Wkorder12            varchar(10)    OUT,                 @Partno24            varchar(20)    OUT,                 @Wkorder24            varchar(10)    OUT,                 @Partno29            varchar(20)    OUT,                 @Wkorder29            varchar(10)    OUT,                 @Partno34            varchar(20)    OUT,                 @Wkorder34            varchar(10)    OUT,                 --@DL1        integer        OUT,                 --@DL2        integer        OUT,                 --@DL3        integer        OUT,                 --@DL4        integer        OUT,                 --@DL5        integer        OUT,                 --@DL6        integer        OUT,                 --@DL7        integer        OUT,                 --@DL8        integer        OUT,                 --@DL9        integer        OUT,                 --@DL10        integer        OUT,                 @DL11        integer        OUT,                 @DL12        integer        OUT,                 --@DL13        integer        OUT,                 --@DL14        integer        OUT,                 --@DL15        integer        OUT,                 --@DL16        integer        OUT,                 --@DL17        integer        OUT,                 --@DL18        integer        OUT,                 --@DL19        integer        OUT,                 --@DL20        integer        OUT,                 --@DL21        integer        OUT,                 --@DL22        integer        OUT,                 --@DL23        integer        OUT,                 @DL24        integer        OUT,                 --@DL25        integer        OUT,                 --@DL26        integer        OUT,                 --@DL27        integer        OUT,                 --@DL28        integer        OUT,                 @DL29        integer        OUT,                 --@DL30        integer        OUT,                 --@DL31        integer        OUT,                 --@DL32        integer        OUT,                 --@DL33        integer        OUT,                 @DL34        integer        OUT)                 --@DL35        integer        OUT,                 --@DL36        integer        OUT)ASSET @Tester = 1WHILE @Tester < 36      BEGIN    Set @Line = (Select Line from dbo.location where Tester = @Tester)        IF @Line = 453        BEGIN        If @Tester = 1 BEGIN SET @LT1 = 453 END        If @Tester = 2 BEGIN SET @LT2 = 453 END        If @Tester = 3 BEGIN SET @LT3 = 453 END        If @Tester = 4 BEGIN SET @LT4 = 453 END        If @Tester = 5 BEGIN SET @LT5 = 453 END        If @Tester = 6 BEGIN SET @LT6 = 453 END        If @Tester = 7 BEGIN SET @LT7 = 453 END        If @Tester = 8 BEGIN SET @LT8 = 453 END        If @Tester = 9 BEGIN SET @LT9 = 453 END        If @Tester = 10 BEGIN SET @LT10 = 453 END        If @Tester = 11 BEGIN SET @LT11 = 453 END        If @Tester = 12 BEGIN SET @LT12 = 453 END        If @Tester = 13 BEGIN SET @LT13 = 453 END        If @Tester = 14 BEGIN SET @LT14 = 453 END        If @Tester = 15 BEGIN SET @LT15 = 453 END        If @Tester = 16 BEGIN SET @LT16 = 453 END        If @Tester = 17 BEGIN SET @LT17 = 453 END        If @Tester = 18 BEGIN SET @LT18 = 453 END        If @Tester = 19 BEGIN SET @LT19 = 453 END        If @Tester = 20 BEGIN SET @LT20 = 453 END        If @Tester = 21 BEGIN SET @LT21 = 453 END        If @Tester = 22 BEGIN SET @LT22 = 453 END        If @Tester = 23 BEGIN SET @LT23 = 453 END        If @Tester = 24 BEGIN SET @LT24 = 453 END        If @Tester = 25 BEGIN SET @LT25 = 453 END        If @Tester = 26 BEGIN SET @LT26 = 453 END        If @Tester = 27 BEGIN SET @LT27 = 453 END        If @Tester = 28 BEGIN SET @LT28 = 453 END        If @Tester = 29 BEGIN SET @LT29 = 453 END        If @Tester = 30 BEGIN SET @LT30 = 453 END        If @Tester = 31 BEGIN SET @LT31 = 453 END        If @Tester = 32 BEGIN SET @LT32 = 453 END        If @Tester = 33 BEGIN SET @LT33 = 453 END        If @Tester = 34 BEGIN SET @LT34 = 453 END        If @Tester = 35 BEGIN SET @LT35 = 453 END        END        SET @Tester = @Tester + 1      ENDSELECT       @parameters_LAMTYPE = LAMTYPE,       @parameters_SHUNT = SHUNT,       @parameters_SHUTYPE = SHUTYPE,       @parameters_STACK = STACK,       @parameters_STEEL = STEEL    FROM DBO.PARAMETERS A    INNER JOIN .DBO.XREF B ON A.PARTNO = B.XREF    WHERE B.PARTNO = @PARTNO    SET @UnitFound = @@rowcountIF @UnitFound = 0    BEGIN            SELECT               @parameters_LAMTYPE = LAMTYPE,               @parameters_SHUNT = SHUNT,               @parameters_SHUTYPE = SHUTYPE,               @parameters_STACK = STACK,               @parameters_STEEL = STEEL            FROM DBO.PARAMETERS            WHERE PARTNO = @PARTNO            SET @UnitFound = @@rowcount            END        --IF @LT1 = @Line  BEGIN SET @DL1 = 1 END        --IF @LT2 = @Line  BEGIN SET @DL2 = 1 END        --IF @LT3 = @Line  BEGIN SET @DL3 = 1 END        --IF @LT4 = @Line  BEGIN SET @DL4 = 1 END        --IF @LT5 = @Line  BEGIN SET @DL5 = 1 END        --IF @LT6 = @Line  BEGIN SET @DL6 = 1 END        --IF @LT7 = @Line  BEGIN SET @DL7 = 1 END        --IF @LT8 = @Line  BEGIN SET @DL8 = 1 END        --IF @LT9 = @Line  BEGIN SET @DL9 = 1 END        --IF @LT10 = @Line  BEGIN SET @DL10 = 1 END        IF @LT11 = 453  BEGIN SET @Partno11 = @Partno SET @Wkorder11 = @Wkorder SET @DL11 = 1 END        --IF @LT11 = @Line  BEGIN SET @DL11 = 1 END        IF @LT12 = 453  BEGIN SET @Partno12 = @Partno SET @Wkorder12 = @Wkorder SET @DL12 = 1 END        --IF @LT13 = @Line  BEGIN SET @DL13 = 1 END        --IF @LT14 = @Line  BEGIN SET @DL14 = 1 END        --IF @LT15 = @Line  BEGIN SET @DL15 = 1 END        --IF @LT16 = @Line  BEGIN SET @DL16 = 1 END        --IF @LT17 = @Line  BEGIN SET @DL17 = 1 END        --IF @LT18 = @Line  BEGIN SET @DL18 = 1 END        --IF @LT19 = @Line  BEGIN SET @DL19 = 1 END        --IF @LT20 = @Line  BEGIN SET @DL20 = 1 END        --IF @LT21 = @Line  BEGIN SET @DL21 = 1 END        --IF @LT22 = @Line  BEGIN SET @DL22 = 1 END        --IF @LT23 = @Line  BEGIN SET @DL23 = 1 END        IF @LT24 = 453  BEGIN SET @Partno24 = @Partno SET @Wkorder24 = @Wkorder SET @DL24 = 1 END        --IF @LT25 = @Line  BEGIN SET @DL25 = 1 END        --IF @LT26 = @Line  BEGIN SET @DL26 = 1 END        --IF @LT27 = @Line  BEGIN SET @DL27 = 1 END        --IF @LT28 = @Line  BEGIN SET @DL28 = 1 END        IF @LT29 = 453  BEGIN SET @Partno29 = @Partno SET @Wkorder29 = @Wkorder SET @DL29 = 1 END        --IF @LT30 = @Line  BEGIN SET @DL30 = 1 END        --IF @LT31 = @Line  BEGIN SET @DL31 = 1 END        --IF @LT32 = @Line  BEGIN SET @DL32 = 1 END        --IF @LT33 = @Line  BEGIN SET @DL33 = 1 END        IF @LT34 = 453  BEGIN SET @Partno34 = @Partno SET @Wkorder34 = @Wkorder SET @DL34 = 1 END        --IF @LT35 = @Line  BEGIN SET @DL35 = 1 END        --IF @LT36 = @Line  BEGIN SET @DL36 = 1 ENDGO

View 1 Replies View Related

Stored Procedure Version Of SELECT Statement - Can't Understand Why It's Not Working

Apr 25, 2006

Hi,
[I'm using VWD Express (ASP.NET 2.0)]
Please help me understand why the following code, containing an inline SQL SELECT query (in bold) works, while the one after it (using a Stored Procedure) doesn't:

<asp:DropDownList ID="ddlContacts" runat="server" AutoPostBack="True" DataSourceID="SqlDataSource2"

DataTextField="ContactNameNumber" DataValueField="ContactID" Font-Names="Tahoma"
Font-Size="10pt" OnDataBound="ddlContacts_DataBound" OnSelectedIndexChanged="ddlContacts_SelectedIndexChanged" Width="218px">
</asp:DropDownList>&nbsp;<asp:Button ID="btnImport" runat="server" Text="Import" />
<asp:Button ID="Button2" runat="server" OnClick="btnNewAccount_Click" Text="New" />
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:ICLConnectionString %>"
SelectCommand="SELECT Contacts.ContactID, Contacts.ContactLastName + ', ' + Contacts.ContactFirstName + ' - ' + Contacts.ContactNumber AS ContactNameNumber FROM Contacts INNER JOIN AccountContactLink ON Contacts.ContactID = AccountContactLink.ContactID WHERE (AccountContactLink.AccountID = @AccountID) ORDER BY ContactNameNumber">
<SelectParameters>
<asp:ControlParameter ControlID="ddlAccounts" Name="AccountID" PropertyName="SelectedValue" />
</SelectParameters>
</asp:SqlDataSource>

View 3 Replies View Related

Working In A Stored Procedure's Result Set In MSSQL 2000 (eg.: Selecting From)

Mar 4, 2004

Hy all.
My main goal would be to create some kind of map of the database, wich contains information of all connecting fields, including wich fields has key references to the other one and wich table. I'd like to have a table wich shows all the database connections tablename, field, field/table_key (from or to the key points), field_key_type (prymary or foreign)
So I thaugh to get sp_fkeys and sp_pkeys from master table and inner joining their results, but I simplay cannot "catch" their result sets. The script must have been written in SQL.
Obviously I'd like to do this:
SELECT * FROM EXEC sp_fkeys @table_name = 'xy'
of course it is not working this way, but I'd like something like this.


Please help! Thanx!

View 3 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

T-SQL (SS2K8) :: One Stored Procedure Return Data (select Statement) Into Another Stored Procedure

Nov 14, 2014

I am new to work on Sql server,

I have One Stored procedure Sp_Process1, it's returns no of columns dynamically.

Now the Question is i wanted to get the "Sp_Process1" procedure return data into Temporary table in another procedure or some thing.

View 1 Replies View Related

SQL Server 2014 :: Embed Parameter In Name Of Stored Procedure Called From Within Another Stored Procedure?

Jan 29, 2015

I have some code that I need to run every quarter. I have many that are similar to this one so I wanted to input two parameters rather than searching and replacing the values. I have another stored procedure that's executed from this one that I will also parameter-ize. The problem I'm having is in embedding a parameter in the name of the called procedure (exec statement at the end of the code). I tried it as I'm showing and it errored. I tried googling but I couldn't find anything related to this. Maybe I just don't have the right keywords. what is the syntax?

CREATE PROCEDURE [dbo].[runDMQ3_2014LDLComplete]
@QQ_YYYY char(7),
@YYYYQQ char(8)
AS
begin
SET NOCOUNT ON;
select [provider group],provider, NPI, [01-Total Patients with DM], [02-Total DM Patients with LDL],

[Code] ....

View 9 Replies View Related

Connect To Oracle Stored Procedure From SQL Server Stored Procedure...and Vice Versa.

Sep 19, 2006

I have a requirement to execute an Oracle procedure from within an SQL Server procedure and vice versa.

How do I do that? Articles, code samples, etc???

View 1 Replies View Related

Grab IDENTITY From Called Stored Procedure For Use In Second Stored Procedure In ASP.NET Page

Dec 28, 2005

I have a sub that passes values from my form to my stored procedure.  The stored procedure passes back an @@IDENTITY but I'm not sure how to grab that in my asp page and then pass that to my next called procedure from my aspx page.  Here's where I'm stuck:    Public Sub InsertOrder()        Conn.Open()        cmd = New SqlCommand("Add_NewOrder", Conn)        cmd.CommandType = CommandType.StoredProcedure        ' pass customer info to stored proc        cmd.Parameters.Add("@FirstName", txtFName.Text)        cmd.Parameters.Add("@LastName", txtLName.Text)        cmd.Parameters.Add("@AddressLine1", txtStreet.Text)        cmd.Parameters.Add("@CityID", dropdown_city.SelectedValue)        cmd.Parameters.Add("@Zip", intZip.Text)        cmd.Parameters.Add("@EmailPrefix", txtEmailPre.Text)        cmd.Parameters.Add("@EmailSuffix", txtEmailSuf.Text)        cmd.Parameters.Add("@PhoneAreaCode", txtPhoneArea.Text)        cmd.Parameters.Add("@PhonePrefix", txtPhonePre.Text)        cmd.Parameters.Add("@PhoneSuffix", txtPhoneSuf.Text)        ' pass order info to stored proc        cmd.Parameters.Add("@NumberOfPeopleID", dropdown_people.SelectedValue)        cmd.Parameters.Add("@BeanOptionID", dropdown_beans.SelectedValue)        cmd.Parameters.Add("@TortillaOptionID", dropdown_tortilla.SelectedValue)        'Session.Add("FirstName", txtFName.Text)        cmd.ExecuteNonQuery()        cmd = New SqlCommand("Add_EntreeItems", Conn)        cmd.CommandType = CommandType.StoredProcedure        cmd.Parameters.Add("@CateringOrderID", get identity from previous stored proc)   <-------------------------        Dim li As ListItem        Dim p As SqlParameter = cmd.Parameters.Add("@EntreeID", Data.SqlDbType.VarChar)        For Each li In chbxl_entrees.Items            If li.Selected Then                p.Value = li.Value                cmd.ExecuteNonQuery()            End If        Next        Conn.Close()I want to somehow grab the @CateringOrderID that was created as an end product of my first called stored procedure (Add_NewOrder)  and pass that to my second stored procedure (Add_EntreeItems)

View 9 Replies View Related

SQL Server 2012 :: Executing Dynamic Stored Procedure From A Stored Procedure?

Sep 26, 2014

I have a stored procedure and in that I will be calling a stored procedure. Now, based on the parameter value I will get stored procedure name to be executed. how to execute dynamic sp in a stored rocedure

at present it is like EXECUTE usp_print_list_full @ID, @TNumber, @ErrMsg OUTPUT

I want to do like EXECUTE @SpName @ID, @TNumber, @ErrMsg OUTPUT

View 3 Replies View Related

System Stored Procedure Call From Within My Database Stored Procedure

Mar 28, 2007

I have a stored procedure that calls a msdb stored procedure internally. I granted the login execute rights on the outer sproc but it still vomits when it tries to execute the inner. Says I don't have the privileges, which makes sense.

How can I grant permissions to a login to execute msdb.dbo.sp_update_schedule()? Or is there a way I can impersonate the sysadmin user for the call by using Execute As sysadmin some how?

Thanks in advance

View 9 Replies View Related

Ad Hoc Query Vs Stored Procedure Performance Vs DTS Execution Of Stored Procedure

Jan 23, 2008



Has anyone encountered cases in which a proc executed by DTS has the following behavior:
1) underperforms the same proc when executed in DTS as opposed to SQL Server Managemet Studio
2) underperforms an ad-hoc version of the same query (UPDATE) executed in SQL Server Managemet Studio

What could explain this?

Obviously,

All three scenarios are executed against the same database and hit the exact same tables and indices.

Query plans show that one step, a Clustered Index Seek, consumes most of the resources (57%) and for that the estimated rows = 1 and actual rows is 10 of 1000's time higher. (~ 23000).

The DTS execution effectively never finishes even after many hours (10+)
The Stored procedure execution will finish in 6 minutes (executed after the update ad-hoc query)
The Update ad-hoc query will finish in 2 minutes

View 1 Replies View Related

Procedure Not Working

Dec 1, 2005

EDIT:

Hey guys sorry for posting in the wrong forum.

Thanks for the links.

View 5 Replies View Related

Remote Procedure Is Not Working

Jul 28, 1999

Hi We have SQL6.5 servers with serpack3. I have registered the remote sql servers on both sqlservers. I have login name as same and trusted.(by the way I did this through GUI)
when I run a remote procedure it comes and tell me
**********************************************

DB-Library: Unexpected EOF from SQL Server. General network error. Check your documentation.
Net-Library error 10054: ConnectionCheckForData ((null)()).

DB-Library Process Dead - Connection Broken

*************************************************
???????
in some servers I did the same configuration it works fine.
All the servers are configured Uniqly. Protocols and dblib were same.

Can anyone Please...Please>>please explain how to configure this or is there any other way to execute a remote procedure.......?????
I really, infact really appreciate any help regarding this.
Thanks a lot.

Mini

View 1 Replies View Related

Alter Procedure Not Working

May 20, 2008


we use alter statements for our stored procedures when rolling out a new version of our app. We have noticed that when viewing the stored proc in production it looks like the altered one, but when the app runs it, the changes are not taking effect. Has any one else experienced anything like this? It's like the old version is still being held on too.

To get around the problem we have been dropping and recreating the procedures.

Any help would be appreciated. Thanks!

View 1 Replies View Related

User 'Unknown User' Could Not Execute Stored Procedure - Debugging Stored Procedure Using Visual Studio .net

Sep 13, 2007

Hi all,



I am trying to debug stored procedure using visual studio. I right click on connection and checked 'Allow SQL/CLR debugging' .. the store procedure is not local and is on sql server.



Whenever I tried to right click stored procedure and select step into store procedure> i get following error



"User 'Unknown user' could not execute stored procedure 'master.dbo.sp_enable_sql_debug' on SQL server XXXXX. Click Help for more information"



I am not sure what needs to be done on sql server side



We tried to search for sp_enable_sql_debug but I could not find this stored procedure under master.

Some web page I came accross says that "I must have an administratorial rights to debug" but I am not sure what does that mean?



Please advise..

Thank You

View 3 Replies View Related

Parameter Store Procedure Not Working

Aug 9, 2006

I have this select statement:
SELECT * FROM [enews] WHERE ([name_nws] LIKE '%" + strSearch + "%' OR
[title_nws] LIKE '%" + strSearch + "%'OR [sub_nws] LIKE '%" + strSearch
+ "%' OR [sum_nws] LIKE '%" + strSearch + "%' OR [content_nws] LIKE '%"
+ strSearch + "%')

This statement works fine but when I turned it into a store procedure,
it returns nothing. I created the store procedure as follows:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        <Author,,Name>
-- Create date: <Create Date,,>
-- Description:    <Description,,>
-- =============================================
CREATE PROCEDURE sqlSearch
    -- Add the parameters for the stored procedure here
    @strSearch varchar(250)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    WHERE ([name_nws] LIKE '%" + @strSearch + "%' OR
[title_nws] LIKE '%" + @strSearch + "%'OR [sub_nws] LIKE '%" +
@strSearch + "%' OR [sum_nws] LIKE '%" + @strSearch + "%' OR
[content_nws] LIKE '%" + @strSearch + "%')
END
GO

Help is appreciated.

View 1 Replies View Related

OUTPUT Paramater From Stocked Procedure Sent To SDS Not Working

Mar 16, 2007

Juste have fun with the following, if you can manage this out your my savior, been wasting far too much time on it, tried a couple of method and none ever worked. Conversion from type 'DBNull' to type 'String' is not valid.



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.InvalidCastException: Conversion from type 'DBNull' to type 'String' is not valid.Source Error:




Line 105:Line 106: Protected Sub sdsProprActionAJO_Inserted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles sdsProprActionAJO.InsertedLine 107: Dim MessageSysteme As String = CType((CType(e.Command.Parameters("SQL_MSG"), IDbDataParameter)).Value, String)Line 108: End SubLine 109:          With sdsProprActionAJO            .ConnectionString = ConfigurationManager.AppSettings("DB_CS_MSSQL")            .InsertCommandType = SqlDataSourceCommandType.StoredProcedure            .InsertCommand = "dbo.sp_adm_cartListe"            .InsertParameters.Clear()            .InsertParameters.Add("Mode", TypeCode.String, "SEL")            .InsertParameters.Add("LPR_Groupe", TypeCode.String, CBool(CType(Me.pnlProprActionAJO.FindControl("rblProprActionAJOGroupe"), RadioButtonList).SelectedValue))            .InsertParameters.Add("LPR_Etiquette", TypeCode.String, CType(Me.pnlProprActionAJO.FindControl("tbProprActionAJOEtiquette"), TextBox).Text)            .Insert()        End With    End Sub    Protected Sub sdsProprActionAJO_Inserted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles sdsProprActionAJO.Inserted        Dim MessageSysteme As String = CType((CType(e.Command.Parameters("SQL_MSG"), IDbDataParameter)).Value, String)    End Sub    Protected Sub sdsProprActionAJO_Inserting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles sdsProprActionAJO.Inserting        Dim Param As System.Data.SqlClient.SqlParameter = New System.Data.SqlClient.SqlParameter()        param.DbType = DbType.String        param.Direction = ParameterDirection.Output        param.ParameterName = "SQL_MSG"        e.Command.Parameters.Add(Param)    End Sub CREATE PROCEDURE [dbo].[sp_Adm_Proprietes_AJO](@MODE        VARCHAR(3),@LPR_Groupe        BIT = NULL,@LPR_Etiquette    NVARCHAR(32) = NULL,@RPP_LPR_IdParent    [INT] = NULL,@RPP_LPR_IdEnfant    [INT] = NULL,@SQL_MSG        NVARCHAR(8)  OUTPUT)AS--DECLARE @SQL_TEMP NVARCHAR(4000)DECLARE @INT_TEMP [INT]--IF @MODE = 'RRP'BEGIN    INSERT INTO t__REL__PropParent_PropEnfant (        RPP_LPR_IdParent,        RPP_LPR_IdEnfant    ) VALUES (        @RPP_LPR_IdParent,        @RPP_LPR_IdEnfant    )END--IF @MODE = 'LPR'BEGIN    IF NOT @LPR_Etiquette = NULL        BEGIN        SELECT * FROM t_Lib_Proprietes  WHERE (LPR_Groupe = @LPR_Groupe ) AND (LPR_Etiquette = @LPR_Etiquette )            IF @@ROWCOUNT <= 0        BEGIN            INSERT INTO t_Lib_Proprietes (                LPR_Groupe,                LPR_Etiquette            ) VALUES (                @LPR_Groupe,                @LPR_Etiquette            )            SET @SQL_MSG = 'CON_000A'        END        ELSE        BEGIN            SET @SQL_MSG = 'ERR_000A'        END    END    ELSE    BEGIN        SET @SQL_MSG = 'ERR_003A'    ENDENDRETURN @SQL_MSGGO 

View 8 Replies View Related

SQL Store Procedure Problem Like Operator Not Working

Nov 16, 2001

Dear Friends,

I Creat a store procedure and i used the like operator but i cant get the correct result can any one help me...

My Store Procedure Code
---------------------------------

Creat Proc [myrecods]
@name nvarchar[200]
AS
Select * from Customers
Where name LIKE '%@name%'

and i execute this proc through this line

exec myrecords 'ina'

but cant received corret result any help.

Thanks
Inayat.

View 3 Replies View Related

Is The Transaction Context Available Within A 'called' Stored Procedure For A Transaction That Was Started In Parent Stored Procedure?

Mar 31, 2008

I have  a stored procedure 'ChangeUser' in which there is a call to another stored procedure 'LogChange'. The transaction is started in 'ChangeUser'. and the last statement in the transaction is 'EXEC LogChange @p1, @p2'. My questions is if it would be correct to check in 'LogChange' the following about this transaction: 'IF @@trancount >0 BEGIN Rollback tran' END Else BEGIN Commit END.
 Any help on this would be appreciated.

View 1 Replies View Related







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