This is a handy script to cycle through every character in a column to determine what each ascii value is.  This is especially useful when a string match isn't matching.  Often times, there is a hidden space, etc.

 DECLARE @counter int = 1;
--DECLARE @asciiString varchar(10) = 'AA%#&    ';
 DECLARE @asciiString varchar(100) 
 
 SELECT @asciiString = [ColumnName]
  FROM schema.TableName
  where ColumnName like '%Something%'

WHILE @counter <= DATALENGTH(@asciiString)
   BEGIN
   SELECT CHAR(ASCII(SUBSTRING(@asciiString, @counter, 1))) as [Character],
   ASCII(SUBSTRING(@asciiString, @counter, 1)) as [ASCIIValue]
     
   SET @counter = @counter + 1
   END
GO
  1. Go to your project properties, either by right-clicking on the project and picking "Properties" or by picking Properties from the Project menu.

  2. Click on Debug, then enter your arguments into the "Script Arguments" field.

  3. Save.

-- start with this:
SELECT
dbfile.name AS DatabaseFileName,
dbfile.size/128 AS FileSizeInMB,
sysFG.name AS FileGroupName,
dbfile.physical_name AS DatabaseFilePath
FROM
sys.database_files AS dbfile
INNER JOIN
sys.filegroups AS sysFG
ON
dbfile.data_space_id = sysFG.data_space_id

-- for a more general look by filegroup, try this:

with fileConfig as
(SELECT
dbfile.name AS DatabaseFileName,
(dbfile.size/128)  AS FileSizeInMB,
sysFG.name AS FileGroupName,
dbfile.physical_name AS DatabaseFilePath
FROM
sys.database_files AS dbfile
INNER JOIN
sys.filegroups AS sysFG
ON
dbfile.data_space_id = sysFG.data_space_id
)
select FileGroupName,
       sum(FileSizeInMB) as TotalFilegroupInMB
 from fileConfig
  group by FileGroupName
  order by FileGroupName
There are probably open sessions on the database you are attempting to bring offline. SQL Server is trying to roll back any existing workloads in-flight for that database. 

Issue the sp_who2 command from a new connection (master db) and view what's active. If you see activity, let it complete--or if you don't want the sessions to complete for whatever reason, issue the kill command for the spid(s).  

In the future, use this command:

ALTER DATABASE yourDBName SET OFFLINE WITH ROLLBACK IMMEDIATE;

To bring the db back online:

ALTER DATABASE yourDBName SET ONLINE

There are probably open sessions on the database you are attempting to bring offline. SQL Server is trying to roll back any existing workloads in-flight for that database. 

Issue the sp_who2 command from a new connection (master db) and view what's active. If you see activity, let it complete--or if you don't want the sessions to complete for whatever reason, issue the kill command for the spid(s).  

In the future, use this command:

ALTER DATABASE yourDBName SET OFFLINE WITH ROLLBACK IMMEDIATE;

To bring the db back online:

ALTER DATABASE yourDBName SET ONLINE

To get a count(*) of distinct column combinations, do the count(*) over the distinct select statement.

Example:

SELECT count(*)
  FROM (SELECT DISTINCT
          ColumnA,
  ColumnB,
  ColumnC
              FROM YourTable 
             ) x
Ambari will still show NiFi as down, however, you will be able to work with the canvas from the URL
If you are seeing the "connection refused" message when attempting to set up a localhost access port, the chances are good that the port is blocked from allowing connections through Windows. To open the port, follow these instructions:

1. Navigate to Control Panel, System and Security and Windows Firewall.
2. Select Advanced settings and highlight Inbound Rules in the left pane.
3. Right click Inbound Rules and select New Rule.
4. Add the port you need to open and click Next.
5. Add the protocol (TCP or UDP) and the port number into the next window and click Next.
6. Select Allow the connection in the next window and hit Next.
7. Select the network type as you see fit and click Next.
8. Name the rule something meaningful and click Finish.

You can also use  netsh (example):
netsh advfirewall firewall add rule name="Open Port 80" dir=in action=allow protocol=TCP localport=80


-- converting column with commas to multiple columns --

declare @col1 varchar(500)
set @col1 = 'I,Hate,Broccoli'

DECLARE @Tmp TABLE ( Id int, Element VARCHAR(20)) 
INSERT @Tmp SELECT 1,@col1
 
 
 
SELECT Id, 
       PARSENAME(REPLACE(Element,',','.'),2) Name, 
       PARSENAME(REPLACE(Element,',','.'),1) Surname 
FROM @Tmp


-- converting column with commas to multiple rows --

declare @col1 varchar(500)
set @col1 = 'I,am,really,a,smart person, one of the smartest'

DECLARE @Tmp TABLE ( Id int, Element VARCHAR(200)) 
INSERT @Tmp SELECT 1,@col1

SELECT A.[id],
    Split.a.value('.', 'VARCHAR(100)') AS String
  FROM (SELECT [id],
      CAST ('<M>' + REPLACE(Element, ',', '</M><M>') + '</M>' AS XML) AS String
    FROM @Tmp) AS A CROSS APPLY String.nodes ('/M') AS Split(a);


This statement will display the datetime stamp of the last user scan and the last user update. It will also include the # of user updates on that table.

SELECT OBJECT_NAME(OBJECT_ID) AS DatabaseName, last_user_update,*
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID( 'yourDatabaseName')
AND OBJECT_ID=OBJECT_ID('yourTableName')
This statement will work 

if object_id('tempdb..#mytempTbl') is not null
  drop table #mytempTbl
For an effective, simple HTML formatted static list go into an MSDOS command window and type: wmic qfe list full /format:htable > c:\winpatches.htm
Open up a windows command shell. Type in:
 
shutdown -r -t360 -m servname.onyourdomain.com

If you are looking for more information about this, check out this link







This took me longer than it should have to figure out. In just a few simple steps, all those emails can easily be dealt with in sets.

  1. Go to the folder you are interested in.
  2. click the top check box--or the "select all" .  You'll see a text line appearing just above the trash can icon that says something like "All 999 conversations on this page are selected." (see below).
  3. Click on the underlined text that says "Select all 999 conversations in YourFileFolderName". The text line will change to something like "All 999 conversations in 'YourFileFolderName' are selected."
  4. Click the Delete button.  It will pop up with a 'bulk action' message about the delete you're about to do. Agree to delete. 


If you have a very large data base (VLDB) a select count(*) can take a long time to resolve. Try this statement as an alternative:

USE YourDatabasename;
SELECT
        s.name AS 'SchemaName'
       ,o.name AS 'TableName'
       ,SUM(p.row_count) AS 'RowCount'
FROM sys.dm_db_partition_stats p
       JOIN sys.objects o ON o.object_id = p.object_id
       JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE p.index_id < 2 AND o.type = 'U'
       AND s.name = 'Schema'
       AND o.name = 'TableName'
GROUP BY s.name,o.name
ORDER BY s.name,o.name;
GO

This will locate specific text within an object on a given database. In this case, it will find all views, stored procs which have the text string "Department" somewhere in the body.

use [databaseInstance]
go

SELECT DISTINCT 
   o.name AS Object_Name,
   o.type_desc
FROM sys.sql_modules m        
  INNER JOIN    sys.objects o 
     ON m.object_id = o.object_id 
WHERE m.definition Like '%Department%';

The most likely reason is that your profile has not been configured, or you are using an incorrect name under the @profile_name parameter.  To determine your profile settings, use this query:

SELECT [profile_id]
      ,[name]  
      ,[description]
      ,[last_mod_datetime]
      ,[last_mod_user]
FROM [msdb].[dbo].[sysmail_profile] 

If after you have checked the results of this query and you are using the correct profile name, check how you are passing the parameter values.

Don't do this:
exec msdb.dbo.sp_send_dbmail @subject, @body, @profile_name, @recipients 

...  it will still fail because you need to explicitly define the parameter values ...

For example,
@subject = 'Some topic or another'

So you would either have to do this:

exec msdb.dbo.sp_send_dbmail @subject = 'Some topic or another', @body = 'read this!' ...etc.,

or

declare @subj varchar(100)
set @subj 'Some topic or another'

exec msdb.dbo.sp_send_dbmail @subject=@subj ... etc.,...






This sql script will start an agent job on a remote server.  If you're running this as a step in another agent job, keep in mind that the job you are running it from will be determined to be successful, even if the remote job fails--as this is an asynchronous kick off only.


declare @returnCode int 
declare @JobName varchar(300) 
declare @ServerName varchar(200) 
declare @query varchar(8000) 
declare @cmd varchar(8000) 

set @JobName = 'TheJobNameYouWantToRun' 
set @ServerName = 'TheRemoteServerWhereTheJobIs' 

set @query = 'exec msdb.dbo.sp_start_job @JobName = ''' + @JobName + '''' 
set @cmd = 'osql -E -S ' + @ServerName + ' -Q "' + @query + '"' 

print ' @JobName = ' +isnull(@JobName,'NULL @JobName') 
print ' @ServerName = ' +isnull(@ServerName,'NULL @ServerName') 
print ' @query = ' +isnull(@query,'NULL @query') 
print ' @cmd = ' +isnull(@cmd,'NULL @cmd') 

exec @returnCode = master.dbo.xp_cmdshell @cmd 

if @returnCode <> 0 or @returnCode is null 
begin 
print 'xp_cmdshell @returnCode = '+isnull(convert(varchar(20),@returnCode),'NULL @returnCode') 
end 
You will need to enable the feature first.

Follow these steps:

EXEC sp_configure 'show advanced options', 1
GO
-- this updates whatever the currently configured value for advanced options
RECONFIGURE
GO
-- Now enable the command shell
EXEC sp_configure 'xp_cmdshell', 1
GO
--update the currently configured value with xp_cmdshell setting update.
RECONFIGURE
GO
If you are using the SSMS GUI, you may not be aware that behind the scenes, you are really issuing an ALTER DATABSE command.  It is likely that another process (or processes) were accessing the database you want to take offline.

If you're a DBA, or have sysadmin privileges, issue an sp_who2 command -- looking for the ALTER DATABSE process logged to you.  Kill the process. 

Once the process has been killed off, issue the ALTER DATABSE command yourself.

To take the database offline:

USE master
GO

ALTER DATABASE yourDBname
SET OFFLINE WITH ROLLBACK IMMEDIATE
GO

To get the database back online:

USE master
GO

ALTER DATABASE yourDBname
SET ONLINE
GO

For added protection of data you do not wish to be online:

ALTER DATABASE yourDBname SET RESTRICTED_USER


Think of this as a lightweight alternative to SQL profiler. Under the hood of SQL profiler, there exists SQL Trace  -- which provides a collection of stored procedures to generate trace info. Cut and paste the t-script below to see how it works.


-- Pay attention to what the server settings for traces look like first:

select * from sys.traces

go

-- you are looking to make sure there isn't already a trace file set somewhere
-- In any case you will need to create a new trace, make sure the @tracefile doesn't exist on the disk yet

declare @myTracefile nvarchar(500) set @tracefile=N'c:\temp\myTraceFile.trc'

declare @myTrace_id int

declare @maxsize bigint

set @maxsize =1

exec sp_trace_create @myTrace_id output,2,@MyTracefile ,@maxsize

go

 

---  add the result columns you care about

--  if you don't have any other traces set, you'll be set to 1, if not, run the select * sys.traces again to see
-- what got assigned. look up in sys.traces to find the @mytrace_id,
--  in this example, I will assume its 1 for now so that is why you see: @mytrace_id=1

declare @myTrace_id int

set @myTrace_id=1

declare @xon bit

set @xon=1

declare @current int

set @current =1

while(@current  <10)   -- 10 times is just an arbitrary number of times i might log something
                                               -- pick whatever you want

      begin
      
      -- here is where you figure out what events you want to log to the file.  
      -- Go here to decide:  https://msdn.microsoft.com/en-us/library/ms186265.aspx

      -- Try it out for now, using #14 -- that's probably not what you want, but get it to work first.

      exec sp_trace_setevent @myTrace_id,14, @current,@xon

      set @current=@current+1


-- later you'll want to look at it

declare @myTrace_id int

set @myTrace_id=1

exec sp_trace_setstatus  @myTrace_id,1

-- see the traced event

select yourlogin, dbinstancename,* from ::fn_trace_gettable(N'myTraceFile.trc',default)

go

 
-- Once you're done, you'll need to cleanup
-- stop the trace and delete the file

declare @myTrace_id int

set @myTrace_id=1

exec sp_trace_setstatus @myTrace_id,0

exec sp_trace_setstatus @myTrace_id,2  -- delete def from server


go
These general statements will get you the information that you're looking for:


SELECT
TABLE_CAT,
TABLE_SCHEM,
TABLE_NAME,
COLUMN_NAME,
DATA_TYPE,
TYPE_NAME,
COLUMN_SIZE,
COLUMN_TEXT

FROM "SYSIBM"."SQLCOLUMNS"
WHERE TABLE_SCHEM = 'YourSchema'
 AND TABLE_NAME = 'YourTableName'

This will get you the definitions of the columns:

SELECT
t.table_schema as Library
,t.table_name
,t.table_type
,c.column_name
,c.ordinal_position
,c.data_type
,c.character_maximum_length as Length
,c.numeric_precision as Precision
,c.numeric_scale as Scale
,c.column_default
,t.is_insertable_into
FROM sysibm.tables t
JOIN sysibm.columns c
   on t.table_schema = c.table_schema
  and t.table_name = c.table_name
WHERE t.table_schema = 'YourSchema'
and t.table_name = 'YourTableName'
order by t.table_name, c.ordinal_position
Posted On Wednesday, April 22, 2015 4:15 PM | Comments (0)
Filed Under [ DB2 ]
Go to the server your are interested in gathering the information from, then issue this statement:

WITH LastRestoresOnServer AS
(
SELECT
    sysdb.[name] as dbName,
    sysdb.[create_date],
    sysdb.[compatibility_level],
    sysdb.[collation_name],
    r.*,
   ROW_NUMBER() OVER (PARTITION BY sysDb.Name ORDER BY r.[restore_date] DESC) as RN
FROM master.sys.databases sysdb
LEFT OUTER JOIN msdb.dbo.[restorehistory] r ON r.[destination_database_name] = sysDb.Name
)
SELECT *
FROM [LastRestoresOnServer]
WHERE RN = 1

You were probably trying to do a comparison between 2 databases with different collation settings on a WHERE clause or on a  join. Here's how you would do the comparison with a JOIN:

Select * 
from source db1.schema.table1 as t1
join source db2.schema.table2 as t2
on t1.col1
collate SQL_Latin1_General_CP1_CS_AS = t2.col1 COLLATE SQL_Latin1_General_CP1_CS_AS

This pattern works well:

USE [yourDatabase]
GO

IF OBJECT_ID('ThisSproc', 'P') IS NOT NULL
DROP PROCEDURE ThisSproc;
GO

CREATE PROCEDURE ThisSproc
  @PersonId INT,
  @NewMoneyCollected MONEY = 0
AS
BEGIN
  BEGIN TRY
    BEGIN TRANSACTION;
      UPDATE OrganizationTripFunding
      SET Balance = MoneyCollectedAllYear + @NewMoneyCollected
      WHERE PersonId = @PersonID;
    COMMIT TRANSACTION;
  END TRY
  BEGIN CATCH
    IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION;

    DECLARE @ErrorNumber INT = ERROR_NUMBER();
    DECLARE @ErrorLine INT = ERROR_LINE();
    DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();
    DECLARE @ErrorSeverity INT = ERROR_SEVERITY();
    DECLARE @ErrorState INT = ERROR_STATE();
    
-- use as debugging tool -- PRINT 'error number: ' + CAST(@ErrorNumber AS VARCHAR(10)); -- PRINT 'line number: ' + CAST(@ErrorLine AS VARCHAR(10)); RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState); END CATCH END; GO