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
ADVERTISEMENT
Dec 11, 2007
i dont know if it is just because im tired or what. im trying to do a update one this table here is the stored procedure im usingALTER PROCEDURE Snake.UpdateSPlits
@name nvarchar(50),
@split nvarchar(50)
AS
Update accounts
Set split_id = @split
Where name = @name
RETURN Here is what im using to call it Protected Sub GridView1_RowUpdating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewUpdateEventArgs) Handles GridView1.RowUpdating
Me.SqlDataSource1.UpdateParameters.Clear()
Dim name As String = Me.GridView1.SelectedRow.Cells(0).Text
Dim Split As String = Me.GridView1.SelectedRow.Cells(1).Text
Dim pname As New Parameter("name", TypeCode.String, name)
Me.SqlDataSource1.UpdateParameters.Add(pname)
Dim psplit As New Parameter("split", TypeCode.String, Split)
Me.SqlDataSource1.UpdateParameters.Add(psplit)
Me.SqlDataSource1.Update()
End Sub I keep getting one of 2 errors they areObject reference not set to an instance of an object. orone that says i had to many aurgements any idea what im doing wrong?
View 7 Replies
View Related
Feb 18, 2008
hi, i am trying to insert my values into the database, however the code i have doesnt seem to work. i have looked at old posts, one suggested to take away my code behind code where the insert method has been written. i did try this but it does not seem to work, can some please help me sort out this problem and advice or examples of what i need to do will be very much appreciated, thank you. the following is the code i have
code behind code
'Save vlues into database.
If IsPostBack = False ThenDim test As SqlDataSource = New SqlDataSource()
test.ConnectionString = ConfigurationManager.ConnectionStrings("ConnectionString").ToString()
test.InsertCommand = "INSERT INTO [UserQuiz] ([QuizID], [DateTimeComplete], [CorrectAnswerCount], [UserName]) VALUES (@QuizID, @DateTimeComplete, @CorrectAnswerCount, @UserName)"test.InsertParameters.Add("QuizID", Session("QuizID").ToString())
test.InsertParameters.Add("DateTimeComplete", DateTime.Now.ToString())test.InsertParameters.Add("CorrectAnswerCount", "12")test.InsertParameters.Add("UserName", User.Identity.Name)
test.Insert()
End If
when i run the program i get this error
Cannot insert the value NULL into column 'UserQuizID', table 'C:VISUAL STUDIO 2008WEBSITESquizAPP_DATAQUIZ.MDF.dbo.UserQuiz'; column does not allow nulls. INSERT fails.The statement has been terminated.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Cannot insert the value NULL into column 'UserQuizID', table 'C:VISUAL STUDIO 2008WEBSITESquizAPP_DATAQUIZ.MDF.dbo.UserQuiz'; column does not allow nulls. INSERT fails.The statement has been terminated.Source Error:
Line 26: test.InsertParameters.Add("UserName", User.Identity.Name)
Line 27:
Line 28: test.Insert()
Line 29:
Line 30: End If
View 4 Replies
View Related
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
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
View Related
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
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
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
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
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
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
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
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
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
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
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
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
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> <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
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
Mar 8, 2007
When running practically any report in FRx (we use it to generate reports in Solomon) we are getting the following error:
FRx Reporting Engine:
01000: [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated.
This just recently started happening. Is anyone familiar with this? It doesnt give much information after that, it just will not generate the report after the message pops up.
View 2 Replies
View Related
Mar 24, 2008
hi,
i have created a webpage so that my images are stored in a database, however i have linked it to a gridview but when i go to run the page it doesnt show the actual picture saved in the database. in the gridview row it does recognise that there is a picture present but all it shows on the web page is that little x that appears when it cant view a picture, does anyone know how i can solve this problem? i will paste my code behind page and aspx page.
thanks for any advice given,
code behind pageProtected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim ds As New Data.DataSetDim da As Data.SqlClient.SqlDataAdapter
Dim strSQL As String
strSQL = "Select imgId,imgTitle from Image"Dim connString As String = (ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)da = New Data.SqlClient.SqlDataAdapter(strSQL, connString)
da.Fill(ds)
ds.Tables(0).Columns.Add("imgFile")For Each tempRow As Data.DataRow In ds.Tables(0).Rows
tempRow.Item("imgFile") = ("imgGrab.aspx?id=" & tempRow.Item("imgID"))
Next
imgGrid.DataSource = ds
imgGrid.DataBind()
End Sub
aspx page
<form id="form1" runat="server">
<div>
</div><asp:GridView ID="imgGrid" runat="server" AutoGenerateColumns="False"
Height="286px" Width="564px">
<Columns><asp:BoundField DataField="imgTitle" HeaderText="imgTitle"
SortExpression="imgTitle" />
<asp:ImageField DataImageUrlField="imgFile" HeaderText="Picture">
</asp:ImageField>
</Columns>
</asp:GridView></form>
</body>
</html>
View 13 Replies
View Related
Apr 9, 2008
Ive got an insert statement that fails, and below that I have code like the following:
IF @@ERROR <> 0
BEGIN
-- Roll back the transaction
ROLLBACK
-- Raise an error and return
RAISERROR ('Error INSERT INTO Address.', 16, 1)
print 'test was here'
RETURN
END
However, there is now rollback and the inserts below it are going through.
what do i have wrong ?
View 5 Replies
View Related
Jan 7, 2008
What does this mean and how can I fix this?
View 1 Replies
View Related
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
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
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
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
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
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
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
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
Dec 1, 2005
EDIT:
Hey guys sorry for posting in the wrong forum.
Thanks for the links.
View 5 Replies
View Related
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
