Datasource Reader - Name for output column is blank.

Hi,

I have a problem using the odbc datasource reader to execute a sql command on a progress database. My query is something like:-

select max(id), sum(amount) from my_table

OR

select a, b, c, recid(my_table) from my_table

which produces external columns and output columns with no name. The progress sql doesn't support using aliases on column names and setting validateexternalmetadata to false and manually naming the input and output parameters in the 'Advanced Editor' doesn't seem to work either. I either get the error 'The name for output column "" is blank and columns can not be blank' or if I add my own column names in the input and output parameters it fails in the pre-execute phase saying it can't find a column in the datasource with name 'myalias'

I can get around the aggregate functions by transfering all the data and doing the aggregate on the local server but I also need to call functions such as recid() which I can't work around. SQL2000 DTS ignored these things and matched as best it could where SQL 2005 IS seems overly strict.

Has anyone encountered similar problems and does anyone have any ideas? I'm currently at a loss :(

Hi,

I don't know about 'Progress'. But just in a curiosity I am asking this. If you can't have a column alias how will you create a view using the statements that you have mentioned above. If there is any workaround there, u better try the same thing.

|||

At a stretch, it should be possible to create a script source which would execute this query and assign the results to columns in the data flow.

another possibility may be to use an Execute SQL task - if you're not returning too many rows.

However, are you sure column aliases are not supported? The Progress online documentation appears to suggest that it may be. there may be a way in progress to make this happen.

Donald

Dataset - Command type question

Hi to all
I need to use a set of stored procedures as source of my report datasets, my
problem is that I have several reports already created some of them using
StoredProcedure as command type, and others using Text and using "EXEC
MyStoreProcedure @.Param1,@.Param2" as the Query String , it seems to work in
the same way, change one of the sets of reports can cost some resources of
my team.
My question is if no exist differences or I can have problems with this
second type of datasets?
ThanksFor "SQL Server" connections they are for all practical purposes identical.
--
Thanks.
Donovan R. Smith
Software Test Lead
This posting is provided "AS IS" with no warranties, and confers no rights.
"Mauricio Cadima" <mcadima@.jalasoft.com> wrote in message
news:#XmzhXedEHA.2544@.TK2MSFTNGP10.phx.gbl...
> Hi to all
> I need to use a set of stored procedures as source of my report datasets, my
> problem is that I have several reports already created some of them using
> StoredProcedure as command type, and others using Text and using "EXEC
> MyStoreProcedure @.Param1,@.Param2" as the Query String , it seems to work in
> the same way, change one of the sets of reports can cost some resources of
> my team.
> My question is if no exist differences or I can have problems with this
> second type of datasets?
> Thanks
>sql

DataRow syntax

command.CommandText = "SELECT UserName from Users WHERE UserID = " = userID

Executing this command returns one table with one column with one row. What is the syntax for getting that value into a variable? I can get the information into a dataSet but I can't get it out. Should I be using a dataSet for this operation?

The rest of the code so far:

SqlDataAdapter dataAdapter =newSqlDataAdapter();

dataAdapter.SelectCommand = command;

dataAdapter.TableMappings.Add("Table","Users");

dataSet =newDataSet();

dataAdapter.Fill(dataSet);

Using that code, your data would be in dataSet.tables[0].rows[0][0].

If that's always just returning one value, you might want to look into using ExecuteScalar instead of the adapter and dataset.

|||

You can just use executeScalar method of SQl command below is example from VB.Net help for scalar modified a little:

Public Function AddProductCategory( _ ByVal UserID As Integer, ByVal connString As String) As Integer Dim Username As string = "" Dim sql As String = "SELECT UserName from Users WHERE UserID = @.USERID" Using conn As New SqlConnection(connString) Dim cmd As New SqlCommand(sql, conn) cmd.Parameters.Add("@.USERID", SqlDbType.Int) cmd.Parameters("@.USERID").Value = newName Try conn.Open() userName = Convert.ToInt32(cmd.ExecuteScalar()) Catch ex As Exception Console.WriteLine(ex.Message) End Try End Using Return newProdIDEnd Function

Thanks

|||

Got it working ... thank you for your help!

DataGridViewComboBoxColumn - DROPDOWN STYLE

Dear Sir,

can u tell what command is available for setting the drop down style of a DataGridViewComboBoxColumn. By default it is coming as dropdown list, but i want to make it only drop down, so that i can make the combo blank and populate the list at my will, which we can do with other combo box controls.

with regards,

wilfi

It isn't T-SQL task. You could configure this setting by DataGridView properties, not bu Transact-SQL commands