|
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
|
|
|
|
|
Hi,
kindly let me know, How may I create cold backup and restore cold backup using Oracle 10g ?
Thank you
(Riaz)
|
|
|
|
|
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
|
|
|
|
|
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.
|
|
|
|
|
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
|
|
|
|
|
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.
|
|
|
|
|
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 ?
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
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.
|
|
|
|
|
Use TCP/IP Provider instead of named pipes.
Regards,
Aman
|
|
|
|
|
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.
|
|
|
|
|
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
|
|
|
|
|
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?
|
|
|
|
|
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
|
|
|
|
|
Try replacing
GROUP BY w.UserId, u.FirstName, u.LastName, u.Email, WorkUnitTypeId by
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
|
|
|
|
|
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.
|
|
|
|
|
Sorry,
My mistake. Try this group by clause:
Group by u.Email, u.FirstName + ' ' + u.LastName
Wout Louwers
|
|
|
|
|
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 
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
|
|
|
|
|
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
|
|
|
|
|
[deleted]
Got it working! Bascilly deleted it and started again and managed to get it working from the advice you both gave (Wrapping the case in a sum and removing Workunittypeid from the top level group by).
Thanks for the help - first and last stored procedure I attempt 
modified on Thursday, August 6, 2009 5:45 AM
|
|
|
|
|
Hi,
How we can design a database tables that record hierarchical data. The levels of hierarchy are not fixed. For example:
Table Header:
A B C D E F G SomeOtherHeader
Respective record:
1 4 3 2 6 t a PK_of_Another_Table
2 3 4 6 8 y a PK_of_Another_Table
Now the record has upto Level "G" with different values. But it can be up to different level for another block of record.
So what is best relational implementation of this Dynamic Hierarchy?
Please refer any link that is worth reading related to this topic.
Thanks.
Good Day.
|
|
|
|
|
Hello,
I am developping ASP.NET 2008. My data is stored in SQL server 2008.
The problem is when I query, I got 0 records and this is because the data is stored in hebrew.
I tried to change the collation property in Column of the table but it doesn't help
Please help
Shay Noy
|
|
|
|
|
Hi--can you post your code so we can see if it might be something with that?
|
|
|
|
|
Private Function GetPathPicturesFromDb(ByVal Period As String, ByVal Location As String) As String
Dim ds As New DataSet("Pictures")
Dim strSQL As String = "SELECT FullFileName FROM PicturesTable " & _
"WHERE PERIOD='" & Period & "' " & _
"AND LOCATION='" & Location & "'"
Dim sqlConnection As New SqlConnection(ConfigurationManager.ConnectionStrings("MemberShipConnectionString").ConnectionString)
sqlConnection.Open()
Dim da As New SqlDataAdapter()
da.SelectCommand = New SqlCommand(strSQL, sqlConnection)
da.Fill(ds)
If ds.Tables(0).Rows.Count > 0 Then _
Return ds.Tables(0).DefaultView(0).Row("FullFileName").ToString.Trim
Return ""
End Function
The Location field contains hebrew words and when I am querying by changing the database content in english it works perfectly with the same code I post upstair. So I am sure that the problem is because the hebrew. I need to config anywhere that the Location column can contain hebrew characters. Again, I success to insert hebrew words in the databse but when I am querying no records are found
Thanks
Shay Noy
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
