SSIS and Sql Server Journey

Personal Notes of Sarabjit Singh and Bhavpreet Singh

Sql Server: Alter Table in Design

Many a time while making changes to the definition of the Sql Server Table we get the following message:
“Saving not permitted. The changes you have made require the following tables to be dropped and re-created or enable option Prevent saving changes that require the table to be re-created. ”

Image

To disable the option “Prevent saving changes that require the table to be re-created”
Go to Tools > Options > Designers > “Un-check” “Prevent saving changes that require the table to be re-created”

Sql Server Hack-Save Changes

Advertisements
Leave a comment »

Sql Server: Using Sqlcmd to export data to text file

A SqlServerCentral friend was looking for a option to export data from Sql Server to a text file on periodic option. Refer the link below for details:

http://www.sqlservercentral.com/Forums/Topic1452185-1550-1.aspx
Adviced him to run the following sqlcmd in the job..
SQLCMD -S YourSqlServer -d YourDatabase -U YourUserName -P YourPassword -Q “Your Query” -s “,” -o “C:\YourFileName.txt”
Feel free to post better work around.
I understand SSIS could be another option.

Leave a comment »

Avoid Dynamic Query

Avoid Dynamic queries, Use the alternate mentioned below:
Here the business logic is that the parameter @Param
Can be ‘ALL’ or a given value. In case of All we don’t need to filter on ColumnName where as otherwise we have to filter out based on parameter.

DECLARE @Query VARCHAR(MAX), @Param VARCHAR(10)
SET @Param = 'ALL'
SELECT @Query = 'SELECT * FROM tblName '
IF  @Param <>'ALL'
BEGIN
SELECT @Query = @Query + 'WHERE ColumnName = ' + @Param
END
EXEC @Param

Above dynamic query can be replaced by the following code.

DECLARE @Query VARCHAR(MAX), @Param VARCHAR(10)
SET @Param = 'ALL'
SELECT * FROM tblName
WHERE (@Param = CASE WHEN @Param = 'ALL' THEN @Param
ELSE ColumnName END) -- All\Specific
Leave a comment »

Back To Basic: Nested Transaction

http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-2630-nested-transactions-are-real/

Point to remember:

ROLLBACK will do two things that’s important to be aware of…
1) It will rollback all work back to the *first* or *outermost* BEGIN TRAN
2) It will also *reset* @@TRANCOUNT to zero (no matter the previous value)

Recall getting the error below when you forget to use @@Transcount>0 before rollback 

“Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0.”

Leave a comment »

Sql Server 2008: Careful with the Merge Hazards!

Do check the best approach to use Merge Statement in the end of the article http://www.sqlservercentral.com/articles/MERGE/97867/

Leave a comment »

Find Table Or Stored Procedure in all Databases on a Server

Inspired by the Pinal Dave’s Post

Following is the SP to find the Stored Procedure or Table in all databases on a given server.

CREATE PROCEDURE usp_FindTableOrStoredProcNameInAllDatabase
@Name VARCHAR(256)
AS
 DECLARE @DBName VARCHAR(256)
 DECLARE @varSQL VARCHAR(512)
 DECLARE @getDBName CURSOR
 SET @getDBName = CURSOR FOR
 SELECT QUOTENAME([name])
 FROM sys.databases
 CREATE TABLE #TmpTable (
 DBName VARCHAR(256),
 SchemaName VARCHAR(256),
 StoredProcTableName VARCHAR(256),
 SpOrTable VARCHAR(10)
 )
 OPEN @getDBName
 FETCH NEXT
 FROM @getDBName INTO @DBName
 WHILE @@FETCH_STATUS = 0
 BEGIN
 SET @varSQL = 'USE ' + @DBName + ';
 INSERT INTO #TmpTable
 SELECT '''+ @DBName + ''' AS DBName,
 SCHEMA_NAME(schema_id) AS SchemaName,
 name AS StoredProcTableName,
 ''Table'' AS SpOrTable
 FROM sys.tables
 WHERE name LIKE ''%' + @Name + '%'''
 EXEC (@varSQL)

 SET @varSQL = 'USE ' + @DBName + ';
 INSERT INTO #TmpTable
 SELECT '''+ @DBName + ''' AS DBName,
 SCHEMA_NAME(schema_id) AS SchemaName,
 name AS StoredProcTableName,
 ''SP'' AS SpOrTable
 FROM sys.procedures
 WHERE name LIKE ''%' + @Name + '%'''
 EXEC (@varSQL)

 FETCH NEXT
 FROM @getDBName INTO @DBName
END
CLOSE @getDBName
DEALLOCATE @getDBName
SELECT *
FROM #TmpTable Order By SpOrTable
DROP TABLE #TmpTable
GO
EXEC usp_FindTableOrStoredProcNameInAllDatabase 'Address'
GO

following is how the out put would look like:

Image

1 Comment »

Cross Apply vs Outer Apply

CROSS APPLY is equivalent to an INNER JOIN expression and OUTER APPLY is equivalent to a LEFT OUTER JOIN expression.

http://sqlwithmanoj.wordpress.com/2010/12/11/cross-apply-outer-apply/

Leave a comment »

Sql Server: Get Random Time in given range : Function

--Requirement to get a random time of day between @startTime and @endTime
Create Function dbo.GetRandomTimeInRange
(
@startTime Time = '00:00:00',
@endTime TIME = '01:30:00'
)
-- Select dbo.GetRandomTimeInRange ('23:00:00', '02:01:00')
RETURNS Time
BEGIN

	-- Get the number of seconds between the specified time
	-- (eg. there are 5400 seconds between 12 AM and 1:30 AM)
	DECLARE @MaxSec int = DATEDIFF(ss, @startTime, @endTime)
	-- Get a random number of seconds between 0 and the number of 
	-- seconds between @startTime and @endTime (@maxSeconds)	
	 DECLARE @randomSec int = (@MaxSec + 1) * 
	  (DATEPART(Ms, Getdate())/1000.0)
	/* Add the random number of seconds to @startTime and 
	return that random time of day  */
	/* In the above code Milli Seconds is divided by 1000 to make the 
	value < 1 or in other words extreact a few percent of @randomSec 
	so as to get the value less than @endTime 
	 */
	RETURN (convert(Time, DateAdd(second, @randomSec, @startTime))) 

END
GO

Leave a comment »

Get Random Time in a given range: Sql Server

DECLARE @startTime Time = '01:00:00'
DECLARE @endTime TIME = '02:30:00'
-- Get the number of seconds between these two times
-- (eg. there are 5400 seconds between 1 AM and 2.30 AM)
DECLARE @maxSeconds int = DATEDIFF(ss, @startTime, @endTime)
-- Get a random number of seconds between 0 and the number of 
-- seconds between @startTime and @endTime (@maxSeconds)
DECLARE @randomSeconds int = (@maxSeconds + 1) 
* RAND(convert(varbinary, newId() )) 
-- Add the random number of seconds to @startTime and return that random time of day
SELECT (convert(Time, DateAdd(second, @randomSeconds, @startTime))) 
AS RandomTime

--Note: Above code cannot be used inside the function as it would give following errors
--Invalid use of a side-effecting operator 'newid' within a function.
--Invalid use of a side-effecting operator 'rand' within a function.

Leave a comment »

Encryption\Decription in Sql Server 2005

  • ENCRYPTION by passphrase
  • ENCRYPTION by symmetric keys
  • ENCRYPTION by Asymmetric keys
  • ENCRYPTION by certificates

http://www.databasejournal.com/features/mssql/article.php/3714031/SQL-Server-2005-Encryption-types.htm

http://dotnetslackers.com/articles/sql/IntroductionToSQLServerEncryptionAndSymmetricKeyEncryptionTutorial.aspx

Leave a comment »