The application called an interface that was marshalled for a different thread

When an SSIS package is validating a large Excel file (over 600,000 records), I get this error:

'The application called an interface that was marshalled for a different thread'

I was able to resolve the issue by setting the 'Delay Validation' property to True on all objects in the package.

MS Dynamics SL - System Database is not available in the Find Database screen (98.000.01)

This is happening after upgrading from Dynamics SL 2011 to Dynamics SL 2015.

The issue was caused by the database not being marked as 'TRUSTWORTHY' in SYS.DATABASES (Master Database).

Ran the following statement to correct the issue (replace SYSDBNAME with the name of your 'missing' database).

ALTER DATABASE SYSDBNAME SET TRUSTWORTHY ON;

I have an SSIS package with a Foreach Loop Container that reads Excel files in a specified directory. I get an error opening the file: Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Envision" failed with error code 0xC0209303. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

The variable that I assign the Excel file path to does get the correct value, however the package can't open the file.

I found articles describing that I need to supply the actual Excel file Connection String in the variable, not just the file path. I had some issues doing that so instead I assigned the Excel Connection String inside a script task. That solved my issue.


Dim strFile As String
strFile = Dts.Variables("User::XLSX_File").Value.ToString
Dts.Connections("Excel Connection Manager").ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strFile + ";Extended Properties=""Excel 12.0;HDR=YES"";"
Dts.Variables("User::ExcelConnString").Value = Dts.Connections("Excel Connection Manager").ConnectionString.ToString

Only subsequently I found that the Excel Connection Manager has a property called ExcelFilePath. So it is possible I didn't have to add the Script Task :)

I am trying to generate an XML string based on data in two SQL tables. One contains Order Header data, the other one Line Item data.

My problem is that I can't get the Line Item info to appear properly as multiple elements within a single order:

This is the SQL statement:

Select 

    LTRIM(RTRIM(H.CustPONbr)) As "Transactions/Transaction/CustomerOrdNumber",             

    (

    Select LTRIM(RTRIM(InvtID)) As "data()" From X888_Amazon_Order_Line L1

    Where L1.CpnyID = H.CpnyID And L1.CustPONbr = H.CustPONbr

    FOR XML PATH (''), ELEMENTS

    ) As "Transactions/Transaction/LineItems/LineItem/InvtId"

From X888_Amazon_Order_Header H (nolock)

 where h.CustPONbr = '99999014'

For XML PATH ('ProcessEngineSubmission'), Root ('XML'), ELEMENTS

This is the result I get:

<XML>

    <Transactions>

      <Transaction>

        <CustomerOrdNumber>99999014</CustomerOrdNumber>

        <LineItems>

          <LineItem>

            <InvtId>TEST 1235 TEST 1234</InvtId>

          </LineItem>

        </LineItems>

      </Transaction>

    </Transactions>

</XML>

If I execute the inner select (replacing "data()" with InvtId), I get what I am trying to achieve:

<InvtId>TEST 1235</InvtId>

<InvtId>TEST 1234</InvtId>

I posted about this on Stackoverflow.com and got the solution there - http://stackoverflow.com/questions/35720120/how-to-generate-xml-using-sql-path-mode-with-line-items

"Nested selects need the ,TYPE extension to come back as XML"

Select 

    LTRIM(RTRIM(H.CustPONbr)) As "Transactions/Transaction/CustomerOrdNumber",             

    (

    Select LTRIM(RTRIM(InvtID)) As "data()" From X888_Amazon_Order_Line L1

    Where L1.CpnyID = H.CpnyID And L1.CustPONbr = H.CustPONbr

    FOR XML PATH (''), TYPE

    ) As "Transactions/Transaction/LineItems/LineItem/InvtId"

From X888_Amazon_Order_Header H (nolock)

 where h.CustPONbr = '99999014'

For XML PATH ('ProcessEngineSubmission'), Root ('XML'), ELEMENTS

SSIS OLE DB Source changes how Date values are returned

I am creating an SSIS package which is reading from a SQL Server database. The data returned needs to be imported into an ERP system.

The SQL statement I use in the OLE DB Source returns the Date values formatted this way: 01/01/1900. The dates need to be formatted this way in order for the ERP import to work. So this is working fine when the SQL statement is run in SQL Management Studio.

However when I execute the statement as part of the task in the SSIS package, the Date values are returned formatted like this: 1900-01-01. This does not work for my ERP import.

The only way I found which let me keep the format I need (01/01/1900) was to return the Date columns as Strings enclosed in double quotes ("01/01/1900").

Then I added a Derived Column task to remove the double quotes using this expression: REPLACE(Col9,"\"","")

SQL - Build a comma separated list of string values which can be used with an 'IN' statement

This is what I used to take a list of values and create one string to use with an 'IN' statement.

 Declare @Val1 Varchar(20) = 'V1'
 Declare @Val2 Varchar(20) = NULL
 Declare @Val3 Varchar(20) = 'V3'
 Declare @Val4 Varchar(20) = 'V4'


 Declare @Val_List Varchar(8000)
 Declare @Select_SQL Varchar(8000)

 Set @Val_List =
Case When @Val1 IS NULL Then '' Else + '''' + @Val1 + '''' End  
    + Case When @Val2 IS NULL Then '' Else + ',' + '''' + @Val2 + '''' End  
    + Case When @Val3 IS NULL Then '' Else + ',' + '''' + @Val3 + '''' End  
    + Case When @Val4 IS NULL Then '' Else + ',' + '''' + @Val4 + '''' End;
   
   
 Set @Select_SQL = 'Select * From Table_Name Where Val In (' + @Val_List + ')';

 Print @Select_SQL;

-- This is the result:

Select * From Table_Name Where Val In ('V1','V3','V4')

Error Cannot open the datafile \\server\share\dir\filename.csv when reading from a CSV file in SSIS

I am getting this error when a Flat File connection tries to read a CSV file. The path to the file, including the file name is correct.

The import works fine when the path to the file is hard-coded. It does NOT however work (with the above error), when I set the Flat File Connection Manager Connection String Property in a VB Script Task.

I am using this to set the Connection String and it does get set correctly. A Variable (strFileLocationFileName contains the File Path and File Name).

ConnectionManager.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFileLocationFileName & ";Extended Properties=""text;HDR=YES;FMT=Delimited"";"

The issue was resolved when I removed this Script Task and instead assigned the File Name and File Location to the Flat File Connection Manager Connection String as an Expression using the strFileLocationFileName Variable.

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.

Undo a Sent Email in Gmail

Ran into this great setting in Gmail allowing you to Undo a Sent email.

Please use this link to see instructions on how to enable the feature - http://www.businessinsider.com/how-to-undo-a-sent-email-in-gmail-2013-12

odbc_fetch_array(): SQL error: [Microsoft][ODBC SQL Server Driver]Invalid Descriptor Index, SQL state S1002 in SQLGetData

Web Site (using PHP) gets error executing SQL Stored Procedure - odbc_fetch_array(): SQL error: [Microsoft][ODBC SQL Server Driver]Invalid Descriptor Index, SQL state S1002 in SQLGetData

This is the Stored Procedure definition. 

Placing the Text fields at the end of the SQL query. Used this article - http://forums.codeguru.com/showthread.php?127276-Invalid-Descriptor-Index%92&p=342672#post342672

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Alter Procedure [dbo].[XEVE_Note_Select]
-- values here passed by web page to stored proc
@EventID Int

As
Set NoCount On

SELECT NoteID, EventID, NoteType, WinUser, SolUser, Crtd_Datetime, Lupd_Datetime, NoteText
FROM XEVE_Notes
WHERE EventID = @EventID
ORDER BY NoteID


Set NoCount Off

SQL Server - Do Not Fire SQL Trigger when table is updated by an SSIS Package

I used this logic so that my Update/Insert trigger would not fire when updates/inserts are being made by SSIS packages:


Declare @Program_Name Char (256)

Set @Program_Name = (Select Top 1 program_name From Master.dbo.sysprocesses Where SPID = @@SPID)

--select * from sysprocesses where program_name like '%ssis%'
--Exit trigger if this is triggered by SSIS
If @Program_Name Like '%SSIS%'
RETURN --This exits the Trigger
Else

Begin
--Execute Trigger Logic Here
End

VBA Getting Runtime Error 94 Invalid Use of NULL

Being new to VBA, common errors like Run Time Error 94 can be hard to figure out. In this example, I was able to resolve the error by NOT declaring the variable CatVal as a String


Private Sub CommandButton3_Click()

' COMMENTING OUT THE NEXT LINE TOOK CARE OF THE RUN-TIME 94 INVALID USE OF 'NULLS
'Dim Cat2Val As String

With Me.Categories2
    Cat2Val = .Value
If IsNull(Cat2Val) Then
    MsgBox ("Select a value for Group 2")
Else:
    PubCat2Val = Cat2Val

End If
End With

End Sub

Email the results of SQL Query, using SQL DB Mail. The contents is formatted as a table, using XML

Email the results of SQL Query, using SQL DB Mail. The contents is formatted as a table, using XML

It is a compilation of information found online, with some modifications made by me.

DECLARE @xml NVARCHAR(MAX)

DECLARE @body NVARCHAR(MAX)

SET @xml = CAST(( SELECT

                     Col1 AS 'td','',

                     Col2 AS 'td','',

                     Col3 AS 'td','',

                     Col4 AS 'td','',

                     Col5 AS 'td','',

                     Col6 AS 'td','',

                     Col7 AS 'td','',

                     Col8 AS 'td','',

                     Col9 AS 'td','',

                     Col10 AS 'td','',

                     Col11 AS 'td','',

                     Col12 AS 'td','',

                     Col13 AS 'td','',

                     Col14 AS 'td','',

                     Col15 AS 'td','',

                     Col16 AS 'td'

From DBName.dbo.Table1

Where New_Status = (Select Col1 From DBName.dbo.Table2 Where DBName.dbo.Table2.PONbr = DBName.dbo.Table1 .PONbr)

ORDER BY [PONbr] Desc

FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))

SET @body ='<html><body><H3>Table Name</H3>

<table border = 1>

<tr>

       <th> [Col1] </th>

       <th> [Col2] </th>

       <th> [Col3] </th>

       <th> [Col4] </th>

       <th> [Col5] </th>

       <th> [Col6]] </th>

       <th> [Col7] </th>

       <th> [Col8] </th>

       <th> [Col9] </th>

       <th> [Col10]] </th>

       <th> [Col11] </th>

       <th> [Col12] </th>

       <th> [Col13] </th>

       <th> [Col14] </th>

       <th> [Col15] </th>

       <th> [Col16] </th>

</tr>' 

SET @body = @body + @xml +'</table></body></html>'

EXEC msdb.dbo.sp_send_dbmail

@profile_name='Profile_Name',

@body = @body,

@body_format ='HTML',

@recipients='Recipient@Domain.com',

@subject='Email Subject';

SQL Server - SSIS Error: "Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

I got this error in an Execute SQL Task.

"Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

This time the issue was that I had commented out one line in the Update statement.

This was the original statement (I've simplified it in order to make this post simpler):

update

  Table1 

set

    Table1.qtyavail=Table2.qtyavail,

    --Table1.price=Table2.price,

    Table1.Weight=Table2.Weight

from

   Table1 

join Table2

       On Table1.ID = Table2.ID

I had to delete the commented out line to be able to resolve the issue:

update

  Table1 

set

    Table1.qtyavail=Table2.qtyavail,

    Table1.Weight=Table2.Weight

from

   Table1 

join Table2

       On Table1.ID = Table2.ID

Stefan Nachev's Blog: SQL Server - SSIS

I am getting an intermittent error in one of my SSIS Packages. One of the SQL Tasks was getting deadlocked. This was the error - 

Executed as user: DOMAIN\administrator. Microsoft (R) SQL Server Execute Package Utility  Version 10.50.2500.0 for 32-bit  Copyright (C) Microsoft Corporation 2010. All rights reserved.    Started:  2:15:33 AM  Error: 2013-08-15 02:15:39.92   
Code: 0xC002F210     Source: Update SL_InvtID From UPC Execute SQL Task    
Description: Executing the query "X888_VendorInv_Update_SLInvtID_FromUPC_Pre ?" failed with the following error:
"Transaction (Process ID 70) was deadlocked on lock |
communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.".
Possible failure reasons: Problems with the query,
"ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  2:15:33 AM  Finished: 2:15:39 AM  Elapsed:  6.209 seconds.  The package execution failed.  The step failed.


The issue seems to be that within the Update SL ID (Pre) sequence container, there were two sequence containers.They were running in parallel (not sequentially).