Remotely accessing database

I'm developing a VB app to manipulate data in a table. I've created an SQL server (local) and have successfully connected to and manipulated data in a database that I created. I would like to be able to run the app on another machine and connect to the same server. I'm having trouble determining what the server name is. Tried a few things but get the "server does not exist" message. Server is set to authenticate SQL server and windows.

Is this happening because my server is (local)? If so how do I change it to permit remote access?

Any assistance would be appreciated.if you have only one sql server in that box, than the default name of the sql server is the name of the windows box. when you try to connect from another box, make sure that the client box has sql server drivers, create a DSN that point to the SQL Server box (using windows server name or ip address), every thing should go fine from there.|||C-Nag

Thanks! I thought there was a simple solution. I'm more familiear with MSAccess and should of thought of this. With access it would require the same.sql

Remote Stored Procedure exec from variable

Hello Folks,

I have a server that is linked to several other servers. I have been able to successfully execute a procedure manually to each from the common server by executing this:

exec server1.dbname.owner.procedure
go
exec server2.dbname.owner.procedure
go
exec server3.dbname.owner.procedure
go

While this is ok, I'd like to wrap this in a loop to execute the procedure but switch out the server name in each iteration (something like this):

while @.variable is not null
begin
select @.server = 'change name in loop'
select @.str = @.server+'.dbname.owner.procedure'
exec @.str
end

This unfortunately does not work. The execute is acting like the server portion of the name does not exist (defaulting to local). I have attempted to use the AT SERVERNAME syntax in a similar fashion and been unsuccessful.

Is there some way I could dynamically create the four part name and execute it?

Any assistance would be greatly appreciated.

Thanks, Mark

DECLARE @.server nvarchar(128)

DECLARE @.cmd nvarchar(1000)

SET @.server = 'MyServer'

SET @.cmd = 'EXEC ' + @.server + '.MyDatabase.MySchema.MySproc'

EXEC (@.cmd)

remote server and local server - distributed queries?

I have a remote server that I'm able to successfully register on a local sql
2000 server through EntMgr, and make successful selects on both remote and
local server databases via odbc.
Here's the question - Can I have a local server with some database/tables, a
remote server with some database2/tables, and make a single select statement
using table relationships from each? Without having to use DTS to make
imports/exports into the local server's database?
It seems that the connection object is specific to a server, but is there a
way to handle the reference to the remote server's database through a
database in the local server so that the connection object thinks
everything's local and can remain one-database-server-specific?
Sorry if I've communicated this poorly. I'd appreciate any responses.
Yes...you can do this.
A query can include tables on a linked server by using 4
part naming conventions:
server.database(or catalog).owner(or schema).table
You can also use some of the other distributed query
functions such as Openquery to do this.
-Sue
On Thu, 10 Feb 2005 08:55:03 -0800, janetb
<janetb@.discussions.microsoft.com> wrote:

>I have a remote server that I'm able to successfully register on a local sql
>2000 server through EntMgr, and make successful selects on both remote and
>local server databases via odbc.
>Here's the question - Can I have a local server with some database/tables, a
>remote server with some database2/tables, and make a single select statement
>using table relationships from each? Without having to use DTS to make
>imports/exports into the local server's database?
>It seems that the connection object is specific to a server, but is there a
>way to handle the reference to the remote server's database through a
>database in the local server so that the connection object thinks
>everything's local and can remain one-database-server-specific?
>Sorry if I've communicated this poorly. I'd appreciate any responses.

remote server and local server - distributed queries?

I have a remote server that I'm able to successfully register on a local sql
2000 server through EntMgr, and make successful selects on both remote and
local server databases via odbc.
Here's the question - Can I have a local server with some database/tables, a
remote server with some database2/tables, and make a single select statement
using table relationships from each? Without having to use DTS to make
imports/exports into the local server's database?
It seems that the connection object is specific to a server, but is there a
way to handle the reference to the remote server's database through a
database in the local server so that the connection object thinks
everything's local and can remain one-database-server-specific?
Sorry if I've communicated this poorly. I'd appreciate any responses.Yes...you can do this.
A query can include tables on a linked server by using 4
part naming conventions:
server.database(or catalog).owner(or schema).table
You can also use some of the other distributed query
functions such as Openquery to do this.
-Sue
On Thu, 10 Feb 2005 08:55:03 -0800, janetb
<janetb@.discussions.microsoft.com> wrote:

>I have a remote server that I'm able to successfully register on a local sq
l
>2000 server through EntMgr, and make successful selects on both remote and
>local server databases via odbc.
>Here's the question - Can I have a local server with some database/tables,
a
>remote server with some database2/tables, and make a single select statemen
t
>using table relationships from each? Without having to use DTS to make
>imports/exports into the local server's database?
>It seems that the connection object is specific to a server, but is there a
>way to handle the reference to the remote server's database through a
>database in the local server so that the connection object thinks
>everything's local and can remain one-database-server-specific?
>Sorry if I've communicated this poorly. I'd appreciate any responses.

Remote Restore via Linked Server

Hi, I'm initiating a restore via a linked server and an SQL Agent Job. The
job completes successfully in about 10 minutes, but the backup is left in a
loading state. This suggests that the connection timed out. Any thoughts
on how to make this more robust? (possibly start an SQL Agent job on the
remote server from the parent server')
Thanks
Bill"Bill Swartz" <swartz.bill@.gmail.com> wrote in message
news:ueVmqUqrGHA.1732@.TK2MSFTNGP03.phx.gbl...
> Hi, I'm initiating a restore via a linked server and an SQL Agent Job.
> The job completes successfully in about 10 minutes, but the backup is left
> in a loading state. This suggests that the connection timed out. Any
> thoughts on how to make this more robust? (possibly start an SQL Agent
> job on the remote server from the parent server')
>
That's what I would do.
David|||Answering my own question to some degree, initiating the remote procedure
via an SQL Agent job on the remote server works fine. (or in other words it
does not time out). But, it also flags the step on the host server
immediately as successful.
What I'm looking for is a way for the job to wait, or at least report the
status of the restore back to the host server.
Bill
"Bill Swartz" <swartz.bill@.gmail.com> wrote in message
news:ueVmqUqrGHA.1732@.TK2MSFTNGP03.phx.gbl...
> Hi, I'm initiating a restore via a linked server and an SQL Agent Job.
> The job completes successfully in about 10 minutes, but the backup is left
> in a loading state. This suggests that the connection timed out. Any
> thoughts on how to make this more robust? (possibly start an SQL Agent
> job on the remote server from the parent server')
> Thanks
> Bill
>|||This is a multi-part message in MIME format.
--050606090705070603040506
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit
Bill S wrote:
> Answering my own question to some degree, initiating the remote procedure
> via an SQL Agent job on the remote server works fine. (or in other words it
> does not time out). But, it also flags the step on the host server
> immediately as successful.
> What I'm looking for is a way for the job to wait, or at least report the
> status of the restore back to the host server.
> Bill
> "Bill Swartz" <swartz.bill@.gmail.com> wrote in message
> news:ueVmqUqrGHA.1732@.TK2MSFTNGP03.phx.gbl...
>> Hi, I'm initiating a restore via a linked server and an SQL Agent Job.
>> The job completes successfully in about 10 minutes, but the backup is left
>> in a loading state. This suggests that the connection timed out. Any
>> thoughts on how to make this more robust? (possibly start an SQL Agent
>> job on the remote server from the parent server')
>> Thanks
>> Bill
>>
>
>
Hi
I've done a similar thing with a DTS package. In the package there's a
connection to the remote server and then a step that runs a restore on
that server. The package will not finish the execution until the restore
is done so that will give you what you want.
Regards
Steen Schlüter Persson
Databaseadministrator / Systemadministrator
--050606090705070603040506
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
Bill S wrote:
<blockquote cite="mid%237WfcfqrGHA.1140@.TK2MSFTNGP05.phx.gbl"
type="cite">
<pre wrap="">Answering my own question to some degree, initiating the remote procedure
via an SQL Agent job on the remote server works fine. (or in other words it
does not time out). But, it also flags the step on the host server
immediately as successful.
What I'm looking for is a way for the job to wait, or at least report the
status of the restore back to the host server.
Bill
"Bill Swartz" <a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=mailto:swartz.bill@.gmail.com"><swartz.bill@.gmail.com></a> wrote in message
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=news:ueVmqUqrGHA.1732@.TK2MSFTNGP03.phx.gbl">news:ueVmqUqrGHA.1732@.TK2MSFTNGP03.phx.gbl</a>...
</pre>
<blockquote type="cite">
<pre wrap="">Hi, I'm initiating a restore via a linked server and an SQL Agent Job.
The job completes successfully in about 10 minutes, but the backup is left
in a loading state. This suggests that the connection timed out. Any
thoughts on how to make this more robust? (possibly start an SQL Agent
job on the remote server from the parent server')
Thanks
Bill
</pre>
</blockquote>
<pre wrap=""><!-->
</pre>
</blockquote>
<font size="-1"><font face="Arial">Hi<br>
<br>
I've done a similar thing with a DTS package. In the package there's a
connection to the remote server and then a step that runs a restore on
that server. The package will not finish the execution until the
restore is done so that will give you what you want.<br>
<br>
<br>
-- <br>
Regards<br>
Steen Schlüter Persson<br>
Databaseadministrator / Systemadministrator<br>
</font></font>
</body>
</html>
--050606090705070603040506--

Remote Restore via Linked Server

Hi, I'm initiating a restore via a linked server and an SQL Agent Job. The
job completes successfully in about 10 minutes, but the backup is left in a
loading state. This suggests that the connection timed out. Any thoughts
on how to make this more robust? (possibly start an SQL Agent job on the
remote server from the parent server')
Thanks
Bill"Bill Swartz" <swartz.bill@.gmail.com> wrote in message
news:ueVmqUqrGHA.1732@.TK2MSFTNGP03.phx.gbl...
> Hi, I'm initiating a restore via a linked server and an SQL Agent Job.
> The job completes successfully in about 10 minutes, but the backup is left
> in a loading state. This suggests that the connection timed out. Any
> thoughts on how to make this more robust? (possibly start an SQL Agent
> job on the remote server from the parent server')
>
That's what I would do.
David|||Answering my own question to some degree, initiating the remote procedure
via an SQL Agent job on the remote server works fine. (or in other words it
does not time out). But, it also flags the step on the host server
immediately as successful.
What I'm looking for is a way for the job to wait, or at least report the
status of the restore back to the host server.
Bill
"Bill Swartz" <swartz.bill@.gmail.com> wrote in message
news:ueVmqUqrGHA.1732@.TK2MSFTNGP03.phx.gbl...
> Hi, I'm initiating a restore via a linked server and an SQL Agent Job.
> The job completes successfully in about 10 minutes, but the backup is left
> in a loading state. This suggests that the connection timed out. Any
> thoughts on how to make this more robust? (possibly start an SQL Agent
> job on the remote server from the parent server')
> Thanks
> Bill
>|||Bill S wrote:
> Answering my own question to some degree, initiating the remote procedure
> via an SQL Agent job on the remote server works fine. (or in other words
it
> does not time out). But, it also flags the step on the host server
> immediately as successful.
> What I'm looking for is a way for the job to wait, or at least report the
> status of the restore back to the host server.
> Bill
> "Bill Swartz" <swartz.bill@.gmail.com> wrote in message
> news:ueVmqUqrGHA.1732@.TK2MSFTNGP03.phx.gbl...
>
>
>
Hi
I've done a similar thing with a DTS package. In the package there's a
connection to the remote server and then a step that runs a restore on
that server. The package will not finish the execution until the restore
is done so that will give you what you want.
Regards
Steen Schlter Persson
Databaseadministrator / Systemadministrator

Remote connection not working using MSSMSE

Im getting quite desperate and frustrated!! I have installed SQL 2005 Express successfully and can connect to it fine using Management Studio locally.

If I try and connect remotely using Management Studio Im getting the old Error 10061 : The machine actively refused the connection!

I have set it up exactly as per all documentation and help from the forums. Remote connections is on TCP and Named Pipes, I even have the firewall disabled.

Im running it on Windows Server 2003 Standard.

Any help would be really apriciated as i have tried everything!!!

Is SQL Server Browser Service started properly ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||Yes I have the browser service running. Does it have any settings that need changing?|||

It would appear that the port is blocked or the server is not listening to 1433. I cant telnet to the port. How would I go about opening that up?

Cheers

|||If Browser is on and Firewall is off, your connection should work. Can you check your server's errorlog and make sure server is listening on some TCP port? One more you can check is to see if there is any outdated alias defined for yourserver\sqlexpress on your client machine? This could translate a connection string. Using Sql Server Configuration Manager and/or c:\windows\system32\cliconfg.exe to check alias.|||This is sqlexpress, most likely, it's not listening on 1433. Check your errorlog to find out which port it's listening on.|||

I checked the errorlog file and found this entry:

Server is listening on [ 'any' <ipv4> 1833].

Could that be anything?

P.S. Sorry, im totally new to SQL Server!

|||

The server is listening on port 1833, and it's normal. Try "telnet yourmachine 1833" and make you can connect to the port.

Sql browser should help you find the port number. If firewall is on, make sure UDP 1434 and TCP 1833 is unblocked. Did you check the alias?

|||Just in addition to Xinwei, the SQL Server browser will redirect request of clients to the appropiate port on SQL Server. In versions prior SQL 2k5 you had to know which port was held by an appropiate instance of SQL Server making your connecting string look like (as part of the server name) Servername\InstanceName,portnumber. But SQL Server Browser does something more for you, as from the

http://msdn2.microsoft.com/en-us/library/ms165724.aspx

"When SQL Server 2000 and SQL Server 2005 clients request SQL Server resources, the client network library sends a UDP message to the server using port 1434. SQL Server Browser responds with the TCP/IP port or named pipe of the requested instance. The network library on the client application then completes the connection by sending a request to the server using the port or named pipe of the desired instance."

Look at this link which describes also how to configure the firewall on the server.

HTH, Jens K. Suessmeyer.

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

Xinwei: I can successfully telnet to port 1833 remotely.

In the configuration manager > SQL Native Client Configuration > Aliases - there is nothing. Is this the alias you mean?

Oh and those ports are unblocked.

Should I be able to telnet 1434?

Jens: I have check that link about setting up the firewall and it is setup exactly like this yet I still have the problem!! :(

|||

It's good that you can telnet to 1833. 1434 is UDP port, you cannot telnet to. Can you also try c:\windows\system32\cliconfg.exe to check the alias there, which is for MDAC. The alias you checked is for SNAC. I'm not sure which driver you are using, so I asked you to check both. By MSSMSE, do you mean MS Sql Managenet Studio or other app?

When you ping you servername, the IP was resolved correctly. right? Can you confirm SQL Browser is on and UDP port 1434 is in firewall exception? Thanks

|||

There is nothing in the ALIAS page of cliconfig! Is that correct?

Yes, Im trying to connect via Management Studio. SQL Browser is on and UDP port 1434 UDP is an exception.

I really appreciate you trying to help me as this is driving me crazy!!!!

By the way, this is a fresh installation of SQL Server Express 2005 on a fresh installation of Windows Server 2003 standard. Surely that should have gone real smooth!

|||I hope you were not confused. The alias should be checked in the client, while browser and firewall stuff should be done on the server. Can you try connect using IP address directly? and also type "xxx.xx.xx.xx, 1833" as the server name?|||

THANK YOU, THANK YOU, THANK YOU.

Im in! I checked the client config util and there were 2 entries in there so i removed them! I then connected using xx.xx.xx.xx, 1833 as you suggested and i got straight in!!!!!!

That super thank you so much. But why do you think that is? Why do i have to specify the port 1833 like you said - i have never had that problem connecting to other servers?

|||Good to know it works. xx.xx.xx.xx, 1833 should work even if you do not remove the alias. Once you remove the alias, you should be able to connect the server using the servername without port number. SQL browser will help you find the port number 1833. If the port number is changed(1833 is dynamic port number), sql browser can still help you find the new port number.

Remote connection not working using MSSMSE

Im getting quite desperate and frustrated!! I have installed SQL 2005 Express successfully and can connect to it fine using Management Studio locally.

If I try and connect remotely using Management Studio Im getting the old Error 10061 : The machine actively refused the connection!

I have set it up exactly as per all documentation and help from the forums. Remote connections is on TCP and Named Pipes, I even have the firewall disabled.

Im running it on Windows Server 2003 Standard.

Any help would be really apriciated as i have tried everything!!!

Is SQL Server Browser Service started properly ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||Yes I have the browser service running. Does it have any settings that need changing?|||

It would appear that the port is blocked or the server is not listening to 1433. I cant telnet to the port. How would I go about opening that up?

Cheers

|||If Browser is on and Firewall is off, your connection should work. Can you check your server's errorlog and make sure server is listening on some TCP port? One more you can check is to see if there is any outdated alias defined for yourserver\sqlexpress on your client machine? This could translate a connection string. Using Sql Server Configuration Manager and/or c:\windows\system32\cliconfg.exe to check alias.|||This is sqlexpress, most likely, it's not listening on 1433. Check your errorlog to find out which port it's listening on.|||

I checked the errorlog file and found this entry:

Server is listening on [ 'any' <ipv4> 1833].

Could that be anything?

P.S. Sorry, im totally new to SQL Server!

|||

The server is listening on port 1833, and it's normal. Try "telnet yourmachine 1833" and make you can connect to the port.

Sql browser should help you find the port number. If firewall is on, make sure UDP 1434 and TCP 1833 is unblocked. Did you check the alias?

|||Just in addition to Xinwei, the SQL Server browser will redirect request of clients to the appropiate port on SQL Server. In versions prior SQL 2k5 you had to know which port was held by an appropiate instance of SQL Server making your connecting string look like (as part of the server name) Servername\InstanceName,portnumber. But SQL Server Browser does something more for you, as from the

http://msdn2.microsoft.com/en-us/library/ms165724.aspx

"When SQL Server 2000 and SQL Server 2005 clients request SQL Server resources, the client network library sends a UDP message to the server using port 1434. SQL Server Browser responds with the TCP/IP port or named pipe of the requested instance. The network library on the client application then completes the connection by sending a request to the server using the port or named pipe of the desired instance."

Look at this link which describes also how to configure the firewall on the server.

HTH, Jens K. Suessmeyer.

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

Xinwei: I can successfully telnet to port 1833 remotely.

In the configuration manager > SQL Native Client Configuration > Aliases - there is nothing. Is this the alias you mean?

Oh and those ports are unblocked.

Should I be able to telnet 1434?

Jens: I have check that link about setting up the firewall and it is setup exactly like this yet I still have the problem!! :(

|||

It's good that you can telnet to 1833. 1434 is UDP port, you cannot telnet to. Can you also try c:\windows\system32\cliconfg.exe to check the alias there, which is for MDAC. The alias you checked is for SNAC. I'm not sure which driver you are using, so I asked you to check both. By MSSMSE, do you mean MS Sql Managenet Studio or other app?

When you ping you servername, the IP was resolved correctly. right? Can you confirm SQL Browser is on and UDP port 1434 is in firewall exception? Thanks

|||

There is nothing in the ALIAS page of cliconfig! Is that correct?

Yes, Im trying to connect via Management Studio. SQL Browser is on and UDP port 1434 UDP is an exception.

I really appreciate you trying to help me as this is driving me crazy!!!!

By the way, this is a fresh installation of SQL Server Express 2005 on a fresh installation of Windows Server 2003 standard. Surely that should have gone real smooth!

|||I hope you were not confused. The alias should be checked in the client, while browser and firewall stuff should be done on the server. Can you try connect using IP address directly? and also type "xxx.xx.xx.xx, 1833" as the server name?|||

THANK YOU, THANK YOU, THANK YOU.

Im in! I checked the client config util and there were 2 entries in there so i removed them! I then connected using xx.xx.xx.xx, 1833 as you suggested and i got straight in!!!!!!

That super thank you so much. But why do you think that is? Why do i have to specify the port 1833 like you said - i have never had that problem connecting to other servers?

|||Good to know it works. xx.xx.xx.xx, 1833 should work even if you do not remove the alias. Once you remove the alias, you should be able to connect the server using the servername without port number. SQL browser will help you find the port number 1833. If the port number is changed(1833 is dynamic port number), sql browser can still help you find the new port number.