tsql

help with recursive tsql query

Using Sql-Server 2005. I have Users table with 3 columns. userID, parentUserID and userEmail. Top level users have null in parentUserID, child users have some userID. I want to write a query that counts children up to lowest level of certain userID. Select user with all his children, their children .... so on. I just need count of tho...

INSERT INTO results_table EXEC sproc from within a scheduled job

I'm trying to setup a scheduled job that with one step that would insert the results from a sproc into a table. INSERT INTO results_table EXEC sproc The job executes and reports a success. Yet nothing gets inserted into a table. When I execute the same script from the SSMS the results are inserted. What might cause the problem? ** E...

Parsing an Address with T-SQL

Hello, I am trying to figure out how to parse an address using T-SQL and I suck at T-SQL. My challenge is this, I have a table called Locations defined as follows: - City [varchar(100)] - State [char(2)] - PostalCode [char(5)] My UI has a text box in which a user can enter an address in. This address could be in the form of essentia...

Natural sort for SQL Server?

I have a column that is typically only numbers (sometimes it's letters, but that's not important). How can I make it natural sort? Currently sorts like this: {1,10,11,12,2,3,4,5,6,7,8,9} I want it to sort like this: {1,2,3,4,5,6,7,8,9,10,11,12} ...

How to create ALTER scripts instead of CREATE scripts using SMO (Server Management Object)

I am using the Microsoft.SqlServer.Management.Smo classes to script out SQL scripts for stored procedures, tables, views etc. I am doing this for putting them in source control. Instead of the CREATE scripts for stored procedures, how can I get ALTER scripts for them? Is there an setting / option in the ScriptingOptions' properties? ...

BEST approach to share messages between data tier and application tier

Hi there I was looking for any suggestion or a good approach to handle messages between the data & application tier, what i mean with this is when using store procedures or even using direct SQL statements in the application, there should be a way the data tier notifies upper layers about statement/operation results in at least an organ...

SQL Search column for each variable in CSV string

I have a variable passed to stored procedure Ex: @keywords = 'val1, val3, val5' And i'm trying to see if column named Title contain any of them in it Ex: Title1 - 'Hello val1' Title2 - 'Hello val3' Title3 - 'Hello val1, val3' Title4 - 'Hello' SO my results should return values Title ------ Hello val1 Hello val3 ...

SQL Scalar UDF to get number of days in Year

I writing code to determine how many days in a year. I am trying to keep it really simple. I found code that I think is very clean to determine a leap year. I am passing the inputted date using DATEPART(Y,@Year) to the leap year program and some how am not getting the correct results so I has to be in my SQL code to process the input da...

How to handle Gmail addresses?

Background Gmail allows '.'s and +filters allowing for an infinite number of email addresses all pointing to the same gmail account. i.e. the following all point to the same gmail account: [email protected] [email protected] [email protected] [email protected] Problem We have a table on our production environment that hold all re...

Performance Hit using CAST() in TSQL (SQL Server)?

We have a SQL generator that emits SQL conditional statements generically for specified fields (which for the sake of discussion: we will label as myField). So, if myField is of type NVARCHAR, we can do a comparison of said field against a string like so: myField = 'foo'. However, this does not work for fields of type NTEXT. Thus, w...

Joins in SQL for retriving data from two tables

There are two tables A and B. You are retreiving data from both tables where all rows from B table and only matching rows from A table should be displayed. Which of the following types of joins will you apply between A and B tables? - Inner join - Left outer join - Right outer join - Self join ...

Conversion failed when converting the varchar value 'Blue color' to data type int.

2 Hi! I am trying to create stored procedure that gone return varchar value, and that value I need to display in textbox. This is the code for stored procedure: Create PROCEDURE Status @id_doc int, @Name varchar(50) OUTPUT AS select @Name =items.name from Doc,Items where @id_doc=IDN and doc.IDN=Items.ID return @Name This is t...

Duplicate Values in Identity Column

I've got a table which has a column named id which is of type Identity. But this column contains duplicate values 1..8 and then again 1..10 How in the world is this possible? ...

SQL How do i update like this?

I'm trying to update all SQL rows in the [Temp_LTGData] table setting the [CORP_REG_NO] value to the value in another row in the same table where the [CUSTOMER_NUMBER] matches. Ultimately I need to do this with quite a few columns, does anyone know if this can be done? I can't seem to use the LTGSource alias like in a select query :( ...

Calling a user defined scalar function from a sql program

I am still really new to SQL functions. I am trying to figure out how to use the in a SQL program properly. I am wanting to test scalar UDF's that I have created to see that the return the data correctly and can return a large quantity of data in order. I am not sure what is wrong with my syntax in the SQL to use the function as this is...

How to select most populated record?

I have the unfortunate luck of having to deal with a db that contains duplicates of particular records, I am looking for a quick way to say "get the most populated record and update the duplicates to match it". From there I can select distinct records and get a useful set of records. Any ideas? It's mainly names and addresses if that ...

SQL Server Update trigger for bulk updates

Below is a SQL Server 2005 update trigger. For every update on the email_subscriberList table where the isActive flag changes we insert an audit record into the email_Events table. This works fine for single updates but for bulk updates only the last updated row is recorded. How do I convert the below code to perform an insert for ever...

How can I get the greatest string value that can be converted to an int from SQL server?

I have a table in MS SQL Server 2008 with a varchar column called WONO. For most records, the value of this field is a padded-on-the-left string representation of an integer, such as: ' 384564' However in some cases there is a non-integer value in the field. I want to select the record with the greatest integer in the WONO column, i...

SQL - Grab Detail Rows as Columns in Join.

This is my query thus far: select C.ACCOUNTNO,C.CONTACT,KEY1,KEY4 from contact1 C left join CONTSUPP CS on C.accountno=CS.accountno where C.KEY1!='00PRSP' AND (C.U_KEY2='2009 FALL' OR C.U_KEY2='2010 SPRING' OR C.U_KEY2='2010 J TERM' OR C.U_KEY2='2010 SUMMER') Now, I have another table (CONTSUPP) which contains multipl...

Force SQL to Add Rather than Concatenate Two Columns?

I have column OFF_SAT_COMP.LINKACCT and OFF_SAT_COMP.COUNTRY. I am trying to add the values of these two columns in a given row together, but when I do so as follows: (OFF_SAT_COMP.LINKACCT + OFF_SAT_COMP.COUNTRY) It is concatenating rather than returning the sum of the two column values. e.g. It is returning 500300 where I want 800. ...