a text file data source

I would know how to create a report which has as data source a text file

someone can help me?

Hi Maaloul-

To retrieve data directly from a text file, you would need to implement your own custom data extension. A sample data extension can be found here:

http://www.gotdotnet.com/Community/UserSamples/Details.aspx?SampleGuid=B8468707-56EF-4864-AC51-D83FC3273FE5

-Jon

|||Another option is to create an ODBC connection to the text file. This has been written up several times.

a text file data source

I would know how to create a report which has as data source a text file

someone can help me?

Hi Maaloul-

To retrieve data directly from a text file, you would need to implement your own custom data extension. A sample data extension can be found here:

http://www.gotdotnet.com/Community/UserSamples/Details.aspx?SampleGuid=B8468707-56EF-4864-AC51-D83FC3273FE5

-Jon

|||Another option is to create an ODBC connection to the text file. This has been written up several times.

A text file data source

Hi all,
I have to write a report that uses a .txt file as a data-source and I
am not sure how to do that.
Anyone know where I can find out how to set this up? ODBC Connection?
The .txt file format is like this...(top 5 rows)
20040881 2004/12/22 01:30:12 NONIMPACT
20040883 2004/12/22 15:02:12 NONIMPACT
20040884 2004/12/22 17:52:12 NONIMPACT
20040885 2004/12/23 11:20:12 NONIMPACT
20040891 2004/12/23 15:10:12 NONIMPACT
and so one....
Any help would be great!!!!!!!!!!!!
Thanks, KerrieI've never tried to reach a flat text file, but I do not think that SRS
supports connecting directly to a flat file...(correct me if I am
wrong).
You may have to write your own data processing extension that can
connect to the files you want and extract the data therein. Hope this
helps!
Lance M.
Kerrie wrote:
> Hi all,
> I have to write a report that uses a .txt file as a data-source and I
> am not sure how to do that.
> Anyone know where I can find out how to set this up? ODBC Connection?
> The .txt file format is like this...(top 5 rows)
> 20040881 2004/12/22 01:30:12 NONIMPACT
> 20040883 2004/12/22 15:02:12 NONIMPACT
> 20040884 2004/12/22 17:52:12 NONIMPACT
> 20040885 2004/12/23 11:20:12 NONIMPACT
> 20040891 2004/12/23 15:10:12 NONIMPACT
> and so one....
> Any help would be great!!!!!!!!!!!!
> Thanks, Kerrie|||Create a linked data source of type CSV to the flat file and use it in
your report against the text file.
Lance M wrote:
> I've never tried to reach a flat text file, but I do not think that SRS
> supports connecting directly to a flat file...(correct me if I am
> wrong).
> You may have to write your own data processing extension that can
> connect to the files you want and extract the data therein. Hope this
> helps!
> Lance M.
> Kerrie wrote:
> > Hi all,
> > I have to write a report that uses a .txt file as a data-source and I
> > am not sure how to do that.
> >
> > Anyone know where I can find out how to set this up? ODBC Connection?
> >
> > The .txt file format is like this...(top 5 rows)
> > 20040881 2004/12/22 01:30:12 NONIMPACT
> > 20040883 2004/12/22 15:02:12 NONIMPACT
> > 20040884 2004/12/22 17:52:12 NONIMPACT
> > 20040885 2004/12/23 11:20:12 NONIMPACT
> > 20040891 2004/12/23 15:10:12 NONIMPACT
> >
> > and so one....
> >
> > Any help would be great!!!!!!!!!!!!
> >
> > Thanks, Kerrie

A substr()-like functin for IMAGE data...

Friends, I'd like to examine bytes 40 through 47 of a very large IMAGE field
in records in one table. I'd like to handle the bytes as text if possible.
A simple litle utility is needed by our customer, so I had hoped to avoid
doing the utility in C++, and thought maybe perhaps I could hanlde this all
as a SQL script. (I looked around, and it doesn't appear SQL really lets
you work with binary.)
Any ideas will be greatly appreciated. (Otherwise, I'll just code up a
little C++/ODBC app for them.)
Thanks in advance,
JamesDid you try SUBSTRING?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"James Hunter Ross" <james.ross@.oneilsoft.com> wrote in message
news:upYfBsLKFHA.1280@.TK2MSFTNGP09.phx.gbl...
> Friends, I'd like to examine bytes 40 through 47 of a very large IMAGE
field
> in records in one table. I'd like to handle the bytes as text if
possible.
> A simple litle utility is needed by our customer, so I had hoped to avoid
> doing the utility in C++, and thought maybe perhaps I could hanlde this
all
> as a SQL script. (I looked around, and it doesn't appear SQL really lets
> you work with binary.)
> Any ideas will be greatly appreciated. (Otherwise, I'll just code up a
> little C++/ODBC app for them.)
> Thanks in advance,
> James
>|||I "read", I did not "try", and it appeared that it would only work with
character fields. I feel stupid. Thanks, I tried it, that works.
My next task is to write the entire IMAGE field to a disk file so that it
may be opened and examined by an external program.
James|||See READTEXT and TEXTPTR commands in Books Online.
<bol>
READTEXT
Reads text, ntext, or image values from a text, ntext, or image column,
starting from a specified offset and reading the specified number of bytes.
Syntax
READTEXT { table.column text_ptr offset size } [ HOLDLOCK ]
...
Examples
This example reads the second through twenty-sixth characters of the pr_info
column in the pub_info table.
USE pubs
GO
DECLARE @.ptrval varbinary(16)
SELECT @.ptrval = TEXTPTR(pr_info)
FROM pub_info pr INNER JOIN publishers p
ON pr.pub_id = p.pub_id
AND p.pub_name = 'New Moon Books'
READTEXT pub_info.pr_info @.ptrval 1 25
GO
</bol>
"James Hunter Ross" <james.ross@.oneilsoft.com> wrote in message
news:upYfBsLKFHA.1280@.TK2MSFTNGP09.phx.gbl...
> Friends, I'd like to examine bytes 40 through 47 of a very large IMAGE
> field in records in one table. I'd like to handle the bytes as text if
> possible. A simple litle utility is needed by our customer, so I had hoped
> to avoid doing the utility in C++, and thought maybe perhaps I could
> hanlde this all as a SQL script. (I looked around, and it doesn't appear
> SQL really lets you work with binary.)
> Any ideas will be greatly appreciated. (Otherwise, I'll just code up a
> little C++/ODBC app for them.)
> Thanks in advance,
> James
>

A substr()-like functin for IMAGE data...

Friends, I'd like to examine bytes 40 through 47 of a very large IMAGE field
in records in one table. I'd like to handle the bytes as text if possible.
A simple litle utility is needed by our customer, so I had hoped to avoid
doing the utility in C++, and thought maybe perhaps I could hanlde this all
as a SQL script. (I looked around, and it doesn't appear SQL really lets
you work with binary.)
Any ideas will be greatly appreciated. (Otherwise, I'll just code up a
little C++/ODBC app for them.)
Thanks in advance,
James
Did you try SUBSTRING?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"James Hunter Ross" <james.ross@.oneilsoft.com> wrote in message
news:upYfBsLKFHA.1280@.TK2MSFTNGP09.phx.gbl...
> Friends, I'd like to examine bytes 40 through 47 of a very large IMAGE
field
> in records in one table. I'd like to handle the bytes as text if
possible.
> A simple litle utility is needed by our customer, so I had hoped to avoid
> doing the utility in C++, and thought maybe perhaps I could hanlde this
all
> as a SQL script. (I looked around, and it doesn't appear SQL really lets
> you work with binary.)
> Any ideas will be greatly appreciated. (Otherwise, I'll just code up a
> little C++/ODBC app for them.)
> Thanks in advance,
> James
>
|||See READTEXT and TEXTPTR commands in Books Online.
<bol>
READTEXT
Reads text, ntext, or image values from a text, ntext, or image column,
starting from a specified offset and reading the specified number of bytes.
Syntax
READTEXT { table.column text_ptr offset size } [ HOLDLOCK ]
...
Examples
This example reads the second through twenty-sixth characters of the pr_info
column in the pub_info table.
USE pubs
GO
DECLARE @.ptrval varbinary(16)
SELECT @.ptrval = TEXTPTR(pr_info)
FROM pub_info pr INNER JOIN publishers p
ON pr.pub_id = p.pub_id
AND p.pub_name = 'New Moon Books'
READTEXT pub_info.pr_info @.ptrval 1 25
GO
</bol>
"James Hunter Ross" <james.ross@.oneilsoft.com> wrote in message
news:upYfBsLKFHA.1280@.TK2MSFTNGP09.phx.gbl...
> Friends, I'd like to examine bytes 40 through 47 of a very large IMAGE
> field in records in one table. I'd like to handle the bytes as text if
> possible. A simple litle utility is needed by our customer, so I had hoped
> to avoid doing the utility in C++, and thought maybe perhaps I could
> hanlde this all as a SQL script. (I looked around, and it doesn't appear
> SQL really lets you work with binary.)
> Any ideas will be greatly appreciated. (Otherwise, I'll just code up a
> little C++/ODBC app for them.)
> Thanks in advance,
> James
>

A substr()-like functin for IMAGE data...

Friends, I'd like to examine bytes 40 through 47 of a very large IMAGE field
in records in one table. I'd like to handle the bytes as text if possible.
A simple litle utility is needed by our customer, so I had hoped to avoid
doing the utility in C++, and thought maybe perhaps I could hanlde this all
as a SQL script. (I looked around, and it doesn't appear SQL really lets
you work with binary.)
Any ideas will be greatly appreciated. (Otherwise, I'll just code up a
little C++/ODBC app for them.)
Thanks in advance,
JamesDid you try SUBSTRING?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"James Hunter Ross" <james.ross@.oneilsoft.com> wrote in message
news:upYfBsLKFHA.1280@.TK2MSFTNGP09.phx.gbl...
> Friends, I'd like to examine bytes 40 through 47 of a very large IMAGE
field
> in records in one table. I'd like to handle the bytes as text if
possible.
> A simple litle utility is needed by our customer, so I had hoped to avoid
> doing the utility in C++, and thought maybe perhaps I could hanlde this
all
> as a SQL script. (I looked around, and it doesn't appear SQL really lets
> you work with binary.)
> Any ideas will be greatly appreciated. (Otherwise, I'll just code up a
> little C++/ODBC app for them.)
> Thanks in advance,
> James
>|||See READTEXT and TEXTPTR commands in Books Online.
<bol>
READTEXT
Reads text, ntext, or image values from a text, ntext, or image column,
starting from a specified offset and reading the specified number of bytes.
Syntax
READTEXT { table.column text_ptr offset size } [ HOLDLOCK ]
...
Examples
This example reads the second through twenty-sixth characters of the pr_info
column in the pub_info table.
USE pubs
GO
DECLARE @.ptrval varbinary(16)
SELECT @.ptrval = TEXTPTR(pr_info)
FROM pub_info pr INNER JOIN publishers p
ON pr.pub_id = p.pub_id
AND p.pub_name = 'New Moon Books'
READTEXT pub_info.pr_info @.ptrval 1 25
GO
</bol>
"James Hunter Ross" <james.ross@.oneilsoft.com> wrote in message
news:upYfBsLKFHA.1280@.TK2MSFTNGP09.phx.gbl...
> Friends, I'd like to examine bytes 40 through 47 of a very large IMAGE
> field in records in one table. I'd like to handle the bytes as text if
> possible. A simple litle utility is needed by our customer, so I had hoped
> to avoid doing the utility in C++, and thought maybe perhaps I could
> hanlde this all as a SQL script. (I looked around, and it doesn't appear
> SQL really lets you work with binary.)
> Any ideas will be greatly appreciated. (Otherwise, I'll just code up a
> little C++/ODBC app for them.)
> Thanks in advance,
> James
>

A Severe error occured ...

Hi,

i am using the HTML Textbox from activeup.com. I get a strange error when i put to much text inside the HTML textbox and insert it into my SQL Server with an SP. Myquestion is: is there a limit on a field in SQL of the type Text or what could it else be (error in HTML Textbox?)can you post some code.

A script to get properties of full text catalog?

Hey, all--

My organization is having a problem with MS-SQL's sporadically stopping full-text catalog population on one of our databases. New content is added to that database constantly, so we have an incremental population schedule set up to update the index once an hour. This works fine for a while and then stops for no apparent reason; we don't notice it having stopped until someone comes along to tell us they can't find something.

We've been trying different things to fix it, but in the meanwhile, is there a way to expose the properties (particularly the Last Population Date) of a full-text catalog to an outside script, so that we could incorporate a check of the catalog status into our daily server health scripts? Or, alternatively, a way to have the database server send out an email when the last incremental update is more than an hour old? It's no good to have our users being the ones to tell us when the indexing has failed.

Thanks much.Hello,

Try

use DatabaseName
select FulltextCatalogProperty(N'CatalogName', N'PopulateCompletionAge')

Its in seconds since 12:00:00 A.M., January 1, 1990.

I'll get you more info after i walk downstairs and get some starbucks :)|||Oh, that's perfect. Thanks so much.

For the reference of future readers of this topic, that first param is actually the catalog name, not the database name. Full Microsoft documentation is here: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_fa-fz_7x15.asp . It's amazing what you can find when you know what you're looking for... thanks again!|||Select dateadd(s,FulltextCatalogProperty(N'CatalogName', N'PopulateCompletionAge'),'1/1/1990')

Alot of times, I'll do a incremental right after the insert. That will only be helpful in certain situations.

Thanks for the note on the Catalog name. It looked fishy but I didnt look into it. I guess my catalog name is the same as my database name ;)

A question about Deadlock.

