Hi I have SQL2k. I have configured the mail profile and SQLMail server is running. Now, how can i can receive mails (go to POP3 server and get mail) Thanks -- ----------------------------------------------------------------- Matviyuk Maxim Director R&D Ethrix Ltd. mobile: +972-68-21...more >>

I have a stored procedure where I try to insert into a table. Some = times the insert will fail because of a primary key constraint. I want = to ignore that message. I want my VB.NET program to not even be aware = of the error. Even though I handle the error in the SP using @@error I = cann...more >>

In SQL SERVER 2000 I have the following data. Names of 100 Radio Stations The song names played on each of the 100 Radio Stations The number each song has played at each of the 100 radio stations. From this I would like to get-- The NAMES of each of the 100 Radio Stations with The TOP 10 ...more >>

I have c1 table Franchise records that have an associated Franchise record in the cs table. I also have primary contact records in c1 that are not a Franchise record. I want to use the zip from a primary record, query the cs table to find a matching zip in the notes field and return the Fran...more >>

I have this triggers in T-SQL to execute when a product qty is 0, it does work, but I have to be able to monitor this on a remote PC using VB6. I know how to execute storeproc with VB6, but this is something I cannot figure it out yet. I have this same problem when trying to get an Activit...more >>

Hi, I have an old dos programe which connect to sqlserver 6.5. Now I want to upgrad the sqlserver to sqlserver 2000.But I can't connect the database -- sqlserver 2000.Then how can I connect to sqlserver 2000 in ms-dos 6.22.Any advice will be appreciated. Thanks! Jack ...more >>

How to drop a table with foreignkey constraint table in sp or a VB sub procedure? Thanks. ...more >>

Hi again, I've written a pair of functions, and for the most part, they work extremely well: -- Exec sp_AddType 'T_Duration', 'Numeric(9,2)', 'Not Null' -- Exec sp_AddType 'T_DurationStr', 'Varchar(15)', 'Not Null' -- 'hhh:mm:ss.m' Create Function dbo.fnStringToDuration (@StrIn T_Dur...more >>

hi, work on sql server 2000. I have serveral dbs that are either 'full' or 'simple', their tran-logs have lots of empty space in it due to one- time big processes, hence, I want to shrink their physical size down. I used the below query on several dbs, some log size got shrinked, som...more >>

hi, I am working on sql server 2000 replication. I have an existing publication from server1 to server2 which has 6 tables in the publication. Now I want to add two more tables into this publication. How can I do it without have to re-initialize it? is it always better to re-initi...more >>

Hi, I'm wondering if this is possible: I have table with only one column (name) and stored data e.g. (John, Mike, Peter ...) now I want., with sql query, to get ouput like this: 1 John 2 Mike 3 Peter ... where numbers are row index in table. Is that possible? Thanks i...more >>

Fairly new to T-SQL and still learning basics here, but I have an immediate need to come up with an AR Aging Report which compiles and cross-tabulates two tables. Table AROPEN includes the following fields: CUST_NBR, INVOICE NUMBER, INVOICE_DATE, and INVOCE_AMT Table UNAPPLIEDCASH includes ...more >>

Hi guys, Do you know if it's possible to change following Query: SELECT ___ as Num, whatever FROM Table WHERE whatever = whatever ORDER BY whatever the way it returns: 1: 1, whatever 2: 2, whatever 3: 3, whatever .... so on where 1,2,3 is row numbers in returned recordset. Ple...more >>

Not clear on the prerequisites to hit a table On a different Server. I'm fine when joining tables from different DBs on the same server but when I need something in a table on a different server, I'm missing something. I Know it should be ServerName.dbName.owner.tblName but..... select top...more >>

I create a stored procedure that returns top 5 records based on username that is passed in. It works great when the name has no spaces however if it does have spaces it does not return anything. Why is that? Thanks ...more >>

