ORDER BY not working with ADO or OLE

I am inserting rows into an Excel file and the ORDER BY is coming out wrong.

When I run the select I get priority 1,2,3,4, ...10, 11, 12, ... as I should.

But in the excel file the rows come out 1,10,11,12,13 ..., 2, 20, 21, ...

SET @.sql = ' INSERT INTO OpenRowset(''Microsoft.Jet.OLEDB.4.0'',''Excel 5.0;Database='+@.finalFile+';HDR=YES'',

''SELECT [ID],[Priority],[Comments] FROM [Sheet1$]'')

SELECT ID,priority,comments FROM OurTable WHERE orgId='+@.orgId+' ORDER BY priority,ID'

EXECUTE (@.sql)

Please help!! Thank you!

The order by is ordering them in string order. The type coming in must be a string type. Try converting them to integer to get the right order (e.g. ORDER BY convert(integer, priority.ID). That should clear things up.

Hope that helps,

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 to Sql Server conversion

Something went a bit wrong. Converted Oracle 10g to Sql Server 2000.
Now, in the schema there is a column that allows nulls.
In Oracle if you look at the column, you see NULL.
However if I look at the same created in Sql Server it actually does not have a <NULL> value there.
When the conversion was done however, all of these NULLs from Oracle came across into Sql Server with the actual value of <NULL>.
This causes the problem that these objects are now no longer displayed within my application. They show up fine using Oracle, and if I use Sql Server from the start it is fine -- the column is just blank, not acutally NULL. I can force the <NULL> value by doing the ctrl+0 on the field, and that breaks it as well.

The column has to allow nulls, but the actual value cannot be <NULL> (in Sql Server). Any suggestions on getting rid of the NULL - I could do an update, but it actually just has to be blank rather than having a value. I tried an update to set it to ' ' but that didn't really work - here was my statement:
update [table] set [columnname] = '' where [columnname] = '<NULL>'

Any other suggestions besides try again? But if it has to be 'try the conversion again' then that's the answer.
Thanks muchupdate [table] set [columnname] = NULL where [columnname] = '<NULL>'

Oracle Procedure with OUT Parameters

I get this error message when I try to create a DataSet for an Oracle
procedure that has an OUT Parameter.
PLS-00306: wrong number or types of parameters in call to 'procedure name'
The Error happens when I click on Refresh Fields.
I can execute procedures with a REFCURSOR OUT Parameter just fine. I only
get this message when the procedure has other out types like DATE or CHAR.
Any help would be greatly appreciated.
FabianOnly out ref cursors are supported. Please follow the guidelines in the
following article on MSDN (scroll down to the section where it talks about
"Oracle REF CURSORs") on how to design the Oracle stored procedure:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpcontheadonetdatareader.asp
To use a stored procedure with regular out parameters, you should either
remove the parameter (if it is possible) or write a little wrapper around
the original stored procedure which checks the result of the out parameter
and just returns the out ref cursor but no out parameter.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Fabian" <Fabian@.discussions.microsoft.com> wrote in message
news:8BE134E6-FE00-48CB-B64A-9EF81FC43BAE@.microsoft.com...
> I get this error message when I try to create a DataSet for an Oracle
> procedure that has an OUT Parameter.
> PLS-00306: wrong number or types of parameters in call to 'procedure name'
> The Error happens when I click on Refresh Fields.
> I can execute procedures with a REFCURSOR OUT Parameter just fine. I only
> get this message when the procedure has other out types like DATE or CHAR.
> Any help would be greatly appreciated.
> Fabian|||Thank you Robert. I wrote a wrapper.
Fabian
"Robert Bruckner [MSFT]" wrote:
> Only out ref cursors are supported. Please follow the guidelines in the
> following article on MSDN (scroll down to the section where it talks about
> "Oracle REF CURSORs") on how to design the Oracle stored procedure:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpcontheadonetdatareader.asp
> To use a stored procedure with regular out parameters, you should either
> remove the parameter (if it is possible) or write a little wrapper around
> the original stored procedure which checks the result of the out parameter
> and just returns the out ref cursor but no out parameter.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Fabian" <Fabian@.discussions.microsoft.com> wrote in message
> news:8BE134E6-FE00-48CB-B64A-9EF81FC43BAE@.microsoft.com...
> > I get this error message when I try to create a DataSet for an Oracle
> > procedure that has an OUT Parameter.
> >
> > PLS-00306: wrong number or types of parameters in call to 'procedure name'
> >
> > The Error happens when I click on Refresh Fields.
> >
> > I can execute procedures with a REFCURSOR OUT Parameter just fine. I only
> > get this message when the procedure has other out types like DATE or CHAR.
> >
> > Any help would be greatly appreciated.
> >
> > Fabian
>
>

Oracle Lite 10g supports .Net Stored Procedures

Hello Team..
No I am not posting in the wrong forum, I meant to check if the MS pros have read/heard of anything about Oracle Lite 10R2 and its support for .Net Stored Procedure..
Oracle et al docs show that it only support java stored proc (on Windows 32 not CE), but do not show if it does not support .NET stored proc. the docs show that Lite 10g R2 does NOT support PL/SQL stored procs....

Can anyone please help if you have any info or reference?

Thanks

PS:
I already posted the question in 3 oracle forums...

I just recently ran into this and found out that only Java stored procs are supported with Oracle Lite 10gR2. Oracle Database 10gR2 on Windows supports .NET stored procs as a call to an external process (i.e. there is no .NET CLR in the database as with SQL Server 2005) as far as I know.

Damir

Oracle Lite 10g supports .Net Stored Procedures

Hello Team..
No I am not posting in the wrong forum, I meant to check if the MS pros have read/heard of anything about Oracle Lite 10R2 and its support for .Net Stored Procedure..
Oracle et al docs show that it only support java stored proc (on Windows 32 not CE), but do not show if it does not support .NET stored proc. the docs show that Lite 10g R2 does NOT support PL/SQL stored procs....

Can anyone please help if you have any info or reference?

Thanks

PS:
I already posted the question in 3 oracle forums...

I just recently ran into this and found out that only Java stored procs are supported with Oracle Lite 10gR2. Oracle Database 10gR2 on Windows supports .NET stored procs as a call to an external process (i.e. there is no .NET CLR in the database as with SQL Server 2005) as far as I know.

Damir