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

DataTable.select method

helloo

Can I use "like" in datatable.select method?

meaning:

Dim exp As String = "c.cDesc like " & txtSearch.Text & " + N'%' "
Dim rows() As DataRow = dtCenters.Select(exp)

knowing that txtSearch.text has unicode characters

Yes. However, your expression does not include quotes so you are probably not sending what you think you are sending.

Dim exp As String = "c.cDesc like '" & txtSearch.Text & "' + N'%' "

The better option is to use a parameter and set the value of the parameter to your textbox.

sql

Datasource Webservice

I test Webservices with the SSRS 2005 and do not get ahead.

Connectionstring: http://www.webservicex.net/WeatherForecast.asmx
Query String: <Query><SoapAction>http://www.webservicex.net/GetWeatherByPlaceName</SoapAction></Query>

I get an error. What is wrong?
Additionally I would like to use parameters. For example Houston, Dallas, New York.

Can someone send an example with this Web service to me?

Willfried

I just tried this using IE and got:
"The underlying connection was closed: The server committed an HTTP protocol violation."

You should contact the author of the web service to request a fix.

-Lukasz

Datasource Connection string

Is there option available to dynamically change the connection string
properties of a published report?
--
Thanks
SaranYou could do that through the SOAP API by calling SetDataSourceContents:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_ref_soapapi_service_lz_2ojd.asp
Other approaches for dynamic database connections in RS 2000 have also been
discussed on this newsgroup. E.g.:
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=b1a2f8b8-457c-4424-9cfd-2c8269734898&sloc=en-us
FYI: RS 2005 will allow expression-based connection strings.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"PTS" <PTS@.discussions.microsoft.com> wrote in message
news:765E3C18-186B-44AB-AA83-A5BA1EC1BEEE@.microsoft.com...
> Is there option available to dynamically change the connection string
> properties of a published report?
> --
> Thanks
> Saran

datasource "on-the-fly"

Hi,
Is it possible to change a datasource (or its connection string) in the
moment of report running?
Thanks.On SQL 2000, the only way is to create an SP which uses linked servers based
on the parameter you pass ie
create proc getdata @.source varchar(10)
as
if @.source = 'a'
select ... from mylinkedserver1.db.dbo.table
else
if @.source
With SQL 2005 you can use a dynamic data source based on a parameter ie. In
the connection string
="data source=" &Parameters!ServerName.Value & ";initial
catalog=AdventureWorks
Hope this helps
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Vladimir Evdokimov" <evesq@.uk2.net> wrote in message
news:OCvqf0kzFHA.2008@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Is it possible to change a datasource (or its connection string) in the
> moment of report running?
> Thanks.
>|||Hello
In SQL 2000 is it possible to supply the datasource (database name etc) as
part of the arguments supplied in the URL?
My application runs off multiple databases corresponding to different
customers and I need to switch the datasource based on the customer user
calling the report.
Any ideas on the best way to accomplish this task in SQL 2000?
Thank you|||Although you can do this with RS 2000 it will be easier in 2005.
In 2000 you have to use dynamic SQL. Use generic query designer (button to
switch to this is to the right of the ...)
declare @.SQL varchar(255)
select @.SQL = 'select table_name from ' + @.Database +
'.information_schema.tables order by table_name'
exec (@.SQL)
Note that @.Database should cause a report parameter called this to be
created. Anyway, this is the concept.
In 2005 you will be able to have dynamic datasources. From 2005 help:
Data Source Expressions
You can put an expression into a connection string to allow users to select
the data source at run time. For example, suppose a multinational firm has
data servers in several countries. With an expression-based connection
string, a user who is running a sales report can select a data source for a
particular country before running the report.
The following example illustrates the use of a data source expression in a
SQL Server connection string. The example assumes you have created a report
parameter named ServerName:
Copy Code
="data source=" &Parameters!ServerName.Value & ";initial
catalog=AdventureWorks
Data source expressions are processed at run time or when a report is
previewed. The expression must be written in Visual Basic. Use the following
guidelines when defining a data source expression:
>>>>>>>>
a.. Design the report using a static connection string. A static
connection string refers to a connection string that is not set through an
expression (for example, when you follow the steps for creating a
report-specific or shared data source, you are defining a static connection
string). Using a static connection string allows you to connect to the data
source in Report Designer so that you can get the query results you need to
create the report.
b.. When defining the data source connection, do not use a shared data
source. You cannot use a data source expression in a shared data source. You
must define a report-specific data source for the report.
c.. Specify credentials separately from the connection string. You can use
stored credentials, prompted credentials, or integrated security.
d.. Add a report parameter to specify a data source. For parameter values,
you can either provide a static list of available values (in this case, the
available values should be data sources you can use with the report) or
define a query that retrieves a list of data sources at run time.
e.. Be sure that the list of data sources share the same database schema.
All report design begins with schema information. If there is a mismatch
between the schema used to define the report and the actual schema used by
the report at run time, the report might not run.
f.. Before publishing the report, replace the static connection string
with an expression. Wait until you are finished designing the report before
you replace the static connection string with an expression. Once you use an
expression, you cannot execute the query in Report Designer. Furthermore,
the field list in the Datasets window and the Parameters list will not
update automatically.
>>>>>>>
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"GavinMc" <GavinMc@.discussions.microsoft.com> wrote in message
news:F7640227-35C6-44CB-89BD-ED283ED242A4@.microsoft.com...
> Hello
> In SQL 2000 is it possible to supply the datasource (database name etc) as
> part of the arguments supplied in the URL?
> My application runs off multiple databases corresponding to different
> customers and I need to switch the datasource based on the customer user
> calling the report.
> Any ideas on the best way to accomplish this task in SQL 2000?
> Thank you
>|||Thanks Bruce, unfortunately I'm stuck with SQL 2000 for the moment...
Just a further enquiry based on the above. To allow for the switching of
databases on the fly for my dataset based on an entered parameter, I created
the following sql:
USE master
declare @.SQL varchar(255), @.mydb varchar(255)
set @.mydb= (SELECT dbID FROM co WHERE (coID = @.co))
select @.SQL='USE ' + @.mydb + ' SELECT accid, accno, accname FROM acc'
exec (@.SQL)
This reads a user defined 'co' table in master that contains a reference to
my target databases, When running a report I then supply a 'coID' to retrieve
the reference to the correct database and this is then used as a variable in
report's underlying dataset to ensure that the correct database is used. I
think this is along the lines of what you suggested above.
My question is whether the select statement needs to be built up as a string
for execution, this will obviously require rewriting all my existing plain
sql statements and inserting concatenation operators around the parameters
etc.
Is there some way that I can declare a parameter for the database I want to
run the report off, and include this at the start of existing sql statements
in the 'use' statement without explicitly declaring a sql str variable?

Dataset Parameters and Expressions in Report Connection String

I am working with RS 2005 and have run into a problem with passing a server and database name as parameters into a server report.

Here's what the connection string looks like in these reports:

="Data Source = " & Parameters!ServerName.Value & ";Initial Catalog=" & Parameters!DBName.Value

I have default values set for both parameters, so testing usually works fine also. If I run a report with "normal" parameters or no parameters, the report runs fine with the supplied connection string.

The problem occurs when I try to add a parameter to the report that uses a dataset to populate a list of choices. I get the following error when trying to run a report in this situation:

"Error during processing of the ConnectString expression of datasource 'dbConnection'"

I don't get any build errors, just the message above in the report canvas.

Any direction/assistance anyone can provide would be greatly appreciated.. thanks in advance.

Very simple solution, it turns out... just make sure the ServerName and DBName parameters appear above all other parameters in the report definition.

dataset connection string defined by a parameter?

Is there any way to dynamically set the connection string for a dataset
based on the value of a parameter?
Would I have to write a custom data extension to accomplish this?
thanks, AndrewHello,
Datasource connection string is not part of your report. It is provided by
ReportServer.
So, datasource is not aware about parameter's reports.
Jerome BERTHAUD MCSD, MCT
http://www.winsight.fr
"Andrew" <nospam@.nospam.com> wrote in message
news:#m8p1veWEHA.1144@.TK2MSFTNGP10.phx.gbl...
> Is there any way to dynamically set the connection string for a dataset
> based on the value of a parameter?
> Would I have to write a custom data extension to accomplish this?
> thanks, Andrew
>|||Hi Jerome,
Thanks for the info. So basically, it would be impossible to set the
connection string from within the designer based on a parameter.
However, wouldn't it still be possible to get hold of the parameters
collection within a custom data extension and use that to determine the
connection string prior to actually querying the datasource?
Thanks, Andrew
"Jerome BERTHAUD" <jerome.berthaud@.winsight.fr> wrote in message
news:uigOlEfWEHA.1656@.TK2MSFTNGP09.phx.gbl...
> Hello,
> Datasource connection string is not part of your report. It is provided by
> ReportServer.
> So, datasource is not aware about parameter's reports.
> Jerome BERTHAUD MCSD, MCT
> http://www.winsight.fr
> "Andrew" <nospam@.nospam.com> wrote in message
> news:#m8p1veWEHA.1144@.TK2MSFTNGP10.phx.gbl...
> > Is there any way to dynamically set the connection string for a dataset
> > based on the value of a parameter?
> >
> > Would I have to write a custom data extension to accomplish this?
> >
> > thanks, Andrew
> >
> >
>|||I have released a DPE that achiveves this. It can be downloaded at
http://workspaces.gotdotnet.com/appworld
Regards
Toby
"Andrew" <nospam@.nospam.com> wrote in message
news:%23m8p1veWEHA.1144@.TK2MSFTNGP10.phx.gbl...
> Is there any way to dynamically set the connection string for a dataset
> based on the value of a parameter?
> Would I have to write a custom data extension to accomplish this?
> thanks, Andrew
>

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.

DataReader not reading

Why won't this dataReader read?

Dim objCon2 As New SqlConnection()
objCon2.ConnectionString = "a standard connection string"
objCon2.Open()

Dim objCommand As SqlCommand
objCommand = New SqlCommand(strSQL, objCon2)
Dim objReader As SqlDataReader
objReader = objCommand.ExecuteReader()

Label1.Text = objReader("email")

strSQL is a select command which I've checked (using SQL Query analyzer) does return data. I know the connection string is valid (and I presume if it wasn't that it'd fail on objCon2.open, which it doesn't).

So why oh why do I get this error on the last line (and yes, there is an "email" field in the contents of the reader)

System.InvalidOperationException: Invalid attempt to read when no data is present.You have to move the "read head" to the start of a record by calling objReader.Read(). You do this each time you want to move to the next record. Since in your case there is presumably only one record you only need to call it once.

objReader = objCommand.ExecuteReader()
if objReader.Read()
 Label1.Text = objReader("email")
End If
' be sure to close and the data reader afterwards
objReader.Close()

Dataconversion: STRING (yyyymmdd) to DATE

Hi All,

I need a solution for the following:

I have a field with data type string, length 8, in the form yyyymmdd (f.e. 20070604).

Now I need to transfer this field into a field with data type DATE.

I know the function DATESERIAL in MS ACCESS but what is the equivalent function in TSQL?

Any hint for me is very

Thanks in Advance

ulrike

You just need to make sure DATEFORMAT is set to match the incoming data and then perform a conversion.

Code Snippet

setDATEFORMAT ymd

selectconvert(datetime,'20070604')as d1,cast('20070604'asdatetime)as d2

|||

You could use CONVERT function with 112 style:

Code Snippet

declare @.dt varchar(20)

set @.dt ='20070507'

selectconvert(datetime,@.dt,112)

|||

Hello again,

Thanks for your advise.

I tried to run the following code:

selectconvert(datetime,whuser.[tbl_source].EffectiveDate_str,112)as EffectiveDate_dt

into WHUser.[tbl_target]

from WHUser.[tbl_source]

BUT it terminated with error:

“The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value”.

What’s going wrong? Please give me some mor advise.

Thanks in advance,

ulrike

|||

Hi ulrikeG,

SQL Server will convert that value implicitly without any problem. You are using the ISO format and SQL Server will interprete it corrrectly, no matter the language or settings of dateformat being used.

Code Snippet

create table dbo.t1 (

c1 char(8) null,

c2 datetime null

)

go

insert into dbo.t1(c1) values('20070606')

go

select * from dbo.t1

go

update dbo.t1

set c2 = c1

go

select * from dbo.t1

go

drop table dbo.t1

go

AMB

|||

ulrikeG wrote:

“The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value”.

Maybe there's bad data in your table.

Try running a query WHERE IsDate (string) = 0.

|||Change your code to:

Code Snippet

select CASE WHEN ISDATE(whuser.[tbl_source].EffectiveDate_str) = 1 THENconvert(datetime,whuser.[tbl_source].EffectiveDate_str,112) ELSE NULL ENDas EffectiveDate_dt

into WHUser.[tbl_target]

from WHUser.[tbl_source]

Then:
SELECT * FROMWHUser.[tbl_target] WHERE EffectiveDate_dt IS NULL
to find the errors