Re: Remote Server(DB) conn from local DB - Database Discussion Boards

Luc Pattyn8-Jun-11 15:05

8-Jun-11 15:05

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.

Smile | :)

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

Re: ORA-01843: not a valid month - Error

Sathiyasoori.S8-Jun-11 17:31

Sathiyasoori.S

8-Jun-11 17:31

Thanks.

Re: ORA-01843: not a valid month - Error

Luc Pattyn14-Jun-11 1:58

Luc Pattyn

14-Jun-11 1:58

You're welcome.

Smile | :)

Luc Pattyn [My Articles] Nil Volentibus Arduum

clustered index more than on one field

MecAlex8-Jun-11 2:52

MecAlex

8-Jun-11 2:52

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?
Confused | :confused:

We live in a Newtonian world of Einsteinian physics ruled by Frankenstein logic

Re: clustered index more than on one field

_Damian S_8-Jun-11 15:38

_Damian S_

8-Jun-11 15:38

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.

Reminiscing just isn't what it used to be!!

Booger Mobile - My bright green 1964 Ford Falcon - check out the blog here!! | If you feel generous - make a donation to Camp Quality!!

Re: clustered index more than on one field

MecAlex8-Jun-11 21:59

MecAlex

8-Jun-11 21:59

Thank you. You confirmed my thoughts.

We live in a Newtonian world of Einsteinian physics ruled by Frankenstein logic

Remote Server(DB) conn from local DB

ravi110387-Jun-11 7:04

ravi11038

7-Jun-11 7:04

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?

Re: Remote Server(DB) conn from local DB

jschell7-Jun-11 10:08

jschell

7-Jun-11 10:08

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.

SQL Server 2008

Member 46502877-Jun-11 1:24

Member 4650287

7-Jun-11 1:24

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

Re: SQL Server 2008

Blue_Boy7-Jun-11 1:28

Blue_Boy

7-Jun-11 1:28

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

Re: SQL Server 2008

Member 46502877-Jun-11 2:03

Member 4650287

7-Jun-11 2:03

No ... I realized

Re: SQL Server 2008

Blue_Boy7-Jun-11 4:08

Blue_Boy

7-Jun-11 4:08

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

Re: SQL Server 2008

Corporal Agarn7-Jun-11 3:34

Corporal Agarn

7-Jun-11 3:34

Besides adding criteria (WHERE) to the data request look into indexes.

Re: SQL Server 2008

thatraja7-Jun-11 9:49

thatraja

7-Jun-11 9:49

Use paging and filtering in result-set. Look at this article.

SQL Server Paging Results[^]

thatraja

My Tip/Tricks
My Dad had a Heart Attack on this day so don't...

Re: SQL Server 2008

Member 46502877-Jun-11 19:37

Member 4650287

7-Jun-11 19:37

thanks for replies

Re: SQL Server 2008

jschell7-Jun-11 10:09

jschell

7-Jun-11 10:09

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.

Date Convertion

yesu prakash6-Jun-11 23:02

yesu prakash

6-Jun-11 23:02

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

Re: Date Convertion

^That's Aragon7-Jun-11 1:09

^That's Aragon

7-Jun-11 1:09

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

Re: Date Convertion

Blue_Boy7-Jun-11 1:12

Blue_Boy

7-Jun-11 1:12

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

Re: Date Convertion

Corporal Agarn7-Jun-11 5:29

Corporal Agarn

7-Jun-11 5:29

If they are using a date column, I think this would be better
select substring(Cast(@mydate as varchar(10)),9,2)

Re: Date Convertion [modified]

Tim Carmichael7-Jun-11 9:21

Tim Carmichael

7-Jun-11 9:21

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

Re: Date Convertion

Luc Pattyn7-Jun-11 9:45

Luc Pattyn

7-Jun-11 9:45

left?

Luc Pattyn [My Articles] Nil Volentibus Arduum

modified on Friday, June 10, 2011 8:41 PM

Re: Date Convertion

Tim Carmichael7-Jun-11 9:50

Tim Carmichael

7-Jun-11 9:50

Good catch... fixed. It DID produce the correct answer, but that was happenstance, not design.

Re: Date Convertion

yesu prakash7-Jun-11 18:10

yesu prakash

7-Jun-11 18:10

gr8...exactly what i required..
thankyou

Re: Date Convertion

^That's Aragon7-Jun-11 20:21

^That's Aragon

7-Jun-11 20:21

Good call

Have my 5.

Regards

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.