Infinite Loop In BOL Code!?

Apr 15, 2004

This code is from BOL (in index type: "DDLs-SQL Server"):
Take a look at error handling .. what happens if one of the three cmd.execute within the [Done:] hanlde fails?

It looks to me like we would have an infinite loop! .. am I missing something here?

BOL CODE EXAMPLE:
-----------------------------
Dim Cn As New ADODB.Connection
Dim Cmd As New ADODB.Command

' If the ADOTestTable does not exist, go to AdoError.
On Error GoTo AdoError

' Connect using the SQLOLEDB provider.
cn.Provider = "sqloledb"
cn.Properties("Data Source").Value = "MyServerName"
cn.Properties("Initial Catalog").Value = "northwind"
cn.Properties("Integrated Security").Value = "SSPI"
cn.Open

' Set up command object.
Set Cmd.ActiveConnection = Cn
Cmd.CommandText = "DROP TABLE ADOTestTable"
Cmd.CommandType = adCmdText
Cmd.Execute

Done:
Cmd.CommandText = "SET NOCOUNT ON"
Cmd.Execute
Cmd.CommandText = "CREATE TABLE ADOTestTable (id int, name char(100))"
Cmd.Execute
Cmd.CommandText = "INSERT INTO ADOTestTable values(1, 'Jane Doe')"
Cmd.Execute
Cn.Close
Exit Sub

AdoError:
Dim errLoop As Error
Dim strError As String

' Enumerate Errors collection and display properties of
' each Error object.
Set Errs1 = Cn.Errors
For Each errLoop In Errs1
Debug.Print errLoop.SQLState
Debug.Print errLoop.NativeError
Debug.Print errLoop.Description
Next

GoTo Done

End Sub
-------------------------------------------------------

View 4 Replies


ADVERTISEMENT

Infinite Loop In Cursor

Jul 5, 2006

Hi

I have an infinite loop in a trigger I and I cant reslove it.

In my system the user updates a stock table from the GUI and on the update I need to check values to see if I need to add records to a StockHistory table.  For Example:  If the user changes the grade of Product X from A to B then I need to add a new line in StockHistory for product X grade A that decrements the total number of products in the warehouse.  Similary I need to increase the quantity of stock for Product X grade B.

I had the trigger working for single updates but now when stock is added to the database (from another db) it has status of 'New'.  This isn't actually 'in stock' until the user sets the status to 'Goods In'.  This process will then update the status for all records in the category.  This caused my trigger to fail as the 'inserted' table now contains many records.

Now the problem I have is the trigger is in an infinite loop. It always shows the id of the first record it finds and the @Quantity values increases as expected.  I've taken all my procesing code out of the trigger and adding some debugging stuff but it still doesnt work:

CREATE TRIGGER [StockReturns_on_change] ON [dbo].[StockReturns]
FOR UPDATE
AS

DECLARE INDIVIDUAL Cursor --- Cursor for all the rows being updated

FOR
SELECT Id FROM inserted

OPEN INDIVIDUAL

FETCH NEXT FROM INDIVIDUAL INTO @Id

select @Quantity = 1

print @@FETCH_STATUS
print @Id
print @Quantity

WHILE @@FETCH_STATUS = 0
begin 

select @Quantity = @Quantity + 1

print @@FETCH_STATUS
print @Id
print @Quantity

-- Get the next row from the inserted table
FETCH NEXT FROM INDIVIDUAL INTO @Id

End  -- While loop on the cursor

-- no close off the cursors
CLOSE INDIVIDUAL
DEALLOCATE INDIVIDUAL





Can you help me please?

Kind Regards

View 13 Replies View Related

Cursor Causing Infinite Loop

Dec 13, 2007

Hi i have a cursor in a Stored Procedure. The problem is that it's poiting to the first row and causing an infinite loop on it.
How can i stop this and make it go to all rows. Here is my code.

Declare @CountTSCourtesy int
Declare @WaiterName nvarchar(100), @CursorRestaurantName nvarchar (100)
Declare waiter_cursor CURSOR FOR

SELECT new_waiteridname, new_restaurantname
FROM dbo.FilteredNew_CommentCard
Where new_dateofvisit between @FromDate and @ToDate and new_restaurantname = @Restaurant
Open waiter_cursor
FETCH NEXT FROM waiter_cursor
into @WaiterName,@CursorRestaurantName
While @@FETCH_STATUS=0

BEGIN
Exec WaitersCountExCourtesy @WaiterName,@CursorRestaurantName

END
Close waiter_cursor
Deallocate waiter_cursor
END


Thanks in advance...

View 1 Replies View Related

T-SQL (SS2K8) :: Simple Cursor Runs Infinite Loop?

Dec 23, 2014

I'm trying to build a simple cursor to understand how they work. From the temp table, I would like to print out the values of the table, when I run my cursor it just keeps running the output of the first row infinitely. I just want it to print out the 7 rows in the table ...

IF OBJECT_ID('TempDB..#tTable','U') IS NOT NULL
DROP TABLE #tTable
CREATE TABLE #tTable

[Code]....

View 2 Replies View Related

Executing SSIS Package Using Jobs In SQL Server Agent Infinite Loop

Jan 4, 2008

I'm trying to execute my package using schedule in SQL Server Agent, I've already tested my package by run it manually in Integration services and it works. The table created, the data from my flat file also inserted into the table correctly and the result return with success.

The question is why when I execute my package using SQL Server Agent, the SQL Server Agent keep executing my package like infinite loop until I stop the job. after I stop the job there is no error generated by sql server. Could you figured why this happen?? I've already tried to upgrade into SP2 and set the package protection level and still not get good result from it. thank you.

View 11 Replies View Related

Help With Trigger Code (loop)

Jun 4, 2007

Hello all
I've been thrown in at the deep end by the manager and asked to use a SQL database for a new site. I've managed to get most of the code working but are having trouble with this particular one.
The idea is that a user can log a fault (online) and that this gets added to a table. Once the data is added a trigger fires that takes this new data and loops through an "e-mail alert" table containing settings for different department managers and then stores the relevant manager name in a separate table (e-mail sent fromt this table to the manager) once the department and fault id's match.
 I have the following code (which is by no means good) in my trigger - did I says this is my first outing into SQL land!!ALTER TRIGGER [dbo].[usd_emailalertqueue]
ON [dbo].[FaultPostings] AFTER INSERT
AS
-- declare variables from new fault table
DECLARE @iDeptID intDECLARE @iFaultID intDECLARE @iReferenceID int
-- declare variables from email alerts tableDECLARE @Managername nvarchar(255)DECLARE @DeptID intDECLARE @FaultID intDECLARE @ReferenceID int
-- Read in values new fault tableSELECT @sParam = FaultDescription FROM INSERTEDSELECT @iFaultID = FaultID FROM INSERTEDSELECT @iDeptID = DeptID FROM INSERTEDSELECT @iReferenceID = ReferenceID FROM INSERTED
 
BEGIN
-- Read values from saved e-alertsSELECT @Managername = Managername FROM [dbo].[emailAlerts]SELECT @DeptID = DeptID FROM [dbo].[emailAlerts]SELECT @FaultID = FaultID FROM [dbo].[emailAlerts]
 
