SQL Server: List objects in a schema

4 de junho de 2014 Deixe um comentário

– List all tables in a schema
SELECT TABLE_SCHEMA, TABLE_NAME
FROM information_schema.tables
where TABLE_SCHEMA LIKE ‘MY_SCHEMA%’

– List all stored procedures in a schema
SELECT s.name SchemaName, pr.name ProcedureName
FROM sys.procedures pr
INNER JOIN sys.schemas s ON pr.schema_id = s.schema_id
WHERE s.name LIKE ‘MY_SCHEMA%’

– List all views in a schema
SELECT s.name SchemaName, v.name AS view_name
FROM sys.views v
INNER JOIN sys.schemas s ON v.schema_id = s.schema_id
WHERE s.name LIKE ‘MY_SCHEMA%’

CategoriasUncategorized

SQL Server: Verify shrink progress

27 de março de 2014 Deixe um comentário

If you are executing a shrink and wants to know the progress, use this select:

SELECT 
	percent_complete, 
	start_time, 
	status, 
	command, 
	estimated_completion_time, 
	cpu_time, 
	total_elapsed_time
FROM 
	sys.dm_exec_requests
WHERE
	command = 'DbccFilesCompact'

 

CategoriasUncategorized

SQL Server: Script to disable/enable SQL jobs

18 de março de 2014 Deixe um comentário

Disable All SQL Server Agent Jobs
USE MSDB;
GO
UPDATE MSDB.dbo.sysjobs
SET Enabled = 0
WHERE Enabled = 1;
GO
 

Enable All SQL Server Agent Jobs
USE MSDB;
GO
UPDATE MSDB.dbo.sysjobs
SET Enabled = 1
WHERE Enabled = 0;
GO
 

Disable Jobs By Job Name
USE MSDB;
GO
UPDATE MSDB.dbo.sysjobs
SET Enabled = 0
WHERE [Name] LIKE ‘Admin%’;
GO
 

Disable Jobs By Job Category
USE MSDB;
GO
UPDATE J
SET J.Enabled = 0
FROM MSDB.dbo.sysjobs J
INNER JOIN MSDB.dbo.syscategories C
ON J.category_id = C.category_id
WHERE C.[Name] = ‘Database Maintenance’;
GO

CategoriasUncategorized

PowerShell: Execute especific function in a script file

12 de janeiro de 2014 Deixe um comentário

If you just want to execute the function from your current PowerShell session then do this:

. .\script.ps1
My-Func
CategoriasUncategorized

SQL Server: Datafiles and sizes

10 de dezembro de 2013 Deixe um comentário

Selecting datafiles and their sizes:

SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,
Physical_Name, (size*8)/1024 SizeMB
FROM sys.master_files
WHERE DB_NAME(database_id) = 'AdventureWorks'

Power Shell: Verify PowerShell Version

10 de dezembro de 2013 Deixe um comentário
PS C:\> get-host
Name             : ConsoleHost
Version          : 2.0
InstanceId       : cf882a4c-e8e5-487d-9f3c-3c78b3b82f7f
UI               : System.Management.Automation.Internal.Host.InternalHostUserInterface
CurrentCulture   : en-US
CurrentUICulture : en-US
PrivateData      : Microsoft.PowerShell.ConsoleHost+ConsoleColorProxy
IsRunspacePushed : False
Runspace         : System.Management.Automation.Runspaces.LocalRunspace PS C:\> $PSVersionTable Name                           Value
----                           -----
CLRVersion                     2.0.50727.5472
BuildVersion                   6.1.7601.17514
PSVersion                      2.0
WSManStackVersion              2.0
PSCompatibleVersions           {1.0, 2.0}
SerializationVersion           1.1.0.1
PSRemotingProtocolVersion      2.1
PS C:\>

SQL Server SSIS: Package migration from version 3 to version 2 failed with error 0xC001700A

9 de dezembro de 2013 Deixe um comentário

I got a error during SSIS package execution:

Executed as user: NGM\sqlprod. …ersion 9.00.3042.00 for 64-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    Started:  3:13:51 PM  Error: 2013-12-09 15:13:51.85     Code: 0xC001700A     Source:       Description: The version number in the package is not valid. The version number cannot be greater than current version number.  End Error  Error: 2013-12-09 15:13:51.85     Code: 0xC0016020     Source:       Description: Package migration from version 3 to version 2 failed with error 0xC001700A “The version number in the package is not valid. The version number cannot be greater than current version number.”.  End Error  Error: 2013-12-09 15:13:51.85     Code: 0xC0010018     Source:       Description: Error loading value “<DTS:Property xmlns:DTS=”www.microsoft.com/SqlServer/Dts” DTS:Name=”PackageFormatVersion”>3</DTS:Property>” from node “DTS:Property”.  End Error  Could not load package “N:\DBA\WC_Exp_Mod_NC_CDX\WC_Exp_Mod_NC_CDX_1.dtsx”…  The package could not be lo…  The step failed.

The failed agent jobs were those, which had steps to execute SSIS packages and they were failing with the error message “Description: The version number in the package is not valid. The version number cannot be greater than current version number.”

On investigating the root cause, what we found out was the below:

1. The SSIS subsystem during the SQL Agent job run was trying to execute DTexec.exe from the folder “c:\Program Files\Microsoft SQL Server\90\DTS\BINN”
2. What happens on boxes running SQL version 2005 and Integration service 2008 is that, whenever a SQL 2005 patch is applied, the value of default key at the location “HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTS\SetUp\DTSPath” in the registry gets reset to “c:\Program Files\Microsoft SQL Server\90\DTS\”

Fix:

1. Manually modify the value to reflect the correct value “c:\Program Files\Microsoft SQL Server\100\DTS\”.
2. If your SQL instance is on a cluster, you need to update the registry on all the cluster nodes.
3. Restart SQL integration service and SQL Agent Service. No need to restart the node or SQL Instance service.
4. SQL instance picks up the newly modified value and the same can be verified by querying the table msdb..syssubsystems. Look for the SSIS subsystem record and the value in the field “agent_exec”

Note: This means, unless SQL instance is also upgraded to version 2008, whenever I apply a CU patch to the SQL 2005 instance, I also need to remember to manually fix the registry key on all the nodes.

 

Seguir

Obtenha todo post novo entregue na sua caixa de entrada.