Help with Transferring Linked Server to another server? Please advice me as I'm trying to move the linked server (Providers with the tables in Catalogs) from SQL server 2005 to another upgraded sqlserver 2008, I scripted out everything from there, but not sure what's the next step or how to import them back in the new server?

Thanks in advance,

I scripted out everything from there, but not sure what's the next step or how to import them back in the new server?

Just run the scripted out version (from 2005 servers) on the new sql 2008 server.

You need to manually key-in the passwords as when you script out linked servers, passwords are not scripted out - they are grabbled - ########.

You can check the linked server properties on old server and then match them on the new server using below t-sql :

SELECT ss.server_id
          ,ss.name
          ,'Server ' = Case ss.Server_id
                            when 0 then 'Current Server'
                            else 'Remote Server'
                            end
          ,ss.product
          ,ss.provider
          ,ss.catalog
          ,'Local Login ' = case sl.uses_self_credential
                            when 1 then 'Uses Self Credentials'
                            else ssp.name
                            end
           ,'Remote Login Name' = sl.remote_name
           ,'RPC Out Enabled'    = case ss.is_rpc_out_enabled
                                   when 1 then 'True'
                                   else 'False'
                                   end
           ,'Data Access Enabled' = case ss.is_data_access_enabled
                                    when 1 then 'True'
                                    else 'False'
                                    end
           ,ss.modify_date
      FROM sys.Servers ss
 LEFT JOIN sys.linked_logins sl
        ON ss.server_id = sl.server_id
 LEFT JOIN sys.server_principals ssp
        ON ssp.principal_id = sl.local_principal_id

Edit:

I know this is old question dated in 2013, but my edit will be useful for future readers ...

If you want to move all or selected Linked servers from one server to another, a better automated option would be to use  Copy-LinkedServer (part of dbatools written by Chrissy LeMaire) can be very useful.

e.g.

  Copy-LinkedServer -Source YourSourceServerName -Destination YourDestinationServerName -LinkedServers mySQL1, lssvr1 -Force

Remember to remove -LinkedServers parameter if you want to migrate all linked servers from one instance to another.


mysql: deleted performance_schema, is it a problem? Been following this tutorial
https://stackoverflow.com/questions/3927690/howto-clean-a-mysql-innodb-storage-engine/4056261#comment14041132_4056261

in the process I deleted performance_schema along with the other dbs.

I dont see it reappear after reimporting dbs. Is it ok if I dont have performance schema?

The MySQL Performance Schema is a feature for monitoring MySQL Server execution at a low level. The Performance Schema is available as of MySQL 5.5.3.

From MySQL Manual there are some of the features of the performance_schema database.

  The Performance Schema provides a way to inspect internal execution of
  the server at runtime. It is implemented using the PERFORMANCE_SCHEMA
  storage engine and the performance_schema database. The Performance
  Schema focuses primarily on performance data. This differs from
  INFORMATION_SCHEMA, which serves for inspection of metadata.

  The Performance Schema monitors server events. An “event” is anything
  the server does that takes time and has been instrumented so that
  timing information can be collected. In general, an event could be a
  function call, a wait for the operating system, a stage of an SQL
  statement execution such as parsing or sorting, or an entire statement
  or group of statements. Currently, event collection provides access to
  information about synchronization calls (such as for mutexes) file and
  table I/O, table locks, and so forth for the server and for several
  storage engines

  Performance Schema events are distinct from events written to the
  server's binary log (which describe data modifications) and Event
  Scheduler events (which are a type of stored program).

For detailed information look this Link Performance Schema.


SQL Server: join two tables with "valid-to" dates I have two tables like so:

T1_Key, T1_Data, T1_ValidUntil
1         A       2000-01-01
1         B       2000-06-30
2         C       2005-05-31
3         D       2004-12-31
3         E       2007-04-30
3         F       2008-01-31

T2_Key, T2_Data, T2_ValidUntil
1         R       2002-03-31
2         S       2001-06-30
2         T       2003-02-28
2         U       2005-05-31
3         V       2006-09-30
3         W       2007-06-30

This defines a number of intervals where specific data is valid. For example for key 2 and date 2002-01-01, the entries (2, C, 2005-05-31) and (2, T, 2003-02-28) are valid (i.e., the entries with the next larger date). For key 2 and date 2010-01-01 no entry is valid.

For a single query like above, I just query both tables seperately and join the data in my application. However, for a maintenance application I'd like to join these two tables. The result should contains all valid intervals/combinations, like so (Key 3 is the most complex one, because there are no shared dates):

T1_Key, T1_Data, T1_ValidUntil, T2_Key, T2_Data, T2_ValidUntil
1        A        2000-01-01    1       R        2002-03-31
1        B        2000-06-30    1       R        2002-03-31
2        C        2005-05-31    2       S        2001-06-30
2        C        2005-05-31    2       T        2003-02-28
2        C        2005-05-31    2       U        2005-05-31
3        D        2004-12-31    3       V        2006-09-30  <- I1
3        E        2007-04-30    3       V        2006-09-30  <- I2
3        E        2007-04-30    3       W        2007-06-30  <- I3
3        F        2008-01-31    3       W        2007-06-30  <- I4

ASCII-Art vor Case 3 (Timelines):

     2004-12-31        2007-04-30             2008-01-31
T1  -----|-----------------|-----------------------|----------
     I1      I2       I3       I4      Nothing valid here
T2 ---------------|-----------------|--------------------------
              2006-09-30       2007-06-30

Currently I simply join using the keys, but that gives a lot of combinations which are not valid. I therefore need a way to say "Join this with the entry having the next-higher (or equal) date in the other table", and a way to do that in both directions...

The scheme is predefined and I can not change it very much. A third many-to-many table seems like redundant data for me.

Thanks in advance!

DECLARE @t1 TABLE (T1_key int, T1_Data char(1), T1_ValidUntil datetime)
DECLARE @t2 TABLE (T2_key int, T2_Data char(1), T2_ValidUntil datetime)

INSERT @t1 VALUES (1, 'A', '2000-01-01')
INSERT @t1 VALUES (1, 'B', '2000-06-30')
INSERT @t1 VALUES (2, 'C', '2005-05-31')
INSERT @t1 VALUES (3, 'D', '2004-12-31')
INSERT @t1 VALUES (3, 'E', '2007-04-30')
INSERT @t1 VALUES (3, 'F', '2008-01-31')

INSERT @t2 VALUES (1, 'R', '2002-03-31')
INSERT @t2 VALUES (2, 'S', '2001-06-30')
INSERT @t2 VALUES (2, 'T', '2003-02-28')
INSERT @t2 VALUES (2, 'U', '2005-05-31')
INSERT @t2 VALUES (3, 'V', '2006-09-30')
INSERT @t2 VALUES (3, 'W', '2007-06-30')

SELECT
   T1.*, T2x.*
FROM
   @t1 T1
   CROSS APPLY
   (SELECT TOP 1*
   FROM @t2
   WHERE T1_key = T2_key AND T2_ValidUntil >= T1_ValidUntil
   ORDER BY T2_ValidUntil
   ) T2x
UNION
SELECT
   T1x.*, T2.*
FROM
   @t2 T2
   CROSS APPLY
   (SELECT TOP 1*
   FROM @t1
   WHERE T1_key = T2_key AND T1_ValidUntil >= T2_ValidUntil
   ORDER BY T1_ValidUntil
   ) T1x


Finding entries which have no foreign key mapping in any other table Let's say I have Table A, which has one column id, which is unique within the table. I have multiple other tables: Table B, Table C, ..., Table N. Each of these tables has a column A_id, which is a foreign key to id of Table A.

Is there an elegant way to get all rows of Table A which are not mapped to as a foreign key by any of the other tables? Ideally, I would like to see if there's a way to do this without having to type in a complicated query that involves manually querying each other dependent table, but any approach would be appreciated.

This can be accomplished with a NOT EXISTS () clause in your WHERE clause.

SELECT id
FROM A
WHERE NOT EXISTS (
SELECT id
FROM B
WHERE A.ID = B.A_id
)
AND NOT EXISTS (
SELECT id
FROM C
WHERE A.ID = C.A_id)
...
...

You could generate this query programmatically by querying the schema for tables that have a foreign key on A.id, and then iterating over them to create the WHERE clause...


Sql server - Rebuilding Indexes - Does it affect foreign keys? i am looking at speeding up the performance of my database, and thought that Rebuilding some indexes might help. My question is, will this invalidate Foreign Keys from other tables pointing to the table I am rebuilding, or will the links be preserved?

will this invalidate Foreign Keys from other tables pointing to the table I am rebuilding, or will the links be preserved?

No it wont invalidate foreign keys.

Rebuilding an index drops and re-creates the index thereby removing fragmentation and it reclaims disk space by compacting the pages based on the specified or existing fill factor setting, and reorders the index rows in contiguous pages. FOREIGN KEY constraints do not have to be dropped in advance.

Now if you disable Index then :

The Query optimizer wont be able to use the index and any of your queries that uses index hint referencing the disabled index will FAIL. SQL Server retains the metadata about the index and the usage stats in sys.dm_db_index_usage_stats. Also, Foreign Key constraints are DISABLED.

Disabling Non-Clustered (NC) Index: This will straight away delete the index pages and thereby freeing up the space in the database.

Disabling a CLUSTERED Index: All related NC indexes and views become unavailable and FK's are also disabled. Any queries that reference the table will fail, as a CLUSTERED index is itself the data. Note that the data will still remain, but will be inaccessible. Only way to again access data is to REBUILD the index.


SQL Server: How to call stored procedure without schema name? SQL Server 2005std/2008web edition
db=user=schema=owner='John'

I'm moving website from one server to another. This piece of code works well on old server with SQL Server 2005.

    Dim sqlCmdVehicle As SqlCommand = New System.Data.SqlClient.SqlCommand("mySP_Name", sqlConn)
    Dim dtVehicle As New DataTable

    With sqlCmdVehicle
        .Parameters.AddWithValue("FullStockNo", "N102010")
        .CommandType = CommandType.StoredProcedure
    End With

    sqlConn.Open()
    sqlAdapter.SelectCommand = sqlCmdVehicle
    sqlAdapter.Fill(dtVehicle)

DB is backed up and successfully restored on new server. When I try to run with new SQL Server 2008 I got an error:

  Could not find stored procedure 'mySP_Name'.

pointing at last line.
I can see procedures with 'MS SQL management studio' not as  dbo.mySP_Name but as John.mySP_Name

When I change

System.Data.SqlClient.SqlCommand("mySP_Name", sqlConn)

into

System.Data.SqlClient.SqlCommand("John.mySP_Name", sqlConn)

all works well, but it's the same with all other procedures and a lot of such kind places in code :(

I got http://msdn.microsoft.com/en-us/library/ms189915.aspx, but caller's default schema is correct.
Again, all works well on old box.

What should I fix in SQL to allow run SP without explicitly mentioned user/schema name?

Thank you.

=======================================

Unfortunately, I didn't find fine solution. The way I took was search-and-replace SP_Name to John.SP_Name for the whole project.
Thanks to all participants.

Best practice is always qualify schema for all object references. See this by Tibor Karaszi or this by Midnight DBA or just trust me or the MS SQL Server Best Practice Analyzer

But you can run this if you choose to ignore best practice:

ALTER SCHEMA dbo TRANSFER John.mySP_Name;


Using postgres_fdw to connect from one read replica to another I have two postgres database servers in AWS, A and B. These databases have different tables. Both databases also have their own read replica.

My goal is a simple solution for doing cross database joins from A replica to B replica. Querying from master is not an option, as this feature is meant for the analyst on my team, not for an application.

I set up postgres_fdw on A's master following the instructions by doing CREATE SERVER, CREATE USER MAPPING, and CREATE FOREIGN TABLEs, and I was able to easily query from A master to B replica. However, these server and foreign table definitions did NOT replicate to A replica. I am unable to run these create commands on the replica as it is read-only.

Is it possible to use postgres_fdw from A replica to B replica? If not, is there another solution for this problem?

You can create replicas using logical decoding.
Check pglogical https://github.com/2ndQuadrant/pglogical

When creating replica using logical decoding it's not read-only.Might help in your case.


space usage on sys.allocation_units and sp_spaceused It is a known fact that the DMVs dont hold accurate information regarding number of pages and count of rows.
However, when you have the stats updated, I can't see why they wouldn't.

I am working on a monitoring tool, want to know disk size of each index and data, etc.
Eventually I would like to find the right fill factor, and other things etc.

The space used by my function and the old sp_spaceused differs a little bit on the space usage, but not on record count.

Can you see if there is anything missing in my select?

this is the sp_spaceused (then I convert the numbers in MB):

sp_spaceused 'tblBOrderRelationship'
go

select 318008/1024.00 AS reserved,
140208/1024.00  AS data,
177048/1024.00 AS index_size,
752/1024.00    AS unused

But when I run my select, code below\picture below, I get slightly different figures.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT
    schema_name(t.schema_id) as SchemaName,
    t.NAME AS TableName,
    t.type_desc,
    t.is_ms_shipped,
    t.is_published,
    t.lob_data_space_id,
    t.filestream_data_space_id,
    t.is_replicated,
    t.has_replication_filter,
    t.is_merge_published,
    t.is_sync_tran_subscribed,
    --t.is_filetable,
    i.name as indexName,
    i.type_desc,
    i.is_unique,
    i.is_primary_key,
    i.is_unique_constraint,
    i.fill_factor,
    i.is_padded,

    sum(p.rows)               OVER (PARTITION BY t.OBJECT_ID,i.index_id)  as RowCounts,
    sum(a.total_pages)        OVER (PARTITION BY t.OBJECT_ID,i.index_id)  as TotalPages,
    sum(a.used_pages)         OVER (PARTITION BY t.OBJECT_ID,i.index_id)  as UsedPages,
    sum(a.data_pages)         OVER (PARTITION BY t.OBJECT_ID,i.index_id)  as DataPages,

    (sum(a.total_pages)       OVER (PARTITION BY t.OBJECT_ID,i.index_id)  * 8) / 1024 as TotalSpaceMB,
    (sum(a.used_pages)        OVER (PARTITION BY t.OBJECT_ID,i.index_id)  * 8) / 1024 as UsedSpaceMB,
    (sum(a.data_pages)        OVER (PARTITION BY t.OBJECT_ID,i.index_id)  * 8) / 1024 as DataSpaceMB
FROM
    sys.tables t
INNER JOIN
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
    sys.allocation_units a ON p.partition_id = a.container_id
WHERE
    t.NAME NOT LIKE 'dt%' AND
    i.OBJECT_ID > 255
AND T.NAME = 'tblBOrderRelationship'

the figures

the bigger picture, including the index names

Now doing some calculations to check the results:

--==================================
-- the figures from sp_spaceused
--==================================
select 318008/1024.00 AS reserved,
140208/1024.00  AS data,
177048/1024.00 AS index_size,
752/1024.00    AS unused

--==================================
-- the figures from my select
--==================================
select 137+61+56+54 AS reserved,
       137 AS data,
       61+56+54 AS index_size

It is not so far off, really, apart the fact I did not calculate the unused space!

What can I do to make this accurate?

AFTER CHANGES:

After I replaced 1024 by 1024.00 the results are much more accurate.
I noticed records have been inserted into the table in question, and obviously the stats are not so up to date, but still the results match (under 1 MB difference - which is all right for me)

The new result sets are:

--==================================
-- the figures from sp_spaceused
--==================================
select
318072 /1024.00 AS reserved,
140208 /1024.00 AS data,
177096 /1024.00 AS index_size,
768 /1024.00 AS unused
go

--==================================
-- the figures from my select
--==================================
select 137.7578125+61.7968750+56.4218750+54.6406250 as reserved,
       137.7578125 as data,
       61.7968750+56.4218750+54.6406250 as index_size

Even though you fixed the immediate rounding issue, the overall algorithm to get per-object / index stats is incorrect. It does not properly handle LOB and row-overflow data. It also excludes: Indexed Views, FullText indexes, XML indexes, and a few other cases. Hence, you might not be seeing all of your data.

The following is an adaptation of the code I posted to an answer on StackOverflow ( sp_spaceused - How to measure the size in GB in all the tables in SQL ) that handles all of the cases that sp_spaceused handles. That S.O. question was only concerned with per-object stats, not per index, so I have adjusted the code to handle things at the index level.

;WITH agg AS
(   -- Get info for Tables, Indexed Views, etc
    SELECT  ps.[object_id] AS [ObjectID],
            ps.index_id AS [IndexID],
            NULL AS [ParentIndexID],
            NULL AS [PassThroughIndexName],
            NULL AS [PassThroughIndexType],
            SUM(ps.in_row_data_page_count) AS [InRowDataPageCount],
            SUM(ps.used_page_count) AS [UsedPageCount],
            SUM(ps.reserved_page_count) AS [ReservedPageCount],
            SUM(ps.row_count) AS [RowCount],
            SUM(ps.lob_used_page_count + ps.row_overflow_used_page_count)
                    AS [LobAndRowOverflowUsedPageCount]
    FROM    sys.dm_db_partition_stats ps
    GROUP BY    ps.[object_id],
                ps.[index_id]
    UNION ALL
    -- Get info for FullText indexes, XML indexes, Spatial indexes, etc
    SELECT  sit.[parent_id] AS [ObjectID],
            sit.[object_id] AS [IndexID],
            sit.[parent_minor_id] AS [ParentIndexID],
            sit.[name] AS [PassThroughIndexName],
            sit.[internal_type_desc] AS [PassThroughIndexType],
            0 AS [InRowDataPageCount],
            SUM(ps.used_page_count) AS [UsedPageCount],
            SUM(ps.reserved_page_count) AS [ReservedPageCount],
            0 AS [RowCount],
            0 AS [LobAndRowOverflowUsedPageCount]
    FROM    sys.dm_db_partition_stats ps
    INNER JOIN  sys.internal_tables sit
            ON  sit.[object_id] = ps.[object_id]
    WHERE   sit.internal_type IN
               (202, 204, 207, 211, 212, 213, 214, 215, 216, 221, 222, 236)
    GROUP BY    sit.[parent_id],
                sit.[object_id],
                sit.[parent_minor_id],
                sit.[name],
                sit.[internal_type_desc]
), spaceused AS
(
SELECT  agg.[ObjectID],
        agg.[IndexID],
        agg.[ParentIndexID],
        agg.[PassThroughIndexName],
        agg.[PassThroughIndexType],
        OBJECT_SCHEMA_NAME(agg.[ObjectID]) AS [SchemaName],
        OBJECT_NAME(agg.[ObjectID]) AS [TableName],
        SUM(CASE
                WHEN (agg.IndexID < 2) THEN agg.[RowCount]
                ELSE 0
            END) AS [Rows],
        SUM(agg.ReservedPageCount) * 8 AS [ReservedKB],
        SUM(agg.LobAndRowOverflowUsedPageCount +
            CASE
                WHEN (agg.IndexID < 2) THEN (agg.InRowDataPageCount)
                ELSE 0
            END) * 8 AS [DataKB],
        SUM(agg.UsedPageCount - agg.LobAndRowOverflowUsedPageCount -
            CASE
                WHEN (agg.IndexID < 2) THEN agg.InRowDataPageCount
                ELSE 0
            END) * 8 AS [IndexKB],
        SUM(agg.ReservedPageCount - agg.UsedPageCount) * 8 AS [UnusedKB],
        SUM(agg.UsedPageCount) * 8 AS [UsedKB]
FROM    agg
GROUP BY    agg.[ObjectID],
            agg.[IndexID],
            agg.[ParentIndexID],
            agg.[PassThroughIndexName],
            agg.[PassThroughIndexType],
            OBJECT_SCHEMA_NAME(agg.[ObjectID]),
            OBJECT_NAME(agg.[ObjectID])
)
SELECT sp.SchemaName,
       sp.TableName,
       sp.IndexID,
       CASE
         WHEN (sp.IndexID > 0) THEN COALESCE(si.[name], sp.[PassThroughIndexName])
         ELSE N'<Heap>'
       END AS [IndexName],
       sp.[PassThroughIndexName] AS [InternalTableName],
       sp.[Rows],
       sp.ReservedKB,
       (sp.ReservedKB / 1024.0 / 1024.0) AS [ReservedGB],
       sp.DataKB,
       (sp.DataKB / 1024.0 / 1024.0) AS [DataGB],
       sp.IndexKB,
       (sp.IndexKB / 1024.0 / 1024.0) AS [IndexGB],
       sp.UsedKB AS [UsedKB],
       (sp.UsedKB / 1024.0 / 1024.0) AS [UsedGB],
       sp.UnusedKB,
       (sp.UnusedKB / 1024.0 / 1024.0) AS [UnusedGB],
       so.[type_desc] AS [ObjectType],
       COALESCE(si.type_desc, sp.[PassThroughIndexType]) AS [IndexPrimaryType],
       sp.[PassThroughIndexType] AS [IndexSecondaryType],
       SCHEMA_ID(sp.[SchemaName]) AS [SchemaID],
       sp.ObjectID
       --,sp.ParentIndexID
FROM   spaceused sp
INNER JOIN sys.all_objects so -- in case "WHERE so.is_ms_shipped = 0" is removed
        ON so.[object_id] = sp.ObjectID
LEFT JOIN  sys.indexes si
       ON  si.[object_id] = sp.ObjectID
      AND  (si.[index_id] = sp.IndexID
         OR si.[index_id] = sp.[ParentIndexID])
WHERE so.is_ms_shipped = 0
--so.[name] LIKE N''  -- optional name filter
--ORDER BY ????


How to setup Linked Server connect with different Domain Account with Windows Authentication we have two SQL Server 2017 "ServerA" and "ServerB" on Windows Server 2016 in the same domain.
Both SQL Server have a Login defined "mydomain\dbaccount".
On ServerA we have a Login defined "mydomain\testaccount" which can access the Database.
We want to connect with the "mydomain\testaccount" to ServerB via Linked Server. The linked server should connect with the "mydomain\dbaccount" via Windows Authentication.
We cannot provide password in linked server of "mydomain\dbaccount" as we want to connect via Windows Authentication. And the Login on ServerB is defined as Windows Authentication.
We tried to setup the Linked Server with the local login as "mydomain\testaccount" and impersonate=true and we are getting the following error:

The OLE DB provider "SQLNCLI11" for linked server "ServerB" reported
an error. Authentication failed. Cannot initialize the data source
object of OLE DB provider "SQLNCLI11" for linked server "ServerB". OLE
DB provider "SQLNCLI11" for linked server "ServerB" returned message
"Invalid authorization specification". (Microsoft SQL Server, Error:
7399)

We tried to setup the Linked Server with the local login as "mydomain\testaccount" and impersonate=false and remote user as "mydomain\dbaccount" and we are getting the following error:

Login failed for user 'mydomain\dbaccount'. (Microsoft SQL Server,
Error: 18456)

We tried to setup the Linked Server with the local login as "mydomain\testaccount" and impersonate=true and without remote user and in options we checked "Be made using this security context" and defined remote login as "mydomain\dbaccount" without password and we are getting the following error:

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. (Microsoft SQL
Server, Error: 18456)

How can we setup Linked Server with remote Windows Authentication?

No can do, I'm afraid. For a local Win login, you can point to a remote SQL login (including the password), or you can ask for impersonation/delegation meaning that the same win account/login will be used on the other SQL Server.
You can't point to a different win account/login on the remote server,


How archive command works in postgres Just wondering how does this postgres wal archive command works in replication:

rsync -z -a --contimeout=120 --partial-dir=.rsync-partial %p db-slave.sr1.net::primary-wal-archive-share/%f

what does this %p and %f mean here?

from docs:

  The local shell command to execute to archive a completed WAL file
  segment. Any %p in the string is replaced by the path name of the file
  to archive, and any %f is replaced by only the file name. (The path
  name is relative to the working directory of the server, i.e., the
  cluster's data directory.) Use %% to embed an actual % character in
  the command. It is important for the command to return a zero exit
  status only if it succeeds

(emphasis mine)

Also take a look at setting WAL archiving


Convert rows into columns and group some colums I saw a lot of example on how to convert rows into columns and I was able to accomplish that.Here is the table that have:

Userid     action      date            TRACKING         zip        Subject
cbg10        O        1/2/2020           Amz           30105       $1 Sales journey
cbg10        O        1/2/2020           Amz           30105       $1 Sales journey
cbg10        C        1/2/2020           Amz           30105       $1 Sales journey
cbg10        P        1/2/2020           Amz           30105       $1 Sales journey
kel64        O        1/2/2020           Amz           45685       $1 Sales journey
kel64        O        1/2/2020           Amz           45685       $1 Sales journey
kel64        O        1/2/2020           Amz           45685       $1 Sales journey
kel64        C        1/2/2020           Amz           45685       $1 Sales journey
kel64        C        1/2/2020           Amz           45685       $1 Sales journey
kel64        p        1/2/2020           Amz           45685       $1 Sales journey
cbg10        O        4/18/2020          TG            30105       Summer arrives
cbg10        P        4/18/2020          TG            30105       Summer arrives
cbg10        C        4/18/2020          TG            30105       Summer arrives

This what I hope the table would look like: As you can see some open open the email 2 times. Instead of having 2 rows like in the first table this one add the actions taken by the person by subject

Userid     date      O(Open)  C(Click)  P(Purchase)  TRACKING  zip     Subject
cbg10    1/2/2020     2         1        1            Amz     30105    $1 Sales journey
kel64    1/2/2020     3         2        1            Amz     45685    $1 Sales journey
cbg10    4/2/2020     1         1        1            Amz     30105    Summer arrives

This is the code that I have, but it is not giving me what I need
select UserId,

max(case when Aciton = 'O' then 1 else 0 end) as [O(Open)],
max(case when Aciton = 'P' then 1 else 0 end) as [P(Paid)],
max(case when Aciton = 'C' then 1 else 0 end) as [C(Cash)],
 [Date],
 Tracking,
 Zip
 from myTable
 GROUP BY UserId, [Date], [Tracking], [Zip]
 ORDER BY UserId

Thankfully SQL Server has a built in function called a PIVOT that should do exactly what you are looking for.  There is a great article at SQLServerTutorial.Net which has some great examples on how to use this.

I think that this query should give you what you are looking for.  I built a table variable called @Table for the purpose of my testing.:

SELECT UserID,
[Date],
[O] AS 'O(Open)', --The value in the [Action] Column called out below, becomes the column name up here
[C] AS 'C(Click)', --The value in the [Action] Column called out below, becomes the column name up here
[P] AS 'P(Purchase)', --The value in the [Action] Column called out below, becomes the column name up here
Tracking,
Zip,
[Subject]
FROM
(
    SELECT UserID,
    [Action],
    [Date],
    Tracking,
    Zip,
    [Subject]
    FROM @Table
) T
PIVOT
(
    COUNT([Action])
    FOR [Action] IN([O], [C], [P]) --Call out the values in the Action Column
) AS Pivot_Table

For Me It Produced a Table Like Below:

UserID  Date        O(Open) C(Click)    P(Purchase) Tracking    Zip     Subject
cbg10   2020-01-02  2       1           1           Amz         30105   $1 Sales journey
cbg10   2020-04-18  1       1           1           TG          30105   Summer arrives
kel64   2020-01-02  3       2           1           Amz         45685   $1 Sales journey

I can break this down in a little more details if you need me to, since there are several nuances in what you asked for, grouping by multiple columns, mostly.

Hope this helps!


Cannot open backup device Error I have a SQL Server Agent job that backs up a database on a remote server to a network drive. Linked Server, I believe is set up correctly but I get the following error:

  Msg 3013, Sev 16, State 1, Line 35
  BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000]

  Msg 3201, Sev 16, State 1, Line 35
  Cannot open backup device. Operating system error 1326
  (failed to retrieve text for this error. Reason: 1815). [SQLSTATE 42000]

I have used SQL Server Configuration Manager to change the Log On to "This Account", selected the domain account and supplied the right password.

Then, on the Linked Server Properties, I mapped the Local Login to the domain account and ticked "Impersonate" The security on the network drive folder is set to "everyone" and full permission is given. And yet still getting this error and cannot backup the database as a result.

What can I do?

So in Configuration Manager, you configured the SQL Agent service account, correct?  That is the account that will execute SQL Agent jobs, by default.

This query will verify your settings:

SELECT servicename, service_account
FROM   sys.dm_server_services
go

Do you get the error when executing the job via a schedule, when executing manually, or both?

If you are using T-SQL commands to perform the backup, what happens when you paste the commands into a query window and manually execute?  Can you reproduce the error there as well?


Is a MERGE with OUTPUT better practice than a conditional INSERT and SELECT? We often encounter the "If not exists, insert" situation. Dan Guzman's blog has an excellent investigation of how to make this process threadsafe.

I have a basic table that simply catalogs a string to an integer from a SEQUENCE. In a stored procedure I need to either get the integer key for the value if it exists, or INSERT it and then get the resulting value. There is a uniqueness constraint on the dbo.NameLookup.ItemName column so data integrity isn't at risk but I don't want to encounter the exceptions.

It's not an IDENTITY so I can't get SCOPE_IDENTITY and the value could be NULL in certain cases.

In my situation I only have to deal with INSERT safety on the table so I'm trying to decide if it's better practice to use MERGE like this:

SET NOCOUNT, XACT_ABORT ON;

DECLARE @vValueId INT
DECLARE @inserted AS TABLE (Id INT NOT NULL)

MERGE
    dbo.NameLookup WITH (HOLDLOCK) AS f
USING
    (SELECT @vName AS val WHERE @vName IS NOT NULL AND LEN(@vName) > 0) AS new_item
        ON f.ItemName= new_item.val
WHEN MATCHED THEN
    UPDATE SET @vValueId = f.Id
WHEN NOT MATCHED BY TARGET THEN
    INSERT
      (ItemName)
    VALUES
      (@vName)
OUTPUT inserted.Id AS Id INTO @inserted;
SELECT @vValueId = s.Id FROM @inserted AS s

I could do this witout using MERGE with just a conditional INSERT followed by a SELECT
I think that this second approach is clearer to the reader, but I'm not convinced it's "better" practice

SET NOCOUNT, XACT_ABORT ON;

INSERT INTO
    dbo.NameLookup (ItemName)
SELECT
    @vName
WHERE
    NOT EXISTS (SELECT * FROM dbo.NameLookup AS t WHERE @vName IS NOT NULL AND LEN(@vName) > 0 AND t.ItemName = @vName)

DECLARE @vValueId int;
SELECT @vValueId = i.Id FROM dbo.NameLookup AS i WHERE i.ItemName = @vName

Or perhaps there is another better way that I haven't considered

I did search and reference other questions. This one: https://stackoverflow.com/questions/5288283/sql-server-insert-if-not-exists-best-practice is the most appropriate I could find but doesn't seem very applicable to my use case. Other questions to the IF NOT EXISTS() THEN approach which I don't think is acceptable.

Because you are using a Sequence, you can use the same NEXT VALUE FOR function -- that you already have in a Default Constraint on the Id Primary Key field -- to generate a new Id value ahead of time. Generating the value first means that you don't need to worry about not having SCOPE_IDENTITY, which then means that you don't need either the OUTPUT clause or doing an additional SELECT to get the new value; you will have the value before you do the INSERT, and you don't even need to mess with SET IDENTITY INSERT ON / OFF :-)

So that takes care of part of the overall situation. The other part is handling the concurrency issue of two processes, at the exact same time, not finding an existing row for the exact same string, and proceeding with the INSERT. The concern is about avoiding the Unique Constraint violation that would occur.

One way to handle these types of concurrency issues is to force this particular operation to be single threaded. The way to do that is by using application locks (which work across sessions). While effective, they can be a bit heavy-handed for a situation like this where the frequency of collisions is probably fairly low.

The other way to deal with the collisions is to accept that they will sometimes occur and handle them rather than try to avoid them. Using the TRY...CATCH construct, you can effectively trap a specific error (in this case: "unique constraint violation", Msg 2601) and re-execute the SELECT to get the Id value since we know that it now exists due to being in the CATCH block with that particular error. Other errors can be handled in the typical RAISERROR / RETURN or THROW manner.

Test Setup: Sequence, Table, and Unique Index

USE [tempdb];

CREATE SEQUENCE dbo.MagicNumber
  AS INT
  START WITH 1
  INCREMENT BY 1;

CREATE TABLE dbo.NameLookup
(
  [Id] INT NOT NULL
         CONSTRAINT [PK_NameLookup] PRIMARY KEY CLUSTERED
        CONSTRAINT [DF_NameLookup_Id] DEFAULT (NEXT VALUE FOR dbo.MagicNumber),
  [ItemName] NVARCHAR(50) NOT NULL
);

CREATE UNIQUE NONCLUSTERED INDEX [UIX_NameLookup_ItemName]
  ON dbo.NameLookup ([ItemName]);
GO

Test Setup: Stored Procedure

CREATE PROCEDURE dbo.GetOrInsertName
(
  @SomeName NVARCHAR(50),
  @ID INT OUTPUT,
  @TestRaceCondition BIT = 0
)
AS
SET NOCOUNT ON;

BEGIN TRY
  SELECT @ID = nl.[Id]
  FROM   dbo.NameLookup nl
  WHERE  nl.[ItemName] = @SomeName
  AND    @TestRaceCondition = 0;

  IF (@ID IS NULL)
  BEGIN
    SET @ID = NEXT VALUE FOR dbo.MagicNumber;

    INSERT INTO dbo.NameLookup ([Id], [ItemName])
    VALUES (@ID, @SomeName);
  END;
END TRY
BEGIN CATCH
  IF (ERROR_NUMBER() = 2601) -- "Cannot insert duplicate key row in object"
  BEGIN
    SELECT @ID = nl.[Id]
    FROM   dbo.NameLookup nl
    WHERE  nl.[ItemName] = @SomeName;
  END;
  ELSE
  BEGIN
    ;THROW; -- SQL Server 2012 or newer
    /*
    DECLARE @ErrorNumber INT = ERROR_NUMBER(),
            @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();

    RAISERROR(N'Msg %d: %s', 16, 1, @ErrorNumber, @ErrorMessage);
    RETURN;
    */
  END;

END CATCH;
GO

The Test

DECLARE @ItemID INT;
EXEC dbo.GetOrInsertName
  @SomeName = N'test1',
  @ID = @ItemID OUTPUT;
SELECT @ItemID AS [ItemID];
GO

DECLARE @ItemID INT;
EXEC dbo.GetOrInsertName
  @SomeName = N'test1',
  @ID = @ItemID OUTPUT,
  @TestRaceCondition = 1;
SELECT @ItemID AS [ItemID];
GO

Question from O.P.

  Why is this better than the MERGE? Won't I get the same functionality without the TRY by using the WHERE NOT EXISTS clause?

MERGE has various "issues" (several references are linked in @SqlZim's answer so no need to duplicate that info here). And, there is no additional locking in this approach (less contention), so it should be better on concurrency. In this approach you'll never get a Unique Constraint violation, all without any HOLDLOCK, etc. It's pretty much guaranteed to work.

The reasoning behind this approach is:

If you have enough executions of this procedure such that you need to worry about collisions, then you don't want to:

take any more steps than are necessary
hold locks on any resources for longer than necessary

Since collisions can only happen upon new entries (new entries submitted at the exact same time), the frequency of falling into the CATCH block in the first place will be pretty low. It makes more sense to optimize the code that will run 99% of the time instead of the code that will run 1% of the time (unless there is no cost to optimizing both, but that is not the case here).

Comment from @SqlZim's answer (emphasis added)

  I personally prefer to try and tailor a solution to avoid doing that when possible. In this case, I don't feel that using the locks from serializable is a heavy handed approach, and I would be confident it would handle high concurrency well.

I would agree with this first sentence if it were amended to state "and _when prudent". Just because something is technically possible does not mean that the situation (i.e. intended use-case) would be benefited by it.

The issue I see with this approach is that it locks more than what is being suggested. It is important to re-read the quoted documentation on "serializable", specifically the following (emphasis added):

  Other transactions cannot insert new rows with key values that would fall in the range of keys read by any statements in the current transaction until the current transaction completes.

Now, here is the comment in the example code:

SELECT [Id]
FROM   dbo.NameLookup WITH (SERIALIZABLE) /* hold that key range for @vName */

The operative word there is "range". The lock being taken is not just on the value in @vName, but more accurately a range starting at the location where this new value should go (i.e. between the existing key values on either side of where the new value fits), but not the value itself. Meaning, other processes will be blocked from inserting new values, depending on the value(s) currently being looked up. If the lookup is being done at the top of the range, then inserting anything that could occupy that same position will be blocked. For example, if values "a", "b", and "d" exist, then if one process is doing the SELECT on "f", then it will not be possible to insert values "g" or even "e" (since any one of those will come immediately after "d"). But, inserting a value of "c" will be possible since it wouldn't be placed in the "reserved" range.

The following example should illustrate this behavior:

(In query tab (i.e. Session) #1)

INSERT INTO dbo.NameLookup ([ItemName]) VALUES (N'test5');

BEGIN TRAN;

SELECT [Id]
FROM   dbo.NameLookup WITH (SERIALIZABLE) /* hold that key range for @vName */
WHERE  ItemName = N'test8';

--ROLLBACK;

(In query tab (i.e. Session) #2)

EXEC dbo.NameLookup_getset_byName @vName = N'test4';
-- works just fine

EXEC dbo.NameLookup_getset_byName @vName = N'test9';
-- hangs until you either hit "cancel" in this query tab,
-- OR issue a COMMIT or ROLLBACK in query tab #1

EXEC dbo.NameLookup_getset_byName @vName = N'test7';
-- hangs until you either hit "cancel" in this query tab,
-- OR issue a COMMIT or ROLLBACK in query tab #1

EXEC dbo.NameLookup_getset_byName @vName = N's';
-- works just fine

EXEC dbo.NameLookup_getset_byName @vName = N'u';
-- hangs until you either hit "cancel" in this query tab,
-- OR issue a COMMIT or ROLLBACK in query tab #1

Likewise, if value "C" exists, and value "A" is being selected (and hence locked), then you can insert a value of "D", but not a value of "B":

(In query tab (i.e. Session) #1)

INSERT INTO dbo.NameLookup ([ItemName]) VALUES (N'testC');

BEGIN TRAN

SELECT [Id]
FROM   dbo.NameLookup WITH (SERIALIZABLE) /* hold that key range for @vName */
WHERE  ItemName = N'testA';

--ROLLBACK;

(In query tab (i.e. Session) #2)

EXEC dbo.NameLookup_getset_byName @vName = N'testD';
-- works just fine

EXEC dbo.NameLookup_getset_byName @vName = N'testB';
-- hangs until you either hit "cancel" in this query tab,
-- OR issue a COMMIT or ROLLBACK in query tab #1

To be fair, in my suggested approach, when there is an exception, there will be 4 entries in the Transaction Log that won't be happening in this "serializable transaction" approach. BUT, as I said above, if the exception happens 1% (or even 5%) of the time, that is far less impacting than the far more likely case of the initial SELECT temporarily blocking INSERT operations.

Another, albeit minor, issue with this "serializable transaction + OUTPUT clause" approach is that the OUTPUT clause (in its present usage) sends the data back as a result set. A result set requires more overhead (probably on both sides: in SQL Server to manage the internal cursor, and in the app layer to manage the DataReader object) than a simple OUTPUT parameter. Given that we are only dealing with a single scalar value, and that the assumption is a high frequency of executions, that extra overhead of the result set probably adds up.

While the OUTPUT clause could be used in such a way as to return an OUTPUT parameter, that would require additional steps to create a temporary table or table variable, and then to select the value out of that temp table / table variable into the OUTPUT parameter.

Further Clarification: Response to @SqlZim's Response (updated answer) to my Response to @SqlZim's Response (in the original answer) to my statement regarding concurrency and performance ;-)

Sorry if this part is a wee-bit long, but at this point we are just down to the nuances of the two approaches.

  I believe the way the information is presented could lead to false assumptions about the amount of locking one could expect to encounter when using serializable in the scenario as presented in the original question.

Yes, I will admit that I am biased, though to be fair:

It is impossible for a human to not be biased, at least to some small degree, and I do try to keep it at a minimum,
The example given was simplistic, but that was for illustrative purposes to convey the behavior without over-complicating it. Implying excessive frequency was not intended, though I do understand that I also didn't explicitly state otherwise and it could be read as to imply a larger problem than actually exists. I will try to clarify that below.
I did also include an example of locking a range between two existing keys (the second set of "Query tab 1" and "Query tab 2" blocks).
I did find (and volunteer) the "hidden cost" of my approach, that being the four extra Tran Log entries each time the INSERT fails due to a Unique Constraint violation. I have not seen that mentioned in any of the other answers / posts.

Regarding @gbn's "JFDI" approach, Michael J. Swart's "Ugly Pragmatism For The Win" post, and Aaron Bertrand's comment on Michael's post (regarding his tests showing what scenarios have decreased performance), and your comment on your "adapation of Michael J. Stewart's adaptation of @gbn's Try Catch JFDI procedure" stating:

  If you are inserting new values more often than selecting existing values, this may be more performant than @srutzky's version. Otherwise I would prefer @srutzky's version over this one.

With respect to that gbn / Michael / Aaron discussion related to the "JFDI" approach, it would be incorrect to equate my suggestion to gbn's "JFDI" approach. Due to the nature of the "Get or Insert" operation, there is an explicit need to do the SELECT to get the ID value for existing records. This SELECT acts as the IF EXISTS check, which makes this approach more so equate to the "CheckTryCatch" variation of Aaron's tests. Michael's re-written code (and your final adaptation of Michael's adaptation) also includes a WHERE NOT EXISTS to do that same check first. Hence, my suggestion (along with Michael's final code and your adaptation of his final code) won't actually hit the CATCH block all that often. It could only be situations where two sessions, given the same non-existent ItemName, and running the INSERT...SELECT at the exact same moment such that both sessions receive a "true" for the WHERE NOT EXISTS at the exact same moment and thus both attempt to do the INSERT at the exact same moment. That very specific scenario happens much less often than either selecting an existing ItemName or inserting a new ItemName when no other process is attempting to do so at the exact same moment.

WITH ALL OF THE ABOVE IN MIND: Why do I prefer my approach?

First, let's look at what locking takes place in the "serializable" approach. As mentioned above, the "range" that gets locked depends on the existing key values on either side of where the new key value would fit. The beginning or ending of the range could also be the beginning or ending of the index, respectively, if there is no existing key value in that direction. Assume we have the following index and keys (^ represents the beginning of the index while $ represents the end of it):

Range #:    |--- 1 ---|--- 2 ---|--- 3 ---|--- 4 ---|
Key Value:  ^         C         F         J         $

If session 55 attempts to insert a key value of:

A, then range # 1 (from ^ to C) is locked: session 56 cannot insert a value of B, even if unique and valid (yet). But session 56 can insert values of D, G, and M.
D, then range # 2 (from C to F) is locked: session 56 cannot insert a value of E (yet). But session 56 can insert values of A, G, and M.
M, then range # 4 (from J to $) is locked: session 56 cannot insert a value of X (yet). But session 56 can insert values of A, D, and G.

As more key values are added, the ranges between key values becomes narrower, hence reducing the probability / frequency of multiple values being inserted at the same time fighting over the same range. Admittedly, this is not a major problem, and fortunately it appears to be a problem that actually decreases over time.

The issue with my approach was described above: it only happens when two sessions attempt to insert the same key value at the same time. In this respect it comes down to what has the higher probability of happening: two different, yet close, key values are attempted at the same time, or the same key value is attempted at the same time? I suppose the answer lies in the structure of the app doing the inserts, but generally speaking I would assume it to be more likely that two different values that just happen to share the same range are being inserted. But the only way to really know would be to test both on the O.P.s system.

Next, let's consider two scenarios and how each approach handles them:

All requests being for unique key values:

In this case, the CATCH block in my suggestion is never entered, hence no "issue" (i.e. 4 tran log entries and the time it takes to do that). But, in the "serializable" approach, even with all inserts being unique, there will always be some potential for blocking other inserts in the same range (albeit not for very long).
High-frequency of requests for same key value at the same time:

In this case -- a very low degree of uniqueness in terms of incoming requests for non-existent key values -- the CATCH block in my suggestion will be entered regularly. The effect of this will be that each failed insert will need to auto-rollback and write the 4 entries to the Transaction Log, which is a slight performance hit each time. But the overall operation should never fail (at least not due to this).

(There was an issue with the previous version of the "updated" approach that allowed it to suffer from deadlocks. An updlock hint was added to address this and it no longer gets deadlocks.) BUT, in the "serializable" approach (even the updated, optimized version), the operation will deadlock. Why? Because the serializable behavior only prevents INSERT operations in the range that has been read and hence locked; it doesn't prevent SELECT operations on that range.

The serializable approach, in this case, would seem to have no additional overhead, and might perform slightly better than what I am suggesting.

As with many / most discussions regarding performance, due to there being so many factors that can affect the outcome, the only way to really have a sense of how something will perform is to try it out in the target environment where it will run. At that point it won't be a matter of opinion :).


After upgrade of SQL Server 2000 database to SQL Server 2008 the view is not sorted on the group by column I have two tables

Location Master ([LocatioNo],[LocationName],[Description])
Location Data ([LocationNo],[LocationCode],[DDate],[BillNo],[Model],[Quantity],[SNo])

(source: staticflickr.com)
](http://www.flickr.com/photos/77377790@N08/6782618850 "Click to view on Flickr")

and a view involving the 2 tables which was created in SQL Server 2000

CREATE VIEW [dbo].[QueryLocation]
AS
  SELECT TOP (100) PERCENT
       dbo.LocationData.LocationNo, dbo.LocationData.LocationCode,
       dbo.LocationData.DDate, dbo.LocationData.BillNo,
       dbo.LocationData.Model,
       SUM(dbo.LocationData.Quantity) AS Quantity,
       dbo.LocationMaster.LocationName
  FROM
       dbo.LocationData
  INNER JOIN
       dbo.LocationMaster ON dbo.LocationData.LocationNo = dbo.LocationMaster.LocatioNo
  GROUP BY
       dbo.LocationMaster.LocationName, dbo.LocationData.LocationNo,
       dbo.LocationData.LocationCode, dbo.LocationData.DDate, dbo.LocationData.BillNo,
       dbo.LocationData.Model ORDER BY dbo.LocationData.BillNo

When the following query is execute in SQL Server 2000 the result in the grid was sorted on billno column.

SELECT *  FROM QueryLocation

(source: staticflickr.com)
](http://www.flickr.com/photos/77377790@N08/6782618858 "Click to view on Flickr")

After upgrade to SQL Server 2008 the same query when executed is not sorted on bill no field

Advice why this is not sorted on the group by column

Thanks in advance

ORDER BY within a view was only ever a broken "feature" (albeit one that enterprise manager seemed to encourage).

Views are meant, for the most part, to be indistinguishable from Tables. And Tables have no inherent order.

This means that the only place that an ORDER BY is guaranteed to affect the order in which results are returned is in the outermost SELECT - i.e. the one that references this view (assuming that that SELECT, in turn, is not within another view or UDF)

Within a view, UDF, etc, ORDER BY was only ever meant to work for its other/overloaded purpose - to make TOP make sense - i.e. without an ORDER BY, asking for the TOP 10 would be poorly defined, because, again, tables have no inherent order.

Unfortunately, there is a common mis-perception that this works because the Graphical view designers in Enterprise Manager and Management Studio actually encourage it - there are "sort" features included there, and if you use them, the tool inserts TOP 100 PERCENT and ORDER BY - despite this having been something that doesn't work since at least 2005.

Having just re-read the question - it was relying on the order of a GROUP BY? That's compounding one issue with another. There's no guarantee that the output from a statement involving a GROUP BY will have any particular order.


SQL Server stored procedure running in infinite loop I am running this stored procedure that updates a column. It runs in an infinite loop. When I run the internal query separately, it completes just fine. When I cancel the execution of the stored procedure after it has ran for a while, it shows several 5000 row batches affected and asks if I want to commit. Please help.

CREATE PROCEDURE [dbo].[UPDATE_INDATE_PARENT_EMAILS_AND_EDOCS_revised]
    @FieldName VARCHAR(16),
    @StartDate varchar(15),
    @EndDate varchar(15),
    @CustodianIDs varchar(500)
AS
    PRINT ('NUMBER OF CUSTODIANS:')

    DECLARE @XML AS XML
    DECLARE @Delimiter AS CHAR(1) =','

    SET @XML = CAST(('<X>'+REPLACE(@CustodianIDs,@Delimiter ,'</X><X>')+'</X>') AS XML)

    DECLARE @cust_tbl TABLE (custodianid INT)

    INSERT INTO @cust_tbl
        SELECT N.value('.', 'INT') AS custodianid
        FROM @XML.nodes('X') AS T(N)

Declare @cust_id    varchar(20)
Declare @update_sql varchar(max)

declare cust_curs  cursor for
SELECT * FROM @cust_tbl
ORDER BY 1
open cust_curs
fetch next from cust_curs into @cust_id
while @@FETCH_STATUS = 0

BEGIN -- custodian loop

set @update_sql = '

SET NOCOUNT ON;

Select ID into #DOCS from tbldoc (nolock) where custodianid = ' + @CUST_ID + '

SET NOCOUNT OFF;

WHILE (1=1)
BEGIN

    BEGIN TRANSACTION

update TOP(5000) tbldoc set ' + @fieldname + ' = 1
from dbo.tbldoc d (nolock)
join #docs d2 on d2.id = d.id
where (
(d.AttachPID in
(select d3.AttachPID from tblDoc d3 (nolock)
join #docs d4 on d4.id = d3.id
where isnull(d3.Exclude,0) = 0 and d3.AttachPID > 0 and d3.AttachLvl = 0 and
(d3.datesent between ''' + @StartDate + ''' and ''' + @EndDate + ''' or
 d3.datelastmod between ''' + @StartDate + ''' and ''' + @EndDate + ''' or
 d3.datecreated between ''' + @StartDate + ''' and ''' + @EndDate + ''')))
or
(isnull(d.Exclude,0) = 0  and d.AttachPID = 0 and d.AttachLvl = 0 and
 (d.datesent between ''' + @StartDate + ''' and ''' + @EndDate + ''' or
  d.datelastmod between ''' + @StartDate + ''' and ''' + @EndDate + ''' or
  d.datecreated between ''' + @StartDate + ''' and ''' + @EndDate + ''')))

       IF @@ROWCOUNT = 0
            BEGIN
            COMMIT TRANSACTION
            BREAK
        END
    COMMIT TRANSACTION
END
'

PRINT ('UPDATED INDATE FOR CUSTODIANID ' + @CUST_ID)

exec (@update_sql)

IF OBJECT_ID('tempdb..#DOCS', 'U') IS NOT NULL
  DROP TABLE #DOCS;

  FETCH NEXT FROM cust_curs  INTO @cust_id
END

CLOSE cust_curs
DEALLOCATE cust_curs

Add AND COALESCE(' + @fieldname + ',0) <> 1 to your WHERE clause. You're updating the same 5,000 rows over and over and over and over...


MySQL - Creating an index in table DDL statement or separately I'm making the leap from SQL Server to MySQL and I'm trying to understand what the difference would be creating an index as part of the DDL table create statement vs creating a separate DDL statement for the each index you want to create.

The MySQL documentation on create index states

  Normally, you create all indexes on a table at the time the table itself is created with CREATE TABLE. See Section 13.1.18, “CREATE TABLE Syntax”. This guideline is especially important for InnoDB tables, where the primary key determines the physical layout of rows in the data file. CREATE INDEX enables you to add indexes to existing tables.

Why is it important for InnoDB table to have the index created as part of the create table DDL?  (Update with more details below)

What is the potential issues with an index being created after the table is created?

Update

My question was towards the statement on the MySQL's site about creating the index during the table creation vs creating it after creating the table but before filling it with data.  The statement on MySQL's site makes it seem like there is a benefit, performance or otherwise, when using InnoDB tables that declaring the indexes within the DDL statement for the table is best practice but they don't explain why.

Referencing how I would approach this in SQL Server, partly because of DDL limitations and partly because I like the explicit nature, is that I would make my DDL statement and assign the primary key along with any foreign key constraints.  After that I would assign my clustered index, if it it wasn't the primary key, and secondary indexes (unique and non unique).  But in either approach if I was to create my clustered index with the table DDL or separate the end result would be the same.  Their statement is making it sound like the RDMS will handle the table creation differently depending on the approach even though the result in the end is the same, a primary key and a clustered index.

In InnoDB, the PRIMARY KEY is, by definition, clusered and BTree-organized.  If you populate a table before specifying the desired PK, a hidden PK is generated.  This hidden PK must be removed and replaced by the desired PK and the table must be re-sorted according to the new PK.  No "harm" is done, but extra time is taken and the BTree may become more fragmented.

Adding secondary keys (any key other than the PK) separately from the CREATE TABLE may or may not be inefficient.  But, again, no "harm" is done.  The inefficiency depends on the version, the nature of the index, etc.

FOREIGN KEYs are another matter -- they must be applied in the "right order".  This can be achieved by having the CREATE TABLEs in the "right order" or by separately adding the FKs in the "right order".

OK, the documentation in this area is wimpy.  You could report it at bugs.mysql.com .

I prefer to have (and see) everything inside the CREATE TABLE DDL.


How to speed up SSIS data load from Oracle database? I need to load the data from a Oracle server in another country by using SSIS package. I've configured Oracle drivers, environment variables and connections in SSDT already. I'm loading about 200 MB of data (one table) and it takes about 20 minutes! to load the data. The link in the remote location is 15Mbps with 86ms of Latency. Is there a way to configure the Oracle or SSIS package to speed up the load ?

You should get some significant performance improvement using the FetchSize connection string property. The Oracle documentation for the max value is here.

  FetchSize - specifies the number of rows the provider will fetch at a
  time (fetch array). It must be set appropriately depending on the data
  size and the response time of the network. If the value is set too
  high, this could result in more wait time during the execution of the
  query. If the value is set too low, this could result in many more
  round trips to the database. Valid values are 1 to 429,496,296. The
  default is 100.

But mainly I would recommend upgrading to SSIS Enterprise edition and using this connector. Here is the one for SSIS 2014 but you can search for other versions easily. The performance of that connector is much improved.


Encryption in SQL Server 2012/2014 We are hosting sharepoint DBs with AlwaysOn AGs and FCI. My boss asked me what the options for encryption are on the content database, it's backup files and at the column level?

I have read about TDE. Could this harm performance?

Is windows level encryption possible?

Are there any other options?

My boss ask me for possibilities to do the encryption on content DB
  level, it backup. or on column level?

This is something you and You're boss need to sit and discuss on as what could be pros and cons of using TDE or Cell level encryption:

However Few points to note:

TDE does not offer the granularity of protection that cell-level encryption offers, it does provide a level of protection that cannot be achieved through cell-level encryption
TDE does not require any schema modifications and since Since the physical data files and not the data itself are encrypted, the primary keys and indexes on the data are unaffected, and so optimal query execution can be maintained.
In MS whitepaper titled "Database Encryption in SQL Server 2008 Enterprise Edition", Microsoft estimates the performance degradation for TDE to be 3-5%, while cell-level encryption is estimated to be 20-28%
Also, we have seen that the compress backup feature from SQL 2008 onwards which helps in saving the space without using any 3 rd party software seems to be of very less active means the compression it do with that feature on is quite less effective.
As far as i know i think Instant File initialization also does not work when TDE has been implemented.
Also, i am sure TDE also encrypts the backups starting from SQL server 2014, and don't think it does same for SQL server 2012

So there's few ups and downs on both TDE and Cell level, but you have to ultimately decide what suits and fits within you're system health as well as budget.

Many other points you would like to go and can be read here

Also,

  Implementing TDE in conjunction with cell-level encryption provides a
  layered approach to data security, which enhances its effectiveness.
  Another option for protecting sensitive data that is available is
  one-way encryption, also referred to as hashing. One-way encryption
  can be applied alongside TDE, cell-level encryption and other
  obfuscation methods


MSA and gMSA and SQL Services i wanted ask about gMSA and MSA accounts and overall do you create a single account or multiple for each service (Engine, Agent, SSIS, SSAS, SSRS).
Basically i am trying to figure out if I should follow the same design as i have when using Domain accounts.
In additions, would you still use gMSA for a single instance or would you than go for MSA accounts and reserve the gMSAs for Always ON AG?
The reason i am asking about gMSA and MSA is that security at my company is cracking down on service accounts that have non expiring passwords and I am trying to save my self from the calls when the account expires and the servers are down.

i am trying to figure out if I should follow the same design as i have when using Domain accounts.

Domain user accounts were both over-used, and over-shared.
They were over-shared to reduce the burden of password maintenance.  With MSA/gMSA you should provision separate accounts for each service that actually needs a domain account, but that shouldn't be too many because you should be using domain accounts less these days than in the past.
They ware over-used because before the service-hardening work in Windows they actually were a best-practice.  But with the adoption of virtual service accounts/per-service SIDs, and the move to virtualization reducing the incidence of large, multi-purpose servers, you should be using the using the default virtual accounts for local permissions, and the computer account for network permissions.
So you should use the default virtual accounts whenever you don't have a specific need for a domain accounts.  The specific needs are typically limited to:

You have a cluster and need the same service account on all nodes, or

You have many different services on the same server and don't want to provision network privileges to the computer account.


Multi column index on nested data I have a table that has a field user and a field group (among others). Each user is in exactly one group, not more, not less. Whenever I query for a user, I also know his group. Would it be faster to have a multi-column index and query for both, user and group, instead of just using a single-column index and querying only for the user? If yes, what index type would I choose? My idea was, that searching for the group first and then searching for the user only in those results would be fastest, but I'm not sure if any of the index types can actually do that.

I also thought about separating users and groups by putting them in different tables but as both numbers can grow indefinitely, I couldn't think of a way to do that without having an indefinite number of tables.

I'm mostly familiar with MySQL and MS SQL, so I can't speak to the performance of PostgreSQL on multi-column indexes.  However, my guess is that as long as the number of users is small (< 100,000), a single-column index on user would work well.

Regarding the second portion of your question:

  I also thought about separating users and groups by putting them in
  different tables but as both numbers can grow indefinitely, I couldn't
  think of a way to do that without having an indefinite number of
  tables.

It sounds like the user and group values are repeated in your table. If this is true, the usual thing to do would be to 'normalize' the data.  In this case, you could move the relationship between users and groups to a different table.

You haven't specified whether the user or the group is the most tied to the row in your current table, though I'll guess it's probably the user.

A table layout with users, groups, and data would usually look like:

data_table
data_id    data_col_1    data_col_2    user_id
1          1.2374        pancakes            2
2          8.2978        strawberries        1
3          78.001        hashbrowns          1
...

user_table
user_id    user_name     group_id
1          Travis        1
2          Melanie       1
3          John          3
...

group_table
group_id   group_name
1          sales
2          marketing
3          engineering
...

This assumes the property that you mentioned, where one user is a member of only one group.  If that property changed to allow users to join more than one group, you would delete the group_id column in the users table and create a new 'mapping' table with two columns: user_id and group_id

If your data is organized this way, then user names and group membership can change without modifying all matching rows in the data table.

Here is a more in-depth explanation of DB normalization:
https://www.essentialsql.com/get-ready-to-learn-sql-database-normalization-explained-in-simple-english/


Connecting to Oracle SQL Developer locally on Mac? I'm trying to connect to Oracle SQL Developer locally on my Mac. How exactly is this done? I've looked all over the network, and can't find anything helpful.

The only thing I did is installing the latest version of Oracle SQL Developer.

I have an assignment due tomorrow and cannot work from labs today.

SQL Developer doesn't come with an Oracle database, it is a client application. You can't "connect to SQL Developer", you use SQL Developer to connect to an existing database.

There are no supported editions of Oracle database for Mac OS X at this time, the latest that was available was 10gR2. You'll need to set one up in a VM on your machine (Windows or Linux, using the Oracle XE edition seems like it would fit your need), or see if you can somehow connect to your lab databases (possibly via a VPN to your school if they have that available).


How can I secure my database code? We have a lot of proprietary database code that is simply encrypted using the "WITH ENCRYPTION" option.  Unfortunately, this is a very weak method of securing database code.

Is there any better way to secure the database code?

We're currently on SQL Server 2005, if that helps, but we're in the process of migrating to SQL Server 2008 R2.

There isn't much you'll be able to do to prevent people from getting access to your code.

If it's encrypted WITH ENCRYPTION it can be decrypted pretty quickly.
If you use CLR procedures the T/SQL statements that are executed against the SQL Server can be captured via SQL Profiler very easily.  Not to mention many shops don't like SQL CLR and wouldn't bring your app in shop.
If you encrypt the procedures via third party code it can still be decrypted pretty easily (if SQL can decrypt the procedures so that it can run them, odds are a user will be able to decrypt it).

Your code should be protected through contracts and NDAs.

Personally when I see an app with encrypted procedures about the first thing I'm going to do is force them to decrypt them.  If they won't, I'll do it myself.  Most databases out there which come from venders aren't tuned very well, and if I'm going to be tuning the database I'm going to need access to the code.


RMAN archivelog backup in Oracle 11gR2 I use below command to do RMAN backups on rhel 5.9,  but archivelog backups always ends up in $ORACLE_HOME/dbs

Backup as compressed backupset database plus archivelog format '/path'

How to use the same path as mentioned in the above command to get both backupset of database in same location or other desirable location

You have to specify the format string twice, once for database backupsets, and once for the archivelog backups, for example:

backup as compressed backupset database format '/path/%U' plus archivelog format '/path/%U';


Index Strategies on Text or NVARCHAR(MAX) Fields I have the following query (simplified for question) I'm trying to speed up for a read only DB ...

SELECT
 [sysid]
,[Date]=CONVERT(CHAR, DATEADD(D, [date], '1800-12-28'),101)
,[From]=[from_addr]
,[To]=[to_addr]  --I'm a very long Text or NVARCHAR(MAX) Field
,[Subject]=[subject]
,CASE WHEN [attach] = 1 THEN 'Yes' ELSE 'No' END AS 'Att'
,[Code]=[ccode]
,[Staff]=[staff]
,[MatNo]=[mat_no]
FROM dbo.[email]
DYNAMIC WHERE CLAUSE ON ANY OF ABOVE

I've tried adding some indexes including covering indexes I can't include the to_addr the way it is (as text or NVARCHAR(MAX) col), and the query optimizer ends up using the clustered index because the to_addr field is not included.  What are some ways to handle a situation like this ?  Unfortunately I' limited to 2005 on this.

Edit

Tried adding Full_Text For to_addr still does a table scan.  However if I comment out that line out it will use the index.  : ( Damn you Text Data !

Why do you think anything but a scan should be used to pull back all the data? A full-text index won't really help - that helps you search those columns, but if you're just returning all the data (for any variety of WHERE clauses) then there's no shortcut to reading all of the data. Can I ask why a to_addr, which is presumably limited to ~320 characters by the SMTP standards (depending on which standard you believe), contains data > 4000 characters?

A lot of people think that a scan is bad. If you need to return a large amount of data, then often a clustered index scan will be used. Your where clause may lead to seeks being used to locate the rows to return, but a seek isn't going to work where the data in that column is that large. Are you just seeing a scan in the execution plan and assuming that must be the problem?


MySQL replication between VPS and shared host I have a VPS where I have ssh access and shared host with CPanel. Both have MySQL.
I need to create a master/slave setup among the two server.
As per this tutorial: How To Set Up Database Replication In MySQL, I will need to modify the my.conf file.
Is it possible to achieve this with the following permissions on both the servers?

VPS
I have root access with ssh login where I can modify /etc/my.conf file.

Shared host
Cpanel with phpmyadmin.

if you have MySQL root access on the shared host, there is a change that it works, just make sure that you:

Use the VPS as master
Enable the shared host ip to connect on vps host
run a SELECT @@server_id on shared host and set the vps to use a different one.
follow the steps described on tutorial
run the CHANGE MASTER TO on SQL tab on PhpMyAdmin


Is there overhead from using synonyms to access another database? If one database is accessing tables on another database on the same SQL server through synonyms, does that incur any significant overhead to either of the databases?

Setup: We have one database (A) that contains all the data for our application. This database runs some time consuming calculations for a point in time, and then stores the output in another database (A_Reports).

Through the client application, a user can load reports hosted on SSRS. These reports connect to database A which uses synonyms to access the data stored on database A_Reports. Some reports might need some of the data from A, but that is rarely needed and only occurs under certain conditions that the report will always be aware of.

We want to keep the utilization of A low since it has all the data for the app servers and A_Reports is only used to store the calculated reports data.

I would assume that since they are on the same SQL server it doesn't matter which database is connected to. So the real question is, if we change the reports in SSRS to connect to A_Reports instead of A, will this allow reports to access the data quicker or enable database A to run its queries more efficiently?

SQL Server resolves Synonyms before it generates the query plan and it should have little to no impact.

  So the real question is, if we change the reports in SSRS to connect to A_Reports instead of A, will this allow reports to access the data quicker or enable database A to run its queries more efficiently?

If your report runs faster on A_Reports instead of A and your synonym references A_Reports, it will run faster. If it references A, it will run slower.

I know your example is asking about cross referencing databases with synonyms and my examples below are regarding synonyms in the same DB. It should be handled identically though, as seen at this answer by Scott Hodgin. To illustrate further it would be handled the same. You will notice the use of the synonym has no impact and furthermore, you can't even notice the impact because it does not appear in the query plan or stats. (Because it happens in the bind phase and has negligible to no cost.)

Consider the experiment:

-- Experiment table
CREATE TABLE test
(one INT NOT NULL,
two INT NOT NULL,
three INT NOT NULL,
four INT NOT NULL);

-- Get records
INSERT INTO test
VALUES (1, 1, 1)
GO 10000

-- Make synonym
CREATE SYNONYM test2
FOR test;

-- Clear cache
DBCC FREEPROCCACHE;

-- Measure
SET STATISTICS IO ON;
SET STATISTICS TIME ON;

-- Test
SELECT * from test2;
SELECT * FROM test;

I ran each select independently and ran DBCC FREEPROCCACHE (DON'T DO THAT IN PRODUCTION) in between.

My first experiment, I created the table as a HEAP. I saw no change in plan or performance.

DBCC FREEPROCCACHE;
SET STATISTICS IO ON;
SET STATISTICS TIME ON;

-- Test synonym
SELECT * from test2;

  (10000 row(s) affected) Table 'test'. Scan count 1, logical reads 33,
  physical reads 0, read-ahead reads 0, lob logical reads 0, lob
  physical reads 0, lob read-ahead reads 0.

  (1 row(s) affected)

  SQL Server Execution Times:    CPU time = 0 ms,  elapsed time = 117
  ms. SQL Server parse and compile time:     CPU time = 0 ms, elapsed
  time = 0 ms.

Test 1: Synonym used in HEAP plan

-- Test base table
SELECT * FROM test;

  (10000 row(s) affected) Table 'test'. Scan count 1, logical reads 33,
  physical reads 0, read-ahead reads 0, lob logical reads 0, lob
  physical reads 0, lob read-ahead reads 0.

  (1 row(s) affected)

  SQL Server Execution Times:    CPU time = 0 ms,  elapsed time = 131
  ms. SQL Server parse and compile time:     CPU time = 0 ms, elapsed
  time = 0 ms.

Test 1: No synonym used in HEAP plan

The second experiment I added a primary key and clustered index, I also saw no change in plan or performance.

DROP TABLE test;

CREATE TABLE test
(one INT NOT NULL identity(1, 1) PRIMARY KEY,
two INT NOT NULL,
three INT NOT NULL,
four INT NOT NULL);

INSERT INTO test(1,1,1)
GO 10000

DBCC FREEPROCCACHE;
SET STATISTICS IO ON;
SET STATISTICS TIME ON;

-- Test synonym
SELECT * from test2;

  (10000 row(s) affected) Table 'test'. Scan count 1, logical reads 33,
  physical reads 0, read-ahead reads 0, lob logical reads 0, lob
  physical reads 0, lob read-ahead reads 0.

  (1 row(s) affected)

  SQL Server Execution Times:    CPU time = 16 ms,  elapsed time = 102
  ms. SQL Server parse and compile time:     CPU time = 0 ms, elapsed
  time = 0 ms.

Test 2: Synonym used in clustered table plan

DBCC FREEPROCCACHE;
SET STATISTICS IO ON;
SET STATISTICS TIME ON;

-- Test base table
SELECT * FROM test;

  (10000 row(s) affected) Table 'test'. Scan count 1, logical reads 33,
  physical reads 0, read-ahead reads 0, lob logical reads 0, lob
  physical reads 0, lob read-ahead reads 0.

  (1 row(s) affected)

  SQL Server Execution Times:    CPU time = 0 ms,  elapsed time = 83
  ms. SQL Server parse and compile time:     CPU time = 0 ms, elapsed
  time = 0 ms.

Test 2: No synonym used in clustered table plan

Last experiment I created a non-clustered index on the two column to include columns three and four as well and forced the use of an index.

CREATE NONCLUSTERED INDEX testnonc ON test (two);

DBCC FREEPROCCACHE;

-- Measure
SET STATISTICS IO ON;
SET STATISTICS TIME ON;

SELECT * FROM test2 WITH (INDEX(testnonc));

  (10000 row(s) affected) Table 'test'. Scan count 1, logical reads
  20654, physical reads 0, read-ahead reads 0, lob logical reads 0, lob
  physical reads 0, lob read-ahead reads 0.

  (1 row(s) affected)

  SQL Server Execution Times:    CPU time = 0 ms,  elapsed time = 104
  ms. SQL Server parse and compile time:     CPU time = 0 ms, elapsed
  time = 0 ms.

Test 3: Synonym used in clustered table with non-clustered index plan

DBCC FREEPROCCACHE;

-- Measure
SET STATISTICS IO ON;
SET STATISTICS TIME ON;

SELECT * FROM test WITH(INDEX(testnonc))

  (10000 row(s) affected) Table 'test'. Scan count 1, logical reads
  20654, physical reads 0, read-ahead reads 0, lob logical reads 0, lob
  physical reads 0, lob read-ahead reads 0.

  (1 row(s) affected)

  SQL Server Execution Times:    CPU time = 31 ms,  elapsed time = 126
  ms. SQL Server parse and compile time:     CPU time = 0 ms, elapsed
  time = 0 ms.

Test 3: No synonym used in clustered table with non-clustered index plan


How do I set up a Derived Column Transformation to get row number in SSIS 2014? I am trying to import a ragged right flat file. I am wanting to import the file as one column and used Derive Column Transform to add a row number to each row. I would like to end up with a column called RowNum and a column called EntireRow. How do I do this?

A derived column won't be able to add a row number. Well, it can add a column called RowNum but the expression language doesn't support the ability to update a value on the fly. Instead, you'll need a Script Component.

You can search for "SSIS generate surrogate key" and find plenty of reference implementations. I'm going to borrow from Joost's post Create a Row Id for this answer

// C# code: surrogate key script
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
    // New internal variable to store the rownumber
    private int rowCounter = 0;

    // Method that will be started for each record in you dataflow
    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        // Seed counter
        rowCounter++;

        // Fill the new column
        Row.RowNum = rowCounter;
    }
}

For those on 2005, the approach would look something like

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

<Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute> _
<CLSCompliant(False)> _
Public Class ScriptMain
    Inherits UserComponent

    ' New internal variable to store the rownumber
    Private rowCounter As Integer = 0

    ' Method that will be started for each record in you dataflow
    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
        'Seed counter
        rowCounter = rowCounter + 1

        ' Fill the new column
        Row.RowNum = rowCounter
    End Sub
End Class

And since I love to provide Biml based answers so you can use the following code, also from Joost Creating BIML Script Component Transformation (rownumber)

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
 <Annotations>
  <Annotation>
   File: Script Component Transformation RowNumber.biml
   Description: Example of using the Script Component as
   a transformation to add a rownumber to the destination.
   Note: Example has an OLE DB Destination that supports
   an identity column. Use your own Flat File, Excel or
   PDW destination that doesn't supports an identity.
   VS2012 BIDS Helper 1.6.6.0
   By Joost van Rossum http://microsoft-ssis.blogspot.com
  </Annotation>
 </Annotations>

 <!--Package connection managers-->
    <Connections>
            <OleDbConnection
                Name="Source"
                ConnectionString="Data Source=.;Initial Catalog=ssisjoostS;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;">
            </OleDbConnection>
            <OleDbConnection
                Name="Destination"
                ConnectionString="Data Source=.;Initial Catalog=ssisjoostD;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;">
            </OleDbConnection>
       </Connections>

       <ScriptProjects>
             <ScriptComponentProject ProjectCoreName="sc_c253bef215bf4d6b85dbe3919c35c167.csproj" Name="SCR - Rownumber">
                    <AssemblyReferences>
                           <AssemblyReference AssemblyPath="Microsoft.SqlServer.DTSPipelineWrap" />
                           <AssemblyReference AssemblyPath="Microsoft.SqlServer.DTSRuntimeWrap" />
                           <AssemblyReference AssemblyPath="Microsoft.SqlServer.PipelineHost" />
                           <AssemblyReference AssemblyPath="Microsoft.SqlServer.TxScript" />
                           <AssemblyReference AssemblyPath="System.dll" />
                           <AssemblyReference AssemblyPath="System.AddIn.dll" />
                           <AssemblyReference AssemblyPath="System.Data.dll" />
                           <AssemblyReference AssemblyPath="System.Xml.dll" />
                    </AssemblyReferences>
                    <ReadOnlyVariables>
                           <Variable VariableName="maxrownumber" Namespace="User" DataType="Int32"></Variable>
                    </ReadOnlyVariables>
                    <Files>
       <!-- Left alignment of .Net script to get a neat layout in package-->
                           <File Path="AssemblyInfo.cs">
using System.Reflection;
using System.Runtime.CompilerServices;

//
// General Information about an assembly is controlled through the following
// set of attributes. Change these attribute values to modify the information
// associated with an assembly.
//
[assembly: AssemblyTitle("SC_977e21e288ea4faaaa4e6b2ad2cd125d")]
[assembly: AssemblyDescription("")]
[assembly: AssemblyConfiguration("")]
[assembly: AssemblyCompany("SSISJoost")]
[assembly: AssemblyProduct("SC_977e21e288ea4faaaa4e6b2ad2cd125d")]
[assembly: AssemblyCopyright("Copyright @ SSISJoost 2015")]
[assembly: AssemblyTrademark("")]
[assembly: AssemblyCulture("")]
//
// Version information for an assembly consists of the following four values:
//
//      Major Version
//      Minor Version
//      Build Number
//      Revision
//
// You can specify all the values or you can default the Revision and Build Numbers
// by using the '*' as shown below:

[assembly: AssemblyVersion("1.0.*")]
                           </File>
       <!-- Replaced greater/less than by &gt; and &lt; -->
                           <File Path="main.cs">#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
#endregion

/// &lt;summary&gt;
/// Rownumber transformation to create an identity column
/// &lt;/summary&gt;
[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
 int rownumber = 0;

 /// &lt;summary&gt;
 /// Get max rownumber from variable
 /// &lt;/summary&gt;
 public override void PreExecute()
 {
  rownumber = this.Variables.maxrownumber;
 }

 /// &lt;summary&gt;
 /// Increase rownumber and fill rownumber column
 /// &lt;/summary&gt;
 /// &lt;param name="Row"&gt;The row that is currently passing through the component&lt;/param&gt;
 public override void Input0_ProcessInputRow(Input0Buffer Row)
 {
  rownumber++;
  Row.rownumber = rownumber;
 }
}
                           </File>
                    </Files>
                    <InputBuffer Name="Input0">
                           <Columns>
                           </Columns>
                    </InputBuffer>
                    <OutputBuffers>
                           <OutputBuffer Name="Output0">
                                  <Columns>
                                        <Column Name="rownumber" DataType="Int32"></Column>
                                  </Columns>
                           </OutputBuffer>
                    </OutputBuffers>
             </ScriptComponentProject>
       </ScriptProjects>

       <Packages>
             <!--A query to get all tables from a certain database and loop through that collection-->
             <# string sConn = @"Provider=SQLNCLI11.1;Server=.;Initial Catalog=ssisjoostS;Integrated Security=SSPI;";#>
             <# string sSQL  = "SELECT name as TableName FROM dbo.sysobjects where xtype = 'U' and category = 0 ORDER BY name";#>
             <# DataTable tblAllTables = ExternalDataAccess.GetDataTable(sConn,sSQL);#>
             <# foreach (DataRow row in tblAllTables.Rows) { #>

             <!--Create a package for each table and use the tablename in the packagename-->
             <Package ProtectionLevel="DontSaveSensitive" ConstraintMode="Parallel" AutoCreateConfigurationsType="None" Name="ssisjoost_<#=row["TableName"]#>">
                    <Variables>
                           <Variable Name="maxrownumber" DataType="Int32">0</Variable>
                    </Variables>

                    <!--The tasks of my control flow: get max rownumber and a data flow task-->
                    <Tasks>
                    <!--Execute SQL Task to get max rownumber from destination-->
                    <ExecuteSQL
                           Name="SQL - Get max rownumber <#=row["TableName"]#>"
                           ConnectionName="Destination"
                           ResultSet="SingleRow">
                           <DirectInput>SELECT ISNULL(max([rownumber]),0) as maxrownumber FROM  <#=row["TableName"]#></DirectInput>
                           <Results>
                           <Result Name="0" VariableName="User.maxrownumber" />
                           </Results>
                    </ExecuteSQL>

                    <!--Data Flow Task to fill the destination table-->
                    <Dataflow Name="DFT - Process <#=row["TableName"]#>">
                    <!--Connect it to the preceding Execute SQL Task-->
                    <PrecedenceConstraints>
                           <Inputs>
                                  <Input OutputPathName="SQL - Get max rownumber <#=row["TableName"]#>.Output"></Input>
                           </Inputs>
                    </PrecedenceConstraints>

                    <Transformations>
                    <!--My source with dynamic, but ugly * which could be replace by some .NET/SQL code retrieving the columnnames-->
                    <OleDbSource Name="OLE_SRC - <#=row["TableName"]#>" ConnectionName="Source">
                           <DirectInput>SELECT * FROM <#=row["TableName"]#></DirectInput>
                    </OleDbSource>

                    <ScriptComponentTransformation Name="SCR - Rownumber">
                           <ScriptComponentProjectReference ScriptComponentProjectName="SCR - Rownumber" />
                    </ScriptComponentTransformation>

                    <!--My destination with no column mapping because all source columns exist in destination table-->
                    <OleDbDestination Name="OLE_DST - <#=row["TableName"]#>" ConnectionName="Destination">
                           <ExternalTableOutput Table="<#=row["TableName"]#>"></ExternalTableOutput>
                    </OleDbDestination>
                    </Transformations>
                    </Dataflow>
                    </Tasks>
             </Package>
             <# } #>
       </Packages>
       </Biml>

<!--Includes/Imports for C#-->
<#@ template language="C#" hostspecific="true"#>
<#@ import namespace="System.Data"#>
<#@ import namespace="System.Data.SqlClient"#>


AlwaysON Availability Groups - IP address change of secondary replica node I have a multi-subnet AlwaysON AG with one primary and one secondary replica in synchronous commit mode.  Due to hardware maintenance the IP address of the secondary node will change.

Is there any actions I should take or anything I should be aware of from a SQL Server, AlwaysON Availability Groups and WSFC perspective?

Due to hardware maintenance the IP address of the secondary node will change.

This shouldn't be a problem if the node ip itself is changing. It's only a slight issue if cluster resource ips are changing.

  Is there any actions I should take or anything I should be aware of from a SQL Server, AlwaysON Availability Groups and WSFC perspective?

From the WSFC point of view, assuming that the subnet does not change, there shouldn't be anything needed here.

From SQL Server/AlwaysOn AGs... assuming the subnet does not change:

Double check the AG endpoint to see if it's bound to that specific ip. If it is, that will need changed. If it isn't (wizard generated, etc.) it'll say 'ALL' and that won't need changed.
If SQL Server was setup to listen on specific ip's and ports in configuration manager, this will need changed. If it's set for IPALL, nothing needs done.
If you're using read only routing, double check the read only routing urls and endpoints.
Service broker endpoints (similar to AG endpoint)

From an overall point of view:

Make sure the firewall rules have been edited for the new ip if needed.

This may not be an exhaustive list, but should give you an idea and the most critical areas to look at.


Need a way to query a table, and JOIN it with the TOP 1 related record from an other table As a follow up to this question, I'm wondering if there is a better and/or more efficient way to gather the data in question.

As stated, this query does return me 95% of the data I need --

SELECT dv.Name
      ,MAX(hb.[DateEntered]) as DE
FROM
    [Devices] as dv
    INNER JOIN
    [Heartbeats] as hb ON hb.DeviceID = dv.ID
GROUP BY dv.Name
HAVING MAX(hb.[DateEntered]) < '20130304';

Is there a way to achieve a the same result (for each Device, select the TOP Heartbeat ordered DESC by DateEntered) but also select the entire row from the [Heartbeats] table? Right now, I only get the DateTime for that row.

If I include the additional columns in the GROUP BY clause, I can then add them to the select; but then I get multiple rows per [Devices] row which I don't want. It sounds odd, but what I basically want to do is do a query against [Devices] and then do a for...each over that set and add the top [Heartbeats] row for that [Devices] row. Is that possible?

update
This is the structure of the Heartbeats table:

CREATE TABLE [dbo].[Heartbeats](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [DeviceID] [int] NOT NULL,
    [IsFMSFMPUp] [bit] NOT NULL,
    [IsFMSWebUp] [bit] NOT NULL,
    [IsPingUp] [bit] NOT NULL,
    [DateEntered] [datetime] NOT NULL,
 CONSTRAINT [PK_Heartbeats] PRIMARY KEY CLUSTERED
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [CommonQueryIndex] ON [dbo].[Heartbeats]
(
    [DateEntered] ASC,
    [DeviceID] ASC
)
INCLUDE ( [ID],
[IsFMSFMPUp],
[IsFMSWebUp],
[IsPingUp]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [HeartbeatDeviceIndex] ON [dbo].[Heartbeats]
(
    [DeviceID] ASC
)
INCLUDE ( [ID]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
/****** Object:  Default [DF_Heartbeats_DateEntered]    Script Date: 03/05/2013 10:45:45 ******/
ALTER TABLE [dbo].[Heartbeats] ADD  CONSTRAINT [DF_Heartbeats_DateEntered]  DEFAULT (getdate()) FOR [DateEntered]
GO
/****** Object:  ForeignKey [FK_Heartbeats_Devices]    Script Date: 03/05/2013 10:45:45 ******/
ALTER TABLE [dbo].[Heartbeats]  WITH CHECK ADD  CONSTRAINT [FK_Heartbeats_Devices] FOREIGN KEY([DeviceID])
REFERENCES [dbo].[Devices] ([ID])
GO
ALTER TABLE [dbo].[Heartbeats] CHECK CONSTRAINT [FK_Heartbeats_Devices]
GO

You can do this pretty easily with OUTER APPLY (if you're on 2005 or newer). Note that there may be better performing ways of achieving the result, such as using ROW_NUMBER() - check execution plans if in doubt. Also, SELECT * is lazy and inadvisable; I'm just doing it here for illustrative purposes, and because I don't know the real structure of the Heartbeats table.

SELECT
    dv.Name,
    hb.*
FROM [Devices] as dv
    OUTER APPLY (
        SELECT TOP 1 *
        FROM Heartbeats
        WHERE Heartbeats.DeviceID = dv.ID
        ORDER BY DateEntered DESC
    ) hb
WHERE ISNULL(hb.DateEntered, '1900-01-01T00:00') < '2013-03-04T00:00'

See Books Online for the finer points of OUTER APPLY vs. CROSS APPLY (it's much like OUTER JOIN vs. INNER JOIN). It was always such a pain doing queries like this in SQL Server 2000 where you didn't have OUTER/CROSS APPLY or the ROW_NUMBER() function.


Assign most elevated permission to Sql Server DB I am using SQL server 2008 R2, on my development machine. I had to delete the old windows user account, with which I had installed SQL Server.

I have created a new admin account.
When doing any DB related work in Visual Studio with new user, I get DB Access denied error on master table.

As this is basically for development purpose, I need most elevated permission for this user.

How do I grant a windows user admin rights to all the tables?

Assuming this is a Windows Auth only instance (with no sa access), try using psexec per here.

An actual backdoor into SQL Server does exist that does not require restarting and/or rebooting anything into single-user mode. I have done this on systems where I did not have access but needed to check stuff.

Download PSexec tools from here. Place this on the server and then in a command prompt execute this command:psexec -i -s SSMS.exe, or sqlwb.exe

This will open up SSMS as the system account that has sysadmin access to the instance of SQL Server. This is done during installation of SQL Server, however I have heard that this will not be so with SQL 2012.


Why do the outdated statistics degrade a query performance? Some facts:

1. The outdated statistics cause generating an inaccurate estimated
    plan. The estimated execution plan is created by Query Optimizer
    which uses both statistics and the query processor tree.

2. The actual execution plan is created after executing the query. When the stats are stale, the Storage Engine generates its own plan which is different than the estimated one.

The question: Why to bother about outdated stats if SQL Server can create accurate actual execution plan (by Storage Engine) when it finds out that data has been changed? How can the difference between the estimated and actual plan influences the query performance?

It seems that the facts you presented are not so precise, let me say:

1. The estimated execution plan is created by Query Optimizer which uses statistics to generate the query processor tree. (query processor tree = execution plan)

2. The actual execution plan is the estimated execution plan, annotated with the real cost. This can be used to validade how wrong is the plan.

If your query optimizer don't have good stats, it will generate bad plans. Your query will run with that bad plan and only at the end (after the execution), if you compare with the actual execution plan, it only proves that Query Optimizer estimated it all wrong. (i.e.: estimates 1 seek, executed 239827492 seeks)

What is called execution plan is the optimized version of the original parsed query tree. The process involved on the optimization is complex and it is a NP-complete problem. The changes made on the original tree are called optimization and they are done based on the statistics, as well as other variables such as number of CPUs available, etc. The plan does not changes during the execution, unless you consider Adaptive query processing, added on SQL Server 2017; but then this starts another topic

I would recommend that you check the following links understant better how it works:

SQL Server Statistics Basics - https://www.red-gate.com/simple-talk/sql/performance/sql-server-statistics-basics/

Execution Plan Basics - https://www.red-gate.com/simple-talk/sql/performance/execution-plan-basics/


How and where do I confirm TCP ports are open when performing mirroring? (Principal, Mirror & Witness) I have a SQL 2017 Principal, Mirror and Witness that I'm trying to set up for high-availability. I've created certificates specifying port 7024 because the customer is running in a Workgroup and not a Domain.  I keep getting the attached error when trying to start mirroring.  I've tried specifying 7024 as the TCP port in the SQL Configuration Manager. Is there something I'm missing?  Does the port for the Mirroring endpoint need to be different from the Principal?  I've tried searching here for the same error but I'm getting mixed information about this.

The error message shown is:

  The server network address "TCP://kb-versiosql1:7024" can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational. (Microsoft SQL Server, Error: 1418)

Thanks to everyone who commented!  Your suggestions were all extremely helpful.  I was able to re-trace my steps and did the following:

Made sure certificates were created for both partners, AND Witness authorized using the correct logins and '.cer' files.

Allowed CONNECT permission on login for remote mirroring endpoint.


Script out objects that are in a specific schema I am running SQL Server 2014 and using SSMS.

Is there a way to script out all objects in a specific schema (stored procedures, views, tables etc.) so that I can generate the objects in another database?

Is there a way to narrow it down to scripting out only all the tables in a certain schema?

There's no automatic way to do that through the UI. You can selectively script just those objects, but it relies on manually checking a bunch of checkboxes:

If you need something better than that, you'll have to let go of the UI, roll up your sleeves, and invest some time in Powershell / SMO.