Different times on different instances

Hi,
we have sql server 2000, sp4 and we need to add new instance to this server.
Is it possible to setup time (zone) different than server local time for this
new instance?
I am asking this because application which should rely on new instance uses
extensively getdate() function, and cost of redevelopement would be huge...
How about changing getdate()?
Thanks,
PedjaPedja wrote:
> Hi,
> we have sql server 2000, sp4 and we need to add new instance to this
> server. Is it possible to setup time (zone) different than server
> local time for this new instance?
> I am asking this because application which should rely on new
> instance uses extensively getdate() function, and cost of
> redevelopement would be huge... How about changing getdate()?
> Thanks,
> Pedja
Not unless you are using virtual servers, then maybe. If you change
getdate() to GETUTCDATE(), you could store all your dates in GMT.
--
David Gugick - SQL Server MVP
Quest Software

Different times on different instances

Hi,
we have sql server 2000, sp4 and we need to add new instance to this server.
Is it possible to setup time (zone) different than server local time for thi
s
new instance?
I am asking this because application which should rely on new instance uses
extensively getdate() function, and cost of redevelopement would be huge...
How about changing getdate()?
Thanks,
PedjaPedja wrote:
> Hi,
> we have sql server 2000, sp4 and we need to add new instance to this
> server. Is it possible to setup time (zone) different than server
> local time for this new instance?
> I am asking this because application which should rely on new
> instance uses extensively getdate() function, and cost of
> redevelopement would be huge... How about changing getdate()?
> Thanks,
> Pedja
Not unless you are using virtual servers, then maybe. If you change
getdate() to GETUTCDATE(), you could store all your dates in GMT.
David Gugick - SQL Server MVP
Quest Software

Different times on different instances

Hi,
we have sql server 2000, sp4 and we need to add new instance to this server.
Is it possible to setup time (zone) different than server local time for this
new instance?
I am asking this because application which should rely on new instance uses
extensively getdate() function, and cost of redevelopement would be huge...
How about changing getdate()?
Thanks,
Pedja
Pedja wrote:
> Hi,
> we have sql server 2000, sp4 and we need to add new instance to this
> server. Is it possible to setup time (zone) different than server
> local time for this new instance?
> I am asking this because application which should rely on new
> instance uses extensively getdate() function, and cost of
> redevelopement would be huge... How about changing getdate()?
> Thanks,
> Pedja
Not unless you are using virtual servers, then maybe. If you change
getdate() to GETUTCDATE(), you could store all your dates in GMT.
David Gugick - SQL Server MVP
Quest Software

Different language instances on same server

Is it possible to install two different SQL server version
instances, for instance, one in english and the other one
in Spanish, in the same server?
We are having a problem in our central server, with a
especific software which runs correctly only in the SQL
server spanish version.
For sure, all collation and language settings have been
tested in the english version with no result.
Thanks in advanceGustavo,
Yes, given you can even install SQL7 and SQL2000 on the same
server - they don't share much at all !
But there are license issues / costs:
http://www.microsoft.com/sql/howtobuy/faq.asp
"SQL Server 2000 Enterprise Edition includes the ability to run multiple instances of SQL Server 2000 on a
single computer. Multiple instances are used by organizations that have several applications running on a
server, but want them to run in isolation so that any problem in one instance will not affect the other
instances.
With SQL Server 2000 Enterprise Edition, you can install multiple instances of SQL Server on the same computer
without having additional licenses. Although SQL Server 2000 Standard Edition will technically support
multiple instances, each instance requires a separate license. "
"Gustavo" <gcarreras@.koppert.es> wrote in message news:002401c39a39$237e29b0$a101280a@.phx.gbl...
> Is it possible to install two different SQL server version
> instances, for instance, one in english and the other one
> in Spanish, in the same server?
> We are having a problem in our central server, with a
> especific software which runs correctly only in the SQL
> server spanish version.
> For sure, all collation and language settings have been
> tested in the english version with no result.
> Thanks in advance

Different instances of SQL Server

Hi there,

I was wondering if it's possible to create a new instance in the SQL Server something like this:

(Local)\\MSSMLBIZ

(Local)\\BUSINESS

Thank you

yes you can create as Server name \\ instance name where the Server name can be the same.......i have 3 instances where my server name is Deepak\\instance1 similarly instance2 and instnace3

|||

you can have multiple instances on one physical machine. One Defualt instance and all others are called Named Instance. Default instance will have same physical machine name. all other will have Physical machine name \Name of the instance

sql server 2005

sql server 32 bit 64 bit

Instances per computer

50 instances on a stand-alone server for all SQL Server 2005 editions except for Workgroup Edition. Workgroup Edition supports a maximum of 16 instances.

SQL Server 2005 supports 25 instances on a failover cluster.

50 instances on a stand-alone server.

25 instances on a failover cluster

http://technet.microsoft.com/en-us/library/ms143432.aspx

Madhu

|||

Does it apply to SQL Server Express? if yes how do I create (Local)\Myinstance in C#? or if I can't do it in C#, how do I create it manually?

Thank you

|||

Yes. You need to re-run setup to create a new instance of sql server with instance name = "MyInstance". The instance name page is on one of pages in the setup wizard. There is no way to automate creating a new instance with C# with an API like SMO. You can invoke setup with parameters to specify things like instance name, and run setup without user intervention. There is more information in BOL.

Different Default Date format for 2 SQL Server Instances

Hi,
We recently had a new environment created. The servers were all installed
as separate instances on the same physical machine.
For my first instance INST1
When I execute the following query exec getMyData '1964-11-19'
in query analyser everything is fine
from my (ASP) website everything is fine
For my second instance INST2
in query analyser everything is fine
from my asp website I get varchar cannot be converted to datetime.
I can only believe that the default settings for the server were different
when each of the SQL Server Installs were performed.
I cannot change the way we pass dates in our website because it is a massive
re-write of everytihg if I do.
Is there some why of changing the default settings of the server after
installing?
I have tried using
sp_configure
SET language
sp_defaultlanguage
and all these methods did not fix my problem.
Any ideas?
JayneK wrote:
> Hi,
> We recently had a new environment created. The servers were all
> installed as separate instances on the same physical machine.
> For my first instance INST1
> When I execute the following query exec getMyData '1964-11-19'
> in query analyser everything is fine
> from my (ASP) website everything is fine
> For my second instance INST2
> in query analyser everything is fine
> from my asp website I get varchar cannot be converted to datetime.
> I can only believe that the default settings for the server were
> different when each of the SQL Server Installs were performed.
> I cannot change the way we pass dates in our website because it is a
> massive re-write of everytihg if I do.
> Is there some why of changing the default settings of the server after
> installing?
> I have tried using
> sp_configure
> SET language
> sp_defaultlanguage
> and all these methods did not fix my problem.
> Any ideas?
When working with dates in character format, you should only ever use a
portable format. Two formats are supported that will never cause
problems related to the server's regional settings:
yyyy-mm-ddThh:mm:ss.mmm (no spaces)
yyyymmdd
What is probably occurring is that one server is using MDY format and
the other is using DMY.
For example:
SET NOCOUNT ON
SET DATEFORMAT MDY
SELECT CAST('1964-11-19' as DATETIME)
SET DATEFORMAT DMY
SELECT CAST('1964-11-19' as DATETIME)
-- Results
1964-11-19 00:00:00.000
Server: Msg 242, Level 16, State 3, Line 9
The conversion of a char data type to a datetime data type resulted in
an out-of-range datetime value.
David Gugick - SQL Server MVP
Quest Software
|||David,
Unfortunately they all have all their language setting exactly the same. So
they are all set to us_english as default language, yet one server acts
differently to the other.
How can we fix this? do we have to uninstall and re-install, can't we hack
a file or something?
"David Gugick" wrote:

> JayneK wrote:
> When working with dates in character format, you should only ever use a
> portable format. Two formats are supported that will never cause
> problems related to the server's regional settings:
> yyyy-mm-ddThh:mm:ss.mmm (no spaces)
> yyyymmdd
>
> What is probably occurring is that one server is using MDY format and
> the other is using DMY.
> For example:
> SET NOCOUNT ON
> SET DATEFORMAT MDY
> SELECT CAST('1964-11-19' as DATETIME)
> SET DATEFORMAT DMY
> SELECT CAST('1964-11-19' as DATETIME)
> -- Results
> 1964-11-19 00:00:00.000
> Server: Msg 242, Level 16, State 3, Line 9
> The conversion of a char data type to a datetime data type resulted in
> an out-of-range datetime value.
>
>
> --
> David Gugick - SQL Server MVP
> Quest Software
>
|||JayneK wrote:[vbcol=seagreen]
> David,
> Unfortunately they all have all their language setting exactly the
> same. So they are all set to us_english as default language, yet one
> server acts differently to the other.
> How can we fix this? do we have to uninstall and re-install, can't
> we hack a file or something?
> "David Gugick" wrote:
The ASP web site client is likely set up different. Go to that PC, open
up QA, and run the example above. The problem is that you are not using
a portable date format and are bound to run into these types of
problems. If you get rid of the hyphens in the date parameter, that will
probably fix the issue. Since you cannot easily change the code
executing the date procedure you wrote, why not just change the
procedure itself to strip the hyphens out using Set @.MyDate =
REPLACE(@.MyDate, '-', '')
David Gugick - SQL Server MVP
Quest Software