0

I would like to use SMO to generate a set of insert statements to copy data between environments. I have this working, but for some reason, datetime fields are formated as hex values that are casted to datetime. This makes the scripts hard to read and impossible to update. Is there a way for me to change this to use a string representation of the date?

Code used to generate:

scripter = New Scripter(<server>)
scripter.Options.ScriptData = True
...
For Each s As String In scripter.EnumScript(list)
    writer.WriteLine(FormatScript(s))
Next

I would like to change this...

INSERT [dbo].[dmMessages] ([MessageId], [MessageCd], [MessageDesc], [Status], [EnteredBy], [EnteredDt])
VALUES (1, N'GenericMessages.FieldRequired', N'The {0} field is required.', 1, N'System', CAST(0x00009B4900000000 AS DateTime))

to

INSERT [dbo].[dmMessages] ([MessageId], [MessageCd], [MessageDesc], [Status], [EnteredBy], [EnteredDt])
VALUES (1, N'GenericMessages.FieldRequired', N'The {0} field is required.', 1, N'System', '2009-1-1 13:00:00:000')
Mike Schall
  • 5,829
  • 4
  • 41
  • 47
  • Did you find an answer to your question? In fact, I came across a case where SMO would script DateTime fields as Strings (as in `CAST(N'2014-07-22 15:24:32.873' AS DateTime)`), and I need it to be binary.. – Andreas Aug 11 '14 at 14:53
  • @Andreas: I do not have an answer. If you could supply the settings you have on your SMO object to get your output that would be great. I get binary output by default. What version of SMO are you using? My Microsoft.SqlServer.Smo.dll is version 10.0.2531.0. – Mike Schall Aug 11 '14 at 16:21
  • The thing is, I don't get the string output systematically. Only one out of 10 users got this string output (all using the same version of my tool); very strange indeed. For the record: my Smo.dll version is 11.0.2100.60. I use a scripter object with options `ScriptData=True`, `ScriptSchema=False`, `ToFileOnly=True`, `FileName="..."` and `Encoding=New Text.UTF8Encoding(True)`. – Andreas Aug 12 '14 at 07:06

1 Answers1

0

One round-about way to get the results you are looking for would be to use BCP to export data and bulk insert to import data as opposed to letting SMO script the data. Read here for more details.

doug_w
  • 1,330
  • 10
  • 10