Order By ignoring the hyphen character

Hi,
I'm using the following SQL script to return a list of part number and the order is not what I expect. Perhaps this is a collation problem but I have no idea where to look to modify that.

Thanks in advance, John
select part from transactions T where (T.transdate between '20070701' and '20070705') and
(T.transtype = 'ISSU' or T.transtype = 'RTRN') order by part

Here is the beginning of the Transactions table create script

CREATE TABLE [Transactions] (
[RecNo] [int] IDENTITY (1,1) NOT NULL,
[Part] [nvarchar] (30) NOT NULL ,
[TransDate] [nvarchar] (8) NOT NULL ,
[TransType] [nvarchar] (4) NOT NULL ,
[FromLoc] [nvarchar] (10) ,

The 'Part' column is an alphanumeric field. The problem I am having is that the
Order By seems to ignore the hyphen character '-' when the returned rows
are ordered by the Part (which can contain hyphens in any column).

Here is an example of what I get.

130909N9
130909N9
130909N9
1-480698-0 * These two should not be here
1-480699-0 *
15-423
164-07700
164-07700
164-07700
1683
I was expecting this ( and I get this in and older database ).

068-03000
068-03000
06A19956
074-03200
077-367-0
08DU08
1-480698-0 * These should be here eariler in the data
1-480699-0
100-364072
100-364072

It is by design. http://support.microsoft.com/kb/305704

What about using another column for sorting?

SELECT part, replace(part,'-','00000') as partForSortingHyphen

FROM Transactions

ORDER BY partForSortingHyphen ASC

|||Thanks,
Your suggusted workaround did the trick.
John
sql

Order By ignoring the hyphen character

Hi,
I'm using the following SQL script to return a list of part number and the order is not what I expect. Perhaps this is a collation problem but I have no idea where to look to modify that.

Thanks in advance, John
select part from transactions T where (T.transdate between '20070701' and '20070705') and
(T.transtype = 'ISSU' or T.transtype = 'RTRN') order by part

Here is the beginning of the Transactions table create script

CREATE TABLE [Transactions] (
[RecNo] [int] IDENTITY (1,1) NOT NULL,
[Part] [nvarchar] (30) NOT NULL ,
[TransDate] [nvarchar] (8) NOT NULL ,
[TransType] [nvarchar] (4) NOT NULL ,
[FromLoc] [nvarchar] (10) ,

The 'Part' column is an alphanumeric field. The problem I am having is that the
Order By seems to ignore the hyphen character '-' when the returned rows
are ordered by the Part (which can contain hyphens in any column).

Here is an example of what I get.

130909N9
130909N9
130909N9
1-480698-0 * These two should not be here
1-480699-0 *
15-423
164-07700
164-07700
164-07700
1683
I was expecting this ( and I get this in and older database ).

068-03000
068-03000
06A19956
074-03200
077-367-0
08DU08
1-480698-0 * These should be here eariler in the data
1-480699-0
100-364072
100-364072

It is by design. http://support.microsoft.com/kb/305704

What about using another column for sorting?

SELECT part, replace(part,'-','00000') as partForSortingHyphen

FROM Transactions

ORDER BY partForSortingHyphen ASC

|||Thanks,
Your suggusted workaround did the trick.
John

order by cluase cause wrong results to be returned.

I have the follow table.

/****** Object: Table [dbo].[deletethisTempOut] Script Date: 09/10/2007 09:20:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[deletethisTempOut](
[ThemeName] [varchar](60) NULL,
[intLocationCount] [int] NULL,
[dblRepValueA] [float] NULL,
[dblRepValueB] [float] NULL,
[dblRepValueC] [float] NULL,
[dblRepValueD] [float] NULL,
[dblTotalRepValue] [float] NULL,
[dblLimit1] [float] NULL,
[dblLimit2] [float] NULL,
[dblLimit3] [float] NULL,
[dblLimit4] [float] NULL,
[dblTotalLimit] [float] NULL,
[fltEmployeecount] [float] NULL,
[intAreaLevel1] [tinyint] NOT NULL,
[strFullName] [varchar](13) NOT NULL,
[strAreaLevel2] [varchar](20) NOT NULL,
[strAreaLevel3] [varchar](20) NOT NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

If I use the following SQL:

SELECT ThemeName, intLocationCount, dblRepValueA, dblRepValueB, dblRepValueC, dblRepValueD, dblTotalRepValue, dblLimit1, dblLimit2, dblLimit3,
dblLimit4, dblTotalLimit, fltEmployeecount, intAreaLevel1, strFullName, strAreaLevel2, strAreaLevel3
FROM deletethisTempOut
ORDER BY strAreaLevel2, strAreaLevel3

GET Following correct results:

Adair 284 899989594 0 574857716 190479902 1665327212 0 0 0 0 1665327212 0 1 United States 1 1

IF I use the following SQL I get the wrong results:

SELECT ThemeName, intLocationCount, dblRepValueA, dblRepValueB, dblRepValueC, dblRepValueD, dblTotalRepValue, dblLimit1, dblLimit2, dblLimit3,
dblLimit4, dblTotalLimit, fltEmployeecount, intAreaLevel1, strFullName, strAreaLevel2, strAreaLevel3
FROM deletethisTempOut
ORDER BY ThemeName

WRONG results:

Adair 74 81733110 0 49616018 24671651 156020779 50510500 0 0 0 203870779 0 1 United States 50 1 Adair 437 1468698657 0 495479839 353202768 2317381264 12984266 0 0 0 2315676030 0 1 United States 25 1 Adair 1813 20309722045 0 6597005374 4253819645 31160547064 43636703 0 0 0 31135010742 0 1 United States 11 1 Adair 606 439581417 0 331746662 132240332 903568411 0 0 0 0 903568411 0 1 United States 45 1 Adair 236 350256381 0 524269553 504973831 1379499765 4080368 0 0 0 1380473415 0 1 United States 23 1

etc.....

In what way do you think the results are wrong? Do you mean that the Themename values are not ordered correctly?

If so, then remember that when you have a column which has duplicate entries, there is nothing to tell sql server to output them in any particular order. You are just ordering your records based upon the value of that column ony. If you want duplicate entries to be ordered by intLocationCount, you'll need to specify that in your order clause too.

Eg ORDER BY ThemeName, intLocationCount DESC

HTH!

|||

when the themename is used I get multiple rows when the temp table contains only one row for each themename.

themename is based on the level2 and level3 values and there is only one row for every level2 and level3 combination.

in this case level2 = state code and level3 = county code. Themename should be county name.

If you can tell me how to send you the under lying table I will.

|||

This is not possible, the order by clause does not affect the number of rows returned merely their ordering.

More records must have been inserted into the underlying table after the initial query was run and before the subsequent query was run.

|||

This a a very repeatable problem can I send you the underlying table? I dump the temp table to a perm table to check the results and I got the same behovior eventhough when you a select * from the table you only get one row from the table for each themename.

I agree this should never happen especial since its a single table no join.....

|||

Since you said there is only one row, I reverse-engineered it and created the following:

Code Snippet

USE tempdb;

GO

/****** Object: Table [dbo].[deletethisTempOut] Script Date: 09/10/2007 09:20:12 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[deletethisTempOut](

[ThemeName] [varchar](60) NULL,

[intLocationCount] [int] NULL,

[dblRepValueA] [float] NULL,

[dblRepValueB] [float] NULL,

[dblRepValueC] [float] NULL,

[dblRepValueD] [float] NULL,

[dblTotalRepValue] [float] NULL,

[dblLimit1] [float] NULL,

[dblLimit2] [float] NULL,

[dblLimit3] [float] NULL,

[dblLimit4] [float] NULL,

[dblTotalLimit] [float] NULL,

[fltEmployeecount] [float] NULL,

[intAreaLevel1] [tinyint] NOT NULL,

[strFullName] [varchar](13) NOT NULL,

[strAreaLevel2] [varchar](20) NOT NULL,

[strAreaLevel3] [varchar](20) NOT NULL

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

Go

INSERT deletethisTempOut (ThemeName, intLocationCount, dblRepValueA, dblRepValueB, dblRepValueC, dblRepValueD, dblTotalRepValue, dblLimit1, dblLimit2, dblLimit3,

dblLimit4, dblTotalLimit, fltEmployeecount, intAreaLevel1, strFullName, strAreaLevel2, strAreaLevel3

) VALUES ('Adair',284,899989594,0,574857716,190479902,1665327212,0,0,0,0,1665327212,0,1,'United,States',1,1)

GO

-- Query 1

SELECT ThemeName, intLocationCount, dblRepValueA, dblRepValueB, dblRepValueC, dblRepValueD, dblTotalRepValue, dblLimit1, dblLimit2, dblLimit3,

dblLimit4, dblTotalLimit, fltEmployeecount, intAreaLevel1, strFullName, strAreaLevel2, strAreaLevel3

FROM deletethisTempOut

ORDER BY strAreaLevel2, strAreaLevel3

GO

-- Query 2

SELECT ThemeName, intLocationCount, dblRepValueA, dblRepValueB, dblRepValueC, dblRepValueD, dblTotalRepValue, dblLimit1, dblLimit2, dblLimit3,

dblLimit4, dblTotalLimit, fltEmployeecount, intAreaLevel1, strFullName, strAreaLevel2, strAreaLevel3

FROM deletethisTempOut

ORDER BY ThemeName

GO

I couldn't repro the problem you are having.

Is this similar to what you have? Are you missing out a WHERE clause in one of the queries?

|||

Caveman,

You'll have to provide more information. You have mentioned a temp table, but the code you posted does not refer to a temp table. Later you said something about a permanent table and a temp table, again without being specific about anything.

If I had to guess (which I do in this case), my suspicion is that perhaps you are seeing problems SELECTing from a view where the view definition contains things like UNION, TOP, or ORDER BY. It is possible, however, that you are encountering a bug.

Can you run SELECT @.@.VERSION and either post the result or find out whether you have installed the latest service pack for the version of SQL Server you're running?

It probably won't help to send anyone the data in the underlying table. What we need to see to help is the *exact* queries that you have problems with, and all underlying definitions. For example, if the problem query involves a temporary table, we need to see the code that inserts data into the temporary table. If there is a view somewhere, we need to see its definition.

Steve Kass

Drew University

http://www.stevekass.com

order by cluase cause wrong results to be returned.

I have the follow table.

/****** Object: Table [dbo].[deletethisTempOut] Script Date: 09/10/2007 09:20:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[deletethisTempOut](
[ThemeName] [varchar](60) NULL,
[intLocationCount] [int] NULL,
[dblRepValueA] [float] NULL,
[dblRepValueB] [float] NULL,
[dblRepValueC] [float] NULL,
[dblRepValueD] [float] NULL,
[dblTotalRepValue] [float] NULL,
[dblLimit1] [float] NULL,
[dblLimit2] [float] NULL,
[dblLimit3] [float] NULL,
[dblLimit4] [float] NULL,
[dblTotalLimit] [float] NULL,
[fltEmployeecount] [float] NULL,
[intAreaLevel1] [tinyint] NOT NULL,
[strFullName] [varchar](13) NOT NULL,
[strAreaLevel2] [varchar](20) NOT NULL,
[strAreaLevel3] [varchar](20) NOT NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

If I use the following SQL:

SELECT ThemeName, intLocationCount, dblRepValueA, dblRepValueB, dblRepValueC, dblRepValueD, dblTotalRepValue, dblLimit1, dblLimit2, dblLimit3,
dblLimit4, dblTotalLimit, fltEmployeecount, intAreaLevel1, strFullName, strAreaLevel2, strAreaLevel3
FROM deletethisTempOut
ORDER BY strAreaLevel2, strAreaLevel3

GET Following correct results:

Adair 284 899989594 0 574857716 190479902 1665327212 0 0 0 0 1665327212 0 1 United States 1 1

IF I use the following SQL I get the wrong results:

SELECT ThemeName, intLocationCount, dblRepValueA, dblRepValueB, dblRepValueC, dblRepValueD, dblTotalRepValue, dblLimit1, dblLimit2, dblLimit3,
dblLimit4, dblTotalLimit, fltEmployeecount, intAreaLevel1, strFullName, strAreaLevel2, strAreaLevel3
FROM deletethisTempOut
ORDER BY ThemeName

WRONG results:

Adair 74 81733110 0 49616018 24671651 156020779 50510500 0 0 0 203870779 0 1 United States 50 1 Adair 437 1468698657 0 495479839 353202768 2317381264 12984266 0 0 0 2315676030 0 1 United States 25 1 Adair 1813 20309722045 0 6597005374 4253819645 31160547064 43636703 0 0 0 31135010742 0 1 United States 11 1 Adair 606 439581417 0 331746662 132240332 903568411 0 0 0 0 903568411 0 1 United States 45 1 Adair 236 350256381 0 524269553 504973831 1379499765 4080368 0 0 0 1380473415 0 1 United States 23 1

etc.....

In what way do you think the results are wrong? Do you mean that the Themename values are not ordered correctly?

If so, then remember that when you have a column which has duplicate entries, there is nothing to tell sql server to output them in any particular order. You are just ordering your records based upon the value of that column ony. If you want duplicate entries to be ordered by intLocationCount, you'll need to specify that in your order clause too.

Eg ORDER BY ThemeName, intLocationCount DESC

HTH!

|||

when the themename is used I get multiple rows when the temp table contains only one row for each themename.

themename is based on the level2 and level3 values and there is only one row for every level2 and level3 combination.

in this case level2 = state code and level3 = county code. Themename should be county name.

If you can tell me how to send you the under lying table I will.

|||

This is not possible, the order by clause does not affect the number of rows returned merely their ordering.

More records must have been inserted into the underlying table after the initial query was run and before the subsequent query was run.

|||

This a a very repeatable problem can I send you the underlying table? I dump the temp table to a perm table to check the results and I got the same behovior eventhough when you a select * from the table you only get one row from the table for each themename.

I agree this should never happen especial since its a single table no join.....

|||

Since you said there is only one row, I reverse-engineered it and created the following:

Code Snippet

USE tempdb;

GO

/****** Object: Table [dbo].[deletethisTempOut] Script Date: 09/10/2007 09:20:12 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[deletethisTempOut](

[ThemeName] [varchar](60) NULL,

[intLocationCount] [int] NULL,

[dblRepValueA] [float] NULL,

[dblRepValueB] [float] NULL,

[dblRepValueC] [float] NULL,

[dblRepValueD] [float] NULL,

[dblTotalRepValue] [float] NULL,

[dblLimit1] [float] NULL,

[dblLimit2] [float] NULL,

[dblLimit3] [float] NULL,

[dblLimit4] [float] NULL,

[dblTotalLimit] [float] NULL,

[fltEmployeecount] [float] NULL,

[intAreaLevel1] [tinyint] NOT NULL,

[strFullName] [varchar](13) NOT NULL,

[strAreaLevel2] [varchar](20) NOT NULL,

[strAreaLevel3] [varchar](20) NOT NULL

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

Go

INSERT deletethisTempOut (ThemeName, intLocationCount, dblRepValueA, dblRepValueB, dblRepValueC, dblRepValueD, dblTotalRepValue, dblLimit1, dblLimit2, dblLimit3,

dblLimit4, dblTotalLimit, fltEmployeecount, intAreaLevel1, strFullName, strAreaLevel2, strAreaLevel3

) VALUES ('Adair',284,899989594,0,574857716,190479902,1665327212,0,0,0,0,1665327212,0,1,'United,States',1,1)

GO

-- Query 1

SELECT ThemeName, intLocationCount, dblRepValueA, dblRepValueB, dblRepValueC, dblRepValueD, dblTotalRepValue, dblLimit1, dblLimit2, dblLimit3,

dblLimit4, dblTotalLimit, fltEmployeecount, intAreaLevel1, strFullName, strAreaLevel2, strAreaLevel3

FROM deletethisTempOut

ORDER BY strAreaLevel2, strAreaLevel3

GO

-- Query 2

SELECT ThemeName, intLocationCount, dblRepValueA, dblRepValueB, dblRepValueC, dblRepValueD, dblTotalRepValue, dblLimit1, dblLimit2, dblLimit3,

dblLimit4, dblTotalLimit, fltEmployeecount, intAreaLevel1, strFullName, strAreaLevel2, strAreaLevel3

FROM deletethisTempOut

ORDER BY ThemeName

GO

I couldn't repro the problem you are having.

Is this similar to what you have? Are you missing out a WHERE clause in one of the queries?

|||

Caveman,

You'll have to provide more information. You have mentioned a temp table, but the code you posted does not refer to a temp table. Later you said something about a permanent table and a temp table, again without being specific about anything.

If I had to guess (which I do in this case), my suspicion is that perhaps you are seeing problems SELECTing from a view where the view definition contains things like UNION, TOP, or ORDER BY. It is possible, however, that you are encountering a bug.

Can you run SELECT @.@.VERSION and either post the result or find out whether you have installed the latest service pack for the version of SQL Server you're running?

It probably won't help to send anyone the data in the underlying table. What we need to see to help is the *exact* queries that you have problems with, and all underlying definitions. For example, if the problem query involves a temporary table, we need to see the code that inserts data into the temporary table. If there is a view somewhere, we need to see its definition.

Steve Kass

Drew University

http://www.stevekass.com

sql

order by cluase cause wrong results to be returned.

I have the follow table.

/****** Object: Table [dbo].[deletethisTempOut] Script Date: 09/10/2007 09:20:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[deletethisTempOut](
[ThemeName] [varchar](60) NULL,
[intLocationCount] [int] NULL,
[dblRepValueA] [float] NULL,
[dblRepValueB] [float] NULL,
[dblRepValueC] [float] NULL,
[dblRepValueD] [float] NULL,
[dblTotalRepValue] [float] NULL,
[dblLimit1] [float] NULL,
[dblLimit2] [float] NULL,
[dblLimit3] [float] NULL,
[dblLimit4] [float] NULL,
[dblTotalLimit] [float] NULL,
[fltEmployeecount] [float] NULL,
[intAreaLevel1] [tinyint] NOT NULL,
[strFullName] [varchar](13) NOT NULL,
[strAreaLevel2] [varchar](20) NOT NULL,
[strAreaLevel3] [varchar](20) NOT NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

If I use the following SQL:

SELECT ThemeName, intLocationCount, dblRepValueA, dblRepValueB, dblRepValueC, dblRepValueD, dblTotalRepValue, dblLimit1, dblLimit2, dblLimit3,
dblLimit4, dblTotalLimit, fltEmployeecount, intAreaLevel1, strFullName, strAreaLevel2, strAreaLevel3
FROM deletethisTempOut
ORDER BY strAreaLevel2, strAreaLevel3

GET Following correct results:

Adair 284 899989594 0 574857716 190479902 1665327212 0 0 0 0 1665327212 0 1 United States 1 1

IF I use the following SQL I get the wrong results:

SELECT ThemeName, intLocationCount, dblRepValueA, dblRepValueB, dblRepValueC, dblRepValueD, dblTotalRepValue, dblLimit1, dblLimit2, dblLimit3,
dblLimit4, dblTotalLimit, fltEmployeecount, intAreaLevel1, strFullName, strAreaLevel2, strAreaLevel3
FROM deletethisTempOut
ORDER BY ThemeName

WRONG results:

Adair 74 81733110 0 49616018 24671651 156020779 50510500 0 0 0 203870779 0 1 United States 50 1 Adair 437 1468698657 0 495479839 353202768 2317381264 12984266 0 0 0 2315676030 0 1 United States 25 1 Adair 1813 20309722045 0 6597005374 4253819645 31160547064 43636703 0 0 0 31135010742 0 1 United States 11 1 Adair 606 439581417 0 331746662 132240332 903568411 0 0 0 0 903568411 0 1 United States 45 1 Adair 236 350256381 0 524269553 504973831 1379499765 4080368 0 0 0 1380473415 0 1 United States 23 1

etc.....

In what way do you think the results are wrong? Do you mean that the Themename values are not ordered correctly?

If so, then remember that when you have a column which has duplicate entries, there is nothing to tell sql server to output them in any particular order. You are just ordering your records based upon the value of that column ony. If you want duplicate entries to be ordered by intLocationCount, you'll need to specify that in your order clause too.

Eg ORDER BY ThemeName, intLocationCount DESC

HTH!

|||

when the themename is used I get multiple rows when the temp table contains only one row for each themename.

themename is based on the level2 and level3 values and there is only one row for every level2 and level3 combination.

in this case level2 = state code and level3 = county code. Themename should be county name.

If you can tell me how to send you the under lying table I will.

|||

This is not possible, the order by clause does not affect the number of rows returned merely their ordering.

More records must have been inserted into the underlying table after the initial query was run and before the subsequent query was run.

|||

This a a very repeatable problem can I send you the underlying table? I dump the temp table to a perm table to check the results and I got the same behovior eventhough when you a select * from the table you only get one row from the table for each themename.

I agree this should never happen especial since its a single table no join.....

|||

Since you said there is only one row, I reverse-engineered it and created the following:

Code Snippet

USE tempdb;

GO

/****** Object: Table [dbo].[deletethisTempOut] Script Date: 09/10/2007 09:20:12 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[deletethisTempOut](

[ThemeName] [varchar](60) NULL,

[intLocationCount] [int] NULL,

[dblRepValueA] [float] NULL,

[dblRepValueB] [float] NULL,

[dblRepValueC] [float] NULL,

[dblRepValueD] [float] NULL,

[dblTotalRepValue] [float] NULL,

[dblLimit1] [float] NULL,

[dblLimit2] [float] NULL,

[dblLimit3] [float] NULL,

[dblLimit4] [float] NULL,

[dblTotalLimit] [float] NULL,

[fltEmployeecount] [float] NULL,

[intAreaLevel1] [tinyint] NOT NULL,

[strFullName] [varchar](13) NOT NULL,

[strAreaLevel2] [varchar](20) NOT NULL,

[strAreaLevel3] [varchar](20) NOT NULL

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

Go

INSERT deletethisTempOut (ThemeName, intLocationCount, dblRepValueA, dblRepValueB, dblRepValueC, dblRepValueD, dblTotalRepValue, dblLimit1, dblLimit2, dblLimit3,

dblLimit4, dblTotalLimit, fltEmployeecount, intAreaLevel1, strFullName, strAreaLevel2, strAreaLevel3

) VALUES ('Adair',284,899989594,0,574857716,190479902,1665327212,0,0,0,0,1665327212,0,1,'United,States',1,1)

GO

-- Query 1

SELECT ThemeName, intLocationCount, dblRepValueA, dblRepValueB, dblRepValueC, dblRepValueD, dblTotalRepValue, dblLimit1, dblLimit2, dblLimit3,

dblLimit4, dblTotalLimit, fltEmployeecount, intAreaLevel1, strFullName, strAreaLevel2, strAreaLevel3

FROM deletethisTempOut

ORDER BY strAreaLevel2, strAreaLevel3

GO

-- Query 2

SELECT ThemeName, intLocationCount, dblRepValueA, dblRepValueB, dblRepValueC, dblRepValueD, dblTotalRepValue, dblLimit1, dblLimit2, dblLimit3,

dblLimit4, dblTotalLimit, fltEmployeecount, intAreaLevel1, strFullName, strAreaLevel2, strAreaLevel3

FROM deletethisTempOut

ORDER BY ThemeName

GO

I couldn't repro the problem you are having.

Is this similar to what you have? Are you missing out a WHERE clause in one of the queries?

|||

Caveman,

You'll have to provide more information. You have mentioned a temp table, but the code you posted does not refer to a temp table. Later you said something about a permanent table and a temp table, again without being specific about anything.

If I had to guess (which I do in this case), my suspicion is that perhaps you are seeing problems SELECTing from a view where the view definition contains things like UNION, TOP, or ORDER BY. It is possible, however, that you are encountering a bug.

Can you run SELECT @.@.VERSION and either post the result or find out whether you have installed the latest service pack for the version of SQL Server you're running?

It probably won't help to send anyone the data in the underlying table. What we need to see to help is the *exact* queries that you have problems with, and all underlying definitions. For example, if the problem query involves a temporary table, we need to see the code that inserts data into the temporary table. If there is a view somewhere, we need to see its definition.

Steve Kass

Drew University

http://www.stevekass.com

oracle oledb provider not registered in local machine

Hi,

I am trying to establish a connection to an Oracle database using the following code in a script task:

Dim oOleDbConnection As OleDbConnection
Dim sConnString As String = _
"Provider=OraOLEDB.Oracle;" & _
"Data Source=DBxxx;" & _
"User ID=Userxxx;" & _
"Password=Passxxx"
oOleDbConnection = New OleDb.OleDbConnection(sConnString)
oOleDbConnection.Open()

When I execute the script task, I receive the following error:
The 'OraOLEDB.Oracle' provider is not registered on the local machine.
Am using the correct provider?

I do not know how to resolve the said error.
Here are some facts:
Oracle 8i is installed.
Tnsnames.ora is updated.
I have successfully connected to Oracle SQL *Plus to test the above credentials.

Please help.
Thanks.

Do you have the Oracle OLE DB driver installed?

http://www.oracle.com/technology/software/tech/windows/ole_db/index.html|||

Shouldn't this be part of SQL Server 2005 standard installation?

I tried creating a new OLEDB connection and found in the drop down: Native OLE DB\Microsoft OLE DB Provider for Oracle.
I checked its full properties and saw provider specified: MSDAORA.1
Does this mean that the provider for Oracle is already installed?
I already tried MSDAORA.1 in my code but the same error appears.

|||

r214acc wrote:

Shouldn't this be part of SQL Server 2005 standard installation?

I tried creating a new OLEDB connection and found in the drop down: Native OLE DB\Microsoft OLE DB Provider for Oracle.
I checked its full properties and saw provider specified: MSDAORA.1
Does this mean that the provider for Oracle is already installed?
I already tried MSDAORA.1 in my code but the same error appears.

The Microsoft OLE DB Provider for Oracle is not the same as the Oracle OLE DB Provider. The Oracle OLE DB Provider is published by Oracle, not Microsoft, and isn't part of the SQL Server 2005 standard installation. I know this because I'm running SQL Server Standard on one of my machines.|||So, like I said, try downloading the Oracle OLE DB driver, and then try your code.|||

Hi Duane,

You are right. However, I tried both MS OLE DB Provider for Oracle and Oracle OLE DB Provider but the error still persists.
Is it possible that Oracle 8i (on the server running SSIS) does not have the components to be used by the SQL Server 2005? or can it connect to a Oracle 9i 64-bit?

Please help me find the answer. Thanks.

|||

r214acc wrote:

Hi Duane,

You are right. However, I tried both MS OLE DB Provider for Oracle and Oracle OLE DB Provider but the error still persists.
Is it possible that Oracle 8i (on the server running SSIS) does not have the components to be used by the SQL Server 2005? or can it connect to a Oracle 9i 64-bit?

Please help me find the answer. Thanks.

Sorry, I don't have an answer for your question. However, I have a question for you. Are you using the 64 bit version of SQL Server 2005?|||

I am using 32 bit version of SQL Server 2005.
Is this the cause of the problem?

|||

There are some issues with Oracle drivers on 64 bit, hence Duane asked.

You say Oracle and SQL with SSIS are installed on the same server. That is fine, but are you really working on the server itself or a desktop?

|||

Hi,

I am working on the server itself.
I found something in the forum that may be related to the problem:
http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=449593&SiteID=17
It says that there is a bug on parsing folder names with parenthesis which happens when a 64-bit OS installs 32-bit programs.
Since I am running a 32-bit SSIS. This may be the problem.

Is there a fix here?
Thanks.

Oracle error through DTS

Hi all,
I'm hoping someone can help me out with an issue that I'm having. I have a script that runs in Oracle just fine, but because of the permissions that I have I cannot create a job in Oracle to execute the package so I created a job in SQL server to execute
the SQL.
When I use the execute SQL task and execute the package I get the following error:
ORA-01861: literal does not match format string.
I'm using an OLE DB connection to an Oracle 8i box. I can literally take the code in the execute sql task and paste it into SQL Navigator and execute there and it works fine.
Any help provided would be greatly appreciated.
Thanks
It's hard to say without see the sql you are running but
trying using OpenQuery to execute the script and see if that
works. Generally you get that Oracle error with dates and
date formats.
-Sue
On Wed, 28 Jul 2004 14:51:02 -0700, "J. Gonzalez"
<JGonzalez@.discussions.microsoft.com> wrote:

>Hi all,
>I'm hoping someone can help me out with an issue that I'm having. I have a script that runs in Oracle just fine, but because of the permissions that I have I cannot create a job in Oracle to execute the package so I created a job in SQL server to execut
e the SQL.
>When I use the execute SQL task and execute the package I get the following error:
>ORA-01861: literal does not match format string.
>I'm using an OLE DB connection to an Oracle 8i box. I can literally take the code in the execute sql task and paste it into SQL Navigator and execute there and it works fine.
>Any help provided would be greatly appreciated.
>Thanks
|||I always forget about openquery. I'll give it a shot.
Thanks!
"J. Gonzalez" wrote:

> Hi all,
> I'm hoping someone can help me out with an issue that I'm having. I have a script that runs in Oracle just fine, but because of the permissions that I have I cannot create a job in Oracle to execute the package so I created a job in SQL server to execu
te the SQL.
> When I use the execute SQL task and execute the package I get the following error:
> ORA-01861: literal does not match format string.
> I'm using an OLE DB connection to an Oracle 8i box. I can literally take the code in the execute sql task and paste it into SQL Navigator and execute there and it works fine.
> Any help provided would be greatly appreciated.
> Thanks
|||I'll chalk this one up as user error on my part. I didn't cast a field correctly (which explained why it didn't error out when there were no records).
J
"J. Gonzalez" wrote:

> Hi all,
> I'm hoping someone can help me out with an issue that I'm having. I have a script that runs in Oracle just fine, but because of the permissions that I have I cannot create a job in Oracle to execute the package so I created a job in SQL server to execu
te the SQL.
> When I use the execute SQL task and execute the package I get the following error:
> ORA-01861: literal does not match format string.
> I'm using an OLE DB connection to an Oracle 8i box. I can literally take the code in the execute sql task and paste it into SQL Navigator and execute there and it works fine.
> Any help provided would be greatly appreciated.
> Thanks
|||We all do it - stare at the sql and convince ourselves it is
correct. Thanks for posting back.
-Sue
On Tue, 3 Aug 2004 08:01:02 -0700, "J. Gonzalez"
<JGonzalez@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>I'll chalk this one up as user error on my part. I didn't cast a field correctly (which explained why it didn't error out when there were no records).
>J
>"J. Gonzalez" wrote:
ute the SQL.[vbcol=seagreen]

Oracle 9i Rel2 to SQL 2000 linked Server Problem..

Using Microsoft SQL OLE DB to create the linked server, whether using
Enterprise Manager or Analyzer script... The linked server appears to be
created successfully, and I can see the database tables, but I cannot see an
y
of the data in the tables.
Anyone have an idea of whay I might be missing? I appreciate any input.
ThanksYou can't view data in linked server tables from Enterprise
Manager. Use Query Analyzer and a query instead. The easiest
way is to use a four part name to reference the table:
select YourColumns
from LinkedServerName.Database.Owner.TableName
-Sue
On Fri, 25 Mar 2005 06:33:03 -0800, bkutsy
<rkutsy@.comcast.net(donotspam)> wrote:

>Using Microsoft SQL OLE DB to create the linked server, whether using
>Enterprise Manager or Analyzer script... The linked server appears to be
>created successfully, and I can see the database tables, but I cannot see a
ny
>of the data in the tables.
>Anyone have an idea of whay I might be missing? I appreciate any input.
>Thanks|||Oops...just noticed the link is to Oracle so use:
LinkedServerName..Schema.TableName
-Sue
On Sun, 27 Mar 2005 20:07:24 -0700, Sue Hoegemeier
<Sue_H@.nomail.please> wrote:
[vbcol=seagreen]
>You can't view data in linked server tables from Enterprise
>Manager. Use Query Analyzer and a query instead. The easiest
>way is to use a four part name to reference the table:
>select YourColumns
>from LinkedServerName.Database.Owner.TableName
>-Sue
>On Fri, 25 Mar 2005 06:33:03 -0800, bkutsy
><rkutsy@.comcast.net(donotspam)> wrote:
>|||Thanks, very much, for responding to this post. Your response confirms our
suspicions. We were able to get the data using the four part name reference
to the table.
thank you again!
Bob.
"Sue Hoegemeier" wrote:

> You can't view data in linked server tables from Enterprise
> Manager. Use Query Analyzer and a query instead. The easiest
> way is to use a four part name to reference the table:
> select YourColumns
> from LinkedServerName.Database.Owner.TableName
> -Sue
> On Fri, 25 Mar 2005 06:33:03 -0800, bkutsy
> <rkutsy@.comcast.net(donotspam)> wrote:
>
>

Oracle 9i Rel2 to SQL 2000 linked Server Problem..

Using Microsoft SQL OLE DB to create the linked server, whether using
Enterprise Manager or Analyzer script... The linked server appears to be
created successfully, and I can see the database tables, but I cannot see any
of the data in the tables.
Anyone have an idea of whay I might be missing? I appreciate any input.
Thanks
You can't view data in linked server tables from Enterprise
Manager. Use Query Analyzer and a query instead. The easiest
way is to use a four part name to reference the table:
select YourColumns
from LinkedServerName.Database.Owner.TableName
-Sue
On Fri, 25 Mar 2005 06:33:03 -0800, bkutsy
<rkutsy@.comcast.net(donotspam)> wrote:

>Using Microsoft SQL OLE DB to create the linked server, whether using
>Enterprise Manager or Analyzer script... The linked server appears to be
>created successfully, and I can see the database tables, but I cannot see any
>of the data in the tables.
>Anyone have an idea of whay I might be missing? I appreciate any input.
>Thanks
|||Oops...just noticed the link is to Oracle so use:
LinkedServerName..Schema.TableName
-Sue
On Sun, 27 Mar 2005 20:07:24 -0700, Sue Hoegemeier
<Sue_H@.nomail.please> wrote:
[vbcol=seagreen]
>You can't view data in linked server tables from Enterprise
>Manager. Use Query Analyzer and a query instead. The easiest
>way is to use a four part name to reference the table:
>select YourColumns
>from LinkedServerName.Database.Owner.TableName
>-Sue
>On Fri, 25 Mar 2005 06:33:03 -0800, bkutsy
><rkutsy@.comcast.net(donotspam)> wrote:
|||Thanks, very much, for responding to this post. Your response confirms our
suspicions. We were able to get the data using the four part name reference
to the table.
thank you again!
Bob.
"Sue Hoegemeier" wrote:

> You can't view data in linked server tables from Enterprise
> Manager. Use Query Analyzer and a query instead. The easiest
> way is to use a four part name to reference the table:
> select YourColumns
> from LinkedServerName.Database.Owner.TableName
> -Sue
> On Fri, 25 Mar 2005 06:33:03 -0800, bkutsy
> <rkutsy@.comcast.net(donotspam)> wrote:
>
>