Launching the Reports From Browser

Hi friends,

We have developed the Reports using SQL Server Reporting Services 2005.

In order to make our all reports dynamic we are referancing DLL in all our reports.

That DLL we have pasted in

D:\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer\bin

C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PublicAssemblies

So when we observer the reports from Preview tab then we can see the effect of our DLL and all reports are working fine.

Now we want to launch all reports on browser. So we are giving the URL path as

http://localhost/ReportServer

But as per the SQL Server 2005 Reporting Services by Brian Larson we have pasted the following code into rssrvpolicy.config file

<CodeGroup

class ="UnionCodeGroup"

version="1"

PermissionSetName="Execution"

Name="WeatherWebServiceCodeGroup"

Description ="Code group for the Weather Web Service">

<IMembershipCondition Class ="UrlMembershipCodndion"

version ="1"

Url="http://localhost/ReportServer"/>

</CodeGroup>

and second code is

<CodeGroup

class="UnionCodeGroup"

version ="1"

PermissionSetName="FullTrust"

Name="MSSQLRSCodeGroup"

Description="Code group for the MS SQL RS Book Custom Assemblies">

<IMembershipCondition

class="StrongNameMembershipCondition"

version="1"

PublicKeyBlob="0024000004800000940000000602000000240000525341310004000001000100B9F74F2D5B0AAD33AA619B00D7BB8B0F7678393A0F4CD586C9036D72455F8D1E85BF635C9FB1DA9817DD0F751DCEE77D9A47959E8728028B9B6CC7C25EB1E59CB3DE01BB516D46FC6AC6AF27AA6E71B65F6AB91B9576886F2EF39417F17B567AD200E151FC744C6DA72FF5882461E6CA786EB2997FA968302B7B2F24BDBFF7A5"/>

</CodeGroup>

so now we are checking the site

http://localhost/ReportServer

so we are getting following error

Configuration Error

Description: An error occurred during the processing of a configuration file required to service this request. Please review the specific error details below and modify your configuration file appropriately.

Parser Error Message: Could not load file or assembly 'ReportingServicesWebServer, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The parameter is incorrect. (Exception from HRESULT: 0x80070057 (E_INVALIDARG))

Source Error:

Line 27: <assemblies> Line 28: <clear /> Line 29: <add assembly="ReportingServicesWebServer" /> Line 30: </assemblies> Line 31: </compilation>

Source File: D:\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer\web.config Line: 29

---

Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.42

so where i am going wrong ?

can anyone help me out plz ?

anyhelp will be greatly appreciated

sandy

You have some typos in the first CodeGroup element:

<IMembershipCondition class ="UrlMembershipCondition"

You don't need this element anyway unless your DLL accesses a web service. In the second CodeGroup, it looks like PublicKeyBlob is copied from the book. Make sure that it matches the public key of your own assembly.

-Albert

|||

hi Albert,

thx for ur help. First that I did is I removed the following code

<CodeGroup

class ="UnionCodeGroup"

version="1"

PermissionSetName="Execution"

Name="WeatherWebServiceCodeGroup"

Description ="Code group for the Weather Web Service">

<IMembershipCondition Class ="UrlMembershipCodndion"

version ="1"

Url="http://localhost/ReportServer"/>

</CodeGroup>

so now I am not getting the configuration error.

Secondly whatever you said that I simply copied the Public Key Blob so now I have replaced it with my current DLL Public Key.

But Albert still I cant see the effect of my function on URL ie browser.

I can see the same effect on my Preview Tab but cant on Browser so where I am going wrong ?

--sandy

|||The easiest way for testing is to add the following:

<CodeGroup class="UnionCodeGroup"
version="1"
PermissionSetName="FullTrust"
Name="MyCodeGroup2"
Description="Code group for my data processing extension">
<IMembershipCondition class="UrlMembershipCondition"
version="1"
Url="C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer\bin\[your assembly].dll"
/>
</CodeGroup>

just below the

