SQL Server Express is a free edition of Microsoft's SQL Server, which is a relational database management system (RDBMS). However, remote access is not enabled by default on this SKU. To access a SQL Server Express instance from the network, we have to do a few steps. There's plenty of guide on the internet telling you how to enable remote access via GUI, but I would like to do it from PowerShell. …
Recently, there was a demand to switch data storage from SQL Server database to Azure Storage Table. However, neither SSMS nor Azure Portal provide a direct import function. You may now consider writing a tool to import data, but actually it isn't necessary. I still managed to do it within mouse clicks. But the first thing to warn everyone is that relational databases like SQL Server are not …
We can use SSMS to export data-tier application for an SQL Server database. I've been using this method for several years on my local machine. However, to make things work under automated environments, like CI/CD environments, or some automated scheduled tasks. We usually need to do it under the command line. Microsoft has provided a cross-platform command-line tool that can import/export DAC: …
今天做了次数据库迁移,目的是开个最新版的Azure SQL Database(V12 Update),然后把博客的数据库迁移到新的server上去。按以往的做法(也就是我曾经写过的《图解:如何将SQL Server数据库迁移到SQL Azure 》),把bacpac文件下载下来,然后import到新的数据里,结果爆了: 爆炸是因为Web Tier和Business Tier在最新版的Azure上面已经被微软撸掉了,SSMS 2014却没有升级,不认识这两个Tier,还在用老的Web Tier。 TITLE: Microsoft SQL Server Management Studio ------------------------------ An exception occurred while executing a Transact-SQL statement or batch. …
有时候我们需要在EF里直接执行参数化的SQL语句,如果有返回,就要把结果映射成C#对象集合。貌似是从EF4.2开始(4.0写法不一样)提供了DbContext.Database.SqlQuery()的API可以直接执行SQL。但是如果碰到可控类型的参数,比如Guid?,就会爆。 看一个例子,定义的SQL语句如下: string sql = @"SELECT p2.Id AS PeriodId, p2.Title AS PeriodTitle, u.UserId AS UserId, u.DisplayName AS …
SQL里要做循环比较麻烦,有时候需要用游标(CURSOR)。我是SQL大菜鸟,昨天刚开荒成功了一个游标。发出来备用。 基本语法形式是这样的: DECLARE @临时变量 UNIQUEIDENTIFIER DECLARE 游标名称 CURSOR FOR -- SELECT的结果 OPEN 游标名称 FETCH NEXT FROM 游标名称 INTO @临时变量 WHILE @@FETCH_STATUS = 0 BEGIN -- 对每一条记录的操作 FETCH NEXT FROM 游标名称 INTO @临时变量 END CLOSE 游标名称 DEALLOCATE 游标名称 举个例子,遍历我博客的分类表,输出每一项的Route名称: DECLARE @tempId UNIQUEIDENTIFIER DECLARE @tempName NVARCHAR(150) …