Cant get CAST function to work in ASP.NET

I'm trying to do something like this SQL statement. I have a table with a field of date/times.

i.e - 2/27/06 9:55:95 PM Basically there are multiple entries in the table per day.

I want to count the records for particular (hence the CAST) and output the count.

Here it was I had just to see if SQL would work in ASP.NET :

SELECT COUNT(*) AS Expr1
FROM dbo.tblActiveDrums
WHERE [CAST](FLOOR([CAST](NCTimeStamp AS [float])) AS datetime) = '0' OR
NCTimeStamp - [CAST](FLOOR([CAST](NCTimeStamp AS [float])) AS datetime) = '0'

It didn't like the AS references?

Any help would be appreciated.

Remove all the brackets and post if there are any error messages. Shouldn't be.|||You want to count the records for a particular what?

Can''t get at first row of input buffer...why?!

Hi

A script component receives some input. But I just can't get at the first row?

Basically, if i use the NextRow method in the in the Do statement, then it advances the row collection to the second row before it gets into the code inside the loop? BUT, if I use the EndOfRowset property to define my loop then I get an error:

[PipelineBuffer has encountered an invalid row index value]

I'm guessing this means...I have to call NextRow before i access the data in the collection? But thats retarted because then I miss the first row? what? What am I missing?

This is the code which works but I miss the first row:

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Dim strConcept As String

Do While Row.NextRow()

strConcept = Row.concept

updateDb(strConcept)

Loop

End Sub

This is the code which throws the invalid row index error:

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Dim strConcept As String

Do While Not Row.EndOfRowSet()

strConcept = Row.concept

updateDb(strConcept)

Row.NextRow()

Loop

End Sub

I've put some try catches in there an the error happens on the line which calls Row.concept....?

Can anyone help, it must be something I'm messing up

thanks!!

andy
You can't advance to the next row because the next row is fed in by the data flow.

What are you trying to do?

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer) is executed for every row passing through the script component.|||hey, thanks for the quick reply.

I have to say I don't really know about the internal workings of things...I'm new to SSIS, SQLServer and .NET...BUT, what I do know is that within that Sub I can in fact advance through the rows.....I do this like so:

Do While row.NextRow()

someVariable = Row.whatever_Input_Column_Ive_defined_in_the_components_input

Loop

If I use the above construct, then it does loop through the rows...NextRow() must return a False when there are no more rows and then the loop stops. Perfect...except I miss the first Row...? I'm assuming this is because NextRow is called before i even get into the loop....?

I must be doing something wrong....but what? Thanks!!

andy
|||But what are you trying to do? You don't need to call NextRow because the next row will be provided automatically. You can setup a variable outside of the sub and then inside the sub populate that variable as you see fit.|||oh, sorry Phil, I'm a bit confused.

I have a bunch of rows coming into this script component....in fact I have 6 rows. I just want to access each column of each row and within each iteration I want to do a SQL INSERT.

Now, I'm not entirely sure what you mean, but are you saying I don't need to even do I DO LOOP block?

What I do know is that I have tried to access each Row without calling NextRow but its throws that error:

[PipelineBuffer has encountered an invalid row index value]

?

Thanks...sorry if I'm not getting what you're saying...you might need to spell it out

much appreciated!

|||

andyrose wrote:

oh, sorry Phil, I'm a bit confused.

I have a bunch of rows coming into this script component....in fact I have 6 rows. I just want to access each column of each row and within each iteration I want to do a SQL INSERT.

Why not just use an OLE DB Destination, instead of using a script, if this is what you want to do?|||ahhh...hold on, I think I get it...so SSIS calls that Input0_ProcessInpoutRow sub as many times as there are rows?

So it calls all the code in there...say...in my case, 6 times? But its thats kinda expensive....all this reduntant code...like connecting to a db is been called without need...?

am I right?
|||well...I lied a little. I kinda have to use a script just because within each interation I'm actually having to do some checks, which involve a load of other code...and only if I get a certain result does that row get added...so yeah, thats why I'm doing it in script
|||

andyrose wrote:

ahhh...hold on, I think I get it...so SSIS calls that Input0_ProcessInpoutRow sub as many times as there are rows?

So it calls all the code in there...say...in my case, 6 times? But its thats kinda expensive....all this reduntant code...like connecting to a db is been called without need...?

am I right?

Correct, and hence the question as to why you want to even use a script. SSIS comes with prebuilt destination components that will perform inserts for you.|||

andyrose wrote:

well...I lied a little. I kinda have to use a script just because within each interation I'm actually having to do some checks, which involve a load of other code...and only if I get a certain result does that row get added...so yeah, thats why I'm doing it in script

And these checks can't be done with lookup components and/or derived columns?|||yeah maybe....truth is I've been thrown this SSIS thing without ever even knwoing this stuff existed...I've never used anything like it...

Its new to me, so I only know how to do stuff in code. I am learning, and SSIS is very cool, but I've got to get this done quickly and so do it I usually jump in the code...but yeah, I have used all the other components for simple stuff...its just when it has to do loads of checks here and there I just write the code and it gets done.
|||

andyrose wrote:

Its new to me, so I only know how to do stuff in code. I am learning, and SSIS is very cool, but I've got to get this done quickly and so do it I usually jump in the code...but yeah, I have used all the other components for simple stuff...its just when it has to do loads of checks here and there I just write the code and it gets done.

Ah, okay, well, good luck! I hope I've helped you understand the script component. There's also a ProcessInput sub that actually passes each row to the ProcessInputRow sub. This is all in the books online though should you need a reference.|||

The script component tries to simplify things for you, so it builds the logic to loop through the rows in the buffer for you, and calls the ProcessInputRow method for you. If you want to see the code that's used, open your script component, click the design the script button to open VSA, and open the project explorer. The BufferWrapper code shows how they are putting friendly names for the column values, and the ComponentWrapper code shows how they are iterating the buffer. This probably goes without saying, but you shouldn't modify the code in either of these.

|||cool, thanks for your help phil....tried that but now am getting a validation VS_ISBROKEN error...though I guess thats whole other thread...thanks for your help!
|||ah yeah, had a look, thanks jwelch. My experience is in classic ASP and VB so its weird to have .NET do everything for you...well not everything, but yeah, cool, thanks for the heads up!

Cant get a simple code to work

Can someone please help me fix this. The if statement always runs. I only want it to run if the statement is true and there is a result coming back. If the productID does not have any magazines linking to it, I don't want the If statement to run. Thanks in advance.

ASP.NET code:

If Not Magazine.GetMagazinesForProduct(ProductID) Is Nothing Then
blanklabel.Text = categoryDetails.Spacer
blanklabel.Visible = True
blanklabel2.Text = categoryDetails.Spacer
blanklabel2.Visible = True
magazinerecommendedlabel.Text = "Recommended/Featured in the following magazine(s):"
magazinerecommendedlabel.Visible = True
End If
--------

magazine class:

Public Function GetMagazinesForProduct(ByVal productID As String) As SqlDataReader
Dim connection As New SqlConnection(connectionString)

Dim command As New SqlCommand("GetMagazinesForProduct", connection)
command.CommandType = CommandType.StoredProcedure

command.Parameters.Add("@.ProductID", SqlDbType.VarChar, 50)
command.Parameters("@.ProductID").Value = productID

connection.Open()

Return command.ExecuteReader(CommandBehavior.CloseConnection)
End Function

--------

Stored Procedure:

CREATE PROCEDURE GetMagazinesForProduct
(@.ProductID varchar)
AS
SELECT Magazine.[Name], Magazine.[Issue], Magazine.SmallImagePath
FROM Magazine INNER JOIN MagazineProduct
ON Magazine.MagazineID = MagazineProduct.MagazineID
WHERE MagazineProduct.ProductID = @.ProductID
RETURN
GO

--------How about trying a while instead? I'm not sure of the exact VB syntax, maybe something like this:

dim myReader as Magazine.GetMagazinesForProduct(ProductID)
While myReader .Read()
 .. do your stuff
end while

|||for some reason, myReader.Read() always executes FALSE. Why is that?|||It means there's no data in present. Are you positive your stored proc is returning data for the id you are passing to it?

Can't Figure out SQL Statement

Perhaps someone can help me on this SQL statement. Let's say my table
has only 2 fields:
RowId Name
-- --
1 A
2 A
3 A
4 A
5 B
6 B
7 B
8 B
I want to return a dataset containing the rows with the lowest RowId
for each DISTINCT Name. Result:
RowId Name
-- --
1 A
5 B
Can anybody help me?
Thanks,
JasonOne method:
SELECT
MIN(RowId) AS RowId,
Name
FROM MyTable
GROUP BY
Name
Hope this helps.
Dan Guzman
SQL Server MVP
"daokfella" <jjbutera@.hotmail.com> wrote in message
news:1126619489.331236.145060@.g49g2000cwa.googlegroups.com...
> Perhaps someone can help me on this SQL statement. Let's say my table
> has only 2 fields:
> RowId Name
> -- --
> 1 A
> 2 A
> 3 A
> 4 A
> 5 B
> 6 B
> 7 B
> 8 B
> I want to return a dataset containing the rows with the lowest RowId
> for each DISTINCT Name. Result:
> RowId Name
> -- --
> 1 A
> 5 B
> Can anybody help me?
> Thanks,
> Jason
>|||SELECT MIN(RowID), Name FROM [my table] GROUP BY Name
"daokfella" <jjbutera@.hotmail.com> wrote in message
news:1126619489.331236.145060@.g49g2000cwa.googlegroups.com...
> Perhaps someone can help me on this SQL statement. Let's say my table
> has only 2 fields:
> RowId Name
> -- --
> 1 A
> 2 A
> 3 A
> 4 A
> 5 B
> 6 B
> 7 B
> 8 B
> I want to return a dataset containing the rows with the lowest RowId
> for each DISTINCT Name. Result:
> RowId Name
> -- --
> 1 A
> 5 B
> Can anybody help me?
> Thanks,
> Jason
>

Cant delete single record? HELP!

OK,

This one is driving me nuts. I've issued a very simple statement to delete a single row from a table. It appears that when I execute it in SQL Query Analyzer the CPUTime spikes and holds one of the CPUs on the box pegged at 100%. I've let this thing run for over a day, and it's not deleting the one damn record. Any thoughts? :confused: :confused: Here's the command I'm executing:

DELETE FROM Invoices WHERE InvoiceID = 153345

Running SELECT * FROM Invoices WHERE InvoiceID = 153345 returns only a single record as it should. InvoiceID is the PK in this table. Any and all help is greatly appreciated. I've rebooted the server, but to no avail. Same thing happens after a reboot.

TIAAny triggers on the table?|||Any triggers on the table?
No, no triggers on the table.|||Any triggles on this table?
No triggles either ;)|||what does the execution plan looks like for the delete statement?

Are there foreign key constraints on Invoices table ?
run something similar to the following:

select object_name(constid) as FkeyName,
object_name(fkeyid) as DependentTable from sysforeignkeys where rkeyid=object_id('Invoices')

Is anything locking Invoices table when you try the delete?

simas|||It won't even pull up an estimated execution plan. It just sits there doing nothing. There are several foreign keys, but I'm deleting from those tables prior to this delete, and they all delete without a hitch. It's just this table. I don't see anything locking the Invoices table.|||Have you ran a DBCC CHECKDB to see if there are any integrity issues?|||use <Your_Database_Name>
go
dbcc opentran
go

You have to have an open transaction that references this table. Oh, and do KILL your DELETE before you issue the DBCC. Make sure that any rollback (doubt it) completes before you fire DBCC.|||CHECKDB found 0 allocation errors and 0 consistency errors in database 'Accounting'.

Nice thought though.|||This is trying to pull a rabit out of the hat, but try to run UPDATE STATISTICS on the table. Also, try to drop and recreate the PRIMARY KEY index on that table. Then, see if you can delete the rows.

Also, I don't know why in the world I didn't suggest this earlier. In Query Analyzer before you do anything else, run: SELECT @.@.SPID. Set up a profiler to capture all activity using that SPID as a filter. In particular, look for SQL:Batch Started; SQL:Batch Completed; all errors, all recompiles, all locks and blocks; RPC:Started; RPC:Completed. See if you can see anything in the profiler that would lead to this problem.

Also, when this is running, if you run this in Query Analyzer do you get any results?

SELECT * FROM master..sysprocesses WHERE blocked <> 0|||UPDATE: DELETING THE PRIMARY KEY AND RECREATING IT WORKED. (no comment on how bad I think it is that this should ever have to be done, but it's fixed so I won't complain)

Mad Thanks go out to those that helped me out with this!

Special Thanks to Derrick! :D|||And you're sure you're on the latest service packs, critical updates, etc for Windows and SQL Server? Make sure the machine that has Query Analyzer on it has also been updated.|||SP3a on both, I'll have to check on the critical updates (since my boss thinks the internet is a fad). Thanks again for everything!

Cant create index on view

Hi,

SQL Server 2005 (SP2) Developer edition on XP Pro

Can anyone help with setting up an indexed view? Part of the select statement calls a couple of very simple functions which are deterministic. The functions which are shown below do not perform any aggregation.

This is the error message.

Msg 8668, Level 16, State 0, Line 2
Cannot create the clustered index 'IX3_SA' on view 'XPS.dbo.SA_INDEXED' because the select list of the view contains an expression on result of aggregate function or grouping column. Consider removing expression on result of aggregate function or grouping column from select list.

Function definitions:

create FUNCTION [dbo].[MonthName](@.MonthNo TINYINT)
RETURNS CHAR(3)
with schemabinding
AS
BEGIN
DECLARE @.MonthName CHAR(3)
IF @.MonthNo BETWEEN 1 AND 12 SET @.MonthName = SUBSTRING('JanFebMarAprMayJunJulAugSepOctNovDec',((@.MonthNo*3) -2),3)
RETURN ISNULL(@.MonthName,'')
END

create FUNCTION [dbo].[FinancialYear](
@.Year INT,
@.MonthNo TINYINT,
@.YearEndMonthNo TINYINT)
RETURNS INT
with schemabinding
AS
BEGIN
DECLARE @.FinancialYear INT
IF @.MonthNo <= @.YearEndMonthNo
SET @.FinancialYear = @.Year
ELSE
SET @.FinancialYear = @.Year + 1
RETURN @.FinancialYear
END

The first part of the select statement is as follows:

(SELECT
count_big(*) as CB,

dbo.FinancialYear(YR.Number,MTH.Number,YEAR_END_MONTH) AS REPORT_FINANCIAL_YEAR,
dbo.MonthName(MTH.Number) + ' ' + CAST(YR.Number AS CHAR(4)) AS REPORT_MONTH,
dbo.MonthName(YEAR_END_MONTH) + ' ' + CAST(dbo.FinancialYear(YR.Number,MTH.Number,YEAR_END_MONTH) AS CHAR(4)) AS REPORT_FINANCIAL_YEAR_END,

I can create the index on this view if I comment out the calls to the functions so I know that is where the problem is. I can't however see why this is erroring as both only return a single value without any agregation.

Anyone got any ideas on this?

Thanks in advance

David.

Well that met with a deafening silence.

The answer was to move these function calls out of the indexed view definition into a standard view that sits above it.