<CodeGroup
class="UnionCodeGroup"
version="1"
PermissionSetName="FullTrust">
<IMembershipCondition
class="UrlMembershipCondition"
version="1"
Url="$CodeGen$/*"
/>
</CodeGroup>

entry.

If that works you can try to reduce the Permissions and try the Public Key-Stuff..
Have you looked at the logfiles? Sometimes you need to assert the permissions for other dlls that you have referenced..
|||

Hi binni,

The code which ur talking about , I have already incorported in my rsspolicy.config file.

Secondly I made sure that my current assembly is not using any other assembly also.

Then I checked all log file which are in

C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\LogFiles

there also I didnt find any log file saying this problem.

Still I cant able to see the effect of my function on Browser.

-sandyee

|||

When you say you don't see the effect of your function, does that mean you see an error message? How do you know it is not working?

-Albert

|||

Hi Friends,

what we have done is that first of all we have stored all the values in one table like background color , forground colro , font size , font weight etc. Now in our function we are refering that table. And whatever values we have stored in the table that values function takes and set for the report. So when we are saying font size of 12 and when we apply that function to the Report Header , Report Header will be of font size 12 , and this effect we can see in Preview tab. But when see the report on browser Report Header is of font 8 only.

This I exactly mean by function not working,

Sandeep

|||

If the report is running and is not producing any errors, then either your code is not being called, or it's being called and not producing the correct result. Have you checked your code to make sure there are no problems? As a last resort you could try debugging the ASP.Net process.

-Albert

|||

Hi friends,

As my reports are working fine on Browser do I need to make any changes in any other configuartion file other that ReportServer config file (rssvpolicy.config)

for eg do I need to make any changes in Report Manager Config file (rsmgrpolicy.config) ?

-sandeep

|||

No, you do not need to make any changes in Report Manager config.

-Albert

|||To provide a solution for other people:
Maybe sandeep has forgotten the:
<Assembly: System.Security.AllowPartiallyTrustedCallers()> VB.NET
[Assembly: System.Security.AllowPartiallyTrustedCallers] C#.NET
for his assembly.

I got the same problem: my assembly just didn't work and the ReportServer-logfiles didn't contain any information of the error! Visual Studio 2005 showed the error, that my assembly doesn't allow partially trusted caller.
sql

Launching Reports on Browser with Custom Assembly

Hi friends,

We have developed the Reports using SQL Server Reporting Services 2005.

In order to make our all reports dynamic we are refering DLL in all our reports.

That DLL we have pasted in

C:\Program Files \Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer\bin

C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PublicAssemblies

So when we observer the reports from Preview tab then we can see the effect of our DLL and all reports are working fine.

Now we want to launch all reports on browser so we have pasted the following code into rssrvpolicy.config file

<CodeGroup class="UnionCodeGroup"

version="1"

PermissionSetName="FullTrust"

Attributes="LevelFinal"

Name="Wizard_1"

Description="Codegroup generated by the .NET Configuration tool">

<IMembershipCondition class="StrongNameMembershipCondition"

version="1"

PublicKeyBlob="0024000004800000940000000602000000240000525341310004000001000100DD11C5519C419099881AE462DC4D257CF2A1C126A3D06FEDFA1BF89A84993ADCC495032E92A1CB28A6400FE2D5667358155D6D35637DAB62CE95995B1FC66D23E2C0664AE8E2021BE66080DA0E5972C18B58E658470B82FC14D6E575EC8903367E16C16B168A90A6D8B20A9D9F91ED7CF95A45FA52435F76058D4F32807F9CF8"

AssemblyVersion="1.0.0.0"/>

and sencond code is

<CodeGroup class="UnionCodeGroup"

version="1"

PermissionSetName="FullTrust"

Name="MyNewCodeGroup"

Description="A special code group for my custom assembly.">

<IMembershipCondition

class="UrlMembershipCondition"

version="1"

Url="C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer\bin\ReportStyle.dll"/>

</CodeGroup>

so now we are checking the site

http://localhost/ReportServer

we can't see the effect of our DLL. So where excatly I am making mistake ?

This is now becoming very critical issue for our project so any help will be grately appreciated.

-- sandeep -

