MS SQL Server :: How Can I Pass A String Parameter More Than 4000 Characters Into Execute() And Return Result For FETCH And Cursor?

SQL Server Compact Edition Ntext Fails With String Over 4000 Characters
This is with SQLCe NET 3.5.0.0 running on Windows Server 2003 or Server 2008, not on a Windows mobile operating system.

The following code fails with ntext entries above 4000 characters:

        Dim cn As New SqlCeConnection(ConnectString())
        If cn.State = ConnectionState.Closed Then
            cn.Open()
        End If
        Dim info as string

info = "This is lengthy text".PadLeft(4200)
        Dim cmd As SqlCeCommand

        strSQL = "create table testTable ("
        strSQL &= "docType nvarchar (50) NULL, "
        strSQL &= "docFlag nvarchar(10) NULL, "
        strSQL &= "docData ntext NULL, "
        strSQL &= " )"

        cmd = New SqlCeCommand(strSQL, cn)
        Try
            cmd.ExecuteNonQuery()
        Catch sqlexception As SqlCeException
            MessageBox.Show(sqlexception.Message & vbNewLine & strSQL, "Table Error 7", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1)
        Catch ex As Exception
            MessageBox.Show(ex.Message, "Table Error 8", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1)
        Finally
            cn.Close()
        End Try
                    If cn.State = ConnectionState.Closed Then
                        cn.Open()
                    End If

                    '---- insert a row into the testTable

                    strSQL = "INSERT INTO testTable ("
                    strSQL &= "docType, "
                    strSQL &= "docFlag, "
                    strSQL &= "docData, "
                    strSQL &= ") "
                    strSQL &= "VALUES ("
                    strSQL &= "@docType, "
                    strSQL &= "@docFlag, "
                    strSQL &= "@docData, "
                    strSQL &= ")"

                    Try
                        cmd = New SqlCeCommand(strSQL, cn)
                        cmd.Parameters.AddWithValue("@docType", "a type")
                        cmd.Parameters.AddWithValue("@docFlag", "a flag")
                        cmd.Parameters.AddWithValue("@docData", info)
                        cmd.ExecuteNonQuery()
                    Catch sqlexception As SqlCeException
                        MessageBox.Show(sqlexception.Message, "Table Error 9", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1)
                    Catch ex As Exception
                        MessageBox.Show(ex.Message, "Table Error 10", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1)
                    Finally
                        cn.Close()
                    End Try
                End If

Changing the cmd.Parameters as follows works:

                    Dim paramdocData As SqlCeParameter
                    Try
                        cmd = New SqlCeCommand(strSQL, cn)
                        cmd.Parameters.AddWithValue("@docType", "a type")
                        cmd.Parameters.AddWithValue("@docFlag", "a flag")
                        paramdocData = cmd.Parameters.Add("docData", SqlDbType.NText)
                        paramdocData.Value = info
                        cmd.ExecuteNonQuery()
                    Catch sqlexception As SqlCeException
                        MessageBox.Show(sqlexception.Message, "Table Error 9", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1)
                    Catch ex As Exception
                        MessageBox.Show(ex.Message, "Table Error 10", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1)
                    Finally
                        cn.Close()
                    End Try

Thanks to the following Microsoft ReadMe for the above suggestion. See:
http://download.microsoft.com/download/f/7/2/f72ebbf8-4df1-4800-b4db-c2405c10d937/ReadmeSSC35.htm

View Replies !

How To Return Multiple Results In 1 Fetch Cursor- Urgent Help!
I want to send 1 email with all clientname records which the cursor gets for me.
My code however is sending 1 email for 1 record i.e clientname got from db. What's wrong? please help.
I ano table to understand here about the while if right.
thanks.
+++++++++++++++++++++++++++++++++++++++++
CREATE PROCEDURE test1
AS

declare @clientName varchar(1000)

declare myCursor CURSOR STATIC
for

select client_name
from clients
-------------------------
-- now prepare and send out the e-mails
declare @ToEmail varchar(255)
declare @FromEmail varchar(255)
declare @Subject varchar(255)
declare @Body varchar(2000)
declare @UserID numeric(38)
declare @UserName varchar(255)
declare @SMTPServer varchar(100)
set @SMTPServer = 'test.testserver.com'

-- loop for each record

open myCursor
fetch next from myCursor
into @clientName

--loop now:
while (@@fetch_status=0)

begin -- while(@@fetch_status=0)
-- check if valid "To" e-mail address was found
if ((@clientName is null) or (ltrim(@clientName) = ''))

begin
--should not come here anytime ideally
set @FromEmail = 'me@test.com'
set @ToEmail = 'me@test.com'
set @Subject = 'was emailed to wrong person'
set @Body = 'the client name got is : '+ @clientName + 'client is null or empty'

end --if

else

begin
set @FromEmail = 'me@test.com'
set @ToEmail = 'me@test.com'
set @Subject = '-testing'
set @Body =

'this will send
ClientName:'+ @clientName
end --end else

-- send the e-mail
--exec dbo.usp_SendCDOSysMailWithAuth @FromEmail, @ToEmail, @Subject, @Body, 0, @SMTPServer
--fetch next from myCursor into @clientName

fetch next from myCursor
into @clientName

end --while(@@fetch_status=0)
exec dbo.usp_SendCDOSysMailWithAuth @FromEmail, @ToEmail, @Subject, @Body, 0, @SMTPServer
close myCursor
deallocate myCursor

GO

View Replies !

How To Create A TVF With A String Input Parameter Longer That 4000 Character
Hello,
I created a TVF that take as input a string and a delimiter (tipically a ',') and return a table.
Very briefly this my code:

<Microsoft.SqlServer.Server.SqlFunction(FillRowMethodName:="FillCharListRow", Name:="Split_CharList", TableDefinition:="Value nvarchar(255)")> _

Public Shared Function Split_CharList(ByVal strList As SqlString, ByVal delimiter As SqlString) As IEnumerable

Return strList.Value.Split(delimiter.Value.ToCharArray(0, 1))

End Function

Private Shared Sub FillCharListRow(ByVal obj As Object, <Out()> ByRef strList As String)

strList = CType(obj, String)

strList = strList.Trim

End Sub

This is what I see from the Managment studio, after I deploy the code:

ALTER FUNCTION [dbo].[Split_CharList](@strList [nvarchar](4000), @delimiter [nvarchar](4000))

RETURNS TABLE (

[Value] [nvarchar](255) NULL

) WITH EXECUTE AS CALLER

AS

It means that the string I give to the function has to be no longer than 4000.
There is the way to define a function that can accept a longer input?

Thankx very much
Marina B.

View Replies !

The Parameterized Query '(@contactdate Nvarchar(4000),@dnbnumber Nvarchar(4000),@prospect' Expects The Parameter '@futureopportunity', Which Was Not Supplied.
HI, I am running the below method which returns this error: The parameterized query '(@contactdate nvarchar(4000),@dnbnumber nvarchar(4000),@prospect' expects the parameter '@futureopportunity', which was not supplied" Please help.Private Shared Sub InsertData(ByVal sourceTable As System.Data.DataTable, ByVal destConnection As SqlConnection)
' old method: Lots of INSERT statements Dim rowscopied As Integer = 0
' first, create the insert command that we will call over and over:
destConnection.Open()Using ins As New SqlCommand("INSERT INTO [tblAppointmentDisposition] ([contactdate], [dnbnumber], [prospectname], [businessofficer], [phonemeeting], [followupcalldate2], [phonemeetingappt], [followupcalldate3], [appointmentdate], [appointmentlocation], [appointmentkept], [applicationgenerated], [applicationgenerated2], [applicationgenerated3], [comments], [newaccount], [futureopportunity]) VALUES (@contactdate, @dnbnumber, @prospectname, @businessofficer, @phonemeeting, @followupcalldate2, @phonemeetingappt, @followupcalldate3, @appointmentdate, @appointmentlocation, @appointmentkept, @applicationgenerated, @applicationgenerated2, @applicationgenerated3, @comments, @newaccount, @futureopportunity)", destConnection)
ins.CommandType = CommandType.Textins.Parameters.Add("@contactdate", SqlDbType.NVarChar)
ins.Parameters.Add("@dnbnumber", SqlDbType.NVarChar)ins.Parameters.Add("@prospectname", SqlDbType.Text)
ins.Parameters.Add("@businessofficer", SqlDbType.NChar)ins.Parameters.Add("@phonemeeting", SqlDbType.NVarChar)
ins.Parameters.Add("@followupcalldate2", SqlDbType.NVarChar)ins.Parameters.Add("@phonemeetingappt", SqlDbType.NVarChar)
ins.Parameters.Add("@followupcalldate3", SqlDbType.NVarChar)ins.Parameters.Add("@appointmentdate", SqlDbType.NVarChar)
ins.Parameters.Add("@appointmentlocation", SqlDbType.NVarChar)ins.Parameters.Add("@appointmentkept", SqlDbType.NVarChar)
ins.Parameters.Add("@applicationgenerated", SqlDbType.NVarChar)ins.Parameters.Add("@applicationgenerated2", SqlDbType.NVarChar)
ins.Parameters.Add("@applicationgenerated3", SqlDbType.NVarChar)ins.Parameters.Add("@comments", SqlDbType.Text)
ins.Parameters.Add("@newaccount", SqlDbType.NVarChar)ins.Parameters.Add("@futureopportunity", SqlDbType.NVarChar)
' and now, do the work: For Each r As DataRow In sourceTable.RowsFor i As Integer = 0 To 15
ins.Parameters(i).Value = r(i)
Next
ins.ExecuteNonQuery()
'If System.Threading.Interlocked.Increment(rowscopied) Mod 10000 = 0 Then
'Console.WriteLine("-- copied {0} rows.", rowscopied)
'End If
Next
End Using
destConnection.Close()
End Sub

View Replies !

Return Only Numeric Characters From A String Column
I have written this query to return only numeric characters from a string.

select *
from TableA
where isNumeric(Column_A) = 1

But I have discovered that the following

SELECT IsNumeric('-')
SELECT IsNumeric('£')
SELECT IsNumeric('$')
SELECT IsNumeric('+')

all return a value of 1. I do not want these in my result set.

If I use Column_A NOT LIKE '%[a-z]%' and Column_A <> '' I get characters such as "", ----- etc in my result set.

I would like a simple way of only returning numbers in my resultset.

Thanks for looking at this.

View Replies !

Do Not Display The Result Of My Long Text String, Approx About 400 Characters
I have the following problem. My SQL Query that i wrote works but the result that is displayed in Query analyzer cuts most of my long text that I want in my result. The long text string is approx about 400 characters and the type is varchar of the field. Any ideas??

SELECT '510', PRODCLASSID
, '1', COMPONENTID,'ENG'+SPACE(2),'#'+SPACE(254),'#'+SPAC E(254),'#'+SPACE(254),'#'+SPACE(99),externalid,
'Desc1' = CASE
WHEN SUBSTRING(externalid,1,2) = 'MF'
THEN 'Full machine warranty : parts, labour, mileage and others covered at warranty rates applicable at the time of repair. '
WHEN SUBSTRING(externalid,1,2) = 'MP'
THEN 'Full machine warranty, parts only : parts covered at warranty rates applicable at the time of repair. '
WHEN SUBSTRING(externalid,1,2) = 'PF'
THEN 'Power line warranty : parts, labour, mileage and others covered at warranty rates applicable at the time of repair. '
WHEN SUBSTRING(externalid,1,2) = 'PP'
THEN 'Power line warranty, parts only : parts are covered at warranty rates applicable at the time of repair. '
END
+
CASE
WHEN SUBSTRING(externalid,LEN(externalid)- 3,4) = '2018'
THEN 'Flexible warranty starts after the standard warranty period has expired and is covered up to 18 month or 2000 HRS, whichever comes first. '
WHEN SUBSTRING(externalid,LEN(externalid)-3,4) = '3024'
THEN 'Flexible warranty starts after the standard warranty period has expired and is covered up to 24 month or 3000 HRS, whichever comes first. '
WHEN SUBSTRING(externalid,LEN(externalid)-3,4) = '4030'
THEN 'Flexible warranty starts after the standard warranty period has expired and is covered up to 30 month or 4000 HRS, whichever comes first. '
WHEN SUBSTRING(externalid,LEN(externalid)-3,4) = '5036'
THEN 'Flexible warranty starts after the standard warranty period has expired and is covered up to 36 month or 5000 HRS, whichever comes first. '
WHEN SUBSTRING(externalid,LEN(externalid)-3,4) = '6042'
THEN 'Flexible warranty starts after the standard warranty period has expired and is covered up to 42 month or 6000 HRS, whichever comes first. '
WHEN SUBSTRING(externalid,LEN(externalid)-3,4) = '8054'
THEN 'Flexible warranty starts after the standard warranty period has expired and is covered up to 54 month or 8000 HRS, whichever comes first. '
WHEN SUBSTRING(externalid,LEN(externalid)-3,4) = '1074'
THEN 'Flexible warranty starts after the standard warranty period has expired and is covered up to 74 month or 10000 HRS, whichever comes first. '
END
+ 'Flexible warranty is handled according to the procedures described in ESPPM 3-10.'
+
CASE

WHEN prodclassid IN ('P1','P11','P8','P9')
THEN ' (mileage limited to 300 km)'
WHEN prodclassid IN ('P7')
THEN ' (mileage limited to 200 km)'
ELSE NULL
END
+
SPACE(5000 - LEN('Desc1'))
.......

