Hi, Do anyone knows how can i get the Unique constraints of a table from SQL-DMO? I know how to get tables, views, PK's, FK's, ... but i can't find the UNIQUE constraint anywhere from the database, table or column object ... Thanks in advance... Carlos Beltrán Vázquez antiCODE@tel...more >>

Here's a simple version of my code if fails just like I expected. I'm so unclear as to my method, that everything I can think of seems stupid. I just need a method, I can work out the script. /* Nearest Age This example fails for a date of 01/07/2003 and a bday of 11/15/1960 */ declare ...more >>

Hi all, How to remove duplicate rows from a table....more >>

Hello, Is there a way to generate an 8 digit alphanumeric password and store these in a table with SQL?...more >>

I am quite new to SQL and did post an ealier question but think did not make myself clear. I have a web application where users request an insurance quote.Quote depends on the AREA one lives.Once their Post codes are known,their AREA can then be determined. A typical business rule is as...more >>

Hi All, I have a table in production with huge amount of records.I need to update certain fields but it take time , i suspect the time consuming is because of it writes each and every update action in to the transaction log , is it any way to do this without writing in to the transaction...more >>

Why is it that you have to specify Top ..... if you want to use Order By in a view? Terry ...more >>

Hello All, while passing a parameter to a SQL Server Stored procedure I would like to pass an INOUT parameter. In C# code, I can set the parameter direction to inout as well. But in the stored procedure code I cannot find the inout keyword. How to make a parameter as an inout parameter i...more >>

Hello, I am learning how to use stored procedures. I have written a stored procedure which calls other stored procedures to update/insert information to other tables. I am having a problem figuring out how to run a select statement and then get some fields out of the select statement to be used...more >>

Hi there, How can I specify SQL to use a certain template, when using the "FOR XML"? Currently I use FOR XML AUTO to get mssql2k's standard output. I need to make some rows CDATA. Thanks, Jas. ...more >>

Hi... does any one have an equivalent of "StrConv("hello world", vbPropperCase)" function for sql? thanks ...more >>

Hi All I have a table of actions for users in a company. I now want to open a recordset showing how many actions there on each separate day of a particular month. e.g. Day Of Month, No of Actions 1 2 2 4 3 ...more >>

I have a need to lookup UPS Routing codes. (see DDL below) I need to return the RoutingCode for a given Zipcode that falls within the PostalLow and PostalHigh values, and return Null or empty string if it does not exist. I am trying to think of a set based approach but the solution is elud...more >>

Thanks for the answer Steve, there seems to be a shortage of good examples around for check constraints, regards Peter...more >>

I have two tables, one called Bookings and the other called Messages. Bookings are taken and the date they are created is entered into the table. When a message is taken for a booking it is added to the Messages table along with the relevant Booking number and the time the message was left. Th...more >>

Hello, I have the following sql query that I would like to add another virtual column to include a count. select car.carname,owner.ownername from owner inner join car on car.carid = owner.carid order by owner.ownername This is the result that I get from the query above: carname ...more >>

I think that you can hide system objects in Enterprise Manager, because I think I've done it before. But for the life of me I can't find where to set this option. Does someone know how I can hide stored procs marked as System from the list shown in Enterprise Manager? John. ...more >>

I generate stored procs for accessing my tables with a tool that I wrote. The tool generates sprocs for use by my data access layer such as Create*, Retrieve*, Update*, Delete*, UpdateConcurrent*, DeleteConcurrent*, Retrieve*For* (many), Retrieve*By* (unique), Filter*, etc.. During development...more >>

Hello: I have a table T1: A | AA | B | BB ----------------------------- 30 | null | 45 | null null | 1 | 48 | null null | 1 | null | 1 null | 2 | null | 2 38 | null | null | 1 I need a query to replace null with 0 for particular condition, like this: SELE...more >>

Hi there, I would like to know where should I look for "How to import data from a file to SQL Server", but I don't want to use the "Right-click - All Tasks - Import Data"... The best, in my case, would be to create a SP where I could pass as parameter the Destination DataBase and the ...more >>

Hello, I've been working on this problem in vain for several days now. I essentially am looking for one row per hour where that row is the youngest row of that hour. So if there are say three rows labeled (17:00, 17:41, 17:43) I only want 17:43's row to show for the 17th hour. Essentially...more >>

Hi all, How can I write a query to obtain the result below. Any help or suggestion would greatly appreciate. Thank you in advance. DROP table #RateHO3Base GO CREATE TABLE #RateHO3Base ( PremGrp_nb INT NULL, TypeFactor_at INT N...more >>

We are distributing a new database application that will hopefully work over different databases (e.g. Access, MSDE, SQL, mySQL, DB2, oracle etc) We are writing in VB6 & ADO 2.x and using odbc/oledb drivers to connect to the database. All the sql is very simple in the hope of it working, unal...more >>

Hi, I need to insert a row in a table but one of the column must be set to max(table.column)+1. How should I achieve this? Thanks! -- Thierry NOTE: Remove 'NOSPAM' from the reply-to address to contact directly ...more >>

What check constraint could you use to make sure only a number (of variable length) is entered into a varchar column?...more >>

Hello, I new to the world of stored procedures, more or less. I want to create a procedure with an optional parameter. If the optional parameter is provided, then I want to use it in the where clause, but if it is not provided, then I do not want to filter by that field. I have tried someth...more >>

Hi, Work with sql server 2000. Some times the attachment is too big and freez up my outlook when I try to open the email. How can I zip it or compress the size of the attachment when I use xp_sendmail? Many thanks. JJ...more >>

Hello, Someone knows How apply a "group by" in order to get this result, for example: MyTable: Field_B1 -------------------------------------------------- b509759c ef0011d7 b95dbe7e 9056092e b509759c ef0011d7 b95dbe7e 9056092e b509759c ef001...more >>

I am working in last 3 years.In .Net, Vb, Asp,SQL Server Technology.I need a query above mentioned. ********************************************************************** Sent via Fuzzy Software @ http://www.fuzzysoftware.com/ Comprehensive, categorised, searchable collection of links to ASP & ...more >>

Hello. We tried to find all the dependencies of a particular table. It did not find all the stored procedures. Some that were dependend on this table were not listed. We used entreprise manager to view dependencies and diplayed all levels. We need to insure that display dependencies is 10...more >>

Scenario: 2 servers w2k sp3 sql2000 sp3 in workgroup NOT domain. server A is web server server B is ready for server A failure. I have to make a strategie that 1) Backup daily alla databases on server A to a different drive on Server A (or B?) 2) Restore daily on Server B from those b...more >>

Hi there, I have 2 arrays coming back from an ASP application. one has a delimited list of PKs, the second one has a delimited list of Counters. For example: Array a = 1,2,3,4,5 Array b = 51,10,40,61,102 I was wondering if there's a better way then creating 1 update per "pair", i.e- ...more >>

I'm having a problem finding the nearest age. If the last bday is closer then the current age, if the next bday is closer then current age + 1 year. declare @bday datetime ,@thisdate datetime ,@age int set @bday = '01/26/1956' set @thisdate = getdate() set @age = case ...more >>

Hi all SQL server 2k dev-ed Win 2k Pro I'm having a bit of a problem designing some parts of my db so I thought I would ask for some help. I need to have several tables with same "main" name but with an additional bit on to make them different. eg Accounts_2001, Inv_2001, Another_200...more >>

Hi SQL Gurus, In our project there many 'small tables' that must be referred by Transactions / as FK. i.e : Area, Region, Market Segment, Currency, Branch etc. These tables typically has colums like this : Area : AreaCode, AreaName, Abbreciation (e.g = '001', 'Asia Pacific', 'As Pac') ...more >>

Hi, I'd like to record the users with their role of every database. The target could be a text file or a view or something else. Has anybody an idea how to do that (stored procedure or any other solution) ? Thank you for every idea, Alexander ...more >>

Hello, Does SqlDataReader maintains a row level lock on the SqlServer? For example if I am using it to do "Select top 10 a, b, c from UserTable order by a", will it lock the first 10 rows? Thanks a lot, /m ...more >>

I am looking for a way to recompile a user-defined-function such that it's meta-data will change... I can call sp_recompile on a user defined function without errors, but it does not seem to do a recompile from the source code. For example, if function "foo" contains a "select * from bar()" ...more >>

Hi All Can any one help me in sql query to search profiles (simple and advance) like in www.match.com. Thanks in advance Mini ...more >>

I have a stock number field That I need to remove characters from right, like 23453-p I need to rove that -p at the end and put it back into the database.Some of the stock #'s do not have this on the end. Examples 26783 38904-p 58768-cr 67890-c -- ______________________ David Fetrow...more >>

Hi all, I have a table which has 3 fields and I need to enforce a unique constrain ID (autoinc) DATEFROM DATETO I need that datefrom and dateto do not overlap (they contain hours as well). What would you guys do to handle this? Thanks in advance, ...more >>

