Basically, I have found that .. and - give different results in a character range comparison. [A-Z] will behave differently to [A..Z] See article below: http://www.mssql.com.au/kb/html/gmgxsl.dll/htmlkb?usexsl=y&sp=psearch_articl e_text&@sa_id=66 ...more >>

You can probably figure out what I'm trying to accomplish by loking at the below better than I could explain. I'm getting syntax errors. Can someone lend some insight? DECLARE @A varchar(255), @T varchar(255), @BT int, @BA int, @SBF int, @MS varchar(5) SET @A = 'A' SET @BT = '1' SET @MS...more >>

Hi, We are currently deploying an Itanium based SQLServer 2005 installation and are having trouble with a CLR UDF developed using vb.net. The UDF was originally tested on a 32 bit SQL Server 2005 with amazing results. The result was that some queries that were using the T-SQL version (S...more >>

Converting a varchar column value of '2006-12-07 11:45:43' to '07-12-2006 11:45:43' using the function convert(datetime, createddatetime, 131) gives error "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.". How can I fix this?...more >>

Hi, I have written some SQL queries in ASP applications to build an organization structure. Since some managers have deeper hierarchies than others, I see the execution over the website taking a long time - 32 seconds which is too long. Deeper hierarchies means it can go up to 4 levels deep fo...more >>

Hi I'm trying to improve the performance of a kind of "pivot" procedure, which takes a master Orders table and child Order Items table, and pivots the child table "Product Type" onto columns, with the Quantity for each Product Type in the relevant column. Here's some sample DDL that sums it...more >>

Hello All, Some time ago, I implemented a data access layer that included a simple connectin pool. At the time, I did it all by myself: I created N connections, each connection associated with a worker thread that would execute the db commands. The pool was fixed and all the connections were...more >>

Hi, Using SQL 2005 Express Studio when creating new table how can the structure be copied from an existing table in the same db or another db on the same server? Happy Holidays Thanks, Roger ...more >>

Does anyone have any good ideas on how to build a complete ancestor/descendant hierarchy from a id/parent-id table using CTE/recursive queries? I have succeeded in finding all ancestors/descendants for a specific node, but what I now want is a list of all ancestor/descendant-relations in the tab...more >>

Hi, I have a table which has got a column called as Balance which needs to be calculated based on the value of the last record. The schema of the table is as folls: Create table Txn ( AUTOID INT IDENTITY(1,1), DEBITS MONEY, CREDITS MONEY, BALANCE MONEY ) Insert Into Txn v...more >>

I have a dropdownlist that can't match up a NULL value to entry during binding giving me this error: has a SelectedValue which is invalid because it does not exist in the list of items. Parameter name: value I know I can get around the issue by doing a union with a DBNULL in the column bef...more >>

I have four tables. One is BankMaster, Regions, SubRegions, and XRegions The rules are for any given bank, it can be in any region (A region is a State - CA, OR, NY, etc....) and can even be in multiple regions. There can be manu SubRegions also, - Los Angeles, San Francisco, Fresno, etc......more >>

I see that DTS is now Legacy in 2005, so what's the replacement please? Thanks ! ...more >>

I have a categories table that will contain many of the same number in the parent_id field (column). How can I write the query to just select one of each number in that column? Example: id parent_id parent_code name description 1 1239 NULL Golf Cart Battery Cha...more >>

Is is redundant (unecessary, wasteful) to create an index ... on a column which is already a Primary Key? Here is my script: IX_EmpTest_EmpID is the item of discussion/concern. I believe it is unnecessary, but wanted to double check. I'm using 2005. set nocount on DROP TABLE dbo.Emp...more >>

There is one particular table that when I save its design in SQL Server 2000 Enterprise Manager I get a message saying "Error validating check constraint <name of constraint>." This happens in the "pre-save validation process" and it does let me save it. However, I cannot figure out why it eve...more >>

Hi Freinds, SQL2005 using between instead of > < has any performance advantage or disadvantage? like : select col1, col2 from table where dregdate between '01/01/2006' and '12/31/2006' or select col1, col2 from table where dregdate >= '01/01/2006' and dregdate <= '12/31/2006' t...more >>

Hi Freinds, SQL2005 I was wondering if you have experience with the performance of Functions via SPs. I am doing a select over 30 differennt databases and usiing union all and returning a table set. Which one will have more better performace? FN or SP ? Thanks in advance, Pat ...more >>

Hi all, I am running into an interesting issue. The 2 queries below produce different results. Query 1 declare @dt datetime set @dt = '12/6/2006' select * from applications where fundeddate > @dt Returns 16 records Query 2 select * from applications where (fundeddate betwee...more >>

I have a one to many relation between two tables. This is basically an order table and a table containing the items in the order. Each of the items have a ship date. These items more often than not will have different ship dates. The ship date may also be null. I am being asked to create ...more >>

This may be a dumb question, but I need to ask anyway. Let's say I have a table with the following columns: ID and Cost. I want a select statement that generates three columns: ID, Cost, and PercentOfCost. If there are two rows in the table, and each row has a cost of $1, then the results...more >>

I have two separate queries which i guess I want to union together. However, what I really want to do is get a list of ids which appear in one dataset but not in the other. Here is a sample of my query: Select st.VendingMachineId, sl.ServiceTransactionType from ServiceTransaction st, Servic...more >>

I have a project that deals with building formulas....simple formulas. Something like: ((A * B) / C) + D However, since the formulas are dynamic (using a GUI) I thought about storing each character in a record. For example: SortOrder Value --------- ----- 1 ( 2 ...more >>

Hello everybody, I made a program that connects to a database (Microsoft SQL Server 2005) in C# , version Visual Studio 2003. Everything worked properly. Buth then... I tried to update the program to the Visual Studio 2005-version, and now I receive error messages (eg. "the user is not associ...more >>

hi, when i'm in sql 2005 management studio and i execute some sql command, the window shows 2 tabs, one for the results, and another for the messages associated with the command. how can i collect these messages using SMO? SQLDMO included a method called ExecuteWithResultsAndMessages, but th...more >>

Hello, I'm trying to create a recursive user defined functio that will list all child objects given a parent object. The problem is my model allows for children being their parents parent. I need some way to determine if a node has already been visited. ALTER FUNCTION [dbo].[Test] ( @inO...more >>

philipbennett25 wrote: > Hi, I hope someone has an idea how I might do this. > I have a table that shows goods recieved. Simplified it looks something > like this: > Part Date QTY Vendor > -------------------------------------------------------------...more >>

How do I specify the column's description in a CREATE TABLE statement? This is my CREATE TABLE statement so far: CREATE TABLE [tTest] ( [ID] [int] NULL , [Name] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ) ON [PRIMARY] GO For the ID field I want a description that says: 'This ...more >>

Can a generic SSIS package be created that allows emails to be sent based on some supplied values to the package? If it is possible how would you call the package with the parameters? ...more >>

Following is the sample code create table mytab1(PID smallint, PName varchar(30), L_No char(10), F_Name varchar(30), FL_No char(10), M_Name varchar(10) , M_No char(10)) go insert into mytab1 values (1,'James','1234ABC','Michael','1234XYZ','Tina','4567LMN') insert into mytab1 ...more >>

This is a trivial matter but I have seen in various sources when writing about indexes the authors use indices. Is there some standard for the use of the word indices instead of indexes and if so when should the word indices be used thanks for any help Sammy...more >>