inserted text take the wrong alignment

i try to insert the following string in the database

the red car (driver)

this string save like this

)the red car (driver

i have a problem when inserting string contains special character at the end of the string.

we have arabic and english string like this

???? ????? (R) radial ????

and it appear in reverse like this

???? (R) radial ???? ?????

You need to check the application that is inserting the data specifically the API commands being used. This is not a SQL Server problem per se. The database engine will store the values as passed from the client and doesn't manipulate it on the server. Also, where are you checking the display of the values? It is possible that the tool is doing something based on your language / regional settings. So this could just be a display issue also. Start with verifying the data in the back end tables directly, then your client code and then whatever UI you are using.|||

hello Umachandar,

me and Batool posted this one together

I do import the data into the database through a certain script, but I thought it was an sql problem, because the data were in the correct alignment before inserting, I see them reversed in the tables directly, actually to test this issue I tried to enter data directly into the database so in the cell I press ctrl + Alt + shift to reverse the alignment inside the cell in table, and when I start submitting my data it is reversed.

how could this be a display problem when it's correct in all other applications on my machine

thank you

|||

I believe I've seen funny behavior in Management Studio when you try to display mixed right-left and left-right scripts. (I doubt this is unique to MS.) Can you inspect the binary contents of the strings and see whether it contains what you expect?

Cheers,

|||

You should verify the data first without involving any UI elements into the picture. The reason I say that it could be a display issue is that the tool might be doing something different when reading and displaying the data. This happens for float data type values today. The accuracy of the digits are different from ISQLW and in some cases two values that differ in say the 17th decimal digit will look the same. But this doesn't mean that the values are the same.

So you could write a script or program that does the insert, reads the data back and verifies it using SQL only. This will eliminate the UI from the picture. Additionally, tracing the calls to the server from the UI / tool via Profiler will also help. You can find out if the provider/driver is translating the string based on code page settings. There are just too many variables involved in this. Is it possible to do the following?

1. Post a simple DDL, insert statements and SELECT which shows the behavior (note that you may have to use the appropriate collation and Unicode data type to avoid any character translation)

2. If #1 doesn't work for you, is it possible to post some steps using say a particular UI (like ISQLW or SSMS). Please be clear on how you are inputting the data (open table, script/open table combination) and so on. Schema and data type of the column(s) are important here also. You talk about entering something in a cell - where is this? What UI are you talking about?

Lastly, the configuration of the OS (language/regional settings) may also be a factor and version of SQL Server. So please post those also.

Insert statement with multiple select statements

hi

first of all is it possible? if so, what am i doing wrong with this

INSERT into TB2

(

ClientCode,
EngagementCode,
EngagementDescription

)

SELECT
(SELECT dbo.tarCustomer.CustID
FROM dbo.tPA00175 INNER JOIN
dbo.tarCustomer ON dbo.tPA00175.CustKey = dbo.tarCustomer.CustKey INNER JOIN
dbo.tPA00007 ON dbo.tPA00175.intJobKey = dbo.tPA00007.intJobKey),

NULL,

SELECT
(SELECT dbo.tPA00175.chrJobNumber
FROM dbo.tPA00175 INNER JOIN
dbo.tarCustomer ON dbo.tPA00175.CustKey = dbo.tarCustomer.CustKey INNER JOIN
dbo.tPA00007 ON dbo.tPA00175.intJobKey = dbo.tPA00007.intJobKey)

the first select statement for works fine, but the second one and all after i get a syntax error near 'select'.

this is just a shortened version of the statement. how would i run select statements for a table to be inserted into with different column names. also with items that are hard coded like the 'null'. thanks

tiborINSERT
into TB2
( ClientCode
, EngagementCode
, EngagementDescription )
SELECT dbo.tarCustomer.CustID
, NULL
, dbo.tPA00175.chrJobNumber
FROM dbo.tPA00175
INNER
JOIN dbo.tarCustomer
ON dbo.tarCustomer.CustKey = dbo.tPA00175.CustKey
INNER
JOIN dbo.tPA00007
ON dbo.tPA00007.intJobKey = dbo.tPA00175.intJobKey|||Thanks alot.. Something so simple but yet such a pain.

have a good one|||Ok the syntax works great but its not doing the insert. i built the select statement with a view so i know all the links are nice and strong and such. it doesnt seem to like the 'from' i think. any suggestions?|||Are you getting any error messages? I don't see anything wrong with r937's code (other than the goofy formatting ;) ).
If you run just the SELECT portion without the insert, does it return records? Are there any constraints on the target table that would prevent inserts (unique indexes, foreign keys...)?|||i apologize for not paying attention. the second part of my select statement had a join that returned nothing so i had to alter that. now its working but im getting the String or binary data would be truncated error. After i figure out which field is causing that, it should be all good. but stay tuned in case something else comes up, haha.

tibor

Insert statement help

any one can help what is wrong on this statement
insert into Test1 (FCODE, FNO) values (
SELECT 'A', Number
FROM dbo.Numbers
WHERE Number >=3
AND Number <= 5
and Number not in (select FNO from Test1 where FCODE = 'A')
)insert into Test1 (FCODE, FNO)
SELECT 'A', Number
FROM dbo.Numbers
WHERE Number >=3
AND Number <= 5
and Number not in (select FNO from Test1 where FCODE = 'A')|||Just AND operator for preventing from NULL's
SELECT 'A', Number
FROM dbo.Numbers
WHERE Number >=3
AND Number <= 5
and Number not in (select FNO from Test1 where FCODE = 'A' AND FNO IS
NOT NULL)
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:9765AB05-8B87-41D7-92C5-C971EB50C549@.microsoft.com...
> insert into Test1 (FCODE, FNO)
> SELECT 'A', Number
> FROM dbo.Numbers
> WHERE Number >=3
> AND Number <= 5
> and Number not in (select FNO from Test1 where FCODE = 'A')
>
>

Insert Statement

Does anyone see anything wrong with this Insert

INSERT INTO dbo.Report
(LossType, Loss, Area)
SELECT [Loss Type], Loss, Area
FROM dbo.[Main Table]

I keep getting does not alow nulls insert fails

this is the Report Table
CREATE TABLE [Report] (
[Incident Report No] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Date] [datetime] NULL ,
[Time] [datetime] NULL ,
[Investigators Name] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Reported by] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Law Enforcement Agency] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Name of Officer] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Evidence Seized] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Associated Reports] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Corrective Action] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_Report_Corrective Action] DEFAULT (N'YES'),
[Comments] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[I/RDocument] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_Report_I/RDocument] DEFAULT (N'SCANNED REPORT'),
[TypeOfIncident] [nvarchar] (300) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Exclusion] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_Report_Exclusion] DEFAULT (N'NO'),
[86_D] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_Report_86_D] DEFAULT (N'NO'),
[Loss] [money] NULL CONSTRAINT [DF_Report_Loss] DEFAULT (0.0000),
[LossType] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Area] [nvarchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Action/Incident] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Security/GC] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_Report_Security/GC] DEFAULT (N'GC'),
CONSTRAINT [PK_Report] PRIMARY KEY CLUSTERED
(
[Incident Report No]
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

and the [Main Table]

CREATE TABLE [Main Table] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[IR Number] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Date] [datetime] NULL ,
[Inspector] [int] NULL ,
[Area] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Violation] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Violation Type] [int] NULL ,
[Loss] [money] NULL ,
[Loss Type] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Employee] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Guest] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Action] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Action Type] [int] NULL ,
[Notes] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_Main Table] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GOYour insert does not supply a value for the column [Incident Report No] which is defined as NOT NULL and does not have a default value.

(Column names with spaces... Shudder...)|||It appears that it's the [Incident Report No] field which is giving you a hassle. It's the sole column that appears to not allow nulls and is not in your insert statement.|||INSERT INTO dbo.Report
( [Incident Report No],
Area,
LossType,
Loss
)
SELECT
dbo.[Main Table].[IR Number]
dbo.[Main Table].Area,
dbo.[Main Table].LossType,
dbo.[Main Table].Loss
FROM dbo.[Main Table]
WHERE [Incident Report No] = [IR NUMBER]

RETURN