**HAVE NULL AND NOT DUBLICATED VALUES IN COL**

In my opinon the best way of doing this would be through
an 'INSTEAD OF INSERT' trigger.
If you give a bit more on the requirements I can help with
the code, for instance can you have two of...
105,'Tom',null
105,'Tom',null
In the table ?
Peter
"Choose a job you love, and you will never have to work a
day in your life."
Confucius

>--Original Message--
>Hi
>I'm using SQL 2000,how is it possible to control a value
of column to get
>the ollowing result?
>Emp (EmpId PK,EmpName,EmpCode)
>EmpId EmpName EmpCode
>-- -- --
>100 John 3
>101 Anne 5
>102 Robert null
>103 Jennifer null
>104 Peter 78
>I want to have some NULLs or a unique number in EmpCode
column as above,
>I tried to define Uniqe index or constraint but it wasn't
successfull
>because of null,
>now how can I prevent of inserting a record of
(105,'Tom',3) and not prevent
>of inserting a record like (105,'Tom',null)
>Any help would be greatly thankful.
>
>.
>No, we can't have
> 105,'Tom',null
> 105,'Tom',null
cause the first column is a PK,
"Peter The Spate" <anonymous@.discussions.microsoft.com> wrote in message
news:204701c5141e$26f85330$a501280a@.phx.gbl...
> In my opinon the best way of doing this would be through
> an 'INSTEAD OF INSERT' trigger.
> If you give a bit more on the requirements I can help with
> the code, for instance can you have two of...
> 105,'Tom',null
> 105,'Tom',null
> In the table ?
> Peter
> "Choose a job you love, and you will never have to work a
> day in your life."
> Confucius
>
> of column to get
> column as above,
> successfull
> (105,'Tom',3) and not prevent

*** DTS Error Messages ***

Hello,
I'd like to know if I use DTS. If I use it immediately, I can see the error
in the dialog box, instead, if I use it with scheduling, where Can I check
the error ?

Thanks

Saimon(Florence)There are actually a few places you can look at errors. There are the
error logs you would have specified in your package under Options.
Another place is when you right click on the Scheduled Job and View
Job History and then check the box that says Show Step Details. Then
click on the details. I know there are a few other places, but these
should get you in the right direction.

"Saimon" <saimon181072@.supereva.it> wrote in message news:<c5bbkr$3rf$1@.newsreader.mailgate.org>...
> Hello,
> I'd like to know if I use DTS. If I use it immediately, I can see the error
> in the dialog box, instead, if I use it with scheduling, where Can I check
> the error ?
> Thanks
> Saimon(Florence)|||There are actually a few places you can look at errors. There are the
error logs you would have specified in your package under Options.
Another place is when you right click on the Scheduled Job and View
Job History and then check the box that says Show Step Details. Then
click on the details. I know there are a few other places, but these
should get you in the right direction.

"Saimon" <saimon181072@.supereva.it> wrote in message news:<c5bbkr$3rf$1@.newsreader.mailgate.org>...
> Hello,
> I'd like to know if I use DTS. If I use it immediately, I can see the error
> in the dialog box, instead, if I use it with scheduling, where Can I check
> the error ?
> Thanks
> Saimon(Florence)

(Urgent)How can I specify the excel File''s column to import data as varchar instead of Float

Hi,

I have a excel file which i want to import the data to sql server... The sql server Data type for that particular column is

varchar and it has a contraint too like the data should be in this fashion 00000-0000 or 00000...

but when i try to import the data from the excel to sql server... 08545 just becomes 8545 (cause excel is treating it as a float) and so my insert fails...

what can i do to rectify the problem...

regards

Karen

Hi Karenros,

You need to put a data conversion task and make the datatype as varchar instead of float.

You need to place this task inbetween the excel task and destination sql server.

Thanks,

SVGP.

|||

SVGP,

thanks for your answer... can u please elaborate as to how can i create the data conversion task...

Regards

Karen

|||

In the dataflow tab ,on the left side you will find dataflow source,dataflow transformation and dataflow destinations.

Drag and drop the Data Conversion task from the dataflow transformation, inbetween your excel and oledb Destination.

edit the data conversion task,you need to select the column you want to convert and it will appear down automatically.

then you need to change the datatype(the drop down will lead you for that).

Remember to map the copy of the column you selected in the oledb destination.

Thanks,

SVGP

|||is the dataflow tab in Excel?|||

No Karen,iam mentioning about the Data flow in Business Intelligence development studio(BIDS)

You have Three tabs,control,dataflow and event handlers,from that u select dataflow and do as i said before.

Good Luck.

SVGP

|||but i am importing the data from an excel file to the database directly|||

What tool you are using to migrate the data?

|||going to sql server and importing the data directly

(Urgent)How can I specify the excel File''s column to import data as varchar instead of Float

Hi,

I have a excel file which i want to import the data to sql server... The sql server Data type for that particular column is

varchar and it has a contraint too like the data should be in this fashion 00000-0000 or 00000...

but when i try to import the data from the excel to sql server... 08545 just becomes 8545 (cause excel is treating it as a float) and so my insert fails...

what can i do to rectify the problem...

regards

Karen

Hi Karenros,

You need to put a data conversion task and make the datatype as varchar instead of float.

You need to place this task inbetween the excel task and destination sql server.

Thanks,

SVGP.

|||

SVGP,

thanks for your answer... can u please elaborate as to how can i create the data conversion task...

Regards

Karen

|||

In the dataflow tab ,on the left side you will find dataflow source,dataflow transformation and dataflow destinations.

Drag and drop the Data Conversion task from the dataflow transformation, inbetween your excel and oledb Destination.

edit the data conversion task,you need to select the column you want to convert and it will appear down automatically.

then you need to change the datatype(the drop down will lead you for that).

Remember to map the copy of the column you selected in the oledb destination.

Thanks,

SVGP

|||is the dataflow tab in Excel?|||

No Karen,iam mentioning about the Data flow in Business Intelligence development studio(BIDS)

You have Three tabs,control,dataflow and event handlers,from that u select dataflow and do as i said before.

Good Luck.

SVGP

|||but i am importing the data from an excel file to the database directly|||

What tool you are using to migrate the data?

|||going to sql server and importing the data directly

(Urgent)How can I specify the excel File''s column to import data as varchar instead of Float

Hi,

I have a excel file which i want to import the data to sql server... The sql server Data type for that particular column is

varchar and it has a contraint too like the data should be in this fashion 00000-0000 or 00000...

but when i try to import the data from the excel to sql server... 08545 just becomes 8545 (cause excel is treating it as a float) and so my insert fails...

what can i do to rectify the problem...

regards

Karen

Hi Karenros,

You need to put a data conversion task and make the datatype as varchar instead of float.

You need to place this task inbetween the excel task and destination sql server.

Thanks,

SVGP.

|||

SVGP,

thanks for your answer... can u please elaborate as to how can i create the data conversion task...

Regards

Karen

|||

In the dataflow tab ,on the left side you will find dataflow source,dataflow transformation and dataflow destinations.

Drag and drop the Data Conversion task from the dataflow transformation, inbetween your excel and oledb Destination.

edit the data conversion task,you need to select the column you want to convert and it will appear down automatically.

then you need to change the datatype(the drop down will lead you for that).

Remember to map the copy of the column you selected in the oledb destination.

Thanks,

SVGP

|||is the dataflow tab in Excel?|||

No Karen,iam mentioning about the Data flow in Business Intelligence development studio(BIDS)

You have Three tabs,control,dataflow and event handlers,from that u select dataflow and do as i said before.

Good Luck.

SVGP

|||but i am importing the data from an excel file to the database directly|||

What tool you are using to migrate the data?

|||going to sql server and importing the data directly

(Urgent)How can I specify the excel File''s column to import data as varchar instead of Float

Hi,

I have a excel file which i want to import the data to sql server... The sql server Data type for that particular column is

varchar and it has a contraint too like the data should be in this fashion 00000-0000 or 00000...

but when i try to import the data from the excel to sql server... 08545 just becomes 8545 (cause excel is treating it as a float) and so my insert fails...

what can i do to rectify the problem...

regards

Karen

Hi Karenros,

You need to put a data conversion task and make the datatype as varchar instead of float.

You need to place this task inbetween the excel task and destination sql server.

Thanks,

SVGP.

|||

SVGP,

thanks for your answer... can u please elaborate as to how can i create the data conversion task...

Regards

Karen

|||

In the dataflow tab ,on the left side you will find dataflow source,dataflow transformation and dataflow destinations.

Drag and drop the Data Conversion task from the dataflow transformation, inbetween your excel and oledb Destination.

edit the data conversion task,you need to select the column you want to convert and it will appear down automatically.

then you need to change the datatype(the drop down will lead you for that).

Remember to map the copy of the column you selected in the oledb destination.

Thanks,

SVGP

|||is the dataflow tab in Excel?|||

No Karen,iam mentioning about the Data flow in Business Intelligence development studio(BIDS)

You have Three tabs,control,dataflow and event handlers,from that u select dataflow and do as i said before.

Good Luck.

SVGP

|||but i am importing the data from an excel file to the database directly|||

What tool you are using to migrate the data?

|||going to sql server and importing the data directly

(Urgent)How can I specify the excel File''s column to import data as varchar instead of Float

Hi,

I have a excel file which i want to import the data to sql server... The sql server Data type for that particular column is

varchar and it has a contraint too like the data should be in this fashion 00000-0000 or 00000...

but when i try to import the data from the excel to sql server... 08545 just becomes 8545 (cause excel is treating it as a float) and so my insert fails...

what can i do to rectify the problem...

regards

Karen

Hi Karenros,

You need to put a data conversion task and make the datatype as varchar instead of float.

You need to place this task inbetween the excel task and destination sql server.

Thanks,

SVGP.

|||

SVGP,

thanks for your answer... can u please elaborate as to how can i create the data conversion task...

Regards

Karen

|||

In the dataflow tab ,on the left side you will find dataflow source,dataflow transformation and dataflow destinations.

Drag and drop the Data Conversion task from the dataflow transformation, inbetween your excel and oledb Destination.

edit the data conversion task,you need to select the column you want to convert and it will appear down automatically.

then you need to change the datatype(the drop down will lead you for that).

Remember to map the copy of the column you selected in the oledb destination.

Thanks,

SVGP

|||is the dataflow tab in Excel?|||

No Karen,iam mentioning about the Data flow in Business Intelligence development studio(BIDS)

You have Three tabs,control,dataflow and event handlers,from that u select dataflow and do as i said before.

Good Luck.

SVGP

|||but i am importing the data from an excel file to the database directly|||

What tool you are using to migrate the data?

|||going to sql server and importing the data directly

(Urgent)How can I specify the excel File''s column to import data as varchar instead of Float

Hi,

I have a excel file which i want to import the data to sql server... The sql server Data type for that particular column is

varchar and it has a contraint too like the data should be in this fashion 00000-0000 or 00000...

but when i try to import the data from the excel to sql server... 08545 just becomes 8545 (cause excel is treating it as a float) and so my insert fails...

what can i do to rectify the problem...

regards

Karen

Hi Karenros,

You need to put a data conversion task and make the datatype as varchar instead of float.

You need to place this task inbetween the excel task and destination sql server.

Thanks,

SVGP.

|||

SVGP,

thanks for your answer... can u please elaborate as to how can i create the data conversion task...

Regards

Karen

|||

In the dataflow tab ,on the left side you will find dataflow source,dataflow transformation and dataflow destinations.

Drag and drop the Data Conversion task from the dataflow transformation, inbetween your excel and oledb Destination.

edit the data conversion task,you need to select the column you want to convert and it will appear down automatically.

then you need to change the datatype(the drop down will lead you for that).

Remember to map the copy of the column you selected in the oledb destination.

Thanks,

SVGP

|||is the dataflow tab in Excel?|||

No Karen,iam mentioning about the Data flow in Business Intelligence development studio(BIDS)

You have Three tabs,control,dataflow and event handlers,from that u select dataflow and do as i said before.

Good Luck.

SVGP

|||but i am importing the data from an excel file to the database directly|||

What tool you are using to migrate the data?

|||going to sql server and importing the data directly

(Urgent)How can I specify the excel File''s column to import data as varchar instead of Float

Hi,

I have a excel file which i want to import the data to sql server... The sql server Data type for that particular column is

varchar and it has a contraint too like the data should be in this fashion 00000-0000 or 00000...

but when i try to import the data from the excel to sql server... 08545 just becomes 8545 (cause excel is treating it as a float) and so my insert fails...

what can i do to rectify the problem...

regards

Karen

Hi Karenros,

You need to put a data conversion task and make the datatype as varchar instead of float.

You need to place this task inbetween the excel task and destination sql server.

Thanks,

SVGP.

|||

SVGP,

thanks for your answer... can u please elaborate as to how can i create the data conversion task...

Regards

Karen

|||

In the dataflow tab ,on the left side you will find dataflow source,dataflow transformation and dataflow destinations.

Drag and drop the Data Conversion task from the dataflow transformation, inbetween your excel and oledb Destination.

edit the data conversion task,you need to select the column you want to convert and it will appear down automatically.

then you need to change the datatype(the drop down will lead you for that).

Remember to map the copy of the column you selected in the oledb destination.

Thanks,

SVGP

|||is the dataflow tab in Excel?|||

No Karen,iam mentioning about the Data flow in Business Intelligence development studio(BIDS)

You have Three tabs,control,dataflow and event handlers,from that u select dataflow and do as i said before.

Good Luck.

SVGP

|||but i am importing the data from an excel file to the database directly|||

What tool you are using to migrate the data?

|||going to sql server and importing the data directly

(Urgent)How can I specify the excel File''s column to import data as varchar instead of Float

Hi,

I have a excel file which i want to import the data to sql server... The sql server Data type for that particular column is

varchar and it has a contraint too like the data should be in this fashion 00000-0000 or 00000...

but when i try to import the data from the excel to sql server... 08545 just becomes 8545 (cause excel is treating it as a float) and so my insert fails...

what can i do to rectify the problem...

regards

Karen

Hi Karenros,

You need to put a data conversion task and make the datatype as varchar instead of float.

You need to place this task inbetween the excel task and destination sql server.

Thanks,

SVGP.

|||

SVGP,

thanks for your answer... can u please elaborate as to how can i create the data conversion task...

Regards

Karen

|||

In the dataflow tab ,on the left side you will find dataflow source,dataflow transformation and dataflow destinations.

Drag and drop the Data Conversion task from the dataflow transformation, inbetween your excel and oledb Destination.

edit the data conversion task,you need to select the column you want to convert and it will appear down automatically.

then you need to change the datatype(the drop down will lead you for that).

Remember to map the copy of the column you selected in the oledb destination.

Thanks,

SVGP

|||is the dataflow tab in Excel?|||

No Karen,iam mentioning about the Data flow in Business Intelligence development studio(BIDS)

You have Three tabs,control,dataflow and event handlers,from that u select dataflow and do as i said before.

Good Luck.

SVGP

|||but i am importing the data from an excel file to the database directly|||

What tool you are using to migrate the data?

|||going to sql server and importing the data directly

(Urgent)How can I specify the excel File''s column to import data as varchar instead of Float

Hi,

I have a excel file which i want to import the data to sql server... The sql server Data type for that particular column is

varchar and it has a contraint too like the data should be in this fashion 00000-0000 or 00000...

but when i try to import the data from the excel to sql server... 08545 just becomes 8545 (cause excel is treating it as a float) and so my insert fails...

what can i do to rectify the problem...

regards

Karen

Hi Karenros,

You need to put a data conversion task and make the datatype as varchar instead of float.

You need to place this task inbetween the excel task and destination sql server.

Thanks,

SVGP.

|||

SVGP,

thanks for your answer... can u please elaborate as to how can i create the data conversion task...

Regards

Karen

|||

In the dataflow tab ,on the left side you will find dataflow source,dataflow transformation and dataflow destinations.

Drag and drop the Data Conversion task from the dataflow transformation, inbetween your excel and oledb Destination.

edit the data conversion task,you need to select the column you want to convert and it will appear down automatically.

then you need to change the datatype(the drop down will lead you for that).

Remember to map the copy of the column you selected in the oledb destination.

Thanks,

SVGP

|||is the dataflow tab in Excel?|||

No Karen,iam mentioning about the Data flow in Business Intelligence development studio(BIDS)

You have Three tabs,control,dataflow and event handlers,from that u select dataflow and do as i said before.

Good Luck.

SVGP

|||but i am importing the data from an excel file to the database directly|||

What tool you are using to migrate the data?

|||going to sql server and importing the data directly

(SQL Server 2005) Instead of Delete Trigger

Hi,

I simply need a trigger to delete some datasets from a view but have some trouble writing an adequate trigger. Here is my attempt:

Use myDB;
Go
CREATE TRIGGER IO_Trig_Del_myView ON myView
INSTEAD OF Delete
AS
BEGIN
SET NOCOUNT ON
-- Check for dataset, if present delete.
IF (EXISTS (SELECT Z.[myPk]
FROM myTable t, deleted
WHERE t.[myPk] = deleted.[myPk]))
Delete From myTable
Where myTable.[myPk] = deleted.[myPk]...

This causes the following failure:

Msg 4104, Level 16, State 1, Procedure IO_Trig_Del_myView, Line 11
The multi-part identifier "deleted.myPK" could not be bound.

Can somebody explain the reason to me? myPk is part of the View I created. Since I do have three tables in myView so I get that message three times, once per table.

Hi...

The deleted object is a Table and you have to treat it as such. That means it can contain more then one row of data so you have to take this into account in your querry...

You could try the following querry

Delete From myTable
Where myTable.[myPk] in ( select [myPk] from deleted) ...

|||

Thanks.

Although I don't understand it. Isn't it possible to select more than one row via the = operator? I mean if I take a join for example I compare via = and can get more than one row and also compare more rows.

|||

The = can only be used to compare one Value against another one. To compare one Value against a set of other values you need the "in" clause. The syntax of a join is a little different here. In fact it could also be rewritten as a join to achieve the same result by my preference to doing delete statements is to first write the querry that will show me what to delete and then encapsulate that querry inside the In clause (Thats just a personal preference)

|||

Check out this delete trigger. This will do what you want, but you have to include the deleted table in the trigger (I am using a correlated subquery, but a join would work to)

create TABLE test
(
myPk int PRIMARY key
)
go
CREATE VIEW myView
AS
SELECT myPk
FROM test
go

CREATE TRIGGER IO_Trig_Del_myView ON myView
INSTEAD OF Delete
AS
BEGIN
SET NOCOUNT ON

-- Check for dataset, if present delete. (do this in one step
DELETE FROM myTable
WHERE EXISTS (SELECT *
FROM deleted
WHERE myTable.myPk = deleted.myPk)
END
go

--Code to test with...

INSERT INTO test
VALUES (1)
INSERT INTO test
VALUES (2)
INSERT INTO test
VALUES (3)
INSERT INTO test
VALUES (4)
INSERT INTO test
VALUES (5)

SELECT * FROM test
DELETE FROM myView WHERE myPk = 1
SELECT * FROM test
DELETE FROM myView WHERE myPk IN (2,4)
SELECT * FROM test