Category: SQL

November 21, 2013

Exec sp_help on a table with a schema

Really quick post regarding sp_help and tables in Schemas (not in dbo). Usually when doing a sp_help, you can do something resembling the following : However, if you try to do the same with a table with a Schema (SomeSchema.TableName) you will get an error “Incorrect syntax near ‘.’”. To execute the sp_help on a table with a schema, simply...

Categories SQL/Tips and Tricks Comments: 0

January 31, 2013

Argument data type xml is invalid for argument 1 of like function

If you haven’t realized it by now, you cannot do a LIKE without any magic on a XML column. If you do, you will get the following error : Argument data type xml is invalid for argument 1 of like function If you need to do a LIKE on a column with XML, there are a few ways to go...

Categories SQL Comments: 0

January 30, 2013

What’s the Difference Between Where and Having in SQL?

A really quick post for you all today over the differences between Where and Having in SQL. This has been a question in a lot of interviews, but no one seems to know why and when to use each of these clauses. Where The where clause is used to filter based on criteria other than aggregates. So say for example...

Categories SQL/Tips and Tricks Comments: 0

January 15, 2013

SQL-IN : A Tool to Quickly Build The In Condition

So after years of copying data to Notepad++, then formatting to use in an IN condition, I decided to create an application to perform that function for me, at a fraction of the time. The tool is called SQL-In and can be used to parse data with common or a custom delimiter, then format to use with an IN condition. The...

Categories Development/SQL/Tips and Tricks/Tools Comments: 0

November 14, 2012

SqlDataAdapter Will Not Always Return a Result, SSMS Will

The Issue I ran into a strange issue with an ASP.NET site (MVC2 – 4.0 Framework) where when executing a stored proc, SqlDataAdapter.Fill(DataTable) will not return any rows some of the time, but SQL Server Management Studio will almost immediately return results. The full method calling the stored procedure is below : (Auto-generated from LLBL Pro V2.6) This issue occurs...

Categories C#/Development/SQL Comments: 0

October 23, 2012

View The Last 50 Queries Ran on SQL Server

I ran into an issue where I needed to see the last few queries ran on SQL Server (Currently using SQL Server 2005). There were quite a few suggestions out there, but one I found on Stack Overflow seemed to be formated the best and didn’t require me messing with the query. (See Query Below) This however wasn’t working for...

Categories SQL Comments: 0

August 25, 2012

Cannot find either column “[ColumnName]” or the user-defined function or aggregate “[ColumnName]”, or the name is ambiguous

I had the following, really simple, issue when adding no locks to my join statements. (I’m relatively new to nolocks since my other applications didn’t require almost immediate concurrent access) Note: Tables names were changed obviously And it was throwing Msg 4121, Level 16, State 1, Line 3 Cannot find either column “b” or the user-defined function or aggregate “b.index”, or the...

Categories SQL Comments: 0

April 13, 2012

Store update, insert, or delete statement affected an unexpected number of rows (0). Entities may have been modified or deleted since entities were loaded. Refresh ObjectStateManager entries.

I don’t know how many times when I was debugging my application which had two databases communicating, I got the following error : Store update, insert, or delete statement affected an unexpected number of rows (0). Entities may have been modified or deleted since entities were loaded. Refresh ObjectStateManager entries. This basically means something updated the row since the last...

Categories C#/SQL/Tech Fixes Comments: 2

March 29, 2012

Unable to update the EntitySet ” because it has a DefiningQuery and no InsertFunction

I was deploying a build on a cloud test machine with its own SQL Server and data. I was testing a sync function (a mobile handheld grabbing information from a server) and got the following error. Unable to update the EntitySet ” because it has a DefiningQuery and no element exists in the element to support the current operation. What...

Categories C#/SQL Comments: 0

March 13, 2012

Package ‘Microsoft SQL Managment Studio Package’ failed to load

I had  a fresh Windows 7 operating system, SQL Server install, and had just finished installing SQL Server management Studio(SSMS). I tried running SSMS and got the failed to load error. I had noticed when I was researching this error there were a few solutions besides the one I found, so I thought I’d compile them here. Solution 1 : Re-installing SQL...

Categories SQL/Tech Fixes/Tips and Tricks Comments: 2

March 13, 2012

Installing SQL Server Management Studio (SSMS) for SQL Server 2008

In years past, you could download the SQL Server Management Studio and it was pretty straight forward during the install and you were good to go in minutes. Now they seemed to have made a confusing way to install it which you will spend far too much time trying to figure out. So to save you time, I thought I’d...

Categories SQL Comments: 0

January 27, 2012

Simple Trigger Example in PostgreSQL

When I was looking for the syntax to do a trigger in PostgreSQL recently I was confused because of the complexity of the examples, even the PostgreSQL documentation. It was thorough, but all I needed was a quick example. (Microsoft’s MSDN has a bad habit of doing this as well) So after I got one working, I thought I’d post...

Categories SQL Comment: 1