No SQL server agent on SQL 2005 Express

  • We have a couple of SQL 2005 Express edition servers, when I use SSMS to manage it, there is no SQL job agent while SQL agent service is running.

  • SQL Agent is not included with SQL Server Express.

  • How to manage the jobs on the Express edition?

  • There are some free third party tools here are two you can test drive.

    http://www.lazycoding.com/products.aspx

    http://www.codeproject.com/KB/database/SQLAgent.aspx

    Kind regards,
    Gift Peddie

  • tyang (2/3/2009)


    How to manage the jobs on the Express edition?

    Since there is no SQL Agent, there are no SQL Agent jobs to manage.

  • One way to do Agent tasks similar to other SQL edition would be to use windows scheduled task using the sqlcmd executable. Not very pretty but that does the job.

  • if no SQL Server Agent, how to remote connect the SQL Server?

  • SQL Server Agent is to schedule tasks inside SQL Server. Not the mean to connect to an instance, this is the SQL Browser Service task to get you that information (instance, ports, DAC and so on).

  • Megistal (3/30/2009)


    SQL Server Agent is to schedule tasks inside SQL Server. Not the mean to connect to an instance, this is the SQL Browser Service task to get you that information (instance, ports, DAC and so on).

    Now I see. But I'm getting this error when I try to connect to a server using server name "SERVER\SQLServer2005". But I won't get this error when I change the server name to "192.168.1.100\SQLServer2005".

    I already set Remote Connections to "using both TCP/IP and named pipes", added sqlservr.exe, sqlbrowser.exe, tcp port 1433 and udp port 1434 at windows firewall.

    Any idea why this error still occur? Is there any steps I left out?

  • Hummm from the IP you seems to be on a "home" network (Class C). DNS names are not resolved for this. If I saw it right you then need a DNS server which will resolved your machine IP, in other words a network infrastructure.

  • This is because SQL Server Express Edition by default uses dynamic ports for connecting to a TCP endpoint. You should go to SQL Server Configuration Manager -> Network Configuration -> Protocols for SQLExpress2005 -> TCP/IP. On IP Addresses tab, go to IPAll section, clear the TCP Dynamic Ports setting and set the TCP Port to 1433.

    This will work if you don't have any other applications using port 1433.

  • Damjan Djurasek, he is able to connect using the IP address therefore the port is not an issue. If the IP would have fail, then configuration, security configuration, sql browser service, instance and the like would have need to be checked.

    It can connect with IP but not with a DNS names thus the name resolver is the issue, in this case, from the class C IP, it is a home network and no dns name resolver server is available thus failing with the server name but succeeding with the IP.

    The path is (I'm no network guru guy but it should go as follows) Windows will try resolving the class C network using the primary DNS server ip, if it fail it will try the secondary and finally failing (the order and failing process can be configured but this is another topic, I assume default configuration here). However those DNS server IP are probably those from the internet service provider (assuming it is using a router with DHCP enabled to provided class C IP) and not from the "private" or business network DNS server.

  • Megistal, you are right. I didn't see the post where he said tht he can connect using the IP address.

  • Megistal (4/1/2009)


    Damjan Djurasek, he is able to connect using the IP address therefore the port is not an issue. If the IP would have fail, then configuration, security configuration, sql browser service, instance and the like would have need to be checked.

    It can connect with IP but not with a DNS names thus the name resolver is the issue, in this case, from the class C IP, it is a home network and no dns name resolver server is available thus failing with the server name but succeeding with the IP.

    The path is (I'm no network guru guy but it should go as follows) Windows will try resolving the class C network using the primary DNS server ip, if it fail it will try the secondary and finally failing (the order and failing process can be configured but this is another topic, I assume default configuration here). However those DNS server IP are probably those from the internet service provider (assuming it is using a router with DHCP enabled to provided class C IP) and not from the "private" or business network DNS server.

    After I turn off the Windows Firewall, I able to connect using "SERVERNAME\SQLExpress", any idea please? 🙂

  • Check for port 53, UDP In and Out. It is that port that's used to query a DNS name in a domain as well as a computer name in a workgroup.

    Otherwise you have reach my limit on network, sniff packets and check which ports were requested then adjust your firewall accordingly.

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply