I have written a very simple query but it gives unexpected result. I have one table ttest having two columns iid and name. name columns have several duplicate values. i want to delete all duplicate value and keep only latest value. first i want to check the values which I have to delete. my q...more >>

Hi All, What is the difference between executing 1) SELECT statement and 2) Declaring a string variable which contains the SELECT query and executing it through EXEC() call. Does the User need any extra access rights for the second case than required for the first case. Cheers, Sank...more >>

using a IF statement in stored procedure but won't save. what is causing 'incorrect syntax error near keyword "else" ' ? IF <condition> EXEC mysp1 ExEC mysp2 --error here ELSE blah blah blah... GO ...more >>

Hi, Is it possible to use REPLACE() function in the WHERE clause in the SELECT query. For example: select * from symbols where symboldesc in ('Contains','Equals') select * from symbols where symboldesc in (replace ('''Contains;Equals''',';',''',''')) The first query is returning ...more >>

Hello, Is there a way to stop stored procedure execution upon user requests. A stored procedure is called from APS.NET page. Actually a new thread is started that calls sp, the page is then reloaded every 5 seconds and progress bar is displayed. Usually it takes a few seconds to 10 mins to ex...more >>

I want a function or SP that can return value indicating whether a specified table is empty or not. I tried (unsuccessfully) to pass the table name as a param - something like: CREATE PROC GetTableSize @TblName varchar(20) AS BEGIN RETURN (SELECT Count(*) FROM @TblName) END GO ...more >>

This is an ADO issue, not quite that of SQL Server itself. Using ADO (V2.6) Recordset, while saving (UpdateBatch) a newly added record with an identity column as PK, just after the execution of "insert" statement there will be a call to "Select @@IDENTITY" (you can see this in profiler). I pre...more >>

Where can i find more info about how to create User Define Function or any body can tell me how to create joey...more >>

OK gentlemen, here's another one. How do I repeat a task a few times over until the condition is met. I have two tables(table1 and table2). Table 1 consists of an ID column and a number of user data columns. Table2 consists of one ID coloumn, one table1ID column and one user data column. ...more >>

As several people have mentioned in this newsgroup and others SQL Server returns invalid XML when FOR XML is used and the column contains control characters (eg 0x03, 0x08 etc) because it fails to escape these characters to their XML safe equivalent (&#xx). I've heard rumours this is fixed in ...more >>

Hello group, Each month we want to import data from an ascii file, the records are of a fixed length (some spare space is added to fill them up) there are 12 different types of records (variants) Each type has a different (but within the type always the same) build. Each record starts with a...more >>

There is a job for SQL Server 2003 DBA ? Open this... http://www.pickajob.com/Main_JobDetail.asp?ReviewJobs=389729&jTitle=SQL+Server+2003+DBA ...more >>

tableA -- 1 to many --> tableB trying to delete unrelated records in tableB need help getting the query to work in msde can u help ? my thought was to do this but having no success =============================== delete tableB where 0= (select count (*) from tableA where tableA.A1...more >>

how can i get the result of 3 stored procedure to get a final view to use with crystal report ...more >>

Is there any possible way to use table variables with dynamic sql? I need to build a temp table using dynamic sql, and read that it was better to use table variables, but also read that because you use exec to create run the dynamic sql it processes as its own batch the table variable is...more >>

Hello, How would I search for a value of 4 in a comma separated string? example: A) 4,14,44,444,564 B) 123,4,675,4343 C) 122,444,44444,654,8764 I would only want A and B to return -- Posted via http://dbforums.com...more >>

I have a stored proc in which I use a unqiue index on a temp table to eliminate dups. The index looks like this: CREATE UNIQUE INDEX dups_be_gone ON #unbundledorders (custid, ordertype) WITH IGNORE_DUP_KEY And it will prevent me from inserting any dups into my temp table. BUT... when i...more >>

I posted a question yesterday and I'm still having problems. I am importing a file into a table and need to convert the date fields(which are text) to date data type. The date is showing as 103003 The code I got yesterday was the following: Insert into table1(Close_Date) Select CONVERT( ...more >>

In Sql Server 2000, there's a "text in row" option for faster retrieval of Ntext/Text data by storing the data in table rows. Is there any disadvantages about using this option? Somebody on the web suggest that NText/Text/Image columns should be isolated to another table. i.e. Table (...more >>

If you look at the advanced search page for books on Amazon.com, you have 4 text boxes where you can enter: Author, Publisher, ISBN, and Title. If you have a "don't care" field, you leave it blank. What is the most efficient way to implement this with SQL Server? I can't put a "don't ca...more >>

Hi All, Quick question. ASP Replace Replace(string,"@", "") Whats SQL equivalent please? Thanks ...more >>

I have a DTS package to download a file from ftp site. Somehow when I ran the package fro the first time, I can see the file is coming in from File Explor window, but as soon as the package finished excuting, the downloaded file is disapperaed. If I run the same package second time, the file will...more >>

Suppose I have a SQL file "test.sql", is there a way that I can type a command to run this file against a specific MSSQL DB from Command Line?...more >>

I'm using SS2000 and I want to generate a random number for each row in a table. I created a table and made the seq_num column an identity column. Then I inserted the rows. Then I used the following statement to create a random number: update tblCFICombined1 Set Random_ID = rand() * SEQ_NUM ...more >>

SQL 7.0 and vb6 I have created some backup devices using sql dmo but when i try to change tha name it says something about existing backup device name can't be changed. Thanx in advance ...more >>

Hi Everyone, I was wondering if there is anyway to ORDER BY in a query by certain values first. I have a table with Projects and subprojects and sub- subprojects and I always want to display the parent project first. Is there anyway that I can do that. Thanks for all your help. -- Poste...more >>

Hi ! How can I get a date as YYYY-MM as a string in a View in SQL7? Background is, I want to have a month-year daterange selectable in a HTML form. The results are based on Views which count rows for this date range. I could go with one-digits months, but then they don't sort well in the s...more >>

I realize that this may seem simple but this one that is baffling both myself and our DBA. Here is the synopsis: I have a table of 8 records to search and pull data from. I was originally using the following sql statement to do this: "Select [Index] as expr1, PullStationList as...more >>

I'm trying to track down the cause of a discrepency between my FoxPro tables & the SQL tables which were created using the DTS Import appl. When I try to Import into SQL using DTS I receive the following error: "Error at Destination for Row number 1. Errors encountered so far in this tas...more >>

Using SQL Server 2000---I have a table which has a datetime field for when a row in this one table (Ticket) was created. This is for trouble tickets for a help desk. After a ticket has been created, any additions to that ticket are recorded in another table called History. There could be several...more >>

SQL Server 2000 Having Trouble with this Update statement. UPDATE TargetDB..Subset SET [Number] = (SELECT [Number] FROM SourceDB..Master WHERE item = Subset.item and week_ending_date_c = '11/09/2003') i get this error. Server: Msg 512, Level 16, State 1, Line 13 Subquery retu...more >>

How do I take an image from MYSQL and write it in SQL Server 7?...more >>

Hello! I would like this to happen: I create a new database. In the database, I would like to create new tables and SP from a file. How can I do this? Are there any easier way of doing this? Could anyone guide me to open the file. Do I put the information inside a StringList, and use thi...more >>

Hi - I posted this last night but it never get through, trying again ... I have a query which inner joins a table to itself 3 times. it is from legacy code base and in old ansi syntex. (select .. from t1, t2, t3 where..., not t1 join t2 ...) table has 20,000 range rows. when there'...more >>

Hello, I have an attendance database where I am trying to write a query that brings me back all pupils and the amount of absences each pupil has for each of their classes. Whether the number of absences or 0 or 23. The query that is written below brings back all pupils that have at least one a...more >>

I have a varchar field which typically has about 200 characters of text in it. I need to extract a particular set of characters from it, which appear at different location in the text. The characters I want always have certain text before and after them. Is there any TSQL command which will ...more >>

I have a server that has 2 instances of sql server 2000 on it. the first instance is named <server name> with the second is named as <server name>\<instance name> When I am connecting from a 3rd party web program I must use the IP address of the server not the name to connect, this works fine, b...more >>

When I right click on a stored procedure in query analyzer I select the debug option. When I then click execute the debug window appears but I get the error msg ODBC: Msg 0, Level 16, State 1 [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot load the DLL mssdi98.dll, or one of the DLLs...more >>

I'm in need of a SQL Server function which can take in a word and also a single character, and that function will return the number of times that character appears in that word. For Example (we're counting the number of times + appears in the word): Bob+Smith 1 Bob+Smith+Rocks 2 Bob 0 ...more >>

Is it possible to do a weighted average in T-SQL? I have some statistical data in a table with this structure (cut down for brevity): CYEAR int CMONTH int NUM decimal(19,5) DEN decimal(19,5) dbo.calcActual(NUM,DEN) as Actual So I have numerators and denominators (NUM and DEN respe...more >>

Greetings, There are multiple applications that update my table TableX. TableX does trigger update to 5 other tables, including TableY. I would like to void/rollback only the update made to TableY if it was called by application AppZ, in effect retain TableY's old/original values. I believe...more >>

Hi, I've encountered following problem: When I start following select statement: --query1 select artcode, av_ont_in as ilosc,cena= case when (uitg_ontv in ('T', 'O','I','V') or (uitg_ontv = 'U' and bkstnr is null)) then inkprv_hfl else vvp END, av_ont_in*inkprv_hfl as wartosc, ontvnr,...more >>

I'm calling a stored procedure which has an output parameter of type int. Once the stored procedure is executed, I want to check the value of the parameter in case it is null. However, when the a null value is returned I don't seem to be able to detect it. Any help would be greatly appreciated...more >>

I'm trying to set up a stored procedure to read/insert data from a local .dbf to a temp table, is this possible using only tSQL (in other words can it be run through a stored procedure without bringing in a DTS package, etc)? If so, could someone post a short bit of code example? Thanks in advance...more >>

Hello! Does anyone knows how to change the column order for columns in a table? I have succeded by changing in syscolumns, but this requires the option = sp_configure "allow update" set to 1. I think this is a server option, = and I don't want this to be changed, though the database will be ru...more >>

I have SQL server 2000. I have table Products with 40columns, some of them are updating all the time. Now, I would like to create an update trigger only on price column. It would update some other table, when the price of the product is changed. But I can't create the triger only on one column,...more >>

I have a short transaction that is locking up and not rolling back in QA. The only way out is to disconnect and start over. At that point it gives me the option to commit to rollback, but that's not what I want. Can someone tell me what's wrong with this code? USE db DECLARE @intErrorCode...more >>

Hello; I have made a Ms.Access Database , But the problem I want to convert it to SQL SARVER2000. I found a tool in the SQL SARVER2000 that convert tables but it has a problem in the queries. I made the query conversion manually , but now the main problem is in queries that had been written in...more >>

I am trying to convert a varchar column to a date. The date in the varchar data is formated like 110103 010203 090403 The select I am using now is select convert(varchar(6),stpefdate,101) as 'date' from r01 When I run it, it comes back as: 110103 ...more >>

Hi I have a database where I record transaction as below order_id char(12) line_number int item_received int location varchar(12) recordID (autonumber) and also primary key here some some sample data order_id = ABC line_number = 1 item_recieved = 1 location = location1 order_id = ...more >>

What is the diference in create a double, a real, a numeric(9,2) and a Decimal(9,2) column??? Thank's ...more >>

Is there any way to pass multiple values to a stored procedure as a set = of values. For example I'd have an application that must do multiple = saves of data...this set of data first, then a second and third set of = data. But I currently have the VB code to run the first update, then the = sec...more >>

I'm new to stored procedures. I can write Visual Basic code to to the following but I'm trying to create a SQL serverbased automatic process. I need to parse a tab delimited text file to update an sql table. Problem is, the supplied text file is in a non standard format. It's tab delimited ...more >>

I need to store formatted data in SQL. I have tried text and varchar datatypes, but the format does not get stored. I am basically trying to do a dictionary with words and definitions....some of the definitions include tables. Anyone have any ideas? Thanks...more >>

Using MSAccess2000 ADP project: I have a form that contains four search fields named: EmployeeID LocationID DepartmentID SearchText The form has a subform that shows a list of all empoyees. What I want to do is to be able to enter criteria in one or more of the search fields to search th...more >>

I had an interesting problem this morning that I'm hoping someone can comment on. We had a problem with a particular stored proc running very slowly on our production server this morning. I ran a trace and determined the offending proc and executed it with the same parameters in Query An...more >>

I am trying to add an identity column to a temp table. There are unexpected results after adding the column. ******************************************************** create table #t1 ( testvc varchar(10), testint int ) insert into #t1 values ( 'vc1', 1 ) insert into #t1 values ( 'vc2'...more >>

I need to store different formated docs( images combined with text) on SQL database.. Can anyone help with code samples pertaining to this issue, I know you can use the image field, but this means converting text back to ASCII format... ...more >>

sql2k sp3 I just set up a table for full text searching. I was going to compare a query with CONTAINS to one without. Here is the query I used without CONTAINS: select from tr_stock_transactions_st where st_ref_no like '@%' but I didnt realize that I cant use CONTAINS and LIKE in the...more >>

In a table of full of text types, how do I get the longest value in each column? ...more >>

Hello, I wrote a store procedures which, among other things, inserts rows into a table. When I attempt to insert a row which violates the primary key constaint, the procedure immediately stops and generates and error. I was expecting that it would fall down into the logic which tested the @@ER...more >>

How do i delete a column from a mssql 2000 DB? I need to find out ASAP. thanks Dave Lugo...more >>

I need to create a view in order to get the data I need for a report. Within my view, I'd like to declare some variables like this: DECLARE @StartDate datetime DECLARE @EndDatePlusOne datetime However, when I attempt to run this, it errors out on the DECLARE. If I put the declare bef...more >>

SQL sever 2000 I have two tables and I'm trying to update one table with info from another table.... Table one is the master table and table two is subset of table one. the two fields in both tables are ITEM and DESCRIPTION. so where the ITEM in the subset exsist in the master then update...more >>

I am creating a view and one of the selection items has division. Here is the selection item: SUM(CASE WHEN Q1Response = N'A' THEN 1 ELSE 0)/GradStatsByCourse.[Grads Returned] As Agree Unfortunately it only returns a 1 or 0 and not a decimal value. What I would really like is a percent...more >>

Dear All, I am trying to write a sp which on error will capture the error in a declaration. However the trace results in 0 being passed to variable @ErrorSave though it is been passed the value from @@ERROR. Could some bright person see what the matter is ? Thanks J Please find the ...more >>

Hi! I'm not very good in C++ and trying to make an Extended Stored Procedure. This one already works: RETCODE __declspec(dllexport) xp_proc3(SRV_PROC *srvproc, BSTR *ServerName) { DBSMALLINT i = 0; DBCHAR colname[MAXCOLNAME]; DBCHAR spName[MAXNAME]; DBCHAR spText[MAXTEXT]; ...more >>

I have the following tables: (not all fields shown) STORES ------ StoreID int PK ContactID int FK StoreName varchar(35) EVENTS ------ EventID int PK City varchar(25) State char(2) EventDate smalldatetime SIGNUPS ------- RecordID int PK StoreID int FK EventID varchar(20) FK ...more >>

Hello.. A field,say "name",in a Table should be bound by a rule.The rule should hold the set of names that could be entered in the field "name".Could the Rule be made dynamic?I mean could I have a select statement in the Rule which fetches list of names from another table.I have not tr...more >>

help! fried brains.... asp calling a sqlserver7 stored proc which dynamically builds a sqlstatement & passes it to sp_executesql asp page gives the operation not allowed when object is closed error this is the asp code: Set connInc= server.CreateObject("ADODB.Connection") connInc.Open "DSN...more >>

I have a table(a) in one database that need updating from a table(b) in a second database Table a has fields called Licence , INVNumber, MonthPeriod, Table b has fields ACNO, INVNO,INVDATE I need to update; table A INVNumber with table b Invno table a MonthPeriod with Inv...more >>

Yesterday we upgraded our production database by adding some new sps and upgrading old sps with new functionality and so on. We applied all the scripts and every script gave us a 'Command completed ok' result. However, from a certain point forward it seems as if the stored procedures were...more >>

Dear All I was wondering if an expert could have a look at this to see what is wrong. I am trying to get a rollback to work, do it I am purposely putting in an error then attempting a rollback. What is happening is that all the non unique records are being inserted, when the should of ...more >>

I have the following entities Customer Hotels TravelAgencies Restaurants CoachOperator Theatres ============================ I thought of doing as follows an entity "customer" links to an address via a CustomerAddress customerA...more >>

Can someone explain what happens when I run my two scenarios below. CREATE TABLE [A] ( [ID] [int] IDENTITY (1, 1) NOT NULL , [val] [varchar] (50) ) ON [PRIMARY] GO INSERT A SELECT '1' INSERT A SELECT '2' --isolation level is "readcommitted" on both connections --Scenario 1 -...more >>

I am running textcopy.exe utility. But, when I enter servername, login, password etc. etc., in the end it shows some error and the textcopy.exe program's windw close down so fast that I even can't read the error? What's the way to read this error which comes in the end. Thanks! ...more >>