Determin the order of triggers,

Hi all,
Can one determine the order of triggers, or set the order of triggers. I
have a two insert triggers, generally I would like one trigger to fire first
all the tme. Is this possible.
RobertYou can specify which trigger fires first (or last) using
sp_settriggerorder. See the Books Online for details.
Hope this helps.
Dan Guzman
SQL Server MVP
"Robert Bravery" <me@.u.com> wrote in message
news:%23nKBoCWMGHA.3408@.TK2MSFTNGP12.phx.gbl...
> Hi all,
> Can one determine the order of triggers, or set the order of triggers. I
> have a two insert triggers, generally I would like one trigger to fire
> first
> all the tme. Is this possible.
> Robert
>|||You can set the first and the last trigger, i you have less than three
that shouldn=B4t be the problem:
sp_settriggerorder=20
HTH, Jens Suessmeyer.|||Hi
u can decide the order of triggers by the help of a Stored Procedure named
'sp_settriggerorder'.The syntax is as follows:
exec sp_settriggerorder <triggername>,<order>,'<operation>'
For Example:
exec sp_settriggerorder trig_Customer,first,'Insert'.
For more Info,log on to
http://www.sqlservercentral.com/col...erswhatsnew.asp
--
Regards
Ashish Narang
AIM: ashishnarang02
YIM: ashish_jsn@.yahoo.com
Mobile: +919820625179
"Jens" wrote:

> You can set the first and the last trigger, i you have less than three
> that shouldn′t be the problem:
> sp_settriggerorder
> HTH, Jens Suessmeyer.
>|||Thanks to all, for helping and poinmting in the right direction
Robert
"Robert Bravery" <me@.u.com> wrote in message
news:%23nKBoCWMGHA.3408@.TK2MSFTNGP12.phx.gbl...
> Hi all,
> Can one determine the order of triggers, or set the order of triggers. I
> have a two insert triggers, generally I would like one trigger to fire
first
> all the tme. Is this possible.
> Robert
>

Detecting the INSERT format for DATE database fields?

Assume I got a database resp. table defintion with a DATE field. How do I fi
nd out in which format the
field should be filled in an INSERT SQL statement?
Sometimes when I enter e.g.
INSERT INTO ... VALUES (...., '2005-01-25',....);
or
INSERT INTO ... VALUES (...., '2005-01-25-13.14.15.00',......);
I got an error like:
ORA-01861: literal does not match format string
before I realized that
INSERT INTO ... VALUES (...., '25-01-2005',...);
is the correct format.
TomThomas Jerkins (tomjerk@.hotmail.com) wrote:
: Assume I got a database resp. table defintion with a DATE field. How do I
find out in which format the
: field should be filled in an INSERT SQL statement?
: Sometimes when I enter e.g.
: INSERT INTO ... VALUES (...., '2005-01-25',....);
: or
: INSERT INTO ... VALUES (...., '2005-01-25-13.14.15.00',......);
: I got an error like:
: ORA-01861: literal does not match format string
: before I realized that
: INSERT INTO ... VALUES (...., '25-01-2005',...);
: is the correct format.
You asked in an oracle group, so I'll give the oracle answer.
INSERT INTO ...
VALUES (...., to_date('25-01-2005','DD-MM-YYYY') ,...);
look up to_date for all the options.
This space not for rent.|||Thomas Jerkins wrote:
> Assume I got a database resp. table defintion with a DATE field. How
> do I find out in which format the field should be filled in an INSERT
> SQL statement?
> Sometimes when I enter e.g.
> INSERT INTO ... VALUES (...., '2005-01-25',....);
> or
> INSERT INTO ... VALUES (...., '2005-01-25-13.14.15.00',......);
> I got an error like:
> ORA-01861: literal does not match format string
> before I realized that
> INSERT INTO ... VALUES (...., '25-01-2005',...);
> is the correct format.
> Tom
The only guaranteed formats inserting a date are the following:
select convert(datetime, 'YYYY-MM-DDThh:mm:ss.mmm')
select convert(datetime, 'YYYYMMDD hh:mm:ss.mmm')
All others are subject the locale and risk throwing exceptions or
inserting the incorrect date.
David Gugick
Imceda Software
www.imceda.com|||"Thomas Jerkins" <tomjerk@.hotmail.com> a crit dans le message de
news:cst3bg$osr$05$1@.news.t-online.com...
| Assume I got a database resp. table defintion with a DATE field. How do I
find out in which format
the
| field should be filled in an INSERT SQL statement?
|
| Sometimes when I enter e.g.
|
| INSERT INTO ... VALUES (...., '2005-01-25',....);
| or
| INSERT INTO ... VALUES (...., '2005-01-25-13.14.15.00',......);
|
| I got an error like:
|
| ORA-01861: literal does not match format string
|
| before I realized that
|
| INSERT INTO ... VALUES (...., '25-01-2005',...);
|
| is the correct format.
|
| Tom
|
Never rely on implicit conversion.
Use to_date to explicitly convert your external format to date datatype:
insert into ... values (..., to_date('2005-01-25','YYYY-MM-DD'),...);
or
insert into ... values (..., to_date('2005-01-25-13.14.15.00','YYYY-MM-DD-HH
24.MI.SS.FF2'),...);
or whatever is your external format.
This is the correct way.
Regards
Michel Cadot|||On Sat, 22 Jan 2005 09:37:04 +0100, tomjerk@.hotmail.com (Thomas
Jerkins) wrote:

>Assume I got a database resp. table defintion with a DATE field. How do I f
ind out in which format the
>field should be filled in an INSERT SQL statement?
>Sometimes when I enter e.g.
>INSERT INTO ... VALUES (...., '2005-01-25',....);
>or
>INSERT INTO ... VALUES (...., '2005-01-25-13.14.15.00',......);
>I got an error like:
>ORA-01861: literal does not match format string
>before I realized that
>INSERT INTO ... VALUES (...., '25-01-2005',...);
>is the correct format.
>Tom
You should never rely on the default date format, and always apply the
to_date function to any date literal submitted.
The default date format, btw, is in the docs (no one ever reads them)
and can be queried from nls_session_parameters.
But you should stop developing bad habits and not rely on it. It can
be changed on client level very easily, in which case your application
won't work anymore.
Sybrand Bakker, Senior Oracle DBA|||"Thomas Jerkins" <tomjerk@.hotmail.com> wrote in message
news:cst3bg$osr$05$1@.news.t-online.com...
> Assume I got a database resp. table defintion with a DATE field. How do I
find out in which format the
> field should be filled in an INSERT SQL statement?
> Sometimes when I enter e.g.
> INSERT INTO ... VALUES (...., '2005-01-25',....);
> or
> INSERT INTO ... VALUES (...., '2005-01-25-13.14.15.00',......);
> I got an error like:
> ORA-01861: literal does not match format string
> before I realized that
> INSERT INTO ... VALUES (...., '25-01-2005',...);
> is the correct format.
> Tom
>
Please don't cross post Oracle problems in other newsgroups. But if you were
using DB2, it would take the date in either order.|||"Thomas Jerkins" <tomjerk@.hotmail.com> wrote in message
news:cst3bg$osr$05$1@.news.t-online.com...
> Assume I got a database resp. table defintion with a DATE field. How do I
> find out in which format the
> field should be filled in an INSERT SQL statement?
> Sometimes when I enter e.g.
> INSERT INTO ... VALUES (...., '2005-01-25',....);
> or
> INSERT INTO ... VALUES (...., '2005-01-25-13.14.15.00',......);
> I got an error like:
> ORA-01861: literal does not match format string
> before I realized that
> INSERT INTO ... VALUES (...., '25-01-2005',...);
> is the correct format.
> Tom
>
you can check the setting of NLS_DATE_FORMAT in the V$NLS_PARAMETERS view
however, you should not rely on the default format because it can be changed
by the dba or by other code that you run
1) if using 10g, use the DATE keyword to specify an ansi date literal, ie
DATE '2005-02-22' (no time element)
2) for full oracle portability and reliability either use TO_CHAR with a
specific date format, ie, TO_CHAR('022205 15:22', 'RRMMDD HH24:MI')
3) or, if you could explicitly set the format for your session with ALTER
SESSION SET NLS_DATE_FORMAT = 'the format of your choice'
check the SQL manual at tahiti.oracle.com for addtional date formats
and _please_ ignore any examples that you ever see, including in Oracle docs
and courseware and OCP stuff, that assume a specific date format without
using one of these three techniques|||What is Oracle?|||On 22 Jan 2005 10:03:57 -0800, Max wrote:

>What is Oracle?
http://en.wikipedia.org/wiki/Oracle
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||On Sat, 22 Jan 2005 09:37:04 +0100, Thomas Jerkins wrote:

>Assume I got a database resp. table defintion with a DATE field. How do I f
ind out in which format the
>field should be filled in an INSERT SQL statement?
Hi Tom,
Most of the answers you got relate to Oracle. The error code indicates
that that's what you want. But since you posted in a SQL Server newsgroup,
the correct answer for SQL Server is:
a) Use one of the unambiguous formats:
- yyyymmdd for date only
- yyyy-mm-ddThh:mm:ss for date plus time
- yyyy-mm-ddThh:mm:ss.ttt for date plus time including milliseconds
b) Use explicit conversion using a style parameter (look up CONVERT and
CAST in Books Online for a list of all supported styles)
c) Use any format that matches your regional settings and pray that nobody
changes the regional setting and that you enver have to export your code
to other countries.
Actually, I recommend using only one of the first two options. Option a
takes the least number of keystrokes.

>before I realized that
>INSERT INTO ... VALUES (...., '25-01-2005',...);
>is the correct format.
Not for SQL Server, it isn't. Even though it won't fail for all settings,
it will fail for some.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Desperatly need help to import .csv file using Bulk Insert

Hi all-

I am in need of some help importing a .CSV file into a SQL Server 2005 Enterprise Edition.

The problem is I already implemented Bulk Insert task in SSIS but it is not importing any data. My detailed layout is as follows :
In SSIS package1 -
In Control Flow Bulk Insert Task has been inserted
Properties of Bulk Insert Task:
Connection adtc009d.ganny
Destination Table ganny.dbo.t4
Format
Format Specify
Row Delimiter {CR}
Column Delimiter Comma{,}
Source Connection
File r.csv
Options
Options Check Constraints
Maxerrors 20

This bulk insert task is connected to Data flow task, if we click edit to data flow task, data flow section will come, here Flat file source & OLE DB Destination is there. Flat file source is connected to OLE DB Destination.

Properties of Flat File
Connection Manager
Flat file connection Manager
here by clicking new link flat file properties to this.
Preview
by clicking preview all data are visible
Properties of OLE DB Destination editor
Oledb connection manager adtc009d.ganny
Data access mode: Table or View - fast load
Name of Table or view dbo.t4

After designing all this then if I start debugging I could able to get records are imported to a table.
Please suggest me where I am going wrong.

Thanks in advance
Karna

Karna,

There are several ways to find out what's wrong, the easiest is

looking at the errors reported by SSIS when you run the package.

The other way is to look at the file source and preview the rows to see if your Column delimiters and row delimiters are working correctly.

Try these and let me know

|||On the flat connection manager, you might need to put a text qualifier (like "). Also you might want to try {CR}{LF} on the row delimeter.|||Dear all,

Thanks for reply.

I solved this problem by using only Bulk Insert option alone, without using data flow task.

My biggest problem is whether we have any tool to check validations before inserting records to a table. It has to check duplicates, if duplicates are existing just insert only only real records not the duplicate ones.

I tried removing redundancy by inserting all records from csv data file to a table called t1, create another table which is copy of table t1 but has primary key called table as t2. Use insert into t2 by selecting only distinct records from t1.

My question apart from above option whether we have any tool which does all related job in SSIS.

Thanks in advance
Karna|||

The short answer is No.

The best option is to import into a 'staging' table. then you can have as many 'clean-up' and data modification steps as is necessary. For example, you may wish to create a output (in some form) of the rows that fail the concurrency test.