Invalid object name '#PageIndex'

Last post 05-09-2008 9:06 AM by JonFinley2001. 15 replies.

Page 1 of 2 (16 items) 1 2 Next >
	Sort Posts: Previous Next

11-23-2005 12:45 AM

johnnyj
Joined on 11-17-2005
Posts 11
Points 315

Invalid object name '#PageIndex'

Reply Contact

I had this problem a little while ago, then it seemed to disappear before rearing its ugly head again today.

I've even reinstalled the templates and recreated the database to no avail.

I checked all the generated SQL and it looks fine to me.

I appreciate that the stack trace isnt too helpful, but does anyone have any ideas?

Thanks in advance,

John.

System.Data.SqlClient.SqlException: Invalid object name '#PageIndex'.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, TdsParserState state)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, TdsParserState state)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
   at System.Data.SqlClient.TdsParser.Run(RunBehavior run, SqlCommand cmdHandler, SqlDataReader dataStream)
   at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.NextResult()
   at SchemaExplorer.SqlSchemaProvider.GetCommandResultSchemas(String connectionString, CommandSchema command)
   at SchemaExplorer.CommandSchema.get_CommandResults()
   at MoM.Templates.CommonSqlCode.IsMatching(CommandSchema command, TableSchema table) in c:\Program Files\SerialCoder\NetTiers 0.9.2 - Caribert\Templates\CommonSqlCode.cs:line 2125
   at _CodeSmith.EntityProviderBase_cst.__RenderMethod1(TextWriter writer, Control control) in C:\Program Files\SerialCoder\NetTiers 0.9.2 - Caribert\Templates\DataAccessLayer\Bases\EntityProviderBase.cst:line 901
   at CodeSmith.Engine.DelegateControl.Render(TextWriter writer)
   at CodeSmith.Engine.Control.RenderChildren(TextWriter writer)
   at CodeSmith.Engine.CodeTemplate.Render(TextWriter writer)
   at CodeSmith.Engine.CodeTemplate.RenderToFile(String path, Boolean overwrite)
   at _CodeSmith.NetTiers_cst.RenderToFile(String templateName, String path, Boolean overwrite) in C:\Program Files\SerialCoder\NetTiers 0.9.2 - Caribert\Templates\NetTiers.cst:line 453
   at _CodeSmith.NetTiers_cst.Go() in C:\Program Files\SerialCoder\NetTiers 0.9.2 - Caribert\Templates\NetTiers.cst:line 1870
   at _CodeSmith.NetTiers_cst.__RenderMethod1(TextWriter writer, Control control) in C:\Program Files\SerialCoder\NetTiers 0.9.2 - Caribert\Templates\NetTiers.cst:line 2154
   at CodeSmith.Engine.DelegateControl.Render(TextWriter writer)
   at CodeSmith.Engine.Control.RenderChildren(TextWriter writer)
   at CodeSmith.Engine.CodeTemplate.Render(TextWriter writer)
   at CodeSmith.Engine.CodeTemplate.RenderToString()
   at CodeSmith.Gui.CodeTemplateGenerator.b(Object , EventArgs )

Post Points: 175

11-23-2005 7:41 AM In reply to

dSquared
Joined on 09-01-2004
Paris - France
Posts 518
Points 260,900

RE: Invalid object name '#PageIndex'

Reply Contact

hi
it's look like your db user do not have the right to create table (so it cannot create the temporary table used by the paged method)

John Roland

------------------------------

Member of the .netTiers team

http://en.serialcoder.net

------------------------------

Post Points: 5

11-23-2005 6:33 PM In reply to

johnnyj
Joined on 11-17-2005
Posts 11
Points 315

RE: Invalid object name '#PageIndex'

Reply Contact

Hi John,

Thanks for the prompt reply.

I'm connecting as 'sa', and also tried creating a new 'nettiers' user with full rights to everything.

I can create tables ok manually using ISQL, but still get the exception when running the template.

Also tried integrated security with the same results...

Any other ideas?

Post Points: 5

11-23-2005 8:07 PM In reply to

johnnyj
Joined on 11-17-2005
Posts 11
Points 315

RE: Invalid object name '#PageIndex'

Reply Contact

More and more curious - I tried again, running against a different SQL server (2K Enterprise) under the sa account and everything ran fine.

Post Points: 5

11-23-2005 8:20 PM In reply to

gamania
Joined on 11-15-2005
Toronto
Posts 52
Points 1,220

RE: Invalid object name '#PageIndex'

Reply Contact

it must be your sql configuration issue..no temp table access somehow? rolleyes

Post Points: 5

11-23-2005 8:33 PM In reply to

johnnyj
Joined on 11-17-2005
Posts 11
Points 315

RE: Invalid object name '#PageIndex'

Reply Contact

I wish it were that simple! I just re-ran against the 'good' SQL server with the same settings, connection, etc, and got the error on the 2nd attempt.
Seems to be intermittent, and not related to the login account...

Post Points: 5

11-23-2005 9:23 PM In reply to

gamania
Joined on 11-15-2005
Toronto
Posts 52
Points 1,220

RE: Invalid object name '#PageIndex'

Reply Contact

one of the weirdest thing i have seen today...

Post Points: 5

11-24-2005 12:02 AM In reply to

johnnyj
Joined on 11-17-2005
Posts 11
Points 315

RE: Invalid object name '#PageIndex'

Reply Contact

It is throwing rendering the GetPaged functions. I've run each and every GetPaged sproc manually in ISQL under the SQL account that I'm using in my netTiers execute configuration and they all work. The SQL is good, and is the same I've used on previous generations that worked ok.

The exception is thrown when accessing CommandSchema.CommandResults in CommonSqlCode.cs IsMatching function (li 2125)

Post Points: 5

11-24-2005 12:41 AM In reply to

johnnyj
Joined on 11-17-2005
Posts 11
Points 315

RE: Invalid object name '#PageIndex'

Reply Contact

Looks like there was an old thread on this topic, but with no resolution:

http://forum.codesmithtools.com/default.aspx?tabid=&f=6&m=1177&p=1

Anyone else having the same problems?

Post Points: 5

12-13-2005 7:44 AM In reply to

trietpham
Joined on 11-11-2005
Posts 4
Points 50

RE: Invalid object name '#PageIndex'

Reply Contact

Hi all,

I'm having the same problem liked that.

But it only occurs when i create a custom stored procedure with paged.

Triet Pham

Post Points: 5

12-13-2005 7:13 PM In reply to

johnnyj
Joined on 11-17-2005
Posts 11
Points 315

RE: Invalid object name '#PageIndex'

Reply Contact

I'm still not sure I got to the real underlying reason for this error, but I found that by dropping ALL stored procs (some having been generated by earlier netTiers builds) then recreating them with the SQL script generated by the latest CTP release of netTiers, the problem disappeared.

Post Points: 5

12-14-2005 2:46 AM In reply to

trietpham
Joined on 11-11-2005
Posts 4
Points 50

RE: Invalid object name '#PageIndex'

Reply Contact

Hi johnnyj,

I think when you set IncludeCustom is true and if you have had a custom sp liked _TABLENAME_CUSTOMSP, Nettier will run that sp and analyze it to generate custom method in code. The trouble occurs here.
But when you run Nettier and set IncludeCustom is false or you don't have any custom sp, it will generate code and sql script. It will only run sql script to insert all generated sp into database. So that it runs OK.

Triet Pham

Post Points: 5

03-07-2006 6:49 AM In reply to

Paulie
Joined on 01-06-2006
Posts 5
Points 175

Re: Invalid object name '#PageIndex'

Reply Contact

This issue doesn't seem to have any closure to me - unless I missed a post somewhere. In which case can someone forward me in to the right direction or read on.

I have the same problem when using the autogenerate custom stored procedures functionality (using the _Object_FunctionName etc). It appears that .nettiers (or is it codesmith) tries to analyse the stored procedure to determine columns returned? This works for the majority of cases, however if you make reference / use #temptables inside of the procedure the analysis seems to fail and throw an exception.

A work around I have been using for the time being is to create these custom procedures without any internal SQL (or just a simple select statements to return the appropriate columns). Kind of like a method signature. After code generation is done overwrite these custom procs with the real deal.

It's an unfortunate extra step... which paves way for you to loose your latest copies of your stored procs when you accidently overwrite them before scripting them first but you learn pretty quickly the sequence you need to do things (maybe even script it.).

Any extra information on this would be most appreciated... I wouldn't mind even thinking about turning off the 'analyze' functionality and just have all custom procs return a DataSet.

Any thoughts?
Paul

Post Points: 35

03-07-2006 7:05 AM In reply to

Robert Hinojosa
Joined on 04-05-2004
Fort Worth, TX
Posts 2,795
Points 70,790

Re: Invalid object name '#PageIndex'

Reply Contact

Since your probably doing a select into the #tmpTable, it's still executing the statement and no resultset.

Try doing something like this to see if it works.

SET NOCOUNT ON
My Select into a #tmpTable

Set NOCOUNT OFF

Select * from #tmpTable

That should return the correct results.

Robert Hinojosa

-------------------------------------
Member of the Codesmith Tools, .netTiers, teams

http://www.nettiers.com
-------------------------------------

Post Points: 35

03-07-2006 7:31 AM In reply to

Paulie
Joined on 01-06-2006
Posts 5
Points 175

Re: Invalid object name '#PageIndex'

Reply Contact

I just tried your suggestion and whilst it doesn't fix the problem, I did determine that the issue is in fact intermittent. Actually every second time I autogen it works fine.

How odd. Well I'm happy enough with running the autogen twice (as opposed to the fake sp signature idea I was using previously).

thanks for your suggestion goofsr.
Paul

Post Points: 35

Page 1 of 2 (16 items) 1 2 Next >