Enterprise Manager for SQL 2005 Express

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

SQL Server 2005 Forums

Express Edition and Compact Edition (2005)

Enterprise Manager for SQL 2005 Express

Author

Topic

davidwhyte
Starting Member

13 Posts

Posted - 2007-03-18 : 09:48:51

Hi,

Ive received a .bak file of our company db from our hosting company and would like to import it into the SQL 2005 Express machine however, Ive never done this before using 2005. Previously with SQL 2000, I used the enterprise manager to view/import data into the database but Im unable to find this feature with 2005.

Can anyone help me?

Thanks

DW

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-18 : 11:08:53

Install SQL Server 2005 Express with Advanced.
Or was it Tools?

Anyway, one of the two option above has SSMS (new EM) and is installable.

Peter Larsson
Helsingborg, Sweden

mikewa
Microsoft SQL Server Product Team

84 Posts

Posted - 2007-03-19 : 11:38:49

Both Express Advanced and Express Toolkit have Management Studio Express, but you can also download it alone, which probably makes more sense here as you already have Express installed. You can get any or all three from http://msdn.microsoft.com/vstudio/express/sql/download.

Regards,
Mike Wachal
SQL Express

This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm

davidwhyte
Starting Member

13 Posts

Posted - 2007-03-20 : 07:17:33

Superb, thanks guys. The enterprise manager has cetainly come along some way since the last time I used it. Is this the tool I would restore the .bak DB file with?

DW

davidwhyte
Starting Member

13 Posts

Posted - 2007-03-20 : 12:51:09

Ok, got the database restored, next part of the problem is as follows. Ive created a userID under the security tab of the database. When i load crystal reports, I open data explorer and go to Make a new Connection in More Data Sources|OLE DB. On the provider screen, I select Microsoft OLD DB Provider SQL Server then click next. The next screen is Connections, I use the drop down for server name and my sql server is available in the list, I select it. I input the username and password which I created within the SSMS console. I then click on the drop down for "Select the database on the server" and I get Specified Server not found. After clicking on OK of the error message, I get "login failed. Catalog information cannot be retrieved.

DW

mikewa
Microsoft SQL Server Product Team

84 Posts

Posted - 2007-03-20 : 13:21:13

Hi David,

When you provide the login informaiton you need to specify a SQL Login, not the database user. SQL Security has two levels, the Login provides access to the server itself, while the User provides specific permissions for a database. Database users are associated with a specific login, which is how user authentication at the server level is passed down to the database level.

Please confirm that you are actually specifying a Login, not your database user. You will also need to ensure that the database user you created is associated with the Login you are using. (Note: Logins are found in the Security folder at the Server level, not under the individual databases.)

For a list of SQL Security topics check out http://msdn2.microsoft.com/en-us/library/ms187648.aspx
For a good diagram of the permissions hierarchy, check out http://msdn2.microsoft.com/en-us/library/ms191465.aspx

Regards,
Mike Wachal
SQL Express

This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm

readysetstop
Posting Yak Master

123 Posts

Posted - 2007-03-20 : 14:26:17

Also, if you restored the DB from SSMS, did you connect to the server with Shared Memory, TCP/IP, or named pipes? IIRC, Shared Mem is the default for Express, and you have to enable TCP/IP for apps other than SSMS to connect to it.

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2007-03-20 : 15:37:36

quote:
Originally posted by mikewa

Both Express Advanced and Express Toolkit have Management Studio Express, but you can also download it alone, which probably makes more sense here as you already have Express installed. You can get any or all three from http://msdn.microsoft.com/vstudio/express/sql/download.

Regards,
Mike Wachal
SQL Express

This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm

correct link
http://msdn.microsoft.com/vstudio/express/sql/download/default.aspx

davidwhyte
Starting Member

13 Posts

Posted - 2007-03-22 : 11:33:24

Ok, still not getting into the db via crystal. Im trying to create a new OLE connection to the server and Im using the sa account but im getting the following error message

Connection failed:
SQLState: '01000'
SQL Server Error: 10061
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionOpen(Connect()).
Connection failed:
SQL Server Error: 17
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]

Would i set up the ole connection via the control panel or from within Crystal (i would of thought it makes no difference).

Thanks
DW

DW

mikewa
Microsoft SQL Server Product Team

84 Posts

Posted - 2007-03-22 : 17:54:45

Per what ReadySetStop said, if you're using an MDAC based connection rather than SNAC, you need to enable TCP/IP. You can do this from the SQL Configuration Manager. MDAC goes through TCP/IP for OLEDB, where SNAC doesn't have to.

Regards,
Mike Wachal
SQL Express

This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm

davidwhyte
Starting Member

13 Posts

Posted - 2007-03-23 : 14:20:43

Ok guys, Ive managed to connect to the database using crystal. The problem was due to the sql server being part of the domain whilst the workstations are in a workgroup. When I removed the sql server from the domain, I was able to connect via the crystal data explorer. Silly mistake on my behalf but thank you all for helping me through getting the database tools to allow me to connect in the first place.

DW

davidwhyte
Starting Member

13 Posts

Posted - 2007-04-11 : 04:42:04

Guys, one more question. Now that I have the database install and Im able to access it from Crystal, I need to export all the tables to aid us when building the reports, can anyway suggest how I get the tables out of the database?

DW

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-11 : 04:48:52

See this topic
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=73608

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources