SSRS: Format number with 2 decimals points

=Format(Sum(Fields!TOTALHABERES.Value, "Detalle"),"#,##0.00")

How to do number formatting to 1 decimal point in SSRS?
SSRS Expressions Tips and Tricks
Formatting Numbers and Dates

Recover SQL Server sa password

Response on StackOverflow.com

You may have forgotten «sa» password, you never got it, the provider didn’t give it to you, whatever.

But, You have Administrator privileges on Windows.

The steps are the following:

1. Go to SQL Server Configuration Manager.
2. Select SQL Server and STOP the service.
3. Right click on it, go to Properties.
4. On «Startup parameters», add the following at the beginning «-m;»
5. Start SQL Server service.
6. Now, you’re on Single User Mode.
7. Open a Command window (on Win7 make sure to Run as Administrator)
8. Type «SQLCMD» and hi ENTER.
9. Now you’re logged on SQL Server in Single User Mode, without any password.
10. Type: «CREATE LOGIN mynewuser WITH PASSWORD = ‘mynewpassword′;»
11. Type: «GO;»
12. Type: «sp_addsrvrolemember ‘mynewuser’, ‘sysadmin’;»
13. Type: «GO;»
14. Stop SQL Server service.
15. Right click it, go to Properties.
16. On «Startup parameters», remove the following from the beginning «-m;»
17. Start SQL Server service.
18. Open SQL Server Management Studio, login as «mynewuser» user.
19. Go to Security, Logins, and change «sa» user password.

References:

How to recover SA password on Microsoft SQL Server 2008 R2
Disaster Recovery: How to regain your lost sysadmin access
Starting SQL Server in Single-User Mode
Tips & Tricks: YOU HAVE LOST ACCESS TO SQL SERVER. NOW WHAT?
sqlcmd Utility

Repeat page headers in SSRS 2008 R2

1. Select your Tablix.
2. On the panel below, you will see Row and Columns Groups.
3. On the upper right corner, click and Select «Advanced Mode».
4. Now, on Row Groups, click on the first item «(Static»).
5. On its Properties, set «Keep with group» to TRUE and «Repeat on new page» to TRUE.

Done!

Further reading:

Katmai Reporting Services 2008 Tablix control Repeat Column Headers does not work

How to: Display Row and Column Headers on Multiple Pages (Report Builder 3.0 and SSRS)

Controlling Row and Column Headings (Report Builder 3.0 and SSRS)

I want to Repeat Page Header on each Page for reports.Repeat on NewPage is not appearing in properties of Tablix member in SSRS 2008

Repeating TABLE Header on each page of the report when you print the exported to excel report

Repeating Page Titles in SSRS 2008 R2

Tablix headers not repeating in SSRS 2008

Repeat Header / Keep Header Visible in Tables in RS 2008

Consume SSRS 2008R2 x64 report from ASP.NET app using Oracle ODAC11.2.0.1.0 on IIS 7.5 (Enabled 32 bit apps) on Win7 x64

On my development machine (Win7 x64, IIS 7.5, SSRS 2008R2 x64), I have an ASP.Net app (application pool with «Enable 32-bit apps» set to «True») connecting to an Oracle database server, using Oracle Data Provider for .Net 32 bits (ODAC 11.2.0.1.0, Oracle.DataAccess 2.112.1.0.). IIS 7.5 and SSRS 2008 R2 run on the same development machine. Oracle runs on a test server.

It works fine, except when I embed a report from SSRS 2008 R2, using ReportViewer, like this:

    reportViewer.ProcessingMode = ProcessingMode.Remote     reportViewer.ServerReport.ReportServerCredentials = New ReportServerCredentials()     Dim serverReport As ServerReport     serverReport = reportViewer.ServerReport     serverReport.ReportPath = "/" + ReportServerCredentials.ReportFolder + "/rptParteIngreso"     serverReport.ReportServerUrl = New Uri(ReportServerCredentials.urlServerReporting)     Dim parameters() As ReportParameter = {P_IDPAIS, P_FECHAINICIO, P_FECHAFIN, P_NROPARTE, P_FLAGAPROBING}     Try         serverReport.SetParameters(parameters)     Catch ex As Exception         MessageBoxs.Show(ex.InnerException.ToString())     End Try 

It throws the following error:

Microsoft.Reporting.WebForms.ReportServerException was unhandled by user code   ErrorCode=rsProcessingAborted   Message=Error al procesar el informe. (rsProcessingAborted)   Source=Microsoft.ReportViewer.WebForms   StackTrace:        en Microsoft.Reporting.WebForms.ServerReportSoapProxy.OnSoapException(SoapException e)        en Microsoft.Reporting.WebForms.Internal.Soap.ReportingServices2005.Execution.RSExecutionConnection.ProxyMethodInvocation.Execute[TReturn](RSExecutionConnection connection, ProxyMethod`1 initialMethod, ProxyMethod`1 retryMethod)        en Microsoft.Reporting.WebForms.Internal.Soap.ReportingServices2005.Execution.RSExecutionConnection.SetExecutionParameters(ParameterValue[] Parameters, String ParameterLanguage)        en Microsoft.Reporting.WebForms.ServerReport.SetParameters(IEnumerable`1 parameters) .....        en System.Web.UI.Control.OnLoad(EventArgs e)        en System.Web.UI.Control.LoadRecursive()        en System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)        ErrorCode=rsErrorOpeningConnection        Message=Cannot create a connection to data source 'DSRRHH'. (rsErrorOpeningConnection)        InnerException: Microsoft.Reporting.WebForms.ReportServerException             Message=El intento de cargar las bibliotecas de clientes de Oracle lanzó BadImageFormatException. **Este problema ocurrirá al ejecutar el modo 64 bits con los componente cliente de Oracle de 32 bits instalados**.             InnerException: Microsoft.Reporting.WebForms.ReportServerException                  Message=Se ha intentado cargar un programa con un formato incorrecto. (Excepción de HRESULT: 0x8007000B) 

The relevant message I think is «This problem will occur when running in 64 bit mode with the 32 bit Oracle client components installed.»

Also, on the Task Manager, I’ve checked Reporting Services is running as a 64-bit process(ReportingServicesService.exe) and my app is running on 32 bits (w3wp.exe *32).

However, when designing the report, it works OK, using «.Net Framework Data Provider for Oracle», which I think is provided by Microsoft.

So, my conclusion is there’s a conflict between my ASP.Net app (running as 32-bit) and my reports published on a SSRS 2008 R2 x64 server.

So, my question is, how can I embed those SSRS reports in my ASP.Net app?

Some alternatives I’ve thought:

1. Diable «Enable 32-bit apps» on application pool on IIS, so it will run as 64-bits.
2. Change SSRS process to run as 32-bits. (Is this even posible?)

Also, I’ve published the reports to another server (SSRS 2008 R2 x64 on Win 2008 R2 x64) and I can embed them without any problem from my ASP.Net 32-bits app on my development machine.

Any help would be greatly appreciated.

 

UPDATE:

Solved. Installed Oracle Data Provider for .Net 64 bits (ODAC 11.2.0.1.0, Oracle.DataAccess 2.112.1.0.) and disabled «Enable 32-bit apps» on application pool on IIS, so it will run as 64-bits. This way, it can connects to SSRS 2008 R2 64 bits.

SQL 2008R2 Linked Servers

I want to connect Server A with Server B, so I can query a table on a database on Server B from a database on Server A. This is called a «distributed query»

1. Login as «sa» into SQL Server on Server A.
2. Go to Server Objects/Linked Servers.
3. Right click, New Linked Server.
4. Note: If linked server is of type SQL Server, then the linked server name is also the network name of the server.
5. Enter Linked server name: «Server B» network name (or IP address). If your SQL Server is a named instance, it would be «SERVER_NAME\INSTANCE_NAME» or «SERVER_IP\INSTANCE_NAME»
6. Choose Server type: SQL Server.
7. Go to Security.
8. Click Add.
9. Local login: sa
10. Impersonate: not checked
11. Remote user: sa
12. Remote password: sa password on remote server
13. Click OK

Now you can write a distributed sql query from a database on Server A, like this:

select * from [linked_server_name].db_name.schema_name.table_name

However, the setup describe above is not the most secure. Use of  «sa» login shoul be disabled on both servers. It’s recommended that you create a login for each database, with the minimum permissions required, and use this login as remote user (11) and remote password (12) described above.

Of course, you can also do this using a SQL script (sp_addlinkedserver)

References:

Linking Servers

Accessing External Data

Identifying a Data Source by Using a Linked Server Name

sp_addlinkedserver (Transact-SQL)

SQL Server: disable Windows authentication

Actually, this is not completely possible.

There are two authentication modes for SQL Server (2008R2):

1. Windows Authentication: stronger security, Kerberos protocol.
2. Mixed Mode (Windows and SQL Server): use of «sa» login. Less secure. Best option for web applications.

I’ve got a web server (IIS) andwhen I log into Windows (2008R2), if I open SQL Server, Windows Authentication is selected by default and it lets me access without any password.

This is because, by default, the Windows administrators group is assigned the «sysadmin» server role. We can remove them from this server role or remove completely the group from the SQL Server logins.

So, I did the following:

1. Access SQL Server as «sa» user.
2. Go to Security/Logins
3. Right click on the BUILTIN\Administrators login or PC-NAME\USER-NAME login.
4. Choose Properties
5. Go to Server Roles
6. Uncheck «sysadmin» role
7. Make sure it has only the «public» role selected.
8. Click OK

This way, Windows administrator can access SQL Server but without permissions to modify anything.

However, «sa» user can still access SQL Server with full privileges.

Of course, it’s not recommended to use «sa» login for any application (ASP.Net).

You better create a specific login for each application, with just the needed permissions to access its database.

References:

How to Disable Windows Authentication

Authentication in SQL Server (ADO.NET)

SQL SERVER – Disable Windows Authentication – Remove Windows Authentication Login Account

 

Powershell: compress backups and FTP transfer

I use this snippet to check my database backups folder for backup files not compressed yet, compress them using 7-Zip, and finally deleting the «*.bak» files to save some disk space. Notice files are ordered by lenght (smallest to biggest) before compression to avoid some files not being compressed.

$bkdir = "E:\BackupsPWS" #backups location directory
$7Zip = 'C:\"Program Files"\7-Zip\7z.exe' #compression utility
$files_to_transfer = New-Object System.Collections.ArrayList #list of zipped files to be transferred over FTP
$ftp_uri="myftpserver"
$user="myftpusername"
$pass="myftppassword"

#find .bak files not zipped yet, zip them, add them to the list to be transferrd
get-childitem -path $bkdir | Sort-Object length |
where { $_.extension -match ".(bak)" -and
-not (test-path ($_.fullname -replace "(bak)", "7z")) } |
foreach {
$zipfilename = ($_.fullname -replace "bak", "7z")
Invoke-Expression "$7Zip a $zipfilename $($_.FullName)"
$files_to_transfer.Add($zipfilename)
}

#find .bak files, if they've been zipped, delete the .bak file
get-childitem -path $bkdir |
where { $_.extension -match ".(bak)" -and
(test-path ($_.fullname -replace "(bak)", "7z")) } |
foreach { del $_.fullname }

#transfer each zipped file over FTP
foreach ($file in $files_to_transfer)
{
$webclient = New-Object System.Net.WebClient
$webclient.Credentials = New-Object System.Net.NetworkCredential($user,$pass) # FTP credentials
$ftp_urix = $ftp_uri + "/" + $file.Substring($bkdir.Length + 1) # ftp address where to transfer the file
$uri=[system.URI] $ftp_urix
$webclient.UploadFile($uri, $file) #transfer the file
}

Powershell: compress files using 7-Zip

This post shows you how to compress a set of files (*.bak) in a directory, using a Powershell script and 7-Zip.

Later, delete the original files and leave only the compressed ones.

Save the script with «.ps1» extension and fire it from a Powershell command line, from a «.bat» file or set it as a Windows scheduled task.

$bkdir = "E:\BackupsPWS"
$7Zip = 'C:\"Program Files"\7-Zip\7z.exe'
get-childitem -path $bkdir | Sort-Object length |
where { $_.extension -match ".(bak)" -and
-not (test-path ($_.fullname -replace "(bak)", "7z")) } |
foreach {
$zipfilename = ($_.fullname -replace "bak", "7z")
Invoke-Expression "$7Zip a $zipfilename $($_.FullName)"
}
get-childitem -path $bkdir |
where { $_.extension -match ".(bak)" -and
(test-path ($_.fullname -replace "(bak)", "7z")) } |
foreach { del $_.fullname }

References:

Compressing SQL Server Backups With Windows PowerShell and 7-zip

Calling 7-Zip from powershell

Powershell: backup and compress SQL databases

The following Powershell script code backups all databases for a specific SQL Server and then compress those files (*.bak) into a ZIP file.

The last line is very important, because it lists the «.bak» files in the directory, ordered by length (ascending), so the compression process doesn’t get interrupted.

Lately, you can call this script from a BAT file and set it up as a scheduled task, so, let’s say, backup all your databases from your server everyday at midnigth, and compress them to a zipped file. Next morning when you arrive to your office, you can download it. Even more, you can schedule it to download it automatically to an FTP server at your data center.

$assemblyInfo = [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")
if ($assemblyInfo.GetName().Version.Major -ge 10)
{
# sql server version is 2008 or later, also load these other assemblies
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMOExtended') | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SQLWMIManagement') | Out-Null
}

$s = new-object ("Microsoft.SqlServer.Management.Smo.Server") "(local)" #this can also be "SERVERNAME\INSTANCENAME"

$bkdir = "E:\Backups" #We define the folder path as a variable
$dbstobackup = @(“DB1", “DB2", “DB3")

$dbs = $s.Databases
foreach ($db in $dbs)
{
if($dbstobackup -contains $db.Name)
{
$dbname = $db.Name
$dt = get-date -format yyyyMMddHHmm
$dbBackup = new-object ("Microsoft.SqlServer.Management.Smo.Backup")
$dbBackup.Action = "Database"
$dbBackup.Database = $dbname
$dbBackup.Devices.AddDevice($bkdir + "\" + $dbname + "_db_" + $dt + ".bak", "File")
$dbBackup.SqlBackup($s)
}
}

# Create a new zip file from pipeline
function Create-Zip()
{
param
(
[string]$zipFile
);

New-Zip -zipfileName $zipFile
$zip = Get-Zip -zipfileName $zipFile

#loop through files in pipeline
foreach($file in $input)
{
#add file to zip and sleep 1/2 second
$zip.CopyHere($file.FullName)
Start-sleep -milliseconds 15000
}

}

#create a new zip file
function New-Zip
{
param([string]$zipfilename)
set-content $zipfilename ("PK" + [char]5 + [char]6 + ("$([char]0)" * 18))
(dir $zipfilename).IsReadOnly = $false
}

#get the zip file
function Get-Zip
{
param([string]$zipfilename)
if(test-path($zipfilename))
{
$shellApplication = new-object -com shell.application
$zipFullFilename = (get-childitem $zipfilename).FullName
$shellApplication.NameSpace($zipFullFilename)
}
}

$dtzip = get-date -format yyyyMMddHHmm
$zipfilename = $bkdir + "\DB_Backups_" + $dtzip + ".zip"
Get-ChildItem -path $bkdir -filter *.bak* | Sort-Object length | create-zip $zipfilename

References:

Backup SQL Server Databases with a Windows PowerShell Script

Loading SMO Assemblies into PowerShell

PowerShell Tips

Windows 2008/ IIS7.5 / Classic ASP/ ASP.Net intermittent 403 errors

We’ve just set up a new dedicated server at some hosting provider, running Windows 2008 Server R2 and IIS7.5.

Sometimes the site works, sometimes, part of the site runs and I can see a 403 error in Firebug. At other times, I only get an IIS error page saying:

«403 – Forbidden: Access is denied. You do not have permission to view this directory or page using the credentials that you supplied.»

This information was not enough to troubleshoot this problem so I checked IIS log files for HTTP STATUS CODE 403.  I found some lines like this:

#Fields: date time s-ip cs-method cs-uri-stem cs-uri-query s-port cs-username c-ip cs(User-Agent) sc-status sc-substatus sc-win32-status time-taken

2011-04-26 14:03:41 xx.xx.xx.xx GET /Calificaciones/Calificaciones.aspx – 80 – yy.yy.yy.yy Mozilla/4.0+(compatible;+MSIE+7.0;+Windows+NT+5.2;+WOW64;+.NET+CLR+2.0.50727;+.NET+CLR+3.0.04506.30;+.NET+CLR+3.0.04506.648;+.NET+CLR+3.0.4506.2152;+.NET+CLR+3.5.30729) 403 502 0 249

So I got HTTP STATUS CODE 403, SUB STATUS CODE 502. Searching for this on Google I found this page:

Server – Windows 2K8/IIS7/ColdFusion throwing intermittent 403 errors

This gave me some light on the problem and I began to search for IP filtering on IIS configuration settings.

1. Open ISS Manager.

2. Go to your website.

3. Go to  Features/IIS/Dynamic IP Restrictions.

Source: IIS.Net: The Official Microsoft IIS Site

4. Set the appropiate values or disable restrictions. In my case, I doubled former values and it’s working like a charm rigth now.

REFERENCES:

Dynamic IP Restrictions