Tech Talk A software developer on all things tech, and then some

14Nov/120

SqlDataAdapter Will Not Always Return a Result, SSMS Will

The Issue

I ran into a strange issue with an ASP.NET site (MVC2 - 4.0 Framework) where when executing a stored proc, SqlDataAdapter.Fill(DataTable) will not return any rows some of the time, but SQL Server Management Studio will almost immediately return results. The full method calling the stored procedure is below : (Auto-generated from LLBL Pro V2.6)

public virtual bool CallRetrievalStoredProcedure(string storedProcedureToCall, SqlParameter[] parameters, DataTable tableToFill)
{
	SqlCommand command = new SqlCommand(CreateCorrectStoredProcedureName(storedProcedureToCall));
	command.Connection = (SqlConnection)base.GetActiveConnection();
	if(base.IsTransactionInProgress)
	{
		command.Transaction = (SqlTransaction)base.PhysicalTransaction;
	}
	command.CommandType = CommandType.StoredProcedure;
	command.CommandTimeout = base.CommandTimeOut;

	using(SqlDataAdapter adapter = (SqlDataAdapter)CreateNewPhysicalDataAdapter())
	{
		adapter.SelectCommand = command;

		for(int i=0;i<parameters.Length;i++)
		{
			command.Parameters.Add(parameters[i]);
		}
		adapter.Fill(tableToFill);
	}
	command.Dispose();
	return true;
}

This issue occurs on both our Development and Testing servers. The difference however is that on the development server, it will usually fail once or twice then work, but on the testing server it will return zero rows every time.

23Oct/120

View The Last 50 Queries Ran on SQL Server

I ran into an issue where I needed to see the last few queries ran on SQL Server (Currently using SQL Server 2005). There were quite a few suggestions out there, but one I found on Stack Overflow seemed to be formated the best and didn't require me messing with the query. (See Query Below)

SELECT TOP 50 * FROM(SELECT COALESCE(OBJECT_NAME(s2.objectid),'Ad-Hoc') AS ProcName,
  execution_count,s2.objectid,
    (SELECT TOP 1 SUBSTRING(s2.TEXT,statement_start_offset / 2+1 ,
      ( (CASE WHEN statement_end_offset = -1
  THEN (LEN(CONVERT(NVARCHAR(MAX),s2.TEXT)) * 2)
ELSE statement_end_offset END)- statement_start_offset) / 2+1)) AS sql_statement,
       last_execution_time
FROM sys.dm_exec_query_stats AS s1
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2 ) x
WHERE sql_statement NOT like 'SELECT TOP 50 * FROM(SELECT %'
ORDER BY last_execution_time DESC

This however wasn't working for me on the database I was in. I checked my compatibility level (code below), due to the suggestions in the comments, and it returned 80, not the expected 90.

SELECT compatibility_level from sys.databases WHERE name=db_name()

I then switched my db to tempdb, ran the query, and it worked. It really doesn't matter where you run the query, it will pull the last 50 ran on any database. Just make sure the db you run it on has a compatibility level > 80 so you can use the cross apply (tempdb is usually untouched, so it's a good one to default to).

Notes : I would suggest using the SQL Profiler if you can, however this is a great quick solution when you don't have time to wait for permissions to change, etc...

13Mar/120

Installing SQL Server Management Studio (SSMS) for SQL Server 2008

In years past, you could download the SQL Server Management Studio and it was pretty straight forward during the install and you were good to go in minutes. Now they seemed to have made a confusing way to install it which you will spend far too much time trying to figure out. So to save you time, I thought I'd log the steps to speed up the process.

16May/110

Specify Your Own Select And Edit Top(n) in SQL Server Management Studio

When right clicking a table in SQL Server Management Studio, you have an array of options. Probably the two that I use most however is the "Select Top 1000 Rows" and the "Edit Top 200 Rows". However, sometimes you want to look at a little more than that. I know most of the time I want to select all from a table, not just the top 1000. Well in SQL Server 2008 (might work in older versions as well) you can change the default select and edit numbers.

1) Go to Tools -> Options
2) Select SQL Server Object Explorer
3) You will be prompted with a screen which looks similar to the one below. Edit the "Value for Edit Top <n> Rows command" and the "Value for Select Top <n> Rows" to the desired value. ( 0 will select all)

15Mar/110

Drop, Truncate, and Delete : The Difference


If you wanted to get rid of all the data in a table, then you could you either of the three. However there are certain situations which using a different one of these is the best route to go.

14Mar/110

Create a Filestream In SQL Server 2008 In Three Easy Steps

Step 1 : Enable FileStream

The first step is to enable the filestream capabilities on your SQL Server instance. This can be found by going to your server properties in your SQL Server Configuration Manager and enabling the filestream there. Microsft explains how here.

Step 2 : Creating a FileStream Database

The following script will create a filestream database for us to use.

CREATE DATABASE Attachments
ON
PRIMARY ( NAME = Attachments,
    FILENAME = 'c:\data\Attachments.mdf'),
FILEGROUP FileStreamGroupAttachments CONTAINS FILESTREAM( NAME = AttachmentsFileStream,
    FILENAME = 'c:\data\Attachmentsfilestream')
LOG ON  ( NAME = Attachments_Log,
    FILENAME = 'c:\data\Attachments_Log.ldf')
GO

Now that we have our database, let's create our table.

Step 3 : Creating a Table Which Uses FileStream

In the following example, I still create my Primary key column, but I also include an id column for the file stream since it requires a GUID column.

CREATE TABLE Attachments.dbo.Attachments
(
    [AttachmentID] [bigint] IDENTITY(1,1) NOT NULL,
    [Id] [uniqueidentifier] ROWGUIDCOL NOT NULL UNIQUE,
    [Name] varchar(128),
    [AttachmentItem] VARBINARY(MAX) FILESTREAM NULL
)
GO

That's all there is to it. Good luck and happy coding!

If you would like to know more about implementing in code, here is an example provided by microsoft in C# here.To use in the entity framework, Guy Burstein wrote  a nice little article here.

19Jan/110

Building The Perfect SQL Script. Part 1 of N

Introduction

Im going to start off by saying I am by no means an expert in SQL. I am however on the road to increase my skills and work towards more efficient and cleaner SQL code. With that said, Im going to start an on going series of posts which I will post from time to time which include tips and tricks to help build a better SQL script. Again I am no expert in this subject, so I encourage any who see inefficient code or something done not quite right, please leave a comment and I will fix as soon as I can.

If Exists

Many of us when first learning SQL will have scripts that will create an object (ex stored procedure or view), then afterwards have to change the CREATE keyword to ALTER. What if you didnt want to even worry about this? Use IF EXISTS and you wont have to. You simply query the system, see if the object you are trying to create exists, then drop or create accordingly. The code for this :

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE Name = 'NameOfObject' and type = 'Type')
DROP VIEW NameOfObject
GO

NameOfObject - The name of the object.

Type - The type of object you are looking for. These include

  • C : CHECK constraint
  • D : Default or DEFAULT constraint
  • F : FOREIGN KEY constraint
  • L : Log
  • P : Stored procedure
  • PK : PRIMARY KEY constraint (type is K)
  • RF : Replication filter stored procedure
  • S : System tables
  • TR : Triggers
  • U : User table
  • UQ : UNIQUE constraint (type is K)
  • V : Views
  • X : Extended stored procedure
  • TF : Functions
Tagged as: , No Comments
22Dec/100

Reseeding a SQL Table

I came across a problem recently in which I needed to reseed a SQL table to a certain number for the auto incrementing of the primary key.

You need to know the following :

  • tableName - The name of the table you are reseeding
  • Reseed or NoReseed - Will discuss briefly after the example.
  • Int - The number you want to start the records off with, subtracted by one.

DBCC CHECKIDENT (tableName,reseed, Int)

An example with Jobs as your table name and you would like to reseed starting at 10 :


DBCC CHECKIDENT (Jobs,reseed, 9)

Now say you just deleted all records from a table and you need to start the identity column over at 1. Notice above we set the reseed int one less than the desired value. This means when we delete everything from a table and reseed, we'll have to use zero.

--Reseeding to start the increment at 1
DBCC CHECKIDENT (Jobs, reseed, 0)

Reseed or NoReseed

Reseed - Choose this option if you would like to set the auto-incrementing primary key to a certain number.

NoReseed - Choose this option to return the current identity value and the maximum identity value from the specified table.  As the name implies, the table will not be changed and the identity value will remain the same.

If you would like to read more, go to 's MSDN article which can be found here about reseeding tables.

   
%d bloggers like this: