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...
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...
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''."
...
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.
...
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?
...
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...
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...
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 ...
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...
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?
...
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...
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, ...
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...
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
...
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
...
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...
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...
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...
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...
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...