Solved

SQL Difficulty importing to Excel

Posted on 2014-02-10
4
253 Views
Last Modified: 2014-03-06
I am trying to bring data into Excel with some SQL code. The code works fine and when I work with it in Microsoft Query it returns the data (while saying the Query cannot be represented graphically). However, when I return the data to Excel all I get is "Query from [Database]" and none of the data. Is there a way to get the data I need into a spreadsheet?

declare @curr int
declare @cstatus int
declare @cnotin int
DECLARE @cepp int
DECLARE @cproduction INT
DECLARE @cdone INT

set @curr= (select min(jobn) from OpenJob where jobStatus in (0,2) and jobN > 0)
SET @cnotin = 0
SET @cepp = 0
SET @cproduction = 0
SET @cdone = 0


WHILE @curr <= (SELECT MAX(jobN) FROM openJob WHERE jobStatus IN (0,2))
BEGIN

	IF EXISTS(SELECT jobn FROM openjob WHERE jobn=@curr AND jobStatus IN (0,2))
		BEGIN

			SELECT @cstatus=StatusCoden FROM statuscode where statuscoden = (select statuscode from JobStatus1 where jobn = @curr and transactionN = (select max(transactionN) from jobStatus1 where jobn=@curr))
			

			if @cstatus IS NULL set @cstatus = ''
			IF @cstatus BETWEEN 35100 AND 35108 SET @cnotin = @cnotin + 1
			ELSE 
				IF @cstatus BETWEEN 35109 AND 35254 SET @cepp = @cepp + 1
				ELSE
					IF @cstatus BETWEEN 35255 AND 78100 SET @cproduction = @cproduction + 1
					ELSE
						IF @cstatus >= 78101 SET @cdone = @cdone + 1	


		END	
	set @curr = @curr + 1
END

SELECT @cnotin AS 'Not In', @cepp AS 'EPP', @cproduction AS 'Production', @cdone AS 'Done';



			IF @cstatus BETWEEN 35100 AND 35108 SET @cnotin = @cnotin + 1
			ELSE 
				IF @cstatus BETWEEN 35109 AND 35254 SET @cepp = @cepp + 1
				ELSE
					IF @cstatus BETWEEN 35255 AND 78100 SET @production = @production + 1
					ELSE
						IF @cstatus >= 78101 SET @done = @done + 1	

Open in new window

0
Comment
Question by:mjburgard
  • 2
4 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39849050
The code works fine and when I work with it in Microsoft Query it returns the data
Your code does not return any data, it just sets some local variables.
0
 
LVL 11

Expert Comment

by:John_Vidmar
ID: 39850141
Perhaps something got chopped out of your example, like a SELECT-statement.

You could put all the logic and data-gathering into a stored-procedure, and get MS-Excel to run that stored-procedure to produce a result-set.
0
 
LVL 1

Author Comment

by:mjburgard
ID: 39871307
The code accidentally included extra lines. Everything after line 38 shouldn't be there. (Is it possible to edit the question and fix this? I cannot figure out how to.)

The SELECT statement on line 38 returns the data I was talking about.
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 400 total points
ID: 39872005
The SELECT statement on line 38 returns the data I was talking about.
My mistake I overlooked that.

So all you are missing is the following code at the top of your script:
SET NOCOUNT ON
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

930 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now