|
A common solution is to use replication to maintain a reporting version of the database. Obviously is not quite real time, but its usually near enough. It also allows for some reindexing/denormailisation to aid reporting performance.
Hope this is of some interest.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Thanks Bob, yes it very much is of some interest.
I know SQL Server very well indeed, that is except for replication which ive not honestly come into contact with. I had a feeling it was relevant to this situation so I started reading up last night. You have just confirmed that my hunch was at least along the right lines.
Thanks again.
|
|
|
|
|
Bob, any idea what a reasonably lag time is for the replicated system (presumably configurable but guidlines?)
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Naturally, it depends on transaction volumes and how important it is, but normally for a reporting database I would expect between 1 and 5 minutes, as opposed to a hot standby where it should be as close as possible to real time.
This msdn article gives a good overview of the overheads etc.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
As Bob said replication could be one solution. Especially transactional replication in your case.
Another option could be log shipping. With log shipping you may have to redesign your backup strategy, but on the other hand you gain a standby database, which can be used for reporting.
The need to optimize rises from a bad design
|
|
|
|
|
guys, i want to make a program using vb6. This program can update sql server 2000 server database based on data taken from MS access 2000. The algorithm that i applied is first, the program read the data from Access database ,then, it is saved temporary. next it will open the connection to the sql server then, compare the data between sql server and Access. If the data is different, the data in sql server will be replaced with the data in Access. Fortunately, until now i am stuck in how i can connect this two database together and compare the value.
Does the algorithm that i applied contain some mistakes and is there any better suggest algorithm for this problem?
Could anyone help me to give some code in essential part of the program?
Please. I need your help.
|
|
|
|
|
Its one way of doing it, but why not have 2 connections open, 1 to Access and 1 to SQL Server and cut out the temporary save? All you need to do then is run a loop over the 2 recordset (ensure you order the results the same way) and soe the details for insert/updatw/delete. When both recordsets are ended apply the changes.
There are probably several other ways too.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Hi
I want run a query like this:
select * from sp_MyTable_Select
i want make a query from result of some stored procedures
like this:
Select * from sp_fKeys Where COLUMN_NAME='X'
i know it is wrong
help me to found how can i do this....
tnx
|
|
|
|
|
Few issues:
- you cannot select from stored procedure directly. Just execute it to get the results
- do not start the name in your own procedures with sp_. That's reserved for system procedures.
- if you want to find foreign keys using a select statement, query sys.foreign_key_columns
The need to optimize rises from a bad design
|
|
|
|
|
you can do this with table functions
CREATE FUNCTION MyFunction()
RETURNS @tbl TABLE(COLUMN_NAME VARCHAR(50))
AS
SELECT
'A'
INTO @tbl
FROM someTable
then
SELECT * FROM MyFunction() WHERE COLUMN_NAME='A'
|
|
|
|
|
I saw the INTO @tbl and thought you were auto creating the column, as in Select * into MyTable from YourTable
Every had the hairs on the back of your neck stand up - that was my first reaction  .
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi All,
I wanted to connect to a remote SQl 2005 database via VPN on Windows XP. After configuring VPN connection on my local area Network. I dont seem to login to the Sql server database.
I am running Service pack 2.Does it have any thing to do with Operating System?
Could you please advice?
Thank you
|
|
|
|
|
By default SQL Server does not permit remote connections. You have to enable TCP/IP on SQL Server.
If that is not the issue, then you might want to look at your firewall settings.
|
|
|
|
|
hello,
i am new to programming and i have gotten far in learning C# in the .Net environment. i am at the point where i want to create Databases with SQL and link them to my programs but i cannot seem to find any headway or pointers in how to do that.
can anyone help me on this issue. i am searching the forums for any clue on how to start using SQL but without luck.
any suggestion is welcome. thank you
|
|
|
|
|
Try searching Google for ADO.NET, that should give you loads of articles.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
download samples here about how to connect to a database 
nelsonpaixao@yahoo.com.br
trying to help & get help
|
|
|
|
|
how do you extract a photo from database in a JPEG2000 format and binary version of the photo ,i have a table in my database that stored photos like this eg dbfec061-a467-4833-8db3-9badfe086e7a.jp2
thanks
regards paula
|
|
|
|
|
Basically you can do something like:
- you read the data in binary form
- store the data to a temp file
- open the temp file using relevant program
This article is a little bit old, but it should get you to start: How To Read and Write BLOB Data by Using ADO.NET with Visual C# .NET[^]
Hope it helps,
Mika
The need to optimize rises from a bad design
|
|
|
|
|
download a sample code here, search for something like load/save picture in sql server. You can store in database picture or gifs as well, videos i dont know but i think it works as well. 
nelsonpaixao@yahoo.com.br
trying to help & get help
|
|
|
|
|
Hi,
I want to retrieve all the column names by a query of a table in SQL SERVER 2005.
For Example: I Have a table: Table1, It has 5 columns: Column1,Column2,Column3,.......
So, I want to write a query that will display all these column names.
Table1
--------
Column1
Column2
Column3
Column4
Column5
I write this query to do the same:
Select column_name, data_type from user_tab_columns where table_name = user_details
But it is not working. Is this a right query or some mistakes are there.
|
|
|
|
|
astrovirgin wrote: But it is not working. Is this a right query or some mistakes are there.
Well, if its not working....
Where to start?
The error message you get is pretty obvious
Invalid object name 'user_tab_columns'.
I'll give you 1 hint (and there are several ways of doing this), look at information_schema.columns
Bob
Ashfield Consultants Ltd
|
|
|
|
|
|
The information_schema.columns technique only works for SQL Server.
I use a lot of different databases, and I don't have the constraint that it be "in SQL".
Here's a portion of my DatabaseAccessor[^]:
System.Data.DataTable result = null ;
try
{
result = this.ExecuteReader
(
string.Format
(
"SELECT * FROM [{0}] WHERE 0=1"
,
TableName
)
).GetSchemaTable() ;
result.TableName = "Columns" ;
|
|
|
|
|
Fair enough, although the OP did specify sql server. I suspect (although I have never tried it) that you could use SMO as well.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Ashfield wrote: the OP did specify sql server
Oh, yes, absolutely, and he wanted it to be an SQL statement too, but after finding one way for SQL Server, another way for Oracle, another way for Ingres, and then facing MySQL, Caché, FireBird, Access, etc. I realized I already had a way that works with all of them.
I aim for database agnosticism. (I do, however, prefer SQL Server.)
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
