Temp Tables in SQL Server

We all know you can create variables in SQL Server...


declare @customer varchar(20)

set @customer='a customer'



...but what if there is a need to store more complex data?

As it turns out, there is an easy way to accomplish that as well.




declare @csrlist Table(customer varchar(20), CSR varchar(25), counts int)


--get the counts of customer service reps orders for each customer.

insert into @csrlist (customer, csr, counts)
(
select distinct customer, Csr, COUNT(1) counts
from Purchase_Order
where Csr is not null
group by customer, csr
) 

The resulting in-memory table can be inserted to, deleted from, updated, just like any real data table.


--find the CSR with the most orders for each customer

insert into @csrlist2 (customer, CSR)
    (select customer, Csr from @csrlist A where counts=(select MAX(counts) from @csrlist B where a.customer=b.customer))




---

Bryan Valencia is a contributing editor and founder of Visual Studio Journey.  He owns and operates Software Services, a web design and hosting company in Manteca, California.

Cannot execute as the database principal.

Cannot execute as the database principal because the principal "username" does not exist, this type of principal cannot be impersonated, or you do not have permission.

I am going to read your mind now.

1. You recently backed up your database copy-only and moved it to another server or development box. 


2. You're attempting to perform an  "Execute As..." command.


3. Your software has been running for some time and this new error just started cropping up after you "refreshed" your copy of the database (from production?).


4. You looked at the server logins, and the database users, and they seem to match (there is a login with the same name as the user).

What happened is that the SIDs (Security IDs) from the server login does not match the database user of the same name.  Remember that LOGINS are stored at the server level and USERS are in the databases.



What you need to do is re-create the user in the database (and reassign any roles and permissions).

USE [myDB]

GO



/****** Object:  User [myUser]    Script Date: 01/14/2013 18:21:22 ******/

IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = N'myUser')

DROP USER [myUser]

GO



USE [myDB]

GO



/****** Object:  User [myUser]    Script Date: 01/14/2013 18:21:22 ******/

GO



CREATE USER [myUser] FOR LOGIN [myUser] WITH DEFAULT_SCHEMA=[dbo]

GO

...

---

Bryan Valencia is a contributing editor and founder of Visual Studio Journey.  He owns and operates Software Services, a web design and hosting company in Manteca, California.

SQL Server Matching on NULL parameter

So I have this query where I am trying to select on customer, unless the user doesn't enter a parameter for customer.  When they leave it blank, we want to see all customers.



So normally I would do it like this:




Select * from Orders where Customer=@customer
And then to handle the null parameter I would change it like this:




Select * from Orders where ((Customer = @customer) OR (@customer is null))
 This works great but then I came across this way of making it simpler.




Select * from Orders where Customer = isnull(@customer, Customer)
The isnull()  effectively handles the case where the parameter (@customer) is null by replacing it with the content of the [Customer] data column, matching to itself!  Problem solved!





...

---

Bryan Valencia is a contributing editor and founder of Visual Studio Journey.  He owns and operates Software Services, a web design and hosting company in Manteca, California.

Getting the Identity Of Inserted Row in Visual C#

This works to return the identity of an inserted row.  The highlighted portions are the important parts.




 private int insertnew()
        {
            int newtix = 0;
            string SQL1 = "insert into delivery (date) values (GETDATE()); SELECT CAST(scope_identity() AS int)";
            SqlCommand myCommand = new SqlCommand(SQL1, myConnection);

            try
            {
                newtix = Convert.ToInt32(myCommand.ExecuteScalar());
            }
            finally
            {
                myReader.Close();
            }

            return newtix;
        }
Note that

1. there are 2 SQL commands in the one SQL string.


2. I had to cast the Scope_Identity as an int in the SQL or it would not read.


3. ExecuteScalar reads the FIRST column in the FIRST row only, but if there were a compound key, or multiple rows inserted, ExecuteReader can be used instead.

---

Bryan Valencia is a contributing editor and founder of Visual Studio Journey.  He owns and operates Software Services, a web design and hosting company in Manteca, California.

MSSQL: Find the next available number in a sequence

 So I have this data table, with a non-key column, and I need to write a SQL Query to find the next unused number in the sequence that is greater than a certain starting number.



It's like this:

A long time ago, the table started somewhere around 1000.  This is not a key column, and some of the older records have been archived out of the table.  So I have a weird number that will serve as my minimum.   BUT! there are also some wacky big numbers in the system and I don't want to do a max(n)+1.



Here's a beakdown:

0-35956	these numbers are there, with lots of missing numbers and gaps due to archiving.
35957-1904121900	a few of these are there, I want to find the next available number in this region.
1904121901 and up	I want to ignore these numbers, and not use them (until all the numbers leading up to here are filled).

My Goal here is to create a SQL Query I can run once to find the next available number (meaning the lowest number above 35957 that does not exist in the column).



Here is what worked.




select min(Item_No)+1 as NextID

from Data_Table DT

where not exists

(select 1 from Data_Table DT2 where DT2.Item_No=DT.Item_No+1)

and Item_No > 35956



...

---

Bryan Valencia is a contributing editor and founder of Visual Studio Journey.  He owns and operates Software Services, a web design and hosting company in Manteca, California.

Avoiding SQL Injection Attacks

Take a simple SQL Query.




Select account_number from users where username='USER' and password='PASSWORD'
This might be the kind of query you'd use to see if a user entered his username and password correctly.  If you get an account_id then the user is logged in.



So, you put a couple of text boxes on a login page and try to pass the values from the text boxes to the query, like this.




string USER=tbUser.text;

string PASSWORD=tbPassword.text;

string SQL="Select account_number from users where username='" + USER + "' and password='" + PASSWORD + "'"

That way if the user types in "Bob" and "MyPassword" the query executed looks like this.




Select account_number from users where username='Bob' and password='MyPassword'
 Perfect.  This works great, and was a pretty standard way of doing this kind of query for a very long time, dating back to before the world wide web.  But what if a SQL-savvy hacker wants to see what kind of mischief he can cause, and tries playing with your SQL's head.  What if he entered something like this...



Username: ' or ''='

Password: ' or ''='



What does that do to our query?




Select account_number from users where username='' or ''='' and password='' or ''=''
There is a really good chance that this query will bring back all the records in your database, and then think that this user is correctly logged into the very first one.  Note that login queries are not the only kind that can be hacked this way, but they are the easiest targets.  It could just as easily be your help ticket system, or anything else exposed to the web.



This is called a SQL injection attack. It's very common, but luckily it's very easy to thwart.  But there are some programmers who go about it the wrong way.



How not to protect yourself.

1. Don't think your site is too small to get noticed and attacked.


2. Don't  rely on Javascript, as it is easily disabled.


3. Don't rely on Flash, as there are still people who hate it and will not load it on their browsers.


4. Don't rely on HTML settings like maximum character lengths. They can be dispatched.

How to protect yourself.

An easy way to render SQL Injection attacks ineffective is to use sql parameters.  The not so easy part of that - if you have a massive web site - is that you have to edit ALL of your SQL that is exposed to the web.  in SQL Server, you do that like this:


string SQL="Select account_number from users where username=@USER and password=@PASSWORD";
Then, in your query you use parameters to fill in the values, like this:

myCommand.Parameters.AddWithValue("@USER", username.text);

myCommand.Parameters.AddWithValue("@PASSWORD", password.text);
Now (at least in SQL Server), no matter what they enter, it will be treated as query values.  if there is no user who's name and password are ' or ''=', then they will not be logged in.  I know it's a hassle to recode all your website queries.  But nowhere near as big a hassle as having a massive data breach to deal with.





...

---

Bryan Valencia is a contributing editor and founder of Visual Studio Journey.  He owns and operates Software Services, a web design and hosting company in Manteca, California.

Using a Stored Procedure that Returns Multiple Results

Microsoft did a brilliant thing in SQL Server.  I applaud them for this.  You can actually pack more than one SQL Query into a stored procedure and have a single procedure return multiple result sets in one pass.  So I used this feature to write a stored procedure that gathers all the data I need for a certain .rdlc report in my app.  Now I can use this one stored procedure to gather all the data in one fell swoop.

I have done this kind of multiple result set stored procedures in the past and it's a simple matter in code to sort out the data tables, as they are returned in order. Here is some sample code for another instance of this method.

/// <summary>
/// gets the dataset from the database.
/// </summary>
public void Load_Data()
{
    SqlCommand myCommand = new SqlCommand("[dbo].[ExtractWebData]", myConnection);
    myCommand.CommandType = CommandType.StoredProcedure;

    System.Data.SqlClient.SqlDataAdapter adapter = new System.Data.SqlClient.SqlDataAdapter(myCommand);
    ds = new DataSet();
    adapter.Fill(ds);
    ds.Tables[0].TableName = "Customers";
    ds.Tables[1].TableName = "Purchase_Orders";
    ds.Tables[2].TableName = "Purchase_Order_Details";
    ds.Tables[3].TableName = "Inventory";           
}

Now if there was just some way to use that in a report.  It would be great to use a stored procedure to twist all the data into shape before handing it off to a reporting tool!  So you create an xsd. You add a table adapter.

You tell it to use your existing stored procedure.

 You select your stored procedure, and there's the first dataset... But wait.  How do you tell it where the other result sets are?  I want to use all 3 datasets! 







Except that you can't do that.  You see, the .rdlc report requires that you have the data available at design time in order to design the report.  And there is no way to import multiple datasets at once into the .xsd at design time.



From this document: http://msdn.microsoft.com/en-us/library/dd239331.aspx

If multiple result sets are retrieved through a single query, only the
first result set is processed, and all other result sets are ignored.
For example, when you run the following query in the text-based query
designer, only the result set for Production.Product appears in the result pane:

SELECT ProductID FROM Production.Product
GO
SELECT ContactID FROM Person.Contact

I have no idea what the text-based query designer is, but as we saw in SQL Server Management Studio...

In my opinion, this is an EPIC DESIGN FAIL on the part of Microsoft.



We know from the earlier code snippet that Visual Studio can access the data, it just - for some stupid reason - is designed in such a way as to disable this feature in certain cases.  This is completely unacceptable.  But until Microsoft fixes this glaring, stupid, boneheaded omission, we're stuck with it.



Now, I know this blog has no regular readers.  You didn't find this page because you're a fan of Visual Studio Journey.  You found it because you were googling for this problem, and there were no answers anywhere else.  I wish I had better news, but I don't.



Here is the only way I know to work around this issue.  Unravel your stored procedure and execute the whole thing in your client.  Convert each piece into individual queries, and add those to your .xsd.



Alternately, you could split your stored procedure into multiple pieces, like Proc1, Proc2, Proc3... but then you've kind of lost the convenience of the one-stop shopping the stored procedure offers.



Just to be clear: I think that the ability to return multiple result sets from a stored procedure is awesome!  Kudos to the SQL Server development team.  If only the Visual Studio guys would catch some of that brilliance, things would be great.



Addendum:

Just to make myself clear, I think that this feature has awesome potential and multi-table stored procedures are still incredibly useful in Visual Studio.  They just are not usable for rdlc reports (the one thing they would be most ideally suited for in an ideal world).



...

---

Bryan Valencia is a contributing editor and founder of Visual Studio Journey.  He owns and operates Software Services, a web design and hosting company in Manteca, California.