query for row creation/last update

I'm working on a database migration. The destination DB has date fields in each table for row creation and last update the source DB does not. Is there a system table or way to query the transaction long to find the entry date and last update date for a row using the primary key in the source DB?

Thanks for any helpThere is no builtin audit function for that. The typical audit is to create a trigger and keep track of your insert/update/delete.

Query Execution Failed for Dataset (Beginner)

I got this Error, Query Execution for Dataset 'Source'.

When i try to run a report that is actually a drillthrough from another report. It runs fine in Report Designer, and when i deploy to my Local server. But when i deploy it to my virtual Report Server, I get this error messege. Why is it doing this? and where can i see errors for this type of stuff, so i can figure this out. This uses the same Datasource as the report linked from it. That report works fine, why wont this one? any ideas?

Look for the report server log at this location C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\LogFiles if installed to the default location.|||A collegue of mine helped me figure this one out. My stored procedure was not set to public in the properties section. Thats why it worked on my local computer and not on the report server.

Query designer toggle button not present

When I create a Report Server Project Using Visual Studio 2005 with SQL Server 2005 I can create a data source with no problem and the test shows it is good good.

When I next create a report and go to the query builder using that same data source and click on the query builder button I see the Query Builder screen, but there is no toggle button in the top left of the screen so I cannot go into the graphical mode to see the tables.

I have uninstalled and reinstalled both Visual Studio and SQL Server but I still have the same problem. What should I do to get the button visible on the screen?

Can anyone help?

Terry,

Are you setting this report up from a shared data source or from making a new datasource. You may want to set up a shared datasource first then make your report from the shared datasource and see if that works. It works that way for me.

|||

It makes no difference either way, I can have new data source or a shared data source.

I have a server and my computer both of which appear to have a nearly identical setup of Studio and SQL Server but I can perform identical steps on both and on the server I get the toggle button but on my computer I get nothing. This is what makes be think it may be a bad installation but I have reinstalled both Studio and SQL Server on my machine and I still get the same thing!

Thanks for responding though.

Terry

|||

Terry,

You may have already figured it out, but you may just want to use stored procedures. When we create a new report we just go through the wizard, pick our datasource, and then tell it what stored proc we want it to use. I believe the syntax is

exec rptsp_MyStoredProcedure @.MyParameter1,@.MyParameter2

Do you have a lot of experience with SQL queries and stored procs? If not maybe I can help.

Query calculated OLAP members

I'm trying to create a report using an OLAP cube as my data source, but I can
retrieve only the dimensions and measures, not the calculated members.
My query is:
select * from MasterCube
Is there any way to extract them?Below is a sample MDX query (used in the sample Foodmart reporting services
report) which includes a calculated measure, Store Cost.
SELECT { [Measures].[Store Sales], [Measures].[Store Cost] } ON COLUMNS,
{ Descendants([Product].[All Products], [Product].[Brand Name], LEAVES) }
ON ROWS,
{ Time.[1997].[Q1],Time.[1997].[Q2],Time.[1997].[Q3],Time.[1997].[Q4] } ON
PAGES
FROM Sales
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Cesar Meza" <CesarMeza@.discussions.microsoft.com> wrote in message
news:0A29D2FD-F3CF-4772-A063-E585C6B1DDA3@.microsoft.com...
> I'm trying to create a report using an OLAP cube as my data source, but I
> can
> retrieve only the dimensions and measures, not the calculated members.
> My query is:
> select * from MasterCube
> Is there any way to extract them?|||The real issue this: I have a cube that is too big for Excel's Pivot table to
handle it. So I'm trying to export it as PDF report using RS.
So I have several formulas that rely on the Analysis Services functionalliy
(aggregates, ranking, etc.) that are already in the cube.
I know I can write the MDX statement to recreate them, but this will mean to
re-create all the calculated members once more in the statement.
Any suggestions?.
Thanks a lot for your response.

Query Assistance.

I am relatively new to the use of coplex queries. Here is a task that I am trying to accomplish.

Source table.

