Gabriel Guimarães's questions

an application needs to have IIS on the same box of Reporting Services.

Today SSRS and SQL stay on the same clustered box.

I need to evaluate the options which for me are:

1. Installing IIS on both Cluster nodes
2. Installing IIS and Reporting Services on another machine

I need to find out what are the bad and good points of having IIS along with SQL

And the bad and good points of having the other way.

I know price is high if having another machine (or vm) just with reporting services and IIS.

But what are the bads of having IIS on a SQL machine.

PS: All of this is on my intranet, not exposed.

I have a SSRS Data Driven Subscription, the reports are save to a folder, I use the Overwrite Write Mode, however there are old reports that aren't generated anymore that lingers on the folder, I would like to clear the destination folder before running my subscription is this possible?

If No what is my alternative?

Thanks

I'm looking for the right replication for my case:

I can't use P2P replication because one of my SQL's is Standard. I have 2 SQL 2008 R2 and want to create a bi-directional replication.

What are my options?

Is a "always on" Merge replication functional?

When I'm adding one procedure article to the Publication the I get this error:

The text data type cannot be selected as DISTINCT because it is not comparable

the proc is a simple

select distinct field1, field2, fieldn from tablex where id = @param1

The database is on compatibility level 80.

The source server is SQL 2008 R2.

Any help is appreciated. Thanks, Gabriel Guimarães

Hi I would like to block access to insert any data on SQL when a User from a determined Active Directory Group access SQL from MS ACCESS.

Is this possible? if not is there any alternative?

I've tried a server trigger, and was able to prevent access to sql from someone by its IP, and the program name. But I would like to prevent inserts, is there a way to do this?

thanks, Gabriel

Hi I Currently have two clusters (W2K8R2) with SQL 2K8R2, both with MSDTC cluster service enabled.

I've configured the MSDTC with all checkboxes, and Incoming Caller Authentication Required and NT AUTHORITY\NetworkService as the DTC Logon Account.

I've also configured the Registry on all cluster nodes of both cluster to restrain the Ports to 5000-5100 (from this KB article http://support.microsoft.com/kb/250367)

The machines are not on the same domain, but there is a trust relationship, there's a firewall between them, and ports 1433, 1434, 135 and 5000-5100 are allowed between machines on the firewall, however it doesn't seem that these ports (5000-5100) are being used, when I try to insert data on a temp table from a remote proc I get an "No transaction is active." If I run DTCTEST I get: The transaction has already been implicitly or explicitly commited or aborted.

I'm getting nuts, any help is appreciated. Thanks, Gabriel

Hi I've a server with 2 physical processors with 8 cores each.

Looking through Management Studio Shows me a tree with NUMA 0 and NUMA 1 and inside 8 childs.

The current maxdop is on 0, should I set a different maxdop for the server? If so this should be 2 or 16? Should MAXDOP be based on physical or logical processors count?

Thanks, any help is appreciated. Gabriel

First I'm aware that Reporting Services is not Cluster aware, But I would like to install it on my each node of my 2 node Cluster on the default instance.

I've got the following error installing

Rule Check Result

Rule "Existing clustered or cluster-prepared instance" failed.

The instance selected for installation is already installed and clustered on computer SQL-1. To continue, select a different instance to cluster.

OK

Is there a way to install it without a named instance?

I'm trying to export a report to PDF with a custom font.

I have installed a font on the server that hosts Reporting Services 2008 R2, restarted the SSRS service, and sucessfully seen it using the web frontend, exported it to Excel, however on PDF the font is not there.

On the Acrobat PDF file If I click on File -> Properties -> Fonts. I'm presented with a screen that shows me a list of fonts on the PDF.

There's an "a" icon with Helvetica-BoldOblique

Type: Type 1

Encoding: Ansi

Actual Font: Arial-BoldItalicMT

Actual Font Type: TrueType

The second one is a double T icon with the font I'm using and a (Embedded Subset) sufix.

Its a True Type font and ANSI encoded.

However the text is not using this embeded font, If I select the text and copy to a word document (I have the font installed) I can see the text in the font, however not on PDF, what's wrong here?

Here's how the Screen looks:

Hi I've just installed Full SQL 2005, however after I've configured Reporting Services I can't seem to access it.

When I go to http://localhost/reports I get this prompt for a username and password and no mather what I put there it fails with 401.1 Access Denied.

I've disabled kerberos on Reports and ReportServer virtual directories, nothing changed.

I've enabled anonymous auth and got: Internet Explorer cannot display the webpage.

