Dictionarry order question

I have an installed sql server 2000 language English with server collation
set to SQL_Latin1_General_CP1_CI_AS on a W2k server box. There are several
applications running in production using that machine and those settings.
Now the company is getting a new program that has as the requirement for its
sql server dictionary order to be set to Custom, Case insensitive, Accent
insensitive, for use with 1252 character set.
This server has lots of capacity left and there is no way to justify
spending dollars on another processor license for this app when the server
we already have should be able to do the job.
Is it possible to change the dictionary order on the current server and its
databases and test my current apps? If they still work as before I can then
leave it like that, if not I would have to be able to come back to current
settings.
What is the expert opinion on the difference in settings between the new app
and the current settings as shown above, do you think we should try keeping
our current settings for the new app? The app coming in is using asp pages
and the users are all French, the data in the files will be French with
accented characters and also some English in some fields.
Any help would be greatly appreciated,
RD
If it was me, I'd set up a new instance of SQL Server (configured with the
collation sequence of your choice) and let 'er rip. But with SQL Server
2000, it's possible to have different collation sequences on the instance,
database, table, or even column (IIRC). So there's no reason to change your
existing apps and databases. As far as I know, changing collations is a
real pain in the butt, and involves DTS'ing or BCP'ing your data to a new
home, or out to a temporary one, and in to the re-built one.
Clint
"RD" <bdufour@.sgiims.com> wrote in message
news:#OXncM9hFHA.4000@.TK2MSFTNGP12.phx.gbl...
> I have an installed sql server 2000 language English with server collation
> set to SQL_Latin1_General_CP1_CI_AS on a W2k server box. There are several
> applications running in production using that machine and those settings.
> Now the company is getting a new program that has as the requirement for
its
> sql server dictionary order to be set to Custom, Case insensitive, Accent
> insensitive, for use with 1252 character set.
> This server has lots of capacity left and there is no way to justify
> spending dollars on another processor license for this app when the server
> we already have should be able to do the job.
> Is it possible to change the dictionary order on the current server and
its
> databases and test my current apps? If they still work as before I can
then
> leave it like that, if not I would have to be able to come back to current
> settings.
> What is the expert opinion on the difference in settings between the new
app
> and the current settings as shown above, do you think we should try
keeping
> our current settings for the new app? The app coming in is using asp pages
> and the users are all French, the data in the files will be French with
> accented characters and also some English in some fields.
> Any help would be greatly appreciated,
> RD
>

Dictionarry order question

I have an installed sql server 2000 language English with server collation
set to SQL_Latin1_General_CP1_CI_AS on a W2k server box. There are several
applications running in production using that machine and those settings.
Now the company is getting a new program that has as the requirement for its
sql server dictionary order to be set to Custom, Case insensitive, Accent
insensitive, for use with 1252 character set.
This server has lots of capacity left and there is no way to justify
spending dollars on another processor license for this app when the server
we already have should be able to do the job.
Is it possible to change the dictionary order on the current server and its
databases and test my current apps? If they still work as before I can then
leave it like that, if not I would have to be able to come back to current
settings.
What is the expert opinion on the difference in settings between the new app
and the current settings as shown above, do you think we should try keeping
our current settings for the new app? The app coming in is using asp pages
and the users are all French, the data in the files will be French with
accented characters and also some English in some fields.
Any help would be greatly appreciated,
RDIf it was me, I'd set up a new instance of SQL Server (configured with the
collation sequence of your choice) and let 'er rip. But with SQL Server
2000, it's possible to have different collation sequences on the instance,
database, table, or even column (IIRC). So there's no reason to change your
existing apps and databases. As far as I know, changing collations is a
real pain in the butt, and involves DTS'ing or BCP'ing your data to a new
home, or out to a temporary one, and in to the re-built one.
Clint
"RD" <bdufour@.sgiims.com> wrote in message
news:#OXncM9hFHA.4000@.TK2MSFTNGP12.phx.gbl...
> I have an installed sql server 2000 language English with server collation
> set to SQL_Latin1_General_CP1_CI_AS on a W2k server box. There are several
> applications running in production using that machine and those settings.
> Now the company is getting a new program that has as the requirement for
its
> sql server dictionary order to be set to Custom, Case insensitive, Accent
> insensitive, for use with 1252 character set.
> This server has lots of capacity left and there is no way to justify
> spending dollars on another processor license for this app when the server
> we already have should be able to do the job.
> Is it possible to change the dictionary order on the current server and
its
> databases and test my current apps? If they still work as before I can
then
> leave it like that, if not I would have to be able to come back to current
> settings.
> What is the expert opinion on the difference in settings between the new
app
> and the current settings as shown above, do you think we should try
keeping
> our current settings for the new app? The app coming in is using asp pages
> and the users are all French, the data in the files will be French with
> accented characters and also some English in some fields.
> Any help would be greatly appreciated,
> RD
>

Dictionarry order question

I have an installed sql server 2000 language English with server collation
set to SQL_Latin1_General_CP1_CI_AS on a W2k server box. There are several
applications running in production using that machine and those settings.
Now the company is getting a new program that has as the requirement for its
sql server dictionary order to be set to Custom, Case insensitive, Accent
insensitive, for use with 1252 character set.
This server has lots of capacity left and there is no way to justify
spending dollars on another processor license for this app when the server
we already have should be able to do the job.
Is it possible to change the dictionary order on the current server and its
databases and test my current apps? If they still work as before I can then
leave it like that, if not I would have to be able to come back to current
settings.
What is the expert opinion on the difference in settings between the new app
and the current settings as shown above, do you think we should try keeping
our current settings for the new app? The app coming in is using asp pages
and the users are all French, the data in the files will be French with
accented characters and also some English in some fields.
Any help would be greatly appreciated,
RDIf it was me, I'd set up a new instance of SQL Server (configured with the
collation sequence of your choice) and let 'er rip. But with SQL Server
2000, it's possible to have different collation sequences on the instance,
database, table, or even column (IIRC). So there's no reason to change your
existing apps and databases. As far as I know, changing collations is a
real pain in the butt, and involves DTS'ing or BCP'ing your data to a new
home, or out to a temporary one, and in to the re-built one.
Clint
"RD" <bdufour@.sgiims.com> wrote in message
news:#OXncM9hFHA.4000@.TK2MSFTNGP12.phx.gbl...
> I have an installed sql server 2000 language English with server collation
> set to SQL_Latin1_General_CP1_CI_AS on a W2k server box. There are several
> applications running in production using that machine and those settings.
> Now the company is getting a new program that has as the requirement for
its
> sql server dictionary order to be set to Custom, Case insensitive, Accent
> insensitive, for use with 1252 character set.
> This server has lots of capacity left and there is no way to justify
> spending dollars on another processor license for this app when the server
> we already have should be able to do the job.
> Is it possible to change the dictionary order on the current server and
its
> databases and test my current apps? If they still work as before I can
then
> leave it like that, if not I would have to be able to come back to current
> settings.
> What is the expert opinion on the difference in settings between the new
app
> and the current settings as shown above, do you think we should try
keeping
> our current settings for the new app? The app coming in is using asp pages
> and the users are all French, the data in the files will be French with
> accented characters and also some English in some fields.
> Any help would be greatly appreciated,
> RD
>

Development Environment Needs?

Trying to figure out what development enviroment we need in order to
do the following:

- develop a non-native SQL server stored procedure;
- call a web service or java program from the stored procedure;
- return static values;
- call the stored procedure from a view.

How do I get a hold of the right tools and what do I need to put the
pieces together?

Obviously, I've not used SQL server and I'm looking for the basic
starting point.

Thanks!CG (chelseagraylin@.hotmail.com) writes:
> Trying to figure out what development enviroment we need in order to
> do the following:
> - develop a non-native SQL server stored procedure;
> - call a web service or java program from the stored procedure;
> - return static values;
> - call the stored procedure from a view.

You cannot call stored procedures from views. You can call extended
stored procedures from used-defined functions though, and these you
call from views. (Or use table-valued functions which are basically
parameterized views, and which can be multi-statement.)

Typically you develop extended stored procedures in C++. If you want to
talk .Net you would need a COM interop.

Since you appear to be forward-looking, you might find interest in
the upcoming version of SQL Server, where you can program CLR directly
in SQL Server. In SQL 2005 you can develop the function directly in
CLR. To call a web service there would still be a few things to go
through, but it would certainly be easier.

Beta 2 of SQL Server is expected soon, and this will be a public beta.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||So if I want to do this with the current version (yes, looks like this
will be much easier in the future!), I need a SQL Server environment
set up (hopefully just the desktop version) and then I need an
environment to be able to write a C++ program that is accessible by
SQL Server?

Sounds so easy...|||CG (chelseagraylin@.hotmail.com) writes:
> So if I want to do this with the current version (yes, looks like this
> will be much easier in the future!), I need a SQL Server environment
> set up (hopefully just the desktop version) and then I need an
> environment to be able to write a C++ program that is accessible by
> SQL Server?

For SQL Server I would recommend using Developer Edition, which is at
49 USD only. Developer Edition comes with graphic tools, and having
Query Analyzer to submit queries is invaluable.

However, once you go in production, you are better of with MSDE, since
Developer Edition is not licensed for production. (And for some strange
reason, the graphic tools can be used against MSDE according to the
license.)

You will also need a couple of include files and link libraries. They
come with Devloper Edition.

For the C++ environment I am not really the guy to ask, but Visual
Studio is of course a safe bet. If you go for GNU C++ to use freeware,
you will probably need the Platform SDK, which I have no idea how it
is available outside VS.

> Sounds so easy...

Getting the environment is indeed the easy part. The actual development
is likely to be tougher. Writing extended stored procedures is not for
the faint of heart. Keep in mind that they execute in-process, so an
access violation in your XP can crash the entire SQL Server.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog (esquel@.sommarskog.se) writes:
> (And for some strange reason, the graphic tools can be used against MSDE
> according to the license.)

An important word disappeared here, so I take it again:

> (And for some strange reason, the graphic tools can *not* be used against
> MSDE according to the license.)

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi

To add to Erland's post...

Don't forget source code control, such as Visual Source Safe or PVCS... If
you go the whole Microsoft suite then a MSDN subscription would be an
excellent investment.

John

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns951B2905B452Yazorman@.127.0.0.1...
> CG (chelseagraylin@.hotmail.com) writes:
> > So if I want to do this with the current version (yes, looks like this
> > will be much easier in the future!), I need a SQL Server environment
> > set up (hopefully just the desktop version) and then I need an
> > environment to be able to write a C++ program that is accessible by
> > SQL Server?
> For SQL Server I would recommend using Developer Edition, which is at
> 49 USD only. Developer Edition comes with graphic tools, and having
> Query Analyzer to submit queries is invaluable.
> However, once you go in production, you are better of with MSDE, since
> Developer Edition is not licensed for production. (And for some strange
> reason, the graphic tools can be used against MSDE according to the
> license.)
> You will also need a couple of include files and link libraries. They
> come with Devloper Edition.
> For the C++ environment I am not really the guy to ask, but Visual
> Studio is of course a safe bet. If you go for GNU C++ to use freeware,
> you will probably need the Platform SDK, which I have no idea how it
> is available outside VS.
> > Sounds so easy...
> Getting the environment is indeed the easy part. The actual development
> is likely to be tougher. Writing extended stored procedures is not for
> the faint of heart. Keep in mind that they execute in-process, so an
> access violation in your XP can crash the entire SQL Server.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

Determining Trigger Order

I'm not sure if this is the correct forum or not for this question. Basically, i have 6 triggers attached to my table (2 of each type). I used sp_settriggerorder to make sure certian triggers fire first.

Is there any way to go back and determine what order a trigger has been set to (first, none or last)? I've scanned through all of the system tables and don't see anything.

Thanks in advance.

With some digging, you can find that they encode the order into the status fields on sysobjects. However, the way they are exposed is with the ObjectProperty() function -- it takes parameters such as ExecIsFirstUpdateTrigger.

It is not obvious to me how triggers are exposed from the Information_Schema views.

The digging involves using sp_helptext on the sp_settriggerorder and sp_helptrigger stored procedures.

|||That's exactly what I was looking for.

I didn't even think abou tusing sp_helptext to see what the procedure did. I'll definitely use that next time I want to know what SQL is doing.

Thanks!