SQLTeam.com | Weblogs | Forums

Import data from SQL Server to Excel failed

sql2012

#1

Hello,

I am trying to import data from SQL Server to Excel but the process failed. I am using a stored procedure. When open the Excel spreadsheet, the stored procedure executed and right after that the following message pops up.

Click on "OK".

Here is the screenshot of the connection properties.

I would appreciate for your help.

-Bill


#2

Remove: set nocount on
you can add it inside the stored procedure to suppress count of records message.


#3

Hello Joseph,

I removed "set nocount on": EXEC spPartsLocation then added right after BEGIN inside the stored procedure. Still getting the same message.


#4

Post your store procedure DDL please.


#5

Here is the store procedure:

USE [Reports]
GO

CREATE Procedure [dbo].[spPartsLocation]
AS
Begin
set nocount on

IF OBJECT_ID('[Reports].[dbo].PartsLocation1', N'U') IS NOT NULL
DROP TABLE [Reports].[dbo].PartsLocation1;

IF OBJECT_ID('[Reports].[dbo].PartsLocation2', N'U') IS NOT NULL
DROP TABLE [Reports].[dbo].PartsLocation2;

set ansi_warnings off
SELECT ItemCode,
MIN(CASE WhsCode WHEN '1F' THEN convert(varchar(11),isnull(U_Bin, '')) END) AS '1A',
MIN(CASE WhsCode WHEN 'Pink' THEN convert(varchar(11),isnull(U_Bin, '')) END) AS 'Pink',
MIN(CASE WhsCode WHEN 'Red' THEN convert(varchar(11),isnull(U_Bin, '')) END) AS 'Red'

INTO [Reports].[dbo].PartsLocation1
FROM [TEST].[dbo].OITW
GROUP BY ItemCode

SELECT *
INTO [Reports].[dbo].PartsLocation2
FROM [Reports].[dbo].PartsLocation1
WHERE [1A] <> '' OR [Pink] <> '' OR [Red] <> ''

END
GO


#6

ADD:

SELECT * FROM  [Reports].[dbo].PartsLocation2 ;

before END.
You drop the tables and do 2 select into statements but no select so your procedure returns no rows.
I suggest always run your procedures in SSMS to validate before using them in Excel or elsewhere.


#7

Joseph,

It works perfectly. Thanks so much.