RSS

A Message Board, Guestbook, or Poll hosted for your website.
Iron Speed Technical Forums

Register Login New Posts Chat
 
Iron Speed > Forums > Running & Deployment Issues V6.X > Build Error: Unable to Create Database Stored Procedure
 
Username:  
Password:  
 
   
 


Thread Tools Search This Thread 
Reply
 
Author Comment
 
GeekZilla
Registered: 07/28/09
Posts: 20

    11/03/09 at 07:40 AMReply with quote#16
Hi Gerard-

Let me see if I understand your suggestion:
  Create a new empty database in SQL Server (call it db2)
  Copy the contents of the original db (call it db1) to db2.
  Run the app wizard and select db2.
  Let the app wizard begin generating and see if it successfully generates stored procedures.

Is that what you had in mind?

Gerard
Registered: 12/08/04
Posts: 588

    11/05/09 at 01:37 AMReply with quote#17
Correct. basically the idea was to see if it was a permissions issue as sql server or the access front end will throw error messages if it doesn't like something and give you clues where to go. Also if you have been working on database a bit it is possible that fingers get in the way and finding issue is really hard. this way generally get you back to normal. Rename when happy
Gerard
GS2
Avatar / Picture

Registered: 06/07/06
Posts: 68

    11/13/09 at 04:19 AMReply with quote#18
Hi, I had this error yesterday when trying to rebuilld after changing views...

here is how i corrected it.

Bascially i gave the database Connection user, Server DBcreator rights and then went in to each views with Ironspeed and unchecked the allow insert, update and delete box.

Once i had done this i could then regenerate the Stored Proces and i was able to update the Sql Server side of things

Finally Build the app so that the base classes will be updated and then go back over youre views and check the insert, update and delete box when required.

Rebuild again and you shoul dbe back to where you where before the errors started.
GeekZilla
Registered: 07/28/09
Posts: 20

    11/13/09 at 08:09 AMReply with quote#19
Hi GS2-

Thank you for responding. I was hoping you could answer some questions to help me understand the problem better.

Are you using SQL Authentication or Windows Authentication?  Also, can you tell me where you change the View settings for insert, update and delete?  I haven't had any views in this application until yesterday and knowing how to change those settings would be helpful.

Really glad you were able to solve your issue!

GS2
Avatar / Picture

Registered: 06/07/06
Posts: 68

    11/13/09 at 08:43 AMReply with quote#20
I am using a sql User...that permission are in sql security under logins..

Within Ironspeed there is a Left hand Tree, at the bottom is the databases node...

Simply start expanding the database structure and clikc on one of your views... on the right hand side you will see the configuration properties screen.

Look down at the borrom of the page you will see the  Allow, insert Update and Delete check box.

remember the view will need a Virtual Primary Key before generating base classes for events insert update and delete.

Hope that helps.
GeekZilla
Registered: 07/28/09
Posts: 20

    11/13/09 at 09:28 AMReply with quote#21
Thanks GS2. The check box was staring me right in the face.

Also, thanks for the feedback on how you are authenticating.  In my case I am using windows authentication and my account has sysadmin privileges to the SQL Server.
GeSmart
Registered: 11/13/09
Posts: 7

    01/26/10 at 03:45 AMReply with quote#22
Hi,
I have been going quietly insane all day today with trying to use views. I have generated the view and managed to get the pages built. I was wondering if anyone has a step by step process instead of the vague apply a virtual primary key somewhere and synchronize the database schema.

I am trying to use views because I could not get it working with any of the "workflow" pages as I was getting a record changed by another user error with that process when editing. It would let me change some fields, sometimes but if I clicked on another tab and changed anything I would get the error until I reran the app

If I run the app and try and edit or view a record generated from the View I get this

! The record could not be retrieved.

if I try it from from a workflow generated page I get

Unable to get record: idClient2Could not find stored procedure 'pTestApp1EditClientAllViewGet'.

I am using SQL EXPRESS 2003 and my view appears below

I have a virtual primary key on idClient, do I need foreign keys as well?

I am just about to give up and go back to our old nearly dead lotus notes and spreadsheets Any help would be greatly appreciated.

Cheers
Steve

My View:

CREATE VIEW dbo.EditClientAllView
AS
SELECT     dbo.Client.*, dbo.CustomerService.*, dbo.Results.*, dbo.Property.*, dbo.Employee.*
FROM         dbo.Client LEFT OUTER JOIN
                      dbo.Property ON dbo.Client.idClient = dbo.Property.idProperty LEFT OUTER JOIN
                      dbo.CustomerService ON dbo.Client.idClient = dbo.CustomerService.idCustomerService LEFT OUTER JOIN
                      dbo.Results ON dbo.Client.idClient = dbo.Results.idResults LEFT OUTER JOIN
                      dbo.Employee ON dbo.Client.idClient = dbo.Employee.idEmployee

 
Attached Files:
doc goingnuts.doc (252.50 KB, 14 views)

pk_davidson
Registered: 07/09/09
Posts: 417

    01/26/10 at 11:22 AMReply with quote#23

My experience with views has been:
1. First get them working under SQL, that way you know they work and they return the stuff you want.

2. I've had sketchy luck with making a view with more than one join be updateable.  You might try turning off the "allow insert,update, delete" flag in the database part of ISD.  If you can get it to work as a read only page in ISD, then at least you know you have the beginning working.  But to get that many joins working and to have them be updatable via a view is going to take some focused work on your part. 

You're going to have to structure your Virtual and Foreign Primary keys correctly.  You need to put work into your schema because if that view is laid out correctly then all those id links require PK/FK relationships. 

The basic reason you could be getting that error is because no record is meeting the join condition you've laid out.  To retrieve a record:

Client id and Property id have to match
Client id and Customer Service id have to match
Client id and Results id have to match
Client id and Employee id have to match

Does this happen?  This is one reason you want to get this working under SQL first, so at least you know your layout assumptions are correct.

3. Make sure you have updated your schema recently.

Often when I've received the error "The record could not be retrieved" and the links are valid and correctly constructured, it is because my ISD app is out of synch with the database schema. 

Another common issue is that I have changed a field name in my schema but forget to change it in my code.  This has given me all sorts of weird errors. 
My suggestions would be:
   make sure you have Primary/Foreign Key links across all the tables defined in your schema.  If you do this ISD will work much better and you probably won't need any Virtual keys.

   After you get all the keys defined, put indexes on all of them.

Then define the view in SQL and make sure it returns all the records you expect.

   Me, I'd go back to a standard page and use tabs/panels to bring in all of the other tables associated with your main customer one.

If you have trouble doing that, then post back here with those problems.

The more experienced guys can give you better direction than me but it seems to me like you're pushing the use of a view here in a situation where a standard page with tabs or panels would better take of the interactions of your tables.

Hope some of this might help.

PS - if you want to continue to pursue the view thing, I'd take a step back and start with two tables in a view, get it working as a read only, then make it updatable, then add in the third table as a read only, then make that readable, etc...    Structure coding allows you to build pieces that build on pieces, etc... so that you know your basics are solid as you build upward.  Trying to get a complicated page working right out of the gate can be frustrating.
__________________
Paul
GeSmart
Registered: 11/13/09
Posts: 7

    01/28/10 at 12:25 AMReply with quote#24
Hi Paul,

Thanks for the response the view works fine and returns what I expected in SQL Manager. But I really would like to get the tabbed pages working I have a

Select and Show Record - editable detail record on side.

All the right data is in all the right places but if I change a field on a panel and save, it say's the record has been changed by another user. I have regenerated the page several times and keep getting this error. Although at one stage I could change the field data and it would save provided I did not change panels. But I broke that version by trying different things.

Cheers

Steve

pk_davidson
Registered: 07/09/09
Posts: 417

    01/28/10 at 11:41 AMReply with quote#25
Hi Steve:

I seem to recall having had that problem before but unfortunately I don't recall anything about it.  I have a vague recollection that it was one of those "oh duh..." moments where I'd made a simple error and just wasn't seeing it.  If I ever remember what it was in my case, I'll let you know.

Questions:
Have you done any code customizations ?
Are you using security ?
What's the setup here ?  Is it all on a local machine using IIS or is it a deployed or test server environment.

I can think of a few other suggestions that I'd pbbly try:
1. Run the SQL tracer and see what name is being used when accesing the database.

2. Verify all of your IIS and database setups.  Check names, permissions, etc...

3. Try building a new page.  If you have any customizations on your current page then build a new one and start applying your customizations one at a time, testing as you go.

4. Go into Visual Studio and start tracing and watching variables.  Sprinkle temp variables around if you need to see things like logged in user name, etc...

Good luck...
__________________
Paul
chrisblake
Registered: 08/10/09
Posts: 4

    04/09/10 at 02:49 PMReply with quote#26
Had the same problem in v6.2.2 and fixed it by regenerating just the 18 views I had using inline and rebuilding the app, deleted the new duplication web reports which were the only things fed by the 18 views and synced schema, rebuilt all and problem went away.
Previous Thread | Next Thread
Page 2 of 2    < 1  |  2
Reply

  Bookmarks  
Digg Diggdel.icio.us del.icio.usStumbleUpon StumbleUponGoogle Google

Download Iron Speed Designer

Copyright � 2005-2009 Iron Speed, Inc. Iron Speed� is a registered trademark. All rights reserved. Privacy Statement