· 8 years ago · Nov 02, 2017, 11:06 AM
1
2/* automatic SOURCE to STAR Update */
3
4DECLARE @count int;
5DECLARE @sql nvarchar(max);
6DECLARE @SourceDB as varchar(150);
7DECLARE @SourceCatalog as varchar(150);
8DECLARE @SourceSchema as varchar(150);
9DECLARE @SourceTable as varchar(150);
10DECLARE @SourceQuery as varchar(150);
11DECLARE @TargetDB as varchar(150);
12DECLARE @TargetCatalog as varchar(150);
13DECLARE @TargetSchema as varchar(150);
14DECLARE @TargetTable as varchar(150);
15DECLARE @TableFilter as varchar(500);
16DECLARE @name sysname ;
17DECLARE @PK_ColumnName as varchar(150);
18DECLARE @PK_CONSTRAINT_NAME as varchar(150);
19DECLARE @PK_ID varchar(max)
20
21
22DECLARE @USERNAME as varchar(max)
23DECLARE @PASSWORD as varchar(max)
24
25
26/*************************************************************************/
27/** Logtabelle Copy and Delete **/
28/*************************************************************************/
29 EXEC CFG_LOG.dbo.pr_DelUpLog
30
31
32/*************************************************************************/
33/********************** Drop and Insert INTO **********************/
34/*************************************************************************/
35
36INSERT INTO CFG_LOG.dbo.LogTable
37 ([DateTime]
38 ,[Funktion]
39 ,[Text]
40 ,[Status])
41 SELECT SYSDATETIME() as ZEIT, 'IMPORT' , 'Der Import wurde gestartet' , 'INFO' as Status ;
42
43
44DECLARE cur_cfg_sta_import CURSOR READ_ONLY
45FOR
46 SELECT Query
47 ,SourceDB
48 ,SourceCatalog
49 ,SourceSchema
50 ,SourceTable
51 ,TargetDB
52 ,TargetCatalog
53 ,TargetSchema
54 ,TargetTable
55 ,ISNULL(Tablefilter,'')
56 ,LnkUser
57 ,LnkPw
58 FROM cfg_log.dbo.CFG_STA_IMPORT
59 WHERE upper([UPDATE]) = 'J'
60
61
62 OPEN cur_cfg_sta_import
63 FETCH NEXT
64 FROM cur_cfg_sta_import
65 INTO @SourceQuery,@SourceDB, @SourceCatalog, @SourceSchema, @SourceTable, @TargetDB, @TargetCatalog, @TargetSchema, @TargetTable, @Tablefilter, @USERNAME, @PASSWORD
66
67/*
68******** Tablefilter ********
69Delticket : where datediff(mm, proddate, getdate()) between 0 and 2
70Deltickdet : where id in (select id from simma.delticket where datediff(mm, proddate, getdate()) between 0 and 2)
71*****************************
72*/
73
74WHILE (@@FETCH_STATUS <> -1)
75BEGIN
76
77 --BEGIN TRY
78 -- BEGIN TRANSACTION
79 set @name = N'' + @TargetCatalog + '.' + @TargetSchema + '.' + @TargetTable
80 IF EXISTS (SELECT * FROM STA.sys.objects WHERE object_id = OBJECT_ID( @name ) AND type in (N'U') )
81 BEGIN
82 IF @TableFilter =''
83 BEGIN
84 set @sql = 'DROP TABLE ' + @TargetCatalog + '.' + @TargetSchema + '.' + @TargetTable
85 EXEC(@sql)
86 PRINT ''
87 PRINT 'Löschen von Tablle ' + @TargetCatalog + '.' + @TargetSchema + '.' + @TargetTable
88 END
89 ELSE
90 BEGIN
91 set @sql = 'DELETE FROM ' + @TargetCatalog + '.' + @TargetSchema + '.' + @TargetTable + ' ' + @TableFilter
92 EXEC(@sql)
93 PRINT ''
94 PRINT @sql
95 END
96 END
97
98 BEGIN
99 --IF @TableFilter = ''
100 -- BEGIN
101 -- set @sql = 'SELECT * INTO ' + @TargetCatalog + '.' + @TargetSchema + '.' + @TargetTable + ' FROM ' + @SourceDB + '.' + @SourceCatalog + '.' + @SourceSchema + '.' + @SourceTable
102 -- END
103 -- ELSE
104 -- BEGIN
105 -- Entfernen von [] des Linked-Server-Namens
106 declare @linkedserverName nvarchar(150) = REPLACE(REPLACE(@SourceDB, '[',''), ']','')
107
108 -- Erstellen von Linked Server, wenn nicht vorhanden
109 if not exists(select server_id from sys.servers where name = @linkedserverName)
110 begin
111 exec master.dbo.sp_addlinkedserver @server=@linkedserverName, @srvproduct=N'SQL Server'
112 EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=@linkedserverName,@useself=N'False',@locallogin=NULL,@rmtuser=@USERNAME,@rmtpassword=@PASSWORD
113 end
114
115 -- check if non sql server based rdbms engine
116 declare @query_text nvarchar(max)
117 SET @query_text = 'select distinct columns.table_name from openquery(' + @SourceDB + ', '' select t.name as table_name from sys.objects o inner join (select object_id, name from sys.objects) t on o.parent_object_id = t.object_id inner join sys.all_columns c on c.object_id = t.object_id'') columns where columns.table_name = ''' + @TargetTable + ''''
118
119 declare @ret_val int
120 set @ret_val = 0
121
122 BEGIN TRY
123 begin transaction
124 exec sp_executesql @query_text
125 set @ret_val = @@ROWCOUNT;
126 commit transaction
127 END TRY
128 BEGIN CATCH
129 rollback transaction
130 PRINT @query_text
131 IF ERROR_NUMBER() = 7411
132 BEGIN
133 PRINT 'MYSQL: ' + @SourceDB + ', ' + @SourceCatalog
134 GOTO NEXT_RESULTSET
135 END
136 ELSE
137 BEGIN
138 insert into CFG_LOG.dbo.LogTable
139 ([DateTime],[Funktion],[Text],[Status])
140 SELECT SYSDATETIME() as ZEIT, 'ErrorHandling' , ERROR_MESSAGE() + ' ErrorNumber' + CAST(ERROR_NUMBER()as VARCHAR(4)) as Error, 'Error' as a ;
141
142 PRINT ERROR_MESSAGE()
143
144 CLOSE cur_cfg_sta_import
145 DEALLOCATE cur_cfg_sta_import
146
147 return
148 END
149 END CATCH
150
151 -- Anfrage um zu prüfen, ob die Tabelle bereits im Zielsystem vorhanden ist
152 SET @query_text = 'select t.name as table_name from sys.objects o inner join (select object_id, name from sys.objects) t on o.parent_object_id = t.object_id inner join sys.all_columns c on c.object_id = t.object_id where t.name = ''' + @TargetTable + ''''
153 set @ret_val = 0
154
155 exec sp_executesql @query_text
156 set @ret_val = @@ROWCOUNT;
157
158 if not exists(select * from sta.sys.schemas where name = @TargetSchema)
159 begin
160 print 'Schema nicht vorhanden'
161 declare @schema_query nvarchar(max)
162
163 set @schema_query = 'exec '+ QUOTENAME(@TargetCatalog) + '..sp_executesql N''CREATE SCHEMA [' + @TargetSchema + '] AUTHORIZATION [dbo]'''
164 execute(@schema_query)
165 end
166
167 -- Erstellen der Tabellenstruktur, wenn nicht vorhanden
168 -- Auslesen der Struktur am Linked Server
169 -- Erstellen der Tabellen auf diesem Server
170 IF (@ret_val > 0)
171 begin
172 print 'Tabellenstruktur vorhanden'
173 end
174 else
175 begin
176 print 'Tabellenstruktur nicht vorhanden'
177
178 DECLARE @SQL_CREATE_TABLE AS NVARCHAR(MAX)
179 SET @SQL_CREATE_TABLE = 'CREATE TABLE ' + @TargetCatalog + '.' + @TargetSchema + '.' + @TargetTable + ' ( '
180
181 DECLARE @COLUMN_NAME sysname
182 DECLARE @DATA_TYPE sysname
183 DECLARE @MAX_LENGTH smallint
184 DECLARE @PRECISION tinyint
185 DECLARE @SCALE tinyint
186 DECLARE @IS_NULLABLE bit
187
188 SET @query_text = 'DECLARE cur_columns CURSOR READ_ONLY
189 FOR
190 select columns.name as columnname, columns.datatype, columns.max_length,
191 columns.precision, columns.scale, columns.is_nullable
192 from openquery(' + @SourceDB + ',' +
193 '''select distinct c.column_id, c.name, c.max_length, c.precision, c.scale, c.is_nullable, ( select top 1 name from ' + @SourceCatalog + '.sys.types types where c.system_type_id = types.system_type_id) as datatype
194 from ' + @SourceCatalog + '.sys.objects o
195 inner join (select object_id, name as table_name from '+ @SourceCatalog +'.sys.objects) t on o.parent_object_id = t.object_id and t.table_name = ''''' + @SourceTable + ''''' inner join '+ @SourceCatalog +'.sys.all_columns c on c.object_id = t.object_id order by c.column_id'') columns where columns.datatype != ''sysname'''
196
197 -- Execute the cursor
198 exec sp_executesql @query_text
199
200 OPEN cur_columns
201 FETCH NEXT
202 FROM cur_columns
203 INTO @COLUMN_NAME,@DATA_TYPE, @MAX_LENGTH, @PRECISION, @SCALE, @IS_NULLABLE
204
205 WHILE (@@FETCH_STATUS <> -1)
206 BEGIN
207 --PRINT 'Constructing DDL statement ...'
208
209 SET @SQL_CREATE_TABLE = @SQL_CREATE_TABLE + '[' + @COLUMN_NAME + '] [' + @DATA_TYPE + ']'
210
211 IF @DATA_TYPE = 'varchar' OR @DATA_TYPE = 'nvarchar' OR @DATA_TYPE = 'char' OR @DATA_TYPE = 'nchar'
212 BEGIN
213 set @SQL_CREATE_TABLE = @SQL_CREATE_TABLE + '(' + cast(@MAX_LENGTH / 2 as nvarchar(max)) + ')'
214 END
215
216 IF @IS_NULLABLE = 0
217 BEGIN
218 set @SQL_CREATE_TABLE = @SQL_CREATE_TABLE + ' NOT NULL'
219 END
220 ELSE
221 BEGIN
222 set @SQL_CREATE_TABLE = @SQL_CREATE_TABLE + ' NULL'
223 END
224
225 SET @SQL_CREATE_TABLE = @SQL_CREATE_TABLE + ','+ CHAR(10)
226
227 FETCH NEXT
228 FROM cur_columns
229 INTO @COLUMN_NAME,@DATA_TYPE, @MAX_LENGTH, @PRECISION, @SCALE, @IS_NULLABLE
230 END
231
232 -- Check if we received an empty record set.
233 IF NULLIF(@SQL_CREATE_TABLE, '') IS NULL
234 begin
235 CLOSE cur_columns
236 DEALLOCATE cur_columns
237 CLOSE cur_cfg_sta_import
238 DEALLOCATE cur_cfg_sta_import
239 print 'ERROR: No data received from linked server'
240 return
241 end
242
243 SET @SQL_CREATE_TABLE = CAST (SUBSTRING(@SQL_CREATE_TABLE, 1, LEN(@SQL_CREATE_TABLE)-2) as nvarchar(max)) + ')'
244 PRINT 'Done creating DDL statement'
245
246 CLOSE cur_columns
247 DEALLOCATE cur_columns
248
249 -- CREATE TABLE
250 PRINT 'Executing the following statement' + CHAR(10)+ @SQL_CREATE_TABLE
251 BEGIN TRY
252 BEGIN TRANSACTION
253 exec sp_executesql @SQL_CREATE_TABLE
254 COMMIT TRANSACTION
255 END TRY
256 BEGIN CATCH
257 ROLLBACK TRANSACTION
258 insert into CFG_LOG.dbo.LogTable
259 ([DateTime],[Funktion],[Text],[Status])
260 SELECT SYSDATETIME() as ZEIT, 'ErrorHandling' , ERROR_MESSAGE() + ' ErrorNumber' + CAST(ERROR_NUMBER()as VARCHAR(4)) as Error, 'Error' as a ;
261 END CATCH
262 end
263
264 PRINT 'Erstellen von Primary Keys: START ... '
265
266 SET @query_text =
267 '''select STUFF((select distinct '''', '''' + col_.name from ' + @SourceCatalog + '.sys.indexes i
268 inner join ' + @SourceCatalog + '.sys.objects t on t.object_id = i.object_id
269 inner join ' + @SourceCatalog + '.sys.index_columns idx on idx.index_id = i.index_id
270 inner join ' + @SourceCatalog + '.sys.index_columns col on col.object_id = i.object_id
271 inner join ' + @SourceCatalog + '.sys.columns col_ on col_.column_id = col.column_id and col_.object_id = i.object_id
272 where is_primary_key = 1 and t.name = ''''' + @SourceTable +
273 ''''' FOR XML PATH('''''''')), 1, 2, '''''''') as stuff'''
274
275 SET @query_text =
276 '''select STUFF((SELECT
277 '''', '''' + c.Name
278 FROM ' + @SourceCatalog + '.sys.indexes i
279 INNER JOIN ' + @SourceCatalog + '.sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
280 INNER JOIN ' + @SourceCatalog + '.sys.columns c ON ic.object_id = c.object_id and ic.column_id = c.column_id
281 INNER JOIN ' + @SourceCatalog + '.sys.objects o ON i.object_id = o.object_id
282 INNER JOIN ' + @SourceCatalog + '.sys.schemas sc ON o.schema_id = sc.schema_id
283 WHERE i.is_primary_key = 1
284 AND o.name = ''''' + @SourceTable +
285 '''''
286 ORDER BY o.Name, i.Name, ic.key_ordinal
287 FOR XML PATH('''''''')), 1, 2, '''''''') as stuff'''
288
289 DECLARE @PK_Columns nvarchar(max);
290 SET @query_text = 'select @PK_Columns=(ISNULL(stuff,'''')) from openquery(' + @SourceDB + ', ' + @query_text + ')'
291 --print @query_text
292
293 exec sp_executesql @query_text, N'@PK_Columns NVARCHAR(MAX) OUTPUT', @PK_Columns = @PK_Columns OUTPUT
294
295 -- Primary Keys wurden gefunden
296 IF @PK_Columns <> ''
297 BEGIN
298 Print 'Es wurden Primary Keys gefunden'
299
300 DECLARE @PK_Name nvarchar(max)
301 SET @PK_Name = ''
302
303 set @query_text = '''select distinct i.name from ' + @SourceCatalog + '.sys.indexes i
304 inner join ' + @SourceCatalog + '.sys.objects t on t.object_id = i.object_id
305 inner join ' + @SourceCatalog + '.sys.index_columns idx on idx.index_id = i.index_id
306 inner join ' + @SourceCatalog + '.sys.index_columns col on col.object_id = i.object_id
307 inner join ' + @SourceCatalog + '.sys.columns col_ on col_.column_id = col.column_id and col_.object_id = i.object_id
308 where is_primary_key = 1 and t.name = ''''' + @SourceTable + ''''''''
309
310 set @query_text = 'select @RET_VAL=(name) from openquery (' + @SourceDB + ', ' + @query_text + ')'
311 print @query_text
312
313 exec sp_executesql @query_text, N'@RET_VAL NVARCHAR(MAX) OUTPUT', @ret_val = @PK_Name OUTPUT
314
315 SET @query_text = 'ALTER TABLE ' + @TargetCatalog + '.' + @TargetSchema + '.' + @TargetTable + ' ADD CONSTRAINT ' + char(10) +
316 @PK_Name + ' PRIMARY KEY NONCLUSTERED ( ' + @PK_Columns + ')'
317
318 --PRINT @query_text
319
320 BEGIN TRY
321 BEGIN TRANSACTION
322 exec sp_executesql @query_text
323 PRINT 'Constaint für ' + @TargetCatalog + '.' + @TargetSchema + '.' + @TargetTable + ' -> ' + @PK_Name + ' wurde erstellt'
324 COMMIT TRANSACTION
325 END TRY
326 BEGIN CATCH
327 ROLLBACK TRANSACTION
328 insert into CFG_LOG.dbo.LogTable
329 ([DateTime],[Funktion],[Text],[Status])
330 SELECT SYSDATETIME() as ZEIT, 'ErrorHandling' , ERROR_MESSAGE() + ' ErrorNumber' + CAST(ERROR_NUMBER()as VARCHAR(4)) as Error, 'Error' as a ;
331 END CATCH
332 END
333
334 set @sql = 'INSERT INTO ' + @TargetCatalog + '.' + @TargetSchema + '.' + @TargetTable + ' SELECT * FROM ' + @SourceDB + '.' + @SourceCatalog + '.' + @SourceSchema + '.' + @SourceTable + ' ' + @TableFilter
335 PRINT @sql
336 END
337 -- INSERT THE DATA INTO THE DESTINATION TABLE
338 --PRINT 'Executing the following statement' + CHAR(10)+ @sql
339 BEGIN TRY
340 BEGIN TRANSACTION
341 EXEC(@sql)
342 set @count = @@ROWCOUNT;
343 insert into CFG_LOG.dbo.LogTable
344 ([DateTime],[Funktion],[Text],[Status])
345 SELECT SYSDATETIME() as ZEIT, 'IMPORT' , CAST(@count as varchar) + ' row(s) affected into Table ' + @TargetCatalog + '.' + @TargetSchema + '.' + @TargetTable ,'Success' as a ;
346 PRINT ''
347 PRINT CAST(@count as varchar) + ' Zeilen wurden zur Tablle ' + @TargetCatalog + '.' + @TargetSchema + '.' + @TargetTable + ' hinzugefügt'
348 COMMIT TRANSACTION
349 END TRY
350 BEGIN CATCH
351 ROLLBACK TRANSACTION
352 insert into CFG_LOG.dbo.LogTable
353 ([DateTime],[Funktion],[Text],[Status])
354 SELECT SYSDATETIME() as ZEIT, 'ErrorHandling' , ERROR_MESSAGE() + ' ErrorNumber' + CAST(ERROR_NUMBER()as VARCHAR(4)) as Error, 'Error' as a ;
355 END CATCH
356 --END
357 NEXT_RESULTSET:
358 FETCH NEXT FROM cur_cfg_sta_import INTO @SourceQuery,@SourceDB, @SourceCatalog, @SourceSchema, @SourceTable, @TargetDB, @TargetCatalog, @TargetSchema, @TargetTable,@Tablefilter,@USERNAME,@PASSWORD
359END
360
361CLOSE cur_cfg_sta_import
362DEALLOCATE cur_cfg_sta_import
363
364insert into CFG_LOG.dbo.LogTable
365 ([DateTime],[Funktion],[Text],[Status])
366 SELECT SYSDATETIME() as ZEIT, 'IMPORT' , 'Import von Source nach STA wurde erfolgreich beendet' , 'INFO' as Status ;