Tag | SQL Posts

On November 28th, 2011 I had the opportunity to give a presentation at the St. Louis .NET User Group on my experiences developing software for the Windows Azure and SQL Azure platform. Having been at PDC in 2009 for the announcement of Azure and been able to work on a number of Azure projects since that time I frequently see many Azure talks that are either: A) too high level and marketing oriented as they never tell you anything important or B) too low level and focused on a specific feature that ...
Todays Deal of the Day from O'Reilly at http://shop.oreilly.com/pro... is CLR via C#, Third Edition at $23.99 "Dig deep and master the intricacies of the common language runtime (CLR) and the .NET Framework 4.0. Written by a highly regarded programming expert and consultant to the Microsoft® .NET team, this guide is ideal for developers building any kind of application-including Microsoft® ASP.NET, Windows® Forms, Microsoft® SQL Server®, Web services, and console applications. You'll ...
I found some nice tutorials to learn SQL and Oracle PL/SQL. I found these tutorials with nice and clear explanation.SQL: http://beginner-sql-tutoria... http://plsql-tutorial.com/i... Will update the blog with latest updates with more information ...
Windows Azure Platform How to Sort Azure Table Store results Chronologically How to upload your Excel and CSV data to the Cloud - Blob Storage SQL Azure Database Notification and Job Scheduling Service for SQL Azure SQL Azure Tutorials – Blog ...
Goal: Rebuild indexes in SQL server. This can be done one at a time or with the example script below to rebuild all index for a specified table or for all tables in a given database. Why? The data in indexes gets fragmented over time. That means that as the index grows, the newly added rows to the index are physically stored in other sections of the allocated database storage space. Kind of like when you load your Christmas shopping into the trunk of your car and it is full you continue to load some ...
SQL Server team has released the new SQL Server Data Tools CTP4. Congratulations and Thanks to Gert Drapers and his team with this great milestone. To lear more about this SSDT CTP4 Release, check: What’s new in SQL Server Data Tools CTP4?http://blogs.msdn.com/... Server Data Tools CTP4 vs. VS2010 Database Projectshttp://blogs.msdn.c... VSDB->SSDT ...
UPDATE: Thanks for the feedback and comments. I have adjusted my table below with your recommendations. I had missed a point or two. I wanted to do a series on creating an entire project using the EDMX XAF code generation and the SpecFlow BDD Easy Test tools discussed in my earlier posts, but I thought it would be appropriate to start with a simple comparison and reasoning on why I choose to use these tools. Let’s start by defining the term ORM, or Object-Relational Mapping. According to Wikipedia ...
Moved from Home to work and went to fire up my project and after long pause "The provider did not return a ProviderManifestToken string" or even More Abscure ProviderIncompatable Exception Now after 20 mins of chasing my tail re different ver of EntityFramework 4.1 vs 4.2...blahblahblah Look inside at the inner exception A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible DOH!!!! Or a clean translation ...
Herve Roggero, SQL Azure MVP, has created a virtual community to focus on Azure. Here is the outline from Herve: User Group Name: Azure Florida Association Purpose: Start a virtual Florida user group that targets the Azure platform Venues: Most meetings will be virtual; however I plan to host a few physical events across Florida if possible from time to time; physical events may be a few hours long with potentially more than one speaker Possible Topics: The topics will touch Azure generally speaking, ...
So I was asked today how to do cross joins in SQL Azure using Linq. Well the simple answer is you cant do it. It is not supported but there are ways around that. The solution is actually very simple and easy to implement. So here is what I did and how I did it. I created two SQL Azure Databases. The first Database is called AccountDb and has a single table named Account, which has an ID, CompanyId and Name in it. The second database I called CompanyDb and it contains two tables. The first table I ...
I am proud to announce the creation of a new virtual user group: the Azure Florida Association. The missiong of this group is to bring national and internaional speakers to the forefront of the Florida Azure community. Speakers include Microsoft employees, MVPs and senior developers that use the Azure platform extensively. How to learn about meetings and the group Go to http://www.linkedin.com/gro... First Meeting Announcement Date: January 25 2012 @4PM ET Topic: Demystifying SQL Azure ...
In this Issue: Michael Crump, Andrea Boschin, Michael Sync, WindowsPhoneGeek(-2-), Erno de Weerd, Jesse Liberty, Derik Whittaker, Antoni Dol, Walter Ferrari, and Jeff Blankenburg(-2-). Above the Fold: Silverlight: "10 Laps around Silverlight 5 (Part 6 of 10)" Michael Crump WP7: "31 Days of Mango | Day #2: Device Status" Jeff Blankenburg Metro/WinRT/W8: "Lighting up your C# Metro apps by being a Share Target" Derik Whittaker Shoutouts: Michael Palermo's latest Desert Mountain Developers is up Michael ...
© 2011 By: Dov Trietsch. All rights reserved Passing the CAML thru the EY of the NEEDL Definitions: CAML (Collaborative Application Markup Language) is an XML based markup language used in Microsoft SharePoint technologies Anonymous: A camel is a horse designed by committee Dov Trietsch: A CAML is a HORS designed by Microsoft I was advised against putting any Camel and Sphinx rhymes in here. Look it up in Google! _____ Now that we have dispensed with the dromedary jokes (BTW, I have many more, but ...
© 2011 By: Dov Trietsch. All rights reserved finding a person in the forest or Limiting the AD result in SharePoint People Picker There are times when we need to limit the SharePoint audience of certain farms or servers or site collections to a particular audience. One of my experiences involved limiting access to US citizens, another to a particular location. Now, most of us – your humble servant included – are not Active Directory experts – but we must be able to handle the “audience restrictions” ...

This blog post has moved:

http://gruffcode.com/2011/11/15/time-warp/

UPDATED March 24th 2016: Test Data Retention is now added to TFS 2015 Update 1, see this post for more information.UPDATED Apr 15th 2013: Corrected to VS2012 Update 2 UPDATED Mar 23rd 2012: Added information about VS 11 UPDATED Mar 21st 2012: Added info and link to VS/TFS 11 tool for getting the Test Attachment Cleaner, now included with the TFS Power Tools UPDATED Mar 17th 2012: Added sql queries for TFS 11 Beta. Changed structure after Adam’s advices. Updated information. UPDATED Jan 9th 2012: ...
"SQL Server-related performance problems come up regularly and diagnosing and solving them can be difficult and time consuming. Read SQL Server MVP Jonathan Kehayias’ Troubleshooting SQL Server: A Guide for the Accidental DBA for descriptions of the most common issues and practical solutions to fix them quickly and accurately." Please go to http://www.red-gate.com/pro... RedGate produce some superb tools for SQL Server. Jonathan's book is ...
Welcome back, in part 1 of Load and Web Performance Testing using Visual Studio 2010 I talked about why Performance Testing the application is important, the test tools available in Visual Studio Ultimate 2010 and various test rig topologies. In this blog post I’ll get into the details of web performance & load tests as well as why it’s important to follow a goal based pattern while performance testing your application. Tools => Options => Test Tools Have you visited the treasures of Visual ...
The latest version of the AppFabric ServiceBus now has support for queues and topics. Today I will show you a bit about using queues and also talk about some of the best practices in using them. If you are just getting started, you can check out this site for more info on Windows Azure. One of the 1st things I thought if when Azure was announced back when was how we handle fault tolerance. Web sites hosted in Azure are no much of an issue unless they are using SQL Azure and then you must account ...
The full error in Microsoft Visual Studio on a compile looks like this: error CS1548: Cryptographic failure while signing assembly 'C:\Program Files\Microsoft SQL Server\100\Samples\Analysis Services\Programmability\AM... This is likely due to a missing strong key pair value file. The easiest way to solve this problem is to create a new one. Navigate to: Microsoft Visual Studio 2010>Visual Studio Tools>Visual Studio x64 Win64 ...
This is the third of a three part series that deals with the issue of faking test data in the context of a legacy app that was built with Microsoft's Entity Framework (EF) on top of an MS SQL Server database – a scenario that can be found very often. Please read the first part for a description of the sample application, a discussion of some general aspects of unit testing in a database context, and of some more specific aspects of the here discussed EF/MSSQL combination. Lately, I wondered how you ...
Cool little trick to determine which sql query which is taking a long time to execute, first while offending query is running from another machine do EXEC sp_who2 Locate the SPID responsible via Login, DBName and ProgramName columns, then do DBCC INPUTBUFFER (<SPID>) The offending query will be in the EventInfo column. This is a great little time saver for me, before I found out about this I used to split my concatenated query script in to multiple sql files until I located the problem query ...
In this Issue: Brian Noyes, Michael Crump, WindowsPhoneGeek, Erno de Weerd, Jesse Liberty, Derik Whittaker, Sumit Dutta, Asim Sajjad, Dhananjay Kumar, Kunal Chowdhury, and Beth Massi. Above the Fold: Silverlight: "Working with Prism 4 Part 1: Getting Started" Brian Noyes WP7: "Getting Started with the Coding4Fun toolkit Tile Control" WindowsPhoneGeek LightSwitch: "How to Connect to and Diagram your SQL Express Database in Visual Studio LightSwitch" Beth Massi Shoutouts: Michael Palermo's latest Desert ...
Normal 0 false false false MicrosoftInternetExplorer4 /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.000... mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} My friend asked me where to ...
This is the second of a three part series that deals with the issue of faking test data in the context of a legacy app that was built with Microsoft's Entity Framework (EF) on top of an MS SQL Server database – a scenario that can be found very often. Please read the first part for a description of the sample application, a discussion of some general aspects of unit testing in a database context, and of some more specific aspects of the here discussed EF/MSSQL combination. Lately, I wondered how ...
For good or bad, Microsoft’s Entity Framework (EF) has become one of the most widely used ORM tools out there. While some may state that it's not among the better ones (or that it's not even a real ORM), it’s definitely the most convenient one: its Visual Studio integration and consequently its integration with the MS SQL Server database is unparalleled. This is especially relevant to people who haven’t used the concept of object-relational mapping before - there's almost no initial learning curve ...
Check out the Fault Tolerance Framework: http://windowsazurecat.com/... This is really sweet. You can download the source code, then open it in VS and build it. Then plug into your app to get fault tolerance for SQL Azure, Windows Azure storage, Service Bus and Caching Service. Once you have it built and referenced in your project, it only takes a few minutes to plumb your application to use it. Next week (hopefully) I will post a new WPF app that uses ServiceBus ...
In SQL Server 2008 you can use table valued parameters which can be pretty useful. In the example I use a very simple one to overcome SQL Server’s lack of having a parameter as an array. The example goes through creating the new type, using it in a stored procedure and calling it from an application (VB.Net in this example). You start off with creating the new type. Under Programmability/Types/User-... Table Types create a new table. Here I created a table with a single column to hold an integer. ...
Normal 0 false false false EN-US X-NONE X-NONE /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin-top:0in; mso-para-margin-right:0in; mso-para-margin-bottom:10.0pt; mso-para-margin-left:0in; line-height:115%; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans... mso-ascii-font-family:Calibri; ...
If you work with SQL Server you will eventually encounter a JOIN statement. This JOIN statement can be stated as an ordinary JOIN, or as a sub select. In most cases I try to use JOINS (even though it’s just for readability), but in some cases it’s quicker to do it in a sub select (for example with production code, in which you don’t want to change the output but filter output). Curious about the performance differences between the two, I tried to create a test. I created 2 tables with country information: ...
For a full explanation and step-by-step guide to setup a linked server through Sql Management Studio (SMS), check out this reference: http://www.databasejournal.... Here it is in a nutshell: If you are setting up a linked server for another sql server 2005/2008 box, just remember to (1) name the Linked server the same name as its network name, (2) select and provide under the security option ,"Be made ...
At http://www.darkreading.com/... you will find details of the mass SQL Injection Attacks that have taken place recently. These attacks appear to have compromised a million web sites. There are links to a further 3 reports. These are: http://twimgs.com/darkreadi... (this explains the blind SQL attack) http://i.techweb.com/darkre... ...
On CodePlex there is a new project at http://nsession.codeplex.com for sharing session state between classic ASP and ASP.NET "The objective of this project is to allow ASP Classic to access ASP.NET out-of-process session stores in the same way that ASP.NET accesses them, and thus share the session state with ASP.NET. In the initial release, we only support the ASP.NET state server as it is a popular choice and it offers better performance than the SQL Server session store. Please see the Documentation ...
Once the installer for the AdventureWorks Community Sample Database has finished, we then need to complete the process (not mandatory, but nice to do to play with the OLAP content). The first thing we need to do is make sure we note the location of the script files and samples. The path if you are working with SQL Server 2008 R2 is as follows: “C:\Program Files\Microsoft SQL Server\100\Tools\Samples\Ad... 2008R2 Analysis Services Project\enterprise” I am working with the developer edition, ...
For a while now, if we have been around SQL Server we got used to the different sample databases that were provided. From Pubs to Northwind to AdventureWorks. In order to get the AdventureWorks samples we need to go to CodePlex and download the one that corresponds to the version of SQL Server we are working with. The link to the download site here : http://msftdbprodsamples.co... Now, on to the installation. Once you download the appropriate file, double click and launch ...
If you develop applications in SQL Azure, you may end up in a situation where your code needs to know if it is operating in the root database or in a Federation Member, or if it is connected to a regular user database. Two ways can be used to make this determination: using system tables, or using the USE FEDERATION command. Using System Tables This approach is the easiest to implement since it relies a SQL call only, without much logic in your application code. Assuming your code is currently connected ...
The following is my experience with SSIS building: Make sure you understand the 3 modes of Lookup. General, I would only do full cache if the lookup table only return less than 200 rows Never use the table or view select unless you really need every field on that table or view. select * from a data source will break in the future if more columns are added to the table select * in a lookup brings back unnecessary fields and impact performance Never use "Sort" in SSIS unless it's absolutely necessary. ...
Today I was working on hooking up some CLR sprocs in SQL Server. My friend Greg Low (SolidQ Australia) pointed out that even vb clr sprocs are case sensitive. I loaded an assembly like so: CREATE ASSEMBLY AuditingSPROCS from 'C:\Development\AuditingSPR... WITH PERMISSION_SET = SAFE Now for the fun. I had to tweak this a bit. The items in [] below are the namespace and class name. That is followed by a . and the sproc name: CREATE PROCEDURE SelectReportsAll AS EXTERNAL NAME AuditingSPROCS .[Auditing.StoredProcedures... ...
In order to refresh your OLAP Cubes (SQL Server Analysis Services), you need to create XMLA files. These XMLA files can be used in a SQL Server Agent Job, so that the Cubes are refreshed and re-processed. To accomplish this, you need to follow the next steps: 1) Deploy your Cube to your SQL Server 2) Right click on the database, and choose “Script database as” –> “Alter To” 3) Add the following node to the top of the XML: <Batch xmlns="http://schemas.micro... ...
At http://www.darkreading.com/... there is a disturbing report on the extent of SQL injection attacks. The atttack is not just SQL Injection attack but uses scripts to initiate "drive-by" downloads to the client PC? What are you doing to secure your websites both externally facing and internally facing? Are you installing a full-spectrum security solution such as Sunbelt's VIPRE on your client PC's ...
SSRS is a wonderful tool for quickly retrieving data from many different data sources and presenting the data to the user at a run-time decided format. One area where SSRS often falls short is when the underlying data needs to come from several different sources. Perhaps we want to retrieve data from the General Ledger which is in Oracle, and join that against a list of departments and employees which are stored in SQL Server for us to display in one table. When this happens, we are unable to join ...
“Because everyone wants to kick their database, but sometimes kicking your database is a good thing!” Many would not argue that you should version your code, and few would argue against versioning your code in a way that can lead back to a specific point in source control history. However, most people don’t really think of doing the same thing with your database. That’s where RoundhousE (RH) comes in. I have been working on RH for over two years now and people always wander what it is, why and what ...
Every ones in a while you want to know the record count of all objects in your database. If you have 10 objects or less in your database, you can choose to type out the statements yourself, but once it’s more then that it gets quite annoying. Because I needed this for a production issue (make sure that all objects contain records after import) I wrote a cursor to query all the database objects, and count the content of it: USE Sandbox --=========================... ...
Here I will explain how to get the column names and corresponding datatypes in particular table using SQL Server. Description: I have one table with lot columns in database at that time I tried to know the column names and corresponding datatypes and their maximum sizes in particular table for that I written the following query in SQL server to get column names and datatypes in particular table. USE MySampleDB GO SELECT column_name 'Column Name', data_type 'Data Type', character_maximum_length 'Maximum ...
To print multiple int variables on one line you need to CAST them first. Otherwise the PRINT command will add them up (at least it does for me anyways using SQL 2008). In the example I needed to have two ID variables print out while I was working on a sproc so I could see if I was getting the correct IDs before adding in my update code. This did not work, it adds the two variables together: PRINT @FirstID + ‘ – ‘ + @NextID This does work, it prints each variable with the dash in-between: PRINT CAST(@FirstID ...
For (almost) all developers, there’s no greater feeling, then knowing your source code is (relatively) safe. The same counts for T-SQL script. Especially if you know that you need the same code next year (argh). So first thing I did after the initial release of scripts, was looking for a way to check-in T-SQL from SQL Server Management Studio (SSMS). So a colleague of mine, Dries (Blog | Twitter), dropped by with a solution: TFS MSSCCI Provider 2010 from the Visual Studio Gallery. It’s a free plugin ...
BizTalk Server 2010 - Using the WCF-SQL adapter to make multiple stored procedure calls A recent project that I was working on required that I take an incoming flat file, containing multiple transaction records, and load these transactions into a database. Within the source file were multiple instances of any of seven transaction types. The database had a separate table for each transaction type, each with its own stored procedure for inserting a record. So thats seven transaction types and seven ...
I have been developing an MVC3 application with the intention of deploying to the Cloud. It was configured to use Forms Authentication with SQL Membership on SQL Azure. Everything seemed to be working perfectly locally, but after I deployed the Membership.ValidateUser no longer seemed to work. I could use the Providers to get information from the database (e.g. Membership.GetUser would return an existing account) which meant the database connection was fine and user existed, just the validation failed. ...
When you work with SQL Server, you regularly want to know when an object is last executed, last modified, etc.. Well, at least I do! In most cases you need to use the sys.objects to obtain this information, join it with other sys tables, search for your information, and so on. But there is an easier way. The script below will get you the latest information of any object in your database. USE SandBox SELECT DB_NAME(Txt.dbid) AS DatabaseName, OBJECT_NAME(Txt.objectid, Txt.dbid) AS ProcedureName, Objects.create_date ...
If you are planning to deploy your application with SQL CE as your application backend then here are the steps you need to perform. If you’re planning to deploy your application with SQL Server, than please read this post, How to Deploy an ASP.NET MVC 3 App to Web Hosting with "\bin Deployment" I assume that you have your ASP.NET MVC 3 application ready to be deployed with SQL CE 4.0 and EF 4.1. But make sure you have add assembly and for SQL CE 4.0 using NuGet. I recommend you to use NuGet here ...