Does SQL Server support all languages using Unicode

I have an international Internet business and I use SQL Server with the Unicode field types of nchar, nvarchar and ntext. I seem to have no problem with customers entering Slovakian, Slovenian, Romanian, English or German, but Hebrew and Japanese do not seem to work. The resulting data coming back from the database after adding a record is garbage.

I had the software email me the data immediately before adding the data to SQL Server. It was appearing correctly with respect to the foreign language character format. The software does the database add and then returns a list of records currently in that table. The information for Japanese or Hebrew entries is always a string of question marks, e.g. ?

I have been told that the only solution is individual databases for those languages. Is this true?SQL Server supports all languages using UNICODE. You don't need a separate database for each language.

There may be some issues with collation order if you have strings from different languages in the same column. You have to choose a particular collation for the column so indexing can work correctly. The collation you choose may give some unconventional ordering for sorted data in some languages because the conventional ordering differs from region to region. Sharp s, ?, is ordered the same as s in English, but not in German Phonebook, for example. You can specify a collation based on the UI locale for your result ordering in queries to overcome this.

The "?" problem generally happens when you convert UNICODE data to multi-byte using the incorrect locale. If your application must use multi-byte characters for some reason, make sure the locale matches the data's language. If your middle-tier and UI can use UNICODE data directly, its best to avoid the wide character to multi-byte and multi-byte to wide character conversions so this problem doesn't occur.|||You don't understand the problem

1.) The Internet is global in nature.

2.)I have 1 database

3.) data is entered from the contents of a form in a program

4.) customers can come from anwhere in the world. They speak many languages. They fill out the form in their native language (Hebrew, Japanese, etc.) The form contents write to the database.

5.) The database field types are nchar, nvarchar and ntext.

6.) the table has 10 fields. There are 15 tables. If these fields have to be replicated for each unique language, and we assume about 10,000 different languages in the world, the database will not fit on any known hard drive. It will require hundreds of programming hours to determine what language the input data is in and what set of table elements they should go to or be retrieved from (once we figure out what language the viewer uses).

7.) If all of the above is true, any new languages can not be used without prior setup.

8.) Why does MySQL handle this and not SQL Server?

We're not looking to translate anything. What we want is something eminently simple. Whatever they write, in whatever language, goes into the database. When that record is retrieved, whatever is in the record, in any language, is returned as data, unaltered.

There's no sorting and no reporting. The database holds the data and it's delivered when asked for.

Surely there is a solution here that doesn't involve something almost as complex as cloning humans.|||

You don't need to replicate your data for each language. You just need to make sure your application stack is using UNICODE strings from end-to-end, including the web forms that are displaying the data. You've already got the SQL Server end covered if you are using nothing but nchar, nvarchar, and ntext.

I saw the ? problem many, many times back when I was working on international programs that used ANSI strings. The system function that does the UNICODE-to-multi-byte translation uses question-marks for the characters it can't translate into the specified multi-byte locale. In my applications, the problem was never in the server, it was always in my application. Invariably, my application was trying to convert UNICODE data from the server into multi-byte (ANSI) characters in my application, but the locale I had specified for the translation was incorrect for the data, so there was no mapping from the UNICODE data to the ANSI locale. (For example, there is no representation for Japanese characters in the ANSI Hebrew code page.) I stopped seeing this problem once the applications I was working on used nothing but UNICODE in the database, database access layer, middle-tier, and the UI.

The next things I would check are whether you've specified UNICODE strings as the data types in your data access layer (ADO automatically converts between UNICODE and ANSI strings if you tell it you want ANSI strings for example) and whether the UI is using UNICODE or ANSI display controls. Those have been the areas where I've had the most trouble with globalized applications in the past.
I hope this helps,
Steve

does grouping on a field imply sorting by that field?

If I group on a field, will the report be sorted by that field as well?
I have a table whose parent is the report body. I'm grouping the
table on a string field that contains values like "SC2", "SC4", "SC5",
and "SC6". These values are all 3 characters long (i.e., no white
space) and all uppercase. When I preview the report, the groups appear
in this order: "SC2", "SC6", "SC5", and "SC4" - which is the order
these values appear in the grid on the Data tab - so it appears that no
sort is being applied to the dataset (which, BTW, is based on a stored
procedure). I have tried sorting by this field (in addition to
grouping by it) but that does not change the order displayed in the
report. Any help would be appreciated.Where are you applying the sort? You need to sort the groups, not the
details (or dataset). In the Group Properties dialog, select he sort tab and
you should get what you want.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Lee" <leemsrs@.hotmail.com> wrote in message
news:1114273595.800769.209160@.l41g2000cwc.googlegroups.com...
> If I group on a field, will the report be sorted by that field as well?
> I have a table whose parent is the report body. I'm grouping the
> table on a string field that contains values like "SC2", "SC4", "SC5",
> and "SC6". These values are all 3 characters long (i.e., no white
> space) and all uppercase. When I preview the report, the groups appear
> in this order: "SC2", "SC6", "SC5", and "SC4" - which is the order
> these values appear in the grid on the Data tab - so it appears that no
> sort is being applied to the dataset (which, BTW, is based on a stored
> procedure). I have tried sorting by this field (in addition to
> grouping by it) but that does not change the order displayed in the
> report. Any help would be appreciated.
>|||Knowing where to apply the sort is the confusing part for me. There is
a Sorting tab on the Table Properties dialog (my report consists of a
single table whose parent is the report body). There is also a Sorting
tab on the Grouping and Sorting Properties dialog (which can be
accessed for each group). What are these two different Sorting tabs
used for? Tell me if I got it right in the following example: the
report my users want should be grouped by Field A, and then within each
Field A group it should be grouped by Field B. Finally, within each
Field B group it should be sorted (not grouped) by Field C. On the
Grouping and Sorting Properties dialog for the Field A group, I went to
the Sorting tab and chose Fields!FieldA.value. Next, on the Grouping
and Sorting Properties dialog for the Field B group, I went to the
Sorting tab and chose Fields!FieldB.value. Finally, on the Table
Properties dialog, I went to the Sorting tab and chose
Fields!FieldC.value as the only sort field. This seems to produce the
grouping and sorting that my users want, but because I find the process
counter-intuitive (my 4 years of previous reporting experience have
been with Cognos Impromptu), I'm wondering if what I did is correct.
Can you tell me?|||You are correct. The sorting on the grouping dialog is used to sort the
groups themselves. For example, you might group by city but then sort the
cities by the population. The table sort is used to sort the details. Since
the sort key might be composite, we have to support multiple fields in the
dialog.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Lee" <leemsrs@.hotmail.com> wrote in message
news:1114441358.314151.237870@.f14g2000cwb.googlegroups.com...
> Knowing where to apply the sort is the confusing part for me. There is
> a Sorting tab on the Table Properties dialog (my report consists of a
> single table whose parent is the report body). There is also a Sorting
> tab on the Grouping and Sorting Properties dialog (which can be
> accessed for each group). What are these two different Sorting tabs
> used for? Tell me if I got it right in the following example: the
> report my users want should be grouped by Field A, and then within each
> Field A group it should be grouped by Field B. Finally, within each
> Field B group it should be sorted (not grouped) by Field C. On the
> Grouping and Sorting Properties dialog for the Field A group, I went to
> the Sorting tab and chose Fields!FieldA.value. Next, on the Grouping
> and Sorting Properties dialog for the Field B group, I went to the
> Sorting tab and chose Fields!FieldB.value. Finally, on the Table
> Properties dialog, I went to the Sorting tab and chose
> Fields!FieldC.value as the only sort field. This seems to produce the
> grouping and sorting that my users want, but because I find the process
> counter-intuitive (my 4 years of previous reporting experience have
> been with Cognos Impromptu), I'm wondering if what I did is correct.
> Can you tell me?
>|||In your example, if we grouped by city but didn't sort by it, what
would happen? I would hope that whatever city was encountered first in
the dataset would appear first in the report, and all rows for that
city (no matter where they were scattered throughout the dataset) would
appear together on the report (i.e., no other cities in between them).
For example, consider a dataset with 10 rows. If there were 5 rows for
Boston at the beginning of the dataset, followed by 3 rows for Atlanta,
followed by another 2 rows for Boston, then the report would show the
Boston group first with all 7 rows followed by the Atlanta group with 3
rows. Is this correct?

Does full text indexing works on unicode field?

Hi all,
Does full text indexing supports searching on a unicode field, for
example, a field that stores chinese characters?
Thank you in advance.Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"alvinyyt" <alvinyyt@.gmail.com> wrote in message
news:1144720305.221875.221920@.e56g2000cwe.googlegroups.com...
> Hi all,
> Does full text indexing supports searching on a unicode field, for
> example, a field that stores chinese characters?
> Thank you in advance.
>|||Yes, make sure you select the appropriate language word breaker.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"alvinyyt" <alvinyyt@.gmail.com> wrote in message
news:1144720305.221875.221920@.e56g2000cwe.googlegroups.com...
> Hi all,
> Does full text indexing supports searching on a unicode field, for
> example, a field that stores chinese characters?
> Thank you in advance.
>

Does field exist in backup?

I run scripts against my db,

1) Rename table
2) Create new table (original name)
3) Create Indexes
4) Insert into new, Select from backup

My problem is that one table may have more fields than on another db, but I want to run the same script to update the tables.

What I'd like it to do, is in the insert select stuff, I want to put logic to select field from backup if it exists and insert in new.

If it doesnt exist in backup then insert null into new table, instead of having the line blow up cause the field doesnt exist in backup.

Suggestions would be appreciated. Thanks!, MitchIf it's not in the list it will automatically put in nulls...as long as it is nullable...

and you could go crazy...but it might just easier to code the dang thing...

USE Northwind
GO
sp_help Orders
GO

-- The lazy man's way to create a table

SELECT * INTO NewOrders FROM Orders WHERE 1=0
GO

ALTER TABLE NewOrders DROP Column RequiredDate
GO

DECLARE @.x varchar(8000)

SELECT @.x = 'INSERT INTO NewOrders ('

SELECT @.x = @.x + COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'NewOrders' AND ORDINAL_POSITION = 1

SELECT @.x = @.x + ', '+COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'NewOrders' AND ORDINAL_POSITION > 1
ORDER BY ORDINAL_POSITION

SELECT @.x = @.x + ') SELECT '

SELECT @.x = @.x + COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'NewOrders' AND ORDINAL_POSITION = 1

SELECT @.x = @.x + ', '+COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'NewOrders' AND ORDINAL_POSITION > 1
ORDER BY ORDINAL_POSITION

SELECT @.x = @.x + ' FROM Orders'

SELECT @.x

SET IDENTITY_INSERT NewOrders ON

EXEC(@.x)

SET IDENTITY_INSERT NewOrders OFF
GO

SELECT * FROM NewOrders
GO

DROP TABLE NewOrders
GO|||If you could send me a link or something, that would help me understand the logic below that would be awesome. I sort of follow the code below, but I'd like to see step by step what does what.
Thanks for your reply.
Mitch

Originally posted by Brett Kaiser
If it's not in the list it will automatically put in nulls...as long as it is nullable...

and you could go crazy...but it might just easier to code the dang thing...

USE Northwind
GO
sp_help Orders
GO

-- The lazy man's way to create a table

SELECT * INTO NewOrders FROM Orders WHERE 1=0
GO

ALTER TABLE NewOrders DROP Column RequiredDate
GO

DECLARE @.x varchar(8000)

SELECT @.x = 'INSERT INTO NewOrders ('

SELECT @.x = @.x + COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'NewOrders' AND ORDINAL_POSITION = 1

SELECT @.x = @.x + ', '+COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'NewOrders' AND ORDINAL_POSITION > 1
ORDER BY ORDINAL_POSITION

SELECT @.x = @.x + ') SELECT '

SELECT @.x = @.x + COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'NewOrders' AND ORDINAL_POSITION = 1

SELECT @.x = @.x + ', '+COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'NewOrders' AND ORDINAL_POSITION > 1
ORDER BY ORDINAL_POSITION

SELECT @.x = @.x + ' FROM Orders'

SELECT @.x

SET IDENTITY_INSERT NewOrders ON

EXEC(@.x)

SET IDENTITY_INSERT NewOrders OFF
GO

SELECT * FROM NewOrders
GO

DROP TABLE NewOrders
GO|||Mitch,

Just cut and paste the code into a query analyzer window...

Just execute...I already tested it and it runs like a champ...

Does DB Size decrease when I delete a huge table ?

Hi,
My DB size (Right click on DB Name, Data Files tab, Space Allocated field) was 10914 MB.
I delete a huge table (1.2 million records * 15 columns).
I checked the db size again. It didnt change.
Shouldn't it decrease because I delete a huge table ??The size will only decrease if you use the shrinkdb command.|||Originally posted by rokslide
The size will only decrease if you use the shrinkdb command.

When and why should we use Shrink DB ??|||Originally posted by rokslide
The size will only decrease if you use the shrinkdb command.

Would the deleted data be still on the database physical file ?|||The use of shrinkdb depends on who you talk to,... I really don't have a theory about it except to say that if the database is taking up too much space, shrink it...

As for the data still being in the database file... I would guess (and it's just a guess) that it would still be there but it would be unaccessible via the database. I think the delete would work much like deleting a file off your hard drive, all that is really deleted is the file header information, the data is still there, but you can't access it unless you use a tool to specificly locate the data.

Does that make sense? Can anyone else offer more advise??|||Originally posted by rokslide
The use of shrinkdb depends on who you talk to,... I really don't have a theory about it except to say that if the database is taking up too much space, shrink it...

As for the data still being in the database file... I would guess (and it's just a guess) that it would still be there but it would be unaccessible via the database. I think the delete would work much like deleting a file off your hard drive, all that is really deleted is the file header information, the data is still there, but you can't access it unless you use a tool to specificly locate the data.

Does that make sense? Can anyone else offer more advise??

Can u give a quick example of how I use ShrinkDB ?|||Originally posted by forXLDB
Can u give a quick example of how I use ShrinkDB ?
How do I know what size to reduce to ?
(If the size doesnt decrease even after deleting tables, how do i know what must be the exact size occupied by all the tables)...
I would have deleted and created lots of huge files involving around a million records atleast 10 times..

and above all, does the size effect the sql server performance in any way ?|||straight from the books online

This example decreases the size of the files in the UserDB user database to allow 10 percent free space in the files of UserDB.

DBCC SHRINKDATABASE (UserDB, 10)
GO

or you can do it through the EM|||Originally posted by rokslide
straight from the books online

or you can do it through the EM

i just did use for another test database, it shrunk more than half the size...

thx anyway !!1

Still looking for the answer on any performance degradation if the db file size is more ?|||Guessing once again I would say that it shouldn't affect the speed to any great extent,.. fragmentation of the database file would... there are some commands/functions that you can use to find out this kinda stuff but I have never used them so I can't really help alot I'm sorry.|||Originally posted by rokslide
Guessing once again I would say that it shouldn't affect the speed to any great extent,.. fragmentation of the database file would... there are some commands/functions that you can use to find out this kinda stuff but I have never used them so I can't really help alot I'm sorry.
thx again...

lemme know any functions !!!|||Since the log file takes the big size of the database, you can use the following script to reduce the file size:

USE DatabaseName
GO
DBCC SHRINKFILE ('DatabaseName_Log', Size)
GO

Here "Size" is the number of MB, i.e., if you put 100, that means the file will be shrinked to 100 MB.|||Since you are already working in em - when you right click on the database go to all task ->shrink database ->files... Under database file, look at the data file and log file. What is the current size/space used ? Are you backing up the database/transaction log ? What is the recovery model ?

What is the performance issue ?|||You can use "dbcc showcontig" to display fragmentation info. Yes performance will be affected - you need to either drop/recreate the indexes, use dbcc dbreindex or dbcc indexdefrag. Since you deleted so many records (more than half), fragmentation is a good start for performance issues.|||Do you have access to Books Online (BOL) ?|||An addition to rnealejr's post on dbcc indexdefrag and recreating indexes. If you have tables with a large amount of data input or modification take a good look at the indexes. If you use a non-seed value for your index then increasing the fillfactor number can help to reduce fragmentation in the future. I.E if your customer table uses first four characters of the name plus the zipcode for a customerID then you would be inserting new records into the middle of the table frequently. Once the fillfactor is used up then the next record inserted will create a new page and now your data is getting fragmented which does impact performance as mentioned be other posters.

Now that you've opened this pandoras box, you could spend a week in BOL learning how to tweak each tables index based on usage, calculating how many records fit on a page, use of varchar over char, benifits/penalties of seed indexes...............|||Originally posted by rnealejr
Do you have access to Books Online (BOL) ?
Yes. I can read BOL

Does all of WHERE clause get executed?

Hi there.
I have an update statement that wishes to update certain fields depending on
a column that may or may not contain numeric values (the field is varchar bu
t
may contain '001', '002' etc. For the WHERE clause, I need to convert the
MaybeNumeric field into an integer BUT... I know the whole update statement
will fail if the value is non-numeric (due to the convert(integer,...)
criteria).
The solution I've chosen is to have IsNumeric(MaybeNumeric)=1 as the first
criteria in the WHERE clause - thinking that this will get executed first an
d
will exclude non-numeric rows.
This seems to work ok on my server (those famous words!) but I've seen cases
on another server where the update statement still fails due to non-numeric
values.
Q: Is there some server setting that would dictate whether all criteria in a
WHERE clause will get executed? Or in which order they get executed? I.e: Is
the following code reliable:
update
MyTable
set
MyTable.SomeField = OtherTable.SomeField
from
OtherTable
where
IsNumeric(MyTable.MaybeNumeric) = 1
and
OtherTable.DefinitelyNumeric = convert(integer, MyTable.MaybeNumeric)
*note: this is a simplified version of the statement - I do need the convert
!
I just want to know whether this is down to my code or is I should look for
some other issue.
Any help would be appreciated!IsNumeric will return 1 for some strings that can't be converted to an
integer, but can be converted to other numeric datatypes, like '1E10', which
can be converted to a float, or '200,000.00', which can be converted to
money.
http://www.aspfaq.com/show.asp?id=2390 gives you a workaround.
Jacco Schalkwijk
SQL Server MVP
"len" <len@.discussions.microsoft.com> wrote in message
news:01E35557-6ED2-4824-A721-F4F00A9B820F@.microsoft.com...
> Hi there.
> I have an update statement that wishes to update certain fields depending
> on
> a column that may or may not contain numeric values (the field is varchar
> but
> may contain '001', '002' etc. For the WHERE clause, I need to convert the
> MaybeNumeric field into an integer BUT... I know the whole update
> statement
> will fail if the value is non-numeric (due to the convert(integer,...)
> criteria).
> The solution I've chosen is to have IsNumeric(MaybeNumeric)=1 as the first
> criteria in the WHERE clause - thinking that this will get executed first
> and
> will exclude non-numeric rows.
> This seems to work ok on my server (those famous words!) but I've seen
> cases
> on another server where the update statement still fails due to
> non-numeric
> values.
> Q: Is there some server setting that would dictate whether all criteria in
> a
> WHERE clause will get executed? Or in which order they get executed? I.e:
> Is
> the following code reliable:
>
> update
> MyTable
> set
> MyTable.SomeField = OtherTable.SomeField
> from
> OtherTable
> where
> IsNumeric(MyTable.MaybeNumeric) = 1
> and
> OtherTable.DefinitelyNumeric = convert(integer, MyTable.MaybeNumeric)
> *note: this is a simplified version of the statement - I do need the
> convert!
> I just want to know whether this is down to my code or is I should look
> for
> some other issue.
> Any help would be appreciated!|||Hi
The query processor can choose to execute a query in any way it pleases.
Depending on statistics, indexes, processors and RAM, it might decide to
execute a different query plan.
Have you tried:
update
MyTable
set
MyTable.SomeField = OtherTable.SomeField
FROM
MyTable
INNER JOIN
OtherTable
ON
OtherTable.DefinitelyNumeric = convert(integer, MyTable.MaybeNumeric)
WHERE
IsNumeric(MyTable.MaybeNumeric) = 1
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"len" wrote:

> Hi there.
> I have an update statement that wishes to update certain fields depending
on
> a column that may or may not contain numeric values (the field is varchar
but
> may contain '001', '002' etc. For the WHERE clause, I need to convert the
> MaybeNumeric field into an integer BUT... I know the whole update statemen
t
> will fail if the value is non-numeric (due to the convert(integer,...)
> criteria).
> The solution I've chosen is to have IsNumeric(MaybeNumeric)=1 as the first
> criteria in the WHERE clause - thinking that this will get executed first
and
> will exclude non-numeric rows.
> This seems to work ok on my server (those famous words!) but I've seen cas
es
> on another server where the update statement still fails due to non-numeri
c
> values.
> Q: Is there some server setting that would dictate whether all criteria in
a
> WHERE clause will get executed? Or in which order they get executed? I.e:
Is
> the following code reliable:
>
> update
> MyTable
> set
> MyTable.SomeField = OtherTable.SomeField
> from
> OtherTable
> where
> IsNumeric(MyTable.MaybeNumeric) = 1
> and
> OtherTable.DefinitelyNumeric = convert(integer, MyTable.MaybeNumeric)
> *note: this is a simplified version of the statement - I do need the conve
rt!
> I just want to know whether this is down to my code or is I should look fo
r
> some other issue.
> Any help would be appreciated!|||Optimiser can choose whatever evaluation path it sees fit. Look for "short
circuit" in the page below and see if it helps:
http://msdn.microsoft.com/library/d...
heckitout.asp
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"len" <len@.discussions.microsoft.com> wrote in message
news:01E35557-6ED2-4824-A721-F4F00A9B820F@.microsoft.com...
Hi there.
I have an update statement that wishes to update certain fields depending on
a column that may or may not contain numeric values (the field is varchar
but
may contain '001', '002' etc. For the WHERE clause, I need to convert the
MaybeNumeric field into an integer BUT... I know the whole update statement
will fail if the value is non-numeric (due to the convert(integer,...)
criteria).
The solution I've chosen is to have IsNumeric(MaybeNumeric)=1 as the first
criteria in the WHERE clause - thinking that this will get executed first
and
will exclude non-numeric rows.
This seems to work ok on my server (those famous words!) but I've seen cases
on another server where the update statement still fails due to non-numeric
values.
Q: Is there some server setting that would dictate whether all criteria in a
WHERE clause will get executed? Or in which order they get executed? I.e: Is
the following code reliable:
update
MyTable
set
MyTable.SomeField = OtherTable.SomeField
from
OtherTable
where
IsNumeric(MyTable.MaybeNumeric) = 1
and
OtherTable.DefinitelyNumeric = convert(integer, MyTable.MaybeNumeric)
*note: this is a simplified version of the statement - I do need the
convert!
I just want to know whether this is down to my code or is I should look for
some other issue.
Any help would be appreciated!|||A more detailed analysis of the "short circuit" feature in SQL Server
is written in the following article (also by Itzik Ben-Gan):
http://www.windowsitpro.com/Article...?ArticleID=9148
Razvan|||>> I have an update statement that wishes to update certain fields [sic] depending on
a column that may or may not contain numeric values (the field [sic] is varchar b
ut may contain '001', '002' etc. <<
Exactly how did you get this kind of problem in the first place?
You have a huge design problem and need to change your schema, not go
hunting for kludges. One of the reasons that I beat on people about
not calling a "column" a "field" is that a field (a file processing
concept) can hold anything; it gets its meaning from being read by a
host program. A relational column has ONE AND ONLY ONE domain which
has ONE AND ONLY ONE data type. It has meaning in and of itself, it
enforces its own integrity, it does not depend on a host program.
If this data element is used for computations, then it needs to be a
numeric. If it is a tag number, then you can use character types. You
do not mix things like this in an RDBMS.
Also, you might want to stop using the unpredictable proprietary UPDATE
syntax.|||Len's requirement is common enough in an ELT data staging scenario. For
conforming external data I would typically load to an "untyped" table
(NVARCHAR throughout) first, perform the necessary validation, then
load the valid data to another staging table with the correct datatyes.
Any UPDATE against the actual data in the target database utilizes the
correctly typed table. This avoids type conversions and errors and
ensures you maximize the benefit of indexing on the two tables.
David Portas
SQL Server MVP
--|||On Wed, 25 May 2005 04:44:03 -0700, len wrote:
(snip)
>The solution I've chosen is to have IsNumeric(MaybeNumeric)=1 as the first
>criteria in the WHERE clause - thinking that this will get executed first a
nd
>will exclude non-numeric rows.
Hi len,
As others have said: there's no guarantee.
You might wish to try this one:
UPDATE MyTable
SET MyTable.SomeColumn = OtherTable.SomeColumn
FROM OtherTable
WHERE OtherTable.DefinitelyNumeric =
CASE WHEN IsNumeric(MyTable.MaybeNumeric) = 1
THEN CONVERT(integer, MyTable.MaybeNumeric)
ELSE OtherTable.DefinitelyNumeric + 1
END
But do beware the gotchas with IsNumeric (see Jacco's post).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Does a large amount 'image' data affect overall SQL performance?

Recently we added a new table into our SQL2000 database specifically to store scanned in images of documents. This new table contains a PK field, a couple of datetime fields, a couple of char(1) fields and one 'image' field.

Before adding this table, the database size was approx 6GB. Six months after adding this new table, the database has grown to 18GB - 11GB of this is due to the scanned in images.

Would this new table affect the SQL performance with regards to accessing other data in the database that has nothing related to the new table?

If so, would moving this new table into it's own database be recommended?

Thanks

Rod

In order to determine the 'best' response to your question, you may find this article useful.

Images –Store in Database or Store in File System
http://research.microsoft.com/research/pubs/view.aspx?msr_tr_id=MSR-TR-2006-45

Does "text type" belong to SQL2 standard ?

Hi,

I am developping an application that should work with different RDBMS.

I need to store a field of 500 characters.

Hence, I thought of using the type "text" that works with MySQL, but I am wondering if it is part of SQL standard or if it is supported by the main RBDMS (Orcale, DB2, SQL Server ...).

Thanks in advance,

SylvainThe "Text" datatype is not part of the ANSI SQL99 standard. Most vendors also have their own variants of datatypes. The nearest agreements in datatypes for your purposes I guess would be to use a VARCHAR. However there is still variation, in Oracle this would be a VARCHAR2.

Hope this help you on your way.|||Hi,

Originally posted by gannet
The "Text" datatype is not part of the ANSI SQL99 standard. Most vendors also have their own variants of datatypes. The nearest agreements in datatypes for your purposes I guess would be to use a VARCHAR. However there is still variation, in Oracle this would be a VARCHAR2.


But with MySQL for example VARCHAR is limited to 255. We can't do VARCHAR(500).

So you see any solution ?

Regards
Sylvain|||Unfortunately there will be no vendor independent way araound this, you will need to generate some vendor specific translations. Not what you wanted to hear I know.

Documenting tables/fields - utility?

As part of a project documentation I want to list out the fields of all the tables to say Excel to plug in a description of the field and other notes.

Is there a tool in EM that would facilitate? Or, is there a utility that would work or a SQL script? Any recommendations is greatly appreciated.

Thanks,

PeterI'll never understand what motivates people to want to take DATA from a DATABASE and store it in a SPREADSHEET.
Tables and columns are already enumerated within the database, and a description of each column can be entered through Enterprise Manager.
You can query the system tables to list all the tables with their columns and descriptions and (if you absolutely insist...) export the results to an Excel file.|||Erwin is good for this. Which reminds me I am about a month and a half behind on that.|||Hey - for a blind guy, you sure help me see the light.

Thanks blindman.|||I keep a bottle of hand-sanitizer with me for occasions when a client requires me to open up Excel, but otherwise all the data I deal with stays in databases.|||LOL - I like that line about having to use hand sanitizer...I will be using that one myself.

I did find a quick query syntax at:

http://searchvb.techtarget.com/tip/1,289483,sid8_gci876303,00.html?bucket=ETA

SELECT
table_name=sysobjects.name,
column_name=syscolumns.name,
datatype=systypes.name,
length=syscolumns.length
FROM sysobjects inner JOIN syscolumns ON sysobjects.id = syscolumns.id
inner join systypes on syscolumns.xtype=systypes.xusertype
where sysobjects.xtype='U'
order by sysobjects.name,syscolumns.colid

I altered the above to bring in the description that a user can enter against each field in the tables:

SELECT
table_name=sysobjects.name,
column_name=syscolumns.name,
datatype=systypes.name,
length=syscolumns.length,
descr=sysproperties.value
FROM sysobjects inner JOIN syscolumns ON sysobjects.id = syscolumns.id
inner join systypes on syscolumns.xtype=systypes.xusertype
left join sysproperties on sysproperties.id=syscolumns.id and syscolumns.colid=sysproperties.smallid
where sysobjects.xtype='U'
order by sysobjects.name,syscolumns.colid

This is fine for my immediate needs.|||If you liked that one, you'll love this one ... and it's guarenteed not to become obsolete with sql server upgrades ...


select * from information_schema.columns|||Tom -

Thanks - another good one for quick data generation.

Documenting MSDE

Can anyone tell me what methods are there for documenting the tables created
in MSDE.
I would like something that would give me the field names, data type, size
etc and also be able to list the indices.
Any suggestions?
June
FMS (http://www.fmsinc.com) makes a reporting tool called Total SQL
Analyzer. They're a reputable company and have been around for a
while. There may be other third-party solutions as well if you don't
want to write your own.
--Mary
On Wed, 26 May 2004 13:12:02 +0100, "June Macleod"
<junework@.hotmail.com> wrote:

>Can anyone tell me what methods are there for documenting the tables created
>in MSDE.
>I would like something that would give me the field names, data type, size
>etc and also be able to list the indices.
>Any suggestions?
>June
>
|||hi June,
"June Macleod" <junework@.hotmail.com> ha scritto nel messaggio
news:%23c4RHpxQEHA.3744@.TK2MSFTNGP10.phx.gbl...
> Can anyone tell me what methods are there for documenting the tables
created
> in MSDE.
> I would like something that would give me the field names, data type, size
> etc and also be able to list the indices.
you can have a look at a free prj of mine, at the link following my sign.,
which provide a way to produce an HTML documentation with cross references
for this kind of thing...
or commercial tools like http://www.ag-software.com/ags_scribe_index.aspx
hth
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.7.0 - DbaMgr ver 0.53.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Thanks Andrea
Obviously I am going blind in my old age <g>
This is a cracker of a tool! The documentation is exactly what I am looking
for and will save me hours of time.
Much appreciated.
June
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:2i1kskFi58j8U1@.uni-berlin.de...[vbcol=seagreen]
> hi June,
> "June Macleod" <junework@.hotmail.com> ha scritto nel messaggio
> news:OXGnIywREHA.3056@.TK2MSFTNGP11.phx.gbl...
documentaion[vbcol=seagreen]
was
> select the databese you want to document in the left pane treeview...
> right-click... select the "Database documentation" menu item...
> hope will meet your requirements..
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.7.0 - DbaMgr ver 0.53.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>

Document Storage inside DB?

Has anyone got any experience or opinions about the
pros/cons of storing Office Type Documents inside an
image field in the database as opposed to storing a URL
pointer to the file in the filesystem?
Assuming there will be several GB of documents Im
thinking it would be better to keep these out of the
database.
AdamA common approach is to :-
Store the documents on a file server
Store a pointer to the Documents in the DB (to aid searching)
HTH
Ryan Waight, MCDBA, MCSE
"Adam" <atoth@.msdnspam.com> wrote in message
news:07ab01c3b4d3$2e07d430$a501280a@.phx.gbl...
> Has anyone got any experience or opinions about the
> pros/cons of storing Office Type Documents inside an
> image field in the database as opposed to storing a URL
> pointer to the file in the filesystem?
> Assuming there will be several GB of documents Im
> thinking it would be better to keep these out of the
> database.
> Adam
>|||Hi,
We are providing content management software that's using such an approach -
but we are using both DB and FileSystem as a storage for the documents. For
storing the documents inside the database, we use a "document-breaker" to
split the documents in meaningfull parts (e.g. the html documents are split
in tags and text).
The advantages we have with the documents inside the database:
- one single resource that contains the whole system
- easy backup
- depending on the document splitting, we can track and validate cross
document references
The advantages we have by also storing the documents in the file system:
- fast document access (knowing the ID, we don't need a query to the DB)
- fast search capability (using Windows Index Services)
By the way: we have productive systems using this approach with 2000+
documents.
Sven
"Adam" <atoth@.msdnspam.com> wrote in message
news:07ab01c3b4d3$2e07d430$a501280a@.phx.gbl...
> Has anyone got any experience or opinions about the
> pros/cons of storing Office Type Documents inside an
> image field in the database as opposed to storing a URL
> pointer to the file in the filesystem?
> Assuming there will be several GB of documents Im
> thinking it would be better to keep these out of the
> database.
> Adam
>|||I see two approaches that use a "file system" is this the
Windows "file system" that you are referring to and if
so... how do you insure the documents are not deleted?
>--Original Message--
>Has anyone got any experience or opinions about the
>pros/cons of storing Office Type Documents inside an
>image field in the database as opposed to storing a URL
>pointer to the file in the filesystem?
>Assuming there will be several GB of documents Im
>thinking it would be better to keep these out of the
>database.
>Adam
>.
>|||>--Original Message--
>I see two approaches that use a "file system" is this
the
>Windows "file system" that you are referring to and if
>so... how do you insure the documents are not deleted?
>
Yes - I do mean the Windows File System; in answer to
your question you could disallow Delete rights (assuming
NTFS) - Ive never tried it but the option is there.
Adam|||Thanks for the reply - do you think it would still work
well with say 100,000 documents?
Adam

Document properties

Can somebody point me to where I can learn how to query the properties
of files stored in an image field. I'm having troubles finding that
documentation. I'm using SQL 2005.
Larry Brown wrote:
> Hi,
> In Office documents for example, you have document properties like
> Doc_Author, or other properties that may have been created by the user who
> wrote the doc.
> In SQL Server 2005, it is possible to search into these properties
> (equivalent of Index Server ALL property). But is there any way to get the
> list of properties with their values ? Meaning to know that the value has
> been found in one property rather than in another ?
> Is there any difference if I use Sharepoint, Services or Portal ?
> My problem is that I'm using not only Office iFilter with document
> properties, but two other iFilters that I created for indexing PDF and
> another format specific to my company, with Index Server. Both these
> iFilters manage document properties, including possibility to create custom
> properties...
> Looks like I will have to do some programming...
> Many thanks in advance
Hello,
Its not possible.
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
[vbcol=seagreen]
> Can somebody point me to where I can learn how to query the properties
> of files stored in an image field. I'm having troubles finding that
> documentation. I'm using SQL 2005.
> Larry Brown wrote:
|||So Larry was mistaken?
"In SQL Server 2005, it is possible to search into these properties
(equivalent of Index Server ALL property)."
|||You can query them if they are emitted by the iFilter. The best way to
determine if they are emitted is to run them through filtdump accessible
from the platform sdk.
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
<ddaiker@.gmail.com> wrote in message
news:1166470714.092854.303040@.79g2000cws.googlegro ups.com...
> Can somebody point me to where I can learn how to query the properties
> of files stored in an image field. I'm having troubles finding that
> documentation. I'm using SQL 2005.
> Larry Brown wrote:
>
|||It is possible to search on properties which are embedded in the document
and accessible through the iFilter-for example the summary properties in
office documents. For some documents, i.e. some pdf properties they are
actually stored in the file system and when you push them into the database
they remain in the file system and don't travel with the document itself.
The best way to see this is to put a value for the keyword metatag of an
html,. store it in an image datatype column and then search on the keyword
value. You will get a hit.
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
"ddaiker" <ddaiker@.gmail.com> wrote in message
news:1166473176.174934.179730@.f1g2000cwa.googlegro ups.com...
> So Larry was mistaken?
> "In SQL Server 2005, it is possible to search into these properties
> (equivalent of Index Server ALL property)."
>
|||Hello Hilary,
But you can't do search for a specific value in a specific property, like
you can with index server
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
[vbcol=seagreen]
> You can query them if they are emitted by the iFilter. The best way to
> determine if they are emitted is to run them through filtdump
> accessible from the platform sdk.
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> <ddaiker@.gmail.com> wrote in message
> news:1166470714.092854.303040@.79g2000cws.googlegro ups.com...

Document Map format

Is is possible to format the text of a field used in Document Map. I'm using
a date field and can format the field on the report, but not in the Document
Map. I couldn't find any expression function that can format a data value.Try the Format function: =Format(Fields!MyDate.Value,"d")
--
My employer's lawyers require me to say:
"This posting is provided 'AS IS' with no warranties, and confers no
rights."
"Elmer Miller" <millere@.empireco.nospam> wrote in message
news:ewP5tzcZEHA.3228@.TK2MSFTNGP12.phx.gbl...
> Is is possible to format the text of a field used in Document Map. I'm
using
> a date field and can format the field on the report, but not in the
Document
> Map. I couldn't find any expression function that can format a data value.
>