SELECT DP1.name as ROLE_NAME , DP2.name as [USER_NAME] FROM sys.database_role_members DRM INNER JOIN sys.database_principals DP1 ON DRM.role_principal_id = DP1.principal_id INNER JOIN sys.database_principals DP2 ON DRM.member_principal_id = DP2.principal_id
2007/09/28
Listing users and all roles they're members of (SQL Server 2005)
I admit it's trivial but it took me awhile today to understand what data is shown in the sys.database_role_members catalog view, so I am posting this in case I forget.
2007/09/05
Disabling or enabling multiple jobs with a single click
If you navigate to <SQL Server instance>\SQL Server Agent\Jobs in SQL Server Management Studio and then right-click on some job, you'll see a context menu with Enable and Disable commands.
However, when you select more than one job and then right-click on the selection, a different menu shows and, of course [sic], it does not contain these commands.

Luckily there is another way to do it - you can use Job Activity Monitor which uses the same context menu for a single job as well as multiple jobs.
However, when you select more than one job and then right-click on the selection, a different menu shows and, of course [sic], it does not contain these commands.

Luckily there is another way to do it - you can use Job Activity Monitor which uses the same context menu for a single job as well as multiple jobs.
2007/09/04
SSIS imports NULLs instead of numeric values from an Excel file
Let's imagine that you have been assigned a task to import some kind of parts catalogue from an Excel file to SQL Server table. You decided to use Integration Services. One of the columns in the Excel file contains catalogue numbers. Most of them look like this XYZ123456 but some of them are plain numeric values.
This is what can happen:
This is what can happen:
Excel SQL Server CAT_NO CAT_NO --------- --------- XYZ121156 XYZ121156 XYZ654321 XYZ654321 ABC120456 => ABC120456 CBA183456 CBA183456 ZYX123416 ZYX123416 123 NULL (!) ZXV654521 ZXV654521This article from SQL Server 2005 Books Online explains this weird behaviour:
Missing values
The Excel driver reads a certain number of rows (by default, 8 rows) in the specified source to guess at the data type of each column. When a column appears to contain mixed data types, especially numeric data mixed with text data, the driver decides in favor of the majority data type, and returns null values for cells that contain data of the other type. (In a tie, the numeric type wins.) Most cell formatting options in the Excel worksheet do not seem to affect this data type determination. You can modify this behavior of the Excel driver by specifying Import Mode. To specify Import Mode, add IMEX=1 to the value of Extended Properties in the connection string of the Excel connection manager in the Properties window.
2007/07/27
Preventing code from being generated in InitializeComponent() method for a property
When you place controls on a form, Visual Studio generates code in the InitializeComponent() method that sets values of their properties.
If you are developing a custom control and you don't want Visual Studio to generate such code for some of its properties, you should apply the DesignerSerializationVisiblityAttribute to such properties and place DesignerSerializationVisiblity.Hidden value on them.
If you are developing a custom control and you don't want Visual Studio to generate such code for some of its properties, you should apply the DesignerSerializationVisiblityAttribute to such properties and place DesignerSerializationVisiblity.Hidden value on them.
[DesignerSerializationVisibility(DesignerSerializationVisibility.Hidden)] public DateTime Value { get { /* ... */ } set { /* ... */ } }More information about customizing the code generated by Visual Studio can be found here.
2007/07/26
Changing file permissions (chmod) without SSH/telnet access
If you need to change file permissions (chmod) for more than just a few files and your web hosting provider gives you only FTP access to your account (neither SSH/telnet nor a good web hosting control panel system), you can use the chmod function available in PHP.
You can either write a PHP script that calls chmod for each file that needs its permissions to be changed or use this snippet to call chmod recursively and change permissions for an entire folder tree.
You can either write a PHP script that calls chmod for each file that needs its permissions to be changed or use this snippet to call chmod recursively and change permissions for an entire folder tree.
2007/06/15
Eddie Vedder speaks Polish during a concert in Poland
I just found a video and a scan of the piece of paper with Eddie's speech.

But even with such help it still isn't easy to fully understand what he says. :)
And to have it all in one place, here's the setlist of the show they gave on 2007.06.13 in Chorzów, Poland.

But even with such help it still isn't easy to fully understand what he says. :)
And to have it all in one place, here's the setlist of the show they gave on 2007.06.13 in Chorzów, Poland.
2007/05/30
KeePassX looks ugly; making it look good again
If KeePassX (or other Qt application) looks really awful on your machine, the problem is probably the GUI Style chosen for Qt applications.
For quite a long time I couldn't find a way to change it, until I read this howto. I installed the qt4-qtconfig package and used the Qt Configuration tool (run qtconfig) to change the GUI Style to Plastique. You can see the results below.
Before

After
For quite a long time I couldn't find a way to change it, until I read this howto. I installed the qt4-qtconfig package and used the Qt Configuration tool (run qtconfig) to change the GUI Style to Plastique. You can see the results below.
Before

