|
i have a query for SQL SERVER Any body help me.
"a table which does not have any Primary key" ok!
this table have a 100 Rows with same data in every rows or (all table has same data like "pavan").
and i want to have only two Rows; and all 98 will be deleting form the table."
Note:
1) this table does not have any kind of Unique Value in Row
2) this table have only one Column(fldName) which consist a same data like "pavan"
3) we have required only 2 Row from this table, all rows will be delete from this table.
please help me write me Query.
Pavan Pareta
|
|
|
|
|
This is an idea.
You will need to implement it in SQL:
1. Select top(2) into temptable
2. Delete all from your table
3. insert into your table values from temptable
Sincerely,
Elina
Life is great!!!
Enjoy every moment of it! 
|
|
|
|
|
hi Elina
i had try number of time but its not working.
please give me right solution.
thanx
Pavan Pareta
|
|
|
|
|
What exactly you tried?
Here is the idea. Translate it to SQL
Select TOP(2)
INTO TempTable
FROM yourTable
now you can drop your table and rename the temp one.
OR TRUNCATE yourTable and move rows from tempTable into yours.
Sincerely,
Elina
Life is great!!!
Enjoy every moment of it!
|
|
|
|
|
the example is:
strConnect = _T("Provider=sqloledb;Data Source=MyServerName;"
"Initial Catalog=MyDatabaseName;"
"User Id=MyUsername;Password=MyPassword;");
mine is:
Provider=sqloledb;Data Source=ronald-40gb;
Initial Catalog=northwind; User Id=ronald;Password=;
why it always show error messages ===> connection failed
what should i do? thanks before.
|
|
|
|
|
mine is:
Provider=sqloledb;Data Source=ronald-40gb;
Initial Catalog=northwind; User Id=ronald; password=1234;
why it always show error messages ===> connection failed
what should i do? thanks before.
|
|
|
|
|
Is the database runnning? Is the name of the server correct? Is the SQL Server accepting TCP/IP requests or is it using just named piped? (If the latter then try changing the server name to "(local)") Have you supplied valid user name and password? (Why aren't you using a trusted connection?)
|
|
|
|
|
the way i always create connection strings:
create a text file called foo.udl
double click it:
set up your params and test
open file in notepad and copy and paste the conn string out. This way you know that you have a string that managed to connect
HTH
Russ
|
|
|
|
|
i think i've seen the problem with your conn string.
i guess you're using an SQLConnection object. This object doesn't need to be told the provider as it is sql specific.
Russ
|
|
|
|
|
I have create a SQL there r some calculation involve, but i got this error:Divide by zero error encountered.
But there is some which work when i select some others DATE but with some other date which no result came out becasue "Divide by zero error encountered." how can i make the SQL will just continue show those value out even is 0.01... something??
select bizdate,concesscode,con.<br />
<br />
[description],totaltrans,totalqty,totalwgt,totalsales,taxper,<br />
<br />
(totalsales*taxper/100) as TaxAmt,baseamt,level3per as APPLY ,<br />
<br />
(level3per*(totalsales-Taxper/100))as LeaseAmt,(totalsales-baseamt-<br />
<br />
(totalsales*taxper/100)-(level3per*(totalsales-taxper/100)))as <br />
<br />
AmountDue<br />
,discountamt, returnamt,(totalsales-totalcost)*100/totalsales as GP <br />
from tot_item ti, concess con <br />
where con.code=ti.concesscode and ti.bizdate='2006-04-11' and<br />
<br />
concesscode>='A' and concesscode<='D'
|
|
|
|
|
Try adding
" and totalsales<>0"
to your where clause
Chandra
|
|
|
|
|
All,
I have the following table
CREATE TABLE TEST
(
TEST1 binary(32)
)
go
The TEST1 column is a binary with 256 bits.
How can I write T-SQL code to turn an arbitrary bit in TEST1 on or off, or check its state?
You obviously get arithmetic overflow if you try something like:
INSERT INTO TEST VALUES(0)
go
UPDATE TEST SET TEST1 = TEST1 | POWER(2, 137) /* Try to set bit 138 to "on" */
go
"............ Arithmetic overflow occurred ......."
Any ideas?
cheers,
Neil
|
|
|
|
|
It's really hard. If you want to support yes/no type values in SQL, use a bit column. The database - well, SQL Server, at least - stores bit columns by bit manipulation in a field that's just long enough to store all the bit columns in a row. That is, if you have 8 bit columns in a particular table, they will only take one byte of space per row.
|
|
|
|
|
Many thanks for your post.
I thought of using the bit type, but then I'd have to have to 256 columns!
Not ideal, and it would be hard to correlate them as well. And how would I update column 178 in code without some kind of array?
I am looking into using a combination of
SUBSTRING(), STUFF(), CAST(), CONVERT() and ASCII() .. I'm sure it's possible with some work!
I thought someone must have done this .. don't want to reinvent the wheel..
cheers,
Neil
|
|
|
|
|
I have worked out how to do this, so I thought I should post the answer here in case anyone is interested:
INSERT INTO TEST VALUES(0)
/* Turn bit 137 on */
UPDATE TEST SET TEST1 = CONVERT(binary(32), STUFF(TEST1, 137 / 8 + 1, 1, CONVERT(binary(1), SUBSTRING(TEST1, 137 / 8 + 1, 1) | POWER(2, 137 % 8))))
/* Test bit 137 value */
SELECT SUBSTRING(TEST1, 137 / 8 + 1, 1) & POWER(2, 137 % 8) FROM TEST
/* Turn bit 137 off */
UPDATE TEST SET TEST1 = CONVERT(binary(32), STUFF(TEST1, 137 / 8 + 1, 1, CONVERT(binary(1), SUBSTRING(TEST1, 137 / 8 + 1, 1) & ~POWER(2, 137 % 8))))
cheers,
Neil
|
|
|
|
|
The int datatype is 4 bytes.
CREATE TABLE BitTesting<br />
(<br />
TestField int<br />
)<br />
insert into BitTesting (TestField) values (0)<br />
UPDATE BitTesting SET TestField = TestField | 138
| is the bitwise OR operator.
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
Many thanks for your post.
That idea is fine, but an int has only 4 bytes. That is just 32 bits ... I need a much longer array of bits (256). And I need to be able to set on/off 1 bit at a time... your example of 138 is actually turning on 3 bits:
138 = 128 + 8 + 2 = 10001010
So I am basically running into problems with large numbers....
cheers,
Neil
|
|
|
|
|
Oh...guess I didn't read your initial post carefully enough. Maybe you could just have a separate table with the following columns:
<br />
FKeyID int<br />
<br />
BitLoc tinyint<br />
<br />
BitVal bit
This table could have 256 records for each record in the parent table (BitLoc would be 0 to 255).
Just another idea.
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
Thanks for that.
That's a neat idea, and one I hadn't thought of. It's more tables to maintain, but it would certainly work. I may just do that .. if I can't find a simple way to hack at a binary(32) directly 
cheers,
Neil
|
|
|
|
|
hai frndz,
i am extracting CSV file.This csv file generated from Informix DB. Do u know the equivalent datatype of Lvarchar(500) in MSSQL2000 and above
All I ever wanted is what others have....
CrazySanker
|
|
|
|
|
crazysanker wrote: i am extracting CSV file.This csv file generated from Informix DB. Do u know the equivalent datatype of Lvarchar(500) in MSSQL2000 and above
Well, a quick search on the internet (and I'm curious why you didn't try this yourself) revealed:
The LVARCHAR data type is an SQL data type that you can use to create a column of variable-length character data types that are potentially larger than 255 bytes.
This would suggest a VARCHAR would probably be acceptable. If you need to go larger than 8000 chars then you need to use a TEXT column.
|
|
|
|
|
I have two XML files with two different schemas coming from two different sources that have a common personid field. Is there an easy way to read in the XML sources into something like a DataSet and then display data from each source in one data grid.
Alot of the examples of merging tables etc on the web deal with data being drawn from a database and this C#/ADO.NET noob can't seem to figure out an easier way than creating a third table and populating it with the relevant elements from the original two.
Thanks,
Kevin
|
|
|
|
|
Hi,
I am facing problems with the Sub Query parameter injecting problem. Let me give u the code so tat u can understand the prob
String strSQL = “select d.deptno,e.empname from dept d, (select empname from emp where hdate = : inputDate and jdate= : inputDate) e”;
OralcConnection con = new OracleConnection(conString);
OracleCommand cmd = new OracleCommand(strSQL,con);
OracleDataReader dr;
cmd.CommandType = CommandType.Text;
cmd.Parameter.Add(“:inputDate”,”01/01/2006”);
dr = cmd.ExecuteReader();
but when ever i run this code its saying tat ORA-01008: not all variables bound.
If I add other parameter to the CommandObject with same name and value its working fine. But I need to add only one parameter to this CommandObject.
Please help out
sri
sri
|
|
|
|
|
Try adding the parameter without the colon ...
cmd.Parameter.Add(“inputDate”,”01/01/2006”);
Also, I don't know if it is a formatting issue with your post, but in your query, you appear to have a space between the colon and the variable name.
|
|
|
|
|
Hi Jim,
thanks for your response, i traied that process also but still i its giving the same error. is there any thing i am missing in my code. please help me out.
sri
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
