tsql

Update table SQL query help

I have a database student(attribute - studentid). studentid is a varchar. Now I want to add 'P' at the end of all studentids. 12 -> 12P 234 -> 234P What will be the sql query for this? ...

How Can I determine is digit Even number?

hi guys Bit rusty on the old sql. Can you help? Given a number eg 1 or 2 or 4 I need to determine if it's even or odd number and do some calculation depending if even or odd . How do you detect that in sql (sql server 2000) thanks a lot ...

how can I add 2 digit together in SQL Server 2000

Hi, suppose I have a number 62. This is composed of 2 digits How can add 2 digit together divide by 10 and if result = something like 6.2 just take reminder declare @Number int,@Result int set @Number =62 if len(@Number) > 1 set @Result=????=--Add 6 and 2 =8 set @result=@result % 10 --Mod operator print @result -- the r...

Save Sql Recordset into the csv file with well formatted?

I need to dump my sql query result into the text file. i have created the following query, DECLARE @cmd VARCHAR(2048) SET @cmd = 'OSQL -Slocalhost ' + ' -UCRN370 -PCRN370' + ' -Q"SELECT TOP 5 GageId FROM EwQMS370..msgages"' + ' -oc:\authors.csv' EXEC master..xp_cmdshell @cmd, NO_OUTPUT The ab...

Performance tuning with SQL Server

Hi, I was trying to contact the author of a book I am reading on SQL Server query performance, but it seems the e-mail address provided in the book does not exis any more. So I decided to ask the community. I am pasting the messasge I had written below. Thanks in advance. ====== I have bought your book (SQL Server 2008 Query Performanc...

"Index was outside the bounds of the array" while Compare SQL schema by SQL compare tool

I got one problem from comparing database schema as i use Red gate SQL Compare 6 , after initialization of the compare databases error is coming as following "Index was outside the bounds of the array". Please provide your valuable comments to get resolve this issue. ...

SQL Server Stored Procedure - 'IF statement' vs 'Where criteria'

The question from quite a long time boiling in my head, that out of the following two stored procedures which one would perform better. Proc 1 CREATE PROCEDURE GetEmployeeDetails @EmployeeId uniqueidentifier, @IncludeDepartmentInfo bit AS BEGIN SELECT * FROM Employees WHERE Employees.EmployeeId = @EmployeeId IF (@Includ...

Infinite Looping Trigger

I have an after update/insert trigger on table x. In this trigger I need to check if a certain column has been updated which is simple enough doing a deleted/inserted table comparison. However, if a certain column has been changed to a certain value I need to update the inserted row in table x. Doing so obviously creates a loop. The ...

Count records by group with 0 number for records out of criteria

Hi, Need a rope ;)) Take a look on example: I have a table: CREATE TABLE [dbo].[test3]( [software] [varchar](50) NOT NULL, [result] [bit] NOT NULL, [computername] [varchar](50) NOT NULL ) With data: INSERT INTO test3 VALUES ('Adobe',1,'abc') INSERT INTO test3 VALUES ('Office',1,'abc') INSERT INTO test3 VALUES ('Adobe',0...

Problem with xml select in tsql - can't seem to orgenize multi nodes in the right order

Hello every one....this is my code, i am trying to make a simple 3 nodes select in XML and it is not working, i am getting the parent, the second nodes (all of them) and then the third nodes (all of them) (categories->category(all)->products(all) and not in the right order (categories->category->all products for that category) selec...

stored procedure error handling

i have this procedure for inserting rows in tables(sql server 2005) CREATE PROCEDURE ans_insert ( @q_desc varchar(2000), @sub_id int, @marks int, @ans1 varchar(1000), @ans varchar(1000), @userid varchar(15), @cr_date datetime ) AS BEGIN BEGIN TRY BEGIN TRANSACTION DECLARE @q_i...

What is the point of "ROLLBACK TRANSACTION named_transaction"?

I've read through MSDN on ROLLBACK TRANSACTION and nesting transactions. While I see the point of ROLLBACK TRANSACTION savepointname, I do not understand ROLLBACK TRANSACTION transactionname. It only works when transactionname is the outermost transaction ROLLBACK always rolls back the entire transaction "stack", except in the case ...

Trying to convert "yy/mm/dd hh:mm:ss" (stored as char) into datetime using computed column

Hi. This seems like it should be simple but it's driving me up the wall. I have two columns - 'tx_date' and 'time' stored each as char(10). (bad database design I know, but wasn't my design) From a query I can convert them into a datetime just fine - "...convert(datetime,tx_date,time,11)..." (so tx_date "09/11/27" and time "07:12:...

Intellisense and code formatting T-SQL for SQL Server 2008 in Visual Studio 2008?

I'm looking for a Visual Studio 2008 add-in / plug-in that can provide Intellisense and flexible code formatting for T-SQL for use with SQL Server 2008. It needs to work with Team System Data Edition ("data dude"). I am familiar with the Red Gate tools, but they do much more than I needwhich unfortunately puts the price on the high sid...

SQL query getting data

In SQL Server 2000: hello i have a table with the following structure: sku brand product_name inventory_count ------ ------ ------------- --------------- c001 honda honda car 1 3 t002 honda honda truck 1 6 c003 ford ford car 1 7 t004 ford ford truck 1 ...

Trying to add a column to a temp table fails

I have looked through this a number of times this morning and cannot find the issue. Its probably dead simple and I'll feel like an idiot when someone points it out. What would this SQL fail? -- Get CurrentRowCount DECLARE @MaxID INT SELECT @MaxID = ISNULL(MAX(WorkTypeID),0) FROM caWorkType ALTER TABLE #WorkType ADD _RowID INT NOT NUL...

SQL Server 2005 T-SQL Problem: Need help in omitting records

Good day! I need help in writing a query.. I have records in a table below.. The condition would be no records should be displayed if the succeeding records' new_state was repeated from the previous records(new_state) and if it is changed in the same date.. here record_id 1 has gone through the ff states: 0->1->2->1->3->4->3 in the sam...

SQL Server: How do you remove punctuation from a field?

Hey everyone, Any one know a good way to remove punctuation from a field in SQL Server? I'm thinking UPDATE tblMyTable SET FieldName = REPLACE(REPLACE(REPLACE(FieldName,',',''),'.',''),'''' ,'') but it seems a bit tedious when I intend on removing a large number of different characters for example: !@#$%^&*()<>:" Thanks in advance ...

Find usage of a function in SQL server

Is there a way to find a usage of a function in SQL server 2008? ...

The query has been canceled because the estimated cost of this query (1660) exceeds the configured threshold of 1500. Contact the system administrator.

"The query has been canceled because the estimated cost of this query (1660) exceeds the configured threshold of 1500. Contact the system administrator." I am getting error as above on live while running one of the stored procedure threads where parameter contain XML variable. I have checked the configuration value of QUERY_GOVERNOR_CO...