Is there a way I can perform an update on a table, but ignore the
trigger (or disable the trigger) each time I run a particular update
script from a DTS package?
I would like subsequent DTS steps to use the trigger except for my last
update statement. Is this possible?
Thanks,
Frank
*** Sent via Developersdex http://www.developersdex.com ***
This isn€™t an problem as such, it€™s more of a debate.
If a table needs a number of update triggers which do differing tasks, should these triggers be separated out or encapsulated into one all encompassing trigger. Speaking in terms of performance, it doesn€™t make much of an improvement doing either depending upon the tasks performed. I was wondering in terms of maintenance and best practice etc. My view is that if the triggers do totally differing tasks they should be a trigger each on their own.
www.handleysonline.com
I need to average a column but ignore records that have a 0. Any thoughts?
View 1 Replies View RelatedThis was not intended here.
View 2 Replies View RelatedHello Everyone,
When i checked the machine this morning i saw that MS DTC service was stopped and when did start it, it was good for a sec or two then it stopped auto matically i was not sure waht was goin on...
Then i checked the Eventviewer, it said the log file is full i tried out to find ms dtc log reset or increase the size but could not find out..where can i find this.
One more thing what is the purpose of DTC, i know in sql 6.5 we have everything in enterprize manager but i can't see the same either in 7.0 or 2000 is the name changed or is it linked to some other source.
Like linked server , does this depend on MS DTC..how and what is the basic purpose of this..kindly tell me what i should do now...
I got to fix it asap..big hassle..
Thanks in advance,
Kavitha
Hi
Does anybody know while loading data from text file into sql server, how
can we ignore milliseconds.
regards
JK
In my stored procedure I'm calling a buggy and flaky stored procedurethat comes from a third party. When I run my stored proceure from QA,I'm getting a whole buch of errors raised inside the third party one.Is there any way I could just ignore them, so that if I run my SP fromQA, only errors from my code, if any, show up?TIA
View 2 Replies View RelatedHi,I'm trying to upload a large number of log entries currently stored astext files into a database table using bcp. For a few rows I get a"right truncation" error and the offending rows are not uploaded to thetable.I don't want to increase the size of the table varchar fields becauseit's only about a dozen out of almost million rows that have thisproblem ... I want to provide an override - i.e. if a row will resultin truncated data, truncate but still bulk copy the offending row. Isthat possible?I couldn't find such an option in the documentation.Any help is greatly appreciated.Thanks,Mudassir Latif
View 2 Replies View RelatedIs there a way to make a SP ignore an error?e.g. I'm looping through each database on a server, checking of a tableexists then selecting a value from that table. Now I have a database putonto the server where the table exists but all column names aredifferent, my SP is not interested in this database so when it errorswith invalid column name I want it to move onto the next databse and notdisplay any error message.
View 2 Replies View RelatedI'm writing a store procedure to accept search strings from user on my site. Currently, this is what I have.
Code Snippet
@schoolID int = NULL,
@scholarship varchar(250) = NULL,
@major varchar(250) = NULL,
@requirement varchar(250) = NULL
--@debug bit = 0
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SELECT * FROM [scholarship]
WHERE ([sectionID] = @schoolID OR @schoolID IS NULL)
AND ([schlrPrefix] LIKE '%' + @scholarship + '%' OR [schlrName] LIKE '%' + @scholarship + '%' OR [schlrSufix] LIKE '%' + @scholarship + '%' OR @scholarship IS NULL )
AND ([Specification] LIKE '%' + @major + '%' OR @major IS NULL )
AND ([reqr1] LIKE '%' + @requirement + '%' OR [reqr2] LIKE '%' + @requirement + '%' OR [reqr3] LIKE '%' + @requirement + '%' OR [reqr4] LIKE '%' + @requirement + '%' OR [reqr5] LIKE '%' + @requirement + '%' OR @requirement IS NULL )
The problem is, somtimes the search doesn't work if there is a space behind or infront of the search string. I wonder if there is away to ignore any spaces and go right into whatever character comes next or after. If so, how do I implement that?
I have a stored procedure that does several steps.
During the stored proc, error messages are produced when certain conditions warrant. But I want to continue anyway....
ex. in a loop in the proc...
SET @strSQL = 'Update Table1 SET col1 = ''' + @strVariable + ''''
EXEC (@strSQL)
--ERROR created by the exec statement.....
When I schedule this as a job, the first error message makes the job fail.
How can I force the proc to run completely, even if an error occurs?
Thanks in Advance..
Dano
sqltech@yahoo.com
Hi! I'm wondering if there is a way to have the AVG() not to include the zero amount as part of the calculation. I'm looking
the field, PurchPrice and RepairCost that are used by the AVG() function...
Code:
SELECT Year, MakeID, ModelID, Style, AVG(PurchPrice), AVG(RepairCost)FROM vwAvgPurchase
WHERE StockDate >= (GETDATE()- 365)
GROUP BY Year,MakeID,ModelID, Style
I don't want to do this way...
Code:
SELECT Year, MakeID, ModelID, Style, AVG(PurchPrice), AVG(RepairCost)FROM vwAvgPurchase
WHERE StockDate >= (GETDATE()- 365)
and (PurchPrice > 0 or RepairCost > 0)
GROUP BY Year,MakeID,ModelID, Style
because the PurchPrice would sometime be zero and the RepairCost wouldn't be zero.
I am importing data into a SQL table and there is a potential for duplicate records to be coming in. How do I simply ignore the duplicates and add only the records that do not violate the keys?
View 6 Replies View RelatedHi,
I have a query like this one
SELECT expense_id, CAST(expense_id AS char(10)) + ' - ' + CAST(trip_km AS char(5))+ ' - ' + CAST(expense_amount AS char(5)) + ' - ' + charge_centre AS ExpenseDesc
If charge center is null, I need to ignore this field. How can I achieve this? The reason is that if any of the field is null, it will return ExpenseDesc as null.
Thanks
if there is an error in the trigger then the update to the table does not happen. is there a way to make sql ignore errors in a trigger and still update the table
View 4 Replies View RelatedHi,
I have 2 tables in which there are 2 fields which hold the same piece of data. In one of the fields however there is a "_" character within the value.
So when comparing the 2 directly they will not be the same.
So the example is the following
Table 1, field 1 value is 1000220_1
Table 2, field 2 value is 10002201
So in table 1 the "_" is always in the 8th position.
Does anybody know a way in the JOIN statement how I can maybe ignore the 8th character in field 1 ? Or ignore any "_" it finds?
Any help is appreciated!
Thanks,
Michiel
Hi,
I am trying to import data from a Text file into a database Table using SQLserver BCP utility. I am able to do that when I have all new records in my Text file. But I am getting primary key violation error when I am trying to import the record which is already existing in the table. This is correct, but I want my program to ignore these errors and import only those records which are fine.
I tried [-m maxerrors] option, but it is not working. My BCP program is getting interrupted at the first error itself, even if I give [-m100] option.
my command looks something like this,
bcp pub..employee in C:data.txt -b1 -m100 -c -t, -Sdatabase -Uuser -Ppassword
here -b1 is, processing 1 row per batch transaction
-m100 is, ignoring first 100 errors
please help.
thanks
madhu
I am looking for best practice when passing a parameter to stored procedure that is not needed. For example, sometime the users will want the list to list only by certain state. Other times the user want all states. How can I make the SP to ignore the where clause if users want all states.
CREATE PROCEDURE usp_Example
@State nvarchar(2)
AS
SELECT FirstName,
LastName,
State
FROM SomeTable
WHERE State = @FirstName;
GO
hi,
sp_spaceused return 2 set of results. How do i store this result into a table?
Thanks
Using SQL2000. According to Books Online, the avg aggregrate functionignores null values. ((3+3+3+3+Null)/5) predictably returns Null. Isthere a function to ignore the Null entry, adjust the divisor, andreturn a value of 3? For example:((3+3+3+3)/4) after ignoring Nullentry.If there's more than one null value, then adjust divisor accordingly.For example: ((5+5+5+4+Null+5+5+Null)/8) would be ((5+5+5+4+5+5)/6)after nulls ignored.Thanks for any help or advice.
View 8 Replies View RelatedHi,I believe my SQL server was configured as Case sensitivity. I have anumber of stored procedures which were moved from a non-Casesensitivity SQL server. Because of the Case sensitivity, I have to doa lot of editing in those stored procedures. Is there a quick way toavoid the editing?Something like ignoring the case in one statement?Thanks in advance, your advice will be greatly appreciated.
View 2 Replies View RelatedI'm using Microsoft SQL Server Management Studio. I've created a view (very basic two field select with a inner join) -- I expect the query to take some time as there are about 1.7 million records in one table and about 3 million in the join table. Problem is, I always get a timeout error when I try to run the query.
I've checked the server settings and "Execution time-out" = 0 (no time-out). Checked this at the server and in Microsoft SQL Server Management Studio. So my question is why am I getting a time-out when I have it set to not time-out?
What is going on?
Thanks, Rob.
I have a SQL 2005 Express installed in my machine and when I try to access it with the following Connection String:
Driver={SQL Server};Server=.SQLEXPRESS;initial catalog=xx;user id=xx;password=xx
I connect, but when I try to access any table from this database I get an error indicating that the object doesn't exist and if I use the fullname xx.table I get no errors.
What may be happening?
Thanks in advance
I want to bulkcopy a pipe delimited text file that has two header records to SQL Server using ADO / C#. The first record contains the datetime the file was recreated and the second record contains the column definitions, both records start with a # character (see below).
# May 15, 2008 12:12:12345
# col1|col2|col3
col1rec1data|col2rec1data|col3rec1data
col1rec2data|col2rec2data|col3rec2data
Is there a way to ignore the two header records when building the select statement for the text file or in the schema.ini?
I have a table that has a date column formatted as char(6), MMDDYY. The dates need to be converted to datetime. I use the following command:
UPDATE Table1
SET CorrectedDate = CONVERT(datetime, SUBSTRING(Date, 5, 2) + SUBSTRING(Date, 1, 2) + SUBSTRING(Date, 3, 2), 1)
That works until it hits a date formatted incorrectly. The date could have blank spaces, double zeros or an invalid month/day/year (e.g. 033986). The command stops with a conversion error and no values are written to the CorrectedDate fields.
Is there a way to have the command skip the invalid dates and continue to write the datetime conversion for the dates that are formatted correctly?
Edit: Forgot to mention this is on SQL Server 2005 (not Express).
Salamo alikom,
when i write this sentence in textbox in reporting service 2005 :
= iif(DateAdd( "h",Parameters! t.Value,Fields! completionTime. Value),"" ,Fields!completi onTime.Value)
Because this column contain null value.
So how to ignore null value with dateadd
AnyBody Can Help me.
I exporting a table of comments. There are some line returns in the comments. Some of these data are paragraphs of data! For some reason, when I am exporting the data, it treats the line return within the comment column as a new record. I am using a -c character data type so (newline character) is the row terminator. How do I get the BCP OUT to ignore a newline character within a record?
For example:
ID~Comment
-- -------
1~This is a comment
2~Hi,how are (user hit carriage)
you (you is part of next row in bcp out)
3~Next record
Thanks!
Joyce
What is the best method for ignoring the time in datetime comparisons. Say I want all records on 07/08/1996 regardless of their time. Or all records between 01/01/1999 and 04/01/1999 even if one of the records on 04/01/1999 had a time of 16:32:22
View 5 Replies View RelatedHello.
There is a trace flag that tells SQL Server to ignore index hinting in incoming queries. I'm having a Monday morning problem and I can't remember the trace number nor find it in my notes. Can anyone else come up with it?
Thanks in advance,
-darin
My intention is to make a copy of a database with some data excluded. One way to do this:
- Make a copy-only backup of the source database
- Restore the backup as a new database
- Delete undesired data from the new database
In my case, the source database has two file groups, primary and secondary. The secondary has just one table with huge amount of data and this file group appears as one physical file. Is there any way to make a partial backup, which does not include this file group or table, and it would still be possible to restore the backup to a new database? My point is to avoid copying huge amount of data if it won't be needed.
create table deltavalues (planid varchar(10), baseval float, highval float)
Insert into deltavalues values ('1002', 2000,5000)
Insert into deltavalues values ('1003', 1000,0)
Insert into deltavalues values ('1004', 500,0)
Insert into deltavalues values ('1005', 1000,2000)
--I have below query , basically getting emnployee table balance between deltavalues table baseval column and highval column
select * from employee e join deltavalues d on e.planid = d.planid
where e.balance between d.baseval and d.highval
but here what i am requesting you is how can i change the highvalue to not include if it is 0
for example above example plan 1003, 1004 has highbal as 0 so i want to write a query to get employee data between 1000 and anyvalue (> 1000 - no limit)
same way for 1004 between 500 and no end values (infinity)
Ive started using try/catch in my t-sql code now and I rather like it, since im a C# developer. I read that some errors with an error code below 10 will not cause the catch block to be entered. What kind of errors does this include ?
View 2 Replies View RelatedIs there a way to ignore a column/variable when the whole result set for the applied filter (where) is null?
Not all clients have data for every variable, i.e. some variables are client specific. There are too many variables and clients to amend the select query every time so I just want to ignore a col if its null.
I hope that makes sense (my inability to describe it might explain my inability to find anything related to it!)
The next step would be to run all clients' data in one go using, I believe, a cursor, but one step at a time!