Hello, I have a table which has 3 columns, [dispatcher], [dueDate],[Delivered] I am trying to write a query which tells me which dispatchers delivered their package on time So I have, select dispatcher, count(datedif(d,dueDate, Delivered)) as [onTime], count(datedif(d,dueDate, Delivered...more >>

I need to write a query which should calculate the Month and year part of the Date that is stored in the database. Please note that the query also has a group by column. Here Date is stored in table c. Please note that the query works fine and able able to retrieve the Month part. What am int...more >>

Hi, During a concurrency test of an Access ADP application, two users were running the same report simulataneously. This resulted in a SQL Server TSQL proc failing as a deadlock victim with error -2147467259. The proc in question consists of three update statements like this one: U...more >>

I am currently developing an asp.net application that uses sql server 2000. In addition, I am also using the sql application data block provide off of Microsoft's website. My connection string to the database is stored in the Web.config file so that it can be accessed by all classes. The ...more >>

i have CAST([PhoneWork] AS bigint) in my select statement. if the varchar has non-integer characters, how can i test the cast because its throwing errors right now ...more >>

Is there a way using a SQL Statement to see if a stored procedure exists in SQL Server? ...more >>

Hello Lets say i have 2 tables t1 and t2 and they are related (inner join) by ID and t1_ID respectivley, 1 to many respectivley.. Now, lets sey they are records in column ID that aren't related to any t1_ID column. How do i write the SQL syntax to show those that are related to nothing. ...more >>

Hi, I'm trying to create a simple trigger in sequel server. However i don't know how to specify a field value in the row which has been updated. Hope this makes some sense ... CREATE TRIGGER My_trigger ON table1 FOR UPDATE AS IF UPDATE(field1) UPDATE table2 SET field1 = DATEADD(Hour,...more >>

Hello I have this query: SELECT ClientCred.ClientID, SUM(ClientCred.Balance) AS TOTBalance FROM ClientCred INNER JOIN Clients ON ClientCred.ClientID = Clients.ClientID WHERE (Clients.Active = '-1') AND (TOTBalance > 0) GROUP BY ClientCred.ClientID In t...more >>

I'm inserting into a temp table with the intent of ordering the data so that I can select a "page" of it to send to the client. However, the data is not inserted in the order that is specified in the query. The data is not in the table in the order that the ORDER BY clause specifies. I do thi...more >>

Can I not use an EXEC command from with in a UDF? I am looking through Books online, but can't find anything that says I can't. In my UDF I have Exec('Select fields fromTABLE Where this=that') I use this in stored procs alot when the table name is a variable. When trying to do it from...more >>

hi guys, how do you transpose a table? I don't need the actual sql codes, but I want to know Algorithm and steps and how you would do it. I went to an interview and tech guy asked me this question and I don't know how to answer him, that really bothers me. ...more >>

hey, this is my procedure but is doesn't work. who can tell me what's wrong? CREATE PROCEDURE new_procedure @test_var int AS BEGIN IF @test_var = NULL BEGIN INSERT INTO table (isnull) VALUES (0) END ELSE BEGIN INSERT INTO table (isnull) VALUES (1) EN...more >>

I need to change the criteria of the select so that if @thstype is 1 then where a =b else a<> b. This is in a stored proc... given declare @thsType as INT set @thsType = 1 select * from t1,t2,t2,.... where u.unvid case @thsType When 1 then = else <> end @thsUnvID and u.unvtype = @ths...more >>

Hi All I need genrate documentation of SqlServer DB and I heard about programs which let you genrate documentation including yours comments of fields, tables etc. to chm file. Do you know that program and how can I find it ? Regards Michal ...more >>

suppose i've got a table like so: create table foo ( foo_name varchar(50) not null primary key nonclustered, foo_age int not null, foo_zipcode varchar(20) null ) go and you populate it with some data like so: insert into foo (foo_name, foo_age, foo_zipcode) values ('john', 31, '...more >>

How can I turn off the transaction logging?...more >>

I am using a simple DELETE FROM tblMyTable I was told the reason it takes 4.5 hours is because of writing to the transaction log. Can I add something to this statement to bypass the transaction log writing? Thanks so much in advance....more >>

All, I am using SQL Server 2000 standard edition. I need to be able to insert data from a SQL Server table into an Oracle table, via a linked server, when data is inserted into the SQL Server table. Please see my example code below: INSERT ORATest..TESTSCHEMA.ACCOUNT ( ACCOUNT_...more >>

Hi all, I have a T-Sql procedure that reads through a transaction table, and does data validation and assigns error_codes. I have indexes on : tx_id, Status, Error_code columns structure of import_tx table is as follows: tx_id, error_code, status, patient_name, patient_address, etc... ...more >>

I need to be pointed in the right direction on extracting data. I currently have a clumn that contains a field that holds several different pieces of data that I want to put into specific fields. Examples of the current data in the Notes column is; A&L-97-92 MOVED BY: Mr. Jenkins SECONDED...more >>

I have been having a very difficult time trying to get the output of a sql query that is in a DTS global variable to return, presently to a messagebox. I created an Execute SQL task with the query I wish to use. The query has been tested in Query Analyzer and works fine and returns the re...more >>

I have an excel file that I am using to populate a table in SQL 2000 using OpenRowSet. If the excel cell contains more than 255 characters the IsNumeric function errors. I can populate the table in other columns with > 255 characters when I do not use the IsNumeric function. If the cell contai...more >>

In a company there are 2 groups of people. One group gets paid on commission 50% for all gross revenue they bring in. Another group gets paid on commission, but on a tiered basis. That is, someone can make: 50% on 0 to 100,000 55% on 100,001 to 250,000 6...more >>

Hello, I'm running a .NET 1.1 program on W2K/SQL Server 2K. I have a VB console program that hits a web site for news articles every minute and inserts betwen 50 and 200 articles (one row per article) into a table. The table has a full-text index on two columns with change tracking. The progra...more >>

Will the following statement give me the date in the format of dd/mm/yyyy? Set @begindate = Convert(Datetime, @begindate, 103)...more >>

Hi, I have built a stored procedure for searching. It works such that if a value is passed in as parameter, it will only return results based on that value otherwise it will return everything. The problem that I noticed with this yesterday is that when I try to return everything (if not v...more >>

I'd just like to say to all you lazy donks out there who can't "write" a query without the damn design viewer have made my life, and I'm sure countless others, a perpetual annoyance! Is it so hard to sit down and write nice and clean queries yourself? Think of how many hours over a period of a c...more >>

while using the inserted and deleted in a query its showing that invalid object name. Could upls suggest me what should i do to get the last updated row in a table. *** Sent via Developersdex http://www.developersdex.com ***...more >>

Hi, How do I remove the decimal places from this to show just whole pounds ? SELECT '=A3' + convert(varchar(30),PotentialRevenue,3) as [Potential Revenue] What is the purpose of the small money data type ? - its seems to have no features for storing/displaying financial data that cant be ...more >>

This is driving me crazy. The following two statements give a count of 1 and 0 respectively. The third statement gives me a 0 count when using NOT IN as the subquery and 1 when using IN subquery. Shouldn't it be the other way round? select count(*) from Table1 where Column1 = '1234567' -...more >>

Dear folks, We'd need have at the same time two NT users in the same Sql Server bearing in mind that: -one domain is NT -another one is an AD We are seeing SQL only allows you have one login at the same time(in both domains is named alike) Does anyone know how could we do such thi...more >>

Is it possible to disable checks for existance of referenced tables(views, user functions) when creating new view Thanks in advance....more >>

How can i return the getdate value minus 5 hrs So if getdate is 2005/09/07 7am .. Id like to output to be 2005/09/07 2am Thanks ...more >>

Hello everybody. I'm looking for book(s) that have very technical information about MS SQL Server. For instance a book that has information about that sysfiles queried in system stored procedures always queries master. I can't find any books that have these kind of details. Can anyone help? D...more >>

Hi All, I have heard that if you create a clustered index on a identity field and its a PK, it reduces the page splits... But how? Thanks, Prad ...more >>

Hello, I want to do a dynamic order but with several criterias, my code look like: SELECT name,price,stock FROM products ORDER BY CASE WHEN @order = 'P' THEN price,stock WHEN @order = 'S' THEN stock,price ELSE name,price END But it does not work, MSSQL doesn't like to have more...more >>

Ussually, I make program and design database at home, then I copy it to office's computer. But, seldom I meet problems. Example: I develope my system so I 've to make a new table at home. If I copy file *.mdf from home, so data at other tables will be loss. Altough I very need to add its table (...more >>

im novice to sqlserver and stored procedures. Can the php code below be converted to a stored procedure $y=1; $query = "Select * From tblNews Order By aOrder"; $result = mysql_query($query,$db_connection); $NoRows = mysql_num_rows($result); if ($NoRows != 0 ) { while ($row = mysql_fetch...more >>

I'm inside EM. I click on a database, then the Stored Procedure list. Everything's cool. When I try to scroll down I get a Message Box: System Error [x] The memory manager has failed. [OK] [Cancel] Obviously, I rebooted, but what the heck would have happened if I...more >>

Hello, I had a discuss of SQL Server's auto rollback behaviour with my colleague. He hasn't convinced me. We use SQL Server 2000. What I've noticed is that, inside a stored procedure, after BEGIN TRANS txTransName If an error is generated while manipulating table data, then the whole tr...more >>

Okay, I've read through several trees on this subject and I think I might have an idea of what is causing a problem in my particular situation. We have a trigger that is reading from the "inserted" and "deleted" virtual tables to return data to a temp table. The issue is that several columns are ...more >>

Dear floks, I use "Embarcadero Rapid SQL 7.2.1" to debug proc,but report error: Program Error establishing a debugger session with the server. See debug log for more information. Does anyone know how could we do such thing? ...more >>

Hi, does anyone can recommend me some good product for groupware development. What I need is a product that will help me document all of my objects in db and automatically create a web site as my end documentation. Anyone? Regards, Andrej ...more >>