|
this is what I can tell you about such issues:
- if you are using an appropriate field type (date, time, datetime, etc), then the most likely bad value you could have is a NULL value.
- if you are using strings (varchar, whatever) to store date/time information, you are bound to get into trouble with regional settings, languages, etc. Just don't use strings!
If that doesn't help, either Google the error message, you'll get a lot of hits. Or show us more detailed information on your table, your code, and the actual data that fails.
Luc Pattyn [My Articles] Nil Volentibus Arduum
The quality and detail of your question reflects on the effectiveness of the help you are likely to get. Please use <PRE> tags for code snippets, they improve readability. CP Vanity has been updated to V2.3
|
|
|
|
|
dd mm yyyy vs mm dd yyyy, anyone?
Cheers,
Peter
Software rusts. Simon Stephenson, ca 1994.
|
|
|
|
|
that is what I was hinting at with "regional settings".
it isn't wise to use strings for storing dates, and when (one thinks) one has to, one should use an invariant culture thingy, and not rely on regional settings that could be changed by the user and create havoc overnight.
Luc Pattyn [My Articles] Nil Volentibus Arduum
The quality and detail of your question reflects on the effectiveness of the help you are likely to get. Please use <PRE> tags for code snippets, they improve readability. CP Vanity has been updated to V2.3
|
|
|
|
|
|
You're welcome.
Luc Pattyn [My Articles] Nil Volentibus Arduum
The quality and detail of your question reflects on the effectiveness of the help you are likely to get. Please use <PRE> tags for code snippets, they improve readability. CP Vanity has been updated to V2.4
|
|
|
|
|
Has someone experience applying clustered index more than on one field?
I think it is wrong action. May be it could work with small database. My observation showed such estimation: same table same number of records. Execution query to select all fields from same table without having clustered index on single column is 8sec (650243). In other case I have apply clustered index on primary and foreign keys. Execution last about 12 sec.; basically it decreased performance. So now is a question: if I can apply non clustered index on couple fields, what is the best practice to apply clustered index. Whether it should be only single field to sort data in a table?
We live in a Newtonian world of Einsteinian physics ruled by Frankenstein logic
|
|
|
|
|
By definition, there can only ever be a single clustered index on a table - it is the clustered index that sets the physical order of records. Usually, the clustered index would be on a single field, and generally that will be a key field that auto-numbers. The performance hit for clustered indices is more related to inserts and updates on your data, as if you insert/update a record that is towards the start of the index, and make it fall towards the end, nearly the whole table is physically reordered on the disk to accomodate this.
|
|
|
|
|
Thank you. You confirmed my thoughts.
We live in a Newtonian world of Einsteinian physics ruled by Frankenstein logic
|
|
|
|
|
I have created one Ssis pkg in my local DB.I am connecting to the the remote server by using C# code in this Ssis pkg.But some cases(machines) it's giving error(user-component failure).So i need to conn remote DB and i have to execute Stored procedure in that DB.
Can anyone give me suggestions to conn remote DB from local DB(machine) or Anyone known about this error?
|
|
|
|
|
ravi11038 wrote: I have created one Ssis pkg in my local DB
You are using SSIS.
ravi11038 wrote: I am connecting to the the remote server by using C# code in this Ssis pkg
And SSIS is using C# code. And the C# code is handling the connection.
ravi11038 wrote: But some cases(machines) it's giving error(user-component failure).
So? The solution is obviously to figure out what the connection
ravi11038 wrote: So i need to conn remote DB and i have to execute Stored procedure in that
DB.
A stored procedure on a remote machine will not fix a connection problem to the remote machine.
Now perhaps you want to create a stored procedure on the local machine and it will connect to the remote machine. But that is just moving the connection problem from one place to another and is unlikely to solve the problem if you do not figure out what is actually causing the connection failure.
|
|
|
|
|
Dear All,
We have a table with millions of records,i created a Windows application to retrieve data from mentioned table,it taking too much time to get result,I loaded data in the form load event,its stucking for few minutes to display as data is too much.
anyone can help me out to find any way to retrieve data w/o stuck the application?
thanks
|
|
|
|
|
Do you really need to load million of rows from table on load of form?
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
www.cacttus.com
|
|
|
|
|
|
Member 4650287 wrote: I realized
You realized what?
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
www.cacttus.com
|
|
|
|
|
Besides adding criteria (WHERE) to the data request look into indexes.
|
|
|
|
|
|
|
You can't be creating a report with millions of lines.
So you are actually creating something else. Instead of getting those millions of lines and then processing them you should write a stored procedure that gets only the data you need.
|
|
|
|
|
Hi,
When i execute the query
select convert(varchar(2),datepart(day,'2011-11-06'))
the result i get is 6, i need to get the answer as 06 if only one digit, if 2 then no need of 0 in the first place.
How to get that?
Regards,
YPKI
|
|
|
|
|
You can do something like below.
DECLARE @Input as varchar(10) = '2011-11-06'
select case when datepart(day,@Input) <= 9 then
cast('0' + cast(datepart(day,@Input) as varchar(1)) as varchar(2))
else cast(datepart(day,@Input) as varchar(2)) end AS NewDate
Regards
|
|
|
|
|
If you have always same format of datetime yyyy-MM-dd then you can use substring
e.g select substring('2011-11-06',9,2)
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
www.cacttus.com
|
|
|
|
|
If they are using a date column, I think this would be better
select substring(Cast(@mydate as varchar(10)),9,2)
|
|
|
|
|
How about: (corrected... thanks Luc)
select right('0' + convert(varchar(2),datepart(day,'2011-11-06')), 2)
modified on Tuesday, June 7, 2011 3:49 PM
|
|
|
|
|
left?
Luc Pattyn [My Articles] Nil Volentibus Arduum
The quality and detail of your question reflects on the effectiveness of the help you are likely to get. Please use <PRE> tags for code snippets, they improve readability. CP Vanity has been updated to V2.3 modified on Friday, June 10, 2011 8:41 PM
|
|
|
|
|
Good catch... fixed. It DID produce the correct answer, but that was happenstance, not design.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
