August 3, 2005 at 1:19 am
Hi,
I had a problem i want to create the script of few tables ina database on remote server
Can any one help me how to do this.
from
killer
August 3, 2005 at 1:40 am
Use Enterprise Manager. Right click the table and select All tasks/Generate Script.
August 3, 2005 at 2:25 am
Hi,
Thanx Chris.
But as my question (i have 2 sql server Server A and Server B i want to script few tables of server b from server A)
Can anyone help
from
killer
August 3, 2005 at 3:18 am
Well, you could still use the suggestion I made. First connect to server A and generate the script. Then open the script in Query Analyzer and connect to server B, and execute the script.
Other options are to use DTS to do the job for you. You need to be more specific about your needs. Is this a one-time thing? If so I would use the semi-manual approach I first mentioned. Or is it some kind of job you need to set up? Do you just want to create a script of the table schema, or do you actually want to copy the tables including the data between the servers?
August 3, 2005 at 4:55 am
Thanx Chris,
I need to do this by code .
Problem is i have a OLAP server having the same database as my production server but sometime my programmers need to increase the field in few tables.
So when i transfer data my job fail becuase of column error.I cannot write the columnname to transfer data becasue most of the table used in olap development have 200 fields and there is not much space in the job window.
So i want to create the script of the table and compare it with olap tables if it is same then datatrasfer will occur else it will drop the table and create again. this all will be scheduled in job.
Hope u understand
from
killer
August 3, 2005 at 5:42 am
I don't have any script readily available for reading and generating a table definition, but I'm sure there are some here at SSC or at least you'll find it by googling. Another option is to use Profiler to 'record' what Enterprise Manager does when you choose Generate Script and use that as the basis for a script.
August 3, 2005 at 7:29 pm
Hi Chris,
Thanx for ur support.As MSQL Server uses a SP to create a script but the profilor fails to trace how Sql server writes the table structure to the disk.(in a script file).
I manged to get that information in a table by up editing the SP.But i dont have any idea how to create the table from that information.
from
Killer
August 4, 2005 at 8:08 am
Yes, naturally Profiler cannot say anything about how the file is created. That is not SQL Server, it is Enterprise Manager that does that. And I don't see why you would need that anyway. Using the statements that you captured when EM fetched the schema definitions you should be able to build an SP of your own that dynamically reads the schema, stores it in a variable and then recreates it afterwards. But it will definitiely not be easy, there is a lot of steps to work through.
August 4, 2005 at 10:25 pm
Hi Chris,
I altered the sp_helpcolumns this gives all the information related to a table structure and i managed to create a table with records but now i dont have any idea how can i create the table with that information.
becasue how can i pass the datatype in a variable from a table.
hope u understand
from
killer
August 5, 2005 at 1:36 am
Raj, I just reread the entire thread and see now that I misunderstood you a little. If I understand you correctly, you will always drop the (target) table and recreate it if there are differences between the source and target table. Why not then just always drop it, and you will not need to compare them? Or do you need to keep the data in the table if they are equal? You could try something like this:
DECLARE @originalcolumns INT, @equalcolumns INT
SELECT @originalcolumns = COUNT(*)
FROM syscolumns
WHERE [id] = OBJECT_ID('dbo.SOURCE')
SELECT @equalcolumns = COUNT(*)
FROM syscolumns c1
LEFT JOIN syscolumns c2
ON c1.[name] = c2.[name]
AND c1.xusertype = c2.xusertype
AND c1.colid = c2.colid
WHERE c1.[id] = OBJECT_ID('dbo.SOURCE')
AND c2.[id] = OBJECT_ID('dbo.TARGET')
IF @originalcolumns - @equalcolumns = 0
BEGIN
PRINT 'Tables are equal'
END
ELSE
BEGIN
PRINT 'Tables are NOT equal'
END
It is in no means perfect, but I think it would work for you. Then, if the tables are not equal, you could insert something like this code to recreate the table:
DROP TABLE TARGET
SELECT * INTO TARGET
FROM SOURCE
WHERE 1 = 0
August 5, 2005 at 10:39 pm
Thanx Chris,
But i changed like this becasue in the code u posted have missing the sysobject so will not get the correct id for the table in syscolumns .
DECLARE @originalcolumns INT,@objectid int,@originalcolumns1 INT,@objectid1 int
select @objectid=id from sysobjects where name='salesline'
select @objectid1=id from [192.168.1.16].epinav.dbo.sysobjects where name='salesline'
SELECT @originalcolumns = COUNT(*)
FROM syscolumns
WHERE [id] = @objectid
SELECT @originalcolumns1 = COUNT(*)
FROM [192.168.1.16].epinav.dbo.syscolumns
WHERE [id] = @objectid1
IF @originalcolumns - @originalcolumns1 = 0
BEGIN
PRINT 'Tables are equal'
END
ELSE
BEGIN
PRINT 'Tables are NOT equal'
END
thanx killer
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply