How to determine MSSQL version and edition?

How to determine MS SQL version and edition?

1. Go to Open Query Analyzer 
2. Paste sql script below
   
   SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')
3. Click on Execute button

There you can see the version and edition for Microsoft SQL server installed in your computer.

The bulk load failed. The column is too long in the data file for row 1, column x. Verify that the field terminator and row terminator are specified correctly.

Hit error below when try to execute bulk insert script

Msg 4866, Level 16, State 1, Line 3
The bulk load failed. The column is too long in the data file for row 1, column 6. Verify that the field terminator and row terminator are specified correctly.
Msg 7399, Level 16, State 1, Line 3
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 3
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

(0 row(s) affected)

Script that hit error:

CREATE TABLE GeoIPCountryWhois2
(
[startIp] [nvarchar](50) NULL,
[endIp] [nvarchar](50) NULL,
[startIpNum] [nvarchar](50) NULL,
[endIpNum] [nvarchar](50) NULL,
[CountryCode] [nvarchar](50) NULL,
[Country] [nvarchar](150) NULL
) ON [PRIMARY]
GO


BULK INSERT GeoIPCountryWhois2
FROM 'C:\GeoIPCountryWhois.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '/n'

)
GO
--Check the content of the table.
SELECT *
FROM GeoIPCountryWhois2
GO

Fix:

CREATE TABLE GeoIPCountryWhois2
(
[startIp] [nvarchar](50) NULL,
[endIp] [nvarchar](50) NULL,
[startIpNum] [nvarchar](50) NULL,
[endIpNum] [nvarchar](50) NULL,
[CountryCode] [nvarchar](50) NULL,
[Country] [nvarchar](150) NULL
) ON [PRIMARY]
GO


BULK INSERT GeoIPCountryWhois2
FROM 'C:\GeoIPCountryWhois.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '0x0a'
)
GO
--Check the content of the table.
SELECT *
FROM GeoIPCountryWhois2
GO

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

Facing issue when try to connect to MSSQL 2005 Express from remote server

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

To solve this issue

1. Enable TCP/IP


Go to Programs -> Microsoft SQL Server 2005 -> Configuration Tools -> SQL Server Configuration Manager

Make sure TCP/IP is Enabled

2. Make sure MS SQL allow remote connection with TCP/IP protocol.

Go to Programs -> Microsoft SQL Server 2005 -> Configuration Tools -> SQL Server Surface Area Configuration

Click on Surface Area Configuration for Services and Connections

Make sure Local and remote connections and using TCP/IP only is selected.

3. Open port for Port: 1433, sqlbrowser.exe and sqlservr.exe in windows firewall.

Launch the Windows Firewall configuration tool from the control panel.

Click the Exceptions Tab

Click on Add Port, key in information as below

Repeat above but this time click the "Add Programs." button and select "sqlservr.exe" from the 

location where you install SQL Server Express (normally in C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn)

Report the same steps but select "sqlbrowser.exe" (normally in: C:\Program Files\Microsoft SQL Server\90\Shared)

 Try again and the problem solved. Hope this help.

Keep track download in ASP.NET

Situation:

I have a software in my web server and let visitor download for free, i wish to keep track of total download and visitor location. 

Feasibility:

For the location keep tracking, i use GeoIP (GeoLiteCity.dat) http://www.maxmind.com/app/ip-location 

Solution:

Database:

I have create a table called downloadlog with field below. I plan to have different type of product in future, so i add ProductFamilyID field. If you don't have that, you can ignore it.

Now, i need a stored procedure called psp_DownloadLog_Add that insert data into table above. The code should be as simple as possible

CREATE PROCEDURE [dbo].[psp_DownloadLog_Add]

(

@p_IP NVARCHAR(50),

@p_Country NVARCHAR(255),

@p_CountryCode VARCHAR(50),

@p_City NVARCHAR(255),

@p_ReferrerURL        NVARCHAR(512),

@p_ProductFamilyID INT

)

AS

BEGIN

SET NOCOUNT ON;

INSERT INTO tblDownloadLog

(

IP,

Country,

CountryCode,

City,

ProductFamilyID,

ReferrerURL,

DownloadDate

)

VALUES

(

@p_IP,

@p_Country,

@p_CountryCode,

@p_City,

@p_ProductFamilyID,

@p_ReferrerURL,

GETDATE()

)

SET NOCOUNT OFF;

END

ASP.NET

To make the system more structural and manageable, I have create 2 classes to handle the download log. DownloadLogInfo class mainly is use the deal with the information that need to log into database. DownloadLog class is mainly the class who do the communication with the database. Please check the code below.

DownloadLogInfo class

 public sealed class DownloadLogInfo

    {

         public DownloadLogInfo() { }

        

         int _downloadID = 0;

         string _ip = "";

         string _country = "";

         string _countryCode = "";

         string _city = "";

         int _productFamilyID = 0;

         string _referrerURL = "";

         DateTime _downloadDate = DateTime.Now;

         public int DownloadID { get { return _downloadID; } set { _downloadID = value; } }

         public string IP { get { return _ip; } set { _ip = value; } }

         public string Country { get { return _country; } set { _country = value; } }

         public string CountryCode { get { return _countryCode; } set { _countryCode = value; } }

         public string City { get { return _city; } set { _city = value; } }

         public int ProductFamilyID { get { return _productFamilyID; } set { _productFamilyID = value; } }

         public string ReferrerURL { get { return _referrerURL; } set { _referrerURL = value; } }

         public DateTime DownloadDate { get { return _downloadDate; } set { _downloadDate = value; } }

         public DownloadLogInfo(int pDownloadID, string pIP, string pCountry, string pCountryCode, string pCity, int pProductFamilyID, string pReferrerURL, DateTime pDownloadDate)

         {

             _downloadID = pDownloadID;

             _ip = pIP;

             _country = pCountry;

             _countryCode = pCountryCode;

             _city = pCity;

             _productFamilyID = pProductFamilyID;

             _referrerURL = pReferrerURL;

             _downloadDate = pDownloadDate;

         }

    }

DownloadLog Class

    public sealed class DownloadLog

    {

        public void Add(DownloadLogInfo pInfo, string pConnectionString)

        {

            SpParamInfo[] myParamInfo = new SpParamInfo[6];

            myParamInfo[0] = new SpParamInfo("@p_IP", SqlDbType.NVarChar, pInfo.IP);

            myParamInfo[1] = new SpParamInfo("@p_Country", SqlDbType.NVarChar, pInfo.Country);

            myParamInfo[2] = new SpParamInfo("@p_CountryCode", SqlDbType.NVarChar, pInfo.CountryCode);

            myParamInfo[3] = new SpParamInfo("@p_City", SqlDbType.NVarChar, pInfo.City);

            myParamInfo[4] = new SpParamInfo("@p_ProductFamilyID", SqlDbType.Int, pInfo.ProductFamilyID);

            myParamInfo[5] = new SpParamInfo("@p_ReferrerURL", SqlDbType.NVarChar, pInfo.ReferrerURL);

            DbAdapter myDbAdapter = new DbAdapter(pConnectionString);

            myDbAdapter.ExecuteNonQuery("psp_DownloadLog_Add", myParamInfo);

            myDbAdapter.Dispose();

        }

    }

Now, I have create a page, call download.aspx. This page will actually get information from the visitor and insert into database. After that it will force a file to be downloaded. Before that, i already GeoLiteCity.dat file and put into my App_Data folder.

Code behind for download.aspx

protected void Page_Load(object sender, EventArgs e)

    {

        string VisitorReferrer;

        // Full path to GeoLiteCity.dat file

        string FullDBPath = Server.MapPath("~/App_Data/GeoLiteCity.dat");

        // Visitor's IP address

        string VisitorIP; 

            

        VisitorIP  = Request.ServerVariables["REMOTE_ADDR"];

      

        if (Request.UrlReferrer != null)

        {

            VisitorReferrer = Request.UrlReferrer.ToString();

        }

        else

        {

            VisitorReferrer = "";

        }

        // Create objects needed for geo targeting

        Geotargeting.LookupService ls = new Geotargeting.LookupService(FullDBPath, Geotargeting.LookupService.GEOIP_STANDARD);

        Geotargeting.Location visitorLocation = ls.getLocation(VisitorIP);

        

        DownloadLog myDownloadLog = new DownloadLog();

        DownloadLogInfo myDownloadLogInfo = new DownloadLogInfo();

        if (visitorLocation != null) // get the geoip information using visitor IP address

        {

            myDownloadLogInfo.Country = visitorLocation.countryName;

            myDownloadLogInfo.CountryCode = visitorLocation.countryCode;

            myDownloadLogInfo.City = "unknown";

            if (visitorLocation.city != null)

            {

                myDownloadLogInfo.City = visitorLocation.city;

            }

            

            myDownloadLogInfo.IP = VisitorIP;

            myDownloadLogInfo.ProductFamilyID = 1;

            myDownloadLogInfo.ReferrerURL = VisitorReferrer;

        }

        else

        {

            string country = "unknown";

            string countryCode = "unknown";

            string city = "unknown";

            myDownloadLogInfo.Country = country;

            myDownloadLogInfo.CountryCode = countryCode;

            myDownloadLogInfo.City = city;

            myDownloadLogInfo.IP = VisitorIP;

            myDownloadLogInfo.ProductFamilyID = 1;

            myDownloadLogInfo.ReferrerURL = VisitorReferrer;

        }

       

       // WebConfig.Connection is the connection the database and insert data into database

       myDownloadLog.Add(myDownloadLogInfo, WebConfig.Connection);

        

       Response.ContentType = "application/zip";

       Response.AddHeader("content-disposition", "attachment; filename=softwaresetup.zip");

       FileStream sourceFile = new FileStream(@"C:\inetpub\dotnetfish\software\softwaresetup.zip", FileMode.Open);

        

       long FileSize;

       FileSize = sourceFile.Length;

       byte[] getContent = new byte[(int)FileSize];

       sourceFile.Read(getContent, 0, (int)sourceFile.Length);

       sourceFile.Close();

       Response.BinaryWrite(getContent);

    }

Error 0xc00470fe: Data Flow Task: The product level is insufficient for component "Source - data1$" (1)

Trying to import data from a text file using MSSQL2005 SSIS import and export wizard to import data from email.txt into MSSQL data and found below error.

Error 0xc00470fe: Data Flow Task: The product level is insufficient for component "Source - email_txt" (1)

Surf around and found that it's a bug for MSSQL2005 SSIS and have fix in MSSQL SP2. So, try to download SP for mssql and it do solve my problem.

SQL Server 2005 Developer Edition at USD49.95

Yes, this is true. Only USD49.95!



Overview for SQL Server 2005 Developer Edition: SQL Server 2005 Developer Edition provides a complete set of enterprise data management and business intelligence features. Because—like Enterprise Edition—it is optimized to run on x64 and Itanium-based servers, it is the most scalable and available edition of SQL Server 2005.

Check out in microsoft store.

Database diagram support objects cannot be installed because this database does not have a valid owner.

I have restore a database from MSSQL 2000 to MSSQL 2005. When i try to open the database diagram, error message below pop up:

Database diagram support objects cannot be installed because this database does not have a valid owner. To continue, first use the Files page of the Database Properties dialog box or the ALTER AUTHORIZATION statement to set the database owner to a valid login, then add the database diagram support objects.

ALTER AUTHORIZATION ON DATABASE: Database_name TO valid_login

I use SSMS to solve my problem.
In SQL Server Management Studio do the following:

1. Right Click on your database, choose properties
2. Go to the Options
3. In the Dropdown at right labeled "Compatibility Level" choose "SQL Server 2005(90)"
4. Go to Files
5. Type "sa" in the owner textbox
6. Click OK

This solve my problem.

Alternatively, we can solve it using TSQL:
- My database name: avlDB
- MY Login: sa

EXEC sp_dbcmptlevel 'avlDB', '90';
GO
ALTER AUTHORIZATION ON DATABASE::avlDB TO "sa"
GO
USE [avlDB]
GO
EXECUTE AS USER = N'dbo' REVERT
GO

SQL Server Setup failed to obtain system account information for the ASPNET

Trying to setup MSSQL 2005 Developer version in my new machine OS Windows XP Profesional SP3, when come to installation point, error message below appear:

"SQL Server Setup failed to obtain system account information for the ASPNET account. To proceed, reinstall the .NET framework, and then run SQL Server Setup again"

Under Add/Remove Programs, i have Framework 1.1, 2.0 and 3.0 installed.

What next? After surf around, here is the solution:

Steps:

1. Click on Start -> Run

2. Type in cmd and click OK





3. Command prompt screen will appear. go to"C:\Windows\Microsoft.NET\Framework\v1.1.4322" folder by using command cd C:\Windows\Microsoft.NET\Framework\v1.1.4322 and hit enter. Please refer screen below:

4. Type in aspnet_regiis -i and hit enter.

5. Screen below will be shown and dotnet user installed.




Try to install MSSQL 2005 again. It's work.


Problem solved.

SQL server remap user - blank Login name

i come across this situation: my customer backup a db and i restore in our development server. The login user have empty "Login name". Hence, i'm not able to connect using this user name.



finally, i found out that we can use sp_change_users_login to map with login name.

for my case, my database name is HHotel, user login will be 'lam' and password 'l!a2m#'

USE HHotel;
GO
EXEC sp_change_users_login 'Auto_Fix', 'lam', NULL, 'l!a2m#';
GO

It's fixed after run above script.

For more example: http://msdn.microsoft.com/en-us/library/ms174378.aspx

Change stored proc owner in MSSQL 2000 & 2005

Below show how to change stored procedure owner in MS SQL 2000 and 2005

http://dotnetfish.blogspot.com/2008/04/change-table-and-stored-procedure.html

The user instance login flag is not supported on this version of SQL Server. The connection will be closed.

I have download an open source asp.net app. The database supported is SQL Express. When i try to run the app, error message [The user instance login flag is not supported on this version of SQL Server. The connection will be closed. ].

The reason is my installed database is full version of mssql 2005 and not express version. In order to make it run in my development machine, i have change the connectionstring in tag
<connectionstrings> from


<add name="TDConnectionString" connectionString="data source=.\SQLExpress;Integrated Security=SSPI;AttachDBFilename=DataDirectoryTD.mdf;User Instance=true"
providerName="System.Data.SqlClient" />

to


<add name="TDConnectionString"
connectionString="server=.\SQLSERVER2005;database=TD;uid=xxx;pwd=xxxxx"
providerName="System.Data.SqlClient" />

where ".\SQLSERVER2005" is my database server.

It's works now.

Thank You

Thanks For your support!

Invalid use of 'EXECUTE' within a function

I've tried to create a MSSQL "User Defined Functions" with execution of dynamic query (return a final value) and hit error message: "Invalid use of 'EXECUTE' within a function"

The reason is MSSQL not allow execution of dynamic query in "User Defined Functions". Because of this, i need to change from user "User Defined Functions" to "stored procedure" SP by using sp_executesql.

Below show example the Stored Procedure that i use to overcome my problem.

I need my SP to return a calculated value.

CREATE PROCEDURE dbo.psp_GetOCPrice
(
@strSKUNo VARCHAR(50),
@BOMT NUMERIC(9,2),
@OtherCharges NUMERIC(9,2) OUTPUT
)
AS
BEGIN

DECLARE @Count INT,
@Max INT,
@Cost NUMERIC(9,2),
@SQL NVARCHAR(4000),
@Formula NVARCHAR(1000),
@othMainIdn INT,
@othIdn INT


DECLARE @tblOthCharges TABLE
(
ID INT IDENTITY(1,1),
SKUNo VARCHAR(50),
othIdn INT,
othMainIdn INT,
Formula VARCHAR(1000),
Cost NUMERIC(9,2)
)

INSERT INTO @tblOthCharges(SKUNo, othIdn, othMainIdn, Formula)
SELECT sm.SKUNo, sm.pid, sm.ItemGroup,ISNULL(sm.Formula,0) Formula
FROM tblSKUMat sm
WHERE SKUNo = @strSKUNo
AND sm.othCharFlag = 1 ORDER BY sm.seqID ASC

SET @BOMT = ISNULL(@BOMT,0)

SET @Max = (SELECT COUNT(ID) FROM @tblOthCharges)

SET @Count = 1
SET @Cost = 0

SET @SQL = ''

SET @SQL = @SQL + ' DECLARE @BOM NUMERIC(9,2), ' + char(13)
SET @SQL = @SQL + ' @Cost NUMERIC(9,2) ' + char(13)
SET @SQL = @SQL + ' SET @BOM = ' + CONVERT(VARCHAR,@BOMT) + ' ' + char(13)
SET @SQL = @SQL + ' SET @rOtherCharges = 0 ' + char(13)

WHILE @Count <= @Max BEGIN SELECT @Formula = Formula, @othMainIdn = othMainIdn, @othIdn = othIdn FROM @tblOthCharges WHERE ID = @Count

SET @SQL = @SQL + ' SELECT @Cost = ' + CONVERT(VARCHAR,@Formula) + char(13)

SET @SQL = @SQL + ' DECLARE @v' + CONVERT(VARCHAR,@othMainIdn) + '_value NUMERIC(9,2) '

SET @SQL = @SQL + char(13)

SET @SQL = @SQL + ' SET @v' + CONVERT(VARCHAR,@othMainIdn) + '_value = ISNULL(@Cost,0) '

