I've been working on a stored procedure doing lots of little changes trying to get that little bit more speed. I've been making the change and then running the stored procedure twice, as I understood that the first time it is slower as it needs to spend more time "compiling it". However ...more >>

Hi all, I have an asp page that writes to an ntext field in SQL Server 2000. All was going well until I had to put in a section of text greater than 8000 bytes. Then I got a timeout error and the update wouldn't go through. After reading that 2000 only accepts chunks of 8000 bytes or under at...more >>

Hi, I have two clients that run my VB app and both connect to one sql server. I want to display new records in the client as soon as they're inserted from another client. What's the best solution to this need? I am thinking of using DCOM but I'm not sure that whether this is the best option. A...more >>

Dear Experts, Case description: At the local server (Server A), I have created a stored procedure (SP) to get the data from 2 oversea servers (Server B and C) and insert the result data in a table of Server A; So I create a scheduled daily job to run that SP, but I found that it failed on...more >>

Hi, I have got a performance problem with one of my SQL 2000 DB table. The table stores invoice details. The query uses "Like '%Invoice Number%'" to retrieve all the matching invoices from the table. As it uses wildcard, no matter how I index the table it will always do a table scan. The re...more >>

I have two stored proecedures that i wish to have NEVER run at the same time to avoid loosing all my data: sp_archive and sp_restore if they called together at the same exact time there is a possible race condition that would result in the entire db being whiped out. e.g. archive and restore bot...more >>

I am trying to bcp out the resultset from a stored procedure to a file. This sp is a report which builds a result set into a temporary table and then returns the contents of this table as the result set of the sp. When I specify this sp as the source for bcp, i get an error stating that the t...more >>

Here's the table: CREATE TABLE Item ( GalleryID int NOT NULL , ItemID INT NOT NULL PRIMARY KEY , ImageThumb VARCHAR(255) ) GO What I want is to return the top 2 ImageThumbs for each distinct GalleryID. I.e. GalID ItemID ImageThumb 1 10 ... 1 11 ...more >>

I have a set of data that has two fields, LowNumber and HighNumber. In most cases, LowNumber and HighNumber can readily be converted to int values. However, some low number and high numbers are varchar data. for example, the low number might be 0000001000 and the high number might be 00000019...more >>

I am trying to use bcp to output a text file tab delimited. I have that down, but I am missing the headers. What is the trick to get the headers to export? Laurel ...more >>

I am using DTS to import csv files that I receive from a third party. Each day I receive many file which I import into many different SQL Server tables depending on the name of the file. I am doing this as part of a larger automation process written in C#. Due to the csv file names differing ...more >>

I'm not sure if this is the correct place for this msg.. I'm a bit new to SQL server and here's what I have: I've created a stored procedure in SQL Server 2000 which has a parameter @Table sysname. The sp calls a scalar user defined function and passes it the @Table variable. The function...more >>

I have trouble getiing the results from calling procedure using sp_executesql tor return an error. To demo that I am testing with this procedure: CREATE PROCEDURE test AS begin raiserror 30001 'Can not run p_process_Guaranteed_COMM after final commissions have been paid.' return ...more >>

Hi, Hi, I have 3 tables: Offer ---------- offerid(PK) userid(FK to Users) estateid(FK to Estates) price rooms Estate ----------- estateid(PK) city phone addr date EstateType Search --------------- searchid(PK) userid(FK to Users) estateid(FKto Estates) ...more >>

I've got two things I'd like to know. First is how to represent a FileSystem (top node is a directory, child nodes are either a file or directory) as a single SQL table. The second is a better way to do the following: If you have an optional parameter that may affect your select statement, I...more >>

Hi, I'm using a parameterized ASP.NET query to insert textbox data into a SQL 2000 database (into text and varchar fields), via a stored procedure. The problem is that some characters (curly quotes & apostrophes, trademark & copyright symbols, etc.) are being dropped on insert. The remain...more >>

We are trying to bulk insert a file residing on a network share not on the SQL Server machine. We gave read-only permission to Everyone on the share and the folder. We get back an error telling us SQL Server does not have permission to access the file (error 5). Here is essentially the bulk i...more >>

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. The statement has been terminated. I have select statement: select SUM(i.moves) as dd from input i, moves m where i.inbound in (select ter...more >>

i lookes at aspfaq.com but i didnt understant about this method.. please more information.. or more source.. i have 6 records and i take 2 parameters into my stored p. the first parameter is how many records i will take from the top 1-aa 2-bb 3-cc 4-dd 5-ee 6-ff the second par...more >>

Hi. I'm trying to build the delete query from a database. The problem is that the item I try to delete has a column that is the foreign key of another table and it doesn't allow me to do that. Why not? I don't want to delete the items from the other table where it's the foreing key. Regar...more >>

Hello, i am wondering... Say i have an INSTEAD OF INSERT trigger on a table. I want to use this trigger to simply modify the value of one of the fields before it is written to the db. Is there any way to modify just the one field, and write them all back in one shot without specifying each ...more >>

Hi Guyz, I need some help with this query: I have a table with those columns: ------------------------------------- ID | PageName | Hits ------------------------------------- I need to get the 5 records which have the maximum hits value. The table is huge, so I cannot sort it ...more >>

Does anybody have any recommendations of good training courses available in the eastern NY, northern NJ, western CT areas? I'm a DBA looking to take a High performance database Tuning class. I would really prefer one with an intructor with real world experience over those who follow a standard...more >>

I have a scheduled job that has shown its status as "Performing Completion Actions" for several days now and from as far as I can tell, isn't holding any locks, but also hasn't run in two weeks. Does anyone have any ideas about this? Thanks, Robert *** Sent via Developersdex http://www...more >>

I'm trying to create temporary tables from VBA in MSProject 2003 to SQL Server 2000. The connection is fine, however I can't create temporary tables, or views, If I create a table I can use it, but I can't drop it. Here are my statements query = "Create table @LDPull (ldkey int)" dbODB...more >>

SQL Server 2000 on XP Pro I do a net start mssqlserver and it's using 21,316K of memory (Task Manager's Process List). In Query Analyzer, I do this: DECLARE @start INT DECLARE @finish INT SET @start = 0 SET @finish = 200000 SELECT AlternateCompanyLocationKey As AlternateKey, A...more >>

Hi All, In a function, can we read data from an external source (like text or binary file)? Thank you Paul...more >>

Hi, I need to use CURSOR from a table (50 fields) What I want to do is to declare a CURSOR and then use the selected record, fields by fields (an of course row by row) I know I can use INTO instruction but as I've got many many fields I wonder if I can use something different like : -- R...more >>

i have 6 records and i take 2 parameters into my stored p. the first parameter is how many records i will take from the top 1-aa 2-bb 3-cc 4-dd 5-ee 6-ff the second parameter must do that the first resultset which i have taken after first step order by desc and after that from to...more >>

How to get date from a table that is on the other SQL server? I need to write an SQL query. Thank you ...more >>

Hi, Am trying runnning SQL Server 2000 with SP3 and VC++ 6.0. I am trying to compile the sample program "bcp" from the odbc directory. I am getting a link error when I compile. It tells me that "bcp_init" and "bcp_exec" are unresolved external references. I have odbcbcp.lib in my "Lin...more >>

Hi all, Would help me put together a function or a SP to return a previous business date if I pass in a date. The function should be smart enough to ignore weekends, holidays and return the previous business day. Thanks in advance! tj...more >>

I have a tables in a sql database that has a field called arrivaldate. Is there a way to have sql email me when the arrivaldate is 7 days before the actual date? Thanks in advance for your help. ...more >>

Hi all, I have a web page where I could do with returning about 6 different counts, what I am trying to avoid is having 6 different sql statements to produce one value each. As an example, I have a table with user access levels in, you are either an administrator (access level 1) or an auth...more >>

I have table time where I have column for second,minute and hour. For example: hour minute second ------------------------------ 9 5 3 If I execute: SELECT cast(hour as varchar(2))+':'+cast(minute as varchar(2))+':'+cast(second as varchar(2)) as time F...more >>

In query analyzer I can use information_schema and sp_spaceused to get information on a table. How can I loop through all (user) tables in a database to return information. TIA -- Alphonse Giambrone Email: a-giam at customdatasolutions dot us ...more >>

How can you apply the parameters to datepart so that it only outputs workdays (Mon. - Fri.)? Thank You!!! Tan...more >>

Dear All, I'm looking for an equivalence to Instr from VBsript and not found any. In Vb script Instr function is used to locate the position of a char in a string. How would you do it in SQL server? The reason I want to do so because I want to add an extra quote in case the values to be ins...more >>

Hi all. SQL 2k (deved) I got a little problem which I hope you can help me with. I'm trying to do my own forum system. The problem I've hit is getting whether a topic has been read by the member or not. I've got the following in mind. Each member table has 2 columns (lastvisit, currentvi...more >>

Hello all, Does anybody know how to identify records by a field that contains alphabets or special characters? I have a ZIP Code field, which unfortunately will continue being a free-text field for sometime, where the STATE, or random characters are usually entered due to data-entry ...more >>

I've a table like below table1 col1 col2 a 1 a 1 b 2 b 3 c 4 c 5 d 6 d 6 above table has two duplicate rows on combination of col1 + col2, for ex: rows 1 and 2 are duplicate, rows 7 and 8 are duplicates how to wrie an update sql stmt to get belo...more >>

Hi, I have a query that is based on two tables: Table1 contains string values, Table 2 contains a selection of keywords that I want to filter Table1 with. I have it working in Access like this (replacing % with * and + with &) SELECT Table1.String FROM Table1 LEFT JOIN Table2 ON Tabl...more >>

Hi - I'm have a VB.NET Windows application that uses an MSDE database. I'm trying to code backup, restore, and checkdb/repair functions. Before each of these operations, I change the connection database to "master" and then execute an "ALTER DATABASE SC01 SET SINGLE_USER" statement. Very ...more >>