Microsoft SQL - DBA work

Installing SQL 2005 SSRS on Windows 2008

First, don't ask me why. :( There was a need from vendor. Let's just leave it at that. So run through your normal SQL 2005 install, the validation page throws up a warning "IIS not running". Since SSRS is a must on this machine, so let's Google Bing it. Following some results like this one: http://www.iis.net/learn/in... run SQL 2005 install wizard again and got the same message. What the? Double check ......

Posted On Friday, September 26, 2014 4:30 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)

Be careful setting up disc for SQL Server

Been going through different setup for SQL server setup. One problem I ran into was that I ran into this error "{FileName}.mdf is Compressed But Does not Reside in a Read-only Database or Filegroup. The File Must be Decompressed".First Bing search result put me through to a path trying to alter the backup file, but that didn't work. After reading this one (http://blogs.msdn.com/b/sa... I was able to fix ......

Posted On Monday, March 18, 2013 3:21 PM | 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)

SSAS deployment error: Internal error: Invalid enumeration value. Please call customer support! is not a valid value for this element.

The first search on this error yielded some blog posts that says to check SQL server version. It suggested that I couldn't deploy a SSAS project originally set for SSAS 2008 to a SSAS 2008 R2, which didn't make sense to me. Combined with the fact that the error message was telling me to call customer support. Why do I need to call customer support unless something catastrophic happened?Turns out that one of the file on the SQL server is corrupt. I could simply delete the database on the SSAS server ......

Posted On Thursday, June 21, 2012 4:18 PM | Comments (0)

SSAS processing error: Client unable to establish connection; 08001; Encryption not supported on the client.; 08001

After getting the cube to successfully deploy and process on Friday, I was baffled on Monday that the newly added dimension caused the cube processing to break. I then followed the first instinct, discarded all my changes to reverted back to the version on Friday, and had no luck. The error message (attached below) did not help as I was looking for some kind of SQL service error. After examining the windows server log and the SQL server log, I just couldn't see anything wrong with it.After swearing ......

Posted On Thursday, June 21, 2012 4:09 PM | Comments (24)

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)

Database Hardware CPU Licensing vs Performance and Fast Track Data Warehouse Architecture

This is just one of those cool post which are not written as often as it should be. Hats off to Glenn Berry for stating it so clearly for all BI / database developers.http://sqlserver... read some Microsoft paper on "Fast Track Data Warehouse Architecture", and did not appreciate the benefit at the time. What a great way for developers to start with someone already figured out the licensing + cost + performance. There ......

Posted On Thursday, March 8, 2012 4:05 PM | Comments (0)

Database Model Diagram

Some very useful diagrams here.  It provides a basis point.  I see that it already does not really fit all project requirements, but this is a really nice starting point, and a good check on business entity relationship.

http://www.databaseanswers.org/data_models/index.htm

Posted On Wednesday, March 7, 2012 12:13 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)

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)

Using BizTalk to bridge SQL Job and Human Intervention (Requesting Permission)

I start off the process with either a BizTalk Scheduler (http://biztalkscheduledtas... or a manual file drop of the XML message. The manual file drop is to allow the SQL Job to call a "File Copy" SSIS step to copy the trigger file for the next process and allows SQL Job to be linked back into BizTalk processing. The Process Trigger XML looks like the following. It is basically the configuration hub of the business process <ns0:MsgSchedulerTrigger... xmlns:ns0="urn:com:something ......

Posted On Friday, February 4, 2011 3:29 PM | Comments (0)

Using SSIS to send a HTML E-Mail Message with built-in table of Counts.

For the record, this can be just as easily done with a .NET class with a DLL call. The two major reasons for this ending up as a SSIS package are: There are a lot of SQL resources in the organization for maintenance, but not as many .NET developers. There is an existing automated process that links up SQL Jobs (more on that in the next post), and this is part of that process. To start, this is what the SSIS looks like: The first part of the control flow is just for the override scenario. In the Execute ......

Posted On Wednesday, February 2, 2011 3:47 PM | Comments (14)

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)

Using SSIS as data source for SSRS

Gotta try this:

http://www.mssqltips.com/tip.asp?tip=1997&home

Posted On Thursday, May 13, 2010 10:40 AM | 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)

SharePoint 2010 and SQL Server 2008 R2 BI

Just saw a presentation on SharePoint 2010 and SQL Server 2008 R2 BI Integration. There are some cool stuff that will be out soon. The presentation focused on PowerPivot and Performance Point server. A end-business user can access her/his datasource, pull in some external data (like weather condition), and create a spreadsheet using PowerPivot. This spreadsheet, in addition to being hosted on SharePoint, can be incorporated through Performance Point by a SharePoint developer, and build a Cube in ......

Posted On Wednesday, January 13, 2010 12:21 PM | Comments (2)

Copyright © Kevin Shyr

Design by Bartosz Brzezinski

Design by Phil Haack Based On A Design By Bartosz Brzezinski