Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x

Microsoft SQL Server

159K

Solutions

49K

Contributors

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

Share tech news, updates, or what's on your mind.

Sign up to Post

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service does not exist as an installed service'”.
0
What does it mean to be "Always On"?
LVL 4
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Windocks web UI
Windocks is an independent port of Docker's open source to Windows. This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
0
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this solution, while refreshing my recursive CTE skills.
0
Ready to get certified? Check out some courses that help you prepare for third-party exams.
5
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
0
The ultimate Citrix XenDesktop 7.x internals cheat sheet!
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down and start your ENTIRE Citrix environment.
0
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
2
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes. We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that with a PowerShell script.
0
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return different results based on different supplied parameters.
0
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
2
Veeam Disaster Recovery in Microsoft Azure
LVL 1
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
2
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
2
Balanced Data Distribution
I have a large data set and a SSIS package. How can I load this file in multi threading?
0
Load multiple files in SSIS
A couple of weeks ago, my client requested me to implement a SSIS package that allows them to download their files from a FTP server and archives them. Microsoft SSIS is the powerful tool which allows us to proceed multiple files at same time even we can use SSIS to download files from FTP server.
0
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts including details about the processes involved.
2
 
LVL 40

Author Comment

by:lcohan
Comment Utility
@Robb Hill -  sorry for late reply, just very busy lately..anyway, I would not modify the script I have for general usage as I believe it serves the purpose of notifying DBA's or sysadmins about locking/blocking occurring in their database servers however please see attached  script that you could adapt and use to serve your purpose. This script will kill the root blocker and the threshold is set at "@blocked_time > 120000" in the code where that value is in milliseconds so if root blocker is still blocking other processes after ~2 minutes it gets killed. I have it set on some older legacy 3rd party systems running SQL 2008 and 2008 R2 as a SQL job executed every 1 minute.

HTH and...please,please...use it cautiously and test it first in an environment where no damages/harm is done IF some SQL SPID gets killed.
Obviously with note above you or anyone else using it AS IS in a production environment and causing issues is sole responsible for the damages done.
TheSPIDkiller.sql
1
 
LVL 11

Expert Comment

by:Robb Hill
Comment Utility
One other thing that would make the initial email notification much more useful.

It currently only shows the blocked SQL versus the SQL that is blocking.

Any ideas on how to reformat the output to show both?
0
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied?
You can use SQL Server Initialize from Backup Transactional replication as described below.
1
 
LVL 40

Author Comment

by:lcohan
Comment Utility
Yes, there are some SQL code samples under each "dot"/item number and in my opinion they should stay in that place for the step by step instructions to be helpful.
0
 
LVL 40

Author Comment

by:lcohan
Comment Utility
Updated now and thanks for all the details/guidance.
0
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
1
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
4
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
Good Article and very well presented. Gets my YES vote :)
0
 

Expert Comment

by:Abrienne Jonethan
Comment Utility
Very Good and Informative Article,
I have also gone through other blog as there are other ways to reset a Lost SA Password in Microsoft SQL Server.
Apart from recovering from manual way you can also use free software like SQL Password Recovery
0
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
0
Efficient way to get backups off site to Azure
LVL 1
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

load balancing
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
0
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
1
 
LVL 66

Expert Comment

by:Jim Horn
Comment Utility
A very well written article on essential SSIS functionality dealing with record changes.  Voting Yes.
0
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
1
 
LVL 143

Author Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
While using this, I found that a field "parentid" will be helpful, so I added this into the function like this:

create function dbo.jsontotable(@json varchar(max))
returns @table table (id int identity, name varchar(max), value varchar(max), level int , object_type varchar(100) , parentid int )
as
begin
  -- http://www.json.org/
  -- we want to partse out objects:
  -- object : { string : value <, repeat> }
  -- string : " <anything except \ and "> or <\ followed by "\/bfnrt>  or < \unnnn >  repeat"
  -- array : [  value <, repeat>]
  -- value : object (starting with { ) or string starting with " ) or array (starting with [ ) or true or false or null or number
  -- number optionally -    digit(s)  optionally . followed by digits  and/or optionall e/E followed by digits
  -- so, we part character by character, waiting for the next special start/end to happen
  declare @pos int
  declare @len int
  declare @c char(1)

  declare @level int = 0

  declare @status int = 0
  -- 0: should start object : {
  -- 1: should start name :   "
  -- 2: waiting for end of string (") of name
  -- 3: should get :
  -- 4: check value start : could be " for value or [ for array  or { for object or true or false or null or number  could be ] as array end
  -- 5: waiting for end of string (") of value (special handling of \)
  -- 9: waiting for end of number
  --10: checking next part, could be , to continue, or ] end of array or } end object
  --11: checking next array part, could be status 0 or 1

  declare @name varchar(max)
  declare @value varchar(max)
  declare @name_start int
  declare @value_start int
  declare @parentid int
 
  declare @object_type varchar(100)
  declare @array_levels varchar(100) = ','

  set @pos = 1
  set @len = len(@json)

 
  while @pos <= @len
  begin
    set @c = substring(@json, @pos, 1)

    if @c = ' '
    or @c = char(9) -- tab
    or @c = char(10) -- nl
    or @c = char(13) -- cr
    begin
        -- skip this character to ignore whitespace between tokens
        goto next_loop
    end

    -- waiting for object start
    if @status in (0 ,11)
    begin
        if @c = '{'
        begin    
			select @parentid  = max(id) from @table where level = @level-1
            insert into @table ( name , object_type, value, level, parentid) values (null, 'object' , null, @level, @parentid )                 
            set @status = 1 -- wait for object name, starting with "
            set @level = @level + 1
        end
        else
        begin
            if @status = 0
            begin
				insert into @table ( object_type ) values ( 'expected object start' )
                return
            end
        end
        goto next_loop
    end -- if @status = 0

    --waiting for pair name
    if @status in ( 1,11)
    begin
        if @c = '"'
        begin
            set @name_start = @pos+1
            set @object_type = 'value'
            set @status = 2
        end
        else
        begin
            if @status = 1
            begin
                insert into @table ( object_type, value , level) values ( 'expected string start' , @c, @level)
                return
            end
        end
        goto next_loop
    end

    --waiting for string end
    if @status in ( 2,5 )
    begin
        if @c = '\'
        begin
            --skip 1 position to bypass any " at that place
            set @pos = @pos + 1
        end
        else
        begin
            if @c = '"'
            begin
                if @status = 2
                begin
                    -- get the unescaped name (may contain \ ...)
                    set @name = substring(@json, @name_start, @pos - @name_start)
                    set @status = 3
                end
                else
                begin
                    -- get the unescaped value (may contain \ ...)
                    set @value = substring(@json, @value_start, @pos - @value_start)
					select @parentid  = max(id) from @table where level = @level-1
                    insert into @table ( object_type, value, name, level, parentid) values ( @object_type, @value , @name, @level, @parentid)
                    -- after the value, check what comes next
                    set @status = 4
                end
            end
        end
        goto next_loop
    end -- if @status in ( 2,5 )

    --checking for : to delimit the name:value pair
    if @status = 3
    begin
      if @c = ':'
      begin
        set @status = 4
      end
      else
      begin
        insert into @table ( object_type, value , level) values ( 'expected column' , @c, @level)
        return
      end
      goto next_loop
    end -- if @status in ( 3 )

    if @status in ( 4 )
    begin
        if @c = '"'
        begin
          --if @array_levels like '%,'  +cast(@level as varchar(10)) + ',%'
          begin
              set @value_start = @pos + 1
              set @status = 5            
          end
        end
        
        if @c = ']'
        begin
            --go down 1 level
            set @level = @level - 1
            --we continue to wait for an object or next step
            set @status = 4
        end
                -- 4: check value start : could be " for value or [ for array  or { for object or true or false or null or number  could be ] as array end
        if @c = '['
        begin
            set @object_type = 'array'
			select @parentid  = max(id) from @table where level = @level-1
            insert into @table ( name , object_type, level, parentid) values (@name, @object_type , @level,@parentid)
            --go up 1 level
            set @object_type = 'item'
            set @name = null
            set @level = @level + 1
            --we continue to wait for an object
            set @status = 4
        end

        if @c = '{'
        begin
            set @object_type = 'object'
            --we want now to start the object name
            set @status = 0
            set @pos = @pos - 1
        end
        if @c = '}'
        begin
            --go down 1 level
            set @level = @level - 1
            --what's next
            set @status = 4            
        end
        if @c = 'n'
        begin -- starting null?
			select @parentid  = max(id) from @table where level = @level-1
            if substring(@json, @pos, 4) = 'null'
            begin
                insert into @table ( name , object_type, level, parentid) values (@name, 'null', @level, @parentid	)                 
                set @pos = @pos +3
                set @status = 4
            end
            else
            begin
                insert into @table ( value , object_type, level, parentid) values (substring(@json, @pos, 4), 'expected null', @level, @parentid)                                 
            end
            --what comes next?
            set @status = 4
        end

        if @c = 't'
        begin -- starting null?
			select @parentid  = max(id) from @table where level = @level-1
            if substring(@json, @pos, 4) = 'true'
            begin
                insert into @table ( name , object_type, value, level, parentid) values (@name, 'boolean' , 'true', @level, @parentid)                 
                set @pos = @pos +3
                set @status = 4
            end
            else
            begin
                insert into @table ( value , object_type, level, parentid) values (substring(@json, @pos, 4), 'expected true', @level, @parentid)                                 
            end
            --what comes next?
            set @status = 4
        end

        if @c = 'f'
        begin -- starting null?
			select @parentid  = max(id) from @table where level = @level-1
            if substring(@json, @pos, 5) = 'false'
            begin
                insert into @table ( name , object_type, value, level, parentid	) values (@name, 'boolean' , 'false', @level, @parentid	)                 
                set @pos = @pos +4
                set @status = 4
            end
            else
            begin
                insert into @table ( value , object_type, level,parentid) values (substring(@json, @pos, 5), 'expected false', @level, @parentid)                                 
            end
            --what comes next?
            set @status = 4
        end

        if @c = ','
        begin
          set @name = null
          --maybe object  or pair
          -- are we inside an object or an array;
          select top 1 @status = case when object_type = 'object' then 1 else 4 end   
            from @table where level = @level - 1 order by id desc
          
        end

        if @c like '[0-9-]'
        begin
            set @status = 9
            set @value_start = @pos
            set @object_type ='number'
        end
        
        goto next_loop
      end-- if @status in ( 4 )


      if @status = 9
      begin
        if @c like '[eE.0-9+-]'
        begin
            -- continue waiting
            set @status = 9
        end
        else
        begin
            set @value = substring(@json, @value_start, @pos - @value_start)
			select @parentid  = max(id) from @table where level = @level
            insert into @table ( object_type, value, name, level , parentid ) values ( @object_type, @value , @name, @level, @parentid)
            --get next part
            set @status = 4
            set @pos = @pos -1
        end
        goto next_loop

      end

    --insert into @table ( name ) values (@c)
    next_loop:
    set @pos = @pos + 1
  end -- loop

  return
end

Open in new window

0
Snapshot type is one of four popular types of SQL Replication techniques. In this article,  I will not only demonstrate how we configure this type but also when we need it in real situations.
3
 
LVL 7

Expert Comment

by:Yashwant Vishwakarma
Comment Utility
Hi Dung,
Thank you for the article , nicely explained.
Can we take three instances also for this practical one for distributor, second for publisher and third for subscriber or is it necessary to distributor and publisher on same server.

Voted YES !!
0
 
LVL 8

Author Comment

by:Dung Dinh
Comment Utility
Yes, we can. Actually, Replication has two architectures in overview.

The first architecture, we can install Publisher and Distributor on the same instance ( same server).

The second one, we can install three components on three different instances even three servers. Obviously, those servers must communicate together.

In real situations, the architecture can be more complicated. A Publisher is also Subscriber or otherwise.
1
Questions about connecting to Microsoft SQL Server using PHP come up fairly often.  This article covers a little of the basics and history.
0
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
4
 
LVL 10

Expert Comment

by:Jason clark
Comment Utility
Thanks for sharing this helpful article. But in my scenario i didn't have any recent backup of my deleted records also the recovery mode is set to simple. Then I tried SysTools SQL Log Analyzer and successfully recovered deleted records via SQL Transaction log file. it's really helpful if you don't have any backup of your database.
1
 

Expert Comment

by:parag Deshmukh
Comment Utility
Hi Yashwant,

I did not get one thing, when we are taking full backup of database before deletion of rows. Restore of that backup should directly give us all rows since we have taken backup before deletion of rows. Then why do we need transaction log backup?
0

Microsoft SQL Server

159K

Solutions

49K

Contributors

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.