Post reply

Special characters showing in stored proc

  • January 22, 2004 at 3:41 pm

    #106847

    We've ran into a strange situation where when we script our stored procedures from our dev server and run the script on production it is adding a special character for a return then bringing up the next line onto the same line. It's not in every case and we have many sps that have returns in them.

    What it does is that if the line is a comment and the next line is a Select it will  show like

    --comment | select

    instead of showing like

    --comment

    select

    Has anyone seen this. Is it the way the script is being generated (to a txt instead of sql) ?

    It has bitten us and we'd like to find a way to solve it.

    Any help is appreciated .

    Thanks K

     

  • January 22, 2004 at 5:18 pm

    #491249

    Kelsey,

     

    How are you scripting the procs??  How were the procs created??  Are you scripting using ANSI or UNICODE

    It looks like the developer probably modified the code in a text editor and then pasted it into the DEV server.  When you are scripting the code out (using EM I assume) the SQL server probably is misreading the characters.

    I would use a text editor with FIXEDSYS font to check ALL of your sp's correct them by removing the characters and running on the DEV box and then moving them to the PROD box.

     

    Good Luck

     



    Good Hunting!

    AJ Ahrens


    webmaster@kritter.net

  • January 22, 2004 at 10:11 pm

    #491264

    I remember seeing that problem a few years ago, and am having no luck trying to find it again.  My guess is that it is might be in SQL*Server 7 and not 2000.  It may be related to the storage of the procedure text in the database, occurring where the split into separate records occurred.

    Whether or not you see the problem may depend on how you do your transfer of the text - cut and pasted between two windows when using Enterise Manager or generating the script in Enterprise Manager and then using Query Analyzer on the target system.

    Another possibility is that is related the operating system on your computer - I've changed from '98 to XP.

  • January 23, 2004 at 8:47 am

    #491394

    We're on SQL 2000 sp 3, server is on Advanced Server but workstations are XP or 2000 Pro.

    I script them out to Unicode txt file when I restore our dev server to run after the db is restored. Most of them do have it at the top of each sp. I just created two new ones from EM and those are fine.

    But my developers do copy and paste from QA to EM and that may explain the ones that come up in the middle.

    I will try my new ones through different scripting and see if it still happens.

    Thanks for the ideas Aj and Mike.

    K

     

     

  • January 23, 2004 at 2:36 pm

    #491467

    I think what I would do is change your methodology. I prefer to have all my SPs saved as scripts. Anytime I change the SP I do it from the main script I have after I pull it out of source control. Then I run the script against my dev machine and if everything works I then put it on the production machine and check the script back into source control.

    BTW: I do all my script development in Visual Studio against a Database Project. The DB Project is controlled by source control. Since all my scripts are in the project I have easy access to them from within the project.




    Gary Johnson
    Microsoft Natural Language Group
    DBA, Sr. DB Engineer

    This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

  • January 23, 2004 at 3:45 pm

    #491479

    We've been throwing around ideas of how best to integrate with Source Safe. I like your idea and I think it would solve some other issue we have. But my question is what kind of project is it (just an empty project) and what are the file types of the procedure scripts. If I have a stored procedure in source safe how do I add it to the .Net project

    Sorry may seem basic but I'm a newbie to .net.

    Thanks for your help

    K

  • January 23, 2004 at 3:55 pm

    #491480

    As I stated it is a "Database Project" that I created. In Visual Studio .Net it is under "Other Projects". When you create the project it will ask you to create a default database connection. This connection can be seen if you open the "Server Explorer". Working with the Server Explorer you can add as many database connections as you like. I think I have about 40 of them at any one time. There are a few things I don't like about this but over all it is better than using EM or QA for most things. I still tend to open QA and load the script I have edited in VS.Net. You can also edit the SP's/Functions directly against the DB using the Server Explorer. Then once you have them the way you want them copy the script and pasted it into a file window(I wish you could just do a save as here!).

    Using VS.Net allows you to have macros and do columnar editing. I use both of these features a lot. Also note that I have been doing this for a lot longer than I have worked where I do now




    Gary Johnson
    Microsoft Natural Language Group
    DBA, Sr. DB Engineer

    This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy