Microsoft SQL - T-SQL

SQL Remove Duplicates without Using Temp Table or Modifying Existing Table

Up until now, the way I understood to "fix" a table with duplicate data and no primary key or unique key is either:Modify the existing table, add a column and populate with unique key data, then delete off that unique key, orSELECT DISTINCT * INTO [a temp table] FROM [current table], TRUNCATE FROM [current table], INSERT INTO [current table] SELECT * FROM [a temp table]I am not saying one way is better than the other. In fact, if our table is huge and we are worried about transaction log file usage ......

Posted On Friday, May 4, 2018 9:11 AM | Comments (0)

SSMS 17.1 missing features

Just noticed that SSMS 17.1 (current version) does not support drag and drop of a SQL file.  Reverted back to SSMS 17 RC3 and that feature is back.  Going to check on other features.

Posted On Thursday, June 29, 2017 10:30 AM | Comments (0)

SSRS subscription SQL Agent Job GUID

Can't believe I didn't have to use this until now. Bad bad automation design...

Thanks, steve_fromOZ.

Posted On Wednesday, September 24, 2014 12:15 PM | Comments (0)

To bcp or Not To bcp... SSIS won (very specific Req)

I'm a recent convert to bcp. Actually, to put it more correctly, I haven't had any ETL requirement that would require complex data flow. I am a huge SSIS fan, but until 2012 came along, logging and debugging has always been an issue, so slowly I was moving back to SQL for basic tasks, breaking the uniformity of the project. Along the way I started to use bcp more heavily So imagine my surprise when I found this article: Basically saying "Data Flow Destination Fast ......

Posted On Tuesday, September 23, 2014 9:53 AM | Comments (0)

Simple solution now to a problem from 8 years ago. Use SQL windowing function

I remember having this problem 8 years ago. We had to find the top 5 donor per month and send out some awards. The SQL we came up with was clunky and had lots of limitation (can only do one year at a time), then switch the where clause and go again. Fast forward 8 years, I got a similar problem where we had to find the top 3 combination of 2 fields for every single day. And the solution is this elegant: SELECT CAST(eff_dt AS DATE) AS "RecordDate" , status_cd , nbr , COUNT(*) AS occurance , ROW_NUMBER() ......

Posted On Tuesday, June 10, 2014 4:18 PM | Comments (2)

My experience working with Teradata SQL Assistant

To this date, I still haven't figure out how to "toggle" between my query windows. It seems like unless I click on that "new" button on top, whatever SQL I generate from right-click just overrides the current SQL in the window. I'm probably missing a "generate new sql in new window" setting The default Teradata SQL Assistant doesn't execute just the SQL query I highlighted. There is a setting I have to change first. I'm not really happy that the SQL assistant and SQL admin are different app. Still ......

Posted On Wednesday, May 28, 2014 9:16 PM | Comments (1)

SQL Cast versus Convert: CAST should be first choice

I've always been a fan of CONVERT because of datetime CONVERT takes in a style argument and allows me to format the string quickly.  I have since learned that CAST is the standard SQL, and should be first choice of use.

Once I read through the entire article here on MSDN, it's pretty clear that's the rule, but seem to be an unwritten one.

Posted On Tuesday, March 25, 2014 2:32 PM | Comments (0)

Quick query to dump table field name and type for data mapping document


Posted On Friday, July 12, 2013 9:38 AM | Comments (0)

Different Mind Set Between Data Warehouse and System Integration

I love doing database design with people. I've done my fair share of database modeling on my own; those are the easy projects, sure, but with no feedback. However, working with people on database never fails to help me look at things from different perspective.A very simple thing like whether a NOT NULL table column should have default values get very different reaction from a BI guy and a System Integration guy. Let me put some of the priorities out from different projects I've been in to help understand ......

Posted On Friday, March 1, 2013 2:54 PM | Comments (0)

Quick SQL script to back up data in some tables.

Disclosure: I used cursor in this script. Normal 0 false false false EN-US X-NONE X-NONE MicrosoftInternetExplorer4 /**************************... ***************************... ***************************... Quick data backup for tables in a particular database schema Change the table search criteria in the cursor declaration ***************************... ***************************... ***************************... USE {some database name} GO DECLARE TablesToBackup CURSOR FOR SELECT ......

Posted On Wednesday, July 18, 2012 8:54 AM | Comments (0)

T-SQL usage: prefer Table Type over CTE (Common Table Expression)

I came to the CTE world late (the same time I learned MERGE), but I've been using it here and there. I love Table Type; the introduction of that changed the way we write our .NET app and BizTalk app, and in some way level the playing field of all these technologies against SSIS.But through my many recent round of building database, data warehouse, data marts, I find my self migrating away from using table type and views, partly because it just seemed like such a hassle to maintain a separate object ......

Posted On Thursday, April 19, 2012 11:34 AM | Comments (0)

Using Recursive SQL and XML trick to PIVOT(OK, concat) a "Document Folder Structure Relationship" table, works like MySQL GROUP_CONCAT

I'm in the process of building out a Data Warehouse and encountered this issue along the way.In the environment, there is a table that stores all the folders with the individual level. For example, if a document is created here:{App Path}\Level 1\Level 2\Level 3\{document}, then the DocumentFolder table would look like this:IDID_ParentFolderName1... 121Level 232Level 3To my understanding, the table was built so that:Each proposal can have multiple documents stored at various locationsDifferent ......

Posted On Wednesday, April 4, 2012 5:29 PM | Comments (0)

Ways to search for column name, table name, or stored procedure text in SQL

This is nothing new, just a way for me to organize the information better. Personally, I like the first 2 ways better just because I can order the result set any way I want.USE {Database Name}GO-- SQL 2008, search table nameSELECT IS_C.COLUMN_NAME , IS_C.* FROM INFORMATION_SCHEMA.COLUMNS AS IS_CWHERE IS_C.TABLE_NAME = '{Table Name}'ORDER BY IS_C.COLUMN_NAME -- , ORDINAL_POSITION, IS_NULLABLE, DATA_TYPE-- SQL 2008, search column nameSELECT IS_C.COLUMN_NAME , IS_C.* FROM INFORMATION_SCHEMA.COLUMNS ......

Posted On Tuesday, March 6, 2012 11:38 AM | Comments (0)

Experience with Indexed View

I had a bad experience working with Indexed view because, well, I didn't have much experience working with Indexed View. I hit 5 major issues before I finally gave up.Indexed view does not allow GETDATE() because it's a non-deterministic data type. I could not limit my result set, or put a flag column based on the age of the record.Indexed view does not allow self joinIndexed view does not allow OUTER JOINIndexed view does not do COUNT(*)Indexed view does not allow subqueriesIndexed view does not ......

Posted On Thursday, March 1, 2012 4:34 PM | Comments (0)

Monitoring BizTalk Server Without SCCM, using SQL query

For the environment that does not have SCCM to monitor BizTalk server health, the following SQL query can be incorporate into a SQL job and have BizTalk server at least on a "constant" check. Note: be aware that the following was tested only against a single suspended instance with a send port. Since BizTalk SQL server does not allow select against the ServiceClass table, I couldn't use a single table join. With the data I currently have, I am not 100% sure the Receive Port, Pipeline, Orchestration ......

Posted On Wednesday, September 14, 2011 10:37 PM | Comments (0)

Do NOT use only CHECKSUM or BINARY_CHECKSUM as sole comparison in SQL, use HASHBYTES instead

We had a process ot using BizTalk to take in a reference file, then send data in via a table_type parameter in a stored procedure. The stored procedure uses a MERGE statement to insert and update; the update part of which does a CHECKSUM first to determine whether an update is even necessary. Then it happened, we had a reference row that should have been updated by the latest file, but it wasn't done. We checked the incoming and outgoing BizTalk Message in and out of the pipeline; everything looked ......

Posted On Friday, May 20, 2011 8:50 PM | Comments (13)

Frustration and rewards on using SSIS Pivot and Unpivot

Granted, if you are just transforming data in the same SQL server, why would you do it in SSIS instead of just using Pivot and Unpivot in T-SQL query? But what about in the case where data comes from various sources? What I had fun doing was the ability to skip using Linked server, pull in data from different sources, like XML over web service. Another point that made me happy was the ability to simulate ROW_NUMBER() function with the merged data by using a Script Component. Anyway, I highly recommend ......

Posted On Friday, May 20, 2011 3:36 PM | Comments (0)

Instead of alter table column to turn IDENTITY on and off, turn IDENTITY_INSERT on and off

First of all, I don't know which version of SQL this post ( is based on, but at least for Microsoft SQL Server 2008, the syntax is not: ALTER TABLE [table_name] MODIFY [column_name] [data_type] NOT NULL; Instead, it should be: ALTER TABLE [table_name] ALTER COLUMN [column_name] [data_type] NOT NULL; Then, as several posts point out, you can't use T-SQL to run an existing column into an IDENTITY column. Instead, use the IDENTITY_INSERT to copy ......

Posted On Wednesday, March 2, 2011 11:24 PM | Comments (0)

Change Data Capture to get a paricular set of change within a time span

The "fn_cdc_map_time_to_lsn" is especially useful DECLARE @begin_time datetime, @end_time datetime, @from_lsn binary(10), @to_lsn binary(10) SET @begin_time = '11/10/2010 10:00 AM' SET @end_time = '11/10/2010 8:00 PM' SET @from_lsn = sys.fn_cdc_map_time_to_lsn(... greater than or equal', @begin_time); SET @to_lsn = sys.fn_cdc_map_time_to_lsn(... less than or equal', @end_time); SELECT _OU.unit_natural_id AS Order_Id , CONVERT(DATETIME, LEFT(CONVERT(VARCHAR(80), _OR.return_body.query(' ......

Posted On Wednesday, November 10, 2010 5:38 PM | Comments (0)

BizTalk UseAmbientTransaction and System.ObjectDisposedException

[UPDATE: 2010-10-15] This update is long overdue. Once we implemented all the changes, we were still getting that error from time to time. The sympton was that we would not get that error for hours, even days, but once we get it, the error keeps coming. After fiddling with some settings, we found that setting the "PollWhileDataFound" to False did the trick. We have not seen the error since. [UPDATE: 2010-08-26] We were able to fix our problem by setting the WCF-SQL receive location transaction isolation ......

Posted On Wednesday, August 25, 2010 10:46 AM | Comments (2)

Cannot do a reliable convert in the where clause for comparison

I'm dealing with this custom database where an attribute for an authorization can store many things (amount approved, flags, yes/no, etc.) For the attribute that store the amount approved, I have a job that starts off periodically to put a TermDate on it based on some business rules. I had an expression like this in the where clause: CONVERT(MONEY, @value, 1) > 10000 simply because @value comes in as a VARCHAR field. This fails, which I can understand. it's doing a table scan and failed when converting ......

Posted On Tuesday, November 3, 2009 6:09 PM | Comments (1)

Create synonym for all tables from another server or linked server object

I created the following into a stored procedure so that I can run it again and again: DECLARE @dynsqlcmd VARCHAR(250) DECLARE @tblname VARCHAR(150) DECLARE @synname VARCHAR(150) DECLARE @cnter INT -- Drop all synonyms DECLARE dbcursor CURSOR FOR SELECT AS 'table name' FROM sysobjects WHERE sysobjects.type = 'sn' OPEN dbcursor FETCH NEXT FROM dbcursor INTO @synname WHILE (@@FETCH_STATUS =0) BEGIN SET @dynsqlcmd = 'DROP SYNONYM ' + @synname EXEC (@dynsqlcmd) FETCH NEXT FROM dbcursor ......

Posted On Thursday, September 24, 2009 1:48 PM | Comments (1)

Execute SQL Job synchronously for BizTalk via a Stored Procedure call

The async one was very easy to do. However, in the process automation task I was given, there was a need to execute SQL Job and waiting for the execution result. I couldn't find anything on the web to do what I wanted, so I make the following stored procedure. In addition, this stored procedure is quasi-synchronous. I start the job and then check the status. You can change the timing of the status check, but for the example below, it is "synchronous within 30 seconds." I wish there is a better way ......

Posted On Monday, November 1, 2010 3:18 PM | Comments (0)

Comparing ways to get rowcount from a database table.

Just read this and found it very interesting. The following are 2 queries to get rowcount on a database table: Query 1 (the one everyone knows): SELECT COUNT(RowID) AS [RowCount] FROM elig_Inbox Query 2 (the interesting one): SELECT st.row_count AS [RowCount] FROM sys.dm_db_partition_stats st WHERE index_id < 2 and OBJECT_NAME(OBJECT_ID)='eli... What's really interesting to me is that once you look at the execution plan of the 2 queries, you'd find the improvement in performance. In real ......

Posted On Thursday, February 25, 2010 10:07 AM | Comments (1)

Stop a SQL job in the first 5 days of the month, but report success

It is actually quite simple when I think this through: Make the first job step with this T-SQL query: IF ((SELECT DAY(GETDATE())) < 6) BEGIN SELECT 1/0 END Go to the Advanced Tab of the Job Step property page. Select "Quit the job reporting sucess" on the "On Failure Action" drop down. Note: You can use RAISERROR, but the Severity has to be set to greater than 10. To avoid future maintenance confusion, I decided to go with the basic fatal error generation ......

Posted On Tuesday, February 2, 2010 1:12 PM | Comments (0)

Useful T-SQL queries on BizTalk Filter, Send Port, Receive Location, etc.

[2011-02-17] Disable all receive locations of Schedule adapter if the system has any suspended messages UPDATE RL SET RL.[Disabled] = -1 FROM BizTalkMgmtDb.dbo.adm_Recei... AS RL WITH(READPAST, ROWLOCK) INNER JOIN BizTalkMgmtDb.dbo.bts_recei... AS RP WITH(READPAST, ROWLOCK) ON RL.ReceivePortId = RP.nID INNER JOIN BizTalkMgmtDb.dbo.bts_appli... AS APP WITH(READPAST, ROWLOCK) ON RP.nApplicationID = APP.nID INNER JOIN BizTalkMgmtDb.dbo.adm_Adapter AS AD WITH(READPAST, ROWLOCK) ON RL.AdapterId ......

Posted On Wednesday, January 20, 2010 11:33 AM | Comments (0)

BizTalk transaction even cover an "AFTER INSERT" trigger

This surprised me.  I have a BizTalk SQL adapter calling a stored procedure to insert a row in some table.  The table has an "AFTER INSERT" trigger on it.

The original design had the trigger fire off an SSIS package.  But when the SSIS is called, the row status is still not committed.  I thought since I'm using an "AFTER INSERT" trigger, I was allowed to do that.  Need to read more on the transaction nature of triggers, I guess.

Posted On Wednesday, December 30, 2009 12:25 PM | Comments (0)

Final note on creating PDF using SSRS and SSIS

Business Scenario: The client gets some kind of feeds that writes into a process table of their client processing system. The data is stored directly as the raw XML file inside a SQL data column. The XML could store multiple enrollment information for the same member. We need to use this info to determine whether a member is eligible, then insert into a staging table. The staging table is then used as a feed to drive a report that generates an image for each claim and sent off to the different provider. ......

Posted On Tuesday, December 8, 2009 2:19 PM | Comments (0)

Tricks to format XML properly for output from a SQL query

A few things I've learned in the past few days on SELECT {something} FOR XML AUTO, ELEMENTS Use subquery to denormalize data so that they can be grouped under the same parent element. Do not use synonym to get to your linked server. If you want any elements to be named properly, use full linked server syntax and alias the table/subquery. I'll add more as I learn them ......

Posted On Thursday, December 3, 2009 1:44 PM | Comments (0)

Calling SSRS report using either T-SQL or SSIS

[Update: 2009-12-30] We got it working using SSIS. Read this post: [Original post] Would love to know if there is a way to kick off a SSRS report and pass some parameters into it via T-SQL or SSIS. Unfortunately, we can't have MS SQL Enterprise edition. Guess this is the true list of possible solutions: VB script PowerShell WMI script batch file T-SQL Sad thing is I know how to make this ......

Posted On Tuesday, November 24, 2009 2:53 PM | Comments (1)

T-SQL extract multiple child node attribute data out of an XML datatype.

Business scenario: We have an XML file (HIPAA 837) saved as a XML data column in a table and we need to go through each row and get different information on different nodes and output to a table. The real reason of this design lies in with how the claim system is designed, which I will not try to explain here. Approach: Using Common Table Expression (CTE) to get all the necessary info so that the data can be joined later. Use ROW_NUMBER() to make sure the information comes from the correct line. ......

Posted On Monday, November 23, 2009 4:48 PM | Comments (0)

Using xp_cmdshell to execute SSIS in a stored procedure

This is the code: DECLARE @SQLCommand AS VARCHAR(1000) SET @SQLCommand = '("dtexec location" /F "package location and name" ' + '/SET \package.Variables[User::your variable name].Value;' + CONVERT(VARCHAR(2), MONTH(@Date)) + '/' + CONVERT(VARCHAR(2), DAY(@Date)) + '/' + CONVERT(VARCHAR(4), YEAR(@Date)) + ')' EXEC xp_cmdshell @SQLCommand Some notes: I tried to pass in the date without formatting it, I get a "Option "1" is not valid" message. I had to convert the date into a string to include in the ......

Posted On Wednesday, September 23, 2009 12:41 PM | Comments (0)

An awesome website on SQL code

Sometimes you have to resort to slang to express yourself, like on this ocasion there is nothing else to say except: "This dude is awesome".

I used his stored procedure to script out that data in a table.  Used to do this in TextPad with Regular expression.  Can't imagine how much time this saves me.

Posted On Wednesday, September 23, 2009 10:27 AM | Comments (0)

Using sql update statement instead of cursor

I've seen too many instances where a cursor can be simply rewritten as an update statement. I was working at a client site where one of their process calls a stored procedure that uses a cursor do some updates. That stored procedure would run over the weekend to update about 2000 records and bog down the production SQL (You'd think I was joking). We re-write the stored procedure. Granted, the update itself is about 50 lines with 5 tables, subqueries, and left outer joins, but we got the stored procedure ......

Posted On Friday, September 11, 2009 10:36 AM | Comments (0)

Copyright © Kevin Shyr

Design by Bartosz Brzezinski

Design by Phil Haack Based On A Design By Bartosz Brzezinski