So here’s another SSMS Object Explorer tip for youThere was a question asked in one of the forums couple of days back.In SSMS object explorer you’ve the option to Edit and Select records from a table The question was whether…
SSIS Tips: Deduping Flat File Data without using Staging Table
Recently there was a SSIS challenge that was brought up by one of my fellow colleagues in his project. The project involves importing data in the form of flat files daily.Some cases the file is found to have duplicate data…
Issues in Altering Objects through SSMS Table Designer
Many people use the table designer component in SQL Management Studio for creating and altering objects in SQL Server. I’ve never used it and I’m more comfortable in using T-SQL queries for doing these activities. Anyways many of my colleagues…
Rowset Concatenation with Special Characters
Whenever we think about row set concatenation in SQL Server the first method that comes to our mind is by the use of FOR XML PATH. There are numerous blogs on the method explained in web.Couple of days back one…
Export Dynamic Query Results to Flat File Using SSIS
I’ve had a request from one of my regular blog followers couple of days back regarding a requirement.The requirement was to create a package which would enable exporting results of sql query to a flat file. The catch here was…
Generating Nested XML Structures with FOR XML PATH
This blog gives you some insight on how nested XML structures can be easily built using FOR XML PATH constructConsider the illustration given belowcreate table parent_child(ParentID varchar(50),ChildID varchar(50))insert parent_childvalues (‘Parent1′,’Child1’),(‘Parent1′,’Child2’),(‘Parent1′,’Child3’),(‘Parent4′,’Child4’),(‘Parent5′,’Child5’)The requirement was to built a xml structure as below The…
How to solve Microsoft.ACE.OLEDB.12.0 error “Unspecified error”
The reason for writing this blog is for the last 2 hours I’ve been battling with the below error message OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “(null)” returned message “Unspecified error”. Msg 7303, Level 16, State 1, Line 1…
Implementing Multivalued Parameter Filtering Using Logical Operators
Being extensively involved in report development projects, I’ve come across numerous occasions where requirements asks for the ability to implementing a logic to filter data in stored procedures based on multiple selection parameter value. In these cases the values will…
T-SQL tips: Pattern Search and Replace within XML using Xpath Functions
T-SQL provides good support for generating and manipulating XML data from SQL 2005 onwards. Using the functions like query(), nodes() etc we can shred the data within XML document. Similarly to build a XML out of relational data we can…
SSIS Base Date != T-SQL Base Date!
Couple of days before I was replicating out a logic in SSIS from T-SQL and I was in for a surprise. I’m sharing it through this blog post for others benefit.I’d blogged sometime back on how to develop server independent…