— Description: fills up a string with @len @padChar+@var from left
— Example: SELECT dbo.[fnPadLeft](‘1’, ‘0’, 3) returns ‘001’
— =============================================
CREATE Function[dbo].[fnLBCustomPadLeft]
(@varvarchar(max),
@padCharchar(1)=‘ ‘,
@lenint)
returnsvarchar(max)
as
begin
return replicate(@PadChar,@len–Len(@var))+@var
end
CREATE FUNCTION[dbo].[fnCharCount](@stringvarchar(8000),@characterchar(1))
RETURNS SMALLINT
AS
BEGIN
DECLARE@stringtruncvarchar(8000);
SET@stringtrunc=REPLACE(@string,@character,”);— remove the specified character
RETURN (LEN(@string)–LEN(@stringtrunc));— return the difference in length, this is the char count
END
who’s a real hottie? 😀
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
CREATE TABLE xml_documents( x XML );
INSERT INTO xml_documents ( x )
SELECT * FROM OPENROWSET(BULK N’myXmlFile.xml’, SINGLE_BLOB) AS x;
GO
DELETE TableA
FROM TableA AS a
INNER JOIN TableB AS b
ON a.BId = b.BId
WHERE [filter condition]
IF EXISTS(select * from sysobjects where id = object_id(‘dbo.spShrinkAllDatabasesOnServer’) and xtype = ‘P’)
DROP PROCEDURE dbo.SP_ShrinkAllDatabasesOnServer
GO
CREATE PROCEDURE [dbo].[spShrinkAllDatabasesOnServer]
AS
BEGIN
CREATE TABLE #TempDatabasesTable
(
[DatabaseName] sysname not null primary key,
Mod tinyint not null default 1
)
INSERT INTO #TempDatabasesTable ([DatabaseName])
SELECT name FROM sys.databases WHERE database_id not in (1,2,3,4,5,6,7,8,9,94) order by database_id
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
–wenn keine offenen Connections dann verkleinern
IF isnull((SELECT count(dbid) FROM sys.sysprocesses WHERE db_name(dbid)= @DatabaseName) , 0) = 0
BEGIN
PRINT ‘ ‘
PRINT ‘ ‘
PRINT ‘*******************************************************************’
PRINT ‘> DB: ‘ + @DatabaseName
DECLARE @SqlCommand nvarchar(4000)
EXEC(
‘ USE ‘ + @DatabaseName +
‘ EXEC(”
DECLARE @TableName varchar(255)
DECLARE TableCursor CURSOR LOCAL FOR
SELECT table_name FROM information_schema.tables WHERE table_type = ””base table””
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
Print ””Reindexing Table: ”” + @TableName
DBCC DBREINDEX(@TableName,”” ”” , 90)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
”)’)
PRINT ‘> SET RECOVERY MODE SIMPLE’
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
END
DROP TABLE #TempDatabasesTable
END
right(‘0000000000000’ + convert(varchar(13), EANNumber), 13)
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
Herbee 