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 fast
in
a single database?

Thank you

View 17 Replies


ADVERTISEMENT

Hundreds Of VIEWS

Feb 2, 2006

What are the downsides to have many multiple views on a SQL Server Database?

It was somebody's design decision to create 10 views for each vendor in our database- that could range from 20 to 500 vendors ... which means we could potentially have 5000+ views in our database.

What is the downside/problems with doing this?

View 17 Replies View Related

Hundreds Of Databases

Feb 2, 2008



Hello All,

We are running a hosted application where we currently have over 700 databases and expect to go to over 2000 within a year. This is all running currently on a 2 node fail-over cluster connected to a SAN.

I'm basically looking for advice on growth startegy from anyone that may have a similar setup.
Should I buy the biggest boxes I can afford and keep piling the databases on the same server? Should I add clusters and split the databases in bunches of, say, 500?

Performance is not bad right now but I'm not sure how long that will continue and I would rather plan than fight fires.

TIA

Andrew

View 1 Replies View Related

How To Import Hundreds Of CSV Files Into SQL Server?

Feb 5, 2004

Hi,
I have about 500 CSV files, mostly they are unser same structure. I need to import them all into SQL server. I create a new table for each one based on the firstline as the column name. I can do them one by one but it is very time consuming and boring. Is there a fast way of doing this?

If someone give me a information, I promise to share all of MY CSV files. They are the zone chart from UPS and I have edit all of them.

Thanks.

View 14 Replies View Related

Avoid Listing Hundreds Of Columns

Jun 3, 2014

i have a table with a few hundred columns. Each SELECT statement, I list each of the columns, this is taking lots and lots of space and it is difficult to review the code due to its length...Below, I have to list out every column when I only want to use case logic on 1 column. In the next step I will have to list out every single column again

SELECT
ACCT1_NO
,ACCT1_DT
,ACCT1_RISK
,ACCT1_RISK_WEIGHT
,ACCT1_CUSTOMER_NAME
,ACCT1_CUSTOMER_ADDRESS
,ACCT1_CUSTOMER_ST = CASE WHEN ACCT1_CUSTOMER_ST = ' ' THEN 'DC' END
,ACCT1_CUSTOMER_CITY
,ACCT1_CUSTOMER_ZIP
--,THIS CONTINUES DOWN FOR ANOTHER 150 OR SO ACCTS.
INTO #A1
FROM STAGE.CUSTOMER_ACCTS

Is there a way I can tell SQL to take all of the columns and then list the column where I want to do my case statement. Something like the code below

(which will fail as ACCT1_CUSTOMER_ST will be listed twice.
SELECT *
,ACCT1_CUSTOMER_ST = CASE WHEN ACCT1_CUSTOMER_ST = ' ' THEN 'DC' END
INTO #A1
FROM STAGE.CUSTOMER_ACCTS

View 1 Replies View Related

Can Synonyms Be Used To Replace Hundreds Of Lines?

Sep 10, 2006

Hello

I'm looking for a way to store a large chunck of text (200 lines) in a variable which can be called in different objects within the database.

For example: I have several stored procedures that create the same temp table which exists of 200 column names over and over again.

It would have to look like this

Import myScript (I keep thinking of Import like used in .NET)

and myScript could exist of:

CREATE #MyTable(
...)

or even some simple text or a part of a sql statement.

I'm familiar with synonyms but you can't use that in this scenario.
Then I thought of functions. The scalar function returns a result, not what I want here. Table valued function return tables, not what I want.

Many thanks in advance!

View 3 Replies View Related

SQL 2012 :: SSIS Variables - Change Value In Hundreds Place

May 21, 2015

in SSIS

( Left(@[User::YourFileName],3) == "AP_" ? 101 : ( Left(@[User::YourFileName],3) == "VD_" ? 102 : ( Left(@[User::YourFileName],3) == "BK_ " ? 103 : 000)))

In the above Variable i want to change the value in Hundreds place i.e from 101,102,103 to 201 ,202, 203 depending upon some Project param

lets say if project param is 1, the values assigned should be as 101,102,103

if project param is 2, the values assigned should be as 201,202,203

if project param is 3, the values assigned should be as 301,302,303 etc...

View 0 Replies View Related

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

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

Adding Delete To Stored Procedures

Aug 31, 2006

I have a stored procedure that Inserts data from one table to the next, I need to add a delete statement to it. I jusually just use the delete option in Access 2003 but I have decided it would be easier to just delete from original table through the sp, only problem is I dont rememeber how to incorporate it into my SP, although at one time I did have it in there then I took it out


REATE PROCEDURE InsertTerms
AS
INSERT INTO [GamingCommissiondb].[dbo].[TERMINATION] ( [TM #],
[FirstName],
[LastName],
[SocialSecurityNumber],
[DateHired],
[Status],
[Title],
[DepartmentName],
[Pictures])

SELECT a.TM#, a.FirstName, a.LASTNAME, a.SSN#, a.HIREDATE, a.STATUS, a.JOBTITLE, a.DEPT#, a.PICS
FROM EmployeeGamingLicense AS a
WHERE a.STATUS = 'TERMINATED'
IF @@Error <> '0'
RETURN


GO

View 4 Replies View Related

Is There An Easy Way To Delete Stored Procedures?

Mar 21, 2007

Hi All,

In SQL server 2000 enterprise manager you were able to select multiple stored procedures and drop them all in one go. In 2005 all the stored procs are listed differently (in a tree view) and you cannot therefore select multiple SP for deleteing. Is there another way to accomplish this?

Danny

View 3 Replies View Related

Update And Delete Stored Procedures Not Working

Feb 24, 2006

I grouped everything together so you see it all. I'm not getting any errors but nothing is happening. I had this working and then I converted to Stored Procedures and now it's not.
CREATE PROCEDURE UpdateCartItem(@itemQuantity int,@cartItemID varchar)ASUPDATE CartItems Set pounds=@itemQuantityWHERE cartItemID=@cartItemIDGO
<asp:Button CssClass="scEdit" ID="btnEdit" Runat="server" Text="Update" CommandName="Update"></asp:Button>
    Sub dlstShoppingCart_UpdateCommand(ByVal s As Object, ByVal e As DataListCommandEventArgs)        Dim connStr As SqlConnection        Dim cmdUpdateCartItem As SqlCommand        Dim UpdateCartItem        Dim strCartItemID As String        Dim txtQuantity As TextBox        strCartItemID = dlstShoppingCart.DataKeys(e.Item.ItemIndex)        txtQuantity = e.Item.FindControl("txtQuantity")        connStr = New SqlConnection(ConfigurationSettings.AppSettings("sqlCon.ConnectionString"))        cmdUpdateCartItem = New SqlCommand(UpdateCartItem, connStr)        cmdUpdateCartItem.CommandType = CommandType.StoredProcedure        cmdUpdateCartItem.Parameters.Add("@cartItemID", strCartItemID)        cmdUpdateCartItem.Parameters.Add("@itemQuantity", txtQuantity.Text)        connStr.Open()        cmdUpdateCartItem.ExecuteNonQuery()        connStr.Close()        dlstShoppingCart.EditItemIndex = -1        BindDataList()    End Sub
____________________________________________________________
CREATE PROCEDURE DeleteCartItem(@orderID Float(8),@itemID nVarChar(50))ASDELETEFROM CartItemsWHERE orderID = @orderID AND itemID = @itemIDGO
<asp:Button CssClass="scEdit" ID="btnRemove" Runat="server" Text="Remove" CommandName="Delete"></asp:Button>
    Sub dlstShoppingCart_DeleteCommand(ByVal s As Object, ByVal e As DataListCommandEventArgs)        Dim connStr As SqlConnection        Dim cmdDeleteCartItem As SqlCommand        Dim DeleteCartItem        Dim strCartItemID        strCartItemID = dlstShoppingCart.DataKeys(e.Item.ItemIndex)        connStr = New SqlConnection(ConfigurationSettings.AppSettings("sqlCon.ConnectionString"))        cmdDeleteCartItem = New SqlCommand(DeleteCartItem, connStr)        cmdDeleteCartItem.CommandType = CommandType.StoredProcedure        cmdDeleteCartItem.Parameters.Add("@cartItemID", strCartItemID)        connStr.Open()        cmdDeleteCartItem.ExecuteNonQuery()        connStr.Close()        dlstShoppingCart.EditItemIndex = -1        BindDataList()    End Sub

View 2 Replies View Related

SQL 2012 :: Generate Stored Procedures For Select / Insert / Update / Delete On Certain Tables?

Apr 3, 2015

Is there a way in SQL server that can generate stored procedures for select, insert, update, delete on certain tables?

View 4 Replies View Related

SQL Server 2012 :: Stored Procedures Compiles Even When There Is No CREATE TABLE For A Temp Table

Feb 11, 2015

i am inserting something into the temp table even without creating it before. But this does not give any compilation error. Only when I want to execute the stored procedure I get the error message that there is an invalid temp table. Should this not result in a compilation error rather during the execution time.?

--create the procedure and insert into the temp table without creating it.
--no compilation error.
CREATE PROC testTemp
AS
BEGIN
INSERT INTO #tmp(dt)
SELECT GETDATE()
END

only on calling the proc does this give an execution error

View 3 Replies View Related

Table Names In Stored Procedures As String Variables And Temporary Table Question

Apr 10, 2008

How do I use table names stored in variables in stored procedures?




Code Snippetif (select count(*) from @tablename) = 0 or (select count(*) from @tablename) = 1000000





I receive the error 'must declare table variable '@tablename''

I've looked into table variables and they are not what I would require to accomplish what is needed.
After browsing through the forums I believe I need to use dynamic sql particuarly involving sp_executesql. However, I am pretty new at sql and do not really understand how to use this and receive an output parameter from it(msdn kind of confuses me too). I am tryin got receive an integer count of the records from a certain table which can change to anything depending on what the user requires.




Code Snippet

if exists(Select * from sysobjects where name = @temptablename)
drop table @temptablename




It does not like the 'drop table @temptablename' part here. This probably wouldn't be an issue if I could get temporary tables to work, however when I use temporary tables i get invalid object '#temptable'.

Heres what the stored procedure does.
I duplicate a table that is going to be modified by using 'select into temptable'
I add the records required using 'Insert into temptable(Columns) Select(Columns)f rom TableA'
then I truncate the original table that is being modified and insert the temporary table into the original.

Heres the actual SQL query that produces the temporary table error.




Code Snippet
Select * into #temptableabcd from TableA

Insert into #temptableabcd(ColumnA, ColumnB,Field_01, Field_02)
SELECT ColumnA, ColumnB, Sum(ABC_01) as 'Field_01', Sum(ABC_02) as 'Field_02',
FROM TableB
where ColumnB = 003860
Group By ColumnA, ColumnB

TRUNCATE TABLE TableA

Insert into TableA(ColumnA, ColumnB,Field_01, Field_02)
Select ColumnA, ColumnB, Sum(Field_01) as 'Field_01', Sum('Field_02) as 'Field_02',
From #temptableabcd
Group by ColumnA, ColumnB




The above coding produces

Msg 208, Level 16, State 0, Line 1

Invalid object name '#temptableabcd'.

Why does this seem to work when I use an actual table? With an actual table the SQL runs smoothly, however that creates the table names as a variable problem from above. Is there certain limitation with temporary tables in stored procedures? How would I get the temporary table to work in this case if possible?

Thanks for the help.


View 6 Replies View Related

Triggers (delete)

Nov 13, 2006

Hi,

I have a parent table (Projects) and few child tables (Project invoices, Project Sub-consultants, etc). Typically, 'Projects' table contain details about projects. Other child tables contain specific information about a particular project, like invoices, details of sub-consultants, etc.

I introduced a 'After Delete' trigger in parent table, which should delete all rows in child tables, at once the row is deleted in parent table. The code I have used is as follows:

Declare @PID char(8) -- Project ID SET @PID = (Select ProjNo from Deleted)BEGIN delete from ProjInvoice where ProjectID = @PID END

Now, when I delete a row in parent table and see the child tables, only the particular field says 'NULL' and all other field remains. (I have tested the SQL statement as stand-alone query and it works fine).
What's wrong with the trigger?

Thanks in advance.

Sathya

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

Fastest Way To Remove Large Table?

Nov 22, 1999

We have a table that we BCP into, the data is then processed and inserted into its appropriate table.
Then the table or its data needs to be removed. This seems to be a very slow operation to remove
the table or table data. I have tried drop table, and truncate table and it takes nearly as long as
the bcp operation. The table has 12 million rows. I didn't think either operation wrote to the
transaction log except for page extent management. Why is the drop and truncate so slow. Suggestions?

View 1 Replies View Related

How To Return A Table With Stored Procedures?

Jun 20, 2007

i have a procedure like below but it dosen't return any tableSELECT Hardwares.HWID, Hardwares.Title
FROM Hardwares INNER JOIN
Category ON Hardwares.CategoryID = Category.CategoryID
WHERE (Category.Type LIKE '%Hardware%') AND (Category.Title = @Title)   

View 3 Replies View Related

Table Parameters In Stored Procedures

Dec 9, 2007

How can I pass a name of a table to a stored procedure.
I want to pass the name as a parameter. The table already exists in the db.
After that, I will do
"SELECT .....
FROM @tableparameter"
What is the right way to do that?

View 3 Replies View Related

CLR Stored Procedures Table Permissions

Jul 4, 2013

I have an app which calls a SP, which in turn calls a CLR Stored Procedure.

The CLR stored procedure calls a number of different tables, using a Context connection string.

The issue is that the CLR SP requires the user to have permissions to the tables directly, instead of just permissions to the SP which was expected.

If I just give permission to the SP, then the CLR SP fails. So I then add the table permissions, and it then works.

So the question is, how do I raise security so the app does not have permissions on the tables?

View 5 Replies View Related

Stored Procedures/ Table Permissions

Jul 20, 2005

Hi, is there any way that I can automate granting user permissions totables/ stored procedures in SQL server 2000?I have a whole bunch of tables and rather than having to right click eachtable/ then permissions in Enterprise manager I would like to be able toiterate through each table object in a database and grant the relevantpermissions.... Same with stored procedures.Is this possible?? If so, how can I do itThanks in advanceMark

View 2 Replies View Related







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