dwight rau

SQL Server select data from CSV as if it were a table

2009-09-24T13:13:00.002-04:00

I can select without using DTS or a linked server.

SELECT * FROM OPENROWSET
('MICROSOFT.JET.OLEDB.4.0','Text;Database=C:\Folder\;',
'SELECT * FROM [Report.csv]') MyAlias

You may need these permissions
sp_configure 'Ad Hoc Distributed Queries', 1
reconfigure

You may have problems on X64

Linq: Split string array and convert into integer using a converter / lambda and then selecting where in or contains from that list

2008-10-09T17:02:00.000-04:00

// This will split an CSV string array into an array of integers
int[] intArr = Array.ConvertAll( priorityCsv.Split(','), n => Convert.ToInt32(n) );

//This will select the count of values where there are in or contain the values in the array
return (from v in this.Context.VACompanyErrors where intArr.Contains(v.Priority) select v).Count();

fill text box with nullable type or empty

2008-05-20T16:44:00.001-04:00

fill text box with nullable type or empty
txtExpectedCloseDateTextBox.Text = olo.ExpectedCloseDate.ToString() ?? string.Empty;

iterate

2008-04-25T15:17:00.000-04:00

list.ForEach(delegate(MyType x) { x.Property1.Replace("AAAA", "").Replace("BBBB", ""); });
OneType one = OneTypeList.Find(delegate(OneType ooo) { return oood.Value == "findstring; });

table with infinite dynamically expandable columns veritcal rows propertes attributes eav otlt

2008-02-13T02:34:00.004-05:00

If you need to have a table with attributes(columns) that need to be added dynamically, consider going vertically. There are various methods to add attributes to an entity by adding the attributes as rows.

you may be looking for these (Google them):

Entity-Attribute-Value model
“OTLT” (One True Lookup Table)
vertical model
vertical database
row modeling
attribute value system
sql_variant

Think deeply before you use any of these. Be careful you don't end up with a mess.
Resources:

Dave’s guide to the EAV

OTLT and EAV: the two big design mistakes all beginners make

Entity-Attribute-Value (EAV) model with SQL Server 2005 xml datatype

An Introduction to Entity-Attribute-Value Design for Generic Clinical Study Data Management Systems

Entity-Attribute-Value model

Explanation of many database models including EAV

-Dwight

Generate or create XSD Schema from Database Table in sql server 2005

2008-02-13T02:09:00.000-05:00

create table Person
(
Age int not NULL check( Age > 0) ,
Height numeric(10,2) not NULL check( Height > 5),
Gender varchar(5) not null check( Gender in ('M', 'F', 'O')),
BirthDate datetime null,
)
DECLARE @schema xml
SET @schema = (SELECT * FROM Person FOR XML AUTO, ELEMENTS, XMLSCHEMA('PersonSchema'))
select @schema
OUTPUT:

identify system service 100% cpu svchost.exe

2008-01-25T13:35:00.000-05:00

Which system service is causing svchost.exe to use 100% cpu value?

First lookup the PID (process identifier) number in task manager that is causing the problem. You may have to tell task manager to add the column.

This command will allow you to identify the problem service by showing what each svchost.exe PID is running.

C:\>tasklist /SVC /FI "IMAGENAME eq svchost.exe"

In vista you can right your problem process click and choose "go to service".

Pattern Links

2007-12-28T12:56:00.000-05:00

dofactory design patterns
http://www.dofactory.com/Patterns/Patterns.aspx

A short summary of the patterns in Patterns of Enterprise Application Architecture (P of EAA).
http://www.martinfowler.com/eaaCatalog/

WCF permission problem

2007-12-12T00:36:00.000-05:00

In windows (likely to be vista) where you do not have acccess to register an http listener with WCF..

"HTTP could not register URL http://+:8080/QuickReturns/. Your process does not have access rights to this namespace (see http://go.microsoft.com/fwlink/?LinkId=70353 for details)."

{"Access is denied"}

as admin run:
netsh http add urlacl url=http://+:8080/ user=dwight

Web Parts with SQL Server 2005 instead of sql express for testing

2007-10-12T00:07:00.000-04:00

try this:

connectionString="data source=VRTSVR04;Initial Catalog=DemoPortal;User Id=sa2;Password=di902jkd89x88-1;"
providerName="System.Data.SqlClient"/>

keywords: connection string default The entry 'LocalSqlServer' has already been added. aspnetdb

Generate XSD Schema From XML using inference

2007-10-05T01:36:00.000-04:00

Upload an xml file and we will return you the schema as an XSD file. This is called Inferring the schema. This uses the .Net InferSchema method of the XmlSchemaInference class. Create an XML file given an XSD.

windows workflow custom activity values not persisting check results after exectution property System.Workflow.ComponentModel.Activity

2007-08-15T11:31:00.000-04:00

If you are having trouble retrieving the results of a custom activity that inherits activity in windows workflow and your value is null or reset to the default, try a property to a static variable.

Extract columns from microsoft SqlProfileProvider

2007-06-29T21:17:00.000-04:00

If you want to select data from the asp.net membership api database but you are having problems because the PropertyValuesString / PropertyNames / PropertyValuesBinary values are all delimited / separated by a colon but you want to but cant use a split function on it. Below is a solution. There are also UserId LastUpdatedDate columns.

Step 1. Execute the sql from HERE to create some functions to extract your data.
Step 2. Create a view like the example below
Step 3. (optional) index your view.

create my_cusomers_vw view as
SELECT
dbo.fn_GetProfileElement('Phone',PropertyNames,PropertyValuesString) AS Company,
dbo.fn_GetProfileElement('ZipCode',PropertyNames,PropertyValuesString) AS ZipCode,
dbo.fn_GetProfileElement('State',PropertyNames,PropertyValuesString) AS State
FROM dbo.aspnet_Profile
GO

select * from my_customers_vw

--------------------------------------------

fn_GetElement fn_GetProfileElement FUNCTION

UML Cheet Sheet

2007-05-08T17:07:00.000-04:00

Cheat Sheet 1

Cheat Sheet 2

Asp.net 2.0 Post Retrieve Send values from another page using PostBackUrl

2007-02-28T10:28:00.000-05:00

Various techniques including Page.PreviousPage.FindControl, PreviousPage, Request.Form

This is how you can post from one page to another
How to: Post ASP.NET Web Pages to a Different Page

This is different ways you can read data posted from one page to another
How to: Pass Values Between ASP.NET Web Pages

C# Web Crawler for links

2006-12-25T00:46:00.000-05:00

This code will crawl a web page and gather urls (hyperlinks) and their titles into collections and bind them to a datagrid. The code uses Regular Expressions (Regex), HttpWebRequest, WebRequest, WebResponse and more.

View original source code here

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Text.RegularExpressions;
using System.Collections.ObjectModel;
using System.Net;

namespace Regexer
{
public partial class Form1 : Form
{
public Form1()

{
InitializeComponent();
}

private string Fixx(string x)
{
return x.Replace(""", @"""").Replace(">", ">").Replace("<", "<");
}

private void button1_Click(object sender, EventArgs e)

{
//string testData = ReadStream("http://www.google.com/search?as_q=test+results&hl=en&num=100&btnG=Google+Search", 9000);

string testData = ReadStream("http://del.icio.us/", 99000);

Regex test = new Regex(Fixx(@"<[aA][\s]+[^>]*?[hH][rR][eE][fF][\s]?=[\s\"\']+(.*?)[\"\']+.*?>([^<]+|.*?)?<\/[aA]>";));
//The regex on the above line will be mangled, click the image below to see what it should be.

MatchCollection mc = test.Matches(testData);
Collection<UrlData> uColl = new Collection<UrlData>();

foreach (Match mt in mc)

{
UrlData url = new UrlData();
url.Url = mt.Groups[1].Value;
url.Title = mt.Groups[2].Value;
uColl.Add(url);
}

dataGridView1.DataSource = uColl;
dataGridView1.AutoResizeColumn(1);
}

public static string ReadStream(string url, int timeOut)
{
String result;
WebResponse objResponse;
WebRequest objRequest = System.Net.HttpWebRequest.Create(url);
objRequest.Timeout = timeOut;

objResponse = objRequest.GetResponse();

using (System.IO.StreamReader sr =
new System.IO.StreamReader(objResponse.GetResponseStream()))
{
result = sr.ReadToEnd();
sr.Close();
}
return result;
}
}

public class UrlData
{
private string url = null;
private string title = null;

public string Url
{
get { return url; }

set { url = value; }
}

public string Title
{
get { return title; }
set { title = value; }
}
}

}

Verisign PayFlow Authentication ECI XID CAVV values Example

2006-07-20T17:26:00.000-04:00

I am sending orders to verisign payflow pro and i am using a third party for
additional "Buyer authentication". The third party sends certain response values
back to me to include in my order request to verisign to decide if the order
is fraudulent or not. They work along with MasterCard secure code and
verified by visa. The upside to this is that if they say the order is okay
then the merchant is not libel for the fraud and they lose no money.
We are using XMLPay document to send our messages to verisign and i want to include
this information i have recieved from the third party.
It is unclear in the XMLPay spec where to insert this information.
I do see examples where to insert the ECI, XID, CAVV values in many other
manuals but they only show examples in a string format instead of an xml format.
I found a quote below in a manual but this was no help.

Special Note for XMLPay users

For Call 4 on page 42, pass AUTHENTICATION_STATUS=,

AUTHENTICATION_ID=, CAVV=, and XID=,

ECI= in the ExtData for Authorization and Sale transactions.

This was not correct. I also emailed verisign and they only responded
with another copy of the manual.

Here is an example document of the correct place to insert these.


/*
<ns0:XMLPayRequest Timeout="60" version="2.0" xmlns:ns0="http://www.verisign.com/XMLPay">
<RequestData>
<Vendor>myvendor</Vendor>
<Partner>verisign</Partner>
<Transactions>
  <Transaction Id="GPECAWJ4AG358HAF35RXK55A87" CustRef="myname@myaddr.com">
    <Authorization>
      <PayData>
        <Invoice>
          <Date>2006-07-05</Date>
          <BillTo>
            <Name>first last</Name>
            <Address>
              <Street>7600 Main Street</Street>
              <Street2></Street2>
              <City>Boaz</City>
              <State>VA</State>
              <Zip>12345</Zip>
              <Country>US</Country>
            </Address>
            <EMail>test2user@email.com</EMail>
          </BillTo>
          <ShipTo>
            <Name>first last</Name>
            <Address>
              <Street>7600 My Street</Street>
              <Street2></Street2>
              <City>Boaz</City>
              <State>VA</State>
              <Zip>21222</Zip>
              <Country>US</Country>
            </Address>
          </ShipTo>
          <TotalAmt>531.75</TotalAmt>
          <Comment>1093293</Comment>
          <CustIP>10.0.30.208</CustIP>
          <Items>
            <Item>
              <SKU>TELE101032</SKU>
              <Quantity>1</Quantity>
              <TotalAmt>495</TotalAmt>
            </Item>
            <Item>
              <SKU>TELE101322</SKU>
              <Quantity>1</Quantity>
              <TotalAmt>203.00</TotalAmt>
            </Item>
          </Items>
          <ExtData Name="COMMENT2" Value="1PECFWJ4Aq358HAF35XRK55A87"></ExtData>
        </Invoice>
        <Tender>
          <Card>
            <CardType>Visa</CardType>
            <CardNum encrypt="1">qFS+dZ9kEi/sCCPe288eB</CardNum>
            <ExpDate encrypt="1">E3V1BuHDhEBCzEvHURwB92zWjfJc2+rC</ExpDate>
            <CVNum encrypt="1">Kd762lK+2HAkxiRrNK7JCA==</CVNum>
            <NameOnCard>My Name</NameOnCard>
          </Card>
        </Tender>
      </PayData>
      <ExtData>
        <CAVV>AAABBEKQkQAAeAABEo3RENiWiVM=</CAVV>
        <XID>v73iqKAE/sM4Z2rX8qSCfMDavt4=</XID>
        <ECI>05</ECI>
        <STATUS>Y</STATUS>
      </ExtData>
    </Authorization>
    <Verbosity>Medium</Verbosity>
    <BrowserUserAgent>Mozilla/4.0 </BrowserUserAgent>
  </Transaction>
</Transactions>
</RequestData>
<RequestAuth>
<UserPass>
  <User>testUser10293</User>
  <Password>password09209320931</Password>
</UserPass>
</RequestAuth>
</ns0:XMLPayRequest>

*/

2006-07-20T16:45:00.000-04:00

Sql T-Sql Sql server 2000 select or show distinct rows by column instead of the entire row. In this case it only shows the latest by date. 2 examples are provided. Thanks to Steve Hamilton.


/*
CREATE TABLE #TestTable( ID INT, TEST VARCHAR(50), TEST2 VARCHAR(50), TEST3 DATETIME )

INSERT INTO #TestTable VALUES ( 1, 'A', 'A', GETDATE() + 0 )
INSERT INTO #TestTable VALUES  ( 2, 'A', 'B', GETDATE() + 1 )
INSERT INTO #TestTable VALUES ( 3, 'A', 'C', GETDATE() + 2 )
INSERT INTO #TestTable VALUES ( 4, 'A', 'D', GETDATE() + 3 )
INSERT INTO #TestTable VALUES ( 5, 'B', 'A', GETDATE() + 4 )
INSERT INTO #TestTable VALUES ( 6, 'B', 'B', GETDATE() + 5 )
INSERT INTO #TestTable VALUES ( 7, 'B', 'C', GETDATE() + 6 )
INSERT INTO #TestTable VALUES ( 8, 'B', 'D', GETDATE() + 7 )
INSERT INTO #TestTable VALUES ( 9, 'C', 'A', GETDATE() + 8 )
INSERT INTO #TestTable VALUES ( 10, 'C', 'B', GETDATE() + 9 )
INSERT INTO #TestTable VALUES ( 11, 'C', 'C', GETDATE() + 10 )
INSERT INTO #TestTable VALUES ( 12, 'C', 'D', GETDATE() + 11 )
INSERT INTO #TestTable VALUES ( 13, 'D', 'A', GETDATE() + 12 )
INSERT INTO #TestTable VALUES ( 14, 'D', 'B', GETDATE() + 13 )
INSERT INTO #TestTable VALUES ( 15, 'D', 'C', GETDATE() + 14 )
INSERT INTO #TestTable VALUES ( 16, 'D', 'D', GETDATE() + 15 )

SELECT DISTINCT *
FROM #TestTable T
  INNER JOIN 
  (
   SELECT ID
   FROM #TestTable T3
   WHERE T3.TEST3 = (SELECT MAX(TEST3) FROM #TestTable T4 WHERE T3.TEST = T4.TEST)
  ) T2
  ON T2.ID = T.ID

SELECT * FROM #TestTable t 
WHERE T.TEST3 = (SELECT MAX(TEST3) FROM #TestTable T2 WHERE T2.TEST = T.TEST)

DROP TABLE #TestTable
*/

Hello

2006-07-03T22:41:00.000-04:00

I put this page here to post my code in hopes it might be useful to other people. Some of the sentences may appear awkward either because i cant type or because i typed it easiest for google to index and people to find.

wrt54g v5 qos does not appear to be working broken

2006-05-08T18:56:00.000-04:00

if you have the wrt54g router, and your using it with a voip service such as vonage or sun rocket, you may need to tune the QOS settings in the router in order to avoid being cut off or to have choppy distorted sounds. this qos will make sure that the router will do its best to give the most bandwidth to voice instead of data. the problem i had with the router was that the QOS (quality of service) does not appear to work correctly when using wireless. if your not using any wireless devices, it appears to work correctly. im not 100% sure but i believe you can have this working correctly by putting the mac address of the wireless device and setting it to low, and by setting the port number of the gizmo or voip device to high. note that the wireless device is only found by mac, and if you dont give it a priority, i think it defaults to high. the only problem with this, is that the default firmware on the device will only allow you to put two different devices at low priority. so if you have more than two wireless devices your screwed. someone on another site said to put the only the mac address of the gizmo instead and set that to highest priority, and not to use the other spot but it didn't appear to work for me, but you try it. if you are willing to upgrade your box to a better firmware that is better, check out here
to see if your box is upgradeable.

convert a dataview to a dataset dataview to datatable

2006-03-16T16:58:00.000-05:00

public static DataTable DataViewAsDataTable( DataView dv)
{
DataTable dt = dv.Table.Clone();
foreach (DataRowView drv in dv)
dt.ImportRow(drv.Row);
return dt;
}

DataSet dsFiltered = new DataSet(); //create a new dataset
dsFiltered.Tables.Add(AppUtil.DataViewAsDataTable (dvResult) ); //fill the dataset with the sorted results

convert a dataview to a dataset dataview to datatable

2006-03-16T16:56:00.000-05:00

public static DataTable DataViewAsDataTable( DataView dv)
{
DataTable dt = dv.Table.Clone();
foreach (DataRowView drv in dv)
dt.ImportRow(drv.Row);
return dt;
}

Comerce server 2002 pup packaging problem Error finding IIS object

2006-03-01T11:47:00.000-05:00

If you have this problem with commerce server 2002 when creating a pup package with the site packager:

Error finding IIS object for site:Automation error
The system cannot find the path specified.
Error finding directory for site 0:There is no entry in IIS for this
application on this machine.

Try this:

Go into the properties for the web site and go to the "Home Directory" tab and click "Remove" under application settings and click "Create." Make sure your app pool is correct.
ALSO repeat the same for your virtual directory.
Run this in this in the MSCS_Admin database
SELECT s_PropertyName, s_value FROM ResourceProps WHERE s_PropertyName ='s_WebServerInstance'
If the value of WebServerInstace has a trailing zero, remove the zero
In commerce center manager go here: Server manager -> Commerce Sites -> Your Site -> Applications -> IIS Site Name and verify your host name and IIS application path are correct. This is probably correcy but check it anyways. Next, click the web servers tab, and make sure your IIS web site is correct and click Synchronize. The odds are your IIS web site was not correct.
Run iisreset and package again.

The biggest problem I see people explainin wrong in newsgroups is you need to do step 1 and 2 both.

Area code lookup

2006-02-14T13:55:00.000-05:00

Search for an area codes and get the telephone company, and approximate GPS coordiantes of where a cell phone was purchased. This is great for looking up cell phones that called you. you can also lookup various other forms of information.

http://www.areacodedownload.com/

I personally just edit the url to lookup locations
http://www.areacodedownload.com/410/247/

bill gates got hit with a pie

2006-02-09T15:24:00.000-05:00

does anyone remember this when bill gates got hit with a pie?