Unable To Create Variable Select Statement In For Each Loop
What I'm trying to do is this;
I have a table with Year , Account and Amount as fields. I want to
SELECT Year, Account, sum(Amount) AS Amt
FROM GLTable
WHERE Year <= varYear
varYear being a variable which is each year from a query
SELECT Distinct Year FROM GLTable
My thought was that I would need to pass a variable into a select statement which then would be used as the source in my Data Flow Task.
What I have done is to defined two variables as follows
Name: varYear (this will hold the year)
Scope: Package
Data type: String
Name:vSQL (This will hold a SQL statement using the varYear)
Scope: Package
Data type: String
Value: "SELECT Year, Account, sum(Amount) AS Amount FROM GLTable WHERE Year <=" + @[User::varYear]
I've created a SQL Task as follows
Result set: Full Result Set
Connection Type: OLE DB
SQL Statement: SELECT DISTINCT Year FROM GLTable
Result Name: 0
Variable Name: User::varYear
Next I created a For Each Loop container with the following parameters
Enumerator: Foreach ADO Enumerator
ADO Object source Variable: User::varYear
Enumeration Mode: Rows in First Table
I then created a Data Flow Task in the Foreach Loop Container and as the source used OLE DB Source as follows
Data Access Mode: SQL Command from Variable
Variable Name: User::varYear
However this returns a couple of errors "Statement(s) could not be prepared."
and "Incorrect syntax near '='.".
I'm not sure what is wrong or if this is the right way to accomplish what I am trying to do. I got this from another thread "Passing Variables" started 15 Nov 2005.
Any help would be most appreciated.
Regards,
Bill
View Complete Forum Thread with Replies
Related Forum Messages:
Loop In Select Lookup Statement
Hello All, Below is a simple Select statement performing a Lookup into a SQL database and returning the columns (associated with the Row) in to Cells on an eForm. The issue I have is there are 42 rows (which go up and down) and do not feel like writing 42 select statements. select RiskDescriptor, RiskImpactLowDescriptor, RiskImpactMediumDescriptor, RiskImpactHighDescriptor from [Risk Descriptors] where [RiskDescriptor ID] in (1) order by [RiskDescriptor ID]; <<1@Cell104>> <<2@Cell105>> <<3@Cell106>> <<4@Cell107>> I would like to add a loop, adding 1 to the RiskDescriptor ID and 4 to the Cells. So on second pass in the loop: RiskDescriptor ID = 2 <<1@Cell108>> <<2@Cell109>> <<3@Cell110>> <<4@Cell111>> Third pass in the loop: RiskDescriptor ID = 3 <<1@Cell112>> <<2@Cell113>> <<3@Cell114>> <<4@Cell115>> and so on. The Until portion of the loop can be hardcode (42 in this example) but would rather use an EOL or Query the DB for the total number of RiskDescriptor ID. This way when the DB changes (ID's go up or down) the SQL Statement does not need to be notified. It is a JDBC call from within the eForm. I would appreciate any help on how to format a loop in a SQL Statement
View Replies !
Cannot Set A Variable From A Select Statement That Contains A Variable??? Help Please
I am trying to set a vaiable from a select statement DECLARE @VALUE_KEEP NVARCHAR(120), @COLUMN_NAME NVARCHAR(120) SET @COLUMN_NAME = (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'CONTACTS' AND COLUMN_NAME = 'FIRSTNAME') SET @VALUE_KEEP = (SELECT @COLUMN_NAME FROM CONTACTS WHERE CONTACT_ID = 3) PRINT @VALUE_KEEP PRINT @COLUMN_NAME RESULTS ------------------------------------------------------------------------------------------- FirstName <-----------@VALUE_KEEP FirstName <-----------@COLUMN_NAME SELECT @COLUMN_NAME FROM CONTACTS returns: FirstName SELECT FirstName from Contacts returns: Brent How do I make this select statement work using the @COLUMN_NAME variable? Any help greatly appreciated!
View Replies !
Table Name In Variable - Create Statement
Is it possible to have part of a table name used in a CREATE statementcontained in a variable? Here's what I'd like to do, althoughobviously the syntax of this isn't quite right or I wouldn't be hereasking:DECLARE @TblPrefix char (3)SET @TblPrefix = 'tst'CREATE TABLE @TblPrefix + TestTable (col1 int)The point there is to have a table named tstTestTableThe reason I need to do this is that my ISP will only give me onedatabase to work with and I'd like to have two copies of theapplication I'm developing running at the same time. So I'd like torun the sql script that creates the tables with TblPrefix set to "dev"and then run it again with TblPrefix set to "liv"thankseric
View Replies !
Variable In A Select Statement
Is there anyway to use a variable to define a column in a select statement. I can put the variable in but I'm sure it will be read as a literal instead of the column. select @column_name from table
View Replies !
Variable Db Name In SP Select Statement
I need a general stored procedure so the database name will be an input parameter. I want to do a Select statemant such as: Select name from @DBName . . sysobjects where xtype - 'u' rather than a hard coded -- Select name from pubs..sysobjects where xtype = 'u' but I can't find the right way of using @DBName
View Replies !
Variable Db Name In SP Select Statement
I need a general stored procedure so the database name will be an input parameter, for example-- @DBName varchar(30) I want to do a Select statemant such as: Select name from @DBName . . sysobjects where xtype - 'u' rather than a hard coded -- Select name from pubs..sysobjects where xtype = 'u' but I can't find the right way of using @DBName Thanks, Judith
View Replies !
SQL Select Statement With A 'string' Variable?
I'm trying to add a 'change password' control to my site and seem to be having some issues. I have code that works if I statically define what user is displayed on the form, but I cant get it to detect the 'authenticated' user and show them the reset for for that ID.If I take the "+ myid" out of the select statement and just define the username statically the form works properly. Error:System.Data.SqlClient.SqlException: The column prefix 'System.Security.Principal' does not match with a table name or alias name used in the query. Here's a piece of the code that is supposed to detect the current logged in user. However, it gives the error. (some of the code may be redundant but its not causing issues that I can tell) public void InitPage() { IPrincipal p = HttpContext.Current.User; String myid = HttpContext.Current.User.ToString(); SqlServer sqlServer = new SqlServer(Util.SqlConnectionString()); DataTable dt; SqlConnection cnn = new SqlConnection(ConfigurationManager.ConnectionStrings["myconnection"].ConnectionString); SqlDataAdapter cmd1 = new SqlDataAdapter("select * from USER WHERE USER_NAME = "+ myid, cnn); DataTable UIDtable = new DataTable(); cmd1.Fill(UIDtable); User_Id.Value = UIDtable.Rows[0]["ID"].ToString(); dt = sqlServer.USER_SELECT(Util.SiteURL(Request.QueryString["Pg"].ToString()), User_Id.Value);
View Replies !
Using A Variable For Tablename In Select Statement?
I have a stored procedure that accepts the table name as a parameter. Is there anyway I can use this variable in my select statement after the 'from' clause. ie "select count(*) from @Table_Name"? When I try that is says "Must declare the table variable @Table_Name". Thanks!
View Replies !
Variable For The Table Name In A SELECT Statement.
Hi,I'm trying to dynamically assign the table name for a SELECT statement but can't get it to work. Given below is my code: SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE GetLastProjectNumber (@DeptCode varchar(20)) AS BEGIN TRANSACTION SET NOCOUNT ON DECLARE @ProjectNumber int SET @ProjectNumber = 'ProjectNumber' + REPLACE(CONVERT(char,@DeptCode),'.','') SELECT MAX(@ProjectNumber) FROM 'tbl_ProjectNumber' + REPLACE(CONVERT(char,@DeptCode),'.',''); END TRANSACTION Basically, I have a bunch of tables which were created dynamically using the code from this post and now I need to access the last row in a table that matches the supplied DeptCode. This is the error I get:Msg 102, Level 15, State 1, Procedure GetLastProjectNumber, Line 29Incorrect syntax near 'tbl_ProjectNumber'. Any help would be appreciated.Thanks.
View Replies !
Variable Not Holding Value For Select Statement
this querry below works perfect when i assign the us.UserID = 29 but i need to be able to use the @UsersMaxID variable..... when i debug all of my values are right where they need to be... even this on ((( @UsersMaxID ))) but for some reason it will not work with the next select statement... can someone make the pain go away and help me here..?? erik.. GOSET ANSI_NULLS ON GO ALTER PROCEDURE AA ASDECLARE @GenericColumn Varchar (200) DECLARE @GenericValue Varchar (200) SET @GenericColumn = 'FirstName'SET @GenericValue = 'Erik' DECLARE @SQL NVARCHAR(4000) DECLARE @UserID INT DECLARE @UsersMaxID INT DECLARE @MaxID INT declare @tempResult varchar (1000) -------------------------------------------Define the #Temporary Table----------------------------------------------CREATE TABLE #UsersTempTable ( ID int IDENTITY PRIMARY KEY, UserID [int], FirstName [varchar](30), LastName [varchar](30), CompanyName [varchar](200), Address1 [varchar](75), Address2 [varchar](75), City [varchar](75),ActiveInd [int], Zip [varchar](10), WkPhone [varchar](12),HmPhone [varchar](12), Fax [varchar](12), Email [varchar](200), Website [varchar](200), UserType [varchar](20),Title [varchar](100),Note [text], StateCD [char](2), CountryCD [char](2), CompanyPhoto [varchar](50), CompanyDescr [varchar](2000)) ---------------------------------------Fill the temp table with the Customers data-----------------------------------SET @SQL = 'INSERT INTO #UsersTempTable (UserID, FirstName, LastName, CompanyName, Address1, Address2, City, ActiveInd, Zip, WkPhone, HmPhone,Fax, Email, Website, UserType, Title, Note, StateCD, CountryCD, CompanyPhoto, CompanyDescr) Select Users.UserID, Users.FirstName,Users.LastName, Users.CompanyName, Users.Address1, Users.Address2, Users.City, Users.ActiveInd, Users.Zip, Users.WkPhone, Users.HmPhone,Users.Fax,Users.Email,Users.Website, Users.UserType,Users.Title, Users.Note,Users.StateCD, Users.CountryCD,Users.CompanyPhoto,Users.CompanyDescr FROM USERS WHERE ' + @GenericColumn +' = ''' + @GenericValue + '''' EXEC sp_executesql @SQL SET @MaxID = (SELECT MAX(ID) FROM #UsersTempTable)SET @UsersMaxID = (SELECT UserID From #UsersTempTable WHERE ID = @MaxID) SELECT SpecialtyName FROM Specialty s INNER JOIN UserSpecialty us ON s.SpecialtyCD = us.SpecialtyCD WHERE us.UserID = 29 SELECT * FROM #UsersTempTable ==========================================================================================SET @UsersMaxID = (SELECT UserID From #UsersTempTable WHERE ID = @MaxID) SELECT SpecialtyName FROM Specialty s INNER JOIN UserSpecialty us ON s.SpecialtyCD = us.SpecialtyCD WHERE us.UserID = 29 <<<<<<<<<<<<<<<<< i need @UserMaxID ........RIGHT HERE
View Replies !
Use A Variable Along With The FROM Clause In SELECT Statement
I have a table 'table_list' which contains two columns, table_name and a record_count. This table stores a list of tables and their corresponding record counts. What I am trying to do is, to be able to write a select statement, that can read each table name in the 'table_name' column, execute a select count(*) for the same, and update its record_count with the result of select count(*). This is the code in my procedure.. DECLARE @tab_list CURSOR set @tab_list = CURSOR FOR select * from table_list OPEN @tab_list DECLARE @tab_name varchar(256) DECLARE @rec_cnt int FETCH NEXT FROM @tab_list INTO @tab_name, @rec_cnt select count(*) from @tab_name This select is looping around along with FETCH till all the table names are exhausted and their counts are updated from the cursor back into the table. Problem is that, I am not able to use select count(*) from @tab_name, and its not accepting a variable there. Please help me to construct the select statement that is similiar to x=<table name> select * from x where x is a variable and the table name gets substituted. what is the syntax for it ?
View Replies !
Put Select Statement In SSIS Variable
Is it possible to add a variable in SSIS like name of variable: myVar Scope: Data Flow Task Data Type: String Value:SELECT hello FROM blah WHERE (azerty = @[User::pda]) AND (qwerty = @[User::phone]) @[User::pda] and @[User::phone] are also variables in SSIS just like the myVar I made I know I'm doing something wrong with the data type because it's stores the whole select statement as a string Help Worf
View Replies !
Return Variable Name As Part Of Select Statement.
hey all, I have the following query: ALTER PROCEDURE [dbo].[sp_SelectMostRecentArticle] AS BEGIN DECLARE @article_id INT SELECT @article_id = ( SELECT TOP 1 article_id FROM article ORDER BY article_id DESC ) DECLARE @comment_count INT SELECT @comment_count = ( SELECT COUNT(comment_id) FROM comment JOIN article ON article_id = comment_article_id GROUP BY article_id HAVING article_id = @article_id ) SELECT TOP 1 article_id, article_author_id, article_title, article_body, article_post_date, article_edit_date, article_status, article_author_id article_author_ip, author_display_name, category_id, category_name--, comment_count AS @comment_count FROM article JOIN author ON author_id = article_author_id JOIN category ON category_id = article_category_id GROUP BY article_id, article_title, article_body, article_post_date, article_edit_date, article_status, article_author_ip,article_author_id, author_display_name, category_id, category_name HAVING article_id = @article_id END GO as you can see, im trying to return a comment_count value, but the only way I can do this is by defining the variable. I have had to do it this way, because I cannot say COUNT(comment.comment_id) AS comment_count or it returns an error that it cant reference the comment.comment_id. But when change it to FROM article, comment; I get errors about the article_author_id and article_comment_id. And i cant add a join, because it would return the amount of rows of the comment... unless someone could help with what i Just decribed (as i would prefer to do it this way), how would i return the variable value as part of the select statement? Cheers
View Replies !
Storing Results Of Select Statement In @variable
I'm new to sql stored procedures but I would like to store the results of an sql statement in a variable such as: SET @value = select max(price) from product but this does not work, can someone tell me how I would go about in storing the results in a variable. @value is declared as int Thanks in advance, Sharp_At_C
View Replies !
How To Assign The SELECT Statement Output To A Local Variable?
In my program i have function that will get one value from Database. Here i want to assign the output of the sql query to a local variable. Its like select emp_id into Num from emp where emp_roll=222; here NUM is local variable which was declared in my program. Is it correct.? can anyone please guide me..?
View Replies !
Combing In A Cursor, A Select Statement With The WHERE Clause Stored In A Variable
Hi I am ramesh here from go-events.com I am using sql mail to send out emails to my mailing list I have difficulty combining a select statement with a where clause stored in a variable inside a cursor The users select the mail content and frequency of delivery and i deliver the mail I use lots of queries and a stored procedure to retrieve thier preferences. In the end i use a cursor to send out mails to each of them. Because my query is dynamic, the where clause of my select statement is stored in a variable. I have the following code that does not work For example DECLARE overdue3 CURSOR LOCAL FORWARD_ONLY FOR SELECT DISTINCT Events.E_Name, Events.E_SDate, Events.E_City, Events.E_ID FROM Events, IndustryEvents + @sqlquery2 OPEN overdue3 I get an error message at the '+' sign which says, cannot use empty object or column names, use a single space if necessary How do I combine the select statement with the where clause? Help me...I need help urgently
View Replies !
Is There A Way To Create Columns From A Row Value In A Select Statement?
Hi! I have a select question that look like this: DECLARE @ID uniqueidentifier; SET @ID = '40bd3052-60f4-414a-99df-ca882c128493'; SELECT rp.ID AS ReportPackId ,rp.SupplierPartyIdentifier AS SupplierPartyIdentifier ,rpap.AdditionalPartyIdentifier AS AdditionalPartyIdentifier FROM ReportPack rp INNER JOIN ReportPackAdditionalParty rpap ON rpap.fk_ReportPack = rp.ID WHERE rp.ID = @ID The result a get when running the select question is: ReportPackId SupplierPartyIdentifier AdditionalPartyIdentifier 40BD3052-60F4-414A-99DF-CA882C128493 String addPartyId1 40BD3052-60F4-414A-99DF-CA882C128493 String addPartyId2 My problem is that the result I want is the following: ReportPackId SupplierPartyIdentifier AdditionalPartyIdentifier AdditionalPartyIdentifier 40BD3052-60F4-414A-99DF-CA882C128493 String addPartyId1 addPartyId2 I always know that the ReportPackId and SupplierPartyIdentifier will be identical for all rows because of the Where condition, and therefore I want all AdditionalPartyIdentifiers to be in columns instead of a new row. Is this possible?
View Replies !
Random Selection From Table Variable In Subquery As A Column In Select Statement
Consider the below code: I am trying to find a way so that my select statement (which will actually be used to insert records) can randomly place values in the Source and Type columns that it selects from a list which in this case is records in a table variable. I dont really want to perform the insert inside a loop since the production version will work with millions of records. Anyone have any suggestions of how to change the subqueries that constitute these columns so that they are randomized? SET NOCOUNT ON Declare @RandomRecordCount as int, @Counter as int Select @RandomRecordCount = 1000 Declare @Type table (Name nvarchar(200) NOT NULL) Declare @Source table (Name nvarchar(200) NOT NULL) Declare @Users table (Name nvarchar(200) NOT NULL) Declare @NumericBase table (Number int not null) Set @Counter = 0 while @Counter < @RandomRecordCount begin Insert into @NumericBase(Number)Values(@Counter) set @Counter = @Counter + 1 end Insert into @Type(Name) Select 'Type: Buick' UNION ALL Select 'Type: Cadillac' UNION ALL Select 'Type: Chevrolet' UNION ALL Select 'Type: GMC' Insert into @Source(Name) Select 'Source: Japan' UNION ALL Select 'Source: China' UNION ALL Select 'Source: Spain' UNION ALL Select 'Source: India' UNION ALL Select 'Source: USA' Insert into @Users(Name) Select 'keith' UNION ALL Select 'kevin' UNION ALL Select 'chris' UNION ALL Select 'chad' UNION ALL Select 'brian' select 1 ProviderId, -- static value '' Identifier, '' ClassificationCode, (select TOP 1 Name from @Source order by newid()) Source, (select TOP 1 Name from @Type order by newid()) Type from @NumericBase SET NOCOUNT OFF
View Replies !
Create Temporary Table Through Select Statement
Hi,I want to create a temporary table and store the logdetails froma.logdetail column.select a.logdetail , b.shmacnocase when b.shmacno is null thenselectcast(substring(a.logdetail,1,charindex('·',a.logde tail)-1) aschar(2)) as ShmCoy,cast(substring(a.logdetail,charindex('·',a.logdeta il)+1,charindex('·',a.logdetail,charindex('·',a.lo gdetail)+1)-(charindex('·',a.logdetail)+1))as char(10)) as ShmAcnointo ##tblabcendfrom shractivitylog aleft outer joinshrsharemaster bon a.logkey = b.shmrecidThis statement giving me syntax error. Please help me..Server: Msg 156, Level 15, State 1, Line 2Incorrect syntax near the keyword 'case'.Server: Msg 156, Level 15, State 1, Line 7Incorrect syntax near the keyword 'end'.sample data in a.logdetailBR··Light Blue Duck··Toon Town Central·Silly Street···02 Sep2003·1·SGL·SGL··01 Jan 1900·0·0·0·0·0.00······0234578······· ····· ··········UB··Aqua Duck··Toon Town Central·Punchline Place···02 Sep2003·1·SGL·SGL··01 Jan 1900·0·0·0·0·0.00·····Regards.
View Replies !
Dynamic Create Table Statement Or SELECT INTO
In SQL Server you can do a SELECT INTO to create a new table, much like CREAT TABLE AS in Oracle. I'm putting together a dynamic script that will create a table with the number of columns being the dynamic part of my script. Got any suggestions that come to mind? Example: I need to count the number of weeks between two dates, my columns in the table need to be at least one for every week returned in my query. I'm thinking of getting a count of the number of weeks then building my column string comma separated then do my CREATE TABLE statement rather then the SELECT INTO... But I'm not sure I'll be able to do that using a variable that holds the string of column names. I'm guess the only way I can do this is via either VBScript or VB rather then from within the database. BTW - this would be a stored procedure... Any suggestions would be greatly appreciated.
View Replies !
Dynamic CREATE TABLE Or SELECT INTO Statement
In SQL Server you can do a SELECT INTO to create a new table, much like CREAT TABLE AS in Oracle. I'm putting together a dynamic script that will create a table with the number of columns being the dynamic part of my script. Got any suggestions that come to mind? Example: I need to count the number of weeks between two dates, my columns in the table need to be at least one for every week returned in my query. I'm thinking of getting a count of the number of weeks then building my column string comma separated then do my CREATE TABLE statement rather then the SELECT INTO... But I'm not sure I'll be able to do that using a variable that holds the string of column names. I'm guess the only way I can do this is via either VBScript or VB rather then from within the database. BTW - this would be a stored procedure... Any suggestions would be greatly appreciated.
View Replies !
Select Statement Within Select Statement Makes My Query Slow....
Hello... im having a problem with my query optimization.... I have a query that looks like this: SELECT * FROM table1 WHERE location_id IN (SELECT location_id from location_table WHERE account_id = 998) it produces my desired data but it takes 3 minutes to run the query... is there any way to make this faster?... thank you so much...
View Replies !
DTS - Capture Variable To Use In Loop
I need to perform a chunk of code for each occurrence of a particular variable. I have plenty of sample code to set up a source, destination, and data pump. What I need to know is how to capture a value returned from a sql statement into a variable within VBScript.
View Replies !
Setting A Variable In A For Loop
The For Loop will execute a stored procedure that passes a variable each time it loops. I need to set this variable, @FiscalWeek, equal to the variable @Counter. So the first time it loops, the counter will be 1, and the Fiscal Year would be set to 1. The next time it would be two, and so forth. Can I do this in the Expressions section of the For Loop? If so, what would the property be? InitExpression @Counter =0 EvalExpression @Counter == @CurFiscalWeek AssignExpression @Counter = @Counter + 1
View Replies !
Variable Inside A Nested Loop
I am trying to write a utility/query to get a report from a table. Belowis the some values in the table:table name: dba_daily_resource_usage_v1conn|loginame|dbname|cum_cpu|cum_io|cum_mem|last_b atch------------------------------------------------------------80 |farmds_w|Farm_R|4311 |88 |5305 |11/15/2004 11:3080 |abcdes_w|efgh_R|5000 |88 |4000 |11/15/2004 12:3045 |dcp_webu|DCP |5967 |75 |669 |11/16/2004 11:3095 |dcp_webu|XYZ |5967 |75 |669 |11/17/2004 11:30I need to write a query which for a given date (say 11/15/2004),generate a resource usage report for a given duration (say 3 days).Here is my query:************************************set quoted_identifier offdeclare @var1 intset @var1=0--BEGIN OUTER LOOPwhile @var1<=3 --INPUT runs the report for 3 daysbegindeclare @vstartdate char (10) --INPUT starting dateset @vstartdate='11/15/2004'--builds a range of datedeclare @var2 datetimeset @var2=(select distinct (dateadd(day,@var1,convert(varchar(10),last_batch,101)))--set @var2=(select distinct (dateadd(day,@var1,last_batch))from dba_daily_resource_usage_v1where convert(varchar (10),last_batch,101)=@vstartdate)set @var1=@var1+1 --increments a daydeclare @var5 varchar (12)--set dateformat mdy--converts the date into 11/15/2004 format from @var2set @var5="'"+(convert(varchar(10),@var2,101))+"'"--print @var5 produces '11/15/2004' as resultdeclare @vloginame varchar (50)declare @vdbname varchar (50)--BEGIN INNER LOOPdeclare cur1 cursor read_only forselect distinct loginame,dbname fromdba_daily_resource_usage_v1where convert(varchar (10),last_batch,101)=@var5--??????PROBLEM AREA ABOVE STATEMENT??????--print @var5 produces '11/15/2004' as result--however cursor is not being built and hence it exits the--inner loop (cursor)open cur1fetch next from cur1 into @vloginame, @vdbnamewhile @@fetch_status=0begin--print @var5 produces '11/15/2004' as resultdeclare @vl varchar (50)set @vl="'"+rtrim(@vloginame)+"'"declare @vd varchar (50)set @vd="'"+@vdbname+"'"--processes the cursorsdeclare @scr varchar (200)set @scr=("select max(cum_cpu) from dba_daily_resource_usage_v1 whereloginame="+@vl+" and dbname="+@vd+" and "+"convert(varchar(10),last_batch,101)="+@var5)--set @var3 =(select max(cum_cpu) from dba_daily_resource_usage_v1where--loginame=@vloginame and dbname=@vdbname--and convert(varchar (10),last_batch,101)=@var5)print @scr--exec @scrfetch next from cur1 into @vloginame, @vdbnameend--END INNER LOOPselect @var2 as "For date"deallocate cur1end--END OUTER LOOP************************************PROBLEM:Even though variable @var5 is being passed as '11/15/2004' inside thecursor fetch (see print @var5 inside the fetch), the value is not beingused to build the cursor. Hence, the cursor has no row set.Basically, the variable @var5 is not being processed/passed correctlyfrom outside the cursor to inside the cursor.Any help please.Thanks*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View Replies !
Collecting Values Into A Variable In A Loop
Dear All, I’m trying to collect values from a query into a single variable within a loop, like so: WHILE condition is true BEGIN SET @intLoop = @intLoop + 1 @myString = @myString + ‘, ‘ + (SELECT companyName FROM @tblTheseComp WHERE id = @intLoop END For some reason though the @myString does not collect up the values, but will equal NULL at the end of the loop. If however I simple do WHILE condition is true BEGIN SET @intLoop = @intLoop + 1 @myString = (SELECT companyName FROM @tblTheseComp WHERE id = @intLoop END Then I get the last value from the query as expected. Can anyone explain why this might be? Thanks in advance!
View Replies !
MERGING Variable In FOR LOOP COntainer
Hi All, Seems like a simple task, but been a struggle. Simply trying to move a group of files from one folder to another folder and renaming the files with the monthyear in the middle of the filename.. I'm using a FOR Loop container and works find. The added complexity is I'm trying to rename the files the same tine and putting the Month Year into the file name. I guess the struggle is how to get the file name out so I can manipulate it. I tried creating variable(V_SOURCE) which stores the file path and create another variable(V_FILENAME) to hold the filename. I believe on the expression page of the FOR LOOP editor if I select the the file name and extension radio button, it should put the file name in the V_FILENAME variable. In my file task trying to join the two variables together in another variable, keep saying my path is wrong with the file task kicks off. here's the syntaxt @[User::V_SourcePath] + @[User::V_FILE_NAME]
View Replies !
Loop By Passing Variable Into Query
Hi all, I'm have created a data flow that uses an OLEDB source with a SQL Query. In the WHERE statement of this query is a condition for the storecode. I want to figure out how to create a loop that will cycle through a list of storecodes using a variable which is passed to the dataflow in turn to the OLEDB source's query and runs through the process for each store. The reason i'm using a loop is because there are about 15 million records that are merge joined with 15 million others which is causing a huge performance problem. I'm hoping that by looping the process with one store at a time it should be faster. Any ideas would be greatly appreciated.
View Replies !
Problem With Setting Variable Values In A Loop
In a stored procedure that I'm fixing, there is a problem with assigning variable values inside a loop. The proc is using dynamic SQL and if statements to build all these statements, but I'm having to add a new variable value to it that is throwing it out of whack. This is the current structure: SET @MktNbr = 10 WHILE @MktNbr < 90 BEGIN DECLARE@sqlstmt varchar(1000) SET @Market = '0' + CONVERT(char(2),@MktNbr) SET @sqlstmt = 'SELECT (columns) INTO dbo.table' + @Market + ' FROM #table WHERE marketcode = ''' + @Market + ''' IF @MktNbr = 50 BEGIN SET @MktNbr = 51 END ELSE IF @MktNbr = 51 BEGIN SET @MktNbr = 52 END ELSE IF @MktNbr = 52 BEGIN SET @MktNbr = 55 END ELSE IF @MktNbr = 55 BEGIN SET @MktNbr = 60 END ELSE BEGIN SET @MktNbr = @MktNbr + 10 END EXEC (@sqlstmt) END I'm probably having a blonde moment, but I'm trying to replace the if statements with this: SET @MktNbr = CASE WHEN @MktNbr = 10 THEN 20 WHEN @MktNbr = 20 THEN 30 WHEN @MktNbr = 30 THEN 40 WHEN @MktNbr = 40 THEN 50 WHEN @MktNbr = 50 THEN 51 WHEN @MktNbr = 51 THEN 52 WHEN @MktNbr = 52 THEN 55 WHEN @MktNbr = 55 THEN 60 WHEN @MktNbr = 60 THEN 70 WHEN @MktNbr = 70 THEN 80 WHEN @MktNbr = 80 THEN 81 ELSE @MktNbr END Clearly it's wrong because the proc bombs every time with a duplicate table error. It has been suggested to me that I should hold these market values in an external table. This sounds reasonable but I'm ashamed to admit that I don't know how I'd implement that. Can someone maybe give me a nudge in the right direction?
View Replies !
Using Array Variable In ForEach Loop Container
Dear all, I have a problem using SSIS since I was a beginner, the problem is I have to do some data transform from flat files into database. These flat files come from many branches of my office with the file structure like this D:SSISranch_nmfile_nm.txt, in folder SSIS there are many branch_nm folder and each branch_nm folder contains many flat files. My idea was grab the branch_nm folder into array variable using Script Task and then loop this array variable using ForEach Loop Container to get the file and using it for Flat File connection, but I don't know the way to do it. May this idea work out for sure ?? How to use array variable, that we previously defined inside Script Task, in ForEach Loop Container ? Thanks in advance
View Replies !
How To Use A Variable From The For Loop Container As A Input Parameter To A SP
Hi Everyone: I have a quick but imp SSIS question. I have a For Each Loop Container, and inside that I wish to add a Execute SQL Task item, so I can call a sp to do some inserts/updates. The ForEachLoop container is looping thru a ADO Object source variable(which is the user variable defined by me, as a FullResultSet). In my Execute SQL Task, I would like to utilize one of the columns from the result set as an input parameter to my Stored procedure. Can someone please advise on how to do this? Please let me know if you have any more questions. I am waiting for a response... Thanks in advance. MA
View Replies !
Using Variable Of Foreach Loop - File Enumerator
I have a Foreeach loop container that is looking for all the files in a specified directory. For each file in the directory I need to open the file, extract the contents, and pass the contents as a variable for a stored procedure call. I was able to loop thru the files in the directory and see each file and store the name to a variable. I was able to use an XML task to open one file via a file connection that connects to a specified file and store the contents in a variable and then execute the SQL task using the variable. What I am unable to figure out is how to put the two processes together and get the XML task to open the file by using the variable that contains the filename. If anyone could help I would really appreciate it. Thanks! GN
View Replies !
Assigning Variable Values Via Loop Using Different Datatypes
Hi all, I need some help regarding a conversion in a Script Task. I am reading my variable values from a database with a sql task, the table has two columns, variable and variableValue. Looping through the recordset and setting the different variables works well, with two links: http://blogs.conchango.com/jamiethomson/archive/2005/02/09/SSIS_3A00_-Writing-to-a-variable-from-a-script-task.aspx http://sqlblog.com/blogs/andy_leonard/archive/2007/10/14/ssis-design-pattern-read-a-dataset-from-variable-in-a-script-task.aspx setting the variable value only works well if the package variable is defined as string, because the db field is a varchar, trying to assign an integer for example brings up an error. Therefor I tried something like CType: Dts.Variables("MyVar").Value = CType(MyRecordsetField,String), where the target datatype should be depending on the variable datatype instead of being assigned as a constant. Could someone give me a hint to handle this? Thanks in advice! Cheers Markus
View Replies !
Unable To Retrieve Column Information In Flatfile Connection Within Foreach Loop Container
Hello, I am getting very frustrated! I have got a Foreach loop container which I am processing files within a folder. I have a flatfile connection manager which I have set up using a test file and have updated the expressions attribute to be the package variable I set up in the collection for the loop container however everytime I run it I get the error: 0xC0202094 cannot retrieve the column information from the flatfile connection manager. I can only guess that it is either the variable being passed to the connection manager or the way I set up the connection manager. When I msgbox the variable in a script component before the dataflow step, the variable for the file seems fine. Any suggestions are REALLY appreciated. Yours Jayne
View Replies !
Stored Procedure - Catch Variable Data In A While Loop..
I'm not quite sure how to make this work. The SQL syntax worked fine if I comment out this line of code but it wouldn't work if I un-commented it out. Take a look at the "@TblPurchaseRaw_RawID" variable... Code: DECLARE @TblPurchaseRaw_RawID INT DECLARE @TblPurchaseRaw_AccountID INT DECLARE @TblPurchaseRaw_AcceptedID INT SELECT RawID INTO #tmpTblPurchaseRaw FROM tblPurchaseRaw WHERE AcceptedID = 0 WHILE EXISTS (SELECT TOP 1 RawID FROM #tmpTblPurchaseRaw) BEGIN --SET @TblPurchaseRaw_RawID = RawID SELECT TOP 1 @TblPurchaseRaw_AccountID = AccountID, @TblPurchaseRaw_AcceptedID = AcceptedID FROM tblPurchaseRaw WHERE RawID = @TblPurchaseRaw_RawID DELETE FROM #tmpTblPurchaseRaw WHERE RawID = @TblPurchaseRaw_RawID END Thanks... Edited: A while later --> Oops, my bad!! I understood the problem a little better now. The sql query inside the () bracket of the while loop had it's own scope so I'm not gonna get the value outside of that () bracket scope. It only work if I do another select query. Here is what works now... Code: DECLARE @TblPurchaseRaw_RawID INT DECLARE @TblPurchaseRaw_AccountID INT DECLARE @TblPurchaseRaw_AcceptedID INT SELECT RawID AS tmpRawID INTO #tmpTblPurchaseRaw FROM tblPurchaseRaw WHERE AcceptedID = 0 WHILE EXISTS (SELECT TOP 1 * FROM #tmpTblPurchaseRaw) BEGIN SELECT TOP 1 @TblPurchaseRaw_RawID = tmpRawID FROM #tmpTblPurchaseRaw SELECT TOP 1 @TblPurchaseRaw_AccountID = AccountID, @TblPurchaseRaw_AcceptedID = AcceptedID FROM tblPurchaseRaw WHERE RawID = @TblPurchaseRaw_RawID DELETE FROM #tmpTblPurchaseRaw WHERE tmpRawID = @TblPurchaseRaw_RawID END
View Replies !
Variable Mapping And Index For The Foreach Loop Container.
Hi: I am trying to loop through 4 files in a folder and read the names of those files through the For each loop container task. I have 4 readme files (readme1.txt thru readme4.txt) in a folder called C:SSIS. I have added a for each Loop container and a script task to my package. In the Variable Mapping page I have named the Variable and configured the index to be 0. The problem is when I execute the package the file name that is read is always readme1.txt. I want all the file names to be read under the folder ( in other words configure the index to be 0 thru 4). How do I configure the index so that it can read all the files?. The following is the code I have in my script task: Public Sub Main() Dim variables As Variables If Dts.Variables.Contains("FileName") = True Then Dts.VariableDispenser.LockOneforRead("FileName", variables) End if Msgbox ("Retrieved the File " & CStr(variables("FileName").Value)) Dts.TaskResult=Dts.Results.Success End Sub When I execute the package with the above code, I always see a message box popping up and saying Retrived File Readme1.txt. I want the msgbox to pop up and say Retrieved Readme1.txt Retrieved Readme2.txt Retrieved Readme3.txt and so forth... Any help on achieving this is appreciated. Thanks AK
View Replies !
How To Use Loop Statement
I have a table which contains 170K of customer_numbers. How can I write a query (loop) to create 340 tables or excel files which contains 500 customer numbers each. Thanks for your assistance in advance.
View Replies !
Loop's To Create A Page!
Hi,Right, I have this problem, and it;s more through lack of understanding vb.net that well more then an actual problem I will out line what I want to do,basically it all revolves around a page that needs to be built when navigated to so it can be easily updated without anyone having to edit the code.Get all the table names from a databaseLoop through each of the results to build a statementNest a 2nd loop to split the returned data from the correct tableBuild a listbox for each table returnedThis is what I have currently, this works but the problem is, if another course is added, someone will need to manually edit the code on the page to add a new code to get the new course hence why I want to create a loop that gets all the data so all someone needs to do is put in the all table the new course name. Please noteI cut this down to just show 2 result but there is about 30 odd. 1 DBConn = New SqlConnection("Server=SD02;Initial Catalog=WhoGetsWhat;Persist Security Info=True;UID=x;Password=xx") 2 'Dim DBDataAdapter As SqlDataAdapter 3 DBDataAdapter = New SqlDataAdapter("Select AOE,ADC, FROM TBL_Role WHERE Title = @ddlTitle", DBConn) 4 DBDataAdapter.SelectCommand.Parameters.Add("@ddlTitle", SqlDbType.NVarChar) 5 DBDataAdapter.SelectCommand.Parameters("@ddlTitle").Value = TitleDropDown.SelectedValue 6 DBDataAdapter.Fill(DSPageData, "Courses") 7 8 'Loop through each record, split at + and place in ListBoxs 9 10 VarDS = DSPageData.Tables("Courses").Rows(0).Item("AOE") 11 Dim VarArray As String() = VarDS.Split("+") 12 Dim i As Integer 13 For i = 0 To VarArray.Length - 1 14 15 Dim li As New ListItem() 16 li.Text = VarArray(i).ToString() 17 li.Value = VarArray(i).ToString() 18 Me.txtAOE.Items.Add(li) 19 Next i 20 21 VarDS = DSPageData.Tables("Courses").Rows(0).Item("ADC") 22 VarArray = Nothing 23 VarArray = VarDS.Split("+") 24 i = Nothing 25 For i = 0 To VarArray.Length - 1 26 27 Dim li As New ListItem() 28 li.Text = VarArray(i).ToString() 29 li.Value = VarArray(i).ToString() 30 Me.txtADC.Items.Add(li) 31 Next i Now here is my pseudo code to what I roughly want to do, hope it makes sense to someone and someone can point me in the correct direction. Please note,I know the split bit works, so at the minute I am just trying to get the loop to get all my courses 1 DBConn = New SqlConnection("Server=SD02;Initial Catalog=WhoGetsWhat;Persist Security Info=True;UID=wgw;PWD=wgwsql;") 2 DBSelect.Connection = DBConn 3 DBSelect.Connection.Open() 4 'Get the row number in the database 5 DBSelect.CommandText = "SELECT COUNT(*) FROM TBL_All" 6 DBResult = DBSelect.ExecuteScalar() 7 DBSelect.Connection.Close() 8 Dim Count = DBResult 9 'Get all the Tables and Keys in the Database's 10 DBDataAdapter = New SqlDataAdapter("SELECT * FROM TBL_All", DBConn) 11 DBDataAdapter.Fill(DSPageData, "All") 12 'declare all loop vars 13 Dim X As Integer 14 Dim Y As Integer 15 Dim i As Integer 16 'Loops through all the tables 17 Dim DSArray As String() = DSPageData.Tables("All").Items() 18 For Y = 0 To Count 19 Dim VarDS As String() = DSPageData.Tables("All").Rows(0).Item(DSArray(Y)) 20 Dim SplitArray As String() = VarDS.Split("+") 21 22 23 For i = 0 To SplitArray.Length - 1 24 Dim Li As New ListItem() 25 Li.Text = SplitArray(i).ToString() 26 Li.Value = SplitArray(i).ToString() 27 Me.txt & DSArray(Y) &.Items.Add(Li) 28 Next i 29 30 Next Y
View Replies !
Not Being Able To Create A View That Requires A Loop
Hey i have a table A that contains 3 columns : id, entry ,sessionid i want to create a view on this table that will contain - for each sessionid s in A --> select top 5 rows having s as sessionid and ordered by id desc (s can have 1 or 2 or 5 or 300 entries i want to get only the latest 5 rows that correspond to this session) I tried many queries and different combinations i could find one yet to do the following. Can anyone help me plz? Can we have a loop in a view?is it possible?
View Replies !
Create A Sql Loop Using Alphabet Characters
I have a basic while loop but I need to be able to increment the counter from one alpha character to the next: declare @counter nvarchar(10) set @counter = 'A' while @counter < 'Z' beginprint 'the counter is ' + @counter set @counter = @counter + @counter end In this example I would need to see the following output: the counter is Athe counter is Bthe counter is Cthe counter is D..... Is this possible in SQL?
View Replies !
Loop To Run 'Create Trigger' Script?
I need to run a script to create a trigger on 18 tables on 3 databases. The code is identical with the exception of the table and trigger names at the beginning. Does anyone know of a way to create them all with a loop instead of manually replacing the table and trigger names and executing over and over? I tried variables but get an 'Incorrect syntax near '@TriggerName' error. if exists (select * from sysobjects where id = object_id (N'dbo.tgUsersAudit') and objectproperty (id, N'IsTrigger') = 1) drop trigger dbo.tgUsersAudit go CREATE TRIGGER tgUsersAudit on tblUsers FOR insert, update, delete AS DECLARE @TableName varchar(128) SET @TableName = tblUsers ..................from here the code is the same for all
View Replies !
Using A Loop To Create A List Of Emails
I have a stored proc I am running, and I would like to create a list of email addresses from a table and put that list into a variable. I did a basic while loop to work on syntax, but now I don't know how to actually get each address added on. Here's how I started it declare @start int, @testEmail nvarchar(2000) set @start = 1 set @testEmail = NULL while @start <= (Select count(PADM_Email) from PADM_Emails) Begin --Print @start set @testEmail = @testEmail + (Select distinct PADM_Email from PADM_Emails) + ';' set @start = @start + 1 End I know that the above is wrong, but I don't know how to get it right. Ideally, I want the @testEmail to look like this: emailaddress1;emailaddress2;emailaddress3;
View Replies !
|
TRACKER
