I'm trying to script the changes to a SQL Server Compact Edition database running on windows mobile 6 and could really use: EXECUTE sp_rename 'MyTable.SomeColumn', 'BrandNewName', 'COLUMN' What other system stored procedures are available? What are the differences to the non compact version? Edit: There ARE system stored procedures...
I am using MSSQL 2005 Server and I have the following SQL query. IF @CategoryId IN (1,2,3) BEGIN INSERT INTO @search_temp_table SELECT * FROM (SELECT d.DataId, (SELECT [Name] FROM Category WHERE CategoryId = d.CategoryId) AS 'Category', d.Description, d.CompanyName, d.City, d.CategoryId, d.CreatedOn, d.Rank, d.vot...
I am using the FULLTEXT in my sql query to search the records. SELECT * FROM (SELECT d.DataId, (SELECT [Name] FROM Category WHERE CategoryId = d.CategoryId) AS 'Category', d.Description, d.CompanyName, d.City, d.CategoryId, d.CreatedOn, d.Rank, d.voteCount, d.commentCount, d.viewCount FROM Data d ...
I am providing search functionality in my website, when user searches a record then I want to display the time the query taken to get the results same as google does. When we search anything then google displays how much time it takes to get results? For this I have declared a @start variable in my SP and finding the difference in the e...
I am a student, with decent knowledge of SQL, but have had very little to do with triggers in the past. I've looked at a few sites for guidance, but comprehensive explanation on all commonly used statements seems fairly sparse. Is there a 'definitive' site for this kind of thing? Perhaps like a w3chools for advanced SQL? ...
I currently have a stored procedure that returns a list of account numbers and associated details. The result set may contain multiple entries for the same account number. I also want to get some aggregate information such as how many distinct accounts are contained within a particular result set. Is there some way to retrieve such a vie...
I'm currently working on a test plan and ran into a possible problem and I was wondering if anyone had any suggestions. The application uses SQL Server and it can connect between the different versions for compatibility (2000, 2005, 2008). Well i'm trying to set up some test machines and was wondering how I should set things up. Shoul...
Suppose there is a table "A" with 2 columns - ID (INT), DATA (VARCHAR(100)). Executing "SELECT DATA FROM A" results in a table looks like: DATA --------------------- Nowshak 7,485 m Maja e Korabit (Golem Korab) 2,764 m Tahat 3,003 m Morro de Moco 2,620 m Cerro Aconcagua 6,960 m (located in the northwestern corner of the province of Mend...
I'm reverse engineering the relationships between a medium-sized number of tables (50+) in an Oracle database where there are no foreign keys defined between the tables. I can count (somewhat) on being able to match column names across tables. For example, column name "SomeDescriptiveName" is probably the same across the set of tables....
I am returning an xml result for a query. Given two tables: **Foo** FooId int FooName varchar(10) **Bar** BarId int FooId int (FK) BarName varchar(10) I create a select statement like: SELECT FooId, FooName, ( SELECT BarId, FooId, BarName FROM Bar WHERE Bar.FooId = Foo.FooId AND...
sp_reset_connection seems to be called by SQL Server connection pooling, to ensure that connections reused from the pool have most of their settings reset. Does anyone know exactly what it does and doesn't do though? eg I see from this post that it doesn't reset the transaction isolation level ...
Table layout: CREATE TABLE t_order (id INT, custId INT, order DATE) I'm looking for a SQL command to select a maximum of one row per order (the customer who owns the order is identified by a field named custId). I want to select ONE of the customer's orders (doesn't matter which one, say sorted by id) if there is no order date given...
I am familiar with the VB6 ADO way of dealing with SQL queries and looping through the record set results. However, what is the correct way to query a server, cycle through the results, and dispose of my query in VB.Net? All the ways I have been using seem to be unstable and crash randomly. I have been using the following code: Public...
I have some data in DATA column (varchar) that looks like this: Nowshak 7,485 m Maja e Korabit (Golem Korab) 2,764 m Tahat 3,003 m Morro de Moco 2,620 m Cerro Aconcagua 6,960 m (located in the northwestern corner of the province of Mendoza) Mount Kosciuszko 2,229 m Grossglockner 3,798 m What I want is this: 7485 2764 3003 2620 6960 2...
I am doing a simple SELECT statement in an Oracle DB and need to select the columns in a somewhat-specific order. Example: Table A has 100 attributes, one of which is "chapter" that occurs somewhere in the order of columns in the table. I need to select the data with "chapter" first and the remaining columns after in no particular order...
I just stumbled (by accident) on yet another stupid not-sanitized-at-all sql injection flaw in a project I'm working on ...and I'm so tired of it. Do you have any advise on how to eliminate such bad sql statements and enforce prepared statements where ever feasible? Right now I would prefer a solution likeREVOKE DarnInlineDataStatements ...
I want to delete rows from a SQL Server 2000/2005 table variable based on the presence of other rows in the same table (delete all 0 count rows if a non-0 count row exists with the same date). Here is a simplified example that should only delete the row added first: declare @O table ( Month datetime, ACount int NULL ) insert i...
Hi all, I have problem with setting the formula in particular field.I am using sql database have been added from->add new item->sql database.I have orders table with following field ordernum primary key orderdate smalldatetime, custemail varchar(50), subtotal money, salestax money, shipping money, total AS(subtotal+salestax+shipping) Ho...
I'm looking for a side-by-side diff program a la xxdiff or DiffMerge that, instead of diffing files, allows blocks of text to be dragged into either the left side or right side window. I'm refactoring some SQL embedded in source files, and it would be nice to drag the sql statements from each source file into the diff program instead of...
Hi.. how do i access the procedure parameters inside the same procedure using a query for example: see this procedure procedure game(left in tab.left%type,right in tab.right%type,...) is --some local variables begin merge into tgt_table using subquery --(here is what i need to use the parameters) on some condition when mat...