European Windows 2012 Hosting BLOG

BLOG about Windows 2012 Hosting and SQL 2012 Hosting - Dedicated to European Windows Hosting Customer

SQL Server 2012 Hosting - HostForLIFE.eu :: SQL Queries for Database Analysis

clock August 2, 2019 12:03 by author Peter

In this post, I'll share few useful SQL queries for database analysis on SQL Server 2012. I shared few SQL queries useful in analyzing database, which I use quite often. This query will return all table names and no.of rows in it for built-in tables.

    -- List all table names and number of rows in it for user-defined tables 
    SELECT distinct t.name,prt.rows 
    FROM sys.tables t INNER JOIN sys.partitions AS prt 
    ON t.object_id = prt.object_id where t.is_ms_shipped=1 -- 0 for user-defined tables 
    order by prt.rows desc 


This query will return column names and its data type of a table.
    -- Get column names and its types of a table 
    SELECT cols.name,t.name 
    FROM sys.objects o join sys.columns cols on o.object_id= cols.object_id 
    join sys.types t on t.system_type_id=cols.system_type_id 
    and o.name='Employee'-- Table Name


This query will return file name, its size and file group name of a database.
    SELECT sdf.name AS [FileName], 
    size/128 AS [Size], 
    fg.name AS [File_Group_Name] 
    FROM sys.database_files sdf 
    INNER JOIN 
    sys.filegroups fg 
    ON sdf.data_space_id=fg.data_space_id 


Batch file to execute all sql files in a directory, Save it as .bat in a folder that have sql script files to be executed.
    @Echo Off 
    FOR /f %%i IN ('DIR *.Sql /B') do call :RunSql %%i 
    GOTO :END 
    :RunSql 
    Echo Executing SQL: %1 
    SQLCMD -S server1 -U user1 -P pwd1 -d DB1 -i %1 
    Echo Completed SQL: %1 
    :END 


This query will return all table names that have a Foreign key:
    SELECT SCHEMA_NAME(schema_id) AS SchemaName, 
    name AS TableName 
    FROM sys.tables where OBJECTPROPERTY(OBJECT_ID,'TableHasForeignKey') = 1 -- Return all

HostForLIFE.eu SQL Server 2012 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.



SQL Server 2012 Hosting - HostForLIFE.eu :: How to Clearing Down A Database Full Of Constraints In SQL Server?

clock November 17, 2016 08:18 by author Peter

In this post I will show you how to Clearing Down A Database Full Of Constraints In SQL Server. Have you ever been in a scenario where you have to clear down some data within a database that is chock full of constraints but don't want to wipe out your precious relationships, indices and all that other jazz?

I found myself in a similar situation earlier this week, and needed a clear-down script that would wipe out all of the data within an entire database, without being bothered by any existing constraints. Here it is.

    USE @YourTable;  
    EXEC sp_MSForEachTable "ALTER TABLE ? NOCHECK CONSTRAINT ALL"  
    EXEC sp_MSForEachTable "DELETE FROM ?"  
    EXEC sp_MSForEachTable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL"  
    GO  

What is this doing?
The script itself takes advantage of an undocumented stored procedure within SQL Server called sp_MSForEachTable that will actually iterate through all of the tables within a given database.

Now that we know we are going to be looping through each of the tables within the specified database, let's see what is going to happen to each of the tables.

    ALTER TABLE ? NOCHECK CONSTRAINT ALL
    This will disable any constraint checking that is present on the table (so, operations like deleting a primary key or a related object won't trigger any errors).

    DELETE FROM ?
    This will delete every record within the table.

    ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL
    This re-enables the constraint checking, bringing your table back to its original state, sans data.

Note
It is very important that you properly scope this query to the table that you are targeting to avoid any crazy data loss.

While I don't think that you could just leave that out and execute on master, I wouldn't want to even risk testing that out (although feel free to try it out and let me know if it nukes everything).

 

HostForLIFE.eu SQL Server 2012 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.



SQL Server 2012 Hosting - HostForLIFE.eu :: Fix SQL Server Can't Connect to Local Host

clock October 5, 2016 21:25 by author Peter

In this post, let me show you how to fix SQL Server Can't Connect to Local Host. Many times we find issues when connecting to the SQL Server. It gives us the message “SQL Server is not able to connect to  local host” as you can see on the following picture:

To fix this issue:
Go to Start->Run->Services.msc.

Once the Services are open, select SQL Server and start it, as per the given screenshot, given below:


After you do it, SQL server will be up and running again.

SQL Server sometimes stops because of some problem. These steps help to make it up. Thanks for reading.

HostForLIFE.eu SQL Server 2012 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.



SQL Server 2012 Hosting - HostForLIFE.eu :: How to Upload Excelsheet Data In SQL Server Table?

clock September 7, 2016 23:24 by author Peter

In This code snippet, i will tell you about how Uploading Excelsheet Data in SQL Server Table. Now, write the followind code:

protected void btnSend_Click(object sender, EventArgs e) { 
    try { 
        string path = string.Concat(Server.MapPath("~/File/" + fileuploadExcel.FileName)); 
        fileuploadExcel.SaveAs(path); 
        string connExcelString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\";", path); 
        OleDbConnection excelConnection = new OleDbConnection(connExcelString); 
        OleDbCommand cmd = new OleDbCommand("Select [Name],[Mobile],[Email],[City],[DataId],[Date],[Source] from [Sheet1$]", excelConnection); 
        excelConnection.Open(); 
        OleDbDataReader dReader; 
        dReader = cmd.ExecuteReader(); 
        SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection); 
        SqlBulkCopyColumnMapping mapName = new SqlBulkCopyColumnMapping("Name", "Name"); 
        sqlBulk.ColumnMappings.Add(mapName); 
        SqlBulkCopyColumnMapping mapMobile = new SqlBulkCopyColumnMapping("Mobile", "Mobile"); 
        sqlBulk.ColumnMappings.Add(mapMobile); 
        SqlBulkCopyColumnMapping mapEmail = new SqlBulkCopyColumnMapping("Email", "Email"); 
        sqlBulk.ColumnMappings.Add(mapEmail); 
        SqlBulkCopyColumnMapping mapCity = new SqlBulkCopyColumnMapping("City", "City"); 
        sqlBulk.ColumnMappings.Add(mapCity); 
        //SqlBulkCopyColumnMapping mapState = new SqlBulkCopyColumnMapping("State", "State"); 
        //sqlBulk.ColumnMappings.Add(mapState); 
        SqlBulkCopyColumnMapping mapDataId = new SqlBulkCopyColumnMapping("DataId", "DataId"); 
        sqlBulk.ColumnMappings.Add(mapDataId); 
        SqlBulkCopyColumnMapping mapAmount = new SqlBulkCopyColumnMapping("Date", "Date"); 
        sqlBulk.ColumnMappings.Add(mapAmount); 
        SqlBulkCopyColumnMapping mapSource = new SqlBulkCopyColumnMapping("Source", "Source"); 
        sqlBulk.ColumnMappings.Add(mapSource); 
        //Give your Destination table name 
        sqlBulk.DestinationTableName = "UploadedExcelData"; 
        sqlBulk.WriteToServer(dReader); 
        excelConnection.Close(); 
        UpdateRecords(); 
        lblMsg.Text = "File Data Uploaded Successfully... "; 
        File.Delete(path); 
    } catch (Exception ex) { 
        lblMsg.Text = "Something Went Wrong... Plz Check Excel File "; 
        //string script = "<script>alert('" + ex.Message + "');</script>"; 
    } 
}

HostForLIFE.eu SQL Server 2012 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.



SQL Server 2012 Hosting - HostForLIFE.eu :: SQL Queries for Database Analysis

clock August 11, 2016 21:32 by author Peter

In this post, I'll share few useful SQL queries for database analysis on SQL Server 2012. I shared few SQL queries useful in analyzing database, which I use quite often. This query will return all table names and no.of rows in it for built-in tables.

    -- List all table names and number of rows in it for user-defined tables 
    SELECT distinct t.name,prt.rows 
    FROM sys.tables t INNER JOIN sys.partitions AS prt 
    ON t.object_id = prt.object_id where t.is_ms_shipped=1 -- 0 for user-defined tables 
    order by prt.rows desc 


This query will return column names and its data type of a table.
    -- Get column names and its types of a table 
    SELECT cols.name,t.name 
    FROM sys.objects o join sys.columns cols on o.object_id= cols.object_id 
    join sys.types t on t.system_type_id=cols.system_type_id 
    and o.name='Employee'-- Table Name


This query will return file name, its size and file group name of a database.
    SELECT sdf.name AS [FileName], 
    size/128 AS [Size], 
    fg.name AS [File_Group_Name] 
    FROM sys.database_files sdf 
    INNER JOIN 
    sys.filegroups fg 
    ON sdf.data_space_id=fg.data_space_id 


Batch file to execute all sql files in a directory, Save it as .bat in a folder that have sql script files to be executed.
    @Echo Off 
    FOR /f %%i IN ('DIR *.Sql /B') do call :RunSql %%i 
    GOTO :END 
    :RunSql 
    Echo Executing SQL: %1 
    SQLCMD -S server1 -U user1 -P pwd1 -d DB1 -i %1 
    Echo Completed SQL: %1 
    :END 


This query will return all table names that have a Foreign key:
    SELECT SCHEMA_NAME(schema_id) AS SchemaName, 
    name AS TableName 
    FROM sys.tables where OBJECTPROPERTY(OBJECT_ID,'TableHasForeignKey') = 1 -- Return all

HostForLIFE.eu SQL Server 2012 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.



SQL Server Hosting - HostForLIFE.eu :: SQL Server Inline Queries

clock June 7, 2016 21:45 by author Anthony

In this tutorial, I will explain about inline queries. Most of the developers are very familiar with inline queries. Inline queries are basically known as sub queries or Inner Select statements which are always used inside a SELECT, INSERT, UPDATE, or DELETE statement, or inside another inline query.  They can be used anywhere in SQL scripts to encounter the conditional expressions. The SQL statement containing an Inline query is also known an outer query or outer select.

Free ASP.NET Hosting - Europe

How can Inline Queries helpful?

Most of the T-SQL statements which include inline queries can be alternatively formulated as join to encounter the conditional expressions. Other questions can be posed only with inline queries. In T-SQL, there is usually no performance difference between a statement that includes an inline query and a semantically equivalent version that does not. However, in some cases where existence must be checked, a join yields better performance.

Components of an Inline Query

An inline query has the same features as a normal SQL query. They only persist in the SQL statements and could have the following components –

  • A regular SELECT query including regular select list components from the main table.
  • A regular FROM clause including one or more table, function or view names.
  • An optional WHERE clause to encounter the conditional expressions.
  • An optional GROUP BY clause if your query contains aggregation functions.
  • An optional HAVING clause if your query contains aggregation functions.

Points to be remember

The SELECT query of an Inline query is always enclosed in parentheses. It cannot include a COMPUTE or FOR BROWSE clause, and may only include an ORDER BY clause when a TOP clause is also specified. There are three basic types of inline queries-
Operate on lists introduced with IN, or those that a comparison operator modified by ANY or ALL.
Are introduced with an unmodified comparison operator and must return a single value.
Are existence tests introduced with EXISTS.

To understand their functionality, we will create a table variable to work as the base table for the inline statements as given below-

---- declare OrderMaster table variable body
DECLARE @OrderMaster TABLE
(
OrdId INT IDENTITY(100,1),
OrdDate VARCHAR(30),
CustName VARCHAR(20)
)
---- declare OrderDetails table variable body
DECLARE @OrderDetails TABLE
(
OrdId INT,
ItemId VARCHAR(30),
ItemName VARCHAR(20),
SellingPrice Decimal(12,4)
)
---- Insert Values
INSERT INTO @OrderMaster (OrdDate,CustName)
Values (Getdate(),'Ryan Arjun'),(Getdate(),'Bill Trade'),
(Getdate(),'Rosy White')

---- Pull Order Data
SELECT * FROM @OrderMaster

----Fill Order Details
Insert Into @OrderDetails (OrdId, ItemId, ItemName, SellingPrice)
Values (100, 201,'Apple',135.78),(100, 202,'Mango',235.78)
,(101, 203,'Banana',124.50),(101, 204,'Orange',321.15)
,(102, 205,'Banana',124.50),(102, 204,'Orange',321.15),(102, 201,'Apple',135.78)

--- Pull Order Details
select * from @OrderDetails

Now, we have the tables and want to pull the order and customer wise total sales.

Pull Single Value

There are many ways to do this but we are using inline query to accomplish this task. To pull the single value, we are using inline query within the main SQL statement as given below-

---- Use inline query in the select statement
select OrdId, CustName,
---- Inline query for single value
[Price] =(Select 
[Price]=sum(SellingPrice)
from @OrderDetails
where OrdId=  Om.OrdId
)
from @OrderMaster OM


Pull Multiple Values with Group By
If we want to pull more than one column then inline query should work as join with the main query as given below:

---- Inline Query as Join
SELECT Om.OrdId, Om.CustName,
OD.[Item-Qty], OD.Price
FROM @OrderMaster OM
JOIN
(
SELECT  OrdId,
[Item-Qty]=COUNT(ItemId),
[Price]=SUM(SellingPrice)
FROM @OrderDetails
GROUP BY ORDID
)OD
ON OM.ORDID=OD.ORDID


Pull Value with Where Clause and Group By
If we want to pull more than one column based on some conditional expression then inline query should work as join with the main query as given below:

---- Inline Query as Join
SELECT Om.OrdId, Om.CustName,
OD.[Item-Qty], OD.Price
FROM @OrderMaster OM
JOIN
(SELECT  OrdId,
[Item-Qty]=COUNT(ItemId),
[Price]=SUM(SellingPrice)
FROM @OrderDetails
---- Conditional Expression
WHERE ItemName='Apple'
GROUP BY ORDID
)OD
ON OM.ORDID=OD.ORDID

Conclusion

It’s very beneficial concept in SQL and we can use them inside a SELECT, INSERT, UPDATE, or DELETE statement, or inside another inline query.  They are easily applicable in the function and stored procedure. These features are important in some Transact-SQL statements; the inline-query can be evaluated as if it were an independent query. Conceptually, the inline-query results are substituted into the outer query.

 

HostForLIFE.eu SQL Server 2016 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.



SQL Server 2012 Hosting - HostForLIFE.eu :: Auto Identity Column Value Jump Error in SQL Server 2012

clock June 1, 2016 18:50 by author Peter

From SQL Server 2012 version, when SQL Server instance is restarted, then table's Identity value is jumped and the actual jumped value depends on identity column data type. If it's integer (int) data type, then jump value is a thousand and if huge integer (bigint), then jump value is ten thousand. From our application point of view, this increment isn't acceptable for all the business cases especially once the value shows to the client. this is the special case/issue ships with only SQL Server 2012 and older versions don't have any such issue.

A few days ago, our QA Engineer claims that one of our table's identity column jumped ten thousand. Which means the last identity value of that table was 2200 now it's 12001. In our business logic is like that the value shows to the client and it will not be accepted by the client. So we must solve the issue.
Using the Code

The first time, we all are surprised and confused on how it's possible? we usually don't insert any value in identity column (insert value to identity column is possible). The identity value is maintained by SQL Server itself. one of our core team members started investigation the issue and found out the solution. Now, i want to elaborate the issue and solution that was found out by my colleague.

Now, you need to setup SQL Server 2012 and create a test database. Then create a table with auto identity column with the following code:
create table MyTestTable(Id int Identity(1,1), Name varchar(255));

Now insert 2 rows there:
insert into MyTestTable(Name) values ('Mr.Tom');
insert into MyTestTable(Name) values ('Mr.Jackson');

You see the result:
SELECT Id, Name FROM MyTestTable;

The result is as expected. Now just restart your SQL Server service. There are various ways in which you can do it. We did it from SQL Server management studio.

Now, insert another 2 rows to the same table again:
insert into MyTestTable(Name) values ('Mr.Tom2');
insert into MyTestTable(Name) values ('Mr.Jackson2');

Now, you can see the result:
SELECT Id, Name FROM MyTestTable;


Now you see that after restarting the SQL Server 2012 instance, then identity value starts with 1002. It means it jumped 1000. Previously, I said that we also see if the data type of that identity column is bigint, then it will jump 10000.


Microsoft declares it is a feature rather than a bug and in many scenarios it would be helpful. But in our case, it would not be acceptable because that number is shown to the client and the client will be surprised to see that new number after jump and the new number depends on how many times SQL Server is restarted. If it is not visible to the client, then it might be acceptable so that the number is used internally.
Solutions

If we are not interested in this so called feature, then we can do two things to stop that jump.
    Using Sequence
  Register -t272 to SQL Server Startup Parameter

Using Sequence
First, we need to remove Identity column from tables. Then create a sequence without cache feature and insert number from that sequence. The following is the code sample:
CREATE SEQUENCE Id_Sequence
    AS INT
    START WITH 1
    INCREMENT BY 1
    MINVALUE 0
    NO MAXVALUE
   NO CACHE

   insert into MyTestTable values(NEXT VALUE FOR Id_Sequence, 'Mr.Tom');

   insert into MyTestTable values(NEXT VALUE FOR Id_Sequence, 'Mr.Jackson');


How to Register -t272 to SQL Server Startup Parameter?
First, open the SQLServer configuration manager from your server. Select SQL Server 2012 instance there right client and select Properties menu. You will find a tabbed dialog window. You select start up parameters tab from there and register -t272. Then restart SQL Server 2012 instance again and see the difference as you can see on the following picture:

If too many tables contain identity column to your database and all contain existing values, then it is better to go for solution 2. Because it is a very simple solution and its scope is server wise. This means if you add SQL Server 2012 parameter -t272 there, then it will affect all your databases there. If you want to create a new database and you need auto generated number field, then you can use solution 1, that means use sequence value to a column instead of auto Identity value. There are so many articles you can find online about when you will use auto identity column when using sequence and advantages/disadvantages of each other. I hope you will read all those and take the appropriate decision.

HostForLIFE.eu SQL Server 2012 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

 



SQL Server 2014 Hosting - HostForLIFE.eu :: How to Empty and Deleter All SQL Database?

clock February 9, 2016 23:06 by author Peter

Today, let me show you how to empty and delete all SQL Database. Now write the following code snippet for Clear Blank SQL Database:

    DECLARE @name VARCHAR(128) 

    DECLARE @SQL VARCHAR(254) 

    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 ORDER BY [name]) 

    WHILE @name is not null 

    BEGIN 

        SELECT @SQL = 'DROP PROCEDURE [dbo].[' + RTRIM(@name) +']' 

        EXEC (@SQL) 

        PRINT 'Dropped Procedure: ' + @name 

        SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 AND [name] > @name ORDER BY [name]) 

    END 

    GO 

    /* Drop all views */ 

    DECLARE @name VARCHAR(128) 

    DECLARE @SQL VARCHAR(254)

 

    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'V' AND category = 0 ORDER BY [name]) 

    WHILE @name IS NOT NULL 

    BEGIN 

        SELECT @SQL = 'DROP VIEW [dbo].[' + RTRIM(@name) +']' 

        EXEC (@SQL) 

        PRINT 'Dropped View: ' + @name 

        SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'V' AND category = 0 AND [name] > @name ORDER BY [name]) 

    END 

    GO 

    /* Drop all functions */ 

    DECLARE @name VARCHAR(128)  

    DECLARE @SQL VARCHAR(254)     

    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND category = 0 ORDER BY [name])      

    WHILE @name IS NOT NULL 

    BEGIN 

        SELECT @SQL = 'DROP FUNCTION [dbo].[' + RTRIM(@name) +']' 

        EXEC (@SQL) 

        PRINT 'Dropped Function: ' + @name 

        SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND category = 0 AND [name] > @name ORDER BY [name]) 

    END 

    GO      

    /* Drop all Foreign Key constraints */ 

    DECLARE @name VARCHAR(128) 

    DECLARE @constraint VARCHAR(254) 

    DECLARE @SQL VARCHAR(254)      

    SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME)      

    WHILE @name is not null 

    BEGIN 

        SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME) 

        WHILE @constraint IS NOT NULL 

        BEGIN 

            SELECT @SQL = 'ALTER TABLE [dbo].[' + RTRIM(@name) +'] DROP CONSTRAINT [' + RTRIM(@constraint) +']' 

            EXEC (@SQL) 

            PRINT 'Dropped FK Constraint: ' + @constraint + ' on ' + @name 

            SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND CONSTRAINT_NAME <> @constraint AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME) 

        END 

    SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME) 

    END 

    GO      

    /* Drop all Primary Key constraints */ 

    DECLARE @name VARCHAR(128) 

    DECLARE @constraint VARCHAR(254) 

    DECLARE @SQL VARCHAR(254)      

    SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME)      

    WHILE @name IS NOT NULL 

    BEGIN 

        SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME) 

        WHILE @constraint is not null 

        BEGIN 

            SELECT @SQL = 'ALTER TABLE [dbo].[' + RTRIM(@name) +'] DROP CONSTRAINT [' + RTRIM(@constraint)+']' 

            EXEC (@SQL) 

            PRINT 'Dropped PK Constraint: ' + @constraint + ' on ' + @name 

            SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND CONSTRAINT_NAME <> @constraint AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME) 

        END 

    SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME) 

    END 

    GO      

    /* Drop all tables */ 

    DECLARE @name VARCHAR(128) 

    DECLARE @SQL VARCHAR(254)      

    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 ORDER BY [name])      

    WHILE @name IS NOT NULL 

    BEGIN 

        SELECT @SQL = 'DROP TABLE [dbo].[' + RTRIM(@name) +']' 

        EXEC (@SQL) 

        PRINT 'Dropped Table: ' + @name 

        SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 AND [name] > @name ORDER BY [name]) 

    END 

    GO 

HostForLIFE.eu SQL Server 2014 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

 



SQL Server 2012 Hosting - HostForLIFE.eu :: Managing Number of SQL Server ErrorLog

clock January 5, 2016 21:36 by author Peter

Microsoft SQL Server saves 7 errorlog files by default. When new errorlog is made, the recent one will be deleted at identical time. If you wish to keep a lot of errorlog, you'll follow 2 ways below to manage number of SQL Server ErrorLog, including both increasing and decreasing ErrorLog number.
Method 1: configure SQL Server ErrorLog number in SSMS
Step 1: Open SQL Server Management Studio. connect with SQL Server with SQL Server Authentication.

Tips: If user account password forgot, you'll only reset user password or change user forgotten password. So login to SQL Server with SQL Server Authentication and new user password. Otherwise, even though you'll successfully connect with SQL Server with Windows Authentication, the following error still occurs as soon as you want to configure SQL Server logs.

Step 2: Navigate to Management > SQL Server Logs. Right-click on SQL Server Logs and choose Configure.

Step 3: In pop-up window Configure SQL Server Error Logs, tick the box "Limit the number of error log files before they are recycling". And set "Maximum number of error log" with willing number. Save changes at last before you close this window.

Method 2: Change Number of SQL Server ErrorLog in Registry Editor

Step 1: First, type regedit in Start box, and press Enter to run Registry Editor.

Step 2: Now, Locate to the following path(1 or 2) and create a new entry in registry editor.
1. Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\SQLEXPRESS\MSSQLServer
2. Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\ MSSQLServer\MSSQLServer

Right-click in the right blank pane of registry editor, and tap New > QWORD (64-bit) Value button in pop-up options.


Step 3: Rename the entry as NumErrorLogs and double-click it to edit its value. Type a number that you want to save SQL Server ErrorLog file. Tick Decimal under Base and click OK.

Close Registry Editor and finish setting on increasing or decreasing number of SQL Server Errorog file.

HostForLIFE.eu SQL Server 2012 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.



SQL Server 2012 Hosting - HostForLIFE.eu:: How to Configuring a Server Side Trace in SQL Server 2012?

clock November 24, 2015 19:37 by author Peter

When I’m after SQL Server performance problems, SQL Server Profiler is still my beloved tool. Allthough i do know that extended events offer a mor light-weight answer, those are still a bit cumbersome to use (but I’ve seen that we are able to expect some enhancements with SQL Server 2012).

When I’m using profiler to isolate performance problems, I attempt to tack server side traces, whenever doable. luckily, SQL Server Profiler can assist you making a script for a server side trace (File/Export/Script Trace Definition), so you don’t have to figure out all the event- and column-codes. As i used to be doing a similar configuration again and again, i made a decision to separate the TSQL code for the configuration within a stored procedure.

And here comes dbo.configureServerSideTrace:
if object_id('dbo.configureServerSideTrace', 'P') is not null
drop procedure dbo.configureServerSideTrace
go

-- Example for:
-- Start Trace
--   declare @traceID int
--   exec dbo.configureServerSideTrace @traceStatus = 1
--                                    ,@traceID = @traceID output
--                                    ,@maxFileSize = 10000
--                                    ,@traceFileName = N'e:\VMITrace\Undo'
--                                    ,@spId = @@spid
--
-- End Trace
--   exec dbo.configureServerSideTrace @traceStatus = 0, @traceID = @traceID

create procedure dbo.configureServerSideTrace
             (@traceStatus   bit                  -- 1 => Start Trace
                                                  -- 0 => Stop Trace
             ,@traceID       int output           -- If the Trace is started, this param will return the TraceID
                                                  -- For stopping the trace, the param has to be provided
             ,@spId          int           = null -- provide the @@spid, if you want to filter only events for this conection
                                                  -- Optional. If not provided => no filter. Not needed for stopping the trace
             ,@maxFileSize   bigint        = 5000 -- Maximum Trace File Size in Megabyte. Trace will be stopped, if the filesize is reached.
             ,@traceFileName nvarchar(200) = null -- Name of the trace file (server side!)
                                                  -- Optional. Not neded for stoping the trace
                                                  -- Attention! If the file already exists, the SP will yield an error
                                                  -- and no trace is started.
             ) as
begin

if (@traceStatus = 0)
begin
exec sp_trace_setstatus @TraceID, 0
exec sp_trace_setstatus @TraceID, 2
return;
end

-- Create a Queue
declare @rc int

exec @rc = sp_trace_create @TraceID output, 0, @traceFileName, @maxfilesize, NULL
if (@rc != 0) goto error

-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 43, 15, @on
exec sp_trace_setevent @TraceID, 43, 48, @on
exec sp_trace_setevent @TraceID, 43, 1, @on
exec sp_trace_setevent @TraceID, 43, 34, @on
exec sp_trace_setevent @TraceID, 43, 35, @on
exec sp_trace_setevent @TraceID, 43, 51, @on
exec sp_trace_setevent @TraceID, 43, 4, @on
exec sp_trace_setevent @TraceID, 43, 12, @on
exec sp_trace_setevent @TraceID, 43, 13, @on
exec sp_trace_setevent @TraceID, 43, 14, @on
exec sp_trace_setevent @TraceID, 43, 22, @on
exec sp_trace_setevent @TraceID, 42, 1, @on
exec sp_trace_setevent @TraceID, 42, 14, @on
exec sp_trace_setevent @TraceID, 42, 22, @on
exec sp_trace_setevent @TraceID, 42, 34, @on
exec sp_trace_setevent @TraceID, 42, 35, @on
exec sp_trace_setevent @TraceID, 42, 51, @on
exec sp_trace_setevent @TraceID, 42, 4, @on
exec sp_trace_setevent @TraceID, 42, 12, @on
exec sp_trace_setevent @TraceID, 45, 16, @on
exec sp_trace_setevent @TraceID, 45, 48, @on
exec sp_trace_setevent @TraceID, 45, 1, @on
exec sp_trace_setevent @TraceID, 45, 17, @on
exec sp_trace_setevent @TraceID, 45, 18, @on
exec sp_trace_setevent @TraceID, 45, 34, @on
exec sp_trace_setevent @TraceID, 45, 35, @on
exec sp_trace_setevent @TraceID, 45, 51, @on
exec sp_trace_setevent @TraceID, 45, 4, @on
exec sp_trace_setevent @TraceID, 45, 12, @on
exec sp_trace_setevent @TraceID, 45, 13, @on
exec sp_trace_setevent @TraceID, 45, 14, @on
exec sp_trace_setevent @TraceID, 45, 22, @on
exec sp_trace_setevent @TraceID, 45, 15, @on
-- XML Statistics Profile
exec sp_trace_setevent @TraceID, 146, 1, @on
exec sp_trace_setevent @TraceID, 146, 51, @on
exec sp_trace_setevent @TraceID, 146, 4, @on
exec sp_trace_setevent @TraceID, 146, 12, @on

-- Filter: Log only events for the provided @@spid
if @spId is not null
exec sp_trace_setfilter @TraceID, 12, 0, 0, @spID

-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1

goto finish

error:
select ErrorCode=@rc

finish:
end
go


Some annotations:

  • For the parameters, see the comments.
  • Don’t specify a string for the trace file. .TRC will be added automatically.
  • ensure that the output file doesn't already exist. Otherwise you’ll get an error.
  • very often I replace the code for beginning and stopping the trace inside “interesting code” inside a stored procedure. That is, I’m wrapping some more or less awkward code by starting and stopping a trace like this:

declare @traceID int
exec dbo.configureServerSideTrace @traceStatus = 1
                                 ,@traceID = @traceID output
                                 ,@maxFileSize = 10000
                                 ,@traceFileName = N'e:\MyTrace\Test'
                                 ,@spId = @@spid

--
-- Code of interest
--

exec dbo.configureServerSideTrace @traceStatus = 0, @traceID = @traceID

HostForLIFE.eu SQL Server 2014 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.



About HostForLIFE.eu

HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

We have offered the latest Windows 2016 Hosting, ASP.NET Core 2.2.1 Hosting, ASP.NET MVC 6 Hosting and SQL 2017 Hosting.


Tag cloud

Sign in