If there are 2 records with the same part number exists in the cursor or table, I want to keep one and delete the other one. In Visual Foxpro, if you set unique on and index on the part number, it copy only first part number. I have a select statement which selects few records but it doe...more >>

I'm writing a program that has spell check feature. I have a list of correctly spelled word in my database ID(autonumber) WORD(varchar) ---------------- -------------------- 1 a 2 ad 3 about and so on ...more >>

Hi, I have a simple question, sorry if this has been answered before. I have 2 tables: Parent: parentID parentDesc Children: childrenID childrenDesc parentID I would like a query to return all parentID (from table parents) with the count of each children. I've been struggling fo...more >>

Hi, Is there any way to obtain descriptions about stored procedures and functions from sql server database. Thank you in advance. Ashwin. ...more >>

Let's say I have a storage bin that's divided into several compartments. Each compartment may have a "depth", i.e. a number of compartments within it. This table defines the StorageBin CREATE TABLE [StorageBin] ( [ID] [int] IDENTITY (1, 1) NOT NULL , [NAME] [varchar] (50) COLLATE SQL_Latin1...more >>

Hello, How can I copy an Image from one table to another table in a stored procedure. The tables are not identical except they have a column of type image Some code example please Thanks ...more >>

I have a simple table with two columns, 'name' and 'feature' (for example). The name is the name of a person, the feature is a feature that person has: Matt | Brown Eyes Matt | Dark Hair Jake | Bushy eyebrow Jake | Dark Hair Carl | Big nose Carl | Brown Eyes Carl | Dark Hair...more >>

Hi I am trying to connect to my database on the server from another terminal through EM. When I register the SQL server through EM it says "timeout expired". The server does not get registered Please guide me as to what am I doing wrong Thanks Dave...more >>

I have the following trigger: CREATE TRIGGER dbo.PatientUpdate ON dbo.Patient FOR update AS INSERT INTO PatientUpdData SELECT ins.*, s.subscriberid, getdate() subcreatedate, null FROM ( SELECT s1.subscriberid FROM subscription s1 WHERE tablename = ...more >>

Hi, I would like to incorporate a dynamic order by in my stored procedure. I am getting a syntax error at my case statement and it is rejecting my stored procedure. Can anyone tell me what I am doing wrong. Any help would be greatly appreciated. Thanks. Alter PROCEDURE sp_newGetDocument...more >>

I am looking a for a tool that would allow automatic generation of help files or HTML based documentation for SQL code. Something like JavaDoc but instead looks up a SQL Server DB or SQL scripts. Allows for custom tags and other functionality similar to JavaDocs If there is not a good one out the...more >>

I have huge text file and want to import into SQL2K. Any suggestion ? Thanks ...more >>

Hello, I have a stored procedure that returns a recordset. I want to place the record set in a temporery location like in a temporery table or in a cursor. Litterature suggests that I cannot populate a cursor through a stored procedure call. This leaves me with the temporery solution. This wor...more >>

Greetings. CustID is setup as an identity field that starts at 100,000. Customer1 would get CustID = 100,001 Customer2 would get CustID = 100,002... The above if for customers that buy the following products: apples, oranges, bananas QUESTION: Can I setup identity field to start at 20...more >>

Hi, I have a database in which I would like to allocate primary keys from a table in another database. I have a sproc called 'getidentityfrompool' which gives me the next id for a given table in a database. This function works fine. However, I need to write a trigger which will intercept inse...more >>

Hello I have a MSSQL database on my server. When I try to access my ASP form on the site it gives the following error Microsoft OLE DB Provider for ODBC Drivers error '80040e09' [Microsoft][ODBC SQL Server Driver][SQL Server]SELECT permission denied on object 'MasterTimer', database 'ripples', o...more >>

Hi all, hope someone can help me with my query. How would I return the result set as coma separate values. I have DDL as: CREATE TABLE Product ( ID int identity(1,1), ProductName varchar(20) ) INSERT Product (ProductName) VALUES ('COKE') INSERT Product (ProductName) VALUES ('PEPSI'...more >>

Hi I have to tables t1 and t2 with the same colums. I want to update t2 with all rows of t1, but only if the row doesn't exists in t2 or has at least one different coloum. Thanks. OM ...more >>

I manually script out all objects from our databases and store them in VSS. I use the "generate Script" option in EM and now need to automate the process. Does anyone already have this process automated? Is there a command line way of envoking this process? Any help would be greatly appreci...more >>

I'm having a stored procedure that should aleways run. I've solved this my putting this procedure in the master database, and setting the option " exec sp_procoption 'MyAlwaysRunningPorc', 'startup', 'true' " (or chekcing the "Execute whenever SQL server starts" option in Enterprise Manager). ...more >>

I have table1: productID countryID quantity1 and table2 productID countryID quantity2 What is the best way for: SELECT productID,countryID,SUM(quantity1)-SUM(quantity2) GROUP BY productID,countryID Each table can have more rows with the same productId and countryId. And the...more >>

Urgent help requested Hello, I ma new to the MSSQL environment On our server, the MSSQL is installed in C drive and the database is in D drive. When we try to access the site through the IE we get the following error Microsoft OLE DB Provider for ODBC Drivers error '80004005' [Microsoft][ODBC S...more >>

Hi All, I'm doing a SELECT WHERE and I need to incrementally update a field in each record of the resulting recordset. Any ideas on how I can do this? Regards, Tony......more >>

This is probably a very simple query.. I have a column of type varchar I want to write a query to display the rows ordered from the longest varchar to the shortest for that column, what would be the best way to approach this..? TIA, Dan ...more >>

Hi, Im writting Triggers for maintaining AuditHistory details. I need to insert IP address of the client machine in AuditTrail Table. Any help to create this trigger. Bhaskaran.B ...more >>

my problem : I have a table with 1 Lac records I need to display First 100 records initially when the user clicks on a button the next 100 records should be displayed. pls do help me with the query -- Sugavaneswaran S California Software Co. Ltd 1205, 12th Floor, 'D'Bloc...more >>

I can't convert char data to float data. An error occurs. Can S.O. have a solution? TKS A LOT...more >>

I have a talbe that have addresses, and I need to split out the Street Number, Street Name and Street Suffix into a new Table. The data looks like this: Create Table Addresses (AddressLineOne varchar(50)) Insert into Addresses (AddressLineOne) Values ('1234 Parkview Dr') Insert into ...more >>

Hello everyone, I have a date '20040106' Is there a way to output the week day from this date ? Thanks, Andrew. ...more >>

In this code I am attempting to process some values and insert these values into a table type variable which is then returned by the function. SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO CREATE FUNCTION Try (@EmpID Int) RETURNS @Test1 TABLE(OrderID Int, Cost Float) AS BEGIN DECLA...more >>

We have an access DB which is over written by an application every couple of minutes. What we would like to try is running a DTS or what ever anyone can suggest to copy the data into a SQL server table to allow us to keep track of the data. ( this is the boss's idea) We would also like to have...more >>

On one of 3 tested SQL7 servers, a plain vanilla BULK INSERT query that's importing a simple tab-tab-return text file says that the query completed successfully, but doesn't import any rows. The failure happens when running the query via Query Analyzer running on the same machine, on and also th...more >>

Hi: In Oracle, I can perform SQL statement which I written in test.sql, and execute the statement in test.sql by just clicking on test.bat TEST.SQL content: =========== INSERT INTO stmsl(mstr_locn_cd, plant_no) SELECT Distinct mstr_locn_cd, '000' FROM xmtpm WHERE mstr_locn_cd NOT IN (S...more >>

how can i use a insert statement with select clause which selects data joining 2,3 tables any ideas the statement works fine in access but not in sql server how do i do it thank sudha...more >>

The following code when run from ADO/SQLOLEDB raises the error "The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION." Anybody know why? begin transaction insert into table1 values (1) rollback transaction Table1 has the following trigger. DDL for table1 an...more >>

Help! is there a build-in function in sql server, which can accept integer 1 to 12, and return month name, if I put 1, result will be JAN, put 2, result will be FEB. I tried to look up sqlserver book online, I can't find anything. ...more >>

Hi. Does anyone know which datatype is the best (text or varbinary) for storing variable size (can be up to 2K bytes) binary data, and then how to insert that data? I have used the text datatype, but when the first NULL byte in the binary data stream will cause the remaining data to be truncated....more >>

Hello Is there a maximum length for the name of a stored procedure Michael Klap ...more >>

Which is quicker Checking Sysconstraints or bypassing errors and subsequently checking if an error occured after issuing an "Alter Table Add Constraint" on a table? I'm using VB 6.0 and ADO. TIA eric ...more >>

Hi all, Could anybody help me to solve the following problem. I build a sql statement, store it in varchar variable and then execute it inside stored procedure. The problem is that the result sql statement is larger than 8000 bytes. How could I execute statements larger than 8000 bytes? Th...more >>

Hi, I need to get a count of groups in the following data set but can't seem to get the correct syntax: CREATE TABLE tbl101 ( [rownum] [int], [d] [smalldatetime] ) Insert Into tbl101(rownum, d) Values(1, '1/1/04') Insert Into tbl101(rownum, d) Values(1, '1/1/04') Insert Into tbl101(...more >>

Can i use DTS to export/import tables to an XML file ? If so how do i do this? Any help much appreciated. ...more >>

(SQL2K w/sp3a) Three tables (see end for defns) - _respData populated fully (like a customer master table - name, address etc) _respPOTF a lookup populated fully (like an inventory table containing all the products, descrips and prices _respOrders an empty table to contain customer or...more >>

what is RTM version? TIA...more >>

Hello, what is the solution for use a delay (like waitfor delay...) in a user function in sql server 2000? Thanks ...more >>

I thought you could debug Stored Procedures in SQL Server 2000... i right click on the stored procedure and i don't see debug... i tried the help but had trouble narrowing down the debug results to what i was looking for. Any help would be appreciated. Thanks, Tim...more >>

I am trying to create a list of records that are duplicated in a list. When I use the SELECT DISTINCT statement, that just trims the duplicates out of the list. The list needs to identify records that are only duplicated. Any suggestions would be greatly appreciated. Thank you in advance. ...more >>

I am running this very basic query from an ASP application: select * from SimpleResultSet where column1 >= 1 or column2 >= 1 or column3 >= 1 or ...column30 >= 1 As you can see this will return records where at least one of the columns is >= 1 and possibly all of the columns are >=1 This g...more >>

I have the following simple stored procedure which works fine IF the value I pass to the @TheBarcode parameter does not contain a space. I need to be able to have spaces in the parameter CREATE PROCEDURE dbo.sp_BarcodeExists @TheBarcode NVarchar(30), @TheCount int OUTPUT AS SELECT @TheCou...more >>

Hi: I am writing some records from VB6 into my table in SQL server (I am new in SQL server). Some of my records will be duplicate values. I like to ignore the duplicate values and just write the new records (in same transaction). I like to use a trigger to do this. So far I have: create trigger in...more >>

I wrote my own sp_lock (SELECT's on syslockinfo ...) and I would like extend it with the last command transmitted by the spid. This information could come from "dbcc inputbuffer()". Does anybody know what "dbcc inputbuffer()" makes internally? Patrick Simons, MCP...more >>

Hi, I have a database in which I would like to allocate primary keys from a table in another database. I have a sproc called 'getidentityfrompool' which gives me the next id for a given table in a database. This function works fine. However, I need to write a trigger which will intercept inse...more >>

Hello Is there a way to pass a value from a stored procedure to an ActiveX dll then update the record with returned value Thank you Brandon...more >>

Hi all I have just a simple question: Can somebody explain the difference between LEFT JOIN and LEFT OUTER JOIN? What exactly does OUTER mean?? Thanks for any comments. Best reagrds Frank...more >>

I have two SQL servers with two tables I wish to join in a select. The server I connect to is running SQL7 and it includes linked server for the table I want on a SQL2000 server. I can access the data until I want to do a Where on the date which is in the linked table on the SQL2000 server. Then I ...more >>

Hello everyone, Could someone point me in the right direction on how to populate Excel spreadsheet by means of stored procedure in asp.net. I'm trying to avoid use of embedded sql. I tried few different approaches, but so far had showed good performance. Due to our policies, I cannot use...more >>

Hi, I have a sales table with 15 fields which may contain null values or 0. I have to open all fields through cursor and fecth the datas through cursor using fetch next into @var1, @var2. Now, I need to check the cursor fields for > 0 values. I need to select the fields from the cursor who...more >>

Hi, When adding an index to a field what is the difference between creating a unique index and a unique constraint? Which should I use if the values in a field must be unique and there is a liklehood that I will be searching for records in the field. Thanks, Paul...more >>

I have faced the below problem in my appl.Any body know reasons?... Table error: Database ID 8, object ID 1602156803, index ID 0. Chain linkage mismatch. (1:19757)->next = (1:1135507), but (1:1135507)->prev = (1:1135506). Thanks Anna...more >>

I have several tables containing varchar fields. A lot of the fields are allowed to be NULLS. Due to old inhouse software and poor formatting controls i am left with tables that conatin records that have trailing spaces on most of the fields ie; ForeName SurName "Fred " " " ...more >>