Environment Variables Within SQL

Is there some way I can access system environment variables within a SQL script? For example, if I have a script "foo.sql" that I'm calling from isql, I want to be able to substitute <hostname> with an env variable I set at the command line:

foo.sql:

update tblFoo set HostName = <hostname>

Thanks.

TerenceQ1 Is there some way I can access system environment variables within a SQL script?

A1 Yes.

Note: The following is in regard to osql (however it should hold true for isql as well)

If issuing queries from osql, (also should work using batch files), one may use environment variables i.e.( %variablename% ) directly. For example:

Define the following two environment variables:
Set TargetDB = Pubs
Set TargetTable = Authors

Then run the following example (replace SqlServer with your SqlServer instance name before running) from the command prompt:

Example:

osql SSqlServer -E -dPubs -q"exit(Select Au_LName from %TargetDB%..%TargetTable% Order By Au_LName Go Select Count(*) As 'AuthorsCount' From %TargetTable%)"|||Note that DBA's suggestion works because all the variables have been substituted PRIOR to running osql.exe. This method won't work with a script. To my knowledge there is no way to reference environment variables from SQL server.|||RE:
Note that DBA's suggestion works because all the variables have been substituted PRIOR to running osql.exe. This method won't work with a script. To my knowledge there is no way to reference environment variables from SQL server.

A good point. If the simple approach demonstrated is unworkable for the requirements at hand; another approach to consider may be to create one or more stored procedures which may be executed such that the desired results may be achieved indirectly.

For example, several utility procs may be created which shell out to the OS and execute OS commands directly or that call short VB scripts to gather, set, and / or otherwise manipulate the environment variables as required.

Enumerate SQL servers?

I'm working on an Access app that will have a SQL 2K backend. My setup will
install MSDE, and I will set the sa password, etc. which I don't want the
end user's to know. My problem is in Relinking the ODBC tables once it is
installed in the field. The one item I won't know is the name of the server
MSDE is installed on. I want to prompt the user to pick the server from a
list, much like you do when creating a DSN through the ODBC Administrator. I
can't use the ODBC Administrator to create a DSN because if the user doesn't
enter the sa password, it fails; also, I want to use DSN-less connections.
But I can't seem to find an API call to enumerate the SQL servers available
on the network. Does anyone know of an API to call? Alternatively, some code
to enumerate SQL servers running on the LAN? TIA!Can also look at :
http://www.extremeexperts.com/sql/f...istServers.aspx
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
"Ron Hinds" <__NoSpam@.__NoSpamramac.com> wrote in message
news:%23rKHj4H3DHA.1392@.TK2MSFTNGP11.phx.gbl...

quote:

---

> I'm working on an Access app that will have a SQL 2K backend. My setup

---

will

quote:

---

> install MSDE, and I will set the sa password, etc. which I don't want the
> end user's to know. My problem is in Relinking the ODBC tables once it is
> installed in the field. The one item I won't know is the name of the

---

server

quote:

---

> MSDE is installed on. I want to prompt the user to pick the server from a
> list, much like you do when creating a DSN through the ODBC Administrator.

---

I

quote:

---

> can't use the ODBC Administrator to create a DSN because if the user

---

doesn't

quote:

---

> enter the sa password, it fails; also, I want to use DSN-less connections.
> But I can't seem to find an API call to enumerate the SQL servers

---

available

quote:

---

> on the network. Does anyone know of an API to call? Alternatively, some

---

code

quote:

---

> to enumerate SQL servers running on the LAN? TIA!
>

---

|||Hi Vinodk,
I can list SQL server instances on my personal computer fairly easily using
SQLBrowseConnect and associated methods but I have trouble trying to
enumerate all SQL servers on the network if I am not connected to a browser
service. Obviously, I don't expect to get a list of servers if I am not
connected but I can't seem to get a *faster* way to check if I am connected
to the browser service.
I use something like the folowing to see if any master browsers are
available in hopes for speed bit it is very slow. If the status return
ERROR_NO_BROWSER_SERVERS_FOUND, I don't want to go further.
nStatus = NetServerEnum(NULL,
(DWORD)100,
(LPBYTE *) &pBufServerBrowser,
dwPrefMaxLen,
&dwEntriesRead,
&dwTotalEntries,
SV_TYPE_DOMAIN_MASTER |
SV_TYPE_POTENTIAL_BROWSER |
SV_TYPE_BACKUP_BROWSER |
SV_TYPE_MASTER_BROWSER,
NULL,
&dwResumeHandle);
Any thoughts on this'
Thanks in advance for any help...
Regards,
Chris H
"Vinodk" <vinodk_sct@.NO_SPAM_hotmail.com> wrote in message
news:eXEf9iL3DHA.2296@.TK2MSFTNGP11.phx.gbl...
> Can also look at :
> http://www.extremeexperts.com/sql/f...istServers.aspx
> --
> HTH,
> Vinod Kumar
> MCSE, DBA, MCAD, MCSD
> http://www.extremeexperts.com
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp
>
> "Ron Hinds" <__NoSpam@.__NoSpamramac.com> wrote in message
> news:%23rKHj4H3DHA.1392@.TK2MSFTNGP11.phx.gbl...
> will
the
is
> server
a
Administrator.
> I
> doesn't
connections.
> available
> code
>

EnterpriseLibrary 2006 DATA ACCESS LAYER

in the class library i written the code name :customer is the lib name

using System;

using System.Collections.Generic;

using System.Text;

namespace Customer

{ class Entites

{

public int inTest;

public int inTest2;

}

}

Now in the Class1.cs i written the code

i am getting the data from the database by using enterprise lib 2006 connection function

now HOW TO BIND THE DATA TO LIST AND RETURN TYPE IS LIST

PLEASE CHECK THE CODE AND REDEFINE THE CODE

using System;

using System.Data ;

using System.Collections.Generic;

using System.Collections.Generic;

using System.Text;

using Microsoft.Practices.EnterpriseLibrary.Data;

using Microsoft.Practices.EnterpriseLibrary.ExceptionHandling;

using Microsoft.Practices.EnterpriseLibrary.Common.Configuration;

using System.Collections;

using System.Xml.Serialization;

using System.Data.Common;

using Customer;

namespace Customer

{

class Class1

{

public List<Entites> getdata(int id)

{

Database db = DatabaseFactory.CreateDatabase("mycon");

System.Data.Common.DbCommand cmd ;

cmd = db.GetStoredProcCommand("GET_CUSTOMER");

cmd.CommandType = CommandType.StoredProcedure;

db.AddInParameter(cmd,"@.CID",System.Data.DbType.Int32,id);

List<Entites> objEntites = new List<Entites>();

using (IDataReader dr = db.ExecuteReader(cmd))

foreach (Entites obj in dr)

{

objEntites.inTest = obj.inTest;--ERROR LINE

// objEntites.Add(obj);

}

return objEntites;

}

}

}

