SQL Server 2008 :: Create A Trigger With A Stored Procedure?

Jun 5, 2012

I am trying to create a trigger with in a stored procedure. When I execute the stored procedure I am getting the following error :

Msg 2108, Level 15, State 1, Procedure JPDSAD1, Line 1

Cannot create trigger on 'FRIT_MIP003_BOK_BTCH_LG.P62XB1.XB1PDS' as the target is not in the current database.

Here is the code for the stored procedure :

CREATE PROCEDURE [dbo].[InsertTRIGGER](@databaseA varchar(50))
AS
BEGIN
exec ('USE ['+@databaseA+'];')
exec ('CREATE TRIGGER [P62XB1].[JPDSAD1] ON [' + @databaseA + '].[P62XB1].[XB1PDS] ' +
'AFTER DELETE AS ' +
'BEGIN ' +
' INSERT INTO [' + @databaseA + '].[P62XB1].[XL1TDS] SELECT CAST(SYSDATETIME() AS DATETIME2(6)) , ''B'' , ''D'' , IDA_DELETE ' +
' ''0001-01-01 00:00:00.000000'' , '' '' FROM DELETED ' +
'END')
END

View 5 Replies


ADVERTISEMENT

SQL Server 2008 :: Trigger To Show Which Stored Procedure Has Updated A Table

Jul 9, 2015

I am looking to created a trigger that inserts records into a log table to show the stored porcedure that has updated a specific column to a specific value in a specific table

At present I have:

CREATE TRIGGER [dbo].[trUpdaterTable]
ON [dbo].[t_account]-- A DB level trigger
FOR UPDATE
--Event we want to capture
AS
IF update (document_status)

[Code] ...

However this doesn't appear to bring back the procedure that triggered the update...

The value the trigger should update on is document_status = 0

DDLProcExecutedByEventDate
NULLNULLLOMBDAadministrator2015-06-25 07:42:01.677
NULLNULLLOMBDA im64602015-06-25 07:51:34.503
NULLNULLLOMBDAadministrator2015-06-25 07:52:01.610
NULLNULLLOMBDAadministrator2015-06-25 08:02:01.417
CREATE TRIGGER [dbo].[trTableupdateaccount] ON [DoesMore].[dbo].[t_account]

[Code] ....

View 9 Replies View Related

Stored Procedure - Create Trigger File In Particular Directory

Oct 26, 2012

I created stored procedure to create trigger file in a particular directory using xp_cmdshell.

I am calling the procedure from windows batch script as follows

@@set osq200=osql /a 4096 /b /E /e /d %dbn% /m-1 /r 0 /S %dbs% /Q "exec SP_Create_TriggerFile %2,%1 "
@%osq200% >>%3rec.txt 2>%3rec_err.txt
@set dberr=%errorlevel%
@if %dberr% GTR 0 goto createDATriggerFileErr >>%3rec.txt

If the directory doesn't exist, its throwing error "The system cannot find the path specified" , but the %errorlevel% still showing as 0..

View 1 Replies View Related

SQL Server 2008 :: Search For A Stored Procedure

Jun 25, 2015

I would like to search for a particular stored procedure written by a developer. I know the name of the procedure but in which db is it residing in. There are 40 databases in this SQL 2008 instance. I search on the name column in sys.all_objects table and it does not return anything. I end up querying sys.procedures on each database to locate the procedure. Is there a system table/view that I can query to look for a procedure, instead of querying sys.procedures on each database one by one?

View 1 Replies View Related

SQL Server 2005 Trigger Or Stored Procedure

Apr 10, 2008

I need to create either a trigger or stored procedure in SQL server 2005(hopefully someone can tell me).. Here is what I need to happen: I have a table with orders that are generated from a website. After the transaction is completed, I need have the record that was just created also copy to another table. There is a field called flag and the values in this field are either 1 or 2. Imediatly after the transaction occurs, I need the records where flag = 1 to copy to this other table. How would I go about doing this?

View 10 Replies View Related

SQL Server 2008 :: Using Execute As And Encryption In Stored Procedure

Oct 7, 2013

I am search for coding criteria I need create a stored procedure with execute as and along with encryption. How can I use the same ? My main motive is to create proc with execute as a user also at the same time I need to encrypt the same from other users seeing the code.

The below query is getting errors:

Create procedure testproc
with execute as 'user' and with encryption
as truncate table some table

View 3 Replies View Related

SQL Server 2008 :: Allow User Only Alter Stored Procedure

Aug 10, 2015

I want that I will allow a user only to select data from any object and only to alter an existing stored procedure or view. That user can not drop and create any stored procedure and view.

I can assign that user db_datareader role, grant view definition but if I grant alter permission, that user can create, alter and drop any stored procedure and view.

View 1 Replies View Related

Trigger Executing Linked Server Stored Procedure

Jul 3, 2006

What is the syntax for creating a update trigger and passing the values which were updated to a stored procedure on a linked server?? Specifically need syntax for updated value, as well as the syntax for executing the stored proc on the linked server.

Thank you

View 1 Replies View Related

SQL Server 2008 :: Need To Recompile Stored Procedure After A Table Alter?

Feb 5, 2015

Version 2008 R2

The stored procedure has the dependency on the table that was altered.

View 4 Replies View Related

SQL Server 2008 :: Skip Code If Certain Condition Not Met In Stored Procedure

Mar 18, 2015

I have a stored procedure with several insert into statements. On occasion one of the insert into queries doesn't return any data. What is the best way to test for no records then, skip that query?

View 5 Replies View Related

SQL Server 2008 :: Stored Procedure Text String Replace

Apr 16, 2015

We have a legacy database that have hundreds of stored procedures.

The previous programmar uses a string like servername.databasename.dbo.tablename in the stored procedures.
We now have migrated the database to a new server. The old server is either needed to be replaced by the new server name, or remove it.

I don't know why he used servername as part of the fully qualified name, we don't use linked servers. So I think better removing the servername in all the stored procedures.

I know I can do a generate script, and replace the text and then use alter procedure to recreate all the stored procedures. But since hundreds of them, is there a programmatically way to replace them?

View 2 Replies View Related

SQL Server 2008 :: Query Execution Plan Of Stored Procedure

Jun 17, 2015

Is it possible to check query execution plan of a store procedure from create script (before creating it)?

Basically the developers want to know how a newly developed procedure will perform in production environment. Now, I don't want to create it in production for just checking the execution plan. However they've provided SQL script for the procedure. Now wondering is there any way to look at the execution plan for this procedure from the script provided?

View 8 Replies View Related

SQL Server 2008 :: Replication Of Stored Procedure Execution Fails

Jul 29, 2015

I am replicating a stored procedure execution, which builds and executes the following dynamic SQL command:

IF EXISTS (select * from MyDB..sysfiles sf (nolock) where name = 'MyDB_201201')
ALTER DATABASE [MyDB] REMOVE FILE [MyDB_201201]
IF EXISTS (select * from MyDB..sysfilegroups sfg (nolock)
where groupname = 'MyDB_201201' and sfg.groupname not in(
SELECT distinct fg.name file_group_name

[Code] ....

I can run this SP with no errors on both the publisher and the subscriber. However, when I try to replicate the execution of this SP, I get an error in replication monitor:

ALTER DATABASE statement not allowed within multi-statement transaction. (Source: MSSQLServer, Error number: 226)

How can I change my code to workaround this? Perhaps some explicit transactions?

View 6 Replies View Related

SQL Server 2008 :: Calling Stored Procedure Into Report Builder

Oct 23, 2015

I am trying to call a stored procedure into report builder. The stored procedure has 2 parameters. When I run the report with allowing nulls and blank value in the parameters it works fine, but when I enter a value in a parameter it ignores the where clause I had in the original query(stored procedure) and displays everything.

View 3 Replies View Related

SQL Server 2008 :: Find All Items That Call A Stored Procedure

Nov 2, 2015

We have a process that uses a stored procedure: sp_MysteryProcedure....

The problem I am having is: I am 95% certain the issue lies with this stored procedure, but I cannot find the process that calls this procedure. It could be a SQL Server Job that calls the procedure directly, it could be an SSIS (*.dtsx) process that calls this procedure, or some other random process.

One of the big issues is we have tons of *.dtsx packages that call a bunch of stored procedures, but it doesn't seem a normal Windows Search (Start --> Search) searches these files (or perhaps something else is going on with this search).

So my question is multi-part.....

1). How would one go about finding a rouge process that loads data via a stored procedure if we believe we know the stored procedures name?
2). How do you search *.dtsx files?

View 9 Replies View Related

Is There A Way To View Stored Procedure, Trigger AndFunction Usage In SQL Server?

Oct 11, 2006

I am maintaining an application where most of the business rules are inTriggers, Stored Procedures and User Defined Functions. When a bugarises, it can get very tedious to debug. Today for example, I wantedto modify a function that was being called by a trigger. The problemis that I don't want to change the function, for fear that it is beingcalled by one of the other SP's or triggers in the database (there arehundreds of them)Essentially, I need a tool that allows me to view where functions andsp's are being referenced from. At the very least, I'd like to performa "full text search" in the database objects, so that let's say I havea function named "fn_doSomething", I can search the schema for thisstring and get all the places where it appears.As you can see, I'm in the dark here. I've never worked on a systemwhere all business rules are at the database level. If you know of atool that does what I describe above, or anything else that wouldfacilitate my life, please let me know!Thanks for your help,Marc

