|
I did now, with no joy. The execution plan isn't too helpful either because I can't search it.
|
|
|
|
|
Put a try - catch block in the starting SP and use the ERROR_PROCEDURE() function.
Thanks.
|
|
|
|
|
That's cool to know, thanks, but in this instance ERROR_PROCEDURE() returns null. 
BEGIN CATCH
ROLLBACK TRANSACTION
SELECT ERROR_PROCEDURE() AS EP
END CATCH
|
|
|
|
|
Sorry, at the moment I don't really have any other suggestions. I've usually had pretty good luck with Profiler. Are you filtering anything out? Perhaps try saving the trace to a text file and searching that?
|
|
|
|
|
Thanks anyway. It turns out that the one proc actually loads dynamic SQL from a table and executes it.
|
|
|
|
|
Well, that's just cheating. 
Anyway, glad you found it.
|
|
|
|
|
Why CTT*.tmp files are generating in %temp% while runing MySQL with ODBC connectivity.
The CTT*.tmp are growing in size as records are growing.
It is reaching upto 2GB.
Due to this my C drive is filling up and leading to crash.
Please help me out by telling what are CTT*.tmp files?
and why are they generated?
|
|
|
|
|
Hi,
Anyone here successfully able to install SQL Server on Vista? I can't seem to install the Databae Engine component. Each time the install finishes, I can't find the service in SCM. Anyone been through the same experience?
(I have Windows Vista Enterprise SP1 and I am trying to install SQL Server 2005 Enterprise)
TIA!
----------------------------------------------------------
Every nation state's armed forces call themselves 'Defence',
makes me wonder why there are conflicts in the world.
modified on Monday, July 7, 2008 9:29 AM
|
|
|
|
|
Hmm - unfortunately I can't give you other help then yes - I've installed SQL Server (developer edition) on my Vista (x64).
I did not have any such problems as you mention - but I do recall there was a large service pack (for SQL Server 2005 for Vista.
Don't know if that might be the way to go
|
|
|
|
|
student table
id stu
1 s1
2 s2
subject table
id sub
1 sub1
2 sub2
student-subject table
id sid subid marks
1 1 1 100
2 1 2 100
3 2 1 100
My question is what shall be the query for those students who did not appear in all exams
If you have an apple & I have an apple and we exchange our apples, then each of us will still have only one apple but if you have an idea & I have an idea and we exchange our ideas, then each of us will have two ideas!
|
|
|
|
|
SELECT stu
FROM student
WHERE id IN (
SELECT sid
FROM [student-subject]
GROUP BY sid
HAVING COUNT(*) < (SELECT COUNT(*) FROM subject)
)
|
|
|
|
|
Hello,
I have a table with one column "Product" which is primary column .
I want to make another table with fields TimePeriod, Qty and the products from Product table.
Product
Shirts
Trousers
Tie
Belts
Now I want to make this Table with following fields
TimePeriod Qty Shirts Trousers Tie Belts
Is there any query that I can write or I will have to hardcode the product names into the table
Regards
Pritha
|
|
|
|
|
Look up Pivot Tables.
Before you implement this, I suggest you decide why you want the table this way, and what might happen if you have, say, 40,000 records in your Products table.
|
|
|
|
|
Hello,
Thanks for your reply
But I am not going to have more than 10 products .
Is the Pivot Tables a link .
But it does not work
Regards,
Priya
|
|
|
|
|
prithaa wrote: But I am not going to have more than 10 products .
Is the Pivot Tables a link .
But it does not work
No, it's not a link - by look up, I meant Google it or BOL or some such reference.
|
|
|
|
|
Hi,
Here is a working code for your case using <big>PIVOT</big> and FOR XML PATH.
The list of columns is built with FOR XML PATH
Pivot columns are created <big>dynamically</big>, so doesn’t really matter how many columns (in your case – how many products) you have.
I hope this solve your problem.
CREATE TABLE #p (ProdID int, ProdName nvarchar(20));
CREATE TABLE #s (TimePeriod int, Qty int, ProdID int);
insert into #p (ProdID, ProdName) select 1, 'Shirts';
insert into #p (ProdID, ProdName) select 2, 'Trousers';
insert into #p (ProdID, ProdName) select 3, 'Tie';
insert into #p (ProdID, ProdName) select 4, 'Belts';
insert into #s (TimePeriod, Qty, ProdID) select 20080705,138,1;
insert into #s (TimePeriod, Qty, ProdID) select 20080706,539,1;
insert into #s (TimePeriod, Qty, ProdID) select 20080707,313,1;
insert into #s (TimePeriod, Qty, ProdID) select 20080705,660,2;
insert into #s (TimePeriod, Qty, ProdID) select 20080706,370,2;
insert into #s (TimePeriod, Qty, ProdID) select 20080707,574,2;
insert into #s (TimePeriod, Qty, ProdID) select 20080705,764,3;
insert into #s (TimePeriod, Qty, ProdID) select 20080706,294,3;
insert into #s (TimePeriod, Qty, ProdID) select 20080707,202,3;
insert into #s (TimePeriod, Qty, ProdID) select 20080705,59,4;
insert into #s (TimePeriod, Qty, ProdID) select 20080706,247,4;
insert into #s (TimePeriod, Qty, ProdID) select 20080707,731,4;
DECLARE @cmd NVARCHAR(max);
SET @cmd = 'SELECT TimePeriod ' +
( SELECT ' , SUM(['+CONVERT(NVARCHAR,ProdID)+']) AS ['+ ProdName +']'
FROM #p AS p
FOR XML PATH('')
)
+
' FROM(
SELECT #s.TimePeriod, #s.Qty, #p.ProdID, #p.ProdName
FROM #p
INNER JOIN #s on #p.ProdID = #s.ProdID
) p
PIVOT (
SUM( Qty )
FOR ProdID IN
( [0] '+
( SELECT ' , ['+CONVERT(NVARCHAR,ProdID)+']'
FROM #p AS p
FOR XML PATH('')
)
+
' )
) AS pvt
GROUP BY TimePeriod
ORDER BY TimePeriod ; ' ;
PRINT @cmd;
EXEC(@cmd);
DROP TABLE #p;
DROP TABLE #s;
|
|
|
|
|
Hello,
Many many thanks for all your answers.Just a last question about the code
What does the following mean ?
select 20080705,138,1;
Regards,
Pritha
|
|
|
|
|
<br /> You have there a #s table defined like:
CREATE TABLE #s (TimePeriod <big>INT</big>, Qty <big>INT</big>, ProdID <big>INT</big>);
Then you have some dummy data inserts like
INSERT INTO #s ( TimePeriod, Qty, ProdID )
SELECT 20080705, 138, 1 ;
or (if you understand better this syntax)
INSERT INTO #s ( TimePeriod, Qty, ProdID )
VALUES ( 20080705, 138, 1 );
You can change TimePeriod field type to DATETIME or whatever suits your needs better.
Let me know if you have any difficulties understanding the code.
modified on Wednesday, July 9, 2008 10:56 AM
|
|
|
|
|
Hi,
In my table
Date Amount TotalAmount
01/07/2008 10000 10000
01/07/2008 10000 5000
01/07/2008 10000 0
I am using the following queries separately.
Select Date, Count(*), Sum(Amount) From Table Group by Date
Select Date, Count(*), Sum(Amount) From Table Where Amount>TotalAmount And TotalAmount <> 0 Group by Date
Select Date, Count(*), Sum(Amount) From Table Where Amount>TotalAmount And TotalAmount = 0 Group by Date
How can I execute the above 3 mentioned separate queries in a single query to get the result like
Date TotalRecords Amount1 TotalRec Amount2 Tot Amount2
01/07/2008 3 30000 1 10000 1 1000
Tnx in adv.
Balasubramanian K.
|
|
|
|
|
BalasubramanianK wrote: In my table
Date Amount TotalAmount
01/07/2008 10000 10000
01/07/2008 10000 5000
01/07/2008 10000 0
I am using the following queries separately.
Select Date, Count(*), Sum(Amount) From Table Group by Date
Select Date, Count(*), Sum(Amount) From Table Where Amount>TotalAmount And TotalAmount <> 0 Group by Date
Select Date, Count(*), Sum(Amount) From Table Where Amount>TotalAmount And TotalAmount = 0 Group by Date
How can I execute the above 3 mentioned separate queries in a single query to get the result like
Date TotalRecords Amount1 TotalRec Amount2 Tot Amount2
01/07/2008 3 30000 1 10000 1 1000
You are probably looking for something like this[^]
|
|
|
|
|
How to write the SQL DB connection string in namespace file.Then i wanna add that connection string like "using DBconn;" <<DbConn should be connection string>>
If you know that how to write DB Connection string using webconfig file also welcome.
BD is MSSQL 2005
Programing language : C#
Web development using ASP.Net
If some one know please reply me
Thank you..!!!
ReGRdZZZ
CheeN
|
|
|
|
|
|
difference between sql server 2000 and 2005, i am going to for quick review, if any once can tell me in simplified manner i will really appreciate him/her.
please max avoid to give links. please give quick reference differences between those
This is haneef.............................................................
|
|
|
|
|
Hi Good Guys!
I want to write a trigger which can iterate through each udpated column and check for a string match. if found then I need to rollback.
TIA
puranonnet@hotmail.com
www.ibrinte.com
|
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
