By popular request:
-
Recent Posts
Categories
-
Join 24 other subscribers
Meta
By popular request:
Okay, so Microsoft seem to have added quite a bit of confusion with the over-type functionality in Word. Having thought that we now had this sussed, we found that one user was unable to enable over-type mode in the Advanced settings as the checkbox was disabled.
After hours of Googling, testing, experimenting, etc we stumbled upon the reason by accident.
If you have Track Changes enabled on a document you cannot enable over-type mode.
Hope this saves someone else hours of frustration!
One of the things with SSRS subscriptions is that they create a load of Agent Jobs that don’t clearly link back to specific reports.
SQL warns you not to mess with these directly, so you need to find the report and remove or edit the subscriptions via the SSRS interface.
We had loads that were expired, but the question was, what reports to they relate to?
The list below shows a number of Subscriptions that have not run for a while and are not scheduled to run again.
So, how do we find the associated reports?
If you double click a Job you’ll see the screen below
Click On “Steps”
Then Click on “Edit”
What we want is the @EventData which is the Subscription ID
If you then open MS Management Studio and run the following Code against your SSRS Report Database (replace the id with the one shown in the @EventData above):
SELECT c.Path, c.Name FROM dbo.Subscriptions s JOIN dbo.Catalog c ON s.Report_OID = c.ItemID WHERE s.SubscriptionID = '56d31dd0-aca0-420c-940b-7d45f862db1c'
This will then show you the report path and name that the subscription relates to.
You can then connect to your SSRS reports and delete the subscription via the subscription options.
Following on from exporting a DataGridView to a CSV file someone asked this week if it’s possible to copy the contents to the clipboard for pasting into another application.
The code below copies the content of the passed DataGridView to the clipboard inserting the specified delimiter between each column.
The basic idea is to scan each row and write the contents of the cell to a string builder, inserting the delimiter after each column (except the last one in the row). At the end of the row append a new line to the string builder. When the end of the grid is reached copy the contents of the string builder to the clipboard.
The procedure returns either true (if the DataGridView had data in it) or false (if the DataGridView is empty).
[csharp gutter=”false”]
//accepts two parameters:
// dgvGrid – reference to a valid DataGridView
// colDelimiter – character(s) to insert between each column
//returns:
// true if the grid has rows
// false if the grid has no rows
private bool copyGridToClipboard(DataGridView dgvGrid, string colDelimiter)
{
//variable to hold the value stored in each cell
string cellValue;
//variable to hold the text extracted from the grid
StringBuilder sb = new StringBuilder();
//set row and column count variables
int rowCount = dgvGrid.RowCount;
int colCount = dgvGrid.ColumnCount;
//check to see if the grid has some rows
if (rowCount > 0)
{
//initialise the varible to hold each grid row
DataGridViewRow gridRow = new DataGridViewRow();
//loop through rows
for (int row = 0; row <= rowCount – 1; row++)
{
//set gridRow to the current row
gridRow = dgvGrid.Rows[row];
//loop through columns
for (int col = 0; col <= colCount – 1; col++)
{
//if the gridRow cell is not null assign the contents to cellValue
if (gridRow.Cells[col].Value != null)
{
cellValue = gridRow.Cells[col].Value.ToString();
}
//otherwise set cell value to an empty string
else
{
cellValue = string.Empty;
}
//append the cell value to the stringbuilder
sb.Append(cellValue);
//if the column is not the last one append the column delimiter
if(col < colCount – 1)
{
sb.Append(colDelimiter);
}
}
//end of row columns: so append newline to the stringbuilder
sb.AppendLine();
}//end of final row: so copy the entire stringbuilder to the clipboard & return "true"
Clipboard.SetData(System.Windows.Forms.DataFormats.Text, sb.ToString());
return true;
}else
//otherwise return "false"
{
return false;
}
}
[/csharp]
Below is an example of calling the procedure (NB the calling form has a DataGridView called dgvExcel)
[csharp gutter=”false”]
//call sending the DataGridView dgvExcel and a comma as the delimiter
if (copyGridToClipboard(dgvExcel, ","))
{
MessageBox.Show("Data Copied to the Clipboard", "Copy – Complete", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
MessageBox.Show("No Data to Copy", "Copy – Failed", MessageBoxButtons.OK, MessageBoxIcon.Information);
} }
[/csharp]
There are many phone scams where someone will ring you claiming to be from Microsoft, Dell, Apple, or some other known IT company.
They will tell you that they have detected issues with your computer, normally “security” issues.
They will often ask you to open the “Event Viewer” on your computer to show you the errors.
They will then ask you to go to a particular webpage and download some software that will fix your computer
Consider the following:
Imagine someone knocked on your door and said, “I can tell your oven is broken and I can fix it if you give me your credit card details”, what would you do?
*All Windows computers will show errors in the “Event Viewer” (this is something the hoaxers rely on). They are generally unimportant errors that you don’t need to worry about, but the scammers rely on you thinking they have “seen” these errors and you need to take action.
Okay, expanding on what I did yesterday.
I Built a table, called ExclusionList with one field, ExcludedText
E.g.
Then created a function:
CREATE FUNCTION [dbo].[udf_clean_string] ( @string VARCHAR(100) ) RETURNS VARCHAR(100) AS BEGIN SELECT @string = REPLACE(@string,ExcludedText,'') FROM ExclusionList RETURN @string
You can then use like this:
SELECT dbo.udf_clean_string(FieldName) FROM TableName
Ok, I’ve seen many ways to do this using loops, but nothing in plain T-SQL
Let’s say we have a string that may contain illegal characters, in my case it was project identifiers that needed to be used as file names for exporting. However, many had / or * in the names.
One thing I hate in T-SQL is loops, so having only found looping ways to solve this I started from scratch.
He’s my solution:
Build a table of exclusions, i.e. characters or strings that you want to remove from your string (in my example below this is a temp table, but I would make it a permanent table in the end solution).
Then use REPLACE joined to this table to clean the string e.g.
/*Create the temp table for the exclusion list table*/ CREATE TABLE #ExclusionList ( ExcludedChar VARCHAR(50) ) /*Add the stuff we want to exclude*/ INSERT INTO #ExclusionList SELECT '\' INSERT INTO #ExclusionList SELECT '|' INSERT INTO #ExclusionList SELECT '?' INSERT INTO #ExclusionList SELECT '*' INSERT INTO #ExclusionList SELECT ':' INSERT INTO #ExclusionList SELECT '<' INSERT INTO #ExclusionList SELECT '>' INSERT INTO #ExclusionList SELECT '"' INSERT INTO #ExclusionList SELECT 'egg' /*Get the horrible string that we want to clean*/ DECLARE @string VARCHAR(50) = 'th""<i>???s:: :|||"<egg>wo|r|k|**s*< ">we*l::l::' /*Do the SELECT*/ SELECT @string = REPLACE(@string,ExcludedChar,'') FROM #ExclusionList /*Show the string post processing*/ SELECT @string /*Drop the temp table*/ DROP TABLE #ExclusionList
If you run this you’ll see that all the excluded text has been stripped and you’re left with “this works well”.
I’m all ready thinking of ways this could be expanded, e.g. Exclusion List type added to the exclusion list, you could then maintain different lists for different things, e.g. File Names, Email Addresses, etc and just add a WHERE clause to the REPLACE select statement.
Any comments or ideas please let me know!
You can buy my programming and computer related gifts from my shop!
I have a reporting portal that serves up SSRS (SQL Server Reporting Services) reports to users. The users are from different organisations, indicated by a user group. I needed to have the front end personalised for each different organisation. The approach below loads a different CSS file depending on the user group of the logged in user. I’m using a master page solution, so the CSS is loaded in the master page file. The CSS files for each organisation are stored in a CSS folder.
If we look at a standard link to a style sheet that we would place in the head of our master page
<head runat="server"> <title></title> <link href="CSS/StyleSheet.css" type="text/css" rel="stylesheet" /> </head>
We are going to change this to what we see below
<head runat="server"> <title></title> <link id="CurrentStyleSheet" type="text/css" rel="stylesheet" runat="server"/> </head>
You need to note three things in the above code. We need to ensure that runat=”server” is added, that there is no “href” to point to our CSS file as we be adding this in code, and we have given our link an id.
Firstly lets load the CSS file in the Page_Load event of the master page. We reference the link using its id and add the “href” attribute and value to the link (the !Page.IsPostBack stops this from being done repeatedly on every page postback).
protected void Page_Load(object sender, EventArgs e) { if (!Page.IsPostBack) { CurrentStyleSheet.Attributes.Add("href", "CSS/StyleSheet.css"); } }
This is all good, however we are still just loading one CSS file regardless. In my database I have a field that holds the CSS file name for each user group and a function getCssFile() that returns the CSS file name for the logged in user. (I’m not going to cover how that function reads the value from the database here).
So the code now becomes
protected void Page_Load(object sender, EventArgs e) { if (!Page.IsPostBack) { CurrentStyleSheet.Attributes.Add("href", string.Format("CSS/{0}", getCssFile())); } }
This goes and gets the name of the style sheet from the database for the logged in user and inserts this into the “href” attribute.