SQL
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...
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...
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...
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...
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...
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...
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
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 )...
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]
...
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...
Full SQL Archive