Xp_cmdshell From Within A Trigger On SQL 2000 SP4
Jan 19, 2007
I have a trigger that should be execute on each row insert and only if
appcode = 'I' and datasent = 0. It should execute a DTS package. The
DTS package by itself runs about 6 seconds. Trigger was created
successfuly. When I try to insert a row my db hangs. I can see that
SPID on my db is hang by SPID from master db. It doesn't complete
until I kill that SPID. Why is that?
This is the trigger that I am trying to execute:
CREATE TRIGGER myExport
ON ruExport
FOR INSERT
AS
declare @appcode varchar (10)
select @appcode = appcode from ruexport where appcode = 'I' and
datesent = 0
if @appcode = 'I'
exec master..xp_cmdshell 'DTSRun /S "KOCL384017SQL" /N "Sys_Export" /G
"{17D112A8-413E-420F-A624-3790BDFBED9F}" /W "0" /E'
go
Any suggestions?
View 2 Replies
ADVERTISEMENT
Jul 20, 2005
I'm using xp_cmdshell to output a text file from a trigger like thisCREATE TRIGGER [CreateFile] ON tblApplicationsFOR INSERTASDECLARE @FirstName varchar(75)DECLARE @LastName varchar(75)Declare @strcmdshell varchar(150)Declare @filename varchar(100)Declare @filecontents varchar(150)SELECT @FirstName = FirstName FROM INSERTEDSELECT @LastName = LastName FROM INSERTEDSELECT @filecontents = 'FirstName: ' + @FirstName+char(13)+'LastName: ' +@LastName+char(13)+SELECT @filename = 'c: est.txt'SELECT @strcmdshell = 'echo '+ @filecontents+ ' >'+ @filenameexec master..xp_cmdshell @strcmdshellWhen I concatenate @FirstName and @LastName(neither is NULL) and try toinsert a blank line with char(13) the command doesn't run. If I don't usechar(13) it works fine. Does this make sense? IS there any other way I canformat the output file.Thanks
View 3 Replies
View Related
Apr 4, 2003
I can run the xp_cmdshell command with any other command until I put something like this is.
CREATE TRIGGER RunUpdateProgram ON [dbo].[UpdateQue]
FOR INSERT
AS
declare @MybatchID varchar(32),
@sendcommand varchar(32)
select @MybatchID = batchid from updateque
select @sendcommand = '"d:xanaduostarterprog.exe ' + @MybatchID + '"'
exec master..xp_cmdshell @sendcommand, no_output
The starterprog is setup to receive commandline input and then do its thing but everytime I try to add in a commandline to the xp_cmdshell command i get nothing.
The security is right and the program is right because i can run it until i try to add that last part "@MybatchID". If I take out the quotes then it runs the program but without the commandline input. I've tested the program manually and it correctly accepts commandlines that way.
Anyone got any ideas on what I've done wrong here.
View 1 Replies
View Related
Jul 20, 2005
Whenever something is inserted to a given table, I want to run someshell commands using xp_cmdshell. Would it be a bad idea to put thisxp_cmdshell in the INSERT trigger of this table?I understand that when using xp_cmdshell, the sql thread in questionwaits until xp_cmdshell finishes what it's doing. Does this mean ifmy xp_cmdshell call takes 30 seconds, that nobody else can insert tothis table until my xp_cmdshell and rest of the insert trigger finishup?The alternative solution seems to be frequent polling of the table inquestion; while this isn't really a great solution it would seem tocircumvent a table lock brought about by the INSERT trigger.Thoughts?Joel
View 4 Replies
View Related
Jul 20, 2005
I have created the following trigger:CREATE TRIGGER [CreateFile] ON OutputTableFOR INSERTASDeclare @filename nvarchar(35)Declare @filecontents nvarchar(2000)Declare @strcmdshell varchar(150)SELECT @filecontents = OutputText FROM INSERTEDSELECT @filename = 'c:' + OutputFileName FROM INSERTEDSELECT @strcmdshell = 'echo '+ @filecontents+ ' >'+ @filenameexec master..xp_cmdshell @strcmdshellIt works fine as long as the column OutputText has no carriagereturns.I have used my Access2K front end to dump about ten lines of text intoOutputText, each line broken by vbcrlf so that OutputText looks like:line1testline2textline3textetc.The trigger won't fire with this text.Is there something I can do to remedy this?lq
View 4 Replies
View Related
Feb 8, 2007
hi,does anybody know how to enable/disable xp_cmdshell in sql server2000.i our server this sp has been disbaled for security puropse.but i needto use it in a job.so please can any body guide me?thanks for the help.-Shark.
View 1 Replies
View Related
Jun 18, 2008
Hi,
I'm just moving over one of our databases from SQL2000 to 2005. Everything is working fine, and all web sites are working, however I just can't seem to get xp_cmdshell to function on the 2005 server.
I have enabled xp_cmdshell, and when I run a stored procedure, which writes the contents of one column to a text file, it says "invalid object name". This table is definitely there, and is actually updated earlier on in the same SP, so permissions for that are fine.
This is the line causing the problem (if I take it out, the rest of the SP works no problems):
--write the text file
EXEC master.sys.xp_cmdshell 'bcp "SELECT newslettertext FROM mydatabase.dbo.newsletters" queryout D:ewslettertext extbody.txt -U -P -c'
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'mydatabase.dbo.newsletters'.
SQLState = 37000, NativeError = 8180
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.
NULL
Could it be to do with permissions on the master.sys.xp_cmdshell? If so, wouldn't it give me a "permission denied" error?
Any help would be greatly appreciated!
Thanks
View 6 Replies
View Related
Jul 20, 2005
Hi allI have a stored procedure that has the lineEXEC master..xp_cmdshell 'dtsrun /Stestjob1 /N testdts /E'If I run the SP from an access front end as a trusted user or from ascheduled job it runs fine and exectues the dts.If I run the stored procedure using VB6 as a standard connection the dtsjobwont run. I get back Execute permissions denied on xp_cmd.. on databasemasterdb_connect_string = "Provider=SQLOLEDB.1;Persist Security Info=False;UserID=test_connect;PWD=pw1test;Initial Catalog=testdb;Data Source=" &database_name....Set cmd = New ADODB.Commandcmd.ActiveConnection = db_connect_stringcmd.CommandType = adCmdStoredProccmd.CommandText = "testStoredProcedure"cmd.ExecuteDo I need to give test_connect permisions to run the test stored procedure.I hoped that because the VB called a stored procedure and the connection hadpermissions to execute the SP then it would be the SP that called thexp_command....can anyone tell me the accepted way to do thismany thanksAndy
View 2 Replies
View Related
Jun 18, 2007
Hello All!
I am trying to create a trigger that upon insertion into my table an email will be sent to that that recipeinent with a image attached ( like a coupon)That comes from a different table, problem is, It will not allow me to send the email ( using xp_sendmail) with the coupon attached. I am using varbinary for the coupon and nvarchar for the rest to be sent, I get an error that Invaild operator for data type. operator equals add, type equals varchar.
Looks basically like this(This is my test tables):
CREATE TRIGGER EileenTest ON OrgCouponTestMainFOR InsertAS declare @emailaddress varchar(50)declare @body varchar(300)declare @fname varchar(50)declare @coupon varbinary(4000)
if update(emailaddress)begin
Select @emailaddress=(select EmailAddress from OrgCouponTestMain as str), @fname=(select EmailAddress from OrgCouponTestMain as str) @Coupon=(select OrgCoupon1 from OrgCouponTest2 as image)
SET @body= 'Thank you' +' '+ @fname +' '+ ',Here is the coupon you requested' +' ' + @couponexec master.dbo.xp_sendmail @recipients = @emailaddress, @subject = 'Coupon', @message = @bodyEND
View 6 Replies
View Related
Apr 3, 2006
Database Layout: Database 1 Contains table called “dbo.Users� Users table contains field/column “Username�. Database 2 Contains table called “dbo.aspnet_Users� Aspnet_Users contains field/column “UserName� Problem: Whenever a record’s “Username� field is updated in dbo.Users (Database 1), I need to update the “UserName� field in aspnet_Users (Database 2). How should I write the trigger to accomplish this task? The following trigger is currently_not_ working. :(ALTER TRIGGER [trig_updateUserNameForForum] ON [dbo].[Users] FOR UPDATE AS DECLARE @oldUserName NVARCHAR(256) DECLARE @newUserName NVARCHAR(256) IF UPDATE(Username) BEGIN SELECT @oldUserName = (SELECT Username FROM Deleted) SELECT @newUserName = (SELECT Username FROM Inserted) UPDATE Database2.dbo.aspnet_Users SET Username = @newUsername WHERE UserName = @oldUserName RETURN END Thanks!!! -Cody
View 22 Replies
View Related
Jan 30, 2008
How can I fire a procedure when a field is less than a threshold in sql server 2000?
Have you got any idea or suggestion?
Thanks in advance,
Andrea
View 1 Replies
View Related
Jul 20, 2005
Hi,can someone tell how to write a Trigger; I am familiar with Sybase SqlAnywhere trigger syntax.Actually I have three tables MEMBER, CONTRACT and PAYMENTI need to update the MEMBER.BALANCE once the PAYMENT.AMOUNT is INSERTEDwhere PAYEMENT.CONTRAC_ID = CONTRACT.CONTRAC_IDand CONTRAT.MEMBER_ID = MEMBRE.MEMBER_IDI have more TRIGGERS to write, but with a good example it would be greatIn fact, send me as many examples as you canThanksFernandJoin Bytes!
View 9 Replies
View Related
Sep 18, 2001
Hi Iam trying to do a trigger that everytime I Update a record de date get update too I finally find a trigger close to that, but this trigger update all the dates from all the record of the same table I wonder is there are a way that I can do it by the date of the record, if somebody could help I will really appreciate.
Thi is the trigger that I have so far
Create Trigger Update_Date
on DBO.Company After Update as
Update dbo.Company
Set ActualiizationDate=Getdate()
go
View 1 Replies
View Related
Aug 3, 2006
Dear Freinds,
we are having an requirement to log the unauthorized backend update on database.Is there any other way ,apart from going and creating the triggers at each table to log the backend update.
Note :Is Database base level trigger is possible on SQL Server 2000?
Looking for your reply.
With Cheers :beer: ,
Sathesh.M
View 3 Replies
View Related
Jul 20, 2005
During testing of an application, i noticed a difference betweenSQL 2000 and SQL 7, both with identical config.In a nutshell:A table has a trigger for UPDATE and DELETE.When a column in the table is UPDATED the following happens:In autocommit mode, when entering a trigger the trancount equals1 for both SQL 7 and 2000.When the same update is performed in an explicit transactionin SQL 7 @@TRANCOUNT equal 2, and in SQL 2000 @@TRANCOUNT equals 1.Configuration is the same and there are no implicit transactions.I don't need a work around as this will invalidate the migrationprocess as both products should behave identically.What would influence the difference or why is there a difference???Is there something which has been overlooked?================================================== =======The following code replicates the problemEnsure implicit transactions are off in both versions at the serverlevel, thus defaulting to autocommitted mode.Ensure sp_configure settings are identical.Step 1: Create a DB called test:Step 2: Execute the following under the context of test DB.if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[trigtest]') and OBJECTPROPERTY(id, Outrigger') = 1)drop trigger [dbo].[trigtest]GOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[test]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[test]GOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[trancount]') and OBJECTPROPERTY(id, N'IsUserTable')= 1)drop table [dbo].[trancount]GOCREATE TABLE [dbo].[test] ([id] [int] IDENTITY (1, 1) NOT NULL ,[text] [char] (10) NULL) ON [PRIMARY]GOCREATE TABLE [dbo].[trancount] ([id] [int] IDENTITY (1, 1) NOT NULL ,[trancount] [int] NOT NULL) ON [PRIMARY]GOCREATE TRIGGER trigtest ON [dbo].[test]FOR UPDATE, DELETEASdeclare @trancount intselect @trancount = @@TRANCOUNTinsert into trancount ( trancount ) values ( @trancount )Step 3: Run the following against the DB, then check trancount table.-- Add a record to the test table (trigger will not fire)insert into test (text) values ( 'xxxx' );go-- Update the value (autocommit mode) to fire trigger-- Under SQL 7 and 2000, trancount table will only indicate 1tranaction open.-- This is being performed in autocommit mode.update test set text = 'test1'go-- Update value using an explicit transaction-- Under SQL 7, trancount will equal 2 in trigger, in SQL 2000trancount equals 1begin transactionupdate test set text = 'test2'commit workgo
View 5 Replies
View Related
Nov 28, 2007
Hi all,
I have a trigger for column eISBNEnteredDate on update or insert changes of eISBN of the table Products2 ( both belong to the same table). The column eISBNEnteredDate can either be added manually along with eISBN value or when only eISBN value is entered it is updated with present date.
The problem I am facing is when I send eISBN along with eISBNEnteredDate the present date is what is getting saved. Upon the same record when a new date is updated the new date is getting saved. Can someone tell me where I am going wrong?
Here is my trigger:
Code Block
ALTER TRIGGER [dbo].[Products2_eISBNEnteredDate] ON [dbo].[Products2]
For Insert, Update
As
Begin
Declare @ProductId int
Declare @eISBN Varchar(17)
Set @eISBN = '0'
If ( Update(eISBN) )
Begin
Select @Productid = I.Productid,@eISBN = I.eISBN
From Inserted I Left Join Deleted d on I.Productid = D.Productid
Left join Products2 P on P.Productid = I.Productid
Where (ISNULL(I.eISBN,'') <> ISNULL(D.eISBN,''))
If (IsNull(@eISBN,'') <> '' and IsNull(@eISBN,'') <> '0')
Begin
Update Products2
Set eISBNEnteredDate = getdate()
Where ProductID in (select i.ProductID
From Inserted i
Left join Deleted d on d.ProductID = i.ProductID
Where (i.eISBN is not null or replace(i.eISBN,' ','') != '') --where the new eISBN is not null or blank
and (d.eISBN is null or replace(d.eISBN,' ','') = '') --where the old eISBN is null or blank
and isnull(i.eISBN,'') != isnull(d.eISBN,'') --where the new eISBN is not equal to the old ISBN13
and d.eISBNEnteredDate is null)
End
If IsNull(@eISBN,'') = '' and IsNull(@eISBN,'') = ''
Begin
Update Products2
set eISBNEnteredDate = NULL
Where ProductID = @Productid
End
End
End
View 5 Replies
View Related
Jul 23, 2005
Hi -We have two SQL 2000 Servers. We have the linked server setup and wecan perform updates and inserts between the databases. But when we adda trigger and insert something into a table, the database hangs. Thereare NO processes blocking or being block in either database. This ONLYoccurs when we have one OS as Windows 2000 Server and the other OS asWindows 2003 Server. This problem does not occur when both servers areWindows 2000. Has anyone ran into anything similar to this???*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 2 Replies
View Related
Jul 20, 2005
Hi All,I'm a relatively newbie to SQL Server 2000, having come from a MySQLbackground.I'm creating my first Trigger statement on a table, and I'd like toknow how I go about performing an update on the row that was changedwhen the trigger was fired.To explain, I have 2 columns, one which contains a member number, theother which contains a flag that is supposed to indicate whether ornot the member number in the row has changed since the last time thetable was processed for updates.So, whenever the value in the member number field [memnum] is updated,I want to set the flag [igproc] to true.The best I've been able to do is:CREATE TRIGGER [updateignoreprocflag] ON [dbo].[dd_testtable]FOR UPDATEASdeclare @key as intIF UPDATE (memnum)select @key = recid from insertedUPDATE dd_testtable set igproc=1 where recid=@keyThis seems to work, but I'd like to know if there's a better way ofretrieving the recid value of the changed row to pass to the UPDATEstatement? Also, I read somewhere in passing that using SELECTstatements and variable assignments within triggers can cause problemswhen called from other applications; in this case it will either be aweb site using ASP.or an application developed in FOXPRO. I can't findwhere I read this originally, so it's entirely possible I imagined itor misunderstood it, but I'd very much appreciate it if someone couldconfirm whether or not this is the case?Many, many thanks in advance!Much warmth,Murray
View 2 Replies
View Related
Jan 17, 2008
Hi
Can u please send the answers for this
1 . Can write more than one trigger for a single table(sql server 2000)?
2. how to create the editable gridview? While clicking particular cell it should be changed to
Edit mode , and I want option for creating new row and delete option, search option
View 1 Replies
View Related
Nov 14, 2006
Hey,
I'm new to this whole SQL Server 2005 thing as well as database design and I've read up on various ways I can integrate business constraints into my database. I'm not sure which way applies to me, but I could use a helping hand in the right direction.
A quick explanation of the various tables I'm dealing with:
WBS - the Work Breakdown Structure, for example: A - Widget 1, AA - Widget 1 Subsystem 1, and etc.
Impacts - the Risk or Opportunity impacts for the weights of a part/assembly. (See Assemblies have Impacts below)
Allocations - the review of the product in question, say Widget 1, in terms of various weight totals, including all parts. Example - September allocation, Initial Demo allocation, etc. Mostly used for weight history and trending
Parts - There are hundreds of Parts which will eventually lead to thousands. Each part has a WBS element. [Seems redundant, but parts are managed in-house, and WBS elements are cross-company and issued by the Government]
Parts have Allocations - For weight history and trending (see Allocations). Example, Nut 17 can have a September 1st allocation, a September 5th allocation, etc.
Assemblies - Parts are assemblies by themselves and can belong to multiple assemblies. Now, there can be multiple parts on a product, say, an unmanned ground vehicle (UGV), and so those parts can belong to a higher "assembly" [For example, there can be 3 Nut 17's (lower assembly) on Widget 1 Subsystem 2 (higher assembly) and 4 more on Widget 1 Subsystem 5, etc.]. What I'm concerned about is ensuring that the weight roll-ups are accurate for all of the assemblies.
Assemblies have Impacts - There is a risk and opportunity impact setup modeled into this design to allow for a risk or opportunity to be marked on a per-assembly level. That's all this table represents.
A part is allocated a weight and then assigned to an assembly. The Assemblies table holds this hierarchical information - the lower assembly and the higher one, both of which are Parts entries in the [Parts have Allocations] table.
Therefore, to ensure proper weight roll ups in the [Parts have Allocations] table on a per part-basis, I would like to check for any inserts, updates, deletes on both the [Parts have Allocations] table as well as the [Assemblies] table and then re-calculate the weight roll up for every assembly. Now, I'm not sure if this is a huge performance hog, but I do need to keep all the information as up-to-date and as accurate as possible. As such, I'm not sure which method is even correct, although it seems an AFTER DML trigger is in order (from what I've gathered thus far). Keep in mind, this trigger needs to go through and check every WBS or Part and then go through and check all of it's associated assemblies and then ensure the weights are correct by re-summing the weights listed.
If you need the design or create script (table layout), please let me know.
Thanks.
View 4 Replies
View Related
Jul 20, 2005
Are there any limitations or gotchas to updating the same table whichfired a trigger from within the trigger?Some example code below. Hmmm.... This example seems to be workingfine so it must be something with my specific schema/code. We'reworking on running a SQL trace but if anybody has any input, fireaway.Thanks!create table x(Id int,Account varchar(25),Info int)GOinsert into x values ( 1, 'Smith', 15);insert into x values ( 2, 'SmithX', 25);/* Update trigger tu_x for table x */create trigger tu_xon xfor updateasbegindeclare @TriggerRowCount intset @TriggerRowCount = @@ROWCOUNTif ( @TriggerRowCount = 0 )returnif ( @TriggerRowCount > 1 )beginraiserror( 'tu_x: @@ROWCOUNT[%d] Trigger does not handle @@ROWCOUNT[color=blue]> 1 !', 17, 127, @TriggerRowCount) with seterror, nowait[/color]returnendupdate xsetAccount = left( i.Account, 24) + 'X',Info = i.Infofrom deleted, inserted iwhere x.Account = left( deleted.Account, 24) + 'X'endupdate x set Account = 'Blair', Info = 999 where Account = 'Smith'
View 1 Replies
View Related
Dec 5, 2006
This Audit Trigger is Generic (i.e. non-"Table Specific") attach it to any tabel and it should work. Be sure and create the 'Audit' table first though.
The following code write audit entries to a Table called
'Audit'
with columns
'ActionType' //varchar
'TableName' //varchar
'PK' //varchar
'FieldName' //varchar
'OldValue' //varchar
'NewValue' //varchar
'ChangeDateTime' //datetime
'ChangeBy' //varchar
using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
public partial class Triggers
{
//A Generic Trigger for Insert, Update and Delete Actions on any Table
[Microsoft.SqlServer.Server.SqlTrigger(Name = "AuditTrigger", Event = "FOR INSERT, UPDATE, DELETE")]
public static void AuditTrigger()
{
SqlTriggerContext tcontext = SqlContext.TriggerContext; //Trigger Context
string TName; //Where we store the Altered Table's Name
string User; //Where we will store the Database Username
DataRow iRow; //DataRow to hold the inserted values
DataRow dRow; //DataRow to how the deleted/overwritten values
DataRow aRow; //Audit DataRow to build our Audit entry with
string PKString; //Will temporarily store the Primary Key Column Names and Values here
using (SqlConnection conn = new SqlConnection("context connection=true"))//Our Connection
{
conn.Open();//Open the Connection
//Build the AuditAdapter and Mathcing Table
SqlDataAdapter AuditAdapter = new SqlDataAdapter("SELECT * FROM Audit WHERE 1=0", conn);
DataTable AuditTable = new DataTable();
AuditAdapter.FillSchema(AuditTable, SchemaType.Source);
SqlCommandBuilder AuditCommandBuilder = new SqlCommandBuilder(AuditAdapter);//Populates the Insert command for us
//Get the inserted values
SqlDataAdapter Loader = new SqlDataAdapter("SELECT * from INSERTED", conn);
DataTable inserted = new DataTable();
Loader.Fill(inserted);
//Get the deleted and/or overwritten values
Loader.SelectCommand.CommandText = "SELECT * from DELETED";
DataTable deleted = new DataTable();
Loader.Fill(deleted);
//Retrieve the Name of the Table that currently has a lock from the executing command(i.e. the one that caused this trigger to fire)
SqlCommand cmd = new SqlCommand("SELECT object_name(resource_associated_entity_id) FROM
ys.dm_tran_locks WHERE request_session_id = @@spid and resource_type = 'OBJECT'", conn);
TName = cmd.ExecuteScalar().ToString();
//Retrieve the UserName of the current Database User
SqlCommand curUserCommand = new SqlCommand("SELECT system_user", conn);
User = curUserCommand.ExecuteScalar().ToString();
//Adapted the following command from a T-SQL audit trigger by Nigel Rivett
//http://www.nigelrivett.net/AuditTrailTrigger.html
SqlDataAdapter PKTableAdapter = new SqlDataAdapter(@"SELECT c.COLUMN_NAME
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
where pk.TABLE_NAME = '" + TName + @"'
and CONSTRAINT_TYPE = 'PRIMARY KEY'
and c.TABLE_NAME = pk.TABLE_NAME
and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME", conn);
DataTable PKTable = new DataTable();
PKTableAdapter.Fill(PKTable);
switch (tcontext.TriggerAction)//Switch on the Action occuring on the Table
{
case TriggerAction.Update:
iRow = inserted.Rows[0];//Get the inserted values in row form
dRow = deleted.Rows[0];//Get the overwritten values in row form
PKString = PKStringBuilder(PKTable, iRow);//the the Primary Keys and There values as a string
foreach (DataColumn column in inserted.Columns)//Walk through all possible Table Columns
{
if (!iRow[column.Ordinal].Equals(dRow[column.Ordinal]))//If value changed
{
//Build an Audit Entry
aRow = AuditTable.NewRow();
aRow["ActionType"] = "U";//U for Update
aRow["TableName"] = TName;
aRow["PK"] = PKString;
aRow["FieldName"] = column.ColumnName;
aRow["OldValue"] = dRow[column.Ordinal].ToString();
aRow["NewValue"] = iRow[column.Ordinal].ToString();
aRow["ChangeDateTime"] = DateTime.Now.ToString();
aRow["ChangedBy"] = User;
AuditTable.Rows.InsertAt(aRow, 0);//Insert the entry
}
}
break;
case TriggerAction.Insert:
iRow = inserted.Rows[0];
PKString = PKStringBuilder(PKTable, iRow);
foreach (DataColumn column in inserted.Columns)
{
//Build an Audit Entry
aRow = AuditTable.NewRow();
aRow["ActionType"] = "I";//I for Insert
aRow["TableName"] = TName;
aRow["PK"] = PKString;
aRow["FieldName"] = column.ColumnName;
aRow["OldValue"] = null;
aRow["NewValue"] = iRow[column.Ordinal].ToString();
aRow["ChangeDateTime"] = DateTime.Now.ToString();
aRow["ChangedBy"] = User;
AuditTable.Rows.InsertAt(aRow, 0);//Insert the Entry
}
break;
case TriggerAction.Delete:
dRow = deleted.Rows[0];
PKString = PKStringBuilder(PKTable, dRow);
foreach (DataColumn column in inserted.Columns)
{
//Build and Audit Entry
aRow = AuditTable.NewRow();
aRow["ActionType"] = "D";//D for Delete
aRow["TableName"] = TName;
aRow["PK"] = PKString;
aRow["FieldName"] = column.ColumnName;
aRow["OldValue"] = dRow[column.Ordinal].ToString();
aRow["NewValue"] = null;
aRow["ChangeDateTime"] = DateTime.Now.ToString();
aRow["ChangedBy"] = User;
AuditTable.Rows.InsertAt(aRow, 0);//Insert the Entry
}
break;
default:
//Do Nothing
break;
}
AuditAdapter.Update(AuditTable);//Write all Audit Entries back to AuditTable
conn.Close(); //Close the Connection
}
}
//Helper function that takes a Table of the Primary Key Column Names and the modified rows Values
//and builds a string of the form "<PKColumn1Name=Value1>,PKColumn2Name=Value2>,......"
public static string PKStringBuilder(DataTable primaryKeysTable, DataRow valuesDataRow)
{
string temp = String.Empty;
foreach (DataRow kColumn in primaryKeysTable.Rows)//for all Primary Keys of the Table that is being changed
{
temp = String.Concat(temp, String.Concat("<", kColumn[0].ToString(), "=", valuesDataRow[kColumn[0].ToString)].ToString(), ">,"));
}
return temp;
}
}
The trick was getting the Table Name and the Primary Key Columns.
I hope this code is found useful.
Comments and Suggestion will be much appreciated.
View 16 Replies
View Related
May 15, 2001
Hello,
I am trying to create a directory containing the date and then copy all the files in the current directory to it.
If I run the T-SQL script found below within Query Analyser it works fine (directory is created and files are copied in to it).
But if I run it as SQL task within DTS, only the directory is created. The files are not copied in to it!?!
I thought it maybe a permissions problem, but the SQL Server/Agent account is a local administrator and has sysadmin role with SQL Server.
I would be grateful of any assistance in this problem.
Thanks in advance,
Chris.
SQL:
declare @directoryname varchar(200)
declare @doscmd varchar(255)
select @directoryname = 'e:Audit_table_archive' + rtrim(cast(day(getdate())as char)) + rtrim(cast(month(getdate())as char)) + rtrim(cast(year(getdate())as char))
select @doscmd = 'mkdir ' + @directoryname
exec master..xp_cmdshell @doscmd
select @doscmd = 'copy e:Audit_table_archive*.* e:Audit_table_archive' + rtrim(cast(day(getdate())as char)) + rtrim(cast(month(getdate())as char)) + rtrim(cast(year(getdate())as char)) + '*.*'
exec master..xp_cmdshell @doscmd
View 1 Replies
View Related
Jul 9, 2001
does anyone know how to execute a FTP command through sql server 7? I am creation a table and need to ftp the result set. I have it currently creation the table, turning it into a CSV txt file and placing it in a directory. I have to then manually ftp the txt file. I want to automate this process but I cant get the FTP command to execute throught the xp_cmdshell. It will work at the DOS prompt though so I know the syntax is correct. Any sugetsions would be appreciated.
-Nathan
View 2 Replies
View Related
Aug 10, 2001
hi everybody
My requirement is, I have to transfer database backup files from one server to another server to take tape backup. Generally in my backup folder there will be 4days backups. I want to schedule a job so that after database backed up, today’s backup file only copied to the other server. I wrote following code, upto xp_cmdshell every thing is working fine.
But xp_cmdshell statement is giving error. I have to use variable value with sp_cmdshell. Please give me the solution for this.
declare @year1 as varchar(4),@month1 as varchar(2),@day1 varchar(2),@filename varchar(40)
set @year1=ltrim(str(year(getdate())))
if month(getdate())<10
set @month1='0'+ltrim(str(month(getdate())))
else
set @month1=ltrim(str(month(getdate())))
if day(getdate())-1 <10
set @day1='0'+ltrim(str(day(getdate())-1))
else
set @day1=ltrim(str(day(getdate())-1))
set @filename='EMPTest_db_'+ @year1+@month1+@day1+'*.bak'
xp_cmdshell @a
thanks
Keerthi
View 1 Replies
View Related
Aug 17, 2001
I am trying to run a Visual Basic Script using the xp_cmdshell stored procedure. When I try to run the file, the MS Script Debugger application is started.
I run the script like this: "xp_cmdshell 'D:ScriptFilesSpaceMail.vbs'"
I can run it from the DOS prompt successfully, but not from Query Analyzer. Does anyone have any suggestions on how to prevent the MS Script Debugger from running?
Chris
View 1 Replies
View Related
Sep 7, 2001
Hi,
I am trying to run bcp with xp_cmdshell inside a trigger. Whenver I update table the server is hanging. It creates the file in specified location but of ZERO size and I cannot delete it unless I stop SQL server service.
The smae code runs from a stored procedure without any problem.
Can you pl tell me if there are any LIMITATIONS with xp_cmdshell and Trigger.
Thanks
sekhar
View 1 Replies
View Related
Apr 14, 2000
Hi!!!
Can someone help me?
How can I get the result of following execution in some stored procedure, and work with it:
exec xp_cmdshell 'dir c:'
Thanx in advance
Laert
View 1 Replies
View Related
Nov 1, 2000
Hi,
while using XP_cmdshell for renaming a file , can I concatenate the current date to the file?
Ramam
View 1 Replies
View Related
Nov 28, 2000
Hi,
What is best way to compare two files and get the most recent one using xp_cmdshell?
Thanks
TT
View 1 Replies
View Related
Nov 28, 2000
Hi,
I am trying to execute this command.
use master
exec xp_cmdshell..'dir estserverest_dataInventory_Files*.txt /b'
I am getting the following error..
output
------------------------------------------------------------------------------Logon failure: unknown user name or bad password.
I am logged on as 'sa' for that server.
Any suggestion on how to resolve this?
Thanks in advance.
View 1 Replies
View Related
Mar 8, 2000
Hai ,
I beleive this is simple question but I have problem using this command
I was trying to delete contents of a temp folder thru TSQL.
Correct me if wrong
For example
xp_cmdshell " del c:emp*.*"
This is leading me to prompt 'Are you sure to delete Y/N'.
How do I write a command to say Y.
I want to schedule this every week. Is there any other way to do this task
like using AT command ,etc.
Thank you in advance
Surya
View 1 Replies
View Related
Mar 1, 2000
Hi !
I am trying to use xp_cmdshell
---
xp_cmdshell 'dir abcc$'
---
where abc is host name
and C$ is shared name
I get the following error message
"Logon failure: unknown user name or bad password."
I execute the same command at the dos prompt , I get the directory listing
Any suggestions??
Thanks.
KMM
View 2 Replies
View Related
