Code Thinks Deleted Stored Procedure Still Exists?

Feb 13, 2013

I'm writing some code to create stored procedures in a database. In order to test it out, I deleted a stored procedure (right clicking in SQL Server 2008 and clicking on delete) and then ran my code to see if it would create it.

My code looks like this:

Code:
SqlCommand command = new SqlCommand();
SqlConnection conn = database.TypeLibraryDatabaseConnection;
command.Connection = conn;
// create the command to create the stored procedure
command.CommandText = database.GetProcedure(node.Name);
// create the stored proc in the database
try
{
command.ExecuteNonQuery();
}
catch
{
}
command.Dispose();

database.GetProcedure(node.name) basically gets a string containing the SQL script to create the stored procedure.

command.ExecuteNonQuery() throws an SqlException that says: "There is already an object named 'SecuritySession_DeleteSessionById' in the

database." But I deleted it! Why does it think it's still there?

View 3 Replies


ADVERTISEMENT

Transact SQL :: Prevent Stored Procedure From Getting Deleted

Sep 28, 2015

How could I prevent a stored procedure from getting deleted?

View 3 Replies View Related

Deleted Stored Procedure Caused Backup Job To Failed

May 29, 2007



Hello,



I created a test stored procedure in MS SQL 2000. When the problem in my app was fixed, I deleted this test stored procedure. But the backup job thinks this procedure still exist and so backup job would failed. How can I fix this problem?



Thank in advance for your assistance.

View 1 Replies View Related

Does Stored Procedure Exists???

Sep 14, 2006

Louis writes "Is it possibble to write a SQL statement that returns "True" or "False" if a stored procedure exists. Basically write a SQL statement that checks whether or not the Stored Procedure exist in the database???


Thanks in advance"

View 3 Replies View Related

Stored Procedure And If Exists

Jul 23, 2005

Hi,In my stored procedure I would like to do something like :IF EXISTS(SELECT WebIdFROM WebsWHERE FormatDateId = @FormatDateId)BEGINSELECT WebIdFROM WebsWHERE FormatDateId = @FormatDateIdSET @RetVal = -1ENDIs there a nicer way to do this, so that we don't have to do therequest twice ?Regards

View 2 Replies View Related

Many Lines Of Code In Stored Procedure && Code Behind

Feb 24, 2008

Hello,
I'm using ASP.Net to update a table which include a lot of fields may be around 30 fields, I used stored procedure to update these fields. Unfortunatily I had to use a FormView to handle some TextBoxes and RadioButtonLists which are about 30 web controls.
I 've built and tested my stored procedure, and it worked successfully thru the SQL Builder.The problem I faced that I have to define the variable in the stored procedure and define it again the code behind againALTER PROCEDURE dbo.UpdateItems
(
@eName nvarchar, @ePRN nvarchar, @cID nvarchar, @eCC nvarchar,@sDate nvarchar,@eLOC nvarchar, @eTEL nvarchar, @ePhone nvarchar,
@eMobile nvarchar, @q1 bit, @inMDDmn nvarchar, @inMDDyr nvarchar, @inMDDRetIns nvarchar,
@outMDDmn nvarchar, @outMDDyr nvarchar, @outMDDRetIns nvarchar, @insNo nvarchar,@q2 bit, @qper2 nvarchar, @qplc2 nvarchar, @q3 bit, @qper3 nvarchar, @qplc3 nvarchar,
@q4 bit, @qper4 nvarchar, @pic1 nvarchar, @pic2 nvarchar, @pic3 nvarchar, @esigdt nvarchar, @CCHName nvarchar, @CCHTitle nvarchar, @CCHsigdt nvarchar, @username nvarchar,
@levent nvarchar, @eventdate nvarchar, @eventtime nvarchar
)
AS
UPDATE iTrnsSET eName = @eName, cID = @cID, eCC = @eCC, sDate = @sDate, eLOC = @eLOC, eTel = @eTEL, ePhone = @ePhone, eMobile = @eMobile,
q1 = @q1, inMDDmn = @inMDDmn, inMDDyr = @inMDDyr, inMDDRetIns = @inMDDRetIns, outMDDmn = @outMDDmn,
outMDDyr = @outMDDyr, outMDDRetIns = @outMDDRetIns, insNo = @insNo, q2 = @q2, qper2 = @qper2, qplc2 = @qplc2, q3 = @q3, qper3 = @qper3,
qplc3 = @qplc3, q4 = @q4, qper4 = @qper4, pic1 = @pic1, pic2 = @pic2, pic3 = @pic3, esigdt = @esigdt, CCHName = @CCHName,
CCHTitle = @CCHTitle, CCHsigdt = @CCHsigdt, username = @username, levent = @levent, eventdate = @eventdate, eventtime = @eventtime
WHERE (ePRN = @ePRN)
and the code behind which i have to write will be something like thiscmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@eName", ((TextBox)FormView1.FindControl("TextBox1")).Text);cmd.Parameters.AddWithValue("@ePRN", ((TextBox)FormView1.FindControl("TextBox2")).Text);
cmd.Parameters.AddWithValue("@cID", ((TextBox)FormView1.FindControl("TextBox3")).Text);cmd.Parameters.AddWithValue("@eCC", ((TextBox)FormView1.FindControl("TextBox4")).Text);
((TextBox)FormView1.FindControl("TextBox7")).Text = ((TextBox)FormView1.FindControl("TextBox7")).Text + ((TextBox)FormView1.FindControl("TextBox6")).Text + ((TextBox)FormView1.FindControl("TextBox5")).Text;cmd.Parameters.AddWithValue("@sDate", ((TextBox)FormView1.FindControl("TextBox7")).Text);
cmd.Parameters.AddWithValue("@eLOC", ((TextBox)FormView1.FindControl("TextBox8")).Text);cmd.Parameters.AddWithValue("@eTel", ((TextBox)FormView1.FindControl("TextBox9")).Text);
cmd.Parameters.AddWithValue("@ePhone", ((TextBox)FormView1.FindControl("TextBox10")).Text);
cmd.Parameters.AddWithValue("@eMobile", ((TextBox)FormView1.FindControl("TextBox11")).Text);
So is there any way to do it better than this way ??
Thank you

View 2 Replies View Related

Check If A Stored Procedure Exists

Mar 14, 2007

Hi All.

Can anyone tell me what the syntax to check and see if a stored procedure exists and if it doesnt to create it is?

Thanks people.

View 8 Replies View Related

Stored Procedure Exists Statement

Feb 26, 2008

When I created an SP in Enterprise Manager, I didn't manually type in the existence check at the inception, a la "if exists (select * from sysobjects...)". I just started with the CREATE PROC AS statement.

I noticed that if I generate a SQL script for the SP, SQL2000 automatically generates the existence check statement.

My question is, can I assume that when the SP is actually executed, SQL2000 does the exists check on its own? I EXEC'd the proc in Query Analyzer with no errors.

I just want to make sure that I don't need to enter the exists statement if it's already being done behind the scenes.

View 3 Replies View Related

Stored Procedure {0} Already Exists At Destination.

Mar 30, 2006

Trying to create a package that will copy all user stored procs from one database to another - on the same server.

I am using a Transfer SQL Server Objects Task to do this.

I am getting the error below.

Warning: 0x8002F356 at STORED PROCS, Transfer SQL Server Objects Task: Stored procedure "{0}" already exists at destination.

DropObjectsFirst is set to True

Can anyone help? Thank you in advance.



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

Determining If A Parameter Exists In A Stored Procedure

Jul 20, 2005

Hey,I'm writing some automated unit tests for a database app. The appcreates a database via script. I'm developing the system in atest-driven manner and need a way to determine if a parameter existswithin a stored procedure using SQL. Any ideas?Thanks,WillJoin Bytes!

View 3 Replies View Related

Stored Procedure That Checks To See Whether A Value Exists And Only Enters Data If It Doesn't

Feb 4, 2008

Hi All,

I Have a nifty little stored procedure that takes data from an ASP form post
and inserts the data into two tables. However what I really need this to do
before the insert is check whether there is already a record that matches
some of the criteria, and if so returns an error "This Username already
exists" and only if there isn't a record that matches the criteria is the
record inserted.

my current stored procedure looks like this -

Code Snippet@siteid int,@companyname nvarchar(50),@address nvarchar(500),@phone nvarchar(50),@fax nvarchar(50),@email nvarchar(225),@url nvarchar(225),@companytype nvarchar(50),@billingcontact nvarchar(50),@name nvarchar(50),@AccountType nvarchar(50),@PASSWORD nvarchar(50),@AccountLive nvarchar(50),@EmployeeLevel nvarchar(50)ASDeclare @NewID INTINSERT INTO dbo.JBClient(JBCLSiteID, JBCLName, JBCLAddress, JBCLPhone, JBCLFax, JBCLEmail, JBCLCompanyType, JBCLURL, JBCLAccountType, JBCLAccountlive, JBCLBillingContact)VALUES (@siteid, @companyname, @address, @phone, @fax, @email, @companytype, @url, @AccountType, @AccountLive, @billingcontact)SELECT @NewID = SCOPE_IDENTITY()INSERT INTO dbo.JBEmployee(JBEClientID, JBESiteID, JBEName, JBELevel, JBEUsername, JBEPassword, JBEAddress, JBEPhone)VALUES (@NewID, @siteid, @name, @EmployeeLevel, @email, @PASSWORD, @address, @phone)
The values that i need to check against are -

@siteid
@email

in the table dbo.JBEmployee against columns JBESiteID & JBEUsername

What i would really like to do if a record exists is return the user to an
ASP page, which contains all of the variables previously enterred -,

@siteid int,
@companyname nvarchar(50),
@address nvarchar(500),
@phone nvarchar(50),
@fax nvarchar(50),
@email nvarchar(225),
@url nvarchar(225),
@companytype nvarchar(50),
@billingcontact nvarchar(50),
@name nvarchar(50),
@AccountType nvarchar(50),
@PASSWORD nvarchar(50),
@AccountLive nvarchar(50),
@EmployeeLevel nvarchar(50)

together with a message that says "This Username already exists"

Does anyone have any idea how to do this???

Many thanks

View 6 Replies View Related

Help With TSQL Stored Procedure - Error-Exec Point-Procedure Code

Nov 6, 2007

I am building a stored procedure that changes based on the data that is available to the query. See below.
The query fails on line 24, I have the line highlighted like this.
Can anyone point out any problems with the sql?

------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------
This is the error...


Msg 8114, Level 16, State 5, Procedure sp_SearchCandidatesAdvanced, Line 24

Error converting data type varchar to numeric.

------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------
This is the exec point...


EXEC [dbo].[sp_SearchCandidatesAdvanced]

@LicenseType = 4,

@PositionType = 4,

@BeginAvailableDate = '10/10/2006',

@EndAvailableDate = '10/31/2007',

@EmployerLatitude = 29.346675,

@EmployerLongitude = -89.42251,

@Radius = 50

GO

------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------
This is the STORED PROCEDURE...


set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go



ALTER PROCEDURE [dbo].[sp_SearchCandidatesAdvanced]

@LicenseType int = 0,

@PositionType int = 0,

@BeginAvailableDate DATETIME = NULL,

@EndAvailableDate DATETIME = NULL,

@EmployerLatitude DECIMAL(10, 6),

@EmployerLongitude DECIMAL(10, 6),

@Radius INT


AS


SET NOCOUNT ON


DECLARE @v_SQL NVARCHAR(2000)

DECLARE @v_RadiusMath NVARCHAR(1000)

DECLARE @earthRadius DECIMAL(10, 6)


SET @earthRadius = 3963.191


-- SET @EmployerLatitude = 29.346675

-- SET @EmployerLongitude = -89.42251

-- SET @radius = 50


SET @v_RadiusMath = 'ACOS((SIN(PI() * ' + @EmployerLatitude + ' / 180 ) * SIN(PI() * p.CurrentLatitude / 180)) + (COS(PI() * ' + @EmployerLatitude + ' / 180) * COS(PI() * p.CurrentLatitude / 180) * COS(PI()* p.CurrentLongitude / 180 - PI() * ' + @EmployerLongitude + ' / 180))) * ' + @earthRadius




SELECT @v_SQL = 'SELECT p.*, p.CurrentLatitude, p.CurrentLongitude, ' +

'Round(' + @v_RadiusMath + ', 0) AS Distance ' +

'FROM ProfileTable_1 p INNER JOIN CandidateSchedule c on p.UserId = c.UserId ' +

'WHERE ' + @v_RadiusMath + ' <= ' + @Radius


IF @LicenseType <> 0

BEGIN

SELECT @v_SQL = @v_SQL + ' AND LicenseTypeId = ' + @LicenseType

END


IF @PositionType <> 0

BEGIN

SELECT @v_SQL = @v_SQL + ' AND Position = ' + @PositionType

END


IF LEN(@BeginAvailableDate) > 0

BEGIN

SELECT @v_SQL = @v_SQL + ' AND Date BETWEEN ' + @BeginAvailableDate + ' AND ' + @EndAvailableDate

END


--SELECT @v_SQL = @v_SQL + 'ORDER BY CandidateSubscriptionEmployerId DESC, CandidateFavoritesEmployerId DESC, Distance'


PRINT(@v_SQL)

EXEC(@v_SQL)


-----------------------------------------------------------------------------------------------------------------

View 4 Replies View Related

Transfer SQL Server Objects Task - Warning: Stored Procedure Already Exists At Destination.

Apr 13, 2006

I am trying to use a Transfer SQL Server Objects Task to copy a portions of tables from one db to a second db.

the connection is using Windows Authentication.

I am logged in as the Administrator

That user has sysadmin rights on the Sql Server in Question.

DropObjectsFirst = True

But when I execute the task I get the follwing warning

[Transfer SQL Server Objects Task] Warning: Stored procedure "{0}" already exists at destination.

What am I missing?

Thanks,Kim

View 4 Replies View Related

Please Help With Vb.net Code And Stored Procedure

Jul 2, 2004

I have the following stored procedure in sql server 2000:

(@nsn varchar 15 int,
@item_nam varchar 255 ouput
)
AS
declare @stockquantity int

select @stockquantity = stockquantity
from inventory
where stockquantity = 0

select @item_nam = item_nam
from inventory where nsn = @nsn

delete from inventory
where nsn = @nsn
and stockquantity = 0
GO

what would the vb.net code be to display the item_nam if the query was successful in deleting a record, but would then retutrn a different mesage if the item was not deleted becasuse the stockquantity was not equal to zero. Right now it returns the item_nam even if the record wsa not deleted.
ANy help is much appreciated.

View 1 Replies View Related

Getting A Stored Procedure's Code

Jul 23, 2005

I don't know if this is possible. However, what i am attempting to do isusing C#'s window forms. I open up an excell sheet stored in my windowsform. The excel sheet stores names of the stored procedures in thatdatabase. I want to know if it's possible to click on that storedprocedure to open up a link to display the code of that stored procedure ofcourse in a read only mode.any suggestions....

View 2 Replies View Related

PHP Code To Stored Procedure

Sep 22, 2007

hi all,i need to convert these simple PHP code into stored procedure :<?php$result=mssql_query( "SELECT whid, whcode FROM warehouse" );while( $wh = mssql_fetch_object( $result ) ){$result=mssql_query( "SELECT plid, nopl FROM packlist WHERE whid ='" . $wh->whid . "'";while( $pl = mssql_fetch_object( $result ) ){$result=mssql_query( "SELECT qty FROM packlistnmat WHERE plid ='" . $pl->plid . "'";while( $pln = mssql_fetch_object( $result ) ){echo "Stock from " . $wh->whcode . " AND Packing List number " .$pl->plid . " = " . $pln->qty;}}}?>my focus is in nested query, then i can call each field from the query(SELECT whid, whcode...) in sub query.thanks,aCe

View 4 Replies View Related

Help FIX BUG In My Sql Code-stored Procedure

Jan 8, 2008

help BUG in my sql code
i can not see this employee 111111 whan i run this code


SELECT 111111,1,'19/01/2008','29/01/2008' UNION ALL
TNX





Code Block
------------------------
-- create mod cycle shift pattern
------------------------
DECLARE
@shifts_pattern TABLE
(
[PatternId] [int] IDENTITY(1,1 ) NOT NULL,[patternShiftValue] [int]NOT NULL)
declare
@I int
set
@i=0
while
@i < 5
BEGIN
INSERT INTO @shifts_pattern ([patternShiftValue] )
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 8
set
@i=@i+1
end
-- select * from @shifts_pattern
--- end shift pattern
--------------------------------
declare
@empList
TABLE
( [empID] [numeric](18, 0) NOT NULL,[ShiftType] [int]NULL,[StartDate][datetime]NOT NULL,[EndDate] [datetime] NOT NULL) INSERT INTO
@empList ([empID], [ShiftType],[StartDate],[EndDate])
SELECT 111111,1,'19/01/2008','29/01/2008' UNION ALL
SELECT 222222,2,'29/01/2008','30/01/2008' UNION ALL
SELECT 333333,3 ,'27/01/2008','30/01/2008' UNION ALL
SELECT 444444,5 ,'26/01/2008','30/01/2008'
--------------------------------
-- create shifts table
declare
@empShifts
TABLE
( [empID] [numeric](18, 0) NOT NULL,[ShiftDate] [datetime]NOT NULL,[ShiftType] [int]NULL
,[StartDate] [datetime]NOT NULL,[EndDate] [datetime]NOT NULL)
DECLARE
@StartDate datetime
DECLARE
@EndDate datetime
Declare
@current datetime
DEclare
@last_shift_id int
Declare
@input_empID int

--SET
----@StartDate = StartDate
---- last day of next month
--SET
--@EndDate = EndDate
--@StartDate = DATEADD(m ,2,GETDATE()-DAY (GETDATE()) + 1 )
---- last day of next month
--SET
--@EndDate = DATEADD(m ,3,GETDATE()-DAY (GETDATE()) + 1)- 1
--set
--@current = @StartDate
----
----------------- open list table for emp with curser
DECLARE
List_of_emp CURSOR FOR
SELECT
emp.empId,emp.ShiftType,emp.StartDate,emp.EndDate FROM @empList emp
OPEN
List_of_emp
FETCH
List_of_emp INTO @input_empID , @last_shift_id ,@StartDate,@EndDate
-----------------
-- loop on all emp in the list
while
@@Fetch_Status = 0
begin
-- loop to insert info of emp shifts
while
@current<=@EndDate
begin
INSERT INTO @empShifts ([empID],[ShiftDate],[ShiftType],[StartDate] ,[EndDate])
select @input_empID ,@current,shift .patternShiftValue ,@StartDate,@EndDate
from @shifts_pattern as shift where PatternId=@last_shift_id+1
-- if it is Friday and we are on one of the first shift we don't move to next shift type .
if (DATENAME(dw ,@current) = 'Friday' ) and
EXISTS(select ShiftType from @empShifts where ShiftDate=@current and empID= @input_empID and ShiftType in ( 1,2,3))
-- do nothing
--set @last_shift_id=@last_shift_id
print ('friday first shift')
ELSE
set @last_shift_id=@last_shift_id+ 1
set @current=DATEADD( d,1, @current)
end
FETCH
List_of_emp INTO @input_empID ,@last_shift_id,@StartDate,@EndDate
-- init of start date for the next emp
set
@current = @StartDate
end
CLOSE
List_of_emp
DEALLOCATE
List_of_emp
select
empID,shiftDate,DATENAME (dw,shift.ShiftDate ), shiftType from @empShifts as shift
RETURN

View 1 Replies View Related

Getting T-SQL Code For A Stored Procedure

Nov 3, 2006

I need to have my middle tier code get the T-SQL code for a stored procedure. So, I need to create a string something like:

string tsqlCode = @"CREATE PROCEDURE "NSInsertGenerator"
  @GeneratorName      NVARCHAR(255),
  @SystemName         NVARCHAR(255),
  @ThreadPoolSize     INT,
  @ActivationState     TINYINT
AS
INSERT INTO "NSGenerators"
      ( GeneratorName,  SystemName,  ThreadPoolSize,  ActivationState)
VALUES(@GeneratorName, @SystemName, @ThreadPoolSize, @ActivationState)

GO"

This string will be analyzed by the middle tier code so that the list of parameters for this procedure will be built dynamically depending on the current version of this stored procedure.

We are using SQL Server 2000 SP4.

Could someone recommend me how I can get T-SQL code of a stored procedure, please?

Thanks a lot!

View 4 Replies View Related

NEED HELP TO MODIFY STORED PROCEDURE CODE...

Apr 3, 2007

Pls check my code for the stored procedure which i created for the companydetails including companyid P.K. Not Null int(4),companyname Not Null varchar (20),address varchar(30) where companyid is the primary key and it should be autogenerate.I also want that it should check if the name exits or not.It should also check that the field is entered and not kept null.If it's null then should return the error message.I want to write the queries select,insert,update and delete in the single stored procedure.How can i differ all the query individually in a stored procedure.The select and insert query are done on the button click event whereas the update,delete queries are performed in the gridview link event. Pls help me and modify my code accordingly with ur suggestions who know
the stored procedure very well.First read what i want then give reply.waiting for the reply and with corrections.The coding is perfomed in sql server 2005 and asp.net with C# 2005, 1 ALTER PROCEDURE CompanyStoredProcedure2 @uspcompanyid int,3 @uspcompanyname varchar(20),4 @uspaddress1 varchar(30), 5 @frmErrorMessage as varchar(256) OUTPUT,6 @RETURNVALUE as int OUTPUT,7 @RETURNID as int OUTPUT8 AS9 declare
10 @companyid int,11 @companyname varchar(20),12 @address1 varchar(30) 13 14 BEGIN15 16 begin17 Select @RETURNVALUE = -918 RETURN -919 end20 21 begin22 Select @frmErrorMessage = 'The Operation Mode Has Not Been Specified'
23 return -924 end25 26 27 28 begin 29 --validation...
30 if (@uspcompanyname is Null or @uspcompanyname = '')31 begin32 Select @RETURNVALUE = -933 select @frmErrorMessage = 'Company Name is empty'
34 return -935 end 36
37 if exists (select companyid from companymaster 38 where upper(companyname) = upper(cast(@uspcompanyname as varchar(20))))39 begin40 select @companyid = companyid from companymaster 41 where upper(companyname)=upper(cast(@uspcompanyname as varchar(20) ) )42 end43 else 44 45 select @companyname= cast (@uspcompanynameas varchar(20))46 select @address1= cast(@uspaddress1 as varchar(30))47 select @companyid = isnull(max(companyid),0) + 1 from companymaster48 49 IF exists(SELECT * from companymaster where companyname=@companyname)50 begin51 Select @frmErrorMessage = 'Record With Company Name ' 52 + @companyname + ' is Already Exisiting For The Company Name ' 53 return -954 end 55 56 -- the following codes inserts
57 begin transaction58 INSERT INTO companymaster59 ( companyname, address1)60 VALUES (@companyname,@address1)61 commit transaction62 63 select @RETURNVALUE = 064 select @RETURNID = @companyid65 66 end67 68 69 -- the following codes edit/updates
70 begin71 UPDATE companymaster 72 SET companyname=@companyname,73 address1=@address1 74 WHERE companyid =cast(@uspcompanyid as int)75 76 select @RETURNVALUE = 077 select @RETURNID = cast(@uspcompanyid as int)78 end79 -- the following codes delete
80 begin81 DELETE companymaster WHERE (companyid = @companyid)82 end 83 84 END 85
  Pls help me and modify my code accordingly with ur suggestions who know the stored procedure very well.First read what i want then give reply.  

View 4 Replies View Related

Alter Stored Procedure With Asp.net Code

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

Extended Stored Procedure And C Code

Jul 12, 2007

Hi,

I am using vs2005, vc++, sql2005 and trying to write a complex extended stored procedure.



It seems I must use switch clrafe for my executable to deploy to sql.



But with clrafe, I can no longer use such c code tools such as typedef structures and pointer manipulation!





But I need to do these things to translate a contiuous block of binary data into a series of field/value pairs to be written to the sql server!!! Doing so externally would mean a severe drop in throughput: going back to the server for each field instead of all as one block!



Is there a technique, or pragma command to allow "unmanaged" c code to reside in a sql extended stored procedure?

View 15 Replies View Related

Running A Stored Procedure In Code

Apr 14, 2008

Hi,

I'm not sure if this is really the right place for this but it is related to my earlier post. Please do say if you think I should move it.

I created a Stored procedure which I want to run from Visual basic (I am using 2008 Express with SQL Sever 2005 Express)

I have looked through many post and the explaination of the sqlConection class on the msdn site but I am now just confussed.

Here is my SP


ALTER PROCEDURE uspSelectBarItemID2

(

@BarTabID INT,

@DrinkID INT,

@ReturnBarItemID INT OUTPUT

)

AS

BEGIN

SELECT @ReturnBarItemID = barItemID

FROM [Bar Items]

WHERE (BarTabID = @BarTabID) AND (DrinkID = @DrinkID)

END

In VB I want to pass in the BarTabID and DrinkID varibles (Which Im grabbing from in as int variables) to find BarItemID in the same table and return it as an int.

What I dont understand is do I have to create a unique connection to my database because it is already liked with a dataset to my project with a number of BindingSources and TableAdapters.

Is there an easier way, could I dispense with SP and just use SQL with the VB code, I did think the SP would be neater.

Cheers.

View 11 Replies View Related

SqlDataSource Call Stored Procedure In Code-behind

Jul 14, 2006

Can someone show me how to use SqlDataSource to call stored procedure in a c# class (a support class for aspx) and not in aspx or code-behind?

View 20 Replies View Related

Execute Stored Procedure From Code Behind File

Mar 15, 2008

hi all
I have to execute the stored procedure from code file
string constr = ConfigurationSettings.AppSettings["ConnectionString"];SqlConnection con = new SqlConnection(constr);con.Open();SqlCommand cmd = new SqlCommand("GetTax",con);cmd.CommandType = CommandType.StoredProcedure;SqlParameter paramFrom = new SqlParameter("@from", SqlDbType.VarChar, 50);paramFrom.Value = "JFK";SqlParameter paramTo = new SqlParameter("@To", SqlDbType.VarChar,50);paramTo.Value = "HOU";SqlParameter paramAirline = new SqlParameter("@Airline", SqlDbType.VarChar,50);paramAirline.Value = "US";SqlParameter rpTax = new SqlParameter("@Tax",SqlDbType.Int);rpTax.Direction = ParameterDirection.Output;cmd.Parameters.Add(rpTax);
insted of this way can i execute stored procedure any other way
like
exec MystoredProc "param1"."param2","param3"
 i appreciate u r help
 
 
 

View 2 Replies View Related

Error When Running A Stored Procedure From My Code

May 9, 2008

 Hi all,      I wonder if you can help me with this. Basically, Visual Web Developer doesn't like this part of my code despite the fact that the stored procedure has been created in MS SQL. It just won't accept that bold line in the code below and even when I comment it just to cheat, it still gives me an error about the Stored Procedure. Here's the line of code:          // Define data objects        SqlConnection conn;        SqlCommand comm;        // Initialize connection        string connectionString =            ConfigurationManager.ConnectionStrings[            "pay"].ConnectionString;        // Initialize connection        conn = new SqlConnection(connectionString);        // Create command         comm = new SqlCommand("UpdatePaymentDetails", conn);        //comm.CommandType = CommandType.StoredProcedure;        // Add command parameters        comm.Parameters.Add("PaymentID", System.Data.SqlDbType.Int);        comm.Parameters["PaymentID"].Value = paymentID;        comm.Parameters.Add("NewPayment", System.Data.SqlDbType.VarChar, 50);        comm.Parameters["NewPayment"].Value = newPayment;        comm.Parameters.Add("NewInvoice", System.Data.SqlDbType.VarChar, 50);        comm.Parameters["NewInvoice"].Value = newInvoice;        comm.Parameters.Add("NewAmount", System.Data.SqlDbType.Money);        comm.Parameters["NewAmount"].Value = newAmount;        comm.Parameters.Add("NewMargin", System.Data.SqlDbType.VarChar, 50);        comm.Parameters["NewMargin"].Value = newMargin;        comm.Parameters.Add("NewProfit", System.Data.SqlDbType.Money);        comm.Parameters["NewProfit"].Value = newProfit;        comm.Parameters.Add("NewEditDate", System.Data.SqlDbType.DateTime);        comm.Parameters["NewEditDate"].Value = newEditDate;        comm.Parameters.Add("NewQStatus", System.Data.SqlDbType.VarChar, 50);        comm.Parameters["NewQStatus"].Value = newQStatus;        comm.Parameters.Add("NewStatus", System.Data.SqlDbType.VarChar, 50);        comm.Parameters["NewStatus"].Value = newStatus;        // Enclose database code in Try-Catch-Finally        try        {            conn.Open();            comm.ExecuteNonQuery();        } 

View 7 Replies View Related

Getting Totals From A User Stored Procedure To Asp.net Code

Dec 1, 2003

Hi. I have records that have a status of either approved, not approved or unknown. I want to create a crystal report that shows a summary of the records - in short how many of each and what percentage. How can I do this? The approved, not approved and unknown are not number columns so I can't add them to get a total. I know I can create a cursor in T-SQL to get the counts but how do I send them back to the asp.net code?
---------------------
Here is part of the cursor I have created.
IF @@FETCH_STATUS = 0
BEGIN
IF @txtPrintSuit = 'U' SELECT @numUnkCount = @numUnkCount + 1
IF @txtPrintSuit = 'Y' SELECT @numSuitCount = @numSuitCount + 1
IF @txtPrintSuit = 'N' SELECT @numUnsuitCount = @numUnsuitCount + 1

FETCH curs_Briefs INTO @txtPrintSuit
END
------------
How do I get @numUnkCount, @numSuitCount and @numUnsuitCount back to the asp.net application? Can the RETURN statement hold more than one value?

THanks in advance

View 4 Replies View Related

How To Decrypt The SQL Encrypted Stored Procedure In ASP.NET (i.e.vb Code)

Oct 19, 2005

How to Decrypt the SQL Encrypted Stored procedure in ASP.NET (i.e.vb code)Pls let me know ASAP.Thanks.

View 1 Replies View Related

Sending Messages To .NET Code From Stored Procedure

Nov 10, 2007



I have some long running stored procedures that I invoke from ADO.NET (using Typed Datasets).

Inside the stored procedure, I like to keep track of the execution by using the PRINT command.

Is there a way of extracting and displaying this PRINT information in .NET during the stored procedure execution?

View 4 Replies View Related

Writing Code Vs Using Stored Procedure Within SSIS

Aug 7, 2007



Was wondering which is better, writing the SQL code within the execute SQL task or calling a SP from within it with respect to performance? and what is the best practice? Also is there any way to incoporate the logic of a cursur within SSIS?

Thanks

View 3 Replies View Related

Using Native-code COM From Unsafe CLR Stored Procedure?

Sep 27, 2006

Hello, everyone.

I have to make a gateway to access third-party closed-source native-code COM objects. As an interface, SQL Server stored procedures are absolutely perfect for my needs. I decided to use SQL Server 2005 CLR rather than using deprecated extended stored procedures or accessing COM objects directly through SQL Server. (This decision is aided by the fact that the third party has plans to make a .NET version of their COM objects "really soon now" (i.e.: not soon enough). Backporting their new interface to my abstraction layer will be very simple.)

I'm having problems using these COM objects from my SQL Server 2005 CLR stored procedure. When I try to run my stored procedure, I get the error below. It appears that SQL Server/CLR refuses to perform the disk access necessary to load the COM object from disk so that it can instantiate it. (My message continues after the error.)

Msg 6522, Level 16, State 1, Procedure MyStoredProcedure, Line 0
A .NET Framework error occurred during execution of user defined routine or aggregate 'MyStoredProcedure':
System.IO.FileLoadException: LoadFrom(), LoadFile(), Load(byte[]) and LoadModule() have been disabled by the host.
System.IO.FileLoadException:
at System.Reflection.Assembly.nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, Assembly locationHint, StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean forIntrospection)
at System.Reflection.Assembly.InternalLoad(AssemblyName assemblyRef, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
at System.Reflection.Assembly.InternalLoadFrom(String assemblyFile, Evidence securityEvidence, Byte[] hashValue, AssemblyHashAlgorithm hashAlgorithm, Boolean forIntrospection, StackCrawlMark& stackMark)
at System.Reflection.Assembly.LoadFrom(String assemblyFile, Evidence securityEvidence, Byte[] hashValue, AssemblyHashAlgorithm hashAlgorithm)
at System.Activator.CreateComInstanceFrom(String assemblyName, String typeName, Byte[] hashValue, AssemblyHashAlgorithm hashAlgorithm)
at System.Activator.CreateComInstanceFrom(String assemblyName, String typeName)
at UserDefinedFunctions.MyStoredProcedure()
.

I'll outline the steps I performed so far to get the assembly working:
Ensured I was running at SQL Server 2005 compatibility level. (90)
- Installed and configured the third-party COM objects on my devel box and my DB box. They registered themselves properly.
- Enabled CLR in the DB.
- Made sure the DB owner has UNSAFE ASSEMBLY permission.
- Added the TRUSTWORTHY property to the DB. (Yes, this isn't the "correct" way to do it, but I didn't want to deal with code-signing voodoo. Apologies to actual believers in voodoo.)
- Created an Unsafe assembly "MyAssembly" in SQL Server 2005 and Visual Studio 2005.
- Created all the necessary Interop assemblies (by creating a normal (non-SQL) C# project and building my code, and raiding the bin directory).
- Added these Interop assemblies to SQL Server 2005 as Unsafe assemblies.
- In VS2005, built my SQL Project code and deployed successfully.
- Tried running my stored procedure through a Query window, and I got the error above.

I've done many searches. All of the results I've found concern themselves with Web Services and XML serialization, and suggest using sgen.exe. I don't think they're relevant to my problem. A few said I should add my COM DLLs as assemblies to SQL Server, but that is impossible for native code DLLs. I found something that hinted at tblexp.exe, but I didn't understand how that could help me. I already figured out a way to get me the Interop libs.

Efforts at solving:
- Reading webpages for hours on end.
- I tried using sgen.exe to create the XML serialization assembly, but it didn't help.
- I tried adding various native-code DLLs directly as SQL Server assemblies, but you can bet it didn't like that.
- A few other pitiful attempts not worth mentioning.
- Reading this forum. I didn't see anything that applied.
- Posting here.

I really wish Microsoft had meant what they said when they stated that "Unsafe" assemblies can access anything, instead of trying to protect developers from their folly.

Any insights on getting those COM objects to work in my CLR stored procedure? Thank you.

-Tony Flay

View 3 Replies View Related

It Gives Code Page Error On Creating Stored Procedure !!!!!

Jun 28, 2006

Hi
I was transferring objects of my existing database from remote to local and I recieved this error during the DTS process. It was on the transfer of the following stored procedure. I tried to create this SP manually but its giving me the same error and doesn't create it. I don't know how to fix it.
I wonder if somebody can help me! Thanks in Advance
[450] Code page translations are not supported for the text data type. From: 1252 To: 1256.
CREATE  PROCEDURE dbo.Novasoft_DL_UpdateDownloadsRating @RatingID int,  @DownloadID int,  @UserID int,  @Rating int, @Comment text, @ReviewDate datetime, @CommentName nvarchar(50) ASUPDATE dbo.Novasoft_DL_DownloadsRating SET [DownloadID] = @DownloadID, [UserID] = @UserID, [Rating] = @Rating, [Comment] = @Comment, [ReviewDate] = @ReviewDate, [CommentName] = @CommentNameWHERE [RatingID] = @RatingID
GO

View 1 Replies View Related

Capture Return Value From Stored Procedure, Use Same In Code Behind Page

Apr 18, 2007

My stored procedure works and codes is working except I need to capture the return value from the stored procedure and use that value in my code behind page to indicate that a duplicate record entry was attempted.  In my code behind file (VB) how would I capture the value "@myERROR" then display in the label I have that a duplicate entry was attempted.
Stored ProcedureCREATE PROCEDURE dbo.usp_InsertNew @IDNumber         nvarchar(25), @ID  nvarchar(50), @LName  varchar(50), @FName  varchar(50)
AS
 DECLARE @myERROR int    -- local @@ERROR      , @myRowCount int  --local @@rowcountBEGIN  -- See if a contact with the same name and zip code exists IF EXISTS (Select * FROM Info   WHERE ID = @ID)   BEGIN    RETURN 1END ELSEBEGIN TRAN    INSERT INTO Info(IDNumber, ID, LName,             FName) VALUES (@IDNumber, @ID, @LName,             @FName)             SELECT @myERROR = @@ERROR, @myRowCount = @@ROWCOUNT If @myERROR !=0 GOTO HANDLE_ERROR
                               COMMIT TRAN RETURN 0 HANDLE_ERROR:  ROLLBACK TRAN  RETURN @myERROR      ENDGO
asp.net page<asp:SqlDataSource ID="ContactDetailDS" runat="server" ConnectionString="<%$ ConnectionStrings:EssPerLisCS %>"            SelectCommand="SELECT * FROM TABLE_One"                        UpdateCommand="UPDATE TABLE_One WHERE ID = @ID"                         InsertCommand="usp_InsertNew" InsertCommandType="StoredProcedure">                        <SelectParameters>                <asp:ControlParameter ControlID="GridView1" Name="ID" PropertyName="SelectedValue" />            </SelectParameters>             </asp:SqlDataSource>
 

View 2 Replies View Related







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