Microsoft SQL - DBA work
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 ......
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 ......
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 ......
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 ......
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 ......
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 ......
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 ......
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 ......
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
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 ......
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 ......
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 ......
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 ......
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 ......
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 ......
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 ......
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 ......
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 ......
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 ......
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 ......
[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 ......
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 ......