tag:blogger.com,1999:blog-43189652167330272822024-02-28T15:43:49.573-08:00An IT SolutionAn IT Solutionhttp://www.blogger.com/profile/11201763791917815178noreply@blogger.comBlogger76125tag:blogger.com,1999:blog-4318965216733027282.post-11849422915029499342017-01-01T03:45:00.002-08:002017-01-01T03:45:14.545-08:00Azure sp_execute_remote / Cross Database query in Sql Azure.<div dir="ltr" style="text-align: left;" trbidi="on">
Cross database query is not allowed in Sql Azure. Rather Azure has applied a different logic to use<br />
the same.<br />
<br />
Please check my article before proceeding with "sp_execute_remote "<br />
<br />
<a href="http://an-it-solution.blogspot.in/2017/01/sql-azure-select-from-another-database.html">http://an-it-solution.blogspot.in/2017/01/sql-azure-select-from-another-database.html</a><br />
<br />
You need to create a SP in "<b style="background-color: white; color: #333333; font-family: "Trebuchet MS", Trebuchet, sans-serif; font-size: 14px;">DbCustomers"</b><span style="background-color: white; color: #333333; font-family: "Trebuchet MS", Trebuchet, sans-serif; font-size: 14px;"> with the name of "</span>LogEvents_Insert" which will be called by following query.<br />
<br />
The below query will execute in "<b style="background-color: white; color: #333333; font-family: "Trebuchet MS", Trebuchet, sans-serif; font-size: 14px;">DbPayments</b><span style="background-color: white; color: #333333; font-family: "Trebuchet MS", Trebuchet, sans-serif; font-size: 14px;"> " and insert data into "LogLogin" of "</span><b style="background-color: white; color: #333333; font-family: "Trebuchet MS", Trebuchet, sans-serif; font-size: 14px;">DbCustomers"</b><br />
<br />
EXEC sp_execute_remote @data_source_name = N'<b>DbUtilityLogging_Datasource</b>',<br />
@stmt = N'LogEvents_Insert @LoginID',<br />
@params = N'@LoginId nvarchar(50)',<br />
@LoginID='narender90'<br />
<br />
<br />
<div>
<br /></div>
</div>
An IT Solutionhttp://www.blogger.com/profile/11201763791917815178noreply@blogger.com2tag:blogger.com,1999:blog-4318965216733027282.post-3663880093789145912017-01-01T03:26:00.003-08:002017-01-01T03:28:07.357-08:00Sql azure select from another database / Cross Database connection<div dir="ltr" style="text-align: left;" trbidi="on">
<b>Cross database query Sql Azure</b><br />
<b><br /></b>
<b>Azure cross database query performance</b><br />
<b><br /></b>
<b>sql azure select from another database</b><br />
<b><br /></b>
<b>reference to database and/or server name in is not supported in this version of sql server.</b><br />
<b><br /></b>
<b>All the above statements are reference to the issue we face while shifting data from Sql Server to Azure Database.</b><br />
<br />
While using Sql Server (Any version) we can reference any database with in Sql server according to user rights assigned to user.<br />
<br />
We can use statements like "select * from db1.dbo.customers"<br />
<br />
But now in Sql Azure the system and terminology has changed.<br />
<br />
Now, azure has new concept of <b>External Tables</b> and <span style="font-family: "calibri" , sans-serif; font-size: 11pt;"><b>sp_execute_remote</b></span> in introduced.<br />
<br />
Now we need to know the who is who in Azure.<br />
<br />
<b>External Tables = "Is for Select"</b><br />
<b><br /></b>
<b style="font-family: Calibri, sans-serif; font-size: 14.6667px;">sp_execute_remote="Execute External Store Procedures"</b><br />
<b style="font-family: Calibri, sans-serif; font-size: 14.6667px;"><br /></b>
Before proceeding.<br />
<br />
Please create 2 azure databases as per your choice.<br />
<br />
Here I am naming them as.<br />
<br />
<b>DbCustomers </b>and <b>DbPayments</b> and keep a reference to them in my examples.<br />
<br />
In <b>DbCustomers</b> create table "<b>LogLogin</b>" with following fields.<br />
[Id] [int] NOT NULL,<br />
<div class="MsoPlainText">
[LoginID] [nvarchar](50) NULL</div>
<br />
Now the main code of cross reference starts.<br />
<br />
In "<b>DbPayments</b> <b>"</b> use the following code<br />
--==================<br />
<span style="background-color: white; color: red;"><b>Note - Marked bold and red are the parameters</b></span><br />
<br />
<div class="MsoPlainText">
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<b><span style="color: red;">jkl$7k@fylj</span></b>';</div>
<div class="MsoPlainText">
GO</div>
<div class="MsoPlainText">
CREATE DATABASE SCOPED CREDENTIAL <span style="color: red;"><b>DbCustomers_Credential</b> </span>WITH IDENTITY = '<b><span style="color: red;">UserName</span></b>', SECRET =
'<b><span style="color: red;">Password</span></b>';</div>
<div class="MsoPlainText">
GO</div>
<div class="MsoPlainText">
<br /></div>
<div class="MsoPlainText">
CREATE EXTERNAL DATA SOURCE <b><span style="color: red;">DbCustomers_Datasource</span></b></div>
<div class="MsoPlainText">
WITH (</div>
<div class="MsoPlainText">
TYPE=RDBMS,</div>
<div class="MsoPlainText">
LOCATION='<span style="color: red;"><b>YourServerName</b></span>',</div>
<div class="MsoPlainText">
DATABASE_NAME='<b style="color: red;">YourDatabaseName</b>', -- <b>DbCustomers</b> </div>
<div class="MsoPlainText">
CREDENTIAL = <b style="color: red;">DbCustomers_Credential</b><span style="color: red;"> </span></div>
<div class="MsoPlainText">
);</div>
<div class="MsoPlainText">
<br /></div>
<div class="MsoPlainText">
GO</div>
<div class="MsoPlainText">
CREATE EXTERNAL TABLE [dbo].[LogLogin] (</div>
<div class="MsoPlainText">
[Id]
[int] NOT NULL,</div>
<div class="MsoPlainText">
[LoginID]
[nvarchar](50) NULL</div>
<br />
<div class="MsoPlainText">
) WITH ( DATA_SOURCE = <b><span style="color: red;">DbCustomers_Datasource</span></b>)</div>
<div class="MsoPlainText">
<br /></div>
<div class="MsoPlainText">
--==================================================</div>
<div class="MsoPlainText">
<br /></div>
<div class="MsoPlainText">
The above code will create an external table and show the table in "<b>DbPayments</b>" to which you can use simply by writing a SQL query.</div>
<div class="MsoPlainText">
<br /></div>
<div class="MsoPlainText">
Select * from LogLogin or use anywhere is the stored procedures.</div>
<div class="MsoPlainText">
<br /></div>
<div class="MsoPlainText">
<br /></div>
</div>
An IT Solutionhttp://www.blogger.com/profile/11201763791917815178noreply@blogger.com2Bahadurpur Rajoa, Punjab, India31.7468168 75.524758431.7198098 75.4844179 31.773823800000002 75.5650989tag:blogger.com,1999:blog-4318965216733027282.post-28889905394647816732014-06-30T23:45:00.001-07:002014-06-30T23:45:16.044-07:00How to add Primary Key and Auto Increment on different fields of MySql<div dir="ltr" style="text-align: left;" trbidi="on">
<b> Normal code generated by MySql:-</b><div>
<br /><div>
<div>
CREATE TABLE `categorymaster` (</div>
<div>
`<b>CategoryID</b>` int(11) NOT NULL AUTO_INCREMENT,</div>
<div>
`<b>Category</b>` varchar(200) DEFAULT NULL,</div>
<div>
`IsActiv` int(11) DEFAULT NULL,</div>
<div>
`ImagePath` varchar(200) DEFAULT NULL,</div>
<div>
`DisplayOrder` int(11) DEFAULT NULL,</div>
<div>
<span style="color: red;"><b>PRIMARY KEY</b></span> (`<b>Category</b>`)</div>
<div>
) ENGINE=InnoDB DEFAULT CHARSET=utf8;</div>
</div>
</div>
<div>
<br /></div>
<div>
In the above data stucture I want to make the "<b>Category</b>" as Primary Key and "<b>CategoryId</b>" as Auto Increment.</div>
<div>
<br /></div>
<div>
<span style="background-color: white;">But Table Wizard always give errors so, I decided to write my own Statement.</span></div>
<div>
<span style="background-color: white;"><br /></span></div>
<div>
<span style="background-color: white;">Following is the statement which will create perfect structure according to my requirement.</span></div>
<div>
<span style="background-color: white;"><br /></span></div>
<div>
<span style="background-color: white;"><div>
DROP TABLE `dbname`.`categorymaster`;</div>
<div>
CREATE TABLE `categorymaster` (</div>
<div>
<b>`CategoryId` int(11) unsigned NOT NULL AUTO_INCREMENT,</b></div>
<div>
`Category` varchar(200) DEFAULT NULL,</div>
<div>
`IsActiv` int(11) DEFAULT NULL,</div>
<div>
`ImagePath` varchar(200) DEFAULT NULL,</div>
<div>
`DisplayOrder` int(11) DEFAULT NULL,</div>
<div>
<b><span style="color: red;">UNIQUE KEY</span> `CategoryId` (`CategoryId`),</b></div>
<div>
PRIMARY KEY (`Category`)</div>
<div>
) ENGINE=InnoDB DEFAULT CHARSET=utf8;</div>
</span></div>
</div>
Anonymoushttp://www.blogger.com/profile/08251499341033355118noreply@blogger.com42tag:blogger.com,1999:blog-4318965216733027282.post-71077481139977564462014-02-07T04:35:00.000-08:002014-02-07T04:35:00.076-08:00How to create User Login in Sql Server 2005/2008<div dir="ltr" style="text-align: left;" trbidi="on">
How to create User Login in Sql Server 2005/2008<br />
Note - You must be a member of "system admin" to create a new user or database.<br />
<br />
Following is the steps to create user login in database for database level security :-<br />
Step 1 .<br />
<br />
Open Sql Server >> Enter User Name >> Enter Password >> Click Connect<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://1.bp.blogspot.com/-sfy61Q-xH8s/UvTDwL30WrI/AAAAAAAAAAg/MJ3hhy97rKU/s1600/untitled.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://1.bp.blogspot.com/-sfy61Q-xH8s/UvTDwL30WrI/AAAAAAAAAAg/MJ3hhy97rKU/s1600/untitled.PNG" height="251" width="400" /></a></div>
<br />
Step 2 .<br />
Open Security >> Logins >> Right Click >> New Login<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-JNMHF-SqxiY/UvTEVDbwPpI/AAAAAAAAAAo/3VgO5u8rTAE/s1600/1.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://2.bp.blogspot.com/-JNMHF-SqxiY/UvTEVDbwPpI/AAAAAAAAAAo/3VgO5u8rTAE/s1600/1.PNG" height="320" width="280" /></a></div>
<br />
Step 3.<br />
Enter login name >> Select Radio button "Sql Server Authentication" >>Enter password>>Enter confirm password>> Un-Check Enforce password policy >> Click Ok<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://3.bp.blogspot.com/-qi3MbPwh3Mk/UvTGfVpiu3I/AAAAAAAAAA8/sdYRRvMoFWc/s1600/3.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://3.bp.blogspot.com/-qi3MbPwh3Mk/UvTGfVpiu3I/AAAAAAAAAA8/sdYRRvMoFWc/s1600/3.PNG" height="285" width="320" /></a></div>
<br />
Step 4.<br />
Right click on Databases >> Click new database<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-w5W3Z93_Y88/UvTIeyN_7AI/AAAAAAAAABI/xfGrg9_uKJA/s1600/4.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://2.bp.blogspot.com/-w5W3Z93_Y88/UvTIeyN_7AI/AAAAAAAAABI/xfGrg9_uKJA/s1600/4.PNG" height="250" width="320" /></a></div>
<br />
<br />
Step 5.<br />
Enter Database name>> Owner "<default>" >> Click Ok<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://1.bp.blogspot.com/-8PqsOQoiOks/UvTJc4gMaFI/AAAAAAAAABU/DkbdeCgFCQI/s1600/5.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://1.bp.blogspot.com/-8PqsOQoiOks/UvTJc4gMaFI/AAAAAAAAABU/DkbdeCgFCQI/s1600/5.PNG" height="284" width="320" /></a></div>
<br />
Step 6 .<br />
Open Newly created database (My Case it is Test)>> Security>> Users>> Right click on dbo >> New users.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://4.bp.blogspot.com/-LW0Ix5DT_q0/UvTKiVQxEXI/AAAAAAAAABg/JDvQA5yypC8/s1600/6.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://4.bp.blogspot.com/-LW0Ix5DT_q0/UvTKiVQxEXI/AAAAAAAAABg/JDvQA5yypC8/s1600/6.PNG" height="320" width="244" /></a></div>
Step 7.<br />
Enter user name>> Login name >> default schema enter "dbo">> Celect check box db_owner>> Select check box db_owner >> Click Ok<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://3.bp.blogspot.com/-0HWwGWyLPt0/UvTMFFAMnpI/AAAAAAAAABs/pHFD3yOgnvQ/s1600/7.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://3.bp.blogspot.com/-0HWwGWyLPt0/UvTMFFAMnpI/AAAAAAAAABs/pHFD3yOgnvQ/s1600/7.PNG" height="284" width="320" /></a></div>
<br />
You are done with user creation.<br />
To make sure user is created and working, please re-login using your newly created user name.<br />
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<br /></div>
Anonymoushttp://www.blogger.com/profile/08251499341033355118noreply@blogger.com3tag:blogger.com,1999:blog-4318965216733027282.post-12849099422226488862013-01-12T01:36:00.001-08:002013-01-12T01:36:40.693-08:00How to Expose/Access Sql Server to Internet<div dir="ltr" style="text-align: left;" trbidi="on">
<b>Task - Expose Sel Server to Internet or Remote Access via IP</b><br />
OS - Windows Server 2008 R2<br />
Sql Version - Sql Server 2008 R2<br />
<br />
To expose the server to internet we need open port for sql server (default port is 1433)<br />
<br />
To open a port in the Windows firewall for TCP access<br />
<br />
1. On the Start menu, click Run, type WF.msc, and then click OK.<br />
2 . In the Windows Firewall with Advanced Security, in the left pane, right-click Inbound Rules, and then click New Rule in the action pane (upper right corner).<br />
3. In the Rule Type dialog box, select Port, and then click Next.<br />
4. In the Protocol and Ports dialog box, select TCP. Select Specific local ports, and then type the port number of the instance of the Database Engine, In my case we are using the default which is 1433. Click Next.<br />
5. In the Action dialog box, select Allow the connection, and then click Next.<br />
6. In the Profile dialog box, I am going to Leave Domain turned on and turn private and public off. Then click Next.<br />
7. In the Name dialog box, type "Allow SQL 1433 Inbound” and for a description I am putting in the same. Then click Finish.<br />
<br />
Then go to Sql Server Configuration Manager.<br />
1. On the Start menu, Click Microsoft Sql Server 2008 R2<br />
2. Click on Configuration Tools.<br />
3. Sql Server Configuration Manager.<br />
<br />
And follow the attached images<br />
<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://4.bp.blogspot.com/-qP3fNCMWpAc/UPEtHhHirDI/AAAAAAAAAOc/kT6T-Glr-fk/s1600/1.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="199" src="http://4.bp.blogspot.com/-qP3fNCMWpAc/UPEtHhHirDI/AAAAAAAAAOc/kT6T-Glr-fk/s320/1.png" width="320" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
================================</div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-a5wexoukzEw/UPEtQUmSpgI/AAAAAAAAAOk/CJYLrJJ3JcY/s1600/2.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="215" src="http://2.bp.blogspot.com/-a5wexoukzEw/UPEtQUmSpgI/AAAAAAAAAOk/CJYLrJJ3JcY/s320/2.png" width="320" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
Now you need to access the Sql server like "IP\sqlexpress" in name, please don't try only with IP address.</div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<br />
</div>
An IT Solutionhttp://www.blogger.com/profile/11201763791917815178noreply@blogger.com10tag:blogger.com,1999:blog-4318965216733027282.post-14406662674195573542012-04-07T06:30:00.001-07:002012-04-07T06:40:38.877-07:00Server Application Unavailable IIS Error<div dir="ltr" style="text-align: left;" trbidi="on">Generally the cause of Server Application Unavailable is the .net framework.<br />
<br />
Error Snapshot<br />
<div class="separator" style="clear: both; text-align: center;"><a href="http://1.bp.blogspot.com/-ZaLzau7iKzM/T4A_aeOZvzI/AAAAAAAAANQ/I3aXeOBlbUA/s1600/Error.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="53" src="http://1.bp.blogspot.com/-ZaLzau7iKzM/T4A_aeOZvzI/AAAAAAAAANQ/I3aXeOBlbUA/s320/Error.png" width="320" /></a></div><br />
For ex. one of our site is in version 1.1 and the recently added site is in 2.0 or later or any other different versions running on the IIS.<br />
<br />
How to Resolve:-<br />
We need to create a new application pool on the IIS and assign the same to the version of the site.<br />
Following image illustrates how to check the version of site.<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="http://1.bp.blogspot.com/-sUHg62KAz7g/T4BASUR3t-I/AAAAAAAAANY/_hE7yECpdL4/s1600/4_0.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="309" src="http://1.bp.blogspot.com/-sUHg62KAz7g/T4BASUR3t-I/AAAAAAAAANY/_hE7yECpdL4/s320/4_0.png" width="320" /></a></div><br />
<br />
For ex. Pool3_5 for version 3.5 and Pool4_0 version 4.0 .<br />
Following image illustrates how to create new Pool.<br />
<br />
Step 1:<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="http://1.bp.blogspot.com/-spIhQlFwuDw/T4BAfIbXQ8I/AAAAAAAAANg/yKXpdjb0LsI/s1600/NewPool1.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="215" src="http://1.bp.blogspot.com/-spIhQlFwuDw/T4BAfIbXQ8I/AAAAAAAAANg/yKXpdjb0LsI/s320/NewPool1.png" width="320" /></a></div><br />
<br />
<br />
<br />
Step 2:<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="http://1.bp.blogspot.com/-hsec0_Z7AUQ/T4BAotBlAcI/AAAAAAAAANo/TUAkEjQJqAw/s1600/NewPool2.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="187" src="http://1.bp.blogspot.com/-hsec0_Z7AUQ/T4BAotBlAcI/AAAAAAAAANo/TUAkEjQJqAw/s320/NewPool2.png" width="320" /></a></div><br />
Step 3:<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="http://3.bp.blogspot.com/-4geycReQ70o/T4BAyTPLIMI/AAAAAAAAANw/H-1YlhbDCeY/s1600/NewPool3.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="310" src="http://3.bp.blogspot.com/-4geycReQ70o/T4BAyTPLIMI/AAAAAAAAANw/H-1YlhbDCeY/s320/NewPool3.png" width="320" /></a></div><br />
<br />
<br />
You can also verify the IIS logs to locate the issue.</div>An IT Solutionhttp://www.blogger.com/profile/11201763791917815178noreply@blogger.com9tag:blogger.com,1999:blog-4318965216733027282.post-30045561135394287842012-04-05T05:26:00.002-07:002012-04-05T05:27:29.742-07:00How to make a site default on IIS 6 Windows Server 2003<div dir="ltr" style="text-align: left;" trbidi="on">While setting up an new Web server with Windows Server 2003 and IIS 6.<br />
<br />
I have to set up my own custom domain (for ex. myname.com) as the default site on IIS. Means if I will run the server from IP my custom domain has to run.<br />
<br />
The process is so, easy we need to setup few things in custom site.<br />
<br />
Right click on Site>>Properties>>WebSite>>Click on Advnaced Button>>Leave the "Host header value" to be blank.<br />
<br />
Please refer the image..<br />
<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="http://3.bp.blogspot.com/-5cs1ZpHLJi4/T32OyCvQM8I/AAAAAAAAANI/UH_eMvGuPCk/s1600/Default.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="116" src="http://3.bp.blogspot.com/-5cs1ZpHLJi4/T32OyCvQM8I/AAAAAAAAANI/UH_eMvGuPCk/s320/Default.png" width="320" /></a></div><br />
</div>An IT Solutionhttp://www.blogger.com/profile/11201763791917815178noreply@blogger.com2Punjab, India31.750795683985153 75.51795921972654930.269911683985153 73.992524219726548 33.231679683985156 77.043394219726551tag:blogger.com,1999:blog-4318965216733027282.post-79493932365692382352011-11-08T03:37:00.000-08:002011-11-08T03:37:31.219-08:00Windows Event Starting up database DatabaseName<div dir="ltr" style="text-align: left;" trbidi="on">Configuration:-<br />
Windows Server 2003 R2 32 bit<br />
Sql Express 2008 R2<br />
<br />
While checking for server event as a part of maintenance we a information which is occuring after every 1-10 seconds the information is Event "Starting up database DatabaseName"<br />
<br />
We found the solution and implemnted the same.<br />
<br />
You need to open "Sql Express Management Studio>>Database>>Properties>>Options>>Auto Close" and set it to false.<br />
<br />
For more information on this property, please refer<br />
<a href="http://blogs.msdn.com/b/buckwoody/archive/2009/06/24/sql-server-best-practices-autoclose-should-be-off.aspx">http://blogs.msdn.com/b/buckwoody/archive/2009/06/24/sql-server-best-practices-autoclose-should-be-off.aspx</a><br />
<a href="http://msdn.microsoft.com/en-us/library/ms135094%28SQL.90%29.aspx">http://msdn.microsoft.com/en-us/library/ms135094%28SQL.90%29.aspx</a><br />
<a href="http://www.anitsolution.com/blog/autoclose.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="165" src="http://www.anitsolution.com/blog/autoclose.png" width="320" /></a> </div>An IT Solutionhttp://www.blogger.com/profile/11201763791917815178noreply@blogger.com2tag:blogger.com,1999:blog-4318965216733027282.post-23649052311181195562011-10-10T02:41:00.000-07:002011-10-10T02:45:35.999-07:00How to set focus on Table row using Javascript<div dir="ltr" style="text-align: left;" trbidi="on">We are developing Application for Google Smart TV and all is going well.<br />
<br />
We are using Table, Div or some other elements which cannot be focused on using .focus() of java script.<br />
<br />
We have used Table to show data in one of our page. Our target is to set the focus on first element of table i.e. first row.<br />
<br />
We tried different solutions suggested on web but nothing worked. All the posts are suggesting to use focusable controls like text box, check box etc.<br />
<br />
<a href="http://www.blogger.com/Finally%20we%20found%20solution%20on%20http://groups.google.com/group/mozilla.dev.tech.css/browse_thread/thread/42591922d95c6399">Finally we found solution on http://groups.google.com/group/mozilla.dev.tech.css/browse_thread/thread/42591922d95c6399</a><br />
<br />
In the post "Stanimir Stamenkov" has used a term "if you just define a 'tabindex' on the element it becomes focusable" I assigned "tabindex" property to table row and then used .focus() and the focus reached to row.<br />
<br />
<br />
Hope this will help all who are looking for such nice solutions.</div>An IT Solutionhttp://www.blogger.com/profile/11201763791917815178noreply@blogger.com1tag:blogger.com,1999:blog-4318965216733027282.post-13555337154473098882011-06-15T06:08:00.000-07:002011-06-15T06:08:45.552-07:00How to allow special characters in URL or query string<div dir="ltr" style="text-align: left;" trbidi="on">I am using asp.net 4.0 with Sql server. I have an requirement to pass special characters in URL query string.<br />
<br />
for example my URL need to be like http://mysite.com/default.aspx?a=xyx(abc)<br />
In the above URL my special character is "(" and ")". While development I have not faced any issue but as I uploaded the files to serve it start giving error 404 (page not found) where ever I have use "(" or ")" special characters.<br />
<br />
When I started searching I have tried many solutions. but nothing works for me.<br />
Then on some site some one has sugested to check IIS log.<br />
<br />
When I checked the log I found that I have Installed Microsoft URL Scan 3.1 on my server which is filtering the URL.<br />
<br />
So, I need to allow "(" and ")" on my server so, I decided to allow there characters in my URL.<br />
<br />
So, to add exception to URL Scan 3.1 you need to open urlscan.ini which you can find in "%windir%\system32\inetsrv\urlscan\"<br />
<br />
You can use 2 sections to allow special characters<br />
1) DenyQueryStringSequences<br />
2) AlwaysAllowedUrls<br />
<br />
<br />
DenyQueryStringSequences - In this section you can add and remove the allowed characters but this will allow for all the URLs.<br />
<br />
AlwaysAllowedUrls - In this you can define a single URL like "/default.aspx".So, the special characters will be allowed to only single URL not to all the URL of the IIS sites<br />
<br />
</div>An IT Solutionhttp://www.blogger.com/profile/11201763791917815178noreply@blogger.com3tag:blogger.com,1999:blog-4318965216733027282.post-11626335254753939162011-03-18T23:45:00.000-07:002011-03-18T23:45:44.710-07:00How to select data from another sql server<div dir="ltr" style="text-align: left;" trbidi="on">We have a target to run a JOB on server to import data from another SQL server instance.<br />
<br />
For example take<br />
Server 1 - Main<br />
Server 2 - From which we have to import.<br />
<br />
If you have admin rights on both the servers you can "Link the servers" under "Server Objects>>Linked Servers" Right click on the same and provide all the parameters of server or as an alternate you can run following command.<br />
<br />
EXEC sp_addlinkedserver<br />
@server = 'db1',<br />
@srvproduct = 'SQLServer OLEDB Provider',<br />
@provider = 'SQLOLEDB',<br />
@datasrc = 'server2',<br />
@provstr='User Id=sa; Password=abc'<br />
<br />
The above command parameters:-<br />
@server- An identification name of server.<br />
@datasrc- Your server instance name or IP.<br />
<br />
Your SQL will look like:-<br />
Select * from db1.dbname.dbo.tablename<br />
<br />
If sometimes the server will get changed, the above statement gives you the option to change the user name, password and server at any points of time without effecting the relevant SQL and SP written the basis of same.<br />
<br />
Second Method (You can use the same if you does not have admin rights to add Linked server)<br />
<br />
SELECT *<br />
FROM OPENDATASOURCE(<br />
'SQLOLEDB',<br />
'Data Source=Server2;User ID=sa;Password=abc'<br />
).dbname.dbo.tablename<br />
<br />
<br />
In the above case you have to hard code all the parameters which you have to change in all the SQL and SP if something will get changed in future. So, I prefer to use Linked Server </div>An IT Solutionhttp://www.blogger.com/profile/11201763791917815178noreply@blogger.com2tag:blogger.com,1999:blog-4318965216733027282.post-33104478416119024292011-02-03T07:08:00.000-08:002011-02-03T07:08:39.278-08:00How to resolve "crdb_adoplus.dl" issue<div dir="ltr" style="text-align: left;" trbidi="on">With the launch of VS 2010, we start using VS 2010 and used the new improved IDE and features of VS 2010.<br />
<br />
While woring with a windows based application we have requirement to use Crystal Report. We are able to add and design a new Crystal Report without facing any problem.<br />
<br />
After writing the code to show data, we start getting a unique problem of "crdb_adoplus.dl". We searched for many solution and finally find the solution.<br />
<br />
We added the following attributes in app.config<br />
<br />
<startup useLegacyV2RuntimeActivationPolicy="true"><br />
<supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.0"/><br />
</startup><br />
<br />
So, the final app.config will be :-<br />
<br />
<?xml version="1.0" encoding="utf-8" ?><br />
<configuration><br />
<br />
</connectionStrings><br />
<startup useLegacyV2RuntimeActivationPolicy="true"><br />
<supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.0"/><br />
</startup><br />
</configuration><br />
<br />
Reference : <br />
<br />
<a href="http://social.msdn.microsoft.com/Forums/eu/vscrystalreports/thread/2503389c-6d25-48d3-bb15-25ed67f52f21">http://social.msdn.microsoft.com/Forums/eu/vscrystalreports/thread/2503389c-6d25-48d3-bb15-25ed67f52f21</a></div>An IT Solutionhttp://www.blogger.com/profile/11201763791917815178noreply@blogger.com1tag:blogger.com,1999:blog-4318965216733027282.post-53275618061088355252010-12-24T00:05:00.000-08:002010-12-24T00:05:22.317-08:00How to set max limt of File Upload in Asp.netMany times I faced the same problem with all my applications, that is Max size of file to be uploaded to server.<br />
<br />
By default, the maximum size limit of a file to be uploaded to a server using the ASP.NET FileUpload control is 4MB. You cannot upload anything that is larger than this limit. <br />
<br />
If you wants to upload a file more the size of 4 MB which is by default, you have to make some changes in the application's web.config:<br />
<br />
<configuration><br />
<system.web><br />
<httpRuntime maxRequestLength="xxx" /><br />
</system.web><br />
</configuration><br />
<br />
Below is the small description of the parameters attributes.<br />
<br />
<b><i>maxRequestLength</i></b> - Attribute limits the file upload size for ASP.NET application. This limit can be used to prevent denial of service attacks (DOS) caused by users posting large files to the server. The size specified is in kilobytes. As mentioned earlier, the default is "4096" (4 MB). Max value is "1048576" (1 GB) for .NET Framework 1.0/1.1 and "2097151" (2 GB) for .NET Framework 2.0.<br />
<br />
<b><i>executionTimeout</i></b> - Attribute indicates the maximum number of seconds that a request is allowed to execute before being automatically shut down by the application. The executionTimeout value should always be longer than the amount of time that the upload process can take.An IT Solutionhttp://www.blogger.com/profile/11201763791917815178noreply@blogger.com1tag:blogger.com,1999:blog-4318965216733027282.post-33865416300482986792010-04-15T04:39:00.000-07:002010-04-15T04:39:03.864-07:00How to Get/Set date time according to Different Time zones in C#<a href="http://www.4shared.com/file/GbYoHLMu/AnItSolutionTimeZones.html"><img src="http://i540.photobucket.com/albums/gg331/narendersinghkahlon/download.png" alt="download"/>Download code example</a><br />
<br />
In an Asp.net application I have developed a Forum in which I have requirement to show date and time according to the time zone of PC on which it is running, I had searched for too many examples on but all are using too complex java scripts and other methods to convert time into local time zone.<br />
<br />
Finally I decided to make my own solution for this problem<br />
My solution is using “TimeZoneInfo” to convert the data to local time zone, this class allow me to convert time according to any time zone, but again I got another problem which is how to take time zone of browser. Again I search on net and finally come out with a code.<br />
<br />
<script language="javascript" type="text/javascript"><br />
function genarateDateValue()<br />
{ <br />
var d = new Date() <br />
var str=d.toString();<br />
var i = str.indexOf("+"); <br />
var j = str.length- i;<br />
<br />
str=str.substring(i, i+5); <br />
setCookie("narender",str,365);<br />
return true;<br />
}<br />
<br />
function setCookie(c_name,value,expiredays)<br />
{ <br />
var exdate=new Date();<br />
exdate.setDate(exdate.getDate()+expiredays);<br />
document.cookie=c_name+ "=" +escape(value)+((expiredays==null) ? "" : ";expires="+exdate.toGMTString());<br />
} <br />
</script><br />
<br />
In Sql Server still there is no method to covert from one time zone to another but My Sql Support converting date time according to provided time zone. <br />
Following is the simple code runs on My Sql Prompt:-<br />
CONVERT_TZ(dt,from_tz,to_tz)<br />
CONVERT_TZ() converts a datetime value dt from the time zone given by from_tz to the time zone given by to_tz and returns the resulting value. This function returns NULL if the arguments are invalid.<br />
If the value falls out of the supported range of the TIMESTAMP type when converted from from_tz to UTC, no conversion occurs. <br />
mysql> SELECT CONVERT_TZ('2004-01-01 12:00:00','GMT','MET');<br />
-> '2004-01-01 13:00:00'<br />
mysql> SELECT CONVERT_TZ('2004-01-01 12:00:00','+00:00','+10:00');<br />
-> '2004-01-01 22:00:00'<br />
<br />
Above is the method which we can use in My Sql which will return all the values from Database itself but if we are using Sql server we need to write our own code to convert time zone in C# code.<br />
You can also download this code from link provided on top on this post.<br />
public string DateFormat(string str)<br />
{<br />
ReadOnlyCollection<TimeZoneInfo> timeZones = TimeZoneInfo.GetSystemTimeZones();<br />
foreach (TimeZoneInfo timeZone in timeZones)<br />
{<br />
TimeSpan offsetFromUtc = timeZone.BaseUtcOffset;<br />
string offsetString;<br />
string strHours = string.Empty;<br />
string strMinutes = string.Empty;<br />
strHours = offsetFromUtc.Hours.ToString();<br />
strMinutes = offsetFromUtc.Minutes.ToString();<br />
if (strMinutes.Length == 1)<br />
{<br />
strMinutes = "0" + strMinutes;<br />
}<br />
if (strHours.Length == 1)<br />
{<br />
strHours = "0" + strHours;<br />
}<br />
<br />
if (Convert.ToInt32(offsetFromUtc.Hours) >= 0)<br />
{<br />
strHours = "+" + strHours;<br />
}<br />
offsetString = strHours + strMinutes;<br />
if (Request.Cookies["narender"] != null)<br />
{<br />
if (Request.Cookies["narender"].Value.ToString() == offsetString)<br />
{<br />
if (str != "")<br />
{<br />
DateTime thisTime = Convert.ToDateTime(str);<br />
TimeZoneInfo tst1 = TimeZoneInfo.FindSystemTimeZoneById(timeZone.Id);<br />
DateTime tstTime1 = TimeZoneInfo.ConvertTime(thisTime, TimeZoneInfo.Local, tst1);<br />
str = Convert.ToDateTime(tstTime1).ToString("dddd,MMMM dd,yyyy hh:mm:ss tt");<br />
}<br />
return str;<br />
}<br />
}<br />
}<br />
return str;<br />
}An IT Solutionhttp://www.blogger.com/profile/11201763791917815178noreply@blogger.com2tag:blogger.com,1999:blog-4318965216733027282.post-47519147165983009662010-03-27T04:12:00.000-07:002010-03-27T04:28:36.958-07:00How to zip a file or folder using C#<a href="http://www.4shared.com/file/250511543/9c4c0fc1/AnItSolutionZipFile.html"><img src="http://i540.photobucket.com/albums/gg331/narendersinghkahlon/download.png" alt="download" />Download code example</a><br /><br /><br />I had searched a lot to make files zip using c#, all the solution provided only support to zip a single file but my requirement is to zip a directory selected by used, I had tried so many utilities but unfortunately all does not works for me.<br /><br />Finally I found a solution which is using Java utility to zip file. This solution is quite easy to implement and debug.<br /><br />To implement this solution you need to add reference of <span style="font-weight: bold;">“C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\vjslib.dll”</span> to your application.<a href="http://www.4shared.com/file/250511543/9c4c0fc1/AnItSolutionZipFile.html"><br /></a>An IT Solutionhttp://www.blogger.com/profile/11201763791917815178noreply@blogger.com1tag:blogger.com,1999:blog-4318965216733027282.post-75685173230031652172010-02-20T03:24:00.000-08:002010-02-20T03:36:20.522-08:00How to pass server values to Javascript using Eval()In my application I have a target to show message if user does not have permission to navigate to another page. I can do this by using server code but it will make a round trip to server and make a bad user experience so, I decided to use java script.<br /><br />Following is the function of Java Script<br /><br /><script language="javascript" type="text/javascript"><br />function func_Message(obj)<br />{ <br /> if (parseFloat(obj)>0) <br /> {<br /> return true;<br /> }<br /> else<br /> {<br /> alert("Please assign a value before viewing the details");<br /> return false;<br /> }<br />}<br /></script><br /><br /><br />And following is the code of Link Button from where I am passing value to Java Script Function<br /><br /> <asp:LinkButton ID="lnk_Details" runat="server" Text="Details" CommandArgument='<%# Eval("festi_id") +"~" + Eval("user_id") %>'<br /> CommandName="ViewDetails" OnClientClick='<%# "return func_Message(" + Eval("user_id") + ");"%>'></asp:LinkButton>An IT Solutionhttp://www.blogger.com/profile/11201763791917815178noreply@blogger.com1tag:blogger.com,1999:blog-4318965216733027282.post-42174801425353809912010-02-20T01:24:00.000-08:002010-04-06T23:50:10.025-07:00Disbale JIT in .net / Application level Exception handling in .net<a href="http://www.4shared.com/file/225871583/5bda800d/JITHandling.html"><img src="http://i540.photobucket.com/albums/gg331/narendersinghkahlon/download.png" alt="download" />Download Code Example</a><br /><br />I was developing windows application for a well known company of World. This application was in C#.net 2005.<br /><br />As per requirement I have to show user defined message whenever an Exception occurs in the application.<br /><br />The answer is very simply write a simple Try{},Catch{} statement to handle exception.<br />Suppose I have a code of 1000 line unwanted code and error handling creates extra thread while executing the application. I had search on the internet and found a solution to disable the JIT debugger. It works but still it is showing the application information related to functions etc. used in the code.<br /><br />Now, a major issue had risen from client, i.e. he don’t want to show JIT information to user he wants to show his own message whenever any Exception occurs.<br /><br />Offf…. Now that’s the task to which needs to done. I cannot use Try{},Catch{} as this decrease the application performance. Finally I came to the solution. I had wired an event with application to handle application error. Here is the code.<br /><br />static void Main()<br /> { <br /><span style="font-weight: bold;"> Application.ThreadException += new ThreadExceptionEventHandler(Application_ThreadException);</span><br /> Application.EnableVisualStyles();<br /> Application.SetCompatibleTextRenderingDefault(false);<br /> Application.Run(new Form1());<br /> }<br /><br /><span style="font-weight: bold;"> static void Application_ThreadException(object sender, ThreadExceptionEventArgs e)</span><br /><span style="font-weight: bold;"> {</span><br /><span style="font-weight: bold;"> MessageBox.Show("Illegal operation or command.", "Illegal operation", MessageBoxButtons.OK, MessageBoxIcon.Warning);</span><br /><span style="font-weight: bold;"> }</span><br />Now, run your code and click on the button, it will show are exception while you are running the application within the .net environment.<br /><br />Now browser the directory and Run you EXE file and again click in the button, <span style="font-weight: bold;">now it will not show exception rather show the message which you wants to display.</span><br /><br />This is a good approach it will show exception and source in while debugging and show and user defined message in EXE<br /><a href="http://www.4shared.com/file/225871583/5bda800d/JITHandling.html"><br /><br /><br /></a>An IT Solutionhttp://www.blogger.com/profile/11201763791917815178noreply@blogger.com1tag:blogger.com,1999:blog-4318965216733027282.post-63191822600621224102010-02-09T02:55:00.000-08:002010-02-09T03:52:48.853-08:00How to alter Auto Increment field in Sql serverIt is not possible to remove Auto Increment field using Alter command in Sql Server.<br /><br />What we can use is <span style="font-weight: bold;">SET IDENTITY_INSERT</span><br /><br />Take this following example this creates a table with auto increment field and still insert data to the same.<br /><br />Generally if you try to insert the data into auto increment field it will through error.<br /><br />But if we use <span style="font-weight: bold;">SET IDENTITY_INSERT <Table Name>ON</span> it will allow you to insert data into auto increment field.<br /><br /><span style="font-weight: bold;">Note:</span> Make sure after completing this operation you set the value to <span style="font-weight: bold;">SET IDENTITY_INSERT <Table Name>OFF</span><br /><br />--Create table and its columns<br />CREATE TABLE [test].[Table_1] (<br /> [Id][int] NOT NULL IDENTITY (1, 1),<br /> [Name][nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL);<br />GO<br /><br />ALTER TABLE [test].[Table_1] ADD CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED ([Id])<br />GO<br /><br />SET IDENTITY_INSERT [test].[Table_1] ON<br />GO<br />INSERT INTO [test].[Table_1] ([Id], [Name])<br /> VALUES (1, 'Narender')<br /><br />GO<br />INSERT INTO [test].[Table_1] ([Id], [Name])<br /> VALUES (3, 'Rohit')<br /><br />GO<br />INSERT INTO [test].[Table_1] ([Id], [Name])<br /> VALUES (4, 'Mohit')<br /><br />GO<br />INSERT INTO [test].[Table_1] ([Id], [Name])<br /> VALUES (5, 'Shipra')<br /><br />GO<br />SET IDENTITY_INSERT [test].[Table_1] OFF<br />GO<br /><br />The above example simply insert the data into table without incrementing the values and keep the error shut..<br /><br />If you try to insert any data after <span style="font-weight: bold;">SET IDENTITY_INSERT [test].[Table_1] OFF</span> it will through an error.An IT Solutionhttp://www.blogger.com/profile/11201763791917815178noreply@blogger.com1tag:blogger.com,1999:blog-4318965216733027282.post-21500875615386838592010-01-19T23:04:00.000-08:002010-01-19T23:05:07.036-08:00C# too many database connections errorEvery database is having some number of connection limits which you can increase up to an extend.<br /><br />But database can support up to an number of connections.<br /><br />This error occurs when we open a connection and forget to close that. Verify each line of your code and make sure you had closed the connection after completing your database operations.<br /><br />I had faced this error in C# My sql application. I had crossed verify my code and find a code where I am not closing my connection after completing my operations. I had simply write <span style="font-weight: bold;">connection.Close();</span> and it had worked.An IT Solutionhttp://www.blogger.com/profile/11201763791917815178noreply@blogger.com1tag:blogger.com,1999:blog-4318965216733027282.post-51354816178767538312009-12-29T22:17:00.000-08:002009-12-29T22:27:38.768-08:00SQL Function Reference:Oracle vs.SQL Server vs. My SQL Part- III<div style="text-align: center; font-weight: bold;">Date Functions<br /><div style="text-align: left;"><br /><span style="font-style: italic;">Function - Date addition</span><br /><span style="font-weight: normal;">Oracle - (use +)</span><br /><span style="font-weight: normal;">SQL Server - DATEADD</span><br /><span style="font-weight: normal;"> My Sql - Need To Update</span><br /><br /><br /> <span style="font-style: italic;">Function - Date subtraction</span><br /><span style="font-weight: normal;">Oracle - (use -)</span><br /> <span style="font-weight: normal;">SQL Server - DATEDIFF</span><br /> <span style="font-weight: normal;"> My Sql - Need To Update</span><br /><br /><br /> <span style="font-style: italic;">Function - Last day of month</span><br /> <span style="font-weight: normal;">Oracle - LAST_DAY</span><br /> <span style="font-weight: normal;">SQL Server - N/A</span><br /> <span style="font-weight: normal;"> My Sql - Need To Update</span><br /><br /> <span style="font-style: italic;">Function -Time zone conversion</span><br /> <span style="font-weight: normal;">Oracle - NEW_TIME</span><br /> <span style="font-weight: normal;">SQL Server - N/A</span><br /> <span style="font-weight: normal;"> My Sql - Need To Update</span><br /><br /> <span style="font-style: italic;">Function -First weekday after date</span><br /> <span style="font-weight: normal;">Oracle - NEXT_DAY</span><br /> <span style="font-weight: normal;">SQL Server - N/A</span><br /> <span style="font-weight: normal;"> My Sql - Need To Update</span><br /> <br /><span style="font-style: italic;">Function -Convert date to string</span><br /> <span style="font-weight: normal;">Oracle -TO_CHAR</span><br /> <span style="font-weight: normal;">SQL Server - DATENAME</span><br /> <span style="font-weight: normal;"> My Sql - Need To Update</span><br /><br /><span style="font-style: italic;">Function -Convert date to number</span><br /> <span style="font-weight: normal;">Oracle -TO_NUMBER(TO_CHAR())</span><br /> <span style="font-weight: normal;">SQL Server - TO_NUMBER(TO_CHAR())</span><br /> <span style="font-weight: normal;"> My Sql - Need To Update</span><br /><br /><span style="font-style: italic;">Function -Convert string to date</span><br /> <span style="font-weight: normal;">Oracle -TO_NUMBER(TO_CHAR())</span><br /> <span style="font-weight: normal;">SQL Server - CAST</span><br /> <span style="font-weight: normal;"> My Sql - Need To Update</span><br /><br /><span style="font-style: italic;">Function -Get current date and time</span><br /> <span style="font-weight: normal;">Oracle -SYSDATE</span><br /> <span style="font-weight: normal;">SQL Server - GETDATE()</span><br /> <span style="font-weight: normal;"> My Sql - Now()</span></div></div>An IT Solutionhttp://www.blogger.com/profile/11201763791917815178noreply@blogger.com0tag:blogger.com,1999:blog-4318965216733027282.post-14934654589190228462009-12-29T22:01:00.000-08:002009-12-29T22:28:39.606-08:00SQL Function Reference:Oracle vs.SQL Server vs. My SQL Part- II<div style="text-align: center; font-weight: bold;">String Functions<br /><br /><div style="text-align: left;"><span style="font-style: italic;">Function - Convert character to ASCII</span><br /><span style="font-weight: normal;">Oracle - ASCII</span><br /><span style="font-weight: normal;">SQL Server - ASCII</span><br /><span style="font-weight: normal;"> My Sql - ASCII<br /><br /></span><div style="text-align: left;"><span style="font-style: italic;">Function -String concatenate</span><br /><span style="font-weight: normal;">Oracle - CONCAT</span><br /><span style="font-weight: normal;">SQL Server - (expression + expression)</span><br /><span style="font-weight: normal;"> My Sql - CONCAT<br /><br /></span><span style="font-style: italic;">Function -Convert ASCII to character</span><br /><span style="font-weight: normal;">Oracle - CHR</span><br /><span style="font-weight: normal;">SQL Server - CHAR</span><br /><span style="font-weight: normal;"> My Sql - CONCAT</span><br /></div><br /><span style="font-style: italic;">Function -Return starting point of character in character string (from left)</span><br /><span style="font-weight: normal;">Oracle - INSTR</span><br /><span style="font-weight: normal;">SQL Server - CHARINDEX</span><br /><span style="font-weight: normal;"> My Sql - INSTR</span><br /><br /><span style="font-style: italic;">Function -Convert characters to lowercase</span><br /><span style="font-weight: normal;">Oracle - LOWER</span><br /> <span style="font-weight: normal;">SQL Server - LOWER</span><br /> <span style="font-weight: normal;"> My Sql - LOWER</span><br /><br /><span style="font-style: italic;">Function -Convert characters to uppercase</span><br /><span style="font-weight: normal;">Oracle - UPPER</span><br /> <span style="font-weight: normal;">SQL Server - UPPER</span><br /> <span style="font-weight: normal;"> My Sql - UPPER</span><br /><br /><br /><span style="font-style: italic;">Function -Pad left side of character string</span><br /><span style="font-weight: normal;">Oracle - LPAD</span><br /> <span style="font-weight: normal;">SQL Server - N/A</span><br /> <span style="font-weight: normal;"> My Sql - LPAD</span><br /><br /><span style="font-style: italic;">Function -Remove leading blank spaces</span><br /><span style="font-weight: normal;">Oracle - LTRIM</span><br /> <span style="font-weight: normal;">SQL Server - LTRIM</span><br /> <span style="font-weight: normal;"> My Sql - LTRIM</span><br /><br /><br /><span style="font-style: italic;">Function -Remove trailing blank spaces</span><br /><span style="font-weight: normal;">Oracle - RTRIM</span><br /> <span style="font-weight: normal;">SQL Server -RTRIM</span><br /> <span style="font-weight: normal;"> My Sql - RTRIM</span><br /><br /><span style="font-style: italic;">Function -Starting point of pattern in character string</span><br /><span style="font-weight: normal;">Oracle - INSTR</span><br /> <span style="font-weight: normal;">SQL Server -PATINDEX</span><br /> <span style="font-weight: normal;"> My Sql - INSTR</span><br /><br /><span style="font-style: italic;">Function -Repeat character string multiple times</span><br /><span style="font-weight: normal;">Oracle - RPAD</span><br /> <span style="font-weight: normal;">SQL Server -REPLICATE</span><br /> <span style="font-weight: normal;"> My Sql - RPAD</span><br /><br /><span style="font-style: italic;">Function -Phonetic representation of character string</span><br /><span style="font-weight: normal;">Oracle - SOUNDEX</span><br /> <span style="font-weight: normal;">SQL Server -SOUNDEX</span><br /> <span style="font-weight: normal;"> My Sql - SOUNDEX</span><br /><br /><span style="font-style: italic;">Function -String of repeated spaces</span><br /><span style="font-weight: normal;">Oracle - RPAD</span><br /><span style="font-weight: normal;"> SQL Server -SPACE</span><br /><span style="font-weight: normal;"> My Sql - RPAD</span><br /><br /><span style="font-style: italic;">Function -Character data converted from numeric data</span><br /><span style="font-weight: normal;">Oracle - TO_CHAR</span><br /> <span style="font-weight: normal;">SQL Server -STR</span><br /> <span style="font-weight: normal;"> My Sql - Need To Update</span><br /><br /><span style="font-style: italic;">Function - Substring</span><br /><span style="font-weight: normal;">Oracle - SUBSTR</span><br /> <span style="font-weight: normal;">SQL Server -SUBSTRING</span><br /> <span style="font-weight: normal;"> My Sql - MID</span><br /><br /><span style="font-style: italic;">Function - Replace characters</span><br /><span style="font-weight: normal;">Oracle - REPLACE</span><br /> <span style="font-weight: normal;">SQL Server -STUFF</span><br /> <span style="font-weight: normal;"> My Sql - REPLACE</span><br /><br /><span style="font-style: italic;">Function - Capitalize first letter of each word in string</span><br /><span style="font-weight: normal;">Oracle - INITCAP</span><br /> <span style="font-weight: normal;">SQL Server -N/A</span><br /> <span style="font-weight: normal;"> My Sql - Need To Update</span><br /><br /><span style="font-style: italic;">Function - Translate character string</span><br /><span style="font-weight: normal;">Oracle -TRANSLATE</span><br /> <span style="font-weight: normal;">SQL Server -N/A</span><br /> <span style="font-weight: normal;"> My Sql - Need To Update</span><br /><br /><span style="font-style: italic;">Function - Length of character string</span><br /><span style="font-weight: normal;">Oracle -LENGTH</span><br /> <span style="font-weight: normal;">SQL Server - DATALENGTH or LEN</span><br /> <span style="font-weight: normal;"> My Sql - LENGTH</span><br /><br /><span style="font-style: italic;">Function - Greatest character string in list</span><br /><span style="font-weight: normal;">Oracle -GREATEST</span><br /> <span style="font-weight: normal;">SQL Server - N/A</span><br /> <span style="font-weight: normal;"> My Sql - Need To Update</span><br /><br /><span style="font-style: italic;">Function - Least character string in list</span><br /><span style="font-weight: normal;">Oracle -LEAST</span><br /> <span style="font-weight: normal;">SQL Server - N/A</span><br /> <span style="font-weight: normal;"> My Sql - Need To Update</span><br /><br /><span style="font-style: italic;">Function - Convert string if NULL</span><br /><span style="font-weight: normal;">Oracle -NVL</span><br /> <span style="font-weight: normal;">SQL Server - ISNULL</span><br /> <span style="font-weight: normal;"> My Sql - Need To Update<br /><br /><a href="http://an-it-solution.blogspot.com/2009/12/sql-function-referenceoracle-vssql_29.html">Visit Part III (Date Functions)</a><br /></span></div></div>An IT Solutionhttp://www.blogger.com/profile/11201763791917815178noreply@blogger.com0tag:blogger.com,1999:blog-4318965216733027282.post-90888170998767271682009-11-23T00:25:00.000-08:002009-12-29T22:16:59.683-08:00SQL Function Reference:Oracle vs.SQL Server vs. My SQL Part- IGenerally the major issue we face while development is the availability of common functions in different database(s).<br /><br />I am facing this issue from last so many years. Then I had start preparing a table for common functions of different database(s).<br /><br />The commonly used databases are Oracle, Sql Server and My Sql. Their is so many other database(s) are also available but commonly I works on these database(s) only.<br /><br />Following is the common list of functions in different database(s):-<br /><br /><div style="text-align: center; font-weight: bold;">Math Functions<br /></div><span style="font-weight: bold; font-style: italic;">Function - Absolute value</span><span style="font-style: italic;"><span style="font-weight: bold;"><br /></span></span>Oracle - ABS<br />SQL Server - ABS<br /> My Sql - ABS<br /><br /><span style="font-weight: bold; font-style: italic;">Function - Arc cosine</span><span style="font-style: italic;"><span style="font-weight: bold;"><br /></span></span>Oracle - ACOS<br />SQL Server - ACOS<br /> My Sql - ACOS<br /><br /><span style="font-weight: bold; font-style: italic;">Function - Arc sine</span><span style="font-style: italic;"><span style="font-weight: bold;"><br /></span></span>Oracle - ASIN<br />SQL Server - ASIN<br /> My Sql - ASIN<br /><br /><span style="font-weight: bold; font-style: italic;">Function - Arc tangent of n</span><span style="font-style: italic;"><span style="font-weight: bold;"><br /></span></span>Oracle - ATAN<br />SQL Server - ATAN<br /> My Sql - ATAN<br /><br /><span style="font-weight: bold; font-style: italic;">Function - Arc tangent of n and m</span><span style="font-style: italic;"><span style="font-weight: bold;"><br /></span></span>Oracle - ATAN2<br />SQL Server - ATAN2<br /> My Sql - Please Suggest<br /><br /><span style="font-weight: bold; font-style: italic;">Function - Smallest integer >= value</span><span style="font-style: italic;"><span style="font-weight: bold;"><br /></span></span>Oracle - CEIL<br /> SQL Server - CEILING<br /> My Sql - Please Suggest<br /><br /><span style="font-weight: bold; font-style: italic;">Function - Cosine</span><span style="font-style: italic;"><span style="font-weight: bold;"><br /></span></span>Oracle - COS<br /> SQL Server - COS<br /> My Sql - COS<br /><br /><span style="font-weight: bold; font-style: italic;">Function - Hyperbolic cosine</span><span style="font-style: italic;"><span style="font-weight: bold;"><br /></span></span>Oracle - COSH<br /> SQL Server - COT<br /> My Sql - Please Suggest<br /><br /><span style="font-weight: bold; font-style: italic;">Function - Exponential value</span><span style="font-style: italic;"><span style="font-weight: bold;"><br /></span></span>Oracle - EXP<br /> SQL Server - EXP<br /> My Sql -EXP<br /><br /><span style="font-weight: bold; font-style: italic;">Function - Round down to nearest integer</span><span style="font-style: italic;"><span style="font-weight: bold;"><br /></span></span>Oracle - FLOOR<br /> SQL Server - FLOOR<br /> My Sql -FLOOR<br /><br /><span style="font-weight: bold; font-style: italic;">Function - Natural logarithm</span><span style="font-style: italic;"><span style="font-weight: bold;"><br /></span></span>Oracle - LN<br /> SQL Server - LOG<br /> My Sql - Please Suggest<br /><br /><span style="font-weight: bold; font-style: italic;">Function - Logarithm, any base</span><span style="font-style: italic;"><span style="font-weight: bold;"><br /></span></span>Oracle - LOG(N)<br /> SQL Server - N/A<br /> My Sql - Please Suggest<br /><br /><span style="font-weight: bold; font-style: italic;">Function - Logarithm, base 10</span><span style="font-style: italic;"><span style="font-weight: bold;"><br /></span></span>Oracle - LOG(10)<br /> SQL Server - LOG10<br /> My Sql - Please Suggest<br /><br /><span style="font-weight: bold; font-style: italic;">Function - Modulus (remainder)</span><span style="font-style: italic;"><span style="font-weight: bold;"><br /></span></span>Oracle - MOD<br /> SQL Server - USE MODULO (%) OPERATOR<br /> My Sql - Please Suggest<br /><br /><span style="font-style: italic; font-weight: bold;">Function - Power<br /></span>Oracle - POWER<br /> SQL Server - POWER<br /> My Sql - POWER<br /><br /><span style="font-weight: bold; font-style: italic;">Function - Random number</span><br />Oracle - N/A<br /> SQL Server - RAND<br /> My Sql - Please Suggest<br /><br /><span style="font-weight: bold; font-style: italic;">Function - Round</span><br />Oracle - ROUND<br /> SQL Server - ROUND<br /> My Sql - ROUND<br /><br /><br /><span style="font-weight: bold; font-style: italic;">Function - Sign of number</span><br />Oracle - SIGN<br /> SQL Server - SIGN<br /> My Sql - SIGN<br /><br /><span style="font-weight: bold; font-style: italic;">Function - Sine</span><br />Oracle - SIN<br /> SQL Server - SIN<br /> My Sql - SIN<br /><br /><span style="font-weight: bold; font-style: italic;">Function - Hyperbolic sine</span><br />Oracle - SINH<br /> SQL Server - N/A<br /> My Sql - Please Suggest<br /><br /><span style="font-weight: bold; font-style: italic;">Function - Square root</span><br /> Oracle - SQRT<br /> SQL Server - SQRT<br /> My Sql - SQRT<br /><br /><span style="font-weight: bold; font-style: italic;">Function - Tangent</span><br /> Oracle - TAN<br /> SQL Server - TAN<br /> My Sql - TAN<br /><br /><span style="font-weight: bold; font-style: italic;">Function - Hyperbolic tangent</span><br /> Oracle - TANH<br /> SQL Server - N/A<br /> My Sql - Please Suggest<br /><br /><span style="font-weight: bold; font-style: italic;">Function - Truncate</span><br /> Oracle - TRUNC<br /> SQL Server - N/A<br /> My Sql - Please Suggest<br /><br /><span style="font-weight: bold; font-style: italic;">Function - Highest number in list</span><br /> Oracle - GREATEST<br /> SQL Server - N/A<br /> My Sql - Please Suggest<br /><br /><span style="font-weight: bold; font-style: italic;">Function - Lowest number in list</span><br /> Oracle - LEAST<br /> SQL Server - N/A<br /> My Sql - Please Suggest<br /><br /><span style="font-weight: bold; font-style: italic;">Function - Convert number if NULL</span><br />Oracle - NVL<br /> SQL Server - ISNULL<br /> My Sql - Please Suggest<br /><br /><span style="font-weight: bold; font-style: italic;">Function - Standard deviation</span><br />Oracle - STDDEV<br /> SQL Server - STDDEV<br /> My Sql - Please Suggest<br /><br /><span style="font-weight: bold; font-style: italic;">Function - Variance</span><br />Oracle - VARIANCE<br /> SQL Server - VAR<br /> My Sql - Please Suggest<br /><br />This is not the end of functions or post. I will post string and datetime functions in coming articles. :)<br /><br /><a href="http://an-it-solution.blogspot.com/2009/12/sql-function-referenceoracle-vssql.html">Visit Part II (String Functions)</a>An IT Solutionhttp://www.blogger.com/profile/11201763791917815178noreply@blogger.com1tag:blogger.com,1999:blog-4318965216733027282.post-86793450665095798542009-11-11T00:00:00.000-08:002009-11-11T00:09:07.261-08:00How to get tables names in MySql (sysobjects in sql server)In Sql server we have a table sysobjects which holds names of all the tables i.e. system and user tables.<br /><br />Like the same we have command “Show tables” which show all the tables of specified database.<br /><br />Syntax<br />Show Tables from <databasename><br /><br />Here is an example.<br /><span style="font-weight: bold;">SHOW TABLES FROM MySql</span><br /><br />If you had already select a database you can only use<br />Show tables to show all the tables in the database. For ex. Select database MySql and in Sql command window enter following text<br /><br /><span style="font-weight: bold;">SHOW TABLES</span><br /><br />And click on Run.<br /><br />It will show all the tables which is avalible in the listAn IT Solutionhttp://www.blogger.com/profile/11201763791917815178noreply@blogger.com1tag:blogger.com,1999:blog-4318965216733027282.post-39703916217561345662009-08-13T23:52:00.000-07:002010-04-06T23:52:04.824-07:00How to take back up of my sql database using C#<a href="http://www.4shared.com/file/124947911/5b6162d4/AnItSolutionMySqlBackup.html"><img src="http://i540.photobucket.com/albums/gg331/narendersinghkahlon/download.png" alt="download" />You can also download running code.</a><br /><br />Taking a back up of my sql using C# is very easy but this also be termed as tricky backup. Like Sql server my sql does not provide built in stored procedures to take back up. For back up my sql use a Exe file named “mysqldump.exe”<br /><br />You will find this Exe file in “C:\Program Files\MySQL\MySQL Server 5.0\bin” folder of mySql.<br /><br />This file takes so many parameters for different options. But my task is to simply take a backup of single database<br /><br />To take a backup of single database following are the parameters<br /><br />mysqldump -u<usernane> -p <password> -h<servername or ip> <databasename> > <localtion to take backup><br /><br />Please let me know if this helps you to perform your task easily.An IT Solutionhttp://www.blogger.com/profile/11201763791917815178noreply@blogger.com6tag:blogger.com,1999:blog-4318965216733027282.post-63822126607689881722009-07-17T01:29:00.000-07:002010-04-06T23:51:26.063-07:00Auto Complete Example in Asp.net C# Using JQuery<a href="http://www.4shared.com/file/118435695/7211012a/EngMohammed_Auto-Complete_ASPNET_JQuery.html"><img src="http://i540.photobucket.com/albums/gg331/narendersinghkahlon/download.png" alt="download"/>Download Running Code Supporting JQuery</a><br /><br />Before writing this post I had written one another post which is using a pure JavaScript to show Auto complete or Auto delete example.<br /><br />My previous example is not using Jquery which is a famous and reliable way to present your data.<br /><br />One of the drawback of my previous example is it will become slow as I have to load a bulk data for auto complete box.<br /><br />I had received many comments on this post, some of them had appreciated and some of them had suggested using Jquery.<br /><br />One of my great readers had given an example of Jquey which I am posting to help you all. So, before posting this example I would like to say thanks to my reader <span style="font-weight: bold;">Eng. Mohammed Ibrahim </span>who had suggested and helped me to help others.<br /><a href="http://www.4shared.com/file/118435695/7211012a/EngMohammed_Auto-Complete_ASPNET_JQuery.html"></a>An IT Solutionhttp://www.blogger.com/profile/11201763791917815178noreply@blogger.com2