Invoke a Web Service Method From A SQL Stored Procedure

No this wasn’t my idea. The company I am currently am contracting for has quite a bit of its infrastructure wrapped up in web services. I needed to invoke a method of one of these services in a stored procedure. So this is the story of how to do that….

First we need to modify the web.config file for your web service. This code should go under .

<webServices>
       <protocols>
         <add name="HttpGet"/>
         <add name="HttpPost"/>
       </protocols>
     </webServices>

Next we need to make sure your SQL Server will allow you to make this kind of call. You will need elevated permissions to run this script. Before you allow your SQL Server to access functions outside of SQL Server consider this. But since I had no choice in this situation in your SQL Server Management Studio query window paste the following:

exec sp_configure 'show advanced options', 1
go
reconfigure
go
exec sp_configure 'Ole Automation Procedures', 1 -- Enable
-- exec sp_configure 'Ole Automation Procedures', 0 -- Disable
go
reconfigure
go
exec sp_configure 'show advanced options', 0
go
reconfigure
go

Next we will want to write the stored procedure itself. Note in your web service address it is important to end the address with a question mark. (?).

USE [DATABASENAMEHERE]
GO

/****** Object:  StoredProcedure [dbo].[Web_Service_Invoke]     ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:        <Kelly Martens>
-- Create date: <4/23/2018>
-- Description:    <Stored Procedure to invoke web service method>
-- =============================================
CREATE PROCEDURE [dbo].[Web_Service_Invoke]
     -- Add the parameters for the stored procedure here
    
AS
DECLARE @obj Integer
DECLARE @sUrl varchar(200)
DECLARE @response varchar(8000)
SET @sURL = 'http://webserviceaddress.asmx/MethodName?'
EXEC sp_OACreate 'MSXML2.ServerXMLhttp',@obj OUT
EXEC sp_OAMethod @obj,'Open',NULL,'GET',@sURL,false
EXEC sp_OAMethod @obj,'send'
EXEC sp_OAGetProperty @obj,'responsetext',@response OUT
SELECT @response [response]
EXEC sp_OADestroy @obj
RETURN

If in the event you have parameters you need to pass to the procedure do this:

Param1=’ + @value + '’

When  you call this stored procedure, the output of the method called, the HTML of the page, will be returned to you, looking something like this:

<?xml version="1.0" encoding="utf-8"?>  <string xmlns="http://tempuri.org/">COMPLETE</string>

Should your permissions not have been configured properly or there is an error in the web service itself, it will also let you know.

Finally let me emphasize again, this is NOT the optimal way to do this. You should be using a WCF service, using SQL CLR and not monkeying about with your permissions. But this is available if you need it.

Advertisements

,

Leave a comment

SQL Object Definition Keyword Search Stored Procedure

There are many times when you encounter a SQL database that has had a lot of fingers in it but not a lot of guidance for you to follow, that you need to find a given concept or word to find what objects you need to be looking at in a given situation. The stored procedure below will allow you to do that. It searches both parent and child objects definition for words that are like or equal to your search term and returns them in order of rank, and then in order of object creation using their object id.


USE [YOURDB]
GO

/****** Object: StoredProcedure [dbo].[Object_Dependency_Keyword_SP] Script Date: 1/3/2018 3:26:16 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================

-- =============================================
CREATE PROCEDURE [dbo].[Object_Dependency_Keyword_SP]
@DefSearchTerm AS varchar(50)
AS
BEGIN
SET NOCOUNT ON;
IF 1=0 BEGIN
SET FMTONLY OFF
END

-- step 1 create temp ta
--DROP TABLE #tempdep
CREATE TABLE #tempdep (objid int NOT NULL, objtype smallint NOT NULL)

-- step 2 load temp table
INSERT INTO #tempdep
SELECT
tbl.object_id AS [ID],
3
FROM
sys.tables AS tbl
--WHERE
--(tbl.name=N'Employee' and SCHEMA_NAME(tbl.schema_id)=N'HumanResources')

-- step 3 find dependencies
declare @find_referencing_objects int
set @find_referencing_objects = 1
-- parameters:
-- 1. create table #tempdep (objid int NOT NULL, objtype smallint NOT NULL)
-- contains source objects
-- 2. @find_referencing_objects defines ordering
-- 1 order for drop
-- 0 order for script

declare @must_set_nocount_off bit
set @must_set_nocount_off = 0

IF @@OPTIONS & 512 = 0
set @must_set_nocount_off = 1
set nocount on

declare @u int
declare @udf int
declare @v int
declare @sp int
declare @def int
declare @rule int
declare @tr int
declare @uda int
declare @uddt int
declare @xml int
declare @udt int
declare @assm int
declare @part_sch int
declare @part_func int
declare @synonym int

set @u = 3
set @udf = 0
set @v = 2
set @sp = 4
set @def = 6
set @rule = 7
set @tr = 8
set @uda = 11
set @synonym = 12
--above 100 -> not in sys.objects
set @uddt = 101
set @xml = 102
set @udt = 103
set @assm = 1000
set @part_sch = 201
set @part_func = 202

/*
* Create #t1 as temp object holding areas. Columns are:
* object_id - temp object id
* object_type - temp object type
* relative_id - parent or child object id
* relative_type - parent or child object type
* rank - NULL means dependencies not yet evaluated, else nonNULL.
* soft_link - this row should not be used to compute ordering among objects
* object_name - name of the temp object
* object_schema - name the temp object's schema (if any)
* relative_name - name of the relative object
* relative_schema - name of the relative object's schema (if any)
* degree - the number of relatives that the object has, will be used for computing the rank
* object_key - surrogate key that combines object_id and object_type
* relative_key - surrogate key that combines relative_id and relative_type
*/
-- DROP TABLE #t1
create table #t1(
object_id int NULL,
object_type smallint NULL,
relative_id int NULL,
relative_type smallint NULL,
rank smallint NULL,
soft_link bit NULL,
object_name sysname NULL,
object_schema sysname NULL,
relative_name sysname NULL,
relative_schema sysname NULL,
degree int NULL,
object_key bigint NULL,
relative_key bigint NULL
)

create unique clustered index i1 on #t1(object_id, object_type, relative_id, relative_type) with IGNORE_DUP_KEY

declare @iter_no int
set @iter_no = 1

declare @rows int
set @rows = 1

declare @rowcount_ck int
set @rowcount_ck = 0

insert #t1 (relative_id, relative_type, rank)
select l.objid, l.objtype, @iter_no from #tempdep l

while @rows > 0
begin
set @rows = 0
if( 1 = @find_referencing_objects )
begin
--tables that reference uddts or udts (parameters that reference types are in sql_dependencies )
insert #t1 (object_id, object_type, relative_id, relative_type, rank)
select t.relative_id, t.relative_type, c.object_id, @u, @iter_no + 1
from #t1 as t
join sys.columns as c on c.user_type_id = t.relative_id
join sys.tables as tbl on tbl.object_id = c.object_id -- eliminate views
where @iter_no = t.rank and (t.relative_type=@uddt OR t.relative_type=@udt)
set @rows = @rows + @@rowcount

--tables that reference defaults ( only default objects )
insert #t1 (object_id, object_type, relative_id, relative_type, rank)
select t.relative_id, t.relative_type, clmns.object_id, @u, @iter_no + 1
from #t1 as t
join sys.columns as clmns on clmns.default_object_id = t.relative_id
join sys.objects as o on o.object_id = t.relative_id and 0 = isnull(o.parent_object_id, 0)
where @iter_no = t.rank and t.relative_type = @def
set @rows = @rows + @@rowcount

--types that reference defaults ( only default objects )
insert #t1 (object_id, object_type, relative_id, relative_type, rank)
select t.relative_id, t.relative_type, tp.user_type_id, @uddt, @iter_no + 1
from #t1 as t
join sys.types as tp on tp.default_object_id = t.relative_id
join sys.objects as o on o.object_id = t.relative_id and 0 = isnull(o.parent_object_id, 0)
where @iter_no = t.rank and t.relative_type = @def
set @rows = @rows + @@rowcount

--tables that reference rules
insert #t1 (object_id, object_type, relative_id, relative_type, rank)
select t.relative_id, t.relative_type, clmns.object_id, @u, @iter_no + 1
from #t1 as t
join sys.columns as clmns on clmns.rule_object_id = t.relative_id
where @iter_no = t.rank and t.relative_type = @rule
set @rows = @rows + @@rowcount

--types that reference rules
insert #t1 (object_id, object_type, relative_id, relative_type, rank)
select t.relative_id, t.relative_type, tp.user_type_id, @uddt, @iter_no + 1
from #t1 as t
join sys.types as tp on tp.rule_object_id = t.relative_id
where @iter_no = t.rank and t.relative_type = @rule
set @rows = @rows + @@rowcount

--tables that reference XmlSchemaCollections
insert #t1 (object_id, object_type, relative_id, relative_type, rank)
select t.relative_id, t.relative_type, c.object_id, @u, @iter_no + 1
from #t1 as t
join sys.columns as c on c.xml_collection_id = t.relative_id
join sys.tables as tbl on tbl.object_id = c.object_id -- eliminate views
where @iter_no = t.rank and t.relative_type = @xml
set @rows = @rows + @@rowcount

--procedures that reference XmlSchemaCollections
insert #t1 (object_id, object_type, relative_id, relative_type, rank)
select t.relative_id, t.relative_type, c.object_id, case when o.type in ( 'P', 'RF', 'PC' ) then @sp else @udf end, @iter_no + 1
from #t1 as t
join sys.parameters as c on c.xml_collection_id = t.relative_id
join sys.objects as o on o.object_id = c.object_id
where @iter_no = t.rank and t.relative_type = @xml
set @rows = @rows + @@rowcount

--udf, sp, uda, trigger all that reference assembly
insert #t1 (object_id, object_type, relative_id, relative_type, rank)
select t.relative_id, t.relative_type, am.object_id, (case o.type when 'AF' then @uda when 'PC' then @sp when 'FS' then @udf when 'FT' then @udf
when 'TA' then @tr else @udf end), @iter_no + 1
from #t1 as t
join sys.assembly_modules as am on am.assembly_id = t.relative_id
join sys.objects as o on am.object_id = o.object_id
where @iter_no = t.rank and t.relative_type = @assm
set @rows = @rows + @@rowcount

-- CLR udf, sp, uda that reference udt
insert #t1 (object_id, object_type, relative_id, relative_type, rank)
select distinct t.relative_id,
t.relative_type,
am.object_id,
(case o.type
when 'AF' then @uda
when 'PC' then @sp
when 'FS' then @udf
when 'FT' then @udf
when 'TA' then @tr
else @udf end),
@iter_no + 1
from #t1 as t
join sys.parameters as sp on sp.user_type_id = t.relative_id
join sys.assembly_modules as am on sp.object_id = am.object_id
join sys.objects as o on sp.object_id = o.object_id
where @iter_no = t.rank and t.relative_type = @udt
set @rows = @rows + @@rowcount

--udt that reference assembly
insert #t1 (object_id, object_type, relative_id, relative_type, rank)
select t.relative_id, t.relative_type, at.user_type_id, @udt, @iter_no + 1
from #t1 as t
join sys.assembly_types as at on at.assembly_id = t.relative_id
where @iter_no = t.rank and t.relative_type = @assm
set @rows = @rows + @@rowcount

--assembly that reference assembly
insert #t1 (object_id, object_type, relative_id, relative_type, rank)
select t.relative_id, t.relative_type, ar.assembly_id, @assm, @iter_no + 1
from #t1 as t
join sys.assembly_references as ar on ar.referenced_assembly_id = t.relative_id
where @iter_no = t.rank and t.relative_type = @assm
set @rows = @rows + @@rowcount

--table references table
insert #t1 (object_id, object_type, relative_id, relative_type, rank)
select t.relative_id, t.relative_type, fk.parent_object_id, @u, @iter_no + 1
from #t1 as t
join sys.foreign_keys as fk on fk.referenced_object_id = t.relative_id
where @iter_no = t.rank and t.relative_type = @u
set @rows = @rows + @@rowcount

--table,view references partition scheme
insert #t1 (object_id, object_type, relative_id, relative_type, rank)
select t.relative_id, t.relative_type, idx.object_id, case o.type when 'V' then @v else @u end, @iter_no + 1
from #t1 as t
join sys.indexes as idx on idx.data_space_id = t.relative_id
join sys.objects as o on o.object_id = idx.object_id
where @iter_no = t.rank and t.relative_type = @part_sch
set @rows = @rows + @@rowcount

--partition scheme references partition function
insert #t1 (object_id, object_type, relative_id, relative_type, rank)
select t.relative_id, t.relative_type, ps.data_space_id, @part_sch, @iter_no + 1
from #t1 as t
join sys.partition_schemes as ps on ps.function_id = t.relative_id
where @iter_no = t.rank and t.relative_type = @part_func
set @rows = @rows + @@rowcount

--view, procedure references table, view, procedure
--procedure references type
--table(check) references procedure
--trigger references table, procedure
insert #t1 (object_id, object_type, relative_id, relative_type, rank)
select t.relative_id, t.relative_type, case when 'C' = obj.type then obj.parent_object_id else dp.object_id end,
case when obj.type in ('U', 'C') then @u when 'V' = obj.type then @v when 'TR' = obj.type then @tr
when obj.type in ( 'P', 'RF', 'PC' ) then @sp
when obj.type in ( 'TF', 'FN', 'IF', 'FS', 'FT' ) then @udf
end, @iter_no + 1
from #t1 as t
join sys.sql_dependencies as dp on
-- reference table, view procedure
( class < 2 and dp.referenced_major_id = t.relative_id and t.relative_type in ( @u, @v, @sp, @udf) )
--reference type
or ( 2 = class and dp.referenced_major_id = t.relative_id and t.relative_type in (@uddt, @udt))
--reference xml namespace ( not supported by server right now )
--or ( 3 = class and dp.referenced_major_id = t.relative_id and @xml = t.relative_type )
join sys.objects as obj on obj.object_id = dp.object_id and obj.type in ( 'U', 'V', 'P', 'RF', 'PC', 'TR', 'TF', 'FN', 'IF', 'FS', 'FT', 'C')
where @iter_no = t.rank
set @rows = @rows + @@rowcount

end -- 1 = @find_referencing_objects
else
begin -- find referenced objects
--check references table
insert #t1 (object_id, object_type, relative_id, relative_type, rank)
select t.relative_id, t.relative_type, dp.object_id, 77 /*place holder for check*/, @iter_no
from #t1 as t
join sys.sql_dependencies as dp on
-- reference table
class < 2 and dp.referenced_major_id = t.relative_id and t.relative_type = @u
join sys.objects as obj on obj.object_id = dp.object_id and obj.type = 'C'
where @iter_no = t.rank
set @rowcount_ck = @@rowcount

--view, procedure referenced by table, view, procedure
--type referenced by procedure
--check referenced by table
insert #t1 (object_id, object_type, relative_id, relative_type, rank)
select distinct
case when 77 = t.relative_type then obj2.parent_object_id else t.relative_id end, -- object_id
case when 77 = t.relative_type then @u else relative_type end, -- object_type
dp.referenced_major_id, -- relative_id
case -- relative_type
when dp.class < 2 then
case when 'U' = obj.type then @u
when 'V' = obj.type then @v
when 'TR' = obj.type then @tr
when obj.type in ( 'P', 'RF', 'PC' ) then @sp
when obj.type in ( 'TF', 'FN', 'IF', 'FS', 'FT' ) then @udf
when exists (select * from sys.synonyms syn where syn.object_id = dp.referenced_major_id ) then @synonym
end
when dp.class = 2 then (case
when exists (select * from sys.assembly_types sat where sat.user_type_id = dp.referenced_major_id) then @udt
else @uddt
end)
end,
@iter_no + 1
from #t1 as t
join sys.sql_dependencies as dp on
-- reference table, view procedure
( class 0
begin
set @iter_no = @iter_no + 1
end

--defaults referenced by types
insert #t1 (object_id, object_type, relative_id, relative_type, rank)
select t.relative_id, t.relative_type, tp.default_object_id, @def, @iter_no + 1
from #t1 as t
join sys.types as tp on tp.user_type_id = t.relative_id and tp.default_object_id > 0
join sys.objects as o on o.object_id = tp.default_object_id and 0 = isnull(o.parent_object_id, 0)
where t.relative_type = @uddt

--defaults referenced by tables( only default objects )
insert #t1 (object_id, object_type, relative_id, relative_type, rank)
select t.relative_id, t.relative_type, clmns.default_object_id, @def, @iter_no + 1
from #t1 as t
join sys.columns as clmns on clmns.object_id = t.relative_id
join sys.objects as o on o.object_id = clmns.default_object_id and 0 = isnull(o.parent_object_id, 0)
where t.relative_type = @u

--rules referenced by types
insert #t1 (object_id, object_type, relative_id, relative_type, rank)
select t.relative_id, t.relative_type, tp.rule_object_id, @rule, @iter_no + 1
from #t1 as t
join sys.types as tp on tp.user_type_id = t.relative_id and tp.rule_object_id > 0
where t.relative_type = @uddt

--rules referenced by tables
insert #t1 (object_id, object_type, relative_id, relative_type, rank)
select t.relative_id, t.relative_type, clmns.rule_object_id, @rule, @iter_no + 1
from #t1 as t
join sys.columns as clmns on clmns.object_id = t.relative_id and clmns.rule_object_id > 0
where t.relative_type = @u

--XmlSchemaCollections referenced by table
insert #t1 (object_id, object_type, relative_id, relative_type, rank)
select t.relative_id, t.relative_type, c.xml_collection_id, @xml, @iter_no + 1
from #t1 as t
join sys.columns as c on c.object_id = t.relative_id and c.xml_collection_id > 0
where t.relative_type = @u

--XmlSchemaCollections referenced by procedures
insert #t1 (object_id, object_type, relative_id, relative_type, rank)
select t.relative_id, t.relative_type, c.xml_collection_id, @xml, @iter_no + 1
from #t1 as t
join sys.parameters as c on c.object_id = t.relative_id and c.xml_collection_id > 0
where t.relative_type in ( @sp, @udf)

--partition scheme referenced by table,view
insert #t1 (object_id, object_type, relative_id, relative_type, rank)
select t.relative_id, t.relative_type, ps.data_space_id, @part_sch, @iter_no + 1
from #t1 as t
join sys.indexes as idx on idx.object_id = t.relative_id
join sys.partition_schemes as ps on ps.data_space_id = idx.data_space_id
where t.relative_type in (@u, @v)

--partition function referenced by partition scheme
insert #t1 (object_id, object_type, relative_id, relative_type, rank)
select t.relative_id, t.relative_type, ps.function_id, @part_func, @iter_no + 1
from #t1 as t
join sys.partition_schemes as ps on ps.data_space_id = t.relative_id
where t.relative_type = @part_sch

end

--cleanup circular references
delete #t1 where object_id = relative_id and object_type=relative_type

--allow circular dependencies by cuting one of the branches
--mark as soft links dependencies between tables or table depending on udf
-- at script time we will need to take care to script fks and checks separately
update #t1 set soft_link = 1 where ( object_type = @u and relative_type = @u ) or
( 0 = @find_referencing_objects and object_type = @u and relative_type = @udf ) or
( 1 = @find_referencing_objects and relative_type = @u and object_type = @udf )

--add independent objects first in the list
insert #t1 ( object_id, object_type, rank)
select t.relative_id, t.relative_type, 1 from #t1 t where t.relative_id not in ( select t2.object_id from #t1 t2 where not t2.object_id is null )

--delete initial objects
delete #t1 where object_id is null

-- compute the surrogate keys to make sorting easier
update #t1 set object_key = object_id + convert(bigint, 0xfFFFFFFF) * object_type
update #t1 set relative_key = relative_id + convert(bigint, 0xfFFFFFFF) * relative_type

create index index_key on #t1 (object_key, relative_key)

update #t1 set rank = 0
-- computing the degree of the nodes
update #t1 set degree = (
select count(*)
from #t1 t_alias
where t_alias.object_key = #t1.object_key and
t_alias.relative_id is not null and
t_alias.soft_link is null)

-- perform topological sorting
set @iter_no=1
while 1=1
begin
update #t1 set rank=@iter_no where degree=0
-- end the loop if no more rows left to process
if (@@rowcount=0) break
update #t1 set degree=NULL where rank = @iter_no

update #t1 set degree = (
select count(*)
from #t1 t_alias
where t_alias.object_key = #t1.object_key and
t_alias.relative_key is not null and
t_alias.relative_key in (select t_alias2.object_key from #t1 t_alias2 where t_alias2.rank=0 and t_alias2.soft_link is null) and
t_alias.rank=0 and t_alias.soft_link is null)
where degree is not null

set @iter_no=@iter_no+1
end

--add name schema
update #t1 set object_name = o.name, object_schema = schema_name(o.schema_id)
from sys.objects AS o
where o.object_id = #t1.object_id and object_type in ( @u, @udf, @v, @sp, @def, @rule, @uda)

update #t1 set relative_type = case op.type when 'V' then @v else @u end, object_name = o.name, object_schema = schema_name(o.schema_id), relative_name =
op.name, relative_schema = schema_name(op.schema_id)
from sys.objects AS o
LEFT OUTER join sys.objects AS op on op.object_id = o.parent_object_id
--join sys.objects AS op on op.object_id = o.object_id
where o.object_id = #t1.object_id and object_type = @tr

update #t1 set object_name = t.name, object_schema = schema_name(t.schema_id)
from sys.types AS t
where t.user_type_id = #t1.object_id and object_type in ( @uddt, @udt )

update #t1 set object_name = x.name, object_schema = schema_name(x.schema_id)
from sys.xml_schema_collections AS x
where x.xml_collection_id = #t1.object_id and object_type = @xml

update #t1 set object_name = p.name, object_schema = null
from sys.partition_schemes AS p
where p.data_space_id = #t1.object_id and object_type = @part_sch

update #t1 set object_name = p.name, object_schema = null
from sys.partition_functions AS p
where p.function_id = #t1.object_id and object_type = @part_func

update #t1 set object_name = a.name, object_schema = null
from sys.assemblies AS a
where a.assembly_id = #t1.object_id and object_type = @assm

update #t1 set object_name = syn.name, object_schema = schema_name(syn.schema_id)
from sys.synonyms AS syn
where syn.object_id = #t1.object_id and object_type = @synonym

-- delete objects for which we could not resolve the table name or schema
-- because we may not have enough privileges
delete from #t1
where
object_name is null or
(object_schema is null and object_type not in (@assm, @part_func, @part_sch))

--final select
select a.object_id,
object_name,
--object_type,
ISNULL(c.type_desc,'') AS Object_Type,
--LTRIM(RTRIM(ISNULL(sm.definition,''))) AS PARENT_DEF,
ISNULL(relative_id,'') AS relative_id,
relative_name = ISNULL(b.name,''),
Rank,
--ISNULL(relative_type,'') AS Relative_Type,

--object_schema,

ISNULL(b.type_desc,'') AS Type_Desc--,
--LTRIM(RTRIM(ISNULL(smc.definition,''))) AS CHILD_DEF
from #t1 a
LEFT JOIN sys.objects b ON b.object_id = a.relative_id
LEFT JOIN sys.Objects c ON c.object_id = a.object_id
LEFT JOIN sys.sql_modules sm ON sm.object_id = c.object_id
LEFT JOIN sys.sql_modules smc ON smc.object_id = b.object_id
WHERE (sm.definition LIKE '%' + @DefSearchTerm + '%' OR smc.definition LIKE '%' + @DefSearchTerm + '%')
-- WHERE object_name = 'Organization_all'
order by Rank,relative_id

drop table #t1
drop table #tempdep

IF @must_set_nocount_off > 0
set nocount off

END;
GO


 

Leave a comment

SQL Object (Table, Stored Procedures, Functions, Views) Dependencies

I often get asked to help figure out a confusing and archaic SQL database structure as various developers over the years have added layer after layer of stored procedures, tables, views and functions and nobody in the present day actually knows what a lot of it is or does anymore and folks are paralyzed because they are afraid to make any kinds of changes. However, not all is lost! Using the sample below you can see how your stored procedures, tables, views and functions are connected to each other and what you will need to address and what you need to thoroughly test if you make any kind of changes. The format below is for SQL, but I am working on an update for MySQL, DB2 and Oracle as well so check back later for that.

USAGE:

select distinct [Table Name] = o.Name, [Found In] = sp.Name, sp.type
from sys.objects o inner join sys.sql_expression_dependencies sd on o.object_id = sd.referenced_id
inner join sys.objects sp on sd.referencing_id = sp.object_id
and sp.type in ('P','TR', 'FN', 'V', 'TF','IF')

-- where o.name = 'Your Table Name'
--where sp.Name = 'your stored procedure, function or trigger or other type'

--order set to sort by table, object type and object name
order by o.Name, sp.type,sp.name
-- change order to drill down from procedure, trigger or function name or type
--order by sp.name,o.Name, sp.type_desc

--Type Options

-- C CHECK_CONSTRAINT
--D DEFAULT_CONSTRAINT
--F FOREIGN_KEY_CONSTRAINT
--FN SQL_SCALAR_FUNCTION
--IF SQL_INLINE_TABLE_VALUED_FUNCTION
--IT INTERNAL_TABLE
--P SQL_STORED_PROCEDURE
--PK PRIMARY_KEY_CONSTRAINT
--R RULE
--S SYSTEM_TABLE
--SQ SERVICE_QUEUE
--TF SQL_TABLE_VALUED_FUNCTION
--TR SQL_TRIGGER
--U USER_TABLE
--UQ UNIQUE_CONSTRAINT
--V VIEW

Leave a comment

Dynamic Predicate (WHERE clause) for a LINQ Query

I love LINQ! It really has been a godsend and anyone still using datatable.select seriously needs to learn it. One thing I didn’t like too much though was I was having to write each LINQ query out instead of being able to pass it off to a function as a parameter. I haven’t gotten all the way there yet but this was a start. The idea was when we have a source of data that we refer to often that we pass the predicate (WHERE statement) off to a function instead of having to rewrite the whole query each time. While this example uses a datatable, you can use it with any collection that can have LINQ written against it.

So how is it done? First we identify the single element type we need to use ( Of TRow As DataRow) and then identify the “source” we are using and tie the identifier to that source ((source As TypedTableBase(Of TRow)). Then we must specify the predicate, or the WHERE clause that is going to be passed (predicate As Func(Of TRow, Boolean)) which will either be returned as true or false. Then we identify how we want the returned information ordered (OrderByField As String). Our function will then return a EnumerableRowCollection(Of TRow), our collection of datarows that have met the conditions of our predicate(EnumerableRowCollection(Of TRow)). This is a basic example. Of course you must make sure your order field doesn’t contain nulls, or have handled that situation properly and make sure your column names (if you are using a strongly typed datasource never mind this, it will rename the columns for you) are standard.

What is left // TO DO is to accomplish this with a table join, which is what my next step will be. It won’t be hard. Hopefully this code helps you and I hope you all have a great Christmas!

VB

USAGE:

 Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Dim da As New DataSet1TableAdapters.OrdersTableAdapter
        da.Fill(ds.Orders)
        Dim MyRet = LINQ_Where(ds.Orders, Function(row As DataSet1.OrdersRow) row.Order_ID < 200 And row.Order_ID > 1, "Order ID")
        If MyRet Is Nothing Then
            MessageBox.Show("NO ROWS")
        Else
            DataGridView1.DataSource = MyRet.CopyToDataTable
        End If
    End Sub

 

 Function LINQ_Where(Of TRow As DataRow)(source As TypedTableBase(Of TRow), predicate As Func(Of TRow, Boolean), OrderByField As String) As EnumerableRowCollection(Of TRow)

        Try

            Dim ReturnedRows = From row In source
                               Where predicate(row)
                               Order By row.Item(OrderByField)
                               Select row
            If ReturnedRows Is DBNull.Value Then
                Return Nothing
            Else
                Return ReturnedRows
            End If
            If ReturnedRows.Any = True Then
                Return ReturnedRows
            Else
                Return Nothing
            End If

        Catch ex As Exception
            Return Nothing
        End Try

    End Function

C#

 

USAGE:

private void Form1_Load(object sender, EventArgs e)
{
        DataSet1TableAdapters.OrdersTableAdapter da = new DataSet1TableAdapters.OrdersTableAdapter();
        da.Fill(ds.Orders);
        var MyRet = LINQ_Where(ds.Orders, (DataSet1.OrdersRow row) => row.Order_ID < 200 && row.Order_ID > 1, "Order ID");
        if (MyRet == null)
        {
            MessageBox.Show("NO ROWS");
        }
        else
        {
            DataGridView1.DataSource = MyRet.CopyToDataTable;
        }
    }

 

public EnumerableRowCollection<TRow> LINQ_Where<TRow>(TypedTableBase<TRow> source, Func<TRow, bool> predicate, string OrderByField) where TRow: DataRow
{

        try
        {

            var ReturnedRows = from row in source
                where predicate(row)
                orderby row.Item(OrderByField)
                select row;
            if (ReturnedRows == DBNull.Value)
            {
                return null;
            }
            else
            {
                return ReturnedRows;
            }
            if (ReturnedRows.Any() == true)
            {
                return ReturnedRows;
            }
            else
            {
                return null;
            }

        }
        catch (Exception ex)
        {
            return null;
        }

    }

Leave a comment

Northwind Database For DB2

Well if I thought Oracle was a mess getting the Northwind database to I hadn’t seen anything yet. I don’t care for the user tools at all in IBM Data Studio. The mapping to get SSIS to push a copy of my SQL based Northwind database was all over the map. If you don’t know, Northwind is the training tool of choice for many developers, businesses and consultants.

So again, I am doing you a solid. Linked here is the SSIS package you can pick up and use to transfer your SQL based version of Northwind to DB2. I also included the DB2 ddl for the database for your reference. I haven’t found an easy way to do it from the Access version. Sorry folks.

A couple things. You will need to create the database on your DB2 instance like below. use your standard command prompt with admin privileges.

create database NORTHWIN using codeset UTF-8 territory en

You may notice I left off the D. Yes I did. DB2 prefers its databases to be 8 characters or less. So that is why

Get the DTSX package here.

Once you have it open in Notepad and edit these lines with your information:

DTS:ConnectionString=”Data Source=DESKTOP-P1TI349;Initial Catalog=Northwind;Provider=SQLOLEDB;Integrated Security=SSPI;Auto Translate=false;” />

DTS:ConnectionString=”Data Source=NORTHWIN;User ID=db2admin;Provider=IBMOLEDB.DB2COPY1;Persist Security Info=True;Location=DESKTOP-P1TI349:50000;Extended Properties=&quot;&quot;;”>

Find/Replace DB2COPY1 with the name of your instance.

Save the file and open in SSIS.

And then you should be good to go…. if you have changes in your SQL Northwind database that aren’t reflected here (it will prompt you) you will need to use SSIS to transfer the data. Be sure for your destination you choose the instance name of your DB2 instance. (Mine was DB2COPY1).

That’s about it. Have a good day….

Leave a comment

Northwind Database For Oracle

Ah Northwind. The database everybody learns on. Well almost everyone. I realize it is a Microsoft product but you would have thought somebody along the line would have developed the same for Oracle and MySQL.

Why did this come up? In the next rendition of MySQLMove (which is being renamed for the next release – I still haven’t settled on a title for it yet. Something that indicates it is multi environment data collector and reporter. If you have suggestions or ideas please let me know…) I have been telling you about I wanted to add Oracle back ends as an option to get your data from. During the examples on past releases I used Northwind to demonstrate how to use the software. So I looked high and low on the web and didn’t find a single useable version of Northwind for Oracle. So I did it the old fashioned way. I exported my Northwind from SQL, table by table to excel files and imported them into Oracle. There’s an hour of my life I won’t get back. So just in case any one else has to go through this I pulled all the Oracle scripts for creating the tables and inserting the data, zipped them up and have made them available to you for download. All you have to do is download it, unzip it and run the scripts. Why? Because I am a nice guy that way and I had the time.  If I saved anyone some time, it was worth it.

, , ,

1 Comment

MySQLMove PivotWizard Adds SQL,CSV, MS Access Support and Chart Creation

If you haven’t been following along, MySQLMove is a tool that started out as a way for MySQL developers to do things the free tools to manage MySQL did not manage well. One of these things was the PivotWizard, which originally allowed MySQL developers, who do not have access to a pivot related function, to accomplish so here. As time went on, it became the most popular part of this software. The early versions of the PivotWizard were crude and did just the basics of pivoting data. I first added SQL support. Now I have added Microsoft Access and CSV file support, in addition to MySQL and SQL Server Database support and also have given you the ability to choose from 50+ different chart types to be able to look at your data in different ways. A lot has changed so I am going to go through it again, step by step.

You can download it here. Be sure to remove the old version if you installed it as the default MS install package can be buggy that way about removing it. Below is the directions on how to use the PivotWizard.

When you open MySQLMove you will see the following:

image

Click on the “Pivot Wizard”.

You will then see the screen to allow you to specify your connection type and parameters. If you choose MS Access you can use mdb or accdb file types and can specify if you use a password for it not. If you choose MS Access or CSV, a file dialog box will appear and ask  you to browse to the path your file is located at. If you choose CSV, it MUST be a comma delimited file and you should have a header row to define the columns. (Incidentally I should note that as long as your CSV files are located inside the same directory (folder) you specified in your connection you can join separate files in a query just as if you were joining two SQL tables like so:

SELECT *FROM [Orders.csv] a
INNER JOIN [Employees.csv] b on b.[ID] = a.[Employee ID]

No need for tools like PowerShell, other applications or confusing syntax.)

If you choose MySQL, be sure to precise on your casing of words. For this example, we will check the box for SQL Server and fill in its connection parameters.

image

I suggest you click on the Test button and verify all is well. I will check it for you before you leave the following screen but may as well get that out of the way now. If you get any of this information incorrect, you can’t proceed until it is fixed.

Once this is done, click the “Next” button.

image

You will then be asked to type or copy and paste a query based on this connection into the textbox. If you chose CSV format in the earlier screen this information will be filled in for you. After  you enter the query, you must click the “Parse” button. This will verify your connection is correct and your statement compiles. If it does, you will be told via message box how many columns you have to work with and then you will click “Next” to proceed. If it does not compile correctly, you will be informed and you will have to fix the statement to be able to proceed. (Please don’t email me about those kinds of issues. I really don’t have time for that.). You might also see a prompt letting you know it compiled with errors. As long as the issues aren’t fatal, you will be allowed to proceed and a report will be given to you letting you know what the problems were. Below the query box, you are asked to identify what you want Excel to call your report. Since this is the raw data, I called it “Raw Data”. This field is not mandatory. You might also notice the “?” mark icon. On the previous version, I attempted to have you directed to links to the MySQL Facebook group to answer common questions. That was a miserable failure. Instead in this edition, I have placed a tooltip that when you hover over the icon, it gives an explanation of what is going on.

Once this step is completed click the “Next” button.

image

Here you will pick what columns you will want to appear as the rows in your pivot report. You can select multiple columns as rows, just be sure they are related somehow. Once selected you are allowed to choose a name for the field. Excel will use that name instead of the field name in your report. Click the “Next” button to continue.

image

You will then be asked to choose “data” for your report. In this case I have chosen to see how many Order_IDs exist for each salesman (see the last screen). Use the arrows to choose your fields (you can have more then one, and you can reuse the same field if need be.) and select a grouping to be applied. Click the “Next” button to continue.

image

You will then be asked to choose the columns you want to represent in your pivot report and how you want the data grouped. If  you notice we choose to see Order_Date twice in our columns because we want it grouped by Quarter and inside that quarter, by Day of Week. I add a column to your original query with the same name and data with a “_1” after it. So now we have asked to see how many orders our salesman did, divided into quarter and inside that quarter by Day of Week. Play with it, you will get the hang of it. Click “Next” to continue.

image

Here you choose the type of chart to represent the data you have requested and in Chart Title have given Excel the information it needs to call your sheet and chart name what you want. Hopefully the images of the charts you see will assist you in making the choice of charts that you want. Click the “Next” button to continue.

image

Finally, at the end. Now you can give your pivot report a name for Excel to use for your pivoted data (or not if you don’t care) and then click the “Finish” button. Be patient, it takes a few second for Excel to show you your work. Just as a word of caution, I tried to anticipate anything you could do wrong in naming your charts and reports. If something you should happen, you will get a notice of what the problem is but it will allow you to still see what you have produced. It just will not give the worksheet name you tried to give it.

You can see the sample we produced here.

The next version of this software will allow you to save templates of different reports and allow you to create more then just one query. The next version will allow you to store multiple connections, multiple querys and pivot reports and charts specific to each query, each produced into the same Excel Workbook. Keep an eye on here and on the MySQLMove Facebook page for updates.Again you can download the install package for MySQLMove here.

Well that about covers it. Well not quite. I have been asked if I would be willing to do a custom version of this for a specific person or company. The answer is of course, yes. Also if you have bugs you have seen please let me know. Just email me at kellyjmartens@hotmail.com and I would be happy to help.

, , , , ,

1 Comment