October 5, 2010 at 4:00 am
Folks,
I have a script which has while loop and the same script working fine in Management Studio. But if i use the same set of script in OLEDB Source it not displaying any of the columns in OLEDB Source Editor.
If i remove the while loop it display all the columns as expected. Can help me how to solve this?
Appreciate your Help!!!
October 5, 2010 at 4:32 am
Why does your data source contain a WHILE loop?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
October 5, 2010 at 5:06 am
Try including SET NOCOUNT ON in the begining of your SQL script that is used in OLEDB source task.
October 5, 2010 at 5:14 am
Phil Parkin (10/5/2010)
Why does your data source contain a WHILE loop?
Actually, i need to get the data's according to From and To Date from a table. So for that i used this while loop.
Even i used NOCOUNT ON. But sorry i didn't get the columns yet.
October 5, 2010 at 5:16 am
sqlusers (10/5/2010)
Phil Parkin (10/5/2010)
Why does your data source contain a WHILE loop?Actually, i need to get the data's according to From and To Date from a table. So for that i used this while loop.
Even i used NOCOUNT ON. But sorry i didn't get the columns yet.
Why not a WHERE clause? Sounds like you're getting the records one at a time?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
October 5, 2010 at 5:32 am
I guess you are using table variable inside your SQL query. If that is the case, then OLEDB source task will not generate the columns.
Use a stored procedure instead of the SQL script, this will generate the columns.
October 5, 2010 at 5:44 am
Leo Suresh (10/5/2010)
I guess you are using table variable inside your SQL query. If that is the case, then OLEDB source task will not generate the columns.Use a stored procedure instead of the SQL script, this will generate the columns.
ohh... yes, i use table variable inside. Let me check. Thanks Leo.
October 5, 2010 at 5:50 am
Why not a WHERE clause? Sounds like you're getting the records one at a time?
Exactly i want to get all the data's. That'y i use loop through.
Here in table my StartDate can be From and To date is one and the same. For ex, if i run the script today, My From and To Date
can be today. If i run my Script tomorrow my From and To Date
can be tomorrow.
Exactly my script is like this.
DECLARE @StartDate DATETIME,@LEndDate DATETIME,@EndDate DATETIME
@StartDate = '2010-10-02'
@LEndDate = '2010-10-05'
WHILE @StartDate <= @LEndDate
BEGIN
SET @StartDate = @StartDate
SET @EndDate = @StartDate
<Insert Statement>
<SELECT Statement WHERE FromDate = @StartDate AND ToDate = @EndDate>
<Calculation Part>
SET @StartDate = @StartDate + 1
END
<SELECT ST>
October 5, 2010 at 5:54 am
sqlusers (10/5/2010)
Why not a WHERE clause? Sounds like you're getting the records one at a time?
Exactly i want to get all the data's. That'y i use loop through.
Here in table my StartDate can be From and To date is one and the same. For ex, if i run the script today, My From and To Date
can be today. If i run my Script tomorrow my From and To Date
can be tomorrow.
Exactly my script is like this.
DECLARE @StartDate DATETIME,@LEndDate DATETIME,@EndDate DATETIME
@StartDate = '2010-10-02'
@LEndDate = '2010-10-05'
WHILE @StartDate <= @LEndDate
BEGIN
SET @StartDate = @StartDate
SET @EndDate = @StartDate
<Insert Statement>
<SELECT Statement WHERE FromDate = @StartDate AND ToDate = @EndDate>
<Calculation Part>
SET @StartDate = @StartDate + 1
END
<SELECT ST>
Why not this
select statement
where FromDate >= '2010-10-02' and ToDate <= '2010-10-05'
?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
October 5, 2010 at 6:14 am
Why not this
select statement
where FromDate >= '2010-10-02' and ToDate <= '2010-10-05'
?[/quote]
yes, Thanks Phil. I am going like Horse eye. :w00t:
October 5, 2010 at 6:47 am
sqlusers (10/5/2010)
Leo Suresh (10/5/2010)
I guess you are using table variable inside your SQL query. If that is the case, then OLEDB source task will not generate the columns.Use a stored procedure instead of the SQL script, this will generate the columns.
Leo, if i create this an SP still i am not getting any columns in OLEDB Source. Do i need to set any thing?
October 5, 2010 at 8:21 am
Do you get any records, if you execute the SP??
Try this...
SET NOCOUNT ON
Exec SP
October 5, 2010 at 9:55 am
sqlusers (10/5/2010)
sqlusers (10/5/2010)
Leo Suresh (10/5/2010)
I guess you are using table variable inside your SQL query. If that is the case, then OLEDB source task will not generate the columns.Use a stored procedure instead of the SQL script, this will generate the columns.
Leo, if i create this an SP still i am not getting any columns in OLEDB Source. Do i need to set any thing?
First, see this thread.
Second, as I gave you a pure T-SQL solution, why are you still using a stored proc to do this?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
October 6, 2010 at 6:26 am
First, see this thread.
Second, as I gave you a pure T-SQL solution, why are you still using a stored proc to do this?
Some times From and To Date be a changeable one. So i used package variable which can get the values from the users. So, i created the same as an Procedure.
Now my OLEDB works fine and getting columns. Thanks for sharing the info. :w00t:
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply