Today’s Date in T-SQL

by fwhagen Wed, 19 February 2014

I forget this one all the time:  I need today’s date in a SQL query.  In C# it’s DateTime.Today, and in T-SQL it’s:

DATEADD(Day, 0, DATEDIFF(Day, 0, GetDate()))

So a simple select that will return all of todays records will look like this:

SELECT * FROM TableName WHERE CompletedDate >= DATEADD(Day, 0, DATEDIFF(Day, 0, GetDate()))

Now I won't forget.

Keywords:

Filed Under: DataBase | Programming | SQL

Fixing Open Transaction Blocks in SQL 2005

by fwhagen Wed, 13 July 2011

Sometimes, when developing in SQL, a transaction will be left open.  Performance will fall through the floor, which can sometimes be the first sign that it happened.  If you cannot close the transaction properly, the following command will show the oldest, and probably orphaned, on the server:

DBCC OPENTRAN

The transaction can easily be termed by issuing a kill on the PID:

KILL 52

That should do it. 

Keywords: ,

Filed Under: Programming | SQL

Get Data From MS-SQL Using a PowerShell Script

by fwhagen Wed, 23 July 2008
Here's an extremely useful stub to get data from SQL-Server within a PowerShell script:
$TaskName = "20071029-AllRejectedDuring"
$SqlServer = "SQLDEV01";
$SqlCatalog = "MyData";

# Get the T-SQL Query from .SQL file
$SqlQuery = Get-Content (".\" + $TaskName + ".sql")

#Write-Host ($SqlQuery) -foregroundcolor "gray"

# Setup SQL Connection
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = 
          "Server = $SqlServer; Database = $SqlCatalog; Integrated Security = True"

# Setup SQL Command
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection

# Setup .NET SQLAdapter to execute and fill .NET Dataset
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet

#Execute and Get Row Count
$nRecs = $SqlAdapter.Fill($DataSet)

Write-Host ($nRecs.ToString() + " Records retrieved.") -foregroundcolor "Cyan"
$SqlConnection.Close();

if ($nRecs -gt 0)
{
  # Do Stuff
  $DataSet.Tables[0].Rows[0][0]  #Print first data element
}
The connection is using Integrated Security for simplicity, it wouldn't be difficult to switch to UID/PWD instead.  Also, I put the SQL in a .sql file (flat text) to make life easier; you could also put the statement in the string declaration, if it is a simple query.  PowerShell's Get-Content mechanism makes reading a file very easy.  Also, clean up after yourself, I won't include that here. UPDATE:  I have posted a full script to export to Excel or XML in a followup post.

Keywords: ,

Filed Under: PowerShell | SQL

Getting Data from SQL to Excel Using PowerShell

by fwhagen Wed, 23 July 2008

Earlier, I wrote about getting data from MS-SQL with PowerShell, which is a very useful and powerful thing.  But just displaying data is very limited.  I have a need at work to create extract "reports" for business owners frequently into Excel.  So I have leverage this scripting technique to do the legwork for me.  After many iterations, I have completed the first version of a script that will take a SQL query in a text file and create an Excel file with the results.  All fully automated with time-stamping, archiving of the successful query, and the ability to export XML or CSV as well.

The script is unfortunately only listed as a page on this site because WordPress does not allow posting of non-media files.  You will have to copy the full content and paste it into a text file named QuerySQL.ps1.  One day, I will host this weblog on a real provider in order to have better control (I will accept donations!).  If many folks prefer, I will post it on my personal webspace with my ISP and provide links on request.  And maybe to CodeProject too.

Feel free to use it in anyway you wish.  Please learn from it if you can, or conversely, send me a note on how to improve it.  I would love to get the field-name bolding working among other things.

Keywords: ,

Filed Under: PowerShell | SQL

SQL Bulk Inserts

by fwhagen Mon, 18 February 2008

It is fair to say that I don't do a huge amount of real-time SQL.  As an application developer, the vast majority of SQL I write is in Stored Procedures and packages.  Very infrequently, I need to do a manual insert statement.  So why do I always forget to drop the VALUES keyword in a T-SQL statement when using a sub-query?  I mean every time!

So here it is.  A representative INSERT statement with sub-query:

insert into UserRoleMembership
(userIdx, roleName)
 
(
  select 488, roleName from Roles
  where appidx = 18
  and roleName not in ( select roleName from UserRoleMembership where userIdx = 488 )
)

Now I won't forget.

Keywords:

Filed Under: SQL

Get Data From MS-SQL Using a PowerShell Script

by fwhagen Tue, 30 October 2007
Here's an extremely useful stub to get data from SQL-Server within a PowerShell script:
$TaskName = "20071029-AllRejectedDuring"
$SqlServer = "SQLDEV01";
$SqlCatalog = "MyData";

# Get the T-SQL Query from .SQL file
$SqlQuery = Get-Content (".\" + $TaskName + ".sql")

#Write-Host ($SqlQuery) -foregroundcolor "gray"

# Setup SQL Connection
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SqlServer; Database = $SqlCatalog; Integrated Security = True"

# Setup SQL Command
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection

# Setup .NET SQLAdapter to execute and fill .NET Dataset
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet

#Execute and Get Row Count
$nRecs = $SqlAdapter.Fill($DataSet)

Write-Host ($nRecs.ToString() + " Records retrieved.") -foregroundcolor "Cyan"
$SqlConnection.Close();

if ($nRecs -gt 0)
{
  # Do Stuff
  $DataSet.Tables[0].Rows[0][0]  #Print first data element
}

The connection is using Integrated Security for simplicity, it wouldn't be difficult to switch to UID/PWD instead.  Also, I put the SQL in a .sql file (flat text) to make life easier; you could also put the statement in the string declaration, if it is a simple query.  PowerShell's Get-Content mechanism makes reading a file very easy.  Also, clean up after yourself, I won't include that here.

UPDATE:  I have posted a full script to export to Excel or XML in a followup post.

Keywords: ,

Filed Under: PowerShell | SQL

Checking for Database NULL Values in C#

by fwhagen Tue, 09 October 2007

This really should have been more obvious to me, so why did I have to look it up?  If you need to check for NULL in a returned field from SQL (or any other datasource), you should the .Equals method on System.DBNull.Value.  I usually prefer the "==" notation for conditionals, but that's just me.  This is more efficient.  The code follows:

if (!dsSpecQuery.Tables[0].Rows[0]["device_type_end"].Equals(System.DBNull.Value)) 
{
    _EndDate = Convert.ToDateTime(dsSpecQuery.Tables[0].Rows[0]["device_type_end"]);
}

Of course, dsSpecQuery is a DataSet, and the field in question is DateTime out of SQLServer (not that it matters for the conditional).

Keywords: , ,

Filed Under: .NET | Programming | SQL

Database Access from VBScript (WSH)

by fwhagen Fri, 21 September 2007

Sometimes its necessary to script out data manipulation for deployments or automation.  The easiest vehicle in Windows is Windows Script Host (WSH) using VBScript.  Below is a very basic framework for accessing a database (MSSql) and looping a RecordSet.

''===// Retrieve Data from Database //=====
Const DBSERVER = "SQLDEV01"
Const DATABASE = "Cellular"
Const DBUSERID = "app_user"
Const DBPASSWD = "app_pass"

'' Construct ConnectionString
szADOConn = "Provider=SQLOLEDB;Network Library=DBMSSOCN;Persist Security Info=True;" &_ 
            "Data Source=" & DBSERVER & ";" &_
            "DATABASE=" & DATABASE & ";" &_
            "User ID=" & DBUSERID & ";Password=" & DBPASSWD & ";" 

Set DBConn = CreateObject("ADODB.Connection")
    DBConn.open szADOConn        
    szSqlQuery = "SELECT PhoneID, Field2, etc  FROM Import_Name WHERE PhoneID <= 200"
Set cmd = CreateObject("ADOdb.Command")
    cmd.CommandType = &H0001  ''adCmdText
    cmd.ActiveConnection = DBConn
    cmd.CommandText = szSqlQuery
Set RecordSet = CreateObject("ADODB.Recordset")
Set RecordSet = cmd.Execute(szSQLlc)

Do While Not RecordSet.EOF
  WScript.Echo RecordSet("PhoneID")  
  RecordSet.movenext
Loop

Set RecordSet = Nothing
Set cmd = Nothing
    DBConn.close
Set DBConn = Nothing

Of Course, it's possible to distill this down further, but I didn't for clarity (Consts) and it's better to not use implicit command objects by opening the database directly with the Recordset object.

Keywords:

Filed Under: SQL

Simple ASP.NET Database Query

by fwhagen Wed, 08 August 2007

Below is the most simple database query for C# to populate a GridView object:

int nItemID = 1; try { using (SqlConnection oSQLConn =
new SqlConnection(ConfigurationManager.ConnectionStrings["UserInfo"].ConnectionString)) { oSQLConn.Open(); using (SqlCommand oSQLCmd = new SqlCommand()) { oSQLCmd.Connection = oSQLConn; oSQLCmd.CommandText = "SELECT DisplayName, EMail FROM Users WHERE UserID = @PARAM_ItemID"; oSQLCmd.Parameters.AddWithValue("@PARAM_ItemID", nItemID); SqlDataReader oSQLReader = oSQLCmd.ExecuteReader(); GridView1.DataSource = oSQLReader; GridView1.DataBind(); } } } catch (Exception ex) { Label_ErrorMsg.Text = ex.Message; }

There are actually many reasons to never use this, including performance, caching, safe SQL, code reuse, standards, proper exception handling, etc, etc.  But sometimes you need a quick and dirty, non-production, piece of code for testing and/or development.  This is it.

Keywords: , ,

Filed Under: .NET | Programming | SQL

Counting Lookup Populations in SQL

by fwhagen Tue, 03 July 2007

I am not a great T-SQL developer, so I frequently struggle with some of the more advanced queries.  I spent some time building the query below, so I want to post it for future reference and the hope it may help someone else.

Say you have a dependant table for lookup values of status.  The status code in the primary table may be 1, 2, 3, etc., so the lookup table will define them as Open, Closed, Pending, whatever.  Now say for reporting you want to know how many Open or Closed items you have in the table.  The SQL query below will return each Status name with a count of items using the codes in the primary table:

SELECT OS.ObjectStateID, OS.Code, OS.Name,
    (SELECT COUNT(RS.ProductRequestID)
       FROM ProductRequestState RS
      WHERE RS.IsCurrent = 1 AND RS.ObjectStateID = OS.ObjectStateID
    )
FROM ObjectState OS
Where OS.IsActive = 1

The output will look something like this:

  OID Code              Name                              Count  
----- ----------------- --------------------------------- -----   
    1 WaitSubmit        Created                             154 
    3 Rejected          Rejected                           4785 
    4 WaitTSTechRvw     Waiting TS Tech Review               68 
    6 WaitGroupApprv    Waiting Cost/Labor/TS Tech Apprv    228 
    8 WaitExecApprv     Waiting Manager/Executive Apprv       0 
   13 Complete          Complete                          14433 
   14 WaitCostApprv     Waiting Cost Accounting Apprv         0 
   15 WaitLaborApprv    Waiting Labor and Supply Apprv        0 
   16 WaitTSTechApprv   Waiting TS Technologist Apprv         0
Sorry for the ugly formatting, WordPress is a bit limiting.

Keywords:

Filed Under: SQL

TextBox

RecentPosts