How to connect to ODBC data source through C#. net

Hi experts,
I am doing an application which takes the DSN name from user in text
box and then open the ODBC data source dialog box. i.e. I want the
(ODBC data source administrator) form which comes after clicking on
ODBC data source icon in administrative tools.
Now my problem is that is it possible to call that system form
(utility) through my program or I have to design the same form (ODBC
data source administrator).
If I can call that form then please tell me how....i can do that?
Any help will be appreciated.
Thanks
DineshYou would be better off posting this in a C# newsgroup. This is for
Reporting Services.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Dinesh" <dinesht15@.gmail.com> wrote in message
news:1166091132.509799.4510@.f1g2000cwa.googlegroups.com...
> Hi experts,
> I am doing an application which takes the DSN name from user in text
> box and then open the ODBC data source dialog box. i.e. I want the
> (ODBC data source administrator) form which comes after clicking on
> ODBC data source icon in administrative tools.
> Now my problem is that is it possible to call that system form
> (utility) through my program or I have to design the same form (ODBC
> data source administrator).
> If I can call that form then please tell me how....i can do that?
> Any help will be appreciated.
> Thanks
> Dinesh
>

How to configure Email Delivery to send out message in Plain/Text format?

Hi, guys,
I have an application which only accept Plain/Text format email, but the
email sent out by Reporting Services Email Delivery was wrap in HTML format.
Is it possible to configure Email Delivery to send out Plain/Text format
message?
Many thanks!
David ZengThe reports server should populate both the plain text and html parts of the
message. Are you saying that you can not have the html portion present? If
so then RS does not support this. You could have RS drop the files to a
local share and then have an app which stripped out the html part before
sending the message on.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"David Zeng" <dzeng@.pembrooke.com> wrote in message
news:#K9xIQqpEHA.3424@.TK2MSFTNGP12.phx.gbl...
> Hi, guys,
> I have an application which only accept Plain/Text format email, but the
> email sent out by Reporting Services Email Delivery was wrap in HTML
format.
> Is it possible to configure Email Delivery to send out Plain/Text format
> message?
> Many thanks!
> David Zeng
>|||Daniel,
That's right! I do not want the HTML portion present.
Are there any ways to configure Reporting Services to export report into CSV
format without header?
I am in the middle of one urgent project. I very much appreciate your help!
Thanks,
David Zeng
"Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
news:uBIS04vpEHA.3716@.TK2MSFTNGP10.phx.gbl...
> The reports server should populate both the plain text and html parts of
the
> message. Are you saying that you can not have the html portion present?
If
> so then RS does not support this. You could have RS drop the files to a
> local share and then have an app which stripped out the html part before
> sending the message on.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "David Zeng" <dzeng@.pembrooke.com> wrote in message
> news:#K9xIQqpEHA.3424@.TK2MSFTNGP12.phx.gbl...
> > Hi, guys,
> >
> > I have an application which only accept Plain/Text format email, but the
> > email sent out by Reporting Services Email Delivery was wrap in HTML
> format.
> > Is it possible to configure Email Delivery to send out Plain/Text format
> > message?
> >
> > Many thanks!
> > David Zeng
> >
> >
>|||Daniel,
That's right! I do not want the HTML portion present.
Are there any ways to configure Reporting Services to export report into CSV
format without header?
I am in the middle of one urgent project. I very much appreciate your help!
Thanks,
David Zeng
"Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
news:uBIS04vpEHA.3716@.TK2MSFTNGP10.phx.gbl...
> The reports server should populate both the plain text and html parts of
the
> message. Are you saying that you can not have the html portion present?
If
> so then RS does not support this. You could have RS drop the files to a
> local share and then have an app which stripped out the html part before
> sending the message on.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "David Zeng" <dzeng@.pembrooke.com> wrote in message
> news:#K9xIQqpEHA.3424@.TK2MSFTNGP12.phx.gbl...
> > Hi, guys,
> >
> > I have an application which only accept Plain/Text format email, but the
> > email sent out by Reporting Services Email Delivery was wrap in HTML
> format.
> > Is it possible to configure Email Delivery to send out Plain/Text format
> > message?
> >
> > Many thanks!
> > David Zeng
> >
> >
>|||No, you can not change the way the Viewer renders the report on export, nor
set any device info for subscriptions. You could write a Delivery extension
fairly quickly that would call the renderer with what ever device info you
want. Then just have it drop it to a file share. Would this solve your
problem?
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"David Zeng" <dzeng@.pembrooke.com> wrote in message
news:#nukQ2wpEHA.3244@.tk2msftngp13.phx.gbl...
> Daniel,
> That's right! I do not want the HTML portion present.
> Are there any ways to configure Reporting Services to export report into
CSV
> format without header?
> I am in the middle of one urgent project. I very much appreciate your
help!
> Thanks,
> David Zeng
> "Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
> news:uBIS04vpEHA.3716@.TK2MSFTNGP10.phx.gbl...
> > The reports server should populate both the plain text and html parts of
> the
> > message. Are you saying that you can not have the html portion present?
> If
> > so then RS does not support this. You could have RS drop the files to a
> > local share and then have an app which stripped out the html part before
> > sending the message on.
> >
> > --
> > -Daniel
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >
> >
> > "David Zeng" <dzeng@.pembrooke.com> wrote in message
> > news:#K9xIQqpEHA.3424@.TK2MSFTNGP12.phx.gbl...
> > > Hi, guys,
> > >
> > > I have an application which only accept Plain/Text format email, but
the
> > > email sent out by Reporting Services Email Delivery was wrap in HTML
> > format.
> > > Is it possible to configure Email Delivery to send out Plain/Text
format
> > > message?
> > >
> > > Many thanks!
> > > David Zeng
> > >
> > >
> >
> >
>

How to concatenate two text columns

Hi all,
I have a two text columns in my table with more than 100,000 rows.
I want to create a third text column with the data from text column 1 + text
column 2.
Is there an easy way to concatenate two text fields?
Thanks
Raju
maybe you can export the 2 columns into excel
eg. first column is in cell A1, second column is in cell B1
at C1 you type this formula =A1&B1, then copy this formula till the end of
the row, then you import it back to your table.
Susanna
"Raju" wrote:

> Hi all,
>
> I have a two text columns in my table with more than 100,000 rows.
> I want to create a third text column with the data from text column 1 + text
> column 2.
>
> Is there an easy way to concatenate two text fields?
>
> Thanks
> Raju
>
>
|||If you created a third column in your table you use an UPDATE statement
to do this like:
UPDATE Sometable
SET col3 = ISNULL(col1,'') + ISNULL(col2,'')
or in a view you could use the almost same syntax like:
SELECT ISNULL(col1,'') + ISNULL(col2,'') as col3
THE ISNULL(col1,'') syntax is related to the issue that in some cases
the attributes could be NULL rather than just an empty or regular
string and this would lead to a NULL result concatenating the two
values together.
HTH, Jens Suessmeyer.

How to Concatenate Strings That Have Trailing Spaces?

I am trying to export data from a SQLServer database into a text file
using a stored procedure. I want to be able to read it and debug it
easily; therefore, I want all the columns to indent nicely. This means
I need to append trailing spaces to a text string (such as "Test1 ")
or append leading space in front of a text string that contains a
number (such as " 12.00"). Now, the stored procedure works fine when
I run it in Query Analyzer. But it doesn't work correctly when I run
it using ISQL - All the columns are not indented. I am wondering why
it doesn't work in ISQL.

This is what I want, and this is also what I get when I run the stored
procedure using Query Analyzer:

Test1 , 2,Test1.txt , 1.00, 1.00
Test22 , 2,Test22.txt , ,
Test333 , 2,Test333.txt , 30.00, 30.00

This is what I get if I run the stored procedure using ISQL
(isql -S myserver -E -w 556 -h-1 -n -d mydb -Q "exec MyTest"):

Test1, 2,Test1.txt, 1.00, 1.00
Test22, 2,Test22.txt, ,
Test333, 2,Test333.txt, 30.00, 30.00

