When I try to run profile 2005 ... as soon as i hit the connect button (authentication dialog) I get this error:
The procedure entry point lstrcpynI could not be located in the dynamic link library MSDART.DLL
any ideas?
Everything is up to date on my system. my configuration:
SQL Server 2000 enterprise (can't wait till next week to get EE for 2005)
WS 2003 w/SP1
.NET 1.1/2.0 (2.0 was installed with SQL 2005)
VS.NET 2003 (going to install 2005 later)
Office 2003 with SP2anyone?
I have created a Stored Procedure, under Stored procedures section under Enterprise Manager on SQL server 2000.
Could anybody tell me, how and what are the steps to follow to TRACE the procedure , using SQL PROFILER ?
Please advice me !
NicolFrom Enterprise Manager:
-- choose Tools
-- SQL Profiler
-- File
-- New
-- Trace
-- select/enter connection informatin as appropriate (I was able to accept the defaults)
-- press OK
-- click the General tab of the Trace Properties window
-- from the "Template name:" dropdown choose SQLProfilerTSQL_SPs
-- choose RUN
-- do whatever it is you need to do to run your Stored Procedure (from within Query Analyzer or ASP.NET)
-- go back to Profiler and click the red square "stop selected trace" icon
-- review the results
Terri|||Thanks Tmorton,
I got started with checking SP using SQL profiler.. :)
Now I found, I think, I have problem with coding in VB.NET or problem in SP.
Even though, I have given valid Username/Password, RETURNVALUE is always showing zero .
Stored procedure
-------
CREATE PROCEDURE test
(@.username varchar(100),
@.userpwd varchar(200)
)
AS
set nocount on
return (select count(*) as s from employer
WHERE user_name like '%@.username%' and
password ='@.userpwd')
GO
VB.NET Code using SP
--------
CmdCoInfo = New SqlCommand("test", ConVM)
CmdCoInfo.CommandType = CommandType.StoredProcedure
CmdCoInfo.Parameters.Add("@.username", Trim(StrUser))
CmdCoInfo.Parameters.Add("@.userpwd", Trim(StrPass))
Dim paramOut As SqlParameter
paramOut = CmdCoInfo.Parameters.Add("Returnvalue", SqlDbType.Int)
paramOut.Direction = ParameterDirection.ReturnValue
paramOut.Size = 40
' Retrieve the record that matches the username/password
ConVM.Open()
CmdCoInfo.ExecuteNonQuery()
Dim retrr As String
If Not IsDBNull(CmdCoInfo.Parameters("Returnvalue").Value) Then
retrr = (CmdCoInfo.Parameters("Returnvalue").Value)
Else
retrr = "Not know"
End If
Response.Write(retrr)
--------------
Is there problem with SP or VB.net code ?
Please advice !|||The problem is in your stored procedure.
Instead of this:
return (select count(*) as s from employer
WHERE user_name like '%@.username%' and
password ='@.userpwd')
GO
This would be more correct:
DECLARE @.RecordCount integer
select @.RecordCount= count(*) from employer
WHERE user_name like '%'+@.username +'%' and
password =@.userpwd
RETURN @.RecordCountGO
HOWEVER, ReturnValue is meant to signal back to the calling program whether or not the stored procedure was successful. So you are misusing it to return a COUNT. You should use an Output parameter instead. Add an additional parameter called RecordCount with ParameterDirection of Output. Add the Output parameter to your stored procedure, which will look like this:
CREATE PROCEDURE test
(@.username varchar(100),
@.userpwd varchar(200) ,
@.RecordCount int OUTPUT
)
AS
set nocount on
select @.RecordCount= count(*) from employer
WHERE user_name like '%'+@.username +'%' and
password =@.userpwd
RETURN @.@.ERRORGO
Terri|||Terri,
ur 2nd option worked for me. But i didnt understand why I am still getting zero as ReturnValue , when I tried with 1st option(given below), eventhough record exists for that username/pwd.
DECLARE @.RecordCount integer
select @.RecordCount= count(*) from employer
WHERE user_name like '%'+@.username +'%' and
password =@.userpwd
RETURN @.RecordCount
GO
Please advice !|||I used that same stored procedure, and your code modified slightly which executes the procedure and puts the returnvalue into a label and it works as expected:
CmdCoInfo = New SqlCommand("returntest", SqlConnection)
CmdCoInfo.CommandType = CommandType.StoredProcedure
CmdCoInfo.Parameters.Add("@.test1", 1)
Dim paramOut As SqlParameter
paramOut = CmdCoInfo.Parameters.Add("Returnvalue", SqlDbType.Int)
paramOut.Direction = ParameterDirection.ReturnValue
paramOut.Size = 40SqlConnection.Open()
CmdCoInfo.ExecuteNonQuery()Label1.Text = CmdCoInfo.Parameters("ReturnValue").Value
Terrisql
I have a search box on my website which is used to search the products database.
I will be using the search text in an SQL stored procedure that uses LIKE statements. The search string could realistically contain any character.
How do I prevent SQL injection when any search string is reasonably feasible? .
I recommend that you read the weblog entry by Microsoft's Bertrand Le Roy,Please, please, please, learn about injection attacks!|||
supergriff:
How do I prevent SQL injection when any search string is reasonably feasible?
Great, so if i'm using stored procedures, rather than string concatenation, this inherently prevents SQL injection? My search function is below:
' Select all ProductsPublicFunction GetAllVisibleProductsBySearchString(ByVal SearchStringAsString)As DataTableDim connAs SqlConnection =New SqlConnection(_connectionString)Dim cmdStoredProcedureAsNew SqlCommand("dis_GetAllVisibleProductsBySearchString", conn)cmdStoredProcedure.CommandType = CommandType.StoredProcedure
cmdStoredProcedure.Parameters.Add(
"@.SearchString", SqlDbType.VarChar, 250).Value = SearchStringDim daAs SqlDataAdapter =New SqlDataAdapter(cmdStoredProcedure)Dim dsAs DataSet =New DataSet()Try"Products")Catch eAs SqlException' Handle exception.Finallyconn.Open()
da.Fill(ds,
conn.Close()
EndTryReturn ds.Tables("Products")EndFunctionI'm working on a procedure that needs to cycle through the records of some raw data and combine the the current record with the datetime field of the prior record. I have been able to write a script to do that with cursors and variables but my problem is it returns each record separately. How do I go about getting the procedure to return all the records as one set of data?
To see what I mean, the following script for the Pubs DB returns each pass through the data as a seperate query. Since I can't do a select *, what approach should I take? If you want the actual script, I would be happy to provide it.
DECLARE authors_cursor CURSOR
FOR SELECT * FROM authors
OPEN authors_cursor
FETCH NEXT FROM authors_cursor
WHILE @.@.FETCH_STATUS = 0
begin
FETCH NEXT FROM authors_cursor
end
Close Authors_cursor
deallocate authors_cursor
Thanks in advance
Tony Murunion
If you only need the previous value, the best thing would be to store the previous data in a local variable. I think that is a common approach.HTH, jens Suessmeyer.
http://www.sqlserver2005.de
|||
Thanks for the reply
The actual script I was working with used local variables to get the data I wanted. I was just stuck with getting the results back in one set instead of one for each record.
I was able to resolve my problem by dumping the data into a table in a function (decided to try that instead of a proc)
Tony
