Error:System.Data.SqlClient.SqlException: profile name is not valid

We were getting the following error

System.Data.SqlClient.SqlException: profile name is not valid

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)

at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)

at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)

at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)

at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)

at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)

at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)

at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()

The profile name is profile1@abc.com

user account associated to it was accountA@abc.com

We had to do the following to get rid of this error

Make sure that the account  accountA@abc.com is a role member of the Role DataBaseMailUserRole in the msdb database.This role is found under Security->Role in the msdb database

Set the profile profile1@abc.com to  public

use msdb

GO

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name =  'profile1@abc.com'
@principal_name = 'public',@is_default = 1;


Once we did the above two setps after creating the profile the error was gone and everything started to work as normal.=

Find missing indexes

--missing indexes on a single table
SELECT user_seeks * avg_total_user_cost * ( avg_user_impact * 0.01 )AS [index_advantage] ,migs.last_user_seek ,mid.[statement] AS [Database.Schema.Table] ,mid.equality_columns ,mid.inequality_columns ,mid.included_columns ,migs.unique_compiles ,migs.user_seeks ,migs.avg_total_user_cost ,migs.avg_user_impactFROM sys.dm_db_missing_index_group_stats AS migs WITH ( NOLOCK )INNER JOIN sys.dm_db_missing_index_groups AS mig WITH ( NOLOCK )
ON migs.group_handle = mig.index_group_handleINNER JOIN sys.dm_db_missing_index_details AS mid WITH ( NOLOCK )ON mig.index_handle = mid.index_handleWHERE
AND mid.database_id = DB_ID() mid.[statement]= '[statusupdates].[dbo].[Referrals]'ORDER
Look at the last_user_seek column and see how frequently it is seeked. This is an important factor to decide whether the suggested index is needed or not


I created the following index based on the equality column and there was no include column NONCLUSTERED INDEX IDX_referrals_Account
CREATE
ON Referrals (Account,Hospitalcode,Deleted,uploaded )
GO
/* to see when was the last index was updated*/
DBCC SHOW_STATISTICS ('referrals', 'IDX_referrals_hospitalcode') GO

/* after adding the new index I updated the statistics of the table */

EXEC
GO sp_updatestats
GO

Finding the bad indexes

Indexes are considered bad if the total writes > total reads



--Find bad indexes for a single table (writes > reads)--find bad indexs SELECT
user_seeks OBJECT_NAME(s.object_id) AS 'Table Name', i.name AS 'Index Name', i.index_id, user_updates AS 'Total Writes', + user_scans + user_lookups AS 'Total Reads', user_updates - ( user_seeks + user_scans + user_lookups ) AS 'Difference' FROM sys.dm_db_index_usage_stats AS s WITH ( NOLOCK ) INNER JOIN sys.indexes AS i WITH ( NOLOCK ) ON s.object_id = i.object_id AND i.index_id = s.index_id WHERE OBJECTPROPERTY(s.object_id, 'IsUserTable') = 1 AND s.database_id = DB_ID() AND user_updates > ( user_seeks + user_scans + user_lookups ) AND i.index_id > 1 ORDER BY 'Difference' DESC, 'Total Writes' DESC, 'Total Reads'

--Find bad indexes for a single table (writes > reads)SELECT OBJECT_NAME(s.object_id) AS 'TableName',i.name AS 'IndexName',i.index_id,SUM(user_seeks) AS 'User Seeks',SUM(user_scans) AS 'User Scans',SUM(user_lookups) AS 'User Lookups',SUM(user_seeks + user_scans + user_lookups) AS 'Total Reads',SUM(user_updates) AS 'Total Writes'FROM
sys.dm_db_index_usage_stats AS sINNER JOIN sys.indexes AS i ON s.object_id = i.object_idAND i.index_id = s.index_idWHERE
OBJECTPROPERTY(s.object_id, 'IsUserTable') = 1AND s.database_id = DB_ID()AND OBJECT_NAME(s.object_id) = 'referrals'GROUP BY OBJECT_NAME(s.object_id),i.name,i.index_idORDER BY 'Total Writes' DESC,'Total Reads' DESC ; ASC ;
 

SSIS : Export Data to a text file using a package

Open up SQL SERVER Business Intelligence Development Studio
Click on File-->New-->Project-->Integrated Services Project
sometimes "Integrated Services Project" is not visible , make sure that in the Project Types - Business Intelligence Projects is selected. Here is the screen shot.

Fig 1

Once the project name is given (seen in Fig 1) , Drag and drop Data Flow Task from the toolbox on the  Control Flow.( seen in Fig 2)

In the case of toolbox is not visible, go to View-->toolbox

Fig 2

Right click on the Connection Manager as shown below in Fig 3 and select New OLEDB Connection" This will lead you to a screen as shown in Fig 4.

Fig 3

This is where you will fill up all the necessary database connection as shown here in Fig 4 and click the Test Connection to make sure that you are able to connect to the database server as selected .

Fig 4

Now whene you hit OK in the Databse Connection manager you will see OLE DB Source object with green and red arrow in the Control Flow as seen in the Fig 5.

Fig 5

Now double click on the OLE DB Source Object on the Control Flow and the OLE DB Source Editor whill open up as shown below in Fig 6

Highlight the Conenction Manager on the left as you will be filling up the information to exract data from the database now.
In the OLE DB connection Manager dropdown select the Conenction Manager that was created in Step 4.
Select SQL Command in the daat Access mode. We use this mode in this example as we are using a query to get the data from the table.
In the sql Command text box write the query that you are goign to use to extract the data from the database and export out to the flat file.

Fig 6

Now, highlight the columns in the left that will show all the colums that your query will pull and the columns to be written out in the flat file. You can check one of the columns off if you don't want the column to be exported to the file. Leave the error output section as it is . WE are not doing anything with this section in this example.

Fig 7

Now from the tool box, navigate to the Data Flow Destination section and drag and drop the Flat File Destination Item in the Data Flow and expand the green arrow to connect to the Flat File Destination item as shown below picture.

Fig 8

Right click again in the Conenction Mager section and select the New Flat file Connection and thsi will pull up a Flat File Destination editor as show below.

Fig 9

Fig 10

Creating Composite nonclustered index

CREATE NONCLUSTERED INDEX IX_InEligible_ptno_Uploaded
ON [271_InEligible] (ptno, uploaded)



--Table Name [271_InEligible]
indexed columns ptno, uploaded

Error:Row handle referred to a deleted row or a row marked for deletion

We got the error "Row handle referred to a deleted row or a row marked for deletion" from a sql job ( that calls a stored procedure) after we moved to sql 2008 R2. When we were running this job in sql 2000 it was running fine.
I had been looking for a solution to fix this and most of the internet articles points to the "mising primary key" issue.
The stored procedure moves the data from TABLE1 to TABLE2. TABLE2 did not have any primary key as this was just used as an intermediate table. I added the primary key to the TABLE2, but the job still continued to fail now and then with the above error.
Upon further look at the code I saw the following code in the stored proc that the job was calling

BEGIN TRANSACITON TRAN1

delete w from DATA2LINK.statusupdates.dbo.workqueue w
INNER JOIN DataUpdate_DTS d2
ON w.ptno = d2.ptno
AND status_Id = 6



SELECT @intErrorCode = @@ERROR
IF (@intErrorCode <> 0) GOTO Error_handler


--remove any deleted(declined) Data update request from workqueue
DELETE w FROM DATA2LINK.statusupdates.dbo.workqueue w
INNER JOIN DATA2LINK.statusupdates.dbo.DataUpdate d
ON w.ptno = d.ptno
AND w.status_Id = 6
AND d.deleted = 1


The culprit seems to be the second delete query because we are referring already marked for deletion row(in the first query) . I removed the second delete statement .

The job has not failed since then, though it has been only few days since the code change. I will revisit again, but for now I consider this as the resolution for the above error.