Hi,
I've a similar scenario right now. The problem seems to be the signing of the assembly!!! I don't know if this is one of MS great "it's not a bug, it's a feature"-Stuff, but try:
-DO NOT sign the assembly!
-delete the code-group with strongNameMembership (the first one) and only use the UrlMembership..
-have fun

@.MS: ReportServer does not throw any error, it just displays "#Error" if you use a method of a signed assembly in a textfield. If you run the report in local debug you get:
"Warning: Der Value-Ausdruck für das Textfeld-Objekt 'textbox1' enth?lt einen Fehler: Die Assembly l?sst keine Aufrufer zu, die nicht voll vertrauenswürdig sind. (rsRuntimeErrorInExpression)".
In english something like:
"Warning: The Value-Expression for textfield 'textbox1' contains an error: the assembly doesn't accept callers without fulltrust"

Benni

|||

Got it working?

edit:

After searching the internet I found what the error message means and that this is really rather a feature than a bug. So just add:
<Assembly: System.Security.AllowPartiallyTrustedCallers()> (VB)
[Assembly: System.Security.AllowPartiallyTrustedCallers] (C#)

to your signed Assembly..

But I think it's still a bug that the ReportServer doesn't write the cause of the error to the logfiles..

Launch SSIS package with SQL Event

Is it possible to launch an SSIS package after a SQL event takes place? I need to run a package after a customer order is placed. Can a trigger in SQL launch the package?

You can use xp_cmdshell to call DTEXEC. You could also set up a job for the package and call sp_start_job.

See this link for some more detail and some other options: http://blogs.msdn.com/michen/archive/2007/03/22/running-ssis-package-programmatically.aspx

Latest record

My table are

Customer: customerId ,name

Order: orderId, customerId, product,date

I want to display latest order from the customer

You can use ORDER BY.

Select O.OrderId, C.Name, O.Product, O.DateFROM Order OJOIN Customer CON C.CustomerId O.CustomerIdORDER BY O.DateDesc

|||

SELECT orderId, customerId, product,[date], [name]FROM(select a.orderId, a.customerId, a.product,a.[date], b.name, row_number()over(partitionby a.customeridorderby [date]DESC)as RowNum

from [Order] ainnerjoin [Customer] bon a.customerId=b.customerId) t

WHERE RowNum= 1

sql

latest order date

Hi,
I have an order table which contains the following fielde: 1). orderid (this
is the order number 2). clientid 3). orderdate.
I need to script so that I can find out those clientids which do not have
place an order for at least 90 days.
Can you tell me how to program it?Read the documentation. You want to select clientids which are NOT IN a
subquery that selects all clients that have ordered in the last 90 days. You
could also use a join where max order date is less than 90 days ago.
RR
"qjlee" <qjlee@.discussions.microsoft.com> wrote in message
news:B84E46A2-9291-4D17-A625-3DBADD9746B1@.microsoft.com...
> Hi,
> I have an order table which contains the following fielde: 1). orderid
(this
> is the order number 2). clientid 3). orderdate.
> I need to script so that I can find out those clientids which do not have
> place an order for at least 90 days.
> Can you tell me how to program it?
>
>|||Something like this should give you the client and their last order date.
declare @.DaysSinceOrder as numeric
set @.DaysSinceOrder = 90
select
clientid ,
max(orderdate)
from "YourTableHere"
group by clientid
having max(orderdate) < getdate()-@.DaysSinceOrder
"qjlee" wrote:

> Hi,
> I have an order table which contains the following fielde: 1). orderid (th
is
> is the order number 2). clientid 3). orderdate.
> I need to script so that I can find out those clientids which do not have
> place an order for at least 90 days.
> Can you tell me how to program it?
>
>|||Please post DDL, sample data and expected results
(http://www.aspfaq.com/etiquette.asp?id=5006 )
Since you have a ClientID column in your Orders table, I am guessing that
you have a Clients table somewhere. Here is a complete guess:
SELECT ClientID, ClientName
FROM Clients
WHERE ClientID NOT IN (SELECT ClientID FROM Orders WHERE DATEDIFF(d,
OrderDate, CURRENT_TIMESTAMP) <= 90)
"qjlee" <qjlee@.discussions.microsoft.com> wrote in message
news:B84E46A2-9291-4D17-A625-3DBADD9746B1@.microsoft.com...
> Hi,
> I have an order table which contains the following fielde: 1). orderid
> (this
> is the order number 2). clientid 3). orderdate.
> I need to script so that I can find out those clientids which do not have
> place an order for at least 90 days.
> Can you tell me how to program it?
>
>

