Azure sp_execute_remote / Cross Database query in Sql Azure.

Cross database query is not allowed in Sql Azure. Rather Azure has applied a different logic to use
the same.

Please check my article before proceeding with "sp_execute_remote "

http://an-it-solution.blogspot.in/2017/01/sql-azure-select-from-another-database.html

You need to create a SP in "DbCustomers" with the name of "LogEvents_Insert" which will be called by following query.

The below query will execute in "DbPayments " and insert data into "LogLogin" of "DbCustomers"

EXEC sp_execute_remote @data_source_name  = N'DbUtilityLogging_Datasource',
@stmt = N'LogEvents_Insert @LoginID',
@params = N'@LoginId nvarchar(50)',
@LoginID='narender90'



Share:
Read More
,

Sql azure select from another database / Cross Database connection

Cross database query Sql Azure

Azure cross database query performance

sql azure select from another database

reference to database and/or server name in is not supported in this version of sql server.

All the above statements are reference to the issue we face while shifting data from Sql Server to Azure Database.

While using Sql Server (Any version) we can reference any database with in Sql server according to user rights assigned to user.

We can use statements like "select * from db1.dbo.customers"

But now in Sql Azure the system and terminology has changed.

Now, azure has new concept of External Tables and sp_execute_remote in introduced.

Now we need to know the who is who in Azure.

External Tables = "Is for Select"

sp_execute_remote="Execute External Store Procedures"

Before proceeding.

Please create 2 azure databases as per your choice.

Here I am naming them as.

DbCustomers and DbPayments and keep a reference to them in my examples.

In DbCustomers create table "LogLogin" with following fields.
[Id] [int]  NOT NULL,
[LoginID] [nvarchar](50) NULL

Now the main code of cross reference starts.

In "DbPayments " use the following code
--==================
Note - Marked bold and red are the parameters

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'jkl$7k@fylj';
GO
CREATE DATABASE SCOPED CREDENTIAL DbCustomers_Credential WITH IDENTITY = 'UserName', SECRET = 'Password';
GO

CREATE EXTERNAL DATA SOURCE DbCustomers_Datasource
WITH (
      TYPE=RDBMS,
      LOCATION='YourServerName',
      DATABASE_NAME='YourDatabaseName', -- DbCustomers 
      CREDENTIAL = DbCustomers_Credential 
);

GO
CREATE EXTERNAL TABLE [dbo].[LogLogin] (
      [Id] [int]  NOT NULL,
      [LoginID] [nvarchar](50) NULL

) WITH ( DATA_SOURCE = DbCustomers_Datasource)

--==================================================

The above code will create an external table and show the table in "DbPayments" to which you can use simply by writing a SQL query.

Select * from LogLogin or use anywhere is the stored procedures.


Share:
Read More
,

How to add Primary Key and Auto Increment on different fields of MySql

 Normal code generated by MySql:-

CREATE TABLE `categorymaster` (
  `CategoryID` int(11) NOT NULL AUTO_INCREMENT,
  `Category` varchar(200) DEFAULT NULL,
  `IsActiv` int(11) DEFAULT NULL,
  `ImagePath` varchar(200) DEFAULT NULL,
  `DisplayOrder` int(11) DEFAULT NULL,
  PRIMARY KEY (`Category`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

In the above data stucture I want to make the "Category" as Primary Key and "CategoryId" as Auto Increment.

But Table Wizard always give errors so, I decided to write my own Statement.

Following is the statement which will create perfect structure according to my requirement.

DROP TABLE `dbname`.`categorymaster`;
CREATE TABLE `categorymaster` (
  `CategoryId` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `Category` varchar(200) DEFAULT NULL,
  `IsActiv` int(11) DEFAULT NULL,
  `ImagePath` varchar(200) DEFAULT NULL,
  `DisplayOrder` int(11) DEFAULT NULL,
  UNIQUE KEY `CategoryId` (`CategoryId`),
   PRIMARY KEY (`Category`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Share:
Read More

How to create User Login in Sql Server 2005/2008

How to create User Login in Sql Server 2005/2008
Note - You must be a member of "system admin" to create a new user or database.

Following is the steps to create user login in database for database level security :-
Step 1 .

Open Sql Server >> Enter User Name >> Enter Password >> Click Connect


Step 2 .
Open Security >> Logins >> Right Click >> New Login


Step 3.
Enter login name >> Select Radio button "Sql Server Authentication" >>Enter password>>Enter confirm password>> Un-Check Enforce password policy >> Click Ok


Step 4.
Right click on Databases >> Click new database



Step 5.
Enter Database name>> Owner "<default>" >> Click Ok


Step 6 .
Open Newly created database (My Case it is Test)>> Security>> Users>> Right click on dbo >> New users.

Step 7.
Enter user name>> Login name >> default schema enter "dbo">> Celect check box db_owner>> Select check box db_owner >> Click Ok


You are done with user creation.
To make sure user is created and working, please re-login using your newly created user name.


Share:
Read More

How to Expose/Access Sql Server to Internet

Task - Expose Sel Server to Internet or Remote Access via IP
OS - Windows Server 2008 R2
Sql Version - Sql Server 2008 R2

To expose the server to internet we need open port for sql server (default port is 1433)

To open a port in the Windows firewall for TCP access

1. On the Start menu, click Run, type WF.msc, and then click OK.
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).
3. In the Rule Type dialog box, select Port, and then click Next.
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.
5. In the Action dialog box, select Allow the connection, and then click Next.
6. In the Profile dialog box, I am going to Leave Domain turned on and turn private and public off. Then click Next.
7. In the Name dialog box, type "Allow SQL 1433 Inbound” and for a description I am putting in the same. Then click Finish.

Then go to Sql Server Configuration Manager.
1. On the Start menu, Click Microsoft Sql Server 2008 R2
2. Click on Configuration Tools.
3. Sql Server Configuration Manager.

And follow the attached images




================================




Now you need to access the Sql server like "IP\sqlexpress" in name, please don't try only with IP address.


Share:
Read More
, ,

Server Application Unavailable IIS Error

Generally the cause of Server Application Unavailable is the .net framework.

Error Snapshot

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.

How to Resolve:-
We need to create a new application pool on the IIS and assign the same to the version of the site.
Following image illustrates how to check the version of site.



For ex. Pool3_5 for version 3.5 and  Pool4_0 version 4.0 .
Following image illustrates how to create new Pool.

Step 1:





Step 2:


Step 3:




 You can also verify the IIS logs to locate the issue.
Share:
Read More
, ,

How to make a site default on IIS 6 Windows Server 2003

While setting up an new Web server with Windows Server 2003 and IIS 6.

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.

The process is so, easy we need to setup few things in custom site.

Right click on Site>>Properties>>WebSite>>Click on Advnaced Button>>Leave the "Host header value" to be blank.

Please refer the image..



Share:
Read More
,

Windows Event Starting up database DatabaseName

Configuration:-
Windows Server 2003 R2 32 bit
Sql Express 2008 R2

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"

We found the solution and implemnted the same.

You need to open "Sql Express Management Studio>>Database>>Properties>>Options>>Auto Close" and set it to false.

For more information on this property, please refer
http://blogs.msdn.com/b/buckwoody/archive/2009/06/24/sql-server-best-practices-autoclose-should-be-off.aspx
http://msdn.microsoft.com/en-us/library/ms135094%28SQL.90%29.aspx
Share:
Read More
, ,

How to set focus on Table row using Javascript

We are developing Application for Google Smart TV and all is going well.

We are using Table, Div or some other elements which cannot be focused on using .focus() of java script.

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.

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.

Finally we found solution on http://groups.google.com/group/mozilla.dev.tech.css/browse_thread/thread/42591922d95c6399

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.


Hope this will help all who are looking for such nice solutions.
Share:
Read More