October 17, 2008 at 1:09 pm
Actually it is working !
You did not use a where clause, so all your rows should have the value "TechOwner_Department" for column Test !
But all the records have been updated to the same value, and that's not what I want.
If line1 is: 'aaaa ', it should be updated to 'aaaa'
If line2 is: 'aaaaaa ', it should be updated to 'aaaaaa'
If line3 is: 'aaaaaaaaa ', it should be updated to 'aaaaaaaaa'
But all the 3 records have been updated to 'aaaa'
October 17, 2008 at 1:09 pm
- it is a client setting. But that setting is used for any object at create time, so the object will use that setting.
- Script the ddl for your tables (easiest with SSMS ) and you'll see which setting is in use.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 17, 2008 at 1:13 pm
halifaxdal (10/17/2008)
Actually it is working !
You did not use a where clause, so all your rows should have the value "TechOwner_Department" for column Test !
But all the records have been updated to the same value, and that's not what I want.
If line1 is: 'aaaa ', it should be updated to 'aaaa'
If line2 is: 'aaaaaa ', it should be updated to 'aaaaaa'
If line3 is: 'aaaaaaaaa ', it should be updated to 'aaaaaaaaa'
But all the 3 records have been updated to 'aaaa'
Darn Access 😉
I thought it would use double quoted stuff for a string value ??
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 17, 2008 at 1:17 pm
OK, here is the scripts: first one is not working, second one is working:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[EPM](
[ID] [int] NOT NULL,
[AppID] [int] NULL,
[AppCode] [varchar](5) NULL,
[Description] [varchar](2000) NULL,
[TechOwner_LastName] [varchar](40) NULL,
[TechOwner_FirstName] [varchar](25) NULL,
[TechOwner_Transit] [varchar](10) NULL,
[TechOwner_Department] [varchar](50) NULL,
[Tech_Vendor] [varchar](100) NULL,
[ClientOwner_LastName] [varchar](40) NULL,
[ClientOwner_FirstName] [varchar](25) NULL,
[ClientOwner_Department] [varchar](60) NULL,
[ClientSponsor] [varchar](50) NULL,
[ClientCode] [char](2) NULL,
[APM_UsageArea] [varchar](50) NULL,
[BITA_GroupName] [varchar](255) NULL,
[RelationshipManager] [nvarchar](255) NULL,
[APM_ValueName] [nvarchar](255) NULL,
[Overall_RiskRating] [nvarchar](255) NULL,
[APM_DispositionName] [nvarchar](255) NULL,
[DBR] [nvarchar](255) NULL,
[RSK_FactorName] [nvarchar](255) NULL,
[RSK_FactorRating] [nvarchar](255) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SupportArea](
[ID] [int] IDENTITY(1,1) NOT NULL,
[OU1] [varchar](50) NULL,
[OU2] [varchar](50) NULL,
[OU3] [varchar](50) NULL,
[OU4] [varchar](50) NULL,
[OrgID] [int] NULL,
[SupportArea] [varchar](50) NULL,
[OrgUnit] [varchar](50) NULL,
[Transit] [varchar](10) NULL,
[Contact] [varchar](50) NULL,
CONSTRAINT [PK_SupportAreaNew] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
October 17, 2008 at 1:33 pm
AS far as ANSI_PADDING goes it isn't only the setting at table creation that matters it also matters what your setting is for
insertion as well. BY default all the standard connection methods set it ON.
Here is a way to find out if you have any odd characters in a string (uses a numbers/tally table[/url]):
[font="Courier New"]DECLARE @test TABLE(id INT, string VARCHAR(10))
-- if you already have a numbers/tally table you don't need this
SELECT TOP 10 -- change this to the length of your column
IDENTITY(INT, 1,1) AS N
INTO
#nums
FROM
sysobjects S1
INSERT INTO @test
SELECT
1,
'abc' --3 chars
UNION ALL
SELECT
2,
'abc ' -- 1 space 4 chars no trim 3 chars with trim
UNION ALL
SELECT
3,
'abc ' -- 2 spaces 5 chars
UNION ALL
SELECT
4,
'abc' + CHAR(9) -- 1 tab 4 chars
UNION ALL
SELECT
5,
'abc' + CHAR(9) + CHAR(9) -- 2 tabs 5 chars
SELECT
id,
string,
LEN(string) AS length,
LEN(RTRIM(string)) AS trim_length,
-- replace tabs with spaces
LEN(RTRIM(REPLACE(string, CHAR(9), ' '))) AS no_tabs_trim_length,
ASCII(SUBSTRING(string, N, 1)) AS ascii_val
FROM
@test JOIN
#nums ON
N <= DATALENGTH(string)
WHERE
-- eliminates spaces, numeric, and standard characters (A-Z, a-z)
ASCII(SUBSTRING(string, N, 1)) NOT BETWEEN 20 AND 126
DROP TABLE #nums
[/font]
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 17, 2008 at 1:45 pm
OK, using ASCII() I found for unknown reason char(160) was inserted as padding.
I believe now I can clear out the table.
But what the heck is char(160)?
October 17, 2008 at 1:47 pm
Problem resolved.
Thanks for all of you who pull me out of this muddy weird pond. But I still don't know why char(160) was padded.
October 17, 2008 at 1:52 pm
According to http://ascii-table.com/ascii-extended-pc-list.php 160 is "á - latin small letter a with acute"
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 17, 2008 at 1:55 pm
Jack Corbett (10/17/2008)
According to http://ascii-table.com/ascii-extended-pc-list.php 160 is "á - latin small letter a with acute"
I know it's á, I just don't know why/how it is padded into my records, I don't want to see this kind of weird behavior happen again.
October 17, 2008 at 2:55 pm
October 17, 2008 at 4:53 pm
Garadin (10/17/2008)
Now that the issue is resolved... Bitmaps?... Seriously? 😛
There you go.
October 17, 2008 at 9:09 pm
halifaxdal (10/17/2008)
Jack Corbett (10/17/2008)
According to http://ascii-table.com/ascii-extended-pc-list.php 160 is "á - latin small letter a with acute"I know it's á, I just don't know why/how it is padded into my records, I don't want to see this kind of weird behavior happen again.
It depends on the character set being used... in many character sets, CHAR(160) is a "hard-space" and it sometimes gets there from people using it to do left or right justification with a guarantee of no line break when it hits a word processor. It's not usually on purpose...
--Jeff Moden
Change is inevitable... Change for the better is not.
October 17, 2008 at 9:12 pm
Oh... and the thing about the BITMAPs is that it's bloody well impossible to copy'n'paste code from them. And, it's actually more difficult for you to make the bit map than it is to copy and paste code. 😉
See the link in my signature for suggestions on how to post code and data on any forum. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
October 18, 2008 at 12:24 am
And the fact that bitmaps are HUGE. Not everyone that checks these forums has a high speed connection, and regardless, it just seems so... 20 years ago, to me =).
Viewing 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply