Archive for the ‘SQL’ Category

how to test if a temp locking table is locked or not

April 30, 2015

sorry, minimal explanation. you’ll figure it out.
What’s the purpose? to prevent multiple updates from multiple servers in our webfarm.

BEGIN TRY

SET XACT_ABORT ON;
SET LOCK_TIMEOUT 100;

if OBJECT_ID('tempdb..##MyTempLockingTable') is not null
BEGIN
select 1 as [IsLocked]
end
else
begin
select -1 as [IsLocked]
end

end try
begin catch
-- when we test this with a second id, we find that the above object_id statement hangs
select 1 as [IsLocked]
end catch

SQL DataReader problems

October 16, 2012

I may have a problem with a certain database server.

But I only see it when I use a DataReader.

The exception I get (sporadically – about 5% of the time, I think) is:

ERROR: System.NullReferenceException:
Object reference not set to an instance of an object.     at
System.Data.SqlClient.SqlDataReader.ReadColumnData()     at System.Data.SqlClient.SqlDataReader.ReadColumnHeader(Int32
i)     at System.Data.SqlClient.SqlDataReader.ReadColumn(Int32
i, Boolean setTimeout)     at
System.Data.SqlClient.SqlDataReader.GetValueInternal(Int32 i)     at
System.Data.SqlClient.SqlDataReader.GetValue(Int32 i)     at
System.Data.SqlClient.SqlDataReader.get_Item(String name)

Note – there are no null columns in my data, and this code has worked for years.

Sometimes it happens after reading 80 records, sometimes 30, sometimes 50. We’re not talking about 10K records here.

Here is the code where the exception occurs:

isdky = dreader.Item(“sdKy”)

I read a nice blog, and converted my code to use ordinal positions:  ‘http://navaneethkn.wordpress.com/2009/07/11/ado-net-best-practices-reading-data-from-data-reader/

fldProductKey = dreader.GetOrdinal(“sdKy”)

then

ProductKey = dreader.GetInt32(fldProductKey)

But I got the same error.

When I converted my code to get a datatable, then loop through the rows, my troubles went away. I haven’t run that in production yet, but it looks very hopeful.

This points out that I don’t quite understand the process as a reader loops and reads. It seems like it loses its schema along the way and loses track of columns.

When I get a datatable, it pulls in everything, then I close the connection. It just works more stably. We’ve seen issues on this database server in the past – but those were with writes, not read like this time.

grant the same exec rights on all stored procedures when you move databases to a new server

August 1, 2012

I wish I’d had this script this morning:


select 'grant exec on ' + procname + ' to ' + username as CopyAndExecute , username , procname
from
(
select u.name as username, o.id, o.name as procname
from sysobjects o
cross join sysusers u
join syspermissions p on p.id = o.id and u.uid = p.grantee
where o.xtype='P'
and u.updatedate > dateadd( m , -6 ,getdate() )
) A
order by username , procname

It produces a column of sql that you can use to grant exec rights. I did something like this this AM, but didn’t test the generated script.

count all updated tables in a database, oracle version

January 30, 2012

OUTPUT – this produces a script that you can execute that produces text to the output window with each table and a count of recent updates and additions.


set serveroutput on format WRAPPED;

--CREATE GLOBAL TEMPORARY TABLE my_temp_table ( TableName varchar(50), CountRecentUpdates INTEGER) ON COMMIT DELETE ROWS;

declare
t_name varchar(150);
c_name varchar(150);
query_base varchar(250);
squery varchar(500);
TempTableInsert varchar(80) := ' insert into my_temp_table ( TableName , CountRecentUpdates ) ' ;

cursor ci is
select table_name , column_name from all_tab_columns
where rownum SYSDATE-2 ;';

query_base := 'select ''QQQTABLE'' , count(*) as CountRecentUpdates into st, k from QQQTABLE where QQQCOLUMN > SYSDATE-1 ; DBMS_OUTPUT.put_line (st || '' '' || TO_CHAR(k) );';

open ci;

LOOP
fetch ci into t_name , c_name;

exit when ci%NOTFOUND;

-- http://psoug.org/reference/translate_replace.html
squery := replace(query_base , 'QQQTABLE' , t_name );
squery := replace(squery , 'QQQCOLUMN' , c_name );
-- query to write to temp table
-- squery := TempTableInsert || squery ;

-- print the query
DBMS_OUTPUT.put_line ( squery );
-- execute the query
-- EXEC SQL EXECUTE IMMEDIATE :squery ;

end loop;
close ci;

DBMS_OUTPUT.put_line ( ' END; ' );

END;

count database updates for all tables

October 17, 2011

To count all database updates for all tables (that containe a datetime field), just execute this query:

— purpose – see if this database is being updated
— use a set of queries on datetime fields.
— Make the queries dynamic, and pass in the table and column names.
— collate results into a temp table and then display all together
declare @t_name varchar(50)
declare @c_name varchar(50)
declare @query_base varchar(250)
set @query_base = ‘select {QQQTABLE} as [TableName], count(*) as CountRecentUpdates from QQQTABLE where QQQCOLUMN > dateadd(d,-1,getdate() )’
declare @query varchar(500)
declare @TempTableInsert varchar(80)
set @TempTableInsert = ‘ insert into #MyTempTable ([TableName] , [CountRecentUpdates] ) ‘

CREATE TABLE #MyTempTable ([TableName] varchar(50), [CountRecentUpdates] varchar(50) )

declare ci cursor for
SELECT TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE (DATA_TYPE = ‘datetime’) AND (NOT (COLUMN_DEFAULT IS NULL))

open ci

fetch next from ci into
@t_name,
@c_name

WHILE @@FETCH_STATUS = 0

BEGIN

–print @t_name + ‘ ‘ + @c_name

set @query = replace(@query_base,’QQQTABLE’, @t_name)
set @query = replace(@query ,’QQQCOLUMN’, @c_name)
set @query = replace(@query ,'{‘ , char(39) )
set @query = replace(@query ,’}’ , char(39) )
set @query = @TempTableInsert + @query
–print @query
EXECUTE(@query)
— *************************************************************
— RE-LOAD THE CURSOR
fetch next from ci into
@t_name,
@c_name

end
close ci
deallocate ci

select * from #MyTempTable
drop table #MyTempTable

udf function to return words for money

February 14, 2011

We started using this function because crystal reports has a nice formatting function to do the number to words conversion, but I think that Microsoft SQL Reporting doesn’t have such a function.

modified from the original to allow billions and also negatives

examples:
SELECT [dbo].[fnMoneyToEnglish] ( -999999999999.12 )
returns
( Nine Hundred Ninety-Nine Billion Nine Hundred Ninety-Nine Million Nine Hundred Ninety-Nine Thousand Nine Hundred Ninety-Nine Dollars and 12 Cents )

SELECT [dbo].[fnMoneyToEnglish] (-10.11)
returns
( Ten Dollars and 11 Cents )


USE [BursarReceipt]
GO

/****** Object: UserDefinedFunction [dbo].[fnMoneyToEnglish] Script Date: 02/14/2011 11:29:23 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- http://www.sqlusa.com/bestpractices2005/moneyformat/
-- SQL convert dollar amount into words for check printing - Dollars and cents format
-- Convert numbers to words - SQL amount into words - Currency to words
-- SQL money format to English - Translate money to text
-- Translate dollar amount to words - Convert numbers into English words
ALTER FUNCTION [dbo].[fnMoneyToEnglish](@Money AS Money)

RETURNS VARCHAR(1024)

AS

BEGIN
DECLARE @Number as BIGINT
DECLARE @MinusFlag as Bit

if @Money < 0
begin
set @Money = -1 * @Money
Set @MinusFlag = 1
end

SET @Number = FLOOR(@Money)

DECLARE @Below20 TABLE (ID int identity(0,1), Word varchar(32))

DECLARE @Below100 TABLE (ID int identity(2,1), Word varchar(32))

INSERT into @Below20 (Word) VALUES ( 'Zero')
INSERT into @Below20 (Word) VALUES ('One')
INSERT into @Below20 (Word) VALUES ('Two')
INSERT into @Below20 (Word) VALUES ('Three')
INSERT into @Below20 (Word) VALUES ('Four')
INSERT into @Below20 (Word) VALUES ('Five')
INSERT into @Below20 (Word) VALUES ('Six')
INSERT into @Below20 (Word) VALUES ('Seven')
INSERT into @Below20 (Word) VALUES ('Eight')
INSERT into @Below20 (Word) VALUES ('Nine')
INSERT into @Below20 (Word) VALUES ('Ten')
INSERT into @Below20 (Word) VALUES ('Eleven')
INSERT into @Below20 (Word) VALUES ('Twelve')
INSERT into @Below20 (Word) VALUES ('Thirteen')
INSERT into @Below20 (Word) VALUES ('Fourteen')
INSERT into @Below20 (Word) VALUES ('Fifteen')
INSERT into @Below20 (Word) VALUES ('Sixteen')
INSERT into @Below20 (Word) VALUES ('Seventeen')
INSERT into @Below20 (Word) VALUES ('Eighteen')
INSERT into @Below20 (Word) VALUES ('Nineteen')

INSERT into @Below100 (Word) VALUES ('Twenty')
INSERT into @Below100 (Word) VALUES ('Thirty')
INSERT into @Below100 (Word) VALUES ('Forty')
INSERT into @Below100 (Word) VALUES ('Fifty')
INSERT into @Below100 (Word) VALUES ('Sixty')
INSERT into @Below100 (Word) VALUES ('Seventy')
INSERT into @Below100 (Word) VALUES ('Eighty')
INSERT into @Below100 (Word) VALUES ('Ninety')

DECLARE @English varchar(1024)
(
SELECT @English =
Case

WHEN @Number = 0 THEN ''

WHEN @Number BETWEEN 1 AND 19

THEN (SELECT Word FROM @Below20 WHERE ID=@Number)

WHEN @Number BETWEEN 20 AND 99

-- SQL Server recursive function

THEN (SELECT Word FROM @Below100 WHERE ID=@Number/10)+ '-' +

dbo.fnMoneyToEnglish( @Number % 10)

WHEN @Number BETWEEN 100 AND 999

THEN (dbo.fnMoneyToEnglish( @Number / 100))+' Hundred '+

dbo.fnMoneyToEnglish( @Number % 100)

WHEN @Number BETWEEN 1000 AND 999999

THEN (dbo.fnMoneyToEnglish( @Number / 1000))+' Thousand '+

dbo.fnMoneyToEnglish( @Number % 1000)

WHEN @Number BETWEEN 1000000 AND 999999999

THEN (dbo.fnMoneyToEnglish( @Number / 1000000))+' Million '+

dbo.fnMoneyToEnglish( @Number % 1000000)

WHEN @Number BETWEEN 1000000000 AND 999999999999

THEN (dbo.fnMoneyToEnglish( @Number / 1000000000))+' Billion '+

dbo.fnMoneyToEnglish( @Number % 1000000000)

ELSE ' INVALID INPUT' END

)

SELECT @English = RTRIM(@English)

SELECT @English = RTRIM(LEFT(@English,len(@English)-1))

WHERE RIGHT(@English,1)='-'

IF @@NestLevel = 1

BEGIN

SELECT @English = @English+' Dollars and '
SELECT @English = @English+
convert(varchar,convert(int,100*(@Money - @Number))) +' Cents'
if (@MinusFlag = 1)
begin
set @English = '( ' + @English + ' )'
end

END

RETURN (@English)

END

GO

list all logins of a server

July 14, 2009

I’ve found some tremendous resources for administrative sql scripts:
in general:
http://vyaskn.tripod.com/generate_scripts_repetitive_sql_tasks.htm
for the one I’m posting about: http://demiliani.com/blog/archive/2005/05/13/2449.aspx

But , you may ask, why should you get excited about some complicated sql script that generates other sql script that you must copy past and execute? Well, I have a server where someone created the SA userid, and it’s probably been there forever, and I’d like to get rid of it, but how do I know which databases it’s being used for?

put all the logins into a table:
1)
use master
select
'insert into loginstemp select ''' + name + ''' as DBName , name as Login from ' + name + '.sys.sysusers where islogin=1 '
as 'CopyAndExecute'
from master.dbo.sysdatabases

2) create temp table
use MyWriteTable

drop table loginstemp

create table loginstemp (DBName varchar(50), [Login] varchar(50) )

3) copy and execute the generated sql

use MyWriteTable

insert into loginstemp select 'ATable' as DBName , name as Login from ATable.sys.sysusers where islogin=1
insert into loginstemp select 'MyWriteTable' as DBName , name as Login from MyWriteTable.sys.sysusers where islogin=1
etc.

4) view temp table
use MyWriteTable
select * from loginstemp

size of all tables in a database

July 13, 2009

purpose – what if some tables are growing a lot more than you expected? wouldn’t it be handy to have a snapshot of how large all your tables are?


-- replace DB2Data for your own database
use db2data

if exists (select [id] from tempdb..sysobjects where [id] = OBJECT_ID ('tempdb..#TempCountRecords '))
DROP TABLE #TempCountRecords

CREATE TABLE #TempCountRecords (
[Table_Name] varchar(50),
[CountRecords] int
)

declare @SQLcommand varchar(200)

declare @TableName varchar(50)
declare @TableSchema varchar(50)
DECLARE c_db CURSOR FOR SELECT table_name, table_schema from information_schema.tables where table_type='base table'

OPEN c_db
FETCH NEXT FROM c_db INTO @TableName, @TableSchema
WHILE @@FETCH_STATUS = 0
begin

set @TableName = '['+@tableschema + '].[' + @tableName + ']'

set @SQLCommand = ' select ''QQQ'' as [Table_Name] , count(*) as [CountRecords] from QQQ '
set @SQLcommand = replace( @SQLcommand , 'QQQ' , @TableName )
print @SQLcommand

-- insert and execute all in one statement - refer to ms_Script.sql in the Status_DBQueries
insert #TempCountRecords
EXECUTE (@SQLcommand)

FETCH NEXT FROM c_db INTO @TableName, @tableschema

end
DEALLOCATE c_db

select * from #TempCountRecords order by CountRecords desc
drop table #TempCountRecords

search all tables for a string

June 5, 2009

I needed to search the ‘description’ field of every table in my database for a string.
To do that, I generated a column of search commands.
Then I copied those search commands into a new query window in sql server enterprise manager and executed them. I could see which tables contained the string I was looking for.

Here is the sql to generate the column of commands. A trick is to concatenate the literal single quote string.

**********************************************
SEARCH all tables , ALL COLUMNS NAMED ‘DESCRIPTION’ FOR RECORDS CONTAINING A KEY WORD
*******************

select (‘Select ‘ + char(39) + table_name + char(39) + ‘ as TableName ,* from ‘ + table_name + ‘ where [description] like ‘ + char(39) + ‘%Back Flow%’ + char(39) + ‘ ‘ ) as copyandexecute , column_name from INFORMATION_SCHEMA.columns
where column_name = ‘Description’
order by column_name

search all stored procedures for a string

June 5, 2009

Search all stored procedures that contain some text

select specific_name , Routine_Definition from INFORMATION_SCHEMA.ROUTINES
where Routine_Definition like ‘%tblRatingSectionItemAttribute%’
— runs into a limit on string length

THIS WORKED
SELECT OBJECT_NAME(id) , [text]
FROM syscomments
WHERE [text] LIKE ‘%Back Flow%’
AND OBJECTPROPERTY(id, ‘IsProcedure’) = 1
GROUP BY OBJECT_NAME(id) , [text]