Hi all, Are there any regular maintenance that should be done on a database on a regular basis, someone asked me if I "re-indexed" the database regulary. Thanks, Lasse Using MS SQL 2000 ...more >>

When I use con.Close() where con is my connection to the sql server, it closes the connection, but how come I cannot drop the database? It keeps telling me that the database is still in use, while it isn't. rg, Eric ...more >>

I'm a SQL Server DBA and more skilled with SQL Server than any other database platform. I wish Microsoft all the best with their product, but I feel that the IDC study they have posted at http://www.microsoft.com/sql/evaluation/compare/Linuxtco.asp is a joke. First, they don't name the Linux RDBMS...more >>

MY DATA NO QTY 5 25 7 22 3 17 8 15 1 13 6 9 2 8 4 6 OUTPUT .... NO QTY GROUP 5 25 A 7 22 B 3 17 A 8 15 B 1 13 B 6 9 A ...more >>

Hi, I wrote a java program to get a date in String format and would like to insert it into a date datatype in SQL server. Have someone know if there is a SQL server's function to convert the string into date? sql = "insert into xxx values ('" + dateString + "' )"; sqlSession.execut...more >>

Hello all... I am really confused. First of all I am inside an active transaction in my stored procedure. The value of @@trancount is 1. Now I am updating a record in a table that has an update trigger. So when I am entering the trigger, internally another transaction is created. Now if the trig...more >>

MY DATA EMPNO QTY 2 100 7 99 3 90 5 90 4 85 1 80 8 75 6 50 HOW TO OUTPUT MIN TWO GROUP BY SQL PROCEDURE ... THANKS!! ...more >>

Hi, Although my application perform nicely in wired network, the performance in wireless network is very bad (I have tested in 802.11b). Would appreciate if anyone could suggest how I can improve the performance in the wireless network. Any one has tried running application in 802.11b vs 802.11g? ...more >>

I want to trace some values in triggers. ...more >>

Is there any system stored procedure in sql server 2000 to get maximum acceptable numeric values of numeric data types something like this. Data Type Maximum Acceptable Value Bigint 9,223,372,036,854,775,808 Int 2,147,483,648 Tinyint 255 I tried using sp_d...more >>

Hello, Does anybody know if it is possible to create a scalar user-defined function without input params? Thank you! Daniel Walzenbach ...more >>

The simple script got errors 16957 at the declare cursor part while execution. I tried all SQL-92 and Transact-SQL Extended Syntax but all in vain. Removing the "FOR UPDATE OF col2" will cause errors on "UPDATE tbl SET col2 = @SeqNo WHERE CURRENT OF MerCursor". Thanks in advance. ==> ...more >>

How can I insert a record to a far linkserver (sql) to a table with identity coloum. when I try to run set identity_insert on the far db I get an error "The object name 'SERV.db_V1.DBO.' contains more than the maximum number of prefixes. The maximum is 2." I tried with the OPENQUERY and faild....more >>

This simple script will compile fine but not execute the alter table statement. What is wrong with this? IF (SELECT [Version] FROM [Preferences]) < '5.10a' BEGIN /* Invoice table */ ALTER TABLE dbo.Invoice ADD ShipperPalletsIn int NULL, ShipperPalletsOut int NULL, ShipperPalletsRec b...more >>

I have an identity column in a table.Now I want to remove the Idenity property of the column and make it simple Numeric column through T-SQL Script. Could anybode tell How? Thanking You Toeen ...more >>

Hi, How do I list all indexes with type 'Index' and index columns on a specific table. I have tried something like: SELECT * from SysIndexes where Id = object_id('CONTACTTYPE') for the indexes, but I also receive 'Unique' and 'Primary key'. I would like only to receive indexes with type 'I...more >>

How do I 'set default' to a different database, since we cannot use USE statement in an SP ? Is there any system stored procedure ?...more >>

Hi there. I was disucssing with a colleague of mine about the usage of stored procs vs dynamic SQL statements. His argument was that setting up a stored procedure call in one of our .NET apps seemed like overkill, since all that would be returned is 1 record. Using dynamic SQL would be a lo...more >>

I am having trouble figuring out how to allow quoted identifiers using the BULK INSRET command. I've browsed google and msdn but haven't seen anything helpful. Please help :) Eric ********source file - bdrecords.txt:******* "my, name", "my,Address" "your ,name", "your, address" ****...more >>

When calling QueryValue, SQL 2000 Enterprise seems to return 4, where the docs seem to say that 4 is MSDE. Any ideas about this? Thanks. Neil ...more >>

Below is 3 queries. The first query is just to show, that the view 'Availableitems' returns what is expected and especially to verify that the 'Available' column is actually 0. The ItemID is the same (340613) in all three cases. The next two queries are identical except for the first, which...more >>

I am trying to write a stored procedure that picks the next row out of a work queue table, changes it's status, and assigns it to an agent. Multiple agents can call the stored proc so I need to prevent two agents from grabbing the same row. I've implemented a proc (see the repro scripts below) us...more >>

I'm working on getting some other software to output a smalldatetime into Sql. This software is capable of outputting a string. It seems that if I just have a date, or if I have a date and a time with a space between them, SQL will accept the date, but will chop off the time. If I try adding sing...more >>

I need to be able to create new company databases programmically. Most companies have the same objects (tables, views, sprocs, etc.) One option I looked at was creating all the common objects in the Model database. Unfortunately this won't work because I also create other databases on the ...more >>

Is there a way to change an integer column into an IDENTITY column without having to build a new table and reload the data? Thanks, Wayne ...more >>

I am trying to catch an error thrown by a unique constraint on a table. I have written an sp that will copy records from one table to another. This sp is called once a bunch of records have been deleted and there are less than 10 records left (trigger). The problem is that the sp will copy all of...more >>

Is it possible to prompt for variables in a sql script, for example what database to use, what values to update. Something along the lines of Update <TABLENAME> SET <FIELD> = <PROMPTUSER> ...more >>

Hi I am trying to replace a select statement I have written to Identify specific records within a table with an update statement to modify the identified records. I am not having alot of success. The query is as follows select t1.eqnum, t2.eqnum , t1.classattribute, t2.classattribute ...more >>

hi, I have a couple of variables of table datatype which hold about 8 to 10 records each.There are a few updates and selects on these variables. Now my question is whether it will be fine to add primary keys to these variables? I tried adding primary keys to these variables and found the relativ...more >>

I can't see it being that hard... ---------------- Update Quotes Set Updated = 1 From WorkLog Where Quotes.QuoteID = worklog.quoteID and worklog.StepNum = 1 And Not Quotes.QuoteID is null ---------------------- Basically I'm just trying to set a bit based on whether or not this other t...more >>

I know this is easy and I am fairly certain I have done it before but for the life of me I cannot get it to work. How do I implement a count of similar rows resetting the count when the value changes...i.e. Row count ID --------- ----- 1 16 2 16 3 1...more >>

I have a view which meets all the BOL specified requirements for an updateable view. The base tables have the check constraint. I can do multiple inserts but the performance is terrible. I insert many rows and the CPU and read counts are good. Many inserts get high read counts and high...more >>

I have developed a project for a company that stores scanned images for web access. The table is quickly growing to fill the 1/2 terabyte sqlserver2000 server (running on win2k server) After researching my options, I am questioning simply truncating old records. The way the binary pic data is st...more >>

I have a TEST field datatype with 1 character as width. The values in the field are c, C, h, & H. However, when I run the following query, I get the following results: QUERY: ====== select distinct piph.CurrentOrHistory, count(Distinct piph.CustID) from tblxxx piph group By piph...more >>

Hi! I have a query which consistently locks up when I run it: The problem occured, since I entered the marked (>>) part into it. Without it, it runs fine. CREATE VIEW dbo.XXOSPReport AS SELECT dbo.JobReference.Reference AS XXReference, dbo.XXOSPDates.ActualStartDateTime AS OSPActua...more >>

Hi, I have a strange issue with Sql server 6.5. I have an applicaiton running in VB updates the tables. Recently I have dropped and re-created few tables with column changes. Now I am able to update the table through ISQL but not through the VB app. Can someone please shed light on thi...more >>

Hi all, What is the latest service pack for SQL Server 2000. Can somebody give me a link where I can download it? Thanks... -Nikhil ...more >>

I'm interested in how the optimizer in Sql Server handles code in User Defined Functions. I have a number of UDF's that include complex select statements and return a table with data. The UDF's is then used from a number of stored procedures. Does anyone have information about how this affe...more >>

I have the following tables create table midlet midlet_id integer spec intege create table compatibility device integer fk_midlet_id integer I have the following data insert into midlet(midlet_id,spec) values(1,1 insert into midlet(midlet_id,spec) values(2,0 insert into midlet(midlet...more >>

I get the above error message when I query an odbc FoxPro table from sql server 2000 sp3. The query seems to run ok except when I use UNION or DISTINCT. Any ideas? This seemed to work fine under sql7. Thanks, John ...more >>

I am invoking EXECUTE(@string) to execute dynamic SQL from within a Stored Procedure. I cannot find in any documentation how I can determine if the dynamic SQL succeeded or failed How can I do this?...more >>

I have a table and would like to query using the Group By clause. Some rows have the same SerialNumber field but will always have a different TestNumber field. I just want to select the row with the highest TestNumber, heres a sample of the data that I would like to query SerialNumber T...more >>

I've got an odd question .. I have an application with an Access 2002 front end with linked tables to a SQL 2000 backend. Users have always been able to edit the SQL tables via access linked tables/forms with no problem. I have now created an audit table and using triggers to register eac...more >>

any affect in query speed if i added "where 1=1" in the sql statement? ...more >>

Hi: I like to format number in sql. e.g. display 0.24 as +.24 i.e. remove leading 0 and show + or - sign. Thank you for help. Shailesh ...more >>

Hi all It seesm that we have reached a sql limitation with a complex query. Any one have any ideas how to resolve this is sql 2000? Greatly apprecoated. Sample query Select SectionID, Section, Description, QuestionID, Question, DisplayType, Designation, Orientation, ResponseID, 0 As NoOf...more >>

Is there a way to force the results of a print statement to immediately be sent to the client? It seems like it waits for a buffer to get full before it does the send. Thanks Tom G. ...more >>

i have a procedure that does a select on one table - if that select comes up empty, then i want to select from a different table. this works fine, but the problem is that i need to return the values as a recordset for my ASP pages. so naturally, rather than getting one recordset i get ...more >>

When a SQL Server job fails, a white X within a red circle is the icon displayed next to the job in Enterprise Manager. After the errors causing code had been corrected, how do i reset the icon to one indicating no errors....more >>

Hello, all. Was wondering if I can move a diagram I've created in Database A to Database B, which is on the same SQL SERVER....with different db names of course. I'm running SQL 2K. If this possible, please let me know Tia Roz ...more >>

I am trying to remove rows in a table, using a schedule job, without logging to the Transaction log file. Previously, I was using the "delete from" statement to delete rows that were older than a day, however, the transaction log was getting too large. Is there a statement that would work in this...more >>

H Is there an easy way of duplicating a row in a table without manually doing every column I trie INSERT INTO t_task SELECT FROM t_tasks WHER id = but this fails as there is an identity column and obviously I want the new row to have a new identity Ada ...more >>

Hi, I'm looking for a script to list names of all the columns in my DB, together with the name of the table they belong to, and whether or not they have Primary Key or Foreign Key status. I found lots of scripts in the archives of this group which do the first part already, but nothing that...more >>

What is the definitive answer on using nolock hints in select queries? Does sql 2000 ignore the hint and process the query as it sees fit? Is it good programming practice to use nolock hints? I am a developer working on an application that must perform Insert, Update, and Delete operations on the...more >>

I have a function which converts a hex string to a bigint. See below. I have run across a few which are too big to fit into a bigint so I'm getting an arithmetic overflow error. Does anyone know any way to do this? An example hex string that is causing this problems is '0x800000000000000000'...more >>

I am very new to monitoring server performance. I am familiar with setting up profiler or system monitor but my main problem is what to track. I have tracked a lot of stuff but then don't really know how to interpret it. For example- I have an intranet site set up and users access all r...more >>

Hi, I have a Web-Based application running against a SQLServer 2000 backend. Some of the queries are very complicated / large / in loops which get repeated a lot and I'm looking at ways of optimising them. I was wondering if anyone could offer some advice on the following:- 1) Is a u...more >>

Hi All I updated few records in a table with a update command. How do I rollback and get the previous data. Please Help Thank JP....more >>

Is there a way to get the following information for each table in a databas Table Nam Field Nam Field Typ Field Lengt Allow Nulls ( Y or N Indexed ( Y or N )...more >>

I have a sql server database with sps,populated tables ,indexes and so on. I need to move the lot to a MSDE database. Could somebody give some suggestions or script or tool to do it. Thanks vbnetuk *** Sent via Developersdex http://www.developersdex.com *** Don't just partici...more >>

I have two SQL2000 tables which will be published using merge replication, I need a rowguid columns. I have heard that using a rowguid column with an index will affect insert/update performance. I wish to create a design that avoids lengthy insert/updates, if possible. I am using ADO.net and C#...more >>

Hi! I have to copy a sql server database (tables, views, stored procedures, ... in a word ALL the database). I tryed to use the SQLDMO "transfer" method, but I had some troubles, pricipally it takes a lot of time (a VERY lot of time), but sometimes it don't copy the database (actually, I don't ...more >>

Hi, I'm want to place a check constraint on a field to make sure that the value entered in the field is either null or if not then the value is greater or equal to 1 (i.e. not 0). The field is of type int and allow nulls is true. I've not used constraints before and am not sure of the syntax....more >>

Hi Can anybody help me? I need to execute following vb script using sql agent job: dim myRequest set myRequest = CreateObject("Microsoft.XMLHTTP") .. . . . . . . . . . . . . set myRequest = nothing but it fails. same time I'm executing this script from command lines. All user per...more >>