Or you could create the temp table outside the proc, before calling the proc.

CREATE PROC TEST3

AS

-- Proc refers to a temp table that...

-- a normal temp table does not work

CREATE PROC TEST

AS

SELECT 'Test' AS Col1, 'Row' AS Col2

INTO #TEST ;

GO

-- now run the proc

EXEC TEST;

-- and...

Just to be complete, the actual solution is as follows (slightly different from Mark's solution)

SELECT t.[AGE],ca.[Rate],ca.[Amount]

FROM [dbo].[Sheet1$] t

CROSS APPLY (VALUES(2.00,[2#00%]),(2.25, [2#25%]), (2.50, [2#50%]),(2.75, [2#75%]), (3.00, [3#00%]),(3.25, [3#25%]), (3.50, [3#50%]),(3.75, [3#75%]))...

Many thanks. I knew there must be a simple solution. 😀

* Note to self: Must learn about CROSS APPLY *

What the...

What the...

I haven't had this behaviour with SSMS but I have had it with Visual Studio and the XAML applications I'm working on and IE10 - but only on this new...

You want help because your code does not function the way you expect it.

We can help but we have to understand your code.

All we can do is , if we...

If I run this

DECLARE

@PCN_Key AS INT = '',

@CostSetKey AS INT = '1609',

@Part_Type_MP AS VARCHAR(100),

@Part_No AS VARCHAR(100) = '',

@Cost_Date AS VARCHAR(20),

@Building_No_MP AS VARCHAR(1000)='',

@qq...

There are some oddities in the code - but this could be a cutnpaste issue

you are setting some INTs to string values

@PCN_Key AS INT = '',

@CostSetKey AS INT =...

DECLARE @StartDate DATETIME;

SELECT @StartDate = DATEADD(year, DATEDIFF(year,0,GETDATE()), 0);

-- This clause is just to get some numbers: a Tally table, for joining to get a range of dates

WITH NumDays AS (...

Thanks, I have been using out of date reference book, where column number only goes up to 44, looks like loads more have been added since.

Have found the latest...

Unfortunately its a complex alphanumeric, not an integer sequence

I was a bit reticent to try indexed views because last time I did that I disappeared down a rabbit hole,...

What fixed this was connecting to SqlExpress in Visual Studio, Server Explorer - creating a new data connection.

This re-created the C:\Users\<username>\AppData\Local\Microsoft\Microsoft SQL Server Data\SQLEXPRESS directory - with default copies of...

Thanks Gail,

For some strange reason in SSMS the dbname was [C:\inetpub\wwwroot\website1\App_Data\ASPNETDB.mdf] I renamed it to ASPNETDB - now the cross database query works.

So I guess I don't have to...