HELP HELP varchar

I have a field type of "varchar"

It can not accept to Store Arabic Characters!!! plus i can't change it into ntext or nvarchar my system which goin to export the data into SQL server by suing insert command will hang. so im stucked with varchar!!!

here some sample of my data stored currently in My SQL SERVER 2000

" /   "

Plzzzz help me if there any other way to do it

Do you have the right collation set in your database?

Have a look in BOL (Books on Line) at COLLATE - there's a lot of info in there about how to deal with charachters from different languages and alphabets.

/Kenneth

|||If you want to store arabic characters you will have to change your column data types to nvarchar. So you will have to investigate the problem you are describing while using the nvarchar column. What is the actual problem here ? I am not sure / doubt that this is related to collation problems, but further investigation might help us to solve the problem.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.desql

help for query

Hello,
I have a table "log" with the following fields:

-log_id(int)
-cust_id(string)
-type(int)
-service_id(int)

type may be 0,1 or 10

I need to fetch one row for each type for each service_id for a specific
cust_id.
That is, if I have 3 different service_id's and I assume that at least
one row for each type exists with the specific cust_id, I should get 3*3
= 9 rows: no more, no less.
I prefer not to use a stored procedure.
How may I do?
Is a self-join the right way?

Thank you
Regards

--
elimina carraro per rispondere via emailYou don't appear to have any criteria for
the one row you want if there are more than one.
If that's the case and log_id is unique, this should work

select max(log_id),cust_id,type,service_id
from log
group by cust_id,type,service_id|||markc600@.hotmail.com ha scritto:
> You don't appear to have any criteria for
> the one row you want if there are more than one.
> If that's the case and log_id is unique, this should work
> select max(log_id),cust_id,type,service_id
> from log
> group by cust_id,type,service_id

It work great, thanks!

I got the same result with the following, but was much more complicated:

select *
from log a, log b
where a.cust_id='myid' and a.type IN (0,10,1) and a.log_id=b.log_id
and b.log_id IN (select top 1 log_id from log c where
c.service_id=a.service_id and c.type=a.type)
order by a.service_id

--
elimina carraro per rispondere via email

Help Converting Character string to smalldatetime data type

I am attempting to pass a string variable from a form to an SQL statement an
d
I get this error "Syntax error converting character string to smalldatetime
data type"
Your assistance is appreciated in advance....
Here is my code
<%
Dim TESTTHIS__MMColParam1
TESTTHIS__MMColParam1 = "0"
If (Request.Form("StartDate") <> "") Then
TESTTHIS__MMColParam1 = Request.Form("StartDate")
End If
%>
<%
Dim TESTTHIS__MMColParam2
TESTTHIS__MMColParam2 = "0"
If (Request.Form("EndDate") <> "") Then
TESTTHIS__MMColParam2 = Request.Form("EndDate")
End If
%>
<%
Dim TESTTHIS
Dim TESTTHIS_numRows
Set TESTTHIS = Server.CreateObject("ADODB.Recordset")
TESTTHIS.ActiveConnection = MM_CSSMetricsCONN_STRING
TESTTHIS.Source = "SELECT Team, SumofInAdherenceSecsQty /
(SumofInAdherenceSecsQty + SumofOutOfAdherenceSecsQty) AS Adherence FROM
(SELECT TOP 1000 Team, SUM(OutOfAdherenceSecsQty) AS
SumofOutOfAdherenceSecsQty, SUM(InAdherenceSecsQty) AS
SumofInAdherenceSecsQty FROM bo.[National Call Stats] WHERE (Site =
'Dallas') AND ([Date] >='" + Replace(TESTTHIS__MMColParam1, "'", "''") + "')
AND ([Date] <= '" + Replace(TESTTHIS__MMColParam2, "'", "''") + "')
GROUP BY Team, Site ORDER BY Team, Site) DERIVEDTBL"
TESTTHIS.CursorType = 0
TESTTHIS.CursorLocation = 2
TESTTHIS.LockType = 1
TESTTHIS.Open()
TESTTHIS_numRows = 0
%>
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200606/1Run a Profiler trace to see the actual select statement being sent to SQL
Sever. Also, consider using parameters instead of concatenating values.
Parameters are more secure and you don't have to double-up embedded quotes.
Hope this helps.
Dan Guzman
SQL Server MVP
"Chamark via webservertalk.com" <u21870@.uwe> wrote in message
news:6168f7c85cf47@.uwe...
>I am attempting to pass a string variable from a form to an SQL statement
>and
> I get this error "Syntax error converting character string to
> smalldatetime
> data type"
> Your assistance is appreciated in advance....
> Here is my code
> <%
> Dim TESTTHIS__MMColParam1
> TESTTHIS__MMColParam1 = "0"
> If (Request.Form("StartDate") <> "") Then
> TESTTHIS__MMColParam1 = Request.Form("StartDate")
> End If
> %>
> <%
> Dim TESTTHIS__MMColParam2
> TESTTHIS__MMColParam2 = "0"
> If (Request.Form("EndDate") <> "") Then
> TESTTHIS__MMColParam2 = Request.Form("EndDate")
> End If
> %>
> <%
> Dim TESTTHIS
> Dim TESTTHIS_numRows
> Set TESTTHIS = Server.CreateObject("ADODB.Recordset")
> TESTTHIS.ActiveConnection = MM_CSSMetricsCONN_STRING
> TESTTHIS.Source = "SELECT Team, SumofInAdherenceSecsQty /
> (SumofInAdherenceSecsQty + SumofOutOfAdherenceSecsQty) AS Adherence FROM
> (SELECT TOP 1000 Team, SUM(OutOfAdherenceSecsQty) AS
> SumofOutOfAdherenceSecsQty, SUM(InAdherenceSecsQty) AS
> SumofInAdherenceSecsQty FROM bo.[National Call Stats] WHERE (Site =
> 'Dallas') AND ([Date] >='" + Replace(TESTTHIS__MMColParam1, "'", "''") +
> "')
> AND ([Date] <= '" + Replace(TESTTHIS__MMColParam2, "'", "''") + "')
> GROUP BY Team, Site ORDER BY Team, Site)
> DERIVEDTBL"
> TESTTHIS.CursorType = 0
> TESTTHIS.CursorLocation = 2
> TESTTHIS.LockType = 1
> TESTTHIS.Open()
> TESTTHIS_numRows = 0
> %>
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200606/1|||Also keep in mind that if something is a valid date it still might not
be able to be converted into smalldatetime (for example dates before
19000101 and after 20790606)
Denis the SQL Menace
http://sqlservercode.blogspot.com/
Chamark via webservertalk.com wrote:
> I am attempting to pass a string variable from a form to an SQL statement
and
> I get this error "Syntax error converting character string to smalldatetim
e
> data type"
> Your assistance is appreciated in advance....
> Here is my code
> <%
> Dim TESTTHIS__MMColParam1
> TESTTHIS__MMColParam1 = "0"
> If (Request.Form("StartDate") <> "") Then
> TESTTHIS__MMColParam1 = Request.Form("StartDate")
> End If
> %>
> <%
> Dim TESTTHIS__MMColParam2
> TESTTHIS__MMColParam2 = "0"
> If (Request.Form("EndDate") <> "") Then
> TESTTHIS__MMColParam2 = Request.Form("EndDate")
> End If
> %>
> <%
> Dim TESTTHIS
> Dim TESTTHIS_numRows
> Set TESTTHIS = Server.CreateObject("ADODB.Recordset")
> TESTTHIS.ActiveConnection = MM_CSSMetricsCONN_STRING
> TESTTHIS.Source = "SELECT Team, SumofInAdherenceSecsQty /
> (SumofInAdherenceSecsQty + SumofOutOfAdherenceSecsQty) AS Adherence FROM
> (SELECT TOP 1000 Team, SUM(OutOfAdherenceSecsQty) AS
> SumofOutOfAdherenceSecsQty, SUM(InAdherenceSecsQty) AS
> SumofInAdherenceSecsQty FROM bo.[National Call Stats] WHERE (Site =
> 'Dallas') AND ([Date] >='" + Replace(TESTTHIS__MMColParam1, "'", "''") + "')
> AND ([Date] <= '" + Replace(TESTTHIS__MMColParam2, "'", "''") + "')
> GROUP BY Team, Site ORDER BY Team, Site) DERIVEDTB
L"
> TESTTHIS.CursorType = 0
> TESTTHIS.CursorLocation = 2
> TESTTHIS.LockType = 1
> TESTTHIS.Open()
> TESTTHIS_numRows = 0
> %>
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200606/1|||I am able to see that it is getting to the server because I get a return of
the column headers but I am not getting any data. When I hard code the dates
in the query works fine. I am sending for example 8/1/2005 from the form. If
I take out the two / and send 812005 I get an overflow error?
Dan Guzman wrote:
>Run a Profiler trace to see the actual select statement being sent to SQL
>Sever. Also, consider using parameters instead of concatenating values.
>Parameters are more secure and you don't have to double-up embedded quotes.
>
>[quoted text clipped - 43 lines]
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200606/1|||On Wed, 07 Jun 2006 20:12:46 GMT, "Chamark via webservertalk.com"
<u21870@.uwe> wrote:

>I am able to see that it is getting to the server because I get a return of
>the column headers but I am not getting any data. When I hard code the date
s
>in the query works fine. I am sending for example 8/1/2005 from the form. I
f
>I take out the two / and send 812005 I get an overflow error?
You will get an error, as '812005' does not convert to datetime:
select convert(datetime,'812005')
Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in
an out-of-range datetime value.
The most reliable format for a date string is YYYYMMDD:
select convert(datetime,'20050801')
--
2005-08-01 00:00:00.000
Roy Harvey
Beacon Falls, CTsql

Help comparing 01234 with 1234 (with complications)

I have to join on two fields, both are of type char(8), but one is entered by the application so it maintains leading 0's while the other is hand entered, so there are no leading 0's (and we're never going to get our users to ALWAYS enter leading 0's).

So, since they're numbers anyway, I figured I would just convert them both to integers and join on that as in:

...CAST(ord_no1 as integer) = CAST(ord_no2 as integer)

Which works :), BUT...

Since the ord_no field is actually a char(8) field, users can enter stuff other than numbers in it. Is there a way that I can use CAST as in the above, but let it ignore values that can not be converted? If it's not all numberals, it won't match anyway so I don't need to worry about them, however, when joining on that field, it has to look at all the records and perform the calculation and it's failing on a handful of them.

Any help would be greatly appreciated, thank you.Did we get past 1st normal form?

:D

SELECT ord_no1, ord_no2
FROM myTable99
WHERE ISNUMERIC(ord_no1) = 1 AND ISNUMERIC(ord_no2) = 1|||How can I either:

1. Convert to Integer, but gracefully ignore values that don't convert

OR

2. Convert to something like varchar or something that will let me join on '01234' and '1234 '

Thank you.|||USE Northwind
GO

SET NOCOUNT OFF
CREATE TABLE myTable99(ord_no1 char(8), ord_no2 char(8))
GO

INSERT INTO myTable99(ord_no1, ord_no2)
SELECT '01234','1234' UNION ALL
SELECT 'X1234','1234' UNION ALL
SELECT '6789','6789' UNION ALL
SELECT 'Brett','Brett'

SELECT *
FROM ( SELECT *
FROM myTable99
WHERE ISNUMERIC(ord_no1) = 1 AND ISNUMERIC(ord_no2) = 1) AS XXX
WHERE CONVERT(int,ord_no1) = CONVERT(int,ord_no2)
UNION ALL
SELECT *
FROM myTable99
WHERE (ISNUMERIC(ord_no1) = 0 OR ISNUMERIC(ord_no2) = 0)
AND ord_no1 = ord_no2
GO

SET NOCOUNT OFF
DROP TABLE myTable99
GO|||THANK YOU!!!!

I can't believe I'm doing this because I have a lot of issues with my accounting systems DB design, but it is in normal form.

When performing a warehouse transfer, a inventory issuance is entered for the warehouse the product is shipping from with ord_no = 1000 and doc_ord_no = 1000.

Then, an inventory receipt is entered for the warehouse the product is shipping to with ord_no = 1001 and doc_ord_no = 1000 which relates that receipt with the issuance.

I am trying to create a transfers report so I can list all the issuances and then do a join between ord_no and doc_ord_no (limiting the second table to only receipts) to show which transfers have arrived and which are in transit.

Thank you again for your help.