DATATYPE PROBLEM(cross)

I have a column in my table BizdekiFiyat . The datatype = float length =8
(to save money values).. It is impossible to change these attributes for
some reasons.
It has records like This
BizdekiFiyat
110
24
29.5
31.35
I use Vb.Net . I use ExecuteReader To select values from my db..
After first attemp
Dim BizdekiFiyat As Integer OR Dim BizdekiFiyat As Decimal
IT returns
110
24
295
3135
Dim BizdekiFiyat As String
It returns right results.
110
24
29.5
31.35
There is a problem with decimal records when i want to evaluate this
values..
For example
Dim BizdekiFiyat As String
BizdekiFiyat = BizdekiFiyat * 1.05
It is supposed to be
29.5 * 1.05 =30.975
31.35*1.05=32.9175
but it returns
309,75
3291,75
How can i solve this problem ?"Savas Ates" <in da club> wrote in message
news:OEhAfpwLGHA.648@.TK2MSFTNGP14.phx.gbl...
>I have a column in my table BizdekiFiyat . The datatype = float length =8
>(to save money values).. It is impossible to change these attributes for
>some reasons.
>
> Dim BizdekiFiyat As Integer OR Dim BizdekiFiyat As Decimal
> IT returns
> 110
> 24
> 295
> 3135
Integer datatype will always truncate your decimal fraction values.
I've had data dimension problems trying to use the Decimal datatype for
holding (SQL) decimal data returned through parameters using MS's EntLib
DAAB. I resolved this by using .NET's Double datatype (though I'd prefer to
know why .NET's decimal gave me the problem in the first place).

> Dim BizdekiFiyat As String
> It returns right results.
Because the value is being represented and stored as a string (just like
typing into a textbox), not a numeric type, so...

> There is a problem with decimal records when i want to evaluate this
> values..
> For example
> Dim BizdekiFiyat As String
> BizdekiFiyat = BizdekiFiyat * 1.05
> How can i solve this problem ?
You're expecting .NET to intelligently convert your datatypes for you, which
it is valiantly trying to do. You should consider setting Option Strict on
(Tools | Options | Projects | VB Defaults) to prevent loose data typing and
late binding. You should strongly type your datatypes as a matter of god
practice. When you need to convert datatypes, dothis explicitely using
CType(sourceObj, targetType), or the shorthand versions such as Cint(value),
CDbl(value), etc.
As for your calculations: e.g. using Double to store your values, create a
function which you'll call when necessary to do your calculations:
private function MultiplyBizdekiFiyat(Byval origValue as double, Byval
MultiplyBy as double) As Double
'Perform the calculation
MultiplyBizdekiFiyat = origValue * MultiplyBy
'Return the value to the calling method.
return MultiplyBizdekiFiyat
end function
Hope that helps
Al

datatype problem

how can i transfer incoming data from flat file which would be a string to my sql table of column int...

i have a problem with datatype can i conver string to int how should i do it...new to it

please help!!

This is what the Data Conversion Task is for. U could also use the Derived Column Task. Place it between source and destination, convert your column there.

What more can I say?

Pipo1

|||Use a derived column transformation.

This is one example of an expression you could use: (DT_I4)[Your_Column]sql

Datatype of column dynamically

Hi All,
How can I get the datatype of a column using a query?
Thanks,
SanjeevUse view information_schema.columns.
Example:
use northwind
go
select
data_type
from
information_schema.columns
where
table_schema = 'dbo'
and table_name = 'orders'
and column_name = 'orderid';
AMB
"mahajan.sanjeev@.gmail.com" wrote:

> Hi All,
> How can I get the datatype of a column using a query?
> Thanks,
> Sanjeev
>|||Thanks!
I was going to use a join on sysobjects, syscolumns and systypes to get
it but this looks better!

DataType Money

Please i need to display the money column in DataBase in an asp.net page but i get something like this 786.0000 how can i format it so that i get something like 786.00

Thanx

That kind of formatting is best done at the application level.|||

You could use the ToString("c") to display as currency on your page.

Double TotalCost = 786.0000;

lblCost.Text = TotalCost.ToString("c");

Your label should now be set to $786.00

|||

If you are binding it, you can use <%# Bind("YourColumnName","{0:c}") %> and will display $786.00
If you dont want to display the currency, repace c with your own format like #0.00 at it should display 786.00

DataType Conversion using WHERE IN ( )

I am getting a "Syntax error converting the varchar value '10,90' to a column of data type int." error when I run the following procedure:

@.myList varchar(200)

SELECT column1
FROM table1
WHERE table1.ID IN (@.myList)

When @.myList is a single value, I get no errors. However, when @.myList is a comma separated list like in the message above, I error out. I am using SQL Server 2000.

How else can I build this list of IDs? Thank you in advance for your comments.

--ColonelYou cannot do what you are trying to do. YOu need to use dynamic SQL, or send in a string and use a function to create a table variable and do the operation based upon a select on that table variable.|||I found that my varchar parameter was being sent in with single quotes around it. I removed these, and now my WHERE clause looks like this:

WHERE table1.ID IN (REPLACE(@.myList,'''',NULL))

and it works just fine.

I did not add those quotes to the list of values. I believe that SQL Server adds them to delimit the text. Thank you for your comments.

Datatype Conversion Problem.

Hi ALL!

I have a table named 'Table1' which contains a column 'Name'.
The data type of column [Name] is varchar(50).

When i try to change its datatype to binary by trying following code

ALTER TABLE Table1 Alter Column [Name] Binary(5000)

It gives following error.

" Creation of table 'bp_MAIN' failed because the row size would be 10021, including internal overhead. This exceeds the maximum allowable table row size, 8060. "

So, how can i change the datatype of this column ?

Regards,
Shabber Abbas.U cannot convert varchar column to binary column explicitly.
One solution is ,create a new table (lets say t1) with binary datatype.
Then convert and insert record into t1 table from ur original table.
Drop original table and rename new table to original table.
set same permission as orginal table.

--eg:
insert into t1(othercolumnnames,name) select othercolumnnames,convert(binary(5000),name) as name from Table1sql

Datatype Conversion Problem.

Hi ALL!

I have a table named 'Table1' which contains a column 'Name'.
The data type of column [Name] is varchar(50).

When i try to change its datatype to binary by trying following code

ALTER TABLE Table1 Alter Column [Name] Binary(5000)

It gives following error.

" Creation of table 'bp_MAIN' failed because the row size would be 10021, including internal overhead. This exceeds the maximum allowable table row size, 8060. "

So, how can i change the datatype of this column ?

Regards,
Shabber Abbas.Not meaning to be thick here, but why are you changing a varchar to a binary? Did you want instead to change it to nvarchar?

Regards,

hmscott|||In this case, you are implying that you want to convert varchar data to binary. I don't think that can be done automatically. The error might be misleading.

If that's the only field, it shouldn't give that error, but a table consisiting of only a binary field seems like it's not very useful. Is a blob out of the question? it only takes up 16bytes of the page. Yould definetely need to export/import then.

You should be able to add a binary column, or export the data, recreate the table with a binary field, and then import the data, with suitable massaging.|||You could use varbinary, but if the amount of data in the row exceeds 8060, you will get errors, instead of warnings.

Datatype change INT to BIGINT on a large table

Hi folks,
I have a table which is of 500 GB in size. I need to change the datatype
of a column from INT to BIGINT.
When I tried making this change from Enterprise Manager, it was throwing
log space is full. I also truncated the log and tried again, eventhen I
face the same problem.
Also I have limited space available on data drive. After some research I
found that SQL Server interally creates a Tmp table with the new
datatype, populates that table with orginal table data, drops the
original table and then renames the Tmp table.
So I must need atleast 500 GB additional freespace on data drive, but I
do not have 500 GB free space on data drive.
I am just thinking the below alternate way to do this task.
1. BCP out the data to a temporary mapped network drive which has 500 GB
free space.
2. Drop the table.
3. Recreate the table with BIGINT datatype on the required column.
4. BCP in the data.
5. Recreate the Keys and constraints.
Can someone suggest me whether this is the best way, any possibility of
loosing the data if I follow this way. Please suggest me if there is a
better approach.
Thanks in advance.
*** Sent via Developersdex http://www.examnotes.net ***Another option is to use ALTER TABLE ... ALTER COLUMN ...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Venkat" <nospam_venkat_asp@.yahoo.co.uk> wrote in message
news:u38r6IDyFHA.3588@.tk2msftngp13.phx.gbl...
> Hi folks,
> I have a table which is of 500 GB in size. I need to change the datatype
> of a column from INT to BIGINT.
> When I tried making this change from Enterprise Manager, it was throwing
> log space is full. I also truncated the log and tried again, eventhen I
> face the same problem.
> Also I have limited space available on data drive. After some research I
> found that SQL Server interally creates a Tmp table with the new
> datatype, populates that table with orginal table data, drops the
> original table and then renames the Tmp table.
> So I must need atleast 500 GB additional freespace on data drive, but I
> do not have 500 GB free space on data drive.
> I am just thinking the below alternate way to do this task.
> 1. BCP out the data to a temporary mapped network drive which has 500 GB
> free space.
> 2. Drop the table.
> 3. Recreate the table with BIGINT datatype on the required column.
> 4. BCP in the data.
> 5. Recreate the Keys and constraints.
> Can someone suggest me whether this is the best way, any possibility of
> loosing the data if I follow this way. Please suggest me if there is a
> better approach.
> Thanks in advance.
> *** Sent via Developersdex http://www.examnotes.net ***|||Thanks Tibor,
If I do ALTER TABLE ...ALTER COLUMN, will it log to transaction log
file?
*** Sent via Developersdex http://www.examnotes.net ***|||You need to test first. Create a similar table in a smaller database, copy o
ver a subset of the rows
and do a test. Sometimes, these changes can go without touching the data (im
mediately), sometimes,
all data is changed immediately, and changes has to be logged. I haven't see
n any document
describing the exact rules for when a change is immediate or not. So, do a t
est first to be certain.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Venkat" <nospam_venkat_asp@.yahoo.co.uk> wrote in message
news:OkPRGnDyFHA.3312@.TK2MSFTNGP09.phx.gbl...
> Thanks Tibor,
> If I do ALTER TABLE ...ALTER COLUMN, will it log to transaction log
> file?
> --
> *** Sent via Developersdex http://www.examnotes.net ***|||I think your bulk copy solution is the best way to go. To reduce the size of
the exported file, use native format instead of fixed width or tab delimited
text columns. To reduce transaction logging while importing the data, set
the database recovery model to "bulk insert" or "simple", set the database
to single user / dbo use only mode, and re-create indexes only after the
import has completed.
"Venkat" <nospam_venkat_asp@.yahoo.co.uk> wrote in message
news:u38r6IDyFHA.3588@.tk2msftngp13.phx.gbl...
> Hi folks,
> I have a table which is of 500 GB in size. I need to change the datatype
> of a column from INT to BIGINT.
> When I tried making this change from Enterprise Manager, it was throwing
> log space is full. I also truncated the log and tried again, eventhen I
> face the same problem.
> Also I have limited space available on data drive. After some research I
> found that SQL Server interally creates a Tmp table with the new
> datatype, populates that table with orginal table data, drops the
> original table and then renames the Tmp table.
> So I must need atleast 500 GB additional freespace on data drive, but I
> do not have 500 GB free space on data drive.
> I am just thinking the below alternate way to do this task.
> 1. BCP out the data to a temporary mapped network drive which has 500 GB
> free space.
> 2. Drop the table.
> 3. Recreate the table with BIGINT datatype on the required column.
> 4. BCP in the data.
> 5. Recreate the Keys and constraints.
> Can someone suggest me whether this is the best way, any possibility of
> loosing the data if I follow this way. Please suggest me if there is a
> better approach.
> Thanks in advance.
> *** Sent via Developersdex http://www.examnotes.net ***|||Thanks JT for your excellent suggestions/comments.
I tried Tibor's suggestion (ALTER TABLE...ALTER COLUMN), it logs to
transaction log even when the recovery model is set to simple.
Internally it updates all the rows. But I do not have enough space on
log drive. So I will have to go with BCP option.
*** Sent via Developersdex http://www.examnotes.net ***|||I tried BCPing with Native format and Char format option. It seems that
the the file unloaded using Char format is smaller than the one created
with Native format. Any ideas..
--
*** Sent via Developersdex http://www.examnotes.net ***|||I don't recall offhand the specifics, but there are cases (perhaps with
decimal data types) where exporting to char format and then re-importing
will cause loss of data resolution. If you have space for exporting to
native format, then go ahead and use that.
"Venkat" <nospam_venkat_asp@.yahoo.co.uk> wrote in message
news:%235R%23ikNyFHA.3420@.TK2MSFTNGP10.phx.gbl...
>I tried BCPing with Native format and Char format option. It seems that
> the the file unloaded using Char format is smaller than the one created
> with Native format. Any ideas..
> --
> *** Sent via Developersdex http://www.examnotes.net ***sql

datatype casting in derived column

Here is my expression in a derived component:

"Failed insert into PONL_WELL. WELL_NO=" + (DT_WSTR,10)PROP_NO

PROP_NO comes from ms sql server , and the derived component datatype for this column is DT_WSTR.

The destination will be ms sql server, and i have a data conversion after the derived component to cast from DT_WSTR to DT_STR.

However, the derived component failed everytime giving me

Error: 0xC0049064 at Load Ponl_Well, Derived Column [1342]: An error occurred while attempting to perform a type cast.

Anyone know how i can eliminate the data conversion component and just do my string and column concatenation in the derived column and have it output as DT_STR?

Sub-expressions and literals in a derived column expression are always DT_WSTR, but you can cast the expression result to DT_STR by wrapping the whole expression with a cast:

(DT_STR,<length>,<codepage>)("Failed insert into PONL_WELL. WELL_NO=" + (DT_WSTR,10)PROP_NO)

That should eliminate the need for the data convert transform after the derived column.

However, the error you listed looks like it was coming from the derived column, and I'm not sure why that would be the case if PROP_NO is of type DT_WSTR...

|||thanks for trying, but i'm still getting the same error msg, anyhow, i create a script component and concatenate the strings together, not the solution i wanted but it should work for now

Datasource Reader - Name for output column is blank.

Hi,

I have a problem using the odbc datasource reader to execute a sql command on a progress database. My query is something like:-

select max(id), sum(amount) from my_table

OR

select a, b, c, recid(my_table) from my_table

which produces external columns and output columns with no name. The progress sql doesn't support using aliases on column names and setting validateexternalmetadata to false and manually naming the input and output parameters in the 'Advanced Editor' doesn't seem to work either. I either get the error 'The name for output column "" is blank and columns can not be blank' or if I add my own column names in the input and output parameters it fails in the pre-execute phase saying it can't find a column in the datasource with name 'myalias'

I can get around the aggregate functions by transfering all the data and doing the aggregate on the local server but I also need to call functions such as recid() which I can't work around. SQL2000 DTS ignored these things and matched as best it could where SQL 2005 IS seems overly strict.

Has anyone encountered similar problems and does anyone have any ideas? I'm currently at a loss :(

Hi,

I don't know about 'Progress'. But just in a curiosity I am asking this. If you can't have a column alias how will you create a view using the statements that you have mentioned above. If there is any workaround there, u better try the same thing.

|||

At a stretch, it should be possible to create a script source which would execute this query and assign the results to columns in the data flow.

another possibility may be to use an Execute SQL task - if you're not returning too many rows.

However, are you sure column aliases are not supported? The Progress online documentation appears to suggest that it may be. there may be a way in progress to make this happen.

Donald

DataSet Xml DateTime incompatible with Sql 2000

This is my first try at using OpenXML. I am trying to insert a datetime
value with a timezone into a SQL 2000 table datetime column, but I get the
error: Syntax error converting datetime from character string.
I have narrowed the problem down to the inclusion of the timezone, but I am
not able to change this in the xml. There was a post on this problem on
12/22/2005, but for SQL 2005, but I have not been able to make the solution
work for me, so it may be a 2005 enhancement.
What is the best way to handle this?
Here is my code:
DECLARE @.iTree INTEGER, @.xmlString VARCHAR(8000)
SET @.xmlString =
'<root><ReceivedDateTime>2005-12-13T11:21:26.000-05:00</ReceivedDateTime></r
oot>'
EXEC sp_xml_preparedocument @.iTree OUTPUT, @.xmlString
SELECT * FROM OpenXML(@.iTree, 'root',2)
WITH (Received datetime 'ReceivedDateTime')
EXEC sp_xml_removedocument @.iTree
<root><ReceivedDateTime>2005-12-13T11:21:26.000-05:00</ReceivedDateTime></ro
ot>The 12/22 solution is indeed a SQL Server 2005 solution.
Since the SQL Server datetime type does not support timezones, the default
casting does not support it.
You have to extract it as a varchar(), use SUBSTRING to drop the timezone
(write your own logic to adjust the value to a normalized timezone first, if
relative order is important) and then cast it to datetime.
Best regards
Michael
"Trillium" <Trillium@.discussions.microsoft.com> wrote in message
news:091651E2-3C18-4FCA-92B4-616A2F12CB99@.microsoft.com...
> This is my first try at using OpenXML. I am trying to insert a datetime
> value with a timezone into a SQL 2000 table datetime column, but I get the
> error: Syntax error converting datetime from character string.
> I have narrowed the problem down to the inclusion of the timezone, but I
> am
> not able to change this in the xml. There was a post on this problem on
> 12/22/2005, but for SQL 2005, but I have not been able to make the
> solution
> work for me, so it may be a 2005 enhancement.
> What is the best way to handle this?
> Here is my code:
> DECLARE @.iTree INTEGER, @.xmlString VARCHAR(8000)
> SET @.xmlString =
> '<root><ReceivedDateTime>2005-12-13T11:21:26.000-05:00</ReceivedDateTime><
/root>'
> EXEC sp_xml_preparedocument @.iTree OUTPUT, @.xmlString
> SELECT * FROM OpenXML(@.iTree, 'root',2)
> WITH (Received datetime 'ReceivedDateTime')
> EXEC sp_xml_removedocument @.iTree
> <root><ReceivedDateTime>2005-12-13T11:21:26.000-05:00</ReceivedDateTime></
root>
>|||Thanks for the quick response. It was exactly what I needed to know. I hav
e
a few other things that I could do easily with a transform (xslt) that would
make the SQL import easier, and I will probably go ahead with that since thi
s
was is also a problem. But, in case I need this again: is there any way to
make that change to the time (SUBSTRINGing the timezone out) within the
OpenXML statement? I did try to eliminate the zone like:
Received datetime left('ReceivedDateTime', 23)
which was clumsy and did not work, but seemed like it had a chance.
"Michael Rys [MSFT]" wrote:

> The 12/22 solution is indeed a SQL Server 2005 solution.
> Since the SQL Server datetime type does not support timezones, the default
> casting does not support it.
> You have to extract it as a varchar(), use SUBSTRING to drop the timezone
> (write your own logic to adjust the value to a normalized timezone first,
if
> relative order is important) and then cast it to datetime.
> Best regards
> Michael
> "Trillium" <Trillium@.discussions.microsoft.com> wrote in message
> news:091651E2-3C18-4FCA-92B4-616A2F12CB99@.microsoft.com...
>
>|||Here is a sample. Note that you can inline the TSQL function into the select
clause directly. You may also want to add some more complex logic to adjust
the date time based on the timezone if you expect more than one timezone to
be provided:
create function RemoveTZ(@.ds as nvarchar(40))
returns nvarchar(40)
begin
declare @.newds nvarchar(40)
if CHARINDEX(N'Z', @.ds) > 0
set @.newds =
SUBSTRING(@.ds, 1, CHARINDEX(N'Z', @.ds)-1)
else if CHARINDEX(N'+', @.ds) > 0
set @.newds =
SUBSTRING(@.ds, 1, CHARINDEX(N'+', @.ds)-1)
else if CHARINDEX(N'-', @.ds, CHARINDEX(N'T', @.ds)) > 0
set @.newds =
SUBSTRING(@.ds, 1, CHARINDEX(N'-', @.ds, CHARINDEX(N'T', @.ds))-1)
else -- assume it has no TZ
set @.newds = @.ds
return @.newds
end
go
declare @.h int;
exec sp_xml_preparedocument @.h output,
N'<root><d>2005-12-13T11:21:26.000-05:00</d><d>2005-12-13T11:21:26.000+05:00
</d><d>2005-12-13T11:21:26.000Z</d><d>2005-12-13T11:21:26.000</d></root>'
select CAST(dbo.RemoveTZ(d) as datetime)
from OpenXML(@.h, '/root/d')
with(d nvarchar(40) '.')
exec sp_xml_removedocument @.h
Michael
"Trillium" <Trillium@.discussions.microsoft.com> wrote in message
news:5ADF3F9D-BF96-4211-9BE0-44E5F27AEB1D@.microsoft.com...
> Thanks for the quick response. It was exactly what I needed to know. I
> have
> a few other things that I could do easily with a transform (xslt) that
> would
> make the SQL import easier, and I will probably go ahead with that since
> this
> was is also a problem. But, in case I need this again: is there any way
> to
> make that change to the time (SUBSTRINGing the timezone out) within the
> OpenXML statement? I did try to eliminate the zone like:
> Received datetime left('ReceivedDateTime', 23)
> which was clumsy and did not work, but seemed like it had a chance.
> "Michael Rys [MSFT]" wrote:
>|||I was and trying to put the logic/function in the WITH clause - no
wonder it did not work. Your explanation not only answers the question, bu
t
explains the OpenXML query structure.
THANK you!
"Michael Rys [MSFT]" wrote:

> Here is a sample. Note that you can inline the TSQL function into the sele
ct
> clause directly. You may also want to add some more complex logic to adjus
t
> the date time based on the timezone if you expect more than one timezone t
o
> be provided:
> create function RemoveTZ(@.ds as nvarchar(40))
> returns nvarchar(40)
> begin
> declare @.newds nvarchar(40)
> if CHARINDEX(N'Z', @.ds) > 0
> set @.newds =
> SUBSTRING(@.ds, 1, CHARINDEX(N'Z', @.ds)-1)
> else if CHARINDEX(N'+', @.ds) > 0
> set @.newds =
> SUBSTRING(@.ds, 1, CHARINDEX(N'+', @.ds)-1)
> else if CHARINDEX(N'-', @.ds, CHARINDEX(N'T', @.ds)) > 0
> set @.newds =
> SUBSTRING(@.ds, 1, CHARINDEX(N'-', @.ds, CHARINDEX(N'T', @.ds))-1)
> else -- assume it has no TZ
> set @.newds = @.ds
> return @.newds
> end
> go
> declare @.h int;
> exec sp_xml_preparedocument @.h output,
> N'<root><d>2005-12-13T11:21:26.000-05:00</d><d>2005-12-13T11:21:26.000+05:
00</d><d>2005-12-13T11:21:26.000Z</d><d>2005-12-13T11:21:26.000</d></root>'
> select CAST(dbo.RemoveTZ(d) as datetime)
> from OpenXML(@.h, '/root/d')
> with(d nvarchar(40) '.')
> exec sp_xml_removedocument @.h
> Michael
> "Trillium" <Trillium@.discussions.microsoft.com> wrote in message
> news:5ADF3F9D-BF96-4211-9BE0-44E5F27AEB1D@.microsoft.com...
>
>

DataSet Xml DateTime incompatible with Sql 2000

This is my first try at using OpenXML. I am trying to insert a datetime
value with a timezone into a SQL 2000 table datetime column, but I get the
error: Syntax error converting datetime from character string.
I have narrowed the problem down to the inclusion of the timezone, but I am
not able to change this in the xml. There was a post on this problem on
12/22/2005, but for SQL 2005, but I have not been able to make the solution
work for me, so it may be a 2005 enhancement.
What is the best way to handle this?
Here is my code:
DECLARE @.iTree INTEGER, @.xmlString VARCHAR(8000)
SET @.xmlString =
'<root><ReceivedDateTime>2005-12-13T11:21:26.000-05:00</ReceivedDateTime></root>'
EXEC sp_xml_preparedocument @.iTree OUTPUT, @.xmlString
SELECT * FROM OpenXML(@.iTree, 'root',2)
WITH (Received datetime 'ReceivedDateTime')
EXEC sp_xml_removedocument @.iTree
<root><ReceivedDateTime>2005-12-13T11:21:26.000-05:00</ReceivedDateTime></root>
The 12/22 solution is indeed a SQL Server 2005 solution.
Since the SQL Server datetime type does not support timezones, the default
casting does not support it.
You have to extract it as a varchar(), use SUBSTRING to drop the timezone
(write your own logic to adjust the value to a normalized timezone first, if
relative order is important) and then cast it to datetime.
Best regards
Michael
"Trillium" <Trillium@.discussions.microsoft.com> wrote in message
news:091651E2-3C18-4FCA-92B4-616A2F12CB99@.microsoft.com...
> This is my first try at using OpenXML. I am trying to insert a datetime
> value with a timezone into a SQL 2000 table datetime column, but I get the
> error: Syntax error converting datetime from character string.
> I have narrowed the problem down to the inclusion of the timezone, but I
> am
> not able to change this in the xml. There was a post on this problem on
> 12/22/2005, but for SQL 2005, but I have not been able to make the
> solution
> work for me, so it may be a 2005 enhancement.
> What is the best way to handle this?
> Here is my code:
> DECLARE @.iTree INTEGER, @.xmlString VARCHAR(8000)
> SET @.xmlString =
> '<root><ReceivedDateTime>2005-12-13T11:21:26.000-05:00</ReceivedDateTime></root>'
> EXEC sp_xml_preparedocument @.iTree OUTPUT, @.xmlString
> SELECT * FROM OpenXML(@.iTree, 'root',2)
> WITH (Received datetime 'ReceivedDateTime')
> EXEC sp_xml_removedocument @.iTree
> <root><ReceivedDateTime>2005-12-13T11:21:26.000-05:00</ReceivedDateTime></root>
>
|||Thanks for the quick response. It was exactly what I needed to know. I have
a few other things that I could do easily with a transform (xslt) that would
make the SQL import easier, and I will probably go ahead with that since this
was is also a problem. But, in case I need this again: is there any way to
make that change to the time (SUBSTRINGing the timezone out) within the
OpenXML statement? I did try to eliminate the zone like:
Received datetime left('ReceivedDateTime', 23)
which was clumsy and did not work, but seemed like it had a chance.
"Michael Rys [MSFT]" wrote:

> The 12/22 solution is indeed a SQL Server 2005 solution.
> Since the SQL Server datetime type does not support timezones, the default
> casting does not support it.
> You have to extract it as a varchar(), use SUBSTRING to drop the timezone
> (write your own logic to adjust the value to a normalized timezone first, if
> relative order is important) and then cast it to datetime.
> Best regards
> Michael
> "Trillium" <Trillium@.discussions.microsoft.com> wrote in message
> news:091651E2-3C18-4FCA-92B4-616A2F12CB99@.microsoft.com...
>
>
|||Here is a sample. Note that you can inline the TSQL function into the select
clause directly. You may also want to add some more complex logic to adjust
the date time based on the timezone if you expect more than one timezone to
be provided:
create function RemoveTZ(@.ds as nvarchar(40))
returns nvarchar(40)
begin
declare @.newds nvarchar(40)
if CHARINDEX(N'Z', @.ds) > 0
set @.newds =
SUBSTRING(@.ds, 1, CHARINDEX(N'Z', @.ds)-1)
else if CHARINDEX(N'+', @.ds) > 0
set @.newds =
SUBSTRING(@.ds, 1, CHARINDEX(N'+', @.ds)-1)
else if CHARINDEX(N'-', @.ds, CHARINDEX(N'T', @.ds)) > 0
set @.newds =
SUBSTRING(@.ds, 1, CHARINDEX(N'-', @.ds, CHARINDEX(N'T', @.ds))-1)
else -- assume it has no TZ
set @.newds = @.ds
return @.newds
end
go
declare @.h int;
exec sp_xml_preparedocument @.h output,
N'<root><d>2005-12-13T11:21:26.000-05:00</d><d>2005-12-13T11:21:26.000+05:00</d><d>2005-12-13T11:21:26.000Z</d><d>2005-12-13T11:21:26.000</d></root>'
select CAST(dbo.RemoveTZ(d) as datetime)
from OpenXML(@.h, '/root/d')
with(d nvarchar(40) '.')
exec sp_xml_removedocument @.h
Michael
"Trillium" <Trillium@.discussions.microsoft.com> wrote in message
news:5ADF3F9D-BF96-4211-9BE0-44E5F27AEB1D@.microsoft.com...[vbcol=seagreen]
> Thanks for the quick response. It was exactly what I needed to know. I
> have
> a few other things that I could do easily with a transform (xslt) that
> would
> make the SQL import easier, and I will probably go ahead with that since
> this
> was is also a problem. But, in case I need this again: is there any way
> to
> make that change to the time (SUBSTRINGing the timezone out) within the
> OpenXML statement? I did try to eliminate the zone like:
> Received datetime left('ReceivedDateTime', 23)
> which was clumsy and did not work, but seemed like it had a chance.
> "Michael Rys [MSFT]" wrote:
|||I was confused and trying to put the logic/function in the WITH clause - no
wonder it did not work. Your explanation not only answers the question, but
explains the OpenXML query structure.
THANK you!
"Michael Rys [MSFT]" wrote:

> Here is a sample. Note that you can inline the TSQL function into the select
> clause directly. You may also want to add some more complex logic to adjust
> the date time based on the timezone if you expect more than one timezone to
> be provided:
> create function RemoveTZ(@.ds as nvarchar(40))
> returns nvarchar(40)
> begin
> declare @.newds nvarchar(40)
> if CHARINDEX(N'Z', @.ds) > 0
> set @.newds =
> SUBSTRING(@.ds, 1, CHARINDEX(N'Z', @.ds)-1)
> else if CHARINDEX(N'+', @.ds) > 0
> set @.newds =
> SUBSTRING(@.ds, 1, CHARINDEX(N'+', @.ds)-1)
> else if CHARINDEX(N'-', @.ds, CHARINDEX(N'T', @.ds)) > 0
> set @.newds =
> SUBSTRING(@.ds, 1, CHARINDEX(N'-', @.ds, CHARINDEX(N'T', @.ds))-1)
> else -- assume it has no TZ
> set @.newds = @.ds
> return @.newds
> end
> go
> declare @.h int;
> exec sp_xml_preparedocument @.h output,
> N'<root><d>2005-12-13T11:21:26.000-05:00</d><d>2005-12-13T11:21:26.000+05:00</d><d>2005-12-13T11:21:26.000Z</d><d>2005-12-13T11:21:26.000</d></root>'
> select CAST(dbo.RemoveTZ(d) as datetime)
> from OpenXML(@.h, '/root/d')
> with(d nvarchar(40) '.')
> exec sp_xml_removedocument @.h
> Michael
> "Trillium" <Trillium@.discussions.microsoft.com> wrote in message
> news:5ADF3F9D-BF96-4211-9BE0-44E5F27AEB1D@.microsoft.com...
>
>

Dataset query with alias column and allow searches

I have a form that loads a dataset. This dataset is composed from SQL statements using alias and unions. Basically it takes uses data from 3 tables. This dataset also has a alias column called ClientName that consists of either people's name or business name.
In addition, the form also consist of a search field that allows user to enter the 'ClientName' to be searched (i.e. to search the alias column). So, my question is how can the alias column be searched (user can also enter % in the search field)

Function QueryByService(ByVal searchClientNameText As String) As System.Data.DataSet

If InStr(Trim(searchClientNameText), "%")>0 Then
searchStatement = "WHERE ClientName LIKE '" & searchClientNameText & "'"
Else
searchStatement = "WHERE ClientName = @.searchClientNameText"
End If

Dim queryString As String = "SELECT RTrim([People].[Given_Name])"& _
"+ ' ' + RTrim([People].[Family_Name]) AS ClientName, [Event].[NumEvents],"& _
"[Event].[Event_Ref]"& _
"FROM [Event] INNER JOIN [People] ON [Event].[APP_Person_ID] = [People].[APP_Person_ID]"& _
searchStatement + " "& _
"UNION SELECT [Bus].[Organisation_Name],"& _
"[Event].[NumEvents], [Event].[Event_Ref]"& _
"FROM [Bus] INNER JOIN [Event] ON [Bus].[APP_Organisation_ID] = [Event].[APP_Organisation_ID] "& _
searchStatement

........
End Function

(1) You would search on each of the columns that comprise the "ClientName".
searchValue = "%" & searchvalue & "%"

Dim queryString As String = "SELECT RTrim([People].[Given_Name])"& _
"+ ' ' + RTrim([People].[Family_Name]) AS ClientName, [Event].[NumEvents],"& _
"[Event].[Event_Ref]"& _
"FROM [Event] INNER JOIN [People] ON [Event].[APP_Person_ID] = [People].[APP_Person_ID]"& _
searchStatement + " "& _
"UNION SELECT [Bus].[Organisation_Name],"& _
"[Event].[NumEvents], [Event].[Event_Ref]"& _
"FROM [Bus] INNER JOIN [Event] ON [Bus].[APP_Organisation_ID] = [Event].[APP_Organisation_ID] "& _
WHERE
([People].[Given_Name] IS NULL OR [People].[Given_Name] LIKE @.searchvalue)
OR ([People].[Family_Name] IS NULL OR [People].[Family_Name] LIKE @.searchvalue)
OR ( [Bus].[Organisation_Name] IS NULL OR [Bus].[Organisation_Name] LIKE @.searchvalue)
(2) You should be using Parameterized Query instead of hardcoding the values into the SQL Statement to prefent your sever from SQL Injection attack. Google for more info.

|||I don't see any reason why you should not go for a stored procedure for this type of situation. I would highly recommend that.
Thanks

DataSet Performance

Hi!
I've a DataTable with 100 rows and 100 columns. Then I'm updating each
column in each row. When I call the Update method on the DataAdapter, this
generates 10000 UPDATE statements.
Are there any solutions how I can create a better solution, which reduces
the UPDATE statements? Or how can I handle such big updates with SQL Server?
Thanks
Klaus Aschenbrenner
MVP Visual C#
www.csharp.at, www.anecon.com
http://weblogs.asp.net/klaus.aschenbrennerUse a stored procedure to do the update. This sounds very much like you
are using a table as an array though, which isn't generally a good way
to model data in SQL.
--
David Portas
SQL Server MVP
--|||I've already tried it with stored procedures and the performance isn't
better.
The problem on the data model is that the 100 rows and 100 columns are
representing a fincance plan. So each column must save additional
information (like formats, formula, ...).
So I have a table for each row (called "Position") and this table references
another table which stores the columns (called "PosVal") of the row. With
this data model I've the possibility that the table "PosVal" can reference
other tables which contains the format, formulas...
Or this there any other way to model this?
Thanks
Klaus Aschenbrenner
MVP Visual C#
www.csharp.at, www.anecon.com
http://weblogs.asp.net/klaus.aschenbrenner
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1109333983.806317.283120@.g14g2000cwa.googlegroups.com...
> Use a stored procedure to do the update. This sounds very much like you
> are using a table as an array though, which isn't generally a good way
> to model data in SQL.
> --
> David Portas
> SQL Server MVP
> --
>|||It seems like you are trying to model an abstraction ("rows", "columns"
and "formulae" from a hypothetical spreadsheet) instead of modelling
the actual data. Isn't your metadata static enough to create a proper
relational representation of it? If not then I suggest you need a
middle tier to present this data. The back end may be largely
irrelevent - I'm not sure just what benefit you are hoping to get from
using SQL Server as the data store for this.
If you do have some real data to model, then A) Normalize your tables,
B) post a CREATE TABLE statement and your stored proc. Since your proc
can update an entire row at a time I would have expected 100 updates to
outperform 10,000 but that largely depends on how you are doing the
updates and what your data looks like.
--
David Portas
SQL Server MVP
--|||Klaus Aschenbrenner wrote:
> Hi!
> I've a DataTable with 100 rows and 100 columns. Then I'm updating each
> column in each row. When I call the Update method on the DataAdapter,
> this generates 10000 UPDATE statements.
> Are there any solutions how I can create a better solution, which
> reduces the UPDATE statements? Or how can I handle such big updates
> with SQL Server?
>
Are you using the CommandBuilder to generate the code? It probably makes
more sense to write the code yourself.
You stated in a subsequent reply that you created a stored procedure to do
the update but that it did not improve performance. Could you elaborate on
what the procedure did? I'm assuming you created a procedure that accepted
parameters for each of the 100 columns and did the update for an entire row
at a time, requiring 100 calls to the procedure instead of 10000 calls to a
procedure that did 1 column at a time...
Is that correct?
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Klaus,
You can use SQLXML that comes with MDAC to reduce the number of hits going
to your database. You can then send an updategram. With 2.0, you have a
graceful upgrade to the Managed SQLXML driver, so it's not a deadend.
This is obviously SQL Server specific, but Oracle has other such equivalent
solutions.
- Sahil Malik
http://codebetter.com/blogs/sahil.malik/
"Klaus Aschenbrenner" <Klaus.Aschenbrenner@.anecon.com> wrote in message
news:#hBwlNzGFHA.2976@.TK2MSFTNGP15.phx.gbl...
> Hi!
> I've a DataTable with 100 rows and 100 columns. Then I'm updating each
> column in each row. When I call the Update method on the DataAdapter, this
> generates 10000 UPDATE statements.
> Are there any solutions how I can create a better solution, which reduces
> the UPDATE statements? Or how can I handle such big updates with SQL
Server?
> Thanks
> Klaus Aschenbrenner
> MVP Visual C#
> www.csharp.at, www.anecon.com
> http://weblogs.asp.net/klaus.aschenbrenner
>|||First and foremost, an RDBMS is NOT FOR PERFORMANCE. You gain performance
by using the tool correctly and using the native enhancements to boost
performance. That is not to say that an RDBMS can not be fast, on the
contrary; however, that is not its chief purpose.
If performance is your ONLY concern, use a flat file or an XML file.
You use an RDBMS to MODEL THE DATA, so that others can query it in a myriad
of ways and garauntee that there results are accurate. Therefore, you have
to use the RELATIONAL rules to model your data before you build the physical
database and constrain it in order to provide DATA INTEGRITY. It is this
integrity that you build on an RDBMS system. The system is optimized for
performance, but only after providing the foundation, a relational database
properly constrained.
Sincerely,
Anthony Thomas
"Klaus Aschenbrenner" <Klaus.Aschenbrenner@.anecon.com> wrote in message
news:%23hBwlNzGFHA.2976@.TK2MSFTNGP15.phx.gbl...
Hi!
I've a DataTable with 100 rows and 100 columns. Then I'm updating each
column in each row. When I call the Update method on the DataAdapter, this
generates 10000 UPDATE statements.
Are there any solutions how I can create a better solution, which reduces
the UPDATE statements? Or how can I handle such big updates with SQL Server?
Thanks
Klaus Aschenbrenner
MVP Visual C#
www.csharp.at, www.anecon.com
http://weblogs.asp.net/klaus.aschenbrenner|||You must be a consultant. :)
- Sahil Malik
http://codebetter.com/blogs/sahil.malik/
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:OIk#AyDHFHA.3352@.TK2MSFTNGP10.phx.gbl...
> First and foremost, an RDBMS is NOT FOR PERFORMANCE. You gain performance
> by using the tool correctly and using the native enhancements to boost
> performance. That is not to say that an RDBMS can not be fast, on the
> contrary; however, that is not its chief purpose.
> If performance is your ONLY concern, use a flat file or an XML file.
> You use an RDBMS to MODEL THE DATA, so that others can query it in a
myriad
> of ways and garauntee that there results are accurate. Therefore, you
have
> to use the RELATIONAL rules to model your data before you build the
physical
> database and constrain it in order to provide DATA INTEGRITY. It is this
> integrity that you build on an RDBMS system. The system is optimized for
> performance, but only after providing the foundation, a relational
database
> properly constrained.
> Sincerely,
>
> Anthony Thomas
>
>
> --
> "Klaus Aschenbrenner" <Klaus.Aschenbrenner@.anecon.com> wrote in message
> news:%23hBwlNzGFHA.2976@.TK2MSFTNGP15.phx.gbl...
> Hi!
> I've a DataTable with 100 rows and 100 columns. Then I'm updating each
> column in each row. When I call the Update method on the DataAdapter, this
> generates 10000 UPDATE statements.
> Are there any solutions how I can create a better solution, which reduces
> the UPDATE statements? Or how can I handle such big updates with SQL
Server?
> Thanks
> Klaus Aschenbrenner
> MVP Visual C#
> www.csharp.at, www.anecon.com
> http://weblogs.asp.net/klaus.aschenbrenner
>|||Klaus,
The rows are updated depending on the rowstate.
It can be that when you start that the rowstate are set to "added" while you
don't want to update them all. (The dataadapter.fill set them automaticly
to unchanged when you have not set the property for that to false, however
when you load them by hand, by instance using a datareader they are all set
to "added".).
You can by instance in the case of that filling with the datareader set the
rowstate of all rows to unchanged by ds.acceptchanges
Maybe this helps?
Cor|||If only consultants would be so highly critical.
Sincerely,
Anthony Thomas
"Sahil Malik" <contactmethrumyblog@.nospam.com> wrote in message
news:%23Phk7IcHFHA.2984@.TK2MSFTNGP15.phx.gbl...
You must be a consultant. :)
- Sahil Malik
http://codebetter.com/blogs/sahil.malik/
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:OIk#AyDHFHA.3352@.TK2MSFTNGP10.phx.gbl...
> First and foremost, an RDBMS is NOT FOR PERFORMANCE. You gain performance
> by using the tool correctly and using the native enhancements to boost
> performance. That is not to say that an RDBMS can not be fast, on the
> contrary; however, that is not its chief purpose.
> If performance is your ONLY concern, use a flat file or an XML file.
> You use an RDBMS to MODEL THE DATA, so that others can query it in a
myriad
> of ways and garauntee that there results are accurate. Therefore, you
have
> to use the RELATIONAL rules to model your data before you build the
physical
> database and constrain it in order to provide DATA INTEGRITY. It is this
> integrity that you build on an RDBMS system. The system is optimized for
> performance, but only after providing the foundation, a relational
database
> properly constrained.
> Sincerely,
>
> Anthony Thomas
>
>
> --
> "Klaus Aschenbrenner" <Klaus.Aschenbrenner@.anecon.com> wrote in message
> news:%23hBwlNzGFHA.2976@.TK2MSFTNGP15.phx.gbl...
> Hi!
> I've a DataTable with 100 rows and 100 columns. Then I'm updating each
> column in each row. When I call the Update method on the DataAdapter, this
> generates 10000 UPDATE statements.
> Are there any solutions how I can create a better solution, which reduces
> the UPDATE statements? Or how can I handle such big updates with SQL
Server?
> Thanks
> Klaus Aschenbrenner
> MVP Visual C#
> www.csharp.at, www.anecon.com
> http://weblogs.asp.net/klaus.aschenbrenner
>

DataSet Access from a matrix

Data Set Access from a matrix

I am trying to create a schedule by room number report.I am using a matrix.The column group that I am using is room number.There is no row group.When you group by room it is only allowing me access to the first data item for that room.Even though there are other data items for that room I can not access them.I can not access all data items in that grouping expression.Is there a way to get unobstructed access to a dataset while working in a matrix and grouping?

If you are not getting back the correct data... I would use a table format and grouping... you can "trick" the look of the report to appear like a matrix..

Hope this helps..

sql

DataRow syntax

command.CommandText = "SELECT UserName from Users WHERE UserID = " = userID

Executing this command returns one table with one column with one row. What is the syntax for getting that value into a variable? I can get the information into a dataSet but I can't get it out. Should I be using a dataSet for this operation?

The rest of the code so far:

SqlDataAdapter dataAdapter =newSqlDataAdapter();

dataAdapter.SelectCommand = command;

dataAdapter.TableMappings.Add("Table","Users");

dataSet =newDataSet();

dataAdapter.Fill(dataSet);

Using that code, your data would be in dataSet.tables[0].rows[0][0].

If that's always just returning one value, you might want to look into using ExecuteScalar instead of the adapter and dataset.

|||

You can just use executeScalar method of SQl command below is example from VB.Net help for scalar modified a little:

Public Function AddProductCategory( _ ByVal UserID As Integer, ByVal connString As String) As Integer Dim Username As string = "" Dim sql As String = "SELECT UserName from Users WHERE UserID = @.USERID" Using conn As New SqlConnection(connString) Dim cmd As New SqlCommand(sql, conn) cmd.Parameters.Add("@.USERID", SqlDbType.Int) cmd.Parameters("@.USERID").Value = newName Try conn.Open() userName = Convert.ToInt32(cmd.ExecuteScalar()) Catch ex As Exception Console.WriteLine(ex.Message) End Try End Using Return newProdIDEnd Function

Thanks

|||

Got it working ... thank you for your help!

DataReader Source and Column Types

Is there a way to control the types for output columns of a DataReader Source? It appears that any System.String will always come out as DT_WSTR. As I have my own managed provider, and I know what went in, I can say that really it should be DT_STR. The GetSchemaTable call from my provider will always say System.String as it does not have much choice, but GetSchemaTable does contain a ProviderType which is different for my DT_STR vs DT_WSTR, or rather when I want each. I think something like MappingFiles as used by the Wizard would work, but can I do anything today?

Darren,

I am afraid there is no way to influence this mapping. The Data Reader Source adapter uses only the CLR type (the DataType column from the table's schema) to determine which DT_... type to choose. The ProviderType field could not be used as it has different meaning for different providers.

The mapping files would definitely help here, but that infrastructure is not used by this component.

I do not have any good advice, but explicit data conversion to the DT_STR type or building your custom ADO .NET adapter are options I see available at this moment.

Thanks.

|||

Bob,

Thanks for confirming what I already suspected, but I had to ask. I'm thinking a MSDN feedback request for the ability to supply mapping files would be coming your way.

Conversion works, but I'm concerned about the impact of doubling the buffer size each time I do this. Most columns I am working with are DT_WTR, but need to be DT_STR, it is 2 x buffer every time. Is this really twice the size or is there some fancy pointer type work going on? I will probably test when I have time as I had some other ideas about custom components for such conversion, but it depends on what the impact really is.

A custom provider had been considered but currently rejected due to time. It took me long enough to get the managed provider written :)

Thanks

|||

Hi Darren,

I believe you are right about the buffer size. It might impact your package performance, but it is not sure how significant that could be. It may depend on many factors. If you get a chance to measure the impact in your configuration, please share results with us.

If the "power" stays with us, we should be able to provide much better story with managed providers in the next version.

Thanks.

|||

I have done some playing around with this.

For information my theory goes like this. If I use a Data Conversion transform I am increasing the number of columns in my buffer so I get less rows per buffer. This seems inefficient. On the other side we know that copying data between buffers has a cost. So lets test which is more efficient, the larger row size versus the cost of moving between buffers, and keeping a small row size.

I wrote a simple asynchronous component that allowed you to select columns from the input buffer which are then copied directly to the output buffer. The one feature is that any DT_WSTR column is reproduced as DT_STR. So the buffer sizes/structure are the same for input and output except for the change in type, and any associated overheads of each type. One would think that unicode types require twice the space of non-unicode, so this should make the asynchronous component test even faster as this allows even more rows to fit into the output buffer of my component.

For a baseline I used a Script Component -> Union All. The script component generated a variable number of rows, as determined by a package variable. The columns produced are 1 integer column (row count), and 9 x 50 character DT_WSTR columns fully populated.

For testing I used the same script component and two methods of converting the columns -

Script Component -> Data Conversion -> Union All

Script Component -> DeUnicodeAsynchTestComponent -> Union All

Tests showed that the data conversion was 1.5-2.5 times slower than the baseline. The asynchronous component was then 2-2.5 times slower than the data conversion. Times were averaged across 6 executions. The range in times are for different row counts, 100,000 to 1,000,000.

N.B. These ratios are for my local machine, and I would fully expect results to vary on different hardware and with different resource constraints. These are for illustration only. If you want to know how this equates to your environment, test it for yourself, and use real hardware, not a test system.

So, whilst it may not look pretty leaving the buffer alone is the way to go. Trying to remove columns or change columns is a non-starter as this means creating a new buffer, the cost of which far outweighs the benefit of the smaller row size in the buffer. When you do need to work on columns, use a synchronous component such as the Data Conversion or Derived Column transformations, and don’t worry if you end up with more columns that you will use at the end. (Obviously don’t create columns for the sake of it!)

DataReader output column length

Hello,

I have an ODBC connection manager to a Progress database. In that database there is a column declared as a string of 10 characters long.
However, some data in this column is actually up to 15 characters long.
This makes my DataReader Source fail everytime I try to run my package because it sets the output column like this :

Datatype : Unicode string [DT_WSTR]
Length : 10

Is there any way to solve this without changing the datatype in the Progress database (that is beyond my control) ?

tanks in advance ...

What are you talking about? How can you have a column declared with a width of 10 and have data that exceeds that length? You might want to double check your source metadata.|||

renyx wrote:

In that database there is a column declared as a string of 10 characters long.
However, some data in this column is actually up to 15 characters long.

That is a physical impossibility.

Can you expand more on what you mean?

-Jamie

|||

In Progress a column can be declared as Char(10) while creating the table, but while inserting in the table, a value larger than 10 can be inserted without causing an error.

So I am looking for a way to make the DataReader output column 15 in length.

|||

renyx wrote:

In Progress a column can be declared as Char(10) while creating the table, but while inserting in the table, a value larger than 10 can be inserted without causing an error.

So I am looking for a way to make the DataReader output column 15 in length.

Really? OK, I take it back. Sorry. That's just....bizarre....for want of a better word.

I think you'll be able to go into the Advanced Editor of the Datareader Source and manually edit the column lengths.

|||

Jamie Thomson wrote:

Really? OK, I take it back. Sorry. That's just....bizarre....for want of a better word.

I think you'll be able to go into the Advanced Editor of the Datareader Source and manually edit the column lengths.

I just looked into this some... Progress 4GL does not use the width definition for the storage of data. Character data types in Progress 4GL can be up to 2,000 characters, I believe. The char(10) definition that the OP mentioned is for query results, I believe, and not for storage.

So, how to get around this? Well, do as Jamie suggested and edit the Datareader Source manually using the advanced editor.|||

Thanks for the suggestion, but that did not work.
I also tried a query on the column in management studio (linked server) and that also did not work.

OLE DB provider "MSDASQL" for linked server "LISA" returned message "[DataDirect][ODBC OPENEDGE driver][OPENEDGE]Column KM4-CODE in table PUB.ARTIKEL has value exceeding its max length or precision.".

Looks like I will have to convince the Progress people to change the datatype.

|||This is a well known Progress problem. Check out http://www.progresstalk.com/archive/index.php?t-76301.html|||By the way, I'm just going on record to say that the Progress leaders should be sent back to logic school for designing a product that enforces the data length on a one-way basis. "Um yeah, we let you store data greater than what's defined, but we won't let you select it back out."

That's the most ridiculous thing I have heard in a long time.|||Same issue happens to me. We import data from a Thoroughbred basic database. The field can be 12 chars long with 20 chars of data. In the Thoroughbred world, this is just an "integrity" problem....Their db still works somehow, but causes fits on our end.|||

If anyone out there wants a humorous outlook on this (and promises not to take offence to SQL zealots), go here: http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=263&messageid=340643

-Jamie

|||

Thoroughbred still lives? Aaargh! The only implementation of a computer language I ever saw that allowed indefinite GOTOs (I'm not kidding). I had not heard about this "integrity" issue with their DB, but am not surprised.

|||

renyx wrote:

Thanks for the suggestion, but that did not work.
I also tried a query on the column in management studio (linked server) and that also did not work.

OLE DB provider "MSDASQL" for linked server "LISA" returned message "[DataDirect][ODBC OPENEDGE driver][OPENEDGE]Column KM4-CODE in table PUB.ARTIKEL has value exceeding its max length or precision.".

Looks like I will have to convince the Progress people to change the datatype.

Renyx, did you try changing the SqlCommand select statement to cast the column to something longer?
As you noted, you cannot change the column length in the advanced editor, but doing so in the select statement might work for you.

datalength doubling values

Am I missing something? I'm trying to return the size of the data contained in a varbinary(max) column, however it appears that the value being returned is double what it should be. Is this normal, or is there something else I need to do?

Thanks,

Devin

Edit: I'm not discounting that my data may be weird, but I wanted to cast my net as wide as possible.

Perhaps the field contains trailing blanks...|||

Why do you think this is double? Can you post a snippet of code that doesn't seem to make sense? Like:

set nocount on

declare @.test varbinary(max)

set @.test = 0x12

select datalength(@.test)

set @.test = 0x1234

select datalength(@.test)

Returns:

--

1

--

2

|||

Ignore this. I was using compression on the streams as I was putting them into the column. Apparently the framework's GZipStream class has a bug that causes it to mishandle files that already have compression in them (video, jpg, pdf) so that they end up larger.

DATALENGTH was reporting the correct size for the contents of the column.

Thanks,

Devin

DataFlow Task & Filters

Hi,

I am getting data from an external source. External data has a column called "Type". I have a variable in my package which contains the list of types as shown below:

Filtered_type_List = 2,4,8,10,11

If this variable(Filtered_type_List) is blank, then I need all the data from the external source and if it is not blank then I only need the records matching to his list. How can I implement this in DataFlow Task?

Thanks

You could do this in an expression. Something like:

"SELECT * FROM MyTable " + (LEN(MySSISVariable) != 0 ? "WHERE MyColumn IN (" + MySSISVariable + ")" : "" )

That expression will (I think) add a WHERE clause if the length of the string inside the variable (which I have called MySSISVariable) is not zero.

HTH

-Jamie

|||

Hi Jamie,

Where should I put this "Select" statement,

1. Source using SQL Command as variable using OLE DB Source or

2. Lookup transformation

Thanks

|||

OLE DB Source. Set it to 'SQL Command from variable' and paste the expression that I provided above into the variable expression. The variable will require EvaluateAsExpression=TRUE.

-Jamie