Which one should be used for the sake of speed if I have choice between EXISTS and ANY? Why, if possible? Thanks Victor...more >>

Hi there... I want to show a specific row in my table, say, the 15th row. But I want to show only this row, not the previous rows. How to do it? Using SQL Server 2000. Thank you. Eduardo ...more >>

I have a simple stored proc that I am trying to pass a parameter list. Only problem is I dont know how to make it work. Please help. create proc usp_searchspecialty @specID varchar(50) as select * from tbl_specialties where specialtyid in (@specid) I am trying to pass in a list o...more >>

what are some other IDE tools to use with sql server 2000 and 2005? I like query analyzer a lot and am not crazy about EM or SQL Workbench (or whatever it is called these days). What does this community use when you are not using QA, EM or Workbench? I like to type in T-SQL rather than be us...more >>

Is it possible to connect to an ODBC driver (e.g. SQL Server or Oracle) directly without having to create a DSN before hand. This would be great as dumb users can't handle creating a DSN. Would be nice if I could ask them SQL Server or Oracle, server name, user name and password only and progr...more >>

I have a database that has first name, last name, and phone numbers. However, there are times when the phone numbers are duplicated. This database is always running, but we want to remove duplicate phone numbers ONLY if it is within a month. So, they can be in there if they have at least 3...more >>

I've googled and looked everywhere but still can't find the exact answer to this question: How much space does an empty varchar field occupy? We have a table for which we expect tons of records and we are trying to minimize the record size. One of us wants to add a "notes" field (maybe ...more >>

Is there anyway to keep a stored procedure in cache forever?...more >>

I am trying to write a stored proc which, via dynamic sql, will give me the next available ID number for a table, name specified as a param. So for example, what i want to be able to do in pseudo-code is: PROC GETNEXTID(TABLENAME) AS BEGIN RETURN 'SELECT MAX(ID)+1 FROM '+TABLENAME END ...more >>

I have time in my program: for example: dim timeTest as datetime timeTest="10:15:10" I save this time to database as decimal value: timeTest.ToOADate - I get the decimal value and I save it to database Then I read from database: ?format(date.FromOADate(test),"hh:mm:ss") but I d...more >>

I would like to pass string parameters holding a table name and field names in to a sproc containing a SQL statement, then use those names to reference the objects within the statement. What is the best way to go about doing this? i.e. SELECT @fieldname FROM @tablename Thanks for the help...more >>

How do I disconnect a particular session without having to stop and restart the server? I read the article on the DISCONNECT statement in BOL but it takes as an argument a name, not a connection number. How do I know the 'name' of the connection? Is there a system stored proc that will do this, ...more >>

How can I hide a record, so other connections (or transactions) can't see it? A very simple example is: Two applications running in two different machines, wants to read records and then update those records. The problem is both applications search for records with same characteristic but...more >>

Hi! I want to use the IN operator in a query to an SQL-server db. The query looks something like this: SELECT TR.AMOUNT, TR.DESCRIPTION FROM AGRTRANSACT TR WHERE TR.DIM_4 IN(strValues) The AGRTRANSACT table contains more then 500 thousend rows and DIM_4 is not an indexed field. ...more >>

In my program I have value as time: for example 10:15:00 If I create stored procedure and add parameter of datetime data type, I = get an error, if I try to insert the time value. Error message is: SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and = 12/31/9999 11:59:59 PM.=20 Wh...more >>

When i call a stored procedure as a user, i get the following errors: EXECUTE permission denied on object 'sp_OACreate', database 'master', owner 'dbo'. EXECUTE permission denied on object 'sp_OASetProperty', database 'master', owner 'dbo'. When i run the stored procedure as 'sa'...more >>

Little example: DECLARE @Table TABLE (table_id INT,table_int INT,table_flag CHAR(1)) INSERT INTO @Table VALUES (1,10,'a') INSERT INTO @Table VALUES (2,20,'b') INSERT INTO @Table VALUES (3,15,'b') INSERT INTO @Table VALUES (4,25,'a') SELECT CASE WHEN table_flag = 'a' THEN SUM(table_int)END ...more >>

Hi we are using CRM package with SQL Server 2000 sp 3. When a user on a particular client machine does a certain amount of work on the database. They get the following error pop up: PushList returned an error code of 0. ----------------------------------------- An attempt was made to push...more >>

Hi all How can I set a record as Read Only, so another App connected to the same database can't see the record. But my connection can update this record. I've tryied the code bellow, but it doesn't work. What valuies should I pass to open the Connection and Recordset? Thanks In Advanc...more >>

I have inherited a database of legacy data (created by Labview software) and I need to import that data into a SQL Server 2000 database. One of the fields I'm importing includes a string of numbers which respresents a datetime field - in the following format: 3172089659 which represen...more >>

I have a table with 3 columns: SUBJECT_KEY, TOPIC_KEY, FIELD_KEY, DTEXT . For example: SUBJECT_KEY TOPIC_KEY FIELD_KEY DTEXT 100 1 10 Microsoft 100 1 10 Dell 100 ...more >>

Hi, Can this be done. I want to have different databases but there may be a possibility that I will want to join some of the tables in a select statement. Thanks Ellie ...more >>

Hi. I have a table with a primary key that I'd like to be an identity, so I don't worry about giving it a value manually (just increases automatically. 1, 2, 3, ...) The problem is that I have another table where these key is a foreign key, so in my application, I must fill the value manually ...more >>

I have a datetime field where the month and day are mixed up. I am trying to write an update statement that will allow me to fix this but Im having a bit of trouble. What I have is: UPDATE contact_created SET dateObtainedtest = year(dateObtainedOld) + '-' + day(dateObtainedold) + '-' + m...more >>

I have to tables - Contacts and Companies Table Contacts: ContactID FirstName LastName CompanyID Phone ... etc Table Companies CompanyID CompanyName I am writing a VB .NET app, where the user should add, update and delete contacts. On a Windows form there are some text boxes for the...more >>

Hi all SQL 2k I'm looking for more info on check constraints, basically when, where & how to use them I got a column defined as TinyInt (0-255 value) but I only want the values of 0-100 stored in the column. In the check constraint am I right in say I can put column >=0 and column <=...more >>

Any other tools than Enterprise Manager and Query Analyzer out there that puts more GUIs on SQL-DMO? Examples: 1) Display a list of all indexes in a database, complete with fragmentation status, etc. 2) An extended object browser that tells me if a specific column is defined th...more >>

I am trying to use the script below in query analyzer to clone a record. The only difference is that I want to duplicate property number 164 with a new number of 7035. Once this is working I'll change the numbers and execute it about 300 times - going back later to add unit numbers. When I run th...more >>

i have a piece of code which uses cursor and have been told to change it to set based. Can anybody help me here understand what is set based and what are the steps involved? Thanks declare @v_TransId numeric(18,0) create table #t_EmpPayrollTrans (transactoin_id numeric(18,0) ) .... DECL...more >>

Hi! I need a SQL query help! I need to insert records into table called "#Document" in such a way that "ObjNo" increments by one every time the record is inserted. The "ObjNo" column is an integer column (it is not a identity column) in the "#Document" table. Please also note that the numb...more >>

Hi all; I have need to XOR a column much like one would SUM( ) a column. I'm figuring this would need a UDF, but I can't for the life of me figure out how this would be done as an aggregate function. Any thoughts greatly appreciated. Thanks! Jim Ankrom ...more >>

I have a stored procedure which aggregates the number of events found in two separate tables at a designated time interval When I run just the query portion in SQL Query Analyzer it works as designed and there are no NULL values. When I run it as a stored procedure I get … "Cannot insert th...more >>

Hi all, I am trying to get some information out of a text string but cant seem to get my charidex's in the right place, my piece of text looks like e.g. Number of bedrooms: 4 Property type: Detached Garage type: Integral Single Garage Awaiting release Please contact us for price in...more >>

Hi, Is it possible to perform a restore of a differential backup done with VDI API ? I'm not sure the way differential backup works, if it uses transactional logs or not. When I do a differential backup juste after a log backup, itself done after a full backup, transaction logs are truncat...more >>

I'm reading records from one database to another database, when something goes wrong i'm doing a rollback. We have to do a rollback of all the records. Everything is going well, except that the records are locked untill the commit. And when a record is locked we can't do a select in this tab...more >>

I am hoping someone can help me here. I am trying to convert some web procedures into stored procedures on the SQL server to speed up the returns. I can do all of my process sans 1. THere is a for next loop that changes the SQL the needs to be exexcuted. The number returned in the for next l...more >>

I have a property table. It has an auto-number key, and contains an address. I have to clone 200 - 300 additional property records on various addresses - just changing the unit number. It would save me hours if I could write a SQL statement where I could just keep changing the property id in quer...more >>

I have managed (Not sure how) to do something to corrupt my database, but I can't seem to identify the corruption using DBCC. The problem I have is in trying to run an insert query against a table using an application role. The error is being trapped through a VB ActiveX but is reproducible i...more >>

I am using SQL Server 2000 and have a requirement to monitor one of the tables continuously (say every 2 mins). I need the output to go into a text file in the format dd/mm/yy, HH:MM:SS No of Ids = 100 At the moment I have scheduled a job in the Enterprise manager to execute every 2 mi...more >>

Hello, I need to update 'dirty' part numbers with valid part numbers. There are 2 tables involved: t1 - used to clean t2 t2 - to be cleaned by t1 t1 col1 col2 col3 col4 col5 123 aaa bbb aab bbb 123 aaa bbb aa bbb t2 (PK = col1, col2, col3) col1 col2 col3 col4 col5 123 aab bbb - ...more >>

Hi , I am trying to insert values into a table from a import table. However i dont wont to insert values from the import table that already exist in the "live" i have the following sql statment with the where condtion is based on fornames , surnames , postcodes and DMSID's not matching. If...more >>

Hi all, I delivered an apps to my client last month, and now my client complains that the longer he use the apps in a day, the longer my apps would take to process a 'command'. This means, the apps runs fine in the morning, but in the afternoon, the apps runs like a turtle. The only way to gai...more >>

Hi there, I'm facing a problem after i upgrade sql version from 7.0 to 2000. Same query is running in both version, there is no problem in 7.0 but in 2000. The query is as following : ---Start--- txnType = "S/NT" SQL = "select year from Leave where txn_year = 2004 and " SQL = SQL +...more >>

Hello Ladies and Gents I have some rules that need to be evaluated to see if a condition is true. Business Rules shown below. Everything works but I don't think it is particularly efficient. If any guru can suggest an improved table structure and query (I thought that cross joins might help...more >>

Well at least for me ;o) I have this sql here that runs fine SELECT tblCompany.Company, tblCompany.Address, tblCompany.City, tblCompany.State, tblCompany.Zip, /* MIN(CASE n WHEN 1 THEN name END) AS name1, MIN(CASE n WHEN 2 THEN name END) AS name2 repeat for as many names as requ...more >>