One last before the week end

How do you translate into T-Sql :
TO_CHAR(Value,'99099')
Thanks for your help.Anobody for help ?|||what is Value, a date? have a look at CAST and CONVERT (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp)|||The value is a number.

TO_CHAR(12345,'99099')

I've had a look at Cast and Convert but I can't find an equivalent style anywhere.
So maybe there's another way to translate it...sql

on Insert, then Update Tigger Help

when a record is inserted into tNTS, I need the columnseq_numupdated with the value of the columnid(it's the PK)

can someone help me with my tigger:

CREATE TRIGGER updateNewSeq
ON dbo.tNTS
FOR INSERT
AS

update tNTS
(
set seq_num =tNTS
)
SELECT
NTS_id
FROM INSERTED

cheersYour update statement looks wierd.

Use as an example:

 update titleauthor
 set title_id = inserted.title_id
 from titleauthor, deleted, inserted
 where deleted.title_id = titleauthor.title_id

|||I tried:

CREATE TRIGGER [updateSeqNum] ON [dbo].[tNTS]
FOR INSERT
AS
update tNTS
set seq_num = NTS_id
from inserted

got error: error 209 ambigious column name: NTS_id

NTS_id is the pk i'm trying to copy to column seq_num after the record is inserted|||You need a where statement for starters so it knows where to actually update.|||

update tNTS

set seq_num=i.NTS_id

from tNTS

JOIN inserted i ON (tNTS.id=i.id)

|||thanks motley, it worked

On demand Subreports

Is it possible to create a subreport, that will show after i clicked a certain value in the main report.
for insance:
Customer sales
Microsoft 200k
Oracle 100k
Clicking on the customer microsoft should start the subreport (on demand for proformance) where all sales details are in.
Please let me know, thanks in advanceYou might want to look into drillthrough reports.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_interactive_v1_38tn.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RShowto/htm/hrs_designer_v1_6wa3.asp
Also check out the samples - the Territory Sales report uses drillthrough:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSAMPLES/htm/rss_samplereports_v1_5u7n.asp
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"M.a.t" <M.a.t@.discussions.microsoft.com> wrote in message
news:6D59E6F9-9EA7-4C7A-9318-B7A30838E3D9@.microsoft.com...
> Is it possible to create a subreport, that will show after i clicked a
certain value in the main report.
> for insance:
> Customer sales
> Microsoft 200k
> Oracle 100k
> Clicking on the customer microsoft should start the subreport (on demand
for proformance) where all sales details are in.
> Please let me know, thanks in advance

OLEDB driver omits SP return value

Hi,

I have a problem with some ATL7/OLEDB code. I'm trying to execute stored procedures from my application. I do get a result set back, but I never get a correct return value back. I've been going through MSDN articles as well as other articles on the web, and I just cannot see what I'm doing wrong!

I have this piece of ATL code:
<pre>
// code generated on den 6 juni 2002, 10:56

[
db_command(L"{ ? = CALL dbo.MyProc(?) }")
]
class CMyProc
{
public:
[ db_column(1, status=m_dwIdStatus, length=m_dwIdLength) ] LONG m_Id;
/* and so on - 7 columns total */

[ db_param(1, DBPARAMIO_OUTPUT) ] LONG m_RETURN_VALUE;
[ db_param(2, DBPARAMIO_INPUT) ] LONG m_id;

void GetRowsetProperties(CDBPropSet* pPropSet)
{
pPropSet->AddProperty(DBPROP_CANFETCHBACKWARDS, true, DBPROPOPTIONS_OPTIONAL);
pPropSet->AddProperty(DBPROP_CANSCROLLBACKWARDS, true, DBPROPOPTIONS_OPTIONAL);
}
};

</pre>

When I use this code I do:
<pre>
CSession dbsession;
CDataSource dbsource;
CDBPropSet dbinit(DBPROPSET_DBINIT);
/* fill dbinit with connection parameters */
/* HRESULTs not checked for brevity*/
dbsource.Open(_T("SQLOLEDB.1"), &dbinit);
dbsession.Open(dbsource);

CMyProc proc;
proc.m_id = 1;
proc.Open(dbsession);

TRACE("Return value is %d\n", proc.m_RETURN_VALUE);

/* Take care of result set - which exists and is valid! */
</pre>

The return value is <tt>-858993460</tt> which is <tt>0xcccccccc</tt>. In Visual C++ this value is used as an "uninitialized" stack value during debug. It seems as if ATL never bothers to update my return value parameters! I even made a little experiment with an extra output parameter - same thing there.

I'm using Visual C++ .NET and the SQL 2000 OLEDB provider (SP2). I've also tried this without attributed programming in both Visual C++ .NET and Visual C++ 6.

Does anybody have a clue what is going on??Is the stored procedure using set nocount on ? If not, put it in the stored procedure.

Good luck.|||Thanks for replying!

I tried with and without nocount and had no success.

I tried something else the other day though; I generated a non-attributed CCommand-class using CMultipleResults as template parameter. Then I could get all out parameters by calling GetNextResult() on the command object.

So my question is now; how do I make the attribute-injector generate a CCommand class for me using CMultipleResults?

OLE DB Source using table name variable

I have a package-level variable [User::viewName], type = string, containing a view name. I want to setup an OLE DB source to use this variable value as the source, so Data access mode = "Table name or view name variable". The Variable name dropdown contains [User:viewName], so I select it. When I click OK to leave the edit dialog I get the error:

The variable User::viewName is required to be of type "VT_BSTR".

Only variables of type String occur in the Variable name dropdown; if I try changing it to a type other than string it doesn't occur in the dropdown. What is VT_BSTR and how can I change the variable type to it?

I've never seen VT_BSTR in my life, that isn't a data type that I know of. Something is awry here - I wonder if it could be package corruption. Does the same happen when you try a differrent string variable?

-Jamie

|||I'm copying and pasting a package in Solution Explorer, then playing with the copy to see what works, then going back to the original. The package this problem is occurring in is a copy; might that be the problem? This seems like such an ordinary thing to do (get the table or view name from a variable) that I'm really surprised it's happening. Are there known problems with copying packages?|||

I don't know of any - but it does sound as though some corruption has occurred somewhere. Can you share the contents of the .dtsx file? (i.e. open it in notepad and copy the contents to here)?

-Jamie

|||

mruniqueid wrote:

I'm copying and pasting a package in Solution Explorer, then playing with the copy to see what works, then going back to the original. The package this problem is occurring in is a copy; might that be the problem? This seems like such an ordinary thing to do (get the table or view name from a variable) that I'm really surprised it's happening. Are there known problems with copying packages?

Can't think it matters, but be sure you generate a new GUID on the copied package. Control-flow background: right click, properties. Select the drop down in the ID field and generate a new GUID.|||

In my original package Data Flow I had an OLE DB Source with Data access mode = "Table or view". When I try to set Data access mode = "Table name or view name variable" in the original package I run into this trouble.

I tried creating a new package from scratch and realized I've got a knowledge gap. If you create an OLE DB Source and set Data access mode = "Table name or view name variable" right from the start, then how do you define the source output columns since there's no table to derive them from? I have several views with the same structure; in my original package I picked one of these as "Name of the table or the view" which of course defined output columns for the source, then tried to change Data access mode to a variable as described above.

Can I start with Data access mode = variable? If I try that I get the error "A destination table name has not been provided". I created an OLE DB Destination and connected it to the OLE DB Source but the error persists. What is the proper sequence of steps and settings to use a variable name for Data Access Mode in an OLE DB Source?

|||

mruniqueid wrote:

In my original package Data Flow I had an OLE DB Source with Data access mode = "Table or view". When I try to set Data access mode = "Table name or view name variable" in the original package I run into this trouble.

I tried creating a new package from scratch and realized I've got a knowledge gap. If you create an OLE DB Source and set Data access mode = "Table name or view name variable" right from the start, then how do you define the source output columns since there's no table to derive them from?

The name of the table needs to be stored in the variable.

mruniqueid wrote:

I have several views with the same structure; in my original package I picked one of these as "Name of the table or the view" which of course defined output columns for the source, then tried to change Data access mode to a variable as described above.

Can I start with Data access mode = variable? If I try that I get the error "A destination table name has not been provided". I created an OLE DB Destination and connected it to the OLE DB Source but the error persists. What is the proper sequence of steps and settings to use a variable name for Data Access Mode in an OLE DB Source?

1. Create the variable of type string

2. Add the table name into teh variable

3. Create your OLE DB Source and select the variable that you have just chosen.

-Jamie

|||

I didn't realize the variable value was evaluated at design time, since it's set at runtime. Makes sense though - thanks a lot!

- Dana

Ole DB Multi value named parameters problem.

I'm trying to use cascading report parameters using the mult-value select option. The first parameter is used as input for second parameter.

The first dataset called Employee,
select employeeid from humanresources.employee

Using SQL Server you can use multi named parameters to create your second dataset. For example,
select employeeid, loginid from humanresources.employee where employeeid
in (@.Employee)

However, I'm sadly not using sql server. I'm trying to do the same thing using an OleDB (non SQL Server) interface which doesn't support named parameters. So I used an expression. For example,

="select employeeid, loginid from humanresources.employee where employeeid
in (" & Parameters!Employee.value &")"
This works in Reporting Services if you do not have multi-value select option checked. Once you check the multi-value select option I get an error creating the second data set.
"Cannot set the command text, Error during process of command text of expression"
How do I do multi-value expressions.

Data providers do not support multi value parameters natively. Consequently, we have chosen to implement query statement rewrite for the managed SQL and the managed Oracle provider. Multi value parameters are also supported for AS 2005 data sources.

The OleDB standard in particular only allows single value parameters. It is not possible to write a "generic" multi value parameter query rewrite algorithm that works with any database server behind an arbitrary OleDB provider.

If you need multi value parameter functionality for a specific OleDB provider, you could write a custom RS data extensions which implements in particular the Microsoft.ReportingServices.DataProcessing.IDataMultiValueParameter interface. Then you could implement the query rewrite yourself in a way that is understood by your target database server.

Hope this helps,
Robert

|||Thanks for the education on Ole DB Providers. The testing I did validates what you have told me.

I'm tyring to do a work around. Is it possible to create a data set expression when multi-value parameters is selected. So I could do an expression query similar to this where I concatanate everything into the in portion of the query?
="select employeeid, loginid from humanresources.employee where employeeid
in (" & Parameters!Employee.value &")"

I believe Reporting Services would construct the query and would therefor the OleDB provider would not have to support multiple parameters. Thus a work around.

Todd|||If you change a report parameter to be multi value, the .Value property will return an object[] rather than an object. Hence you cannot just use regular string concatenation.

In your particular case, you may want to try the following expression:
="select employeeid, loginid from humanresources.employee where employeeid in (" & Join(Parameters!Employee.value, ", ") & ")"

Below is more information about using multi value parameters in reports. To access individual values of a multi value parameter you can use expressions like this:
=Parameters!MVP1.IsMultiValue
boolean flag - tells if a parameter is defined as multi value
=Parameters!MVP1.Count
returns the number of values in the array
=Parameters!MVP1.Value(0)
returns the first selected value
=Join(Parameters!MVP1.Value)
creates a space separated list of values
=Join(Parameters!MVP1.Value, ", ")
creates a comma separated list of values
=Split("a b c", " ")
to create a multi value object array from a string (this can be used e.g. for drillthrough parameters, subreports, or query parameters)

See also MSDN:
* http://msdn.microsoft.com/library/en-us/vblr7/html/vafctjoin.asp
* http://msdn.microsoft.com/library/en-us/vbenlr98/html/vafctsplit.asp

-- Robert

OLe DB Command and Using the value of an input column more than once

Hi there,

I have an OLE DB Command which updates a table. However, the command needs to use the value of an input column more than once.

For example I want to update TableA only if either ColumnA or ColumnB have actually changed: -

update tableA

set columnA = ?,

column B = ?

where columnC = ?

AND (columnA != ? OR columnB != ?)

I can't map the Input column to more than one parameter so I've been forced to create a copy of columnA and ColumnB as input columns so I can map to the extra paramters that the Command shape expects.

I also attempted to modify the command syntax so it set up variables for the 3 values required and then set the values to parameters - but I get a very unhelpful syntax error message: -

declare @.ValueA varchar(50),

@.ValueB varchar(50),

@.ValueC varchar(50)

select @.ValueA = ?,

@.ValueB = ?,

@.ValueC = ?,

update tableA

set columnA = @.ValueA ,

column B = @.ValueB

where columnC = @.ValueC

AND (columnA != @.ValueA OR columnB != @.ValueB)

Any suggestions?

anydobbo wrote:

Hi there,

I have an OLE DB Command which updates a table. However, the command needs to use the value of an input column more than once.

For example I want to update TableA only if either ColumnA or ColumnB have actually changed: -

update tableA

set columnA = ?,

column B = ?

where columnC = ?

AND (columnA != ? OR columnB != ?)

I can't map the Input column to more than one parameter so I've been forced to create a copy of columnA and ColumnB as input columns so I can map to the extra paramters that the Command shape expects.

Yeah, that's what you have to do!

anydobbo wrote:

I also attempted to modify the command syntax so it set up variables for the 3 values required and then set the values to parameters - but I get a very unhelpful syntax error message: -

declare @.ValueA varchar(50),

@.ValueB varchar(50),

@.ValueC varchar(50)

select @.ValueA = ?,

@.ValueB = ?,

@.ValueC = ?,

update tableA

set columnA = @.ValueA ,

column B = @.ValueB

where columnC = @.ValueC

AND (columnA != @.ValueA OR columnB != @.ValueB)

Any suggestions?

I may be wrong but I don't think you can do that. The OLE DB Command accepts a DML statement but not a statement block (as far as I know).

-Jamie

|||Thanks Jamie

OLE DB Command and Stored Procedure that returns value and/or error

Guys,

could someone please tell me : am I supposed to use the OLE DB

Command in a dataflow to call a stored procedure to return a value? Or

is it just supposed to be used to call a straightforward insert

statement only?

What I am hoping to do:

I have a table with a few columns and one identity column. In a

dataflow I would like to effect an insert of a record to this table and

retrieve the identity value of the inserted record... and I'd like to

store the returned identity in a user variable.

If I AM supposed to be able to do this... then how on earth do I do it?

I have spent hours fooling around with the OLE DB command trying to call a stored proc and get a return value.

In the Advanced Editor any time I try to add an output column (by

clicking on Add Column) I just get an error dialog that says "the

component does not allow adding columns to this input or output)

So, am getting pretty concussed .. banging my head of the wall like this...

So put me out of my misery someone please.... is the OLE DB Command intended for this or not?

Thanks

PJ

I'm not terribly au fait with the OLE DB Command other than using it for UPDATEs but I do know that you can execute a stored procedure that uses values from the pipeline as parameters.

I suspect that you cannot get return values from the sproc and add that returned value into the pipieline - I stand to be corrected though. I have never tried it.

-Jamie

|||

PJ,

You can probably look at this post..

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=490010&SiteID=1

You can pass back return value into the pipe and use it in a derived column.

|||

PJ,

I do not know how many rows are you planning to insert every time; but trying to capture the identity value at the dataflow sounds a kind of expensive from performance standpoitn. Have you consider to generate your own surrogatekey in a script task instead?

Rafael Salas

|||

Hi there Rafael,

Yeah, I suppose it could get pretty slow if you have a lot of data.. I only have ten small files (10 rows each) to load.. so its not a huge performance hit.

An I already have a DAL written for other applications to call stored procs's and was hoping to use that....

Its a while ago that I wrote the previous post.. and solved the problem in the meantime by writing a script task and calling the stored proc from inside that...

It's an ok approach for me (using small amounts of data) but I remember it seemed like a lot of work to do something I think should be easy to do...

Thanks

PJ

OLE DB Command and Stored Procedure that returns value and/or error

Guys,
could someone please tell me : am I supposed to use the OLE DB Command in a dataflow to call a stored procedure to return a value? Or is it just supposed to be used to call a straightforward insert statement only?
What I am hoping to do:
I have a table with a few columns and one identity column. In a dataflow I would like to effect an insert of a record to this table and retrieve the identity value of the inserted record... and I'd like to store the returned identity in a user variable.
If I AM supposed to be able to do this... then how on earth do I do it?
I have spent hours fooling around with the OLE DB command trying to call a stored proc and get a return value.
In the Advanced Editor any time I try to add an output column (by clicking on Add Column) I just get an error dialog that says "the component does not allow adding columns to this input or output)
So, am getting pretty concussed .. banging my head of the wall like this...
So put me out of my misery someone please.... is the OLE DB Command intended for this or not?
Thanks
PJ

I'm not terribly au fait with the OLE DB Command other than using it for UPDATEs but I do know that you can execute a stored procedure that uses values from the pipeline as parameters.

I suspect that you cannot get return values from the sproc and add that returned value into the pipieline - I stand to be corrected though. I have never tried it.

-Jamie

|||

PJ,

You can probably look at this post..

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=490010&SiteID=1

You can pass back return value into the pipe and use it in a derived column.

|||

PJ,

I do not know how many rows are you planning to insert every time; but trying to capture the identity value at the dataflow sounds a kind of expensive from performance standpoitn. Have you consider to generate your own surrogatekey in a script task instead?

Rafael Salas

|||

Hi there Rafael,

Yeah, I suppose it could get pretty slow if you have a lot of data.. I only have ten small files (10 rows each) to load.. so its not a huge performance hit.

An I already have a DAL written for other applications to call stored procs's and was hoping to use that....

Its a while ago that I wrote the previous post.. and solved the problem in the meantime by writing a script task and calling the stored proc from inside that...

It's an ok approach for me (using small amounts of data) but I remember it seemed like a lot of work to do something I think should be easy to do...

Thanks

PJ

OlE Db Command

Hi,

Iam trying to generate the increment value for a column by taking Maximum value of that field by using OLE DB Command Transformation.

This is the query iam using for that :
select max(ApplicationId)+1 as APPLICATIONID from Source..Applications

Iam finding difficulty in getting the 'APPLICATIONID' as the out put from OLEDB Transformation.OLE DB is not going to work with out any parameters ?can you please throw some light on this .First time iam using this transformation.

Another alternate solution is generating through script task (Thanks Jamie Thomson for his article).But i dont know how i can modify this to get the Max value instead of declaring some constant value for increment

Public Class ScriptMain
Inherits UserComponent
Dim Counter As Int32
Public Sub New()
Counter = 1006
End Sub

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
'
' Add your code here
'
Counter += 1
Row.APPLICATIONTASKID = Counter

End Sub

End Class
Please me help me in finding one solution from the above two

Thanks
Niru
No replies ..

Am I dint explained properly or nobody are aware of this?

Thanks
Niru
|||

If I understand your question correctly, you're asking to generate a surrogate key id (1,2,3,..) which key id is started from last key id + 1 from your source table. If yes, you can try this:

On control flow, create an Execute SQL task with your select statement:

select isnull(max(ApplicationId),1) as APPLICATIONID from Source..Applications

Set Result Set property = Single Row

Create a variable, on the Result Set assign your variable.

On data flow, create a column, ex. ApplicationKeyID set to null. Follow by script component task; in Input Columns, select the ApplicationKeyID and Usage type = ReadWrite. On Script, type in the variable name for ReadOnlyVariables property. And your script will look something like this:

Public Class ScriptMain

Inherits UserComponent

Dim Counter As Integer = 0

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Row.ApplicationKeyID = Variables.VariableName+ Counter

Counter = Counter + 1

End Sub

End Class

Hope this does not cause any more confusion for you...