Error in Crystal Reports After Adding a New View

After adding a new View using the Database Expert, I started getting the following error when running the report:

Database Connector Error: 'If tables are already linked then the join type cannot change.'
Failed to retrieve data from the database.
Error in File 04600test {FE1B1F52-937A-4372-9E11-E8220A9478FA}.rpt:
Database Connector Error: 'If tables are already linked then the join type cannot change.'


The issue seems to have been that the View was doing an Inner Join to Table A, while I was doing a Left Outer Join to the same table in the report (Database Expert).

The error disappeared after I did an Inner Join to the table in question, inside the report (Database Expert).

Using the SQL Round function with negative values

I had an issue where the Round finction was rounding the value '-19.395' as '-19.39'

The SQL statement was -

Select Round(((((Table.Value) * ISNULL(Table.Value, 1) * (-1))) - (Table.Value* 0.029)), 2) As Result

I was able to resolve the issue by 'casting' the value as a 'Money' data type.

Select Round(Cast(((((Table.Value) * ISNULL(Table.Value, 1) * (-1))) - (Table.Value * 0.029)) As Money), 2) As Result

With this, the value returned was '-19.40'

SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Invalid character value for cast specification".

I got the following errors in an existing SSIS package.

SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80004005  Description: "Invalid character value for cast specification".

[OLE DB Destination [2460]] Error: There was an error with input column "QtyAvail_Fld_24" (2570) on input "OLE DB Destination Input" (2473). The column status returned was: "Conversion failed because the data value overflowed the specified type.".

[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "OLE DB Destination" (2460) failed with error code 0xC0209029 while processing input "OLE DB Destination Input" (2473). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.  There may be error messages posted before this with more information about the failure.

[Source for GetFile [1]] Error: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.

Resolution:

The issue was that the QtyAvail_Fld_24 in my Destination (a SQL DB table), was a Smallint data type, while the source value (a Flat File), had overflowed that. 

I had to empty the destination table (backed up the data), then dropped and re-created the destination table (changed the data type to Varchar (to match the source).

I got this error in a VB Script Component task in an SSIS package: microsoft.sqlserver.dts.pipeline.cannotcreateusercomponentexception

I created the task as a copy of another task, which is working OK.

I was able to resolve the issue by deleting everything from the new task and copying everything from the existing task, into the new task.

SQL Server View does not return a newly added column

I added a column to a table in a SQL Server DB. A View which does 'Select *' from the table, did NOT return the new column.

Before the change, the definition of the table was -

Column_A Column_B Column_C

I added a new column so the table now has 4 columns -

Column_A Column_B Column_C Column_D

The definition of the view is:
Select * From Table

To get the view to return the new column (Column_D), I had to drop and re-create the view.

[Microsoft][SQL Server Native Client 11.0]Connection is busy with results for another command

Creating a custom SQL Trigger in the Dynamics SL Application DB started causing this error when deleting lines items from Order Manager Sales Orders.

---------------------------
SQL Server Message 93
---------------------------
[Microsoft][SQL Server Native Client 11.0]Connection is busy with results for another command

  Debugging info: select * from soline where cpnyid= '0040' and ordnbr= 'O0016328' and lineref= '00001'
  Cursor(RefreshCursor) select * from soline where cpnyid= '0040' and ordn
  Optional info: SqlState = HY000 NativeError = 0 ErrorMsg = [Microsoft][SQL Server Native Client 11.0]Connection is busy with results for another command pcbErrorMsg = 93
ODBCRowNumber = -1 Column = -1 SSrvrLine = 0 SSrvrMsgState = 0 SSrvrSeverity = 0 SSrvrProcname =  SSrvrSrvname =

---------------------------
OK
---------------------------

The solution was to add a 'Set Nocount On' statement at the beginning of the Trigger definition.