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 @ 11:18 AM
Points: 5,316, Visits: 12,346
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.

It is better to keep your mouth shut and appear stupid than to open it and remove all doubt. (Mark Twain)
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 @ 11:18 AM
Points: 5,316, Visits: 12,346
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.

It is better to keep your mouth shut and appear stupid than to open it and remove all doubt. (Mark Twain)
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 @ 11:18 AM
Points: 5,316, Visits: 12,346
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.

It is better to keep your mouth shut and appear stupid than to open it and remove all doubt. (Mark Twain)
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