Showing posts with label copy. Show all posts
Showing posts with label copy. Show all posts

Friday, March 9, 2012

programmatically create SqlServer JobSchedule

I wanted to programmatically create SqlServer JobSchedule

The job is to copy data from a particular table in one Database to another table in another DataBase
User should be able to Schedule/modify Date and Time on which a job is to be executed is to be configured through a UI Screen (WebForm.aspx)

I am using VSStudion2003 ,ASP.net with VB.net with SQL2000 as database .

ANY idea on how to do this ???

Please help me?

Thank u all in advance

There are some job related system stored procedures in SQL2000. To schedule a existing job, you can use a SQLCommand to call 'sp_add_jobschedule' in your application, with parameters from textboxes of UI. Please go to this website for more information:

http://msdn.microsoft.com/library/en-us/tsqlref/ts_sp_adda_6ijp.asp?frame=true

Wednesday, March 7, 2012

Programmatically copy table structure?

I have been looking for a way to copy the table definition (column, identities, default constraints, etc) programmatically between databases.
I have tried 'select * into...' but that only copies column names, data types and nullibility.
sp_helptext does not have any data on table objects.

So, the only thing I can think of is to use DDL triggers to keep the tables in sync. But I would prefer a method where I could run a stored procedure with a table name and have it return the DDL to create that table.

Any help would be welcome.

-Preston M. Price
You should use a management API like SQL DMO/SMO instead of writing server side code. There is no built-in mechanism on the server-side to do scripting - you have to write your own code. DMO/SMO have objects to script schema easily but not changes comparing two tables for example. It is not clear if you require that functionality. For comparing objects and generating schema change scripts, you need to use 3rd party tools.|||I was working with SMO and DMO and they do what I want, but there is no way to include their functionality inside a CLR stored procedure.

So as far as I can tell there is no way to programmatically (T-SQL or CLR supported procedures) copy the complete structure of a table (with or without data) from inside SQL Server, this seems like a weakness to me.

-Preston M. Price