Hi,
Background: I'm on a Pentium 2.4 with 1024 mb Memory and 160 Gb harddrive, running XPPro with SP2.
I started a project in VS2005Beta2 with Sql2005Beta. When the RTM versions came along I ungraded to VS2005Pro, and tried to upgrade the SqlDeveloper. This ran amuck, but I could still access my database, so after three attempts I gave up temporarily.
When it looked as if I was actually going to finish my project, I decided to sort out my Sql. Still had the same problems, but found the Cleanup Utility, which did a great job of cleaning up the residual stuff in the registry. I downloaded SqlDeveloper, and ended up with file references to MSSQL.1 and 2 and 3 and 4. Unfortunately, my project couldnt locate my database, and this went on for days.
In desperation I removed the SqlDeveloper version, and downloaded the latest version of SQLExpress. Reluctantly thinking I'd rebuild my database as an .mdf file. No apparent troubles, but when I go back into my application, the Developer database has reappeared (.dbo) . really scary, and SqlExpress won't run.
I've wanted to try out Crystal Reports, and worked as far as needing the tables for the report, but when I look for the tables in my database (throughCR), all I get is a blank.
I'd really like to clean up the mess in Sql, but am scared to start, in case I once again can't find the database tables. I'd prefer to have SqlExpress operating.
Any ideas or comments would be welcome.
Tailor
SqlDeveloper/SqlExpress Conflict.
jpguest
Hi Mike,
I've emailed those snapshots you requested. Hope they help.
John
GregStroud
Hi Mike,
I'm sorry about the confusion surrounding my problem. Basically, it amounts to difficulty on my part in understanding what has happened with my system, and what is likely to happen in the future.
I know I have two versions of SQL Server on my machine. Sql2005 Developer and SqlExpr_ADV. but there is no real evidence that SqlExpr exists, other than a running instance showing up in SMS. This in spite of the fact that I couldnt access the Developer version until after I had loaded SqlExpr_ADV the last time. when somehow, the Sql2005 Developer suddenly reappeared, and seemed to be in control. The whole mess started because of not being able to upgrade Developer from Beta to RTM. in spite of following the instructions. After deleting remaining files through Add/Remove software I was stuck in no mans land, unable to do anything with my database, until after loading the lastest version of Express.
I can understand your confusion, but this being my first experience with MSSQL I am more so.
I started learning VS2005 and Sql2005, about 9 months ago, and have just about finished a fairly complex project, for our own internal use. Eventually it will be accessed by a couple of our personell over the net, so I will be moving the finished application to a Server running Small Business Server 2003. This was another reason why I decided to download SQLExpr. earlier than planned, when I couldnt get Developer functioning properly. I am just wanting to have a stable system, and really dont want to have to completely rebuild my database to do so. On the surface, the only way I can get any assurance would be to format this drive and start again. If I have to do that it would be helpful to know how tosave my database structure and data while I did so.
I have followed your request however got the following error message.
Msg 2812, Level 16, State 62, Server John-C70FBA2191\BRADVIEW. Line 1 could not find stored procedure 'select@@version'
John
MilwaukeeCharlie
Hi John,
As a member of the SQL Express team, I'm gratified to hear you want to stick with Express.
I recall that you have SQL Express Advanced installed, and before we start moving things around, you might want to wait for the final release as the version you have is a CTP. I expect the final release to come out very soon. The reason I suggest this is that you will have to unintal the Express Advanced CTP in order to move to the final release. It will be easier in the long run to just do all this once.
In the mean time, I'd like to sort out the multiple copies of your database so we really know which one is the right one. Start out by finding all the copies of the database that are on your computer. To do this, use the Windows search functionality. (Start menu | Search and then search for the file "bradview.mdf"). Search your entire hard drive and you should get a list of all the copies of the file. Post the list back here so we know what we're dealing with.
Next, I want to take a look at all the servers you have using Management Studio, just to see what's what. Here's what I'd like you to do:
This should start to give you an idea of what is on your computer. Feel free to look at the databases that you find, particularly the one named Bradview, which seems to be the one you are interested in. Remember, you have a Server named BRADVIEW and a database named Bradview. They are not the same thing Every database you see in this exploration is a completely different file and a completely different database. You can not share the same database between two servers.
Once you've finished exploring, you can re-connect the Object Explorer to the Server that you are connected to in Visual Studio. Based on your previous posts, that server is john-c70fba2191\BRADVIEW, so that's the one I'd stick with.
On final thing, I'd like you to get some stats on the BRADVIEW server, run the following at a command prompt:
(Don't forget the space between 'select' and '@@Version'
) This will tell me where this particular copy of SQL Server came from.
Good luck in your exploration. Let me know if any discoveries are made. Once we get down to a single copy of your database and understand what server we're working with, we can talk about deleting the extra copies and consider uninstalling unneeded servers.
Regards,
Mike Wachal
SQL Express team
----
Check out my tips for getting your answer faster and how to ask a good question: http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=307712&SiteID=1
mattman206
Hi Tailor,
Could you please be more specific in your problem Like, as i see there are 3 issues, 1st you were able to connect to Database, 2nd your project couldnt locate your database and 3rd developer database reappeared.So, we actually are unable to figure out the problem. If you have link to error messages you are getting, please add them in the post or any more detailed error messages can allow us resolve your problem.
Thank you,
Bhanu.
antho
Hi John,
It's still not clear exactly what problem you're trying to solve. What exactly is it that you're trying to do and what is not working
I'd also like you to do some basic troubleshooting and respond with the results just so we can establish what you have installed:
1. On the same computer where you have SQL Express installed, open a command windows and run the following command:
This will tell us what databases you have attached to your instance of SQL Express. Please respond with this list. At this point you should be left at a prompt that looks like this "1>" so I want you to type the following:
Again, let us know what you get back from this to confirm which version of SQL Express you're using. You can type "exit" at this point and then "exit" again to close the command window.
Regards,
Mike Wachal
SQL Express team
----
Check out my tips for getting your answer faster and how to ask a good question: http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=307712&SiteID=1
Henry Chan
Hi John,
Looks like you missed a space between "select" and "@@version", hence the error you see.
I doubt you'll need to format you're computer; the fact that you got an error indicates that things are running, we just need to do a bit of a primer on how SQL works to get you up and running. I'd still like you to check the version just to be sure that you're running the latest and greatest.
From the error message, it looks like your copy of SQL Express is running under the instance name BRADVIEW, is that correct Just want to make sure.
As far as figuring out what happened to your database, the easiest thing to do is to just search for it in Windows. Go to Start | Search | All files and folders and search your hard driver for "*.mdf". This will return every SQL database file on your hard drive. Hopefully one of the files returned will be the database you're looking for. (Upgrading or uninstalling SQL Server does not delete user databases, but if you deleted the SQL directories where the database was stored during your troubleshooting, that would obviously have deleted it.)
Once you've found the MDF file, you can easily attach the database to your SQL Express instance using SQL Management Studio that came as part of SQL Developer. Just do the following:
At this point, you should be able to work with the database in Management Studio as you would any other database. You should also be able to connect to the database from VS as well.
It is normal that SQL Express doesn't give any indication of being present other than showing up in a list of services. SQL Express only installs the database engine service, there is no UI other than opening a configuration tool such as SQL Configuration Manager. In SQL Express, we added Management Studio Express for a GUI managment tool. VS also provides some UI for working with SQL Server and database via the Server Explorer or Database Explorer, depending on which Edition of VS you're using.
Let me know how things go.
Regards,
Mike Wachal
SQL Express team
----
Check out my tips for getting your answer faster and how to ask a good question: http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=307712&SiteID=1
Zamial
Hi John,
Here's what I can tell from your description:
The Server Explorer in VS constructs the name under Data Connection as follows:
Which mean in VS you're using:
Sever: BRADEVIEW
Database: bradview
I'm not sure what version of SQL your connected to, but you can check by right-clicking the Data Connection in Server Explorer and clicking Properties. Look in the Properties pane in VS and you should see a Version property. If it is 09.00.1399, this is the original release, if it is 09.00.2040, then it's the CTP release.
In management studio, you are connected to a different server. The fact that it just lists your machine name indicates you're connected to the "default instances" which in this case is the server you installed with SQL Developer Edtion. The information listed shows this to be the original release as indicated by the version number of 1399.
Both of these servers have a database named Bradview, but they are two different databases and the information in them resides in two different files named bradview.mdf. It looks like you have multiple copies of your database file made at different times. These files are in different locations, but I can't determine where based on the information provided.
One of your previous messages indicated that you also had a third instance of SQL Server installed named SQLEXPRESS; at this time, that instance is not being used by either of the tools you're working with.
You mentioned that there is only one copy of SQL 2005 listed in Add/Remove programs; this is normal. That entry actually represents all the copies of SQL 2005 installed and you have to run the SQL maintenance mode from that entry to get to the point where you can install or uninstall individual named instances. Before you start removing instances of SQL Server, you need to work out which one you actually want to use and get your tools pointing at that copy.
Let me know which of the three you really want to use and then we'll sort out connecting both VS and Management Studio to that Instance, and if necesary, transfering the correct copy of your database to that Instance.
Regards,
Mike Wachal
SQL Express team
----
Check out my tips for getting your answer faster and how to ask a good question: http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=307712&SiteID=1
Kyaw N Paing
Hi Mike,
Sorry about the missing space. My old eyes arent the best. I've revisited the dot prompt with the following result.
Microsoft SQL Server 2005-9.00.2040.00 <intel X86>
Mar 13 2006 11.20.51
Copyright <c> 1988-2005 Microsoft Corporation
Express Edition with Advanced Services on Windows NT 5.1 <Build 2600: Service Pack 2>
I've also followed your suggestion to do a search on .mdf and come up with a list of 62 files, with 4 being Master Database Primary Data Files. Two dated 4/4/06, one dated 17 Mar 06 and the other 22 Mar 06. Maybe useless information for you, but they probably result from my number of attempts to straighten out my system.
Using Server management Studio, I cannot connect to the instance named BRADVIEW, but I have no trouble connecting to that instance through VS IDE, however in the VS IDE I cannot connect Crystal Reports to the database.
This may help. Looking in SQL Configuration Manager I can see three instances.
SQLServer(BRADVIEW) Stopped Log on as NT Authority\Local Service
SQLServer(SQLExpress) Running Log on as NT Authority\NetworkService
SQLServer(MSSQLSERVER) Running Log on as Local System.
Looking at that I imagine logging on SQLExpress as Network Service, is why I cant locate it on my Local machine, but doesnt explain (at least to me), why I cannot start the instance BRADVIEW in Management Studio, but it starts in VS.
At present I am happy to leave things at the status quo, provided I dont get any more surprises, as I can develop, and deploy to my local machine without any problems. However I do need to know what to do with my database, when I move it to the Server.
I guess at 64 I'm somewhat old to be doing a crash course in VS, SQL and CR, plus digging into the bowels of XP Pro. and SBServer 2003, So I much appreciate your assistance. Most young programmers think I'm either insane or senile I guess.
John
Mike, since writing the above, I've managed to find out a little more which may help. I finally got some sense out of Server Management Studio, and found two copies of my database. One is the BRADVIEW.mdf file, which mostly seems to elude me, the other is SQL Server version 9.0.1399, which I presume is the developer version.
The .mdf version is fairly out of date, missing recent tables and data. My untrained mind has come to the conclusion, that the ,mdf file replaced the original Developer Beta version, then somehow, the RTM developer has copied the .mdf file, and continued on from there. How this has happened when the latest Sql version I've installed on this machine, was SQLExpress Advanced services, has me completely puzzled.
This is the point I've been trying to make for some time.
Therefore if I happen to connect to the .mdf file, the information therein will not be up to date.
Hope this is a little more helpful
John
Sealed
Hey John,
Go ahead and e-mail me the snapshot. My e-mail address is in my profile (click on my name to the left of this message), just remove the work "online" which I put in as a spam blocker.
While you're at it, grab a screen shot of the SQL Configuration Manager opened to the SQL Server 2005 Services node.
- Mike
smgraham
Hi John,
I think it's great that you're doing a crash cource in SQL and VS, no matter what you're age. I think there are a couple things that we should get straightened out even if you're happy with the status quo. Without them, it likely that things could get mixed up again down the road.
One thing I want to touch on is that you have three copies of SQL Server on your computer. I'm not sure you need all of them, but you've got them.
BRADVIEW - Not sure what this one is. Since it is not running, you are not actually connecting to it with any of your tools.
SQLExpress - This is likely the one you used when you checked @@version and you installed this from the Express Advanced CTP. (The version 9.00.2040 is the CTP version.) This is most likely the copy of SQL that you are connecting to when you use VS.
MSSQLSERVER - This is what is called the "default instance" of SQL Server and is likely what you installed when you installed SQL Developer. I'd wager that when you open SSMS, you are actually connecting to this copy of SQL.
Past that, you should also know that bradview.mdf is a database file not a copy of SQL Server. This file is totally different than the named instance of SQL listed above. The way of the world is that you attach a database file (.mdf) and it's log file (.ldf) to an instance of SQL Server. It is just a coincidence that your database file has the same name as one of the instances of SQL Server, there is no intrinsic connection between them.
Finally, you can only attach a database file to a single instance of SQL Server at a time. Once you've attached a database to a server, it will fail to attach to another server.
When you put all these facts together, I think I can explain the behavior you're seeing as follows:
You say "I cannot start the BRADVIEW instance in Management Studio, but it starts in VS."
You're not actually starting the BRADVIEW instance in VS, you're attaching the bradview.mdf file to the SQLExpress instance, which VS uses by default. When you later try to attach the bradview.mdf file in Mangement Studio, it fails because you are likely now working in MSSQLSERVER and the database is already attached to SQLExpress.
My main point here is that the .mdf file is not SQL Server, which I think might be a point of confusion here. I think you need to do two things to get on the straight an narrow:
Let me know if this makes sense so far, and then we can move onto service accounts (that's the the "Log on as NT Authority\Network Service" part), what they mean and how they affect what SQL Server is doing.
Regards,
Mike Wachal
SQL Express team
----
Check out my tips for getting your answer faster and how to ask a good question: http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=307712&SiteID=1
nkoranda
Hi Bhanu,
Thank you for your reply. I'm only slowly learning to deal with error log files, and at the time wasnt smart enough to keep a copy of the error messages, however would be pleased to send you anything that would help resolve my situation.
If the Setup Log files would be any use, please let me know which ones to look for, and I'll send them to you. In MSSQL\90\Setup Bootstrap\Log\Files I have 26 different files, would these have any useful information for you. Otherwise let me know what you need and I'll do my best.
My main worry is that I intend to set up a Server, using SqlExpr_Adv, and I dont want to end up with a database I cannot access. At present I am just doing my development with everything on this local machine, and after the past experiences am afraid to try to make changes until I know what I'm doing.
Regards,
John
lumpynose
Hi Mike,
Thank you for the detailed explanation. It certainly helps me to understand what is going on under the hood.
I agree with your comments, that I should sort this out as soon as possible, and I have been worried that it could all suddenly go haywire, always late at night, and when its most inconvenient.
I hate to take up so much of your time, but if you can bear with me, I'll try to give you more detail as I see it at my end.
When I open up Server Explorer in VS. I get the following info
Data Connetcions: john-c70fba2191\bradview.BRADVIEW.dbo
Server: john-c70fba2191
Now in SMS Object Explorer we find the following. I've just listed the essentials.
Connect to:
JOHN-C70FBA2191(SQL Server 9.0.1399-JOHN C70FBA2191\JOHN)
Databases
Bradview
Tables
C:\MICROSOFT SQL Server\MSSQL.1\Data\Bradview.mdf
Tables.
Both Tables contain similar structures and data, however, the .mdf one is not as up to date as the other. I'd like to delete it, but would like your opinion first.
I suppose the mess comes back to my trying to do as you suggest, and only have one instance of Sql Server on my machine. Where I though and hoped I got rid of earlier versions, I was obviously wrong. I did originally buy copies of VS2003 and Sql2000 Developer, but when I first saw the Beta versions of 2005 in both, I thought they were so much further advaced, and easier to use, I opted to work with them. I still dont think it was a mistake, so long as I can clean up the mess I'm in.
Control Panel\Add Remove programs, only shows the one instance of Sql 2005. Prior to downloading SqlExpress, I'd deleted any Sql files there, and also used the CleanUp Utility to clean out the registry, so I thought I was OK when SqlExpress installed without any trouble.
I hope you will be able to sort out where I am, but if you need any further information, I'd be pleased to try to find it for you.
Thank you so much for your time and patience, its much appreciated.
John
Matthew McDonald
Hi Mike,
Congratulations on a job well done with SqlExpress. I'll be looking forward to the Release version of SQLExp Advanced, and this time I am expecting to be able to cope with the upgrade. Hopefully there will not be residual references left hanging about in the Registry, but the Clean Utility should take care of that I guess. Wish I had known of that when I tried to upgrade the Developer Version. The good thing about having problems is the learning which comes with overcoming them.
Now to fix the problem at hand. I've done the search as suggested, and found the following two references to Bradview.mdf.
C:\Microsoft SQL Server\MSSQL.1\Data\Bradview.mdf
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Bradview.mdf.
This appears to be the same file as they are both shown as SQL Server Database Primary File. Modified on 15/04/2006 at the same time.
Followed you next instructions:
1. Object Explorer had no Servers listed.
2. Registered Servers Window is open showing
Reporting Services
john-c702191
3. I added the other server names
4. Double clicked on each server and got error messages. John-c70fba2191 and SQLExpress both showed the message "Client found response content type of 'text/html: charset = UTC-8' but expected 'text/xml' "Request failed.
.\BRADVIEW error message reads "Exception has been thrown by the target of an invocation (mscorlib).ADDITIONAL INFORMATION The report Services instance could not be found.
5. I exited Management Studio, and reopened it. Clicked on Connect and this chowed the following data in Object Explorer.
JOHN-C70FBD2191(SQL Server 9.0.1399-JOHN-C70FBA2191\JOHN
Databases
Bradview
Tables (Contains 47 tables) This contains the most recent data.
C;\Microsoft SQL Server\MSSQL.1\Data\BRADVIEW.mdf
Tables (Contains 42 tables with the same names but less data) This ceased to be updated some time ago.
I also exited and reentered Management Studio a couple more times and tried to connect to .\BRADVIEW AND .\SQLEXPRESS but neither would open in Object Explorer. Just got an error message that they couldnt connect.
I then ran the SQLCmd -E -S .\BRADVIEW -Q "Select @@Version" as requested. The result being. HResult 0XFFFFFFFF, Level 16, State 1. Error locating Server/Instance specified.
I did the same with .\SQLEXPRESS and got the same message.
I tried with just .\ and found the following information.
Microsoft SQL Server 2005 - 9.00.1399.06 <intel X86> Oct 14 2005 00:33:37 Copyright <c> 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.1 <Build 2600 Service Pack 2>
This would then be the instance which VS is using. What is really confusing to me, is that when I tried to use Crystal Reports, it couldnt find a database. I think it was looking for one of the other insatnces, and was, like me, totally confused.
The reference to Reporting Services would have come from my desperate attempts to find some way of getting reports out. I had a look at Reporting Services, and also ran into similar problems there of accessing data, but that could be just me.
Hopefully the details above will enlighten you, and we will be able to get back to one instance of SQLExpress, and one database. I'm really glad I didn't have half a dozen different projects with different databases.
Again, my thanks for your patience.
By the way, I captured a screen shot of Object Explorer which may be more enlightening to you, if you need it. However I am not sure if I can attach the file to this. If there is a way, or I can email it to you. Let me know how, and I'd be happy to do so.
John
Stathread01
Hi Mike,
Sorry for any delay in replying. The local Telco Exchange had some problems, which left me without internet access for nearly two days. Funny how everything grinds to a complete halt.
I think using SqlExpress is the best way for me to go. I intend to have my data on a server, eventually, and have a few pC's accessing it, so that is really not permitted with Developer I believe. Also, in view of my being less than a professional, I would think Express has enough features for my usage.
I'd certainly appreciate your advice on how to clean up my mess, and I'll look forward to your reply.
Have a Happy Easter.!
John