How to fetch all numeric values from the string?

In this article I will show how to fetch all numeric values from a string.

I am creating a User defined function which will return all numeric values from the supplied string.

Function Code:

CREATE FUNCTION dbo.FetchNumeric
(@strAlphaNumeric VARCHAR(256))
RETURNS VARCHAR(256)
AS
BEGIN
DECLARE @intAlpha INT
SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric)
BEGIN
WHILE @intAlpha > 0
BEGIN
SET @strAlphaNumeric = STUFF(@strAlphaNumeric, @intAlpha, 1, '' )
SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric )
END
END
RETURN ISNULL(@strAlphaNumeric,0)
END

Example to Fetching Numeric Values from String:

SELECT dbo.FetchNumeric('jvhew832gj3hgj45')

I hope you enjoyed the article don't forget to share on Facebook. Please leave your comments below if there are any questions.

Error attaching MdxStepByStep.mdf for SQL Server 2008 R2

In This article I will show how to attach MDXStepByStep database. I have seen many people face problem when attaching the MDXStepByStep database using the attach_db.sql script.

When you execute attach_db.sql script it gives the below error.

Msg 5120, Level 16, State 101, Line 1

Unable to open the physical file "C:\Microsoft Press\MDX SBS\Setup\SQL Server\MdxStepByStep.mdf". Operating system error 5: "5(Access is denied.)".

Please follow the below steps to attach the MDXStepByStep database:

Step 1: Copy only MDXStepByStep.mdf file from “C:\Microsoft Press\MDX SBS\Setup\SQL Server” to “C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA”.

Step 2: Login to your SQL Server with your appropriate credentials, make sure your account must have permission to create database.

Step 3: Right Click on the Database and click on Attach, as shown below.

Step 4: Once you click on attach button it will open a new window. You have to click on add button and then select the MDXStepByStep.mdf file and click ok. You can find below the screenshot.

Step 5: You have added the MDXStepByStep.mdf file but MDXStepByStep.ldf file will be missing for this database because you didn’t copy that file in setp 1. In this step you have to remove the missing file.

Select MDXStepByStep.ldf file and click on remove button and click Ok as shown below.

 

Congratulations you have successfully attached the MDXStepByStep Database.

I hope you enjoyed the article don't forget to hit the Facebook Page Like Button. Please leave your comments below if there are any questions.

Sql Server Reporting Services (SSRS) Architecture

Reporting Services is one of the core component of MS SQL Server. Reporting Services is a server based reporting platform that provides reporting functionality to the user.Report Server is central component of Reporting Services. In this article we will try to understand how SSRS works in native mode deployment.

Reporting Services Report Server runs in one of two deployment modes:

1.       Native Mode

2.       Sharepoint Mode

You can’t switch Report Server mode from one to another  so user has to decide in which mode Reporting Services should be configured at the time of Reporting Services installation. Native mode is the default mode for Report server.

In Native mode Report Server is a stand alone application server that provides all viewing, processing and delivering of the reports and report model. Report Server is implemented as a Microsoft Windows Service called as Report Server.

Native mode reporting services Report Server 3 tier architecture is given below.

Report Server consists of a pair of Processing Engines and five special purposes of extensions which handle Authentication, Report Processing, Rendering, Data Processing and Delivery operation.

Let’s discuss each component from the above architecture.

Tier 3 (Client Application in Presentation layer)

User sends request to report server for Report Processing, Report Scheduling and Delivery etc from any of the client application present in presentation layer i.e Report Manager, Report Builder, Report Designer.

Tier 2 (Report Server Components)

1- Programmatic Interface:

Programmatic interface process all the requests sent to Report Server from the client application present in presentation layer. This includes requests from Report manager, Scheduling and Delivery Processor, Report Design Tools and Third party Tools. Programmatic Interface uses Internet Information Services (IIS) to receive the requests.

When a request arrives at the report server in the form of SOAP (Simple Object Access Protocol) and http request; Programmatic Interface interacts with the Report Server database in response to the request. When a report is requested the programmatic Interface initialize the Report Processor and when a Report Scheduling and Delivery is requested the programmatic Interface initialize the Scheduling and Delivery Processor.

2- Report Processor:

When a report is send from the presentation layer, it will reach to the programmatic layer. Programmatic layer will initialize the Report Processor. Report processor will retrieve the report definition or model information then combines layout information with the data from data processing extension and renders it in the requested format.

Authentication, Report Processing, Rendering , Data Processing and Delivery Extensions

The report server supports five types of extensions, authentication extensions, report processing extensions, rendering extensions, data processing extensions and delivery extensions. A report server requires at least one authentication extension, rendering extension and data processing extension. Delivery and custom report processing extensions are optional.

3- The Scheduling and Delivery Processor

The Scheduling and Delivery Processor processes reports triggered from a schedule, and delivers reports to target destinations.

Tier 1 (Data Layer)

1- Report Server Database:

Reporting Server Database is a SQL Server database. This is created at the time of Reporting Services configuration. It stores Reporting Services data such as Report Definitions, Report Metadata, Cached Reports and snapshots. It also stored security settings, encrypted data, scheduling -delivering and extension information and folder hierarchy. This data is accessed through Report Server. Report Server Database can provide internal storage information for a single reporting services or multiple report server.

2- Data Sources:

This is the data which user wants to represent in reports.

I hope you enjoyed the article don't forget to share on Facebook. Please leave your comments below if there are any questions.

Content reference is msdn.