|
Hi guys, trying to access Reporting Service:
http://localhost/reports
Ran into this: "The underlying connection was closed: The remote name could not be resolved."
Any idea? Thanks.
|
|
|
|
|
i have a problem after i insert some records using insert into when i use select * , the records appear sorted with different order than i enterd it.
|
|
|
|
|
Of course not. SQL Server, or any other datbase for that matter, doesn't sort records in the physical table. It's too much of a performance hit for very little gain. Records are only sorted upon request, such as in your SELECT statement.
SELECT * FROM table ORDER BY fieldName
RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
|
|
|
|
|
but the scenario done as
in insert phase i entered as follow:
256
512
1024
2048
in select phase when i select * i have that o/p:
1024
2048
256
512
do u have any explanation
|
|
|
|
|
It appears to be defaulting to an alpha sort on the string value.
How is the data stored (integers or strings)
What is the primary Key for the table?
Does it have a clustered index? (normally this would be the default sort order, since it dictates the physical organization of the data).
Is that column indexed (if so, how)
All of the above can impact the default sort order for "select *"
Absolute faith corrupts as absolutely as absolute power
Eric Hoffer
All that is necessary for the triumph of evil is that good men do nothing.
Edmund Burke
|
|
|
|
|
In addition to Rob's explanation I should also point out that the best way to think of the contents of a table are as a set. Sets are naturally unordered - they are just a collection of things. Even if I want it ordered by ascending primary key (the primary key is ususally the clustered index which is the thing that determines the physical order within the database) I always put an ORDER BY myPrimaryKey ASC because I take no guarantee that the functionality will remain or that someone else will come along after and optimise the database by moving the clustered index onto something else.
In short, if you want an ordered result set then specify the order. Otherwise treat the result as unordered (even if it appears otherwise)
ColinMackay.net
Scottish Developers are looking for speakers for user group sessions over the next few months. Do you want to know more?
|
|
|
|
|
thank u all for ur explanation, it helped my very much
|
|
|
|
|
Beolw is the tables Structure and dummy data used in Access database
Employee
========
EmpId(Pk int) EName(Text) DeptId(Fk int)
1 Rashid 1
2 Kashif 2
3 Black 1
4 White 2
5 Brown 2
Department
==========
DeptId(Pk int) DName(Text)
1 Labor
2 Secret
3 Production
4 Purchase
Attendance
==========
AttId(Pk Int) EmpId(Fk int) In/Out_Time(Datetime) Direction
1 2 2/5/2006 6:37:02 PM 1 // 1 for IN, 0 For OUT
2 2 2/5/2006 8:37:02 PM 0
3 2 2/5/2006 8:50:00 PM 1
4 1 2/5/2006 8:51:00 PM 1
5 1 2/5/2006 9:37:02 PM 0
6 2 2/5/2006 9:40:02 PM 0
7 3 2/5/2006 6:37:04 PM 1
8 4 2/5/2006 6:37:06 PM 1
9 5 2/5/2006 6:37:08 PM 1
10 5 2/5/2006 8:40:02 PM 0
11 4 2/5/2006 8:40:40 PM 0
12 3 2/5/2006 8:40:50 PM 0
13 2 3/5/2006 6:37:02 PM 1
14 2 3/5/2006 8:37:02 PM 0
15 2 3/5/2006 8:50:00 PM 1
16 1 3/5/2006 8:51:00 PM 1
17 1 3/5/2006 9:37:02 PM 0
18 2 3/5/2006 9:40:02 PM 0
19 3 3/5/2006 6:37:04 PM 1
20 4 3/5/2006 6:37:06 PM 1
21 5 3/5/2006 6:37:08 PM 1
22 5 3/5/2006 8:40:02 PM 0
23 4 3/5/2006 8:40:40 PM 0
24 3 3/5/2006 8:40:50 PM 0
And so on the attendance data for whole month.
What i need is an optimized efficient query that takes a Start_date(DateTime) as an input parameter and returns me the
Attendance data of all Employees for one week. Means starting from startdate and ending at startdate+6 days in the following
format
EName DName IstDay 2ndDay 3rdDay 4thDay 5thDay 6thDay 7thDay
Where as IstDay to 7thDay Columns contains the value for total Working hrs at that day as
IstDay = [{Value of In/Out_Time Column for Max(AttId) When Direction=0}-
{Value of In/Out_Time Column for Min(AttId) When Direction=1}
Where DatePart(d,In/Out_Time)=DatePart(d,@Start_Time) for any EMpId]
2ndDay = [{Value of In/Out_Time Column for Max(AttId) When Direction=0}-
{Value of In/Out_Time Column for Min(AttId) When Direction=1}
Where DatePart(d,In/Out_Time)=DatePart(d,@Start_Time+1 Day) for any EMpId]
3rdDay = [{Value of In/Out_Time Column for Max(AttId) When Direction=0}-
{Value of In/Out_Time Column for Min(AttId) When Direction=1}
Where DatePart(d,In/Out_Time)=DatePart(d,@Start_Time+2 Days) for any EMpId]
4thDay = [{Value of In/Out_Time Column for Max(AttId) When Direction=0}-
{Value of In/Out_Time Column for Min(AttId) When Direction=1}
Where DatePart(d,In/Out_Time)=DatePart(d,@Start_Time+3 days) for any EMpId]
And so on upto 7th day.
Now i m in need of an efficient query that returns me the required above result set.
NOTE
====
I am using Access 2000 database so i need a query or anything else that may give me the required output in Access database
Thnx in Advance
|
|
|
|
|
I have two tables that I have joined
First table is ExpenseReport and has a field in it called tboxBeginDate
Second table is called BeginDates and has a field in it also called tboxBeginDate.
I want to query and show only those dates in BeginDates that are not used in ExpensReport
Select ExpenseReport.tboxBeginDate, BeginDates.tboxBeginDate from BeginDates.tboxBeginDate RIGHT JOIN ON ExpenseReport.tboxBeginDate where BeginDats.tboxBeginDate != ExpenseReport.tboxBeginDate.
any help would be great thanks
Win32newb
"Programming is like Sex, make a mistake and you end up providing support for a long time"
|
|
|
|
|
Either of these should work. I prefer the first one for maintainability. If your tables are large the second one may be quicker with the correct indexes:
SELECT
tboxBeginDate
FROM
BeginDates
WHERE
tboxBeginDate NOT IN (SELECT DISTINCT
tboxBeginDate
FROM
ExpenseReport)
OR
SELECT
bd.tboxBeginDate
FROM
BeginDate bd
LEFT JOIN
ExpenseReport er
ON (bd.tboxBeginDate = er.tboxBeginDate)
WHERE
er.tboxBeginDate IS NULL
|
|
|
|
|
thanks a bunch you wouldn't happen to have a good place "tutorial" that will help you with sql other than w3schools would you?
thanks again
Win32newb
"Programming is like Sex, make a mistake and you end up providing support for a long time"
|
|
|
|
|
I don't know any really good sites. You may want to look at http://www.sqlservercentral.com[^]. Sign up for their newsletter and you will get a little dose of knowledge every day.
The book I learned the most from was written by Ken Henderson: The Guru's Guide to Transact-SQL. It was tough to grasp at first but, it really gives you confidence with the SQL language.
|
|
|
|
|
When I try both of your methods it ask me to enter a value for bd.tboxBeginDate I checked the database and this table has values so I'm not sure whats wrong must be something with the join statement 
Win32newb
"Programming is like Sex, make a mistake and you end up providing support for a long time"
|
|
|
|
|
I am guessing you are not using SQL server. Are you using Access?
|
|
|
|
|
Yeah unfortunately I'm using access for now. We will be converting to sql server soon but for now Access. Sorry I should have been more specific.
Win32newb
"Programming is like Sex, make a mistake and you end up providing support for a long time"
|
|
|
|
|
Access syntax is very different from SQL. The join syntax is really nasty when mulitple joins are involved. I used to write code that converted between the two but, that was many years ago. I think you can modify your original WHERE clause like this:
SELECT
ExpenseReport.tboxBeginDate,
BeginDates.tboxBeginDate
FROM
BeginDates.tboxBeginDate
LEFT JOIN ON
ExpenseReport.tboxBeginDate
WHERE
BeginDates.tboxBeginDate == ExpenseReport.tboxBeginDate AND
ExpenseReport.tboxBeginDate IS NULL
The 'IS NULL' is what I am not sure about. Create a new query and paste this into the SQL view of the query and see if it resolves correctly by going back to the design view. I can't remember how null is resolved in Access. It might be ISNULL([Field Name]) or something like that.
Do remember the following, when NULL is directly compared with a defined value:
BeginDats.tboxBeginDate != ExpenseReport.tboxBeginDate
Your result will be NULL not TRUE as you expected. That is why you would never get the correct result.
-- modified at 14:48 Friday 24th March, 2006
|
|
|
|
|
Thanks for all your help. I was wondering about that != but yeah makes scense kinda like multiplying something by 0 it is always goning to be zero.
again thanks
Win32newb
"Programming is like Sex, make a mistake and you end up providing support for a long time"
|
|
|
|
|
Hello,
I am creating my first procedures in SQL using SQL 2005.
I have 3 tables, with the following columns:
Surveys - [SurveyId](PK) and [SurveyName] Questions - [SurveyId](FK), [SurveyQuestionId](PK) and [SurveyQuestion] Answers - [SurveyQuestionId](FK), [SurveyAnswerId](PK) and [SurveyAnswer]
Each survay can include various questions and each question can include several answers.
This is way I am using the Foreign Keys in both Questions and Answers tables. To relate the tables.
I created a procedure which deletes a Survey given its SurveyId. This is part is done.
I also need to delete all the questions dependent on that survey and all the answers dependent on those questions.
How can I delete survey, its questions and their answers when receiving the SurveyId?
Thank You Very Much,
Miguel
Here is the code of the procedure that I created which in this moment only deletes the survey from the Surveys table:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[Surveys_DeleteSurvey]
-- Procedure Parameters
@SurveyId As uniqueidentifier
AS
BEGIN
-- Check if SurveyId is null
IF( @SurveyId IS NULL )
RETURN -1
ELSE
BEGIN
-- Return '-1' if a survey with SurveyId given value is not found
IF( NOT EXISTS( SELECT @SurveyId FROM dbo.Surveys WHERE @SurveyId = SurveyId ) )
RETURN -1
END
-- Delete the survey with SurveyId given value
DELETE FROM dbo.Surveys WHERE @SurveyId = SurveyId
-- Return '0' when successful
RETURN 0
END
|
|
|
|
|
You could just implement Cascade on Delete when you implement referencial integrity. But, I always do it the hard way also:
ALTER PROCEDURE [dbo].[Surveys_DeleteSurvey]
(
@SurveyId As uniqueidentifier
)
AS
BEGIN
-- Check if SurveyId is null
IF( @SurveyId IS NULL )
RETURN -1
ELSE
BEGIN
-- Return '-1' if a survey with SurveyId given value is not found
IF( NOT EXISTS( SELECT @SurveyId FROM dbo.Surveys WHERE @SurveyId = SurveyId ) )
RETURN -1
END
-- Delete Answers
DELETE FROM
dbo.Answers
WHERE
SurveyQuestionId IN (SELECT
SurveyQuestionId
FROM
Questions
WHERE
SurveyId = @SurveyId)
-- Delete Questions
DELETE FROM
QUestions
WHERE
SurveyId = @SurveyId
-- Delete the survey with SurveyId given value
DELETE FROM dbo.Surveys WHERE @SurveyId = SurveyId
-- Return '0' when successful
RETURN 0
END
|
|
|
|
|
Hi All,
A couple of basic questions - can SQL server run over a VPN? If so, can it use Windows Password Verification? Can I use SQL Server Express? (has it network capabilities?).
I am moving from an internal networked solution, which has an Access Back end, to a scenario where workers are based around different parts of the country, so I'm thinking SQL Server is the best way forward.
I do use SQL Server over an internal network on another project, but any advice work be greatly appreciated.
Thanks
Jonny
|
|
|
|
|
StyleGuide wrote: can SQL server run over a VPN?
For apps, VPN is basically a normal network, so they can hardly tell the difference. So, SQL can run in a network that is accessed by VPN (or even be run in a VPN client and be used by other computers in the network.)
StyleGuide wrote: Can I use SQL Server Express? (has it network capabilities?).
Yes, but they are not enabled by default. You have to go to the SQL Configuration Manager and enable the SQL Browser Service, and also enable the TCP/IP protocol.
StyleGuide wrote: If so, can it use Windows Password Verification?
I'm not 100% sure on this one, but you should if you are part of a domain.
Luis Alonso Ramos
Intelectix
Chihuahua, Mexico Not much here: My CP Blog!
|
|
|
|
|
thanks Luis, thats really helpful.
Jonny
|
|
|
|
|
You are welcome! I am glad I could be of help 
Luis Alonso Ramos
Intelectix
Chihuahua, Mexico Not much here: My CP Blog!
|
|
|
|
|
hey guys, i was working on a simple login form that i get the UserID from Access database and match ir with selected password also got from that database but I get this erorr message. I just can't figure out what the problem is.
My code :
VerificationConn.Open();<br />
OleDbCommand Cmd = new OleDbCommand("Select PassCode from Password where UserID = '" + ID.Text + "'", VerificationConn);<br />
string lookupPassword = (string)Cmd.ExecuteScalar();<br />
VerificationDA.Fill(VerificationDS, "Password");<br />
VerificationConn.Close();<br />
if(lookupPassword == Pass.Text)<br />
{<br />
}
Error:
System.Data.OleDb.OleDbException was unhandled
Message="Syntax error in FROM clause."
Source="Microsoft JET Database Engine"
ErrorCode=-2147217900
StackTrace:
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteScalar()
at Phone.Verification.button1_Click(Object sender, EventArgs e) in C:\Documents and Settings\Mr.K\My Documents\Visual Studio 2005\Projects\Phone\Phone\Verification.cs:line 38
I thank you in advance
|
|
|
|
|
hi,
try like this.
strSQL="SELECT passcode FROM PassWordTable WHERE Useranme=@Username AND Password=@Password"
'Add the parameters for the query
Dom ComObj as New SqlCommand(strSQL,myConnection)
ComObj.Parameters.Add("@Username",txtUserName.text.Trim())
Like that for password also.
Dim dr As SqlDataReader
dr=ComObj.ExecuteReader(CommandBehavior.CloseConnection)
Try If dr.Read() then
Response.Write("We are logged in")
FormsAuthentication.RedirectFromLoginPage(txtUserName.Text,False)
else
Response.Write("Login failed")
End Try
I hope this helps.
Regards
Bhar
Bharathi
Books for Programmers
http://www.vkinfotek.com
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
