huangzhi

[code] create table #test1 (ID int, name1 varchar(255)) insert #test1 values (1, 'value1') create table #test2 (ID int, name2 varchar(255)) insert #test2 values (1, 'value2') create table #test3 (ID int, name3 varchar(255)) insert #test3 values (3, 'value3_01') insert #test3 values (4, 'value3_02') select #test1.ID, name1, name2, name3 from #test1 inner …

Try this code below: [code] select * from #tmpX X inner join (select A, B from #tmpX group by A, B having count(*) > 1) Y on X.A = Y.A and X.B = Y.B [/code]

change your command into: [code] select * from (Select table_name from information_schema.tables where table_name= @table_name ) AliasName [/code] change your second command into: [code] declare @table_name varchar(255) select @table_name = 'sysdatabases' exec ('select * from ' + @table_name) [/code]

[Code] create table #tmp (Code varchar(255), Price money, Date smalldatetime) insert #tmp (Code, Price, Date) select 'ProductA', 12.00, '12/10/2002' union all select 'ProductA', 12.50, '01/15/2005' union all select 'ProductA', 12.01, '01/02/2008' union all select 'ProductB', 15.12, '12/01/2005' union all select 'ProductB', 16.00, '01/09/2008' union all select 'ProductB', 16.24, '01/15/2008' declare …

1. All lines are execute. Not just 1 line below the. If you want to exit, use severity more than 10 (> 10). 2. No comment 3. I am not use TRY CATCH. I usually use RAISERROR alone with severity 16. So the error will be send to my application …

May be this query can solve your problem: [CODE] DECLARE @person xml, @idoc int, @Local varchar(255), @XML varchar(255) set @person = ' <ROOT> <people> <person.1> <fname>brian</fname> <lname>smith</lname> </person.1> <person.11> <fname>joe</fname> <lname>carey</lname> </person.11> </people> </ROOT>' EXEC sp_xml_preparedocument @idoc OUTPUT, @person select @idoc declare cs_Parent cursor forward_only for select LocalName FROM OPENXML(@idoc, …

Use DISTINCT to eliminate double row in Pf.RecruiterID, Pf.ProjectID. Then count the ProjectID The query may be looks like below: [CODE] declare @StartDate smalldatetime, @EndDate smalldatetime select @StartDate = '2010/01/01', @EndDate = '2010/12/31' select RecruiterID, count(*) from (select distinct Pf.RecruiterID, Pf.ProjectID from tblProfiles Pf inner join tblProjects Pr on Pf.ProjectID …

Give some example data and the result you want.

It is better if you give some data and the result that you want.

Please give some data for an example. Or you can read help about : 1. SET IDENTITY_INSERT for inserting ID to another table. 2. IDENT_CURRENT('table_name') to return last ID. 3. DBCC CHECKIDENT to return last ID and corrects the identity value

I think there are data double in table Receipts. Please give some data for example.

I think you cannot use simple sql command to get result like that. You can use cursor to concatenate the sales quantity like below: [CODE=ms sql] declare @Day varchar(255), @Sales int create table #tmpSales ([Day] varchar(255), SalesQuantity int) insert #tmpSales select 'Monday', 20 union all select 'Monday', 302 union all …

Can u give some data and the result you want? or you can see [url]http://www.daniweb.com/forums/thread114466.html[/url].

May be this SQL can solve this problem: [code] create table #tmpT (ABC char(1)) insert #tmpT select 'A' union all select 'B' union all select 'C' union all select 'D' union all select 'E' union all select 'F' union all select 'G' union all select 'H' union all select 'I' …

maybe you can use CASE ... WHEN ... END command like below: [code] select cast(case when exists (SELECT * FROM UserList WHERE UserName = 'Tester') then 1 else 0 end as bit) [/code]

Hope this code can help you. 1. Execute your first SP in query analyzer to show the columns list. 2. Create temporary table and the field must the same as first SP columns [code] create table #tmpTemp ( Field1 varchar(255), Field2 varchar(255)) [/code] 3. Insert the columns from first SP …

Make it subquery like below: [Code] select [Year], [Month], sum([Benefits Rating]) as [Benefits Rating], sum([Facilities Rating]) as [Facilities Rating] from (...... your origin query ......) X group by [Year], [Month] [/Code]

Try this query below: [Code] SELECT 'Testing' as [Title] , [MONEY1] , [MONEY2] from (SELECT sum(Total) AS [MONEY1] FROM((SELECT SUM(T1.Amount+T2.Amount) AS Total FROM TABLE1 T1 INNER TABLE2 T2 ON T1.ID = T2.ID) UNION ALL (SELECT SUM(T3.Amount+T4.Amount) AS Total FROM TABLE3 T3 INNER TABLE4 T4 ON T3.ID = T4.ID) ) DERIVETBL) …

You cannot use just select command. You must use cursor to list the time list. Try and modify code below: [Code] set nocount on declare @CounterDate smalldatetime, @MaxDate smalldatetime create table #tmpAct ( ActDate smalldatetime, Act varchar(255) ) create table #tmpChart ( StartTime smalldatetime ) insert #tmpAct (ActDate, Act) select …

You can use table master.dbo.sysprocesses. This system table contain connected users.

Try this command: [code] select T1.idx, T2.idy, T2.u from Table1 T1 right join Table2 T2 on T1.idy = T2.Idy and T1.idx = 'one' [/code]

[code] select sum(Total) from ( select T1.Amount+T2.Amount as Total From TABLE1 T1 INNER TABLE2 T2 ON T1.ID = T2.ID union all select Amount as Total FROM TABLE3 ) X [/code]

Try this code below: [code] select 1 as Col1, 'MT' as Col2, 30 as Col3, 'UK' as Col4, 0 as Col5, 70 as Col6, 'FR' as Col7 into #tmpX union all select 2 as Col1, 'SM' as Col2, 30 as Col3, 'IL' as Col4, 0 as Col5, 0 as Col6, …

Try this code below: [code] select top 1 * from (select top 6 * from #tmpSalary order by Salary) X order by Salary desc [/code]

Try code below: [code] declare @Ask varchar(255) select @Ask = '100.100.2225.104' create table #tmpAddr ( Addr varchar(255), Inc int) insert #tmpAddr select '100.100.2225.100', 5 insert #tmpAddr select '100.100.2225.103', 5 insert #tmpAddr select '100.100.2225.110', 5 select left(Addr, len(Addr) - charindex('.', reverse(Addr))), right(Addr, charindex('.', reverse(Addr)) - 1) from #tmpAddr where @Ask between …

Change your Select check to: charindex(' ' + uzip + ' ',' ' + 'S10 S11 S7 S17 S8 S3 S30' + ' ') > 0 This code will check 'S1' <> 'S10'

Try this one: [code] select completed from table order by case when completed is null then 0 else 1 end, completed desc [/code]

[Code] select * from tb_TableName where AColumn like 'user1%' [/Code]

You can combine your 2 query with 'UNION ALL' or 'UNION' like below: [code] SELECT * FROM TBL_Modules WHERE identifier IN ('1','2','3','4','5','6') ORDER BY Identifier union all SELECT m.* FROM TBL_Modules m,TBL_ComponentList c WHERE m.Identifier=c.CompIdentifierID AND c.CreatedBy = '2418' ORDER BY m.Identifier [/code]

[Code] create table #tmp (Code varchar(255), Price money, Date smalldatetime) insert #tmp (Code, Price, Date) select 'ProductA', 12.00, '12/10/2002' union all select 'ProductA', 12.50, '01/15/2005' union all select 'ProductA', 12.01, '01/02/2008' union all select 'ProductB', 15.12, '12/01/2005' union all select 'ProductB', 16.00, '01/09/2008' union all select 'ProductB', 16.24, '01/15/2008' select …

Try this code. [code] SELECT DISTINCT COALESCE (s3.ADM_CODE, s1.ADM_CODE) AS Expr1, COALESCE (s3.FULLNAME, COALESCE (COALESCE (CASE s2.shortform WHEN '' THEN NULL ELSE s2.shortform END, s2.FULLNAMEND), COALESCE (CASE s1.shortform WHEN '' THEN NULL ELSE s1.shortform END, s1.FULLNAMEND))) AS name FROM worldcities_pro_states AS s1 LEFT OUTER JOIN worldcities_pro_states AS s2 ON s2.CC1 …

Try this code [code] select c.name, isNull(doc_A.Doc_A, 0) as Doc_A, isNull(doc_B.Doc_B, 0) as Doc_B, isNull(doc_C.Doc_C, 0) as Doc_C, isNull(doc_D.Doc_D, 0) as Doc_D from tUsers u inner join tContacts c on u.contact_id = c.id left join (select user_id, count(*) as Doc_A from tDocumentsA group by user_id) Doc_A on c.id = Doc_A.user_id …

You cannot increase this limit. You must change your program. See [url]http://www.daniweb.com/forums/thread114466.html[/url]. Hope this link can give you some idea.

[code] select top 1 * from (select * from (select * from table where columname > '2') X where columname > '4') Y where columname > '6' order by columname desc [/code]

[code] update ABC set XYZ = left(XYZ, 8) + 'A' + substring(XYZ, 9, 1000) where XYZ like '%P' and XYZ not like '%A%' [/code]

I think you use Transaction. So you must start Distributed Transaction Coordinator on both server.

I think you can create identity columns. [CODE] create table #tmpTemp (ID int identity(1,1), Code varchar(255), Name varchar(255)) insert #tmpTemp (Code, Name) select 'A', 'AA' --> replace with your view union all select 'B', 'BB' select * from #tmpTemp drop table #tmpTemp [/CODE]

The correct code is: [icode]select * from mail3 where email like '%+%' or email like '%*%'[/icode]

Try this code in Query Analyser. It enough for small data. If you want to use for big data, modify the code. [code=MSSQL] set nocount on create table #tmpName (ID int, [Name] varchar(255)) create table #tmpRelation (ID int, ID_Parent int) insert #tmpName (ID, [Name]) select 1, 'Frank' insert #tmpName (ID, …

Try this code select 1 as EmpID, 'John' as EmpName, '2007/01/01' as DateOfBirth into #tmpData union all select 2 as EmpID, 'Anne' as EmpName, '2007/01/05' as DateOfBirth union all select 3 as EmpID, 'Tony' as EmpName, '2007/01/08' as DateOfBirth union all select 4 as EmpID, 'Erin' as EmpName, '2007/02/28' as …

you need to create table tb_IPList (IP varchar(20)) and code (in ASP) below: <% option explicit Dim Conn as object Dim RS as object set Conn = createobject("ADODB.Connection") Conn.Mode = 3 Conn.open "DRIVER={SQL Server};Server=YourServer;Database=YourDB" set RS = Conn.execute ("select 1 from tb_IPList where IP = '" + Request.ServerVariables("REMOTE_ADDR")) + "'" …