Greetings, I have two stored procedures in SQL 2000. Procedure 1 (procMfrGageLookup) - looks up an item by part number, @MfrGageLookup, and returns the description, vendor, and price from the tblMfrGages. Procedure 2 (procMfrReplacedGageLookup) - looks up the above part number to see if...more >>

Can someone tell me whether Wombats live only in Australia, or also on other continents? Apart from zoos, of course. 134 -- Posted via a free Usenet account from http://www.teranews.com ...more >>

I have an Access Project that is using a SQL Server 2000 database. I want to create a function in Access to read a table called tblDisplayOptions that only has one record. It is used for configuration. One of the fields is called CompanyName. I want to retrieve the company name and use it ...more >>

Hi All I get a list of testids to a function and call the following sql query: Example: I have just one testid 1 ( this works fine) select TESTSCRIPTID, TESTSCRIPTNAME, TESTCASENAME from TESTSCRIPTS where APPID=1 and TESTSCRIPTID in(1); However in case where I do not have any test ids my ...more >>

I need to allow a user to enter the fiscal quarter (1,2,3,or 4) and year in two separate parameters and then query my database for corresponding sales with dates between and including those corresponding to each fiscal quarter. i.e. The user chooses quarter 1 of 2006 and the query returns all...more >>

Hi i have a SQL that i need to be more efficent. my current SQL is SELECT * FROM TABLE2 WHERE TABLE2.TABLE1PKID IN (SELECT TOP 1000 TABLE1PKID FROM TABLE1 ORDER BY TABLE1PKID) This SQL runs fine, but its a very resource consuming SQL. What i basically need is the TABLED1PKID at the 1000 (...more >>

Hi, I'm looking into the best way to do the following: execute a stored proc, sending the results to a file and emailing the file to someone. I've done this in the past, where I use BCP - but that was when I was dumping a whole table to a file, so I am not sure I can use that to execute a st...more >>

Hi all, I'm working on an off-the-shelf-ish app that uses SQL Server for data storage. One of my requirements is integrated backup and restore -- users should be able to backup and restore the SQL database from the application. I've been trying to cut down on the SQL login's required priv...more >>

I need to replace the old interface DbLibrary from C++ code (ntwdblib.dll), replacing with ADO (i.e. using #import <msado15.dll>). One handy functionality the DbLibrary had was dberrhandle(my_error_handler) and dbmsghandler(my_msg_handler), that was called from PRINT statements issued inside ...more >>

I thought I knew what "connection" meant, but now I guess i don't. Two books told me that temp tables were not destroyed until the "connection" ends. I created temp tables in one SQL script, and tried to build a view from them in another, and got SQL Server error messages saying that "Views or...more >>

i am using SQL Enterprise Manager to connect to a dbase on my hosting companys server. How could i query an entire database (holding approx. 30 tables) to look for a specific email address.... there are too many tables and records to do it manually.... thanks...more >>

Hello, I have an sp1 which calls sp2 which calls sp3. Is it correct that if I only need to surround the code of sp1 with BEGIN / END TRANSACTION to run all the code in a Transaction? Thank You Joachim...more >>

I need a result set that gives me back the following Information LastName, FirstName, Count(EventTime), Max(EventTime), ReaderDesc from the query follows. I have tried many different queries to get this information and I just can't get it. Below is my last try and it will give me for use...more >>

Hi I have a function that removes several strange characters from strings. Most of these characters are from Japanese or other foreign languages. This works fine except for one character that makes SQL 2000 crazy! Try this: declare @string nvarchar(100) set @string = N'abcã‚›def' select ...more >>

How do I make a SELECT stmt that returns multiple rows for each customer be on the same row. For example if I have a select stmt that returns CUST# , CATEGORY# , CITY , STATE 22 , 4, Memphis, TN 22, 6, Memphis, TN 22 , 3, Memphis, TN 65, 4, Atlanta, GA 65, 6, Atlanta, GA 65 , 3...more >>

Hi, I have an application that connects to an SQLExpress database. The database has a named instance (name is "SQLEXPRESS"). The problem I'm having is that my application can only connect to the database if the database is installed on the same machine as the application. I'm having trouble con...more >>

Hi Everyone, When I run the dateadd function adding months on 30th and 31st of any months returns the same result. I am OK with that because logically it is correct. If I have to do a reverse lookup for the date, how can I do that. Here is the code sample... select dateadd(mm,6,'...more >>

I am upgrading laptops..... I have WinXP sp2 on my previous laptop.. and I just installed my SQL SERVER 2000 ENT EDITION on that machine... to perfectly match my 'real' environment.... on this new laptop.. which is also WinXP sp2 .... if I try to install SQL SERVER 2000 ENT EDITION... it sa...more >>

Ladies / Gentlemen I need to know what is the best way to Find the Number of Records in a #Temp Table in TSQL. I know that I can use the following IF ( SELECT COUNT(*) FROM #Z003_3 Z3 ) > 0 BEGIN RETUR...more >>

I am adding functionality for my users to be able to write ad-hoc Select statements in an application (heaven help us all!). I want to be able to at least check to see if the Select statement is valid. Is there an activeX control or some other way to pass the statement to the server just to ch...more >>

I am trying to create the following update trigger that puts the current date in a field whenever that row is updated but I'm getting error on the inserted.... Code is below. Thanks. David CREATE TRIGGER [dbo].[T_Company_UTrig] ON [dbo].[Company] FOR UPDATE AS UPDATE dbo.Comp...more >>

I have a Query which is running in in < 2seconds. When i place that same query into a stored proc, it takes 2:26. This issues creeps up on me (with the same proc) every now and then. Lately, I have taken to adding some code to the proc to change it and that resets the spiffyness; however, to...more >>

I have a query (see end of post) which runs in under 2 seconds until I place it in a stored proc. Now it takes 2:20. Has anyone seen such a beast before? ________________________________________ CREATE PROCEDURE [dbo].[usp_svms_check_GetAllVerifiedChecks_new] ( @iUserID int = 0, @sStat...more >>

I am having trouble writing a procedure with an output parameter. My first attempt works, but it doesn't create the text output. It merely returns the input without modification. CREATE PROCEDURE GOODPROC @PARM1 TEXT OUTPUT AS BEGIN PRINT SUBSTRING(@PARM1,1,100); END; GO EXEC GOODPR...more >>

hey all, i'm trying to create a stored procedure that needs code from about 3 tables. the information needed from 2 of the tables is just one or two values. is it better to build a join query or create variables in my stored procedure an run small select statements? thanks, rodchar...more >>

Hi, this easy t-sql (MS Sql Server 2005) statement works fine when I leave out the last line (AND Rows >=3D 1 and Rows <=3D 1) SELECT m.*, row_number() over (order by m.mes_id) as 'Rows' FROM [dbo].Users as u, [dbo].message as m WHERE m.MES_ID =3D m.MES_ID AND m.MES_USERID =3D u.USE_ID...more >>

In a 2005 database table function that has SELECT TOP 100 Percent .... ORDER BY TextField This displays it in the primary key (numeric field) order and not the specified. Is there a setting change that is required to make the function return the order as specified? ...more >>

Hi, I have table something like this. Create table t ( sno int null, ans varchar(10) ) I have 10,000 records in this format null, a null, b null, c null, d etc., Now i want to write a query which would update the table with running numbers from 1 to 10. Starting from 11th to...more >>

Hi - I'm using SQL 2K5 and am trying to create a trigger that updates a field of a record that was just inserted (based on a lookup in another table). I'm having trouble getting the identity value of the record I just inserted (the identity field is a primary key that auto-increments). I h...more >>

Hi all When i want to return one row from a sql table using one sp and ado.net, what is more effective? Using in/out parameters with select into statement or using a select statament and a dataset/datareader to read the data? Thanks in advance ...more >>

Dear All, I'm attempting to create a query that will transpose repeated fields into a single table structure. Can anyone think of how this can be done as I'm stumped at the minute? I'd like to do this without having to create a cursor due to the overheads and performance issues associated wit...more >>

I have 2 SQL 2000 servers connected with network. There is no firewall between them. MSDTC is running on both servers. One of the 2 servers is called locale the other is called base. The base one is a linked server in the locale SQL server. The base server is on Win server 2003 while ...more >>

I use the UPDATE statement on ocassion and it always strikes as rather dangerous. If for any reason the where clause fails to work as expected then too many records get updated. The worst case would be if someone left the where clause off altogether or someone accidentally deleted it at a late...more >>

Hello, I've got a table with the following Columns and data: Project, Start Date, End Date. 123A, 10/2/2006, 10/20/2006 Now I want to create a query that shows all days from start date to end date, excluding weekends. Like this: 123A, 10/2/2006 (which is the start date) 123A, 10/3/20...more >>

hi, how to compare two strings after remove space: select * from where remove(string1) = remove(string2) thanks. ...more >>