try
{								
	SqlCommand cmd = new SqlCommand("SP_TEST_GET_SURVEY", conn);						
	cmd.CommandType = CommandType.StoredProcedure;
								
	conn.Open();
	SqlDataReader reader = cmd.ExecuteReader();	// SqlException thrown here

	...
}
catch (SqlException e)
{
	Console.WriteLine(e.Message);					
}

CREATE      PROCEDURE SP_TEST_GET_SURVEY  AS

DECLARE @select varchar(8000)
DECLARE @sumfunc varchar(100)
DECLARE @pivot varchar(100)
DECLARE @table varchar(100) 

SET @select = 'SELECT VW_ALL_SURVEYS.[Project Manager ID] '
SET @select = @select + 'FROM VW_ALL_SURVEYS '
SET @select = @select + 'WHERE VW_ALL_SURVEYS.[Project Manager ID] = ' + CAST(15 AS VARCHAR(12)) + ' '
SET @select = @select + 'GROUP BY VW_ALL_SURVEYS.[Project Manager ID]'

SET @sumfunc = 'min([PCT Manager])'
SET @pivot = 'VW_ALL_SURVEYS.Role'
SET @table = 'VW_ALL_SURVEYS'

DECLARE @sql varchar(8000), @delim varchar(1)
SET NOCOUNT ON
SET ANSI_WARNINGS OFF

EXEC ('SELECT ' + @pivot + ' AS pivot INTO ##pivot FROM ' + @table + ' WHERE 1=2')
EXEC ('INSERT INTO ##pivot SELECT DISTINCT ' + @pivot + ' FROM ' + @table + ' WHERE ' 
+ @pivot + ' Is Not Null')

SELECT @sql='',  @sumfunc=stuff(@sumfunc, len(@sumfunc), 1, ' END)' )

SELECT @delim=CASE Sign( CharIndex('char', data_type)+CharIndex('date', data_type) ) 
WHEN 0 THEN '' ELSE '''' END 
FROM tempdb.information_schema.columns 
WHERE table_name='##pivot' AND column_name='pivot'

SELECT @sql=@sql + '''' + convert(varchar(100), pivot) + ''' = ' + 
stuff(@sumfunc,charindex( '(', @sumfunc )+1, 0, ' CASE ' + @pivot + ' WHEN ' 
+ @delim + convert(varchar(100), pivot) + @delim + ' THEN ' ) + ', ' FROM ##pivot

DROP TABLE ##pivot

SELECT @sql=left(@sql, len(@sql)-1)
SELECT @select=stuff(@select, charindex(' FROM ', @select)+1, 0, ', ' + @sql + ' ')

PRINT @select

EXEC (@select)


SET ANSI_WARNINGS ON
GO

CREATE PROCEDURE SP_CROSSTAB
@select varchar(8000),
@sumfunc varchar(100), 
@pivot varchar(100), 
@table varchar(100) 
AS

DECLARE @sql varchar(8000), @delim varchar(1)
SET NOCOUNT ON
SET ANSI_WARNINGS OFF

EXEC ('SELECT ' + @pivot + ' AS pivot INTO ##pivot FROM ' + @table + ' WHERE 1=2')
EXEC ('INSERT INTO ##pivot SELECT DISTINCT ' + @pivot + ' FROM ' + @table + ' WHERE ' 
+ @pivot + ' Is Not Null')

SELECT @sql='',  @sumfunc=stuff(@sumfunc, len(@sumfunc), 1, ' END)' )

SELECT @delim=CASE Sign( CharIndex('char', data_type)+CharIndex('date', data_type) ) 
WHEN 0 THEN '' ELSE '''' END 
FROM tempdb.information_schema.columns 
WHERE table_name='##pivot' AND column_name='pivot'

/* @delim may be null if user lacks appropriate access rights to tempdb  */
SELECT @delim = IsNull(@delim, '''')


SELECT @sql=@sql + '''' + convert(varchar(100), pivot) + ''' = ' + 
stuff(@sumfunc,charindex( '(', @sumfunc )+1, 0, ' CASE ' + @pivot + ' WHEN ' 
+ @delim + convert(varchar(100), pivot) + @delim + ' THEN ' ) + ', ' FROM ##pivot

DROP TABLE ##pivot

SELECT @sql=left(@sql, len(@sql)-1)
SELECT @select=stuff(@select, charindex(' FROM ', @select)+1, 0, ', ' + @sql + ' ')

EXEC (@select)
SET ANSI_WARNINGS ON
GO

quote:

---

Originally posted by don_t

What's really perplexing to me is not the stored procedure itself, but the fact that the same stored procedure behaves one way when called from Visual C#, and differently from Query Analyzer or Server Explorer.

---

quote:

---

Originally posted by robvolk

You need to have CREATE TABLE rights in tempdb, and SELECT permissions on any table(s) that would be included in the cross tab. The tempdb part shouldn't be a problem, you can test it easily with CREATE TABLE ##temp and DROP TABLE ##temp. If either of those throw errors, you'd have to get that fixed. You might also need to have them set the "select into/bulk copy" option to "on" for tempdb, they might've forgotten to set it (or came up with a really dumb reason to turn it off)

---