Hi All I have a tablw which has names of districts and also contain id in that table but when we get data to be processed we get only the names so what is the best way to match these as it may also have spelling mistakes I do something like this JOIN on Substring(fieldname,1,5)=substring(fiel...more >>

There's a small error in the script I just posted - corrected below - where I was trying to make a primary key from a null default column. Sorry! And thanks for any help. The results of a survey of consumer preference is stored in a table, below. Persons can indicate for each of three produc...more >>

I have setup a linked server to an oracle server. Everything works fine but when I try to run queries as below select * from banner.UCM_DEV.BANINST1.TZVIDEN I get the following error... Server: Msg 7312, Level 16, State 1, Line 1 Invalid use of schema and/or catalog for OLE DB provid...more >>

The results of a survey of consumer preference is stored in a table, below. Persons can indicate for each of three products (one per column) whether they 1 strongly like - Hard - or 2 moderately like - Soft - that product. Zero or null values in product columns are not counted. Persons can also ...more >>

Dear Programmers, I want to store a number of .NET type double (16,95) into the database column of type numeric, but it is stored as integer (17) and when I call the value in my application, it is displayed as 17. How can I solve this problem? Thanks in advance, Burak ...more >>

Hello all, I'm creating a balanced scorecard web application for my company (a call center). One of the performance indicators is attendance. Calculating an attendance percentage at the agent-level is easy: [sum of all absent days] / [sum of total days in reporting period]. However, I n...more >>

Declare a variable XXX a table name = TableXXXName ( insert variable between Table and Name) how to write a select statement where table name got insert a variable ? Select * from Table+XXX+Name .......can not Select * from Table%XXX%Name......can not Select * from Table&XXX&Name .......c...more >>

I need to code a read ahead loop in my sql. If I find a certain record I need to read ahead to find the match and determine if the two need to be exported or not. Under normal conditions the next record is the match, but there could be two or more records in between. I think I want to...more >>

hi Is there any way to access the row id in the sql server in oracle we have ROWID but i was unable to find it in sql thanks kumar...more >>

I have 2 data sets, one in a SQL 2000 database, the other in a Jet 4.0 database. I am using ADO, and a select statement to extract an identically keyed subset with an ORDER BY for a field (part number). Initially, an extract is made from the SQL database to create the Jet database. The Jet da...more >>

Hi, I'm debugging some weird problems on stored procedures/triggers inside transactions, on a production server. I can't use tracing. So for the moment I used sp_send_cdosysmail for tracing sp/triggers execution by sending mails to myself. I dare is a better way to do that. Is it another way t...more >>

We need to create a record based on the insert/update on three tables and then moved/update to two tables residing on a different database. Table1 Table2 Table3 Client Name Lognumber lognumber Lognumber Clinical Notes Appt.date ...more >>

I have a strange situation. I have a stored proc I am trying to compile in the master database. I get the following error message Server: Msg 134, Level 16, State 2, Procedure sp_send_samail, Line 75 The variable name '@From' has already been declared. Variable names must be unique within a ...more >>

as in query analyzer, when a "divide by zero" occurs, the result be automatically set to null and the execution will resume. but when a stored procedure is executed in a job item, it fails when such an error happens. can anyone tell me if there is any option I can set to resume the execut...more >>

Hello. One of our developers executed an ad-hoc INSERT INTO ... WHERE ... statement that inserted about 49,000 records. While this ran, an application that utilizes the table that was being Inserted Into timed out while trying to access the table. I assume that SQL Server somehow locked thi...more >>

Hi, When I open transaction with insert statement from linked server and that linked server is not available, then connection is stuck and MySQLSServerTable is locked (see script below). When I kill connection it cannot finish rollback until SQL server restart. How can I cancel transaction or k...more >>

Hello Sql Gurus, Please throw some light on the following query. I had a table with 2 columns col1 days 10 10 10 1 10 30 10 50 10 60 20 30 20 40 20 60 col1 daysout daysout dayout 1 - 30 31 - 60 61-90 10 3 2 0 20 1 2 0 Thanks in Advanc...more >>

If this is a snipet of my code, how can I go about supplying parameters so that @regionID equals the value from a drop down list, ddl reps. Here's the select: SELECT * FROM AccountTransfers WHERE FromRegionID = @RegionID OR ToRegionID = @RegionID heres the method: Sub BindDataToGri...more >>

Hello there I am runing on sql 7 environment and planing to move to 2k environment (quite late but......). for one of the databases, I have a server with 4G of ram but I have only sql server standart, I am wondering if it will still take 4 G of ram since MS claims it will take only 2 G???? f...more >>

Hi guys, I have hit this bug more than once and was wondering if anyone else has ever seen it? SELECT A.nId ,B.nId FROM Server1.myDB_1.dbo.TableA A LEFT OUTER JOIN Server2.myDB_2.dbo.TableB B ON B.nId = A.nId WHERE A.nId IN (SELECT cId FROM Server1.myDB_3.dbo.LogTable WHER...more >>

I am using an "left outer join", but am not getting rows back where I thought I would. Table "Item" contains one rows per item Table "Item_Cost" contains multiple rows per item, with an "ACTIVE_COST" flag indicating the current cost. select i.desciption, ic.Item_cost fr...more >>

Hello, all. We need to loop through the values in one table and compare them with values in another. Which is more efficient -- a cursor or a table data type for the first table? For example, this is the sort of query we run, -- from the cursor on the 1st table, we get @strCourseSection and @s...more >>

Hi all, What I'm essentially trying to do is: for a table (table1) containing the columns x, y and z select * from "the row containing the max value for column y in table1" If someone could help out with how to actually write this query properly, it would be very much appreciated Cheers...more >>

Hi There, My query below has an order by clause that is not working, could someone help with the syntax please? Cheers, Moe Select lbllocation, tblListings.location, tblListings.formid, tblformlbl.formid , tblListings.listeddate from tblListings,tblformlbl WHERE tblListings.formid=tb...more >>

I have the following tables: (simplified) Customers: Master Custid C(2) Programs: Master Progid C(4) CustPrograms: Details. Custid C(2) Progid C(4) UserPermissions C(1) ProgramsUsed: Details Custid C(2) Progid C(4) Date DateTime I'd like to calculate how many times every cus...more >>

hi, 1)is there any easy way to disable identity property of a column in a stored procedure. In entraprise manager it is very easy to do . 2) I was trying to insert into a table with a identity column using a dynamic sql statement and i tryed to use identity_insert on , but it did not work. ...more >>

Hi I have a general question with regards to the various switches that can be set to ON or OFF. I have a client that I issue with SQL Script patches to allow them to fix problems, update their databases etc. The problem I am suddenly having is that they are getting errors when running the scripts...more >>

Hi I have various table, one for each different type of exam (e.g. tblExamOne, tblExamTwo etc). tblExamOne will have a design like this userid| Answer1 | WMScore | Answer2 | etc. Each exam table contains the userID but vary with no. of other columns When a person picks the exams they...more >>

This is probably a FAQ and/or something you just cant' do, but I'll ask anyway... Say you'ge got a table variable into which you've selected some rows from some table. Can you then iterate over the rows in the table variable, in an analogous sort of way to how you might use a cursor to it...more >>

I have an problem updating value of one cell. This cell is nvarchar format, and it is supposed to contain data from one column. lets say for example, all the data in the column "FirstName" with some privileges needs to be inserted in a cingle cell into another table. I've tryed with: update ta...more >>

Hi All, I dont suppose anyone know's of a funtion or code that can be used to change the word for a number to it's numerical value, e.g. one=1, two=2, three=3 I think I have seen it somewhere but I could just be hoping. Thanks Phil...more >>

I am having great difficulty with getting COLUMNS_UPDATED() to work they way I want. The problem I am attempting to solve is as follows: I have a table with 27 columns, the last of which is a Timestamp column. This table is access from Microsoft Access 2000 with a bound form. Even though us...more >>

Hi, I need an advice how to do this better: table Cities, columns: City, Country_id, Order_id City Order_id Country_id NY 1 1 Chicago 2 1 Washington 3 1...more >>