I want to delete rows in a table based upon when status_final is true in another table. Something like this (using Select to see what the query will pull up) Running SQL Server 7 select * from startstop_dictation_log s Join Accession a ON (s.Accession_no = a.Accession_no) Where a.Status_Fina...more >>

Platform SQL Server 2000/ASP.Net/C#/ADO.Net ------------------------------------------------------ My DataTable has following structure ItemTypeID int ItemID int AccessDate DateTime I want to store only the latest 10 items of each type. I want to delete Record 11 onwards whenev...more >>

Hi, I'm working on a Web Service that is working fine, but sometimes I received this SqlException. Error #-2: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. on line 0. Error reported by .Net SqlClient Data Provi...more >>

Hey guys, Im trying to create a store procedure that will perform certain duties on a given table. The table name will be passed as a parameter. The problem is I am not allowed to use the parameter as a tablename, so the following queries are rejected: SELECT #temp.[id] as SForceID, @Table....more >>

Books Online (as well as SQL Server 2000 Black Book) do a lousy job of explaining how to use xp_fileexist. How can I use the returned values of this extended stored procedure? How can I set a variable to the returned value? Thanks, ross ...more >>

Hi, does anyone know if its possible in t-sql to manipulate data returned from a table which is larger than the magic 8000 char limit? Am playing with tables where the data is about 10000 chars - so have had to use a blob in the table to store the data, but need to manipulate it in its enti...more >>

Hi, Hope someone can help me out with this. Using SQLServer 2k sp3 I have been trying without success to use the following: ALTER TABLE [dbo].[tblClmCfgFileSpecs] NOCHECK CONSTRAINT ALL ALTER TABLE dbo.tblClmCfgSenders NOCHECK CONSTRAINT ALL Truncate Table tblClmCfgFileSpecs The error...more >>

Hello All, I am doing a very basic xp_sendmail command but it is getting me the following error message: "Server: Msg 18031, Level 16, State 1, Line 0 xp_sendmail: Could not resolve recipient" I re-check several times the recipient name, changed several times also, but did not work. I...more >>

I am building an order reciving system. Typically, I have a table which have among other fields, order number, order line number and quantity ordered fields. I want to record how many items were recived as opposed to what was actually ordered. How can go about building this second table wh...more >>

Hello, I am aware of a way of defining data types in Oracle procedures, and this allows you to change a table's data type and have the procedure dynamically realize the change. The syntax is something like this: DECLARE @Password LoginTable.Password%TYPE So, if the LoginTable's Passwo...more >>

Hi, is that possible to call a stored procedure through SQL-DMO? cheers p ...more >>

Hi all, I am hoping that somebody had run into the sorting problem that I am having - which is: I have a coulomn which contains varchar data such as 1,2,3,...,10,..,20,... when I sort it by "ORDER BY" it will shows as 1,10,...,2,20,... Many thanks in advance. ...more >>

How can you programming in Visual Basic to process the two recordsets? ...more >>

Hi, I am working on a query that will update records like dept_id sort_index 1000153 0 1000155 0 1000156 0 1000160 0 1000161 0 to dept_id sort_index 1000153 1 1000155 2 1000156 3 1000160 4 1000161 ...more >>

Hello everybody, I wonder if someone can advise me on how to pass parameters to a stored procedure that come from another query/stored procedure. I am trying to pass a member's of staff's initials as a parameter. The first recordset returned therefore will be something like: Initials S...more >>

How can I take 3 columns from one table that are related by one ID and insert them into a single column, but individual rows in another table ex. begin: int1 int2 int3 CompID 1 2 3 15 result: int CompID 1 15 2 15 3 15 Is it jus...more >>

hi, which versions of SQL Server support extended stored procedures and the T-SQL OLE Automation API?are they supported on SQL Server 6.5 and 7? cheers p ...more >>

Hi, I am tring to calculate percentage for a given two numeric or integer values. If possible the output should have '%' sign with it. Is there any function for this? If we need to write a function for this, how do we include '%' sign with the output? Any help would be very much apprec...more >>

Hello, I'm trying to update the year portion of a datetime field to 2003 that looks like this: 28.06.1997 20:30:07 (european time format dd.mm.yyyy) where I have some bad year entries of value 2034 with the following script: UPDATE oh SET oh_cr_date = '2003' WHERE oh_cr_date IN (select oh_...more >>

Hi all, I have a problem. The table I designed one column using data type is text. Now I tried to do a trigger. The SQL didn't allow me because the data type is text. The average size of this column is about 11000 characters. How do I resolve this problem which including the triggers an...more >>

...more >>

Hi people, I'd like to know if it's possible or not to call a function from a VB6 dll. If it's possible, how I'm suppose to do it or where I can look for some information. The funcion returns a 6000 lenght string. Any help would be appreciated, Juli....more >>

Is it possible to restore a sql 2000 backup to sql 7.0 . If yes, is it direct ?? If no, what are the all option (work-around) to get this done ?? Changing the compatibility level of 2K db to 7.0 and then backup the 2K db, will this backup able to restore to sql 7.0 ?? Thanks Sh ...more >>

Hi; The stored procedure that I am having a problem with is listed below. the field; (select sum(amount) from dbo.HUDDescription where loanid=loan.loanid) as SUMOFADDITIONAMOUNT is used in the very next field GROSSPROCEEDS calculation and generates the error message Invalid column name 'SUMO...more >>

Hi guys, I am stuck on how to approach this problem: I have a table of items, each item exists one or more times, with different data for different time periods, simplified in this structure: item_id, start_date, end_date, price e.g. item 1 can have different prices in different periods...more >>

I have over 100 text files with the column heading as the first record. They all have a different number of columns and records. The tables do not exist in sql. I'd like to create a table with name of the text file and create columns based on the first row of each file. Then import the data i...more >>

Hello All, I am running the query statment shown below and query the result to a file. As you can see one of my column header is a bit toolong and i like to know how i could break it into more than one line. In other words, is there a way to have column headers in more than one line? ...more >>

Hi, where I can find information about extended procedures in "Master" database in SQL server 2000? For example "xp_dirtree" or "xp_instance_regread" ... Thank you. Michal Svoboda. ...more >>

I am trying to count values in a stored procedure using the case statement. The code is below. I get an error when trying to use a catch all > 3. Am I doing this right. What I am trying to do is get all orders that the datediff is = 0, =1, =2., =3 and then all > 3. I have to do this for se...more >>

Hi, I am looking for a T-SQL script to right justify numerical data in result, example currency. Thanks in advance, Lam...more >>

Can someone give me a pointer to what I need to do to get info from our Active Directory, such as peoples names, email addresses, OU, etc etc? I'm new to this, but saw someone else do it. Have no idea what I need to set up in order to access the AD. Do i need to register my MSSQL server on AD ...more >>

I underestand default date format for dates SELECT statement returns is: "YYYY-MM-DD hh:mm:ss" I concern if i relay on that format, the format changes in onother server implimentation and my program won't be functional. Can i change that default date format ? Thanks, Ali ...more >>

What is the syntax to show only 2 decimals and if there is no decimals, I will assign .00 at the end. Select Discount, Total From Table1 Thanks. Karen ...more >>

Friends, I have a Database A which had some important Data. I Performed a Restore operation on this Database from another Database B. Is there any way I can get back the previous version of database A, just as it was like before I performed the Restore operation. Any help is appreciated...more >>

Hello: I'm running a simple stored procedure from ASP.Net application: ALTER PROC uspGetForm @Ord int, @Form varchar(10) OUTPUT AS SET @Form=(SELECT [Form Nbr] FROM tblPressReleasesWip WHERE [ORDER #]=@Ord) RETURN @Form If [Form Nbr] is a numeric value, everything is OK. However, if ...more >>

I have a SP which uses a table storing value, timehours & timeminutes. I need to calculate the hourly rate from the total value and time, i.e. value divided by time in minutes multiplied by 60 (mins). The formula I've used is: Convert (varchar(12), (timevalue / ((timehours * 60) + timemin...more >>

--- I am trying to create generic sql so that - I can log any updated column into a generic table. EX tblDoucmentHistory (DocHIsAID,TableName,ColumnName,OldValue,NewValue) so that there is one table that holds every change in the system -- -- currently i have the system inserting the "N...more >>

As I do to join those two procedures stored in only one: Alter Procedure sp400_Delvendstoque ( @codprod int, @qtped int, @numev int, @topest int ) As set nocount on insert TMP_delEstoque SELECT @@spid,@codprod,@qtped,@numev,@topest return -------------------...more >>

I believe need to change to convert this and other select into a stored procedure SELECT @PriorityKey=PriorityKey FROM Priority_DIM WHERE PriorityId = @Priority All the select statements are in one large SP and the where clause varaibles are passed to the SP ( WHERE PriorityId = @...more >>

Hi All, I'm having a problem with an unknown locking problem, it is occuring at a client site, so don't have the exact details of when it's occuring. I've added the switch (-t1204 -t3605) which are supposed to log deadlock information (to the log file), but this information doesn't seem to be...more >>

I'm using DTS to transfer tables from 1 server into another one. During this operation Primary Keys and Default values are lost. How can I prevent that? Thank you Vlad ...more >>

Posted in ODBC, no response. SQL 2K, SP3, Win2K, SP4. We've created and scheduled a maintenance job to perform optimization tasks. All was running fine until we added a computed column (without an index) to one of our tables. Now the job fails, reporting: [Microsoft SQL-DMO (ODBC SQLStat...more >>

How would you extract data from a field within Query analyzer. For example if I had a field which stored an entire sentence like "Hello my name is Rog." How would I create a procedure or function to extract anything after the word "is" Thanks in advance. ...more >>

I have the following select statement: SELECT PersonID, TotalAmount = Case When ClassID = 'A' Then 'A' Else Convert(varchar(50), ReceivedAmount, 1) End, --Max(date) What I need to do is show the ReceivedAmount for the latest date only. So if there are 3 dates for a person ...more >>

I have previously selected the top 30 records and manually updated the "ROUND" column to 'A'. Then, I selected top 30 where "ROUND" is null and manually set the "ROUND" column to B. I do not know how to write the script that will select and update the "ROUND" field. I want to grab 3...more >>

Good morning. Does anyone knows how to select a counter along with other fields from a table? For example, I have a table: Product ProdName Price Prod1 20.00 Prod2 25.00 Prod3 30.00 Prod4 12.00 I would like to query it and get the following output: Prod...more >>

hi, the user met the below problems when trying to update the table records from the Microsoft Access application: the first error msg is : Microsoft/ODBC SQL server driver/SQL server: the text, ntext, image data type cannot be used in the where, having, on clause, except with the null o...more >>

I can't figure out how to create a script to select top 50 * from table update a column with a value then select the next 50, update the same column with different data select the next 50 and so on any ideas? Thanks, Aaron...more >>

Does anyone know how to integrate reports generation in ASP based on SQL Server 2000? Could Crystal Reports be used? Thanks david ...more >>

Hello, Someone knows the way yo build this query. you Suppose that i have this tables: Table: Customers Cust-City1-City2-Phone-MaskIn-MaskOut Peter-New York-Miami-1000#5716-American-DataPort Peter-Canada-Boston-null-European-SouthAmerica Peter-Canada-Tampa-null-African-IBASIS Table: Un...more >>

Hi all, I have a problem. The table I designed one column using data type is text. I tried to do a trigger which not allowed me too because the data type is text. The average size of this column is about 11000 characters. How do I resolve this problem which including the triggers and con...more >>

set xact_abort on --tried with and without declare @var varchar(3) set @var='xxx' create table #tmp (f1 varchar(10),f2 varchar(8)) DECLARE @SQLString NVARCHAR(500) SET @SQLString =N'SELECT * FROM OPENQUERY (linkedPSQL2000db,''call spl_MyProc('''''+@var+''''')'')' insert into #tmp --this seem...more >>

hi Can we change the Incremental value of the IDENTITY column in T-SQL without using ALTER TABLE Command. I mean, as we can change the Seed value using DBCC CHECKIDENT, is there a way to change the Incremental Value. Thanks Anand ...more >>

Hi! I have a problem, i dont want the exec sp_executesql to promt anything to the screen. Just run in the backgroung and dont show the result. sql code: .... SELECT @sSQL = @sSQL + ' LEFT OUTER JOIN it_reg_kringut AS kringut on kringut.id = ut_kringut.kringut_id ' .... IF(@m...more >>

i have writen a stored procedure in sql server. while executing this stored procedure in vb-ide error occures. i am writing details on it. please reply me. details is given below: suppose there is a database in sql server named test, a table named tbltest and a stored procedure named s...more >>

Declare @mSQL VarChar(1000), @mTranId int Set @mSQL = 'SELECT TranId FROM [Standard].vw_AccountsTransaction' DECLARE @myCursor CURSOR --SET @myCursor = CURSOR SCROLL KEYSET FOR SELECT TranId FROM [Standard].vw_AccountsTransaction -- This Working ---SET @myCursor = CURSOR FOR SELECT ...more >>

Hello, In oracle we can define a synonym for schema.tableName Can this be done in SQL Server? If yes, how? Thanks Anand...more >>