Triggers And Stored Procedures

Jun 19, 2006

I have to write a script that would make prefixes for a word

for ex: Ragini Venkataraman ( is the string) is the record from one table and i need to store prefix "rag" and "ven" as two records in another table....
I need to know if this can be done using just SQL stored procedures or triggers...or if there needs to be some code(java code..we are using language java) written..
any help on how it can be done would be great....iam completely new to sql and it all seems confusing....

thanks

View 4 Replies


ADVERTISEMENT

Stored Procedures && Triggers

Jul 21, 2007

 want to check, triggers are executed After the command is done right? not b4 isit? so if i want to check if the entry is to be Inserted, i should use SP instead? either that or i can use ROLLBACK right? how do i use ROLLBACK? finding Google also at the moment

View 6 Replies View Related

Triggers And Stored Procedures

Dec 5, 2001

Does anyone know of or have a method for tracking stored procedures that get called by a trigger. I have inherited a project that when a user updates a row in a particular table, triggers get fired and result in a nesting error. Thanks for your help in advance.

View 1 Replies View Related

Triggers And Stored Procedures

Dec 14, 2000

Hello,

Can I have a trigger call a stroed procedure and then in turn have the stored procedure call a COM object? If so how? Or where can I get the info to do it?

Thanks
Tony

tfeuz@sorcerysolutions.com

View 1 Replies View Related

Triggers Vs. Stored Procedures

Mar 13, 2003

IS trigger also precompiled and stored in memory on server like SP?

Is there any advantage of converting a trigger into SP?


Thanks in advance

jfkuser

View 1 Replies View Related

How To Run A Executable From Triggers Or Stored Procedures

Nov 16, 2001

Hi folks,

Is there is any way to run a executable from triggers or stored procedures..

Please let me know, whether it is possible in SQl Server 7.0.

Let me know asap...

rgds,
VJ

View 1 Replies View Related

Triggers, Extended Stored Procedures, And VB5/6

Sep 30, 1998

I would like to know if the the DLL`s one can build with VB5/6 can be used to construct Extended Stored Procedures in MS SQLServer 6.5? If so, how does one do this. All the reference material I`ve come across is for C

Thanks in Advance - Ralph

View 1 Replies View Related

Triggers, Extended Stored Procedures, And VB5/6

Sep 30, 1998

I would like to know if the the DLL`s one can build with VB5/6 can be used to construct Extended Stored Procedures in MS SQLServer 6.5? If so, how does one do this. All the reference material I`ve come across is for C

Thanks in Advance - Ralph

View 1 Replies View Related

Disabling Triggers From Stored Procedures

Mar 8, 2004

Hi,

Is it possible to disable a trigger from a Stored Procedure? If it is, how do you do it?


Thanks,


Federico

View 4 Replies View Related

T-sql Try Catch - Using Triggers And Nested Stored Procedures

Jan 7, 2008

For every trigger and stored procedure I have a try-catch that writes to an error_log table.
The problem is the inner error is not preserved, always get:
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.

As seen below - though commented out:
I tried commiting any transactions - though I didn't create one.
I played with the
XACT_STATE though that was 0
My test case was last procedure has 1/0

Thanks,
Russ
-----------------------------------------------------------

Below is what I have

Step 1)

ALTER Trigger [trg_ActivityLogEntryReportsError] ON [dbo].[ActivityLog]
FOR INSERT AS

DECLARE @ActivityLogID int
,@AlertMessageTypeID int
,@comment nvarchar(max)
,@Error_Source nvarchar(max)
--- etc.
SELECT
@ActivityLogID = ActivityLogID
,@AlertMessageTypeID = AlertMessageTypeID
,@Comment = Comment
FROM INSERTED
BEGIN TRY

if @AlertMessageTypeID = 2 -- activity reported an error
begin
exec proc_CreateAlertLogEntry_forError
@ActivityLogID
,@Comment

update ActivityLog
set flgActivityChecked = 1
where @activityLogId = activityLogID
end
END TRY


BEGIN CATCH
select
@Error_Source = 'trg_ActivityLogEntryReportsError '
,@Error_Procedure = ERROR_Procedure()
--- etc.
INSERT INTO ERROR_LOG
( Error_Source
,Error_Procedure
,Error_Message
--- etc.
)
VALUES
(
@Error_Source
,@Error_Procedure
,@Error_Message
---etc.
,@Error_Comment )
-- if @@TRANCOUNT > 0
--begin
--commit
--end
END CATCH

Step 2)

/*
This will be called by a Trigger
*/
ALTER Procedure [dbo].[proc_CreateAlertLogEntry_forError]
(@ActivityLogID int
,@Comment nvarchar(max))
AS

Declare
@ProcessScheduleID int
,@ProcessID int
--,@comment nvarchar(max)
,@Error_Source nvarchar(max)
---etc
BEGIN TRY
insert into AlertLog
(
AlertMessageTypeID
,comment
,ActivityLogID
)
values
(
2 -- error
,@comment
,@ActivityLogID
)

end

END TRY


BEGIN CATCH
PRINT 'ERROR OCCURED'
PRINT ERROR_Procedure() + ' ' + ERROR_MESSAGE()
select
@Error_Source = 'proc_CreateAlertLogEntry_forError '
---etc.
INSERT INTO ERROR_LOG
( Error_Source
,Error_Procedure
,Error_Message
---etc.
)
VALUES
(
@Error_Source
,@Error_Procedure
,@Error_Message
--- etc.)

-- if @@TRANCOUNT > 0
--begin
--commit
--end
END CATCH

update ActivityLog
set
flgActivityChecked = 1
,UpdatedDate = getdate()
,UpdatedBy = suser_sname()
where
ActivityLogID = @ActivityLogID


STEP 3

ALTER Trigger [trg_AlertLogEntry_SendsOnInsert] ON [dbo].[AlertLog]
FOR INSERT AS

declare
@AlertLogID Int
,@AlertMessageTypeID int
,@Comment nvarchar(max)
,@Error_Source nvarchar(max)
,@Error_Procedure nvarchar(max)
,@Error_Message nvarchar(max)
--- etc.
SELECT
@AlertLogID = AlertLogID
,@AlertMessageTypeID = AlertMessageTypeID
,@Comment = isnull(Comment,'')
,@ActivityLogID = isnull(ActivityLogID,-1)
FROM INSERTED

BEGIN TRY

PRINT 'trg_AlertLogEntry_SendsOnInsert'
PRINT @COMMENT
exec proc_SendEmail
@AlertLogID
,@AlertMessageTypeID
,@comment
,@ActivityLogID


END TRY


BEGIN CATCH

select
@Error_Source = 'trg_AlertLogEntry_SendsOnInsert '
,@Error_Procedure = ERROR_Procedure()
,@Error_Message = ERROR_MESSAGE()
--- etc.
INSERT INTO ERROR_LOG
( Error_Source
,Error_Procedure
-- etc.)
VALUES
(
@Error_Source
,@Error_Procedure
,@Error_Message
---etc.)
-- if @@TRANCOUNT > 0
--begin
--commit
--end
END CATCH



STEP 4

ALTER Procedure [dbo].[proc_SendEmail]
(
@AlertLogID Int
,@AlertMessageTypeID int
,@Comment nvarchar(max) = ''
,@ActivityLogID int = -1
)


AS

declare @AlertSubject nvarchar(512)
,@AlertBody nvarchar(max)
,@myQuery nvarchar(512)
,@profile_name1 nvarchar(128)
,@return_value int
,@mymailitem int
,@Error_Source nvarchar(max)
---etc.
,@Error_Comment nvarchar(max)
,@Test int
/*
@return_value int -- not using at this point but 0 is OK 1 is failure
@mymailitem int -- not using now could store mailitem_id which is on msdb.dbo.sysmail_mailitems
sysmail_mailitems.sent_status could be either 0 new, not sent, 1 sent, 2 failure or 3 retry.
*/

select top 1 @profile_name1 = [name] from msdb.dbo.sysmail_profile
order by profile_id

set @profile_name1 = rtrim(ltrim(@profile_name1))
print '@profile_name1: ' + @profile_name1
print '@comment: ' + @comment
Declare @CrsrRecipient Cursor

BEGIN TRY
PRINT 'proc_SendEmail'
--set @test = 1/0 'test crashing
select
@AlertSubject = 'AlertSubject'
,@AlertBody = 'AlertBody'

,@recipients = 'russ@test.com'


print 'sending email ' + CAST(getdate() as nvarchar(100))
EXEC @return_value = msdb.dbo.sp_send_dbmail
@profile_name = @profile_name1
,@recipients = @EMAILID
,@body = @AlertBody
,@subject = @AlertSubject
,@mailitem_id = @mymailitem OUTPUT

print 'Done ' + CAST(getdate() as nvarchar(100))
print cast(@return_value as nvarchar(100))



update alertlog
set AlertSendStatusID = 1 --sent
where
@AlertLogID = AlertLogID

END TRY

BEGIN CATCH
PRINT 'ERROR OCCURED'
PRINT ERROR_Procedure() + ' ' + ERROR_MESSAGE()
select
@Error_Source = ' proc_SendEmail '
,@Error_Procedure = ERROR_Procedure()
--- etc.
INSERT INTO ERROR_LOG
( Error_Source
,Error_Procedure
---etc.)
VALUES
(
@Error_Source
,@Error_Procedure
,@Error_Message
--- etc.)


update alertlog
set AlertSendStatusID = 2 --error
where
@AlertLogID = AlertLogID

--if @@TRANCOUNT > 0
--begin
--commit
--end
END CATCH

View 5 Replies View Related

Problematic Stored Procedures Using Views With Triggers

May 9, 2006

Hi,

I would like to use the view of the company data in the following stored procedures

without needing to pass a value into the CompanyID identity column. Is there a way to do this? The following code contains the view, the insert trigger on the view, and the desired stored procedures.

Also, this code is given me problem as well the error message says it cannot convert varchar to int. This code is located in the insert trigger.

INSERT INTO State(StateName)
VALUES(@StateName)

Here is the code of the company view, notice the C.CompanyID it is used in the GetCompany stored procedure.

SELECT C.CompanyID, C.CompanyName, A.Street, A.City, S.StateName, A.ZipCode, A.Country, P.PhoneNumber


FROM Company AS C INNER JOIN
Address AS A ON C.AddressID = A.AddressID INNER JOIN
State AS S ON A.StateID = S.StateID INNER JOIN
Phone AS P ON C.PhoneID = P.PhoneID

Here is code for the insert trigger on the view

CREATE TRIGGER InsertTriggerOnCustomerView
ON ViewOfCompany
INSTEAD OF INSERT
AS
BEGIN

DECLARE @CompanyName VARCHAR(128)
DECLARE @AddressID INT
DECLARE @Street VARCHAR(256)
DECLARE @City VARCHAR(128)
DECLARE @StateID INT
DECLARE @StateName VARCHAR(128)
DECLARE @ZipCode INT
DECLARE @Country VARCHAR(128)
DECLARE @PhoneID INT
DECLARE @PhoneNumber VARCHAR(32)
--DECLARE @CompanyID INT

SELECT
--@CompanyID = CompanyID,
@CompanyName = CompanyName,
@Street = Street,
@City = City,
@StateName = StateName,
@ZipCode = ZipCode,
@Country = Country,
@PhoneNumber = PhoneNumber
FROM INSERTED

INSERT INTO State(StateName)
VALUES(@StateName)

SET @StateID = @@IDENTITY

INSERT INTO Address(Street, City, StateID, Country, ZipCode)
VALUES(@Street, @City, @StateID, @ZipCode, @Country)

SET @AddressID = SCOPE_IDENTITY()

INSERT INTO Phone(PhoneNumber)
VALUES(@PhoneNumber)

SET @PhoneID = SCOPE_IDENTITY()

INSERT INTO Company(CompanyName, AddressID, PhoneID)
VALUES(@CompanyName, @AddressID, @PhoneID)

END

The stored procedures are here.

CREATE PROCEDURE GetCompany
(
@CompanyID INT
)
AS
BEGIN
SELECT CompanyName,Street, City, StateName,
ZipCode, Country, PhoneNumber
FROM
ViewOfCompany
WHERE
CompanyID = @CompanyID
END
GO

CREATE PROCEDURE AddCompany
(
@CompanyName VARCHAR(128),
@Street VARCHAR(256),
@City VARCHAR(128),
@StateName VARCHAR(128),
@ZipCode VARCHAR(128),
@Country VARCHAR(128),
@PhoneNumber VARCHAR(32)
)
AS
BEGIN
INSERT INTO ViewOfCompany
VALUES(@CompanyName, @Street, @City, @StateName,
@ZipCode, @Country, @PhoneNumber)
END
GO

View 3 Replies View Related

Pro SQLCLR 2005 CLR Stored Procedures, Functions, And Triggers TOC

Aug 15, 2006

For those intersted here is our TOC and the book's link. You can preorder at this point. We are sticking to the Nov. timeframe, but we may get it done sooner.

Chapter 1   Introducing SQLCLR
Chapter 2   Building a Procedure
Chapter 3   SQLCLR Strucutre & Common Tasks
Chapter 4   Creating Objects
Chapter 5   Compare & Contrast
Chapter 6   Replacing TSQL Objects
Chapter 7   Using the Base Library
Chapter 8   Using Procedures in Apps
Chapter 9   Error Handling
Chapter 10 Administration
Chapter 11 Case Study

Here is the link:

http://www.wrox.com/WileyCDA/WroxTitle/productCd-0470054034.html

Enjoy,

Derek

View 2 Replies View Related

Fastest Way To Delete Hundreds Of Table Triggers And Hundreds Of Stored Procedures?

Jul 20, 2005

How can i delete all user stored procedures and all table triggers very fastina single database?Thank you

View 17 Replies View Related

Triggers And Procedures

Oct 23, 2003

Hi guys,
I am really new to the Database triggers and procedures
and I just need you to recommend some links or books
that will explain them to me

coz I'm gonna have to use them soon.

Any help will be appreciated.

Thank you in advance.

View 2 Replies View Related

Triggers For Procedures

Nov 29, 2006

is there a trigger that will be activated whena procedure is carried out?

View 13 Replies View Related

HELP!!! ASAP!! Procedures And Triggers

Oct 16, 2005

I need to create a procedure to output mean and deviation AND then I also need to create a trigger. Below is the table 'Account' I made.. but the procedure and trigger I made below that is not correct. Can someone revise this for me? I just can't figure it out.

Here is the 'Account' table I made earlier:

create table Account(Account_Number varchar(6)NOT NULL PRIMARY KEY, Branch_Name varchar(12)NOT NULL REFERENCES Branch (Branch_Name), Balance money)

insert into Account values(10101, 'First Bank', 5500)

insert into Account values(20202, 'US Bank', 3550)

insert into Account values(30303, 'Commerce', 7550)

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

Now I need to output the Mean and Standard Deviation of the above Account. So I tried this code but I'm not sure if its right since when I Analyze it, some values come out as 'null'.

CREATE PROCEDURE project2question2 AS

DECLARE @Mean MONEY

DECLARE @Deviation MONEY

SET @Mean = (SELECT avg(cast(Balance as float)) as mean from Account)

SET @Deviation = (SELECT Balance, STDEV(Balance) st_deviation

FROM Account

GROUP BY Balance)

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

The second question was to create a Trigger that would output the name "Account" and the time of change/update to it. So I had this so far, but I KNOW its not right, so can anyone revise this for me? I basically need to create a trigger named ActionHistory that would have two columns: TableName and ActionTime. They should tie to my Account table so that when it was changed/updated then the name 'Account' would appear under TableName in my trigger, and the datetime of update would appear under ActionTime. But I just can't figure this one out.

Create table ActionHistory(TableName varchar(12) NOT NULL, ActionTime datetime NOT NULL)

CREATE TRIGGER trigger_ex2

ON ActionHistory

AFTER INSERT, UPDATE

AS

BEGIN

UPDATE ActionHistory

SET Account = UPPER(LName) WHERE TableName in (SELECT TableName FROM ActionHistory)

SET Loan = UPPER(LName) WHERE IDN in (SELECT TableName FROM ActionHistory)

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

If someone could give me these codes I would be very grateful. I know you guys are the SQL wizzards. Thanks. (by the way, I use SQL 2000 edition)

- SOnia

View 1 Replies View Related

Oracle Stored Procedures VERSUS SQL Server Stored Procedures

Jul 23, 2005

I want to know the differences between SQL Server 2000 storedprocedures and oracle stored procedures? Do they have differentsyntax? The concept should be the same that the stored proceduresexecute in the database server with better performance?Please advise good references for Oracle stored procedures also.thanks!!

View 11 Replies View Related

Stored Procedures 2005 Vs Stored Procedures 2000

Sep 30, 2006

Hi,



This Might be a really simple thing, however we have just installed SQL server 2005 on a new server, and are having difficulties with the set up of the Store Procedures. Every time we try to modify an existing stored procedure it attempts to save it as an SQL file, unlike in 2000 where it saved it as part of the database itself.



Thank you in advance for any help on this matter



View 1 Replies View Related

All My Stored Procedures Are Getting Created As System Procedures!

Nov 6, 2007



Using SQL 2005, SP2. All of a sudden, whenever I create any stored procedures in the master database, they get created as system stored procedures. Doesn't matter what I name them, and what they do.

For example, even this simple little guy:

CREATE PROCEDURE BOB

AS

PRINT 'BOB'

GO

Gets created as a system stored procedure.

Any ideas what would cause that and/or how to fix it?

Thanks,
Jason

View 16 Replies View Related

FoxPro Triggers Call FoxPro Stored Proc Calls SQL Server Stored Procedure

Mar 10, 2005

I didn't want to maintain similar/identical tables in a legacy FoxPro system and another system with SQL Server back end. Both systems are active, but some tables are shared.

Initially I was going to use a Linked Server to the FoxPro to pull the FP data when needed. This works. But, I've come up with what I believe is a better solution. Keep in mind that these tables are largely static - occassional changes, edits.

I will do a 1 time DTS from FP into SQL Server tables.

I then create INSERT and UPDATE triggers within FoxPro.

These triggers fire a stored procedure in FoxPro that establishes a connection to the SQL Server and fire the appropriate stored procedure on SQL Server to CREATE and/or UPDATE the corresponding table there.

In the end - the tables are local to both apps.

If the UPDATES or TRIGGERS fail I write to an error log - and in that rare case - I can manually fix. I could set it up to email me from within FoxPro as well if needed.

Here's the FoxPro and SQL Server code for reference for the Record Insert:

FOXPRO employee.dbf InsertTrigger:
employee_insert_trigger(VAL(Employee.ep_pk),Employ ee.fname,Employee.lname,Employee.email,Employee.us er_login,Employee.phone)

FOXPRO corresponding Stored Procedure:
FUNCTION EMPLOYEE_INSERT_TRIGGER
PARAMETERS wepk,wefname,welname,weemail,WEUSERID,WEPHONE

nhandle=SQLCONNECT('SS_PDITHP3','userid','password ')

IF nhandle<0
m.errclose=.f.
IF !USED("errorlog")
USE tisdata!errorlog IN SELECT(1)
m.errclose=.t.
ENDIF

SELECT errorlog
INSERT INTO errorlog (date, time, program,source,user) ;
values (DATE(), TIME(), 'EMPLOYEE_INSERT_TRIGGER','nhandle<0 PARAMS: '+STR(wepk)+wefname+welname+weemail+WEUSERID+WEPHO NE,GETENV("username"))

IF m.errclose
USE IN errorlog
ENDIF
RETURN

ENDIF
nquery="exec ewo_sp_insertNewEmployee @WEPK ="+STR(wepk)+",@WEFNAME ='"+wefname+"',@WELNAME ='"+welname+"',@WEEMAIL ='"+weemail+"',@WEUSERID ='"+weuserid+"',@WEPHONE='"+wephone+"',@RETCODE =0"
nsucc=SQLEXEC(nhandle,nquery)

