Best practise to provide non-DBAs querying capabilities in SQL200

When we used SQLServer 2000, we installed just query analyzer on desktops of
several of our IT folks so they could query data easily. Some of these folks
have admin priv for non-DBA reasons. Now that querying data is integrated
into the SQL 2005 management studio I am not sure the best way to provide
this function to these users. I don't want to give the full management
studio, especially to those with Admin but no appreciation for DBA tasks.
Any thoughts would be appreciated.
Thanks
--
FloraPerhaps QALite work against 2005?
http://rac4sql.net/qalite_main.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Flora Seymour" <FloraSeymour@.discussions.microsoft.com> wrote in message
news:66C0EC4F-ACDB-47DB-9417-5258409A9584@.microsoft.com...
> When we used SQLServer 2000, we installed just query analyzer on desktops of
> several of our IT folks so they could query data easily. Some of these folks
> have admin priv for non-DBA reasons. Now that querying data is integrated
> into the SQL 2005 management studio I am not sure the best way to provide
> this function to these users. I don't want to give the full management
> studio, especially to those with Admin but no appreciation for DBA tasks.
> Any thoughts would be appreciated.
> Thanks
> --
> Flora|||Thanks - I'd rather stay within the MS SQL Server2005 product if possible,
but QALite may do the trick if I that does not work out. The info I found
on it does not mention SQL2005 , just SQL2000, SQL Server7. Any experience
with QALite on SQL2005?
--
Flora
"Flora Seymour" wrote:
> When we used SQLServer 2000, we installed just query analyzer on desktops of
> several of our IT folks so they could query data easily. Some of these folks
> have admin priv for non-DBA reasons. Now that querying data is integrated
> into the SQL 2005 management studio I am not sure the best way to provide
> this function to these users. I don't want to give the full management
> studio, especially to those with Admin but no appreciation for DBA tasks.
> Any thoughts would be appreciated.
> Thanks
> --
> Flora|||I haven't used QALite myself, but since the Query Analyzer (2000 version) work against 2005 (almost
all things), I think there's a good chance that QALite does as well.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Flora Seymour" <FloraSeymour@.discussions.microsoft.com> wrote in message
news:7E6DFEA1-4E1A-407C-938A-07EDB894F066@.microsoft.com...
> Thanks - I'd rather stay within the MS SQL Server2005 product if possible,
> but QALite may do the trick if I that does not work out. The info I found
> on it does not mention SQL2005 , just SQL2000, SQL Server7. Any experience
> with QALite on SQL2005?
> --
> Flora
>
> "Flora Seymour" wrote:
>> When we used SQLServer 2000, we installed just query analyzer on desktops of
>> several of our IT folks so they could query data easily. Some of these folks
>> have admin priv for non-DBA reasons. Now that querying data is integrated
>> into the SQL 2005 management studio I am not sure the best way to provide
>> this function to these users. I don't want to give the full management
>> studio, especially to those with Admin but no appreciation for DBA tasks.
>> Any thoughts would be appreciated.
>> Thanks
>> --
>> Flora

Best practise to provide non-DBAs querying capabilities in SQL200

When we used SQLServer 2000, we installed just query analyzer on desktops of
several of our IT folks so they could query data easily. Some of these folks
have admin priv for non-DBA reasons. Now that querying data is integrated
into the SQL 2005 management studio I am not sure the best way to provide
this function to these users. I don't want to give the full management
studio, especially to those with Admin but no appreciation for DBA tasks.
Any thoughts would be appreciated.
Thanks
--
FloraPerhaps QALite work against 2005?
http://rac4sql.net/qalite_main.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Flora Seymour" <FloraSeymour@.discussions.microsoft.com> wrote in message
news:66C0EC4F-ACDB-47DB-9417-5258409A9584@.microsoft.com...
> When we used SQLServer 2000, we installed just query analyzer on desktops
of
> several of our IT folks so they could query data easily. Some of these fol
ks
> have admin priv for non-DBA reasons. Now that querying data is integrated
> into the SQL 2005 management studio I am not sure the best way to provide
> this function to these users. I don't want to give the full management
> studio, especially to those with Admin but no appreciation for DBA tasks.
> Any thoughts would be appreciated.
> Thanks
> --
> Flora|||Thanks - I'd rather stay within the MS SQL Server2005 product if possible,
but QALite may do the trick if I that does not work out. The info I found
on it does not mention SQL2005 , just SQL2000, SQL Server7. Any experience
with QALite on SQL2005?
Flora
"Flora Seymour" wrote:

