|
Doing dishes is definitely underrated.  
|
|
|
|
|
Why don't you just limit access?
The database server should be on a secured box anyways and logins to that box should be limited to only a few people in the organization.
All other access to the database are via login credentials.
|
|
|
|
|
Hi,
I have to convert Sql Query Result Into Report like crystal report or pdf format.
Suppose I run query
select EmpName, EmpDepartment from MasterDepartment.
EmpName EmpDepartment
Rakesh IT
Shirish IT
Shalu HR
This way I want to print in PDF file
|
|
|
|
|
thakrerakesh wrote: This way I want to print in PDF file
Then you need to use some library to produce the PDF output; take a look at iTextSharp[^].
|
|
|
|
|
if i apply a NOLOCK on one table in a Stored Procedure then will it be applicable to all other tables consumed in that Stored Procedure too automatically
- Happy Coding -
Vishal Vashishta
|
|
|
|
|
|
Ok ... but can you make me clear little bit more about the NOLOCK concept,any article you want to refer.?
- Happy Coding -
Vishal Vashishta
|
|
|
|
|
Sorry to take so long to get back with you but I was on vacation.
Take a look at:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
Since NOLOCK is a 'hint' it is only for the one command.
|
|
|
|
|
I have a table in SQL Server 2008 like this:
EventID UserID State
1 0987 A
2 0987 A
3 0987 B
4 0956 A
5 0987 A
6 0019 A
7 0019 B
8 1289 B
9 1289 B
10 0019 A
I want to get some results from this table in such form:
UserID StateCount_of_A StateCount_of_B
0987 3 1
0019 2 1
1289 0 2
0956 1 0
I have try a few days, but I could get a disappointed result for ony one UserID. My result likes this one, for example with UserID 0987:
StateCount_of_A 3
StateCount_of_B 1
Thanks for anyone who could give me some tip.
|
|
|
|
|
Try ...
Select EventID,(select count(*) from <myeventtable> E2 where E1.EventID = E2.EventID) As Count_of_A, (select count(*) from <myeventtable> E3 where E1.EventID = E3.EventID) As Count_of_B,
from <myeventtable> E1
Remeber to Vote. 
|
|
|
|
|
You seemed to have picked up a stalker or someone objects to the unformatted code or you are getting a reaction to your sig!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I haven't tried this, however my first try would look like this:
SELECT DISTINCT UserID, StateCount_of_A, StateCount_of_B FROM tablename AS T1
LEFT JOIN (SELECT UserID, COUNT(*) AS StateCount_of_A FROM tablename AS TA WHERE State='A') ON TA.UserID = T1.UserID
LEFT JOIN (SELECT UserID, COUNT(*) AS StateCount_of_B FROM tablename AS TB WHERE State='B') ON TB.UserID = T1.UserID
ORDER BY UserID
(Actually I would try a subset of it with a single state first, then expand for the exact requirements).
The fundamental trick is to use the same table more than once, and give each "instance" a different name using the AS keyword.
And then one might have to throw in a couple of COALESCE functions to replace null counts by zeroes.
|
|
|
|
|
SELECT UserId
,SUM(StateA) StateCount_of_A
,SUM(StateB) StateCount_of_B
FROM (
SELECT UserId
,CASE WHEN [State]='A' THEN 1 ELSE 0 END StateA
,CASE WHEN [State]='B' THEN 1 ELSE 0 END StateB
FROM Junk
) T
GROUP BY UserID
|
|
|
|
|
Hello,
I cannot register new server in SQL Server 2008. I can save it but cannot start all the items (Start, Stop, ...) under Service Control menu appear disabled.
|
|
|
|
|
Kujtim Hyseni wrote: I cannot register new server in SQL Server 2008
What does that mean exactly?
Are you saying that you INSTALLED sql server and start is not enabled in the windows services control panel applet?
|
|
|
|
|
No, my 'base' or don't know how to call it server works fine and I am connecting to it as usual via 'Microsoft SQL Server Management Studio'. But when I enter to 'Microsoft SQL Server Management Studio' and try to register new server i.e. from the menu I choose from menu 'Register...' then appears the form 'New Server Registration' I cannot save this new server but cannot start it.
|
|
|
|
|
When you went to the register dialog did you hit 'test'?
If you did and it worked then can you log into the other box and start and stop the server from there? (Presumably you are using windows authentication.)
|
|
|
|
|
Yes I hit 'test' but I got the following message:
-------------------
Testing the registered server failed. Verify the server name, login credntials, and database, and then click Test again.
Additional information:
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40- Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 53)
Yes I am using Windows authentication (in fact I can login via 'sa' also).
|
|
|
|
|
Kujtim Hyseni wrote: Yes I hit 'test' but I got the following message:
So of course that is why you cannot now interact with it.
|
|
|
|
|
|
Kujtim Hyseni wrote:
And ... ?
And what?
You registered a server to which you do not have access. Thus you cannot start it.
As to why you do not have access it could either be because of permissions or because it does not exist.
|
|
|
|
|
Right-click the icon for "Sql Management Studio", choose "Run as Administrator". That solved a similar problem I had under Sql Server 2005, should not be required for 2008 though.
Since you can connect using the 'sa', I'd assume that the configuration is correct (TCP/IP being enabled) and that you have the name of the correct instance on the server. Do you have rights to start/stop the Sql Server Service when you open the "Services" list from the Configuration Panel?
Bastard Programmer from Hell 
|
|
|
|
|
I have a db with following values :
col1 col2 col3 position
aaa rack1 1-3 3
bbb rack2 22-23 2
ccc rack3 19-20 2
Output should be
col1 col2 col3 position
aaa rack1 1-3 1
aaa rack1 1-3 2
aaa rack1 1-3 3
bbb rack2 22-23 22
bbb rack2 22-23 23
ccc rack3 19-20 19
ccc rack3 19-20 20
Basically, its taking the position and creating a row for each value from col3.
Can i do this on-fly and create a temp table? or i have to create a procedure?
thanks!!!
VK
|
|
|
|
|
If you are doing this in the database you are going to have to write some code, you can't magic it into the multiple row. I would look at the method of inserting the original record and change or enhance that method.
Alternatively you can use an insert trigger spit, I would do this as a last resort.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Try this
CREATE TABLE #Temp
(
ID INT IDENTITY(1,1),
col1 VARCHAR(50),
Col2 VARCHAR(50),
Col3 VARCHAR(50),
position VARCHAR(50)
)
CREATE TABLE #Temp2
(
col1 VARCHAR(50),
Col2 VARCHAR(50),
Col3 VARCHAR(50),
position VARCHAR(50)
)
INSERT INTO #Temp
SELECT 'aaa', 'rack1', '1-3', '3' UNION
SELECT 'bbb', 'rack2', '22-23', '2' UNION
SELECT 'ccc', 'rack3', '19-20', '2'
DECLARE @TotalRowCount INT
DECLARE @RowCount INT
DECLARE @Positon INT
DECLARE @I INT
SET @RowCount = 1
SELECT @TotalRowCount = COUNT(*) FROM #Temp
WHILE @RowCount <= @TotalRowCount
BEGIN
SELECT @Positon = Position FROM #Temp WHERE Id = @RowCount
SET @I = 1
WHILE @I <= @Positon
BEGIN
INSERT INTO #Temp2
SELECT Col1, Col2, Col3, (SUBSTRING(Col3,0,CHARINDEX('-',Col3)) + @I - 1) FROM #Temp WHERE Id = @RowCount
SET @I = @I + 1
END
SET @RowCount = @RowCount + 1
END
SELECT * FROM #Temp2
DROP TABLE #Temp
DROP TABLE #Temp2
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
