Bunch's Blog

One day I'll have a catchy subtitle, one day
posts - 87 , comments - 122 , trackbacks - 0

SQL

There are 19 entries for the tag SQL
SSMS Slow To Save
When using SSMS 2008 I found it to be really slow when trying to save a file, even a small one with just some simple SELECT statements on it. The symptoms were the flashing save disk icons in the lower right corner and selecting the location to save in the Save File dialog would hang each time I picked a location (e.g. Libraries, a folder). This was the first I had seen anything like this where it was really, really slow. It ended up that a server had died last week and I was still mapping a drive ......

Posted On Tuesday, May 29, 2012 1:52 PM | Comments (0) |

Old SQL, Top and a Variable
I came across this the other day. Using SQL 2000 I could not get a Top statement to work with a variable. I had forgotten that only with SQL 2005 and higher could you do this trick. I found a good way around it, you can read about it here. Anyway this works on SQL 2000: declare @top intset @top = 5 BEGINset rowcount @top select Some_ID from tblStuffset rowcount 0END The code above would return the first five records from tblStuff. For any of the newer SQL Server version you can simply use the @top ......

Posted On Tuesday, May 15, 2012 1:48 PM | Comments (0) |

Searching SQL Server Objects
I came across this tool the other day and found it to be very useful. In the past if I needed to search within all the stored procedures or views in a database I would use some SQL to run a query using sysobjects and syscomments. That worked well but this is a bit slicker. The tool is redgate's SQL Search and it is available (for now at least) as a free stand alone tool. You can download it here. It supports SQL 2005 and later and I found it to integrate with the SSMS IDE well. It will add a red ......

Posted On Tuesday, May 8, 2012 9:41 AM | Comments (0) |

Using WCF Service With a Class

This post is an example of how to write a WCF Service using a class. The example uses Visual Studio 2010, written in C#, SQL Server 2008 and hosted in IIS. So lets have at it.

Posted On Wednesday, January 18, 2012 9:19 AM | Comments (1) |

Using Table Valued Parameters

In SQL Server 2008 you can use table valued parameters which can be pretty useful. In the example I use a very simple one to overcome SQL Server’s lack of having a parameter as an array. The example goes through creating the new type, using it in a stored procedure and calling it from an application (VB.Net in this example). You start off with creating the new type. Under Programmability/Types/User-Defined Table Types create a new table. Here I created a table with a single column to hold an int

Posted On Thursday, November 3, 2011 8:05 AM | Comments (2) |

PRINT Ints in TSQL
To print multiple int variables on one line you need to CAST them first. Otherwise the PRINT command will add them up (at least it does for me anyways using SQL 2008). In the example I needed to have two ID variables print out while I was working on a sproc so I could see if I was getting the correct IDs before adding in my update code. This did not work, it adds the two variables together: PRINT @FirstID + ‘ – ‘ + @NextID This does work, it prints each variable with the dash in-between: PRINT CAST(@FirstID ......

Posted On Thursday, October 20, 2011 10:48 AM | Comments (0) |

CTE Updating
Using a CTE to help with an update can be pretty handy. In this simple example the CTE is to select SiteIDs for a specific sales rep. The update is to change the address code to ‘Street’ since the rep entered them all in as ‘Mailing’ by mistake. In the update code you use an inner join to match up the SiteIDs from the CTE with the ones in tblAddress so you only update the specific sales rep’s sites and not all of them in the table. WITH CTE(SiteID) AS ( SELECT srs.SiteID FROM tblSalesRepSites srs ......

Posted On Friday, May 27, 2011 12:47 PM | Comments (0) |

Cycling Through Selected CTE Values
A great benefit of using a CTE is their ability to replace some uses of Cursors. Sometimes you may need a Cursor to fetch an ID value from a table. Use that ID in a Select statement. Then fetch the next ID value, use that in the same Select statement over and over again until you ran through all the ID values. A CTE can help eliminate that and from what I can see is generally a bit faster than using a Cursor. This example is using a table to hold purchase orders (tblPO) and another table to hold ......

Posted On Wednesday, April 13, 2011 1:05 PM | Comments (0) |

Combining Multiple Values in a Single Field
Here is a simple way to combine values into one field using T-SQL. This method is particularly handy when you don’t know ahead of time how many values will be returned. This simple example would return a student’s name and list of professors (separated by commas). SELECT stu.FullName, (SELECT prof.FullName + ', ' FROM tblProfessors prof INNER JOIN tblStuProf stuprof ON prof.ProfID = stuprof.ProfID WHERE stuprof.StuID = 1234 FOR XML PATH('')) AS Professors from tblStudents stu where stu.StuID = 1234 ......

Posted On Monday, September 20, 2010 10:53 AM | Comments (1) |

SQL Concatenate
Concatenating output from a SELECT statement is a pretty basic thing to do in SQL. The main ways to perform this would be to use either the CONCAT() function, the || operator or the + operator. It really all depends on which version of SQL you are using. The following examples use T-SQL (MS SQL Server 2005) so it uses the + operator but other SQL versions have similar syntax. If you wanted to join two fields together for a full name: SELECT (lname + ', ' + fname) AS Name FROM tblCustomers To add ......

Posted On Thursday, May 20, 2010 1:49 PM | Comments (1) |

Listing SQL Columns
When I am writing up stored procedures in SSMS sometimes I need to know what column types are used in a table. For instance I will know the table name but I might not remember exactly the length of a varchar column or if a column stored the data as an integer or varchar. And I may not want to scroll through all the tables in Object Explorer to find the one I want. A lot of times it is easier if I can just write a quick query to pull up the information I need. The syntax to do something like this ......

Posted On Wednesday, May 19, 2010 8:39 AM | Comments (0) |

Finding Duplicates in a Table
Here is an easy way to find duplicate data in a table. This simple example would return any userNames that appear twice or more in the table. SELECT userName, COUNT(userName) as UserDup FROM tblUsers GROUP BY userName HAVING Count(userName) >= 2 I find this handy to use when users need to know how many times a certain item appears but there is no way to sort that data in their application or there is no report written for them to use. It is also a useful check when migrating data over from an ......

Posted On Friday, November 6, 2009 1:25 AM | Comments (0) |

Updating Parameter Check
Every now and again you may come across an error when trying to update a database from a data driven control (i.e. gridview, formview) that is tied to a SQL or Object data source. The errors usually are along the lines of “Could not find xxx that has parameters” along with a list of those parameters. Or an error that states there are too many parameters being passed. Aside from looking for typos, counting or lining up the parameters I will use one of these techniques to try and narrow down what may ......

Posted On Friday, October 2, 2009 5:47 AM | Comments (0) |

Dynamic Order By in Stored Procedure
Here is a way to change the Order By in a sproc using Case. I find it useful when I am writing a sproc that specifically feeds a report (usually in SSRS) and has different sort possibilities. After the user picks the report and how they want it sorted from the application it is outputted directly to a PDF so they can’t change the sorting. Usually I will use a DropDownList with the various sorting criteria and pass the value back to the sproc (the @OrderBy param in the examples). For a regular SELECT ......

Posted On Tuesday, April 7, 2009 2:13 PM | Comments (0) |

Database Columns With Spaces
Why do you always forget the little things? I try to account for what users may do when using an application and add in checks and validation where I can. But something I don’t have to do a whole lot is to upload a user created Access database to our SQL Server. In my case it was a smaller Access database with just one table that I could use as some initial data for a new app. I already had a DSN setup and the Upsizing Wizard in Access actually worked pretty good to get the table structure and data ......

Posted On Tuesday, May 27, 2008 2:50 PM | Comments (0) |

Cascading DropDowns within a FormView (Without AJAX)
This was something else that gave me fits recently. I have a web app that uses a formview, which wasn’t that big a deal to me. Until I tried to have the value in one dropdown affect the value(s) in a second dropdown. For my problem the first dropdown holds manufacturer names and then the second displays the models by the selected manufacturer in the first dropdown. I could get the dropdowns to load up proper on the first load based on a SQL query, but the issue came about when I was testing changing ......

Posted On Monday, March 10, 2008 2:29 PM | Comments (1) |

Alternating Table Row Colors in SSRS (The Easy Way!)
A lot of the applications I work with utilize SQL Server Reporting Services (SSRS) for reporting. I was shown a way by the previous owner of an app to make a table show up in green bar (alternating colors). I added a function to the XML code for the .rdl to determine if the row is odd or even. Then I would add a <BackgroundColor> entry to each item in the table (again in the XML). It never took too long but was a bit of a pain to setup. I recently found a MUCH easier way. All you have to do ......

Posted On Friday, March 7, 2008 12:31 PM | Comments (6) |

Data into a SSRS Header
This was an issue for me a while back. I was making a report using SQL Server Reporting Services (SSRS) in SQL 2005 and I needed to put data into a textbox in the header. After searching the internet fairly thoroughly I found many posts and articles that pointed me in the right direction but they just didn’t work for me. The solution I used was to place a textbox on the body of the report and make it hidden. In the textbox I would add in the data I needed (i.e. =Fields or =Countrows). I would then ......

Posted On Wednesday, March 5, 2008 10:01 AM | Comments (20) |

Hello

Hello everyone!

This is my first post on my blog here so this is more a test than anything. I hope to blog about various items regarding SQL Server, VB.Net, ASP.Net and Ajax/JavaScript. Other technical blogging interests will most likely revolve around Windows, Linux, PC hardware and the XBox 360. Outside of the geeky realm I'll mostly write about soccer (or football if you prefer). Yeah I'm a Yank but I still love the beautiful game!

Bunch

Posted On Tuesday, March 4, 2008 11:23 PM |

Powered by: