January 16, 2012 at 7:24 am
Hi there, I have 2 database servers. I would like to take a table from one of the servers and use that table to make a new table on the 2nd database on a daily basis. This is something I am currently doing in Access (without of course storing the new table on a SQL database), but which I woud like to transfer to SQL Server. I just started using SQL Server so I am not too familiar with what I would have to do to accomplish this. I have the full SQL coding to make the table, I just need some instructions on how to "set up" the template in SQL Server Management Studio. Thanks
January 16, 2012 at 7:31 am
i'd avoid this at all costs.
functionally, I see you want to put the data into separate ytables because it makes "sense"...you want to compartimentalize the data the way you think of it.
what happens if you need the data aggregated for the week? join 5 tables?
from a SQL standpoint, all similar the data could and should be kept in the same .
from there, you can always limit your queries to a single day, but it provides much more flexibility. when your data is together.
you could always create a VIEW for each day (why though? WHY!?) that limits the data, but i cannot think of any good reason to do this at all.
Lowell
January 16, 2012 at 7:33 am
guerillaunit (1/16/2012)
Hi there, I have 2 database servers. I would like to take a table from one of the servers and use that table to make a new table on the 2nd database on a daily basis. This is something I am currently doing in Access (without of course storing the new table on a SQL database), but which I woud like to transfer to SQL Server. I just started using SQL Server so I am not too familiar with what I would have to do to accomplish this. I have the full SQL coding to make the table, I just need some instructions on how to "set up" the template in SQL Server Management Studio. Thanks
I would define a linked server on "source" server pointing to "target" server then you can execute whaterver you want agaisnt the linked server.
An alternative would be replication between "source" and "target" servers.
Hard to tell not having much information about the particular scenario.
Question... this "make a new table on the 2nd database on a daily basis" means that after one your you would have 365 new tables on the "target" server?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.January 16, 2012 at 7:41 am
Hi Lowell,
This isn't a productiont table I am producing. It's a reference table that I use for research. I currently make the table in Access, but I would like to transfer that to SQL Server to get a better execution time and more control. Not sure why you would suggest a view for this...
Can anyone walk me the steps to set this process up in SQL Server? Specifically, I am a little hung up as to how to reference a table from one server (ie. "Reports" server) to create a table in another server (ie. "Analysis" server).
January 16, 2012 at 7:43 am
Hey Paul,
I am not making a separate table for each day. Just making a new version of the same table everyday and then "pasting" over the previous day's version
January 16, 2012 at 7:48 am
guerillaunit (1/16/2012)
Hey Paul,I am not making a separate table for each day. Just making a new version of the same table everyday and then "pasting" over the previous day's version
Okay... that means you are replicating/refreshing the content of the table in a daily basis as opposed to creating a new table in a daily basis.
The target table on "target" database has to be created just once.
Your refresh process, if the table is not so big and you are new to replication can be done in a fairly easy way by:
1- Create target table on target database - this is a one off task.
2- Create a linked server pointing to "target" server - this is a one off task
3- Schedule a daily job on source database, pointing to target database.
3.1- Truncate target table.
3.2- Insert rows into target table.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.January 16, 2012 at 8:01 am
Bit of a stab in the dark: -
USE [msdb]
IF EXISTS (
SELECT job_id
FROM msdb.dbo.sysjobs_view
WHERE name = N'YourJobName'
)
BEGIN
EXEC msdb.dbo.sp_delete_job @job_name = N'YourJobName',
@delete_unused_schedule = 1
END
DECLARE @jobId BINARY (16)
EXEC msdb.dbo.sp_add_job @job_name = N'YourJobName',
@enabled = 1,
@notify_level_eventlog = 2,
@notify_level_email = 0,
@notify_level_netsend = 0,
@notify_level_page = 0,
@delete_level = 0,
@description = N'',
@category_name = N'',
@owner_login_name = N'sa'
EXEC msdb.dbo.sp_add_jobstep @job_name = N'YourJobName',
@step_name = N'Copy over table',
@step_id = 1,
@cmdexec_success_code = 0,
@on_success_action = 1,
@on_success_step_id = 0,
@on_fail_action = 2,
@on_fail_step_id = 0,
@retry_attempts = 0,
@retry_interval = 1,
@os_run_priority = 0,
@subsystem = N'TSQL',
@command = N'IF EXISTS (
SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N''yourSecondDatabase.yourSchema.yourNewTable'') AND type IN (N''U'')
)
BEGIN
DROP TABLE yourSecondDatabase.yourSchema.yourNewTable
END
SELECT *
INTO yourSecondDatabase.yourSchema.yourNewTable
FROM yourFirstDatabase.yourSchema.yourOldTable',
@database_name = N'yourSecondDatabase',
@flags = 0
EXEC msdb.dbo.sp_add_jobserver @job_name = N'YourJobName',
@server_name = N'(local)'
EXEC msdb.dbo.sp_add_jobschedule @job_name = N'YourJobName',
@name = N'Daily',
@enabled = 1,
@freq_type = 4,
@freq_interval = 1,
@freq_subday_type = 1,
@freq_subday_interval = 0,
@freq_relative_interval = 0,
@freq_recurrence_factor = 1,
@active_start_date = 20120116,
@active_end_date = 99991231,
@active_start_time = 0,
@active_end_time = 235959
--edit--
Corrected typo
January 16, 2012 at 8:44 am
Hi Paul,
Can you walk me through on how to have my target server point to my linked server? What would the query syntax me for this?
Cadavre,
Is the code you posted the daily job scheduling?
January 16, 2012 at 8:46 am
guerillaunit (1/16/2012)
Cadavre,Is the code you posted the daily job scheduling?
Yes. You'd need to go through and change the job name, the table names, schema names and database names. Also, it's currently scheduled to run at 00:00:00 every day, so you may want to change that.
January 16, 2012 at 9:01 am
guerillaunit (1/16/2012)
Can you walk me through on how to have my target server point to my linked server? What would the query syntax me for this?
Create your linked server pointing Source server to Target server.
Please check here http://msdn.microsoft.com/en-us/library/ff772782.aspx
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.January 16, 2012 at 1:29 pm
guerillaunit (1/16/2012)
Hi Lowell,This isn't a productiont table I am producing. It's a reference table that I use for research. I currently make the table in Access, but I would like to transfer that to SQL Server to get a better execution time and more control. Not sure why you would suggest a view for this...
Can anyone walk me the steps to set this process up in SQL Server? Specifically, I am a little hung up as to how to reference a table from one server (ie. "Reports" server) to create a table in another server (ie. "Analysis" server).
Lowell suggested a view because he did not really know what you were doing and preferred a view because it acts as a table without separating the data (a view is a saved query referenced like a table).
Being that you are coming from Access to SQL Server, I think you may be approaching the situation from more of a spreadsheet standpoint versus a database standpoint (Access kind of being in between). If you can tell us why you want to have this separate database and a table that is overwritten, maybe we can suggest a better way. For example, I may want to retain a record of all of the data in this table. So I would not overwrite the data each day, but add to it with a new column inserted_date or something. This way I can simply get the data for that date when I need it, or I can do analysis on all of the data. I would also ask why you are pulling this data and overwriting it every day when all of the data is already in the primary database? You can just query against it using a filter.
We can give you simple answers here on how to do what you are asking, but we would prefer to look at it from a larger point of view to help you think of the best solution for your whole situation.
Jared
CE - Microsoft
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply