Blending life, nature and technology.
Posts tagged sql server
AdventureWorks…
Dec 24th
Adventureworks is a huge sample database of a make believe bike company provider by Microsoft, and l find this particularly useful when working on/learning OLTP and SSAS – ie Online transaction processing as well as SQL Server analysis services used to make business intelligence decisions, datamarts etc.
Helpful notes:
- download the latest version from codeplex – click here - the file l got was AdventureWorks2008_SR4.exe
- Run this program
- For installing SSAS - SQL Server Analysis Services database see this webpage
- Look for the install scripts in C:\Program Files\Microsoft SQL Server\100\Tools\Samples\
- dont forget to execute the scripts in sqlcmd mode,
Look for code like:
–:setvar SqlSamplesDatabasePath “C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\”
–:setvar SqlSamplesSourceDataPath “C:\Program Files\Microsoft SQL Server\100\Tools\Samples\”
In the scripts, in the sample source path folder on your workstation, make sure you modify and set correctly at least the top line for the sql samples database path
Also, make sure if you made modifications to your model database default size, you adjust the database file size in the create database portion of the script as eg:
CREATE DATABASE [AdventureWorks2008]
ON (NAME = ‘AdventureWorks2008_Data’, FILENAME = N’$(SqlSamplesDatabasePath)AdventureWorks2008_Data.mdf’, SIZE = 170, FILEGROWTH = 8),
FILEGROUP DocumentFileStreamGroup CONTAINS FILESTREAM ( NAME = FileStreamDocuments, FILENAME = N’$(SqlSamplesDatabasePath)Documents’)
LOG ON (NAME = ‘AdventureWorks2008_Log’, FILENAME = N’$(SqlSamplesDatabasePath)AdventureWorks2008_Log.ldf’ , SIZE = 2, FILEGROWTH = 96);
GO
SQL Azure
Dec 6th
SQL Azure is a fully relational database in the cloud by Microsoft.
See this link for the main website.
For how to connect to Azure using ADO.Net heres a good link from msdn and another link which l’m currently using to work on plugging the northwind sample database to azure and using a WPF datagrid view to present the data.
Microsoft Certified Technology Specialist: Sql Server 2008…
Jun 5th
Passed my Microsoft Cerfification Exam on first attempt on Tuesday June 2nd! Score: 814
The certificate says l’m now recognized as a MCTS – Microsoft Certified Technology Specialist: SQL Server 2008, Implementation and Maintenance. The exam is the MCTS 70-432. Next goal is the 70-450 which has no study book in print, and the only material l can find so far is the CBT-NUGGETS training video on Exam 70-450 which is very good.
Materials l used to study other than multiple SQL Server instances on my laptop, Virtual PC Windows 2008 Machines with SQL Server 2008 installed – for database replication, mirroring and failover clustering practise!!!
- MCTS Self-Paced Training Kit (Exam 70-432): Microsoft® SQL Server® 2008 – Implementation and Maintenance by Mike Hotek (Very good, in-depth all round material, lab oriented book with real life stories). This seems to be the only main book out there.
- Self Test Software for MCTS 70-432, bought the download version of this excellent practise test program.
- Microsoft Sql Server 2008 Books on line (download the latest update from Microsoft)
- Took the official Microsoft 6231 course at SQLSoft (Beaverton)
- Books on Microsoft SQL Server 2005 exam 70-431
Deleting a SQL Server maintenance plan….
Mar 25th
If you keep getting this error:
The DELETE statement conflicted with the REFERENCE constraint “FK_subplan_job_id”. The conflict occurred in database “msdb”, table “dbo.sysmaintplan_subplans”, column ‘job_id’.
The statement has been terminated. (Microsoft SQL Server, Error: 547)
RUN:
(1)
Use MSDB
GO
Delete from sysmaintplan_subplans
Where subplan_name = ‘xyz’
GO
If theres a reference error to sysmaintplan_log, first get the subplan_id
(2)
SELECT
[subplan_id]
–,[subplan_name]
–,[subplan_description]
–,[plan_id]
–,[job_id]
–,[schedule_id]
–,[msx_job_id]
–,[msx_plan]
FROM [msdb].[dbo].[sysmaintplan_subplans]
then RUN:
Use MSDB
GO
Delete from sysmaintplan_log
Where subplan_id = ’0805BDF5-4A25-4175-8D3E-D6B041714D30′
GO
(above subplan_id was from my server)
Then run query #1 again.
SQL Server error
Feb 6th
Cannot insert the value NULL into column ‘owner_sid’, table ‘msdb.dbo.sysjobs’; column does not allow nulls. INSERT fails.
If you get the above message, make sure you set the login correctly in the script you are attempting to run: For example, on my script that causes the error above,
changing
@owner_login_name=N’sa’,
to
@owner_login_name=N’stevesadminaccount’,
Obviously logging in as sa would have solved the problem, but thats not a recommended safe practise.
SQL 2008 Reinstall nightmare..
Jan 18th
Trying to un-install a Microsoft Heros event 2008 release of SQL Server 2008 on my Windows 2008 server box but each time l uninstall it and all related components, on trying a fresh install for a default instance l keep seeing the previous default MSSERVER instance.
Heres the link to Mark Michaelis Weblog which has hacks on forcefully removing Sql Server 2008 using command line as well as registry hacks. http://mark.michaelis.net/Blog/SQLServer2008InstallNightmare.aspx
Finally, after running all the steps in his blog, l ran the Installed SQL server discovery report from SQL Server 2008 installation center (under the Tools section) and l found l have succesfully removed the instance.
Microsoft SQL Server 2008 Setup Discovery Report
Product Instance Instance ID Feature Language Edition Version Clustered
None None None None None None None None