|
You have just repeated what you said before. You did not explain what you want.
I am guessing you want assistance with the SQL to delete information from the table and update information in the table. However, it is not clear what criteria you are using for the delete or update. You will have to explain more.
If it is this simple, you could always look in SQL Server's books online.
|
|
|
|
|
hi,
I m creating a Dynamic Query in Procedure on that time if some Syntax Error Occurs due to unformatted input how can I handle It in side procedure.If any one can guide me please help.
rup
|
|
|
|
|
I had to cast datetime-parameters to an nvarchar(50) to make the dynamic query work. Note: This had to be done within the query-string.
I also had to specify the length of an varchar-parameter to make it work.
Maybe this could help you, too.
|
|
|
|
|
|
Are they unique to a user/session? Like if 2 users simultaneously run the stored procedure?
TIA!
I am a SysAdmin, I battle my own daemons.
|
|
|
|
|
There are two types of temporary tables. If you specify single # sign to the table name, it's local temporary table which will be unique for each session. This will be dropped automatically when connection closes. If two # sign is provided, it's global and all connections made to SQL server can access that.
|
|
|
|
|
Hi all,
I've got a problem when manipulating on float type data, I'm working on SQL Server 2005.
Here is the code:
DECLARE
@First float,
@Second float,
@Third float
SET @First = 1.22
SET @Second = 2.72
SET @Third = 3.94
SELECT @First + @Second - @Third
The result is "4.44089209850063E-16" instead "0"
I can't understand this situation
Thanks a lot
|
|
|
|
|
Use Decimal or Numeric data types. if you are looking for fixed decimal places.
float and real are represented using scientific notation.
In your case you have to use Decimal or Numeric
Regards
KP
|
|
|
|
|
Yeah floating point arithmetic isn't ever exact. 4.4 * 10^-16 is so small its pretty much zero anyway - close enough! 
|
|
|
|
|
Good evening All
am creating a Procedure for a Login Screen, Obviously i want to search for a Username and password that has been passed as a Parameter. and return "0" if it has been found and If it was not Found it must return "1"
So i have Written this
<br />
<br />
ALTER Proc [prcLogin]<br />
(<br />
@Username VarChar(50), <br />
@UPassword varChar(50),<br />
@OutRes int OUTPUT<br />
)<br />
AS<br />
DECLARE @rc Int<br />
SELECT @OutRes = 1<br />
FROM [Login] <br />
WHERE Username = @Username And UPassword = @UPassword<br />
IF @@RowCount > 0 <br />
BEGIN<br />
SELECT @rc = 0<br />
RETURN @rc<br />
END<br />
ELSE<br />
BEGIN<br />
SELECT @rc = 1<br />
RETURN @rc<br />
END<br />
<br />
Well i dont Get an Error with this code here is how i use it
<br />
Public Function Check_Login(ByVal Username As String, ByVal Password As String) As Boolean<br />
<br />
con = New SqlConnection(strcon)<br />
<br />
cmdselect = New SqlCommand<br />
<br />
cmdselect.CommandTimeout = 0<br />
<br />
cmdselect.CommandType = CommandType.StoredProcedure<br />
<br />
cmdselect.CommandText = "prcLogin"<br />
<br />
cmdselect.Parameters.Add("@Username", SqlDbType.VarChar, 50, "UserName")<br />
<br />
cmdselect.Parameters("@Username").Value = Username<br />
<br />
cmdselect.Parameters.Add("@UPassword", SqlDbType.VarChar, 50, "UPassword")<br />
<br />
cmdselect.Parameters("@UPassword").Value = Password<br />
<br />
cmdselect.Parameters.Add("@OutRes", SqlDbType.Int)<br />
<br />
cmdselect.Parameters("@OutRes").Direction = ParameterDirection.Output<br />
<br />
cmdselect.Connection = con<br />
<br />
Dim res As Integer<br />
<br />
Dim ds As New DataSet<br />
Try<br />
con.Open()<br />
<br />
res = cmdselect.ExecuteScalar<br />
<br />
con.Close()<br />
<br />
Catch ex As Exception<br />
<br />
MsgBox(ex.Message)<br />
<br />
End Try<br />
<br />
Return res<br />
<br />
End Function<br />
<br />
I keep on getting a "0" and that means even a Bad login will login
What am i doing Wrong here
Thanks
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za
|
|
|
|
|
I am just wondering if your proc can be restructured the following way.
Create Proc [prcLogin]
@pUserName varchar(50),
@pPwd varchar(50)
as
Select count(*) from [Login] where UserName = @pUserName and Password=@pPwd
The above proc returns a value 1 for a valid login, and 0 for an invalid login (assuming that the PK constraints are set properly for the username column in your login table).
You may now remove the code that deals the output param (vb.net)
res = cmdselect.ExecuteScalar
res will now hold a value 1 for a valid login and 0 for a bad/invalid login.
HTH
Give a man a fish, he'll eat for a day. Teach a man how to fish, he'll eat for lifetime.
Pradeep Joe
|
|
|
|
|
What you are getting here is the return code of the stored procedure and not the output parameter. You need to set as the following:
con.Open()
cmdselect.ExecuteScalar
res = cmdselect.Parameters("@OutRes").value
con.Close()
Would also suggest putting you connection close in a finally block.
|
|
|
|
|
Hi Man
Thanks you are a Star , i dont kow how i missed that
Thanks here is a vote for you
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za
|
|
|
|
|
Does anyone have a link or document I can look at that helps me with creating a connection string to log in to a sql server?
This is what I have and it is failing.
"Data Source=.\SQLEXPRESS;AttachDbFilename=" + txtbxdatabase.Text + ";Uid= " + txtbxUserId.Text + ";Pwd= " + txtbxUserId.Text"
I think I am missing something but can't find info online about it. At least not clear enough. I can successfully login manually to sql server express using login and password so I know it is not that.
Thank you.
Cory
|
|
|
|
|
|
|
|
Hi All,
I have a table with the below columns.
ID , First Name , Last Name , City. The ID column is unique. This table contains duplicate rows (First Name , Last Name , City values are same). Could you please advise me on how to write a select query to display duplicate rows only once along with the other rows.
Thanks and Regards
Pha
|
|
|
|
|
Do you need the ID for display? If not, a simple DISTINCT might be easiest.
|
|
|
|
|
Thanks for the response. But i need the ID as well in the display.
Regards
Pha
|
|
|
|
|
-- ALWAYS TEST FIRST
-- I don't have your table name
-- REPLACE "TABLE" WITH THE ACTUAL TABLE NAME
-- Try this to select the dups
select count(*),FirstName , LastName , City
from "TABLE"
group by FirstName , LastName , City
having count(*) > 1
-- select the data into another table and TEST THIS
-- REPLACE "TABLE" WITH THE ACTUAL TABLE NAME
--*********************** DELETE DUPS ***************************************
-- the Magic is in the rowcount setting!!! make sure it is set to 1 at the start and 0 on completion
set rowcount 1
while exists (select FirstName,LastName,City
from "TABLE"
group by FirstName , LastName , City
having count(*) > 1)
begin
delete T
from "TABLE" T
inner join (select FirstName,LastName,City
from "TABLE"
group by FirstName,LastName,City
having count(*) > 1) dup on T.FirstName = dup.FirstName and T.LastName = dup.LastName and T.City = dup.City
end
go
set rowcount 0
go
Good Luck DBranscome Phoenix AZ
David Branscome
modified on Tuesday, February 12, 2008 7:19 PM
|
|
|
|
|
I am having a little problem in handshakes routed through MSMDPump getting unduly delayed and thrown back with a Timeout exception.
I have the configuration in line with http://www.microsoft.com/technet/prodtechnol/sql/2005/httpasws.mspx[^] setup. It is working fine with a significant number of installations except a typical one which involves the server and DB in isolated networks. The initial request goes through however, the subsequent requests choke the server with timeouts. There is no heavy CPU activity anyway.
I have tried adjusting MaxThreadsPerClient in the above URL but not much significant gains.
Any clues?
Vasudevan Deepak Kumar
Personal Homepage
Tech Gossips
A pessimist sees only the dark side of the clouds, and mopes; a philosopher sees both sides, and shrugs; an optimist doesn't see the clouds at all - he's walking on them. --Leonard Louis Levinson
|
|
|
|
|
Hi to all
create table emp(eid int identity(1,1) primary key,ename varchar(30),sal money)
create table emprel(RelId int,RelMobile char(10),RelAddress varchar(50))
i created two tables like above, i want to create a view and i want restrict to access the RelAddress for some users how can i do it
RelId is foreign key for emp table
eid ename sal RelId RelMobile RelAddress
1 aaa 10000 1 84874 doorno:5435,hyd
2 bbb 10000 1 84874 doorno:4435,pune
3 ccc 10000 1 84874 doorno:5435,bangalore
i want to specific users restrict to access the RelAddress column , can we do this by using view,
what is the exact use of view, how can i user, i very familiar using of stored procedure, but no idea on view please any body can help, i want simple view example to Analise initially about views.
This is haneef.............................................................
|
|
|
|
|
Hi all
i want to develop a win desktop application in vb.net with mssql as backend for a client which has 6 to 10 users in his office.
as i am new to mssql, please tell me which edition of mssql is suitable for this type application.
is there need to install msssql on all pcs or on a single (main server)
thanks a lot in advance
rmshah
Developer
|
|
|
|
|
If cost is not a factor then definitely SQL Server 2005 Enterprise edition.
Otherwise go for SQL Server 2005 Standard edition.
If you are on a shoe-string budget then MySQL is the answer Clickety
r_mohd wrote: is there need to install msssql on all pcs or on a single (main server)
is answered by this
r_mohd wrote: i want to develop a win desktop application in vb.net with mssql as backend
The clients will need the engines/etc to be able to communicate with the server, which you will be providing with your client end applications
If you want one of the clients to be able to administer the server then you will need to install the client tools on that client.
Regards
Guy
You always pass failure on the way to success.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
