All content (copyright) respective authorsvmonpara's code bookmark tagged with sql serverhttp://www.codaddict.com/vmonpara/sql servervmonpara's code bookmark tagged with sql serverAttach/Detach database using osqlexec sp_attach_db @dbname = 'Portal', @filename1 ='C:\Program Files\Microsoft SQL Server\MSSQL$VISHAL\Data\Portal.mdf', @filename2 ='C:\Program Files\Microsoft SQL Server\MSSQL$VISHAL\Data\Portal_log.LDF'
go

exec sp_detach_db 'Portal'
gohttp://www.codaddict.com/vmonpara/SQL ServervmonparaSat, 29 Dec 2007 11:33:32 GMTGet all nodes from SQL Server clusterSELECT * FROM sys.dm_os_cluster_nodeshttp://www.codaddict.com/vmonpara/SQL ServervmonparaWed, 19 Dec 2007 10:17:09 GMTGet active node from SQL Server clusterSELECT SERVERPROPERTY('ComputerNamePhysicalNetBIOS') ActiveNodehttp://www.codaddict.com/vmonpara/SQL ServervmonparaWed, 19 Dec 2007 10:13:48 GMTSQL Server Case Statement syntaxSELECT title, price,
Budget = CASE price
WHEN price > 20.00 THEN 'Expensive'
WHEN price BETWEEN 10.00 AND 19.99 THEN 'Moderate'
WHEN price < 10.00 THEN 'Inexpensive'
ELSE 'Unknown'
END,
FROM titleshttp://www.codaddict.com/vmonpara/SQL ServervmonparaTue, 14 Aug 2007 04:37:50 GMTDTS Error: Object not a collectionInstead of DTSGlobalVarialbes("gVar"), put DTSGlobalVarialbes("gVar").Valuehttp://www.codaddict.com/vmonpara/SQL ServervmonparaMon, 13 Aug 2007 03:34:02 GMTGive permission to SQL Server DB user using command lineosql -E -S "mycomputer\instance" -Q "sp_grantlogin 'mycomputer\ASPNET'"
osql -E -S "mycomputer\instance" -d dbname -Q "sp_grantdbaccess 'mycomputer\ASPNET'"
osql -E -S "mycomputer\instance" -d dbname -Q "sp_addrolemember 'db_owner', 'mycomputer\ASPNET'"http://www.codaddict.com/vmonpara/SQL ServervmonparaMon, 13 Aug 2007 03:29:41 GMTDTS SQL Statement / Stored procedure / Function to check for existence of file.CREATE FUNCTION [dbo].[fn_FileExists] (@FileName nvarchar(255))
RETURNS bit AS
BEGIN
DECLARE @FS int
DECLARE @FileID int
DECLARE @OLEResult int
declare @Exists bit

EXECUTE @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT
IF @OLEResult <> 0
BEGIN
RETURN 0
END

EXECUTE @OLEResult = sp_OAMethod @FS, 'FileExists', @Exists OUT, @FileName

IF @OLEResult <> 0
BEGIN
return 0
END
EXECUTE @OLEResult = sp_OADestroy @FS

return @Exists

ENDhttp://www.codaddict.com/vmonpara/SQL ServervmonparaSun, 12 Aug 2007 08:16:31 GMTStop DTS Package Execution Conditionally Without FailureDim fso, file1
Dim pkg
SET pkg = DTSGlobalVariables.Parent

Set fso = CreateObject("Scripting.FileSystemObject")

IF (fso.FileExists("c:\abc.txt")) THEN
Msgbox "File exist. Continue"
pkg.Steps("DTSStep_DTSActiveScriptTask_2").DisableStep=False

Set fso= Nothing

ELSE
Msgbox "Stop"
pkg.Steps("DTSStep_DTSActiveScriptTask_2").DisableStep=True
END IF
set pkg=nothing
Main = DTSTaskExecResult_Successhttp://www.codaddict.com/vmonpara/SQL ServervmonparaSun, 12 Aug 2007 08:14:11 GMT