Database Trigger To Run Managed C# Code
Aug 24, 2007
Hi there,
Values in my database need to updated periodically. The code, upon starting the application, queries the database and stores the values in the Application collection. This is to avoid making a database call everytime the values are needed (increases performance). The drawback is that changes to the database values are not updated in the code.
How can I create a database trigger that will update the C# Application colllection whenever a table value is updated?
View 2 Replies
ADVERTISEMENT
Apr 11, 2007
Can anyone explain to me why I would choose one over the other? Please provide some simple examples of when I would choose each. Thanks!
View 4 Replies
View Related
Nov 20, 2006
I have a SQL Server project in Visual Studio 2005 which deploys an assembly to SQL Server 2005 containing various stored procedures user defined functions. Is there any way to tell Visual Studio to drop/create the stored procedures in a schema other than dbo?
ie: User.ChangePassword instead of dbo.ChangePassword.
View 2 Replies
View Related
Apr 19, 2007
There is an interesting article from MSDN Magazine titled "Identify and Prevent Memory Leaks in Managed Code"
http://msdn.microsoft.com/msdnmag/issues/07/01/ManagedLeaks/default.aspx
Are there any additional documents or utilities that people would suggest for monitoring and managing CLR impact on SQL server resources and performance?
View 1 Replies
View Related
Oct 3, 2006
I have been attempting to create a managed stored procedure which calls a web service using WSE 3.0 for security.
It appears that the WSE-generated config file (or possibly the app.config file) is not accessible to the .Net code.
Is there a method for using config files with CLR managed sprocs?
Thanks,
Max
View 1 Replies
View Related
Jan 10, 2007
Hi everyone,
Primary platform is Framework 2.0
Our current service throws on-demand .DTSX and now we'd like that will throw old ETL 2000 too.
I'd like to know if success or not success when calling dtspkg.dll from my managed code (vb).
Is it possible or not? I only capture errors for the sake of TRY..END TRY but I don't know how the hell to know
if the dts execution was successful.
Execute method not returns anything.
Currently my schema is the following:
paquete = New DTS.Package2
paquete.LoadFromSQLServer("SRVDESA1", , , DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_UseTrustedConnection, _
, , , "pruebaenric")
paquete.FailOnError = True
Try
paquete.Execute()
Catch ex As Exception
' stuff
End Try
paquete = Nothing
I've tried to use WITHEVENTS but it's useless at all from there.
Events are not fired. I though that RCW created for this could be able to call them...
Private Withevents paquete as dts.package2
Private paquete_OnError()
stuff
Private paquete_OnProgress()
stuff
Thanks for any input related with,
View 3 Replies
View Related
Mar 12, 2007
SQL Server 2005 gives you a possibility to create managed triggers. In a managed trigger I can create a new thread and process trigger event in various ways. My question is that are there any reasons why I should NOT start new threads in database triggers? The following code shows how I could create new threads. Do you see that this could cause any errors or problems in SQL Server functionality? My goal is to minimize the trigger effects in a database overall performance.
'This handles database updates of AdventureWorks Person.Contact table.
<Microsoft.SqlServer.Server.SqlTrigger(Name:="UpdateContact_Trigger", Target:="Person.Contact", Event:="FOR UPDATE")> _
Public Shared Sub UpdateContact_Trigger()
'Notify:
SqlContext.Pipe.Send("Trigger FIRED")
Dim triggContext As SqlTriggerContext = SqlContext.TriggerContext()
'Ensure that it was update:
If triggContext.TriggerAction = TriggerAction.Update Then
'Open a connection:
Using conn As New SqlConnection("context connection=true")
conn.Open()
'Fetch some information about the update:
Using sqlComm As New SqlCommand
sqlComm.Connection = conn
sqlComm.CommandText = "SELECT ContactID, FirstName, LastName from INSERTED"
Using rdr As SqlDataReader = sqlComm.ExecuteReader
If rdr.Read Then
'Process the data in a separate thread. The thread could send a message to MSMQ about the change
'or process if differently. The purpose of using separate thread is avoid performance hit
'in the application / database operation.
Dim oData As New TriggerData("update", rdr.GetInt32(0).ToString, "contact")
Dim trd As New Threading.Thread(New Threading.ThreadStart(AddressOf oData.ProcessEvent))
trd.IsBackground = True
trd.Start()
End If
End Using
End Using
End Using
End If
End Sub
View 2 Replies
View Related
Mar 6, 2008
Hi all( Create a VB 2005 SQL Server Project )
i want to Create a Trigger using Managed Code in VB.Net (.NET CLR integration with SQL Server.)Somebody help me.Thanks
View 2 Replies
View Related
Aug 22, 2004
I am trying to debug sql2000 sp from managed code app with VS.Net 2003 archetect Ed..
It did not stop at the break point within the sql sp.
I did granted execute permission for sp_sdidebug.
Do I need to attach any process?
Is there anything left off by the article?
I referenced msdn article option 2: http://support.microsoft.com/default.aspx?kbid=316549
Thanks.
View 3 Replies
View Related
Jan 23, 2008
Hi,
I have a requirement to create #Temp table in database and insert values to it.
I use following code:
DbCommand dbCreateTable;
dbCreateTable = provider.CreateCommand();
dbCreateTable.Connection = conn;
dbCreateTable.CommandText ="Create table #MyTemp (Id varchar(10))";
dbCreateTable.ExecuteNonQuery();
string[] insertValues = {"Insert into #Mytemp values ('TestString1')",
"Insert into #Mytemp values ('TestString2')"};
DbCommand dbInsertData = provider.CreateCommand();
dbInsertData.Connection = conn;
foreach (String insertStr in insertValues)
{
dbInsertData.CommandText = insertStr;
dbInsertData.ExecuteNonQuery();
}
Code creates the Temp table but when it comes to insert statement, it throws error saying "Temp table not found".
Reason can be Create and Insert statement gets executed as 2 different sessions.
How to get the above requirement work fine?
Thank you.
HV
View 4 Replies
View Related
Apr 18, 2006
hi
I am trying to store the output of sp-executesql into a variable to implement it as a user defined function later
The function is
ALTER function [dbo].[UnitsAvailable] (@id int)
returns int
as
begin
declare @sql nvarchar(100)
declare @params nvarchar(500)
declare @count nvarchar(10)
set @sql = N'Select count(*) from units where projectid=' + convert(varchar,@id) + 'and sold=0 and displayunit=1'
set @params = N'@countOUT nvarchar(10) OUTPUT';
exec sp_executesql @sql, @params, @countOUT=@count OUTPUT;
return @count
end
The result is that I am able to parameterize the sql end execute with the right result. The only problem is that the value is not stored in the variable @count. I could get to the same result using managed code in sql 2005 but still I am curious to find out where the problem is ....
Can you please help?
Thanks Alex
View 6 Replies
View Related
Apr 13, 2006
Hi All,
I am trying to open multiple connections in a Managed Trigger but encoutering an error as :
System.Data.SqlClient.SqlException: Transaction context in use by another session.
Below is the sample code:
public partial class Triggers
{
[Microsoft.SqlServer.Server.SqlTrigger (Name="TrgInsertContract", Target="Contracts", Event="FOR INSERT")]
public static void TrgInsertContract()
{
SqlTriggerContext triggContext = SqlContext.TriggerContext;
SqlConnection connection = new SqlConnection("context connection = true");
connection.Open();
SqlCommand command = connection.CreateCommand();
command.CommandText = "SELECT * from " + "inserted WHERE Active=1";
SqlDataReader reader;
reader = command.ExecuteReader(CommandBehavior.CloseConnection);
connection.Close();
SqlConnection connection1 = new SqlConnection("Initial Catalog=TestDB;Data Source=SHAIKDEV;User ID=sa;password=****");
connection1.Open();
}
Any help please ?????
Thanks...
View 5 Replies
View Related
Mar 11, 2008
All --
Please help.
I am using managed code in SQL Server 2005 and have a question about stored procedure name qualification.
With a VS.NET 2005 Pro Database project, when I use >Build, >DeploySolution, it works great but my stored procedures are named with qualification using my domainusername.ProcName convention, something like this...
XYZ_TECHKamoskiM.GetData
...when I want them to be named with the dbo.ProcName convention, something like this...
dbo.GetData
...and I cannot see where this can be changed.
Do you happen to know?
Please advise.
(BTW, I have tried going to this setting >Project, >Properties, >Database, >AssemblyOwner, and putting in the value "dbo" but that does not do what I want.)
(BTW, as a workaround, I have simply scripted the procedures, so that I can drop them and then add them back with the right names-- but, that is a bit tedious and wasted effort, IMHO.)
Thank you.
-- Mark Kamoski
View 1 Replies
View Related
Aug 4, 2006
Dear friends,
please help,
let me tell you what happens,
1) first i installed Sql Server 2005 Enterprise Edition, everything was working fine except i was not able to create managed objects from BI, like stored procedures, triggers, UDT, etc etc.
2) so i installed VS.net 2005 complete + Sql Server 2005 Express Edition
now i was able to create stored procedures, triggers, etc etc, i'm talking about the CLR objects, ok, i.e. the managed code, but........ i was only able to do this from Sql Server 2005 Express Edition.
whenever i try to create managed objects CLR like stored procedures and stuff from the Sql Server 2005 Enterprise edition it gives me the following error :
"The Sql Server supplied by these connection properties, does not support managed code, please choose a different server"
please help meeeee.. please please, tell me what's the problem ?
below i'm attaching a screenshot of the error....
View 3 Replies
View Related
Aug 17, 2006
Dear all,
I am very new to the subject of writing CLR code inside SQL Server, so I apologise if my questions seem naive.
I have a requirement to populate an asp.net 2.0 GridView control with data columns, some of which are directly from a SQL Server 2005 database, but some of which are calculated by calling CLR methods passing the values from the database columns to those methods.
However, the methods I need to call only make sense in the process context of the client web site which is calling the stored procedure which I want to write to return the data columns.
In effect, I want to be able to make a remote procedure call from within the SQL Server CLR code to the methods available in the client process.
Is this possible? If so, could someone please refer me to an example of how to do it.
If it can be done, it opens up lots of very cool possibilities!
Thanks.
View 3 Replies
View Related
May 12, 2006
I have inherited a database and with it a set of triggers that are 'apparently' installed. However I have a number of different 'versions' for a specific update trigger and it is not clear from the files which is the latest, hence I am unsure of what 'version' of the trigger is installed in the database itself.
I am trying to retrieve the trigger code from the databse so that i can correctly determine which one of my trigger files is the latest version.
I don't want to end up in a situation where an earlier version of the update trigger is installed as this could wreck the underlying data.
I have tried using the syscomments.text column that relates to the trigger but it only gives me a concatenated version of the sql code and not enough to determine which file version was used for the trigger.
Is there a way of retreiving the entire SQL code that was entered rather than just the first few lines?
View 5 Replies
View Related
Feb 10, 2006
I am trying to understand creating SQL Server projects and managed code. So I created a C# SQL Server Database project and named it "CSharpSqlServerProject1" and followed the steps in the following "How to: " from the Help files:
"How to: Create and Run a CLR SQL Server Stored Procedure "
I used the exact code in this "How to: " for creating a SQL Server managed code stored procedure (see below) in C#. However it didn't even compile! When I went to build the code I got the following error message:
"Error 1 Target string size is too small to represent the XML instance CSharpSqlServerProject1"
It does not give a line number or any further information! Since this is a Microsoft example I'm following I figure others must have run into this too. I can't figure out how to fix it!
Here's the code as copied directly from the howto:
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class StoredProcedures
{
[SqlProcedure()]
public static void InsertCurrency_CS(
SqlString currencyCode, SqlString name)
{
using (SqlConnection conn = new SqlConnection("context connection=true"))
{
SqlCommand InsertCurrencyCommand = new SqlCommand();
InsertCurrencyCommand.CommandText =
"insert Sales.Currency (CurrencyCode, Name, ModifiedDate)" +
" values('" + currencyCode.ToString() +
"', '" + name.ToString() +
"', '" + System.DateTime.Now.ToString() + "')";
InsertCurrencyCommand.Connection = conn;
conn.Open();
InsertCurrencyCommand.ExecuteNonQuery();
conn.Close();
}
}
}
Thanks for any help you can give!
View 7 Replies
View Related
Sep 3, 2007
I need to archive files in a database by checking an archive date for the file contained in a field in a table of a database, if the archive date is greater than todays date then archive the file by moving it to an archive folder. I am thinking the best way might be to use a manged stored procedure, but I also need to run this procedure once every 24 hours at about midnight so how would I do thi? Another way might be by using DTS or something. Has someone else done this and how did they go about it?
View 1 Replies
View Related
Jan 31, 2008
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports System.Data.OleDb
Imports System.Configuration
Imports System.Text
Imports System.Collections
Partial Public Class StoredProcedures
<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Sub StoredProcedureTest(ByVal strAS400ServerName As String, _
ByVal strCompany As String, _
ByVal decSerial As Decimal, _
ByVal strSerialCode As String, _
ByVal strSerialScan As String, _
ByVal decMasterSerialNumber As Decimal, _
ByVal strCustomerPart As String, _
ByVal strTakataPart As String, _
ByVal strCustomerRanNo As String, _
ByVal strCustomerAbv As String, _
ByVal strDestinationAbv As String, _
ByVal decQty As Decimal, _
ByVal strCreatDate As String, _
ByVal decVoidSerialNo As Decimal, _
ByVal strProductionLineNo As String, _
ByVal strProcType As String)
Dim sp As SqlPipe = SqlContext.Pipe
Dim strResult As Integer = 0
Dim strErrorText As String = String.Empty
Dim dsData As New DataSet
Dim parameter(15) As OleDbParameter
If Not strAS400ServerName Is Nothing And strAS400ServerName <> String.Empty Then
' Populate parameter collection
parameter(0) = (CreateParameter("PARM1", OleDbType.Char, 20, ParameterDirection.InputOutput, strAS400ServerName))
parameter(1) = (CreateParameter("PARM2", OleDbType.Char, 2, ParameterDirection.InputOutput, strCompany))
parameter(2) = (CreateParameter("PARM3", OleDbType.Decimal, 10, ParameterDirection.InputOutput, decSerial))
parameter(3) = (CreateParameter("PARM4", OleDbType.Char, 2, ParameterDirection.InputOutput, strSerialCode))
parameter(4) = (CreateParameter("PARM5", OleDbType.Char, 25, ParameterDirection.InputOutput, strSerialScan))
parameter(5) = (CreateParameter("PARM6", OleDbType.Decimal, 10, ParameterDirection.InputOutput, decMasterSerialNumber))
parameter(6) = (CreateParameter("PARM7", OleDbType.Char, 30, ParameterDirection.InputOutput, strCustomerPart))
parameter(7) = (CreateParameter("PARM8", OleDbType.Char, 15, ParameterDirection.InputOutput, strTakataPart))
parameter(8) = (CreateParameter("PARM9", OleDbType.Char, 15, ParameterDirection.InputOutput, strCustomerRanNo))
parameter(9) = (CreateParameter("PARM10", OleDbType.Char, 6, ParameterDirection.InputOutput, strCustomerAbv))
parameter(10) = (CreateParameter("PARM11", OleDbType.Char, 6, ParameterDirection.InputOutput, strDestinationAbv))
parameter(11) = (CreateParameter("PARM12", OleDbType.Decimal, 9, ParameterDirection.InputOutput, decQty))
parameter(12) = (CreateParameter("PARM13", OleDbType.Char, 10, ParameterDirection.InputOutput, strCreatDate))
parameter(13) = (CreateParameter("PARM14", OleDbType.Decimal, 10, ParameterDirection.InputOutput, decVoidSerialNo))
parameter(14) = (CreateParameter("PARM15", OleDbType.Char, 3, ParameterDirection.InputOutput, strProductionLineNo))
parameter(15) = (CreateParameter("PARM16", OleDbType.Char, 2, ParameterDirection.InputOutput, strProcType))
RunDB2Sp("FABLE.MAP", parameter, dsData)
If dsData.Tables.Count > 0 Then
dsData.Tables(0).TableName = "Supreeth"
Dim bitresult As String = dsData.Tables(0).Rows(0)(0).ToString()
Dim errorstring As String = dsData.Tables(0).Rows(0)(1).ToString()
' I am not sure here
SqlContext.Pipe.Send(bitresult)
SqlContext.Pipe.Send("No errors")
End If
Else
Throw New ArgumentException("AS400Db.GetAS400TraceabilityResult: AS400 server name is empty or invalid")
End If
End Sub
Public Shared Sub RunDB2Sp(ByVal strProcedure As String, ByRef parms As OleDbParameter(), ByRef dsData As DataSet)
'*********************************************
' Declare Variables
'*********************************************
Dim daAdaptor As OleDbDataAdapter
Dim cmdAS400 As OleDbCommand
'Dim dstestMe As New DataSet
Try
cmdAS400 = CreateCommand(strProcedure, parms)
daAdaptor = New OleDbDataAdapter(cmdAS400)
' Fill the Data Set
daAdaptor.Fill(dsData)
Catch expError As OleDbException
daAdaptor = Nothing
Finally
daAdaptor = Nothing
cmdAS400.Dispose()
'Me.Close()
End Try
End Sub
Public Shared Function CreateParameter(ByVal name As String, _
ByVal type As OleDbType, _
ByVal size As Integer, _
ByVal direction As ParameterDirection, _
ByVal paramValue As Object) As OleDbParameter
Dim param As OleDbParameter = New OleDbParameter
param.ParameterName = name
param.OleDbType = type
param.Size = size
param.Direction = direction
param.Value = paramValue
Return param
End Function
Private Shared Function CreateCommand(ByVal strProcedure As String, ByVal prams As OleDbParameter()) As OleDbCommand
Dim CmdSAS400 As OleDbCommand
Dim parameter As OleDbParameter
Dim connAS400 As OleDbConnection
connAS400 = New OleDbConnection("Provider=IBMDA400;Data Source=AHISERIESDEV1;User Id=****;Password=****;")
connAS400.Open()
CmdSAS400 = connAS400.CreateCommand()
CmdSAS400.CommandText = strProcedure
CmdSAS400.CommandType = CommandType.StoredProcedure
CmdSAS400.Parameters.Clear()
'CmdAS400.CommandTimeout = intTimeOut
If (prams Is Nothing) Then
Else
For Each parameter In prams
CmdSAS400.Parameters.Add(parameter)
Next
End If
Return CmdSAS400
End Function
I have a UI which supplies 16 parameters to my stored procedure , which in turn call another sored procedure on as400 which returns result set. So far i am able to send 16 parms and get the values in dataset.
My question here how would i send the result set to UI for display, please feel free to comment on any changes need to be made on code . I badly need to find a solution for this and i appreciate any feed backs
Thanks
View 3 Replies
View Related
Jul 14, 2006
I have table T1 and I am trying to extract some date from T1 table based on inserts, updates and deletes. The destination table T2 has three fields F1, F2, F3, so I need something like
Insert into T2 (F1, F2, F3)
Select (F1, F2,Type)
From T1
Type should be defined based on Insert, Update, or Delete. This will be my first trigger, Can anyone write this trigger for me?
View 1 Replies
View Related
Jun 14, 2008
How can I write a trigger for the below issue,
I have table named Subscribers with fields Name (varchar), MobileNumber (varchar), status (char). Here if I want to insert a record into this table then first I need to check whether this mobile number exist or not in this table. If it is not exist then this record should be insert and if it is exist then this record should not be insert in to this table.
How can I do a trigger for this one?
with regards
Shaji
View 5 Replies
View Related
Aug 26, 2007
CREATE TRIGGER tr_1
ON Users
FOR UPDATE OF u_paid
AS
BEGIN
..........
END
any idea what is wrong with the above code?
The users table exist and u_paid is a column in that table.
Im getting the following error:
Incorrect syntax near the keyword 'OF'.
View 18 Replies
View Related
Jul 20, 2005
Am using SQL Server 2000 on WINXP Pro. Have a requirement to change someOracle triggers to SQL 2000. I have modied this one Insert Trigger, but getan error when I attempt to compile:CREATE TRIGGER trg_ins_attend_audit_logON ATTENDAFTER INSERT-- Insert Trigger for SQL ServerASDECLAREBEGINInsert ATTEND_AUDIT_LOGSelect licnum,lic_suffix,als_hsp_id,last_name,first_name,mid_name,birth_date,sex,serv1,serv2,serv3,paid_vol,amb_res,street1,street2,town,state,zipcode,phone,lic_year,print_lic,lic_exp,send_hx,user_id,getdate(),"I",modifiedfrom insertedENDERROR:Incorrect syntax near "AS."Can anyone point out the error of my ways here??
View 2 Replies
View Related
Jul 14, 2006
I have table T1 and I am trying to extract some date from T1 table based on inserts, updates and deletes. The destination table T2 has three fields F1, F2, F3, so I need something like
Insert into T2 (F1, F2, F3)
Select (F1, F2,Type)
From T1
Type should be defined based on Insert, Update, or Delete. This will be my first trigger, Can anyone write this trigger for me?
View 5 Replies
View Related
Jun 4, 2007
Hello all
I've been thrown in at the deep end by the manager and asked to use a SQL database for a new site. I've managed to get most of the code working but are having trouble with this particular one.
The idea is that a user can log a fault (online) and that this gets added to a table. Once the data is added a trigger fires that takes this new data and loops through an "e-mail alert" table containing settings for different department managers and then stores the relevant manager name in a separate table (e-mail sent fromt this table to the manager) once the department and fault id's match.
I have the following code (which is by no means good) in my trigger - did I says this is my first outing into SQL land!!ALTER TRIGGER [dbo].[usd_emailalertqueue]
ON [dbo].[FaultPostings] AFTER INSERT
AS
-- declare variables from new fault table
DECLARE @iDeptID intDECLARE @iFaultID intDECLARE @iReferenceID int
-- declare variables from email alerts tableDECLARE @Managername nvarchar(255)DECLARE @DeptID intDECLARE @FaultID intDECLARE @ReferenceID int
-- Read in values new fault tableSELECT @sParam = FaultDescription FROM INSERTEDSELECT @iFaultID = FaultID FROM INSERTEDSELECT @iDeptID = DeptID FROM INSERTEDSELECT @iReferenceID = ReferenceID FROM INSERTED
BEGIN
-- Read values from saved e-alertsSELECT @Managername = Managername FROM [dbo].[emailAlerts]SELECT @DeptID = DeptID FROM [dbo].[emailAlerts]SELECT @FaultID = FaultID FROM [dbo].[emailAlerts]
INSERT INTO EmailAlertQueue(ReferenceID,Managername)SELECT @iReferenceID, @Managername
FROM [dbo].[emailAlerts] WHERE (@iDeptID = @Deptid) AND (@iFaultID = @Faultid) then END
At the moment, this is what happens when a fault is added: the EmailAlertQueue table gets populated with the name of the last managername from the emailAlerts table several times (only if the Dept and Fault id's macth otherwise nothing gets populated.. e.g. if there at 4 alerts setup in the EmailAlerts table with different dept and fault ID's but the last one matches the criteria the emailAlerts table is uotaed with 4 rows containing the managername. (Hope this makes sense).
I think I need to have some sort of loop but so far as I can tell a "For Each" loop doesn't work in triggers (i could be wrong though).
(I'm not even sure if this is the correct approach to take)
As usual, any help is appreciated.
Thanks
Chooch
View 3 Replies
View Related
Mar 11, 2005
Does anyone have code for a trigger that when the user deletes the record trigger it to insert into another table? Thanks in advance and appreciate your help!
View 4 Replies
View Related
Apr 21, 2006
Hi.
I am trying to get the schema in which the trigger is created within the CLR code.
1)create a new schema MySchema.
2) created a table MySchema.MyTable
3) created the assembly and trigger ( create trigger MySchema.MyTrigger on MySchema.MyTable..... ) Trigger writes to another table MySchema.MyLog.
Code works fine if I hardcode Myschema.MyLog in the CLR but fails when I say just MyLog.
So how do dynamically get the trigger's schema name ?
Thanks for your help.
Nach
View 1 Replies
View Related
Apr 3, 2008
Hi,
Please can anyone help me code the trigger below. I have had errors pointing to "sp_configure 'SQL Mail XPs', 1;" and "RECONFIGURE", that this lines should be not where I have placed them. I have tried other combinations and have not worked at all! Is any other ways I can achieve this all?
Code Snippet
CREATE TRIGGER reminder
ON Bug_Report
FOR INSERT, UPDATE, DELETE
AS
sp_configure 'SQL Mail XPs', 1;
EXEC master..xp_sendmail 'MaryM',
'Don't forget to print a bug report.'
RECONFIGURE
View 3 Replies
View Related
Aug 2, 2006
I would like to create a script that lists out all of the SQL code for the procedures and triggers I have created in a database.
What I want to do is save the code for each procedure/trigger as a *.SQL file without having to open up each one in Enterprise Manager and save them individually.
Listing the names is easy by looking in sysobjects, but is the SQL code stored in a system table anywhere?
Does anybody have any alternative approach to this problem?
Thanks in advance,
Mark
View 1 Replies
View Related
Jun 26, 2007
I've been searching for a bit but I can't seem to find a difinitive answer.
Can an SQL Server trigger call native C/C++ functions? If so, what is the mechanism?
Thanks in advance.
View 7 Replies
View Related
Apr 22, 2007
Hi Everyone, I m using ASP.NET 2005 with C# and SQL SERVER 2005. I m using stored procedure and sql datasource control to retrieve the data. I want to use the trigger alongwith storedprocedure. I have created the following trigger on emp_table. CREATE TRIGGER Employeee on emp_tableAFTER DELETE ASDECLARE @empid int SELECT @empid=empid from emp_table where locationid=(SELECT locationid from deleted)IF @@ROWCOUNT=0 DELETE FROM location_table where locationid=(SELECT locationid from deleted)What i wanted to know is how can i use it with asp.net and sql server 2005 when any update,delete or insert query is executed thru the stoed procedure...What code is required to use the trigger with the application of .NET thanxs.....
View 2 Replies
View Related
May 7, 2008
I have a database trigger that is set to call a CLR trigger/stored proc when a certain field in a table is updated. The issue is that if i execute the stored proc manually in enterprise studio, it works perfectly but the same call made through the trigger does not go through. A few more details -
I have CLR integration enabled on the sql server.
The dbo has UNSAFE ASSEMBLY rights
I have the both the assembly and the serialized dll imported in the database.
Here's the definition of the stored proc -
CREATE PROCEDURE [dbo].[WriteXMLNotification]
@TaskID [nvarchar](20)
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [DataInterfaceWebServices].[TaskUpdateXMLWriter.WriteXMLNotification].[run]
and the trigger -
CREATE TRIGGER [dbo].[tr_Task_U]
ON [dbo].[_Task]
FOR UPDATE, INSERT AS
IF UPDATE (TaskType_Status) OR UPDATE (TaskType) OR UPDATE (TaskType_SubType1)
BEGIN
SET NOCOUNT ON;
DECLARE @status AS INT
DECLARE @taskType AS INT
DECLARE @taskSubType AS INT
DECLARE @taskID as sysname
DECLARE @cmd as sysname
DECLARE @parentTask as sysname
DECLARE @NotificationXMLTaskID as sysname
SELECT @status = [TaskType_Status] FROM inserted
SELECT @taskType = [TaskType] FROM inserted
SELECT @taskSubType = [TaskType_SubType1] FROM inserted
SELECT @taskID = [TaskID] FROM inserted
SELECT @parentTask = [Parent_TaskID] FROM inserted
SELECT @NotificationXMLTaskID = [MCCTaskID] FROM _TaskNotificationXML WHERE [MCCTaskID] = @parentTask
IF (@status = 2602) AND (@taskType = 2282) AND (@taskSubType = 19500)
BEGIN
exec WriteXMLNotification @taskID;
END
ELSE IF (@taskType = 2285) AND (@parentTask IS NOT NULL) AND (@NotificationXMLTaskID IS NOT NULL)
BEGIN
exec WriteXMLNotification @parentTask;
END
END
I stepped into the trigger and it seems to execute the line " exec WriteXMLNotification @taskID;" but nothing happens, but if I run that same line manually, it works. Could it be that the impersonation by the EXECUTE AS clause is causing it to fail?
Please advise!
Thanks in Advance,
-Mihir Sonalkar.
View 1 Replies
View Related
Sep 6, 2007
My goal is to create a trigger to automatically set the value for a status id on a table based on set criteria. Depending on the values of other fields will determine the value that the status id field is assigned. So far, I have written the trigger to update the status id field and I go through a seperate Update statement for each status id value. The problem is that I can't get this to work at the record level. The problem that I am getting is that if I have 50 records in TABLE1 and at least one of them satisfies the where clause of the update statement, all of the records get updated. So, using these two update statements, all of my records end up with a status value of '24' because that was the last update statement run in the trigger. Here is the code I have so far:
CREATE TRIGGER dbo.JulieTrigger1
ON dbo.Table1
AFTER INSERT,UPDATE
AS
BEGIN
BEGIN TRY
/*Update Table1.Status to POTENTIAL (id 23) status */
UPDATE TABLE1
SET status_id = 23
WHERE EXISTS (SELECT *
FROM TABLE1 a INNER JOIN TABLE2 b
ON b.order_id = a.order_id
WHERE a.start_dt IS NULL
AND b.current_status_ind = 1
AND b.lead_status_id NOT IN (15,16)
AND a.order_id = TABLE1.order_id)
/*Update Table1.Status to ACTIVE (id 24) status */
UPDATE TABLE1
SET status_id = 24
WHERE EXISTS (SELECT *
FROM TABLE1 a
WHERE fill_ind = 1
AND (end_dt IS NULL OR end_dt > getdate() )
AND a.job_order_id = TABLE1.job_order_id)
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE()
IF @@TRANCOUNT > 0
ROLLBACK TRAN
-- Return the error to the calling object
RAISERROR (@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
)
END CATCH
SET NOCOUNT ON;
END
GO
Thanks in advance for any help!
-Julie
View 1 Replies
View Related
