Tag | SQL Posts

Last week, at the PASS (Professional Association for SQL Server) Summit in Seattle, Microsoft held a coming out party, not only for SQL Server 2012 (formerly “Denali”), but also for the company’s “Big Data” initiative. Microsoft’s banner headline announcement: it is developing of a version of Apache Hadoop that will run on Windows Server and Windows Azure. Hadoop is the open source implementation of Google’s proprietary MapReduce parallel computation engine and environment, and it's used (quite widely ...
If you write a SQL query, it’s important to know when certain pieces of you query are executed. For example, it’s possible that some statement in your query interferes with another part of your query, because of the execution time of a specific piece. The order of execution is: 1. FROM Clause 2. JOIN / APPY / PIVOT / UNPIVOT Clause 3. WHERE Clause 4. GROUP BY Clause 5. CUBE / ROLLUP Clause 6. HAVING Clause 5. SELECT Clause 6. DISTINCT Clause 7. TOP Clause 8. ORDER BY Clause So in many cases this ...
Yesterday, I just posted about How to Fix error: 26 - Error Locating Server/Instance Specified Well, this is an error message that we can find too "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 28 - Server doesn't support requested protocol)" I struggle to find what is the problem. ...
This is an error message that we can find when we connect to SQL Server: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) Hmm… We sometimes spend for hours to fix this issue and In this brief tutorial, I will ...
I cut my DBA teeth on IBM's DB2 UDB EEE (enough acronyms) back in the 90's. I have always appreciated the scalability that is available with a MPP architecture. So a couple of years ago I was excited to hear that SQL Server was going to have a MPP architecture available. Now I am at SQLPass Summit 2011 and seeing the PDW (Parallel Data Warehouse) and the hardware offerings by Dell and HP to scale that up and out. However, I was disappointed to learn that this technology does not extend to Analysis ...
Hi Everyone, This is a place for .NET lovers like us can go and learn more about all Microsoft Cloud technologies. This website makes it more easy, simple and actually fun to know what the technologies are, how they work and what we can do for it in the perception of both business and personal entities. Here is what you have to do (to make it much easier, follow the steps) 1. Go to http://www.microsoftvirtual... this website 2. Login with your Windows Live ID (Create one if you ...
Hi Everyone, The only and main purpose of this blog is to educate all .NET pre-Beginners, who love to do .NET Programming but don't know where, what and how to do. Now you have a place to share and ask for that little thing that is holding you to move on your development. I was in your place 2 years ago, dont know anything about it, doesnt know where to start. I did googled(Bing'd) a lot, found so may articles, blogs about .NET and got very excited about it. Now I wanted to get all that information ...
Ok, I think this is the coolest contest I have seen. Red-Gate is sending a DBA to Space! If you have mad SQL skills, you should definitely look into this contest. More Information: Red Gate, a U.K.-based software company, has launched a campaign that will reward a database administrator (DBA) with the ultimate getaway: a seat on a commercial flight into space, courtesy of Space Adventures, Ltd But first, DBAs must unravel a Gordian knot of a plot involving morphing Martians, pets in peril, alien ...
Hello Everyone, I thought I’d take a minute to update everyone on my upcoming talks and events that I will be attending. I have talks ranging from Kinect, Silverlight, HTML5 to OData! Wow, that’s a wide range of technology and I’m very passionate about everyone of them. Let’s get started. DevReach - October 17th through the 18th in Sofia, Bulgaria. I will not be speaking at this event only attending. I am very excited because this will be my first time visiting Bulgaria. I am looking forward to meeting ...
I got this error today while trying to access my new SharePoint 2010 environment via PowerShell: Cannot access the local farm. Verify that the local farm is properly configured, currently available, and that you have the appropriate permissions to access the database before trying again. If I would have just read the error message a little more carefully, I would have realized that I should start by looking at my permissions in SQL – which my account didn’t. This happened because I installed SQL ...
I came across (thanks Paul) a great way to quickly generate test data for SQL Server today. DECLARE @rows INT, @a int SET @rows = 50 SELECT * INTO dbo.RobTest FROM (SELECT TOP (@rows) Rownumber = ROW_NUMBER() OVER (ORDER BY (SELECT 1)), SomeInt = ABS(CHECKSUM(NEWID())) % 50000 + 1 , SomeLetters2 = CHAR(ABS(CHECKSUM(NEWID())) % 26 + 65) + CHAR(ABS(CHECKSUM(NEWID())) % 26 + 65) , SomeMoney = CAST(ABS(CHECKSUM(NEWID())) % 10000 / 100.0 AS MONEY) , SomeDate = CAST(RAND(CHECKSUM(NEWID())) * 3653.0 + 36524.0 ...
I'm currently in Seattle enjoying the start of some of the activities around the SQL PASS Summit. One of the activities that I'm looking forward to is the book signing session for the MVP Deepdives Volume 2 at lunchtime on Wednesday. I was fortunate to be one of the 60 or so authors this time around with a chapter on using Powershell to manipulate Analysis Services databases. All of the proceeds from this book go to support Operation Smile. You'll find the book's website here: http://www.manning.com/dela... ...
Oracle Dynamic SQL – Drop Table, Copy Table, Purge Tables CREATEORREPLACEPROCEDURE COMPLIANCE11.table_drop (table_name VARCHAR2) IS dml_str VARCHAR2 (500); BEGIN dml_str := 'DROP TABLE ' || table_name ; EXECUTEIMMEDIATE dml_str ; END; void the_Table_Housekeeping() { try { string JobDateTime_string = _jobDateTime.ToString("yyyy... JobDateTime_string = JobDateTime_string.Replace(... ""); JobDateTime_string = JobDateTime_string.Replace(... ""); Microsoft_Enterprise_Librar... ...
The SQL Server team or group of teams responsible for the learning content has done a fantastic job continually of providing great ways to work with their content. This time it is through the Virtual Labs made available to ramp on Denali. How to get it? Go to the Learning Center’s Virtual Lab Link. http://www.microsoft.com/sq... ...
Full Disclosure: I was one of the technical reviewers on this book. I think my friend Tomislav did a great job on this book and it would make a valuable addition to the bookshelf of anyone that is working with MDX. I really enjoyed reading this and there were even a couple of interesting techniques that I have added to my toolkit. As far as I know there are not any other MDX books on the market quite like this one. It's more aimed at the intermediate level of MDX user and assumes that you have some ...
Normal 0 false false false EN-GB 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:0cm 5.4pt 0cm 5.4pt; mso-para-margin-top:0cm; mso-para-margin-right:0cm; mso-para-margin-bottom:10.0pt; mso-para-margin-left:0cm; line-height:115%; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans... mso-ascii-font-family:Calibri; ...
In SQL Server we know 3 kinds of temporary tables: Local Temp Tables, Global Temp Tables and Table Variables. In this blog I will try to explain the differences, and tell you how to use it. Local Temp Tables The local temp table is the most commonly used temp table. This temp tables is created with the following statement: CREATE TABLE #TempTable (ID INT IDENTITY(1,1) NOT NULL, Description VARCHAR(10) NULL) The table that the script above created is a temporary table that is stored on disk. To be ...
Introduction To save on hosting costs and simplify maintenance, Software as a Service (SaaS) providers typically rely on schema separation to host multiple customers' records. This implementation relies on a specific SQL Server and SQL Azure feature called a schema object. A schema object behaves like a container, or a namespace in programmatic terms, allowing multiple tables (and other objects) to be stored with the same name in a single database. However schema separation comes with its own set ...
First we would like to thank each and every one of you who have supported Visual WebGui in the nominations for DevProConnections and Windows IT Pro. We appreciate all of our customers and friends of Gizmox for being with us each step of the way in our efforts to bring our unique products to the forefront of code trans-positioning and migration. Without further ado - Congratulations to our 3 big winners! Gevorg Horomyan Marcin Pytel Ugur Yildirim Please contact us at your earliest convenience at marketing@gizmox.com ...
In this Issue: AfricanGeek, Andrea Boschin, Colin Eberhardt, Anoop Madhusudanan, Lee, Laurent Bugnion, Rudi Grobler, Xianzhong Zhu, Jesse Liberty, Asim Sajjad, Dhananjay Kumar, XAMLNinja, and Kunal Chowdhury. Above the Fold: Silverlight: "Develop a Flexible 2.5D Scene Editor Targeting Silverlight RPG Games - Part 2" Xianzhong Zhu WP7: "A Windows Phone 7.1 (Mango) MVVM Tombstoning Example" Colin Eberhardt Metro/WinRT/Windows 8: "Consuming WCF Service in Windows 8 XAML/C# metro application" AfricanGeek ...

At http://www.sqlsharp.com/features/ there is a series of SQL CLR functions for SQL Server 2005 and later. The basic set can be downloaded for free! For $200, an additional 50+ functions are available.

The benefit of using them is well argued at http://www.simple-talk.com/content/article.aspx?article=1353

At http://blog.sqlauthority.com/2011/09/21/sql-server-denali-14-new-functions-a-quick-guide/ there is an excellent article on functions that have been added to the next version of SQL Server. I like in particular the TryParse function that will allow a more controlled approach to loading numerical data that has been manually entered.

FormView and SQL Datasource is awesome in my opinion. You can just bind your controls to data field in the markup, its great. Becomes a bit of a challenge when you want all your data related stuff to be done in the Data Access Layer, but hey, you have to bind the data to the UI one way or the other, right. Anyway, if you want to create your SQLDataSource in the code behind, you can easily do that. Here is an example: Function GetSQLDataSource() As SqlDataSource Dim sqlReturn As SqlDataSource = Nothing ...
This example is not covered in the ESB Toolkit samples and I bumped my forehead a few times while making it work. I thought it’d be helpful to save other fellow BizTalk'ers from headaches by publishing findings. I have been fun of dynamic generic messaging for quite a while and went long ways to avoid working with typed messages and static bindings when it made sense. Nowadays, with the ESB Toolkit one does not have to spend much effort to achieve this goal. But one has to learn intricacies of configuration ...
In order to test Performance in SQL Server, normally you would take a look at how may I/O or how many cycles a statement takes to complete. To make it less complex, you can take a look at how long a statements takes, just by looking at the execution-time. In most cases, network latency, SQL Server hardware and workstation performance are also included in this result (time to create result set, and time needed to send across the internet or internal network). And if a statement takes les then a second ...
In response to a post from an anonymous individual on one of my posts (http://geekswithblogs.net/... regarding free Microsoft tools I thought a follow up post was required! Anonymous, unfortunately you are mistaken; Websitespark is free and is open to individuals it truly provides all the possible tools & software that you could require to get started. If you read the program details on the Microsoft website (http://www.microsoft.com/w... ...
If you're working with large chunks of data, eventually you'll probably use a foreach loop to iterate through an enumerable data source and execute the same actions on each item (for example, do something to every DataRow in a DataSet). With the new Task Parallel Library (TPL) in .Net 4 you can execute these loops in parallel for a noticeable improvement in speed. .Net has had support for parallel programming since the 1.0 version, but the developer had to do (sometimes...) extensive work to create ...
How fun is it to use new software? As a developer you always want to use the latest software, and in many cases it’s backwards compatible. This (unfortunately) isn’t the case with Report Builder 3.0 and SQL Server 2005 Reporting Services. This is a “by design” issue, but it’s such an annoying “feature”. If you try to deploy a Report built in RB 3.0, you will get the error: "The report definition is not valid. Details: The report definition has an invalid target namespace 'http://schemas.microsoft.c... ...
At last, I can announce that ‘BizTalk Server 2010 Unleashed’ has been published and is available through major booksellers in both printed and electronic form. The book is not a new edition of the old ‘BizTalk Server 2004 Unleashed’ book from several years ago, although Brian Loesgen, our fearless team leader, provided continuity with that title. Instead, this is entirely new content written by a team of six authors, including myself. BizTalk Server is such a huge subject. It proved a challenge to ...
How often does it happen: you write an application that uses a SQL Database as source, and when you want to fetch data you get a time-out. Damn! But when you need to get a time-out (for example, if you want to test an exception), you don’t get one. In SQL Server you can create your own time-outs if you want. One of the simplest ways to generate a time-out, is to lock an object, and running a query against the object in another session. You can lock an object with a simple query: SELECT * FROM Company ...
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:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; 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;} To ensure the systems I am ...
How many times do you wonder about when an object was last updated? You can find out by using the default sysobjects in SQL Server: USE Adventureworks DECLARE @DBID INT = DB_ID('Adventureworks') SELECT o.name AS TableName, ddius.* FROM sys.dm_db_index_usage_stats ddius JOIN sys.objects o ON o.object_id = ddius.object_id WHERE database_id = @DBID ORDER BY ddius.last_user_update DESC This will give you the last_user_update, which is based on indexes. The Insert, Delete or Update are stored in the sys ...
At the beginning I want to inform that I've already read the similar question here: Entity Framework 4.0: is it worthy now? but it does not answer my questions and doubts concerning EF 4.0. Here's the case: We are working on a quite big project written in WPF and WCF. The structure of the database contains about 60 tables, lots of stored procedures, views and some functions. We are not using any ORM at the moment. Some time ago when the first version of EF was release we did not go with it because ...
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 ...
Recently I was assigned a task of helping a developer to cope with an unusual input file processing. The file we needed to load into SQL Server had double quotes surrounding those fields that contained comma(s). So a short excerpt from it would resemble something like: 123,ABC 456,"D,E,F" "7,89",GHS Since SSIS’s Flat File Connection does not support alternating text qualifiers, the chief difficulty stems from the fact if we even only remove the quotes, the file structure becomes broken because the ...
One benefit of my recent experience on a BA flight was that I got plenty of time to read through “Microsoft BizTalk 2010 Line of Business Systems Integration”. I’d promised the publisher weeks ago that I would take a look and publish some comments, but August has been such a busy month for me, and they have had to be patient. I should point out that, for the sake of transparency, that with another BizTalk book about to be released (next week) which I helped co-author, I have an urgent and obvious ...
A few weeks ago I ran into an error while setting up our test farm. I was creating the service applications and when I got to PowerPivot, I got the following error: I was able to work around this error by using PowerShell to create the service application. Background First, some details of our installation: two web front ends, two application servers, and one SQL server. All are running Windows Server 2008 R2 x64 with Service Pack1. The SQL Server is 2008 R2 with SP1. The SharePoint servers are SharePoint ...
The Visual Studio 11 Developer Preview was released to MSDN subscribers yesterday, and will be available in general release tomorrow (9/16). There's way too many new features to list them all here (for all the product highlights go to http://msdn.microsoft.com/e... but here are a few that interest me the most: Enhanced multi-monitor support .NET Framework regular expression syntax in Find and Replace IntelliSense enhancements for JavaScript JavaScript breakpoints ...
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 ...
You have at least 2 choices: Source 1: SQL Server 2008 Feature Pack http://www.microsoft.com/do... ... this includes Microsoft OLEDB Provider for DB2 Source 2: Available from IBM, is the IBM I Access pack http://www-03.ibm.com/syste... Remember, whichever one you use, you will need to specifiy which libraries under the detail/advance settings in ODBC ...
On the 9th Sep, the SQL Azure team announced that the new version of SQL Azure had just been release. In this version not only the SQL Azure engine had been upgraded, the SQL Azure Management Portal had been upgraded massively. Below are the features and improvements available in this release: Foundational updates for scalability and performance. Co-administrator support, which enables customers to specify multiple database administrators. Increased capability for using spatial data types, which ...
I'm working on a mobile location search app, and needed a way to find addresses within a certain radius of the device's current position. A little Googling found a simple function that will return the distance in miles: CREATE FUNCTION dbo.Distance( @lat1 float , @long1 float , @lat2 float , @long2 float) RETURNS float AS BEGIN DECLARE @DegToRad float = 57.29577951 DECLARE @Ans float = 0 DECLARE @Miles float = 0 SET @Ans = SIN(@lat1 / @DegToRad) * SIN(@lat2 / @DegToRad) + COS(@lat1 / @DegToRad ) ...
DECLARE @RegLoc VARCHAR(100) select @RegLoc='SOFTWARE\Microsoft... NT\CurrentVersion' EXEC [master].[dbo].[xp_regread] @rootkey='HKEY_LOCAL_MACHINE', @key=@RegLoc, @value_name='ProductName' The above code will return the installed version of windows on which the current instance of Sql Server is running. references: http://www.mssqlcity.com/Ar... ...
The ability to write scale out applications for SQL Azure will soon become much easier, thanks to the upcoming SQL Azure Data Federation capabilities. In an earlier post, I outlined specific steps developers can take to prepare for this key enhancement. Some of the key capabilities of SQL Azure Data Federation is to distribute large data sets across multiple databases, hence providing a mechanism to avoid the current database size limitations of SQL Azure. While it may be tempting to look at this ...
On SQL Server Central there is a very good article at http://www.sqlservercentral... on least priviledge access to a database. The only thing I can add to such an excellent article is that where there is a series of applications each with its own database on a common database server, there should never be a common account across all the databases that the applications use ...
While testing the performance characteristics of the SQL Azure backup tool I am building (called Enzo Backup for SQL Azure), I decided to try Spotlight (R) on Azure in order to obtain specific performance metrics from a virtual machine (VM) running on Microsoft's data center. Indeed, my backup solution comes with a cloud agent (running as a worker role in Azure) that performs backup and restore operations entirely in the cloud. Due to the nature of this application, I needed to have an understanding ...
If your orchestration is heavily rely on WCF SQL adapter based on SQL stored procedures, you may often encounter an issue related to strongly-typed schema and receive an error which is similar to the following. The adapter failed to transmit message going to send port "WcfSendPort" with URL "mssql://sqlserver//databas... It will be retransmitted after the retry interval specified for this Send Port. Details:"System.Data.SqlCli... Invalid object name '#Temp'. This is because of the ...
At http://devcheatsheet.com/ there is a comprehensive list of cheat sheets. Of particular note are: http://devcheatsheet.com/ta... SQL Injection! http://devcheatsheet.com/ta... ReSharper Cheat Sheets https://www.owasp.org/index... OWASP's SQL Inject Sheet Can't code withoutThe best C# & VB.NET refactoring plugin for Visual Studio ...
In this Issue: Tony Champion, Kevin Hoffman, Pete Brown(-2-), Mike Gold, Jeremy Likness, Xpert360, Den Delimarsky, Sumit Dutta, Asim Sajjad, Vikram Pendse, and Michael Crump. Above the Fold: Silverlight: "Silverlight 5: Remote control and MediaCommand Support" Pete Brown WP7: "Extracting a SQL CE DB from Isolated Storage in WP7 Mango" Michael Crump PivotViewer: "To CXML or not to CXML" Tony Champion Shoutouts: Michael Palermo's latest Desert Mountain Developers is up Michael Washington's latest Visual ...
At http://www.bbc.co.uk/news/t... there is a report on yet another SQL Injection attack. This time it is Nokiia's Developer network. How many other systems are vulnerable to such attack? If you run a Dot Net based web application, run FXCOP to check for vulnerabilities. Can't code withoutPowerful search and navigation in C#,VB, XML, ASP.NET, XAML and more ...