Showing posts with label time. Show all posts
Showing posts with label time. Show all posts

Thursday, March 29, 2012

Distributed Partitions (Across multiple servers)

I have an existing table that I want to partition across multiple servers. I am having a hard time finding an article that explains how to do it.

I am in the process of designing applying a scale out architecture to our database...but have hit this brick wall.

Any help would be great.

Thanks!

Eric Elliston

SWFLParent.com

http://www.swflparent.com

Hi Eric,

I think you're thinking of distributed partitioned views. Have a look at http://msdn2.microsoft.com/en-us/library/aa479364.aspx which actually presents a few options available to you.

Cheers,

Rob

Distributed Partioned Views between different SS versions

Can you have a updatable Distributed Partioned Views between SQL
Server 2000 and SQL Server 2005 ? The reason I ask is the down time to
upgrade all 2000 sql servers to 2005 at the same time is prohibitive in
our environment.
Nevermind, I have tested and confirmed this works.

Distributed Partioned Views between different SS versions

Can you have a updatable Distributed Partioned Views between SQL
Server 2000 and SQL Server 2005 ? The reason I ask is the down time to
upgrade all 2000 sql servers to 2005 at the same time is prohibitive in
our environment.Nevermind, I have tested and confirmed this works.

Distributed Partioned Views between different SS versions

Can you have a updatable Distributed Partioned Views between SQL
Server 2000 and SQL Server 2005 ? The reason I ask is the down time to
upgrade all 2000 sql servers to 2005 at the same time is prohibitive in
our environment.Nevermind, I have tested and confirmed this works.

Tuesday, March 27, 2012

distribute in a page..

Hello..
I'm for a very long time trying to make somthing, but everytime it doesn't
work..
This is what must happend:
If i have a guestbook with 100 letters, then wath i want is that a script
distribute the letters on a page of 10 end that i have under the letters a
buttun to go to te next letters ( 11-20 ) and a button to go back if you are
not at page one. And in the middle of those 2 buttons i want to see how many
pages with letters there are, and that you kan choose a number of a page to
go to it.
It must show a bit like this
(1) (2) (3) (4) (5) Next page <-- this if you are on the first page
Prevous page (1) (2) (3) (4) (5) next page <-- this if you are on page 2 or
whits one you want..
It must happend with this script:
set rst = cnn.execute("SELECT * FROM tblGastenboek WHERE Gb_Pro_ID_Aan=" &
CheckDBNum(Request("Pro_ID")) & " ORDER BY Gb_ID DESC ")
do until rst.eof
%>
<html>
<head>
<title>Profiel wijzigen</title>
<link rel="stylesheet" type="text/css" href="http://links.10026.com/?link=Style/Default.css">
</head>
<body topmargin="12" leftmargin="10" rightmargin="10" bottommargin="10">
<br>
<center>
<table width="400" cellpadding="0" cellspacing="1" border="0"
bgcolor="#000000">
<tr>
<td bgcolor="#000000" height="15" width="165"><font
color="#FFFFFF"><b>&nbsp;<%=rst("GBNickname")% ></b></font></td>
<td bgcolor="#000000" width="84" align="center"><%
sPro_ID = session("Pro_ID")
if trim(sPro_ID) = trim(request("Pro_ID")) then %>
<a
href="Gastenboek.asp?ACTION=DELETE&ID=<%=rst("GB_I D")%>&Pro_ID=<%=request("Pro_ID")%>"><font
color="#FFFFFF">&nbsp;Verwijderen&nbsp;</font></a>
<%end if%>
</td>
<td bgcolor="#000000" width="147" align="center"><font
color="#FFFFFF"><b><%=rst("GBDatum")%></b></font></td>
</tr>
<tr>
<td bgcolor="white" colspan="3">
<%
Bericht = rst("GBtekst")
If Bericht <> "" then
Bericht = Replace(Bericht, ">", ">")
Bericht = Replace(Bericht, "<", "<")
End if
%>
<%=Bericht%>
</td>
</tr>
</table>
</center>
</body>
</html>
<%
rst.movenext
loop
rst.close
set rst = nothing
Is there sombody how can make it for me in this script.. please don't tell
me wath i can try or can do, it wont work.. Zo please
if you know how to fix it, please make it for me..
If you can help me i wanna thank you very much!
Gr. Robert
Robert
Read these articles
http://www.aspfaq.com/show.asp?id=2120
http://www.aspfaq.com/show.asp?id=2424
"Robert" <Bla@.bla.nl> wrote in message news:dfcot2$beu$1@.news.cistron.nl...
> Hello..
> I'm for a very long time trying to make somthing, but everytime it doesn't
> work..
> This is what must happend:
> If i have a guestbook with 100 letters, then wath i want is that a script
> distribute the letters on a page of 10 end that i have under the letters a
> buttun to go to te next letters ( 11-20 ) and a button to go back if you
> are not at page one. And in the middle of those 2 buttons i want to see
> how many pages with letters there are, and that you kan choose a number of
> a page to go to it.
> It must show a bit like this
> (1) (2) (3) (4) (5) Next page <-- this if you are on the first page
> Prevous page (1) (2) (3) (4) (5) next page <-- this if you are on page 2
> or whits one you want..
> It must happend with this script:
> set rst = cnn.execute("SELECT * FROM tblGastenboek WHERE Gb_Pro_ID_Aan="
> & CheckDBNum(Request("Pro_ID")) & " ORDER BY Gb_ID DESC ")
> do until rst.eof
> %>
> <html>
> <head>
> <title>Profiel wijzigen</title>
> <link rel="stylesheet" type="text/css" href="http://links.10026.com/?link=Style/Default.css">
> </head>
> <body topmargin="12" leftmargin="10" rightmargin="10"
> bottommargin="10">
> <br>
> <center>
> <table width="400" cellpadding="0" cellspacing="1" border="0"
> bgcolor="#000000">
> <tr>
> <td bgcolor="#000000" height="15" width="165"><font
> color="#FFFFFF"><b>&nbsp;<%=rst("GBNickname")% ></b></font></td>
> <td bgcolor="#000000" width="84" align="center"><%
> sPro_ID = session("Pro_ID")
> if trim(sPro_ID) = trim(request("Pro_ID")) then %>
> <a
> href="http://links.10026.com/?link=Gastenboek.asp?ACTION=DELETE&ID=<%=rst("GB_I D")%>&Pro_ID=<%=request("Pro_ID")%>"><font
> color="#FFFFFF">&nbsp;Verwijderen&nbsp;</font></a>
> <%end if%>
> </td>
> <td bgcolor="#000000" width="147" align="center"><font
> color="#FFFFFF"><b><%=rst("GBDatum")%></b></font></td>
> </tr>
> <tr>
> <td bgcolor="white" colspan="3">
> <%
> Bericht = rst("GBtekst")
> If Bericht <> "" then
> Bericht = Replace(Bericht, ">", ">")
> Bericht = Replace(Bericht, "<", "<")
> End if
> %>
> <%=Bericht%>
> </td>
> </tr>
> </table>
> </center>
> </body>
> </html>
> <%
> rst.movenext
> loop
> rst.close
> set rst = nothing
> Is there sombody how can make it for me in this script.. please don't tell
> me wath i can try or can do, it wont work.. Zo please
> if you know how to fix it, please make it for me..
> If you can help me i wanna thank you very much!
> Gr. Robert
>
sql

distribute in a page..

Hello..
I'm for a very long time trying to make somthing, but everytime it doesn't
work..
This is what must happend:
If i have a guestbook with 100 letters, then wath i want is that a script
distribute the letters on a page of 10 end that i have under the letters a
buttun to go to te next letters ( 11-20 ) and a button to go back if you are
not at page one. And in the middle of those 2 buttons i want to see how many
pages with letters there are, and that you kan choose a number of a page to
go to it.
It must show a bit like this
(1) (2) (3) (4) (5) Next page <-- this if you are on the first page
Prevous page (1) (2) (3) (4) (5) next page <-- this if you are on page 2 or
whits one you want..
It must happend with this script:
---
set rst = cnn.execute("SELECT * FROM tblGastenboek WHERE Gb_Pro_ID_Aan=" &
CheckDBNum(Request("Pro_ID")) & " ORDER BY Gb_ID DESC ")
do until rst.eof
%>
<html>
<head>
<title>Profiel wijzigen</title>
<link rel="stylesheet" type="text/css" href="http://links.10026.com/?link=Style/Default.css">
</head>
<body topmargin="12" leftmargin="10" rightmargin="10" bottommargin="10">
<br>
<center>
<table width="400" cellpadding="0" cellspacing="1" border="0"
bgcolor="#000000">
<tr>
<td bgcolor="#000000" height="15" width="165"><font
color="#FFFFFF"><b> <%=rst("GBNickname")%></b></font></td>
<td bgcolor="#000000" width="84" align="center"><%
sPro_ID = session("Pro_ID")
if trim(sPro_ID) = trim(request("Pro_ID")) then %>
<a
href="Gastenboek.asp?ACTION=DELETE&ID=<%=rst("GB_ID")%>&Pro_ID=<%=request("P
ro_ID")%>"><font
color="#FFFFFF"> Verwijderen </font></a>
<%end if%>
</td>
<td bgcolor="#000000" width="147" align="center"><font
color="#FFFFFF"><b><%=rst("GBDatum")%></b></font></td>
</tr>
<tr>
<td bgcolor="white" colspan="3">
<%
Bericht = rst("GBtekst")
If Bericht <> "" then
Bericht = Replace(Bericht, ">", ">")
Bericht = Replace(Bericht, "<", "<")
End if
%>
<%=Bericht%>
</td>
</tr>
</table>
</center>
</body>
</html>
<%
rst.movenext
loop
rst.close
set rst = nothing
---
Is there sombody how can make it for me in this script.. please don't tell
me wath i can try or can do, it wont work.. Zo please
if you know how to fix it, please make it for me..
If you can help me i wanna thank you very much!
Gr. RobertRobert
Read these articles
http://www.aspfaq.com/show.asp?id=2120
http://www.aspfaq.com/show.asp?id=2424
"Robert" <Bla@.bla.nl> wrote in message news:dfcot2$beu$1@.news.cistron.nl...
> Hello..
> I'm for a very long time trying to make somthing, but everytime it doesn't
> work..
> This is what must happend:
> If i have a guestbook with 100 letters, then wath i want is that a script
> distribute the letters on a page of 10 end that i have under the letters a
> buttun to go to te next letters ( 11-20 ) and a button to go back if you
> are not at page one. And in the middle of those 2 buttons i want to see
> how many pages with letters there are, and that you kan choose a number of
> a page to go to it.
> It must show a bit like this
> (1) (2) (3) (4) (5) Next page <-- this if you are on the first page
> Prevous page (1) (2) (3) (4) (5) next page <-- this if you are on page 2
> or whits one you want..
> It must happend with this script:
> ---
> set rst = cnn.execute("SELECT * FROM tblGastenboek WHERE Gb_Pro_ID_Aan="
> & CheckDBNum(Request("Pro_ID")) & " ORDER BY Gb_ID DESC ")
> do until rst.eof
> %>
> <html>
> <head>
> <title>Profiel wijzigen</title>
> <link rel="stylesheet" type="text/css" href="http://links.10026.com/?link=Style/Default.css">
> </head>
> <body topmargin="12" leftmargin="10" rightmargin="10"
> bottommargin="10">
> <br>
> <center>
> <table width="400" cellpadding="0" cellspacing="1" border="0"
> bgcolor="#000000">
> <tr>
> <td bgcolor="#000000" height="15" width="165"><font
> color="#FFFFFF"><b> <%=rst("GBNickname")%></b></font></td>
> <td bgcolor="#000000" width="84" align="center"><%
> sPro_ID = session("Pro_ID")
> if trim(sPro_ID) = trim(request("Pro_ID")) then %>
> <a
> href="http://links.10026.com/?link=Gastenboek.asp?ACTION=DELETE&ID=<%=rst("GB_ID")%>&Pro_ID=<%=request(
"Pro_ID")%>"><font
> color="#FFFFFF"> Verwijderen </font></a>
> <%end if%>
> </td>
> <td bgcolor="#000000" width="147" align="center"><font
> color="#FFFFFF"><b><%=rst("GBDatum")%></b></font></td>
> </tr>
> <tr>
> <td bgcolor="white" colspan="3">
> <%
> Bericht = rst("GBtekst")
> If Bericht <> "" then
> Bericht = Replace(Bericht, ">", ">")
> Bericht = Replace(Bericht, "<", "<")
> End if
> %>
> <%=Bericht%>
> </td>
> </tr>
> </table>
> </center>
> </body>
> </html>
> <%
> rst.movenext
> loop
> rst.close
> set rst = nothing
> ---
> Is there sombody how can make it for me in this script.. please don't tell
> me wath i can try or can do, it wont work.. Zo please
> if you know how to fix it, please make it for me..
> If you can help me i wanna thank you very much!
> Gr. Robert
>

distribute in a page..

Hello..
I'm for a very long time trying to make somthing, but everytime it doesn't
work..
This is what must happend:
If i have a guestbook with 100 letters, then wath i want is that a script
distribute the letters on a page of 10 end that i have under the letters a
buttun to go to te next letters ( 11-20 ) and a button to go back if you are
not at page one. And in the middle of those 2 buttons i want to see how many
pages with letters there are, and that you kan choose a number of a page to
go to it.
It must show a bit like this
(1) (2) (3) (4) (5) Next page <-- this if you are on the first page
Prevous page (1) (2) (3) (4) (5) next page <-- this if you are on page 2 or
whits one you want..
It must happend with this script:
---
set rst = cnn.execute("SELECT * FROM tblGastenboek WHERE Gb_Pro_ID_Aan=" &
CheckDBNum(Request("Pro_ID")) & " ORDER BY Gb_ID DESC ")
do until rst.eof
%>
<html>
<head>
<title>Profiel wijzigen</title>
<link rel="stylesheet" type="text/css" href="http://links.10026.com/?link=Style/Default.css">
</head>
<body topmargin="12" leftmargin="10" rightmargin="10" bottommargin="10">
<br>
<center>
<table width="400" cellpadding="0" cellspacing="1" border="0"
bgcolor="#000000">
<tr>
<td bgcolor="#000000" height="15" width="165"><font
color="#FFFFFF"><b> <%=rst("GBNickname")%></b></font></td>
<td bgcolor="#000000" width="84" align="center"><%
sPro_ID = session("Pro_ID")
if trim(sPro_ID) = trim(request("Pro_ID")) then %>
<a
href="http://links.10026.com/?link=Gastenboek.asp?ACTION=DELETE&ID=<%=rst("GB_ID")%>&Pro_ID=<%=request("Pro_ID")%>"><font
color="#FFFFFF"> Verwijderen </font></a>
<%end if%>
</td>
<td bgcolor="#000000" width="147" align="center"><font
color="#FFFFFF"><b><%=rst("GBDatum")%></b></font></td>
</tr>
<tr>
<td bgcolor="white" colspan="3">
<%
Bericht = rst("GBtekst")
If Bericht <> "" then
Bericht = Replace(Bericht, ">", ">")
Bericht = Replace(Bericht, "<", "<")
End if
%>
<%=Bericht%>
</td>
</tr>
</table>
</center>
</body>
</html>
<%
rst.movenext
loop
rst.close
set rst = nothing
---
Is there sombody how can make it for me in this script.. please don't tell
me wath i can try or can do, it wont work.. Zo please
if you know how to fix it, please make it for me..
If you can help me i wanna thank you very much!
Gr. RobertRobert
Read these articles
http://www.aspfaq.com/show.asp?id=2120
http://www.aspfaq.com/show.asp?id=2424
"Robert" <Bla@.bla.nl> wrote in message news:dfcot2$beu$1@.news.cistron.nl...
> Hello..
> I'm for a very long time trying to make somthing, but everytime it doesn't
> work..
> This is what must happend:
> If i have a guestbook with 100 letters, then wath i want is that a script
> distribute the letters on a page of 10 end that i have under the letters a
> buttun to go to te next letters ( 11-20 ) and a button to go back if you
> are not at page one. And in the middle of those 2 buttons i want to see
> how many pages with letters there are, and that you kan choose a number of
> a page to go to it.
> It must show a bit like this
> (1) (2) (3) (4) (5) Next page <-- this if you are on the first page
> Prevous page (1) (2) (3) (4) (5) next page <-- this if you are on page 2
> or whits one you want..
> It must happend with this script:
> ---
> set rst = cnn.execute("SELECT * FROM tblGastenboek WHERE Gb_Pro_ID_Aan="
> & CheckDBNum(Request("Pro_ID")) & " ORDER BY Gb_ID DESC ")
> do until rst.eof
> %>
> <html>
> <head>
> <title>Profiel wijzigen</title>
> <link rel="stylesheet" type="text/css" href="http://links.10026.com/?link=Style/Default.css">
> </head>
> <body topmargin="12" leftmargin="10" rightmargin="10"
> bottommargin="10">
> <br>
> <center>
> <table width="400" cellpadding="0" cellspacing="1" border="0"
> bgcolor="#000000">
> <tr>
> <td bgcolor="#000000" height="15" width="165"><font
> color="#FFFFFF"><b> <%=rst("GBNickname")%></b></font></td>
> <td bgcolor="#000000" width="84" align="center"><%
> sPro_ID = session("Pro_ID")
> if trim(sPro_ID) = trim(request("Pro_ID")) then %>
> <a
> href="http://links.10026.com/?link=Gastenboek.asp?ACTION=DELETE&ID=<%=rst("GB_ID")%>&Pro_ID=<%=request("Pro_ID")%>"><font
> color="#FFFFFF"> Verwijderen </font></a>
> <%end if%>
> </td>
> <td bgcolor="#000000" width="147" align="center"><font
> color="#FFFFFF"><b><%=rst("GBDatum")%></b></font></td>
> </tr>
> <tr>
> <td bgcolor="white" colspan="3">
> <%
> Bericht = rst("GBtekst")
> If Bericht <> "" then
> Bericht = Replace(Bericht, ">", ">")
> Bericht = Replace(Bericht, "<", "<")
> End if
> %>
> <%=Bericht%>
> </td>
> </tr>
> </table>
> </center>
> </body>
> </html>
> <%
> rst.movenext
> loop
> rst.close
> set rst = nothing
> ---
> Is there sombody how can make it for me in this script.. please don't tell
> me wath i can try or can do, it wont work.. Zo please
> if you know how to fix it, please make it for me..
> If you can help me i wanna thank you very much!
> Gr. Robert
>

Sunday, March 25, 2012

distinct quey

I am using the below:
datediff(hour,b.[Time Finish], b.[Time Start]
to calculate the difference between the Finish time and start time. However,
I would like to do so only for distinct Time finish and start values.
Table: Schedule
S.No Time St. Time fin. SID# Date
1 10:00 11:00 1234 1-dec-04
2 10:00 11:00 1235 1-dec-04
3 01:00 02:00 1236 1-dec-04
The table design is very bad (the SIDs should have been in a separate table
referencing one distinct row of schedule from the schedule table) but I am
stuck with this.
Is there a way I can get it to return:
2 for 1-dec-04 (2 because 11-10 =1 and 2-1 =1)
distinct datediff(hour,b.[Time Finish], b.[Time Start]
returns only 1 because "1" is the distinct value.
I would like to be able to do something like:
datediff(hour,distinct b.[Time Finish],distinct b.[Time Start])
which is wrong.
Can somebody please throw some light? Hope this is clear.
Thanks a lot!
VinceYou could select from a derived table:
select datediff(hour,[Time Finish], [Time Start]
from (select distinct [Time Finish], [Time Start] from [table]) AS d
"Vince" wrote:

> I am using the below:
> datediff(hour,b.[Time Finish], b.[Time Start]
> to calculate the difference between the Finish time and start time. Howeve
r,
> I would like to do so only for distinct Time finish and start values.
> Table: Schedule
> S.No Time St. Time fin. SID# Date
> 1 10:00 11:00 1234 1-dec-04
> 2 10:00 11:00 1235 1-dec-04
> 3 01:00 02:00 1236 1-dec-04
> The table design is very bad (the SIDs should have been in a separate tabl
e
> referencing one distinct row of schedule from the schedule table) but I am
> stuck with this.
> Is there a way I can get it to return:
> 2 for 1-dec-04 (2 because 11-10 =1 and 2-1 =1)
> distinct datediff(hour,b.[Time Finish], b.[Time Start]
> returns only 1 because "1" is the distinct value.
> I would like to be able to do something like:
> datediff(hour,distinct b.[Time Finish],distinct b.[Time Start])
> which is wrong.
> Can somebody please throw some light? Hope this is clear.
> Thanks a lot!
> Vince
>
>|||Thanks! Didn't know you could do that.
"KH" <KH@.discussions.microsoft.com> wrote in message
news:66A5062F-5144-4364-9509-0294EAAFA985@.microsoft.com...
> You could select from a derived table:
> select datediff(hour,[Time Finish], [Time Start]
> from (select distinct [Time Finish], [Time Start] from [table]) AS d
>
> "Vince" wrote:
>
However,
table
am

Distinct insert

I am trying to insert a record id primary at the same time that I am trying
to insert a Distinct Account number.
The insert statement is something kind of like this:
insert into Accounts (AccountsOID,Account)
select Distinct "Rec NO" + 100 as AccountsOID, "LIC TAG" as Account from
AutoExpt
In the AutoExpt table there are multiple occurrences of "LIC TAG" values all
of which need to be put into the Accounts table, however there must be only
one entry for the Lic tag in the accounts table. The Rec No +100 is just to
create a unique primary key for the AccountsOID.
Is there anyway I can get only the Distinct Lic Tag's, but at the same time
get a unique primary key for them.
Thanks,
On Wed, 25 Aug 2004 12:55:03 -0700, Robert wrote:

>I am trying to insert a record id primary at the same time that I am trying
>to insert a Distinct Account number.
>The insert statement is something kind of like this:
>insert into Accounts (AccountsOID,Account)
>select Distinct "Rec NO" + 100 as AccountsOID, "LIC TAG" as Account from
>AutoExpt
>In the AutoExpt table there are multiple occurrences of "LIC TAG" values all
>of which need to be put into the Accounts table, however there must be only
>one entry for the Lic tag in the accounts table. The Rec No +100 is just to
>create a unique primary key for the AccountsOID.
>Is there anyway I can get only the Distinct Lic Tag's, but at the same time
>get a unique primary key for them.
>Thanks,
Hi Robert,
Please provide DDL (CREATE TABLE statements, with constraints), sample
data (as INSERT statements), expected output and a description of the
business problem you're trying to solve.
http://www.aspfaq.com/etiquette.asp?id=5006
http://vyaskn.tripod.com/code.htm#inserts
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Unfortunately I do not have the create table SQL statements
What I am trying to accomplish is importing data from an excel file into a
SQL database. To make it easier to work with I simply straight imported the
excel file into a new table in the SQL database (AutoExpt). Accounts is one
of the tables that I need to import into. I do know that the OID for the
accounts table is not automatically generated, and must be generated manually.
In the AutoExpt table there is a listing of vechiles and what accounts
maintenance should be charged to. I can add the vechiles fine to the
Equipment table (Not listed here because it works just fine). However another
table EquipmentFeature is linked to the Equipment table. It contains things
such as account numbers, and the department that the vechile belongs to.
From what I can tell I can not add records to the Equipment feature table
such as the account number because they do not exist in the Accounts table.
So the solution is to first add all the distinct account numbers into the
accounts table. This also applies to the departments table as well.
I do have a backup of the database so I can fool aroudn with it all I want
without worry of data loss or damage, plus I am working on a dev server so no
actual users are accessing the database. I found that I can add duplicate
records in the accounts table and the departments table, however I have found
that I can't remove some duplicates in the departments table because it
claims that a record needs to exist in the General table first.
My theory is that if I add only the distinct account numbers and departments
I should be fine, and it should stop telling me that "A record must exist in
the accounts table first"
Unfortunately the company that makes the software is not offering much
support on importing the data. So I am left to figure it out myself.
"Hugo Kornelis" wrote:

> On Wed, 25 Aug 2004 12:55:03 -0700, Robert wrote:
>
> Hi Robert,
> Please provide DDL (CREATE TABLE statements, with constraints), sample
> data (as INSERT statements), expected output and a description of the
> business problem you're trying to solve.
> http://www.aspfaq.com/etiquette.asp?id=5006
> http://vyaskn.tripod.com/code.htm#inserts
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>
|||On Thu, 26 Aug 2004 05:55:04 -0700, Robert wrote:

>Unfortunately I do not have the create table SQL statements
(snip)
Hi Robert,
Did you read the links I posted in my message? They contain instructions
on how you can generate the create table statements from the existing
tables (the ewb site shows how it's done with Query Analyzer; there's an
option to generate SQL in Enterprise Manager as well).
I read your description, but I can't figure out what you're trying to do
and what problems you are trying to tackle. I really need the table
structure and sample data to understand your problem.
(BTW, if therre are legal or other issues that prohibit you from posting
the table structure, you may also create a fake table that recreates the
problem).
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||I can't post the structure or data because it's against our policies. I can
provide you something that looks similair. I basically need to be able to
insert only the unique account numbers from one table into another table,
however at the same time inserting a unique primary key into the same
destination table.
For example I kinda have something like this:
Primary Key From Table 1, Account Number
1,A
2,A
3,B
4,C
5,B
I need to put that information in a seperate table that is similair to this:
Primary From table2, Account Number
-564564,K
564578,D
7246,S
54567,F
The second table can not have any duplicate account numbers and of course
the Primary key.
What I want to do is select from table1 something looking like this:
Primary Key From Table 1, Account Number
1,A
3,B
4,C
Leaving me only with unique primary keys and account numbers.
The end result for table2 should look something like this:
-564564,K
564578,D
7246,S
54567,F
1,A
3,B
4,C
"Hugo Kornelis" wrote:

> On Thu, 26 Aug 2004 05:55:04 -0700, Robert wrote:
> (snip)
> Hi Robert,
> Did you read the links I posted in my message? They contain instructions
> on how you can generate the create table statements from the existing
> tables (the ewb site shows how it's done with Query Analyzer; there's an
> option to generate SQL in Enterprise Manager as well).
> I read your description, but I can't figure out what you're trying to do
> and what problems you are trying to tackle. I really need the table
> structure and sample data to understand your problem.
> (BTW, if therre are legal or other issues that prohibit you from posting
> the table structure, you may also create a fake table that recreates the
> problem).
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>
|||On Thu, 26 Aug 2004 08:39:12 -0700, Robert wrote:

>I can't post the structure or data because it's against our policies. I can
>provide you something that looks similair. I basically need to be able to
>insert only the unique account numbers from one table into another table,
>however at the same time inserting a unique primary key into the same
>destination table.
>For example I kinda have something like this:
>Primary Key From Table 1, Account Number
>1,A
>2,A
>3,B
>4,C
>5,B
>I need to put that information in a seperate table that is similair to this:
>Primary From table2, Account Number
>-564564,K
>564578,D
>7246,S
>54567,F
>The second table can not have any duplicate account numbers and of course
>the Primary key.
>What I want to do is select from table1 something looking like this:
>Primary Key From Table 1, Account Number
>1,A
>3,B
>4,C
>Leaving me only with unique primary keys and account numbers.
>The end result for table2 should look something like this:
>-564564,K
>564578,D
>7246,S
>54567,F
>1,A
>3,B
>4,C
Hi Robert,
The following is untested as I only test solutions I provide in newsgroups
if I can copy and paste CREATE TABLE and INSERT statements. I like solving
problems and helping people, but I detest typing - I have to do plenty of
typing in my job :-)
But try if this gives you what you need:
INSERT INTO table2 (PrimKeyFromTable2, AccountNumber)
SELECT MIN(PrimKeyFromTable1), AccountNumber
FROM Table1
GROUP BY AccountNumber
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||That did the trick, I should have thought about grouping them.
Hugo... Your a genius!
Thanks,
"Hugo Kornelis" wrote:

> On Thu, 26 Aug 2004 08:39:12 -0700, Robert wrote:
>
> Hi Robert,
> The following is untested as I only test solutions I provide in newsgroups
> if I can copy and paste CREATE TABLE and INSERT statements. I like solving
> problems and helping people, but I detest typing - I have to do plenty of
> typing in my job :-)
> But try if this gives you what you need:
> INSERT INTO table2 (PrimKeyFromTable2, AccountNumber)
> SELECT MIN(PrimKeyFromTable1), AccountNumber
> FROM Table1
> GROUP BY AccountNumber
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>

Wednesday, March 21, 2012

Displaying various Time Intelligence calculations at the same time

I created a new calculation for a measure to display Year over Year growth % using the business intelligence wizard which created new calculated members in my time dimension. How can I display the new calculation and the original value at the same time without these two options being columns or rows? For example:

2005

Sales Sales Year over Year Growth %

Product A $23 1%

Product B $45 15%

I have tried to do this using perspectives but these are not included in the 'Standard Edition' SKU. Thanks in advance.

Here is an example that should help you (from Foodmart 2000)

with
member [Measures].[Prev Sales] as '([Measures].[Unit Sales],[Time].currentmember.lag(1))'
member [Measures].[% Increase] as 'iif([Measures].[Prev Sales]<>0,100*([Measures].[Unit Sales]-[Measures].[Prev Sales]) / [Measures].[Prev Sales],0)'

select crossjoin({[Time].[1997].[Q3],[Time].[1997].[Q4]},{[Measures].[Unit Sales],[Measures].[% Increase]}) on columns,
{[Product].[Product Name].members} on rows
from sales

Hope this helps,

Santi

Monday, March 19, 2012

Displaying the time in hh:mm PM

Hi all
How display the time in requried format
example acutal time is 3:15:23PM
requried format is 3:15Pm how to display in that format please give the reply for our requrimentin formula field :

currenttime - second (currenttime)

Displaying Series Group Total for a RS 2005 Chart

Hi there,

I'm trying to create a chart in RS 2005 that will split one set of
data into two separate lines while at the same time, creating a line
that will also display the average of all records. Some background
may be useful:

TABLE

Student_IdAttended_Meeting_CountTest_ScoreTest_ID11090125851315011109522580231652

What I would like to do is create 1 line that shows the average test score for all students with the test id as the x-axis value, 1 line that shows the average test score for all students who attended at least 5 meetings, and 1 line that does the same for all students who attended less than 5 meetings. In the Values box, I am using "=cint(AVG(Fields!Test_Score.Value))", and I have set a series group with the group on expression as "=Fields!Attended_Meetings_Count.Value > 5". This works great for splitting the line representing all values into the two lines I want, however, the test_score for all students line goes away. Anyone have any ideas on how to generate this line again? I've tried adding it as another value, but the series group then applies itself to the new values line as well and I end up with 4 lines.

Help!
-Rich

Hi,

From your description, it seems that you want to show three average values in three different conditions, right?

Why don't you calculate these three values separately? See the following sample:

=CINT(AVG(IIF(Fields!Attended_Meetings_Count.Value > 5, Fields!Test_Score.Value,0)))

=CINT(AVG(IIF(Fields!Attended_Meetings_Count.Value < 5, Fields!Test_Score.Value,0)))

=CINT(AVG(Fields!Test_Score.Value,0))

Thanks.

|||

Hi there,

Thanks for the reply. I did try the values you had suggested above, however, I get the following error when trying to view the preview:

The Value expression for the chart 'chart1' has a scope parameter that is not valid for an aggregate function. The scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a data set.

Any other suggestions?

Thanks again!

Rich

Displaying selected rows from a Fact table

I have a fact table which stores data ( customer name, document type, editing start time, editing end time, editor, revision id etc) for each revision of a document.

While displaying data however i need to take into account only the last revision of each document.

What is the best way of doing this? Do I need to create a separate dimension table with the document id and max revision id as fields or is there a better way of doing it?

One idea would be to mark Revision dimension as of type Time, and use semiadditive measure LastNonEmptyChild - this will show data for the last revision only.|||

I also need to create calculated members based on the lastnonemptychild. How do I do that?

Eg: for last nonemptychild ie. last revision I need to count the number of records that are of type 'S'

I also need to calculate percentage of records of last revision that are greater than target time and less than target time.....

|||This is very easy to do. Assuming you have attribute called RecordType, you can create calculated measure with|||

In the previous post you mentioned mark Revision dimension of type time. How do I do tht?

Does this also mean tht I should hv a separate dimension for revisions with attributes being documentid and revid and the hierarchy being documentid -> revid ? For the lastnonemptychild aggregation to work? That would mean tht the dimension table would contain as many records as the fact table isnt it?

|||

You don't need to change anything about your revision dimension. I imagine, that it has key attribute having values of 1,2,3,... up to whatever largest revision you think you will have in few years. I don't see the reason to include document id into this dimension - different documents can have same revision - there is no problem with it.

In the dimension editor, simply go to the properties of dimension, and choose the value Time for the property Type.

|||After changing revision dimension's property type to time, how do I use the semiadditive measure last child to sum only the records with the last revision id for the Measure InTAT ( where InTAT is either 1 or 0) ?|||You need to change Aggregation Function for this measure from Sum to LastNonEmptyChild.|||For a calculated measure how do I use the LastNonempty measure and get the sum of records with last revid?|||It is not a calculated measure. It is a real measure. Marking it as LastNonEmpty will cause returning sum of records with last revid.|||I have some calculated measures called TAT Factor, Half TAT etc for which too I need to be able to sum on the lastrevid. How do I do that?|||Make them a real measures, and move whatever expressions you use for them to the Leaves(Revision) inside MDX Script.|||I am new to analysis services. Could you explain what you mean by moving the expressions to the leaves? Should I make calculated columns in the view?

Displaying selected rows from a Fact table

I have a fact table which stores data ( customer name, document type, editing start time, editing end time, editor, revision id etc) for each revision of a document.

While displaying data however i need to take into account only the last revision of each document.

What is the best way of doing this? Do I need to create a separate dimension table with the document id and max revision id as fields or is there a better way of doing it?

One idea would be to mark Revision dimension as of type Time, and use semiadditive measure LastNonEmptyChild - this will show data for the last revision only.|||

I also need to create calculated members based on the lastnonemptychild. How do I do that?

Eg: for last nonemptychild ie. last revision I need to count the number of records that are of type 'S'

I also need to calculate percentage of records of last revision that are greater than target time and less than target time.....

|||This is very easy to do. Assuming you have attribute called RecordType, you can create calculated measure with|||

In the previous post you mentioned mark Revision dimension of type time. How do I do tht?

Does this also mean tht I should hv a separate dimension for revisions with attributes being documentid and revid and the hierarchy being documentid -> revid ? For the lastnonemptychild aggregation to work? That would mean tht the dimension table would contain as many records as the fact table isnt it?

|||

You don't need to change anything about your revision dimension. I imagine, that it has key attribute having values of 1,2,3,... up to whatever largest revision you think you will have in few years. I don't see the reason to include document id into this dimension - different documents can have same revision - there is no problem with it.

In the dimension editor, simply go to the properties of dimension, and choose the value Time for the property Type.

|||After changing revision dimension's property type to time, how do I use the semiadditive measure last child to sum only the records with the last revision id for the Measure InTAT ( where InTAT is either 1 or 0) ?|||You need to change Aggregation Function for this measure from Sum to LastNonEmptyChild.|||For a calculated measure how do I use the LastNonempty measure and get the sum of records with last revid?|||It is not a calculated measure. It is a real measure. Marking it as LastNonEmpty will cause returning sum of records with last revid.|||I have some calculated measures called TAT Factor, Half TAT etc for which too I need to be able to sum on the lastrevid. How do I do that?|||Make them a real measures, and move whatever expressions you use for them to the Leaves(Revision) inside MDX Script.|||I am new to analysis services. Could you explain what you mean by moving the expressions to the leaves? Should I make calculated columns in the view?

Displaying seconds value as [mm]:ss

Hello
I am returning a int value from a database which is for an elasped
time in seconds. I want to convert this to mm:ss, however I dont want
it roll over to hours or days.
I trying something along the lines of:
=String.Format("{0:0:mm:ss}",CDate("0:0:0").AddSeconds(Sum(Fields!Airtime.Value)))
However this still allows values to go to hours/days, just doesn't
display them
So for example a value returned from the database of 19257 (seconds)
should be displayed as 320:57 ([mm]:ss)
This is achived in excel by
19257 / 24 = 802.375 / 60 = 13.37291667 /60 = 0.222881944
Then change the format on the cell to [mm]:ss
Then 320:57 is displayed.
Any ideas would be great
Thanks
DavidYou could create custom function, something like this:
Function Seconds2mmss(ByVal seconds As Integer) As String
Dim ss As Integer = seconds Mod 60
Dim mm As Integer = seconds - ss * 60
Seconds2mmss = String.Format("{0:0}:{1:00}", mm, ss)
End Function
and then call it from textbox expression:
=Code.Seconds2mmss(Sum(Fields!Airtime.Value))
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"David" <david.glasgow@.unison.co.nz> wrote in message
news:9f43b964.0408112015.79b5c9b9@.posting.google.com...
> Hello
> I am returning a int value from a database which is for an elasped
> time in seconds. I want to convert this to mm:ss, however I dont want
> it roll over to hours or days.
> I trying something along the lines of:
> =String.Format("{0:0:mm:ss}",CDate("0:0:0").AddSeconds(Sum(Fields!Airtime.Value)))
> However this still allows values to go to hours/days, just doesn't
> display them
> So for example a value returned from the database of 19257 (seconds)
> should be displayed as 320:57 ([mm]:ss)
> This is achived in excel by
> 19257 / 24 = 802.375 / 60 = 13.37291667 /60 = 0.222881944
> Then change the format on the cell to [mm]:ss
> Then 320:57 is displayed.
> Any ideas would be great
> Thanks
> David|||Thanks for that, the final code that returned the result was:
Function Seconds2mmss(ByVal seconds As Integer) As String
Dim ss As Integer = seconds Mod 60
Dim mm As Integer = (seconds - ss) / 60
Seconds2mmss = String.Format("{0:0}:{1:00}", mm, ss)
End Function
Thanks again for your help

Displaying Reports without Report Manager & Login

I'm having a hard time believing others have not ran into this issue.
Anyway...here it is:
I've got a pretty extensive web app. that we're using SQL Reporting Services
to fullfill the reporting requirements. (This is a shared server in a server
farm...I don't have the capabilities to change security settings on the
reporting server).
So, long story short...I'd like to display the reports to the users a single
report at a time, without the report manager capabilities. I've went through
the rendering process and it would work if that is the only way to do it, but
I'd rather show them the HTML toolbar the comes with reporting services and
let them interact with the report. I realize that I can do this just by
calling a URL but when I do that it asks for a username and password. So I
guess the bottom line question is how can I open the report via the URL
without asking me for the report manager username and password (without using
the reporting services render method).
Thanks in Advance!!This sounds like a security issue between domains/machines etc.
Does the user sign in on machine 1 and then click on a link to go to machine
2 that has a different username/password? If so the user will need to enter
their username/password to get on to machine 2.
There are some work arounds but that may cause security wholes for you.
What is the exact security scenario you have?
Craig
"Nick Stineman" <NickStineman@.discussions.microsoft.com> wrote in message
news:476573EC-A15B-4F36-B6EF-EFB4ABE15E9F@.microsoft.com...
> I'm having a hard time believing others have not ran into this issue.
> Anyway...here it is:
> I've got a pretty extensive web app. that we're using SQL Reporting
> Services
> to fullfill the reporting requirements. (This is a shared server in a
> server
> farm...I don't have the capabilities to change security settings on the
> reporting server).
> So, long story short...I'd like to display the reports to the users a
> single
> report at a time, without the report manager capabilities. I've went
> through
> the rendering process and it would work if that is the only way to do it,
> but
> I'd rather show them the HTML toolbar the comes with reporting services
> and
> let them interact with the report. I realize that I can do this just by
> calling a URL but when I do that it asks for a username and password. So
> I
> guess the bottom line question is how can I open the report via the URL
> without asking me for the report manager username and password (without
> using
> the reporting services render method).
> Thanks in Advance!!|||Exact Situation...
We use a shared web server host to host our web application. We've signed
up for SQL reporting services with the host. From what I can decipher with
my e-mails to the web hosting provider the reporting services resides on a
seperate machine as the website. The reporting services server requires SSL
encryption. It is when I try and access the report manager that I am asked
for a username and password. I've created a rendering capability that spits
my reports out as PDF files...this will work if required...but I'd rather
give the end user the functionality of the HTML toolbar located within
reporting services.
If our hosting provider has made a choice or installation configuration that
is causing issues accessing via a direct URL I guess I just need to know. I
can't believe that Microsoft would roll out a product that required
additional login to simply view a report.
Thanks!!
-Nick
"Craig" wrote:
> This sounds like a security issue between domains/machines etc.
> Does the user sign in on machine 1 and then click on a link to go to machine
> 2 that has a different username/password? If so the user will need to enter
> their username/password to get on to machine 2.
> There are some work arounds but that may cause security wholes for you.
> What is the exact security scenario you have?
> Craig
>
> "Nick Stineman" <NickStineman@.discussions.microsoft.com> wrote in message
> news:476573EC-A15B-4F36-B6EF-EFB4ABE15E9F@.microsoft.com...
> > I'm having a hard time believing others have not ran into this issue.
> > Anyway...here it is:
> >
> > I've got a pretty extensive web app. that we're using SQL Reporting
> > Services
> > to fullfill the reporting requirements. (This is a shared server in a
> > server
> > farm...I don't have the capabilities to change security settings on the
> > reporting server).
> >
> > So, long story short...I'd like to display the reports to the users a
> > single
> > report at a time, without the report manager capabilities. I've went
> > through
> > the rendering process and it would work if that is the only way to do it,
> > but
> > I'd rather show them the HTML toolbar the comes with reporting services
> > and
> > let them interact with the report. I realize that I can do this just by
> > calling a URL but when I do that it asks for a username and password. So
> > I
> > guess the bottom line question is how can I open the report via the URL
> > without asking me for the report manager username and password (without
> > using
> > the reporting services render method).
> >
> > Thanks in Advance!!
>
>|||This is an IIS security issue. I think the hosting provided doesn't allow
anonymous access to the report server.
Is the website your running public domain or do users have to sign in to
that as well?
Craig
"Nick" <Nick@.discussions.microsoft.com> wrote in message
news:AC213DC2-7DAA-4B9A-8688-FB92E22426C2@.microsoft.com...
> Exact Situation...
> We use a shared web server host to host our web application. We've signed
> up for SQL reporting services with the host. From what I can decipher
> with
> my e-mails to the web hosting provider the reporting services resides on a
> seperate machine as the website. The reporting services server requires
> SSL
> encryption. It is when I try and access the report manager that I am
> asked
> for a username and password. I've created a rendering capability that
> spits
> my reports out as PDF files...this will work if required...but I'd rather
> give the end user the functionality of the HTML toolbar located within
> reporting services.
> If our hosting provider has made a choice or installation configuration
> that
> is causing issues accessing via a direct URL I guess I just need to know.
> I
> can't believe that Microsoft would roll out a product that required
> additional login to simply view a report.
> Thanks!!
> -Nick
> "Craig" wrote:
>> This sounds like a security issue between domains/machines etc.
>> Does the user sign in on machine 1 and then click on a link to go to
>> machine
>> 2 that has a different username/password? If so the user will need to
>> enter
>> their username/password to get on to machine 2.
>> There are some work arounds but that may cause security wholes for you.
>> What is the exact security scenario you have?
>> Craig
>>
>> "Nick Stineman" <NickStineman@.discussions.microsoft.com> wrote in message
>> news:476573EC-A15B-4F36-B6EF-EFB4ABE15E9F@.microsoft.com...
>> > I'm having a hard time believing others have not ran into this issue.
>> > Anyway...here it is:
>> >
>> > I've got a pretty extensive web app. that we're using SQL Reporting
>> > Services
>> > to fullfill the reporting requirements. (This is a shared server in a
>> > server
>> > farm...I don't have the capabilities to change security settings on the
>> > reporting server).
>> >
>> > So, long story short...I'd like to display the reports to the users a
>> > single
>> > report at a time, without the report manager capabilities. I've went
>> > through
>> > the rendering process and it would work if that is the only way to do
>> > it,
>> > but
>> > I'd rather show them the HTML toolbar the comes with reporting services
>> > and
>> > let them interact with the report. I realize that I can do this just
>> > by
>> > calling a URL but when I do that it asks for a username and password.
>> > So
>> > I
>> > guess the bottom line question is how can I open the report via the URL
>> > without asking me for the report manager username and password (without
>> > using
>> > the reporting services render method).
>> >
>> > Thanks in Advance!!
>>

Friday, March 9, 2012

Displaying inividual fact details in report body

I have a dimension for account and measures for time taken for editing etc. which provide a sum of minutes taken to edit all documents in each account.

The requirement is on clicking on the account link to be able to get details regarding each document in every account. for each document, I would need to display editor name, time taken, document type etc.

How do I display details that are not an aggregation along the rows but individual details of each document?

I would suggest using the drillthrough mdx command - more details @. BOL: http://msdn2.microsoft.com/en-us/library/ms145964.aspx

Saturday, February 25, 2012

display time for tooltips on reports built in Reportserver

Hi All,

Users Love the tooltips. But the tooltip disappers in very very short time, before they get to read it.

Can any one helpme in cotrolling the lenght of the time, tooltip is displayed.

Regards

Bobba

I closest I could get is ask users to go to

controlpannel>display>appearance>advanced>item>tooltip.

But it only lets me change the font. If any one found a way to control the time the tooltip is display, that would be a lot of help.

Regards

Bobba

display time

How do I display time from
23:00:00 to 11:00pm
or vice versa,
Thanks,
Lam"culam" <culam@.discussions.microsoft.com> wrote in message
news:39CB0644-279D-448A-9389-357871CEBDF1@.microsoft.com...
> How do I display time from
> 23:00:00 to 11:00pm
> or vice versa,
> Thanks,
> Lam
Do you mean how do you format the display of a DATETIME value? Your client
application controls how dates and times are displayed - not SQL Server.
Best to take a look at the features of whatever client you are using.
The only way to have any control over this with SQL Server is to return a
string instead of a DATETIME. That's a poor idea for several reasons but if
you really want to do it take a look at the CONVERT function in Books
Online.
David Portas
SQL Server MVP
--|||Since CONVERT() function return different format for Date, I thought one of
them might get me the desired time format. I tried but no prevail. There is
no Client side on my environment. I just get data from Query Analyzer and
present it to my boss.
Thanks,
Lam
"David Portas" wrote:

> "culam" <culam@.discussions.microsoft.com> wrote in message
> news:39CB0644-279D-448A-9389-357871CEBDF1@.microsoft.com...
> Do you mean how do you format the display of a DATETIME value? Your client
> application controls how dates and times are displayed - not SQL Server.
> Best to take a look at the features of whatever client you are using.
> The only way to have any control over this with SQL Server is to return a
> string instead of a DATETIME. That's a poor idea for several reasons but i
f
> you really want to do it take a look at the CONVERT function in Books
> Online.
> --
> David Portas
> SQL Server MVP
> --
>
>|||you could parse CONVERT results, e.g.
select substring(convert(varchar, getdate(), 100),13,7)
culam wrote:
> Since CONVERT() function return different format for Date, I thought one o
f
> them might get me the desired time format. I tried but no prevail. There
is
> no Client side on my environment. I just get data from Query Analyzer and
> present it to my boss.
> Thanks,
> Lam
> "David Portas" wrote:
>|||"culam" <culam@.discussions.microsoft.com> wrote in message
news:66069841-E6F0-44A3-85AB-D75711D8356C@.microsoft.com...
> Since CONVERT() function return different format for Date, I thought one
> of
> them might get me the desired time format. I tried but no prevail. There
> is
> no Client side on my environment. I just get data from Query Analyzer and
> present it to my boss.
> Thanks,
> Lam
> "David Portas" wrote:
>
CONVERT allows you to format dates as *strings*. DATETIME itself has no
specific formatting. For example:
SELECT CONVERT(VARCHAR(8),CURRENT_TIMESTAMP,14)
The result is a VARCHAR not a DATETIME.
Alternatively, in Query Analyzer select Tools / Options / Connections and
check the option that says "Use regional settings when displaying currency,
number, dates and times." to control how the dates are formatted. Query
Analyzer is a client application like any other but it's not a very good
tool for presentational work. You might be better off investing in a
reporting tool.
David Portas
SQL Server MVP
--

Sunday, February 19, 2012

display record by retrieving parameter value in module

in my report, i have user name, test name, start time, end time, full marks and grade. when user click on "report this test" after finishing a certain test, i want the report displays this record which contains current user, test and result details. my sql query is:

SELECT CYMUSER.UNAME, TEST.TNAME, RESULT.STARTTIME, RESULT.ENDTIME, RESULT.TOTALMARKS, RESULT.GRADE
FROM CYMUSER INNER JOIN
TEST ON CYMUSER.UID = TEST.UID INNER JOIN
RESULT ON CYMUSER.UID = RESULT.UID AND TEST.TID = RESULT.TID
WHERE (RESULT.RID = @.RID)

the @.RID parameter is held in my moduleUser.curRID.

How can i pass the @.RID value to report and let it display the only one record that i want?I created a sqldataadapter and a dataset, set the select command as the query mentioned above, and get the dataset from CrystalReportCurResult.rpt

it seems working. However, when i open the form that contains the report viewer at run time, it asks for server and database name, user name and password for the database. In fact my database is local and no username and password at all.

can anyone help with the problem plz? being waiting for reply for past 5 hrs.|||if i get the field data from the database straight way, it wont ask for anything, but will display all the data in the requried column. If i get field data from Project Data --> ADO.NET Datasets, no matter which dataset i use, it asks for database login details (i.e. server, db, name and pw) at runtime. annoying, help plz!|||You need to use Windows Authentication mode of SQL Server