INSERT INTO EmailAlertQueue(ReferenceID,Managername)SELECT  @iReferenceID, @Managername
FROM [dbo].[emailAlerts] WHERE (@iDeptID = @Deptid) AND (@iFaultID = @Faultid) then  END
At the moment, this is what happens when a fault is added:  the EmailAlertQueue table gets populated with the name of the last managername from the emailAlerts table several times (only if the Dept and Fault id's macth otherwise nothing gets populated.. e.g. if there at 4 alerts setup in the EmailAlerts table with different dept and fault ID's but the last one matches the criteria the emailAlerts table is uotaed with 4 rows containing the managername.  (Hope this makes sense).
 I think I need to have some sort of loop but so far as I can tell a "For Each" loop doesn't work in triggers (i could be wrong though).
(I'm not even sure if this is the correct approach to take)
As usual, any help is appreciated.
 Thanks
 Chooch

View 3 Replies View Related

Stored Procedure Loop Not Working, Please Advise, Code Attached

Apr 13, 2008

This is my first attempt at a loop in a stored procedure, and it is not working, but the rest of the sp works fine. Could anyone please provide me with some feedback. I am not sure if I need to execute the loop first or all the sp at once ? Thanks so much.CREATE PROCEDURE Table_1TT_1T                    (@PartNo                 varchar(20),                 @Wkorder        varchar(10),                 @Setup        datetime,                 @Line        smallint,                 @TT        integer,                 @Tester        smallint,                 @LT1        integer,                 @LT2        integer,                 @LT3        integer,                 @LT4        integer,                 @LT5        integer,                 @LT6        integer,                 @LT7        integer,                 @LT8        integer,                 @LT9        integer,                 @LT10        integer,                 @LT11        integer,                 @LT12        integer,                 @LT13        integer,                 @LT14        integer,                 @LT15        integer,                 @LT16        integer,                 @LT17        integer,                 @LT18        integer,                 @LT19        integer,                 @LT20        integer,                 @LT21        integer,                 @LT22        integer,                 @LT23        integer,                 @LT24        integer,                 @LT25        integer,                 @LT26        integer,                 @LT27        integer,                 @LT28        integer,                 @LT29        integer,                 @LT30        integer,                 @LT31        integer,                 @LT32        integer,                 @LT33        integer,                 @LT34        integer,                 @LT35        integer,                 @LT36        integer,                 @UnitFound        integer        OUT,                         @parameters_LamType         varchar(50)       OUT,                 @parameters_Shunt        real               OUT,                 @parameters_ShuType     varchar(50)       OUT,                 @parameters_Stack        real              OUT,                 @parameters_Steel          varchar(50)       OUT,                 @Partno11            varchar(20)    OUT,                 @Wkorder11            varchar(10)    OUT,                 @Partno12            varchar(20)    OUT,                 @Wkorder12            varchar(10)    OUT,                 @Partno24            varchar(20)    OUT,                 @Wkorder24            varchar(10)    OUT,                 @Partno29            varchar(20)    OUT,                 @Wkorder29            varchar(10)    OUT,                 @Partno34            varchar(20)    OUT,                 @Wkorder34            varchar(10)    OUT,                 --@DL1        integer        OUT,                 --@DL2        integer        OUT,                 --@DL3        integer        OUT,                 --@DL4        integer        OUT,                 --@DL5        integer        OUT,                 --@DL6        integer        OUT,                 --@DL7        integer        OUT,                 --@DL8        integer        OUT,                 --@DL9        integer        OUT,                 --@DL10        integer        OUT,                 @DL11        integer        OUT,                 @DL12        integer        OUT,                 --@DL13        integer        OUT,                 --@DL14        integer        OUT,                 --@DL15        integer        OUT,                 --@DL16        integer        OUT,                 --@DL17        integer        OUT,                 --@DL18        integer        OUT,                 --@DL19        integer        OUT,                 --@DL20        integer        OUT,                 --@DL21        integer        OUT,                 --@DL22        integer        OUT,                 --@DL23        integer        OUT,                 @DL24        integer        OUT,                 --@DL25        integer        OUT,                 --@DL26        integer        OUT,                 --@DL27        integer        OUT,                 --@DL28        integer        OUT,                 @DL29        integer        OUT,                 --@DL30        integer        OUT,                 --@DL31        integer        OUT,                 --@DL32        integer        OUT,                 --@DL33        integer        OUT,                 @DL34        integer        OUT)                 --@DL35        integer        OUT,                 --@DL36        integer        OUT)ASSET @Tester = 1WHILE @Tester < 36      BEGIN    Set @Line = (Select Line from dbo.location where Tester = @Tester)        IF @Line = 453        BEGIN        If @Tester = 1 BEGIN SET @LT1 = 453 END        If @Tester = 2 BEGIN SET @LT2 = 453 END        If @Tester = 3 BEGIN SET @LT3 = 453 END        If @Tester = 4 BEGIN SET @LT4 = 453 END        If @Tester = 5 BEGIN SET @LT5 = 453 END        If @Tester = 6 BEGIN SET @LT6 = 453 END        If @Tester = 7 BEGIN SET @LT7 = 453 END        If @Tester = 8 BEGIN SET @LT8 = 453 END        If @Tester = 9 BEGIN SET @LT9 = 453 END        If @Tester = 10 BEGIN SET @LT10 = 453 END        If @Tester = 11 BEGIN SET @LT11 = 453 END        If @Tester = 12 BEGIN SET @LT12 = 453 END        If @Tester = 13 BEGIN SET @LT13 = 453 END        If @Tester = 14 BEGIN SET @LT14 = 453 END        If @Tester = 15 BEGIN SET @LT15 = 453 END        If @Tester = 16 BEGIN SET @LT16 = 453 END        If @Tester = 17 BEGIN SET @LT17 = 453 END        If @Tester = 18 BEGIN SET @LT18 = 453 END        If @Tester = 19 BEGIN SET @LT19 = 453 END        If @Tester = 20 BEGIN SET @LT20 = 453 END        If @Tester = 21 BEGIN SET @LT21 = 453 END        If @Tester = 22 BEGIN SET @LT22 = 453 END        If @Tester = 23 BEGIN SET @LT23 = 453 END        If @Tester = 24 BEGIN SET @LT24 = 453 END        If @Tester = 25 BEGIN SET @LT25 = 453 END        If @Tester = 26 BEGIN SET @LT26 = 453 END        If @Tester = 27 BEGIN SET @LT27 = 453 END        If @Tester = 28 BEGIN SET @LT28 = 453 END        If @Tester = 29 BEGIN SET @LT29 = 453 END        If @Tester = 30 BEGIN SET @LT30 = 453 END        If @Tester = 31 BEGIN SET @LT31 = 453 END        If @Tester = 32 BEGIN SET @LT32 = 453 END        If @Tester = 33 BEGIN SET @LT33 = 453 END        If @Tester = 34 BEGIN SET @LT34 = 453 END        If @Tester = 35 BEGIN SET @LT35 = 453 END        END        SET @Tester = @Tester + 1      ENDSELECT       @parameters_LAMTYPE = LAMTYPE,       @parameters_SHUNT = SHUNT,       @parameters_SHUTYPE = SHUTYPE,       @parameters_STACK = STACK,       @parameters_STEEL = STEEL    FROM DBO.PARAMETERS A    INNER JOIN .DBO.XREF B ON A.PARTNO = B.XREF    WHERE B.PARTNO = @PARTNO    SET @UnitFound = @@rowcountIF @UnitFound = 0    BEGIN            SELECT               @parameters_LAMTYPE = LAMTYPE,               @parameters_SHUNT = SHUNT,               @parameters_SHUTYPE = SHUTYPE,               @parameters_STACK = STACK,               @parameters_STEEL = STEEL            FROM DBO.PARAMETERS            WHERE PARTNO = @PARTNO            SET @UnitFound = @@rowcount            END        --IF @LT1 = @Line  BEGIN SET @DL1 = 1 END        --IF @LT2 = @Line  BEGIN SET @DL2 = 1 END        --IF @LT3 = @Line  BEGIN SET @DL3 = 1 END        --IF @LT4 = @Line  BEGIN SET @DL4 = 1 END        --IF @LT5 = @Line  BEGIN SET @DL5 = 1 END        --IF @LT6 = @Line  BEGIN SET @DL6 = 1 END        --IF @LT7 = @Line  BEGIN SET @DL7 = 1 END        --IF @LT8 = @Line  BEGIN SET @DL8 = 1 END        --IF @LT9 = @Line  BEGIN SET @DL9 = 1 END        --IF @LT10 = @Line  BEGIN SET @DL10 = 1 END        IF @LT11 = 453  BEGIN SET @Partno11 = @Partno SET @Wkorder11 = @Wkorder SET @DL11 = 1 END        --IF @LT11 = @Line  BEGIN SET @DL11 = 1 END        IF @LT12 = 453  BEGIN SET @Partno12 = @Partno SET @Wkorder12 = @Wkorder SET @DL12 = 1 END        --IF @LT13 = @Line  BEGIN SET @DL13 = 1 END        --IF @LT14 = @Line  BEGIN SET @DL14 = 1 END        --IF @LT15 = @Line  BEGIN SET @DL15 = 1 END        --IF @LT16 = @Line  BEGIN SET @DL16 = 1 END        --IF @LT17 = @Line  BEGIN SET @DL17 = 1 END        --IF @LT18 = @Line  BEGIN SET @DL18 = 1 END        --IF @LT19 = @Line  BEGIN SET @DL19 = 1 END        --IF @LT20 = @Line  BEGIN SET @DL20 = 1 END        --IF @LT21 = @Line  BEGIN SET @DL21 = 1 END        --IF @LT22 = @Line  BEGIN SET @DL22 = 1 END        --IF @LT23 = @Line  BEGIN SET @DL23 = 1 END        IF @LT24 = 453  BEGIN SET @Partno24 = @Partno SET @Wkorder24 = @Wkorder SET @DL24 = 1 END        --IF @LT25 = @Line  BEGIN SET @DL25 = 1 END        --IF @LT26 = @Line  BEGIN SET @DL26 = 1 END        --IF @LT27 = @Line  BEGIN SET @DL27 = 1 END        --IF @LT28 = @Line  BEGIN SET @DL28 = 1 END        IF @LT29 = 453  BEGIN SET @Partno29 = @Partno SET @Wkorder29 = @Wkorder SET @DL29 = 1 END        --IF @LT30 = @Line  BEGIN SET @DL30 = 1 END        --IF @LT31 = @Line  BEGIN SET @DL31 = 1 END        --IF @LT32 = @Line  BEGIN SET @DL32 = 1 END        --IF @LT33 = @Line  BEGIN SET @DL33 = 1 END        IF @LT34 = 453  BEGIN SET @Partno34 = @Partno SET @Wkorder34 = @Wkorder SET @DL34 = 1 END        --IF @LT35 = @Line  BEGIN SET @DL35 = 1 END        --IF @LT36 = @Line  BEGIN SET @DL36 = 1 ENDGO

View 1 Replies View Related

Infinite Recursive?

Nov 24, 1999

Hey there, Hopefully someone has a sec to help me out. I have a pretty tough query here for ya.

I want to turn this:

Parent | Child
Fred | Mark
John | Cindy
Mark | John
John | Suzy

into this:

Ancestor | Level | Descendant
Fred | 1 | Mark
Fred | 2 | John
Fred | 3 | Cindy
Fred | 3 | Suzy
Mark | 1 | John
Mark | 2 | Cindy
Mark | 2 | Suzy
John | 1 | Cindy
John | 1 | Suzy

I want to do this in a single select statement. No loops or cursors. Please tell me you are as stumped as I am so that I don't feel so dumb.

Thanks,
Jason
SQL DBA/MCSE

View 1 Replies View Related

What Exactly Is Infinite Clickthrough?

Nov 1, 2007

Another thread (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2347749&SiteID=1) got me wondering: what exactly is this "infinite clickthrough" feature that SSRS Enterprise Edition has? Searching for "infinite clickthrough" yields little but frustration - nearly all of the hits are merely pages explaining that Enterprise Edition has it and the other editions don't, without ever describing what exactly it is.

Near as I can tell, "infinite clickthrough" refers to a feature of ReportBuilder when used in conjunction with Report Models on SSRS Enterprise whereby ReportBuilder will synthesize a clickthrough report on the fly, and has nothing to do with the ability to build "clickthrough" reports using links (the "Navigation" tab on the Properties of most any reporting services object).

View 1 Replies View Related

TOP Solves Infinite Query ???

Oct 9, 2007

Hi There

I have a query that runs every night. It has been fine for months. Yesterday it started to run infinately.

When i execute it it never returns.

BUT when i add a top clause to othe query it works, now in my experience this normally happens because you are now eliminating the "bad" row with the top clause, so the join that causes it to run infinately now never occurs.

HOWEVER i run the exact query with a TOP 730 cluase and it only return 720 rows, so i am not eliminating any rows ?

Basically i have a query that runs infinately but if i add a top clause that does eliminate any rows it works ?

But if i increase the top cluase to 800 it runs inifnatley again, but it only returns 720 rows ?

I am stumped, can anyone shed some light on this please.


Thanx

View 10 Replies View Related

DB Engine :: Infinite Recursive Trigger

May 28, 2015

I have a after update trigger that calculates one of the columns based on other column values. It has to be a trigger and only fires an update statement against the table itself when the column has changed.  At a simple level it is doing something similar to the code below

IF UPDATE(Col1)
update MyTable SET Col2 = Col1 WHERE Col2 <> Col1

It works everywhere except on one site where the trigger causes itself to recurse until it reaches the 32 level error. It can be fixed by checking whether there and any records in the inserted table at the top. Like so.

IF EXISTS (SELECT * FROM inserted)
begin
IF UPDATE(Col1)
update MyTable SET Col2 = Col1 WHERE Col2 <> Col1
end

However, I would like to know whether there is some system setting other than "nested triggers" that I am missing that would cause the behaviour.

View 8 Replies View Related

SQL Server 2008 :: Difference Between FOR LOOP And FOREACH LOOP?

May 28, 2010

difference between FOR LOOP and FOREACH LOOP with example(if possible) in SSIS.

View 4 Replies View Related

Loop Though Table Using RowID, Not Cursor (was Loop)

Feb 22, 2006

I have a table with RowID(identity). I need to loop though the table using RowID(not using a cursor). Please help me.
Thanks

View 6 Replies View Related

Foreach Loop Doesn't Loop

Mar 3, 2006

I have a foreach loop that is supposed to loop through a recordset, however it doesn't loop. It just repeats the same row, row after row.

I would like to look into the recordset variable but I can't because it is a COM object and the ADODB namespace is not available in the script task.

Any solution to this? anyone experienced anything similar

View 1 Replies View Related

Fishing For A Clue. To Loop Or Not To Loop

Jul 8, 2006

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.

View 18 Replies View Related

Help With Converting Code: VB Code In SQL Server 2000-&&>Visual Studio BI 2005

Jul 27, 2006

Hi all--I'm trying to convert a function which I inherited from a SQL Server 2000 DTS package to something usable in an SSIS package in SQL Server 2005. Given the original code here:
Function Main()
on error resume next
dim cn, i, rs, sSQL
Set cn = CreateObject("ADODB.Connection")
cn.Open "Provider=sqloledb;Server=<server_name>;Database=<db_name>;User ID=<sysadmin_user>;Password=<password>"
set rs = CreateObject("ADODB.Recordset")
set rs = DTSGlobalVariables("SQLstring").value

for i = 1 to rs.RecordCount
sSQL = rs.Fields(0).value
cn.Execute sSQL, , 128 'adExecuteNoRecords option for faster execution
rs.MoveNext
Next

Main = DTSTaskExecResult_Success

End Function

This code was originally programmed in the SQL Server ActiveX Task type in a DTS package designed to take an open-ended number of SQL statements generated by another task as input, then execute each SQL statement sequentially. Upon this code's success, move on to the next step. (Of course, there was no additional documentation with this code. :-)

Based on other postings, I attempted to push this code into a Visual Studio BI 2005 Script Task with the following change:

public Sub Main()

...

Dts.TaskResult = Dts.Results.Success

End Class

I get the following error when I attempt to compile this:

Error 30209: Option Strict On requires all variable declarations to have an 'As' clause.

I am new to Visual Basic, so I'm on a learning curve here. From what I know of this script:
- The variables here violate the new Option Strict On requirement in VS 2005 to declare what type of object your variable is supposed to use.

- I need to explicitly declare each object, unless I turn off the Option Strict On (which didn't seem recommended, based on what I read).

Given this statement:

dim cn, i, rs, sSQL

I'm looking at "i" as type Integer; rs and sSQL are open-ended arrays, but can't quite figure out how to read the code here:

Set cn = CreateObject("ADODB.Connection")

cn.Open "Provider=sqloledb;Server=<server_name>;Database=<db_name>;User ID=<sysadmin_user>;Password=<password>"

set rs = CreateObject("ADODB.Recordset")

This code seems to create an instance of a COM component, then pass provider information and create the recordset being passed in by the previous task, but am not sure whether this syntax is correct for VS 2005 or what data type declaration to make here. Any ideas/help on how to rewrite this code would be greatly appreciated!

View 7 Replies View Related

How To Show Description In Report Instead Of Code (Desc For Code Is In Master Table)

Mar 28, 2007

Dear Friends,



I am having 2 Tables.

Table 1: AddressBook
Fields --> User Name, Address, CountryCode



Table 2: Country
Fields --> Country Code, Country Name


Step 1 : I have created a Cube with these two tables using SSAS.



Step 2 : I have created a report in SSRS showing Address list.

The Column in the report are User Name, Address, Country Name



But I have no idea, how to convert this Country Code to Country name.

I am generating the report using the Layout tab. ( Data | Layout | Preview ) Report1.rdl [Design]



Anyone help me to solve this issue. Because, in our project most of the transaction tables have Code and Code description in master table. I need to convert all code into corresponding description in all my reports.




Thanks in advance.





Regards
Ramakrishnan
Singapore
28 March 2007

View 4 Replies View Related

ForEach Loop Or For Loop??

Feb 23, 2006

I have source and destination table names in the database(one table) and I need to read the source and destination tables one by one...

My Lookp table is like the following...

Srn srctable desttable

1 SRC1 DEST1

2 SRC2 DEST2

3 SRC3 DEST3

Now I want one package to load from source to destination.. how do I do it.. I dont know how to use....

How do I run the pacakge for each of the rows... ..............................

View 1 Replies View Related

Many Lines Of Code In Stored Procedure && Code Behind

Feb 24, 2008

Hello,
I'm using ASP.Net to update a table which include a lot of fields may be around 30 fields, I used stored procedure to update these fields. Unfortunatily I had to use a FormView to handle some TextBoxes and RadioButtonLists which are about 30 web controls.
I 've built and tested my stored procedure, and it worked successfully thru the SQL Builder.The problem I faced that I have to define the variable in the stored procedure and define it again the code behind againALTER PROCEDURE dbo.UpdateItems
(
@eName nvarchar, @ePRN nvarchar, @cID nvarchar, @eCC nvarchar,@sDate nvarchar,@eLOC nvarchar, @eTEL nvarchar, @ePhone nvarchar,
@eMobile nvarchar, @q1 bit, @inMDDmn nvarchar, @inMDDyr nvarchar, @inMDDRetIns nvarchar,
@outMDDmn nvarchar, @outMDDyr nvarchar, @outMDDRetIns nvarchar, @insNo nvarchar,@q2 bit, @qper2 nvarchar, @qplc2 nvarchar, @q3 bit, @qper3 nvarchar, @qplc3 nvarchar,
@q4 bit, @qper4 nvarchar, @pic1 nvarchar, @pic2 nvarchar, @pic3 nvarchar, @esigdt nvarchar, @CCHName nvarchar, @CCHTitle nvarchar, @CCHsigdt nvarchar, @username nvarchar,
@levent nvarchar, @eventdate nvarchar, @eventtime nvarchar
)
AS
UPDATE iTrnsSET eName = @eName, cID = @cID, eCC = @eCC, sDate = @sDate, eLOC = @eLOC, eTel = @eTEL, ePhone = @ePhone, eMobile = @eMobile,
q1 = @q1, inMDDmn = @inMDDmn, inMDDyr = @inMDDyr, inMDDRetIns = @inMDDRetIns, outMDDmn = @outMDDmn,
outMDDyr = @outMDDyr, outMDDRetIns = @outMDDRetIns, insNo = @insNo, q2 = @q2, qper2 = @qper2, qplc2 = @qplc2, q3 = @q3, qper3 = @qper3,
qplc3 = @qplc3, q4 = @q4, qper4 = @qper4, pic1 = @pic1, pic2 = @pic2, pic3 = @pic3, esigdt = @esigdt, CCHName = @CCHName,
CCHTitle = @CCHTitle, CCHsigdt = @CCHsigdt, username = @username, levent = @levent, eventdate = @eventdate, eventtime = @eventtime
WHERE (ePRN = @ePRN)
and the code behind which i have to write will be something like thiscmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@eName", ((TextBox)FormView1.FindControl("TextBox1")).Text);cmd.Parameters.AddWithValue("@ePRN", ((TextBox)FormView1.FindControl("TextBox2")).Text);
cmd.Parameters.AddWithValue("@cID", ((TextBox)FormView1.FindControl("TextBox3")).Text);cmd.Parameters.AddWithValue("@eCC", ((TextBox)FormView1.FindControl("TextBox4")).Text);
((TextBox)FormView1.FindControl("TextBox7")).Text = ((TextBox)FormView1.FindControl("TextBox7")).Text + ((TextBox)FormView1.FindControl("TextBox6")).Text + ((TextBox)FormView1.FindControl("TextBox5")).Text;cmd.Parameters.AddWithValue("@sDate", ((TextBox)FormView1.FindControl("TextBox7")).Text);
cmd.Parameters.AddWithValue("@eLOC", ((TextBox)FormView1.FindControl("TextBox8")).Text);cmd.Parameters.AddWithValue("@eTel", ((TextBox)FormView1.FindControl("TextBox9")).Text);
cmd.Parameters.AddWithValue("@ePhone", ((TextBox)FormView1.FindControl("TextBox10")).Text);
cmd.Parameters.AddWithValue("@eMobile", ((TextBox)FormView1.FindControl("TextBox11")).Text);
So is there any way to do it better than this way ??
Thank you

View 2 Replies View Related

Custom Code (Embedded Code) Question

Oct 16, 2007



Hi all,

Could someone tell me if custom code function can capture the event caused by a user? For example, onclick event on the rendered report?

Also, can custom code function alter the parameters of the report, or refresh the report?

Thanks.

View 2 Replies View Related

Putting SqlDataSource Code In Code-behind

Jan 25, 2007

Hi,I need some help here. I have a SELECT sql statement that will query the table. How do I get the return value from the sql statement to be assigned to a label. Any article talk about this? Thanks  geniuses.  

View 2 Replies View Related

&&<Code&&>-8462&&</Code&&>

Apr 19, 2006

Hi:

My service broker is working with 2 different instances in local server.But could not able to get working on 2 different servers because of Conversation ID cannot be associated with an active conversation error which I have posted.

After I receive the message successfully...in the end I get this message sent...

<Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error">

<Code>-8462</Code>

<Description>The remote conversation endpoint is either in a state where no more messages can be exchanged, or it has been dropped.</Description>

</Error>

Why am i gettting this error after the conversation.

Thanks,

Pramod

View 7 Replies View Related

SSIS Error Code DTS_E_OLEDBERROR. An OLE DB Error Has Occurred. Error Code: 0x8000FFFF.

Jan 28, 2008

Hi All,

Recently in an SSIS package I am getting the following error for a particular Data flow task.





Error: 2008-01-25 12:01:48.58

Code: 0xC0202009

Source: Import Datasynapse Data User Events Source [3017]

Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x8000FFFF.

End Error

Error: 2008-01-25 12:01:48.73

Code: 0xC004701A

Source: Import Datasynapse Data DTS.Pipeline

Description: component "User Events Source" (3017) failed the pre-execute phase and returned error code 0xC0202009.

End Error

Our guess is when the data size of User Events table is more it throws this error. If we try to transfer small subset of data it succeeds. What could be reason for this error?

Since this is very urgent, immediate response would be very much appreciated.

Thanks & Regards,
Prakash Srinivasan

View 4 Replies View Related

How Do You LOOP In T-SQL?

Aug 20, 2007

I used to loop through recordsets with ease in old classic .asp pages.
Please Help me understand how Professionals now loop through and update tables using JUST SQL Query Analyzer using my pseudo-code provided below.
I would love to learn how to do it to better develop my skills.
 SELECT * FROM zz_2007_Booth_Visitors
WHERE COALESCE ([Product Interest - Other Actuator],
[Product Interest - Chillers],
[Product Interest - Other Chiller],
[Product Interest - Electronic Products],
[Product Interest - Other network interfaces],
[Product Interest - Fittings],
[Product Interest - High Vacuum],
[Product Interest - Other high vacuum actuators],
[Product Interest - Pick& Place and Transfer],
[Product Interest - Teflon Products],
[Product Interest - Training],
[Product Interest - Valves& Manifolds],
[Product Interest - Actuators]) Is Not Null
Order BY [Contact Name]

IF [Product Interest - Actuators] IS NOT NULL THEN
UPDATE Booth_Visitors_Data Set Act_Phuematic = 1 Where [Contact Name] = [Contact Name]

IF [Product Interest - Other Actuator] IS NOT NULL THEN
UPDATE Booth_Visitors_Data Set Act_Electric = 1 Where [Contact Name] = [Contact Name]

IF [Product Interest - Other Chillers] IS NOT NULL THEN
UPDATE Booth_Visitors_Data Set Chiller = 1 Where [Contact Name] = [Contact Name]
 
 

View 24 Replies View Related

For Loop

Jan 12, 2008

Dear All.
 Have a nice day.
I have db table, I need to update all fields of table.
Please can you write code," for loop " how can update all fields of my table by loop.
 Thanks.
Zahyea.

View 3 Replies View Related

While Loop

Mar 3, 2008

Hello everyone,I've got this While loop here which is giving me a problem:WHILE (SELECT ProductId FROM _ShoppingCart WHERE CartId = @CartId) IS NOT NULLBEGIN            DECLARE @ProdId int, @ProdSize varchar, @ProdQuan int            SELECT @ProdId = ProductId, @ProdSize = ProductSize, @ProdQuan = Quantity FROM _ShoppingCart WHERE CartId = @CartId                                   If @ProdSize = 'XL'            BEGIN                UPDATE                    _ProductBatches                SET                    XL = '0'                WHERE                    ProductId = @ProdId            END            DELETE FROM _ShoppingCart WHERE ProductId = @ProdId AND CartId = @CartIdEND The problem is that the IF statement isn't being executed. And I know for a fact that 'XL' is ProductSize in my _ShoppingCart database. Whats even stranger is that my delete statement is being executed. So @ProdId is Being set correctly, but when it gets to the IF @ProdSize = 'XL' it doesn't get executed for some reason. If @ProdId is being recognized correctly in my DELETE statement, why is my @ProdSize not being reconized correctly in my IF statement. I checked my _ShoppingCart database, and my ProductSize is definitely 'XL'. Can any one see what could be going on here.  Thank you, Alec 

View 7 Replies View Related

More Of This While Loop

Mar 4, 2008

Hello everyone...... I'm trying to do the following but am having issues:WHILE (SELECT ProductId FROM _ShoppingCart WHERE CartId = @CartId) IS NOT NULLBEGIN        execute code with item......... erase itemEND      In the while loop I want to execute code from each item in my _ShoppingCart and then erase them until there are no more items. However the above code gives me the error: "Subquery returned more than 1 value. This is not permitted........" It works fine when there is only one item. Does any one know what format to use when dealing with more that one entry?  Thank you, Alec 

View 2 Replies View Related

For Next Loop

Sep 21, 2000

hi,
I am trying to find a way of using a loop that won't be an endless loop because I have to insert parts of a string until the string reaches the end. I am unable to make the loop get to a point where the statement is false.

Is there anyway I can find out the length of the string so that I can tell the statement to loop while the statement is true?

Help!!!!!!!!!!!!1

View 1 Replies View Related

For Loop

Jan 26, 2004

HeaderLoop: for forHeader as curHeader dynamic scroll cursor for
select lngALSHeadrID from "DBA".ALSHEADR where lngFedTaxID>0 do
set AcctNum=lngALSHeadrID;
exec "DBA".sp_ALSHeadr2Policy(AcctNum);
set Cntr=Cntr+1
end for;


The above is the sybase version of a 'for loop' . The query
select lngALSHeadrID from "DBA".ALSHEADR where lngFedTaxID>0 results in 1000 results.
How do I change that in SQL?? Do we have a for loop ??
I checked in BOL but it is confusing with "browse" etc n some other options.

can I write like this?


for { Browse { declare curHeader dynamic cursor for
select lngALSHeadrID from "DBA".ALSHEADR where lngFedTaxID>0 }
set @AcctNum=lngALSHeadrID;
exec "DBA".sp_ALSHeadr2Policy(@AcctNum);
set @Cntr=@Cntr+1
}


I duno its just my guess, can any one help me out. @Cntr and @Acctnum are declared
in the beginnning.



tks

View 14 Replies View Related

Loop Help

Apr 11, 2008

I get the following results on a view.

Job | Qty | Desc
06-182 | 1 | B1011
06-324 | 2 | A1102
99-999 | 4 | AB839

What I would like is the following.

Job | Qty | Desc
06-182 | 1 | B1011
06-324 | 1 | A1102
06-324 | 1 | A1102
99-999 | 1 | AB839
99-999 | 1 | AB839
99-999 | 1 | AB839
99-999 | 1 | AB839

View 2 Replies View Related

Loop Won't End.

Apr 18, 2008

I have a loop is running with no end point. What I'm trying to do is get the Grand total of each row where BudgetNodeID = 120. Your help is much appreciated.

AV

Set NoCount on
Declare @Amt as bigint
Declare @Cont as bigint
Declare @Mark as Bigint
Declare @Total as bigint
Declare @BudgetNodeID as Bigint
Declare @GTotal as bigint
Set @BudgetNodeID ='120'
Set @Amt = 0
set @Cont = 0
set @Mark = 0
set @GTotal = 0

While exists (Select * from xBudgetNodeCosts where BudgetNodeID =@BudgetNodeID) Begin
select @Amt = IsNull(xBudgetNodeCosts.Qty,0) * IsNull(xBudgetNodeCosts.CostRate,0)
FROM xBudgetNode INNER JOIN
xBudget ON xBudgetNode.BudgetID = xBudget.BudgetID INNER JOIN
xBudgetNodeCosts ON xBudgetNode.BudgetNodeID = xBudgetNodeCosts.BudgetNodeID left JOIN
xProposalChanges pc on xbudgetnodecosts.ProposalChangeID = pc.ProposalChangeID
WHERE (xBudgetNodeCosts.BudgetNodeID = @BudgetNodeID) AND (xBudget.IsActive = '1') AND (xbudgetnodecosts.ProposalChangeID IS NULL OR
pc.Status='Approved')

select @Cont = @Amt * (xBudgetNodeCosts.Contingency/100)
FROM xBudgetNode INNER JOIN
xBudget ON xBudgetNode.BudgetID = xBudget.BudgetID INNER JOIN
xBudgetNodeCosts ON xBudgetNode.BudgetNodeID = xBudgetNodeCosts.BudgetNodeID left JOIN
xProposalChanges pc on xbudgetnodecosts.ProposalChangeID = pc.ProposalChangeID
WHERE (xBudgetNodeCosts.BudgetNodeID = @BudgetNodeID ) AND (xBudget.IsActive = '1') AND (xbudgetnodecosts.ProposalChangeID IS NULL OR
pc.Status='Approved')
select @Mark = @Cont * (xBudgetNodeCosts.Markup/100)
FROM xBudgetNode INNER JOIN
xBudget ON xBudgetNode.BudgetID = xBudget.BudgetID INNER JOIN
xBudgetNodeCosts ON xBudgetNode.BudgetNodeID = xBudgetNodeCosts.BudgetNodeID left JOIN
xProposalChanges pc on xbudgetnodecosts.ProposalChangeID = pc.ProposalChangeID
WHERE (xBudgetNodeCosts.BudgetNodeID = @BudgetNodeID) AND (xBudget.IsActive = '1') AND (xbudgetnodecosts.ProposalChangeID IS NULL OR
pc.Status='Approved')
-- compute the sell

select @Total = @Amt + @Cont + @Mark

-- add to grand total
Select @GTotal = Sum(@Total+ @GTotal)

select @GTOtal
end

View 5 Replies View Related

Loop

Jun 17, 2008

Hi

I want to loop this command with a pause. It must run every 30 min to refresh my query. Can anyone assist me.



select * from BULKPICKXLOC



Thanks

View 4 Replies View Related

For To Next Or Do While Loop In SP

Apr 11, 2006

Exist a funtion that I can use in a SP that do something like the for to next or Do while Loop do?

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved