|
how abt this ?
select b.id, b.name from TableA b
where b.id in (select distinct a.id from TableA a) Apurv
“Never trust a computer you can’t throw out a window.”
(Steve Wozniak)
“There are only two industries that refer to their customers as ‘users’.”
(Edward Tufte)
|
|
|
|
|
Thanks for reply.
the query returns my table as it is, not the output which I am searching forAbdul Rahaman Hamidy
Database Developer
Kabul, Afghanistan
|
|
|
|
|
Oh .. sry ..
I think 'cursors' can only solve this ...Apurv
“Never trust a computer you can’t throw out a window.”
(Steve Wozniak)
“There are only two industries that refer to their customers as ‘users’.”
(Edward Tufte)
|
|
|
|
|
Hi all, How to create temp table with new column name from a existing table that has different
column name.
thanks
modified on Wednesday, February 3, 2010 8:19 AM
|
|
|
|
|
Easy
select column1 as newname
into #temp
from oldtable
where 1 = 2
insert into #temp(newname)
select column1
from oldtable
By creating an empty table you avoid locking a lot of the system tables in tempdb - ALL the time a select into is running the system tables in tempdb are locked, which prevents other users from creating temp tables.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
I will have to try it then I will let you know.
thanks for your help
|
|
|
|
|
Jashimu--you have been a member long enough to know not to use such a general message title. Please refresh yourself with the forum guidelines if needed!
|
|
|
|
|
Hi,
How can I get all children in just one level downer than a parent in hiererchy in SQL Server 2008?
Best wishes
|
|
|
|
|
Typical example, organ area problem. The table name is organ:
ID OrganName ParentID
1 A 0
2 a 1
3 b 1
SELECT a.* from organ as a, organ as b where a.ParentID=B.ID
The result is:
ID OrganName ParentID
2 a 1
3 b 1
April
Comm100 - Leading Live Chat Software Provider
modified 27-May-14 8:49am.
|
|
|
|
|
For some reason I can't add my dataconnect. I click on add connection select sql server
then I browse to the programfiles/...sqlserver/data and click on books.mdf
it tells me every single time that the file is in use. But I don't know of any program that would be using it. I'm running the server on my desktop simply to try and learn it and do a little programming with it. i can't seem to understand why it will not access that database.
Any help would be great thanks...
|
|
|
|
|
With ProcMon[^] you can investigate which process has the file in use.
Wout Louwers
|
|
|
|
|
At a guess the database has a connection that is still live, it may be left over from debugging or a corrupt connection.
Also Access may be corrupt and refusing the connection, try repair and rebuild via the Access UI.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Is there a new file in the directory holding your database, specifically a lock file? I'm new to SQL Server, but in Access a file is created when a connection is established that locks out other users. Sometimes when connections are not correctly closed, the file defining the lock is not deleted, blocking any subsequent access attempts. Deleting the locking file eliminates the problem. Is there an equivalent locking file in SQL Server? If so, deleting it may restore your access to the database.
"A Journey of a Thousand Rest Stops Begins with a Single Movement"
|
|
|
|
|
I finally got it resolved. I actually had the server manager running connected to the sql server. So I took and closed that and refreshed the datasource and worked fine. thanks for the help all
|
|
|
|
|
Hi,
I need to handle missing select values programattically. this is what i have
insert into A(id, name, age, value, DOB)
select 1,'Max','21','a','100928' union
select 2,'x','100948' union
select 3,'Rex','32','b','100928' union
select 4,'xx','100938' union
select 5 'Fed','24','xx','100928'
all my insert statement are stripped off via regex. the structure of insert statement is as show above. but at times it may miss "name" and "age" and it gives me error something like
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions
now those insert scripts are quite bulky. sometime 100MB I would like to automatically handle missing name and age. I am not sure how could i do that? could you please give me some idea?
|
|
|
|
|
You could pass Null as the value if you don't know the name or age.
Wout Louwers
|
|
|
|
|
how do i pass null value without modifying below block of query
select 1,'Max','21','a','100928' union
select 2,'x','100948' union
select 3,'Rex','32','b','100928' union
select 4,'xx','100938' union
select 5 'Fed','24','xx','100928'
Lets just assume above text is a read only text.
|
|
|
|
|
If you don't change the query, the insert will not work. The query should look like this:
select 1,'Max','21','a','100928' union
select 2, Null, Null,'x','100948' union
select 3,'Rex','32','b','100928' union
select 4, Null, Null,'xx','100938' union
select 5 'Fed','24','xx','100928'
Wout Louwers
|
|
|
|
|
Its not the insert you need to change, its the code that generates it. Without seeing the code its difficult to help further.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Hello,
The application was developed in .NET and SQL 2005. Originally, we'd thought that it'd be a web-based application (i.e. the SQL database will be hosted by us and users can connect to it).
But some clients want the database to be hosted on their computer itself. Of course the application needs the database to run, however, by putting the database on the client's machine we will lose control of our data.
I cannot find an easy way that SQL provides to allow only the application to access the data, but not humans.
Nothing is altered in the database--we are simply reading data from the SQL table, so should we explore other things (indexed flat files?) to make this into a software product without losing control over the data? The Database size is around 30GB.
G. Satish
|
|
|
|
|
You have a number of options - none of which is having 30gb of text files.
You can manage the database access by permissions so only you can get into the database. Most DBAs discourage this as they are all control freaks.
You can encrypt the data and the code (stored procs) this will probably be more acceptable and gives reasonable protection.
There are probably others that I'm not conversant with.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
It is possible to make a Sql Server database readonly.
ALTER DATABASE database-name SET READ_ONLY
However, this can only be done once the database has been attached to the server instance.
|
|
|
|
|
Satish - Developer wrote: I cannot find an easy way that SQL provides to allow only the application to access the data, but not humans.
AFAIK, there is none; your application runs under the security-restrictions of the human that started the application. This gets mapped to a SQL-Login, and that determines what securables you're allowed to see, and what not.
ASP.NET has it's own Windows-identity defined; perhaps you can do something similar?
I are Troll 
|
|
|
|
|
Satish - Developer wrote: I cannot find an easy way that SQL provides to allow only the application to access the data, but not humans.
Thats because there isn't one. The best you can do is either restrict access by using sql security (plenty of details in the help files) or encrypting your database.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Your data? What do you mean your data? 
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
