February 24, 2009 at 9:07 am
I have a situation where I need some help/suggestions. I have a SQL Server 2005 db that drives a web site. The data for this web site originates from several larger dbs on the back end. One of these source databases happens to be a SQL Server 2005 database as well which is on the same server. On a scheduled basis, daily and monthly, a process kicks off and gathers all the info from the multiple data sources and applies a bunch of business logic and stores it in a table. From there the web site db needs to suck this info in to support the web site. The schema on the web site db is much diffent from the source table.
What I need is some ideas on how I may be able to keep the web site db up to date after the back end dbs run their refresh processes. My idea is to create a stored proc (or several) on the web site db to support the unique loading logic effort. When the process is kicked that collects the data from all these sources to simply call the web site db spoc(s) with an xml representation of the table data to load. Does this seem reasonable or is there some other means to achive this behavior?
If this seems like a reasonable approach, how do you call connect/call a sproc from w/in a diffent db on the same server?
Thanks in advance!
February 24, 2009 at 9:35 am
I'm not sure I have the complete picture. Are the sources of the web databases data all SQL Server databases or some other kind of data store? Does the "collection" process run in SQL Server?
If the data collection process is scheduled in a SQL Agent job, you could put the execution of the loading stored procedure in a step in the job so it runs after the collection step(s) complete. You can specify the database where the stored procedure resides.
Another option is to create an SSIS package that collects the data and loads it in the web database. The package can be executed in a scheduled job.
Greg
February 24, 2009 at 9:36 am
Procs is a pretty standard way to do that kind of thing.
If you want to call a proc in one database from another database, you use a three-part-name. Four-part-name can call a proc in a database on a different server. Looks like this:
Three-part:exec databasename.schemaname.procname
Four-part:exec servername.databasename.schemaname.procname
On the same server, if the databases are MyDB1 and MyDB2, and the proc is dbo.MyProc in MyDB2, called from MyDB1, the command would be:
exec MyDB2.dbo.MyProc
Clear?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 24, 2009 at 9:48 am
Hi, thanks for your response.
"I'm not sure I have the complete picture. Are the sources of the web databases data all SQL Server databases or some other kind of data store? Does the "collection" process run in SQL Server?"
No actually they are not. Some are Oracle. The collection process is run in a SQL Server and this is the db that will hold the source table for web site db. I was thinking rather than having that process create this and possible several simalar tables, why not simply serialize the table to xml and pass it to the web site db for loading straight away. No need to wait for a scheduled event just push it.
Hopefully this helps to complete the picture, if not let me know, I can elaborate.
Thanks so much for your help!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply