who’s a real hottie? :D

who’s a real hottie?

TSQL: excute a SSIS/dts package in a stored procedure bypassing parameters

DECLARE @returncode int
DECLARE @cmd varchar(4000)
DECLARE @ImgURL varchar(250)
DECLARE @ImageToolPath varchar(250)
DECLARE @Msg varchar(500)
SET @ImgURL = ‘http://www.webdatenblatt.de/cds/de/?pid=f822e0c1E_’
SET @ImageToolPath = ‘C:\Daten\imgtest’

 

–execute ssis package with parameters

SELECT @cmd = ‘dtexec /f  ”C:\SSIS\GetImages.dtsx”‘
+ ‘ /SET \Package.Variables[ImgURL].Properties[Value];’ + @ImgURL
+ ‘ /SET \Package.Variables[ImageToolPath].Properties[Value];’ + @ImageToolPath
EXEC @returncode = xp_cmdshell @cmd
IF @ReturnCode <> 0
BEGIN
SELECT @Msg = ‘SSIS package execution failed GetImages.dtsx on SQL Server\Instance: ‘ + QUOTENAME(@@servername,’”‘)
EXEC msdb.dbo.sp_send_dbmail @profile_name=’SQLServerMail’, @recipients = ‘xy@xy.xy’ , @body = @Msg, @subject = ‘SSIS Execution Failure’
END
ELSE
BEGIN
print ‘image download for import items finished’
END

TSQL: Trigger on a table to catch delete command

CREATE TRIGGER [dbo].[nodelete]

    ON [dbo].[LBSysEvents]
INSTEAD OF DELETE
AS
BEGIN
IF 1=1
BEGIN
RAISERROR(‘You cannot delete’,16,1)
RETURN;
END
END

TSQL: importing XML data using OPENROWSET

CREATE TABLE xml_documents( x XML );

 INSERT INTO xml_documents ( x )

   SELECT * FROM OPENROWSET(BULK N’myXmlFile.xml’, SINGLE_BLOB) AS x;

GO

TSQL: DELETE FROM Joined Table

DELETE TableA 
FROM TableA AS a
INNER JOIN TableB AS b
ON a.BId = b.BId
WHERE [filter condition]

TSQL: shrink all databases on server SQL 2008

IF EXISTS(select * from sysobjects where id = object_id('dbo.SP_ShrinkAllDatabasesOnServer') and xtype = 'P')
	DROP PROCEDURE dbo.SP_ShrinkAllDatabasesOnServer
GO

CREATE PROCEDURE dbo.SP_ShrinkAllDatabasesOnServer
AS
BEGIN
	CREATE TABLE #TempDatabasesTable
	(
		[DatabaseName] sysname not null primary key,
		Mod tinyint not null default 1
	)
	INSERT INTO #TempDatabasesTable ([DatabaseName]) 
	SELECT 
		name 
	FROM 
		master..sysdatabases 
	WHERE
		dbid > 4 
	DECLARE @DatabaseName sysname

	SET @DatabaseName = ''

	WHILE @DatabaseName is not null
	BEGIN
		SET @DatabaseName = NULL

		SELECT TOP 1 @DatabaseName = [DatabaseName] from #TempDatabasesTable where Mod = 1

		IF @DatabaseName is NULL
			break

		print '*******************************************************************'
		print '> DB: ' + @DatabaseName
		print '> SET RECOVERY MODE SIMPLE'
		declare @SqlCommand nvarchar(4000)
		set @SqlCommand = 'ALTER DATABASE [' + @DatabaseName + '] SET recovery simple'
		exec sp_executesql @SqlCommand
		print '> Shrinking database'
		set @SqlCommand = 'dbcc shrinkdatabase([' + @DatabaseName + '])'
		exec sp_executesql @SqlCommand
		update #TempDatabasesTable set Mod = 0 where [DatabaseName] = @DatabaseName
	end
	DROP TABLE #TempDatabasesTable
END
GO
exec dbo.SP_ShrinkAllDatabasesOnServer

TSQL: create number with leading zero

right(’0000000000000′ + convert(varchar(13), EANNumber), 13)

TSQL: how to search for certain columnames in all tables

SELECT table_name=sysobjects.name, column_name=syscolumns.name, datatype=systypes.name, length=syscolumns.length
FROM sysobjects
JOIN syscolumns ON sysobjects.id = syscolumns.id
JOIN systypes ON syscolumns.xtype=systypes.xtype
WHERE sysobjects.xtype=’U’ AND syscolumns.name like ‘%columname%’
ORDER BY sysobjects.name,syscolumns.colid

TSQL: how to search content in all columns

DECLARE @SearchStr nvarchar(100)

SET @SearchStr = ‘USER_’

CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)

SET @TableName = ”

SET @SearchStr2 = QUOTENAME(‘%’ + @SearchStr + ‘%’,””)

 

WHILE @TableName IS NOT NULL

BEGIN

SET @ColumnName = ”

SET @TableName =

(

SELECT MIN(QUOTENAME(TABLE_SCHEMA) + ‘.’ + QUOTENAME(TABLE_NAME))

FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_TYPE = ‘BASE TABLE’

AND QUOTENAME(TABLE_SCHEMA) + ‘.’ + QUOTENAME(TABLE_NAME) > @TableName

AND OBJECTPROPERTY(

OBJECT_ID(

QUOTENAME(TABLE_SCHEMA) + ‘.’ + QUOTENAME(TABLE_NAME)

), ‘IsMSShipped’

) = 0

)

 

WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)

BEGIN

SET @ColumnName =

(

SELECT MIN(QUOTENAME(COLUMN_NAME))

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)

AND TABLE_NAME = PARSENAME(@TableName, 1)

AND DATA_TYPE IN (‘char’, ‘varchar’, ‘nchar’, ‘nvarchar’)

AND QUOTENAME(COLUMN_NAME) > @ColumnName

)

 

IF @ColumnName IS NOT NULL

BEGIN

INSERT INTO #Results

EXEC

(

‘SELECT ”’ + @TableName + ‘.’ + @ColumnName + ”’, LEFT(‘ + @ColumnName + ‘, 3630)

FROM ‘ + @TableName + ‘ (NOLOCK) ‘ +

‘ WHERE ‘ + @ColumnName + ‘ LIKE ‘ + @SearchStr2

)

END

END

END

SELECT ColumnName, ColumnValue FROM #Results

TSQL: example how to find duplicate records

WITH R
AS
(SELECT ROW_NUMBER() OVER(PARTITION BY EANNummer ORDER BY V.Artikelnummer, AuspraegungID, ltrim(EANNummer)) as ‘rownumber’, V.Artikelnummer, AuspraegungID, EANNummer

FROM ArtikelVarianten V

INNER JOIN Artikel A on V.Mandant=A.Mandant AND V.Artikelnummer= A.Artikelnummer
where A.Aktiv = -1)

SELECT  * FROM R WHERE RowNumber >1 AND NOT EANNummer IS NULL