tag:blogger.com,1999:blog-66982103092621374332024-03-14T01:23:49.461-04:00Code By Joshua the LionheartedThis is where I blog mostly for my own benefit, so I can remember some of the programming and IT stuff that I have worked on in the past.Joshuahttp://www.blogger.com/profile/08903612467158866477noreply@blogger.comBlogger16125tag:blogger.com,1999:blog-6698210309262137433.post-76353160110361185972022-06-23T13:40:00.002-04:002022-06-23T13:40:51.460-04:00Paradigm Architectural Control Processor Compact Flash (CF) card not found<div>Hi everyone,</div><div><br /></div><div>Just a little update on this error: CF Card not found. They say this can be the result of a bad compact flash card. But I just noticed that plugging in a new/blank/used card that has not been formatted by the Paradigm system can cause this as well.</div><div><br /></div><div>What do you need to do to fix it? Just FORMAT the new/used CF card on the system itself. I hope this helps someone out there save some trouble. I formatted up several used and new CF cards, and they worked just fine. We had the original card get corrupted due to power issues when our building switched from regular power to generator power, causing a likely power surge and damaging the old CF card.</div><div><br /></div><div>Follow the instructions for physically pulling the old CF card, and replacing with the different one. (see link below, under more info)</div><div><br /></div><div>With the new card installed, now do the following:</div><div><br /></div><div><br /></div><div>1. Upon booting you'll be met with an "Arch Config Failed to Load" and/or a "Unassigned Processor" error.</div><div><br /></div><div>2. Press the check mark to enter the Main Menu</div><div><br /></div><div>3. Scroll to Restricted Access and press the check</div><div><br /></div><div>4. Scroll to Login and press the check</div><div><br /></div><div>5. Enter 4116 and press the check. This put you back in the Restricted Access menu</div><div><br /></div><div>6. Press the back button ONCE to return to the Main Menu -- NOTE: If you accidentally press the back button twice, this takes you out of the Main Menu and you must follow steps 2 through 6 again to re-login</div><div><br /></div><div>7. Scroll down to ETC Only and press the check</div><div><br /></div><div>8. Scroll to Format Media and press the check</div><div><br /></div><div>9. Format Compact Flash should already be selected on the screen. Press the check to begin the process.</div><div><br /></div><div>10. During the format the screen will remain backlit, but will otherwise be blank. This is normal. After as little as a couple minutes (it could be longer if it's a larger CF card) the P-ACP will reboot, and you should see the standard Paradigm logo along with "Booting...." below it.</div><div><br /></div><div>11. After the P-ACP finishes booting you'll be met with the "Arch Config Failed to Load" and "Unassigned Processor" errors, or even the "CF Card not found". Now load the desired config file, either from LightDesigner, from the another P-ACP or P-CCS on the network, or via USB/SD.</div><div><br /></div><div><br /></div><div><br /></div><div>More info: <a href="https://support.etcconnect.com/ETC/Architectural/Paradigm/Paradigm_Architectural_Control_Processor_P-ACP/Hardware/Paradigm_Processor_Showing_CF_Card_Not_Found#:~:text=Reseat%20the%20CF%20Card%20into,this%20card%20has%20gone%20corrupt.&amp;text=Make%20sure%20to%20have%20the,the%20back%20of%20the%20unit." target="_blank">Replace CF Card</a></div>Joshuahttp://www.blogger.com/profile/08903612467158866477noreply@blogger.com0tag:blogger.com,1999:blog-6698210309262137433.post-78239584307972545482018-08-27T12:42:00.003-04:002018-08-27T12:42:50.892-04:00Find bad or corrupted PDF filesHi everyone,<br />
<br />
I work with several thousand PDF files and wanted a good way to find which ones are bad or corrupted due to various issues. I came up with this batch script that will work well on windows. It uses CPDF, which must be downloaded separately, and installed. Then my batch file will call the CPDF.exe file and proceed appropriately. Download here: https://www.coherentpdf.com/<br />
<br />
NOTE:<br />
<br />
You will need access to the location of CPDF in the filesystem. I chose to place it in the C:\Windows\System32 directory. You may choose to place it somewhere else. You may need to add the DOS path to the location of CPDF, in order for the batch script to correctly run, or you could probably place the path in the script itself, for example:<br />
<br />
REPLACE THE LINE: for /f "delims=?" %%W in ('cpdf -page-info "%%Z" 2^>^&1 ^| find /c "error"') do set NumberOfErrors=%%W<br />
<br />
WITH SOMETHING LIKE THIS: for /f "delims=?" %%W in ('c:\windows\system32\cpdf -page-info "%%Z" 2^>^&1 ^| find /c "error"') do set NumberOfErrors=%%W<br />
<br />
USAGE: Copy the script below and save in a .bat file, and then run in the folder that has all your PDF files...
=============================================================================================================<br />
<br />
Here is the actual Batch script:<br />
<br />
<br /><code>
:: This will cycle through PDF files and if any have errors or are corrupted<br />
<br />
:: it will rename the problem files with a prefix.<br />
<br />
:: EXAMPLE: myFile.pdf will be renamed to _BadPDF_myFile.pdf<br />
<br />
:: Uses the CPDF application, which must be downloaded...<br />
<br />
setlocal enabledelayedexpansion<br />
for /f "delims=?" %%Z in ('dir /b *.pdf') do (<br />
<br />
rem @echo "%%Z"<br />
for /f "delims=?" %%W in ('cpdf -page-info "%%Z" 2^>^&1 ^| find /c "error"') do set NumberOfErrors=%%W<br />
rem @echo !NumberOfErrors!<br />
if !NumberOfErrors! GTR 0 ren "%%Z" "_BadPDF_%%Z"<br />
<br />
)<br />
<br />
timeout /T 15<br />
<br />
exit
</code>Joshuahttp://www.blogger.com/profile/08903612467158866477noreply@blogger.com0tag:blogger.com,1999:blog-6698210309262137433.post-13992971752080906552016-11-03T10:32:00.000-04:002016-11-03T10:58:20.738-04:00Drop SQL tables based on table nameHere is a little SQL snippet for Microsoft SQL Server to drop (delete) tables based on their name. I have the custom of prefixing all old tables I don't use with a Z, so they drop to the bottom of the list. I finally wanted to delete them all, so here is a nice script that accomplished that. You must specify the specific database by changing 'YourDatabaseName.'<br />
<br />
<br />
<br />
<code>
-- Drop all tables based on a specific name<br />
-- Currently set to drop all tables starting with z<br />
-- This likely won't work for tables that have foreign keys...<br />
<br />
DECLARE @TABLE_NAME varchar(255)<br />
declare @SQL varchar(255)<br />
<br />
DECLARE CUR CURSOR FOR<br />
<br />
SELECT TABLE_NAME<br />
FROM INFORMATION_SCHEMA.TABLES<br />
WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_CATALOG='YourDatabaseName'<br />
and TABLE_NAME like 'z%'<br />
order by TABLE_NAME<br />
<br />
<br />
OPEN CUR<br />
<br />
FETCH NEXT FROM CUR INTO @TABLE_NAME<br />
<br />
WHILE @@FETCH_STATUS = 0<br />
BEGIN<br />
SET @SQL = 'drop table if exists ['+@TABLE_NAME + ']'<br />
<br />
PRINT @SQL<br />
--EXEC (@SQL) -- uncomment this line to actually delete your tables<br />
<br />
FETCH NEXT FROM CUR INTO @TABLE_NAME<br />
END<br />
<br />
CLOSE CUR<br />
<br />
DEALLOCATE CUR <br />
</code>
<br />
<br />
<br />Joshuahttp://www.blogger.com/profile/08903612467158866477noreply@blogger.com0tag:blogger.com,1999:blog-6698210309262137433.post-21616853298424698062015-08-10T19:20:00.000-04:002015-08-10T19:23:29.167-04:00A MS Word Macro for Auto Footer GenerationHere is a Microsoft Word macro that I worked up for a situation where I was trying to print a large number of .rtf files that didn't have any footer, but I wanted a footer that contained the filename, the print date and time, and the current page (of the total pages). It should work for printing other types of files, such as .doc, .docx, .txt, or possibly whatever you can open with word.<br />
<br />
In my case, I didn't want to save the footer, so I wrote a second macro that calls the first one, to print the file and exit without saving the file.<br />
<br />
<br />
Sub GenerateFooterAndInfo()<br />
'<br />
'<br />
If ActiveWindow.View.SplitSpecial <> wdPaneNone Then<br />
ActiveWindow.Panes(2).Close<br />
End If<br />
If ActiveWindow.ActivePane.View.Type = wdNormalView Or ActiveWindow. _<br />
ActivePane.View.Type = wdOutlineView Then<br />
ActiveWindow.ActivePane.View.Type = wdPrintView<br />
End If<br />
ActiveWindow.ActivePane.View.SeekView = wdSeekCurrentPageFooter<br />
<br />
'add filename to left side of footer<br />
Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, Text:= _<br />
"FILENAME ", PreserveFormatting:=True<br />
Selection.TypeText Text:=vbTab<br />
<br />
'add date to center of footer<br />
Selection.InsertDateTime DateTimeFormat:="M/d/yyyy h:mm:ss am/pm", _<br />
InsertAsField:=True, DateLanguage:=wdEnglishUS, CalendarType:= _<br />
wdCalendarWestern, InsertAsFullWidth:=False<br />
Selection.TypeText Text:=vbTab<br />
<br />
'add page of pagecount to right side of footer<br />
Selection.TypeText Text:="Page "<br />
Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, Text:= _<br />
"PAGE ", PreserveFormatting:=True<br />
Selection.TypeText Text:=" of "<br />
Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, Text:= _<br />
"NUMPAGES ", PreserveFormatting:=True<br />
ActiveWindow.ActivePane.View.SeekView = wdSeekMainDocument<br />
<br />
End Sub<br />
<br />
<br />
<br />
And here is the second macro that avoids saving the changes:<br />
<br />
<br />
<br />
<br />
Sub CreateTempFooterThenPrintAndExit()<br />
'<br />
'<br />
GenerateFooterAndInfo<br />
<br />
ActiveDocument.PrintOut<br />
<br />
'close document without saving autogenerated footer<br />
ActiveWindow.Close (False)<br />
Application.Quit<br />
<br />
End Sub<br />
<div>
<br /></div>
<div>
<br /></div>
<div>
These macros make it easy to open a file and immediately print it, without changing the file, and yet having the information about the file easily at hand. These could be easily modified to print the path to the filename, etc. for either headers or footers in Word documents.</div>
<div>
<br /></div>
<div>
Hope this helps someone out there.</div>
Joshuahttp://www.blogger.com/profile/08903612467158866477noreply@blogger.com0tag:blogger.com,1999:blog-6698210309262137433.post-62301290830516090382015-04-14T18:47:00.001-04:002015-04-14T18:47:18.351-04:00Add a TAB character to a Microsoft Access column directlyI was trying to add a tab character directly into a column in Microsoft Access in the same way ALT+CODE works for other types of characters. For example, ALT+0169 produces the Copyright symbol (©) just fine. But not for TAB, and probably other standard ASCII non-printable characters less than 128...<br />
<br />
So...<br />
<br />
Open Notepad and enter a tab, COPY it, and go to your cell in Microsoft Access, and add the tab wherever you want it.<br />
<br />
You won't be able to see it though...<br />
<br />
But not to worry, just copy the whole cell, and then past it back into Notepad and you will see it show up.<br />
<br />
Not sure why you may want a tab in your data, but if you do, this will work...<br />
<br />
I was using SQL Server 2005 and Access 2013 for thisJoshuahttp://www.blogger.com/profile/08903612467158866477noreply@blogger.com0tag:blogger.com,1999:blog-6698210309262137433.post-70125532710891209202015-04-14T17:42:00.000-04:002015-04-14T18:49:51.991-04:00Clean Every SQL Table Column of Unwanted DataHere is a simple T-SQL snippet for Microsoft SQL Server 2005 (and probably other editions) that you can use to clean up the strings in the columns of your table by removing extra spaces, tabs, carriage returns, and newlines. This is useful when you have imported data from somewhere and need to do some cleaning on it...<br />
<br />
<code>
/***************************************************************<br />
<br />
DESCRIPTION: This TSQL Code snippet will perform data cleaning on every varchar<br />
column and nvarchar column in the specified table, except for varchar(max) and<br />
nvarchar(max) columns.<br />
<br />
PURPOSE: This is useful for removing extra spaces, tabs, carriage returns, new lines, and<br />
trimming the front and end of the strings in all your columns. Add other cleaning<br />
routines if you would like, or combine multiple cleaning activities in one statement.<br />
<br />
WARNING: DATA LOSS WILL LIKELY RESULT<br />
Every [n]varchar column with a length less than max will be affected.<br />
<br />
RECOMMENDED: Test on a COPY of the desired table before use to determine data loss.<br />
<br />
<br />
<br />
***************************************************************/<br />
<br />
<br />
declare @sql as varchar(2000)<br />
declare @Table as varchar(250)<br />
declare @Column as varchar(250)<br />
declare @Count as int<br />
declare @x as int<br />
<br />
set @Table = 'Authors' -- ENTER YOUR TABLE NAME HERE<br />
set @Column = ''<br />
set @Count = 0<br />
set @x = 1<br />
<br />
<br />
-- delete the temp table if it exists<br />
IF OBJECT_ID(N'tempdb..#aTempTable') IS NOT NULL drop table #aTempTable<br />
<br />
<br />
-- get valid column names in a temp table<br />
SELECT<br />
row_number() over (order by ordinal_position) as ROW<br />
,COLUMN_NAME<br />
into #aTempTable<br />
FROM INFORMATION_SCHEMA.COLUMNS<br />
WHERE<br />
data_type like '%varchar' /* find all varchar and nvarchar columns */<br />
and table_name like @Table<br />
and character_maximum_length > 0 /* avoid varchar(max) columns which are -1 character_maximum_length */<br />
<br />
<br />
-- how many columns are in the table?<br />
set @Count = (select max(row) from #aTempTable)<br />
<br />
<br />
while @x <= @Count -- Loop through the column names, and perform data cleaning<br />
begin<br />
<br />
-- get the column name for this iteration<br />
set @Column = (select column_name from #aTempTable where row = @x)<br />
<br />
-- notify user what is happening (on the messages tab in SQL Server Managment Studio)<br />
print ''<br />
print ''<br />
print cast(@x as varchar(20)) + '. Cleaning ' + @Column<br />
<br />
<br />
<br />
-- perform various data cleaning activities on each column of the data in a given table<br />
<br />
<br />
-- replace tabs, carriage returns, and newlines with spaces<br />
set @sql = 'update ' + @Table + ' set [' + @Column + '] = replace(replace(replace([' + @Column +'],char(10),'' ''), char(13), '' ''),char(9),'' '')'<br />
--select (@sql)<br />
exec (@sql)<br />
<br />
<br />
-- replace two spaces with one<br />
set @sql = 'update ' + @Table + ' set [' + @Column + '] = replace(replace([' + @Column +'],'' '','' ''), '' '', '' '')'<br />
--select (@sql)<br />
exec (@sql)<br />
<br />
<br />
-- trim everything, removing beginning and trailing spaces<br />
set @sql = 'update ' + @Table + ' set [' + @Column + '] = ltrim(rtrim([' + @Column +']))'<br />
--select (@sql)<br />
exec (@sql)<br />
<br />
<br />
<br />
-- add your specialized cleaning code here...<br />
<br />
<br />
<br />
<br />
-- go to next column name<br />
set @x = @x + 1<br />
<br />
<br />
<br />
end<br />
<br />
<br />
drop table #aTempTable<br />
<br />
go<br />
<br />
<br />
/* Sample Output -- that would be run by the exec (@sql) statements<br />
<br />
update Author set [AuthorName] = replace(replace([AuthorName],char(10),' '), char(13), ' ')<br />
<br />
*/<br />
</code>
<br />
<br />
<br />
<br />Joshuahttp://www.blogger.com/profile/08903612467158866477noreply@blogger.com0tag:blogger.com,1999:blog-6698210309262137433.post-12806035639419577542014-02-25T17:25:00.000-05:002014-02-25T17:25:27.377-05:00Add the column Description property to the table designer view in SQL Server Management Studio 2012Wouldn't it be nice to see the column Description property right beside the Allow Nulls column for the columns in your table? This seems like a no-brainer, but it is not that easy to do in SQL Server Management Studio (SSMS). Unfortunately, you have to edit the registry. But if you do it correctly (be careful), you can take your table designer view from looking like this:<br />
<br />
SQL Server Management Studio before Description property is added...<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
</div>
<div class="separator" style="clear: both; text-align: center;">
</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-gV8zL6VZJhI/Uw0VCmxAUJI/AAAAAAAAABE/BltFLmkzObo/s1600/Before.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://2.bp.blogspot.com/-gV8zL6VZJhI/Uw0VCmxAUJI/AAAAAAAAABE/BltFLmkzObo/s1600/Before.png" height="398" width="640" /></a></div>
<br />
to this:<br />
<br />
SQL Server Management Studio after Description property is added...<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://4.bp.blogspot.com/-Pr0_7XHpaRw/Uw0Vcf0Z0MI/AAAAAAAAABM/jVdHvg5KirM/s1600/After.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://4.bp.blogspot.com/-Pr0_7XHpaRw/Uw0Vcf0Z0MI/AAAAAAAAABM/jVdHvg5KirM/s1600/After.png" height="398" width="640" /></a></div>
<br />
This looks so much more useful!<br />
<br />
Follow these instructions for how to do this for SQL Server Management Studio 2012. See the bottom for a link that should work for older versions of SSMS.<br />
<br />
USE CAUTION BELOW.<br />
<br />
REGEDIT, if not used correctly, can really mess up your computer. I recommend backing up your registry BEFORE trying this. Be sure to back up the WHOLE registry, and not just a portion of it when using File>Export. Notice that at the bottom of the save dialog you can choose the Export Range. You should choose All, so you save the whole registry and not just a portion of it…<br />
<br />
Using regedit, modify your registry by going to the following location (for SQL Server Management Studio 2012)<br />
<br />
HKEY_CURRENT_USER\Software\Microsoft\SQL Server Management Studio\11.0\DataProject<br />
<br />
Find and modify the following keys:<br />
•<span class="Apple-tab-span" style="white-space: pre;"> </span>SSVPropViewColumnsSQL70<br />
•<span class="Apple-tab-span" style="white-space: pre;"> </span>SSVPropViewColumnsSQL80<br />
<br />
Modify the values of these keys from 1,2,6; to 1,2,6,17; which is effectively just adding 17, which is the value for the table column Description property. You could add probably add other properties as well, as noted below, but I haven’t tried it…<br />
<br />
Once you have made the changes, exit Regedit, and restart SQL Server Management Studio. Then you should see the Description column as shown above.<br />
<br />
If you wanted to add other columns, you probably could... see the corresponding numbers below...<br />
<br />
Property sequence:<br />
1.<span class="Apple-tab-span" style="white-space: pre;"> </span>Column Name<br />
2.<span class="Apple-tab-span" style="white-space: pre;"> </span>Data Type<br />
3.<span class="Apple-tab-span" style="white-space: pre;"> </span>Length<br />
4.<span class="Apple-tab-span" style="white-space: pre;"> </span>Precision<br />
5.<span class="Apple-tab-span" style="white-space: pre;"> </span>Scale<br />
6.<span class="Apple-tab-span" style="white-space: pre;"> </span>Allow Nulls<br />
7.<span class="Apple-tab-span" style="white-space: pre;"> </span>Default Value<br />
8.<span class="Apple-tab-span" style="white-space: pre;"> </span>Identity<br />
9.<span class="Apple-tab-span" style="white-space: pre;"> </span>Identity Seed<br />
10.<span class="Apple-tab-span" style="white-space: pre;"> </span>Identity Increment<br />
11.<span class="Apple-tab-span" style="white-space: pre;"> </span>Row GUID<br />
12.<span class="Apple-tab-span" style="white-space: pre;"> </span>Nullable<br />
13.<span class="Apple-tab-span" style="white-space: pre;"> </span>Condensed Type<br />
14.<span class="Apple-tab-span" style="white-space: pre;"> </span>Not for Replication<br />
15.<span class="Apple-tab-span" style="white-space: pre;"> </span>Formula<br />
16.<span class="Apple-tab-span" style="white-space: pre;"> </span>Collation<br />
17.<span class="Apple-tab-span" style="white-space: pre;"> </span>Description<br />
<br />
<br />
For older versions of SQL Server Management Studio, see this link…<br />
<br />
http://stackoverflow.com/questions/10537610/how-do-i-add-the-description-property-to-the-table-designer-view-in-ssms<br />
<a href="http://stackoverflow.com/questions/10537610/how-do-i-add-the-description-property-to-the-table-designer-view-in-ssms">Go there ></a><br />
<br />
<br />
<br />
<br />Joshuahttp://www.blogger.com/profile/08903612467158866477noreply@blogger.com9tag:blogger.com,1999:blog-6698210309262137433.post-75407804070973417322013-08-27T18:28:00.001-04:002013-08-27T18:52:47.861-04:00Find Count of Files in a Folder at the Command PromptSo you want to find a count of the number of files in a directory using the command prompt or a batch file, etc. Here is a really easy way to do it.<br />
<br />
Just run the dir command and pipe the results to the find command, using the count parameter:<br />
<br />
dir|find /c ".tif"<br />
<br />
<br />
This will return a count of all the TIF files in your folder. You could replace ".tif" with whatever extension you want, such as ".pdf"<br />
<br />
<br />
What about a folder with multiple types of files in it? Just add the /b parameter to the dir command to get the bare filenames, and then just look for the single period in the find command, like so:<br />
<br />
dir /b|find /c "."<br />
<br />
<br />
An alternate version that would count all the files in the directory and any sub directories (by adding the /s parameter to the dir command) would be:<br />
<br />
dir /b /s|find /c "."<br />
<br />
<br />
The above won't work if the filenames have other periods in them, like some.name.txt, but for the average filename, this should work just fine.<br />
<br />
<br />
Lastly, here is the approach that will work with filenames, like some.name.txt, that have multiple periods in them, because it counts the lines, not the periods, in the output:<br />
<br />
dir /b /a-d | find /c /v ""<br />
<br />
<br />
That last one came from Joey:<br />
<br />
<a href="http://serverfault.com/questions/110725/windows-command-prompt-how-to-get-the-count-of-all-files-in-current-directory">Go Visit Joey's explanation...</a><br />
<br />
<br />
<br />
Hope this helps someone out there...<br />
<br />
<br />
Keywords: batch file, batch script counting files in a folderJoshuahttp://www.blogger.com/profile/08903612467158866477noreply@blogger.com2tag:blogger.com,1999:blog-6698210309262137433.post-80334567031554275482013-08-12T13:11:00.000-04:002013-08-12T13:11:12.595-04:00SQL Server 2005 Restore Database Error: The operating system returned the error '5(Access is denied.)'I was trying to restore some old SQL Server 2005 databases to get some data they contained, when I ran into this error:<br />
<br />
System.Data.SqlClient.SqlError: The operating system returned the error '5(Access is denied.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\test.mdf'. (Microsoft.SqlServer.Smo)<br />
<br />
Your error message may look similar, except for the file location on the C drive, or whatever drive you are using.<br />
<br />
After some frustration, I figured out the problem. <b>I was actually trying to write my .mdf and .ldf files in a folder where they should not be located. That is why "Access is Denied" and it should be! </b>You should write the files where your other database files are written. (If you need to find where they are, in SQL 2005, set up your Restore Database popup to restore from a currently active database, then go to the Options page on the left, and under "<i>Restore the database files as:</i>" look at "<i>Restore As</i>" which will contain the correct file location.)<br />
<br />
In my error case, this location was not where my currently active databases where located, but was set to:<br />
<br />
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\<br />
<br />
Your settings may be similar. In my case, this was definitely the wrong location, in fact, <b>the wrong drive!</b><br />
<br />
So to fix my situation, (and hopefully yours), I had to choose a new path and location for my .mdf and .ldf files. This is where the newly restored database files will be stored. (Which should be in the same folder as your other active databases):<br />
<br />
<ol>
<li>Starting from the Restore Database popup, look to the left and choose the Options page (in SQL 2005), then look for "<i>Restore the database files as</i>" right in the middle of the page.</li>
<li>Just look to the right of the Restore As cell (on the Restore Database Options page), and click on the edit box symbol, which will bring up the Locate Database Files popup. Choose the correct path to the directory where you want the .mdf file to be stored, then enter the correct filename. Do the same for the .ldf file. </li>
</ol>
Once I did this for both files, I was able to restore the database successfully. My assumption is that this will be similar for issues with SQL 2008 and 2012, etc.<br />
<br />
Hope this helps you...<br />
<br />
<br />
<br />Joshuahttp://www.blogger.com/profile/08903612467158866477noreply@blogger.com3tag:blogger.com,1999:blog-6698210309262137433.post-70173201893625581762013-06-04T14:03:00.002-04:002013-06-04T14:03:41.893-04:00I was having trouble on Windows 2008 R2 running a batch script from a (local) shared drive as an Administrator (by right clicking and choosing run as Administrator). The command prompt window would just flash and never run successfully run the script, so doing the following made the shared drive "visible" to the Administrator profile, and would then allow successful running of the script.<br />
<br />
Here is what I did, using ideas gleaned from the websites listed below... and copying some of their comments... Thanks guys...<br />
<br />
<br />
--I wanted to create a service that would allow me to spawn a command prompt under the context of the local system<br />
<br />
sc create syscommand binpath= "cmd /K start" type= own type= interact<br />
<br />
<br />
--Then I started the service<br />
<br />
sc start syscommand<br />
<br />
<br />
--Voila, apart from a few minor errors, the command prompt popped up (sigh of relief)<br />
<br />
--Run Net to map a drive for the system context, then every user account, including the Administrator, will have access to that shared drive<br />
<br />
NET USE Z: \\MyLocalMachine\MyLocalShare<br />
<br />
<br />
--removed the service account<br />
<br />
sc delete syscommand<br />
<br />
<br />
<br />
Ideas taken from:<br />
<br />
<br />
http://wmug.co.uk/wmug/b/scambler/archive/2011/02/07/interactive-command-prompt-under-the-system-context-and-server-2008.aspx<br />
<br />
and<br />
<br />
http://www.sevenforums.com/network-sharing/34110-run-admin-network-drive.html<br />
<div>
<br /></div>
Joshuahttp://www.blogger.com/profile/08903612467158866477noreply@blogger.com0tag:blogger.com,1999:blog-6698210309262137433.post-44303409335487433192013-03-28T11:45:00.000-04:002014-10-02T11:32:50.936-04:00Convert Excel .xls to .xlsx and .xlsm using VBAHere are some Excel macros to quickly convert .xls files to .xlsx and .xlsm formats for newer versions of Microsoft Excel. Just paste this in your VBA window (in a module) in a .xlsm file, and use Excel to automatically do its own conversions. You then run either macro, which will ask you to choose a folder, and then all .xls files will be converted to .xlsx or .xlsm depending on whether they have macros or not...<br />
<br />
<br />
<code>
Sub Copy_XLS_as_XLSX()<br />
<br />
Convert_XLS_to_XLSX False<br />
<br />
End Sub<br />
<br />
Sub Delete_XLS_after_Copy_XLS_as_XLSX()<br />
<br />
Convert_XLS_to_XLSX True<br />
<br />
End Sub<br />
<br />
Sub Convert_XLS_to_XLSX(ByVal deleteXLS As Boolean)<br />
<br />
' Allow user to choose a folder, where all .xls files in that folder will be converted to<br />
' .xlsx or .xlsm format, depending on whether they have macros or not...<br />
<br />
<br />
<br />
Dim xDirect$, xFname$, InitialFoldr$<br />
Dim wbk As New Workbook<br />
Dim msg As Integer<br />
<br />
<br />
<br />
InitialFoldr$ = "c:\temp\" 'Startup folder to begin searching from<br />
<br />
If deleteXLS = True Then 'as user if they really want to delete .xls files<br />
<br />
msg = MsgBox("Do you want to delete all .xls files after you have created a copy in .xlsx format? If you are not sure, click NO!", vbYesNo, "Ready to delete .xls files?")<br />
<br />
End If<br />
<br />
If msg = vbNo Then 'user doesn't want to delete files...<br />
<br />
deleteXLS = False<br />
<br />
End If<br />
<br />
With Application.FileDialog(msoFileDialogFolderPicker)<br />
.InitialFileName = Application.DefaultFilePath & "\"<br />
.Title = "Please select a folder containing the .xls files you want to convert..."<br />
.InitialFileName = InitialFoldr$<br />
.Show<br />
If .SelectedItems.Count <> 0 Then<br />
xDirect$ = .SelectedItems(1) & "\"<br />
xFname$ = Dir(xDirect$, 7)<br />
<br />
Do While xFname$ <> "" 'loop through all filenames in folder<br />
<br />
If Right(xFname$, 4) = ".xls" Then 'only convert .xls files<br />
<br />
Application.DisplayAlerts = False 'turn off any unwanted messages<br />
<br />
Set wbk = Workbooks.Open(Filename:=xDirect$ & xFname$)<br />
<br />
If wbk.HasVBProject Then ' convert Excel files containing Macros<br />
wbk.SaveAs Filename:=xDirect$ & xFname$ & "m", _<br />
FileFormat:=xlOpenXMLWorkbookMacroEnabled<br />
<br />
Else ' convert standard Excel files<br />
wbk.SaveAs Filename:=xDirect$ & xFname$ & "x", _<br />
FileFormat:=xlOpenXMLWorkbook<br />
End If<br />
<br />
wbk.Close SaveChanges:=False<br />
<br />
If deleteXLS = True Then 'delete existing xls files if desired<br />
<br />
With New FileSystemObject 'include Excel reference to Microsoft Scripting.Runtime library... or this won't work... Go to Tools>References in the VBA editing window<br />
<br />
If .FileExists(xDirect$ & xFname$) Then<br />
.DeleteFile xDirect$ & xFname$<br />
End If<br />
<br />
End With<br />
<br />
End If<br />
<br />
Application.DisplayAlerts = True 'turn messages back on<br />
<br />
End If<br />
<br />
xFname$ = Dir ' get next filename in folder<br />
<br />
Loop<br />
<br />
End If<br />
<br />
End With<br />
<br />
xRow = MsgBox("All .xls files have now been converted.", , "Finished!")<br />
<br />
<br />
<br />
End Sub<br />
</code><br />
<div>
<code><br /></code></div>
<code>
<br />
</code>
<br />
<div>
<br /></div>
Joshuahttp://www.blogger.com/profile/08903612467158866477noreply@blogger.com3tag:blogger.com,1999:blog-6698210309262137433.post-32870734367802235562012-02-29T12:15:00.001-05:002012-02-29T12:16:55.084-05:00PDF Bookmarks: Change Zoom level and Name of PDF BookmarksIf you are wanting to change the Bookmark zoom level on a bunch of your PDF bookmarks in Adobe Acrobat, or just modify the name of your bookmarks, here is the easy way to do it:<br />
<br />
<br />
Use JPDFBookmarks: <br />
<br />
http://sourceforge.net/projects/jpdfbookmarks/<br />
<br />
<br />
<br />
And follow these instructions:<br />
<br />
<ol><li>Modify one of your bookmarks to inherit the zoom (or choose whatever type of zoom you want)</li>
<li>Download <a href="http://sourceforge.net/projects/jpdfbookmarks/">JPDFBookmarks</a> </li>
<li>Open the zip file and extract the files, then open JPDFBookmarks.</li>
<li>Open your PDF file in JPDFBookmarks.</li>
<li>Dump the bookmark data to a text file using JPDFBookmarks with the command Tools>Dump.</li>
<li>Find the bookmark you edited (in the text file that opens,) copy the correct portion of the text associated with your edited bookmark that is different from all the other bookmarks, such as <b>TopLeftZoom,0,0,0.0</b>, and replace the other setting on all the other bookmarks, such as <b>FitWidth, -6, </b>using Find and Replace in Notepad.</li>
<li>Change the name of any bookmark as well, by editing the name of the bookmark. </li>
<li>Save the changes to the text file.</li>
<li>In JPDFBookmarks, load the bookmark data back using Tools>Load</li>
<li>Save the file in JPDFBookmarks. </li>
<li>Open the PDF in Adobe Acrobat, and the bookmarks are fixed.</li>
</ol><br />
This is a great open source solution for working with PDF bookmarks, and changing the zoom level of existing PDF bookmarks.<br />
<br />
See also: <br />
<br />
http://www.serpentus.com/2011/05/10/acrobat-set-all-bookmarks-to-inherit-zoom/<br />
<br />
Keywords:<br />
<br />
Rename many PDF Bookmarks<br />
Inherit Zoom<br />
Fit Page<br />
Actual Size<br />
Fit Width<br />
Fit Visible<br />
Named Destination<br />
pdf bookmarks inherit zoom <br />
dozens hundreds thousands many bunch<br />
edit PDF bookmarksJoshuahttp://www.blogger.com/profile/08903612467158866477noreply@blogger.com9tag:blogger.com,1999:blog-6698210309262137433.post-61318600041607384822012-01-05T20:40:00.000-05:002012-01-05T20:40:55.088-05:00Get data from a SQL Server Stored Procedure into Excel automaticallyIntro:<br />
<br />
Do you want to get data from a stored procedure in SQL Server and have it drop right into MS Excel automatically? I recently needed just that, so follow this simple tutorial to get the job done right away...<br />
<br />
How to run a SQL Stored Procedure from Excel without using VBA, and even include dynamic parameters that can be stored in cells in Microsoft Excel!!!<br />
<br />
<br />
<br />
To pull data from a SQL Server Stored Procedure in Excel using parameters, do the following:<br />
<br />
<br />
Excel 2007 Instructions:<br />
<br />
<br />
<br />
1. Select the Data tab on Excel's Ribbon, then within the Get Exernal Data group choose the "From other Sources" drop-down. Then Choose "From Microsoft Query"<br />
<br />
2. Within "Choose Data Source" pop-up box, select your SQL Server, then hit OK.<br />
<br />
3. Close the "Add Tables" popup if necessary.<br />
<br />
4. Click on the "SQL" button, or choose View > SQL to open the SQL pop-up editor.<br />
<br />
5. Enter the following syntax: {CALL myDatabaseName.dbo.myStoredProc (?, ?, ?)}<br />
<br />
For example: {CALL northwind.dbo.spGetMaxCost (?, ?, ?)}<br />
<br />
Be sure to include the squiggly braces around the call statement. Each Question Mark (?) indicates a parameter. If your stored procedure calls for more or less parameters, add or subtract question marks as needed.<br />
<br />
6. Hit the OK button. A question box should pop-up saying "SQL Query can't be represented graphically, continue anyway?", just hit the OK button.<br />
<br />
7. You will now be asked for sample parameters for each question mark you included above. Enter valid parameter values for the data you are querying.<br />
<br />
8. Once you have entered the last parameter, you should get some results back in Microsoft Query. If they look good, close Microsoft Query.<br />
<br />
9. You should now be looking at an "Import Data" pop-up. Click the Properties button, which will bring up the "Connection Properties" pop-up.<br />
<br />
10. Select the Definition tab, then select the Parameters button. You should now see a "Parameters" pop-up, where you can connect the parameter to a specific cell.<br />
<br />
11. Select Get the value from the following cell, and then connect to an appropriate cell in Excel that will hold your parameter, by clicking the little box with the arrow.<br />
<br />
12. If you want the data to refresh every time you change the cell containing the parameter, check the box stating "Refresh automatically when cell value changes"<br />
<br />
13. Continue as above for the other parameters. When finished, click OK, to return to the Connection Properties pop-up. Click OK to return to the Import Data pop-up, and click OK again.<br />
<br />
14. You should now have some data straight from your stored procedure.<br />
<br />
<br />
<br />
Hope this helps someone out there!<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
Here are some common error messages that you may experience when trying to do this...<br />
<br />
No value given for one or more required parameters.<br />
<br />
Parameters are not allowed in queries that can't be displayed graphically.<br />
<br />
Could not find stored procedure ... <your name="" procedure="" stored=""> </your><br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
Tags and Common Search Phrases:<br />
<br />
<br />
<br />
connect excel to stored procedure<br />
<br />
Excel Get external data from stored procedure<br />
Microsoft query to SQL Server stored procedure from excel<br />
get data into excel from stored procedure<br />
add parameters to excel for stored procedureJoshuahttp://www.blogger.com/profile/08903612467158866477noreply@blogger.com22tag:blogger.com,1999:blog-6698210309262137433.post-40519297389398535072011-01-26T18:12:00.000-05:002011-01-26T18:12:08.904-05:00Speed up Windows Search on Windows 2008 R2I wanted to speed up Windows Search indexing on my Windows 2008 R2 server. Every time I moved my mouse or something, the indexing of my content would slow or stop. I have a quad core cpu and wanted it to just index the content quickly, so I could get on with life and not have to wait for it, while using my computer for other things...so....<br />
<br />
Open regedit as an Administrator, and brows to the registry key:<br />
<br />
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows Search\Gathering Manager\<br />
<br />
now look for the key DisableBackOff<br />
<br />
which should be set to zero.<br />
<br />
This should be set to 1, then Windows Search will index like mad until it is done Indexing your content. If you try to set this value and get an error saying you don't have adequate permissions, you have to take ownership of the Gathering Manager node. To do this right click on the Gathering Manager node, then choose permissions, then Advanced, then Owner tab, then give ownership to the Administrators group (or yourself), then apply.<br />
<br />
Now you should be able to change the Disablebackoff setting to 1, and Windows search should really perform.<br />
<br />
Don't forget to restart the Windows Search Service, to apply this new setting.Joshuahttp://www.blogger.com/profile/08903612467158866477noreply@blogger.com0tag:blogger.com,1999:blog-6698210309262137433.post-43229020477481580882010-10-28T20:57:00.000-04:002010-10-28T20:57:17.552-04:00Calling SQLXMLBulkLoad from aspx page using VB.NETIf you are using SQLXMLBulkLoad in VB.Net from an aspx page consider the following:<br />
<br />
I was having trouble getting SQLXMLBulkLoad to load my xml file when called from a .aspx page. What I had to do was add the <b>AspCompat="true"</b> to the header (.aspx @ Page directive) on the aspx page.<br />
<br />
For example, here is my header:<br />
<br />
<%@ Page <b>AspCompat="true"</b> Language="vb" AutoEventWireup="false" Codebehind="MyPage.aspx.vb" Inherits="AST.Web.Directory.MyPage" %> <br />
<br />
Adding <b>AspCompat="true"</b> makes the aspx page run in a Single Thread Apartment mode, so then the SQLXMLBulkLoad COM object will work correctly.<br />
<br />
Here is an example of my code calling the SQLXMLBulkLoad object in VB.net:<br />
<br />
<pre> Dim bulkXML As SQLXMLBULKLOADLib.SQLXMLBulkLoad4Class = New SQLXMLBULKLOADLib.SQLXMLBulkLoad4Class
connection = "provider=SQLOLEDB;data source=" & Server & ";database=" & Database & ";integrated security=SSPI"
bulkXML.ConnectionString = connection
bulkXML.ErrorLogFile = ErrorFilePath
bulkXML.SGDropTables = False
bulkXML.SchemaGen = True
bulkXML.BulkLoad = False
bulkXML.Execute(XMLSchemaFilePath)
bulkXML.BulkLoad = True
bulkXML.Execute(XMLSchemaFilePath, XMLFilePath)</pre><br />
Once I added the AspCompat="true" statement, everything worked!<br />
<br />
I learned this from Gary here: <br />
<a href="http://sqlxml.org/faqs.aspx?faq=90">http://sqlxml.org/faqs.aspx?faq=90</a><br />
<pre>For anyone trying to run this in an ASP.NET page, ensure that
ASPCompat="true" is added to the .aspx @Page directive. The lines
declaring a new thread are not necessary.
</pre>Hope this saves someone some time!Joshuahttp://www.blogger.com/profile/08903612467158866477noreply@blogger.com0tag:blogger.com,1999:blog-6698210309262137433.post-66205551909854192092010-08-11T13:17:00.000-04:002010-08-11T13:17:43.988-04:00Compact and Repair on Access 2010 64-bitI recently had some issues using Compact and Repair while using Access 2010 (64-bit version). I couldn't get my database to compact and repair, and kept getting the following error message:<br />
<br />
Could not use 'C:\temp\myDB.mdb'; file already in use. <br />
<br />
Of course Access help provided nothing, so hopefully this will help you. Turns out that you need to have EXCLUSIVE ACCESS to the file, so it is guaranteed that no one else is using it, in order to compact and repair it. Here is how you do that:<br />
<br />
<ol><li>Open Access, but not a specific database.</li>
<li>On the File tab, click Open.</li>
<li> Navigate your file system to the database you want.</li>
<li>Instead of clicking the Open button, click the dropdown on the button.</li>
<li>Choose Open Exclusive.</li>
<li>With your database now open, choose File, and then Compact and Repair.</li>
</ol>This allows you to have Exclusive access to the database, so running Compact and Repair doesn't affect some other user of the database. Of course, other users will have to be logged off of the database for you to have exclusive access.<br />
<br />
Hope this helps some user out there...<br />
<br />
<br />
JoshuaJoshuahttp://www.blogger.com/profile/08903612467158866477noreply@blogger.com5