Blog ala Vidar

SQL, AppFrame and other cool technologies

Tag Archives: SQL

Active Directory support in AppFrame

Leave a comment Posted by Vidar Nordnes on September 4, 2009

Most of you probably know what AD (Active Directory) is, but for you who don’t, it’s a (LDAP) directory service from Microsoft. This enables you to have all user, group and policy management in one place, instead of on all your servers. Most of the systems engineers in Omega have an AD user, which is a member of the SystemsEngineers group. This group has access to the test servers most system engineers need access to. Before we started using AD, when a new guy got hired, we had to manually go in to every server he needed access to, create a local user and add him to the local administrators-group. Now we just add a new user in AD, add him (or her) as a member of the SystemsEngineers group and he is now able to log on to all the servers he needs to, with the same username and password. Neat?

In AD you also got something that’s called an organization unit (OU). This is used to separate objects in the directory. For example could there be one OUs called Omega, OmegaIAT, and OmegaPS. Under these we would put the users, groups etc associated with those OUs. You can also have nested OUs, which means that in the Omega OU you can have one OU called Users, another called Groups, and a third called Servers. This is just to make things easier to find in the directory.

So, what does this got to do with AppFrame? Most of our customers, and also our selves are using AD, and it would be nice to be able to log on automatically into AppFrame without having to type your username and password, right? We already support this, in some degree. You can add a user to AppFrame just by adding his username. For example “COMPANY\User1”. But, what if you want to have a group in AD where you can add users, and those users would automatically be added in AppFrame? Here is an example of code that you would need. For this example we’ll use the domain “MyCompany.com”. Our domain admin have created a group called “AppFrameUsers” which is placed in the OU Office1, Department1. (Department1 is a sub OU of Office1).

First we need to create a linked server from SQL to AD:

EXEC master.dbo.sp_addlinkedserver
	@server = N'AD1',
	@provider=N'ADSDSOObject'

EXEC master.dbo.sp_addlinkedsrvlogin
	@rmtsrvname=N'AD1',
	@useself=N'False',
	@locallogin=N'JobLogin',
	@rmtuser=N'MyCompany\MyUser',
	@rmtpassword='MyDomainPassword'

We create a server “AD1”, where we link the login (“JobLogin”) that is going to run the script under in the job to our domain user (“MyCompany\MyUser”).

DECLARE @SQL AS NVARCHAR(MAX)
DECLARE @Domain AS NVARCHAR(20) = 'MyCompany'
DECLARE @TopDomain AS NVARCHAR(6) = 'COM'
DECLARE @OrgUnit AS NVARCHAR(200) = 'OU=Department1,OU=Office1'
DECLARE @GroupName AS NVARCHAR(200) = 'AppFrameUsers'
DECLARE @TemplateUser AS NVARCHAR(128) = 'AppFrameTemplateUser'

SET @SQL = '
	SELECT
	  givenName AS FirstName,
	  sn AS LastName,
	  mail AS EMailAddress,
	  sAMAccountName AS Username,
	  mobile AS MobileNumber
		INTO ##ADUsers
		FROM OPENQUERY(adsi3, ''SELECT givenName, sn, mail, sAMAccountName, mobile
				FROM ''''LDAP://DC=' + @Domain + ',DC=' + @TopDomain + '''''
				WHERE objectCategory= ''''Person''''
				  AND objectClass = ''''user''''
				  AND memberOf = ''''
					CN=' + @GroupName + ',' + @OrgUnit + ',
					DC=' + @Domain + ',
					DC=' + @TopDomain + ''''''')
		WHERE NOT EXISTS (SELECT *
					FROM stbl_System_Users
					WHERE Login = ''' + @Domain  + ''' + sAMAccountName)'

EXEC (@SQL)

WHILE EXISTS (SELECT * FROM ##ADUsers)
BEGIN
	SELECT TOP 1
	  @FirstName = FirstName,
	  @LastName = LastName,
	  @EMailAddress = @EMailAddress,
	  @Login = Username,
	  @MobileNumber = MobileNumber
		FROM ##ADUsers

	EXEC sstp_Security_Users_Create
		@Login = @Login,
		@FirstName = @FirstName,
		@LastName = @LastName,
		@UserEMailAddress = @EMailAddress,
		@UserSMS = @MobileNumber

	EXEC sstp_Security_Users_CopyGroupsMemberships
		@FromUser = @TemplateUser,
		@ToUser = @Login

	DELETE
		FROM ##ADUsers
		WHERE Username = @Login
END

DROP TABLE ##ADUsers

This script will create the logins that doesn’t already exist in the system and copy AppFrameTemplateUser’s permissions to the logins. Running this script every night or similar will make it much easier for our customers to add users to AppFrame (or PIMS).

NB! Please notice that the @OrgUnit is “backwards”, meaning that the top level OU is on the right. Office1->Department1 is therefore OU=Department1,OU=Office1

Published Active Directory, AppFrame, SQL

Azure

Leave a comment Posted by Vidar Nordnes on August 25, 2009

What does Azure mean? Blue sky. But what does Microsoft mean with this? You might have heard of “the cloud”. Storing data in the cloud etc. This is a term that is very popular nowadays. The point here is that your data (or applications) are stored “in the cloud”, which means that you don’t have a relationship to where it’s actually stored or executed. I just created a database in Microsoft’s SQL Azure. I have NO idea where this database is, but I can access it through management studio or similar. There are a lot of other things you can do “in the cloud”. You can store your pictures, make it do heavy calculations etc. Some have been dreaming about having a screen (TV like) where all the actual computing is done “in the cloud”. I think it’s really interesting, because most of our computers are idling most of the time anyways.

So, why am I writing about this? Arild posted a blog about SQL Azure CTP earlier which started a discussion if we should support it. That lead me to try to make AppFrame “Azure compatible”. After a couple of hours, I’ve made my decision: If we should go “Azure”, we need to do it from scratch. There are so many things that are not supported by the SQL Azure which are imho show stoppers. For starters, you can not connect to it using management studio. Correction, you CAN, but you don’t get the object browser, and it’s very limited. The unsupported features are a long list. This in it self isn’t a big problem, because we don’t use most of them, but here’s a list of those we do use, and a little description.

SUSER_SNAME() – This is used EVERYWHERE. Literally! Triggers, views, defaults etc
NEWSEQUENTIALID() – Can be replaced by NEWID(), but you loose performance
WITH (NOLOCK) – Can be removed, but can lead to performance issues and lock-problems
FullText Indexing – Can of course be replaced by normal WHERE-clauses, but this will be a performance issue.

This is the biggest problems I’ve found so far, and I’ve only scripted tables and views. In addition, you don’t have sysobjects, syslogins, sysusers etc. Making AppFrame Azure compatible IS possible, but it would in my mind be just as time consuming as making it MySQL compatible. I’m sure Microsoft will add more features. This is only the CTP, so I hope they’ll at least add SUSER_SNAME and NEWSEQUENTIALID. The others we CAN live without.

So, to summarize. Should we be thinking to “go to the cloud”? No, at least not yet. I see it as a MySQL replacer. It’s great for small applications, but when you want to do real enterprise-stuff, you run MSSQL Server Enterprise Edition 🙂 One other thing to keep in mind is where is my data stored? All companies on in the US needs to be SOX compliant. This might be a problem for Azure. Don’t get me wrong. I’m positive about Azure, but not as an enterprise database!

If you want to test out Azure SQL, go to http://connect.microsoft.com and sign up for Azure SQL.

NB! We’ve got two new bloggers (from TGE). Looking forward to their postings 🙂
Tim Bosinius
Florian Biermaier

Published Azure, SQL, SQL Server

Comma Separated List in SQL

Leave a comment Posted by Vidar Nordnes on August 19, 2009

What do you do if you got some records of for example users and you want to return them in a comma separated list? Most of you will probably do something like this:

SET NOCOUNT ON

CREATE TABLE MyTable (
	Login NVARCHAR(128)
)

INSERT INTO MyTable
  (Login)
VALUES
  ('Vidar'), ('Johnny'), ('Peter'), ('JanCB'), ('Georgij'), ('Tomas'), ('Torgeir')

DECLARE @Logins AS NVARCHAR(MAX)
DECLARE @Login AS NVARCHAR(128)

SELECT Login
	INTO #MyLogins
	FROM MyTable

WHILE EXISTS (SELECT * FROM #MyLogins)
BEGIN
	SELECT TOP 1
	  @Login = Login
		FROM #MyLogins

	IF @Logins IS NULL
	BEGIN
		SET @Logins = @Login
	END
	ELSE
	BEGIN
		SET @Logins = @Logins + ', ' + @Login
	END

	DELETE
		FROM #MyLogins
		WHERE Login = @Login
END

DROP TABLE #MyLogins
DROP TABLE MyTable

PRINT @Logins

Yes, I know. This is not best practice with creating two tables etc…. but you get my point. Looping through a record set and add a field to a variable. But, why not just do:

SET NOCOUNT ON

CREATE TABLE MyTable (
	Login NVARCHAR(128)
)

INSERT INTO MyTable
  (Login)
VALUES
  ('Vidar'), ('Johnny'), ('Peter'), ('JanCB'), ('Georgij'), ('Tomas'), ('Torgeir')

DECLARE @Logins AS NVARCHAR(MAX) = ''

SELECT @Logins = @Logins + Login + ', '
	FROM MyTable

DROP TABLE MyTable

PRINT SUBSTRING(@Logins, 1, LEN(@Logins) - 1)

This works in SQL Server 2005 too. You just have to SET @Logins = ‘’ after you declare it. And you need to split up the values to be inserted.

Published SQL, SQL Server

NULL?

Leave a comment Posted by Vidar Nordnes on October 27, 2008

A couple of days ago Torgeir came over to my desk and told me proud “you shouldn’t use SELECT when setting variables. You should use SET”. Then he told me the reason, and it got me thinking.. The reason is simple. If you do this:

DECLARE @PrimKey AS UNIQUEIDENTIFIER
SELECT @PrimKey = PrimKey
	FROM stbl_WinClient_Projects
	WHERE ProjectName = 'Sys.Security.ManageUsers'
PRINT @PrimKey
SELECT @PrimKey = PrimKey
	FROM stbl_WinClient_Projects
	WHERE ProjectName = 'A.Project.That.Does.Not.Exist'
PRINT @PrimKey

Notice that it will return the same PrimKey both times. When you’re writing the query, you might think that this will give you NULL on the last SELECT, but it won’t because there’s no records returned, and it will therefore not touch the variable. The correct way of doing this would then be:

DECLARE @PrimKey AS UNIQUEIDENTIFIER
SET @PrimKey = (SELECT PrimKey
		FROM stbl_WinClient_Projects
		WHERE ProjectName = 'Sys.Security.ManageUsers')
PRINT @PrimKey
SET @PrimKey = (SELECT PrimKey
		FROM stbl_WinClient_Projects
		WHERE ProjectName = 'A.Project.That.Does.Not.Exist')
PRINT @PrimKey

In some cases you might want to add ISNULL. For instance in subselects, this might be important. In the next query you’ll notice what I’m talking about. The first column will return NULL, and the other will return 0.000000:

SELECT
  (SELECT TOP 1 ISNULL(Amount, 0) AS Amount
	FROM atbv_Accounting_Transactions
	WHERE Series = 'SomethingThatDoNotExists'
	ORDER BY Period DESC) AS Amount1,
  ISNULL((SELECT TOP 1 Amount AS Amount
	FROM atbv_Accounting_Transactions
	WHERE Series = 'SomethingThatDoNotExists'
	ORDER BY Period DESC), 0) AS Amount2

Both queries are using ISNULL, but the first is using INSIDE the subquery, but since this will never return any records, it will never come that far.. The second uses ISNULL OUTSIDE the subquery, so if the subquery returns NULL, it will replace it with 0.0000

NB! One last thing… Some bright souls may have noticed that I haven’t used NOLOCK on the first queries. I’ll send you a price. Good boy/girl 😀 ALWAYS REMEMBER TO USE WITH (NOLOCK) WHEN SELECTING FROM TABLES!

Published SQL, SQL Server

Table Valued Parameters?

Leave a comment Posted by Vidar Nordnes on October 22, 2008

This is a new feature to SQL Server 2008, where you can send a table to a SP or UDF. I’ve got the question about how to handle this in 2005 many times, and I’ve now decided to post a blog about it. Before you read more about this, please consider. IS IT REALLY NECCESSARY to do this from the client? Can we do everything on the SQL Server? If so, DO IT! My following example can all be done on the server, but this is only to show you how to do it if you really need to get it from the client.

Let’s say we got a bunch of data we want to send to a SP. In this case, I’ve just built up a dataset from stbv_System_Users, where I’ve got one column, Login.

Dim vUsersDataSet As New DataSet
Dim vUsers As New afRecordSource("stbv_System_Users")

vUsers.SelectColumns.Add("Login")
vUsers.MaxRecords = 10

vUsersDataSet.Tables.Add(afAccessLayer.GetData(vUsers))

Dim vProcedure As New AppFrame3.Common.Data.afProcedureCall
vProcedure.ProcedureName = "astp_Vidar_Test1"
vProcedure.Parameters.Add("MyXML", vUsersDataSet.GetXml().ToString())

Try
	AppFrame3.Common.Data.afAccessLayer.ExecProcedure(vProcedure)
Catch ex As Exception
	AppFrame3.Win.CR3.HandleException(ex)
End Try

Here you see I’ve put it in a dataset, then used .GetXml to send it as a parameter to my SP:

CREATE PROCEDURE astp_Vidar_Test1 (@MyXML AS XML)
AS

DECLARE @DocID AS INT

EXEC sp_xml_preparedocument @DocID OUTPUT, @MyXML

SELECT *
	FROM OPENXML(@DocID, '/NewDataSet/Table1', 2)
	WITH (Login NVARCHAR(128))

EXEC sp_xml_removedocument @DocID

My SP takes one parameter, and “converts” it to SQL using OPENXML. Now I could go on, inserting these records somewhere. OPENXML takes three parameters. First one is docid, which is created by sp_xml_preparedocument. Second one is the path in the XML you want to handle. Third one is what type of input it is. If it’s with elements or attributes. 2 is elements, 1 is attributes. Under you’ll see the XML created by the .GetXml method on the dataset. I don’t care about the PrimKey field, so I’ve only specified the Login field in the WITH clause.

<NewDataSet>
  <Table1>
    <Login>vidar</Login>
    <PrimKey>63444aa2-e0b8-4c89-b476-229c81145961</PrimKey>
  </Table1>
  <Table1>
    <Login>vidar2</Login>
    <PrimKey>e72d74d0-3c8f-4256-b188-8001557d9849</PrimKey>
  </Table1>
</NewDataSet>

Published SQL, SQL Server

Common mistakes

Leave a comment Posted by Vidar Nordnes on October 3, 2008

When doing QA on SQL-objects, I’ve come over some very common mistakes. Number one has to be people forgetting that the Inserted and Deleted views inside triggers may contain more than more record.
This is a very good (and simplyfied) example of code from an insert trigger:

DECLARE @Login AS NVARCHAR(128)

SELECT @Login = Login
	FROM Inserted

IF NOT EXISTS (SELECT * FROM stbl_System_Users WITH (NOLOCK) WHERE Login = @Login)
BEGIN
	INSERT INTO stbl_System_Users (Login) VALUES (@Login)
END

This will only work if you’re inserting one record, but what if you’re inserting several? It will only do the code you’ve added for ONE (random) record.
The solution for this is very simple:

INSERT INTO stbl_System_Users
  (Login)
SELECT
  I.Login
	FROM Inserted AS I
	WHERE NOT EXISTS (SELECT *
				FROM stbl_System_Users WITH (NOLOCK)
				WHERE Login = I.Login)

In some cases you might want to execute a SP. Instead of INSERT, then EXEC:

DECLARE @Login AS NVARCHAR(128)

SELECT @Login = Login
    FROM Inserted

IF NOT EXISTS (SELECT * FROM stbl_System_Users WITH (NOLOCK) WHERE Login = @Login)
BEGIN
    EXEC astp_Some_SP @Login = @Login
END

Solution to this:

DECLARE @Login AS NVARCHAR(128)

SELECT  I.Login
	INTO #LoopLogins
	FROM Inserted AS I
	WHERE NOT EXISTS (SELECT *
				FROM stbl_System_Users WITH (NOLOCK)
				WHERE Login = I.Login)

WHILE EXISTS (SELECT * FROM #LoopLogins)
BEGIN
	SELECT TOP 1
	  @Login = Login
		FROM #LoopLogins

	EXEC astp_Some_SP @Login = @Login

	DELETE
		FROM #LoopLogins
		WHERE Login = @Login
END

Published SQL, SQL Server

Query Optimization

Leave a comment Posted by Vidar Nordnes on July 10, 2008

When doing query optimization, there is a couple of things to remember:

NEVER USE “OR”!
This KILLS performance! Instead of using OR, use UNION (ALL) or IN

UNION vs UNION ALL
The performance difference between these are big. UNION first groups and then orders. UNION ALL just adds the resultsets together

EXISTS vs IN
In many cases, you can use:

SELECT *
	FROM table1
	WHERE Login IN (SELECT Login
				FROM table2)

instead of using:

SELECT *
	FROM table1
	WHERE EXISTS (SELECT *
			FROM table2
			WHERE table2.Login = table1.Login)

Subselects vs JOIN
In some cases, like in the queryoptimizing webcast, subselects are MUCH quicker than using JOIN. This is not the case in all queries, so test both!

WITH (NOLOCK)
When you use NOLOCK, you read uncommited data from the table. This means that there MIGHT be data that are not written to the table yet. In most of our cases this is not a problem.

DO NOT USE *
… unless you need it. When using *, you can’t use indexes to select fields. Using a * forces the execution plan to select from the table.

Indexes and Statistics
Always make good indexes! Make sure indexes and statistics are rebuild when they need to! I’ll post an input here later on about indexes and statistics.

Published Indexing, SQL, SQL Server

7. Nov; Match-day

Leave a comment Posted by Vidar Nordnes on November 8, 2007

I’m never going to get used to Øystein waking me up. We had some breakfast before we headed over to CCIB. First session started at 10:45:
DAT307, Best Practices for Optimizing Procedural Code and Queries in SQL Server 2005 and Beyond with Bob Beauchemin.

This session was not about query-tuning. It was more about understanding the execution plan, and what other things that’s going on when you press F5. Pretty interesting!
Rating: 3

Arriving late means early lunch. Pasta, sausages and potatoes are actually an ok mix! In the lunch we decided to go to something which seemed fancy. Not work related at all, but seemed very interesting after all:
MED03-IS, Microsoft Robotics Studio Demonstrations with Oliver Bloch
Admit it! It sounds really fun! Well, I’ll tell you. It was fun, but not the demonstration in itself. First of all, he is French, so his accent is funny. Second of all, I’m pretty sure he said fuck at least 5 times. He probably said something else, but I couldn’t help myself. I started giggling like a little girl. After about 10 minutes, I couldn’t stand it anymore. He were just repeating himself, and showing a whole lot of powerpoint slides with TEXT. No code, no screenshots, no demos, no nothing. BORING!
Rating: 9

Still giggling, I went down to the exhibition hall, again and walked around a couple of minutes before Øystein and Jan Leon joined me. They couldn’t stand the French dude anymore! We bought 16 books at a Microsoft-stand. 40% discount on all books from Microsoft Press, and 30% on everything else! Total sum: 442€. That’s 28€ pr book. In Norway, they cost about 60-75€!

After leaving our bags at the hotel, we went shopping. I can, of course, not remember the name of the mall, but it was big! Went to the top floor to get some food, and got the worst service EVER! First the waiters ”didn’t” see us, then when we got a menu and tried ordering, the waiter took the menu from us and just said ”NO FOOD!” and left. We couldn’t get an explanation from them, so we left in anger. Beate (one of Ronny’s little helpers) sais she gets good service wherever she goes. Why do three guys, that look like Rangers supporters, not get good service?! I can’t figure it out… Anyhow, found a restaurant after HOURS of walking, and then went back to the hotel. Next: FOOTBALL!

Today is Champions League. For those who haven’t heard of this (USA-dudes), football is when you only use your feet to kick the ball around, not arms and violent tackles! We followed some Rangers-supporters to the Metro. Scottish people know how to drink, and knows how to entertain themselves! We had fun watching them sing all the way to Camp Nou, and learning how to act as a (brain-dead) Rangers-supporter. I’ve never been to a game this big! 90.000 supporters! That’s more than Haugesund’s poor 4000. I got goose bumps! This is NOT going to be my last match! Anyways, the game ended Barcelona 2, Glasgow Rangers 0. Goals by Henry and Messi.

On our way home, we dropped in at Hard Rock Cafe, where the Norwegian participants of TechEd had a gathering. Free beer! We showed up at about 23:30, and they’d been drinking since 19:00. You think they were drunk. YEPP! We had a couple of beers, tried talking to a couple of people, but no luck. One of my conversations were
Vidar: ”What do you do?”
Norwegian-dude 1: ”I’m developing”
Vidar: ”Ok. What are you developing?”
ND1: ”Systems”
Vidar: ”Oooook. See you!”
I’m not a fan of talking to people that are drunk. At least not when I’m sober!

Tomorrow, there’ll be more interesting sessions. I’ve figured out I’m not going to go to any more non-interesting sessions, since we’re getting DVDs of all sessions in (snail)mail in a couple of weeks.
Quick overview of free things I’ve got from the exhibition hall: Pens (even with lights), stress balls, installation CDs/DVDs, t-shirts, advertisements, caps and even a deck of cards!

Published Conferences, SQL

Tired!

Leave a comment Posted by Vidar Nordnes on November 8, 2007

Today I woke up by myself at 10am, REALLY tired. After some breakfast we were on our way to get lunch at CCIB. I’ve only been to two sessions today.

DAT313, SQL Server 2005 Security and Innovations in SQL Server 2008 Security, with Sethu Kalavakur.
In this session, he talked much, but had a couple of demos. Including encryption of whole databases and auditing. I think we can use auditing when it comes to SOX, because you can not only log who’s inserting, updating and deleting from tables, you can also log who’s selecting! His language was very understandable, and he knew what he was talking about.
Rating: 4

DAT01-IS, Tell Us Where it Hurts! SQL Server Product Feedback Discussion, with Micael Rys, Steve Lasker, Carl Perry, Cristian Petsculescu, Sethu Kalavakur and my hero Bob Beauchemin.
This was actually a pretty fun (interactive) session. No demos, only Q&A. All of the speakers are employees of Microsoft, except Bob. But, he seemed to be the one with most knowledge. He answered questions about EVERYTHING from Reporting Services to hard-core memory handling. He is without doubt my hero! I think I’m going to start “Bobs fanclub”. Anyhow, I got to ask a question I’ve been dying to ask for a while: “When is row level security coming inside SQL Server”. Mr. Kalavakur told me they actually had it in the first CTP of SQL 2005, but it got too complex to finish till the end release of 2005, so they removed it. But he ensured me it was number two on his list of things to come in the next release of SQL Server AFTER 2008. He added that this could easily be done by using views, just like we do, so we’re probably doing it “by the book” ? This was a very informative session, because we got to know how the development process worked in Microsoft, very detailed answers on difficult questions etc. I was VERY pleased with this session!
Rating: 2

Before these sessions I was walking around the exhibition hall, again. Tried to ask, not less than 4 people, about an issue we’re having in SQL 2005. This is the new “user must change password on next login” feature. The problem is that you will not be able to log in if you have to change the password. We have to send the old, and the new password in the connection-string, because since we can’t connect, we can’t execute a stored procedure to change the password. I found one solution, I thought, but this didn’t work. So, anyway… I asked two people at the SQL stand with green “ask the expert” t-shirts. They had no idea how to solve this. They pointed me to a guy at the office-stand. By the way, never tell anyone from Microsoft, or any other company, you’re using Access. They do not take you seriously. The office-dude didn’t know either, so I went to a ODBC-stand. You think he knew what he was talking about? Not at all. He hadn’t even heard of SQL Server Management Studio! After a while the people at the “Microsoft Unified Communication” (the once that deal with all communication APIs) showed up so I asked one of them. He knew the answer, but didn’t remember it, so I got his email.

Today’s free material: Windows Server 2008 e-book (on DVD), security development book, a couple of SQL Server magazines, yo-yo, caps, and last but not least one T-shirt (for all MCPs) which pretty much sums me up! On the front it says “Geek”, and on the back it says:
geek (‘gek), noun.
1. Obsessive Computer User: somebody who enjoys or takes pride in using computers or other technology, often to what others consider an excessive degree.
2. Someone with greater than normal computer skills.

After getting back to the hotel, we visited a tapas-place, again. Great food, great beer, great service and beautiful girls!

Published Conferences, SQL