Communication Link Failure

Thanks in advance for any help. I really appreciate it.
I am getting a strange error message when connecting to a single Sql Server
all of the sudden.
From enterprise manager the error is:
A connection could not be established to ServerName.
Reason: Communication link failure
Please verify Sql Server is running and check your Sql Server registration
properties (by right-clicking on the ServerName node) and try again.
From Query Analyzer the error is:
Unable to connect to server ServerName
ODBC: Msg 0, Level 16, State 1
[Microsoft][ODBC Sql Server Driver]Communication link failure
I am running Sql 2000 SP 3. Both my pc and the server are running Windows
2000.
If I use a different pc under the same login I am able to connect to the
server so the problem must be on my individual pc. I can also connect to
other Sql servers from the pc that is giving me problems. I have tried
uninstalling and reinstalling Sql and I get the same error message.
Does anyone have any idea as to the cause of this?Update...
I am able to register the server via IP address but not server name. When I
register by IP I get the same message that says:
SQL Server registration failed because of the connection failure displayed
below. Do you wish to Register anyway?
Communication link failure
If I click Yes I can then expand the server and view the databases as
normal. When I register by server name I get the same error message but I
still get the Communication link failure when expanding the server.
Help please!!!

Communication Link Failure

Thanks in advance for any help. I really appreciate it.
I am getting a strange error message when connecting to a single Sql Server
all of the sudden.
From enterprise manager the error is:
A connection could not be established to ServerName.
Reason: Communication link failure
Please verify Sql Server is running and check your Sql Server registration
properties (by right-clicking on the ServerName node) and try again.
From Query Analyzer the error is:
Unable to connect to server ServerName
ODBC: Msg 0, Level 16, State 1
[Microsoft][ODBC Sql Server Driver]Communication link failure
I am running Sql 2000 SP 3. Both my pc and the server are running Windows
2000.
If I use a different pc under the same login I am able to connect to the
server so the problem must be on my individual pc. I can also connect to
other Sql servers from the pc that is giving me problems. I have tried
uninstalling and reinstalling Sql and I get the same error message.
Does anyone have any idea as to the cause of this?
Update...
I am able to register the server via IP address but not server name. When I
register by IP I get the same message that says:
SQL Server registration failed because of the connection failure displayed
below. Do you wish to Register anyway?
Communication link failure
If I click Yes I can then expand the server and view the databases as
normal. When I register by server name I get the same error message but I
still get the Communication link failure when expanding the server.
Help please!!!

Common Table Expression Issue

I am playing around with Common Table Expressions in SQL Server 2005 and am noticing a strange issue.

SQL Server Version: Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Service Pack 2

Please find a query below that works on AdventureWorks.

Purpose of query

The query starts from the CEO of the company (ManagerID = null) and there is a recursive portion that it uses to find all the direct reports.

What I am seeing

Look at the recursive query where I have made the field, Title, really big and bold. This field always returns, "Chief Executive Officer" - the person at the very top of the food-chain.

What I expect to see

I thought that you should see the title of your immediate supervisor (the person who you directly report to). NOTE: Not everybody reports directly to the CEO.

Query

USE AdventureWorks;

GO

WITH DirectReports (ManagerID, EmployeeID, Title, Level)

AS

(

-- Anchor member definition -- Start from the CEO of the company

SELECT e.ManagerID, e.EmployeeID, e.Title, 0 AS Level

FROM HumanResources.Employee AS e

INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh

ON e.EmployeeID = edh.EmployeeID AND edh.EndDate IS NULL

WHERE ManagerID IS NULL

UNION ALL

-- POSSIBLE PROBLEM! with this part of the query.

-- Recursive member definition for getting direct reports

SELECT e.ManagerID, e.EmployeeID, d.Title, Level + 1

FROM HumanResources.Employee AS e

INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh

ON e.EmployeeID = edh.EmployeeID AND edh.EndDate IS NULL

INNER JOIN DirectReports AS d

ON e.ManagerID = d.EmployeeID

)

-- Statement that executes the CTE

SELECT ManagerID, EmployeeID, Title, Level

FROM DirectReports

I was able to get the desired results with the following query:

USE AdventureWorks;

GO

WITH DirectReports (ManagerID, EmployeeID, Title, Level)

AS

(

-- Anchor member definition -- Start from the CEO of the company

SELECT e.ManagerID, e.EmployeeID, e.Title, 0 AS Level

FROM HumanResources.Employee AS e

INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh

ON e.EmployeeID = edh.EmployeeID AND edh.EndDate IS NULL

WHERE ManagerID IS NULL

UNION ALL

-- Recursive member definition for getting direct reports

SELECT e.ManagerID, e.EmployeeID, e2.Title, Level + 1

FROM HumanResources.Employee AS e

INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh

ON e.EmployeeID = edh.EmployeeID AND edh.EndDate IS NULL

INNER JOIN HumanResources.Employee AS e2

ON e.managerId = e2.employeeId

INNER JOIN DirectReports AS d

ON e.ManagerID = d.EmployeeID

)

-- Statement that executes the CTE

SELECT ManagerID, EmployeeID, Title, Level

FROM DirectReports

Can somebody explain why I am not able to get the Title of the Immediate supervisor in the first query? It looks like the Title of the very first row is propagating during recursion. Is this how CTEs are supposed to behave?

Thanks!

Radha Mukkai

The anchor member's title is being propagated to all rows simply because you're not introducing any other Employee title into the two SELECT statements that form the CTE.

During the first recursion you return d.Title which is actually the title belonging to the anchor member. You now have two rows with the Title of the anchor member, the recursion then continues keeping the value of d.Title constant each time.

To keep the CTE simple you could use the code below as an alternative to your second example.

Chris

Code Snippet

WITH DirectReports (ManagerID, EmployeeID, Title, LEVEL)
AS
(
-- Anchor member definition -- Start from the CEO of the company
SELECT e.ManagerID, e.EmployeeID, 0 AS LEVEL
FROM HumanResources.Employee AS e
INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
ON e.EmployeeID = edh.EmployeeID AND edh.EndDate IS NULL
WHERE ManagerID IS NULL
UNION ALL

-- Recursive member definition for getting direct reports
SELECT e.ManagerID, e.EmployeeID, LEVEL + 1
FROM HumanResources.Employee AS e
INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
ON e.EmployeeID = edh.EmployeeID AND edh.EndDate IS NULL
INNER JOIN DirectReports AS d
ON e.ManagerID = d.EmployeeID
)

-- Statement that executes the CTE
SELECT dr.ManagerID,
dr.EmployeeID,
CASE WHEN dr.ManagerID IS NULL
THEN ( SELECT e2.Title
FROM HumanResources.Employee e2
WHERE e2.EmployeeID = dr.EmployeeID),
ELSE em.Title
END AS Title,
dr.LEVEL
FROM DirectReports dr
LEFT JOIN HumanResources.Employee AS em
ON dr.managerId = em.employeeId

|||Your logic is kinda messed up here.

Let's talk through the pseudo code a little.

1 - Populate DirectReports with the CEO, using "CEO" as Title.
2 - Populate the DirectReports with the people that report to anyone in the DirectReports table, using the Title that's already in there as the new value for Title.
3 - Repeat from 2.

So you see, you're never actually putting any new Titles in there. Your source of the Titles is your existing list of Titles, which is just "CEO".

To solve this, you could store both titles in the CTE, or just fetch the Manager's title after you've created the CTE.

Hope this helps,

Rob|||

Thanks Rob and Chris. I realized my mistake after mentally walking through the recursion tree.

Thanks,

Radha Mukkai

Comma Separated Output with .TXT extension

I have a somewhat strange situation where I need the output of a query thrown into a file with a .txt extension but I need the data in comma separate form. Any idea how I would get this accomplished?
Thanks in Advance,
TechRickIf you use bcp (from the command-line) you can specify the full filename

- Andy Abel|||You could use DTS. The fast way would be to choose the DTS Import/Export Wizard from the menu. Choose your source, then set your destination as a text file. Third select Query as your copy type, then input your query. Set your delimitor to be commas on the next screen. Then run, about 7 clicks to do.
You can also save the package to run again or edit it in DTS.|||Originally posted by achorozy
You could use DTS. The fast way would be to choose the DTS Import/Export Wizard from the menu. Choose your source, then set your destination as a text file. Third select Query as your copy type, then input your query. Set your delimitor to be commas on the next screen. Then run, about 7 clicks to do.
You can also save the package to run again or edit it in DTS.

Thanks so much for the info. I'm giving it a go but I'm running into an error. Perhaps you or someone else might know how to fix it.

Upon running the package I get this error: "Incomplete File Format Information - File Cannot Be Opened."

I don't know if this has anything to do with it, but I had to jimmy-rig the package to build it. I am using 3 temporary tables and 4 views in my query. DTS would not let me finish the package unless the main temporary table was already created. So, instead of having the query build it I just have the query delete and recreate it at the end so it is blank.

Any additional help would be appreciated.

TechRick|||Can you post the dts package ? Also, which version of sql server are you using - including service pack ?|||Originally posted by rnealejr
Can you post the dts package ? Also, which version of sql server are you using - including service pack ?

I am running 8.00.384 SP1 I believe.

Anyway, I was able to work it out by breaking the task up into smaller steps and running some of them through the job scheduler. I just had the last step run under the DTS package so I could get the output I wanted.

I had to break it up because I always seem to be above the 3200 charater limit for my queries to run as a job. Either my queries are too long or the job scheduler doesn't give enough characters...

Thanks for your assistance.

TechRick