Issue while importn data into Excel usg while loop

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

Issue while importn data into Excel usg while loop

Author

Topic

SN32
Starting Member

5 Posts

Posted - 2010-09-23 : 13:02:20

Hi All,

I've written a procedure which has WHILE LOOP and iterates itself certain number of times. My intention is to run this procedure and import
data into excel spreadsheet. The problem I'm facing is that when the procedure runs only the data that gets generated during the first WHILE LOOP gets displayed in the excel and the rest gets ignored. Just to briefly explain what I mean, when the below SP runs and @A = 0 the results gets displayed in the excel spreadsheet but when it loops back and the value becomes 1 (because of SET @A= @A+ 1) nothing happens
Here's the code snippet:


Use [databasename]
DECLARE @A varchar(2)
SET @A = 0
WHILE @A <=5)
BEGIN

Select * from dbo.randomtables where columnnameA = @A

SET @A= @A+ 1
END
GO

I run this query in the SSMS and the query runs fine except the fact that the resultsets are displayed in seperate Result pane. Can someone shed some light on how can I get results from all the LOOPS and not just the first one?

Below is my environment specs:
DB info: Sql server 2005 Build 3790: Service Pack 2
Server Info: MS 2003 Service Pack 2

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-09-23 : 13:21:10

why should you loop? isnt it enough to just fire a select query which retrieves data in a batch and use bcp or OPENROWSET to export it to excel?

http://www.mssqltips.com/tip.asp?tip=1202

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

SN32
Starting Member

5 Posts

Posted - 2010-09-23 : 13:41:24

quote:
Originally posted by visakh16

why should you loop? isnt it enough to just fire a select query which retrieves data in a batch and use bcp or OPENROWSET to export it to excel?

http://www.mssqltips.com/tip.asp?tip=1202

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Thanks for your prompt response Visakh!
To give you a gist of what I want to achieve: I'm trying to create a report that pulls data from Sql Server using a Data connection (DC). This DC in turn calls a SP which has While Loop in it. I cant use OPENROWSET since this feature is disabled.

How do you propose I accomplish this task?
Using BCP is it possible to call stored procedure from it.

Thanks!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-09-23 : 13:44:16

you can use bcp with query out option and give query as exec procedure .... to execute the procedure and get results

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

SN32
Starting Member

5 Posts

Posted - 2010-09-23 : 14:02:25

quote:
Originally posted by visakh16

you can use bcp with query out option and give query as exec procedure .... to execute the procedure and get results

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Thanks again! This solution should work if the users run their reports from the server itself (where sql server is installed), correct? In my case the user opens/refreshes the report from their workstation. Any suggestions?

Thanks!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-09-23 : 14:04:22

you mean you're trying to generate excel from reports? then why use bcp ? you can directly export it to excel from SSRS itself

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

SN32
Starting Member

5 Posts

Posted - 2010-09-23 : 14:13:26

quote:
Originally posted by visakh16

you mean you're trying to generate excel from reports? then why use bcp ? you can directly export it to excel from SSRS itself

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Sorry that I wasn't clear enough.

quote:
you mean you're trying to generate excel from reports?

No, I'm using excel to import data from sql server using data connection
FYI: I'm not using SSRS.

Thank!!

SN32
Starting Member

5 Posts

Posted - 2010-09-24 : 13:21:57

I managed to put in the BCP command into excel data connection but now it does the same thing. That is, it just returns the result set from the first loop as mentioned in my previous entries and ignores the remaining rows. Can someone help?

Thanks!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources