In my VB.NET program I want to execute a DROP DATABASE. But when I do that, I get an error telling me the database is in use. How can I disconnect from this database. I've tried DISCONNECT DATABASE, but that is unknown. Any help is welkom. rg, Eric ...more >>

Recently I have been asked in a interview how the two queries processings( architect wise) are different. or how the two queries are diff in internal processing. ANSI syntax SELECT fname, lname, department FROM names left outer join departments ON names.employeeid = departments.employee...more >>

Hi, I have alarge table with 60 Million rows. I needed to add a new INT column to the table and then assign it a value of 0. The Alter statement to add the column ran pretty quick: ALTER TABLE tb1 ADD c1 INT NOT NULL but the update statement: UPDATE tb1 SET c1 =...more >>

Hi, I've recently been trying to develop a directory structure for a database orientated file storage system. I've seen some posts around, but what I am really stuck on is how to select a folder object and all its sub-objects (folders and files) and then return them. I want to use the resul...more >>

I'm using the LIKE in a query that is built conditionally and can't figure out how to reference the single quotation mark associated with the '% when it's inside a string. I'm a dope. Really appreicate the help. Example below. mary --------------------------- Declare @VisitorLastName as...more >>

I saw a very interesting concept in Oracle where you can create your own context (like a session in ASP). You can fill this on logon. So you could for example hold a customerID in a cache that later will be uses in Views, Stored procedures or Functions. In a Virtual Private Database (VPD) you can...more >>

WE have recently moved to MS SQL from an inhouse database, our issue is that we have a number of offices connecting. Is it possible to set each user with a default language and dateformat when they logon. I am using ASP to connect to the DB, and have tried the following: Sql = "SET LANGUAGE En...more >>

Hi all, From what I understand from SQL help online, convert (or cast) are meant to change the datatype of a value to another datatype. However, in this case neither of them seem to work! I've been staring at this line for the past few hours, and cant even begin to imagine what im doing wrong...more >>

Hi, How do I get a value to round to 3 decimals in a select statement ? Select Round(SUM(Float_Value1)xSUM(Float_Value2),3) I expect it to show 3 decimals but it still show a lot of decimals, does the datatypes need to be Decimal? Using MS SQL 2000 Lasse ...more >>

Sorry if this has been done before, I'm new to SQL and can't seem to get this to work. The basic idea is I have Table1 that has a heirarchtical structure, and a second table that holds information about the nodes in the heirarchy. I want to be able to delete from anywhere in the heirarchy (thr...more >>

I have a table with a bunch of nulls. I wnt to replace all the nulls with a space. I can't use Replace(mycolumn,null, ' ') becase replace requires a string and the word null is not a string. what do I do? is this the right news group for this? Thanks!!!...more >>

So in my stored proc i create a temp table with 3 columns. I populate that temp table with 50 rows of data (say). I have an existing stored procedure and i want to call it (using "exec") for each row in the temp table. Each column in the temp table represents a parameter to the stored procedu...more >>

Hi all, I have just noticed an error when I try the following : SELECT * FROM viewWebPages WHERE UPPER(PageContent) LIKE '%my_text%' Can you not use UPPER on a 'text' data type? If not, does it ignore case sensitivity altogether? Any help would be appreciated, Regards Rob ...more >>

Given two tables how do I store a unique number in each Row that is unique to both tables at the same time example: Table 1 someColumn ( rows 1-7) 1, 2, 6,7,8, 11, 16 Table 2 someRow ( rows 1-9 ) 3,4,5,9,10,12,13,14,15 I need to design these table in the right way Thanks ...more >>

Lo all, Ok - just spotted a problem I'm going to have. All of the rows for the page content in the database contain formatting ie, <b>some bold text</b> This will cause me a problem initially as if I wanted to search for <company name> the SQL statement might end something like this : ...more >>

We have a table, in which contineous insertion is being done. At some = time, we need to reindex the whole table. But before this, want to know = that how many rows are not indexed since last reindex is being done. So = is there any way in sql server to get this informations ? - Ketan Patel...more >>

how can I check for existence of table column from within a stored procedure ?? ...more >>

There are 10 fields which get the values from a stored procedure and displayed in the Frontend. My doubt is Out of 10 fields 5 fields are on Server A Table A 5 fields are on Server B Table B stored procedure is on SERVER A so My stored procedure looks something like this ...more >>

I have an update statement, which when run on a local server runs fine. It is written as follows: UPDATE table1 SET field1 = 1 WHERE field2 = 'ABC' and field3 = --(also runs fine with "in" keyword) (select min(field3) FROM table1 where field2 = 'ABC') This updates one row as expected...more >>

Hi I have an asp.net/sql server application that I would like to demo on a website. I'd like each new visitor to see a pre-populated database setup which they can then manipulate, but their changes should be lost as soon as they end the session and their changes should not be visible to any other...more >>

Hi, Does anyone know how to determine which service packs are installed, either programmatically or via a query, on versions 7 and 2000? Ben ...more >>

Hello, I have few table like this GreatGrandParent, GrandParent, Parent, Child ( related by PKs) Activity table that keeps track of their activity. Now I want to get a set containing a GreatGrandParent, YoungestGrandParent, YoungestParent, YoungestChild Note Youngest = last entered int...more >>

Hi Ive got two tables with date fields in them. They are both set to datetime. When i write to the tables they store in the format mm/dd/yyyy. How ever when i pull the back from the tables and load them into vb.net on table returns dd/mm/yyyy and the other returns mm/dd/yyyy. Why is this and how c...more >>

Hello Situation: Application->ODBC->MSSQL2K I need to change some queries (replace) between ODBC and MSSQL on the fly, or, in the other words transparently to application modify some query before they executed by SQL server. Another Q. There is the query: SELECT COUNT(A) FROM B WHERE B.A='C'...more >>

I have query similar to the following SELECT * FROM (TB1 INNER JOIN TB2 ON TB1.RecNum = TB2.RecNum) INNER JOIN TB3 ON TB2.RecNum = CNT3.RecNum Where ((DteProc > DteDone ) or (DteProc>='02/07/04 09:58') or (DteUpd>DteDone) or (DteProc<'01/01/2003') ) We have about 4 thousand records in the tabl...more >>

i know you can do a SQl statment like thi Select @variable = Count(*) From Table can you do something similer for a statment like thi INSERT INTO Table1 SELECT Field1, Fields2 FROM Table2 WHERE Field1 = 'AValue' or do i have to use the @@rowCount to check if the record was u...more >>

I need to return the sql server log file for source 'logon' grouped by description in a qry for today. Is there any internal function that will return that. Client is non technical and I need to send them a security report of sql server by email. Don ...more >>

Im trying to return the record that has the greatest datediff between PswDate and todaysdat SELECT T1.Operator_recordId, T1.Operator_userName, T2.UsedPsword, DateDiff(Day, T2.PswDate, GetDate()) AS DAYS FROM Operators AS T LEFT JOIN OperatorUsedPasswords As T ON T1.Operator_recordId = T2.Operat...more >>

Dear Friends I have three tables Employee primary table. Salary foregin Key table and Department Foregin key table. I want to delete all the records in one go for the provided Employee ID from all the three tables. Please suggest how i can achive the same. Best regards Sharad...more >>

ive set a quer SELECT T1.Operatorr_RecordId, T1.Operator_UserName, T2.PswDate, T2.UsedPswor FROM Operators AS T JOIN OperatorUsedPasswords As T ON T1.Operator_recordId = T2.OperatorID WHERE T1.Operator_UserName = @OperatorName This only returns 1 record, when there is one record in Opera...more >>

we are using a third party Software package for our accounts system, which uses SQL. I have written a VB6 (SP5) application to auto insert invoices into the Accounts system using the Accounts Systems liberies. The problem i have is that there is no ' undo' facility in the accounts system, so if ther...more >>

How can prevent an error from my stored procedure when it gets to the end of the table with no more records to select? ...more >>