|
Can be done only using corelated subqueries
SELECT Distinct SalesID FROM fky a WHERE
EXISTS (SELECT * FROM fky b WHERE a.SalesID = b.SalesID AND fkey = 12345)
AND
EXISTS (SELECT * FROM fky c WHERE a.SalesID = c.SalesID AND fkey = 775)
Regards
KP
|
|
|
|
|
Thanks. This worked great.
|
|
|
|
|
Hi,
We have two tables and we views like:
1) create view ... select bno from A : we can create a clustered index on this view
2) create view ... select bno from B : we can create a clustered index on this view
3) create view ...
select A.bno
from A
LEFT JOIN B ON B.bno=A.bno
: we cannot create a clustered index for this view, it gives error like:
"... one or more disallowed construct"
How can we solve this problem.
Thanks for your help (:
|
|
|
|
|
Simple. You cannot have a clustered index on a multi-table view - you can on the underlying tables, but not the view. This is because a view does not actually exist in the database, it is effectively just a select statement. A clustered index physically orders the data and as a multi-table view does not have a physical presence it cannot be ordered. The clustered index you have created on the single tyable views have been applied to the underlying table.
Hope this helps
Bob
Ashfield Consultants Ltd
|
|
|
|
|
So, how can we optimize that query to make it faster if we cannot declare an index?
What's the way to improve our performance?
Thanks.
|
|
|
|
|
Thats a whole can of worms. Firstly, outer joins are bad. Can it be avoided? Secondly, you can create a clustered index on each of the underlying tables, also a (non-clustered) index covering the columns in the view may help, but its really not possible to give a definative answer with only the limited oinformation yuo have supplied. Thirdly, how large are the tables? The whole table may be cached if they are small, so indexes are relatively unimportant in that case. What is the frequency of insert/update/delete from the underlying tables? More indexes means slower for these activities.
I could go on, but you get the idea, there is no simple answer.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
I used to look for a definitive Yes/No answer to managing indexes, I have since come to the conclusion the DB tuning is an Art for NOT a science. There are so many factors that weigh differently on the results that each case is very individual. So I have a thumb rule - grab the fields used in the join and where clauses and create a index covering the most common 2/3 queries.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
We have found a sample that contains multi-table view and it has a clustered index, we tasted its create script and found the difference.
Multi-table views can have Clustered Index but it mustn't contain LEFT OUTER JOIN, in our view the select query defined like LEFT JOIN but we've just learned that LEFT JOIN means LEFT OUTER JOIN so we couldn't be able to create clustered index, the problem was JOIN statement.
That was all (:
|
|
|
|
|
Apologies, I got it wrong.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
I have a SQL 2005 Express database (mdf) to which I am connecting from my application.
Table has two columns : ID (primary key, datatype int, allow NULL = false, identity = yes) and Name1 (datatype string).
When I use the "INSERT INTO table1 (Name1) VALUES (@Name1)"-command to create a new entry I get an error telling me NULL can't be inserted into the ID-column since NULL is not allowed. But I dont't want to give the ID-value to the database via my SQL-command but ID should automatically be the next value.
What am I missing in the column's settings?
"I love deadlines. I like the whooshing sound they make as they fly by." (DNA)
|
|
|
|
|
Hi,
Since you have set the ID column as Identity it will be incremented automatically whenever a new record inserted into the table. You do not need to supply values for ID field via SQL-Command.
Try following SQL Command this works fine for me.
Create table table1 (ID int primary key identity not null, Name1 VarChar(30))
insert into table1 (Name1) values ('John Adams1')
insert into table1(Name1) values ('John Adams2')
insert into table1(Name1) values ('John Adams3')
insert into table1(Name1) values ('John Adams4')
insert into table1(Name1) values ('John Adams5')
Select * from table1
Hope this helps  .
Regards,
John Adams
ComponentOne LLC
|
|
|
|
|
Thanks John.
John_Adams wrote: Create table table1 (ID int primary key identity not null, Name1 VarChar(30))
I think, that's exactly how I defined my ID-column in table definitions
Name: ID
data type: int
primary key
allow null = false
identity = yes (begin with 1, increase 1)
But when inserting a new row via INSERT-command I just receive this message telling me I am not allowed to insert NULL in ID. But I don't give a value for ID at all in my INSERT-command.
"I love deadlines. I like the whooshing sound they make as they fly by." (DNA)
|
|
|
|
|
Have you switched IDENTITY_INSERT on by mistake?
SET IDENTITY_INSERT table OFF
Maybe a space between identity and insert, check the docs.
|
|
|
|
|
Thanks Mark. You are right, it was switched on. Now it works fine.
Thanks.
"I love deadlines. I like the whooshing sound they make as they fly by." (DNA)
|
|
|
|
|
There are several ways to insert images into a report. The first method I tried was a database based solution. IE 7.0 was unable to render the image. It worked on FireFox 2.0 only. However FireFox had other lacks which obstructed developement.
Then I went on using project based method, wich gave exectly the same result.
The embeded solution is not suitable for us, because we have hundreds of reports and the size is an important factor.
The URL based method worked fine with outer addresses like:
http://szerver1.krk-gyarfas.sulinet.hu/~ref/erdokertes_elemei/kazetta2.jpg
BUT IT DIDN'T WORK WITH local images like:
http://rs1/ReportServer?%2fGrassReports%2flogo.png
In later case the Report Manager truncated the URL of the imeges to:
http://rs1/
This seems to be a BUG. If there is a workaround then please send it asap!
Adam
|
|
|
|
|
in an insert statement how to insert a row when all the values are not supplied
please help
Thanks
|
|
|
|
|
show the table name followed by the columns you have
sort of
insert into table1(name, firstname) values ('Smith','Fred')
where table 1 also contains the address etc
Hope this helps, but I suspect you need a book on SQL before going much further.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
I am trying to set dynamic field in the page header .
If the report has multiple pages, header(with the field) occurs while viewing the report.
But while exporting to PDF or Excel , header(with the field) comes on first page only. Other pages do not contain the header information.
Thanks in advance
|
|
|
|
|
hai.
currently i m using asp.net with c# (2.0 framework)
sql server 2000.
i need to convert row into column. (without using pivot table. since some days back i found a query to execute it in "google". but i missed the query)
for example...
ID - Name
101 - Rajesh
102 - Jay
103 - Karthick
104 - Vishnu
i need the output as...
ID - NAME - ID - NAME - ID - NAME - ID - NAME
101 Rajesh 102 Jay 103 Karthick 104 Vishnu
how to achieve it? help me - KARAN
|
|
|
|
|
Karan_TN wrote: (without using pivot table
The whole technique is pivoting - taking rows and turning them into columns is pivoting.
Karan_TN wrote: ID - NAME - ID - NAME - ID - NAME - ID - NAME
101 Rajesh 102 Jay 103 Karthick 104 Vishnu
What happens when you have 50,000 records? You cannot string them together in a single row, and if you could what use would it be?
I think you need to think about what you are trying to achieve and then decide how you need the data.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Thanks Bob.. You are right.
let me think upon some other way.
thanks for understanding
|
|
|
|
|
I've written a series of ASP and winform applications for a database my one of my company's software vendors built. The vendor puts user login credentials in the sql connection strings in order to authenticate users. Since the same database is to be used, I have to use the same authentication method to keep users from being confused with additional usernames/passwords to remember.
Other than an attacker appending Integrated Security=True to the end of the connection string. What security issues should I be concerned about with this method?
|
|
|
|
|
Make sure that the connecting user only have access to the bare minimum he requires.
Such as only execute on sprocs and not access to change the sprocs, or direct access to the underlying tables.
|
|
|
|
|
<sarcasm>HAHAHA!! Of COURSE all users have very as-needed access to the DB! Our software vendor's thought of that!</sarcasm>
Of course, with any of my tables I try to keep things restricted to stored procs that certain users have access to.
Thanks for pointing this out, though. I appreciate any response.
|
|
|
|
|
I would be concerned about storing user names and passwords as plain text.
Bob
Ashfield Consultants Ltd
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
