Database

Re: how to check " is not null condition" in a field of single select query

6-Aug-09 1:21

select *
from table
where column is not null

Wout Louwers

anushh6-Aug-09 2:24

anushh

6-Aug-09 2:24

Thanks for your reply.

but i didn't mention like this.
There is any possibilities to check the field as not null that which is to be selected using select query (not in where condition)

Re: how to check " is not null condition" in a field of single select query

WoutL6-Aug-09 3:07

Re: how to check " is not null condition" in a field of single select query

6-Aug-09 3:07

You can use select IsNull(Column, 'Value') from table. But I am not sure if this is what you mean.

Wout Louwers

Blue_Boy6-Aug-09 3:41

Blue_Boy

6-Aug-09 3:41

select isnull(column,'N/A') from tablename where column='condition'

Hope this will help you.

I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.

www.aktualiteti.com

Need Help,METHOD of COLD BACKUP AND RESTORE COLD BACKUP ORACLE 10G

M Riaz Bashir5-Aug-09 20:08

M Riaz Bashir

5-Aug-09 20:08

Hi,

kindly let me know, How may I create cold backup and restore cold backup using Oracle 10g ?

Thank you
(Riaz)

Re: Need Help,METHOD of COLD BACKUP AND RESTORE COLD BACKUP ORACLE 10G

Mycroft Holmes5-Aug-09 22:10

Mycroft Holmes

5-Aug-09 22:10

I had not heard of a cold backup before but there is plenty of resource[^] out there pertaining to this subject.

Never underestimate the power of human stupidity
RAH

SQL server not starting

User 61271795-Aug-09 5:52

User 6127179

5-Aug-09 5:52

I get the following errors when starting my local SQL server:

2009-08-05 11:46:04.68 Server      Error: 17182, Severity: 16, State: 1.
2009-08-05 11:46:04.68 Server      TDSSNIClient initialization failed with error
 0x2, status code 0xd.

2009-08-05 11:46:04.68 spid8s      Starting up database 'model'.
2009-08-05 11:46:04.68 Server      Error: 17182, Severity: 16, State: 1.
2009-08-05 11:46:04.68 Server      TDSSNIClient initialization failed with error
 0x2, status code 0x1.

2009-08-05 11:46:04.69 spid5s      Starting up database 'msdb'.
2009-08-05 11:46:04.69 Server      Error: 17826, Severity: 18, State: 3.
2009-08-05 11:46:04.69 Server      Could not start the network library because o
f an internal error in the network library. To determine the cause, review the e
rrors immediately preceding this one in the error log.

2009-08-05 11:46:04.69 Server      Error: 17120, Severity: 16, State: 1.
2009-08-05 11:46:04.69 Server      SQL Server could not spawn FRunCM thread. Che
ck the SQL Server error log and the Windows event logs for information about pos
sible related problems.

I have looked at a few solutions to the errors such as disabling the VIA protocol in the configuration manager, but it was already disabled. I did discover that the issue seems to be with the TCP/IP protocol, which I can't even enable. I found this page:
http://www.thelastpickle.com/2008/02/28/invalid-server-tcpip-settings/[^]
unfortunately the properties of the TCP/IP protocol are totally missing the “Listen All” property. The only property under TCP/IP is "keep alive". Enabling a single IP doesn't help either.

Any recommendations?

modified 4-Apr-21 21:01pm.

Re: SQL server not starting

Byron70705-Aug-09 16:46

Byron7070

5-Aug-09 16:46

If it was working before and isn't working now, I would look to differences.

Maybe a windows update or other software installed. Maybe windows update is on automatic and you don't know it.

Try rolling any changes back to when things were working.

If it never worked, and this is a "fresh" install, go ahead and remove and reinstall.

Byron Mann
mannb@hostmysite.com
byronosity@gmail.com
Software Architect
hosting.com | hostmysite.com
http://www.hostmysite.com/?utm_source=bb

Re: SQL server not starting

phoenixR7726-Nov-09 3:46

phoenixR77

26-Nov-09 3:46

I had the same problem today.
Found out that the error 0x2 = The specified service does not exist as an installed service.

sc query 2

Only thing that I was able to do was to uninstall the database service then reinstall it.

Remote access problem - sql server2008- Out of Domain

Paramu19735-Aug-09 5:36

Paramu1973

5-Aug-09 5:36

For my project, I have setup a small domain, and arranged a LAN connection.
From my domain, when I connect with my Sqlserver 2008 remotely It's connected, no problem.And the application is running good.

But when I run the program, out of domain not in LAN, it's not connecting with my sql server and showing the error,
"A network-related or instance-specific error occurred while establishing a connection to sql server......(provider; Named pipes provider, error: 40- could not open a connection to sql server).
Iam using the followings as connection string

Dim ConStr As String = "Data Source=1.1.1.1; initial Catalog=Employee_Master; User Id=Codeproject; Password=xxxxxxxx"

It's cnnecting under LAN, but not from remote machines.
Any ideas to fix this problem ?

Re: Remote access problem - sql server2008- Out of Domain

Byron70705-Aug-09 16:37

Byron7070

5-Aug-09 16:37

Two things I would check are if you have a firewall, if the proper ports are open (1433). Other would be if the sql server is listening on 1433 on a public IP address and internal address.

Byron Mann
mannb@hostmysite.com
byronosity@gmail.com
Software Architect
hosting.com | hostmysite.com
http://www.hostmysite.com/?utm_source=bb

Re: Remote access problem - sql server2008- Out of Domain

Paramu19735-Aug-09 23:21

Paramu1973

5-Aug-09 23:21

Hi,
Sorry. Iam zero in this Sql Servers. This my first Project. So can you explain a bit more.

1.Yes. I have a firewall. But how can i understand my ports are open 1433? Any commands....?
2.How can i check my Sqlserver is listening on 1433 on public IP address and internal address..
Can you provide any sample commands?

Thanks

Re: Remote access problem - sql server2008- Out of Domain

Byron70706-Aug-09 6:48

Byron7070

6-Aug-09 6:48

From the server access SQL outside your local network, to tell if the outside server can connect to the port.

From the command line ->telnet SqlServerHostname 1433

If it connects it will blank screen. Hit ctrl+] to escape; otherwise it will just sit there "Connecting to sqlserverhostname" and eventually time out and return you to the command prompt.

On the sql server from the command line issue this command

>netstat -an

sift through the list and look for this in the second column

PublicIP:1433

208.208.208.208:1433 for example, that first column is the ip and ports your server is listening on.

Re: Remote access problem - sql server2008- Out of Domain

Aman786Singh7-Aug-09 2:44

Aman786Singh

7-Aug-09 2:44

Use TCP/IP Provider instead of named pipes.

Regards,
Aman

To fetch the data from different tables

srikantha_nagaraj5-Aug-09 3:45

srikantha_nagaraj

5-Aug-09 3:45

I have two tables and 3 columns in each table.Each column contains 5 rows

T1-- T2--
c1 c2 c3 b1 b2 b3
1 6 11 a b c
2 7 12 d e f
3 8 13 g h i
4 9 14 j k l
5 10 15 m n o

I want data as
Final Table
c1 b2
1 b
2 e
3 h
4 k
5 n

Please suggest solution for this. I have to show this in gridview.

Re: To fetch the data from different tables

Mycroft Holmes5-Aug-09 22:13

Mycroft Holmes

5-Aug-09 22:13

Are you using winforms DataGridView or webforms GridView.

If it is GridView then you need to take this question to the ASP forum.

Never underestimate the power of human stupidity
RAH

Re: To fetch the data from different tables

Kschuler7-Aug-09 9:48

Kschuler

7-Aug-09 9:48

What are the column names, and how does the data in the two tables link to each other? It looks like you are grabbing the first column of the first table and the second column of the second table, so if these are in a database and you are trying to pull the data it will look something like this:

SELECT Table1Column1, Table2Column2 FROM Table 1 JOIN Table 2 ON Table1KeyColumn=Table2KeyColumn

Is this what you were looking for?

stop select case creating new rows?

Tommy Pickersgill5-Aug-09 2:33

5-Aug-09 2:33

I've created a stored procedure which retrives a list of worklogs for a specific week, log time is added together, joined to user table based on UserID and then returned as HoursWorked.

This worked fine as the following code:

ALTER procedure [dbo].[GetAllUsersWhoHaventSubmittedTimeForWeek]
(
@MinimumHours float,
@EndDate datetime,
@AmountOfDaysBack int
)
as
begin
SELECT u.Email, u.FirstName + ' ' + u.LastName AS Usersname, round(sum(WorkDone) / 60, 1)
FROM Users u LEFT OUTER JOIN 
(
	select sum(WorkDone) WorkDone, UserId UserId, WorkUnitTypeId WorkUnitTypeId
	FROM worklog
	WHERE WorkLogDateTime >= dateadd(d, -@AmountOfDaysBack, @EndDate) and WorkLogDateTime <= dateadd(d, 1, @EndDate)
	GROUP BY UserID, WorkUnitTypeId
	HAVING round(sum(WorkDone) / 60, 1) < @MinimumHours
) w
ON u.UserId=w.UserId
WHERE u.userid not in (SELECT User_ID FROM ReportUsers WHERE Status = 0) and u.isactive = 1
GROUP BY w.UserId, u.FirstName, u.LastName, u.Email, WorkUnitTypeId
ORDER BY sum(WorkDone) DESC

END

I then realised WorkDone can sometimes store hours, days or weeks and is defined in 'WorkUnitTypeID' so I added a CASE statement to the Select:

ALTER procedure [dbo].[GetAllUsersWhoHaventSubmittedTimeForWeek]
(
@MinimumHours float,
@EndDate datetime,
@AmountOfDaysBack int
)
as
begin
SELECT u.Email, u.FirstName + ' ' + u.LastName AS Usersname,
	CASE w.WorkUnitTypeId
		WHEN 1 THEN ISNULL(round(sum(w.WorkDone / 60),1), 0) --Minutes
		WHEN 2 THEN ISNULL(round(sum(w.WorkDone),1), 0)		 --Hours
		WHEN 3 THEN ISNULL(round(sum(w.WorkDone * 24),1), 0) --Days
		WHEN 4 THEN ISNULL(round(sum(w.WorkDone * 168),1), 0) --Weeks
		ELSE 0
	END WorkDone
FROM Users u LEFT OUTER JOIN 
(
	select sum(WorkDone) WorkDone, UserId UserId, WorkUnitTypeId WorkUnitTypeId
	FROM worklog
	WHERE WorkLogDateTime >= dateadd(d, -@AmountOfDaysBack, @EndDate) and WorkLogDateTime <= dateadd(d, 1, @EndDate)
	GROUP BY UserID, WorkUnitTypeId
	HAVING round(sum(WorkDone) / 60, 1) < @MinimumHours
) w
ON u.UserId=w.UserId
WHERE u.userid not in (SELECT User_ID FROM ReportUsers WHERE Status = 0) and u.isactive = 1
GROUP BY w.UserId, u.FirstName, u.LastName, u.Email, WorkUnitTypeId
ORDER BY sum(WorkDone) DESC

END

Again this worked but presented a new issue, the procedure now returns multiple rows for the same user but for each of the WorkUnitTypeID - instead of adding them together.

I'm new to SQL in general and have that feeling I've missed something really simple! Any feedback would be much appreciated.

Tommy

WoutL5-Aug-09 3:29

5-Aug-09 3:29

Try replacing

GROUP BY w.UserId, u.FirstName, u.LastName, u.Email, WorkUnitTypeId

Group by u.Email, u.FirstName + ' ' + u.LastName,
	CASE w.WorkUnitTypeId
		WHEN 1 THEN ISNULL(round(sum(w.WorkDone / 60),1), 0) --Minutes
		WHEN 2 THEN ISNULL(round(sum(w.WorkDone),1), 0)		 --Hours
		WHEN 3 THEN ISNULL(round(sum(w.WorkDone * 24),1), 0) --Days
		WHEN 4 THEN ISNULL(round(sum(w.WorkDone * 168),1), 0) --Weeks
		ELSE 0
	END

Wout Louwers

Tommy Pickersgill5-Aug-09 3:51

5-Aug-09 3:51

Thanks for the reply but no joy:

Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause.

WoutL5-Aug-09 9:25

5-Aug-09 9:25

Sorry,

My mistake. Try this group by clause:

Group by u.Email, u.FirstName + ' ' + u.LastName

Wout Louwers

Tommy Pickersgill5-Aug-09 6:46

5-Aug-09 6:46

Can anyone else offer any more on this? Sorry for the spam but it's quite urgent. I've tried playing about with it more by placing the case inside the JOIN - which I got my hopes up about but still the same result of recieving multiple user rows with different worklogs Mad | :mad:

ALTER procedure [dbo].[GetAllUsersWhoHaventSubmittedTimeForWeek]
(
@MinimumHours float,
@EndDate datetime,
@AmountOfDaysBack int
)
as
begin
SELECT	u.Email,
		u.FirstName + ' ' + u.LastName AS Usersname,
		w.WorkDone
from Users u LEFT OUTER JOIN 
	( 
	Select	UserID,
			WorkUnitTypeId,
			CASE WorkUnitTypeId
			WHEN 1 THEN ISNULL(round(sum(WorkDone / 60),1), 0)	--Minutes
			WHEN 2 THEN ISNULL(round(sum(WorkDone),1), 0)		--Hours
			WHEN 3 THEN ISNULL(round(sum(WorkDone * 24),1), 0)	--Days
			WHEN 4 THEN ISNULL(round(sum(WorkDone * 168),1), 0)	--Weeks
			ELSE 0
			END as WorkDone
			FROM worklog
			WHERE WorkLogDateTime >= dateadd(d, -@AmountOfDaysBack, @EndDate) and WorkLogDateTime <= dateadd(d, 1, @EndDate)
			--HAVING round(sum(WorkDone) / 60, 1) < @MinimumHours
			GROUP BY	UserId, 
						WorkUnitTypeId
	) w
ON u.UserId=w.UserId
WHERE u.userid not in (SELECT User_ID FROM ReportUsers WHERE Status = 0) and u.isactive = 1
GROUP BY w.UserId,
			u.FirstName,
			u.LastName,
			u.Email,
			WorkUnitTypeId,
			WorkDone
ORDER BY sum(WorkDone) DESC

END

Re: stop select case creating new rows? [modified]

i.j.russell5-Aug-09 11:39

i.j.russell

5-Aug-09 11:39

Try this

ALTER procedure [dbo].[GetAllUsersWhoHaventSubmittedTimeForWeek]
(
@MinimumHours float,
@EndDate datetime,
@AmountOfDaysBack int
)
as
begin
SELECT u.Email,
u.FirstName + ' ' + u.LastName AS Usersname,
w.WorkDone
from Users u LEFT OUTER JOIN
(
Select UserID,
Sum(CASE WorkUnitTypeId
WHEN 1 THEN ISNULL(round(sum(WorkDone / 60),1), 0) --Minutes
WHEN 2 THEN ISNULL(round(sum(WorkDone),1), 0) --Hours
WHEN 3 THEN ISNULL(round(sum(WorkDone * 24),1), 0) --Days
WHEN 4 THEN ISNULL(round(sum(WorkDone * 168),1), 0) --Weeks
ELSE 0
END) as WorkDone
FROM worklog
WHERE WorkLogDateTime >= dateadd(d, -@AmountOfDaysBack, @EndDate) and WorkLogDateTime <= dateadd(d, 1, @EndDate)
--HAVING round(sum(WorkDone) / 60, 1) < @MinimumHours
GROUP BY UserId
) w
ON u.UserId=w.UserId
WHERE u.userid not in (SELECT User_ID FROM ReportUsers WHERE Status = 0) and u.isactive = 1
GROUP BY w.UserId,
u.FirstName,
u.LastName,
u.Email,
WorkUnitTypeId,
WorkDone
ORDER BY sum(WorkDone) DESC

END

Tommy Pickersgill5-Aug-09 23:06