|
Do a "Truncate Table MyTableName". That will delete all of the records from the table, and resets the seed for the identity column. Note that this will only work if you don't have any tables with foreign keys pointing at your table (you would have to drop the foreign key contraints before doing the truncate).
Hope this helps.
Andy
|
|
|
|
|
Thanks Andy...
Follow your goals, Means will follow you ---Gandhi---
|
|
|
|
|
I'm trying to get a view that contains a sub-query to work in a special way. Right now I'm getting the error message
ORA-01427: single-row subquery returns more than one row when I query the view.
Essentially what I want the subquery to return, is a single string of comma separated values of the multiple rows that exist. Is this possible?
So a table with 3 rows in it and the column that I'm querying has as data
ROWA
ROWB
ROWC
would return the string "ROWA,ROWB,ROWC".
Thanks.
Chris Meech
It's much easier to get rich telling people what they want to hear. Chistopher Duncan
But for a man, barbecuing eggplant and portobello mushrooms is a sure way to have people question your sexual orientation. Kuro5hin
|
|
|
|
|
After some thought, I realized that what I needed was a 'grouping function'. So I wrote my own function that returns a single string of the comma separated data. Works like a charm.
Chris Meech
It's much easier to get rich telling people what they want to hear. Chistopher Duncan
But for a man, barbecuing eggplant and portobello mushrooms is a sure way to have people question your sexual orientation. Kuro5hin
|
|
|
|
|
I have the following query where table1 located on Server1 and rest tables located on different server2.
How do I connect to server2 in order to perform this query?
INSERT Table1
select t.id,sum(p.paymenttotal)
from transactions t,payments p
where t.transactionnum=p.transactionnum
group by t.id
|
|
|
|
|
You have to use the full name of the object if you have objects on different servers. e.g.:
Server1.DatabaseName.dbo.Table1
or
Server2.DatabaseName.dbo.transactions
or
Server2.DatabaseName.dbo.payments
dbo=Database Owner
--Colin Mackay--
"In the confrontation between the stream and the rock, the stream always wins - not through strength but perseverance." (H. Jackson Brown)
|
|
|
|
|
I have a query that populates a datagrid. My question is how can i take the results in the datagrid and use them as parameters in an insert query. Essentially I am taking data out of one table and putting it back into another one.
|
|
|
|
|
Try to bind a DataSet into your DataGrid, therefore you can add/update/delete items in your DataSet through the DataGrid.
When you have finished, use a different SQLDataAdapter to insert/update another table.
Have a look at the example below:
ADO.NET: Update a Database from a DataSet
http://samples.gotdotnet.com/quickstart/aspplus/default.aspx?url=%2fquickstart%2fhowto%2fdoc%2fadoplus%2fupdatedatafromdb.aspx
|
|
|
|
|
I'm using this code to execute an stored procedure:
VARIANT vRecords;
_RecordsetPtr m_pRecordsetPtr;
irecordsaffected=0;
try{
vRecords.vt=VT_I4;
m_pRecordsetPtr=pQDef->Execute(&vRecords, NULL, adCmdText);
irecordsaffected= vRecords.iVal;
}
when I do it for "Insert" statements, the vRecords variable gets the correct value, but I get always 0 if I try an "Update" statement, any idea?.
thanks in advance.
|
|
|
|
|
The problem was that I was using allways the same _CommandPtr (pQDef), and never delete the Params used, this is why I got always 0 records when Update
and 1 record on the first Insert.
now works fine.
David.
|
|
|
|
|
Hi, Currently i need to display information from 1 table. This is the information of the table which contain 3 fields
Logdate ProjCode Hrs
2003-09-11 SBS 3
2003-09-11 asd 9
2003-09-11 asd 9
2003-09-12 dsa 6
2003-09-13 zxc 5
2003-09-13 cxz 2
When i add a new field using
Quey: Select *, (select Sum(Hrs) from Table a where a.Logdate = b.Logdate Group by Logdate)from Table b
Result:
Logdate ProjCode Hrs Sum
2003-09-11 SBS 3 22
2003-09-11 asd 9 22
2003-09-11 asd 9 22
2003-09-12 dsa 6 6
2003-09-13 zxc 5 7
2003-09-13 cxz 2 7
how do i edit my query statement where i can get
Result:
Logdate ProjCode Hrs Sum
2003-09-11 SBS 3 22
2003-09-11 asd 9 null
2003-09-11 asd 9 null
2003-09-12 dsa 6 6
2003-09-13 zxc 5 7
2003-09-13 cxz 2 null
as only display 1 sum in 1 logdate where the rest is null.
|
|
|
|
|
If you are using SQL-Server then one way would be:
create procedure RepProjHours<br />
as begin<br />
set nocount on<br />
<br />
--Create temporary table with line-number.<br />
create table #temp1 (<br />
LineNo integer identity not null,<br />
LogDate datetime null,<br />
ProjCode varchar(3) null,<br />
Hrs decimal(6,2) null<br />
)<br />
insert into #temp1<br />
select LogDate, ProjCode, Hrs<br />
from MyTimesheetTable<br />
order by LogDate, ProjCode, Hrs desc --Nice ordering?<br />
<br />
--Find the first timesheet entry for each day.<br />
create table #temp2 (<br />
LogDate datetime null,<br />
MinLineNo integer not null,<br />
TotalHrs decimal(6,2) not null<br />
)<br />
insert into #temp2<br />
select LogDate, Min(LineNo), Sum(Hrs)<br />
from #temp1<br />
group by LogDate<br />
<br />
--Join everything together (with subtotal on first line for each date).<br />
select T1.LogDate, T1.ProjCode, T1.Hrs, T2.TotalHrs<br />
from #temp1 T1<br />
left outer join #temp2 T2<br />
on T2.LogDate = T1.LogDate<br />
and T2.MinLineNo = T1.LineNo<br />
order by T1.LineNo<br />
end
Hope this helps. There are several other ways of achieving the same effect).
Andy
|
|
|
|
|
ALTER TABLE tt ALTER COLUMN [cc] [int] identity [(2, 1)] not null
and it failed with below error:
Incorrect syntax near the keyword 'IDENTITY'.
How can I make it work ?
Thanks
|
|
|
|
|
Remove the square brackets around the parentheses after 'IDENTITY'. Then it should work.
|
|
|
|
|
I Removed the square brackets around the parentheses after 'IDENTITY'.
ALTER TABLE tt ALTER COLUMN [cc] [int] identity (2, 1) not null
but it still failed with below error :
Incorrect syntax near the keyword 'IDENTITY'.
why?~
|
|
|
|
|
I have a query that fills a datagrid with a order number, order qty, and shipped qty. After I click a button to fill the grid I want the code to be able to highlight any rows where the order qty isn't equal to the shipped qty.
|
|
|
|
|
Hi,
Environment - VB.NET, ASP.NET, SQL Server 2000.
In a SQL User-Defined Function, I am selecting a column which returns multiple rows. I need to construct one single string out of those returned values. To do that, I am using CUROSR.
Now, CURSOR is expensive operation. If there are 1000 users at a time, it will consume lot of resources.
Is there a way, I can construct this String without using CURSORs??
Please advice. Thanks
Pankaj
Follow your goals, Means will follow you ---Gandhi---
|
|
|
|
|
I don't believe there is.
Generally, rolling up data is an operation best performed on the client in procedural code.
You can reduce the cost of your cursors by ensuring that you use a LOCAL FAST_FORWARD STATIC cursor.
|
|
|
|
|
If you're satisfied with adding them to a single column, I recently discovered that this actually worked (atleast on MS SQL 2000):
DECLARE @t varchar(8000)
SET @t = ''
SELECT @t = columnname + @t FROM thetable
SELECT @t
Have a look at my latest article about Object Prevalence with Bamboo Prevalence.
|
|
|
|
|
Thanks for the responses.
Arjan: I read on some other site article that this way of adding the values to single column is a bug in SQL. Probably, will be taken care of in future updates.. ..thought of passing this information to you and readers of this question .
Though, I am still looking for a neat solution for this problem.
Follow your goals, Means will follow you ---Gandhi---
|
|
|
|
|
Hi,
I am writing a small program to test the scalability of SQL Server. My code is:
class MyClass<br />
{<br />
string sql = "select * from table1";<br />
string conn_s = "...";<br />
<br />
private bool threadFail = false;<br />
private bool ThreadFail <br />
{<br />
get <br />
{<br />
lock ( this ) <br />
{<br />
return threadFail;<br />
}<br />
}<br />
set<br />
{<br />
lock ( this ) <br />
{<br />
threadFail = value;<br />
}<br />
}<br />
}<br />
<br />
private void DoQuery()<br />
{<br />
DataSet ds = new DataSet();<br />
OleDbDataAdapter da = new OleDbDataAdapter(sql, conn_s);<br />
<br />
while ( !ThreadFail ) <br />
{<br />
try <br />
{<br />
da.Fill(ds);<br />
Thread.Sleep(100);<br />
} <br />
catch ( Exception e ) <br />
{<br />
ThreadFail = true;<br />
}<br />
}<br />
}<br />
<br />
public void Test()<br />
{<br />
int i = 0;<br />
while ( !ThreadFail ) <br />
{<br />
i++;<br />
Thread t = new Thread(new ThreadStart(DoQuery));<br />
t.Start();<br />
Thread.Sleep(100);<br />
}<br />
ShowMessage( "Total threads = " + i );<br />
}<br />
}
However, there's a problem. It threw an exception of without enough memory. I think it's because of "da.Fill(ds)". It occupied a lot of memory every time. What I want to know is: can I use OleDbCommand.ExecuteReader() instead of OleDbDataAdapter.Fill()? And I don't get the data from sqlserver. But I am not sure if the OleDbCommand.ExecuteReader() already run the sql command in SQL Server? Because I need the query to be executed but I don't want the return data.
Anyone has any idea? Thanks in advance!
|
|
|
|
|
Why don't you try using OleDbCommand.ExecuteNonQuery. It will execute the query, but won't return nothing.
Free your mind...
|
|
|
|
|
|
Is it possible to do the following query?
declare @Year varchar(5)
set @Year='02/03'
SELECT
case
when @Year='02/03' then AccountNumber between '50' and '59'
when @Year='01/02' then AccountNumber between '40' and '49'
else AccountNumber end
FROM Master
WHERE (Number = '71')
|
|
|
|
|
Hi everyone,
Is somebody able to answer my question? - I'm trying to have access to the MSysObjects table (in MSAccess database file) to get a list of all user-defined tables it holds. As the subject suggests I'm using ADO.NET. My select command (SELECT Name FROM MSysObjects WHERE Type=1 AND Flags=0) query gives me familiar to many of us "...no read permission in MSysObjects" exception, that (in my case) means that I didn't configue MSAccess to allow anyone to see any system/hidden tables. And it's Ok, but the funny thing is that I can create and open connection to the same *.mdb file from the VS.NET IDE (using Data Connections in the Server Explorer) without any changes of the database properties from the MSAccess application. So, VS.NET knows some other way to get (unlimited?) access to any *.mdb file (by either programmaticaly changing database properties, or using some other tricks).
I've found a workarround for this issue at
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vcmfc98/html/_mfcnotes_tn054.asp[^]
but they're usind direct calls to DAO to change internall database setting from the user's code. So:
- am I able to use direct DAO calls from inside my C# code?
- is there any good method to do it by means of just C# itself?
- is there probably another way to get the list of user tables without pulling MSysObjects table?
Any ideas will be highly appreciated.
Thanks,
Armen.
|
|
|
|