Help me with this one

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I've attached the sql 7 database to a new 2000 MSDE but when I access the db
with adp I cannot edit stored procedures or tables.Does anyone know what is
going on?

Thanks in advance

Armin
 
Are you able to do what you want with another database? Check the ownership
of the database/tables/views and sp.

S. L.
 
yes, I was able to do it with the SQL 7 Db.All my SP have ;1 added at the end.

sp_GETUSERID (old) New sp_GETUSERID;1

thanks in advance

Armin
 
This is the old bug which appended the version number at the end of the
stored procedure; probably because the SQL-Server 7 or 2000 didn't have the
latest service pack applied.

Sorry, I don't remember how to remove these other than recreating the
objets. You can try using the sp_rename procedure but I don't know if this
will work:

EXEC sp_rename 'sp_GETUSERID;1', 'sp_GETUSERID;1'

Please take note also that the sp_rename procedure doesn't modify the
syscomments table; with the effect that the next time you will try to edit
the stored procedure, the old name will be displayed in the text command.

Also, you shouldn't use the prefix "sp_" with your own stored procedures; as
this is reserved for the SQL-Server and may have some strange side effects.
See the BOL (online documention) for more information.

S. L.
 
First of all I would like to thank you for your responses.

the SQL 7 db with the adp part has been designed by someone who left the
source code (part of the contract I assume)and I'm making some changes to the
existing application.

My new question:

would it be easier to download the patches for SQL7, install them and then
detach and attach to SQL 2000 which is SP3.

or

to recreate everything?What is your opinion?

I can edit everything using SQL manager but I can't edit anything using the
adp project.However, on SQL 7 everything is fine, I can do everything.

Thanks

Armin
 
Hi,

Sorry, but it's too long now, so I have no opinion about downloading
patches for SQL7 or not before reattaching the database to SQL-2000;
however, it never hurts to have the latest patches applied, especially in
the case of SQL-Server. It also possible that a simple backup/restore will
repair the database.

If you have access to the Enterprise Manager, you can also use it to
generate SQL script for your database and then use BCP or any other mean to
copy your data or even simpler, use the DTS Export (or Import, if called
from SQL-2000) or the Copy Database Wizard to copy your database from
SQL-Server 7 to SQL-2000.

You can even use tools like SQLCompare/SQLDataCompare/SQLPackage from
RedGate software to do this kind of job.

S. L.
 
I forgot to say that in SQL-Server 7, the Copy Database Wizard had often
trouble with foreign keys, so it may be a good idea to delete them and,
after the copy, to recreate them on the target server.

S. L.
 
I solved it.Client didn't have any service packs installed (2000 dev
edition). It was driving me nuts that I can edit SP and everything on SQL but
not from adp so I tried with my laptop and it worked.

Thank you vary much for all your help.

Armin
 
Back
Top