Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 PLEASE DELETE

Author  Topic 

xrdomains
Starting Member

28 Posts

Posted - 2006-02-16 : 20:50:40
Please delete. Your members are unable help. Thanks anyway!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-16 : 20:55:50
>> The only software I have available to me on the new dedicated hosting server is ASP.Net Enterprise Manager
You will definately need to install the MS SQL Server 2000 on your new server.

You can use Enterprise Manager to backup the databases, copy over to the new server and restore it there

----------------------------------
'KH'

Time is always against us
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-16 : 21:01:20
Yes. It comes with MS SQL Server 2000. Check Start - All Programs - Microsoft SQL Server. It should be listed there together with Query Analyser and Books Online etc

----------------------------------
'KH'

Time is always against us
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-16 : 21:41:44
The SERVER shown on the Service Manager is it your new server ? If so looks like the Enterprise Manager and other tools are not installed. Only the database serve is installed. Get hold of the installation CD and installed it


----------------------------------
'KH'

Time is always against us
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-16 : 21:43:52
Alternatively you can also backup and restore using t-sql script.

Using BACKUP DATABASE and RESTORE DATABASE

----------------------------------
'KH'

Time is always against us
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-16 : 22:40:54
as you have the mdf & ldf, you can attach the database directly.

This is from Books OnLine
quote:
sp_attach_db
Attaches a database to a server.

Syntax
sp_attach_db [ @dbname = ] 'dbname'
, [ @filename1 = ] 'filename_n' [ ,...16 ]

Arguments
[@dbname =] 'dbname'

Is the name of the database to be attached to the server. The name must be unique. dbname is sysname, with a default of NULL.

[@filename1 =] 'filename_n'

Is the physical name, including path, of a database file. filename_n is nvarchar(260), with a default of NULL. There can be up to 16 file names specified. The parameter names start at @filename1 and increment to @filename16. The file name list must include at least the primary file, which contains the system tables that point to other files in the database. The list must also include any files that were moved after the database was detached.

Examples
This example attaches two files from pubs to the current server.
EXEC sp_attach_db @dbname = N'pubs', 
@filename1 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf',
@filename2 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs_log.ldf'




----------------------------------
'KH'

Time is always against us
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-16 : 22:53:53
use

DROP DATABASE yourdatabasename


----------------------------------
'KH'

Time is always against us
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-17 : 00:39:35
you can't cut down a tree branch if you are sitting on that branch. Actually you can if you want to

Make sure you are not in that database and no other users are accessing it before dropping it.

use master
drop database yourdatabase


----------------------------------
'KH'

Time is always against us
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2006-02-17 : 21:29:15
"Error: The object 'usd_User' does not exist in database 'MyDB'.
Source: .Net SqlClient Data Provider
"

The database you restored, on your new server, has IDs for users based on the Login IDs of the old server. You can sort this out with:

EXEC sp_change_users_login 'Auto_Fix', 'MyUserID'

If a login for the user does NOT already exist then you'll need to create one first:

EXEC sp_addlogin 'MyUserID', 'MyPassword', 'MyDatabaseName'

"if I rename it to index.htm it loads"

That's 'coz IIS will just "display" an HTM file, whereas it has to "execute" an ASP file. I reckon that the User permissions thing may well be why INDEX.ASP is not running, so once you've fixed that the problem may go away.

If not:

You need to change the default behaviour of the 500.100 error so that it displays more information. You do this in the IIS Maintenance Tools:

Programs : Administrative tools : Internet Information Server

Expand Web Sites and then right click your specific web site

You need to add a Virtual folder (I usually call it "errors") pointing to:

C:\WINNT\Help\iisHelp\common (might be "WINDOWS" on your machine)

Then right click the site again and choose : Properties

[Custom Errors] tab

Scroll down to the 500.100 and press [Edit] and change it to:

Message type : URL
URL : /errors/500-100.asp

Make a note of what it was originally, you may want to set it back to that when you are done (this more verbose reporting style is usually not appropriate for live sites as it can "give away" too much information to hackers when an error occurs!)

Kristen
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2006-02-17 : 21:43:24
Some more info here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61966
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2006-02-17 : 22:15:51
"Should I still run this command on it?"

Only if you need to reinstate the original user (for example, if it had a complex set of permissions assigned to it)

"[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'usd_bannerSettings'.
/ads/Include/Constants_inc.asp, line 25
"

It must have got connected to the database, otherwise you'd get some sort of connection error. So that fact that it is trying to access the object 'usd_bannerSettings' looks like good news to me ...

I reckon this is a permissions error - the user that the application is logging on as is not able to see this table. So you may need to re-synchronise that user.

Just to double check, if you take the actual UserID/Password details from "UID=[userid];PWD=[password]" and use that to connect to SQL Server (in Query Analyser, for example) then you will be running with the same permissions as you application. If you can't see the table then its definitely a permissions problem!

Kristen
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2006-02-18 : 06:03:14
Your connection string looks fine to me. Only other option would be to use Trusted Security, but doesn't look like that was how it used to be set up.

If you can connect to the server using Query Analyser WITH the user credentials then you are good-to-go on that score.

Other possibility is that the original User ID has specific permissions associated with it. In that case you do really need to resynchronise that user [between the restored database and the new server] and connect using that user's UserID - changing the password is fine.

You could check if that [original] user had "special" permissions in enterprise manager:

Select database : Users : Right click the specific user : Properties

Are there any "Roles" at the bottom, BELOW db_denywriter? particularly if they are ticked? Is so that's a specific role set up with the permissions the application needs. You could apply that role to the new user [your application is connecting with], or synchronise the old user as mentioned above.

Whether that is the case OR NOT then also click on the [PERMISSIONS] button.

If you see ANYTHING ticked here that means the developers set up the permissions specifically for this user (bad move generally, but its possible!). In which case each of these permissions needs to be applied to whatever user connects (again, synchronising the user as above is the easiest way to sort this out).

If you've Dropped and Recreate the original user then this info will have been lost, so your best bet would be to re-restore the backup (you could do that to a different, temporary, database if you like - that would enable you to have a look at it "how it was".

Final thing: In Enterprise Manager right-click the SERVER (rather than the database) : Properties : Security

Check that "Authentication" is set to "SQL Server and Windows" and NOT to "Windows only". (This is effect will allow a named user to connect, otherwise only windows [domain] users can connect [trusted security].

Kristen
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2006-02-19 : 03:30:53
"The object 'usd_Advertiser' does not exist in database 'testdb'"

This is probably permissions - SQL Server is making that the table doesn't exit, which is a good idea if the intention is to prevent it being accessed.

"I don't think my EM (ASP.Net) will allow me ..."

Ah ... "EM", in my-speak, is Enterprise Manager which is one of the tools that comes with SQL Server 2000 - you "install" the server on your workstation and then choose to just! install "Client Tools" - is that what you are using? (I reckon it has to be given you managed to do the previous tasks, but just thought I'd check)

Do you have Query Analyser? If so try connecting to the DB with the same UserID/password as your application. Select a database from the dropdown list and type:

SELECT TOP 10 * FROM usd_Advertiser

that will tell you what you can see!

Only other thing I can think of is that the tables are created for a specific user - normally they would be created owned by "dbo", which all users can access.

If they are owned by, say, "FRED" then either you have to login as FRED or you have to access the tables as:

SELECT TOP 10 * FROM FRED.usd_Advertiser

Kristen
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2006-02-20 : 02:03:16
"Microsoft SQL Server 8.00.194"

That's SQL Server 2000 - however, it has NO Service Packs applied, and is susceptible to some viruses, so you should patch to SP3 or SP4 with reasonable urgency!

"ASP.Net Enterprise Manager"

I don't know about that, nor how it compares with the Windows-Application tool that MS provide. However, I do know that the MS one makes it very easy to connect to a local DB when other applications are having problems connecting, and I would know how to help you find the problem more easily using it. It comes with the Server software, but not MSDE, if you have a CD lying around for a server install then run that up and amongst the install options you can choose "Client tools" rather than a full server install.

"Yohz Query Analzyer Tool"

Again, I have been referring to the MS tool. So I don't know what capability your QA has. The MS tool will let you log in as a specific user (I'm sure they all allow that!) but that should let you identify is logging in as, say, SA allows you to see everything and logging in as your Application User prevents you seeing things. However, if you have connection problems in general I'm not confident that a 3rd party tool isn't going to reproduce the same problems that your application is seeing

"I've created an ODBC DSN for this db in Plesk and when I test the DNS connection, it says it connects successfully."

My reckoning is that you are connecting fine, but with insufficient permissions to see a bunch of the tables ...

"I can log into the db with QAT and see the table 'usd_bannerSettings' but I'm not seeing where the actual data is"

Does QAT just let you type SQL statements in?

What does
SELECT TOP 10 * FROM usd_bannerSettings
give you?

If error then try
SELECT TOP 10 * FROM dbo.usd_bannerSettings

But I suspect this is going to be hard to solve without the MS tools.

One other thought. There is a command line utility called OSQL.EXE - this may be provided with MSDE (I forget!), it usually lives in
C:\Program Files\Microsoft SQL Server\80\Tools\Binn

You could try running that from a command prompt. Just typing OSQL will give you the switches for User ID / Password / etc. and then you could try the commands above.

Once OSQL gives you a prompt type:

USE MyDatabaseName
GO

In fact I think you will need to type GO after every command (so that OSQL known when you have finished typing each multi-line command)

Kristen
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2006-02-26 : 03:57:54
Have you tried specifically connecting using your user "test" with your Management Studio Express tools? If you manage that you should try to see which tables you can access (as that user) to prove that the permissions are all A1-OK

Kristen
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2006-02-26 : 20:20:58
If your username contains non alphanumeric characters enclose it in [xxx]

SELECT TOP 10 * FROM [username].usd_User

Its possible that your table is owned by "dbo" thus:

SELECT TOP 10 * FROM dbo.usd_User

Kristen
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2006-02-26 : 21:46:31
Change the username to something that only contains alphanumeric characters? <g>

Seriously though system names which contain punctuation characters are always going to be a nuisance ...

Kristen
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2006-02-27 : 08:56:11
"I don't have punctuation marks or any other special characters in my username or password"

Then I don't understand why you could not use the name without putting {xxx} around it - although a leading digit probably would be a problem.

"I tried "Trusted_Connection=yes" in my connection string to no avail"

You need to decide whether you are using integrates security, and thus a trusted connection, or not - and if not then SQL Server must be set up to allow non-trusted logins - but I have no experience of SQL2k5 in this respect, it may be different.

Check that your connection string has all the appropriate information using
http://www.connectionstrings.com/

Kristen
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-03-02 : 11:45:08
quote:
Originally posted by xrdomains

Please delete. Your members are unable help. Thanks anyway!



Are you kidding me? How much time was spent trying to assist you, and you just delete all your posts and claim that no one was able to help you?

Weak. Very weak.
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2006-03-02 : 12:02:16
Obviously intending to never ask me, or probably anyone else here, for help again.

I don't suppose Google will keep the history for ever, and it isn't even complete - including only the first 2 days of this thread, and not the remaining 10!!, but it does give an idea of how ungrateful people can be.

"02/16/2006 : 20:50:40

Newbiw here. I need to move an existing MS SQL (2000) database from a hosting company to my new dedicated sever hosting. The only software I have available to me on the new dedicated hosting server is ASP.Net Enterprise Manager. I looked around on it and can't see any way to backup and restore, thinking I could backup from hosting company and restore to my new dedicated server. Can someone please shed some light on this task? It just doesn't seem like it should be this hard... Thanks in advance!

02/16/2006 : 20:57:53

Sorry, I do have MS SQL Server 2000 on the new dedicated hosting server and it is currently running. Does Enterprise Manager come with MS SQL Server 2000? Thanks for the ultra-quick response!

02/16/2006 : 21:15:26

Okay, now I'm questioning whether I do have MS SQL 2000. I don't see MS SQL 2000 in my progrmas list and when I run Services Management it shows Microsoft SQL Server 8.00.194. What does this mean?

02/16/2006 : 21:58:03

While I look up t-sql, can you please tell me how I can find out if I actually have MS SQL Server 2000? This is a Vertual Dedicated Server and I don't see anything on the company's Website about having MS SQL Server 2000 included with my VDH account.

02/16/2006 : 21:58:57

Another question, If I don't have it available to me, isn't this software like $500 (US)?

02/16/2006 : 22:11:45

Let me also add that I do not have access to my original "running" SQL database, as my previous host backed it up and I downloaded it. So I do have a backed up copy of the database that I need to load on the new VDH.

02/16/2006 : 22:18:07

Okay I found the t-sql script info. Since I already my mdf and ldf files, can I just use these t-sql scripts:

CREATE DATABASE MyDB ON
PRIMARY
(
NAME = 'MyDB_Data',
FILENAME = 'C:\Program Files\MSSQL\MyDB_Data.mdf',
SIZE = 50 MB,
FILEGROWTH = 10%

)
LOG ON
(
NAME = 'MyDB_Log',
FILENAME = 'C:\Program Files\MSSQL\MyDB_Log.ldf',
SIZE = 10 MB,
FILEGROWTH = 10%

)

Obviously if I upload my mdf and ldf file to the appropriate directories. Also, is there a particular place these should go? I know my mdf file is almost 500MB so what should I set the "SIZE =" to? And FILEGROWTH?

02/16/2006 : 22:52:13

Okay, I have a little problem first... In trying to figure out the ASP.Net EM I inadvertently created a database with the same name that I need to create and I see no way of deleting it with ASP.Net EM... Do you see something there that will allow me to delete the database that I inadvertently created? I really appreciate your help on this!

02/16/2006 : 23:47:23

I've tried Query Analyzer Tool and ASP.Net EM and both don't seem to be doing anything with the t-sql scripts. What is the best application to use for t-sql scripts?

Update: QAT seems to be working but it's saying cannot drop the database because it's currently in use. It shouldn't be though...

--------------------------------------------------------------------------------
Edited by - xrdomains on 02/16/2006 23:56:36

02/17/2006 : 15:36:15

Success! Well partly. I've got the database connected and I can see the tables and everything in QAT, but when I try to connect to it with hosting admin app (Plesk) it says "unable to connect to data source using the defined settings. Server has returned: ODBCError 28000" I'm trying to create an ODBC connection like with my other databases (currently 2 Access).

Okay more info: When I try to access the tables with ASP.Net Enterprise Manager it says:

Error: The object 'usd_User' does not exist in database 'MyDB'.
Source: .Net SqlClient Data Provider

I can see all the tables and they look correct...

--------------------------------------------------------------------------------
Edited by - xrdomains on 02/17/2006 15:43:05

02/17/2006 : 15:47:04

Okay, I think I may have it. I created a user and was able to log in... still working on it now... (This is so much fun! )

02/17/2006 : 20:09:07

Somehow I created a server the same name as the database that I was trying to install. Do you know where to remove the extra server?

Also, my application is causing a 500 Internal Server Error. Do I need to adjust the permission levels on the directory or mdf and ldf files that I placed in my Microsoft SQL Server directory?

I seem to be having a third problem, and this is weird. I can't get the index.asp file (that's what the creator of the app used) to load without giving me a 500 IS Error, but if I rename it to index.htm it loads. What does this tell me? I do have index.asp listed in my file types in IIS.

--------------------------------------------------------------------------------
Edited by - xrdomains on 02/17/2006 20:21:05


02/17/2006 : 21:51:02

Kristen, I love you! I've been trying to figure out how to get the 500 error to display for 3 days now!

My error reports as follows:

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E37)
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'usd_bannerSettings'.
/ads/Include/Constants_inc.asp, line 25

I checked line 25 in the particular file and it shows:

rsConstants.Open strConstantsSQL, DB_CONNECTION

Which I think is a problem with my database connection. I am using the following as my connection string:

Const DB_CONNECTION = "Driver=SQL Server;Server=[servername];Database=[databasename];UID=[userid];PWD=[password]"

I may be a little confused on what my server name should be. Is it what shows up in my MSSQL Services Manager as the SQL that is running?

As mentioned before, I created a "server" that I did not want to and don't know how to remove or delete it.

I'm going to get this going if I DIE trying!

02/17/2006 : 21:53:57
quote:
--------------------------------------------------------------------------------
Originally posted by Kristen

Some more info here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61966

--------------------------------------------------------------------------------

Thanks for the link. I can log in to my database with the username and password I created. Should I still run this command on it?

02/17/2006 : 23:01:41

I can log in to the db with QAT and see everything but my script is not connecting for some reason. Just so I can confirm the spec in the script, where or what exactly do I put for the server (as mentioned in my connection string, in a previous post)?

02/17/2006 : 23:27:53

Regarding permissions for the user that the script is using, I have the following set:

public
db_owner
db_datareader
db_datawriter

Is this correct?

02/17/2006 : 23:53:17

I just discovered a whole new page of permissions, uh oh...! How do I figure out what gets what?

02/17/2006 : 23:57:31

I set all the checkmarks to allow (green check), still nothing... I'm lost!
"
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-03-02 : 12:22:49
Wow ... I cannot believe how many questions were asked, and how much time you spent helping ..... I sure hope he never comes back here for help -- we'll need to immediately show anyone who spends any time helping him out this thread.

Go to Top of Page
  Previous Page&nsp;  Next Page

- Advertisement -