|
Friends I have written a Stored Procedure like below. But It's not working. Can Any1 help me solving this plz
create procedure sp_victim_status_by_casenumber
SELECT District, Thana, CaseNumber, COUNT(IdVictim) AS Male, 0 AS Victim
into #tmp4
FROM tblVictim
WHERE Gender = 'Male'
GROUP BY District, Thana, CaseNumber
insert into #tmp4
SELECT District, Thana, CaseNumber, 0 AS Victim, COUNT(IdVictim) AS Male
FROM tblVictim
GROUP BY District, Thana, CaseNumber
SELECT District, Thana, CaseNumber, COUNT(IdVictim) AS Female
into #tmp4
FROM tblVictim
WHERE Gender = 'Female'
GROUP BY District, Thana, CaseNumber
insert into #tmp4
SELECT District, Thana, CaseNumber,0 AS Victim, COUNT(IdVictim) AS Female
FROM tblVictim
GROUP BY District, Thana, CaseNumber
select District, Thana, CaseNumber,sum (Victim) as Victim, sum( Male) as Male, sum(Female) as Female
from #tmp4
group by District, Thana, CaseNumber
Any help would be really helpful.
|
|
|
|
|
SELECT INTO's only work on tables that dont exist when the command is run, you can use a SELECT INTO #tmp4 once but it wont work a second time beacuse #tmp4 will exist. also the columns in your insert into select statement arent in the same order as the select into, since the select into will create the table with the column order specified you need to make sure your column order in the inserts match, or you'll get wierd data. the better solution is to list out the columns from the table you're insterting into i.e. INSERT INTO (col1, col2, col3..) SELECT col1, col2, col3...
that way the order in the table wont matter as long as each column list matches. I dont know what you are trying to accomplish but you may be able to use self joins, or the NULLIF command to figure it out without a temp table. Hope this helps.
Please remember to rate helpful or unhelpful answers, it lets us and people reading the forums know if our answers are any good.
|
|
|
|
|
Thanks, That made many things clear to me.
|
|
|
|
|
Hello All,
I have a sudden problem. I have my MS SQL Server 2005 on my local development machine and about 7 DB's on it. Well, I have a .net application that accesses 2 of the DB's. The connection to the first one opens just fine, but I get an error on the second connection when I attempt to open it: "Cannot open database "xyz" requested by the login. The login failed". The really strange thing is that I can connect to the DB's from VS 2005 via the Server Explorer (and yes I made sure that the connection string in the Server Explorer and my code jives), but then it kicks back that error in my try block. The following is the error that is generated in the sql server log:
2008-07-01 06:42:55.12 Logon Error: 18456, Severity: 14, State: 16.
2008-07-01 06:42:55.12 Logon Login failed for user 'DENNIS-CB848706\Dennis'. [CLIENT: <local machine="">]
Any help or insight on this problem would be greatly appreciated,
Dennis
|
|
|
|
|
Hi All,
I will need you all professional expertise here. I have a database (Mssql server 2000) on a box that has been running for a year now, but suddenly crashed and some data were lost... The backup (Mssql server 2000) has been re-instored on a new box A. so we want to replicate the same data on box A to a new sql server 2005 on box B real time.
I will appreciate it if you guys can assist me by letting me know how to do data replication from mssql server 2000 to mssql server 2005.
Thanks.
|
|
|
|
|
I am displaying a report that has table control on it.
I want that the table should display first 5 rows on the first page of the report , next 5 on other page and so on.
Can anyone help me with this?
Thanks in Advance.
|
|
|
|
|
S.No Name city
1 N1 C1
2 N2 C2
3 N3 C3
4 N4 C2
5 N5 C1
from the above table , I want the data in the following format
List the total no of names as per the city
S.No City Total
1 C1 2
2 C2 2
3 C3 1
How should i start to get in mimimal queries ?
Should I get all these things in stored procedure or an inline multivalues function
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!
|
|
|
|
|
Use group by and count in your query.
Giorgi Dalakishvili
#region signature
my articles
#endregion
|
|
|
|
|
in a single query !!
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!
|
|
|
|
|
What have you tried so far?
Giorgi Dalakishvili
#region signature
my articles
#endregion
|
|
|
|
|
Select
City
,Count(Name)
From #c
group by City
Replace #c with your table's name.
|
|
|
|
|
Hi all,
Please kindly suggest me a sql query to get all the second occuring of a row using foreign key.
For eg:
id fid priority updated
1 123 1 2008-06-29
2 123 2 2008-06-29
3 123 1 2008-06-25
4 124 1 2008-06-30
5 124 1 2008-06-29
6 124 2 2008-06-29
Using the foreign key fid and the updated how to get the second row for all the fid
Condition:
i. the priority should be 1
ii. the update value should should be the minimum than the first row
Sample o/p:
the output should be like this
3 123 1 2008-06-25
5 124 1 2008-06-29
Please help me
Thanks in advance
Know is Drop, Unknown is Ocean
|
|
|
|
|
you just want the MIN(updated) for each FID where priority = 1
so..
SELECT Id, Fid, Priority, Updated
FROM table a
WHERE Priority = 1
AND a.Updated = (SELECT MIN(Updated)
FROM table
WHERE Fid = a.Fid
AND Priority = 1)
might work...i havnt tested it...and i know its a bad way to do things...but its the first that came to mind...you can do it with a join, i just dont want to think that hard right now.
Please remember to rate helpful or unhelpful answers, it lets us and people reading the forums know if our answers are any good.
|
|
|
|
|
when I am trying to connect to remotely located sqlserver-2000 from asp.net (VS 2005) I am getting error msg "login failed to user XXXX".
I am using the following connection string
<add name="masterConnectionString">
connectionString="Data Source=ip;Network Library=DBMSSOCN
;Initial Catalog=dbname;Persist Security Info=False;User ID=user;Password=Xyz"
providerName="System.Data.SqlClient"></add>
Where am I going wrong ? our db server is located in 3rd party system. where we don't have any direct access to it to do any settings. what can I ask them to do?
Thank U all,
kiranmayi
|
|
|
|
|
ask the third party admin to thier sql server default username and password, Use that username and password in your conection string, it will definately work
|
|
|
|
|
Hi,
Does any one know if SQL Server 2008 Express is available yet? I tried looking for a download link but could find none.
Thanks
Brendan
|
|
|
|
|
|
RC0 version is available. Been using the February 2008 CTP for a while and does fine here.
"The clue train passed his station without stopping." - John Simmons / outlaw programmer
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
When executing a stored procedure in .NET code, are all statements inside the procedure considered to be a unit of work, or do I have to run this procedure inside a transaction (either by modifying the procedure, or by starting and comitting a transaction in .NET code)?
Thanks in advance!
|
|
|
|
|
In SQL Server, each individual statement, whether executed as a single batch, as a statement within a batch, or within a stored procedure, is independent. If you need the ACID properties to apply over multiple statements, you need to create a transaction.
In a stored procedure this is easiest to do with the BEGIN TRANSACTION statement. Use COMMIT TRANSACTION when you're done, to keep the results, or ROLLBACK TRANSACTION to discard them. Be aware that the transaction is not automatically rolled back for non-fatal errors, that is, errors that do not abort the batch. You should check @@ERROR after each statement to check whether there was an error.
DoEvents: Generating unexpected recursion since 1991
|
|
|
|
|
|
Hi,
I have an application which uses Pervasive SQL as a backend.
Is there any tool for Pervasive similar to Microsoft's Profiler for MS SQL server?
i would like to monitor the queries being executed behind the application?
Please help me.
Akash Agarwal
modified on Monday, June 30, 2008 9:27 AM
|
|
|
|
|
sql schema tblstudent(sid,sname)
i need a query like: select count(sid),count(sname) from tblstudent where sname=john;
Where count(sid) represent total number of student.
anyone help me plz...
|
|
|
|
|
Are you getting any error while executing this query? What's your requirement? Try modify the query as:
Select count(sid), count(sname) from tblstudent where sname = john group by sid, sname.
Success is the good fortune that comes from aspiration, desperation, perspiration and inspiration.
|
|
|
|
|
mdgkabir wrote: sql schema tblstudent(sid,sname)
i need a query like: select count(sid),count(sname) from tblstudent where sname=john;
Where count(sid) represent total number of student.
It looks like you just need to know how many rows with "john" are there in your table...
Try this:
select count(sid) from tblStudent where sName='john';
HTH
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
![Java | [Coffee]](https://www.codeproject.com/script/Forums/Images/coffee.gif)