View Replies !

Problem About Pass A Big String (over 8000 Characters) To A Variable Nvarchar(max) In Stored Procedure In SQL 2005!
Problem about pass a big string (over 8000 characters) to a variable nvarchar(max) in stored procedure in SQL 2005!
I know that SQL 2005 define a new field nvarchar(max) which can stored 2G size string.
I have made a stored procedure Hellocw_ImportBookmark, but when I pass a big string to @Insertcontent , the stored procedure can't be launch! why?
create procedure Hellocw_ImportBookmark @userId varchar(80), @FolderId varchar(80), @Insertcontent nvarchar(max)
as declare @contentsql nvarchar(max); set @contentsql=N'update cw_bookmark set Bookmark.modify(''declare namespace x="http://www.hellocw.com/onlinebookmark"; insert '+ @Insertcontent+' as last into (//x:Folder[@Id="'+@FolderId+'"])[1]'') where userId='''+@userID+''''; exec sp_executesql @contentsql;

View Replies !

Problem About Pass A Big String (over 8000 Characters) To A Variable Nvarchar(max) In Stored Procedure In SQL 2005!
Problem about pass a big string (over 8000 characters) to a variable nvarchar(max) in stored procedure in SQL 2005!

I know that SQL 2005 define a new field nvarchar(max) which can stored 2G size string.

I have made a stored procedure Hellocw_ImportBookmark, but when I pass a big string to @Insertcontent , the stored procedure can't be launch! why?

----------------------13-------------------------------------
create procedure Hellocw_ImportBookmark
@userId         varchar(80),
@FolderId       varchar(80),
@Insertcontent nvarchar(max)

as
declare @contentsql nvarchar(max);
set @contentsql=N'update cw_bookmark set Bookmark.modify(''declare namespace x="http://www.hellocw.com/onlinebookmark"; insert '+
                    @Insertcontent+' as last into (//x:Folder[@Id="'+@FolderId+'"])[1]'') where userId='''+@userID+'''';
exec sp_executesql @contentsql;

View Replies !

Execute SQl Task Return Decimal Type Result

I am trying to have an Excecute SQL Task return a single row result set executed on SQL Server 2005.

The query in the Execute SQL Task is:
select 735.234, 2454.123

I get a conversion error when trying to assign to SSIS variables of type Double.
I have nothing configured in the "Parameter Mapping" tab.
I have the two SSIS Double variables mapped to the Tesult Name 0 and 1 in the "Result Set" tab

I don't want to use a for loop enumerator since there is a single row returned.

I simply want to assign these two values to SSIS Double variables (double is the closest match)

I can't even hack this by converting the decimals as string and then using DirectCast to convert them to Double.

Thanks for the help

View Replies !

Using Stuff To Return A String Of Values From A Column Without A Cursor
I think it was Pat Phelan who posted a little trick here where he used the STUFF function to create a string fo values from a column without using a cursor.

I am starting a brand new project and I did my table design and I am awaiting a finalized requirements document to start coding and I thought I would spend a little time writing some code to autogenerate some generic one record at a time SELECT, INSERT,UPDATE and DELETE stored procedures. With the coming holiday things are getting quiet around here.

The code that is not working is listed below. It does not work. It returns Null. I suck.

DECLARE @column_names varchar(8000)

SET @column_names = ''

SELECT @column_names = STUFF(@column_names,LEN(@column_names),0,C.COLUMN_ NAME + ', ')
FROM INFORMATION_SCHEMA.COLUMNS C
WHERE TABLE_NAME = 'MyTable'

SELECT @column_names

little help?

View Replies !

How To Accept More Than 4000 Characters?
Hi all,I'm using MSSQL 2005, using the nvarchar(MAX) but it doesn't seems to take more than 4000 characters... Any idea why?Thank you,Kenny.

View Replies !

How To Run Sp_executesql With An Sql-statement > 4000 Characters
I need to run sp_executesql with an sql-statement thats longer than a nvarchar.

Does anyone know how to?

View Replies !

Expressions Using Values Of More Than 4000 Characters
Is it not possible for expressions to work with variables that contain more than 4000 characters? I've heard of a limitation of expressions and 4000 characters, but I thought this meant that the expression itself cannot be more than 4000 characters -- not that the expression cannot work with values of more than 4000 characters.

For example, I have a two variables:

Test1 (type = String):
Value = [Some really long string whose length is > 4000 characters]

Test2 (type = String, EvaluateAsExpression = True):
Expression = "-" + @[User::Test1] + "-"

Test2 throws the following error:

The variable User::Test1 contains a string that exceeds the maximum allowed length of 4000 characters.

Reading the variable "User::Test1" failed with error code 0xC0047100.

Why is there such a limitation when working with string values? This seems really restricting. Are there any possible workarounds?

Thanks in advance.
Jerad

View Replies !

How Many Result-rows Does Mssql Return Should Be Used Asynchronous Method To Use Mssql Cursor?
How many result-rows does mssql return should be used asynchronous method to use mssql cursor, can get the best performance in any time in any result offset?

i want to make the cursor fast in any time whatever how many results returned

View Replies !

On SQL Mobile 3.5 NTEXT Will Not Accept Data Above 4000 Characters... This Is Serious And Confirmed
When using SQL CE 3.5 and I try to insert data to DataSet with NTEXT type column that has more than 4000 characters I get this error

"InvalidOperationException was unhandled
@p4 : String truncation: max=4000, len=8414

I had not problem with SQL Compact 3.1 I didn't even changed DataSet I just upgraded my database 3.1 -> 3.5 and I get this error. This is serious bug....

There are other people who have the same problem
http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=812683&SiteID=17

View Replies !

Why For SQL 2000 Max Row Size Can Not Be More Than 8060 Characters (Japanese) Nvarchar(4000)
I want the reason for the above statement where I user nvarchar(4000)
to insert the japanese text it give the same error , why we cannot have
maximum size ? if we can have maximum size than 8060 what is the
setting

Please help me ..

Thanks in anticipation

Ashish Tahasildar

View Replies !

SSIS Expression Result Limit Of 4000 Bytes
I have a package with an input column that is varchar(8000).

I want to strip the first byte off of this column and put it in one result column and the remainder of the field I want to go to a second column. If the input column is an empty string, I want to return NULL.

Pulling the first byte off works fine, no issues, however putting the remainder of the input column into an output column is giving me a little trouble.

If I use this expression:

LEN(FLD1) == 0 ? NULL(DT_WSTR,1) : SUBSTRING(FLD1, 2, LEN(FLD1) - 1))

I get an error that says my expression evaluates to a string of greater than 4000 bytes.

If I do this instead:

LEN(FLD1) == 0 ? NULL(DT_WSTR,1) : RTRIM(SUBSTRING(FLD1, 2, 7999))

The expression passes muster but I get the warning that I will be truncating the column at 4000 bytes.

In actuality, I don't care if the result column is truncated after 4000 bytes. I find the second solution to be a bit clunky and I'm wondering if anyone can give me a reason why the first solution won't evaluate but the second will?

View Replies !

Fetch Cursor Help
Let's say i have 5 unique RRID's, column APID and ITID

RRID - APID - ITID
1 13 700
2 13 700
3 13 700
4 14 700
5 15 700

If I run the stored procedure below, I get the results above however, I want my result to be

RRID - APID - ITID
1 13 700
2 13 700
3 13 700
4 14 701
5 15 702

I want my cursor to loop at the same APID then assign one ITID then move to the next APID and so on...

Any help is highly appreciated....

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

ALTER PROCEDURE InsNewEmployeeImpTaskP2
@REID int,
@LOID int,
@RetValintoutput

AS

Declare @RRID int
Declare @APID int
Declare @intREID varchar(20)
Declare @intIMID varchar(20)

Declare crReqRec cursor for
select RRID from RequestRecords where REID = @REID and RRSTatus = 'AC' and APID is not null
open crReqRec
fetch next from crReqRec
into
@RRID

set @APID = (select APID from RequestRecords where REID = @REID and RRID = @RRID)

set @intIMID = (SELECT ImplementationGroup.IMID
FROM ImplementationGroup_Location INNER JOIN
ImplementationGroup ON ImplementationGroup_Location.IMID = ImplementationGroup.IMID INNER JOIN
Applications_ImplementationGroup ON ImplementationGroup.IMID = Applications_ImplementationGroup.IMID where APID = @APID and ImplementationGroup_Location.LOID = @LOID )

insert into ImplementationTasks
(
IMID,
ITStatus,
ITStatusDate
)
VALUES
(
@intIMID,
'2',
GetDate()
)
SET @RetVal = @@Identity
while @@fetch_status = 0
Begin

Update RequestRecords
set ITID = @RETVal, RRStatus = 'IA'
where REID = @REID and RRID = @RRID

FETCH NEXT FROM crReqRec
into
@RRID
end

close crReqRec
deallocate crReqRec

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

View Replies !

Can't Fetch Record From Cursor
Hi,
I'm relatively inexperienced in sql, and am having trouble interpreting the behavior of a cursor in some code I have inherited. When there is a record in both the Filters and FilterElements tables, the fetch_status is 0. If there is a record in Filters, but no child record in FilterElements, the fetch_status is -1. Since the tables are joined with a RIGHT OUTER JOIN, even when there is no corresponding record in FilterElements, a record is returned (I have verified running the select in a query window). But when used in a cursor, the record is not fetched. The fetch_status is -1. Can anyone tell me why the fetch doesn't work in this case. Thanks
----
DECLARE @CreatedByUser nchar(100), @WorkflowIDs varchar(50);
DECLARE @MyVariable CURSOR;
SET @MyVariable = CURSOR FOR
SELECT isnull(Filters.WorkflowIDs, ''),
isnull(FilterElements.CreatedByUser, '')

FROM Filters RIGHT OUTER JOINFilterElements ON Filters.ItemID = FilterElements.FiltersItemID
WHERE FiltersItemID = @FilterID;
OPEN @MyVariable;FETCH NEXT FROM @MyVariable
INTO @WorkflowIDs, @CreatedByUser;

View Replies !

MDAC 2.8 And Cursor FETCH NEXT
it seems that starting with MDAC 2.8 the FETCH NEXT statement for cursors requires an INTO clause. otherwise an unspecified error is returned.

so it's not possible to scroll through the records anymore without storing the values of the fields into local variables?

View Replies !

Help With Cursor And Fetch Statement
Hello,

I am hoping someone can help me with using the cursor and fetch functions. I have not used these features in the past and I am now stuck when trying to use IF statements with the fetch function.

I have a temp table populated with the below headers and their associated data.

The headers are as follows:
ItemRcvdKey, TranID, TranDate, QtyReceived, UnitCost, ItemKey, WhseKey, ItemID, ShortDesc, WhseID, QtyOnHand, StdCost.

The information contained in this temp table lists every single receipt of goods against all of our inventoried items. The QtyOnHand listed on each record is the total QtyOnHand for that item in that warehouse. What I need the fetch to do is grab the receipt of goods records, starting with the most recent TranDate, and pull them into the new temp table until the QtyOnHand is reached. The QtyonHand it should be comparing too is the one listed on the first fetched record. Once the Sum of the QtyRcvd is equal to or is greater than the QtyOnHand for that item I need the fetch to move on to the next item number and perform the same function.

One thing I need to be clear on is that if there are 3 Receipt Records(TranID) for Item A in Warehouse A, the total QtyOnHand will be listed 3 times. I need to make sure that the Fetch is comparing all the records for Item A in Warehouse A to one instance of the QtyOnHand.

The other aspect is that there will be receipt of goods for the same item in multiple warehouses. So I also need the Fetch to be sure that when it is grabbing records and putting them in the temp table, it makes sure it is matching the ItemID and the WhseID with the record it started with.

The current script I have written is below. If you can offer any help I would greatly appreciate it.

Code SnippetDeclare @ItemID VarChar(30),
        @QtyOnHand Decimal (16,8),
        @WhseID VarChar (6),
        @SumRcvd Int,
        @TranID VarChar(30),
        @TranDate DateTime,
        @QtyRcvd Decimal (16,8),
        @UnitCost Decimal (16,8),
        @ItemKey Int,
        @WhseKey Int,
        @ShortDesc VarChar (40),
        @StdCost Decimal (16,8)

        DECLARE Temp_cursor CURSOR FOR
            SELECT TranID, TranDate, QtyRcvd,
                UnitCost, ItemKey, WHseKey,
                ItemID, ShortDesc, WhseID,
                QtyOnHand, StdCost
            FROM #Temp1 tem

    OPEN Temp_cursor

        FETCH NEXT FROM Temp_cursor
            INTO @TranID, @TranDate, @QtyRcvd,
            @UnitCost, @ItemKey, @WHseKey,
            @ItemID, @ShortDesc, @WhseID,
            @QtyOnHand, @StdCost


    WHILE  @@FETCH_STATUS = 0

    BEGIN -- 0

        Insert Into #Temp3
        (TranID, TranDate, QtyRcvd,
        UnitCost, ItemKey, WHseKey,
        ItemID, ShortDesc, WhseID,
        QtyOnHand, StdCost)

        Values
        (@TranID, @TranDate, @QtyRcvd,
        @UnitCost, @ItemKey, @WHseKey,
        @ItemID, @ShortDesc, @WhseID,
        @QtyOnHand, @StdCost)

    FETCH NEXT FROM Temp_cursor
    INTO @TranID, @TranDate, @QtyRcvd,
        @UnitCost, @ItemKey, @WHseKey,
        @ItemID, @ShortDesc, @WhseID,
        @QtyOnHand, @StdCost

View Replies !

Fetch Result Of Sp_WHO
Hello All,
I need to find out which user are connected to a What database. For this I can Use SP_WHo. But How can i fetch the required fields only dat too in Query Analyzer

Thank you in advance

View Replies !

Multiple While Fetch Cursor Code
I seem to have a few problems with the below double cursor procedure. Probably due to the fact that I have two while loops based on fetch status. Or???

What I want to do is select out a series of numbers in medlemmer_cursor(currently set to only one number, for which I know I get results) and for each of these numbers select their MCPS code and gather these in a single string.

For some reason the outpiut (the insert into statement) returns the correct number 9611 but the second variable @instrumentlinje remains empty.

If I test the select clause for 9611, it gets 4 lines. So to me its like the "SELECT @instrumentlinje = @instrumentlinje + ' ' + @instrument" statement doesn't execute.

DELETE FROM ALL_tbl_instrumentkoder

DECLARE @medlem int
DECLARE @instrument varchar(10)
DECLARE @instrumentlinje varchar(150)

DECLARE medlemmer_cursor CURSOR FOR
SELECT medlemsnummer
FROM ket.ALL_tbl_medlemsinfo (NOLOCK)
WHERE medlemsnummer = 9611

DECLARE instrumenter_cursor CURSOR FOR
SELECT [MCPS Kode]
FROM Gramex_DW.dbo.Instrumentlinie (NOLOCK)
WHERE Medlemsnummer = @medlem

OPEN medlemmer_cursor

FETCH NEXT FROM medlemmer_cursor INTO @medlem

WHILE @@FETCH_STATUS = 0
BEGIN

OPEN instrumenter_cursor
FETCH NEXT FROM instrumenter_cursor INTO @instrument

WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @instrumentlinje = @instrumentlinje + ' ' + @instrument
FETCH NEXT FROM instrumenter_cursor INTO @instrument
END

CLOSE instrumenter_cursor

INSERT INTO ALL_tbl_instrumentkoder VALUES(@medlem, @instrumentlinje)

FETCH NEXT FROM medlemmer_cursor INTO @medlem

END

CLOSE medlemmer_cursor
DEALLOCATE medlemmer_cursor
DEALLOCATE instrumenter_cursor

View Replies !

Dynamic Execution Of Cursor Fetch
I'm trying to write code which will fetch records from a dynamically created cursor. Therefore the cursor name is not known at design time.

Unfortunately applying an enclosing the fetch command in quotes and then calling the execute method with this string will not work.

e.g.

declare @A varchar(10),
@B varchar(10),
@sFetch varchar(255)

--Open the cursor
Execute ('Open ' + crAnyCursor)

--create fetch string
Select @sFetch = 'Fetch Next From' + crAnyCursor + 'into @A, @B'

execute (@sFetch)

The following error is returned...
Msg 137, Level 15, State 1
Must declare variable '@A'.

Is there another means of working with dynamically executed cursors?

Thanks
JohnG

View Replies !

Will A Cursor Fetch Lock The Table
Open a cursor , Fetch the record ,
during this kind of operation , will the specific table be locked and fail
to be updated or select by another session ?

View Replies !

Possible To Fetch Next From Cursor Into Table Variable?

Hello,

I have searched the net for an answer but could not find one. When I declare a table variable
and then try to insert fetched row into the table variable like:

Code Snippet
declare @table table (col1 nvarchar(50), col2 nvarchar(50))
declare curs for
select * from sometable
open curs
fetch next from curs into @table

it does not work. any help would be great.

thnx

View Replies !

How 2 Fetch Result Of A Qry Whn Exeuted Using Exec
Hi Folks,

declare @strDBName as varchar(100)
set @strDBName = 'MYHRMIS_WEBEX'
declare @EmpCode as varchar(50)
set @EmpCode = '60874'
declare @SQL as varchar(8000)
set @SQL = ' select count(*) from ' + @strDBName + '.dbo.Reqrec_EmployeeDetails where ed_empcode = ''' + @EmpCode + ''' and ed_status in (1, 4) '
print @SQL
exec(@SQL)

How do I get the result of this query into a variable.
Thanks in advance.

View Replies !

Fetch Results Of A Cursor To A Temporary DB For Manipulation
Hi all

I want to put the fetch results of a cursor to a temporary DB for manipulation, Im selecting all columns from the table in the cursor and the number of total columns is unknow.

Please guide me on how this could be done...

Thanks in advance

Regards

Benny

View Replies !

It Is Very Slow At Updating By Use Cursor (fetch Method)
Hi all,

I got a problem. I am working on DTS package. The last step is updating a table field. I wrote a stored procedure as below:

CREATE PROCEDURE [Update_product_manufacturer] AS

Declare @product_id int
Declare @supplier_name VarChar (255)

Declare ValueCursor Cursor For

select product.product_id, [P21_SUPPLIER_id_name_ke].[supplier_name]
from [VARIANT],[P21_INV_MAST_uid_itenID_weight_ke],[product],
[P21_INVENTORY_SUPPLIER_uid_supplierID_price_cost_k e],[P21_SUPPLIER_id_name_ke]
where
[product].product_id = [VARIANT].[product_id]
and
[P21_INV_MAST_uid_itenID_weight_ke].[item_id]=[VARIANT].[SKU]
AND
[P21_INV_MAST_uid_itenID_weight_ke].[inv_mast_uid]=[P21_INVENTORY_SUPPLIER_uid_supplierID_price_cost_k e].[inv_mast_uid]
AND
[P21_SUPPLIER_id_name_ke].[supplier_id]=[P21_INVENTORY_SUPPLIER_uid_supplierID_price_cost_k e].[supplier_id]
order by [product].[product_id]
for read only

Open ValueCursor
while (0 = 0)
begin
fetch next
from ValueCursor
Into @product_id, @supplier_name

update product
set manufacturer = @supplier_name
where product_id = @product_id

end

close ValueCursor
Deallocate ValueCursor

Notes: Table: Product has 28,000 rows, other tables with 28,000 - 56,000 rows

it's been 2 hours, the job is still working.

Who has this kind of experience? How can I make updating quickly?

Thanks,

Kevin Zhang

View Replies !

Hi, Please Help Me W/ Fetch Cursor Querying, This Sends Email Automatically
create    proc emailnew

as
declare @From varchar(8000)
declare @Subject varchar(8000)
declare @Body varchar(4000)
declare @smtp varchar(8000)
declare @counter int, @tbl varchar(8000)
Declare @MailID int
Declare @hr int
Declare @To varchar(8000)
Declare @tblquery varchar(8000)
declare @id int, @deptemail varchar(8000), @tmpmth varchar(8000), @usedb varchar(8000)
set @from = 'name@mail.com'
set @subject = 'testheader'
set @body = 'testing successful'
set @smtp = 'smtp.com'

--=========================================================================
--============================    get database name       =======================
IF (LEN(MONTH(GETDATE())) = 1)
   BEGIN
      Set @TmpMth = '0' + CAST(MONTH(GETDATE()) AS varchar(2))    --01
   END
   ELSE
   BEGIN
      Set @TmpMth = CAST(MONTH(GETDATE()) AS varchar(2))    --12
   END
   SET @UseDB = 'DATA' + CAST(YEAR(GETDATE()) AS varchar(4)) + @TmpMth    --aia_DATA200712
--===================================================================
--============================   get table number       =======================
set @counter = 1
while @counter >= 59
begin
    IF (LEN(@counter) = 1)
       BEGIN
          Set @Tbl = '0' + @counter    --01
       END
       ELSE
       BEGIN
          Set @Tbl = @counter    --12
       END

--=========================check if table being created exists
IF EXISTS (SELECT 1
       FROM information_schema.schemata
       WHERE catalog_name = 'temptable')
GOTO table_1
--=================================== get all email accounts =====================
set @tblquery = '
select ID, Email
INTO temptable
FROM
(
select distinct d.id, d.email from tt32_aia.dbo.department d right outer join tt32_aia.dbo.extension e ON (e.parentid = d.id) right outer join
' + @usedb + '.dbo.other' + @tbl + ' data ON (e.extn = data.extn) where callclass = '''' or callclass is null
UNION ALL select distinct d.id, d.email from tt32_aia.dbo.department d right outer join tt32_aia.dbo.extension e ON (e.parentid = d.id) right outer join
' + @usedb + '.dbo.inward' + @tbl + ' data ON (e.extn = data.extn) where callclass = '''' or callclass is null
UNION ALL select distinct d.id, d.email from tt32_aia.dbo.department d right outer join tt32_aia.dbo.extension e ON (e.parentid = d.id) right outer join
' + @usedb + '.dbo.local' + @tbl + ' data ON (e.extn = data.extn) where callclass = '''' or callclass is null
UNION ALL select distinct d.id, d.email from tt32_aia.dbo.department d right outer join tt32_aia.dbo.extension e ON (e.parentid = d.id) right outer join
' + @usedb + '.dbo.other' + @tbl + ' data ON (e.extn = data.extn) where callclass = '''' or callclass is null
)data
DECLARE deptemail_cursor CURSOR FOR select id, email
from temptable where id is not null
'

--=============================================================================
--====just above you can see the cursor.. below is sending the emails
--======================================================================

exec(@tblquery)

OPEN deptemail_cursor

   FETCH NEXT FROM deptemail_cursor INTO @id, @deptemail

   WHILE @@FETCH_STATUS = 0
   BEGIN
      --If LEN(@deptemail) > 0
      --BEGIN
     set @to = @deptemail
    EXEC @hr = sp_OACreate 'CDo.message', @MailID OUT --CDo.message |CDONTS.NewMail   <-- different mail server
    EXEC @hr = sp_OASetProperty @MailID ,
    'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'
    EXEC @hr = sp_OASetProperty @MailID ,
     'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', @smtp
    EXEC @hr = sp_OASetProperty @MailID , 'From', @From
    EXEC @hr = sp_OASetProperty @MailID , 'HTMLBody', @Body
    EXEC @hr = sp_OASetProperty @MailID , 'Subject', @Subject
    EXEC @hr = sp_OASetProperty @MailID , 'To', @to
    EXEC @hr = sp_OAMethod @MailID , 'Send', NULL
    EXEC @hr = sp_OADestroy @MailID
      --END
      FETCH NEXT FROM deptemail_cursor INTO @id, @deptemail
END

CLOSE deptemail_cursor
DEALLOCATE deptemail_cursor

Table_1:
    drop table temptable
return

set @counter = @counter + 1

end

this is suppose to send email automatically to every email account that it will get from all the tables (around 250 tables).
the problem is its not sending, but if i try to take my code outside of the "SET @COUNTER = @COUNTER + 1 END" and close the if statement above, i can produce the correct result.. i'm thinking maybe its the positioning? but, there could be some overhauling needed to do with this.. sorry for posting the sp. sorry for the trouble.. please help me

View Replies !

How To Pass Pass The Parameter In SQL Command In SSIS Package
Hi

We already used Oracle Datasatage Server the following Query statement for Source and Lookup.here there is parameter maping in the SQl Statement . How can achive in SSIS the Folowing Querystatment?

Query 1: (source View Query)
SELECT
V_RDP_GOLD_PRICE.GDR_PRODUCT_ID, V_RDP_GOLD_PRICE.ASSET_TYPE, V_RDP_GOLD_PRICE.PREFERENCE_SEQ, V_RDP_GOLD_PRICE.RDP_PRICE_SOURCE, TO_CHAR(V_RDP_GOLD_PRICE.PRICE_DATE_TIME,'YYYY-MM-DD HH24:MI:SS'), TO_CHAR(V_RDP_GOLD_PRICE.REPORT_DATE,'YYYY-MM-DD HH24:MI:SS'), V_RDP_GOLD_PRICE.SOURCE_SYSTEM_ID
FROM
V_RDP_GOLD_PRICE V_RDP_GOLD_PRICE
WHERE
REPORT_DATE = (select max(report_date) from V_RDP_GOLD_PRICE where source_system_id = 'RM' )

Query 2: (look up )

SELECT
GDR_PRODUCT_ID,
TO_CHAR(MAX(PRICE_DATE_TIME),'YYYY-MM-DD HH24:MI:SS') ,
TO_CHAR(REPORT_DATE,'YYYY-MM-DD HH24:MI:SS')
FROM
V_RDP_GOLD_PRICE
where
GDR_PRODUCT_ID = :1 and
report_date = TO_DATE(:2,'YYYY-MM-DD HH24:MI:SS') AND
PRICE_DATE_TIME BETWEEN TO_DATE(:2,'YYYY-MM-DD HH24:MI:SS') - 7) AND TO_DATE(:2,'YYYY-MM-DD HH24:MI:SS')
GROUP BY GDR_PRODUCT_ID, TO_CHAR(REPORT_DATE,'YYYY-MM-DD HH24:MI:SS')

please anyone give the sample control flow and how to pass the parameter?

Thanks & regards

Jeyakumar.M

View Replies !

Frror: 4000 Max String Literal In Expression For Datareader Component
Hi, I have a datareader component of which i am dynamically setting its sqlcommand statement with expression (click the background of dataflow > properties > expressions). Now my sql select statement has about 600 fields so that makes my expression statment "select field1, field2, .....from table1 where field2 >=" + @[User::dateforfield2] but when i evalute the expresssion (which is right), i get the error: A string literal in the expression exceeds the maximum allowed length of 4000 character and i think its because of the fields in my select statment causing my string literal to grow more than 4000 characters. Is there any way to increase the max string literal for expressions. Please help.

View Replies !

OLEDB Data Source Limit Lenght Of String To 4000

Hello,
I have 2 table, "table1" is the source one and the other one "table2" is the destination.
Columns in Table1 and in Table2 are nvarchar(max).
Data loaded from table1 is performed by SSIS OLEDB data flow source, I have found out that opening "Data flow Path Editor" in the Metadata, columns are as: DT_WSTR with lenght 4000.

First question:
Why SSIS limit the column to 4000.

Then I get some error for this issue, with the error:
input column "col1" (xxxx) and reference column named (coln) have incompatible data type.

As written before both columns are string, the problem is that SSIS limit the lenght of the string to 4000.

How can I solve this issue?

Thank

View Replies !

SQL Server 2005 JDBC Driver 1.1 Truncate Output Parameter From A Stored Procedure To 4000 Char.
I have a procedure that uses varchar(max) as output parameter, when I tried to retrieve

the result of calling the procedure, the result is truncated to 4000 character. Is this a driver bug?

Any workaround?

Here is the pseudo code:

create Procedure foo(@output varchar(max))

{

    set @foo = 'string that has more than 4000 characters...';

    return;

}

Java code:

    CallableStatement cs = connection.prepareCall("{call foo ?}");

   cs.registerOutputParameter(1, Types.longvarchar);   // also tried Types.CLOB.

   cs.execute();

  String result = cs.getString(1); // The result is truncated to 4000 char.

   -- Also tried

CLOB clob = cs.getClob(1);

long len = clob.length();   // The result is 4000.

Thanks,

Eric Wang

View Replies !

Conditional Execute By Execute SQL Task Return Value?
I have a SSIS package contains an "Execute SQL Task". The SQL will raise error or succeed. However, it sounds the package won't pick up the raised error?

Or is it possible to conditional run other control flow items according the the status of SQL task execution?

View Replies !

Need To Pass Result Set To Other Packages
We have an SSIS package that executes a query to a DB2 database and stores the result set in a variable of type object. The result set is typically ten rows or less. Several steps in the package loop through these rows and do various processing based on the contents of the rows.

We now have the need for other packages to have access to the original result set. These packages might be run minutes or hours after the first package. Is there a simple way to save the original result set in a file and then pick it up for processing later? Unfortunately, using a SQL Server table to store the results is not an option for us at this time.

Thanks,
Marty

View Replies !

&"Cursor-Fetch&" Problem:Oracle2SQL Server Migration
Dear all,

I have a procedure in Oracle that contains the following cursor:

CURSOR SCHED_TRIPS IS
SELECT TRAVELDATE, STOP_NUM, TRIPID, STOP_TYPE, PROMISED_TIME, ETA, PERFORM_TIME, DEPART_TIME, ETD, DRIVERWAIT, PASSENGERWAIT, TRIPTIME, GROUP_ID
FROM Dbo.SCHEDTRIPS_VIEW
WHERE UNQ_ID = SESSION_ID AND TRUNC(TRAVELDATE) = TRUNC(TDATE)
AND DISPOSITION <> 'V';
BEGIN
FOR S IN SCH_TRIPS LOOP
UPDATE dbo.SCHEDULES T
SET T.DIRTYBIT = 1
WHERE T.TRIPID = S.TRIPID AND T.STOP_TYPE = S.STOP_TYPE AND (T.STOP_NUM <> S.STOP_NUM OR T.ETA <> S.ETA);

UPDATE dbo.SCHEDULES T
SET T.STOP_NUM = S.STOP_NUM, T.PROMISED_TIME = S.PROMISED_TIME, T.ETA = S.ETA, T.ETD = S.ETD, T.LAST_CHANGED = SYSDATE
WHERE T.TRIPID = S.TRIPID AND T.STOP_TYPE = S.STOP_TYPE;
END LOOP;
COMMIT ;
END;

My problem is with the line shown in Red. What will be the T-SQL equivalent for this line.

Anxiously waiting for help!

View Replies !

Ntext Over 4000 Chars Causes 'Data In Row (n) Was Not Update... String Or Binary Data Would Be Truncated...'
When I enter over 4000 chars in any ntext field in my SQL Server 2005 database (directly in the database and through the application) I get an error saying that the data could not be updated because string or binary data would be truncated.Has anyone ever seen this? I cannot figure out what is causing it, ntext should be able to hold a lot more data that this...

View Replies !

How Do I Pass A Result Set Into An SQL Task For An Insert?

Hello,

I am new to SSIS so I'm sure this is easy to do but I can't seem to figure it out. I created a SQL task that creates a result set. I would like to loop through each result of the result set and insert it into another table.

I'm not sure how to map the result set as input to the second SQL task that will do the insert. I can put the variable in the parameter mapping but I don't know what the SQL should look like to insert this into my table. Each row of the result set has five columns. Normally I have something that looks like INSERT...Values(?,?,?,?,?) but because my input parameter is just one result set object this doesn't work.

Help?

View Replies !

Pass Linked ServerName To A Cursor Issue...
Need to loop through a Cursor to linked server:
-----------------------------------------------
Declare Cursor_Loop_serverName Cursor for
select cast(name as varchar(30)) name, cast(dbID as varchar(5)) dbID,
cast(crdate as varchar(25)) crdate
from ServerName_A.master.dbo.sysdatabases

***How could I pass @serverName to change the from to
from @RemoteServer.master.dbo.sysdatabases?
I have tried dynamic sql, it did not work after the Declare Cursor for...

thanks for the help
David

View Replies !

How To Call A Stored Procedure In T-SQL And Pass It To A Cursor
Hi,

I have a kind of problem. In SQL Server I have a stored procedure ressembling this:

Code Block
ALTER PROCEDURE procedure1
(

@param int
)

SELECT * FROM table WHERE param = @param

Now I want to call this procedure and pass it to a cursor. We all know you can do this:

Code Block

DELCARE cursor1 CURSOR for
SELECT * FROM table WHERE param = @param

.. , but I want something like this:

Code Block
DECLARE cursor1 CURSOR for
EXEC procedure1 @param

Is it possible? I could solve it in another, but then I have to connect 2x to the database, which is less performant.

I have also tried something like this:

Code Block
ALTER PROCEDURE procedure1
(

@param int
)
SELECT @test = id FROM table WHERE param = @param
RETURN @test

ALTER PROCEDURE procedure2
(

@param int
)
DECLARE @var varchar(100)
EXEC @var = procedure1 @param

But then it returns always 0.

So is there a way to pass a procedure's select to a cursor?

Thanks in advance

Stevevil0

View Replies !

Truncation Of Characters In A Query Result
Hi!
The product description table contains a field,
long_desc varchar(8000). Is there any way to get the entire description? The query, 'select long_desc from product_desc' always truncates characters more than 255 characters long. Even exporting the data to an Excel spreadsheet using DTS does the same thing.

Thank you
Praveena

View Replies !

Is It Possible To Put The Result From A Sp Into A Cursor
I am tring to find out wheather a SQL Agent job is running.
I was wanting to put the result from sp_help_job into a cursor and check whether @@fetch_status was successful.
if it fails then job is not running

the below code fails because when declaring a cursor expects a select statement.

is there anyway to get result from a sp into a cursor thanks

declare @sql varchar(100)
set @sql = 'sp_help_job null, ''event_167'', @job_aspect = ''job'', @execution_status = 1'
declare Job_Cursor cursor for
sp_help_job @job_name = 'some_job_name', @job_aspect = 'job', @execution_status = 1
open Job_Cursor
fetch next from Job_Cursor
if @@fetch_status <> 0
begin
print 'Job is not runnning'
end
if @@fetch_status = 0
begin
print 'Job is runnning'
end
close Job_Cursor
deallocate Job_Cursor

View Replies !

Using The Result Of A Cursor
I am trying to build a Windows application using: Windows XP Pro ; VS Pro 2005, C# and SQL2005.

I have a database with two tables:
eg
1) myGameRecency which contains columns : GameId , Date, Zero, One, Two, Three
2) myGameFrequency which contains a column : weeksSinceLastDrawn
Using the myGameRecency table ---
I wish to Fetch the GameId (which is just a counter) which corresponds to the last time a 0 was entered into that column....known in the programme as @lastTimeDrawn
I also wish to Fetch the GameId which corresponds to the current date....known in the programme as @currentTimeDrawn
I then wish to subtract @lastTimeDrawn from @currentTimeDrawn and place that result in the myGameFrequency table.

I have tried a variety of code and last tried the following:

Can anyone please assist?
Thank you.
lpbcorp

if (Ball1 == true)

{

sqlCmd.CommandText = "DECLARE @currentTimeDrawn int " +
                                     "DECLARE @lastTimeDrawn CURSOR " +

                                     "DECLARE lastrow_cursor CURSOR FOR " +

                                     "(SELECT GameId FROM " + DBGameName.ToString() + "Recency WHERE One = 0) " +

                                     "OPEN lastrow_cursor " +

                                     "FETCH LAST FROM lastrow_cursor " +

                                     "SET @lastTimeDrawn = lastrow_cursor " +

                                 "SET @currentTimeDrawn = (SELECT GameId FROM " + DBGameName.ToString()

+ DBGameName.ToString() + "Recency WHERE Date = '" + Date + "') " +

"UPDATE " + DBGameName.ToString() + "Frequency SET WeeksSinceDrawnMainDrawBall = @currentTimeDrawn - @lastTimeDrawn WHERE Ball = 1 " +

"CLOSE lastrow_cursor " +

"DEALLOCATE lastrow_cursor";

sqlCmd.ExecuteScalar();

}

break;

View Replies !

How To Pass Result From Case Statement To An Equation?
How to get the CASE results highlighted in BOLD into this equation; "(LogOut - LogIn) + (LunchBreak) -(AMBreak) + (PMBreak) AS TimeWorked" ?
Thank you.
CREATE VIEW dbo.vwu_ReportViewASSELECT EmployeeID , LastName , FirstName , LocationCode , UserID , Today , Login , AMBreakOut , AMBreakIn , CASE WHEN ISNULL(DATEDIFF(Minute, AMBreakOut, AMBreakIn),0) >= 0 AND ISNULL(DATEDIFF(Minute, AMBreakOut, AMBreakIn),0) <= 19 THEN '0' WHEN ISNULL(DATEDIFF(Minute, AMBreakOut, AMBreakIn),0) >= 20 AND ISNULL(DATEDIFF(Minute, AMBreakOut, AMBreakIn),0) <= 34 THEN '15' WHEN ISNULL(DATEDIFF(Minute, AMBreakOut, AMBreakIn),0) >= 35 AND ISNULL(DATEDIFF(Minute, AMBreakOut, AMBreakIn),0) <= 49 THEN '30' WHEN ISNULL(DATEDIFF(Minute, AMBreakOut, AMBreakIn),0) > = 50 AND ISNULL(DATEDIFF(Minute, AMBreakOut, AMBreakIn),0) <= 64 THEN '45' ELSE '60' END AS AMBreak , LunchOut , LunchIn , CASE WHEN ISNULL(DATEDIFF(Minute, LunchOut, LunchIn),0) >= 0 AND ISNULL(DATEDIFF(Minute, LunchOut, LunchIn),0) <= 66 THEN '0' WHEN ISNULL(DATEDIFF(Minute, LunchOut, LunchIn),0) >= 67 AND ISNULL(DATEDIFF(Minute, LunchOut, LunchIn),0) <= 81 THEN '15' WHEN ISNULL(DATEDIFF(Minute, LunchOut, LunchIn),0) >= 82 AND ISNULL(DATEDIFF(Minute, LunchOut, LunchIn),0) <= 96 THEN '30' WHEN ISNULL(DATEDIFF(Minute, LunchOut, LunchIn),0) >= 97 AND ISNULL(DATEDIFF(Minute, LunchOut, LunchIn),0) <= 111 THEN '45' WHEN ISNULL(DATEDIFF(Minute, LunchOut, LunchIn),0) >= 112 AND ISNULL(DATEDIFF(Minute, LunchOut, LunchIn),0) <= 126 THEN '60' ELSE '75' END AS LunchBreak, PMBreakOut , PMBreakIn , CASE WHEN ISNULL(DATEDIFF(Minute, PMBreakOut, PMBreakIn),0) >= 0 AND ISNULL(DATEDIFF(Minute, PMBreakOut, PMBreakIn),0) <= 19 THEN '0' WHEN ISNULL(DATEDIFF(Minute, PMBreakOut, PMBreakIn),0) >= 20 AND ISNULL(DATEDIFF(Minute, PMBreakOut, PMBreakIn),0) <= 34 THEN '15' WHEN ISNULL(DATEDIFF(Minute, PMBreakOut, PMBreakIn),0) >= 35 AND ISNULL(DATEDIFF(Minute, PMBreakOut, PMBreakIn),0) <= 49 THEN '30' WHEN ISNULL(DATEDIFF(Minute, PMBreakOut, PMBreakIn),0) >= 50 AND ISNULL(DATEDIFF(Minute, PMBreakOut, PMBreakIn),0) <= 64 THEN '45' ELSE '60' END AS PMBreak , Logout , Comments , LoginLogon , AMBreakOutLogon , AMBreakInLogon , LunchOutLogon , LunchInLogon , PMBreakOutLogon , PMBreakInLogon , LogoutLogon ,(LogOut - LogIn) + (LunchBreak) -(AMBreak) + (PMBreak) AS TimeWorked

View Replies !