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
ADVERTISEMENT
May 2, 2008
My Alter Table below doesn't seem to be adding the column, any ideas why? I reference the column later in my TSQL and get a invalid column name error, and sure enough, when I check the table the column is missing.
-Thanks
*******************************************************
CREATE PROCEDURE [dbo].[sp_alk_populate_cust_to_pledge_tbl] AS
-- This procedure should go through each customer and populate the TEMP Table MI_Flat_Customer_Pledge by using Table mi_pledgehold.
-- Table mi_pledgehold is a replicat of the needed info from pledgehold with the addition of an actual primary key
-- Table MI_Flat_Customer_Pledge changes the one to many relationship between Customers and Pledges into a flat table for reporting
-- Table MI_Flat_Customer_Pledge will only show 10 pledges
DROP TABLE dbo.mi_pledgehold
SELECT [cust-num], [loc-id], [pledge-amt], [ticket] into [dbo].[mi_pledgehold] from pledgehold
ALTER TABLE [mi_pledgehold]
ADD [mi_pledge_id] smallint IDENTITY(1,1)PRIMARY KEY
-- Drop and Create the MI_Flat_Customer_Pledge
DROP TABLE mi_flat_customer_pledge
CREATE TABLE dbo.mi_flat_customer_pledge
(
[cust-num] int,
[pledge-amt0] numeric,
[pledge-amt1] numeric,
[pledge-amt2] numeric,
[pledge-amt3] numeric,
[pledge-amt4] numeric,
[pledge-amt5] numeric,
[pledge-amt6] numeric,
[pledge-amt7] numeric,
[pledge-amt8] numeric,
[pledge-amt9] numeric,
)
*****************************************************8
View 7 Replies
View Related
Apr 1, 2007
Keep in mind this is my first compiled SQL program Stored Procedure(SP), copied from a book by Frasier Visual C++.NET in Visual Studio2005 (Chap12). So far, so theory, except for one bug (feature?)below. At some point I'm sure I'll be able to laugh about this, akinto forgeting a semi-colon in C/C++, but right now it's frustrating(time to sleep on it for a while).Problem--For some reason I get the error when trying to save files where twotables (called Author and Content), linked by a single key, form arelationship.By simple comparison of the source code in the textbook and my program(below) I found the difference: instead of, like in the textbook, theStored Procedure (SP) starting with "CREATE PROCEDURE", it*automatically* is (was somehow) given the name of 'ALTER PROCEDURE'and I cannot change this to "CREATE PROCEDURE" (you get an error in MSVisual Studio 2005 Pro edition of "There is already an object namedXXX in the database", see *|* below). No matter what I do, the SP isalways changed by Visual Studio 2005 to 'ALTER PROCEDURE'!!!(otherwise it simply will not save)Anybody else have this happen? (See below, others have had this happenover the years but it's not clear what the workaround is)Keep in mind this is my first attempt and I have ordered somespecialized books on SQL, but if this is a common problem (and Isuspect it's some sort of bug or quirk in VS2005), please let me know.Frankly I think SQL as done by VS2005 is messed up.Here are two Usenet threads on this problem:(1) http://tinyurl.com/2o956m or,http://groups.google.com/group/micr...1454182ae77d409(2) http://tinyurl.com/2ovybv or,http://groups.google.com/group/micr...9e5428bf0525889The second thread implies this is a bug--any fix?Also this bug might be relate to the fact I've switched (and notrebooted) from Administrator to PowerUser after successfully changingthe permissions in the SQL Server Management Studio Express (see thisthread: http://tinyurl.com/2o5yqa )Regarding this problem I might try again tommorrow to see if rebootinghelps.BTW, in the event I can't get this to work, what other SQL editor/compiler should I use besides MS Visual Studio 2005 for ADO.NET andSQL dB development?RL// source files// error message:'Authors' table saved successfully'Content' table- Unable to create relationship 'FK_Content_Authors'.The ALTER TABLE statement conflicted with the FOREIGN KEY constraint"FK_Content_Authors". The conflict occurred in database "DCV_DB",table "dbo.Authors", column 'AuthorID'.// due to the below no doubt!--CREATE PROCEDURE dbo.InsertAuthor /* THIS IS CORRECT (what I want)'CREATE PROCEDURE' not 'ALTER PROCEDURE'*/(@LastName NVARCHAR(32) = NULL,@FirstName NVARCHAR(32) = NULL)AS/* SET NOCOUNT ON */INSERT INTO Authors (LastName, FirstName)VALUES(@LastName, @FirstName)RETURN--ALTER PROCEDURE dbo.InsertAuthor /* WRONG! I want 'CREATE PROCEDURE'not 'ALTER PROCEDURE' but VS2005 won't save it as such!!!*/(@LastName NVARCHAR(32) = NULL,@FirstName NVARCHAR(32) = NULL)AS/* SET NOCOUNT ON */INSERT INTO Authors (LastName, FirstName)VALUES(@LastName, @FirstName)RETURN--*|* Error message given: when trying to save CREATE PROCEDURE StoredProcedure: "There is already an object named 'InsertAuthor' in the dB
View 11 Replies
View Related
Jul 5, 2007
Hi
How can I set alter procedure rights for all procedures in a database for one user?
View 1 Replies
View Related
Nov 13, 2007
I want to use Create a procedure if not exisit and Alter it if exisit.
Can I use Create and Alter keyword together while writing a procedure in SQL Server 2005? If YES than HOW?
View 2 Replies
View Related
Dec 24, 2007
I want to alter a stored procedure from within my code base. I'm basically wanting to write an in house app that will update my stored procedures across many databases that we have. Any help would be appreciated. I'm having difficulty finding anything on this topic.
View 3 Replies
View Related
May 18, 2007
Hi all,
I use SQL2005 and I recently noticed this...
When I right click a stored procedure and select modify I get something like this
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[xxxxxx]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
xxx xxx xxx'
instead of the usual alter procedure...
I think that this happened after I installed SP2 (which I cannot remove)
Why this is happening and how can I revert it to the old way of altering stored procs?
View 2 Replies
View Related
Sep 11, 2006
Hello
As I'm not an experienced stored procedure programmer I was wondering where I need to put the BEGIN and END statement in a ALTER Stored Procedure.
As I understand it's the proc that gets altered, not the statements in the proc and that I find confusing.
I need to change the name of a column like:
UPDATE table
SET
Fielddd1 = 'test'
and I wan to change it into
UPDATE table
SET
Field1 = 'test'
Quite simple no? Forget it! Even if I hit the execute command to modify it. It get's changed back to the old code behind my back. So I guess I need to put a BEGIN and an END statement but I tried that already.
Could someonegive me a pointer?
Many thanks!
Worf
View 3 Replies
View Related
Dec 23, 2007
I have looked all around and I am having no luck trying to figure out how to alter a stored procedure within an asp.net application.Here is a short snippet of my code, but it keeps erroring out on me.Try myCommand.CommandText = "Using " & DatabaseName & vbNewLine & Me.txtStoredProcedures.Text myCommand.ExecuteNonQuery() myTran.Commit()Catch ex As Exception myTran.Rollback() Response.Write(ex.ToString())End Try The reason for this is because I have to propagate stored procedures across many databases and was hoping to write an application for it.Basically the database name is coming from a loop statement and I just want to keep on going through all the databases that I have chosen and have the stored procedure updated (altered) automatically So i thought the code above was close, but it keeps catching on me. Anybody's help would be greatly appreciated!!!
View 3 Replies
View Related
May 28, 2002
Hello.
I'm trying to create a batch sql script which first alters some existing tables via the ALTER TABLE command, I then want to alter some existing stored procedures via the ALTER PROCEDURE command within the same batch. I have found that I can encompas the alter table scripts within a conditional IF EXISTS (Begin/End) but not the alter procesdure scripts. I have looked in reference material and have found nothing to suggest this type of operation is not possible. Is this possible? Is this a know bug fixed by a service pack?
Thanks in advance for any replies.
David.
View 3 Replies
View Related
Jun 27, 2007
Is there any way to extend the Alter Procedure command or fire somekind of event when it is executed in SQL Server 2005?
Thanks,
David
View 9 Replies
View Related
May 13, 2008
Hi,
I am using SQL 2000( Version: 8.00.000)
I want to ask can I add View in the stored procedure like the following:
Create Procedure sp_createTblLogReasonColorView
@m int,
@y int
AS
BEGIN
ALTER View vw_tblLogReasonColor
AS
Select * From tblLogReasonColor where Year(n_Delivery_Date) = @y AND Month(n_Delivery_Date) = @m
END
Thanks for you reply.
View 1 Replies
View Related
Apr 27, 2006
Can the ALTER USER statement be used (without a hack like using EXEC) in a stored procedure? I know that the sp_password system stored procedure can not be. Additionally, it is being deprecated anyway. I guess what is boggling me about my attempts so far relate to the errors I am getting due to the user being specified not being in quotes in the syntax. All of the searching I have done so far have come up lame so far; the only examples I have found about it were in scripts that create other scripts for transferring users and other administrative tasks that would be run from the query window, but not from an application. To be complete as possible, here is an example of a script the returns errors:
ALTER PROC [dbo].[lbxChangePassword]
(
@loginid nvarchar(180),
@oldpassword nvarchar(40),
@newpassword nvarchar(40)
) AS BEGIN
IF @oldpassword = (SELECT password FROM contacts WHERE loginid = @loginid)
BEGIN
BEGIN TRANSACTION
UPDATE contacts
SET password = @newpassword
WHERE loginid = @loginid
ALTER LOGIN @loginid WITH PASSWORD=@newpassword OLD_PASSWORD=@oldpassword
END
ELSE
BEGIN
RAISERROR(N'The password you entered does not match your current password.', 16, 1)
RETURN
END
IF @@ERROR <> 0
BEGIN
RAISERROR(N'There was an error creating your new password.', 16, 1)
RETURN
END
COMMIT TRANSACTION
END
************
This returns:
Msg 102, Level 15, State 1, Procedure lbxChangePassword, Line 15
Incorrect syntax near '@loginid'.
Msg 319, Level 15, State 1, Procedure lbxChangePassword, Line 15
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
************
If ALTER LOGIN isn't how to change the password, then please tell me what the correct practice of changing a password is. I want to use the CURRENT_USER keyword in my queries and want I can't finish setting that up until I have this resolved because users will need to change their own passwords through the application I am developing.
View 10 Replies
View Related
Jan 28, 2008
Hi all,
I need to get a better understanding of alter procedure. If I Create a Procedure in SQL Server 2005, and want to modify that to Alter Procedure statement instead of create procedure... How do I do that??
I'm not an SQL Server person.. I dont understand why my work place needs Alter proceudre all the time but all our procedures have Alter procedure statement. Once they are created they have to be changed to Alter procedure...
Do I just need to change the keyword CREATE to ALTER?? because when I try to save it ,,, it saves it as a .sql file... Not the script.
Immediate reply would be appreciated
Thanks
View 7 Replies
View Related
Jun 26, 2001
I'm running SQL server 2000 sp1. I created a stored procedure that (1) drops a table, (2) recreates it with a "select into" statement, (3) alters the table by adding a field, and then (4) updates that field.
The trouble I'm having is that when I execute the stored procedure I get an error stating that I have an "invalid column name" between steps (2) and (3). It seems as though when I drop the table in step (1), the entire procedure wants to re-compile and it can't get past step (4) because the table hasn't been altered yet.
I've noticed a similar problem in editing stored procedures when they refer to tables or fields that don't exist yet because WITHIN the procedure they are created/modified. I'm not able to get a successful syntax check and therefore not able to save my work.
I must be missing something. Any suggestions?
View 4 Replies
View Related
Aug 10, 2015
I want that I will allow a user only to select data from any object and only to alter an existing stored procedure or view. That user can not drop and create any stored procedure and view.
I can assign that user db_datareader role, grant view definition but if I grant alter permission, that user can create, alter and drop any stored procedure and view.
View 1 Replies
View Related
Feb 5, 2015
Version 2008 R2
The stored procedure has the dependency on the table that was altered.
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 23, 2005
Hi people,I?m trying to alter a integer field to a decimal(12,4) field in MSACCESS 2K.Example:table : item_nota_fiscal_forn_setor_publicofield : qtd_mercadoria integer NOT NULLALTER TABLE item_nota_fiscal_forn_setor_publicoALTER COLUMN qtd_mercadoria decimal(12,4) NOT NULLBut, It doesn't work. A sintax error rises.I need to change that field in a Visual Basic aplication, dinamically.How can I do it? How can I create a decimal(12,4) field via script in MSACCESS?Thanks,Euler Almeida--Message posted via http://www.sqlmonster.com
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 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
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
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
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
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
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
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
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
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
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
