Pass datetime parameters to openrowset/query

DECLARE @sqlCommand varchar(1000)
DECLARE @StartDate datetime
DECLARE @EndDate datetime
DECLARE @local int
SET @StartDate = '20100101'
SET @EndDate = '20110101'
SET @local = 1
SET @sqlCommand = 'SELECT * FROM OPENROWSET(''SQLNCLI'',''server=servername;trusted_connection=yes'',''SET FMTONLY OFF exec procname ''''' + convert(varchar(10),@StartDate,112) + ''''',''' + convert(varchar(10),@EndDate,112) + ''''',' + cast(@local as varchar(10)) + ''')'

PRINT @sqlCommand
--EXEC (@sqlCommand)

SQL - Query xml attribute

 

SELECT TOP 100 s.owner_id, mpln_key, mpln_member, mpln_owner, pln_key , pln_cb_id ,

asset_mix.value('(/asset_mix/mem_plan/@market_value)[1]','decimal(18,2)') as market_value,

asset_mix.value('(/asset_mix/mem_plan/@plan_allocate_rating)[1]','varchar(2)') as plan_allocate_rating,

asset_mix.value('(/asset_mix/mem_plan/asset_mix_result[@investment_type_EN="Short Term (GTD Investments)"]/@investment_type_EN)[1]','varchar(30)') as investment_type_EN,

asset_mix.value('(/asset_mix/mem_plan/asset_mix_result[@investment_type_EN="Short Term (GTD Investments)"]/@asset_allocation)[1]','varchar(30)') as asset_allocation,

asset_mix.value('(/asset_mix/mem_plan/asset_mix_result[@investment_type_EN="Global Equity"]/@investment_type_EN)[1]','varchar(30)') as investment_type_EN,

asset_mix.value('(/asset_mix/mem_plan/asset_mix_result[@investment_type_EN="Global Equity"]/@asset_allocation)[1]','varchar(30)') as asset_allocation,

asset_mix.value('(/asset_mix/mem_plan/asset_mix_result[@investment_type_EN="Canadian Equity"]/@investment_type_EN)[1]','varchar(30)') as investment_type_EN,

asset_mix.value('(/asset_mix/mem_plan/asset_mix_result[@investment_type_EN="Canadian Equity"]/@asset_allocation)[1]','varchar(30)') as asset_allocation,

asset_mix.value('(/asset_mix/mem_plan/asset_mix_result[@investment_type_EN="Bonds"]/@investment_type_EN)[1]','varchar(30)') as investment_type_EN,

asset_mix.value('(/asset_mix/mem_plan/asset_mix_result[@investment_type_EN="Bonds"]/@asset_allocation)[1]','varchar(30)') as asset_allocation

 

from [DC4Reports].[dbo].[member_plan_wrapper_extraction] as s

where owner_id= 3620

SQL -Update XML column

 

declare @new_rating int

set @new_rating = 0

 

 

UPDATE [member_plan_wrapper_extraction]

SET asset_mix.modify('replace value of (/asset_mix/mem_plan/@plan_allocate_rating)[1] with sql:variable("@new_rating") ')

WHERE owner_id=@owner_id

and mpln_member = @mpln_member

and mpln_owner = @mpln_owner

and pln_cb_id=@pln_cb_id ;

SQL - Query to Find Column From All Tables of Database

 SELECT t.name AS table_name,
 SCHEMA_NAME(schema_id) AS schema_name,
 c.name AS column_name
 FROM sys.tables AS t
 INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
 WHERE c.name LIKE '%sp_id%'
 ORDER BY schema_name, table_name;

SQL Format datetime

select convert(varchar, getdate(), 100) convertResult,100 style union
select convert(varchar, getdate(), 101),101 union
select convert(varchar, getdate(), 102),102 union
select convert(varchar, getdate(), 103),103 union
select convert(varchar, getdate(), 104),104 union
select convert(varchar, getdate(), 105),105 union
select convert(varchar, getdate(), 106),106 union
select convert(varchar, getdate(), 107),107 union
select convert(varchar, getdate(), 108),108 union
select convert(varchar, getdate(), 109),109 union
select convert(varchar, getdate(), 110),110 union
select convert(varchar, getdate(), 111),111 union
select convert(varchar, getdate(), 112),112 union
select convert(varchar, getdate(), 113),113 union
select convert(varchar, getdate(), 114),114  union
select convert(varchar, getdate(), 120),120  union
select convert(varchar, getdate(), 121),121  union
select convert(varchar, getdate(), 126),126  union
select convert(varchar, getdate(), 127),127  union
select convert(varchar, getdate(), 130),130  union
select convert(varchar, getdate(), 131),131

 

09:05:22 108
09:05:22:173 114
12-17-2013 110
12/17/2013 101
14 jQuery15205048664805831008_1387289176437? 1435  9:05:22:173AM 130
14/02/1435  9:05:22:173AM 131
17 Dec 2013 106
17 Dec 2013 09:05:22:173 113
17-12-2013 105
17.12.2013 104
17/12/2013 103
2013-12-17 09:05:22 120
2013-12-17 09:05:22.173 121
2013-12-17T09:05:22.173 126
2013-12-17T09:05:22.173 127
2013.12.17 102
2013/12/17 111
20131217 112
Dec 17 2013  9:05:22:173AM 109
Dec 17 2013  9:05AM 100
Dec 17, 2013 107

Insert into temporary table from Stored Procedure

DECLARE @mast_key_table table
(
    mast_key int,
    inc_mmk_calculation int,
    lastest_trx_date datetime null,
    ast_key int,
    pln_key int,
    sp_key int,
    seg_key int,
    incdet_key int,
    incdet_effdate datetime,
    uv_value decimal (18,8),
    incdet_cutoffdate datetime
)
-- EXEC sp_get_mastfordailyincdist <task_key>
INSERT INTO @mast_key_table
EXEC sp_get_mastfordailyincdist 4158
SELECT * from @mast_key_table order BY lastest_trx_date

SQL - Index node value in XML column

SET ARITHABORT { ON | OFF }

The below is from MSDN

Caution noteCaution

The default ARITHABORT setting for SQL Server Management Studio is ON. Client applications setting ARITHABORT to OFF can receive different query plans making it difficult to troubleshoot poorly performing queries. That is, the same query can execute fast in management studio but slow in the application. When troubleshooting queries with Management Studio always match the client ARITHABORT setting.

 

"SET ARITHABORT must be ON when you are creating or changing indexes on computed columns or indexed views. If SET ARITHABORT is OFF, CREATE, UPDATE, INSERT, and DELETE statements on tables with indexes on computed columns or indexed views will fail."

SQL SERVER – Compatibility level

SQL SERVER – Fix : Error : Incorrect syntax near . You may need to set the compatibility level of the current database to a higher value to enable this feature. See help for the stored procedure sp_dbcmptlevel

 

 

Solution:

Change the database compatibility level using following command in management studio.

For SQL Server 2005:
EXEC sp_dbcmptlevel 'DatabaseName', 90

For SQL Server 2008:
EXEC sp_dbcmptlevel 'DatabaseName', 100

SQL Server 2008: "Saving changes is not permitted"

To change the Prevent saving changes that require the table re-creationoption, follow these steps:

  1. Open SQL Server Management Studio (SSMS).
  2. On the Tools menu, click Options.
  3. In the navigation pane of the Options window, click Designers.
  4. Select or clear the Prevent saving changes that require the table re-creation check box, and then click OK.

The AcquireConnection method call to the connection manager failed with error code 0xC0202009

PROBLEM:

Today I encounter a problem when deploying my SSIS packages to production environment. The package itself is basically get data from a flat file located at an input folder, populate flat file data to a database table, do manipulation with the data within the table then generate some report and output some flat file to a destinlation folder.

Configurations like connection string, are set up in the config file. There are some other parameter are setup in the database table which the SSIS package will read at the first step then use it at runtime.

 

ERROR:

Message
Executed as user: MSOIT\app_services_ppsql. Microsoft (R) SQL Server Execute Package Utility  Version 10.50.1600.1 for 64-bit  Copyright (C) Microsoft Corporation 2010. All rights reserved.    Started:  11:54:21 AM  Error: 2011-12-09 11:54:22.38     Code: 0xC0202009     Source: CBLoad_Bell_Package Connection manager "ctor-dc01sql04.ArielDC4"     Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E4D.  An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80040E4D  Description: "Login failed for user 'MSOIT\App_Services_PPSQL'.".  End Error  Error: 2011-12-09 11:54:22.38     Code: 0xC020801C     Source: CBLoad_Bell_Package Log provider "SSIS log provider for SQL Server"     Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "ctor-dc01sql04.ArielDC4" failed with error code 0xC0202009.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.  COM error object information is available.  Source: "CBLoad_Bell_Package"  error code: 0xC0202009  Description: "SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E4D.  An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80040E4D  Description: "Login failed for user 'MSOIT\App_Services_PPSQL'.".  ".  End Error  Error: 2011-12-09 11:54:22.46     Code: 0xC0202009     Source: CBLoad_Bell_Package Connection manager "ctor-dc01sql04.ArielDC4"     Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E4D.  An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80040E4D  Description: "Login failed for user 'MSOIT\App_Services_PPSQL'.".  End Error  Error: 2011-12-09 11:54:22.46     Code: 0xC020801C     Source: Data Flow Task - Flat File to SQL OLE DB Destination [224]     Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "ctor-dc01sql04.ArielDC4" failed with error code 0xC0202009.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.  End Error  Error: 2011-12-09 11:54:22.46     Code: 0xC0047017     Source: Data Flow Task - Flat File to SQL SSIS.Pipeline     Description: component "OLE DB Destination" (224) failed validation and returned error code 0xC020801C.  End Error  Error: 2011-12-09 11:54:22.46     Code: 0xC004700C     Source: Data Flow Task - Flat File to SQL SSIS.Pipeline     Description: One or more component failed validation.  End Error  Error: 2011-12-09 11:54:22.46     Code: 0xC0024107     Source: Data Flow Task - Flat File to SQL      Description: There were errors during task validation.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  11:54:21 AM  Finished: 11:54:22 AM  Elapsed:  0.656 seconds.  The package execution failed.  The step failed.

 

CAUSE:

As per my understanding, if DelayValidation is not set to true to any connection that use config file value or any runtime value, SSIS engine uses the design time values of a task until it actually runs a task. Since we are dynamically setting the connection manager, instead of using the dynamically set value (Which it uses anayway while running the task), it tried to validate the task with the design time values set for the connection manager. Unfortunately, the service account with which the job is running doesn't have access to the server and database set during the design time for the connection manager. So, the validation of the task failed causing the job to fail.

 

Fix:

Changed the DelayValidation property of anything need to swap runtime value to true.