When I access from another machine, I get the user and password prompt only once and get the same error.

If I try to access with Management Studio i get this error:

TITLE: Microsoft SQL Server Management Studio

The underlying connection was closed: An unexpected error occurred on a receive. (Microsoft.SqlServer.Management.UI.RSClient)

---

ADDITIONAL INFORMATION:

Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host. (System)

---

An existing connection was forcibly closed by the remote host (System)

---

BUTTONS:

OK

Server is Win 2003 R2 Standard with IIS 6

UPDATE: Reporting Services Configuration Manager is all green, I've configured it completely, all accounts are domain accounts, the SQL database server is the same server, the databases are created successfully, if I add a index.html page on Reports virtual directory (directory) I can access it normally (with integrated security enabled).

UPDATE2: I've re-downloaded SQL 2005 from microsoft site. Uninstalled, deleted the remaining folders, Re-installed and I'm still stuck with the same error. I got the KRB_AP_ERR_MODIFIED Kerberos Error on System eventviewer after I reinstalled...

UPDATE3: I've downloaded SQL 2005 SP3 and have applied it to SQL, and still have the same problem. I also noted that on Surface Area Configuration when I click Reporting Services Feature Windows Integrated Security I get this error:

TITLE: Surface Area Configuration

The underlying connection was closed: An unexpected error occurred on a receive. (SQLSAC)

---

ADDITIONAL INFORMATION:

Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host. (System)

---

An existing connection was forcibly closed by the remote host (System)

---

BUTTONS:

OK

I Found this link relating the above error and will try to reinstall IIS.

Can't seem to understand this. Does anyone have a hint?

UPDATE 4: Solved reinstalling IIS with the correct files (thank god SQL 2008 doesn't use IIS anymore).

UPDATE I have a SCOM 2007 R2 server that uses a sql 2005 server.

Machine 1: SCOM (SCOM 2007 R2 - W2K3) Machine 2: SQL2005 (SQL 2005 SP2 - W2K3) SQL Cluster: SQLCLUSTER (SQL 2008 R2 on Cluster - W2K8R2) Reporting Services is on one of the cluster machines as it's not cluster aware.

I Currently use SQL2005 for OperationsManager, OperationsManagerDW db, and Reporting services.

I need to know how to migrate All this (Including Reporting Services) to the cluster.

Reinstalling is not an option since I don't know how to do this, and don't want to lose data.

Microsoft released the SCOM 2007 R2 CU 4 that does support Migration to SQL 2008 R2.

I Found this KB from MS that points to this KB that does suport SQL 2008 R2: and I've successfully updated SCOM 2007 R2 to CU 4.

I've found this tutorial on updating SCOM 2007 R2 to CU 4 and followed it:

This KB says: The feature for SQL Server 2008 database update support lets you upgrade a database for SCOM 2007 R2 to SQL Server 2008 R2. The tools for this feature are in the SupportTools folder of the update package. For more information about how to upgrade a database from SCOM 2007 R2, visit the following Microsoft TechNet article: http://technet.microsoft.com/en-us/library/dd789004.aspx

In the following upgrade scenarios, follow the steps that are described on the "How to update a database from SCOM 2007 R2" TechNet website: SQL Reporting Services 2005 to SQL Reporting Services 2008 Service Pack 1 (SP1) SQL Reporting Services 2008 Service Pack 1 (SP1) to SQL Reporting Services 2008 R2

So I guess I have to upgrade to SQL 2008, and then I'll be able to migrate to SQL 2008 R2, however I already have a SQL 2008 R2 Cluster and want to simply add this databases and reporting services there, if anyone have info on this I would gladly apreciate it.

Thanks

I recently saw that when I issue

select DB_NAME(0) 

there is some answer, and when I check sys.databases there is no DB ID 0, so I think there should be a NULL return. If I select DB_NAME(1000) and there is no db id 1000 the return is NULL.

I noticed that the database I'm currently using is always displayed.

Is this normal? is this a bug or something?

Is there a way to check when was the last integrity check run for each database?

I know when the service startup it checks for these values, so they exist, and they are probably writen on the database header information.

By the way I would like an T-SQL solution.

I'm using Win 2K3 R2 + SP2 with SQL 2K8 R2 installed.

I'm trying to set SQL Agent History to keep 1 week of logs. So here's what I do: After I connect, I right click SQL Server Agent and select Properties. After that I select History. Then I observe that no checkboxes are selected. I Then select the second checkbox (Remove Agent history:). And change the value of weeks to 1. And click OK.

After that I reopen to see that nothing was altered.

If that's not enough sometimes when I try to see the history of some particular jobs it timeouts.

I appreciate any help.

Thanks @Pradeep I've added two steps to my daily maintenance job here's the script for the tasks.

For purging the Backup History (optimizes backup speed)

declare @lastweek date;
select @lastweek = CONVERT(DATE,DATEADD(WEEK,-1,GETDATE()));
EXEC MSDB.DBO.SP_DELETE_BACKUPHISTORY @oldest_date = @lastweek;

For purging Job history I had to use a cursor.

declare @lastweek date
select @lastweek = CONVERT(DATE,DATEADD(WEEK,-1,GETDATE()));

DECLARE jobs_cursor CURSOR
FAST_FORWARD
FOR select job_id from msdb.dbo.sysjobs
DECLARE @job_id UNIQUEIDENTIFIER
OPEN jobs_cursor

FETCH NEXT FROM jobs_cursor INTO @job_id
WHILE (@@fetch_status <> -1)
BEGIN
    IF  (@@fetch_status <> -2)
    BEGIN
        EXEC msdb.dbo.sp_purge_jobhistory @job_id = @job_id, @oldest_date = @lastweek;
    END
    FETCH NEXT FROM jobs_cursor INTO @job_id
END

CLOSE jobs_cursor
DEALLOCATE jobs_cursor
GO

Hope this helps if anyone also needs this.

I will configure Log Shipping for more than 1 database on single a server. And I wan't to know If I can use a single Shared Folder or if I have to create a Folder For Each Database.

What I mean is:

• Does SQL know what backups to copy If I use a Shared Folder?
• How about the copy destination, can it be the same for more than one database?

How do I know the directories independently of Instance Name, cluster Name or default instance?

**UPDATE: ** Very important you can't read registry data with a nvarchar(MAX) only with a valued nvarchar like for example nvarchar(4000). I didn't know that so my reads was always failing...

**UPDATE2: ** On clusters the Default Data Directory is located on the

Software\Microsoft\MSSQLServer\Setup

registry on the SQLDataRoot Key.

**UPDATE3: ** For remote Server use linked server!

This is the final Script:

declare @datadir nvarchar(4000)
        ,@logdir nvarchar(4000);

EXEC master.dbo.xp_instance_regread 
    N'HKEY_LOCAL_MACHINE'
    , N'Software\Microsoft\MSSQLServer\MSSQLServer'
    , N'DefaultData'
    , @datadir output;

IF @datadir IS NULL
BEGIN
EXEC master.dbo.xp_instance_regread 
    N'HKEY_LOCAL_MACHINE'
    , N'Software\Microsoft\MSSQLServer\Setup'
    , N'SQLDataRoot'
    , @datadir output;
END
EXEC master.dbo.xp_instance_regread 
    N'HKEY_LOCAL_MACHINE'
    , N'Software\Microsoft\MSSQLServer\MSSQLServer'
    , N'DefaultLog'
    , @logdir output;

SELECT @datadir as Data, ISNULL(@logdir,@datadir) as Log;

Hi I have a Win 2K3 with 6 gigs of memory.

I had 3 gigs, and then added 3 more.

However Windows won't recognize more than 4 Gigs as its a 32 bits machine.

What do I have to do to make things work?

I've two sql instances and set SQL Maximum memory for them both as 2560, and clicked Enable AWE checkbox.

Is this enough? I've heard it's actually not, windows should recognize the memory first. How do I do that? Is Min Memory required to be more than 0 for AWE to work?

Thanks in advance. Gabriel Guimarães

I'm installing SQL Server 2008 R2 using a DVD that says 32/64onto a Windows Server 2008 R2 server. I want to install the 64-bit version of SQL Server 2008 R2. I am installing SQL Server with the failover cluster feature enabled and configured. However, when I run the SQL Server 2008 R2 setup file I get the following error.

The version of this file is not compatible with the version of Windows you're running. Check your computer's system information to see whether you need an x86 (32-bit) or x64 (64-bit) version of the program, and then contact the software publisher.

When I look at the Event Viewer I see the following error:

The program or feature "\??\D:\setup.exe" cannot start or run due to incompatibity with 64-bit versions of Windows. Please contact the software vendor to ask if a 64-bit Windows compatible version is available.

The SQL Server 2008 R2 installer is 32/64 bit and of course Windows Server 2008 R2 is 64 bits. What could be preventing the installation from proceeding?