Excel: Searching inside a string

VBA = instr(string to be searched, string searching for, where to start)


formula:  = search()

Microsoft Home Use Program

If you have a valid SOCOM email address, you can take advantage of the Microsoft Home Use Program.

http://www.microsofthup.com/hupus/home.aspx?culture=en-US

Program Code:  8FC2A88309

The cost for Microsoft Office is a staggering $9.95.

SELECT from a Stored Procedure

There are multiple ways of doing this.

Method #1

1. if exists (select * from master..sysservers where srvname = 'loopback')     
2. exec sp_dropserver 'loopback'
3. go 

Method #2

1. exec sp_addlinkedserver @server = N'loopback', @srvproduct = N'',     @provider, @datasrc = N'SQLOLEDB', = @@servername 
2. go

Method #3

1. select * into #t from openquery(loopback, 'exec yourSproc') 
2. select * from #t 
3. drop table #t

go

  

Export to XML from SQL Server

select EmployeeID, FirstName, LastName from employees

ORDER BY EmployeeID

FOR XML AUTO, elements

select EmployeeID, FirstName, LastName from employees

ORDER BY EmployeeID

FOR XML AUTO

Upload image into SQL Server 2005

INSERT INTO myTable(Document)

SELECT * FROM OPENROWSET(

     BULK N'C:\Inetpub\wwwroot\file.jpg', SINGLE_BLOB

) rs

SQL Server T-SQL table variable example

DECLARE @tblTravelRecords table (

     RequestID uniqueidentifier,

     TravelNumber varchar(7),

     EstimatedTravelCost decimal(11,2),

     [status] int

)

INSERT INTO @tblTravelRecords

SELECT pol.RequestID

     , pol.PONumber

     , CAST( subtotal + pol.Shipping + IsNull(pol.Discount,0) + pol.SalesTax as decimal(11,2) ) as 'EstimatedTravelCost'                             

     , pol.Status

FROM luPOStatus lu RIGHT JOIN (PurchaseOrderLog pol LEFT JOIN @tblPOsubtotal pod ON pol.RequestID=pod.RequestID) ON lu.StatusID=pol.Status

WHERE [Contract] = @Contract

          AND TaskNumber = @TaskNumber

          AND SubTask = @SubTask

          AND ( UseFFPFunds <> 1 OR UseFFPFunds IS NULL )

         

Excel Functions

http://www.contextures.com/xlFunctions04.html

Listing all constraints in SQL Server & MS-Access

SELECT     FK_Table  = FK.TABLE_NAME,     FK_Column = CU.COLUMN_NAME,     PK_Table  = PK.TABLE_NAME,     PK_Column = PT.COLUMN_NAME,     Constraint_Name = C.CONSTRAINT_NAME FROM     INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C     INNER JOIN     INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK         ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME     INNER JOIN     INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK         ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME     INNER JOIN     INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU         ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME     INNER JOIN     (         SELECT             i1.TABLE_NAME, i2.COLUMN_NAME         FROM             INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1             INNER JOIN             INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2             ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME             WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'     ) PT     ON PT.TABLE_NAME = PK.TABLE_NAME -- optional: ORDER BY     1,2,3,4

If you want to limit it to specific tables, you can add any of the following immediately prior to the optional ORDER BY clause:
 

WHERE PK.TABLE_NAME='something'     WHERE FK.TABLE_NAME='something'     WHERE PK.TABLE_NAME IN ('one_thing', 'another')     WHERE FK.TABLE_NAME IN ('one_thing', 'another')

Microsoft Access

Here is some code that uses ADOX.Catalog:
 

<%     Set conn = CreateObject("ADODB.Connection")     Set cat = CreateObject("ADOX.Catalog")     conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _         "Data Source=<path to db>"     Set cat.ActiveConnection = conn     Response.Write "<table border=1><tr>" & _         "<th>Parent Table/Column</th>" & _         "<th>Child Table/Column</th>" & _         "<th>Key Name</th></tr>"     For Each tbl in cat.Tables         if left(tbl.Name, 4) <> "MSys" then             For Each key in tbl.Keys                 If key.Type = 2 Then                     For Each col in key.Columns                         Response.Write "<tr><td>" & tbl.Name & "." & _                             col.Name & "</td><td>" & _                             key.RelatedTable & "." & _                             col.RelatedColumn & "</td><td>" & _                             key.Name & "</td></tr>"                     Next                 End If             Next         End If     Next     Response.Write "</table>"     Set cat = Nothing     conn.Close : Set conn = Nothing %>

Last Modified Date in SQL Server

USE Evaluation;

GO

SELECT name, create_date, modify_date

FROM sys.objects

WHERE type = 'P'

ORDER BY modify_date DESC

GO

select o.name, o.create_date, o.modify_date, len(m.definition)
from sys.sql_modules as m inner join sys.objects as o on m.object_id=o.object_id
where type = 'P'
order by o.modify_date DESC


SELECT o.schema_ID, s.name, o.name, o.create_date, o.modify_date, len(m.[definition]) as 'Definition_length'
from sys.schemas s INNER JOIN (sys.sql_modules m inner join sys.objects o on m.object_id=o.object_id) ON s.schema_id = o.schema_id
where type = 'P'
and m.[definition] LIKE '%2015%'
--and o.name IN ('snapshot', 'snapshotbase', 'snapshotLimit', 'snapshotlimitnonhc', 'snapshot_rel2_model_a','snapshot_rel2_model_b', 'snapshot_rel2_model_c', 'snapshotlimitnonhc')
and s.name NOT IN ('Reporting')
order by o.schema_ID, o.name, o.modify_date DESC


select * from sys.schemas

KB: IListSource does not contain any data sources

Is it possible that there is nothing in the returned dataset?

A handy way to investigate is to insert

r_DataSet.WriteXML("c:\test.xml")

When you open text.xml, you'll see what the dataset looks like.