How to make you reports load faster ?

Any ideas how to make reports faster when returning lots fo rows?
I know you would need to work on your sql query etc..
Or maybe cache it.

But i'm thinking of having a kind of middle tier thing that sits between your sql database and the reports itself.

Any ideas would be appreciated

Dear rote,

Caching is certainly one option. Search the sql books online for Report Caching, Execution Snapshots and Report History.

If possible, keep your report server on a different machine. Adding memory will help increase performance.

http://www.microsoft.com/technet/prodtechnol/sql/2005/pspsqlrs.mspx#E6NAC

HTH,
Suprotim Agarwal

--
http://www.dotnetcurry.com
--

|||

RS comes with two middle tier elements of its own: snapshots and caching <s>. Do either of these meet your needs?

>L<

How to make a load test a database

Common question i thing. We finished a project and about to deploy but we need tto test it. Is there a way to load random value into our tables(we got 54 tables in it). Or we need to write an application to do this. Thanks for suggestionsVSDB has a data generator which can generate various based data, either on real data, pulled fromyour produtional system or random data, based on the possible range for the data types.

Jens K. Suessmeyer.

http://www.sqlserver2008.de
|||

When I had such a requirement used DTM data generator that has worked perfectly.

FYi

How to load a Unicode file into the database in the same order as the file order

The data file is a simple Unicode file with lines of text. BCP
apparently doesn't guarantee this ordering, and neither does the
import tool. I want to be able to load the data either sequentially or
add line numbering to large Unicode file (1 million lines). I don't
want to deal with another programming language if possible and I
wonder if there's a trick in SQL Server to get this accomplished.
Thanks for any help.
Mark Leary
--== Posted via webservertalk.com - Unlimited-Uncensored-Secure Usenet News==--
http://www.webservertalk.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
--= East/West-Coast Server Farms - Total Privacy via Encryption =--
no-email wrote:
> The data file is a simple Unicode file with lines of text. BCP
> apparently doesn't guarantee this ordering, and neither does the
> import tool. I want to be able to load the data either sequentially or
> add line numbering to large Unicode file (1 million lines). I don't
> want to deal with another programming language if possible and I
> wonder if there's a trick in SQL Server to get this accomplished.
> Thanks for any help.
> Mark Leary
>
Why does the order of the rows inserted into the table matter in your
case? Relational databases don't understand row order. If you need them
sorted in some way after the import, you can create a clustered index on
the table to get the rows ordered in a way that helps your queries
perform better.
In general, I think BCP processes the rows in the file sequentially. But
again, I'm not clear on why this matters.
Could you elaborate on the exact issue you are trying to avoid.
David Gugick
Imceda Software
www.imceda.com
|||"David Gugick" <davidg-nospam@.imceda.com> wrote:

> Why does the order of the rows inserted into the table matter in your
> case? Relational databases don't understand row order. If you need them
> sorted in some way after the import, you can create a clustered index on
> the table to get the rows ordered in a way that helps your queries perform
> better.
> In general, I think BCP processes the rows in the file sequentially. But
> again, I'm not clear on why this matters.
> Could you elaborate on the exact issue you are trying to avoid.
I am trying to load a text file sequentially in order to perform text
manipulations using T-SQL that do depend on the exact order. I would be
happy with simply adding a line number to each line of the Unicode text
file, and then loading the file with line number determining the order, but
I want to avoid programming in another language if possible. Eventually the
loaded text would be converted to proper relational tables. This doesn't
have to do with improving performance. Does this help?
Thanks.
|||no-email wrote:
> "David Gugick" <davidg-nospam@.imceda.com> wrote:
>
> I am trying to load a text file sequentially in order to perform text
> manipulations using T-SQL that do depend on the exact order. I would
> be happy with simply adding a line number to each line of the Unicode
> text file, and then loading the file with line number determining the
> order, but I want to avoid programming in another language if
> possible. Eventually the loaded text would be converted to proper
> relational tables. This doesn't have to do with improving
> performance. Does this help?
> Thanks.
Yes. It sounds like you have rows in a specific order that will need to
be processed once on SQL Server. You want to preserve the order of rows
in the file so the rows can be processed in the same order once on SQL
Server.
In order to do this in any relational database, you need a sort key.
There is never a guarantee that a query you run without an ORDER BY will
return rows in the same order in any consistent way.
My understanding is that BCP feeds the rows in the order they appear in
a file. I can't imagine any reason it would or could do it differently.
In that case, you want to insert the data into a table that contains an
IDENTITY column. You can then use that key for your ORDER BY when
processing the rows from whatever process does that.
David Gugick
Imceda Software
www.imceda.com
|||Do you know what order the source file is sorted in? If so, and if the
sort order column(s) are included then you may not need to know the
line number since it is (theoretically anyway) possible to derive that
information from the other data.
If not, then this article has a useful suggestion:
http://www.google.co.uk/groups?selm=...GP11.phx.gb l
not sure if that will work with unicode data though.
David Portas
SQL Server MVP
|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in
message:

> Do you know what order the source file is sorted in? If so, and if
the
> sort order column(s) are included then you may not need to know the
> line number since it is (theoretically anyway) possible to derive
that
> information from the other data.
Unfortunately the data file consists of simple lines of text with no
other way to extract potential column information before loading it
into the database.

> If not, then this article has a useful suggestion:
>
http://www.google.co.uk/groups?selm=...GP11.phx.gb l
> not sure if that will work with unicode data though.
Good suggestion but it does fail with Unicode. Thanks anyway.
--== Posted via webservertalk.com - Unlimited-Uncensored-Secure Usenet News==--
http://www.webservertalk.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
--= East/West-Coast Server Farms - Total Privacy via Encryption =--
|||"David Gugick" <davidg-nospam@.imceda.com> wrote:

> My understanding is that BCP feeds the rows in the order they appear
in
> a file. I can't imagine any reason it would or could do it
differently.
> In that case, you want to insert the data into a table that contains
an
> IDENTITY column. You can then use that key for your ORDER BY when
> processing the rows from whatever process does that.
In general BCP loads the data in the same order as the file but not
always. The ordering sometimes reverses for thousands of rows, or
skips certain rows, but you need to check it carefully to find the
misordering. You can create a table with an identity column and load
the data, but again if the rows are not loaded in the same sequence as
the file this won't matter. You will end up with an ordered table that
is unfortunately not in the same order as the original file.
To be honest I have tried all these suggestions in the past. My
typical solution would be to open the original file in Excel, add a
rownumber column and then save the resulting file as a Unicode file.
This works up until around a maximum of 63,000 rows. You can break a
file into 63,000 row subfiles, but this would be too tedious if you
have row counts approaching a million.
Thanks for the suggestions but I may have to learn some C#.
Unfortunately Visual Basic has problems with Unicode, as I suspect
also C++ and C have similar problems.
--== Posted via webservertalk.com - Unlimited-Uncensored-Secure Usenet News==--
http://www.webservertalk.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
--= East/West-Coast Server Farms - Total Privacy via Encryption =--
|||What about using the CTS Import Wizard to import the data from the flat
file into a table with an identity.
Where is this data coming from? Is there any way to recreate it with a
counter column included in the output?
David Gugick
Imceda Software
www.imceda.com
|||"David Gugick" <davidg-nospam@.imceda.com> wrote:

> What about using the CTS Import Wizard to import the data from the
flat
> file into a table with an identity.
It's the same problem. The table order generally follows the order in
the file but not always.

> Where is this data coming from? Is there any way to recreate it with
a
> counter column included in the output?
It's foreign language dictionary data that cannot be recreated. I
could manipulate the data on the file level but I am trying to avoid
potential problems with Unicode. Once the data gets into SQL Server I
don't have any problems, as long as the table order exactly matches
the file order.
--== Posted via webservertalk.com - Unlimited-Uncensored-Secure Usenet News==--
http://www.webservertalk.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
--= East/West-Coast Server Farms - Total Privacy via Encryption =--
|||no-email wrote:
> It's foreign language dictionary data that cannot be recreated. I
> could manipulate the data on the file level but I am trying to avoid
> potential problems with Unicode. Once the data gets into SQL Server I
> don't have any problems, as long as the table order exactly matches
> the file order.
I'm not sure what problems you would have as long as the tool you are
using to edit the data is unicode aware. I use TextEdit for editing
(www.textpad.com) and it has simple replacement expressions.
Assuming you had each row of data on a single line, you could simply do
the following:
1- Add a leading CARRIAGE RETURN to the file
2- Open the Replace dialog
3- Check the Regular Expression option
4- Type "\n" - WITHOUT QUOTES in the Find What entry- means New Line
character
5- Type "\n\i\t" - WITHOUT QUOTES in the Replace With entry - means New
Line + Auto Number + TAB
6- Click Replace All
7 - Remove the leading carriage return in the file
8 - Click FILE SAVE AS and make sure the UNICODE option is selected
You can replace the TAB character with whatever your file requires or
add DOUBLE QUOTES around the Auto Number, etc.
You can download a free trial of TextPad on the web site.
David Gugick
Imceda Software
www.imceda.com

How to load XML from filename and path

Hi,

I have inherited a database that has a table called "Resources" that contains a field called "FilePath". FilePath is a VarChar(100) and contains the server path and filename of an XML file on the server hard disk.

I need to join the data in the XML with rows in the "Resources" table (and other tables).

Is it possible to get a Stored Procedure to load the XML file into a temporary table?

Is loading it into a table the best way to do it - or can I join directly to the XML file somehow?

What is the best approach? (I know nothing about XML in SQL Server yet)

Thanks in advance,

Chiz.

Try the link below to use OPENXML in SQL Server, I am not sure about Temp tables I would try it to see if it is possible, just remember to drop the temp table explictly. Hope this helps.
http://msdn.microsoft.com/msdnmag/issues/05/06/DataPoints/default.aspx|||Thanks Caddre,
At the moment the .NET code calls a Stored Procedure which JOINs the Resources table with some other tables and returns the result which goes into a DataSet.
In order to build a DataSet that can be displayed in a DataGrid the DataSet has to be looked at (one row at a time) and the FilePath 'field' used to load the XML file from disk into an XmlDocument. Then the right data in the right node has to be found and then finally when that is found it can be added to the DataSet.
Anyway, to cut a long story short, I reckon that if I can somehow get T-SQL to load an XML file from disk into a temporary table that it will be neater and more efficient.
Any other help appreciated,
Chiz.|||

The text below is from the BOL(books online) sp_xml_preparedocument System stored Procedure can be used, run a search for sp_xml_preparedocument in the BOL (books online). Hope this helps

(sp_xml_preparedocument
Reads the Extensible Markup Language (XML) text provided as input, then parses the text using the MSXML parser (Msxml2.dll), and provides the parsed document in a state ready for consumption. This parsed document is a tree representation of the various nodes (elements, attributes, text, comments, and so on) in the XML document.)

How To Load Periodic SnapShot Fact Table With SSIS

I need help from you data warehouse / SSIS experts out there! I have a Transaction Fact Table with dollar amounts as the measurements. The grain is one row per transaction. I want to roll this up into a Monthly Periodic Snapshot based on 5 keys. I am having no problem where there is transaction data for each month.

However, the problem I am having is - how do I gracefully insert the Monthly rows for the five keys where there was no activity in the transaction fact table - I am sure there is a slick way to do this with SSIS but I am definitely having a mental block on how to accomplish this. Any help would be appreciated!

Hopefully you have a method of deriving all possibly combinations of the 5 key columns. Personally I would do this by extracting all possible values from the dimension tabes, unioning them together but where each column creates a new column in the output from the UNION ALL and then use the Aggregate component to produce all of the combinations.

Once you have done that you can use a MERGE JOIN component to join to your dataset and produce nulls/zeros for teh fact values for all of the missing rows.

-Jamie

|||

Thanks Jamie. I will give that a shot.

-Steve

How to load JPG images into SQL SERVER 2000

How to load JPG images into SQL SERVER 2000Sunny,
While this is not really a datawarehouse related question, there are many,
many ways to do this and just a couple of KB articles on this FAQ are the
following:
258038 (Q258038) HOWTO: Access and Modify SQL Server BLOB Data by Using the
ADO Stream Object
http://support.microsoft.com/?kbid=258038
201785 (Q201785) HOWTO: Import FileSystem Data Using DTS and Index Server
http://support.microsoft.com/defaul...kb;en-us;201785 (MSIDXS)
309158 (Q309158) HOW TO: Read and Write BLOB Data by Using ADO.NET with C#
http://support.microsoft.com/defaul...kb;EN-US;309158
308042 (Q308042) HOW TO: Read and Write BLOB Data by Using ADO.NET with
VB.NET
http://support.microsoft.com/defaul...kb;EN-US;308042
Regards,
John
"Sunny" <anonymous@.discussions.microsoft.com> wrote in message
news:a8ce01c3b841$65786580$a601280a@.phx.gbl...

quote:

---

> How to load JPG images into SQL SERVER 2000

---

How to load jpeg file in SqL2000 and how to retrieve from SQL2000.

Hi friends
Now I am working in SQL2000 as back end .I want to load jpeg file in database and retrieve from database. Please guide me.You can use this:

CREATE TABLE Images ([stream] [image] NULL)
insert into Images ([stream]) values (@.image)

Hope this helps!!
Deven.

How to load images to table

I added a column to a table to hold images. How can I easily upload
these images from the disk file? There are only a few so i thought
there would be a command or utility that I could use, but I haven't
been able to find one.In SQL Server 2005 you can use the BULK option of OPENROWSET:
CREATE TABLE Foo ( image_data VARBINARY(MAX));
INSERT INTO Foo (image_data)
SELECT image_data
FROM OPENROWSET(BULK N'C:\image.jpg',
SINGLE_BLOB) AS ImageSource(image_data);
HTH,
Plamen Ratchev
http://www.SQLStudio.com|||Also take a look at the TEXTCOPY.exe utility. You can Google for TEXTCOPY,
and find a lot of info. But here's one
http://www.mssqlcity.com/Articles/KnowHow/Textcopy.htm
Linchi
"cs_hart@.yahoo.com" wrote:
> I added a column to a table to hold images. How can I easily upload
> these images from the disk file? There are only a few so i thought
> there would be a command or utility that I could use, but I haven't
> been able to find one.
>|||On Jan 22, 2:20 pm, "Plamen Ratchev" <Pla...@.SQLStudio.com> wrote:
> In SQL Server 2005 you can use the BULK option of OPENROWSET:
> CREATE TABLE Foo ( image_data VARBINARY(MAX));
> INSERT INTO Foo (image_data)
> SELECT image_data
> FROM OPENROWSET(BULK N'C:\image.jpg',
> SINGLE_BLOB) AS ImageSource(image_data);
> HTH,
> Plamen Ratchevhttp://www.SQLStudio.com
I tried this but get an error Incorrect syntax near the keyword 'BULK'.|||Are you using SQL Server 2005? As I noted this works only on SQL Server
2005. For uploading images to SQL Server 2000, you can see this example by
Erland Sommarskog:
http://www.sommarskog.se/blobload.txt
HTH,
Plamen Ratchev
http://www.SQLStudio.com

How to load images dinamically?

I want to show a different image on my header depending on some data on the
database, the path of the image could be on a table.
How can I do that?
--
LUIS ESTEBAN VALENCIA
MICROSOFT DCE 3.
MIEMBRO ACTIVO DE ALIANZADEV
http://spaces.msn.com/members/extremed/I would like to know this too. Rpt Designer allows you to use links for
images, but my question is similar. How can you change the value of the link?
"Luis Esteban Valencia" wrote:
> I want to show a different image on my header depending on some data on the
> database, the path of the image could be on a table.
> How can I do that?
> --
> LUIS ESTEBAN VALENCIA
> MICROSOFT DCE 3.
> MIEMBRO ACTIVO DE ALIANZADEV
> http://spaces.msn.com/members/extremed/
>
>|||Hi Luis,
I figured out a way to do it using Links. Create a textbox and point it to
link stored in the dB. Create an image and under the image properties, set
it's value to that textbox (e.g., =ReportItems.txtImageLink.Value).
HTH
"Luis Esteban Valencia" wrote:
> I want to show a different image on my header depending on some data on the
> database, the path of the image could be on a table.
> How can I do that?
> --
> LUIS ESTEBAN VALENCIA
> MICROSOFT DCE 3.
> MIEMBRO ACTIVO DE ALIANZADEV
> http://spaces.msn.com/members/extremed/
>
>|||Neo,
I tried what you suggested, but can't get it to work. I put
=ReportItems!txtPhotoLoc.Value in the Value property of the Image control.
The Source property is set to External. txtPhotoLoc is the name of a textbox
that has its value set to =Fields!PhotographLocation.Value.
Is this what you did? It doesn't work for me in either the preview or the
deployed version viewed through IE.
Is there something special about the "ReportItems" qualifier? I can't find
that object group anywhere in my project, but it doesn't complain about it
when I save and deploy it.
I checked and privileges are not a problem -- the content of
PhotographLocation it a fully qualified path to a URL you can paste in any
browser and get an image (e.g.,
http://msdn.microsoft.com/library/toolbar/3.0/images/banners/msdn_masthead_ltr.gif).
When I view source on the results in IE, the area that should show the
picture contains
<TD COLSPAN="5" class="a55"><IMG class="r1" src="http://pics.10026.com/?src="></TD></TR>
Why does SRC = ""?
-prophead
"Neo" wrote:
> Hi Luis,
> I figured out a way to do it using Links. Create a textbox and point it to
> link stored in the dB. Create an image and under the image properties, set
> it's value to that textbox (e.g., =ReportItems.txtImageLink.Value).
> HTH
> "Luis Esteban Valencia" wrote:
> > I want to show a different image on my header depending on some data on the
> > database, the path of the image could be on a table.
> > How can I do that?
> >
> > --
> > LUIS ESTEBAN VALENCIA
> > MICROSOFT DCE 3.
> > MIEMBRO ACTIVO DE ALIANZADEV
> > http://spaces.msn.com/members/extremed/
> >
> >
> >|||Prophead,
In my report, I have an image placed in the page header and the report text
is placed in the body. I set the image value =ReportItems!txtImageLink.Value.
What is the value of Fields.PhotgraphLocation.Value? It doesn't look like
the field is being properly filled in. It should be something like
"http://mysite/images/img.gif".
BTW - SRC is the property of the Img html tag. Since the field is not being
populated correctly, it will be "" or null.
"prophead" wrote:
> Neo,
> I tried what you suggested, but can't get it to work. I put
> =ReportItems!txtPhotoLoc.Value in the Value property of the Image control.
> The Source property is set to External. txtPhotoLoc is the name of a textbox
> that has its value set to =Fields!PhotographLocation.Value.
> Is this what you did? It doesn't work for me in either the preview or the
> deployed version viewed through IE.
> Is there something special about the "ReportItems" qualifier? I can't find
> that object group anywhere in my project, but it doesn't complain about it
> when I save and deploy it.
> I checked and privileges are not a problem -- the content of
> PhotographLocation it a fully qualified path to a URL you can paste in any
> browser and get an image (e.g.,
> http://msdn.microsoft.com/library/toolbar/3.0/images/banners/msdn_masthead_ltr.gif).
> When I view source on the results in IE, the area that should show the
> picture contains
> <TD COLSPAN="5" class="a55"><IMG class="r1" src="http://pics.10026.com/?src="></TD></TR>
> Why does SRC = ""?
> -prophead
> "Neo" wrote:
> > Hi Luis,
> >
> > I figured out a way to do it using Links. Create a textbox and point it to
> > link stored in the dB. Create an image and under the image properties, set
> > it's value to that textbox (e.g., =ReportItems.txtImageLink.Value).
> >
> > HTH
> >
> > "Luis Esteban Valencia" wrote:
> >
> > > I want to show a different image on my header depending on some data on the
> > > database, the path of the image could be on a table.
> > > How can I do that?
> > >
> > > --
> > > LUIS ESTEBAN VALENCIA
> > > MICROSOFT DCE 3.
> > > MIEMBRO ACTIVO DE ALIANZADEV
> > > http://spaces.msn.com/members/extremed/
> > >
> > >
> > >|||Thanks, Neo. Installing SP1 fixed the problem. Evidently there was a
problem when you set the source to "External" and the Value to a field from
the db with a URL. I also deleted the original report from the server and
re-deployed it, so that might have fixed it as well.
-prophead
"Neo" wrote:
> Prophead,
> In my report, I have an image placed in the page header and the report text
> is placed in the body. I set the image value => ReportItems!txtImageLink.Value.
> What is the value of Fields.PhotgraphLocation.Value? It doesn't look like
> the field is being properly filled in. It should be something like
> "http://mysite/images/img.gif".
> BTW - SRC is the property of the Img html tag. Since the field is not being
> populated correctly, it will be "" or null.
>
> "prophead" wrote:
> > Neo,
> >
> > I tried what you suggested, but can't get it to work. I put
> > =ReportItems!txtPhotoLoc.Value in the Value property of the Image control.
> > The Source property is set to External. txtPhotoLoc is the name of a textbox
> > that has its value set to =Fields!PhotographLocation.Value.
> >
> > Is this what you did? It doesn't work for me in either the preview or the
> > deployed version viewed through IE.
> >
> > Is there something special about the "ReportItems" qualifier? I can't find
> > that object group anywhere in my project, but it doesn't complain about it
> > when I save and deploy it.
> >
> > I checked and privileges are not a problem -- the content of
> > PhotographLocation it a fully qualified path to a URL you can paste in any
> > browser and get an image (e.g.,
> > http://msdn.microsoft.com/library/toolbar/3.0/images/banners/msdn_masthead_ltr.gif).
> >
> > When I view source on the results in IE, the area that should show the
> > picture contains
> > <TD COLSPAN="5" class="a55"><IMG class="r1" src="http://pics.10026.com/?src="></TD></TR>
> > Why does SRC = ""?
> >
> > -prophead
> >
> > "Neo" wrote:
> >
> > > Hi Luis,
> > >
> > > I figured out a way to do it using Links. Create a textbox and point it to
> > > link stored in the dB. Create an image and under the image properties, set
> > > it's value to that textbox (e.g., =ReportItems.txtImageLink.Value).
> > >
> > > HTH
> > >
> > > "Luis Esteban Valencia" wrote:
> > >
> > > > I want to show a different image on my header depending on some data on the
> > > > database, the path of the image could be on a table.
> > > > How can I do that?
> > > >
> > > > --
> > > > LUIS ESTEBAN VALENCIA
> > > > MICROSOFT DCE 3.
> > > > MIEMBRO ACTIVO DE ALIANZADEV
> > > > http://spaces.msn.com/members/extremed/
> > > >
> > > >
> > > >|||The SP1 readme explains why it "works" on SP1. It is something that was
specifically added to SP1:
http://download.microsoft.com/download/7/f/b/7fb1a251-13ad-404c-a034-10d79ddaa510/SP1Readme_EN.htm#_external_images
In particular, make sure you read the comment about configuring the
UnattendedExecutionAccount in the readme notes.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"prophead" <prophead@.discussions.microsoft.com> wrote in message
news:DC1075B2-4261-419D-9ABD-5ED96C5A0E2F@.microsoft.com...
> Thanks, Neo. Installing SP1 fixed the problem. Evidently there was a
> problem when you set the source to "External" and the Value to a field
> from
> the db with a URL. I also deleted the original report from the server and
> re-deployed it, so that might have fixed it as well.
> -prophead
> "Neo" wrote:
>> Prophead,
>> In my report, I have an image placed in the page header and the report
>> text
>> is placed in the body. I set the image value =>> ReportItems!txtImageLink.Value.
>> What is the value of Fields.PhotgraphLocation.Value? It doesn't look
>> like
>> the field is being properly filled in. It should be something like
>> "http://mysite/images/img.gif".
>> BTW - SRC is the property of the Img html tag. Since the field is not
>> being
>> populated correctly, it will be "" or null.
>>
>> "prophead" wrote:
>> > Neo,
>> >
>> > I tried what you suggested, but can't get it to work. I put
>> > =ReportItems!txtPhotoLoc.Value in the Value property of the Image
>> > control.
>> > The Source property is set to External. txtPhotoLoc is the name of a
>> > textbox
>> > that has its value set to =Fields!PhotographLocation.Value.
>> >
>> > Is this what you did? It doesn't work for me in either the preview or
>> > the
>> > deployed version viewed through IE.
>> >
>> > Is there something special about the "ReportItems" qualifier? I can't
>> > find
>> > that object group anywhere in my project, but it doesn't complain about
>> > it
>> > when I save and deploy it.
>> >
>> > I checked and privileges are not a problem -- the content of
>> > PhotographLocation it a fully qualified path to a URL you can paste in
>> > any
>> > browser and get an image (e.g.,
>> > http://msdn.microsoft.com/library/toolbar/3.0/images/banners/msdn_masthead_ltr.gif).
>> >
>> > When I view source on the results in IE, the area that should show the
>> > picture contains
>> > <TD COLSPAN="5" class="a55"><IMG class="r1" src="http://pics.10026.com/?src="></TD></TR>
>> > Why does SRC = ""?
>> >
>> > -prophead
>> >
>> > "Neo" wrote:
>> >
>> > > Hi Luis,
>> > >
>> > > I figured out a way to do it using Links. Create a textbox and point
>> > > it to
>> > > link stored in the dB. Create an image and under the image
>> > > properties, set
>> > > it's value to that textbox (e.g., =ReportItems.txtImageLink.Value).
>> > >
>> > > HTH
>> > >
>> > > "Luis Esteban Valencia" wrote:
>> > >
>> > > > I want to show a different image on my header depending on some
>> > > > data on the
>> > > > database, the path of the image could be on a table.
>> > > > How can I do that?
>> > > >
>> > > > --
>> > > > LUIS ESTEBAN VALENCIA
>> > > > MICROSOFT DCE 3.
>> > > > MIEMBRO ACTIVO DE ALIANZADEV
>> > > > http://spaces.msn.com/members/extremed/
>> > > >
>> > > >
>> > > >|||The notes on configuring the Unattended Execution Account are interesting,
and I can see how they would apply within a domain or intranet. Howeve, I
have a situation where my
customer is running a report on my Report Server and the photos to which the
URL's in the datafield point are on his/her secured server, which requires a
username and pwd to access?
Windows seems to store credentials for such access within user profiles
(e.g., get prompted for username and password when accessing a secure FTP
site and also get an opportunity to save the credentials). Can such
credentials be set once for the unattended report processing account for each
remote secured FTP/HTTP site that will contain photos that will be referenced?
-prophead
"Robert Bruckner [MSFT]" wrote:
> The SP1 readme explains why it "works" on SP1. It is something that was
> specifically added to SP1:
> http://download.microsoft.com/download/7/f/b/7fb1a251-13ad-404c-a034-10d79ddaa510/SP1Readme_EN.htm#_external_images
> In particular, make sure you read the comment about configuring the
> UnattendedExecutionAccount in the readme notes.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "prophead" <prophead@.discussions.microsoft.com> wrote in message
> news:DC1075B2-4261-419D-9ABD-5ED96C5A0E2F@.microsoft.com...
> > Thanks, Neo. Installing SP1 fixed the problem. Evidently there was a
> > problem when you set the source to "External" and the Value to a field
> > from
> > the db with a URL. I also deleted the original report from the server and
> > re-deployed it, so that might have fixed it as well.
> >
> > -prophead
> >
> > "Neo" wrote:
> >
> >> Prophead,
> >>
> >> In my report, I have an image placed in the page header and the report
> >> text
> >> is placed in the body. I set the image value => >> ReportItems!txtImageLink.Value.
> >>
> >> What is the value of Fields.PhotgraphLocation.Value? It doesn't look
> >> like
> >> the field is being properly filled in. It should be something like
> >> "http://mysite/images/img.gif".
> >>
> >> BTW - SRC is the property of the Img html tag. Since the field is not
> >> being
> >> populated correctly, it will be "" or null.
> >>
> >>
> >> "prophead" wrote:
> >>
> >> > Neo,
> >> >
> >> > I tried what you suggested, but can't get it to work. I put
> >> > =ReportItems!txtPhotoLoc.Value in the Value property of the Image
> >> > control.
> >> > The Source property is set to External. txtPhotoLoc is the name of a
> >> > textbox
> >> > that has its value set to =Fields!PhotographLocation.Value.
> >> >
> >> > Is this what you did? It doesn't work for me in either the preview or
> >> > the
> >> > deployed version viewed through IE.
> >> >
> >> > Is there something special about the "ReportItems" qualifier? I can't
> >> > find
> >> > that object group anywhere in my project, but it doesn't complain about
> >> > it
> >> > when I save and deploy it.
> >> >
> >> > I checked and privileges are not a problem -- the content of
> >> > PhotographLocation it a fully qualified path to a URL you can paste in
> >> > any
> >> > browser and get an image (e.g.,
> >> > http://msdn.microsoft.com/library/toolbar/3.0/images/banners/msdn_masthead_ltr.gif).
> >> >
> >> > When I view source on the results in IE, the area that should show the
> >> > picture contains
> >> > <TD COLSPAN="5" class="a55"><IMG class="r1" src="http://pics.10026.com/?src="></TD></TR>
> >> > Why does SRC = ""?
> >> >
> >> > -prophead
> >> >
> >> > "Neo" wrote:
> >> >
> >> > > Hi Luis,
> >> > >
> >> > > I figured out a way to do it using Links. Create a textbox and point
> >> > > it to
> >> > > link stored in the dB. Create an image and under the image
> >> > > properties, set
> >> > > it's value to that textbox (e.g., =ReportItems.txtImageLink.Value).
> >> > >
> >> > > HTH
> >> > >
> >> > > "Luis Esteban Valencia" wrote:
> >> > >
> >> > > > I want to show a different image on my header depending on some
> >> > > > data on the
> >> > > > database, the path of the image could be on a table.
> >> > > > How can I do that?
> >> > > >
> >> > > > --
> >> > > > LUIS ESTEBAN VALENCIA
> >> > > > MICROSOFT DCE 3.
> >> > > > MIEMBRO ACTIVO DE ALIANZADEV
> >> > > > http://spaces.msn.com/members/extremed/
> >> > > >
> >> > > >
> >> > > >
>
>|||There is only one Unattended Execution Account per report server. This
account would be used for all remote secured sites.
The closest you can get to achieve this on RS 2000 is to write your own
custom assembly to retrieve the remotely secured images. Then you would have
full control over which credentials to use when accessing certain sites.
However, you no longer have the benefit of the credentials securely managed
and stored encrypted by the report server - you have to take care of that in
the custom assembly yourself.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"prophead" <prophead@.discussions.microsoft.com> wrote in message
news:A4A36A70-583A-401E-9D9C-80D93EE224AD@.microsoft.com...
> The notes on configuring the Unattended Execution Account are interesting,
> and I can see how they would apply within a domain or intranet. Howeve, I
> have a situation where my
> customer is running a report on my Report Server and the photos to which
> the
> URL's in the datafield point are on his/her secured server, which requires
> a
> username and pwd to access?
> Windows seems to store credentials for such access within user profiles
> (e.g., get prompted for username and password when accessing a secure FTP
> site and also get an opportunity to save the credentials). Can such
> credentials be set once for the unattended report processing account for
> each
> remote secured FTP/HTTP site that will contain photos that will be
> referenced?
> -prophead
>
> "Robert Bruckner [MSFT]" wrote:
>> The SP1 readme explains why it "works" on SP1. It is something that was
>> specifically added to SP1:
>> http://download.microsoft.com/download/7/f/b/7fb1a251-13ad-404c-a034-10d79ddaa510/SP1Readme_EN.htm#_external_images
>> In particular, make sure you read the comment about configuring the
>> UnattendedExecutionAccount in the readme notes.
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "prophead" <prophead@.discussions.microsoft.com> wrote in message
>> news:DC1075B2-4261-419D-9ABD-5ED96C5A0E2F@.microsoft.com...
>> > Thanks, Neo. Installing SP1 fixed the problem. Evidently there was a
>> > problem when you set the source to "External" and the Value to a field
>> > from
>> > the db with a URL. I also deleted the original report from the server
>> > and
>> > re-deployed it, so that might have fixed it as well.
>> >
>> > -prophead
>> >
>> > "Neo" wrote:
>> >
>> >> Prophead,
>> >>
>> >> In my report, I have an image placed in the page header and the report
>> >> text
>> >> is placed in the body. I set the image value =>> >> ReportItems!txtImageLink.Value.
>> >>
>> >> What is the value of Fields.PhotgraphLocation.Value? It doesn't look
>> >> like
>> >> the field is being properly filled in. It should be something like
>> >> "http://mysite/images/img.gif".
>> >>
>> >> BTW - SRC is the property of the Img html tag. Since the field is not
>> >> being
>> >> populated correctly, it will be "" or null.
>> >>
>> >>
>> >> "prophead" wrote:
>> >>
>> >> > Neo,
>> >> >
>> >> > I tried what you suggested, but can't get it to work. I put
>> >> > =ReportItems!txtPhotoLoc.Value in the Value property of the Image
>> >> > control.
>> >> > The Source property is set to External. txtPhotoLoc is the name of
>> >> > a
>> >> > textbox
>> >> > that has its value set to =Fields!PhotographLocation.Value.
>> >> >
>> >> > Is this what you did? It doesn't work for me in either the preview
>> >> > or
>> >> > the
>> >> > deployed version viewed through IE.
>> >> >
>> >> > Is there something special about the "ReportItems" qualifier? I
>> >> > can't
>> >> > find
>> >> > that object group anywhere in my project, but it doesn't complain
>> >> > about
>> >> > it
>> >> > when I save and deploy it.
>> >> >
>> >> > I checked and privileges are not a problem -- the content of
>> >> > PhotographLocation it a fully qualified path to a URL you can paste
>> >> > in
>> >> > any
>> >> > browser and get an image (e.g.,
>> >> > http://msdn.microsoft.com/library/toolbar/3.0/images/banners/msdn_masthead_ltr.gif).
>> >> >
>> >> > When I view source on the results in IE, the area that should show
>> >> > the
>> >> > picture contains
>> >> > <TD COLSPAN="5" class="a55"><IMG class="r1" src="http://pics.10026.com/?src="></TD></TR>
>> >> > Why does SRC = ""?
>> >> >
>> >> > -prophead
>> >> >
>> >> > "Neo" wrote:
>> >> >
>> >> > > Hi Luis,
>> >> > >
>> >> > > I figured out a way to do it using Links. Create a textbox and
>> >> > > point
>> >> > > it to
>> >> > > link stored in the dB. Create an image and under the image
>> >> > > properties, set
>> >> > > it's value to that textbox (e.g.,
>> >> > > =ReportItems.txtImageLink.Value).
>> >> > >
>> >> > > HTH
>> >> > >
>> >> > > "Luis Esteban Valencia" wrote:
>> >> > >
>> >> > > > I want to show a different image on my header depending on some
>> >> > > > data on the
>> >> > > > database, the path of the image could be on a table.
>> >> > > > How can I do that?
>> >> > > >
>> >> > > > --
>> >> > > > LUIS ESTEBAN VALENCIA
>> >> > > > MICROSOFT DCE 3.
>> >> > > > MIEMBRO ACTIVO DE ALIANZADEV
>> >> > > > http://spaces.msn.com/members/extremed/
>> >> > > >
>> >> > > >
>> >> > > >
>>

how to load image to and retrieve from database(sql server)

can anyone out there help me to solve this problem?

how to load image to and retrieve from database(sql server)??
thanks alot

cyndiehttp://www.123aspx.com

They have a couple of articles on the subject.|||I am biased but I think that's a great site for ASP.NET information :-)

You should also search this forum for BLOB and you will see lots of posts on this topic.

Terri|||Hi, there is a good article titled "Load Images from and Save Images to a Database".

Check the below link.

http://www.codeguru.com/vb/vb_internet/database/article.php/c7427/

How to load Fact table and dimension using SSIS?

Hi All,

I am just curious to know how I can load data from a data warehouse to an Analysis Service Cube (both to the fact tables and dimensions).

Does any body have some way to achieve this?

I appreciate if any body provide me a good material which describe this scenario.

Sincerely,

--Amde

Amde wrote:

Hi All,

I am just curious to know how I can load data from a data warehouse to an Analysis Service Cube (both to the fact tables and dimensions).

Does any body have some way to achieve this?

I appreciate if any body provide me a good material which describe this scenario.

Sincerely,

--Amde

this link should help: http://www.microsoft.com/technet/prodtechnol/sql/2005/rtbissas.mspx

How to load database name at runtime?

I m creating a report using data view.
its working fine. BUt i want to load db name at run time, I tried a sample and which does it very fine.

But when i use the same code in my project, its not loading the database name at runtime, old name is being used.

What i m doing is like this in visual basic.net

rptCustomersOrders.Load("..\CustomerOrders.rpt")

' Set the connection information for all the tables used in the report
' Leave UserID and Password blank for trusted connection
For Each tbCurrent In rptCustomersOrders.Database.Tables
tliCurrent = tbCurrent.LogOnInfo
With tliCurrent.ConnectionInfo
.ServerName = ServerName
.UserID = ""
.Password = ""
.DatabaseName = "Northwind"
End With
tbCurrent.ApplyLogOnInfo(tliCurrent)
Next tbCurrent

ReportViewer.ReportSource = rptCustomersOrders

Plz. tell me ; what property i have to set, to load the database name at runtime.

Thanks in adavancehttp://www.dev-archive.com/forum/archive/index.php/t-293276.html|||try adding

.location = .name

after

.ServerName = ServerName
.UserID = ""
.Password = ""
.DatabaseName = "Northwind"

so that it will erase previous location details ....

if u have any other methords ... plzz let me know too dude .........|||Here's my code which you can use to change Database Name, User Name, Password and SQL Server Name at run time. This code is written in VB6 and works with Crystal Reports 10.

Copy this code in a Module in VB and I used frm Report where I have placed crystal report viewer control.

Public Sub DisplayReport(ReportFileName As String)
Dim app2 As CRAXDRT.Application
Dim rap As CRAXDRT.Report

Set app2 = New CRAXDRT.Application
Set rap = New CRAXDRT.Report
Set rap = app2.OpenReport(ReportFileName)

rap.EnableParameterPrompting = False

For Each CRXDatabaseTable In rap.Database.Tables
STORED_DATABASE_NAME = CRXDatabaseTable.ConnectionProperties("INITIAL CATALOG") ''JUST TO READ DATABASE NAME STORED IN CRYSTAL REPORT FILE
Exit For
Next

For Each CRXDatabaseTable In rap.Database.Tables
CRXDatabaseTable.ConnectionProperties("Data Source") = SQLServerName
CRXDatabaseTable.ConnectionProperties("INITIAL CATALOG") = DatabaseName
CRXDatabaseTable.ConnectionProperties("USER ID") = UserName
CRXDatabaseTable.ConnectionProperties("PASSWORD") = LoginPassword
If Not CRXDatabaseTable.TestConnectivity Then
MsgBox "Error connecting to database table." & vbCrLf & "Please contact Adminstrator to validate Report Database"
Exit Sub
End If
Next

rap.EnableParameterPrompting = True
On Error Resume Next
Ret = rap.SQLQueryString 'THIS WILL CALL USER TO INPUT PARAMETERS FOR REPORT (IF ANY)
On Error GoTo 0

If Err.Number = -2147206395 Then 'CHECK IF USER PRESSED CANCEL
Err.Clear
Exit Sub
ElseIf Err.Number <> 0 Then 'CHECK IF ANY OTHER ERROR OCCURED
MsgBox Err.Number & " : " & Err.Description
Err.Clear
Exit Sub
End If

If UCase(STORED_DATABASE_NAME) <> UCase(DatabaseName) Then
''IF DATABASE WHILE CREATING REPORT WAS DIFFERENT THEN THE CURRENT DATABASE.
''UPDATE DATABASE NAME IN CONNECTION STRING, ELSE CONNECTION STRING READS DATA FROM DATABASE USED WHILE CREATING REPORT
DoEvents
rap.EnableParameterPrompting = False
Ret = rap.SQLQueryString
Ret = Replace(Ret, STORED_DATABASE_NAME, DatabaseName)
rap.SQLQueryString = Ret
rap.SQLQueryString = Ret ''IT DOESNOT WORK IF I SET THIS ONCE (PARAMETERS ARE NOT SET). :-( CRYSTAL BUG
DoEvents
End If

frmReport.Show
frmReport.CrystalActiveXReportViewer1.ReportSource = rap
frmReport.CrystalActiveXReportViewer1.ViewReport
frmReport.CrystalActiveXReportViewer1.Refresh
End Sub|||Sorry forget to add this declaration in my above function:

Dim CRXDatabaseTable As CRAXDRT.DatabaseTable

How to load data into a table using a synonym?

I'd like to use a data flow task to load data into a table by specifying the synonym name of the destination table, instead of the actual table name.

The OLE DB Destination is forcing me to pick an actual table or view from a drop down list. Any ideas on how to get around this?

Thank you.

This will be fixed in SP1, so that synonyms also appear in the UI in the drop down list. For RTM, the workaround is to use SqlCommand access mode, and then if you specify "select * from synonymTable", that will work.

|||

Using a SELECT statement (which is what Ranjeeta has said) is what you should be doing anyway - don't use the drop-down.

Simon explains why here: http://sqljunkies.com/WebLog/simons/archive/2006/01/20/17865.aspx

-Jamie

|||Jamie,

Is that your recommendation even for OLE DB Destinations? I can

certainly understand why you would recommend this for Sources/Lookups,

but for a destination it seems odd because you lose some options

(fast-load, keep identity, check constraints,etc) when using a SQL

statement.

Also do know or have experienced the "feature" or "bug" that Simon describes as being applicable to the OLE DB Destination?

Thanks,

Larry Pope|||

Larry,

No, I was talking about source adapters only.

-Jamie

How to load data from a data warehouse to a Cube ?

Hi All,

I am just curious to know how I can load data from a data warehouse to an Analysis Service Cube (both to the fact tables and dimensions). Whenever there is a change in the data warehouse, it should be reflected to the fact table and dimension

Does any body have some way to achieve this?

I appreciate if any body provide me a good material which describe this scenario.

Sincerely,

--Amde

Hi Amde,

You should process your cube, every time that you have news records in your fact table. If you have data every month, for example, then you could use partitions cubes by month.

Good coding!

Javier Luna
http://guydotnetxmlwebservices.blogspot.com/

|||

You mean I can load the data automatically using partitions cubes? Because, the thing is we are developing a product that will be delivered to a client. So in this case the user is not supposed to use the analysis service environment to proceess the cube, instead I want to schedule the process so that it will automatically populate the cube with the new records.

Would you please send me a link which talks about this scenario?

Thanks

--Amde

|||

If you open Analysis Services 2005 in management studio and expand the database with the cubes and dimension you want to schedule, you do the following.

Right click on a dimension and choose process. In the dialoge you are presented with you are able to script this command to a file or the clipboard.

After this you expand the SQL Server Agent in SQL Server 2005. Under this you expand job and choose new job(right click). After you choose new step. In this dialoge you paste the code in to the large window att the bottom. Do not forget to change the type of the command to an Analysis Services command.

Repeat this process for each dimension and finally each cube.

When the job is complete you need to add some parameters and command for the job.

You can also build a workflow for processing cubes in SSIS. Perhaps this is easier.

HTH

Thomas Ivarsson

|||

I understand what you are saying. I want to confirm one thing. Let's say we have 6 dimension, we need to create 6 jobs and schedule them at the same time. Am I right saying that?

--Amde

How to load any file into FTP server through Javascript.

Dear all,
Iam with a small problem.
I want to access to FTP server through Html page by entering a username
and password.In the same html page I have to slect a file through
browse button and load it into FTP server.
PLz help me with the code also.
All this should be in Javascript.
Any help is appreciated...
Thanks a lot...~!~!~!
Bye
Hi
If this is ASP then you can use one of the products mentioned on
http://www.aspfaq.com/show.asp?id=2189.
This is not really an XML or SQL Server question so posting in a more
appropriate group may be an idea!
John
"vinodh" <vinodh.singh@.gmail.com> wrote in message
news:1126834578.588797.184610@.f14g2000cwb.googlegr oups.com...
> Dear all,
>
> Iam with a small problem.
> I want to access to FTP server through Html page by entering a username
> and password.In the same html page I have to slect a file through
> browse button and load it into FTP server.
> PLz help me with the code also.
> All this should be in Javascript.
> Any help is appreciated...
>
> Thanks a lot...~!~!~!
> Bye
>

How to load any file into FTP server through Javascript.

Dear all,
Iam with a small problem.
I want to access to FTP server through Html page by entering a username
and password.In the same html page I have to slect a file through
browse button and load it into FTP server.
PLz help me with the code also.
All this should be in Javascript.
Any help is appreciated...
Thanks a lot...~!~!~!
ByeHi
If this is ASP then you can use one of the products mentioned on
http://www.aspfaq.com/show.asp?id=2189.
This is not really an XML or SQL Server question so posting in a more
appropriate group may be an idea!
John
"vinodh" <vinodh.singh@.gmail.com> wrote in message
news:1126834578.588797.184610@.f14g2000cwb.googlegroups.com...
> Dear all,
>
> Iam with a small problem.
> I want to access to FTP server through Html page by entering a username
> and password.In the same html page I have to slect a file through
> browse button and load it into FTP server.
> PLz help me with the code also.
> All this should be in Javascript.
> Any help is appreciated...
>
> Thanks a lot...~!~!~!
> Bye
>

How to load a Unicode file into the database in the same order as the file order

The data file is a simple Unicode file with lines of text. BCP
apparently doesn't guarantee this ordering, and neither does the
import tool. I want to be able to load the data either sequentially or
add line numbering to large Unicode file (1 million lines). I don't
want to deal with another programming language if possible and I
wonder if there's a trick in SQL Server to get this accomplished.
Thanks for any help.
Mark Leary
--== Posted via webservertalk.com - Unlimited-Uncensored-Secure Usenet News==--
http://www.webservertalk.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
--= East/West-Coast Server Farms - Total Privacy via Encryption =--
no-email wrote:
> The data file is a simple Unicode file with lines of text. BCP
> apparently doesn't guarantee this ordering, and neither does the
> import tool. I want to be able to load the data either sequentially or
> add line numbering to large Unicode file (1 million lines). I don't
> want to deal with another programming language if possible and I
> wonder if there's a trick in SQL Server to get this accomplished.
> Thanks for any help.
> Mark Leary
>
Why does the order of the rows inserted into the table matter in your
case? Relational databases don't understand row order. If you need them
sorted in some way after the import, you can create a clustered index on
the table to get the rows ordered in a way that helps your queries
perform better.
In general, I think BCP processes the rows in the file sequentially. But
again, I'm not clear on why this matters.
Could you elaborate on the exact issue you are trying to avoid.
David Gugick
Imceda Software
www.imceda.com
|||"David Gugick" <davidg-nospam@.imceda.com> wrote:

> Why does the order of the rows inserted into the table matter in your
> case? Relational databases don't understand row order. If you need them
> sorted in some way after the import, you can create a clustered index on
> the table to get the rows ordered in a way that helps your queries perform
> better.
> In general, I think BCP processes the rows in the file sequentially. But
> again, I'm not clear on why this matters.
> Could you elaborate on the exact issue you are trying to avoid.
I am trying to load a text file sequentially in order to perform text
manipulations using T-SQL that do depend on the exact order. I would be
happy with simply adding a line number to each line of the Unicode text
file, and then loading the file with line number determining the order, but
I want to avoid programming in another language if possible. Eventually the
loaded text would be converted to proper relational tables. This doesn't
have to do with improving performance. Does this help?
Thanks.
|||no-email wrote:
> "David Gugick" <davidg-nospam@.imceda.com> wrote:
>
> I am trying to load a text file sequentially in order to perform text
> manipulations using T-SQL that do depend on the exact order. I would
> be happy with simply adding a line number to each line of the Unicode
> text file, and then loading the file with line number determining the
> order, but I want to avoid programming in another language if
> possible. Eventually the loaded text would be converted to proper
> relational tables. This doesn't have to do with improving
> performance. Does this help?
> Thanks.
Yes. It sounds like you have rows in a specific order that will need to
be processed once on SQL Server. You want to preserve the order of rows
in the file so the rows can be processed in the same order once on SQL
Server.
In order to do this in any relational database, you need a sort key.
There is never a guarantee that a query you run without an ORDER BY will
return rows in the same order in any consistent way.
My understanding is that BCP feeds the rows in the order they appear in
a file. I can't imagine any reason it would or could do it differently.
In that case, you want to insert the data into a table that contains an
IDENTITY column. You can then use that key for your ORDER BY when
processing the rows from whatever process does that.
David Gugick
Imceda Software
www.imceda.com
|||Do you know what order the source file is sorted in? If so, and if the
sort order column(s) are included then you may not need to know the
line number since it is (theoretically anyway) possible to derive that
information from the other data.
If not, then this article has a useful suggestion:
http://www.google.co.uk/groups?selm=...GP11.phx.gb l
not sure if that will work with unicode data though.
David Portas
SQL Server MVP
|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in
message:

> Do you know what order the source file is sorted in? If so, and if
the
> sort order column(s) are included then you may not need to know the
> line number since it is (theoretically anyway) possible to derive
that
> information from the other data.
Unfortunately the data file consists of simple lines of text with no
other way to extract potential column information before loading it
into the database.

> If not, then this article has a useful suggestion:
>
http://www.google.co.uk/groups?selm=...GP11.phx.gb l
> not sure if that will work with unicode data though.
Good suggestion but it does fail with Unicode. Thanks anyway.
--== Posted via webservertalk.com - Unlimited-Uncensored-Secure Usenet News==--
http://www.webservertalk.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
--= East/West-Coast Server Farms - Total Privacy via Encryption =--
|||"David Gugick" <davidg-nospam@.imceda.com> wrote:

> My understanding is that BCP feeds the rows in the order they appear
in
> a file. I can't imagine any reason it would or could do it
differently.
> In that case, you want to insert the data into a table that contains
an
> IDENTITY column. You can then use that key for your ORDER BY when
> processing the rows from whatever process does that.
In general BCP loads the data in the same order as the file but not
always. The ordering sometimes reverses for thousands of rows, or
skips certain rows, but you need to check it carefully to find the
misordering. You can create a table with an identity column and load
the data, but again if the rows are not loaded in the same sequence as
the file this won't matter. You will end up with an ordered table that
is unfortunately not in the same order as the original file.
To be honest I have tried all these suggestions in the past. My
typical solution would be to open the original file in Excel, add a
rownumber column and then save the resulting file as a Unicode file.
This works up until around a maximum of 63,000 rows. You can break a
file into 63,000 row subfiles, but this would be too tedious if you
have row counts approaching a million.
Thanks for the suggestions but I may have to learn some C#.
Unfortunately Visual Basic has problems with Unicode, as I suspect
also C++ and C have similar problems.
--== Posted via webservertalk.com - Unlimited-Uncensored-Secure Usenet News==--
http://www.webservertalk.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
--= East/West-Coast Server Farms - Total Privacy via Encryption =--
|||What about using the CTS Import Wizard to import the data from the flat
file into a table with an identity.
Where is this data coming from? Is there any way to recreate it with a
counter column included in the output?
David Gugick
Imceda Software
www.imceda.com
|||"David Gugick" <davidg-nospam@.imceda.com> wrote:

> What about using the CTS Import Wizard to import the data from the
flat
> file into a table with an identity.
It's the same problem. The table order generally follows the order in
the file but not always.

> Where is this data coming from? Is there any way to recreate it with
a
> counter column included in the output?
It's foreign language dictionary data that cannot be recreated. I
could manipulate the data on the file level but I am trying to avoid
potential problems with Unicode. Once the data gets into SQL Server I
don't have any problems, as long as the table order exactly matches
the file order.
--== Posted via webservertalk.com - Unlimited-Uncensored-Secure Usenet News==--
http://www.webservertalk.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
--= East/West-Coast Server Farms - Total Privacy via Encryption =--
|||no-email wrote:
> It's foreign language dictionary data that cannot be recreated. I
> could manipulate the data on the file level but I am trying to avoid
> potential problems with Unicode. Once the data gets into SQL Server I
> don't have any problems, as long as the table order exactly matches
> the file order.
I'm not sure what problems you would have as long as the tool you are
using to edit the data is unicode aware. I use TextEdit for editing
(www.textpad.com) and it has simple replacement expressions.
Assuming you had each row of data on a single line, you could simply do
the following:
1- Add a leading CARRIAGE RETURN to the file
2- Open the Replace dialog
3- Check the Regular Expression option
4- Type "\n" - WITHOUT QUOTES in the Find What entry- means New Line
character
5- Type "\n\i\t" - WITHOUT QUOTES in the Replace With entry - means New
Line + Auto Number + TAB
6- Click Replace All
7 - Remove the leading carriage return in the file
8 - Click FILE SAVE AS and make sure the UNICODE option is selected
You can replace the TAB character with whatever your file requires or
add DOUBLE QUOTES around the Auto Number, etc.
You can download a free trial of TextPad on the web site.
David Gugick
Imceda Software
www.imceda.com

How to load a Unicode file into the database in the same order as the file order

The data file is a simple Unicode file with lines of text. BCP
apparently doesn't guarantee this ordering, and neither does the
import tool. I want to be able to load the data either sequentially or
add line numbering to large Unicode file (1 million lines). I don't
want to deal with another programming language if possible and I
wonder if there's a trick in SQL Server to get this accomplished.
Thanks for any help.
Mark Leary
--== Posted via webservertalk.com - Unlimited-Uncensored-Secure Usenet News==--
http://www.webservertalk.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
--= East/West-Coast Server Farms - Total Privacy via Encryption =--
no-email wrote:
> The data file is a simple Unicode file with lines of text. BCP
> apparently doesn't guarantee this ordering, and neither does the
> import tool. I want to be able to load the data either sequentially or
> add line numbering to large Unicode file (1 million lines). I don't
> want to deal with another programming language if possible and I
> wonder if there's a trick in SQL Server to get this accomplished.
> Thanks for any help.
> Mark Leary
>
Why does the order of the rows inserted into the table matter in your
case? Relational databases don't understand row order. If you need them
sorted in some way after the import, you can create a clustered index on
the table to get the rows ordered in a way that helps your queries
perform better.
In general, I think BCP processes the rows in the file sequentially. But
again, I'm not clear on why this matters.
Could you elaborate on the exact issue you are trying to avoid.
David Gugick
Imceda Software
www.imceda.com
|||"David Gugick" <davidg-nospam@.imceda.com> wrote:

> Why does the order of the rows inserted into the table matter in your
> case? Relational databases don't understand row order. If you need them
> sorted in some way after the import, you can create a clustered index on
> the table to get the rows ordered in a way that helps your queries perform
> better.
> In general, I think BCP processes the rows in the file sequentially. But
> again, I'm not clear on why this matters.
> Could you elaborate on the exact issue you are trying to avoid.
I am trying to load a text file sequentially in order to perform text
manipulations using T-SQL that do depend on the exact order. I would be
happy with simply adding a line number to each line of the Unicode text
file, and then loading the file with line number determining the order, but
I want to avoid programming in another language if possible. Eventually the
loaded text would be converted to proper relational tables. This doesn't
have to do with improving performance. Does this help?
Thanks.
|||no-email wrote:
> "David Gugick" <davidg-nospam@.imceda.com> wrote:
>
> I am trying to load a text file sequentially in order to perform text
> manipulations using T-SQL that do depend on the exact order. I would
> be happy with simply adding a line number to each line of the Unicode
> text file, and then loading the file with line number determining the
> order, but I want to avoid programming in another language if
> possible. Eventually the loaded text would be converted to proper
> relational tables. This doesn't have to do with improving
> performance. Does this help?
> Thanks.
Yes. It sounds like you have rows in a specific order that will need to
be processed once on SQL Server. You want to preserve the order of rows
in the file so the rows can be processed in the same order once on SQL
Server.
In order to do this in any relational database, you need a sort key.
There is never a guarantee that a query you run without an ORDER BY will
return rows in the same order in any consistent way.
My understanding is that BCP feeds the rows in the order they appear in
a file. I can't imagine any reason it would or could do it differently.
In that case, you want to insert the data into a table that contains an
IDENTITY column. You can then use that key for your ORDER BY when
processing the rows from whatever process does that.
David Gugick
Imceda Software
www.imceda.com
|||Do you know what order the source file is sorted in? If so, and if the
sort order column(s) are included then you may not need to know the
line number since it is (theoretically anyway) possible to derive that
information from the other data.
If not, then this article has a useful suggestion:
http://www.google.co.uk/groups?selm=...GP11.phx.gb l
not sure if that will work with unicode data though.
David Portas
SQL Server MVP
|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in
message:

> Do you know what order the source file is sorted in? If so, and if
the
> sort order column(s) are included then you may not need to know the
> line number since it is (theoretically anyway) possible to derive
that
> information from the other data.
Unfortunately the data file consists of simple lines of text with no
other way to extract potential column information before loading it
into the database.

> If not, then this article has a useful suggestion:
>
http://www.google.co.uk/groups?selm=...GP11.phx.gb l
> not sure if that will work with unicode data though.
Good suggestion but it does fail with Unicode. Thanks anyway.
--== Posted via webservertalk.com - Unlimited-Uncensored-Secure Usenet News==--
http://www.webservertalk.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
--= East/West-Coast Server Farms - Total Privacy via Encryption =--
|||"David Gugick" <davidg-nospam@.imceda.com> wrote:

> My understanding is that BCP feeds the rows in the order they appear
in
> a file. I can't imagine any reason it would or could do it
differently.
> In that case, you want to insert the data into a table that contains
an
> IDENTITY column. You can then use that key for your ORDER BY when
> processing the rows from whatever process does that.
In general BCP loads the data in the same order as the file but not
always. The ordering sometimes reverses for thousands of rows, or
skips certain rows, but you need to check it carefully to find the
misordering. You can create a table with an identity column and load
the data, but again if the rows are not loaded in the same sequence as
the file this won't matter. You will end up with an ordered table that
is unfortunately not in the same order as the original file.
To be honest I have tried all these suggestions in the past. My
typical solution would be to open the original file in Excel, add a
rownumber column and then save the resulting file as a Unicode file.
This works up until around a maximum of 63,000 rows. You can break a
file into 63,000 row subfiles, but this would be too tedious if you
have row counts approaching a million.
Thanks for the suggestions but I may have to learn some C#.
Unfortunately Visual Basic has problems with Unicode, as I suspect
also C++ and C have similar problems.
--== Posted via webservertalk.com - Unlimited-Uncensored-Secure Usenet News==--
http://www.webservertalk.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
--= East/West-Coast Server Farms - Total Privacy via Encryption =--
|||What about using the CTS Import Wizard to import the data from the flat
file into a table with an identity.
Where is this data coming from? Is there any way to recreate it with a
counter column included in the output?
David Gugick
Imceda Software
www.imceda.com
|||"David Gugick" <davidg-nospam@.imceda.com> wrote:

> What about using the CTS Import Wizard to import the data from the
flat
> file into a table with an identity.
It's the same problem. The table order generally follows the order in
the file but not always.

> Where is this data coming from? Is there any way to recreate it with
a
> counter column included in the output?
It's foreign language dictionary data that cannot be recreated. I
could manipulate the data on the file level but I am trying to avoid
potential problems with Unicode. Once the data gets into SQL Server I
don't have any problems, as long as the table order exactly matches
the file order.
--== Posted via webservertalk.com - Unlimited-Uncensored-Secure Usenet News==--
http://www.webservertalk.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
--= East/West-Coast Server Farms - Total Privacy via Encryption =--
|||no-email wrote:
> It's foreign language dictionary data that cannot be recreated. I
> could manipulate the data on the file level but I am trying to avoid
> potential problems with Unicode. Once the data gets into SQL Server I
> don't have any problems, as long as the table order exactly matches
> the file order.
I'm not sure what problems you would have as long as the tool you are
using to edit the data is unicode aware. I use TextEdit for editing
(www.textpad.com) and it has simple replacement expressions.
Assuming you had each row of data on a single line, you could simply do
the following:
1- Add a leading CARRIAGE RETURN to the file
2- Open the Replace dialog
3- Check the Regular Expression option
4- Type "\n" - WITHOUT QUOTES in the Find What entry- means New Line
character
5- Type "\n\i\t" - WITHOUT QUOTES in the Replace With entry - means New
Line + Auto Number + TAB
6- Click Replace All
7 - Remove the leading carriage return in the file
8 - Click FILE SAVE AS and make sure the UNICODE option is selected
You can replace the TAB character with whatever your file requires or
add DOUBLE QUOTES around the Auto Number, etc.
You can download a free trial of TextPad on the web site.
David Gugick
Imceda Software
www.imceda.com

How to load a Unicode file into the database in the same order as the file order

The data file is a simple Unicode file with lines of text. BCP
apparently doesn't guarantee this ordering, and neither does the
import tool. I want to be able to load the data either sequentially or
add line numbering to large Unicode file (1 million lines). I don't
want to deal with another programming language if possible and I
wonder if there's a trick in SQL Server to get this accomplished.
Thanks for any help.
Mark Leary
--== Posted via webservertalk.com - Unlimited-Uncensored-Secure Usenet New
s==--
http://www.webservertalk.com The #1 Newsgroup Service in the World! >100,000
Newsgroups
--= East/West-Coast Server Farms - Total Privacy via Encryption =--no-email wrote:
> The data file is a simple Unicode file with lines of text. BCP
> apparently doesn't guarantee this ordering, and neither does the
> import tool. I want to be able to load the data either sequentially or
> add line numbering to large Unicode file (1 million lines). I don't
> want to deal with another programming language if possible and I
> wonder if there's a trick in SQL Server to get this accomplished.
> Thanks for any help.
> Mark Leary
>
Why does the order of the rows inserted into the table matter in your
case? Relational databases don't understand row order. If you need them
sorted in some way after the import, you can create a clustered index on
the table to get the rows ordered in a way that helps your queries
perform better.
In general, I think BCP processes the rows in the file sequentially. But
again, I'm not clear on why this matters.
Could you elaborate on the exact issue you are trying to avoid.
David Gugick
Imceda Software
www.imceda.com|||"David Gugick" <davidg-nospam@.imceda.com> wrote:

> Why does the order of the rows inserted into the table matter in your
> case? Relational databases don't understand row order. If you need them
> sorted in some way after the import, you can create a clustered index on
> the table to get the rows ordered in a way that helps your queries perform
> better.
> In general, I think BCP processes the rows in the file sequentially. But
> again, I'm not clear on why this matters.
> Could you elaborate on the exact issue you are trying to avoid.
I am trying to load a text file sequentially in order to perform text
manipulations using T-SQL that do depend on the exact order. I would be
happy with simply adding a line number to each line of the Unicode text
file, and then loading the file with line number determining the order, but
I want to avoid programming in another language if possible. Eventually the
loaded text would be converted to proper relational tables. This doesn't
have to do with improving performance. Does this help?
Thanks.|||no-email wrote:
> "David Gugick" <davidg-nospam@.imceda.com> wrote:
>
> I am trying to load a text file sequentially in order to perform text
> manipulations using T-SQL that do depend on the exact order. I would
> be happy with simply adding a line number to each line of the Unicode
> text file, and then loading the file with line number determining the
> order, but I want to avoid programming in another language if
> possible. Eventually the loaded text would be converted to proper
> relational tables. This doesn't have to do with improving
> performance. Does this help?
> Thanks.
Yes. It sounds like you have rows in a specific order that will need to
be processed once on SQL Server. You want to preserve the order of rows
in the file so the rows can be processed in the same order once on SQL
Server.
In order to do this in any relational database, you need a sort key.
There is never a guarantee that a query you run without an ORDER BY will
return rows in the same order in any consistent way.
My understanding is that BCP feeds the rows in the order they appear in
a file. I can't imagine any reason it would or could do it differently.
In that case, you want to insert the data into a table that contains an
IDENTITY column. You can then use that key for your ORDER BY when
processing the rows from whatever process does that.
David Gugick
Imceda Software
www.imceda.com|||Do you know what order the source file is sorted in? If so, and if the
sort order column(s) are included then you may not need to know the
line number since it is (theoretically anyway) possible to derive that
information from the other data.
If not, then this article has a useful suggestion:
http://www.google.co.uk/groups?selm...FTNGP11.phx.gbl
not sure if that will work with unicode data though.
--
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in
message:

> Do you know what order the source file is sorted in? If so, and if
the
> sort order column(s) are included then you may not need to know the
> line number since it is (theoretically anyway) possible to derive
that
> information from the other data.
Unfortunately the data file consists of simple lines of text with no
other way to extract potential column information before loading it
into the database.

> If not, then this article has a useful suggestion:
>
http://www.google.co.uk/groups?selm...FTNGP11.phx.gbl
> not sure if that will work with unicode data though.
Good suggestion but it does fail with Unicode. Thanks anyway.
--== Posted via webservertalk.com - Unlimited-Uncensored-Secure Usenet New
s==--
http://www.webservertalk.com The #1 Newsgroup Service in the World! >100,000
Newsgroups
--= East/West-Coast Server Farms - Total Privacy via Encryption =--|||"David Gugick" <davidg-nospam@.imceda.com> wrote:

> My understanding is that BCP feeds the rows in the order they appear
in
> a file. I can't imagine any reason it would or could do it
differently.
> In that case, you want to insert the data into a table that contains
an
> IDENTITY column. You can then use that key for your ORDER BY when
> processing the rows from whatever process does that.
In general BCP loads the data in the same order as the file but not
always. The ordering sometimes reverses for thousands of rows, or
skips certain rows, but you need to check it carefully to find the
misordering. You can create a table with an identity column and load
the data, but again if the rows are not loaded in the same sequence as
the file this won't matter. You will end up with an ordered table that
is unfortunately not in the same order as the original file.
To be honest I have tried all these suggestions in the past. My
typical solution would be to open the original file in Excel, add a
rownumber column and then save the resulting file as a Unicode file.
This works up until around a maximum of 63,000 rows. You can break a
file into 63,000 row subfiles, but this would be too tedious if you
have row counts approaching a million.
Thanks for the suggestions but I may have to learn some C#.
Unfortunately Visual Basic has problems with Unicode, as I suspect
also C++ and C have similar problems.
--== Posted via webservertalk.com - Unlimited-Uncensored-Secure Usenet New
s==--
http://www.webservertalk.com The #1 Newsgroup Service in the World! >100,000
Newsgroups
--= East/West-Coast Server Farms - Total Privacy via Encryption =--|||no-email wrote:
> It's foreign language dictionary data that cannot be recreated. I
> could manipulate the data on the file level but I am trying to avoid
> potential problems with Unicode. Once the data gets into SQL Server I
> don't have any problems, as long as the table order exactly matches
> the file order.
I'm not sure what problems you would have as long as the tool you are
using to edit the data is unicode aware. I use TextEdit for editing
(www.textpad.com) and it has simple replacement expressions.
Assuming you had each row of data on a single line, you could simply do
the following:
1- Add a leading CARRIAGE RETURN to the file
2- Open the Replace dialog
3- Check the Regular Expression option
4- Type "\n" - WITHOUT QUOTES in the Find What entry- means New Line
character
5- Type "\n\i\t" - WITHOUT QUOTES in the Replace With entry - means New
Line + Auto Number + TAB
6- Click Replace All
7 - Remove the leading carriage return in the file
8 - Click FILE SAVE AS and make sure the UNICODE option is selected
You can replace the TAB character with whatever your file requires or
add DOUBLE QUOTES around the Auto Number, etc.
You can download a free trial of TextPad on the web site.
David Gugick
Imceda Software
www.imceda.com|||"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:#ZNRubc$EHA.4004@.tk2msftngp13.phx.gbl...
> no-email wrote:
avoid[vbcol=seagreen]
Server I[vbcol=seagreen]
matches[vbcol=seagreen]
> I'm not sure what problems you would have as long as the tool you
are
> using to edit the data is unicode aware. I use TextEdit for editing
> (www.textpad.com) and it has simple replacement expressions.
Let me give it a try. With Word it just locks up after a few minutes
and dies. Notepad is way too slow on the replacements. I have 512 Meg
of memory but I still have problems. I'll try textpad.
Thanks for the suggestion.|||"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:#ZNRubc$EHA.4004@.tk2msftngp13.phx.gbl...
> no-email wrote:
avoid[vbcol=seagreen]
Server I[vbcol=seagreen]
matches[vbcol=seagreen]
> I'm not sure what problems you would have as long as the tool you
are
> using to edit the data is unicode aware. I use TextEdit for editing
> (www.textpad.com) and it has simple replacement expressions.
I tried it but it doesn't properly handle Unicode.
Thanks anyway.

How to load a Unicode file into the database in the same order as the file order

The data file is a simple Unicode file with lines of text. BCP
apparently doesn't guarantee this ordering, and neither does the
import tool. I want to be able to load the data either sequentially or
add line numbering to large Unicode file (1 million lines). I don't
want to deal with another programming language if possible and I
wonder if there's a trick in SQL Server to get this accomplished.
Thanks for any help.
Mark Leary
--== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==--
http://www.newsfeeds.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
--= East/West-Coast Server Farms - Total Privacy via Encryption =--no-email wrote:
> The data file is a simple Unicode file with lines of text. BCP
> apparently doesn't guarantee this ordering, and neither does the
> import tool. I want to be able to load the data either sequentially or
> add line numbering to large Unicode file (1 million lines). I don't
> want to deal with another programming language if possible and I
> wonder if there's a trick in SQL Server to get this accomplished.
> Thanks for any help.
> Mark Leary
>
Why does the order of the rows inserted into the table matter in your
case? Relational databases don't understand row order. If you need them
sorted in some way after the import, you can create a clustered index on
the table to get the rows ordered in a way that helps your queries
perform better.
In general, I think BCP processes the rows in the file sequentially. But
again, I'm not clear on why this matters.
Could you elaborate on the exact issue you are trying to avoid.
David Gugick
Imceda Software
www.imceda.com|||"David Gugick" <davidg-nospam@.imceda.com> wrote:
> Why does the order of the rows inserted into the table matter in your
> case? Relational databases don't understand row order. If you need them
> sorted in some way after the import, you can create a clustered index on
> the table to get the rows ordered in a way that helps your queries perform
> better.
> In general, I think BCP processes the rows in the file sequentially. But
> again, I'm not clear on why this matters.
> Could you elaborate on the exact issue you are trying to avoid.
I am trying to load a text file sequentially in order to perform text
manipulations using T-SQL that do depend on the exact order. I would be
happy with simply adding a line number to each line of the Unicode text
file, and then loading the file with line number determining the order, but
I want to avoid programming in another language if possible. Eventually the
loaded text would be converted to proper relational tables. This doesn't
have to do with improving performance. Does this help?
Thanks.|||no-email wrote:
> "David Gugick" <davidg-nospam@.imceda.com> wrote:
>> Why does the order of the rows inserted into the table matter in your
>> case? Relational databases don't understand row order. If you need
>> them sorted in some way after the import, you can create a clustered
>> index on the table to get the rows ordered in a way that helps your
>> queries perform better.
>> In general, I think BCP processes the rows in the file sequentially.
>> But again, I'm not clear on why this matters.
>> Could you elaborate on the exact issue you are trying to avoid.
> I am trying to load a text file sequentially in order to perform text
> manipulations using T-SQL that do depend on the exact order. I would
> be happy with simply adding a line number to each line of the Unicode
> text file, and then loading the file with line number determining the
> order, but I want to avoid programming in another language if
> possible. Eventually the loaded text would be converted to proper
> relational tables. This doesn't have to do with improving
> performance. Does this help?
> Thanks.
Yes. It sounds like you have rows in a specific order that will need to
be processed once on SQL Server. You want to preserve the order of rows
in the file so the rows can be processed in the same order once on SQL
Server.
In order to do this in any relational database, you need a sort key.
There is never a guarantee that a query you run without an ORDER BY will
return rows in the same order in any consistent way.
My understanding is that BCP feeds the rows in the order they appear in
a file. I can't imagine any reason it would or could do it differently.
In that case, you want to insert the data into a table that contains an
IDENTITY column. You can then use that key for your ORDER BY when
processing the rows from whatever process does that.
David Gugick
Imceda Software
www.imceda.com|||Do you know what order the source file is sorted in? If so, and if the
sort order column(s) are included then you may not need to know the
line number since it is (theoretically anyway) possible to derive that
information from the other data.
If not, then this article has a useful suggestion:
http://www.google.co.uk/groups?selm=uKOCiqtDEHA.1604%40TK2MSFTNGP11.phx.gbl
not sure if that will work with unicode data though.
--
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in
message:
> Do you know what order the source file is sorted in? If so, and if
the
> sort order column(s) are included then you may not need to know the
> line number since it is (theoretically anyway) possible to derive
that
> information from the other data.
Unfortunately the data file consists of simple lines of text with no
other way to extract potential column information before loading it
into the database.
> If not, then this article has a useful suggestion:
>
http://www.google.co.uk/groups?selm=uKOCiqtDEHA.1604%40TK2MSFTNGP11.phx.gbl
> not sure if that will work with unicode data though.
Good suggestion but it does fail with Unicode. Thanks anyway.
--== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==--
http://www.newsfeeds.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
--= East/West-Coast Server Farms - Total Privacy via Encryption =--|||"David Gugick" <davidg-nospam@.imceda.com> wrote:
> My understanding is that BCP feeds the rows in the order they appear
in
> a file. I can't imagine any reason it would or could do it
differently.
> In that case, you want to insert the data into a table that contains
an
> IDENTITY column. You can then use that key for your ORDER BY when
> processing the rows from whatever process does that.
In general BCP loads the data in the same order as the file but not
always. The ordering sometimes reverses for thousands of rows, or
skips certain rows, but you need to check it carefully to find the
misordering. You can create a table with an identity column and load
the data, but again if the rows are not loaded in the same sequence as
the file this won't matter. You will end up with an ordered table that
is unfortunately not in the same order as the original file.
To be honest I have tried all these suggestions in the past. My
typical solution would be to open the original file in Excel, add a
rownumber column and then save the resulting file as a Unicode file.
This works up until around a maximum of 63,000 rows. You can break a
file into 63,000 row subfiles, but this would be too tedious if you
have row counts approaching a million.
Thanks for the suggestions but I may have to learn some C#.
Unfortunately Visual Basic has problems with Unicode, as I suspect
also C++ and C have similar problems.
--== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==--
http://www.newsfeeds.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
--= East/West-Coast Server Farms - Total Privacy via Encryption =--|||What about using the CTS Import Wizard to import the data from the flat
file into a table with an identity.
Where is this data coming from? Is there any way to recreate it with a
counter column included in the output?
David Gugick
Imceda Software
www.imceda.com|||"David Gugick" <davidg-nospam@.imceda.com> wrote:
> What about using the CTS Import Wizard to import the data from the
flat
> file into a table with an identity.
It's the same problem. The table order generally follows the order in
the file but not always.
> Where is this data coming from? Is there any way to recreate it with
a
> counter column included in the output?
It's foreign language dictionary data that cannot be recreated. I
could manipulate the data on the file level but I am trying to avoid
potential problems with Unicode. Once the data gets into SQL Server I
don't have any problems, as long as the table order exactly matches
the file order.
--== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==--
http://www.newsfeeds.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
--= East/West-Coast Server Farms - Total Privacy via Encryption =--|||no-email wrote:
> It's foreign language dictionary data that cannot be recreated. I
> could manipulate the data on the file level but I am trying to avoid
> potential problems with Unicode. Once the data gets into SQL Server I
> don't have any problems, as long as the table order exactly matches
> the file order.
I'm not sure what problems you would have as long as the tool you are
using to edit the data is unicode aware. I use TextEdit for editing
(www.textpad.com) and it has simple replacement expressions.
Assuming you had each row of data on a single line, you could simply do
the following:
1- Add a leading CARRIAGE RETURN to the file
2- Open the Replace dialog
3- Check the Regular Expression option
4- Type "\n" - WITHOUT QUOTES in the Find What entry- means New Line
character
5- Type "\n\i\t" - WITHOUT QUOTES in the Replace With entry - means New
Line + Auto Number + TAB
6- Click Replace All
7 - Remove the leading carriage return in the file
8 - Click FILE SAVE AS and make sure the UNICODE option is selected
You can replace the TAB character with whatever your file requires or
add DOUBLE QUOTES around the Auto Number, etc.
You can download a free trial of TextPad on the web site.
David Gugick
Imceda Software
www.imceda.com|||"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:#ZNRubc$EHA.4004@.tk2msftngp13.phx.gbl...
> no-email wrote:
> > It's foreign language dictionary data that cannot be recreated. I
> > could manipulate the data on the file level but I am trying to
avoid
> > potential problems with Unicode. Once the data gets into SQL
Server I
> > don't have any problems, as long as the table order exactly
matches
> > the file order.
> I'm not sure what problems you would have as long as the tool you
are
> using to edit the data is unicode aware. I use TextEdit for editing
> (www.textpad.com) and it has simple replacement expressions.
Let me give it a try. With Word it just locks up after a few minutes
and dies. Notepad is way too slow on the replacements. I have 512 Meg
of memory but I still have problems. I'll try textpad.
Thanks for the suggestion.|||"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:#ZNRubc$EHA.4004@.tk2msftngp13.phx.gbl...
> no-email wrote:
> > It's foreign language dictionary data that cannot be recreated. I
> > could manipulate the data on the file level but I am trying to
avoid
> > potential problems with Unicode. Once the data gets into SQL
Server I
> > don't have any problems, as long as the table order exactly
matches
> > the file order.
> I'm not sure what problems you would have as long as the tool you
are
> using to edit the data is unicode aware. I use TextEdit for editing
> (www.textpad.com) and it has simple replacement expressions.
I tried it but it doesn't properly handle Unicode.
Thanks anyway.|||no-email wrote:
> I tried it but it doesn't properly handle Unicode.
> Thanks anyway.
Textpad does handle unicode. I use it with unicode data all the time.
What problems are you having with it? Just because it doesn't look right
in the editor doesn't mean it's not saving the file properly.
Could you elaborate on the issue you are seeing?
David Gugick
Imceda Software
www.imceda.com|||"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:eBFSCDe$EHA.1408@.TK2MSFTNGP10.phx.gbl...
> no-email wrote:
> > I tried it but it doesn't properly handle Unicode.
> >
> > Thanks anyway.
> Textpad does handle unicode. I use it with unicode data all the
time.
> What problems are you having with it? Just because it doesn't look
right
> in the editor doesn't mean it's not saving the file properly.
> Could you elaborate on the issue you are seeing?
I open the original Unicode file and save it using UTF-8 encoding.
I then open the file with Textpad using File->Open, with UTF-8
encoding, and I get the following error message:
"WARNING: "filename" contains characters that do not exist in code
page 1253 (ANSI-Greek). They will be converted to the system default
character, if you click OK."
All the Unicode characters are converted to question marks.
--== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==--
http://www.newsfeeds.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
--= East/West-Coast Server Farms - Total Privacy via Encryption =--|||David Gugick (davidg-nospam@.imceda.com) writes:
> Textpad does handle unicode. I use it with unicode data all the time.
> What problems are you having with it? Just because it doesn't look right
> in the editor doesn't mean it's not saving the file properly.
Are you using any version 5 beta?
Textpad 4.7 can read Unicode files, but if there actually is data outside
you ANSI code pages, that data will be mutilated. I have had problems
with as simple things as BKS files (control files for NT backup). If
I edit them with Textpad, NT backup does not like the file after I've
been to it.
See http://www.abaris.se/abaperls/doc/textpad.html for a couple of
links to similar tools. I have not evaulated them with regards to
Unicode, but I have a vague recollection that UltraEdit may cut it.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||> In general BCP loads the data in the same order as the file but not
> always. The ordering sometimes reverses for thousands of rows, or
> skips certain rows, but you need to check it carefully to find the
> misordering. You can create a table with an identity column and load
> the data, but again if the rows are not loaded in the same sequence as
> the file this won't matter. You will end up with an ordered table that
> is unfortunately not in the same order as the original file.
Have you tried loading to a table with an identity column with BCP with
batch size set to 1?
Craig