given 2 tables T1 and T2, what is the SQL statement to add a new field for each record in T2 when there are common records in both T1 and T2? For example, T1 is: ID Name 1001 N1 1002 N2 1003 N3 and T2 is ID Name 1001 N1 1003 N3 1005 N5 After executing ...more >>

Hi... i have: SELECT DATEDIFF(yyyy, birthdate, GETDATE()) AS Age, case when DATEDIFF(yyyy, birthdate, GETDATE())<=17 then 'Teen' when DATEDIFF(yyyy, birthdate, GETDATE())>17 and DATEDIFF(yyyy, birthdate, GETDATE())<=20 then 'Young' else 'Other' end as ...more >>

Hi, I am currently running a large database (1.6 million inserts a day, a query on that once a second with heaps of aggregate functions) on a single relatively powerful server, although it is not powerful enough! As cost is a very large factor I am thinking about clustering. Clustering to sa...more >>

Hello, We are migration for Oracle to Sql-Server but having some problems with the triggers. This trigger should fire for each row when a delete happens, as, from what i've found on the net in SqlServer each trigger is automatically FOR EACH ROW (Oracle Syntax). But when i'm doing a 'delete f...more >>

Hi, I am working on Stored Procedure in MSSQL Server. How can I navigate throught a table row by row without using cursor within stored procedure? Uddhab ...more >>

Hi All, I came up with the following sql to do the paging.... SELECT TOP 10 * FROM ORDERS WHERE ORDERID NOT IN (SELECT TOP 20, ORDERID FROM ORDERS) --|Where 10 is my records size per page. but soon I realise that it is not the most efficient way of doing it ... any better ways ? ...more >>

Hello, I have this problem: 2 tables tblProductCategories: CategoryID, CategoryName tblProducts: ProductID, CategoryID, ProductName, ProductRanking I want to select using one querry the top 5 products for each category of products ordered descending after the Ranking field. How can I do th...more >>

I have used the BEGIN TRAN statement from the Query Analyser many times when running SQL statements that I would like to roll back if I make a mistake. Can this be done programmatically, i.e, can I call BEGIN TRAN and then make many UPDATE calls and then if my program produces an error I can th...more >>

I am using SQL Server 2000 SELECT (RTRIM(contacts.leave_date) + str(contacts.id)) AS DateId FROM contacts ORDER BY DateId When the results come back, its not sorted by date, but it order by Alphabetically. Example: July 7, 2003 120000 July 8, 2003 131000 June 1, 2003 150000 I w...more >>

Hi, I have a table with a column 'foo' of type varchar containing lots of values. Most of the values can be cast as int, and I need to get the maximum, i.e. SELECT max(cast(foo as int)) ... However, there are some values of foo which can't be cast as int, and I want these to be ignored,...more >>

Hi, Please help me if it is possible to put data received from existing stored procedure into the cursor. I can not redefine that stored procedure to use cursor output variable. Thanks in advance, Aleksander Kaczmarek ...more >>

Given a datetime column in a resultset, how do I calculate the Beginning of the Week and End of Week dates using a custom week of Saturday to Friday? Thanks in advance. Chris example: DateCol1 BeginWeek EndWeek -------- --------- ------- 7/1/03 6/28/03 7/4/03 7/8/0...more >>

I am not sure if this is the right forum for this question but hopefully someone can help me. I need to BCP a large amount of data into sql server. I have txt files working ok. I need to know if I can BCP excel files. I don't find any mention of that in the BOL. Thanks for any tips. Tomm...more >>

Hi, I need to import a few tables in SQL server database (7.0) , from another SQL server databse (2000). The original table has autonumber as the primary key and another table has a foreign key relationship to this table on the autonumber key. Now, when i import the data to the sql ser...more >>

In the following query, (Value1 - 5) as Value2 is not admissible. SQL does not acknowledge Value1 as a column (it displays that error). The SELECT statement corresponding to Value1 is a very complicated one and I do not want SQL to calculate Value2 as (SELECT COUNT(...) FROM Table2...) -...more >>

Is there any equivalence to ROWID feature that is in ORACLe in SQLSERVER. Also any equivalence to DECODE statement. Thanks...more >>

I have a simple query to pull a large comments field. the query succeeds in drawing in all the data when the query is bound to a form or report, but when I run it in query analyzer or try to output the query to excel, i only get the first 256 chars of the field. what shall i do? ...more >>

Is there any way, shape or form that the following stored procdure can be called successfully? Basically I am trying to passing in a WHERE clause that contains some full-text predicates (CONTAINS, etc...). This WHERE clause has to be a variable passed in. The 'public' role has NO rights to th...more >>

I have a to_date function, which formats the date to this: 'MM/DD/YYYY HH24:MI:SS' In sql server, is there another way of accomplishing the same thing - besides this? select convert(char(11), getdate(), 101)+ convert(char(8), getdate(), 108) Thank you. Alex Ivascu ...more >>

Folks, Here is a classic: I have a table with a hierarchical listing (be it directory tree, process tree, whatever) with an ID and a PID field, like so: ID PID Name 1 <null> WinNt 2 <null> Programs 3 1 System32 4 2 MSOffice 5 3 Drivers Of course, I want the data to...more >>

If I have a timestamp column in my product table, how can I select only updated products from the last time? Should I campare: SELECT * FROM products WHERE Timestamp>oldTimeStamp oldTimestamp I remember from the last SELECT from the last week for example. Why use timestamp instead of da...more >>

In using the TSQL Debugger to debug a stored procedure, I am running into a problem specifying a value for a datetime parameter when the datetime value has any nonzero seconds. The debugger accepts 2003-07-29 11:33:00 but will not accept 2003-07-29 11:33:01. When placing the latter value...more >>

if used like this: select convert(bigint, convert(varbinary, newid())) Thanks in advance. Alex Ivascu ...more >>

I am drawing a complete blank, please help the weak of mind.... I want to write one select statement. I want to select the bill to key = column if it is not null, OR the customer key column if it is not null = for a record where the account number is X and the routing number is Y. Record wo...more >>

Hi all, I have this SQL statement: select count(*) from tblOrders where id='xyz' and orderDate='some date' tblOrders has about a million records and an index on id and orderDate When I execute this it is FAST, no probem there, but now I need a specific column, so count(*) won't cut it, s...more >>

Hi again. I'm trying to use a function to return the value that the procedure within the function will give back. Here's the code for the procedure (all it does it increments the value in the dual table by one, and returns the new value): create procedure sched_seq_get (@vi_seq_name nvarchar...more >>

Hi here is some sample data order_number Line Number Price 8908ABB23200 1 4 8908ABB23201 1 5 8908ABB23201 2 6 8908ABB23202 1 7 8908ABB23202 1 8 8908ABB23202 2 9 order_number Line Number Price 8908ABB23200 1 5 8908ABB23201 2 6 8908ABB23202 1 8 8908ABB23202 2 9 I want to create a ...more >>

I have a SP's that monitors growth and I want it to send me a "net send" when a certain criteria is met. I have no problem sending a regular net send, but need to pass the dbname as a variable. Here's what I have so far.. if @maxsize < (@size + @growth) begin EXEC master..xp_cmdshell 'net...more >>

Hi! I have a SQL code that takes 5-6 minutes to run in QA but when I create a view using the same code and try to run this view i.e. select * from myview, its takes forever??? please help!!! i am using sql server 7.0 *** Sent via Developersdex http://www.developersdex.com *** Do...more >>

I am trying to write a stored proc that uses a dynamic value to set the <datepart> parameter of the datename function datename(<datepart>,<date>) here is the test query I am using against northwind: select Shipcountry, datename(weekday,orderdate) as omonth, sum(extendedprice) totalEP from ...more >>

Hi this maybe very elementary for some people. But I have this sql set nocount on declare @order_items int declare @counter int declare @order_num char(10) Select @order_items = 0 Select @counter = 0 select @order_items = Count(*) from order_detail_org While @counter < @order_ite...more >>

There is a starange problem happening in our Application. We have big Transaction and as a part of that transaction we are inserting multiple rows in the same table. When we run the first instance of this transaction it runs fine. Then we start the second instant of the same transaction and the ...more >>

Hi all, I want to be able to retrieve and rename all the primary keys in a db for all tables that start with ad_ or wk_ I know that when you add a constraint that is a foreign key you can set the name. Trouble is, when you add a foriegn key constraint it's not possible to add a name. ...more >>

I am desinging a table that will contain information on employees. One of the things I would like to store are word documents or excel documents. How do I store the documents in the database? ...more >>

I want to get the error text from an insert query that is run within a stored procedure to save in an insert reject table for later analysis. I know how to check @@error and get the error number and even look up the generic text in sysmessages, but I don't know how to get the full message...more >>

I am new to this Group, and have little Sql experience. As an Access guy I have long used Access Crosstab queries to wow and amaze customers. As you all know (as I have just discovered) Sql 2000 doesn't know anything about Transform and Pivot. Soo... How can I create the equivalent of an Acc...more >>

Hi I want to know how GUID is globally unique identifier? How can no other computer in the world will generate a duplicate of that GUID value? Can any one explain to me about this. Regards Balaji ...more >>

Hi, I want to combine two records into one. Let's say I have 3 fields in the record -- ID, Name, Description. My current SP returns two records with these 3 fields. What I'd like to get is ID1, Name1, Description1, ID2, Name2, Description2 in one record. Thanks Sam ...more >>

Is it possible to return data in a random order from a query each time the query is run. If so, how is this done? Many thanks, Brian Martin...more >>

With the following query: create table #t ( customerid varchar(50), calltime datetime ) insert into #t (customerid,calltime) values ('123', '12:01 PM') insert into #t (customerid,calltime) values ('123', '9:30 PM') insert into #t (customerid,calltime) values ('123', '8:57 PM') ins...more >>

I use the Database solution in Visio to design databases. It can generate DDL scripts for creating databases. How can I use those scripts to generate the database in SQL Server 2000? Thanks....more >>

TheTable = TheDate datetime TheHour int TheTemp int This tracks the hourly tempature I want to find the Max temp and what day it occured on for each year and month Select year(TheDate)AS [TheYear], Month(TheDate)AS [TheMonth], Max(TheTemp) from TheTable Group by year(TheDat...more >>

Hi all, I have a query as follows: update stage.FinanceVehicleSummary set vin = (select VIN from stage.ChasisLookup where stage.chasislookup.chasis = stage.FinanceVehicleSummary.ChasisNo and stage.chasislookup.sr_registration_number = stage.FinanceVehicleSummary.srregno) and i ...more >>

declare @obj int ,@err int ,@body varchar(100) set @body = 'Testing....TSQL and Cdonts...' declare @obj int ,@err int exec sp_oacreate 'cdonts.newmail' ,@obj output print @obj exec @err = sp_oasetproperty @obj ,'from' ,'jpmgt@NoSpamsbcglobal.net' exec @err = sp_oasetproperty @obj...more >>

Most of the installations of sp3 are smooth, but I got error messages couple times like this: "Error runing script: sp1_uni_sql(1)" Can someone help to solve this problem? --Saiyou...more >>

Hello All, If you can shed some light on this, so any help is appreciated. I am trying to execute the "USE" @databasename within a stored procedure. But when I run the SP, it doesn't change database to the specified database. Am I doing something wrong? Is this possible? If not, is there ...more >>

Hiya, this may be simple if you know hex calculations but its got me very confused!! In my table I have a field called unitNumber. I want to return the rows where this field has a 6 in the 5th position (e.g. 60001, 60586, 62323 etc) I have tried select * from table where (unitNum...more >>

or better still, can I query the excel pivot table from sql ? ALF...more >>

When I employ the GetDate function, in inserts the date with a timestamp. Is there any way to have it simply input only the date? TIA...more >>

Does anybody know how to determine when a stored procedure was last modified? The crdate or refdate in the system table sysobjects only shows when the stored procedure was created. ...more >>