Activating Server Logs

I have two remote Win2000 servers running SQL2000. There are no items under
SQL Server Logs in Ent. Mgr. How do I conjfigure the server to log database
activity?
Thanks,
John Steen
remove (nospam) from e-mail address to send me a message
Never heard we must activate something for the log. What do you see in
\Program Files\Microsoft SQL Server\MSSQL\LOG folder? There should be files
named ERRORLOG.
"John Steen" <moderndads(nospam)@.hotmail.com> wrote in message
news:09D2F211-886A-4904-8B56-1471835E0C04@.microsoft.com...
>I have two remote Win2000 servers running SQL2000. There are no items
>under
> SQL Server Logs in Ent. Mgr. How do I conjfigure the server to log
> database
> activity?
> Thanks,
> John Steen
> remove (nospam) from e-mail address to send me a message
|||Hi
SQL Server does not log activity like inserts and deletes. You need to run
SQL Server Profiler to do that.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"ME" <ME@.hotmail.com> wrote in message
news:eIj85KVYFHA.3164@.TK2MSFTNGP12.phx.gbl...
> Never heard we must activate something for the log. What do you see in
> \Program Files\Microsoft SQL Server\MSSQL\LOG folder? There should be
> files named ERRORLOG.
>
> "John Steen" <moderndads(nospam)@.hotmail.com> wrote in message
> news:09D2F211-886A-4904-8B56-1471835E0C04@.microsoft.com...
>
|||My bad. Didn't read the last part of the post.
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:ugABXMVYFHA.2572@.TK2MSFTNGP14.phx.gbl...
> Hi
> SQL Server does not log activity like inserts and deletes. You need to run
> SQL Server Profiler to do that.
> Regards
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "ME" <ME@.hotmail.com> wrote in message
> news:eIj85KVYFHA.3164@.TK2MSFTNGP12.phx.gbl...
>
|||There are log files in the log folder, but the latest one is dated May 17,
2004, which is around the time they were moved there from the location where
they were set up. The IP addresses were also changed when they were moved to
their current location. I don't what else may have changed.
Could that have something to do with why the logs stopped recording?
Thanks,
John
"ME" wrote:

> Never heard we must activate something for the log. What do you see in
> \Program Files\Microsoft SQL Server\MSSQL\LOG folder? There should be files
> named ERRORLOG.
>
> "John Steen" <moderndads(nospam)@.hotmail.com> wrote in message
> news:09D2F211-886A-4904-8B56-1471835E0C04@.microsoft.com...
>
>
|||I'm not trying to log inserts and such, Mike, just normal DB activtiy
(startups, shutdowns, recoveries, DBCC jobs, etc.) you see in the server logs.
Thanks,
John
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> SQL Server does not log activity like inserts and deletes. You need to run
> SQL Server Profiler to do that.
> Regards
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "ME" <ME@.hotmail.com> wrote in message
> news:eIj85KVYFHA.3164@.TK2MSFTNGP12.phx.gbl...
>
>
|||Right-click the server in EM, properties, Parameters. Verify that you have an -e parameter and the
path that the parameter points to.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Steen" <moderndads(nospam)@.hotmail.com> wrote in message
news:04B3D2BC-6E90-4103-96E6-EB998BD613A0@.microsoft.com...[vbcol=seagreen]
> There are log files in the log folder, but the latest one is dated May 17,
> 2004, which is around the time they were moved there from the location where
> they were set up. The IP addresses were also changed when they were moved to
> their current location. I don't what else may have changed.
> Could that have something to do with why the logs stopped recording?
> Thanks,
> John
> "ME" wrote:
|||Thanks, Tibor, that did it!
John
"Tibor Karaszi" wrote:

> Right-click the server in EM, properties, Parameters. Verify that you have an -e parameter and the
> path that the parameter points to.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "John Steen" <moderndads(nospam)@.hotmail.com> wrote in message
> news:04B3D2BC-6E90-4103-96E6-EB998BD613A0@.microsoft.com...
>
>

Activating Server Logs

I have two remote Win2000 servers running SQL2000. There are no items under
SQL Server Logs in Ent. Mgr. How do I conjfigure the server to log database
activity?
Thanks,
John Steen
remove (nospam) from e-mail address to send me a messageNever heard we must activate something for the log. What do you see in
\Program Files\Microsoft SQL Server\MSSQL\LOG folder? There should be files
named ERRORLOG.
"John Steen" <moderndads(nospam)@.hotmail.com> wrote in message
news:09D2F211-886A-4904-8B56-1471835E0C04@.microsoft.com...
>I have two remote Win2000 servers running SQL2000. There are no items
>under
> SQL Server Logs in Ent. Mgr. How do I conjfigure the server to log
> database
> activity?
> Thanks,
> John Steen
> remove (nospam) from e-mail address to send me a message|||Hi
SQL Server does not log activity like inserts and deletes. You need to run
SQL Server Profiler to do that.
Regards
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"ME" <ME@.hotmail.com> wrote in message
news:eIj85KVYFHA.3164@.TK2MSFTNGP12.phx.gbl...
> Never heard we must activate something for the log. What do you see in
> \Program Files\Microsoft SQL Server\MSSQL\LOG folder? There should be
> files named ERRORLOG.
>
> "John Steen" <moderndads(nospam)@.hotmail.com> wrote in message
> news:09D2F211-886A-4904-8B56-1471835E0C04@.microsoft.com...
>|||My bad. Didn't read the last part of the post.
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:ugABXMVYFHA.2572@.TK2MSFTNGP14.phx.gbl...
> Hi
> SQL Server does not log activity like inserts and deletes. You need to run
> SQL Server Profiler to do that.
> Regards
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "ME" <ME@.hotmail.com> wrote in message
> news:eIj85KVYFHA.3164@.TK2MSFTNGP12.phx.gbl...
>|||There are log files in the log folder, but the latest one is dated May 17,
2004, which is around the time they were moved there from the location where
they were set up. The IP addresses were also changed when they were moved t
o
their current location. I don't what else may have changed.
Could that have something to do with why the logs stopped recording?
Thanks,
John
"ME" wrote:

> Never heard we must activate something for the log. What do you see in
> \Program Files\Microsoft SQL Server\MSSQL\LOG folder? There should be fil
es
> named ERRORLOG.
>
> "John Steen" <moderndads(nospam)@.hotmail.com> wrote in message
> news:09D2F211-886A-4904-8B56-1471835E0C04@.microsoft.com...
>
>|||I'm not trying to log inserts and such, Mike, just normal DB activtiy
(startups, shutdowns, recoveries, DBCC jobs, etc.) you see in the server log
s.
Thanks,
John
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> SQL Server does not log activity like inserts and deletes. You need to run
> SQL Server Profiler to do that.
> Regards
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "ME" <ME@.hotmail.com> wrote in message
> news:eIj85KVYFHA.3164@.TK2MSFTNGP12.phx.gbl...
>
>|||Right-click the server in EM, properties, Parameters. Verify that you have a
n -e parameter and the
path that the parameter points to.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Steen" <moderndads(nospam)@.hotmail.com> wrote in message
news:04B3D2BC-6E90-4103-96E6-EB998BD613A0@.microsoft.com...[vbcol=seagreen]
> There are log files in the log folder, but the latest one is dated May 17,
> 2004, which is around the time they were moved there from the location whe
re
> they were set up. The IP addresses were also changed when they were moved
to
> their current location. I don't what else may have changed.
> Could that have something to do with why the logs stopped recording?
> Thanks,
> John
> "ME" wrote:
>|||Thanks, Tibor, that did it!
John
"Tibor Karaszi" wrote:

> Right-click the server in EM, properties, Parameters. Verify that you have
an -e parameter and the
> path that the parameter points to.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "John Steen" <moderndads(nospam)@.hotmail.com> wrote in message
> news:04B3D2BC-6E90-4103-96E6-EB998BD613A0@.microsoft.com...
>
>

Activating Server Logs

I have two remote Win2000 servers running SQL2000. There are no items under
SQL Server Logs in Ent. Mgr. How do I conjfigure the server to log database
activity?
Thanks,
John Steen
remove (nospam) from e-mail address to send me a messageNever heard we must activate something for the log. What do you see in
\Program Files\Microsoft SQL Server\MSSQL\LOG folder? There should be files
named ERRORLOG.
"John Steen" <moderndads(nospam)@.hotmail.com> wrote in message
news:09D2F211-886A-4904-8B56-1471835E0C04@.microsoft.com...
>I have two remote Win2000 servers running SQL2000. There are no items
>under
> SQL Server Logs in Ent. Mgr. How do I conjfigure the server to log
> database
> activity?
> Thanks,
> John Steen
> remove (nospam) from e-mail address to send me a message|||Hi
SQL Server does not log activity like inserts and deletes. You need to run
SQL Server Profiler to do that.
Regards
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"ME" <ME@.hotmail.com> wrote in message
news:eIj85KVYFHA.3164@.TK2MSFTNGP12.phx.gbl...
> Never heard we must activate something for the log. What do you see in
> \Program Files\Microsoft SQL Server\MSSQL\LOG folder? There should be
> files named ERRORLOG.
>
> "John Steen" <moderndads(nospam)@.hotmail.com> wrote in message
> news:09D2F211-886A-4904-8B56-1471835E0C04@.microsoft.com...
>>I have two remote Win2000 servers running SQL2000. There are no items
>>under
>> SQL Server Logs in Ent. Mgr. How do I conjfigure the server to log
>> database
>> activity?
>> Thanks,
>> John Steen
>> remove (nospam) from e-mail address to send me a message
>|||My bad. Didn't read the last part of the post.
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:ugABXMVYFHA.2572@.TK2MSFTNGP14.phx.gbl...
> Hi
> SQL Server does not log activity like inserts and deletes. You need to run
> SQL Server Profiler to do that.
> Regards
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "ME" <ME@.hotmail.com> wrote in message
> news:eIj85KVYFHA.3164@.TK2MSFTNGP12.phx.gbl...
>> Never heard we must activate something for the log. What do you see in
>> \Program Files\Microsoft SQL Server\MSSQL\LOG folder? There should be
>> files named ERRORLOG.
>>
>> "John Steen" <moderndads(nospam)@.hotmail.com> wrote in message
>> news:09D2F211-886A-4904-8B56-1471835E0C04@.microsoft.com...
>>I have two remote Win2000 servers running SQL2000. There are no items
>>under
>> SQL Server Logs in Ent. Mgr. How do I conjfigure the server to log
>> database
>> activity?
>> Thanks,
>> John Steen
>> remove (nospam) from e-mail address to send me a message
>>
>|||There are log files in the log folder, but the latest one is dated May 17,
2004, which is around the time they were moved there from the location where
they were set up. The IP addresses were also changed when they were moved to
their current location. I don't what else may have changed.
Could that have something to do with why the logs stopped recording?
Thanks,
John
"ME" wrote:
> Never heard we must activate something for the log. What do you see in
> \Program Files\Microsoft SQL Server\MSSQL\LOG folder? There should be files
> named ERRORLOG.
>
> "John Steen" <moderndads(nospam)@.hotmail.com> wrote in message
> news:09D2F211-886A-4904-8B56-1471835E0C04@.microsoft.com...
> >I have two remote Win2000 servers running SQL2000. There are no items
> >under
> > SQL Server Logs in Ent. Mgr. How do I conjfigure the server to log
> > database
> > activity?
> >
> > Thanks,
> > John Steen
> > remove (nospam) from e-mail address to send me a message
>
>|||I'm not trying to log inserts and such, Mike, just normal DB activtiy
(startups, shutdowns, recoveries, DBCC jobs, etc.) you see in the server logs.
Thanks,
John
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> SQL Server does not log activity like inserts and deletes. You need to run
> SQL Server Profiler to do that.
> Regards
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "ME" <ME@.hotmail.com> wrote in message
> news:eIj85KVYFHA.3164@.TK2MSFTNGP12.phx.gbl...
> > Never heard we must activate something for the log. What do you see in
> > \Program Files\Microsoft SQL Server\MSSQL\LOG folder? There should be
> > files named ERRORLOG.
> >
> >
> > "John Steen" <moderndads(nospam)@.hotmail.com> wrote in message
> > news:09D2F211-886A-4904-8B56-1471835E0C04@.microsoft.com...
> >>I have two remote Win2000 servers running SQL2000. There are no items
> >>under
> >> SQL Server Logs in Ent. Mgr. How do I conjfigure the server to log
> >> database
> >> activity?
> >>
> >> Thanks,
> >> John Steen
> >> remove (nospam) from e-mail address to send me a message
> >
> >
>
>|||Right-click the server in EM, properties, Parameters. Verify that you have an -e parameter and the
path that the parameter points to.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Steen" <moderndads(nospam)@.hotmail.com> wrote in message
news:04B3D2BC-6E90-4103-96E6-EB998BD613A0@.microsoft.com...
> There are log files in the log folder, but the latest one is dated May 17,
> 2004, which is around the time they were moved there from the location where
> they were set up. The IP addresses were also changed when they were moved to
> their current location. I don't what else may have changed.
> Could that have something to do with why the logs stopped recording?
> Thanks,
> John
> "ME" wrote:
>> Never heard we must activate something for the log. What do you see in
>> \Program Files\Microsoft SQL Server\MSSQL\LOG folder? There should be files
>> named ERRORLOG.
>>
>> "John Steen" <moderndads(nospam)@.hotmail.com> wrote in message
>> news:09D2F211-886A-4904-8B56-1471835E0C04@.microsoft.com...
>> >I have two remote Win2000 servers running SQL2000. There are no items
>> >under
>> > SQL Server Logs in Ent. Mgr. How do I conjfigure the server to log
>> > database
>> > activity?
>> >
>> > Thanks,
>> > John Steen
>> > remove (nospam) from e-mail address to send me a message
>>|||Thanks, Tibor, that did it!
John
"Tibor Karaszi" wrote:
> Right-click the server in EM, properties, Parameters. Verify that you have an -e parameter and the
> path that the parameter points to.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "John Steen" <moderndads(nospam)@.hotmail.com> wrote in message
> news:04B3D2BC-6E90-4103-96E6-EB998BD613A0@.microsoft.com...
> > There are log files in the log folder, but the latest one is dated May 17,
> > 2004, which is around the time they were moved there from the location where
> > they were set up. The IP addresses were also changed when they were moved to
> > their current location. I don't what else may have changed.
> >
> > Could that have something to do with why the logs stopped recording?
> >
> > Thanks,
> > John
> >
> > "ME" wrote:
> >
> >> Never heard we must activate something for the log. What do you see in
> >> \Program Files\Microsoft SQL Server\MSSQL\LOG folder? There should be files
> >> named ERRORLOG.
> >>
> >>
> >> "John Steen" <moderndads(nospam)@.hotmail.com> wrote in message
> >> news:09D2F211-886A-4904-8B56-1471835E0C04@.microsoft.com...
> >> >I have two remote Win2000 servers running SQL2000. There are no items
> >> >under
> >> > SQL Server Logs in Ent. Mgr. How do I conjfigure the server to log
> >> > database
> >> > activity?
> >> >
> >> > Thanks,
> >> > John Steen
> >> > remove (nospam) from e-mail address to send me a message
> >>
> >>
> >>
>
>

Activating MSDE for remote Access

I'm finding that when I install MSDE, by default no protocols are enabled
for remote access. I've found that I can use the SQL Server Network Utility
to enable the TCP/IP protocol and then the MSDE instance is accessible from
remote workstations.
But how can I do this without having the SQL Server Client Tools installed?
Is there a command line or configuration option I can use?
Thanks for your help,
Joe Geretz
I think I found it. DISABLENETWORKPROTOCOLS=0.
- Joe Geretz -
"Joseph Geretz" <jgeretz@.nospam.com> wrote in message
news:uoPxQ8DYEHA.3300@.TK2MSFTNGP09.phx.gbl...
> I'm finding that when I install MSDE, by default no protocols are enabled
> for remote access. I've found that I can use the SQL Server Network
Utility
> to enable the TCP/IP protocol and then the MSDE instance is accessible
from
> remote workstations.
> But how can I do this without having the SQL Server Client Tools
installed?
> Is there a command line or configuration option I can use?
> Thanks for your help,
> Joe Geretz
>
|||But how would I do this after the instance is already installed? I can't
rely on SQL Server Client Tools because presumably, they won't exist locally
on the MSDE box.
Thanks for your help,
- Joe Geretz -
"Joseph Geretz" <jgeretz@.nospam.com> wrote in message
news:ucsgMAEYEHA.716@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> I think I found it. DISABLENETWORKPROTOCOLS=0.
> - Joe Geretz -
> "Joseph Geretz" <jgeretz@.nospam.com> wrote in message
> news:uoPxQ8DYEHA.3300@.TK2MSFTNGP09.phx.gbl...
enabled
> Utility
> from
> installed?
>
|||Program Files\Microsoft SQL Server\80\Tools\Binn\SVRNETCN.exe
Enable tcp/ip and restart MSDE
David P. Lurie
"Joseph Geretz" <jgeretz@.nospam.com> wrote in message
news:e$2DuCEYEHA.3988@.tk2msftngp13.phx.gbl...
> But how would I do this after the instance is already installed? I can't
> rely on SQL Server Client Tools because presumably, they won't exist
locally
> on the MSDE box.
>
|||hi Joseph,
"Joseph Geretz" <jgeretz@.nospam.com> ha scritto nel messaggio
news:e$2DuCEYEHA.3988@.tk2msftngp13.phx.gbl...
> But how would I do this after the instance is already installed? I can't
> rely on SQL Server Client Tools because presumably, they won't exist
locally
> on the MSDE box.
on the box where MSDE has been installed, the Server Network Tools will be
alwayse available (as David already pointed out), and the Client Network
Tools (cliconfg.exe) too...
on all client, only the Client Network Tools will be available, installed by
MDAC...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Thank David and Andrea!
I hadn't noticed that these tools are actually installed with MSDE. This is
great, exactly what I'll need.
Thanks,
- Joe Geretz -
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:2kladsF3m4fmU1@.uni-berlin.de...
> hi Joseph,
> "Joseph Geretz" <jgeretz@.nospam.com> ha scritto nel messaggio
> news:e$2DuCEYEHA.3988@.tk2msftngp13.phx.gbl...
> locally
> on the box where MSDE has been installed, the Server Network Tools will be
> alwayse available (as David already pointed out), and the Client Network
> Tools (cliconfg.exe) too...
> on all client, only the Client Network Tools will be available, installed
by
> MDAC...
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>

Accessing SQL Server 2005 through Vista

I just bought a new HP laptop that has Vista installed.

I need to use Enterprise Manager, or an equivalent, to access a remote server, running SQL 2005.

With XP, I always installed and used SQL Server 2000 Client features.

Vista does not play well with SQL 2000 so I am looking for an alternative to simply be able to access, and perform DTS functions, a remote SQL 2005 system.

Does SQL Server 2005 Express offer client alias features?

Can anyone recommend another approach or product?

Thank you,

Steve

Just install the SQL 2005 client tools with the DTS add tool (available from microsoft.com/sql).|||SQL 2000 is not supported on Vista and you have depend only on SQL 2005 - http://www.microsoft.com/sql/prodinfo/previousversions/faq-sql_on_vista.mspx fyi on vista support.|||

SQL Server Configuration Manager lets you define aliases for servers in the SQL Native Client Configuration | Aliases node. You can register servers in Management Studio to simplify connecting to them as well.

Hope this helps,

Steve

Accessing SQL Server 2005 through Vista

I just bought a new HP laptop that has Vista installed.

I need to use Enterprise Manager, or an equivalent, to access a remote server, running SQL 2005.

With XP, I always installed and used SQL Server 2000 Client features.

Vista does not play well with SQL 2000 so I am looking for an alternative to simply be able to access, and perform DTS functions, a remote SQL 2005 system.

Does SQL Server 2005 Express offer client alias features?

Can anyone recommend another approach or product?

Thank you,

Steve

Just install the SQL 2005 client tools with the DTS add tool (available from microsoft.com/sql).|||SQL 2000 is not supported on Vista and you have depend only on SQL 2005 - http://www.microsoft.com/sql/prodinfo/previousversions/faq-sql_on_vista.mspx fyi on vista support.|||

SQL Server Configuration Manager lets you define aliases for servers in the SQL Native Client Configuration | Aliases node. You can register servers in Management Studio to simplify connecting to them as well.

Hope this helps,

Steve

Accessing SQL 2000 servers from 2005 Management Studio

Hi
Do you configure SQL Server 2005 remote connection to be enabled?
Can you show an exact error you are getting?
<PepperFleming@.gmail.com> wrote in message
news:1150642596.033192.219160@.u72g2000cwu.googlegroups.com...
> Hi,
> I have seen many post on this problem, but I have not seen a concise
> answer.
> I have 2 servers running SQL 2000. I have a fresh installation of XP
> and SQL 2005 on my laptop (No Firewall). I cannot register the 2000
> servers using the SQL 2005 Management studio by Named Pipes or by
> TCPIP. Names Pipes throws the error 40 error, and TCP throws the error
> 0, connection refused error.
> the 2000 severs can regsiter each other and a 4th machine with EM on it
> can register them, so I know this isn't a firewall or connection issue
> on the 2000 servers.
> Can anyone shed some light on how to open up my SQL 20005 installation
> to allow it to communicate with the 2000 servers.
> Thanks
> Pepper Fleming
>Hi Uri,
Thanks so much for responding. The message I am getting through the
Management studio when trying to connect to the server running SQL 2000
is:
An error has occurred while establishing a connection to the server.
When connecting to SQL Server 2005, this failure may be caused by the
fact that under the default settings SQL Server does not allow remote
connections. (provider: Named Pipes Provider, error: 40 - Could not
open a connection to SQL Server) (Microsoft SQL Server, Error: 1326)
I have named pipes enabled on my laptop under the SQL Server
Configuration manager.
The message looks a little misleading to me, because the way I read it,
the studio thinks it is connecting to a SQL Server 2005 instance.
Thanks,
Pepper|||One more piece of information if it helps, I can connect to other SQL
2005 servers remotely, this seems specific to SQL 2000 servers.
Pepper|||These may help. Using portqry you should get similar output as below.
http://www.microsoft.com/downloads/...&displaylang=en
http://support.microsoft.com/defaul...kb;en-us;287932
F:\PortQryV2>
*** User Input***
F:\PortQryV2>portqry -i
PortQry Interactive Mode
Type 'help' for a list of commands
Default Node: 127.0.0.1
Current option values:
end port= 80
protocol= TCP
source port= 0 (ephemeral)
*** User Input***
> node 192.168.1.98
Default Node: 192.168.1.98
>
*** User Input***
> q sql
resolving service name using local services file...
TCP port resolved to the 'ms-sql-s' service
IP address resolved to pe1600.dspatrick.local.com
querying...
TCP port 1433 (ms-sql-s service): LISTENING
>
resolving service name using local services file...
UDP port resolved to the 'ms-sql-m' service
IP address resolved to pe1600.dspatrick.local.com
querying...
UDP port 1434 (ms-sql-m service): LISTENING or FILTERED
Sending SQL Server query to UDP port 1434...
Server's response:
ServerName PE1600
InstanceName MSSQLSERVER
IsClustered No
Version 9.00.1399.06
tcp 1433
F
==== End of SQL Server query response ====
UDP port 1434 is LISTENING
*** User Input***
> exit
exiting PortQry Interactive Mode...
F:\PortQryV2>
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
<PepperFleming@.gmail.com> wrote:
| One more piece of information if it helps, I can connect to other SQL
| 2005 servers remotely, this seems specific to SQL 2000 servers.
|
| Pepper
||||Hi,
I have seen many post on this problem, but I have not seen a concise
answer.
I have 2 servers running SQL 2000. I have a fresh installation of XP
and SQL 2005 on my laptop (No Firewall). I cannot register the 2000
servers using the SQL 2005 Management studio by Named Pipes or by
TCPIP. Names Pipes throws the error 40 error, and TCP throws the error
0, connection refused error.
the 2000 severs can regsiter each other and a 4th machine with EM on it
can register them, so I know this isn't a firewall or connection issue
on the 2000 servers.
Can anyone shed some light on how to open up my SQL 20005 installation
to allow it to communicate with the 2000 servers.
Thanks
Pepper Fleming|||Hi
Do you configure SQL Server 2005 remote connection to be enabled?
Can you show an exact error you are getting?
<PepperFleming@.gmail.com> wrote in message
news:1150642596.033192.219160@.u72g2000cwu.googlegroups.com...
> Hi,
> I have seen many post on this problem, but I have not seen a concise
> answer.
> I have 2 servers running SQL 2000. I have a fresh installation of XP
> and SQL 2005 on my laptop (No Firewall). I cannot register the 2000
> servers using the SQL 2005 Management studio by Named Pipes or by
> TCPIP. Names Pipes throws the error 40 error, and TCP throws the error
> 0, connection refused error.
> the 2000 severs can regsiter each other and a 4th machine with EM on it
> can register them, so I know this isn't a firewall or connection issue
> on the 2000 servers.
> Can anyone shed some light on how to open up my SQL 20005 installation
> to allow it to communicate with the 2000 servers.
> Thanks
> Pepper Fleming
>|||Hi Uri,
Thanks so much for responding. The message I am getting through the
Management studio when trying to connect to the server running SQL 2000
is:
An error has occurred while establishing a connection to the server.
When connecting to SQL Server 2005, this failure may be caused by the
fact that under the default settings SQL Server does not allow remote
connections. (provider: Named Pipes Provider, error: 40 - Could not
open a connection to SQL Server) (Microsoft SQL Server, Error: 1326)
I have named pipes enabled on my laptop under the SQL Server
Configuration manager.
The message looks a little misleading to me, because the way I read it,
the studio thinks it is connecting to a SQL Server 2005 instance.
Thanks,
Pepper|||One more piece of information if it helps, I can connect to other SQL
2005 servers remotely, this seems specific to SQL 2000 servers.
Pepper|||These may help. Using portqry you should get similar output as below.
http://www.microsoft.com/downloads/...&displaylang=en
http://support.microsoft.com/defaul...kb;en-us;287932
F:\PortQryV2>
*** User Input***
F:\PortQryV2>portqry -i
PortQry Interactive Mode
Type 'help' for a list of commands
Default Node: 127.0.0.1
Current option values:
end port= 80
protocol= TCP
source port= 0 (ephemeral)
*** User Input***
> node 192.168.1.98
Default Node: 192.168.1.98
>
*** User Input***
> q sql
resolving service name using local services file...
TCP port resolved to the 'ms-sql-s' service
IP address resolved to pe1600.dspatrick.local.com
querying...
TCP port 1433 (ms-sql-s service): LISTENING
>
resolving service name using local services file...
UDP port resolved to the 'ms-sql-m' service
IP address resolved to pe1600.dspatrick.local.com
querying...
UDP port 1434 (ms-sql-m service): LISTENING or FILTERED
Sending SQL Server query to UDP port 1434...
Server's response:
ServerName PE1600
InstanceName MSSQLSERVER
IsClustered No
Version 9.00.1399.06
tcp 1433
F
==== End of SQL Server query response ====
UDP port 1434 is LISTENING
*** User Input***
> exit
exiting PortQry Interactive Mode...
F:\PortQryV2>
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
<PepperFleming@.gmail.com> wrote:
| One more piece of information if it helps, I can connect to other SQL
| 2005 servers remotely, this seems specific to SQL 2000 servers.
|
| Pepper
||||Thanks Dave,
I will run this, but does anyone know if my problem is unusual or is
this a configuration issue common with a standard SQL Server 2005
installation.
Can you manage SQL 2000 servers from the 2005 Management studio out of
the box?
Pepper

Accessing shares over internet

I have a remote client accessing a SQL database via an internet connection to
a public IP address on our SQL server. THe program needs to access a shared
folder for printing reports in the program, but the client does not recognize
the UNC path to the folder because they are not on the network. How can I
get around this? LMHOSTS?
TIA,
S. Thompson
Hi
\\ipaddress\sharename does work over the internet, as long as the correct
ports are opened to allow Kerberos or NT Challenge/Response authentication.
If you do, you are asking to get hacked. I would not recommend doing this.
Is there not another printing solution you can use?
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"scottdog129" <scottdog129@.discussions.microsoft.com> wrote in message
news:24306B87-DD2D-4E0F-9C46-4CD8D7C9C999@.microsoft.com...
>I have a remote client accessing a SQL database via an internet connection
>to
> a public IP address on our SQL server. THe program needs to access a
> shared
> folder for printing reports in the program, but the client does not
> recognize
> the UNC path to the folder because they are not on the network. How can I
> get around this? LMHOSTS?
> TIA,
> S. Thompson
|||No, there doesnt seem to be another solution. Would I use an LMHOST entry
for that?
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> \\ipaddress\sharename does work over the internet, as long as the correct
> ports are opened to allow Kerberos or NT Challenge/Response authentication.
> If you do, you are asking to get hacked. I would not recommend doing this.
> Is there not another printing solution you can use?
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "scottdog129" <scottdog129@.discussions.microsoft.com> wrote in message
> news:24306B87-DD2D-4E0F-9C46-4CD8D7C9C999@.microsoft.com...
>
>

Accessing Same Remote Table Form Two Linked Servers Return Different Results

We have two SQL Server 2000 instances A and B, both have
a linked server "NAR" defined to access the same database
also called "NAR" at a third remote SQL Server instance.
But executing the following query returns different
results: A returns NULL result set, while B returns the
expected result set.
The query simply selects the new or updated transactions
in the remote table based on a timestamp.
What could be the reason causing this different result?
What makes things even more complicated is that, While
executing on A, not all dates returns NULL set, some of
the dates return result set just fine. While on B, it
always return expected result set no matter what dates you
set. This is what we expected. But how do you explain this
behavior on A?
Select s.TransID
From NAR.NAR.dbo.StoreTransaction s
Where s.CreateTimeStamp > '2004-03-02 18:00:00'
And s.CreateTimestamp <= getdate()
OR
s.updateTimeStamp > '2004-03-02 18:00:00'
And s.updateTimestamp <= getdate()- What results do you get if you were to execute that query on NAR itself?
Same results as from B?
- Have you tried using OPENQUERY when running the query from A to see what
results you get?
- Another suggestion might be to run a profiler trace when the query is
being executed from A->NAR and B->NAR and check for differences.
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.|||Thank you for your reply, I posted the problem in many places and to
many so-called SQL experts, nobody replied so far except for you.
Regarding your suggestion:
- What results do you get if you were to execute that query on NAR
itself?
Good point! It occurred to me last week that it may be a problem on NAR
itself. And IT IS! I ran the query in SQL ANALYZER of NAR instance
changing the from clause to reference table StoreTransaction directly,
what is interesting is:
Select count(*) returns correct number in a few seconds, but, select any
column or run all listed columnsreturns NULL after running for almost 2
minutes.
e.g, select count(*) OR Select count(Transid)
returns correct number, select Transid returns NULL.
The from and where clause is exactly the same.
And this problem can be found only on certain dates. For example, Mar.
22 is not OK, but March 23 is fine.
It seems to be data related, but how do you explain aggregate returns
corerct results?
- Have you tried using OPENQUERY when running the query from A to see
what
results you get?
How do I use OPENQUERY in SQL ANALYZER?
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!

Accessing Remote SQL Server Management Console from the web browser?

How does one access a remote SQL Server from the web browser?

Thanks,

-Conrad

There is no builtin functionality for that. But Microsoft offers a Web Administrator (don′t knwo if that one is also capable for SQL Server 2005)

http://www.microsoft.com/downloads/details.aspx?FamilyID=c039a798-c57a-419e-acbc-2a332cb7f959&DisplayLang=en

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

accessing remote sql server 2000 ( enterprise manager like tool)

Hi all,

I need to gain access to a remote SQL Server 2000 storing my data.
The remote SQL machine can only be accessed via an internet connection,
that is - it is not part of my network.

I know Enterprise Manager can do the job, but EM only comes bundled in
MS SQL Server, which is not installed on the client machine.

Please let me know which other (preferably free) tools can perform
such remote access.

Thanks,
Gilad HaimovOn 25 Oct 2003 03:36:11 -0700, giladha@.hotmail.com (gilad) wrote:

>Hi all,
>I need to gain access to a remote SQL Server 2000 storing my data.
>The remote SQL machine can only be accessed via an internet connection,
>that is - it is not part of my network.
>I know Enterprise Manager can do the job, but EM only comes bundled in
>MS SQL Server, which is not installed on the client machine.
>Please let me know which other (preferably free) tools can perform
>such remote access.
>Thanks,
>Gilad Haimov
In theory anything that can attach to a database, VB MS-Access Fxopro
etc etc. More of a problem would be is the database directly
accessible from the internet at all? I would have thought this was a
very bad idea.

Accessing remote SQL 2005 db using C# express edition

I have downloaded Visual C# express edition and SQL Server 2005 Express
Edition.
Installed Visual C# Express and SQL Server Express edition on Windows XP Sp2
and .NET framework 2.0
I am trying to write a C# program using ADO.NET to access a SQL 2000
database stored on a remote server Windows 2003 Sp1.
User logged in on this XP machine does not have rights on Windows 2003 serve
r.
Getting some kind of errors that login failed. And if I use user name and
password while connecting to SQL 2000 database getting some different errors
.
My questions are:
1. Is it possible with Visual C# express edition to access remote server SQL
db?
or do I need to use local database?
2. Is it possible to import SQL 2000 datbase in SQL 2005 Express edition?
Please advise.
Thanks in advance.Hi
1. If you are using windows authentication then there will need to be a
trust between the server and workstation if they are not on the same domain.
If you have mixed mode authentication then you may be able to use a SQL
Server login. Try connecting with osql first.
2. You can backup/restore from SQL 2000 to SQL 2005.
John
"SPD" wrote:

> I have downloaded Visual C# express edition and SQL Server 2005 Express
> Edition.
> Installed Visual C# Express and SQL Server Express edition on Windows XP S
p2
> and .NET framework 2.0
> I am trying to write a C# program using ADO.NET to access a SQL 2000
> database stored on a remote server Windows 2003 Sp1.
> User logged in on this XP machine does not have rights on Windows 2003 ser
ver.
> Getting some kind of errors that login failed. And if I use user name and
> password while connecting to SQL 2000 database getting some different erro
rs.
> My questions are:
> 1. Is it possible with Visual C# express edition to access remote server S
QL
> db?
> or do I need to use local database?
> 2. Is it possible to import SQL 2000 datbase in SQL 2005 Express edition?
> Please advise.
> Thanks in advance.
>

accessing remote server

Hi
I have SQL server installed on windows 2003 server as name (local) on
server.
Windows 2003 have public IP and I can ping from my machine and I can connect
to that machine via terminal service as well.
I just want to register that SQL Server to my local machine SQL server
group.
I tried but its failed.
Any suggestion or advise please.
Kind Regards
fayyaz"F@.yy@.Z" <fayyaz.ahmed@.mvwebmaker.com> wrote in message
news:uNzIA3WpEHA.1668@.TK2MSFTNGP14.phx.gbl...
> I have SQL server installed on windows 2003 server as name (local) on
> server.
> Windows 2003 have public IP and I can ping from my machine and I can
connect
> to that machine via terminal service as well.
> I just want to register that SQL Server to my local machine SQL server
> group.
> I tried but its failed.
> Any suggestion or advise please.
Have you tried registering by IP address? If that works, you may have a name
resolution issue (DNS).
Steve

accessing remote server

Hi
I have SQL server installed on windows 2003 server as name (local) on
server.
Windows 2003 have public IP and I can ping from my machine and I can connect
to that machine via terminal service as well.
I just want to register that SQL Server to my local machine SQL server
group.
I tried but its failed.
Any suggestion or advise please.
Kind Regards
fayyaz
"F@.yy@.Z" <fayyaz.ahmed@.mvwebmaker.com> wrote in message
news:uNzIA3WpEHA.1668@.TK2MSFTNGP14.phx.gbl...
> I have SQL server installed on windows 2003 server as name (local) on
> server.
> Windows 2003 have public IP and I can ping from my machine and I can
connect
> to that machine via terminal service as well.
> I just want to register that SQL Server to my local machine SQL server
> group.
> I tried but its failed.
> Any suggestion or advise please.
Have you tried registering by IP address? If that works, you may have a name
resolution issue (DNS).
Steve

Accessing remote Index Server from XP

I have SQL Server 2000 running on Windows XP, and want to access the index server on a Windows 2003 server.
I use the well known servername.indexname..Scope() to access the remote IS. This actually worked for me for a bit. I'm not sure what happened, but it stopped working and I can't get it to work again. (I receive the oh-so-helpful error: [Microsoft][ODBC
SQL Server Driver][SQL Server][OLE/DB provider returned message: Unspecified error])
I *suspect* it has something to do with security. This is what I've done in the name of security:
1. On Win 2003 Server Machine: Set up trust for XP/SQL Server machine.
2. Made the XP/SQL Server machine a member of the 2003 Server's domain.
3. *Tried* to change the SQL Server log on account to an account belonging to the 2003 Server machine, but XP keeps telling me that it doesn't recognize the login username/password. Can I not do this in XP?
I've been banging my head on this problem for days now. It's a tough one. I hope somoene out there can help.
Thanks to all!
Brandon
what happens when you change the scope to \
ie
select From from openquery(web,'select path from
remoteservername.remotecatalogname..scope(''\'')')
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Brandon" <Brandon@.discussions.microsoft.com> wrote in message
news:F78FD457-1968-4082-AF87-01D26B302FC7@.microsoft.com...
> I have SQL Server 2000 running on Windows XP, and want to access the index
server on a Windows 2003 server.
> I use the well known servername.indexname..Scope() to access the remote
IS. This actually worked for me for a bit. I'm not sure what happened, but
it stopped working and I can't get it to work again. (I receive the
oh-so-helpful error: [Microsoft][ODBC SQL Server Driver][SQL Server][OLE/DB
provider returned message: Unspecified error])
> I *suspect* it has something to do with security. This is what I've done
in the name of security:
> 1. On Win 2003 Server Machine: Set up trust for XP/SQL Server machine.
> 2. Made the XP/SQL Server machine a member of the 2003 Server's domain.
> 3. *Tried* to change the SQL Server log on account to an account belonging
to the 2003 Server machine, but XP keeps telling me that it doesn't
recognize the login username/password. Can I not do this in XP?
> I've been banging my head on this problem for days now. It's a tough one.
I hope somoene out there can help.
> Thanks to all!
> Brandon
|||same result.
"Hilary Cotter" wrote:

> what happens when you change the scope to \
> ie
> select From from openquery(web,'select path from
> remoteservername.remotecatalogname..scope(''\'')')
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Brandon" <Brandon@.discussions.microsoft.com> wrote in message
> news:F78FD457-1968-4082-AF87-01D26B302FC7@.microsoft.com...
> server on a Windows 2003 server.
> IS. This actually worked for me for a bit. I'm not sure what happened, but
> it stopped working and I can't get it to work again. (I receive the
> oh-so-helpful error: [Microsoft][ODBC SQL Server Driver][SQL Server][OLE/DB
> provider returned message: Unspecified error])
> in the name of security:
> to the 2003 Server machine, but XP keeps telling me that it doesn't
> recognize the login username/password. Can I not do this in XP?
> I hope somoene out there can help.
>
>

accessing remote databases thru agent jobs

We have some agents jobs which are run on schedule.
The agent jobs call stored procedures created on
local databases.
We want to move the agent jobs to a different server
(remote server). How do we set up accessing the databases
from the remote server so that none of the agent jobs need to
be changed, or with minimum change.
TIA.You would have to set up linked severs. For more information and examples,
see sp_addlinkedserver, sp_addlinkedsrvlogin, sp_serveroption in SQL Server
Books Online.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message
news:1121880962.361317.54810@.g44g2000cwa.googlegroups.com...
> We have some agents jobs which are run on schedule.
> The agent jobs call stored procedures created on
> local databases.
> We want to move the agent jobs to a different server
> (remote server). How do we set up accessing the databases
> from the remote server so that none of the agent jobs need to
> be changed, or with minimum change.
> TIA.
>|||Narayana Vyas Kondreddi wrote:
> You would have to set up linked severs. For more information and examples,
> see sp_addlinkedserver, sp_addlinkedsrvlogin, sp_serveroption in SQL Server
> Books Online.
I too thought about the linked server option, but it seems it
still requires using a linked server name as the qualifier
for the database name.
this is the original code of the Agent job
-- Add the job steps
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobstep @.job_id = @.JobID,
@.step_id = 1, @.step_name =N'Purge', @.command = N'exec bts_CleanupDeadProcesses', @.database_name =N'BizTalkMsgBoxDb',
@.server = N'', @.database_user_name = N'', @.subsystem = N'TSQL',
@.cmdexec_success_code = 0, @.flags
= 0, @.retry_attempts = 0, @.retry_interval = 1, @.output_file_name = N'',
@.on_success_step_id = 0,
@.on_success_action = 1, @.on_fail_step_id = 0, @.on_fail_action = 2
Now how do i make the agent job refer to a remote database at the
parameter @.database_name = N'BizTalkMsgBoxDb' without qualifying it
with the linked server.
Thanks for your help.|||Narayana Vyas Kondreddi wrote:
> You would have to set up linked severs. For more information and examples,
> see sp_addlinkedserver, sp_addlinkedsrvlogin, sp_serveroption in SQL Server
That's what my initial guess was. However when I add
a linked server, in the SQL Agent Job, I still need to
qualify the database name from MyDB to LINKEDSERVER.MyDB.
is there a way to avoid it. That is, is there a way for
Agent job to know the context of server is LINKEDSERVER.
Thanks for your help.|||No. Instead you could have a constant linked server name, by adding an alias
on the server, using Client Network Utility, and by adding a linked server
using the alias name. this way you could change the server name, but still
keep the same alias name.
Anyway, makes me curious why are you movig the job to a different server?
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message
news:1121884794.983994.137000@.g43g2000cwa.googlegroups.com...
>
> Narayana Vyas Kondreddi wrote:
>> You would have to set up linked severs. For more information and
>> examples,
>> see sp_addlinkedserver, sp_addlinkedsrvlogin, sp_serveroption in SQL
>> Server
> That's what my initial guess was. However when I add
> a linked server, in the SQL Agent Job, I still need to
> qualify the database name from MyDB to LINKEDSERVER.MyDB.
> is there a way to avoid it. That is, is there a way for
> Agent job to know the context of server is LINKEDSERVER.
> Thanks for your help.
>|||Narayana Vyas Kondreddi wrote:
> No. Instead you could have a constant linked server name, by adding an alias
> on the server, using Client Network Utility, and by adding a linked server
> using the alias name. this way you could change the server name, but still
> keep the same alias name.
I am not sure how the above is relevant to my case. Please refer to
the code example I have posted in the other reply.
> Anyway, makes me curious why are you movig the job to a different server?
as you can see from the code snippet it is Biztalk jobs. We
are going to host our production server with a hosting company
and they do not support running application jobs on the
database server. So I am doing pro/con matrix to see how
much we have to change.|||Okay, you will have to make a change to the job step, if the server context
has to change.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message
news:1121888749.038267.117940@.g49g2000cwa.googlegroups.com...
> Narayana Vyas Kondreddi wrote:
>> No. Instead you could have a constant linked server name, by adding an
>> alias
>> on the server, using Client Network Utility, and by adding a linked
>> server
>> using the alias name. this way you could change the server name, but
>> still
>> keep the same alias name.
> I am not sure how the above is relevant to my case. Please refer to
> the code example I have posted in the other reply.
>> Anyway, makes me curious why are you movig the job to a different server?
> as you can see from the code snippet it is Biztalk jobs. We
> are going to host our production server with a hosting company
> and they do not support running application jobs on the
> database server. So I am doing pro/con matrix to see how
> much we have to change.
>

accessing remote databases thru agent jobs

We have some agents jobs which are run on schedule.
The agent jobs call stored procedures created on
local databases.
We want to move the agent jobs to a different server
(remote server). How do we set up accessing the databases
from the remote server so that none of the agent jobs need to
be changed, or with minimum change.
TIA.
You would have to set up linked severs. For more information and examples,
see sp_addlinkedserver, sp_addlinkedsrvlogin, sp_serveroption in SQL Server
Books Online.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message
news:1121880962.361317.54810@.g44g2000cwa.googlegro ups.com...
> We have some agents jobs which are run on schedule.
> The agent jobs call stored procedures created on
> local databases.
> We want to move the agent jobs to a different server
> (remote server). How do we set up accessing the databases
> from the remote server so that none of the agent jobs need to
> be changed, or with minimum change.
> TIA.
>
|||Narayana Vyas Kondreddi wrote:
> You would have to set up linked severs. For more information and examples,
> see sp_addlinkedserver, sp_addlinkedsrvlogin, sp_serveroption in SQL Server
> Books Online.
I too thought about the linked server option, but it seems it
still requires using a linked server name as the qualifier
for the database name.
this is the original code of the Agent job
-- Add the job steps
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobstep @.job_id = @.JobID,
@.step_id = 1, @.step_name =
N'Purge', @.command = N'exec bts_CleanupDeadProcesses', @.database_name =
N'BizTalkMsgBoxDb',
@.server = N'', @.database_user_name = N'', @.subsystem = N'TSQL',
@.cmdexec_success_code = 0, @.flags
= 0, @.retry_attempts = 0, @.retry_interval = 1, @.output_file_name = N'',
@.on_success_step_id = 0,
@.on_success_action = 1, @.on_fail_step_id = 0, @.on_fail_action = 2
Now how do i make the agent job refer to a remote database at the
parameter @.database_name = N'BizTalkMsgBoxDb' without qualifying it
with the linked server.
Thanks for your help.
|||Narayana Vyas Kondreddi wrote:
> You would have to set up linked severs. For more information and examples,
> see sp_addlinkedserver, sp_addlinkedsrvlogin, sp_serveroption in SQL Server
That's what my initial guess was. However when I add
a linked server, in the SQL Agent Job, I still need to
qualify the database name from MyDB to LINKEDSERVER.MyDB.
is there a way to avoid it. That is, is there a way for
Agent job to know the context of server is LINKEDSERVER.
Thanks for your help.
|||No. Instead you could have a constant linked server name, by adding an alias
on the server, using Client Network Utility, and by adding a linked server
using the alias name. this way you could change the server name, but still
keep the same alias name.
Anyway, makes me curious why are you movig the job to a different server?
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message
news:1121884794.983994.137000@.g43g2000cwa.googlegr oups.com...
>
> Narayana Vyas Kondreddi wrote:
> That's what my initial guess was. However when I add
> a linked server, in the SQL Agent Job, I still need to
> qualify the database name from MyDB to LINKEDSERVER.MyDB.
> is there a way to avoid it. That is, is there a way for
> Agent job to know the context of server is LINKEDSERVER.
> Thanks for your help.
>
|||Narayana Vyas Kondreddi wrote:
> No. Instead you could have a constant linked server name, by adding an alias
> on the server, using Client Network Utility, and by adding a linked server
> using the alias name. this way you could change the server name, but still
> keep the same alias name.
I am not sure how the above is relevant to my case. Please refer to
the code example I have posted in the other reply.

> Anyway, makes me curious why are you movig the job to a different server?
as you can see from the code snippet it is Biztalk jobs. We
are going to host our production server with a hosting company
and they do not support running application jobs on the
database server. So I am doing pro/con matrix to see how
much we have to change.
|||Okay, you will have to make a change to the job step, if the server context
has to change.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message
news:1121888749.038267.117940@.g49g2000cwa.googlegr oups.com...
> Narayana Vyas Kondreddi wrote:
> I am not sure how the above is relevant to my case. Please refer to
> the code example I have posted in the other reply.
>
> as you can see from the code snippet it is Biztalk jobs. We
> are going to host our production server with a hosting company
> and they do not support running application jobs on the
> database server. So I am doing pro/con matrix to see how
> much we have to change.
>

accessing remote databases thru agent jobs

We have some agents jobs which are run on schedule.
The agent jobs call stored procedures created on
local databases.
We want to move the agent jobs to a different server
(remote server). How do we set up accessing the databases
from the remote server so that none of the agent jobs need to
be changed, or with minimum change.
TIA.You would have to set up linked severs. For more information and examples,
see sp_addlinkedserver, sp_addlinkedsrvlogin, sp_serveroption in SQL Server
Books Online.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message
news:1121880962.361317.54810@.g44g2000cwa.googlegroups.com...
> We have some agents jobs which are run on schedule.
> The agent jobs call stored procedures created on
> local databases.
> We want to move the agent jobs to a different server
> (remote server). How do we set up accessing the databases
> from the remote server so that none of the agent jobs need to
> be changed, or with minimum change.
> TIA.
>|||Narayana Vyas Kondreddi wrote:
> You would have to set up linked severs. For more information and examples,
> see sp_addlinkedserver, sp_addlinkedsrvlogin, sp_serveroption in SQL Serve
r
> Books Online.
I too thought about the linked server option, but it seems it
still requires using a linked server name as the qualifier
for the database name.
this is the original code of the Agent job
-- Add the job steps
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobstep @.job_id = @.JobID,
@.step_id = 1, @.step_name =
N'Purge', @.command = N'exec bts_CleanupDeadProcesses', @.database_name =
N'BizTalkMsgBoxDb',
@.server = N'', @.database_user_name = N'', @.subsystem = N'TSQL',
@.cmdexec_success_code = 0, @.flags
= 0, @.retry_attempts = 0, @.retry_interval = 1, @.output_file_name = N'',
@.on_success_step_id = 0,
@.on_success_action = 1, @.on_fail_step_id = 0, @.on_fail_action = 2
Now how do i make the agent job refer to a remote database at the
parameter @.database_name = N'BizTalkMsgBoxDb' without qualifying it
with the linked server.
Thanks for your help.|||Narayana Vyas Kondreddi wrote:[vbcol=seagreen]
> You would have to set up linked severs. For more information and examples,
> see sp_addlinkedserver, sp_addlinkedsrvlogin, sp_serveroption in SQL Server[/vbcol
]
That's what my initial guess was. However when I add
a linked server, in the SQL Agent Job, I still need to
qualify the database name from MyDB to LINKEDSERVER.MyDB.
is there a way to avoid it. That is, is there a way for
Agent job to know the context of server is LINKEDSERVER.
Thanks for your help.|||No. Instead you could have a constant linked server name, by adding an alias
on the server, using Client Network Utility, and by adding a linked server
using the alias name. this way you could change the server name, but still
keep the same alias name.
Anyway, makes me curious why are you movig the job to a different server?
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message
news:1121884794.983994.137000@.g43g2000cwa.googlegroups.com...
>
> Narayana Vyas Kondreddi wrote:
> That's what my initial guess was. However when I add
> a linked server, in the SQL Agent Job, I still need to
> qualify the database name from MyDB to LINKEDSERVER.MyDB.
> is there a way to avoid it. That is, is there a way for
> Agent job to know the context of server is LINKEDSERVER.
> Thanks for your help.
>|||Narayana Vyas Kondreddi wrote:
> No. Instead you could have a constant linked server name, by adding an ali
as
> on the server, using Client Network Utility, and by adding a linked server
> using the alias name. this way you could change the server name, but still
> keep the same alias name.
I am not sure how the above is relevant to my case. Please refer to
the code example I have posted in the other reply.

> Anyway, makes me curious why are you movig the job to a different server?
as you can see from the code snippet it is Biztalk jobs. We
are going to host our production server with a hosting company
and they do not support running application jobs on the
database server. So I am doing pro/con matrix to see how
much we have to change.|||Okay, you will have to make a change to the job step, if the server context
has to change.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message
news:1121888749.038267.117940@.g49g2000cwa.googlegroups.com...
> Narayana Vyas Kondreddi wrote:
> I am not sure how the above is relevant to my case. Please refer to
> the code example I have posted in the other reply.
>
> as you can see from the code snippet it is Biztalk jobs. We
> are going to host our production server with a hosting company
> and they do not support running application jobs on the
> database server. So I am doing pro/con matrix to see how
> much we have to change.
>