Recently I have been checking all the Dependencies on the Reports in SSRS(Sql server Reporting Services) because of a Major deployment on the ETL. The ETL change was to accomodate the transfer of a few columns from old Table TableA to the new table TableB. Some of the Datasets in the Reports are Stored Procedures and so I used sp_depends procedure to get the list of dependencies for TableA.
I also used the following TSQL to get list of objects in any database where the command text includes TableA.
DECLARE @tablename VARCHAR(100)
SET @tablename = ‘%tableA%’
SELECT OBJECT_NAME(id) AS OBJECT ,TEXT,* FROM syscomments WHERE TEXT LIKE @Tablename
So far its good. But I found there are some TSQL written directly in the Reports Datasets(which I think is bad SSRS Development) and I cannot search them using the above two procedures.
Upon researching in the ReportServer database the TSQL that is stored in the datasets are embedded in the CommandText tag of contentcolumn in the Catalog table.
The following is the script that illustrates it. DECLARE @tablename VARCHAR(100)
SET @tablename = ‘%TableA%’
SELECT PATH
, NAME
, CAST(CAST(content AS VARBINARY(MAX)) AS XML) AS ReportXML
FROM ReportServer.dbo.Catalog
WHERE CAST(content AS VARBINARY(MAX)) LIKE @tablename
You can even update the reports using the ReportXML column which I will discuss about it the future blogs.
Recently the ETL load times of my Datawarehouse are increasing and so I decided to check the Indexes status on the tables . I found some fragmentation on the indexes and also some Statistics that are not being updated regularly . I searched online and found this blog by Ben Nevarez(SQL Blog).
I decided to check the fragmentation levels of the Index after the ETL load is done daily on a regular basis. So I digged deep into the DMV’s and got this script below
select
OBJECT_NAME(ps.object_id) as TableName
,si.Name
,ps.Avg_Fragmentation_in_percent
,STATS_DATE(ss.object_id,ss.stats_id) as LastUpdatedStatistics
from
sys.dm_db_index_physical_stats(DB_ID(DB_NAME()),null,null,null,null) ps
join
sysindexes si
on
ps.object_id = si.id
and
ps.index_id = si.indid
left outer join
sys.stats ss
on
ss.object_id = ps.object_id
and
ss.name = si.name
where
ps.avg_fragmentation_in_percent > 10
order by
ps.avg_fragmentation_in_percent desc
I published the above query in my Reporting services suite of Operational Reports and check it daily morning. The report shows all the Indexes with more than 10% percent fragmentation along with Last updated Statistics time . If you want to go further.You may want to rebuild all the indexes that are above 20% fragmented in TSQL. If there is a better way of doing it please comment.
Recently I have read a lot of Blog posts on how to export files in different formats. But I use a custom procedure which exports text files in Pipe Delimited format using BCP.This procedure can be used in other processes for Output and Error Handling.
Here is the Script for the procedure.You can modify the below procedure using any of the delimiters.
Many Clients have varied Requirements for Dates. You can do the conversions in Date format using T-SQL from the following link in MSDN.
In reporting services you can convert datetime using the customs visual basic datetime formats using Expressions.
Here are the following links on MSDN for Visual Basic Datetime Formats Predefined DateTime Formats Using Format Function
If the sql server reporting services is configured on the server called “SQLBOX” then the URL is http://SQLbox/reports.
If there is a named instance like SQLBOX\VIVEK then the reporting server URL is http://SQLBOX/reports_vivek
Today I stuck with an interesting problem of converting number data in Varchar fields to numeric datatypes. I have created the following sample data
Create table SQL script
The data in the table contains Decimal and scientific values in varchar format as shown in the figure below.
Now when I tried to convert the above values to numeric as below it returns only 3 rows but with the following message
Wrong SQL
Msg 8114, Level 16, State 5, Line 2
Error converting data type varchar to numeric.
The isnumeric does return 1 for all the 4 values in the table. I modified the query so that it initially converts to
float and then converts to numeric and it works.
Storing the list of email subscribers is quite often seen in the web based companies. Once the emails are stored they are segmented based on the customer interests and products they have purchased on the website.
In this article I will show the simple step of extracting the Account Name and Domain Name using T-sql.
Expressions are powerful in Integration Services. With Expressions you can dynamically set the servername (based on the environment) or the name of the excel file(if it is a fixed format). Let me give you an example.
I have a simple Dataflow task(as shown below) that gets the data from a table and exports it in a Tab delimited text file.
The Data Flow Task
I have set up a following variable called ServerName where I can change the value dynamically on the Connection String.
Variable Server Name
To configure the ConnectionString to use the Variable name dynamically at RunTime we need to configure Dimensions. This is how its done.Right Click on the Connection String and click properties(or click F4 by highlighting Databaes connection)
Database Connection Properties
In the Properties section click on the Expression for configuring nd also setting the Servername settings as below.
Expressions_Connection_Properties
Select ServerName
Configure Value as ServerName
Click Ok twice and the expressions are now configured. The best way to check it is to use the Bidshelper. (www.codeplex.com/BidsHelper) In the below diagram as you notice the Pink colour on the edge of the Connection String Icon which represents that the connection is configured via Expression.
The above example should give you a good example on how to configure Connection Strings using Expressions. Just keep commenting .
Watched Flintoff Depression on Iplayer yesterday Good one - do the same for the Fans Depression - the way Indian cricket team is playingabout 3 weeks agofrom web
