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>
<b>2</b>
<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>
> <b>2</b>
> <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>
<b>2</b>
<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>
<b>2</b>
<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>
> <b>2</b>
> <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>
> <b>2</b>
> <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>
<b>2</b>
<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>
> <b>2</b>
> <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>
<b>2</b>
<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>
<b>2</b>
<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>
> <b>2</b>
> <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>
> <b>2</b>
> <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.

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.

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 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)

go

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, use

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

hope it works now.

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