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

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
Independent Software Vendors: We Want Your Opinion
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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
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
Will your db performance match your db growth?
LVL 3
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

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
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
How Blockchain Is Impacting Every Industry
LVL 4
How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

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
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and will then append that to the file name.
0
 

Expert Comment

by:Shaad Parihar
Comment Utility
The above code is for one database.
If i have multiple databases and i want to use a single batch file.how i add multiple database in the code.
0
Title Image
Many times as a report developer I've been asked to display normalized data such as three rows with values Jack, Joe, and Bob as a single comma-separated string such as 'Jack, Joe, Bob', and vice versa.  Here's how to do it. 
6
 
LVL 66

Author Comment

by:Jim Horn
Comment Utility
Done.  This is a republication from My Website which is allowed with the NoIndex checkbox checked per recent rules change.
0
Training
Members of Experts Exchange have asked a hundred times 'Where should I go to get SQL Server Training?' As of March 2016 here's the answer.
5
 
LVL 34

Expert Comment

by:Mike Eghtebas
Comment Utility
Very helpful article. Thank you very much.

Mike
0
 
LVL 1

Expert Comment

by:csehz
Comment Utility
Thanks Jim, I have found this article as you copied in my question and found it very useful
0
Fuzzy Lookup Transformation
My client has a dictionary table. They're defining a list of standard naming convention. Now, they are requiring my team to provide us a mechanism how to match new incoming data with existing data in their system.
2
 
LVL 51

Expert Comment

by:Vitor Montalvão
Comment Utility
Very nice
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.