|
More info needed.
If they are on the same network and the servers can "see" each other, there's no reason why you couldn't transfer data... BUT... are they the same kind of database? What is it? Is it SQL Server? Access? Foxpro? DBase? A filing cabinet with a cat5 connection?
|
|
|
|
|
Thanks So Much for your response.Its Much appreciated.
Both databases are in SQL Server 2008 database with same table structures and design.
There are tables with the same structure in both database.
I would like to copy Data from table in One databse tothe other Databse.
Hennce ,I will have one Databse to put all my reporting etc.....
I would like to run it twice a day.As i am running my report twice a day.
Thanks
|
|
|
|
|
Some further information is needed.
What is you database (SQL Server, Oracle, MySQL ???)
Why is the transfer needed (development sync, replicated data, integrated query)
Is this a one off operation?
Frequency of transfer
How do you expect the transfer to be initieated?
Have you investigated the following to see if they suit your needs
Linked Servers
Replication
You have some work to do before you can ask a sensible question.
|
|
|
|
|
Thanks So Much for your response.Its Much appreciated.
Both databases are in SQL Server 2008 database with same table structures and design.
There are tables with the same structure in both database.
I would like to copy Data from table in One databse tothe other Databse.
Hennce ,I will have one Databse to put all my reporting etc.....
I would like to run it twice a day.As i am running my report twice a day.
Thanks
|
|
|
|
|
I would look into replication as you seem to be able to identify 1 database as the "master". It is not a trivial subject, you will need to spend some time reading the BOL and understanding the implications of replication.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Sorry to bother you again.Can you please get me some links in getting this specific problem.I seem to get a general database copy procedures.
Many thanks for your help.
|
|
|
|
|
We have very large tables of price data that need to be searched efficiently. The table consists of
id - bigint (Primary Key w/ clustered index)
iCurve - bigint - links to a small table defining price curves
dtFwdDate - forward date
dtEffDate - effective date
fValue - price
The usual search is
SELECT fValue FROM table WHERE iCurve = 1 AND dtFwdDate='01/01/2010' AND dtEffDate='11/11/2009'
I created an index that keys on iCurve, dtFwdDate, dtEffDate and includes fValue.
It works okay but the index size is now larger than the data.
We have to update this table daily with very large numbers of data points (100,000+)
Questions -
What is the best way to index this table for fast searches?
How can we keep the index size small?
How can we optimize this so insertions are not painful?
In terms of performance, I would rather have performance problems inserting data.
thx
Mark Jackson
|
|
|
|
|
Do you use a sql-server so I would say try to use the sql-profiler and the optimizion tool
in combination and let the optimizer make a proposal for. But the problem will be
that you will not be able to create a small index and have a good performance on it.
At last one idea is to remove the clustered index of the primary key and make it to a not
clustered one and use the clustered index on iCurve, but only if iCurve differs very often.
I read this tip some days ago in the book SQL Server 2008, but its efficent if you do not
often query for the id and if the new clustered column doesn't have too often the same
value.
Greetings
Covean
|
|
|
|
|
This is in MS-SQL server BTW
|
|
|
|
|
Tuning a database is an art and there is no 1 silver bullet, you can only try a number of things and see if they are worth the changes.
For a start the size of your indexes should not concern you, on a heavily indexed table this is not unusual.
If you insert is a bacth job (with 100k records is should be) then it may be worth dropping the indexes for the insert and recreating them. This is a DRASTIC measure and should be tested for cost on another server.
Consider the changes to the clustered index suggested
Consider moving the indexes to another physical drive
Partitioning your data (think archiving but you can query across partitions)
These are but some of your options, I assume you have use query profiler to asses the cost of your queries.
|
|
|
|
|
"hi
i have a confusion i m using this query in asp.vb page now i have
Dim strsearch As String
strsearch = txtsearch1.Text
i want to search records form mytable with the value of strsearch.
strseach is input variable for search a record form mytable
please guide me about this
dadsubmit = New SqlDataAdapter("SELECT AuthorId, UrlUserName, Urlid, UrlAddress, ChooseMedia, Title, Description, ChooseThumbnail, ChooseTopic, Datetime, (SELECT COUNT(CommentId) AS Expr1 FROM CommentTable WHERE (CommentUrlid = Submitnewtable.Urlid)) AS cnt,
DATEDIFF(day, Datetime, GETDATE()) AS diff FROM Submitnewtable WHERE
Title like '%%' &strsearch , consumit)
there is error in block code
|
|
|
|
|
Hi, I don't know mush about VB.net but I think it will be
'%' & strsearch & '%'
Niladri Biswas
|
|
|
|
|
Try the following:
dadsubmit = New SqlDataAdapter(
String.Concat("SELECT AuthorId, UrlUserName, Urlid, UrlAddress, ChooseMedia, Title, ", _
"Description, ChooseThumbnail, ChooseTopic, Datetime, ", _
"(SELECT COUNT(CommentId) AS Expr1 FROM CommentTable WHERE ", _
"(CommentUrlid = Submitnewtable.Urlid)) AS cnt, ", _
"DATEDIFF(day, Datetime, GETDATE()) AS diff ", _
"FROM Submitnewtable WHERE Title like '%", strsearch.Trim(), "%';") _
, consumit)
If you have knowledge, let others light their candles at it.
Margaret Fuller (1810 - 1850)
www.JacksonSoft.co.uk
|
|
|
|
|
|
Hi!
I would like to get first 15 word from a long sentence but not sure how to do this. I guess I should be looking for 15th space in the word but not sure how to do this could some please help?
thank you
|
|
|
|
|
Looking for the 15th space won't help you, as there may be more than one space between words. What about punctuation? Do you need to take that into account?
Basically, you need to parse the string, finding one word at a time (and adding them into a new string) until you reach the end of your 15th word...
There are plenty of ways to do this. One simple way is to read the string one character at a time until you either reach the 15th word (at which point you have the answer you are after) or you reach the end of the string (at which point you can return some meaningful error such as 'string too short').
Have a crack at it and post again when you have some code to show us!!
|
|
|
|
|
If there is more than one space for e.g. 2 space I will count that in too so I will be printing only upto 14 words. could you please help. with below i can count number of words based on space. could you please give me one simple example as to how i can obtain without writing complex function.
SELECT LEN(text) - LEN(REPLACE(text, ' ', '')) + 1 from table
|
|
|
|
|
I think you are confusing words with characters...
If you are after 15 characters, simply use the left function.
select left(FIELDNAME, 15) from TABLENAME
|
|
|
|
|
Hi! I am after 15 words not characters. thanks
|
|
|
|
|
If you want to keep the beginning of a text, not necessarily 15 words, but as much as possible, not exceeding N characters, then you could search for the last space before position N, and keep everything up to that space.
This[^] would give some clues then.
Luc Pattyn
I only read code that is properly indented, and rendered in a non-proportional font; hint: use PRE tags in forum messages
|
|
|
|
|
Try this
declare @str as varchar(100)
declare @startposition int
declare @endposition int
declare @stopingcondition int
declare @delimeter char(1)
set @startposition = 0
set @stopingcondition = 15
set @delimeter =''
set @str = 'I would like to get first 15 word from a long sentence but not sure how to do this. I guess I should be looking for 15th space in the word but not sure how to do this could some please help?' -- original data
--Program starts
;with num_cte as
(
select 1 as rn
union all
select rn +1 as rn
from num_cte
where rn <= len(@str)
)
, get_all_delimited_char_pos_cte as
(
select row_number()over(order by rn) cnt ,rn,chars
from num_cte
cross apply( select substring(@str,rn,1) AS chars) splittedchars
where chars = @delimeter
)
select @endposition = rn from get_all_delimited_char_pos_cte where cnt = @stopingcondition
select SUBSTRING(@str,@startposition,@endposition) as First15thWords
Output:
First15thWords
I would like to get first 15 word from a long sentence but not sure
The approach:
If you do a substring like
declare @str as varchar(100)
set @str = 'I would like to get first 15 word from a long sentence but not sure how to do this. I guess I should be looking for 15th space in the word but not sure how to do this could some please help?' -- original data
select SUBSTRING(@str,0,68)
you will get the first 15 words.
So the challenge was to get the number 68(count by youself and you will find that)
By using the get_all_delimited_char_pos_cte CTE and with the help of cross apply I am able to get the position of the delimited characters and from there I just picked up the position of the 15th occurrence of the delimited charecter
Hope this helps
Niladri Biswas
modified on Wednesday, November 11, 2009 7:58 AM
|
|
|
|
|
Good afternoon. I am working on an automated booking system, and I am trying to figure a strategy for resource availability as it pertains to data storage.
The database has service providers in it (People who do things) and they are each available up to 24 hours a day 7 days a week. They might be available only on Monday from 1 to 2 AM. They need to have the ability to say they are available in different hour brackets.
I am scratching my head a little bit thinking about the database design. I could have 169 columns in the table (1 for userid, and then 1 for each hour of the week), but that sounds laborious. I also considered a binary style string 168 characters long where each hour of the week has a specific string position (1000000100000111110000), but that is equally or more laborious. I also considered the idea of allowing the user to store their information in a table that tracks the day of the week, and the start/end time of their availability. The table could have multiple rows per day so if the user is available from 1 to 5 and then 6 to 10, he or she could have two Monday rows in the DB, but I am not sold on this strategy either.
Anyways, does anyone have experience in this particular kind of data storage that they would like to share? Any and all thoughts are welcome.
Cheers
|
|
|
|
|
You may want to consider 7 columns with a 24 byte array in each column. A blank indicated availability. My reason for this is that is would be easy to generate queries like "who is available on Mondays ?".
select username from availTable where mondayAvail = ""
Your design really depends on how you think you will be using (querying) the data.
You may want to consider the ability to store a person's availability for a given time frame. Consider ... Joe is available Monday (9:00 - 17:00) for January, but after that he will switch to evenings (17:00 - 02:00). With that requirement, then I would go with an effective data in the front of the availability table which indicates FromThisDate ToThisDate on these days of week and these hours, the resource is available.
Sorry I couldn't be more definitive.
Good luck.
|
|
|
|
|
Quite insightful, thank you. In regards to the 24 bit array, when you say that a blank indicates availability how would you manage partial availability? Were you thinking the same sort of array as I suggested prior? (111100001111111111111111 indicates availability at all times aside from 5 to 9 AM)
|
|
|
|
|
Well I would say you can take your bits and .....
I would not even consider the bit type solution, purely from a downstream support aspect. Imagine coming in to support such a system.
I think the table solution is going to be more flexible and supportable and will be more queriable by your users (who probably have no idea what a bit is). I would have 2 tables, Dates and Times, allow the user to enter as many times for a day as they require, supply a propagate forward by week/month methods. This can then interact with a calendar control.
|
|
|
|