Error: SSIS package : A rowset based on the SQL command was not returned by the OLE DB provider

I am trying to use a stored procedure in the OLE DB source, I am using the SQL Command for the Data Access mode.  It returns values when using the preview but when I test the SSIS package I receive the error, "A rowset based on the SQL command was not returned by the OLE DB provider


Solution

I just had to add SET NOCOUNT ON in the begining of the stored procedure that I was using.

Replacing multiple occurences of space with one space in a string

Given string :  THIS                IS                A                 BAD                 STRING
Out put string : THIS IS A BAD STRING

The Logic
-----------------

• In the given string replcae the space with a token (e,g. ~ in this case) and a space  e.g. '~ '
• Repace the space and token in the string result from step 1 with  an empty string.' ~'
• Replace the result of the above atring with a token and the space

DECLARE
@str AS VARCHAR(1000) = 'THIS IS A BAD STRING',@Token AS VARCHAR(1) = '~'

SELECT REPLACE(REPLACE(REPLACE(@str,' ','~ '),' ~',''),'~',' ')

Counting the occurence of a substring within a string

Today I learnt a special techinque from SQL Server magazine to find the total number of substrings within a string. The logic is so simple that I am surprised why I never used it.
E.g. abcdballmnopballqrstball
We need to find the total number of occurences of the substring "ball" in the given string.
 Logic
--------------
Find the substring ball and replace it with ''   e.g.abcdmnopqrst
Find the length of the new string                   12
Subtract the new length from the lenth of the original string  24-12 = 12
Divide the new number with the length of the substring   12/4 = 3

Here is the script - taken form the November issue of SQL server magazine

DECLARE
@str       AS VARCHAR(1000) = ' abcdballmnopballqrstball ';
@substr  AS  VARCHAR(1000) = 'ball';


