We are using an old version of Numara TrackIT for our helpdesk software, and it doesn't have much in the way of configurable options. There is no way to set validation or formatting on the text fields in the program.
There is a field, WO_TEXT1, Which I would like to be formatted as 6 characters, 3 integers + a period + 2 integers. The first the integers would be padded with zeros on the left, and the last 2 integers would be padded with zeros on the right.
IE, if someone enters 2, it would actually end up being 002.00
If someone enters 3.5 it would end up being 003.50
If someone enters 12.1 it would end up being 012.10
If someone enters 172.80 it would end up being 172.80
I was hoping to achieve this via an update trigger.
Below is the guts of the trigger I created, mostly as a proof of concept.
-- This update properly formats the Estimated Hours field
Update t SET WO_TEXT1 = (SELECT RIGHT('000000' + CONVERT(VARCHAR(6), WO_TEXT1), 6) FROM inserted)
FROM dbo.TASKS as t
Where (EXISTS (SELECT * FROM inserted WHERE WOID = 24773));
I expected that this update trigger would only affect the Work Order with a WOID of 24773. Unfortunately, it updated all 21000 work orders in our system, wiping out all of the actual estimated hours that had been inserted by technicians!
Luckily I had a report that I could quick dump the 300 or so active work order's estimated hours back into the DB from (all the other Work orders are closed, and no one really cares about their estimated hours).
My question is three fold,
1) Why did my trigger update every record in the tasks table instead of just WO 24773?
2) Is using a trigger the best way of accomplishing what I'm trying to do?
3) if a trigger is the best way of accomplishing this, what should my trigger look like?
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)
Hi, How can I convert a text file (.txt) into SQL in ASP.net 2.0 ? The sample of the file format is like that ... 09/03/2007 08:41 "Fung, Kitty" Granted Access D1 Main 2354 111 09/03/2007 08:42 "Ng, Jaclyn" Granted Access D1 Main 21906 18 09/03/2007 08:42 "Leung, Agnes" Granted Access D1 Main 21920 18 Cheers
I have the following Query which gives the output in XML format. select * from <Table_Name> For XML auto I wanted to store this output in a .Xml file at my local machine. I would prefer to have a proc when called generates the xml and stores it on the local m/c. Any ideas ? Thanks,
I have a database of over 200 tables. I want to delete data in about 100 tables. It's hard for me to delete because there are so many dependencies. I wind up tracing each table until I find the end and then deleting backwards. Is there a script I can run where I can get a textual view of which tables are related to which tables (foreign key constraints). I don't want to hit 'display dependencies' on each table. I know I can look at logical diagram but with a database of over 200 tables, it is hard to look at it.
Hello. I am in the process of migrating an old app to SQL Server. The old app reads hundreds of different flat file formats. One of the more complex ones is a multi-format delimited file. For example:
01^Bob Johnson^123 Main St^Anytown^St 02^Book1^$20 02^Book2^$30 03^Gift Cert^Happy Birthday^$100
This file is delimited with the ^ character. Note that the first 2 characters identify the row type. All 01 rows have data in the format: Name, Street Address, City, State. All 02 have data in the format: Book name, price. Etc.
Any clever ideas on how to parse this? I tried setting it up as a flat file source with the ^ delimiter. It doesn't work - in this example it wraps the third row to the end of the second row and keeps adding columns to fill out the row.
The only option that I can think of is to pull the entire row into one long column, and then use a script component to manually substring each column out.
If I tries to extract data as Delimited the columns are not containing the same data for each row as there are some wired characters in the "Description" filed.
How can I extract this data as proper csv file? If not is there a way to extract only ther first line in detail row as shown below
How can I get this exported as it is to the .txt file?
Currently I have acheived this with SSIS, where I have kept the header template, footer template in my folder. The SSIS generates the .txt file and then I use the execute process transformation to merge the files and data. I am getting the exact o/p as I want. But in this I am intorducing lot of IO in the package by every time creating a new header for the data to be replaced as todays date and the period to be replaced by the user selected Report Period.
How can this be achived using SSRS? Is any custome code required? Thanks in advacne for your help.
We use an internal tool that can display ASP pages. I would like to use SQL Reporting Services to create reports in ASP format. The ASP page will access the database to get data. Is this possible possible with Reporting Services?
I've got a field I'm trying to convert into a date format as it currently sits as a text field. The date setup is of the format DD/MM/YYYY. Some of the fields are NULL, however for the sake of filling in gaps I've just set the nulls to '01-01-1905'.
I've tried to use CAST to change the data into a readable format for SQL Server however I tend to get one of two messages:
Conversion failed when converting date and/or time from character string.
We have migrated a CRM Database from SQLServer 2000 to SQLServer 2005.
Database contains very sensitive data about customer in text format (Datatype varchar(20)) how can i encrypt the same without any change in the table design.
I have an access table that has a primary key (entitled "ID Number"), no duplicates, the field is an integer.And, importantly, the value is set to "increment".The format is "phd"000 - so it starts out phd001, phd002, and so on...How to do this in an SQL table? Can that format be done? Or is it better not to do it via SQL but in coding instead?
I have written a trigger that emails a specified person.
I am trying to include a body of the email which comprises of the stock level and a warning. Pulling out my hair...Help
Code so far in the trigger is :
CREATE TRIGGER Warnings ON [dbo].[tbl_sql_cartridges_kh] for update AS declare @SL as int declare @SS as int declare @Msg as nvarchar(100) set @SL= (select stock_level from inserted) set @SS =(select cartridge_key from inserted) set @Msg = 'Print Cartridges Level Warning' if @SL < 3 begin
exec sp_send_cdontsmail 'Print-Cartridges','XXX@XXXX.co.uk','Print Cartridges Level Warning',@Msg end
I would like the @Msg to say something like Cartridge XXX stock level is YYY, where XXX and YYY are taken from the table after update. I can get the values, but cant put them in the MSG string..
I have a table that has a number of data fields,I need to be able to capture datatime when the date field was entered or entered value changed.I was told I need to create a trigger on that table that contains all the fields.
I have seen the syntax for creating triggers, and read some documentation but I am still in the dark as how to create what I need to.
I was hoping to see if somebody had a similar example or an advice, anything is more than what I have at the moment.
CREATE TRIGGER NotifyDateFieldUpdates ON RelocateeRemovalist For INSERT, UPDATE, DELETE AS DECLARE @RemovalistNumber VARCHAR(200) DECLARE @RelocateID INT
/*InspectionDate */ DECLARE getInsp CURSOR FOR SELECT RelocateID,RemovalistNumber FROM INSERTED a LEFT JOIN DELETED b ON (a.RemovalistNumber=b.RemovalistNumber and a.RelocateID=b.RelocateID) WHERE a.InspectionDate IS NOT NULL AND b.InspectionDate IS NULL
OPEN getInsp
FETCH NEXT FROM getInsp INTO @RelocateID, @RemovalistNumber WHILE (@@FETCH_STATUS <> -1) BEGIN INSERT INTO RelocateeRemovalistFieldEntry(RElocateID, RemovalistID)SELECT RelocateID,RemovalistID FROM INSERTED a LEFT JOIN RelocateeRemovalistFieldEntry b ON (a.RelocateID=b.RelocateID AND a.RemovalistNumber=b.RemovalistNumber)WHERE b.RElocateID is null
UPDATE RelocateeRemovalistFieldEntry SET InspectionDateDateTime=GETDATE() WHERE RelocateID=@RelocateID aND RemovalistNumber=@RemovalistNumber
FETCH NEXT FROM getInsp INTO @RelocateID, @RemovalistNumber END
DEALLOCATE getInsp
GO
This is what I was able to come up with so far,but when i check the syntax it gives me an error "Ambiguous column name "RelocateID" and "Ambiguous column name "RemovalistNumber" I don't know what is it trying to tell me here and couldn't find much help.
Since we have given Null to ''Workphone'',''Cellphone '', ''Email ID''.when inserting the data into these table.if the particular columns are empty while inserting also the data will get populate into the table.And.I need is if these columns are ''Workphone'',''Cellphone'' , ''Email ID'' they cant insert the data into table.
Like it must trigger like ''Please enter atleast one of these ''Workphone'',''Cellphone'' , ''Email ID'' columns.
Hi everybody,How can I Update a field from another table by Trigger? Can someone sendme the statment to do it?I have a table called Clients with fields : ID_Clients, ClientAnd Another called Doc with fields : ID_Doc, ID_Clients, ClientThese tables are in different databases and I would like to esure theintegrity by add a Trigger to update in Docīs table the field Clienteverytime itīs changed in the Clientīs table.Thanks for Attetion.Leonardo Almeida*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
Hii have 2 Tablefirst one : Customerwith 4 Fields : cst_no,cst_name,total_Debit,tot_creditsecond one : Transactionwith 5 Fields : Trns_no,Trns_Date,cst_no,debit,creditMY QUESTION:HOW TO CREATE TRIGGER FOR UPDATE TOT_DEBIT AND TOT_CREDIT FILEDS INCUSTOMER TABLE FROM Transaction TABLEThank you
I have created a trigger that I can enter and works great from the sqlcmd terminal. When I try to submit the same query in a .NET project I get an error that "Create Trigger must be the first statement in a batch". The Create trigger is submitted by itself through the sqlcommand.executenonquery() method.
I am trying to create a database for a project but the only thing that I can't seem to get working is submitting this trigger.
I need to search a nvarchar field based on the format of the text. This field holds values in two formats: 000 000 000 000 and 000000. I only want to search through the records that are in the 000 000 000 000 format. Can anyone give me direction on how to go about doing this or give me some key words to search for on Google? Fixing this problem is not an option. This is a county tax DB from a poor county with almost a million records in it. Thanks for the help!
I want the users of my site to be able to write a couple of paragraphs, save it to SQL Server and then have it read back and look the same. I don't want the users to be able to add any html to the text they submit. I just want them to be able to seperate or indent their paragraphs.
At one point, I had a textbox that saved text to the server and read it back the same way it was originally inputed. However, I can't figure our what I had done to make it work.