SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 .NET Inside SQL Server (2005)
 invalid object name "tempdb.information_schema.col
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

robygus
Starting Member

Italy
1 Posts

Posted - 05/10/2013 :  02:52:45  Show Profile  Reply with Quote
Hi

I developed an application in c# 2005 with SQLServer 2005 with a store procedure to create a pivot and everything worked fine in 100 PC but now in a specific turkish pc ( windows XP sp3 user administrator ) the store procedure is not working and gives a message to the program when I try to use it "invalid object name "tempdb.information_schema.columns""

If I take the database from the pc and I put it in my pc everything works fine.

I thing this could be related to something specific in the pc, any idea?


Following the store procedure for pivot:

>>
set ANSI_NULLS OFF
set QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[PivotQueryJob]
@select varchar(8000),
@sumfunc varchar(100),
@pivot varchar(100),
@table varchar(100)
AS
DECLARE @sql varchar(8000), @delim varchar(1) , @conteggio varchar(10) , @vari as integer
SET NOCOUNT ON
SET ANSI_WARNINGS OFF


SELECT @vari = count(*) from job

if ( @vari > 0 )
begin
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 + ' ')

end

EXEC (@select)
SET ANSI_WARNINGS ON
>>>>

bandi
Flowing Fount of Yak Knowledge

India
2218 Posts

Posted - 06/04/2013 :  09:43:20  Show Profile  Reply with Quote
That was the issue with case sensitive...
You can use TEMPDB.INFORMATION_SCHEMA.COLUMNS

--
Chandu
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000