· 8 years ago · Dec 20, 2017, 11:44 AM
1
2/* automatic SOURCE to STAR Update */
3-- CFG_STA_IMPORT
4
5DECLARE @count int;
6DECLARE @sql nvarchar(max);
7DECLARE @SourceDB as varchar(150);
8DECLARE @SourceCatalog as varchar(150);
9DECLARE @SourceSchema as varchar(150);
10DECLARE @SourceTable as varchar(150);
11DECLARE @SourceQuery as varchar(150);
12DECLARE @TargetDB as varchar(150);
13DECLARE @TargetCatalog as varchar(150);
14DECLARE @TargetSchema as varchar(150);
15DECLARE @TargetTable as varchar(150);
16DECLARE @TableFilter as varchar(500);
17DECLARE @name sysname ;
18DECLARE @PK_ColumnName as varchar(150);
19DECLARE @PK_CONSTRAINT_NAME as varchar(150);
20DECLARE @PK_ID varchar(max)
21
22
23DECLARE @USERNAME as varchar(max)
24DECLARE @PASSWORD as varchar(max)
25
26
27/*************************************************************************/
28/** Logtabelle Copy and Delete **/
29/*************************************************************************/
30 EXEC CFG_LOG.dbo.pr_DelUpLog
31
32
33/*************************************************************************/
34/********************** Drop and Insert INTO **********************/
35/*************************************************************************/
36
37INSERT INTO CFG_LOG.dbo.LogTable
38 ([DateTime]
39 ,[Funktion]
40 ,[Text]
41 ,[Status])
42 SELECT SYSDATETIME() as ZEIT, 'IMPORT' , 'Der Import wurde gestartet' , 'INFO' as Status ;
43
44
45DECLARE cur_cfg_sta_import CURSOR READ_ONLY
46FOR
47 SELECT Query
48 ,SourceDB
49 ,SourceCatalog
50 ,SourceSchema
51 ,SourceTable
52 ,TargetDB
53 ,TargetCatalog
54 ,TargetSchema
55 ,TargetTable
56 ,ISNULL(Tablefilter,'')
57 ,LnkUser
58 ,LnkPw
59 FROM cfg_log.dbo.CFG_STA_IMPORT
60 WHERE upper([UPDATE]) = 'J'
61
62
63 OPEN cur_cfg_sta_import
64 FETCH NEXT
65 FROM cur_cfg_sta_import
66 INTO @SourceQuery,@SourceDB, @SourceCatalog, @SourceSchema, @SourceTable, @TargetDB, @TargetCatalog, @TargetSchema, @TargetTable, @Tablefilter, @USERNAME, @PASSWORD
67
68/*
69******** Tablefilter ********
70Delticket : where datediff(mm, proddate, getdate()) between 0 and 2
71Deltickdet : where id in (select id from simma.delticket where datediff(mm, proddate, getdate()) between 0 and 2)
72*****************************
73*/
74
75WHILE (@@FETCH_STATUS <> -1)
76BEGIN
77
78 --BEGIN TRY
79 -- BEGIN TRANSACTION
80 set @name = N'' + @TargetCatalog + '.' + @TargetSchema + '.' + @TargetTable
81 IF EXISTS (SELECT * FROM STA.sys.objects WHERE object_id = OBJECT_ID( @name ) AND type in (N'U') )
82 BEGIN
83 IF @TableFilter =''
84 BEGIN
85 set @sql = 'DROP TABLE ' + @TargetCatalog + '.' + @TargetSchema + '.' + @TargetTable
86 EXEC(@sql)
87 PRINT ''
88 PRINT 'Löschen von Tablle ' + @TargetCatalog + '.' + @TargetSchema + '.' + @TargetTable
89 END
90 ELSE
91 BEGIN
92 set @sql = 'DELETE FROM ' + @TargetCatalog + '.' + @TargetSchema + '.' + @TargetTable + ' ' + @TableFilter
93 EXEC(@sql)
94 PRINT ''
95 PRINT @sql
96 END
97 END
98
99 BEGIN
100 --IF @TableFilter = ''
101 -- BEGIN
102 -- set @sql = 'SELECT * INTO ' + @TargetCatalog + '.' + @TargetSchema + '.' + @TargetTable + ' FROM ' + @SourceDB + '.' + @SourceCatalog + '.' + @SourceSchema + '.' + @SourceTable
103 -- END
104 -- ELSE
105 -- BEGIN
106 -- Entfernen von [] des Linked-Server-Namens
107 declare @linkedserverName nvarchar(150) = REPLACE(REPLACE(@SourceDB, '[',''), ']','')
108
109 -- Erstellen von Linked Server, wenn nicht vorhanden
110 if not exists(select server_id from sys.servers where name = @linkedserverName)
111 begin
112 exec master.dbo.sp_addlinkedserver @server=@linkedserverName, @srvproduct=N'SQL Server'
113 EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=@linkedserverName,@useself=N'False',@locallogin=NULL,@rmtuser=@USERNAME,@rmtpassword=@PASSWORD
114 end
115
116 -- check if non sql server based rdbms engine
117 declare @query_text nvarchar(max)
118 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 + ''''
119
120 declare @ret_val int
121 set @ret_val = 0
122
123 BEGIN TRY
124 begin transaction
125 exec sp_executesql @query_text
126 set @ret_val = @@ROWCOUNT;
127 commit transaction
128 END TRY
129 BEGIN CATCH
130 rollback transaction
131 PRINT 'ERROR: ' + @SourceDB + ', ' + @SourceCatalog + '; ' + ERROR_MESSAGE()
132
133 --PRINT @query_text
134 IF ERROR_NUMBER() = 7411
135 BEGIN
136 GOTO NEXT_RESULTSET
137 END
138 ELSE
139 BEGIN
140 insert into CFG_LOG.dbo.LogTable
141 ([DateTime],[Funktion],[Text],[Status])
142 SELECT SYSDATETIME() as ZEIT, 'ErrorHandling' , ERROR_MESSAGE() + ' ErrorNumber' + CAST(ERROR_NUMBER()as VARCHAR(4)) as Error, 'Error' as a ;
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 DDL to create Table ' + @TargetCatalog + '.' + @TargetSchema + '.' + @TargetTable
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
268 '''', '''' + c.Name
269 FROM ' + @SourceCatalog + '.sys.indexes i
270 INNER JOIN ' + @SourceCatalog + '.sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
271 INNER JOIN ' + @SourceCatalog + '.sys.columns c ON ic.object_id = c.object_id and ic.column_id = c.column_id
272 INNER JOIN ' + @SourceCatalog + '.sys.objects o ON i.object_id = o.object_id
273 INNER JOIN ' + @SourceCatalog + '.sys.schemas sc ON o.schema_id = sc.schema_id
274 WHERE i.is_primary_key = 1
275 AND o.name = ''''' + @SourceTable +
276 '''''
277 ORDER BY o.Name, i.Name, ic.key_ordinal
278 FOR XML PATH('''''''')), 1, 2, '''''''') as stuff'''
279
280 DECLARE @PK_Columns nvarchar(max);
281 SET @query_text = 'select @PK_Columns=(ISNULL(stuff,'''')) from openquery(' + @SourceDB + ', ' + @query_text + ')'
282 --print @query_text
283
284 exec sp_executesql @query_text, N'@PK_Columns NVARCHAR(MAX) OUTPUT', @PK_Columns = @PK_Columns OUTPUT
285
286 -- Primary Keys wurden gefunden
287 IF @PK_Columns <> ''
288 BEGIN
289 Print 'Es wurden Primary Keys gefunden'
290
291 DECLARE @PK_Name nvarchar(max)
292 SET @PK_Name = ''
293
294 set @query_text = '''select distinct i.name from ' + @SourceCatalog + '.sys.indexes i
295 inner join ' + @SourceCatalog + '.sys.objects t on t.object_id = i.object_id
296 inner join ' + @SourceCatalog + '.sys.index_columns idx on idx.index_id = i.index_id
297 inner join ' + @SourceCatalog + '.sys.index_columns col on col.object_id = i.object_id
298 inner join ' + @SourceCatalog + '.sys.columns col_ on col_.column_id = col.column_id and col_.object_id = i.object_id
299 where is_primary_key = 1 and t.name = ''''' + @SourceTable + ''''''''
300
301 set @query_text = 'select @RET_VAL=(name) from openquery (' + @SourceDB + ', ' + @query_text + ')'
302 --print @query_text
303
304 exec sp_executesql @query_text, N'@RET_VAL NVARCHAR(MAX) OUTPUT', @ret_val = @PK_Name OUTPUT
305
306 SET @query_text = 'ALTER TABLE ' + @TargetCatalog + '.' + @TargetSchema + '.' + @TargetTable + ' ADD CONSTRAINT ' + char(10) +
307 @PK_Name + ' PRIMARY KEY NONCLUSTERED ( ' + @PK_Columns + ')'
308
309 --PRINT @query_text
310
311 BEGIN TRY
312 BEGIN TRANSACTION
313 exec sp_executesql @query_text
314 PRINT 'Constaint für ' + @TargetCatalog + '.' + @TargetSchema + '.' + @TargetTable + ' -> ' + @PK_Name + ' wurde erstellt'
315 COMMIT TRANSACTION
316 END TRY
317 BEGIN CATCH
318 ROLLBACK TRANSACTION
319 PRINT 'FEHLER beim Erstellen der Constaint für ' + @TargetCatalog + '.' + @TargetSchema + '.' + @TargetTable + ' -> ' + @PK_Name
320 insert into CFG_LOG.dbo.LogTable
321 ([DateTime],[Funktion],[Text],[Status])
322 SELECT SYSDATETIME() as ZEIT, 'ErrorHandling' , ERROR_MESSAGE() + ' ErrorNumber' + CAST(ERROR_NUMBER()as VARCHAR(4)) as Error, 'Error' as a ;
323 END CATCH
324 END
325
326 set @sql = 'INSERT INTO ' + @TargetCatalog + '.' + @TargetSchema + '.' + @TargetTable + ' SELECT * FROM ' + @SourceDB + '.' + @SourceCatalog + '.' + @SourceSchema + '.' + @SourceTable + ' ' + @TableFilter
327 PRINT @sql
328 END
329 -- INSERT THE DATA INTO THE DESTINATION TABLE
330 --PRINT 'Executing the following statement' + CHAR(10)+ @sql
331 BEGIN TRY
332 BEGIN TRANSACTION
333 EXEC(@sql)
334 set @count = @@ROWCOUNT;
335 insert into CFG_LOG.dbo.LogTable
336 ([DateTime],[Funktion],[Text],[Status])
337 SELECT SYSDATETIME() as ZEIT, 'IMPORT' , CAST(@count as varchar) + ' row(s) affected into Table ' + @TargetCatalog + '.' + @TargetSchema + '.' + @TargetTable ,'Success' as a ;
338 PRINT ''
339 PRINT CAST(@count as varchar) + ' Zeilen wurden zur Tablle ' + @TargetCatalog + '.' + @TargetSchema + '.' + @TargetTable + ' hinzugefügt'
340 COMMIT TRANSACTION
341 END TRY
342 BEGIN CATCH
343 ROLLBACK TRANSACTION
344 insert into CFG_LOG.dbo.LogTable
345 ([DateTime],[Funktion],[Text],[Status])
346 SELECT SYSDATETIME() as ZEIT, 'ErrorHandling' , ERROR_MESSAGE() + ' ErrorNumber' + CAST(ERROR_NUMBER()as VARCHAR(4)) as Error, 'Error' as a ;
347 END CATCH
348 --END
349 NEXT_RESULTSET:
350 FETCH NEXT FROM cur_cfg_sta_import INTO @SourceQuery,@SourceDB, @SourceCatalog, @SourceSchema, @SourceTable, @TargetDB, @TargetCatalog, @TargetSchema, @TargetTable,@Tablefilter,@USERNAME,@PASSWORD
351END
352
353CLOSE cur_cfg_sta_import
354DEALLOCATE cur_cfg_sta_import
355
356insert into CFG_LOG.dbo.LogTable
357 ([DateTime],[Funktion],[Text],[Status])
358 SELECT SYSDATETIME() as ZEIT, 'IMPORT' , 'Import von Source nach STA wurde erfolgreich beendet' , 'INFO' as Status ;