View 5 Replies View Related

SQL Server 2008 :: Backup Of Distribution Database Hangs On Stored Procedure

Nov 29, 2014

Have a server running 14 production databases, 12 of which are replicated (transactional replication) to a second server for reporting. Distributor on same machine as publishers. All set to 'SyncWithBackup' = true, distribution set to Full recovery mode, log backups every 30 minutes, full backup nightly. This generally runs just fine.

Occasionally, the process 'hangs' indefinitely (has gone 12 hours or more before being caught) and I need to stop the backup job, stop one or more of the log reader agents, and restart everything, and it proceeds just fine. Annoying, but not fatal, and not very often.

This time, no matter what, the backup job hangs when it runs. This is true whether it is the FULL backup or just a Transaction Log backup. It hangs on the stored procedure sp_msrepl_backup_start, at the point where it is attempting to update the table 'MSrepl_backup_lsns'. When it is hung like this, all of the log reader agent jobs are also hung, blocked by this stored proc. I've tried stopping ALL of the log reader agents prior to starting the backup, but the backup process still hangs up at the same spot and never ends.

I can run the select statement in that SP that gathers the data for the databases 'manually' in a query window and it finishes in about 10 seconds. It actually seems to be hung up on the 'UPDATE' statement. When it is hung, I cannot SELECT from MSrepl_backup_lsns unless I append WITH (NOLOCK) to the statement. Nothing else I can find indicates that there is anything else locking that table. DBCC OPENTRAN shows that there is a lock on that table held by that stored proc -- but I can't see any reason why it won't update the table (17 total records) and move on.

As I said, normally this runs just fine. Totally baffled by what may be causing this at this time.

View 1 Replies View Related

SQL Server 2008 :: Copy Multiple CSV Files Into Database Using Stored Procedure?

Feb 9, 2015

I need to import multiple csv files and load into table and everytime new database has to be created .

I was able to create new databases using stored proc

How do i do a bulk insert for all the files at once to insert into tables .

i want to load all the files at once .

View 6 Replies View Related

SQL Server 2008 :: Running Batch File To Execute A Stored Procedure

May 11, 2015

I have create a batch file to execute a stored proc to import data.

When I run it from the server (Remote Desktop) it works fine, but if I share the folder and try to run it from my pc, it doesn't do anything. I don't get an error, it just doesn't do anything. My windows user has admin rights in SQL. Why is it not executing from my PC?

View 9 Replies View Related

SQL Server 2008 :: Find List Of Stored Procedure Used By Crystal Reports

Jul 1, 2015

We have more that 500 crystal reports and we would like to find out list of stored procedure used by crystal reports. Can we find out ?

View 4 Replies View Related

SQL Server 2008 :: Find Out What Data Was Changed By A Stored Procedure After It Was Executed?

Jul 22, 2015

isn't there an automatic log of some sort to check and see what exactly was changed by a given SQL command? A stored proc was ran and I need to figure out what exactly it changed in the underlying table.

View 3 Replies View Related

SQL Server 2008 :: Stored Procedure Script Output To Text File

Apr 29, 2009

I am having a Stored Procedure Or SQL Script to be attached to Job Scheduler. When this Stored procedure executes it generates some output text. I need to store this output to text file when ever this store Procedure (or) SQL Script executed by job Scheduler.

View 9 Replies View Related

SQL Server 2008 :: Granting Explicit View Definition Permissions On Stored Procedure To DBO?

Mar 6, 2013

The developers in our shop have a need to explicitly grant view definition permissions to themselves on stored procedures they create in their development databases. They have dbo level permissions in these databases and although they can explicitly grant view definition permissions to other developers in the same database, they are unable to do so for themselves. When they attempt this, it appears that they are successful but when they check the stored procedure afterwards the permission is not there for themselves.

While this does not cause an issue in development, the intention is for these view definition permissions to be carried forward to the test and production databases where they only have datareader permissions.

When these stored procedures are scripted out by the dba to move to Test and Production the view definition permissions are not scripted out for the developer in question.

Is there a way that a developer with dbo rights in a database can explicitly grant themselves view definition permissions on a stored procedure they create as dbo?

View 9 Replies View Related

SQL Server 2008 :: Stored Procedure Returning Different Datasets Based On Input Variable

Sep 15, 2015

I'm seeing where previous developers have used a single stored procedure for multiple reports, where each report required different columns to be returned. They are structured like this:

CREATE PROCEDURE dbo.GetSomeData (@rptType INT, @customerID INT)
AS
BEGIN
IF @rptType = 1
BEGIN
SELECT LastName, FirstName, MiddleInitial

[Code] ....

As you can see, the output depends on the given report type. I've personally never done this, but that's more because it's the way I learned as opposed to any hard facts to support it.

So what I'm looking for is basically 2-fold.

View 5 Replies View Related

How Do I Create A Stored Procedure In SQL Server 2005 ?

Feb 8, 2008

Yes it looks like a stupid question
but when i right click stored procedures
and click new stored procedure, it gives me a
QRY analyzer style window and all i can do is save
the qry as a regular .qry file ?

View 4 Replies View Related

SQL Server 2012 :: How To Create Password On Stored Procedure

Jun 27, 2014

is it possible to create PW on Stored Procedure? No one can execute or Alter any Store Procedure with Password?

View 1 Replies View Related

How Do I Create A Stored Procedure In SQL Server Management Studio Express?

Dec 5, 2006

I have wrriten many stored procedures in the past without issue, but this is my first time using SQL Server Management Studio Express.  I am having trouble creating a new stored procedure.  Here is what's happening:
I am opening my database, right clicking on "Stored Procedures" and selecting "New Stored Procedure".  A new window opens with a template for creating a stored procedure.  The window is called: "SQLEXPRESS.DBName - sqlquery1.sql".  I then type up my stored procedure without an issue.
However, when I go to save the stored procedure it wants to save it as a separate file in a projects folder.  It does not become part of the DB (as far as I can tell).
When I used to use Enterprise Manager (not an option anymore) this never happened.
I'm sure I'm doing something dumb.  Can someone enlighten me.
Thanks,Chris

View 4 Replies View Related

SQL Server 2012 :: Using CTAS Create Stored Procedure For Client

May 14, 2014

I have table named TEMPLATE_ACTIVITY. This is template table I have 27 this kind of tables.

I want to create stored procedure to change name MICHELIN_US_ instead of TEMPLATE_ all remaining name should be same. For that I am using 'Create Table As Select' to keep same structure as Template tables.

I want to create sp as like execute this way Exec @MICHELIN_US_

So that in future if Client change to MICHELIN_US_ to UNITED_ I can just change Exec @UNITED_

And it will change all table names to UNITED_ACTIVITY

I want to create this SP for different client.

View 3 Replies View Related

SQL Server 2012 :: Mirror Database - Create Stored Procedure

Nov 5, 2014

I have a database which uses "Database Mirroring", and I need to write stored procedure and pull data from "Principal Server".

My Current Logic:

CREATE PROCEDURE abc123
as
BEGIN
IF Server01 = 'ONLINE'
BEGIN

[Code] .....

The problem I am facing is: Stored procedure is not created because "One of the server is not Online"...

View 4 Replies View Related

Create XML In ASP.NET 2.0 Then Use For Joined Table In SQL Server 2005 Stored Procedure

Jun 11, 2006

Here's my problem:I'm developing an ASP.NET 2.0 application that has a user select one or moreauto manufacturers from a listbox ("lstMakes"). Once they do this, anotherlistbox ("lstModels") should be filled with all matching models made by theselected manufacturers. If lstMakes was not multi-select, I'd have noproblem. But in this case it has to be multi-select. The database is SQLServer 2005 which does not accept arrays as parameters. I've been told thatI have to create an XML document that will act as a filtered Manufacturerstable that I can join to my Models table in my stored procedure. Problem isI don't have the foggiest idea how to do this. I've seen some examples thatjust leave me scratching my head so I was hoping someone could look at whatI'm trying to do and show me how to do this. Thanks!

View 2 Replies View Related

SQL Server 2000 - Create An Excel File By Stored Procedure..

Mar 3, 2008

Can I create an excel file by stored procedure?

View 2 Replies View Related

Create Procedure Or Trigger To Auto Generate String ID

Feb 20, 2004

Dear everyone,

I would like to create auto-generated "string" ID for any new record inserted in SQL Server 2000.

I have found some SQL Server 2000 book. But it does not cover how to create procedure or trigger to generate auto ID in the string format.

Could anyone know how to do that?? Thanks!!

From,

Roy

View 7 Replies View Related

DDL Create User In T-SQL Stored Proc/trigger

Dec 7, 2004

Hello SQL Server programming gurus:

I am trying to create a trigger that fires after a user logon and logoff and does the following:

creates a new user
deletes data from old temp table

Then I need to create a stored procedure that executes dynamically to
drop old users
remove the old user account access

We are running SQL Server 2000.
Since I am new to T-SQL programming could anyone help point me in the right direction? Can I write dynamic SQL in a trigger to do these things?

View 1 Replies View Related







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