Wednesday, November 23, 2005

Studying for exam 70-320

This exam is very difficult, I am studying and the most difficult themes are COM componentes, .net remoting, and advanced web services, I am studying with crammaster software and the official microsoft books.
I expecto to earn this new certification by half december

Good luck to me.

Wednesday, November 16, 2005

How to connect to sql express

If you are a purist or you only like to use console then this command is for your its very easy, and almost any one uses it because we have many graphical tools to make life easier.

sqlcmd

yeah you already heard about it!! I bet it

Just use the following line to connect and thats it. if you want more parameters search on msdn2.

sqlcmd -S server1\SQLExpress -U SqlUserAccount -P SqlPassword

Friday, November 11, 2005

Which type of replication to choose?

Microsoft SQL Server 2005 provides the following types of replication for use in distributed applications:

Transactional replication.
Merge replication.
Snapshot replication.

The type of replication you choose for an application depends on many factors, including the physical replication environment, the type and quantity of data to be replicated, and whether the data is updated at the Subscriber. The physical environment includes the number and location of computers involved in replication and whether these computers are clients (workstations, laptops, or handheld devices) or servers.



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




Transactional replication typically starts with a snapshot of the publication database objects and data. As soon as the initial snapshot is taken, subsequent data changes and schema modifications made at the Publisher are usually delivered to the Subscriber as they occur (in near real time). The data changes are applied to the Subscriber in the same order and within the same transaction boundaries as they occurred at the Publisher; therefore, within a publication, transactional consistency is guaranteed.

Transactional replication is typically used in server-to-server environments and is appropriate in each of the following cases:

You want incremental changes to be propagated to Subscribers as they occur.
The application requires low latency between the time changes are made at the Publisher and the changes arrive at the Subscriber.
The application requires access to intermediate data states. For example, if a row changes five times, transactional replication allows an application to respond to each change (such as firing a trigger), not simply the net data change to the row.
The Publisher has a very high volume of insert, update, and delete activity.
The Publisher or Subscriber is a non-SQL Server database, such as Oracle objects and data. Subsequent data changes and schema modifications made at the Publisher and Subscribers are tracked with triggers. The Subscriber synchronizes with the Publisher when connected to the network and exchanges all rows that have changed between the Publisher and Subscriber since the last time synchronization occurred.


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


Merge replication is typically used in server-to-client environments. Merge replication is appropriate in any of the following situations:

Multiple Subscribers might update the same data at various times and propagate those changes to the Publisher and to other Subscribers.
Subscribers need to receive data, make changes offline, and later synchronize changes with the Publisher and other Subscribers.
Each Subscriber requires a different partition of data.
Conflicts might occur and, when they do, you need the ability to detect and resolve them.
The application requires net data change rather than access to intermediate data states. For example, if a row changes five times at a Subscriber before it synchronizes with a Publisher, the row will change only once at the Publisher to reflect the net data change (that is, the fifth value).

Merge replication allows various sites to work autonomously and later merge updates into a single, uniform result. Because updates are made at more than one node, the same data may have been updated by the Publisher and by more than one Subscriber. Therefore, conflicts can occur when updates are merged and merge replication provides a number of ways to handle conflicts.


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


Snapshot replication distributes data exactly as it appears at a specific moment in time and does not monitor for updates to the data. When synchronization occurs, the entire snapshot is generated and sent to Subscribers.

Using snapshot replication by itself is most appropriate when one or more of the following is true:

Data changes infrequently.
It is acceptable to have copies of data that are out of date with respect to the Publisher for a period of time.
Replicating small volumes of data.
A large volume of changes occurs over a short period of time.


Snapshot replication is most appropriate when data changes are substantial but infrequent. For example, if a sales organization maintains a product price list and the prices are all updated at the same time once or twice each year, replicating the entire snapshot of data after it has changed is recommended. Given certain types of data, more frequent snapshots may also be appropriate. For example, if a relatively small table is updated at the Publisher during the day, but some latency is acceptable, changes can be delivered nightly as a snapshot.

Snapshot replication has a lower continuous overhead on the Publisher than transactional replication, because incremental changes are not tracked. However, if the dataset set being replicated is very large, it will require substantial resources to generate and apply the snapshot. Consider the size of the entire data set and the frequency of changes to the data when evaluating whether to utilize snapshot replication.

Use Try Catch In Sql 2005

A TRY…CATCH construct catches all execution errors with severity greater than 10 that do not terminate the database connection.

A TRY block must be followed immediately by an associated CATCH block. Placing any other statements between the END TRY and BEGIN CATCH statements generates a syntax error.

A TRY…CATCH construct cannot span multiple batches. A TRY…CATCH construct cannot span multiple blocks of Transact-SQL statements. For example, a TRY…CATCH construct cannot span two BEGIN…END blocks of Transact-SQL statements and cannot span an IF…ELSE construct.



Retrieving Error Information
Within the scope of a CATCH block, the following system functions can be used to obtain information about the error that caused the CATCH block to be executed:

ERROR_NUMBER() returns the number of the error.
ERROR_SEVERITY() returns the severity.
ERROR_STATE() returns the error state number.
ERROR_PROCEDURE() returns the name of the stored procedure or trigger where the error occurred.
ERROR_LINE() returns the line number inside the routine that caused the error.

ERROR_MESSAGE() returns the complete text of the error message. The text includes the values supplied for any substitutable parameters, such as lengths, object names, or times.







Well this is easy.

Just see this example and it will tell you everythin you need to know.

USE AdventureWorks;
GO

BEGIN TRY
-- Table does not exist; object name resolution
-- error not caught.
SELECT * FROM NonexistentTable;
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() as ErrorNumber,
ERROR_MESSAGE() as ErrorMessage;
END CATCH

or Using Transactions.

USE AdventureWorks;
GO
BEGIN TRANSACTION;

BEGIN TRY
-- Generate a constraint violation error.
DELETE FROM Production.Product
WHERE ProductID = 980;
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() as ErrorState,
ERROR_PROCEDURE() as ErrorProcedure,
ERROR_LINE() as ErrorLine,
ERROR_MESSAGE() as ErrorMessage;

IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
END CATCH;

IF @@TRANCOUNT > 0
COMMIT TRANSACTION;
GO

Configure Membership provider in Windows 2003

In .net 2.0 is very easy to create the databse with the comand aspnet_reqsql, but you have to grant acess to the special user in windows 2003.

After giving access you can change your web.config.

-- Create a SQL Server login for the Network Service account
sp_grantlogin 'NT AUTHORITY\Network Service'

-- Grant the login access to the membership database
USE aspnetdb
GO
sp_grantdbaccess 'NT AUTHORITY\Network Service', 'Network Service'

-- Add user to database role
USE aspnetdb
GO
sp_addrolemember 'aspnet_Membership_FullAccess', 'Network Service'



Connection Strings:

connectionString="Initial Catalog=aspnetdb;data source=localhost;Integrated Security=SSPI;" />



and the l membership section.




connectionStringName="MyLocalSQLServer"
applicationName="MyAppName"
type="System.Web.Security.SqlMembershipProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />

Import Excel file into dataset.

I didnt think it was so easy, just take a look at my code. Now the easy part will be to iterate through all the rows of the dataset, as you know dataset is in memory so the process will be very fast.

I love it.

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

Dim str As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\levalencia\Mis documentos\Libro1.xls;Extended Properties=Excel 8.0"

Dim objConn As New OleDbConnection(str)

Try

objConn.Open()

Dim objCmdSelect As New OleDbCommand("SELECT * FROM [Hoja1$]", objConn)

' Create new OleDbDataAdapter that is used to build a DataSet

' based on the preceding SQL SELECT statement.

Dim objAdapter1 As New OleDbDataAdapter()

' Pass the Select command to the adapter.

objAdapter1.SelectCommand = objCmdSelect

' Create new DataSet to hold information from the worksheet.

Dim objDataset1 As New DataSet()

' Fill the DataSet with the information from the worksheet.

objAdapter1.Fill(objDataset1, "XLData")

GridView1.DataSource = objDataset1

GridView1.DataBind()

Catch ex As Exception

Label1.Text = ex.Message

Finally

objConn.Close()

End Try

End Sub

Create XML schemas programatically

Well, in the project I am developing I have a XML column, the problem is that column must be customizable, because the customer way want some fields or some not. So the only way was to have an XML schema created at run time so the user cant input bad information in that column.

It was not so difficult, check it out.
Its basically the same as in .net 1.1, You only send a string with the command.

Even tough I think that it must be a way to make this with SMO. Server Management Objects. If somebody knows let me know.

using System.Data.SqlClient;
namespace WebApplication1
{
///
/// Summary description for WebForm1.
///

public class WebForm1 : System.Web.UI.Page
{
protected System.Data.SqlClient.SqlCommand sqlCommand1;
protected System.Data.SqlClient.SqlConnection sqlConnection1;

private void Page_Load(object sender, System.EventArgs e)
{
// Put user code to initialize the page here
sqlConnection1 = new SqlConnection("... fill with proper connection string ...");
sqlConnection1.Open();

sqlCommand1 = new SqlCommand("create xml schema collection sc1 as ' '",
sqlConnection1);
sqlCommand1.ExecuteNonQuery();
sqlConnection1.Close();
}
...
}

Hotmail Stats

  • 200 million active users
    3.3 billion inbound emails a day
    1.5 billion blocked at the router
    1.0 billion deleted as spam (never hits the user's mailbox)
    0.5 billion sent to the junk folder
    Over 100 million messages sent a day
    80 - 100 million logins per day
    5000 peak logins per second

Transactions in Sql 2005

An explicit transaction is one in which you explicitly define both the start and end of the transaction. Explicit transactions were also called user-defined or user-specified transactions in SQL Server 7.0 or earlier.
DB-Library applications and Transact-SQL scripts use the BEGIN TRANSACTION, COMMIT TRANSACTION, COMMIT WORK, ROLLBACK TRANSACTION, or ROLLBACK WORK Transact-SQL statements to define explicit transactions.
BEGIN TRANSACTION
Marks the starting point of an explicit transaction for a connection.
COMMIT TRANSACTION or COMMIT WORK
Used to end a transaction successfully if no errors were encountered. All data modifications made in the transaction become a permanent part of the database. Resources held by the transaction are freed.
ROLLBACK TRANSACTION or ROLLBACK WORK
Used to erase a transaction in which errors are encountered. All data modified by the transaction is returned to the state it was in at the start of the transaction. Resources held by the transaction are freed.
Examples:
DECLARE @TranName VARCHAR(20);
SELECT @TranName = 'MyTransaction';
BEGIN TRANSACTION @TranName;
GO
USE AdventureWorks;
GO
DELETE FROM AdventureWorks.HumanResources.JobCandidate
WHERE JobCandidateID = 13;
GO
COMMIT TRANSACTION MyTransaction;
GO

New Blog

Hello, everyone I have just creaed my new blog, today November 11 2005. I am Microsoft Certified Professional and I pretend to bring to my blog all what I learn from Visual studio 2005 team system, asp.net 2 sql server 2005 and anything that I find interesting.