sql

Visual Studio 2008 Debugging of SQL Server 2005 SP on Remote Host

I am using VS 2008 and trying to step into a stored procedure on a SQL Server 2005 database. I bring up Server Explorer, double-click on the procedure. It comes up in the text window. I set a break point in it, right click and select "Step into stored procedure". It comes back with "canceled by user". MSVSCOM.exe is running on the SQL Se...

TSQL: Return row(s) with earliest dates

Given 2 tables called "table1" and "table1_hist" that structurally resemble this: TABLE1 id status date_this_status 1 open 2008-12-12 2 closed 2009-01-01 3 pending 2009-05-05 4 pending 2009-05-06 5 open 2009-06-01 TABLE1_hist id status date_this_status 2 open 2008-12-24 2 pending 2008-12-26 3 open 2009-04-24 4 open...

sql query from usp results

How to query the results of a stored procedure? Here's my example: sp_columsn 'tblSomeTableName' --// Returns a recordset select COLUMN_NAME from [sp_columns 'tblSomeTableName'] --// Raises an error saying "Invalid object name 'sp_columns 'tblSomeTableName''." ...

Executing SQL LIKE in SQLObject

Is there a pretty way to execute an SQL statement with a LIKE clause in SQLObject? This one: fields = Foo.select("field LIKE '%%%s%%'" % bar) works, but it's somewhat ugly. ...

T-SQL, Cursors, FETCH INTO. How to use SELECT *

I am building a one off query to iterate through a set of joined tables. The select statement is using "SELECT *". Since it's is a table with lots of fields, I don't want to specify each column as a variable. Is there a way to FETCH INTO an array or SET of some sort and just grab the values I want? ...

Selecting the top n rows within a group by clause

I have schema similar to the following: create table bar ( instrument varchar(255) not null, bar_dttm datetime not null, bar_open int not null, bar_close int not null ) I would like to query the table, and return the most recent 5 rows per instrument. I can do it instrument by instrument, with: select top 5 instrumen...

TSQL ORDER-BY with a UNION of disparate datasets

I have a query that UNIONs two somewhat similar datasets, but they both have some columns that are not present in the other -- i.e. the columns have NULL values in the resulting UNION. The purpose of the UNION is to get the data in a friendly format for the software-side. The problem is, I need to ORDER the resulting data using those c...

How can i speed up this Indexed View?

Hi folks, I have a simple Indexed View. When I query against it, it's pretty slow. First I show you the schema's and indexes. Then the simple queries. Finally a query plan screnie. Update: Proof of Solution at the bottom of this post. Schema This is what it looks like :- CREATE view [dbo].[PostsCleanSubjectView] with SCHEMABINDING ...

How can I change NULL to 0 when getting a single value from a SQL function?

Hi, I have a query that counts the price of all items between two dates. Here is the select statement: SELECT SUM(Price) AS TotalPrice FROM InventoryWHERE (DateAdded BETWEEN @StartDate AND @EndDate) You can assume all of the tables have been set up properly. If I do a select between two dates and there are no items within that date r...

SQL Round function not working, any ideas?

Here is the SELECT statement: SELECT ROUND(ISNULL(SUM(Price),0),2) As TotalPrice FROM Inventory WHERE (DateAdded BETWEEN @StartDate AND @EndDate) Any ideas of why it's not rounding to two decimal places? ...

MySQL: Is it possible to JOIN the GROUP-BY'd results to two SELECTs?

Hi, I have two separate SELECT statements which are both GROUP-BY'd separately e.g.: SELECT x, y, z FROM a GROUP BY x SELECT x, n, o FROM b GROUP BY x I would very much like to JOIN these two SELECTs together to combine their columns, such as: SELECT x as x1, y, z FROM a GROUP BY x LEFT JOIN ( SELECT x as x2, n, o FROM b GROUP BY...

SQL data and JTable

I'm trying one sample program for practice and i want to display results of database in JTable. The problem is i have no idea how to do that. I know how to get data from database and display it on text field or console but never tried with JTable. How to do that ? Consider that i've table which is holding information like person name, ...

What database should I use in this VB.NET app?

My database must be updated at arbitrary intervals, manually, with new info to put on standart tables that don't require structural modifications. one app will update the database. Another app, the one I will distribute (to friends and family only, but doesn't require any security feature to it, like encrypting the database) will read th...

Save float values in SQL Server

Hi, I have a simple web app , and want to save some numbers of Float or Double format in SQL server. but there is a problem , when I try to save 123.66 , In Table I see 123.6600003662109 stored. How nd why my float number changed when save on DB? how can I fix this error? Thanks ...

In storprocedure how to set variable on where ?

CREATE PROCEDURE[BoardID] @sParent varchar(8000) AS Select Boardid,PONO,ImageStatus from BoardDetail Where Boardid=1 and @sParent Why @sParent create error how to use this variable on where ......i want to supply string as parameter and it will be set on where ......like @sParent ...

Find lowest common parent in recursive SQL table

Hi, Suppose I have a recursive table (e.g. employees with managers) and a list of size 0..n of ids. How can I find the lowest common parent for these ids? For example, if my table looks like this: Id | ParentId ---|--------- 1 | NULL 2 | 1 3 | 1 4 | 2 5 | 2 6 | 3 7 | 3 8 | 7...

SQL AVERAGE TIME

I have the following query in MSSQL: select TRANSACTION_TYPE_ID ,COUNT(TRANSACTION_TYPE_ID)AS NUMBER_OF_TRANSACTIONS ,CAST(SUM(AMOUNT)AS DECIMAL (30,2)) AS TOTAL FROM [ONLINE_TRANSACTION] WHERE CONVERT(CHAR(8), CREATED_ON, 114) >='17:30' AND AMOUNT IS NOT NULL AND TRANSACTION_TYPE_ID !='CHEQUE-STOP-TRANS-TYPE' GROUP BY TRAN...

Are writing triggers in MS SQL server the same as writing them in MS Access?

I have written the following trigger in SQL server: create trigger test_trigger on invoice -- This is the invoice table for insert as declare @invoiceAmount int -- This is the amount specified in the invoice declare @custNumber int -- This is the customer's id --use the 'inserted' keyword to access the values inserted into th...

another sql query statment

ok thanks alot for all who help me ,but now i have another problem i want to get this statement correct also if (byNametextBox.Text != null && byBuildingtextBox.Text !=null && seTextBoxPublic1.Text == null) { da = new SqlDataAdapter("SELECT * FROM Students WHERE name='" + byNametextBox.Text +"and [buil-id]='"+byBuildingtextBox.T...

SQL Execution Plan shows an "Actual Number of Rows" which is larger than the table size.

I have an execution plan for a fairly complex join which shows an index seek being performed on a table with the "Actual Number of Rows" reading ~70,000, when there are in fact only ~600 rows in the table in total (the estimated number of rows is only 127). Note that all of the statistics are up to date and the input parameters to the q...