You are browsing archives for
SQL Server / Microsoft SQL Server / MSSQL
Or rather, why is there no “undo” in when working with Integration Services packages? This has been something that has me puzzled me since working with Beta 2 of Integration Services. I guess I figured it would be fixed by RTM, and just worked around the issue by regularly checking in finalised changes, and saving […]
I’ve been asked plenty of times, how do you reset an IDENTITY column? After inserting data into a table, and then deleting everything, how do I make the IDENTITY to start from 1 again? Personally I think is a bad idea to reset identity columns to any value lower than they currently are. To find […]
Joe Salvatore called out for help on an interesting use of the SSIS Lookup component: Can you lookup a row using a value within a range? Joe specified his criteria as: DataStagingSource.ModifyDate < DataWarehouseDimension.RowExpiredDate AND DataStagingSource.ModifyDate >= DataWarehouseDimension.RowEffectiveDate AND DataStagingSource.NaturalKey = DataWarehouseDimension.NaturalKey Easy! To show how it is done I’ve created a test database “SCRATCH” […]
Yesterday while trying to get OUTPUT parameters to work with SSIS Execute SQL Task I encountered a lot of problems, which I’m sure other people have experienced. BOL Help is very light on this subject, so consider this the lost page in help. The problem comes about because different providers expect parameters to be declared […]
If you want to be able to have your own template appear in Visual Studio when you add a new item to a project, this is where you will need to save it. Visual Studio 2005 Database Projects: C:\Program Files\Microsoft Visual Studio 8\Common7\Tools\Templates\Database Project Items Reporting Services 2005: C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\ ProjectItems\ReportProject Reporting […]
When SQL Server 2005 Management Studio’s Object Browser shows the SQL Server Agent service with a red down arrow and the text Agent XP’s disabled, the service is not started or disabled. Start the SQL Server Agent service by: using the SQL Server Configuration Manger located in Start -> Microsoft SQL Server 2005 -> Configuration […]
I was asked today how to select distinct columns while ordering by another column that does not appear in the column output list. For example: select distinct [name] from msdb.sys.columns order by system_type_id If you try to run this code, you will get the error message: Server: Msg 145, Level 15, State 1, Line 1 […]
Over the holiday break one of our servers suffered a disk failure in the main raid set. The disk was replaced and the raid set repaired. All seemed to be going well, until SQL Server wouldn’t start. Checking the latest ERRORLOG for SQL Server showed: 2005-12-28 10:44:26.17 spid3 Starting up database ‘master’. 2005-12-28 10:44:26.18 spid3 […]
Rebuilding the master database is done using the Rebuild Master utility “rebuildm.exe” which is located in the 80\Tools\Binn directory of the SQL Server installation. Select the server and instance that you want to rebuild. Now you are going to need access to the files from the install CD for SQL Server. Browse to the Data […]
Stop the SQL Server instance. And start it in single user mode. Open a command prompt window. Change into you SQL Server Binn directory for the instance you are repairing (the default is c:\Program Files\Microsoft SQL Server\MSSQL\Binn) and run: sqlservr.exe -c -m The -c option tells SQL Server to run independantly, and not as a […]