You can see that the result from ISQL has the following differences:
1. It puts a space in front of each row.
2. It appends enough spaces at the end of each line to make
the line length to be exactly 61 characters.
3. It gets rid of the trailing space from each column.
4. It leaves only one blank space if the column has nothing
but a serie of spaces.

The following is the stored procedure that I am testing:

create procedure MyTest
as

set nocount on

create table #Test
(
Field1 varchar(10) null,
Field2 varchar( 5) null,
Field3 varchar(20) null,
Field4 varchar(10) null,
Field5 varchar(10) null
)
insert into #Test values
( "Test1 ", " 2","Test1.txt ", " 1.00", " 1.00" )
insert into #Test values
( "Test22 ", " 2","Test22.txt ", " ", " " )
insert into #Test values
( "Test333 ", " 2","Test333.txt ", " 30.00", " 30.00" )

select Field1 + "," +
Field2 + "," +
Field3 + "," +
Field4 + "," +
Field5
from #Test

drop table #Test
go

Strangely, the differences #3 and #4 only show up when I use the
SELECT statement on a table. They don't show up when I use SELECT
statements to show constant text strings or string variables, like
this:

set nocount on
select "Test1 " + "," +
" 2" + "," +
"Test1.txt " + "," +
" 1.00" + "," +
" 1.00"
select "Test22 " + "," +
" 2" + "," +
"Test22.txt " + "," +
" " + "," +
" "
select "Test333 " + "," +
" 2" + "," +
"Test333.txt " + "," +
" 30.00" + "," +
" 30.00"

The result is like the following if I use constant text strings or
string variables:

Test1 , 2,Test1.txt , 1.00, 1.00

Test22 , 2,Test22.txt , ,

Test333 , 2,Test333.txt , 30.00, 30.00

I need to run it from ISQL because that is how I run _all_ my other
stored procedures. I don't want to do anything differently just
because I need to run this stored procedure.

Thanks in advance for any suggestion.

Jay Chan"Jay Chan" <jaykchan@.hotmail.com> wrote in message
news:c7e5acb2.0308120646.2c8593ec@.posting.google.c om...
> I am trying to export data from a SQLServer database into a text file
> using a stored procedure. I want to be able to read it and debug it
> easily; therefore, I want all the columns to indent nicely. This means
> I need to append trailing spaces to a text string (such as "Test1 ")
> or append leading space in front of a text string that contains a
> number (such as " 12.00"). Now, the stored procedure works fine when
> I run it in Query Analyzer. But it doesn't work correctly when I run
> it using ISQL - All the columns are not indented. I am wondering why
> it doesn't work in ISQL.

Check out the 'SET ANSI_PADDING' setting in BOL.

Ian.|||Jay Chan (jaykchan@.hotmail.com) writes:
> I am trying to export data from a SQLServer database into a text file
> using a stored procedure. I want to be able to read it and debug it
> easily; therefore, I want all the columns to indent nicely. This means
> I need to append trailing spaces to a text string (such as "Test1 ")
> or append leading space in front of a text string that contains a
> number (such as " 12.00"). Now, the stored procedure works fine when
> I run it in Query Analyzer. But it doesn't work correctly when I run
> it using ISQL - All the columns are not indented. I am wondering why
> it doesn't work in ISQL.

This is because with ISQL you get a different setting for ANSI_PADDING.
This setting is OFF by default with ISQL, but ON by default with Query
Analyzer. The effect of this setting is that if it is OFF, SQL Server
trims trailing spaces from varchar data when you insert it.

Use SET ANSI_PADDING ON in ISQL to get ISQL to behave like QA.

The setting is actually saved with the table column, so if you create
the table in QA, it should work in ISQL even with the setting off. (But
I have not tested this.)

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> Check out the 'SET ANSI_PADDING' setting in BOL.

Thanks! This switch fixes the problem very nicely. Now, all the
columns indent correctly.

Jay Chan|||> This is because with ISQL you get a different setting for ANSI_PADDING.
> This setting is OFF by default with ISQL, but ON by default with Query
> Analyzer. The effect of this setting is that if it is OFF, SQL Server
> trims trailing spaces from varchar data when you insert it.
> Use SET ANSI_PADDING ON in ISQL to get ISQL to behave like QA.

Yes, you are right. I add the switch in my stored procedure right
before it starts creating temporary tables, and now it can indent the
column of info quite nicely. Thanks.

> The setting is actually saved with the table column, so if you create
> the table in QA, it should work in ISQL even with the setting off. (But
> I have not tested this.)

I believe this is correct according to the Help info on that switch. I
also create tables in Query Analyzer; this explains why I haven't come
across this problem until now.

Jay Chan

How to concate 2 ore more text fields into one field?

Hello,

I hope someone has already done this, but I have a table with a text column- example ColA, now i want to run a query to select the ColA in this table and combine the results of ColA into a ColB in another table.

Something like - Note: the codes below doesn't work!!

DECLARE @.ResultID as int
DECLARE @.AccID int
DECLARE _rows CURSOR
FOR SELECT AccID FROM tableA

FETCH NEXT FROM _rows INTO @.AccID

WHILE (@.@.fetch_status <> -1)
BEGIN

UPDATE TableB SET Report = Report + (SELECT txtField FROM tableA WHERE AccID = @.AccID)

WHERE AccID = @.AccID

FETCH NEXT FROM _rows INTO @.AccID

END

Thanks in advance

I tested something like this:(Assume you are using SQL Server 2000 text fields)

You can try it out in your code.

DECLARE @.ptr varbinary(50),@.ptr2 varbinary(50)

DECLARE @.len int, @.len2 int

update Table_B SET colB=(SELECT colA FROM Table_A where Accid=1) WHERE Accid=1

select @.ptr = TEXTPTR(colB), @.len=datalength(colB), @.ptr2 = TEXTPTR(report), @.len2=datalength(report) from Table_B WHERE Accid=1

UPDATETEXT Table_B.report @.ptr2 @.len2 0 Table_B.colB @.ptr

In SQL Server 2005, you can define Varchar(MAX) field, it will be a lot more easy to manipulate by using .WRITE function.

|||

Thanks Limno,

Sorry I could reply this sooner. This is one slick trick - appreciated.

However, I couldn't get this working as the way you have. Not sure what I am missing.

I kept having the error

Server: Msg 7116, Level 16, State 4, Line 13
Offset 17 is not in the range of available text, ntext, or image data.
The statement has been terminated.

DECLARE @.ResultID as int
DECLARE @.AccID int
DECLARE @.Heading varchar(255)
DECLARE @.ptrReport varbinary(16)
DECLARE @.ptrTmpField varbinary(16)
DECLARE @.Len1 int, @.Len2 int

SELECT @.ptrTmpField = TEXTPTR(tmpField), @.Len1 = Datalength(tmpField),
@.ptrReport = TEXTPTR(Report), @.len2 = Datalength(report)
FROM PHILTBL2 WHERE AccID = 1624728

UPDATETEXT PHILTBL2.Report @.ptrReport @.len2 0 tmpField @.ptrTmpField

Thanks

|||

If I changed the updatetext statement as

UPDATETEXT PHILTBL2.Report @.ptrReport @.len2 0 @.ptrTmpField

The error went away, but I get gebrish text in my report field.

Look like the textptr doesn't work

|||

Check this link that Jared Ko provided earlier today:

http://databases.aspfaq.com/general/how-do-i-concatenate-strings-from-a-column-into-a-single-row.html

|||

Thanks CetinBasoz, but the the problem I am dealing with is the text field and it's a different animal than varchar field. :-(

Check here for more info

http://msdn2.microsoft.com/en-us/library/ms189466.aspx

|||

Please see below scripts for some examples on how to use UPDATETEXT with TEXTPTR.

-- Script #1

/* To concatenate several binary values into one image field: */
create table #Bin ( SegmentID int IDENTITY ( 1 , 1 ) , Segment varbinary(2) )
insert #Bin values (0x11)
insert #Bin values (0x22)
select * from #Bin
go
declare @.imageptr varbinary(16), @.segmentid int, @.segment varbinary(16)
create table #AllBin ( Segments image null )
-- Get valid pointer first
insert #AllBin values ( 0x0 )
select @.imageptr = TEXTPTR( Segments ) from #AllBin
-- Set data to null
update #AllBin set Segments = null