After
2007/05/28
Neostrada ADSL + Linux + Siemens SpeedStream 4100 modem
If you have a Neostrada ADSL connection and a Siemens SpeedStream 4100 modem, it is really easy to configure it on Linux. You only have to:
- edit the /etc/network/interfaces file and configure the card connected to the modem (you need to set the IP address, the gateway, the netmask and the broadcast address - your provider should give you all this data)
- execute /etc/init.d/networking restart
2007/05/16
A SQL CLR user-defined aggregate - notes on creating and debugging
At the end of this post you will find my very first attempt to use one of the new features of SQL Server 2005 - CLR integration. It is a CLR user-defined aggregate that produces a comma-separated (comma plus space to be precise) list of values.
Global variables
You may expect (at least I did) that if you have a global variable (e.g. intermediateResult in my example) and you assign it a value in the Init method then you will be able to use this value in the Terminate method. Well, that's not true.
If you need to use a value of some variable in the Terminate method, you have to serialize it in the Write method (to save the needed value) and then deserialize it in the Read method and assign to some variable (to restore the needed value).
You can of course save and restore more than one variable - use some kind of serializable object to store the values.
Debugging
There is an MSDN article that explains exactly how to debug a CLR user-defined aggregate - Walkthrough: Debugging a SQL CLR User-Defined Aggregate - nonetheless, I encountered one problem.
I started with a solution that contained two projects - one project contained the aggregate code and the other was for a SQL test script. It is possible to perform debugging in such configuration (I managed to do it, although I am not sure how), but it is a better idea to have a single SQL Server project with both the aggregate code and SQL test scripts. This is what the IDE expects, so following this advice can spare you lots of hassle.
For example, MSDN says that to debug a CLR user-defined aggregate you need to enable CLR Debugging, but it doesn't say that Application Debugging must also be enabled.

When I worked with a single project, Visual Studio enabled this option automatically when needed, while with the two-project solution I had to do it myself.
Column1 -------- aaa bbb --> aaa, bbb, ccc cccMy code is a modified version of an example that I found here.
Global variables
You may expect (at least I did) that if you have a global variable (e.g. intermediateResult in my example) and you assign it a value in the Init method then you will be able to use this value in the Terminate method. Well, that's not true.
If you need to use a value of some variable in the Terminate method, you have to serialize it in the Write method (to save the needed value) and then deserialize it in the Read method and assign to some variable (to restore the needed value).
You can of course save and restore more than one variable - use some kind of serializable object to store the values.
Debugging
There is an MSDN article that explains exactly how to debug a CLR user-defined aggregate - Walkthrough: Debugging a SQL CLR User-Defined Aggregate - nonetheless, I encountered one problem.
I started with a solution that contained two projects - one project contained the aggregate code and the other was for a SQL test script. It is possible to perform debugging in such configuration (I managed to do it, although I am not sure how), but it is a better idea to have a single SQL Server project with both the aggregate code and SQL test scripts. This is what the IDE expects, so following this advice can spare you lots of hassle.
For example, MSDN says that to debug a CLR user-defined aggregate you need to enable CLR Debugging, but it doesn't say that Application Debugging must also be enabled.

When I worked with a single project, Visual Studio enabled this option automatically when needed, while with the two-project solution I had to do it myself.
using System; using System.Data; using Microsoft.SqlServer.Server; using System.Data.SqlTypes; using System.IO; using System.Text; [Serializable()] [SqlUserDefinedAggregate( Format.UserDefined, IsInvariantToNulls=true, IsInvariantToDuplicates=false, IsInvariantToOrder=false, MaxByteSize=8000)] public class Concat : IBinarySerialize { #region Private fields private string separator; private StringBuilder intermediateResult; #endregion #region IBinarySerialize members public void Read(BinaryReader r) { this.intermediateResult = new StringBuilder(r.ReadString()); } public void Write(BinaryWriter w) { w.Write(this.intermediateResult.ToString()); } #endregion #region Aggregation contract methods public void Init() { this.separator = ", "; this.intermediateResult = new StringBuilder(); } public void Accumulate(SqlString pValue) { if (pValue.IsNull) { return; } if (this.intermediateResult.Length > 0) { this.intermediateResult.Append(this.separator); } this.intermediateResult.Append(pValue.Value); } public void Merge(Concat pOtherAggregate) { this.intermediateResult.Append(pOtherAggregate.intermediateResult); } public SqlString Terminate() { return this.intermediateResult.ToString(); } #endregion }
Labels:
.NET,
CLR integration,
Microsoft,
programming,
SQL Server,
Visual Studio
2007/05/14
How to improve sub-pixel font rendering for Feisty (from Ubuntu Guide)
The fonts look way better out-of-the-box in Ubuntu 7.04 than they used to in previous versions (I wrote about this before - 1, 2), but they can still be improved.
How to improve sub-pixel font rendering for Feisty
In my opinion, the results are really impressive.
How to improve sub-pixel font rendering for Feisty
This will dramatically improve the appearance of fonts with respect to the default Ubuntu install. The patched libraries are built against Freetype 2.3.x (not currently in feisty) and include David Turner's sub-pixel rendering patches.
In my opinion, the results are really impressive.
Subscribe to:
Posts (Atom)