July 2009
Mon	Tue	Wed	Thu	Fri	Sat	Sun
<< <		> >>
		1	2	3	4	5
6	7	8	9	10	11	12
13	14	15	16	17	18	19
20	21	22	23	24	25	26
27	28	29	30	31

Dynamic column names and fields in SSRS (Custom Matrix)

by onpnt

01 Jul 2009 12:26 , Categories: Data Modelling & Design, Database Programming, Database Administration, Microsoft SQL Server Admin, Microsoft SQL Server

I had no choice but to do work with creating dynamic column headings and dynamically determine what field in my dataset should go where in a report today. Sense this is the second time I’ve gone through this exercise and knowing the lack of information out there on really how to do it, I thought it deserves a blog entry.

So here is the basis of the requirements. You have a query that uses PIVOT but thrown into the mix is the need for dynamic columns in the PIVOT. This is usually a task when you are going after things like current week plus the last 52 weeks. That was the case in this situation. I needed to bring in a dynamic set of columns to be used in PIVOT. The matrix in 2005 did not give me what I needed in the end result so this is the path I took.

First task is to write the procedure to use PIVOT with dynamic column headers. I’m not going to go into that method sense it’s well documented out there and out of scope. I will point you to Pivots with Dynamic Columns in SQL Server 2005 as it explains the way to accomplish this well.

I wrote something in AdventureWorks to for this example so if you have AdventureWorks floating around you should be able to read this and run through step for step with success.

Here is our procedure. I’m sure my methods will take great notice from my local TSQL friends The dynamic SQL more so than anything...

tsqlLine number On/Off | Show/Hide | Select all

CREATE PROCEDURE GetSalesPerWeek
AS
DECLARE @weeks_ordered TABLE (num VARCHAR(3))
DECLARE @weeks TABLE (wk INT)
DECLARE @DATE DATETIME
DECLARE @cols NVARCHAR(3000)
DECLARE @INT INT
DECLARE @col_pv VARCHAR(2000)
DECLARE @query VARCHAR(3000)
SET @INT = 1
SET @DATE = GETDATE()
WHILE @INT <= 52
BEGIN
INSERT INTO @weeks VALUES (@INT)
SET @INT = @INT + 1
END
INSERT INTO @weeks_ordered
SELECT
wk
FROM @weeks
ORDER BY
CASE WHEN DATEPART(wk,@DATE) - wk < 0
THEN DATEPART(wk,@DATE) - wk + 53
ELSE DATEPART(wk,@DATE) - wk
END DESC
SELECT @col_pv = STUFF(( SELECT
'],[' + w.num
FROM @weeks_ordered AS w
FOR XML PATH('')
), 1, 2, '') + ']'
SELECT @cols = STUFF(( SELECT
'],0) as W' + CASE WHEN CAST(w.num - 1 AS VARCHAR(2)) = 0 THEN '52' ELSE
CAST(w.num - 1 AS VARCHAR(2)) END + ',isnull([' + w.num
FROM @weeks_ordered AS w
FOR XML PATH('')
), 1, 2, '') + '],0) as W' + CAST(DATEPART(wk,GETDATE()) AS VARCHAR(2))
IF OBJECT_ID('tempdb.dbo.#detail') IS not null
DROP TABLE #detail
CREATE TABLE #detail
(
AccountNumber VARCHAR(10)
,PruductNumber VARCHAR(25)
,OrderQty INT
,WeekNumber SMALLINT
)
INSERT INTO #detail
SELECT
cust.AccountNumber
,items.ProductNumber
,det.OrderQty
,DATEPART(wk,hdr.ShipDate) WeekNumber
FROM
Sales.SalesOrderHeader hdr
INNER Join Sales.SalesOrderDetail det ON hdr.SalesOrderID = det.SalesOrderID
INNER Join Production.Product items ON det.ProductID = items.ProductID
INNER Join Sales.Customer cust ON hdr.CustomerID = cust.CustomerID
INNER Join @weeks ord ON DATEPART(wk,ShipDate) = wk
WHERE ShipDate >= DATEADD(wk,-52,'2004-06-01')
GROUP BY
cust.AccountNumber
,items.ProductNumber
,det.OrderQty
,DATEPART(wk,hdr.ShipDate)
,wk
ORDER BY
CASE WHEN DATEPART(wk,ShipDate) - wk < 0
THEN DATEPART(wk,ShipDate) - wk + 53
ELSE DATEPART(wk,ShipDate) - wk
END
SET @query =
'
Select
AccountNumber
,PruductNumber
, ' + RIGHT(@cols,LEN(@cols)-10) + '
From
#detail as sales
PIVOT (sum(OrderQty) FOR WeekNumber IN (' + @col_pv + ')) as pv
Order By AccountNumber
'
EXEC(@query)

The results shows us the PIVOT results of each account number and the sales for the week of the year

The problem with all of this is the dynamic nature of the column names. In reporting services we’re used to handling column names as static entities. So here is how we’ll build our report given the fact these column names can and will change over time.

So create a new report in your solution and add a new DataSet. Make it a text call with the following statement

tsqlLine number On/Off | Show/Hide | Select all

EXEC GetSalesPerWeek

Run the DataSet to verify everything comes in ok.

Now add a new table to your empty report. Add the account number and product number as you would normally. Next we need to figure out what week is actually first. To do this we’re going to write a function in the code section of SSRS

In the layout tab go to Report and select Report Properties. This will give you the properties for the entire report. Select the Code tab. Copy and paste the following code into the window

vbLine number On/Off | Show/Hide | Select all

Public Function GetColumnHeading(ByVal x As Integer)
Dim WeeksArr As New System.Collections.ArrayList()
Dim i As Long
Dim CurrentWeek As Long
CurrentWeek = DatePart(DateInterval.WeekOfYear, System.DateTime.Now)
For i = 1 To 52
WeeksArr.Add(1 + (i + CurrentWeek - 1) Mod 52)
Next
Return WeeksArr(x)
End Function

This code was written by our own gmmastros. Thanks to him for this and the help it gave me when I needed it. Gets the job done and it does it quickly.

Final results should look like this

Hit OK to save.

Now in the field next to the Product Number go ahead and enter an expression for the heading like this

="W" & Code.GetColumnHeading(0)

Recall in our procedure we return each week as Wnn for the week number. So in our code we created an ArrayList filled up with the order we want. The same order we based the procedure off of. Now by using the index of the ArrayList we can simply call for the heading that should be all the way to the left (-51 weeks from the current) by means of index of 0. In the details textbox we can then simply do the following as well given the same guidelines

=Fields("W" & Code.GetColumnHeading(0)).Value

Most developers don’t know they can reference the fields by name in this manner. Usually it just isn’t required and that is the case. It can be useful to note that you can dynamically fill the object name in though and get the same results as Fields!name.Value

I went ahead and put a few more columns and increased the index requested from the ArrayList. Running that results in the following.

Now you have your customer matrix in a sense by means of dynamic column and field referencing. You also a nice example of PIVOT by means of dynamic column names.

This blog probably has about 12 pages worth of explanations but I’d like to leave those to the forums so please follow the directions below if you want to discuss this method further. Otherwise, have fun with it!

*** If you have a SQL related question try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum

3 comments » • Send a trackback » 274 views

DBA SQLCLR Procedures

by onpnt

26 Jun 2009 06:57 , Categories: Database Programming, Database Administration, Microsoft SQL Server

SQLCLR adds a great deal to the SQL Developer list of methods to get the job done. That is pretty much a given. Hardware, resource usage and all that endless discussion aside, SQLCLR given handled correctly and given the hardware to utilize its abilities without putting memory and other pressure on your instances can be truly a beneficial aspect to your bag of tricks.

As a DBA I have dozens of SQLCLR procedures that make my life unbelievably easier than in prior years. Also coming from years of developer experience have helped me along in catching on the SQLCLR. Today I thought sharing a few common and very useful DBA procedures would make a handy post.

The first is DateTime formatting. Yesterday I posted on SSRS internal procedures In that post I gave up a handy SQLCLR procedure for formatting a DateTime value to handle Time Zone Offset. I use this one often in DBA tasks and draw off of it for many things.

Here is that method again (this is also a UDF and not a Proc)

csharpLine number On/Off | Show/Hide | Select all

public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString DateTimeTimeZoneOffset(DateTime datetime_sent)
{
return new SqlString(datetime_sent.ToString("yyyy-MM-ddTHH:mm:ss.fffzzzz"));
}
};

Call

tsqlLine number On/Off | Show/Hide | Select all

SELECT dbo.DateTimeTimeZoneOffset('2009-06-26')

Usage of this is seen directly in the blog post from yesterday and how it can be a simply task for formatting things like DateTime. Don't let it stop there though. Formatting strings for file names, headers, logs and on can be done quickly and easily with C# (or VB.NET) and makes this a great tool to any DBA.

Next goes into file operations.

The first is file movement. This can be done with external objects like batch, ActiveX scripts and even executables called via the OS command in jobs. Using this as a SQLCLR procedure makes it nice in calling the operation directly from the TSQL task you are performing though.

Move file (which is also the rename file method)

csharpLine number On/Off | Show/Hide | Select all

public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void MoveFile(string sfilename, string dfilename)
{
try
{
File.Move(sfilename, dfilename);
}
catch (Exception ex)
{
SqlContext.Pipe.Send("Error writing to file : " + ex.Message);
}
}
};

Call

tsqlLine number On/Off | Show/Hide | Select all

EXEC MoveFile 'C:\txt_doc.txt','C:\txt_doc_new.txt'

Write file

csharpLine number On/Off | Show/Hide | Select all

public class SQLCLRIO
{
public static void WriteToFile(String content, String filename)
{
try
{
File.WriteAllText(filename, content);
}
catch (Exception ex)
{
SqlContext.Pipe.Send("Error writing to file : " + ex.Message);
}
}
}

Call

tsqlLine number On/Off | Show/Hide | Select all

EXEC WriteToFile 'Testing....','C:\txt_doc_new.txt'

Last one I'll post today is removing old files (backups typically) which is similar to a task I used to do using vbs scripts. The vbs scripts are a good method. I still use it on a few instances I want security more restricted as well. I say that of course as the permission level of any file operations SQLCLR will be external or unsafe. Given that setting is permitted in the situation, the SQLCLR is handy and easier to throw logging and other methods into the process.

Delete old backups in a series of subdirectories.

csharpLine number On/Off | Show/Hide | Select all

public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void RemoveOldBackups(string path,int retention)
{
try
{
DirectoryInfo dirList = new DirectoryInfo(path);
DirectoryInfo[] subs;
System.TimeSpan diff;
subs = dirList.GetDirectories();
foreach (DirectoryInfo dir in subs)
{
foreach (FileInfo file in dir.GetFiles())
{
diff = DateTime.Now.Subtract(file.LastWriteTime);
if (diff.Hours > retention)
{
file.Delete();
}
}
}
}
catch (Exception ex)
{
SqlContext.Pipe.Send("Error deleting from diretory : " + ex.Message);
}
}
};

Call

tsqlLine number On/Off | Show/Hide | Select all

EXEC RemoveOldBackups 'C:\test\',23

Have fun and don't forget the pressure SQLCLR can bring to you instances.

*** If you have a SQL related question try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum

Leave a comment » • Send a trackback » 358 views

Not a fan of the Report Manager in SSRS? Using SSRS procedures to get the job done

by onpnt

25 Jun 2009 11:02 , Categories: Data Modelling & Design, Database Programming, Database Administration, Microsoft SQL Server Admin, Microsoft SQL Server

I took some time off so apologize to readers for my lack of writing lately.

Today I thought I'd talk about the report manager and SSRS. Personally I'm not a big fan of it. It lands in there with SSMS and tasks like creating or modifying indexes. I use it when I need it really quick and it's the only thing I need to touch. If you have to modify or create a bunch of things it becomes very cumbersome and pretty much an annoyance. Any browser based front end is a dog. Sense report manager is just that, you can expect refresh issues, slow response and having to hit 30 "OK" buttons to get one thing saved. The nice thing about most SQL Server Services is they are controlled by system procedures, functions etc... This means in most cases you can utilize these same system objects to your advantage. One that I'll show you today is the create subscriptions procedure named, "CreateSubscription" for Reporting Services.

If you're are using SSRS and in a high reporting environment like most are, then you more than likely have dozens if not hundreds of subscriptions setup so reports are automatically delivered to users on a time basis. In most cases the business has critical tasks that reply on reports to be run at specific times. If those reports are not run at the exact time specified, there may be missed opportunities or missed issues that can be hidden by data later on. When I first started using SSRS years back I dreaded the task of creating a subscription for a report to run here and there through the day. It was time consuming and again, an annoyance. This lead me to run profiler and do a bit of reading on what SSRS does behind the scenes in report manager. Once I found the CreateSubscription procedure, creating a dozen scheduled subscriptions for days in the week was actually pretty easy.

To run this procedure I recommend first looking at it to see what is does. You should always follow this guideline anytime you try to use objects like this. Follow the train all the way through to the end result set. An example of a really big catch on why you should do this can be seen in the CreateSubscription procedure. In order to gather the records required to insert the SID's for the person creating the subscription, the procedure GetUserIDBySid is called given the authority type of 1 being sent. If you do not accurately gather your SID before calling the CreatSubscription, you will essentially force the GetUserIDBySid to insert another row for your SUSER_NAME value. That will essentially break down the integrity sense you will now have duplicates of your account listed in the Users table. So for this issue you should follow back to the Users table and see that the SID is stored as a varbinary(85) and you can gather that based on your SUSER_NAME value as such

tsqlLine number On/Off | Show/Hide | Select all

SET @my_usersid = (SELECT [SID] FROM ReportServer.dbo.Users WHERE UserName = suser_name())

Use this SID the modify and create user value.

The ExtensionSettings and Schedule are XML values so be careful on how you form them. THe best resource you will probably find on the XML values is by looking up the "CreateSubscription Method" on MSDN here

The one thing that is another catch is the StartDateTime is based on a datetime value with the time zone offset included. If this is not formatted correctly, the report subscription is created but the schedule will fail. This creates a mess and will require you to remove the entire subscription to clear it up. To format this datetime I use a SQLCLR UDF as shown below

csharpLine number On/Off | Show/Hide | Select all

public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString DateTimeTimeZoneOffset(DateTime datetime_sent)
{
return new SqlString(datetime_sent.ToString("yyyy-MM-ddTHH:mm:ss.fffzzzz"));
}
};

You can then pass to this function a basic datetime value while only needing to worry about sending the time that you want over to it.

as such...

tsqlLine number On/Off | Show/Hide | Select all

SET @time_send = (SELECT dbo.DateTimeTimeZoneOffset(CAST('2009-06-25 08:00:00' AS DATETIME)))

So putting this all together you can come up with a script similar to the following. This tested will create a subscription for each day of the week to be sent out at 8:00 AM

tsqlLine number On/Off | Show/Hide | Select all

DECLARE @me NVARCHAR(260)
DECLARE @now DATETIME
DECLARE @time_send VARCHAR(35)
DECLARE @schedule NVARCHAR(1000)
DECLARE @report_id UNIQUEIDENTIFIER
DECLARE @report_name NVARCHAR(425)
DECLARE @my_usersid VARBINARY(85)
SET @report_name = '/{folder}/{reportname}'
SET @report_id = (SELECT ItemID FROM ReportServer.dbo.CATALOG WHERE [PATH] = @report_name)
SET @now = GETDATE()
SET @me = SUSER_NAME()
SET @my_usersid = (SELECT [SID] FROM ReportServer.dbo.Users WHERE UserName = suser_name())
SET @time_send = (SELECT dbo.DateTimeTimeZoneOffset(CAST('2009-06-25 08:00:00' AS DATETIME)))
SET @schedule = '<ScheduleDefinition xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<StartDateTime xmlns="http://schemas.microsoft.com/sqlserver/2006/03/15/reporting/reportingservices">' + @time_send + '</StartDateTime>
<WeeklyRecurrence xmlns="http://schemas.microsoft.com/sqlserver/2006/03/15/reporting/reportingservices">
<WeeksInterval>1</WeeksInterval>
<DaysOfWeek>
<Monday>true</Monday>
<Tuesday>true</Tuesday>
<Wednesday>true</Wednesday>
<Thursday>true</Thursday>
<Friday>true</Friday>
</DaysOfWeek>
</WeeklyRecurrence>
</ScheduleDefinition>'
EXEC CreateSubscription @Report_Name=@report_name,
@id=@report_id,
@OwnerSid = @my_usersid,
@OwnerName=@me,
@OwnerAuthType=1,
@Locale=N'en-US',
@DeliveryExtension=N'Report Server Email',
@InactiveFlags=0,
@ExtensionSettings=N'<ParameterValues>
<ParameterValue>
<Name>TO</Name>
<Value>enduser@emails.com</Value>
</ParameterValue>
<ParameterValue>
<Name>BCC</Name>
<Value>system_retention@dba.com</Value>
</ParameterValue>
<ParameterValue>
<Name>ReplyTo</Name>
<Value>NoReply@company.com</Value>
</ParameterValue>
<ParameterValue>
<Name>IncludeReport</Name>
<Value>True</Value>
</ParameterValue>
<ParameterValue>
<Name>RenderFormat</Name>
<Value>EXCEL</Value>
</ParameterValue>
<ParameterValue>
<Name>Subject</Name>
<Value>@ReportName was executed at @ExecutionTime</Value>
</ParameterValue>
<ParameterValue>
<Name>IncludeLink</Name>
<Value>True</Value>
</ParameterValue>
<ParameterValue>
<Name>Priority</Name>
<Value>NORMAL</Value>
</ParameterValue>
</ParameterValues>',
@ModifiedBySid = @my_usersid,
@ModifiedByName=@me,
@ModifiedByAuthType=1,
@ModifiedDate=@now,
@Description=N'Send e-mail to users and other descriptions',
@LastStatus=N'New Subscription',
@EventType=N'TimedSubscription',
@MatchData=@schedule,
@PARAMETERS=N'<ParameterValues />',
@Version=3

With all of this you can now create multiple subscriptions for the same report to run through the day while only changing the time entered. You can also modify this easily to utilize the method in a procedure. This makes it much cleaner and easier to dynamically send multiple times and multiple reports so you can create mass subscriptions with one call. I will try to get a well error handled and procedure like that up in the next few days for download.

*** If you have a SQL related question try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum

Leave a comment » • Send a trackback » 397 views

Replace Your Case When Then Else Syntax With the Sign Function In SQL Server

by SQLDenis

22 Jun 2009 08:50 , Categories: Data Modelling & Design, Database Programming, Microsoft SQL Server Tags: functions, howto, sql server 2000, sql server 2008

If you want to show a 1 when there is a value for something in the column and 0 if none of the rows have that values you typically do something like this

tsqlLine number On/Off | Show/Hide | Select all

CASE WHEN SUM(CONVERT(INT,SomeValue)) > 0 THEN 1 ELSE 0 END

Basically you sum it up and if the sum is greater than 0 then you show 1 otherwise you show 0
Here is what it might look like in code

First create the following table with this data

tsqlLine number On/Off | Show/Hide | Select all

CREATE TABLE #Cars(id INT,brand VARCHAR(20),HasDefects BIT)
INSERT #Cars VALUES(1,'Chevy Corvette',1)
INSERT #Cars VALUES(2,'Ford Taurus',0)
INSERT #Cars VALUES(3,'Ford Taurus',1)
INSERT #Cars VALUES(4,'BMW 635 CSi',0)
INSERT #Cars VALUES(5,'BMW 635 CSi',0)
INSERT #Cars VALUES(6,'Fiat 500',1)
INSERT #Cars VALUES(7,'Fiat 500',1)

And here is our CASE WHEN THEN ELSE query

tsqlLine number On/Off | Show/Hide | Select all

SELECT brand,CASE WHEN SUM(CONVERT(INT,HasDefects)) > 0 THEN 1 ELSE 0 END AS HasDefects
FROM #Cars
GROUP BY brand

We need to convert HasDefects to an integer before using the sum function, otherwise you will get the following error

Server: Msg 409, Level 16, State 2, Line 1
The sum or average aggregate operation cannot take a bit data type as an argument.

So how can we change that to use the sign function? It is very easy all you have to do is wrap the sign function around the sign function

tsqlLine number On/Off | Show/Hide | Select all

SELECT brand, SIGN(SUM(CONVERT(INT,HasDefects))) AS HasDefects
FROM #Cars
GROUP BY brand

Here is our output
--------------------

brand	HasDefects
BMW 635 CSi	0
Chevy Corvette	1
Fiat 500	1
Ford Taurus	1

As you can see that made the code smaller by about 20 bytes. This of course will only work if you want 0 and 1; if you want more posibilities then you need to use case. Another reason why you maybe don't want to use the sign function is that someone looking at your code might not immediately know what the code is supposed to do.
Remember you should always write code with the assumption that the person who will be maintaining your code is a psychopatic killer who has your address ;-)

How does the sign function work?

If the value is negative then -1 is returned
If the value is positive then 1 is returned
If the value is 0 then 0 is returned
If the value is NULL then NULL is returned

Scale will affect the output also; if you use -22.0001 then -1.0000 will be returned and if you use -22.01 then -1.00 will be returned
Here is a query you can run to see what sign returns for different values

tsqlLine number On/Off | Show/Hide | Select all

SELECT SIGN (0), -- 0
SIGN (1), -- 1
SIGN (-1), -- -1
SIGN (null), --null
SIGN (-200), -- -1
SIGN (200), -- 1
SIGN (-22.0001), -- -1.0000
SIGN (22.0001), -- 1.0000
SIGN (-22.01), -- -1.00
SIGN (22.01) -- 1.00

On our wiki there is an article that shows you another 9 lesser know functions; these functions are

BINARY_CHECKSUM
COLUMNPROPERTY
DATALENGTH
ASCII, CHAR,UNICODE
NULLIF
PARSENAME
STUFF
REVERSE
GETUTCDATE

You can find that article here Ten SQL Server Functions That You Have Ignored Until Now

*** If you have a SQL related question try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum

1 comment » • Send a trackback » 979 views

Writing reports off third party databases the easy way

by onpnt

05 Jun 2009 13:30 , Categories: Data Modelling & Design, Database Programming, Microsoft SQL Server Admin, Microsoft SQL Server

SQL Server profile is misleading when you look at it under the All Programs listing. It states that SQL Server Profile is part of the, "Performance Tools" set. OK, yeah it really is primarily a performance tool for DBAs. There’s something else that it does though that is a pretty handy friend when either writing reports on third party databases or trouble shooting issues with application not functioning.

Let's say a user comes to you and asks for a report "just" like that one in "this" screen. They just want a summarized column or maybe that one column that they can’t get at in the applications reporting tools. What do you do? You start looking at the table designs and SELECT *ing the living hell out of your database server to the point you are actually the one user in which you try so hard to prevent from causing issues. What are you doing man???

OK, what does SQL Server Profiler do? It does exactly what you need to determine the location and design of the database.schema.tables behind the application for starters. Think about the fact that if you had a user that you just couldn’t figure out why they take the server to its knees at 8:03 PM nightly. You open profiler about 10 minutes before, log the batches to a table or files and then query it up after the server is back to life once the user did the mystical, "I don’t do anything" magic once again. In that result set you find the user, the time and the query that you need to help you along or give suggestive criteria for other means to the task being completed.

Now think about that same scenario but in the aspect of you writing a query on a database you know little about. User wants it to look "just" like "this" screen! Start profiler and use the filters in profiler to your advantage then. Say I’m a DBA helping the user (isn’t that fun?), The user I log in under is MeDBA_CanDOAnything and you already know that the screen in the application is working off at the least, customers table. First thing is open profiler and hit the trace properties button. Go to the events selection and select the ApplicationName filter. Throw the application name in there and then go to LoginName and put in MeDBA_CanDOAnything. Now go to TextData and put your typical wildcard phrase of %customers%. Hit ok and then run that thing!

Open the application now and go to the screen where you run what is it that is "just" like what "they" need. Watch the query fly by in profiler once you do that. Stop profiler and find the row that has some relevant value to the screen you executed. Something around customer order number or customer number etc... If you saved these to a table, just write a select to find the rows. There you have it! Now if you’re lucky, adding the column to this is already there and in the tables in the query already written for you. If you not, then all I can say is 99.9% of your work was just done for you so I’d still be pretty damn happy.

WARNINGS! It’s my duty as a DBA to tell you these points.

DO NOT run profiler on a production system during heavy load periods. Profiler is a resource intensive tool and WILL cause performance issues on its own. If you use the save to table option, then watch your log size if in full recovery.
Profiler has intersting security on it. Bascially you need ALTER TRACE but you also need to be sure you can execute everything you read in (kind of). You can see this here with the example that is better than I could give. If you are not sure or get errors, ask your DBA very nicely if he or she would be so kind (as all us DBAs are) to run it for you when they say they are going to execute the screen in the application
If you Google this tip, you will see it about a zillion times. I’m just refreshing it for everyone sense my SQL developer actually didn’t know what the hell I was talking about when I said, “just use profiler, duh?!?”

2 comments » • Send a trackback » 329 views

User	Ratings
Emtucifor	23
SQLDenis	22
Thirster42	10
chrissie1	10
gmmastros	10
AlexCuse	9
Remou	6
kaht	6
traingamer	6
Niikola	4

EXPERTS, INFORMATION, IDEAS & KNOWLEDGE

Your profile

Search

Main Categories

Categories

Tools

XML Feeds

Tag cloud

Authors

Categories: Database Programming, IBM DB2, Microsoft SQL Server, MySQL, Oracle

Data Management

Dynamic column names and fields in SSRS (Custom Matrix)

by onpnt

DBA SQLCLR Procedures

by onpnt

Not a fan of the Report Manager in SSRS? Using SSRS procedures to get the job done

by onpnt

Replace Your Case When Then Else Syntax With the Sign Function In SQL Server

by SQLDenis

Writing reports off third party databases the easy way

by onpnt

Tools

Blogs

Statistics

Top 10 rated users for the last 7 days

Links

With Thanks To...