Tag | SQL Server Posts

During the last MICSUG meeting, Geoff Snowman gave a great presentation on "When to Use What" with regards to which remoting and messaging technologies should be used in certain scenarios. The SQL Server 2005 Service Broker (SSB) was mentioned a couple of times in regards to messaging systems and it got me a little intrigued to look at it. So What Is SSB? From the Microsoft SQL Server Books Online: Microsoft SQL Server 2005 Service Broker helps developers build scalable, secure database applications. ...
At TechEd 2006, I was quite impressed with the new SQL Server Everywhere. I am not sure why ‘Everywhere’, but I guess just because this program is so dang easy to install, that you will just start using it in all of your applications – therefore it will get installed ‘everywhere’. SQL Everywhere was formally called SQL Server Mobile and has been expanded to now run on the desktop. Rather excited about it myself as we are going to use it as a local store in our new smart ...
Yeah... I got my HP iPAQ 6515 this weekend. Although I am having some problems adjusting to this whole PDA idea whilst also using the thing as a phone, I am planning on using it as a way to get going with the .NET Compact Framework.So, I was looking for a good database solution for mobile devices - and then I saw this: Microsoft SQL Server 2005 Everywhere Edition CTP Yummy. Will be testing ASAP ...
So I'm in Boston at Tech Ed 2006. Lots to see and learn... Its been a great time so far. I went to a session today on changes to improve the integration between SQL Server Reporting Services 2005 (SSRS) and Sharepoint V3 (MOSS & WSS). Here's a few things I learned: The changes will be part of a SQL Server 2005 SP coming out a month or two after Office 2007 ships. Reports can be stored in Sharepoint document libraries and use Sharepoint's security, including report-level (like item-level) security ...
Geoff Snowman has announced that Joe Francis, a longtime Microsoft employee and expert on the Windows platform, has agreed to speak at the MICSUG meeting on June 22. Joe, a principal product specialist with Microsoft Corporation, specializes in identity management and Windows infrastructure. One of the most challenging issues in integration is cross-platform management of identity, and Joe will describe Microsoft's approaches for addressing this issue, including Microsoft Identity Integration Server ...
If you are wondering why you should upgrade to Biztalk 2006 (Pathfinder), here are some features why I feel it makes more reasonable to do an upgrade 1. Large message handling performance improvement. No more the messages is processed in memory and its written to disk and hence significantly reduces the "OutOfMemory" errors which you frequently see in BT2004 while processing large messages 2. BT2006 provides 64 bit support at no-cost .It support native 64-bit execution on Windows Server 2003 x64 ...
This is my first blog post ever. D'Arcy convinced me that I should start a blog to talk about all the good times at Tech-Ed. So here we go. I'm headed down to Boston for Tech-Ed 2006 in a couple days. Should be a great time. This is my first Tech-Ed and I've been looking through the list of sessions available and it's pretty overwhelming. Out of the 700 or sessions available, I picked out about 50 or so I might be interested in attending. I'll probably only have time for about a dozen or so, so I ...
I set off intending to experiment with Team Foundation Server recently to potentially replace our Subversion deployment. As I built up my virtual machine for the TFS application tier I went to install the data tier on our beefy 64-bit SQL Server machine and was stopped by a problem: SQL Server 2005 Reporting Services service is installed on the data-tier computer. The service should instead be on the application-tier computer. Uninstall SQL Server 2005 Reporting Services from the data-tier computer ...
I had to resort to using the web installer as the CS MSI installer didn't work (threw error stating it could not find MDAC 2.7 or above - even though I'm running XP Pro SP2 which has the MDAC components built into it - duh!). Download CS 2 web install & unzip. Edit Installer\default.aspx - modify the line: bool INSTALLER_ENABLED = false;so the installer is enabled eg: bool INSTALLER_ENABLED = true; Using IIS admin, create virtual dir (eg 'cs') pointing to the 'web' dir beneath the installation. ...
Well today is day one of the Mobile and Embedded Developer Conference here in Nice and I thought I would do my best to give a few brief overviews of what goes on at the event and maybe even podcast a few of the sessions, assuming MS allow me to host the content. :) The first session of the day was with Todd Warren, Corporate VP of Mobile and Embedded Development for Microsoft Corporation. His talk was all about the big picture of mobility, embedded devices and where Microsoft are planning on taking ...
In previous posts, I talked about designing your classes with security in mind. Designing assemblies is no different when it comes to keeping security in mind at all times. Today I will cover what things to keep in mind when designing secure assemblies. The basic points we will be covering today with regards to assembly security is: * Who is the target for the assembly * Identify Potentially Sensitive Code * Identify the Resources Needed * Explore Trust Levels * Abstract or Sandbox Highly Privileged ...
So, i've spent most of the day messing around with sql server 2005s new features and i'm impressed with the XML data type. The ability to store and search through well formed xml documents stored in database fields, even though it requires using xquery which is yet another lanugage is going to be very useful. I think i'll be sticking to T-Sql whenever possible for triggers and stored procedures, though I might write a c# function here and there just to see how it performs nothing to important.. yet.. ...
Well lots of buzz in the town.. watched some kwel thoughts around and listened to some wild wind about Teched 2006. But here is my recommended list for IT pro people and particularly those who work in remote management Believe me you should start from here {Click it}.... where Teched is more than we thought about it .... Dont miss the big MVP Logo on the left handside.... ask me if you want to know what it means...??? Some good sessions that I am interested in Using CLR inside SQL Server: Best Practices[Nauzad ...
I came across another tool to make my life easier. Red-Gate has released a product called SQLPrompt which provides Intellisense to your SQL. This is a free product (with registration) until September 1st, so hurry while supplies last. Let's go over briefly the features. This is a tray application that provides the Intellisenses to most major SQL Editors. It sits in the background and monitors when you have a .sql, .prc or other custom file extensions open. From there, it provides the Intellisense ...
The company that hosts one of the servers that I use online is using OpenVPN as their vpn server. And there are no drivers available voor the enhanced security model of Windows vista. Yep let's call it enhanced security model instead of f***** security dialog boxes that pop up whenever you want to do something that is not an installed program on your pc.I am loving vista absolutely so I'm not uninstalling because I don't think it works. I just think that in my case it is too early to make the move ...
Strange behavior from MS SQL Server 2005. I "spontaneously" get this error when trying to connect: Login failed for user 'sa' because the account is currently locked out. The system administrator can unlock it. (Microsoft SQL Server, Error: 18486). After some digging I've found this useful chain of posts, logged into the server locally and ran the following line: ALTER LOGIN sa WITH PASSWORD = 'your_password_here' UNLOCK I suspect what happened was that MS SQL Server decided to lock the sa login ...
Last Friday, Microsoft organized a training on SQL Server 2005. It was conducted by, Dinesh Priyankara, one of the MVPs in Sri Lanka. Allthough the plan was to cover most of the new features of SQL Server 2005, all we had time for was the new TSQL stuff. And at the end he just touched on the CLR integration. The presentation was really interesting considering I'm not much of a db guy. The presentation made me want to learn more on the subject area. So, thank you Microsoft and Mr. Dinesh Priyankara ...
Earlier this month, Microsoft released the Requirements Authoring Starter Kit (RASK) for Visual Studio 2005. It is available here: http://www.microsoft.com/do... RASK provides a customizable requirements-authoring solution for software development teams. It provides the basis for a Requirements Authoring solution and shows how to access Microsoft Visual Studio 2005 Team Foundation Server programmatically from Microsoft ...
In previous days I have talked about other ways to make your application secure. All of these lessons come down to a basic set of points: 1. Never trust user input 2. Use least privileged accounts What SQL Injection Is Today we will cover SQL injection, what it means and how to protect against this attack. SQL injection is a security vulnerability in an application that calls the database with SQL with unescaped characters which can lead to malicious data manipulation. This can be even more dangerous ...
I have a featured article in CSharpCorner Community about Data Mining using Analysis Service in Microsoft SQL Server 2005. That is a good article, because I talk about the underlying theory (Machine Learning) and how these ideas are being implemented using new released of Microsoft Sql Server. My article has rating 5 of 5 and a total page views of 5318 in this right moment. I'm very happy with this one. I want to share my joy with this community and encourage you to read my article. You can reach ...
Abstract: - No extra cost for 64-Bit support - Assign the orchestration to run in a host instance that has the 32-bit only property not selected. The host instance must be running on a Windows x64 machine. - BTS 2006 requires CPU hardware that supports AMD64 or EM64T but does not support Itanium-based hardware. However, BTS 2006 does support running with an Itanium-based SQL Server 2000 or SQL Server 2005 deployment - BTS 2006 allows to create MSI file that can contain both 32-bit and 64-bit dlls ...
If you've used Visual Studio, and ever switched to SQL Server (Query Analyzer, for example), you've probably wished for Intellisense. I know I have. Now, Red Gate, makers of the best SQL products (besides the server itself) out there, is offering SQL Prompt for free until September 2006. I've downloaded it, and will be installing it soon, considering I work with SQL stored procedures on a daily basis right now. It adds intellisense to a whole slew of products, including Query Analyzer and Visual ...
Ok, felt this bug was worth noting. I'm maintaining some code that was originally written to utilize a sql connection code generator called bonebox. I was notified of an error that occured where a few reports that were running were timing out. Upon reviewing my log messages I discovered that the sql server appeared to be blocking during connection attempts. (as a side note: I was informed later that another application I'm not responsible for was stuck and was eating up most of the processing power ...
If you've been using the SQL Server Management Studio that comes with SQL Server 2005 for a while, you might noticed this nice message box that shows all exception messages. Not only it shows the exception message, but also includes hierarchy of all inner exceptions. What's more you can see the technical details of the exception including its call stack. On top of that you can easily copy all this to the Clipboard. As soon as I've seen this I thought: "I wish I had something like this in my project". ...
Just ran into a bit of nastiness with the SqlParameter class in .Net 2.0. The scenario: I have a DateTime with a value of '05/30/2006 23:59:59.999' (that's 999 milliseconds). When I save it to a (Microsoft SQL Server 2005) DB using a stored procedure invoked by SqlHelper.ExecuteNonQuery(), the value surprisingly changes to '05/31/2006 00:00:00.000'. A bit of step-by-step debugging led me to the culprit. When the DateTime is stored in a SqlParameter to be passed to SqlHelper.ExecuteNonQuery(), the ...
The Never Ending Story (Pt1): With the Latest release of SQL Server Microsoft have really gone to town on BI. To understand this a little better I am going to run a series of interlinked blogs looking at this topic. Today we will start with “What is BI and what goes to make it” BI or Business Intelligence is a relatively new term but with old roots. The original concept is the same as it has always been: To make better use of the information collected by a company/business to help the ...
Today for me was a day of learning. It started morning, when I attended a free seminar from Microsoft on SQL Server 2005 Intelligence. We went through Analysis Service, Integration Services, Data Mining and Reporting Services. It was first opportunity for me to actually work with SQL Server 2005 and, although I’m not much a database guy, but I really liked it. I’ve already installed the trial version and I’m going try out some of the things I’ve learned. So if you have a chance ...
I finished orientation for my new job yesterday. I'll be starting Hanover County Public Schools on their first big .NET project. It'll be my first exposure with IBM DB2, but hopefully that will eventually go away to be replaced with SQL Server or Oracle. In other news, I've seen almost 180 hits on my articles that I published last week! That's certainly better than I expected. No feedback yet, so I'm not sure if that's good or bad. I've started work on my next article, sending email in .NET 2.0. ...
You can do magic: In a Connected Systems World the addition of Service Broker to SQL 2005 can be seen as ‘yet another messaging option” and its arrival applauded. Perversely it can also been seen in a negative light as a threat to BizTalk 2004 and so interesting discussions have come ourt of this thinking. For my part I welcome the addition, I am also a little sceptical about when I’ll opt to use it! Service Broker OK so what really is Service Broker: In the SQL world its a tentative ...
I've just wrapped up the second part of my articles on Inserting into SQL Server without the VS Designer. This is only my second article, so comments are welcome! Inserting into SQL Server, without the Designer - Part 2 - Creating a Connection And if you haven't read part 1, you can find it here: Inserting into SQL Server, without the Designer - Part 1 – Creating a Stored Procedure Chris ...
Microsoft has published another team blog, this time for the data team, those are the gurus bhind SQL server and so, not the data access (who already have their own blog). Check the data team blog. (introduction) Als, Channel9 has started a new series of videos about upcoming data access technologies at Microsoft. The frst episode is on the general vision behind the new technologies. It's a combination of various talks regarding O/R problems and solutions, and a demo for DLINQ. You can check it, ...
Below is a question that was posted to AZGroups earlier this afternoon. I removed the database and table names because I'm downright anal about security :) Having table problems, and I am just stumped.... Our dev website started coming back with the following error when a user logs in: Microsoft OLE DB Provider for SQL Server error '80004005'Warning: Fatal error 605 occurred .... Microsoft's MSDN gives: ERROR_BAD_COMPRESSION_BUFFER 605The specified buffer contains ill-formed data.(http://msdn.microsoft... ...
Tonight's Toronto SharePoint Users Group meeting will feature the Business Scorecard Manager: Business Scorecards provide graphical snapshots of your business data, revealing underlying trends and critical inflection points. Come learn out how this latest Business Intelligence tool works with SQL Server Analysis Services (for SQL 2000 or 2005) to make real-time business dashboards that management will love. [See a demo] You can register at the link above. See you there ...
Well, technically its just day 2, but counting the UG Summit, Wednesday is Day 3. I blogged a bit earlier about the night before, so this is more of a recap for today in general. I was really looking forward to Derek Hatchard's talk on Open Source vs Team System for testing and building, but I heard he had a bout of the flu so it was cancelled (I saw him around later on in the day, so I hope you're feeling better man). Instead, I checked out Beth Massi's talk on datasources and databinding. Great ...
OK, so this is my first ever blog post and I've decided to write about recent changes made to the Microsoft Virtual Labs. If you have no prior experience with these Virtual Labs, they allow you to experience Microsoft software and technologies in a virtual, hands-on lab environment. Say you want to try out some of the new features of SQL Server 2005 but you don't want to download and install the server software, you can take a Virtual Lab instead. Best of all, it's free. In the current issue of Windows ...
If you're developing WinForm applications, generally at some point in the project you develop an Exception Dialog box that provides a user friendly interface for error messages. Usually, it's a generic box that displays parts of the exception stack and perhaps a way to log or allow users to initiate some other action such as proceeding, ignoring, or even reporting the error. Usually the solution hooks in as the Application or AppDomain unhandled exception handler at startup. Thing is, I don't see ...
As normal service has resumed and I’m blogging again (admittedly I’m not going to blog about absolutely everything) here is a wonderful little utility I found on my travels for adding Intellisense to editor in SQL Server. It’s called promptSQL from Red Gate. I really love how Intellisense makes me more productive (i.e. lazy) in Visual Studio and I was wondering about its rather obvious admission from SQL Server 2005 Management Studio, missing a trick strings to mind. Anyway for ...
I ran across this weird problem while debugging a stored procedure in Sql Server 2000. Here's a sample stored procedure: drop procedure p1gocreate procedure p1as create table #t1( col1 int identity(1,1), col2 datetime, col3 int) select *from #t1 return update #t1set col3 = col2return Ok, notice that I have a return statement after the first select. That is my exit point for now because I am debugging so I am not interested in the section after that. Also notice that the update statement will throw ...
Kudos and cheers to: INETA for the speaker's bureau RV.NUG membership for the keen interest in .NETSahil Malik for a fantastic presentation Hard to believe that last fall I was considering giving up on being a UG leader. I sure am glad I didn't (our founder checks in often to make sure I don't quite). Since the VS 2005 / SQL Server 2005 Launch we keep growing and growing and ........The May meeting of RV.NUG was no exception. Sahil Malik (brought to us by INETA) presented on Transactions. He discussed ...
When I was helping out a coworker with an issue for data mining for a logging and auditing ISAPI tool that we created (don't worry I'll get to the basic concepts of what we did), we realized we needed an easy way to pull information from AD easily during our data loads. SQL Server makes this easy of course with the OLE DB Provider for Directory Services. Information about this can be found here: http://msdn2.microsoft.com/... for SQL Server 2005. The reference from ADO 2.8 ...
In .NET 1.x if you wanted to modify the stack size of a thread you created you would have to go through some pretty nasty code .. Here is a hint :) [DllImport("kernel32.dll", SetLastError=true)]static extern int CreateThread (ref SECURITY_ATTRIBUTES lpThreadAttributes, int dwStackSize, ref int lpStartAddress, ref object lpParameter, int dwCreationFlags, ref int lpThreadId) In 2.0 a new overload has been added to the Thread class to support alterring your stack size directly through managed code http://msdn2.microsoft.com/... ...
I'm trying to add a solution to sourcesafe in Microsoft SQL Server Management Studio (or Visual Studio, it behaves the same), but it seems every time I do this I always manage to create a silly structure like this: [AllMyProjects] [ExistingProject] [MyNewSolution] [MyNewSolution] [MyProject] *Project Files* So I get this duplication of the solution name repeated in sourcesafe! Pretty annoying! It turns out that the way to avoid this is make sure you understand the completely user-unfriendly UI that ...
Recently, one of my peers asked a question regarding the ability of SQL CLR classes to read configuration data from an Application Configuration file. After doing some quick Google searches on the topic, I came up with a couple of links that indicated the potential, but no concrete HOWTO on getting it all wired up. Now, I don’t want to debate whether this makes good architectural sense to store static settings in the file system when you’re already working inside a database management ...
The last month was pretty rich in terms of the free downloads I found available online, so, thought I needed to list some of those, and clearly got busy/late for that! however, I thought I'll also drop the list in case someone missed any. Form Scott Guthrie (AKA: ASP.NET Hero)'s Blog: Source Code for the Built-in ASP.NET 2.0 Providers! This really a BIG hit. The source code for the whole set of ASP.NET 2.0 providers downloadable for free, and also, Scoots points to 130+ pages of awasome pages walking ...

According to this page on the Microsoft site, the new 70-431 exam now counts towards an MCSD certification, and you get a TS: SQL Server 2005 to boot!

D

It is amazing the amount of cycles, hardware and brain ware, go into paging results efficiently. Recently Scott Mitchell authored an article titled Efficiently Paging Through Large Result Sets in SQL Server 2000 that looked at a stored procedure that returned a particular "page" of data from a table. After examining Scott's approach, I saw some potential improvements in his method. In this article we'll look at two ways to improve Scott's method. The first approach uses a table variable (just like ...
There was a question on the newsgroup a few months ago asking how to extract data from an Analysis Services cube and store it in a SQL table using SSIS. I responded saying that in theory you should be able to setup a connection using the OLE DB provider for MSOLAP 9.0 and then put an MDX query in place of the SQL text. And I even when as far as setting up a package and previewing the data to make sure this was a viable approach. To this point everything looked fine and I assumed that the next step ...
I'm stumped. I've got an instance of SQL 2000 (staging\staging2000) running on a box side-by-side just fine with an instance of SQL 2005. When I'm working in Visual Studio on the staging box, it works just fine. I connect and get into the 2000 instance of the database with no problems. As soon as I move over to my laptop (same configuration), I get ... 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 ...
SQL Server 2005 offers a number of new features over its predecessor, including many features aimed at making working with databases more like writing .NET application code. For example, in SQL Server 2005, stored procedures, triggers, UDFs, and so on can be written using any .NET Framework programming language (such as Visual Basic or C#). Another feature, and the focus of this article, is SQL Server 2005's support for TRY...CATCH blocks. Read An Easier Approach to Rolling Back Transactions in the ...
When you open Reports from within MS CRM 3.0 after installation, it is possible to receive an error stating something like "An error has occurred during report processing. (rsProcessingAborted) Cannot create a connection to data source 'xxx'. (rsErrorOpeningConnection)". The solution to this error is not easy because there are many possible reasons. Since I encountered this error several times now, I developed a solution strategy that works for me. Since there are also other peoples encountering ...