Manage instances in another machine

I'm trying to manage another SQL Server 2005 instance in another machine. I'm doing this by connecting to another computer thru Computer Management. When I go the SQL Server 2005 Services, the right pane is showing There are no items to show in this view. How can I view the services?

Another problem that I have is that I have to turn off the Windows Firewall in the other machine. What exceptions are needed? I have tried by adding the specific TCP used by the instance.As far as I know the Computer management console can only adminsiter the local instances fpr Sql Server 2005.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Jens is right, you have to enable SQL Server remote access from the console of the server. Here's a couple relevant pages explaining how to enable remote access to SQL Server:

http://support.microsoft.com/default.aspx?scid=kb%3bEN-US%3b914277

http://www.aspcode.net/articles/l_en-US/t_default/Databases/SQL-Server/SQL-Server-2005-Expressremote-connection_article_123.aspx

Once you have remote access enabled, you can manage your server using SQL Server Management Studio from other machines.

Hope this helps,
Steve

|||

Hi Steven,

I'm following this MS article: http://msdn2.microsoft.com/en-US/library/ms190622.aspx

I can connect to the SQL Server Configuration Manager of the remote computer thru Computer Management. I can enable and disable protocols under both SQL Server 2005 Network Configuration and SQL Native Client Configuration. However, SQL Server 2005 Services will only show "There are no items to show in this view". It does not make sense to me since I can control the services thru Services but not thru SQL Server 2005 Services under SQL Server Configuration Manager.

I have disabled Windows Firewall and SQL Browser service is running.

Peter

|||If you login is a part of local administrator group on that server then using Computer Management console can do the job as it relies on the user privileges, as explained above for the SQL Configuration manager you can only manage local instances.sql

Manage detached mdf file

I want to manage my sql express .mdf database file (e.g. set Roles) but I seem to only be working with a temporary instance of my database when I attach to .sqlexpress and manage in SQL Server Management Studio Express (SSMSE). The Role I set is gone when I detach and re-attach.

If I try to manage my file in Visual Studio under Server Explorer, I do not see the "Roles" folder to modify roles.

Any help is appreciated.

Roles don't disappear once they've been created as they are part of the database where they are created. If you're trying to create Server Roles, those obviously stay with the Server instance, not with the database. This is true even for temporary instances. There are a couple things that are probably causing confusion.

If you created your database in VS, you are likely working with a User Instance. This is a temporary instance that is create at runtime of your application. You can manage a User Instance similar to any other Instance of SQL Server, but you can only connect to it using Named Pipes. You will have to query sys.db_os_child_instances (see BOL for more info on this) to determine what the pipe name is for the User Instances on your computer. You should also be aware that each User has a separate and independent User Instance; this is important if you're talking about Server Roles, which would have to be created on every User Instance separately after the User Instance is created the first time it is used. Database Roles will travel with your database, so those are a bit easier to manage.

You may also be confused by the fact that VS actually creates several copies of your database as part of the Build and Debug process. It is possible that you are creating the Roles in one copy, but then looking for them in another copy. Check out the FAQ topic on why data doesn't appear to be saved in VS for an explaination of what's happening with the copies of the database.

If you give us an idea of what types of Roles you're looking to manage, we'll have a better idea what to suggest.

Mike

|||

Ok, I'll take a look at the information here. The type of role I am trying to create is Application Role. In order to use Full-text index on my database I cannot use User Instances. Therefore, I need to have an Application Role for the the application to login.

Yes, I created the database in VS.

Ok, I've resolved this by creating the database in sql express. You were right about the different copies of the mdf file. I guess if I don't want to use User Instances (which I don't) then I need to create the db in sql express and update my connection string to use either windows authentication or sql authentication.

I am having login issues however...will create another post for that.

Thank you.

Making only certain data show in a cell

I am building a report and would like only certain data to show in a cell.
For instance I have 8 pay codes in my select statement, but would like to
show just 4 of them in the detail section. I don't want to limit my select
statement to just the 4 because I need the other 4 in another cell of this
report. Any ideas?
Its almost like I need a where clause within my particular cell.
Thanks,
RyanAlso, I thought about using multiple data sources and create a new table, but
is there a way to link two different data sources in a report? For instance
my report is on employee id and I would need to make sure that the data from
both data sources in the row detail are for the same employee id.
"Ryan Mcbee" wrote:
> I am building a report and would like only certain data to show in a cell.
> For instance I have 8 pay codes in my select statement, but would like to
> show just 4 of them in the detail section. I don't want to limit my select
> statement to just the 4 because I need the other 4 in another cell of this
> report. Any ideas?
> Its almost like I need a where clause within my particular cell.
> Thanks,
> Ryan|||Maybe you can use Filters.
In your data properties, select tab "Filters" and configure there the values
you want to show.
Does it help?
"Ryan Mcbee" <RyanMcbee@.discussions.microsoft.com> escribió en el mensaje
news:0F3503DD-A100-47C5-B4F3-B36EE6AF8BBE@.microsoft.com...
> Also, I thought about using multiple data sources and create a new table,
> but
> is there a way to link two different data sources in a report? For
> instance
> my report is on employee id and I would need to make sure that the data
> from
> both data sources in the row detail are for the same employee id.
> "Ryan Mcbee" wrote:
>> I am building a report and would like only certain data to show in a
>> cell.
>> For instance I have 8 pay codes in my select statement, but would like to
>> show just 4 of them in the detail section. I don't want to limit my
>> select
>> statement to just the 4 because I need the other 4 in another cell of
>> this
>> report. Any ideas?
>> Its almost like I need a where clause within my particular cell.
>> Thanks,
>> Ryan|||I will give that a shot. I built some case statements in my syntax to
seperate the data better.
"Mónica" wrote:
> Maybe you can use Filters.
> In your data properties, select tab "Filters" and configure there the values
> you want to show.
> Does it help?
>
> "Ryan Mcbee" <RyanMcbee@.discussions.microsoft.com> escribió en el mensaje
> news:0F3503DD-A100-47C5-B4F3-B36EE6AF8BBE@.microsoft.com...
> > Also, I thought about using multiple data sources and create a new table,
> > but
> > is there a way to link two different data sources in a report? For
> > instance
> > my report is on employee id and I would need to make sure that the data
> > from
> > both data sources in the row detail are for the same employee id.
> >
> > "Ryan Mcbee" wrote:
> >
> >> I am building a report and would like only certain data to show in a
> >> cell.
> >> For instance I have 8 pay codes in my select statement, but would like to
> >> show just 4 of them in the detail section. I don't want to limit my
> >> select
> >> statement to just the 4 because I need the other 4 in another cell of
> >> this
> >> report. Any ideas?
> >>
> >> Its almost like I need a where clause within my particular cell.
> >>
> >> Thanks,
> >> Ryan
>
>

Make RS the default website in RS 2005

When installing RS 2000 I did not have to do anything to have RS be the
default website for the server. For instance, I could put in http://myserver
and it would re-direct to http://myserver/Reports/Pages/Folder.aspx
I have gone into IIS Manager attempting to set the default appropriately but
I always end up with security violations and report manager does not come
up.
What are the steps I need to take to have RS 2005 to act the same way as RS
2000 as far as being the default website.
Thanks,
BruceBruce,
For the moment I am trying to keep my RS2005 RTM install as vanilla as
possible, so I haven't tried this.
Go to the RS Configuration tool.
Select the Report Manager Virtual Directory option in the left pane.
Click New.
Delete Reports from the Virtual Directory.
Click OK.
Click Apply.
I think that is likely to do what you want. As I say, I haven't tried
it.
Andrew Watt
MVP - InfoPath
On Mon, 31 Oct 2005 14:15:44 -0600, "Bruce L-C [MVP]"
<brucelc@.newsgroup.nospam> wrote:
>When installing RS 2000 I did not have to do anything to have RS be the
>default website for the server. For instance, I could put in http://myserver
>and it would re-direct to http://myserver/Reports/Pages/Folder.aspx
>I have gone into IIS Manager attempting to set the default appropriately but
>I always end up with security violations and report manager does not come
>up.
>What are the steps I need to take to have RS 2005 to act the same way as RS
>2000 as far as being the default website.
>Thanks,
>Bruce|||Nope. Nice try though. I tried this and first, you couldn't have it blank,
so I put / and then I could click OK and it failed and left it as it was.
Besides, what I want is the same way it works in 2000. If you go to just the
servername it redirects to reports. So you can either specify the full path
or just the server name. I think it will turn out to be a straight IIS
configuration issue.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Andrew Watt [MVP - InfoPath]" <SVGDeveloper@.aol.com> wrote in message
news:opjfm1d25ve92cef6322fc7jrbtcm0qfh2@.4ax.com...
> Bruce,
> For the moment I am trying to keep my RS2005 RTM install as vanilla as
> possible, so I haven't tried this.
> Go to the RS Configuration tool.
> Select the Report Manager Virtual Directory option in the left pane.
> Click New.
> Delete Reports from the Virtual Directory.
> Click OK.
> Click Apply.
> I think that is likely to do what you want. As I say, I haven't tried
> it.
> Andrew Watt
> MVP - InfoPath
> On Mon, 31 Oct 2005 14:15:44 -0600, "Bruce L-C [MVP]"
> <brucelc@.newsgroup.nospam> wrote:
>>When installing RS 2000 I did not have to do anything to have RS be the
>>default website for the server. For instance, I could put in
>>http://myserver
>>and it would re-direct to http://myserver/Reports/Pages/Folder.aspx
>>I have gone into IIS Manager attempting to set the default appropriately
>>but
>>I always end up with security violations and report manager does not come
>>up.
>>What are the steps I need to take to have RS 2005 to act the same way as
>>RS
>>2000 as far as being the default website.
>>Thanks,
>>Bruce
>|||If you have deleted your "default" website from IIS then you'll have to
modify the IIS meta database to make another website the "default". Works
the same way in 2000 and I was hoping they would do away with this CRAZY
requirement. As long as the web has the default IP address of the box....
Here are the steps sent to me from Microsoft Support (it's a recap of what
they had me do):
1. Backed up you IIS Metabase.
2. Downloaded the MetaEdit utility.
232068 HOW TO: Download, Install, and Remove the IIS MetaEdit 2.2 Utility
<http://support.microsoft.com/?id=232068>
3. Shutdown IIS and all its related services via â'iisreset /stopâ'.
4. Shutdown all MMC IIS related tools so nothing be accessing the metabase.
5. Verified that IIS was shutdown by browsing to a few of the sites it served.
6. Started MetaEdit. Expanded the outline control on the left side to locate
the
W3SVC entry and then located current default web site ID.
8. Changed it back to a 1.
9. Changed the AppRoot values on the right hand side for each of the VDIRS
under
the default web site.
10. Exited MetaEdit and restarted IIS.
In addition you can refer to the following article:
http://www.sqljunkies.com/HowTo/525B575A-7F61-483A-AC8F-FEC700C34674.scuk
"Bruce L-C [MVP]" wrote:
> Nope. Nice try though. I tried this and first, you couldn't have it blank,
> so I put / and then I could click OK and it failed and left it as it was.
> Besides, what I want is the same way it works in 2000. If you go to just the
> servername it redirects to reports. So you can either specify the full path
> or just the server name. I think it will turn out to be a straight IIS
> configuration issue.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Andrew Watt [MVP - InfoPath]" <SVGDeveloper@.aol.com> wrote in message
> news:opjfm1d25ve92cef6322fc7jrbtcm0qfh2@.4ax.com...
> > Bruce,
> >
> > For the moment I am trying to keep my RS2005 RTM install as vanilla as
> > possible, so I haven't tried this.
> >
> > Go to the RS Configuration tool.
> >
> > Select the Report Manager Virtual Directory option in the left pane.
> >
> > Click New.
> >
> > Delete Reports from the Virtual Directory.
> >
> > Click OK.
> >
> > Click Apply.
> >
> > I think that is likely to do what you want. As I say, I haven't tried
> > it.
> >
> > Andrew Watt
> > MVP - InfoPath
> >
> > On Mon, 31 Oct 2005 14:15:44 -0600, "Bruce L-C [MVP]"
> > <brucelc@.newsgroup.nospam> wrote:
> >
> >>When installing RS 2000 I did not have to do anything to have RS be the
> >>default website for the server. For instance, I could put in
> >>http://myserver
> >>and it would re-direct to http://myserver/Reports/Pages/Folder.aspx
> >>
> >>I have gone into IIS Manager attempting to set the default appropriately
> >>but
> >>I always end up with security violations and report manager does not come
> >>up.
> >>
> >>What are the steps I need to take to have RS 2005 to act the same way as
> >>RS
> >>2000 as far as being the default website.
> >>
> >>Thanks,
> >>
> >>Bruce
> >
>
>

Make duplicate db?

I am wanting to be able to take my work home with me, problem is I can access the server from there.

Is it possible to work with a local instance on my laptop, then when completed transfer it to another instance on a different pc?

Meaning, talbes,queries,etc.

Thanks

Davids Learning

You can create a backup of the database and restore it to another machine.|||

does that work with the express version?

or would I be better of using Access?

Davids Learning

|||That works very fine with SQL Express.|||

I need help with this.

How do you backup or restore to a different instance on a different machine?

I am new to SQL Server.

I can from my laptop see the server and access the files and do a backup(I believe the backup ran on the server and not on mine), but how do you get the db to my instance on my laptop?

Thanks

DavidsLearning

|||

Look in Books Online for the usage of RESTORE, and especially the WITH MOVE option.

If you don't have Books Online, download a copy here:

SQL Server 2005 Express Books Online Express Edition
http://msdn2.microsoft.com/en-us/library/ms165706.aspx

|||

I quess this is a Monday.

Is there a book for dummies?

Tried to apply what I read, and I still dont get how to assign a different instance.

Is there a way to make it simplier?

DavidsLearning

|||

You execute the RESTORE operation from the INSTANCE (after connection to that instance) where you want to install the database. (You don't 'assign an a different instance'.)

Some weeks , everyday seems like Monday...

Majority Node Set ?

Hello All
Has anyone created a clustered sql2000 instance on windows 2003 in different
geographical areas? I gather this can be done, but there is no shared
storage as such. I believe one has to replicate data another way.
I am looking for some docs on this before I dive in!
Thanks
Simone
Save yourself the trouble. SQL depends on shared storage for the data as
well as the cluster state. No Majority Node Set for SQL Server.
Geoff N, Hiten
Microsoft SQL Server MVP.
"Simone Harris" <pleasenospam@.here.com> wrote in message
news:%23eWcFPTYFHA.3712@.TK2MSFTNGP09.phx.gbl...
> Hello All
> Has anyone created a clustered sql2000 instance on windows 2003 in
> different geographical areas? I gather this can be done, but there is no
> shared storage as such. I believe one has to replicate data another way.
> I am looking for some docs on this before I dive in!
> Thanks
> Simone
>
|||Many thanks
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:efOOlvVYFHA.2664@.TK2MSFTNGP15.phx.gbl...
> Save yourself the trouble. SQL depends on shared storage for the data as
> well as the cluster state. No Majority Node Set for SQL Server.
> Geoff N, Hiten
> Microsoft SQL Server MVP.
> "Simone Harris" <pleasenospam@.here.com> wrote in message
> news:%23eWcFPTYFHA.3712@.TK2MSFTNGP09.phx.gbl...
>