Latest and next-to-latest query?

I use a number of queries where I get the "latest" price/quantity/whatever. I
rely on the id's being in order (they are for us) and do this...
SELECT p.name, p.price
FROM tblPrices p
WHERE p.PriceID IN
(SELECT MAX(priceid) FROM tblprices GROUP BY
name)
Now I need to change this slightly, in addition to the latest price, I need
to get the "next-to-latest". My first attempt...
SELECT p1.name, p1.Price1 AS latestPrice, p2.Price1 AS olderPrice,
p1.Price1 - p2.Price1 AS diff
FROM tblPrices p1, tblPrices p2
WHERE p1.PriceID IN
(SELECT MAX(priceid)
FROM tblprices
GROUP BY accountid) AND (p2.PriceID IN
(SELECT MAX(priceid)
FROM tblprices
WHERE priceid NOT IN
(SELECT
MAX(priceid)
FROM
tblprices
GROUP BY accountid)
GROUP BY accountid))
This works, and I can live with that, but its very slow. Can anyone suggest
another way to go about this?
Maury
Hi
I meant PK--Primary Key
SELECT p.name, p.price
FROM tblPrices p
WHERE p.PriceID =(SELECT MAX(priceid) FROM tblprices
tp WHERE tp.PK=p.PK)
--OR
SELECT p.name, p.price
FROM tblPrices p
join
(SELECT MAX(priceid),name FROM tblprices GROUP BY name) AS D
ON d.PK=p.pk
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:14FBC159-36EA-4460-8CBE-BE7C9035AFF0@.microsoft.com...
>I use a number of queries where I get the "latest" price/quantity/whatever.
>I
> rely on the id's being in order (they are for us) and do this...
> SELECT p.name, p.price
> FROM tblPrices p
> WHERE p.PriceID IN
> (SELECT MAX(priceid) FROM tblprices GROUP BY
> name)
> Now I need to change this slightly, in addition to the latest price, I
> need
> to get the "next-to-latest". My first attempt...
> SELECT p1.name, p1.Price1 AS latestPrice, p2.Price1 AS olderPrice,
> p1.Price1 - p2.Price1 AS diff
> FROM tblPrices p1, tblPrices p2
> WHERE p1.PriceID IN
> (SELECT MAX(priceid)
> FROM tblprices
> GROUP BY accountid) AND (p2.PriceID IN
> (SELECT MAX(priceid)
> FROM tblprices
> WHERE priceid NOT IN
> (SELECT
> MAX(priceid)
> FROM
> tblprices
> GROUP BY
> accountid)
> GROUP BY accountid))
> This works, and I can live with that, but its very slow. Can anyone
> suggest
> another way to go about this?
> Maury

Latest and next-to-latest query?

I use a number of queries where I get the "latest" price/quantity/whatever. I
rely on the id's being in order (they are for us) and do this...
SELECT p.name, p.price
FROM tblPrices p
WHERE p.PriceID IN
(SELECT MAX(priceid) FROM tblprices GROUP BY
name)
Now I need to change this slightly, in addition to the latest price, I need
to get the "next-to-latest". My first attempt...
SELECT p1.name, p1.Price1 AS latestPrice, p2.Price1 AS olderPrice,
p1.Price1 - p2.Price1 AS diff
FROM tblPrices p1, tblPrices p2
WHERE p1.PriceID IN
(SELECT MAX(priceid)
FROM tblprices
GROUP BY accountid) AND (p2.PriceID IN
(SELECT MAX(priceid)
FROM tblprices
WHERE priceid NOT IN
(SELECT
MAX(priceid)
FROM
tblprices
GROUP BY accountid)
GROUP BY accountid))
This works, and I can live with that, but its very slow. Can anyone suggest
another way to go about this?
MauryHi
I meant PK--Primary Key
SELECT p.name, p.price
FROM tblPrices p
WHERE p.PriceID =(SELECT MAX(priceid) FROM tblprices
tp WHERE tp.PK=p.PK)
--OR
SELECT p.name, p.price
FROM tblPrices p
join
(SELECT MAX(priceid),name FROM tblprices GROUP BY name) AS D
ON d.PK=p.pk
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:14FBC159-36EA-4460-8CBE-BE7C9035AFF0@.microsoft.com...
>I use a number of queries where I get the "latest" price/quantity/whatever.
>I
> rely on the id's being in order (they are for us) and do this...
> SELECT p.name, p.price
> FROM tblPrices p
> WHERE p.PriceID IN
> (SELECT MAX(priceid) FROM tblprices GROUP BY
> name)
> Now I need to change this slightly, in addition to the latest price, I
> need
> to get the "next-to-latest". My first attempt...
> SELECT p1.name, p1.Price1 AS latestPrice, p2.Price1 AS olderPrice,
> p1.Price1 - p2.Price1 AS diff
> FROM tblPrices p1, tblPrices p2
> WHERE p1.PriceID IN
> (SELECT MAX(priceid)
> FROM tblprices
> GROUP BY accountid) AND (p2.PriceID IN
> (SELECT MAX(priceid)
> FROM tblprices
> WHERE priceid NOT IN
> (SELECT
> MAX(priceid)
> FROM
> tblprices
> GROUP BY
> accountid)
> GROUP BY accountid))
> This works, and I can live with that, but its very slow. Can anyone
> suggest
> another way to go about this?
> Maurysql

Latest and next-to-latest query?

I use a number of queries where I get the "latest" price/quantity/whatever.
I
rely on the id's being in order (they are for us) and do this...
SELECT p.name, p.price
FROM tblPrices p
WHERE p.PriceID IN
(SELECT MAX(priceid) FROM tblprices GROUP BY
name)
Now I need to change this slightly, in addition to the latest price, I need
to get the "next-to-latest". My first attempt...
SELECT p1.name, p1.Price1 AS latestPrice, p2.Price1 AS olderPrice,
p1.Price1 - p2.Price1 AS diff
FROM tblPrices p1, tblPrices p2
WHERE p1.PriceID IN
(SELECT MAX(priceid)
FROM tblprices
GROUP BY accountid) AND (p2.PriceID IN
(SELECT MAX(priceid)
FROM tblprices
WHERE priceid NOT IN
(SELECT
MAX(priceid)
FROM
tblprices
GROUP BY accountid)
GROUP BY accountid))
This works, and I can live with that, but its very slow. Can anyone suggest
another way to go about this?
MauryHi
I meant PK--Primary Key
SELECT p.name, p.price
FROM tblPrices p
WHERE p.PriceID =(SELECT MAX(priceid) FROM tblprices
tp WHERE tp.PK=p.PK)
--OR
SELECT p.name, p.price
FROM tblPrices p
join
(SELECT MAX(priceid),name FROM tblprices GROUP BY name) AS D
ON d.PK=p.pk
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:14FBC159-36EA-4460-8CBE-BE7C9035AFF0@.microsoft.com...
>I use a number of queries where I get the "latest" price/quantity/whatever.
>I
> rely on the id's being in order (they are for us) and do this...
> SELECT p.name, p.price
> FROM tblPrices p
> WHERE p.PriceID IN
> (SELECT MAX(priceid) FROM tblprices GROUP BY
> name)
> Now I need to change this slightly, in addition to the latest price, I
> need
> to get the "next-to-latest". My first attempt...
> SELECT p1.name, p1.Price1 AS latestPrice, p2.Price1 AS olderPrice,
> p1.Price1 - p2.Price1 AS diff
> FROM tblPrices p1, tblPrices p2
> WHERE p1.PriceID IN
> (SELECT MAX(priceid)
> FROM tblprices
> GROUP BY accountid) AND (p2.PriceID IN
> (SELECT MAX(priceid)
> FROM tblprices
> WHERE priceid NOT IN
> (SELECT
> MAX(priceid)
> FROM
> tblprices
> GROUP BY
> accountid)
> GROUP BY accountid))
> This works, and I can live with that, but its very slow. Can anyone
> suggest
> another way to go about this?
> Maury

LastNonEmpty vs MDX Script solve order

Hi,

I'm using the Account Intelligence and some account members use the aggregation function LastNonEmpty.

Witch evaluates before the LastNonEmpty function or my MDX Script that I put in the calculation inside the cube?

If my MDX Script change some values only in the the leaves of the time dimension in one account (taht uses LastNonEmpty function). What will be evaluate first? My MDX Script and after the LastNonEmpty function or the oposite?

Handerson

LastNonEmpty is applied to the members above the leaves of Time dimension by aggregation the leaves through LastNonEmpty. Therefore if you will apply some calculation to the leaves of Time - it will be done before the LastNonEmpty aggregation.sql

last updated date

Hi,
I'm trying to retrieve data of previous day from my 'sales' table. Sales
table has:
order#, order date, cust#, sales, qty
I have a metadates table: one colummn
date
my script is
select sum(sales), sum(qty) from sales where order_date=(select top 1 date
from metadates order by date desc)
But I got time out all the time.
I'm considering the Christmas holiday, when I come back on Dec 28, the job
runs every day, should pick up the sales on Dec 23rd instead of Dec 27.
Please help,
Thanks so much,
SarahPerhaps this will run faster:
declare @.prev_workday as datetime
select @.prev_workday = (select top 1 date from metadates order by date desc)
select sum(sales), sum(qty) from sales where order_date = @.prev_workday
So, if Dec 24 - 27 are not business days, then do not include them in the
metadates table.
"SG" <sguo@.coopervision.ca> wrote in message
news:OcxHvbCAGHA.272@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I'm trying to retrieve data of previous day from my 'sales' table. Sales
> table has:
> order#, order date, cust#, sales, qty
> I have a metadates table: one colummn
> date
> my script is
> select sum(sales), sum(qty) from sales where order_date=(select top 1 date
> from metadates order by date desc)
> But I got time out all the time.
> I'm considering the Christmas holiday, when I come back on Dec 28, the job
> runs every day, should pick up the sales on Dec 23rd instead of Dec 27.
> Please help,
> Thanks so much,
> Sarah
>|||"SG" <sguo@.coopervision.ca> wrote in message
news:OcxHvbCAGHA.272@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I'm trying to retrieve data of previous day from my 'sales' table. Sales
> table has:
> order#, order date, cust#, sales, qty
> I have a metadates table: one colummn
> date
> my script is
> select sum(sales), sum(qty) from sales where order_date=(select top 1 date
> from metadates order by date desc)
> But I got time out all the time.
> I'm considering the Christmas holiday, when I come back on Dec 28, the job
> runs every day, should pick up the sales on Dec 23rd instead of Dec 27.
> Please help,
> Thanks so much,
> Sarah
This may give you a better idea of what the date table should look like.
http://www.aspfaq.com/show.asp?id=2519sql

Last row in select question

DECLARE @.LastID int
DECLARE @.RowsToSelect int

SET ROWCOUNT @.RowsToSelect
SELECT @.LastID = [id] FROM MyTable ORDER BY [id]
SET ROWCOUNT 0

I think that value of @.LastID will be equals to [id] in the last row
of select(i.e. row with number @.RowsToSelect). But I don't understand
why it's true. For example, @.LastID not necessarily equals to last row
value in the same select without 'ORDER BY [ID]'.Kurzman (max@.virtuman.com) writes:
> DECLARE @.LastID int
> DECLARE @.RowsToSelect int
> SET ROWCOUNT @.RowsToSelect
> SELECT @.LastID = [id] FROM MyTable ORDER BY [id]
> SET ROWCOUNT 0
> I think that value of @.LastID will be equals to [id] in the last row
> of select(i.e. row with number @.RowsToSelect). But I don't understand
> why it's true.

Books Online says:

If the SELECT statement returns more than one value, the variable is
assigned the last value returned.

So your observation is correct.

> For example, @.LastID not necessarily equals to last row value in the same
> select without 'ORDER BY [ID]'.

How do you know that it is not the last value? Without an ORDER BY
clause, SQL Server is free to return the rows in any order, so any value
of [id] is correct. Recall that logically tables are unordered sets of
data, so there is no first or last value in a table.

You must always specify an ORDER BY clause to impose a certain order.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Last Row Got Stuck

Hello,

I have a small table to manage orders in my company. When a new order is entered, the script makes use of the last row of the table to find out the last order, increments it and creates the new order number. The problem is, a few days ago the last row got stuck. New insertions to the table all got the same order number and are placed above the last row. Anybody has any idea what's going on?hey
do you have multiple concurrent applciations accesing the table?
any reason you are doing the incremting 'manually' instead of using an auto-inc int?
des|||Hello Des,

Below is the protion of the ASP code that assigns the ordernumber. It is in the form "TPD-mmm-nnn" where mmm is the mont (like 002 for Feb) and nnn is the order number (like 001,002,etc). The if clause tries to reset the order number back to 001 if the month starts anew.

...
set rs = Server.CreateObject("ADODB.recordset")
rs.CursorType = 3
selectstr="SELECT * FROM konuttekliflist"
rs.open selectstr, conn
rs.movelast
tekno=rs.fields("teklifno")
tekay=mid(tekno,5,3)
teklifindex=right("00" & right(tekno,4)+1,3)
monthnow=right("00" & month(now()),3)
if tekay=monthnow then
teklifno1="TPD-" & monthnow & "-" & teklifindex
else
teklifno1="TPD-" & monthnow & "-001"
end if
sql="INSERT INTO konuttekliflist ("
...

And yes, the application is used concurrently by 4 people.

Thanks to your interest and help.|||So ID is generated with datenow...are the four apps runnning on sep machines to db server & eachother? are all their dates/times
in perfect sync? otherwise this could cause problems.

If two orders come in at the same time, they could potentially conflict?

i see you select all into a recordset - this could cause locks that conflict with your other apps. why not select(max) substring(mmm)+subsrting(nnn) into local variable, instead of iterating through recordset?
How does it cross over the year?

how about generating the ID in sql with a computed column, using its own date and increment, then at least you dont have four different things competing for the next value? ie.gen id's centrally

des

LAst order date

---

I have a fact table that contains amongst other facts, the orderdate. There is 1 line per order.
I would like to show for each "sold from BP", the last order date, like i do in the example below for the "Transaction amount".
Can anyone give me a hint on how the MDX syntaxt should be?

select NON EMPTY {[Measures].[Transaction amount (EURO)]} ON COLUMNS,
NON EMPTY {[sold from BP.BP(ID)].[All sold from BP(ID)]} ON ROWS
from [finalizedtransactionscube]
where [Transaction type.Transaction type].[PCI+PCC]

Hopefully this gives you an idea on how to proceed with this:

Code Snippet

withmember [measures].[x] as

MAX(

EXISTS(

[Ship Date].[Date].[Date].Members,

{Product.SubCategory.CurrentMember},

'Internet Sales'

),

[Ship Date].[Date].CurrentMember.MemberValue

)

select

[x] on 0,

TAIL(Product.SubCategory.SubCategory.Members,10) on 1

from [Adventure Works]

I use TAIL on Axis(1) to just limit the data returned. The calculated member at the top is where all the work is performed.

What this says is build a set of ship date members associated with the current product subcategory based on the relationship between these two dimensions as defined in the Internet Sales measure group. Take the max value for ship date from this set and return that as the measure's value. The MAX() function was the tricky part.

Good luck,
Bryan

Last Modified Date of the Database Object ?

