localhost question

I have always connected to my MS SQL DB's through a remote connection, like this:

<%
Dim oConn, oRS, sSQL
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open "Driver={SQL Server};" & _
"Database=mydb;" & _
"Server=myservername;" & _
"UID=mylogin;" & _
"PWD=mypwd"
%>

Is there a way to connect to an MS SQL DB locally with VBScript... I have seen code blocks use the keywork localhost... does that have something to do with it?localhost will point to the local machine...

if you look in your hosts file you will find localhost is mapped to 127.0.0.1 which is the loopback address for the ethernet adapater...

So yeah,.. you can use the machine name specificly, the ip number for the local machine, localhost, or 127.0.0.1 as your myservername value|||Originally posted by rokslide
localhost will point to the local machine...

if you look in your hosts file you will find localhost is mapped to 127.0.0.1 which is the loopback address for the ethernet adapater...

So yeah,.. you can use the machine name specificly, the ip number for the local machine, localhost, or 127.0.0.1 as your myservername value

Ahhhh I see... but that gives me a problem... localhost only works if you are running SQL Server on your own machine. I wanted to connect to a server that I don't run, then, even if the website is located on the same network as the SQL server, I would have to connect to it remotely?|||if the website is on the same box as the database then when the vbscript to connect to the database is executed it will execute in the context of the server it resides on... so localhost would work...

if you then took the code and tried to run it on your machine it would look on your machine for the database which would fail.

does that make sense??

Local VB.NET app connection to remote SQL server

I know this is strickly not a website question, but dunno where else to post...

To remotely admin and monitor some functions of the website, I wish to use a local application to connect to the MSSQL DB which is held on the remote webhosting server

I have the following code:

Dim StrSQLUNAs String ="[UN]"Dim StrSQLPWAs String ="[PW]"Dim StrServerAs String ="[IP]\[INSTANCE]"Dim StrDBAs String ="[DB]"Dim strTimeOutAs String ="Connection Timeout=0;"Dim pStrSQLConnAs String ="Server=" & StrServer &";Database=" & StrDB &";User Id=" & StrSQLUN &";Password=" & StrSQLPW &";" & strTimeOutDim sqlConnAs New SqlClient.SqlConnection(pStrSQLConn)If sqlConn.State = ConnectionState.ClosedThen sqlConn.Open()

This has basically been take from the existing code on the website, but changing to the server details. I had just started dev'ing this app when the admins decided to move the SQL server over to a different server. It was working on the old one, but the new one doesn't. It just times out after whatever time you put in the timeout variable. 0=unlimited, and so just sits there.

I am also using the MSSQL Server Management Studio locally to connect to the same database, and although slow, does connect after about a minute or so. I thought they would be using the same type of underlying connection to access the server and database? Is this correct?

Can they put restrictions in place for this specific sort of data access?

Does anyone have any suggestions on how to resolve this issue??

Thanks for any help

Adam.

does the new server allow external connections? A lot of hosts I've used in the past only allow you to connect to the SQL database from an Asp.Net app on their webservers.

|||

Yeah it does allow external access as I am using MSSQL Mgmt Studio.

Anyway I think I have found the cause of the problem - my Cisco router. I needed to put in IP Inspect rules in for the MSSQL. Thing that confuses me still is that I could use the Mgnt Studio and I thought it would be accessing it in the same way. Anyway - it still seems to work - albeit with random connection times (eg last night it was connecting in a second or to and now taking about 5mins!)

Anyway have experience in this area??

Thanks

Adam.

Local user XP -> remote XP

HI Guys,
Got the following scenario:
Client machine: Windows XP Pro (SP2), logged in as local user
Server machine: Windows XP Pro (SP2), running SQL Server 2000
both machines in same Domain.
I am attempting to connect from the client to the server using SQL
Server authentication, which is definitely enabled as an option on the
remote machine. SQL Client Network lists TCP/IP and Named Pipes
enabled, with TCP/IP highest. Neither connecting through TCP/IP nor
name pipes (using isql) succeeds, and I get a generic "SQL Server does
not exist" type message through query analyzer.
This started with a problem running an ASP.NET website against a remote
database, but I've verified I can 1) Connect to the server using
Windows Authentication, when I'm logged in as a domain user, 2) Connect
using the SQL Server credentials, when I'm logged in as a domain user.
I wouldn't be surprised for NP to fail when I'm a local user, but
TCP/IP shouldn't be affected by who's logged in, should it?
Okay, normal bits now of "this used to work, and I'm sure I haven't
changed anything". Only thing I have done recently was install the
latest MS updates as delivered through Automatic Updates. I'm not sure
if anything has changed on the server, since it's another devs
development machine, but I wouldn't expect him to have been "playing"
in this area.
I was initially unconcerned by this problem, since our live webserver
talks directly to a local instance of SQL, so all should be well. Until
I remembered that as part of the next rollout, we plan to move the
databases onto a separate box. So I'd like to understand this problem
in the development area in case it happens on live as well.
Sorry for rambling on. And I've probably forgotten something important
too. Thanks in advance for any help.
DamienHi,
I guess secuzrity is blocking your request on the other machine. YOu
have to open the ports of SQL Server on the hosting machine that the
client can connect to an instance. The default port of SQL Server is
1433, but that can be different on your machine if you changed another
one on installation time. For more information about Windows XP SP2 and
SQL Server read the following article.
http://support.microsoft.com/defaul...kb;en-us;841249
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--|||Jens wrote:
> Hi,
> I guess secuzrity is blocking your request on the other machine. YOu
> have to open the ports of SQL Server on the hosting machine that the
> client can connect to an instance. The default port of SQL Server is
> 1433, but that can be different on your machine if you changed another
> one on installation time. For more information about Windows XP SP2 and
> SQL Server read the following article.
> http://support.microsoft.com/defaul...kb;en-us;841249
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
Doh! The thing I forgot to do was... Check that TCP connections worked
directly. Turned out that all my previous succesful connections were NP
connections. So I'll be looking at the firewall then...
Cheers,
Damien

Local smtp configure with remote smtp

Hello dear
i am using windows 2003 server where i have installed smtp server
now i want to configure with remote stmp so i can send an email to any domain.

So pls tell me how it will be done.
Thanks

i think your describe credentials. admin credentials or on Report Manager

|||

HI ashok,

did you get an answer to your question? i am facing the same problem. I need to configure my local SMTP server to forward emails to another SMTP server which accepts a username and password.

did you get a solution to your question?

Local smtp configure with remote smtp

Hello dear
i am using windows 2003 server where i have installed smtp server
now i want to configure with remote stmp so i can send an email to any domain.

So pls tell me how it will be done.
Thanks

i think your describe credentials. admin credentials or on Report Manager

|||

HI ashok,

did you get an answer to your question? i am facing the same problem. I need to configure my local SMTP server to forward emails to another SMTP server which accepts a username and password.

did you get a solution to your question?

local server

maybe this can sound like a really stupid and basic question, but I dont have access to a remote server and i want to know how can I conect to a local server in my own machine.

if you expect a seepdy and accurate response , you must explain the full scenario... What version/Edition of sql server, what all are the action you have taken, whats the errror you got... pse be more descriptive...

madhu

|||

I have the same problem too. I'm Win xp pro Sp2 + Vs2002 + Vs2005 Pro. Sqlexpress 2005 is running. I want to connect with the master.mdf or create a new DB or attach an old sqlserver 2000 db as I did it with msde. Need I to install something else ? I 'm a beginner in Vs2005, in Sql server 2005 ... and english language too ! Many thank's

|||

this has been discussed in this topic ... just refer this and tell whether it solves your problem or not

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1266332&SiteID=1

Madhu

|||

I'm still Lungomare. I had to get another account because the other doesn't work anymore: not a good day... Thank you Madhu, my problem is another, a step behind: in the past i worked with VS2002 and sqlserver 2000 Enterprise Manager to project and manage my DB out from code. Must i reinstall sqlserver 2000 or any other tool ? I'd thought sqlserver express was enough... This is what i'm trying to do : VS2005, explore server, create new DB, server name : local, use win authentication, new db name: lungomare, the response is "PROVIDER NAMED PIPES : ERROR 40 ".

local server

maybe this can sound like a really stupid and basic question, but I dont have access to a remote server and i want to know how can I conect to a local server in my own machine.

if you expect a seepdy and accurate response , you must explain the full scenario... What version/Edition of sql server, what all are the action you have taken, whats the errror you got... pse be more descriptive...

madhu

|||

I have the same problem too. I'm Win xp pro Sp2 + Vs2002 + Vs2005 Pro. Sqlexpress 2005 is running. I want to connect with the master.mdf or create a new DB or attach an old sqlserver 2000 db as I did it with msde. Need I to install something else ? I 'm a beginner in Vs2005, in Sql server 2005 ... and english language too ! Many thank's

|||

this has been discussed in this topic ... just refer this and tell whether it solves your problem or not

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1266332&SiteID=1

Madhu

|||

I'm still Lungomare. I had to get another account because the other doesn't work anymore: not a good day... Thank you Madhu, my problem is another, a step behind: in the past i worked with VS2002 and sqlserver 2000 Enterprise Manager to project and manage my DB out from code. Must i reinstall sqlserver 2000 or any other tool ? I'd thought sqlserver express was enough... This is what i'm trying to do : VS2005, explore server, create new DB, server name : local, use win authentication, new db name: lungomare, the response is "PROVIDER NAMED PIPES : ERROR 40 ".

Local Reporting Services and Remote SQL Server

I am new to Reporting Services, so this may be a simple question. I want to try and download the evaluation copy of Microsoft's RS from their website, however, they require you to have a SQL Server db setup. I have one, but it is remote. I do all my development in Visual Studio on Server A and have my db setup on Server B. I want to be able to use RS within Visual Studio, so I am assuming I need to install RS on Server A. Or do I need to just install on Server B with the database (or both)?

Thanks for your help...

MS SQL Reporting Services is just an ASP.NET web application. You can install it on IIS servers that have .NET 1.1. You do not have to install it on the DB server. The MS SQL reporting services application stores its catalog in the db and thus you need to have a SQL server when installing it. The MS SQL reporting services installation has two components: the server component (to be installed on the web server) and the developer component (to be installed on developer workstation that has the Visual Studio). Using Visual Studio, you can build reports and then publish tem to the web server where you installed the MS SQL Reporting Services server component. In your case, you can install both components on Server A.

Hope this helps

Local or Remote?

Hi all - beginner here :)

Have not used Reporting Services before - used to use crystal report.

I have finally got a report working using the following code:

//ReportViewer1.Visible is set to false in design mode ReportViewer1.Visible =true;// Set the processing mode for the ReportViewer to Local ReportViewer1.ProcessingMode = ProcessingMode.Local; LocalReport rep = ReportViewer1.LocalReport; rep.ReportPath ="reports/Despatch.rdlc"; despatch desp2 =new despatch(); DataSet dtable2 = desp2.DespatchNotesByOrderID("52"); DataSet ds = dtable2;// Create a report data source for the sales order data ReportViewer1.LocalReport.DataSources.Clear(); ReportViewer1.LocalReport.DataSources.Add(new ReportDataSource("DespatchByID_despatchbyid", dtable2.Tables[0])); rep.Refresh();

This works perfectly :) However - I have just discovered that I cannot make use of the "print" button on the reportviewer when it is a local report. Can anyone tell me how I would go about using server reports for the same purpose?

Thanks in advance.

Stephen.

Hi - having looked around - I figure that I need to deploy my designed reports to my reporting server - I'm running Visual Studio 2005 Standard and SQL Server Express with Advanced Services. Any help greatly received :)

|||

This is easy this is a great article which go with u step by step

http://msdn2.microsoft.com/en-us/library/ms251712(VS.80).aspx

Local Management Studio cannot connect to Remote IS

I am not able to connect my local SQL Server Management Studio to a remote server hosting our Integration Services. I get the infamous "Access Denied". I have walked through Kirk Haselden's howto at:

http://www.sqljunkies.com/WebLog/knight_reign/archive/2006/01/05/17769.aspx

but the problem persists.

Thanks for any help.

Keehan

Well, with the help of MS support this has been figured out. The above web link is very nearly complete. The only part that I was missing that was crucial was to go to Component Services on the local machine, right click on My Computer and go to the Default Properties tab. Make sure that "Enable Distributed COM on this computer" is checked. Once I did this I was able to connect right away. So, the moral of the story, check your local DCOM settings as well as those on the server.

Now, one thing to remember, you can connect using Management Studio, but if you try to execute packages they will fail. To run packages you need to also install SSIS from say the Developers Edition on your local machine. Once these 2 things were fixed, I can connect with Management Studio and execute packages via Management Studio.

With both of these changes I am also able now to kickoff jobs via the local dtexec commandline tool.

Cheers,

Keehan

|||

I have the same problem. And, I reviewed/setting based on those documents. I still got the "Access Denied".

However, if the local user have local admin on remote SSIS server, then user will be able connect to the server.

Any input will be helpful.

James Cheng

|||

I overcome this problem by adding local user to "Distributed DCOM Users' group on SSIS server.

Thanks.

|||

Keehan,

As suggested, I have added myself and others in my group to the Distributed COM group on our web server. I still get the error below. The link below appears bad.I am trying to connect using Windows Authentication, as is is the only method available in the Connect dialog. For the Engine, either Windows or SQL Server Authentication works just fine.

---

TITLE: Connect to Server

Cannot connect to ded1368dter.maximumasp.com.

ADDITIONAL INFORMATION:

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

Connect to SSIS Service on machine "ded1368dter.maximumasp.com" failed:
The RPC server is unavailable.
.

Connect to SSIS Service on machine "ded1368dter.maximumasp.com" failed:
The RPC server is unavailable.
.

BUTTONS:

OK

---

|||

I have the same problem. What's different about our setups that we have to jump through all these hoops ? Did everyone else in the world know to add themselves to the DCOM group ? (which didn't seem to help me anyway). What account do people use on the server for the SQL services ? LocalSystem, or a special windows account ?

When I go into Surface Area Configuration, I notice that "Database Engine" and "Analysis Services" have a sub-tab for "Service" and "Remote Connections". However, "Integration Services" only has a sub-tab for Service, not Remote Connections. Is that OK ?

|||FYI Same problems... tried all the setups including manually adding the group... still "access denied" except for people that are admin on the server.|||I believe that the problem was caused by a bad file of some sort on the CD. We were using the MSDN license CDs and everything else seemed to work. Another guy in the dept had his own MSDN CD he got at a trade show, and his worked fine. When his was installed on the other developers machines, they worked fine as well ........ So maybe others have similar bad CDs ?

Local Management Studio cannot connect to Remote IS

I am not able to connect my local SQL Server Management Studio to a remote server hosting our Integration Services. I get the infamous "Access Denied". I have walked through Kirk Haselden's howto at:

http://www.sqljunkies.com/WebLog/knight_reign/archive/2006/01/05/17769.aspx

but the problem persists.

Thanks for any help.

Keehan

Well, with the help of MS support this has been figured out. The above web link is very nearly complete. The only part that I was missing that was crucial was to go to Component Services on the local machine, right click on My Computer and go to the Default Properties tab. Make sure that "Enable Distributed COM on this computer" is checked. Once I did this I was able to connect right away. So, the moral of the story, check your local DCOM settings as well as those on the server.

Now, one thing to remember, you can connect using Management Studio, but if you try to execute packages they will fail. To run packages you need to also install SSIS from say the Developers Edition on your local machine. Once these 2 things were fixed, I can connect with Management Studio and execute packages via Management Studio.

With both of these changes I am also able now to kickoff jobs via the local dtexec commandline tool.

Cheers,

Keehan

|||

I have the same problem. And, I reviewed/setting based on those documents. I still got the "Access Denied".

However, if the local user have local admin on remote SSIS server, then user will be able connect to the server.

Any input will be helpful.

James Cheng

|||

I overcome this problem by adding local user to "Distributed DCOM Users' group on SSIS server.

Thanks.

|||

Keehan,

As suggested, I have added myself and others in my group to the Distributed COM group on our web server. I still get the error below. The link below appears bad.I am trying to connect using Windows Authentication, as is is the only method available in the Connect dialog. For the Engine, either Windows or SQL Server Authentication works just fine.

---

TITLE: Connect to Server

Cannot connect to ded1368dter.maximumasp.com.

ADDITIONAL INFORMATION:

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

Connect to SSIS Service on machine "ded1368dter.maximumasp.com" failed:
The RPC server is unavailable.
.

Connect to SSIS Service on machine "ded1368dter.maximumasp.com" failed:
The RPC server is unavailable.
.

BUTTONS:

OK

---

|||

I have the same problem. What's different about our setups that we have to jump through all these hoops ? Did everyone else in the world know to add themselves to the DCOM group ? (which didn't seem to help me anyway). What account do people use on the server for the SQL services ? LocalSystem, or a special windows account ?

When I go into Surface Area Configuration, I notice that "Database Engine" and "Analysis Services" have a sub-tab for "Service" and "Remote Connections". However, "Integration Services" only has a sub-tab for Service, not Remote Connections. Is that OK ?

|||FYI Same problems... tried all the setups including manually adding the group... still "access denied" except for people that are admin on the server.|||I believe that the problem was caused by a bad file of some sort on the CD. We were using the MSDN license CDs and everything else seemed to work. Another guy in the dept had his own MSDN CD he got at a trade show, and his worked fine. When his was installed on the other developers machines, they worked fine as well ........ So maybe others have similar bad CDs ?

Local instances being used for all linked (remote) instances

I'm getting this bizarre behavior where the local instances are being used i
ntead of the remote instances I'm trying to attach to. For background sake,
I'm running on a Windows 2003 Server that is a Domain controller. The other
SQL instances are on other
systems on another domain.
On the Windows 2003 server that has this problem it can only connect to remo
te instances that have similar names and then it overrides it with its own i
nstance. For example:
2003 server domain D2:
Instance: SRV1 (default, using maching name only)
Other systems on other domain D1:
Instance: SRV2 (default, using maching name only)
SRV2\Test
SRV3
SRV3\Test2
Assume all default instances have same sa password.
In enterprise manager on SRV1 you can register remote default instances and
it seems to connect up to them but when you view the databases you realize i
t connected to the local default instance.
Now if you try to connect up to an remote instance name that isn't on the lo
cal SRV1 system or if the password is different, it can't register it.
Finally if you go on SRV2 or SRV3, there is no conflicts registering instanc
es from the other systems including SRV1.
I tried to remove/reinstall SQL (and all instances) from SRV1, but same thin
g occurred.
Any ideas?Try registring the server using the IP and port number instead of the DNS
name. See if that helps..
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"MJF" <anonymous@.discussions.microsoft.com> wrote in message
news:F7B2A2D9-5304-4B3A-8F39-04C4AB9F390B@.microsoft.com...
> I'm getting this bizarre behavior where the local instances are being used
intead of the remote instances I'm trying to attach to. For background sake,
I'm running on a Windows 2003 Server that is a Domain controller. The other
SQL instances are on other systems on another domain.
> On the Windows 2003 server that has this problem it can only connect to
remote instances that have similar names and then it overrides it with its
own instance. For example:
> 2003 server domain D2:
> Instance: SRV1 (default, using maching name only)
> Other systems on other domain D1:
> Instance: SRV2 (default, using maching name only)
> SRV2\Test
> SRV3
> SRV3\Test2
> Assume all default instances have same sa password.
> In enterprise manager on SRV1 you can register remote default instances
and it seems to connect up to them but when you view the databases you
realize it connected to the local default instance.
> Now if you try to connect up to an remote instance name that isn't on the
local SRV1 system or if the password is different, it can't register it.
> Finally if you go on SRV2 or SRV3, there is no conflicts registering
instances from the other systems including SRV1.
> I tried to remove/reinstall SQL (and all instances) from SRV1, but same
thing occurred.
> Any ideas?
>
>|||Also look at the SQL Client Network Utility on the SRV1 server and see if
there is an alias that points to SRV1. It could be using that alias when
you rgister the other SRV1 servers. If there is one there, remove it and
see what happnes. You should not need an alias on the actual server itself.
Rand
This posting is provided "as is" with no warranties and confers no rights.

Local copy of Remote Database?

Is there are direct and simply way to backup a database hosted on a
remote machine to my local workstation without involving sharing out
the hardware(eg: A:\ or some folder in local hdd) or even creating new
accounts which has the admin rights?If you are taking a backup, the backup path is always related to the
server, so you either can backup on a local drive OR backup the data on
a network share (UNC) where both machines have access to. Another copy
of a local database can be done using the DTS wizrad and copying all
objects and data via SQL Server objects copy.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--|||You can use net use to map a drive and use it for backup.
Regards
Amish Shah

Local copy of Remote Database?

Is there are direct and simply way to backup a database hosted on a
remote machine to my local workstation without involving sharing out
the hardware(eg: A:\ or some folder in local hdd) or even creating new
accounts which has the admin rights?If you are taking a backup, the backup path is always related to the
server, so you either can backup on a local drive OR backup the data on
a network share (UNC) where both machines have access to. Another copy
of a local database can be done using the DTS wizrad and copying all
objects and data via SQL Server objects copy.
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--|||You can use net use to map a drive and use it for backup.
Regards
Amish Shah

Local and Remote Index Servers returning different resultsets

I have a project that uses a linked server in SQL Server 2000 to query a
local Index Server. This works fine.
I've been experimenting with moving the catalog to a remote Index Server and
querying it via Index Server running on the SQL Server machine (using the
four-part SCOPE syntax). I've followed plenty of other pointers relating to
syntax and security and seem to be getting there, but have noticed some
strange behaviour.
I seem to be getting a different number of results returned from the local
and remote index servers when I query them through Query Analyzer. My test
queries (which run over a catalog of about 12,000 documents) are very
simple:
select * from openquery (ImpelHR, 'select filename from
"NTS-1".ImpelHR..scope()')
This returns 12,012 results from NTS-1 (where the Index Server actually is)
and only 9,915 results when run from NTS-2 (where the SQL Server instance
and linked server are set up).
As soon as I add a CONTAINS clause:
select * from openquery (ImpelHR, 'select filename from
"NTS-1".ImpelHR..scope() where contains(''microsoft'')')
...I get 2,691 results on NTS-1 and no results on NTS-2. Changing the search
term makes no difference - no results (or errors) are returned from NTS-2 as
soon as I add the CONTAINS clause.
The two machines are setup very similarly - two Win2K Servers with SQL
Server 2000 Ent. Ed. Could the database and collation settings of the linked
server be acting up? Does anyone know what this strangeness is due to? I
seem to be passed all the permissions problems but this one is weird.
Thanks,
Alan
NZ! I used to live in New Plymouth!
results are trimmed by security and scope. You have to verify that
1) you are using the same catalogs
2) the account you are querying with in SQL Server is present on the remote
server and on both servers they have identical rights
3) the content is the same on both servers
4) the default scope (\) is the same on both servers. To verify this open up
ciadv.msc and expand your default catalog. expand the directories folder and
ensure the directories which show up there are the same and they are both
physical, ie don't have globes on them
5) when you are not doing a contains, you are basically enumerating the file
system on the local and remote servers. If AllowEnumeration is turned off,
this will account for this behavior
6) try something like this
select * from openquery (ImpelHR, 'select filename from
"NTS-1".ImpelHR..scope(''shallow traversal of ""c:\""'')')
thats scope ( ' ' deep traversal of " " c:\ " " ' ' ) ' )
I think your problems are due to different content and probably that the
index is not finished building on one or more servers.
BTW - are all IS servers on the same version? IE is one on IS 2 (NT 4.0) and
the other on IS 3.x?
"Alan Howard" <Xalan.howardX@.Xparadise.net.nzX> wrote in message
news:%23iKyiIYaEHA.2840@.TK2MSFTNGP11.phx.gbl...
> I have a project that uses a linked server in SQL Server 2000 to query a
> local Index Server. This works fine.
> I've been experimenting with moving the catalog to a remote Index Server
and
> querying it via Index Server running on the SQL Server machine (using the
> four-part SCOPE syntax). I've followed plenty of other pointers relating
to
> syntax and security and seem to be getting there, but have noticed some
> strange behaviour.
> I seem to be getting a different number of results returned from the local
> and remote index servers when I query them through Query Analyzer. My test
> queries (which run over a catalog of about 12,000 documents) are very
> simple:
> select * from openquery (ImpelHR, 'select filename from
> "NTS-1".ImpelHR..scope()')
> This returns 12,012 results from NTS-1 (where the Index Server actually
is)
> and only 9,915 results when run from NTS-2 (where the SQL Server instance
> and linked server are set up).
> As soon as I add a CONTAINS clause:
> select * from openquery (ImpelHR, 'select filename from
> "NTS-1".ImpelHR..scope() where contains(''microsoft'')')
> ...I get 2,691 results on NTS-1 and no results on NTS-2. Changing the
search
> term makes no difference - no results (or errors) are returned from NTS-2
as
> soon as I add the CONTAINS clause.
> The two machines are setup very similarly - two Win2K Servers with SQL
> Server 2000 Ent. Ed. Could the database and collation settings of the
linked
> server be acting up? Does anyone know what this strangeness is due to? I
> seem to be passed all the permissions problems but this one is weird.
> Thanks,
> Alan
>
|||Hi Alan,
can you share the "..plenty of other pointers relating to syntax and security.."
to set up the configuration you used: Index Server and SQLServer on different
machines ?
Many guys (including me..) posted in this group questions on the argument, but
got no aswer..
Many THX,
Max
"Alan Howard" wrote:

> I have a project that uses a linked server in SQL Server 2000 to query a
> local Index Server. This works fine.
> I've been experimenting with moving the catalog to a remote Index Server and
> querying it via Index Server running on the SQL Server machine (using the
> four-part SCOPE syntax). I've followed plenty of other pointers relating to
> syntax and security and seem to be getting there, but have noticed some
> strange behaviour.
> I seem to be getting a different number of results returned from the local
> and remote index servers when I query them through Query Analyzer. My test
> queries (which run over a catalog of about 12,000 documents) are very
> simple:
> select * from openquery (ImpelHR, 'select filename from
> "NTS-1".ImpelHR..scope()')
> This returns 12,012 results from NTS-1 (where the Index Server actually is)
> and only 9,915 results when run from NTS-2 (where the SQL Server instance
> and linked server are set up).
> As soon as I add a CONTAINS clause:
> select * from openquery (ImpelHR, 'select filename from
> "NTS-1".ImpelHR..scope() where contains(''microsoft'')')
> ...I get 2,691 results on NTS-1 and no results on NTS-2. Changing the search
> term makes no difference - no results (or errors) are returned from NTS-2 as
> soon as I add the CONTAINS clause.
> The two machines are setup very similarly - two Win2K Servers with SQL
> Server 2000 Ent. Ed. Could the database and collation settings of the linked
> server be acting up? Does anyone know what this strangeness is due to? I
> seem to be passed all the permissions problems but this one is weird.
> Thanks,
> Alan
>
>

Local and Remote Connections question

Hello all,

We are currently testing all our applications on Vista and SQL Server 2005. Everything is running and working, but our applications that connect to SQL Server 2005 are running very slow. We have our client installed on a vista pc running SQL Server 2005 on the same pc. I was trying to troubleshoot the problem and realized that we had "Local connections only" checked in the Surface Area Configuration tool, and changed it to "Local and remote connections". After making this change all our apps seem to be running much faster. I just didn't expect this to be the problem since we were connecting from the same computer that is running the server. Can anyone explain to me why this has made the difference in performance?

Thanks for your time and attention,

DrewV

Hi DrewV,

How many connections were you attempting to sql server through your application(s).

How much memory do you have on the box and how much memory is configured for SQL Server?

regards

Jag

|||

There is only one connection being opened by our app.

The machine is a brand new Dell Precision 390 with 2GB or ram.

We are running SQL Express which I think uses a max of 1GB.

It is almost like it is trying to connect with a "remote" connection and trying locally after the first attempt fails. I just don't understand why since we are running on the same machine.

|||

I'm only guessing here, but the local connections system generally uses shared memory to pass the information between the client and server avoiding the network protocols. However this requires an area of memory which is accessible to both processes.

It may be that this is seen as a security weakpoint in Vista (direct access to non-process specific memory ... hmmm) so that there is a significant amount of checking and policing of such access which did not occur before and so slows down the whole process. The remote connection goes through the network stack and so is probably considered less of a vulnerability and so does not need the extra control.

Maybe SQL Server 2005 prefers on Vista to use the tcp/ip stack (if available) rather than shared memory (because it will be faster) - but this is only possible if you enable remote as well as local connections.

Also you don't say which service pack of SQL Server 2005 you are using.

|||Thanks for your response. We are on Service Pack 2.

Local Admin

If you start your SQL services with local admin and you have a process that
uses xp_cmdshell to get files based upon date on a remote server. Can you do
that?
What permission will I need to apply on the remote server folders or from
within the Sql2005 box issuing the xp_cmdshell request.
Thanks.First, you need to enable xp_cmdshell by using sp_configure (it is disabled
by default for security reasons).
Second, you need to give permissions on the remote shared folder to the
account used to run the SQL Server service. Usually this should be a Windows
domain account so it can access resources on the network.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"AHartman" wrote:
> If you start your SQL services with local admin and you have a process that
> uses xp_cmdshell to get files based upon date on a remote server. Can you do
> that?
> What permission will I need to apply on the remote server folders or from
> within the Sql2005 box issuing the xp_cmdshell request.
>
> Thanks.
>|||No. Local admin won't have the required permissions for remote system
access. You will need to use a domain level account for the sql services
logins and give that account appropriate access.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"AHartman" <Hoosbruin@.Kconline.com> wrote in message
news:0PmdnSu0MdRCWNHanZ2dnUVZ_gGdnZ2d@.kconline.com...
> If you start your SQL services with local admin and you have a process
> that uses xp_cmdshell to get files based upon date on a remote server. Can
> you do that?
> What permission will I need to apply on the remote server folders or from
> within the Sql2005 box issuing the xp_cmdshell request.
>
> Thanks.|||In addition to the other posts:
If the login who executes xp_cmdshell isn't sysadmin you also need to define a proxy account.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"AHartman" <Hoosbruin@.Kconline.com> wrote in message
news:0PmdnSu0MdRCWNHanZ2dnUVZ_gGdnZ2d@.kconline.com...
> If you start your SQL services with local admin and you have a process that uses xp_cmdshell to
> get files based upon date on a remote server. Can you do that?
> What permission will I need to apply on the remote server folders or from within the Sql2005 box
> issuing the xp_cmdshell request.
>
> Thanks.

Local Admin

If you start your SQL services with local admin and you have a process that
uses xp_cmdshell to get files based upon date on a remote server. Can you do
that?
What permission will I need to apply on the remote server folders or from
within the Sql2005 box issuing the xp_cmdshell request.
Thanks.First, you need to enable xp_cmdshell by using sp_configure (it is disabled
by default for security reasons).
Second, you need to give permissions on the remote shared folder to the
account used to run the SQL Server service. Usually this should be a Windows
domain account so it can access resources on the network.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"AHartman" wrote:

> If you start your SQL services with local admin and you have a process tha
t
> uses xp_cmdshell to get files based upon date on a remote server. Can you
do
> that?
> What permission will I need to apply on the remote server folders or from
> within the Sql2005 box issuing the xp_cmdshell request.
>
> Thanks.
>|||No. Local admin won't have the required permissions for remote system
access. You will need to use a domain level account for the sql services
logins and give that account appropriate access.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"AHartman" <Hoosbruin@.Kconline.com> wrote in message
news:0PmdnSu0MdRCWNHanZ2dnUVZ_gGdnZ2d@.kc
online.com...
> If you start your SQL services with local admin and you have a process
> that uses xp_cmdshell to get files based upon date on a remote server. Can
> you do that?
> What permission will I need to apply on the remote server folders or from
> within the Sql2005 box issuing the xp_cmdshell request.
>
> Thanks.|||In addition to the other posts:
If the login who executes xp_cmdshell isn't sysadmin you also need to define
a proxy account.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"AHartman" <Hoosbruin@.Kconline.com> wrote in message
news:0PmdnSu0MdRCWNHanZ2dnUVZ_gGdnZ2d@.kc
online.com...
> If you start your SQL services with local admin and you have a process tha
t uses xp_cmdshell to
> get files based upon date on a remote server. Can you do that?
> What permission will I need to apply on the remote server folders or from
within the Sql2005 box
> issuing the xp_cmdshell request.
>
> Thanks.

Local Admin

If you start your SQL services with local admin and you have a process that
uses xp_cmdshell to get files based upon date on a remote server. Can you do
that?
What permission will I need to apply on the remote server folders or from
within the Sql2005 box issuing the xp_cmdshell request.
Thanks.
First, you need to enable xp_cmdshell by using sp_configure (it is disabled
by default for security reasons).
Second, you need to give permissions on the remote shared folder to the
account used to run the SQL Server service. Usually this should be a Windows
domain account so it can access resources on the network.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"AHartman" wrote:

> If you start your SQL services with local admin and you have a process that
> uses xp_cmdshell to get files based upon date on a remote server. Can you do
> that?
> What permission will I need to apply on the remote server folders or from
> within the Sql2005 box issuing the xp_cmdshell request.
>
> Thanks.
>
|||No. Local admin won't have the required permissions for remote system
access. You will need to use a domain level account for the sql services
logins and give that account appropriate access.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"AHartman" <Hoosbruin@.Kconline.com> wrote in message
news:0PmdnSu0MdRCWNHanZ2dnUVZ_gGdnZ2d@.kconline.com ...
> If you start your SQL services with local admin and you have a process
> that uses xp_cmdshell to get files based upon date on a remote server. Can
> you do that?
> What permission will I need to apply on the remote server folders or from
> within the Sql2005 box issuing the xp_cmdshell request.
>
> Thanks.

Local 2K5 server & Remote 2K server

Hi,
I was wondering what the issues I might face if I were to upgrade my
local SQL server to 2005 while my remote hosting server is still SQL
2000. Are there huge differences in formats of dbs, tables, jobs, etc
or is it largely UI & feature enhancements?
There isn't a great deal of interation between the servers, but I would
like to be able to, for instance, continue exporting a nightly copy
down to my local server and it being useable without too having to
re-jig things too much.
Thanks for any help you can lend,
Mick
Mick
I have installed on my pc sql server 2005 and linked/connected to sql server
2000 . By saying "nightly copy" dod you mean a backup of the database and
then restore in on local server. It is also possible.
<mick.hodgson@.gmail.com> wrote in message
news:1162982342.259348.291440@.m73g2000cwd.googlegr oups.com...
> Hi,
> I was wondering what the issues I might face if I were to upgrade my
> local SQL server to 2005 while my remote hosting server is still SQL
> 2000. Are there huge differences in formats of dbs, tables, jobs, etc
> or is it largely UI & feature enhancements?
> There isn't a great deal of interation between the servers, but I would
> like to be able to, for instance, continue exporting a nightly copy
> down to my local server and it being useable without too having to
> re-jig things too much.
> Thanks for any help you can lend,
> Mick
>
|||Thanks Uri,
It is a nightly DTS Export which copies the database objects down to
the local server.
Do you have the full 2005 database engine on your PC or just the client
tools? I need to know i can develop on a SQL2005 db and it will work
when uploaded to the SQL2000 db.
Thanks again!
Mick
Uri Dimant wrote:[vbcol=seagreen]
> Mick
> I have installed on my pc sql server 2005 and linked/connected to sql server
> 2000 . By saying "nightly copy" dod you mean a backup of the database and
> then restore in on local server. It is also possible.
>
> <mick.hodgson@.gmail.com> wrote in message
> news:1162982342.259348.291440@.m73g2000cwd.googlegr oups.com...
|||Mick
> Do you have the full 2005 database engine on your PC or just the client
> tools? I need to know i can develop on a SQL2005 db and it will work
> when uploaded to the SQL2000 db.
Yes, I do. Using DTS/SSIS to move the data is a headache ,epsecially
between 2000/2005.
Most likely to backup database on the sql server 2000 and restore on sql
server 2005
<mick.hodgson@.gmail.com> wrote in message
news:1162986618.135708.111690@.m7g2000cwm.googlegro ups.com...
> Thanks Uri,
> It is a nightly DTS Export which copies the database objects down to
> the local server.
> Do you have the full 2005 database engine on your PC or just the client
> tools? I need to know i can develop on a SQL2005 db and it will work
> when uploaded to the SQL2000 db.
> Thanks again!
> Mick
> Uri Dimant wrote:
>