SQL Server Troubleshooting: Login Failed for User

    Since the installation of an SQL Server 2017 on a virtual machine (VM) in the Microsoft Cloud started to appear in the error log records with the following message:

Login failed for user '<domain>\<computer>$'. Reason: Could not find a login matching the name provided. [CLIENT: <local machine>]
Error: 18456, Severity: 14, State: 5.

   From the text it seemed like a permission problem, thing confirmed by the documentation (see [1]), the Error Number and State correspond to a „User Id is not valid“ situation. In a first step I attempted to give permissions to the local account (dollar sign included). The account wasn’t found in the Active Directory (AD), though by typing the account directly in the “Login name” I managed to give temporarily sysadmin permission to the account. The error continued to appear in the error log. I looked then at the accounts under which the SQL Services run - nothing suspect in there.

   Except the error message, which was appearing with an alarming frequency (a few seconds apart), everything seemed to be working on the server. The volume of  records (a few hundred thousands over a few days) bloating the error log, as well the fact that I didn’t knew what’s going on made me take the time and further investigate the issue.

  Looking today at the Windows Logs for Applications I observed that the error is caused by an account used for the Microsoft SQL Server IaaS Agent and IaaS Query Service. Once I gave permissions to the account the error disappeared.

   The search for a best practice on what permissions to give to the IaaS Agent and IaaS Query Service lead me to [2]. To quote, the “Agent Service needs Local System rights to be able to install and configure SQL Server, attach disks and enable storage pool and manage automated security patching of Windows and SQL server”, while the “IaaS Query Service is started with an NT Service account which is a Sys Admin on the SQL Server”. In fact, this was the only resource I found that made a reference to the IaaS Query Service.

   This was just one of the many scenarios in which the above error appears. For more information see for example  [3], [4] or [5].

References:
[1] Microsoft (2017) MSSQLSERVER_18456 [Online] Available from: https://docs.microsoft.com/en-us/sql/relational-databases/errors-events/mssqlserver-18456-database-engine-error?view=sql-server-2017
[2] SQL Database Engine Blog (2018) SQL Server IaaS Extension Query Service for SQL Server on Azure VM, by Mine Tokus Altug [Online] Available from:  https://blogs.msdn.microsoft.com/sqlserverstorageengine/2018/10/25/sql-server-iaas-extension-query-service-for-sql-server-on-azure-vm/
[3] Microsoft Support (2018) "Login failed for user" error message when you log on to SQL Server [Online] Available from: https://support.microsoft.com/en-sg/help/555332/login-failed-for-user-error-message-when-you-log-on-to-sql-server
[4] Microsoft Technet (2018) How to Troubleshoot Connecting to the SQL Server Database [Online] Available from: Engine https://social.technet.microsoft.com/wiki/contents/articles/2102.how-to-troubleshoot-connecting-to-the-sql-server-database-engine.aspx 
[5] Microsoft Blogs (2011)Troubleshoot Connectivity/Login failures (18456 State x) with SQL Server, by Sakthivel Chidambaram [Online] Available from: https://blogs.msdn.microsoft.com/sqlsakthi/2011/02/06/troubleshoot-connectivitylogin-failures-18456-state-x-with-sql-server/

SQL Server Troubleshooting: Searching the Error Log

    Searching for a needle in a haystack is an achievable task though may turn to be daunting. Same can be said about searching for a piece of information in the SQL error log. Fortunately, there is xp_readerrorlog, an undocumented (extended) stored procedure, which helps in the process. The stored procedure makes available the content of the error log and provides basic search capabilities via a small set of parameters. For example, it can be used to search for errors, warnings, failed backups, consistency checks, failed logins, databases instant file initializations, and so on. It helps identify whether an event occurred and the time at which the event occurred.

   The following are the parameter available with the stored procedure:

Parameter	Name	Type	Description
1	FileToRead	int	0 = Current, 1 or 2, 3, … n Archive Number
2	Logtype	int	1 = SQL Error Log and 2 = SQL Agent log
3	String1	varchar(255)	the string to match the logs on
4	String2	varchar(255)	a second string to match in combination with String1 (AND)
5	StartDate	datetime	beginning date to look from
6	EndDate	datetime	ending date to look up to
7	ResultsOrder		ASC or DESC sorting

Note:
If the SQL Server Agent hasn’t been active, then there will be no Agent log and the call to the stored procedure will return an error.

   Here are a few examples of using the stored procedure:

-- listing the content of the current SQL Server error log
EXEC xp_readerrorlog 0, 1

-- listing the content of the second SQL Server error log
EXEC xp_readerrorlog 1, 1

-- listing the content of the current SQL Server Agent log
EXEC xp_readerrorlog 0, 2

-- searching for errors 
EXEC xp_readerrorlog 0, 1, N'error'

-- searching for errors that have to do with consistency checks
EXEC xp_readerrorlog 0, 1, N'error', N'CHECKDB'

-- searching for errors that have to do with consistency checks
EXEC xp_readerrorlog 0, 1, N'failed', N'backups'

-- searching for warnings 
EXEC xp_readerrorlog 0, 1, N'warning'

-- searching who killed a session
EXEC xp_readerrorlog 0, 1, N'kill'

-- searching for I/O information
EXEC xp_readerrorlog 0, 1, N'I/O'

-- searching for consistency checks 
EXEC xp_readerrorlog 0, 1, N'CHECKDB'

-- searching for consistency checks performed via DBCC
EXEC xp_readerrorlog 0, 1, N'DBCC CHECKDB'

-- searching for failed logins  
EXEC xp_readerrorlog 0, 1, N'Login failed'

-- searching for 
EXEC xp_readerrorlog 0, 1, N'[INFO]'

-- searching for shutdowns 
EXEC xp_readerrorlog 0, 1, N'shutdown'

-- searching for a database instant file initialization event  
EXEC xp_readerrorlog 0, 1, N'database instant file initialization'

   If the error log is too big it’s important to narrow the search for a given time interval:

-- searching for errors starting with a given date 
DECLARE @StartDate as Date = DateAdd(d, -1, GetDate())
EXEC xp_readerrorlog 0, 1, N'error', N'', @StartDate

-- searching for errors within a time interval 
DECLARE @StartDate as Date = DateAdd(d, -14, GetDate())
DECLARE @EndDate as Date = DateAdd(d, -7, GetDate())
EXEC xp_readerrorlog 0, 1, N'', N'', @StartDate, @EndDate, N'desc' 

   The output can be dumped into a table especially when is needed to perform a detailed analysis on the error log. It might be interesting to check how often an error message occurred, like in the below example. One can take thus advantage of more complex pattern searching.

-- creating the error log table 
CREATE TABLE dbo.ErrorLogMessages (
    LogDate datetime2(0) 
  , ProcessInfo nvarchar(255)
  , [Text] nvarchar(max))

-- loading the errors 
INSERT INTO dbo.ErrorLogMessages
EXEC xp_readerrorlog 0, 1

-- checking the results 
SELECT *
FROM dbo.ErrorLogMessages

-- checking messages frequency 
SELECT [Text]
, count(*) NoOccurrences
, Min(LogDate) FirstOccurrence
FROM dbo.ErrorLogMessages
GROUP BY [Text]
HAVING count(*)>1
ORDER BY NoOccurrences DESC

-- getting the errors and their information 
SELECT *
FROM (
 SELECT *
 , Lead([Text], 1) OVER (PARTITION BY LogDate, ProcessInfo ORDER BY LogDate) PrevMessage
 FROM dbo.ErrorLogMessages
 ) DAT
WHERE [Text] LIKE '%error:%[0-9]%'

-- cleaning up 
--DROP TABLE IF EXISTS dbo.ErrorLogMessages 

   For those who don’t have admin permissions it is necessary to explicitly give execute permissions on the xp_readerrorlog stored procedure:

-- giving explicit permissions to account
GRANT EXECUTE ON xp_readerrorlog TO [<account_name>]

   Personally, I’ve been using the stored procedure mainly to check whether error messages were logged for a given time interval and whether the consistency checks run without problems. Occasionally, I used it to check for failed logins or sessions terminations (aka kills).

Notes:
Microsoft warns that undocumented objects might change in future releases. Fortunately, xp_readerrorlog made it since SQL Server 2005 to SQL Server 2017, so it might make it further…
The above code was tested also on SQL Server 2017.

Happy coding!

SQL Server Troubleshooting: Root Blocking Sessions

Unless special monitoring tools like Redgate’s SQL Monitor are available, when it comes to troubleshooting blocking sessions one can use the sp_who2 and sp_whoisactive stored procedures or the further available DMVs, which can prove useful upon case. In the case one just needs to find the root session that blocks other sessions, as it happens in poorly designed and/or heavily used databases, one can follow the chain of blockings provided by the sp_who2, though it requires time. Therefore, based on sp_who2 logic I put together a script that with the help of common tables expressions walks through the hierarchical chain to identify the root blocking session(s):

 ;WITH CTERequests
 AS ( -- taking a snapshot of requests  
	 SELECT ER.session_id  
	 , ER.request_id
	 , ER.blocking_session_id  
	 , ER.database_id   	  
	 , ER.status      
	 , ER.sql_handle   
	 , ER.command 
	 , ER.start_time
	 , ER.total_elapsed_time/(1000*1000) total_elapsed_time
	 , ER.cpu_time/(1000*1000) cpu_time
	 , ER.wait_type 
	 , ER.wait_time/(1000*1000) wait_time
	 , ER.reads 
	 , ER.writes 
	 , ER.granted_query_memory/128.0 granted_query_memory_mb
	 FROM sys.dm_exec_requests ER with (nolock)
	 WHERE ER.session_id > 50 – user sessions 
 ),  
 CTE  
 AS ( -- building the tree  
	 SELECT ER.session_id  
	 , ER.request_id
	 , ER.blocking_session_id  
	 , ER.database_id 
	 , ER.status  
	 , ER.command   
	 , ER.sql_handle   
	 , Cast(NULL as varbinary(64)) blocking_sql_handle   
	 , 0 level  
	 , CAST(ER.session_id as nvarchar(max)) sessions_path 
	 , ER.start_time 
	 , ER.total_elapsed_time
	 , ER.cpu_time
	 , ER.wait_type 
	 , ER.wait_time
	 , ER.reads 
	 , ER.writes 
	 , ER.granted_query_memory_mb
	 FROM CTERequests ER
	 -- WHERE ER.blocking_session_id <>0 -- only blocked sessions 
	 UNION ALL  
	 SELECT ER.session_id  
	 , ER.request_id
	 , BR.blocking_session_id  
	 , ER.database_id  
	 , ER.status  
	 , ER.command   
	 , ER.sql_handle   
	 , BR.sql_handle blocking_sql_handle   
	 , ER.level + 1 level  
	 , ER.sessions_path + '/' + Cast(BR.session_id as nvarchar(10)) sessions_path 
	 , ER.start_time
	 , ER.total_elapsed_time
	 , ER.cpu_time
	 , ER.wait_type 
	 , ER.wait_time
	 , ER.reads 
	 , ER.writes 
	 , ER.granted_query_memory_mb
	 FROM CTERequests br  
		  JOIN CTE ER 
			ON BR.session_id = ER.blocking_session_id   
		   AND BR.session_id <> ER.session_id  
 )  
 -- the final query   
 SELECT CTE.session_id 
 , CTE.blocking_session_id   
 , CTE.request_id
 , DB_NAME(CTE.database_id) database_name  
 , CTE.status   
 , CTE.start_time
 , BES.last_request_end_time   
 , CTE.level   
 , CTE.sessions_path   
 , CTE.command   
 , CTE.sql_handle   
 , est.text sql_text  
 , CTE.blocking_sql_handle   
 , best.text blocking_sql_text 
 , CTE.total_elapsed_time
 , CTE.cpu_time
 , CTE.wait_type 
 , CTE.wait_time
 , CTE.reads 
 , CTE.writes 
 , CTE.granted_query_memory_mb
 , TSU.request_internal_objects_alloc_page_count/128.0 allocated_mb
 , TSU.request_internal_objects_dealloc_page_count/128.0 deallocated_mb 
 , ES.login_name   
 , ES.[host_name]  
 , BES.[host_name] blocking_host_name  
 FROM CTE 
      LEFT JOIN sys.dm_exec_sessions ES with (nolock)
        ON CTE.session_id = ES.session_id   
      LEFT JOIN sys.dm_exec_sessions BES with (nolock)
        ON CTE.blocking_session_id = BES.session_id        
      LEFT JOIN ( -- allocated space
	  SELECT session_id
	  , request_id
	  , SUM(internal_objects_alloc_page_count) AS request_internal_objects_alloc_page_count
	  , SUM(internal_objects_dealloc_page_count)AS request_internal_objects_dealloc_page_count 
	  FROM sys.dm_db_task_space_usage with (nolock)
	  WHERE session_id > 50 
	  GROUP BY session_id
	  , request_id
      ) TSU
        ON TSU.request_id = CTE.request_id
       AND TSU.session_id = CTE.session_id 
      OUTER APPLY sys.dm_exec_sql_text(CTE.sql_handle) as est  
      OUTER APPLY sys.dm_exec_sql_text(CTE.blocking_sql_handle) as best 
 WHERE CTE.level IN (SELECT MAX(B.level) -- longest path per session
                 FROM CTE B  
                 WHERE CTE.session_id = B.session_id  
                 GROUP by B.session_id)  
 ORDER BY CTE.session_id

Notes:
The script considers only the user sessions (session_id>50), however the constraint can be left out in order to get also the system sessions.
Over the years I discovered also 1-2 exceptions where the logic didn’t work as expected. Unfortunately, not having historical data made it difficult to find the cause.
Killing the root blocking session usually makes the other blockings disappear, however it’s not always the case.
There can multiple root blocking sessions as well.
For the meaning of the above attributes see the MSDN documentation: sys.dm_exec_requests, sys.dm_exec_sessions, sys.dm_db_task_space_usage, sys.dm_exec_sql_text.
Be careful when killing the sessions (see my other blog post)!

Happy coding!

SQL Server Troubleshooting: Could not load file or assembly Microsoft.MSXML…

I’m not programming anymore as I used to do, though from time to time I still fancy some .Net programming. It’s not much, small applications or CLR-based libraries for SQL Server. Quite often, when I return to programming after a long pause it happens that I run into problems, finding that something that was working previously stopped working. During my last attempt I couldn’t load anymore one of the projects I worked on, receiving the following error:

“Could not load file or assembly ‘Microsoft.MSXML, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a’ or one of its dependencies. The system cannot find the file specified.”

Same happened when I tried to load other projects. I looked then into GAC in “C:\Windows\assembly” folder and saw no reference to Microsoft.MSXML dll. So I tried to install the msxml6.dll assembly in GAC however, never doing that, I run into another problem. In the meantime I tried to install the Visual Studio 2010 SP1, the MSXML 6.0 and even the Windows .Net SDK. All this without success. After several good hours, I returned to one of the forum posts (here) I run into in a first place. Thomas Sun was pointing that it might be a problem with the Microsoft Document Explorer 20xx. The solution was to reinstall it from  “C:\Program Files\Common Files\microsoft shared\Help 9\Microsoft Document Explorer 2008”. Once I did that everything was back to normal. At least until I’ll run into another issue.

After all this there is still one positive point: I managed to install SP1 and all the goodies it comes with, and I’m thinking here at the support for HMTL5. The downside – several good hours of lost time! I don’t want to think how much time I lost until now trying to solve things that were supposed to work in a first place – probably weeks, months…  That’s part of programmers’ life.

Disclaimer:
As Microsoft changed the whole structure of their support websites, most of the resources become unavailable. Therefore I had to remove the links pointing to the various sources.

SQL Server Troubleshooting: Who is Active

    I checked ReplTalk’s post on “waiting” statements, realizing that I had to write a similar query when troubleshooting performance of running queries in general, respectively blocking issues in particular. The novelty resides in Adam Machanic’s comment to the respective post, the link leading me to sp_whoisactive stored procedure which provides a collection of wait stats, lock information, outer command/batch, etc. The procedure is impressive from the point of view of its complexity (and number of lines), the author spending many hours on this piece of art. The parameters are well documented but not the logic, some additional information could be gathered from the several posts on this topic, however that won’t make easier reviewer’s work. After a first review I’m missing a few examples and scenarios in which to use the procedure, without them I’m kind of starting from 0. 

     I am a little reticent in using other people’s code when the code is too complex and requires too much time to be understood, especially when the respective code is not documented, requires a whole manual and/or the code isn’t easy to use. These aspects don’t decrease the merit of the person who wrote it but it decreases the overall utility of the respective code, because when people don’t understand the usability and/or range of applicability of a piece of code, the risk of people misusing the respective piece of code is quite high. From my perspective I find it more useful to have in place a set of queries and procedures that help you troubleshoot performance issues than having a stored procedure that attempts to do everything, requires many parameters, time to be debugged and understood. 

   SQL Server lacks in documentation describing in detail how its internal actually work. The stored procedure could be used to understand how various situations were handled, one of the first problems residing in identifying the piece of code corresponding to each scenario, “isolate” it and make most of it. Most probably a developer who’s trying to understand the respective code will need to break down the code in chunks, like I did, and use them in isolation in order to prove code’s logic. Sure, the followed approach depends also on each person’s skills, level of understanding and exploration techniques. As too much philosophy is not good, especially when divagating from the subject, I will end by inviting the reader to look over the respective stored procedure and, why not, to improve it, eventually derive more knowledge out of it.

Resources: SQL Server Troubleshooting I

    Last week the SQL Server troubleshooting-related topics came again on my table, and it’s actually quite an important topic. As I have disparate links I thought it makes sense to bring the links together in a post.

    When searching for information it’s always a good idea to start with the documentation or the support site, in what concerns SQL Server troubleshooting Microsoft has several valuable resources on what concerns performance issues, application performance, ad-hoc queries, blocking, stored procedure recompilation, clusters, etc.  Even if outdated, of interest could be also the Improving .Net Application Performance and Scalability resource,  with a chapter on SQL Server Performance and ADO.NET. Other resources could be found in Technet, for example Troubleshooting Performance Problems in SQL Server 2005, a similar document being available on SQL Server 2008 from MSDN. As a way to avoid the need for troubleshooting, it makes sense to check also the SQL Server Best Practices.

    There are several good books on this topic I heartily recommend, the first on my list is the book of C. Bolton et al, Professional SQL Server 2008 Internals and Troubleshooting. What’s interesting to note is that the book is available to browse and read on Scribd as part of a eBook Deal with Wiley, deal that includes several other programming and non-programming books (See Wiley’s profile on Scribd). The code for several chapters from the C. Bolton's book is available on Wiley’s website. Of interest are especially the chapters on DMV (dynamic management views) because performance troubleshooting often resumes in searching for hints in SQL Server internal tables. Another book, actually booklet, on DMV comes from Redgate, the SQL Server DMV Starter Pack, the pack being available also with a list of 30 sample scripts downloadable together as zip.

   Talking about scripts, there are many scripts available on DMV from Technet’s Script Repository. If you want to get an overview of your SQL Server configuration and health, you could check Diagnostic Information Queries available from Glen Berry’s blog for SQL Server 2005, respectively SQL Server 2008 and 2008R2.

SQL Server Troubleshooting: AdventureWorks requires FILESTREAM enabled II (one year later)

   I tried a few days ago to reinstall the AdventureWorks examples, I knew about the FILESTREAM issue mentioned in a previous post, though this time after I enabled FILESTREAM via Management Studio I got another error message:
     "AdeventureWorks2008 OLTP requires FILESTEAM which is not enabled for the instance you selected. FILESTREAM must be enabled BOTH via the SQL Server Configuration Manager and via Management Studio for this instance."

    I searched for the issues without any hits, then on how to install the sample and found a post on Stuart Cox’s Tech Punch blog.     I tried to set the FILESTREAM values in SQL Server Configuration Manager and didn’t worked, getting the following error message:    

"There was an unknown error applying the FILESTREAM settings. Check the parameters are valid"    

The search returned several hits but not for Vista. In the end following without success the script-based solution proposed on MSDN, I tried my luck in the register and using regedit tool I found the entry for Filestream in Software/Microsoft SQL Server/MSSQL10.MSSQLSERVER/MSSQLServer/Filestream, and changed Enabled Level to 2. Now the values in SQL Server Configuration Manager were updated and after I installed the AdeventureWorks example everything looks to be ok.

SQL Server Troubleshooting: Adventure Works installation error on Vista

 I tried to install the Adventure Works OLTP & DW on SQL Server 2008 RTM from CodePlex though I got an error:

  “The installer has encountered an unexpected error installing this package. This may indicate a problem with this package. The error code is 2738.”

Initially I thought that the problem was caused by existing Adventure Works installations made on a previous CTP version of SQL Server 2008, forgetting to uninstall them when I uninstalled the CTP version. Totally wrong! Doing a little research, I found first a post on CodePlex Discussion forum mentioning that the problem could be caused by VBScript runtime because, as Toms’Tricks blog highlights, VBScript and Jscript are not registered on Windows Vista. Wonderful! I just run regsvr32 vbscript.dll command and it worked! Another situation in which regsvr32 saved the day!

 I wonder why I haven’t got the same problem when I previously installed Adventure Works database on CTP version! Could it be because of Windows Vista SP1 changes (I installed Windows Vista SP1 after SQL Server CTP)?

SQL Server Troubleshooting: Microsoft SQL Server 2008 installation error

 This week I tried to install SQL Server 2008 however I got the following error: 

  “A previous release of Microsoft Visual Studio 2008 is installed on this computer. Upgrade Microsoft Visual Studio 2008 to the SP1 before installing SQL Server 2008.” 

 It’s true that I have previously installed Visual Studio 2008, though once I did that I checked if there are any updates and thus I installed SP1 too. I did a quick search on Google and the first results pointed me to an article o Microsoft Help and Support website: Visual Studio 2008 SP1 may be required for SQL Server 2008 installations. It didn’t make sense; in the end I’ve installed the server but enabled the installation of the following components: 

 • Management Tools (Basic or Complete) 

 • Integration Services 

 • Business Intelligence Development Studio 

 The server was installed without problems, so I tried to install the remaining components getting the same error as above. I had to stop at that point and today, giving more thought to the problem, I realized that the error could be caused by Microsoft Visual Studio 2008 Express edition, which I managed to install a few months back. Instead of uninstalling Microsoft Visual Studio 2008 it looked easier to uninstall the Express version, and once I did that, I managed to install the remaining components. Actually I checked before if there is a SP1 for Microsoft Visual Studio 2008 Express, I arrived at Microsoft Visual Studio 2008 Express Editions with SP1 page, though I remembered that I have to install the Web Developer, Visual Basic and C# 2008 separately and in the end I presumed that maybe it would be easier to uninstall the existing versions and try then to install SQL Server remaining components. I haven’t tried to install the Express editions with SP1 as now I have the Professional edition.

SQL Server Troubleshooting: Problems in SQL Server 2000 DTS Packages

    There are cases in which a problem can not be solved with a query, additional data processing being requested. In this category of cases can be useful to use a temporary table or the table data type. Once the logic built you want to export the data using a simple DTS project having as Source the built query.

    While playing with SQL Server 2005 (Beta 2) I observed that the DTS Packages have problem in handling temporary tables as well table data types.

1. Temporary table Sample:

CREATE PROCEDURE dbo.pTemporaryTable
AS
CREATE TABLE #Temp(ID int, Country varchar(10)) -- create a temporary table

-- insert a few records
INSERT #Temp VALUES (1, 'US')
INSERT #Temp VALUES (2, 'UK')
INSERT #Temp VALUES (3, 'Germany')
INSERT #Temp VALUES (4, 'France')   

SELECT * FROM #Temp -- select records

DROP TABLE #Temp – drop the temporary table

-- testing the stored procedure
EXEC dbo.pTemporaryTable

Output:
ID Country
    ----------- ----------
1 US
2 UK
3 Germany
4 France
(4 row(s) affected)

Trying to use the stored procedure as Source in a DTS package brought the following error message: Error Source: Microsoft OLE DB Provider for SQL Server Error Description: Invalid object name ‘#Temp’   

 I hoped this has been fixed in SQL Server 2005 (Beta 3) version, but when I tried to use the stored procedure as a Source I got the error:
Error at Data Flow Task [OLE DB Source [1]]: An OLE DB error has occurred. Error code: 0x80004005 An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80004005 Description: "Invalid object name '#Temp'."
Error at Data Flow Task [OLE DB Source [1]]: Unable to retrieve column information from the data source. Make sure your target table in the database is available. ADDITIONAL INFORMATION: Exception from HRESULT: 0xC020204A (Microsoft.SqlServer.DTSPipelineWrap)

2. Table data type sample:

CREATE PROCEDURE dbo.pTableDataType
AS
DECLARE @Temp TABLE (ID int, Country varchar(10)) -- create table

-- insert a few records
INSERT @Temp VALUES (1, 'US')
INSERT @Temp VALUES (2, 'UK')
INSERT @Temp VALUES (3, 'Germany')
INSERT @Temp VALUES (4, 'France')   

SELECT * FROM @Temp -- select records

-- testing the stored procedure
EXEC dbo.pTableDataType

Output:
ID Country
   ----------- ----------
1 US
2 UK
3 Germany
4 France
(4 row(s) affected)    

This time the error didn't appear when the Source was provided, but when the package was run: Error message: Invalid Pointer    

In Server 2005 (Beta 3) it raised a similar error:
SSIS package "Package6.dtsx" starting. Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning Information: 0x40043006 at Data Flow Task, DTS.Pipeline: Prepare for Execute phase is beginning Information: 0x40043007 at Data Flow Task, DTS.Pipeline: Pre-Execute phase is beginning SSIS package "Package6.dtsx" finished: Canceled.    

 I saw there will be problems because in Flat File Connection Manager’s Preview no data were returned. I hope these problems were solved in the last SQL Server Release, if not we are in troubles!

Possible Solutions:    
If the Stored Procedures provided above are run in SQL Query Analyzer or using ADO, they will work without problems. This behavior is frustrating, especially when the logic is really complicated and you put lot of effort in it; however there are two possible solutions, both involving affordable drawbacks:
1. Create a physical table manually or at runtime, save the data in it and later remove the table. This will not work for concurrent use (it works maybe if the name of the table will be time stamped), but for ad-hoc reports might be acceptable.
2. Built an ADO based component which exports the data to a file in the format you want. The component is not difficult to implement, but additional coding might be requested for each destination type. The component can be run from a UI or from a DTS package.
3. In SQL Server Yukon is possible to use the CLR and implement the logic in a managed stored procedure or table valued function.

Happy Coding!