Guest User

Untitled

a guest
Nov 2nd, 2017
27
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 14.86 KB | None | 0 0
  1.  
  2. /* automatic SOURCE to STAR Update */
  3.  
  4. DECLARE @count int;
  5. DECLARE @sql nvarchar(max);
  6. DECLARE @SourceDB as varchar(150);
  7. DECLARE @SourceCatalog as varchar(150);
  8. DECLARE @SourceSchema as varchar(150);
  9. DECLARE @SourceTable as varchar(150);
  10. DECLARE @SourceQuery as varchar(150);
  11. DECLARE @TargetDB as varchar(150);
  12. DECLARE @TargetCatalog as varchar(150);
  13. DECLARE @TargetSchema as varchar(150);
  14. DECLARE @TargetTable as varchar(150);
  15. DECLARE @TableFilter as varchar(500);
  16. DECLARE @name sysname ;
  17. DECLARE @PK_ColumnName as varchar(150);
  18. DECLARE @PK_CONSTRAINT_NAME as varchar(150);
  19. DECLARE @PK_ID varchar(max)
  20.  
  21.  
  22. DECLARE @USERNAME as varchar(max)
  23. DECLARE @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.  
  36. INSERT 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.  
  44. DECLARE cur_cfg_sta_import CURSOR READ_ONLY
  45. FOR
  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 ********
  69. Delticket : where datediff(mm, proddate, getdate()) between 0 and 2
  70. Deltickdet : where id in (select id from simma.delticket where datediff(mm, proddate, getdate()) between 0 and 2)
  71. *****************************
  72. */
  73.  
  74. WHILE (@@FETCH_STATUS <> -1)
  75. BEGIN
  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
  359. END
  360.  
  361. CLOSE cur_cfg_sta_import
  362. DEALLOCATE cur_cfg_sta_import
  363.  
  364. insert 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 ;
Add Comment
Please, Sign In to add comment