SELECT  LEN(@str) - LEN(REPLACE(@str,(@substr,''>/LEN(@substr)

This is so awesome!

COALESCE - Selecting each record with all not nul values

I had three rows of data that had the same acount number but differnt variation in the three columns. I had to select each account with not null column . This was achieved by the following...

My data
create table #tmpcol(c219 int, c220 int,c221 int)
INSERT INTO #tmpcol
SELECT null,220,null
INSERT INTO #tmpcol
SELECT 219,null,null
INSERT INTO #tmpcol
SELECT null,null,221


select top 1 * from #tmpcolorderby coalesce(100*c219,10*c220,c221) desc

Shrink the transaction log file

The steps needed to shrink the transaction log file
Scenario: The database is in FULL RECOVERY mode and the transaction log file was growing to more than 2GB. We were taking the transaction logbackups in every 1 hour inteval. However the transaction log file still grew because at one point someone just set the recovery mode to FULL and there was no transaction log backups set up. The following steps were taken to reduce the siz of the log file.

--check the log file name
select * from sys.database_files

--Truncate the log by changing the database recovery model to SIMPLE. ( checkpoint occurs-frees up VLFs)  

ALTER DATABASE ARCHIVEhcfsdatabase
 SET RECOVERY SIMPLE;
 GO

-- Shrink the truncated log file the smalles size possible
DBCC SHRINKFILE (ARCHIVEhcfsdatabase_Log,0, TRUNCATEONLY);
GO

--reset the log file size to 64MB

ALTER DATABASE archivehcfsdatabase
Modify FIle(Name = 'ARCHIVEhcfsdatabase_Log',
,Size = 1024
)
 -- Reset the database recovery model.
ALTER  DATABASE ARCHIVEhcfsdatabase
SET RECOVERY FULL;
GO

--Do a full Back up - Until a full back up is done the database is not set to full recovery mode--

Drop a database

1. You have to be in the master database.
2. Take the database to the single user mode.
3. Drop the database.

use master;GO
alter database [databaseName]
set single_user with rollback immediate;
drop database [databaseName];

Creating a XML file from SQL data using VB.net

The project involved creating a xml file in the following format with the data from the SQLserver 2008 tables.

File Sample
<claimTrackingRequest>
<Target>
<identifier>1015100236</identifier>
<Type>RCV</Type>
<Comment>
<Code />
<Text>07/29 - comment test1 </Text>
</Comment>
</Target>
<Target>
<identifier>1015700095</identifier>
<Type>RCV</Type>
<Comment>
<Code />
<Text>07/25 - comment test1</Text>
</Comment>
<Comment>
<Code />
<Text>07/26 - comment test2 </Text>
</Comment>
<Comment>
<Code />
<Text>07/26 - comment test2 </Text>
</Comment>
</Target>
</claimTrackingRequest>

The above result was returned by the stored proc PROC_NOTES_STATUSCHANGENOTES_BY_HOSCODE_SELECT_CNA_newformat
( The stored procedure details is listed at the end of the post)
I am using VB.NET to write the result returned by the stored proc to a file with extension XML
I am using a DATASET to pull the result and then using the STREAMWRITER to writer the dataset result to the file.

Sub Main()　
Dim sqlConn As New SqlConnection
sqlConn.ConnectionString = "Data Source=sqlsvr1;Initial Catalog=db01;Integrated Security=SSPI;"Dim sqlCmd As New SqlCommandsqlCmd.CommandText = "PROC_NOTES_STATUSCHANGENOTES_BY_HOSCODE_SELECT_CNA_newformat"sqlCmd.CommandType = CommandType.StoredProcedure
sqlCmd.Connection = sqlConn
sqlCmd.CommandTimeout = 120
sqlCmd.Parameters.Add("@DTBEGIN", SqlDbType.Date).Value = "07/26/2011"sqlCmd.Parameters.Add("@DTEND", SqlDbType.Date).Value = "07/29/2011"Dim ds As New DataSetTrysqlConn.Open()
Dim a As System.Data.SqlClient.SqlDataAdaptera = New SqlDataAdapter(sqlCmd)
a.Fill(ds)
sqlConn.Close()

Using writer As StreamWriter = New StreamWriter("c:\test\notes.xml", True)
writer.Flush()
For Each _row As DataRow In ds.Tables(0).Rows
writer.Write(_row.Item(0).ToString())
Nextwriter.Close()
End Using
Catch ex As ExceptionEnd Try　
End Sub

 /*The stored proc returning the XML result is as follows */

--The proc is using XML Query

USE [db01]GO/****** Object: StoredProcedure [dbo].[PROC_NOTES_STATUSCHANGENOTES_BY_HOSCODE_SELECT_CNA_newformat] Script Date: 08/03/2011 12:48:43 ******/SET
GO
SET
GO
CREATE ANSI_NULLS ON QUOTED_IDENTIFIER ON PROCEDURE [dbo].[PROC_NOTES_STATUSCHANGENOTES_BY_HOSCODE_SELECT_CNA_newformat] (@DTBEGIN SMALLDATETIME, @DTEND SMALLDATETIME)AS/*turn these off so that it wont error if it trys to divide by Zero when doing calcs*/SET
SETARITHABORT OFF ANSI_WARNINGS OFF/*Create a temp table*/CREATE TABLE #TEMPTBLCNA (
RECORD_NUM
C1 INT NULL, VARCHAR(1) NULL,COMMENT_CODE VARCHAR(100) NULL,STATUS_CODE INT NULL,STATUS_DESC VARCHAR(200) NULL,COMMENT VARCHAR(1500) NULL,ACCOUNT VARCHAR(12) NULL,HOSPITAL INT NULL,DATEENTERED DATETIME NULL,PTNO INT NULL,NOTEID INT IDENTITY(1,1) )/*GET 100's*/INSERT INTO #TEMPTBLCNA (RECORD_NUM, C1,COMMENT_CODE,STATUS_CODE,STATUS_DESC,COMMENT,ACCOUNT,HOSPITAL,DATEENTERED,PTNO)SELECTMIN(C.RECORD_NUM), LEFT(COMMENT_CODE,1),C.COMMENT_CODE,LEFT(C.COMMENT_CODE, 3),m.[STATUS],c.COMMENT,RIGHT(DBO.STRIP_NONNUMERIC_12(P.ACCOUNT), 12),p.HOSPITAL, CAST(CONVERT(VARCHAR(10),C.DATEENTERED,101) AS DATETIME),P.PTNOFROMCOMMENTS C INNER JOIN PATIENT_TABLE P ON C.PTNO = P.PTNOINNER JOIN MasterCodeList mON m.NewCode = LEFT(C.COMMENT_CODE, 3)
WHERE CAST(CONVERT(VARCHAR,C.DATEENTERED,101) AS DATETIME) BETWEEN '07/22/2011' AND '07/29/2011'
GROUP BY P.PTNO,P.ACCOUNT,C.Comment,C.COMMENT_CODE,p.HOSPITAL,C.DATEENTERED,LEFT(COMMENT_CODE,1),m.[status]ORDERBY P.PTNO--select * from #TEMPTBLCNASELECT
( c.ACCOUNT as 'identifier', 'RCV' as 'Type', SELECT '' AS 'Code',a.comment AS 'Text'
FROM #TEMPTBLCNA aWHERE a.ptno = c.ptnoFOR XML PATH(''), TYPE) AS 'Comment'
FROM #TEMPTBLCNA cgroup by c.ptno,c.ACCOUNTFOR XML PATH('Target'), ROOT('claimTrackingRequest')
DROP TABLE #TEMPTBLCNASET
SET ARITHABORT ON ANSI_WARNINGS ON