Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Not displaying Columns in OLEDB Source Expand / Collapse
Author
Message
Posted Tuesday, October 5, 2010 4:00 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, June 18, 2014 3:47 AM
Points: 199, Visits: 512
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!!!
Post #998190
Posted Tuesday, October 5, 2010 4:32 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:31 AM
Points: 5,162, Visits: 12,010
Why does your data source contain a WHILE loop?


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #998208
Posted Tuesday, October 5, 2010 5:06 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, September 20, 2012 7:08 AM
Points: 27, Visits: 40
Try including SET NOCOUNT ON in the begining of your SQL script that is used in OLEDB source task.
Post #998222
Posted Tuesday, October 5, 2010 5:14 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, June 18, 2014 3:47 AM
Points: 199, Visits: 512
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.
Post #998225
Posted Tuesday, October 5, 2010 5:16 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:31 AM
Points: 5,162, Visits: 12,010
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?




Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #998227
Posted Tuesday, October 5, 2010 5:32 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, September 20, 2012 7:08 AM
Points: 27, Visits: 40
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.
Post #998240
Posted Tuesday, October 5, 2010 5:44 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, June 18, 2014 3:47 AM
Points: 199, Visits: 512
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.
Post #998247
Posted Tuesday, October 5, 2010 5:50 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, June 18, 2014 3:47 AM
Points: 199, Visits: 512


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 [table columns] can be today. If i run my Script tomorrow my From and To Date [table columns] 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>
Post #998250
Posted Tuesday, October 5, 2010 5:54 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:31 AM
Points: 5,162, Visits: 12,010
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 [table columns] can be today. If i run my Script tomorrow my From and To Date [table columns] 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'

?



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #998252
Posted Tuesday, October 5, 2010 6:14 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, June 18, 2014 3:47 AM
Points: 199, Visits: 512
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.
Post #998262
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse