|
If you did a normal install then the instance name should be the name or IP of the server it was installed on. If you installed a specific instance the name of the server should be servername/instancename. Never underestimate the power of human stupidity
RAH
|
|
|
|
|
thanks so much for your reply.
but can you tel me how i can change instance name??
please explain more i am a beginner...M.Alizadeh
|
|
|
|
|
mehrnoosh wrote: please explain more i am a beginner
Then you need to do some research, there are plenty of resources on the web and you should have Books On Line (BOL) with your SQL Server. Use them, research instancename.
I think you need to reinstall if you need to change the instance name but I'm not sure.Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hello Friends,
I've a date in string variable i want to convert this string into datetime format(mm/dd/yyyy) but it's giving me an error. Because my date is stored in dd/mm/yyyy but sql analyser require datetime in mm/dd/yyyy.
So please suggest me the way. Here is my example
Declare @str varchar(100),@strDate datetime
Set @str='14/02/2010'--(dd/mm/yyyy)
Set @strDate=@str
Print @strDate
|
|
|
|
|
Use the following
Declare @str varchar(100),@strDate datetime
Set @str='14/02/2010'--(dd/mm/yyyy)
Set @strDate=CONVERT(DATETIME, @str,103)
Print @strDate
Look into CONVERT in BOLNever underestimate the power of human stupidity
RAH
|
|
|
|
|
Try this
Declare @str varchar(100)
Set @str='14/02/2010'--(dd/mm/yyyy)
select ConvertedDate = CONVERT(varchar(10),CONVERT(datetime,@str,103),101)--mm/dd/yyyy
Output:
ConvertedDate
02/14/2010
Niladri Biswas
|
|
|
|
|
try the below
SELECT CONVERT(VARCHAR(10), GETDATE(), 103) AS [DD/MM/YYYY]
|
|
|
|
|
.
.
.
SELECT PD_Users.FirstName as FirstName, PD_Users.LastName as LastName, PD_Users.UserName as UserName, PD_Users.CityID as CityID,
PD_Cities.CityName as CityName, aspnet_Membership.CreateDate as CreateDate,
(SELECT COUNT(*) FROM PD_Articles WHERE (PD_Users.UserName = UserName)) AS ArticleCount,
(SELECT COUNT(*) FROM PD_News WHERE (PD_Users.UserName = SenderName AND IsDevNews = 1)) AS NewsCount,
(SELECT COUNT(*) FROM PD_News WHERE (PD_Users.UserName = SenderName AND IsDevNews = 0)) AS ITNewsCount,
(SELECT COUNT(*) FROM PD_ForumMessage WHERE (PD_Users.UserName = UserName)) AS MessageCount,
CASE WHEN aspnet_Users.LastActivityDate > @DateActive THEN cast(1 as bit) ELSE cast(0 as bit) end AS IsOnline,
ROW_NUMBER() OVER (ORDER BY aspnet_Membership.CreateDate ) AS RowNumber
FROM aspnet_Users INNER JOIN aspnet_Membership ON aspnet_Users.UserId = aspnet_Membership.UserId INNER JOIN
PD_Users INNER JOIN PD_Cities ON PD_Users.CityID = PD_Cities.CityId ON aspnet_Users.UserName = PD_Users.UserName
WHERE (@LastName = PD_Users.LastName OR (@LastName = '' AND 1=1))
AND (@UserName = PD_Users.UserName OR (@UserName = '' AND 1=1))
AND (@CityID = PD_Users.CityID OR (@CityID = -1 AND 1=1))
AND (@UserStatus = IsOnline OR (@UserStatus = 0 AND 1=1))
.
.
.
Error:
Invalid name column 'IsOnline' (in Where statement)
How could I solve it?
thanks
|
|
|
|
|
Obviously IsOnline is not a column in the table. Find out what the column name should be I know the language. I've read a book. - _Madmatt
|
|
|
|
|
Yes I know and there is not the same column name in the table but I have to compare it with @UserStatus
|
|
|
|
|
Try this[^] I know the language. I've read a book. - _Madmatt
|
|
|
|
|
Is the UserStatus a string ? Then the IsOnline should be 'IsOnline' (in quotes).
Or is IsOnline a SQL Server function that you have written ? Maybe it needs a parameter.
|
|
|
|
|
@UserStatus is declared as bit and IsOnline is not a SQL function.
|
|
|
|
|
It's not legal to filter on a column that doesn't exist before the selection
select 1 as bla
where bla <> 1 This will generate an exception. You'd need to change the condition in your where-clause. Alternatively, you could use a view without the filter, and filter that using a second query.
I'm thinking that IsOnline would be a good candidate to become a computed column.I are Troll
|
|
|
|
|
this is the full SQL
@PageNumber int,
@PageSize int,
@MinutesSinceLastInActive int,
@CurrentTimeUtc datetime,
@LastName nvarchar(64),
@UserName nvarchar(256),
@CityID int,
@UserStatus bit
AS
BEGIN
DECLARE @FirstRow INT,
@LastRow INT,
@TotalRecords INT,
@DateActive datetime
SELECT @DateActive = DATEADD(minute, -(@MinutesSinceLastInActive), @CurrentTimeUtc)
SELECT @FirstRow = 1 + (@PageNumber * @PageSize),
@LastRow = @PageSize + @FirstRow - 1 ;
SELECT @TotalRecords = (SELECT COUNT(*) FROM PD_Users WHERE @LastName = PD_Users.LastName OR (@LastName = '' AND 1=1));
WITH Members AS
(
SELECT PD_Users.FirstName as FirstName, PD_Users.LastName as LastName, PD_Users.UserName as UserName, PD_Users.CityID as CityID,
PD_Cities.CityName as CityName, aspnet_Membership.CreateDate as CreateDate,
(SELECT COUNT(*) FROM PD_Articles WHERE (PD_Users.UserName = UserName)) AS ArticleCount,
(SELECT COUNT(*) FROM PD_News WHERE (PD_Users.UserName = SenderName AND IsDevNews = 1)) AS NewsCount,
(SELECT COUNT(*) FROM PD_News WHERE (PD_Users.UserName = SenderName AND IsDevNews = 0)) AS ITNewsCount,
(SELECT COUNT(*) FROM PD_ForumMessage WHERE (PD_Users.UserName = UserName)) AS MessageCount,
CASE WHEN aspnet_Users.LastActivityDate > @DateActive THEN cast(1 as bit) ELSE cast(0 as bit) end AS IsOnline,
ROW_NUMBER() OVER (ORDER BY aspnet_Membership.CreateDate ) AS RowNumber
FROM aspnet_Users INNER JOIN aspnet_Membership ON aspnet_Users.UserId = aspnet_Membership.UserId INNER JOIN
PD_Users INNER JOIN PD_Cities ON PD_Users.CityID = PD_Cities.CityId ON aspnet_Users.UserName = PD_Users.UserName
WHERE (@LastName = PD_Users.LastName OR (@LastName = '' AND 1=1))
AND (@UserName = PD_Users.UserName OR (@UserName = '' AND 1=1))
AND (@CityID = PD_Users.CityID OR (@CityID = -1 AND 1=1))
AND (@UserStatus = IsOnline OR (@UserStatus = 0 AND 1=1))
)
SELECT RowNumber, FirstName, LastName, UserName, CityID, CityName, CreateDate,
ArticleCount,NewsCount, ITNewsCount, MessageCount, IsOnline
FROM Members
WHERE RowNumber BETWEEN @FirstRow AND @LastRow AND (@UserStatus = IsOnline)
ORDER BY RowNumber ASC
RETURN @TotalRecords
END
because I use this stored procedure for paging so I need to use IsOnline in first Select statement.
|
|
|
|
|
A bit dirty, but turning it into a subquery should work too;
SELECT @FirstRow = 1 + (@PageNumber * @PageSize),
@LastRow = @PageSize + @FirstRow - 1 ;
SELECT @TotalRecords = (SELECT COUNT(*) FROM PD_Users WHERE @LastName = PD_Users.LastName OR (@LastName = '' AND 1=1));
WITH Members AS
(
select * from
(
SELECT PD_Users.FirstName as FirstName, PD_Users.LastName as LastName, PD_Users.UserName as UserName, PD_Users.CityID as CityID,
PD_Cities.CityName as CityName, aspnet_Membership.CreateDate as CreateDate,
(SELECT COUNT(*) FROM PD_Articles WHERE (PD_Users.UserName = UserName)) AS ArticleCount,
(SELECT COUNT(*) FROM PD_News WHERE (PD_Users.UserName = SenderName AND IsDevNews = 1)) AS NewsCount,
(SELECT COUNT(*) FROM PD_News WHERE (PD_Users.UserName = SenderName AND IsDevNews = 0)) AS ITNewsCount,
(SELECT COUNT(*) FROM PD_ForumMessage WHERE (PD_Users.UserName = UserName)) AS MessageCount,
CASE WHEN aspnet_Users.LastActivityDate > @DateActive THEN cast(1 as bit) ELSE cast(0 as bit) end AS IsOnline,
ROW_NUMBER() OVER (ORDER BY aspnet_Membership.CreateDate ) AS RowNumber
FROM aspnet_Users INNER JOIN aspnet_Membership ON aspnet_Users.UserId = aspnet_Membership.UserId INNER JOIN
PD_Users INNER JOIN PD_Cities ON PD_Users.CityID = PD_Cities.CityId ON aspnet_Users.UserName = PD_Users.UserName
) as TempCTE
WHERE (@LastName = PD_Users.LastName OR (@LastName = '' AND 1=1))
AND (@UserName = PD_Users.UserName OR (@UserName = '' AND 1=1))
AND (@CityID = PD_Users.CityID OR (@CityID = -1 AND 1=1))
AND (@UserStatus = IsOnline OR (@UserStatus = 0 AND 1=1))
)
SELECT RowNumber, FirstName, LastName, UserName, CityID, CityName, CreateDate,
ArticleCount,NewsCount, ITNewsCount, MessageCount, IsOnline
FROM Members
WHERE RowNumber BETWEEN @FirstRow AND @LastRow AND (@UserStatus = IsOnline)
ORDER BY RowNumber ASC I are Troll
|
|
|
|
|
As far as Sql Server is concern, this you cannot do but the same can be done in Oracle.
What you do is like
AND (@UserStatus = (CASE WHEN aspnet_Users.LastActivityDate > @DateActive THEN cast(1 as bit) ELSE cast(0 as bit) end) OR (@UserStatus = 0 AND 1=1))
Hope this helps
Niladri Biswas
|
|
|
|
|
Thank you Niladri
it helped
|
|
|
|
|
Hi all
i am getting this error in my web applicacion.can any one help me how to resolve this error.plz
distributed transaction completed.either enlist this session in new transection or the null transaction
thnx in advance...
|
|
|
|
|
Great explanation for a question in the database forum. txtspeak is the realm of 9 year old children, not developers. Christian Graus
|
|
|
|
|
thnx for help....
but i already solved this issue.....
|
|
|
|
|
Can you post how you resolved it? Naina
|
|
|
|
|
I have a table with only one column as
Emp_Name
A
B
C
D
E
I want to display this data as
Emp_Name
A, B, C, D, E
Please help me as soon as possible
|
|
|
|
|
I'm not sure I understand, could you elaborate please? It looks like you're wanting to merge every record in a table into one? That's sort of anti-database design, no?
|
|
|
|
|
Mugdha_Aditya wrote: Please help me as soon as possible
Start here[^] I are Troll
|
|
|
|
|