FLOAT datatype weirdness

Hello - I have the following two sets of very simple SQL
--Example #1
DECLARE @.BaseNum float
SELECT @.BaseNum = RAND(
(DATEPART(mm, GETDATE()) * 100000 )
+ (DATEPART(ss, GETDATE()) * 1000 )
+ DATEPART(ms, GETDATE()) )
print @.BaseNum
go
--Example #2
SELECT RAND(
(DATEPART(mm, GETDATE()) * 100000 )
+ (DATEPART(ss, GETDATE()) * 1000 )
+ DATEPART(ms, GETDATE()) )
go
WHY does example #1 only return approx 4, 5, or 6 digits to the right of the
decimal, while Example #2 returns 17 digits to the right of the decimal. I
have tried everything including CAST as CHAR(20), CONVERT(char(20),@.Basenum)
,
etc.
Can someone please provide some input?
ThanksThe results are different because PRINT implictly casts its output to a
string. Exactly what result do you want? FLOAT is an inexact numeric so if
you want to control the decimal precision you will need to cast it as
NUMERIC for example. On the other hand if you just want to *display* a fixed
number of decimal places then just let the application handle that
formatting client-side.
David Portas
SQL Server MVP
--|||MSSQLServerDeveloper wrote:
> Hello - I have the following two sets of very simple SQL
> --Example #1
> DECLARE @.BaseNum float
> SELECT @.BaseNum = RAND(
> (DATEPART(mm, GETDATE()) * 100000 )
> + (DATEPART(ss, GETDATE()) * 1000 )
> + DATEPART(ms, GETDATE()) )
> print @.BaseNum
> go
> --Example #2
> SELECT RAND(
> (DATEPART(mm, GETDATE()) * 100000 )
> + (DATEPART(ss, GETDATE()) * 1000 )
> + DATEPART(ms, GETDATE()) )
> go
> WHY does example #1 only return approx 4, 5, or 6 digits to the right
> of the decimal, while Example #2 returns 17 digits to the right of
> the decimal. I have tried everything including CAST as CHAR(20),
> CONVERT(char(20),@.Basenum), etc.
> Can someone please provide some input?
> Thanks
I don't see a difference here on those queries you supplied in SQL 2000
SP3a. Float is an inexact data type, so that might have something to do
with it.
What version of SQL Server (and SP) are you using.
David Gugick
Imceda Software
www.imceda.com|||I'm using 2k with sp2.
I solved it defining the variable @.BaseNum decimal(18,17)
"David Gugick" wrote:

> MSSQLServerDeveloper wrote:
> I don't see a difference here on those queries you supplied in SQL 2000
> SP3a. Float is an inexact data type, so that might have something to do
> with it.
> What version of SQL Server (and SP) are you using.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||MSSQLServerDeveloper wrote:
> I'm using 2k with sp2.
> I solved it defining the variable @.BaseNum decimal(18,17)
Why are you only on SP2 and not on SP3a?
David Gugick
Imceda Software
www.imceda.com|||The default conversion from float to string yields only
6 digits of precision, and by using PRINT to display the
result you are using this conversion (as David pointed
out). In the second example, you are using SELECT,
which returns the float to the client for display without
first converting it to a string. Most clients will display
15 or 16 digits of precision for a float.
The actual float value contains the full precision in
each case, but you are only seeing part of it when
you use PRINT.
Be aware that RAND(), when used with a parameter,
is not random, but is completely reproducible and
deterministic. See
http://groups.google.co.uk/groups?q=0.7135733602414355
for more on this.
Steve Kass
Drew University
"MSSQLServerDeveloper" wrote:

> Hello - I have the following two sets of very simple SQL
> --Example #1
> DECLARE @.BaseNum float
> SELECT @.BaseNum = RAND(
> (DATEPART(mm, GETDATE()) * 100000 )
> + (DATEPART(ss, GETDATE()) * 1000 )
> + DATEPART(ms, GETDATE()) )
> print @.BaseNum
> go
> --Example #2
> SELECT RAND(
> (DATEPART(mm, GETDATE()) * 100000 )
> + (DATEPART(ss, GETDATE()) * 1000 )
> + DATEPART(ms, GETDATE()) )
> go
> WHY does example #1 only return approx 4, 5, or 6 digits to the right of t
he
> decimal, while Example #2 returns 17 digits to the right of the decimal.
I
> have tried everything including CAST as CHAR(20), CONVERT(char(20),@.Basenu
m),
> etc.
> Can someone please provide some input?
> Thanks
>

Flat File Source Queston

I want to read the following file using the Flat File Source flow:

10000 Router
20000 Hub
10000 Switch
30000 Server
40000 Harddisk

Spaces are used between the numbers (e.g. 10000) and the following text (e.g. Router). Each line is temrinated by a {CR}{LF} pair. I would normally think of this as a "fixed width" file.

But in the Flat File connection manager, if I use format "Fixed width", the preview shows a row width of 1 character wide (the first row contains the number "1"; the next row contains "0", and so on for the remaining 3 rows.in the first line) Not what I had in mind. How do I setup SSIS to handle this file in intended maner?

TIA,

Barker

P.S. I never had any trouble with this type of file under Sql 2000 DTS. Also, have you noticed the crappy-looking "bitmap" displayed when one wants to click and define columns for a fixed-width file?

I might add that using "Fixed width" format I can successfully parse the columns: "Column 0" contains the digits; "Column 1" contains the spaces between the digits and the text; "Column 2" contains the text itself and "Column 3" contains the <CR><LF> line terminators. I'm sure I'm missing the point as this seems like a lot of work for something that should be very straightforward.

barker

|||

I'm beginning to think that "ragged right" is the preferred format. That format allows for fixed-width columns but assumes that last column has variable width (and is terminated). In other words, ragged right recognizes the line terminator as exactly that and doesn't think of it as another character(s) to be parsed.

Barker

|||

You've got it right. The RaggedRight seems as the most appropriate format for your file.

Thanks.

Flat file destination could not be loaded

Hi,

I have created a package and when i was trying to configure a flat file destination, i am getting the following error:

===================================

The component could not be added to the Data Flow task.
Could not initialize the component. There is a potential problem in the ProvideComponentProperties method. (Microsoft Visual Studio)

===================================

Error at Extract Test Flat File [DTS.Pipeline]: The module containing "component "" (245)" cannot be located, even though it is registered.

===================================

Exception from HRESULT: 0xC0048021 (Microsoft.SqlServer.DTSPipelineWrap)

Program Location:

at Microsoft.SqlServer.Dts.Pipeline.Wrapper.CManagedComponentWrapperClass.ProvideComponentProperties()
at Microsoft.DataTransformationServices.Design.PipelineTaskDesigner.AddNewComponent(String clsid, Boolean throwOnError)

Please advise.

Thanks & Regards,

Deepak

Was it working before? If yes, what has changed in the meantime?

Thanks,

Bob

Flat file destination could not be loaded

Hi,

I have created a package and when i was trying to configure a flat file destination, i am getting the following error:

===================================

The component could not be added to the Data Flow task.
Could not initialize the component. There is a potential problem in the ProvideComponentProperties method. (Microsoft Visual Studio)

===================================

Error at Extract Test Flat File [DTS.Pipeline]: The module containing "component "" (245)" cannot be located, even though it is registered.

===================================

Exception from HRESULT: 0xC0048021 (Microsoft.SqlServer.DTSPipelineWrap)

Program Location:

at Microsoft.SqlServer.Dts.Pipeline.Wrapper.CManagedComponentWrapperClass.ProvideComponentProperties()
at Microsoft.DataTransformationServices.Design.PipelineTaskDesigner.AddNewComponent(String clsid, Boolean throwOnError)

Please advise.

Thanks & Regards,

Deepak

Was it working before? If yes, what has changed in the meantime?

Thanks,

Bob

Flag a record

Given the following record set, how can I "Flag" the first payment record
(Typecode = 'P') so that it has satisfied the "Fire_Fee_Recvd"
In other words when a payment comes in, the billed fire fee amount is to be
applied first (first in,first out) and when subsequent payments come in, I d
o
not want to subtract the amount of the fire fee from the premium recvd
Policy BilledPrem BilledFireFee Pymt Typecode (PremRecvd)
(FFRecvd)
bp 001 2292 22.05 B
0 22.05
bp 001 500 P
477.95 22.05
bp 001 367.77 P
345.72 22.05
The last row needs to read Prem Recvd = 367.77 and FFRecvd = 0
My current logic is
PremRecvd = Pymt - FFRecvd
How can I flag that first pymt record as True for satisfying the fire fee so
that the next record will not subtract if from the pymt?
Thanks!Hi Patrice,
You table is little bit confusing for me ..if you want to flag a field (with
true or false)
you can try like this
-- create a new column 'flag filed'
-- write a trigger with your business requied logic[trigger can be insert or
update based on ur requirement] and to display true or false in newly
inserted Flag column based on the logic .
I dont know how far it is true ..iam also curious to know abt this.
hope this will help you
Kumar
So that ,it will check automatically when you insert or update new columns
"Patrice" wrote:

> Given the following record set, how can I "Flag" the first payment record
> (Typecode = 'P') so that it has satisfied the "Fire_Fee_Recvd"
> In other words when a payment comes in, the billed fire fee amount is to b
e
> applied first (first in,first out) and when subsequent payments come in, I
do
> not want to subtract the amount of the fire fee from the premium recvd
> Policy BilledPrem BilledFireFee Pymt Typecode (PremRecvd)
> (FFRecvd)
> bp 001 2292 22.05 B
> 0 22.05
> bp 001 500 P
> 477.95 22.05
> bp 001 367.77 P
> 345.72 22.05
> The last row needs to read Prem Recvd = 367.77 and FFRecvd = 0
> My current logic is
> PremRecvd = Pymt - FFRecvd
> How can I flag that first pymt record as True for satisfying the fire fee
so
> that the next record will not subtract if from the pymt?
>
> Thanks!

Fixing a expression

Hi,
I have the following expression for one of the text boxes in my report:
=Sum(Fields!totallagtime.Value)/Sum(Fields!openduring.Value)
But now if division by 0 occurs, it shows up as 'NaN' when run, I would like
it to show up as 0 if division by zero occurs.
Any suggestions on how this expression can be modified to do this?
ThanksTry this:
= iif(Fields!openduring.Value = 0, 0, Sum(Fields!totallagtime.Value) /
iif(Fields!openduring.Value = 0, 1, Fields!openduring.Value))
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Spencer23" <Spencer23@.discussions.microsoft.com> wrote in message
news:3C67F551-06F3-4185-A3ED-74E56DD31FB9@.microsoft.com...
> Hi,
> I have the following expression for one of the text boxes in my report:
> =Sum(Fields!totallagtime.Value)/Sum(Fields!openduring.Value)
> But now if division by 0 occurs, it shows up as 'NaN' when run, I would
like
> it to show up as 0 if division by zero occurs.
> Any suggestions on how this expression can be modified to do this?
> Thanks
>|||=Iif(sum(Fields!openduring.Value)=0,0,Sum(Fields!totallagtime.Value)/Sum(Fie
lds!openduring.Value))
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
"Spencer23" <Spencer23@.discussions.microsoft.com> wrote in message
news:3C67F551-06F3-4185-A3ED-74E56DD31FB9@.microsoft.com...
> Hi,
> I have the following expression for one of the text boxes in my report:
> =Sum(Fields!totallagtime.Value)/Sum(Fields!openduring.Value)
> But now if division by 0 occurs, it shows up as 'NaN' when run, I would
like
> it to show up as 0 if division by zero occurs.
> Any suggestions on how this expression can be modified to do this?
> Thanks
>|||Hey Rebert,
Could you explain to me what this is doing in english becaue I have never
used iff before and I am not sure what it is doing.
Thanks
"Robert Bruckner [MSFT]" wrote:
> Try this:
> = iif(Fields!openduring.Value = 0, 0, Sum(Fields!totallagtime.Value) /
> iif(Fields!openduring.Value = 0, 1, Fields!openduring.Value))
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Spencer23" <Spencer23@.discussions.microsoft.com> wrote in message
> news:3C67F551-06F3-4185-A3ED-74E56DD31FB9@.microsoft.com...
> > Hi,
> >
> > I have the following expression for one of the text boxes in my report:
> >
> > =Sum(Fields!totallagtime.Value)/Sum(Fields!openduring.Value)
> >
> > But now if division by 0 occurs, it shows up as 'NaN' when run, I would
> like
> > it to show up as 0 if division by zero occurs.
> >
> > Any suggestions on how this expression can be modified to do this?
> >
> > Thanks
> >
>
>|||Thanks Wayne
"Wayne Snyder" wrote:
> =Iif(sum(Fields!openduring.Value)=0,0,Sum(Fields!totallagtime.Value)/Sum(Fie
> lds!openduring.Value))
>
> --
> 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
> "Spencer23" <Spencer23@.discussions.microsoft.com> wrote in message
> news:3C67F551-06F3-4185-A3ED-74E56DD31FB9@.microsoft.com...
> > Hi,
> >
> > I have the following expression for one of the text boxes in my report:
> >
> > =Sum(Fields!totallagtime.Value)/Sum(Fields!openduring.Value)
> >
> > But now if division by 0 occurs, it shows up as 'NaN' when run, I would
> like
> > it to show up as 0 if division by zero occurs.
> >
> > Any suggestions on how this expression can be modified to do this?
> >
> > Thanks
> >
>
>|||IIF is a function call which essentially gives you similar functionality as
an IF statement - however since IIF is a function it will evaluate all
arguments before it is invoked. Therefore you have to avoid divisions by
zero also in the arguments of the IIF function. This makes it necessary to
write the expression as shown in my previous posting.
More information on IIF is available on MSDN:
http://msdn.microsoft.com/library/en-us/vblr7/html/vafctiif.asp
Among others, all the following VB.NET runtime functions are available for
writing expression in Reporting Services:
http://msdn.microsoft.com/library/en-us/vblr7/html/vaoriVBRuntimeLibraryKeywords.asp
Hope this helps,
Robert
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Spencer23" <Spencer23@.discussions.microsoft.com> wrote in message
news:71BEBB45-521A-4FAC-8D3F-65245709A897@.microsoft.com...
> Hey Rebert,
> Could you explain to me what this is doing in english becaue I have never
> used iff before and I am not sure what it is doing.
> Thanks
> "Robert Bruckner [MSFT]" wrote:
> > Try this:
> > = iif(Fields!openduring.Value = 0, 0, Sum(Fields!totallagtime.Value) /
> > iif(Fields!openduring.Value = 0, 1, Fields!openduring.Value))
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> >
> > "Spencer23" <Spencer23@.discussions.microsoft.com> wrote in message
> > news:3C67F551-06F3-4185-A3ED-74E56DD31FB9@.microsoft.com...
> > > Hi,
> > >
> > > I have the following expression for one of the text boxes in my
report:
> > >
> > > =Sum(Fields!totallagtime.Value)/Sum(Fields!openduring.Value)
> > >
> > > But now if division by 0 occurs, it shows up as 'NaN' when run, I
would
> > like
> > > it to show up as 0 if division by zero occurs.
> > >
> > > Any suggestions on how this expression can be modified to do this?
> > >
> > > Thanks
> > >
> >
> >
> >

Fixed It

Hey all, trying to attach to a database in SQL Server 2k5 Management Studio and I am getting the following Error:
--
TITLE: Microsoft SQL Server Management Studio

Attach database failed for Server '[MYDBSVRHERE]'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Could not find row in sysindexes for database ID 7, object ID 1, index ID 1. Run DBCC CHECKTABLE on sysindexes.
Could not open new database '[MYDBNAMEHERE]'. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 602)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=602&LinkId=20476

--

Does anybody have ANY idea about how to fix? Is this a permissions thing?
Thanks in advance!

So... I am a complete idiot!. I spent a DAY trying to figure out the problem. In the end, I had not installed SQL2k5. I had installed the tools, but they were connecting to SQL 2k.

thanks,

Dork|||Glad to hear you "fixed" the problem!|||

Hi,

my server have SQL Server 2000. I detach a database in this server and copy to my laptop.

I attact that database to SQL Server 2005 and do so many things on it.

some days after, i copy this file from my laptop to my server i try to attact it to SQL server 2000.

the same prolem appear.!!!

Does anyone know HOW TO fix? Thanks!

Fix mistake in fact table

Following is the business problem:

- say there are invoices coming from customers
- those invoices are assigned to accounts
- invoices are assigned to wrong accounts and this is not discovered for months
- users correct the mistakes and the way the source system is built - all invoices are reassigned to the NEW account - NO CHANGE DATE though

So, say they found that the account is wrong and fix mistake that updates invoice records all the way to 8 month ago.

What is the right thing to do?

Our DW already contains all the invoices assigned to wrong account. Create another record and time stamp it? Then add "as of" parameter to reports?

Also, there are aggregate tables created for history - those need to be recalculated.
I'm looking for best practice here, rather than details.

Thanks!According to Ralph Kimball there are two methods to handle correcting facts, physical and logical deletes.
With the physical delete the incorrect record is deleted and the new correct record is added. Any downstream application would then need to recalculated. With the logical delete, you add a flag field to all records, insert the correct records, and mark the incorrect flags as such (ie Deleted = 1 for deleted records, Deleted = 0 for correct records). You may also want to add a DeletedReason field to track the error reasons. Again any downstream application would then need to be modified to look only for correct records.
The logical delete method is the "safe" method, because the DW still contains all pertinent history. The choice between the two is really a function of whether the history needs to be maintained vs the effort to implement the Deleted flag in all downstream apps.
Another choice (although may not have one) is the mechanism to process deleted/corrected facts. You can have either a manual or automated process -- the automated process is only viable if your source system has some type of audit/journal/logging mechanism. Even if your system contains a record of the change, depending on the frequency of the errors you may not want an automated process.
In a perfect world, I would implement a logical delete with a manual correction methodology. If errors occur enough to warrant an automated process, perhaps you ought to focus on the process in the source system causing the errors. Even if I did implement an automated error correction method, I would certainly implement some kind of error correction KPI. That way management can track the number of errors and work to impement better controls.
Although I probably didn't answer your question, I hope I've given you some food for thought.
Larry
|||I think you did. Thank you.

I've been playing with similar ideas. I wanted to see what other people do..so keep posting everyone!

fishing for a clue. to loop or not to loop

I have a table called Tbltimes in an access database that consists of the following fields:

empnum, empname, Tin, Tout, Thrs

what I would like to do is populate a grid view the a select statement that does the following.

display each empname and empnum in a gridview returning only unique values. this part is easy enough. in addition to these values i would also like to count up all the Thrs for each empname and display that sum in the gridview as well. Below is a little better picture of what I’m trying to accomplish.

Tbltimes

|empnum | empname | Tin | Tout | Thrs |

| 1 | john | 2:00PM | 3:00PM |1hr |

| 1 | john | 2:00PM | 3:00PM | 1hr |

| 2 | joe | 1:00PM | 6:00PM | 5hr |

GridView1

| 1 | John | 2hrs |

| 2 | Joe | 5hrs |

im using VWD 2005 for this project and im at a loss as to how to accomplish these results. if someone could just point me in the right direction i could find some material and do the reading.

Post a table structure if this doesn't quite work for you. I will assume that Thrs is actually stored and formatted as Nhr where N is a number.

SELECT empnum, empname,
sum(cast(replace(Thrs,'hr','') as int)) as TotalHours --add hrs on the UI side
FROM tblTimes

If the time isn't calculated, you could easily calculate that too. Remove the replace if it isn't actually formatted.

|||thanks louis, im gonna give this a try tonight some time and ill let ya know if it works out.|||

I think lack of experience and knowledge are standing in my way here but im not able to make this method work for me Louis. i have changed a few things and here is a little better look at what i have atm.

i have a table called tbltimes. it has the following columns.

empname | Tin | Tout |

john | 9:00AM | 5:00PM |

john | 7:00AM | 5:00PM |

joe | 9:00AM | 3:00PM |

what i would like to return on the page is something simular to the following:

John | 8hrs |

Joe | 6hrs |

I can select 1 row and figure the time in hours using the following code:

Dim mins = DateDiff(DateInterval.Minute, timein, timeout)

Lblhours.Text = mins / 60

but i have yet to figure out how to use this on a larger scale such as what im trying to accomplish above.

|||

Instead of just the time, a datetime in sql server includes the date also. This is a good idea for you too in case someone works past midnight for some reason. So I added July 12 to the times, and here is the query:

drop table test
go
create table test
(
empname varchar(10),
timeIn smalldatetime,
timeOut smalldatetime
)

insert into test
select 'john', '20060712 09:00:00', '20060712 17:00:00'
union all
select 'john', '20060712 07:00:00', '20060712 17:00:00'
union all
select 'joe', '20060712 09:00:00', '20060712 15:00:00'

select empName, sum(numberHours) as hours
--put the time conversion in a derived table (or perhaps a view/calculated column)
from ( select empname, datediff(hour,timeIn, timeOut) as numberHours
from test) as getHours
group by empName

|||

Louis,

Please correct me if I’m wrong. The code you supplied above, as I understand it, says to do the following:

1. Create a temporary table in the database called test with columns named empname, timein and time out.

2. Insert the following values into the temporary table

· select 'john', '20060712 09:00:00', '20060712 17:00:00

· select 'john', '20060712 07:00:00', '20060712 17:00:00

· select 'joe', '20060712 09:00:00', '20060712 15:00:00

(What if I have 200+ columns here, would I have to type this out for each entry in the table or could I just use (Select * from tbltimes)?)

The parts that are new to me and I don’t really understand are the following:

In the insert portion of your code you use "Union All" can you explain what this does?

In the last part of your code you use:

Select empName, sum(numberHours) as hours
from ( select empname, datediff(hour,timeIn, timeOut) as numberHours
from test) as getHours
group by empName

I can only understand bits and pieces of this last section of code. Any input into what this snippet is doing would be great. I’m sorry for the newbie questions here, i would just like to understand this better so i can retain as much as possible for future use.

|||

A "View" would be more of what you are looking for.

Instead of using a temp table, create a view so you can see the results. Use the View to populate your gridview.

Create View MyTestView

AS

select empName, sum(numberHours) as hours
--put the time conversion in a derived table (or perhaps a view/calculated column)
from ( select empname, datediff(hour,timeIn, timeOut) as numberHours
from test) as getHours
group by empName

You can then view the view the same way you would view a table

Select * from MyTestView

Adamus

|||

1. No, I was just building a table to approximate what you told me that your situation was. This is why we always suggest that you give us a table and data to work with when you ask questions.

2. Same thing here. I was just approximating your data soe I could write the query. UNION ALL just does the same thing as UNION but doesn't eliminate duplicates (so no need for a sort)

As for the query:

Select empName, sum(numberHours) as hours
from ( select empname, datediff(hour,timeIn, timeOut) as numberHours
from test) as getHours
group by empName

The bold part is known as a derived table. Basically I was taking your employee name and then applying the function to get the difference between the in time and out time. This set can then be used just like a table or a view.

Then the outer part sums the different hours together grouped by empName.

|||

thanks for the explination louis, that helps a bunch.

i have yet to try your suggestions (will be trying them today sometime) but i was wondering if the following Select statement could possibly get me close to what i want.

SELECT empname, TimeIn, TimeOut, SUM(DateDiff(DateInterval.Minute, TimeIn, TimeOut)) AS TotalHours FROM tbltimes GROUP BY empname

my thinking on this (and i have NOT tried it yet) was that if i made an sqldatasource in VWD and used this as the select statement, then i could just use a gridview to display the results.

am i way off here? as i said, i have not tried this yet and im not even sure the syntax on the above statement is correct.

|||

Yes, that makes perfect sense and is the same thing more or less that I did, but I was just thinking that I would do the dateDiff stuff in a view or calculated column.

Two issues: 1. You can't include timeIn and timeOut in the output list unless they are in the group by (which wouldn't make any sense here)

2. There is no DateInterval.Minute, it would just be Minute

3. (ok, three issues, but this one is really tiny) I would name TotalHours TotalMinutes if you are doing that calculation on the UI

SELECT empname, SUM(DateDiff(Minute, TimeIn, TimeOut)) AS TotalHours FROM tbltimes GROUP BY empname

returns

empname TotalHours
- --
joe 360
john 1080

|||yeah...sum(minutes) as [TotalHours] looks weird from sql.

btw, it might be acceptable to include the first TimeIn and the last TimeOut for each employee.

e.g.
SELECT empname, Min(TimeIn) as [first TimeIn], Max(TimeOut) as [last TimeOut],
SUM(DateDiff(Minute, TimeIn, TimeOut)) AS TotalHours
FROM tbltimes GROUP BY empname

|||

ok, i have tried the Select statement above and now im gettig a few different errors.

Select Statement

SELECT employeename, Sum(DateDiff(Minute, TimeIn, TimeOut)) As Totalhours FROM tbltimes Group By employeename

ERROR:

You tried to execute a query that does not include the specified expression 'employeename' as part of an aggregate function

ERROR:

No value given for one or more required parameters.

any ideas?

|||

I am really, really sorry, but I did not realize that you were using Access. When I read the first question, the lowercase access just didn't ring that bell. Duh. Sorry :)

I don't think there are Access forums here, but there are Access newsgroups on NNTP at msnews.microsoft.com or here on the web at: http://support.microsoft.com/newsgroups/ under your language (probably English) and then under Office.

|||

im using Visual Web Developer 2005 with an access database. so as i understand it this should still work shouldnet it?

the error im getting now is just this:

"No value given for one or more required parameters."

Select statement as follows:

SELECT employeename, SUM(DateDiff(Minute, TimeIn, TimeOut)) As Totalhours FROM tbltimes GROUP BY employeename

|||No, SQL Server has a different syntax. I can't move the post, but try this forum: http://www.asp.net/forums and they might be able to help.

FiscalWeekOfQuarter missing

When I build a time dimension, FiscalWeekOfQuarter does not show up. On the following MSDN page it is listed as an attribute.

http://msdn2.microsoft.com/en-US/library/ms175662.aspx#time_dimension_attribute_types

Anyone know how I can add it? Thanks.

Can you describe the steps you took to build the dimension? I just ran through the dimension wizard, building a server-based time dimension and an attribute of type "FiscalWeekOfQuarter" was created by the wizard. I also looked at an existing time dimension I had based on a dimension table and I can select "FiscalWeekOfQuarter" as the Type for an existing dimension attribute...

Dave Fackler

FiscalWeekOfQuarter missing

When I build a time dimension, FiscalWeekOfQuarter does not show up. On the following MSDN page it is listed as an attribute.

http://msdn2.microsoft.com/en-US/library/ms175662.aspx#time_dimension_attribute_types

Anyone know how I can add it? Thanks.

Can you describe the steps you took to build the dimension? I just ran through the dimension wizard, building a server-based time dimension and an attribute of type "FiscalWeekOfQuarter" was created by the wizard. I also looked at an existing time dimension I had based on a dimension table and I can select "FiscalWeekOfQuarter" as the Type for an existing dimension attribute...

Dave Fackler

First Time Installation of SQL Server 2005 Developer Edition

Just placed the SQL Server 2005 - Developer Edition into my Drive...

I get prompted with the following options..



This DVD contains 32-bit (x86) and 64-bit versions of this edition of SQL Server 2005. Click the link that matches your environment to begin



x86-based operating systems

x64-based operating systems

Itanuim-based operating syystems



I'm new to SQL Server... I just need to start learning how to use it... because it is being used at work... Don't need the all the features installed, just the normal table and query for the moment...



I will be installing it on my PC... i



Microsoft Windows XP - Home Edition - Version 2 - Service Pack 2



My local Hard Drive remaining capacity is 12.6GB

External Hard Drive remaining capacity is 11.0GB



Tried looking everywhere for the answer... lol (even looked at YouTube for a simple installation video...





Thanks for your helpWindowx XP Home Edition is a 32 bit operating system. You want the X86 code for use on XP Home Edition.

-PatP|||I'm new to SQL Server... I just need to start learning how to use it... because it is being used at work... Don't need the all the features installed, just the normal table and query for the moment...

As I only need to install the basic feature...

which one should i click?

SQL Server Database Services
Create a SQL Server fallover cluster
Analysus Services
Create an Analysis Server fallover cluster
Notification Services
Integration Services
Workstation components, Books Online development tools

Please help...|||On the Service Account section...

What should I click / Enter?

Username
Password
Domain

Under the... "Start Services at the end of setup" section, what should I tick?

SQL Server (tick already placed)
SQL Server Agent
SQL Brower

http://img.photobucket.com/albums/v675/akhlaq768/ServiceAccount.jpg

Many thanks

first 7 rows

I have a table sal with the following structure
emp_no int,
dept_no int,
basic_salary money
Is it possible to extract 7 highly paid employed with one SQL statement?
any help will be highly appreciated.select TOP 7
emp_no,
dept_no,
basic_salary
from YourTable
order by basic_salary desc|||how will it calculate highest salary? I mean how system knows to display record based on salary field?|||"order by basic_salary desc"|||the top clause retrieves the rows that would exist at the "Top" of your result set so if your result set was
joe
steve
bill
rob
mary

and you selected the top 3
you would get
joe
steve
bill

the control factor with the top clause is the order by clause
the order by clause sorts the result set either in ascending (ASC) or descending (DESC) order. so if you sorted a salary column asc, the lowest salary would be at the top correct?. and if you selected the top 7 salaries in that example, you would have the 7 lowest salaries.
by sorting the salary column in desc order, you would get the top 7 salaries

please open and read this help file
Books Online{Limiting Result Sets Using TOP and PERCENT}|||how will it calculate highest salary? I mean how system knows to display record based on salary field?

What is the context of the salary table? Does it hold weekly salary data?

Everyone here thinks it's annual.|||Sheesh!

create table sal
(
emp_no int
,dept_no int
,basic_salary money
)

insert into sal values (1 ,10,1000.00)
insert into sal values (2 ,10,2000.00)
insert into sal values (3 ,10,1500.00)
insert into sal values (4 ,10,1200.00)
insert into sal values (5 ,10,1000.00)
insert into sal values (6 ,10,3000.00)
insert into sal values (7 ,10,2200.00)
insert into sal values (8 ,10,1250.00)
insert into sal values (9 ,10,1350.00)
insert into sal values (10 ,10,1000.00)

select * from sal

select top 7 emp_no, dept_no
,Basic_Salary as 'WeeklySalary' --by week
,BiWeeklySalary=(Basic_Salary*2) --BI week
,AnnualSalary=((basic_salary*2)*26) --Annual
from sal
order by AnnualSalary desc|||OK...so what if there's more than 1 salary row per employee?|||...an aggregate query to sum up the salary values.|||...an aggregate query to sum up the salary values.

Thanks you...my point...

Without the DDL of the table we'd be just shooting in the dark

Bang

Yo blind dude..did that hit you?|||Nyah nyah, ya missed me!

You forget that, as the Blindman, I shoot in the dark just fine! :cool:|||okay
since the poster hasnt said anything about it then this post is officially dead.

dont be a malingerer.|||"since the poster hasnt said anything about it then this post is officially dead"?

Like THAT has ever stopped us before...

and "malingerer"?

In the words of Inigo Montoya -
"You keep using that word. I do not think it means what you think it means."

http://dictionary.reference.com/search?q=malingerer|||you killed my father -- prepare to die!|||i know exactly what it means.
you are spending your time posting to a thread that is dead
so you are in effect acting crazy(sick) to get out of the real work here
and that is driving rdjabarov and pat phelan crazy.|||i know exactly what it means.
you are spending your time posting to a thread that is dead
so you are in effect acting crazy(sick) to get out of the real work here
and that is driving rdjabarov and pat phelan crazy.Huh? I seem to keep missing meetings around here!

-PatP|||you killed my father -- prepare to die!

Do you have 6 fingers on your left hand?

That is a great movie

What's with Scott? Are there time issues involved here?

What's officially Dead?|||You are!

Because you killed my father! Prepare to die!

...and Ruprect: Glad you looked up the definition! It pays to enrich your wordpower!|||okay
since the poster hasnt said anything about it then this post is officially dead.

dont be a malingerer.

Hey...he's not even suppose to be here...he's an...an...Oracle developer...

Yo Blind dude..are you feeling OK? (http://www.google.com/search?hl=en&lr=&oi=defmore&q=define:malingerer)|||I have a cough that has been malingering on for weeks now...|||I have a cough that has been malingering on for weeks now...I resemble that remark!

-PatP|||Hey...he's not even suppose to be here...he's an...an...Oracle developer...Its Ok... He can have his kids tell folks that he plays the piano in... Nevermind!

-PatP|||And this thread is only mostly dead...there a difference

To blathe

Have fun stormin the castle boys

I would like to stay and malinger...but I have to go

EDIT: Ya know, this is corral stuff|||Where the heck is a Yak when a guy needs one? I'm off (but you knew that)!

-PatP|||Mostly dead. Now THAT was funny. I wish I'd though of that one, Buttercup.

Can this thread possibly go on? Inconceivable!|||The discussion of this mostle dead thread has moved to the corral

http://www.dbforums.com/showthread.php?p=3978181#post3978181

firehouse mode

I just got the following message after trying to update
records in a sql table:
"transaction cannot start while in firehouse mode"
any ideas what the problem is? please advise and thanksWhat does your code look like? My suggestion is to not use recordset
objects in ADO to modify data. Send UPDATE statements instead...
http://www.aspfaq.com/
(Reverse address to reply.)
"Lisa P" <anonymous@.discussions.microsoft.com> wrote in message
news:940101c478a1$06a5e660$a601280a@.phx.gbl...
> I just got the following message after trying to update
> records in a sql table:
> "transaction cannot start while in firehouse mode"
> any ideas what the problem is? please advise and thanks|||hi Aaron -
I am entering data directly into a table, no query.

>--Original Message--
>What does your code look like? My suggestion is to not
use recordset
>objects in ADO to modify data. Send UPDATE statements
instead...
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>
>"Lisa P" <anonymous@.discussions.microsoft.com> wrote in
message
>news:940101c478a1$06a5e660$a601280a@.phx.gbl...
>
>.
>|||Use an UPDATE statement in Query Analyzer, do not "enter data directly into
a table"...
http://www.aspfaq.com/
(Reverse address to reply.)
"Lisa P" <anonymous@.discussions.microsoft.com> wrote in message
news:973d01c478a7$ed278c00$a301280a@.phx.gbl...[vbcol=seagreen]
> hi Aaron -
> I am entering data directly into a table, no query.
>
> use recordset
> instead...
> message|||If you are in SEM, the Jim's response is the most likely cure... Until all
of the rows which you have chosen are displayed, the cursor for displaying
those rows is still open , and the connection can not do anything else...
So scroll to the bottom, then go back up and make your changes...
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
"Lisa P" <anonymous@.discussions.microsoft.com> wrote in message
news:940101c478a1$06a5e660$a601280a@.phx.gbl...
> I just got the following message after trying to update
> records in a sql table:
> "transaction cannot start while in firehouse mode"
> any ideas what the problem is? please advise and thanks

firehouse mode

I just got the following message after trying to update
records in a sql table:
"transaction cannot start while in firehouse mode"
any ideas what the problem is? please advise and thanks
What does your code look like? My suggestion is to not use recordset
objects in ADO to modify data. Send UPDATE statements instead...
http://www.aspfaq.com/
(Reverse address to reply.)
"Lisa P" <anonymous@.discussions.microsoft.com> wrote in message
news:940101c478a1$06a5e660$a601280a@.phx.gbl...
> I just got the following message after trying to update
> records in a sql table:
> "transaction cannot start while in firehouse mode"
> any ideas what the problem is? please advise and thanks
|||hi Aaron -
I am entering data directly into a table, no query.

>--Original Message--
>What does your code look like? My suggestion is to not
use recordset
>objects in ADO to modify data. Send UPDATE statements
instead...
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>
>"Lisa P" <anonymous@.discussions.microsoft.com> wrote in
message
>news:940101c478a1$06a5e660$a601280a@.phx.gbl...
>
>.
>
|||Use an UPDATE statement in Query Analyzer, do not "enter data directly into
a table"...
http://www.aspfaq.com/
(Reverse address to reply.)
"Lisa P" <anonymous@.discussions.microsoft.com> wrote in message
news:973d01c478a7$ed278c00$a301280a@.phx.gbl...[vbcol=seagreen]
> hi Aaron -
> I am entering data directly into a table, no query.
> use recordset
> instead...
> message
|||If you are in SEM, the Jim's response is the most likely cure... Until all
of the rows which you have chosen are displayed, the cursor for displaying
those rows is still open , and the connection can not do anything else...
So scroll to the bottom, then go back up and make your changes...
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
"Lisa P" <anonymous@.discussions.microsoft.com> wrote in message
news:940101c478a1$06a5e660$a601280a@.phx.gbl...
> I just got the following message after trying to update
> records in a sql table:
> "transaction cannot start while in firehouse mode"
> any ideas what the problem is? please advise and thanks

firehouse mode

I just got the following message after trying to update
records in a sql table:
"transaction cannot start while in firehouse mode"
any ideas what the problem is? please advise and thanksWhat does your code look like? My suggestion is to not use recordset
objects in ADO to modify data. Send UPDATE statements instead...
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Lisa P" <anonymous@.discussions.microsoft.com> wrote in message
news:940101c478a1$06a5e660$a601280a@.phx.gbl...
> I just got the following message after trying to update
> records in a sql table:
> "transaction cannot start while in firehouse mode"
> any ideas what the problem is? please advise and thanks|||hi Aaron -
I am entering data directly into a table, no query.
>--Original Message--
>What does your code look like? My suggestion is to not
use recordset
>objects in ADO to modify data. Send UPDATE statements
instead...
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>
>"Lisa P" <anonymous@.discussions.microsoft.com> wrote in
message
>news:940101c478a1$06a5e660$a601280a@.phx.gbl...
>> I just got the following message after trying to update
>> records in a sql table:
>> "transaction cannot start while in firehouse mode"
>> any ideas what the problem is? please advise and thanks
>
>.
>|||Use an UPDATE statement in Query Analyzer, do not "enter data directly into
a table"...
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Lisa P" <anonymous@.discussions.microsoft.com> wrote in message
news:973d01c478a7$ed278c00$a301280a@.phx.gbl...
> hi Aaron -
> I am entering data directly into a table, no query.
> >--Original Message--
> >What does your code look like? My suggestion is to not
> use recordset
> >objects in ADO to modify data. Send UPDATE statements
> instead...
> >
> >--
> >http://www.aspfaq.com/
> >(Reverse address to reply.)
> >
> >
> >
> >
> >"Lisa P" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:940101c478a1$06a5e660$a601280a@.phx.gbl...
> >> I just got the following message after trying to update
> >> records in a sql table:
> >> "transaction cannot start while in firehouse mode"
> >> any ideas what the problem is? please advise and thanks
> >
> >
> >.
> >|||SYMPTOMS
If you attempt to make changes to a row in a table
displayed in SQL Server Enterprise Manager (SEM), unless
you scroll down to the end of the table (the last row of
the table), Enterprise Manager returns the following
error:
Cannot start transaction while in firehose mode.
CAUSE
When using SEM to display the rows from a table, all rows
are returned by a "firehose cursor"; however, only the
rows that are displayed have been processed. A "firehose
cursor" refers to how the server sends rows to the client
as fast as the client can process them. Rows that are not
displayed in the Enterprise Manager are not processed and,
therefore, they remain in the network buffer.
The "Cannot start transaction while in firehose mode"
error occurs when an OLE-DB provider attempts to perform a
join transaction with results pending and while not in an
updateable cursor mode.
WORKAROUND
Scroll all the way down to the last row of the table. This
forces all the rows to be processed. You can then edit the
row needed and execute the update.
>--Original Message--
>I just got the following message after trying to update
>records in a sql table:
>"transaction cannot start while in firehouse mode"
>any ideas what the problem is? please advise and thanks
>.
>|||thanks Jim, I'll try it - Aaron's suggestion did not work.
regards!
>--Original Message--
>SYMPTOMS
>If you attempt to make changes to a row in a table
>displayed in SQL Server Enterprise Manager (SEM), unless
>you scroll down to the end of the table (the last row of
>the table), Enterprise Manager returns the following
>error:
>Cannot start transaction while in firehose mode.
>CAUSE
>When using SEM to display the rows from a table, all rows
>are returned by a "firehose cursor"; however, only the
>rows that are displayed have been processed. A "firehose
>cursor" refers to how the server sends rows to the client
>as fast as the client can process them. Rows that are not
>displayed in the Enterprise Manager are not processed
and,
>therefore, they remain in the network buffer.
>The "Cannot start transaction while in firehose mode"
>error occurs when an OLE-DB provider attempts to perform
a
>join transaction with results pending and while not in an
>updateable cursor mode.
>WORKAROUND
>Scroll all the way down to the last row of the table.
This
>forces all the rows to be processed. You can then edit
the
>row needed and execute the update.
>
>>--Original Message--
>>I just got the following message after trying to update
>>records in a sql table:
>>"transaction cannot start while in firehouse mode"
>>any ideas what the problem is? please advise and thanks
>>.
>.
>|||> Aaron's suggestion did not work.
What does "did not work" mean?
--
http://www.aspfaq.com/
(Reverse address to reply.)|||If you are in SEM, the Jim's response is the most likely cure... Until all
of the rows which you have chosen are displayed, the cursor for displaying
those rows is still open , and the connection can not do anything else...
So scroll to the bottom, then go back up and make your changes...
--
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
"Lisa P" <anonymous@.discussions.microsoft.com> wrote in message
news:940101c478a1$06a5e660$a601280a@.phx.gbl...
> I just got the following message after trying to update
> records in a sql table:
> "transaction cannot start while in firehouse mode"
> any ideas what the problem is? please advise and thanks

Firefox Issues in 2005...

Can someone please verify that the following items are not supported in Firefox. Our app issues an http request to the report server (url access). The following differences are seen between IE and FF:

1) The message "Report is being generated" does not disply in FF during report generation.

2) The printer icon does not appear in the toolbar.

3) Is there any plan to support search functionality within the report in the future?

Thanks.

I'm not sure about #1 or #3, but for #2, client side print is only supported in IE. This is because the client side printing is done via an ActiveX control.

|||Thanks Daniel. I'm recommending to my clients that the IE Tab add-on be installed in Firefox; it solves the problems with the search bar and also displays the "Report is being generated" message. I understand why the search bar functionality is not available in vanilla FF but I'm not sure why the Report generation message with the twirly doesn't appear.

FireEvent issues with Reporting Services

I am attempting to use the following command line (and associated script) to trigger an existing subscription within Reporting Services (2000):
command line:

rs.exe -i MyScriptFile.rss -s "MyReportServer/ReportServer"

The input scriptfile (MyScriptFile) contains the lines below where the scheduleID is taken from Reporting Services from an existing 'once only' schedule. The schedule, - when triggered - should fire off a subscription and email recipients with a particular existing report.

Sub Main
Dim rs As New ReportingService()
rs.Credentials = System.Net.CredentialCache.DefaultCredentials
rs.FireEvent("TimedSubscription", "87206163-7665-4458-a86c-75b84cf18b2d")
End Sub

Unfortunately, it doesn't work and I almost always get the response:

Unhandled exception:
The request failed with HTTP status 404: Not Found.

If I comment out the line rs.FireEvent( . . . from the script then the command line runs successfully. I can only conclude that there appears to be something wrong with this particular line.

Any ideas anyone?

Kev

Rs.exe takes care of creating the proxy to SSRS and doing authentication, so the first two lines of code (Dim RS & rs.Credentials) aren't actually necessary. The rest of your code looks fine, though.

You're saying that it sometimes does work?

Here are a few things to try:

- Can you check your Subscription ID to make sure it actually exists?

- Does this code work against a Subscription ID directly (in code) from a different (known good) subscription? (For example, see if this sample works: http://msdn2.microsoft.com/en-us/library/microsoft.wssux.reportingserviceswebservice.rsmanagementservice2005.reportingservice2005.fireevent.aspx)

- FireEvent is the only method you're actually firing against the SOAP API...why don't you include another before you call FireEvent (something simple like ListChildren()) to make sure that the web service can do *any* work on your behalf.

- I've never seen someone specify the server name in their command-line w/o appending http:// first. This mean it won't work, I've just never seen it. Can you try something like this: rs.exe -i myFile.rss -s http://localhost/reportserver -u myDomain\myAccount -p myPassword ? Do this after you remove the first two lines of your code.

Let us know...good luck!

|||Russell, Thanks tremendously for the reply! I spent two whole days on this (without success) trying every variation I could think of.

Firstly, no the code in the original post has never worked; I've not been able to trigger anything. The Subscription ID definitely exists however: I highlighted it and copied it from within the Reporting Services address bar (in the Scheduling page) directly making sure I had it perfect. I did wonder whether it 'was' the intended ScheduleID but the address bar in the browser reads:

http://MyServer/Reports/Pages/Schedule.aspx?ScheduleID=87206163-7665-4458-a86c-75b84cf18b2d

. . . so I am assuming that I really 'do' indeed have the correct ScheduleID it's looking for (87206163-7665-4458-a86c-75b84cf18b2d)? Or is it? I've tried this with double quotes, without quotes, with single quotes all with no luck.

Secondly, the code below works perfectly indicating that the SOAP API will indeed do something for me:

rs.exe -i ListFormatExtensions.rss -s "MyServer/ReportServer"

with the following code in the ListFormatExtensions.rss file:

Sub Main
For Each Ext As Extension In rs.ListExtensions(ExtensionTypeEnum.Render)
Console.WriteLine(Ext.Name)
Next
End Sub

Strangely, ListChildren (as you suggested) didn't work at all? It failed with error BC30451: Name 'ListChildren' is not declared. I would assume that this is something perhaps due to the fact that I am using Reporting Services 2000 and not 2005? The actual failed code I used in the rss file was:

Sub Main
Dim rs As New ReportingService()
rs.Credentials = System.Net.CredentialCache.DefaultCredentials
rs.ListChildren()
rs.FireEvent("TimedSubscription", "87206163-7665-4458-a86c-75b84cf18b2d")
End Sub
The server name also works either way I specify it with the ListFormatExtensions test. So both these code snippets work happily and there's no issue with username and password:

rs.exe -i ListFormatExtensions.rss -s "MyServer/ReportServer"
rs.exe -i ListFormatExtensions.rss -s http://MyServer/ReportServer

I tried the option you suggested (without the first two lines of code) which it didn't like:

Sub Main
FireEvent("TimedSubscription", "87206163-7665-4458-a86c-75b84cf18b2d")
End Sub

error BC30451: Name 'FireEvent' is not declared

. . . so I promptly put the first two lines back in.

I am still trying to figure out what you mean by - Does this code work against a Subscription ID directly (in code) from a different (known good) subscription? (For example, see if this sample works: http://msdn2.microsoft.com/en-us/library/microsoft.wssux.reportingserviceswebservice.rsmanagementservice2005.reportingservice2005.fireevent.aspx)

The web address you gave simply takes me straight to the MSDN definition for FireEvent in Reporting Services 2005.

Kev


|||

Hey Kev --

Even though you can remove "Dim RS", etc. you still use "rs.Whatever" in your code

This worked for me:

1. Created a new subscription off the Adventure Works - Company Sales report: Used the fileshare delivery extension because I don't have an SMTP server handy

2. Put it on a schedule that would not fire until 3 days from now

3. Executed select * from reportserver..subscriptions to get the Subscription ID

4. Threw code below in an RSS file:

Sub Main()

Try
rs.FireEvent("TimedSubscription", "E34849EE-63EE-4BF6-957F-55A9B1132DDB")
Catch ex As Exception
Console.WriteLine(ex.Message)
End Try

End Sub

5. In Report Manager, made sure that my user had "Generate Events" permissions (a system permission)

6. Executed the code with rs.exe -i c:\tryme.rss -s http://servername/reportserver

7. Checked my fileshare, found the report sitting there.

|||

Yes, make sure that you are using the subscription ID, as Russell is, and not the schedule ID that you said you were using.

-Daniel

|||

Guys, thanks for your input!! The problem is now sorted.

Unfortunately, I never gave a thought to the fact that I was running the rs.exe command on my own desktop and not the server on which Reporting Services is actually installed ! Dumb hey! When I put the file TriggerReport.rss on the report server and then ran the command to execute on the report server itself (using the psexec command):

psexec \\myserver rs.exe -i C:\TriggerReport.rss -s "http://myserver/ReportServer"

. . . it triggered the report immediately. The report promptly arrived in my email. I guess the lesson here is to stand back often and ask yourself if you're doing anything obviously stupid before blaming the code.

Kev

|||What Imports do you need for your .NET app if using SSRS 2005? Just thought I'd ask since you guys are the only place I've found really good info on this subject.