Dear All,
In order to get a version control.
I would like to get the last modified date of the Store Procedure, Table De
finition
I find the the [sysobjects] Table ONLY contains a Create Date.
How can i find it.
Do SQL Server remember the [ALTER] and [RECOMPILE] time of these objects
Thank you very much
EricLunCurrent versions of SQL Server do not track modifications to objects, only
creation. You will have to track these yourself, or use a third party
product to do so. Your version control strategy, by the way, should not
rely on a feature of the product. In other words, if you were using version
control tools, you should have alter scripts stored in your source control
tool, without having to look at the database (other than to verify that what
is deployed exists in source control, or vice versa).
http://www.aspfaq.com/2495
Yukon will offer DDL triggers, but that's several months away, at least.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"EricLun >" <eric_klyuen@.yahoo.com.hk <no spam pls> wrote in message
news:DCE9AA09-6AF7-4BFD-A576-6D730BD4D79B@.microsoft.com...

quote:

---

> Dear All,
> In order to get a version control.
> I would like to get the last modified date of the Store Procedure, Table

---

Definition

quote:

---

> I find the the [sysobjects] Table ONLY contains a Create Date.
> How can i find it.
> Do SQL Server remember the [ALTER] and [RECOMPILE] time of these objects
> Thank you very much
> EricLun

---

|||Thank you very much form your help.

Last Modified Date of the Database Object ?

Dear All,
In order to get a version control.
I would like to get the last modified date of the Store Procedure, Table Definition
I find the the [sysobjects] Table ONLY contains a Create Date.
How can i find it.
Do SQL Server remember the [ALTER] and [RECOMPILE] time of these objects
Thank you very much
EricLunCurrent versions of SQL Server do not track modifications to objects, only
creation. You will have to track these yourself, or use a third party
product to do so. Your version control strategy, by the way, should not
rely on a feature of the product. In other words, if you were using version
control tools, you should have alter scripts stored in your source control
tool, without having to look at the database (other than to verify that what
is deployed exists in source control, or vice versa).
http://www.aspfaq.com/2495
Yukon will offer DDL triggers, but that's several months away, at least.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"EricLun >" <eric_klyuen@.yahoo.com.hk <no spam pls> wrote in message
news:DCE9AA09-6AF7-4BFD-A576-6D730BD4D79B@.microsoft.com...
> Dear All,
> In order to get a version control.
> I would like to get the last modified date of the Store Procedure, Table
Definition
> I find the the [sysobjects] Table ONLY contains a Create Date.
> How can i find it.
> Do SQL Server remember the [ALTER] and [RECOMPILE] time of these objects
> Thank you very much
> EricLun|||Thank you very much form your help.

last 30 dates measures

Hi all,

I want to do a top / order by query like

select top 30 f.measure, d.date
from fact f
join date_dim d on f.date_key = f.date_key
group by d.date
order by d.date desc

you get the picture,

in MDX.

I know I can use now() but how do I get the dd/mm/yyyy format and use strtomember to put this on the rows axis?

also, I cannot get order() to order the dates axis (or any other dimension for that matter)

any ideas?

Thanks

Hi,

To convert the current date, you can use something like

StrToMember("[Period].[Calendar].[Day].&[" + format(DateAdd("d",-1,now()),"yyyyMMdd") +"]")

"[Period].[Calendar].[Day].&[" - represents the structure of your date dimension.

To get a 30 dates from the current date use something like -

WITHSET [30 Dates] AS {StrToMember("[Period].[Calendar].[Day].&[" + format(DateAdd("d",-1,now()),"yyyyMMdd") +"]"):StrToMember("[Period].[Calendar].[Day].&[" + format(DateAdd("d",-1,now()),"yyyyMMdd") +"]").lag(30)}

SELECT [Measures].[Value] on COLUMNS,

[30 Dates] on ROWS

FROM [CUBE]

Note, this query is only showing you, the measure you are interested in for the last 30 days.

Does this help?

When you want to order the dates, what exactly do you mean? Do you want to order them by a certain property or the value of your measures.

Thanks

Punita