Hi All,
I read a book which has the following text:
"DEADLOCK - Although locking solves one problem, it introduces
another. Consider what might happen when two users
want to order two items from inventory. Suppose User A
wants to order some paper, and if she can get the paper,
she wants to order some pencils. Then suppose User B
wants to order some pencils, and if he can get the pencils,
he wants to order some paper.
1. Lock paper for user A.
2. Lock pencils for user B.
3. Process A's requests; write paper record.
4. Process B's requests; write pencil record.
5. Put A in wait state for pencils.
6. Put B in wait state for paper.
** Locked ** "
My question is: Could A releases the lock on paper after he is done with
step 3 and
B releases the lock on pencil after he is done with step 4? In this way, A
and B will not have a deadlock.
It seems that the locks have to be in place during the entire transaction
and causing the deadlock.
Thank you a lot.
ChrisDeadlocks occur when two transactions are trying to access a resource the
other has locked. They don't happen all the time, it depends on the design
of the queries and the program. What happens a lot to is actually blocks,
where one transaction blocks other ones because it is using a resource.
One thing for example that controls this is the size of the transaction.
The transaction holds a lock until it is completed and then committed. The
longer the transaction runs, the higher the likelihood a blocking or
deadlock situation will occur. To prevent issues like this, query design,
index design and table design all come into play. Denormalizing a table so
it contains data about multiple items (paper and pencils) to cut down on
joins helps.
When a deadlock occurs, SQL will choose a loser transaction and disconnect
it.
--
*************************************
Andy S.
andy_mcdba@.yahoo.com
*************************************
"yma" <yma@.pacbell.net> wrote in message
news:e0wTDWjeDHA.1836@.TK2MSFTNGP09.phx.gbl...
> Hi All,
> I read a book which has the following text:
> "DEADLOCK - Although locking solves one problem, it introduces
> another. Consider what might happen when two users
> want to order two items from inventory. Suppose User A
> wants to order some paper, and if she can get the paper,
> she wants to order some pencils. Then suppose User B
> wants to order some pencils, and if he can get the pencils,
> he wants to order some paper.
> 1. Lock paper for user A.
> 2. Lock pencils for user B.
> 3. Process A's requests; write paper record.
> 4. Process B's requests; write pencil record.
> 5. Put A in wait state for pencils.
> 6. Put B in wait state for paper.
> ** Locked ** "
> My question is: Could A releases the lock on paper after he is done with
> step 3 and
> B releases the lock on pencil after he is done with step 4? In this way,
A
> and B will not have a deadlock.
> It seems that the locks have to be in place during the entire transaction
> and causing the deadlock.
> Thank you a lot.
> Chris
>|||> It seems that the locks have to be in place during the entire
transaction
> and causing the deadlock.
This is true. If locks were released before the transaction was
committed, it would not be possible to rollback the transaction and data
integrity would be lost.
Note that SQL Server will choose a deadlock victim in the scenario you
describe so one of the transactions will continue; the deadlock will not
be indefinite.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"yma" <yma@.pacbell.net> wrote in message
news:e0wTDWjeDHA.1836@.TK2MSFTNGP09.phx.gbl...
> Hi All,
> I read a book which has the following text:
> "DEADLOCK - Although locking solves one problem, it introduces
> another. Consider what might happen when two users
> want to order two items from inventory. Suppose User A
> wants to order some paper, and if she can get the paper,
> she wants to order some pencils. Then suppose User B
> wants to order some pencils, and if he can get the pencils,
> he wants to order some paper.
> 1. Lock paper for user A.
> 2. Lock pencils for user B.
> 3. Process A's requests; write paper record.
> 4. Process B's requests; write pencil record.
> 5. Put A in wait state for pencils.
> 6. Put B in wait state for paper.
> ** Locked ** "
> My question is: Could A releases the lock on paper after he is done
with
> step 3 and
> B releases the lock on pencil after he is done with step 4? In this
way, A
> and B will not have a deadlock.
> It seems that the locks have to be in place during the entire
transaction
> and causing the deadlock.
> Thank you a lot.
> Chris
>|||Transactions are all about achieving correct results, and correct results
depend on the needs of your business logic. The example given below is a
simplified one for demonstration purposes and it is thus easy to find a
workaround such as splitting it into two transactions. However, that split
may not represent the actual business requirement. Enhance the description
just slightly and you'll see this. Say that the users only want to place
the order if they can get all of the items. Now it is clear that you can't
split the transaction into two because doing so might result in a user
getting paper but no pencils, or vice versa.
--
Hal Berenson, SQL Server MVP
True Mountain Group LLC
"yma" <yma@.pacbell.net> wrote in message
news:e0wTDWjeDHA.1836@.TK2MSFTNGP09.phx.gbl...
> Hi All,
> I read a book which has the following text:
> "DEADLOCK - Although locking solves one problem, it introduces
> another. Consider what might happen when two users
> want to order two items from inventory. Suppose User A
> wants to order some paper, and if she can get the paper,
> she wants to order some pencils. Then suppose User B
> wants to order some pencils, and if he can get the pencils,
> he wants to order some paper.
> 1. Lock paper for user A.
> 2. Lock pencils for user B.
> 3. Process A's requests; write paper record.
> 4. Process B's requests; write pencil record.
> 5. Put A in wait state for pencils.
> 6. Put B in wait state for paper.
> ** Locked ** "
> My question is: Could A releases the lock on paper after he is done with
> step 3 and
> B releases the lock on pencil after he is done with step 4? In this way,
A
> and B will not have a deadlock.
> It seems that the locks have to be in place during the entire transaction
> and causing the deadlock.
> Thank you a lot.
> Chris
>

A long query problem

Hi all,
I currently build a text processing database for my application.
The database is to store all the plain text files submitted by customers. The plain text files will be processed before it store in the sql server. Thus I have 3 major tables Document, Term and TermDocument.
I encounter a problem when I did a query. For example when I performed the following query

SELECT T.TermName, S.* FROM Weight AS S INNER JOIN Term AS T ON S.TermID = T.TermID WHERE T.TermName IN ('electronic','commerce','consists', ....)

The system told me that "No enough storage is available to complete the operation". The terms in "IN(....)" could have up 3k entries. I know this is huge, but I have no choice but to retrieve all of them in a single query.

Is there anyway to solve the problem or perform similar operation in alternative way.

Maybe it would be easier to use reverse logic?

If possible you could reverse the IN to a NOT IN any maybe the list is shorter then (assumption).

You could also insert the 3000 terms in a temporary table and join with that table instead of using the IN clause.

Anyway, I'm not too fond of queries with 3000 entries in the WHERE clause to be honest.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

I agree that its best to go down the table route, either with a temp table or perhaps a table valued UDF.

There are some good examples of the split function referenced in this thread which may suit your needs as it will enable you to convert a string of terms in to a table.

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

Hope that makes sense.