hot backup: March 2012

Friday, March 30, 2012

How can I 'copy' a SQL Express database to SQL Everywhere?

Now that SQL Everywhere can be used on the desktop, how can I create an
'Everywhere' version of a database that I have set up in Express or 2000?
Clearly, as' Everywhere' is a subset, the mobile version may not be
identical, but there must be a way to create the tables, columns and indexes.
Thanks,
--
John AustinHi,
Take a look into the SQLCMD utility in books online.
Thanks
Hari
SQL Server MVP
"John Austin" wrote:
> Now that SQL Everywhere can be used on the desktop, how can I create an
> 'Everywhere' version of a database that I have set up in Express or 2000?
> Clearly, as' Everywhere' is a subset, the mobile version may not be
> identical, but there must be a way to create the tables, columns and indexes.
> Thanks,
> --
> John Austin|||In management studio you should be able to script any objects you want. In
most cases the DDL commands are the same or should work with minor
modifications.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"John Austin" <John.Austin@.nospam.nospam> wrote in message
news:BDE7588B-9EA7-4B2B-B1CC-4FB89FBF7109@.microsoft.com...
> Now that SQL Everywhere can be used on the desktop, how can I create an
> 'Everywhere' version of a database that I have set up in Express or 2000?
> Clearly, as' Everywhere' is a subset, the mobile version may not be
> identical, but there must be a way to create the tables, columns and
> indexes.
> Thanks,
> --
> John Austin|||Dear John,
From your description, I understand that:
You wanted to know how to create a SQL Everywhere database that you have
created in SQL Express or 2000.
If I have misunderstood, please let me know.
Unfortunately by now SQL Server Everywhere Edition support service may not
be available in newsgroup. For your concerns, I recommend that you contact
Microsoft Customer Support Services (CSS) via telephone so that a dedicated
Support Professional can assist you in a more efficient manner. Please be
advised that contacting phone support will be a charged call.
To obtain the phone numbers for specific technology request please take a
look at the web site listed below.
http://support.microsoft.com/default.aspx?scid=fh;EN-US;PHONENUMBERS
If you are outside the US please see http://support.microsoft.com for
regional support phone numbers.
Also, I would like to share you my experiences on SQL Server 2005
Everywhere Edition.
From SQL Server 2005 Everywhere Edition Books Online (How To (SQL Server
Everywhere) - Performing Common Database Tasks), we can see there are five
ways to create an Everywhere database:
1. Create a SQL Server Everywhere database on the server
2. Create a SQL Server Everywhere Database on a Connected Device
3. Create a SQL Server Everywhere Database by Using the Engine Object
(Programmatically)
4. Create a SQL Server Everywhere Database by Using the Replication Object
(Programmatically)
5. Create a Database by Using OLE DB (Programmatically)
Practically I just tried the first and the third method, but I recommend
that you use the third method because I can just find "SQL Server Mobile"
in the "server type" list, not "SQL Server Everywhere".
You can use T-SQL as well as SQL Server 2000 and other editions to create
tables, columns and indexes. For more information, you can refer to SQL
Server 2005 Everywhere Edition Books Online (Especially the How To chapter).
If you have any other questions or concerns, please feel free to let me
know. It's my pleasure to be of assistance.
Charles Wang
Microsoft Online Partner Support
PLEASE NOTE: The partner managed newsgroups are provided
to assist with break/fix issues and simple how to questions.
We also love to hear your product feedback!
Let us know what you think by posting
- from the web interface: Partner Feedback
- from your newsreader:
microsoft.private.directaccess.partnerfeedback.
We look forward to hearing from you!
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Thanks, I will try your suggestion. My first attempt was to script a table
from the Express database and paste it into the SQL window for the Everywhere
database in VS 2005, but the script would not execute.
--
John Austin
"Charles Wang[MSFT]" wrote:
> Dear John,
> From your description, I understand that:
> You wanted to know how to create a SQL Everywhere database that you have
> created in SQL Express or 2000.
> If I have misunderstood, please let me know.
> Unfortunately by now SQL Server Everywhere Edition support service may not
> be available in newsgroup. For your concerns, I recommend that you contact
> Microsoft Customer Support Services (CSS) via telephone so that a dedicated
> Support Professional can assist you in a more efficient manner. Please be
> advised that contacting phone support will be a charged call.
> To obtain the phone numbers for specific technology request please take a
> look at the web site listed below.
> http://support.microsoft.com/default.aspx?scid=fh;EN-US;PHONENUMBERS
> If you are outside the US please see http://support.microsoft.com for
> regional support phone numbers.
> Also, I would like to share you my experiences on SQL Server 2005
> Everywhere Edition.
> From SQL Server 2005 Everywhere Edition Books Online (How To (SQL Server
> Everywhere) - Performing Common Database Tasks), we can see there are five
> ways to create an Everywhere database:
> 1. Create a SQL Server Everywhere database on the server
> 2. Create a SQL Server Everywhere Database on a Connected Device
> 3. Create a SQL Server Everywhere Database by Using the Engine Object
> (Programmatically)
> 4. Create a SQL Server Everywhere Database by Using the Replication Object
> (Programmatically)
> 5. Create a Database by Using OLE DB (Programmatically)
> Practically I just tried the first and the third method, but I recommend
> that you use the third method because I can just find "SQL Server Mobile"
> in the "server type" list, not "SQL Server Everywhere".
> You can use T-SQL as well as SQL Server 2000 and other editions to create
> tables, columns and indexes. For more information, you can refer to SQL
> Server 2005 Everywhere Edition Books Online (Especially the How To chapter).
> If you have any other questions or concerns, please feel free to let me
> know. It's my pleasure to be of assistance.
> Charles Wang
> Microsoft Online Partner Support
> PLEASE NOTE: The partner managed newsgroups are provided
> to assist with break/fix issues and simple how to questions.
> We also love to hear your product feedback!
> Let us know what you think by posting
> - from the web interface: Partner Feedback
> - from your newsreader:
> microsoft.private.directaccess.partnerfeedback.
> We look forward to hearing from you!
> ======================================================> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ======================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
> ======================================================>|||Dear John,
Appreciate your response.
I look forward to your resolving this issue.
Please feel free to let me know if you have any other questions or concerns.
Sincerely,
Charles Wang
Microsoft Online Community Support|||I guess what would be really useful, would be a utility that generates an
everywhere compatible script from a SQL Server table.
The problem with using script is that the number of deletions and amendments
needed are quite huge.
--
John Austin
"Charles Wang[MSFT]" wrote:
> Dear John,
> Appreciate your response.
> I look forward to your resolving this issue.
> Please feel free to let me know if you have any other questions or concerns.
> Sincerely,
> Charles Wang
> Microsoft Online Community Support
>|||Dear John,
As you mentioned in email, you are just surprised that Microsoft have no
such migration tool.
I would like that you could give Microsoft feedback which will route to SQL
team so that this tool will be released in future.
You can submit your feedback via:
http://connect.microsoft.com/feedback/default.aspx?SiteID=68
Note: please logon before submitting a feedback.
If you have any other questions or concerns, please feel free to let me
know. It's always my pleasure to be of assistance.
Sincerely,
Charles Wang
Microsoft Online Community Support

How can I 'copy' a SQL Express database to SQL Everywhere?

Now that SQL Everywhere can be used on the desktop, how can I create an
'Everywhere' version of a database that I have set up in Express or 2000?
Clearly, as' Everywhere' is a subset, the mobile version may not be
identical, but there must be a way to create the tables, columns and indexes
.
Thanks,
--
John AustinHi,
Take a look into the SQLCMD utility in books online.
Thanks
Hari
SQL Server MVP
"John Austin" wrote:

> Now that SQL Everywhere can be used on the desktop, how can I create an
> 'Everywhere' version of a database that I have set up in Express or 2000?
> Clearly, as' Everywhere' is a subset, the mobile version may not be
> identical, but there must be a way to create the tables, columns and index
es.
> Thanks,
> --
> John Austin|||In management studio you should be able to script any objects you want. In
most cases the DDL commands are the same or should work with minor
modifications.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"John Austin" <John.Austin@.nospam.nospam> wrote in message
news:BDE7588B-9EA7-4B2B-B1CC-4FB89FBF7109@.microsoft.com...
> Now that SQL Everywhere can be used on the desktop, how can I create an
> 'Everywhere' version of a database that I have set up in Express or 2000?
> Clearly, as' Everywhere' is a subset, the mobile version may not be
> identical, but there must be a way to create the tables, columns and
> indexes.
> Thanks,
> --
> John Austin|||Dear John,
From your description, I understand that:
You wanted to know how to create a SQL Everywhere database that you have
created in SQL Express or 2000.
If I have misunderstood, please let me know.
Unfortunately by now SQL Server Everywhere Edition support service may not
be available in newsgroup. For your concerns, I recommend that you contact
Microsoft Customer Support Services (CSS) via telephone so that a dedicated
Support Professional can assist you in a more efficient manner. Please be
advised that contacting phone support will be a charged call.
To obtain the phone numbers for specific technology request please take a
look at the web site listed below.
http://support.microsoft.com/defaul...US;PHONENUMBERS
If you are outside the US please see http://support.microsoft.com for
regional support phone numbers.
Also, I would like to share you my experiences on SQL Server 2005
Everywhere Edition.
From SQL Server 2005 Everywhere Edition Books Online (How To (SQL Server
Everywhere) - Performing Common Database Tasks), we can see there are five
ways to create an Everywhere database:
1. Create a SQL Server Everywhere database on the server
2. Create a SQL Server Everywhere Database on a Connected Device
3. Create a SQL Server Everywhere Database by Using the Engine Object
(Programmatically)
4. Create a SQL Server Everywhere Database by Using the Replication Object
(Programmatically)
5. Create a Database by Using OLE DB (Programmatically)
Practically I just tried the first and the third method, but I recommend
that you use the third method because I can just find "SQL Server Mobile"
in the "server type" list, not "SQL Server Everywhere".
You can use T-SQL as well as SQL Server 2000 and other editions to create
tables, columns and indexes. For more information, you can refer to SQL
Server 2005 Everywhere Edition Books Online (Especially the How To chapter).
If you have any other questions or concerns, please feel free to let me
know. It's my pleasure to be of assistance.
Charles Wang
Microsoft Online Partner Support
PLEASE NOTE: The partner managed newsgroups are provided
to assist with break/fix issues and simple how to questions.
We also love to hear your product feedback!
Let us know what you think by posting
- from the web interface: Partner Feedback
- from your newsreader:
microsoft.private.directaccess.partnerfeedback.
We look forward to hearing from you!
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||Thanks, I will try your suggestion. My first attempt was to script a table
from the Express database and paste it into the SQL window for the Everywher
e
database in VS 2005, but the script would not execute.
--
John Austin
"Charles Wang[MSFT]" wrote:

> Dear John,
> From your description, I understand that:
> You wanted to know how to create a SQL Everywhere database that you have
> created in SQL Express or 2000.
> If I have misunderstood, please let me know.
> Unfortunately by now SQL Server Everywhere Edition support service may not
> be available in newsgroup. For your concerns, I recommend that you contact
> Microsoft Customer Support Services (CSS) via telephone so that a dedicate
d
> Support Professional can assist you in a more efficient manner. Please be
> advised that contacting phone support will be a charged call.
> To obtain the phone numbers for specific technology request please take a
> look at the web site listed below.
> http://support.microsoft.com/defaul...US;PHONENUMBERS
> If you are outside the US please see http://support.microsoft.com for
> regional support phone numbers.
> Also, I would like to share you my experiences on SQL Server 2005
> Everywhere Edition.
> From SQL Server 2005 Everywhere Edition Books Online (How To (SQL Server
> Everywhere) - Performing Common Database Tasks), we can see there are five
> ways to create an Everywhere database:
> 1. Create a SQL Server Everywhere database on the server
> 2. Create a SQL Server Everywhere Database on a Connected Device
> 3. Create a SQL Server Everywhere Database by Using the Engine Object
> (Programmatically)
> 4. Create a SQL Server Everywhere Database by Using the Replication Object
> (Programmatically)
> 5. Create a Database by Using OLE DB (Programmatically)
> Practically I just tried the first and the third method, but I recommend
> that you use the third method because I can just find "SQL Server Mobile"
> in the "server type" list, not "SQL Server Everywhere".
> You can use T-SQL as well as SQL Server 2000 and other editions to create
> tables, columns and indexes. For more information, you can refer to SQL
> Server 2005 Everywhere Edition Books Online (Especially the How To chapter
).
> If you have any other questions or concerns, please feel free to let me
> know. It's my pleasure to be of assistance.
> Charles Wang
> Microsoft Online Partner Support
> PLEASE NOTE: The partner managed newsgroups are provided
> to assist with break/fix issues and simple how to questions.
> We also love to hear your product feedback!
> Let us know what you think by posting
> - from the web interface: Partner Feedback
> - from your newsreader:
> microsoft.private.directaccess.partnerfeedback.
> We look forward to hearing from you!
> ========================================
==============
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ========================================
==============
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> ========================================
==============
>|||Dear John,
Appreciate your response.
I look forward to your resolving this issue.
Please feel free to let me know if you have any other questions or concerns.
Sincerely,
Charles Wang
Microsoft Online Community Support|||I guess what would be really useful, would be a utility that generates an
everywhere compatible script from a SQL Server table.
The problem with using script is that the number of deletions and amendments
needed are quite huge.
John Austin
"Charles Wang[MSFT]" wrote:

> Dear John,
> Appreciate your response.
> I look forward to your resolving this issue.
> Please feel free to let me know if you have any other questions or concern
s.
> Sincerely,
> Charles Wang
> Microsoft Online Community Support
>|||Dear John,
As you mentioned in email, you are just surprised that Microsoft have no
such migration tool.
I would like that you could give Microsoft feedback which will route to SQL
team so that this tool will be released in future.
You can submit your feedback via:
http://connect.microsoft.com/feedba...aspx?SiteID=68
Note: please logon before submitting a feedback.
If you have any other questions or concerns, please feel free to let me
know. It's always my pleasure to be of assistance.
Sincerely,
Charles Wang
Microsoft Online Community Support

How can I convert xml into table using SQL Server 2005?

How can I convert the xml as:
<row>
<a>1</a>
2
<c>3</c>
<d>4</d>
..
..
..
</row>
into table
a b c d ... ... ...
--
1 2 3 4 ... ... ...ABC wrote:
> How can I convert the xml as:
> <row>
> <a>1</a>
> 2
> <c>3</c>
> <d>4</d>
> ...
> ...
> ...
> </row>
> into table
> a b c d ... ... ...
> --
> 1 2 3 4 ... ... ...
This is an example using the stored procedure sp_xml_preparedocument and
the rowset provider OPENXML:
DECLARE @.x xml;
SET @.x = '<row>
<a>1</a>
2
<c>3</c>
<d>4</d>
</row>';
DECLARE @.iDoc int;
EXEC sp_xml_preparedocument @.iDoc OUTPUT, @.x;
SELECT *
FROM OPENXML(@.iDoc, '/row', 2)
WITH (a int, b int, c int, d int);
EXEC sp_xml_removedocument @.iDoc;
Another approach is to use the XQuery nodes function as follows:
DECLARE @.x xml;
SET @.x = '<row>
<a>1</a>
2
<c>3</c>
<d>4</d>
</row>';
SELECT T.col.value('a[1]', 'int') AS a,
T.col.value('b[1]', 'int') AS b,
T.col.value('c[1]', 'int') AS c,
T.col.value('d[1]', 'int') AS d
FROM @.x.nodes('/row') AS T(col);
Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/|||Thanks, but I have problem if the number of tag under the row node is
dynamic, it is hard apply this method.
"Martin Honnen" <mahotrash@.yahoo.de> wrote in message
news:u8HuKRkvHHA.356@.TK2MSFTNGP02.phx.gbl...
> ABC wrote:
> This is an example using the stored procedure sp_xml_preparedocument and
> the rowset provider OPENXML:
> DECLARE @.x xml;
> SET @.x = '<row>
> <a>1</a>
> 2
> <c>3</c>
> <d>4</d>
> </row>';
> DECLARE @.iDoc int;
> EXEC sp_xml_preparedocument @.iDoc OUTPUT, @.x;
> SELECT *
> FROM OPENXML(@.iDoc, '/row', 2)
> WITH (a int, b int, c int, d int);
> EXEC sp_xml_removedocument @.iDoc;
>
> Another approach is to use the XQuery nodes function as follows:
> DECLARE @.x xml;
> SET @.x = '<row>
> <a>1</a>
> 2
> <c>3</c>
> <d>4</d>
> </row>';
> SELECT T.col.value('a[1]', 'int') AS a,
> T.col.value('b[1]', 'int') AS b,
> T.col.value('c[1]', 'int') AS c,
> T.col.value('d[1]', 'int') AS d
> FROM @.x.nodes('/row') AS T(col);
> --
> Martin Honnen -- MVP XML
> http://JavaScript.FAQTs.com/|||ABC wrote:
> but I have problem if the number of tag under the row node is
> dynamic, it is hard apply this method.
That is true, I am not sure how to solve that case.
Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/|||It can't be completely dymanic for two reasons...
The first is XML should conform to a fixed schema, and secondly,
you're trying to push data into a fixed table.
On Thu, 5 Jul 2007 09:43:12 +0800,
"ABC" <abc@.abc.com> wrote in message
news:OXjNdXqvHHA.4516@.TK2MSFTNGP06.phx.gbl

> Thanks, but I have problem if the number of tag under the row node is
> dynamic, it is hard apply this method.
>
>
> "Martin Honnen" <mahotrash@.yahoo.de> wrote in message
> news:u8HuKRkvHHA.356@.TK2MSFTNGP02.phx.gbl...
>

How can I convert xml into table using SQL Server 2005?

How can I convert the xml as:
<row>
<a>1</a>
2
<c>3</c>
<d>4</d>
...
...
...
</row>
into table
a b c d ... ... ...
1 2 3 4 ... ... ...
ABC wrote:
> How can I convert the xml as:
> <row>
> <a>1</a>
> 2
> <c>3</c>
> <d>4</d>
> ...
> ...
> ...
> </row>
> into table
> a b c d ... ... ...
> --
> 1 2 3 4 ... ... ...
This is an example using the stored procedure sp_xml_preparedocument and
the rowset provider OPENXML:
DECLARE @.x xml;
SET @.x = '<row>
<a>1</a>
2
<c>3</c>
<d>4</d>
</row>';
DECLARE @.iDoc int;
EXEC sp_xml_preparedocument @.iDoc OUTPUT, @.x;
SELECT *
FROM OPENXML(@.iDoc, '/row', 2)
WITH (a int, b int, c int, d int);
EXEC sp_xml_removedocument @.iDoc;
Another approach is to use the XQuery nodes function as follows:
DECLARE @.x xml;
SET @.x = '<row>
<a>1</a>
2
<c>3</c>
<d>4</d>
</row>';
SELECT T.col.value('a[1]', 'int') AS a,
T.col.value('b[1]', 'int') AS b,
T.col.value('c[1]', 'int') AS c,
T.col.value('d[1]', 'int') AS d
FROM @.x.nodes('/row') AS T(col);
Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/
|||Thanks, but I have problem if the number of tag under the row node is
dynamic, it is hard apply this method.
"Martin Honnen" <mahotrash@.yahoo.de> wrote in message
news:u8HuKRkvHHA.356@.TK2MSFTNGP02.phx.gbl...
> ABC wrote:
> This is an example using the stored procedure sp_xml_preparedocument and
> the rowset provider OPENXML:
> DECLARE @.x xml;
> SET @.x = '<row>
> <a>1</a>
> 2
> <c>3</c>
> <d>4</d>
> </row>';
> DECLARE @.iDoc int;
> EXEC sp_xml_preparedocument @.iDoc OUTPUT, @.x;
> SELECT *
> FROM OPENXML(@.iDoc, '/row', 2)
> WITH (a int, b int, c int, d int);
> EXEC sp_xml_removedocument @.iDoc;
>
> Another approach is to use the XQuery nodes function as follows:
> DECLARE @.x xml;
> SET @.x = '<row>
> <a>1</a>
> 2
> <c>3</c>
> <d>4</d>
> </row>';
> SELECT T.col.value('a[1]', 'int') AS a,
> T.col.value('b[1]', 'int') AS b,
> T.col.value('c[1]', 'int') AS c,
> T.col.value('d[1]', 'int') AS d
> FROM @.x.nodes('/row') AS T(col);
> --
> Martin Honnen -- MVP XML
> http://JavaScript.FAQTs.com/
|||ABC wrote:
> but I have problem if the number of tag under the row node is
> dynamic, it is hard apply this method.
That is true, I am not sure how to solve that case.
Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/
|||It can't be completely dymanic for two reasons...
The first is XML should conform to a fixed schema, and secondly,
you're trying to push data into a fixed table.
On Thu, 5 Jul 2007 09:43:12 +0800,
"ABC" <abc@.abc.com> wrote in message
news:OXjNdXqvHHA.4516@.TK2MSFTNGP06.phx.gbl

> Thanks, but I have problem if the number of tag under the row node is
> dynamic, it is hard apply this method.
>
>
> "Martin Honnen" <mahotrash@.yahoo.de> wrote in message
> news:u8HuKRkvHHA.356@.TK2MSFTNGP02.phx.gbl...
>
sql

How can i convert Pivot table

Hi,
how can I convert a pivot table in Access to SQL Server.
Access SQL example:
TRANSFORM Sum(AM.TOTAL) AS SommaOfTOTAL
SELECT AM.RIGAS, AM.RIGARIF, AM.DESC, AM.SCHEMA
FROM AM
GROUP BY AM.RIGAS, AM.RIGARIF, AM.DESC, AM.SCHEMA
PIVOT AM.YEAR;
Thanks!!!Hi
There are many posts on how to pivot and crosstab posted in these groups
such as
http://tinyurl.com/8tqfr
SQL Server 2005 has features that make this alot simpler
http://msdn.microsoft.com/library/d...TSQLEnhance.asp
John
"claude81" wrote:

> Hi,
> how can I convert a pivot table in Access to SQL Server.
> Access SQL example:
> TRANSFORM Sum(AM.TOTAL) AS SommaOfTOTAL
> SELECT AM.RIGAS, AM.RIGARIF, AM.DESC, AM.SCHEMA
> FROM AM
> GROUP BY AM.RIGAS, AM.RIGARIF, AM.DESC, AM.SCHEMA
> PIVOT AM.YEAR;
> Thanks!!!

How can I convert font in database

I have one field type ntext, I want to change font of this data. Can I do this.Please help me.

Thank you alot.

this is the duty of presentation layer. as such you should change the font in the FE or GUI not in the database and its not possilble and its not logcally correct also. And also please tell us why you want to change the font in DB?

Madhu

|||Do you mean change the font, change the encoding, or change the collation? The font isn't stored in the server, the encoding is set by the application, but the collation of a column can be changed for a specific language or ordering. You can find more information on SQL Server collations at:

http://msdn2.microsoft.com/en-us/library/ms144260.aspx

Hope that helps!

John

|||I mean encoding, before user use font VNI-Times (Vietnamese language ) and save to database, now, if we show it with Unicode, we can not read anything, so that I am finding solution to convert encoding to Unicode.I intend export to excel, and import with some option that can change font encoding (if have any ).

How can I convert font in database

I have one field type ntext, I want to change font of this data. Can I do this.Please help me.

Thank you alot.

Madhu

http://msdn2.microsoft.com/en-us/library/ms144260.aspx

Hope that helps!

John

How can I convert font in database

I have one field type ntext, I want to change font of this data. Can I do this.Please help me.

Thank you alot.

Madhu

http://msdn2.microsoft.com/en-us/library/ms144260.aspx

Hope that helps!

John

How can I convert download SQL Server 2005 to a licensed version.

I downloaded a 180 day trial version of SQL Server 2005, and have it running. I have purchased a 5 user workgroup version. I would like to apply the 5 user license to the version that is currently installed.

My preference is to not reinstall everything since I have everything configured as I would like, and it is working great. What is the best approach?

Paul

The only way to do this is to Upgrade your Evaluation Edition to the Workgroup SKU. To do this you should run the Workgroup installation program and select the edition of SQL Server that you already have installed instead of installing a new edition.

Michelle

|||

Will this leave the installed database and all of its configuration as is?

Or will it change any of the configuration?

The reason I ask, is that due to a lack of proper planning we did not have SQL Server installed prior to a vendor arriving to install their application. I quickly downloaded the trial version, and installed it and ordered the Workgroup Edition. The applicationn is in and running, and I now want to make everything legal, and to have it run past the 180 day evaluation period.

If upgrading will change the application, I will need to pay the vendor to reconfigure things, and cause me problems.

Paul

sql

How Can I Convert Decimal To Hexadecimal

Hi!!!!!

I'm looking for a SQL FUnction that convert a decimal to Hexadecimal and

Hexadecimal to decimal data.

I know the way to convert for. But not with a SQL Function. certainly I

need to know How to express an Exponential Function.

Thank's.Hi!!!!!

I'm looking for a SQL FUnction that convert a decimal to Hexadecimal and

Hexadecimal to decimal data.

I know the way to convert for. But not with a SQL Function. certainly I

need to know How to express an Exponential Function.

Thank's.
check this...

/* User Defined Function To Convert HexaDecimal Value To Decimal Value
Input: HexaDecimal Value In String Format
Output: Decimal Value
*/
CREATE FUNCTION [dbo].[Fn_HEXCONV] (@.HEXVAL as VARCHAR(25)) RETURNS DECIMAL(20,0)
AS BEGIN
/* Declarations Of Variables Two Decimal Values To Store The Intermdeiate & Final Result,
String Value To Store The Hexadecimal Value During The Process,Two Counter Variables*/
DECLARE @.position int, @.INTVAL INT , @.CMDSTR NVARCHAR( 255 ) ,@.DECVAL DECIMAL(20,0),@.DECVALUE DECIMAL(20,0)
/* Initialising Variables */
SET @.position = 1
SET @.DECVAL=0
WHILE @.position <= DATALENGTH(REVERSE(@.HEXVAL)) /* Looping Through The String Until It Reaches The 0th Position */
BEGIN
/* Store The Decimal Value If the Hexa Value is Between A-F */
SET @.CMDSTR=CASE UPPER(SUBSTRING(REVERSE(@.HEXVAL) ,@.position,1)) WHEN 'A' THEN '10' WHEN 'B' THEN '11' WHEN 'C' THEN '12' WHEN 'D' THEN '13' WHEN 'E' THEN '14' WHEN 'F' THEN '15' ELSE SUBSTRING(REVERSE(@.HEXVAL) ,@.position,1) END
SET @.INTVAL=CAST(@.CMDSTR as INT) /* Casting The String To Integer */
SET @.DECVALUE=@.INTVAL
SET @.DECVAL=@.DECVAL+((@.DECVALUE)*POWER(CAST(16 AS BIGINT),@.position-1))/* Finding The Corresponding Decimal Value & Adding it To The Result */
SET @.position=@.position+1 /* Incrementing The Counter */
End
return CAST(@.DECVAL as Decimal(20,0)) /* Return The Converted Decimal Value Back */
End

Hope it will help you.
Joydeep ;)|||That functionality is built in, you don't need a function for it.DECLARE @.d DECIMAL(4)

SET @.d = 128

SELECT CAST(@.d AS VARBINARY(8)), CAST(0x0400000101000000 AS DECIMAL(4))-PatP

How can I convert datetime to number of minutes

I have a column in a table that stores the number of hours a task took to do. The column TaskDuration is a datetime datatype. I need to convert the hours to something that can be summed. Does anyone how this can be done? I tried CONVERT(nvarchar(5), tblTasks.TaskDuration, 108) but of course the nvarchar(5) cannot be summed. Maybe there is a way to convert the time portion to minutes and divide it by 60, anyway if someone can offer some help I appreciate it.

Try something like this

(datepart(hh, tblTasks.TaskDuration) * 60) + datepart(mm, tblTasks.TaskDuration)

|||

I tried this and it will return the number of minutes for the hours; however, the Parenthesis will not stay around the (datepart(hh, tblTasks.TaskDuration) * 60) in the view. So the (mm) are not being added.

Well it is adding time for the minutes but 30 is calculating to 10, so 03:30 is returning 190 minutes and it should be 210.

Any ideas?

|||I gave you the wrong datepart signifier, try datepart(n, tblTasks.TaskDuration)|||

Ok, so now that I have the number of minutes, can I convert this to hours and minutes. What I mean is the reporting tool needs a numeric column to sum on, so 03:15 needs to be 3.25.

Is this possible?

|||

I tried and it looks to be returning the correct format. If you have any comments, I appreciate them.

CONVERT (FLOAT, DATEPART(hh, dbo.tblVolunteerTasks.VTaskDuration) * 60 + DATEPART(n, dbo.tblVolunteerTasks.VTaskDuration)) / 60

How can I convert DateTime to Date as Parameter?

Hi Guys! Need Help on this!! I am using a Datetime data type as my Parameter on my stored procedure in SQL Server 2005. I am also using Crystal Reports XI for my reporting using the stored procedure in SQL but my problem is that I want to use ONLY the DATE data type as my Parameter instead of using the datetime parameter in Crystal Reports! Since the SQL server does not have a Date data type, how can I convert this from DateTime to Only Date data type as my parameter?....Thanks!!

Use datatime data type and pass just date part from CR or strip off the time part wherever you are planning to use it.

declare @.d datetime

set @.d = getdate()

select dateadd(day, datediff(day, 0, @.d), 0)

AMB

|||

Thanks! but how do you pass just the date part from CR? Any idea would be greatly appreciated!! I can strip off the time part inside the stored procedure in SQL 2005 but CR is using the parameter which is datetime....

|||

Sorry about that, but I think that question could be answered better in a CR newsgroup. Try:

datetime(datepart("yyyy", {@.d}), datepart("m", {@.d}), datepart("d", {@.d}), 00, 00, 00)

AMB

|||Thanks AMB....that will work but that code is for the inside on the report...my problem lies in the parameter prompt window..how can I let the user only select the date without seeing the the time on the parameter prompt window?....|||

Sorry I have no idea. As I mentioned in my previos post, these questions would be better asked in a CR newsgroup.

AMB

how can i convert binary(8) to datetime?

HI! :shocked:
I tried to convert 0x01C3F0F5012D36E0, binary(8) to datetime
But
How can I do that?
thanks for allwhat does 0x01C3F0F5012D36E0 repesent in datetime as ?|||You can't convert it, it is bigger than the largest possible datetime:DECLARE @.dMax DATETIME

SET @.dMax = '9999-12-31 23:59:59.997'

SELECT Cast(@.dMax AS VARBINARY(8)), 0x01C3F0F5012D36E0-PatP

How can i convert binary(16) to Integer or numeric?

HI!!!:shocked:

Im trying to convert 0x00085180F0A2D511B69600508BE96424 to Integer or numeric format.

I just tried, At to many forms and combinations of that query
Help me !!!

SELECT CAST(CAST(CAST("field name " AS nvarchar) AS varbinary) AS float)

select cast(cast("field name " as varbinary)as integer)

select convert(int," field name") from FILE

select convert(varchar," field name") from FILE

The only answer that I have is
-1947638748 or or

And if I try with to many rows of the field at the same format,
It Answer me the same: -1947638748 for all the rows.

Thank`s for allcreate table #t1(f1 binary(16))

select *
from #t1

insert into #t1 values(convert(binary(16),'0x00085180F0A2D511B6960 0508BE96424'))

select convert(integer, f1)
from #t1

--The result is 1093813301. I dont see -1947638748

drop table #t1|||:D THANK YOU!!!, forXLDB

Just one more question please

Whats the reason for the space in the middle of the expression?

Before convert

Original Expresin
'0x00085180F0A2D511B69600508BE96424'

Convert expresin
'0x00085180F0A2D511B6960 0508BE96424'|||thank you...|||Sorry, but the actual answer is MUCH larger! You can't express that VARBINARY value as an integer, or even as a NUMERIC(38) which is the largest SQL Server will allow. The following code shows what I mean:DECLARE
@.b VARBINARY(16) -- binary image to convert
, @.i INT -- Which byte we're working on
, @.m FLOAT -- Multiplier for this byte
, @.a FLOAT -- Accumulator

SET @.b = 0x00085180F0A2D511B69600508BE96424
SELECT @.i = DataLength(@.b), @.m = 1, @.a = 0 -- Start with lowest order byte

WHILE 0 < @.i -- While bytes left to process
BEGIN
-- SELECT @.a, @.m, @.i, SubString(@.b, @.i, 1) -- Show your work
SELECT @.a = @.a + (@.m * CAST(SubString(@.b, @.i, 1) AS INT))
SELECT @.m = 256 * @.m, @.i = @.i - 1 -- Prepare for next byte
END

SELECT @.m -- Show results-PatP|||Thanks Pat Phelan

I like that explication step by step.
It really please me.sql

How can I convert a date and an amount in my select statement

I need to convert a date like 08/1/2009 to 0809

I also need to show currency as 100.00 and not 100.0000

How can I do these in a select statement?

SELECT CONVERT(Varchar(20),ExpirationDate,10) AS ExpirationDate, Amount FROM tblPayment

I appreciate any help!

hi Jackxxx,

can you try this

SELECT convert(varchar,datepart(dd,getdate()))+convert(varchar,datepart(yy,getdate())) AS ExpirationDate, convert(decimal(10,2), 2323.2422)

thanks,

Satish.

|||

I tried the expiration date and the date was 7/1/2009 and your statement returned 172007.

Also I goofed on the other the field name is AmountPaid that I need to show 100.00 for.

|||

hi Jackxxx,

what i gave was an example you need to modify your actuall query accordingly like i've put getdate() so you need to put your datetime field in there similary amount field also.

thanks,

satish,

|||

It's almost perfect, the date still shows all for digits of the year. Is there a way to only show the last two digits? Like 09 for 2009

I very much appreciate your help!

|||

hi jackxxx,

i tried alot but its giving 4 digits atlast i had to cheat Big Smile , use

select right(datepart(yy,getdate()),2)

hope it works now Smile .

regards,

satish.

How can I convert 12/2/ to 12/2/current year

I have a field in my database that holds a date, the only part of the date I care about is the month and day (12/2/). I'm trying to use this field in a view column to show the for example 12/2/ and the current year (2007). Also if the month and day have passed like 11/1/ then it would be next year (2008).

Can anyone help me with this?

Look up the DatePart function in sql server. It has pretty much what you need.

|||

DECLARE @.ddatetimeSET @.d='11/20/2007'SELECTCASEWHENDATEADD(year,DATEDIFF(year,@.d,getdate()),@.d)<getdate()THENDATEADD(year,DATEDIFF(year,@.d,getdate())+1,@.d)ELSEDATEADD(year,DATEDIFF(year,@.d,getdate()),@.d)END

To put this in a view, just remove the DECLARE and SET statements. Copy the code from CASE through END into your select statement, and replace @.d with the field name from your table.Optionally add ' AS MyNewField' after the END to give the column a name.|||

Motley,

Is it possible to use this in a udf so I can use it in other views?

I tried the following, but received and error:

Msg 102, Level 15, State 1, Procedure ufn_getdate, Line 14

Incorrect syntax near 'END'.

CREATE FUNCTION dbo.ufn_getdate (@.ddatetime)
RETURNSDATETIME
BEGIN

SELECTCASEWHENDATEADD(year,DATEDIFF(year,@.d,getdate()),@.d)<getdate()
THENDATEADD(year,DATEDIFF(year,@.d,getdate())+1,@.d)
ELSEDATEADD(year,DATEDIFF(year,@.d,getdate()),@.d)

END
GO

|||

CREATE FUNCTION dbo.ufn_getdate (@.ddatetime)RETURNSDATETIMEBEGIN RETURN (SELECTCASEWHENDATEADD(year,DATEDIFF(year,@.d,getdate()),@.d)<getdate()THENDATEADD(year,DATEDIFF(year,@.d,getdate())+1,@.d)ELSEDATEADD(year,DATEDIFF(year,@.d,getdate()),@.d)END )ENDGO

|||

Motley,

I get a new error: maybe I'm have missed something

Msg 102, Level 15, State 1, Procedure ufn_getdate, Line 12

Incorrect syntax near ')'.

CREATE FUNCTION dbo.ufn_getdate (@.ddatetime)RETURNSDATETIMEBEGINRETURN (SELECTCASEWHENDATEADD(year,DATEDIFF(year,@.d,getdate()),@.d)<getdate()THENDATEADD(year,DATEDIFF(year,@.d,getdate())+1,@.d)ELSEDATEADD(year,DATEDIFF(year,@.d,getdate()),@.d)END)GO

|||

Sorry, I editted the above code, it should work now. It was missing an END.

|||

Motley,

Thanks very much for your help, please take the rest of the week off.

How can I control the user to SQL Server?

Hi,
I want to control on SQL Server 2000 users. I use C# language. My scenario
that have SQL Server 2000 on my user "CIMBOM". How can i write code there
user for control username and password. May be prepared sql function?
i hope explain my problem :)
too thanks...Hi
If you check out the topic "How to allow access by granting permissions" in
Books online it may help you to understand the how different logins can have
different access level.
Also you may want to look at the IS_MEMBER function or the other "Security
Functions" available if you need to control access to specific data within a
given table.
John
"Gürol Ayanlar" wrote:

> Hi,
> I want to control on SQL Server 2000 users. I use C# language. My scenario
> that have SQL Server 2000 on my user "CIMBOM". How can i write code there
> user for control username and password. May be prepared sql function?
> i hope explain my problem :)
> too thanks...

How can I consume webservices on SQL 2005

Hi,
I'd like know if can I build ( and how can do ) a store procedore on MS
SQL 2005 to access a remote webservices, process it and return the result to
my client ?
Thanks,
Solli M. Honório
Hello Solli,

> I'd like know if can I build ( and how can do ) a store procedore on
> MS SQL 2005 to access a remote webservices, process it and return the
> result to my client ?
Yes.
The real trick is that you need to add build step that generates a static
proxy class. Do that by adding this as a Post-build Event Step in your SqlClr
project:
"C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin\sgen.exe" /force
"$(TargetPath)"
Note that you cannot use Visual Studio to deploy all of the needed assemblies.
You'll need to fire up SSMS or like and issue these commands:
use ...whatever database you like...
go
create assembly ...what you want to call your assembly...
from ...where the DLL is on your disk...
with permission_set = external_access
go
create assembly [...whatever your project name is... .XmlSerializers]
from '...where the DLL is on your disk...\SqlServerProject1.XmlSerializers.dll'
go
create procedure ...whatever you want to call the procedure...(...list of
parameters, if any...)
as external name Assembly Name.[fully qualified class name]. ...name of method...
go
exec ...whatever you called the procedure...
go
The guts of my stored procedure looks like:
[Microsoft.SqlServer.Server.SqlProcedure]
public static void WebMath(SqlInt32 x,SqlInt32 y)
{
SqlMetaData[] cols = new SqlMetaData[1];
cols[0] = new SqlMetaData("Result", SqlDbType.Int);
SqlDataRecord rec = new SqlDataRecord(cols);
using (SqlServerProject1.ws1proxy.ws1 proxy = new SqlServerProject1.ws1proxy.ws1())
{
rec.SetInt32(0, proxy.AddTwo(x.Value, y.Value));
SqlContext.Pipe.Send(rec);
}
}
The Web Service in question looks like:
public int AddTwo(int x,int y) {
return(x+y);
}
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/

How can I consume webservices on SQL 2005

Hi,
I'd like know if can I build ( and how can do ) a store procedore on MS
SQL 2005 to access a remote webservices, process it and return the result to
my client ?
Thanks,
Solli M. HonórioHello Solli,

> I'd like know if can I build ( and how can do ) a store procedore on
> MS SQL 2005 to access a remote webservices, process it and return the
> result to my client ?
Yes.
The real trick is that you need to add build step that generates a static
proxy class. Do that by adding this as a Post-build Event Step in your SqlCl
r
project:
"C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin\sgen.exe" /force
"$(TargetPath)"
Note that you cannot use Visual Studio to deploy all of the needed assemblie
s.
You'll need to fire up SSMS or like and issue these commands:
use ...whatever database you like...
go
create assembly ...what you want to call your assembly...
from ...where the DLL is on your disk...
with permission_set = external_access
go
create assembly [...whatever your project name is... .XmlSerializers]
from '...where the DLL is on your disk...\SqlServerProject1.XmlSerializers.d
ll'
go
create procedure ...whatever you want to call the procedure...(...list of
parameters, if any...)
as external name Assembly Name.[fully qualified class name]. ...name of meth
od...
go
exec ...whatever you called the procedure...
go
The guts of my stored procedure looks like:
[Microsoft.SqlServer.Server.SqlProcedure]
public static void WebMath(SqlInt32 x,SqlInt32 y)
{
SqlMetaData[] cols = new SqlMetaData[1];
cols[0] = new SqlMetaData("Result", SqlDbType.Int);
SqlDataRecord rec = new SqlDataRecord(cols);
using (SqlServerProject1.ws1proxy.ws1 proxy = new SqlServerProject1.ws1proxy
.ws1())
{
rec.SetInt32(0, proxy.AddTwo(x.Value, y.Value));
SqlContext.Pipe.Send(rec);
}
}
The Web Service in question looks like:
public int AddTwo(int x,int y) {
return(x+y);
}
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/sql

How can I conserve the initial zero when convert numeric to string using STR()

Sorry to raise a stupid question but I tried many methods which did
work.
how can I conserve the initial zero when I try to convert STR(06) into
string in SQL statment?
It always gives me 6 instead of 06.

Thanks a lot.You can't "preserve" the zero. Integer 06 = Integer 6 = Integer
00000000006.

If you want to convert an integer to a varchar you can prepend a 0 character
to the result:

SELECT '0' + CAST(06 AS VARCHAR)

Returns '06'. The downside to this method is that if you do something like

SELECT '0' + CAST(10 AS VARCHAR)

You'll end up with '010' which may or may not be what you want. You can
build on this example with the SUBSTRING function to get exactly what you
really want out of it.

<angellian@.gmail.com> wrote in message
news:1148779910.070643.296910@.g10g2000cwb.googlegr oups.com...
> Sorry to raise a stupid question but I tried many methods which did
> work.
> how can I conserve the initial zero when I try to convert STR(06) into
> string in SQL statment?
> It always gives me 6 instead of 06.
> Thanks a lot.|||You are confusing the PHYSICAL display with the internal LOGICAL model.

This is SQL and not COBOL. There is no initial zero in a number; there
is an internal binary, BCD or whatever the hard uses representation.

Your next problem is that you do not understand that dispaly is NEVER
done in the database, but in the front end application. That is the
most basic concept of *any* tiered architecture, not just SQL.|||--CELKO-- (jcelko212@.earthlink.net) writes:
> Your next problem is that you do not understand that dispaly is NEVER
> done in the database, but in the front end application. That is the
> most basic concept of *any* tiered architecture, not just SQL.

Working so long as you have done in the database trade should have learnt
you to never say never.

There is at least one obvious case where formatting of output must be
done in SQL: to wit when the display is done in a standard query tool
like Query Analyzer. Which typically is the case for admin stuff.

--
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|||> This is SQL and not COBOL. There is no initial zero in a number; there
> is an internal binary, BCD or whatever the hard uses representation.

This is SQL SERVER not SQL and not COBOL, SQL SERVER has many facilities to
aid the developer in creating a highly scalable, robust and maintainable
architecture.

Standard SQL is very weak in terms of features that we need out in the real
world.

> Your next problem is that you do not understand that dispaly is NEVER
> done in the database, but in the front end application. That is the
> most basic concept of *any* tiered architecture, not just SQL.

"Display" can never be done in the database because the database is a
service and has such has no UI, we use tools to get at the data.

The big problem here is your continued misconception that ALL formatting
should be done in the front end application, have you actually sat down and
thought about what that means? The fundemental principle of tiered
architecture design and development is that formatting is done where it is
most sensible and efficient, in terms of development and support cost and in
terms of performance.

My blog entry on this covers in more detail:
http://sqlblogcasts.com/blogs/tonyr.../05/11/429.aspx

I see you use CTE, why don't you pull the results down into the application,
CTE's are a form of formatting for display purposes, as is COALESCE on the
SELECT clause, as is ORDER BY etc... Just where do you draw the line?

Anyway, you are still stuck in the mainframe model of all resources are in
the same box and that you use the VTAM protocol out to remote terminals.

--
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials

"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1148830663.994422.182500@.y43g2000cwc.googlegr oups.com...
> You are confusing the PHYSICAL display with the internal LOGICAL model.
>
> This is SQL and not COBOL. There is no initial zero in a number; there
> is an internal binary, BCD or whatever the hard uses representation.
> Your next problem is that you do not understand that dispaly is NEVER
> done in the database, but in the front end application. That is the
> most basic concept of *any* tiered architecture, not just SQL.|||Something like this may help...Assuming your column name is COLUMN1 and
COLUMN1 has a numeric type.

select case
when COLUMN1 < 10 then '0' + cast(COLUMN1 as varchar(10))
end
when COLUMN1 > = 10 then cast(COLUMN1 as varchar(10)) end
......(rest of your statement...)

Hope this helps...|||Hi Angellian,

For 2 character string you can just use CASE...

declare @.number tinyint
set @.number = 2

select case when @.number between 0 and 9 then '0' else '' end + cast(
@.number as varchar(2) )

Otherwise, if your resultant string needs to be bigger than 2 characters do
this...

declare @.number int
declare @.string varchar(10)
declare @.size_of_fixed_string tinyint
set @.size_of_fixed_string = 10
set @.number = 40

print replicate( '0', @.size_of_fixed_string )

set @.string = left( replicate( '0', @.size_of_fixed_string ),
@.size_of_fixed_string - len( @.number ) ) + cast( @.number as varchar(10) )

print @.string

http://sqlblogcasts.com/blogs/tonyr.../05/29/765.aspx

--
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials

<angellian@.gmail.com> wrote in message
news:1148779910.070643.296910@.g10g2000cwb.googlegr oups.com...
> Sorry to raise a stupid question but I tried many methods which did
> work.
> how can I conserve the initial zero when I try to convert STR(06) into
> string in SQL statment?
> It always gives me 6 instead of 06.
> Thanks a lot.|||>> There is at least one obvious case where formatting of output must be done in SQL: to wit when the display is done in a standard query tool like Query Analyzer. <<

No, that formatting is done in the Query Analyzer, which is a program
and not part of SQL. Trust me, we never voted on a "standard query
tool" in ANSI X3H2.|||>> I see you use CTE, why don't you pull the results down into the application, CTE's are a form of formatting for display purposes, as is COALESCE on the SELECT clause, as is ORDER BY etc... Just where do you draw the line? <<

UNH? CTEs are virtual tables and have nothing to do with display. Do
ypou also think that VIEWs and derived tables are formatting for user
display? COALESCE is a function that works with NULLs and CAST() to
get another internal data type result.

Things like CONVERT() on dates or PRINT in T-SQL is formatting.|||Just cause I've seen a couple of examples using CASE; I use trick
similar to your second example:

SELECT RIGHT('0' +CONVERT(varchar(2), @.number), 2)

Granted, it only works on a two-digit number, but it saves typing. The
REPLICATE idea is pretty smooth, though.

Stu

Tony Rogerson wrote:
> Hi Angellian,
> For 2 character string you can just use CASE...
> declare @.number tinyint
> set @.number = 2
> select case when @.number between 0 and 9 then '0' else '' end + cast(
> @.number as varchar(2) )
> Otherwise, if your resultant string needs to be bigger than 2 characters do
> this...
> declare @.number int
> declare @.string varchar(10)
> declare @.size_of_fixed_string tinyint
> set @.size_of_fixed_string = 10
> set @.number = 40
> print replicate( '0', @.size_of_fixed_string )
> set @.string = left( replicate( '0', @.size_of_fixed_string ),
> @.size_of_fixed_string - len( @.number ) ) + cast( @.number as varchar(10) )
> print @.string
> http://sqlblogcasts.com/blogs/tonyr.../05/29/765.aspx
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
> Server Consultant
> http://sqlserverfaq.com - free video tutorials
>
> <angellian@.gmail.com> wrote in message
> news:1148779910.070643.296910@.g10g2000cwb.googlegr oups.com...
> > Sorry to raise a stupid question but I tried many methods which did
> > work.
> > how can I conserve the initial zero when I try to convert STR(06) into
> > string in SQL statment?
> > It always gives me 6 instead of 06.
> > Thanks a lot.|||--CELKO-- wrote:
> >> I see you use CTE, why don't you pull the results down into the application, CTE's are a form of formatting for display purposes, as is COALESCE on the SELECT clause, as is ORDER BY etc... Just where do you draw the line? <<
> UNH? CTEs are virtual tables and have nothing to do with display. Do
> ypou also think that VIEWs and derived tables are formatting for user
> display? COALESCE is a function that works with NULLs and CAST() to
> get another internal data type result.
> Things like CONVERT() on dates or PRINT in T-SQL is formatting.

Hi Joe,

I didn't understand Tony's point about CTEs, but I think his point
about COALESCE stands. Surely, COALESCE is shorthand for having
formatting code at the front end like:

If Column1 is not null then
show Column1
Else if column2 is not null then
show Column2
Else if column3 is not null then
:
:
:
Else
show ColumnN
End If

Damien|||> UNH? CTEs are virtual tables and have nothing to do with display. Do
> ypou also think that VIEWs and derived tables are formatting for user
> display? COALESCE is a function that works with NULLs and CAST() to
> get another internal data type result.
> Things like CONVERT() on dates or PRINT in T-SQL is formatting.

Ok - I conceede CTEs, I was thinking about them within the scope of paging
on which you have in the past stated you would have the front end perform,
that literally means pushing a million rows over the network to the front
end.

> Things like CONVERT() on dates or PRINT in T-SQL is formatting.

The operator was trying to create a string with leading zeros which you
stated should be done in the front end.

Why on earth would you want to go to all the effort of using a 3GL / 4GL to
format the data when you can just simply do it in TSQL within the SQL Server
itself - nice and simple, nice and easy to support and maintain.

Your method relies on additional skills, the developer would need to
understand a programming language as well as SQL, that then translates into
a support and maintanence burden which costs money.

You can very easily do the formatting in the TSQL and use Integration
Services or DTS to export the data out to whatever you want - XML, XLS
etc...

Your recommendations around formatting date back to the 70's where rdbms
didn't have many facilities available for the developer other than SUM,
COUNT, MIN, MAX and AVG.

--
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials

"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1149114884.873451.172270@.g10g2000cwb.googlegr oups.com...
>>> I see you use CTE, why don't you pull the results down into the
>>> application, CTE's are a form of formatting for display purposes, as is
>>> COALESCE on the SELECT clause, as is ORDER BY etc... Just where do you
>>> draw the line? <<
> UNH? CTEs are virtual tables and have nothing to do with display. Do
> ypou also think that VIEWs and derived tables are formatting for user
> display? COALESCE is a function that works with NULLs and CAST() to
> get another internal data type result.
> Things like CONVERT() on dates or PRINT in T-SQL is formatting.|||>> Why on earth would you want to go to all the effort of using a 3GL / 4GL
>> to format the data when you can just simply do it in TSQL within the SQL
>> Server itself - nice and simple, nice and easy to support and maintain.

The general answer is that one would prefer to have the centralized database
as generic as possible so that it can support a variety of applications.

Having an application specific formatting at the central data source tend to
generate something called "application bias". Considering the OP's question,
given certain 5 applications requesting same data formatted in 5 different
ways, should he formulate a single generic query and do the formatting in
the application or should he create 5 different queries to support each
application? How about when the number of applications increases to 50? Or
say 500?

While it may appear to be efficient and easy to manage in the short term, it
can often be highly detrimental to the long term stability and management of
data centric systems.

This is nothing new but such bias is known to software engineers for decades
now. For details on why this separation of concern is important for data
oriented systems, ~Principles of Program Design~ by Michael Jackson is a
good book.

--
Anith|||--CELKO-- (jcelko212@.earthlink.net) writes:
>>> There is at least one obvious case where formatting of output must be
done in SQL: to wit when the display is done in a standard query tool like
Query Analyzer. <<
> No, that formatting is done in the Query Analyzer,

QA only has a standard formatting, with no options to specify a how a
certainly column should look like.

Thus, if you want a certain format when you look at the data in QA, SQL
is the only place to do formatting.

> which is a program and not part of SQL. Trust me, we never voted on a
> "standard query tool" in ANSI X3H2.

I never said so. I only meant to say that it is a plain query tool,
and about every RDBMS comes with one.

--
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|||Anith Sen (anith@.bizdatasolutions.com) writes:
> The general answer is that one would prefer to have the centralized
> database as generic as possible so that it can support a variety of
> applications.

I think the Perl has the right answer to this: There is more than one
way do it!

That is, if you can do things either in the server or the in the client/
middile layer, you can pick what fits best for the situation.

--
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|||I think of it the other way round, surely the 5 applications would call 1
single query and not different queries for different applications.

SQL Server is more a service orientated architecture, well - becoming that
anyway.

So, doing things centrally in the SQL Server is better because you only need
do it once and not in 5 places in 5 different langauges requiring 6
different skill sets.

--
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials

"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:e5n83p$v6r$1@.nntp.aioe.org...
>>> Why on earth would you want to go to all the effort of using a 3GL / 4GL
>>> to format the data when you can just simply do it in TSQL within the SQL
>>> Server itself - nice and simple, nice and easy to support and maintain.
> The general answer is that one would prefer to have the centralized
> database as generic as possible so that it can support a variety of
> applications.
> Having an application specific formatting at the central data source tend
> to generate something called "application bias". Considering the OP's
> question, given certain 5 applications requesting same data formatted in 5
> different ways, should he formulate a single generic query and do the
> formatting in the application or should he create 5 different queries to
> support each application? How about when the number of applications
> increases to 50? Or say 500?
> While it may appear to be efficient and easy to manage in the short term,
> it can often be highly detrimental to the long term stability and
> management of data centric systems.
> This is nothing new but such bias is known to software engineers for
> decades now. For details on why this separation of concern is important
> for data oriented systems, ~Principles of Program Design~ by Michael
> Jackson is a good book.
> --
> Anith

how can i connect with lank password

hi all
i setup my sqlserver2000 on win 2000 and let him to take username and password from windows
so how can i connect to it with blank uid,pwd?
thanxYou can use trusted connection as :
"Server=Aron1;Database=pubs;Trusted_Connection=True;"

FYI: http://www.connectionstrings.com

Regards,|||thanx alot|||hello aain
i tried to connect but an eror msg appears:
Exception Occurred: Login failed for user 'COMPU-DSW51C8DZ\ASPNET'.

i'm using this code:

SqlConnection cn;
SqlCommand cm;
String strSQL;

cn = new SqlConnection ("server=localhost;database=Northwind;Trusted_Connection=true;");
strSQL = "INSERT INTO Employees " +
"(EmployeeID,FirstName,LastName,City) " +
"VALUES ('" + txtID.Text + "', '" +
txtFirstName.Text + "', '" +
txtLastName.Text + "', '" +
ChkToInt(chkCity) + "')";

cm = new SqlCommand(strSQL, cn);

// ** Open connection
try
{
cn.Open();
// ** Execute command
cm.ExecuteNonQuery();
}
catch(SqlException sx)
{
Response.Write("Exception Occurred: " + sx.Message);
}
finally
{
if (cn.State == ConnectionState.Open)
cn.Close();
}|||This means you need to set up COMPU-DSW51C8DZ\ASPNET as a login in SQL Server.

Do you have Query Analyzer? Running this will create the login:

exec sp_grantlogin 'COMPU-DSW51C8DZ\ASPNET'

And running this will give permission to the database:

exec sp_grantdbaccess 'COMPU-DSW51C8DZ\ASPNET'

Make sure that northwind is the current database before running executing sp_grantdbaccess.

Terri

how can i connect to two databases?

hello,

i want to make a relation betwen one of my tables and the user tables (to take it's unique ID), if there isn't any methode to do that without using the two databases(ASPNETDB - automaticly created when a user registers, and MyData), how can i connect to both databases? here is my connection string, but what should i do?

<connectionStrings> <add name="SiteConnection" connectionString="Server=(local)\SqlExpress; Integrated Security = True; Database = MyData" providerName="System.Data.SqlClient" /> <add name="ConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\MyData.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient" /></connectionStrings>

thank you

Hello zuperboy90,

Maybe you should take a look at this post:-

Asp.net database created at for memebership logan

Cheers,

Eric

|||

is there any problem if i use the same databse(created by default) for users and my other things?...but still, it will be a very big mess there

|||

Hello zuperboy90,

I don't think there is any problem I supposed. If someone out there aware of any potential problem, please share your concerns with us.

Thank you.

Regards,

Eric

how can i connect to sqlserver2005 express by ado

hr = m_pConnection.CreateInstance("ADODB.Connection");
if (SUCCEEDED(hr))
{
if (FAILED(m_pConnection->Open("Provider = sqloledb;Server=(local);Database=c863;Trusted_Connection=yes","","",-1)))
{
AfxMessageBox("Can not open Database!");
m_pConnection.Release();
return FALSE;
}

}
when i debug ,there is an exception at "m_pConnection->Open("Provider = sqloledb;Server=(local);Database=c863;Trusted_Connection=yes","","",-1)))"
the exception is
'Book.exe': Loaded 'C:\Program Files\Common Files\System\Ole DB\sqloledb.rll', Binary was not built with debug information.
First-chance exception at 0x7c81eb33 in Book.exe: Microsoft C++ exception: _com_error at memory location 0x0013fba4..
how should i do?

Try

Server=(local)\SQLEXPRESS

SQL Express is installed as a "named" instance by default, an drequires the instance name specification when making a connection (as opposed to a "default" instance).|||Sorry,it dont still work.the exception is
"First-chance exception at 0x7c81eb33 in Book.exe: Microsoft C++ exception: _com_error at memory location 0x0013fba4.."|||It looks like the failure may be unrelated to your connection string.

>> First-chance exception at 0x7c81eb33 in Book.exe: Microsoft C++ exception: _com_error at memory location 0x0013fba4.

Try running your application with a debugger attached and breaking at the point the exception occurs. This will likely give you more insight into the cause of the problem.

Hope this helps,
Vaughn

how can i connect to sqlserver2005 express by ado

Server=(local)\SQLEXPRESS

SQL Express is installed as a "named" instance by default, an drequires the instance name specification when making a connection (as opposed to a "default" instance).|||Sorry,it dont still work.the exception is
"First-chance exception at 0x7c81eb33 in Book.exe: Microsoft C++ exception: _com_error at memory location 0x0013fba4.."
|||It looks like the failure may be unrelated to your connection string.

>> First-chance exception at 0x7c81eb33 in Book.exe: Microsoft C++ exception: _com_error at memory location 0x0013fba4.

Try running your application with a debugger attached and breaking at the point the exception occurs. This will likely give you more insight into the cause of the problem.

Hope this helps,
Vaughnsql

How can i connect to Sqlserver2000 from sqlserver2005

Hi,

Can any1 tell me how to connect to Sqlserver2000 from sqlserver2005 ...I cant see sqlserver2000 instance name when i try to search in database engine

With regards

mahender

I assume you're talking about using SSMS and you can't see your sql2k instance?

When you click "browse" button, the application send a broadcast onto the network on UDP 1434 to get a list of available sql instances from sqlbrowsers. If the sqlbrowser port is blocked or it's not running on the node where your sql2k instance resides, you would not get a reply back. This does not mean, however, that you cannot connect to your instance. If this is a default instance, you can just specify the servername explicitly and the application (ssms) should connect. If it is a named instance, you will need to specify the listening tcp port (e.g. servername,12345) to connect (this assumes your sqlbrowser is not running).

If you're talking about creating a connection between the two servers, you will want to look for "sp_addlinkedserver" in book online. There should be detailed info to walk you through.

how can i connect to sqlserver express manually when i use text boxed

hi guys,

i am very happy that i found this forum which discuss ASP.net.
i am new to ASP.net 2.0 and some need serious help. when i debug my application and try to insert data an error occur saying:

"Cannot open database "ecb" requested by the login. The login failed. Login failed for user 'HOME-USER\user'."

the code that i have used to submit data is as follow:

Imports System.Data.SqlClient
...
Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click

Dim objConn As SqlConnection
Dim objCmd As SqlCommand

objconn =New SqlConnection("data source= localhost\sqlexpress; initial catalog =ecb; persist security info =true; trusted_connection=true")

objCmd = New SqlCommand("INSERT INTO staff (firstname, surname) VALUES (@.firstname, @.surname,)", objConn)

objCmd.Parameters.Add("@.FirstName", Data.SqlDbType.VarChar).Value = txtFirstName.Text
objCmd.Parameters.Add("@.Surname", Data.SqlDbType.VarChar).Value = txtSurname.Text

objConn.Open()
objCmd.ExecuteNonQuery()
objConn.Close()

i also used this data connection but didn't help:
objconn = New SqlConnection("Server=localhost; Database=ecb;user ID=' ';Password=' ' ") when i use this data

connection i get this message: Login failed for user ''. The user is not associated with a trusted SQL Server connection.

as far as i can remember i have used windows authentication when creating my database in sql server 200 express edition.

the funny thing is that when i use datagrid wizard or dataform the connection is fine ( i mean is automatically done for me but i don't know when i like to do it manually does not work).

can anyone help? thank you in advanced

Hi!

My guess is going to be that you have selected Mixed Authentication mode when you installed the server. Try specifying login details in your connection string. My guess is going to be 'ASPNET' for username and "" for password.

Something else:

What i could recommend you to do is to have a look at this link:http://www.asp.net/default.aspx?tabindex=4&tabid=3000. See Working with "Gridview and Formview"

If you are only updating Firstname and Surnames fields and other basic tasks, then you might rather want to make use of the visual tools in VWD to accomplish this.

|||

hi again:

thank you for your help. Anyway it sounds like is not working. i have tried many many solutions but all failed. however my question here can i uninstall SQL server express edition while keeping hold of the tables that i have done (is quite important)? and then reinstall the application just to make sure that the authentication level i will use is correct ( because i can't remember whether i have used sql server authentication or windows based authentication.!!!

do you thing is a good solution? if yes would you please tell me how to keep the tables and uninstall the application?

Many thanks saif44

|||

If you have created the website in your file system then its all fine. If you really want to then just make a back up of the folder.

You can go ahead and do a re-installation.

It really hard to say if its a good solution. I cant say. It's def worth a try Wink [;)]

|||

Hello Saif,

This might help, I was having the same issue and this is one way to figure out your connection string.

Use your datagrid wizard and create a valid connection and make sure it works, then go back to your datagrid and go to configure data source.

This takes you to choose your data connection menu and the current connection that your datagid is using will be selected in the drop down list. Below the drop down list is a box with a + and the words connection string across from it. Click the + button and the connection string that was generated by the wizard will be shown. That should be your valid connection string that works.

A nicer way of doing it is to create the connection string in the web.config file and then reference the connection string name in your code.

for example in my web.config file I have this connection string:

<removename="LocalSqlServer"/>

<addname="LocalSqlServer"connectionString="Server=[your server name];Database=[ur dbase name];User ID=;Password=;Trusted_Connection=False"/>

</connectionString>

Now I can reference the connection string programatical in my code as such:

Using connectionAsNew SqlConnection(ConfigurationManager.ConnectionStrings"LocalSqlServer").ConnectionString)

....[my code here]

end using

Of course this is just a short explanation, hopefully it gets you in the right direction.

The best resource I have found for asp.net is from 4guysfromRolla.com, this is their article on connection to a database:

http://aspnet.4guysfromrolla.com/articles/022206-1.aspx

thanks,

manny

|||Thank you very very much Manny. the problem is sloved. However, there is another problem with

objCmd.ExecuteNonQuery() it triggers a message saying "Invalid object name 'staff ' ". i don't know why this is happening?' Staff ' is the name of my table i am using and its syntax is correct.

please can you review this code for me and tell me what's wrong with it:

............

objconn =New SqlConnection(ConfigurationManager.ConnectionStrings("localsqlserver").ConnectionString)

objCmd =New SqlCommand("INSERT INTO staff(staffno, FirstName, Surname, DateRegistered) _

values (@.staffno, @.firstname, @.surname, @.dateregistered)", objconn

objCmd.Parameters.Add("@.StaffNo", Data.SqlDbType.Int).Value = txtStaffNo.Text

objCmd.Parameters.Add("@.FirstName", Data.SqlDbType.VarChar).Value = txtFirstName.Text

objCmd.Parameters.Add("@.Surname", Data.SqlDbType.VarChar).Value = txtSurname.Text

objCmd.Parameters.Add("@.DateRegistered", Data.SqlDbType.SmallDateTime).Value = txtRegDate.Text

objConn.Open()

objCmd.ExecuteNonQuery()

objConn.Close()

Response.Redirect("addstaff.aspx")

I have to say here that i am using an automatic insertion of the StaffNo when i first created the data definition table. do you think the code above is correct for the StaffNo? and what do you think it does cause the above error message to be trigged.

thank you very much for your help

How can i connect to sql sever 2005 express from the command prompt.

Iam trying to connect to local copy of sql sever express from the command prompt with this command(sqlcmd) but i get the error below. What should i do to overcome the error. Basically what i want to do is to try out some commandline backup utilities and i deadly want to know how to do a backup from the command prompt. Help is greatly appreciated.

HResult 0x2, level 16, state 1

Named pipes provider: could not open a connection to sql sever [2]

sqlcmd: Error: Microsft sql native client: An error has occurred while estarblishing a connection to the sever. When connecting to sql sever 2005, this failure may be caused by the fact that under the default settings SQL sever does not allow remote connections..

Sqlcmd: Error: Microsoft sql native client: Login time out expired.

Assuming you have the server name correct I would check in the control panel --> administrative tools --> Services and make sure that sql express is running|||I got it right. Looks like it was a typing mistake. But i have one question here, backingup a database from the command prompt to me looks tiresome. What is likely to go wrong if i just copied my projects folder from the production sever to a nother machine where i want the backup to be insteady of going through all these good but confusing steps. I i just copied the folder to a nother location or computer, are the end results not the same with if i had follwed all these database backup procedures. Dont laugh at me, iam still new to this stuff.|||

Hi,

This might be caused since SQL Server 2005 does not allow remote connection under default configuration. Please enable this according to the following KB article.

http://support.microsoft.com/kb/914277/en-us

HTH. If this does not answer your question, please feel free to mark the post as Not Answered and reply. Thank you!

How can I connect to SQL Server 2005 analysis services server programmatically?

Hi, all here,

Would please anyone here give me any idea about how can I connect to SQL Server 2005 analysis services server and send XML request to it programmatically (with Business intelligence development studio in SQL Server 2005)? Thanks a lot.

With best regards,

Yours sincerely,

That's a big topic - you can start here

http://msdn2.microsoft.com/en-us/library/ms186654.aspx

For the XMLA ThinMiner sample look here

http://www.sqlserverdatamining.com/DMCommunity/LiveSamples/124.aspx

Here's a blog entry of someone who's figured it out as well

http://geekswithblogs.net/darrengosbell/archive/2006/05/25/xmlaClient.aspx

|||Hi, Jamie, thanks a lot.

How can I connect to my server over the internet

I need to make connections from my app to the server in the officeHello Joe,
Ask your network administrator to set up a VPN so that you can connect into
the office and communicate securely. Using a VPN is as simple to use as a
dial up connection. Usually easier because you don't have a modem to worry
about.
Aaron Weiker
http://aaronweiker.com/
http://sqlprogrammer.org/

> I need to make connections from my app to the server in the office
>|||I would like to be able to connect without using a VPN. One of the web
hosting companies with deal with just gave me a domain name to connect to. I
would like to do something like that.
"Aaron Weiker" <aaron@.sqlprogrammer.org> wrote in message
news:203279632424549284370779@.news.microsoft.com...
> Hello Joe,
> Ask your network administrator to set up a VPN so that you can connect
into
> the office and communicate securely. Using a VPN is as simple to use as a
> dial up connection. Usually easier because you don't have a modem to worry
> about.
> --
> Aaron Weiker
> http://aaronweiker.com/
> http://sqlprogrammer.org/
>
>|||Hello Joe,
You will need to get directions them from your web hosting company on how
to make the connection. I know the ones I use have step-by-step directions
which may vary depending on how they have it set up.
Aaron Weiker
http://aaronweiker.com/
http://sqlprogrammer.org/
> I would like to be able to connect without using a VPN. One of the web
> hosting companies with deal with just gave me a domain name to connect
> to. I would like to do something like that.
> "Aaron Weiker" <aaron@.sqlprogrammer.org> wrote in message
> news:203279632424549284370779@.news.microsoft.com...
>
> into
>|||I'm not going to be using their database. This is to connect to my own. I
was just using them as an example of what I want to do.
I haven't been able to find any information on how to do this.
Thanks,
Joe
"Aaron Weiker" <aaron@.sqlprogrammer.org> wrote in message
news:218108632425010745037690@.news.microsoft.com...
> Hello Joe,
> You will need to get directions them from your web hosting company on how
> to make the connection. I know the ones I use have step-by-step directions
> which may vary depending on how they have it set up.
> --
> Aaron Weiker
> http://aaronweiker.com/
> http://sqlprogrammer.org/
>
>|||Hello Joe,
Ok, if that's the case then all you need to do use your your database server
s
host name, IP to connect to the database. You'll also need to make sure that
you can connect via the correct ports from your hosted application.
I would not recomend this as you are allowing open connections to your datab
ase
across the internet. You should at least only allow connections from your
hosting company.
Aaron Weiker
http://aaronweiker.com/
http://sqlprogrammer.org/
> I'm not going to be using their database. This is to connect to my
> own. I was just using them as an example of what I want to do.
> I haven't been able to find any information on how to do this.
> Thanks,
> Joe
> "Aaron Weiker" <aaron@.sqlprogrammer.org> wrote in message
> news:218108632425010745037690@.news.microsoft.com...
>sql

How can I connect to Microsoft SQL Server 2005 CTP with Microsoft SQL Server 2005 Express Manage

I installed Microsoft SQL Server 2005 Express Manager and connect to SQL 2000 normally
but when I connect to Microsoft SQL Server 2005 CTP, I failed! when I open SQL Configuration Manager,
I find the SQL Server had been running, after I opened "Attribute" window, I find "This account" box had been checked,
and "NT AUTHORITY\NETWORK SERVICE" was displayed in Account Name edit box, "**********" was displayed in "Password" edit box.

I think that the reason I can't connect to Microsoft SQL Server 2005 CTP is that I can't know the name of SQL Server 200, account, password.
right? In Microsoft SQL Server 2000 I can find the name of server from "Service Manager" and default accunt is "SA", the default password is blank.
but I can't find it in Microsoft SQL Server 2005. Any more, when I open "Express Manager", there are two radio button, "Windows Authentication" and "SQL Server Authentication",
which one will I choose? Does "Server Instance" point the name of SQL Server 2000? or Does "Server Instance" point the name of my computer?

Hi,

Try connecting with '.\SqlExpress' as the instance name. If you haven't specified a username and password then, windows authentication is the mode of logging into Sql2005...

cheers,

Paul June A. Domag

|||Hi,

You can also enable SQL Authentication. Follow instructions to enable it:
Enabling SQL Authentication

STEP1:
You need to enable SQL Authentication on the machine. This is complex in SQLExpress and you need to edit the registry. Copy the following lines of text onto a Notepad and save it with extention .REG.
-COPY BELOW
Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer]
"LoginMode"=dword:00000002
-COPY TILL THIS
STEP2: Double Click on the .REG file to change the registry. This change the Authentication Mode to enable SQL Authentication.

STEP3: Restart the SQL Server Express Service or reboot the machine for registry change to take effect.

Next, you need to add a user with SQLLogin.
To do this: Login to SQLExpress using Express Manager and run the script below:
-
USE MASTER
sp_addLogin 'newuser', 'newuser@.123'
GO
sp_addsrvrolemember 'newuser', 'sysadmin'
GO
-
Once this is done, you can logon with the username/password combination.

Regards,
Vikram

|||Hi Vikram,
thank you for this post - was of great help !

Regards,
Fabian
|||Hey this is very cool! But I have a question- in the past it seems these settings follows each individual SQL Server Instance, but now it seems its a global setting as it does not require any instance specific configuration?|||

Help!!!

Please help me sort through this mass of web server information and configuration. I don't know where to begin?

|||

Thank you, I am a novice in this area.

Charles

|||

Hi William,

No, configuration is still instance specific. Each instance has a seperate registry hive in the format:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.x
where x is some integer.

The mapping from MSSQL.x to the actual instance name can be found in HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names.

Il-Sung.

How can I connect to Microsoft SQL Server 2005 CTP with Microsoft SQL Server 2005 Express Ma

Hi,

Try connecting with '.\SqlExpress' as the instance name. If you haven't specified a username and password then, windows authentication is the mode of logging into Sql2005...

cheers,

Paul June A. Domag

|||Hi,

You can also enable SQL Authentication. Follow instructions to enable it:
Enabling SQL Authentication

STEP3: Restart the SQL Server Express Service or reboot the machine for registry change to take effect.

Regards,
Vikram

|||Hi Vikram,
thank you for this post - was of great help !

Regards,
Fabian|||Hey this is very cool! But I have a question- in the past it seems these settings follows each individual SQL Server Instance, but now it seems its a global setting as it does not require any instance specific configuration?|||

Help!!!

Please help me sort through this mass of web server information and configuration. I don't know where to begin?

|||

Thank you, I am a novice in this area.

Charles

|||

Hi William,

No, configuration is still instance specific. Each instance has a seperate registry hive in the format:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.x
where x is some integer.

The mapping from MSSQL.x to the actual instance name can be found in HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names.

Il-Sung.

How can I connect to Microsoft SQL Server 2005 CTP with Microsoft SQL Server 2005 Express Ma

Hi,

Try connecting with '.\SqlExpress' as the instance name. If you haven't specified a username and password then, windows authentication is the mode of logging into Sql2005...

cheers,

Paul June A. Domag

|||Hi,

You can also enable SQL Authentication. Follow instructions to enable it:
Enabling SQL Authentication

STEP3: Restart the SQL Server Express Service or reboot the machine for registry change to take effect.

Regards,
Vikram

|||Hi Vikram,
thank you for this post - was of great help !

Regards,
Fabian|||Hey this is very cool! But I have a question- in the past it seems these settings follows each individual SQL Server Instance, but now it seems its a global setting as it does not require any instance specific configuration?|||

Help!!!

Please help me sort through this mass of web server information and configuration. I don't know where to begin?

|||

Thank you, I am a novice in this area.

Charles

|||

Hi William,

No, configuration is still instance specific. Each instance has a seperate registry hive in the format:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.x
where x is some integer.

The mapping from MSSQL.x to the actual instance name can be found in HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names.

Il-Sung.

Friday, March 30, 2012

How can I 'copy' a SQL Express database to SQL Everywhere?

How can I 'copy' a SQL Express database to SQL Everywhere?

How can I convert xml into table using SQL Server 2005?

How can I convert xml into table using SQL Server 2005?

How can i convert Pivot table

How can I convert font in database

How can I convert font in database

How can I convert font in database

How can I convert download SQL Server 2005 to a licensed version.

How Can I Convert Decimal To Hexadecimal

How can I convert datetime to number of minutes

How can I convert DateTime to Date as Parameter?

how can i convert binary(8) to datetime?

How can i convert binary(16) to Integer or numeric?

How can I convert a date and an amount in my select statement

How can I convert 12/2/ to 12/2/current year

How can I control the user to SQL Server?

How can I consume webservices on SQL 2005

How can I consume webservices on SQL 2005

How can I conserve the initial zero when convert numeric to string using STR()

how can i connect with lank password

how can i connect to two databases?

how can i connect to sqlserver2005 express by ado

how can i connect to sqlserver2005 express by ado

How can i connect to Sqlserver2000 from sqlserver2005

how can i connect to sqlserver express manually when i use text boxed

How can i connect to sql sever 2005 express from the command prompt.

How can I connect to SQL Server 2005 analysis services server programmatically?

How can I connect to my server over the internet

How can I connect to Microsoft SQL Server 2005 CTP with Microsoft SQL Server 2005 Express Manage

How can I connect to Microsoft SQL Server 2005 CTP with Microsoft SQL Server 2005 Express Ma

How can I connect to Microsoft SQL Server 2005 CTP with Microsoft SQL Server 2005 Express Ma

hot backup

Blog Archive

About Me