I have written a stored procedure to list out all tables in which rows or the table itself is locked. The only information I am not able to get is the time when the lock occurred. The way I want is that if I run the procedure it should show all locks on a table which are at least 5 or x seconds ...more >>

I have a field in my database which stores product codes. This field is a varchar. Some users use characters in their codes but most just use numbers. Whenever I go to sort the products by code the order of the numbers go something like: 1, 100, 101, 2, 210, 220, 2500, 3, ... I understand why...more >>

Hi, The following script does not return any resultset against a test db while I know for a fact tables with letter "aaa" has columns that contains "ccc". What's wrong? the the inner cursor? Thanks. -- get all tbls with letter aaa declare @tbl varchar(8000) declare tblCursor cursor f...more >>

Hi Folks, I am a newbie when it comes to SQL Server 2000 and have a Sybase background. How can I reset my sa password in MSSQL like we do it in the runserver file(-p) in sybase??? Also, can I configure replication using the standard edition of MSSQL server???? Thanks Regards dba_sybase2...more >>

Hi does anyone know how to make MS SQL server 2000 calculate the right week number around new year ? According to the help file, MSSQL should follow ISO8601, which ensures that i.e. 2003 has only 52 weeks. but these two prove it wrong: Select datepart(ww,'2003/12/31') --(wednesday -> retu...more >>

Hi I have a table that contains information that has start dates and end dates. They are stored in short date format. I have built a web page that initially returned all the information. I then want to return information spcific to todays date, where I used dim todDate todDate = now t...more >>

Hi, Don't worry about the vars, they are defined, the following line give me an err of "Incorrect syntax near '.'." Goal: to rename nonstardard column name. EXEC sp_rename '+@tbuffer+'.['+@cbuffer+']','+Replace(+@cbuffer+','%[^A-Za-z0-9_#$@]%','')', 'COLUMN'; Thanks....more >>

I am trying to upsize a database to SQL server (on which I am a novice). In Access as part of a much more complex query I had the following (from sql view) SELECT IIf(InStr([ItemName],"*")>0,Left([ItemName],InStr([ItemName],"*")-1),[ItemNa me]) AS ShortName FROM corp_info Which gives a ret...more >>

hi I just gotta know: what is the database that sql server creates/works with? Can it be used in an oracle environment? Or can it be uploaded to a Oracle db? (like access thru odbec) thanks!!jim...more >>

Hello, I've noticed on my DTS that it is connecting to the remote SQL Server via Named Pipes. Not sure why I've been getting the following error: [DBNETLIB][ConnectionWrite (WrapperWrite()).] but I wanted to change the DTS from using Named Pipes to use TCP/IP. How to do it? Also, if anyone ...more >>

I am attempting to export data on a daily basis via DTS to an XL spreadsheet. I would either like to: a) have a separate worksheet in the spreadsheet for each export or b) a completely different spreadsheet for each export. Whenever I attempt to use a variable as the name of my table in the DTS ...more >>

tentaively, got: create #table(a,b,c,d) insert into #table select A,B,C,D from s,t,x,y,z where y.price > 20 select *, (select max(D) from (select distinct top 2 with ties from #temp v where v.B=W.B and v.C = W.c), (select min(D) from (select distinct top 2 with ties from #temp v where ...more >>

Hi. I have a IIS server with php3 installed. I have SQL server database and data stored in unicode format (nvarchar fields). In header of my php I have the meta: <meta HTTP-EQUIV="content-type" CONTENT="text/html; charset=UTF-8">. I have the following problem: - I trie to do a query usin...more >>

Which would be a more useful relational database server to learn nowadays: MS SQL SERVER or ORACLE? Thanks!...more >>

We get the following error message. "a floating point exception occured in the user process. current transaction is cancelled". this message comes when trying to excute a stored procedure. This exception is unpredictable. OS : Windows 2000 (SP3) Version: SQL server 2000 (SP3)....more >>

I have imported data from excel file. When data came to SQL table, the type of AMOUNT column was varchar. I tried to convert and cast amount type of amount column to number type but it does not allow me to convert. What is the best way of importing data into SQL and type stays the same as ...more >>

I've been doing some experiments with speeding up copying tables of approximately 1 million rows between databases using BCP and BULK INSERT. I noticed that the total time for removing the indexes (non-clustered) and then recreating them after the BULK INSERT was significantly less than just d...more >>

Hi The maximum length of a nvarchar could be 4000 characters while that of varchar could be 8000. We are trying to use unicode which would require that the datatype for one our fields be converted from varchar to nvarchar. But looks like this would result in loss of existing data. Is t...more >>

Hi Is there a sp command I can issue which will show me what the "sort order" etc for my MSsql server. I need to re-install it, but need to record what custom setting it has if any before I wipe it. I have to reinstall to reconfigure the RAID arry under it Thanks ...more >>

Hi Some days ago I posted a problem with a query. Thaks to Dave and John, I got a little closer to a solution. Their suggestion was a solution to my problem as I stated it, but the real problem is a bit more complicated, so I have to expand the original problem a bit. The query has to handle u...more >>

Suppose that I have the basic db structure as follow: imgnum varchar(16) description text imagecode varchar(16) note text photographer varchar(16) date date location varchar(50) The <imgnum> format is as follows(and its unique): J...more >>

Hi there; I was trying to apply SQL SP3a on my windows 2000 server (SP4), but the installation failed and when I rebooted my server the SQL agent failed to start and I get the following error: SQLAGENT.exe - Entry Point Not Found the procedure entry point SFMapi0GetProfiles could not be locat...more >>

Hi There are a lot of articles about the fillfactor. I did change the fill factor and that did not work as intended. How do I get back to the default fill factor I am using sp_msforeachtable undocumented database procedure and when the indexes are rebuilt the fill factor that shows up in or...more >>

We have a production database, but due to the crippling load of some reporting tools, we are looking to have a separate reporting database. Due to it's nature, the reporting database doesn't need to be as up to date as the master, perhaps it could be refreshed every evening? What I wish to kn...more >>

Hi all, I am doing a project that involved with the big volume of data to be uploaded to the SQL server. Due to the limitation at my employer's site, I couldn't have the bulk admin right. I am looking at the command BCP and wonder if there is any copyright issues if I were to use BCP and to ...more >>

Hi, I have writtem the query.... select sum( case when shmsharect >= 10000 then 1 else 0 end ) as cnt_big , sum( case when shmsharect >= 10000 then shmsharect else 0 end ) as shares_big , sum( case when shmsharect < 10000 then 1 else 0 end ) as cnt_small , sum( case when sh...more >>

table(name,age) need to find top 2 that are distinct by age. error check: if two names are the same go to next top age). table(name,age) A 17 A 17 <<error B 14 C 16 D 16 got a table like that and select distinct top 2 age from table, returns: A 17 A 17 -- ...more >>

I want to generate a resultset that is just a series of numbers in ascending order or perhaps a series of dates.. What I mean is, is there a way to generate a temporary table of dates given an input of a start date and an end date.. This table would then contain an entry for each date in ascendi...more >>

#Table(class, name,exam,score) A name1 math 100 A name1 math 88 A name1 Phy 98 A name1 Chm 98 A name1 SPT 89 A name1 math 23 A name1 math 45 A name2 math 54 A name2 math 79 A name2 Phy 79 A name2 Chm 44 A name2 SPT 34 A name2 math 45 A name2 math 65 B...more >>

Hi all Im relatively new to using stored procedures and im not sure if it is possible to do what I am trying to do so any help here is greatly appreciated. I am using the variable @MachineName which is obviously the local machine name mainly in this procedure. What is loop through from the fi...more >>

How do I select the First Record only based on MemberID The layout is PhotoID,MemberID,Title 1, 666, blah1 2, 555, blah2 3, 666, blah3 4, 777, blah4 6, 777, blah5 7, 999, blah6 8, 444, blah7 So the result Im after is 1, 666, blah1 2, 555, blah2 4, 777, blah4 ...more >>

In the code below, the statement 'Print @Sites' prints nothing, even though the cursor contains 4 records, and 'Print @Site' prints the appropriate values. Can anyone see my mistake? I am attempting to print a delimited string of the values referred to by @Sites. Thanks. Dan Fisherman ...more >>

Hi All Can we connect remotely using SQL Server Enterprise Manager from our home PCs using a VPN connection, across the Internet, to an instance of SQL Server(or a database on it) that resides on an ISP's Server? Can this be done using a VPN IP connection? I believe a static IP is required fo...more >>

I work for an organisation that uses a bespoke document imaging system, the database of which is an MS sql server. We have MS Access and already use it for some querying of the database. The database comprises a large number of distinct cases, which are differentiated by case reference numbers, ...more >>

This doesn't work because the first INSERT is creating multiple records for multiple projects. @@IDENTITY, then, contains the Identity column value for the last tblWeekReportedLine record inserted. Consequently, all the hours records are then associated with that last value. The source wor...more >>

I uploaded some data about 2 or 3 times and it keep appending it to the table. Now I want to keep only first duplicate and delete rest of. Suppose part number 123 has been added 3 times so I want to keep only 1 record. Thanks ...more >>

Hi!! I`m looking for SQL server but somekind of OEM version. The program witch I`m working use the MSDE Desktop engine (5 user version) and I want to use it at 12 user. The SQL 2000 server is expensive for me and I`m looking for something cheap, becouse I use only a SQL database engine. Thanks ...more >>

Hi! I have a problem with a query: Two tables: CREATE TABLE Emp (empno INT, depno INT) CREATE TABLE Work (empno INT, depno INT, date DATETIME) I want a list of all employees that belongs to a department (from Emp table), together with ("union") all employeees WORKING on that department a sp...more >>

I have two tables with a 1-many relationship. I want to write a select statement that looks in the table w/many records and compares it to the records in the primary table to see if there are any records that do not match based on a certain field. Here is how my tables are setup: Task Code...more >>

the relations are MEMBERSHIP(membership_number (pk), cover_type(pk), start_date(pk), end_date) PERSON(person_number(pk), surname, initials, street_address, suburb, , membership_number) DOCTOR(doctor_number(pk), surname, initials, street_address, suburb) PROVIDED-SERVICE(person_nu...more >>

Hello! I have an MS SQL-server with an database, that runs replication. In this database there is an table with an column i want to extend; varchar(50)->varchar(60). But I get this error (using design window of Enterprise Manager): Cannot drop the table 'MytableName' because it is being used ...more >>

Hello, I have created the following trigger: CREATE TRIGGER tr_UDFTest ON UserDefinedFields FOR INSERT, UPDATE AS DECLARE @foobar varchar(100) SELECT @foobar= foobar FROM inserted IF ( @foobar = 'foobar') INSERT INTO LogTable (LogText) values ('Found foobar') ELSE INSERT INTO LogTab...more >>

Hi, the following query returns select shmsharect as shares, shmctryres from shrsharemaster where shmctryinc = '' shares shmctryres 0 SGL 0 MY 0 SGL 10000 MY 27000 SGL 0 ID 0 MY 5000 MY 0 ID 0 JP 3000 CN 1000 CN 12000 CN 0 IN I have changed the query to sele...more >>

I have a stored procedure which copies data from a view into a temporary table (x2) and then from the temporary table into a table which the users use. It takes 1 minute to get the data into the temp table and seconds to update into the final one (hence the two stages). When I do the initial c...more >>

We have a situation that occurs every so often with blocking of various databases on one server (Win200 SQL7). It appears to happen at random, so I'm assuming it originates from something a user does and not a regularily run process. We've examined the data available to us and used the very he...more >>

Hi, I'm using the JDBC API's to access a MS-SQL 2000 DB.I have a manual-commit transaction block involving multiple inserts and updates. One of the step involves retrieving the auto generated key (Identity column) which, I presume can be done with @@IDENTITY. My Question is: Will @@IDENTITY ...more >>

Hello, I have wrote a stored procedure but its real slow. Its activated by a button on web page but its takes to long to process and the web server gives a timeout message after 5 minutes. Is there anyway to speed up this stored procedure? What am I doing wrong here? --------------------...more >>

Hi Folks, My basic requirement is I want to write a trigger on a table based on certain conditions post-update from another table in another database. The actors in this scenario are: Database D1, Table T1 Database D2, Table T2, T3 Action: T1 updates T2 Requirement: As soon as T2 is...more >>

hello everyone. i am new to the group so i aplogize if this question has been answered recently... our sql db machine is too old. we bought a new server with which to replace it. the trick is, we want to keep the name of the machine and db instance the same. obviously, we don't plan on h...more >>

I'm trying to make a view that uses organization name from one table and contact first and last name from another table. In the view I have a field that I want to show Organization followed by the main contact. Problem is if the organization field or name field is NULL then it doesn't show any...more >>

Hi. I am searching for a reference with all error numbers an descriptions for ms sql odbc. Has anybody something for me? Thanks a lot. Greeting Jan Entzminger ...more >>

Hi, Version Info: SQLSERVER 2000 SP3 I am trying to understand how SQL Server works with SERIALIZABLE read. I am fairly new to SQL Server, having mainly worked with Informix. We are getting lock timeout error in our application. Our lock timeout is set to 20 seconds. I am trying to investig...more >>

Is there a type of TRIGGER that I can use to do do post-INSERT processing? I've got one table that I never want to block the INSERT, but instead need to do post INSERT processing on. The only way I know to do this is via a JOB, which isn't ideal in this scenerio. mas...more >>

Hi, I have 2 tables: tblStations StationID Station tblStationUser RecordID UserName Station I'm trying to come up with a dataset that contains the tblStations.Station EXCEPT for where that Station exists in tblStationUser where the U...more >>

Hi there... How do I get to extract info like, current Database logged in user or general stats like, ram usage... etc etc etc into a form if I use Microsoft Access 2002 for my forms application? Thanks in advance Rudi ...more >>

I am trying to convert this query to slq server 2000 and I cant figure out how to get rid of the IIF statements and make them case statements. If anyone could help I would greatly appreciate it! Thanks! spafa SELECT Jeopardy.Main, Jeopardy.Name, Jeopardy.COMMENTS2, Je...more >>

Hi, Just wondering...Suppose I have a couple of stored procedures that updates some tables. How can I "wrap" these in a transaction that can be rolled back in case of an error? I cant find any good documentation on the subject (I assume I wanna use BEGIN TRANSACTION and COMMIT - or something s...more >>

Hi, Can the English version of Sql Server (or other DBMSs) store data/records in other languages (e.g. French, Chinese, etc.) or do you have to use the French version of Sql Server to store French data? Where can I go to read more on this? Thanks. Eric ...more >>

I've accidentally (of course) overwritten all Intranet database tables in production with those in dev using DTS. I'm meant to only copy over new tables but somehow got everything instead. Server guys said backups weren't running for some reason. Is there anyway I can revert this database b...more >>

I'm running a resource-intensive stored procedure, which reads a file with about 50,000 lines with a BULK INSERT into a temp table, then goes through it and inserts a record for each line into another table. While this procedure is running, SQL server stops accepting any other requests coming fr...more >>

I have an existing table: CREATE TABLE TestQuestions ( qId int NOT NULL IDENTITY(1,1) CONSTRAINT PK_TestQuestions PRIMARY KEY NONCLUSTERED, testId int NOT NULL REFERENCES Tests(testId) ON DELETE CASCADE, objectiveId varchar (30) NULL qType int NOT NULL CONSTRAINT...more >>

Hi! Can anybody give me a hint how to put sa resut from EXEC into a variable. EXEC is called: EXEC(@TmpQuery) and it returns a single int value (SELECT COUNT(*) ....) Thanks! Mario. ...more >>

I am trying to populate a field in a SQL table based on the values returned from using substring on a text field. Example: Field Name = RecNum Field Value = 024071023 The 7th and 8th character of this number is the year. I am able to get those digits by saying substring(recnum,7,2) and ...more >>

I have a table: MasterFileID FileName ParentID 1 Hello <null> 2 World 1 3 Another <null> 4 Again 1 I want a query that will return the following result set...more >>

I'd like insert null value from a query but i can do it, if my query is : UPDATE table:name UPDATE euro='' WHERE id='5'; i have a sybase message warning like this : Error converting data type varchar to numeric. Why ???? ...more >>

Hi, I have a table with the follow fields : ID - Int Date - Datetime I need to make a simple query to result the records between to dates with a single ID. Ex.: Get the records between 01/08/2003 to 30/08/2003 only from ID=230 I´m using the follow : ADOQuery1.Close; ADOQuer...more >>

I am using vb.net and the myoledb provider. I've run into a problem where what I need to do is open a database connection to a mysql server, easy. The problem arrises in that I want to, if the server cannot be found, open from a locally stored encrypted XML file. Works fine when t...more >>

Hi, Suppose I have a table containing monthly sales figures from my shop branches: Branch Month Sales ----------------------- London Jan 5000 London Feb 4500 London Mar 5200 Cardiff Jan 2900 Cardiff Feb 4100 Cardiff Mar 3500 The question I...more >>

Como lo hago para tener o usar el Administrador corporativo en mi computador si el SQL esta instalado en el servidor ? lo que pasa es que en mi computador tengo instalado el Windows 2000 Server y el SQL 2000, pero ahora me voy a bajar a Windows 2000 Professional y el SQL no lo podre instalar y...more >>

Hi Everyone, Is there an easy way that I could pass a filter (variable) unto a view via stored procedures? I would appreciate your suggestions. Benito...more >>

We've been using CVS for our HTML, JAVA etc.. Now we would like to use it for our SQL CODE to. We have a large database with many stored procedures. I would like to have something using Ant like: Download the latest version of source code. If the SQL patch has been updated, run the patch...more >>

I am facing a problem while using SQL Server with VB application. Implicit conversion from datatype text to nvarchar is not allowed. Use the convert function to run this query. When i see the trace file, i see one stored procedure called but no lines of code get executed, and immediately aft...more >>

Hi, I need help on modifying this SQL query. select count(*)as cnt, sum(a.shmsharect)as shares, b.cdename from shrsharemaster a, shrcodemaster b where b.cdecode = a.shmtype group by b.cdename The above query returns: 4 0 Individuals 4 27000 Nominees 1 0 Others others 1 0 Unit...more >>