MS SQL 2005 errors

[cosmicfool]

Hello,

I've been given the task of transferring a website from one server to another. Unfortunately the website was written in asp and is linked to a MS SQL database and I haven't had much experience with either. It was using a MS SQL 2000 database but the new host only has MS SQL 2005. I got a backup of the database and uploaded that to the new one and it all seemed to upload fine.

At first the website wouldn't connect but I changed the connection string to

Driver={SQL Native Client};Server=xxx.xxx.xxx.xxx, xxxx;Database=myDataBase;Uid=myUsername;Pwd=myPassword;

It's now coming up with an error which says:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][SQL Native Client][SQL Server]Could not find stored procedure 'sp_list_map_citys'.

I haven't been able to find any useful help about this error.

Apart from the connection string is there anything else I have to change to get it to work on MS SQL 2005 or should I be looking somewere else for the problem?

Sorry if this question has been answered elsewhere but I couldn't find anything to help me.

Any help would be greatly appreciated as I have to get it working soon.

Thank you

[dickersonka]

First make sure the stored procedure does exist

Second make sure the user specified in the connection has privileges to use it, if not log in as administrator and allow that user to access it

you can log in through sql management studio as that user after you apply your changes and see if its visible then

[cosmicfool]

Ok, I just need a small definition of a stored proceedure. Should there be a column in the database called sp_list_map_citys?

Because there isn't. In the source code of the website there is a line which links sp_list_map_citys to a column in the database.

I'm pretty sure the user I'm using has access because I can connect to it using SQL Server Management Studio.

Sorry if this is obvious stuff but I've been dropped in the deep end here and I have no support from the people who originally designed the site.

[dickersonka]

A stored procedure is a set of sql code for the database, not a column.

You can find it in the programmability section for the database.

The security on the stored procedure is separate from allowing the login, find the stored procedure going into programmability -> stored procedures for that database

[cosmicfool]

I checked for it and it doesn't exist.

Any suggestions. The old website is still running error free so that would suggest to me that it has this stored proceedure.

Any suggestions?

Thank you for you help so far.

[dickersonka]

Whenever you backed up the original database make sure to backup stored procedures, views, and users along with it. Then do the restore and they should be there.

[cosmicfool]

You sir, are god amongst men. You have solved all my woes. The website is working all nice and smoothly now.

I found that about 40 of the stored proceedures hadn't been uploaded.

Your help has priceless, thank you.