select @.segmentid = -1
while(1=1)
begin
select @.segmentid = min(segmentid)
from #Bin
where segmentid > @.segmentid
if @.segmentid is null break

select @.segment = segment from #Bin where segmentid = @.segmentid

updatetext #AllBin.Segments @.imageptr null 0 @.segment
end
select * from #AllBin
go
drop table #Bin
drop table #AllBin
go

-- Script #2

create procedure #t (
@.t1 text , @.i1 image, @.t2 text, @.i2 image, @.t3 varchar(30), @.i3 varbinary(2)
)
as
declare @.tptr varbinary(16), @.iptr varbinary(16), @.tpos int, @.ipos int
create table #blob(id int identity, t text, i image)

insert #blob values(@.t1, @.i1)
select id, convert(varchar(50), t) as text_val, convert(varbinary, i) as image_val
from #blob

update #blob set t = @.t2, i = @.i2 where id = @.@.identity
select id, convert(varchar(50), t) as text_val, convert(varbinary, i) as image_val
from #blob

select @.tptr = TEXTPTR(t), @.tpos = PATINDEX('%TEXT...%', t) - 1,
@.iptr = TEXTPTR(i), @.ipos = 2
from #blob

updatetext #blob.t @.tptr @.tpos 0 @.t3
select id, convert(varchar(50), t) as text_val, convert(varbinary, i) as image_val
from #blob

updatetext #blob.i @.iptr @.ipos 1 @.i3
select id, convert(varchar(50), t) as text_val, convert(varbinary, i) as image_val
from #blob
go

exec #t 'SOME TEXT HERE...', 0x02498765bcde3,
'MODIFIED TEXT...', 0xab86ec64,
'(INSERT BEFORE TEXT) ', 0xcd -- replace 3rd byte

-- Inserted text & image value
/* id text_val image_val
-- -- --
1 SOME TEXT HERE... 0x002498765BCDE3
*/

-- Updated text & image value. This one replaces the existing values
/*
id text_val image_val
-- -- --
1 MODIFIED TEXT... 0xAB86EC64
*/

-- Modified text value only. This one inserts some text into the existing value
/*
id text_val image_val
-- -- --
1 MODIFIED (INSERT BEFORE TEXT) TEXT... 0xAB86EC64
*/

-- Modified image value only. This one changes a byte in the existing value
/*
id text_val image_val
-- -- --
1 MODIFIED (INSERT BEFORE TEXT) TEXT... 0xAB86CD64

*/
go
drop proc #t
go

Also, here is a link to a SP that show how to generate text value from multiple strings.

|||Sorry my bad. When I wrote it was around 3 AM here.

How to compress text before storing in DB?

I was planning on running a service where thousands of text messages are stored. Obviously I'd want to make the most of my DB space, and was wondering if there's some way for SQL to compress text down to the smallest space possible. If not, is there some kind of ASP component I could download to do this? Failing that, I could always write a simple one, which takes the most common letter combinations, and shortens them down to a single character.

Any advice?Space is cheap. Use it. Otherwise, your data will be dependent on whatever compression algorithm you use, and everyone will have to consult with you to read the data. Of course, if you're a consultant focused on billable hours, writing some hair-brained compression may be just what you want.

Either don't worry about space (have you done the math on what this gives you vs. buying the adequate space?), or store your text outside the db on a compressed drive.|||I don't have to consult with anyone :) It's a one-man project, just something I'm building for consumers, there's no income involved. And space is cheap, yes, if I'm hosting this solution myself. But I'm not. And SQL Server 2000 DB storage does not come cheaply at a good, reliable host.|||Anyone else? Anyone?|||Maybe this will get you to drink the water: Video on Hotmail Architecture (http://channel9.msdn.com/ShowPost.aspx?PostID=39016)

how to compare two word documents using full text search?

can we use full text search and mining algorithms to comapre two word or text documents to find out if they are similar
please help.
thaks for reading

You can do this, but it isn't the best solution all the time. Read here first:

http://www.microsoft.com/technet/itshowcase/content/intdocmgmtsql2005.mspx

And here is an example:

http://www.aspcode.net/articles/l_en-US/t_default/ASP.NET/ASP.NET-2.0/How-toin-practice/File-uploadinghowto-part-3_article_311.aspx

Here is more info:

http://www.simple-talk.com/sql/learn-sql-server/sql-server-full-text-search-language-features/

how to compare two word documents using full text search?

can we use full text search and mining algorithms to comapre two word or text documents to find out if they are similar
please help.
thaks for reading

You can do this, but it isn't the best solution all the time. Read here first:

http://www.microsoft.com/technet/itshowcase/content/intdocmgmtsql2005.mspx

And here is an example:

http://www.aspcode.net/articles/l_en-US/t_default/ASP.NET/ASP.NET-2.0/How-toin-practice/File-uploadinghowto-part-3_article_311.aspx

Here is more info:

http://www.simple-talk.com/sql/learn-sql-server/sql-server-full-text-search-language-features/

How to compare two strings

How can I compare two text strings?
DIFFERENCE is not very precise. Is there another string function in T-SQL?
What do you mean by compare? Presumably not =.
Have a look at patindex and soundex.
"Dave" wrote:

> How can I compare two text strings?
> DIFFERENCE is not very precise. Is there another string function in T-SQL?
>
>

How to compare two strings

How can I compare two text strings?
DIFFERENCE is not very precise. Is there another string function in T-SQL?What do you mean by compare? Presumably not =.
Have a look at patindex and soundex.
"Dave" wrote:

> How can I compare two text strings?
> DIFFERENCE is not very precise. Is there another string function in T-SQL
?
>
>

How to compare two strings

How can I compare two text strings?
DIFFERENCE is not very precise. Is there another string function in T-SQL?What do you mean by compare? Presumably not =.
Have a look at patindex and soundex.
"Dave" wrote:
> How can I compare two text strings?
> DIFFERENCE is not very precise. Is there another string function in T-SQL?
>
>

how to compare data of text type?

We can't use the term "=" to compare the data directly ,of which type
is text or ntext. Are there other good ideas to get the exact matchs?
In T-SQL you can use the LIKE operator without any wildcard characters.
However, you'd much better off using an appropriate text comparison tool on
the client.
ML
http://milambda.blogspot.com/
|||Do hierarchy of tests, first check datalength to see if they are the same,
if not bail stating difference, if they are, then you need to do byte by
byte comparisons.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"chemInformatic" <cdfuwu@.hotmail.com> wrote in message
news:1133871808.033165.299100@.g47g2000cwa.googlegr oups.com...
> We can't use the term "=" to compare the data directly ,of which type
> is text or ntext. Are there other good ideas to get the exact matchs?
>

how to combine text data rows into single row?

Hi!!!!

Need some quick help on SQL.....DTS packages.

I’ve loaded data from text files into a table which is in the following format.....

Seq_No

Row_No

Data

1

1

ABC

2

2

DEF

3

3

GHI

4

4

JKL

5

1

MNO

6

2

PQR

7

3

STU

8

4

VWX

Every four lines correspond to one single logical record. So, all the data in every 4 rows have to be combined into one big string which’ll then be loaded into one col of another temporary table .....structure below.....

Record_No

Data

1

ABCDEFGHIJKL

2

MNOPQRSTUVWX

I’m looking for a way to achieve this WITHOUT using Cursors (as it’ll slow down performance)......

Any suggestions will be highly appreciated.

what data type is the Data column?|||

One way is to use the CLR Aggregate function for string concatenation shown in the BOL

http://msdn2.microsoft.com/en-us/library/ms254508.aspx

and then run a query like this

select ((Seq_No-1) / 4)+1 as Record_No, dbo.Concatenate(Data) as Data
into dest
from source
group by ((Seq_No-1) / 4)+1
go

select * from dest
go

|||

hi pramy,

please refer to this post

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=586833&SiteID=1

regards

|||

hi Derek,

thanks for ur reply. the data type is of varchar.

|||

pramy, you still need help on this issue? if not please mark an answer.

thanks,

derek

|||

hi all,

thanks for ur response.

Derek, i've got the solution for my problem and the query is as given below.

select 'data' = convert(varchar(100),(a.data)+''+(b.data)+''+(c.data)+''+(d.data))

from test_data a,test_data b,test_data c,test_data d

where ((b.seqno=a.seqno+1 and b.rowno=a.rowno+1)and(c.seqno=b.seqno+1 and c.rowno=b.rowno+1)and(d.seqno=c.seqno+1 and d.rowno=c.rowno+1))

how to combine text data rows into single row?

Hi!!!!

Need some quick help on SQL.....DTS packages.

I’ve loaded data from text files into a table which is in the following format.....

Seq_No

Row_No

Data

1

1

ABC

2

2

DEF

3

3

GHI

4

4

JKL

5

1

MNO

6

2

PQR

7

3

STU

8

4

VWX

Every four lines correspond to one single logical record. So, all the data in every 4 rows have to be combined into one big string which’ll then be loaded into one col of another temporary table .....structure below.....

Record_No

Data

1

ABCDEFGHIJKL

2

MNOPQRSTUVWX

I’m looking for a way to achieve this WITHOUT using Cursors (as it’ll slow down performance)......

Any suggestions will be highly appreciated.

what data type is the Data column?|||

One way is to use the CLR Aggregate function for string concatenation shown in the BOL

http://msdn2.microsoft.com/en-us/library/ms254508.aspx

and then run a query like this

select ((Seq_No-1) / 4)+1 as Record_No, dbo.Concatenate(Data) as Data
into dest
from source
group by ((Seq_No-1) / 4)+1
go

select * from dest
go

|||

hi pramy,

please refer to this post

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=586833&SiteID=1

regards

|||

hi Derek,

thanks for ur reply. the data type is of varchar.

|||

pramy, you still need help on this issue? if not please mark an answer.

thanks,

derek

|||

hi all,

thanks for ur response.

Derek, i've got the solution for my problem and the query is as given below.

select 'data' = convert(varchar(100),(a.data)+''+(b.data)+''+(c.data)+''+(d.data))

from test_data a,test_data b,test_data c,test_data d

where ((b.seqno=a.seqno+1 and b.rowno=a.rowno+1)and(c.seqno=b.seqno+1 and c.rowno=b.rowno+1)and(d.seqno=c.seqno+1 and d.rowno=c.rowno+1))

How to combine a calendar control?

This is a multi-part message in MIME format.
--=_NextPart_000_0052_01C4F56E.058CFCB0
Content-Type: text/plain;
charset="windows-1255"
Content-Transfer-Encoding: quoted-printable
If I include a date parameter in a report- in the title of the deployed = report appears a textbox in which the user should type the date in the = correct format with no mistakes (08/01/2004 ?.. 01/08/2004 ?.. 20040108 = ?..).
Is there a way to combine a standart windows calendar control instead? = how?
--=_NextPart_000_0052_01C4F56E.058CFCB0
Content-Type: text/html;
charset="windows-1255"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
If I include a date parameter in a = report- in the title of the deployed report appears a textbox in which the user should = type the date in the correct format with no mistakes (08/01/2004 ?.. = 01/08/2004 ?.. 20040108 ?..).
Is there a way to combine a standart = windows calendar control instead? how?
--=_NextPart_000_0052_01C4F56E.058CFCB0--This is a multi-part message in MIME format.
--=_NextPart_000_000F_01C4F624.EE666500
Content-Type: text/plain;
charset="windows-1255"
Content-Transfer-Encoding: quoted-printable
Hi Geri,
You'll need to provide your own user interface (eg. a web page) and =place the control on it to capture (and validate) the date before the =report submision is made.
Usuallly, it's best to create a web page that will call RS to determine =what parameters are required for the report at runtime and dynamically =show the user control that best fists the parameter datatype.
This generic web page can then be used over and over to provide =parameter entry / validation support for other reports as well.
- peteZ
"Geri Reshef" <GeriReshef@.Yahoo.com> wrote in message =news:uOMd81V9EHA.2032@.tk2msftngp13.phx.gbl...
If I include a date parameter in a report- in the title of the =deployed report appears a textbox in which the user should type the date =in the correct format with no mistakes (08/01/2004 ?.. 01/08/2004 ?.. =20040108 ?..).
Is there a way to combine a standart windows calendar control instead? =how?
--=_NextPart_000_000F_01C4F624.EE666500
Content-Type: text/html;
charset="windows-1255"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Hi Geri,
You'll need to provide your own user interface (eg. =a web page) and place the control on it to capture (and validate) the date before the report submision is made.
Usuallly, it's best to create a web page that will =call RS to determine what parameters are required for the report at runtime and =dynamically show the user control that best fists the parameter =datatype.
This generic web page can then be used over and over =to provide parameter entry / validation support for other reports as well.
- peteZ
"Geri Reshef" =wrote in message news:uOMd81V9EHA.2032=@.tk2msftngp13.phx.gbl...
If I include a date parameter in a =report- in the title of the deployed report appears a textbox in which the user =should type the date in the correct format with no mistakes (08/01/2004 ?.. 01/08/2004 ?.. 20040108 ?..).
Is there a way to combine a standart =windows calendar control instead? how?

---

--=_NextPart_000_000F_01C4F624.EE666500--|||This is a multi-part message in MIME format.
--=_NextPart_000_01C5_01C4F61F.AA3585F0
Content-Type: text/plain;
charset="windows-1255"
Content-Transfer-Encoding: quoted-printable
"PeteZ" <peteZ@.aol.com> wrote in message =news:%23r4R4ic9EHA.3940@.tk2msftngp13.phx.gbl...
Hi Geri,
You'll need to provide your own user interface (eg. a web page) and =place the control on it to capture (and validate) the date before the =report submision is made.
Usuallly, it's best to create a web page that will call RS to =determine what parameters are required for the report at runtime and =dynamically show the user control that best fists the parameter =datatype.
This generic web page can then be used over and over to provide =parameter entry / validation support for other reports as well.
- peteZ
"Geri Reshef" <GeriReshef@.Yahoo.com> wrote in message =news:uOMd81V9EHA.2032@.tk2msftngp13.phx.gbl...
If I include a date parameter in a report- in the title of the =deployed report appears a textbox in which the user should type the date =in the correct format with no mistakes (08/01/2004 ?.. 01/08/2004 ?.. =20040108 ?..).
Is there a way to combine a standart windows calendar control =instead? how?
--=_NextPart_000_01C5_01C4F61F.AA3585F0
Content-Type: text/html;
charset="windows-1255"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
"PeteZ" wrote in message news:%23r4R4ic9EHA.=3940@.tk2msftngp13.phx.gbl...
Hi Geri,

You'll need to provide your own user interface =(eg. a web page) and place the control on it to capture (and validate) the date before the report submision is made.

Usuallly, it's best to create a web page that will =call RS to determine what parameters are required for the report at runtime =and dynamically show the user control that best fists the parameter datatype.

This generic web page can then be used over and =over to provide parameter entry / validation support for other reports as well.

- peteZ

"Geri Reshef" =wrote in message news:uOMd81V9EHA.2032=@.tk2msftngp13.phx.gbl...
If I include a date parameter in a =report- in the title of the deployed report appears a textbox in which the user =should type the date in the correct format with no mistakes (08/01/2004 ?.. = 01/08/2004 ?.. 20040108 ?..).
Is there a way to combine a =standart windows calendar control instead? how?

---

--=_NextPart_000_01C5_01C4F61F.AA3585F0--|||You'd better combine a web calendar control in a web application ,which call
Reporting Service as a web service.I.ve implemented it .
"Geri Reshef" wrote:
> If I include a date parameter in a report- in the title of the deployed report appears a textbox in which the user should type the date in the correct format with no mistakes (08/01/2004 ?.. 01/08/2004 ?.. 20040108 ?..).
> Is there a way to combine a standart windows calendar control instead? how?