build application: instead

Showing posts with label instead. Show all posts

Friday, March 30, 2012

how to change the decimal symbol

Hi , i have a problem i live in Europe (Spain) and we use
the " , " symbol instead of " . " as decimal symbol for
money. Actually my problem is that i have a table with
some varchar fields storing money values but when i try to
use them in stored procedures as money using Cast(field as
money) sql Server don't use the " , " symbol as decimal
and the values i get are useless.
How can i set up the sql server to change the , for the .?
thanks in advance.
hi Jorge
"Jorge Lozano" <capikirk@.terra.es> ha scritto nel messaggio
news:189101c4bc43$eb5bcff0$a501280a@.phx.gbl
> Hi , i have a problem i live in Europe (Spain) and we use
> the " , " symbol instead of " . " as decimal symbol for
> money. Actually my problem is that i have a table with
> some varchar fields storing money values but when i try to
> use them in stored procedures as money using Cast(field as
> money) sql Server don't use the " , " symbol as decimal
> and the values i get are useless.
> How can i set up the sql server to change the , for the .?
> thanks in advance.
first of all, you should not pass decimal as chars, but you should treat
them as numeric values..
SQL Server correctly understand only the dot "." as decimal place holder
and, as it's not a reporting tool but a data tool, it just pass them in and
out that way.. no thousend placeholder and "." for decimal
befor casting it, you should convert decimal sep to "."..
SET NOCOUNT ON
DECLARE @.n VARCHAR(14)
DECLARE @.dec DECIMAL (14,4)
PRINT 'this number, 123456.1234 is formatte in "italian" style =;-D'
SET @.n = '123.456,1234'
SELECT @.n AS [Original]
PRINT 'remove thousend placeholder'
SET @.n = REPLACE( @.n , '.' , '')
SELECT @.n AS [thousand stripped]
PRINT 'replace decimal placeholder'
SET @.n = REPLACE( @.n , ',' , '.')
SELECT @.n AS [Final string]
PRINT 'cast it to numeric value'
SET @.dec = CONVERT(DECIMAL(14,4), @.n)
SELECT @.dec AS [Casted decimal value]
PRINT 'in one single step'
SET @.n = '123.456,1234'
SELECT CONVERT(DECIMAL(14,4), REPLACE( REPLACE( @.n , '.' , '') , ',' , '.'))
AS [Final value]
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Wednesday, March 28, 2012

How to change sql login username instead of create new one.

As title, thanks!Can you explain what you mean by "change sql login username"? Are you referring to the login name
(in master) or the user name (in your database)? Anyhow, you cannot change the name of a login or a
user, You will be able to rename a user in 2005, not sure about login, though.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Pleo" <rx8@.hotmail.com> wrote in message news:eVAlmnv0FHA.2428@.tk2msftngp13.phx.gbl...
> As title, thanks!
>|||I'm not familiar sql. At enterprise server (sql2000) > security > logins >
(want to change name here).
Anyway, I guess it can't be changed there. Thanks.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> ?
news:%23R4Ixpv0FHA.3068@.TK2MSFTNGP10.phx.gbl ?...
> Can you explain what you mean by "change sql login username"? Are you
referring to the login name
> (in master) or the user name (in your database)? Anyhow, you cannot change
the name of a login or a
> user, You will be able to rename a user in 2005, not sure about login,
though.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Pleo" <rx8@.hotmail.com> wrote in message
news:eVAlmnv0FHA.2428@.tk2msftngp13.phx.gbl...
> > As title, thanks!
> >
> >
>|||Hi,
As Tibor mentioned; In SQL 2000 you can not rename a SQL Server Login or
database user.
Thanks
Hari
SQL Server MVP
"Pleo" <rx8@.hotmail.com> wrote in message
news:ON0lytv0FHA.404@.TK2MSFTNGP09.phx.gbl...
> I'm not familiar sql. At enterprise server (sql2000) > security > logins >
> (want to change name here).
> Anyway, I guess it can't be changed there. Thanks.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> ?
> news:%23R4Ixpv0FHA.3068@.TK2MSFTNGP10.phx.gbl ?...
>> Can you explain what you mean by "change sql login username"? Are you
> referring to the login name
>> (in master) or the user name (in your database)? Anyhow, you cannot
>> change
> the name of a login or a
>> user, You will be able to rename a user in 2005, not sure about login,
> though.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Pleo" <rx8@.hotmail.com> wrote in message
> news:eVAlmnv0FHA.2428@.tk2msftngp13.phx.gbl...
>> > As title, thanks!
>> >
>> >
>|||Hi,
Yes, you can rename a SQL Server Login. You have to manually change values
in master.dbo.syslogins and master.dbo.sysxlogins.
Gabi Tianu
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:uW5cTJ80FHA.916@.TK2MSFTNGP10.phx.gbl...
> Hi,
> As Tibor mentioned; In SQL 2000 you can not rename a SQL Server Login or
> database user.
> Thanks
> Hari
> SQL Server MVP
> "Pleo" <rx8@.hotmail.com> wrote in message
> news:ON0lytv0FHA.404@.TK2MSFTNGP09.phx.gbl...
>> I'm not familiar sql. At enterprise server (sql2000) > security > logins
>> >
>> (want to change name here).
>> Anyway, I guess it can't be changed there. Thanks.
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> ?
>> news:%23R4Ixpv0FHA.3068@.TK2MSFTNGP10.phx.gbl ?...
>> Can you explain what you mean by "change sql login username"? Are you
>> referring to the login name
>> (in master) or the user name (in your database)? Anyhow, you cannot
>> change
>> the name of a login or a
>> user, You will be able to rename a user in 2005, not sure about login,
>> though.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Pleo" <rx8@.hotmail.com> wrote in message
>> news:eVAlmnv0FHA.2428@.tk2msftngp13.phx.gbl...
>> > As title, thanks!
>> >
>> >
>>
>|||sysxlogins is the name of the table, and syslogins is a view. So, the only place where a change
would be needed is sysxlogins. However, hacking system tables is not supported and Pleo would be on
his own by doing that.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Gabi Tianu" <gabi_tianu@.hotmail.com> wrote in message news:eS3wd$T1FHA.1108@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Yes, you can rename a SQL Server Login. You have to manually change values in master.dbo.syslogins
> and master.dbo.sysxlogins.
> Gabi Tianu
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:uW5cTJ80FHA.916@.TK2MSFTNGP10.phx.gbl...
>> Hi,
>> As Tibor mentioned; In SQL 2000 you can not rename a SQL Server Login or database user.
>> Thanks
>> Hari
>> SQL Server MVP
>> "Pleo" <rx8@.hotmail.com> wrote in message news:ON0lytv0FHA.404@.TK2MSFTNGP09.phx.gbl...
>> I'm not familiar sql. At enterprise server (sql2000) > security > logins >
>> (want to change name here).
>> Anyway, I guess it can't be changed there. Thanks.
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> ?
>> news:%23R4Ixpv0FHA.3068@.TK2MSFTNGP10.phx.gbl ?...
>> Can you explain what you mean by "change sql login username"? Are you
>> referring to the login name
>> (in master) or the user name (in your database)? Anyhow, you cannot change
>> the name of a login or a
>> user, You will be able to rename a user in 2005, not sure about login,
>> though.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Pleo" <rx8@.hotmail.com> wrote in message
>> news:eVAlmnv0FHA.2428@.tk2msftngp13.phx.gbl...
>> > As title, thanks!
>> >
>> >
>>
>>
>|||Very good point Tibor. Any unsupported change to the system table allways
has to be done first in a test environment.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OIynWFU1FHA.916@.TK2MSFTNGP10.phx.gbl...
> sysxlogins is the name of the table, and syslogins is a view. So, the only
> place where a change would be needed is sysxlogins. However, hacking
> system tables is not supported and Pleo would be on his own by doing that.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Gabi Tianu" <gabi_tianu@.hotmail.com> wrote in message
> news:eS3wd$T1FHA.1108@.TK2MSFTNGP14.phx.gbl...
>> Hi,
>> Yes, you can rename a SQL Server Login. You have to manually change
>> values in master.dbo.syslogins and master.dbo.sysxlogins.
>> Gabi Tianu
>> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
>> news:uW5cTJ80FHA.916@.TK2MSFTNGP10.phx.gbl...
>> Hi,
>> As Tibor mentioned; In SQL 2000 you can not rename a SQL Server Login or
>> database user.
>> Thanks
>> Hari
>> SQL Server MVP
>> "Pleo" <rx8@.hotmail.com> wrote in message
>> news:ON0lytv0FHA.404@.TK2MSFTNGP09.phx.gbl...
>> I'm not familiar sql. At enterprise server (sql2000) > security >
>> logins >
>> (want to change name here).
>> Anyway, I guess it can't be changed there. Thanks.
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> ?
>> news:%23R4Ixpv0FHA.3068@.TK2MSFTNGP10.phx.gbl ?...
>> Can you explain what you mean by "change sql login username"? Are you
>> referring to the login name
>> (in master) or the user name (in your database)? Anyhow, you cannot
>> change
>> the name of a login or a
>> user, You will be able to rename a user in 2005, not sure about login,
>> though.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Pleo" <rx8@.hotmail.com> wrote in message
>> news:eVAlmnv0FHA.2428@.tk2msftngp13.phx.gbl...
>> > As title, thanks!
>> >
>> >
>>
>>
>>
>|||Hi Gabi,
This is not recommended by microsoft to update or modify system tables.
from
Dollerssql

How to change sql login username instead of create new one.

Monday, March 26, 2012

how to change password to dts package programmatically?

Hi,

I was wondering if it is possible to change the password on a SQL Server 2000 DTS package programatically instead of just
manually using the the interface?

I have over 150 DTS packages used for building BI data cubes and our password policy requires a password change every 60 days.

I would like to write a windows vb.net application that loops through all the DTS packages and changes the password.

Please advise.

Thanks

Chris

This is just an analysis...not an answer.

If you develop a program that loops through each dts package and changes the pwd then you must have to put the pwd into a file, which kind of defeats security.

My suggestion is to remove the policy for changing pwds on DTS packages and control security at a higher level in SQL Server.

|||

I was going to have the password be entered via a password text box by the user.

Is this even possible?

Thanks

Chris

Friday, March 23, 2012

How to change instance ?

I did an upgrade to my MSDE on my machine, but i use a different name (Infinity) instead of SQLEXPRESS or MSSQLSERVER. I'm wondering if there is a way for me to change it back to SQLExpress ?

There is no way to rename an instance in SQL Server. You'll need to install a new instance and move your database from the old instance to the new instance.

Cheers,

Dan

Wednesday, March 21, 2012

How to change collation on MODEL ?

> How can I force tempdb to have a specific collation, instead of the same
> collation as the server collation?
You have to rebuild the system databases, as you cannot have different colla
tions across the system
databases. This is done using the SETUP program in 2005. This is described i
n
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/df40c888-691c-4962-a420-
78a57852364d.htm.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Pat" <pat@.online.nospam> wrote in message
news:B4BB3864-D003-426A-9860-CC2FC80D0E06@.microsoft.com...
>I have a 2005 SP1 server with the wrong server collation.
> All user databases on this server have the right collation.
> I am getting collation conflict errors when I use temp tables.
> I believe this is because tempdb is created based on "model", and model ha
s
> the server collation.
> I have tried to change the "model" collation using ALTER DATABASE, but I g
et
> the error message "Cannot alter the database 'model' because it is a syste
m
> database.
> "
> How can I force tempdb to have a specific collation, instead of the same
> collation as the server collation?I have a 2005 SP1 server with the wrong server collation.
All user databases on this server have the right collation.
I am getting collation conflict errors when I use temp tables.
I believe this is because tempdb is created based on "model", and model has
the server collation.
I have tried to change the "model" collation using ALTER DATABASE, but I get
the error message "Cannot alter the database 'model' because it is a system
database.
"
How can I force tempdb to have a specific collation, instead of the same
collation as the server collation?|||> How can I force tempdb to have a specific collation, instead of the same
> collation as the server collation?
You have to rebuild the system databases, as you cannot have different colla
tions across the system
databases. This is done using the SETUP program in 2005. This is described i
n
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/df40c888-691c-4962-a420-
78a57852364d.htm.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Pat" <pat@.online.nospam> wrote in message
news:B4BB3864-D003-426A-9860-CC2FC80D0E06@.microsoft.com...
>I have a 2005 SP1 server with the wrong server collation.
> All user databases on this server have the right collation.
> I am getting collation conflict errors when I use temp tables.
> I believe this is because tempdb is created based on "model", and model ha
s
> the server collation.
> I have tried to change the "model" collation using ALTER DATABASE, but I g
et
> the error message "Cannot alter the database 'model' because it is a syste
m
> database.
> "
> How can I force tempdb to have a specific collation, instead of the same
> collation as the server collation?|||Hi Pat,
Thank you for your posting!
I agreed with Tibor. You need to rebuild the system database.
Also, here is an article for your reference.
325335 How to transfer a database from one collation to another collation
in SQL Server
http://support.microsoft.com/defaul...kb;EN-US;325335
Hope this will be helpful!
Sincerely,
Wei Lu
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Pat,
Thank you for your posting!
I agreed with Tibor. You need to rebuild the system database.
Also, here is an article for your reference.
325335 How to transfer a database from one collation to another collation
in SQL Server
http://support.microsoft.com/defaul...kb;EN-US;325335
Hope this will be helpful!
Sincerely,
Wei Lu
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||The doc mentions that all service packs and hotfixes are lost when rebuildin
g
the system databases, and need to be re-applied.
We have SP1 and several hotfixes, this sounds like a lot of work for just
changing the collation.
"Tibor Karaszi" wrote:

> You have to rebuild the system databases, as you cannot have different col
lations across the system
> databases. This is done using the SETUP program in 2005. This is described
in
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/df40c888-691c-4962-a42
0-78a57852364d.htm.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Pat" <pat@.online.nospam> wrote in message
> news:B4BB3864-D003-426A-9860-CC2FC80D0E06@.microsoft.com...
>|||> We have SP1 and several hotfixes, this sounds like a lot of work for just
> changing the collation.
Yep, that is why I always advocate to be very careful when selecting the col
lation during
installation.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Pat" <pat@.online.nospam> wrote in message
news:DFA53097-0EB5-4125-A9EC-E0EF157212D9@.microsoft.com...[vbcol=seagreen]
> The doc mentions that all service packs and hotfixes are lost when rebuild
ing
> the system databases, and need to be re-applied.
> We have SP1 and several hotfixes, this sounds like a lot of work for just
> changing the collation.
> "Tibor Karaszi" wrote:
>|||The doc mentions that all service packs and hotfixes are lost when rebuildin
g
the system databases, and need to be re-applied.
We have SP1 and several hotfixes, this sounds like a lot of work for just
changing the collation.
"Tibor Karaszi" wrote:

> You have to rebuild the system databases, as you cannot have different col
lations across the system
> databases. This is done using the SETUP program in 2005. This is described
in
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/df40c888-691c-4962-a42
0-78a57852364d.htm.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Pat" <pat@.online.nospam> wrote in message
> news:B4BB3864-D003-426A-9860-CC2FC80D0E06@.microsoft.com...
>|||> We have SP1 and several hotfixes, this sounds like a lot of work for just
> changing the collation.
Yep, that is why I always advocate to be very careful when selecting the col
lation during
installation.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Pat" <pat@.online.nospam> wrote in message
news:DFA53097-0EB5-4125-A9EC-E0EF157212D9@.microsoft.com...[vbcol=seagreen]
> The doc mentions that all service packs and hotfixes are lost when rebuild
ing
> the system databases, and need to be re-applied.
> We have SP1 and several hotfixes, this sounds like a lot of work for just
> changing the collation.
> "Tibor Karaszi" wrote:
>|||"Pat" wrote:

> I have a 2005 SP1 server with the wrong server collation.
> All user databases on this server have the right collation.
> I am getting collation conflict errors when I use temp tables.
> I believe this is because tempdb is created based on "model", and model ha
s
> the server collation.
> I have tried to change the "model" collation using ALTER DATABASE, but I g
et
> the error message "Cannot alter the database 'model' because it is a syste
m
> database.
> "
> How can I force tempdb to have a specific collation, instead of the same
> collation as the server collation?
In addition to the other replies to your query, there may be another way to
do this.
If you create a new temporary table in a stored procedure, use the good
collation.
example:
Create Table #Status
(
State nvarchar(128) collate database_default
)
This might be a good solution if there are not too many places where
temporary tables are created.
hope this helps.
kind regards,
Bart.

Monday, March 19, 2012

How to change clustered PK to non clustered on a replicated table

Hello,
I want to know if the re is an easy way to modify the Primary Key on a
replicated table to be a non-clustered index instead. Looking through
queries hitting this table show a clustered index on a different field will
significantly increase performance.
Anyone run into this and find a solution?
Thanks.
~lbLonnye Blake Bower wrote:
> Hello,
> I want to know if the re is an easy way to modify the Primary Key on a
> replicated table to be a non-clustered index instead. Looking through
> queries hitting this table show a clustered index on a different
> field will significantly increase performance.
> Anyone run into this and find a solution?
> Thanks.
> ~lb
Unfortunately, I don't think there is an easy way to do this. If you
ever wonder how a tool , like SQL Enterprise Manager, does something
using its UI, you can run Profiler to watch the activity. When you
change a PK constraint from clustered to non-clustered it looks like SQL
EM is creating new tables with the desired changes (and this affects FK
tables as well).
What you can do is work from a test database until you get the script
working properly. The script will have to remove the FK constraints,
remove the PK constraint, change the PK constraint, and re-create the FK
constraints. Once you have the script working, you should start the
database is single-user mode and make your changes off-hours.
David Gugick
Imceda Software
www.imceda.com

Friday, March 30, 2012

how to change the decimal symbol

Wednesday, March 28, 2012

How to change sql login username instead of create new one.

How to change sql login username instead of create new one.

How to change sql login username instead of create new one.

Monday, March 26, 2012

how to change password to dts package programmatically?

Friday, March 23, 2012

How to change instance ?

Wednesday, March 21, 2012

How to change collation on MODEL ?

Monday, March 19, 2012

How to change clustered PK to non clustered on a replicated table

build application

Blog Archive

About Me