rights issue

How do I text base add my domain users group with full rights to my database. I am using teratrax to manage my database. This is what comes up when I click on new database user

-- Replace all lower case words with your own code.

EXECUTE SP_GRANTDBACCESS 'login', 'user_name'Hi,

with that statement you posted you only grant database access for an user. The user cannot do anything in your database if you don't grant him further permissions. What exactly do you mean with full rights? Systemadministrators' rights? Or are DBOwner-Rights sufficient?

Otherwise you should create a role which has specific rights in your databases and add the users group to that role.

:)|||Yeah DBOWNER would be enough rights. How do I add roles and then how do I add domain users group to them?|||To create a new role use:

sp_addrole [ @.rolename = ] 'role'
[ , [ @.ownername = ] 'owner' ]

in the specific database.

Grant Permissions to that role for each object needed:

GRANT
{ ALL [ PRIVILEGES ] | permission [ ,...n ] }
{
[ ( column [ ,...n ] ) ] ON { table | view }
| ON { table | view } [ ( column [ ,...n ] ) ]
| ON { stored_procedure | extended_procedure }
| ON { user_defined_function }
}
TO security_account [ ,...n ]
[ WITH GRANT OPTION ]
[ AS { group | role } ]

To add a user or group to a role use:

sp_addrolemember [ @.rolename = ] 'role' ,
[ @.membername = ] 'security_account'

in the specific database.

To add a user/group to the db_owner role use:

sp_addrolemember [ @.rolename = ] 'db_owner' ,
[ @.membername = ] 'security_account'

in the specific database.

EDIT: Replace 'security_account' with your domain users name in the database, e.g.: [domainname\groupname]

:)

Right-justify a column on export to text

Hello, I have a column (AccountNo per the below create script) that displays
properly with leading zeroes to fill a 22-character column while in SQL.
However, when I use a DTS export to a standard text file with no
transformation, it left-justifies. Could someone pls advise how I can get i
t
to export in a fixed length file preserving the leading zeroes and
right-justified? Thanks, Pancho.
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[GVMOI2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[GVMOI2]
GO
CREATE TABLE [dbo].[GVMOI2] (
[TranDateSold] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CustomerID] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TaxIDNum] [nvarchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TaxIDType] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ApplicationCode] [nvarchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AccountNo] [varchar] (22) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TraceNbr] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CreditAmtCash] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DebitAmtCash] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CreditAmtChecks] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DebitAmtChecks] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TranCode] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TranName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TellerID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BranchNo] [varchar] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CheckReferenceNbr] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[CheckNbr] [nvarchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BankNumber] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Remitter1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Payee1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ThirdParty] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Denomination] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IDType] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[IDNumber] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[IDIssueBy] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[IDOthers] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GOGenerally, the following expression should create the string with the value
right-justified: SELECT RIGHT( SPACE(22) + CAST( col AS VARCHAR ) , 22 )
Anith|||Well, it is right-justifying but losing my leading zeroes. I wish for the
column to generate like this:
0000000000000915999999
0000000000009160000000
Our data has varying length. In the example above, our source data begins
with the 9 and we would like to have leading zeroes as needed to make the
column 22 characters wide. We want to export it to a text file and keep the
zeroes but also keep the right-justified format.
"Anith Sen" wrote:

> Generally, the following expression should create the string with the valu
e
> right-justified: SELECT RIGHT( SPACE(22) + CAST( col AS VARCHAR ) , 22 )
> --
> Anith
>
>|||Instead of SPACE(22), use REPLICATE( '0', 22 ).
Anith|||"Pancho" <Pancho@.discussions.microsoft.com> wrote in message
news:0213C7CC-C11D-40BF-8314-2FE240B37C77@.microsoft.com...
> Well, it is right-justifying but losing my leading zeroes. I wish for the
> column to generate like this:
> 0000000000000915999999
> 0000000000009160000000
> Our data has varying length. In the example above, our source data begins
> with the 9 and we would like to have leading zeroes as needed to make the
> column 22 characters wide. We want to export it to a text file and keep
> the
> zeroes but also keep the right-justified format.
> "Anith Sen" wrote:
>
I use this to right justify and zero fill a string in one of my
applications. May not be the best solution but it is what I came up with
when faced with a similar problem.
SELECT REPLICATE('0', 22 - LEN(ISNULL(column, REPLICATE('0', 22)))) +
ISNULL(column, REPLICATE('0', 22))
Kevin|||Well, both approaches work to create the column and display leading zeroes,
right-justified. However, exporting to a flat file, the zeroes remain but i
t
is defaulting to left-justified. Is there something I need to set in DTS?
Using Kevin's script I got the column to create with leading zeroes, 22
displaying and right justified, but it created a varchar column width of
8000. I would like it to be 22 characters wide in the output file. In DTS
I
changed size to 22 and tried type varchar and char but both resulted in
left-justified output columns in the text file.
"Kevin Haugen" wrote:

> "Pancho" <Pancho@.discussions.microsoft.com> wrote in message
> news:0213C7CC-C11D-40BF-8314-2FE240B37C77@.microsoft.com...
> I use this to right justify and zero fill a string in one of my
> applications. May not be the best solution but it is what I came up with
> when faced with a similar problem.
> SELECT REPLICATE('0', 22 - LEN(ISNULL(column, REPLICATE('0', 22)))) +
> ISNULL(column, REPLICATE('0', 22))
> Kevin
>
>

right to left direction in reporting service

When i add arabic text (right to left language) in a dropdown list (report
parameters). the text in the drop down list appear reversed
e.g: the boy is good --> good is boy the ===> this if i use arabic lang.
can any help me pleaseThere is an attribute of a textbox that might help you ( From Books) (left
to right) and left to right top to bottom lr-tb, etc
Text direction and writing mode
Provides information about the direction and writing mode for the text box.
Direction
Type or select a direction or an expression that evaluates to a direction.
Valid values are LTR and RTL. Click the expression (fx) button to edit the
expression.
Mode
Type or select a writing mode or an expression that evaluates to a writing
mode. Valid values are lr-tb and tb-rl. Click the expression (fx) button to
edit the expression.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Mostafa Salama" <MostafaSalama@.discussions.microsoft.com> wrote in message
news:2E0F2881-202B-4666-AA57-0D14C6672DAE@.microsoft.com...
> When i add arabic text (right to left language) in a dropdown list (report
> parameters). the text in the drop down list appear reversed
> e.g: the boy is good --> good is boy the ===> this if i use arabic
> lang.
> can any help me please|||the problem is not in the text box, it can be handled easily, the problem is
of the report paramet, where it is created automatically by reporting
service, developer can not edit on its properties.
this parameter appear as a drop down list , the arabic words appeares
reversed
like he is a boy
boy a is he
but in arabic of course
!!!!!!!!
so what can i do ?
"Wayne Snyder" wrote:
> There is an attribute of a textbox that might help you ( From Books) (left
> to right) and left to right top to bottom lr-tb, etc
> Text direction and writing mode
> Provides information about the direction and writing mode for the text box.
> Direction
> Type or select a direction or an expression that evaluates to a direction.
> Valid values are LTR and RTL. Click the expression (fx) button to edit the
> expression.
> Mode
> Type or select a writing mode or an expression that evaluates to a writing
> mode. Valid values are lr-tb and tb-rl. Click the expression (fx) button to
> edit the expression.
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Mostafa Salama" <MostafaSalama@.discussions.microsoft.com> wrote in message
> news:2E0F2881-202B-4666-AA57-0D14C6672DAE@.microsoft.com...
> > When i add arabic text (right to left language) in a dropdown list (report
> > parameters). the text in the drop down list appear reversed
> > e.g: the boy is good --> good is boy the ===> this if i use arabic
> > lang.
> > can any help me please
>
>

Right Justifying Numbers

I can't seem to get my numbers to right justify, but text works just fine. I
set
the format direction to "LTR" and the text right justifies, but when a
number is displayed it's always left justified. How do I correct this?Set the TextAllign property to the setting that you want.
"Lee" wrote:
> I can't seem to get my numbers to right justify, but text works just fine. I
> set
> the format direction to "LTR" and the text right justifies, but when a
> number is displayed it's always left justified. How do I correct this?

right justify left zero fill

I have to build a text file with a dollar amount that must be right
justified, left zero filled and no decimal. The field I am extracting data
from is 'amount' and defined as decimal 9(15,2).
Stan Gosselin
On Fri, 28 Oct 2005 05:35:03 -0700, Stan wrote:

>I have to build a text file with a dollar amount that must be right
>justified, left zero filled and no decimal. The field I am extracting data
>from is 'amount' and defined as decimal 9(15,2).
Hi Stan,
You can use the following expression:
RIGHT(REPLICATE('0', 11) + LTRIM(CAST(YourColumn * 100 AS INT)), 11)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thanks again Hugo. I'm just getting exposed to SQL and, because of a short
deadline, I am being lazy about finding the answers. I shoudl be looking
stuff up the old fashioned way.
Stan Gosselin
"Hugo Kornelis" wrote:

> On Fri, 28 Oct 2005 05:35:03 -0700, Stan wrote:
>
> Hi Stan,
> You can use the following expression:
> RIGHT(REPLICATE('0', 11) + LTRIM(CAST(YourColumn * 100 AS INT)), 11)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>
|||Hugo,
If you don't mind, I have a variation on the last question. I have the same
type field (decimal 9(15,2)) and the recipient of a conversion .txt file want
to see just the meaningful digits, no decimals, no zero fill but does want to
see a trailing minus sign for negative numbers. So they want to see 550.45
as 55045 and -45.25 as 4525-.
Should I be sending things like this or should I do a re-post?
Thanks for understanding.
Stan Gosselin
"Stan" wrote:
[vbcol=seagreen]
> Thanks again Hugo. I'm just getting exposed to SQL and, because of a short
> deadline, I am being lazy about finding the answers. I shoudl be looking
> stuff up the old fashioned way.
> --
> Stan Gosselin
>
> "Hugo Kornelis" wrote:
|||On Wed, 2 Nov 2005 13:24:02 -0800, Stan wrote:

>Hugo,
>If you don't mind, I have a variation on the last question. I have the same
>type field (decimal 9(15,2)) and the recipient of a conversion .txt file want
>to see just the meaningful digits, no decimals, no zero fill but does want to
>see a trailing minus sign for negative numbers. So they want to see 550.45
>as 55045 and -45.25 as 4525-.
>Should I be sending things like this or should I do a re-post?
>Thanks for understanding.
Hi Stan,
Sending like this is fine, though a repost might attract more eyes.
Generally, if the subject really changes, post a new message. If it's a
variation on the question, use a reply.
I see that Steve has already answered the question. I trust that his
reply is what you wanted.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thanks for the direction Hugo!
Stan Gosselin
"Hugo Kornelis" wrote:

> On Wed, 2 Nov 2005 13:24:02 -0800, Stan wrote:
>
> Hi Stan,
> Sending like this is fine, though a repost might attract more eyes.
> Generally, if the subject really changes, post a new message. If it's a
> variation on the question, use a reply.
> I see that Steve has already answered the question. I trust that his
> reply is what you wanted.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>
sql

Rich TextBox with Picture and Text

Dear All

I want to create a RichTextBox, and then i want to show picture and Text at a time, suppose

Welcome to MSDN Forum

how can i accompalished this task.

Thanks

You're not really asking a SQL question. What development tool and language are you using to accomplish this? I can move the question to the correct forum based on your answer.

Mike

|||

Sorry for missplaced thread, this problem is already solved, any one interested can refere to following URL

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=667916&SiteID=1

sql

Rich Textbox Value in Reporting Services Report

I am looking to create a a report that displays a value that has been
saved to a database using a rich text box (Infragistics
WebHtmlEditor). The database (SQL server) value is a string that
contains HTML markup (ex: <strong>test</strong>). Is it possible for
reporting services to parse this markup and display the formatted text
within the report?On Sep 25, 2:49 pm, jkpo...@.gmail.com wrote:
> I am looking to create a a report that displays a value that has been
> saved to a database using a rich text box (Infragistics
> WebHtmlEditor). The database (SQL server) value is a string that
> contains HTML markup (ex: <strong>test</strong>). Is it possible for
> reporting services to parse this markup and display the formatted text
> within the report?
This link might be helpful.
http://blogs.msdn.com/bimusings/archive/2005/12/14/503648.aspx
Regards,
Enrique Martinez
Sr. Software Consultant|||Thanks for the link.|||On Sep 27, 12:03 pm, jkpo...@.gmail.com wrote:
> Thanks for the link.
You're welcome. Let me know if I can be of further assistance.
Regards,
Enrique Martinez
Sr. Software Consultant

Rich Text Format interpretation using .RDLC report file

Can someone point me to any RTF text interpretation capability with VB 2005 .rdlc file? I would like to select a field that is saved to SQL server with formatting, and do so without reporting the format string.

This is what I would like to avoid reporting: http://img381.imageshack.us/img381/6217/rtf1gd.png

Thanks, josh.

Rich text formatting is not supported. You would have to write your own custom code to interpret the text to display in the text box.|||

I've searched the web for example code for my report, but haven't had much luck. Any ideas you could provide me?

thanks again.

|||

Refer to my post in this thread. I have a walkthrough and class code that should solve your problem.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=738557&SiteID=1

|||

Thanks, I will give this a try on Reporting Services.

Do you know if this can work with a locally processed report with windows forms?

|||It should work fine. It is a pretty generic class that just renders the RTF to a fixed size, creates an array of images that correspond to the total number of pages then either merges all of the pages into a single image, returns back a specific page, returns back a bit array (which is what is required for reporting services) or returns all of the pages in an ArrayList.

Rich Text Format interpretation using .RDLC report file

Can someone point me to any RTF text interpretation capability with VB 2005 .rdlc file? I would like to select a field that is saved to SQL server with formatting, and do so without reporting the format string.

This is what I would like to avoid reporting: http://img381.imageshack.us/img381/6217/rtf1gd.png

Thanks, josh.

Rich text formatting is not supported. You would have to write your own custom code to interpret the text to display in the text box.|||

I've searched the web for example code for my report, but haven't had much luck. Any ideas you could provide me?

thanks again.

|||

Refer to my post in this thread. I have a walkthrough and class code that should solve your problem.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=738557&SiteID=1

|||

Thanks, I will give this a try on Reporting Services.

Do you know if this can work with a locally processed report with windows forms?

|||It should work fine. It is a pretty generic class that just renders the RTF to a fixed size, creates an array of images that correspond to the total number of pages then either merges all of the pages into a single image, returns back a specific page, returns back a bit array (which is what is required for reporting services) or returns all of the pages in an ArrayList.

Rich Text Fields

I am trying to produce a report using MS SQL Server Reporting Services that
will include data that is stored as Rich Text Format.
If I use a regular TextBox, Reporting Services displays all of the RTF
escape codes around the text.
Do you know of a way to get the text out of a Rich Text Field within either
a SQL Server stored procedure or through an add-in to Reporting Services?Reporting Services currently does not support rich text. The best you can do
at the moment is to write some code to convert the rich text to plain text.
Support for rich text is a high priority for the next version (the version
after SQL Server 2005.)
--
Rajeev Karunakaran [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"TroyS" <troy.stauber@.ilg.com> wrote in message
news:eStQoWnRFHA.4068@.TK2MSFTNGP10.phx.gbl...
>I am trying to produce a report using MS SQL Server Reporting Services that
>will include data that is stored as Rich Text Format.
> If I use a regular TextBox, Reporting Services displays all of the RTF
> escape codes around the text.
> Do you know of a way to get the text out of a Rich Text Field within
> either a SQL Server stored procedure or through an add-in to Reporting
> Services?
>|||thanks for that info...
"Rajeev Karunakaran" <rajeevkarunakaran@.online.microsoft.com> wrote in
message news:uC9X%23gpRFHA.1096@.tk2msftngp13.phx.gbl...
> Reporting Services currently does not support rich text. The best you can
> do at the moment is to write some code to convert the rich text to plain
> text. Support for rich text is a high priority for the next version (the
> version after SQL Server 2005.)
> --
> Rajeev Karunakaran [MSFT]
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "TroyS" <troy.stauber@.ilg.com> wrote in message
> news:eStQoWnRFHA.4068@.TK2MSFTNGP10.phx.gbl...
>>I am trying to produce a report using MS SQL Server Reporting Services
>>that will include data that is stored as Rich Text Format.
>> If I use a regular TextBox, Reporting Services displays all of the RTF
>> escape codes around the text.
>> Do you know of a way to get the text out of a Rich Text Field within
>> either a SQL Server stored procedure or through an add-in to Reporting
>> Services?
>

rich text

Does anyone know if a rich textbox is in the work for 2008?

Thanks

Yes, in fact it was demo'd at TechEd. See http://sqljunkies.com/WebLog/sqlbi/archive/2007/06/07/35631.aspx

|||What we demonstrated at TechEd was an early preview of this functionality. While we hope to get it into SQL 2008, it is not complete yet and might not make it into the final release. We certainly understand how much people want this functionality.

rich text

Does anyone know if a rich textbox is in the work for 2008?

Thanks

Yes, in fact it was demo'd at TechEd. See http://sqljunkies.com/WebLog/sqlbi/archive/2007/06/07/35631.aspx

|||What we demonstrated at TechEd was an early preview of this functionality. While we hope to get it into SQL 2008, it is not complete yet and might not make it into the final release. We certainly understand how much people want this functionality.

rich text

Does anyone know if a rich textbox is in the work for 2008?

Thanks

Yes, in fact it was demo'd at TechEd. See http://sqljunkies.com/WebLog/sqlbi/archive/2007/06/07/35631.aspx

|||What we demonstrated at TechEd was an early preview of this functionality. While we hope to get it into SQL 2008, it is not complete yet and might not make it into the final release. We certainly understand how much people want this functionality.

Rich Text

I have a sql database program that has a memo field that contains text that has Rich Text Control Characters embeded into the text. The control characters are at the begining and the end of the field. Is there a way to remove these charatcters?
Thanks,If your data will fit in within a VARCHAR datatype, try CASTing it to VARCHAR first. Then you can do text manipulation on it. Memo fields won't let you.|||I did an analysis of the Rich Text Control Characters and it appears that the ending Characters are "\fs17". I made up the following command that searches the memo field for these characters and then display the remainder of the memo field:

Mid([SUMMARY],(InStr([SUMMARY],'\fs17')+5))

Thanks for the information!

reverse only the text parts of the field

i have to import addresses from an text file.
the text is reversed but numbers are not reversed
"abcd 12 def" comes as "dcba 12 fed"
if i use the reverse function it reveses also the number
and the it becomes "abcd 21 def"
how i can reverse only the text parts of the field?
thanksChop the parts of the string, cehck every single string for ISNUMERIC and
REVERSE it if it evaluates to false, then put the string together in a new
string.
--
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Sam" wrote:

> i have to import addresses from an text file.
> the text is reversed but numbers are not reversed
> "abcd 12 def" comes as "dcba 12 fed"
> if i use the reverse function it reveses also the number
> and the it becomes "abcd 21 def"
> how i can reverse only the text parts of the field?
> thanks
>
>|||how to Chop the parts of the string?
"Jens S'meyer" <Jens@.[Remove_that][for contacting me]sqlserver2005.de>
wrote in message news:9044AF5F-E604-4EEC-974C-A2CB06AE5756@.microsoft.com...
> Chop the parts of the string, cehck every single string for ISNUMERIC and
> REVERSE it if it evaluates to false, then put the string together in a new
> string.
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Sam" wrote:
>|||Loomk for PATINDEX() in bol
DECLARE @.S Varchar(100)
SET @.s='Some text 123 text'
SELECT PATINDEX('%[0-9]%',@.s)
SELECT LEFT(@.s, PATINDEX('%[0-9]%',@.s)-1)
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Sam" <focus10@.zahav.net.il> wrote in message
news:eYbeeAamFHA.268@.TK2MSFTNGP10.phx.gbl...
> how to Chop the parts of the string?
> "Jens S'meyer" <Jens@.[Remove_that][for contacting me]sqlserver2005.de>
> wrote in message
> news:9044AF5F-E604-4EEC-974C-A2CB06AE5756@.microsoft.com...
>|||Try this.. should handle all the cases...
/*
ddl
Create table TestRev (Id int, Name Varchar(255))
Insert into TestRev values (1, 'abc 123 def 256')
Insert into TestRev values (2, 'olleh 321 dlrow 987')
Insert into TestRev values (3, 'abc 123456789 reversethis 256')
Insert into TestRev values (4, 'sqlserver 2000 or yukon')
*/
set nocount on
declare @.str Varchar(255)
declare @.id char(15)
declare @.strRev Varchar(255)
declare @.word Varchar(255)
declare @.newWord Varchar(255)
declare @.len int
declare @.pos int
Declare Cur_Rev Cursor
For select id, name from TestRev
open Cur_Rev
Fetch next from Cur_Rev into @.id, @.str
while @.@.fetch_status = 0
begin
set @.str = @.str + ' '
set @.strRev = @.str
set @.len = LEN(@.str)
while (@.len > 1)
begin
set @.pos = PATINDEX('% %',@.str)
set @.word = ltrim(rtrim(SUBSTRING(@.str, 1, @.pos)))
if IsNumeric(@.word) <> 1
Begin
set @.newword = reverse(@.word)
set @.strRev = Replace(@.strRev, @.word, @.newWord)
End
set @.str = substring(@.str, @.pos+1, @.len)
set @.len = len(@.str)
if @.pos = 0
break
end
select @.strRev 'Reversed'
fetch next from Cur_Rev into @.id, @.str
set @.strRev = @.str
end
Close Cur_Rev
Deallocate Cur_Rev
go
Thanks,
Prad
"Sam" <focus10@.zahav.net.il> wrote in message
news:eIlxouZmFHA.3648@.TK2MSFTNGP10.phx.gbl...
>i have to import addresses from an text file.
> the text is reversed but numbers are not reversed
> "abcd 12 def" comes as "dcba 12 fed"
> if i use the reverse function it reveses also the number
> and the it becomes "abcd 21 def"
> how i can reverse only the text parts of the field?
> thanks
>|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.programming:544016
Pradeep Kutty wrote:
> Try this.. should handle all the cases...
> /*
> ddl
> Create table TestRev (Id int, Name Varchar(255))
> Insert into TestRev values (1, 'abc 123 def 256')
> Insert into TestRev values (2, 'olleh 321 dlrow 987')
> Insert into TestRev values (3, 'abc 123456789 reversethis 256')
> Insert into TestRev values (4, 'sqlserver 2000 or yukon')
> */
>
> set nocount on
> declare @.str Varchar(255)
> declare @.id char(15)
> declare @.strRev Varchar(255)
> declare @.word Varchar(255)
> declare @.newWord Varchar(255)
> declare @.len int
> declare @.pos int
>
> Declare Cur_Rev Cursor
> For select id, name from TestRev
>
> open Cur_Rev
> Fetch next from Cur_Rev into @.id, @.str
> while @.@.fetch_status = 0
> begin
> set @.str = @.str + ' '
> set @.strRev = @.str
> set @.len = LEN(@.str)
> while (@.len > 1)
> begin
> set @.pos = PATINDEX('% %',@.str)
> set @.word = ltrim(rtrim(SUBSTRING(@.str, 1, @.pos)))
> if IsNumeric(@.word) <> 1
> Begin
> set @.newword = reverse(@.word)
> set @.strRev = Replace(@.strRev, @.word, @.newWord)
> End
> set @.str = substring(@.str, @.pos+1, @.len)
> set @.len = len(@.str)
> if @.pos = 0
> break
> end
> select @.strRev 'Reversed'
> fetch next from Cur_Rev into @.id, @.str
> set @.strRev = @.str
> end
> Close Cur_Rev
> Deallocate Cur_Rev
> go
>
> Thanks,
> Prad
>
Doesn't work for if the string contains a string and it's reverse:
insert into TestRev values (5,'abc cba abc cba')
and run again (I get) 'abc abc abc abc'
I cannot work out how to fix that (other than to build up StrRev one
word at a time rather than doing replaces.
Damien|||yes what you said is correct:
heres a fix for that.. this should work try...
set nocount on
declare @.str Varchar(255)
declare @.id char(15)
declare @.strRev Varchar(255)
declare @.word Varchar(255)
declare @.newWord Varchar(255)
declare @.len int
declare @.pos int
Declare Cur_Rev Cursor
For select id, name from TestRev
open Cur_Rev
Fetch next from Cur_Rev into @.id, @.str
while @.@.fetch_status = 0
begin
set @.str = @.str + ' '
set @.strRev = ''
set @.len = LEN(@.str)
while (@.len > 1)
begin
set @.pos = PATINDEX('% %',@.str)
set @.word = ltrim(rtrim(SUBSTRING(@.str, 1, @.pos)))
if IsNumeric(@.word) <> 1
Begin
Select @.newword = reverse(@.word)
set @.strRev = @.strRev + ' ' + @.newWord
End
if IsNumeric(@.word) = 1
set @.strRev = @.strRev + ' ' + @.word
set @.str = substring(@.str, @.pos+1, @.len)
set @.len = len(@.str)
if @.pos = 0
break
end
select ltrim(rtrim(@.strRev)) 'Reversed'
fetch next from Cur_Rev into @.id, @.str
set @.strRev = @.str
end
Close Cur_Rev
Deallocate Cur_Rev
go
Thank,
Prad
"Damien" <Damien_The_Unbeliever@.hotmail.com> wrote in message
news:1123253615.736504.302120@.g43g2000cwa.googlegroups.com...
> Pradeep Kutty wrote:
> Doesn't work for if the string contains a string and it's reverse:
> insert into TestRev values (5,'abc cba abc cba')
> and run again (I get) 'abc abc abc abc'
> I cannot work out how to fix that (other than to build up StrRev one
> word at a time rather than doing replaces.
> Damien
>|||If you have a value seperator (delimiter) you could use this.
e.g
-- digits table needs to be created once
select top 8000 digit=identity(int,1,1)
into digits
from sysobjects,syscolumns
go
create function dbo.xreverse(@.input varchar(8000))
returns varchar(8000)
as
begin
declare @.tb table (i int identity primary key, value sysname)
declare @.s varchar(8000)
declare @.delim char
set @.delim=space(1)
set @.input = @.delim+rtrim(ltrim(@.input))+@.delim
insert @.tb
select case when isnumeric(value)=0 then reverse(value) else value end
from (
select substring(@.input, n.digit+1,
charindex(@.delim,@.input,n.digit+1)-n.digit-1) value
from digits as n
where n.digit<len(@.input)
and substring(@.input,n.digit,1) = @.delim
)x
select @.s=isnull(@.s+@.delim,'')+value from @.tb
return @.s
end
go
select dbo.xreverse(Name)
from TestRev
-oj
"Damien" <Damien_The_Unbeliever@.hotmail.com> wrote in message
news:1123253615.736504.302120@.g43g2000cwa.googlegroups.com...
> Pradeep Kutty wrote:
> Doesn't work for if the string contains a string and it's reverse:
> insert into TestRev values (5,'abc cba abc cba')
> and run again (I get) 'abc abc abc abc'
> I cannot work out how to fix that (other than to build up StrRev one
> word at a time rather than doing replaces.
> Damien
>|||in most cases it's work nice
but in cases like "cba (2000) fed" or "cba 2/3 fed" or "cba 2-3 fed"
it's wrong
sam
"Pradeep Kutty" <pradeepk@.healthasyst.com> wrote in message
news:elyhOYdmFHA.3828@.TK2MSFTNGP12.phx.gbl...
> yes what you said is correct:
> heres a fix for that.. this should work try...
>
> set nocount on
> declare @.str Varchar(255)
> declare @.id char(15)
> declare @.strRev Varchar(255)
> declare @.word Varchar(255)
> declare @.newWord Varchar(255)
> declare @.len int
> declare @.pos int
>
> Declare Cur_Rev Cursor
> For select id, name from TestRev
>
> open Cur_Rev
> Fetch next from Cur_Rev into @.id, @.str
> while @.@.fetch_status = 0
> begin
> set @.str = @.str + ' '
> set @.strRev = ''
> set @.len = LEN(@.str)
> while (@.len > 1)
> begin
> set @.pos = PATINDEX('% %',@.str)
> set @.word = ltrim(rtrim(SUBSTRING(@.str, 1, @.pos)))
> if IsNumeric(@.word) <> 1
> Begin
> Select @.newword = reverse(@.word)
> set @.strRev = @.strRev + ' ' + @.newWord
> End
> if IsNumeric(@.word) = 1
> set @.strRev = @.strRev + ' ' + @.word
> set @.str = substring(@.str, @.pos+1, @.len)
> set @.len = len(@.str)
> if @.pos = 0
> break
> end
> select ltrim(rtrim(@.strRev)) 'Reversed'
> fetch next from Cur_Rev into @.id, @.str
> set @.strRev = @.str
> end
> Close Cur_Rev
> Deallocate Cur_Rev
> go
> Thank,
> Prad
> "Damien" <Damien_The_Unbeliever@.hotmail.com> wrote in message
> news:1123253615.736504.302120@.g43g2000cwa.googlegroups.com...
>

Returning Text String.

I am returning a rather large result set using a basic select query.
One of the columns being returned is a char(1) column.
I am wondering if their is a way in sql to return an associated text string
to a char(1) column.
Eg:
If char(1) = 'c' Then text string returned in place place of 'c' should be
contractor.
I am sure i could possibly do this using an if statement on the result
set..of some kind.
Can it be done using any other method?You weren't very specific. Perhaps:
select
case when MyCol = 'c' then 'contractor'
else 'something else'
end
from
MyTable
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"AJ" <AJ@.discussions.microsoft.com> wrote in message
news:B8B7C546-3708-4326-8311-580CF570FF62@.microsoft.com...
I am returning a rather large result set using a basic select query.
One of the columns being returned is a char(1) column.
I am wondering if their is a way in sql to return an associated text string
to a char(1) column.
Eg:
If char(1) = 'c' Then text string returned in place place of 'c' should be
contractor.
I am sure i could possibly do this using an if statement on the result
set..of some kind.
Can it be done using any other method?|||Another way would be to have all of these values in a lookup table and join
to it.
E.g.
Lookup Value, Lookup Name
C, Contractor
Then join to this table on Lookup Value but return Lookup Name in your
select statement
select col1, col2, LookupName
from table
inner join LookupTable
on table.lookupvalue = lookuptable.lookupValue
Hope this helps
Clint Colefax|||Yes you can use a CASE WHEN statement to return the data in the format you
want it. Below is an example of how to do this with your sinaro.
Create TABLE #TEMP
(
EMPTYPE nvarchar (1),
empNAME nvarchar (5)
)
Insert #temp
values ( 'c','Dave')
Insert #temp
values ( 'f','Joe')
Insert #temp
values ( 'p','Rick')
Insert #temp
values ( 'c','Bo')
Select CASE WHEN emptype ='c'
THEN 'Contractor'
WHEN emptype = 'f'
THEN 'FULLTIME'
WHEN emptype = 'p'
THEN 'PARTTIME'
END AS EMPLOYEE_TYPE,
EMPNAME
from #temp
dROP TABLE #TEMP
--
Please refer to books online for more indepth look at how to use CASE
statement.
Hope this helps
JEP
"AJ" wrote:

> I am returning a rather large result set using a basic select query.
> One of the columns being returned is a char(1) column.
> I am wondering if their is a way in sql to return an associated text strin
g
> to a char(1) column.
> Eg:
> If char(1) = 'c' Then text string returned in place place of 'c' should b
e
> contractor.
> I am sure i could possibly do this using an if statement on the result
> set..of some kind.
> Can it be done using any other method?

Returning Text + Column

Dear All
CREATE TABLE [dbo].[Tester] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[TesterText] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Tester] WITH NOCHECK ADD
CONSTRAINT [PK_Tester] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
GO
insert into Tester (TesterText) Values ('AAAA')
insert into Tester (TesterText) Values ('BBBB')
insert into Tester (TesterText) Values ('CCCC')
insert into Tester (TesterText) Values ('DDDD')
Given the above Schema and insert I would like to return a varchar with text
+ the column TesterText, so in this case I would like a varchar with 'Hello
AAAA;Hello BBBB;Hello CCCC;Hello DDDD;'.
I would rather not use cursors.
TIA
JJulie
Why not doing shuch things on the client side
I did not check NULLs and for another world it does not work as well
DECLARE @.v VARCHAR(100)
SET @.v=''
SELECT @.v=@.v+''+TesterText+';'+'Hello' FROM Tester
SELECT REPLACE(LEFT(@.v,LEN(@.v)-6),' ','')+';'
"Julie" <Julie@.discussions.microsoft.com> wrote in message
news:3C344282-FD6C-46DC-B051-1830200B247B@.microsoft.com...
> Dear All
> CREATE TABLE [dbo].[Tester] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [TesterText] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Tester] WITH NOCHECK ADD
> CONSTRAINT [PK_Tester] PRIMARY KEY CLUSTERED
> (
> [ID]
> ) ON [PRIMARY]
> GO
> insert into Tester (TesterText) Values ('AAAA')
> insert into Tester (TesterText) Values ('BBBB')
> insert into Tester (TesterText) Values ('CCCC')
> insert into Tester (TesterText) Values ('DDDD')
> Given the above Schema and insert I would like to return a varchar with
text
> + the column TesterText, so in this case I would like a varchar with
'Hello
> AAAA;Hello BBBB;Hello CCCC;Hello DDDD;'.
> I would rather not use cursors.
> TIA
> J
>|||The best place for this kind of non-relational concatenation is on the
client, not in the database. However, see http://www.aspfaq.com/2529 for a
starting point if you really, really, really want to do this in the
database.
On 3/15/05 6:05 AM, in article
3C344282-FD6C-46DC-B051-1830200B247B@.microsoft.com, "Julie"
<Julie@.discussions.microsoft.com> wrote:

> Dear All
> CREATE TABLE [dbo].[Tester] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [TesterText] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Tester] WITH NOCHECK ADD
> CONSTRAINT [PK_Tester] PRIMARY KEY CLUSTERED
> (
> [ID]
> ) ON [PRIMARY]
> GO
> insert into Tester (TesterText) Values ('AAAA')
> insert into Tester (TesterText) Values ('BBBB')
> insert into Tester (TesterText) Values ('CCCC')
> insert into Tester (TesterText) Values ('DDDD')
> Given the above Schema and insert I would like to return a varchar with te
xt
> + the column TesterText, so in this case I would like a varchar with 'Hell
o
> AAAA;Hello BBBB;Hello CCCC;Hello DDDD;'.
> I would rather not use cursors.
> TIA
> J
>|||Thanks Guys,
I have given Uri the 'answered' as he was the first :D
Anyway what I'm actually doing is preparing a number of Dynamic SQL
Statments for a nightly batch run (we have the column + table names in a
table) however I couldn't find a way of concatinating them together.
Anyway thanks for that
J
"Julie" wrote:

> Dear All
> CREATE TABLE [dbo].[Tester] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [TesterText] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Tester] WITH NOCHECK ADD
> CONSTRAINT [PK_Tester] PRIMARY KEY CLUSTERED
> (
> [ID]
> ) ON [PRIMARY]
> GO
> insert into Tester (TesterText) Values ('AAAA')
> insert into Tester (TesterText) Values ('BBBB')
> insert into Tester (TesterText) Values ('CCCC')
> insert into Tester (TesterText) Values ('DDDD')
> Given the above Schema and insert I would like to return a varchar with te
xt
> + the column TesterText, so in this case I would like a varchar with 'Hell
o
> AAAA;Hello BBBB;Hello CCCC;Hello DDDD;'.
> I would rather not use cursors.
> TIA
> J
>

Returning recordset as xml via DomDocument in ASP results in error

Hi all,
I'm trying to return a recordset obtained from a sql server 2000 stored
procedure, as a text/XML content type via old asp.
The problem is the sql data contains foreign characters and when returning
the xml page to the IE6, the default xsl shows the error "invalid character"
for the foreign characters.
The code for the sp is the following:
Create Procedure webTest( @.nDummy Int )
As
Begin
Select Id = 1, Name = 'PARAGRAPH SYMBOL '
End
The code for the asp is:
<%@. Language=JavaScript %>
<%
Response.ContentType = "text/xml";
var cn=Server.CreateObject("ADODB.Connection");
var rs=Server.CreateObject("ADODB.Recordset");
var cmd=Server.CreateObject("ADODB.Command");
cn.Open("Provider=SQLOLEDB.1;User ID=xxxx;Password=yyyy;Initial
Catalog=MATDESA;Data Source=sqlrespaldo01");
rs.CursorLocation = 3;
cmd.ActiveConnection = cn;
cmd.CommandType = adCmdStoredProc;
cmd.CommandText = "webTest";
var prm = cmd.CreateParameter("@.nDummy", adInteger, adParamInput, 0, 123);
cmd.Parameters.Append(prm);
var rs = cmd.Execute();
var doc = Server.CreateObject("MSXML2.DOMDocument");
rs.Save(doc, adPersistXML);
Response.Write(doc.xml);
rs.Close();
cn.Close();
%>
Looking at the return on IE6 it returns "invalid character" on the ""
symbol
But if I modify the code to save the recordset directly to the Response
stream, then it works. I mean if substituting the lines
var doc = Server.CreateObject("MSXML2.DOMDocument");
rs.Save(doc, adPersistXML);
Response.Write(doc.xml);
by
rs.Save(Response, adPersistXML);
the browser shows ok the foreign symbols.
Where is the error on the first case? It looks like the created doc assumes
ascii character encoding or something alike...
Any hint is welcomed
Thanks in advance
Sammy
"SammyBar" <sammybar@.gmail.com> wrote in message
news:e%2393NI9QHHA.3592@.TK2MSFTNGP06.phx.gbl...
> Hi all,
> I'm trying to return a recordset obtained from a sql server 2000 stored
> procedure, as a text/XML content type via old asp.
> The problem is the sql data contains foreign characters and when returning
> the xml page to the IE6, the default xsl shows the error "invalid
character"
> for the foreign characters.
> The code for the sp is the following:
> Create Procedure webTest( @.nDummy Int )
> As
> Begin
> Select Id = 1, Name = 'PARAGRAPH SYMBOL '
> End
> The code for the asp is:
> <%@. Language=JavaScript %>
> <%
> Response.ContentType = "text/xml";
> var cn=Server.CreateObject("ADODB.Connection");
> var rs=Server.CreateObject("ADODB.Recordset");
> var cmd=Server.CreateObject("ADODB.Command");
> cn.Open("Provider=SQLOLEDB.1;User ID=xxxx;Password=yyyy;Initial
> Catalog=MATDESA;Data Source=sqlrespaldo01");
> rs.CursorLocation = 3;
> cmd.ActiveConnection = cn;
> cmd.CommandType = adCmdStoredProc;
> cmd.CommandText = "webTest";
> var prm = cmd.CreateParameter("@.nDummy", adInteger, adParamInput, 0, 123);
> cmd.Parameters.Append(prm);
> var rs = cmd.Execute();
> var doc = Server.CreateObject("MSXML2.DOMDocument");
> rs.Save(doc, adPersistXML);
> Response.Write(doc.xml);
> rs.Close();
> cn.Close();
> %>
> Looking at the return on IE6 it returns "invalid character" on the ""
> symbol
> But if I modify the code to save the recordset directly to the Response
> stream, then it works. I mean if substituting the lines
> var doc = Server.CreateObject("MSXML2.DOMDocument");
> rs.Save(doc, adPersistXML);
> Response.Write(doc.xml);
> by
> rs.Save(Response, adPersistXML);
> the browser shows ok the foreign symbols.
> Where is the error on the first case? It looks like the created doc
assumes
> ascii character encoding or something alike...
>
All strings in script are unicode.
Hence doc.xml returns a unicode encoding of the XML document content.
Response.Write takes such a string and sends it to the client encoding it
using the the current Response.CodePage setting (Session.Codepage on
IIS5[.1])
The codepage defaults to the system codepage, typically in the West 1252.
Hence the character is encoded as a standard ANSI 1252 single byte
character.
However the receiving client XML DOM is expecting UTF-8 which is the default
encoding used by XML. Hence the character causes an error.
rs.Save(Response, adPersistXML)
OTH, will send the XML generated by the recordset in it's 'native' UTF-8
format directly to the client without being converted to another codepage.
Hence all is well.
As Martin has said this is the recommended approach.
BTW, personnally I prefer to tell the client explicitly it is receiving
UTF-8 using:-
Response.CharSet = "UTF-8"
Immediately after setting the content type.
Anthony.

Returning recordset as xml via DomDocument in ASP results in error

Hi all,
I'm trying to return a recordset obtained from a sql server 2000 stored
procedure, as a text/XML content type via old asp.
The problem is the sql data contains foreign characters and when returning
the xml page to the IE6, the default xsl shows the error "invalid character"
for the foreign characters.
The code for the sp is the following:
Create Procedure webTest( @.nDummy Int )
As
Begin
Select Id = 1, Name = 'PARAGRAPH SYMBOL '
End
The code for the asp is:
<%@. Language=JavaScript %>
<%
Response.ContentType = "text/xml";
var cn=Server.CreateObject("ADODB.Connection");
var rs=Server.CreateObject("ADODB.Recordset");
var cmd=Server.CreateObject("ADODB.Command");
cn.Open("Provider=SQLOLEDB.1;User ID=xxxx;Password=yyyy;Initial
Catalog=MATDESA;Data Source=sqlrespaldo01");
rs.CursorLocation = 3;
cmd.ActiveConnection = cn;
cmd.CommandType = adCmdStoredProc;
cmd.CommandText = "webTest";
var prm = cmd.CreateParameter("@.nDummy", adInteger, adParamInput, 0, 123);
cmd.Parameters.Append(prm);
var rs = cmd.Execute();
var doc = Server.CreateObject("MSXML2.DOMDocument");
rs.Save(doc, adPersistXML);
Response.Write(doc.xml);
rs.Close();
cn.Close();
%>
Looking at the return on IE6 it returns "invalid character" on the ""
symbol
But if I modify the code to save the recordset directly to the Response
stream, then it works. I mean if substituting the lines
var doc = Server.CreateObject("MSXML2.DOMDocument");
rs.Save(doc, adPersistXML);
Response.Write(doc.xml);
by
rs.Save(Response, adPersistXML);
the browser shows ok the foreign symbols.
Where is the error on the first case? It looks like the created doc assumes
ascii character encoding or something alike...
Any hint is welcomed
Thanks in advance
Sammy"SammyBar" <sammybar@.gmail.com> wrote in message
news:e%2393NI9QHHA.3592@.TK2MSFTNGP06.phx.gbl...
> Hi all,
> I'm trying to return a recordset obtained from a sql server 2000 stored
> procedure, as a text/XML content type via old asp.
> The problem is the sql data contains foreign characters and when returning
> the xml page to the IE6, the default xsl shows the error "invalid
character"
> for the foreign characters.
> The code for the sp is the following:
> Create Procedure webTest( @.nDummy Int )
> As
> Begin
> Select Id = 1, Name = 'PARAGRAPH SYMBOL '
> End
> The code for the asp is:
> <%@. Language=JavaScript %>
> <%
> Response.ContentType = "text/xml";
> var cn=Server.CreateObject("ADODB.Connection");
> var rs=Server.CreateObject("ADODB.Recordset");
> var cmd=Server.CreateObject("ADODB.Command");
> cn.Open("Provider=SQLOLEDB.1;User ID=xxxx;Password=yyyy;Initial
> Catalog=MATDESA;Data Source=sqlrespaldo01");
> rs.CursorLocation = 3;
> cmd.ActiveConnection = cn;
> cmd.CommandType = adCmdStoredProc;
> cmd.CommandText = "webTest";
> var prm = cmd.CreateParameter("@.nDummy", adInteger, adParamInput, 0, 123);
> cmd.Parameters.Append(prm);
> var rs = cmd.Execute();
> var doc = Server.CreateObject("MSXML2.DOMDocument");
> rs.Save(doc, adPersistXML);
> Response.Write(doc.xml);
> rs.Close();
> cn.Close();
> %>
> Looking at the return on IE6 it returns "invalid character" on the ""
> symbol
> But if I modify the code to save the recordset directly to the Response
> stream, then it works. I mean if substituting the lines
> var doc = Server.CreateObject("MSXML2.DOMDocument");
> rs.Save(doc, adPersistXML);
> Response.Write(doc.xml);
> by
> rs.Save(Response, adPersistXML);
> the browser shows ok the foreign symbols.
> Where is the error on the first case? It looks like the created doc
assumes
> ascii character encoding or something alike...
>
All strings in script are unicode.
Hence doc.xml returns a unicode encoding of the XML document content.
Response.Write takes such a string and sends it to the client encoding it
using the the current Response.CodePage setting (Session.Codepage on
IIS5[.1])
The codepage defaults to the system codepage, typically in the West 1252.
Hence the character is encoded as a standard ANSI 1252 single byte
character.
However the receiving client XML DOM is expecting UTF-8 which is the default
encoding used by XML. Hence the character causes an error.
rs.Save(Response, adPersistXML)
OTH, will send the XML generated by the recordset in it's 'native' UTF-8
format directly to the client without being converted to another codepage.
Hence all is well.
As Martin has said this is the recommended approach.
BTW, personnally I prefer to tell the client explicitly it is receiving
UTF-8 using:-
Response.CharSet = "UTF-8"
Immediately after setting the content type.
Anthony.