|
I have an application under test, where I iteratively reset an export batch and then run the export for different configurations. For major testing, I wish to test each config over the full monty, 70k documents exported, but for dev testing I like to keep the rows in each section down to 500. Last night in the interests of not breaking code, I was popping ‘TOP 500’ into each query, but I’m sure there is a better way. Any suggestions will be appreciated. I’m using a SqlDataAdapter to fill my DataTable objects.
|
|
|
|
|
hai,
i m using SQL server 2000.
i have 3 columns A,B,C respectively. the columns consist of the following values.
Col-A: a,b,c,d,e,f,g,h,i,i,i,i,i
Col-B: j,k,l,m,n,o,p,q,r,i,r,r,r
Col-C: s,t,u,v,w,x,y,z,a,b,c,n,s
I need the output as
Col-D: a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z
Note : Commas are not needed. just in row-wise
i.e, i want to combine 3 columns and want to remove the REPEAT values,NULL and EMPTY
how to achieve it? - KARAN
|
|
|
|
|
here it is my quick solution:
select distinct cola <br />
from myTable<br />
union all<br />
select distinct colb from myTable where colb <> (select top 1 cola from myTable order by cola desc)<br />
union all<br />
select distinct t3.colc from myTable as t3 where t3.colc <='z' and t3.colc >(select top 1 colb from myTable order by colb desc)
I Love T-SQL
|
|
|
|
|
thanks blue_boy.
but all the three columns is in the same table and i dt know the last value whether the last value is "z" or anyother (since, i cant understand {t3.colc <='z'} statement)
|
|
|
|
|
Instead harcoded 'z' condition then modified query looks like this:
select distinct cola <br />
from myTable<br />
union all<br />
select distinct colb from myTable where colb <> (select top 1 cola from myTable order by cola desc)<br />
union all<br />
select distinct t3.colc from myTable as t3 <br />
where t3.colc <=(select top 1 colc from myTable order by colc desc)<br />
and t3.colc >(select top 1 colb from myTable order by colb desc)
result of this query comes from one table in my case named myTable
I Love T-SQL
|
|
|
|
|
wwowooow.... awosome...... my friend! thats working fine! i achieved 99% of my result. thanks a lot. But the thing here i have some EMPTY values (i.e, string without any values(string count is 0)). how to remove these?.. expecting your reply so as to fulfil my work in 100%
|
|
|
|
|
which column return empty text?
I Love T-SQL
|
|
|
|
|
The Final result should not contain any NULL or EMPTY values..................
Now the NULL is removed. how about the EMPTY?
|
|
|
|
|
The way how you eliminate NULL values same do with empty text.
e.g
columnname <> ''
I Love T-SQL
|
|
|
|
|
You are welcome. Did you establish 100% your query?
I Love T-SQL
|
|
|
|
|
|
Much of articles to read are here[^] about your answer.
I Love T-SQL
|
|
|
|
|
Basically you will need set up your SQL Server installation using Windows authentication so that each user logs in using their Windows credentials.
You can then apply security settings for each user depending on which tables etc you want them to have access to.
There is a lot more behind this which google will more than help with.
What I have described above should get you heading in the right direction.
Regards
Guy
Continuous effort - not strength or intelligence - is the key to unlocking our potential.(Winston Churchill)
|
|
|
|
|
Thanks to both of u for reply
Sorry i m facing a problem in login
I right clicked on Local n then select property->Security Tab
n select sql server authentication mode set the username prof and password prof
i worked but when i wanna connect ,it ask authentication when i enter prof username n password prof
it says:
Login Failed please varify the sql server is running and check your sql server registration properties using right click
but when i select property it ask athentication again
The username n password is right then wht's the problem???
The Great Pleasure In Doing That Things That Other People Say U Can't By Doing This U Can Shut Their Mouth
|
|
|
|
|
I need to store my RTF files in a database. I have tried WRITETEXT function for that and also normal insert command. My problem is when I read the file back from database (using READTEXT and normal SELECT) and save it to hard disk the file gets corrupted . ( 28 characters are added at the begining of the file . When I remove those redundant characters the file can be opened by wordpad). Why is it ? Do you have any idea of storing files on database and retrieving them ? I use SQL Server 2000.
Yahoo ID = hosseinenick
modified on Tuesday, April 29, 2008 2:46 AM
|
|
|
|
|
I have this table
UserAttributes
UserAttributeId int identity (1,1) not null
AttributeId int not null
UserId int not null
AttributeValue default (1) not null
Attribute Id is a FK to a table of attributes and user Id is a FK to the table of users. I am trying to create a trigger that whenever AttributeValue is changed that the sum of the two highest attributes is loaded into a field in the Users table. I would like it to be one statement (so I don't have to iterate a cursor), but I'm stuck on the syntax. Any ideas?
Broken Bokken
You can't carry out a ninja-style assasination dressed as an astronaut. It's the luminous fabric; too visible. - Tripod
http://www.brokenbokken.com
|
|
|
|
|
Hi
I am not clear with your question, do you want to get two maximum values from a column without using cursor?
Please go thro' the following SQL Script, I am not sure whether you are looking for this solution:
USE northwind<br />
<br />
DECLARE @MaxStock SMALLINT<br />
DECLARE @NextMaxStock SMALLINT<br />
<br />
SELECT <br />
@MaxStock = MAX(UnitsInStock) <br />
FROM <br />
PRODUCTS<br />
<br />
SELECT <br />
@MaxStock<br />
<br />
SELECT <br />
@NextMaxStock = MAX(UnitsInStock) <br />
FROM<br />
PRODUCTS <br />
WHERE <br />
UnitsInStock < @MaxStock<br />
<br />
SELECT <br />
@NextMaxStock<br />
<br />
SELECT <br />
@MaxStock + @NextMaxStock<br />
Hope this is clear.
Harini
|
|
|
|
|
You could try something like:
Select Sum(AttributeValue)<br />
From<br />
(<br />
Select top 2 AttributeValue<br />
From UserAttributes<br />
Order by AttributeValue desc<br />
)
Wout Louwers
|
|
|
|
|
This was exactly what I was trying to do. Thanks!
Broken Bokken
You can't carry out a ninja-style assasination dressed as an astronaut. It's the luminous fabric; too visible. - Tripod
http://www.brokenbokken.com
|
|
|
|
|
I'm trying to retrieve an image from Sybase DB using VB.Net. The field (sImage) is of type
"image". I've tried with dataset and dataReader, but no luck so far.
Here is what I have done:
This code works fine for SQL Server database.
Example:
Dim strConn As String
Dim strQuery As String
Dim objConn As OleDbConnection
Dim objDs As New DataSet
Dim intId As Integer
Dim objDa As OleDbDataAdapter
intId = Request.QueryString("uniqueId").ToString()
strQuery = "Select SImage From IMAGE Where ImageId= " & intId
strConn = ConfigurationManager.AppSettings.Get("ConStr")
objConn = New OleDbConnection(strConn)
objDa = New OleDbDataAdapter(strQuery, objConn)
objDa.Fill(objDs)
If Not IsNothing(objDs) AndAlso objDs.Tables.Count > 0 Then
Response.BinaryWrite(objDs.Tables(0).Rows(0)("SImage"))
End If
objDa.Dispose()
The code gave me this error:
"Invalid cursor position"
I'm using The Sybase DB version 9 .0.1
I'm stuck here so please help me!
Venu
|
|
|
|
|
Hi
I installed "QAnywhere patch to a Version 9.0.2" and "SQL Anywhere Bug Fix Readme for Version 9.0.2, build 3320". Now my code works fine.
Venu
|
|
|
|
|
Hi all,
I have a dataset containing a few tables. Now i want to create a new MS Access database, and populate it with the tables of this dataset. How do i do it programatically?
Can someone provide me with code snippets of how to create a new MSAccess database with a dataset in C#?
Would really appreciate an early response. Its quite urgent
Please Help...
Thanks
Shreyas
|
|
|
|