SET @SQL = @SQL + char(13)

SET @SQL = @SQL + ' SET @rOtherCharges = @rOtherCharges + ISNULL(@Cost,0) ' + char(13)

SET @Count = @Count + 1
END

DECLARE @ParmDefinition NVARCHAR(500);

SET @ParmDefinition = N'@rOtherCharges NUMERIC(9,2) OUTPUT';

EXECUTE sp_executesql
@SQL,
@ParmDefinition,
@rOtherCharges = @OtherCharges OUTPUT;

END

GO

i need to use dynamic query because the field "Formula" is mathematics expression where involve calculation using value from parameter.
By using sp_executesql, the value for @rOtherCharges will be assigned to @OtherCharges OUTPUT.

To call this SP, simply call execution

Declare @OtherCharges
Execute dbo.psp_GetOCPrice 'XIN200805010001', 2000.20, @OtherCharges NUMERIC(9,2)

Done!

Create View using Stored Procedure dynamically

We can create view using stored procedure dynamically.

Please find my sample below:
I want to create a view name v_rpt + @Type and @Type as parameter. If the view already exists, it will be drop and recreate a new one.

We will need to execute @SQL twice

• execution 1: check whether the view exists, drop it if exists
• execution 2: create a new view

if we put both in the same query, it will hit error "'CREATE VIEW' must be the first statement in a query batch."


CREATE PROCEDURE [dbo].psp_Generate_view
(
@Type VARCHAR(255)
)
AS

BEGIN

SET NOCOUNT ON

DECLARE @SQL VARCHAR(8000)

SET @SQL = ''
SET @SQL = @SQL + 'IF EXISTS (SELECT * FROM dbo.sysobjects WHERE ID = OBJECT_ID(N''[dbo].[v_rpt' + CONVERT( VARCHAR, @Type) + ']'') AND OBJECTPROPERTY(id, N''IsView'') = 1) ' + char(13)
SET @SQL = @SQL + 'DROP VIEW [dbo].[v_rpt' + CONVERT( VARCHAR, @Type) + '] ' + char(13)

EXECUTE (@SQL)

SET @SQL = ''
SET @SQL = @SQL + 'CREATE VIEW dbo.v_rpt' + CONVERT( VARCHAR, @Type) + ' AS ' + char(13)
SET @SQL = @SQL + 'SELECT DISTINCT '
SET @SQL = @SQL + 's.SKUNo, '
SET @SQL = @SQL + 's.Model, '
SET @SQL = @SQL + 'lk1.LookupDesc [Region], '
SET @SQL = @SQL + 'lk2.LookupDesc [Country], '
SET @SQL = @SQL + 'CASE s.Status '
SET @SQL = @SQL + ' WHEN 1 THEN ''Saved'' '
SET @SQL = @SQL + ' WHEN 2 THEN ''Rejected'''
SET @SQL = @SQL + ' WHEN 3 THEN ''Approved'''
SET @SQL = @SQL + 'END AS Status, '
SET @SQL = @SQL + 's.Remark, '
SET @SQL = @SQL + 'u1.FullName [Creator], '
SET @SQL = @SQL + 's.CreDte [Date of Creation], '
SET @SQL = @SQL + 'u2.FullName [UpdatedBy], '
SET @SQL = @SQL + 's.LastUpdDte [Updated On], '
SET @SQL = @SQL + 'FROM tblSKU s '
SET @SQL = @SQL + 'LEFT OUTER JOIN tblUser u1 ON s.OwnerID = u1.UserID '
SET @SQL = @SQL + 'LEFT OUTER JOIN tblUser u2 ON s.LastUserID = u2.UserID '
SET @SQL = @SQL + 'LEFT OUTER JOIN tblLookup lk1 ON s.Region = lk1.RefIdn AND lk1.GroupIdn = 1 '
SET @SQL = @SQL + 'LEFT OUTER JOIN tblLookup lk2 ON s.CTY = lk2.RefIdn AND lk2.GroupIdn = 2 '
SET @SQL = @SQL + 'WHERE '
SET @SQL = @SQL + 's.Type= ' + CONVERT( VARCHAR, @Type) + ' AND '

EXECUTE (@SQL)


SET NOCOUNT OFF
END

Please let me know if you have any comment.

Shrink Database MSSQL

Log file grow until very huge file? Check this step to shrink sql ldf file http://dotnetfish.blogspot.com/2007/12/shrinking-transaction-log.html

Change Table and Stored Procedure Ownership From xxx to DBO

