If you're binding an ADO.NET DataTable to Infragistics UltraWebGrid and then programmaticaly sort the grid (e.g. add a column to a band's SortedColumns collection) you may get an error:
Cannot find column My Column Name.
with stack trace starting from grid databinding and finishing in datatable's sorting:
at System.Data.DataTable.ParseSortString(String sortString)
at System.Data.DataView.CheckSort(String sort)
at System.Data.DataView.set_Sort(String value)
at Infragistics.WebUI.UltraWebGrid.DBBinding.ProcessDataViewForFillRows(DataView dataView, RowsCollection rows)
at Infragistics.WebUI.UltraWebGrid.DBBinding.FillRows(UltraWebGrid grid, RowsCollection rows, IEnumerable datasource)
at Infragistics.WebUI.UltraWebGrid.DBBinding.BindList(IEnumerable datasource)
at Infragistics.WebUI.UltraWebGrid.DBBinding.DataBind(Object dataSource, String dataMember)
at Infragistics.WebUI.UltraWebGrid.UltraWebGrid.DataBind()
If the grid binds OK without sorting and grouping, but fails with either - most likely the culprit is one of the columns in data table. Read more...
If you're writing an SQL upgrade script that updates a field in your table from NVARCHAR(n) data type to NVARCHAR(max) and the script is designed to run many times - an easy way to avoid multiple table alteration is to check for max character length for that field, i.e.:
IF (SELECT CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'MY_TABLE' AND COLUMN_NAME = 'MY_COLUMN')<>-1
BEGIN
ALTER TABLE [dbo].[MY_TABLE] ALTER COLUMN [MY_COLUMN] [nvarchar](max) NOT NULL
END
This code checks length of the field which is specific number for standard NVARCHAR and -1 for NVARCHAR(max), and alters column type only if it hasn't already been altered.
Let's say you need to add a column to an existing table that already contains some rows. And this new column needs to be prepopulated with a default value. For example integer nullable column NEW_COLUMN is added to table MY_TABLE. Common approach is to check for column existence, and if it doesn't exist - create and populate it:
if not (exists(SELECT * FROM SYSCOLUMNS WHERE ID = OBJECT_ID('[dbo].[MY_TABLE]') AND Name = 'NEW_COLUMN'))
BEGIN
ALTER TABLE [dbo].[MY_TABLE] ADD [NEW_COLUMN] [int] NULL
UPDATE [dbo].[MY_TABLE] SET [NEW_COLUMN] = 1
END
But this doesn't work - you get "Invalid column name" error. The trick is - you don't need a separate UPDATE statement. ALTER TABLE ... ADD statement has a DEFAULT parameter. You can modify previous code like this:
if not (exists(SELECT * FROM SYSCOLUMNS WHERE ID = OBJECT_ID('[dbo].[MY_TABLE]') AND Name = 'NEW_COLUMN'))
BEGIN
ALTER TABLE [dbo].[MY_TABLE] ADD [NEW_COLUMN] [int] NULL DEFAULT 1 WITH VALUES
END
This ALTER statement assigns default value of 1 to the new column. WITH VALUES parameter is important, it populates existing rows with the default value (without it it will be populated by NULLs).
December 11th, 2009
Yuriy
Sometimes I bring information to a couple of my other WordPress blogs via RSS feed. It's a nice feature, allowing you to create several posts at once without manual entry. Unfortunately if RSS feed is broken or improperly formatted it can result in blank posts imported into the blog.
I was looking for a WordPress plugin that would allow me to mass-delete empty posts, but apparently none exist. You can delete posts based on date, tags, category, but not the content. Fortunately if you have access to phpMyAdmin of your MySQL installation - there is a solution. Read more...
I was running a basic LINQ 2 SQL statement:
From role In db.user_role _
Where role.USER_ID = Session("user_id") Select role
when I encountered following error message:
Method 'System.Object CompareObjectEqual(System.Object, System.Object, Boolean)' has no supported translation to SQL.
After a little research I found the solution.
Read more...
Imagine after running a query like this:
SELECT ContactName, Country FROM Customers ORDER BY Country
on Northwind database and getting following result:
ContactName Country
Patricio Simpson Argentina
Yvonne Moncada Argentina
Sergio Gutiérrez Argentina
Georg Pipps Austria
Roland Mendel Austria
Catherine Dewey Belgium
Pascale Cartrain Belgium
Anabela Domingues Brazil
Paula Parente Brazil
Bernardo Batista Brazil
Lúcia Carvalho Brazil
Janete Limeira Brazil
Aria Cruz Brazil
André Fonseca Brazil
Mario Pontes Brazil
Pedro Afonso Brazil
Elizabeth Lincoln Canada
Jean Fresnière Canada
Yoshi Tannamuri Canada
...
You're asked to retreive only the 2nd and the 5th group of contacts. How do you do it? Read more...
T-SQL dialect of SQL doesn't have aggregate functions for strings, but there is an easy workaround using magic of XML.
Consider Employees table of the Northwind database. When I run following query:
SELECT Country, FirstName FROM Employees ORDER BY Country, FirstName
I get following result:
Country FirstName
UK Anne
UK Michael
UK Robert
UK Steven
USA Andrew
USA Janet
USA Laura
USA Margaret
USA Nancy
Now I want to combine first names into comma separated strings grouped by country. Read more...
IsNumeric(data) is a widely used function in SQL Server programming. If passed in data is a number - it returns 1, otherwise 0. Supposedly. There're several cases when it returns 1 even though the data is not numeric. Some of those cases are: dollar sign "$", minus sign "-", space, tab. If you try to call Cast or Convert function on the data after that to convert data to number - they will throw an error
An excellent article at SQL Hacks suggest adding "e0" to the data when passing it to IsNumeric function. What it does essentualy is making the number to be in scientific format, e.g 12.34 becomes 12.34e0 (or 12.34 * 1). Which still remains a number. So IsNumeric(12.34e0) returns 1 while IsNumeric($e0) returns 0.
Which works great until actual number in scientific format is passed. If you try to test for example 1.234e5, IsNumeric correctly returns 1, but by adding "e0" as article suggest IsNumeric(1.2345e5e0) returns 0.
To work around this limitation I simple test for an "e" within passed data. If it exist, I am calling IsNumeric as usual, if it does not - I am adding "e0" to the tested data:
CREATE FUNCTION IsRealNumber (@i_sValueToTest nvarchar(250))
/* This function tests if passed in value a real number */
RETURNS int
AS
BEGIN
DECLARE @iResult int
-- if there is 'E' in the value - do a standard IsNumeric test
IF CHARINDEX('E', UPPER(@i_sValueToTest))<> 0
SET @iResult = IsNumeric(@i_sValueToTest)
-- Otherwise do a modified IsNumeric test by adding 'E0' at the end
ELSE
SET @iResult = IsNumeric(@i_sValueToTest + 'E0')
RETURN @iResult
END
I've encountered a problem using SQL Server Reporting Services 2005 (I am using SSRS from an ASP.NET application). Underlying stored procedure can return different number of fields depending on one of the parameter. I.e. if that parameter is NULL the fields returned are
A B C
if that parameter is "1", the fields returned are:
A B C D_1
if that parameter is "1,2", the fields returned are:
A B C D_1 D_2
etc. And I need to be able to use those fields with the report (in particular - I need to group on them). But report's dataset has a fixed number of fields based on original query and new dynamic fields are inaccessable.
The solution is to modify report definition. I am already doing it to insert custom groups in the report, and reports dataset is exposed via definition as well. The definition can be downloaded via GetReportDefinition of SSRS Webservice and serialized into a class (See Tutorial: Updating Reports Using Classes Generated from the RDL Schema for more details). The DataSet in that definition contains array of fields that can easily be resized in code to accomodite for new fields. This way the fields in the definition will match fields returned by the query and can be accessed within the report.
Had a weird error today in my MS SQL Query that had worked for months.
Cannot sort a row of size 8304, which is greater than the allowable maximum of 8094
The only difference - a few text fields the query returned had larger amount of data than usual. What saved my day is ROBUST PLAN hint, when I added OPTION(ROBUST PLAN) to my ORDER BY clause - the query ran perfectly. So if you ever experience similar problem (usually it happens when you join several tables with large sets of data) try this approach.
For detailed explanation of ROBUST PLAN and other hints visit Microsoft Tech Net
Recent Comments