Navigation

Search

Categories

On this page

Using SQL Server to Create Random Passwords
Concatenate SQL Server Columns into a String with CONCAT()
List of all fields and data types in a SQL Server table
Generate Random Passwords in SQL Server
Several Uses of COALESCE In SQL Server
SQL Server Row Count for all Tables in a Database
Importing Data Into SQL Server Drops Leading Zeros
SQL Server User Defined Functions
Paging in SQL Server 2012
How to Restore a SQL Database
Searching and finding a string value in all columns in a SQL Server table
Tool to Deploy SQL Server Database Changes
Export data to an earlier SQL Server version
Searching the text of stored procedures
SQL Server PIVOT Function
SQL Server: Handling Multiple Result sets in a Procedure
SQL Server–CSVExpress
SQL Server - Unmatched Query Record
Restore backup of database (.bak) using Sql Server 2005
Copy a table from one database to another in SQL Server 2005
How to Grant a user Execute Permissions to All Stored Procedures
SQL Backup and FTP
SQL Server: Find Database File Path
SQL LIKE Search Techniques
Send Email from SQL Server Express Using a CLR Stored Procedure
Transfer SQL Server 2005/2008 Logins
T-SQL Moving Data to a Different Table using the OUTPUT Clause
Find the Most Time Consuming Code in your SQL Server Database
SQL Server - SELECT with In Clause for Varchar Data
SQL Server List of tables, fields and data types
SQL SERVER - Import CSV File into Database Table Using SSIS
Import CSV File Into SQL Server Using Bulk Insert
SQL Server Reporting Services (SSRS)
T-SQL Format a Date Field Without the Time
T-SQL: Convert amount of time between two dates
T-SQL the number of records in every table and the space used
FOR XML function
How to pass a Comma-Delimited String passed as a Parameter to a Stored Procedure
Sql Server: Return Multiple Values From a Function
Using T-SQL to remove the last character from a string
Columns in the SELECT not in the GROUP BY
How to do Bulk Copy of data from one Database Table to Another Database Table in C# and ASP.Net
The basics of TRY CATCH error handling in T-SQL
Using the SQL Substring Function to Mask a SSN
SQL Server - REPLACE Multiple Spaces with One
Showing Database User Connections
Debugging SQL Queries, Functions, & Stored Procedures with SQL Management Studio’s Integrated Debugger
SQL Server Blog
SQL SERVER – 2005 – Create Script to Copy Database Schema and All The Objects – Stored Procedure, Functions, Triggers, Tables, Views, Constraints and All Other Database Objects
Getting a Record Count from a SqlDataSource
Find Maximum Value in Each Row Using UNPIVOT
Drop and Recreate Stored Procedures in T-SQL
Formatting Dates in T-SQL
SQL Search – RedGate Software
Find the Nth Maximum and Minimum Value in a Column
Count the number of rows in every table in a database
Convert Date to String in SQL Server
T-SQL Examples
How to Duplicate a SQL Database
SQL Derived Tables
How to Automate Database Backups with SQL Server Express
SQL REPLACE Function In UPDATE Statement
Basic Transactions in SQL Server 2008
Basic Error Handling in SQL Server 2008
SQL Server – Scalar Function With Parameter
SQL Server - Debugging with variables and output parameters
Updating or Deleting a List of Records
SQL – IN vs. EXISTS
SQL Server IsNull Function
Case Sensitive SQL Query Search
Encrypting SQL Server Data
Working With Dates and Times in SQL Server
SQL Practice
SQL Server RSS Reporter
Updating Multiple Fields and Rows in a Repeater
How to Connect to a SQL 2005 Server When You Are Completely Locked Out
Dynamic SQL
Checking for duplicate records
SQL Formatter
Moving Databases With the ALTER Command
SQL Server Split Function
Getting ID of the newly inserted record in SQL Server Database using ADO.Net
T-SQL: Get only Date from DateTime
Examples of using output parameters with SQL Server
Creating a SQL User Defined Function
Using a DbTransaction object to add transaction context with the SqlDataSource control to update data
Using Multiple Active Result Sets (MARS) to Execute Multiple Commands on a Connection
SQL Server Information_Schema
SQL Collation Conflict
REPLACE Function for a SQL Server Text Field
Getting the ID value of the most recently added record
Common Solutions for T-SQL Problems
Getting column information using T-SQL
SqlConnection.RetrieveStatistics()
How to Share Data Between Stored Procedures
Connection String has not been properly iniitialized
Finding a column name in SQL Server
Optional Parameters for Stored Procedures
Using SELECT to INSERT records
How to Insert Values into an Identity Column in SQL Server
Comparing Records from Two Tables
Update records from two tables
Using XP_EXECRESULTSET To Obtain Database Size Information
Preventing Duplicate Logins
A SQL Query to Find Duplicate Values
SQL: Preventing Duplicate Records In a Database In a Signup Form
SQL Server CASE-WHEN Statement

Archive

Blogroll

Disclaimer
The opinions expressed herein are my own personal opinions and do not represent my employer's view in any way.

RSS 2.0 | Atom 1.0 | CDF

Send mail to the author(s) E-mail

Total Posts: 445
This Year: 7
This Month: 1
This Week: 1
Comments: 17

Sign In
Pick a theme:

# Tuesday, October 01, 2013
Tuesday, October 01, 2013 2:40:15 PM (GMT Daylight Time, UTC+01:00) ( SQL )


Very nice sproc to create random passwords in SQL

create proc [dbo].uspRandChars
    @len int,
    @min tinyint = 48,
    @range tinyint = 74,
    @exclude varchar(50) = '0:;<=>?@O[]`^\/',
    @output varchar(50) output
as 
    declare @char char
    set @output = ''
 
    while @len > 0 begin
       select @char = char(round(rand() * @range + @min, 0))
       if charindex(@char, @exclude) = 0 begin
           set @output += @char
           set @len = @len - 1
       end
    end
;
go

The above stored procedure creates strings of random characters based on four parameters that configure the result.

  1. LEN - specifies the length of the result (required)
  2. MIN - sets the starting ASCII code (optional: defaults to "48": which is the number zero)
  3. RANGE - determines the range of how many ASCII characters to include (optional: defaults to "74"  (48 + 74 = 122) where 122 is a lowercase "z")
  4. EXCLUDE - a string of characters to exclude from the final output (optional: defaults include zero, capital "O", and these punctuation marks :;<=>?@[]`^\/)

To use the stored procedure above, execute commands

declare @newpwd varchar(20)

-- all values between ASCII code 48 - 122 excluding defaults
exec [dbo].uspRandChars @len=8, @output=@newpwd out
select @newpwd

-- all lower case letters excluding o and l
exec [dbo].uspRandChars @len=10, @min=97, @range=25, @exclude='ol', @output=@newpwd out
select @newpwd

-- all upper case letters excluding O
exec [dbo].uspRandChars @len=12, @min=65, @range=25, @exclude='O', @output=@newpwd out
select @newpwd

-- all numbers between 0 and 9
exec [dbo].uspRandChars @len=14, @min=48, @range=9, @exclude='', @output=@newpwd out
select @newpwd
Comments [0] | | # 
# Monday, September 16, 2013
Monday, September 16, 2013 3:20:56 PM (GMT Daylight Time, UTC+01:00) ( SQL )

 

Prior to SQL Server 2012 concatenation was accomplished by using the plus (+) sign to concatenate fields together. The limitation of this method is if any of the fields you are concatenating are NULL, the entire result is NULL. With the introduction of SQL Server 2012 there is a new CONCAT() function that replaces NULL with an empty string. Take a look at this tip to see how this new function works and how it can be beneficial in your code.

For this demo I am going to use the Person.Person table from the AdventureWorks2012 database to demo having to generate a full name for creating mailing labels. First, the code below is the old technique to perform concatenation using the + sign:

SELECT 
    Title, 
    FirstName, 
    MiddleName, 
    LastName,
    Title+ ' ' + FirstName + ' ' + MiddleName + ' ' + LastName as MailingName
FROM Person.Person

As you can see in the screen shot below the MailingName is NULL for any row that has NULL for any one of the name columns. The only rows that have MailingName filled in have a value for all the title, firstname, middlename, and lastname columns. This could be corrected by wrapping ISNULL(column,'') around all the columns in the concatenated field to account for any values having nulls, but that code gets long, messy, and hard to read.

Concat the old way

Below is an example is using ISNULL along with the plus sign for concatenation. The ISNULL function will replace null values with the value noted in the second parameter, which in this example is an empty string.

SELECT 
    Title, 
    FirstName, 
    MiddleName, 
    LastName,
    ISNULL(Title,'') + ' ' + ISNULL(FirstName,'') + ' ' 
  + ISNULL(MiddleName,'') + ' ' + ISNULL(LastName,'') as MailingName
FROM Person.Person

As you can see in the example below, the MailingName is no longer NULL as it replaced the NULL values with an empty string. This achieves the same as using the CONCAT() function, but requires a lot more code and readability.

Concat with ISNULL

The next set of code is using the new CONCAT() function, new to SQL Server 2012. It replaces NULL values with an empty string of type VARCHAR(1). This code is much easier to read and write when you need to have NULL code handling in place.

SELECT 
    Title, 
    FirstName, 
    MiddleName, 
    LastName,
    CONCAT(Title,' ',FirstName,' ',MiddleName,' ',LastName) as MailingName
FROM Person.Person
Comments [0] | | # 
# Friday, December 07, 2012
Friday, December 07, 2012 1:14:58 PM (GMT Standard Time, UTC+00:00) ( SQL | SQL Server )

 

This nice section of code generates the field names, data types,  length and some other information for every field in a given table.  Just replace the table name value in the last line.

SELECT 
    c.name 'Column Name',
    t.Name 'Data type',
    c.max_length 'Max Length',
    c.precision ,
    c.scale ,
    c.is_nullable,
    ISNULL(i.is_primary_key, 0) 'Primary Key'
FROM    
    sys.columns c
INNER JOIN 
    sys.types t ON c.system_type_id = t.system_type_id
LEFT OUTER JOIN 
    sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
LEFT OUTER JOIN 
    sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
WHERE
    c.object_id = OBJECT_ID('TableName')
 
image
Comments [0] | | # 
# Wednesday, November 28, 2012
Wednesday, November 28, 2012 6:44:34 PM (GMT Standard Time, UTC+00:00) ( SQL )


You need to generate a random password in SQL Server.  Here’s how to do it

The following stored procedure creates strings of random characters based on four parameters that configure the result.

  1. LEN - specifies the length of the result (required)
  2. MIN - sets the starting ASCII code (optional: defaults to "48": which is the number zero)
  3. RANGE - determines the range of how many ASCII characters to include (optional: defaults to "74" (48 + 74 = 122) where 122 is a lowercase "z")
  4. EXCLUDE - a string of characters to exclude from the final output (optional: defaults include zero, capital "O", and these punctuation marks :;<=>?@[]`^\/)
create proc [dbo].uspRandChars
    @len int,
    @min tinyint = 48,
    @range tinyint = 74,
    @exclude varchar(50) = '0:;<=>?@O[]`^\/',
    @output varchar(50) output
as 
    declare @char char
    set @output = ''
 
    while @len > 0 begin
       select @char = char(round(rand() * @range + @min, 0))
       if charindex(@char, @exclude) = 0 begin
           set @output += @char
           set @len = @len - 1
       end
    end
;
go

To use the stored procedure issue commands such as the following.

declare @newpwd varchar(20)

-- all values between ASCII code 48 - 122 excluding defaults
exec [dbo].uspRandChars @len=8, @output=@newpwd out
select @newpwd

-- all lower case letters excluding o and l
exec [dbo].uspRandChars @len=10, @min=97, @range=25, @exclude='ol', @output=@newpwd out
select @newpwd

-- all upper case letters excluding O
exec [dbo].uspRandChars @len=12, @min=65, @range=25, @exclude='O', @output=@newpwd out
select @newpwd

-- all numbers between 0 and 9
exec [dbo].uspRandChars @len=14, @min=48, @range=9, @exclude='', @output=@newpwd out
select @newpwd
Comments [0] | | # 
Wednesday, November 28, 2012 3:15:39 PM (GMT Standard Time, UTC+00:00) ( SQL )

 

According to MSDN, coalesce returns the first non-null expression among its arguments.

For example,

SELECT COALESCE(NULL, NULL, NULL, GETDATE()) 

will return the current date. It bypasses the first NULL values and returns the first non-null value.

Using Coalesce to Pivot

A simple SELECT statement like this will pull back a set of rows like this

SELECT [ReasonForCall]
      ,[ReasonGroup]
  FROM [CallReasons]
  WHERE (ReasonGroup = 'COD-A')
image
If we want to pivot the data, we can use coalesce like this
DECLARE @ReasonForCall VARCHAR(1000) 

SELECT @ReasonForCall = COALESCE(@ReasonForCall,'') + ReasonForCall + ';'  
FROM CallReasons 
WHERE (ReasonGroup = 'COD-A') 

SELECT @ReasonForCall AS ReasonForCall 

image

Kill a Process

Being able to kill all the transactions in a database using three lines of code is very cool. If you have ever tried to restore a database and could not obtain exclusive access, you know how useful this can be.

DECLARE @SQL VARCHAR(8000) 

SELECT @SQL=COALESCE(@SQL,'')+'Kill '+CAST(spid AS VARCHAR(10))+ '; '  
FROM sys.sysprocesses  
WHERE DBID=DB_ID('AdventureWorks') 

PRINT @SQL --EXEC(@SQL) Replace the print statement with exec to execute 

will give you a result set such as the following.

Comments [0] | | # 
# Monday, November 26, 2012
Monday, November 26, 2012 7:39:38 PM (GMT Standard Time, UTC+00:00) ( SQL )


Nice article on how to determine the number of rows in every table in the database.

http://www.mssqltips.com/sqlservertip/2537/sql-server-row-count-for-all-tables-in-a-database/?utm_source=dailynewsletter&utm_medium=email&utm_content=headline&utm_campaign=20121121 

Approach 1: sys.partitions Catalog View

sys.partitions is an Object Catalog View and contains one row for each partition of each of the tables and most types of indexes (Except Fulltext, Spatial, and XML indexes). Every table in SQL Server contains at least one partition (default partition) even if the table is not explicitly partitioned.

The T-SQL query below uses the sys.partitions Catalog View to capture the row counts for all tables in a database.

SELECT
      QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) + '.' + QUOTENAME(sOBJ.name) AS [TableName]
      , SUM(sPTN.Rows) AS [RowCount]
FROM 
      sys.objects AS sOBJ
      INNER JOIN sys.partitions AS sPTN
            ON sOBJ.object_id = sPTN.object_id
WHERE
      sOBJ.type = 'U'
      AND sOBJ.is_ms_shipped = 0x0
      AND index_id < 2 -- 0:Heap, 1:Clustered
GROUP BY 
      sOBJ.schema_id
      , sOBJ.name
ORDER BY [TableName]
GO

Below are the highlights of this approach:

  1. Requires membership in the public role.
  2. Can be used even when working with source systems which offer limited privileges such as read-only.

Approach 2: sys.dm_db_partition_stats Dynamic Management View (DMV)

sys.dm_db_partition_stats is a Dynamic Management View (DMV) which contains one row per partition and displays the information about the space used to store and manage different data allocation unit types - IN_ROW_DATA, LOB_DATA and ROW_OVERFLOW_DATA.

The T-SQL query below uses the sys.dm_db_partition_stats DMV to capture the row counts for all tables in a database.

SELECT
      QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) + '.' + QUOTENAME(sOBJ.name) AS [TableName]
      , SUM(sdmvPTNS.row_count) AS [RowCount]
FROM
      sys.objects AS sOBJ
      INNER JOIN sys.dm_db_partition_stats AS sdmvPTNS
            ON sOBJ.object_id = sdmvPTNS.object_id
WHERE 
      sOBJ.type = 'U'
      AND sOBJ.is_ms_shipped = 0x0
      AND sdmvPTNS.index_id < 2
GROUP BY
      sOBJ.schema_id
      , sOBJ.name
ORDER BY [TableName]
GO

Below are the highlights of this approach:

  1. VIEW DATABASE STATE permissions are required in the database.
  2. The values in the sys.dm_db_partition_stats DMV are reset on server restart or when an object/partition is dropped and recreated.

In general, querying the Dynamic Management Views (DMVs), requires VIEW SERVER STATE or VIEW DATABASE STATE permissions based on the Dynamic Management View/Function which is being queried.


Approach 3: sp_MSforeachtable System Stored Procedure

sp_MSforeachtable is an undocumented system stored procedure which can be used to iterate through each of the tables in a database. In this approach we will get the row counts from each of the tables in a given database in an iterative fashion and display the record counts for all the tables at once.

The T-SQL query below uses the sp_MSforeachtable system stored procedure to iterate through each of the tables to capture the row count for all the tables in a database.

DECLARE @TableRowCounts TABLE ([TableName] VARCHAR(128), [RowCount] INT) ;
INSERT INTO @TableRowCounts ([TableName], [RowCount])
EXEC sp_MSforeachtable 'SELECT ''?'' [TableName], COUNT(*) [RowCount] FROM ?' ;
SELECT [TableName], [RowCount]
FROM @TableRowCounts
ORDER BY [TableName]
GO

Below are the highlights of this approach:

  1. This is an iterative approach which captures the row count for each of the individual tables, puts them together and displays the results for all the tables.
  2. sp_MSforeachtable is an undocumented system stored procedure.
  3. This approach can be used for testing purposes but it is not recommended for use in any production code. sp_MSforeachtable is an undocumented system stored procedure and may change anytime without prior notification from Microsoft.

Approach 4: COALESCE() Function

The COALESCE() function is used to return the first non-NULL value/expression among its arguments. In this approach we will build a query to get the row count from each of the individual tables with UNION ALL to combine the results and run the entire query.

The T-SQL query below uses the COALESCE() function to iterate through each of the tables to dynamically build a query to capture the row count from each of the tables (individual COUNT queries combined using UNION ALL) and provides the row counts for all the tables in a database.

DECLARE @QueryString NVARCHAR(MAX) ;
SELECT @QueryString = COALESCE(@QueryString + ' UNION ALL ','')
                      + 'SELECT '
                      + '''' + QUOTENAME(SCHEMA_NAME(sOBJ.schema_id))
                      + '.' + QUOTENAME(sOBJ.name) + '''' + ' AS [TableName]
                      , COUNT(*) AS [RowCount] FROM '
                      + QUOTENAME(SCHEMA_NAME(sOBJ.schema_id))
                      + '.' + QUOTENAME(sOBJ.name) + ' WITH (NOLOCK) '
FROM sys.objects AS sOBJ
WHERE
      sOBJ.type = 'U'
      AND sOBJ.is_ms_shipped = 0x0
ORDER BY SCHEMA_NAME(sOBJ.schema_id), sOBJ.name ;
EXEC sp_executesql @QueryString
GO

Below are the highlights of this approach:

  1. Can be used in cases where the number of tables is huge like say few hundred tables. This query can be modified to capture the row counts from a set of tables at one time instead of all the tables which might otherwise put a lot of load on the system.
  2. Can be used even when working with source systems which offer limited privileges such as read-only.
Comments [0] | | # 
# Thursday, October 04, 2012
Thursday, October 04, 2012 7:21:42 PM (GMT Daylight Time, UTC+01:00) ( SQL )
This is a common issue:  you import data with leading zeros (SSNs) and the leading zeros are dropped 
off in the results. This won’t happen if the field is defined as varchar in SQL Server but it will if

it is numeric field.
Example 1:
SELECT COALESCE(REPLACE(STR(SSN, 10, 0), ' ', '0'), 'NO SSN') AS SSN
FROM Table1
Example 2:
select
    SSN,
    coalesce(right(1000000000+ssn,9),'NO SSN') as NEW_SSN
from
    (-- Test Data
    select ssn = 004581944    union all
    select ssn = null
    ) a

Results:
SSN         NEW_SSN   
----------- --------- 
4581944     004581944
NULL        NO SSN
Comments [0] | | # 
# Friday, September 21, 2012
Friday, September 21, 2012 2:45:31 AM (GMT Daylight Time, UTC+01:00) ( SQL | SQL Server )


CREATE FUNCTION GetAverageSalesByProduct (@ProductID int)
RETURNS float
AS
BEGIN
    DECLARE @Average @float
    
    SELECT @Average = AVG(CAST(Quantity as float))
    FROM CurrentProducts cp
    INNER JOIN SalesInvoiceDetail sdd
    ON cp.ProductID = sdd.ProductID
    WHERE cp.ProductID = @ProductID
    
    IF(@Average IS NULL)
    SET @Average = 0 
    
    RETURN @Average
    
END

--To call this UDF

SELECT ProductID, ProductName
dbo.GetAverageSalesByProduct (ProductID) AS Rating
FROM CurrentProducts
ORDER BY ProductID

Comments [0] | | # 
# Thursday, September 20, 2012
Thursday, September 20, 2012 7:12:33 PM (GMT Daylight Time, UTC+01:00) ( SQL | SQL Server | SQL Server 2012 )


Paging in SQL Server 2012 is a breeze compared to previous versions

--Returns first 25 records
SELECT e.FirstName,
       e.LastName,
       e.Address
FROM HumanResources AS e
ORDER BY LastName
OFFSET 0 ROWS
FETCH NEXT 25 ROWS ONLY;

--Returns records 26-50
SELECT e.FirstName,
       e.LastName,
       e.Address
FROM HumanResources AS e
ORDER BY LastName
OFFSET 24 ROWS
FETCH NEXT 25 ROWS ONLY;
Comments [0] | | # 
# Friday, July 20, 2012
Friday, July 20, 2012 9:18:00 PM (GMT Daylight Time, UTC+01:00) ( SQL )

How to restore a SQL Server backup

 

Overview

The RESTORE DATABASE option allows you to restore either a full, differential, file or filegroup backup.

Explanation

When restoring a database will need exclusive access to the database, which means no other user connections can be using the database.

The RESTORE DATABASE option can be done using either T-SQL or using SQL Server Management Studio.


T-SQL

Restore a full backup
This will restore the database using the specified file. If the database already exists it will overwrite the files. If the database does not exist it will create the database and restore the files to same location specified in the backup. The original location can be checked by using RESTORE FILELISTONLY.

RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AdventureWorks.BAK'
GO

Restore a full backup allowing additional restores such as a differential or transaction log backup (NORECOVERY)
The NORECOVERY option leaves the database in a restoring state after the restore has completed. This allows you to restore additional files to get the database more current. By default this option is turned off.

RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AdventureWorks.BAK' WITH NORECOVERY
GO

Restore a differential backup
To restore a differential backup, the options are exactly the same. The first thing that has to happen is to do a full restore using the NORECOVERY option. Then the differential can be restored.

RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AdventureWorks.BAK' WITH NORECOVERY
GO
RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AdventureWorks.DIF'
GO

Restore using a backup file that has multiple backups
Let's say we use the same backup file, AdventureWorks.BAK, to write our full backup and our differential backup. We can use RESTORE HEADERONLY to see the backups and the positions in the backup file. Let's say that the restore headeronly tells us that in position 1 we have a full backup and in position 2 we have a differential backup. The restore commands would be.

RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AdventureWorks.BAK' WITH NORECOVERY, FILE = 1
GO
RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AdventureWorks.BAK' WITH FILE = 2
GO

Similarly, to restore a transaction log backup use the command below

Restore a transaction log backup
To restore a transaction log backup the database need to be in a restoring state. This means that you would have to restore a full backup and possibly a differential backup as well.

RESTORE LOG AdventureWorks FROM DISK = 'C:\AdventureWorks.TRN'
GO
Comments [0] | | # 
# Thursday, July 19, 2012
Thursday, July 19, 2012 6:20:44 PM (GMT Daylight Time, UTC+01:00) ( SQL )


Source: http://www.mssqltips.com/sqlservertip/1522/searching-and-finding-a-string-value-in-all-columns-in-a-sql-server-table/?utm_source=dailynewsletter&utm_medium=email&utm_content=headline&utm_campaign=2012717 

Problem
Sometimes there is a need to find if a string value exists in any column in your table.  Although there are system stored procedures that do a "for each database" or a "for each table", there is not a system stored procedure that does a "for each column".  So trying to find a value in any column in your database requires you to build the query to look through each column you want to search using an OR operator between each column.  Is there any way this can be dynamically generated?

Solution
Once again this is where T-SQL comes in handy along with the use of system tables or system views.  The code below allows you to search for a value in all text data type columns such as (char, nchar, ntext, nvarchar, text and varchar). 

The stored procedure gets created in the master database so you can use it in any of your databases and it takes three parameters:

  • stringToFind - this is the string you are looking for.  This could be a simple value as 'test' or you can also use the % wildcard such as '%test%', '%test' or 'test%'.
  • schema - this is the schema owner of the object
  • table - this is the table name you want to search, the procedure will search all char, nchar, ntext, nvarchar, text and varchar columns in the table

The first thing you need to do is create this stored procedure by copying the below code and executing it in a query window.

USE master 
GO 
CREATE PROCEDURE sp_FindStringInTable @stringToFind VARCHAR(100), @schema sysname, @table sysname 
AS 
DECLARE @sqlCommand VARCHAR(8000) 
DECLARE @where VARCHAR(8000) 
DECLARE @columnName sysname 
DECLARE @cursor VARCHAR(8000) 
BEGIN TRY 
SET @sqlCommand = 'SELECT * FROM ' + @schema + '.' + @table + ' WHERE' 
SET @where = '' 
SET @cursor = 'DECLARE col_cursor CURSOR FOR SELECT COLUMN_NAME 
   FROM ' + DB_NAME() + '.INFORMATION_SCHEMA.COLUMNS 
   WHERE TABLE_SCHEMA = ''' + @schema + ''' 
   AND TABLE_NAME = ''' + @table + ''' 
   AND DATA_TYPE IN (''char'',''nchar'',''ntext'',''nvarchar'',''text'',''varchar'')' 
EXEC (@cursor) 
OPEN col_cursor    
FETCH NEXT FROM col_cursor INTO @columnName    
WHILE @@FETCH_STATUS = 0    
BEGIN    
       IF @where <> '' 
SET @where = @where + ' OR' 
SET @where = @where + ' ' + @columnName + ' LIKE ''' + @stringToFind + '''' 
FETCH NEXT FROM col_cursor INTO @columnName    
END    
   CLOSE col_cursor    
DEALLOCATE col_cursor  
SET @sqlCommand = @sqlCommand + @where 
--PRINT @sqlCommand 
EXEC (@sqlCommand)  
END TRY 
BEGIN CATCH 
PRINT 'There was an error' 
IF CURSOR_STATUS('variable', 'col_cursor') <> -3 
BEGIN 
       CLOSE col_cursor    
DEALLOCATE col_cursor  
END 
END CATCH 

Once the stored procedure has been created you can run some tests.

Here are some tests that were done against the AdventureWorks database.

Find the value 'Irv%' in the Person.Address table.

USE AdventureWorks
GO
EXEC sp_FindStringInTable 'Irv%', 'Person', 'Address'

Find the value '%land%' in the Person.Address table.

USE AdventureWorks
GO
EXEC sp_FindStringInTable '%land%', 'Person', 'Address'

Find the value '%land%' in the Person.Contact table.

USE AdventureWorks
GO
EXEC sp_FindStringInTable '%land%', 'Person', 'Contact'

That's all there is to it.  Once this has been created you can use this against any table and any database on your server.

Comments [0] | | # 
# Tuesday, July 10, 2012
Tuesday, July 10, 2012 1:56:23 PM (GMT Daylight Time, UTC+01:00) ( SQL )


An interesting VB tool used to deploy SQL changes to a SQL Server application when the user does not have access to SQL Server Management Studio.

Comments [0] | | # 
# Wednesday, April 25, 2012
# Monday, April 09, 2012
Monday, April 09, 2012 6:18:00 PM (GMT Daylight Time, UTC+01:00) ( SQL )


Sometimes I need to look for all stored procedures containing <text>.  This is how you do it:

SELECT OBJECT_NAME(id) 
    FROM syscomments 
    WHERE [text] LIKE '%my search text%' 
    AND OBJECTPROPERTY(id, 'IsProcedure') = 1 
    GROUP BY OBJECT_NAME(id
Comments [0] | | # 
# Wednesday, November 16, 2011
Wednesday, November 16, 2011 2:36:36 PM (GMT Standard Time, UTC+00:00) ( SQL )

 

Suppose you have a SQL Server stored procedure that returns multiple result sets and you want to store all these results into another table.

CREATE procedure [dbo].[mytest] 
as 
select 1 as id, 'test1' as myname 
select 2 as id, 'test2' as myname

When you execute this procedure, it returns two result sets. The following code will copy these two result sets in a table variable.

declare @t table(id int, names varchar(100)) 
insert into @t 
exec mytest
select * from @t

Comments [0] | | # 
Wednesday, November 16, 2011 2:01:55 PM (GMT Standard Time, UTC+00:00) ( SQL )


http://csvexpress.expressor-software.com/

Some cool software to move CSV data around to different databases.

Comments [0] | | # 
# Thursday, November 10, 2011
Thursday, November 10, 2011 8:00:00 PM (GMT Standard Time, UTC+00:00) ( SQL )


SELECT * FROM Employees
SELECT * FROM EmployeeHistory

If table Employees has a one to many relationship with EmployeeHistory and you need to find any missing records from EmployeeHistory, then you could run the two queries above and compare them side-by-side but this pretty tedious and kind of dumb.  A better way is to run an unmatched query. A left outer join of the Employee table to the EmployeeHistory table on the EmpID field will get all matching and non-matching records. To look for every record where the Employee table does not have a matching record in the EmployeeHistory table, we look for a null in the EmployeeHistory table.

SELECT E.* FROM Employees
LEFT OUTER JOIN EmployeeHistory EH
ON E.EmpID = EH.EmpID
WHERE EH.EmpID IS NULL

Similarly, to insert the missing record into the EmployeeHistory table, you could do this:

INSERT INTO EmployeeHistory
SELECT E.* FROM Employee E
LEFT OUTER JOIN EmployeeHistory EH
ON E.EmpID = EH.EmpID
WHERE EH.EmpID IS NULL
Comments [0] | | # 
# Monday, October 31, 2011
Monday, October 31, 2011 2:16:36 PM (GMT Standard Time, UTC+00:00) ( SQL )
 
If you intend to create a back up of your database in Server A and restore it on a Server B, here's how to go about it. Create a .bak file (Open SSMS > Database > Right Click your database > Tasks > BackUp > Specify the destination).

  Once the .bak is created, copy this file from Server A to a Server B. We will assume that the file has been copied at the location ''C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\" at Server B. It can any location that you desire.
 
How to restore the .bak file

  Use this query:

  RESTORE DATABASE [SouthWind]
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\South.BAK'
WITH REPLACE,
MOVE 'SouthWind_Data' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\SouthWind_Data.MDF',
MOVE 'SouthWind_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\SouthWind_Log.LDF'
where FromDisk is where the .bak file is kept on Server B and MOVE specifies the location of the data and log file.

  /*
Backup database using a script
*/
CREATE PROCEDURE [dbo].[usersp_BackUpDB]
@DBNAME nvarchar(50),
@PATH nvarchar(200),
@BCKUPNAME nvarchar(50)
AS
BACKUP DATABASE @DBNAME
TO DISK = @PATH
WITH NOFORMAT, NOINIT,
NAME = @BCKUPNAME,
STATS = 10

You can call this script using :

DECLARE @return_value int
EXEC @return_value = [dbo].[usersp_BackUpDB]
@DBNAME = N'Northwind',
@PATH = N'C:\temp\Northwind.bak',
@BCKUPNAME = N'NW'
SELECT 'Return Value' = @return_value
GO

Comments [0] | | # 
Monday, October 31, 2011 2:10:41 PM (GMT Standard Time, UTC+00:00) ( SQL )

If you have a table in a database and you would like to copy the table to another database, use this query: SELECT * INTO AdventureWorks.dbo.CustomersTemp FROM Northwind.dbo.Customers

Just remember that using this query will only transfer the schema and data. It does not transfer the indexes, foreign keys, statistics etc.

If you want to transfer all the objects from one database to another, open Sql Server Management Studio > Right click on your database > All Tasks > Generate SQL Scripts. Then run these scripts against the new database.

Transfer both schema and data

To copy both data and schema, use the Microsoft SQL Server Database Publishing Wizard 1.1. This tool works for both SQL 2000 and SQL 2005 and generates a single SQL script file which can be used to recreate a database (both schema and data).

Comments [0] | | # 
# Thursday, October 06, 2011
Thursday, October 06, 2011 12:56:35 PM (GMT Daylight Time, UTC+01:00) ( SQL )

If you have a lot of stored procedures in your database and need to provide EXECUTE permission to these for specific users, rather than provide them to individual stored procedures, you can create a new group, add the user(s) to the group, and then assign whatever permissions to want to the group.

/* CREATE A NEW ROLE */

CREATE ROLE db_executor

/* GRANT EXECUTE TO THE ROLE */

GRANT EXECUTE TO db_executor

Image001
Comments [0] | | # 
# Monday, October 03, 2011
Monday, October 03, 2011 7:41:34 PM (GMT Daylight Time, UTC+01:00) ( SQL )

PinalDave has a great blog post on a neat product SQLBackupand FTP which does just that:

http://blog.sqlauthority.com/2011/10/03/sql-server-sql-backup-and-ftp-a-quick-and-handy-tool/

Product Link:

http://sqlbackupandftp.com/

Comments [0] | | # 
# Wednesday, September 28, 2011
Wednesday, September 28, 2011 5:14:46 PM (GMT Daylight Time, UTC+01:00) ( SQL )

Suppose you want to find out the path where the SQL Server database files(mdf and ldf) are located, using a query. You can use system procedure sp_helpdb and system view sysfiles to obtain this information.

The following methods show the path of the database files for master database.

Method 1:

Image001

Method 2:

Image002
Comments [0] | | # 
# Thursday, September 22, 2011
Thursday, September 22, 2011 3:51:11 PM (GMT Daylight Time, UTC+01:00) ( SQL )

Different ways of performing a LIKE search

SELECT *
FROM Employee
WHERE LastName LIKE 'A%'
OR LastName LIKE 'B%'
OR LastName LIKE 'C%'
OR LastName LIKE 'D%'
OR LastName LIKE 'E%'
OR LastName LIKE 'F%'
OR LastName LIKE 'G%'
OR LastName LIKE 'H%'
OR LastName LIKE 'I%'
OR LastName LIKE 'J%'
OR LastName LIKE 'K%'

 

SELECT
FROM
WHERE
LastName LIKE '[ABCDEFGHIJK]%'

SELECT
FROM
WHERE LastName LIKE '[A-K]%'

Comments [0] | | # 
# Monday, September 19, 2011
Monday, September 19, 2011 7:47:58 PM (GMT Daylight Time, UTC+01:00) ( SQL )

One of the nice things about SQL Server is the ability to send email using T-SQL.  The downside is that this functionality does not exist in SQL Server Express.  In this tip I will show you how to build a basic CLR stored procedure to send email messages from SQL Server Express, although this same technique could be used for any version of SQL Server.

http://www.mssqltips.com/sqlservertip/1795/send-email-from-sql-server-express-using-a-clr-stored-procedure/

Comments [0] | | # 
# Tuesday, September 06, 2011
Tuesday, September 06, 2011 2:45:06 PM (GMT Daylight Time, UTC+01:00) ( SQL )

When a copy and restore a SQL Server database from one server to another, the logins associated with that database are not copied along with it.  Here is the best method for transferring the logins which is explained in this MS KB article http://support.microsoft.com/kb/918992.

After you have created the script explained in the article above, execute the sp_help_revlogin stored procedure (EXEC sp_help_revlogin)  which creates CREATE LOGIN commands for every user.  Here are some examples:

--Login Eric

CREATE LOGIN [Eric] WITH PASSWORD = 0x0100F6C35D74E7D8A5035780081E02854C304F55DFFC

--Login NT AUTHORITY\SYSTEM

CREATE LOGIN [NT AUTHORITY\SYSTEM] FROM WINDOWS WITH DEFAULT_DATABASE = [master]

Now go to the new database, start SSMS and connect to the instance where you moved the database and execute the CREATE LOGIN scripts above.  You may have to specify a new domain name if the domain is different.

Comments [0] | | # 
Tuesday, September 06, 2011 1:59:50 PM (GMT Daylight Time, UTC+01:00) ( SQL )

Suppose you want to move some data from one table to another table and then delete the data from source table. In versions prior to SQL Server 2005, this is done in two steps.

1. Copy to target table
2. Delete from source table

In versions from SQL Server 2005/2008 onwards, you can use OUTPUT clause to do this

delete from @table1
output deleted.* into @table2
where cust_id in (1,2)

In a previous post, I discussed Using the T-SQL Output clause

http://www.stonecoastwebdesign.com/blog/2011/08/16/UsingTheTSQLOUTPUTClause.aspx

Comments [0] | | # 
# Thursday, September 01, 2011
Thursday, September 01, 2011 1:58:02 PM (GMT Daylight Time, UTC+01:00) ( SQL )

The sys.dm_exec_query_stats is a Dynamic Management view that gives the statistical information's about cached data. The sys.dm_exec_sql_text is the another view that gives actual text of the sql_handle which is in binary format.

--Top 10 codes that takes maximum time

select top 10 source_code,stats.total_elapsed_time/1000000 as seconds,

last_execution_time from sys.dm_exec_query_stats as stats

cross apply

(SELECT text as source_code FROM sys.dm_exec_sql_text(sql_handle))AS query_text

order by total_elapsed_time desc

--Top 10 codes that takes maximum physical_reads

select top 10 source_code,stats.total_elapsed_time/1000000 as seconds,

last_execution_time from sys.dm_exec_query_stats as stats

cross apply

(SELECT text as source_code FROM sys.dm_exec_sql_text(sql_handle))

AS query_text

order by total_physical_reads desc

Comments [0] | | # 
# Thursday, June 16, 2011
Thursday, June 16, 2011 7:39:40 PM (GMT Daylight Time, UTC+01:00) ( SQL )

Wow, do I feel stupid.  That’s probably because I am.  For months, I worked under the assumption that you cannot pass a list of varchar values to a SELECT statement, stored procedure or whatever without using some type of custom split function (see link below).  Turns out that this indeed cannot be done.

declare @company varchar

SET @dir = 'IBM, GMI Ratings, Bank of America'

SELECT *, company FROM Deposits WHERE (company IN (@company))

But this can be done

SELECT *, company FROM Deposits WHERE company IN (select company FROM Deposits_Temp)

Additional links:

·         http://www.stonecoastwebdesign.com/blog/2011/03/25/HowToPassACommaDelimitedStringPassedAsAParameterToAStoredProcedure.aspx

·         http://www.stonecoastwebdesign.com/blog/2008/02/03/SQLServerSplitFunction.aspx

Comments [0] | | # 
# Wednesday, June 15, 2011
Wednesday, June 15, 2011 6:24:24 PM (GMT Daylight Time, UTC+01:00) ( SQL )

SELECT    

table_catalog, table_name, column_name,       

data_type, character_maximum_length as max_length      

FROM

information_schema.columns      

ORDER

BY table_name, column_name

Comments [0] | | # 
# Friday, May 13, 2011
Friday, May 13, 2011 3:03:15 PM (GMT Daylight Time, UTC+01:00) ( SQL )
Friday, May 13, 2011 2:39:36 PM (GMT Daylight Time, UTC+01:00) ( SQL )

This came from Pinal Dave’s blog: http://blog.sqlauthority.com/2008/02/06/sql-server-import-csv-file-into-sql-server-using-bulk-insert-load-comma-delimited-file-into-sql-server/

First create a test table

USE Galactic

GO

CREATE TABLE CSVImport

(ID INT,

FirstName VARCHAR(50),

LastName VARCHAR(50),

Email VARCHAR(50))

GO

Then create a CSV file (CSVImport.csv) for test data

1,James,Smith,jsmith@stonecoastwebdesign.com

2,Meggie,Rogers,mrogers@stonecoastwebdesign.com

3,Anna,Jones,ajones@stonecoastwebdesign.com

4,Nathan,Rodriguez,nrodriguez@stonecoastwebdesign.com

Last, run the following script to import the CSV data

BULK
INSERT
CSVImport
FROM 'c:\csvimport.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO


--Check the contents of the table


SELECT *
FROM CSVImport
GO

Comments [0] | | # 
# Thursday, May 12, 2011
Thursday, May 12, 2011 8:18:59 PM (GMT Daylight Time, UTC+01:00) ( SQL | SSRS )

I’ve been learning SQL Server Reporting Services during my free time and here are some useful articles on the topic:

·         Designing a Tablix Report in SSRL 2008

·         Cascading Parameterized Report

·         Designing a Chart Report in SSRS 2008

·         Designing Key Performance Indicator Report in SSRS 2008

Comments [0] | | # 
Thursday, May 12, 2011 4:06:17 PM (GMT Daylight Time, UTC+01:00) ( SQL )

By default, you get both the date and time value whenever you retrieve a datetime value from the database. So something like :

SELECT GETDATE()

returns 2007-12-17 21:17:33.933

Let us quickly see how to return only the date portion in different formats

YYYY-MM-DD
SELECT CONVERT(VARCHAR(10), GETDATE(), 120)

returns 2007-12-17

MM-DD-YYYY
SELECT CONVERT(VARCHAR(10), GETDATE(), 110)

returns 12-17-2007


DD-MM-YYYY
SELECT CONVERT(VARCHAR(10), GETDATE(), 105)

returns 17-12-2007

Comments [0] | | # 
# Sunday, March 27, 2011
Sunday, March 27, 2011 2:41:54 AM (GMT Daylight Time, UTC+00:00) ( SQL )

 

 

DECLARE @Startdate DATETIME, @Enddate DATETIME
SET @Startdate = '2011-01-02 11:35:26'
SET @Enddate = '2011-03-25 03:15:31' 

-- Total seconds in a day
DECLARE @TotalSec int
SET @TotalSec = 24*60*60; 

-- Convert DateDiff into seconds
DECLARE @DiffSecs int
SET @DiffSecs = DATEDIFF(SECOND, @Startdate, @Enddate) 

SELECT
CONVERT(char(2), (@DiffSecs/@TotalSec))as [Days],
CONVERT(char(2), ((@DiffSecs%@TotalSec)/3600)) as [Hours],
CONVERT(char(2), (((@DiffSecs%@TotalSec)%3600)/60)) as [Minutes],
CONVERT(char(2), (((@DiffSecs%@TotalSec)%3600)%60)) as [Seconds]
Comments [0] | | # 
# Friday, March 25, 2011
Friday, March 25, 2011 6:51:40 PM (GMT Standard Time, UTC+00:00) ( SQL )

This sql lists the number of records in every table and the space used

 

USE Bonds_G269_Focus

GO

-- Count All Rows and Size of Table by SQLServerCurry.com

SELECT

TableName = obj.name,

TotalRows = prt.rows,

[SpaceUsed(KB)] = SUM(alloc.used_pages)*8

FROM sys.objects obj

JOIN sys.indexes idx on obj.object_id = idx.object_id

JOIN sys.partitions prt on obj.object_id = prt.object_id

JOIN sys.allocation_units alloc on alloc.container_id = prt.partition_id

WHERE

obj.type = 'U' AND idx.index_id IN (0, 1)

GROUP BY obj.name, prt.rows

ORDER BY TableName

Comments [0] | | # 
Friday, March 25, 2011 6:28:39 PM (GMT Standard Time, UTC+00:00) ( SQL )
  

I was experimenting with the FOR XML function in SQL.  Think how useful this would be in reports.

 

 

 

Image005
   -----à 
Image006

 

This small amount of code changes the code in the first table into the second.

 

CREATE Table Tempo

(

      Col1 varchar(5),

      Col2 varchar(2)

)

 

INSERT INTO Tempo VALUES('A','A1');

INSERT INTO Tempo VALUES('A','A2');

INSERT INTO Tempo VALUES('A','A3');

INSERT INTO Tempo VALUES('A','A4');

INSERT INTO Tempo VALUES('B','B1');

INSERT INTO Tempo VALUES('B','B2');

INSERT INTO Tempo VALUES('B','B3');

INSERT INTO Tempo VALUES('C','C1');

INSERT INTO Tempo VALUES('C','C2');

 

GO

 

SELECT DISTINCT

      Col1,

      Col2 = SUBSTRING(( SELECT ', ' + Col2 As [text()]

                                 FROM Tempo t2

                                 WHERE t2.Col1 = t1.Col1

                                 FOR XML PATH(''), elements

                                ), 2, 100

                              )

            FROM Tempo t1

Comments [0] | | # 
Friday, March 25, 2011 5:05:49 PM (GMT Standard Time, UTC+00:00) ( SQL )
Some time ago I posted code examples on how to use a user-defined function (UDF) to split a comma-delimitted string
to pass as a parameter into a stored procedure:

  http://www.stonecoastwebdesign.com/blog/2008/02/03/SQLServerSplitFunction.aspx
http://www.stonecoastwebdesign.com/blog/2009/07/13/SQLServerSplitFunction.aspx

  This approach works well, but I came across a much easier approach.

  First, create a table and some sample data

create table testing
(
id int,
names varchar(100)
)
go

  insert into testing (id, names)
select 1, 'Chung' union all
select 2, 'Freda' union all
select 3, 'Homer' union all
select 4, 'Paco' union all
select 5, 'Luis'

Next, create the stored procedure named proc_testing:

  create procedure proc_testing
(
@id varchar(100)
)
as
select id, names from testing
where charindex(',' + CAST(id as varchar(10)) + ',',','+@id+',') > 0

  This is how you execute it:

  exec proc_testing '1,2,4'

  Notice that it works even if you leave a comma at the begining or end of the string:

  exec proc_testing ',2,4,5,6'
exec proc_testing '1,3,5,'

Comments [0] | | # 
# Friday, March 18, 2011
# Thursday, March 03, 2011
Thursday, March 03, 2011 1:30:58 AM (GMT Standard Time, UTC+00:00) ( SQL )


DECLARE @MyString VARCHAR(50)  

SET @MyString = 'THIS IS MY TEST STRING'    

--REMOVE THE LAST CHARCTER BY USING LEFT FUNCTION  
SET @MyString = LEFT(@MyString, LEN(@MyString) - 1)  
SELECT @MyString AS [OUTPUT] 

 

Comments [0] | | # 
# Wednesday, March 02, 2011
Wednesday, March 02, 2011 2:51:33 AM (GMT Standard Time, UTC+00:00) ( SQL )


I have an SQL query where I am using the SUM function with the GROUP BY clause. Now what I require is that I need to select a couple of more columns from the table without adding those columns in the GROUP BY clause. Is there any way I can do this?

The answer is yes, but only in a very certain way.

Let's imagine that this is your current query:

 select foo , sum(bar) as sumbar from qux group by foo

Now, we want to add two more columns—let's call them other1 and other2—but we don't want to add them to the GROUP BY, because we still want only one row per foo in the results.

The very certain and only way that this will be allowed is by ensuring that the two new columns are inside aggregate functions.

Here's one example of many possibilities:

 select foo , sum(bar) as sumbar , max(other1) as maxcol1 , avg(other2) as avgcol2 from qux group by foo

What is NOT ALLOWED is to put "naked" columns (not inside aggregate functions) into the SELECT clause but leave them out of the GROUP BY clause:

 select foo , sum(bar) as sumbar , other1 , other2 from qux group by foo
Comments [0] | | # 
# Wednesday, February 16, 2011
Wednesday, February 16, 2011 2:55:43 PM (GMT Standard Time, UTC+00:00) ( C# | SQL )

SqlBulkCopy class is released in .Netframework 2.0 which can be used for performing bulk copy of data from one table to another table in same database server or different server similar to DTS and bcp utility at high speed. It can also be used to bulk copy from sqlserver 2005 database table to sql server 2000 database table, keeping in mind that destination table will have all the columns matching with the one we are copying from the source.

 

The below code will help us to that,

string connectionString = "Data Source=BABULIVES;Initial Catalog=tempdb;Integrated Security=True";      

        using (SqlConnection sourceConnection =
                   new SqlConnection("Data Source=BABULIVES;Initial Catalog=NorthWind;Integrated Security=True"))
        {

            sourceConnection.Open();
              using (SqlConnection destinationConnection =
                           new SqlConnection(connectionString))
                      {
                     destinationConnection.Open();            

                     using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnection))
                            {
                            bulkCopy.DestinationTableName = "dbo.Employees";

                            try
                            {
                                   bulkCopy.WriteToServer(reader);
                             }
                            catch (Exception ex)
                             {
                                   Response.Write(ex.Message);
                             }
                            finally
                             {                   
                                reader.Close();
                             }

                     }

                    }
       }     

Comments [0] | | # 
# Friday, February 11, 2011
Friday, February 11, 2011 5:39:23 PM (GMT Standard Time, UTC+00:00) ( SQL )
The basics of TRY CATCH error handling in T-SQL.

  Error Functions

BEGIN TRY
SELECT [Second] = 1/0
END TRY
BEGIN CATCH
SELECT [Error_Line] = ERROR_LINE(),
[Error_Number] = ERROR_NUMBER(),
[Error_Severity] = ERROR_SEVERITY(),
[Error_State] = ERROR_STATE()
SELECT [Error_Message] = ERROR_MESSAGE()
END CATCH


  Error Handling and Transactions

  BEGIN TRY
BEGIN TRANSACTION
INSERT INTO dbo.invoice_header
(invoice_number, client_number)
VALUES (2367, 19)
INSERT INTO dbo.invoice_detail
(invoice_number, line_number, part_number)
VALUES (2367, 1, 84367)
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF @@TRANCOUNT() > 0 ROLLBACK TRANSACTION
-- And do some cool error handling
END CATCH
Comments [0] | | # 
# Sunday, January 09, 2011
Sunday, January 09, 2011 1:44:07 AM (GMT Standard Time, UTC+00:00) ( SQL )


Here is the T-SQL code:

'xxx-xx-' + SUBSTRING(dbo.SSN, 6, 9) AS SSN

To display the code in your aspx file

SSN xxxxx<asp:Label ID="lblSSN" runat="server" Text='<%# Eval("SSN") %>' />
Comments [0] | | # 
# Monday, January 03, 2011
Monday, January 03, 2011 8:26:35 PM (GMT Standard Time, UTC+00:00) ( SQL )
# Wednesday, December 29, 2010
Wednesday, December 29, 2010 6:08:03 PM (GMT Standard Time, UTC+00:00) ( ObjectDataSource Control | SQL )
How to show the some connection stats on your SQL Server database
<script runat="server">

Private Sub Page_Load()
Dim connectionString As String = "Min Pool Size=10;data source=x.x.x.x;initial catalog=Bonds;user id=user;password=mypassword"
Dim con As New SqlConnection(connectionString)
Dim cmd As New SqlCommand("SELECT * FROM master..sysprocesses WHERE hostname<>''", con)
Using con


con.Open()
grdStats.DataSource = cmd.ExecuteReader()
grdStats.DataBind()

End Using
End Sub
</script>

<asp:GridView
id="grdStats"
Runat="server" />
Comments [0] | | # 
# Sunday, November 28, 2010
# Monday, September 13, 2010
# Wednesday, August 18, 2010
Wednesday, August 18, 2010 3:11:07 PM (GMT Daylight Time, UTC+01:00) ( ASP.NET | SQL )

 

Private Sub OnSelectedHandler(ByVal source As Object, ByVal e As SqlDataSourceStatusEventArgs)
        Dim cmd As IDbCommand
        cmd = e.Command
        Dim recordCount As Integer = e.AffectedRows()
        lblRecordCount.Text = "Showing " & recordCount & " IMFs"
End Sub


<asp:SqlDataSource ID="dsMyIMFs" runat="server" ConnectionString="<%$ ConnectionStrings:Tearsheet_ConString %>" 
SelectCommand="p_MyIMFs" SelectCommandType="StoredProcedure" OnSelected="OnSelectedHandler">
</asp:SqlDataSource>

<asp:Label ID="lblRecordCount" runat="server" />
Comments [0] | | # 
# Monday, August 02, 2010
Monday, August 02, 2010 4:31:29 PM (GMT Daylight Time, UTC+01:00) ( SQL )

 

DECLARE @t table (id int PRIMARY KEY, col1 int, col2 int, col3 int)

-- SAMPLE DATA
INSERT INTO @t SELECT 1, 45, 2, 14
INSERT INTO @t SELECT 2, 8, 1, 12
INSERT INTO @t SELECT 3, 21, 20, 8
INSERT INTO @t SELECT 4, 8, 5, 2
INSERT INTO @t SELECT 5, 23, 49, 7
INSERT INTO @t SELECT 6, 19, 7, 5
INSERT INTO @t SELECT 7, 7, 7, 2
INSERT INTO @t SELECT 8, 14, 17, 2
-- QUERY
SELECT id, MAX(col) AS maxValue
FROM
(  
    SELECT id, col FROM @t  
    UNPIVOT  
        (col FOR ListofColumns IN (col1,col2,col3))   
    AS unpivott) AS p
    GROUP BY id 
Comments [0] | | # 
Monday, August 02, 2010 4:30:08 PM (GMT Daylight Time, UTC+01:00) ( SQL )


The syntax shown below will drop a stored procedure if it exists and recreate it.

IF OBJECTPROPERTY(object_id('dbo.YourStoredProcName'), N'IsProcedure') = 1
DROP PROCEDURE [dbo].[YourStoredProcName]
GO
CREATE PROCEDURE dbo.YourStoredProcName
AS
-- Logic Comes Here
GO
Comments [0] | | # 
Monday, August 02, 2010 4:28:12 PM (GMT Daylight Time, UTC+01:00) ( SQL )

 

DECLARE @CurrentDate DateTime
SET @CurrentDate = '2010-03-27 14:15:15.390'

--Print Date in MM/YYYY format

SELECT DATENAME(m, @CurrentDate) 
+ ' ' + CONVERT(varchar(4), DATEPART(year, @CurrentDate))
as 'MM/YYYY'

--Print Date in MM-DD-YY format

SELECT CONVERT(varchar(10), @CurrentDate, 110)
as 'MM-DD-YY'
Comments [0] | | # 
# Wednesday, July 14, 2010
Wednesday, July 14, 2010 1:50:43 PM (GMT Daylight Time, UTC+01:00) ( SQL )


A very nice little free tool from RedGate software which gives you the ability to search through all of your SQL objects.  Examples from RedGate on how it is useful include:

    You want to rename one of your table columns but aren't sure what stored procedures reference it. Using SQL Search, you can search for the column name and find all the stored procedures where it is used.

    Finding anything in the SSMS object tree requires a lot of clicking. Using SQL Search, you can press the shortcut combo, start typing the name, and jump right there.

    You need to find stored procedures you’ve not yet finished writing. Using SQL Search, you can search for stored procedures containing the text 'TODO'.

    You are a DBA, and developers keep using 'SELECT *' in their views and stored procedures. You want to find all these and replace them with a correct list of columns to improve performance and prevent future bugs. Using SQL Search, you can look for 'SELECT *' in the text of stored procedures and views.

You can download the free tool here.

Comments [0] | | # 
# Thursday, June 03, 2010
Thursday, June 03, 2010 9:25:16 PM (GMT Daylight Time, UTC+01:00) ( SQL )

Here’s a simple query to find the Nth Maximum and Minimum values in a SQL Server table column using the Row_Number() function. We will find the 2nd highest and 3rd lowest values in the column.

DECLARE @tmp TABLE(id integer, amount integer)
INSERT INTO @tmp values(4, 9543)
INSERT INTO @tmp values(6, 34)
INSERT INTO @tmp values(3, 54)
INSERT INTO @tmp values(2, 6632)
INSERT INTO @tmp values(5, 645)
INSERT INTO @tmp values(1, 1115)
INSERT INTO @tmp values(7, 345)

-- FIND Nth Maximum value
SELECT id, amount
FROM(SELECT id, amount, Row_Number() OVER(ORDER BY amount DESC) AS highest FROM @tmp) as x
WHERE highest = 2
-- FIND Nth Minimum value
SELECT id, amount
FROM(SELECT id, amount, Row_Number() OVER(ORDER BY amount ASC) AS lowest FROM @tmp) as x
WHERE lowest = 3
image 
Comments [0] | | # 
Thursday, June 03, 2010 8:35:43 PM (GMT Daylight Time, UTC+01:00) ( SQL )


To get an accurate value of all the rows in a SQL Server table, use DBCC UPDATEUSAGE. Here’s a query that first uses DBCC UPDATEUSAGE and then count all the rows in all the tables of a database using the undocumented stored procedure sp_msForEachTable.

DECLARE @DynSQL NVARCHAR(255) 
SET @DynSQL = 'DBCC UPDATEUSAGE (' + DB_NAME() + ')' 
EXEC(@DynSQL) 
EXEC sp_msForEachTable'SELECT PARSENAME(''?'', 1) as TableName,COUNT(*) as NumberOfRows FROM ?'

image

Here is another method of doing this:

CREATE  Procedure dbo.RecordCountForAllTables 
AS
SELECT 
      'Table Name'=convert(char(25),t.TABLE_NAME),
      'Record Count'=max(i.rows)
FROM sysindexes i, INFORMATION_SCHEMA.TABLES t
WHERE t.TABLE_NAME = object_name(i.id)
      and t.TABLE_TYPE = 'BASE TABLE'
GROUP BY t.TABLE_SCHEMA, t.TABLE_NAME
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
 
Exec RecordCountForAllTables 
Comments [0] | | # 
Thursday, June 03, 2010 8:25:37 PM (GMT Daylight Time, UTC+01:00) ( SQL )


Here’s a query that converts a Date to a String in SQL Server:

DECLARE @Dt as DateTime
SET @Dt = '2010-02-22 11:45:17' SELECT CONVERT(CHAR(8), @Dt, 112)+ REPLACE(CONVERT(CHAR(8), @Dt, 114), ':', '')

In the query shown above, the style value 112 gives an output of yymmdd and a style value 114, gives an output of hh:mi:ss:mmm(24h). To display the milliseconds too, change Char(8) to Char(12).

image

Comments [0] | | # 
# Tuesday, June 01, 2010
Tuesday, June 01, 2010 8:34:40 PM (GMT Daylight Time, UTC+01:00) ( SQL )

Here are some simple T-SQL examples  I came across that I wasn’t aware of.

Select * from tblTest Where TestName Like '[ABC]%' (Return all rows of name start with A / B / C)

Select * from tblTest Where TestName Like '[^ABC]%' (Return all rows of name not start with A and B and C)

Select *  into #test from tblTest  (Create temporary table #test and insert all records from tblTest)

Select db_name() (shows the database name which you are working on)

Select @@Servername (Shows name of the server)
Comments [0] | | # 
# Monday, May 17, 2010
Monday, May 17, 2010 8:06:34 PM (GMT Daylight Time, UTC+01:00) ( SQL )


Here are some examples of stored procedures on how to do this

http://slashgeek.com/?p=105

Comments [0] | | # 
# Monday, May 10, 2010
Monday, May 10, 2010 9:14:56 PM (GMT Daylight Time, UTC+01:00) ( SQL )


The GROUP BY clause is logically processed before the SELECT clause, so at the GROUP BY phase, the OrderYear alias has not yet been created. By using a derived table that contains only the SELECT and FROM elements of the original query, you can create aliases and make them available to the outer query in any element.

SELECT ShipperID, CompanyName
FROM dbo.Shippers AS S
WHERE ShipperID NOT IN
    (SELECT O.ShipVia FROM dbo.Orders AS O
    WHERE O.CustomerID = N'LAZYK')
    
Derived tables Example

DECLARE @EmpID AS Int;
SET @EmpID = 3;

SELECT OrderYear, COUNT(DISTINCT CustomerID) As NumCusts
FROM (SELECT YEAR(OrderDate) AS OrderYear, CustomerID
      FROM dbo.Orders
      WHERE EmployeeID = @EmpID) AS D
GROUP BY OrderYear
Comments [0] | | # 
# Sunday, April 25, 2010
Sunday, April 25, 2010 2:08:14 PM (GMT Daylight Time, UTC+01:00) ( SQL )
# Friday, April 16, 2010
Friday, April 16, 2010 8:49:00 PM (GMT Daylight Time, UTC+01:00) ( SQL )


REPLACE searches the the first string for any occurance of the the second string and replaces it with the third string.

SELECT Replace('Clarence Thomas is a crazy fool!', 'crazy', 'old')

gives us

Clarence Thomas is a old fool!

Here is how to use REPLACE in conjunction with an UPDATE statement.

UPDATE dbo.authors
SET    city = REPLACE(city, 'Salt', 'Olympic')
WHERE  city LIKE 'Salt%';
Comments [0] | | # 
# Tuesday, April 13, 2010
Tuesday, April 13, 2010 3:40:18 PM (GMT Daylight Time, UTC+01:00) ( SQL )


Here are some simple examples on how to Begin, Commit and Rollback transactions in SQL Server 2008.

-- *********************
-- Explicit Transactions
-- *********************

-- Use @@TRANCOUNT
USE Northwind;
GO

BEGIN TRANSACTION;
UPDATE dbo.Employees
    SET LastName = 'Da Volio'
WHERE LastName = 'Davolio';

IF @@TRANCOUNT > 0
    BEGIN
        PRINT N'The transaction is active.';
        ROLLBACK TRANSACTION;
        PRINT N'The transaction has been rolled back.';
    END
GO

SELECT LastName FROM dbo.Employees;

-- Use @@ERROR 

SELECT message_id, text from sys.messages;

-- Simple error handling
DECLARE @err int
BEGIN TRANSACTION;
INSERT INTO dbo.Customers (CustomerID, CompanyName)
    VALUES ('ALFKI', 'New ALFKI');
SET @Err = @@ERROR
IF @Err = 0
    BEGIN
        PRINT 'Commit transaction.';
        COMMIT
    END
ELSE
    BEGIN    
        PRINT 'Error = ' + CAST(@Err AS varchar(10));
        ROLLBACK
    END
GO
Comments [0] | | # 
Tuesday, April 13, 2010 3:31:18 PM (GMT Daylight Time, UTC+01:00) ( SQL )


Here are some examples on how to use Try/Catch and RaiseError to address error handling in SQL Server 2008

-- ************************
-- TRY/CATCH Error Handling
-- ************************

BEGIN TRY
    BEGIN TRANSACTION;
    INSERT INTO dbo.Customers (CustomerID, CompanyName)
        VALUES ('ALFKI', 'New ALFKI');
    COMMIT TRANSACTION;
    PRINT 'Transaction committed.';
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION;
    PRINT 'Transaction rolled back.';
END CATCH
GO

-- Display detailed error information
BEGIN TRY
    BEGIN TRANSACTION;
    INSERT INTO dbo.Customers (CustomerID, CompanyName)
        VALUES ('ALFKI', 'New ALFKI');
    COMMIT TRANSACTION;
    PRINT 'Transaction committed.';
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION;
    SELECT
        ERROR_NUMBER() AS Number,
        ERROR_SEVERITY() AS Severity,
        ERROR_STATE() AS State,
        ERROR_PROCEDURE() AS procedureName,
        ERROR_LINE() AS Line,
        ERROR_MESSAGE() AS messageText;
    PRINT 'Transaction rolled back.';
END CATCH
GO

-- Use RAISERROR in a TRY/CATCH
BEGIN TRY
    RAISERROR ('Error severity 9', 9, 1);
    RAISERROR ('Error severity 16', 16, 1);
END TRY

BEGIN CATCH
    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;

    SELECT 
        @ErrorMessage = ('In CATCH block: ' + ERROR_MESSAGE()),
        @ErrorSeverity = ERROR_SEVERITY();
    RAISERROR (@ErrorMessage, @ErrorSeverity, 1);
END CATCH;
GO

-- Create a stored procedure using TRY/CATCH and RAISERROR
CREATE PROCEDURE dbo.InsertCategoryTryCatch
    @CategoryName nvarchar(15) = NULL,
    @CategoryID int = NULL OUTPUT,
    @ReturnCode int = NULL OUTPUT,
    @ReturnMessage nvarchar(255) = NULL OUTPUT 
AS
    SET NOCOUNT ON

    BEGIN TRY
        -- Test if CategoryName null.
        IF @CategoryName IS NULL
            RAISERROR ('Validation failed: Null CategoryName.', 16, 1);

        -- Test if CategoryName already exists.
        IF EXISTS (SELECT CategoryName FROM dbo.Categories 
                WHERE CategoryName=@CategoryName)
            RAISERROR ('Validation failed: Duplicate CategoryName.', 16, 1);

        -- Begin the transaction.
        BEGIN TRANSACTION
        INSERT INTO dbo.Categories (CategoryName)
            VALUES (@CategoryName);
        COMMIT TRANSACTION;
        SET @CategoryID=SCOPE_IDENTITY();
        SELECT @ReturnCode=0, 
            @ReturnMessage='Success! ' + @CategoryName + ' added.';
    END TRY

    BEGIN CATCH
        -- Test to see if we're in a transaction.
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;

        -- Return error information to client.
        SELECT @ReturnCode = ERROR_NUMBER();
        SELECT @ReturnMessage = ERROR_MESSAGE() +
            ' Severity=' + CAST(ERROR_SEVERITY() AS nvarchar(2)) + 
            '; State=' + CAST(ERROR_STATE() AS nvarchar(2)) + 
            '; Proc=' + CAST(ERROR_PROCEDURE() AS nvarchar(50)) + 
            '; Line=' + CAST(ERROR_LINE() AS nvarchar(10));
    END CATCH
GO
Comments [0] | | # 
# Monday, April 05, 2010
Monday, April 05, 2010 12:54:54 AM (GMT Daylight Time, UTC+01:00) ( SQL )
-- Scalar function with parameter
CREATE FUNCTION dbo.fnProductsSoldByProduct
    (@ProductID int = null) 
    RETURNS int
    WITH RETURNS NULL ON NULL INPUT
AS
    BEGIN
        DECLARE @total int;
        SELECT @Total = SUM(Quantity)
            FROM dbo.[Order Details]
        WHERE ProductID = @ProductID;
        
        RETURN @Total;
    END
GO

Examples of how to call the function above
SELECT dbo.fnProductsSoldByProduct(NULL);
SELECT dbo.fnProductsSoldByProduct(DEFAULT);
SELECT dbo.fnProductsSoldByProduct(3);

-- Causes an error:
SELECT dbo.fnProductsSoldByProduct();
GO
--Another example
-- Create a scalar function to calculate a discount
CREATE FUNCTION dbo.fnCalcDiscountPrice 
(
    @price money,
    @discount int
)
RETURNS money
WITH RETURNS NULL ON NULL INPUT
AS
    BEGIN
        DECLARE @work money
        SELECT @work = @price - (@price * (@discount * .01))
        RETURN @work
    END;


-- Execute the function against the Products table
SELECT ProductID, ProductName, UnitPrice, 
    dbo.fnCalcDiscountPrice(UnitPrice,5) AS [5% Off],
    dbo.fnCalcDiscountPrice(UnitPrice,3) AS [3% Off]
FROM dbo.Products
ORDER BY UnitPrice DESC;
 
Comments [0] | | # 
# Sunday, April 04, 2010
Sunday, April 04, 2010 9:56:40 PM (GMT Daylight Time, UTC+01:00) ( SQL )


Here is a nice example of how to use output parameters in a stored procedure to debug the proc.

CREATE PROC dbo.InsertShipperOutputParams
    @CompanyName nvarchar(40) = NULL,
    @Phone nvarchar(24) = NULL,
    @ShipperID int = NULL OUTPUT,
    @ReturnCode int OUTPUT,
    @ReturnMessage varchar(255) OUTPUT
AS
    SET NOCOUNT ON;
    SET @ReturnCode = 0;
    SET @ReturnMessage = '';

    IF @CompanyName IS NULL
        SET @ReturnMessage = 'ShipperID is null.';
    ELSE
        BEGIN
            DECLARE @id int, @rows int, @error int;
            INSERT INTO dbo.Shippers(CompanyName, Phone)
                VALUES (@CompanyName, @Phone);
            SELECT @error = @@ERROR, @rows = @@ROWCOUNT, 
                @id = SCOPE_IDENTITY();
            IF @error = 0 AND @rows > 0
                BEGIN
                    SET @ShipperID = @id;
                    SET @ReturnCode = @rows;
                    SET @ReturnMessage = 'Success!';
                END
            ELSE
                SET @ReturnMessage = 'Error=' 
                    + ISNULL(@error, 'No number for this error.');
        END
GO
Comments [0] | | # 
# Wednesday, March 31, 2010
Wednesday, March 31, 2010 3:51:30 PM (GMT Daylight Time, UTC+01:00) ( ASP.NET | SQL | VB.NET )


This is a nice example of how to pass a delimitted list of values to SQL Server and perform some kind of dbase function with those whether that be an UPDATE, DELETE or whatever.

First here is the sample stored procedure we are going to call updating each company sent to it by the PK value, id_company

USE [my_dbase]
GO
CREATE PROCEDURE [dbo].[p_UpdateCompany] 
    @id_assign int = NULL
AS
BEGIN

    SET NOCOUNT ON;

    UPDATE Companies 
    SET markedfordeletion = 1 --or whatever could be a delete statement as well    

END

Next, here is the VB.NET code used to call the sproc

Protected Sub btnCompanySubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs)
        Dim strConnection As SqlConnection
        Dim strSql As String
        Dim cmd As SqlCommand
        Dim CompanyValues As String = txtid_company.Text
        
        Dim CompanyList As New ArrayList
        CompanyList.AddRange(Split(CompanyValues, ","))
        
        strConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("conString_Repository").ConnectionString)
        strSql = "p_UpdateCompany"
        cmd = New SqlCommand(strSql)
        cmd.CommandType = CommandType.StoredProcedure
        
        For i As Integer = 0 To CompanyList.Count - 1
            Dim CompanyVal As Integer = CompanyList(i)
            
            cmd.Parameters.Add("@id_Company", SqlDbType.Int, CompanyVal)
            Try
                strConnection.Open()
                cmd.Connection = strConnection
                cmd.ExecuteNonQuery()
            Finally
                strConnection.Close()
            End Try
        Next
    End Sub

<form id="form1" runat="server">
    <div>
        <table id="Companies">
            <tr>
                <td>Enter id_company values:<br />
                    (separated by commas)</td>
                <td><asp:TextBox ID="txtid_company" runat="server" TextMode="MultiLine" Rows="5" Columns="25" /></td>
            </tr>           
            <tr>
                <td colspan="2">
                    <asp:Button ID="btnCompanySubmit" runat="server" onclick="btnCompanySubmit_Click" Text="Submit" />
                </td>
            </tr>           
        </table>
    </div>
    
    </form>
Comments [0] | | # 
# Friday, February 12, 2010
Friday, February 12, 2010 8:52:54 PM (GMT Standard Time, UTC+00:00) ( SQL )


Here are some examples of using IN vs. EXISTS

--Customers from Spain who made no orders 

SELECT CustomerID, CompanyName 
FROM Customers AS C
WHERE Country = 'Spain'
 AND NOT EXISTS
    (SELECT * FROM Orders AS O
        WHERE O.CustomerID = C.CustomerID)
SELECT CustomerID, CompanyName 
FROM Customers AS C
WHERE Country = 'Spain'
    AND CustomerID NOT IN(SELECT CustomerID FROM Orders 
                          WHERE CustomerID IS NOT NULL)  
  
--Return customer for whom you cannot find any employee from the USA
--For whom you cannot find any order placed for the subject customer and by the select employee                          
SELECT * FROM Customers AS C
WHERE NOT EXISTS
    (SELECT * FROM Employees AS E
     WHERE Country = 'USA'
    AND NOT EXISTS
        (SELECT * FROM Orders AS O
        WHERE O.CustomerID = C.CustomerID
        AND O.EmployeeID = E.EmployeeID))    
Comments [0] | | # 
# Monday, February 01, 2010
Monday, February 01, 2010 3:33:22 PM (GMT Standard Time, UTC+00:00) ( SQL )


I found this interesting. If any of the following field are NULL, then the concatenated value for DirName is also NULL.   You would think that if the less commonly populated fields of Prefix of Suffix were null, dirfname and dirlname would still be returned but this isn’t the case. 

select prefix + ' ' + dirfname + ' ' + dirlname + ' ' + suffix As DirName from individual

To solve this issue, use the IsNull function.

select isnull(prefix, '') + ' ' + dirfname + ' ' + dirlname + ' ' + isnull(suffix, '') As DirName from individual
Comments [0] | | # 
# Thursday, January 28, 2010
Thursday, January 28, 2010 3:32:08 PM (GMT Standard Time, UTC+00:00) ( SQL )


Case Sensitive SQL Query Search

If Column1 of Table1 has following values ‘CaseSearch, casesearch, CASESEARCH, CaSeSeArCh’, following statement will return you all the four records.

SELECT Column1
FROM Table1
WHERE Column1 = 'casesearch'

To make the query case sensitive and retrieve only one record (“casesearch”) from above query, the collation of the query needs to be changed as follows.

SELECT Column1
FROM Table1
WHERE Column1 COLLATE Latin1_General_CS_AS = 'casesearch'

Adding COLLATE Latin1_General_CS_AS makes the search case sensitive.

Default Collation of the SQL Server installation SQL_Latin1_General_CP1_CI_AS is not case sensitive.

To change the collation of the any column for any table permanently run following query.

ALTER TABLE Table1
ALTER COLUMN Column1 VARCHAR(20)
COLLATE Latin1_General_CS_AS

To know the collation of the column for any table run following Stored Procedure.

EXEC sp_help DatabaseName

Second results set above script will return you collation of database DatabaseName.

Comments [0] | | # 
# Tuesday, January 19, 2010
Tuesday, January 19, 2010 1:58:07 AM (GMT Standard Time, UTC+00:00) ( SQL )


I’m working on a large project for a federal agency and they want to start using some sensitive data on a Web site I’m working on for them.  While SSL is being used, they also require that some of the data being stored in the database be encrypted.  I found a pretty simple to do it on this article: http://www.sql-server-helper.com/functions/string-encryption.aspx

Here is the code:

Encryption

CREATE FUNCTION [dbo].[Encrypt] ( @pClearString VARCHAR(100) )
RETURNS NVARCHAR(100) WITH ENCRYPTION AS
BEGIN
    
    DECLARE @vEncryptedString NVARCHAR(100)
    DECLARE @vIdx INT
    DECLARE @vBaseIncrement INT
    
    SET @vIdx = 1
    SET @vBaseIncrement = 128
    SET @vEncryptedString = ''
    
    WHILE @vIdx <= LEN(@pClearString)
    BEGIN
        SET @vEncryptedString = @vEncryptedString + 
                                NCHAR(ASCII(SUBSTRING(@pClearString, @vIdx, 1)) +
                                @vBaseIncrement + @vIdx - 1)
        SET @vIdx = @vIdx + 1
    END
    
    RETURN @vEncryptedString

END
GO

Decrypt

CREATE FUNCTION [dbo].[Decrypt] ( @pEncryptedString NVARCHAR(100) )
RETURNS VARCHAR(100) WITH ENCRYPTION AS
BEGIN

DECLARE @vClearString VARCHAR(100)
DECLARE @vIdx INT
DECLARE @vBaseIncrement INT

SET @vIdx = 1
SET @vBaseIncrement = 128
SET @vClearString = ''

WHILE @vIdx <= LEN(@pEncryptedString)
BEGIN
    SET @vClearString = @vClearString + 
                        CHAR(UNICODE(SUBSTRING(@pEncryptedString, @vIdx, 1)) - 
                        @vBaseIncrement - @vIdx + 1)
    SET @vIdx = @vIdx + 1
END

RETURN @vClearString

END
GO

Here are examples of how to use these functions
INSERT INTO USERS values('TestUser1', dbo.ENCRYPT('TestPW1'))
SELECT UserID, dbo.Decrypt(UserPW) AS Password from Users

Comments [0] | | # 
# Wednesday, January 13, 2010
Wednesday, January 13, 2010 3:38:29 AM (GMT Standard Time, UTC+00:00) ( SQL )

Two great articles on this topic

http://www.sqlteam.com/article/working-with-time-spans-and-durations-in-sql-server

http://weblogs.sqlteam.com/jeffs/jeffs/jeffs/jeffs/jeffs/jeffs/archive/2007/01/02/56079.aspx

Comments [0] | | # 
# Monday, January 11, 2010
Monday, January 11, 2010 9:11:01 PM (GMT Standard Time, UTC+00:00) ( SQL )


A query with an ORDER BY clause cannot be used as a table expression.  That is, a view, inline table-valued function,sub0query, derived table
or common table expression.  This produces the error message "The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified."

SELECT UserID, Name 
FROM (SELECT UserID, Name FROM TSubscribers ORDER BY Name) AS D 

The way to get around this is to use the TOP option.

SELECT UserID, Name
FROM (SELECT TOP 100 Percent UserID, Name, Company FROM TSubscribers ORDER BY Name) AS D

SELECT CASE

SELECT CustomerID, City,
    CASE
        WHEN COUNT(orderID) = 0 Then 'No_Orders'
        WHEN COUNT(orderID) <=2 Then 'Up_to_2_orders'
        WHEN COUNT(orderID) > 2 Then 'More_than_2_orders'
    END AS Category
    FROM Customers
    GROUP BY CustomerID, City
Comments [0] | | # 
# Wednesday, November 18, 2009
Wednesday, November 18, 2009 9:11:53 PM (GMT Standard Time, UTC+00:00) ( SQL )


RSS Reporter is a very simple tool and that is the beauty of it. In addition of providing one of the best ways to monitor SQL Server jobs it provides something even cooler – it allows you to write any T-SQL query you want and with one click the results of your query are streamed in an elegant, standard RSS feed that you can subscribe to from any device.

http://www.xsqlsoftware.com/Product/Sql_Server_Rss_Reporter.aspx

Comments [0] | | # 
# Friday, November 06, 2009
Friday, November 06, 2009 1:43:05 AM (GMT Standard Time, UTC+00:00) ( ASP.NET | SQL )


 

This is an interesting example useful for many different applications.  Suppose you create a repeater containing multiple rows and each field contains various types of controls (textboxes, dropdownlists, checkboxes etc.) and you want to update every field in every row with a single button click.  The repeater control has no inherent update capabilities but it can be done.

Sub btnUpdateProductDetails_Click(ByVal sender As Object, ByVal e As System.EventArgs)
      
        Dim id_fieldSelected As Integer
        Dim ProductSelected As Integer
        Dim FeatureSelected As Integer
        Dim SectionSelected As Integer
        Dim createdDateSelected As DateTime
        Dim createdBySelected As String = lblEnteredBy.Text
        Dim IsActiveSelected As Boolean
        Dim ProductLabelSelected As Integer
        
        Dim dataItem As RepeaterItem
        For Each dataItem In rptFieldsInProducts.Items
            
            id_fieldSelected = CType(dataItem.FindControl("lblId_field"), Label).Text
            ProductSelected = CType(dataItem.FindControl("ddlProducts"), DropDownList).SelectedValue
            FeatureSelected = CType(dataItem.FindControl("ddlFeatures"), DropDownList).SelectedValue
            SectionSelected = CType(dataItem.FindControl("ddlSections"), DropDownList).SelectedValue
            createdDateSelected = CType(dataItem.FindControl("lblCreatedDate"), Label).Text
            IsActiveSelected = CType(dataItem.FindControl("chkIsActive"), CheckBox).Checked
            ProductLabelSelected = CType(dataItem.FindControl("ddlProductLabel"), DropDownList).SelectedValue   
            
            'Updates the repeater row
            'The stored procedure that updates this needs these parameter
            'id_field, id_product, id_ProductFeature, id_ProductSection, createdDate, createdBy, IsActive, id_ProductLabel
            
            UpdateProductDetails(id_fieldSelected, ProductSelected, FeatureSelected, SectionSelected, createdDateSelected, _
createdBySelected, IsActiveSelected, ProductLabelSelected) Next End Sub Sub UpdateProductDetails(ByVal id_fieldSelected As Integer, ByVal ProductSelected As Integer, ByVal FeatureSelected As Integer, _ ByVal SectionSelected As Integer, ByVal createdDateSelected As DateTime, ByVal createdBySelected As String, _ ByVal IsActiveSelected As Boolean, ByVal ProductLabelSelected As Integer) Dim strConnection As SqlConnection Dim strSql As String Dim cmd As SqlCommand strConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("conString_FieldInventory").ConnectionString) strSql = "proc_InsertFieldsInProducts" cmd = New SqlCommand(strSql) cmd.CommandType = CommandType.StoredProcedure cmd.Parameters.AddWithValue("@id_field", SqlDbType.Int).Value = id_fieldSelected cmd.Parameters.AddWithValue("@id_Product", SqlDbType.Int).Value = ProductSelected cmd.Parameters.AddWithValue("@id_ProductFeature", SqlDbType.Int).Value = FeatureSelected cmd.Parameters.AddWithValue("@id_ProductSection", SqlDbType.Int).Value = SectionSelected cmd.Parameters.AddWithValue("@createdDate", SqlDbType.DateTime).Value = createdDateSelected cmd.Parameters.AddWithValue("@createdBy", SqlDbType.VarChar).Value = createdBySelected cmd.Parameters.AddWithValue("@IsActive", SqlDbType.Bit).Value = IsActiveSelected cmd.Parameters.AddWithValue("@id_ProductLabel", SqlDbType.Int).Value = ProductLabelSelected cmd.Parameters.AddWithValue("@batchCreatedDate", SqlDbType.DateTime).Value = System.DateTime.Now Try strConnection.Open() cmd.Connection = strConnection cmd.ExecuteNonQuery() Finally strConnection.Close() End Try End Sub

Here is the code for the repeater used for the code above

<asp:SqlDataSource ID="dsProductName" runat="server" ConnectionString="<%$ ConnectionStrings:conString_FieldInventory%>"
    ProviderName="System.Data.SqlClient" SelectCommand="SELECT DISTINCT(ProductName), id_Product FROM Products 
WHERE ProductName IS NOT NULL ORDER BY ProductName"
> </asp:SqlDataSource> <asp:SqlDataSource ID="dsFeatureName" runat="server" ConnectionString="<%$ ConnectionStrings:conString_FieldInventory%>" ProviderName="System.Data.SqlClient" SelectCommand="SELECT DISTINCT(FeatureName), id_ProductFeature FROM ProductFeatures
WHERE FeatureName IS NOT NULL ORDER BY FeatureName"
> </asp:SqlDataSource> <asp:SqlDataSource ID="dsSectionName" runat="server" ConnectionString="<%$ ConnectionStrings:conString_FieldInventory%>" ProviderName="System.Data.SqlClient" SelectCommand="SELECT DISTINCT(SectionName), id_ProductSection FROM ProductSections
WHERE SectionName IS NOT NULL ORDER BY SectionName"
> </asp:SqlDataSource> <asp:SqlDataSource ID="dsProductLabel" runat="server" ConnectionString="<%$ ConnectionStrings:conString_FieldInventory%>" ProviderName="System.Data.SqlClient" SelectCommand="SELECT DISTINCT(ProductLabel), id_ProductLabel FROM ProductLabels
WHERE ProductLabel IS NOT NULL ORDER BY ProductLabel"
> </asp:SqlDataSource>
<asp:Repeater id="rptFieldsInProducts" runat="server" OnItemDataBound="ddlProducts_ItemDataBound">
      <HeaderTemplate>
         <fieldset style="width: 900px">
         <legend><h4 class="pageHeader">Product Details</h4></legend>
         <table width="900px" Cellpadding="3" CellSpacing="1" border="0">
             <tr>
                <td class="fieldDetailLabel" width="15%">Product</td>
                <td class="fieldDetailLabel" width="15%">Feature</td>
                <td class="fieldDetailLabel" width="15%">Section</td>                
                <td class="fieldDetailLabel" width="15%">Label</td>
                <td class="fieldDetailLabel" width="15%">Is Active?</td>
            </tr>                  
      </HeaderTemplate>
      <ItemTemplate>                   
             <tr>
               
               <td class="fieldControl" width="25%">
               <asp:DropDownList id="ddlProducts" Runat="Server"
                    DataSourceID="dsProductName"
                    DataTextField="ProductName" 
                    DataValueField="id_Product"
                    CssClass="fieldControl" />
               </td>
               <td class="fieldControl" width="25%">
               <asp:DropDownList id="ddlFeatures" Runat="Server"
                    DataSourceID="dsFeatureName"
                    DataTextField="FeatureName" 
                    DataValueField="id_ProductFeature"
                    CssClass="fieldControl" />
               </td>      
               <td class="fieldControl" width="25%">
               <asp:DropDownList id="ddlSections" Runat="Server"
                    DataSourceID="dsSectionName"
                    DataTextField="SectionName" 
                    DataValueField="id_ProductSection"
                    CssClass="fieldControl" />
               </td>                                            
               <td>
               <asp:DropDownList id="ddlProductLabel" Runat="Server"
                    DataSourceID="dsProductLabel"
                    DataTextField="ProductLabel" 
                    DataValueField="id_ProductLabel"
                    CssClass="fieldControl" />
               </td>
               <td><asp:Checkbox ID="chkIsActive" runat="server" Checked='<%# Eval("IsActive") %>' /></td>
               <td><asp:Label ID="lblId_Field" runat="server" Text='<%# Eval("id_field") %>' class="displayNone" />
               <asp:Label ID="lblCreatedDate" runat="server" Text='<%# Eval("createdDate") %>' class="displayNone" />
               <asp:Label ID="lblCreatedBy" runat="server" Text='<%# Eval("createdBy") %>' class="displayNone" /></td>           
            </tr>
      </ItemTemplate>
      <FooterTemplate>
         <tr>
             <td colspan="5" align="center"><asp:Button ID="btnUpdateProductDetails" Text="Update Product Details" 
CssClass="button" runat="server" OnClick="btnUpdateProductDetails_Click"/></td> </tr> </table> </FooterTemplate> </asp:Repeater>
Comments [0] | | # 
# Tuesday, November 03, 2009
Tuesday, November 03, 2009 1:35:54 PM (GMT Standard Time, UTC+00:00) ( SQL )


This is from the article: http://www.sqlservercentral.com/articles/Administration/68271/

  • You have forgotten (or do not have) the "sa" password
  • The builtin\administrators account has been removed for security reasons
  • You cannot connect to the SQL Server instance

First off you need to shut down all the SQL Server related services. This can be done manually or you can use the batch file below. Either way you need to ensure that all SQL Server services are stopped. If you do the manual process see Image 1, which is a screen shot of the services you need to stop.

If you use this batch file, copy it to your editor like Notepad and save is as a batch file with the .bat extension. If you are stopping an instance you will have to modify the file.

@echo off
cls
echo.*****************************************
echo.**** Shutting Down SQL2005 Services ****
echo.*****************************************
echo.
echo. Shutting Down Integration Services...
net stop "SQL Server Integration Services"
echo.
echo. Shutting Down Full Text Search...
net stop "SQL Server FullText Search (MSSQLSERVER)"
echo.
echo. Shutting Down SQL Agent...
net stop "SQL Server Agent (MSSQLSERVER)"
echo. 
echo. Shutting Down Analysis Services..
net stop "SQL Server Analysis Services (MSSQLSERVER)"
echo.
echo. Shutting Down Reporting Services...
net stop "SQL Server Reporting Services (MSSQLSERVER)"
echo.
echo. Shutting Down SQL Browser...
net stop "SQL Server Browser"
echo.
echo. Shutting Down SQL Server...
net stop "SQL Server (MSSQLSERVER)"
echo.
echo. 
echo. To stop the SQL Server Brower use this command: net stop "SQL Server Browser"
echo.
echo.
echo.**** Shut Down Completed ****
echo.

The service window should look like this once completed. See Image 2. Now you can open a command line window by clicking on START then RUN finally type: cmd, which will open a new window. From here you execute the .bat file you saved.

Once executed you will see that all the SQL Server services have stopped. If not, stop anything that might still be running. This step is important because if any other SQL Server services are still running you will not be able to connect when SQL Server is started in single user mode.

Now you need to start just the SQL Server in single user mode. In the command line window change to the directory that has SQL Server installed. In my configuration it's here on the C: drive:

cd\program files\microsoft sql server\mssql.1\mssql\binn

This location may be different if you are accessing an instance.

Type the following in the command window for the default instance

sqlservr.exe -m

For a named instance type

sqlservr.exe -m -s <instance name>

Once this is executing you will see screen scroll and then stop. This process is not hung just waiting as SQL Server is now running in single user mode.

Next open another command line window (cmd) and now you can use the sqlcmd tool included with SQL 2005 installation. In this new window type the following and hit enter.

sqlcmd -E

A new prompt will appear. The buildin\administrators account needs to be created and added to the proper role. The following is what you would type in the new command line window:

create login [builtin\administrators] from windows
go
exec sp_addsrvrolemember [builtin\administrators], [sysadmin]
go
shutdown
go

The first line will create a login called builtin\administrators as per the one created within the OS. GO command executes the previous command. Now you add this login to the sysadmin role and finally you shut down the SQL Server that is running in single user mode. Note the first command line window exits the execution of SQL Server and returns the prompt. You may now close this window.

PS. Below is a batch file you can use to start up all SQL Server related servers. I use both to stop and start SQL Server services as needed. If you have stopping an instance you will have to modify the file.

@echo off
cls
echo.---------------------------------------
echo.---- Starting Up SQL2005 Services ----
echo.---------------------------------------
echo.
echo. Starting Up SQL Server...
net start "SQL Server (MSSQLSERVER)"
echo.
echo. Starting Up SQL Agent...
net start "SQL Server Agent (MSSQLSERVER)"
echo.
echo. Starting Up Integration Services...
net start "SQL Server Integration Services"
echo.
echo. Starting Up Full Text Search...
net start "SQL Server FullText Search (MSSQLSERVER)"
echo. 
echo. Starting Up Analysis Services..
net start "SQL Server Analysis Services (MSSQLSERVER)"
echo.
echo. Starting Up Reporting Services...
net start "SQL Server Reporting Services (MSSQLSERVER)"
echo.
echo. Starting Up SQL Browser...
echo. 
echo. To start the SQL Server Brower use this command: net start "SQL Server Browser"
echo.
echo.---- SQL 2005 Servers Start-Up Completed ----
echo.
Comments [0] | | # 
# Tuesday, October 20, 2009
Tuesday, October 20, 2009 6:54:57 PM (GMT Daylight Time, UTC+01:00) ( SQL )


http://www.sommarskog.se/dyn-search-2005.html#sp_executesql

I create a lot of search forms and I’ve always just used in-line SQL by examining each form element value and then building a SQL string to run against a view or table.  This article discusses how to build a stored procedure in SQL Server 2005 or 2008 using the system stored procedure, sp_executesql.

Here is my example

CREATE PROCEDURE [dbo].[p_Search]

    @id_field int = NULL,
    @FieldName varchar(300) = NULL,
    @TableName varchar(250) = NULL,
    @FieldType varchar(3000) = NULL,
    @DataEntry varchar(250) = NULL                  
                   
AS                                                                    
DECLARE @sql  nvarchar(max),
    @paramlist  nvarchar(4000)                                 
                                     
SELECT @sql =                                                  
    'SELECT * FROM v_FieldDetail          
     WHERE  1 = 1'                                                 
                                                                   
IF @id_field IS NOT NULL                                            
   SELECT @sql = @sql + ' AND id_field = @xid_field'           
                                                                   
IF @FieldName IS NOT NULL                                           
   SELECT @sql = @sql + ' AND FieldName LIKE + ''%'' + @xFieldName + ''%'''            

IF @TableName IS NOT NULL                                           
   SELECT @sql = @sql + ' AND TableName LIKE + ''%'' + @xTableName + ''%''' 
--This one utilizes a split function (below) to accept a comma-delimited string for use with an IN clause
IF @FieldType IS NOT NULL                                           
    SELECT @sql = @sql + ' AND FieldType IN ( SELECT * from dbo.Split( @xFieldType, '',''))'
IF @DataEntry IS NOT NULL                                           
   SELECT @sql = @sql + ' AND DataEntry LIKE + ''%'' + @xDataEntry + ''%'''
                                                                  
   SELECT @sql = @sql + ' ORDER BY FieldName'
    

--PRINT @sql

SELECT @paramlist =                      
      '@xid_field int,
      @xFieldName varchar(300), 
     @xTableName varchar(250),
    @xFieldType varchar(3000),
    @xDataEntry varchar(250)                       
                                                                   
EXEC sp_executesql @sql, @paramlist,@id_field,
    @FieldName,
    @TableName,
    @FieldType,
    @DataEntry
            
                                      
To call this stored procedure and bind the results to a Gridview, do something like this:
Protected Sub btnSearch_Click(ByVal sender As Object, ByVal e As System.EventArgs)       

        Dim ds As DataSet           
        Dim strSql As String = "p_Search"
        Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("conString_FieldInventory").ConnectionString)
        
        Dim cmd As New SqlCommand(strSql, con)
        cmd.CommandType = CommandType.StoredProcedure
        
           'p_Search uses dynamic SQL so we pass a value to it only when there is one
           If lblid_field2Text <> "" Then
               cmd.Parameters.Add("@id_field", SqlDbType.Int).Value = Convert.ToInt32(lblid_field.Text)        
        End If
        
        If txtFieldName.Text <> "" Then
            cmd.Parameters.Add("@FieldName", SqlDbType.VarChar).Value = txtFieldName.Text
        End If
        
        If txtTableName.Text <> "" Then
            cmd.Parameters.Add("@TableName", SqlDbType.VarChar).Value = txtTableName.Text
        End If
   
        ‘This one passes a comma-delimited string for @FieldType which is used in the split function
        If ddlFieldType.SelectedValue <> "" Then
            Dim strSearchValue As String = ""
            Dim li As ListItem
            For Each li In ddlFieldType.Items
                If li.Selected = True Then
                    strSearchValue = strSearchValue & li.Value & "#"
                End If
            Next
            strSearchValue = Left(strSearchValue, (Len(strSearchValue) - 1))
            strSearchValue = Replace(strSearchValue, "#", ",")
            cmd.Parameters.AddWithValue("@FieldType", SqlDbType.VarChar).Value = strSearchValue
        End If
        
        If txtDataEntry.Text <> "" Then
            cmd.Parameters.Add("@DataEntry", SqlDbType.VarChar).Value = txtDataEntry.Text
        End If         
        
        Try
            con.Open()
            Dim MyAdapter As New SqlDataAdapter(cmd) 
            
            ds = New DataSet()
            MyAdapter.Fill(ds, "Requests")
              
            Dim intRecordCount As Integer = ds.Tables(0).Rows.Count() 
            lblRowCount.Text = "Your search returned " & intRecordCount & " records"
            
            GridView1.DataSource = ds.Tables("Requests").DefaultView
            GridView1.DataBind()
        Finally
            con.Close()
        End Try        
    End Sub

--Split function

CREATE FUNCTION [dbo].[Split](@String varchar(8000), @Delimiter char(1))     
returns @temptable TABLE (items varchar(8000))     
as     
begin     
    declare @idx int     
    declare @slice varchar(8000)     
    
    select @idx = 1     
        if len(@String)<1 or @String is null  return     
    
    while @idx!= 0     
    begin     
        set @idx = charindex(@Delimiter,@String)     
        if @idx!=0     
            set @slice = left(@String,@idx - 1)     
        else     
            set @slice = @String     
        
        if(len(@slice)>0)
            insert into @temptable(Items) values(@slice)     

        set @String = right(@String,len(@String) - @idx)     
        if len(@String) = 0 break     
    end 
return     
end  
Comments [0] | | # 
# Monday, September 21, 2009
Monday, September 21, 2009 8:07:01 PM (GMT Daylight Time, UTC+01:00) ( SQL )


This is an interesting example of how to check for a duplicate record in a table.  If this record does not exist, then proceed with the INSERT.  If a duplicate already exists, then do an UPDATE instead.

-- Check for duplicate Employee. If no there is duplicate, do an INSERT.
IF (NOT EXISTS (SELECT E.SSN
      FROM EmployeeTable E, inserted
      WHERE E.SSN = inserted.SSN))
   INSERT INTO EmployeeTable
      SELECT EmployeeID,SSN, Department, Salary
      FROM inserted
ELSE
--If there is a duplicate, change to UPDATE so that there will not
--be a duplicate key violation error.
   UPDATE EmployeeTable
      SET EmployeeID = I.EmployeeID,
          Department = I.Department,
          Salary = I.Salary
   FROM EmployeeTable E, inserted I
   WHERE E.SSN = I.SSN
END

A second way of doing this using a parameter is to count the number of records matching the given criteria.

IF (SELECT COUNT(UserID) 
FROM TLoginStatus
WHERE UserID = @UserID) = 0
        
BEGIN 
INSERT INTO TLoginStatus(UserID, SessionID) Values (@UserID, @SessionID)
     
END 
ELSE
         UPDATE TLoginStatus
         SET SessionID = @SessionID
         WHERE UserID = @UserID
Comments [0] | | # 
# Thursday, August 27, 2009
Thursday, August 27, 2009 8:15:56 PM (GMT Daylight Time, UTC+01:00) ( SQL )

Beautify your SQL code with this handy little utility

http://www.dpriver.com/pp/sqlformat.htm

Comments [0] | | # 
# Wednesday, August 26, 2009
Wednesday, August 26, 2009 2:29:14 PM (GMT Daylight Time, UTC+01:00) ( SQL )


You can move SQL Server databases a few ways: backup the database and restore it in a new location; detach it and reattach it in the new location or use the ALTER command.  This example uses the latter.

First create 3 databases named db1, db2, db3 using SQL Server Management Studio

Create database db1
  
Create database db2
  
Create database db3

Next, create a stored procedure named p_MoveDatabase compliments of James DiMauro (http://www.sqlservercentral.com/articles/ALTER+DATABASE+MODIFY+FILE/67527/)

*/
  
IF ( OBJECT_ID('dbo.p_MoveDatabase') IS NOT NULL )
  
begin
  
   DROP PROCEDURE dbo.p_MoveDatabase
  
end
  
GO
  
  
create procedure p_MoveDatabase 
         
  
  @NewDataFolder nvarchar(1000),
  
    @NewLogFolder nvarchar(1000),
  
     @DbList nvarchar(4000) 
as
        Begin
        declare @DbTable table (lkey int identity (1,1) primary key, dbname nvarchar(100))
  
        declare @FileTable table (lkey int identity (1,1) primary key, [name]nvarchar(100), physical_name nvarchar(1000), [type] int )
  
    declare @sql nvarchar(4000)
  
       declare @count int,
  
                       @RowNum int
  
       declare @DbName nvarchar(100)
  
     declare @OldPath nvarchar(1000)
  
   declare @Type int
  
 declare @LogicalName nvarchar(100)
  
        declare @ParmDefinition nvarchar(1000)
  
    declare @FileName nvarchar(100)
  
   declare @NewPath nvarchar(1000)
  
   declare @ShowAdvOpt int
  
   declare @XPCMD int
        set nocount on; 
  
  
        --insert into @DbTable (dbname) values ('testdb1')
  
        --insert into @DbTable (dbname) values ('testdb2') 
  
if right(@DbList,1) = ','
  
Begin
  
      print 'DbList must NOT end with "''"'
  
     return
  
End
declare @MyString NVARCHAR(100)
  
declare @Pos INT
  
declare @NextPos INT
  
declare @String NVARCHAR(4000)
  
declare @Delimiter NVARCHAR(1)
set @String = @DbList
  
set @Delimiter = ','
SET @String = @String + @Delimiter
  
SET @Pos = charindex(@Delimiter,@String)
WHILE (@pos <> 0)
  
BEGIN
  
      SET @MyString = substring(@String,1,@Pos - 1)
  
       insert into @DbTable (dbname) values (LTRIM(RTRIM(@MyString)))
        SET @String = substring(@String,@pos+1,len(@String))
  
      SET @pos = charindex(@Delimiter,@String)
  
END 
  
        set @ShowAdvOpt = cast(( select [value] from sys.configurations where [name] = 'show advanced options') as int)
  
   set @XPCMD = cast(( select [value] from sys.configurations where [name] = 'xp_cmdshell') as int)
  
        if right(@NewDataFolder,1)<> '\' or  right(@NewLogFolder,1)<>'\'
  
 Begin
  
             print 'new path''s must end with \'
  
               return
  
    end
        EXEC sp_configure 'show advanced option', '1'
  
     RECONFIGURE
  
  
        exec sp_configure 'xp_cmdshell' , '1'
  
     RECONFIGURE
  
        print 'NewMdfFolder is ' + @NewDataFolder
  
        print 'NewLdfFolder is ' + @NewLogFolder 
  
        SET @RowNum = 1
  
   SET @count = (select count(*) from @DbTable)
  
        while @RowNum <= @count
  
   Begin
                select @DbName = DBName from @DbTable
  
             where lKey = @RowNum
  
                set @sql = 'select name, physical_name, type from  ' + @DbName + '.sys.database_files'
                insert into @FileTable
  
            exec sp_executesql @sql
  
             
  
  
                -- kill all user connections by setting to single user with immediate
  
             set @sql=  'ALTER DATABASE [' + @DbName  + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE'
  
            print ''
  
          print 'Executing line  -' + @sql
  
          exec sp_executesql @sql
                -- set db off line
  
                set @sql = 'ALTER DATABASE [' + @DbName + '] SET OFFLINE;'
  
               print ''
  
          print 'Executing line - ' + @sql
  
          exec sp_executesql @sql
                select * from @FileTable
  
          while @@rowcount > 0    
  
          begin
  
                     select top 1 @OldPath = physical_name, @Type = [type], @LogicalName = [name] from @FileTable
                        --move physical files
  
                     set @FileName = (SELECT REVERSE(SUBSTRING(REVERSE(@OldPath), 0, CHARINDEX('\', REVERSE(@OldPath), 1))))
  
                     if @type = 0
  
                              begin
  
                                     set @NewPath = @NewDataFolder + @FileName
  
                         end
  
                       else
  
                              begin
  
                                     set @NewPath = @NewLogFolder + @FileName
  
                          end
                        set @Sql = 'EXEC master..xp_cmdshell ''MOVE "' + @OldPath + '" "' + @NewPath +'"'''    
  
                  print ''
  
                  print 'Executing line  -' + @sql
  
                  exec sp_executesql @sql
  
                        --alter file paths
  
                        set @sql = 'ALTER DATABASE ' + @DbName + ' MODIFY FILE (NAME = ' + @LogicalName + ', FILENAME = "' + @NewPath   + '")'
  
                    exec sp_executesql @sql 
  
                        delete from @FileTable where [name] = @LogicalName
  
                  select * from @FileTable
                end --while 
  
  
                set @sql = 'ALTER DATABASE [' + @DbName + '] SET ONLINE;'
  
         print ''
  
          print 'Executing line  -' + @sql
  
          exec sp_executesql @sql
                SET @RowNum = @RowNum + 1 
                -- allow multi user again.
  
                set @sql=  'ALTER DATABASE [' + @DbName  + '] SET MULTI_USER'
  
             print ''
  
          print 'Executing line  -' + @sql
  
          exec sp_executesql @sql
  
        end
  
        exec sp_configure 'xp_cmdshell' , @XPCMD
  
  reconfigure
        EXEC sp_configure 'show advanced option', @ShowAdvOpt
  
     RECONFIGURE
End --procedure

To call this script, you must pass the names of the new database and log file folders as well as the names of the database(s) that you want to move.

exec p_MoveDatabase @NewDataFolder = 'c:\sqldata\', @NewLogFolder= 'c:\sqllog\',@DbList = 'db1, db2, db3'
Comments [0] | | # 
# Monday, July 13, 2009
Monday, July 13, 2009 5:28:49 PM (GMT Daylight Time, UTC+01:00) ( SQL )


Some time ago, I posted something about using user-defined function to split a comma-delimited string into a usable format for searches. This one is simpler.

CREATE FUNCTION dbo.Split(@String varchar(8000), @Delimiter char(1))     
returns @temptable TABLE (items varchar(8000))     
as     
begin     
    declare @idx int     
    declare @slice varchar(8000)     
    
    select @idx = 1     
        if len(@String)<1 or @String is null  return     
    
    while @idx!= 0     
    begin     
        set @idx = charindex(@Delimiter,@String)     
        if @idx!=0     
            set @slice = left(@String,@idx - 1)     
        else     
            set @slice = @String     
        
        if(len(@slice)>0)
            insert into @temptable(Items) values(@slice)     

        set @String = right(@String,len(@String) - @idx)     
        if len(@String) = 0 break     
    end 
return     
end

To use this function do something like this:

CREATE PROCEDURE [dbo].[ba_Company_Profile_Div4_Customize]
(
@id_company int,
@UpdateDescription varchar(8000)
)
AS 
BEGIN
SELECT 
id_event,
EventDate,
UpdateDescription,
EventDescription

FROM events

WHERE id_company=@id_company
AND
UpdateDescription IN (SELECT * FROM dbo.Split(@UpdateDescription,','))
ORDER BY EventDate DESC
END
GO
Comments [0] | | # 
# Monday, June 15, 2009
# Wednesday, March 18, 2009
Wednesday, March 18, 2009 8:17:26 PM (GMT Standard Time, UTC+00:00) ( SQL )

 

SQL Server's 'smalldatetime' data type includes, as you may have guessed, both the date and time in this format mm/dd/yyyy 00:00:00.  Often times you'll want to display just the date part of this without the time.

Here are a couple of ways of doing this.

The first is explicitly convert the smalldatetime field to CHAR field like this

> CONVERT(CHAR(10), dbo.Events.EventDate, 101) AS EventDate

This produces the format that we want but it's no longer a date field and if it's used in a gridview then you won't be able to sort the data using this field.

Another approach is to get the number of days in the date since 'date 1' and add it to 'date 1'. You get a clean new date where the time component is 0.

> DATEADD(dd, - DATEDIFF(dd, dbo.Events.EventDate, 1), 1) AS EventDate

Comments [0] | | # 
# Friday, February 13, 2009
Friday, February 13, 2009 9:24:34 PM (GMT Standard Time, UTC+00:00) ( SQL )


CREATE PROCEDURE [dbo].[GetLastAccountLogin] 
    -- OUTPUT parameter to hold the count.
    @AccountNo Varchar(50), 
    @ReturnVal smalldatetime = ' ' OUTPUT 
AS 
    -- This will return the last date returned by the SELECT query. 
    Set @ReturnVal = SELECT MAX(Login) FROM TUserLogs WHERE AccountNo=@AccountNo



CREATE PROCEDURE [dbo].[GetTotalUserCompanyProfileViews] 
    -- OUTPUT parameter to hold the count.
    @UserID int,
    @ReturnVal int OUTPUT 
AS 
    -- This will return the totals returned by the SELECT query.     
    SET @ReturnVal = SELECT COUNT(ID) FROM TTrackSearchResults WHERE UserID=@UserID
Comments [0] | | # 
# Thursday, February 12, 2009
Thursday, February 12, 2009 4:48:35 PM (GMT Standard Time, UTC+00:00) ( SQL )

In the past, I would usually just create a client side or front-end function to change the way data is displayed to the user but this example shows how to do this in SQL Server using a User Defined Function or UDF.

CREATE FUNCTION [dbo].[GradeConversion](@Grade varchar(50))
RETURNS varchar(50)
AS
BEGIN
    DECLARE @MyOutput varchar(50)
    If @Grade='A'
        SET @MyOutput = 'Low Concern'
    Else if @Grade='B'
        SET @MyOutput =  'Low Concern'
    Else if @Grade='C'
        SET @MyOutput =  'Moderate Concern'
    Else if @Grade='D'
        SET @MyOutput =  'High Concern'
    Else if @Grade='F'
        SET @MyOutput =  'Very High Concern'
    Else
        SET @MyOutput =  'Not Rated'
RETURN @MyOutput
END

To call this function you would simply do this dbo.GradeConversion(dbo.companyRatings.ScoreCEOComp) AS ScoreCEOComp

Comments [0] | | # 
# Friday, December 12, 2008
Friday, December 12, 2008 8:18:44 PM (GMT Standard Time, UTC+00:00) ( ASP.NET | SQL )


This example shows how you can use a DbTransaction object to add transaction context when using the SqlDataSource control to update data.

<%@Page  Language="VB" %>
<%@Import Namespace="System.Data" %>
<%@Import Namespace="System.Data.Common" %>
<%@Import Namespace="System.Diagnostics" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<script runat="server">

 Sub On_Click(ByVal source As Object, ByVal e As EventArgs)
        SqlDataSource1.Update()
 End Sub 'On_Click

 Sub On_Sql_Updating(ByVal source As Object, ByVal e As SqlDataSourceCommandEventArgs)
     Dim command as DbCommand
     Dim connection as DbConnection
     Dim transaction as DbTransaction

     command = e.Command
     connection = command.Connection     
     connection.Open()     
     transaction = connection.BeginTransaction()
     command.Transaction = transaction

 End Sub 'On_Sql_Updating

 Sub On_Sql_Updated(ByVal source As Object, ByVal e As SqlDataSourceStatusEventArgs)

    Dim command As DbCommand
    Dim transaction As DbTransaction

    command = e.Command
    transaction = command.Transaction

    ' We must succeed for the data change to be committed. For 
    ' simplicity, we set this value to true. 
    Dim OtherProcessSucceeded as Boolean = True

    If (OtherProcessSucceeded) Then
        transaction.Commit()
        Label2.Text="The record was updated successfully!"
    Else    
        transaction.Rollback()
        Label2.Text="The record was not updated."
    End If
    End Sub 'On_Sql_Updated
</script>

<html  >
  <head id="Head1" runat="server">
    <title>Command Transaction</title>
</head>
<body>
    <form id="form1" runat="server">
      <asp:SqlDataSource
          id="SqlDataSource1"
          runat="server"
          ConnectionString="<%$ ConnectionStrings:MyDatabase%>"
          SelectCommand="SELECT ID, Title, Director FROM Movies"
          UpdateCommand="UPDATE Movies SET Title=@Title WHERE ID=@ID"
          OnUpdating="On_Sql_Updating"
          OnUpdated ="On_Sql_Updated">
          <UpdateParameters>
              <asp:ControlParameter Name="Title" ControlId="TextBox1" PropertyName="Text" />
              <asp:ControlParameter Name="ID" ControlId="DropDownList1" PropertyName="SelectedValue" />
          </UpdateParameters>
      </asp:SqlDataSource>

      <asp:DropDownList
          id="DropDownList1"
          runat="server"
          DataTextField="Title"
          DataValueField="ID"
          DataSourceID="SqlDataSource1">
      </asp:DropDownList>

      <br /><br />
      <asp:Label id="Label1" runat="server" Text="Enter a new movie title." AssociatedControlID="TextBox1" />
      <asp:TextBox id="TextBox1" runat="server" />
      <asp:Button id="Submit" runat="server" Text="Submit" OnClick="On_Click" />

      <br /><asp:Label id="Label2" runat="server" Text="" />

    </form>
  </body>
</html>
Comments [0] | | # 
# Tuesday, November 18, 2008
Tuesday, November 18, 2008 7:59:46 PM (GMT Standard Time, UTC+00:00) ( ASP.NET | Database | SQL )

One of the problems with the DBDataReader object is that it keeps an open server-side connection while you are looping through the results of a query.  If you try to execute another command while the first command is still executing, you receive an 'InvalidoperationException' stating that "There is already an open DataReader associated with this connection which must be closed first."  You can avoid this exception by setting the MultipleActiveResultSets connection string option to True when connecting to SQL Server 2005 and above.

<configuration> 
<connectionStrings> 
<clear /> 
<add name="PublishersConnectionString" connectionString="Data Source=localhost;Initial Catalog=Publishers;User ID=xxx;Password=xxx" 
providerName="System.Data.SqlClient; MultipleActiveResultSets=True" /> </connectionStrings>
 
  Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)
        Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("PublishersConnectionString").ConnectionString)

        'Create the command object, passing in the SQL string
        Dim strSQL As String = "SELECT au_id, LastName FROM Publishers"
        Dim cmd As New SqlCommand(strSQL)

        Try
            con.Open()
            cmd.Connection = con
            Dim reader As SqlDataReader = cmd.ExecuteReader()
            While reader.Read()
                Dim cmdSales As SqlCommand
                cmdSales.CommandType = CommandType.Text
                cmdSales.CommandText = "SELECT  SUM(royaltyper) FROM TitleAuthor WHERE (au_id = @auID)"
                cmdSales.Parameters.AddWithValue("@au_id", reader("au_id"))
                Dim qtySales As Object = cmdSales.ExecuteScalar()
                lbl.Text += reader("LastName").ToString() + ": " + String.Format("{0:C}", qtySales) + "<br />"
            End While
        Finally
            con.Close()
            con.Dispose()
            End
        End Try
    End Sub
 

Another example and working demo using a TreeView:

http://stonecoastwebdesign.com/code/ShowMARS.aspx

Comments [0] | | # 
# Friday, November 14, 2008
Friday, November 14, 2008 4:15:26 PM (GMT Standard Time, UTC+00:00) ( SQL )


I ran into an issue where I needed to change a list of columns from text and ntext in SQL Server 2000 to varchar(max) and nvarchar(max) fields in SQL Server 2005.  Not wanting to do this one field at a time, I poked around and found a solution using the built in Information_Schema.

To simply produce a list of table names, column names or data types you can do something like this:

SELECT table_name,column_name, data_type
FROM INFORMATION_SCHEMA.Columns
WHERE column_name like '%FirstName%'
ORDER BY table_name

To produce a list of ALTER TABLE statements that will change a column type from Text to Varchar(MAX), run this in Query Analyzer or Management Studio.

SELECT 'ALTER TABLE ' + TABLE_NAME + ' ALTER COLUMN ' + COLUMN_NAME + ' VARCHAR(MAX);'
FROM INFORMATION_SCHEMA.COLUMNS WHERE DATA_TYPE = 'TEXT'
ORDER BY TABLE_NAME

This will produce a list of ALTER TABLE statements that look like this:

ALTER TABLE AuditorFees ALTER COLUMN AuditorChangeNotes VARCHAR(MAX);
ALTER TABLE CEOCompFiguresNotes ALTER COLUMN NotesCEOContract VARCHAR(MAX);
/* etc.

Copy and paste these invidual ALTER TABLE statements in Query Analyzer or Management Studio and run them. Problem solved!

Comments [0] | | # 
# Wednesday, November 12, 2008
Wednesday, November 12, 2008 8:31:28 PM (GMT Standard Time, UTC+00:00) ( SQL )
Error: Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AI" in the equal to operation
Resolution:
SELECT 
        TAnalystAlerts.ID,
        TAnalystAlerts.Category,        
        TAnalystAlerts.Ticker,
        TAnalystAlerts.Title,
        TAnalystAlerts.DatePosted,
        TAnalystAlerts.[Description],
        TAnalystAlerts.URL,
        Companies.id_company,
        Companies.BAStatus
FROM 
        TAnalystAlerts LEFT JOIN Companies ON TAnalystAlerts.Ticker COLLATE Latin1_General_CI_AI  = Companies.Ticker COLLATE Latin1_General_CI_AI
Comments [0] | | # 
Wednesday, November 12, 2008 6:31:11 PM (GMT Standard Time, UTC+00:00) ( SQL )


SQL 2000:

http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=367&lngWId=5

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=31893

http://www.sqlteam.com/article/search-and-replace-in-a-text-column

SQL 2005:

Update CompanyProfiles
SET 
CompanyProfiles.CompanyProfile = REPLACE(CAST(companyprofile as varchar(max)),'A' ,'?')
WHERE id_company = 12931

.NET Version:

http://equatorlive.com/blogs/dotnetdave/tech/sql-search-and-replace-on-text-type-columns/

Comments [0] | | # 
# Friday, September 19, 2008
Friday, September 19, 2008 9:52:16 PM (GMT Daylight Time, UTC+01:00) ( ASP.NET | SQL )


The built-in functions @@Identity() and Scope_Identity() are designed to retrieve the most recently added record's autoincrement identity value from Access and Sql Server respectively. Here are some usage examples.

Access and @@Identity()

The Jet 4.0 provider supports @@Identity(), which means that developers no longer need to use Select Max(ID) or some other method. The key to @@Identity is that it returns the value of an autoincrement column that is generated on the same connection.

This last bit is important, because it means that the Connection object used for the Insert query must be re-used without closing it and opening it up again. Access doesn't support batch statements, so each must be run separately. It is also therefore possible, though not necessary, to create a new Command object to run the Select @@Identity query. The following code shows this in action where the Connection object is opened, then the first query is executed against cmd, followed by changing the CommandText property of cmd to "Select @@Identity" and running that.

Dim query As String = "Insert Into Categories (CategoryName) Values (?)"
Dim query2 As String = "Select @@Identity"
Dim ID As Integer
Dim connect As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=|DataDirectory|Northwind.mdb"
Using conn As New OleDbConnection(connect)
  Using cmd As New OleDbCommand(query, conn)
    cmd.Parameters.AddWithValue("", Category.Text)
    conn.Open()
    cmd.ExecuteNonQuery()
    cmd.CommandText = query2
    ID = cmd.ExecuteScalar()
  End Using
End Using

SQL Server and Scope_Identity()

While Sql Server also supports @@Identity(), the recommended method for obtaining identity values on this platform is Scope_Identity(),

Dim query As String = "Insert Into Categories (CategoryName) Values (@CategoryName);" & _
    "Select Scope_Identity()"
Dim ID As Integer
Dim connect As String = "Server=.\SQLExpress;AttachDbFilename=|DataDirectory|Northwind.mdf;" & _
    "Database=Northwind;Trusted_Connection=Yes;"
Using conn As New SqlConnection(connect)
  Using cmd As New SqlCommand(query, conn)
    cmd.Parameters.AddWithValue("@CategoryName", Category.Text)
    conn.Open()
    ID = cmd.ExecuteScalar()
  End Using
End Using

Alternatively, you may prefer to use an output parameter from a stored procedure, and ExecuteNonQuery().

Dim query As String = "AddCategory"
Dim ID As Integer
Dim connect As String = "Server=.\SQLExpress;Database=Northwind;Trusted_Connection=Yes;"
Using conn As New SqlConnection(connect)
  Using cmd As New SqlCommand(query, conn)
    cmd.CommandType = CommandType.StoredProcedure
    cmd.Parameters.AddWithValue("@Category", Category.Text)
    cmd.Parameters.Add("@CategoryID", SqlDbType.Int, 0, "CategoryID")
    cmd.Parameters("@CategoryID").Direction = ParameterDirection.Output
    conn.Open()
    cmd.ExecuteNonQuery()
    ID = cmd.Parameters("@CategoryID").Value
  End Using
End Using
CREATE PROCEDURE AddCategory
  -- Add the parameters for the stored procedure here
  @Category nvarchar(15),
  @CategoryID int OUTPUT
AS
BEGIN
  SET NOCOUNT ON;

  -- Insert statements for procedure here
  Insert Into Categories (CategoryName) Values (@Category)
  Set @CategoryID = Scope_Identity()
END

Finally, you can create a stored procedure that contains no output parameter, but ends with 'Select Scope_Identity()'. This version requires ExecuteScalar(), and requires less ADO.NET code and a shorter Stored Procedure.

Dim query As String = "AddCategory"
Dim ID As Integer
Dim connect As String = "Server=.\SQLExpress;Database=Northwind;Trusted_Connection=Yes;"
Using conn As New SqlConnection(connect)
  Using cmd As New SqlCommand(query, conn)
    cmd.CommandType = CommandType.StoredProcedure
    cmd.Parameters.AddWithValue("@Category", Category.Text)
    conn.Open()
    ID = cmd.ExecuteScalar()
  End Using
End Using

The stored procedure:

CREATE PROCEDURE AddCategory
  -- Add the parameters for the stored procedure here
  @Category nvarchar(15)
AS
BEGIN
  SET NOCOUNT ON;

  -- Insert statements for procedure here
  Insert Into Categories (CategoryName) Values (@Category)
  Select Scope_Identity()
END
Comments [0] | | # 
# Thursday, June 26, 2008
Thursday, June 26, 2008 5:05:44 PM (GMT Daylight Time, UTC+01:00) ( SQL )


This site from Microsoft has some great T-SQL examples: http://code.msdn.microsoft.com/SQLExamples

Comments [0] | | # 
# Wednesday, April 30, 2008
Wednesday, April 30, 2008 7:25:42 PM (GMT Daylight Time, UTC+01:00) ( Database | SQL )


SELECT ORDINAL_POSITION , COLUMN_NAME , DATA_TYPE , CHARACTER_MAXIMUM_LENGTH , IS_NULLABLE , COLUMN_DEFAULT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'MyAwesomeTable' ORDER BY ORDINAL_POSITION ASC;
Another method is
sp_help 'tablename' 
 
Comments [0] | | # 
# Monday, April 21, 2008
Monday, April 21, 2008 7:28:00 PM (GMT Daylight Time, UTC+01:00) ( Database | SQL )


The SqlConnection object has a new RetrieveStatistics method in .NET 2.0, which provides some interesting statistics that could come in handy while debugging and performance tuning your .NET applications.  Normally statistics are turned off by default, so you have to set StatisticsEnabled = true in order for the SqlConnection object to begin collecting statistics.  Here is a working demo.

<%@ Page Language="VB" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Web.Configuration" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
    
    Sub Page_Load()
        Dim connectionString As String = WebConfigurationManager.ConnectionStrings("MyDatabase").ConnectionString
        Dim con As New SqlConnection(connectionString)
        Dim cmd As New SqlCommand("WAITFOR DELAY '0:0:03';SELECT Title,Director FROM Movies", con)
        con.StatisticsEnabled = True
        Using con
            con.Open()
            Dim reader As SqlDataReader = cmd.ExecuteReader()
        End Using
        grdStats.DataSource = con.RetrieveStatistics()
        grdStats.DataBind()
    End Sub
</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
    <style type="text/css">
        td,th
        {
            padding:4px 20px;
        }
    </style>
    <title>Show All Statistics</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>

    <h1>Connection Statistics</h1>
    
    <asp:GridView
        id="grdStats"
        AutoGenerateColumns="false"
        Runat="server">
        <Columns>
        <asp:BoundField DataField="Key" HeaderText="Key" />
        <asp:BoundField DataField="Value" HeaderText="Value" />
        </Columns>    
    </asp:GridView>
    
    </div>
    </form>
</body>
</html>
Comments [0] | | # 
# Tuesday, April 08, 2008
Tuesday, April 08, 2008 6:35:23 PM (GMT Daylight Time, UTC+01:00) ( SQL )


Erland Sommarskog, a SQL Server MVP, has a nice entry on how to share data between stored procedures.  The example I'll use the most often is using OUTPUT parameters

This method can only be used when the result set is one single row. Nevertheless, this is a method that is sometimes overlooked. Say you have this simple stored procedure:

CREATE PROCEDURE insert_customer @name    nvarchar(50),
                                 @address nvarchar(50),
                                 @city    nvarchar(50) AS
DECLARE @cust_id int
BEGIN TRANSACTION
SELECT @cust_id = coalesce(MAX(cust_id), 0) + 1 FROM customers (UPDLOCK)
INSERT customers (cust_id, name, address, city)
   VALUES (@cust_id, @name, @address, @city)
COMMIT TRANSACTION
SELECT @cust_id
That is, the procedure inserts a row into a table, and returns the id for the row.

Rewrite this procedure as:

CREATE PROCEDURE insert_customer @name    nvarchar(50),
                                 @address nvarchar(50),
                                 @city    nvarchar(50),
                                 @cust_id int OUTPUT AS
BEGIN TRANSACTION
SELECT @cust_id = coalesce(MAX(cust_id), 0) + 1 FROM customers (UPDLOCK)
INSERT customers (cust_id, name, address, city)
   VALUES (@cust_id, @name, @address, @city)
COMMIT TRANSACTION

You can now easily call insert_customer from another stored procedure. Just recall that in T-SQL you need to specify the OUTPUT keyword also in the call:

EXEC insert_customer @name, @address, @city, @cust_id OUTPUT
Comments [0] | | # 
# Wednesday, November 21, 2007
Wednesday, November 21, 2007 5:45:26 PM (GMT Standard Time, UTC+00:00) ( ASP.NET | Database | SQL )

I Was recently asked to move a standalone application inside of WSS 3.0. No matter what I tried I was getting an error message "Connection String has not been properly iniitialized" even though it worked fine in a separate app. The problem turned out to be that the first version which worked in version 1.1 does not work ASP.NET 2.0. In the first example, the connection string is pulling it's connection info from the <AppSettings> section. Version 2.0 doesn't use <appsettings> and instead uses <ConnectionStrings> so the SqlConnection and SqlCommand objects have to be structured a little differently.

********************

My typical method which worked fine in a separate app but did not work in WSS:

Dim strSQLText As String = "SELECT UserID, Name FROM tblUsers ORDER BY Name"
Dim con As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("QCentralConnectionString"))
Dim cmd As New SqlCommand(strSQLText, con)
con.Open()
ddluserID.DataSource = cmd.ExecuteReader()
ddluserID.DataBind()
con.Close()

web.config:

<appSettings>       
    <add key="QCentralConnectionString" value="server=localhost; database=QCentral; uid=xxx; pwd=xxx" />   
</appSettings>

********************

This version worked in WSS 3.0 (ASP.NET 2.0):

Dim strSQLText As String = "SELECT UserID, Name FROM tblUsers ORDER BY Name"
Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("QCentralConnectionString").ConnectionString)
Dim cmd As New SqlCommand(strSQLText)
con.Open()
cmd.Connection = con
ddluserID.DataSource = cmd.ExecuteReader()
ddluserID.DataBind()
con.Close()

web.config:

<configuration>
<connectionStrings>
<clear />
<add name="QCentralConnectionString" connectionString="Data Source=localhost;Initial Catalog=QCentral;User ID=xxx;Password=xxx" providerName="System.Data.SqlClient" /> 
</connectionStrings>

********************

Comments [0] | | # 
# Tuesday, November 13, 2007
Tuesday, November 13, 2007 5:21:03 PM (GMT Standard Time, UTC+00:00) ( Database | SQL )

Use this to find out where a given field name or column reside and is being used in SQL Server.

select so.name
from sysobjects so inner join syscolumns sc
ON so.id = sc.id where sc.name = 'YourColumnNameGoesHere'

or

select so.name, sc.name
from sysobjects so inner join syscolumns sc
ON so.id = sc.id where sc.name like '%YourPartialColumnNameGoesHere%'

or

Select table_name from information_Schema.columns where column_name='yourCol'

 

Comments [0] | | # 
# Monday, November 05, 2007
Monday, November 05, 2007 2:05:42 AM (GMT Standard Time, UTC+00:00) ( SQL )

I feel as though I've written a thousand search forms where a user is presented with a form and then can pick from a list of fields in a form to perform a search against. In the past I've always just build a dynamic WHERE clause looking for a value in each field of the form and then appending that value(s) to the SQL statement i.e.:

Dim strReturnTrend As String = ddlReturnTrend.SelectedValue
If strReturnTrend <> "" Then
   If blnNotFirst = True Then SqlText += " AND "
   SqlText += " ReturnTrend= '" & strReturnTrend & "'"
   blnNotFirst = True
End If

You can avoid this lengthy process of building a dynamic WHERE clause by using default values and optional parameters in your stored procedures.

create procedure [dbo].[sp_search_directors]
@Company varchar(50) = null,
@DirectorFirstName varchar(50) = '',
@DirectorLastName varchar(50) = '',
@Address varchar(50) = ''
@Title varchar(50) = ''

AS

SELECT
Company,
Director_FirstName,
Director_LastName,
Address,
Title

FROM
TDirectors

WHERE
Company = IsNull(@Company, Company),
and Director_FirstName LIKE
case @DirectorFirstName when '' then Director_FirstName
else @DirectorFirstName +'%' end
and Director_LastName LIKE
case @DirectorLastName when '' then Director_LastName
else @DirectorLastName +'%' end
and Address like
case @Address when '' then Address
else @Address + '%' end
and Title like
case @Title when '' then Title
else '%' + @Title + '%' end

This single query can search for directors by any combination of the parameters.  The conditions in the where clause are such that when the default value is used, the condition will always be true. So to search for directors by a title starting with 'Big Money' the call would look like the following:

sp_search_directors null,'','','','Big Money'

The conditional logic contains a couple of different ways to do this and they are both equivalent. 

 First, if the default value of a parameter is null then the IsNull construct of the first condition is a simple way to implement this. 

In the second example, the case statement is used to test for the default value and simply subsitute the field value in place of the parameter value, if the parameter value is something other than the default,  then the parameter value is used instead.

For the directors name and address, the condition is a "starts with" match.  For the title the condition is a "contains" match.

One more thing to remember, in SQL, a null = null is not a match.  So if the data columns are allowed to have null values, we need to use IsNull to supply a dummy value for the match and in that case the syntax for the condition will be as follows:

and IsNull(Director_FirstName,'') like
case @DirectorFirstName when '' then IsNull(Director_FirstName,'')
else @DirectorFirstName +'%' end

 Not everyone has a first name so we allow that field to be null.  If the value of the director field is null, it will be converted to an empty string so instead of null = null which does not match, we now have ''='' ( 'empty string' = 'empty string'  ) which does match.

Comments [0] | | # 
# Wednesday, September 26, 2007
Wednesday, September 26, 2007 8:51:56 PM (GMT Daylight Time, UTC+01:00) ( Database | SQL )

I found this article on one of my favorite sites on how to insert new records using an embedded SELECT statement.  Here is an example:

INSERT california_authors (au_id, au_lname, au_fname)
SELECT au_id, au_lname, au_fname
FROM authors
WHERE State = 'CA'

http://www.sqlteam.com/article/using-select-to-insert-records

Comments [0] | | # 
# Wednesday, August 08, 2007
Wednesday, August 08, 2007 3:09:12 PM (GMT Daylight Time, UTC+01:00) ( Database | SQL )

Inserting a value into an identity column (primary key) is something I need to do often but usually forget how to do.  It's actually pretty easy so I made myself some notes on this topic.

If you try and run this in QA in SQL Server, you'll receive the error below because ID is the PK for tableA 

INSERT tableA(ID, TheValue)
VALUES (1, 'First Row')
GO

Msg 544, Level 16, State 1, Line 3
Cannot insert explicit value for identity column in table 'tableA' when IDENTITY_INSERT is set to OFF.

The trick is to enable IDENTITY_INSERT for the table like this

SET IDENTITY_INSERT tableA ON

INSERT tableA(ID, TheValue)
VALUES (3, 'First Row')

SET IDENTITY_INSERT tableA OFF

Comments [0] | | # 
# Monday, July 23, 2007
Monday, July 23, 2007 4:09:52 PM (GMT Daylight Time, UTC+01:00) ( SQL )

I picked this up from SQLTeam.com and it's come in handy a few times.

CREATE PROCEDURE CompareTables(

@table1 varchar(100), 
@table2 Varchar(100),
@T1ColumnList varchar(1000),
@T2ColumnList varchar(1000) = '')
AS

-- Table1, Table2 are the tables or views to compare.
-- T1ColumnList is the list of columns to compare, from table1.
-- Just list them comma-separated, like in a GROUP BY clause.
-- If T2ColumnList is not specified, it is assumed to be the same
-- as T1ColumnList. Otherwise, list the columns of Table2 in
-- the same order as the columns in table1 that you wish to compare.
--
-- The result is all records from either table that do NOT match
-- the other table, along with which table the record is from.

declare @SQL varchar(8000);

IF @t2ColumnList = '' SET @T2ColumnList = @T1ColumnList

set @SQL = 'SELECT ''' + @table1 + ''' AS TableName, ' + @t1ColumnList +
' FROM ' + @Table1 + ' UNION ALL SELECT ''' + @table2 + ''' As TableName, ' +
@t2ColumnList + ' FROM ' + @Table2

set @SQL = 'SELECT Max(TableName) as TableName, ' + @t1ColumnList +
' FROM (' + @SQL + ') A GROUP BY ' + @t1ColumnList +
' HAVING COUNT(*) = 1'

exec ( @SQL)

Comments [0] | | # 
# Wednesday, July 11, 2007
Wednesday, July 11, 2007 9:17:55 PM (GMT Daylight Time, UTC+01:00) ( SQL )

UPDATE table1
SET col1 = t2.col1
FROM table1 AS t1
JOIN table2 AS t2
ON t1.col2 = t2.col2

Comments [0] | | # 
# Wednesday, June 27, 2007
Wednesday, June 27, 2007 4:01:39 AM (GMT Daylight Time, UTC+01:00) ( Database | SQL )

Came across this article and code which provides a script for obtaining the size of every database on your SQL Server 2000 box. Very nice...

http://www.sqlservercentral.com/columnists/jtshyman/usingxp_execresultsettoobtaindatabasesizeinformati.asp

Comments [0] | | # 
# Monday, June 18, 2007
Monday, June 18, 2007 5:21:48 PM (GMT Daylight Time, UTC+01:00) ( ASP.NET | SQL )

I recently had to put into place a method that prevented more than one user from logging into our company web site under the same login ID. With the help of a friend, this is what we came up with. 

Basically, each time a user logs in, the user's UserID and sessionID are stored in a database table at login.  Each time the user visits a page in the site, the user's session ID is matched against what is stored in the database.  If the sessionIDs do not match, then the user is logged out and redirected to a page that says something like "duplicate login detected.  you're a cheating bum. etc."

A typical scenario would work like this:

User A logs into the site and the sessionID is recorded into the dbase. She browses from page and page within the site and on each page her sessionID is verified against what's stored in the database.

User B then logs into the site using User A's ID and password. The sessionID information stored in the database for User A is overwritten by User B. When user A tries to browse to another page, she receives a message stating that someone has logged into the site using her ID.  User A can log back in, but then User B will receive the duplicate login message and round and round they go.

Here's the code for it.

This stored procedure is called on the login page. If the user's userID is not already in the datbase, then it is inserted as a new record. Otherwise, the existing record is updated with the new session ID.

CREATE Procedure sp_LoginStatusCheck
@UserID as Int,
@SessionID as nvarchar(100)


AS
    IF (SELECT COUNT(UserID)
FROM TLoginStatus
WHERE UserID = @UserID) = 0
        
BEGIN
INSERT INTO TLoginStatus(UserID, SessionID) Values (@UserID, @SessionID)
    
END
ELSE
         UPDATE TLoginStatus
         SET SessionID = @SessionID
         WHERE UserID = @UserID
GO

Call sp_LoginStatusCheck in the login page.

Sub AddSessionCheck(ByVal UserID As Integer)
'Due to some bizarre requirement that there must be some value assigned to a Session variable not to lose
'the value for SessionID, I created a random session variable and value            
Session("BA") = "BA"
            
            
Dim myConnection As SqlConnection
Dim cmd As SqlCommand
            
myConnection = New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
cmd = New SqlCommand("sp_LoginStatusCheck", myConnection)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@UserID", SqlDbType.Int, 4).Value = UserID
cmd.Parameters.Add("@SessionID", SqlDbType.NVarChar, 100).Value = Session.SessionID
                        
Try
myConnection.Open()
cmd.ExecuteNonQuery()
Finally
myConnection.Close()
End Try

End Sub


On each page that I Want to check for a duplicate login, I would first get the userID value which is stored in a cookie value in the Page_Load function. 

<%@ Page Language="VB" Inherits="MyBaseClass" src="~/classes/MyBaseClass.vb" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SQLClient" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<script runat="server">
Sub Page_Load()
If Not IsPostBack Then
Dim strUserID As String
If Not IsNothing(Request.Cookies("BA")) Then
strUserID = Request.Cookies("BA")("UserID").ToString()
lblUserID.Value = strUserID
End If


'Call CheckLoginStatus
    CheckLogin(strUserID)
    
End If
End Sub
</script>

I created a new class called MyBaseClass with a sub called CheckLogin that accepts UserID as a parameter. This gets called on every page that I check.

Imports Microsoft.VisualBasic
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web.Configuration
Imports System.Web.HttpContext

Public Class MyBaseClass
Inherits System.Web.UI.Page

Public Shared Sub CheckLogin(ByVal UserID As Integer)

'This entire section is the duplicate login prevention section
System.Web.HttpContext.Current.Session("Spice") = "Ginger"
Dim strSessionID = System.Web.HttpContext.Current.Session.SessionID
Dim strLoginStatus As String

Dim MyConnection As SqlConnection
Dim Cmd As SqlCommand

MyConnection = New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
Cmd = New SqlCommand("sp_LoginStatusCheck2", MyConnection)
Cmd.CommandType = CommandType.StoredProcedure
Cmd.Parameters.Add("@UserID", SqlDbType.Int, 4).Value = UserID
Cmd.Parameters.Add("@SessionID", SqlDbType.NVarChar, 100).Value = strSessionID
Cmd.Parameters.Add("@LoginStatus", SqlDbType.Bit).Direction = ParameterDirection.Output

Try
MyConnection.Open()
Cmd.ExecuteNonQuery()
strLoginStatus = Cmd.Parameters(2).Value
'System.Web.HttpContext.Current.Response.Write(strLoginStatus)
Finally
MyConnection.Close()
End Try

If strLoginStatus = "False" Then
System.Web.HttpContext.Current.Response.Redirect("/duplicate.login.aspx")
End If

End Sub

End Class

Here's the code for the stored procedure sp_LoginStatusCheck2

CREATE Procedure sp_LoginStatusCheck2
@UserID as Int,
@SessionID as nvarchar(100),
@LoginStatus as Bit OUTPUT

--We don't want to check the LoginStatus of enterprise level accounts UserID 884, UserID 7996
AS
IF @UserID=884
    SET @LoginStatus = 1
ELSE IF @UserID=7996 
    SET @LoginStatus = 1
ELSE    

    IF (SELECT COUNT(*) FROM TLoginStatus WHERE UserID = @UserID AND SessionID = @SessionID) > 0
        SET @LoginStatus = 1
    ELSE
        SET @LoginStatus = 0
GO

Comments [0] | | # 
# Thursday, June 07, 2007
Thursday, June 07, 2007 4:11:39 PM (GMT Daylight Time, UTC+01:00) ( SQL )

SELECT <KEY_COLUMN>, COUNT(*)
FROM <TABLE>
GROUP BY <KEY_COLUMN>
HAVING COUNT(*) > 1

Comments [0] | | # 
# Wednesday, June 06, 2007
Wednesday, June 06, 2007 7:26:16 PM (GMT Daylight Time, UTC+01:00) ( SQL )

I use this on signup forms to prevent a user from registering a second account with the same email address.

CREATE Procedure sp_AddSubscriber
@Name as nvarchar(50),
@Email as nvarchar(50),
@AddSubscriber varchar(255) OUTPUT

AS
 IF (SELECT COUNT(Email)
                  FROM TSubscribers
                  WHERE Email = @Email) = 0
  
  BEGIN INSERT TSubscribers
  (Name, Email)  Values  (@Name, @Email)
SET @AddSubscriber = "True"                           
END              
ELSE
     SET @AddSubscriber = "The email address you have entered has already been registered in our database."
GO

Comments [0] | | # 
# Tuesday, June 05, 2007
Tuesday, June 05, 2007 6:32:07 PM (GMT Daylight Time, UTC+01:00) ( SQL )

I was recently working on a project where I had to return the larger of two values in my T-SQL code.  COALESCE would not work because often one of the two values was not NULL which COALESCE requires.  The solution ended up being relatively simple by using a CASE-WHEN statement like below.

CASE
  WHEN CEOAnnualBonus > CEOBonus THEN CEOAnnualBonus
  WHEN CEOBonus IS NULL THEN CEOAnnualBonus
  ELSE CEOBonus
END 
AS CEOBonusCombined,
CASE
  WHEN CEOOtherAnnualComp > CEOAllOtherCompensation THEN CEOOtherAnnualComp
  WHEN CEOAllOtherCompensation IS NULL THEN CEOOtherAnnualComp
  ELSE
  CEOAllOtherCompensation
  END 
AS CEOAnnualCompCombined 

Also, I learned about using ISNULL to return a different value when you encounter a NULL value in your data.  This helps clean it up at run time.

ISNULL(dbo.TBenchmarkTemp.ChairIndependent, '-') AS ChairIndependent

 

Comments [0] | | #