Need to change the ownership for table and stored procedure

Script for change table ownership

DECLARE @old sysname, @new sysname, @sql varchar(1000)

SET @old = 'oldOwner'
SET @new = 'dbo'
SET @sql = ' IF EXISTS (SELECT NULL FROM INFORMATION_SCHEMA.TABLES
WHERE
QUOTENAME(TABLE_SCHEMA)+''.''+QUOTENAME(TABLE_NAME) = ''?''
AND TABLE_SCHEMA = ''' + @old + ''' )
EXECUTE sp_changeobjectowner ''?'', ''' + @new + ''''

EXECUTE sp_MSforeachtable @sql

Credit should give to Scott Forsyth

change ownership for stored procedure

DECLARE
@OldOwner sysname,
@NewOwner sysname

SET @OldOwner = 'oldOwner'
SET @NewOwner = 'dbo'

DECLARE CURS CURSOR FOR
SELECT
name
FROM sysobjects
WHERE type = 'p'
AND
uid = (SELECT uid FROM sysusers WHERE name = @OldOwner)
AND
NOT name LIKE 'dt%' FOR READ ONLY

DECLARE @ProcName sysname
OPEN CURS
FETCH CURS INTO @ProcName
WHILE @@FETCH_STATUS = 0
BEGIN
IF @@VERSION >= 'Microsoft SQL Server 2005'
BEGIN
EXEC('alter schema ' + @NewOwner + ' transfer ' + @OldOwner + '.' + @ProcName)
exec('alter authorization on ' + @NewOwner + '.' + @ProcName + ' to schema owner')
END
ELSE
EXEC('sp_changeobjectowner ''' + @OldOwner + '.' + @ProcName + ''', ''' + @NewOwner + '''')

FETCH CURS INTO @ProcName
END

CLOSE CURS
DEALLOCATE CURS

Credit should give to Greg Duffield

Exclusive access could not be obtained because the database is in use.

I try to restore a new database in MSSQL 2005 and hit error
Restore Failed For Server 'xxxx' (Microsoft.SqlServer.Smo)
Additional information:
System.Data.SqlClient.SqlError: Exclusive access could not be obtained because the database is in use. (Microsoft.SqlServer.Smo)

This is mostly caused by others user/session is using the database at the same time. To check whether got others user accessing the same database, execute SP_WHO will show who is using the database. Execute KILL to kill the process. In my case, i want to kill process with spid = 53

execute SP_WHO -- See which process in running

Execute KILL 53 -- 53 is the process id spid that i want to kill before restore.

In my case, i restore the database using script instead of using the SSMS.

Below is the script that i use to restore without any issue.

RESTORE DATABASE RateMyPosts
FROM DISK = 'C:\Documents and Settings\DEV1\Desktop\RatePosts.bak' WITH REPLACE,
MOVE 'RatePosts' TO
'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\RatePosts.mdf',
MOVE 'RatePosts_Log' TO
'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\RatePosts_log.ldf'

UPDATE ... INNER JOIN

Example:

UPDATE sm
SET sm.SysConfId = sc.Id
FROM tblSKUMat sm
INNER JOIN tblSysConf sc
ON sm.Id = sc.Id

Create failed for Login 'xxx'. (Microsoft.SqlServer.Express.Smo)

I was trying to create a user with weak Login Name and password. Error message below shown.

Create failed for Login 'xxx'. (Microsoft.SqlServer.Express.Smo)
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)

The MUST_CHANGE option is not supported by this version of Microsoft Windows. (Microsoft SQL Server, Error: 15195)





What i do is uncheck the "Enforce password policy" when create the new user. This way you will not get the error message, but this is not the recommended way for the production Login Name and Password.





Problem solved.

Database diagram support objects cannot be installed because this database does not have a valid owner. To continue, first use the Files page ...

I backup a database called RatePosts from production server and restored it on my development machine. When I tried to access the diagram I received the following message:

Database diagram support objects cannot be installed because this database does not have a valid owner. To continue, first use the Files page of theDatabase Properties dialog box or the ALTER AUTHORIZATION statement to setthe database owner to a valid login, then add the database diagram support objects.

To solve that, open a new query and run query below

ALTER AUTHORIZATION ON DATABASE::DatabaseName TO [ComputerName\Administrator];

In my case, my database name: RatePosts, machine name: AppDev05

ALTER AUTHORIZATION ON DATABASE::RatePosts TO [AppDev05\Administrator];

Done. Problem solved.