|
DECLARE @iColumns INT, @sql VARCHAR(2500),@cProduct VARCHAR(10)
SET @sql = ''
DECLARE curProducts CURSOR FOR SELECT DISTINCT product FROM Table1
OPEN curProducts
FETCH NEXT FROM curProducts INTO @cProduct
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = @sql + ', (SELECT count(supplier) FROM Table1 t2 WHERE t2.supplier = t1.supplier and t2.product = ''' + @cProduct + ''') as ' + @cProduct
FETCH NEXT FROM curProducts INTO @cProduct
END
close curProducts
DEALLOCATE curProducts
SET @sql = 'SELECT Supplier ' + @sql + ' FROM Table1 t1 GROUP BY supplier'
exec(@sql)
----------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
Does the job for me:
SELECT PRODUCT, SUPPLIERS, COUNT(SUPPLIERS)
FROM MYTABLE
GROUP BY PRODUCT, SUPPLIERS
|
|
|
|
|
A table T contains salary of employees. i want to find out
N th highest salary in sal server 2000
SAM>
|
|
|
|
|
Maybe like this:
-- Create temp table with ID and Salary (ID being identity column, starting at 1 and incrementing by 1)
-- Select all the distinct salaries from employees into temp table, ordered descending.
-- Select from temp table where id = N
Something like that?
|
|
|
|
|
Have you looked at this thread[^] from a few days ago. Though the original poster wanted the fifth highest salary, the query can be done for any number of n, just change the 5 to whatever n needs to be. Probably best to do this as a stored procedure.
Hope this helps,
Paul
|
|
|
|
|
SELECT TOP 1 * FROM (SELECT TOP N salary FROM employees ORDER BY salary DESC) AS EMP ORDER BY salary ASC
----------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
select max(column_name) from <table_name>
Regards
Preetha
|
|
|
|
|
I have a database running on SQL Server 2005, itself running on XP Pro (32-bit), which I access through ADO within ASP. I set up a System DSN (call it "whatever"), and the ASP code simply uses "DSN=whatever" as the connection string. Straightforward enough. A few months ago I had this running on SQL Server 2000, and migrating it over to 2005 wasn't a problem.
I'm now trying to get this to run on SQL Server 2005 64-bit on XP x64. If I try to use a system DSN, the same ASP code complains about the provider not being found (despite having it redefined through the Data Sources control panel applet on x64). Google searches are leading to believe (though not yet confirmed) the control panel applet can only be used to define 32-bit data sources and, essentially, I'm SOL if I try to go down that route on x64.
So I tried replacing the "DSN=whatever" connection string with a "real" one, based on samples posted on www.connectionstrings.com. I'm now running into access permissions galore.
At this point I can't begin to enumerate what I've tried and what I haven't (defining users in SQL, a custom user for anonymous access on IIS, etc), so instead of trying to figure out where I may have gone wrong...I'd rather start again from a clean slate... So, given:
- XP x64
- SQL Server 2005 x64
- IIS
- classic ASP
- ADO
...can someone point me to some resources explaining the steps needed to connect to this stupid database? Preferably explained in dumb user terms--this is so fundamental, I can't believe I can't get it to work. 
|
|
|
|
|
Too general? I'm just having a hell of a time finding the right keywords on Google.
The system worked on x86. I'm just looking for gotchas for doing the same on x64...anyone?
|
|
|
|
|
Hi all,
I am working for information warehouse application. Here database used is Oracle.Here we are loading data from flat file to oracle table using SQL Loader. Which is getting failed and showing error
ORA 00001 unique constraint violated
I have checked for the corresponding table by using DESC <table_name>;
It is showing NOT NULL constraint for all the columns and not showing any primary key. Is there any other way to check for primary key?
I know there can be a issue of indexes as i am new to database please guide me how can i check for index constraints in a table.
Please help me to resolve this unique constraint voilation.
Please guide me, what can be the expected reason for this failure.
|
|
|
|
|
The error message has nothing to do with indexes, but rather referential integrity. There is a column that has a constraint on it that constrains what data is allowed for the column. The data must exist in some column in another table. It is this other column in the other table that is indexed. The usual method to avoid this error is to disable the contraints while loading data with SQL*Load and to then enable the constraints after all data has been successfully loaded. Alternatively, if you can obtain the schema definition, you may try to rearrange the order of the data in your flat file and ensure that the referred-to tables are loaded prior to loading a table that contains referential integrity constraints.
Chris Meech
I am Canadian. [heard in a local bar]
The America I believe in has always understood that natural harmony is only one meal away from monkey burgers. [Stan Shannon]
GOOD DAY FOR: Bean counters, as the Australian Taxation Office said that prostitutes and strippers could claim tax deductions for adult toys and sexy lingerie. [Associated Press]
|
|
|
|
|
Hi
Thanks for your response.
I am new to database can u please explain me more regarding referential integrity constraint and how to check it.
I need it urgently please help me
|
|
|
|
|
Apologies, but the other thing to check is the data itself. There is probably some index on this table that has been defined as unique, or no duplicates allowed. This index could be on a single column or a group of columns. You should check the schema definition to verify this.
Chris Meech
I am Canadian. [heard in a local bar]
The America I believe in has always understood that natural harmony is only one meal away from monkey burgers. [Stan Shannon]
GOOD DAY FOR: Bean counters, as the Australian Taxation Office said that prostitutes and strippers could claim tax deductions for adult toys and sexy lingerie. [Associated Press]
People vote 1s for espier because there is no zero. [Ed Gadziemski]
|
|
|
|
|
What are the best practices for writing stored procedures in SQL which improves the performance of the server?
|
|
|
|
|
Try to restrict the queries result set by using the WHERE clause.
Try to restrict the queries result set by returning only the particular columns from the table, not all table's columns.
Use views and stored procedures instead of heavy-duty queries.
Try to avoid using SQL Server cursors, whenever possible.
If you need to return the total table's row count, you can use alternative way instead of SELECT COUNT(*) statement.
Try to use constraints instead of triggers, whenever possible.
Use table variables instead of temporary tables.
Try to avoid the HAVING clause, whenever possible.
Try to avoid using the DISTINCT clause, whenever possible.
Include SET NOCOUNT ON statement into your stored procedures to stop the message
Use the select statements with TOP keyword or the SET ROWCOUNT statement, if you need to return only the first n rows.
Use the FAST number_rows table hint if you need to quickly return 'number_rows' rows.
UNION ALL statement instead of UNION, whenever possible.
Ricardo Casquete
|
|
|
|
|
Hi Deeksha,
Guess the following links will help you.
http://vyaskn.tripod.com/coding_conventions.htm
http://www.extremeexperts.com/SQL/Articles/BestPractices.aspx
Thanks and Regards
Pani
|
|
|
|
|
Hi Guys and Gals,
Your help is needed, is there any way to see when (date/time) is the specific record inserted or updated in sql server?
Many Thanks.
Stephen
---------------------
www.islasolutions.net
|
|
|
|
|
No. To acheive this, You need to include modifieddate, createddate columns manually in all your tables and update them.
|
|
|
|
|
how about the transaction log, is sql server store info in this log?
---------------------
www.islasolutions.net
|
|
|
|
|
Not sure of that.
Let's see, if some one could answer this..
|
|
|
|
|
How about using a trigger? of course you will need to have a column to store the value but I bet that's easiest/simplest way..
Greetings,
:->
|
|
|
|
|
Thanks, actually planning to implement this in future, but for now i just want to check, if possible to can see the date of my previous records when it was inserted or updated,  think theres no other way then.
many thanks to all the response .
Regards,
Stephen
---------------------
www.islasolutions.net
|
|
|
|
|
Hi,
I have the small doubt,I created a table Author in oracle and i put primary key constraint on authorid field.And i created another table Article and foreign key constaraint placed on this table's authorid.Now iam inserting data into these two tables through front end.
My doubt is why we have to create
Dim pk(1) As DataColumn
pk(0) = ds.Tables(0).Columns("custid")
ds.Tables("customers").PrimaryKey = pk
This is for primary key.
The below is for foreign key:
Dim fk As ForeignKeyConstraint
fk = New ForeignKeyConstraint("fk", ds.Tables(0).Columns("custid"),
ds.Tables(1).Columns("custid"))
fk.DeleteRule = Rule.Cascade
fk.UpdateRule = Rule.Cascade
ds.Tables(1).Constraints.Add(fk)
ds.EnforceConstraints = True
with out creating this constraints iam getting exception "sys... violated" when iam trying to insert duplicate values.
Then what is the use of above code and when the code will be useful.
Thanks in advance.
|
|
|
|
|
If I understand your situation correctly, it sounds like when you get the error without the contraints THAT error is coming from the actual database. The constraints you are setting up are on the dataset object. The difference being that with the constraints you will throw an error right away when you try to add a duplicate record into the dataset. Without the constraints the error will not be thrown until you try to update the database by using the dataset's update method. At least, this is what it sounds like. I could be wrong.
|
|
|
|
|
Hi Friends,
I have created a stored procedure which copy data from Table1 to Table2 table, its working i am happy.
Now i have to use Table2 in crystal report how can i run that procedure from the crystal report so it copy data from table1 to table2 to use in the crystal report.
if any explaination required pls do not hesitate to ask.
I am stuck help me pls.
Naveed Kamboh
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
