Showing posts with label column. Show all posts

Monday, March 26, 2012

FT Index on column with multiple language doesn't work - any ideas

Hi all,
I have a table on SQL Server 2005 with a column (collation
SQL_Latin1_General_CP1_CI_AS) that can contain multiple languages. Some of
the possible languages include English, German, French, Kazakh, Arabic,
Chinese (Simplified and Traditional), Japanese, Korean, Thai, etc.
I tried querying for documents that contains 台灣 (meaning "Taiwan" in
Simplified Chinese), but it is not working as I expected.
I ran the following query:
select * from tblDocuments
where CONTAINS(DocumentTitle, '台灣', LANGUAGE 'Traditional Chinese')
With the English language breaker, I get 0 results.
With the Traditional Chinese language breaker, I get 1 result and it is the
document I was looking for.
I would've expected the LANGUAGE keyword to ignore the default language
breaker and use whatever is specified, but apparently I'm missing
something... maybe table setup or whatnot.
Any ideas would be greatly appreaciated.
Cheers,
Andry
This is correct behavior. When the document is indexed and you are
specifying the English word breaker it interprets the contents of the row to
be English words, unless you are using the xml language tags and specify
that the XML doc is in Chinese and that you are using the XML data type, or
you are using Word and marked that passage containing the character as
Chinese, or the language settings for your Word document are Chinese, or you
are using html and have set the ms.locale to Chinese and set the correct
code page. With Word Docs and HTML docs you must save them in the image or
varbinary data type columns and use a document type column with the
extension the document would have if it was stored in the file system.
If you don't follow the above the Chinese character will be interpreted as a
Unicode sequence which will not be interpreted correctly in your queries.
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
"Andry" <Andry@.discussions.microsoft.com> wrote in message
news:569E9782-47E3-4657-AC68-7787214D3D97@.microsoft.com...
> Hi all,
> I have a table on SQL Server 2005 with a column (collation
> SQL_Latin1_General_CP1_CI_AS) that can contain multiple languages. Some of
> the possible languages include English, German, French, Kazakh, Arabic,
> Chinese (Simplified and Traditional), Japanese, Korean, Thai, etc.
> I tried querying for documents that contains ? (meaning "Taiwan" in
> Simplified Chinese), but it is not working as I expected.
> I ran the following query:
> select * from tblDocuments
> where CONTAINS(DocumentTitle, '?', LANGUAGE 'Traditional Chinese')
> With the English language breaker, I get 0 results.
> With the Traditional Chinese language breaker, I get 1 result and it is
> the
> document I was looking for.
> I would've expected the LANGUAGE keyword to ignore the default language
> breaker and use whatever is specified, but apparently I'm missing
> something... maybe table setup or whatnot.
> Any ideas would be greatly appreaciated.
> Cheers,
> Andry

Wednesday, March 21, 2012

front end options for entering data into an oracle database

Hi,

I need to set up a generic table in Oracle that allows users to enter
data that can later be retrieved in reports.

I was thinking of a 3 column table in this structure:

DATE,ITEM,VALUE

figuring that you can store anything in this way because ITEM can be
anything and you can have multiple instances of a particular value by
having a new date.

Does that seem reasonable?

In any case, what are my user front end options? I was thinking a
Microsoft Access database but its not ideal because not everyone here
has Access?

Can you do it with Excel? Or what other options are there as a simple
user front end to enter the data?

Thanks for any advice.

Kimanasttin@.excite.com wrote:

> Hi,
> I need to set up a generic table in Oracle that allows users to enter
> data that can later be retrieved in reports.
> I was thinking of a 3 column table in this structure:
> DATE,ITEM,VALUE
> figuring that you can store anything in this way because ITEM can be
> anything and you can have multiple instances of a particular value by
> having a new date.
> Does that seem reasonable?

No. It's a classic design error that defeats most of the reasons why we
use a DBMS at all.

http://tonyandrews.blogspot.com/200...n-mistakes.html

Also, this is a Microsoft SQL Server group. You'll most probably get
more help in an Oracle-specific group.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--

Fromatting excel column

Hi,

I have an excel export with numeric values. When the SSIS package writes into the excel it treats all data types as strings and attaches an apostrophe in the beginning. I tried formatting the excel sheet with the number data type and saving it . It doesnt work.

Other strange thing is that when I go into the advanced editor for the excel destination and look into the properties of the external columns all of them have the data type as Unicode String[DT_WSTR] irrespective of what the data type is from the input.The UI allows me to change it to numeric for numeric columns without any error but the value is not saved at all which is very frustrating. I also tried changing it in the xml file of the package, it some how seems to overwrite it after validation. It would be really nice if it threw an error saying that it cannot be changed.

Anyways there seems to be no way of changing the destination data type if it s an existing file and if I create a new excel sheet there is no way of formatting data. Is there any way out?

Thanks

Interesting report on the behavior your seeing. My experience was quite the opposite when working with an Excel import package.

I found the Excel connector to be VERY tightly bound to the defined data types within the actual Excel file. I also found that it seemed to determine the type by the top few rows and not necessarily by the whole data set.|||chandrala's issues are with an Excel destination, not a source though.|||Ya my issue is with destination if I create a new excel file or if I already have an excel file it doesnt matter. It is kind of dumb to expect that the file already has to have some data before the export to determine the type. Most exports are into an unpopulated file. Please let me know how to tackle this.|||I'm having the same issue. Anyone come up with a resolution?|||On the destination connection, I had first row has column names. Set this to no, and everything worked.|||

Grant Mackay wrote:
On the destination connection, I had first row has column names. Set this to no, and everything worked.

This make sense as SSIS try to guess the data type based on the first values of the column. If you have the column names in you first row; then SSIS will treat it as String; which be correct. May that be your problem?

I also found few trheads taking about data conversition when excel is the destination:

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

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

Fromatting excel column

Hi,

Anyways there seems to be no way of changing the destination data type if it s an existing file and if I create a new excel sheet there is no way of formatting data. Is there any way out?

Thanks

Interesting report on the behavior your seeing. My experience was quite the opposite when working with an Excel import package.

Grant Mackay wrote:
On the destination connection, I had first row has column names. Set this to no, and everything worked.

I also found few trheads taking about data conversition when excel is the destination:

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

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

sql

From varchar(max) to xml

I have a table with 2 columns. Column a(varchar(max)) and column b(xml).
Column a contains the following data:
Col1;Col2
New York;USA
Rio;Brasil
Tokio;Japan
The first line contains the column header, the following the data.
The data should be transferred to column b with the following xml-structure:
<Col1>New York</Col1><Col2>USA</Col2>
<Col1>Rio</Col1><Col2>Brasil</Col2>
<Col1>Tokio</Col1><Col2>Japan</Col2>
The number of columns and the column names are various.
Any ideas?
Thanks psychodad71
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...er-xml/200606/1It looks like this would require a lot of string manipulation. Although I
believe it could be done with T-SQL, the string functions are a little limit
ed.
I'd suggest you use the CLR.
I'll give it a shot myself when I get some time and I'll post an update.
Denis Ruckebusch
http://blogs.msdn.com/denisruc
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"psychodad71 via webservertalk.com" <u2248@.uwe> wrote in message
news:6232e7ee08949@.uwe...
>I have a table with 2 columns. Column a(varchar(max)) and column b(xml).
> Column a contains the following data:
> Col1;Col2
> New York;USA
> Rio;Brasil
> Tokio;Japan
> The first line contains the column header, the following the data.
> The data should be transferred to column b with the following xml-structur
e:
> <Col1>New York</Col1><Col2>USA</Col2>
> <Col1>Rio</Col1><Col2>Brasil</Col2>
> <Col1>Tokio</Col1><Col2>Japan</Col2>
> The number of columns and the column names are various.
> Any ideas?
> Thanks psychodad71
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...er-xml/200606/1|||I can't think of a nice set-based method of doing this, but you could use
procedural code to do it. I agree with Denis, this should probably be done
in the front end. But that said, here's a little procedural sample. Note
that I don't have SQL 2005 on the computer I'm at right now so I had to put
this thing together on SQL 2000. It should run properly on SQL 2005 as
well. It's *extremely* procedural and assumes that the TestInput table has
a numeric id for each row, row 0 being the column names and all other rows
containing data. The procedural nature of this type of code makes me think
you'd be a lot better off doing it on the front end though:
-- Create a "Numbers" table and an inline UDF that uses it to parse your
-- comma-delimited string. Run this section one time.
SELECT TOP 10000 number = IDENTITY(INT, 1, 1)
INTO Numbers
FROM syscomments a1
CROSS JOIN syscomments a2
-- Add Primary Key to Numbers table
ALTER TABLE Numbers
ALTER COLUMN Number INT NOT NULL
ALTER TABLE Numbers
ADD CONSTRAINT PK_Numbers PRIMARY KEY (Number)
-- Create inline UDF
GO
CREATE FUNCTION dbo.ParseDelimitedList (@.list AS NVARCHAR(4000))
RETURNS TABLE
AS
RETURN (
SELECT Number, LTRIM(RTRIM(CASE Number
WHEN 1 THEN SUBSTRING(@.list, 1,
CASE WHEN CHARINDEX(';', @.list, Number + 1) > 0 THEN
CHARINDEX(';', @.list, Number + 1) - 1
ELSE LEN(@.list) - CHARINDEX(';', @.list, Number + 1)
END)
ELSE SUBSTRING(@.list, Number + 1,
CASE WHEN CHARINDEX(';', @.list, Number + 1) > 0 THEN
CHARINDEX(';', @.list, Number + 1) - Number - 1
ELSE LEN(@.list)
END)
END)) AS Value
FROM Numbers
WHERE (SUBSTRING(@.list, Number, 1) = ';' OR Number = 1)
)
GO
-- End of the Numbers table/UDF initialization.
CREATE TABLE TestInput([id] INT PRIMARY KEY,
a VARCHAR(8000),
b VARCHAR(8000))
INSERT INTO TestInput([id], a)
SELECT 0, 'Col1;Col2'
UNION SELECT 1, 'New York;USA'
UNION SELECT 2, 'Rio;Brasil'
UNION SELECT 3, 'Tokio;Japan'
DECLARE @.sql VARCHAR(8000)
DECLARE @.temp_str VARCHAR(8000)
DECLARE @.cols TABLE ([id_num] INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
[col_name] VARCHAR(8000))
SELECT @.temp_str = a
FROM TestInput
WHERE [id] = 0
INSERT INTO @.cols([col_name])
SELECT Value
FROM dbo.ParseDelimitedList(@.temp_str)
ORDER BY [Number]
DECLARE @.col_count INT
SELECT @.col_count = MAX([id_num])
FROM @.cols
DECLARE @.vals TABLE ([id_num] INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
[value] VARCHAR(8000))
DECLARE @.id INT
SELECT @.id = 1
DECLARE @.i INT
WHILE @.id <= (SELECT MAX([id]) FROM TestInput)
BEGIN
SELECT @.temp_str = a
FROM TestInput
WHERE [id] = @.id
IF NOT(@.temp_str IS NULL)
BEGIN
INSERT INTO @.vals([value])
SELECT [Value]
FROM dbo.ParseDelimitedList(@.temp_str)
ORDER BY [Number]
SELECT @.temp_str = ''
SELECT @.i = 1
WHILE @.i <= @.col_count
BEGIN
SELECT @.temp_str = @.temp_str + '<' +
(
SELECT [col_name]
FROM @.cols
WHERE [id_num] = @.i
) + '>'
SELECT @.temp_str = @.temp_str +
(
SELECT COALESCE([value], '')
FROM @.vals
WHERE [id_num] = @.i + (@.id - 1) * @.col_count
)
SELECT @.temp_str = @.temp_str + '</' +
(
SELECT [col_name]
FROM @.cols
WHERE [id_num] = @.i
) + '>'
SELECT @.i = @.i + 1
END
UPDATE TestInput
SET b = @.temp_str
WHERE [id] = @.id
END
SELECT @.id = @.id + 1
END
SELECT *
FROM @.vals
SELECT *
FROM TestInput
"psychodad71 via webservertalk.com" <u2248@.uwe> wrote in message
news:6232e7ee08949@.uwe...
>I have a table with 2 columns. Column a(varchar(max)) and column b(xml).
> Column a contains the following data:
> Col1;Col2
> New York;USA
> Rio;Brasil
> Tokio;Japan
> The first line contains the column header, the following the data.
> The data should be transferred to column b with the following
> xml-structure:
> <Col1>New York</Col1><Col2>USA</Col2>
> <Col1>Rio</Col1><Col2>Brasil</Col2>
> <Col1>Tokio</Col1><Col2>Japan</Col2>
> The number of columns and the column names are various.
> Any ideas?
> Thanks psychodad71
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...er-xml/200606/1|||Ooops, the "syscomments" references will need to be changed for SQL 2005 to
"sys.comments".
"Mike C#" <xyz@.xyz.com> wrote in message
news:ORxRYYwlGHA.2056@.TK2MSFTNGP03.phx.gbl...
>I can't think of a nice set-based method of doing this, but you could use
>procedural code to do it. I agree with Denis, this should probably be done
>in the front end. But that said, here's a little procedural sample. Note
>that I don't have SQL 2005 on the computer I'm at right now so I had to put
>this thing together on SQL 2000. It should run properly on SQL 2005 as
>well. It's *extremely* procedural and assumes that the TestInput table has
>a numeric id for each row, row 0 being the column names and all other rows
>containing data. The procedural nature of this type of code makes me think
>you'd be a lot better off doing it on the front end though:
> -- Create a "Numbers" table and an inline UDF that uses it to parse your
> -- comma-delimited string. Run this section one time.
> SELECT TOP 10000 number = IDENTITY(INT, 1, 1)
> INTO Numbers
> FROM syscomments a1
> CROSS JOIN syscomments a2
> -- Add Primary Key to Numbers table
> ALTER TABLE Numbers
> ALTER COLUMN Number INT NOT NULL
> ALTER TABLE Numbers
> ADD CONSTRAINT PK_Numbers PRIMARY KEY (Number)
> -- Create inline UDF
> GO
> CREATE FUNCTION dbo.ParseDelimitedList (@.list AS NVARCHAR(4000))
> RETURNS TABLE
> AS
> RETURN (
> SELECT Number, LTRIM(RTRIM(CASE Number
> WHEN 1 THEN SUBSTRING(@.list, 1,
> CASE WHEN CHARINDEX(';', @.list, Number + 1) > 0 THEN
> CHARINDEX(';', @.list, Number + 1) - 1
> ELSE LEN(@.list) - CHARINDEX(';', @.list, Number + 1)
> END)
> ELSE SUBSTRING(@.list, Number + 1,
> CASE WHEN CHARINDEX(';', @.list, Number + 1) > 0 THEN
> CHARINDEX(';', @.list, Number + 1) - Number - 1
> ELSE LEN(@.list)
> END)
> END)) AS Value
> FROM Numbers
> WHERE (SUBSTRING(@.list, Number, 1) = ';' OR Number = 1)
> )
> GO
> -- End of the Numbers table/UDF initialization.
> CREATE TABLE TestInput([id] INT PRIMARY KEY,
> a VARCHAR(8000),
> b VARCHAR(8000))
> INSERT INTO TestInput([id], a)
> SELECT 0, 'Col1;Col2'
> UNION SELECT 1, 'New York;USA'
> UNION SELECT 2, 'Rio;Brasil'
> UNION SELECT 3, 'Tokio;Japan'
> DECLARE @.sql VARCHAR(8000)
> DECLARE @.temp_str VARCHAR(8000)
> DECLARE @.cols TABLE ([id_num] INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
> [col_name] VARCHAR(8000))
> SELECT @.temp_str = a
> FROM TestInput
> WHERE [id] = 0
> INSERT INTO @.cols([col_name])
> SELECT Value
> FROM dbo.ParseDelimitedList(@.temp_str)
> ORDER BY [Number]
> DECLARE @.col_count INT
> SELECT @.col_count = MAX([id_num])
> FROM @.cols
> DECLARE @.vals TABLE ([id_num] INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
> [value] VARCHAR(8000))
> DECLARE @.id INT
> SELECT @.id = 1
> DECLARE @.i INT
> WHILE @.id <= (SELECT MAX([id]) FROM TestInput)
> BEGIN
> SELECT @.temp_str = a
> FROM TestInput
> WHERE [id] = @.id
> IF NOT(@.temp_str IS NULL)
> BEGIN
> INSERT INTO @.vals([value])
> SELECT [Value]
> FROM dbo.ParseDelimitedList(@.temp_str)
> ORDER BY [Number]
> SELECT @.temp_str = ''
> SELECT @.i = 1
> WHILE @.i <= @.col_count
> BEGIN
> SELECT @.temp_str = @.temp_str + '<' +
> (
> SELECT [col_name]
> FROM @.cols
> WHERE [id_num] = @.i
> ) + '>'
> SELECT @.temp_str = @.temp_str +
> (
> SELECT COALESCE([value], '')
> FROM @.vals
> WHERE [id_num] = @.i + (@.id - 1) * @.col_count
> )
> SELECT @.temp_str = @.temp_str + '</' +
> (
> SELECT [col_name]
> FROM @.cols
> WHERE [id_num] = @.i
> ) + '>'
> SELECT @.i = @.i + 1
> END
> UPDATE TestInput
> SET b = @.temp_str
> WHERE [id] = @.id
> END
> SELECT @.id = @.id + 1
> END
> SELECT *
> FROM @.vals
> SELECT *
> FROM TestInput
>
>
> "psychodad71 via webservertalk.com" <u2248@.uwe> wrote in message
> news:6232e7ee08949@.uwe...
>|||psychodad71 via webservertalk.com wrote:
> I have a table with 2 columns. Column a(varchar(max)) and column b(xml).
> Column a contains the following data:
> Col1;Col2
> New York;USA
> Rio;Brasil
> Tokio;Japan
> The first line contains the column header, the following the data.
> The data should be transferred to column b with the following xml-structur
e:
> <Col1>New York</Col1><Col2>USA</Col2>
> <Col1>Rio</Col1><Col2>Brasil</Col2>
> <Col1>Tokio</Col1><Col2>Japan</Col2>
> The number of columns and the column names are various.
> Any ideas?
If your database supports access to external scripting languages, dump
column a out and pass it through the following filter
awk -F\; 'BEGIN {ORS=""}
{if(NR==1)n=split($0,gi);else{for(i=1;i<=NF;++i)print "<" gi[i] ">" $i
"</" gi[i] ">";print "\n"}}'
and read the result into column b. The GNU awk processor for Windows can
be downloaded from http://gnuwin32.sourceforge.net/packages/gawk.htm
///Peter|||Your table will need something to identity the first row, so I added an iden
tity
col. The first row inserted is assumed to be a column header.
CREATE TABLE #t1
(
id int identity primary key,
city VARCHAR(50),
xdata xml DEFAULT ''
)
INSERT INTO #t1 (city) values ('Col1;Col2;Col3;Col4')
INSERT INTO #t1 (city) values ('New York;Boston;Chicago;USA')
INSERT INTO #t1 (city) values ('Rio;Bla;Sao Paulo;Brasil')
INSERT INTO #t1 (city) values ('Tokio;Nagasaki;ABCD;Japan')
INSERT INTO #t1 (city) values ('Tokio;Nagasaki;Japan')
INSERT INTO #t1 (city) values ('Tokio;Nagasaki;ABCD;EF;Japan')
The city column has a compound value in it. You can use recursion to "unflat
ten"
this into a table with one row per city. Once you have done that you can
compose that table into xml, using recursion again.
-- start by making CTE of elementNames
WITH elementNames
AS
(
SELECT TOP(1) id, 1 as colNum, LEFT(city+';', CHARINDEX(';', city+';')-1)
as colName, RIGHT(city+';', LEN(city+';')-CHARINDEX(';', city+';')) as remai
n
from #t1
ORDER BY id
UNION ALL
SELECT t.id, en.colNum + 1 as colNum, LEFT(en.remain, CHARINDEX(';', en.rema
in)-1)
as single, RIGHT(en.remain, LEN(en.remain)-CHARINDEX(';', en.remain)) as
remain from elementNames en
JOIN #t1 AS t ON t.id = en.id
WHERE LEN(remain)>0
),
-- now recurse to "unflatten" the composite value in the city column
pos
AS
(
-- find the first city
SELECT id, 1 as colNum, LEFT(city+';', CHARINDEX(';', city+';')-1) as single
,
RIGHT(city+';', LEN(city+';')-CHARINDEX(';', city+';')) as remain from #t1
WHERE id not in (select id from elementNames)
UNION ALL
-- find the rest
SELECT id, colNum + 1 as colNum,
LEFT(remain, CHARINDEX(';', remain)-1) as single, RIGHT(remain, LEN(remain)-
CHARINDEX(';',
remain))
as remain from pos
where LEN(remain) > 0
and id not in (select id from elementNames)
),
-- now compose xml of of the expanded table
compose
as
(
SELECT p.id, p.colNum, CAST('<' + e.colName + '>' + p.single + '</' + e.colN
ame
+ '>'
as VARCHAR(MAX)) as xdata from pos AS p
JOIN elementNames AS e ON p.colNum = e.colNum
where p.colNum = 1
UNION ALL
SELECT p.id, p.colNum, CAST(c.xdata + '<' + e.colName + '>' + p.single +
'</' + e.colName + '>'
as VARCHAR(MAX)) as xdata from pos AS p
JOIN elementNames AS e ON p.colNum = e.colNum
JOIN compose AS c on p.colNum = c.colNum+1 and p.id = c.id
)
-- use composed xml to update the original table
UPDATE #t1 set xdata = (SELECT xdata from compose where #t1.id = compose.id
AND compose.colNum = (SELECT MAX(colNum) from compose as c WHERE c.id = #t1.
id)
)
Then to test the results:
SELECT * FROM #t1
1 Col1;Col2;Col3;Col4 NULL
2 New York;Boston;Chicago;USA <Col1>New
York</Col1><Col2>Boston</Col2><Col3>Chicago</Col3><Col4>USA</Col4>
3 Rio;Bla;Sao Paulo;Brasil <Col1>Rio</Co
l1><Col2>Bla</Col2><Col3>Sao
Paulo</Col3><Col4>Brasil</Col4>
4 Tokio;Nagasaki;ABCD;Japan <Col1>Tokio</
Col1><Col2>Nagasaki</Col2><Col3>ABCD</Col3><Col4>Japan</Col4>
5 Tokio;Nagasaki;Japan <Col1>Tokio</
Col1><Col2>Nagasaki</Col2><Col3>Japan</Col3>
6 Tokio;Nagasaki;ABCD;EF;Japan <Col1>Tokio</
Col1><Col2>Nagasaki</Col2><Col3>ABCD</Col3><Col4>EF</Col4>
Note that this works as even when the number of column headings do not match
the number of cities, though the results might not be what you want.
Dan

> I have a table with 2 columns. Column a(varchar(max)) and column
> b(xml). Column a contains the following data:
> Col1;Col2
> New York;USA
> Rio;Brasil
> Tokio;Japan
> The first line contains the column header, the following the data.
> The data should be transferred to column b with the following
> xml-structure:
> <Col1>New York</Col1><Col2>USA</Col2>
> <Col1>Rio</Col1><Col2>Brasil</Col2>
> <Col1>Tokio</Col1><Col2>Japan</Col2>
> The number of columns and the column names are various.
> Any ideas?
> Thanks psychodad71
>|||For some reason I see that some of the xml like stuff I have shown in this q
uery seems to be lost once it is posted. I have attached a text file version
of it.
Dan|||Alternatively, using a numbers table
as in http://www.aspfaq.com/show.asp?id=2516
you can do this
;
with Headers(id,rn,ColName)
as(
select id,
rank() over(order by Number),
ltrim(substring(city,
Number,
charindex(';',
city + ';',
Number) - Number))
from #t1
inner join Numbers on Number between 1 and len(city) + 1
and substring(';' + city, Number, 1) = ';'
where id=1),
Cities(id,rn,City)
as(
select id,
rank() over(partition by id order by Number),
ltrim(substring(city,
Number,
charindex(';',
city + ';',
Number) - Number))
from #t1
inner join Numbers on Number between 1 and len(city) + 1
and substring(';' + city, Number, 1) = ';'
where id>1)
update #t1
set xdata=(select cast('<'+h.ColName+'>'+c.City+'</'+h.ColName+'>' as
xml)
from Headers h
inner join Cities c on c.rn=h.rn
where c.id=#t1.id
for xml path(''))
select * from #t1
Regards
Mark|||Slight correction.
where c.id=#t1.id
for xml path(''))
should be
where c.id=#t1.id
order by c.rn
for xml path(''))

From Sql Column to file, OPENROWSET?

Hi There

I am using OPENROWSET to import a file from disk to a varbinary(max) column in sql server.

However as far as i can see OPENROWSET is only to import into Sql Server. However i need to reverse this as well, by that i mean export a varbinary(max) column data to a file on disk. How can i do this ? (The file is a compress text file).

How does this work for file types, i mean you can import any file as binary but when you export it back to disk will the file typr still work, will a .xls or a.exe imprted and exported like this still function 100%.

Thanx?

OPENROWSET

Includes all connection information necessary to access remote data from an OLE DB data source. This method is an alternative to accessing tables in a linked server and is a one-time, ad hoc method of connecting and accessing remote data using OLE DB. The OPENROWSET function can be referenced in the FROM clause of a query as though it is a table name. The OPENROWSET function can also be referenced as the target table of an INSERT, UPDATE, or DELETE statement, subject to the capabilities of the OLE DB provider. Although the query may return multiple result sets, OPENROWSET returns only the first one.

you can use it with insert or update.

if you are handling blobs however like images, pdfs,docs etc. i recommend streaming it out to a file using ado or ado.net

here are some usefull links:

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q258038
http://support.microsoft.com/kb/308042/EN-US/

|||

Hi joeydj

I would really like to stay away from .net, my last option would be to write a clr stored proc to output the file from the database, are you 100% this cannot be dine in TSQL? What about TEXTCOPY? This seems to only work for Text and image data. However somethig like this is what i am looking for.

Also it is not documented in BOL obviously this makes me nervous as it may be depricated in future?

Thanx

|||

I stand corrected seems textcopy was only for Sql Server 6.5 and 7.

It just seems od to me that Sql Server would provide a feature to get data from disk into sql server but not vica versa?

|||

You could use DTS (in SQL2000) or SSIS (in sql2005) to import/export the data.

|||

Thanx for the feedback

Let me elaborate.

This is at a distributed site running SQL Express and only Sql express(No ssis avalable). There is a column in a table that is in a varbinary(max) format, this column was populated from a file using OPENROWSET at a central location and sent to to site via service broker, now i need to write this file back at the remote site from the varbinary(max) column?

Thanx

|||

As far as I know (and I might be wrong on this) the openrowset allows importation from file in order for us to have bulk inserts - it was not created as a primary mechanism to communicate with the file system.

In your situation I would go with CLR . SQL Server 2005 ships with a sample of a CLR SP that shows how to do it (check on <<your sql server installation directory>>\90\Samples\Engine\Programmability\CLR\HandlingLOBUsingCLR) so you wouldn't have to code much...

|||

Thanx Miguel

Yes i am sure OPENROWSET was not made for that , just thought there was a function or something i did not know about.

Thanx i will check out the sample, just out of interest if everyone has reccomended CLR then i am guessing there is no way to do this in SS2000?

Thanx

|||In SQL2000 I would recommend DTS's.

Friday, March 9, 2012

'freezing' table heading

i am pretty new to ssrs and i am wondering if it is possible to
"freeze" the column headings of a table.
are there any good links i can visit that would help me with this / any
suggestions?
Thanks soooooo much!!
rebekahIf you mean having them always visible while scrolling, there is a property
on the heading row that allows this. I believe it's only available for the
reporting heading, not for group headings though. Right-click on the table
and click properties. Look for "header should remain visible while
scrolling".
-T
<roadie.girl@.gmail.com> wrote in message
news:1147786512.245883.255890@.y43g2000cwc.googlegroups.com...
>i am pretty new to ssrs and i am wondering if it is possible to
> "freeze" the column headings of a table.
> are there any good links i can visit that would help me with this / any
> suggestions?
> Thanks soooooo much!!
> rebekah
>

freezing column and header in report

I have report with large records and many columns

I would like to freez header and specific column like excel

I tried to freez header it self it's worked

I tried to freez a column it self it's worked

when I tried to freez both in same report it does not work..

any help?

This should work. I assume that you are useing the Matrix type. Are there mutiple groups (rows)?
Are you specifying the

Freezed column headers in RS Webreports

Hi,
a lot of my users were asking me to have the possibilty of a freeze
pane (similar to excel) in the RS web reports because often the reports
are to long to see the column haeders. Especially when you have
drilldowns it would help.
Has somebody tried to implement such feature by DLL to RS 2000. Is this
feature planned for RS 2005 '
thanks
BBIf I remember correctly, that might be in SQL 2005
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"BB_Reporting" <Bruce.Baessler@.web.de> wrote in message
news:1123855073.170843.218720@.o13g2000cwo.googlegroups.com...
> Hi,
>
> a lot of my users were asking me to have the possibilty of a freeze
> pane (similar to excel) in the RS web reports because often the reports
> are to long to see the column haeders. Especially when you have
> drilldowns it would help.
>
> Has somebody tried to implement such feature by DLL to RS 2000. Is this
> feature planned for RS 2005 '
>
> thanks
>
> BB
>|||I am sorry for not answering your question directly... I also have not seen
any 3rd party prods which currently do that in sql 2000 either
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"BB_Reporting" <Bruce.Baessler@.web.de> wrote in message
news:1123855073.170843.218720@.o13g2000cwo.googlegroups.com...
> Hi,
>
> a lot of my users were asking me to have the possibilty of a freeze
> pane (similar to excel) in the RS web reports because often the reports
> are to long to see the column haeders. Especially when you have
> drilldowns it would help.
>
> Has somebody tried to implement such feature by DLL to RS 2000. Is this
> feature planned for RS 2005 '
>
> thanks
>
> BB
>|||This is available in RS 2005.
I don't know of anyone who has implemented this for RS 2000 - but in that
case, you would need to implement a full custom rendering extension yourself
for RS 2000 (which is a major effort and lots of work).
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"BB_Reporting" <Bruce.Baessler@.web.de> wrote in message
news:1123855073.170843.218720@.o13g2000cwo.googlegroups.com...
> Hi,
>
> a lot of my users were asking me to have the possibilty of a freeze
> pane (similar to excel) in the RS web reports because often the reports
> are to long to see the column haeders. Especially when you have
> drilldowns it would help.
>
> Has somebody tried to implement such feature by DLL to RS 2000. Is this
> feature planned for RS 2005 '
>
> thanks
>
> BB
>|||Hi Robert,
I haven't had a chance to test this yet so, with fixed headers in 2005 will
the scroll position stay in place for auto-refreshing reports or will it be
a pain an spring back to the top?
Thanks in advance,
James Snape
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:O8oAI$goFHA.904@.TK2MSFTNGP10.phx.gbl...
> This is available in RS 2005.
> I don't know of anyone who has implemented this for RS 2000 - but in that
> case, you would need to implement a full custom rendering extension
> yourself for RS 2000 (which is a major effort and lots of work).
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "BB_Reporting" <Bruce.Baessler@.web.de> wrote in message
> news:1123855073.170843.218720@.o13g2000cwo.googlegroups.com...
>> Hi,
>>
>> a lot of my users were asking me to have the possibilty of a freeze
>> pane (similar to excel) in the RS web reports because often the reports
>> are to long to see the column haeders. Especially when you have
>> drilldowns it would help.
>>
>> Has somebody tried to implement such feature by DLL to RS 2000. Is this
>> feature planned for RS 2005 '
>>
>> thanks
>>
>> BB
>|||Auto-Refresh will remember the current page number, but it will not remember
the scroll position within the page.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"James Snape" <jim_snape.at.hotmail.com@.online.nospam> wrote in message
news:ubgRWP3oFHA.3988@.TK2MSFTNGP10.phx.gbl...
> Hi Robert,
> I haven't had a chance to test this yet so, with fixed headers in 2005
> will the scroll position stay in place for auto-refreshing reports or will
> it be a pain an spring back to the top?
> Thanks in advance,
> James Snape
> "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
> news:O8oAI$goFHA.904@.TK2MSFTNGP10.phx.gbl...
>> This is available in RS 2005.
>> I don't know of anyone who has implemented this for RS 2000 - but in that
>> case, you would need to implement a full custom rendering extension
>> yourself for RS 2000 (which is a major effort and lots of work).
>> -- Robert
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "BB_Reporting" <Bruce.Baessler@.web.de> wrote in message
>> news:1123855073.170843.218720@.o13g2000cwo.googlegroups.com...
>> Hi,
>>
>> a lot of my users were asking me to have the possibilty of a freeze
>> pane (similar to excel) in the RS web reports because often the reports
>> are to long to see the column haeders. Especially when you have
>> drilldowns it would help.
>>
>> Has somebody tried to implement such feature by DLL to RS 2000. Is this
>> feature planned for RS 2005 '
>>
>> thanks
>>
>> BB
>>
>

Freeze Panes in Excel

hi,
In matrix row and column grouping there is one property "Group Header should
remain visible while Scrolling",which is equal to Freeze panes property of
Excel.
But when i am exporting the report into Excel i am not getting the same
effect of Static Header.I have to manually set it to get that effort in
exported excel.
So what is the solution of this?
Thanks in advance.
DibakarOn Mar 2, 4:42 am, Dibakar <Diba...@.discussions.microsoft.com> wrote:
> hi,
> In matrix row and column grouping there is one property "Group Header should
> remain visible while Scrolling",which is equal to Freeze panes property of
> Excel.
> But when i am exporting the report into Excel i am not getting the same
> effect of Static Header.I have to manually set it to get that effort in
> exported excel.
> So what is the solution of this?
> Thanks in advance.
> Dibakar
As far as I know, the freeze panes functionality only exists in the
Reporting Services view of the report; though when exported to PDF,
the groups that are frozen are repeated on every page. Sorry that I
could not be of more assistance.
Regards,
Enrique Martinez
Sr. SQL Server Developer

Freeze first column in report

Is there a way to freeze the first column in a report as you scroll to the
right as in Excel?
Thanks in advance!!If you set the "FixedHeader" property of the column to True in the report
that should lock the column like you want.
--
Chris Alton, Microsoft Corp.
SQL Server Developer Support Engineer
This posting is provided "AS IS" with no warranties, and confers no rights.
--
> Thread-Topic: Freeze first column in report
> thread-index: Acg+eIAmNYrdHESSSlqSjULBWnIitA==> X-WBNR-Posting-Host: 65.168.98.110
> From: =?Utf-8?B?Q2FybCBIZW50aG9ybg==?=<CarlHenthorn@.discussions.microsoft.com>
> Subject: Freeze first column in report
> Date: Fri, 14 Dec 2007 09:41:04 -0800
> Is there a way to freeze the first column in a report as you scroll to
the
> right as in Excel?
> Thanks in advance!!
>

freeze columns (not headers

I have many columns out to the right. The name column is the first column in
the report. I wan to "freeze" the name column so that when you scroll to the
right in the report the name column is still visible.
From this forum i learn that freeze headers isn't available until RS 2005.
Is this the same case for Freeze columns?
Does anyone else try to do this?I'm not sure that Freeze Columns will even be available in the initial RS
2005 release. I haven't heard anything about it and it's a feature that I
would love to see.
Currently the only way to accomplish these things are to programmatically
pull reports from RS and save the resulting output file. Then your
application must open the Excel file and manipulate it directly. You'll need
to have a way for the application to know which columns/rows get frozen for
each report that you have.
It's doable, but you have to have a lot of custom code done and it does slow
down the delivery speed some.
"Phillip" wrote:
> I have many columns out to the right. The name column is the first column in
> the report. I wan to "freeze" the name column so that when you scroll to the
> right in the report the name column is still visible.
> From this forum i learn that freeze headers isn't available until RS 2005.
> Is this the same case for Freeze columns?
> Does anyone else try to do this?|||It will be available in RS 2005.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Todd" <Todd@.discussions.microsoft.com> wrote in message
news:F6011202-F219-48FB-8674-EEA5E938FEA2@.microsoft.com...
> I'm not sure that Freeze Columns will even be available in the initial RS
> 2005 release. I haven't heard anything about it and it's a feature that I
> would love to see.
> Currently the only way to accomplish these things are to programmatically
> pull reports from RS and save the resulting output file. Then your
> application must open the Excel file and manipulate it directly. You'll
> need
> to have a way for the application to know which columns/rows get frozen
> for
> each report that you have.
> It's doable, but you have to have a lot of custom code done and it does
> slow
> down the delivery speed some.
> "Phillip" wrote:
>> I have many columns out to the right. The name column is the first column
>> in
>> the report. I wan to "freeze" the name column so that when you scroll to
>> the
>> right in the report the name column is still visible.
>> From this forum i learn that freeze headers isn't available until RS
>> 2005.
>> Is this the same case for Freeze columns?
>> Does anyone else try to do this?

Wednesday, March 7, 2012

FreeTextTable on image column of document metadata

I would like to be able to search the metadata of files (e.g. Title,
Keywords,
etc) that I have stored in a database. I have included the "metainfo" column
(image) in the full-text catalog. Running a freetexttable on the documents
table returns matching results for web pages (.htm, .aspx), however for
other documents (e.g. word, excel, etc), I am getting no results. Is this
an IFilter issue? Am I missing something here?
Thanks in advance!
This will work in SQL 2005, however you will not be able to search on only
the property. The contains search will be on all properties.
In SQL 2000 you will have to extract the properties and store them in
seperate columns and SQL FTI them.
"Jeremy" <jmaddreysp@.cox.net> wrote in message
news:%23K$d4z9BFHA.3940@.TK2MSFTNGP09.phx.gbl...
>I would like to be able to search the metadata of files (e.g. Title,
> Keywords,
> etc) that I have stored in a database. I have included the "metainfo"
> column
> (image) in the full-text catalog. Running a freetexttable on the
> documents
> table returns matching results for web pages (.htm, .aspx), however for
> other documents (e.g. word, excel, etc), I am getting no results. Is this
> an IFilter issue? Am I missing something here?
> Thanks in advance!
>
|||Jeremy,
Could you post the full output of -- SELECT @.@.version -- as this is most
helpful in understanding your environment and will help more quickly answer
your questions.
If you're using SQL Server 7.0, you are limited to extracting the text from
file and storing this text in a column defined with TEXT or NText datatype.
I'm assuming from your comments that you're using SQL Server 2000, you can
store binary files (MS word, MS Excel, Adobe PDF files, etc.) in an column
defined with an IMAGE datatype. Note, you will need to also define a "file
extension" column that identifies the file type (use char(3), varchar(4) or
sysname) and populate this column with "doc", ".doc" respectively.
Can I assume that if you search on keywords that you know to be in the body
of the documents (e.g. word, excel, etc) that you do get the row that
contains that keyword returned in your CONTAINS or FREETEXT query?
Thanks,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Jeremy" <jmaddreysp@.cox.net> wrote in message
news:#K$d4z9BFHA.3940@.TK2MSFTNGP09.phx.gbl...
> I would like to be able to search the metadata of files (e.g. Title,
> Keywords,
> etc) that I have stored in a database. I have included the "metainfo"
column
> (image) in the full-text catalog. Running a freetexttable on the
documents
> table returns matching results for web pages (.htm, .aspx), however for
> other documents (e.g. word, excel, etc), I am getting no results. Is this
> an IFilter issue? Am I missing something here?
> Thanks in advance!
>
|||btw-you might want to check out
http://www.indexserverfaq.com/blobs.htm for more information on how to index
blobs.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OeOh$1%23BFHA.2392@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> This will work in SQL 2005, however you will not be able to search on only
> the property. The contains search will be on all properties.
> In SQL 2000 you will have to extract the properties and store them in
> seperate columns and SQL FTI them.
> "Jeremy" <jmaddreysp@.cox.net> wrote in message
> news:%23K$d4z9BFHA.3940@.TK2MSFTNGP09.phx.gbl...
this
>
|||John,
I am using SQL Server 2000. Here's my select statement:
SELECT * FROM FreeTextTable(Docs, MetaInfo, @.SearchTerm)
I have two columns defined on the document information, MetaInfo and Content
(as well as the extension column that stores the doc extension). When using
terms I know to be in the document, I can successfully return results from
the "Content" column, however, the "MetaInfo" column does not seem to return
any correct results (besides web pages). I have programmatically pulled the
column data and viewed the metainfo column in an application with no
problem. However, the freetexttable statement I'm using does not seem to be
finding the search term.
Ideas?
"John Kane" <jt-kane@.comcast.net> wrote in message
news:uxQNbJACFHA.1452@.TK2MSFTNGP11.phx.gbl...
> Jeremy,
> Could you post the full output of -- SELECT @.@.version -- as this is most
> helpful in understanding your environment and will help more quickly
answer
> your questions.
> If you're using SQL Server 7.0, you are limited to extracting the text
from
> file and storing this text in a column defined with TEXT or NText
datatype.
> I'm assuming from your comments that you're using SQL Server 2000, you can
> store binary files (MS word, MS Excel, Adobe PDF files, etc.) in an column
> defined with an IMAGE datatype. Note, you will need to also define a "file
> extension" column that identifies the file type (use char(3), varchar(4)
or
> sysname) and populate this column with "doc", ".doc" respectively.
> Can I assume that if you search on keywords that you know to be in the
body[vbcol=seagreen]
> of the documents (e.g. word, excel, etc) that you do get the row that
> contains that keyword returned in your CONTAINS or FREETEXT query?
> Thanks,
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
> "Jeremy" <jmaddreysp@.cox.net> wrote in message
> news:#K$d4z9BFHA.3940@.TK2MSFTNGP09.phx.gbl...
> column
> documents
this
>
|||Jeremy,
Actually, I have more follow-up questions as I want to be sure that I
understand your environment...
Could you confirm your FT-enabled table (Docs) column definitions? Content
defined with an IMAGE datatype. (Correct?) and MetaInfo defined with what
type of datatype? What is the content of the MetaInfo column? Is it document
property type data, such as Author, Company, Keywords, etc.? If so, how did
you import the text into this column?
Thanks!
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Jeremy" <jmaddreysp@.cox.net> wrote in message
news:uNVt0tHCFHA.3376@.TK2MSFTNGP12.phx.gbl...
> John,
> I am using SQL Server 2000. Here's my select statement:
> SELECT * FROM FreeTextTable(Docs, MetaInfo, @.SearchTerm)
> I have two columns defined on the document information, MetaInfo and
Content
> (as well as the extension column that stores the doc extension). When
using
> terms I know to be in the document, I can successfully return results from
> the "Content" column, however, the "MetaInfo" column does not seem to
return
> any correct results (besides web pages). I have programmatically pulled
the
> column data and viewed the metainfo column in an application with no
> problem. However, the freetexttable statement I'm using does not seem to
be[vbcol=seagreen]
> finding the search term.
> Ideas?
>
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:uxQNbJACFHA.1452@.TK2MSFTNGP11.phx.gbl...
> answer
> from
> datatype.
can[vbcol=seagreen]
column[vbcol=seagreen]
"file[vbcol=seagreen]
> or
> body
for
> this
>
|||On the Docs table, I have FT-enabled the following:
DocName nvarchar(128)
MetaInfo image(16)
Content image(16)
Here's an example of the MetaInfo that I have pulled out programmatically.
vti_cachedcustomprops:VX|vti_title
vti_modifiedby:SR|lab2003\\jmaddrey
vti_cachedtitle:SR|catsdogs
vti_title:SR|catsdogs
vti_author:SR|lab2003\\jmaddrey
When I perform the search "catsdogs", nothing is found. However, any text I
place within the body of the document is accurately returned.
In regards to importing the data into the metainfo column, I am working with
a Windows Sharepoint Services site, so the database was generated by
Sharepoint. I am not exactly sure how that data is extracted by the
Sharepoint framework.
Thanks!
Jeremy
"John Kane" <jt-kane@.comcast.net> wrote in message
news:enMKQrMCFHA.1404@.TK2MSFTNGP11.phx.gbl...
> Jeremy,
> Actually, I have more follow-up questions as I want to be sure that I
> understand your environment...
> Could you confirm your FT-enabled table (Docs) column definitions?
Content
> defined with an IMAGE datatype. (Correct?) and MetaInfo defined with what
> type of datatype? What is the content of the MetaInfo column? Is it
document
> property type data, such as Author, Company, Keywords, etc.? If so, how
did[vbcol=seagreen]
> you import the text into this column?
> Thanks!
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
> "Jeremy" <jmaddreysp@.cox.net> wrote in message
> news:uNVt0tHCFHA.3376@.TK2MSFTNGP12.phx.gbl...
> Content
> using
from[vbcol=seagreen]
> return
> the
to[vbcol=seagreen]
> be
most[vbcol=seagreen]
> can
> column
> "file
varchar(4)[vbcol=seagreen]
"metainfo"[vbcol=seagreen]
> for
Is
>
|||Anyone? Anyone?
"Jeremy" <jmaddreysp@.cox.net> wrote in message
news:uX$hrbTCFHA.1408@.TK2MSFTNGP10.phx.gbl...
> On the Docs table, I have FT-enabled the following:
> DocName nvarchar(128)
> MetaInfo image(16)
> Content image(16)
> Here's an example of the MetaInfo that I have pulled out programmatically.
> vti_cachedcustomprops:VX|vti_title
> vti_modifiedby:SR|lab2003\\jmaddrey
> vti_cachedtitle:SR|catsdogs
> vti_title:SR|catsdogs
> vti_author:SR|lab2003\\jmaddrey
> When I perform the search "catsdogs", nothing is found. However, any text
I
> place within the body of the document is accurately returned.
> In regards to importing the data into the metainfo column, I am working
with[vbcol=seagreen]
> a Windows Sharepoint Services site, so the database was generated by
> Sharepoint. I am not exactly sure how that data is extracted by the
> Sharepoint framework.
> Thanks!
> Jeremy
>
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:enMKQrMCFHA.1404@.TK2MSFTNGP11.phx.gbl...
> Content
what[vbcol=seagreen]
> document
> did
> from
pulled[vbcol=seagreen]
> to
> most
text[vbcol=seagreen]
you[vbcol=seagreen]
> varchar(4)
the[vbcol=seagreen]
that[vbcol=seagreen]
Title,[vbcol=seagreen]
> "metainfo"
however
> Is
>
|||Jeremy,
Sorry, for the late reply! I've had a lot of things going on lately, no
excuse, but just keeping me busy...
The thing about this is I don't have a real answer for you as metadata in
the MetaInfo column was generated by Sharepoint and I too am I am not
exactly sure how that data is extracted by the Sharepoint framework.
Fundamentally, and outside of Sharepoint, SQL FTS must have a valid file
extension, usually .doc for MS Word documents, or .pdf for Adobe PDF file
types as is with your Content column. This file extension is then used by
the MSSearch service and calls a daemon process to FT Index a specific file
type. However, there is no file extension column and file type that is
linked with the MetaInfo column (defined with the image datatype) in order
to successfully FT Index the metadata generated by SharePoint, I'm sad to
say...
At this point, I'd recommend that you open a support case with Microsoft PSS
Sharepoint support first (and if they can't help, then SQL Server support)
and have them help you through this understanding how to accomplish what
you're trying to do. If you don't mind, if they do provide a solution, could
you post it back here in this newsgroup for others to read?
Thanks,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Jeremy" <jmaddreysp@.cox.net> wrote in message
news:OhD8seVDFHA.512@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> Anyone? Anyone?
>
> "Jeremy" <jmaddreysp@.cox.net> wrote in message
> news:uX$hrbTCFHA.1408@.TK2MSFTNGP10.phx.gbl...
programmatically.[vbcol=seagreen]
text[vbcol=seagreen]
> I
> with
> what
how[vbcol=seagreen]
When[vbcol=seagreen]
results[vbcol=seagreen]
to[vbcol=seagreen]
> pulled
seem[vbcol=seagreen]
is[vbcol=seagreen]
quickly[vbcol=seagreen]
> text
> you
an[vbcol=seagreen]
a[vbcol=seagreen]
> the
> that
> Title,
the[vbcol=seagreen]
> however
results.
>
|||Can you explain what you mean by pulled out programmatically?
I took your content, stored it in and image data type column and using a
value of txt for the document column type I was able to query for catsdogs.
Can you perhaps tell us what the value of your document type column is?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Now available on Amazon.com
http://www.amazon.com/gp/product/off...?condition=all
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Jeremy" <jmaddreysp@.cox.net> wrote in message
news:uX$hrbTCFHA.1408@.TK2MSFTNGP10.phx.gbl...
> On the Docs table, I have FT-enabled the following:
> DocName nvarchar(128)
> MetaInfo image(16)
> Content image(16)
> Here's an example of the MetaInfo that I have pulled out programmatically.
> vti_cachedcustomprops:VX|vti_title
> vti_modifiedby:SR|lab2003\\jmaddrey
> vti_cachedtitle:SR|catsdogs
> vti_title:SR|catsdogs
> vti_author:SR|lab2003\\jmaddrey
> When I perform the search "catsdogs", nothing is found. However, any text
I
> place within the body of the document is accurately returned.
> In regards to importing the data into the metainfo column, I am working
with[vbcol=seagreen]
> a Windows Sharepoint Services site, so the database was generated by
> Sharepoint. I am not exactly sure how that data is extracted by the
> Sharepoint framework.
> Thanks!
> Jeremy
>
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:enMKQrMCFHA.1404@.TK2MSFTNGP11.phx.gbl...
> Content
what[vbcol=seagreen]
> document
> did
> from
pulled[vbcol=seagreen]
> to
> most
text[vbcol=seagreen]
you[vbcol=seagreen]
> varchar(4)
the[vbcol=seagreen]
that[vbcol=seagreen]
Title,[vbcol=seagreen]
> "metainfo"
however
> Is
>

FreeTextTable multiple Columns

I'm trying to do a FREETEXTTABLE search on two columns.
I can get it to work correctly with searching only one column.
I basically need it to do an OR on the two columns and haven't been
able to find anything on that. It seems like I'm limited to finding
matches that have it in both columns.
What I have so far is:
SELECT a.Name, b.rank, c.rank
FROM Test a, FreeTextTable(Test, Keywords, 'author') AS b,
FreeTextTable(Test, SolutionProblem, 'author) AS c
I picture it being in the WHERE clause as:
WHERE (FreeTextTable(Test, Keywords, 'author') OR FreeTextTable(Test,
SolutionProblem, 'author'))
but I haven't found an example where I can put it in the where clause.
Thanks for any help,
Tim
Tim,
Below is an example of using FREETEXTTABLE with two columns from the same
table (Northwind's Employee) with an OR condition on the join of the KEYs:
use Northwind
go
SELECT e.EmployeeID, e.LastName, e.FirstName, e.Title, e.Notes
from Employees AS e,
containstable(Employees, Notes, 'psychology') as A,
containstable(Employees, Title, 'Vice') as B
where
A.[KEY] = e.EmployeeID or
B.[KEY] = e.EmployeeID
-- returns 4 rows
Note, that with the below freetexttable query using 'include' as the search
word that 'includes' is retuned as freetext will often return more
"imprecise" results as would a samilar contains query.
SELECT e.EmployeeID, e.LastName, e.FirstName, e.Title, e.Notes
from Employees AS e,
freetexttable(Employees, Notes, 'include') as A,
freetexttable(Employees, Title, 'Vice') as B
where
A.[KEY] = e.EmployeeID or
B.[KEY] = e.EmployeeID
-- returns 2 rows
Hope this helps!
Regards,
John
"Tim" <tim_cavins@.hotmail.com> wrote in message
news:3020d2f4.0407071416.2866d84b@.posting.google.c om...
> I'm trying to do a FREETEXTTABLE search on two columns.
> I can get it to work correctly with searching only one column.
> I basically need it to do an OR on the two columns and haven't been
> able to find anything on that. It seems like I'm limited to finding
> matches that have it in both columns.
> What I have so far is:
> SELECT a.Name, b.rank, c.rank
> FROM Test a, FreeTextTable(Test, Keywords, 'author') AS b,
> FreeTextTable(Test, SolutionProblem, 'author) AS c
> I picture it being in the WHERE clause as:
> WHERE (FreeTextTable(Test, Keywords, 'author') OR FreeTextTable(Test,
> SolutionProblem, 'author'))
> but I haven't found an example where I can put it in the where clause.
> Thanks for any help,
> Tim

freetexttable issue

Hi,

We are using freetexttable as our search function in our application and it seems to be partially working. I search for a word from a column of a table I included in the search catalog and it sometimes pickup the record and sometimes it doesn't. I got 2 records having 'business' as the keyword. And when I search that keyword, it only returned 1 record.

here is the codesnippet:

select * from freetexttable(<tablename>, *, 'business')

Any help is greatly appreciated.

Baldwin

bbudiongan@.misicompany.com

Are the two keyword int he same column ? if no, are there different word breakers defined on the column ? Are the two columns freetextindexed ?

Jens Suessmeyer.

http://www.sqlserver2005.de

Freetext Search - SQL CONTAINS (column,"R-483*") FAILS

Hi
I would like to search for "R-483" phase using SQL CONTAINS
function :
DECLARE @.i_FreeText Varchar(255)
SET @.i_FreeText= '"r-483*"'
SELECT TOP 10 * from iBlockFreeText
where
(@.i_FreeText is null or contains(iBlockFreeText.Content,
@.i_FreeText))
, but it fails. It's okay when i use
SET @.i_FreeText= '"r-483"' (without asterix)
It's also okay when I search for
SET @.i_FreeText= '"filip-483*"'
I thought it's noise character problem, but after deleting noise words
in noise.* files nothing has changed... I have to use double quots ""
due to exact phases with spaces ex. "exact phase".
Can somebody help me ? Thank's in advance
Ragards
Filip Fiolka
This works for me. Exactly what do you mean by fails?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Filip Fiolka" <filip@.lac.gda.pl> wrote in message
news:f1eb0b6.0501070414.153a4007@.posting.google.co m...
> Hi
> I would like to search for "R-483" phase using SQL CONTAINS
> function :
> DECLARE @.i_FreeText Varchar(255)
> SET @.i_FreeText= '"r-483*"'
> SELECT TOP 10 * from iBlockFreeText
> where
> (@.i_FreeText is null or contains(iBlockFreeText.Content,
> @.i_FreeText))
> , but it fails. It's okay when i use
> SET @.i_FreeText= '"r-483"' (without asterix)
> It's also okay when I search for
> SET @.i_FreeText= '"filip-483*"'
> I thought it's noise character problem, but after deleting noise words
> in noise.* files nothing has changed... I have to use double quots ""
> due to exact phases with spaces ex. "exact phase".
> Can somebody help me ? Thank's in advance
> Ragards
> Filip Fiolka
|||Filip,
Sure, I can. Could you post the full output of -- SELECT @.@.version -- as
this information is most helpful in troubleshooting SQL FTS issues. Did you
delete all words in the language specific noise word file or just the single
letters? What noise word file did you delete the words from? Did you run a
Full Population after deleting these words?
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Filip Fiolka" <filip@.lac.gda.pl> wrote in message
news:f1eb0b6.0501070414.153a4007@.posting.google.co m...
> Hi
> I would like to search for "R-483" phase using SQL CONTAINS
> function :
> DECLARE @.i_FreeText Varchar(255)
> SET @.i_FreeText= '"r-483*"'
> SELECT TOP 10 * from iBlockFreeText
> where
> (@.i_FreeText is null or contains(iBlockFreeText.Content,
> @.i_FreeText))
> , but it fails. It's okay when i use
> SET @.i_FreeText= '"r-483"' (without asterix)
> It's also okay when I search for
> SET @.i_FreeText= '"filip-483*"'
> I thought it's noise character problem, but after deleting noise words
> in noise.* files nothing has changed... I have to use double quots ""
> due to exact phases with spaces ex. "exact phase".
> Can somebody help me ? Thank's in advance
> Ragards
> Filip Fiolka
|||Thank's for reply. I realized that I cannot delete all noise words from
noise.* file- I should leave space at last. After that I could find my
'"R-483*"' product. But I cannot still see the reason why :
CONTAINS(column,'R-438')
and
CONTAINS(column,'"R-438"')
works, but
CONTAINS(column,'"R-438*"') fails when "r" is a noise word?!
Perhaps You will be able to explain that.
SELECT @.@.version :
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on
Windows NT 5.2 (Build 3790: )
Thank's in advance
Filip Fiolka
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||You're welcome, Filip Fiolka,
This is why I ask for the @.@.version information in nearly all of my initial
replies. You have SQL Server 2000 SP3 installed on Windows Server 2003
(Windows NT 5.2) and this OS Platform uses a new wordbreaker dll
(langwbrk.dll) that breaks the string "R-438" as follows:
Original text: 'R-438'
IWordSink::PutWord: cwcSrcLen 1, cwcSrcPos 0, cwc 1, 'R'
IWordSink::PutAltWord: cwcSrcLen 3, cwcSrcPos 2, cwc 3, '438'
IWordSink::PutWord: cwcSrcLen 3, cwcSrcPos 2, cwc 5, 'NN438'
NN438 indicates that Win2003 treats this as a number. In the FT-enable
table's column text, do you have other rows of values, such as "R-4385" or
"R-43800" or is the trailing values alphanumeric values?
Thanks,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"FIlip FIolka" <filip@.lac.gda.pl> wrote in message
news:eWU#n6u9EHA.3624@.TK2MSFTNGP10.phx.gbl...
> Thank's for reply. I realized that I cannot delete all noise words from
> noise.* file- I should leave space at last. After that I could find my
> '"R-483*"' product. But I cannot still see the reason why :
> CONTAINS(column,'R-438')
> and
> CONTAINS(column,'"R-438"')
> works, but
> CONTAINS(column,'"R-438*"') fails when "r" is a noise word?!
> Perhaps You will be able to explain that.
> SELECT @.@.version :
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on
> Windows NT 5.2 (Build 3790: )
> Thank's in advance
> Filip Fiolka
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||There are no other values like %R-438%. "R-483" is a specyfic name, so
there are no trailing values of that ("R-483" is never a prefix). My
FT-enable table's column rows consist of file start content.
Thanks
Filip Fiolka
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Filip,
Ok, then the Windows Server 2003 (Win2003) wordbreaker dll - langwbrk.dll -
is treating this number "438" as a number:
IWordSink::PutAltWord: cwcSrcLen 3, cwcSrcPos 2, cwc 3, '438'
IWordSink::PutWord: cwcSrcLen 3, cwcSrcPos 2, cwc 5, 'NN438'
and not allowing any stemming of this number and therefore ignoring the
trailing wildcard "*". Specifically, when this query is executed:
CONTAINS(column,'"R-438*"')
and "R" is a noise word, the hyphen or dash "-" is thrown away and all that
is left is to return rows that contain the number 438. This behavior is
specific to Win2003 and its wordbreaker langwbrk.dll
Hope that helps,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"FIlip FIolka" <filip@.lac.gda.pl> wrote in message
news:ejOnLPh#EHA.2580@.TK2MSFTNGP15.phx.gbl...
> There are no other values like %R-438%. "R-483" is a specyfic name, so
> there are no trailing values of that ("R-483" is never a prefix). My
> FT-enable table's column rows consist of file start content.
> Thanks
> Filip Fiolka
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!

FREETEXT Help please

Hi All... I'm trying to play around with the "FREETEXT" function to see if it might help us with our application. I have a column of type "text" and I understand it needs to have the Full-text specification set to yes. But I cant seem to figure out how to do that. Can anyone help me out with this please?

Thanks! -- Curt

FreeText is not a function, it is a predicate used for FullText index searches, if you cannot find Full Text the questions is what version and edition of SQL Server are you using. In 2000 it is a separate install and it is not avalable in Express by default you have to use code to implement it. All other editions of 2005 you can enable it either from the menu or right click in Management Studio. Here are all the Full Text predicates CONTAINS, CONTAINSTABLE, FREETEXT and FREETEXTTBALE, run a search for all in the BOL(books online). Hope this helps.

FREETEXT FILTER ON FREETEXTTABLE USING COLUMN LIST FOR REFINED SEARCH

I am trying to do a freetext filter with mutiple columns using a column list, but I can't get the syntax down for multiple column list. First, am I am going about this the right way...Do I need to be doing both? Second why doesn't mutiple columns work. I can't find any good samples online. What I am trying to accomplish is a refined search stored procedure that uses the freetext to do the search refinement. Any help would be appreciated.

select

b.rank,

a.ProductID,

a.ProductName,

a.Sequence,

a.ProductImage,

a.ItemID,

a.ItemName,

a.ManufacturerItemCode,

a.ItemImage,

a.ItemSourceID,

a.PackageID,

a.BrandID,

a.BrandName,

a.ManufacturerID,

a.ManufacturerName,

a.ProductCategoryID,

a.CategoryID,

a.CategoryName,

d.CustomerGroupName,

isnull(h.PackageDescription,a.ItemPKG) as PKG,

case g.StockStatus

when 1 then 'Yes'

when 0 then 'No'

else ''

end as StockStatus,

isnull(g.StandardUnitPrice,a.ListPrice) as Price,

isnull(j.SupplierAbbreviation,a.ManufacturerAbbreviation) as ItemSource

from

dbo.vw_mcProductItem a

inner join freetexttable(dbo.vw_mcProductItem, (ProductName,ItemName,ManufacturerItemCode,ItemPKG,BrandName,ManufacturerName,ManufacturerAbbreviation,CategoryName), @.SearchWord) as b ON a.ItemID = b.[KEY]

inner join [dbo].[mcCustomerGroupItem] c on c.ItemID = a.ItemID

inner join [dbo].[mcCustomerGroup] d on d.CustomerGroupID = c.CustomerGroupID

inner join [dbo].[mcCustomerGroupCustomer] e on e.CustomerGroupID = d.CustomerGroupID

inner join [dbo].[mcCustomerUser] f on f.CustomerID = e.CustomerID

left outer join [dbo].[mcSupplierItem] g on g.ItemID = a.ItemID

left outer join [dbo].[mcPackage] h on h.PackageID = g.SellingPackageID

left outer join [dbo].[mcItemSource] i on i.ItemSourceId = a.ItemSourceId

left outer join [dbo].[mcSupplier] j on j.SupplierID = g.SupplierID

where

d.CustomerGroupID = @.CustomerGroupID

and f.UserID = @.UserID

and FREETEXT(BrandName,ManufacturerName,CategoryName, @.SearchWord)

Freetext takes either a column or all (*). So, your query should be changed.

e.g.

Code Snippet

and (

FREETEXT(BrandName, @.SearchWord)

or

and FREETEXT(ManufacturerName, @.SearchWord)

or

and FREETEXT(CategoryName, @.SearchWord)

)

Friday, February 24, 2012

Freaking column names

I'm dealing with a database with tables that have freaking columns.
Partial DDL:
Create table [tbl IMN] ([Ave Cost-Mn] varchar(10))
-- yeah, this column contains string value

Now, I'd like to rename all these freaking columns without special
charaters like '-', 'whitespace' etc systematically (meaning loop
through all tables and columns dynamically). It seems that the
sp_rename proc can't handle some function call or ...?
e.g.
exec sp_rename '[tbl-IMN].[Ave Cost-Mn]',replace('[Ave
Cost-Mn]','-',''),'COLUMN'

better if it can work,
exec sp_rename '[tbl-IMN].[Ave Cost-Mn]',replace('[Ave Cost-Mn]','-|
',''),'COLUMN'

TIANickName (dadada@.rock.com) writes:
> I'm dealing with a database with tables that have freaking columns.
> Partial DDL:
> Create table [tbl IMN] ([Ave Cost-Mn] varchar(10))
> -- yeah, this column contains string value
> Now, I'd like to rename all these freaking columns without special
> charaters like '-', 'whitespace' etc systematically (meaning loop
> through all tables and columns dynamically). It seems that the
> sp_rename proc can't handle some function call or ...?
> e.g.
> exec sp_rename '[tbl-IMN].[Ave Cost-Mn]',replace('[Ave
> Cost-Mn]','-',''),'COLUMN'
> better if it can work,
> exec sp_rename '[tbl-IMN].[Ave Cost-Mn]',replace('[Ave Cost-Mn]','-|
> ',''),'COLUMN'

Correct. In difference to most other languages, you cannot pass
expressions as parameters to stored procedures. You can only psss
constants and variables.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||How about this? Rather than a stored procedure, generate a script to
run against all of your columns, like so:

SELECT 'exec sp_rename ''' + Table_Name + '.'
+ Column_name + ''', ''' +
REPLACE(COLUMN_NAME, '-', '') + ''', ''COLUMN'''
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%-%'

Cut and paste the results of the query into a new QA window and execute
the statements.

HTH,
Stu|||Actually, I could do something like
select @.colTemp = Replace(col,'-','')
then
exec sp_rename '[tbl-IMN].[Ave Cost-Mn]',@.colTemp,'COLUMN'

I thought about that before posting but I did not try (just don't know
why somtimes I'm so freaking lazy).

Thanks for the note though.

Don|||Interesting idea, however, I don't think it works.|||Worked in my test bed; what error did you get? Just to make sure that
I was clear, the above command will NOT execute the stored procedure;
it'll just generate a script of all the columns in all the tables in
your database with a '-' in the name, like so:

exec sp_rename 'SPLAT.Check-ID', 'CheckID', 'COLUMN'
exec sp_rename 'SPLAT.Check-ID2', 'CheckID2', 'COLUMN'

You have to cut and paste that script into a new window in query
analyzer to actually execute the changes.

You could, of course, modify the SQL statement to actually execute the
stored procedure for you; I just prefer to do it this way so I can
visually check what I'm about to execute.

Stu|||Yeah, I see. Problem resolved. Thanks though.
Stu wrote:
> Worked in my test bed; what error did you get? Just to make sure that
> I was clear, the above command will NOT execute the stored procedure;
> it'll just generate a script of all the columns in all the tables in
> your database with a '-' in the name, like so:
> exec sp_rename 'SPLAT.Check-ID', 'CheckID', 'COLUMN'
> exec sp_rename 'SPLAT.Check-ID2', 'CheckID2', 'COLUMN'
> You have to cut and paste that script into a new window in query
> analyzer to actually execute the changes.
> You could, of course, modify the SQL statement to actually execute the
> stored procedure for you; I just prefer to do it this way so I can
> visually check what I'm about to execute.
> Stu

Freakin X-files type unexplained bull crap...

People
Scenario 1
I have a small DEMOGRAPHICS table that has close to 500000 rows of data. It has a column POSTALCODE. This table has no indexes/keys yet. The database size as per SQL Server enterprise manager is 77MB. I create a clustered index on the POSTALCODE column. The database size now is 135MB and space available is 24MB. I shrink the database. The database size is now back to 77MB. YIKES! Does the clustered index take no space'?
Scenario 2
I have a small DEMOGRAPHICS table that has close to 500000 rows of data. It has a column POSTALCODE. This table has no indexes/keys yet. The database size as per SQL Server enterprise manager is 77MB. I create a clustered index on the POSTALCODE column. The database size now is 135MB and space available is 24MB.
I run sp_spaceused @.updateusage=true. The database size is now 135MB and the space available is 74MB. Want more? I shrink the database and lo behold everything's intact!!!!!!!!!!!!! What the f...
Can anyone, I mean ANYONE, including all you MVPs explain this sort of a typical MS crap
Thanks.This is a multi-part message in MIME format.
--=_NextPart_000_0095_01C3F0B0.98CB5A80
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: 7bit
No surprises here. In order to create a clustered index on a table, you
need 1.2X the size of the table's data in free space available. This
equates to 1.2*77MB = 92.4MB - in addition to the existing 77MB. Once the
clustered index has been created, it frees up that space again. Thus, your
database will have a fair bit of unused space when you are done.
Check out Kalen Delaney's "Inside SQl Server 2000" for more details.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Frustrated..." <anonymous@.discussions.microsoft.com> wrote in message
news:746F0D84-3ABF-4E75-9FF4-56434676608C@.microsoft.com...
People,
Scenario 1:
I have a small DEMOGRAPHICS table that has close to 500000 rows of data. It
has a column POSTALCODE. This table has no indexes/keys yet. The database
size as per SQL Server enterprise manager is 77MB. I create a clustered
index on the POSTALCODE column. The database size now is 135MB and space
available is 24MB. I shrink the database. The database size is now back to
77MB. YIKES! Does the clustered index take no space'
Scenario 2:
I have a small DEMOGRAPHICS table that has close to 500000 rows of data. It
has a column POSTALCODE. This table has no indexes/keys yet. The database
size as per SQL Server enterprise manager is 77MB. I create a clustered
index on the POSTALCODE column. The database size now is 135MB and space
available is 24MB.
I run sp_spaceused @.updateusage=true. The database size is now 135MB and the
space available is 74MB. Want more? I shrink the database and lo behold
everything's intact!!!!!!!!!!!!! What the f...?
Can anyone, I mean ANYONE, including all you MVPs explain this sort of a
typical MS crap?
Thanks.
--=_NextPart_000_0095_01C3F0B0.98CB5A80
Content-Type: text/html;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
=EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

No surprises here. In order to =create a clustered index on a table, you need 1.2X the size of the table's data =in free space available. This equates to 1.2*77MB =3D 92.4MB - in addition =to the existing 77MB. Once the clustered index has been created, it frees =up that space again. Thus, your database will have a fair bit of unused =space when you are done.
Check out Kalen Delaney's "Inside SQl =Server 2000" for more details.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Frustrated..." wrote in message news:746=F0D84-3ABF-4E75-9FF4-56434676608C@.microsoft.com...People,Scenario 1:I have a small DEMOGRAPHICS table that has close to 500000 rows of =data. It has a column POSTALCODE. This table has no indexes/keys yet. The =database size as per SQL Server enterprise manager is 77MB. I create a clustered =index on the POSTALCODE column. The database size now is 135MB and space =available is 24MB. I shrink the database. The database size is now back to 77MB. =YIKES! Does the clustered index take no space'Scenario 2:I have a =small DEMOGRAPHICS table that has close to 500000 rows of data. It has a =column POSTALCODE. This table has no indexes/keys yet. The database size as per =SQL Server enterprise manager is 77MB. I create a clustered index on the =POSTALCODE column. The database size now is 135MB and space available is 24MB. =I run sp_spaceused @.updateusage=3Dtrue. The database size is now 135MB and =the space available is 74MB. Want more? I shrink the database and lo behold =everything's intact!!!!!!!!!!!!! What the f...?Can anyone, I mean ANYONE, =including all you MVPs explain this sort of a typical MS crap?Thanks.

--=_NextPart_000_0095_01C3F0B0.98CB5A80--|||Don't forget that any non-clustered indexes are also de-allocated, but that
space is not freed until the overall transaction is complete, so that the
existing indexes can be snapped back in if need be.
If you're worried about this space usage, you could always use
SORT_IN_TEMPDB, althought that'll use more space, just not in your local
database. (and you could have TempDB on a different set of spindles).
James Hokes
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23%23eTGqN8DHA.2764@.TK2MSFTNGP09.phx.gbl...
No surprises here. In order to create a clustered index on a table, you
need 1.2X the size of the table's data in free space available. This
equates to 1.2*77MB = 92.4MB - in addition to the existing 77MB. Once the
clustered index has been created, it frees up that space again. Thus, your
database will have a fair bit of unused space when you are done.
Check out Kalen Delaney's "Inside SQl Server 2000" for more details.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Frustrated..." <anonymous@.discussions.microsoft.com> wrote in message
news:746F0D84-3ABF-4E75-9FF4-56434676608C@.microsoft.com...
People,
Scenario 1:
I have a small DEMOGRAPHICS table that has close to 500000 rows of data. It
has a column POSTALCODE. This table has no indexes/keys yet. The database
size as per SQL Server enterprise manager is 77MB. I create a clustered
index on the POSTALCODE column. The database size now is 135MB and space
available is 24MB. I shrink the database. The database size is now back to
77MB. YIKES! Does the clustered index take no space'
Scenario 2:
I have a small DEMOGRAPHICS table that has close to 500000 rows of data. It
has a column POSTALCODE. This table has no indexes/keys yet. The database
size as per SQL Server enterprise manager is 77MB. I create a clustered
index on the POSTALCODE column. The database size now is 135MB and space
available is 24MB.
I run sp_spaceused @.updateusage=true. The database size is now 135MB and the
space available is 74MB. Want more? I shrink the database and lo behold
everything's intact!!!!!!!!!!!!! What the f...?
Can anyone, I mean ANYONE, including all you MVPs explain this sort of a
typical MS crap?
Thanks.|||Thanks. If you read carefully, you will see that I am NOT surprised at the fact that a clustered index took space. Quite the contrary actually.
-- Tom Moreau wrote: --
No surprises here. In order to create a clustered index on a table, yo
need 1.2X the size of the table's data in free space available. Thi
equates to 1.2*77MB = 92.4MB - in addition to the existing 77MB. Once th
clustered index has been created, it frees up that space again. Thus, you
database will have a fair bit of unused space when you are done
Check out Kalen Delaney's "Inside SQl Server 2000" for more details
-
To
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDB
SQL Server MV
Columnist, SQL Server Professiona
Toronto, ON Canad
www.pinnaclepublishing.com/sq
"Frustrated..." <anonymous@.discussions.microsoft.com> wrote in messag
news:746F0D84-3ABF-4E75-9FF4-56434676608C@.microsoft.com..
People
Scenario 1
I have a small DEMOGRAPHICS table that has close to 500000 rows of data. I
has a column POSTALCODE. This table has no indexes/keys yet. The databas
size as per SQL Server enterprise manager is 77MB. I create a clustere
index on the POSTALCODE column. The database size now is 135MB and spac
available is 24MB. I shrink the database. The database size is now back t
77MB. YIKES! Does the clustered index take no space'?
Scenario 2
I have a small DEMOGRAPHICS table that has close to 500000 rows of data. I
has a column POSTALCODE. This table has no indexes/keys yet. The databas
size as per SQL Server enterprise manager is 77MB. I create a clustere
index on the POSTALCODE column. The database size now is 135MB and spac
available is 24MB
I run sp_spaceused @.updateusage=true. The database size is now 135MB and th
space available is 74MB. Want more? I shrink the database and lo behol
everything's intact!!!!!!!!!!!!! What the f...
Can anyone, I mean ANYONE, including all you MVPs explain this sort of
typical MS crap
Thanks|||This is a multi-part message in MIME format.
--=_NextPart_000_0104_01C3F0B4.E5AA0410
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: 7bit
Then I guess I don't understand your problem. The growth of the database is
in reaction to having to create a clustered index. It frees the space - but
does not shrink the database - when the index build is complete.
As for the use of @.updateusage, a better test would be to update the usage
before and after the clustered index build, in order to get accurate
information. It appears from your experience that Enterprise Manager did
not update usage before querying the space used.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Frustrated..." <anonymous@.discussions.microsoft.com> wrote in message
news:8418A72E-DBE0-41A2-AE13-8B13B41C3E1A@.microsoft.com...
Thanks. If you read carefully, you will see that I am NOT surprised at the
fact that a clustered index took space. Quite the contrary actually.
-- Tom Moreau wrote: --
No surprises here. In order to create a clustered index on a table,
you
need 1.2X the size of the table's data in free space available. This
equates to 1.2*77MB = 92.4MB - in addition to the existing 77MB. Once
the
clustered index has been created, it frees up that space again. Thus,
your
database will have a fair bit of unused space when you are done.
Check out Kalen Delaney's "Inside SQl Server 2000" for more details.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Frustrated..." <anonymous@.discussions.microsoft.com> wrote in message
news:746F0D84-3ABF-4E75-9FF4-56434676608C@.microsoft.com...
People,
Scenario 1:
I have a small DEMOGRAPHICS table that has close to 500000 rows of
data. It
has a column POSTALCODE. This table has no indexes/keys yet. The
database
size as per SQL Server enterprise manager is 77MB. I create a clustered
index on the POSTALCODE column. The database size now is 135MB and
space
available is 24MB. I shrink the database. The database size is now back
to
77MB. YIKES! Does the clustered index take no space'
Scenario 2:
I have a small DEMOGRAPHICS table that has close to 500000 rows of
data. It
has a column POSTALCODE. This table has no indexes/keys yet. The
database
size as per SQL Server enterprise manager is 77MB. I create a clustered
index on the POSTALCODE column. The database size now is 135MB and
space
available is 24MB.
I run sp_spaceused @.updateusage=true. The database size is now 135MB
and the
space available is 74MB. Want more? I shrink the database and lo behold
everything's intact!!!!!!!!!!!!! What the f...?
Can anyone, I mean ANYONE, including all you MVPs explain this sort of
a
typical MS crap?
Thanks
--=_NextPart_000_0104_01C3F0B4.E5AA0410
Content-Type: text/html;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
=EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

Then I guess I don't understand your problem. The growth of the database is in reaction to having to =create a clustered index. It frees the space - but does not shrink the =database - when the index build is complete.
As for the use of @.updateusage, a =better test would be to update the usage before and after the clustered index build, =in order to get accurate information. It appears from your experience =that Enterprise Manager did not update usage before querying the space used.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Frustrated..." wrote in message news:841=8A72E-DBE0-41A2-AE13-8B13B41C3E1A@.microsoft.com...Thanks. If you read carefully, you will see that I am NOT surprised at the fact =that a clustered index took space. Quite the contrary actually. -- Tom =Moreau wrote: -- No =surprises here. In order to create a clustered index on a table, you need 1.2X the size of the table's data =in free space available. This equates to =1.2*77MB =3D 92.4MB - in addition to the existing 77MB. Once the clustered index has been created, it =frees up that space again. Thus, your database =will have a fair bit of unused space when you are =done. Check out Kalen Delaney's "Inside SQl =Server 2000" for more details. = -- Tom --- = Thomas A. Moreau, BSc, PhD, MCSE, MCDBA SQL =Server MVP Columnist, SQL Server Professional Toronto, ON Canada http://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql ="Frustrated..." wrote in message news:746=F0D84-3ABF-4E75-9FF4-56434676608C@.microsoft.com... &nb=sp; People, =Scenario 1: I have a small DEMOGRAPHICS table that =has close to 500000 rows of data. It has a column =POSTALCODE. This table has no indexes/keys yet. The =database size as per SQL Server enterprise manager is 77MB. I create a clustered index on the POSTALCODE column. =The database size now is 135MB and space =available is 24MB. I shrink the database. The database size is now back to 77MB. YIKES! Does the clustered index =take no space' =Scenario 2: I have a small DEMOGRAPHICS table that =has close to 500000 rows of data. It has a column =POSTALCODE. This table has no indexes/keys yet. The =database size as per SQL Server enterprise manager is 77MB. I create a clustered index on the POSTALCODE column. =The database size now is 135MB and space =available is 24MB. I run sp_spaceused @.updateusage=3Dtrue. The database size is now 135MB and the space available is 74MB. Want more? I =shrink the database and lo behold everything's intact!!!!!!!!!!!!! What the f...? Can anyone, I mean ANYONE, including all =you MVPs explain this sort of a typical MS crap? Thanks

--=_NextPart_000_0104_01C3F0B4.E5AA0410--|||Your frustration stems from a fundamental lack of understanding... A table
that took up 77MB with no indexes on it would still take up 77MB once a
clustered index is applied because a clustered index is the same thing as an
Index Organized Table in Oracle (if you are familiar with Oracle).
Basically the leaf level of the clustered index IS the table. This is the
reason you can have only one per table. Obviously, there would be a few
extra pages for the top and middle layers of the index, but in most cases
the amount of space that they occupy are negligible.
Now, if you create non-clustered indexes those will definitely use extra
space...
"Frustrated..." <anonymous@.discussions.microsoft.com> wrote in message
news:8418A72E-DBE0-41A2-AE13-8B13B41C3E1A@.microsoft.com...
> Thanks. If you read carefully, you will see that I am NOT surprised at the
fact that a clustered index took space. Quite the contrary actually.
> -- Tom Moreau wrote: --
> No surprises here. In order to create a clustered index on a table,
you
> need 1.2X the size of the table's data in free space available. This
> equates to 1.2*77MB = 92.4MB - in addition to the existing 77MB.
Once the
> clustered index has been created, it frees up that space again.
Thus, your
> database will have a fair bit of unused space when you are done.
> Check out Kalen Delaney's "Inside SQl Server 2000" for more details.
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
>
> "Frustrated..." <anonymous@.discussions.microsoft.com> wrote in
message
> news:746F0D84-3ABF-4E75-9FF4-56434676608C@.microsoft.com...
> People,
> Scenario 1:
> I have a small DEMOGRAPHICS table that has close to 500000 rows of
data. It
> has a column POSTALCODE. This table has no indexes/keys yet. The
database
> size as per SQL Server enterprise manager is 77MB. I create a
clustered
> index on the POSTALCODE column. The database size now is 135MB and
space
> available is 24MB. I shrink the database. The database size is now
back to
> 77MB. YIKES! Does the clustered index take no space'
> Scenario 2:
> I have a small DEMOGRAPHICS table that has close to 500000 rows of
data. It
> has a column POSTALCODE. This table has no indexes/keys yet. The
database
> size as per SQL Server enterprise manager is 77MB. I create a
clustered
> index on the POSTALCODE column. The database size now is 135MB and
space
> available is 24MB.
> I run sp_spaceused @.updateusage=true. The database size is now 135MB
and the
> space available is 74MB. Want more? I shrink the database and lo
behold
> everything's intact!!!!!!!!!!!!! What the f...?
> Can anyone, I mean ANYONE, including all you MVPs explain this sort
of a
> typical MS crap?
> Thanks|||This is a multi-part message in MIME format.
--=_NextPart_000_0027_01C3F0C9.D41032F0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
One other reason for the apparent discrepancy is that the un-indexed table
could have been heavily fragmented. Adding a clustered index automatically
defrags the table.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
.
"Don Peterson" <no1@.nunya.com> wrote in message
news:ejwVbtO8DHA.1040@.TK2MSFTNGP10.phx.gbl...
Your frustration stems from a fundamental lack of understanding... A table
that took up 77MB with no indexes on it would still take up 77MB once a
clustered index is applied because a clustered index is the same thing as an
Index Organized Table in Oracle (if you are familiar with Oracle).
Basically the leaf level of the clustered index IS the table. This is the
reason you can have only one per table. Obviously, there would be a few
extra pages for the top and middle layers of the index, but in most cases
the amount of space that they occupy are negligible.
Now, if you create non-clustered indexes those will definitely use extra
space...
"Frustrated..." <anonymous@.discussions.microsoft.com> wrote in message
news:8418A72E-DBE0-41A2-AE13-8B13B41C3E1A@.microsoft.com...
> Thanks. If you read carefully, you will see that I am NOT surprised at the
fact that a clustered index took space. Quite the contrary actually.
> -- Tom Moreau wrote: --
> No surprises here. In order to create a clustered index on a table,
you
> need 1.2X the size of the table's data in free space available. This
> equates to 1.2*77MB = 92.4MB - in addition to the existing 77MB.
Once the
> clustered index has been created, it frees up that space again.
Thus, your
> database will have a fair bit of unused space when you are done.
> Check out Kalen Delaney's "Inside SQl Server 2000" for more details.
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
>
> "Frustrated..." <anonymous@.discussions.microsoft.com> wrote in
message
> news:746F0D84-3ABF-4E75-9FF4-56434676608C@.microsoft.com...
> People,
> Scenario 1:
> I have a small DEMOGRAPHICS table that has close to 500000 rows of
data. It
> has a column POSTALCODE. This table has no indexes/keys yet. The
database
> size as per SQL Server enterprise manager is 77MB. I create a
clustered
> index on the POSTALCODE column. The database size now is 135MB and
space
> available is 24MB. I shrink the database. The database size is now
back to
> 77MB. YIKES! Does the clustered index take no space'
> Scenario 2:
> I have a small DEMOGRAPHICS table that has close to 500000 rows of
data. It
> has a column POSTALCODE. This table has no indexes/keys yet. The
database
> size as per SQL Server enterprise manager is 77MB. I create a
clustered
> index on the POSTALCODE column. The database size now is 135MB and
space
> available is 24MB.
> I run sp_spaceused @.updateusage=true. The database size is now 135MB
and the
> space available is 74MB. Want more? I shrink the database and lo
behold
> everything's intact!!!!!!!!!!!!! What the f...?
> Can anyone, I mean ANYONE, including all you MVPs explain this sort
of a
> typical MS crap?
> Thanks
--=_NextPart_000_0027_01C3F0C9.D41032F0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

One other reason for the apparent =discrepancy is that the un-indexed table could have been heavily fragmented. =Adding a clustered index automatically defrags the table.
-- Tom
----Thomas A. =Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql.
"Don Peterson" wrote in message news:ejwVbtO8DHA.1040=@.TK2MSFTNGP10.phx.gbl...Your frustration stems from a fundamental lack of understanding... A tablethat took up 77MB with no indexes on it would still take up =77MB once aclustered index is applied because a clustered index is the same =thing as anIndex Organized Table in Oracle (if you are familiar with Oracle).Basically the leaf level of the clustered index IS the =table. This is thereason you can have only one per table. Obviously, =there would be a fewextra pages for the top and middle layers of the =index, but in most casesthe amount of space that they occupy are =negligible.Now, if you create non-clustered indexes those will definitely use extraspace..."Frustrated..." wrote in messagenews:841=8A72E-DBE0-41A2-AE13-8B13B41C3E1A@.microsoft.com...> Thanks. If you read carefully, you will see that I am NOT surprised at thefact that a clustered index took space. Quite the contrary actually.>> -- Tom Moreau =wrote: -->> No surprises =here. In order to create a clustered index on a table,you> need 1.2X the size =of the table's data in free space available. This> equates to 1.2*77MB =3D =92.4MB - in addition to the existing 77MB.Once =the> clustered index has been created, it frees up that space again.Thus, =your> database will have a fair bit =of unused space when you are =done.>> Check out Kalen Delaney's "Inside SQl Server 2000" for more details.>> --> Tom>> --->&n=bsp; Thomas A. Moreau, BSc, PhD, MCSE, =MCDBA> SQL Server MVP> Columnist, SQL =Server Professional> Toronto, ON Canada>http://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql>>> "Frustrated..." wrote inmessage> news:746=F0D84-3ABF-4E75-9FF4-56434676608C@.microsoft.com...> =; People,>> Scenario 1:> I have a small DEMOGRAPHICS =table that has close to 500000 rows ofdata. =It> has a column POSTALCODE. This table has no indexes/keys yet. Thedatabase> size as per SQL =Server enterprise manager is 77MB. I create aclustered> index on the =POSTALCODE column. The database size now is 135MB andspace> available is 24MB. I =shrink the database. The database size is nowback to> 77MB. YIKES! Does the clustered =index take no space'>> =Scenario 2:> I have a small DEMOGRAPHICS =table that has close to 500000 rows ofdata. =It> has a column POSTALCODE. This table has no indexes/keys yet. Thedatabase> size as per SQL =Server enterprise manager is 77MB. I create aclustered> index on the =POSTALCODE column. The database size now is 135MB andspace> available is 24MB.>> I run sp_spaceused @.updateusage=3Dtrue. The database size is now 135MBand the> space available is 74MB. Want =more? I shrink the database and =lobehold> everything's intact!!!!!!!!!!!!! What the f...?>> Can anyone, I mean =ANYONE, including all you MVPs explain this sortof a> typical MS crap?>> =Thanks

--=_NextPart_000_0027_01C3F0C9.D41032F0--

Monday, March 26, 2012

FT Index on column with multiple language doesn't work - any ideas

Wednesday, March 21, 2012

front end options for entering data into an oracle database

Fromatting excel column

Fromatting excel column

From varchar(max) to xml

From Sql Column to file, OPENROWSET?

OPENROWSET

Friday, March 9, 2012

'freezing' table heading

freezing column and header in report

Freezed column headers in RS Webreports

Freeze Panes in Excel

Freeze first column in report

freeze columns (not headers

Wednesday, March 7, 2012

FreeTextTable on image column of document metadata

FreeTextTable multiple Columns

freetexttable issue

Freetext Search - SQL CONTAINS (column,"R-483*") FAILS

FREETEXT Help please

FREETEXT FILTER ON FREETEXTTABLE USING COLUMN LIST FOR REFINED SEARCH

Friday, February 24, 2012

Freaking column names

Freakin X-files type unexplained bull crap...

Get Next Number

Blog Archive

About Me