Rob Kraft's Software Development Blog

Software Development Insights

Archive for the ‘SQL Server’ Category

How To Reduce The Connection Pools For a .Net Web Site Hitting Multiple Databases

Posted by robkraft on May 9, 2020

Microsoft’s connection pooling built in to the .Net Framework greatly improves performance by allowing hundreds of database calls to share the same pool of connections used to connect to the database. The .Net Framework creates a new pool of connections for each unique connection string. This works fantastic when your web application connects to a single database and all your connections use the same connection string. It even works well if you have two or three different connection strings, but for each unique connection string value, the .Net Framework creates a new connection pool. If your web site has dozens of different databases to connect to, you may find your application creates dozens of connection pools which begins to consume a lot of resources on both the web server and SQL Server.

There is a fairly easy way to eliminate this problem, and that is to use the ChangeDatabase method on the Connection object.

Our web application was connecting to one of fifty different databases that all reside on the same instance of SQL Server. Each of our clients has their own database. But our application was creating fifty different connection pools. We were able to have all the clients share a single pool of connections by first connecting to a neutral database, then redirecting the connection to the desired database.

We created a new database on the server, called Central, then we had all the connections to the database first connect to Central as the Initial Catalog. The next step was to call ChangeDatabase to switch to the desired database. This technique did not create a new connection to SQL Server and did not create a new connection pool on the .Net client. Microsoft documentation mentions this technique here: https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql-server-connection-pooling#pool-fragmentation-due-to-many-databases , but their example code does not show the ChangeDatabase method, which is more efficient than their example.

In a nutshell, you can do this:

connection.Open();
connection.ChangeDatabase(“Client1”);

I created a simple Windows Form App to test the idea.  The test app runs a query to return the name of the current database it is in.  Also, on the SQL Server I run a query to see all the connections that exist.  Doing this I was able to prove that using ChangeDatabase did not create a new connection to SQL Server and my .Net SQL Connection Object was pointing at the correct desired database.  Sample code, query, and output is below:

Sample C# .Net Forms App:

using System;
using System.Data.SqlClient;
using System.Windows.Forms;

namespace SQLConnectionTest
{
	public partial class Form1 : Form
	{
		//https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql-server-connection-pooling#pool-fragmentation-due-to-many-databases
		private void button1_Click(object sender, EventArgs e)
		{
			using (SqlConnection connection = new SqlConnection(@"Server=.\;Database=master;Trusted_Connection = yes"))
			{
				connection.Open();
				RunSQL(connection, "select DB_NAME()");
			}
			using (SqlConnection connection = new SqlConnection(@"Server=.\;Database=model;Trusted_Connection = yes"))
			{
				connection.Open();
				RunSQL(connection, "select DB_NAME()");
			}
			using (SqlConnection connection = new SqlConnection(@"Server=.\;Database=msdb;Trusted_Connection = yes"))
			{
				connection.Open();
				RunSQL(connection, "select DB_NAME()");
			}
			using (SqlConnection connection = new SqlConnection(@"Server=.\;Database=master;Trusted_Connection = yes"))
			{
				connection.Open();
				RunSQL(connection, "select DB_NAME()");
			}
			using (SqlConnection connection = new SqlConnection(@"Server=.\;Database=master;Trusted_Connection = yes"))
			{ 
				connection.Open();
				connection.ChangeDatabase("TempDb");
				RunSQL(connection, "select DB_NAME()");
			}

			using (SqlConnection connection = new SqlConnection(@"Server=.\;Database=master;Trusted_Connection = yes"))
			{
				connection.Open();
				RunSQL(connection, "select DB_NAME()");
			}
			using (SqlConnection connection = new SqlConnection(@"Server=.\;Database=master;Trusted_Connection = yes"))
			{
				connection.Open();
				connection.ChangeDatabase("TempDb");
				RunSQL(connection, "select DB_NAME()");
				connection.ChangeDatabase("model");
				RunSQL(connection, "select DB_NAME()");
			}
		}

		public static void RunSQL(SqlConnection connection, string queryString)
		{
			SqlCommand command = new SqlCommand(queryString, connection);
			try
			{
				SqlDataReader reader = command.ExecuteReader(); 
				if (reader != null)
				{
					while (reader.Read())
					{
						Console.WriteLine("\t{0}", reader[0]);
					}
					reader.Close();
				}
			}
			catch (Exception ex){Console.WriteLine(ex.Message);}
		}
		public Form1()
		{
			InitializeComponent();
		}


	}
}

Output from Running the App written to console:

master
model
msdb
master
tempdb
master
tempdb
model

Query to see connections on SQL Server:

CREATE TABLE #TmpLog
(SPID int, Status VARCHAR(150), Login varchar(100), HostName VARCHAR(150), BlkBy varchar(30), DBName varchar(60), Command varchar(500),
CPUTime int, DiskIO int, LastBatch varchar(20), ProgramName varchar(200), SPID2 int, requestid int)
INSERT INTO #TmpLog
EXEC sp_who2
SELECT SPID, Status, Login, HostName, BlkBy, DBName, Command, ProgramName
FROM #TmpLog
where STATUS not in (‘BACKGROUND’) and Command not in (‘TASK MANAGER’) and ProgramName like ‘.net%’
DROP TABLE #TmpLog
go

Results of running query on SQL Server:

Results1

Posted in Coding, SQL Server | Leave a Comment »

How To Filter SP_WHO to Just The Connections You Care About

Posted by robkraft on May 8, 2020

You can run sp_who, or better yet, sp_who2 to quickly get some information about all the current connections to your SQL Server, but for those of us that run it often you probably prefer that it not show all the internal processes connected to the engine.  You can filter the the results of the stored procs by dumping the query results into a temp table and filtering on the temp table.  Something like this:

CREATE TABLE #TmpLog

(SPID int, Status VARCHAR(150), Login varchar(100), HostName VARCHAR(150), BlkBy varchar(30), DBName varchar(60), Command varchar(500),

CPUTime int, DiskIO int, LastBatch varchar(20), ProgramName varchar(200), SPID2 int, requestid int)

INSERT INTO #TmpLog

EXEC sp_who2

SELECT SPID, Status, Login, HostName, BlkBy, DBName, Command, ProgramName

FROM #TmpLog

where STATUS not in (‘BACKGROUND’) and Command not in (‘TASK MANAGER’)

DROP TABLE #TmpLog

Better yet, you can create your own stored proc in the master database then you can run your stored proc with a short command when desired:

create proc sp_whoMine
as
CREATE TABLE #TmpLog
(SPID int, Status VARCHAR(150), Login varchar(100), HostName VARCHAR(150), BlkBy varchar(30), DBName varchar(60), Command varchar(500),
CPUTime int, DiskIO int, LastBatch varchar(20), ProgramName varchar(200), SPID2 int, requestid int)
INSERT INTO #TmpLog
EXEC sp_who2
SELECT SPID, Status, Login, HostName, BlkBy, DBName, Command, ProgramName
FROM #TmpLog
where STATUS not in (‘BACKGROUND’) and Command not in (‘TASK MANAGER’)
DROP TABLE #TmpLog
go

Posted in SQL Server | Leave a Comment »

A C# .Net Dialog For Connecting to SQL Server

Posted by robkraft on May 7, 2020

I created a Windows C# .Net Form and made it an example for anyone that wants to create a prompt for logging into SQL Server that looks at behaves like SQL Server Management Studio and other Microsoft products.

The code can be found here: https://github.com/RobKraft/SQLServerLoginTemplate

The code does not “share” the saved credentials with the Microsoft products. It stores those credentials in User Specific Settings for the specific application. Passwords are encrypted using the Windows Machine key (DPAPI), therefore this code will not work if used on Linux.

Posted in Coding, SQL Server | Leave a Comment »

SQL to Find Who Is Trying To Hack The SQL Server You Exposed To The Internet

Posted by robkraft on May 5, 2020

If you are running a SQL Server exposed to the Internet on the default port 1433 then hackers are probably try to hack in to it.  If the SQL Server is running on a different port it is less likely, but it could still be happening.  If you exposed a SQL Server to the world the hackers on the Internet will find it and will launch repeated login attempts against the ‘sa’ account.

That is a good reason for disabling the ‘sa’ account.

That is also a good reason for renaming the ‘sa’ account.

Most SQL Server admins record the Failed Login Attempts in the SQL Server Error Log.  If you have this option turned on, which is recommended, you may see entries like this:

Login failed for user ‘sa’. Reason: Could not find a login matching the name provided. [CLIENT: 113.64.210.68]

You can write an SQL to read the SQL Server ErrorLog and extract all the IP addresses that are attempting to login to your server using the ‘sa’ account.  You can then provide that list to your firewall to blacklist them, although a whitelist of known good addresses is a much better approach.  After you blacklist those IP Addresses, come back the next day and check your logs again because you will probably find a whole bunch of new IP addresses doing the same thing.  This game of ‘whack-a-hacker’ can be played for a long time before the hackers run out of IP addresses.  By the way, if you are going to try to blacklist the bad IP addresses, I recommend blocking entire blocks like 113.64.*.*, not a single IP Address at a time.

Here is an SQL to get you started with reading and filtering your SQL Server Error Logs.  Notice this script only looks for failed login attempts using the ‘sa’ account.  If you look at your logs, you will find failed login attempts for other accounts too.  Happy Hunting.

CREATE TABLE #TmpLog
(LogDate datetime, ProcessInfo VARCHAR(150), Text VARCHAR(max))
INSERT INTO #TmpLog
EXEC sp_readerrorlog
SELECT replace(Text,'Login failed for user ''sa''. Reason: Could not find a login matching the name provided. ',''), 
count(*)
FROM #TmpLog where Text like '%Client%' and Text like '%''sa''%' group by
replace(Text,'Login failed for user ''sa''. Reason: Could not find a login matching the name provided. ','')
having count(*)>10
order by 2 desc
DROP TABLE #TmpLog

 

 

Posted in Security, SQL Server | Leave a Comment »

SQL Server’s sp_executesql Does Not Protect You from SQL Injection

Posted by robkraft on August 18, 2019

Many coders of SQL have learned we can dynamically construct SQL statements inside of stored procedures and then execute the constructed SQL.  In Microsoft’s SQL Server product there are two commands we can choose for running the constructed SQL:

  • EXEC (EXEC is an alias for EXECUTE, both do the same thing).
  • sp_executesql.

We SQL Server “experts” often advise coders to use sp_executesql instead of EXEC when running dynamically constructed SQL statements to reduce the risk of SQL Injection, and this is good advice.  But it is not the use of sp_executesql that prevents SQL injection, it is the use of parameters with sp_executesql that helps protect against SQL Injection.  You can still construct SQL dynamically and run that SQL using sp_executesql and be affected by a SQL Injection attack.

If you use parameters to substitute all the values in the SQL and then use sp_executesql you have probably eliminated the SQL Injection risk; but as a developer this means you may be unable to dynamically construct the SQL you want to run.

When you use sp_executesql parameters correctly, you can only replace data values in your SQL statement with values from parameters, not parts of the SQL itself.  Thus we can do this to pass in a value for the UserName column:

declare @sql nvarchar(500)
declare @dynvalue nvarchar(50)
select @dynvalue=’testuser’
SET @sql = N’SELECT * FROM appusers WHERE UserName = @p1′;
EXEC sp_executesql @sql, N’@p1 nvarchar(50)’, @dynvalue

But the following code will return an error when trying to pass in the name of the table:

declare @sql nvarchar(500)
declare @dynvalue nvarchar(50)
select @dynvalue=’appusers’
SET @sql = N’SELECT * FROM @p1′;
EXEC sp_executesql @sql, N’@p1 nvarchar(50)’, @dynvalue

Msg 1087, Level 16, State 1, Line 1
Must declare the table variable “@p1”.

If you are dynamically constructing SQL, and you are changing parts of the SQL syntax other than the value of variables, you need to manually write the code yourself to test for the risk of SQL injection in those pieces of the SQL.  This is difficult to do and probably best handled by the application calling the stored procedure.  I recommend that the calling program do the following at a minimum before calling a stored procedure that dynamically constructs SQL:

  1. Validate the length of the parameter. Don’t allow input longer than the maximum length expected.  If the stored procedure allows a column to be passed in that is used for sorting in an ORDER BY clause, and all of your column names are less than or equal to 10 characters in length, then make sure that the length of the parameter passed in does not exceed 10 characters.
  2. Don’t allow a single single quote, make sure to replace a single single quote with two single quotes.
  3. Don’t allow other special characters or even commands such as a semicolon or the UNION keyword or two hyphens that represent a comment in SQL.
  4. Don’t allow ASCII values greater than 255.

That short list is not sufficient to prevent all SQL Injection attacks, but it will block a lot of them and gives you an idea of the challenge involved in preventing SQL Injection attacks from being effective.

If you would like to see for yourself how the EXEC and sp_executesql statements behave I have provided a script you can use to get started with.  Related to this article, the most important query to understand is the last one because it shows a case of SQL injection even though the dynamically generated SQL is ran using sp_executesql.

–1. Create tables and add rows
DROP TABLE InjectionExample
GO
DROP TABLE Users
GO
CREATE TABLE InjectionExample ( MyData varchar (500) NULL)
GO
INSERT INTO InjectionExample VALUES(‘the expecteddata exists’), (‘data only returned via sql injection’)
GO
CREATE TABLE Users( username varchar(50) NULL,[password] varchar(50) NULL)
go
INSERT INTO Users VALUES (‘user1′,’password1’), (‘user2′,’password2’), (‘user3′,’password3’)
GO
–2. Run a test using EXEC with data the programmer expects
declare @sql nvarchar(500)
declare @p1 nvarchar(50)
select @p1 = ‘expecteddata’
select @sql = ‘SELECT * FROM InjectionExample WHERE MyData LIKE ”%’ + @p1 + ‘%”’
exec (@sql)–returns 1 row as expected
GO

–3. Run a test using EXEC with data the hacker used for sql injection
declare @sql nvarchar(500)
declare @p1 nvarchar(50)
select @p1 = ”’ or 1 = 1–‘
select @sql = ‘SELECT * FROM InjectionExample WHERE MyData LIKE ”%’ + @p1 + ‘%”’
exec (@sql)–returns all rows – vulnerable to sql injection
GO

–4. Run a test using sp_executeSQL to prevent this SQL Injection
declare @sql nvarchar(500)
declare @p1 nvarchar(50)
select @p1 = ‘expecteddata’
select @sql = N’select * from InjectionExample WHERE MyData LIKE ”%” + @param1 + ”%”’
exec sp_executesql @sql, N’@param1 varchar(50)’, @p1
GO

–5. Run a test using sp_executeSQL to prevent this SQL Injection – hacker data returns no results
declare @sql nvarchar(500)
declare @p1 nvarchar(50)
declare @pOrd nvarchar(50)
select @p1 = ”’ or 1 = 1–‘
set @pOrd = ‘MyData’
select @sql = N’select * from InjectionExample WHERE MyData LIKE ”%” + @param1 + ”%” order by ‘ + @pOrd
exec sp_executesql @sql, N’@param1 varchar(50)’, @p1
GO

–6. But sp_executesql does not protect against all sql injection!
–In this case, sql is injected into the @pOrd variable to pull data from another table
declare @sql nvarchar(500)
declare @p1 nvarchar(50)
declare @pOrd nvarchar(50)
set @p1 = ‘expecteddata’
set @pOrd = ‘MyData; SELECT * FROM Users’
select @sql = ‘select * from InjectionExample WHERE MyData LIKE ”%” + @param1 + ”%” order by ‘ +@pOrd
exec sp_executesql @sql, N’@param1 nvarchar(50)’, @p1

 

 

Posted in CodeProject, Security, SQL Server | Leave a Comment »

Microsoft SQL Server Violates the Commutative Law of Basic Math

Posted by robkraft on April 16, 2015

I love SQL Server.  I’ve been a SQL Server expert for decades, but just a few days ago I found a bug in SQL Server when performing simple math calculations.

These two queries yield different results:

  • select 1.0 * 3/2
  • select 3/2 * 1.0

The first query returns 1.5, and the second query returns 1.0.  Different results violates the Commutative rule of math that tells us the order of operands should not matter.

I understand why this is happening.  SQL Server looks at the data type of the first argument and rounds the results of calculations to that data type.  So in the first query, 1.0 is a float (aka double); but in the second query 3 is an int (aka integer) with no decimal places so 3/2 rounded to fit in an integer is 1, not 1.5.  But just because we can explain it does not mean that it is acceptable.

This problem affects real code.  The problem occurs not just with values, but when doing similar calculations with columns in tables.

  • create table CommTest (int1 int, int2 int, dbl1 float)
    insert into CommTest values(3,2,1.0)
    select dbl1 * int1/int2 from CommTest
    select int1/int2 * dbl1 from CommTest

The first select in this query will return 1.5, and the second select will return 1.

The fix is to cast the first variable as a float “select cast(int1 as float)/int2 * dbl1…”, but that is challenging to remember and difficult to apply if you have code generating SQL for you.  The better solution is probably to do all math in the program calling the database; at least if SQL Server is your DBMS.  Though this is not easy when you just have a reporting tool like SSRS for your front end.

Until Microsoft fixes this, which I doubt they ever will, developers just need to be extra careful.

This problem does not exist in other database management systems.  At least I know it does not exist in Microsoft Access nor Oracle.  For reference, I tested this on SQL Server 2014.

Posted in SQL Server | Tagged: | 2 Comments »

How to move data from a DBISAM database into SQL Server

Posted by robkraft on December 15, 2014

In December 2014 I acquired a task to retrieve all the data from an application storing the data in a DBMS format known as DBISAM.  DBISAM stores data for each table in separate files.  I discovered that each table consisted of either two or three files, a .dat file with the data and a .idx file for indexes and optionally a .blb for blog storage of images or large text blocks.

The first, and just about only, place you can go on the Internet for things related to DBISAM is www.elevatesoft.com.  You can download some free tools to assist with this task, and there are occasionally used forums where you can post questions.  The staff responded quickly to my one post.  From the downloads section you can download a package of tools called DBISAM-ADD.  Get the most current version.  The robust installer will place some programs on your computer (I was running Windows 8.1 and the tools worked fine).

The helpful tool is called “Database System Utility”, which is a program named dbsys.exe installed to C:\Program Files (x86)\DBISAM Additional\Version 4\utils\dbsys\win32.  With this program you can select any table to open and run queries against it, including data modifications.  You can also use the Export option to export the data to a .csv file.  However, blob data, including Memo fields will not be included in the exported .csv file.  So I do not recommend this option.

DBISAM Export

Getting the Data from Memo Fields Requires ODBC

ElevateSoft provides an ODBC driver on the same download page for the other DBISAM tools.  After running the installer for the ODBC driver, you need to open “ODBC Data Source Administrator (32-bit)” and add a “System DSN”.  I think you can use all the defaults if you are connecting to DBISAM files on your local computer.   My DBISAM files all have a password to protect them, so I needed to enter that password on the last tab/prompt of the ODBC configure wizard.  Then I was ready to use that ODBC driver with SSIS.

SSIS requires a standard edition or higher of SQL Server.  You cannot use it with SQL Server Express Edition.  Although I have installed a standard edition of SQL Server 2014, the most current SSIS tools are for SQL Server 2012.  And even though they use Visual Studio, I don’t think you can open Visual Studio directly, or at least that has not worked for me.  To get into SSIS, I open “SQL Server Data Tools for Visual Studio 2012”.

The secret to importing data using ODBC from DBISAM is to use the SSIS Import and Export Wizard.  I could not figure out how to do this import without the wizard.  Only the wizard allowed me to pick the ODBC connection I had created to the DBISAM Files.  See the picture below for an example of the Source configuration for the wizard.

Configure Data Source for DBISAM

For the destination you can pick your SQL Server database, then you should be prompted with a list of tables to include.  I had over 300 tables to import so I ran the wizard about 10 times and broke the 300 tables into smaller groups so that I could rerun the smaller chunks later.

The wizard does seem to have a problem with date and time fields coming from DBISAM and it will set their datatype to a number for mapping as in the screen shot below.  Simply change that to a datetime, or smalldatetime if you prefer, and you should be ready to go.

Edit Mappings for DBISAM

If you plan to run this multiple times, which you probably want to write a script that runs before your packages to delete the tables you created if they currently exist.  I recommend using the “Execute SQL Task” on the “Control Flow” tab of your package and having it run your “Data Flow Tasks” upon completion.

I also had some imports fail because the dates entered by the users into the DBISAM based system were outside the range of valid dates that SQL Server allows.  I found data with dates like ’12/31/0210′ instead of ’12/31/2010′.  I used the tools in the DBISAM utility to correct that data as shown here:

Run A Data Modification Query in DBISAM

Select,   “New Query” from the File menu and you can type in a query.  Dates must be surrounded by single quotes and be in yyyy-mm-dd format.  After correcting the data I was able to successfully import all the data.

Even the blob data such as images came across successfully into SQL Server blob datatypes.

Send me a message if you have any trouble with this.

Posted in Coding, SQL Server | Tagged: | 8 Comments »

The Correct Way to Write A SQL Server Trigger That Modifies the Record Just Inserted

Posted by robkraft on October 31, 2014

It is easy to write a trigger that has unintended consequences, which is why I recommend you always test your triggers for two things:

  1. Did the trigger update the records I expected it to correctly?
  2. Did the trigger update records I did not expect it to?

Here is an example of a trigger we had that was incorrect.  We discovered it because it was taking so long to run the insert query that the query was timing out.  And it was taking so long because it was updating all the records in the table, not just the one we wanted it to modify.

In this example, we want to update the OrderDetails date to be equal to the OrderDate from the parent table, but only for orders with type=200 and only when new order details are inserted.  Run this SQL to create the tables and add some test data.

drop Table Orders
go
drop table Orderdetails
go
Create Table Orders (OrderID int, OrderDate datetime, OrderType int)
Create Table OrderDetails (DetailID int, parentOrderID int, DetailDate datetime)
insert into orders values (1,’10/25/2014′,100)
insert into orders values (2,’10/27/2014′,200)
insert into orders values (3,’10/28/2014′,100)
insert into orders values (4,’10/29/2014′,200)
insert into OrderDetails values (10, 1, ’10/25/2015′)
insert into OrderDetails values (11, 1, ’10/26/2015′)
insert into OrderDetails values (14, 2, ’10/25/2015′)
insert into OrderDetails values (15, 2, ’10/26/2015′)
insert into OrderDetails values (16, 3, ’10/25/2015′)
insert into OrderDetails values (17, 3, ’10/26/2015′)
insert into OrderDetails values (18, 4, ’10/25/2015′)
insert into OrderDetails values (19, 4, ’10/26/2015′)

Then run this script to create the incorrect stored procedure:

GO
CREATE TRIGGER dbo.MyFirstTriggerThatDoesntWork
ON OrderDetails
FOR INSERT AS
BEGIN
UPDATE OrderDetails
SET DetailDate=Orders.OrderDate
FROM Orders INNER JOIN
INSERTED I ON Orders.OrderID = I.parentOrderID
WHERE (Orders.OrderType = 200)
END

Now let’s run an INSERT and check the results:

insert into OrderDetails (DetailID, parentOrderID) values (20, 4)

The messages displayed look like the following, and that message with “9 row(s) affected” is a good indicator that something went wrong:

(1 row(s) affected)
(1 row(s) affected)
(9 row(s) affected)
(1 row(s) affected)

Looking at all of our data, we can see that every record got updated to the 10/29 date:

select * from OrderDetails
10 1 2014-10-29 00:00:00.000
11 1 2014-10-29 00:00:00.000
14 2 2014-10-29 00:00:00.000
15 2 2014-10-29 00:00:00.000
16 3 2014-10-29 00:00:00.000
17 3 2014-10-29 00:00:00.000
18 4 2014-10-29 00:00:00.000
19 4 2014-10-29 00:00:00.000
20 4 2014-10-29 00:00:00.000

That is not what we wanted.  Here is the correct way to write this trigger. Do this after running the script above to drop and recreate the tables.

CREATE TRIGGER dbo.MySecondTriggerThatDoesWork
ON OrderDetails
FOR INSERT AS
BEGIN
UPDATE OrderDetails
SET DetailDate=Orders.OrderDate
FROM Orders INNER JOIN
INSERTED I ON Orders.OrderID = I.parentOrderID INNER JOIN OrderDetails d on d.DetailID = I.DetailID
WHERE (Orders.OrderType = 200)
END

If we run the insert again and look at the output messages we will see that only 1 record was affected and we can see the correct output in our results:

insert into OrderDetails (DetailID, parentOrderID) values (20, 4)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
select * from OrderDetails
10 1 2015-10-25 00:00:00.000
11 1 2015-10-26 00:00:00.000
14 2 2015-10-25 00:00:00.000
15 2 2015-10-26 00:00:00.000
16 3 2015-10-25 00:00:00.000
17 3 2015-10-26 00:00:00.000
18 4 2015-10-25 00:00:00.000
19 4 2015-10-26 00:00:00.000
20 4 2014-10-29 00:00:00.000

I emboldened the line of code in the trigger that made the difference.  In the first trigger, we are updating the OrderDetails table with the result of a join between the Inserted table and the Orders table.  So basically, that says “run a query that joins the Orders table with the Inserted table, and apply the results of that join to EVERY record in OrderDetails”.  It is applied to EVERY record in OrderDetails because we did not apply any criteria to limit which records on OrderDetails are affected.

UPDATE OrderDetails
SET DetailDate=Orders.OrderDate
FROM Orders INNER JOIN INSERTED I ON Orders.OrderID = I.parentOrderID

The fix was include a join to the table we are updating (OrderDetails) and limit which records it is going to affect by joining the OrderDetails table to the Inserted table.

UPDATE OrderDetails
SET DetailDate=Orders.OrderDate
FROM Orders INNER JOIN INSERTED I ON Orders.OrderID = I.parentOrderID INNER JOIN OrderDetails d on d.DetailID = I.DetailID

In the example above, the Inserted table would have 1 record in it, with the values of DetailID=20 and parentOrderID=4.  Our trigger would update a record in OrderDetails that had the same DetailID of 20 and set the DetailDate equal to the OrderDate of the Order we were joined to.

Posted in SQL Server, Uncategorized | Leave a Comment »

Use “NETWORK SERVICE” to Start SQL Server Service in 2014 on Local Computer

Posted by robkraft on May 14, 2014

I had a really hard time getting the SQL Server 2014 install to succeed.  I spent days on it.  I uninstalled all my earlier SQL Server Editions, which is a tedious process because you need to uninstall a lot of different components in specific order.  The SQL Server 2014 install then succeeded, but the SQL Server service would not start.

Event viewer showed this error when trying to start SQL Server:
Initializing the FallBack certificate failed with error code: 1, state: 20, error number: 0.

I tried configuring the SQL Server Service to start using a different windows account, but that did not work either, although I was on the right track.  I did need to change the account used by SQL Server, but I needed to do it during the install process.  So, I uninstalled all the SQL Server components again, then ran the install again.

Apparently the default SQL Server service account is not working on my Windows 7 machine, so I need to pick a different account, and that account needed to be “Network Service”.  I tried several other promising looking accounts such as “Administrator”, “LOCAL SERVICE”, and “SERVICE”, but only “NETWORK SERVICE” from my local machine security group worked.

Don’t do this on production servers, it is not the best security choice.

Select NETWORK SERVICE to get SQL Server 2014 to work

Select NETWORK SERVICE to get SQL Server 2014 to work

Posted in SQL Server | Leave a Comment »

My Least Favorite New Feature in SQL Server 2014 – No CTRL+E

Posted by robkraft on April 4, 2014

Since as long as I can remember, perhaps back to SQL Server 6.5, I’ve been using CTRL+E to “execute” queries inside SQL Server Management Studio.  I knew that Microsoft warned they were going to eliminate that keystroke, and they finally have in SQL Server 2014.  Now the only keystroke for running queries appears to be F5.  I consider this a productivity setback because to hit F5, I have to lift my palm off of its resting position by the keyboard, then relocate my fingers back on the keyboard correctly to continue typing.  I did not need to do this when using CTRL+E.

Is it possible to complain enough to get a response from Microsoft?  I’m not asking for CTRL+E to come back, but I would like a key combination that does not require me to take my hands of the keyboard to use it.  I like running queries!

Posted in SQL Server | 1 Comment »