Import data from SQL Server to Excel failed

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.

960×146 5.09 KB

Click on "OK".

Here is the screenshot of the connection properties.

964×503 23.5 KB

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.

1 Like
7

Joseph,

It works perfectly. Thanks so much.