SQL: Maintaining Custom User Data Via Publishing Wizard
We refresh our testing databases periodically with no problems. It is a logical protocol to make sure to alert all users ahead of time so that they may backup any needed custom data that they have entered themselves; however, when I did a data refresh this week something went awry and before I knew it there was a line to my desk, the phone was ringing, and email started piling up.
Fortunately, we did have a backup copy of this database so I was able to query and recover this data. However, I needed a quick and simple way to have the users re-create this data without my involvement. I could set up some type of job to restore the data after each refresh , but the data would only need to exist for a few months and I did not have time to go through the red tape that would be required for this. Given this particular scenario, it was decided to create a SQL script that would do INSERTS for the data.
Creating an INSERT script should not be difficult but it was too timely. Exporting and Importing the data was not ideal. I decided to investigate tools that would create the script for me. And there it was — on my machine all along — the SQL Server Publishing Wizard. I had forgotten all about it. I had just needed to locate it since there was no icon or link; therefore, the entire purpose of this way too-long-winded post :
C:Program FilesMicrosoft SQL Server90ToolsPublishing1.4SqlPubWiz.exe
Yay, now I have a reference and reminder!
Anyway, it is worth mentioning that this tool is way inappropriate for large data. The data needed was a very small portion compared to the millions of rows that exists. I had to query the needed data to insert into a temp table and this made the wizard fast. Ideally, it would have been better to have the ability to query a table for data from within the wizard but I’m sure it is just a matter of time.
Error: Excel 2007: Cannot Find the File …
Excel 2007 Network Error: Cannot find file …
When using Excel 2007, I’ve had problems with this error occurring when I tried to open an .xls or .xslx file for the first time. The file would open on a second attempt but not the first. I had assumed that it had something to do with my files being on a network drive instead of being stored locally. Anyway, I was always too busy to deal with it; however, this week I began getting the error on every attempt to open an Excel file. The only way I could get the file to open would be to start Excel and then open a file from within. I finally broke down and did some research.
First, I checked my file associations, reset them, and that did not work. Then I tried manipulating the permissions and this did not work either. Finally, I found the resolution:
1. Open Excel
2. Click on Orb
3. Click on “Excel Options”
4. Navigate to “Advanced” in the left pane.
5. In the right pane, scroll down until you seea “Ignore other applications that use Dynamic Data Exchange” and uncheck the checkbox.
Previous Version of Excel: Navigate to Tools –> Options –> locate and uncheck the above checkbox
Error: Cannot resolve the collation conflict
This error started occurring after one of my SQL Server 2008 databases had been restored:
Msg 468, Level 16, State 9 …
Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “SQL_Latin1_General_CP1_CS_AS” in the equal to operation.
Supposedly, this type of error can occur when the tempdb collation does not match the current database collation — especially if using temp tables.
The easiest way to deal with this is to cast one of the field’s collation so it matches the other, or you can cast both if you need. I’m not sure if this is best practice in the long run; however, if you’re just doing some ad-hoc data mining then this is definitely a quick fix:
INNER JOIN [TABLE1].FIELD1 AS EA
ON EA.SERIAL_NBR COLLATE database_default = E.SERIAL_NBR COLLATE database_default
XBox Game Review: Your Shape — Fitness Evolved
My Rating: 7/10
I got this program last year for the Kinect and still have not finished it. Even though the graphics are obviously first-generation Kinect, the content is good — if you’ve got the motivation. Unfortunately, the game is not very motivating. I wouldn’t even call this a game since there is no fun-factor. I’m sure I burned many more calories in Kinect Adventures. After all, white-water rafting is much more enjoyable than squats.