Archive for the ‘SQL Server 2005’ Category

SQL Server 2005 SP3 and above is supported on Windows 7. Trying to install this I ran into the catch 22 situation: it won’t let me install SQL Server as only SP 3 and over is supported. I can’t install the service pack until SQL is installed. You can have hours of fun like this.

You may be able to get a SQL Server 2005 disc with SP3 applied but I couldn’t find one. What I did find was that it let me install developer edition. After that I installed SP4. Then I upgraded to Enterprise.

That all sounds too easy…

Of course it didn’t let me install developer edition just like that. It had to fail a couple of times first. The failure reported that SQLNCLI.msi was missing. It was there so I tried again. It failed. I then did some searching and found that this was because an earlier version of the client was installed. I went to add remove programs and removed it. After that the install went through OK.

This link will take you to my previous post on upgrading SQL Server 2005

Advertisements

The Problem

A data import failed with the following error messages:

  • Failed to retrieve long data for column “Description”.
  • There was an error with output “OLE DB Source Output” (742) on component “OLE DB Source” (732). The column status returned was: “DBSTATUS_UNAVAILABLE”.
  • SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The “output “OLE DB Source Output” (742)” failed because error code 0xC0209071 occurred, and the error row disposition on “component “OLE DB Source” (732)” specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.
  • SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component “OLE DB Source” (732) returned error code 0xC0209029. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.

The column description is of the data type VarChar(Max). This error will normally occur when the data type is some form of BLOB

The Solution

When SSIS Imports BLOB data it writes the data our to a file and then imports the file. By default it will use the system temp directory for this. However you can set two directory locations for Blob data and system cache. It is recommended that you use fast disks and that these two directories are on separate disks.

Click on the Data Flow Task and view the properties. You need to enter values for two options: SSISBlobTempStoragePath and BufferTempStoragePath. I created directories with those names on the E and L drives.

Blob Path Config Setting

After making this change the data task ran in 5 minutes rather than 8. More importantly it shouldn’t fail in the future

Pivot or Sum

Posted: August 2, 2010 in Pivot, SQL Server 2005, TSQL

A developer was working on a report. The data was originally in a SQL 2000 Server database. The data being used was then transferred into a SQL 2005 database. The original report used SUM and Group BY. Now it was in 2005 Pivot could be used. He wrote a new script and compared results. His question give the same results, is it wrong to assume that SQL Pivot newness means betterness?

Here are the two queries:

Select [Reviewed] + [Logged] as [Queue], [Open] As Development, [Testing], [Pending Imp],
[Open] + [Testing] + [Pending Imp] as [Total In Progress],
[Reviewed] + [Logged] + [Open] + [Testing] + [Pending Imp] as [Grand Total]
from (Select [state] FROM dbo.tblCombinedSnapshto) Datatable
Pivot
(
Count([state])
For [state]
In ([Reviewed], [Logged], [Open], [Testing], [Pending Imp])
) Pivottable;

Select Sum(case when [state] = ‘Reviewed’ or [state] = ‘Logged’ then 1 else 0 end) as [Queue],
Sum(case when [state] = ‘Open’ then 1 else 0 end) as Development,
Sum(case when [state] = ‘Testing’ then 1 else 0 end) as Testing,
Sum(case when [state] = ‘Pending Imp’ then 1 else 0 end) as [Pending Imp],
Sum(case when [state] = ‘Open’ or[state] = ‘Testing’
or [state] = ‘Pending Imp’ then 1 else 0 end) as [Total In Progress],
Count(*) as [Grand Total]
from dbo.tblCombinedSnapshto;

The results were:

PIVOT timing
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 38 ms.
IO Table ‘tblCombinedSnapshto’. Scan count 1, logical reads 8, physical reads 1, read-ahead reads 48, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

2000 timing
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 50 ms.
IO Table ‘tblCombinedSnapshto’. Scan count 1, logical reads 8, physical reads 1, read-ahead reads 48, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

So for disk usage they were equal. For speed Pivot wins out.

Only a small scale test but worth doing now and then.

Table diff will let you compare two tables and generate a change script to sync them up.

By default it is installed here:

“C:\Program Files\Microsoft SQL Server\90\COM\tablediff.exe”

In this example I am comparing two tables in the same database called Live_Support. The tables are LF29699_Orig and LF29699_New.

I have created a batch file called comapretables.cmd.

The contents of the table looks like this:

“C:\Program Files\Microsoft SQL Server\90\COM\tablediff.exe” -sourceserver sqlmgmt1 -sourcedatabase Live_Support -sourcetable LF29699_Orig -destinationserver sqlmgmt1 -destinationdatabase colt_Live_Support -destinationtable LF29699_new -f c:\LF29699_diff.sql

Execute this and you get a file c:\LF29699_diff.sql which is a change script for that table:

— Host: sqlmgmt1

— Database: [Live_Support]

— Table: [dbo].[LF29699_new]

SET IDENTITY_INSERT [dbo].[LF29699_new] ON

UPDATE [dbo].[LF29699_new] SET [GroupIndex]=3 WHERE [pkid] = 3

UPDATE [dbo].[LF29699_new] SET [GroupIndex]=2 WHERE [pkid] = 683

SET IDENTITY_INSERT [dbo].[LF29699_new] OFF

You can see from the script that there are 2 differences between the tables. It does not tell you what the values in the table are currently; you do have the primary key value though so you can look it up.

The two tables you compare will need to have primary keys defined on them.

BOL tableDiff.Exe: ‘http://msdn.microsoft.com/en-us/library/ms162843.aspx

Moving System Databases in SQL Server 2005

Notes

Tempdb is built when ever the sql server restarts so you tell it where you want the database then restart sql and it will build the database there. You will then need to delete the old files.

In SQL 2005 the resource database log and data file have to go into the same directory as the master data file.

sp_helpfile will show you where the database files are – good to run after the change to check they are where you think they are.

Move Tempdb

Run the script below using the correct path for the server:

USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = ‘F:\SQLLogs\templog.ldf’);
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = ‘E:\SQLData\tempdb.mdf’);
GO

For this next step you can wait and do the stop /start for msdb
Next stop Sql Server
Restart SQL Server
Go back and delete the old files

Move Model and MSDB Files

First run this – with the filename being the path required

ALTER DATABASE Model MODIFY FILE (NAME = modellog, FILENAME = ‘F:\SQLLogs\modellog.ldf’);
Go
ALTER DATABASE MSDB MODIFY FILE (NAME = MSDBLog, FILENAME = ‘F:\SQLLogs\msdblog.ldf’);
Go
ALTER DATABASE Model
MODIFY FILE (NAME = modeldev, FILENAME = ‘E:\SQLData\model.mdf’);
Go
ALTER DATABASE MSDB
MODIFY FILE (NAME = MSDBData, FILENAME = ‘E:\SQLData\msdbdata.mdf’);
go

Stop Sql Server
Move the files
Restart SQL Server

Move Master and Resource Log

1. From the Start menu, point to All Programs, point to Microsoft SQL Server 2005, point to Configuration Tools, and then click SQL Server Configuration Manager.

2. In the SQL Server 2005 Services node, right-click the instance of SQL Server (for example, SQL Server (MSSQLSERVER)) and choose Properties.

3. In the SQL Server (instance_name) Properties dialog box, click the Advanced tab.

4. Edit the Startup Parameters values to point to the planned location for the master database data and log files, and click OK. Moving the error log file is optional.

The parameter value for the data file must follow the -d parameter and the value for the log file must follow the -l parameter.

5. Stop the instance of SQL Server by right-clicking the instance name and choosing Stop.

6. Move the master.mdf and mastlog.ldf files to the new location.

7. Start the instance of SQL Server in master-only recovery mode by entering one of the following commands at the command prompt. The parameters specified in these commands are case sensitive. The commands fail when the parameters are not specified as shown.

1. For the default (MSSQLSERVER) instance, run the following command.

NET START MSSQLSERVER /f /T3608

2. For a named instance, run the following command.

NET START MSSQL$instancename /f /T3608

8. Using sqlcmd commands or SQL Server Management Studio, run the following statements. Change the FILENAME path to match the new location of the master data file. Do not change the name of the database or the file names.

Save the script into a file called c:\move.sql (make sure the paths are right for your server NOTE the ldf and mdf go to the same location as the master mdf

ALTER DATABASE mssqlsystemresource MODIFY FILE (NAME=log, FILENAME=
‘E:\SQLData\mssqlsystemresource.ldf’);
ALTER DATABASE mssqlsystemresource MODIFY FILE (NAME=data, FILENAME=
‘E:\SQLData\mssqlsystemresource.mdf’);

Now run sqlcmd
“C:\Program Files\Microsoft SQL Server\90\tools\binn\SQLCMD.EXE” -E -S . -d master /i c:\move.sql

1. Move the files to the new location.

2. Set the Resource database to read-only by running the following statement.

ALTER DATABASE mssqlsystemresource SET READ_ONLY;

1. Stop the instance of SQL Server.

2. Restart the instance of SQL Server using the sqlcmd this time without the flags /f /t3608

3. Check file and log locations:

SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files

Moving System Databases in SQL Server 2000
Moving the Master Database

The location of the master database and its associated log can be changed from within SQL Server Enterprise Manager. To do this:

Open SQL Enterprise Manger and drill down to the proper database server.

Right-click the SQL Server in Enterprise Manager and click Properties.
Click the Startup Parameters button and you will see something similar to the following entries:

-dC:\MSSQL\data\master.mdf
-eC:\MSSQL\log\ErrorLog
-lC:\MSSQL\data\mastlog.ldf

Change these values as follows:

Remove the current entries for the Master.mdf and Mastlog.ldf files.

Add new entries specifying the new location:

-dE:\SQLData\master.mdf
-lL:\SQLLogs\mastlog.ldf

Stop SQL Server.

Copy the Master.mdf and Mastlog.ldf files to the new locations

Restart SQL Server.

Moving MSDB and Model

When you are using this procedure to move the msdb and model databases, the order of reattachment must be model first and then msdb.

To move the MSDB follow these steps:

In SQL Server Enterprise Manager, right-click the server name and click Properties. On the General tab, click Startup Parameters.

Add a new parameter as -T3608. Select OK to close the Startup Parameters and the Properties page. You will not be able to access any user databases at this time. You should not perform any operations other than the steps below while using this trace flag.

Drill down to the msdb database and then right click on it. Select Properties and then select the Options tab.

Select Restrict access and then Single User. Close the Properties sheet by selecting OK.

Stop and then restart SQL Server.

Open SQL Query Analyzer and then detach the msdb database using the following commands:

use master
go
sp_detach_db ‘msdb’
go

Move the Msdbdata.mdf and Msdblog.ldf files from the current location to the new location.

Reattach the MSDB database as using the following commands:

use master
go
sp_attach_db ‘msdb’,’E:\SQLData\msdbdata.mdf’,’L:\SQLLogs\msdblog.ldf’;
go
Remove the -T3608 trace flag from the Startup Parameters box in the SQL Enterprise Manager.

Stop and then restart SQL Server.
Moving Tempdb

*** This is the same as the SQL 2005 method ***

Run the script below using the correct path for the server:

USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = ‘F:\SQL_Logs\templog.ldf’);
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = ‘E:\SQL_Data\tempdb.mdf’);
GO

Next stop Sql Server
Restart SQL Server
Go back and delete the old files

This assumes a user does not have Content Manager as their SSRS role and SSRS is set up more or less as it comes out of the box.

The Problem

If a user hits subscribe on a report then the chooses email as the delivery option they will see that the To field has been pre-populated with their windows login. They will not have the permissions need to change this setting. This is fine if you are running Exchange as it will resolve the login against the Active Directory and work out the real email address.

If you are not running Exchange it is not so fine.

The fix

You need to make a change to the rsreportserver.config file.

This should be under C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer by default – if you installed SQL somewhere else then the first part of this path will be wrong.

Open up this file and look for a value <sendemailtouseralias> under <rsemaildpconfiguration>. If this is set to True, as it is by default, you will get the problem above. If it is set to False the To field will be blank and the user can enter an email address.

I ran a query which resulted in this:
An error occurred while executing batch. Error message is: The directory name is invalid.

The reason for this was an invalid temp directory. The system was pointing at c:\temp but the directory did not exist. I re-pointed all the temp and tmp environment variables at the real temp directory and all was well again.