Hello, theres,
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 ?
Regards,
Agi
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
[Code] ....
Hi,
I tried to backup the master key by the following syntax :
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'mypassword'
BACKUP MASTER KEY TO FILE = 'c: empmaster' ENCRYPTION BY PASSWORD = 'mypassword'
but it failed and i got the following message:
Cannot write into file 'c: empmaster'. Verify that you have write permissions, that the file path is valid, and that the file does not already exist.
NB: I am using the "sa" user to execute this command.
I know that we have a security permission issue , but where and how ?
Regards,
Tarek Ghazali
SQL Server MVP
I have table T1 with the fields: ID,Type,Status,F1,F2,F3,F4 in database1. I also have T2 in Database2 which has the same fields and some extra fields. Now based on the T1.Type=Insert or Update, I need to perform either update or Insert in T2 based on where T1.ID=T2.ID and T1.Status<>’Done’. If this is a success, I need to set T1.Status=’Done’ for the updated records. So this should only be updated records. T1 is a frequently inserted table, so there might be more than one record coming there at the same time. How should I write my insert trigger and correctly set T1.Status=’Done’, any example would be greatly appreciated.
View 9 Replies View RelatedI have a table with a last_updated field and a review_date field. I would like to write a trigger that when the last_updated field is updated, it adds 6 months to the review_date field.
This is what I started, not sure of how to finish it:
CREATE TRIGGER Update_Review_Date
ON dbo.Category_Type
FOR Update
AS
Hi,
I am pretty new to MSSQL server environment. In Oracle i write Trigger and compile it at command prompt in case of SQL plus the same way i need to know where we write the script for SQL server. Is there any menu driven editor where we can write or simply writing a trigger in SQL Query analyzer and executing it will work
Please advice.
Thanks and Regards
D. Pavan Kumar
hi
I want to audit data before update or delete with modified date,userid,machineid
How to write trigger for it
Example
-------
I have a table as Immigration With Following fields
Employee_id int(foriegn key)
Passport no varchar(50)
Issue date datetime
Expiry date datetime
Iwant to store data or audit data before update or delete data from above table
Malathi Rao
Great.
Can you give it a first try? Post it and we will fix it.
Hi all,
i want to write a trigger which returns a row to the application whenever a row is inserted into a table.
E.g.
When row R1 is inserted into a table T1.
the trigger should return the R1 to the application.
Wanna be coding monkey...
I have table T1 with the fields: ID,Type,Status,F1,F2,F3,F4 in database1. I also have T2 in Database2 which has the same fields and some extra fields.
Now based on the T1.Type=Insert or Update, I need to perform either update or Insert in T2 based on where T1.ID=T2.ID and T1.Status<>€™Done€™. If this is a success, I need to set T1.Status=€™Done€™ for the updated records. So this should only be updated records.
T1 is a frequently inserted table, so there might be more than one record coming there at the same time. How should I write my insert trigger and correctly set T1.Status=€™Done€™, any example would be greatly appreciated.
Hello All,
As we have INSERTED,DELETED tables to trace what values are inserted and deleted, how to write triggers for Updates on the tables.
Your help would be appreciated.
Shiva Kumar
Hello ExpertsI am new in Sql triggers and i would like some helpI have a Table called ITEM and another callded SOURCE[ITEM]ItemNo,ItemName,SourceNo[Source]SourceNo,Sourcei want to create a Trigger so when i delete a record from [SOURCE] allthe records in [ITEM] where item.SourceNo = [SourceNo that I deletedfrom SOURCE]How can i do that?
View 1 Replies View RelatedOne of my table called as 'customertable' contains following fields
customername, emailid, subscriptionendperiod
Example records are:
david, david@john.com, 12/20/2005(mm/dd/yyyy format).
My question is that: Just one month before subscriptionendperiod that is on 11/20/2005(mm/dd/yyyy) an automatic email should go to david@john.com with the message 'Your subscription period ends on 12/20/2005'
How to write trigger for this.
Please Note : No ASP code is invloved in this.
Only MSSQL coding to be done.
Regards
how to i write a trigger to enforce this constraint..A rental can be made only if the customer is registered with the company and the car is not currently rented out. If not, the rental will not be successful.
View 1 Replies View RelatedHello,While working through my encryption questions from preivous posts, I amfinding that I may have to resort to use triggers to do the encryption(not that this is the only way, but might be the best way for mycircumstances).I would like to create a trigger that will encrypt the field before thewrite is committed.I've found serveral posts about triggers, but nothing that fits what Iwish to do.Upon an insert or update I want to modify the ssn field with this:cast(EncryptByAsymKey(AsymKey_ID('Student_aKey'), cast(SSN asnvarchar(11))) as nvarchar(40))so, ssn '123456789' in SSN would become <something encrypted> in SSNThis is the trigger I have so far, but it is generating an error:CREATE TRIGGER F_Student_SSN.eSSNON F_STUDENT_SSNINSERT, UPDATEAS SELECT cast(EncryptByAsymKey(AsymKey_ID('Student_aKey'), cast(SSN asnvarchar(11))) as nvarchar(40))TIARob
View 3 Replies View RelatedHi
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
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.
Hi,I am trying to use BULK INSERT with format file. All of our data hasfew bytes of header in the data file which I would like to skip beforedoing BULK INSERT.Is it possible to write format file to skip these few bytes ofheader before doing BULK INSERT? For example, I have a 1 GB data filewith 1000 byte header. Except for first 1000 bytes, rest of the data isgood for BULK INSERT.Thanks in advance. Sorry if it is really a dumb question as I am newto BULK INSERT and practicing still.Bob
View 7 Replies View RelatedIs there any real purpose to the File Connection Manager? Not the Flat File Connection Manager, just the plain old File Connection Manager.
I have one in my SSIS package because I thought it might make writing to a file easier. But from what I can tell, it doesn't really do much. I have to pull the file name from the connection manager and basically open and manage the file myself in my scripts.
I went this route because I have two scripts that write to the file in turn... one writing out parent records and the other inserting the child records after each parent.
The script looks like this:
Code Block
Dim outFile As String
outFile = Dts.Connections("TestFile.LDIF").AcquireConnection(Dts.Transaction).ToString()
Dim sw As New StreamWriter(outFile, True)
sw.WriteLine()
sw.WriteLine("dn: " & Dts.Variables("GroupID").Value.ToString())
sw.WriteLine("changeType: add")
sw.Close()
I have to close the connection every time because the other script uses the file. It seems that this is quite inefficient. Am I using the File Connection Manager incorrectly? What's a more appropriate way to do this?
I could open the file in a different script and store the StreamWriter object in a global variable, I suppose...
Thoughts, suggestions?
Thanks.
J
I need how we can write into a file result of query in SQL.
i need to write into a file the details we get after executing a query.
I'm a junior programmer, and i must write/append several string into a file
but i don't know how to do it??
In oracle i used UTL_FILE
Hi, How can I read a file on the server and insert it into my SQL Server database. The user is not uploading the file, but it already exists on the Server. Thanks
View 1 Replies View RelatedHi,
I need help please.
I have a image field in SQL called AttachData.
I need to retrieve the information and save it on my computer.
I tried the following but it fails on the line 7.
How can I write the data from sql to my computer?
Here is my table in sql:
CREATE TABLE [dbo].[MailAttachment](
[MsgID] [int] NOT NULL,
[AttachName] [nvarchar](255) COLLATE Hebrew_CI_AS NULL,
[AttachType] [nvarchar](255) COLLATE Hebrew_CI_AS NULL,
[AttachSize] [int] NOT NULL DEFAULT (0),
[AttachCompSize] [int] NOT NULL DEFAULT (0),[AttachData] [image] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]1 Dim s As New _
2 System.IO.FileStream("c:emails" _
3 & "" & AttachName.ToString, System.IO.FileMode.CreateNew,System.IO.FileAccess.Write)
4
6
7 ' s.Write(CType(AttachData, Byte()), 0, CInt(AttachSize))
8
10
11 s.Close()
I don't have direct access to the SQL server. I have to write/read through a class I recieved from the IT department. The class also had an example in it but I can't get it to work correctly.
I get an error: Object reference not set to an instance of an object. Line 35
Dim clsWDD As New WebDevDB.WebDevDB
Dim intLeng As Int32
Dim intLock As Int32
Dim strAppl As String = "MyApplication"
Dim strData As String = "This is my text data!"
Dim strFile As String = "MyFile"
intLock = clsWDD.LockTextFile(strAppl, strFile) 'line 35
intLeng = clsWDD.WriteTextFile(strAppl, strFile, strData, WriteAction.Create)
intLock = clsWDD.UnlockTextFile(intLock)
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.
View 1 Replies View RelatedI need to write data returned by a stored proc to an XML file. The resultant file will then be consumed by an external application. The file has to adhere to a specific schema. Is there a way to do this through SSIS? Is .NET CLR a better option?
View 1 Replies View RelatedI need to write data returned by a stored proc to an XML file. The resultant file will then be consumed by an external application. The file has to adhere to a specific schema. Is there a way to do this through .NET CLR in SQL Serer 2005? Is SSIS a better option? Thanks.
View 5 Replies View Related
How to write to a flat file, using a stored procedure?
Thank you,
SQL Server 2005 (SP2).
Smith
How do I write the record set to a .csv file using oledb in vb.net? If anyone has sample code to write to .csv file, I greatly appreciated.
Hi,
I m new to SQL,I have created a Stored Procedure that writes a file on SQL server,but I want that it should write file on another machine.How can it be done.
I know oracle uses UTL_FILE procedures lik putline, but microsoft?
Thank you for trying to help.
hello!
can somebody tell me how i can redirect the result of my
select statement to a file.
thank's
May i know whether i can write some text/string to
a file from inside a stored procedures?
Currently i can retrieve data from a query and use bcp
to write it to a file but i need to add some others text
to the same file.
Thanks for any reply!