Hi all,
I have several columns which store currency values (typically up to 4
integer values, plus two decimal places)
Using Enterprise Manager I can set a column as decimal type, but it doesn't
allow me to specify precision) and any values show as the integer amount plu
s
.00 (ie 123.45 shows as 123.00). I converted these fields to money, but
several stored procedures showed a slow-down.
What is the most efficient datatype for storing very low precision real
numbers? What went wrong with my decimal datatype?
Many thanks in advance!Within EM look at the bottom half of the window. You will see a precision
and scale attribute there.
Keith Kratochvil
"GeorgeBR" <GeorgeBR@.discussions.microsoft.com> wrote in message
news:B8499051-76E7-4F2E-8650-971709333F6D@.microsoft.com...
> Hi all,
> I have several columns which store currency values (typically up to 4
> integer values, plus two decimal places)
> Using Enterprise Manager I can set a column as decimal type, but it
> doesn't
> allow me to specify precision) and any values show as the integer amount
> plus
> .00 (ie 123.45 shows as 123.00). I converted these fields to money, but
> several stored procedures showed a slow-down.
> What is the most efficient datatype for storing very low precision real
> numbers? What went wrong with my decimal datatype?
> Many thanks in advance!|||Also, don't use the money type. In addition to the performance issues you
are seeing, you will get rounding errors with money.
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:e$STqrafGHA.1208@.TK2MSFTNGP02.phx.gbl...
> Within EM look at the bottom half of the window. You will see a precision
> and scale attribute there.
>
> --
> Keith Kratochvil
>
> "GeorgeBR" <GeorgeBR@.discussions.microsoft.com> wrote in message
> news:B8499051-76E7-4F2E-8650-971709333F6D@.microsoft.com...
>
Showing posts with label store. Show all posts
Showing posts with label store. Show all posts
Sunday, March 25, 2012
Best query||design for table>subtable
Hi,
I've had few times the same problem and i'd like to go over it.
I'm facing this kind of data:
say that i need to store documents, all of them have a set of fields in common and all also have specific fields:
articles [ TITLE, AUTHOR, DATE, PIC, EXTRACT, KEYWORDS, ... ]
news [ TITLE, AUTHOR, DATE, URL, SOURCE, KEYWORDS, ... ]
...
my first solution was to store all of this data like this:
doc [ TITLE, AUTHOR, DATE, KEYWORDS, TABLENAME, TABLEID ]
doc_articles [ ID, PIC, EXTRACT ]
doc_news [ID, URL, SOURCE ]
It works fine BUT i need 2 queries for each doc to get its corresponding specific data.
Is there a way to dynamicaly link the corresponding fields leading to a result set with all table fields??
Is there another way to design tables to make it easier?
Thx in advance for reading,
++Check to see how your database engine supports the JOIN operation. I think it will do what you want.
-PatP|||Yes, i may not have been precise enough,
I actually JOIN when i know TABLENAME:
If i want news rows i do a
select * from doc,doc_news left join doc.TABLEID=doc_news.ID where doc.TABLENAME=doc_news
BUT i sometimes get a doc row without knowing if its a news or an article... i then need a 2nd query to get the specific table rows.
Would there be a query to select the doc row AND the specific table row with one query?
I'd say, Is there dynamic SQL possilities like
SELECT * from doc,doc.TABLENAME where doc.ID=X ??
(doesn't work "as is" in mysql)...
++|||use LEFT OUTER JOINs to join the doc table to both the news and the articles table
each PK in doc will have only one matching FK in either news or articles, so bob's your uncle -- still only one query!!
:)
but check your syntax carefully, because this isn't even close to being valid syntax --
select * from doc
,doc_news
left join doc.TABLEID=doc_news.ID
where doc.TABLENAME=doc_news|||if you must continue with your current design you could also consider aliasing
eg
select Article.docID as ArtDoc, Article.Author as ArtAuthor from blah
not nice but it may help
I've had few times the same problem and i'd like to go over it.
I'm facing this kind of data:
say that i need to store documents, all of them have a set of fields in common and all also have specific fields:
articles [ TITLE, AUTHOR, DATE, PIC, EXTRACT, KEYWORDS, ... ]
news [ TITLE, AUTHOR, DATE, URL, SOURCE, KEYWORDS, ... ]
...
my first solution was to store all of this data like this:
doc [ TITLE, AUTHOR, DATE, KEYWORDS, TABLENAME, TABLEID ]
doc_articles [ ID, PIC, EXTRACT ]
doc_news [ID, URL, SOURCE ]
It works fine BUT i need 2 queries for each doc to get its corresponding specific data.
Is there a way to dynamicaly link the corresponding fields leading to a result set with all table fields??
Is there another way to design tables to make it easier?
Thx in advance for reading,
++Check to see how your database engine supports the JOIN operation. I think it will do what you want.
-PatP|||Yes, i may not have been precise enough,
I actually JOIN when i know TABLENAME:
If i want news rows i do a
select * from doc,doc_news left join doc.TABLEID=doc_news.ID where doc.TABLENAME=doc_news
BUT i sometimes get a doc row without knowing if its a news or an article... i then need a 2nd query to get the specific table rows.
Would there be a query to select the doc row AND the specific table row with one query?
I'd say, Is there dynamic SQL possilities like
SELECT * from doc,doc.TABLENAME where doc.ID=X ??
(doesn't work "as is" in mysql)...
++|||use LEFT OUTER JOINs to join the doc table to both the news and the articles table
each PK in doc will have only one matching FK in either news or articles, so bob's your uncle -- still only one query!!
:)
but check your syntax carefully, because this isn't even close to being valid syntax --
select * from doc
,doc_news
left join doc.TABLEID=doc_news.ID
where doc.TABLENAME=doc_news|||if you must continue with your current design you could also consider aliasing
eg
select Article.docID as ArtDoc, Article.Author as ArtAuthor from blah
not nice but it may help
Tuesday, March 20, 2012
best practices for internationalization on sql
i'm working on a international project that we are working with about 70 different countries and locales on one database. we store our data in nchar, nvarchar, ntext type fields to overcome different language characters, but when we tried to sort data for example for danish sql server 2000 sorts it by binary sort so the result set is
a
?
b
c
d
......
but when we sort this data by
"select column1 from table where column2='true' order by column1 collate Danish_Norwegian_CS_AS "
the result set will be like
......
v
w
x
y
z
?
so we want the second one in other words the alphabethic sort order,
we can get the same result set by .net side by defining cultureinfo to danish and bind the data into a dataset and sort the data in a dataview. I want to learn that which way is best on sql side or on .net side.
If your answer will be sql side how can i recieve the client collate to my stored procedur.
i wrote a procedur like this
ALTER procedure dbo.dt_getproductcollation_test
@.collation nvarchar(50)
AS
DECLARE @.cmd nvarchar(3000)
set nocount on
SET @.cmd='select column1 from tcollate where [column2]=1 ORDER BY COLUMN1 COLLATE ' + @.collation
Execute(@.cmd)
but by this way i gues my procedure wont be compile because of execute statement, and also this will reduce my procedure performans.
is anybody has a best practises about this issue.
Thank You.
a
?
b
c
d
......
but when we sort this data by
"select column1 from table where column2='true' order by column1 collate Danish_Norwegian_CS_AS "
the result set will be like
......
v
w
x
y
z
?
so we want the second one in other words the alphabethic sort order,
we can get the same result set by .net side by defining cultureinfo to danish and bind the data into a dataset and sort the data in a dataview. I want to learn that which way is best on sql side or on .net side.
If your answer will be sql side how can i recieve the client collate to my stored procedur.
i wrote a procedur like this
ALTER procedure dbo.dt_getproductcollation_test
@.collation nvarchar(50)
AS
DECLARE @.cmd nvarchar(3000)
set nocount on
SET @.cmd='select column1 from tcollate where [column2]=1 ORDER BY COLUMN1 COLLATE ' + @.collation
Execute(@.cmd)
but by this way i gues my procedure wont be compile because of execute statement, and also this will reduce my procedure performans.
is anybody has a best practises about this issue.
Thank You.
If it is easy to implement and the result set is not too big, I think it would be better to sort it on middle tier/client, i.e., on .NET side. This will avoid the complicated logic to do dynamically COLLATE on SQL side, and save some server cycles.
As you said, using dynamic SQL in the stored procedure will cause recompile every time, which is certainly not good. You could build multiple version of the same proc to handle different collations, or build the SQL statements on the client, but neither is a good/convenicent option.
best practices for internationalization on sql
i'm working on a international project that we are working with about 70 different countries and locales on one database. we store our data in nchar, nvarchar, ntext type fields to overcome different language characters, but when we tried to sort data for example for danish sql server 2000 sorts it by binary sort so the result set is
a
?
b
c
d
......
but when we sort this data by
"select column1 from table where column2='true' order by column1 collate Danish_Norwegian_CS_AS "
the result set will be like
......
v
w
x
y
z
?
so we want the second one in other words the alphabethic sort order,
we can get the same result set by .net side by defining cultureinfo to danish and bind the data into a dataset and sort the data in a dataview. I want to learn that which way is best on sql side or on .net side.
If your answer will be sql side how can i recieve the client collate to my stored procedur.
i wrote a procedur like this
ALTER procedure dbo.dt_getproductcollation_test
@.collation nvarchar(50)
AS
DECLARE @.cmd nvarchar(3000)
set nocount on
SET @.cmd='select column1 from tcollate where [column2]=1 ORDER BY COLUMN1 COLLATE ' + @.collation
Execute(@.cmd)
but by this way i gues my procedure wont be compile because of execute statement, and also this will reduce my procedure performans.
is anybody has a best practises about this issue.
Thank You.
a
?
b
c
d
......
but when we sort this data by
"select column1 from table where column2='true' order by column1 collate Danish_Norwegian_CS_AS "
the result set will be like
......
v
w
x
y
z
?
so we want the second one in other words the alphabethic sort order,
we can get the same result set by .net side by defining cultureinfo to danish and bind the data into a dataset and sort the data in a dataview. I want to learn that which way is best on sql side or on .net side.
If your answer will be sql side how can i recieve the client collate to my stored procedur.
i wrote a procedur like this
ALTER procedure dbo.dt_getproductcollation_test
@.collation nvarchar(50)
AS
DECLARE @.cmd nvarchar(3000)
set nocount on
SET @.cmd='select column1 from tcollate where [column2]=1 ORDER BY COLUMN1 COLLATE ' + @.collation
Execute(@.cmd)
but by this way i gues my procedure wont be compile because of execute statement, and also this will reduce my procedure performans.
is anybody has a best practises about this issue.
Thank You.
If it is easy to implement and the result set is not too big, I think it would be better to sort it on middle tier/client, i.e., on .NET side. This will avoid the complicated logic to do dynamically COLLATE on SQL side, and save some server cycles.
As you said, using dynamic SQL in the stored procedure will cause recompile every time, which is certainly not good. You could build multiple version of the same proc to handle different collations, or build the SQL statements on the client, but neither is a good/convenicent option.
Saturday, February 25, 2012
Best datatype ?
One of my users wants to store 20,000 characters in a field. The worst part is he wants to be able to search on that field and he is expecting it to have atleast 10,000 records.
Please let me know what is the best thing to do in this case.
Thanks.I suspect your only choice is to use a text data type. To handle the searching I would setup full text search. Read up on these in Books Online and post back with questions.|||Thanks Paul.
Please let me know what is the best thing to do in this case.
Thanks.I suspect your only choice is to use a text data type. To handle the searching I would setup full text search. Read up on these in Books Online and post back with questions.|||Thanks Paul.
Best Data Type for...
What is the best data type to use to store numbers with limited decimal
places - ie. 1.25?
ThanksKeith,
Decimal or Numeric will do, eg numeric(10,2). They have 2 arguments -
precision and scale, with scale referring to the maximum number of decimal
places (2 in the example above). Have a look in BOL at "decimal data type,
overview".
HTH,
Paul Ibison|||Thank you
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OVihrFQLEHA.644@.tk2msftngp13.phx.gbl...
> Keith,
> Decimal or Numeric will do, eg numeric(10,2). They have 2 arguments -
> precision and scale, with scale referring to the maximum number of decimal
> places (2 in the example above). Have a look in BOL at "decimal data type,
> overview".
> HTH,
> Paul Ibison
>
places - ie. 1.25?
ThanksKeith,
Decimal or Numeric will do, eg numeric(10,2). They have 2 arguments -
precision and scale, with scale referring to the maximum number of decimal
places (2 in the example above). Have a look in BOL at "decimal data type,
overview".
HTH,
Paul Ibison|||Thank you
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OVihrFQLEHA.644@.tk2msftngp13.phx.gbl...
> Keith,
> Decimal or Numeric will do, eg numeric(10,2). They have 2 arguments -
> precision and scale, with scale referring to the maximum number of decimal
> places (2 in the example above). Have a look in BOL at "decimal data type,
> overview".
> HTH,
> Paul Ibison
>
Best Data Type for...
What is the best data type to use to store numbers with limited decimal
places - ie. 1.25?
ThanksKeith,
Decimal or Numeric will do, eg numeric(10,2). They have 2 arguments -
precision and scale, with scale referring to the maximum number of decimal
places (2 in the example above). Have a look in BOL at "decimal data type,
overview".
HTH,
Paul Ibison|||Thank you
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OVihrFQLEHA.644@.tk2msftngp13.phx.gbl...
> Keith,
> Decimal or Numeric will do, eg numeric(10,2). They have 2 arguments -
> precision and scale, with scale referring to the maximum number of decimal
> places (2 in the example above). Have a look in BOL at "decimal data type,
> overview".
> HTH,
> Paul Ibison
>
places - ie. 1.25?
ThanksKeith,
Decimal or Numeric will do, eg numeric(10,2). They have 2 arguments -
precision and scale, with scale referring to the maximum number of decimal
places (2 in the example above). Have a look in BOL at "decimal data type,
overview".
HTH,
Paul Ibison|||Thank you
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OVihrFQLEHA.644@.tk2msftngp13.phx.gbl...
> Keith,
> Decimal or Numeric will do, eg numeric(10,2). They have 2 arguments -
> precision and scale, with scale referring to the maximum number of decimal
> places (2 in the example above). Have a look in BOL at "decimal data type,
> overview".
> HTH,
> Paul Ibison
>
Best Data Type for...
What is the best data type to use to store numbers with limited decimal
places - ie. 1.25?
Thanks
Keith,
Decimal or Numeric will do, eg numeric(10,2). They have 2 arguments -
precision and scale, with scale referring to the maximum number of decimal
places (2 in the example above). Have a look in BOL at "decimal data type,
overview".
HTH,
Paul Ibison
|||Thank you
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OVihrFQLEHA.644@.tk2msftngp13.phx.gbl...
> Keith,
> Decimal or Numeric will do, eg numeric(10,2). They have 2 arguments -
> precision and scale, with scale referring to the maximum number of decimal
> places (2 in the example above). Have a look in BOL at "decimal data type,
> overview".
> HTH,
> Paul Ibison
>
places - ie. 1.25?
Thanks
Keith,
Decimal or Numeric will do, eg numeric(10,2). They have 2 arguments -
precision and scale, with scale referring to the maximum number of decimal
places (2 in the example above). Have a look in BOL at "decimal data type,
overview".
HTH,
Paul Ibison
|||Thank you
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OVihrFQLEHA.644@.tk2msftngp13.phx.gbl...
> Keith,
> Decimal or Numeric will do, eg numeric(10,2). They have 2 arguments -
> precision and scale, with scale referring to the maximum number of decimal
> places (2 in the example above). Have a look in BOL at "decimal data type,
> overview".
> HTH,
> Paul Ibison
>
Friday, February 24, 2012
Best and quickest approach to importing thousands of 2 meg XML files into XMl column?
Hi all,
I intend to use SQL Server 2005 (which I haven't used before), to store hundreds of thousands of XML files this autumn and I'm trying to figure out which approach is the best to do this. These files are very complex (multiple nested elements) and use multiple namespaces (imports).The files will be on the same server as the Database. Below is a list of some ideas I have.
I intend to use SQL Server 2005 (which I haven't used before), to store hundreds of thousands of XML files this autumn and I'm trying to figure out which approach is the best to do this. These files are very complex (multiple nested elements) and use multiple namespaces (imports).The files will be on the same server as the Database. Below is a list of some ideas I have.
1) Use Some DTS process to import the XML files if possible in SQL Server 2005?
2) Create a stored procedure that interates through a local directory, opens each XML file and inserts it's content into the database column using the bulkload method (any examples would be appreciated). Does this require the use of some scripting code such as VB script?
3) Run a server-side script such as PHP that loops through a local directory and stores the content of the XML file into a variable which is passed to a Stored procedure which stores the variable in to the database column? I have tried this and it seems very unstable and slow, roughly 2 minutes per file (the application server and database servers are on different boxes). I'm worried when I need to loop through thousands of files it will crash the servers!
Any suggestions would be appreciated
Muhi
Muhi,
SQL Server provides a command line tool called 'BCP' which is one of the good ways to bulk load XML data into an XML column. For instance you can store your XML instances in a file with a delimiter inbetween each instance (default delimiter is ,) and you can use the following command to bulk load your data:
bcp YourDB..YourTable in "XMLData.csv" -T -b 300 -N -h "TABLOCK"
This will insert the instances from 'XMLData.csv' into 'YourTable' in database 'YourDB'. BOL has a lot more information about the command line options in BCP. There are also more hints you can provide to BCP to improve performance. For instance if you have a clustered index on the table and if you know that your input data in the file is ordered on the index column (say id) then you can provide another hint to BCP -h "ORDER(id)" to avoid some additional sorts.
Thanks
Babu
|||Hi Muhi,
try SQL Server 2005 Books Online
Examples of Bulk Importing and Exporting XML Documents
http://msdn2.microsoft.com/en-us/library/ms191184.aspx
Best regards
Jiri
Sunday, February 12, 2012
Beginner- Need Help With SQL Server 2005 Express
I am an absolute beginner with SQL Server, and I have now developed an app that needs to store, read, and search for saved data. Basically, my program will have to search through the databse to find a particular URL match to a string I supply, and then retrieve the Username and Password data that is associated with that URL, storing those to strings.
My problem is, I've learned how to create a database and tables in the IDE, but all of the examples on the web that I've found only talk about using SQL Server with Visual Basic 2005 in uses such as databinding to controls and displaying the database in datagrids and such on the form. I need help with how to interact with the database in code, seeing as the user of the program will likely never actually see the database contents- the data will be for the program's use only.
So, I was wondering if anyone could provide/guide me to some helpful information on interacting with SQL databases through code in VB Express 2005.
Thanks for any help.
In your VB code, define variables, and then instead of assigning data to control.text, assign data to the variables.
|||OK, but then how do I search through all of the fields in 1 column, and then if I find the right one, extract the adjacent columns for that field and save them as strings?
beginner in SQL Server 2000
Hi,
i'm a beginner in sql server 2000 and i have a question.
i want to store word, excel, pdf etc documents in order to search them
for specific content. i don't want to get as result lines of the
documents but the names of them.What data type i must use? can i search
and for Metadata information and how? i will really appreciate any
answer.
Thanks in advance.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
use the image datatype.
Have a look at
http://www.indexserverfaq.com/blobs.htm for more information on how to do
this.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"bill stam" <balisx@.in.gr> wrote in message
news:OWRhJIeGFHA.2156@.TK2MSFTNGP09.phx.gbl...
> Hi,
> i'm a beginner in sql server 2000 and i have a question.
> i want to store word, excel, pdf etc documents in order to search them
> for specific content. i don't want to get as result lines of the
> documents but the names of them.What data type i must use? can i search
> and for Metadata information and how? i will really appreciate any
> answer.
> Thanks in advance.
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||Bill,
Yes, you can store word, excel, pdf etc documents in order to search them
for specific content (as in Full-text) the contents of the MS Word files
stored in an SQL Table's FT-enable IMAGE column. As you're are using SQL
Sever 2000, you will need to add a "file extension" column to your table and
populate it with the file extension value, for example "doc" or ".doc" for
MS Word documents. Note, this "file extension" column must be defined as
sysname, char(3) or varchar(4) in order for the MSSearch service to
correctly identify the file type to be indexed.
Additionally, below is a SQL script example of using TextCopy.exe (ships
with SQL Server 2000) to import MS Word documents into SQL Sever table and
IMAGE column and then using SQLFTS CONTAINS or FREETEXT to search the
contents of that MS word document:
use pubs
go
if exists (select * from sysobjects where id = object_id('FTSTable'))
drop table FTSTable
go
CREATE TABLE FTSTable (
KeyCol int IDENTITY (1,1) NOT NULL
CONSTRAINT FTSTable_IDX PRIMARY KEY CLUSTERED,
TextCol text NULL,
ImageCol image NULL,
ExtCol char(3) NULL, -- can be either sysname or char(3)
TimeStampCol timestamp NULL
) ON [PRIMARY]
go
-- Insert data... (Note: Initalizing IMAGE column with 0xFFFFFFFF for use
with TextCopy.exe)
INSERT FTSTable values('Test TEXT Data for row 1', 0xFFFFFFFF, 'doc', NULL)
go
-- Select data
SELECT * from FTSTable
go
declare @.query varchar(200)
-- Insert MS_Word_document.doc into Row 5 !!
-- NOTE: Ensure the correct path for textcopy.exe!!
set @.query = 'D:\MSSQL80\MSSQL$SQL80\Binn\textcopy /s '+@.@.servername+' /u sa
/p<password> /d pubs /t FTSTable /c ImageCol /f
D:\SQLFiles\Shiloh\<MS_Word>.doc /i /k 5000 /w "where KeyCol=1"'
print @.query
exec master..xp_cmdshell @.query
go
-- Select data
SELECT * from FTSTable
go
-- FTI
use pubs
go
exec sp_fulltext_database 'enable' -- only do this once!
go
-- Drop FTI, if necessary...
-- exec sp_fulltext_table 'FTSTable','drop'
-- exec sp_fulltext_Catalog 'FTSCatalog','drop'
exec sp_fulltext_catalog 'FTSCatalog','create'
exec sp_fulltext_table 'FTSTable','create','FTSCatalog','FTSTable_IDX'
exec sp_fulltext_column 'FTSTable','ImageCol','add', 0x0409, 'ExtCol'
exec sp_fulltext_column 'FTSTable','TextCol','add'
exec sp_fulltext_table 'FTSTable', 'activate'
go
-- Start FT Indexing...
exec sp_fulltext_catalog 'FTSCatalog','start_full'
go
-- Wait for FT Indexing to complete and check NT/Win2K Application log for
success/errors..
-- Search for search_word_here in MS_Word
select KeyCol, ImageCol from FTSTable where
contains(*,'<search_word_in_MS_Word_here>') order by KeyCol
go
-- Search for search_word_here in MS_Word file...
select KeyCol, ImageCol from FTSTable where
freetext(*,'<search_word_in_MS_Word_here>') order by KeyCol
go
-- Remove FT Indexes & Catalog & table..
exec sp_fulltext_table 'FTSTable','drop'
exec sp_fulltext_Catalog 'FTSCatalog','drop'
drop table FTSTable
Note, that to FT Search the Metadata info contained in the documents, such
as author, title, you wll need to programmaticlly extract this information
and store it in a textual column and then FT Index these columns in addition
to the document column.
Hope that helps!
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"bill stam" <balisx@.in.gr> wrote in message
news:OWRhJIeGFHA.2156@.TK2MSFTNGP09.phx.gbl...
> Hi,
> i'm a beginner in sql server 2000 and i have a question.
> i want to store word, excel, pdf etc documents in order to search them
> for specific content. i don't want to get as result lines of the
> documents but the names of them.What data type i must use? can i search
> and for Metadata information and how? i will really appreciate any
> answer.
> Thanks in advance.
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
i'm a beginner in sql server 2000 and i have a question.
i want to store word, excel, pdf etc documents in order to search them
for specific content. i don't want to get as result lines of the
documents but the names of them.What data type i must use? can i search
and for Metadata information and how? i will really appreciate any
answer.
Thanks in advance.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
use the image datatype.
Have a look at
http://www.indexserverfaq.com/blobs.htm for more information on how to do
this.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"bill stam" <balisx@.in.gr> wrote in message
news:OWRhJIeGFHA.2156@.TK2MSFTNGP09.phx.gbl...
> Hi,
> i'm a beginner in sql server 2000 and i have a question.
> i want to store word, excel, pdf etc documents in order to search them
> for specific content. i don't want to get as result lines of the
> documents but the names of them.What data type i must use? can i search
> and for Metadata information and how? i will really appreciate any
> answer.
> Thanks in advance.
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||Bill,
Yes, you can store word, excel, pdf etc documents in order to search them
for specific content (as in Full-text) the contents of the MS Word files
stored in an SQL Table's FT-enable IMAGE column. As you're are using SQL
Sever 2000, you will need to add a "file extension" column to your table and
populate it with the file extension value, for example "doc" or ".doc" for
MS Word documents. Note, this "file extension" column must be defined as
sysname, char(3) or varchar(4) in order for the MSSearch service to
correctly identify the file type to be indexed.
Additionally, below is a SQL script example of using TextCopy.exe (ships
with SQL Server 2000) to import MS Word documents into SQL Sever table and
IMAGE column and then using SQLFTS CONTAINS or FREETEXT to search the
contents of that MS word document:
use pubs
go
if exists (select * from sysobjects where id = object_id('FTSTable'))
drop table FTSTable
go
CREATE TABLE FTSTable (
KeyCol int IDENTITY (1,1) NOT NULL
CONSTRAINT FTSTable_IDX PRIMARY KEY CLUSTERED,
TextCol text NULL,
ImageCol image NULL,
ExtCol char(3) NULL, -- can be either sysname or char(3)
TimeStampCol timestamp NULL
) ON [PRIMARY]
go
-- Insert data... (Note: Initalizing IMAGE column with 0xFFFFFFFF for use
with TextCopy.exe)
INSERT FTSTable values('Test TEXT Data for row 1', 0xFFFFFFFF, 'doc', NULL)
go
-- Select data
SELECT * from FTSTable
go
declare @.query varchar(200)
-- Insert MS_Word_document.doc into Row 5 !!
-- NOTE: Ensure the correct path for textcopy.exe!!
set @.query = 'D:\MSSQL80\MSSQL$SQL80\Binn\textcopy /s '+@.@.servername+' /u sa
/p<password> /d pubs /t FTSTable /c ImageCol /f
D:\SQLFiles\Shiloh\<MS_Word>.doc /i /k 5000 /w "where KeyCol=1"'
print @.query
exec master..xp_cmdshell @.query
go
-- Select data
SELECT * from FTSTable
go
-- FTI
use pubs
go
exec sp_fulltext_database 'enable' -- only do this once!
go
-- Drop FTI, if necessary...
-- exec sp_fulltext_table 'FTSTable','drop'
-- exec sp_fulltext_Catalog 'FTSCatalog','drop'
exec sp_fulltext_catalog 'FTSCatalog','create'
exec sp_fulltext_table 'FTSTable','create','FTSCatalog','FTSTable_IDX'
exec sp_fulltext_column 'FTSTable','ImageCol','add', 0x0409, 'ExtCol'
exec sp_fulltext_column 'FTSTable','TextCol','add'
exec sp_fulltext_table 'FTSTable', 'activate'
go
-- Start FT Indexing...
exec sp_fulltext_catalog 'FTSCatalog','start_full'
go
-- Wait for FT Indexing to complete and check NT/Win2K Application log for
success/errors..
-- Search for search_word_here in MS_Word
select KeyCol, ImageCol from FTSTable where
contains(*,'<search_word_in_MS_Word_here>') order by KeyCol
go
-- Search for search_word_here in MS_Word file...
select KeyCol, ImageCol from FTSTable where
freetext(*,'<search_word_in_MS_Word_here>') order by KeyCol
go
-- Remove FT Indexes & Catalog & table..
exec sp_fulltext_table 'FTSTable','drop'
exec sp_fulltext_Catalog 'FTSCatalog','drop'
drop table FTSTable
Note, that to FT Search the Metadata info contained in the documents, such
as author, title, you wll need to programmaticlly extract this information
and store it in a textual column and then FT Index these columns in addition
to the document column.
Hope that helps!
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"bill stam" <balisx@.in.gr> wrote in message
news:OWRhJIeGFHA.2156@.TK2MSFTNGP09.phx.gbl...
> Hi,
> i'm a beginner in sql server 2000 and i have a question.
> i want to store word, excel, pdf etc documents in order to search them
> for specific content. i don't want to get as result lines of the
> documents but the names of them.What data type i must use? can i search
> and for Metadata information and how? i will really appreciate any
> answer.
> Thanks in advance.
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
Beginner Help Wanted With PL/SQL
Hi I was wondering if anybody could help me with this problem:
Step 1: Create a table to store text strings entered by a database user. When a text string is entered to the database, various information about the entry should be recorded including:
A unique identifier for the entry that will be the primary key. You must set the primary key using a constraint
The actual text string entered by the user
The name of the database user that entered the text string
The date that the string was entered
Step 2: Create a sequence that starts off with an initial value of 100 and increments by 10
Step 3: Declare a PL/SQL block that:
Declares four appropriately named variables using anchored datatypes that match each column in the table defined in Step 1
Prompts the user to enter a text string
Assign the value entered by the user to the variable defined to store the string entered
Assign the date, user and next value in the sequence to the other variable defined previously. Note that you should use the SELECT INTO FROM DUAL method of assignment
Within the PL/SQL Block, insert the information assigned to the variables into the table defined in Step 1Which specific part are you having trouble with? Clearly you are not asking us to do all your homework for you!|||Homework? Yeah hold on I have to go ask my mammy if I can use the computer!|||Originally posted by iknownothing
Homework? Yeah hold on I have to go ask my mammy if I can use the computer!
Don't forget to say "please"!|||Jaysus you're hilarious!!|||Originally posted by iknownothing
Jaysus you're hilarious!!
You are too kind. But seriously, is there any specific help you want with your "homework" ("class assignment", call it what you will), or did you just want someone to do it all for you? You will find that people round here are very helpful if you are prepared to put in some of the effort yourself. On the other hand, people are less inclined to help when it appears that someone just wants to pass off someone else's effort as their own.
So: what have you come up with so far, and where are you stuck?|||Well, you are already using a cursor-based record! :-
student_val c_student%ROWTYPE;
i.e. the record type is defined in terms of the cursor c_student.
The table-based cursor would be student%ROWTYPE.
If you use a cursor FOR loop you can get rid of the declaration altogether, along with a lot of other code:
SET SERVEROUTPUT ON;
DECLARE
CURSOR c_student IS
SELECT * FROM student;
begin
open c_student;
for student_val in c_student loop
DBMS_OUTPUT.PUT_LINE('Student Details: ' || student_val.salutation || student_val.first_name
|| student_val.last_name || student_val.phone || student_val.Registration_date );
end loop;
end;|||Yeah I figured it out later and deleted the post coz it was pointless but what I need to know now is how to change it from a cursor to a table based! Im in the process of trying but am getting nowhere!!
Step 1: Create a table to store text strings entered by a database user. When a text string is entered to the database, various information about the entry should be recorded including:
A unique identifier for the entry that will be the primary key. You must set the primary key using a constraint
The actual text string entered by the user
The name of the database user that entered the text string
The date that the string was entered
Step 2: Create a sequence that starts off with an initial value of 100 and increments by 10
Step 3: Declare a PL/SQL block that:
Declares four appropriately named variables using anchored datatypes that match each column in the table defined in Step 1
Prompts the user to enter a text string
Assign the value entered by the user to the variable defined to store the string entered
Assign the date, user and next value in the sequence to the other variable defined previously. Note that you should use the SELECT INTO FROM DUAL method of assignment
Within the PL/SQL Block, insert the information assigned to the variables into the table defined in Step 1Which specific part are you having trouble with? Clearly you are not asking us to do all your homework for you!|||Homework? Yeah hold on I have to go ask my mammy if I can use the computer!|||Originally posted by iknownothing
Homework? Yeah hold on I have to go ask my mammy if I can use the computer!
Don't forget to say "please"!|||Jaysus you're hilarious!!|||Originally posted by iknownothing
Jaysus you're hilarious!!
You are too kind. But seriously, is there any specific help you want with your "homework" ("class assignment", call it what you will), or did you just want someone to do it all for you? You will find that people round here are very helpful if you are prepared to put in some of the effort yourself. On the other hand, people are less inclined to help when it appears that someone just wants to pass off someone else's effort as their own.
So: what have you come up with so far, and where are you stuck?|||Well, you are already using a cursor-based record! :-
student_val c_student%ROWTYPE;
i.e. the record type is defined in terms of the cursor c_student.
The table-based cursor would be student%ROWTYPE.
If you use a cursor FOR loop you can get rid of the declaration altogether, along with a lot of other code:
SET SERVEROUTPUT ON;
DECLARE
CURSOR c_student IS
SELECT * FROM student;
begin
open c_student;
for student_val in c_student loop
DBMS_OUTPUT.PUT_LINE('Student Details: ' || student_val.salutation || student_val.first_name
|| student_val.last_name || student_val.phone || student_val.Registration_date );
end loop;
end;|||Yeah I figured it out later and deleted the post coz it was pointless but what I need to know now is how to change it from a cursor to a table based! Im in the process of trying but am getting nowhere!!
Subscribe to:
Posts (Atom)