Is there a way to take rows with a datetime field and group them in hours without writing code for each hour you're trying to seek? ie BETWEEN '10-07-2003 06:00:00.000' AND '10-07-2003 07:00:00.000' (repeat for each hour)...more >>

I have a client application, which collects bunches of records, then submits the records to the server. If a record is new, it is inserted, but if its primary key already exists, the record in the database is updated. I'd like to know if there might be a more efficient way to do this than wh...more >>

Can someone explain to me the impact of using hyphens and/or underscores in the actual server name? My network department told me to have the server be publicly addressable we need to use hyphens and not underscores. For example, ABC-APP instead of ABC_APP. Is there any other repercussi...more >>

Hi I'm trying to create a column that will record the last time that row was altered. The thing is I want this row updated automatically by the DB server itself rather than by the application (since it's a commercial app and I don't have acces to the source code). My first thought was to ...more >>

I have code here that does what I want it to do, but it is very slow. I am new at this and am sure the code is the issue. This will take 16hours to insert 30,000 records. Here is the code: <SCRIPT LANGUAGE=javascript> var conn = new ActiveXObject("ADODB.Connection") ; var connectionstri...more >>

Provider=SQLOLEDB.1;Password=m4i3n2k1;Persist Security Info=True;User ID=mmuser;Initial Catalog=Mink;Data Source=webserver01 the problem is in this record source: SELECT TBL_Client.ClientID, TBL_Catalog.CatName, TBL_Seller.GroupID, TBL_Seller.SellerID, TBL_Seller.LastName, TBL_Seller.FirstName...more >>

Hello Could someone tell the easiest way of copying data from one server to the other. I have a Sql Server 7.0 database in a test server and a production server. The database structure and schema is identical in both the servers. I want the data copied from Production server to Test server....more >>

I'm wondering if someone can help me with this. I have an application that I'm building to create legacy versions of a database. Trust me, there are reasons for me doing this the way I am doing it. (I believe the method I am using to be irrelevant). Here's a code snippet: private static vo...more >>

I am debugging a stored procedure in Query Analyser that imports data from an excel file into a table. In order to prevent duplicates this table is defined with a unique index: CREATE UNIQUE INDEX nodups ON TempImport (APC) WITH IGNORE_DUP_KEY SET @Insertsql= 'INSERT INTO TempImport(' + @ins...more >>

Hi All, Given below is the scenario Create Table tblSumm(ID INT NOT NULL IDENTITY(1,1), Project INT, Employee INT, Day1 Float, Day2 Float, Weekno INT, Year INT) INSERT INTO tblSumm(Project, Employee, Day1, Day2,Weekno, Year) VALUES(100, 111, 10.0, 11.0, 20, 2003) INSERT INTO tblSumm...more >>

Hi When creating stored procedures do you have to know the names of the databases to gain the benefits of using SP's. For example the system I am working on is made up of 7 different databases and although I know the names of these databases there is nothing to say that one/some of them...more >>

Here is my table layout : Symbol | TimeofSale | High | Low | Last | Volume Text DateTime Num Num Num Num Example Data: BarSymbol BarTime BarHigh BarLow BarLast BarVolume AAPL 10/6/2003 7:32:00 AM 21.69 20.39 21.49 0 AAPL 10/6/2003 7:33:00 AM 23.69 20.69...more >>

Ugh...I am so frustrated... Does anyone know why SQL server will not allow me to put null values into a datetime field I have explicitly defined as allowing nulls? Is this a bug? and is there a fix? Please help, I am desperate... Thank you!...more >>

My question can be illustated by this example: use pubs go declare @discounttype varchar(40) declare @meanqty smallint select @discounttype = discounttype, (lowqty + highqty) / 2 as meanqty from discounts order by meanqty go Executing this gives the error "A SELECT statement ...more >>

Hi there: At the low part of the Design Table window (SQL 2000), there is a Formula. Anyone can tell me where to find information how to use this Formula? For exp, if I want to limit the lenth of password must great than 6, if I want to give a default value of today's date, ... what should I p...more >>

Hey, I know it's a common problem - but I'd like to ask you if there's a more elegant solution to this. I want to pass a parameter to my stored procedure to tell it which column to order by. I've previously done this by: 1) Making different stored procedures - each with different order...more >>

i have two tables that need to join together. one is Delivery with fields delivery_id, from_country_id, to_country_id and the others tables is Country with fields country_id, country_name. for example, here... Delivery Delivery_id from_country_id to_country_id -----------------...more >>