Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2004
    Location
    New Jersey
    Posts
    65

    Unanswered: SP Designer Validation and Temp Field issues

    Hi all. I was wondering if there was a way to supress the error messages while designing an SP in Enterprise Manager, such as if you have a column name in your T-SQL that doesn't exist in a table and you click 'Apply' or 'OK', Ent Mgr throws an error 'Invalid column name ...' and you cannot save the SP.

    The reason I need to do this is because I am writing a large data-transforming SP. The SP takes data from an old, un-normalized DB and converts the data into a new, normalized format. I need two temp fields in three tables. These fields will act as references to bridge the data transformation from the old data to the new. So, I will create the temp fields in one SP, run the data transformation in a second SP, and drop the fields in a third SP. Note that I split out the adding and dropping of the fields because of experimenting with some debugging of the SP failure.

    Which brings me to my second issue. If I run the SP to add the temp fields, the SP runs fine and the fields are added. Then I can open the data x-fer SP, develop and debug it, and click 'OK' or 'Apply'. No problem. Then, I can run the SP to drop the temp fields.

    However, I'd like to have everything in one SP. This way, when I need to transform the production data, I can just execute one SP all under the scope of one BEGIN/COMMIT TRAN transation. Assuming that I have everything in one SP: if I comment out the lines that drop the temp fields, then the SP runs fine. If I uncomment the lines that drop the temp fields (at the end of the SP), then the SP fails, with and error in the middle part of the code that does an INSERT INTO <temp_field_created_in_the_SP>.

    Why would the SP fail to insert into temp fields if I uncomment lines of code at the end of the SP to drop those fields. Does it have to do with the scope of the transaction? I even tried to COMMIT the main transaction that adds the fields and transforms the data, and wrapped the dropping of the fields in a separate transaction. It still errors out.

    Here is the code to add and drop the temp fields:
    Code:
    IF NOT EXISTS (SELECT * FROM syscolumns WHERE ID=OBJECT_ID('Customers') AND NAME='TEMP_ad_addr')
        ALTER TABLE Customers ADD TEMP_ad_addr VARCHAR(80) NULL
    
    .
    <transform all data code>
    .
    
    IF EXISTS (SELECT * FROM syscolumns WHERE ID=OBJECT_ID('Customers') AND NAME='TEMP_ad_addr')
        ALTER TABLE Customers DROP COLUMN TEMP_ad_addr
    Last edited by HardCode; 05-04-07 at 12:51.
    Retired Moderator at Xtreme VB Talk, for all of your Visual Basic needs.

  2. #2
    Join Date
    Oct 2004
    Location
    New Jersey
    Posts
    65
    Okay. I resolved the issue of getting errors about the temp fields not existing. I just ran all three SPs separately. One to add temp fields, one to transform the data, and one to drop the temp fields. There must be an issue of scope where the SP adding the fields have to complete before dropping them.

    However, I am still in the situation where after I run the three SPs successfully, I cannot modify the data transfer SP, because the temp fields aren't there anymore. Ent Mgr won't let me 'A'pply any changes. I have to run the SP to add the temp fields first, then modify the data SP. Is there any way around this?
    Retired Moderator at Xtreme VB Talk, for all of your Visual Basic needs.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •