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…
Generate Format Files Based on Table Metadata
Recently I had blogged about advantages of using format files in bcp to generate custom formatted output files.http://visakhm.blogspot.in/2013/10/bcp-out-custom-format-data-to-flat-file.htmlI got lot of responses on this post and many of them were asking whether there’s any easy way to generate a format…
BCP Out Custom Format Data to Flat File
One of the frequent requirements that I have come across in most of the ETL projects which I’ve undertaken is the ability to transfer data from and to flat files. The usual way to implement this was by designing a…
Different ways to export SQL Server data to text file
This blog explains few methods you can use to export data from SQL Server table to a text file. Consider the below table as an illustration The requirement is to export data to a text file.Now lets see different ways…