Error 2 foreach statement cannot operate on variables of type 'System.Data.IDataReader' because 'System.Data.IDataReader' does not contain a public definition for 'GetEnumerator' D:\KOTI_PRJS\Enterprise\Customer\Class1.cs 34 13 Customer

Is this related to SSIS? If not, let me know and I'll be happy to move it to a more appropriate forum. If it is, please describe where the problem is occurring in SSIS.

Enterprise Manager User Log-in DB Visability

I have created new login in my Enterprise Manager. The login is not part of any roles and only allowed access to 1 database (of 200). However, they can see all the databases in Enterprise Manager.

They can't do anything with them, but I don't even want them to see them.

Have I set something up wrong?Hi ,

I posted earlier the same questune , i think that its not possible for users wont be bale to see the dbs , but i couldnt prevent also access to the master db objects that is realy bad thing .|||There must be something that can be done! How would an ISP that wants to host SQL Server for people deal with it?

Enterprise Manager Shortcuts for Remote Servers

Hello and thank you in advance.
I have multiple databases on a single remote MSSQL server (from my ISP). I
can only access them via Enterprise Manager. My quandary is that every time
I log in, I have to click the server name, then wait for the database to
load, then sift through this enormous list to find the database I am seeking.
I'd like to have shortcuts to each specific database so I can connect and
get to the tables with one click.
Anyone know how to do this?
Thanks,
JulianHi Julian,
Do you have restriction for using Query Analyzer also? If not, while
configuring the client ODBC, you can specify the default database you wish to
connect to.
--
Thanks
Yogish|||Thank you for your reply. I don't think I have a restriction for Query
Analyzer, but I am a novice at best and am only barely qualified to use
Enterprise Manager.
Does this mean I'd have to switch to Query Analyzer or are you suggesting
that I can use Query Analyzer to store my default database and somehow tie
that back to using Enterprise Manager? It puzzles me why Enterprise Manager
doesn't allow you to store a default database for each SQL Server
Registration.
"Yogish" wrote:
> Hi Julian,
> Do you have restriction for using Query Analyzer also? If not, while
> configuring the client ODBC, you can specify the default database you wish to
> connect to.
> --
> Thanks
> Yogish|||Hi Julian,
You can do lot of operations with Query Analyzer (QA). If you are using SQL
Server 2000 client, you can use Object Browser to access all the database
objects, provided you have the permission.
Check out Books online.
--
Thanks
Yogish

Enterprise Manager Shortcuts for Remote Servers

Hello and thank you in advance.
I have multiple databases on a single remote MSSQL server (from my ISP). I
can only access them via Enterprise Manager. My quandary is that every time
I log in, I have to click the server name, then wait for the database to
load, then sift through this enormous list to find the database I am seeking.
I'd like to have shortcuts to each specific database so I can connect and
get to the tables with one click.
Anyone know how to do this?
Thanks,
Julian
Hi Julian,
Do you have restriction for using Query Analyzer also? If not, while
configuring the client ODBC, you can specify the default database you wish to
connect to.
Thanks
Yogish
|||Thank you for your reply. I don't think I have a restriction for Query
Analyzer, but I am a novice at best and am only barely qualified to use
Enterprise Manager.
Does this mean I'd have to switch to Query Analyzer or are you suggesting
that I can use Query Analyzer to store my default database and somehow tie
that back to using Enterprise Manager? It puzzles me why Enterprise Manager
doesn't allow you to store a default database for each SQL Server
Registration.
"Yogish" wrote:

> Hi Julian,
> Do you have restriction for using Query Analyzer also? If not, while
> configuring the client ODBC, you can specify the default database you wish to
> connect to.
> --
> Thanks
> Yogish
|||Hi Julian,
You can do lot of operations with Query Analyzer (QA). If you are using SQL
Server 2000 client, you can use Object Browser to access all the database
objects, provided you have the permission.
Check out Books online.
Thanks
Yogish

Enterprise Manager Shortcuts for Remote Servers

Hello and thank you in advance.
I have multiple databases on a single remote MSSQL server (from my ISP). I
can only access them via Enterprise Manager. My quandary is that every time
I log in, I have to click the server name, then wait for the database to
load, then sift through this enormous list to find the database I am seeking
.
I'd like to have shortcuts to each specific database so I can connect and
get to the tables with one click.
Anyone know how to do this?
Thanks,
JulianHi Julian,
Do you have restriction for using Query Analyzer also? If not, while
configuring the client ODBC, you can specify the default database you wish t
o
connect to.
Thanks
Yogish|||Thank you for your reply. I don't think I have a restriction for Query
Analyzer, but I am a novice at best and am only barely qualified to use
Enterprise Manager.
Does this mean I'd have to switch to Query Analyzer or are you suggesting
that I can use Query Analyzer to store my default database and somehow tie
that back to using Enterprise Manager? It puzzles me why Enterprise Manager
doesn't allow you to store a default database for each SQL Server
Registration.
"Yogish" wrote:

> Hi Julian,
> Do you have restriction for using Query Analyzer also? If not, while
> configuring the client ODBC, you can specify the default database you wish
to
> connect to.
> --
> Thanks
> Yogish|||Hi Julian,
You can do lot of operations with Query Analyzer (QA). If you are using SQL
Server 2000 client, you can use Object Browser to access all the database
objects, provided you have the permission.
Check out Books online.
Thanks
Yogish

Enterprise Manager Security

I am trying to retrict access to SQL Server by the NT Administratoe, my
question in two parts:
1. SQL Server 2K and Win 2K Server are loaded on the Same Box. Is there a
way to prevent the NT Administrator gaining access to SQL Server through
Enterprise Maanager?
2. If not is there a way to prevent the NT Administrator making any changes
via Enterprise Manager
Any pointers would be gratefully accepted
MOCHi,
Solution is : Remove SYSADMIN role from BUILD/Administrators.
But this will lead in DB MAintenance plan tasks to fail. So try to avoid
this. Still OS Admins will be able to browse the Enterprise manager.
In general "OS Administrators" can do any activity in the Server being he is
the one administering the entire server and normally
he wont do any thing harm.
Thanks
Hari
MCDBA
"MOC" <MOC@.MOC.com> wrote in message
news:#Y14$EBDEHA.2308@.tk2msftngp13.phx.gbl...
> I am trying to retrict access to SQL Server by the NT Administratoe, my
> question in two parts:
> 1. SQL Server 2K and Win 2K Server are loaded on the Same Box. Is there
a
> way to prevent the NT Administrator gaining access to SQL Server through
> Enterprise Maanager?
> 2. If not is there a way to prevent the NT Administrator making any
changes
> via Enterprise Manager
> Any pointers would be gratefully accepted
> MOC
>|||You can remove builtin\administrators but should add back the service
account that SQL Server and SQL Server Agent are running under, and the [
;nt
authority\system] account if you want to use full-text search.
Probably want to review this article as well:
http://support.microsoft.com/defaul...kb;EN-US;263712
Although it talks about clustered SQL Servers most of the info applies to
stand-alone instances as well...
Richard Waymire, MCSE, MCDBA
This posting is provided "AS IS" with no warranties, and confers no rights.
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:eG0GnoBDEHA.3064@.tk2msftngp13.phx.gbl...
> Hi,
> Solution is : Remove SYSADMIN role from BUILD/Administrators.
> But this will lead in DB MAintenance plan tasks to fail. So try to avoid
> this. Still OS Admins will be able to browse the Enterprise manager.
> In general "OS Administrators" can do any activity in the Server being he
is
> the one administering the entire server and normally
> he wont do any thing harm.
> Thanks
> Hari
> MCDBA
>
> "MOC" <MOC@.MOC.com> wrote in message
> news:#Y14$EBDEHA.2308@.tk2msftngp13.phx.gbl...
there
> a
> changes
>

Enterprise Manager question

Hi everyone,
Wondering if someone can help me with this scenario:
We need to give one of our clients access to their database which is
currently running on one of our SQL servers. We have created the VPN access.
We have also created a SQL login id and given that login-id dbo privileges to
the particular database. Once the client connects via the VPN, using SQL
enterprise manager, he can connect to the database that he needs. He can see
the other db's on the server but cannot go into the tables of those
databases. That is good. However the problem is that he can go to the other
categories in Enterprise manager such as DTS, Management etc. He can see the
users that are defined on the system. We do not want him going into any of
those options. Is there a way in which we can stop him from probing things on
our server. He needs to have access to only one DB and that's all.
Any help in this matter will be greatly appreciated.
Regards
RCdid you hear "=?Utf-8?B?UkM=?=" <RC@.discussions.microsoft.com> say in
news:2A35260C-7263-467E-B6F8-AF9A2D755E34@.microsoft.com:
> Hi everyone,
> Wondering if someone can help me with this scenario:
> We need to give one of our clients access to their database which is
> currently running on one of our SQL servers. We have created the VPN
> access. We have also created a SQL login id and given that login-id
> dbo privileges to the particular database. Once the client connects
> via the VPN, using SQL enterprise manager, he can connect to the
> database that he needs. He can see the other db's on the server but
> cannot go into the tables of those databases. That is good. However
> the problem is that he can go to the other categories in Enterprise
> manager such as DTS, Management etc. He can see the users that are
> defined on the system. We do not want him going into any of those
> options. Is there a way in which we can stop him from probing things
> on our server. He needs to have access to only one DB and that's all.
> Any help in this matter will be greatly appreciated.
> Regards
> RC
>
could you not just give him Query Analyzer? What do they need that
requires the full SEM?
AFAIK the only extension that can be pulled from the MMC is the MetaData
services (not much help for you).
--
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs|||How can we do this (forgive me if this question comes across as a stupid one)
when the user is not in my AD domain? I tried configuring this user as a
Windows NT user and then allowing him access to the DB. But I cannot connect
to the server under those conditions as to use Windows authentication - I
assume - we need to be on the same domain.
"Neil MacMurchy" wrote:
> did you hear "=?Utf-8?B?UkM=?=" <RC@.discussions.microsoft.com> say in
> news:2A35260C-7263-467E-B6F8-AF9A2D755E34@.microsoft.com:
> > Hi everyone,
> > Wondering if someone can help me with this scenario:
> > We need to give one of our clients access to their database which is
> > currently running on one of our SQL servers. We have created the VPN
> > access. We have also created a SQL login id and given that login-id
> > dbo privileges to the particular database. Once the client connects
> > via the VPN, using SQL enterprise manager, he can connect to the
> > database that he needs. He can see the other db's on the server but
> > cannot go into the tables of those databases. That is good. However
> > the problem is that he can go to the other categories in Enterprise
> > manager such as DTS, Management etc. He can see the users that are
> > defined on the system. We do not want him going into any of those
> > options. Is there a way in which we can stop him from probing things
> > on our server. He needs to have access to only one DB and that's all.
> > Any help in this matter will be greatly appreciated.
> >
> > Regards
> > RC
> >
> could you not just give him Query Analyzer? What do they need that
> requires the full SEM?
> AFAIK the only extension that can be pulled from the MMC is the MetaData
> services (not much help for you).
> --
> Neil MacMurchy
> http://spaces.msn.com/members/neilmacmurchy
> http://spaces.msn.com/members/mctblogs
>
>|||The client wants to run scripts and also import data into tables.
"Neil MacMurchy" wrote:
> did you hear "=?Utf-8?B?UkM=?=" <RC@.discussions.microsoft.com> say in
> news:2A35260C-7263-467E-B6F8-AF9A2D755E34@.microsoft.com:
> > Hi everyone,
> > Wondering if someone can help me with this scenario:
> > We need to give one of our clients access to their database which is
> > currently running on one of our SQL servers. We have created the VPN
> > access. We have also created a SQL login id and given that login-id
> > dbo privileges to the particular database. Once the client connects
> > via the VPN, using SQL enterprise manager, he can connect to the
> > database that he needs. He can see the other db's on the server but
> > cannot go into the tables of those databases. That is good. However
> > the problem is that he can go to the other categories in Enterprise
> > manager such as DTS, Management etc. He can see the users that are
> > defined on the system. We do not want him going into any of those
> > options. Is there a way in which we can stop him from probing things
> > on our server. He needs to have access to only one DB and that's all.
> > Any help in this matter will be greatly appreciated.
> >
> > Regards
> > RC
> >
> could you not just give him Query Analyzer? What do they need that
> requires the full SEM?
> AFAIK the only extension that can be pulled from the MMC is the MetaData
> services (not much help for you).
> --
> Neil MacMurchy
> http://spaces.msn.com/members/neilmacmurchy
> http://spaces.msn.com/members/mctblogs
>
>|||did you hear "=?Utf-8?B?UkM=?=" <RC@.discussions.microsoft.com> say in
news:FDA1529D-F67F-40E5-97D6-F7D950D6AA25@.microsoft.com:
> How can we do this (forgive me if this question comes across as a
> stupid one) when the user is not in my AD domain? I tried configuring
> this user as a Windows NT user and then allowing him access to the DB.
> But I cannot connect to the server under those conditions as to use
> Windows authentication - I assume - we need to be on the same domain.
use a standard SQL login. You will need to have the server in Mixed mode
authentication but this is the only way to do this.
--
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs|||did you hear "=?Utf-8?B?UkM=?=" <RC@.discussions.microsoft.com> say in
news:EADF1A2E-01F0-4AD9-A900-E3EAD10C7618@.microsoft.com:
> The client wants to run scripts and also import data into tables.
script can be handled through QA. As for the Import/Export, you could
create the package for them once (assuming they will be running the same
import over and over again) and then save it as a structured storage file,
put the package on a flopy or ftp site and have the client use DTSRUN.EXE
to run the package. There is also BCP, creating a custom app in VS, or 3rd
party products like TOAD for SQL
(http://www.quest.com/toad_for_sql_server/index.asp) that might be of use.
(TOAD is free, but you have to re-download it every 60 days which is a
pain)
--
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs|||did you hear Neil MacMurchy <neilmcse@.nospamf.gmail.com> say in
news:Xns96CAA3729742Aneilmcsehotmailcom@.207.46.248.16:
> 3rd
> party products like TOAD for SQL
> (http://www.quest.com/toad_for_sql_server/index.asp) that might be of
> use.
> (TOAD is free, but you have to re-download it every 60 days which is a
> pain)
DOH! TOAD's free version doesn't include an import. Sorry about that...
SQL Import from EMS is only $65 though
http://www.sqlmanager.net/en/products/mssql/dataimport
(they have a manager as well, but it also allows you to see the users)
--
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs|||Neil, thanks for your tips so far. A quick question though.
My problem is that my client is fairly knowledgeable. If I create a standard
SQL login for him and assign him permissions only on that database, he can
easily figure out that if he can connect via Query Analyzer, he might be able
to connect via SEM as well. I currently have assigned his SQL login dbo
permissions on that particular database.
"Neil MacMurchy" wrote:
> did you hear Neil MacMurchy <neilmcse@.nospamf.gmail.com> say in
> news:Xns96CAA3729742Aneilmcsehotmailcom@.207.46.248.16:
> > 3rd
> > party products like TOAD for SQL
> > (http://www.quest.com/toad_for_sql_server/index.asp) that might be of
> > use.
> >
> > (TOAD is free, but you have to re-download it every 60 days which is a
> > pain)
> DOH! TOAD's free version doesn't include an import. Sorry about that...
> SQL Import from EMS is only $65 though
> http://www.sqlmanager.net/en/products/mssql/dataimport
> (they have a manager as well, but it also allows you to see the users)
> --
> Neil MacMurchy
> http://spaces.msn.com/members/neilmacmurchy
> http://spaces.msn.com/members/mctblogs
>
>|||did you hear "=?Utf-8?B?UkM=?=" <RC@.discussions.microsoft.com> say in
news:B0BD1429-D580-4454-A286-3A028BE5164B@.microsoft.com:
> My problem is that my client is fairly knowledgeable. If I create a
> standard SQL login for him and assign him permissions only on that
> database, he can easily figure out that if he can connect via Query
> Analyzer, he might be able to connect via SEM as well. I currently
> have assigned his SQL login dbo permissions on that particular
> database.
>
you will be in a bit of a bind then. There is much that would keep someone
from downloading the SEM (from an eval version or like) or some other tool.
There is the ability to use GPOs to keep people from using applications,
but since he is outside your domain this wont help.
You can lock down the access he gets (assign permissions to just the one
database is a start, though everyone will get access to Master and tempdb)
but you can look at denying permission on certain system wide functions
(create/alter/drop database, sp_grantlogin, sp_addlogin, etc.) but you will
need to spend some time.
if it's going to be a real problem I would question giving them access at
all. Place them on a separate server and if the cuase thier own headaches,
they deserve them.
then again, perhaps someone else knows of a way...<g>
--
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs|||Hey Neil,
I took the help of one of my colleagues and he dug out this article:
http://groups.google.com/group/microsoft.public.kr.sql/browse_frm/thread/f0594d4801e5e3b/36d78f72cf839622?lnk=st&q=prevent+use+of+%22enterprise+manager%22&rnum=21&hl=en#36d78f72cf839622
I used step g from this article and was able to stop the user from viewing
the packages. Now the only issue I have to worry about is to stop him from
viewing the SQL logins on the server. He can see the logins but cannot do
anything with them. I would neverthless not like him seeing the users at all
if I can.
Thanks for all your help.
Regards
"Neil MacMurchy" wrote:
> did you hear "=?Utf-8?B?UkM=?=" <RC@.discussions.microsoft.com> say in
> news:B0BD1429-D580-4454-A286-3A028BE5164B@.microsoft.com:
> > My problem is that my client is fairly knowledgeable. If I create a
> > standard SQL login for him and assign him permissions only on that
> > database, he can easily figure out that if he can connect via Query
> > Analyzer, he might be able to connect via SEM as well. I currently
> > have assigned his SQL login dbo permissions on that particular
> > database.
> >
> you will be in a bit of a bind then. There is much that would keep someone
> from downloading the SEM (from an eval version or like) or some other tool.
> There is the ability to use GPOs to keep people from using applications,
> but since he is outside your domain this wont help.
> You can lock down the access he gets (assign permissions to just the one
> database is a start, though everyone will get access to Master and tempdb)
> but you can look at denying permission on certain system wide functions
> (create/alter/drop database, sp_grantlogin, sp_addlogin, etc.) but you will
> need to spend some time.
> if it's going to be a real problem I would question giving them access at
> all. Place them on a separate server and if the cuase thier own headaches,
> they deserve them.
> then again, perhaps someone else knows of a way...<g>
> --
> Neil MacMurchy
> http://spaces.msn.com/members/neilmacmurchy
> http://spaces.msn.com/members/mctblogs
>
>

Enterprise Manager question

Hi everyone,
Wondering if someone can help me with this scenario:
We need to give one of our clients access to their database which is
currently running on one of our SQL servers. We have created the VPN access.
We have also created a SQL login id and given that login-id dbo privileges to
the particular database. Once the client connects via the VPN, using SQL
enterprise manager, he can connect to the database that he needs. He can see
the other db's on the server but cannot go into the tables of those
databases. That is good. However the problem is that he can go to the other
categories in Enterprise manager such as DTS, Management etc. He can see the
users that are defined on the system. We do not want him going into any of
those options. Is there a way in which we can stop him from probing things on
our server. He needs to have access to only one DB and that's all.
Any help in this matter will be greatly appreciated.
Regards
RC
did you hear "=?Utf-8?B?UkM=?=" <RC@.discussions.microsoft.com> say in
news:2A35260C-7263-467E-B6F8-AF9A2D755E34@.microsoft.com:

> Hi everyone,
> Wondering if someone can help me with this scenario:
> We need to give one of our clients access to their database which is
> currently running on one of our SQL servers. We have created the VPN
> access. We have also created a SQL login id and given that login-id
> dbo privileges to the particular database. Once the client connects
> via the VPN, using SQL enterprise manager, he can connect to the
> database that he needs. He can see the other db's on the server but
> cannot go into the tables of those databases. That is good. However
> the problem is that he can go to the other categories in Enterprise
> manager such as DTS, Management etc. He can see the users that are
> defined on the system. We do not want him going into any of those
> options. Is there a way in which we can stop him from probing things
> on our server. He needs to have access to only one DB and that's all.
> Any help in this matter will be greatly appreciated.
> Regards
> RC
>
could you not just give him Query Analyzer? What do they need that
requires the full SEM?
AFAIK the only extension that can be pulled from the MMC is the MetaData
services (not much help for you).
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs
|||How can we do this (forgive me if this question comes across as a stupid one)
when the user is not in my AD domain? I tried configuring this user as a
Windows NT user and then allowing him access to the DB. But I cannot connect
to the server under those conditions as to use Windows authentication - I
assume - we need to be on the same domain.
"Neil MacMurchy" wrote:

> did you hear "=?Utf-8?B?UkM=?=" <RC@.discussions.microsoft.com> say in
> news:2A35260C-7263-467E-B6F8-AF9A2D755E34@.microsoft.com:
>
> could you not just give him Query Analyzer? What do they need that
> requires the full SEM?
> AFAIK the only extension that can be pulled from the MMC is the MetaData
> services (not much help for you).
> --
> Neil MacMurchy
> http://spaces.msn.com/members/neilmacmurchy
> http://spaces.msn.com/members/mctblogs
>
>
|||The client wants to run scripts and also import data into tables.
"Neil MacMurchy" wrote:

> did you hear "=?Utf-8?B?UkM=?=" <RC@.discussions.microsoft.com> say in
> news:2A35260C-7263-467E-B6F8-AF9A2D755E34@.microsoft.com:
>
> could you not just give him Query Analyzer? What do they need that
> requires the full SEM?
> AFAIK the only extension that can be pulled from the MMC is the MetaData
> services (not much help for you).
> --
> Neil MacMurchy
> http://spaces.msn.com/members/neilmacmurchy
> http://spaces.msn.com/members/mctblogs
>
>
|||did you hear "=?Utf-8?B?UkM=?=" <RC@.discussions.microsoft.com> say in
news:FDA1529D-F67F-40E5-97D6-F7D950D6AA25@.microsoft.com:

> How can we do this (forgive me if this question comes across as a
> stupid one) when the user is not in my AD domain? I tried configuring
> this user as a Windows NT user and then allowing him access to the DB.
> But I cannot connect to the server under those conditions as to use
> Windows authentication - I assume - we need to be on the same domain.
use a standard SQL login. You will need to have the server in Mixed mode
authentication but this is the only way to do this.
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs
|||did you hear "=?Utf-8?B?UkM=?=" <RC@.discussions.microsoft.com> say in
news:EADF1A2E-01F0-4AD9-A900-E3EAD10C7618@.microsoft.com:

> The client wants to run scripts and also import data into tables.
script can be handled through QA. As for the Import/Export, you could
create the package for them once (assuming they will be running the same
import over and over again) and then save it as a structured storage file,
put the package on a flopy or ftp site and have the client use DTSRUN.EXE
to run the package. There is also BCP, creating a custom app in VS, or 3rd
party products like TOAD for SQL
(http://www.quest.com/toad_for_sql_server/index.asp) that might be of use.
(TOAD is free, but you have to re-download it every 60 days which is a
pain)
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs
|||did you hear Neil MacMurchy <neilmcse@.nospamf.gmail.com> say in
news:Xns96CAA3729742Aneilmcsehotmailcom@.207.46.248 .16:

> 3rd
> party products like TOAD for SQL
> (http://www.quest.com/toad_for_sql_server/index.asp) that might be of
> use.
> (TOAD is free, but you have to re-download it every 60 days which is a
> pain)
DOH! TOAD's free version doesn't include an import. Sorry about that...
SQL Import from EMS is only $65 though
http://www.sqlmanager.net/en/products/mssql/dataimport
(they have a manager as well, but it also allows you to see the users)
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs
|||Neil, thanks for your tips so far. A quick question though.
My problem is that my client is fairly knowledgeable. If I create a standard
SQL login for him and assign him permissions only on that database, he can
easily figure out that if he can connect via Query Analyzer, he might be able
to connect via SEM as well. I currently have assigned his SQL login dbo
permissions on that particular database.
"Neil MacMurchy" wrote:

> did you hear Neil MacMurchy <neilmcse@.nospamf.gmail.com> say in
> news:Xns96CAA3729742Aneilmcsehotmailcom@.207.46.248 .16:
>
> DOH! TOAD's free version doesn't include an import. Sorry about that...
> SQL Import from EMS is only $65 though
> http://www.sqlmanager.net/en/products/mssql/dataimport
> (they have a manager as well, but it also allows you to see the users)
> --
> Neil MacMurchy
> http://spaces.msn.com/members/neilmacmurchy
> http://spaces.msn.com/members/mctblogs
>
>
|||did you hear "=?Utf-8?B?UkM=?=" <RC@.discussions.microsoft.com> say in
news:B0BD1429-D580-4454-A286-3A028BE5164B@.microsoft.com:

> My problem is that my client is fairly knowledgeable. If I create a
> standard SQL login for him and assign him permissions only on that
> database, he can easily figure out that if he can connect via Query
> Analyzer, he might be able to connect via SEM as well. I currently
> have assigned his SQL login dbo permissions on that particular
> database.
>
you will be in a bit of a bind then. There is much that would keep someone
from downloading the SEM (from an eval version or like) or some other tool.
There is the ability to use GPOs to keep people from using applications,
but since he is outside your domain this wont help.
You can lock down the access he gets (assign permissions to just the one
database is a start, though everyone will get access to Master and tempdb)
but you can look at denying permission on certain system wide functions
(create/alter/drop database, sp_grantlogin, sp_addlogin, etc.) but you will
need to spend some time.
if it's going to be a real problem I would question giving them access at
all. Place them on a separate server and if the cuase thier own headaches,
they deserve them.
then again, perhaps someone else knows of a way...<g>
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs
|||Hey Neil,
I took the help of one of my colleagues and he dug out this article:
http://groups.google.com/group/micro...d78f72cf839622
I used step g from this article and was able to stop the user from viewing
the packages. Now the only issue I have to worry about is to stop him from
viewing the SQL logins on the server. He can see the logins but cannot do
anything with them. I would neverthless not like him seeing the users at all
if I can.
Thanks for all your help.
Regards
"Neil MacMurchy" wrote:

> did you hear "=?Utf-8?B?UkM=?=" <RC@.discussions.microsoft.com> say in
> news:B0BD1429-D580-4454-A286-3A028BE5164B@.microsoft.com:
>
> you will be in a bit of a bind then. There is much that would keep someone
> from downloading the SEM (from an eval version or like) or some other tool.
> There is the ability to use GPOs to keep people from using applications,
> but since he is outside your domain this wont help.
> You can lock down the access he gets (assign permissions to just the one
> database is a start, though everyone will get access to Master and tempdb)
> but you can look at denying permission on certain system wide functions
> (create/alter/drop database, sp_grantlogin, sp_addlogin, etc.) but you will
> need to spend some time.
> if it's going to be a real problem I would question giving them access at
> all. Place them on a separate server and if the cuase thier own headaches,
> they deserve them.
> then again, perhaps someone else knows of a way...<g>
> --
> Neil MacMurchy
> http://spaces.msn.com/members/neilmacmurchy
> http://spaces.msn.com/members/mctblogs
>
>

Enterprise Manager question

Hi everyone,
Wondering if someone can help me with this scenario:
We need to give one of our clients access to their database which is
currently running on one of our SQL servers. We have created the VPN access.
We have also created a SQL login id and given that login-id dbo privileges t
o
the particular database. Once the client connects via the VPN, using SQL
enterprise manager, he can connect to the database that he needs. He can see
the other db's on the server but cannot go into the tables of those
databases. That is good. However the problem is that he can go to the other
categories in Enterprise manager such as DTS, Management etc. He can see the
users that are defined on the system. We do not want him going into any of
those options. Is there a way in which we can stop him from probing things o
n
our server. He needs to have access to only one DB and that's all.
Any help in this matter will be greatly appreciated.
Regards
RCdid you hear "examnotes" <RC@.discussions.microsoft.com> say in
news:2A35260C-7263-467E-B6F8-AF9A2D755E34@.microsoft.com:

> Hi everyone,
> Wondering if someone can help me with this scenario:
> We need to give one of our clients access to their database which is
> currently running on one of our SQL servers. We have created the VPN
> access. We have also created a SQL login id and given that login-id
> dbo privileges to the particular database. Once the client connects
> via the VPN, using SQL enterprise manager, he can connect to the
> database that he needs. He can see the other db's on the server but
> cannot go into the tables of those databases. That is good. However
> the problem is that he can go to the other categories in Enterprise
> manager such as DTS, Management etc. He can see the users that are
> defined on the system. We do not want him going into any of those
> options. Is there a way in which we can stop him from probing things
> on our server. He needs to have access to only one DB and that's all.
> Any help in this matter will be greatly appreciated.
> Regards
> RC
>
could you not just give him Query Analyzer? What do they need that
requires the full SEM?
AFAIK the only extension that can be pulled from the MMC is the MetaData
services (not much help for you).
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs|||How can we do this (forgive me if this question comes across as a stupid one
)
when the user is not in my AD domain? I tried configuring this user as a
Windows NT user and then allowing him access to the DB. But I cannot connect
to the server under those conditions as to use Windows authentication - I
assume - we need to be on the same domain.
"Neil MacMurchy" wrote:

> did you hear "examnotes" <RC@.discussions.microsoft.com> say in
> news:2A35260C-7263-467E-B6F8-AF9A2D755E34@.microsoft.com:
>
> could you not just give him Query Analyzer? What do they need that
> requires the full SEM?
> AFAIK the only extension that can be pulled from the MMC is the MetaData
> services (not much help for you).
> --
> Neil MacMurchy
> http://spaces.msn.com/members/neilmacmurchy
> http://spaces.msn.com/members/mctblogs
>
>|||The client wants to run scripts and also import data into tables.
"Neil MacMurchy" wrote:

> did you hear "examnotes" <RC@.discussions.microsoft.com> say in
> news:2A35260C-7263-467E-B6F8-AF9A2D755E34@.microsoft.com:
>
> could you not just give him Query Analyzer? What do they need that
> requires the full SEM?
> AFAIK the only extension that can be pulled from the MMC is the MetaData
> services (not much help for you).
> --
> Neil MacMurchy
> http://spaces.msn.com/members/neilmacmurchy
> http://spaces.msn.com/members/mctblogs
>
>|||did you hear "examnotes" <RC@.discussions.microsoft.com> say in
news:FDA1529D-F67F-40E5-97D6-F7D950D6AA25@.microsoft.com:

> How can we do this (forgive me if this question comes across as a
> stupid one) when the user is not in my AD domain? I tried configuring
> this user as a Windows NT user and then allowing him access to the DB.
> But I cannot connect to the server under those conditions as to use
> Windows authentication - I assume - we need to be on the same domain.
use a standard SQL login. You will need to have the server in Mixed mode
authentication but this is the only way to do this.
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs|||did you hear "examnotes" <RC@.discussions.microsoft.com> say in
news:EADF1A2E-01F0-4AD9-A900-E3EAD10C7618@.microsoft.com:

> The client wants to run scripts and also import data into tables.
script can be handled through QA. As for the Import/Export, you could
create the package for them once (assuming they will be running the same
import over and over again) and then save it as a structured storage file,
put the package on a flopy or ftp site and have the client use DTSRUN.EXE
to run the package. There is also BCP, creating a custom app in VS, or 3rd
party products like TOAD for SQL
(http://www.quest.com/toad_for_sql_server/index.asp) that might be of use.
(TOAD is free, but you have to re-download it every 60 days which is a
pain)
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs|||did you hear Neil MacMurchy <neilmcse@.nospamf.gmail.com> say in
news:Xns96CAA3729742Aneilmcsehotmailcom@.
207.46.248.16:

> 3rd
> party products like TOAD for SQL
> (http://www.quest.com/toad_for_sql_server/index.asp) that might be of
> use.
> (TOAD is free, but you have to re-download it every 60 days which is a
> pain)
DOH! TOAD's free version doesn't include an import. Sorry about that...
SQL Import from EMS is only $65 though
http://www.sqlmanager.net/en/products/mssql/dataimport
(they have a manager as well, but it also allows you to see the users)
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs|||Neil, thanks for your tips so far. A quick question though.
My problem is that my client is fairly knowledgeable. If I create a standard
SQL login for him and assign him permissions only on that database, he can
easily figure out that if he can connect via Query Analyzer, he might be abl
e
to connect via SEM as well. I currently have assigned his SQL login dbo
permissions on that particular database.
"Neil MacMurchy" wrote:

> did you hear Neil MacMurchy <neilmcse@.nospamf.gmail.com> say in
> news:Xns96CAA3729742Aneilmcsehotmailcom@.
207.46.248.16:
>
> DOH! TOAD's free version doesn't include an import. Sorry about that...
> SQL Import from EMS is only $65 though
> http://www.sqlmanager.net/en/products/mssql/dataimport
> (they have a manager as well, but it also allows you to see the users)
> --
> Neil MacMurchy
> http://spaces.msn.com/members/neilmacmurchy
> http://spaces.msn.com/members/mctblogs
>
>|||did you hear "examnotes" <RC@.discussions.microsoft.com> say in
news:B0BD1429-D580-4454-A286-3A028BE5164B@.microsoft.com:

> My problem is that my client is fairly knowledgeable. If I create a
> standard SQL login for him and assign him permissions only on that
> database, he can easily figure out that if he can connect via Query
> Analyzer, he might be able to connect via SEM as well. I currently
> have assigned his SQL login dbo permissions on that particular
> database.
>
you will be in a bit of a bind then. There is much that would keep someone
from downloading the SEM (from an eval version or like) or some other tool.
There is the ability to use GPOs to keep people from using applications,
but since he is outside your domain this wont help.
You can lock down the access he gets (assign permissions to just the one
database is a start, though everyone will get access to Master and tempdb)
but you can look at denying permission on certain system wide functions
(create/alter/drop database, sp_grantlogin, sp_addlogin, etc.) but you will
need to spend some time.
if it's going to be a real problem I would question giving them access at
all. Place them on a separate server and if the cuase thier own headaches,
they deserve them.
then again, perhaps someone else knows of a way...<g>
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs|||Hey Neil,
I took the help of one of my colleagues and he dug out this article:
http://groups.google.com/group/micr...6d78f72cf839622
I used step g from this article and was able to stop the user from viewing
the packages. Now the only issue I have to worry about is to stop him from
viewing the SQL logins on the server. He can see the logins but cannot do
anything with them. I would neverthless not like him seeing the users at all
if I can.
Thanks for all your help.
Regards
"Neil MacMurchy" wrote:

> did you hear "examnotes" <RC@.discussions.microsoft.com> say in
> news:B0BD1429-D580-4454-A286-3A028BE5164B@.microsoft.com:
>
> you will be in a bit of a bind then. There is much that would keep someone
> from downloading the SEM (from an eval version or like) or some other tool
.
> There is the ability to use GPOs to keep people from using applications,
> but since he is outside your domain this wont help.
> You can lock down the access he gets (assign permissions to just the one
> database is a start, though everyone will get access to Master and tempdb)
> but you can look at denying permission on certain system wide functions
> (create/alter/drop database, sp_grantlogin, sp_addlogin, etc.) but you wil
l
> need to spend some time.
> if it's going to be a real problem I would question giving them access at
> all. Place them on a separate server and if the cuase thier own headaches,
> they deserve them.
> then again, perhaps someone else knows of a way...<g>
> --
> Neil MacMurchy
> http://spaces.msn.com/members/neilmacmurchy
> http://spaces.msn.com/members/mctblogs
>
>

Enterprise manager loading databases

Hi,

I'm on a shared MS SQL server, and I have access on 2 databases there.
On the server there are many other database, I guess around 170.
When I want to access my database I open enterprise manager choose the server from the server list and click on "databases" to list all databases. This last step (listing all databases) takes ages to complete, it takes something like 3 minutes to show all databases so I can click my database from the list and do my thing.

Isn't there any way to only view my own databases and not showing the others. I can't access the others so there's really no point in showing them anyway, it only takes time.

anyone?

Enterprise Manager Hangs

Everytime I try to use the Enterprise Manager tool to
access my SQL 2000 (sp3) server on a W2K3 Enterprise
machine it hangs (stops responding). The tool loads
correctly but when I dig down the tree to get at the
instance on the local machine it stops responding when
trying to Establish the Connection and checking Windows
Security.
The server is loading and running, but I can't make any
new databases.
I can stop and start the server.
I have tried reinstalling. I have checked that the
services are running correctly.Hi, please verify permissions on folder Microsoft SQL Server.
Verify if you services account have permissions on foder MSSQL
Verify the event viewer to view if you have more messages about mssqlserver
The problem is generally by permissions
I hope help you
Carlos Augusto
Bogot=E1 Colombia
>--Original Message--
>Everytime I try to use the Enterprise Manager tool to >access my SQL 2000 (sp3) server on a W2K3 Enterprise >machine it hangs (stops responding). The tool loads >correctly but when I dig down the tree to get at the >instance on the local machine it stops responding when >trying to Establish the Connection and checking Windows >Security.
>The server is loading and running, but I can't make any >new databases.
>I can stop and start the server.
>I have tried reinstalling. I have checked that the >services are running correctly.
>.
>

Enterprise Manager from desktop

Is there a way to install just the Enterprise Manager on my workstation so I
can access databases on our servers without having to install the whole SQL
Server 2000 program? Thanks.
David
Yes, install just the client tools. This can be done by only selecting the
"Client Tools Only" radio button on the "Installation Definition"
installation window.
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"David" <dlchase@.lifetimeinc.com> wrote in message
news:ew0zW4hUEHA.3988@.tk2msftngp13.phx.gbl...
> Is there a way to install just the Enterprise Manager on my workstation so
I
> can access databases on our servers without having to install the whole
SQL
> Server 2000 program? Thanks.
> David
>

Enterprise Manager from desktop

Is there a way to install just the Enterprise Manager on my workstation so I
can access databases on our servers without having to install the whole SQL
Server 2000 program? Thanks.
DavidYes, install just the client tools. This can be done by only selecting the
"Client Tools Only" radio button on the "Installation Definition"
installation window.
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"David" <dlchase@.lifetimeinc.com> wrote in message
news:ew0zW4hUEHA.3988@.tk2msftngp13.phx.gbl...
> Is there a way to install just the Enterprise Manager on my workstation so
I
> can access databases on our servers without having to install the whole
SQL
> Server 2000 program? Thanks.
> David
>

Enterprise Manager from desktop

Is there a way to install just the Enterprise Manager on my workstation so I
can access databases on our servers without having to install the whole SQL
Server 2000 program? Thanks.
DavidYes, install just the client tools. This can be done by only selecting the
"Client Tools Only" radio button on the "Installation Definition"
installation window.
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"David" <dlchase@.lifetimeinc.com> wrote in message
news:ew0zW4hUEHA.3988@.tk2msftngp13.phx.gbl...
> Is there a way to install just the Enterprise Manager on my workstation so
I
> can access databases on our servers without having to install the whole
SQL
> Server 2000 program? Thanks.
> David
>

Enterprise Manager error when using WITH (NOLOCKS)

I have added WITH (NOLOCK) to a couple of views that were causing unecessary
blocking for MS Access end user reporting.
I can update edit and save the views fine with Query Analyzer, however when
I try and run the query from Enterprise Manager Design View I get this
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect Syntax
The views work fine in everywhere else. When in EM if I remove the WITH
(NOLOCK) I do not get the error.
It looks as if the alias is causing the issue.
select * from MyTable WITH (NOLOCK) works
however Select * from MyTable mt WITH (NOLOCK) causes the error. But only in
EM not in query analyzer.
Is anyone else experiencing this?
"lurdan" wrote:

> I have added WITH (NOLOCK) to a couple of views that were causing unecessary
> blocking for MS Access end user reporting.
> I can update edit and save the views fine with Query Analyzer, however when
> I try and run the query from Enterprise Manager Design View I get this
> [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect Syntax
> The views work fine in everywhere else. When in EM if I remove the WITH
> (NOLOCK) I do not get the error.
>
|||I got the same result. When I tried to run the query in EM, the query tool
inverted the alias to after the table hint, which is bad syntax.
Russel Loski, MCSD.Net
"lurdan" wrote:
[vbcol=seagreen]
> It looks as if the alias is causing the issue.
> select * from MyTable WITH (NOLOCK) works
> however Select * from MyTable mt WITH (NOLOCK) causes the error. But only in
> EM not in query analyzer.
> Is anyone else experiencing this?
> "lurdan" wrote:

Enterprise Manager error when using WITH (NOLOCKS)

I have added WITH (NOLOCK) to a couple of views that were causing unecessary
blocking for MS Access end user reporting.
I can update edit and save the views fine with Query Analyzer, however when
I try and run the query from Enterprise Manager Design View I get this
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect
Syntax
The views work fine in everywhere else. When in EM if I remove the WITH
(NOLOCK) I do not get the error.It looks as if the alias is causing the issue.
select * from MyTable WITH (NOLOCK) works
however Select * from MyTable mt WITH (NOLOCK) causes the error. But only in
EM not in query analyzer.
Is anyone else experiencing this?
"lurdan" wrote:

> I have added WITH (NOLOCK) to a couple of views that were causing unecessa
ry
> blocking for MS Access end user reporting.
> I can update edit and save the views fine with Query Analyzer, however whe
n
> I try and run the query from Enterprise Manager Design View I get this
> [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorre
ct Syntax
> The views work fine in everywhere else. When in EM if I remove the WITH
> (NOLOCK) I do not get the error.
>|||I got the same result. When I tried to run the query in EM, the query tool
inverted the alias to after the table hint, which is bad syntax.
--
Russel Loski, MCSD.Net
"lurdan" wrote:
[vbcol=seagreen]
> It looks as if the alias is causing the issue.
> select * from MyTable WITH (NOLOCK) works
> however Select * from MyTable mt WITH (NOLOCK) causes the error. But only
in
> EM not in query analyzer.
> Is anyone else experiencing this?
> "lurdan" wrote:
>

Enterprise manager crashing SQL Server

Hi

We're having trouble with Enterprise manager when trying to view views or table data/properties, we get an access violation error and the database crashes sometimes corrupting tables.
Ive seen some posts stating a post SP3 for SQL Server is required and some posts saying client access should be modfied.
However our enterprise manager clients are registered with SA so I dont think that is an issue.
Can anyone give me some help with this and/or direct me to the hotfix please.

Cheers

LouiseThis is the problem due to mismatched DLLs and corrupted client tools installed. Ensure to install SQL client tools from scratch.

And its better to review the task using query analyzer rather than opening from Enterprise Manager due to flaky behaviour of such GUI tools.|||thanks will give that a go and reinstall my client tools

Cheers

Enterprise Manager Connection

I need to open up my firewall so that a client can access my SQL server
using the Enterprise Manager. I opened up port 1433, but apparently
that's just the data connection port, not for management.
Where can I get a list of all SQL related ports?
Thanks!
--DaveINF: TCP Ports Needed for Communication to SQL Server Through a Firewall
http://support.microsoft.com/defaul...2&Product=sql2k
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Dave Navarro" <dave@.dave.dave> wrote in message
news:MPG.1a959ad45c280f7a989782@.news-40.giganews.com...
> I need to open up my firewall so that a client can access my SQL server
> using the Enterprise Manager. I opened up port 1433, but apparently
> that's just the data connection port, not for management.
> Where can I get a list of all SQL related ports?
> Thanks!
> --Dave|||You can use 1433 for management if the client is connecting using TCP
Sockets. They can create an alias or change the NetLib choice in the Client
Network Utility to use 1433 for this. If they are using Named Pipes
(probably) then they are using the same ports as TCP over NetBios. I think
it is 135, 137, and 138. If they are using MultiProtocol (doubt it) they
are using the ports for RPC, 135 for the mapper and then 50,000 and up.
Christian Smith
"Dave Navarro" <dave@.dave.dave> wrote in message
news:MPG.1a959ad45c280f7a989782@.news-40.giganews.com...
> I need to open up my firewall so that a client can access my SQL server
> using the Enterprise Manager. I opened up port 1433, but apparently
> that's just the data connection port, not for management.
> Where can I get a list of all SQL related ports?
> Thanks!
> --Dave|||In article <OPZAL5Z8DHA.2480@.TK2MSFTNGP12.phx.gbl>,
SRDBA@.Careerbuilder.com says...
> INF: TCP Ports Needed for Communication to SQL Server Through a Firewall
> http://support.microsoft.com/defaul...www.sqlpass.org
> "Dave Navarro" <dave@.dave.dave> wrote in message
> news:MPG.1a959ad45c280f7a989782@.news-40.giganews.com...
Thanks for the URL.
Unfortunately, I can't connect to the server using Enterprise Manager.
I can connect to it from ASP, however. The FAQ isn't specific as to
what the connection is used for, but from experience it's used to send
queries/receive data (strictly for data access).
When I am "inside" the firewall and use Enterprise Manager to connect
from another computer on the same network, it works just fine. But I
can't connect to it using Enterprise Manager from outside of the
firewall.
--Dave|||In article <u39HU5Z8DHA.1632@.TK2MSFTNGP12.phx.gbl>, csmith@.digex.com
says...
> You can use 1433 for management if the client is connecting using TCP
> Sockets. They can create an alias or change the NetLib choice in the Clie
nt
> Network Utility to use 1433 for this. If they are using Named Pipes
> (probably) then they are using the same ports as TCP over NetBios. I thin
k
> it is 135, 137, and 138. If they are using MultiProtocol (doubt it) they
> are using the ports for RPC, 135 for the mapper and then 50,000 and up.
> Christian Smith
> "Dave Navarro" <dave@.dave.dave> wrote in message
> news:MPG.1a959ad45c280f7a989782@.news-40.giganews.com...
Thanks for the info. I can successfully connect to port 1433 on the
server using ASP code running in IIS from outside the firewall.
However, Enterprise Manager will not make a connection to the server at
all.
--Dave|||I download TDImon from www.sysinternals.com and watched the
connections...
Enterprise Manager uses port 1433 for its data connection and port 1434
for the management connection.
It's working now.
--Dave
In article <MPG.1a9600c12aad755f989784@.news-40.giganews.com>,
dave@.dave.dave says...
> In article <u39HU5Z8DHA.1632@.TK2MSFTNGP12.phx.gbl>, csmith@.digex.com
> says...
> Thanks for the info. I can successfully connect to port 1433 on the
> server using ASP code running in IIS from outside the firewall.
> However, Enterprise Manager will not make a connection to the server at
> all.