Address ID Workstation Test-a 1 WS1 Test-b 2 WS1 Test-a 5 WS2 Test-d 3 WS2 Test-b 7 WS2

I am trying to write a query that will display this result into Excel.

Address Duplicate WS1 WS2 Test-a
Yes
1
5 Test-b
Yes
2
7 Test-d
No

Basically I am trying to identify if there is a duplicate address, if so mark it as such in the duplicate column and then placing the ID into a column under the Workstation.
I only want to see the duplicated address once (Distinct?) but mark that it is indeed a duplicate and mark the ID's that it has under the workstations.

Any ideas? I have created a query that does pull the data in the first example that is doing a DTS export to excel. However I need to format this to show the second example.

I appreciate any help I can get on this.

What are the possible values for the Workstation column? Is it always at most 2 workstations for any address?|||No, there are actually around 8-10 workstations.
|||

This should give you an idea about how to approach the solution.

Code Snippet

SET NOCOUNT ON

DECLARE @.MyTable table
( RowID int IDENTITY,
Address varchar(20),
[ID] int,
Workstation varchar(20)
)

INSERT INTO @.MyTable VALUES ( 'Test-a', 1, 'WS1' )
INSERT INTO @.MyTable VALUES ( 'Test-b', 2, 'WS1' )
INSERT INTO @.MyTable VALUES ( 'Test-a', 5, 'WS2' )
INSERT INTO @.MyTable VALUES ( 'Test-d', 3, 'WS2' )
INSERT INTO @.MyTable VALUES ( 'Test-b', 7, 'WS2' )

SELECT
Duplicate = CASE WHEN dt.ADDRESS IS NULL THEN 'No' ELSE 'Yes' END,
m.Address,
m.[ID],
m.Workstation
FROM @.MyTable m
LEFT JOIN (SELECT Address
FROM @.MyTable
GROUP BY
Address
HAVING count( Address ) >= 2
) dt
ON m.Address = dt.Address
ORDER BY
Address,
Workstation

Duplicate Address ID Workstation
-- -- --
Yes Test-a 1 WS1
Yes Test-a 5 WS2
Yes Test-b 2 WS1
Yes Test-b 7 WS2
No Test-d 3 WS2

|||I appreciate the information. I have been working with this to pull the data I need. I have modified the insert statement to pull the data from another table.

I am getting an error however:
Server: Msg 209, Level 16, State 1, Line 15
Ambiguous column name 'PointAddress'.

As I mentioned I am new to alot of this, how do I identify line 15? I have searched on the error itself and found references that I should be creating an alias for the "pointaddress" column.

I appreciate the help with this. Explanations will be helpful as well.

Code Snippet

SET NOCOUNT ON

DECLARE @.dup_trnd table
( RowID int IDENTITY,
PointAddress varchar(30),
TrendID int,
Workstation varchar(30)
)

INSERT dup_trnd
SELECT PointAddress,TrendID,Workstation from DuplicateTrends

SELECT
Duplicate = CASE WHEN dt.PointAddress IS NULL THEN 'No' ELSE 'Yes' END,
m.PointAddress,
m.TrendId,
m.Workstation
FROM @.dup_trnd m
LEFT JOIN (SELECT PointAddress
FROM @.dup_trnd
GROUP BY
PointAddress
HAVING count( PointAddress ) >= 2
) dt
ON m.PointAddress = dt.PointAddress
ORDER BY
PointAddress,
Workstation

|||

Try this:

Code Snippet

SET NOCOUNT ON

SELECT
Duplicate = CASE WHEN dt.PointAddress IS NULL THEN 'No' ELSE 'Yes' END,
d.PointAddress,
d.TrendId,
d.Workstation
FROM Dup_Trnd d
LEFT JOIN (SELECT PointAddress
FROM Dup_Trnd
GROUP BY PointAddress
HAVING count( PointAddress ) >= 2
) dt
ON d.PointAddress = dt.PointAddress
ORDER BY
d.PointAddress,
d.Workstation

|||Thank you for the assistance. This is mostly what I need and can work on it from here.