Database Discussion Boards

Query a DB for a field value on SQL 2005 STD SP2

Hulicat6-Aug-09 7:43

6-Aug-09 7:43

This does not seem to work; I dont know which table the value is in.

USE DB
Go
SELECT *
FROM sys.Tables where fieldid = '%'
GO

I presume I cannot use a conditional clause in that statement?

It will parse but errors out

Regards,
Hulicat

Re: Query a DB for a field value on SQL 2005 STD SP2

David Mujica6-Aug-09 8:06

David Mujica

6-Aug-09 8:06

This is what I would use ...

select SO.NAME AS TABLENAME, SC.NAME AS COLNAME 
from sysobjects so, syscolumns sc 
where so.id = sc.id and sc.name = 'DESCRIPTION'

This query will give you a listing of every table that has a field that is named, 'DESCRIPTION'.

Good luck,
david

Re: Query a DB for a field value on SQL 2005 STD SP2

Hulicat6-Aug-09 8:22

Hulicat

6-Aug-09 8:22

Thanks!

Regards,
Hulicat

problem with creating view?

shankbond6-Aug-09 2:45

shankbond

6-Aug-09 2:45

Hi, I need help with gridview?

actually the gridview is supposed to show a view(aka join) consisting of two tables .

In my case I have (userid) , (money deposited) as fields in table 1; userid can also be left as null.

I have another table I have first_name, last_name,phone numbers( fields) in table2

I want to display first_ name ,money deposited.
in the gridview.

It has to show all the enteries made in table 1 either it is null or has some userid; if it becomes null , then a space should appear instead of displaying null.

can anyone please guide me how to do this?

thanks

Re: problem with creating view?

Ashfield6-Aug-09 8:57

Ashfield

6-Aug-09 8:57

try something like this - I have to assume that userid appears in table3 also, therwise this is no way to join them

select isnull(first_name,' '), [money deposited]
from table1 t1
left outer join table2 t2 on t2.userid = t1.userid

Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP

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

anushh6-Aug-09 0:45

anushh

6-Aug-09 0:45

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

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

WoutL6-Aug-09 1:21

WoutL

6-Aug-09 1:21

select *
from table
where column is not null

Wout Louwers

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

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

WoutL

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

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

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

Tommy Pickersgill

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

Re: stop select case creating new rows?

WoutL5-Aug-09 3:29

WoutL

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

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.