Where Do I See My SQL 2005 Databases?
Feb 1, 2008I recently upgraded from SQL Server 2000 to 2005 in our test environment. In 2000 I used Enterprise Manager to view my databases, users etc. but where can I see my databases in 2005?
View 2 Replies
ADVERTISEMENT
Service Broker Not Working For Restored Databases (SQL 2000 Databases Restored On 2005)
Jan 24, 2006I just restored my SQL server 2000 database on the SQL server 2005. after this i ran the Service broker sample ("Hello World") on this database by changing the AdventureWorks name to the new database name. The "setup.sql" runs fine. When i run the "SendMessage.sql" i was not getting any rows in the output (The message was not getting inserted into the queue). I checked the Service broker is enabled on this databased using the query "select is_broker_enabled from sys.databases where name = 'newdbname' " It was 1. I even tried the ALTER DATABASE SET ENABLE_BROKER. but it didnt work.
When i tried the sample on a newly created database it worked fine.
Is there any solution to make the restored database to work for service broker.
Thanks
Prashanth
SQL 2005 && VWD 2005 Express And Databases
Mar 16, 2007Help, don't quite understand, or more likely I've missed the point!
Using VWD 2005 Express, I can create, manage/modify and SQL 2005 database aswell as maintain any data I enter into it. Fine, no problem there. However, when it comes to doing the same kind of thing with the database using MSSQL 2005 Management Studio Express, I remain mystified!?
Any database I create above, through VWD 2005 Express, shows as being an attached DB in SQL MSExp and I'm able to detach and re-attach them. However, they are always attached as read only files so are unmodifiable!!!
So... What is it I'm missing? What do I have to do to be able detach, attach, re-attach such databases in a modifiable form, ie NOT read-only
Pulling my hair out over this one, so any advice gratefully rec'd
Thanks in anticipation.......!!!
User Can See All Databases SQL 2005
Mar 20, 2008I have a database user with rights on one database. If he connects with management studio 2005 he can see all databases.
How to change that he can only see database where he has rights on viewing and/or changing?
Henri
~~~~
There's no place like 127.0.0.1
Please Help Me SQL 2005 And Access Databases
Nov 16, 2006I have a restaurant POS application that is written in VB 6 and the data is being written to a access database. I need to access this database over the internet. I need to get certain tables (current sales) in this database to automatically send the updated data to a Web Server. My questions are: What is the best way to do this? Do I need to have sql express loaded on site and let it do the interactions with the Access server and then connect to SQL express over the internet? I am new to this and not sure of the best way to design it. Do I use Report services or analysis services to design?
Thanks
Using Two Databases With SSRS 2005
Aug 6, 2007Hi,
I want to use two databases for my reporting services 2005. I want to connect to one database and check whether the logged in user is a portal admin using portalid and userid.If yes, then I will allow the user to user use all the stores. Stores are displayed in the drop down list as the multiselect parameter. If no, then I will connect to another database and check the user rights. Based on the user rights I will select only the stores that are applicable the user. i don't know how to use two databases in the reporting services. Please give me an idea to do this.
Thanks,
Muniappan Kandasamy
2005 Local Databases
Nov 9, 2007I had both SQL server 2000 & 2005 on my machine. I recently un-installed 2000 for some reason, and now the SQL 2005 does not show local databases. It cannot update local server information or it doesnt let me local as a new server. How do i go about installing local databases in this situation?
Thanks
Upgrading Databases From SQL 7.0 To 2005
May 9, 2006I am upgrading from SQL 7.0 to 2005 and was wondering what the best method for doing this was. I tried simply backing the 7.0 database up to a file and restoring it back to the 2005 database. It seems to have worked because I can see all the database objects. The problem is that it also restored the users and I now have permission issues when accessing the data. I am not so shure it completely converted the database to SQL 2005. What is this compatability layer? Is there a prescribed method for upgrading the data, what's a good source for explaining how to do it.
View 1 Replies View RelatedMSSQL 2005: How To Hide Databases?
Mar 2, 2006For MSSQL 2005, how do you hide the databases so mssql db user will not see the database that he does not have access to when he logs in?
I can't seem to figure this out. I want to use this approach to increase the login time (to reduce loading speed on the database list).
Thank you Thank you...
Cannot Create Databases On SQL Express 2005
Aug 9, 2006Help me please!
I've installed SQL Express 2005 on my XP. I'm trying to create a database but I keep getting the error no matter what I do.
Msg 1802, Level 16, State 4, Server MYCOMPUTERSQLSERV, Line 1
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
Msg 5123, Level 16, State 1, Server MYCOMPUTERSQLSERV, Line 1
CREATE FILE encountered operating system error 5(error not found) while attempting to open or create the physical file 'C:Program FilesExos9300DatabaseExos93.mdf'.
Large Number Of Databases On 2005
Feb 23, 2007For anyone with a larger number of databases (500+): How many do you have in a single instance. If you are using multiple instance on a single server, how many dbs per instance. This is why I'm asking
We are experiencing 701 "out of system memory" and temporary (usually) system freezes when the error occurs. We have 32bit 2005 version 9.00.2153.00, 32GB of memory, AWE enabled, quad dual-core 3GHz hyperthreaded server. Nether the bPool or VAS show any pressure when the "out of system memory error" occurs. Since this error usually indicates a VAS problem we tried increasing VAS to 1GB w/the -g flag. It made no difference. PSS has been working on the case for 3 weeks. They dont seem to be finding any evidince of memory pressure either. When I last spole to the escalation engineer yesterday it seemed that they are going to recommend reducing the number of databases on the server. I asked for clarification as to whether we are hitting a 32 bit barrior, an instance limitation, or both. I am awaiting the answer. How many databases do you have on your server? We had between 1700 and 1900 (the number varies) at times when the error occured. We are now at 1500, and have not had the error in the 2 days since reducing the number of databases...
Transfer SQL 6.5 Databases To SQL 2005 Server
Jun 6, 2007I got 2 SQL servers. One is legacy SQL 6.5 and the other one is SQL 2005. They are installed in different machine. How can I copy or migrate the old 6.5 version databases to SQL 2005 server?
Someone said I should upgrade the 6.5 to version 2000 first. Is it the only method? I didn't have SQL 2000 install cdrom and license. How can I do that?
Please help.
Managing SQL 2000 Databases From SQL 2005 EM
Nov 7, 2007Hi,
I have just installed SQL 2005 client tools and management studio to register SQL 2000 databases. Although it registers the servers successfully , I can't see any activity monitor on the SQL 2000 registered server . Is it something more that I have to install to see what's happening ?
Thanks in advance.
Moving SQL Server 2005 Databases
May 19, 2007What is the easiest way to move SQL Server 2005 databases from one oldserver to a new server?
View 1 Replies View RelatedNumber Of Databases In Sql Server 2005
Sep 20, 2007Hello,If I have a database of 5 GB with only 20 tables in it, how many ofthose can we put in how many database servers on a high end machine(Dual Xeon 8 GB on windows 2003)?It is to maka a quotation and an estimation of the cost.TIAMario
View 1 Replies View RelatedHow To Link Two SQL Server-2005 Databases?
Jan 9, 2008HI,
I need to link two SQL-2005 server's databases. And both databases are on different machine and on same network.
After linking I have to create Views.
In other words :
I need to create a View of One -Database in to Second Database . For this Do I need to link first?
please let me know. ASAP.
Moving System Databases SQL 2005
Mar 16, 2007I am in the process of moving my system databases to another volume. I have accommplished the first section pertaining to master database.
I have reached step 3 in the Resource database move section down below.
It states to change the FILENAME path to match the new location of the master database. Do not change the name of the database or the file names.
ALTER DATABASE mssqlsystemresource
MODIFY FILE (NAME=data, FILENAME= 'new_path_of_mastermssqlsystemresource.mdf');
I changed the query to point ot the new location of the master, (E:MSSQLData)
ALTER DATABASE mssqlsystemresource
MODIFY FILE (NAME=data, FILENAME= E:MSSQLDatamssqlsystemresource.mdf');
I get the following error when I run the query:
Could not locate entry in sysdatabases for database mssqlsystemresource. No entry found with that name. Make sure that the name is entered correctly.
What am I doing wrong? My syntax must be incorrect. But I can't figure it. Anybody done this before.
These are the steps per msdn.
****************************************************************************************************************
To move the master database, follow these steps.
1. From the Start menu, point to All Programs, point to Microsoft SQL Server 2005, point to Configuration Tools, and then click SQL Server Configuration Manager.
2. In the SQL Server 2005 Services node, right-click the SQL Server (MSSQLSERVER) service and choose Properties.
3. In the SQL Server (MSSQLSERVER) Properties dialog box, click the Advanced tab.
4. Edit the startup parameters values to point to the planned location for the master database data and log files and click Apply. Moving the error log file is optional.
The parameter value for the data file must follow the -d parameter and the value for the log file must follow the -l parameter. The following example shows the parameter values for the default location of the master data and log files.
-dC:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAmaster.mdf;-eC:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLLOGERRORLOG;-lC:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAmastlog.ldf
If the planned relocation for the master data file is E:SQLData and the planned relocation for the log file is F:SQLLog, the parameter values would be changed as follows:
-dE:SQLDatamaster.mdf;-eC:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLLOGERRORLOG;-lF:SQLLogmastlog.ldf
5. Stop the MSSQLSERVER service.
6. Physically move the files to the new location.
7. Restart the MSSQLSERVER service.
8. Verify the file change.
SELECT name, physical_name, state_desc
FROM sys.master_files
WHERE database_id = DB_ID('master');
********************************************************************************************
To move the Resource database, follow these steps.
1. Stop the MSSQLSERVER service if it is started.
2. Start the service in minimal mode. To do this, at the command prompt, enter <SQLPath>innsqlservr -c -f -T3608 where <SQLPath> is the path for the instance of ssNoVersion. For example, C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQL. This will start the instance of ssNoVersion for master-only recovery.
3. Run these queries. Change the FILENAME path to match the new location of the master database. Do not change the name of the database or the file names.
ALTER DATABASE mssqlsystemresource MODIFY FILE (NAME=data, FILENAME= 'new_path_of_mastermssqlsystemresource.mdf');
ALTER DATABASE mssqlsystemresource MODIFY FILE (NAME=log, FILENAME= 'new_path_of_mastermssqlsystemresource.ldf');
4. Make sure the Resource database is set to read-only by running this query:
ALTER DATABASE mssqlsystemresource SET READ_ONLY;
5. Physically move the files to the new location.
6. Restart the MSSQLSERVER service.
No Available Databases In A New Query In SSMS 2005
Mar 7, 2007When I create a new query window in SSMS 2005 (SP2) , I do not have any available database in de SQL Editor pane. Though in the Object Explorer window below, I can access all objects of all databases. The only way to query a table is by using the 'Open Table' feature in the Object Explorer window, then right-click on Pane-->SQL .
I also can open a stored procedure, but not modify it as the SQL Editor pane is disabled.
The SQL-Editor pane is available for all the other servertypes (Analysis, Reporting, Integration).
All services all started.
I re-installed completely SQL2005 and SP2 : nothing changed.
What am I missing ???
Versions = Microsoft SQL Server Management Studio 9.00.3042.00
SQL Developer Edition 2005 (Service Pack 2)
Windows XP Pro 5.1 (build 2600 Service Pack 2)
Any help is welcome !!!
Moving Sql Server 2005 DataBases From C: To D:
Dec 7, 2007
How do you move databases from C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQL
to d:MSSQL.1MSSQL?
I want to copy them, then detach/retach at the new location
Thanks
Access 2005 Databases From 2000
Oct 31, 2007
I have upgraded 2000 databases to sql server 2005.If the users use just query analyzer & enterprise manager in 2000 to access 2005 databases.
will there be any issues???
let me know
SQL 2005 - Why Do Databases Suddenly Think They Are Replicated?
Nov 10, 2007
Hello All
I hope somebody can help. This is the second time I've seen this issue. The first time I thought it was a one off, but now I'm getting concerned.
I have two clients - one had Merge replication set up on a database that had a log log file that just grew and grew. The database was set to SIMPLE recovery. I could understand the log file growing if it was set up for transactional replication, but it wasn't transactional, just merge. Looking in sys.databasese the log_reuse column was set to REPLICATION.
I ran the sp_repldone stored proc on this database and all was well - I could shrink the log file down to a reasonable size. This server was SQL Server 2005, Service Pack 2.
Now I have yet another client who has a growing transaction log file. Low and behold the log_reuse for the database is set to REPLICATION again. This server has never had any type of replication set up at all!!!! The log file is still growing and DBCC OPENTRAN indicates undistributed transcations. This particular server is SQL Server 2005 Service Pack 1.
Can someone PLEASE shed some light on what's going on here? Is there some kind of bug that I don't know about? Searching Goolgle Groups seems to indicate that other people have had this problem, and they have have to set up replication and run sp_repldone to resolve it, but that's surely uncalled for on a database that has never seen replication?
Thanks
SQLGal2
Sql 2005 Databases That Are Not In A Maintenance Plan
Apr 16, 2008I need to build a query that shows which databases on a sql 2005 server are NOT included in a maintenance plan.
So far I have been unable to find a matching column from msdb sys.databases with any column in the following msdb tables:
sysmaintplan_plans
sysmaintplan_subplans
sysmaintplan_log
sysmaintplan_logdetail
Has anyone else been able to solve this challenge?
Thanks!
.40
Restoring Databases In Sql 2005 Express - Filename Changes?
Mar 22, 2008Hello,
Does anyone else ever experience this problem, and if so do you know what causes it:
When ever I restore a database called say XXX.mdf, it restores OK and I can view via MSE as XXX.mdf. Then when I visit MSE again there is another database called C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataXXX.mdf - both are linked to the same .mdf file, however if you try to expand XXX.mdf nothing happens as though the database has been detached?
Cheers MArco
Maximum Number Of Databases Used For Each Query In 2005?
Mar 22, 2006In MS Sql Server 2000, if you run a stored procedure that query's morethan 8 databases, you get this error gets raised:Maximum number of databases used for each query has been exceeded. Themaximum allowed is 8.In 2005, does this maximum number still exist? if so, is it still 8, orhas the number been increased?Thanks,Mike
View 2 Replies View RelatedCopy Databases From SQL 2005 Server To SQL Express
Nov 6, 2006Two Windows 2003 server,one with SQL 2005 server,another with SQL Express.Is it possible to copy databases from SQL 2005 to SQL Express?Thanks.
View 6 Replies View RelatedSQL Svr 2005 - Use Same Stored Proc && Views For 2 Databases?
Jan 10, 2007I am moving a fairly large app from Access 2003 to C#/SQL Server 2005.
The app has one front end mde, but it has two backend data mdbs, one is used in the US and one in the UK.
The two backend mdb's are identical in structure, but of course the data is different. There are 150 tables or so, and we plan to maintain separate databases after the upgrade.
Now, of course, all of the queries are in the front end, the backend db's contain only tables.
In the new world, I do not want to have to maintain the same views and stored procs in two different SQL Server databases.
Is there a way to have all views and stored procs in one database, but have them draw on the data in the two other databases. There must should be a way to deal with this scenario in SQL Server.
Many thanks
Mike Thomas
Move Log Files For SQL 2005 Databases To Another Drive
Jan 24, 2008
Hello,
I need to move all log files for my SQL 2005 databases to another drive. I don't wish to shrink the files, I need to move the logs to another drive spindle. I did find an article (Article ID: 224071) that describes moving both the database and logs using sp_detach and then sp_attach. What is the best way just to move the logs to another drive on the same server, and that keeps the databases in their original location?
Thanks.
Moving And Restoring Connections W/ SQL 2005 Databases
Dec 27, 2007
I have a question regarding moving databases within the same server but to a different drive. Using the 'Alter Database' query transaction procedures within the SQL Server Management Studio, I'm able to take a user database offline and move it. However, when using the Alter transaction to bring the database back online, I get errors. There was also an attempt that seemed to work, but the app that uses the database through ODBC can't see the database that was moved and errors out.
How can databases be moved and connections re-established within the same server?
Thanks...
About New SQL Server 2005 Samples And Sample Databases
Aug 10, 2006There are five msi files listed for downloading.
SqlServerSamples.msi
AdventureWorksDB.msi
AdventureWorksDBCI.msi
AdventureWorksBI.msi
AdventureWorksBICI.msi
What are the AdventureWorksDBCI.ms and AdventureWorksBICI.msi used for? I try to run it, but it return the message "Another version of this product is already installed" and ask me remove the old version of this product. Anybody know what I should remove?
Thanks,
Basic Printing From SQL Server 2005 Databases
Apr 17, 2008Hi,
Does Microsoft SQL Server 2005 Reporting Services come standard with SQL Server 2005?
If so, where can I download it.
Thanks
Creating Databases On WinXP SP2 With SQL Server 2005 CE RC
Nov 28, 2006Hi,
For various reasons a new desktop application I am developing is using the SQL Server 2005 Compact Edition Release Candidate to store local data. On my development machine (XP SP2, VS2005, SQL Server 2005 Developer Edition) the software is able to create the database file, and read and write to it fine.
However on our test machine, which does not have any of the development tools the software is unable to create the database. A System.Data.SqlServerCe.SqlCeException is thrown. The message is: "SQL Mobile usage is restricted on this platform. To use SQL Mobile, you must install SQL Server 2005, Visual Studio 2005, or the Tablet PC SKU." Obviously this goes somewhat against the blurb for SQL Server CE.
Is this something that will be fixed shortly? Or is it the intention that databases can't be created on machines that do not SQL Server or Visual Studio installed? (Incidentally the reason for creating the database on the fly is that we are encrypting it, using the users credentials, so that only they can open the database.)
Thanks, Steve
Creating Databases Under MS SQL 2005 Server (SP-2): Dbo User Role
Jun 9, 2007Good Day,
I am having a problem with creating databases in MS SQL 2005 Server. When I attempt to assign an User Mapping for my SQL user account to the newly created database, the "dbo" user and schema is already assigned. When I try to drop this user in favor of my own, I receive an error message: Cannot alter the user "dbo" (Microsoft SQL Server, Error: 15150). I am connected to my database engine through the "sa" account.
Regards,
Keith
Creating Sql Server 2005 Express Databases Via VS2005 Std.
Dec 5, 2007
This post is an extension onto the one below....
Please read my question at the bottom... cheers
---------------------------------------------------------------------------------------------------------------------------------------
Sept. 26th 2006
Hello all,
I use Visual Studio 2005 Standard. I would like to know the best way that I can create Sql Server 2005 Express databases visually since VS2005 Std. does not allow me to do it.
Any help is appreciated and thanks in advance,
- Noble Bell
I'd be interested in knowing how VS doesn't allow you to create a database. What error are you getting?
There are two ways to create databases, depending on your goal:
To just create a database on your server, do the following:
Open the Server Explorer
Right-click on Data Connections
Click Create New SQL Server Database
Specify Server Name and Database Name
Your database will be created and you can start working with it.
Embed a database in your project:
On the Project menu, click Add New Item.
Select SQL Database from the list and give it a name.
Click OK
This will run you thorugh a wizard to create the database.
If your having problems doing either of these, you may not have SQL Server installed on your computer or VS might be pointing to the wrong Instance Name. Check out the Option dialog under Database Tools:ata Connection and verify that the correct instance is specified.
- Mike Wachel - MSFT
---------------------------------------------------------------------------------------------------------------------------------------
I am trying to create a similar project and I also recieve an error while trying to create a database...
"An error has occured while establishing a connection to the server. When connecting to SQL Server 2005, this
failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
(provider: Named Pipes Provider, error: 40 - Could Not Open A Connection to SQL Server."
Now let me explain what i am trying to do ...
I am trying to create a web interface for the data that I want to store in the SQL database. I have installed VS2005 & SQL Server Express 2005 on my local machine. I want to develope this project on my local machine and then transfer it to a server once i have finished the project.
If i am doing this all wrong, please let me know !