Hi everyone,
I'm a british developer and so my SQL Server is on a british computer. The
problem I'm having is that dates are being stored in the British format and
I desperately need them to be stored in the American format. The applications
I'm making are all for the American market and having the dates stored in
the db as british dates is causing all sorts of problems. The two main problems
is that my application sends american formatted dates to the application
(which the db freaks out at) and the database tries to send back british
dates (which my application freaks out at).
Can anyone tell me, is there a way to have the DB store the dates in the
American format? I was hoping that this could be set on a per database basis
because I do have a couple of databases that are for British clients.
If anyone can help I would be very very grateful...!
Thanks
Simon
Simon,
Contrary to what you may believe, SQL does not store dates with any
formatting.
This is taken from books online:
Values with the datetime data type are stored internally by Microsoft SQL
Server as two 4-byte integers.
The formatting you see is done by the client application.
When you insert a date into SQL, use a date that SQL can understand.
The norm is yyyymmdd, so today would be '20060215'.
When presenting dates, I would suggest formatting the date client side.
You can personalize formatting to the client's taste.
I support English and French clients here, so I format dates depending on
the client "local".
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:7c72785b1eb258c800620757197b@.news.microsoft.c om...
> Hi everyone,
> I'm a british developer and so my SQL Server is on a british computer. The
> problem I'm having is that dates are being stored in the British format
> and I desperately need them to be stored in the American format. The
> applications I'm making are all for the American market and having the
> dates stored in the db as british dates is causing all sorts of problems.
> The two main problems is that my application sends american formatted
> dates to the application (which the db freaks out at) and the database
> tries to send back british dates (which my application freaks out at).
> Can anyone tell me, is there a way to have the DB store the dates in the
> American format? I was hoping that this could be set on a per database
> basis because I do have a couple of databases that are for British
> clients.
> If anyone can help I would be very very grateful...!
> Thanks
> Simon
>
|||Hi Simon
If the dates are stored using the datetime datatype, they are not stored in
either the British or American format. They are stored in an internal,
unambiguous format that you never see, and are presented in whatever format
the client application requests.
Can you be specific about why you think they are being stored in a British
format?
You might want to take a look at the following topics in Books Online, and
then come back with more questions:
datetime datatype
Convert (use of a third parameter forces dates to be DISPLAYED in a chosen
format, it does not change how they are stored)
SET DATFORMAT (you can determine how you want strings to be interpreted as
dates, but again, it does not change how they are stored)
SET LANGUAGE (will set a default DATEFORMAT value as well as certain other
language options)
You might also search the SQL Server Magazine website for some articles I
wrote several years ago on dealing with datetime data in SQL Server.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:7c72785b1eb258c800620757197b@.news.microsoft.c om...
> Hi everyone,
> I'm a british developer and so my SQL Server is on a british computer. The
> problem I'm having is that dates are being stored in the British format
> and I desperately need them to be stored in the American format. The
> applications I'm making are all for the American market and having the
> dates stored in the db as british dates is causing all sorts of problems.
> The two main problems is that my application sends american formatted
> dates to the application (which the db freaks out at) and the database
> tries to send back british dates (which my application freaks out at).
> Can anyone tell me, is there a way to have the DB store the dates in the
> American format? I was hoping that this could be set on a per database
> basis because I do have a couple of databases that are for British
> clients.
> If anyone can help I would be very very grateful...!
> Thanks
> Simon
>
>
|||A longer elaboration, in addition to the other replies:
http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:7c72785b1eb258c800620757197b@.news.microsoft.c om...
> Hi everyone,
> I'm a british developer and so my SQL Server is on a british computer. The problem I'm having is
> that dates are being stored in the British format and I desperately need them to be stored in the
> American format. The applications I'm making are all for the American market and having the dates
> stored in the db as british dates is causing all sorts of problems. The two main problems is that
> my application sends american formatted dates to the application (which the db freaks out at) and
> the database tries to send back british dates (which my application freaks out at).
> Can anyone tell me, is there a way to have the DB store the dates in the American format? I was
> hoping that this could be set on a per database basis because I do have a couple of databases that
> are for British clients.
> If anyone can help I would be very very grateful...!
> Thanks
> Simon
>
|||Hi Simon,
I think the following commands work.You can get a variety of US date
'style's by changging the 3rd parameter.
SELECT CONVERT(char(11), getdate(),0)
SELECT CONVERT(char(10), getdate(),10)
SELECT CONVERT(char(10), getdate(),1)
SELECT CONVERT(char(10), getdate(),2)
BYE
|||Hi Simon,
I think the following commands work.You can get a variety of US date
'style's by changging the 3rd parameter.
SELECT CONVERT(char(11), getdate(),0)
SELECT CONVERT(char(10), getdate(),10)
SELECT CONVERT(char(10), getdate(),1)
SELECT CONVERT(char(10), getdate(),2)
BYE
|||Hi everyone,
I'm looking through the articles you suggested. Very interesting stuff.
Thank you all very much for your time - you've all been a big big help!
Thanks again
Simon
Showing posts with label computer. Show all posts
Showing posts with label computer. Show all posts
Sunday, March 25, 2012
Changing the default date format for a database
Hi everyone,
I'm a british developer and so my SQL Server is on a british computer. The
problem I'm having is that dates are being stored in the British format and
I desperately need them to be stored in the American format. The application
s
I'm making are all for the American market and having the dates stored in
the db as british dates is causing all sorts of problems. The two main probl
ems
is that my application sends american formatted dates to the application
(which the db freaks out at) and the database tries to send back british
dates (which my application freaks out at).
Can anyone tell me, is there a way to have the DB store the dates in the
American format? I was hoping that this could be set on a per database basis
because I do have a couple of databases that are for British clients.
If anyone can help I would be very very grateful...!
Thanks
SimonSimon,
Contrary to what you may believe, SQL does not store dates with any
formatting.
This is taken from books online:
Values with the datetime data type are stored internally by Microsoft SQL
Server as two 4-byte integers.
The formatting you see is done by the client application.
When you insert a date into SQL, use a date that SQL can understand.
The norm is yyyymmdd, so today would be '20060215'.
When presenting dates, I would suggest formatting the date client side.
You can personalize formatting to the client's taste.
I support English and French clients here, so I format dates depending on
the client "local".
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:7c72785b1eb258c800620757197b@.news.microsoft.com...
> Hi everyone,
> I'm a british developer and so my SQL Server is on a british computer. The
> problem I'm having is that dates are being stored in the British format
> and I desperately need them to be stored in the American format. The
> applications I'm making are all for the American market and having the
> dates stored in the db as british dates is causing all sorts of problems.
> The two main problems is that my application sends american formatted
> dates to the application (which the db freaks out at) and the database
> tries to send back british dates (which my application freaks out at).
> Can anyone tell me, is there a way to have the DB store the dates in the
> American format? I was hoping that this could be set on a per database
> basis because I do have a couple of databases that are for British
> clients.
> If anyone can help I would be very very grateful...!
> Thanks
> Simon
>|||Hi Simon
If the dates are stored using the datetime datatype, they are not stored in
either the British or American format. They are stored in an internal,
unambiguous format that you never see, and are presented in whatever format
the client application requests.
Can you be specific about why you think they are being stored in a British
format?
You might want to take a look at the following topics in Books Online, and
then come back with more questions:
datetime datatype
Convert (use of a third parameter forces dates to be DISPLAYED in a chosen
format, it does not change how they are stored)
SET DATFORMAT (you can determine how you want strings to be interpreted as
dates, but again, it does not change how they are stored)
SET LANGUAGE (will set a default DATEFORMAT value as well as certain other
language options)
You might also search the SQL Server Magazine website for some articles I
wrote several years ago on dealing with datetime data in SQL Server.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:7c72785b1eb258c800620757197b@.news.microsoft.com...
> Hi everyone,
> I'm a british developer and so my SQL Server is on a british computer. The
> problem I'm having is that dates are being stored in the British format
> and I desperately need them to be stored in the American format. The
> applications I'm making are all for the American market and having the
> dates stored in the db as british dates is causing all sorts of problems.
> The two main problems is that my application sends american formatted
> dates to the application (which the db freaks out at) and the database
> tries to send back british dates (which my application freaks out at).
> Can anyone tell me, is there a way to have the DB store the dates in the
> American format? I was hoping that this could be set on a per database
> basis because I do have a couple of databases that are for British
> clients.
> If anyone can help I would be very very grateful...!
> Thanks
> Simon
>
>|||A longer elaboration, in addition to the other replies:
http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:7c72785b1eb258c800620757197b@.news.microsoft.com...
> Hi everyone,
> I'm a british developer and so my SQL Server is on a british computer. The
problem I'm having is
> that dates are being stored in the British format and I desperately need t
hem to be stored in the
> American format. The applications I'm making are all for the American mark
et and having the dates
> stored in the db as british dates is causing all sorts of problems. The tw
o main problems is that
> my application sends american formatted dates to the application (which th
e db freaks out at) and
> the database tries to send back british dates (which my application freaks
out at).
> Can anyone tell me, is there a way to have the DB store the dates in the A
merican format? I was
> hoping that this could be set on a per database basis because I do have a
couple of databases that
> are for British clients.
> If anyone can help I would be very very grateful...!
> Thanks
> Simon
>|||Hi Simon,
I think the following commands work.You can get a variety of US date
'style's by changging the 3rd parameter.
SELECT CONVERT(char(11), getdate(),0)
SELECT CONVERT(char(10), getdate(),10)
SELECT CONVERT(char(10), getdate(),1)
SELECT CONVERT(char(10), getdate(),2)
BYE|||Hi Simon,
I think the following commands work.You can get a variety of US date
'style's by changging the 3rd parameter.
SELECT CONVERT(char(11), getdate(),0)
SELECT CONVERT(char(10), getdate(),10)
SELECT CONVERT(char(10), getdate(),1)
SELECT CONVERT(char(10), getdate(),2)
BYE|||Hi everyone,
I'm looking through the articles you suggested. Very interesting stuff.
Thank you all very much for your time - you've all been a big big help!
Thanks again
Simonsql
I'm a british developer and so my SQL Server is on a british computer. The
problem I'm having is that dates are being stored in the British format and
I desperately need them to be stored in the American format. The application
s
I'm making are all for the American market and having the dates stored in
the db as british dates is causing all sorts of problems. The two main probl
ems
is that my application sends american formatted dates to the application
(which the db freaks out at) and the database tries to send back british
dates (which my application freaks out at).
Can anyone tell me, is there a way to have the DB store the dates in the
American format? I was hoping that this could be set on a per database basis
because I do have a couple of databases that are for British clients.
If anyone can help I would be very very grateful...!
Thanks
SimonSimon,
Contrary to what you may believe, SQL does not store dates with any
formatting.
This is taken from books online:
Values with the datetime data type are stored internally by Microsoft SQL
Server as two 4-byte integers.
The formatting you see is done by the client application.
When you insert a date into SQL, use a date that SQL can understand.
The norm is yyyymmdd, so today would be '20060215'.
When presenting dates, I would suggest formatting the date client side.
You can personalize formatting to the client's taste.
I support English and French clients here, so I format dates depending on
the client "local".
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:7c72785b1eb258c800620757197b@.news.microsoft.com...
> Hi everyone,
> I'm a british developer and so my SQL Server is on a british computer. The
> problem I'm having is that dates are being stored in the British format
> and I desperately need them to be stored in the American format. The
> applications I'm making are all for the American market and having the
> dates stored in the db as british dates is causing all sorts of problems.
> The two main problems is that my application sends american formatted
> dates to the application (which the db freaks out at) and the database
> tries to send back british dates (which my application freaks out at).
> Can anyone tell me, is there a way to have the DB store the dates in the
> American format? I was hoping that this could be set on a per database
> basis because I do have a couple of databases that are for British
> clients.
> If anyone can help I would be very very grateful...!
> Thanks
> Simon
>|||Hi Simon
If the dates are stored using the datetime datatype, they are not stored in
either the British or American format. They are stored in an internal,
unambiguous format that you never see, and are presented in whatever format
the client application requests.
Can you be specific about why you think they are being stored in a British
format?
You might want to take a look at the following topics in Books Online, and
then come back with more questions:
datetime datatype
Convert (use of a third parameter forces dates to be DISPLAYED in a chosen
format, it does not change how they are stored)
SET DATFORMAT (you can determine how you want strings to be interpreted as
dates, but again, it does not change how they are stored)
SET LANGUAGE (will set a default DATEFORMAT value as well as certain other
language options)
You might also search the SQL Server Magazine website for some articles I
wrote several years ago on dealing with datetime data in SQL Server.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:7c72785b1eb258c800620757197b@.news.microsoft.com...
> Hi everyone,
> I'm a british developer and so my SQL Server is on a british computer. The
> problem I'm having is that dates are being stored in the British format
> and I desperately need them to be stored in the American format. The
> applications I'm making are all for the American market and having the
> dates stored in the db as british dates is causing all sorts of problems.
> The two main problems is that my application sends american formatted
> dates to the application (which the db freaks out at) and the database
> tries to send back british dates (which my application freaks out at).
> Can anyone tell me, is there a way to have the DB store the dates in the
> American format? I was hoping that this could be set on a per database
> basis because I do have a couple of databases that are for British
> clients.
> If anyone can help I would be very very grateful...!
> Thanks
> Simon
>
>|||A longer elaboration, in addition to the other replies:
http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:7c72785b1eb258c800620757197b@.news.microsoft.com...
> Hi everyone,
> I'm a british developer and so my SQL Server is on a british computer. The
problem I'm having is
> that dates are being stored in the British format and I desperately need t
hem to be stored in the
> American format. The applications I'm making are all for the American mark
et and having the dates
> stored in the db as british dates is causing all sorts of problems. The tw
o main problems is that
> my application sends american formatted dates to the application (which th
e db freaks out at) and
> the database tries to send back british dates (which my application freaks
out at).
> Can anyone tell me, is there a way to have the DB store the dates in the A
merican format? I was
> hoping that this could be set on a per database basis because I do have a
couple of databases that
> are for British clients.
> If anyone can help I would be very very grateful...!
> Thanks
> Simon
>|||Hi Simon,
I think the following commands work.You can get a variety of US date
'style's by changging the 3rd parameter.
SELECT CONVERT(char(11), getdate(),0)
SELECT CONVERT(char(10), getdate(),10)
SELECT CONVERT(char(10), getdate(),1)
SELECT CONVERT(char(10), getdate(),2)
BYE|||Hi Simon,
I think the following commands work.You can get a variety of US date
'style's by changging the 3rd parameter.
SELECT CONVERT(char(11), getdate(),0)
SELECT CONVERT(char(10), getdate(),10)
SELECT CONVERT(char(10), getdate(),1)
SELECT CONVERT(char(10), getdate(),2)
BYE|||Hi everyone,
I'm looking through the articles you suggested. Very interesting stuff.
Thank you all very much for your time - you've all been a big big help!
Thanks again
Simonsql
Changing the default date format for a database
Hi everyone,
I'm a british developer and so my SQL Server is on a british computer. The
problem I'm having is that dates are being stored in the British format and
I desperately need them to be stored in the American format. The applications
I'm making are all for the American market and having the dates stored in
the db as british dates is causing all sorts of problems. The two main problems
is that my application sends american formatted dates to the application
(which the db freaks out at) and the database tries to send back british
dates (which my application freaks out at).
Can anyone tell me, is there a way to have the DB store the dates in the
American format? I was hoping that this could be set on a per database basis
because I do have a couple of databases that are for British clients.
If anyone can help I would be very very grateful...!
Thanks
SimonHi Simon
If the dates are stored using the datetime datatype, they are not stored in
either the British or American format. They are stored in an internal,
unambiguous format that you never see, and are presented in whatever format
the client application requests.
Can you be specific about why you think they are being stored in a British
format?
You might want to take a look at the following topics in Books Online, and
then come back with more questions:
datetime datatype
Convert (use of a third parameter forces dates to be DISPLAYED in a chosen
format, it does not change how they are stored)
SET DATFORMAT (you can determine how you want strings to be interpreted as
dates, but again, it does not change how they are stored)
SET LANGUAGE (will set a default DATEFORMAT value as well as certain other
language options)
You might also search the SQL Server Magazine website for some articles I
wrote several years ago on dealing with datetime data in SQL Server.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:7c72785b1eb258c800620757197b@.news.microsoft.com...
> Hi everyone,
> I'm a british developer and so my SQL Server is on a british computer. The
> problem I'm having is that dates are being stored in the British format
> and I desperately need them to be stored in the American format. The
> applications I'm making are all for the American market and having the
> dates stored in the db as british dates is causing all sorts of problems.
> The two main problems is that my application sends american formatted
> dates to the application (which the db freaks out at) and the database
> tries to send back british dates (which my application freaks out at).
> Can anyone tell me, is there a way to have the DB store the dates in the
> American format? I was hoping that this could be set on a per database
> basis because I do have a couple of databases that are for British
> clients.
> If anyone can help I would be very very grateful...!
> Thanks
> Simon
>
>|||Simon,
Contrary to what you may believe, SQL does not store dates with any
formatting.
This is taken from books online:
Values with the datetime data type are stored internally by Microsoft SQL
Server as two 4-byte integers.
The formatting you see is done by the client application.
When you insert a date into SQL, use a date that SQL can understand.
The norm is yyyymmdd, so today would be '20060215'.
When presenting dates, I would suggest formatting the date client side.
You can personalize formatting to the client's taste.
I support English and French clients here, so I format dates depending on
the client "local".
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:7c72785b1eb258c800620757197b@.news.microsoft.com...
> Hi everyone,
> I'm a british developer and so my SQL Server is on a british computer. The
> problem I'm having is that dates are being stored in the British format
> and I desperately need them to be stored in the American format. The
> applications I'm making are all for the American market and having the
> dates stored in the db as british dates is causing all sorts of problems.
> The two main problems is that my application sends american formatted
> dates to the application (which the db freaks out at) and the database
> tries to send back british dates (which my application freaks out at).
> Can anyone tell me, is there a way to have the DB store the dates in the
> American format? I was hoping that this could be set on a per database
> basis because I do have a couple of databases that are for British
> clients.
> If anyone can help I would be very very grateful...!
> Thanks
> Simon
>|||A longer elaboration, in addition to the other replies:
http://www.karaszi.com/SQLServer/info_datetime.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:7c72785b1eb258c800620757197b@.news.microsoft.com...
> Hi everyone,
> I'm a british developer and so my SQL Server is on a british computer. The problem I'm having is
> that dates are being stored in the British format and I desperately need them to be stored in the
> American format. The applications I'm making are all for the American market and having the dates
> stored in the db as british dates is causing all sorts of problems. The two main problems is that
> my application sends american formatted dates to the application (which the db freaks out at) and
> the database tries to send back british dates (which my application freaks out at).
> Can anyone tell me, is there a way to have the DB store the dates in the American format? I was
> hoping that this could be set on a per database basis because I do have a couple of databases that
> are for British clients.
> If anyone can help I would be very very grateful...!
> Thanks
> Simon
>|||Hi Simon,
I think the following commands work.You can get a variety of US date
'style's by changging the 3rd parameter.
SELECT CONVERT(char(11), getdate(),0)
SELECT CONVERT(char(10), getdate(),10)
SELECT CONVERT(char(10), getdate(),1)
SELECT CONVERT(char(10), getdate(),2)
BYE|||Hi Simon,
I think the following commands work.You can get a variety of US date
'style's by changging the 3rd parameter.
SELECT CONVERT(char(11), getdate(),0)
SELECT CONVERT(char(10), getdate(),10)
SELECT CONVERT(char(10), getdate(),1)
SELECT CONVERT(char(10), getdate(),2)
BYE|||Hi everyone,
I'm looking through the articles you suggested. Very interesting stuff.
Thank you all very much for your time - you've all been a big big help!
Thanks again
Simon
I'm a british developer and so my SQL Server is on a british computer. The
problem I'm having is that dates are being stored in the British format and
I desperately need them to be stored in the American format. The applications
I'm making are all for the American market and having the dates stored in
the db as british dates is causing all sorts of problems. The two main problems
is that my application sends american formatted dates to the application
(which the db freaks out at) and the database tries to send back british
dates (which my application freaks out at).
Can anyone tell me, is there a way to have the DB store the dates in the
American format? I was hoping that this could be set on a per database basis
because I do have a couple of databases that are for British clients.
If anyone can help I would be very very grateful...!
Thanks
SimonHi Simon
If the dates are stored using the datetime datatype, they are not stored in
either the British or American format. They are stored in an internal,
unambiguous format that you never see, and are presented in whatever format
the client application requests.
Can you be specific about why you think they are being stored in a British
format?
You might want to take a look at the following topics in Books Online, and
then come back with more questions:
datetime datatype
Convert (use of a third parameter forces dates to be DISPLAYED in a chosen
format, it does not change how they are stored)
SET DATFORMAT (you can determine how you want strings to be interpreted as
dates, but again, it does not change how they are stored)
SET LANGUAGE (will set a default DATEFORMAT value as well as certain other
language options)
You might also search the SQL Server Magazine website for some articles I
wrote several years ago on dealing with datetime data in SQL Server.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:7c72785b1eb258c800620757197b@.news.microsoft.com...
> Hi everyone,
> I'm a british developer and so my SQL Server is on a british computer. The
> problem I'm having is that dates are being stored in the British format
> and I desperately need them to be stored in the American format. The
> applications I'm making are all for the American market and having the
> dates stored in the db as british dates is causing all sorts of problems.
> The two main problems is that my application sends american formatted
> dates to the application (which the db freaks out at) and the database
> tries to send back british dates (which my application freaks out at).
> Can anyone tell me, is there a way to have the DB store the dates in the
> American format? I was hoping that this could be set on a per database
> basis because I do have a couple of databases that are for British
> clients.
> If anyone can help I would be very very grateful...!
> Thanks
> Simon
>
>|||Simon,
Contrary to what you may believe, SQL does not store dates with any
formatting.
This is taken from books online:
Values with the datetime data type are stored internally by Microsoft SQL
Server as two 4-byte integers.
The formatting you see is done by the client application.
When you insert a date into SQL, use a date that SQL can understand.
The norm is yyyymmdd, so today would be '20060215'.
When presenting dates, I would suggest formatting the date client side.
You can personalize formatting to the client's taste.
I support English and French clients here, so I format dates depending on
the client "local".
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:7c72785b1eb258c800620757197b@.news.microsoft.com...
> Hi everyone,
> I'm a british developer and so my SQL Server is on a british computer. The
> problem I'm having is that dates are being stored in the British format
> and I desperately need them to be stored in the American format. The
> applications I'm making are all for the American market and having the
> dates stored in the db as british dates is causing all sorts of problems.
> The two main problems is that my application sends american formatted
> dates to the application (which the db freaks out at) and the database
> tries to send back british dates (which my application freaks out at).
> Can anyone tell me, is there a way to have the DB store the dates in the
> American format? I was hoping that this could be set on a per database
> basis because I do have a couple of databases that are for British
> clients.
> If anyone can help I would be very very grateful...!
> Thanks
> Simon
>|||A longer elaboration, in addition to the other replies:
http://www.karaszi.com/SQLServer/info_datetime.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:7c72785b1eb258c800620757197b@.news.microsoft.com...
> Hi everyone,
> I'm a british developer and so my SQL Server is on a british computer. The problem I'm having is
> that dates are being stored in the British format and I desperately need them to be stored in the
> American format. The applications I'm making are all for the American market and having the dates
> stored in the db as british dates is causing all sorts of problems. The two main problems is that
> my application sends american formatted dates to the application (which the db freaks out at) and
> the database tries to send back british dates (which my application freaks out at).
> Can anyone tell me, is there a way to have the DB store the dates in the American format? I was
> hoping that this could be set on a per database basis because I do have a couple of databases that
> are for British clients.
> If anyone can help I would be very very grateful...!
> Thanks
> Simon
>|||Hi Simon,
I think the following commands work.You can get a variety of US date
'style's by changging the 3rd parameter.
SELECT CONVERT(char(11), getdate(),0)
SELECT CONVERT(char(10), getdate(),10)
SELECT CONVERT(char(10), getdate(),1)
SELECT CONVERT(char(10), getdate(),2)
BYE|||Hi Simon,
I think the following commands work.You can get a variety of US date
'style's by changging the 3rd parameter.
SELECT CONVERT(char(11), getdate(),0)
SELECT CONVERT(char(10), getdate(),10)
SELECT CONVERT(char(10), getdate(),1)
SELECT CONVERT(char(10), getdate(),2)
BYE|||Hi everyone,
I'm looking through the articles you suggested. Very interesting stuff.
Thank you all very much for your time - you've all been a big big help!
Thanks again
Simon
Thursday, March 22, 2012
Changing the (local) SQL Server registration name
Hi,
When we install SQL server, it creates a (local) SQL
Server registration. How can we change this name from
(local) to computer's name?
Thank you.You should be able to access the server via (local), servername or ..
If you are talking about enterprise manager then just delete the registration and add one with the servername.
Check @.@.server_name you may have to add it via sp_addserver.
"anonymous@.discussions.microsoft.com" wrote:
> Hi,
> When we install SQL server, it creates a (local) SQL
> Server registration. How can we change this name from
> (local) to computer's name?
> Thank you.
>|||I tried deleting the (local) registration but it created
a lot of problem. After deleting the (local) registration
I wasn't able to add it back. SQLAgent service wasn't
showing up in the service list.
>--Original Message--
>You should be able to access the server via (local),
servername or ..
>If you are talking about enterprise manager then just
delete the registration and add one with the servername.
>Check @.@.server_name you may have to add it via
sp_addserver.
>"anonymous@.discussions.microsoft.com" wrote:
>> Hi,
>> When we install SQL server, it creates a (local) SQL
>> Server registration. How can we change this name from
>> (local) to computer's name?
>> Thank you.
>.
>|||?
Are you talking about enterprise manager?
It's just a client and deleting a registration won't affect services running on the m/c.
The agent service is separate from the server and may have not been started.
See if the mssqlserver service is running - if so you should be able to regidter in enterprise manager.
"anonymous@.discussions.microsoft.com" wrote:
> I tried deleting the (local) registration but it created
> a lot of problem. After deleting the (local) registration
> I wasn't able to add it back. SQLAgent service wasn't
> showing up in the service list.
>
> >--Original Message--
> >You should be able to access the server via (local),
> servername or ..
> >If you are talking about enterprise manager then just
> delete the registration and add one with the servername.
> >
> >Check @.@.server_name you may have to add it via
> sp_addserver.
> >
> >"anonymous@.discussions.microsoft.com" wrote:
> >
> >> Hi,
> >>
> >> When we install SQL server, it creates a (local) SQL
> >> Server registration. How can we change this name from
> >> (local) to computer's name?
> >>
> >> Thank you.
> >>
> >.
> >
>
When we install SQL server, it creates a (local) SQL
Server registration. How can we change this name from
(local) to computer's name?
Thank you.You should be able to access the server via (local), servername or ..
If you are talking about enterprise manager then just delete the registration and add one with the servername.
Check @.@.server_name you may have to add it via sp_addserver.
"anonymous@.discussions.microsoft.com" wrote:
> Hi,
> When we install SQL server, it creates a (local) SQL
> Server registration. How can we change this name from
> (local) to computer's name?
> Thank you.
>|||I tried deleting the (local) registration but it created
a lot of problem. After deleting the (local) registration
I wasn't able to add it back. SQLAgent service wasn't
showing up in the service list.
>--Original Message--
>You should be able to access the server via (local),
servername or ..
>If you are talking about enterprise manager then just
delete the registration and add one with the servername.
>Check @.@.server_name you may have to add it via
sp_addserver.
>"anonymous@.discussions.microsoft.com" wrote:
>> Hi,
>> When we install SQL server, it creates a (local) SQL
>> Server registration. How can we change this name from
>> (local) to computer's name?
>> Thank you.
>.
>|||?
Are you talking about enterprise manager?
It's just a client and deleting a registration won't affect services running on the m/c.
The agent service is separate from the server and may have not been started.
See if the mssqlserver service is running - if so you should be able to regidter in enterprise manager.
"anonymous@.discussions.microsoft.com" wrote:
> I tried deleting the (local) registration but it created
> a lot of problem. After deleting the (local) registration
> I wasn't able to add it back. SQLAgent service wasn't
> showing up in the service list.
>
> >--Original Message--
> >You should be able to access the server via (local),
> servername or ..
> >If you are talking about enterprise manager then just
> delete the registration and add one with the servername.
> >
> >Check @.@.server_name you may have to add it via
> sp_addserver.
> >
> >"anonymous@.discussions.microsoft.com" wrote:
> >
> >> Hi,
> >>
> >> When we install SQL server, it creates a (local) SQL
> >> Server registration. How can we change this name from
> >> (local) to computer's name?
> >>
> >> Thank you.
> >>
> >.
> >
>
Changing the (local) SQL Server registration name
Hi,
When we install SQL server, it creates a (local) SQL
Server registration. How can we change this name from
(local) to computer's name?
Thank you.You should be able to access the server via (local), servername or ..
If you are talking about enterprise manager then just delete the registratio
n and add one with the servername.
Check @.@.server_name you may have to add it via sp_addserver.
"anonymous@.discussions.microsoft.com" wrote:
> Hi,
> When we install SQL server, it creates a (local) SQL
> Server registration. How can we change this name from
> (local) to computer's name?
> Thank you.
>|||I tried deleting the (local) registration but it created
a lot of problem. After deleting the (local) registration
I wasn't able to add it back. SQLAgent service wasn't
showing up in the service list.
>--Original Message--
>You should be able to access the server via (local),
servername or ..
>If you are talking about enterprise manager then just
delete the registration and add one with the servername.
>Check @.@.server_name you may have to add it via
sp_addserver.
>"anonymous@.discussions.microsoft.com" wrote:
>
>.
>|||?
Are you talking about enterprise manager?
It's just a client and deleting a registration won't affect services running
on the m/c.
The agent service is separate from the server and may have not been started.
See if the mssqlserver service is running - if so you should be able to regi
dter in enterprise manager.
"anonymous@.discussions.microsoft.com" wrote:
> I tried deleting the (local) registration but it created
> a lot of problem. After deleting the (local) registration
> I wasn't able to add it back. SQLAgent service wasn't
> showing up in the service list.
>
> servername or ..
> delete the registration and add one with the servername.
> sp_addserver.
>sql
When we install SQL server, it creates a (local) SQL
Server registration. How can we change this name from
(local) to computer's name?
Thank you.You should be able to access the server via (local), servername or ..
If you are talking about enterprise manager then just delete the registratio
n and add one with the servername.
Check @.@.server_name you may have to add it via sp_addserver.
"anonymous@.discussions.microsoft.com" wrote:
> Hi,
> When we install SQL server, it creates a (local) SQL
> Server registration. How can we change this name from
> (local) to computer's name?
> Thank you.
>|||I tried deleting the (local) registration but it created
a lot of problem. After deleting the (local) registration
I wasn't able to add it back. SQLAgent service wasn't
showing up in the service list.
>--Original Message--
>You should be able to access the server via (local),
servername or ..
>If you are talking about enterprise manager then just
delete the registration and add one with the servername.
>Check @.@.server_name you may have to add it via
sp_addserver.
>"anonymous@.discussions.microsoft.com" wrote:
>
>.
>|||?
Are you talking about enterprise manager?
It's just a client and deleting a registration won't affect services running
on the m/c.
The agent service is separate from the server and may have not been started.
See if the mssqlserver service is running - if so you should be able to regi
dter in enterprise manager.
"anonymous@.discussions.microsoft.com" wrote:
> I tried deleting the (local) registration but it created
> a lot of problem. After deleting the (local) registration
> I wasn't able to add it back. SQLAgent service wasn't
> showing up in the service list.
>
> servername or ..
> delete the registration and add one with the servername.
> sp_addserver.
>sql
Monday, March 19, 2012
Changing SQL Server Logon Config.
Hello,
When I installed MS SQL Server 2000 on our client's computer I installed it under a local account using Windows Authentication as the server logon and am now accessing it using a network login (remoting into the same box). The result is that if I change the password for the local Windows account and reboot the server, SQL server cannot be restarted; I get an error:
A connection could not be established to (LOCAL)
Reason: SQL SERVER does not exist or access denied.
ConnectionOpen (Connect())..
Please verify SQL Server is running and check your SQL Server registration properties and try again.
I've changed the registration properties to use SQL Server Authenticaiton instead of Windows to no avail. Whichever connection option I use the server will not start unless I change the local account's password back to what it was when I installed. This works, but our client wants to have that local account's password changed for security/peace of mind. Any help or advice would be appreciated here :)
Thank youI always use a dedicated domain account for many reasons. exchange integration, access to file servers etc... and you should set that domain account to never have a password that never expires.|||... and you should set that domain account to never have a password that never expires.I suspect that you might have "over nevered" here. I would want the domain account to have a password that never expired (or I'd create a single application to change the service passwords and the AD password, all in one swell foop).
-PatP|||I'm afraid I don't quite get what you're saying here. The domain login is to stay the same in this case. It is the local account that the sql server installation is tied to that must have its password altered. The problem is that upon altering the password in Windows, SQL server will not start up the instance, giving that invalid logon message. Sorry to say that I'm still at a loss here. :confused:
When I installed MS SQL Server 2000 on our client's computer I installed it under a local account using Windows Authentication as the server logon and am now accessing it using a network login (remoting into the same box). The result is that if I change the password for the local Windows account and reboot the server, SQL server cannot be restarted; I get an error:
A connection could not be established to (LOCAL)
Reason: SQL SERVER does not exist or access denied.
ConnectionOpen (Connect())..
Please verify SQL Server is running and check your SQL Server registration properties and try again.
I've changed the registration properties to use SQL Server Authenticaiton instead of Windows to no avail. Whichever connection option I use the server will not start unless I change the local account's password back to what it was when I installed. This works, but our client wants to have that local account's password changed for security/peace of mind. Any help or advice would be appreciated here :)
Thank youI always use a dedicated domain account for many reasons. exchange integration, access to file servers etc... and you should set that domain account to never have a password that never expires.|||... and you should set that domain account to never have a password that never expires.I suspect that you might have "over nevered" here. I would want the domain account to have a password that never expired (or I'd create a single application to change the service passwords and the AD password, all in one swell foop).
-PatP|||I'm afraid I don't quite get what you're saying here. The domain login is to stay the same in this case. It is the local account that the sql server installation is tied to that must have its password altered. The problem is that upon altering the password in Windows, SQL server will not start up the instance, giving that invalid logon message. Sorry to say that I'm still at a loss here. :confused:
Thursday, March 8, 2012
Changing provider from Access to SQL Server
Hello,
I have developped an app that previously used locally stored Access database on my computer and now I want to switch to SQL server on the provider's machine. However I'm not sure exactly how to do this. I didn't find any help of this sort that would help me with this. Any ideas?
Thanks a lot!
Jan
I think I need to specify my problem a bit better. I managed to make my ASPX pages to connect to the SQL database (instead of to Access file), but the problem is with features like membership or web-parts. Those seem to store some information in the database, however on the new server they don't seem to know how to connect to the SQL server.
I thought this is set-up in the web.config file, but eve though I do have SQL server connection strings there it doesn't work for me.
I tried adding a new provider using the VS2005 Website/Asp.Net Configuration wizzard, but I don't have the option to Add provider there :-(
Thanks a lot for any help.
Jan
I have developped an app that previously used locally stored Access database on my computer and now I want to switch to SQL server on the provider's machine. However I'm not sure exactly how to do this. I didn't find any help of this sort that would help me with this. Any ideas?
Thanks a lot!
Jan
You need to change the using or imports statement from System.Data.OleDb to System.Data.SqlClient
Change all instances of OleDbConnection, OleDbCommand and OleDbDataAdapter to (respectively) SqlConnection, SqlCommand and SqlDataAdapter.
OleDb uses positional parameters, which are represented by ? characters in the SQL Strings, SqlClient uses named parameters.
Obviously, your connection string needs to be different. www.connectionstrings.com has examples, as does the MSDN documentation.
That should get you started.
I think I need to specify my problem a bit better. I managed to make my ASPX pages to connect to the SQL database (instead of to Access file), but the problem is with features like membership or web-parts. Those seem to store some information in the database, however on the new server they don't seem to know how to connect to the SQL server.
I thought this is set-up in the web.config file, but eve though I do have SQL server connection strings there it doesn't work for me.
I tried adding a new provider using the VS2005 Website/Asp.Net Configuration wizzard, but I don't have the option to Add provider there :-(
Thanks a lot for any help.
Jan
Tuesday, February 14, 2012
Changing default local server
Hi!
How can i change default assignment of local SQL server instance.
I have two instances installed on my computer, one - SQL personal 2000,
second - SQL developer 2000. I have to install Reporting Services on SQL
Developer, but first, i need to install Service Pack 3a on SQL Delveoper. Th
e
default instance of SQL server is - Personal (I see it as (local) in
enterprise manager). Even, if I'll stop SQL Personal server, I still would
not be able to install Reporting Services on SQL Developer. I always get
error - SQL Server Service Pack 3a not installed.
Please, tell me, how can I solve this problem !
Thanks!
Igor.Igor,
Why not install SP3a on each SQL Server? Or better yet SP4. For greater
control over the RS installation see the 'Installing Reporting Services from
the Command Line' topic in the Reporting Services Books Online. Also be
sure to download the latest version of RS BOL at
http://www.microsoft.com/downloads/...&DisplayLang=en
HTH
Jerry
"Igor" <Igor@.discussions.microsoft.com> wrote in message
news:E7B65CD3-64FF-4E84-9D6B-003F98E4EFAD@.microsoft.com...
> Hi!
> How can i change default assignment of local SQL server instance.
> I have two instances installed on my computer, one - SQL personal 2000,
> second - SQL developer 2000. I have to install Reporting Services on SQL
> Developer, but first, i need to install Service Pack 3a on SQL Delveoper.
> The
> default instance of SQL server is - Personal (I see it as (local) in
> enterprise manager). Even, if I'll stop SQL Personal server, I still would
> not be able to install Reporting Services on SQL Developer. I always get
> error - SQL Server Service Pack 3a not installed.
> Please, tell me, how can I solve this problem !
> Thanks!
> Igor.|||There is a problem. I do not know how to force to install Service Pack for
the SQL Developer server.
The problem is that the SQL Personal already was installed in computer, and
the SQL Developer has been installed additionaly. For Example, the instance
of SQL Personal is "SQLSRV1" (the same as computer name), in enterprise
manager i see it as (local), and instance of SQL Developer server is
"SQLSRV1\NEW_DEV". Here is a problem ! The Service pack takes only SQL
Personal server and do not see SQL Developer, even if I'll stop SQL Personal
server and SQL Developer is running.
The question is: how to force Service Pack to be installed on SQL Developer
server in this configuration.
I already install Reporting services on other computers with one instance of
SQL server and it works perfectly !
Is there any way to solve this ?
Thanks!
Igor.
"Jerry Spivey" wrote:
> Igor,
> Why not install SP3a on each SQL Server? Or better yet SP4. For greater
> control over the RS installation see the 'Installing Reporting Services fr
om
> the Command Line' topic in the Reporting Services Books Online. Also be
> sure to download the latest version of RS BOL at
> http://www.microsoft.com/downloads/...&DisplayLang=en
> HTH
> Jerry
> "Igor" <Igor@.discussions.microsoft.com> wrote in message
> news:E7B65CD3-64FF-4E84-9D6B-003F98E4EFAD@.microsoft.com...
>
>
How can i change default assignment of local SQL server instance.
I have two instances installed on my computer, one - SQL personal 2000,
second - SQL developer 2000. I have to install Reporting Services on SQL
Developer, but first, i need to install Service Pack 3a on SQL Delveoper. Th
e
default instance of SQL server is - Personal (I see it as (local) in
enterprise manager). Even, if I'll stop SQL Personal server, I still would
not be able to install Reporting Services on SQL Developer. I always get
error - SQL Server Service Pack 3a not installed.
Please, tell me, how can I solve this problem !
Thanks!
Igor.Igor,
Why not install SP3a on each SQL Server? Or better yet SP4. For greater
control over the RS installation see the 'Installing Reporting Services from
the Command Line' topic in the Reporting Services Books Online. Also be
sure to download the latest version of RS BOL at
http://www.microsoft.com/downloads/...&DisplayLang=en
HTH
Jerry
"Igor" <Igor@.discussions.microsoft.com> wrote in message
news:E7B65CD3-64FF-4E84-9D6B-003F98E4EFAD@.microsoft.com...
> Hi!
> How can i change default assignment of local SQL server instance.
> I have two instances installed on my computer, one - SQL personal 2000,
> second - SQL developer 2000. I have to install Reporting Services on SQL
> Developer, but first, i need to install Service Pack 3a on SQL Delveoper.
> The
> default instance of SQL server is - Personal (I see it as (local) in
> enterprise manager). Even, if I'll stop SQL Personal server, I still would
> not be able to install Reporting Services on SQL Developer. I always get
> error - SQL Server Service Pack 3a not installed.
> Please, tell me, how can I solve this problem !
> Thanks!
> Igor.|||There is a problem. I do not know how to force to install Service Pack for
the SQL Developer server.
The problem is that the SQL Personal already was installed in computer, and
the SQL Developer has been installed additionaly. For Example, the instance
of SQL Personal is "SQLSRV1" (the same as computer name), in enterprise
manager i see it as (local), and instance of SQL Developer server is
"SQLSRV1\NEW_DEV". Here is a problem ! The Service pack takes only SQL
Personal server and do not see SQL Developer, even if I'll stop SQL Personal
server and SQL Developer is running.
The question is: how to force Service Pack to be installed on SQL Developer
server in this configuration.
I already install Reporting services on other computers with one instance of
SQL server and it works perfectly !
Is there any way to solve this ?
Thanks!
Igor.
"Jerry Spivey" wrote:
> Igor,
> Why not install SP3a on each SQL Server? Or better yet SP4. For greater
> control over the RS installation see the 'Installing Reporting Services fr
om
> the Command Line' topic in the Reporting Services Books Online. Also be
> sure to download the latest version of RS BOL at
> http://www.microsoft.com/downloads/...&DisplayLang=en
> HTH
> Jerry
> "Igor" <Igor@.discussions.microsoft.com> wrote in message
> news:E7B65CD3-64FF-4E84-9D6B-003F98E4EFAD@.microsoft.com...
>
>
Changing default local server
Hi!
How can i change default assignment of local SQL server instance.
I have two instances installed on my computer, one - SQL personal 2000,
second - SQL developer 2000. I have to install Reporting Services on SQL
Developer, but first, i need to install Service Pack 3a on SQL Delveoper. The
default instance of SQL server is - Personal (I see it as (local) in
enterprise manager). Even, if I'll stop SQL Personal server, I still would
not be able to install Reporting Services on SQL Developer. I always get
error - SQL Server Service Pack 3a not installed.
Please, tell me, how can I solve this problem !
Thanks!
Igor.
Igor,
Why not install SP3a on each SQL Server? Or better yet SP4. For greater
control over the RS installation see the 'Installing Reporting Services from
the Command Line' topic in the Reporting Services Books Online. Also be
sure to download the latest version of RS BOL at
http://www.microsoft.com/downloads/d...DisplayLang=en
HTH
Jerry
"Igor" <Igor@.discussions.microsoft.com> wrote in message
news:E7B65CD3-64FF-4E84-9D6B-003F98E4EFAD@.microsoft.com...
> Hi!
> How can i change default assignment of local SQL server instance.
> I have two instances installed on my computer, one - SQL personal 2000,
> second - SQL developer 2000. I have to install Reporting Services on SQL
> Developer, but first, i need to install Service Pack 3a on SQL Delveoper.
> The
> default instance of SQL server is - Personal (I see it as (local) in
> enterprise manager). Even, if I'll stop SQL Personal server, I still would
> not be able to install Reporting Services on SQL Developer. I always get
> error - SQL Server Service Pack 3a not installed.
> Please, tell me, how can I solve this problem !
> Thanks!
> Igor.
|||There is a problem. I do not know how to force to install Service Pack for
the SQL Developer server.
The problem is that the SQL Personal already was installed in computer, and
the SQL Developer has been installed additionaly. For Example, the instance
of SQL Personal is "SQLSRV1" (the same as computer name), in enterprise
manager i see it as (local), and instance of SQL Developer server is
"SQLSRV1\NEW_DEV". Here is a problem ! The Service pack takes only SQL
Personal server and do not see SQL Developer, even if I'll stop SQL Personal
server and SQL Developer is running.
The question is: how to force Service Pack to be installed on SQL Developer
server in this configuration.
I already install Reporting services on other computers with one instance of
SQL server and it works perfectly !
Is there any way to solve this ?
Thanks!
Igor.
"Jerry Spivey" wrote:
> Igor,
> Why not install SP3a on each SQL Server? Or better yet SP4. For greater
> control over the RS installation see the 'Installing Reporting Services from
> the Command Line' topic in the Reporting Services Books Online. Also be
> sure to download the latest version of RS BOL at
> http://www.microsoft.com/downloads/d...DisplayLang=en
> HTH
> Jerry
> "Igor" <Igor@.discussions.microsoft.com> wrote in message
> news:E7B65CD3-64FF-4E84-9D6B-003F98E4EFAD@.microsoft.com...
>
>
How can i change default assignment of local SQL server instance.
I have two instances installed on my computer, one - SQL personal 2000,
second - SQL developer 2000. I have to install Reporting Services on SQL
Developer, but first, i need to install Service Pack 3a on SQL Delveoper. The
default instance of SQL server is - Personal (I see it as (local) in
enterprise manager). Even, if I'll stop SQL Personal server, I still would
not be able to install Reporting Services on SQL Developer. I always get
error - SQL Server Service Pack 3a not installed.
Please, tell me, how can I solve this problem !
Thanks!
Igor.
Igor,
Why not install SP3a on each SQL Server? Or better yet SP4. For greater
control over the RS installation see the 'Installing Reporting Services from
the Command Line' topic in the Reporting Services Books Online. Also be
sure to download the latest version of RS BOL at
http://www.microsoft.com/downloads/d...DisplayLang=en
HTH
Jerry
"Igor" <Igor@.discussions.microsoft.com> wrote in message
news:E7B65CD3-64FF-4E84-9D6B-003F98E4EFAD@.microsoft.com...
> Hi!
> How can i change default assignment of local SQL server instance.
> I have two instances installed on my computer, one - SQL personal 2000,
> second - SQL developer 2000. I have to install Reporting Services on SQL
> Developer, but first, i need to install Service Pack 3a on SQL Delveoper.
> The
> default instance of SQL server is - Personal (I see it as (local) in
> enterprise manager). Even, if I'll stop SQL Personal server, I still would
> not be able to install Reporting Services on SQL Developer. I always get
> error - SQL Server Service Pack 3a not installed.
> Please, tell me, how can I solve this problem !
> Thanks!
> Igor.
|||There is a problem. I do not know how to force to install Service Pack for
the SQL Developer server.
The problem is that the SQL Personal already was installed in computer, and
the SQL Developer has been installed additionaly. For Example, the instance
of SQL Personal is "SQLSRV1" (the same as computer name), in enterprise
manager i see it as (local), and instance of SQL Developer server is
"SQLSRV1\NEW_DEV". Here is a problem ! The Service pack takes only SQL
Personal server and do not see SQL Developer, even if I'll stop SQL Personal
server and SQL Developer is running.
The question is: how to force Service Pack to be installed on SQL Developer
server in this configuration.
I already install Reporting services on other computers with one instance of
SQL server and it works perfectly !
Is there any way to solve this ?
Thanks!
Igor.
"Jerry Spivey" wrote:
> Igor,
> Why not install SP3a on each SQL Server? Or better yet SP4. For greater
> control over the RS installation see the 'Installing Reporting Services from
> the Command Line' topic in the Reporting Services Books Online. Also be
> sure to download the latest version of RS BOL at
> http://www.microsoft.com/downloads/d...DisplayLang=en
> HTH
> Jerry
> "Igor" <Igor@.discussions.microsoft.com> wrote in message
> news:E7B65CD3-64FF-4E84-9D6B-003F98E4EFAD@.microsoft.com...
>
>
Changing default local server
Hi!
How can i change default assignment of local SQL server instance.
I have two instances installed on my computer, one - SQL personal 2000,
second - SQL developer 2000. I have to install Reporting Services on SQL
Developer, but first, i need to install Service Pack 3a on SQL Delveoper. The
default instance of SQL server is - Personal (I see it as (local) in
enterprise manager). Even, if I'll stop SQL Personal server, I still would
not be able to install Reporting Services on SQL Developer. I always get
error - SQL Server Service Pack 3a not installed.
Please, tell me, how can I solve this problem !
Thanks!
Igor.Igor,
Why not install SP3a on each SQL Server? Or better yet SP4. For greater
control over the RS installation see the 'Installing Reporting Services from
the Command Line' topic in the Reporting Services Books Online. Also be
sure to download the latest version of RS BOL at
http://www.microsoft.com/downloads/details.aspx?FamilyID=5e550d73-8f35-435e-bb71-c8573a1cdbdb&DisplayLang=en
HTH
Jerry
"Igor" <Igor@.discussions.microsoft.com> wrote in message
news:E7B65CD3-64FF-4E84-9D6B-003F98E4EFAD@.microsoft.com...
> Hi!
> How can i change default assignment of local SQL server instance.
> I have two instances installed on my computer, one - SQL personal 2000,
> second - SQL developer 2000. I have to install Reporting Services on SQL
> Developer, but first, i need to install Service Pack 3a on SQL Delveoper.
> The
> default instance of SQL server is - Personal (I see it as (local) in
> enterprise manager). Even, if I'll stop SQL Personal server, I still would
> not be able to install Reporting Services on SQL Developer. I always get
> error - SQL Server Service Pack 3a not installed.
> Please, tell me, how can I solve this problem !
> Thanks!
> Igor.|||There is a problem. I do not know how to force to install Service Pack for
the SQL Developer server.
The problem is that the SQL Personal already was installed in computer, and
the SQL Developer has been installed additionaly. For Example, the instance
of SQL Personal is "SQLSRV1" (the same as computer name), in enterprise
manager i see it as (local), and instance of SQL Developer server is
"SQLSRV1\NEW_DEV". Here is a problem ! The Service pack takes only SQL
Personal server and do not see SQL Developer, even if I'll stop SQL Personal
server and SQL Developer is running.
The question is: how to force Service Pack to be installed on SQL Developer
server in this configuration.
I already install Reporting services on other computers with one instance of
SQL server and it works perfectly !
Is there any way to solve this ?
Thanks!
Igor.
"Jerry Spivey" wrote:
> Igor,
> Why not install SP3a on each SQL Server? Or better yet SP4. For greater
> control over the RS installation see the 'Installing Reporting Services from
> the Command Line' topic in the Reporting Services Books Online. Also be
> sure to download the latest version of RS BOL at
> http://www.microsoft.com/downloads/details.aspx?FamilyID=5e550d73-8f35-435e-bb71-c8573a1cdbdb&DisplayLang=en
> HTH
> Jerry
> "Igor" <Igor@.discussions.microsoft.com> wrote in message
> news:E7B65CD3-64FF-4E84-9D6B-003F98E4EFAD@.microsoft.com...
> > Hi!
> >
> > How can i change default assignment of local SQL server instance.
> >
> > I have two instances installed on my computer, one - SQL personal 2000,
> > second - SQL developer 2000. I have to install Reporting Services on SQL
> > Developer, but first, i need to install Service Pack 3a on SQL Delveoper.
> > The
> > default instance of SQL server is - Personal (I see it as (local) in
> > enterprise manager). Even, if I'll stop SQL Personal server, I still would
> > not be able to install Reporting Services on SQL Developer. I always get
> > error - SQL Server Service Pack 3a not installed.
> >
> > Please, tell me, how can I solve this problem !
> >
> > Thanks!
> > Igor.
>
>
How can i change default assignment of local SQL server instance.
I have two instances installed on my computer, one - SQL personal 2000,
second - SQL developer 2000. I have to install Reporting Services on SQL
Developer, but first, i need to install Service Pack 3a on SQL Delveoper. The
default instance of SQL server is - Personal (I see it as (local) in
enterprise manager). Even, if I'll stop SQL Personal server, I still would
not be able to install Reporting Services on SQL Developer. I always get
error - SQL Server Service Pack 3a not installed.
Please, tell me, how can I solve this problem !
Thanks!
Igor.Igor,
Why not install SP3a on each SQL Server? Or better yet SP4. For greater
control over the RS installation see the 'Installing Reporting Services from
the Command Line' topic in the Reporting Services Books Online. Also be
sure to download the latest version of RS BOL at
http://www.microsoft.com/downloads/details.aspx?FamilyID=5e550d73-8f35-435e-bb71-c8573a1cdbdb&DisplayLang=en
HTH
Jerry
"Igor" <Igor@.discussions.microsoft.com> wrote in message
news:E7B65CD3-64FF-4E84-9D6B-003F98E4EFAD@.microsoft.com...
> Hi!
> How can i change default assignment of local SQL server instance.
> I have two instances installed on my computer, one - SQL personal 2000,
> second - SQL developer 2000. I have to install Reporting Services on SQL
> Developer, but first, i need to install Service Pack 3a on SQL Delveoper.
> The
> default instance of SQL server is - Personal (I see it as (local) in
> enterprise manager). Even, if I'll stop SQL Personal server, I still would
> not be able to install Reporting Services on SQL Developer. I always get
> error - SQL Server Service Pack 3a not installed.
> Please, tell me, how can I solve this problem !
> Thanks!
> Igor.|||There is a problem. I do not know how to force to install Service Pack for
the SQL Developer server.
The problem is that the SQL Personal already was installed in computer, and
the SQL Developer has been installed additionaly. For Example, the instance
of SQL Personal is "SQLSRV1" (the same as computer name), in enterprise
manager i see it as (local), and instance of SQL Developer server is
"SQLSRV1\NEW_DEV". Here is a problem ! The Service pack takes only SQL
Personal server and do not see SQL Developer, even if I'll stop SQL Personal
server and SQL Developer is running.
The question is: how to force Service Pack to be installed on SQL Developer
server in this configuration.
I already install Reporting services on other computers with one instance of
SQL server and it works perfectly !
Is there any way to solve this ?
Thanks!
Igor.
"Jerry Spivey" wrote:
> Igor,
> Why not install SP3a on each SQL Server? Or better yet SP4. For greater
> control over the RS installation see the 'Installing Reporting Services from
> the Command Line' topic in the Reporting Services Books Online. Also be
> sure to download the latest version of RS BOL at
> http://www.microsoft.com/downloads/details.aspx?FamilyID=5e550d73-8f35-435e-bb71-c8573a1cdbdb&DisplayLang=en
> HTH
> Jerry
> "Igor" <Igor@.discussions.microsoft.com> wrote in message
> news:E7B65CD3-64FF-4E84-9D6B-003F98E4EFAD@.microsoft.com...
> > Hi!
> >
> > How can i change default assignment of local SQL server instance.
> >
> > I have two instances installed on my computer, one - SQL personal 2000,
> > second - SQL developer 2000. I have to install Reporting Services on SQL
> > Developer, but first, i need to install Service Pack 3a on SQL Delveoper.
> > The
> > default instance of SQL server is - Personal (I see it as (local) in
> > enterprise manager). Even, if I'll stop SQL Personal server, I still would
> > not be able to install Reporting Services on SQL Developer. I always get
> > error - SQL Server Service Pack 3a not installed.
> >
> > Please, tell me, how can I solve this problem !
> >
> > Thanks!
> > Igor.
>
>
Sunday, February 12, 2012
changing database owner to access diagrams
hello,
i recently changed the machine name of my development computer and am now no longer able to create or view any diagrams for the sql database that was created by the old machine name user. i receive an error where I cannot make myself "the dbo of this database." i can see the old name in the "owner" properties field of the mdf database, but the box is grayed out and i am unable to change it to the new machine/user name. is there a way to change the owner of the database to my new machine/user name? the new name has admin rights and the computer is a standalone workstation not connected to a network.
i am using sql server 2005 express edition with visual web developer.
thanks!
Hi,
you can achieve this by sp_changedbowner store procedure refer ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/516ef311-e83b-45c9-b9cd-0e0641774c04.htm in SQL BOL.
Hemantgiri S. Goswami
|||thank you for the response. unfortunately, i am new to sql and do not know where or how to use this command. do i use VWD? Sql Server Management Studio Express does not allow me to navigate to the directory where the database resides. also, one additional piece of information: only the machine name changed.
old name: "old_machine_name/steve"
new name: "new_machine_name/steve"
which EXEC command should I use to change ownership of the database to the new name?
EXEC sp_changedbowner 'new_machine_name/steve'
or simply,
EXEC sp_changedbowner 'steve'
i am concerned that there is a larger problem with permissions and sql. anytime I add a new website and new database in VWD, the default "owner" field comes up with the old name, "old_machine_name/steve". how do I change this default so that all future databases and web sites have the new owner, "new_machine_name/steve"?
thanks!
|||Hi,
When you rename your Server name it will reflect to your SQL Server too, your sql server's name also change refer http://blog.opsan.com/archive/2005/05/10/465.aspx for more. If you wants to change the DB Owner Open
SQL Server Management Studio -> connect -> Click on New Query -> run exec sp_changedbowner <schema> Refer http://weblogs.asp.net/dneimke/archive/2003/11/10/36691.aspx and http://weblogs.asp.net/eporter/archive/2004/10/29/249627.aspx for more on DB owner change.
Hemantgiri S. Goswami
|||ok i tried using Sql Server Management Studio Express but was unable to navigate into the "C:\Documents and Settings\Steve\My Documents\My Web Sites\WebSite1\AppData" user directory. even though Steve is a user with admin rights, the Add database window would not open any directories below the Steve directory. therefore, i was unable to even see the mdf file to open.
i tried copying the complete web site to a c:\temp directory and was able to navigate to the AppData directory and open the database. when I ran the sql command in a query window:
EXEC sp_changedbowner 'Steve'
i received the error:
"Cannot find the principal 'Steve', because it does not exist or you do not have permission."
I received the same error when using this command: EXEC sp_changedbowner 'new_machine_name/Steve'
i did try renaming the computer name back to the old_machine_name, rebooted, and was successfully able to open the Diagrams directory of the database in the user Steve directory. Although I find this very strange, i do not know why it fails when I simply change the computer name to new_machine_name. do you have any insight to this? it still seems that there is a default setting using the old_machine_name/Steve stored somewhere in VWD or SQL Server 2005 Express.
thanks!
|||Hi,
That is because of Schema refer Schema in BOL and below articles http://www.databasejournal.com/features/mssql/article.php/3481751 and http://www.sswug.org/see/SQL_Server_2005_-_Schema_Definition-19357
Hemantgiri S. Goswami
|||i finally gave up and called Microsoft. the root cause was not found, but the fix was to create a new user with admin rights, then move all the files over to the new user account. the new machine name is now recognized and i am able to access the diagrams folder for the database. i hope this helps others with similar user account issues.
thanks for your help!
changing database owner to access diagrams
hello,
i recently changed the machine name of my development computer and am now no longer able to create or view any diagrams for the sql database that was created by the old machine name user. i receive an error where I cannot make myself "the dbo of this database." i can see the old name in the "owner" properties field of the mdf database, but the box is grayed out and i am unable to change it to the new machine/user name. is there a way to change the owner of the database to my new machine/user name? the new name has admin rights and the computer is a standalone workstation not connected to a network.
i am using sql server 2005 express edition with visual web developer.
thanks!
Hi,
you can achieve this by sp_changedbowner store procedure refer ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/516ef311-e83b-45c9-b9cd-0e0641774c04.htm in SQL BOL.
Hemantgiri S. Goswami
|||thank you for the response. unfortunately, i am new to sql and do not know where or how to use this command. do i use VWD? Sql Server Management Studio Express does not allow me to navigate to the directory where the database resides. also, one additional piece of information: only the machine name changed.
old name: "old_machine_name/steve"
new name: "new_machine_name/steve"
which EXEC command should I use to change ownership of the database to the new name?
EXEC sp_changedbowner 'new_machine_name/steve'
or simply,
EXEC sp_changedbowner 'steve'
i am concerned that there is a larger problem with permissions and sql. anytime I add a new website and new database in VWD, the default "owner" field comes up with the old name, "old_machine_name/steve". how do I change this default so that all future databases and web sites have the new owner, "new_machine_name/steve"?
thanks!
|||Hi,
When you rename your Server name it will reflect to your SQL Server too, your sql server's name also change refer http://blog.opsan.com/archive/2005/05/10/465.aspx for more. If you wants to change the DB Owner Open
SQL Server Management Studio -> connect -> Click on New Query -> run exec sp_changedbowner <schema> Refer http://weblogs.asp.net/dneimke/archive/2003/11/10/36691.aspx and http://weblogs.asp.net/eporter/archive/2004/10/29/249627.aspx for more on DB owner change.
Hemantgiri S. Goswami
|||ok i tried using Sql Server Management Studio Express but was unable to navigate into the "C:\Documents and Settings\Steve\My Documents\My Web Sites\WebSite1\AppData" user directory. even though Steve is a user with admin rights, the Add database window would not open any directories below the Steve directory. therefore, i was unable to even see the mdf file to open.
i tried copying the complete web site to a c:\temp directory and was able to navigate to the AppData directory and open the database. when I ran the sql command in a query window:
EXEC sp_changedbowner 'Steve'
i received the error:
"Cannot find the principal 'Steve', because it does not exist or you do not have permission."
I received the same error when using this command: EXEC sp_changedbowner 'new_machine_name/Steve'
i did try renaming the computer name back to the old_machine_name, rebooted, and was successfully able to open the Diagrams directory of the database in the user Steve directory. Although I find this very strange, i do not know why it fails when I simply change the computer name to new_machine_name. do you have any insight to this? it still seems that there is a default setting using the old_machine_name/Steve stored somewhere in VWD or SQL Server 2005 Express.
thanks!
|||Hi,
That is because of Schema refer Schema in BOL and below articles http://www.databasejournal.com/features/mssql/article.php/3481751 and http://www.sswug.org/see/SQL_Server_2005_-_Schema_Definition-19357
Hemantgiri S. Goswami
|||i finally gave up and called Microsoft. the root cause was not found, but the fix was to create a new user with admin rights, then move all the files over to the new user account. the new machine name is now recognized and i am able to access the diagrams folder for the database. i hope this helps others with similar user account issues.
thanks for your help!
Subscribe to:
Posts (Atom)