Microsoft SQL - T-SQL

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.  http://msdn.microsoft.com/en-us/library/ms187928.aspx

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

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

SELECT COLUMN_NAME AS [Field Name] , (DATA_TYPE + CASE WHEN CHARACTER_MAXIMUM_LENGTH IS NOT NULL THEN '(' + CONVERT(VARCHAR(20), CHARACTER_MAXIMUM_LENGTH) + ')' ELSE '' END ) AS [Field Type] FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'SOME TABLE NAME'SELECT COLUMN_NAME AS [Field Name] , (DATA_TYPE + CASE WHEN CHARACTER_MAXIMUM_LENGTH IS NOT NULL THEN '(' + CONVERT(VARCHAR(20), CHARACTER_MAXIMUM_LENGTH) + ')' ELSE '' END ) AS [Field Type] FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ......

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)

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 (http://www.techonthenet.co... 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)

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 (9)

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 sysobjects.name 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 (0)

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: http://geekswithblogs.net/L... [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.

http://vyaskn.tripod.com/code.htm

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