Checking Constraint In A Stored Procedure + MSSQL 7.0

May 21, 2008

Hi i have a stored procedure which deletes a row from a table.

 Sometimes the proc will break the constraints for the table.

 How can i check if the constraint is going to be broken before I try to delete so that I can avoid a nasty error message coming onto my asp.net page?

 

Thanks in advance.

Gary

View 2 Replies


ADVERTISEMENT

Stored Procedure Checking The Value

Mar 26, 2008

I have two tables called A and B and C. Where A and C has the same schema

A contains the following columns and values
-------------------------------------------
PoId Podate Approved

22008-07-07 No
42007-05-05 No
52005-08-06 Yes
62006-07-07 Yes


Table B contains the following columns and values
-------------------------------------------------
TaskId TableName Fromdate Approved_Status

1 A 7/7/2007 No
3 B 2/4/2006 Yes

Now i need to create a stored procedure that should accept the values (Yes/No) from the Approved_Status column in Table B and should look for the same values in the Approved column in the Table which is specified in the table name column in Table B. If both values match then the corresponding rows in Table A should be archived in table C which has the same schema as that of Table A. That is the matching columns should get deleted from Table A and shoud be inserted into Table C.

Pls provide me with full stored procedure code.

It is very urgent.

C.R.P RAJAN

View 4 Replies View Related

Stored Procedure Checking 2 Tables

May 12, 2004

ok, ill lay it out like this, and try not to be too confusing.

I need to check my trasaction table to see that the user with a UserID bought, keeping track(adding up) of how many times they bought the Product with a productID, , then i need to selcect items from product table with the productID for the items the user bought,


i know how to return the product information, and how to check the tranaction table and i think how to add up the amount of tranastions for that item


so bacially i need to select data from a product table, for the products that the user bought tahts stored in the transaction table.

well i tried not to be confusing, but i think i failed

View 1 Replies View Related

Stored Procedure Checking The Values In Another Table

Mar 26, 2008

I have two tables called A and B and C. Where A and C has the same schema
A contains the following columns and values-------------------------------------------PoId   Podate         Approved
2 2008-07-07  No 4 2007-05-05      No 5 2005-08-06      Yes 6 2006-07-07      Yes 
Table B contains the following columns and values-------------------------------------------------TaskId      TableName   Fromdate     Approved_Status
1                A        7/7/2007     No3                B       2/4/2006      Yes
Now i need to create a stored procedure that should accept the values (Yes/No) from the Approved_Status column in Table B and should look for the same values in the Approved  column in Table A. If both values match then the corresponding rows in Table A should be archived in table C which has the same schema as that of Table A. That is the matching columns should get deleted from Table A and shoud be inserted into Table C.
Pls provide me with full stored procedure code.
It is very urgent.

View 3 Replies View Related

Checking If A Stored Procedure Exists Before Creating It

Oct 27, 2005

I'm trying to put scripts to create our stored procedures under version control.

However I don't want these scripts to be run if the stored procedure already exists (we'll be using update scripts to alter existing stored procedure I think).

Anyway I tried :


Code:


SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

/****** Object: Stored Procedure dbo.getEnglandHotelsByATOPResort Script Date: 26/10/2005 10:40:01 ******/
if NOT EXISTS (SELECT object_id('procedureName','p'))
CREATE PROCEDURE [dbo].[procedureName]
@location char(2)
AS
...Procedure...

GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO



I know that the if NOT EXISTS ... part works as doing the following returns the expected result :


Code:


IF NOT EXISTS (SELECT object_id('procedureName','p'))
SELECT 'FALSE'
ELSE
SELECT 'TRUE'



However when trying to do this before the CREATE PROCEDURE I get :

Server: Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'PROCEDURE'.
Server: Msg 137, Level 15, State 1, Line 12
Must declare the variable '@location'.

Any ideas how to do this?

-D

View 3 Replies View Related

Checking Success Of A Request In A Stored Procedure

Jul 23, 2005

Hi,Say I have a stored procedure which does two INSERT operation.How can I check if the first INSERT succeeded in order to know if Ishould carry on and do the second one ?Regards

View 1 Replies View Related

Error Handling In Stored Procedure AND Checking

Jan 3, 2008

I am stumped on the error reporting with sql server. I was told ineed to return @SQLCode(code showing if successful or not) and@ErrMsg(and the message returned). I am clueless on this.I wrote this procedure:
Code: ( text )

View 4 Replies View Related

Catching Violation Of UNIQUE KEY Constraint In Stored Procedure

Dec 20, 2005

Dear All,

I have a stored procedure which bulk inserts records into a table based on a passed in variable that contains comma separated values of record Ids.

However I have a constraint on the table ensuring that value-pairs in 2 columns must be unique (as a person can not be twice on the same project)

Since I insert the passed in person Ids in a loop, I’d like to catch if this constraint has been violated and skip that specific cycle if it has but do commit the rest.

Not sure if this can be done, and if yes could someone let me know the SQL syntax and structure please?

Am I explaining this clearly?

Thanks in advance all comments are much appreciated!

View 2 Replies View Related

MSSQL Stored Procedure

Apr 28, 2008

Hi.
Now I am learning mssql stored procedure and I faced on some trouble.
I want to make some stored procedure following as...
CREATE PROCEDURE PeopleSearch
(
@name nvarchar(50),
@country nvarchar(50),
@city nvarchar(5),
@condition integer
 ) 
My question from here...But I don't know how to build the code with IF conditions.
IF @condition = 0 THEN
"SELECT * FROM tbl_User WHERE fname='@name'"
IF @condition = 1 THEN
....SELECT Statement..
IF @condition = 2 THEN
....SELECT Statement..
I wish to make some condition in stored procedure like above style.
@condition is not field and it come from outside(vb behind-code).
Is it possible ? Please let me know....how do i have to do...

View 5 Replies View Related

MsSQL Stored Procedure Help

Jun 22, 2008

please help for MsSQL stored procedure for ASP.net/C#.  Inside stored procedure i used 4 different statements in 'where' clause .   first one is working but last 3's are not . Statements are given below-FILENAME LIKE ''%'+ @FILENAMETEXT +'%''             -----workingFILENAME LIKE '+ @FILENAMETEXT +'%''                 -----not workingSUBSTRING(FILENAME,1,4)='+@FILENAMETEXT+'    ----not workingLEFT(FILENAME,4)='+@FILENAMETEXT+'                 -----not working bunch of code for 'where' clause are given below- DECLARE @FILENAMETEXT CHAR(4) DECLARE @CONDITION VARCHAR(2000) SET @FILENAMETEXT='http' SET @CONDITION = ' WHERE (' SET @CONDITION = @CONDITION + 'TITLE LIKE ''%' +@SearchText+ '%'' OR DESCRIPTION LIKE ''%'+@SearchText+ '%'' OR TAGS LIKE ''%'+@SearchText+ '%'') AND FILENAME LIKE ''%'+ @FILENAMETEXT +'%'' AND ISAPPROVED=1 AND ENCODESTATUS=1 AND ISARCHIVED=0 AND ISDELETED=0 ' SET @CONDITION = ' WHERE (' SET @CONDITION = @CONDITION + 'TITLE LIKE ''%' +@SearchText+ '%'' OR DESCRIPTION LIKE ''%'+@SearchText+ '%'' OR TAGS LIKE ''%'+@SearchText+ '%'') AND FILENAME LIKE '+ @FILENAMETEXT +'%'' AND ISAPPROVED=1 AND ENCODESTATUS=1 AND ISARCHIVED=0 AND ISDELETED=0 ' SET @CONDITION = ' WHERE (' SET @CONDITION = @CONDITION + 'TITLE LIKE ''%' +@SearchText+ '%'' OR DESCRIPTION LIKE ''%'+@SearchText+ '%'' OR TAGS LIKE ''%'+@SearchText+ '%'') AND SUBSTRING(FILENAME,1,4)='+@FILENAMETEXT+' AND ISAPPROVED=1 AND ENCODESTATUS=1 AND ISARCHIVED=0 AND ISDELETED=0 ' SET @CONDITION = ' WHERE (' SET @CONDITION = @CONDITION + 'TITLE LIKE ''%' +@SearchText+ '%'' OR DESCRIPTION LIKE ''%'+@SearchText+ '%'' OR TAGS LIKE ''%'+@SearchText+ '%'') AND LEFT(FILENAME,4)='+@FILENAMETEXT+' AND ISAPPROVED=1 AND ENCODESTATUS=1 AND ISARCHIVED=0 AND ISDELETED=0 ' please reply me as soon as possible

View 1 Replies View Related

MSSQL Stored Procedure Query Using SELECT TOP

Dec 14, 2006

Is there a way to do a SELECT TOP # using a variable for the #?
In other words I'm doing a SELECT TOP 50* FROM DATATABLE
If I pass an @value for the number
SELECT TOP @value* FROM DATATABLE doesn't work
I am generating a random sampling of data and I want to allow the user to select the number of results they choose to have.
Thanks in advance.

View 2 Replies View Related

Mssql 2005. How To Make Update Stored Procedure ?

May 1, 2008

Hi ~
I made simple stored procedure that is to update user information following as...
ALTER PROCEDURE UpdateUserProfile(  @user_id uniqueidentifier,  @user_firstname nvarchar(50),  @user_lastname nvarchar(50),  @user_birth nvarchar(20),   @user_gender nvarchar(20)  )
AS 
 UPDATE user_profile    SET         user_firstname = @user_firstname,     user_lastname = @user_lastname,     user_birth = @user_birth,     user_gender =  @user_gender  WHERE user_id = @user_id  RETURN
When I tried to save this procedure, I faced on "Invalid Object : UpdateUserProfile" error message.
What's the problem ? 
 

View 2 Replies View Related

Need Help In Writing A Stored Procedure In MSSQL Server 2000

Mar 29, 2007

Hi Everybody,

I am trying to update a column Percentage in a table named Critical Doctors with a column named

PercentTime from tblPercent table, Where the column Doctor matches with any DoctorId from

tblPercent.

I am getting an error message for the following query.

Have Two tables

1.CriticalDoctors
2.tblPercent

update CriticalDoctors set Percentage =
(select PercentTime from tblPercent)
where CriticalDoctors.Doctor = (select DoctorId from tblPercent)

Server: Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <=

, >, >= or when the subquery is used as an expression.
The statement has been terminated.

Pls give me reply on how to write a stored procedure so that I can equate the percentage column

with one value and also check the condition with one value.

Thanking you guys in advance.

madhav

View 4 Replies View Related

Creating New Table From Passed Variabe In MSSQL Stored Procedure

Mar 2, 2004

Hi there i really cant understand why this should be a problem... it seems that i cant create a table from a stored procedure when passing the tablenam from application like this...

CREATE PROCEDURE dbo.CreateTable
@TableName NVARCHAR(50)
AS
BEGIN

create table @TableName ([id] int identity(1,1),[diller] int)

end
GO

THIS CANT BE TRUE !!!!!!!!

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

Slow Procedure While Checking Duplicate Records

Jun 21, 2001

hello friends


i m stuck up with a problem...actually i dont have much experience in database line....i m new to this line....i have recently joined the job & this problem is like a test of me....if i will be able to give the solution then everything is fine otherwise i will be fired & im not in a condition to leave this job as this is my first job in software development....i have got this chance with lots of difficulty....so please help me if u can...


the problem is....>> i m using a procedure to check the duplicatye records by using string comparison against address of persons..allover the country....

i m using SQL server 7.0
i have a single table(name of table is DATA) which contains 350000 records( i mean address entries) there are about 35 columns but i have to check duplicate records only against address field...for that first of all i remove special characters from the address field.....then i compare first 20 characters for duplicate entries...

for this i m generating another table(name of another table is RESULT)...

how the logic works...initially the data table contains the records but the result table is totally blank....first of all i pick first entry of address from DATA table then...check it with the entry in RESULT table if the entry exists... it compares the address if the record is same then it generates a refference of this address and make an entry....means a refference of that entry....(as far as very first record is concerned there will be no entry in the RESULT table so it will enter the address over there...then it picks up the second record...checks it in the RESULT table...now this record will be compared with the one & only entry in the RESULT table....if the entry is same then the refference will be entered... otherwise it will be entered as second record in the RESULT table....)

now where lies the problem.....initially the procedure is very fast.... but it gradually slows down .....because(when it checks the 10th record for duplication it compares the entry in RESULT table for 9 times only
*** similarly when it checks the 100th record it compares it for 99 times
*** similarly when it checks the 10000th record it compares it for 9999 times
so here lies the problem....

when it checks the 100000th record it gets dammm slow...
what i have get till now is that i have checked.....>>>>>
5000 records in 4 mins....
25000 records in 22 mins....
and
100000 records in 20 hours....(means initially its faster but it gradually slows down)
************************************************** ************************
here i m giving the code for the procedure......
************************************************** *************************


CREATE PROCEDURE pro1 as

SET NOCOUNT ON
Declare @IvgId as numeric(15)
Declare @Address as nvarchar(250)
Declare @AddressClean as nvarchar(250)
Declare @MaxLen as INT
Declare @Add as nvarchar(250)
Declare @Ic as int
Declare @FoundIvgId as numeric(15)
Declare @NewIvgId as numeric(15)

/* here 'N' is for keeping track for some system failures etc */

Declare CurData CURSOR forward_only FOR Select IvgId, Address From Data Where ProcessClean = 'N'

OPEN CurData

FETCH NEXT FROM CurData INTO @IvgId, @Address

WHILE @@FETCH_STATUS = 0
Begin
/*here i m doing string cleaning by removing special characcters */
Select @MaxLen = len(LTRIM(RTRIM(@Address)))
Select @Address = LOWER(@Address)
Select @Ic = 1
Select @AddressClean = ' '
While @Ic <= @MaxLen
/* here @MaxLen is the maximum length of the address field but i have to compare only first 20 characters */
Begin
Select @Add = Substring(@Address, @Ic, 1)

If ascii(@Add) > 47 AND ascii(@Add) <= 64 AND @Add <> ' '
Begin
Select @AddressClean = @AddressClean + @Add
End

If ascii(@Add) > 90 AND @Add <> ' '
Begin
Select @AddressClean = @AddressClean + @Add
End

Select @Ic = @Ic + 1
End

/* now we have removed special characters , for failure checking i m using this 'Y' */
Update Data Set AddressClean = @AddressClean, ProcessClean = 'Y'
Where IvgId = @IvgId

FETCH NEXT FROM CurData INTO @IvgId, @Address
End

PRINT 'Cleaning Done.............................'

Close CurData
Deallocate CurData

/* till now procedure doesnt take too much time & cleans all the 3 lack records in abt 40 mins but next part is giving trouble */

Declare CurData CURSOR FOR Select IvgId, Address, AddressClean From Data Where ProcessDup = 'N'
OPEN CurData

FETCH NEXT FROM CurData INTO @IvgId, @Address, @AddressClean
Select @NewIvgId = 100

WHILE @@FETCH_STATUS = 0
Begin

If EXISTS (Select IvgId From Result Where SubString(RTRIM(LTRIM(AdressClean)),1,20) = SubString(RTRIM(LTRIM(@AddressClean)),1,20))
Begin
Update Result Set DupIvgId = @IvgId Where SubString(RTRIM(LTRIM(AdressClean)),1,20) = SubString(RTRIM(LTRIM(@AddressClean)),1,20)
End

ELSE
Begin
Insert Into Result Values (@NewIvgId, @Address, @AddressClean,0)
Select @NewIvgId = @NewIvgId + 1
End

Update Data set ProcessDup = 'Y' Where IvgId = @IvgId
FETCH NEXT FROM CurData INTO @IvgId, @Address, @AddressClean
End

Close CurData
Deallocate CurData
SET NOCOUNT OFF

Print 'Done................................'

************************************************** **************************
now the procedure is over....now i m writing the SQL script of DATA & RESULT table
************************************************** ************************

CREATE TABLE [dbo].[DATA] (
[IVGID] [numeric](18, 0) NOT NULL ,
[Title] [varchar] (10) NULL ,
[FirstName] [varchar] (50) NULL ,
[MiddleName] [varchar] (10) NULL ,
[LastName] [varchar] (30) NULL ,
[Add1] [varchar] (150) NULL ,
[Add2] [varchar] (50) NULL ,
[Add3] [varchar] (50) NULL ,
[City] [varchar] (30) NULL ,
[State] [varchar] (30) NULL ,
[Country] [varchar] (20) NULL ,
[Pincode] [varchar] (10) NULL ,
[OffPhone] [varchar] (20) NULL ,
[OffFax] [varchar] (20) NULL ,
[ResPhone] [varchar] (20) NULL ,
[ResFax] [varchar] (20) NULL ,
[EMail] [varchar] (50) NULL ,
[Source] [varchar] (20) NULL ,
[MODEL] [varchar] (20) NULL ,
[PNCD] [varchar] (6) NULL ,
[DupKey] [decimal](18, 0) NULL ,
[Duplicate] [int] NULL ,
[HouseHoldID] [varchar] (50) NULL ,
[YearSlab] [varchar] (10) NULL ,
[CleanStatus] [int] NULL ,
[AddStatus] [int] NULL ,
[BatchNo] [varchar] (20) NULL ,
[ModelStatus] [int] NULL ,
[Month] [int] NULL ,
[Year] [int] NULL ,
[SapStatus] [int] NULL ,
[ErrCase] [int] NULL ,
[cmpCity] [varchar] (50) NULL ,
[Product] [varchar] (1) NULL ,
[cmpPinCode] [varchar] (6) NULL ,
[Address] [nvarchar] (250) NULL ,
[AddressClean] [nvarchar] (250) NULL ,
[DupIvgId] [numeric](18, 0) NULL ,
[ProcessClean] [nvarchar] (1) NULL ,
[ProcessDup] [nvarchar] (1) NULL
) ON [PRIMARY]
GO

/****** Object: Table [dbo].[DATA_TEST] Script Date: 15/06/2001 8:36:21 PM ******/
CREATE TABLE [dbo].[DATA_TEST] (
[IVGID] [numeric](18, 0) NOT NULL ,
[Address] [nvarchar] (50) NULL ,
[AddressClean] [nvarchar] (50) NULL ,
[DupIvgId] [numeric](18, 0) NULL ,
[ProcessClean] [nvarchar] (1) NULL ,
[ProcessDup] [nvarchar] (1) NULL
) ON [PRIMARY]
GO

so now i have given the whole description of my problem....i m eagerly waiting for reply......
if anybody can help....i will be very thankful.....
bye for now
Bhupinder singh

View 1 Replies View Related

Quick Question About Checking For Existing Procedure Before Creation.

Feb 27, 2008

Hey everyone,

I'm getting a rather bizarre (but probably simple) error when trying to check for the existence of a procedure before creating it. I know I could just drop it, but I'm really curious as to why this code doesn't work right.



Code SnippetIF OBJECT_ID(N'dbo.spCoverageLog') IS NULL
CREATE PROCEDURE [dbo].[spCoverageLog] ( @spName VARCHAR(100) )
AS
BEGIN
UPDATE dbo.CodeCoverage
SET CreationDate = GETDATE(),ProcedureName = @spName;
END

GO
It's generating a syntax error at PROCEDURE.

Anyone know why this might happen?

The solution was to run the CREATE PROCEDURE statement with EXEC, but it shouldn't be.




Thanks in advance for any suggestions!

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

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

Constraint Error In Stored Proc

Dec 18, 2007

This query keeps giving me a Primary Key violation error and I am not sure what it is getting at. maybe you can help?

truncate table tbl_rtv_cosmetic_dif
go
insert tbl_RTV_COSMETIC_DIF
select d.fisc_yr
,d.fisc_pd
,d.fisc_wk
,d.fisc_dy
,substring(a.long_sku,1,4) as DeptNo
,substring(a.long_sku,7,3) as VendorNo
,substring(a.long_sku,10,5) as MarkStyleNo
,a.Loc as LocNo
,convert(int,a.RsnCd) as ReasonCode
,min(a.EAN_Nbr) as UPCEANNo
,coalesce(i.own_rtl,0) as OwnedCur
,sum(convert(int,a.units)) as UPCUnits


from november a
inner join generalinfo..tbl_fisc_date d
on a.date = d.cal_date

left outer join itemdata..tbl_item_import_history i
on substring(a.long_sku,1,4) = i.dept
and substring(a.long_sku,7,3) = i.vendor
and substring(a.long_sku,10,5) = i.mrk_style
and d.fisc_yr = i.amc_yr
and d.fisc_pd = i.amc_pd
and d.fisc_wk = i.amc_wk

group by fisc_yr,fisc_pd,fisc_wk,fisc_dy
,substring(long_sku,1,4),substring(long_sku,7,3),substring(long_sku,10,5)
,loc,rsncd,own_rtl

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

Named Constraint Is Not Supported For This Type Of Constraint (not Null)

May 13, 2008

Hi, all.

I am trying to create table with following SQL script:





Code Snippet

create table Projects(
ID smallint identity (0, 1) constraint PK_Projects primary key,
Name nvarchar (255) constraint NN_Prj_Name not null,
Creator nvarchar (255),
CreateDate datetime
);

When I execute this script I get following error message:

Error source: SQL Server Compact ADO.NET Data Provider
Error message: Named Constraint is not supported for this type of constraint. [ Constraint Name = NN_Prj_Name ]

I looked in the SQL Server Books Online and saw following:

CREATE TABLE (SQL Server Compact)
...
< column_constraint > ::= [ CONSTRAINT constraint_name ] { [ NULL | NOT NULL ] | [ PRIMARY KEY | UNIQUE ] | REFERENCES ref_table [ ( ref_column ) ] [ ON DELETE { CASCADE | NO ACTION } ] [ ON UPDATE { CASCADE | NO ACTION } ]

As I understand according to documentation named constraints should be supported, however error message says opposite. I can rephrase SQL script by removing named constraint.





Code Snippet

create table Projects(
ID smallint identity (0, 1) constraint PK_Projects primary key,
Name nvarchar (255) not null,
Creator nvarchar (255),
CreateDate datetime
);
This script executes correctly, however I want named constraints and this does not satisfy me.

View 1 Replies View Related

Column Level Constraints... I Need A Constraint, Not A Rule, Or A Trigger Or A Stored Proc... Thnx.

Jul 17, 2001

Hello all.

First of all, I've been a reader of swynk.com for quite sometime now, and I'd like to say 'thank you' to everyone who contributes.

Today, I'm the town moron.. haha I'm having issues with column level constraints. I have a varchar(50) where I want to keep *,=,#,/, .. etc, OUT OF the value input. I don't want to strip them. I simply want for sql to throw an error if the insert contains those (and other characters). The only characters that I want in the column are A-Z and 0-9. However, it's not a set number of characters per insert. It always varies... There has to be an easier way to do this than creating a constraint for every possibilty... Any help would be greatly appreciated.

tia,

Jeremy

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

Stored Procedures In MSSQL Server

May 6, 2006

hi, anyone can guide me on how to create stored procedures in MS SQL Server?

View 1 Replies View Related

Stored Procedures In A Mssql 6.5 Application?

Feb 22, 1999

We have vendor from we puchased an application. It is going into production next week
and we have our final set of table ddl and it includes stored procedures.

Since I am fairly new to MSSQL 6.5, and forced to include the stord procedures within
the application, I would like to get some feedback as to whether or not stored proceures
are efficient or are going to cause me problems?

Aside from periodically running sp_recompile what else will I need to do?

Any information that can be provided willl be greatly appreciated. THanks.

David Spaisman

View 1 Replies View Related

Debug Stored Proc's In MSSQL 2005

Mar 15, 2007

In SQL Server 2000's Query Analyzer, you can debug a stored procedure by right-clicking a SP and select "Debug".
You can then step through the SP one line at a time.I don't see this in SQL Server 2005 and have searched bu cannot find any documentation on what happened to this feature.What did Microsoft do with this?

View 1 Replies View Related







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