Encrypting passwords

A friend of my self asked me how he can save a password not as clear text. He wanted to encrypt the password and save the encrypted string in the database.

How can he do this? Maybe somebody can help me here.

Regards Markus

What does your friend need the password for? If it is used for authentication (to verify that another password submitted is matching the password stored in the database), then you can hash the password. If you need to store the password to use it somewhere else in clear form, then you need to encrypt it.

For SQL Server 2000 there are no builtin functions for hashing or encryption You may hear about pwdencrypt - an undocumented function - do not use it. For SQL Server 2000, you will have to write your own extended procedures for performing encryption or hashing.

In SQL Server 2005, you can use HashBytes to hash the password and EncryptByKey to encrypt it.

Thanks
Laurentiu

|||

but is this truly secure? you're still sending the password over a connection in clear text, unless you're in SSL, yes? isn't it best to simply hash the password on the clientside to begin with?

Laurentiu Cristofor wrote:

In SQL Server 2005, you can use HashBytes to hash the password and EncryptByKey to encrypt it.

Thanks
Laurentiu

|||

It is secure if the connection is secured using SSL - it should be secured that way if you're concerned about security.

Hashing on the client side does not address the insecure connection problem, because your authentication will then only depend on the hash (that's all the server will see from the client), and then the hash will effectively serve the same role as the password, so if a hash is intercepted on an insecure connection, a third party can pass it back to the server and connect this way.

Also, note that in my previous post I did not recommend implementing custom authentication schemes using those functions. Instead, you should leverage the mechanisms already provided by SQL Server.

Thanks
Laurentiu

sql

encrypt(string) Question!

SQL Server 2000:

################################################## ######
I run the following as a normal query from Analyzer:
################################################## ######

SELECT encrypt(user_password) FROM emp WHERE user_id = 1

################################################## #######
I run the following query from inside a stored proc:
################################################## #######

SELECT encrypt(user_password) FROM emp WHERE user_id = 1

################################################## #######
Question??
################################################## #######

If the data inside the emp table does not change, how can these two
queries return different values?

Any help would be much appreciated!

thanks,
Russ> SELECT encrypt(user_password) FROM emp WHERE user_id = 1
> SELECT encrypt(user_password) FROM emp WHERE user_id = 1

> If the data inside the emp table does not change, how can these two
> queries return different values?

They return different values because the encrypt function 'salts' the data
to prevent someone from just encrypting a bunch of stuff to figure out the
other data in the table.

The Unix crypt function used to do this by putting two random characters on
the front of the data string and also on the front of the encryption string
using the 'salt' as part of the key.

Regards,
Jim|||In addition to James's reply, note that the Encrypt function is undocumented
so its behaviour can change between versions of the product. Don't rely on
it in production code. Generate a password hash client-side would be my
suggestion.

--
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:0eadncyJC6oF1hzcRVn-tg@.giganews.com...
> In addition to James's reply, note that the Encrypt function is
undocumented
> so its behaviour can change between versions of the product. Don't rely on
> it in production code. Generate a password hash client-side would be my
> suggestion.

And in the at least one case I looked at, trivial to decrypt.

> --
> David Portas
> SQL Server MVP
> --

Encoding string from MS SQL

Hi all,

I have an application which will send out email in plain text in multi langauage.
the email content will be pull from txt file save in UTF-8.
i can send out email from the template with the encoding.
but when i insert data from the SQl server. the data from the SQL server are not encoded.
how do i encode the data (in other lanagauge) from sql server into UTF-8 so that it can be send together with the template.

I have try changing the data into byte and encode it in UTF-8.
but it won't displayed correctly. pls help. thanks

Unicode in SQL Server is either UCS-2 or UTF 16 and the later is the generally used version, .NET is UTF 16 by default so you can change your .NET encoding to UTF 16. The reason is NChar, NVarchar, NText, NChar max and NVarchar max are multi bytes by definition so you just need to convert to UTF 8 in your code. Hope this helps.|||

my data from the database look like this ??òú??. it is chinese simplified gb2313
the datatype for the fields is varchar.
so how do i encode it into UTF8 and it can be display in chinese.
pls help i'm totally confuse by the encoding.

|||

The Chinese you are using is Windows code page, there are six Chinese collation in SQL Server you have to find the right one in the thread below. And to UTF 8 encode in VS you start at the link below it is for VS2003 but I think it should work. So you do column level collation for your specific Chinese in SQL Server and do Unicode encoding in VS and it may be resolved. Hope this helps.

http://forums.asp.net/1067798/ShowPost.aspx

http://www.aspnetresources.com/blog/unicode_in_vsnet.aspx

|||

Hi Thanks for you advice,

base on your info i manage to find the extended proc to solve my prob xp_cp2u_web.
but i have another prob. in my stored procedure the output parameter i set it to a size of 20 data type nvarchar. it will return the result. and it will display correctly on screen. but when it is use to send via email. the rest of the content in the email is gone after my chinese character.

after some debugging, i found that it is due to the size of nvarchar i set. my chinese character size is 3. so if i set the nvarchar size to 3 all will work but if the size i set is bigger than the actual result return. it will affect the rest of the text in my email..

can anyone pls advise me on how to set the nvarchar size for my output sqlparameter.
is there a way where i can set the size of the Nvarchar as dynamic. Thanks

cmd5.CommandText = "GetEmailDetail";
cmd5.CommandType = CommandType.StoredProcedure;
cmd5.Parameters.Add("@.No", Service_ID);
SqlParameter parameterfullname = cmd5.Parameters.Add("@.name", SqlDbType.NVarChar, 20);
parameterfullname.Direction = ParameterDirection.Output;

|||

Try the link below everything you need is covered including the correct stored procedure because that is important. When you are getting value back from a SQL Server stored procedure it is OUTPUT parameter except INT which is return value, so if in doubt always use OUTPUT if it is not needed SQL Server will ignore it. So use correct column level collation and ADO.NET OUTPUT parameters. Hope this helps.

http://msdn.microsoft.com/msdnmag/issues/05/05/DataPoints/

Encoding For HashBytes

When SQL Server attempts to do a MD5 hash on this string it most encode the
string to binary before hashing it. Does anyone know how varchar is encoded
,
i.e. what encoding is used for varchar? UTF-8? UTF-16? Example code:
SELECT HashBytes('MD5',CONVERT(varchar,’some string’))
If you send a nvarchar, it uses UTF-16, example:
SELECT HashBytes('MD5',CONVERT(nvarchar,’some
string’))
Thanks in advance. With this information I can write some C# code to create
a hash that matches what SQL server does.
-WayneWayne Berry (WayneBerry@.discussions.microsoft.com) writes:
> When SQL Server attempts to do a MD5 hash on this string it most encode
> the string to binary before hashing it. Does anyone know how varchar is
> encoded, i.e. what encoding is used for varchar? UTF-8? UTF-16?
> Example code:
> SELECT HashBytes('MD5',CONVERT(varchar,some string))
> If you send a nvarchar, it uses UTF-16, example:
> SELECT HashBytes('MD5',CONVERT(nvarchar,some string))
> Thanks in advance. With this information I can write some C# code to
> create a hash that matches what SQL server does.
I would suspect that it simply hashes the byte value. Which for varchar
means codes in the range 0 to 255(*), and for nvarchar a UTF-16 encoding.
(*) For Western scripts. For East Asian scripts it would be a double-
byte character set.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Enable User Instances in SQL Server

I'm just starting out and trying to connect to my first database using the following string:

Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\RFPdb.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True

I get the following error during debug:

"Generating user instances in SQL Server is disabled. Use sp_configure 'user instances enabled' to generate user instances."

I've done this on every database - Master, Model, Tempdb, etc, and my database.

Still get the error. I may not be enabling it correctly. Any help would be appreciated.

The documentation suggests that I use user instances so that users without Admin rights can work with the database. Add, delete, edit data. Is this true?

is the database on your local machine or are you trying to connect through remote machies. Remote connections will not be allowed.|||Local database that I will eventually deploy with the product for local use only.|||Try using User Instance=False. User Instance is a special feature in express edition that allows normal users to have owner privilges and I guess this is not what is require here.|||

When I turn User Instance Off I get the error:

"A database with the same name exists, or specified file cannot be opened, or it is located on UNC share."

I tried downloading SSEUtil to set enable user instances but the .exe doesn't work. It flashes a command window and shuts down. Any ideas on this?

|||

is it necessary to use to attachdb. I mean any specific reason. If not try connecting using a connection string like :

connectionString="Data Source=LPXP561;Initial Catalog=AdventureWorks;Integrated Security=True

|||Lot's of other options will work. I'm trying to use the new User Instance feature of SQL Sever Express. It requires the "attachbd" usage and User Instance = True. I can't get it to work as decribed in the documentation.|||

Have got the same problem: cant connect to user instance, since it is not enabled in express version by default..

can anybody suggest how to "Use sp_configure 'user instances enabled' ", where to type it.. or whatever..

|||Hi,

Open the SQL Server Management Studio Express. This is the downloadable program in the same site where you downloaded the SQL Server 2005 express used to manage SQL Server 2005 Express.
In the query editor type this text: exec sp_configure 'user instances enabled', 1.
Then type: Reconfigure.
Then restart the SQL Server database.

Good luck.

KaBalweg?|||

Hi DDH,

I know this is now several weeks after your posted problem, but I have exactly the same issue. Did the suggested solution resolve the problem?

Mike

|||Thanks kabalweg for the str8 forward solution to this issue. It worked for me and made complete sense as soon as I read it.|||it's really nice when someone gives you a straight answer!-)
this one got me going right away!
i was trying to connect to the DBs I'm building in SQL 2005.
"exec sp_configure 'user instances enabled', 1" got me started quickly.
heck! ...i might re-visit this forum!-)
|||God bless you mate, you gave me heaven on earth. wonderful, amazing, magical. It works like .....wawwwwwww|||woot!!dude..u simply rocks!!|||

Hi, I have been doing this, and in the query editor, it says it has been changed, and then restarted the server, but it neva worked.

So i tried it again (and the query results said changed from 1 to 1), but it still hasnt worked.

Have i configured the sql server incorrectly?

Please help

PS i have been restarting my sql service through the sql server configuration manager

Enable User Instances in SQL Server

I'm just starting out and trying to connect to my first database using the following string:

Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\RFPdb.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True

I get the following error during debug:

"Generating user instances in SQL Server is disabled. Use sp_configure 'user instances enabled' to generate user instances."

I've done this on every database - Master, Model, Tempdb, etc, and my database.

Still get the error. I may not be enabling it correctly. Any help would be appreciated.

The documentation suggests that I use user instances so that users without Admin rights can work with the database. Add, delete, edit data. Is this true?

is the database on your local machine or are you trying to connect through remote machies. Remote connections will not be allowed.|||Local database that I will eventually deploy with the product for local use only.|||Try using User Instance=False. User Instance is a special feature in express edition that allows normal users to have owner privilges and I guess this is not what is require here.|||

When I turn User Instance Off I get the error:

"A database with the same name exists, or specified file cannot be opened, or it is located on UNC share."

I tried downloading SSEUtil to set enable user instances but the .exe doesn't work. It flashes a command window and shuts down. Any ideas on this?

|||

is it necessary to use to attachdb. I mean any specific reason. If not try connecting using a connection string like :

connectionString="Data Source=LPXP561;Initial Catalog=AdventureWorks;Integrated Security=True

|||Lot's of other options will work. I'm trying to use the new User Instance feature of SQL Sever Express. It requires the "attachbd" usage and User Instance = True. I can't get it to work as decribed in the documentation.|||

Have got the same problem: cant connect to user instance, since it is not enabled in express version by default..

can anybody suggest how to "Use sp_configure 'user instances enabled' ", where to type it.. or whatever..

|||Hi,

Open the SQL Server Management Studio Express. This is the downloadable program in the same site where you downloaded the SQL Server 2005 express used to manage SQL Server 2005 Express.
In the query editor type this text: exec sp_configure 'user instances enabled', 1.
Then type: Reconfigure.
Then restart the SQL Server database.

Good luck.

KaBalweg?|||

Hi DDH,

I know this is now several weeks after your posted problem, but I have exactly the same issue. Did the suggested solution resolve the problem?

Mike

|||Thanks kabalweg for the str8 forward solution to this issue. It worked for me and made complete sense as soon as I read it.|||it's really nice when someone gives you a straight answer!-)
this one got me going right away!
i was trying to connect to the DBs I'm building in SQL 2005.
"exec sp_configure 'user instances enabled', 1" got me started quickly.
heck! ...i might re-visit this forum!-)
|||God bless you mate, you gave me heaven on earth. wonderful, amazing, magical. It works like .....wawwwwwww|||woot!!dude..u simply rocks!!|||

Hi, I have been doing this, and in the query editor, it says it has been changed, and then restarted the server, but it neva worked.

So i tried it again (and the query results said changed from 1 to 1), but it still hasnt worked.

Have i configured the sql server incorrectly?

Please help

PS i have been restarting my sql service through the sql server configuration manager

Enable User Instances in SQL Server

I'm just starting out and trying to connect to my first database using the following string:

Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\RFPdb.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True

I get the following error during debug:

"Generating user instances in SQL Server is disabled. Use sp_configure 'user instances enabled' to generate user instances."

I've done this on every database - Master, Model, Tempdb, etc, and my database.

Still get the error. I may not be enabling it correctly. Any help would be appreciated.

The documentation suggests that I use user instances so that users without Admin rights can work with the database. Add, delete, edit data. Is this true?

is the database on your local machine or are you trying to connect through remote machies. Remote connections will not be allowed.|||Local database that I will eventually deploy with the product for local use only.|||Try using User Instance=False. User Instance is a special feature in express edition that allows normal users to have owner privilges and I guess this is not what is require here.|||

When I turn User Instance Off I get the error:

"A database with the same name exists, or specified file cannot be opened, or it is located on UNC share."

I tried downloading SSEUtil to set enable user instances but the .exe doesn't work. It flashes a command window and shuts down. Any ideas on this?

|||

is it necessary to use to attachdb. I mean any specific reason. If not try connecting using a connection string like :

connectionString="Data Source=LPXP561;Initial Catalog=AdventureWorks;Integrated Security=True

|||Lot's of other options will work. I'm trying to use the new User Instance feature of SQL Sever Express. It requires the "attachbd" usage and User Instance = True. I can't get it to work as decribed in the documentation.|||

Have got the same problem: cant connect to user instance, since it is not enabled in express version by default..

can anybody suggest how to "Use sp_configure 'user instances enabled' ", where to type it.. or whatever..

|||Hi,

Open the SQL Server Management Studio Express. This is the downloadable program in the same site where you downloaded the SQL Server 2005 express used to manage SQL Server 2005 Express.
In the query editor type this text: exec sp_configure 'user instances enabled', 1.
Then type: Reconfigure.
Then restart the SQL Server database.

Good luck.

KaBalweg?|||

Hi DDH,

I know this is now several weeks after your posted problem, but I have exactly the same issue. Did the suggested solution resolve the problem?

Mike

|||Thanks kabalweg for the str8 forward solution to this issue. It worked for me and made complete sense as soon as I read it.|||it's really nice when someone gives you a straight answer!-)
this one got me going right away!
i was trying to connect to the DBs I'm building in SQL 2005.
"exec sp_configure 'user instances enabled', 1" got me started quickly.
heck! ...i might re-visit this forum!-)
|||God bless you mate, you gave me heaven on earth. wonderful, amazing, magical. It works like .....wawwwwwww|||woot!!dude..u simply rocks!!|||

Hi, I have been doing this, and in the query editor, it says it has been changed, and then restarted the server, but it neva worked.

So i tried it again (and the query results said changed from 1 to 1), but it still hasnt worked.

Have i configured the sql server incorrectly?

Please help

PS i have been restarting my sql service through the sql server configuration manager

Enable User Instances in SQL Server

I'm just starting out and trying to connect to my first database using the following string:

Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\RFPdb.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True

I get the following error during debug:

"Generating user instances in SQL Server is disabled. Use sp_configure 'user instances enabled' to generate user instances."

I've done this on every database - Master, Model, Tempdb, etc, and my database.

Still get the error. I may not be enabling it correctly. Any help would be appreciated.

The documentation suggests that I use user instances so that users without Admin rights can work with the database. Add, delete, edit data. Is this true?

is the database on your local machine or are you trying to connect through remote machies. Remote connections will not be allowed.|||Local database that I will eventually deploy with the product for local use only.|||Try using User Instance=False. User Instance is a special feature in express edition that allows normal users to have owner privilges and I guess this is not what is require here.|||

When I turn User Instance Off I get the error:

"A database with the same name exists, or specified file cannot be opened, or it is located on UNC share."

I tried downloading SSEUtil to set enable user instances but the .exe doesn't work. It flashes a command window and shuts down. Any ideas on this?

|||

is it necessary to use to attachdb. I mean any specific reason. If not try connecting using a connection string like :

connectionString="Data Source=LPXP561;Initial Catalog=AdventureWorks;Integrated Security=True

|||Lot's of other options will work. I'm trying to use the new User Instance feature of SQL Sever Express. It requires the "attachbd" usage and User Instance = True. I can't get it to work as decribed in the documentation.|||

Have got the same problem: cant connect to user instance, since it is not enabled in express version by default..

can anybody suggest how to "Use sp_configure 'user instances enabled' ", where to type it.. or whatever..

|||Hi,

Open the SQL Server Management Studio Express. This is the downloadable program in the same site where you downloaded the SQL Server 2005 express used to manage SQL Server 2005 Express.
In the query editor type this text: exec sp_configure 'user instances enabled', 1.
Then type: Reconfigure.
Then restart the SQL Server database.

Good luck.

KaBalweg?|||

Hi DDH,

I know this is now several weeks after your posted problem, but I have exactly the same issue. Did the suggested solution resolve the problem?

Mike

|||Thanks kabalweg for the str8 forward solution to this issue. It worked for me and made complete sense as soon as I read it.|||it's really nice when someone gives you a straight answer!-)
this one got me going right away!
i was trying to connect to the DBs I'm building in SQL 2005.
"exec sp_configure 'user instances enabled', 1" got me started quickly.
heck! ...i might re-visit this forum!-)
|||God bless you mate, you gave me heaven on earth. wonderful, amazing, magical. It works like .....wawwwwwww|||woot!!dude..u simply rocks!!|||

Hi, I have been doing this, and in the query editor, it says it has been changed, and then restarted the server, but it neva worked.

So i tried it again (and the query results said changed from 1 to 1), but it still hasnt worked.

Have i configured the sql server incorrectly?

Please help

PS i have been restarting my sql service through the sql server configuration manager

Enable User Instances in SQL Server

I'm just starting out and trying to connect to my first database using the following string:

Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\RFPdb.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True

I get the following error during debug:

"Generating user instances in SQL Server is disabled. Use sp_configure 'user instances enabled' to generate user instances."

I've done this on every database - Master, Model, Tempdb, etc, and my database.

Still get the error. I may not be enabling it correctly. Any help would be appreciated.

The documentation suggests that I use user instances so that users without Admin rights can work with the database. Add, delete, edit data. Is this true?

is the database on your local machine or are you trying to connect through remote machies. Remote connections will not be allowed.|||Local database that I will eventually deploy with the product for local use only.|||Try using User Instance=False. User Instance is a special feature in express edition that allows normal users to have owner privilges and I guess this is not what is require here.|||

When I turn User Instance Off I get the error:

"A database with the same name exists, or specified file cannot be opened, or it is located on UNC share."

I tried downloading SSEUtil to set enable user instances but the .exe doesn't work. It flashes a command window and shuts down. Any ideas on this?

|||

is it necessary to use to attachdb. I mean any specific reason. If not try connecting using a connection string like :

connectionString="Data Source=LPXP561;Initial Catalog=AdventureWorks;Integrated Security=True

|||Lot's of other options will work. I'm trying to use the new User Instance feature of SQL Sever Express. It requires the "attachbd" usage and User Instance = True. I can't get it to work as decribed in the documentation.|||

Have got the same problem: cant connect to user instance, since it is not enabled in express version by default..

can anybody suggest how to "Use sp_configure 'user instances enabled' ", where to type it.. or whatever..

|||Hi,

Open the SQL Server Management Studio Express. This is the downloadable program in the same site where you downloaded the SQL Server 2005 express used to manage SQL Server 2005 Express.
In the query editor type this text: exec sp_configure 'user instances enabled', 1.
Then type: Reconfigure.
Then restart the SQL Server database.

Good luck.

KaBalweg?|||

Hi DDH,

I know this is now several weeks after your posted problem, but I have exactly the same issue. Did the suggested solution resolve the problem?

Mike

|||Thanks kabalweg for the str8 forward solution to this issue. It worked for me and made complete sense as soon as I read it.|||it's really nice when someone gives you a straight answer!-)
this one got me going right away!
i was trying to connect to the DBs I'm building in SQL 2005.
"exec sp_configure 'user instances enabled', 1" got me started quickly.
heck! ...i might re-visit this forum!-)
|||God bless you mate, you gave me heaven on earth. wonderful, amazing, magical. It works like .....wawwwwwww|||woot!!dude..u simply rocks!!|||

Hi, I have been doing this, and in the query editor, it says it has been changed, and then restarted the server, but it neva worked.

So i tried it again (and the query results said changed from 1 to 1), but it still hasnt worked.

Have i configured the sql server incorrectly?

Please help

PS i have been restarting my sql service through the sql server configuration manager

Enable User Instances in SQL Server

I'm just starting out and trying to connect to my first database using the following string:

Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\RFPdb.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True

I get the following error during debug:

"Generating user instances in SQL Server is disabled. Use sp_configure 'user instances enabled' to generate user instances."

I've done this on every database - Master, Model, Tempdb, etc, and my database.

Still get the error. I may not be enabling it correctly. Any help would be appreciated.

The documentation suggests that I use user instances so that users without Admin rights can work with the database. Add, delete, edit data. Is this true?

is the database on your local machine or are you trying to connect through remote machies. Remote connections will not be allowed.|||Local database that I will eventually deploy with the product for local use only.|||Try using User Instance=False. User Instance is a special feature in express edition that allows normal users to have owner privilges and I guess this is not what is require here.|||

When I turn User Instance Off I get the error:

"A database with the same name exists, or specified file cannot be opened, or it is located on UNC share."

I tried downloading SSEUtil to set enable user instances but the .exe doesn't work. It flashes a command window and shuts down. Any ideas on this?

|||

is it necessary to use to attachdb. I mean any specific reason. If not try connecting using a connection string like :

connectionString="Data Source=LPXP561;Initial Catalog=AdventureWorks;Integrated Security=True

|||Lot's of other options will work. I'm trying to use the new User Instance feature of SQL Sever Express. It requires the "attachbd" usage and User Instance = True. I can't get it to work as decribed in the documentation.|||

Have got the same problem: cant connect to user instance, since it is not enabled in express version by default..

can anybody suggest how to "Use sp_configure 'user instances enabled' ", where to type it.. or whatever..

|||Hi,

Open the SQL Server Management Studio Express. This is the downloadable program in the same site where you downloaded the SQL Server 2005 express used to manage SQL Server 2005 Express.
In the query editor type this text: exec sp_configure 'user instances enabled', 1.
Then type: Reconfigure.
Then restart the SQL Server database.

Good luck.

KaBalweg?|||

Hi DDH,

I know this is now several weeks after your posted problem, but I have exactly the same issue. Did the suggested solution resolve the problem?

Mike

|||Thanks kabalweg for the str8 forward solution to this issue. It worked for me and made complete sense as soon as I read it.|||it's really nice when someone gives you a straight answer!-)
this one got me going right away!
i was trying to connect to the DBs I'm building in SQL 2005.
"exec sp_configure 'user instances enabled', 1" got me started quickly.
heck! ...i might re-visit this forum!-)
|||God bless you mate, you gave me heaven on earth. wonderful, amazing, magical. It works like .....wawwwwwww|||woot!!dude..u simply rocks!!|||

Hi, I have been doing this, and in the query editor, it says it has been changed, and then restarted the server, but it neva worked.

So i tried it again (and the query results said changed from 1 to 1), but it still hasnt worked.

Have i configured the sql server incorrectly?

Please help

PS i have been restarting my sql service through the sql server configuration manager

Enable User Instances in SQL Server

I'm just starting out and trying to connect to my first database using the following string:

Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\RFPdb.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True

I get the following error during debug:

"Generating user instances in SQL Server is disabled. Use sp_configure 'user instances enabled' to generate user instances."

I've done this on every database - Master, Model, Tempdb, etc, and my database.

Still get the error. I may not be enabling it correctly. Any help would be appreciated.

The documentation suggests that I use user instances so that users without Admin rights can work with the database. Add, delete, edit data. Is this true?

is the database on your local machine or are you trying to connect through remote machies. Remote connections will not be allowed.|||Local database that I will eventually deploy with the product for local use only.|||Try using User Instance=False. User Instance is a special feature in express edition that allows normal users to have owner privilges and I guess this is not what is require here.|||

When I turn User Instance Off I get the error:

"A database with the same name exists, or specified file cannot be opened, or it is located on UNC share."

I tried downloading SSEUtil to set enable user instances but the .exe doesn't work. It flashes a command window and shuts down. Any ideas on this?

|||

is it necessary to use to attachdb. I mean any specific reason. If not try connecting using a connection string like :

connectionString="Data Source=LPXP561;Initial Catalog=AdventureWorks;Integrated Security=True

|||Lot's of other options will work. I'm trying to use the new User Instance feature of SQL Sever Express. It requires the "attachbd" usage and User Instance = True. I can't get it to work as decribed in the documentation.|||

Have got the same problem: cant connect to user instance, since it is not enabled in express version by default..

can anybody suggest how to "Use sp_configure 'user instances enabled' ", where to type it.. or whatever..

|||Hi,

Open the SQL Server Management Studio Express. This is the downloadable program in the same site where you downloaded the SQL Server 2005 express used to manage SQL Server 2005 Express.
In the query editor type this text: exec sp_configure 'user instances enabled', 1.
Then type: Reconfigure.
Then restart the SQL Server database.

Good luck.

KaBalweg?|||

Hi DDH,

I know this is now several weeks after your posted problem, but I have exactly the same issue. Did the suggested solution resolve the problem?

Mike

|||Thanks kabalweg for the str8 forward solution to this issue. It worked for me and made complete sense as soon as I read it.|||it's really nice when someone gives you a straight answer!-)
this one got me going right away!
i was trying to connect to the DBs I'm building in SQL 2005.
"exec sp_configure 'user instances enabled', 1" got me started quickly.
heck! ...i might re-visit this forum!-)
|||God bless you mate, you gave me heaven on earth. wonderful, amazing, magical. It works like .....wawwwwwww|||woot!!dude..u simply rocks!!|||

Hi, I have been doing this, and in the query editor, it says it has been changed, and then restarted the server, but it neva worked.

So i tried it again (and the query results said changed from 1 to 1), but it still hasnt worked.

Have i configured the sql server incorrectly?

Please help

PS i have been restarting my sql service through the sql server configuration manager

Enable Quoted Identifiers=0 System.Data.SqlClient.sqlConnection

Hi there,
this is probably a common question,
how can i include the "enabled Quoted Identifiers" settings in the
connection string of the sqlconnection.
on trawling through web pages i have identified
1 .that by default the quoted identifiers in SQL Server 2000 is by default
set to on
2. changing the status of quoted identifiers at the catalog level has no
affect at the connection level
since i have 1 global connection string
I would be grateful if anyone could help me in setting up this connection
stringHello,
As I know, you have to run SET QUOTED_IDENTIFIER { ON | OFF } via
connection and there is no such option to set this option in connection
string to SQL server.
Also, if you use odbc DSN to connect to SQL Server, you have the option to
configure this option in SQL Server DSN.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner 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.
--
>Thread-Topic: Enable Quoted Identifiers=0
System.Data.SqlClient.sqlConnection
>thread-index: AcZaMC8+PO+Q1I/hQvOGfgZaOBq/Mw==
>X-WBNR-Posting-Host: 83.141.82.45
>From: examnotes <cathiec@.newsgroups.nospam>
>Subject: Enable Quoted Identifiers=0 System.Data.SqlClient.sqlConnection
>Date: Fri, 7 Apr 2006 03:44:02 -0700
>Lines: 18
>Message-ID: <D8216A73-DDA7-417E-B53A-54889FDF94AF@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
> charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
>Newsgroups: microsoft.public.sqlserver.connect
>Path: TK2MSFTNGXA01.phx.gbl
>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.connect:47316
>NNTP-Posting-Host: TK2MSFTNGXA01.phx.gbl 10.40.2.250
>X-Tomcat-NG: microsoft.public.sqlserver.connect
>Hi there,
>this is probably a common question,
>how can i include the "enabled Quoted Identifiers" settings in the
>connection string of the sqlconnection.
>on trawling through web pages i have identified
>1 .that by default the quoted identifiers in SQL Server 2000 is by default
>set to on
>2. changing the status of quoted identifiers at the catalog level has no
>affect at the connection level
>since i have 1 global connection string
>I would be grateful if anyone could help me in setting up this connection
>string
>
>

Empty string

MyCol is a varchar(1) column.
SELECT MyCol + '.' FROM tbl
returns ' .' (space+dot), when MyCol contains empty string.
Why? How can I make it to return '.' (with no space)?
I use SQL Server 2000, default settings.
Thanks.
First of all, why are you using varchar(1)? You're wasting an extra byte
per row for nothing... Use CHAR(1).
Second, how are you determining that the output is ' .'? Is this happening
client-side? I cannot reproduce what you're talking about, using the
following:
declare @.table table(blah varchar(1))
insert @.table values ('')
select len(blah + '.')
from @.table
Can you post code to reproduce your problem?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Vik" <viktorum@.==hotmail.com==> wrote in message
news:uHeX%23XCyEHA.352@.TK2MSFTNGP14.phx.gbl...
> MyCol is a varchar(1) column.
> SELECT MyCol + '.' FROM tbl
> returns ' .' (space+dot), when MyCol contains empty string.
> Why? How can I make it to return '.' (with no space)?
> I use SQL Server 2000, default settings.
> Thanks.
>
|||>> Can you post code to reproduce your problem?
INSERT @.table SELECT SPACE(1) ;
Anith
|||You can use any of LTRIM, REPLACE, SUBSTRING, STUFF, CASE, RIGHT or some
other string function to get this done. See the topic String functions in
SQL Server Books Online for details.
Anith
|||> when MyCol contains empty string.
What is your definition of an empty string? Can you show a repro? Like
Adam, I can't figure out whow you're doing this, unless you have a different
definition of "empty string" than I. I couldn't yield your result unless I
insert a space.
set nocount on
set concat_null_yields_null on
set ansi_padding off
create table #t(blah varchar(1))
insert #t values (SPACE(0))
insert #t values ('')
insert #t values (SPACE(1))
insert #t values (' ')
insert #t values (NULL)
select blah+'.', len(blah + '.') from #t
drop table #t
go
set concat_null_yields_null off
set ansi_padding off
create table #t(blah varchar(1))
insert #t values (SPACE(0))
insert #t values ('')
insert #t values (SPACE(1))
insert #t values (' ')
select blah+'.', len(blah + '.') from #t
drop table #t
go
set concat_null_yields_null on
set ansi_padding on
create table #t(blah varchar(1))
insert #t values (SPACE(0))
insert #t values ('')
insert #t values (SPACE(1))
insert #t values (' ')
insert #t values (NULL)
select blah+'.', len(blah + '.') from #t
drop table #t
go
set concat_null_yields_null off
set ansi_padding on
create table #t(blah varchar(1))
insert #t values (SPACE(0))
insert #t values ('')
insert #t values (SPACE(1))
insert #t values (' ')
select blah+'.', len(blah + '.') from #t
drop table #t
go
http://www.aspfaq.com/
(Reverse address to reply.)
|||I believe the OP said it was an empty string?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:uoK1udCyEHA.1956@.TK2MSFTNGP14.phx.gbl...
> INSERT @.table SELECT SPACE(1) ;
> --
> Anith
>
|||I asked for a definition of empty string. To me, that's SPACE(0), not
SPACE(1).
http://www.aspfaq.com/
(Reverse address to reply.)
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:#KGvagCyEHA.3400@.TK2MSFTNGP10.phx.gbl...
> I believe the OP said it was an empty string?
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Anith Sen" <anith@.bizdatasolutions.com> wrote in message
> news:uoK1udCyEHA.1956@.TK2MSFTNGP14.phx.gbl...
>
|||It appeared that MyCol contained a space instead of empty string. What
confused me was that LEN(MyCol) returned 0, when MyCol=' ' (one space).
So, a question is why LEN(' ') returns 0 and DATALENGTH(' ') returns 1?
Thanks.
"Vik" <viktorum@.==hotmail.com==> wrote in message
news:uHeX%23XCyEHA.352@.TK2MSFTNGP14.phx.gbl...
> MyCol is a varchar(1) column.
> SELECT MyCol + '.' FROM tbl
> returns ' .' (space+dot), when MyCol contains empty string.
> Why? How can I make it to return '.' (with no space)?
> I use SQL Server 2000, default settings.
> Thanks.
>
|||From BOL:
LEN
Returns the number of characters, rather than the number of bytes, of the
given string expression, excluding trailing blanks.
I agree, that can get confusing!
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Vik" <viktorum@.==hotmail.com==> wrote in message
news:OSjkejCyEHA.4064@.TK2MSFTNGP10.phx.gbl...
> It appeared that MyCol contained a space instead of empty string. What
> confused me was that LEN(MyCol) returned 0, when MyCol=' ' (one space).
> So, a question is why LEN(' ') returns 0 and DATALENGTH(' ') returns 1?
> Thanks.
> "Vik" <viktorum@.==hotmail.com==> wrote in message
> news:uHeX%23XCyEHA.352@.TK2MSFTNGP14.phx.gbl...
>
|||Here is a reason to use VARCHAR(1) instead of CHAR(1): when ANSI_PADDING is
set on. CHAR(1) will store SPACE(0) as space, whereas VARCHAR(1) will store
SPACE(0) as an empty string.
Sincerely,
Anthony Thomas

"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OpZ3DaCyEHA.1452@.TK2MSFTNGP11.phx.gbl...
First of all, why are you using varchar(1)? You're wasting an extra byte
per row for nothing... Use CHAR(1).
Second, how are you determining that the output is ' .'? Is this happening
client-side? I cannot reproduce what you're talking about, using the
following:
declare @.table table(blah varchar(1))
insert @.table values ('')
select len(blah + '.')
from @.table
Can you post code to reproduce your problem?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Vik" <viktorum@.==hotmail.com==> wrote in message
news:uHeX%23XCyEHA.352@.TK2MSFTNGP14.phx.gbl...
> MyCol is a varchar(1) column.
> SELECT MyCol + '.' FROM tbl
> returns ' .' (space+dot), when MyCol contains empty string.
> Why? How can I make it to return '.' (with no space)?
> I use SQL Server 2000, default settings.
> Thanks.
>

Empty string

MyCol is a varchar(1) column.
SELECT MyCol + '.' FROM tbl
returns ' .' (space+dot), when MyCol contains empty string.
Why? How can I make it to return '.' (with no space)?
I use SQL Server 2000, default settings.
Thanks.First of all, why are you using varchar(1)? You're wasting an extra byte
per row for nothing... Use CHAR(1).
Second, how are you determining that the output is ' .'? Is this happening
client-side? I cannot reproduce what you're talking about, using the
following:
declare @.table table(blah varchar(1))
insert @.table values ('')
select len(blah + '.')
from @.table
Can you post code to reproduce your problem?
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Vik" <viktorum@.==hotmail.com==> wrote in message
news:uHeX%23XCyEHA.352@.TK2MSFTNGP14.phx.gbl...
> MyCol is a varchar(1) column.
> SELECT MyCol + '.' FROM tbl
> returns ' .' (space+dot), when MyCol contains empty string.
> Why? How can I make it to return '.' (with no space)?
> I use SQL Server 2000, default settings.
> Thanks.
>|||You can use any of LTRIM, REPLACE, SUBSTRING, STUFF, CASE, RIGHT or some
other string function to get this done. See the topic String functions in
SQL Server Books Online for details.
--
Anith|||>> Can you post code to reproduce your problem?
INSERT @.table SELECT SPACE(1) ;
--
Anith|||> when MyCol contains empty string.
What is your definition of an empty string? Can you show a repro? Like
Adam, I can't figure out whow you're doing this, unless you have a different
definition of "empty string" than I. I couldn't yield your result unless I
insert a space.
set nocount on
set concat_null_yields_null on
set ansi_padding off
create table #t(blah varchar(1))
insert #t values (SPACE(0))
insert #t values ('')
insert #t values (SPACE(1))
insert #t values (' ')
insert #t values (NULL)
select blah+'.', len(blah + '.') from #t
drop table #t
go
set concat_null_yields_null off
set ansi_padding off
create table #t(blah varchar(1))
insert #t values (SPACE(0))
insert #t values ('')
insert #t values (SPACE(1))
insert #t values (' ')
select blah+'.', len(blah + '.') from #t
drop table #t
go
set concat_null_yields_null on
set ansi_padding on
create table #t(blah varchar(1))
insert #t values (SPACE(0))
insert #t values ('')
insert #t values (SPACE(1))
insert #t values (' ')
insert #t values (NULL)
select blah+'.', len(blah + '.') from #t
drop table #t
go
set concat_null_yields_null off
set ansi_padding on
create table #t(blah varchar(1))
insert #t values (SPACE(0))
insert #t values ('')
insert #t values (SPACE(1))
insert #t values (' ')
select blah+'.', len(blah + '.') from #t
drop table #t
go
--
http://www.aspfaq.com/
(Reverse address to reply.)|||I believe the OP said it was an empty string?
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:uoK1udCyEHA.1956@.TK2MSFTNGP14.phx.gbl...
> >> Can you post code to reproduce your problem?
> INSERT @.table SELECT SPACE(1) ;
> --
> Anith
>|||I asked for a definition of empty string. To me, that's SPACE(0), not
SPACE(1).
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:#KGvagCyEHA.3400@.TK2MSFTNGP10.phx.gbl...
> I believe the OP said it was an empty string?
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Anith Sen" <anith@.bizdatasolutions.com> wrote in message
> news:uoK1udCyEHA.1956@.TK2MSFTNGP14.phx.gbl...
> > >> Can you post code to reproduce your problem?
> >
> > INSERT @.table SELECT SPACE(1) ;
> >
> > --
> > Anith
> >
> >
>|||It appeared that MyCol contained a space instead of empty string. What
confused me was that LEN(MyCol) returned 0, when MyCol=' ' (one space).
So, a question is why LEN(' ') returns 0 and DATALENGTH(' ') returns 1?
Thanks.
"Vik" <viktorum@.==hotmail.com==> wrote in message
news:uHeX%23XCyEHA.352@.TK2MSFTNGP14.phx.gbl...
> MyCol is a varchar(1) column.
> SELECT MyCol + '.' FROM tbl
> returns ' .' (space+dot), when MyCol contains empty string.
> Why? How can I make it to return '.' (with no space)?
> I use SQL Server 2000, default settings.
> Thanks.
>|||From BOL:
LEN
Returns the number of characters, rather than the number of bytes, of the
given string expression, excluding trailing blanks.
I agree, that can get confusing!
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Vik" <viktorum@.==hotmail.com==> wrote in message
news:OSjkejCyEHA.4064@.TK2MSFTNGP10.phx.gbl...
> It appeared that MyCol contained a space instead of empty string. What
> confused me was that LEN(MyCol) returned 0, when MyCol=' ' (one space).
> So, a question is why LEN(' ') returns 0 and DATALENGTH(' ') returns 1?
> Thanks.
> "Vik" <viktorum@.==hotmail.com==> wrote in message
> news:uHeX%23XCyEHA.352@.TK2MSFTNGP14.phx.gbl...
> > MyCol is a varchar(1) column.
> >
> > SELECT MyCol + '.' FROM tbl
> >
> > returns ' .' (space+dot), when MyCol contains empty string.
> > Why? How can I make it to return '.' (with no space)?
> >
> > I use SQL Server 2000, default settings.
> >
> > Thanks.
> >
> >
>|||Here is a reason to use VARCHAR(1) instead of CHAR(1): when ANSI_PADDING is
set on. CHAR(1) will store SPACE(0) as space, whereas VARCHAR(1) will store
SPACE(0) as an empty string.
Sincerely,
Anthony Thomas
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OpZ3DaCyEHA.1452@.TK2MSFTNGP11.phx.gbl...
First of all, why are you using varchar(1)? You're wasting an extra byte
per row for nothing... Use CHAR(1).
Second, how are you determining that the output is ' .'? Is this happening
client-side? I cannot reproduce what you're talking about, using the
following:
declare @.table table(blah varchar(1))
insert @.table values ('')
select len(blah + '.')
from @.table
Can you post code to reproduce your problem?
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Vik" <viktorum@.==hotmail.com==> wrote in message
news:uHeX%23XCyEHA.352@.TK2MSFTNGP14.phx.gbl...
> MyCol is a varchar(1) column.
> SELECT MyCol + '.' FROM tbl
> returns ' .' (space+dot), when MyCol contains empty string.
> Why? How can I make it to return '.' (with no space)?
> I use SQL Server 2000, default settings.
> Thanks.
>|||"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OpZ3DaCyEHA.1452@.TK2MSFTNGP11.phx.gbl...
> First of all, why are you using varchar(1)? You're wasting an extra byte
> per row for nothing... Use CHAR(1).
>
Originally MyCol was Char(1) and contained Nulls. Then it appeared that this
column should be used in a join, so I had to get rid of Nulls.
I also have a few Web pages (in ASP.NET) built in assumption that MyCol is
not blank. So, I decided to use Varchar(1) and an empty string for MyCol
instead of using a space and updating the queries or code with a Trim
function.
> Second, how are you determining that the output is ' .'? Is this
happening
> client-side? I cannot reproduce what you're talking about, using the
> following:
>
> declare @.table table(blah varchar(1))
> insert @.table values ('')
> select len(blah + '.')
> from @.table
>
> Can you post code to reproduce your problem?
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Vik" <viktorum@.==hotmail.com==> wrote in message
> news:uHeX%23XCyEHA.352@.TK2MSFTNGP14.phx.gbl...
> > MyCol is a varchar(1) column.
> >
> > SELECT MyCol + '.' FROM tbl
> >
> > returns ' .' (space+dot), when MyCol contains empty string.
> > Why? How can I make it to return '.' (with no space)?
> >
> > I use SQL Server 2000, default settings.
> >
> > Thanks.
> >
> >
>|||Is your database set to a compatibility level of 65, perhaps? SQL
Server 6.5 could not store an empty string, if I recall correctly.
Otherwise, how do you know MyCol is empty? This behavior will result if
MyCol contains the value ' '. You can try rtrim(MyCol) to trim any
trailing spaces, but it would be best to find out what is going on.
Steve Kass
Drew University
Vik wrote:
>MyCol is a varchar(1) column.
>SELECT MyCol + '.' FROM tbl
>returns ' .' (space+dot), when MyCol contains empty string.
>Why? How can I make it to return '.' (with no space)?
>I use SQL Server 2000, default settings.
>Thanks.
>
>|||Vik wrote:
> It appeared that MyCol contained a space instead of empty string. What
> confused me was that LEN(MyCol) returned 0, when MyCol=' ' (one
> space).
> So, a question is why LEN(' ') returns 0 and DATALENGTH(' ') returns
> 1?
> Thanks.
> "Vik" <viktorum@.==hotmail.com==> wrote in message
> news:uHeX%23XCyEHA.352@.TK2MSFTNGP14.phx.gbl...
>> MyCol is a varchar(1) column.
>> SELECT MyCol + '.' FROM tbl
>> returns ' .' (space+dot), when MyCol contains empty string.
>> Why? How can I make it to return '.' (with no space)?
>> I use SQL Server 2000, default settings.
>> Thanks.
Use DATALENGTH() for the real stored length.
create table #testing(col1 varchar(1))
insert into #testing values (space(1))
select len(col1) as 'len', datalength(col1) as 'datalength'
from #testing
len datalength
-- --
0 1
David Gugick
Imceda Software
www.imceda.com

Empty string

MyCol is a varchar(1) column.
SELECT MyCol + '.' FROM tbl
returns ' .' (space+dot), when MyCol contains empty string.
Why? How can I make it to return '.' (with no space)?
I use SQL Server 2000, default settings.
Thanks.First of all, why are you using varchar(1)? You're wasting an extra byte
per row for nothing... Use CHAR(1).
Second, how are you determining that the output is ' .'? Is this happening
client-side? I cannot reproduce what you're talking about, using the
following:
declare @.table table(blah varchar(1))
insert @.table values ('')
select len(blah + '.')
from @.table
Can you post code to reproduce your problem?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Vik" <viktorum@.==hotmail.com==> wrote in message
news:uHeX%23XCyEHA.352@.TK2MSFTNGP14.phx.gbl...
> MyCol is a varchar(1) column.
> SELECT MyCol + '.' FROM tbl
> returns ' .' (space+dot), when MyCol contains empty string.
> Why? How can I make it to return '.' (with no space)?
> I use SQL Server 2000, default settings.
> Thanks.
>|||>> Can you post code to reproduce your problem?
INSERT @.table SELECT SPACE(1) ;
Anith|||You can use any of LTRIM, REPLACE, SUBSTRING, STUFF, CASE, RIGHT or some
other string function to get this done. See the topic String functions in
SQL Server Books Online for details.
Anith|||> when MyCol contains empty string.
What is your definition of an empty string? Can you show a repro? Like
Adam, I can't figure out whow you're doing this, unless you have a different
definition of "empty string" than I. I couldn't yield your result unless I
insert a space.
set nocount on
set concat_null_yields_null on
set ansi_padding off
create table #t(blah varchar(1))
insert #t values (SPACE(0))
insert #t values ('')
insert #t values (SPACE(1))
insert #t values (' ')
insert #t values (NULL)
select blah+'.', len(blah + '.') from #t
drop table #t
go
set concat_null_yields_null off
set ansi_padding off
create table #t(blah varchar(1))
insert #t values (SPACE(0))
insert #t values ('')
insert #t values (SPACE(1))
insert #t values (' ')
select blah+'.', len(blah + '.') from #t
drop table #t
go
set concat_null_yields_null on
set ansi_padding on
create table #t(blah varchar(1))
insert #t values (SPACE(0))
insert #t values ('')
insert #t values (SPACE(1))
insert #t values (' ')
insert #t values (NULL)
select blah+'.', len(blah + '.') from #t
drop table #t
go
set concat_null_yields_null off
set ansi_padding on
create table #t(blah varchar(1))
insert #t values (SPACE(0))
insert #t values ('')
insert #t values (SPACE(1))
insert #t values (' ')
select blah+'.', len(blah + '.') from #t
drop table #t
go
http://www.aspfaq.com/
(Reverse address to reply.)|||I believe the OP said it was an empty string?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:uoK1udCyEHA.1956@.TK2MSFTNGP14.phx.gbl...
> INSERT @.table SELECT SPACE(1) ;
> --
> Anith
>|||I asked for a definition of empty string. To me, that's SPACE(0), not
SPACE(1).
http://www.aspfaq.com/
(Reverse address to reply.)
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:#KGvagCyEHA.3400@.TK2MSFTNGP10.phx.gbl...
> I believe the OP said it was an empty string?
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Anith Sen" <anith@.bizdatasolutions.com> wrote in message
> news:uoK1udCyEHA.1956@.TK2MSFTNGP14.phx.gbl...
>|||It appeared that MyCol contained a space instead of empty string. What
confused me was that LEN(MyCol) returned 0, when MyCol=' ' (one space).
So, a question is why LEN(' ') returns 0 and DATALENGTH(' ') returns 1?
Thanks.
"Vik" <viktorum@.==hotmail.com==> wrote in message
news:uHeX%23XCyEHA.352@.TK2MSFTNGP14.phx.gbl...
> MyCol is a varchar(1) column.
> SELECT MyCol + '.' FROM tbl
> returns ' .' (space+dot), when MyCol contains empty string.
> Why? How can I make it to return '.' (with no space)?
> I use SQL Server 2000, default settings.
> Thanks.
>|||From BOL:
LEN
Returns the number of characters, rather than the number of bytes, of the
given string expression, excluding trailing blanks.
I agree, that can get confusing!
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Vik" <viktorum@.==hotmail.com==> wrote in message
news:OSjkejCyEHA.4064@.TK2MSFTNGP10.phx.gbl...
> It appeared that MyCol contained a space instead of empty string. What
> confused me was that LEN(MyCol) returned 0, when MyCol=' ' (one space).
> So, a question is why LEN(' ') returns 0 and DATALENGTH(' ') returns 1?
> Thanks.
> "Vik" <viktorum@.==hotmail.com==> wrote in message
> news:uHeX%23XCyEHA.352@.TK2MSFTNGP14.phx.gbl...
>|||Here is a reason to use VARCHAR(1) instead of CHAR(1): when ANSI_PADDING is
set on. CHAR(1) will store SPACE(0) as space, whereas VARCHAR(1) will store
SPACE(0) as an empty string.
Sincerely,
Anthony Thomas
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OpZ3DaCyEHA.1452@.TK2MSFTNGP11.phx.gbl...
First of all, why are you using varchar(1)? You're wasting an extra byte
per row for nothing... Use CHAR(1).
Second, how are you determining that the output is ' .'? Is this happening
client-side? I cannot reproduce what you're talking about, using the
following:
declare @.table table(blah varchar(1))
insert @.table values ('')
select len(blah + '.')
from @.table
Can you post code to reproduce your problem?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Vik" <viktorum@.==hotmail.com==> wrote in message
news:uHeX%23XCyEHA.352@.TK2MSFTNGP14.phx.gbl...
> MyCol is a varchar(1) column.
> SELECT MyCol + '.' FROM tbl
> returns ' .' (space+dot), when MyCol contains empty string.
> Why? How can I make it to return '.' (with no space)?
> I use SQL Server 2000, default settings.
> Thanks.
>