|
A big problem is a Datetime object in the CommandText property. I always get an error. Is .NET unable to convert Datetime object into the right SQL Server system format? When it's possible ... how to solve it?
|
|
|
|
|
Seraphin wrote:
A big problem is a Datetime object in the CommandText property.
Why are you putting a DateTime in the CommandText property?
Seraphin wrote:
Is .NET unable to convert Datetime object into the right SQL Server system format?
.NET is perfectly capbable of converting a DateTime object to what ever format you wish in multiple calendars (should you so wish it).
Seraphin wrote:
how to solve it?
If you need to use a DataTime (or anything else for that matter) in a query you should use parameters. This reduces the possibility of security holes.
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "SELECT * FROM MyTable WHERE SomeDate > @theDate";
cmd.Parameters.Add("@theDate", myDateTimeObject);
Does this help?
My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
Thank you. That's what I want.
|
|
|
|
|
I want my DateTime value type fields just store date values (month/day/year) or time values (hour:minute). How can i do that?
I have datetime fields in tables with full information (month/day/year hour:minute:second:milisecond). But i just want to take month/day/year or hour:minute in my select query string. How can i do that?
Thanks!
|
|
|
|
|
|
Is there an easier way than
SELECT ltrim(str(DATEPART(month, GETDATE()))) + '/' + ltrim(str(DATEPART(day, GETDATE()))) + '/' + ltrim(str(DATEPART(year, GETDATE()))) ?
<italic>Work hard, Work effectively.
|
|
|
|
|
|
I haven't got the solution Colin. I have tried
select convert(datetime,getDate(),101) .  I only want the day, month, and year. Thank you for the reply.
<italic>Work hard, Work effectively.
|
|
|
|
|
Yeah, i often use ltrim to take the day, month, year string too. But it's too long. I wonder why there isn't just a date data type? And i can't find out in which situation we need to store millisecond. 
The way using the convert function to get the day, month, year that Colin helps works Yulianto! You must convert it into varchar type, not datetime type :
select convert(varchar(100),getdate(),101)
Thanks Colin! 
But sometimes i what i want to take is hour and minute  I think the convert function doesn't work on this case. Maybe i should use ltrim again. 
|
|
|
|
|
Blue_Skye wrote:
And i can't find out in which situation we need to store millisecond.
There are a number of reasons for storing milliseconds. For instance, Security audit information often uses milliseconds.
Blue_Skye wrote:
But sometimes i what i want to take is hour and minute I think the convert function doesn't work on this case.
You're right, the function is quite limited and you will need to roll your own conversion if you want anything other than what the CONVERT function supplies.
My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
|
Can I use a Parameter ( either in MSAccess / SQL Server ) to set the name of a table which I want to create or get data from .
To make it simple, can I execute an SQL this way,
(Assuming the DB to be MSAccess)
Parameters TableName Text;
SELECT * FROM TableName;
Or is there a workaround for this?
|
|
|
|
|
You for something like that you will need to create dynamic SQL in either .NET or in a Stored Procedure. In other words:
string tableName = "Account";
string sqlQuery = string.Format("SELECT * FROM {0}", tableName);
Here's how you do this in a stored procedure
CREATE PROCEDURE dbo.ExecQuery
(
@tableName varchar(50)
)
AS
declare @sql varchar(100)
set @sql = 'SELECT * FROM ' + @tableName
exec @sql
return 0
The main question is, is this there a different approach all together?
~Javier Lozano
|
|
|
|
|
Be careful with both those approaches as they are weak points from which a SQL Injection attack could be launched. If you are selecting an existing table, then it is worthwhile checking that the table exists already with a proper parameterised query so you can be sure there isn't anything nasty injected into the tableName variable you are using.
My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
You are very much correct, and why I specifically asked for a parameterised query is for the same reason, to avoid Injection Attack. So can you explain how I can use a parameter to substitite for table name? I couldn't get it to work !!
|
|
|
|
|
csylesh wrote:
So can you explain how I can use a parameter to substitite for table name? I couldn't get it to work !!
You would have to use something like Javier suggested. You cannot have a table name as a parameter. However, table names are unlikely to have strange characters in them* so you can easily check it for non-alphanumeric characters and reject the name if it contains such things.
Also, if you are just checking for a tables existance, you can create a query such as
SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = @MyTableNameParameter
Does this help?
* Having said that, a table name, if I remember correctly, can be any unicode string upto 128 characters so it is still possible.
My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
Thanks, I just wanted to hear from somebody authoritatively that a parameter substitution is not possible for a table name.
Let me raise a new question for curiosity, how does parameter substitution actually prevents injection attack evenif it is a string datatype that is being substituted. Is it some magic with the quotes substitution or the like? Any ideas?? Thanks in advance.
|
|
|
|
|
Hello friends!!
In Execution plan please somebody help me to understand I am new to it.
If I run one select statement having Execution plan...
1.first of all what we will watch closely so that it will understand easily i.e related to costing....
2.Here I am seeing factors like 'SELECT' ,' TOP' , Hash Match/Left Anti Semi Join.
3.Index Scan : Physical Operation and Logical Operation
Here only I can see some factores like
Physical Operation And Logical Operation
a)Row Count =========== 26,795,748
b)Estimated Row Size ==== 132 (What this exactly means??)
c)I/O Cost ============ 209
d)CPU Cost ============ 29.5
e)Number Of Executes ==== 1
f)Cost ============== 238.884090(57%)
h)Subtree Cost ======== 417
i)Estimated Row Count==== 26,795,748
4.Computer Scalar:
5.Stram Aggregate/Aggregate
6.Table Scan
Like That........
Please somebody guide me in that I will be thankful.. (Please Give me detail description where i can judge all factors of execution plan so that i can reduce the cost of query if possible)
T.I.A
Shashank
|
|
|
|
|
|
1. The most important thing, generally, to look at is whether the operations being performed are what you think should be performed. Are any indexes being maintained that you think are unnecessary? Is an index being ignored? If there are multiple steps in a query (perhaps this was a batch of statements, or a stored procedure) look first at the one that consumed the most time - this is reported as a percentage. Concentrate your efforts on improving the subtrees that consumed the most time.
2. These are the operations being performed. The SELECT operator returns the rows returned by the operations to its right to the client - as such it doesn't add very much overhead at all. TOP guides the operations to the right to stop once the given number of rows are returned.
A Hash Match[^] is a way of performing a join, useful if there are a lot of join conditions. The server builds a hash table for both the left and right sides and compares the hash values to determine if there's a match. The Left Anti Semi Join[^] means that rows that are in the first but not in the second are returned - your query probably uses NOT IN, or similar.
Hash matches tend to be used if no suitable index was used. If a suitable index is available, and the number of rows to be returned is small, SQL Server will normally use nested loops with an index seek to find rows in the second table.
3. An Index Scan is where SQL Server starts at the beginning of the index and reads every row, to the end, looking for rows that match. Compare this to an Index Seek, where SQL Server looks directly for a particular value by looking in the right place. It's the difference between looking for an entry in a phone book by starting at the beginning and looking at every page, and looking instead for the first letter, then the next letter, etc.
a) This is the number of rows that were actually returned by this operator.
b) This is how big each row was. If the source was an actual database table, this is the size of the row from the table. Multiplying this by the number of rows gives an indication of how much data needed to be transferred from disk or read from buffers. If the query plan includes a Spool operator, it indicates how much needed to be written out to the disk and re-read.
c) I/O cost - seconds spent waiting for data to be retrieved from disk
d) CPU Cost - seconds of CPU time spent
e) Number of executes - the number of times this operator was executed. If a child of a 'nested loops' operation, the larger data set will show as 1, while the smaller will be the number of rows returned from the larger set, typically (because for each entry in the data set, the execution engine performs the second operation to find rows that match).
f) Cost - total cost of this operation, which is I/O + CPU cost
h) Subtree cost - seconds of time spent executing this operation and the operations to the right of it
i) Estimated row count - number of rows that SQL Server estimated that this operation would produce, based on the statistics it keeps for indexes and optionally for other columns too.
4. Compute Scalar[^] simply calculates values from other values in the row. This seems to also appear if you have text, ntext or image type columns - an additional lookup has to occur to find the data if it's not stored in the row.
5. Stream Aggregate/Aggregate computes aggregate expressions such as SUM, AVG, COUNT, etc. The difference between Stream Aggregate and Aggregate is that Stream Aggregate requires that the data is already sorted into the correct groups.
6. Table Scan[^] reads the table from beginning to end to match rows.
Why would SQL Server choose an Index Scan rather than a Table Scan? It has to do with how the index is implemented. Basically, all the columns listed in the index definition are duplicated in the index data, plus the lookup key to correlate this entry in the index back into the main table. If the table has a clustered index, this lookup key is the data from the columns that make up the clustered index. If all the columns needed for the query are included in the index, SQL Server may decide to read the index rather than the table itself. Doing so will reduce the amount of data that needs to be read from disk, since the index row size is smaller than the table row size.
The first thing I'd do would be to try to turn your table scans and index scans into index seeks. To do that we have to make your existing indexes work better and perhaps add new indexes. First, ensure that you're not trying to optimise an operation that will occur very rarely - introducing new indexes to speed up this operation will normally slow down insert, update and delete operations as the indexes need to be maintained. Of course an index may also speed up write operations as the appropriate place for the record may be found faster.
Without seeing the data it's hard to say what could help. The Index Tuning Wizard will be able to offer some suggestions. Ensure you give it a decent amount of realistic trace data.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
I have a store procedure that will insert two rows into two tables. I want my procedure either both two statements process sucessful or none of that (if the second insert statement fails, the first one will roll back). And how can we catch an error when it appears from the insert statement to raise our custom error (using the statement 'raise error')?
Thanks! 
|
|
|
|
|
Immediately after the INSERT statement you can do something like this:
SELECT @errorCode = @@error;
You must do this on the next statment as @@error returns the error code of the previous statement. The SELECT here is to capture the code so it can be used in multiple statements afterwards. Remember to DECLARE your variable before the INSERT statement.
My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
Thanks a lot!
|
|
|
|
|
Hai,
Can any body explain me how I can debug a dll file.
Thank you,
Deepa.N
|
|
|
|
|
If you don't want to go down to assembler level, you'll probably have to find the source code for the dll, set it to debug and recompile it. If you have the same version dll and project, you should be able to step through the code in debug mode without a problem. If you have no source code, you'll be in for a tough time.
Simplicity is the key to the universe
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
