T-SQL (SS2K8) :: Trigger To Call A Program To Write Text File Onto A Folder In Server
Apr 29, 2014
I have created a trigger to call a program that is written by our program. The program is basically read the record in the table and write to a text file, then delete the record from the table.
The trigger is a after insert trigger. After we added the trigger, we insert a record to the table. The result is that the record still and did not get deleted. Also, the text file didn't get created either. It seems that it take a long time for the record to be written to the table.
But if we just run the program (a exe file), it can write a text file in the folder and delete the record. the trigger is basically:
USE [Zinter]
GO
/****** Object: Trigger [dbo].[ZinterProcess] Script Date: 04/29/2014 18:34:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
I have a VB program to sit somethere. I like to call that program with passing two parameters from a trigger on the SQL Server. The parameters are the contents of the table which the trigger is created on.
Hi, I want to create a text file and write to text it by calling its assembly from Stored Procedure. Full Detail is given below
I write a code in class to create a text file and write text in it. 1) I creat a class in Visual Basic.Net 2005, whose code is given below: Imports System Imports System.IO Imports Microsoft.VisualBasic Imports System.Diagnostics Public Class WLog Public Shared Sub LogToTextFile(ByVal LogName As String, ByVal newMessage As String) Dim w As StreamWriter = File.AppendText(LogName) LogIt(newMessage, w) w.Close() End Sub Public Shared Sub LogIt(ByVal logMessage As String, ByVal wr As StreamWriter) wr.Write(ControlChars.CrLf & "Log Entry:") wr.WriteLine("(0) {1}", DateTime.Now.ToLongTimeString(), DateTime.Now.ToLongDateString()) wr.WriteLine(" :") wr.WriteLine(" :{0}", logMessage) wr.WriteLine("---------------------------") wr.Flush() End Sub Public Shared Sub LotToEventLog(ByVal errorMessage As String) Dim log As System.Diagnostics.EventLog = New System.Diagnostics.EventLog log.Source = "My Application" log.WriteEntry(errorMessage) End Sub End Class
2) Make & register its assembly, in SQL Server 2005. 3)Create Stored Procedure as given below:
CREATE PROCEDURE dbo.SP_LogTextFile ( @LogName nvarchar(255), @NewMessage nvarchar(255) ) AS EXTERNAL NAME [asmLog].[WriteLog.WLog].[LogToTextFile]
4) When i execute this stored procedure as Execute SP_LogTextFile 'C:Test.txt','Message1'
5) Then i got the following error Msg 6522, Level 16, State 1, Procedure SP_LogTextFile, Line 0 A .NET Framework error occurred during execution of user defined routine or aggregate 'SP_LogTextFile': System.UnauthorizedAccessException: Access to the path 'C:Test.txt' is denied. System.UnauthorizedAccessException: at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath) at System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy) at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share, Int32 bufferSize, ileOptions options) at System.IO.StreamWriter.CreateFile(String path, Boolean append) at System.IO.StreamWriter..ctor(String path, Boolean append, Encoding encoding, Int32 bufferSize) at System.IO.StreamWriter..ctor(String path, Boolean append) at System.IO.File.AppendText(String path) at WriteLog.WLog.LogToTextFile(String LogName, String newMessage)
the idea is to put together a folder that is in some place in the computer, like "c:excel_fles" and the database full backup in a single .bak file, the database can be recovered with sql server management studio(but no the folder), the restoration of the program can be done with a program, I think it can be done with msdb, but I'm a little lost in the road.
Hello All, I want to write Batch Program which will insert Data into Table every day based on some conditions. can anyone please let me know how to write Batch Process in SQL server 2005?? Thanks.
I need some help calling a DLL or EXE from a SQL Trigger. I have the trigger set up, except I have no clue how to call a DLL or EXE or if it is even possible. Here is what I have for the Trigger so far:
-- ================================================ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= CREATE TRIGGER CallProgIfParentID ON Toc FOR INSERT AS IF ((select ins.[ParentID] FROM inserted ins) = '57660') -- I want to be able to programmatically change '57660' from an ASPX page EXEC -- This is where the call function goes GO
Another question I have is can a trigger be created programmatically using ASPX pages written in VB.NET in VS2003?
Dealing with 3rd party app that is somehow deleting records. Was going to put a delete trigger on the table so we can at least be notified of the delete and save the record. Great idea until I found out the table has a text data type for one of the fields which are'nt allowed on the delete or update portion of the trigger. Unfortunately the text data contains what we really need to retain. Tried doing a convert varchar and that would not work.
I have a request to write to a file whenever new record added to the table.
When records insert row by row, it goes well. but when more than 2 session insert at the same time, sometimes it duplicate some record in the file. I try to add synchonize code ( like lock , Monitor) but it doesn't work. any idea ?
I am using bcp to write from a query or table into a text file from a stored procedure. No problem. However, what do I do if I want to write to a text file from another stored procedure which returns a record set? Any help gratefully received. thanks.
basically i am trying to create a program wherein after saving a new transaction to the sql database, the fields saved will be retrieved and then written to a text file.
i read a thread here which is similar to what i am trying to do but it was in xml format..
What I am trying to do is very simple. I just want to run 2 stored procedures, then have the output from both sp's written to a single file.
I created a Data Flow task, then put 2 OLE DB Source adapters in the Data Flow task, one for each stored procedure.
Next, I dropped a Flat File destination object onto the page. However, I can only connect ONE arrow from one of the OLE DB Source adapters to the Flat File destination. It won't let me connect both.
What am I doing wrong here? How can I accomplish what I am trying to do here?
Hi, I have a question about sql server trigger. For example, I have a trigger for table insert. The way it works is: A: my program run the insert query, and wait, then the record is inserted, then the trigger is launched, after the trigger job done, my program then return, or B: my porgram run the insert query, and wait, then the record is inserted, then my porgram returns, then the trigger is launched. Basically, my program does not have to wait the trigger job done. Which way is right? or neither one is right. The problem we are facing right now is, we need to run a pretty big stored procedure and it takes a long time. I am thinking about move some of them to a trigger job, so our porgram don't have to wait for everything done. Any inputs are welcome. Thanks.
I have a sp which saves the necessary information regarding the status of action(whether success or failure, rows affected etc) to a log table say( StatusLog )After this, I was sending a database mail with information taken from the log table via sp_send_dbmail. Now I would like to write the status information to a 'txt' file instead of sending via mail.How can I write to a text file from a stored procedure in ms sql server 2005?
I have a table with a column [FileData] of type TEXT or XML. I need to write the content of the column to a file, using sp_OAWrite for example, but the stored procedure who needs to read the content and write it to the file does not work because the limitation is no local variable for those types. What use is that datatype if i can not use it in procedures like this?
Anyone out there who could give me a tip on how to solve that?
We noticed SQL Server 2005 is creating Program FilesCommon FilesMicrosoft SharedDW on our largest drive for each 64-bit installation. Does anyone know what this is? It appears there is no Microsoft documentation regarding this installation and if we need to keep it. It may be .NET related, but I have no idea why it is needed.
C:Program FilesMicrosoft SQL Server90DTS folder missing. Why is this folder missing on my XP machine. I installed DTS backward compatibility components, SQL SP1 and Hot fixes. Im installing it on a non-server. Is this the reason?
Running NT 4.0, sp6a. and sql 2k. I have a DTS job that works just fine if i go to 'design view' and then 'execute' it. But if I schedule it, the following error appears.. My question is... why? Also I modified the path from the current below (denapp02cmc$cd01.txt to the local path of the server as well... still came up with the same problem. ?? TIA! ::
DTSRun: Loading... DTSRun: Executing... DTSRun OnStart: Copy Data from Results to denapp02cmc$cd01.txt Step DTSRun OnError: Copy Data from Results to denapp02cmc$cd01.txt Step, Error = -2147467259 (80004005) Error string: Error opening datafile: Access is denied. Error source: Microsoft Data Transformation Services Flat File Rowset Provider Help file: DTSFFile.hlp Help context: 0 Error Detail Records: Error: 5 (5); Provider Error: 5 (5) Error string: Error opening datafile: Access is denied.
I want to check a table to see what rows have been updated today, then write to a text file some data from the selected rows; then I want to automate this (DTS package? TSQL stored procedure job?) to run every night at midnight. Is the DTS Wizard the best way, that's what I did, but have not confirmed that it is writing a new text file every day, and does each new version write over the old one?
We've all seen this, where it uses an individual .write statement for each column.
Code Snippet
Public Overrides Sub AWCCogent_ProcessInputRow(ByVal Row As AWCCogentBuffer)
With textWriter
Dim item As Object
If Not Row.AddressID_IsNull Then .Write(Row.AddressID) End If .Write(columnDelimiter) If Not Row.City_IsNull Then .Write(Row.City) End If
.WriteLine()
End With
End Sub
But hard coding this seems not the smartest way. Especially since in my text file, there needs to be close to 100 columns. This could be a nightmare to update down the road. But I can't seem to find the object collection to loop through, like row.items which would seem to be logical.
How do we connect and get data from SAP database using SSIS 2012/2014.Currently we are extracting data using WebService, and BAPI function call for RFC, but it seems not so much reliable, due to timeout and others. ion providing the link for download of .NET Framework Data Provider for mySAP Business Suite and some sample to invoke and get the data using SSIS.
I've got a query that returns the data I need. I want to put the query in a stored procedure such that, when the SP runs I get a pipe delimited text file on disk. I don't really want to mess with SSIS, etc. Is there a Q&D way to do this?
I see that a recent related message was posted about built-in accounts.
Anyway, a vendor set up system using SQL 2014. The SQL Server Agent service was running under the 'Local System Account'.
Agent jobs were failing because they couldn't write to a shared folder on the same server. I decided to change the account to a regular privileged domain user. Here is the error message:
Executed as user MYDOMAINSQL2014Agent Cannot open backup device 'EackupMyBackup' Operating system error 5(Access is denied). [SQLSTATE 42000] [Error 3201] BACKUP DATABASE is terminating abnormally [SQLSTATE 42000] [Error 3013]. The step failed
I granted Full Control to that domain user and it still failed. I added 'Everyone' and gave it Full Control and it succeeded.
I understand that the account under which the Agent is running should have Write permissions on the share. However, I must have missed something. The only way I can get this to work is to grant Write or Full Control to Everyone. I absolutely do not want to do that.
tblHIT - this table will reveive record every minutes Date | Time | Main_ID | Hit ------------------------------------ 3/1/2006 | 100 | 200 | 8700 ... ... ... 4/14/2008 | 100 | 200 | 4500 4/14/2008 | 100 | 201 | 8700 4/14/2008 | 200 | 200 | 3500 4/14/2008 | 300 | 201 | 7700 ... ...
I've also 1 job in SQL Server Agent will execute STORED PROCEDURE and will filter 7 days record before from current date and insert it into tblHIT_7days. tblHIT_7days shown as follow tblHIT_7days Date | Time | Main_ID | Hit ------------------------------------ 3/7/2008 | 100 | 200 | 8700 ... ... ... 4/13/2008 | 100 | 200 | 4500 4/13/2008 | 100 | 201 | 8700 4/13/2008 | 200 | 200 | 3500 4/13/2008 | 300 | 201 | 7700
I've plan to create TRIGGER. This TRIGGER will insert the appropriate record from tblHIT into tblHIT_7days and also delete a appropriate record in tblHIT_7days. This INSERT and DELETE transaction depend on current date.
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
I need to write a process to get file size in kb and record count in a file. I was planning on writing a c# console app that takes the file path and name as a param however should i use a CLR?
I cant put a script in the ssis when it's bringing the file down because it has been deemed that we only use ssis for file consumption.
I am having problem with moving a file from one folder to another folder. Here is the detailed scenario:
I want to move a input.csv file from shared input folder to shared archive folder. i am using the below code to do this. declare @inpath varchar(100) SET @inpath = 'move "\abcdefINPUTinput*.csv" "\abcdefARCHIVEarchive.csv"' EXEC @filestatus = master..xp_cmdshell @inpath but the problem was it was cutting the input.csv file from INPUT folder but not pasting it in the ARCHIVE folder. I really appreciate if anyone can help me to solve this or anyone can tell some workarounds.
