SQL Script to export table data in xml files, or export schema data to xsd files This use OLE Automation Stored Procedures (Transact-SQL), and maybe, you need to change the global configuration settings of SQL Server. It can be an issue in production environment, and this case.
The SQL Script have three stored procedures : #spWriteStringToFile #spExportTableToXmlFile #spExportDatabaseToXmlFile
Check GitHub for last version : https://github.com/sozezzo/SqlServer/
You run #spExportDatabaseToXmlFile, and you can filter by the table name or the scheme name. The filter can use wildcard character : %
Limitation : Some column type cannot be exported. ( Ex. Type := Geography ) You can change the column type, or copy the data to a new table with transformed columns.
Suggestions Backup and restore the database over another SQL Server, it is preferable to use develop SQL Server. Use shared folder with low security to storage xml files.
Fix Error : SQL Server blocked access to procedure ‘sys.sp_OACreate’ of component ‘Ole Automation Procedures’
1
2GO
3exec sp_configure 'show advanced options', 1
4GO
5RECONFIGURE;
6GO
7exec sp_configure 'Ole Automation Procedures', 1
8GO
9RECONFIGURE;
10GO
11exec sp_configure 'show advanced options', 1
12GO
13RECONFIGURE;
14GO
Sql Script - Check last line to change the configuration
1
2GO
3/*
4* Name : #spFileDetails
5* Description : Get info file
6*
7* Syntax :
8* EXEC #spFileDetails 'C:MyFile.txt';
9*
10*/
11CREATE OR ALTER PROCEDURE #spFileDetails
12( @Filename VARCHAR(100) /* spFileDetails 'c:autoexec.bat' */
13)
14AS
15BEGIN
16
17 -- https://www.red-gate.com/simple-talk/sql/t-sql-programming/reading-and-writing-files-in-sql-server-using-t-sql/
18
19 SET NOCOUNT ON;
20
21 DECLARE @hr INT , --the HRESULT returned from
22 @objFileSystem INT , --the FileSystem object
23 @objFile INT , --the File object
24 @ErrorObject INT , --the error object
25 @ErrorMessage VARCHAR(255) , --the potential error message
26 @Path VARCHAR(100) , --
27 @ShortPath VARCHAR(100) , @Type VARCHAR(100) , @DateCreated datetime , @DateLastAccessed datetime , @DateLastModified datetime , @Attributes INT , @size INT
28
29 SET nocount ON
30
31 SELECT @hr=0
32 , @errorMessage='opening the file system object '
33 EXEC @hr = sp_OACreate 'Scripting.FileSystemObject'
34 , @objFileSystem OUT
35 IF @hr=0
36 SELECT @errorMessage='accessing the file ''' COLLATE DATABASE_DEFAULT
37 +@Filename+''''
38 , @ErrorObject=@objFileSystem
39 IF @hr=0
40 EXEC @hr = sp_OAMethod @objFileSystem
41 , 'GetFile'
42 , @objFile out
43 , @Filename
44 IF @hr=0
45 SELECT @errorMessage='getting the attributes of ''' COLLATE DATABASE_DEFAULT
46 +@Filename+''''
47 , @ErrorObject=@objFile
48 IF @hr=0
49 EXEC @hr = sp_OAGetProperty @objFile
50 , 'Path'
51 , @path OUT
52 IF @hr=0
53 EXEC @hr = sp_OAGetProperty @objFile
54 , 'ShortPath'
55 , @ShortPath OUT
56 IF @hr=0
57 EXEC @hr = sp_OAGetProperty @objFile
58 , 'Type'
59 , @Type OUT
60 IF @hr=0
61 EXEC @hr = sp_OAGetProperty @objFile
62 , 'DateCreated'
63 , @DateCreated OUT
64 IF @hr=0
65 EXEC @hr = sp_OAGetProperty @objFile
66 , 'DateLastAccessed'
67 , @DateLastAccessed OUT
68 IF @hr=0
69 EXEC @hr = sp_OAGetProperty @objFile
70 , 'DateLastModified'
71 , @DateLastModified OUT
72 IF @hr=0
73 EXEC @hr = sp_OAGetProperty @objFile
74 , 'Attributes'
75 , @Attributes OUT
76 IF @hr=0
77 EXEC @hr = sp_OAGetProperty @objFile
78 , 'size'
79 , @size OUT
80
81 IF @hr<>0
82 BEGIN
83 DECLARE @Source VARCHAR(255) , @Description VARCHAR(255) , @Helpfile VARCHAR(255) , @HelpID INT
84
85 EXECUTE sp_OAGetErrorInfo @errorObject
86 , @source OUTPUT
87 , @Description OUTPUT
88 , @Helpfile OUTPUT
89 , @HelpID OUTPUT
90
91 SELECT @ErrorMessage='Error whilst ' COLLATE DATABASE_DEFAULT
92 +@Errormessage+', '
93 +@Description
94 RAISERROR (@ErrorMessage,16,1)
95 END
96 EXEC sp_OADestroy @objFileSystem
97 EXEC sp_OADestroy @objFile
98 SELECT [Path]= @Path
99 , [ShortPath]= @ShortPath
100 , [Type]= @Type
101 , [DateCreated]= @DateCreated
102 , [DateLastAccessed]= @DateLastAccessed
103 , [DateLastModified]= @DateLastModified
104 , [Attributes]= @Attributes
105 , [size]= @size
106 RETURN @hr
107END
108GO
109
110GO
111/*
112* Name : #spWriteStringToFile
113* Description : Write in text file
114*
115* Syntax :
116* EXEC #spWriteStringToFile @String = 'Hello Word!', @Path = 'C:Temp', @Filename = 'myfile.txt';
117*
118*/
119CREATE OR ALTER PROCEDURE #spWriteStringToFile
120( @String VARCHAR(MAX)
121, @Path VARCHAR(512)
122, @Filename VARCHAR(512)
123)
124AS
125BEGIN
126
127 SET NOCOUNT ON;
128
129 DECLARE @objFileSystem INT , @objTextStream INT , @objErrorObject INT , @strErrorMessage VARCHAR(1000) , @Command VARCHAR(1000) , @hr INT , @fileAndPath VARCHAR(512);
130 SET NOCOUNT ON;
131 SELECT @strErrorMessage = 'opening the File System Object';
132 EXECUTE @hr = sp_OACreate 'Scripting.FileSystemObject'
133 , @objFileSystem OUT;
134
135 SELECT @FileAndPath = @path + '' COLLATE DATABASE_DEFAULT + @filename;
136
137 IF @HR = 0
138 SELECT @objErrorObject = @objFileSystem
139 , @strErrorMessage = 'Creating file "' COLLATE DATABASE_DEFAULT + @FileAndPath + '"' COLLATE DATABASE_DEFAULT ;
140 IF @HR = 0
141 EXECUTE @hr = sp_OAMethod @objFileSystem
142 , 'CreateTextFile'
143 , @objTextStream OUT
144 , @FileAndPath
145 , 2
146 , True;
147 IF @HR = 0
148 SELECT @objErrorObject = @objTextStream
149 , @strErrorMessage = 'writing to the file "' COLLATE DATABASE_DEFAULT + @FileAndPath + '"' COLLATE DATABASE_DEFAULT ;
150 IF @HR = 0
151 EXECUTE @hr = sp_OAMethod @objTextStream
152 , 'Write'
153 , NULL
154 , @String;
155 IF @HR = 0
156 SELECT @objErrorObject = @objTextStream
157 , @strErrorMessage = 'closing the file "' COLLATE DATABASE_DEFAULT + @FileAndPath + '"' COLLATE DATABASE_DEFAULT ;
158 IF @HR = 0
159 EXECUTE @hr = sp_OAMethod @objTextStream
160 , 'Close';
161 IF @hr <> 0
162 BEGIN
163 DECLARE @Source VARCHAR(255) , @Description VARCHAR(255) , @Helpfile VARCHAR(255) , @HelpID INT;
164 EXECUTE sp_OAGetErrorInfo @objErrorObject
165 , @source OUTPUT
166 , @Description OUTPUT
167 , @Helpfile OUTPUT
168 , @HelpID OUTPUT;
169 SELECT @strErrorMessage = 'Error whilst ' + COALESCE(@strErrorMessage, 'doing something' COLLATE DATABASE_DEFAULT ) + ', ' COLLATE DATABASE_DEFAULT + COALESCE(@Description, '' COLLATE DATABASE_DEFAULT );
170 RAISERROR(@strErrorMessage, 16, 1);
171 END;
172 EXECUTE sp_OADestroy @objTextStream;
173 EXECUTE sp_OADestroy @objFileSystem;
174END;
175
176GO
177
178GO
179/*
180* Name : #spExportTableToXmlFile
181* Description : Export table in xml file
182*
183* Use stored procedure : #spWriteStringToFile
184*
185* Syntax :
186* EXEC #spExportTableToXmlFile @SchemaName = 'dbo', @TableName = 'User', @Path = 'C:Temp', @Filename = 'user.xml';
187*
188*/
189CREATE OR ALTER PROCEDURE #spExportTableToXmlFile
190( @SchemaName VARCHAR(255)
191, @TableName VARCHAR(255)
192, @Path VARCHAR(255)
193, @FileName VARCHAR(512) = NULL
194, @SafeMode INT = 0
195, @CreateXSDFile INT = 0
196, @Debug INT = 0
197)
198AS
199BEGIN
200
201 SET NOCOUNT ON;
202
203 IF (@Filename IS NULL)
204 BEGIN
205 SET @Filename = @SchemaName+'.' COLLATE DATABASE_DEFAULT +@TableName+'.xml' COLLATE DATABASE_DEFAULT;
206 IF (@CreateXSDFile = 1) SET @Filename = @SchemaName+'.' COLLATE DATABASE_DEFAULT +@TableName+'.xsd' COLLATE DATABASE_DEFAULT;
207 END
208 -- TODO : Clean up invalided chars
209
210 DECLARE @Template NVARCHAR(MAX) = '
211
212PRINT ''-- Export Table to Xml File : [@(SchemaName)].[@(TableName)]''
213PRINT ''-- '' + CONVERT(VARCHAR(50), GETDATE(), 121);
214DECLARE @xml varchar(max);
215SET @xml = (select * from [@(SchemaName)].[@(TableName)] FOR XML AUTO, ROOT (''@(SchemaName).@(TableName)''))
216EXEC #spWriteStringToFile @String = @xml, @Path = ''@(Path)'', @Filename = ''@(Filename)'';
217' COLLATE DATABASE_DEFAULT
218
219 IF (@CreateXSDFile = 1)
220 BEGIN
221 SET @Template = '
222
223PRINT ''-- Export Table to Xml File : [@(SchemaName)].[@(TableName)]''
224PRINT ''-- '' + CONVERT(VARCHAR(50), GETDATE(), 121);
225DECLARE @xml varchar(max);
226SET @xml = (select TOP 0 * from [@(SchemaName)].[@(TableName)] FOR XML AUTO, ELEMENTS, XMLSCHEMA )
227EXEC #spWriteStringToFile @String = @xml, @Path = ''@(Path)'', @Filename = ''@(Filename)'';
228' COLLATE DATABASE_DEFAULT
229 END
230
231 SET @Template = REPLACE(@Template,'@(SchemaName)' COLLATE DATABASE_DEFAULT , @SchemaName);
232 SET @Template = REPLACE(@Template,'@(TableName)' COLLATE DATABASE_DEFAULT , @TableName);
233 SET @Template = REPLACE(@Template,'@(Path)' COLLATE DATABASE_DEFAULT , @Path);
234 SET @Template = REPLACE(@Template,'@(Filename)' COLLATE DATABASE_DEFAULT , @Filename);
235
236 IF (@Debug = 1) PRINT @Template
237 IF (@SafeMode = 1)
238 BEGIN
239 BEGIN TRY
240 EXEC (@Template)
241 END TRY
242 BEGIN CATCH
243 PRINT '-----------------------------------' COLLATE DATABASE_DEFAULT
244 PRINT '-- WARNING - Safe Mode' COLLATE DATABASE_DEFAULT
245 PRINT '-- Table cannot be exported : ' COLLATE DATABASE_DEFAULT + @SchemaName +'.'+ @TableName
246 PRINT '-----------------------------------' COLLATE DATABASE_DEFAULT
247 END CATCH
248 END
249 ELSE
250 BEGIN
251 EXEC (@Template);
252 END
253
254END
255GO
256
257GO
258/*
259* Name : #spExportDatabaseToXmlFile
260* Description : Export database tables in xml files
261*
262* Use stored procedure : #spExportTableToXmlFile, #spWriteStringToFile
263*
264* Syntax :
265* EXEC #spExportTableToXmlFile @Path = 'C:Temp';
266* EXEC #spExportTableToXmlFile @Path = 'C:Temp', @TableFilter = 'Test%';
267* EXEC #spExportTableToXmlFile @Path = 'C:Temp', @SchemaFilter = '%dbo%', @TableFilter = 'Test%';
268*
269*/
270CREATE OR ALTER PROCEDURE #spExportDatabaseToXmlFile
271( @Path VARCHAR(1024)
272, @SchemaFilter VARCHAR(512) = NULL
273, @TableFilter VARCHAR(512) = NULL
274, @CreateXSDFile INT = 0
275, @SafeMode INT = 0
276, @Debug INT = 0
277)
278AS
279BEGIN
280
281 SET NOCOUNT ON;
282
283 DECLARE @SchemaName VARCHAR(512);
284 DECLARE @TableName VARCHAR(512);
285 SELECT s.name AS SchemaName
286 , t.name AS TableName
287 INTO #TableToExport
288 FROM sys.tables t
289 INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
290 WHERE (
291 @SchemaFilter IS NULL
292 OR s.name LIKE @SchemaFilter)
293 AND (
294 @TableFilter IS NULL
295 OR t.name LIKE @TableFilter)
296
297 WHILE (EXISTS (SELECT * FROM #TableToExport))
298 BEGIN
299 SELECT TOP 1 @SchemaName = SchemaName, @TableName = TableName FROM #TableToExport;
300 DELETE FROM #TableToExport WHERE @SchemaName = SchemaName AND @TableName = TableName;
301 EXEC #spExportTableToXmlFile @SchemaName = @SchemaName
302 , @TableName = @TableName
303 , @Path = @Path
304 , @CreateXSDFile = @CreateXSDFile
305 , @SafeMode = @SafeMode
306 , @Debug = @Debug;
307 END
308END
309GO
310
311-- exec #spExportDatabaseToXmlFile @Path = 'C:TEMP'; -- Export data
312-- exec #spExportDatabaseToXmlFile @Path = 'C:TEMP', @SafeMode = 1; -- Export data & Safe mode
313-- exec #spExportDatabaseToXmlFile @Path = 'C:TEMP', @CreateXSDFile = 1, @Debug = 1; -- Export schema
Source : (alternative solution with a lot steps and things to do) https://www.easeus.com/sql-database-recovery/sql-to-xml.html
Comments