SQLDISCONNECT(nhandle)

IF nSucc<0
m.errclose=.f.
IF !USED("errorlog")
USE tisdata!errorlog IN SELECT(1)
m.errclose=.t.
ENDIF

SELECT errorlog
INSERT INTO errorlog (date, time, program,source,user) ;
values (DATE(), TIME(), 'EMPLOYEE_INSERT_TRIGGER','nSucc<0 PARAMS: '+STR(wepk)+wefname+welname+weemail+WEUSERID+WEPHO NE,GETENV("username"))

IF m.errclose
USE IN errorlog
ENDIF
ENDIF

RETURN

SQL SERVER Stored Procedure called from FOXPRO Stored Procedure
CREATE procedure ewo_sp_insertNewEmployee (
@WEPK int,
@WEFNAME char(20),
@WELNAME char(20),
@WEEMAIL char(50),
@WEUSERID char(15),
@WEPHONE char(25),
@RETCODE int OUTPUT
)

AS

insert into WO_EMP (
WE_PK,
WE_FNAME,
WE_LNAME,
WE_EMAIL,
WE_USERID,
WE_PHONE
)

VALUES (
@WEPK,
@WEFNAME,
@WELNAME,
@WEEMAIL,
@WEUSERID,
@WEPHONE
)


IF @@ERROR <> 0
BEGIN
SET @RETCODE=@@ERROR
END
ELSE
BEGIN
-- SUCCESS!!
SET @RETCODE=0
END

return @RETCODE
GO

View 2 Replies View Related

Stored Procedure Vs. Triggers

Aug 2, 2007

Which one is better when it comes to security and performance?

View 4 Replies View Related

How To Get Value From The Stored Procedure In Triggers

Dec 26, 2007

Hi

My doubt is

I have write a tigger and stored procedure

Stored procedure returns random password. Now I wnat to get that random password and store into temp variable in Trigger

Please send me the syntax

Thanks in advance

View 3 Replies View Related

How To Search And List All Stored Procs In My Database. I Can Do This For Tables, But Need To Figure Out How To Do It For Stored Procedures

Apr 29, 2008

How do I search for and print all stored procedure names in a particular database? I can use the following query to search and print out all table names in a database. I just need to figure out how to modify the code below to search for stored procedure names. Can anyone help me out?
 SELECT TABLE_SCHEMA + '.' + TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'

View 1 Replies View Related

Using A Stored Procedure To Query Other Stored Procedures And Then Return The Results

Jun 13, 2007

Seems like I'm stealing all the threads here, : But I need to learn :) I have a StoredProcedure that needs to return values that other StoredProcedures return.Rather than have my DataAccess layer access the DB multiple times, I would like to call One stored Procedure, and have that stored procedure call the others to get the information I need. I think this way would be more efficient than accessing the DB  multiple times. One of my SP is:SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived,     I.Expired, I.ExpireDate, I.Deleted, S.Name AS 'StatusName', S.ItemDetailStatusID,    S.InProgress as 'StatusInProgress', S.Color AS 'StatusColor',T.[Name] AS 'TypeName',    T.Prefix, T.Name AS 'ItemDetailTypeName', T.ItemDetailTypeID    FROM [Item].ItemDetails I    INNER JOIN Item.ItemDetailStatus S ON I.ItemDetailStatusID = S.ItemDetailStatusID    INNER JOIN [Item].ItemDetailTypes T ON I.ItemDetailTypeID = T.ItemDetailTypeID However, I already have StoredProcedures that return the exact same data from the ItemDetailStatus table and ItemDetailTypes table.Would it be better to do it above, and have more code to change when a new column/field is added, or more checks, or do something like:(This is not propper SQL) SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived,     I.Expired, I.ExpireDate, I.Deleted, EXEC [Item].ItemDetailStatusInfo I.ItemDetailStatusID, EXEC [Item].ItemDetailTypeInfo I.ItemDetailTypeID    FROM [Item].ItemDetails IOr something like that... Any thoughts? 

View 3 Replies View Related

How To Save Stored Procedure To NON System Stored Procedures - Or My Database

May 13, 2008

Greetings:

I have MSSQL 2005. On earlier versions of MSSQL saving a stored procedure wasn't a confusing action. However, every time I try to save my completed stored procedure (parsed successfully ) I'm prompted to save it as a query on the hard drive.

How do I cause the 'Save' action to add the new stored procedure to my database's list of stored procedures?

Thanks!

View 5 Replies View Related

Stored Procedure Being Saved In System Stored Procedures

Apr 7, 2006

We recently upgraded to SQL Server 2005. We had several stored procedures in the master database and, rather than completely rewriting a lot of code, we just recreated these stored procedures in the new master database.

For some reason, some of these stored procedures are getting stored as "System Stored Procedures" rather than just as "Stored Procedures". Queries to sys.Objects and sys.Procedures shows that these procs are being saved with the is_ms_shipped field set to 1, even though they obviously were not shipped with the product.

I can't update the sys.Objects or sys.Procedures views in 2005.

What effect will this flag (is_ms_shipped = 1) have on my stored procedures?

Can I move these out of "System Stored Procedures" and into "Stored Procedures"?

Thanks!

View 24 Replies View Related

How Can I Call One Or More Stored Procedures Into Perticular One Stored Proc ?

Apr 23, 2008

Hello friends......How are you ? I want to ask you all that how can I do the following ?
I want to now that how many ways are there to do this ?



How can I call one or more stored procedures into perticular one Stored Proc ? in MS SQL Server 2000/05.

View 1 Replies View Related

SSIS And Stored Procedures Results Stored In #Tables

Mar 26, 2008

Hello
I'm start to work with SSIS.

We have a lot (many hundreds) of old (SQL Server2000) procedures on SQL 2005.
Most of the Stored Procedures ends with the following commands:


SET @SQLSTRING = 'SELECT * INTO ' + @OutputTableName + ' FROM #RESULTTABLE'

EXEC @RETVAL = sp_executeSQL @SQLSTRING


How can I use SSIS to move the complete #RESULTTABLE to Excel or to a Flat File? (e.g. as a *.csv -File)

I found a way but I think i'ts only a workaround:

1. Write the #Resulttable to DB (changed Prozedure)
2. create data flow task (ole DB Source - Data Conversion - Excel Destination)

Does anyone know a better way to transfer the #RESULTTABLE to Excel or Flat file?

Thanks for an early Answer
Chaepp

View 9 Replies View Related

MS SQL Stored Procedures Inside Another Stored Procedure

Jun 16, 2007

Hi,
 Do you know how to write stored procedures inside another stored procedure in MS SQL.
 
Create procedure spMyProc inputData varchar(50)
AS
 ----- some logical
 
 procedure spMyProc inputInsideData varchar(10)
AS
   --- some logical
  ---  go
-------

View 5 Replies View Related

Calling Stored Procedures From Another Stored Procedure

May 8, 2008

I am writing a set of store procedures (around 30), most of them require the same basic logic to get an ID, I was thinking to add this logic into an stored procedure.

The question is: Would calling an stored procedure from within an stored procedure affect performance? I mean, would it need to create a separate db connection? am I better off copying and pasting the logic into all the store procedures (in terms of performance)?

Thanks in advance

John

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

Copy Db With Dtswizard Doesn't Export Triggers And Stored Procs

Jan 30, 2007

I am using dtswizard to export my db but it doesn't export triggers and all stored procedures. Am I doing anything wrong or is there a better way to do an exact copy of my DB? I am not able to use backup/restore because my db is on a server to which I don't have admin-right, and I have to pay my hoster to create a backup and email me. TIAThomas 

View 8 Replies View Related

A Stored Proc To Search All Procs, Views, Triggers And Functions

Jul 20, 2005

I developed a search stored proc that searches all orsome of Procs, Views, Triggers and functions. Would anyone be interestedto see it posted here?Do you have any suggestions about other places, websites forums ...., toshare something I have developed?Thanks you for your response in advancePLease send me emailJoin Bytes! {Remove ### before responding}

View 1 Replies View Related







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