Cannot run SQL2005 stored procedure from excel 2003

Usually have no problems pulling data into excel via SQL stored procedures until now.

Created an sp that contains a

declare @.tbl table(

Customerno varchar(6)

,InvoiceDate varchar(25)

,CustomerType varchar(10)

,CustomerRegion varchar(50)

,CustomerTypeName varchar(50)

)

Followed by a insert then a select statement on this @.tbl variable.

whenever i try to call this sp i get an error message saying "The query did not run, or the database table could not be opened"

This is what i'm using to connect:

With Sheets(1).QueryTables.Add(connection:="OLEDB;Provider=SQLOLEDB.1;" & _
"Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=UnityBI;" & _
"Data Source=SQLdatabase;Use Procedure for Prepare=1;Auto Translate=True;" & _
"Packet Size=4096;Workstation ID=ACER-DAVEW;Use Encryption for Data=False;" & _
"Tag with column collation when possible=False", Destination:=Sheets(1).Range("A1"))
.CommandType = xlCmdSql
.CommandText = "usp_gordonsnodrops "
'.Name = "proclarity UnityBI_2"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With

The stored procedure runs fine from other applications, just excell seems to be the problem.

Any help would be greatly appreciated.

Ok, after a very frustating week with this problem the answer turns out to be very easy.

Kinda deflating really after the amount of hair i've pulled out.

The stored procedure needed the statement "Set NoCount On" adding.

Works like a charm now

eg

ALTERPROCEDURE [dbo].[usp_GordonsNoDrops]

@.MonthYear asvarchar(50)='march06/07'

--@.End as varchar(10)

as

begin

setnocounton

sql statements etc....

end