Archive

Posts Tagged ‘sql’

Migrate Users Across Sitecore Databases

This script is used to migrate users in a specific role from two different Sitecore Instances.

As you can imagine, it runs on the Core databases.

begin tran
declare @roleId  uniqueidentifier
declare @destinationRoleId  uniqueidentifierselect TOP 1 @roleId  =RoleId from dbo.aspnet_Roles where LoweredRoleName=’sitecore\sourcegroup’

select @roleId

select TOP 1 @destinationRoleId =RoleId from [OtherDB].dbo.aspnet_Roles where LoweredRoleName=’sitecore\destinationgroup’
select @destinationRoleId
select *from aspnet_UsersInRoles where RoleId=  @roleId

select *from aspnet_Roles where RoleId=  @roleId

select *from [OtherDB].dbo.aspnet_Roles where RoleId=  @destinationRoleId

select distinct sourceUsers.ApplicationId, sourceUsers.UserId,sourceUsers.UserName,sourceUsers.LoweredUserName,sourceUsers.MobileAlias,sourceUsers.IsAnonymous,sourceUsers.LastActivityDate
into #TmpUsers
from dbo.aspnet_Users as sourceUsers
inner join dbo.aspnet_UsersInRoles on sourceUsers.UserId = aspnet_UsersInRoles.UserId
where aspnet_UsersInRoles.RoleId=@roleId
and sourceUsers.LoweredUserName not in (select distinct LoweredUserName from  [OtherDB].dbo.aspnet_Users )

select * from #TmpUsers

INSERT into [OtherDB].dbo.aspnet_Users
select * from #TmpUsers

INSERT into [OtherDB].dbo.aspnet_UsersInRoles
select UserId, @destinationRoleId as RoleId from #TmpUsers

select distinct gcu.ApplicationId, gcu.UserId,gcu.UserName,gcu.LoweredUserName,gcu.MobileAlias,gcu.IsAnonymous,gcu.LastActivityDate
into #TmpExistingUsers
from dbo.aspnet_Users as sourceUsers inner join dbo.aspnet_UsersInRoles on sourceUsers.UserId = aspnet_UsersInRoles.UserId
inner join [OtherDB].dbo.aspnet_Users gcu on gcu.LoweredUserName = sourceUsers.LoweredUserName
where aspnet_UsersInRoles.RoleId=@roleId

select * from #TmpExistingUsers

INSERT into [OtherDB].dbo.aspnet_UsersInRoles
select ExistingUsers.UserId, @destinationRoleId as RoleId from #TmpExistingUsers ExistingUsers
left outer join [OtherDB].dbo.aspnet_UsersInRoles   on ExistingUsers.UserId = [OtherDB].dbo.aspnet_UsersInRoles.UserId and [OtherDB].dbo.aspnet_UsersInRoles.RoleId = @destinationRoleId
where [OtherDB].dbo.aspnet_UsersInRoles.UserId is null

drop table #TmpUsers

drop table #TmpExistingUsers

select *from [OtherDB].dbo.aspnet_UsersInRoles where RoleId=  @destinationRoleId

–rollback tran
commit tran

Categories: DEV Tags: ,

Setup SQL 2008 unattended mode

SQLEXPR_x86_ENU.exe /q

/ACTION=Install 

/INSTANCEID="AEEXPRESS"

/INSTANCENAME="AEEXPRESS"

/FEATURES=SQLENGINE,CONN 

/SQLSVCACCOUNT="NT AUTHORITY\SYSTEM" 

/SQLSYSADMINACCOUNTS="BUILTIN\ADMINISTRATORS"

/SQLSVCSTARTUPTYPE="Automatic"

/SECURITYMODE=SQL

/SAPWD=Password01

http://msdn.microsoft.com/en-us/library/ms144259%28v=sql.100%29.aspx#Install

Categories: Infrastructure Tags: ,

Using SQL Table Parameters in ADO.NET

Some time is much better to use in your Store Procedures Table parameters for reduce the round-trip with the server and have better performances…. this is an example on how to do implement it.

SQL Store Procedure with a TABLE Parameter

   1: Create PROC [dbo].[getData]

   2:  

   3:  @MyTableParam ProductsTableType  READONLY ,

   4:  @InputId varchar (200)

   5:  

   6:  AS

   7:  

   8:       SELECT  *

   9:       FROM  table1 sub

  10:       

  11:       inner join @MyTableParam p on  sub.Productid = p.productId and sub.VariantId = p.VariantId

  12:       

  13:       INNER JOIN somethingelse ON sub.SubscriptionID = Subscription.ID

  14:         

  15:     where Field =@InputId

  16:       

and this is the code to call the storeProc

   1: private void AssignProductDataTableRow(DataTable products, CatalogItemsDataSet.CatalogItem catalogItem)

   2:        {

   3:            DataRow dr = products.NewRow();

   4:            dr[0] = catalogItem.ProductId;

   5:            dr[1] = catalogItem.VariantId;

   6:            products.Rows.Add(dr);

   7:        }

   8:  

   9:        public static DataTable PrepareProductsDataTable()

  10:        {

  11:            var products = new DataTable();

  12:  

  13:            var prIdCol = new DataColumn("ProductId", Type.GetType("System.String"));

  14:            var varIdCol = new DataColumn("VariantId", Type.GetType("System.String"));

  15:  

  16:            products.Columns.Add(prIdCol);

  17:            products.Columns.Add(varIdCol);

  18:            return products;

  19:        }

  20:  

  21:      using (

  22:                var conn =

  23:                    new SqlConnection(ConfigurationManager.ConnectionStrings["CustomProductData"].ToString()))

  24:            {

  25:                SqlCommand cmd = conn.CreateCommand();

  26:                cmd.CommandType = CommandType.StoredProcedure;

  27:                cmd.CommandText = "dbo.getData";

  28:  

  29:                cmd.Parameters.AddWithValue("@MyTableParam", products);

  30:                cmd.Parameters.AddWithValue("@InputId", "1");

  31:  

  32:                conn.Open();

  33:                SqlDataReader res = cmd.ExecuteReader();

  34:  

  35:  

  36:                while (res.Read())

  37:                {

  38:                 //Read Row...

  39:                 }

  40: }

Categories: DEV Tags: , ,

SQL Tips

A few tips useful for your Sql Query…

Update with a join:

   1: UPDATE MyTable

   2: SET Column = NewValue

   3: FROM MyTable

   4: INNER JOIN OtherTable ON MyTable.Key = OtherTable.Key

trimming…

   1: select LTRIM(RTRIM(columnOne)) from table1

select into

   1: SELECT Persons.LastName,Orders.OrderNo

   2: INTO Backup

   3: FROM Persons

   4: INNER JOIN Orders

   5: ON Persons.P_Id=Orders.P_Id

Categories: DEV Tags:

Native WS on SQL

in sql 2005 and 2008 there is an interesting feature to expose your store procedures as web service….

well, in several cases that could be useful and avoid to write useless wrapper code… but I understand the possible concern on the hosting runtime, performances and so on….

unluckily it is not going to be supported anymore from MS in next versions of SQL

for a quick reference on how to implement it, take a look at this article:
http://www.developer.com/db/article.php/10920_3767311_1/Creating-Native-Web-Services-in-SQL-Server.htm

Categories: Infrastructure Tags: