|
Satips wrote: Then how will you program and create query Diana.
I guess because she said she wanted to learn SQL.
|
|
|
|
|
Hi Diana
Academics with the same family name would be:
select Academic.AcNum, Academic.FamName, Academic.GivenName, Academic.DeptNum
from Academic
where Academic.FamName in (
select FamName
from Academic
group by FamName
having count(*) > 1)
order by Academic.AcNum The sub-query gives you a list of all of the family names where there are two-or-more researchers.
This type of query is often used to identify duplicate records in tables.
Regards
Andy
|
|
|
|
|
Thanks Andy
You're a Star...
|
|
|
|
|
I created a little query to assist our support guy in a workaround. The task was to create a backup table for all vendors not flagged for VAT, then flag all vendors for VAT, run an export to Pastel, then use the backup table to reset all the originally non-VAT vendors.
My manager called me over because step 2 wouldn't execute, it complained that the table VendorBak doesn't exist. When he added the create table statement, step 1 and 2 worked. How can the select into work when the table exists? Is there some strange condition that allows this?
Declare @stepid int
set @stepid = 1 --change to 2 after you have run the export
if @stepid = 1
create table vendorbak (DCLink int, Account nvarchar(1000), Name nvarchar(1000))
select DCLink, Account, Name into VendorBak from Vendor where CT = 0
update Vendor set CT = 1
if @stepid = 2
update Vendor set CT = 0 from VendorBak vb where Vendor.DCLink = vb.DCLink
drop table vendorbak
|
|
|
|
|
The "select .... into" statement creates its own table.
You should either remove your explicit "create table" statement, or replace your "select .... into" statement with an "insert .... select" statement.
|
|
|
|
|
IF statements only control the next statement. They do not act as a block, despite your indentation. To control more than one statement, use BEGIN/END.
As a result, your code really looks like this:
Declare @stepid int
set @stepid = 1 --change to 2 after you have run the export
if @stepid = 1
create table vendorbak (DCLink int, Account nvarchar(1000), Name nvarchar(1000))
select DCLink, Account, Name into VendorBak from Vendor where CT = 0
update Vendor set CT = 1
if @stepid = 2
update Vendor set CT = 0 from VendorBak vb where Vendor.DCLink = vb.DCLink
drop table vendorbak That means that when @stepid is 1, it will create the table, then do the SELECT INTO, then update, then drop the table.
|
|
|
|
|
Hi,
I have a T-SQL script file which i want to run using C#. Can any body please tell me how can i do it?
Regards,
Wasif.
|
|
|
|
|
Running SQL Scripts with a .NET Application[^]
Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...
"I wouldn't say boo to a goose. I'm not a coward, I just realise that it would be largely pointless."
Ready to Give up - Your help will be much appreciated.
My website
|
|
|
|
|
|
That wasn't on the page I linked to. Which page did that link come from?
Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...
"I wouldn't say boo to a goose. I'm not a coward, I just realise that it would be largely pointless."
Ready to Give up - Your help will be much appreciated.
My website
|
|
|
|
|
It was not the link what you referred. I bookmarked this link long back. And yesterday I tried to get it again and resulted in 404. I just thought of intimating you
|
|
|
|
|
Navaneeth. wrote: I bookmarked this link long back. And yesterday I tried to get it again and resulted in 404.
Ah... That website fell off the internet. The new link is: Types of Join[^]
Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...
"I wouldn't say boo to a goose. I'm not a coward, I just realise that it would be largely pointless."
Ready to Give up - Your help will be much appreciated.
My website
|
|
|
|
|
Hi, I need to retrieve the column info only from a table that i created in the DB. Though I know it can be done using sys.objects/sys.columns, i do not know how to form the query. please help.
|
|
|
|
|
Retrieving column names of a table
There are basically 3 approaches to this problem:
1.Use the sp_help procedure to get extended information about a
database object. By database object, we mean a table, view etc.
2.You can use the following select statement to retrieve a table’s columns:
SELECT TOP 0 * FROM table_name
3.The third approach, which I personally prefer, is using the schema object of SQL Server. For example, to retrieve the column names of Authors table, you can
use the following SELECT statement:
SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME 'authors'
Which results in the following result set:
column_name<br />
----------------------------<br />
au_id<br />
au_lname<br />
au_fname<br />
phone<br />
address<br />
city<br />
state<br />
zip<br />
contract<br />
(9 row(s) affected)
Tirtha
Do not go where the path may lead, go instead where there is no path and leave a trail.
Author: Ralph Waldo Emerson (1803-82), American writer, philosopher, poet, essayist
|
|
|
|
|
I have a data table and I want to find the row in the data table that contains the string "Dan" in the third column of the data table but I am unsure how to do this.
I have looked at .find but it uses the PK, which is set in the original database but not in the data table. Is it possible to bring the PK and the field names from the database to the data table?
Thanks,
Dan
|
|
|
|
|
Hey
Can you not just iterate through each row checking the content of the third column?
Or maybe look at this:
http://msdn2.microsoft.com/en-us/library/system.data.datatable.primarykey(VS.80).aspx
Try looknig at datasets in msdn also they may be more suitable for the job as I am sure they retain the schema.
Hope this is of use
Dan
|
|
|
|
|
|
That article says if it's not being returned it's because you've done something wrong!
Whats your code in the client for getting the output param?
|
|
|
|
|
|
|
|
No probs
|
|
|
|
|
|
I gathered that You seemed pretty switched on so I took it your SP was right
|
|
|
|
|
In a report I want to check if a picture exists before showing it.
I put this function in the Report Properties:
Function DoesFileExist(PathnFileName As String) As Boolean
DoesFileExist = My.Computer.FileSystem.FileExists(PathnFileName)
End Function
And in the Visibility property of the image placeholder I call the DoesFileExist function.
Inside the MS Visual Studio Development Editor everything works fine, but when I deploy the report to the IIS Server and run it from there it doesn't work:
the DoesFileExist function always return "False" even if the pictures exist.
I'm 100% sure it's a permission problem, I tried to correctly set the permissions both on IIS and on NTFS Folder with no luck.
Please help.
Thank you,
Roberto
|
|
|
|