ttt

I can’t believe I couldn’t find a working solution to this after an hour of searching. I’m following this articleon Entity Framework 6.0 which gives a simple walk-through on Code First. I created the project and installed the latest EF Nuget package for the project to compile. I also verified that I have Microsoft SQL Server 2012 Express LocalDB installed which came with Visual Studio 2013. I don’t have any other instances of SQL installed on my local computer. The program runs and entries are added to the database and outputted in the console. But when the article says “check your localdb” it doesn’t say how! I don’t see any ‘.mdf’ or ‘.ldf’ files created under the project folder. I tried every way to connect Visual Studio’s Server Explorer to LocalDB. The wizard cannot locate (localdb) or cannot find any provider in Server Explorer to accept connection string like (localdb)\v11.0;Integrated Security=true;" I’ve seen this asked several places in StackOverflow but no answer works or marked as answer. Please help, this doesn’t have to be this frustrating!

What are the steps to connect Visual Studio Server Explorer to LocalDB?

Steps to connect LocalDB to Visual Studio Server Explorer

  1. Open command prompt
  2. Run SqlLocalDB.exe start v11.0
  3. Run SqlLocalDB.exe info v11.0
  4. Copy the Instance pipe name that starts with np:\…
  5. In Visual Studio select TOOLS > Connect to Database…
  6. For Server Name enter (localdb)\v11.0. If it didn’t work, use the Instance pipe name that you copied earlier. You can also use this to connect with SQL Management Studio.
  7. Select the database on next dropdown list
  8. Click OK

How to Rank your search results with multiple search terms using LINQ and EntityFramework

I have always used a ranked search criteria, it’s the only true way to get good results back from a data set.  But I’ve been doing my ranking within C# code.  But this got all the data from the data source and then ranked the results.  This is very poor on performance, as we should only be returning back the results for the page size we require.

So task at hand it to produce a LINQ statement to reterive only the data you require.

Here is the solution:

var entity = new myEntities();

var searchTerm = "a b Ba";

var searchArray = searchTerm.Split(new[] { ' ' }, StringSplitOptions.RemoveEmptyEntries);

var usersAll = entity.User.AsExpandable().Where(TC_User.ContainsInLastName(searchArray));

Console.WriteLine("Total Records: {0}", usersAll.Count());

var users = usersAll
    .Select(x => new { 
        x.LastName, 
        Rank = searchArray.Sum(s => ((x.LastName.Length - x.LastName.Replace(s, "").Length) / s.Length)) });

var results = users.OrderByDescending(o => o.Rank)
    .Skip(0)
    .Take(20);

foreach (var user in results)
{
    Console.WriteLine("{0}, {1}", user.LastName, user.Rank);
}

Console.ReadLine();

You’ll also need to add a new method to your User class to check for that the search term is contain in the LastName

public static Expression<Func<TC_User, bool>> ContainsInLastName(
                                                params string[] keywords)
{
    var predicate = PredicateBuilder.False<TC_User>();
    foreach (string keyword in keywords)
    {
        string temp = keyword;
        predicate = predicate.Or(p => p.LastName.Contains(temp));
    }
    return predicate;
}

One thing that is required is LinqKit, which is available via NuGet to handle the PredicateBuilder and AsExpandable.

Watch out as the results coming back from Sum is a BigInt so if you create a model to return back make sure it is a long type.

Backup SQL Azure

There are a number of options for backing up SQL Azure, which can be found here:

Different ways to Backup your Windows Azure SQL Database

I like the Azure way, which is just exporting, importing and setting a scheduled

Before You Begin

The SQL Database Import/Export Service requires you to have a Windows Azure storage account because BACPAC files are stored here. For more information about creating a storage account, see How to Create a Storage Account. You must also create a container inside Blob storage for your BACPAC files by using a tool such as the Windows Azure Management Tool (MMC) or Azure Storage Explorer.

If you want to import an on-premise SQL Server database to Windows Azure SQL Database, first export your on-premise database to a BACPAC file, and then upload the BACPAC file to your Blob storage container.

If you want to export a database from Windows Azure SQL Database to an on-premise SQL Server, first export the database to a BACPAC file, transfer the BACPAC file to your local server (computer), and then import the BACPAC file to your on-premise SQL Server.

Export a Database

  1. Using one of the tools listed in the Before You Begin section, ensure that your Blob has a container.

  2. Log on to the Windows Azure Platform Management Portal.

  3. In the navigation pane, click Hosted Services, Storage Accounts & CDN, and then click Storage Accounts. Your storage accounts display in the center pane.

  4. Select the required storage account, and make a note of the following values from the right pane: Primary access key and BLOB URL. You will have to specify these values later in this procedure.

  5. In the navigation pane, click Database. Next, select the subscription, your SQL Database server, and then your database that you want to export.

  6. On the ribbon, click Export. This opens the Export Database to Storage Account window.

  7. Verify that the Server Name and Database match the database that you want to export.

  8. In the Login and Password boxes, type the database credentials to be used for the export. Note that the account must be a server-level principal login – created by the provisioning process – or a member of the dbmanager database role.

  9. In New Blob URL box, specify the location where the exported BACPAC file is saved. Specify the location in the following format: “https://” + Blob URL (as noted in step 4) + “/<container_name>/<file_name>”. For example: https://myblobstorage.blob.core.windows.net/dac/exportedfile.bacpac. The Blob URL must be in lowercase without any special characters. If you do not supply the .bacpac suffix, it is applied by the export operation.

  10. In the Access Key box, type the storage access key or shared access key that you made a note of in step 4.

  11. From the Key Type list, select the type that matches the key entered in the Access Key box: either a Storage Access Key or a Shared Access Key.

  12. Click Finish to start the export. You should see a message saying Your request was successfully submitted.

  13. After the export is complete, you should attempt to import your BACPAC file into a Windows Azure SQL Database server to verify that your exported package can be imported successfully.

Database export is an asynchronous operation. After starting the export, you can use the Import Export Request Status window to track the progress. For information, see How to: View Import and Export Status of Database (Windows Azure SQL Database).

noteNote
An export operation performs an individual bulk copy of the data from each table in the database so does not guarantee the transactional consistency of the data. You can use the Windows Azure SQL Database copy database feature to make a consistent copy of a database, and perform the export from the copy. For more information, see Copying Databases in Windows Azure SQL Database.

Configure Automated Exports

Use the Windows Azure SQL Database Automated Export feature to schedule export operations for a SQL database, and to specify the storage account, frequency of export operations, and to set the retention period to store export files.

To configure automated export operations for a SQL database, use the following steps:

  1. Log on to the Windows Azure Platform Management Portal.

  2. Click the SQL database name you want to configure, and then click the Configuration tab.

  3. On the Automated Export work space, click Automatic, and then specify settings for the following parameters:

    • Storage Account
    • Frequency
      • Specify the export interval in days.
      • Specify the start date and time. The time value on the configuration work space is UTC time, so note the offset between UTC time and the time zone where your database is located.
    • Credentials for the server that hosts your SQL database. Note that the account must be a server-level principal login – created by the provisioning process – or a member of the dbmanager database role.
  4. When you have finished setting the export settings, click Save.

  5. You can see the time stamp for the last export on under Automated Export in the Quick Glance section of the SQL Database Dashboard.

To change the settings for an automated export, select the SQL database, click the Configuration tab, make your changes, and then click Save.

Create a New SQL Database from an Existing Export File

Use the Windows Azure SQL Database Create from Export feature to create a new SQL database from an existing export file.

To create a new SQL database from an existing export file, use the following steps:

  1. Log on to the Windows Azure Platform Management Portal.

  2. Click a SQL database name and then click the Configuration tab.

  3. On the Create from Export work space, click New Database, and then specify settings for the following parameters:

    • Bacpac file name – This is the source file for your new SQL database.
    • A name for the new SQL database.
    • Server – This is the host server for your new SQL database.
    • To start the operation, click the checkmark at the bottom of the page.

Import and Export a Database Using API

You can also programmatically import and export databases by using an API. For more information, see the Import Export example on Codeplex.

Import a Database

  1. Using one of the tools listed in the Before You Begin section, ensure that your Blob has a container, and the BACPAC file to be imported is available in the container.

  2. Log on to the Windows Azure Platform Management Portal.

  3. In the navigation pane, click Hosted Services, Storage Accounts & CDN, and then click Storage Accounts. Your storage accounts display in the center pane.

  4. Select the storage account that contains the BACPAC file to be imported, and make a note of the following values from the right pane: Primary access key and BLOB URL. You will have to specify these values later in this procedure.

  5. In the navigation pane, click Database. Next, select the subscription, and then your SQL Database server where you want to import the database.

  6. On the ribbon, click Import. This opens the Import Database from Storage Account window.

  7. Verify that the Target Server field lists the SQL Database server where the database is to be created.

  8. In the Login and Password boxes, type the database credentials to be used for the import.

  9. In the New Database Name box, type the name for the new database created by the import. This name must be unique on the SQL Database server and must comply with the SQL Server rules for identifiers. For more information, see Identifiers.

  10. From the Edition list, select whether the database is a Web or Business edition database.

  11. From the Maximum Size list, select the required size of the database. The list only specifies the values supported by the Edition you have selected.

  12. In the BACPAC URL box, type the full path of the BACPAC file that you want to import. Specify the path in the following format: “https://” + Blob URL (as noted in step 4) + “/<container_name>/<file_name>”. For example: https://myblobstorage.blob.core.windows.net/dac/file.bacpac. The Blob URL must be in lowercase without any special characters. If you do not supply the .bacpac suffix, it is applied by the import operation.

  13. In the Access Key box, type the storage access key or shared access key that you made a note of in step 4.

  14. From the Key Type list, select the type that matches the key entered in the Access Key box: either a Storage Access Key or a Shared Access Key.

  15. Click Finish to start the import.

Database import is an asynchronous operation. After starting the import, you can use the Import Export Request Status window to track the progress. For information, see How to: View Import and Export Status of Database (Windows Azure SQL Database).

Original Article

SQL Performance Profiler for SQL Express

I came across this smart little Profiler for SQL Express, or any version of SQL as it happens.

Microsoft SQL Server family includes free Express edition, that is fully functional, however has some disappointing limitations which prevent from using it in development process. One of them is absense of profiling tools, standard SQL profiler is not included. However, I came across this SQL Performance Profiler to use with the express edition for tuning your system.

SQL Server Express Edition Profiler provides most of functionality standard profiler does, such as choosing events to profile, setting filters, etc. 

It is a small fee of about $5 for three licences, which is well worth the money.

http://www.datawizard.com

Search every SQL table for a string

Here is some neat SQL to allow you to search every table and every field in the database for a string

CREATE TABLE myTable99 (TABLE_NAME sysname, COLUMN_NAME sysname, Occurs int)
GO
 
SET NOCOUNT ON
 
DECLARE @SQL varchar(8000), @TABLE_NAME sysname, @COLUMN_NAME sysname, @Sargable varchar(80), @Count int
 
SELECT @Sargable = 'There are too many subscription products resulting from the supplied request data'
 
DECLARE insaneCursor CURSOR FOR 
      SELECT c.TABLE_NAME, c.COLUMN_NAME 
        FROM INFORMATION_SCHEMA.Columns c INNER JOIN INFORMATION_SCHEMA.Tables t
          ON t.TABLE_SCHEMA = c.TABLE_SCHEMA AND t.TABLE_NAME = c.TABLE_NAME
      WHERE c.DATA_TYPE IN ('char','nchar','varchar','nvarchar','text','ntext')
         AND t.TABLE_TYPE = 'BASE TABLE'
 
OPEN insaneCursor
 
FETCH NEXT FROM insaneCursor INTO @TABLE_NAME, @COLUMN_NAME
 
WHILE @@FETCH_STATUS = 0
      BEGIN
            SELECT @SQL = 'INSERT INTO myTable99 (TABLE_NAME, COLUMN_NAME, Occurs) SELECT '
                        + '''' +  @TABLE_NAME + '''' + ','
                        + '''' + @COLUMN_NAME + '''' + ','
                        + 'COUNT(*) FROM [' + @TABLE_NAME  
                        + '] WHERE [' + @COLUMN_NAME + '] Like '
                        + ''''+ '%' + @Sargable + '%' + ''''
            --SELECT @SQL
            EXEC(@SQL)
            IF @@ERROR <> 0 
                  BEGIN 
                        SELECT @SQL
                        SELECT * FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME = @TABLE_NAME
                        GOTO Error
                  END 
            FETCH NEXT FROM insaneCursor INTO @TABLE_NAME, @COLUMN_NAME
      END
 
SELECT * FROM myTable99 WHERE Occurs <> 0 
 
 
Error:
CLOSE insaneCursor
DEALLOCATE insaneCursor
 
GO
 
DROP TABLE myTable99
GO
 
SET NOCOUNT OFF

Deploy a database project with TFS Build

When you develop web application you often have this scenario. Some people are testers, they are testing the application in dev all day, and they want always to test latest version. Moreover testers usually fills data into web application, so they want their data to be preserved between various deploy. If you simply clear all data in test database you will end with a lot of furious tester so do not even think to do this.
 
If a developer needs to change database schema, we need to automate not only the deploy of the web application, but we need also to sync test database with the latest changes. This is necessary so the test site can work with the new version, but old data is preserved for the happiness of the testers.
 
This is quite simple using TFS and Database project. The situation is this one

 

When a developer does a check-in, the build server grabs the tip from TFS, then it runs the builds script and deploy the new version of the site in the test web server. At the same time test database gets updated with the latest database schema.

Next we need the ability to automatically deploy changes to a specific database, this task is quite simple and you can find some details here in msdn, but we need to insert this command into the msbuild script. Here is the code:

<Target Name="AfterDropBuild"><Message Text="Deploy of project database" />
<MSBuild Projects="$(SolutionRoot)srcDbEditionNorthwindTestNorthwindTestNorthwindTest.dbproj"
Properties="OutDir=$(DropLocation)$(BuildNumber)debug;
TargetDatabase=NorthwindCiTest;
DefaultDataPath=C:Program FilesMicrosoft SQL ServerMSSQL.4MSSQLData;
TargetConnectionString=Data Source=VM2003R2_1SQLTEST%3Buser=sa%3Bpwd=Pa$$w0rd;
DeployToDatabase=true;" 
Targets="Deploy"/> 
</Target>

 

Original post from Ricci Gian Maria

http://geekswithblogs.net/jakob/archive/2012/04/25/deploying-ssdt-projects-with-tfs-build/

Who is locking my SQL database?

Need to know who is logged on to your SQL database ,here is a nice script that I got from Gary Dunne

DECLARE @spid1 int = NULL      /* server process id to check for locks */ 
DECLARE @spid2 int = NULL       /* other process id to check for locks */ 

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
DROP TABLE #locktable

sp_lock2.sql (3.44 kb)

SQL71566 when upgrading to VS12

In VS 2012 I receive the following error message (one for each table) when trying to build a database project.

SQL71566: Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlFilegroup cannot not be set on both the Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlTable and the clustered Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlPrimaryKeyConstraint.

For reference here is script associated with this error.

CREATE TABLE [dbo].[OperationsMaster](
[OperationID] [numeric](18, 0) NOT NULL,
[OperationName] [nvarchar](150) NOT NULL,
[OperationTypeID] [numeric](18, 0) NOT NULL,
CONSTRAINT [PK_OperationsMaster] PRIMARY KEY CLUSTERED
(
[OperationID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

In above script in last line on word [PRIMARY] red line appears. When I run the same script in SSMS it executed successfully.

You can get your project to build by enabling the project setting “Enable extended Transact-SQL verification for common objects.”.