Tuesday, March 31, 2009

Using NOLOCK and READPAST table in SQL Server

NOLOCK
This table hint, also known as READUNCOMMITTED, is applicable to SELECT statements only. NOLOCK indicates that no shared locks are issued against the table that would prohibit other transactions from modifying the data in the table.
The benefit of the statement is that it allows you to keep the database engine from issuing locks against the tables in your queries; this increases concurrency and performance because the database engine does not have to maintain the shared locks involved. The downside is that, because the statement does not issue any locks against the tables being read, some "dirty," uncommitted data could potentially be read. A "dirty" read is one in which the data being read is involved in a transaction from another connection. If that transaction rolls back its work, the data read from the connection using NOLOCK will have read uncommitted data. This type of read makes processing inconsistent and can lead to problems. The trick is being able to know when you should use NOLOCK.
As a side note, NOLOCK queries also run the risk of reading "phantom" data, or data rows that are available in one database transaction read but can be rolled back in another. (I will take a closer look at this side effect in part two of this article series.)
The following example shows how NOLOCK works and how dirty reads can occur. In the script below, I begin a transaction and insert a record in the SalesHistory table.
BEGIN TRANSACTION
INSERT INTO SalesHistory(Product, SaleDate, SalePrice) VALUES('PoolTable', GETDATE(), 500)

The transaction is still open, which means that the record that was inserted into the table still has locks issued against it. In a new query window, run the following script, which uses the NOLOCK table hint in returning the number of records in the SalesHistory table.

SELECT COUNT(*) FROM SalesHistory WITH(NOLOCK)
The number of records returned is 301. Since the transaction that entered the record into the SalesHistory table has not been committed, I can undo it. I'll roll back the transaction by issuing the following statement:ROLLBACK TRANSACTION
This statement removes the record from the SalesHistory table that I previously inserted. Now I run the same SELECT statement that I ran earlier:

SELECT COUNT(*) FROM SalesHistory WITH(NOLOCK)
This time the record count returned is 300. My first query read a record that was not yet committed -- this is a dirty read.

READPAST
This is a much less commonly used table hint than NOLOCK. This hint specifies that the database engine not consider any locked rows or data pages when returning results.
The advantage of this table hint is that, like NOLOCK, blocking does not occur when issuing queries. In addition, dirty reads are not present in READPAST because the hint will not return locked records. The downside of the statement is that, because records are not returned that are locked, it is very difficult to determine if your result set, or modification statement, includes all of the necessary rows. You may need to include some logic in your application to ensure that all of the necessary rows are eventually included.
The READPAST table hint example is very similar to the NOLOCK table hint example. I'll begin a transaction and update one record in the SalesHistory table.
BEGIN TRANSACTION
UPDATE TOP(1) SalesHistory
SET SalePrice = SalePrice + 1

Because I do not commit or roll back the transaction, the locks that were placed on the record that I updated are still in effect. In a new query editor window, run the following script, which uses READPAST on the SalesHistory table to count the number of records in the table.

SELECT COUNT(*) FROM SalesHistory WITH(READPAST)

My SalesHistory table originally had 300 records in it. The UPDATE statement is currently locking one record in the table. The script above that uses READPAST returns 299 records, which means that because the record I am updating is locked, it is ignored by the READPAST hint.

Monday, March 30, 2009

ERRMSG: 2.0 .NET Framework Error: The format of the file 'dotnetCHARTING' is invalid.

With the official release of ASP.NET 2.0 along with the 2.0 .NET Framework and Visual Studio 2005 .netCHARTING has been updated to natively support such environments.
If you get the following error:

Server Error in '/dotnetcharting' Application.--------------------------------------------------------------------------------
Parser ErrorDescription: An error occurred during the parsing of a resourcerequired to service this request. Please review the following specificparse error details and modify your source file appropriately.
Parser Error Message: The format of the file 'dotnetCHARTING' is invalid.
Source Error:
Line 1: <%@ Page Language="C#" debug="true"Description="dotnetCHARTING Component"%>Line 2: <%@ Register TagPrefix="dotnet" Namespace="dotnetCHARTING"Assembly="dotnetCHARTING"%>
Source File: c:\inetpub\wwwroot\dotnetcharting\csharp\features\SonBox.aspxLine: 2
...
--------------------------------------------------------------------------------Version Information: Microsoft .NET Framework Version:1.1.4322.2032;ASP.NET Version:1.1.4322.2032
-------------------


You can see above the error references ASP.NET Version:1.1.4322.2032. This indicates the application you have configured in IIS for .netCHARTING is setup to use the previous framework version.

RESOLUTION:
1) Open the Internet Information Services (IIS) Manager.
2) Right click properies on the web site or directory in use.
3) .NET 2.0 adds a new tab to the properties called "ASP.NET" with a version drop down.
4) Select 2.0 and hit apply.

JavaScript: Copy to a Date var

1.First you start with the Date object you want to copy; let's call that "d1".
2.Create a new Date object where you want to copy the value to, say "d2":
"var d2 = new Date()".
3.Call "setTime" on the new variable and pass in the "valueOf()" of your source date: "d2.setTime(d1.valueOf())".

How to create a For Loop In JavaScript?

1- A basic While Loop.
The Basic While Loop-First of all you just need to understand the while loop in it's most basic form, so that you'll understand how the for loop works. Lets take a look at a very basic form of the while loop:
i = 0;
while(i <= end)
{...i++;

)

First of all you'll notice the "i" variable, this will most likely be declared as an int by the way, it's basically what will keep count of the number of times the computer is supposed to go through the loop. You'll also notice the i++ inside the loop, this is what makes the "i" variable go up by one after every loop. It can then stop looping once it reaches whatever you've set the "end" variable to, which will more than likely be an int as well.

2-Variables
Now we'll take a look at the for loop piece by piece. The for loop itself is really just shorthand in Java for the above while loop. it's also going to have an "i" variable, so you could go ahead and declare that. It'll also have a starting value, which was zero in the while loop above, and an end value. Just make sure the starting value is smaller than the end value, and that it will eventually reach the end value. Don't want an infinite loop do you?
int i;
int begin;
int end;
3-Start, End, And Count-
Now all you have to do is to set up the for loop:
for(i = begin; i <= end; i++)

Just remember that you can set the beginning and ending values to whatever you need them to be.

How to create and write to a browser cookie in ASP.NET using C#

1- Go to the code-behind of the .aspx page you wish to create the browser cookie from. I will show 2 approaches on creating such a cookie.
2- Method 1:

// A cookie called "userInfo" is dynamically created and the key "userName" is assigned the value "patrick".
Response.Cookies["userInfo"]["userName"] = "patrick";
// A new key called "lastVisit" is added to the collection of values in the cookie and assigned the current datetime value.
Response.Cookies["userInfo"]["lastVisit"] = DateTime.Now.ToString();
// The expiration value of the cookie is set to 1 day in the future from the current date.
Response.Cookies["userInfo"].Expires = DateTime.Now.AddDays(1);
3- Method 2:

HttpCookie aCookie = new HttpCookie("userInfo");
// The key "userName" is added to the cookie and assigned the value "patrick" as the above example.
aCookie.Values["userName"] = "patrick";
// The key "lastVisit" is added to the cookie and assigned the current datetime value.
aCookie.Values["lastVisit"] = DateTime.Now.ToString();
// The expiration date of the cookie is set to 1 day in the future from the current date.
aCookie.Expires = DateTime.Now.AddDays(1);
// Add the cookie to the browser cookie collection.
Response.Cookies.Add(aCookie);

Friday, March 27, 2009

How to disable a trigger of a table in Oracle?

ALTER TRIGGER [schema_name.]trigger_name DISABLE;

Disable all table Constraints Oracle

-- DISABLE --
--accept tab prompt "Enter the name of the table to disable its constraints: "
set serveroutput on
declare
sql_stm varchar2(2000);
begin
dbms_output.enable(1000000);
for x in (select constraint_name
,decode(constraint_type,'P',' cascade') casc
from user_constraints
-- where table_name = upper('&tab')
) loop
sql_stm := 'alter table &tab disable constraint '
x.constraint_namex.casc;
dbms_output.put_line(sql_stm);
execute immediate sql_stm;
end loop;
end;
/

How can i disable many users who login names in the file.txt.

Try following command line statement on Windows 2003 machine.
Following statement will read NTLogins,txt file and will disable all user whose names are listed.

Click Start -> Run -> Cmd.exe -> OK

FOR /F "delims=" %u IN ('TYPE NTLogins.txt') DO @DSQuery user -samID "%u" DSMod user -disabled Yes

Tab Stop on Disabled Text Boxes

One option is to add the onkeyup attribute to the input element for the quantity that will check if the disabled text box qualifies to be enabled and if yes enables it. The code snippet below has a successfully tested example that is simple.The onkeyup event of the quantity input element is called immediately after the user entered something into the quantity field, we then check if the criteria is met and if yes enable the initially disabled text box so that by the time the user tabs to it, it is already enabled and the tab works.


input type="text" id="txtQuantity" onkeyup="if(this.value != '') { document.getElementById('txtSecondBox').disabled = false; }" />


input type="text" id="txtSecondBox" disabled="disabled" />

Thursday, March 26, 2009

How to Develop a Multi Tier Web Application in Visual Studio .NET 2008 - Part 1

A .NET multi-tier application (also known as an n-tier application) is an application that is composed of 3 or more functional layers. Typically, such an application contains one layer for the presentation interface, another for the business process functionality, and one more for the data handling logic. Today I will show you how to create a C# multi-tier web application in Visual Studio 2008. Due to the many steps involved I have broken up this article into multiple parts. In this section (Part 1). I will show you how to create the folder structure.

INSTRUCTIONS:

1.First we will create a Visual Studio Solution. The Solution will be the top level container for the tiers in our multi-tier application. Open Visual Studio and under the File menu select “New Project”. The “New Project” window will open.

2.In the "New Project" window open the “Other Project Types” node and select “Visual Studio Solutions”. Click on the “Blank Solution” template. Select “.NET Framework 3.5” from the dropdown list. Name the solution “NTierSolution”. Browse to the folder where you would like the solution stored. Click OK.


3.Now that the Solution has been created we can start adding projects to it. Each project will represent one tier in our multi-tier application. We will begin by adding the Presentation tier to our application. In the Solution Explorer, right click the newly created solution and select “Add” and “New Project”.


4. In the “New Project” window open the “Visual C Sharp” node and select the “ASP.NET Web Application” template. Select “.NET Framework 3.5” from the dropdown list. Name the project “NTierWeb”. In the “Location” box, leave the location pointing to the Solution folder so that the new project gets created under it. Click OK.


5.The Solution Explorer should now show the Web Project you just added. The solution is no longer visible in the Solution Explorer.




6.Open Windows Explorer and navigate to the folder that contains your solution. You will see that the solution is still there and now it contains the folder with your Web Project.

7. So far, we have created a Visual Studio Solution and added a Web Project to the solution. The Web Project we just added is our Presentation layer. We will now add a Business Process layer to our solution. Go back to Visual Studio and close the Default.aspx page if it’s open. Then in the File menu select “New Project”. In the “New Project” window open the “Visual C#” node and select the “Class Library” template. Select “.NET Framework 3.5” from the dropdown list. Name the solution “BusinessProcessLayer”. In the “Location” box, leave the location pointing to the Solution folder so that the new project gets created under it. Finally, in the solution dropdown list make sure you select “Add to Solution”. Click OK.

8.Notice that in Solution Explorer our solution is visible again with the projects underneath. This is because we now have more than one project.


9.We will now add the Data Handling tier to our multi-tier application. Go back to Visual Studio and close the Class1.cs page if it’s open. Then in Solution Explorer, right click the solution and select "Add Project".


10.In the "New Project" window open the “Visual C#” node and select the “Class Library” template. Select “.NET Framework 3.5” from the dropdown list. Name the solution “DataLayer”. In the Location box, leave the location pointing to the Solution folder so that the new project gets created under it. Click OK.

11.And that’s it. You have created the structure for a multi-tier application. See the image in this step. Your solution and projects in Visual Studio should now look like the image.




12.In Windows Explorer, navigate to the folder that contains your solution. See the image in this step. Your folder structure should look like the image.











13.The next step would be to add some functionality to your multi-tier web application. Please read my article “How to Develop a Multi-tier Web Application in Visual Studio.NET – Part 2”.

How to Block SQL Injection Hackers with ASP Validation

Protect your database-driven ASP website from hacker attacks with powerful input validation. This will slow down hackers who use methods like SQL injection attacks and XSS (cross-site scripting) via the URL querystring and form inputs. These methods are simple enough that you can do it yourself with only basic coding knowledge.
Step 1
The first goal of a hacker is to repeatedly try to break a website, causing it to display a variety of valuable errors that give away private database details. In this way, he can gain insight into the structure of the database and ultimately create a map or footprint of all its tables and columns. The second goal of the hacker is to actually manipulate the database by executing scripts in malicious ways. With control over the database, the hacker may possibly steal credit card numbers, erase data or infect it with viruses, among other nasty things. In essence, the URL querystring and textbox are the two backdoors into a database. Getting errors and manipulating the backdoors are the two methods used by hackers to ultimately destroy a database. For more details, see my companion article in the resources section or at http://www.ehow.com/how_4434719_protect-website-hacker-attacks.html .
Step 2
Block input containing malicious code.The number one way to block a hacker from manipulating the URL querstrying and textboxes is to block their input. But, how do you determine who they are, what they will input and whether or not it is safe? Unfortunately, you cannot know. So, you must assume that all user input could be potentially dangerous. A common saying in the programming world is that ALL INPUT IS EVIL. Thus, it must be treated with caution. Everything from everybody should be checked every time to ensure dangerous code does not slip in. This is accomplished by checking all input that is submitted via a querystring or form and then rejecting or removing unsafe characters before it ever reaches the database. If this sounds like a lot of trouble, you are right. But, it is the price we pay to protect our websites and databases from the wrath of hackers. It is your responsibility as the webmaster to ensure that only clean, safe input is allowed to enter your database.
Step 3
Input validation.To check if the input entered into the URL querystring or textbox is safe, we can use input validation rules. In other words, using ASP code on a web page can validate the input collected from the querystring or form to make sure it contains only safe characters. Once the input is deemed safe, it can be stored in a new variable, inserted into the SQL string and sent to the database.
Step 4
The wash and rinse cycle.Input validation should be a two-part process, like a wash and rinse cycle. We want to thoroughly clean all input by first checking for safe characters and second by checking for bad strings. See the resources at the end of this article for a more in depth discussion on this method.
Step 5
Only allow safe characters.Part one of the validation process is to reject all input unless it contains safe characters. This is the strictest and most effective form of input validation. It only allows input that is known to be good. Essentially, letters and numbers can be trusted. Special characters are the real culprits which give hackers their power and should be avoided. This extreme measure may not be feasible for all types of input, but try to restrict as many special characters as possible. See the resources at the end of this article for a more in depth discussion on this method.
Step6
Following is an ASP example that could be used for a login or search input field. It allows only a-z, A-Z, 0-9 and an apostrophe, hyphen and space. You can modify the function to include characters as you see fit. %'validation allows only good charactersfunction valGoodChars(input)good_chars = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'- "valGoodChars = truefor i = 1 to len(input)c = mid(input, i, 1)if (InStr(good_chars, c) = 0) thenvalGoodChars = falseexit functionend ifnextend function'collect the form inputsearchInput = Request.QueryString("searchKeyword")‘if an unsafe input is entered the user is asked to try againif (not valGoodChars(searchInput)) thenresponse.redirect("sorryTryAgain.asp")end if%>Remember to use a solution that best fits your website or consult a professional.
Step 7
Part two of the validation process is to reject all input if it contains bad strings. After you have collected good input with the method above, you should check it again for input that is known to be bad. Dangerous things could happen if the good character function allowed an apostrophe and hyphen, or other letter combinations like SCRIPT, SELECT, UPDATE, DELETE, etc. That is why the bad string function should be used in conjunction with the good character function. See the resources at the end of this article for a more in depth discussion on this method.
Step 8
Following is an ASP example that rejects bad characters and should be used after checking for good characters. Bad characters could include a pair of hyphens and the word script, among other things. You can modify the function to include the bad strings as you see fit. |%'validation disallows bad stringsfunction valBadStrings( input )bad_strings = Array( "--", "script" )for each i in bad_stringsif ( InStr( input, i ) <> 0 ) thenvalBadStrings = falseexit functionend ifnextvalBadStrings = trueend function‘if an unsafe input is entered the user is asked to try againif (not valBadStrings(searchInput)) thenresponse.redirect("sorryTryAgain.asp")end if%>Remember to use a solution that best fits your website or consult a professional.
Step 9
Safely query the database.Now that the input has been laundered through the good and bad functions, it is ready to be inserted into the SQL query and executed by the database. Following is sample ASP code that inserts the searchInput. queries the database for a match, then displays the results.<%'check the database for a matchSet myRecordSet = Connect.Execute ("SELECT * FROM dbo.myTable WHERE myColumn LIKE '%" & searchInput & "%'")'display the resultsif myRecordSet.EOF then'display message no results foundelse'display resultsend if end if%>By validating the input before sending it to the database you have greatly reduced the risk of your database being compromised by hackers.
Step 10
Filter characters.Another method that can be used in conjunction with the above two functions, but is considered to be very weak when used alone, is to sanitize the input by filtering or escaping. A well-known threat is the single quote or apostrophe because it breaks the SQL statement. Following is an ASP example that renders the single quote harmless, by replacing it with two single quotes.'doubleup single quotesnewSafeString = replace(searchInput, "'", "''")Other variations for the replace function include stripping out the script tag and replacing it with a space. Or, filter out characters such as the dollar sign $ quotation mark “ semi-colon ; and apostrophe ‘ the left and right angle brackets <> the left and right parentheses ( ) the pound sign # and the ampersand &. Or convert these characters to their HTML entities.Remember to use a solution that best fits your website or consult a professional.
Step 11
If you would like to pursue more advanced security techniques, please see the resources at the end of this article. Topics discussed include, password policies, buffer overrun, creative table and column names, table name aliases, set and check data types, .bak files, stored procedures with parameters, and log files.

How to Change the Colors on Your Webpage?




1.I'm going to open up the page I wrote for my previous article. You go ahead and open up the page you want to edit.
2.We are going to be using CSS (Cascading Style Sheets) This is a language developed specifically for changing the way a page looks. It is possible to do this with HTML as well, but it is not recommended. (Other web designers will laugh at you)
3.Add the code as shown in the image. You can change the color green with whatever color you wish. Note, however, that not every color name will be recognized by the browser reading it. Defining exact colors is a bit more complex, and will probably be covered in a later





4.Save your file and open it, the background should now be green.
Now add the code I have in the image shown. We're not done yet.




5.Now add the code in the image shown. Save and refresh the page, the text in that paragraph tag should appear red. I guess I'm going for some kind of Christmas theme.


6.Well you're done! Let's look at what the code means

How to add a "Bookmark Us" Link to Your Website or Blog using Javascript

1.Open your blog or webpage code editor. There will typically be an option in your blog interface or web page design software. The default editing option is usually "Visual" or "WYSIWYG". Look for "edit HTML" or similar.


2.Within your code, locate the BODY tag. This will come after the HEAD tag. You will need to add the code below right after the BODY tag.


3.Save your page and upload to your server if you have your own website. If you have a blog, you will need to save and publish.

CSharp: How to fix following C# error in Visual Studio: Cannot apply indexing with [] to an expression of type 'System.Data.DataTable'

Error:
Cannot apply indexing with [] to an expression of type 'System.Data.DataTable'

Cause:
string variable = Table[0][1].ToString();

Solution:
DataTable Table;string variable = Table.Rows[index][column-index].ToString();

Example:

DataTable Table;
string variable = Table.Rows[0][1].ToString();

The above error happens when you try to index the datatable without first accessing the "Rows" property. You must first access the "Rows" property and then provide the index.

Another Dynamic Cross-Tabs/Pivot Tables in MS-SQL

First off, before going any further make sure you have read the hall of fame SQLTeam article by Rob Volk on generating crosstab results using a flexible, dynamic stored procedure that has been viewed over 100,000 times! This entire concept and pretty much all of the ideas I've had regarding this topic and this techinique in general are all due to Rob's great work and his very clever stored procedure.
It must be crosstab season or something, because lately I've been getting quite a few emails and comments about an alternative stored procedure that I've posted in the comments to that article that has been helping quite a few users. To potentially help others out there with this common request (which I still feel should be mostly done at the presentation layer, but I suppose it's not always possible) here's a quick recap/reprint of my adaptation of Rob's excellent idea and some notes.
The main difference between Rob's original stored procedure and mine are that
  • You can order by pivot Column
  • It works fine for multi-users (no global temp tables)
  • You can condition by pivot column

Note that if you read the comments from the article, you'll see lots of modifications and adaptations of Rob's original that do address some of these issues, but I am proud of the brevity and flexibility of my code and I feel that it works well and is easily modified (see the end of this article for one idea). Depending on your needs, it may be useful to incorporate some of the other ideas presented in the article and the comments, so be sure to do some research if necessary or if you are interested in learning more.
First, here's the code for the procedure:



ALTER PROCEDURE crosstab
@select varchar(8000),
@sumfunc varchar(100),
@pivot varchar(100),
@table varchar(100),
@Where varchar(100),
@OrderByColumn varchar(100)
AS
DECLARE @sql varchar(8000), @delim varchar(1)
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
IF @OrderByColumn<>''
BEGIN
SET @OrderByColumn = ' ORDER BY ' + @OrderByColumn
END
IF @Where<>''
BEGIN
SET @Where = ' AND ' + @Where
END
EXEC ('SELECT ' + @pivot + ' AS pivot1 INTO ##pivot FROM ' + @table + ' WHERE 1=2')
EXEC ('INSERT INTO ##pivot SELECT DISTINCT ' + @pivot + ' FROM ' + @table + ' WHERE ('
+ @pivot + ' Is Not Null OR ' + @pivot + '<>'''')' + @Where + @OrderByColumn)
SELECT @sql='', @sumfunc=stuff(@sumfunc, len(@sumfunc), 1, ' END)' )
SELECT @delim=CASE Sign( CharIndex('char', data_type)+CharIndex('date', data_type) )
WHEN 0 THEN '' ELSE '''' END
FROM tempdb.information_schema.columns
WHERE table_name='##pivot' AND column_name='pivot1'
SELECT @sql=@sql + '''' + convert(varchar(100), pivot1) + ''' = ' +
stuff(@sumfunc,charindex( '(', @sumfunc )+1, 0, ' CASE ' + @pivot + ' WHEN '
+ @delim + convert(varchar(100), pivot1) + @delim + ' THEN ' ) + ', ' FROM ##pivot
DROP TABLE ##pivot
SELECT @sql=left(@sql, len(@sql)-1)
SELECT @select=stuff(@select, charindex(' FROM ', @select)+1, 0, ', ' + @sql + ' ')
EXEC (@select)
SET ANSI_WARNINGS ON


PROPERTY:

EXECUTE crosstab
select statement,
summary calculation,
pivot column,
pivot table name,
pivot column condition,
pivot column order by

(1).The SELECT statement can be anything, as long as it has proper syntax and includes a GROUP BY clause. You can use JOINs, but if you use table aliases you should include the alias in the summary calculation expression (2).

(2).The summary calculation must have an aggregate function like SUM(), AVG(), MIN(), MAX(), etc. You'd have to modify the code if you want to use DISTINCT with these functions. COUNT(*) won't work, you have to COUNT on a column.

(3).The pivot column must be in the table (4). You can use an expression for the pivot column (a+b, LEFT(FirstName,3), etc.) as long as it can be derived from the table listed in (4). A cross-tab heading will be created for each distinct value in the pivot colum/expression.

(4).This table can be any table in your database, or another database if you use the full naming syntax (database.owner.table). Tables in a linked server may also work, but I haven't tested this. It's possible that a derived table (nested SELECT) can work, but I haven't tested this either. You would need to enclose the SELECT statement in parentheses, and use a table alias outside these parentheses, like this: '(SELECT LastName FROM myTable) AS Surnames'

(5).This parameter ordering by pivot column. For example, parameter(3) = "NAME" then maybe parameter(5) = "NAME DESC".

(6).This parameter is used to filter out Pivot column


Here's two you can run :
1.
EXECUTE crosstab
'SELECT MONTH(DATE_) AS MONTHS FROM LG_208_01_STLINE STL WITH (NOLOCK)
INNER JOIN LG_208_CLCARD CL WITH (NOLOCK) ON CL.LOGICALREF = STL.CLIENTREF
WHERE DEFINITION_ LIKE ''%SERKAN%'' GROUP BY MONTH(DATE_) ORDER BY MONTH(DATE_) ',
'SUM(TOTAL)',
'DEFINITION_ ',
'LG_208_CLCARD',
'DEFINITION_ LIKE ''%SERKAN%'' ',
''

2.
EXECUTE crosstab
'SELECT CL.CODE,CL.DEFINITION_ FROM LG_208_01_STLINE STL WITH (NOLOCK)
INNER JOIN LG_208_CLCARD CL WITH (NOLOCK) ON CL.LOGICALREF = STL.CLIENTREF
WHERE TRCODE=1 GROUP BY CODE,DEFINITION_ ORDER BY CODE,DEFINITION_ ',
'SUM(TOTAL)',
'MONTH(DATE_)',
'LG_208_01_STLINE',
'MONTH(DATE_) ASC'

Wednesday, March 25, 2009

Find SQL Servers From Local Network - (By Command Line)

Go to command prompt and type in “osql -L” or “sqlcmd -L”.


Or
SQLCMD -L > c:\servers_filename.txt
Copy to servers_filename.txt

MS-SQL: How to enable the use of 'Ad Hoc Distributed Queries' by using sp_configure (OpenRowset)

If you are planning to use OpenRowset queries in order to connet to remote database servers or if you have already implemented OpenRowset queries as a solution to remote connections as an alternative tp linked servers in Microsoft SQL Server 2005, you should first configure the database instance to enable Ad Hoc Distributed Queries in the installed SQL Server database instance where the Ad Hoc query will run.



We should connect to the related SQL Server as an administrator and open a new query window.



To set 'show advanced options' run_value equal to 1 or to enable it, run


sp_configure 'show advanced options', 1

reconfigure


The return message from the above sql statements for a successful run is as;


Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.


After Advanced Options is enabled, you can again run sp_configure t-sql command and in the returned list of configuration settings, go to row where name is 'Ad Hoc Distributed Queries' and control its run_value.

If 'Ad Hoc Distributed Queries' is turned off for considering server security run_value should be "0"But since we want to enable 'Ad Hoc Distributed Queries' component in order to run 'OpenRowset/OpenDatasource' sql statements, we should set the run_value to "1"
The below sql code is a sample how you can enable a SQL Server configuration parameter.


sp_configure 'Ad Hoc Distributed Queries', 1

reconfigure


The returned message is :
Configuration option 'Ad Hoc Distributed Queries' changed from 0 to 1. Run the RECONFIGURE statement to install.

Now you can see the run_value is set to 1 if you run the "sp_configure" command and control for the "Ad Hoc Distributed Queries" row.




Now you can run your OpenRowset queries successfully from your SQL Server 2005 or SQL Server 2008 (Katmai) databases.

Unity : Günlük Ciroları gösteren SQL sorgusu

Günlük Ciroları gösteren SQL sorgusu



SELECT SUM(LG_444_01_INVOICE.NETTOTAL) AS [TOPLAM Ciro], LG_444_01_INVOICE.DATE_
FROM LG_444_01_INVOICE INNER JOIN
LG_444_CLCARD ON LG_444_01_INVOICE.CLIENTREF = LG_444_CLCARD.LOGICALREF
WHERE (LG_444_01_INVOICE.CANCELLED = 0)
GROUP BY LG_444_01_INVOICE.TRCODE, LG_444_01_INVOICE.DATE_
HAVING (LG_444_01_INVOICE.TRCODE = 1 AND LG_444_01_INVOICE.DATE_ >='20090101'
AND LG_444_01_INVOICE.DATE_ <='20090131') ORDER BY LG_444_01_INVOICE.DATE_



Sonuç:

Unity : Firma ve dönemleri listeleyen sorgu

Unity'de kullanılan dönem ve firmaları listelemek için aşağıdaki sorguyu çalıştırabilirsiniz.

SELECT FIRM.NR AS FIRMA_NO,FIRM.NAME AS FIRMA_ADI,PERIOD.NR AS DONEM_NO FROM L_CAPIFIRM AS FIRM INNER JOIN L_CAPIPERIOD AS PERIOD ON FIRM.NR=PERIOD.FIRMNR ORDER BY FIRMA_NO,DONEM_NO

Unity : 2009 Aylık BS formu sorgusu

SELECT C.CODE AS CARI, C.DEFINITION_ AS ACIKLAMA, C.TAXNR AS VERGI_NO, C.TAXOFFICE AS VERGI_DAIRESI, C.TELNRS1,
SUM(INV.NETTOTAL - INV.TOTALVAT) AS TUTAR, COUNT(INV.LOGICALREF) AS BELGE_SAYISI,MONTH(DATE_) AS AY
FROM dbo.LG_444_01_INVOICE INV INNER JOIN
dbo.LG_444_CLCARD C ON INV.CLIENTREF = C.LOGICALREF
WHERE (INV.TRCODE IN (6, 7, 8, 9,14)) AND (INV.CANCELLED = 0) AND INV.DECPRDIFF=0
GROUP BY C.CODE, C.DEFINITION_, C.TAXNR, C.TAXOFFICE, C.TELNRS1,MONTH(DATE_)
HAVING (SUM(INV.NETTOTAL - INV.TOTALVAT) >= 8000)

Unity : 2009 Aylık BA formu sorgusu

SELECT C.CODE CARI
, C.DEFINITION_ ACIKLAMA
,C.TAXNR AS VERGI_NO
,C.TAXOFFICE AS VERGI_DAIRESI
,C.TELNRS1
, SUM(INV.nettotal-INV.totalvat) AS TUTAR
,COUNT(INV.LOGICALREF) AS BELGE_SAYISI
,MONTH(DATE_) AS AY

FROM LG_444_01_INVOICE INV, LG_444_CLCARD C
WHERE INV.CLIENTREF =C.LOGICALREF
and ((INV.TRCODE IN (1,2,3,4)) OR
(INV.TRCODE = 14 AND INV.DECPRDIFF=1))

AND INV.CANCELLED=0
group BY C.CODE,C.DEFINITION_,C.TAXNR,C.TAXOFFICE,C.TELNRS1,MONTH(DATE_)
HAVING SUM(INV.nettotal-INV.totalvat)>=8000

Tuesday, March 24, 2009

Prevent displaying ASP/SQL Error messages

Pop IIS, right click on your Web site, then properties.
Home Directory Tab, configuration Button, Debugging Tab, error messages for script error, pick the second one and stick a canned message in there.

UDF function: STRINS

STRINS

Inserts set of characters into another set of characters at a specified starting point.


Syntax
StrIns ( character_expression, start, character_expression )


Arguments
character_expression - an expression of character data. character_expression can be a constant, variable, or column of character data.

start - an integer value that specifies the location to begin insertion. If start or length is negative, a null string is returned. If start is longer than the first character_expression, a null string is returned.


Return Types
nvarchar


The function's text:
CREATE FUNCTION StrIns
( @str_1 nvarchar(4000),
@start int,
@str_2 nvarchar(4000) )
RETURNS nvarchar(4000)
AS
BEGIN
RETURN (STUFF (@str_1, @start, 0, @str_2))
END
GO


Examples
This example returns a character string created by inserting the second string starting at position 2 (at b) into the first string.

SELECT dbo.StrIns('abcdef', 2, 'ijklmn')

Here is the result set:

------------
aijklmnbcdef

(1 row(s) affected)

Error message "Reserved 7713" in MS-Access

This message in ODBC error. Invalid SQL returned by ODBC

SP_lock2 Returns Additional Locking Details in MS-SQL

The sp_lock2 procedure is an enhanced version of the sp_lock SQL Server system stored procedure (see SQL Server 7.0 Books Online for more documentation). In addition to the output of sp_lock, sp_lock2 returns the names for users, databases, and tables involved in the current locks, and therefore it can help you analyze a locking scenario.

The sp_lock2 procedure returns the following additional information to sp_lock:
1.User names for the listed system process IDs (SPIDs).
2.Database names for the listed databases.
3.Table names for the listed database objects.

You can use the following script to create the sp_lock2 procedure for SQL Server 7.0:


USE MASTER
GO
create procedure sp_lock2
@spid1 int = NULL, /* server process id to check for locks */
@spid2 int = NULL /* other process id to check for locks */
as

set nocount on
/*
** Show the locks for both parameters.
*/
declare @objid int,
@dbid int,
@string Nvarchar(255)

CREATE TABLE #locktable
(
spid smallint
,loginname nvarchar(20)
,hostname nvarchar(30)
,dbid int
,dbname nvarchar(20)
,objId int
,ObjName nvarchar(128)
,IndId int
,Type nvarchar(4)
,Resource nvarchar(16)
,Mode nvarchar(8)
,Status nvarchar(5)
)

if @spid1 is not NULL
begin
INSERT #locktable
(
spid
,loginname
,hostname
,dbid
,dbname
,objId
,ObjName
,IndId
,Type
,Resource
,Mode
,Status
)
select convert (smallint, l.req_spid)
--,coalesce(substring (user_name(req_spid), 1, 20),'')
,coalesce(substring (s.loginame, 1, 20),'')
,coalesce(substring (s.hostname, 1, 30),'')
,l.rsc_dbid
,substring (db_name(l.rsc_dbid), 1, 20)
,l.rsc_objid
,''
,l.rsc_indid
,substring (v.name, 1, 4)
,substring (l.rsc_text, 1, 16)
,substring (u.name, 1, 8)
,substring (x.name, 1, 5)
from master.dbo.syslockinfo l,
master.dbo.spt_values v,
master.dbo.spt_values x,
master.dbo.spt_values u,
master.dbo.sysprocesses s
where l.rsc_type = v.number
and v.type = 'LR'
and l.req_status = x.number
and x.type = 'LS'
and l.req_mode + 1 = u.number
and u.type = 'L'
and req_spid in (@spid1, @spid2)
and req_spid = s.spid
end
/*
** No parameters, so show all the locks.
*/
else
begin
INSERT #locktable
(
spid
,loginname
,hostname
,dbid
,dbname
,objId
,ObjName
,IndId
,Type
,Resource
,Mode
,Status
)
select convert (smallint, l.req_spid)
--,coalesce(substring (user_name(req_spid), 1, 20),'')
,coalesce(substring (s.loginame, 1, 20),'')
,coalesce(substring (s.hostname, 1, 30),'')
,l.rsc_dbid
,substring (db_name(l.rsc_dbid), 1, 20)
,l.rsc_objid
,''
,l.rsc_indid
,substring (v.name, 1, 4)
,substring (l.rsc_text, 1, 16)
,substring (u.name, 1, 8)
,substring (x.name, 1, 5)
from master.dbo.syslockinfo l,
master.dbo.spt_values v,
master.dbo.spt_values x,
master.dbo.spt_values u,
master.dbo.sysprocesses s
where l.rsc_type = v.number
and v.type = 'LR'
and l.req_status = x.number
and x.type = 'LS'
and l.req_mode + 1 = u.number
and u.type = 'L'
and req_spid = s.spid
order by spID
END
DECLARE lock_cursor CURSOR
FOR SELECT dbid, ObjId FROM #locktable WHERE Type ='TAB'

OPEN lock_cursor
FETCH NEXT FROM lock_cursor INTO @dbid, @ObjId
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @string =
'USE ' + db_name(@dbid) + char(13)
+ 'UPDATE #locktable SET ObjName = object_name('
+ convert(varchar(32),@objId) + ') WHERE dbid = ' + convert(varchar(32),@dbId)
+ ' AND objid = ' + convert(varchar(32),@objId)

EXECUTE (@string)
FETCH NEXT FROM lock_cursor INTO @dbid, @ObjId
END
CLOSE lock_cursor
DEALLOCATE lock_cursor


SELECT * FROM #locktable
return (0)
-- END sp_lock2
GO


Referance : http://support.microsoft.com/kb/q255596

Malzeme Kartı özelliklerine otomatik kayıt açmak. (LG_XXX_CHARASGN)

Unity'de malzeme kartında bulunan "Malzeme Özellikleri" tabına otomatik olarak kayıt eklemek için aşağıdaki cursor'u kullanabilirsiniz.





Kullanımı:



EXEC [dbo].[SP_CHARASGN_INSERT] '508' ,'822096031A0330640000208'

Açıklaması:
İlk parametre firma numarası, ikinci parametre ise malzeme kodudur.


SQL Kodu:
CREATE PROCEDURE [dbo].[SP_CHARASGN_INSERT]

@FIRMNR VARCHAR(5),
@NEWCODE VARCHAR(30)
AS
EXEC(
'DECLARE
@MAXREF INT,
@MAXREF2 INT,
@MAXREF3 INT,
@LOGICALREF INT,
@LINENR INT,
@CHARVALREF INT,
@CHARCODEREF INT,
@CODE VARCHAR(50),
@CHARVAL VARCHAR(50),
@SPECODE VARCHAR(50),
@CYPHCODE VARCHAR(50)

DECLARE CARI CURSOR FOR

SELECT LOGICALREF,CODE FROM LG_' + @FIRMNR + '_ITEMS (NOLOCK) WHERE LOGICALREF NOT IN (
SELECT ITEMREF FROM LG_' + @FIRMNR + '_CHARASGN) AND CARDTYPE NOT IN (20,22) AND ACTIVE =0 AND CODE =''' + @NEWCODE + '''
ORDER BY CODE

OPEN CARI
FETCH NEXT FROM CARI
INTO @LOGICALREF,@CODE
WHILE @@FETCH_STATUS = 0
BEGIN
SET @LINENR=1
WHILE @LINENR<>5
BEGIN
SELECT @MAXREF=ISNULL(MAX(LASTLREF),0) + 1 FROM LG_' + @FIRMNR + '_CHARASGNSEQ WHERE ID=1
UPDATE LG_' + @FIRMNR + '_CHARASGNSEQ SET LASTLREF=@MAXREF WHERE ID=1
SET @CHARCODEREF = @LINENR + 1
IF @LINENR=1 --BRAND
BEGIN
SELECT @CHARVAL=NAME FROM [10.0.0.26].[BURSA1].[DBO].LG_BRAND WHERE CODE=SUBSTRING(@CODE,4,3)
END
IF @LINENR=2 --TICKET
BEGIN
SELECT @CHARVAL=NAME FROM [10.0.0.26].[BURSA1].[DBO].LG_TICKETNO WHERE CODE=SUBSTRING(@CODE,7,3)
END
IF @LINENR=3 --METER
BEGIN
SELECT @CHARVAL=NAME FROM [10.0.0.26].[BURSA1].[DBO].LG_METRAJ WHERE CODE=SUBSTRING(@CODE,10,4)
END
IF @LINENR=4 --COLOR
BEGIN
SET @CHARVAL=SUBSTRING(@CODE,19,5)
END
SET @CHARVALREF = 0

SELECT @CHARVALREF=LOGICALREF FROM LG_' + @FIRMNR + '_CHARVAL WHERE CHARCODEREF=@CHARCODEREF AND CODE =@CHARVAL
IF @CHARVALREF = 0
BEGIN
SELECT @MAXREF2=ISNULL(MAX(LASTLREF),0) + 1 FROM LG_' + @FIRMNR + '_CHARVALSEQ WHERE ID=1
UPDATE LG_' + @FIRMNR + '_CHARVALSEQ SET LASTLREF=@MAXREF2 WHERE ID=1

SELECT @MAXREF3=ISNULL(MAX(VALNO),0) + 1 FROM LG_' + @FIRMNR + '_CHARVAL WHERE CHARCODEREF=@CHARCODEREF
INSERT INTO LG_' + @FIRMNR + '_CHARVAL (LOGICALREF,CHARCODEREF,VALNO,CODE,NAME)
VALUES (@MAXREF2,@CHARCODEREF,@MAXREF3,@CHARVAL,@CHARVAL)
SELECT @CHARVALREF=LOGICALREF FROM LG_' + @FIRMNR + '_CHARVAL WHERE CHARCODEREF=@CHARCODEREF AND CODE =@CHARVAL
END

INSERT INTO LG_' + @FIRMNR + '_CHARASGN (LOGICALREF,ITEMREF,CHARCODEREF,CHARVALREF,LINENR,MATRIXLOC,PRIORITY)
VALUES (@MAXREF,@LOGICALREF,@CHARCODEREF,@CHARVALREF,@LINENR,0,0)
SET @LINENR = @LINENR + 1
END
FETCH NEXT FROM CARI
INTO @LOGICALREF,@CODE
END
CLOSE CARI
DEALLOCATE CARI')

Monday, March 23, 2009

SQL injection finder query in MS-SQL

The following SQL Query and Stored procedures injection 's can list.


DECLARE @T VARCHAR(255),
@C VARCHAR(255)
DECLARE Table_Cursor CURSOR FOR
SELECT a.name,b.name FROM sysobjects a,syscolumns b
WHERE a.id=b.id AND a.xtype='u' AND
(b.xtype=99 OR b.xtype=35 OR b.xtype=231 OR b.xtype=167)
OPEN Table_Cursor
FETCH NEXT FROM Table_Cursor INTO @T,@C
WHILE(@@FETCH_STATUS=0)
BEGIN
EXEC('Select ['+@C+'] as [' +@T+'.'+@C+'] From [' +@T+'] where ['+@C+'] like ''% script src=%''')
FETCH NEXT FROM Table_Cursor INTO @T,@C
END
CLOSE Table_Cursor
DEALLOCATE Table_Cursor

MS-SQL Basic Copy Table

Following command is used to copy a simple table.

Select * INTO NewTable From OLDTABLE

Pattern matching in Javascript

var pattern1 = /^[789]{1}[\d]{2}[AaBbCcDd]{1}$/;
var pattern2 = /^[3456]{1}[1234]{1}[\d]{2}[AaBbCcDd]{1}$/;
var pattern3 = /^[54]{1}[12345]{1}[\d]{2}[YyZz]{1}$/;

if (!obj.match(pattern1) || !obj.match(pattern2) || !obj.match(pattern3)) {
alert("Doesn't match!");
return false;
}

JavaScript : IsDate function

function isDate(dtStr){
var daysInMonth = DaysArray(12)
var pos1=dtStr.indexOf(dtCh)
var pos2=dtStr.indexOf(dtCh,pos1+1)
var strMonth=dtStr.substring(0,pos1)
var strDay=dtStr.substring(pos1+1,pos2)
var strYear=dtStr.substring(pos2+1)
strYr=strYear
if (strDay.charAt(0)=="0" && strDay.length>1) strDay=strDay.substring(1)
if (strMonth.charAt(0)=="0" && strMonth.length>1) strMonth=strMonth.substring(1)
for (var i = 1; i <= 3; i++) {
if (strYr.charAt(0)=="0" && strYr.length>1) strYr=strYr.substring(1)
}
month=parseInt(strMonth)
day=parseInt(strDay)
year=parseInt(strYr)
if (pos1==-1 || pos2==-1){
alert("The date format should be : mm/dd/yyyy")
return false
}
if (strMonth.length<1 || month<1 || month>12){
alert("Please enter a valid month")
return false
}
if (strDay.length<1 || day<1 || day>31 || (month==2 &&
day>daysInFebruary(year)) || day > daysInMonth[month]){
alert("Please enter a valid day")
return false
}
if (strYear.length != 4 || year==0 || yearmaxYear){
alert("Please enter a valid 4 digit year between "+minYear+" and "+maxYear)
return false
}
if (dtStr.indexOf(dtCh,pos2+1)!=-1 ||
isInteger(stripCharsInBag(dtStr, dtCh))==false){
alert("Please enter a valid date")
return false
}
return true
}

Friday, March 20, 2009

Function : Currency formatting in JavaScript

This function will convert numeric value (interger or float) into the formatted currency:

function num2money(n_value) {
// validate input
if (isNaN(Number(n_value)))
return 'ERROR';

// save the sign
var b_negative = Boolean(n_value < 0);
n_value = Math.abs(n_value);

// round to 1/100 precision, add ending zeroes if needed
var s_result = String(Math.round(n_value * 1e2) % 1e2 + '00').substring(0, 2);

// separate all orders
var b_first = true;
var s_subresult;
while (n_value >= 1) {
s_subresult = (n_value >= 1e3 ? '00' : '') + Math.floor(n_value % 1e3);
s_result = s_subresult.slice(-3) + (b_first ? '.' : ',') + s_result;
b_first = false;
n_value = n_value / 1e3;
}
// add at least one integer digit
if (b_first)
s_result = '0.' + s_result;

// apply formatting and return
return b_negative
? '($' + s_result + ')'
: '$' + s_result;
}

Thursday, March 19, 2009

JavaScript :Calculator







JAVASCRIPT CODE:

JavaScript : Formatting numbers for decimals and digits

Formatting numbers for
decimals and significant digits in JavaScript

Formatting numbers so they confirm to a specific format can be
deceivingly tricky. For example, one of the most common tasks is to format a
number for currency display- an integer followed by two decimals. You may be
tempted to use number rounding to first shift the number's decimal places (via multiplication),round it, then shift the decimal back (via division) to pound the number into your hard earned dollar, though that won't work in many cases. For example, consider the number 120. Number rounding certainly won't get you to 120.00.




To easily format numbers for a specific number of trailing
decimals or total digits (aka padding), JavaScript 1.5 introduces the below
two nifty methods:
















Methods



Description



Number.toFixed(x)



Formats any number for "x" number of trailing
decimals. The number is rounded up, and "0"s are used after the
decimal point if needed to create the desired decimal length.



Number.toPrecision(x)



Formats any number so it is of "x" length. Also
called significant digits. A decimal point and "0"s are used if
needed to create the desired length.





Number.toFixed()


The best way to see all the subtleties of toFixed() is to see it in action:


var profits=2489.8237


profits.toFixed(3)
//returns 2489.824 (round up)


profits.toFixed(2)
//returns 2489.82


profits.toFixed(7)
//returns 2489.8237000 (padding)


Displaying any number in currency format can't get any easier!




Number.toPrecision()


To toPrecision() now:


var anumber=123.45


anumber.toPrecision(6)
//returns 123.450 (padding)


anumber.toPrecision(4)
//returns 123.5 (round up)


anumber.toPrecision(2)
//returns 1.2e+2 (you figure it out!)


toPrecision() is useful if your number must be of a certain length.




Browser
considerations


Now, as noted, our two heros above are JavaScript 1.5 methods.
What this means is that they'll only work in IE5.5+ and NS6+. The issue of
legacy browsers not performing the desired formatting operation not
withstanding, how do you ensure that these two methods at least degrade well?
Well, by using method detection in your code. For example:


var profits=2489.8237


if (profits.toFixed)
//if browser supports toFixed() method


profits.toFixed(2)




Wednesday, March 18, 2009

The SQL SELECT DISTINCT Statement

In a table, some of the columns may contain duplicate values. This is not a problem, however, sometimes you will want to list only the different (distinct) values in a table.

The DISTINCT keyword can be used to return only distinct (different) values.

SQL SELECT DISTINCT Syntax
SELECT DISTINCT column_name(s) FROM table_name



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

SELECT DISTINCT Example
The "Persons" table:



Now we want to select only the distinct values from the column named "City" from the table above.

We use the following SELECT statement:

DECLARE @Persons TABLE (LogRef int, FirstName varchar(30),LastName varchar(30),Address varchar(50),City varchar(50))
INSERT INTO @Persons
SELECT 1, 'Serkan','SONMEZ','Altiparmak Mh','Bursa'
UNION SELECT 2, 'Egemen','TANIRER','Muradiye Mh.','Trabzon'
UNION SELECT 3, 'Ozkan','YAKUT','Ataevler Mh.','Ankara'
UNION SELECT 4, 'Rahmi','TABAN','Kadıkoy','Istanbul'
UNION SELECT 4, 'Didem','KIZILKAYA','Sarıyer','Istanbul'

SELECT DISTINCT City FROM @Persons


The SQL SELECT Statement

The SELECT statement is used to select data from a database.
The result is stored in a result table, called the result-set.
SQL SELECT Syntax
SELECT column_name(s) FROM table_name
and
SELECT * FROM table_name
Note: SQL is not case sensitive. SELECT is the same as select.

An SQL SELECT Example
We use the following SELECT statement:


DECLARE @Persons TABLE (LogRef int, FirstName varchar(30),LastName varchar(30),Address varchar(50),City varchar(50))
INSERT INTO @Persons
SELECT 1, 'Serkan','SONMEZ','Altiparmak Mh','Bursa'
UNION SELECT 2, 'Egemen','TANIRER','Muradiye Mh.','Trabzon'
UNION SELECT 3, 'Ozkan','YAKUT','Ataevler Mh.','Ankara'
UNION SELECT 4, 'Rahmi','TABAN','Kadıkoy','Istanbul'
UNION SELECT 5, 'Didem','KIZILKAYA','Sarıyer','Istanbul'

SELECT FirstName,LastName FROM @Persons

Results:





SELECT * Example

We use the following SELECT statement:

SELECT * FROM @Persons



SQL DML and DDL

SQL can be divided into two parts: The Data Manipulation Language (DML) and the Data Definition Language (DDL).

The query and update commands form the DML part of SQL:

SELECT - extracts data from a database
UPDATE - updates data in a database
DELETE - deletes data from a database
INSERT INTO - inserts new data into a database
The DDL part of SQL permits database tables to be created or deleted. It also define indexes (keys), specify links between tables, and impose constraints between tables. The most important DDL statements in SQL are:

CREATE DATABASE - creates a new database
ALTER DATABASE - modifies a database
CREATE TABLE - creates a new table
ALTER TABLE - modifies a table
DROP TABLE - deletes a table
CREATE INDEX - creates an index (search key)
DROP INDEX - deletes an index

Friday, March 13, 2009

Tenigma: Invalid Decrypt Buffer Size

Unity'ye bağlanırken "Tenigma: Invalid Decrypt Buffer Size" hatası gelirse

UPDATE L_CAPIUSER SET PREVKEY1= KEY_, PREVKEY2= KEY_

SQL sorgusu çaıştırılmalıdır.

Thursday, March 12, 2009

How Can I UpdateProgress in AjaxToolkit?

First, our page would add the following definitions.
1.ToolkitScriptManager
2.UpdatePanel
3.UpdateProgress
4.Button
5.Image





Then the properties of objects are organized as follows:













Clicking the button works ButtonClick procedure. While this process is the defining image picture looks on the update panel.

To list all tables in a PostgreSQL database

select tablename from pg_tables where SUBSTRING(tablename,1,2)<>'pg' order by tablename

Wednesday, March 11, 2009

Cursor : Disable All Triggers on a Database

CREATE PROCEDURE [dbo].[DisableAllTriggers]
AS
DECLARE @string VARCHAR(8000)
DECLARE @tableName NVARCHAR(500)
DECLARE cur CURSOR
FOR SELECT name AS tbname FROM sysobjects WHERE id IN(SELECT parent_obj FROM sysobjects WHERE xtype=‘tr’)
OPEN cur
FETCH next FROM cur INTO @tableName
WHILE @@fetch_status = 0
BEGIN
SET @string =‘Alter table ’+ @tableName + ‘ Disable trigger all’
EXEC (@string)
FETCH next FROM cur INTO @tableName
END
CLOSE cur
DEALLOCATE cur
GO
—-To execute the SP
EXEC [DisableAllTriggers]

Reference : Pinal Dave (http://blog.SQLAuthority.com)

Tuesday, March 10, 2009

T-SQL Round Function Error : "An error occurred while executing batch. Error message is: Arithmetic Overflow."

PROBLEM:
I am doing sums of percentages have used the Round(99.999875,3) function to great avail to get a value of 100.000.

But, when I migrated to SQL 2K5, i used the same function and it gave me an arithmetic overflow error; more specifically:
"An error occurred while executing batch. Error message is: Arithmetic Overflow."

SELECT ROUND(99.999875,3)
-- An error occurred while executing batch. Error message is: Arithmetic Overflow

SOLUTION:

SELECT ROUND( CAST(99.999875 as decimal(10,3)), 3) -- 100.000

Thursday, March 5, 2009

Unity'de hareket görmeyen Stok,Cari ve Muhasebe kartlarını iptal etmek

Hareket görmeyen cari kartlarını, muhasebe hesap kodlarını,malzeme kartlarını
kullanım dışı yapmak için siteden topladığım kodları tek başlık altında toplamak istedim. Kartları silmek isteyen arkadaşlara tavsiyem önce aşağıdaki kodlarlar kartları kullanım dışı yapmak ve sonra muhasebe programından kartları toplu çıkartmak olacaktır.


Önemli not bu işlemleri gerçekleştirirken lütfen gerekli özeni gösteriniz.
Sizlere tavsiyem kartları kullanım dışı yapmak yeterli olmalı. a ve b seçenekleri bu işlem için yeterli. C seçeneği ise kartları silmek için yapılan bir işlem. C seçeneğini gerçekleştirmemenizi tavsiye ediyorum.

1)Hareket Görmemiş Cari Kartları Kullanımdışı Yapmak için

a) Öncelikle hareket görmeyen cari kartların bir dökümünü alalım.
SELECT * FROM LG_108_CLCARD
WHERE LOGICALREF NOT IN (SELECT CLIENTREF FROM LG_108_01_CLFLINE) order by CODE ASC

alınan döküm ile muhasebe programında sonucun doğru olup olmadığını test edelim.

b) Eğer döküm ve muhasebe programındaki durum aynı ise aşağıdaki kodlarla kartları kullanımdışı yapalım
UPDATE LG_108_CLCARD
SET ACTIVE=1 --KULLANIMDIŞI YAPMAK ICIN
WHERE LOGICALREF NOT IN (SELECT CLIENTREF FROM LG_108_01_CLFLINE)

c) b seçeneğini de gerçekleştirdikten sonra muhasebe programından kullanımdışı kartları görelim. Ctrl - A tuş kombinasyonu ile hareket görmeyen tüm kartları seçelim. Ve sağ tıkladıktan sonra Toplu Kart Çıkar seçeneğini seçelim. Eğer hata yapacağınızı düşünüyorsanız bu seçeneği gerçekleştirmeyiniz.

Hareketsiz cari kartlar silinmiş olması gerekli.

Önemli not bu işlemleri gerçekleştirirken lütfen gerekli özeni gösteriniz.


2)Hareket Görmemiş Muhasebe Hesap Kodlarını Kullanımdışı Yapmak için

a) Öncelikle hareket görmeyen kartların bir dökümünü alalım.
SELECT * FROM LG_108_EMUHACC
WHERE LOGICALREF NOT IN (SELECT ACCOUNTREF FROM LG_108_01_EMFLINE) order by CODE ASC

alınan döküm ile muhasebe programında sonucun doğru olup olmadığını test edelim.

b) Eğer döküm ve muhasebe programındaki durum aynı ise aşağıdaki kodlarla kartları kullanımdışı yapalım. Ancak burada dikkat edilmesi gereken ana hesapları kullanımdışı yapmamalıyız.
UPDATE LG_108_EMUHACC
SET ACTIVE=1 --KULLANIMDIŞI YAPMAK ICIN
WHERE LOGICALREF NOT IN (SELECT ACCOUNTREF FROM LG_108_01_EMFLINE)

c) b seçeneğini de gerçekleştirdikten sonra muhasebe programından kullanımdışı kartları görelim. Ctrl - A tuş kombinasyonu ile hareket görmeyen tüm kartları seçelim. Ve sağ tıkladıktan sonra Toplu Kart Çıkar seçeneğini seçelim. Eğer hata yapacağınızı düşünüyorsanız bu seçeneği gerçekleştirmeyiniz.

Hareketsiz muhasebe hesap kodları silinmiş olması gerekli.

Önemli not bu işlemleri gerçekleştirirken lütfen gerekli özeni gösteriniz.

3) Hareket Görmemiş Stok Kartlarını Kullanımdışı Yapmak için

a) Öncelikle hareket görmeyen kartların bir dökümünü alalım.
SELECT * FROM LG_108_ITEMS
WHERE LOGICALREF NOT IN (SELECT STOCKREF FROM LG_108_01_STLINE)

alınan döküm ile muhasebe programında sonucun doğru olup olmadığını test edelim.

b) Eğer döküm ve muhasebe programındaki durum aynı ise aşağıdaki kodlarla kartları kullanımdışı yapalım.
UPDATE LG_108_ITEMS
SET ACTIVE=1 --KULLANIMDIŞI YAPMAK ICIN
WHERE LOGICALREF NOT IN (SELECT STOCKREF FROM LG_108_01_STLINE)


c) b seçeneğini de gerçekleştirdikten sonra muhasebe programından kullanımdışı kartları görelim. Ctrl - A tuş kombinasyonu ile hareket görmeyen tüm kartları seçelim. Ve sağ tıkladıktan sonra Toplu Kart Çıkar seçeneğini seçelim. Eğer hata yapacağınızı düşünüyorsanız bu seçeneği gerçekleştirmeyiniz.

Hareketsiz stok kartları silinmiş olması gerekli.

Unity için Navigatör kurulumu


1- *.lrf dosyaları (6 tane) navi.sys (1 tane) C:\Program Files\Microsoft Office\OFFICE11 dizinine kopyalanacak




2- LGNAVI.DLL dosyası C:\Program Files\Microsoft Office\OFFICE11\XLSTART dizinine kopyalanacak.

3- Kopyalama tamamlandığında “incorrect registry settings “ hatası veriyorsa versiyon hatası olabilir. Yaptığımız örnekte LGNAVI.dll 1.71.0.0 , LENGINE1 1.71.0.0 olduğunda sorun giderildi.



Wednesday, March 4, 2009

How can i install/remove a .net windows service using InstallUtil?

With Community Server 2007.1, it is now possible to install multiple instances of a Windows
Service without the need for custom MSI builds. This is done by manually
installing the Windows Service with the command line utility and specifying the
name of the service to be used.

Previously, the limitations on multiple installs were due to the product name installed with
the MSI, the hard coded service name in the service installer, and also some
registry keys that were needed by the installer. Doing multiple
installations required these to be manually changed in code and re-compiled in
a custom build.

With the new version, the hard coded limitations have been removed, allowing for one
instance of the add-ons to be installed with the MSI, and then additional
instances of the program to be activated through the command line.


How to Install

In order to install additional instances of the service, you will need to use the
InstallUtil.exe program that is included with the .NET Framework SDK (available
here
). The InstallUtil.exe program is located in the .NET Framework's
installation directory, which will typically be in the
following directory:

C:\Windows\Microsoft.NET\Framework\v2.0.50727

First, you will need to create the first instance by installing using the MSI package. This extracts all of the files and allows you to quickly install your first instance. To create a second instance, you will need to make a copy of the installation directory, since the second instance will need its own set of files, configuration, and will run separately from the first instance.
The add-ons are normally installed to a folder in C:\Program Files\Telligent by default.

Once you have created a copy of the add-on's installation files, you need to register it with
Windows as a service. To activate it, you will install the service using InstallUtil.exe and add a /name parameter to specify what the service will be named. The names have to be unique, so if you already have " PostGreSQL Backup Service ", you might need to name it "PostGreSQL Backup Service". You then specify the filename of the Windows Service, which is usually like “
postgrebackup.exe ". So for example, it would be:

installutil c:\postgrebackup\postgrebackup.exe /LogToConsole=true /LogFile=c:\backuplog.log

You would need to run this from a command prompt within the add-ons directory. The name
must be in quotes if it contains spaces, and it must come before the filename. After installation, if you bring up the Windows Services console (Start, Run, services.msc), you should find the new service installed and listed alphabetically.

You may need to add the path to the InstallUtil.exe program onto the command if the .NET
Framework folder isn't in your PATH setting.

How to Uninstall

Uninstalling the service is very similiar to installing. You will still use the InstallUtil.exe program and will need to specify the name of the service to uninstall, though this time you will need to add a /u switch to specify that you intend to uninstall it. For instance:

InstallUtil /u /name=" PostGreSQL Backup Service" postgrebackup.exe

The name must be specified since if you have multiple instances of the same executable
installed, it will need to know which one. If no name is specified, it will remove the one with the default name as opposed to a custom named one.

Again, the uninstall and name parameters must come before the filename so that it know they are for that file.

You may need to add the path to the InstallUtil.exe program onto the command if the .NET
Framework folder isn't in your PATH setting.




Tuesday, March 3, 2009

How to read the first sheet in Excel with C#


string AppPath=Request.ServerVariables["APPL_PHYSICAL_PATH"]+"Tahsilat/";
OleDbConnection m_ConnectionToExcelBook;
OleDbDataAdapter m_AdapterForExcelBook;
m_ConnectionToExcelBook = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+AppPath+"deneme.xls;Extended Properties=Excel 8.0;");
bool ErrorExist=false;

try
{

m_ConnectionToExcelBook.Open();
DataTable dtExcelSchema = m_ConnectionToExcelBook.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
// select first sheetname
string sheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
DataTable DataObject = new DataTable();
OleDbCommand selectCommand = new OleDbCommand("select * from [" + sheetName + "]");


selectCommand.Connection = m_ConnectionToExcelBook;
m_AdapterForExcelBook = new OleDbDataAdapter();
m_AdapterForExcelBook.SelectCommand = selectCommand;
m_AdapterForExcelBook.Fill(DataObject);

if (DataObject.Rows.Count > 0)
{
.......
.......
}