I have a single windows shell command I'd like to run (via EXEC master..xp_cmdshell) once for each row in a table. I'm using information from various fields to build the command output. I'm relativity new to writing T-SQL programs (as opposed to individual queries) and can't quite get my head around the syntax for this, or if it's even...
I have a table (T1) in t-sql with a column (C1) that contains almost 30,000 rows of data. Each column contains values like MSA123, MSA245, MSA299, etc. I need to run an update script so the MSA part of the string changes to CMA. How can I do this? ...
Can I call a remote webservice from a Stored Procedure and use the values that areretuned? ...
I am working on some schema changes to an existing database. I backed up the database to get a dev copy, and have made my changes. I will be creating a single roll script to migrate the changes on the production machine in a single transaction. Is there a best practice for creating a rollback script encase a deployment issue arises? B...
Quick synopsis: The guys on my team have been working on a production database (sql server 2005). We've added various things such as constraints, added triggers, etc. Now we've found that someone or something has been rolling back our changes at various times. Problem is we all share a common admin login. (dumb, yeah I know, we're fixi...
The Table - Query has 2 columns (functionId, depFunctionId) I want all values that are either in functionid or in depfunctionid I am using this: select distinct depfunctionid from Query union select distinct functionid from Query How to do it better? ...
What is the difference between left join and left outer join? ...
Is there any good way convert a binary (base 2) strings to integers in T-SQL (if not I guess I can write a function...) '0110' => 6 etc. ...
Hi i executed the following stored procedure in .net web application. Then run the application. I got this error " Divide By zero error " Stored procedure: CREATE procedure Details(@Stringtext varchar(8000),@SearchStringtext varchar(100)) as begin SELECT ({fn LENGTH(@Stringtext)}- {fn LENGTH({fn REPLACE(@Stringtext, @SearchStringtext,...
This is the SP... USE [EBDB] GO /****** Object: StoredProcedure [dbo].[delete_treatment_category] Script Date: 01/02/2009 15:18:12 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO /* RETURNS 0 FOR SUCESS 1 FOR NO DELETE AS HAS ITEMS 2 FOR DELETE ERROR */ ALTER PROCEDURE [dbo].[delete_treatment_category] ( ...
My application needs to execute a fairly complicated series of queries against a database. Normally I would just dump everything into a stored procedure and execute it that way. But I do not have access to the database I'm trying to access so I can't create a stored procedure. Is there a better way of doing this instead of hitting t...
Let's say I have a table tbl with columns id and title. I need to change all values of title column: from 'a-1' to 'a1', from 'a.1' to 'a1', from 'b-1' to 'b1', from 'b.1' to 'b1'. Right now, I'm performing two UPDATE statements: UPDATE tbl SET title='a1' WHERE title IN ('a-1', 'a.1') UPDATE tbl SET title='b1' WHERE title IN ('b-1',...
The following T/SQL:- create table #temp(foo varchar(10)) insert into #temp select '' insert into #temp select 'abc' insert into #temp select ' ' select len(foo) from #temp where foo = ' ' drop table #temp Returns two 0's, despite the fact the criteria means it should return nothing. Additionally it's returning the length of the t...
I have a select statement with calculated columns and I would like to use the value of one calculated column in another. Is this possible? Here is a contrived example to show what I am trying to do. SELECT [calcval1] = CASE Statement, [calcval2] = [calcval1] * .25 ...
First check out this code. I seems like it should work for me, but it doesn't! (surprise!) Anyway, this is what I tried first: SELECT Status as status, Address as ip, PCName as pc_name, (Numbers.Phone = 'CPU/' + PCName) as cpu_contact, (Numbers.Phone = 'PC/' + PCName) as pc_contact, (Numbers.Phone = 'LOGIN/' + PCName) as login_contac...
I'm looking at creating a basic ORM (purely for fun), and was wondering, is there a way to return the list of tables in a database and also the fields for every table? Using this, I want to be able to loop through the result set (in C#) and then say for each table in the result set, do this (e.g. use reflection to make a class that will...
While developing a new query at work I wrote it and profiled it in SQL Query Analyzer. The query was performing really good without any table scans but when I encapsulated it within a stored procedure the performance was horrible. When I looked at the execution plan I could see that SQL Server picked a different plan that used a table sc...
Is there any FREE "build-in" way to capitalize the "keywords" in the SQL 2005/2008 management studio when you write a sql query? i.e truncate table x should be automatically changed to: TRUNCATE TABLE x ...
I have this scenario where I need data integrity in the physical database. For example, I have a variable of @email_address VARCHAR(200) and I want to check if the value of @email_address is of email format. Anyone has any idea how to check format in T-SQL? Many thanks! ...
Hi, Does anyone know why, using SQLServer 2005 SELECT CONVERT(DECIMAL(30,15),146804871.212533)/CONVERT(DECIMAL (38,9),12499999.9999) gives me 11.74438969709659, but when I increase the decimal places on the denominator to 15, I get a less accurate answer: SELECT CONVERT(DECIMAL(30,15),146804871.212533)/CONVERT(DECIMAL (38,15),124999...