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

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.

An SSIS package does not run when you call the SSIS package from a SQL Server Agent job step

PROBLEM:

When I try to run a SSIS package using a config file keep getting error....

FIX:

Use SSIS Package configuration files to store sensitive information, and then store these configuration files in a secured folder. You can then change the ProtectionLevel property to DontSaveSensitive so that the package is not encrypted and does not try to save secrets to the package. When you run the SSIS package, the required information is loaded from the configuration file. Make sure that the configuration files are adequately protected if they contain sensitive information.