NotNullOrEmpty

Android Training videos

2011-11-13T18:16:00.000-08:00

These are the best Android training videos i have found so far:

http://marakana.com/techtv/android_bootcamp_screencast_series.html

These videos come from Marakana's 5-Day Android Bootcamp Training Course that Marko Gargenta taught in San Jose, CA earlier this year.

Installing APk file into your emulator

2011-11-09T08:01:00.000-08:00

You can use File explorer view in Eclipse to drag files there to install into Android.

If you dont want to do it with Eclipse, you can do it in command line. You need to use "adb.exe" to do that. First add "...android-sdk/platform-tools/ to your path from advanced computer settings, so you can call adb from command line.

Type "cmd" add search or run screen to launch command editor. If you have the path to adb, you can call that. Start the service: adb start-server

If you see some error, you may need to manually delete old running adb.exe from your task manager.

To install file after adb daemon running, you can type "adb install C:\yourfolder\yourfile.apk". If you previously installed this, it does not replace the old app. You need to delete that from emulator. You can use menu options to go to settings->apps>your app->uninstall.

Hope, this helps you to use android emulator

Rendering pdf in android app

2011-11-04T19:30:00.000-07:00

I was expecting this to be easy, but it got some complications. Android does not have native rendering for pdf,so you have different options. I decided to load the pdf in async task and then open the available pdf application. One nice feature with the Android framework is "the intents". You can launch application from another application with those intents. They have very nice way of managing different instances of same app. You can read more about this here.

You need to set manifest file first to enable file permissions.

Here is my AndroidManifest.xml:

<?xml version="1.0" encoding="utf-8"?>
<manifest xmlns:android="http://schemas.android.com/apk/res/android"
    package="com.android.demo"
    android:versionCode="1"
    android:versionName="1.0" >

    <uses-sdk android:minSdkVersion="14" />

    <application
        android:icon="@drawable/ic_launcher"
        android:label="@string/app_name" >
        <activity
            android:label="@string/app_name"
            android:name=".PdfprintdemoActivity" >
            <intent-filter >
                <action android:name="android.intent.action.MAIN" />

                <category android:name="android.intent.category.LAUNCHER" />
            </intent-filter>
        </activity>
    </application>

    <uses-permission android:name="android.permission.WRITE_EXTERNAL_STORAGE" >
    </uses-permission>
    <uses-permission android:name="android.permission.INTERNET" >
    </uses-permission>
    <uses-permission android:name="android.permission.ACCESS_NETWORK_STATE" >
    </uses-permission>
    <uses-permission android:name="android.permission.READ_PHONE_STATE" >
    </uses-permission>
</manifest>

I put everything inside one activity to make this example simple. There are some important elements that need to be explained.

OnCreate method: It calls this method when creating the view. You should call the base method first then do your overrides.

/** Called when the activity is first created. */
    @Override
    public void onCreate(Bundle savedInstanceState) {
        //call base
        super.onCreate(savedInstanceState);
        //set layout main.xml to be layout for this activity
        setContentView(R.layout.main);
        //create references to the controls
        startBtn = (Button) findViewById(R.id.startBtn);
        //attach listener in code or you can do that in layout file.
        startBtn.setOnClickListener(new OnClickListener() {
            public void onClick(View v) {
                startDownload();
            }
        });         
    }

I used Async task to handle the download. It helps you to performasynchronous work on your user interface without halting the UI. You don't needto handle threads by yourself.

doInBackground() executes automatically on a worker thread if you call "execute" method from this class.
onPreExecute(), onPostExecute(), onProgressUpdate() are all invoked on the UI thread.
You can set the return value from the "doInBackround()" as input to onPostExecute()

You can access publishProgress() from "doInBackround()" method to call "onProgressUpdate()"

We are setting the dialog at onPreExecute():

@Override
        protected void onPreExecute() {
            super.onPreExecute();
            showDialog(DIALOG_DOWNLOAD_PROGRESS);
        }

        //do actual download
        @Override
        protected String doInBackground(String... aurl) {
            int count;
            String fullpath = "";
            try {
                URL url = new URL(aurl[0]);
                URLConnection conexion = url.openConnection();
                conexion.connect();
                int lenghtOfFile = conexion.getContentLength();
                Log.d("ANDRO_ASYNC", "Lenght of file: " + lenghtOfFile);
                //Get directory
                File root = Environment.getExternalStorageDirectory();
                InputStream input = new BufferedInputStream(url.openStream());
                OutputStream output =  new FileOutputStream(new File(root,
                        aurl[1]));
                byte data[] = new byte[1024];
                fullpath = root.getAbsolutePath()+"/"+aurl[1];
                Log.d("ANDRO_ASYNC","path:"+fullpath);
                long total = 0;
                while ((count = input.read(data)) != -1) {
                    total += count;
                    publishProgress("" + (int) ((total * 100) / lenghtOfFile));
                    output.write(data, 0, count);
                }
                output.flush();
                output.close();
                input.close();
                Log.d("ANDRO_ASYNC", "File closed1: " + lenghtOfFile);
            } catch (Exception e) {
                Log.d("error",e.getMessage());
            }
            return fullpath;
        }

/*We use this method to update progress bar*/
        protected void onProgressUpdate(String... progress) {
            Log.d("ANDRO_ASYNC", progress[0]);
            mProgressDialogWindow.setProgress(Integer.parseInt(progress[0]));
        }

        
        /* This method takes the return param from doinbackground call as input*/
        @Override
        protected void onPostExecute(String linkname) {
            mProgressDialogWindow.setMessage("Finished");
            dismissDialog(DIALOG_DOWNLOAD_PROGRESS);
            Log.d("onPostExecute","Send:"+linkname);
            OpenFile(linkname);
        }
        
        /*We are firing intent to open this file with avaialble pdf viewer*/
        protected void OpenFile(String linkname)
        {
            File file = new File(linkname);

            if (file.exists()) {
                Log.d("OpenFile","Exists:"+linkname);
                Uri path = Uri.fromFile(file);
                Intent intent = new Intent(Intent.ACTION_VIEW);
                intent.setDataAndType(path, "application/pdf");
                intent.setFlags(Intent.FLAG_ACTIVITY_CLEAR_TOP);

                try {
                    startActivity(intent);
                } 
                catch (ActivityNotFoundException e) {
                    Toast.makeText(PdfprintdemoActivity.this, 
                        "No Application Available to View PDF", 
                        Toast.LENGTH_SHORT).show();
                }
            }else
            {
                Log.d("OpenFile","DOES NOT Exists:"+linkname);
            }
        }

Gitignore settings for Android

2011-10-30T19:23:00.000-07:00

# ignore built application files
*.apk*.ap_


# Java class files
*.class

# VM files
*.dex
# dont need generated files
bin/
gen/
# Local configuration files
local.properties

#You can copy those into gitignore.txt and then save as ".gitignore" in "all files format".

Ruby on Rails 3.0

2011-10-24T18:58:00.000-07:00

I have created a webpage in Ruby on Rails after going through all those server setup at slicehost.com and all those plugins. I kinda like the CMS editor, but don't like plugin hell!

I installed one file management plugin to upload css and js files,but it was so stupid to cut off after some characters. After checking their code, i realized that they were using "Get" method to send data. I learned hard way that you should not trust those weird plugins they got. They got so many different versions and it needs to be compatible. Besides all those, I really like the scaffolding, conventions in modelling, and different view engine options. It is definitely ahead of the current Asp.net MVC 3 or 4 with all those features. You may know that recent asp.net mvc features and code sharing with nuget packages are copy of RoR. You can do scaffolding with T4 templates and do the data modeling style coding with new Entity framework code first approach. They are still weak on the data migration part, but it is coming very strong.

If you setup a server for your RoR project before, you may know all different options and little things to do. If you have limited memory on your slice, it takes some time to restart worker threads after inactivity period. They are not very efficient. I did bunch different things to make it work. Good thing is you can find everything about to start a server from scratch. You can check articles at Slicehost.com for different server options.

Anyway, What do you think about Ruby on Rails and Asp.Net MVC?

Android ADT 14 and Android Unknown Command 'crunch'

2011-10-24T18:47:00.000-07:00

I just setup a sample project to check out android development, but got weird error related to 'crunch' command. How is that possible with fresh sdk tools and all included eclipse package setup. It was very discouraging, but i decide to figure out a way to at least run some demos and see "hello world" in different platform versions.

Solution: You need to install this ADT 14. Yes. you need to uninstall all those old Android SDK and try again:)
I checked all necesary items in the Eclipse window. After downloading all those libraries, you should setup the SDK directory for android.

At the end, you should see references to correct version of ADT. "Hello world" will work fine now. You can create new projects from sample Android apps. It really helps to learn overall design.

How to create shortcut for namespaces in your view files for ASP?

2010-11-25T06:24:00.000-08:00

You can import namespaces at the beginning of the page and reference them directly.

<%@ Import Namespace="myapp.Controllers" %>

If you have a Product controller in myapp, you can reference this directly without writing myapp.Controllers.Product.

How to trigger event for Truncate action in MS SQL

2010-11-19T11:21:00.000-08:00

NOTE:

?????
I guess it is not possible to trigger event for Truncate,but prevent it.

From msdn:
TRUNCATE TABLE cannot activate a trigger because the operation does not log individual row deletions. We can prevent truncate action,if we create a view WITH SCHEMABINDING.

If we don't use truncate on the application level, we will be dealing with only delete command. It is easy to prevent that with Instead Of Trigger.

ASP MVC IIS not running

2010-10-29T06:57:00.000-07:00

Hey,

I had the same problem on IIS 7.5 with MVC 1. I fixed the problem and listed some steps here:

Figure-1:Install other IIS options

Read other normal IIS config steps

If you dont see Asp.Net tab in config option, you need to register asp.net

%windir%\Microsoft.NET\Framework\v2.0.50727\aspnet_regiis.exe -i

You need to check add-on features to install some other components to make IIS work. Installing them solves most of the problem. Redirect is off by default. Go to control panel ->programs-> Windows components part as show on Figure-1. If you don't see your aspx pages, it might be missing .net integration.
Create your app and add simple html, asp pages for testing

if it is showing default app, you need to remove that application
You may need to start and stop application to make it work

Change app pool identity to local system and give permissions at your database to access your tables for this user
Allow all users for .net authorization
It will work after these steps. If not check these:

You may need to check connection strings to make sure you are connecting to the right sql instance
Permission problem: You may need to give permission to web folders for application identity. If you change that to something else, add that user to list and give permissions.

notes about xml processing in sql server

2010-08-07T20:09:00.000-07:00

----working with xml data
--retrieving relational data as xml

--passing data a xml

--storing and querying actual
use AdventureWorks;

select
c.CustomerID,
c.AccountNumber,
c.CustomerType

from sales.Customer as c
where c.CustomerID in (1,2) ;

select soh.CustomerID,
soh.SalesOrderID,
soh.OrderDate,
soh.ShipDate
from Sales.SalesOrderHeader as soh
where soh.CustomerID in (1,2);

--instead use join query
select c.customerid , c.accountnumber, c.customertype,
soh.SalesPersonID, soh.OrderDate, soh.ShipDate

from
Sales.Customer as c left outer join

Sales.SalesOrderHeader as soh on soh.CustomerID = c.CustomerID
where c.CustomerID in (1,2)

--xml
select c.customerid as "@Id"
, c.accountnumber as "@AccountNumber"
, c.customertype as "@Type"
,(
SELECT
soh.SalesOrderID as "@Id"
,soh.OrderDate as "@OrderDate"
, soh.ShipDate as "@ShipDate"
from Sales.SalesOrderHeader as soh
where soh.CustomerID = c.CustomerID
for XML Path('Order') , TYPE
) as "Orders"
from
Sales.Customer as c
where c.CustomerID in (1,2)
FOR XML PATH('Customer'), ROOT('Customers');

---
USe AdventureWorks;
go

select
p.ProductID as "@Id",
p.Name as "Info/@Name",
p.ListPrice as "Info/@Listprice",
(
select
sod.SalesOrderID as "@Orderid",
sod.OrderQty as "@QTY",
soh.OrderDate as "@date",
soh.CustomerID as "@customerid"

from Sales.SalesOrderHeader soh
inner join Sales.SalesOrderDetail as sod
on sod.SalesOrderID = soh.SalesOrderID
where sod.ProductID = p.ProductID
for XML path('Ordersub'), TYPE
)
from Production.Product as p
where p.ProductID = 707
for xml path('Product');

---master order and details
select
p.ProductID as "@Id",
p.Name as "Info/@Name",
p.ListPrice as "Info/@Listprice",
(
select
sod.SalesOrderID as "@Orderid",
sod.OrderQty as "@QTY",
soh.OrderDate as "@date",
soh.CustomerID as "@customerid"

from Sales.SalesOrderHeader soh
inner join Sales.SalesOrderDetail as sod
on sod.SalesOrderID = soh.SalesOrderID
where sod.ProductID = p.ProductID
for XML path('Ordersub'), TYPE
)
from Sales.SalesOrderHeader soh
where p.ProductID = 707
for xml path('Product');

--list top 10 orders with amount max

--
select top 1
sh.SalesOrderID as "@ID",
sh.OrderDate as "@OrderDate",
sh.ShipDate as "@ShipDate",
sh.Status as "@Status",
sh.CustomerID as "@CustomerID",
sh.SalesPersonID as "@SalesPersonID",
sh.Totaldue as "@TotalDue",
(select top 5
sod.ProductID,
sod.OrderQty,
sod.LineTotal
from
Sales.SalesOrderDetail sod
where sod.SalesOrderID = sh.SalesOrderID
order by sod.LineTotal desc
for XML path('Ordersub'), TYPE
)
from Sales.SalesOrderHeader sh
order by sh.TotalDue desc
for xml path('Sales') ,root('SalesTop10')

;

--read test
Declare @myxml XML

set @myxml = N'



   969
   21
   26159.208075


   966
   16
   19930.825200


   954
   16
   12206.438400


   892
   23
   12064.488425


   967
   6
   8582.652000


';

select @myxml.query('

{for $o in /SalesTop10/Sales/Ordersub
where $o/OrderQty > 16
return
  }

')

/* output




*/

---all of them in properties
select top 1
sh.SalesOrderID as "@ID",
sh.OrderDate as "@OrderDate",
sh.ShipDate as "@ShipDate",
sh.Status as "@Status",
sh.CustomerID as "@CustomerID",
sh.SalesPersonID as "@SalesPersonID",
sh.Totaldue as "@TotalDue",
(select top 5
sod.ProductID as "@ProductID",
sod.OrderQty as "@QTY",
sod.LineTotal as "LineTotal"
from
Sales.SalesOrderDetail sod
where sod.SalesOrderID = sh.SalesOrderID
order by sod.LineTotal desc
for XML path('Ordersub'), TYPE
)
from Sales.SalesOrderHeader sh
order by sh.TotalDue desc
for xml path('Sales') ,root('SalesTop1')

;

declare @otherxml XML
set @otherxml = N'



   26159.208075


   19930.825200


   12206.438400


   12064.488425


   8582.652000


';

---reshape results with xquery

SELECT (SELECT top 10 * FROM Sales.Customer as Customers FOR XML AUTO,type ).query(
'{
   for $c in /Customers
   return

}')

---
;
create schema TestXml;
go

create table TestXml.Messages
(
MessageID int identity primary key,
FromUser nvarchar(50) not null,
Message nvarchar(max) not null,
CreatedDateTime datetime2 not null default sysdatetime()
);

--proc to insert data from xml input

create procedure TestXml.spMessageAddMany
@Message XML
as
Begin
set nocount on;
Insert TestXml.Messages (FromUser, Message)
Select
tab.col.value('@FromUser', 'NVARCHAR(50)'),
tab.col.value('text()[1]', 'NVARCHAR(max)')
from @Message.nodes('/Messages/Message') as tab(col);
end

---now insert some xml messages

EXEC TestXml.spMessageAddMany @Message = N'

Naber nasil?
Naber nasil2?
Naber nasil3?
';

Select * from TestXml.Messages;

--working with xml stored in xml variable or column
EXIST METHOd

select top 1 Demographics
from Sales.Individual

with xmlnamespaces(default
'http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey')
select COUNT(*)
from Sales.Individual
where Demographics.exist(
'/IndividualSurvey/Occupation') = 1

with xmlnamespaces(default
'http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey')
select COUNT(*)
from Sales.Individual
where Demographics.exist(
'/IndividualSurvey/TotalPurchaseYTD[xs:decimal(.) > 8000]') = 1

with xmlnamespaces(default
'http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey')
select COUNT(*)
from Sales.Individual
where Demographics.exist(
'/IndividualSurvey/TotalPurchaseYTD[xs:decimal(.) > 8000]') = 1

---Extract Properties from XML Variable
DECLARE @xVar XML
SET @xVar =
'

Michael

Howard

David

LeBlanc

2David

2LeBlanc

3LeBlanc

39.99
'

SELECT nref.value('first-name[1]', 'nvarchar(50)') FirstName, --get first element [1]
   nref.value('last-name[1]', 'nvarchar(50)') LastName
FROM @xVar.nodes('//author') AS R(nref)
WHERE nref.exist('.[first-name != "David"]') = 1

--value method

with xmlnamespaces(default
'http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey')
select SUM(demographics.value('count(/IndividualSurvey)', 'INT')) AS NumberofISurveys
from Sales.Individual
--TotalPurchaseYTD
with xmlnamespaces(default
'http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey')
select SUM(demographics.value('sum(/IndividualSurvey/TotalPurchaseYTD)', 'decimal(10,2)')) AS NumberofISurveys
from Sales.Individual

--avg TotalChildren
with xmlnamespaces(default
'http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey')
select (demographics.value('sum(/IndividualSurvey/TotalChildren)', 'decimal(10,2)')) AS NumberofISurveys
from Sales.Individual

---NODES

declare @orders xml;
set @orders = N'

a
b
c
';
SELECT nref.value('Order[1]', 'nvarchar(50)') rowOrder --get first element [1]
,nref.value('Product[1]', 'nvarchar(50)') rowOrderProduct
FROM @orders.nodes('//Orders') AS R(nref)

DECLARE @T varchar(max)
SET @T =
'


















'

DECLARE @X xml

SET @X = CAST(@T as xml)
SELECT
   YY.t.value('(@FieldRowId)[1]', 'int') as FieldID,
   YY.t.value('(Items/Item/@Name)[1]', 'varchar(max)') as "Name",
   YY.t.value('(Attributes/Attribute/@ID)[1]', 'int') as AttributeID
FROM @X.nodes('/root/Field') as YY(t)

How to call stored procs from User Defined Functions (udf) in SQL Server

2010-07-17T18:56:00.000-07:00

If you want to use stored procs from your functions , it is not possible with simple "exec sp_myproc".

You can use openquery to call your server as linked server and execute stored procs.
You need to run some commands before running openquery.

Step-1: enable openquery
---open query to have access to stored procs within functions
--add your server as linked server
sp_addlinkedserver @server = 'LOCALSERVER', @srvproduct = '', @provider = 'SQLOLEDB', @datasrc = @@servername

Step-2: enable data access

EXEC sp_serveroption 'OMER-PC', 'DATA ACCESS', TRUE

---Check access

Step-3: Select * from openquery(LOCALSERVER, 'Exec sp_who')

This query should return some results. Openquery does not work for every stored proc. You need to look into the details.

Some simple examples:

1)--inline table function calling stored proc
-----------------------------------------------------------
create FUNCTION Test.udfCallProcInline()
returns table
AS
return
select * from openquery(LOCALSERVER, 'Exec sys.sp_tables')
GO

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

You can call your function as normal udfs:

select * from Test.udfCallProcInline()

It is also possible to make joins and select columns from the results. You don't need to know column data types here.

2-)-multi statement function

--you may need to check column data types here

CREATE FUNCTION Test.udfCallAProc2()

returns @mytable table

(table_qualifier varchar(50),

table_owner varchar(50),

table_name varchar(50),

table_type varchar(50),

remarks varchar(255)

)

begin

insert @mytable

select * from openquery(LOCALSERVER, 'Exec sys.sp_tables')

return

end

select * from Test.udfCallAProc2()

----More examples....later...

Recommended Software Related Tools

2010-06-22T18:00:00.000-07:00

Source Control and Bug track:

http://www.mantisbt.org/ , Mantis: Open source bug tracking system for multiple projects. It is free!

http://git-scm.com/ , Fast version control system. It is free and open source.

http://subversion.tigris.org/ , Subversion: Open source code repository and code control. Multiplatform and feature rich.

Windows Administration:

http://www.SysInternals.com, Microsoft acquired Sysinternals in July, 2006. Whether you’re an IT Pro or a developer, you’ll find Sysinternals utilities to help you manage, troubleshoot and diagnose your Windows systems and applications.

http://www.logparser.com/

Web Development:

FireBug, Excellent addon tool for Fire Fox. You should have this.

NotePad++ Very good geneal purpose text editor.

WAMP Apache, MySQL, PHP on Windows. Single install with utilities if you don't want to mess with configs.

jQuery: Excellent javascript library. Check also JQGRID and JQuery UI tools.

Desktop Apps:

7-Zip, file compresison, multiple formats, open source.

FileZilla, my FTP

KLite Codec Pack, codecs.

Portable Apps, you can carry your favorite portable programs in a USB drive. Use any USB flash drive, portable hard drive, iPod/MP3 player, etc

Interesting:

http://eyeos.org/ open source web OS. There are others out there, but this is Open Source and apears stable.

Content Management Systems:

CMS Made Simple : It is really simple and powerful. You can add code snippets inside layouts and it is evaluated in run time. It has admin menu to change layouts and styles. you can also edit them. You can install different Modules or develop your own module. It has good level of supporting documentation.

FREE

2010-06-10T06:43:00.000-07:00

FREE ADVICE!

What happens if you delete default database for a user in sql server?
--You will get an error msg as : Cannot open user default database
--Easy solution:
1- Click options in login window

2-Set database to master or model

Change database for connection

3-This will fix your login problem. Next step is to find your default database and change that. It is normally located in security> logins> your loginname

Find your Login settings in security tab

4-Change your default database if it is not changed.

Now, you can fix the problem with your default database. Here is the cause of the problem from Mic:

The user default database is unavailable at the time of connection. It is possible that the database:

Is in suspect mode.
No longer exists.
Is in single user mode and the only available connection is already being used by someone else or by something else.
Has been detached.
Has been set to the RESTRICTED_USER state.
Is offline.
Is set to emergency status.
Does not have the login account mapped to a user or the user has been denied access.
Is part of a database mirror.

Additionally, the login account may be a member of multiple groups and the default database for one of those groups is unavailable at the time of connection.-->http://support.microsoft.com/kb/307864

Eclipse Php Zend Debugger

2010-05-15T22:50:00.000-07:00

How to Debug PhP Code in ECLIPSE?
If you want to see variable values with mouse over and change them to see the effect in your program without echo statements, you need debugging. You can follow the steps below to set up a php editor with debugging. You can email me for questions.

Install apache server
Install Php from www.php.net

Select all necessary items such as mysql, openssl, dba

Download ZEND plugins for eclipse

Copy zenddebugger.dll to PHP directory
Add these to your php.ini file

zend_extension_ts=.\ZendDebugger.dll

zend_debugger.allow_hosts= 127.0.0.1

zend_debugger.expose_remotely=always

You may want to run phpinfo() script to see your actual php.ini file. You may have one copy at C:Windows directory.

4. Download Eclipse php editor with debugger. It will show you xdebug and zend for debugging.

5. Setup your work directory

6. Configure your debug options: it may launch at http://localhost. This is fine for the default settings.

7. Restart apache server after changing php.ini file

8. When you run in debug mode, Eclipse will ask you to load in perspective show detail debug screen

9. If you get error about php.ini directives, you should check you php.ini file. You can add phpinfo(); command line to list all properties of PHP. It will show you the address of php.ini file.

Summary: If you see zend debugger properties in your PHP property list, you have the debugging. Disable Xdebug if you have that. Zend and Xdebug may not work properly at the same time.

I am happy to have debugging in php. I guess no more "echo this echo that"

Best Regards,

ASP Hosting

2010-05-13T21:47:00.000-07:00

I am looking for asp hosting. Most of the hosting providers are offering similar services.
Check the list:
http://www.hosting-review.com/hosting-directory/top-10-lists/Top-10-ASP-Hosting-Companies.shtml

IX Web Hosting looks good with the low price tag.

1-How much does it cost to use asp hosting with sql server 2008?
--It looks like with 36 month plan, they charge around 10$ /month at IX web hosting.
2-Reliability ?

3-Support ?

4-Speed

5-Bandwidth limits

I will look into more details about this...Let me know your favorite Hosting provider for Windows!!!

Another info: http://www.dotnetpanel.com/ is open source now. You can download that and use it for hosting management.

Transaction and Locks in MS SQL 2008

2010-04-27T06:24:00.000-07:00

Today 's topic is TRANSACTION operations. You can test many concepts with running multiple connections in SQL Server management studio. This article will talk about the type of locks, consistency and update operations. if you have large tables, some data modification statements may take time and block others. You will learn how to read data with different level of consistency and durability.

Transactions have four basic properties: All-Or-Nothing, Consistency,Isolation, and Durability.
All-Or-Nothing(Atomicity): You should keep transactions compact and one unit operation. If the system fails before committing the code, system will rollback the changes automatically. Some errors are not severe enough for automatic rollback such as lock expiration time-out, key violation. You can add your Try/Catch blocks to decide for a rollback. You can also check @@TRANCOUNT to find out if there is any open transactions.
print 'Current Trans Ccount: ' + cast( @@TRANCOUNT as varchar(50))
Consistency: At the end of the transaction, you may have your new and valid data accepted or rollback to original state.You can control the consistency of the data with different lock levels at resources.
Isolation: Before the data is committed or rollback to the original state , It is kept in isolation and not accessible to other transactions. You can control the lock level for each resource in the database. Type of Lock status defines your consistency for different levels of acces. You will see in the examples.
Durability: The transaction is considered durable after commit instruction is written to the transaction log on disk. When system restarts it can check transaction logs, to apply committed changes if it is not recorded in data portion.

Each individual INSERT, DELETE, UPDATE statement is an individual transaction and committed automatically without rollback. You can also use those statements within begin transaction and commit transaction to check for rollback state.

--TIP: ROLLBACK statement may return the data to its previous state, but it may not reset your identity column seed values.

LOCKS: Lock settings help you to modify the resources with the level of customized consistency from your transaction. When you try to modify data, your transaction asks for an exclusive lock on the data resource, if it is granted, it will keep the lock until the end of transaction. Only one exclusive lock is available at that resource and other request will wait for the end of the transaction. When you read the data, server asks for a shared lock and it does not place shared lock over exclusive. We will go into details of these locks, but it will help you if you can see list of locks in your database.

You can lock Row, Page, Object, Database and other type of resources. Different than your main lock type, SQL server add intent locks at higher level than your locked resource. If your transaction is using exclusive lock at the row level, intent locks will be placed at the page level and the table level.
You can query the current locks and waiting request at your server with the following query:
--QUERY for all sessions
SELECT
request_session_id AS spid,
resource_type AS restype,
resource_database_id AS dbid,
DB_NAME(resource_database_id) as DBNAME,
resource_description AS res,
resource_associated_entity_id AS resid,
CASE
when resource_type = 'OBJECT' then OBJECT_NAME( resource_associated_entity_id)
ELSE 'N/A'
END as ObjectName,
request_mode AS mode,
request_status AS status
FROM sys.dm_tran_locks
order by spid, restype;

You don't see any exclusive request right now in the figure above. We have Shared locks granted. If you run the following query at your Adventureworks Db, you will see the list of locks at different levels. < I assume you have that from MS SQL Samples.>

BEGIN TRAN;

UPDATE Production.Product
SET ListPrice = 0.15
where productid =4

--Commit tran; --Commit this later. We want to see the current request.

print 'Current Trans Ccount: ' + cast( @@TRANCOUNT as varchar(50))

Figure: Our uncommitted transaction is showing X (Exclusive) lock at the row key.

Since we have foreign key relations to other tables, server will automatically add shared intent (IS) locks to those tables. We have the Exclusive intent (IX) lock at the Product table and Page resource. Our row key has an exclusive lock (X) from SPID-56. This SPID is at the bottom of the query page. Each query page will have an unique SPID. If you run another query from a new query window, we can exercise wait blocks.

Run this query at another query page and check waiting processes again with sys.dm_tran_locks:
SELECT * from production.product
where ProductID = 4;

Here is our waiting list:

My new query page has SPID of 59 and previous page has SPID of 56. You may different numbers in your system. Process-59 is waiting for the resource to lock for Shared mode. Since process-56 is locked this row for an EXCLUSIVE lock, Shared lock will not be applied over that.
In simple terms, you don't get shared lock when another transaction is holding an exclusive lock. You can have multiple shared locks as you might guess. If you really want to read this row before uncommitted, you can set isolation level of READ UNCOMMITTED.
Open another query window and type this:
SET transaction isolation level read uncommitted;
SELECT * from production.product
where ProductID = 4
This query will read it immediately without waiting for the commitment of the transaction. If you rollback the transaction or do some other modifications to this data at the same transaction, other query result will be INCONSISTENT.
NOLOCK is same as specifiying "READ UNCOMMITTED". If you run this query, you will get uncommited data without waiting for the resource:
SELECT * from production.product with (nolock )
where ProductID = 4

You can specify READ COMMITTED, which is default for select statements, to read with shared lock. It will show you the data after update statements.

Duration of this lock is important. Reader use shared lock for "READ COMMITTED" state until it is finished with this reosurce. It is not going to keep this lock until the end of the transaction. You may get inconsistent results if another transaction modify your data in between your reads, becuase you are not keeping shared lock untill the end of your transaction.

If you want to read data and make sure that others do not change the values in between your reads, you can specify "REPEATABLE READ" isolation level.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRAN;
select * from production.product
where ProductID = 4

Other users may insert rows even if you have "REPEATABLE READ" lock. This may change your reader output and result in inconsistent data. You can use SERIALIZABLE LOCK to prevent that.

SERIALIZABLE LOCK: You can lock row insert operations with serializable lock. this will prevent insert operations on your resource while you were reading it with this lock option.

Snapshot Isolation Levels

If you enable any of the snapshot based isolation levels, DELETE and UPDATE statements will copy the row before the change at resource to the "tempdb". SELECT statements will have better performance , but Modifications will be slower. Readers using snapshot level isolations will not use shared locks, so they don't need to wait for the resource. You need to turn on this option if you want to use it.

With the Snapshot isolation, reader will check the last committed version of the resource, so it is doing row versioning.

When you use Snapshot level access, you request last committed version of the resource.
SET TRANSACTION ISOLATION LEVEL SNAPSOT;
BEGIN TRAN;
select * from production.product
where ProductID = 4

This query will checkout the latest committed row data and if you start another transaction after this statement to change same rowdata and committed, you will still read from your version.

Now to test this, first open a new query window and run this query. It is reading from last committed row.

--QUERY1:
Alter database adventureworks set allow_snapshot_isolation on;
SET TRANSACTION ISOLATION LEVEL snapshot
BEGIN TRAN;
select * from Production.Product
where ProductID = 2;
--Commit TRAN --commit later
You will see latest committed product-2.

Now, Open an another window and run QUERY2. It will run that immediately without waiting. If you go back to other page and run only select statement in QUERY1, you will not see the updated price. Because we are still using previous version of the row. If you commit the transaction at QUERY1 and run it again, you will see the changes.

--QUERY2:
UPDATE Production.Product
SET ListPrice = 0.35
where productid =2

If you want to read latest data within your transaction, you can use "READ COMMITTED SNAPSHOT" isolation level. Readers will not use shared locks, and do not need to wait within this transaction. They will get latest version of the data. You need to set READ_COMMITTED_SNAPSHOT flag to ON to use this.

Lock TYPE Uncommitted
Data Consistency ISSUE within current TRANS RESTRICT Updates within current TRANS RESTRICT INSERT within current TRANS DETECT UPDATE CONFLICTS within current TRANS ROW VERSIONs Holds
Lock to end of TRANS
READ UNCOMMITTED Yes Yes No No No No Lowest level
READ COMMITTED No Yes No No No No NO
REPEATABLE READ No No Yes No No,but
(DEADLOCK) No YES
SERIAlizable No No Yes Yes No,but
(DEADLOCK) No Yes
READ COMMITTED SNAPSHOT No Yes No No No Yes --
SNAPSHOT No No YES YES YES(Fails transaction) YES --
Table: Properties of different lock types within a transaction

DEADLOCK: Transaction queries may block each other if ask for same resource with high level access. SQL server detects the deadlock and rollback the transaction which has lower amount work.

If you want to see blocking and waiting resources with their sql text, you can run next query::You can add more information from sys.dm_exec_connections, sys.dm_exec_sessions, sys.dm_exec_requests, and system function to get sql-text( sys.dm_exec_sql_text).

WITH CTE as --THIS IS a table expression
(
SELECT
request_session_id AS spid,
resource_type AS restype,
resource_database_id AS dbid,
DB_NAME(resource_database_id) as DBNAME,
resource_description AS res,
resource_associated_entity_id AS resid,
CASE
when resource_type = 'OBJECT' then OBJECT_NAME( resource_associated_entity_id)
ELSE 'N/A'
END as ObjectName,
request_mode AS mode,
request_status AS status
FROM sys.dm_tran_locks
)
select sc.session_id as WAITINGSESSIONID,
BLOCKS.spid as FirstBLOCKINGID,
mn.DBNAME,mn.mode as WAITingMODE,
(select text from sys.dm_exec_sql_text( sc.most_recent_sql_handle) ) as SQLWAITING,
(select text from sys.dm_exec_sql_text( BLOCKS.most_recent_sql_handle) ) as SQLBLOCKING
from CTE mn join sys.dm_exec_connections sc on mn.spid = sc.session_id
cross apply
(
select top 1 * from CTE sub join sys.dm_exec_connections sc on sub.spid = sc.session_id
where sub.res = mn.res and sub.status = 'GRANT'
) as BLOCKS
where mn.status = 'WAIT' ;

If you are not comfortable with cross apply and CTE type tables, you can check articles about that from my blog.

SUMMARY:
1. Default request is shared lock for SELECT statements and they wait for the resource if it is locked as "Exclusive" for modifications.
2. You can read uncommitted data:: select <...> from with(NOLOCK) ::-> will give you uncommitted dirty data. 3. Server adds intent locks at higher level of the resource to make quick checks for resource state. 4. READ MORE from MSDN website if you are curious about LOCKS and Transactions 5. Waiting and blocking sessions may change, so you need to be careful if you want to kill a process. (You can kill the process like: KILL 52 (52 is SPID). If you kill the process before it finishes the transaction, system may rollback the changes. You need to check details on that. It might not do a good job if it is a long transaction. 6. Keep Transactions as simple as possible to reduce deadlocks and wait.

How to find start and end of the month as Datetime in SQL

2010-04-25T14:47:00.000-07:00

While waiting at McDonalds, I decide to make a small post.

Some examples to get the start and the end of the month as datetime field.

Declare @mysearch datetime
Declare @StartDate datetime
Declare @EndDate datetime
set @mysearch = '20100401 00:00:01';
--First day of the month as Datetime field
set @StartDate = dateadd(ms,0,DATEADD(mm, DATEDIFF(m,0,getdate() ), 0));
--Last day of the month as Datetime field
set @EndDate = dateadd(ms,0,DATEADD(mm, DATEDIFF(m,0,getdate() )+1, 0)) ;
if (@mysearch >= @StartDate and @mysearch < @EndDate)
print 'Between this datetime' else print 'Not Between this datetime'
--In this example,we are using DATEDIFF, DATEADD, and GetDATE() functions.

DATEDIFF ( datepart , startdate , enddate ): You can set the datepart from nanosecond to year. It basically gives you the difference between startdate and enddate.

::To find difference in years:
SELECT DATEDIFF(year, '2003-01-01', '2006-01-01'); --> will give you 3 as a difference. You can specify startdate and enddate in different formats.

::TO find difference in hrs:

SELECT DATEDIFF(HOUR, '2010-04-22 22:59:59.9999999'
, '2010-04-23 00:00:00.0000000');-->this will give you 2 hrs.

Now if you run this::
SELECT DATEDIFF(HOUR, '2010-04-22 23:59:59.9999999'
, '2010-04-23 00:00:00.0000000');-->You will get 1hr difference. If you run for minutes, you will get 1 minute. As you might guess, it will also give 1 for difference in days.

::Referenced Datetime
In our first example we used 0 as our reference datetime field. You will still get the same result even if you set something else. We are simply taking difference and adding over the referenced datetime.

declare @REFERENCE datetime
set @REFERENCE = '20100401'

select dateadd(ms,0,DATEADD(mm, DATEDIFF(m,@REFERENCE,getdate() )+1, @REFERENCE))

If you specify this @REFERENCE as '20100401 10:12:12', you will get time field in your result. Output: 2010-05-01 10:12:12.000

Using same technique we can find many other datetime fields::

::LAST DAY OF THE PREVIOUS YEAR as Datetime
select dateadd(ms, -3, DATEADD(yy, DATEDIFF( year,0,getdate() ), 0))
::FIRST DAY OF this week
select DATEADD(week, DATEDIFF(week,0,getdate() ),0);
::LAST DAY of this week
select dateadd(day,-1,DATEADD(week, DATEDIFF(week,0,getdate() )+1,0));
::Last day of this week within ms for datetime comparison
select dateadd(ms,-3,DATEADD(week, DATEDIFF(week,0,getdate() )+1,0));

::I guess you figured out how to do the other possible cases.

::SQL Helpers

2010-04-25T14:45:00.000-07:00

I will add some of the useful sql functions:

::COLUMN NAMES in a Table
select name from sys.columns
where object_id = OBJECT_ID(N'Sales.Orders');

::Information about schema views

lists the user tables in the current database along with their schema names
select table_schema, table_name, IST.TABLE_TYPE, IST.TABLE_CATALOG
from INFORMATION_SCHEMA.TABLES IST
--where TABLE_TYPE = N'Base table'

::List of tables in the database
exec sys.sp_tables;

::Waiting and Blocking Processes
Run this query at another query page and check waiting processes again with sys.dm_tran_locks

SELECT

request_session_id AS spid,

resource_type AS restype,

resource_database_id AS dbid,

DB_NAME(resource_database_id) as DBNAME,

resource_description AS res,

resource_associated_entity_id AS resid,

CASE

when resource_type = 'OBJECT' then OBJECT_NAME( resource_associated_entity_id)

ELSE 'N/A'

END as ObjectName,

request_mode AS mode,

request_status AS status

FROM sys.dm_tran_locks

order by spid, restype;

SQL Merging DATA

2010-04-15T17:46:00.000-07:00

:::SQL MERGE

SQL server 2008 has a new feature to merge tables with actions for different conditions.

Here is an example for your reference:

MERGE INTO dbo.Target as TT
using dbo.Source as SS
    on TT.keyid = SS.keyid
WHEN MATCHED THEN
   UPDATE SET
     TT.col1 = SS.col1,
     TT.col2 = SS.col2,
    TT.col3 = SS.col3,
WHEN NOT MATCHED THEN
INSERT (col1,col2, col3,colX,colY )
values (SS.col1,SS.col2,SS.col3,SS.colX,SS.colY);

--you need to use semicolon to finish the statement!

You can use merge statement to insert new table data to the existing table. You can also do the same operation with multiple insert, update , delete statements.

If you want to specify multiple conditions for matched case, you can do that.

MERGE INTO dbo.Target as TT
using dbo.Source as SS
on TT.keyid = SS.keyid
WHEN MATCHED AND (
TT.col1 <> SS.col1
OR TT.col2 <> SS.col2 ) THEN
UPDATE SET
     TT.col1 = SS.col1,
     TT.col2 = SS.col2
WHEN NOT MATCHED THEN
     INSERT (col1,col2, col3,colX,colY )
        values (SS.col1,SS.col2,SS.col3,SS.colX,SS.colY);

You can update the related record, if it justifies your condition. You can monitor transactions with this statement. If there is a change in the record, you can add that to audit records.

:::HOW TO DELETE with TOP Option and USE ordering

Well, you might want to delete top 10 product reviews based on Ordering of low ratings. Ordering will not work directly. A logical ORDER BY for the top option in a modification statement is not allowed. When you say :

::>Delete top 10 from dbo.myreviews;

It will delete arbitrary records. if you want to order them, you can use table expressions.

WITH C as
(
   select top( 10 ) *
   from dbo.myreviews
   order by rating asc
)
DELETE FROM C;

Now, you got rid of the bad ratings for your product.

SQL : Deleting Data

2010-04-14T18:17:00.000-07:00

I will add more sql related topics. How do we delete data?

::SQL Delete Statement
The standard statement has two parts "from" and "where".
Example:
DELETE from dbo.mytabletodelete
WHERE recordno < 15;

This statement basically deletes records based on where clause.

::TRUNCATE
This statement deletes all rows from a table. It performs better than delete statement. Truncate resets identity value back to original seed. One good protection to delete all rows from a table is to set foreign keys on this table. Truncate statement does not work if the target table is referenced by a foreign key constraint.
Example:
Truncate Table dbo.mytableallrows;

USE AdventureWorks;

GO
--Copy some table to dummy table for testing
Select * into dbo.truncatetest
from HumanResources.JobCandidate;

SELECT COUNT(*) AS BeforeTruncateCount
FROM dbo.truncatetest;

TRUNCATE TABLE dbo.truncatetest;

SELECT COUNT(*) AS AfterTruncateCount
FROM dbo.truncatetest;

GO::DELETE With A Join Operation
You may want to delete filtered records from the join operation. You can join multiple tables and check columns from those tables.

DELETE FROM T1
from dbo.atable as T1
join dbo.btable as T2
on T1.rowid = T2.rowid
where T1.afield = "something" and T2.anotherfield = "another"

Wow, we delete records from table T1 based on two conditions. You can write complex queries to filter what to delete.

::Delete with Top option
You can specify how many records to delete. If you want to take top 10 of some ordered list, you can do that.
USE AdventureWorks;

DELETE TOP (2) PERCENT
FROM Production.ProductInventory;

::SQL UPDATE Statement
It allows you to update a subset from a table. If we start with the simple example:

UPDATE dbo.Products
Set price = price * 1.1
WHERE Color = "blue"

Update happens in one call, so you can easily assign one column to anotherr column.

Update Table1
Set col1 = col1 + 5, col2 = col1 + 3;

if Col1 is 10 and Col2 is 15 before the assignment, their values will be 15 and 13 after the assignment.

You can Update rows based on a join. This is similar to DELETE operation.

::UPdate based on a Join

UPDATE PR

Set price = price * 1.1
from dbo.Products as PR
join dbo.Orders as O
on PR.productid = O.productid

WHERE PR.Color = "blue" and O.orderstatus = "incomplete"

::Here, we use Orders table to find records with specific orderstatus for an update operation.

::OUTPUT Clause in Update
--if you are curious to see the column values before the update and after the update, you can use OUTPUT clause to return them

UPDATE dbo.OrderDetails
set priority = priority +1
OUTPUT
inserted.productid, deleted.priority as oldp,
inserted.priority as newp
where productid > 91;

------The end---------Next---> MERGe operations--->

SQL Commands Lookup : INSERT

2010-03-30T20:22:00.000-07:00

I decide to keep some notes here. It may help you as well.

==========Insert Statements=========================
--You can insert records into table without specifying the column names.
INSERT INTO table_name

VALUES (value1, value2, value3,...)

It may be better to specify columns for some cases.
INSERT INTO table_name (column1, column2, column3,...)

VALUES (value1, value2, value3,...)

--Multiple records can be inserted as well. SQL Server 2008 has "values" clause.

insert into dbo.myorders (orderid, orderdate, empid, custid)
values
(102, '20090212', 4, 'B'),
(103, '20090212', 1, 'A'),
(104, '20090212', 5, 'C'),
(105, '20090212', 6, 'D');

You can create this table to test it.

create table dbo.myorders

(
orderid int not null
constraint PK_myorders primary key,
orderdate date not null
constraint dft_orderdate default(current_timestamp),
empid int not null,
custid varchar(10) not null
)

==Insert Select statement inserts the rows returned by SELECT query into a target table.

insert into dbo.myorders (orderid, orderdate, empid, custid)
SELECT orderid, orderdate, empid, custid
from SomeOrderTable
where shipcountry = 'USA';

This query will insert all selected records into our table.

==Insert Exec statement is similar to "insert select", but you can insert result set returned from sql procedure. We will create a simple stored proc to show this.

create PROC proc_getorders

@custid as int
as

select orderid, orderdate, empid, custid
from Sales.Orders
where custid = @custid;
go

This proc will return a table for a given customer id. We can insert returned result from stored proc easily with "Insert ...exec" statement.
INSERT INTO dbo.myorders(orderid, orderdate, empid, custid)
EXEC dbo.proc_getorders 5;

===Insert with "SELECT INTO"
You can use to create copy of the tables. This statement copies column names, null cases, identity, and types from source table. However, it does not copy constraints, indexes, and triggers.

SELECT orderid, orderdate, empid, custid
INTO dbo.myorder_copy
from dbo.myorder

You are not going to have primary key and default value constraint at your myorder_copy table.
Bulk insert dbo.myorders from 'C:\SomeDirectory\myorderdata.txt'

with
(
datafiletype = 'char',
fieldterminator = ',',
rowterminator = '\n'
);
You can create a txt file similar to this:
2003,20090102,1,3
2004,20090102,1,6

====Insert and IDENTITY property====
I showed examples without identity column. If you have columns with autogenerated type, you can skip that field when inserting data.
You can make another table with auto generated column. IDENTITY(2,3) will set starting number to 2 and increment to 3. You need to create constraints with different names, so add something to their name.

create table dbo.myorderAutoID

(
orderid int not null identity(1,1),
constraint PK2_myorders primary key,

orderdate date not null
constraint dft2_orderdate default(current_timestamp),
empid int not null,
custid varchar(10) not null
)

Now, you can add records. Table will have auto generated order id.

insert into dbo.myorderAutoID ( orderdate, empid, custid)

values
( '20090212', 4, 'B'),
( '20090212', 4, 'B'),

( '20090212', 4, 'B');

==@@identity and SCOPE_IDENTITY() will return last generated identity value in current session. If you call those after your insert operation, it will give last identity value.
Run "Select @@identity; " to see the last generated identity value. If you want to get last identity value produced, you can use IDENT_CURRENT function. It is related to the table.

Select IDENT_CURRENT('dbo.myorderAutoID') will return 3, because you entered only 3 records to the table.

If these records are not inserted for some reason, table will skip the identity value. If your record is rejected and not inserted, table will not use that id.

insert into dbo.myorderAutoID ( orderdate, empid, custid)
values
(null , 4, 'B');

This query will skip the identity 4 and next inserted row will have identity value of 5.

===End of Insert===

SQL Coalesce

2010-03-30T19:03:00.000-07:00

If you want to convert null fields into something else inside your select command, you can do that with "Coalesce".

select

custid, coalesce( region, ' ') as region
from Sales.Customers
order by region;

This SQL query will show null regions as space.

My first post!

2010-03-15T19:11:00.000-07:00

CROSS APPLY
Examples are based on AdventureWorks DB. You can find that from MSDN and install that with your express version of sql server 2008. I will post links about how to setup that.

I like the "Cross Apply" command in MS SQL. It gives you access to left side of the join and you can perform operations for each row from left side.

Normally cross join operator performs cartesion product if you don't specify where statement in your query.

USE AdventureWorks;
GO
Select p.SalesPerson, st.Name as Territory
from Sales.SalesPerson p
cross join Sales.SalesTerritory st

This query will produce total of nxm rows. You don't have access to table p inside table st.

You can do much better with Cross Apply.

1- This will calculate avg qty in orders and total number of orders for each product.

select pt.ProductID, pt.Name, pt.StandardCost, A.num_orders, A.avg_order
from Production.Product pt
cross apply(
select count(distinct sd.SalesOrderID) as num_orders, avg(sd.OrderQty) as avg_order
from Sales.SalesOrderDetail as sd
where sd.ProductID = pt.ProductID

) as A
order by num_orders desc;

2-Latest 3 orders per customer

Select c.CustomerID, A.SalesOrderID, A.orderdate

from Sales.Customer as c
cross apply(
   select top(3) O.SalesOrderID, O.SalesPersonID, O.OrderDate,O.DueDate
   from sales.SalesOrderHeader as O
   where O.CustomerID = c.CustomerID
   order by orderdate desc, SalesOrderID desc
) as A;

3-Other examples......