> When we used SQLServer 2000, we installed just query analyzer on desktops
of
> several of our IT folks so they could query data easily. Some of these fol
ks
> have admin priv for non-DBA reasons. Now that querying data is integrated
> into the SQL 2005 management studio I am not sure the best way to provide
> this function to these users. I don't want to give the full management
> studio, especially to those with Admin but no appreciation for DBA tasks.
> Any thoughts would be appreciated.
> Thanks
> --
> Flora|||I haven't used QALite myself, but since the Query Analyzer (2000 version) wo
rk against 2005 (almost
all things), I think there's a good chance that QALite does as well.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Flora Seymour" <FloraSeymour@.discussions.microsoft.com> wrote in message
news:7E6DFEA1-4E1A-407C-938A-07EDB894F066@.microsoft.com...[vbcol=seagreen]
> Thanks - I'd rather stay within the MS SQL Server2005 product if possible
,
> but QALite may do the trick if I that does not work out. The info I fou
nd
> on it does not mention SQL2005 , just SQL2000, SQL Server7. Any experience
> with QALite on SQL2005?
> --
> Flora
>
> "Flora Seymour" wrote:
>

best practices to optimize a DB ?

What are the best pactices to optimize performance accsiing an SQLServer DB ?
commands, mantenance plan...
ThanksWow. That's a pretty wide open question. Here are some thoughts:

1a. Make sure you know what your backup and recovery plan is; performance isn't worth too much if a single point of failure or a user error causes an extended period of downtime.
1b. Make sure you TEST your recovery plan. If you don't, it's not a plan (it's something between a hope and a dream).
2. Make sure your performance and monitoring effort is in keeping with the priorities of the business; you can spend a LOT of time and money in this area, but if it isn't the business' priority, then you're wasting it.
3. Read, read, read.

MS SQL Server 2000 DBA Survival Guide (SAMS, 2000)
Microsoft SQL Server 2000 Operations Guide (Microsoft, 2001)
Inside SQL Server 2000 (Microsoft Press, 2000)
Books On Line

Regards,

hmscott

Best practices for Unicode column supporting mixed languages and searching

(I posted this to sqlserver.server newsgroup but did not get a helpful
response)
Globalization gurus,
The problem is storing in a single column character data from mixed
languages and then providing a search capability to find the best match
given a search string in some arbitrary language. The column, of course, is
Unicode data type with some collation. What is recommendation for table
design for efficiency?
As an example, consider an international directory of business names and a
function to search for a name "like" <some string> where <some string> is
user input in any language. Since collation determines comparison rules it
seems the appropriate collation is one that best matches the language of the
search string. Further, to facilitate matching it seems appropriate to relax
restrictions such as case and accent sensitivity. That is a strict binary
comparison is not "user friendly." However there are performance
implications when the collation of the search string does not match the
collation of the database column.
Has anyone solved this problem or know of a good discussion?
Thank you,
SethSeth,
Hmm... This seem a very good fit for SQL Server 2005 Full-Text Search (FTS).
Note, that I'm NOT recommending SQL Server 2000 FTS for this as there have
been specific SQL FTS enhancements for these requirements that are not in
SQL 2000. Specifically, in SQL Server 2005, you can define multiple
languages in one Unicode column and then do CONTAINS or FREETEXT and specify
the language (via LCID) on a per query basis, for example:
select * from table where
contains(*,'formsof(inflectional,"englishword")',language 1033) OR
contains(*,'formsof(inflectional,"chineseword")',language 2052)
The above query will find the englishword or chineseword and inflectional
variations from the same FT-enable column. You can also use this query with
CONTAINSTABLE or FREETEXTTABLE and use the RANK value. As for table design,
that depend upon what you're storing and what combined column you want
returned to your searchers.
You can also control the accent sensitivity via
"CREATE FULLTEXT CATALOG <FT_Catalog_Name> WITH ACCENT_SENSITIVITY = [OFF |
ON]
However, FTS is not case sensitive, but based upon research in to how people
search, case sensitivity is usually viewed as a hindrance as most internet
search engines are case-insensitive as well. Why do you need this level of
control?
Feel free to email me directly if you'd like to continue this discuss in
more details.
Thanks,
John
--
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Seth" <seth@.no*spam.com> wrote in message
news:Orp0SUb2FHA.2472@.TK2MSFTNGP12.phx.gbl...
> (I posted this to sqlserver.server newsgroup but did not get a helpful
> response)
> Globalization gurus,
> The problem is storing in a single column character data from mixed
> languages and then providing a search capability to find the best match
> given a search string in some arbitrary language. The column, of course,
> is
> Unicode data type with some collation. What is recommendation for table
> design for efficiency?
> As an example, consider an international directory of business names and a
> function to search for a name "like" <some string> where <some string> is
> user input in any language. Since collation determines comparison rules it
> seems the appropriate collation is one that best matches the language of
> the
> search string. Further, to facilitate matching it seems appropriate to
> relax
> restrictions such as case and accent sensitivity. That is a strict binary
> comparison is not "user friendly." However there are performance
> implications when the collation of the search string does not match the
> collation of the database column.
> Has anyone solved this problem or know of a good discussion?
> Thank you,
> Seth
>
>|||If anyone has ideas on non-FTS solutions I would appreciate that as well.
John,
Good idea!
I have read about FTS enhancements in SQL Server 2005 but I admit to having
an aversion to FTS and dismissed it as an option prematurely. It is
definitely worth consideration.
Seth
--
"John Kane" wrote in message news:OVefrPe2FHA.1140@.tk2msftngp13.phx.gbl...
Seth,
Hmm... This seem a very good fit for SQL Server 2005 Full-Text Search (FTS).
Note, that I'm NOT recommending SQL Server 2000 FTS for this as there have
been specific SQL FTS enhancements for these requirements that are not in
SQL 2000. Specifically, in SQL Server 2005, you can define multiple
languages in one Unicode column and then do CONTAINS or FREETEXT and specify
the language (via LCID) on a per query basis, for example:
select * from table where
contains(*,'formsof(inflectional,"englishword")',language 1033) OR
contains(*,'formsof(inflectional,"chineseword")',language 2052)
The above query will find the englishword or chineseword and inflectional
variations from the same FT-enable column. You can also use this query with
CONTAINSTABLE or FREETEXTTABLE and use the RANK value. As for table design,
that depend upon what you're storing and what combined column you want
returned to your searchers.
You can also control the accent sensitivity via
"CREATE FULLTEXT CATALOG <FT_Catalog_Name> WITH ACCENT_SENSITIVITY = [OFF |
ON]
However, FTS is not case sensitive, but based upon research in to how people
search, case sensitivity is usually viewed as a hindrance as most internet
search engines are case-insensitive as well. Why do you need this level of
control?
Feel free to email me directly if you'd like to continue this discuss in
more details.
Thanks,
John
--
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Seth" <seth@.no*spam.com> wrote in message
news:Orp0SUb2FHA.2472@.TK2MSFTNGP12.phx.gbl...
> (I posted this to sqlserver.server newsgroup but did not get a helpful
> response)
> Globalization gurus,
> The problem is storing in a single column character data from mixed
> languages and then providing a search capability to find the best match
> given a search string in some arbitrary language. The column, of course,
> is
> Unicode data type with some collation. What is recommendation for table
> design for efficiency?
> As an example, consider an international directory of business names and a
> function to search for a name "like" <some string> where <some string> is
> user input in any language. Since collation determines comparison rules it
> seems the appropriate collation is one that best matches the language of
> the
> search string. Further, to facilitate matching it seems appropriate to
> relax
> restrictions such as case and accent sensitivity. That is a strict binary
> comparison is not "user friendly." However there are performance
> implications when the collation of the search string does not match the
> collation of the database column.
> Has anyone solved this problem or know of a good discussion?
> Thank you,
> Seth
>
>

Best practices - currency elements

Hello! I'm a long-time SQLServer developer, but new to XML.

I find myself doing some XML related to EDI messages.

When you have a field containing a dollar amount, what format should you use in the XSD?

We've been using decimal.

But that's just half the question!

When the XML comes in and there is a round dollar amount, we've been getting the data in integer format, a five dollar order just looks like <mytotal>5</mytotal>.

Wouldn't it seem like a best practice to make this <mytotal>5.00</mytotal>?

Thanks.

Josh

Could this be a problem with the specification of the database column? For example:

Code Snippet

declare @.testo table(myTotal decimal , dec_9_2 decimal(9,2))
insert into @.testo select 5, 3
--select myTotal from @.testo

select myTotal
from @.testo
for xml path('')

/*
XML_F52E2B61-18A1-11d1-B105-00805F49916B
--
<myTotal>5</myTotal>
*/

select dec_9_2
from @.testo
for xml path('')

/*
XML_F52E2B61-18A1-11d1-B105-00805F49916B
--
<dec_9_2>3.00</dec_9_2>
*/

In the first query the source column is simply defined as a DECIMAL column and is displayed without any fractional "decimal" portion. When this column is converted to XML it displays only the whole number portion because really, the data consists of whole number only.

In the second query the source column is defined as DECIMAL (9, 2) column. This provides for 7 whole number digits and 2 decimal digits. When this column is converted to XML it displays the desired decimal places.

Can you provide the DDL for your source column?

|||

The XML is prepared by an outside source, in fact it comes from an EDI message.

I'm wondering whether - more like just how - to raise it with them as an improvement they should make.

Thanks.

Josh

|||

What I would wander is first, are ANY of these fields coming in with decimals. If none, I would definitely raise the issue if your are supposed to be getting 2-decimal accuracy. They may have an error that they are not aware of.

Also, the advantage of getting the decimals is that it eliminates doubt -- which is exactly what you are expressing. It is probably a good idea just to ask the question so that the doubt is eliminated. Much better to talk now than miss something.

sql