InSQL 7.1 - count how many times a tag equals 1 over a specified time period query....

Need help to create a query to count how many times over a specified time the tag(s) equal the value of 1.

Thanks

Gary

Would you please provide some sample data and an example of expected results?

|||SELECT at.TagName,
'month'=month(datetime),
'YEAR'=YEAR(DATETIME),
TAGVALUE =1,
FREQ = COUNT(*)
FROM AnalogTag at, v_AnalogHistory ah
WHERE DateTime >= "2007/07/01 00:00"
AND DateTime <= "2007/07/31 00:00"
AND ah.TagName in ('PV249_STEP_NO')
AND ah.Value = 1
AND ah.TagName = at.TagName
AND wwRetrievalMode = 'DELTA'
GROUP BY at.TagName, month(DATETIME), YEAR(DATETIME)

I think I did it, using the above. I need to test a little more.

the value of the tag increments from 1 to 64, my purpose is to count the cycles of the tag.

Thanks

Gary|||

It looks like you have it correct for TagValue = 1; if you are looking to get it for all 64 different values of the tag, it would be something like:

Code Snippet

SELECT at.TagName,
'month'=month(datetime),
'YEAR'=YEAR(DATETIME),
-- TAGVALUE =1,
ah.value as TAGVALUE,
FREQ = COUNT(*)
FROM AnalogTag at, v_AnalogHistory ah
WHERE DateTime >= "2007/07/01 00:00"
AND DateTime <= "2007/07/31 00:00"
AND ah.TagName in ('PV249_STEP_NO')
-- AND ah.Value = 1
AND ah.TagName = at.TagName
AND wwRetrievalMode = 'DELTA'
GROUP BY at.TagName, ah.value, month(DATETIME), YEAR(DATETIME)

Inserting XMl string into table.

Hi,

I want the value of a field as an XML string.

Ex: <student1 name ="df" age="16"/>

<student2 name ="gfdg" age="21"/>

<student3 name ="ddddf" age="11"/>

Please let me know whether the normal insert command can be used to insert this XML string

I have done with normal insert Command like

Insert INTO listtable list_id, listDetails VALUES 1, '<student1 name ="df" age="16"/>

<student2 name ="gfdg" age="21"/>

<student3 name ="ddddf" age="11"/>'

Here, the XML string is inserted into 2nd column.

Is this the way to do?

Because, when i read the string back, i am getting the characters &lt; etc. in place of "<", ">" etc.

How the XML string column is selected back to get the correct XML string without junk characters?

Please help me out.

I am not sure what goes wrong but you have not shown how you read out the data exactly.

The following is a sample that creates the table and inserts your sample data and then reads it out with a simple SELECT FROM, that works fine for me with SQL Server 2005 Management Studio Express, the angle brackets <> are certainly not escaped:

Code Snippet

CREATE TABLE #listtable (

list_id int,

listDetails xml

);

GO

INSERT INTO #listtable (list_id, listDetails)

VALUES(1, '<student1 name ="df" age="16"/>

<student2 name ="gfdg" age="21"/>

<student3 name ="ddddf" age="11"/>');

SELECT list_id, listDetails FROM #listtable;

Inserting without using INSERT INTO

Hi,
How do i insert a value into varbinary column using ASP
Here i dont want to use "INSERT INTO"
Tx.
DNKHi
if your concern is to hide sql then alternative is to use StoredProcedure
with encryption.
Regards
R.D
"DNKMCA" wrote:

> Hi,
> How do i insert a value into varbinary column using ASP
> Here i dont want to use "INSERT INTO"
> Tx.
> DNK
>
>

Inserting without using INSERT INTO

Hi,
How do i insert a value into varbinary column using ASP
Here i dont want to use "INSERT INTO"
Tx.
DNKSorry, "INSERT INTO" is the answer if you want to insert. Maybe you
could you explain why you don't want to if that's not the answer you
are looking for.
Note however that you insert into *tables* not a column. Perhaps you
just mean you want to update a column in which case use UPDATE.
David Portas
SQL Server MVP
--|||I want to know is there a way i can put a value into VARBINARY Column thru
ASP Code.
-DNK
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1126860791.506472.201910@.f14g2000cwb.googlegroups.com...
> Sorry, "INSERT INTO" is the answer if you want to insert. Maybe you
> could you explain why you don't want to if that's not the answer you
> are looking for.
> Note however that you insert into *tables* not a column. Perhaps you
> just mean you want to update a column in which case use UPDATE.
> --
> David Portas
> SQL Server MVP
> --
>|||All updates should normally be done through stored procedures so the
answer is to call a proc using the ADO command object. The proc can
then perform the INSERT.
David Portas
SQL Server MVP
--|||DNKMCA (dnk@.msn.com) writes:
> I want to know is there a way i can put a value into VARBINARY Column thru
> ASP Code.
> -DNK
If you want a sample about ASP, you may want to ask in an ASP forum.
From the SQL side of things, varbinary is not that much different than
any other data value.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Inserting values into multiple tables

Hi. I have a primary key field in one table that is a foreign key field in another table. How do I write an INSERT statement that puts the value in both tables? Can this even be done?
On Thu, 20 May 2004 20:56:02 -0700, twright wrote:

>Hi. I have a primary key field in one table that is a foreign key field in another table. How do I write an INSERT statement that puts the value in both tables? Can this even be done?
Hi twright,
Not in one statement. You have to use two INSERT statements. If you want
to be sure that either both or none are executed, whatevert happens,
enclose them in a transaction:
BEGIN TRANSACTION
INSERT FirstTable (KeyColumn)
VALUES (17)
INSERT SecondTable (OtherKeyColumn, ForeignKeyColumn)
VALUES (231, 17)
COMMIT TRANSACTION
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Inserting values into a column by selecting value from different table

Hi, I have a question regarding how to insert one column values into a table by selecting from different table..Here is the syntax..
----
insert into propertytable values (select lastvalue+incrementby from agilesequences where name='SEQPROPERTYTABLE', 13926, 0, 4, 1, 451, 1, 8, 1)

the first column in the propertytable will be... select lastvalue+incrementby from agilesequences where name='SEQPROPERTYTABLE'
How do I do that..Help PLZ..There are probably a number of ways to accomplish this...my preference would be to use a local variable to hold the value.

declare @.seq varchar(20); --[whatever...could be int]

select @.seq = lastvalue+incrementby
from agilesequences
where name='SEQPROPERTYTABLE';

insert into propertytable values (@.seq, 13926, 0, 4, 1, 451, 1, 8, 1);

--------
This should work unless I fat-fingered something.
There may be a way to do this in one pop...
--------|||INSERT INTO propertytable(ColName)
SELECT lastvalue+incrementby
FROM agilesequences
WHERE name='SEQPROPERTYTABLE'

What's this?

13926, 0, 4, 1, 451, 1, 8, 1

And You should supply a collist for the inserted tables...

Damn another fluff reply...|||There are probably a number of ways to accomplish this...my preference would be to use a local variable to hold the value.

declare @.seq varchar(20); --[whatever...could be int]

select @.seq = lastvalue+incrementby
from agilesequences
where name='SEQPROPERTYTABLE';

insert into propertytable values (@.seq, 13926, 0, 4, 1, 451, 1, 8, 1);

--------
This should work unless I fat-fingered something.
There may be a way to do this in one pop...
--------
Thanks a lot guys..
This worked for me.

Inserting value to Identity Column

I want all users in database to be able to call Set IDENTITY_INSERT
[table] = ON
However when a user who is not the dbo logs in to execute the stored
procedure I get the following error message.
prcIdentityFudgeSave, Line 8
The current user is not the database or object owner of table
'IdentityFudge'. Cannot perform SET operation.
Please Help
(See Stored Prc below that the users are trying to execute)
CREATE PROCEDURE prcIdentityFudgeSave
(
@.IdentityBeforeTrigger int
) AS
--Fudge the Identity Column
delete From IdentityFudge
SET IDENTITY_INSERT IdentityFudge ON
Insert Into IdentityFudge (IndentityFudgeID) values
(@.IdentityBeforeTrigger)
GO> I want all users in database to be able to call Set IDENTITY_INSERT
> [table] = ON
Then why are you using IDENTITY at all?|||give permission to the SQL Server login [SQL Server authentication] / Window
login [Windows Authentication] to acess the table and sp.
"dermot" <dfrench@.tommyfrench.co.uk> wrote in message
news:1110465514.012467.136020@.f14g2000cwb.googlegroups.com...
>I want all users in database to be able to call Set IDENTITY_INSERT
> [table] = ON
> However when a user who is not the dbo logs in to execute the stored
> procedure I get the following error message.
> prcIdentityFudgeSave, Line 8
> The current user is not the database or object owner of table
> 'IdentityFudge'. Cannot perform SET operation.
> Please Help
> (See Stored Prc below that the users are trying to execute)
>
> CREATE PROCEDURE prcIdentityFudgeSave
> (
> @.IdentityBeforeTrigger int
> ) AS
> --Fudge the Identity Column
> delete From IdentityFudge
> SET IDENTITY_INSERT IdentityFudge ON
> Insert Into IdentityFudge (IndentityFudgeID) values
> (@.IdentityBeforeTrigger)
> GO
>|||Joel,
I think that is not enough. See "SET IDENTITY_INSERT (Permissions)" in BOL.
AMB
"Joel Leong" wrote:

> give permission to the SQL Server login [SQL Server authentication] / Wind
ow
> login [Windows Authentication] to acess the table and sp.
> "dermot" <dfrench@.tommyfrench.co.uk> wrote in message
> news:1110465514.012467.136020@.f14g2000cwb.googlegroups.com...
>
>|||If the value in this column has meaning to your users then don't use
IDENTITY. Why would you want to do this?
David Portas
SQL Server MVP
--

Inserting the date as defaultvalue in a sqldatasource Parameter

Hi Guys.

I am trying to insert the date as the default value into the DatePosted parameter in the sqldatasource object. I have put have the following below but it doesn't work. I have also tried <asp:Parameter Name="DatePosted" Type="DateTime" DefaultValue="<%= Date.Now %>" />

<asp:Parameter Name="DatePosted" Type="DateTime" DefaultValue="<%= now() %>" />

I know the solution is probably simple and I look like an idiot, but excuse me because I am very knew and fragile at this lol...

any help would be great :).

Mike.

Change your code to this,and it is OK.

<asp:Parameter Name="DatePosted" Type="DateTime" DefaultValue='<%# DateTime.Now %>' />

|||

Hi Jason,This does not work either. It says you can't use databind in a parameter I tried it with <%= %> as well but still nothing?

Any other ideas?

|||

If you can't override the parameter, it would be easiest to change the sql statement to reference getdate or getutcdate instead like:

INSERT INTO MyTable(col1,DatePosted) VALUES (@.col1,getutcdate())

If you have to allow for the parameter to be overridden, try putting this code into page_init:

SqlDatasource1.InsertParameters("DatePosted").DefaultValue=now

|||just do it at the database level via enterprise manager by modifying the table in question and set the default for the column by specifying the getdate() function so when a recorded is inserted via code with dateposted not supplied the sql server will insert that column with the current date.|||I have the same problem and this solution doesnt work. The column in the table has a default of getdate(), but I still get an error that NULL cannot be insert into the column. The problem is there is no way to tell it to not supply a value--if a value is not set it will supply NULL. Is there another way to default a DateTime parameter to DateTime.Now or getdate()? TIA|||

The solution I posted should work. The solution posted after mine should also work.

Please post your code.

|||

Doing it at the database level only works if you can remove it as a parameter entirely. In my case I needed to allow the user to enter datetime and only default it to getdate()/DateTime.Now when they choose not to enter specific date.

I knew of the page init method you suggested, but I was trying to avoid this as I was experimenting with how much you can do with only declarative code--I guess I just hit one of the limits of declarative coding.

(For the record, I much prefer DDD with NHibernate, NUnit, etc., but in some cases, especially for small/trivial/non-critical projects, being able to do things quickly outweighs other concerns.)

Inserting Specific Value in Query Result

I have a table where I need to insert a unique USERID based on the
USERNAME. Seems pretty simple to me, however I am a novice writing SQL
statements.
My knowledge is very limited, so when responding please do not assume I
know basic code.
I have taken on this task in an emergency situation.
Thanks in advance for your help and please let me know if further
information is needed.DK13 (DericK@.sklarcorp.com) writes:
> I have a table where I need to insert a unique USERID based on the
> USERNAME. Seems pretty simple to me, however I am a novice writing SQL
> statements.
> My knowledge is very limited, so when responding please do not assume I
> know basic code.
> I have taken on this task in an emergency situation.
If it's an emergencty I am puzzled by the fact that you did not provide
more information. The standard recommendation for assistance is that
you post:
o CREATE TABLE statement(s) for the tables involved.
o INSERT statements with sample data.
o The desired result given the sample.
This permits anyone who wants to answer your question to easily
copy-and-paste into a query and developer a tested solution.
I realise that you if don't know basic code, this may go over your
head. But you should at least be able to produce some sample input,
and the output from it. "A unique USERID based on the USERNAME" is
quite ambiguous. And supposedly there is a requirement that these
user id follows some pattern. It would also be interesting to know
how these usernames look like. Furthermore, it is also unclear whether
you want the userid added to the query result, or a column added
to the table. (Your subject lines says the former, but the latter
makes more sense.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||OK, let's see if this helps.
The query I ran:
select* from sysdba.HISTORY where USERNAME = ('Cruz, Jane')
The result set looks like this(I've left out several columns):
USERID USERNAME ORIGINALDATE
<NULL> Cruz, Jane 2/6/2006 12:00:05 AM
<NULL> Cruz, Jane 2/6/2006 11:25:00 PM
Now from this result I would like to Insert a userid, such as
'U6UJ9A00003J' where the USERID above = "<NULL>". I have several users
that I need to do this for.
Let me know if this is more useful info|||DK13 (DericK@.sklarcorp.com) writes:
> OK, let's see if this helps.
> The query I ran:
> select* from sysdba.HISTORY where USERNAME = ('Cruz, Jane')
> The result set looks like this(I've left out several columns):
> USERID USERNAME ORIGINALDATE
><NULL> Cruz, Jane 2/6/2006 12:00:05 AM
><NULL> Cruz, Jane 2/6/2006 11:25:00 PM
> Now from this result I would like to Insert a userid, such as
> 'U6UJ9A00003J' where the USERID above = "<NULL>". I have several users
> that I need to do this for.
> Let me know if this is more useful info
You could do:
UPDATE tbl
SET USERID = dbo.createuseridfromname(USERNAME)
WHERE USERID IS NULL
All that remains is to write the user-defined function. Unfortunately,
I cannot do that, because I don't know the rules.
Or are the user ids in fact already defined in a table somewhere?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thank you, I did some other research and found the same function to be
helpful, much appreciated!!

Inserting same identity value into two linked tables

Hi,
I have two tables which has 2 linked fields, as David Portas posted in
'Field value determines whether there is extra info that should be held
about that record':
CREATE TABLE events (event_no INTEGER NOT NULL PRIMARY KEY, event_type
INTEGER NOT NULL CHECK (event_type BETWEEN 1 AND 10 /* however many
different types you have */), UNIQUE (event_no, event_type) /* ...
other columns for events */) ;
CREATE TABLE type1_events (event_no INTEGER NOT NULL PRIMARY KEY,
event_type INTEGER NOT NULL DEFAULT (1) CHECK (event_type=1), FOREIGN
KEY (event_no, event_type) REFERENCES events (event_no, event_type) ON
DELETE CASCADE /* ... other columns for event type 1 */) ;
I've set the event_no identity property in table 'events' to true. I have a
stored procedure to insert data to both tables at once (using 2 INSERT INTO
statements coming one after another).
The problem is that currently I'm using ADO to generate the new ID (By
selecting MAX of the event_no, adding 1 to the MAX value, and using the
result as the new event_no) which is sent as a parameter to the stored
procedure, which uses it in the 2 INSERT INTO statements.
This method is working fine by now, but could potentially cause problems.
How can generate a new value for that event_no identity column, then use it
in both the INSERT INTO statements?
I've read about @.@.IDENTITY, but I'm not sure about how to use it. I've also
read that there are better options, but these are not relevant since I'm
using SQL Server 7.
Kind Regards,
Amir.Amir wrote:
> Hi,
> I have two tables which has 2 linked fields, as David Portas posted in
> 'Field value determines whether there is extra info that should be held
> about that record':
> CREATE TABLE events (event_no INTEGER NOT NULL PRIMARY KEY, event_type
> INTEGER NOT NULL CHECK (event_type BETWEEN 1 AND 10 /* however many
> different types you have */), UNIQUE (event_no, event_type) /* ...
> other columns for events */) ;
> CREATE TABLE type1_events (event_no INTEGER NOT NULL PRIMARY KEY,
> event_type INTEGER NOT NULL DEFAULT (1) CHECK (event_type=1), FOREIGN
> KEY (event_no, event_type) REFERENCES events (event_no, event_type) ON
> DELETE CASCADE /* ... other columns for event type 1 */) ;
> I've set the event_no identity property in table 'events' to true. I have
a
> stored procedure to insert data to both tables at once (using 2 INSERT INT
O
> statements coming one after another).
> The problem is that currently I'm using ADO to generate the new ID (By
> selecting MAX of the event_no, adding 1 to the MAX value, and using the
> result as the new event_no) which is sent as a parameter to the stored
> procedure, which uses it in the 2 INSERT INTO statements.
> This method is working fine by now, but could potentially cause problems.
> How can generate a new value for that event_no identity column, then use i
t
> in both the INSERT INTO statements?
> I've read about @.@.IDENTITY, but I'm not sure about how to use it. I've als
o
> read that there are better options, but these are not relevant since I'm
> using SQL Server 7.
> Kind Regards,
> Amir.
Use @.@.IDENTITY to retrieve the last inserted IDENTITY value. Like this
for example:
CREATE TABLE events (event_no INTEGER IDENTITY NOT NULL PRIMARY KEY,
event_name VARCHAR(10) NOT NULL UNIQUE, event_type INTEGER NOT NULL
CHECK (event_type BETWEEN 1 AND 10), UNIQUE (event_no, event_type) ) ;
INSERT INTO events (event_name, event_type)
VALUES ('foo',1);
SELECT @.@.IDENTITY AS last_identity_value ;
David Portas
SQL Server MVP
--|||Thanks, David!
Kind Regards,
Amir.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1137159522.649050.309090@.g47g2000cwa.googlegroups.com...
> Amir wrote:
> Use @.@.IDENTITY to retrieve the last inserted IDENTITY value. Like this
> for example:
> CREATE TABLE events (event_no INTEGER IDENTITY NOT NULL PRIMARY KEY,
> event_name VARCHAR(10) NOT NULL UNIQUE, event_type INTEGER NOT NULL
> CHECK (event_type BETWEEN 1 AND 10), UNIQUE (event_no, event_type) ) ;
> INSERT INTO events (event_name, event_type)
> VALUES ('foo',1);
> SELECT @.@.IDENTITY AS last_identity_value ;
> --
> David Portas
> SQL Server MVP
> --
>

Inserting records with value outside replication identity ranges

I have a live system that uses merge replication. We are upgrading the
system by setting up a whole new set of servers. When we go live on the new
servers, I would like to copy into the new server database, any changes that
have been made to the old databases. Unfortunately, replication adds check
constraints to identity columns starting with the last identity value in the
database. The old system is still adding id values below the maximum id
(e.g. each region is adding values within their own range which is below the
maximum id in the table.)
I can't just copy the entire database and then setup replication because the
snapshot would take way too long to get to one of the subscribers and we need
to be able to switchover relatively quickly.
Since SQL Server does not let me modify or drop the identity column check
constraint that replication added, how can I add these identity values after
replication has already been set up on the new system?
There could be other issues though. What if the subscriber record is updated
rather than inserted - how will you know which updated rows to take? I think
the simplest way is to sync all subscribers prior to migrating the production
database to the new server. Then using this as the master, set up replication
from the new publisher.
Paul Ibison
|||During the cutover, we will stop all changes to the old database, wait for
replication to sync up, then copy changes from the old publisher over to the
new publisher. then get everyone connected to the new servers.
One of our subscribers is in China where network connectivity is slow and
unreliable. I have tried getting a snapshot with all the data to this
subscriber and it just takes too long - days if it ever succeeds, which so
far it has not. I have to get the snapshot to China with some of the tables
empty, then add the data after.
"Paul Ibison" wrote:

> There could be other issues though. What if the subscriber record is updated
> rather than inserted - how will you know which updated rows to take? I think
> the simplest way is to sync all subscribers prior to migrating the production
> database to the new server. Then using this as the master, set up replication
> from the new publisher.
> Paul Ibison

Inserting record in DB2 from Sql Server 2000

I am trying to insert a record in DB2 Database which is a Linked Server in SQl SERVER 2000.

I have tried fetching the value from DB2 Database using Openquery which is working fine, but when i try insertion of record it gives an error.

Follwoing is the Tsql I am using for insertion

insert into openquery(DB2_DB2T, 'Select Audit_nbr from $ZUDBA01.TPT200_VOLS where 1=0') values(9999999)

When i execute this Tsql i get following Error message

Server: Msg 7399, Level 16, State 1, Line 26
OLE DB provider 'MSDASQL' reported an error. The provider reported an unexpected catastrophic failure.
[OLE/DB provider returned message: Query cannot be updated because the FROM clause is not a single simple table name.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowsetChange::InsertRow returned 0x8000ffff: The provider reported an unexpected catastrophic failure.].

What could be the mistake I am making, Please help me out.

Thanks in Advance

Pranjal

You are doing an insert into a table and specifying a filter - it could be complaining about that

Try

insert into openquery(DB2_DB2T, 'Select Audit_nbr from $ZUDBA01.TPT200_VOLS') values(9999999)

also try

insert into openquery(DB2_DB2T, 'Select * from $ZUDBA01.TPT200_VOLS') values(all the values for the row)

|||

Hi,

I tried the query as you suggested by still I am encountering the same error. Please advice. Thanks

INSERTING NULL VALUES VIA STORED PROCEDURES

Hi,

Becouse some of my stored procedure parameters can be NULL, for every parameter with potential NULL value I have to do checking like this:

errorParams[3].Value = (e.InnerException==null)?(object)DBNull.Value:(object)e.InnerException;

When I do it like this ( it is a part of preparing values for insert ):

errorParams[3].Value = e.InnerException

no row is added. Is there any way to pass over that check : (e.InnerException==null)?(

and do it easier?

This the sample exception message for that issue:

{System.Data.SqlClient.SqlException: Procedure or Function 'sp_addError' expects parameter '@.InnerException', which was not supplied.

Thanks,

Pawe?

That depends on your Procedure declaration, you will have to allow a default parameter in orde to let it work.

CREATE PROCEDURE sp_addError
(
@.InnerException VARCHAR(1000) = NULL --Which declares the default if no value is passed through
)
(...)

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Cool :) Big thanks!

Pawe?

|||Thank you very much
It was solution of my Big problem

INSERTING NULL VALUES VIA STORED PROCEDURES

Hi,

Becouse some of my stored procedure parameters can be NULL, for every parameter with potential NULL value I have to do checking like this:

errorParams[3].Value = (e.InnerException==null)?(object)DBNull.Value:(object)e.InnerException;

When I do it like this ( it is a part of preparing values for insert ):

errorParams[3].Value = e.InnerException

no row is added. Is there any way to pass over that check : (e.InnerException==null)?(

and do it easier?

This the sample exception message for that issue:

{System.Data.SqlClient.SqlException: Procedure or Function 'sp_addError' expects parameter '@.InnerException', which was not supplied.

Thanks,

Pawe?

That depends on your Procedure declaration, you will have to allow a default parameter in orde to let it work.

CREATE PROCEDURE sp_addError
(
@.InnerException VARCHAR(1000) = NULL --Which declares the default if no value is passed through
)
(...)

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Cool :) Big thanks!

Pawe?

|||Thank you very much
It was solution of my Big problem

Inserting Null Value

Is there a way, I can insert NULL value to "DT_Date" type Row Column using Script Component Transformation of Data flow?

Ie. I have a column named Ordered Date which is type DT_Date. Based on some condition within a script component task, I want to set the value to NULL, however, since the type is date it will not allow nulls.

Found the answer, it was fairly simple. Row.FieldName_ISNULL() property is a read/write property. This property can be used to set nulls.

|||I'm assuming you are also doing some other work in the Script, but I thought I would mention that you can also do this in the Derived Column transform with the NULL() function. In your case, NULL(DT_DATE).|||Let me just add that the <columnname>_IsNull property may be read/write, or may be read-only, depending on the corresponding Usage Type specified for the individual column.

-Doug
|||

I have a simple package that takes data from excel sources, runs a Data Transformation to get the decimal conversion etc into the same format as SQL ... but some of the columns are nullable - so my Data Transformation fails when it hits a null value in the source... (converting from float in the source to decimal in the destination.. though becuase of the empty values it sees the source as a string). Seems like it should be simple to allow it to pass nulls but I can't seem to figure it out (am new to SSIS). None of the fields in the transformation appear to be editable so that I can add the NULL() as mentioned above - if I got into the Advanced editor I can manually type the data type but NULL(DT_DECIMAL) gives me error "DataTypeConverter cannot convert from System.String."

Can anyone point me in the right direction? :)

|||

<<a Data Transformation to get the decimal conversion etc into the same format as SQL >>

I believe your problem is in the decimal conversion processing you mention. The expression - or code - should be written to handle the occurence of nulls without bombing...

|||

That is just it... I am not "writing" anything - its not a script component transformation task but rather just a "Data Conversion" object. If you edit the Data Conversion object it has input and then output... you can change the output alias, datatype, scale, codepage etc but there isn't any options regarding nulls that I can find.

|||

So the problem is that the source data looks like an empty string, and in that case you want to put NULL in the destination? You might try using a Derived Column instead of the Data Conversion. There, you can write an expression.

For example, if your string input column is named "InputCol", and you are converting to DT_DECIMAL with scale 10, your expression for the new column would look like:

LEN(InputCol) == 0 ? NULL(DT_DECIMAL, 10) : (DT_DECIMAL, 10)InputCol

Let me know if that helps.

Mark

Inserting Null Value

Is there a way, I can insert NULL value to "DT_Date" type Row Column using Script Component Transformation of Data flow?

Ie. I have a column named Ordered Date which is type DT_Date. Based on some condition within a script component task, I want to set the value to NULL, however, since the type is date it will not allow nulls.

Found the answer, it was fairly simple. Row.FieldName_ISNULL() property is a read/write property. This property can be used to set nulls.

|||I'm assuming you are also doing some other work in the Script, but I thought I would mention that you can also do this in the Derived Column transform with the NULL() function. In your case, NULL(DT_DATE).|||Let me just add that the <columnname>_IsNull property may be read/write, or may be read-only, depending on the corresponding Usage Type specified for the individual column.

-Doug
|||

I have a simple package that takes data from excel sources, runs a Data Transformation to get the decimal conversion etc into the same format as SQL ... but some of the columns are nullable - so my Data Transformation fails when it hits a null value in the source... (converting from float in the source to decimal in the destination.. though becuase of the empty values it sees the source as a string). Seems like it should be simple to allow it to pass nulls but I can't seem to figure it out (am new to SSIS). None of the fields in the transformation appear to be editable so that I can add the NULL() as mentioned above - if I got into the Advanced editor I can manually type the data type but NULL(DT_DECIMAL) gives me error "DataTypeConverter cannot convert from System.String."

Can anyone point me in the right direction? :)

|||

<<a Data Transformation to get the decimal conversion etc into the same format as SQL >>

I believe your problem is in the decimal conversion processing you mention. The expression - or code - should be written to handle the occurence of nulls without bombing...

|||

That is just it... I am not "writing" anything - its not a script component transformation task but rather just a "Data Conversion" object. If you edit the Data Conversion object it has input and then output... you can change the output alias, datatype, scale, codepage etc but there isn't any options regarding nulls that I can find.

|||

So the problem is that the source data looks like an empty string, and in that case you want to put NULL in the destination? You might try using a Derived Column instead of the Data Conversion. There, you can write an expression.

For example, if your string input column is named "InputCol", and you are converting to DT_DECIMAL with scale 10, your expression for the new column would look like:

LEN(InputCol) == 0 ? NULL(DT_DECIMAL, 10) : (DT_DECIMAL, 10)InputCol

Let me know if that helps.

Mark