How to change the default dateformat?

My Sql Server connection has default dateformat to "ymd".
I have to change the dateformat to "dmy" each time when I insert or
update a datetime table column e.g.with '31.12.2005' using the
"set dateformat dmy" command. Does anybody know, how to change the
default dateformat to avoid to perform the set dateformat on
each connection?
thanks
LiborLook here:
You can change the settings of the server, but if yor provider will
change the settings during the login process and sets it back to the
default language settings of the user you have to convert the date in a
explicit format or change the codepage of the user:
http://groups.google.de/group/microsoft.public.sqlserver.programming/browse_frm/thread/6496fbdbbd4cffd9/952557646f8b9a9b?lnk=st&q=serversettings+date+format&rnum=1&hl=en#952557646f8b9a9b
http://groups.google.de/group/microsoft.public.sqlserver.server/browse_frm/thread/6f543ebd963f6e20/46144e7348a7b6ff?lnk=st&q=%22Jens+Suessmeyer%22+boss+tibor&rnum=1&hl=en#46144e7348a7b6ff
HTH, Jens Suessmeyer.

how to change server option for isolaiton level?

I'm having problem with locking in update and select.
my update takes long and select from clients happens all the time.
I know with NOLOCK in select or set transaction isolation level, I can
read data uncommited.
I want to know if there is an option I can change on server or
database
to change isolation level for all incoming connections.

thanks for your help in advance.AFAIK, you can't set READ UNCOMMITTED at the server level. This is
probably a good thing since one should read uncommitted data only in
specific situations where all off the ramifications have been fully
evaluated and understood.

--
Hope this helps.

Dan Guzman
SQL Server MVP

--------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index...epartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--------

"neo" <second714@.hotmail.com> wrote in message
news:155f8e7d.0311071340.6727ff6d@.posting.google.c om...
> I'm having problem with locking in update and select.
> my update takes long and select from clients happens all the time.
> I know with NOLOCK in select or set transaction isolation level, I can
> read data uncommited.
> I want to know if there is an option I can change on server or
> database
> to change isolation level for all incoming connections.
> thanks for your help in advance.|||Hi

To add to Dan's comment...

Are you wanting to make the database read only?

John

"neo" <second714@.hotmail.com> wrote in message
news:155f8e7d.0311071340.6727ff6d@.posting.google.c om...
> I'm having problem with locking in update and select.
> my update takes long and select from clients happens all the time.
> I know with NOLOCK in select or set transaction isolation level, I can
> read data uncommited.
> I want to know if there is an option I can change on server or
> database
> to change isolation level for all incoming connections.
> thanks for your help in advance.|||Thanks to Dan/John.

No I just wanted to allow dirty-read, not read-only db.

I thought there was an option in SQL to set db to allow read uncommited.
Maybe for SQL 7?

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message news:<boieas$93o$1@.titan.btinternet.com>...
> Hi
> To add to Dan's comment...
> Are you wanting to make the database read only?
> John
> "neo" <second714@.hotmail.com> wrote in message
> news:155f8e7d.0311071340.6727ff6d@.posting.google.c om...
> > I'm having problem with locking in update and select.
> > my update takes long and select from clients happens all the time.
> > I know with NOLOCK in select or set transaction isolation level, I can
> > read data uncommited.
> > I want to know if there is an option I can change on server or
> > database
> > to change isolation level for all incoming connections.
> > thanks for your help in advance.|||Hi

This is not something you would want do as a default, and when you do
require it, then it has to be a concious decission to allow this;
therefore not having it at server level or as a default for the
connection is probably a good thing. You can always set it for the
connection/session.

John

second714@.hotmail.com (neo) wrote in message news:<155f8e7d.0311101122.ca78281@.posting.google.com>...
> Thanks to Dan/John.
> No I just wanted to allow dirty-read, not read-only db.
> I thought there was an option in SQL to set db to allow read uncommited.
> Maybe for SQL 7?
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message news:<boieas$93o$1@.titan.btinternet.com>...
> > Hi
> > To add to Dan's comment...
> > Are you wanting to make the database read only?
> > John
> > "neo" <second714@.hotmail.com> wrote in message
> > news:155f8e7d.0311071340.6727ff6d@.posting.google.c om...
> > > I'm having problem with locking in update and select.
> > > my update takes long and select from clients happens all the time.
> > > I know with NOLOCK in select or set transaction isolation level, I can
> > > read data uncommited.
> > > I want to know if there is an option I can change on server or
> > > database
> > > to change isolation level for all incoming connections.
> > > > thanks for your help in advance.|||Thanks,
one more thing,
what about Query analyzer?
is there any way not to run "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED"
everytime in Query Analyzer?|||Hi

As far as I know, you can't do this, configurable options appear on
the tools/options dialogues and that is not one of them. If it was you
could use a specific configuration file. The only thing I can suggest
it to put it in a template.

John

second714@.hotmail.com (neo) wrote in message news:<155f8e7d.0311111702.4609998d@.posting.google.com>...
> Thanks,
> one more thing,
> what about Query analyzer?
> is there any way not to run "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED"
> everytime in Query Analyzer?

how to change permission via t-sql

Hello,
I have a database that has about 50 tables. I have a user group assigned
to the database. I want them to be able to insert/update but not delete. The
only way I have seen is to go into the table permissions and change the
permissions. Is there an easier way via t-sql? Thanks in advance.
JakeRun this, verify that the output will do what you want and then execute the
output:
SELECT 'GRANT INSERT, UPDATE ON ' + TABLE_NAME + ' TO YourUserGroup' FROM
INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'base table'
--
Keith
"Jake" <rondican@.hotmail.com> wrote in message
news:uyD6ueAoEHA.1456@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I have a database that has about 50 tables. I have a user group
assigned
> to the database. I want them to be able to insert/update but not delete.
The
> only way I have seen is to go into the table permissions and change the
> permissions. Is there an easier way via t-sql? Thanks in advance.
> Jake
>|||Keith,
Thanks very much for the info
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:efMdMqAoEHA.2588@.TK2MSFTNGP12.phx.gbl...
> Run this, verify that the output will do what you want and then execute
the
> output:
> SELECT 'GRANT INSERT, UPDATE ON ' + TABLE_NAME + ' TO YourUserGroup' FROM
> INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'base table'
> --
> Keith
>
> "Jake" <rondican@.hotmail.com> wrote in message
> news:uyD6ueAoEHA.1456@.TK2MSFTNGP10.phx.gbl...
> > Hello,
> >
> > I have a database that has about 50 tables. I have a user group
> assigned
> > to the database. I want them to be able to insert/update but not delete.
> The
> > only way I have seen is to go into the table permissions and change the
> > permissions. Is there an easier way via t-sql? Thanks in advance.
> >
> > Jake
> >
> >
>

how to change permission via t-sql

Hello,
I have a database that has about 50 tables. I have a user group assigned
to the database. I want them to be able to insert/update but not delete. The
only way I have seen is to go into the table permissions and change the
permissions. Is there an easier way via t-sql? Thanks in advance.
Jake
Run this, verify that the output will do what you want and then execute the
output:
SELECT 'GRANT INSERT, UPDATE ON ' + TABLE_NAME + ' TO YourUserGroup' FROM
INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'base table'
Keith
"Jake" <rondican@.hotmail.com> wrote in message
news:uyD6ueAoEHA.1456@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I have a database that has about 50 tables. I have a user group
assigned
> to the database. I want them to be able to insert/update but not delete.
The
> only way I have seen is to go into the table permissions and change the
> permissions. Is there an easier way via t-sql? Thanks in advance.
> Jake
>
|||Keith,
Thanks very much for the info
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:efMdMqAoEHA.2588@.TK2MSFTNGP12.phx.gbl...
> Run this, verify that the output will do what you want and then execute
the
> output:
> SELECT 'GRANT INSERT, UPDATE ON ' + TABLE_NAME + ' TO YourUserGroup' FROM
> INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'base table'
> --
> Keith
>
> "Jake" <rondican@.hotmail.com> wrote in message
> news:uyD6ueAoEHA.1456@.TK2MSFTNGP10.phx.gbl...
> assigned
> The
>
sql

How to catchup job schedule? Scheduled jobs not running.

Hi,
I try not to schedule jobs during sw/hw maintenance windows, but sometimes
it happens - even more so now with active windows update running. It just
shuts down without regard to what's running.
Unfortunately, when the sql server comes back up, it doesn't run jobs that
are past due. I can't seem to find any setting that would 'catch-up' the
jobs. Would you share your thoughts on how to deal with this situation.
thanks
Doug
I don't think there is a "built-in" function for this type of situation.
However, you can create your own stored procedure that queries
msdb..sysjobhistory and look for run_status = 0, then use sp_start_job to
start those jobs that failed. And then you can set up a scheduled job to run
this SP during every restart.
"Doug Little" wrote:

> Hi,
> I try not to schedule jobs during sw/hw maintenance windows, but sometimes
> it happens - even more so now with active windows update running. It just
> shuts down without regard to what's running.
> Unfortunately, when the sql server comes back up, it doesn't run jobs that
> are past due. I can't seem to find any setting that would 'catch-up' the
> jobs. Would you share your thoughts on how to deal with this situation.
> thanks
> --
> Doug

How to catchup job schedule? Scheduled jobs not running.

Hi,
I try not to schedule jobs during sw/hw maintenance windows, but sometimes
it happens - even more so now with active windows update running. It just
shuts down without regard to what's running.
Unfortunately, when the sql server comes back up, it doesn't run jobs that
are past due. I can't seem to find any setting that would 'catch-up' the
jobs. Would you share your thoughts on how to deal with this situation.
thanks
DougI don't think there is a "built-in" function for this type of situation.
However, you can create your own stored procedure that queries
msdb..sysjobhistory and look for run_status = 0, then use sp_start_job to
start those jobs that failed. And then you can set up a scheduled job to ru
n
this SP during every restart.
"Doug Little" wrote:

> Hi,
> I try not to schedule jobs during sw/hw maintenance windows, but sometimes
> it happens - even more so now with active windows update running. It just
> shuts down without regard to what's running.
> Unfortunately, when the sql server comes back up, it doesn't run jobs that
> are past due. I can't seem to find any setting that would 'catch-up' the
> jobs. Would you share your thoughts on how to deal with this situation.
> thanks
> --
> Doug

How to catchup job schedule? Scheduled jobs not running.

Hi,
I try not to schedule jobs during sw/hw maintenance windows, but sometimes
it happens - even more so now with active windows update running. It just
shuts down without regard to what's running.
Unfortunately, when the sql server comes back up, it doesn't run jobs that
are past due. I can't seem to find any setting that would 'catch-up' the
jobs. Would you share your thoughts on how to deal with this situation.
thanks
--
DougI don't think there is a "built-in" function for this type of situation.
However, you can create your own stored procedure that queries
msdb..sysjobhistory and look for run_status = 0, then use sp_start_job to
start those jobs that failed. And then you can set up a scheduled job to run
this SP during every restart.
"Doug Little" wrote:
> Hi,
> I try not to schedule jobs during sw/hw maintenance windows, but sometimes
> it happens - even more so now with active windows update running. It just
> shuts down without regard to what's running.
> Unfortunately, when the sql server comes back up, it doesn't run jobs that
> are past due. I can't seem to find any setting that would 'catch-up' the
> jobs. Would you share your thoughts on how to deal with this situation.
> thanks
> --
> Doug

how to call a batch file from trigger

Hi,
I have a requirement to invoke a java application and update the remote MySQL database, when a particular field in MSSQL Server 2000 is updated. my idea is to write a trigger on AFTER update of field and from trigger invoke a batch file which will run jav
a application.
if any one provide me information on how to invoke a batch file from trigger or any alternative solution to my problem, would be mutch appriciated.
Thanks In advance.
Sunil
Hi Sunil,
Try using the following command in the trigger
exec master.dbo.xp_cmdshell '<bat_file>'
HTH
Ashish
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Sunil,
As Ashish indicates, xp_cmdshell will enable you to invoke external shell
cmds.
I personally would, in general, be wary of this approach as there are a
number of undesirable implications with it: the primary one being execution
time. Within a transaction, the time taken to invoke the shell (and then run
your application which updates a remote MySQL instance) would be extremely
costly; this would be even more so for multirow updates. One consequence of
this is that you will be hold locks for long periods of time, and your table
is likely to become a hot spot / point of contention. Security could also be
an issue. How much control do you have of the batch file that will be
invoked ? Can anyone replace the file with a potentially malicious script ?
As regards alternatives, the logically obvious one is to make the relevant
transaction distributed. This also gives rise to various issues, but I
gather linking to MySQL is problematic because of insufficient support from
the ODBC driver. Still you may wish to investigate this further.
Alternatively, within your trigger, write the updated data to another table.
From outside SQL Server (in your Java app / server, say) you could then poll
the table, and apply any updates to MySQL. This would probably be the more
"robust" approach.
A "similar" approach would be use the SQL Server Agent's alert system. This
would, essentially, entail your defining a custom error / message [ie.
sp_addmessage 50100, 10, 'Field Updated', say], and then defining an alert
based on your custom error number; your alert's response could then invoke
your batch file. To raise the event from within your trigger, you would
simply call raiserror with your custom error number [ie. raiserror(50100,
10, 1), say].
It would also be possible to combine these: you could write the updated data
to another table, and when you've built up a batch of pending updates raise
an event / alert to run your batch file / app. If you decide to batch
updates, try to avoid counting rows if at all possible (you may be able to
use sysindexes.rowcnt instead); you could also batch based on time or on the
data being updated.
Without knowing a bit more about your requirements (inc. timeliness of
updates to MySQL) and your environment, it is difficult to decide which
approach is "best". Still, the above should give you a few more options to
consider.
Regards
Stewart
"Sunil" <sunil@.methdos.com> wrote in message
news:A29F6296-6088-421D-8DE4-364B0D7EF6C8@.microsoft.com...
> Hi,
> I have a requirement to invoke a java application and update the remote
MySQL database, when a particular field in MSSQL Server 2000 is updated. my
idea is to write a trigger on AFTER update of field and from trigger invoke
a batch file which will run java application.
> if any one provide me information on how to invoke a batch file from
trigger or any alternative solution to my problem, would be mutch
appriciated.
> Thanks In advance.
> Sunil

how to call a batch file from trigger

Hi,
I have a requirement to invoke a Java application and update the remote MySQ
L database, when a particular field in MSSQL Server 2000 is updated. my idea
is to write a trigger on AFTER update of field and from trigger invoke a ba
tch file which will run jav
a application.
if any one provide me information on how to invoke a batch file from trigger
or any alternative solution to my problem, would be mutch appriciated.
Thanks In advance.
SunilHi Sunil,
Try using the following command in the trigger
exec master.dbo.xp_cmdshell '<bat_file>'
HTH
Ashish
This posting is provided "AS IS" with no warranties, and confers no rights.|||Sunil,
As Ashish indicates, xp_cmdshell will enable you to invoke external shell
cmds.
I personally would, in general, be wary of this approach as there are a
number of undesirable implications with it: the primary one being execution
time. Within a transaction, the time taken to invoke the shell (and then run
your application which updates a remote mysql instance) would be extremely
costly; this would be even more so for multirow updates. One consequence of
this is that you will be hold locks for long periods of time, and your table
is likely to become a hot spot / point of contention. Security could also be
an issue. How much control do you have of the batch file that will be
invoked ? Can anyone replace the file with a potentially malicious script ?
As regards alternatives, the logically obvious one is to make the relevant
transaction distributed. This also gives rise to various issues, but I
gather linking to mysql is problematic because of insufficient support from
the ODBC driver. Still you may wish to investigate this further.
Alternatively, within your trigger, write the updated data to another table.
From outside SQL Server (in your Java app / server, say) you could then poll
the table, and apply any updates to MySQL. This would probably be the more
"robust" approach.
A "similar" approach would be use the SQL Server Agent's alert system. This
would, essentially, entail your defining a custom error / message [ie.
sp_addmessage 50100, 10, 'Field Updated', say], and then defining an alert
based on your custom error number; your alert's response could then invoke
your batch file. To raise the event from within your trigger, you would
simply call raiserror with your custom error number [ie. raiserror(50100
,
10, 1), say].
It would also be possible to combine these: you could write the updated data
to another table, and when you've built up a batch of pending updates raise
an event / alert to run your batch file / app. If you decide to batch
updates, try to avoid counting rows if at all possible (you may be able to
use sysindexes.rowcnt instead); you could also batch based on time or on the
data being updated.
Without knowing a bit more about your requirements (inc. timeliness of
updates to MySQL) and your environment, it is difficult to decide which
approach is "best". Still, the above should give you a few more options to
consider.
Regards
Stewart
"Sunil" <sunil@.methdos.com> wrote in message
news:A29F6296-6088-421D-8DE4-364B0D7EF6C8@.microsoft.com...
> Hi,
> I have a requirement to invoke a Java application and update the remote
MySQL database, when a particular field in MSSQL Server 2000 is updated. my
idea is to write a trigger on AFTER update of field and from trigger invoke
a batch file which will run Java application.
> if any one provide me information on how to invoke a batch file from
trigger or any alternative solution to my problem, would be mutch
appriciated.
> Thanks In advance.
> Sunil

how to call a batch file from trigger

Hi
I have a requirement to invoke a java application and update the remote MySQL database, when a particular field in MSSQL Server 2000 is updated. my idea is to write a trigger on AFTER update of field and from trigger invoke a batch file which will run java application
if any one provide me information on how to invoke a batch file from trigger or any alternative solution to my problem, would be mutch appriciated
Thanks In advance
SunilHi Sunil,
Try using the following command in the trigger
exec master.dbo.xp_cmdshell '<bat_file>'
HTH
Ashish
This posting is provided "AS IS" with no warranties, and confers no rights.|||Sunil,
As Ashish indicates, xp_cmdshell will enable you to invoke external shell
cmds.
I personally would, in general, be wary of this approach as there are a
number of undesirable implications with it: the primary one being execution
time. Within a transaction, the time taken to invoke the shell (and then run
your application which updates a remote MySQL instance) would be extremely
costly; this would be even more so for multirow updates. One consequence of
this is that you will be hold locks for long periods of time, and your table
is likely to become a hot spot / point of contention. Security could also be
an issue. How much control do you have of the batch file that will be
invoked ? Can anyone replace the file with a potentially malicious script ?
As regards alternatives, the logically obvious one is to make the relevant
transaction distributed. This also gives rise to various issues, but I
gather linking to MySQL is problematic because of insufficient support from
the ODBC driver. Still you may wish to investigate this further.
Alternatively, within your trigger, write the updated data to another table.
From outside SQL Server (in your Java app / server, say) you could then poll
the table, and apply any updates to MySQL. This would probably be the more
"robust" approach.
A "similar" approach would be use the SQL Server Agent's alert system. This
would, essentially, entail your defining a custom error / message [ie.
sp_addmessage 50100, 10, 'Field Updated', say], and then defining an alert
based on your custom error number; your alert's response could then invoke
your batch file. To raise the event from within your trigger, you would
simply call raiserror with your custom error number [ie. raiserror(50100,
10, 1), say].
It would also be possible to combine these: you could write the updated data
to another table, and when you've built up a batch of pending updates raise
an event / alert to run your batch file / app. If you decide to batch
updates, try to avoid counting rows if at all possible (you may be able to
use sysindexes.rowcnt instead); you could also batch based on time or on the
data being updated.
Without knowing a bit more about your requirements (inc. timeliness of
updates to MySQL) and your environment, it is difficult to decide which
approach is "best". Still, the above should give you a few more options to
consider.
Regards
Stewart
"Sunil" <sunil@.methdos.com> wrote in message
news:A29F6296-6088-421D-8DE4-364B0D7EF6C8@.microsoft.com...
> Hi,
> I have a requirement to invoke a java application and update the remote
MySQL database, when a particular field in MSSQL Server 2000 is updated. my
idea is to write a trigger on AFTER update of field and from trigger invoke
a batch file which will run java application.
> if any one provide me information on how to invoke a batch file from
trigger or any alternative solution to my problem, would be mutch
appriciated.
> Thanks In advance.
> Sunil