Variable Scope Problem
Mar 1, 2007
Greetings SSIS friends!
Consider the following scenario :
Source table : Result (contains 100 rows with primary key Reuslt_ID)
Destination Table : stage_RESULT (same structure as source table)
Source table gets regular inserts with new result_ids. I want my package to pick up new result_ids only, i.e. (Result_ids > maximum(result_id) in stage_RESULT.
My package is designed to do the following :
1) Retrieve current maximum result_id into @max_result_id from RESULT_STAGE
2) Retrieve rows from Result where result_id > @max_result_id
sounds simple, BUT.. it's not working.... my source table resides in a SQL Server 6.5 database so I am having to use a datareader source adapter to pull the data.
The first time I run the package (when my stage_RESULT) is empty, the package pulls all 100 rows from the source to the destination table, but the second time I run it, it still retrieves all 100 rows again even though the value of the variable is greater than all result_ids in the source.
What am I doing wrong?
I have a variable defined at the package level. I use a SQL Query Task to assign a value to this variable (this bit works just fine). I then use this variable in my data flow task in order to retrieve data using the My expression used for my datareader source adapter is :
"select * from result where result_id > " + (dt_str, 10, 1252) @[max_result_id]
This is confusing the hell out of me.
View 3 Replies
ADVERTISEMENT
Mar 13, 2008
I have taken three dtsx files and re written them into one each in its own container. I use the XML Task task alot which the File connection is set by a variable and the variable value is evaluated by expression (the expression makes up the path/filename from other variable values). All the variables that make up the connection are at the container scope. The package will not run now because it is saying that the source (created by variables) for the file connection do not exist.
It seems the answer is that file connections exist at the package level therefore the variable has to be at the package level. This seems to be alot of variables i now have to move to package level to generate the XML source connection. Which in essence makes it confusing as to which variables operate in which container.
My question is can we easily move variable scope (Not ideal as we have alot of variables at package level) Or Can we do the same for connection managers as we do for variables and have them only used in a scope? (this will be ideal as some connections only need to be at a container scope)
View 1 Replies
View Related
Dec 4, 2007
Hi,
Is there any way to change the scope of a user defined variable?
View 3 Replies
View Related
Dec 4, 2007
Hi,
how to change the a scope of a user defined variable?
View 2 Replies
View Related
Sep 7, 2006
Hello again,
Variable scope of package variables should be in a dropdown. I want to copy (20+) variables from one sequence container to another. Do I have to retype all the names, types and initial values because I made the mistake not to place them one level higher?
Greets,
Tom
View 2 Replies
View Related
Aug 22, 2006
Hi!
I want to know the scope of a Global Variable in case of multi users.
Means i have declared a global variable in a function. And a new value is assigned to this global variable into this function, each time it is called.
So if, 3 users call this function at same time, then will the get different gloabl variables or same?
Regards,
Shabber.
View 11 Replies
View Related
Mar 19, 2008
can i create the variable with scope --Project
View 5 Replies
View Related
Jul 20, 2005
I have 24 tables named tblData1 ... tblData24 and I have a scheduledjob that runs successfully to delete all data older than 31 days.My problem is that I need to keep at least one record in each tablefor the aggregate function max() to work in one of my application'sfunctions, as if there are no records the result is null.Although I have figured out a workaround in the function using max() Iwould like to know how to change my script.Functionally I would like to get the max() value of the ID column(autoincrementing) and then add to the where "And ID <> @maxID".I have tried a few options and come unstuck with scope of variables,and tried to use a temp table to store the max values for the 24tables and got no where. Can anyone help ?Working script without the @maxID bit:-DECLARE @days VARCHAR(12)DECLARE @intData intDECLARE @SQL1 VARCHAR(2000)set @Days = 31set @intData = 1While @intData<=24BeginSET @SQL1 = 'DELETE FROM [DB1_SQL].[dbo].[tblData'+rtrim(CONVERT(char(2), @intData)) + '] Wheredatediff(Day,Datim,getdate())> '+ @daysEXEC(@SQL1)/*print @SQL1*/set @intData= @intData + 1Endgo
View 2 Replies
View Related
Mar 19, 2008
Hi All,
Could u plz tell me how to create a variable with scope project but not package(normally)
View 4 Replies
View Related
Dec 14, 2007
if a dataset doesnt use a stored proc to define a table variable, what is the scope of that table variable? Does the name need to be unique from such variables defined by other datasets?
View 4 Replies
View Related
Jun 15, 2006
Is there any way to change variable scope while using package templates?
I have created a package template that has several variables, a "typical" control flow and data flow. My goal was to try and use this as a starting point to create other packages within the same project and edit as required in the new package. I couldn't find any way (yet) to change scope of variables...these still show as belonging to the scope of package used to create the template.
Appreciate any help...thanks.
View 4 Replies
View Related
May 21, 2015
I have created a heat map and it is working pretty well. The only issue I am having is that the expression for the fill is using "DataSet1"
=Code.GetHeatmapColor(sum(Fields!AnnualPremium.Value),Min(Fields!AnnualPremium.Value,"DataSet1",Recursive),
Max(Fields!AnnualPremium.Value,"DataSet1",Recursive))
This is making the heatmap look at the whole dataset instead of just what I am grouping by. Within the Dataset there are Regions and Credit Unions. Since the Dataset is looking at an entire region, the heatmap is coloring based on all data for the region. I need to heatmap to color based on the Credit Unions in that region. The Credit Unions are a group. I need the group to be the value it is referencing in the heat map and not "DataSet1". I have been told to use scope or a variable but cannot get it to work correctly.
View 3 Replies
View Related
Jul 19, 2007
Hi friends,
I have a for each loop that populates from a set of flat files into a Sql Server table, I run the Flat file Import via a dts package embedded into Execute DTS 2000 Task. I want to pass the Sourcefile Name that is fetched by the For Each Loop to assign it Global Variable in DTS. how this can be made ?
Thanks
Subhash Subramanyam
View 4 Replies
View Related
Mar 6, 2007
I have been using tis page as a reference http://forums.asp.net/thread/1511323.aspxbut i cant seem to get this to work. The above page suggests using Dim newId As Object = e.Command.Parameters("@RETURN_VALUE").Value to get the value but when i do that i get an error that Command is not a member of system.web.ui.webcontrols.formViewInsertedEventArgs Can anyone help?ThanksMatt
View 1 Replies
View Related
Sep 27, 2007
HelloI am trying to run a program to check for transaction scopeI have written the following code.But it seems I need to add a namespace or referenceWhat namespace am I supposed to addthanksusing (TransactionScope scope = new TransactionScope(TransactionScope.Required, options)){SqlConnection MyCon = new SqlConnection("server=hemalatha\sqlexpress;integrated security=sspi;database=demo");MyCon.Open();SqlCommand Mycmd=new SqlCommand("insert into t1 values 574,'scope','10/10/2007',3,3,3");Mycmd.ExecuteNonQuery();SqlConnection MyCon1 = new SqlConnection("server=hemalatha\sqlexpress;integrated security=sspi;database=persons");MyCon1.Open();SqlCommand Mycmd1 = new SqlCommand("insert into persons values 'scope',123,123,12,'scope'");Mycmd1.ExecuteNonQuery();}
View 2 Replies
View Related
Apr 19, 2008
Hi folks
I'm using a function to create a record on a database, and then I want to return the ID of that record to passinto another function. I believe its scope identity that does this, but I'm not sure how to do it. public static void putrecordin(string record) { SqlCommand cmd = new SqlCommand("insert Table (record) values (@record; scope identity)" conn.Open(); cmd.Parameters.Add(new SqlParameter("@record", record)); cmd.ExecuteNonQuery(); conn.Close(); another(new-record-ID, anothervalue); } public static void another(string new-record-ID) {do stuff
}
so you'll see I have a function called putrecordin, and at the end of the sql statment I want to return the id of the new record and pass it into another function called another.
Anyone know how to do this?
Thanks!
View 6 Replies
View Related
Oct 7, 2002
I have created a proc that uses @@datefirst to have the weekending on Friday and starting on Saturday.
If I execute the proc from w/in a cursor, a nested cursor, will I have to constantly check and confirm the @@datefirst value?
TIA
JeffP....
View 1 Replies
View Related
Jun 6, 2007
In the following Query, is Table1.Column3 updated in the subquery for each row in Table1, or does it take the first value, or is it NULL, or is it ____?
Select Column1, Column2 from Table1 where Column1 IN
(Select Col1 from Table2 where Col2 > Table1.Column3
Group by Col1 Having count(Col1) > 1) and Table1.Column3 < Column4
View 6 Replies
View Related
Nov 26, 2007
OK, So I'm Getting some XML Like this
<PifToMepData Mode="3">
<MEP MEPName="Test Combining PIFs" MEPType="Close" PIFRecId="12" IsPrimaryPIF="1"><AssignedTo>X000525</AssignedTo></MEP>
<MEP MEPName="Test Combining PIFs" MEPType="Close" PIFRecId="13" IsPrimaryPIF="0"><AssignedTo>X000525</AssignedTo></MEP>
</PifToMepData>
I then use
INSERT INTO #myTemp99 (
Mode
, MEPName
, MEPType
, PIFRecId
, IsPrimaryPIF
, AssignedTo)
SELECT *
FROM OPENXML (@idoc, './/AssignedTo',3)
WITH(
Mode varchar(20) '../../@Mode'
,MEPName varchar(2000) '../@MEPName'
,MEPType varchar(500) '../@MEPType'
,PIFRecId int '../@PIFRecId'
,IsPrimaryPIF varchar(20) '../@IsPrimaryPIF'
,AssignedTo varchar(20) '.'
)
To Parse it out. This then has to be inserted into another table with an identity column (damn Identity column), and I need to grab the generated id for each row, because then there are other children tables that need to be populated.
Question: Is there any set way to grab multiple generated id's?
Or do I need to loop or use a cursor?
Is the 1% of the time that they are needed?
Any ideas?
Here's the temp table DDL
CREATE TABLE #myTemp99 (
Mode varchar(20)
, MEPName varchar(2000)
, MEPType varchar(500)
, PIFRecId int
, IsPrimaryPIF varchar(20)
, AssignedTo varchar(20))
And the document prep
DECALRE @idoc varchar(8000)
--Just assign the sampel data
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
EXEC sp_xml_removedocument @idoc
Brett
8-)
Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Add yourself!
http://www.frappr.com/sqlteam
View 8 Replies
View Related
Jul 23, 2005
Is an index in a database the equivalent for a <TH scope="col"> in a columnof atable in the html code?--Luigi ( un italiano che vive in Svezia)https://www.scaiecat-spa-gigi.com/s...kor-italien.php
View 3 Replies
View Related
Mar 26, 2007
I've been trying to figure out the usage of "Nothing" as the scope parameter, and the more I try, the more I can confused.
It says on the RDL spec:
"For expressions inside data regions:
.......
Specifying the keyword Nothing as the scope is equivalent to specifying the
outermost data region containing the report item in which the aggregate is used."
It seems that what's it saying is that if I have an expression in a table as
= last(somefield, "the name of the table")
is the same as
= last(somefield,Nothing)
But, apparently it;s not. (I tried last, first,sum, count,CountRows, min, max...........)
it doesn't matter where I put "=last(somefield,Nothing)" in the table (i tried table header,footer, detail, and table group header, footer), none of them worked.
and I tried it everywhere in matrix, in charts.
It's just not working.
It always complains about the invalid scope not being the containing data region, containing grouping or dataset name.
However, the only way I get the "Nothing" as the scope to work is in a RunningValue function, not in any other
aggregate funcitons.
Anyone help me with this, please... I need a complete definition on the usage of Nothing as scope.
View 11 Replies
View Related
Jan 10, 2008
I am really starting to like CTEs. My only qualm is that you can only use them within the first statement after you declare them. I wish they would remain active for the duration of the sql batch just like everything else (variables,local temp tables, ect). Is there any trick so that you can use them multiple times? Maybe define them as a string and do dynamic sql or something like that? Hopefully (i have not researched the new version of sql server) in SQL Server 2008 the scope has increase. Anyways any help would be appreciated.
thanks,
Ncage
View 19 Replies
View Related
Jan 28, 2007
Hi,
I've been trying to insert data in a Sql server (.mdf) db and use SCOPE IDENTITY to be able to insert an id in both the parent and the child tables. However, I don't know how to write one of the lines correctly:
Dim MyConn As SqlConnection = New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("myConnectionString").ConnectionString)
Dim MySQL As String = "INSERT INTO Document (fid, serie, vnr, datum, vtext, sign) " & _
"Values (@fid, @serie, @vnr, @datum, @vtext, @sign) Select @verid=SCOPE_IDENTITY()"
Dim Cmd As New SqlCommand(MySQL, MyConn)
With Cmd.Parameters
.Add(New SqlParameter("@fid", fid))
.Add(New SqlParameter("@serie", SerieDropDownList.SelectedValue))
.Add(New SqlParameter("@vnr", vnr))
.Add(New SqlParameter("@datum", datumet))
.Add(New SqlParameter("@vtext", VtextTextBox.Text))
.Add(New SqlParameter("@sign", sign))
.Add(New SqlParameter("@verid", SqlDbType.Int)) 'HERE IS THE LINE THAT SHOULD BE CHANGED (OR SO I THINK)
End With
MyConn.Open()
Cmd.ExecuteNonQuery()
Dim p As SqlParameter = Cmd.Parameters.Add("@verid", SqlDbType.Int)
p.Direction = ParameterDirection.Output
Dim verid = Cmd.Parameters("@verid").Value.ToString
For Each row As GridViewRow In StampelGridView.Rows
Dim kpnr As String = row.Cells(0).Text
Dim kst As String = row.Cells(1).Text
If kst = " " Then kst = ""
Dim projekt As String = row.Cells(2).Text
If projekt = " " Then projekt = ""
Dim debettext As String = "0.00"
Dim kredittext As String = "0.00"
If row.Cells(3).Text.Length > 3 Then debettext = Replace(row.Cells(3).Text, ",", ".")
If row.Cells(4).Text.Length > 3 Then kredittext = Replace(row.Cells(4).Text, ",", ".")
Dim ptext As String = row.Cells(5).Text
If ptext = " " Then ptext = ""
Dim MySQLpost As String = "INSERT INTO Vpost (verid, kpnr, kst, projekt, debet, kredit, ptext) " & _
"Values (@verid, @kpnr, @kst, @projekt, @debet, @kredit, @ptext)"
Dim Cmdpost As New SqlCommand(MySQLpost, MyConn)
With Cmdpost.Parameters
.Add(New SqlParameter("@verid", p)) 'THIS SHOULD CHECK OUT AS WELL...
.Add(New SqlParameter("@kpnr", kpnr))
.Add(New SqlParameter("@kst", kst))
.Add(New SqlParameter("@projekt", projekt))
.Add(New SqlParameter("@debet", debettext))
.Add(New SqlParameter("@kredit", kredittext))
.Add(New SqlParameter("@ptext", ptext))
End With
Cmdpost.ExecuteNonQuery()
Next
MyConn.Close()
In other words, the verid is the id of the new post in the first table. This id should be inserted in the new table as well.
Thank you very much in advance fro helping me out! I've worked a LONG time with this.
Pettrer
View 3 Replies
View Related
Oct 15, 2007
Is it possible to write the InsertCommand for a SqlDataSource to return the value of the AUTONumber of a field generated when a new record is added to a database table and display that value on label1.Text after the postback of ItemInsersted event? For example, <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:NearMissConnectionString >" InsertCommand="INSERT INTO [NearMiss] ([Branch], [Division] VALUES (@Branch, @Division); SELECT SCOPE_IDENTITY()" SelectCommand="SELECT [NearMissID], [Branch], [Division], FROM [NearMiss]" <InsertParameters> <asp parameter Name="Branch" Type="Int32" /> <asp parameter Name="Division" Type="String" /> </InsertParameters>
</asp:SqlDataSource>
NearMissID would be the value of the autonumber generated by the SQL Server
View 1 Replies
View Related
Oct 22, 2007
I am trying to retrieve the value of the autonumber generated when a new record is inserted into a SQL 2000 Database using a SqlDataSource attached to a FormView. Is this possible? Can some give me a short example of how to retrieve that value and display it in a textbox?
Thanks for any help with this. It seems like it would be a common issue for .net developers.
View 1 Replies
View Related
Dec 24, 2007
Hi,
I have been trying to get the scope Identity after inserting a record using an ObjectDataSource.
I can't find what event, or how to get the value that the scope identity returns.
Here is my Sproc.
ALTER PROCEDURE dbo.[YourCompany_LanCustomer_Insert]
(
@DNNUserID int,
@FirstName nvarchar(50),
@LastName nvarchar(50),
@Address nvarchar(50),
@Address2 nvarchar(50),
@City nvarchar(50),
@State nvarchar(50),
@Zip nvarchar(50),
@EmailAddress nvarchar(50),
@PhoneNumber nvarchar(50),
@CustomerID int OUTPUT
)
AS
INSERT INTO YourCompany_LanCustomer
(DNNUserID, FirstName, LastName, Address, Address2, City, State, Zip, EmailAddress, PhoneNumber, DateEntered)
VALUES (@DNNUserID, @FirstName, @LastName, @Address, @Address2, @City, @State, @Zip, @EmailAddress, @PhoneNumber, getdate())
SET @CustomerID = Scope_Identity()
RETURN
View 8 Replies
View Related
Sep 26, 2005
Which is the best approach with regards to the mirror of data from one server to another.
My first option is to use the replication service available in SQL.
what I need to know here is what are thr pro's and the con's of using this method.
My second option is to use a third party software such as Double Take. Again what would be the pro's and con's
I will not be using cluster service or any hardware high availability.
answers on a postcard.
View 8 Replies
View Related
Jun 29, 2007
This is kind of what I'm trying to do in my MS SQL 2000 query. ShouldI be able to reference s1.col1 inside the 2nd derived table?I'm getting 'Invalid column name col1' and it's coming from the 2ndderived table (I've commented out other refs to just it to check).Maybe I need to use a temp table instead.SELECT s1.col1,(SELECT * FROM(SELECT COUNT(zzz) AS SomeTotalFROM tab1WHERE s1.col1 = zzz)) AS RowCount) /* error here */FROM(SELECT col1 FROM table) AS s1Thank you!
View 1 Replies
View Related
Jul 20, 2005
We're working on a new release of a software.In the existing version, each client connects and executes a long batch ofanalysis on some tables.In the new release, we need to allow each client to execute analysis on asubset of data, according to certain login information.Our idea is:1) Connection to db2) Creation of one View according to restrictions3) Execution of analysis using just created view and some tables.The problem is that executingcreate view myvista asselect * from tb_data where testo='A1'from two different clients, creates just one view.Question: Does it exist a way of creating a VIEW with a connection scope,something like 'select into ##temptable'?We tried Create View #MyVista or ##Myvista, but this name is not accepted.Alternative way, is creating a ##TempTable, at the beginning of the batch,and using it as data source instead of full table, but, due to really hugeamount of data, this could really disturb server performances.Thanks for any helpAlberto
View 2 Replies
View Related
Nov 1, 2006
HI ...
I have a detailed report ..with summary lines and detailed lines (drill down).
I have a column with a function "RowNumber(Nothing)" which is supposed to just count the rows
when I put this in the summary row for the column I need in the Design Layout section and run the report, I get numbers on the summary lines which include the number of rows in the level below (detailed rows).
I just want to number the summary rows sequentially without taking into consideration, the number of detailed rows.
How do I modify RowNumber(Nothing) to exclude counting the detailed rows....???
Any help will be much appreciated...thanks
View 9 Replies
View Related
May 22, 2008
Hi there,
I have a problem using back_color in a time calculation member...
CREATE MEMBER CURRENTCUBE.[TIME].[TIME AGGREGATIONS].[Full Year]
AS null,
VISIBLE = 1;
Scope(
{
[Measures].[GP COMMODITY ACT],
[Measures].[GP COMMODITY PF],
[Measures].[GP COMMODITY AP],
[Measures].[GP COMMODITY IOP],
[Measures].[GP COMMODITY ACT PF],
[Measures].[GP COMMODITY ACT AP],
[Measures].[GP COMMODITY ACT IOP]
}
);
// Full Year
(
[TIME].[TIME AGGREGATIONS].[FULL YEAR],
[TIME].[TIME YEAR].[TIME YEAR].Members,
[TIME].[TIME].Members
) =
Aggregate(ANCESTOR( [TIME].[TIME].CurrentMember, [TIME].[TIME].[TIME YEAR] )
);
BACK_COLOR (this) = IIF([Measures].Currentmember.value > 0, [GREEN],[RED]);
End Scope;
I see the color just on current perio but not in time aggregation members...
Any ideas?
Thanks a lot
View 4 Replies
View Related
Mar 28, 2007
Hi ,
I have 2 packages and they get executed in SQL Job.
Each of these package will have seperate transactions with the Database and I am not sure of how to maintain the transactions between these 2 packages.
Can someone explain me the necessary setting to achieve this. If I set the TransactionOption property to Required (or) Supported in both these packages, will the Transaction Scope of the first package extend to the second one.
For me, If the second package ends up in error, the first package should also have to be rollbacked.
Any suggestions would be of great help.
Thanks
Evan
View 1 Replies
View Related
May 15, 2008
Hi,
A need a trigger to scope my data modifications but this code return EventData a NULL. Can someone help me with this?
Thanks
Code Snippet
CREATE TABLE [dbo].[_Log](
[EventID] [int] IDENTITY(1,1) NOT NULL,
[EventData] [xml] NULL,
PRIMARY KEY CLUSTERED
(
[EventID] ASC
) WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
---------
CREATE TRIGGER [dbo].[TRG_Clients]
ON [dbo].[Clients]
FOR DELETE,INSERT,UPDATE
AS
INSERT INTO [dbo].[_Log](EventData)
SELECT EVENTDATA()
View 9 Replies
View Related
