Model Builder Field Order and Inheritance

When I create a report model, it is easy to change the order in a given entity. Does anyone know how to order the fields in an entity when you are using inheritance or inlining? I want to have the fields in alphabetical order so that the end user doesn't have to hunt for the correct field.

The other item I need to find out is how to customize the prefixes that inlining uses. Any ideas there?

Ron

Specifying field order across entities is not supported in this release. One thing you can do to mitigate this is use field folders so the user has a "tree" to search instead of a long list that is not ordered very well. In the case of inlining, you can actually place the role in a folder by itself, and all its fields will show up there when the role is expanded.

By "prefixes" I assume you mean the contextual naming that is applied based on the Role.ContextualName and Attribute.ContextualName properties. These are the mechanism for controlling this behavior.

Hope this helps!

|||

That was right on the money with both of the questions. That folder trick is really helpful in cleaning up some seriously unmanageable lists of fields.

R

sql

Mixing parameter syntax in Execute SQL task

Hi all,

As part of the logging process for data input, I want to update two fields in a logging table. The first is a datetime, derived from looking up the maximum value in another table (the table I've just imported), and the second is an integer - the number of rows captured in a variable during the task.

I can do this in two separate Execute SQL tasks as follows:

Task 1 syntax

DECLARE @.maxDate datetime
SELECT @.maxDate = max(dtLastChangedDate)
FROM dbo.tblCancel_RAW

UPDATE dbo.tblLogging
SET PreviousFilterValue = CurrentFilterValue,
CurrentFilterValue = ISNULL(CAST ( @.maxdate as varchar(25)),CurrentFilterValue),
DateSourceTableLastRead = GetDate(),
RowsReturned= -1
WHERE SourceTableName = 'cancel'

Task 2 Syntax, with the variable user::rowsimported mapped to parameter 0

UPDATE dbo.tblLogging
SET
RowsReturned= ?
WHERE SourceTableName = 'cancel'

However I cannot make this work with a single SQL statement such as

DECLARE @.maxDate datetime
SELECT @.maxDate = max(dtLastChangedDate)
FROM dbo.tblCancel_RAW

UPDATE dbo.tblLogging
SET PreviousFilterValue = CurrentFilterValue,
CurrentFilterValue = ISNULL(CAST ( @.maxdate as varchar(25)),CurrentFilterValue),
DateSourceTableLastRead = GetDate(),
RowsReturned= ?
WHERE SourceTableName = 'cancel'

because no matter how I try to map the parameter (0,1,2,3,4 etc) the task fails.

Is this behaviour by design, is it a bug, or is there something I've missed?

Thanks as ever,

Richard

Richard,

What error message do you get?

-Jamie

Missing fields in MSmerge_history table under SQL Server 2005

Hi,

I have a query that uses the following fields from MSmerge_history:

MSmerge_history.start_time, MSmerge_history.runstatus and MSmerge_history.duration

Below is the query that I am using:

SELECT

MSmerge_agents.subscriber_name AS SubscriberName,

MSmerge_history.start_time AS SyncTime,

MSmerge_history.runstatus AS SyncStatusID,

MSmerge_history.comments AS Comments,

MSmerge_history.duration AS Duration

FROM distribution.dbo.MSmerge_agents MSmerge_agents INNER JOIN distribution.dbo.MSmerge_history MSmerge_history

ON MSmerge_agents.id = MSmerge_history.agent_id

WHERE MSmerge_history.runstatus IN (2, 6) AND publisher_db = DB_NAME()

AND MSmerge_agents.subscriber_name + CONVERT(nvarchar, MSmerge_history.start_time) NOT IN

(SELECT SubscriberName + CONVERT(nvarchar, SyncTime) FROM SyncActivities)

My query runs fine under SQL Server 2000 but when I run it in SQL Server 2005, it doesn't work any more. Looking at MSmerge_history table under SQL Server 2005, this fields have been removed. Does anyone know where I can access those fields? Is it in another table?

thanks

Romeo

You should start using table distribution.dbo.MSmerge_sessions, which has a much better summary of all sync sessions.

Missing fields in MSmerge_history table under SQL Server 2005

Hi,

I have a query that uses the following fields from MSmerge_history:

MSmerge_history.start_time, MSmerge_history.runstatus and MSmerge_history.duration

Below is the query that I am using:

SELECT

MSmerge_agents.subscriber_name AS SubscriberName,

MSmerge_history.start_time AS SyncTime,

MSmerge_history.runstatus AS SyncStatusID,

MSmerge_history.comments AS Comments,

MSmerge_history.duration AS Duration

FROM distribution.dbo.MSmerge_agents MSmerge_agents INNER JOIN distribution.dbo.MSmerge_history MSmerge_history

ON MSmerge_agents.id = MSmerge_history.agent_id

WHERE MSmerge_history.runstatus IN (2, 6) AND publisher_db = DB_NAME()

AND MSmerge_agents.subscriber_name + CONVERT(nvarchar, MSmerge_history.start_time) NOT IN

(SELECT SubscriberName + CONVERT(nvarchar, SyncTime) FROM SyncActivities)

My query runs fine under SQL Server 2000 but when I run it in SQL Server 2005, it doesn't work any more. Looking at MSmerge_history table under SQL Server 2005, this fields have been removed. Does anyone know where I can access those fields? Is it in another table?

thanks

Romeo

You should start using table distribution.dbo.MSmerge_sessions, which has a much better summary of all sync sessions.

Missing Fields

I am calling a stored procedure from reporting services. The problem I'm
having is that it's only returning the first field in the result set in the
field list.
When I query under the data tab, data and field names are there and all data
is returned successfully.
I have tried refreshing the fieds, refreshing data, creating a new
report...you name it.
I have used both the sql server and ole db providers and both produce the
same behavoir.
The stored procedure has 5 parameters. I have tried both temp tables and
table variables in the stored procedure to no avail.
When I manually add the fields I get errors as well.
Can anyone provide any help or insight? I'm stumped on this one.
Thanks!OK...I think I have this figured out, although I'm not really happy about the
behavoir. Perhaps MS can put this on their list of issues.
It appears that if you use a stored procedure within another stored
procedure (say the one your calling to populate your report) , it throws off
the fields that are returned.
In my case, I have a stored procedure internal to the one I was calling that
figures out the current date. The return value was the only field being
returned within the reporting services .net interface.
Perhaps it's the way I'm calling the SP and assigning the return value to an
internal variable.
Nonetheless...I hope this helps. Hopefully someone from MS will comment on
this behavoir for us.
"Scott M" wrote:
> I am calling a stored procedure from reporting services. The problem I'm
> having is that it's only returning the first field in the result set in the
> field list.
> When I query under the data tab, data and field names are there and all data
> is returned successfully.
> I have tried refreshing the fieds, refreshing data, creating a new
> report...you name it.
> I have used both the sql server and ole db providers and both produce the
> same behavoir.
>
> The stored procedure has 5 parameters. I have tried both temp tables and
> table variables in the stored procedure to no avail.
>
> When I manually add the fields I get errors as well.
> Can anyone provide any help or insight? I'm stumped on this one.
> Thanks!|||Can you duplicate this behavior with either adventureworks2000 or northwind.
I would like to investigate this and see if there is a workaround.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Scott M" <ScottM@.discussions.microsoft.com> wrote in message
news:548E8585-626C-4A02-BD43-3B61B5E910E4@.microsoft.com...
> OK...I think I have this figured out, although I'm not really happy about
the
> behavoir. Perhaps MS can put this on their list of issues.
> It appears that if you use a stored procedure within another stored
> procedure (say the one your calling to populate your report) , it throws
off
> the fields that are returned.
> In my case, I have a stored procedure internal to the one I was calling
that
> figures out the current date. The return value was the only field being
> returned within the reporting services .net interface.
> Perhaps it's the way I'm calling the SP and assigning the return value to
an
> internal variable.
> Nonetheless...I hope this helps. Hopefully someone from MS will comment
on
> this behavoir for us.
> "Scott M" wrote:
> > I am calling a stored procedure from reporting services. The problem
I'm
> > having is that it's only returning the first field in the result set in
the
> > field list.
> >
> > When I query under the data tab, data and field names are there and all
data
> > is returned successfully.
> >
> > I have tried refreshing the fieds, refreshing data, creating a new
> > report...you name it.
> >
> > I have used both the sql server and ole db providers and both produce
the
> > same behavoir.
> >
> >
> > The stored procedure has 5 parameters. I have tried both temp tables
and
> > table variables in the stored procedure to no avail.
> >
> >
> > When I manually add the fields I get errors as well.
> >
> > Can anyone provide any help or insight? I'm stumped on this one.
> >
> > Thanks!|||Bruce,
I don't have those databases installed here at work...but I can give you the
essence of what happened.
There was a stored procedure called within a wrapper stored procedure. The
internal stored procedure was written to return the current period code
(200408, 200409 etc).
The wrapper stored procedure returned a recordset based on the period code
that was returned in the internal stored procedure.
When the report executed the wrapper stored procedure, it was only returning
the record from the internal stored procedure. So all I got was one field
with a value of the current period code.
I resolved the issue by creating a function to return the period code. That
seemed to fix it.
There might be a better way to call an internal stored procedure than the
way this was used. It was assigning an internally declared variable within
the wrapper stored procedure to the output parameter on the stored procedure
that returns the current period code.
Not sure if that helps. Seems like a bug to me, but perhaps it's by design
for some strange reason. Nonetheless, I'll have to re-write a lot of SP's in
order to utilize Reporting Services since the developer who originally
created a lot of the procedures in our environment didn't seem to utilize
functions.
Thanks for your response. Let me know if I can provide any further insight.
Scott
"Bruce L-C [MVP]" wrote:
> Can you duplicate this behavior with either adventureworks2000 or northwind.
> I would like to investigate this and see if there is a workaround.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Scott M" <ScottM@.discussions.microsoft.com> wrote in message
> news:548E8585-626C-4A02-BD43-3B61B5E910E4@.microsoft.com...
> > OK...I think I have this figured out, although I'm not really happy about
> the
> > behavoir. Perhaps MS can put this on their list of issues.
> >
> > It appears that if you use a stored procedure within another stored
> > procedure (say the one your calling to populate your report) , it throws
> off
> > the fields that are returned.
> >
> > In my case, I have a stored procedure internal to the one I was calling
> that
> > figures out the current date. The return value was the only field being
> > returned within the reporting services .net interface.
> >
> > Perhaps it's the way I'm calling the SP and assigning the return value to
> an
> > internal variable.
> >
> > Nonetheless...I hope this helps. Hopefully someone from MS will comment
> on
> > this behavoir for us.
> >
> > "Scott M" wrote:
> >
> > > I am calling a stored procedure from reporting services. The problem
> I'm
> > > having is that it's only returning the first field in the result set in
> the
> > > field list.
> > >
> > > When I query under the data tab, data and field names are there and all
> data
> > > is returned successfully.
> > >
> > > I have tried refreshing the fieds, refreshing data, creating a new
> > > report...you name it.
> > >
> > > I have used both the sql server and ole db providers and both produce
> the
> > > same behavoir.
> > >
> > >
> > > The stored procedure has 5 parameters. I have tried both temp tables
> and
> > > table variables in the stored procedure to no avail.
> > >
> > >
> > > When I manually add the fields I get errors as well.
> > >
> > > Can anyone provide any help or insight? I'm stumped on this one.
> > >
> > > Thanks!
>
>|||I've encountered the same problem. I've created about 50 reports based on
stored procedures and this is the first time this has occured.
The proc in question has 11 parameters. It returns the result set in the
Data Preview section, but only returns a single field in the field list. The
field returned is not even in the actual result set.
The proc I'm calling does call other procs. It was created bby a third party
and is used for other functions as well as the report I'm writing, so I can't
modify it.
Any ideas?
"Bruce L-C [MVP]" wrote:
> Can you duplicate this behavior with either adventureworks2000 or northwind.
> I would like to investigate this and see if there is a workaround.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Scott M" <ScottM@.discussions.microsoft.com> wrote in message
> news:548E8585-626C-4A02-BD43-3B61B5E910E4@.microsoft.com...
> > OK...I think I have this figured out, although I'm not really happy about
> the
> > behavoir. Perhaps MS can put this on their list of issues.
> >
> > It appears that if you use a stored procedure within another stored
> > procedure (say the one your calling to populate your report) , it throws
> off
> > the fields that are returned.
> >
> > In my case, I have a stored procedure internal to the one I was calling
> that
> > figures out the current date. The return value was the only field being
> > returned within the reporting services .net interface.
> >
> > Perhaps it's the way I'm calling the SP and assigning the return value to
> an
> > internal variable.
> >
> > Nonetheless...I hope this helps. Hopefully someone from MS will comment
> on
> > this behavoir for us.
> >
> > "Scott M" wrote:
> >
> > > I am calling a stored procedure from reporting services. The problem
> I'm
> > > having is that it's only returning the first field in the result set in
> the
> > > field list.
> > >
> > > When I query under the data tab, data and field names are there and all
> data
> > > is returned successfully.
> > >
> > > I have tried refreshing the fieds, refreshing data, creating a new
> > > report...you name it.
> > >
> > > I have used both the sql server and ole db providers and both produce
> the
> > > same behavoir.
> > >
> > >
> > > The stored procedure has 5 parameters. I have tried both temp tables
> and
> > > table variables in the stored procedure to no avail.
> > >
> > >
> > > When I manually add the fields I get errors as well.
> > >
> > > Can anyone provide any help or insight? I'm stumped on this one.
> > >
> > > Thanks!
>
>