Sorry for the re-post, my original post wasn't very clear. this is very important. I need a query that can return wildcard matches if there's no exact match. I'm using IIS 5 with ASP.NET and MS Access. Bascially what I'm trying to do is to partially match a field. It's like when you type a mis-s...more >>

Just wanted to know if this is possible. In my stored procedure lets say I fire the below select query : "SELECT * FROM EMP where EmpName = 'Nilesh'" and before my stored procedure completes execution. If another user fires the same select query, his call should wait. However if he fires a SEL...more >>

My application needs to calculate the sort order of an index key (whether the index key is descending or ascending). The user may connect to MS7 or MS2K servers. As far as I know, the descending indices are supported in version 8 i.e SQL 2000. In MS2K, I can get the index key information by SELEC...more >>

I must be stating my problem incorrectly or I don't know how to use a newsgroup but I will try one last time to learn if a SELECT or other combination of SQL statements can perform the following task For illustration purpose I have two conceptual database tables: Table A and Table B from which...more >>

Hi all, I have a situation here. I have a table (Options) which has the following single record {field}={value}:- Quota=50 (smalllint) CarryFwd=1 (bit - can be 0 or 1) CarryFwdMax=20 (smallint) And I have a table (Users) which has the concerned field called AvailCredit as one of its fie...more >>

Hi I need to select the top 5 item from a table based on the price. I know MAX would return the highest priced item but I need the next four items as well. Any help would be greatly appreciated. Regards Carl Gilbert. ...more >>

I am looking for professional SQL Server 2000 programming skills for a single task. Please advise as to hourly rates. Thank you, Don Montgomery...more >>

First, happy new years to all :) Got a problem with a SP. I have been researching this issue most of the day but have found no answer yet. Server is SQL Server 2000 with SP3 installed. My application is calling a stored procedure through ADO. It is done in C++ and the call of the SP happens...more >>

I sure hope someone can help... I am trying to link to a database of another server to perform a mass update on a field in my user database that resides on a different server. I have a stored procedure with this statement: UPDATE D01_InvDetail01 SET D01_InvDetail01.D01_BuyerCode = NTS...more >>

I'm trying to retrieve a list of cubes from AS Server using Microsoft DSO in an ASP.NET page Here's my sample code Dim dsoServer As New DSO.Serve Dim dsoDB As DSO.MDStor Dim dsoCube As DSO.MDStor dsoServer.Connect ("LocalHost" For Each dsoDB In dsoServer.MDStore If dsoDB.Name = "XXX" T...more >>

how can I get row number? example: SELECT rownumber, name from products thank you, Simon ...more >>

Select A.DESC_ID, A.CURVE_TYPE, B.DESCRIPTION as FUNCTION_TYPE , A.NAME, A.DESCRIPTION, A.EFFECTIVEDATED, A.CREATED_BY, A.EDITED_BY, A.UDF_MATCH_TYPE, A.MATCH_TYPE_DEF_ID, A.TAB_TYPE, convert(BIT,0) as used_in_plan from UDF_DESC A, FUNCTION_TYPES B WHERE TAB_TYPE= 1 and A.FUNCTION_TYPE ...more >>

Trouble removing duplicates by Rank. I have two sample database tables - Table A and Table B. Table A has 2 fields; Company_ID (prikey), CompanyName Table B has 4 fields; Link_ID (prikey), Company_ID, Descript and Rank Results Table is to consist of 3 fields: Link_ID, Company_ID, Descr...more >>

Hello I have this query: SELECT * FROM Employees FOR XML AUTO,ELEMENTS and it returns about 100 tuples. problem is that the column gets som wierd name (XML_F248734-F........) and i have no idea how to retrieve the result in my app....... is there a better way to solve it? concatenate t...more >>

Hi, I know what I've got below doesn't work as each SQL statement is run procedurally, but is there any way to do this without using a cursor? Speed is my key factor. I've BCPed in a table of legacy file containing data from an old system, half of which I need, and some of which I want to ...more >>

Hi, I dunno how to update a query when it has an apostrophe in the string...To make life easy, look at this: sql_Update = "UPDATE tblTable SET name = '" & param & "' WHERE id = 1" this works fine if the param, which is a string parameter doesn't has an apostrophe. Error will occur when param ...more >>

Hello There! I would like to create an stored procedure that would execute multiple updates on a column, basically renaming existing entities in a column with another names. Basically is like i have 5 entities (01,02,03,04,05)in column entity and want to update (01 to "Moon", 02 to "Su...more >>

Hi, Is it possible to create a stored procedure with a variable number of arguments (by example, like the "printf" function in the C language) ? Where could I find documentation about this ? Thanks for any answer. ...more >>

Using SQL DMO, If I use ExecuteWithResultsAndMessages2 to return a money column from a table I can get it to show NULL values properly. For example if I do SELECT LicenceNo, PurchasePrice FROM Vehicles, It returns the following data:- Licence No Purchase Price ------------ ------------...more >>

I'm having trouble to come up with a query. Here is DLL: CREATE TABLE [dbo].[Prices] ( [PR_From] [smalldatetime] NOT NULL , [PR_PriceB] [smallmoney] NULL , [PR_PriceC] [smallmoney] NULL , [PR_PriceD] [smallmoney] NULL ) ON [PRIMARY] GO ALTER TABLE [dbo].[Prices] ADD CONSTRAINT [...more >>

Hi All, I have a lot of queries that run on the first of the month that require date parameter which is previous month date. Example: Query runs on Jan 1 2004, parameter should be between 12/01/03 and 12/31/2003. I would like to create stored Procedure that automatically generate the fi...more >>

I was wondering if it is at all possible to create a identity field that will maintain some sort of algorithm. For instance the keys would start with the following pattern AAA001 AAB002 AAC003 etc. Thanks for your help....more >>

While creating scripts for prod.env. is it advisable to generate with on primary. e.g. CREATE TABLE [Mytable] ( [CreateId] [varchar] (15) , [CreateDt] [datetime] ) ON [PRIMARY] GO...more >>

My classic ASP app uses SQL Server 2000. There are several tables, but one main one. The boss has asked me to do something to keep track of when changes are made to that one main table. I thought I was going to have to write a bunch of complicated code to make this happen, but then I remembered ...more >>

I need a query that can return wildcard matches if there's no exact match. There's how the process works. 1. Input = "my email address is kfjsk@fsdf.com" 2. Look for exact match. 3. Look for wildcard match. 4. Found = "my * address *" 5. output = "my * address *" I hope this makes sense, ...more >>

Hello, I have to perform multiple updates in one column in a table, i am trying to create an stored procedure that will automate this task. I have an entity column with nvarchar(20) and for each distinct entry i have an update. Any ideas would be very appreciated. thanks...more >>

Hi all, In what appears to be a solution to my sending out a few emails problem I've put the following together : DECLARE @SubjectName varchar(150), @SubscriberName varchar(50), @SubscriberEmailAddress varchar(255) DECLARE Subscription_Cursor CURSOR FOR SELECT SubjectN...more >>

Hi All, Trying to figure out how to get the total number of hours between 2 dates, excluding weekends... This is part of what I have: SELECT datediff(hour, StatusDate, getdate()) AS Hours Thanks in advance, R ...more >>

We have created a query to compare table structures of two databases including constraints, defaults, etc. it generates a file indictaing the differneces between the two databases. We are facing a issue in comparing defaults between the two databases. The default in the two databses are identi...more >>

Hello, I have a problem designing a query. I'm working on a material tracking system, and have a table linking surplus materials to storage location. The table describes a storage with 12 fixed positions where any 1 piece of material can be stored. The table LOG_SEQ_NO int Identity ...more >>

I have inherited a program written with access as the backend database, and I've been tasked with porting it over to SQL Server. I was hoping I would be able to import the data from access to SQL 2000 then change the properties of the data controls to connect to SQL 2000. However, there ...more >>

I have a problem where an update statement that runs on a linked server updates all the rows in a table. It is not correctly parsing the WHERE clause, or is ignoring it altogether. Here is the table definition for the server named EDDIE_SQL_DEV: USE Pubs GO if exists (select * f...more >>

Sorry... I posted a message hours ago and it is still not here, just submitting again to see if this works or not....more >>

I have a user who needs access to our production data to troubleshoot an application issue. We have created a database with views of the tables in question, but I'm concerned about the potential queries that could be run against the server and hose up other applications. I know there is th...more >>

Hi, I am facing a problem in writing a query in SQL2000, my query is very simple. I wanted to have a random numbers against of every row in a table. So that whenever I will execute a query I should get different result in every row in that randomNumber column. If you have any solution Please...more >>

Hello everyone, I need to create one table from the data from two other tables. I have the select which creates a view of the data in the format of the new table, but how could I now copy all these records into the new table from this select ? Thanks, Andrew. ...more >>

Hi, does anyone of you know a way, how to run a dynamic query longer than 8000 chars (varchar limitation) as a t-sql statement? I mean _real_ dynamic, not concatenation of constant number of varchars. I was trying to use a combination of #tmp_table, text data type, and SP, but w/o any su...more >>

Is there a log or record of what IP addresses have arrived at my SQL Server to get data? ...more >>

This is a code James Hokes posted in response to one of my questions. The code accomplishes what I am trying to do, but I don't quite understand the syntax. Can you please explain it to me? What does "THEN 1", "ELSE 0", and "END = 1" all mean in the code? My original question/code is poste...more >>

COMMON SELECT STATEMENTS - (moved to seperate stored procedure) ---------------------------------------------------- I have three procedures which use the same select statement to filter data. If I could move the search select statement to a common stored procedure then call the search ...more >>

How can I select all companies with the word 'boats' in the description and remove those records with duplicated company names except for the one with the highest Rank value and then order them by rank. Sample data Table A - (Company_ID - primarykey, CompanyName) 1104, Sunfish Company 1...more >>

Greetings, I'm trying to run a query from Query analyzer using For XML Explicit. The results are OK, I just can't figure out how to put the "root" tags. For example, select 1 as tag, null as parent, a.id as [a!1!a], a.name as [a!1!name!Element} from table1 for XML Explicit - Returns ...more >>

Hello, I have a stored procedure that is scheduled to run once a week on Sunday at 12:00:00 AM. The problem I am having is that it appears that the stored procedure runs twice. Once before 12 and once at 12. I have a similar problem with another stored procedure that runs once a week on M...more >>

I am building an app (C#) that will monitor SQL Server 2000 and make data inserts when the server is not under heavy usage. Does anyone have suggestions on Stored Procs or other methods to programmatically get SQL Server performance data? Thanks! Al G....more >>

Hi, Can i have "Jobs" on MSDE . If yes, then i want that jobs to be fired from SQL Server. Actually what i want is to have one SQL Server, and MSDE on all other machines. And the central SQL Server would take care of when a perticual job is to be exicuted. Can i do this. Will "...more >>

Can I add an alias to a dB? I've inherited code that joins table across dBs and uses db_name..table in the SQL code. The dB name in productiondifferent that the development environment and I'd like a simple solution to test it. Thanks in advance....more >>

I am having a consistent problem processing tables with more than a couple million records. Specifically, I have a table of phone numbers. This table contains a field for lookups on part of the phone number. I use a query to populate the lookup field as a substring of the phone number. (SUBSTR...more >>

Is there anyway though TSQL to find out the table schema of a stored procedure that has the output of a select statement of a table. I know the syscolumns will not have the result schema of the stored procedure. I know I can do this with opening an ADO recordset and call the stored proce...more >>

I have already some data in a table with a identity column, when I insert a row it inserts the next identity value, anybody has idea which system table stores the latest inserted identity value. TIA...more >>

Hello, I was asked to make some modifications to a current application. My customers want to make all the query fields "not case sensitive". The simple solutions seems to put UCASE & UPPER through the entire application, but doesn't this cause a serious performance hit? If so, is there ano...more >>

Developers have the ability to run the debugger but the Icons are gray-out for the debugger functionally Step Out, Step Into, Step Over, Restart, and Stop Debugging. Please help me resolve this issue. Thank You, Tim ...more >>

Hello, I have a data table with 4.5 million records (old data for RnD purposes). The table has one unique field and 11 fields total (varchar, smallint, int, smalldatetime) and no indexes. To copy all the data from the original table (tbl1) to another table of exact design takes between ...more >>

Hello Everyone, I have an employee table that contains the first name, middle initial, and last name. Right now, if the person has a middle initial it looks good..."John P. Goodman" but there are people that don't have middle initials..."Susan . Wilson". I have the query to select the right s...more >>

Hi, Can you give me some advices on how to create, backup, and restore a database with over 1 TeraByte. I would like to know more specifically how to build the database...like multiple filegroups, index, what to use for backup and restore. This is mainly a read only type of database with sel...more >>

Hi, Tried to use sp_OACreate in a stored procedure on SQL 2000 servers, see sample stored procedure below, to create a COM object of the ProgID 'MSXML2.FreeThreadedDOMDocument' FAILED with HRESULT=0x8004001 ("Not implemented"). More info: 1) The stored procedure works fine on SQL Server 7...more >>

Good day All, I am a newbie to this forums and a newbie to SQL server. I have an access database with loads of information and am wondering if it is possible to create a SQL server database from the access database - sort of a tool that reads the access database and creates a correspoding ...more >>

Hello, Someone knows why it is happen and how can fix it ??? I am just doing... ------------------ EXEC sp_attach_db @dbname = N'OldExtranet', @filename1 = N'D:\Data\Extranet\EXTRANET_Data.MDF', @filename2 = N'D:\Data\Extranet\EXTRANET_Log.LDF' Server: Msg 823, Level 24, S...more >>

Hi, Is there an easy way to convert a number representing a month to the actual month name. Any help would be gratefully appreciated. Kevin. *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it!...more >>

Hi, I need to be able to pass data from one database (say called DBCapture1) running in one instance of sqlserver (say called DBServer1) to another another database (say called CompanyDB) running in another instance of sqlserver (say called DBServer2). What's the least troublesome way of doing...more >>

i have to take the query in SQL server in mysql run mysql_query('set @attivita:=0'); mysql_query('set @incremento:=0'); mysql_query('select giorno ,matricola,if(fid_attivita! =@attivita,@attivita:=fid_attivita,@attivita) as attivita ,min(cinquina) as da ,max(cinquina) as a from pres group b...more >>

Hello, i'm trying to find SQLServers all SQL-Servers in a given IP subnet within a program. searching google i found a piece of code which send a broadcast upd packet to the default port of sqlserver installations. i'm running a msde2000 installation to test this program, but i got no response...more >>