Stefan Nachev's Blog: 2013

Thursday, December 5, 2013

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

Wednesday, November 13, 2013

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

Monday, October 7, 2013

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

Wednesday, September 4, 2013

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

Thursday, August 29, 2013

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>

<tr>

</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';

Tuesday, August 27, 2013

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

Thursday, August 15, 2013

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).