Shawn Weisfeld

I find when I talk to myself nobody listens. - Shawn Weisfeld
posts - 365, comments - 174, trackbacks - 34

My Links

News


Shawn Weisfeld's Facebook profile

The views expressed in this blog are mine and mine alone, not that of my employer, Microsoft, or anyone else’s. No warrantee is given for the quality of any material on this site.

Archives

Post Categories

SQL

Entity Framework insert performance

While I am big believer in Entity Framework (EF) there are some cases where dropping down to raw ADO.NET make sense. In an application I am working on I need to insert thousands of records into the database, and while the change tracking mechanism and other features in EF provides a great service sometimes the overhead of it doesn’t outweigh the benefits it offers. I threw together a quick little sample app (you can download it from GitHub here) that shows the problem. The app simply creates a bunch of random customer objects and saves them to sql server. Here...

posted @ Thursday, January 17, 2013 12:28 PM | Feedback (0) | Filed Under [ SQL ]

"Why should DBA's support Azure?"

Recently I heard a question asking "Why should DBA's support Azure?" I thought I would muse a bit on it here. IMHO it boils down to the question "What is your job?" I ask myself this all the time. Is it my job to write really cool code or to tune that sql query? I don't think so. I think my job is to "solve business problems". Regardless of if that problem is provide accounting the ability to do their thing or give people the ability to purchase our products, IMHO, technology is the...

posted @ Wednesday, August 18, 2010 6:39 AM | Feedback (1) | Filed Under [ SQL ]

Could not open a connection to SQL Server

Problem: .NET application throws the following error. 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: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) Solution: Check SQL Server to ensure that TCP/IP is enabled. Open the List of available “Protocols for SQL Server”. You can find this in the “Computer Management” Console under “Services...

posted @ Wednesday, July 8, 2009 11:44 AM | Feedback (1) | Filed Under [ SQL ]

Using ROW_NUMBER() to eliminate the multiplicative effects of a join

Problem: It is a common situation to have a header/detail tables in SQL Server. Additionally sometimes you include a calculated value on the header table to make reporting easier. For Example lets say you had an SalesPerson table that had a column for total sales. Now you also have a SalesDetails table that lists every sale that the company made. The purpose of having the total sales column is that it eliminates the need to touch the larger SalesDetails table when you just need to get the total by sales person. This is a good thing until the user...

posted @ Friday, July 3, 2009 12:33 PM | Feedback (6) | Filed Under [ SQL ]

SQL CLR: Query the file system to get a list of folders

Today was a good day for questions, got one asking how to query the file system from sql server. See they have a list of network shares that they want to find out what folders are in each. SQL CLR to the rescue. . . First lets write a Table Valued Function to get a list of folders. I chose a Table Valued Function so I can join it to a list of paths to search in sql server. Remember that since we are leaving the confines of the SQL Server to get data from the disk we...

posted @ Thursday, June 11, 2009 5:12 AM | Feedback (0) | Filed Under [ SQL ]

SQL Server: Who am I?

Got a question today. How do I tell what user my sql server query is running as? Well you just ask. Using the USER_NAME() function, without passing it an id, finds the name of the current user. http://msdn.microsoft.com/en-us/library/ms188014.aspx Using the SUSER_NAME() function, gets me the login identification name of the user. http://msdn.microsoft.com/en-us/library/ms187934.aspx   Here is an example of each, using both a regular sql server login (left) and windows authentication (right)   Now that I know who I am, why not be someone else. . . In this...

posted @ Thursday, June 11, 2009 3:48 AM | Feedback (0) | Filed Under [ SQL ]

SQL Server 2008 Access is Denied Error During Install

Problem: when installing SQL Server 2008 you get an Access is Denied Error. Here is the error you get during install. . . . Fix: Check your Local Security Policy, you need to ensure that Administrators have “Debug programs” permission (Security Settings | Local Policies | User Rights Assignment) More information can be found in the following articles: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=390424 (If you have had this error please follow this link and vote that it gets fixed in connect) http://social.msdn.microsoft.com/forums/en-US/sqlsetupandupgrade/thread/ed4514fa-8d1d-4383-bbdf-fb06bfbad106/ http://support.microsoft.com/?kbid=2000257

posted @ Monday, June 8, 2009 10:33 AM | Feedback (2) | Filed Under [ SQL ]

Cross Tab Queries in SQL Server V3

Using the example from my last post (Cross Tab Queries in SQL Server V2) I was asked to figure out how to make the query more dynamic. The only way to make it truly dynamic (i.e. return a different # of columns) would be to use dynamic SQL. However lets say you want to do a sliding window of 5 years for reporting. . . . Here is an example that utilizes a table valued function. . . . . CREATE FUNCTION tvfTest (     @year int )...

posted @ Tuesday, March 10, 2009 2:07 AM | Feedback (0) | Filed Under [ SQL ]

Pivot/Cross Tab Queries in SQL Server V2

Back in 2008 I blogged about Pivot/Cross Tab Queries in SQL Server (http://drowningintechnicaldebt.com/blogs/shawnweisfeld/archive/2008/01/21/pivot-cross-tab-queries-in-sql-server.aspx). I was asked for another example so here goes:--Sample Sales Table CREATE TABLE [dbo].[Sales]( [ClientID] [int] NOT NULL, [SalesDate] [datetime] NOT NULL, [SalesAmount] [decimal](18, 0) NOT NULL ) ON [PRIMARY] --Added sample data SELECT [ClientID] ,[SalesDate] ,[SalesAmount] FROM [Sales] ClientID SalesDate SalesAmount 1 1/1/2006 100 1 1/1/2007 200 1 1/1/2008 300 1 1/1/2009 400 2 1/1/2006 150 2 1/1/2007 250 2 1/1/2008 250 2 1/1/2009 250--Create view to report Sales by Year by Client SELECT [ClientID] ,DATEPART(yy, [SalesDate]) AS SalesYear ,SUM([SalesAmount]) AS [SalesAmount] FROM [Sales] GROUP BY [ClientID] ...

posted @ Tuesday, March 10, 2009 12:32 AM | Feedback (5) | Filed Under [ SQL ]

What do your error messages reveal?

I know that most of you all know this already, but I figured it was worth a blog post since I just saw this from http://www.autosite.com. I got this very informative error message back when using their site earlier. . . Query Of Queries runtime error.Expected type STRING but encountered type NULL on the left of the LIKE condition  The error occurred in D:\INETPUB\wwwroot\content\Research\kbb\act_valueReport.cfm: line 74 Called from D:\INETPUB\WWWROOT\content\Research\kbb\index.cfm: line 2172 : 73 :  <cfmodule template="mod_kbbtable.cfm" width="#kbbModTableWidth#">74 :  <cfquery dbtype="query" name="qEquip">75 :  SELECT * FROM valueReport.Equipments WHERE GroupCode LIKE 'M' ORDER BY DisplayOrder76 :  </cfquery> While it is important for us...

posted @ Monday, March 9, 2009 12:46 PM | Feedback (0) | Filed Under [ .NET ASP.NET C# SQL VB.NET ]

Full SQL Archive

Powered by: