About Me

My photo
Northglenn, Colorado, United States
I'm primarily a BI Developer on the Microsoft stack. I do sometimes touch upon other Microsoft stacks ( web development, application development, and sql server development).

Tuesday, July 01, 2008

Bulk Export from SQL Server into a CSV file

Well, here is a simple query/stored procedure that you can run to bulk export the tables in a database into a csv file.

Just change the “databaseName” in the file to the one you want to point to and also change the location if you wish. Currently it is the C:\ drive.

 

DECLARE @var nvarchar(MAX)

DECLARE curRunning
CURSOR LOCAL FAST_FORWARD FOR
select name from sysobjects where type = 'U'

Open curRunning

Fetch NEXT From curRunning into @var

WHILE @@FETCH_STATUS = 0
BEGIN
    --select 'exec master.dbo.xp_cmdshell ''bcp databaseName.dbo.' + @var + ' out C:\' + @var + '.csv -c -T -t ,'''
    DECLARE @Exec nvarchar(MAX)
    set @Exec = 'exec master.dbo.xp_cmdshell ''bcp databaseName.dbo.' + @var + ' out C:\' + @var + '.csv -c -T -t ,'''
    execute sp_executesql @Exec
FETCH NEXT FROM curRunning into @var
END

close curRunning
DEALLOCATE curRunning

Wednesday, March 12, 2008

WCF Binding Info

BasicHttpBinding

Element Collection

System.ServiceModel.Channels.TextMessageEncodingBindingElement
System.ServiceModel.Channels.HttpTransportBindingElement

Support Features

  • Transport-Level Security
  • Message-Level Security
  • WS-*Interoperability

Use if

  • Need basic level of interoperability

Performance : Good

Communication

  • Request/Reply
  • One-Way


WSHttpBinding

Element Collection

System.ServiceModel.Channels.TransactionFlowBindingElement
System.ServiceModel.Channels.SymmetricSecurityBindingElement
System.ServiceModel.Channels.TextMessageEncodingBindingElement
System.ServiceModel.Channels.HttpTransportBindingElement

Support Features

  • Transport-Level Security
  • Message-Level Security
  • WS-*Interoperability
  • WS-*Transactions
  • Reliable Sessions / Reliable Messaging

Use if

  • Need WS-* level of interoperability

  • Don't need duplexing

  • Don't need federated security

Performance : Good

Communication

  • Request/Reply
  • One-Way


WSDualHttpBinding

Element Collection

System.ServiceModel.Channels.TransactionFlowBindingElement
System.ServiceModel.Channels.ReliableSessionBindingElement
System.ServiceModel.Channels.SymmetricSecurityBindingElement
System.ServiceModel.Channels.CompositeDuplexBindingElement
System.ServiceModel.Channels.OneWayBindingElement
System.ServiceModel.Channels.TextMessageEncodingBindingElement
System.ServiceModel.Channels.HttpTransportBindingElement

Support Features

  • Transport-Level Security
  • Message-Level Security
  • WS-*Interoperability
  • WS-*Transactions
  • Reliable Sessions / Reliable Messaging

Use if

  • Need WS-* level of interoperability

  • Need Duplexing

Performance : Good

Communication

  • Request/Reply
  • One-Way
  • Duplex


NetTcpBinding

Element Collection

System.ServiceModel.Channels.TransactionFlowBindingElement
System.ServiceModel.Channels.BinaryMessageEncodingBindingElement
System.ServiceModel.Channels.WindowsStreamSecurityBindingElement
System.ServiceModel.Channels.TcpTransportBindingElement

Support Features

  • Transport-Level Security
  • Message-Level Security
  • WS-*Transactions
  • Reliable Sessions / Reliable Messaging

Use if

  • Don't need interoperability

  • Is not local

  • Don't need queuing

  • Don't need peer networking

Performance : Better

Communication

  • Request/Reply
  • One-Way
  • Duplex


NetNamedPipeBinding

Element Collection

System.ServiceModel.Channels.TransactionFlowBindingElement
System.ServiceModel.Channels.BinaryMessageEncodingBindingElement
System.ServiceModel.Channels.WindowsStreamSecurityBindingElement
System.ServiceModel.Channels.NamedPipeTransportBindingElement

Support Features

  • Transport-Level Security
  • WS-*Transactions

Use if

  • Don't need interoperability

  • Is local

Performance : Best

Communication

  • Request/Reply
  • One-Way
  • Duplex


NetMsmqBinding

Element Collection

System.ServiceModel.Channels.BinaryMessageEncodingBindingElement
System.ServiceModel.Channels.MsmqTransportBindingElement

Support Features

  • Transport-Level Security
  • Message-Level Security
  • Durable Reliable Messaging

Use if

  • Don't need interoperability

  • Is not local

  • Need Queuing

  • Don't need legacy MSMQ

Performance : Better

Communication

  • One-Way


NetPeerTcpBinding

Element Collection

System.ServiceModel.Channels.PnrpPeerResolverBindingElement
System.ServiceModel.Channels.BinaryMessageEncodingBindingElement
System.ServiceModel.Channels.PeerTransportBindingElement

Support Features

  • Transport-Level Security

Use if

  • Don't need interoperability

  • Is not local

  • Need queuing

  • Need peer networking

Performance : Good

Communication

  • One-Way
  • Duplex


MsmqIntegrationBinding

Element Collection

System.ServiceModel.MsmqIntegration.MsmqIntegrationBindingElement

Support Features

  • Transport-Level Security
  • Durable Reliable Messaging

Use if

  • Don't need interoperability

  • Is not local

  • Need queuing

  • Need legacy MSMQ

Performance : Better

Communication

  • One-Way


WSFederationHttpBinding

Element Collection

System.ServiceModel.Channels.TransactionFlowBindingElement
System.ServiceModel.Channels.SymmetricSecurityBindingElement
System.ServiceModel.Channels.TextMessageEncodingBindingElement
System.ServiceModel.Channels.HttpTransportBindingElement

Support Features

  • Transport-Level Security
  • Message-Level Security
  • WS-*Interoperability
  • Reliable Sessions / Reliable Messaging

Use if

  • Need WS-* level of Interoperability

  • Don't need duplexing

  • Need federated security

Performance : Good

Communication

  • Request/Reply
  • One-Way

WS2007HttpBinding

Element Collection

System.ServiceModel.Channels.TransactionFlowBindingElement
System.ServiceModel.Channels.SymmetricSecurityBindingElement
System.ServiceModel.Channels.TextMessageEncodingBindingElement
System.ServiceModel.Channels.HttpTransportBindingElement

Support Features

  • Transport-Level Security
  • Message-Level Security
  • WS-*Interoperability
  • WS-*Transactions
  • Reliable Sessions / Reliable Messaging

Use if

  • Need WS-* level of Interoperability

  • Don't need duplexing

    Don't need federated security

Performance : Good

Communication

  • Request/Reply
  • One-Way


WS2007FederationHttpBinding

Element Collection

System.ServiceModel.Channels.TransactionFlowBindingElement
System.ServiceModel.Channels.SymmetricSecurityBindingElement
System.ServiceModel.Channels.TextMessageEncodingBindingElement
System.ServiceModel.Channels.HttpTransportBindingElement

Support Features

  • Transport-Level Security
  • Message-Level Security
  • WS-*Interoperability
  • Reliable Sessions / Reliable Messaging

Use if

  • Need WS-* level of Interoperability

  • Don't need duplexing

  • Need federated security

Performance : Good

Communication

  • Request/Reply
  • One-Way

Tuesday, March 04, 2008

WCF Error: HTTP could not register URL http://+:80/Temporary_Listen_Addresses// because TCP port 80 is being used by another application

So I was doing a dual http binding (WSDualHttpBinding) [page 53-54 in Essential Windows Communication Foundation For .Net Framework 3.5] when I got the error above. I doubled checked by running the svcutil and the wizard, but with no luck.

 

Found the solution at(http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2782499&SiteID=1)

 

Just need to include the clientBaseAddress attribute with a URI in the binding tag, since the default is using port 80.

 

<?xml version="1.0" encoding="utf-8"?>

<configuration>

   <system.serviceModel>

      <bindings>

         <wsDualHttpBinding>

               <binding   name="WSDualHttpBinding_IServerStock"

                        closeTimeout="00:01:00"

                        openTimeout="00:01:00"

                        receiveTimeout="00:10:00"

                        sendTimeout="00:01:00"

                        bypassProxyOnLocal="false"

                        transactionFlow="false"

                        hostNameComparisonMode="StrongWildcard"

                        maxBufferPoolSize="524288"

                        maxReceivedMessageSize="65536"

                        messageEncoding="Text"

                        textEncoding="utf-8"

                        useDefaultWebProxy="true"

                        clientBaseAddress="http://localhost:8001/WCFDuplexService">

               <readerQuotas   maxDepth="32"

                           maxStringContentLength="8192"

                           maxArrayLength="16384"

                           maxBytesPerRead="4096"

                           maxNameTableCharCount="16384" />

               <reliableSession   ordered="true"

                              inactivityTimeout="00:10:00" />

               <security   mode="Message">

                  <message   clientCredentialType="Windows"

                           negotiateServiceCredential="true"

                           algorithmSuite="Default" />

               </security>

            </binding>

         </wsDualHttpBinding>

      </bindings>

      <client>

         <endpoint   address="http://localhost:8000/WCFDuplexService"

                  binding="wsDualHttpBinding"

                  bindingConfiguration="WSDualHttpBinding_IServerStock"

                  contract="IServerStock"

                  name="WSDualHttpBinding_IServerStock">

            <identity>

               <userPrincipalName value="wandrus@mbsnav.com" />

            </identity>

         </endpoint>

      </client>

   </system.serviceModel>

</configuration>

Generating WCF's app.config and proxy class.

Currently reading: Essential Windows Communication Foundation for .Net Framework 3.5. The book is written for Visual Studio 2008, but I'm running on VS 2005 -- which means that I will have to run the svcutil.exe instead of using the wizard on some cases.  A quick reminder on how to do so:

 

Synchronous:

svcutil <Host Service IMetadataExchange address> -config:app.config -out:generatedProxy.cs

Example:

svcutil http://localhost:8000/EssentialWCF/mex -config:app.config -out:generatedProxy.cs

Asynchronous:

Example:

svcutil http://localhost:8000/EssentialWCF/mex -config:app.config -out:generatedProxy.cs /async

The svcutil.exe comes with Microsoft's SDK 6.0, for more information on this tool can be found at: http://msdn2.microsoft.com/en-us/library/aa347733.aspx

Thursday, February 28, 2008

Finding all variables and functions in Powershell

To get the list of all the variables or functions that have been declared and pre-existing:

ls variable:*

ls function:*

Note: You can change the wildcard(*) if you are looking for something more specific.

ls can obviously be substituted with dir or get-childitem if you are more familiar with those key-words.

Something Interesting:

If you want to look more closely at what the function is actually doing then you just need to type: $function:<name of function> like so:

$function:mkdir

which will return:

param([string] $name) New-Item $name -type directory

Monday, February 18, 2008

Lights Out Game (XAML)

I figured I should start learning Xaml, since it is used in WPF and Silverlight. I found this nice tutorial on Code Project: http://www.codeproject.com/KB/silverlight/SilverlightsOut.aspx on how to make a game as a introduction to silverlight.

I however first decided to make it a WPF application to try my hand at that first. One of the first things I learned was about the error:

The calling thread cannot access this object because a different thread owns it.

In the original he had it setup to use the HTMLtimer use the Tick event to move the background of stars like so:

System.Windows.Browser.HtmlTimer timer =

   new System.Windows.Browser.HtmlTimer();

timer.Interval = 1;

timer.Enabled = true;

timer.Tick += new EventHandler(timer_Tick);

 

I figured that I would need to change this to use the Timer class:

Timer timer_error = new Timer();

timer_error.Interval = 1.0;

timer_error.Enabled = true;

timer_error.Start();

timer_error.Elapsed += new ElapsedEventHandler(timer_error_Elapsed);

Then I got the error, which I then found:

"The .NET Framework 2.0 supports several timer objects, among them System.Timers.Timer, System.Threading.Timer, and System.Windows.Forms.Timer. However, these timers cannot be used directly by a WPF application since they run in a different thread than the one running the WPF UI."

(source: http://blogs.msdn.com/wpfsdk/archive/2006/06/23/Animating-Traffic-Map-Image-Data.aspx)

The solution is to use the System.Windows.Threading.DispatcherTimer like so:

System.Windows.Threading.DispatcherTimer timer  = new DispatcherTimer();  

timer.Interval = new TimeSpan(0,0,0,0,85);

timer.Start();

timer.Tick += new EventHandler(timer_Tick);

 

I also had to change little things through out the program to get it to work, such as:

void timer_Tick(object sender, EventArgs e)

{

   double currentLeft = (double)background.GetValue(Canvas.LeftProperty);

   // get current left position of background

   if (currentLeft <= 0)

   {

      // move background pixels over

      background.SetValue(Canvas.LeftProperty, currentLeft + 2);

   }

   else

   {

     // reset backgrounds position

     background.SetValue(Canvas.LeftProperty, currentLeft - 340); 

     //instead of

    background.SetValue(Canvas.LeftProperty, -340);

   }

}

 

I still have lots to learn. Currently I'm still adding more functionality to the app. I'm thinking that I should add a "restart" and make a auto-solve.

Technorati Tags: ,,,

Thursday, February 07, 2008

Signing your PowerShell script

I got an error "File ... cannot be loaded. The file ... is not digitally signed. The script will not execute on the system. Please see "get-help about_signing" for more details.." when starting PowerShell today, after installing the new Windows SDK.

So, I scrambled to find a solution on how to make a certificate and sign it to a PowerShell script. On Scott Hanselman's Blog: http://www.hanselman.com/blog/SigningPowerShellScripts.aspx 
he shows a easy to follow step by step on how to do so.

 

P.S. 

I also had to rest my Home directory.

Tuesday, February 05, 2008

Running Powershell in C#

Found this interesting article on Code Project the other day, on how to run PowerShell scripts from within C#.

http://www.codeproject.com/KB/cs/HowToRunPowerShell.aspx

Prerequisites:

Code:

Add a reference System.Management.Automation from C:\Program Files\Reference Assemblies\Microsoft\WindowsPowerShell\v1.0

Import

using System.Management.Automation;

using System.Collections.ObjectModel;

using System.Management.Automation.Runspaces;

 

Note: I did have to edit his function, to get it to work for me, and also added a try/catch to spew out the error.

private string RunScript(string scriptText)

{

    // create Powershell runspace

    Runspace runspace = RunspaceFactory.CreateRunspace();

 

    // open it

    runspace.Open();

 

   // create a pipeline and feed it the script text

   Pipeline pipeline = runspace.CreatePipeline();

   pipeline.Commands.AddScript(scriptText);

 

   // add an extra command to transform the script output objects into nicely formatted strings

   // remove this line to get the actual objects that the script returns. For example, the script

   // "Get-Process" returns a collection of System.Diagnostics.Process instances.

   pipeline.Commands.Add("Out-String");

 

   // execute the script

   Collection<PSObject> results = new Collection<PSObject>();

   try

   {

      results = pipeline.Invoke();

   }

   catch (Exception ex)

   {

      results.Add(new PSObject((object)ex.Message));

   }

 

 

   // close the runspace

   runspace.Close();

 

   // convert the script result into a single string

   StringBuilder stringBuilder = new StringBuilder();

   foreach (PSObject obj in results)

   {

       stringBuilder.AppendLine(obj.ToString());

   }

 

   return stringBuilder.ToString();

}

 

Technorati Tags: ,,,

Monday, January 28, 2008

Some Unix commands for PowerShell

I've been playing with PowerShell a little and decided to make some Unix commands that were not ported over.

 

Command Function
whoami

function whoami
{  [System.Security.Principal.WindowsIdentity]::GetCurrent().Name       
}

~ (works better if it was not an alias)

function ~
{
   $home
}

mkdir

function mkdir([string] $name)
{
   New-Item $name -type directory
}

touch

function touch([string] $path = ".\", [string] $name)
{
   $file = $path + $name
   $result = test-path $file
   if($result -eq $true)
   {
      ##"Changing LastWriteTime"
      gci $file | foreach{$_.lastwritetime = $(get-date)}
   }
   else
   {
      ##"Creating new file"
      new-item $file -type file
   }
}

find (still currently working on this one. I need to implement wild cards)

function find([string] $name = "", [string] $ext = "", [string] $drive = "C:")
{
   $computer = "."
   if($name -ne "")
   {
      $files = get-wmiobject -computer $computer cim_datafile -filter "FileName = '$name'"
   }
   if($ext -ne "")
   {
      $files = get-wmiobject -computer $computer cim_datafile -filter "Extension = '$ext'"
   }

   $files | add-member --type PropertySet "FindInfo" ([string[]]("Name","FileType","Extension","CreationDate")) -force
   $files | select FindInfo | ft -wrap
}

 

I figured re-writing Unix commands might help me learn powershell. I'm going to use http://en.wikibooks.org/wiki/Guide_to_UNIX/Commands as source of basic Unix commands to implement.

Wednesday, January 23, 2008

LINQ's "Outer Variables in a Loop"

Started using LINQPad and going through the "C# 3.0 in a Nutshell" tutorial that came with the program.

Then I ran the following script from "Chapter 8 - LINQ Queries/Deferred Execution/Outer Variables in a Loop" :

IEnumerable<char> query = "Not what you might expect";

 

query = query.Where (c => c != 'a');

query = query.Where (c => c != 'e');

query = query.Where (c => c != 'i');

query = query.Where (c => c != 'o');

query = query.Where (c => c != 'u');

 

new string (query.ToArray()).Dump ("All vowels are stripped, as you'd expect.");

 

query = "Not what you might expect";

 

foreach (char vowel in "aeiou")

{

    query = query.Where (c => c != vowel);

}

 

new string (query.ToArray()).Dump ("Notice that only the 'u' is stripped!");

 

with the following results:

▪ All vowels are stripped, as you'd expect.

Nt wht y mght xpct

▪ Notice that only the 'u' is stripped!

Not what yo might expect

 

 

Very confusing hmmm..., here is the solution (add a temp variable):

foreach (char vowel in "aeiou")

{

   char temp = vowel;

   query = query.Where (c => c != temp);

}

 

Reason:

"The temporary variable in the loop is required to avoid the outer variable trap, where the same variable is captured for each iteration of the foreach loop." -- C# 3.0 IN A NUTSHELL http://www.albahari.com/nutshell/predicatebuilder.html

 

I would like a more detail explanation of this.

I'm guessing that the variable vowel goes through some sort of reset each time the foreach loop is ran, so in the end you will be actually checking

query = query.Where (c => c != 'u');

query = query.Where (c => c != 'u');

query = query.Where (c => c != 'u');

query = query.Where (c => c != 'u');

query = query.Where (c => c != 'u');

 

instead of

query = query.Where (c => c != 'a');

query = query.Where (c => c != 'e');

query = query.Where (c => c != 'i');

query = query.Where (c => c != 'o');

query = query.Where (c => c != 'u');