You will need a reference to the library "Microsoft XML, v6.0".
Sub getWebServiceX()
Dim req As New MSXML2.XMLHttp
req.Open _
"GET" _
, "http://www.webservicex.com/stockquote.asmx/GetQuote?symbol=GOOG" _
, False
req.send
MsgBox req.responseText
End Sub
2014-12-29
VBA - XMLHttp.open access is denied
If you get an access denied error, then your windows/internet explorer security settings might not be letting you access the URL being requested pro grammatically.
- Use the URL over HTTPS if available (change the URL from http:// to https://).
- Relax your security settings.
2014-11-08
TSQL - Integer Percentiles
select
T1.Field1
, PERCENT_RANK()
over( order by Field1 asc ) as PercentRank
, cast(
round(
PERCENT_RANK()
over( order by Field1 asc )
* 100
, 0
)
as int
) as PercentRankInt
from
(
select 1 as Field1
union all select 1
union all select 1
union all select 2
union all select 2
union all select 3
union all select 4
--union all select 8
) as T1
order by
Field1
;
PercentRankInt calculates the percentile each value occupies in relation to all other values in the column, as an integer number, ready to be used for quick filters, thus enabling the exclusion of, for example, top and bottom deciles, quintiles, etc.
2014-09-18
TSQL - MSDTC on server is unavailable.
Go to "Services", and start the "Distributed Transaction Coordinator" service.
TSQL - Stored procedure results insertion into table at linked server - No columns or permissions
ISSUE:
The OLE DB provider "SQLNCLI11" for linked server "LINKED_SERVER_NAME_HERE" indicates that either the object has no columns or the current user does not have permissions on that object.
USE THIS METHOD:
insert THESIS.dbo.ExposureCFLM
(
Field1
, Field2
, Field3
)
execute(
'
set
fmtonly off
exec
DATABASE_NAME_HERE.dbo.STORED_PROCEDURE_NAME_HERE
@prm1=222
,
@prm2 = ''aBC''
;
') at LINKED_SERVER_NAME_HERE;
TSQL - Server is not configured for RPC
exec sp_serveroption
@server='me'
, @optname='rpc'
, @optvalue='TRUE'
;
exec sp_serveroption
@server='me'
, @optname='rpc out'
, @optvalue='TRUE'
;
TSQL - Restore database from .mdf, recreating the log
CREATE DATABASE THESIS ON
(FILENAME =
N'C:\MSSQL\PATH...\DATABASE_FILE.mdf')
FOR ATTACH_REBUILD_LOG
;
2014-09-06
TSQL - Add self-referencing linked server (windows authentication)
exec sp_addlinkedserver
@server='me' -- here you can specify the name of the linked server
, @srvproduct=''
, @provider='sqlncli' -- using SQL Server native client
, @datasrc='(local)' -- add here your server name
, @location=''
, @provstr=''
, @catalog='THESIS'
;
-- Using MSDASQL... not optimal.
exec sp_addlinkedserver
@server =
'me'
, @srvproduct=''
, @provider='MSDASQL'
, @provstr='DRIVER={SQL Server}; Server=(local); Initial Catalog=master;
Integrated Security=SSPI;'
;
Use this command instead, if using SQLEXPRESS...
exec sp_addlinkedserver
@server =
'selfSQLEXPRESS'
, @srvproduct=''
, @provider='MSDASQL'
, @datasrc=NULL
, @location=NULL
, @provstr='DRIVER={SQL Server}; Server=(local)\SQLEXPRESS; Initial
Catalog=master; Integrated Security=SSPI;'
;
TSQL - Enable OPENROWSET
Symptom:
Msg 15281, Level 16, State 1, Line 1
SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of
component 'Ad Hoc Distributed Queries' because
this component is turned off as part of the security
configuration for this server. A system
administrator can enable the use of 'Ad Hoc Distributed Queries' by
using sp_configure. For more
information about enabling 'Ad Hoc Distributed Queries', search for 'Ad Hoc Distributed Queries' in
SQL Server
Books Online.
Fix:
sp_configure 'show
advanced options',1
reconfigure with
override
go
sp_configure 'Ad Hoc
Distributed Queries',1
reconfigure with
override
go
If after this you still get an error about name pipes, enable them.
https://www.blackbaud.com/files/support/infinityinstaller/content/installermaster/tkenablenamedpipesandtcpipconnections.htm
2014-08-27
Teradata - Table inventory query
select
DatabaseName
,
TableName
,
TableKind
from
dbc.tablesX as T
where
T.AccessCount
is not null
and T.DatabaseName in (
' DATABASE_1_NAME_HERE
'
, 'DATABASE_2_NAME_HERE'
)
;
Teradata - PeriodID arithmetics (PeriodID = date in YYYYMM format)
--declare prmPeriodID as integer;
--set prmPeriodID = 201112;
sel
-- PeriodID,
starting point, main
201112
as PeriodID
-- Year
,
cast( 201112 / 100 as integer) as PeriodYear
-- Month
,
201112 - ( cast( 201112 / 100 as integer) * 100 ) as PeriodMonth
-- Date
,
cast
(
cast( 201112 / 100 as integer)
||
'-'
||
trim( 201112 - ( cast( 201112 / 100 as integer) * 100 ) )
||
'-01'
as date--varchar(50)
)
as PeriodDate
-- Previous Month
,
cast
(
cast( 201112 / 100 as integer)
||
'-'
||
trim( 201112 - ( cast( 201112 / 100 as integer) * 100 ) )
||
'-01'
as date--varchar(50)
)
- interval '1' month
as PeriodDateMinus1Month
-- Next Month
,
cast
(
cast( 201112 / 100 as integer)
||
'-'
||
trim( 201112 - ( cast( 201112 / 100 as integer) * 100 ) )
||
'-01'
as date--varchar(50)
)
+ interval '1' month
as PeriodDatePlus1Month
-- NExt PeriodID
,
(
-- Year
(
extract(
year from
cast
(
cast( 201112 / 100 as integer)
||
'-'
||
trim( 201112 - ( cast( 201112 / 100 as integer) * 100 ) )
||
'-01'
as date--varchar(50)
)
+ interval '1' month
)
* 100
)
-- Month
+
extract (
month from
cast
(
cast( 201112 / 100 as integer)
||
'-'
||
trim( 201112 - ( cast( 201112 / 100 as integer) * 100 ) )
||
'-01'
as date--varchar(50)
)
+ interval '1' month
)
)
as PeriodIDPlus1
;
Teradata - Macro example/template
/*
--
Execution
exec
CFDW2_RDL_ERIDL_CFS_VWS.mPeriodTest(201112,12);
*/
replace macro
CFDW2_RDL_ERIDL_CFS_VWS.mPeriodTest
(
prmPeriodID
INTEGER
,
prmAdd BYTEINT
)
as
(
-- Calculations
--declare
prmPeriodID INTEGER DEFAULT 201112;
--set prmPeriodID =
( ( ( :prmPeriodYear - 1 ) * 100 ) + 12 );
select
:prmPeriodID
as PeriodID
,
:prmAdd as Adder
;
);
Teradata - Database, table and columns inventory query
SELECT
T.DatabaseName
,
T.TableName
,
T.TableKind
,
C.*
FROM
dbc.tablesX T
inner join
dbc.columnsX C
on
T.DatabaseName
= C.Databasename
and T.TableName = C.Tablename
where
1=1
-- Access count
--T.AccessCount is
not null
-- Filter in/out
databases you don't want to inspect
/*and
T.DatabaseName NOT in (
''
)*/
-- Request text?
--and T.RequestText
LIKE '%TERM_HERE%'
-- Filter for
column names of interest
--and C.ColumnName like '%PARTIAL_COLUMN_NAME_HERE%'
ORDER BY
T.DatabaseName
,
T.TableName
,
C.ColumnName
;
Teradata - Basic arithmetics sampler
select
2+3
,
2-3
,
2*3
,
2/3
,
2/3.0
,
2.0/3.0
,
2.00/3.00
,
cast( 2 as float ) / cast( 3 as float )
,
2**3
,
2||3
,
EXP(1)
,
LOG(1)
,
2 / NULLIFZERO(0)
,
RANDOM (1,100)
,
SQRT(9)
,
2 mod 3
,
3 mod 2
,
date '2012-06-06' - date '2012-06-01'
,
cast('' as float ) as EmptyStringToFloat
;
Teradata - Function dates demo
select
-- Date pieces
current_date as Today
,
current_timestamp as aTimestamp
,
current_date (format 'yyyyddd') (char(7)) as Formatyyyydd
,
extract( year from date '2012-06-06' ) as TheYear
,
extract( month from date '2012-06-06' ) as TheMonth
,
extract( day from date '2012-06-06' ) as TheDay
-- Period ID
,
cast(
(
extract( year from current_date ) * 100 )
+
extract( month from current_date )
as int
)
as PeriodID
-- Alternative
,
CAST( cast((current_date (format 'YYYYMM')) as varchar(6)) AS INT ) as PeriodIDAlt
-- Day ID
,
cast(
(
extract( year from current_date ) * 10000 )
+
( extract( month from current_date ) * 100 )
+
extract( day from current_date )
as int
)
as DayID
-- Timestamp
conversions
,
cast(
cast(CURRENT_DATE as timestamp(3))
+
((CURRENT_TIME - time '00:00:00') hour to second(0))
as timestamp(3)
)
as Timestamp3
,
current_date (format 'yyyyddd') (char(7)) as JulianDate
-- Date artithmetic
,
current_date + 1 as Tomorrow
,
add_months( current_date, 1) as NextMonth
--, current_date +
interval '1' month as NextMonth2
,
add_months( current_date, -1) as PreviousMonth
--, current_date -
interval '1' month as PreviousMonth2
,
current_date - date '2012-01-31' as DateDiffInDays
,
cast(
cast(
cast( current_date - date '2012-01-31' as float)
/
cast(30.5 as float)
as decimal(4,0)
)
as byteint
)
as DateDiffInMonths
;
Subscribe to:
Posts (Atom)