Closed sheldonhull closed 8 years ago
eloyesp
commented
8 years ago Could you post the json you are trying to import, the only important part is the date ("published_at") of the post that is failing (the next one to "on-how-to-googlify-your-sql-statements-for-future-searching").
Thanks for posting the issue.
sheldonhull
commented
8 years ago Sorry for the delay, still newer to github. Here is the post before and the post after that one.
{
"id": 822,
"uuid": "3915ffcf-801b-4cbe-ae06-63138a5b9476",
"title": "Get synonym definitions for all databases in server",
"slug": "get-synonym-definitions-for-all-databases-in-server-4",
"markdown": "\n<span data-rel=\"content\">If you want to audit your enviroment to look at all your synonyms and see where they are pointing, you can use exec sys.sp_MSforeachdb to loop through databases, and even filter. It will save some coding. However, [my research indicates it is probably a bad practice to rely on this undocumented function as it may have issues not forseen and fully tested](http://shaunjstuart.com/archive/2012/10/its-time-to-retire-sp_msforeachdb/). Additionally, support may drop for it in the future. I recreated what I needed with a cursor to obtain all the synonym definitions into a temp table and display results.\n\n/******************************************************* create temp table for holding synonym definitions & list of DB *******************************************************/ if object_id( 'tempdb..#dblist' ) is not null drop table #dblist; select * into #dblist from sys.databases where Name not in ('master', 'tempdb', 'model', 'msdb') and State_desc = 'ONLINE' and Is_In_Standby = 0 if object_id( 'tempdb..#temp' ) is not null drop table #temp; create table #temp ( db_name sysname ,object_id int ,name sysname ,base_object_name sysname ,server_name_hardcoded as case when base_object_name like '%ThisDatabaseIsOkToHardCode%' then 0 when len( base_object_name ) - len( replace( base_object_name, '.', '' ) ) > 2 then 1 else 0 end ) go declare @DbName sysname declare @XSQL varchar(max) declare @CompleteSQL varchar(max) declare db_cursor cursor fast_forward read_only local for select Name from #dblist open db_cursor fetch next from db_cursor into @DbName; while @@fetch_status = 0 begin set @XSQL = ' insert into #temp ( db_name ,object_id ,name ,base_object_name ) select db_name() ,s.object_id ,s.name ,s.base_object_name from sys.synonyms s ' set @CompleteSQL = 'USE ' + @dbName + '; EXEC sp_executesql N''' + @XSQL + ''''; exec (@CompleteSQL) fetch next from db_cursor into @DbName; end close db_cursor deallocate db_cursor go select * from #temp t\n\n \n\n</span>\n",
"html": "<p><span data-rel=\"content\">If you want to audit your enviroment to look at all your synonyms and see where they are pointing, you can use exec sys.sp_MSforeachdb to loop through databases, and even filter. It will save some coding. However, <a href=\"http://shaunjstuart.com/archive/2012/10/its-time-to-retire-sp_msforeachdb/\">my research indicates it is probably a bad practice to rely on this undocumented function as it may have issues not forseen and fully tested</a>. Additionally, support may drop for it in the future. I recreated what I needed with a cursor to obtain all the synonym definitions into a temp table and display results.</p>\n\n<p>/<strong><em>*</em><em>*</em><em>*</em><em>*</em><em>*</em><em>*</em><em>*</em><em>*</em><em>*</em><em>*</em><em>*</em><em>*</em><em>*</em><em>*</em><em>*</em><em>*</em><em>*</em></strong> create temp table for holding synonym definitions & list of DB <strong><em>*</em><em>*</em><em>*</em><em>*</em><em>*</em><em>*</em><em>*</em><em>*</em><em>*</em><em>*</em><em>*</em><em>*</em><em>*</em><em>*</em><em>*</em><em>*</em><em>*</em></strong>/ if object<em>id( 'tempdb..#dblist' ) is not null drop table #dblist; select * into #dblist from sys.databases where Name not in ('master', 'tempdb', 'model', 'msdb') and State</em>desc = 'ONLINE' and Is<em>In</em>Standby = 0 if object<em>id( 'tempdb..#temp' ) is not null drop table #temp; create table #temp ( db</em>name sysname ,object<em>id int ,name sysname ,base</em>object<em>name sysname ,server</em>name<em>hardcoded as case when base</em>object<em>name like '%ThisDatabaseIsOkToHardCode%' then 0 when len( base</em>object<em>name ) - len( replace( base</em>object<em>name, '.', '' ) ) > 2 then 1 else 0 end ) go declare @DbName sysname declare @XSQL varchar(max) declare @CompleteSQL varchar(max) declare db</em>cursor cursor fast<em>forward read</em>only local for select Name from #dblist open db<em>cursor fetch next from db</em>cursor into @DbName; while @@fetch<em>status = 0 begin set @XSQL = ' insert into #temp ( db</em>name ,object<em>id ,name ,base</em>object<em>name ) select db</em>name() ,s.object<em>id ,s.name ,s.base</em>object<em>name from sys.synonyms s ' set @CompleteSQL = 'USE ' + @dbName + '; EXEC sp</em>executesql N''' + @XSQL + ''''; exec (@CompleteSQL) fetch next from db<em>cursor into @DbName; end close db</em>cursor deallocate db_cursor go select * from #temp t</p>\n\n<p></span></p>",
"image": "http://res.cloudinary.com/r2d2imagehosting/image/upload/f_auto,fl_lossy,q_80,c_limit,h_1000,w_700/SqlBarbarian_helmet_izfaig.png",
"featured": 0,
"page": 0,
"status": "published",
"language": "en_US",
"meta_title": null,
"meta_description": null,
"author_id": 1,
"created_at": "2013-05-21T13:41:20.000Z",
"created_by": 1,
"updated_at": "2016-01-28T09:19:14.000Z",
"updated_by": 1,
"published_at": "2013-05-21T13:41:20.000Z",
"published_by": 1,
"visibility": "public",
"mobiledoc": null
},
{
"id": 823,
"uuid": "a713140b-d1ca-43d0-be1d-4aaa20fa3479",
"title": "On how to Googlify your SQL statements for future searching",
"slug": "on-how-to-googlify-your-sql-statements-for-future-searching-4",
"markdown": "\nFor sake of future generations, let’s begin to reduce typing and reuse code we’ve built. I think we can all agree that TSQL statements are often repeated. \n\nIdeally, snippets should be created to reduce repeated typing and let us focus on logic and content. However, some statements may not really be “snippet worthy”, and just be quick adhoc queries. \n\nIn the past, the solution for saving queries for reuse or reference in the future would be to just save in the projects folder manually. However, it is difficult to always make sure the file is saved, review previous version that may be want had overrode, or even review what statements you actually executed. [SSMSToolsPack](http://www.ssmstoolspack.com/) has historically offered a great logging option. However, as an individual it was hard to justify the cost out of my own pocket. [SSMSBoost](http://www.ssmsboost.com/) has provided a great solution! Note that this was recently added (April), and is a “rough draft” , with minimal interface options, yet provides an amazing solution that I’ve found to offer a great solution.\n\nIn addition to the other great features that SSMSBoost offers (which I’ll write about in the future), SSMSBoost now offers 3 unique solutions to saving work. \n\n1. **Executed Statement** Logging This feature saves all your executed statements (ie, when you hit execute) as a .sql file for future reference. As of today, there is no GUI for managing this. Never fear, I have a great solution for you. \n\n2. **Editor History Logging** This feature saves the current contents of your open query windows at predefined intervals, by default set to 60 seconds. According to their documentation, if no changes have been made to file, it will not save a new version. It will only add a new version once changes are detected to a file. \n\n\n3.**Tab History Logging** If you crash SSMS, close SSMS without saving tabs, or have some unsavory Windows behavior that requires SSMS to be restarted, don’t fear… your tabs are saved. When restarting you can select restore tabs and begin work again. I’ve found this feature to be a lifesaver! (http://res.cloudinary.com/r2d2imagehosting/image/upload/f_auto,fl_lossy,q_80,c_limit,h_1000,w_700/ssmsBoostRecentTabs_bndhth.jpg) \n\n\n##Searching Your Executed and Editor History\n\nInstructions I recommend for setup and searching your entire sql history nearly instantly. \n\n1. Install [SSMSBoost](http://www.ssmsboost.com/) (free community edition if you can’t support with professional version) \n2. Install [DocFetcher ](http://docfetcher.sourceforge.net/en/index.html)(open source full text search tool. Best I found for searching and previewing sql files without the complexity of using GREP or other similar tools) \n3. Download and run [Preview Handler](http://www.winhelponline.com/utils/previewconfig.zip) from [WinHelp](http://www.winhelponline.com/) 4. Run Preview Handler > Find .SQL > Preview as plain text \n5. Run SSMS > Open Settings in SSMSBoost \n6. Configure settings as you see fit. I personally move my Editor History and Executed statement’s location to my SSMS Folder, so that I can use something like Create Synchronicity to backup all my work daily. (http://res.cloudinary.com/r2d2imagehosting/image/upload/f_auto,fl_lossy,q_80,c_limit,h_1000,w_700/SSMSBoost_settings_kzsbzr.jpg) 7. Restart SSMS for settings to take effect. 8. Start DocFetcher, go to settings in the top right hand corner. (http://res.cloudinary.com/r2d2imagehosting/image/upload/f_auto,fl_lossy,q_80,c_limit,h_1000,w_700/DocFetcher_1_startup_irjelx.jpg) Basic Settings I choose (If you aren’t using [Bitstream ](http://ftp.gnome.org/pub/GNOME/sources/ttf-bitstream-vera/1.10/)font… you are missing out) (http://res.cloudinary.com/r2d2imagehosting/image/upload/f_auto,fl_lossy,q_80,c_limit,h_1000,w_700/DocFetcher_2_basicSettings_unugtr.jpg) \n\n####Docfetcher Advance settings tweaks Change:\n\n```\nCurvyTabs = true HtmlExtensions = html;htm;xhtml;shtml;shtm;php;asp;jsp;sql InitialSorting = -8\n```\n\n- Why? Curvy tabs… because curves are nice \n- HTML Extensions, obvious \n- Initial Sorting = -8 means that instead of sorting by “match %” which I didn’t find helpful for me, to sort by modified date in desc order. This means I’ll find the most most recent match for the text I’m searching for at the top of my list. \n\n9. Setup your custom indexes. I setup separate indexes for executed statements and editor history so I could filter down what I cared about and eliminate near duplicate matches for the most part. Right click in blank space to create index. I setup as follows: (http://res.cloudinary.com/r2d2imagehosting/image/upload/f_auto,fl_lossy,q_80,c_limit,h_1000,w_700/DocFetcher_createIndex1_t0qhl5.jpg) (http://res.cloudinary.com/r2d2imagehosting/image/upload/f_auto,fl_lossy,q_80,c_limit,h_1000,w_700/DocFetcher_createIndex2_i121sx.jpg) 10. Now the DocFetcher daemon will run in the background, if you copied my settings, and update your indexes. Searching requires no complex regex, and can be done easily with statements. I’d caution on putting exact phrases in quotes, as it does detect wildcards.\n\n\n(http://res.cloudinary.com/r2d2imagehosting/image/upload/f_auto,fl_lossy,q_80,c_limit,h_1000,w_700/DocFetcher_previewSearch_m2g07n.jpg)\nbad search, due to wild card count(*) and test_table -- should better handle your search (implicit AND due to basic settings we chose) \"count(*)\" test_table or \"count(*)\" and test_table\n",
"html": "<p>For sake of future generations, let’s begin to reduce typing and reuse code we’ve built. I think we can all agree that TSQL statements are often repeated. </p>\n\n<p>Ideally, snippets should be created to reduce repeated typing and let us focus on logic and content. However, some statements may not really be “snippet worthy”, and just be quick adhoc queries. </p>\n\n<p>In the past, the solution for saving queries for reuse or reference in the future would be to just save in the projects folder manually. However, it is difficult to always make sure the file is saved, review previous version that may be want had overrode, or even review what statements you actually executed. <a href=\"http://www.ssmstoolspack.com/\">SSMSToolsPack</a> has historically offered a great logging option. However, as an individual it was hard to justify the cost out of my own pocket. <a href=\"http://www.ssmsboost.com/\">SSMSBoost</a> has provided a great solution! Note that this was recently added (April), and is a “rough draft” , with minimal interface options, yet provides an amazing solution that I’ve found to offer a great solution.</p>\n\n<p>In addition to the other great features that SSMSBoost offers (which I’ll write about in the future), SSMSBoost now offers 3 unique solutions to saving work. </p>\n\n<ol>\n<li><p><strong>Executed Statement</strong> Logging This feature saves all your executed statements (ie, when you hit execute) as a .sql file for future reference. As of today, there is no GUI for managing this. Never fear, I have a great solution for you. </p></li>\n<li><p><strong>Editor History Logging</strong> This feature saves the current contents of your open query windows at predefined intervals, by default set to 60 seconds. According to their documentation, if no changes have been made to file, it will not save a new version. It will only add a new version once changes are detected to a file. <br />\n<img src=\"http://res.cloudinary.com/r2d2imagehosting/image/upload/f_auto,fl_lossy,q_80,c_limit,h_1000,w_700/ssmsBoost_findInHistory_wl9xgr.jpg\" alt=\"ssmsBoost_findInHistory\" /></p></li>\n</ol>\n\n<p>3.<strong>Tab History Logging</strong> If you crash SSMS, close SSMS without saving tabs, or have some unsavory Windows behavior that requires SSMS to be restarted, don’t fear… your tabs are saved. When restarting you can select restore tabs and begin work again. I’ve found this feature to be a lifesaver! <img src=\"http://res.cloudinary.com/r2d2imagehosting/image/upload/f_auto,fl_lossy,q_80,c_limit,h_1000,w_700/ssmsBoostRecentTabs_bndhth.jpg\" alt=\"ssmsBoostRecentTabs\" title=\"\" />(<a href=\"http://res.cloudinary.com/r2d2imagehosting/image/upload/f\">http://res.cloudinary.com/r2d2imagehosting/image/upload/f</a><em>auto,fl</em>lossy,q<em>80,c</em>limit,h<em>1000,w</em>700/ssmsBoostRecentTabs_bndhth.jpg) </p>\n\n<h2 id=\"searchingyourexecutedandeditorhistory\">Searching Your Executed and Editor History</h2>\n\n<p>Instructions I recommend for setup and searching your entire sql history nearly instantly. </p>\n\n<ol>\n<li>Install <a href=\"http://www.ssmsboost.com/\">SSMSBoost</a> (free community edition if you can’t support with professional version) </li>\n<li>Install <a href=\"http://docfetcher.sourceforge.net/en/index.html\">DocFetcher </a>(open source full text search tool. Best I found for searching and previewing sql files without the complexity of using GREP or other similar tools) </li>\n<li> Download and run <a href=\"http://www.winhelponline.com/utils/previewconfig.zip\">Preview Handler</a> from <a href=\"http://www.winhelponline.com/\">WinHelp</a> 4. Run Preview Handler > Find .SQL > Preview as plain text </li>\n<li>Run SSMS > Open Settings in SSMSBoost </li>\n<li>Configure settings as you see fit. I personally move my Editor History and Executed statement’s location to my SSMS Folder, so that I can use something like Create Synchronicity to backup all my work daily. <img src=\"http://res.cloudinary.com/r2d2imagehosting/image/upload/f_auto,fl_lossy,q_80,c_limit,h_1000,w_700/SSMSBoost_settings_kzsbzr.jpg\" alt=\"SSMSBoost<em>settings\" title=\"\" />(<a href=\"http://res.cloudinary.com/r2d2imagehosting/image/upload/f\">http://res.cloudinary.com/r2d2imagehosting/image/upload/f</a></em>auto,fl<em>lossy,q</em>80,c<em>limit,h</em>1000,w<em>700/SSMSBoost</em>settings<em>kzsbzr.jpg) 7. Restart SSMS for settings to take effect. 8. Start DocFetcher, go to settings in the top right hand corner. <img src=\"http://res.cloudinary.com/r2d2imagehosting/image/upload/f_auto,fl_lossy,q_80,c_limit,h_1000,w_700/DocFetcher_1_startup_irjelx.jpg\" alt=\"DocFetcher</em>1<em>startup\" title=\"\" />(<a href=\"http://res.cloudinary.com/r2d2imagehosting/image/upload/f\">http://res.cloudinary.com/r2d2imagehosting/image/upload/f</a></em>auto,fl<em>lossy,q</em>80,c<em>limit,h</em>1000,w<em>700/DocFetcher</em>1<em>startup</em>irjelx.jpg) Basic Settings I choose (If you aren’t using <a href=\"http://ftp.gnome.org/pub/GNOME/sources/ttf-bitstream-vera/1.10/\">Bitstream </a>font… you are missing out) <img src=\"http://res.cloudinary.com/r2d2imagehosting/image/upload/f_auto,fl_lossy,q_80,c_limit,h_1000,w_700/DocFetcher_2_basicSettings_unugtr.jpg\" alt=\"DocFetcher<em>2</em>basicSettings\" title=\"\" />(<a href=\"http://res.cloudinary.com/r2d2imagehosting/image/upload/f\">http://res.cloudinary.com/r2d2imagehosting/image/upload/f</a><em>auto,fl</em>lossy,q<em>80,c</em>limit,h<em>1000,w</em>700/DocFetcher<em>2</em>basicSettings_unugtr.jpg) </li>\n</ol>\n\n<h4 id=\"docfetcheradvancesettingstweakschange\">Docfetcher Advance settings tweaks Change:</h4>\n\n<pre><code>CurvyTabs = true HtmlExtensions = html;htm;xhtml;shtml;shtm;php;asp;jsp;sql InitialSorting = -8 \n</code></pre>\n\n<ul>\n<li>Why? Curvy tabs… because curves are nice </li>\n<li>HTML Extensions, obvious </li>\n<li><p>Initial Sorting = -8 means that instead of sorting by “match %” which I didn’t find helpful for me, to sort by modified date in desc order. This means I’ll find the most most recent match for the text I’m searching for at the top of my list. </p></li>\n<li><p>Setup your custom indexes. I setup separate indexes for executed statements and editor history so I could filter down what I cared about and eliminate near duplicate matches for the most part. Right click in blank space to create index. I setup as follows: <img src=\"http://res.cloudinary.com/r2d2imagehosting/image/upload/f_auto,fl_lossy,q_80,c_limit,h_1000,w_700/DocFetcher_createIndex1_t0qhl5.jpg\" alt=\"DocFetcher<em>createIndex1\" title=\"\" />(<a href=\"http://res.cloudinary.com/r2d2imagehosting/image/upload/f\">http://res.cloudinary.com/r2d2imagehosting/image/upload/f</a></em>auto,fl<em>lossy,q</em>80,c<em>limit,h</em>1000,w<em>700/DocFetcher</em>createIndex1<em>t0qhl5.jpg) <img src=\"http://res.cloudinary.com/r2d2imagehosting/image/upload/f_auto,fl_lossy,q_80,c_limit,h_1000,w_700/DocFetcher_createIndex2_i121sx.jpg\" alt=\"DocFetcher</em>createIndex2\" title=\"\" />(<a href=\"http://res.cloudinary.com/r2d2imagehosting/image/upload/f\">http://res.cloudinary.com/r2d2imagehosting/image/upload/f</a><em>auto,fl</em>lossy,q<em>80,c</em>limit,h<em>1000,w</em>700/DocFetcher<em>createIndex2</em>i121sx.jpg) 10. Now the DocFetcher daemon will run in the background, if you copied my settings, and update your indexes. Searching requires no complex regex, and can be done easily with statements. I’d caution on putting exact phrases in quotes, as it does detect wildcards.</p></li>\n</ul>\n\n<p><img src=\"http://res.cloudinary.com/r2d2imagehosting/image/upload/f_auto,fl_lossy,q_80,c_limit,h_1000,w_700/DocFetcher_previewSearch_m2g07n.jpg)(http://res.cloudinary.com/r2d2imagehosting/image/upload/f_auto,fl_lossy,q_80,c_limit,h_1000,w_700/DocFetcher_previewSearch_m2g07n.jpg\" alt=\"DocFetcher_previewSearch\" />\nbad search, due to wild card count(<em>) and test_table -- should better handle your search (implicit AND due to basic settings we chose) \"count(</em>)\" test<em>table or \"count(*)\" and test</em>table</p>",
"image": "http://res.cloudinary.com/r2d2imagehosting/image/upload/f_auto,fl_lossy,q_80,c_limit,h_1000,w_700/ssmsBoost_findInHistory_wl9xgr.jpg",
"featured": 0,
"page": 0,
"status": "published",
"language": "en_US",
"meta_title": null,
"meta_description": null,
"author_id": 1,
"created_at": "2013-05-22T07:39:51.000Z",
"created_by": 1,
"updated_at": "2016-03-18T17:18:43.000Z",
"updated_by": 1,
"published_at": "2013-05-22T07:39:51.000Z",
"published_by": 1,
"visibility": "public",
"mobiledoc": null
},
{
"id": 824,
"uuid": "3f932273-14ef-4a7d-8119-25cfee021b0c",
"title": "Are you using a fixed width font for development?",
"slug": "temp-slug-9",
"markdown": "\n<span data-rel=\"content\"></span>\n",
"html": "<p><span data-rel=\"content\"></span></p>",
"image": null,
"featured": 0,
"page": 0,
"status": "draft",
"language": "en_US",
"meta_title": null,
"meta_description": null,
"author_id": 1,
"created_at": "2013-05-22T08:30:25.000Z",
"created_by": 1,
"updated_at": "2013-05-22T08:30:25.000Z",
"updated_by": 1,
"published_at": "0000-00-00 00:00:00",
"published_by": 1,
"visibility": "public",
"mobiledoc": nullSo I did a quick fix for myself after messing around with it. I know from reading that rescue isn't a great practice, but i needed an adhoc run of this. I am brand new to Ruby so probably better way to do this.
I added error handling to the line that parses dates
posts.each do |post|
date = case post[:published_at]
when String
DateTime.strptime(post[:published_at], '%Y-%m-%d %H:%M:%S') rescue Date.today.next_year
# DateTime.parse(post[:published_at])
when Integer
Time.at(post[:published_at] / 1000).utc.to_datetime
endthe section i updated was this line with rescue statement to ensure it handled error if couldn't parse the date. I'm sure you'll know a better way! :-)
DateTime.strptime(post[:published_at], '%Y-%m-%d %H:%M:%S') rescue Correction, that hard coded everything wrong :-) When I removed the draft post published dates of 0000-00-00 everything worked, so some handling needs to happen at the beginning on the parsing to prevent 0000 dates from being throwing error. Hope this helps a little.
eloyesp
commented
8 years ago Thanks for the report, it should be fixed in the version 0.4.0 :)
Fresh backup taken this morning and failing to properly import.