Role creation with alter privilege Hi I am creating a SSIS package that truncates table as part of ETL process. A role needs to be defined with the alter privilege and the service account added to the role

I have done it the following way. Could you let me know if it is correct ? Isnt there a way of only assigning alter permission to role rather than involving table

USE CoreReferenceStaging
exec sp_addrole  'alterpermissions', 'INT\svc-w-corerefdata-de'
exec sp_addrolemember 'alterpermissions', 'INT\svc-w-corerefdata-de'
GRANT ALTER ON [dbo].[theTable] TO [alterpermissions]

First to answer your question, yes that looks like it will work.  However, this approach opens up permissions a bit more than you probably want.  While it's accurate that ALTER permissions are required to Truncate a table, ALTER permissions also grant the following:

  When granted on a scope, ALTER also bestows the ability to alter, create, or drop any securable that is contained within that scope.

If you only require this process to truncate a table, I suggest you create a Stored Procedure that utilizes elevated rights via Impersonation to perform the truncate so you keep your SSIS account at a minimal level of security.

First, you need to create a stored procedure that provides your Truncate Table functionality, as follows:

-- Run this procedure as a user with appropriate Control privileges in the database, and adjust to your liking
CREATE PROCEDURE [dbo].[TRUNCATE_TABLE]
(
    @TableName NVARCHAR(128)
) WITH EXECUTE AS SELF
AS
BEGIN
    -- Only Allow Truncates to dbo schema and user's owned Schema
    IF EXISTS (SELECT * FROM sys.tables t INNER JOIN sys.schemas s
                    ON t.schema_id = s.schema_id
                   WHERE (s.name + N'.' + t.name = @TableName
                        OR s.name + N'.' + t.name = (SELECT TOP 1 default_schema_name FROM sys.database_principals
                                                     WHERE type = 'S' and name = USER_NAME(ISNULL(DATABASE_PRINCIPAL_ID(ORIGINAL_LOGIN()), DATABASE_PRINCIPAL_ID()))) + N'.' + @TableName)
                        AND s.name IN (N'dbo', (SELECT TOP 1 default_schema_name FROM sys.database_principals
                                                     WHERE type = 'S' and name = USER_NAME(ISNULL(DATABASE_PRINCIPAL_ID(ORIGINAL_LOGIN()), DATABASE_PRINCIPAL_ID())))))
    BEGIN
        DECLARE @ReturnCode INT
        DECLARE @DynamicSQL NVARCHAR(272)

        SELECT @TableName = CASE WHEN @TableName LIKE N'%.%' THEN @TableName
                                 ELSE QUOTENAME((SELECT TOP 1 default_schema_name FROM sys.database_principals
                                                 WHERE type = 'S' and name = USER_NAME(ISNULL(DATABASE_PRINCIPAL_ID(ORIGINAL_LOGIN()), DATABASE_PRINCIPAL_ID())))) + N'.' + @TableName
                            END

        SET @DynamicSQL = N'TRUNCATE TABLE ' + @TableName

        -- Initialize @ReturnCode variable
        SET @ReturnCode = -1

        BEGIN TRY
            -- Attempt Truncate Operation
            EXEC @ReturnCode = sp_executeSQL @DynamicSQL
        END TRY
        BEGIN CATCH
            -- If an Error is encountered, trap it
            DECLARE @ErrorMessage NVARCHAR(2048), @ErrorNumber INT, @ErrorSeverity INT,
                @ErrorState INT, @ErrorLine INT, @ErrorProcedure NVARCHAR(126);

            SELECT @ErrorNumber = ERROR_NUMBER(),
                   @ErrorSeverity = ERROR_SEVERITY(),
                   @ErrorState = ERROR_STATE(),
                   @ErrorLine = ERROR_LINE(),
                   @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '');

            SELECT @ErrorMessage = N'Error %d, Level %d, State %d, PROCEDURE %s, Line %d, ' +
                N'Message: ' + ERROR_MESSAGE();

            -- Raise an error: msg_str parameter of RAISERROR will contain
            -- the original error information.
            RAISERROR(@ErrorMessage, @ErrorSeverity, 1, @ErrorNumber, @ErrorSeverity,
                      @ErrorState, @ErrorProcedure, @ErrorLine)

            RETURN @ReturnCode
        END CATCH
    END

    ELSE
    BEGIN
        -- If user is trying to Truncate a table they are not allowed to, throw and error.
        RAISERROR(N'Cannot find the object because it does not exist or you do not have permissions.'
                ,16, 1, 1088, 16, 7, N'[dbo].[TRUNCATE_TABLE]', 1)
        RETURN -1
    END
END

Now create a database role against which execution of this stored procedure is provided.

-- Create a DB Role
CREATE ROLE TruncatePrivs
GRANT EXECUTE ON TRUNCATE_TABLE TO TruncatePrivs
GO

-- Add Users to the Role
EXEC sp_addrolemember 'TruncatePrivs', 'INT\svc-w-corerefdata-de'
GO

The SSIS user can now truncate whatever table you wish to allow simply by calling the stored procedure from within a Execute SQL Task step within the package, as follows:

EXEC dbo.TRUNCATE_TABLE @Tablename = N'TableName'


Compare the tablespace concept between Postgres and Oracle When I create a table, it is placed in a default tablespace such as "USERS", unless I specify a user created tablespace, say, MyTableSpace.

For example,

create table test_tab(

name varchar2(100),
photo BLOB,
text   CLOB
)
tablespace "USERS"   --default
lob ("photo")  tablespace "MyTableSpace"   --user created
lob ("text")  tablespace "MyTableSpace"    --user created

Now, convert the above table to the counterpart in Postgres.  Where could those columns be placed?

A default tablespace?  User create tablespace?  Or some proprietary logical structure in Postgres?

In PostgreSQL, if you don't specify a tablespace in your CREATE TABLE command, the value of parameter default_tablespace will be used.

If default_tablespace is not set, or has an invalid value, the table will be created in the same tablespace as the database.

https://demo.pganalyze.com/databases/6/config/default_tablespace


How to tell what invoked a trigger in SQL Server? I'm working on a legacy SQL Server application, with close to 3,000 tables and more than 10,000 stored procedures.

One of those stored procedures is deleting some needed data from a table. I don't know which one it is.

I created a trigger on the table which logs all deleted rows in another table, created just for debugging. It would be very helpful if the trigger could also log the name of the stored procedure which invoked it. Is such a thing possible in SQL Server 2008 R2 (v10.50)?

If not, is there any other information available from within a trigger which could help me identify why it was invoked?

Couple of Options:

Option1:

Your best option is to run a server side trace with filtering options enabled.

This way you can narrow down the amount of data captured.

Option2:

Depending on the version (Enterprise), you can use SQL Audit as it allows you to track DELETE, EXECUTE and other Actions on the Object level as well. So you can filter the table that you are interested and enable the audit. Note that SQL Audit behind the covers
uses Extended Events.

Option 3: (I have not used this .. but its from msdn)

Light weight SQL Server procedure auditing without using SQL Server auditing version 2

Another option would be to use CONTEXT_INFO as described in this answer. I dont see if this will help you as you will not be modifying legacy code.


Is it possible to get execution call stack in a trigger? I have 10 stored procedures and each of them does INSERTs into one tableX.

Is it possible in a trigger body of tableX to get what object causes modification of tableX (stored  proc1 or sp2 or....) ?

Thank you.

Yes, it is possible to identify the running code, by using the @@procid system function, and better OBJECT_NAME(@@PROCID) to have the complete name.

Definition: "Returns the object identifier (ID) of the current Transact-SQL module. A Transact-SQL module can be a stored procedure, user-defined function, or trigger. @@PROCID cannot be specified in CLR modules or the in-process data access provider."

You can read about it here.

Another option would be to check the sql plan of the current spid and save that info in a logging table.
A sample query to be used in each procedure to save audit data would be :

select sp.hostname, sp.program_name, sp.loginame,
    st.text as query_text
from sysprocesses sp
cross apply sys.dm_exec_sql_text(sp.sql_handle) as st
where sp.spid = @@spid

Maybe there are too many details there..but I believe that you get the idea.

A third option would be to use the context_info information to the current SP's session. And associate somewhere the context information saved there with each procedure. For example in procedure1 you write 111 to the context, in procedure2 you write 222.. and so on.

A lot more info regarding context_info you can read in this SO question.


Question marks instead Hebrew characters after mysql dump and import I tried to migrate my AWS RDS instance from Aurora to MySQL. I created a dump file from the Aurora and imported it into the MySQL instance.

Both instances have the same characters set.

mysql> show variables like '%char%';
+--------------------------+-------------------------------------------+
| Variable_name            | Value                                     |
+--------------------------+-------------------------------------------+
| character_set_client     | utf8mb4                                   |
| character_set_connection | utf8mb4                                   |
| character_set_database   | latin1                                    |
| character_set_filesystem | binary                                    |
| character_set_results    | utf8mb4                                   |
| character_set_server     | latin1                                    |
| character_set_system     | utf8                                      |
| character_sets_dir       | /rdsdbbin/mysql-5.7.26.R1/share/charsets/ |
+--------------------------+-------------------------------------------+

In the original DB the Hebrew letters appears as black diamonds (Converted to Hebrew in app side). After the migration to the new MySQL instance, instead of black diamonds i see questions marks.

What could be the issue?

I think your problem is that you have exported your dump file with ANSI character set, to take a dump file using UTF8, issue your mysqldump command with option --default-character-set=utf8 and check the results. (AFAIK the same options for mysqldump work for Aurora as well)

PS : After your comments, I dug the problem a bit more. The problem comes from different configurations for character-set-system (which is utf8), character_set_server (which is latin1) with character-set-client (which is utf8mb4). Based on this link to documentation, this difference might cause issues in data input and may result in bad format in output. You can check the validity of this root cause by changing your character sets for different components.


Autogrow. Primary vs additional data files My databases all use autogrow, which grows the primary MDF file on a percentage.  But one of the databases, from a third party application grows by adding additional .NDF files.

Where is this option set?  When I look at autogrow settings, there is the option to grow or not, by percentage or by xMB, and an option for limited or unlimit growth.  But I see nothing that tells it whether to grow the primary MDF, or grow by adding additional NDFs.

And, is there a way to combine these NDF files back into the primary MDF?

Thanks!

RZ

Like @JonSeigel said above in his comment, this is not a SQL Server action.  Your third party application(s) must be doing this on their own, or somebody manually creating the additional data files.

  And, is there a way to combine these NDF files back into the primary MDF?

What you're looking for is DBCC SHRINKFILE(YourDataFileName, EMPTYFILE); where YourDataFileName is the logical name of the *.ndf file(s).  What this does is, provided you have sufficient space in the other data file(s) in the containing filegroup, is it empties the database data file by moving all of the data into the other data files in the same filegroup.  You can then remove it.

See this BOL reference: DBCC SHRINKFILE

There is an example on that same page that I will quote verbatim (as it seems to be exactly what you are trying to do [Note: The below excerpt is taken from the above link]):

  D. Emptying a file

  The following example demonstrates the procedure for emptying a file
  so that it can be removed from the database. For the purposes of this
  example, a data file is first created and it is assumed that the file
  contains data.

USE AdventureWorks2012;
GO

-- Create a data file and assume it contains data.
ALTER DATABASE AdventureWorks2012
ADD FILE (
    NAME = Test1data,
    FILENAME = 'C:\t1data.ndf',
    SIZE = 5MB
    );
GO

-- Empty the data file.
DBCC SHRINKFILE (Test1data, EMPTYFILE);
GO

-- Remove the data file from the database.
ALTER DATABASE AdventureWorks2012
REMOVE FILE Test1data;
GO


Modelling products along with their attributes and discounts for an e-Commerce database Scenario
My application will have products belonging to 1 company. Each of those products will:

Have at least 1 quantity
Have 0 or more discounts
Have 1 or more attributes
Belong to 1 category, which can have parent a parent category

A quantity can be 1 unit, 1 box, 1 pallet... Each quantity will thus have it's own price and that price can change over time. So I've added a fromDate attribute to know which price was active at which time. Is necessary when a product gets bought so I know the price of the quantity at the time of purchase.
A discount can be something like 50 euro, 5%, 2 for 1... Also having a fromDate so I know which discount is applicable.
Attributes will always belong to 1 product. And these will contain a name of the attribute and the value of the attribute. Their goal is to give the company the possibility to add more detailed information next to the existing required information.
Examples of attributes:

Key Feature 1 => Key Value 1
Name => Something
Color => Black
Size => 10 cm (h) x 5 cm (w)
Weight => 450 gr

Diagram
The following diagram represents my complete database schema as I have it now (only the section regarding products is relevant here):

Questions

How do I create my schema so that my requirement for discounts is met? How do you handle the ability to have different types of discounts? And how do you best keep track when there is an active discount for the product and where there is none?
Does it make sense to simply store attributes as name and value? It's possible that the same attribute name will be used for multiple products. Do I keep a list of all possible attribute names? What is the most dynamic way of tackling this issue?

regarding question 1):
A table with the following fields
1) date ,to set the date-from which the discount is valid
2) product, to define which product we are talking about
3) discount_type, i.e. 1=percentage, 2=how_many_product_for_this_price, 3=price
4) percentage, needed for discount_type=1
5) quantity, needed for discount_type=2
6) price, needed discount_type=3

Example:
If you have this data (I will leave out date for simplicity):

product, discount_type, percentage, quantity, price
1, 1, 0.5, null, 42
2, 2, null, 2, 42
3, 3, null, null, 25

product 1 has a discount of 0.5*100 = 50%, and a price of $42

product 2 can be sold 2 for the price of one, so 2 for $42

product 3 has a discount for $25


How can I normalize this database? I've got legacy ecommerce database, which is awfully designed and I need to move all products from it to new solution. But I stuck with how product properties stored. So it basically has tens of tables named category_properties_<category_id> that have first column named product_id and then variable number of columns that contain properties_id.

I. e. there can be table category_properties_42 with records like

|<product1_id>|20|21|22|23|24|27|

and table category_properties_43 with records like

|<product2_id>|17|21|56|

Is it possible using MySQL to export this data in more reasonable format? Because variable number of columns and variable table names makes querying by product almost impossible. Thanks in advance.

I would lean toward a single Category_Properties table with product_id, category_id, and optionally (if you need to retain the position of the property values in the original design) a sequence number of some sort.  You are going to have to do some programming to convert this, but it can be done with some 'brute force' SQL if you are trying to avoid scripting something.

CREATE TABLE Category_Properties
( product_id
, category_id
, sequence_value  /* this is optional */
, property_id
);

INSERT INTO Category_Properties
SELECT * FROM (
  SELECT product_id, 43 AS cat, 1 AS seq, <first_property_col> AS pval FROM category_properties_43
   UNION
  SELECT product_id, 43, 2, <second_property_col> FROM category_properties_43
   UNION
  SELECT product_id, 43, 3, <third_property_col> FROM category_properties_43
 )
 WHERE pval IS NOT NULL
;

-- From your example this would be <first_property_col> = 17, then 21, then 56.

You can do this provided you know the max number of property columns for each category, the outer query will eliminate the missing values for products which don't use all the property columns.  You can see why you might want to script this, rather than changing it for each new category.


Tips on reshaping data, pivot-like? I have a table of levels and years that looks roughly like this:

INSERT INTO demo(first_name, rank, year) AS VALUES
  ('Peter',2,2017), ('Robert',3,2016), ('Evelyn',2,2017),
  ('Rand',3,2017), ('Kofi',3,2017), ('Daniel',3,2016), ('Leonard',1,2017),
  ('Mary',1,2017), ('Frank',1,2017), ('John',3,2017), ('Mary',1,2016),
  ('Gilbert',3,2016), ('Quince',2,2017), ('David',3,2017), ('Louis',2,2017),
  ('Felipe',2,2016), ('Alfonso',1,2017), ('Elise',3,2016), ('Jorge',3,2017),
  ('Mary',3,2016), ('Jasvant',2,2017), ('Paul',3,2017), ('Richard',2,2017),
  ('Christopher',2,2017), ('Johannes',2,2016), ('Nasser',3,2016), ('Bruce',3,2017),
  ('John',3,2016), ('Frederic',3,2016);

I can capture totals for each level and year with a pretty basic COUNT():

SELECT  rank, COUNT(*), year
FROM demo
GROUP BY rank, year
ORDER BY year, rank;

That gives me a fairly tidy summary of the demo data:

Rank  Count  Year
1      1    2016
2      2    2016
3      8    2016
1      4    2017
2      7    2017
3      7    2017

My actual data is a lot bigger, and I'd like to be able to see it in a summary table that looks something more like this:

Rank    2016    2017
1         1     4
2         2     7
3         8     7

Is there a straightforward way to query a pivot-like view like this? For a much wider range of ranks and years?

You can pivot your data using crosstab, but as usual it requires a well-known column's name.

First you should install tablefunc module.

CREATE EXTENSION tablefunc;

Then you can call a crosstab with

SELECT * FROM
  crosstab('SELECT rank, year::INT, COUNT(*)::INT ct FROM demo
            GROUP BY 1, 2 ORDER BY 1, 2')
  AS ct(rk INT, "2016" INT, "2017" INT)

rk | 2016 | 2017
-: | ---: | ---:
 1 |    1 |    4
 2 |    2 |    7
 3 |    8 |    7

dbfiddle here


MS SQL Server instance name Quick question guys!

Will there be any issues if I use the default instance (MSSQLSERVER) for both test and production environments? They are on separate physical servers. I'm using MS SQL Server 2012 which will run on clustered Windows Server 2012?

Thanks in advance and Happy New Year.

That will be no problem to have multiple default instances in your environment. The only real restriction here is you can only have one default instance per machine.

You will have different network names for the different failover cluster instances. In short, if I'm understanding your question correctly then no that'll be no problem.


Why does PostgreSQL perform a seq scan when comparing a numeric value with a bigint column? Given a table vp with column timestamp type bigint, and a btree index on timestamp, why would Postgres ignore the index and run a seq scan on comparison of timestamp with a floating point value, when an index scan would produce identical results?

Integer comparison:

SELECT * FROM vp WHERE vp.timestamp > 1470752584 takes 48 ms:

 Index Scan using vp_ts_idx on vp  (cost=0.57..257.87 rows=2381 width=57) (actual time=0.014..38.669 rows=80323 loops=1)
   Index Cond: ("timestamp" > 1470752584)
 Total runtime: 48.322 ms

Numeric comparison:

SELECT * FROM vp WHERE vp.timestamp > 1470752584.1 takes 103 seconds because it ignores vp_ts_idx and performs a seq scan of the entire table:

  Seq Scan on vp  (cost=0.00..7378353.16 rows=95403915 width=57) (actual time=62625.420..103122.701 rows=98240 loops=1)
   Filter: (("timestamp")::numeric > 1470752584.1)
   Rows Removed by Filter: 285945491
 Total runtime: 103134.333 ms

Context: A query for recent vehicle positions compared timestamp with EXTRACT(EPOCH FROM NOW()) - %s, where %s was the desired number of seconds, without explicitly casting to a bigint. The workaround is to use CAST(EXTRACT(EPOCH FROM NOW()) - %s AS bigint).

Why wouldn't the query planner doesn't do this automatically when the column type is bigint? Is this a bug, or am I not considering some edge case where this behavior would be useful?

The key is that you don't compare the same types.  When comparing a bigint to a numeric, the easier way is to 'expand' the bigint with the decimal places being 0 (like 1 -> 1.0), while the other way around it would mean rounding/truncation.  (In this specific case it is easy to see that the both lead to the same result, but what if the values are negative?)

So, what you get in your comparison, is a numeric to numeric comparison, which is not something a bigint index could serve.

It's worth a look which casts are possible and which is performed in these cases.  For this, here are the two rows from pg_cast:

SELECT castsource::regtype, casttarget::regtype, castcontext
  FROM pg_cast
 WHERE castsource::regtype = 'bigint'::regtype
   AND casttarget::regtype = 'numeric'::regtype;

 castsource │ casttarget │ castcontext
────────────┼────────────┼─────────────
 bigint     │ numeric    │ i

SELECT castsource::regtype, casttarget::regtype, castcontext
  FROM pg_cast
 WHERE castsource::regtype = 'numeric'::regtype
   AND casttarget::regtype = 'bigint'::regtype;

 castsource │ casttarget │ castcontext
────────────┼────────────┼─────────────
 numeric    │ bigint     │ a

According to the linked documentation page, castcontext

  Indicates what contexts the cast can be invoked in. e means only as an explicit cast (using CAST or :: syntax). a means implicitly in assignment to a target column, as well as explicitly. i means implicitly in expressions, as well as the other cases.

So this means that the numeric -> bigint direction is happening 'by itself' (i. e. you not calling one of the casting operators explicitly) only if you assign the former to the latter.  In expressions like your comparison, this is not the case, so the parser will only consider the other way (marked with an i above).  That means, you get a numeric to numeric comparison, unless you force it otherwise.

Notes:

psql is a command line client to PostgreSQL, it does not do any sort of these things itself (I've edited the title accordingly)
using keywords as column names (like timestamp) is never a good idea - you'll possibly get unexpected parsing errors here and there, unless you are careful enough to double-quote them everywhere
using the Unix epoch as a timestamp might be cumbersome.  There is a rich functionality for 'real' timestamps in PostgreSQL - in most cases it is much easier to use it.


Executing stored procedures in oracle inside a package using dbms_job I am trying to execute multiple stored procedures through package using DBMS_JOB. Even if the job is getting submitted and is showing in USER_JOBS its not getting started for some reason. I have tried with and without putting the 'START DATE' parameter with the same result i.e. no start.

I am using ORACLE 9i and i guess i cannot use dbms_scheduler.

Find below the code :

--PACKAGE BODY--

create or replace
PACKAGE BODY PKG_TEST IS

  PROCEDURE PASSPORT_DC_1 IS

  l_jobno binary_integer;
  l_jobno1 binary_integer;

    BEGIN

       dbms_job.submit(l_jobno1, 'BEGIN SP_ABC(); END;',SYSDATE );
       DBMS_OUTPUT.PUT_LINE('SP_UPDATE_TIDCUMTL :' || l_jobno1);
       COMMIT;

       dbms_job.submit(l_jobno, 'BEGIN SP_XYZ(); END;',SYSDATE);
       DBMS_OUTPUT.PUT_LINE('SP_UPDATE_TIDPTDTL :' || l_jobno);
       COMMIT;

  END PASSPORT_DC_1;

--PACKAGE SPEC--

create or replace PACKAGE PKG_TEST AS

  PROCEDURE PASSPORT_DC_1;

END PKG_TEST;

If you just want to run the job now then don't add a date/time.
You can use this as a test case, run as an anonymous block to verify it works then modify your package.

DECLARE
l_jobno1 NUMBER;
BEGIN
dbms_job.submit(l_jobno1, 'SP_ABC();');
END;

You should definitely qualify the procedure name with the schema owner as in
XYZ.sp_ABC()


Retrieving What variables mean when retrieving view definitions? I'm working on a small script to transfer views over from one database to another. I can get the view definition by using select view_definition from information_schema.views where table_name = 'actor_info', however the result is

SELECT a.actor_id,
       a.first_name,
       a.last_name,
       group_concat(DISTINCT (((c.name)::text || ': '::text)
       || ( SELECT group_concat((f.title)::text) AS group_concat
              FROM ((film f
                     JOIN film_category fc_1 ON ((f.film_id = fc_1.film_id)))
                    JOIN film_actor fa_1 ON ((f.film_id = fa_1.film_id)))
             WHERE ((fc_1.category_id = c.category_id) AND (fa_1.actor_id = a.actor_id))
             GROUP BY fa_1.actor_id))) AS film_info
  FROM (((actor a
          LEFT JOIN film_actor fa ON ((a.actor_id = fa.actor_id)))
         LEFT JOIN film_category fc ON ((fa.film_id = fc.film_id)))
        LEFT JOIN category c ON ((fc.category_id = c.category_id)))
 GROUP BY a.actor_id, a.first_name, a.last_name;

I'm looking for a way to figure which tables a and c refer to. Is there a SQL statement that can be used?

a and c are table aliases; they're defined in the FROM clause of the SELECT statement:

...
FROM (((actor a
        LEFT JOIN film_actor fa ON ((a.actor_id = fa.actor_id)))
       LEFT JOIN film_category fc ON ((fa.film_id = fc.film_id)))
      LEFT JOIN category c ON ((fc.category_id = c.category_id)))
...

a refers to actor, c to category, etc.

It's a little hard to follow until you notice that there's a sub-query involved inside the outermost group_concat function.


Different sporadic SQL Server errors First question on this SA but I feel my problem is more Database related than code related.

I have multiple Delphi Datasnap servers connecting to SQL Server 2012.
The SQL server has multiple databases (500+) which the different Database servers connect to. The SQL Server and the Application servers run on different VM's. Different users connect to these application servers and open connections to the databases.

Since today we are getting different SQL errors on the servers, but no errors are seen on the SQL Server. These errors happen only sporadically for different users on the server in different databases. We are almost unable to reporoduce as this seems like a small % of the calls to the SQL server.

Example of errors we get are :

[FireDAC][Phys][ODBC][Microsoft][SQL Server Native Client 11.0]TCP Provider: An existing connection was forcibly closed by the remote host.
[FireDAC][Phys][ODBC][Microsoft][SQL Server Native Client 11.0]SMux Provider: Physical connection is not usable [xFFFFFFFF].
[FireDAC][Phys][ODBC][Microsoft][SQL Server Native Client 11.0]Unable to complete login process due to delay in opening server connection.
[FireDAC][Phys][ODBC][sqlncli11.dll] SQL_ERROR

Googling these errors gives some results on Chimney settings but most of the results are older than 2012 and most are for SQL Server 2008.

Are there any suggestions on possible causes? Any other info you need?

(More like a Delphi problem to me)

In my case, it had to do with a recent reinstallation of Access.
Access MDAC not beeing compatible with sqlncli11.dll, this mean I had to rebuild the whole application.

If I get it right, Delphi compiled only the units that I modified, using (I don't even know why, as I don't use Access) the current MDAC and it collided with the old DCU that were compiled using the old MDAC.


Run aspnet_regsql.exe from SQLCMD Post-Deployment Script I am trying to create a Post-Deployment script in SSDT that will include the creation of an ASP.NET Session State database to make sure everyone has this thing in place after deployment of my database project.

The following command creates the database for me if it's not yet in place:

C:\Windows\Microsoft.NET\Framework\v2.0.50727\aspnet_regsql.exe -ssadd -sstype c -S localhost -d ASPSTATE -E

At first I tried running in as part of a SQLCMD script in general. The following approach in SQLCMD mode:

:!!C:\Windows\Microsoft.NET\Framework\v2.0.50727\aspnet_regsql.exe -ssadd -sstype c -S localhost -d myStateDb -E

Then I tried to include it in the Post Deployment script basically the same way:

:!!C:\Windows\Microsoft.NET\Framework\v2.0.50727\aspnet_regsql.exe -ssadd -sstype c -S localhost -d TESTdb -E

This ends up with the Visual Studio compiler complaining about a backslash exactly in this line.

  "SQL72007: Thy syntax check failed. Syntax error close to '\' in the
  batch near: [The command above again]"

Since it's working in SSMS I do not understand why my Post-Deployment script keeps complaining?

SQL Server Management Studio (SSMS) only supports a subset of the SQLCMD commands, and SQL Server Data Tools (SSDT) only supports a subset of the SQLCMD commands that are allowed in SSMS. The [:]!! SQLCMD command, while supported by SSMS, is not supported in SSDT Pre-Deployment or Post-Deployment SQL scripts. This is most likely due to the large amount of confusion that would be caused by people not realizing that the command that is executed by the [:]!! SQLCMD command actually runs on the machine where SQLCMD is running, which is not guaranteed to be consistent across executions, and is not necessarily the same machine that SQL Server is running on.

For example, when doing development in Visual Studio on your local workstation or laptop and running this deployment, the command would execute on your local workstation or laptop. Then, when deploying to testing / QA, it would run on whatever machine does that deployment, which is typically not your local workstation. And then when deploying to Production, it would again run on the build server, which may or may not be the same server that handled the deployment to the QA server.

If you change your Post-Deployment to do just the following and then do a build:

:!! dir

then you would see this error:

  72006: Fatal scripting error: Command Execute is not supported.

You have three choices (that I can think of, at least):

Run aspnet_regsql.exe via xp_cmdshell (which will run under the security context of the Log On account associated with the SQLSERVER service). This option allows you to manage the call to aspnet_regsql.exe within the deployment SQL script and will get executed whenever you do a build.
Edit the "Post-build event" to call this same command line. This will allow aspnet_regsql.exe to be executed in the security context of whatever Windows / Active Directory Login runs the deployment. But, the execution of aspnet_regsql.exe is not in the SQL script, so the publish process needs to use SqlPackage.exe so that it knows to execute whatever commands are in the "Post-build event". But, this is at least still part of the SSDT publish process and will get executed when you do a build.
Handle this outside of the SSDT deployment by creating a CMD script that will be executed on the server running SQL Server, and that calls SQLCMD to run your deployment SQL script and then aspnet_regsql.exe. This option, however, is outside of the SSDT publish process and won't get executed when doing a build in Visual Studio.


Invalid date format in SSIS I am using BIDS to export data from Oracle to SQL server.

I am using OLEDB source and destination.

I have around 6 datetime fields in ORACLE out of which two fields give no problem while 4 fields give invalid date format issues.

These four fields either have nulls or have only date part in the datetime fields of ORACLE.

Any solution for this?

I have Tried all sorts of data conversions and insert nulls option.

You need to use derived column transformation and with help of expressions perform required conversion operation on original columns.

To replace null with default value use REPLACENULL(expression 1,expression 2) in derived column transformation.

REPLACENULL returns the value of second expression parameter if the value of first expression parameter is NULL; otherwise, returns the value of first expression.

REPLACENULL(SHIPPED_DATE, "1900-01-01")

Or with ISNULL

(DT_DBTIMESTAMP) (ISNULL(SHIPPED_DATE) ? “1900-01-01” : SHIPPED_DATE)

ISNULL([SHIPPED_DATE]) ? "UnknownDate" : [SHIPPED_DATE]

To deal with date part, you can use Concatenate and make into valid date-time format.

Say you have 01/07/2014 as original field value then make it to "01/07/2014 00:00:00"

OracleDateField + "00:00:00"

If you have to perform both operations at same time(checking nulls and dealing with date part ) then it's good to use script component rather than derived column transformation.


Who changed the size of the transaction log? Can I know, who and when changed size of database transation log?

Most likely you did. Most cases database files, including transaction log, are configured for auto-growth. Auth-growth enables the file to increase its size as needed, any time is needed. The trigger for growth can be any activity that requires log space. The activity that triggered the growth is most cases ordinary, normal activity. The true problem is why is the log growing. Most times is simply lack of log truncation, see Factors That Can Delay Log Truncation. Other time it can be a single event that generates a lot of log and requires growth. But most often is just bad maintenance (lack of truncation).

To shrink the log now, see How to Shrink the SQL Server log.

If the log was explicitly increased in size, the event is likely captured in the default administrative trace.


Is log shipping good for periodic synchronization? I have Postgresql 9.3 server on both my laptop and my desktop at home. I keep identical data on both system. Sometimes I work on the database on my laptop while other times I work on the database on the desktop. I want to make sure that the data and structure of both the laptop and desktop versions of the databases remain in sync. I don't need them to be hot swappable or anything, but just need to sync them once a day or once every two days.

So my question is whether log shipping is a good way to keep both databases in sync for periodic synchronizations? Or is this a bad choice since the laptop database cannot always access the desktop database from a remote location.

It is a creative way of thinking. If you know how to handle role exchanges (promoting to primary) and do this often, it could work but I am not sure it makes you happy. Especially not because there is a bug where WALL files are prematurely recycled which makes re establishing the old primary as a slave a pain.
(this is fixed in May 2020)
Since you want to be able to work on both(I assume read and write) you have a challenge. You can only have one writable db in a replication pair.
My choice - as a dba who does this kind f things regularly - would be to set up a little RDS instance in Amazon or similar so you can access the database always, if you have a network available.
It would be different if you only need writes to one db and the others only has reads. In that case: go ahead.


Find old linked servers on a restored copy of master/msdb db We moved our dbs to a new server and the drives on the old server are still available, but the SQL service is offline and cannot be started.

I want to find the linked servers that were registered on the old server. I tried restoring the master db under a different name on another SQL server. That's all fine, but when I tried to run

select * from newmaster.sys.servers

it actually looks in the actual master db of that server instead of what I specified?

How do I view the list of linked servers in this case?

The system catalogs (SYS objects) are stored in the resource DB. The resource DB is usually located at: <drive>:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\, i.e. the same location of the master DB. Yes, this is like the fifth system database.

Check this link on: Moving the master and Resource Databases to move your master and resource DB.


How can Update table column with the virtual column? I want to update repetitive columns, And I build virtual column for did this. I can "select" but can't "update"

Here is SQL Fiddle :

http://sqlfiddle.com/#!9/d09189/3

Also Code review :

I have this table :

CREATE TABLE game_log (
    user_id int,
    game_id int,
    day_number int,
    game_detail int,
    wallp_det int );

INSERT INTO game_log (user_id, game_id, day_number, game_detail, wallp_det)
VALUES (11,21, 1,12,14),
       (11,21, 2,18,24),
       (11,22, 3,20, 1),
       (11,22, 4,25,30),
       (11,23, 5, 5, 2),
       (12,21, 6,25,10),
       (12,22, 7,12,22),
       (12,22, 8,23, 2),
       (12,23, 9, 4,12),
       (13,21,10, 9, 8),
       (13,21,11,28,38);

I can "select" but can't "update" :

SET @s := 0;
select  sum(game_detail)  As ga_datail, user_id,game_id, @s := @s + 1 As day_num
from game_log
group by user_id,game_id;

After this, I will delete repetitive rows

what I have :

   user_id  | game_id |  day_number | game_detail |   wallp_det
    ------  |---------|-------------|-------------|--------
    11      | 21      |  1          | 12          |  14
    11      | 23      |  2          | 18          |  24
    11      | 22      |  3          | 20          |  1
    11      | 23      |  4          | 25          |  30
    11      | 23      |  5          | 5           |  2
    12      | 21      |  6          | 25          |  10
    12      | 22      |  7          | 12          |  22
    12      | 23      |  8          | 23          |  2
    12      | 23      |  9          | 4           |  12
    13      | 21      |  10         | 9           |  8
    13      | 21      |  11         | 28          |  38

-
what i want :

   user_id  | game_id |  day_number | game_detail |   wallp_det
    ------  |---------|-------------|-------------|--------
    11      | 21      |  1          | 12          |  14
    11      | 23      |  0          | 48          |  56
    11      | 22      |  3          | 20          |  1
    12      | 21      |  6          | 25          |  10
    12      | 22      |  7          | 12          |  22
    12      | 23      |  0          | 27          |  14
    13      | 21      |  0          | 37          |  46

Thanks in advance.

I solved it this way :

INSERT INTO game_log (game_detail,user_id,game_id,day_number)
select  sum(game_detail) As game_datail,user_id,game_id,0   from game_log group by user_id,game_id

--

DELETE FROM game_log where day_number != 0


Best practises for DB management What are the prevalent best practices for managing database of an application?

One option is to have an sql script to create the empty tables, and also to start with hardcoded options. (Say all locations, or profession types). During upgrade, you know whats in there and whats not, so on the server, run an sql script that adds columns and any new data.

Another is to do this programmatically, where everytime the server runs- it checks if the db exists, and if not, create it. And during addition of new columns- there would be code that would check if a certain column exists in this schema, and if not add it with a default value of my choice, and also any new data that should be needed.

My constraints are:

Should be blindly able to restore a backup if anything goes wrong.
Minimal manual intervention
I'm planning to run it 24/7
Under addition of tables and columns: By upgrading the SQL database first, and then the application code for corresponding fields.
Under removal of tables/columns: By downgrading the application first, and the SQL database.
I'm not using an ORM.

Am I missing things here that I should add to the constraints?
Please clarify your reasoning in relation to the answer.

Also, I hope that since I'm asking for a constrained answer along with sources and reasoning, this wouldn't count as a opinion based question.

EDIT: In Added constraint(3) in response to comment.

I'd have a few points built up over the years.

There is always risk involved in projects such as
this - for every extra 9, you can add a 0!

What I mean
but that is that in order to move from (say) 99% reliability
to 99.9%, you'll have to multiply your costs by 10.

Minimise, to the greatest extent possible, your web-facing data.
Always perform your upgrades at your least busy time.

DDL on a running server is difficult.

In order for upgrades to proceed smoothly - ALWAYS put field lists in your SQL, NEVER SELECT or INSERT more than you absolutely have to.

i.e. SELECT f1, f2, f3... FROM tab1.... (not SELECT * FROM tab1) and

INSERT INTO tab1 (f1, f2, f3) VALUES (not INSERT INTO tab1 VALUES(f1, f2...fn)

The best thing for your scenario IMHO is to have some sort of
replication scenario. Get to know your RDBMS's HA capacties
very well. Take a look at my answer here (it's for MySQL, but
the general principals will hold for any system).

Normally, Master is replicated to Slave.

At cutover time, Halt Slave. Upgrade the Slave. Old Slave becomes new Master. Old Master becomes new Slave.

Test, test and test again!

Good luck.


Restoring the database dump of a newer version to an older version of mongodb I find myself having to move a database between hosts but at the moment don't have the option of upgrading or downgrading any of the hosts.

I'm trying to find out if it is safe to do the following:

I have a running mongodb 3.0.15 instance which I've taken a dump from.
I've restored that to another mongo instance running version 3.4.4 without any issues.

Can I now take a dump of the data in 3.4.4 and restore it on 3.0.15? There has been no material change to the db besides the usual create/update & deletion of records while running on the surrogate host.

Assuming you aren't using any newer features or index/collection options from MongoDB 3.2+ that aren't supported in MongoDB 3.0, you should be able to restore the backup.

Before doing so, I would review the downgrade instructions in the MongoDB documentation to check for potential issues:

Downgrade MongoDB 3.4 to 3.2
Downgrade MongoDB from 3.2

The downgrade instructions assume a change of binaries rather than a mongodump backup, but the caveats on backward-incompatible features such as newer index and collection options still apply.

I suggest using the 3.4 versions of mongodump and mongorestore  to test your outcome in a development or staging environment before applying to a production deployment.


Database stored procedure with a "preview mode" A fairly common pattern in the database application I work with is a need to create a stored procedure for a report or utility that has a "preview mode". When such a procedure does updates, this parameter indicates that the results of the action should be returned, but the procedure should not actually perform the updates to the database.

One way to accomplish this is to simply write an if statement for the parameter, and have two complete code blocks; one of which does updates and returns data and the other just returns the data. But this is undesirable because of the code duplication and a relatively low degree of confidence that the preview data is actually an accurate reflection of what would happen with an update.

The following example attempts to leverage transaction savepoints and variables (which are not affected by transactions, in contrast to temp tables which are) to use just a single block of code for the preview mode as the live update mode.

Note: Transaction rollbacks are not an option since this procedure call may itself be nested in a transaction. This is tested on SQL Server 2012.

CREATE TABLE dbo.user_table (a int);
GO

CREATE PROCEDURE [dbo].[PREVIEW_EXAMPLE] (
  @preview char(1) = 'Y'
) AS

CREATE TABLE #dataset_to_return (a int);

BEGIN TRANSACTION; -- preview mode required infrastructure
  DECLARE @output_to_return TABLE (a int);
  SAVE TRANSACTION savepoint;

  -- do stuff here
  INSERT INTO dbo.user_table (a)
    OUTPUT inserted.a INTO @output_to_return (a)
    VALUES (42);

  -- catch preview mode
  IF @preview = 'Y'
    ROLLBACK TRANSACTION savepoint;

  -- save output to temp table if used for return data
  INSERT INTO #dataset_to_return (a)
  SELECT a FROM @output_to_return;
COMMIT TRANSACTION;

SELECT a AS proc_return_data FROM #dataset_to_return;
RETURN 0;
GO

-- Examples
EXEC dbo.PREVIEW_EXAMPLE @preview = 'Y';
SELECT a AS user_table_after_preview_mode FROM user_table;

EXEC dbo.PREVIEW_EXAMPLE @preview = 'N';
SELECT a AS user_table_after_live_mode FROM user_table;

-- Cleanup
DROP TABLE dbo.user_table;
DROP PROCEDURE dbo.PREVIEW_EXAMPLE;
GO

I'm looking for feedback on this code and design pattern, and/or if other solutions to the same problem exist in different formats.

There are several flaws to this approach:

The term "preview" can be quite misleading in most cases, depending on the nature of the data being operated on (and that changes from operation to operation). What is to ensure that the current data being operated on will be in that same state between the time the "preview" data is gathered and when the user comes back 15 minutes later -- after grabbing some coffee, stepping outside for a smoke, walking around the block, coming back in, and checking something on eBay -- and realizes that they didn't click the "OK" button to actually perform the operation and so finally clicks the button?

Do you have a time-limit on proceeding with the operation after the preview is generated? Or possibly a way to determine that the data is in the same state at modification time as it was at initial SELECT time?
This is a minor point as the example code could have been done hastily and not represent a true use-case, but why would there be a "Preview" for an INSERT operation? That could make sense when inserting multiple rows via something like INSERT...SELECT and there could be a variable number of rows inserted, but this doesn't make much sense for a singleton operation.

  this is undesirable because of ... a relatively low degree of confidence that the preview data is actually an accurate reflection of what would happen with an update.

Where exactly does this "low degree of confidence" come from? While it is possible to update a different number of rows than show up for a SELECT when multiple tables are JOINed and there is duplication of rows in a result set, that should not be an issue here. Any rows that should be affected by an UPDATE are selectable on their own. If there is a mismatch then you are doing the query incorrectly.

And those situations where there is duplication due to a JOINed table that matches multiple rows in the table that will be updated are not situations where a "Preview" would be generated. And if there is an occasion where this is the case, then it needs to be explained to the user that they are updated a subset of the report that is repeated within the report so that it does not appear to be an error if someone is only looking at the number of affected rows.
For the sake of completeness (even though the other answers mentioned this), you are not using the TRY...CATCH construct so could easily run into issues when nesting these calls (even if not using Save Points, and even if not using Transactions). Please see my answer to the following Question, here on DBA.SE, for a template that handles transactions across nested Stored Procedure calls:

Are we required to handle Transaction in C# Code as well as in stored procedure
EVEN IF the issues noted above were accounted for, there is still a critical flaw: for the short-period of time the operation is being performed (i.e. prior to the ROLLBACK), any dirty-read queries (queries using WITH (NOLOCK) or SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ) can grab data that isn't there a moment later. While anyone using dirty-read queries should already be aware of this and have accepted that possibility, operations such as this greatly increase the chances of introducing data anomalies that are very difficult to debug (meaning: how much time do you want to spend trying to find a problem that has no apparent direct cause?).
A pattern like this also degrades system performance by both increasing blocking by taking out more locks, and generating more Transaction Log activity. (I see now that @MartinSmith also mentioned these 2 issues in a comment on the Question.)

Additionally, if there are Triggers on the tables being modified, that could be quite a bit of additional processing (CPU and Physical/Logical reads) that is unnecessary. Triggers would also further increase the chances of data anomalies resulting from dirty reads.
Related to the point noted directly above -- increased locks -- the use of the Transaction increases the likelihood of running into deadlocks, especially if Triggers are involved.
A less severe issue that should relate only to the less-likely scenario of INSERT operations: the "Preview" data might not be the same as what is inserted with regards to column values determined by DEFAULT Constraints (Sequences / NEWID() / NEWSEQUENTIALID())  and IDENTITY.
There is no need for the additional overhead of writing the contents of the Table Variable into the Temporary Table. The ROLLBACK wouldn't affect the data in the Table Variable (which is why you said you were using Table Variables in the first place), so it would make more sense to simply SELECT FROM @output_to_return; at the end, and then don't even bother creating the Temporary Table.
Just in case this nuance of Save Points is not known (hard to tell from the example code as it only shows a single Stored Procedure): you need to use unique Save Point names so that the ROLLBACK {save_point_name} operation behaves as you are expecting it to. If you re-use the names, a ROLLBACK will roll-back the most recent Save Point of that name, which might not be at the same nesting level where the ROLLBACK is being called from. Please see the first example code block in the following answer to see this behavior in action: Transaction in a stored procedure

What this comes down to is:

Doing a "Preview" doesn't make a lot of sense for user-facing operations. I do this frequently for maintenance operations so that I can see what will be deleted / Garbage Collected if I proceed with the operation. I add an optional parameter called @TestMode and do an IF statement that either does a SELECT when @TestMode = 1 else it does the DELETE. I sometimes add the @TestMode parameter to Stored Procedures called by the application so that I (and others) can do simple testing without affecting the state of the data, but this parameter is never used by the application.
Just in case this was not clear from the top section of "issues":

If you do need/want a "Preview" / "Test" mode to see what should be affected if the DML statement were to be executed, then do NOT use Transactions (i.e. the BEGIN TRAN...ROLLBACK pattern) to accomplish this. It is a pattern that, at best, only really works on a single-user system, and not even a good idea in that situation.
Repeating the bulk of the query between the two branches of the IF statement does present a potential problem of needing to update both of them each time there is a change to make. However, differences between the two queries are usually easy enough to catch in a code review and easy to fix. On the other hand, problems such as state differences and dirty reads are much harder to find and fix. And the problem of decreased system performance is impossible to fix. We need to recognize and accept that SQL is not an Object-Oriented language, and encapsulation / reducing duplicated code was not a design-goal of SQL like it was with many other languages.

If the query is long / complex enough, you can encapsulate it in an Inline Table-Valued Function. Then you can do a simple SELECT * FROM dbo.MyTVF(params); for the "Preview" mode, and JOIN to the key value(s) for the "do it" mode. For example:

UPDATE tab
SET    tab.Col2 = tvf.ColB
       ...
FROM   dbo.Table tab
INNER JOIN dbo.MyTVF(params) tvf
        ON tvf.ColA = tab.Col1;

If this is a report scenario as you mentioned it could be, then running the initial report is the "Preview". If someone wants to change something they see on the report (a status perhaps), then that doesn't require an additional preview since the expectation is to change the currently displayed data.

If the operation is to perhaps change a bid amount by a certain % or business rule, then that can be handled in the presentation layer (JavaScript?).
If you really need to do a "Preview" for an end-user-facing operation, then you need to capture the state of the data first (maybe a hash of all of the fields in the result set for UPDATE operations or the key values for DELETE operations), and then, before performing the operation, compare the captured state info with the current info -- within a Transaction doing a HOLD lock on the table so that nothing changes after doing this comparison -- and if there are ANY difference, throw an error and do a ROLLBACK rather than proceed with the UPDATE or DELETE.

For detecting differences for UPDATE operations, an alternative to computing a hash on the relevant fields would be to add a column of type ROWVERSION. The value of a ROWVERSION datatype automatically changes every time there is a change to that row. If you had such a column, you would SELECT it along with the other "Preview" data, and then pass it along to the "sure, go ahead and do the update" step along with the key value(s) and value(s) to change. You would then compare those ROWVERSION values passed-in from the "Preview" with the current values (per each key), and only proceed with the UPDATE if ALL of the values matched. The benefit here is that you don't need to calculate a hash which has the potential, even if unlikely, for false-negatives, and takes some amount of time each and every time you do the SELECT. On the other hand, the ROWVERSION value is incremented automatically only when changed, so nothing you ever need to worry about. However, the ROWVERSION type is 8 bytes, which can add up when dealing with many tables and/or many rows.

There are pros and cons to each of these two methods for dealing with detecting inconsistent state related to UPDATE operations, so you would need to determine which method has more "pro" than "con" for your system. But in either case, you can avoid a delay between generating the Preview and doing the operation from causing behavior outside of the end-user's expectations.
If you are doing an end-user-facing "Preview" mode, then in addition to capturing the state of the records at select-time, passing along, and checking at modification-time, include a DATETIME for SelectTime and populate via GETDATE() or something similar. Pass that along to the app layer so that it can be passed back to the stored procedure (mostly likely as a single input parameter) so that it can be checked in the Stored Procedure. Then you can determine that IF the operation is not the "Preview" mode, then the @SelectTime value needs be no more than X minutes prior to the current value of GETDATE(). Maybe 2 minutes? 5 minutes? Most likely not more than 10 minutes. Throw an error if the DATEDIFF in MINUTES is over that threshold.


Bulk Data Loading and Transaction Log I'm currently working on a project which bulk import data from flat files (csv) about 18 different files each linking to a specific table through some stored procedure.

I followed the steps as  advised in Data Loading Performance guide.

The database is in BulkLogged recovery mode to minimize the logging, when executing the stored procedure below on a file containing 600000 rows I get an error

  Msg 9002, Level 17, State 4, Procedure SP_Import__DeclarationClearanceHistory_FromCSV, Line 34
  The transaction log for database  is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

(for testing purposes I do a full backup before starting the import).

Looking at the log_reuse_wait_desc I see the following:

  log_reuse_wait_desc CHECKPOINT. All other import get imported successfully.

Any input in solving this would be welcomed.

PROCEDURE [dbo].[SP_Import_DeclarationClearanceHistory_FromCSV]
    @FilePath [nvarchar](1000)
AS
BEGIN
    -- Creating a Temproary Table for importing the data from csv file.
    DBCC TRACEON(610)

    CREATE TABLE #DeclarationClearanceHistory
    (
          [ItemID] [int] IDENTITY(1, 1) NOT NULL ,
          [CMSDeclarationID] [bigint] NOT NULL ,
          [StatusCode] [nvarchar](10) NOT NULL ,
          [SubStatus] [nvarchar](10) NULL ,
          [DepartmentCode] [nvarchar](10) NULL ,
          [StartDate] [datetime] NULL ,
          [EndDate] [datetime] NULL ,
          PRIMARY KEY CLUSTERED ( [ItemID] ASC )
            WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
                   IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
                   ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY]
        )
    ON  [PRIMARY]

    -- Inserting all the from csv to temproary table using BULK INSERT
    EXEC ('BULK INSERT #DeclarationClearanceHistory
    FROM ''' + @FilePath + '''
    WITH ( FIELDTERMINATOR = ''<,>'', ROWTERMINATOR =''\n'', FIRSTROW = 2, KEEPIDENTITY, CODEPAGE = ''ACP'', ORDER = ''ITEMID ASC'' );') ;

    -- By using MERGE statement, inserting the record if not present and updating if exist.
    MERGE dbo.DeclarationClearanceHistory AS TargetTable                            -- Inserting or Updating the table.
        USING #DeclarationClearanceHistory AS SourceTable                           -- Records from the temproary table (records from csv file).
        ON ( TargetTable.ItemID = SourceTable.ItemID )      -- Defining condition to decide which records are alredy present
        WHEN NOT MATCHED BY TARGET
            THEN INSERT (
                          ItemID ,
                          CMSDeclarationID ,
                          StatusCode ,
                          SubStatus ,
                          DepartmentCode ,
                          StartDate ,
                          EndDate
                        )
               VALUES   ( SourceTable.ItemID ,
                          SourceTable.CMSDeclarationID ,
                          SourceTable.StatusCode ,
                          SourceTable.SubStatus ,
                          SourceTable.DepartmentCode ,
                          SourceTable.StartDate ,
                          SourceTable.EndDate
                        )
        WHEN MATCHED -- If  matched then UPDATE
            THEN UPDATE
               SET      TargetTable.ItemID = SourceTable.ItemID ,
                        TargetTable.CMSDeclarationID = SourceTable.CMSDeclarationID ,
                        TargetTable.StatusCode = SourceTable.StatusCode ,
                        TargetTable.SubStatus = SourceTable.SubStatus ,
                        TargetTable.DepartmentCode = SourceTable.DepartmentCode ,
                        TargetTable.StartDate = SourceTable.StartDate ,
                        TargetTable.EndDate = SourceTable.EndDate ;
DBCC TRACEOFF(610)
END

When you see CHECKPOINT as the log_reuse_wait_desc for that database, it is because no checkpoint has happened since the last time the log was truncated.

You can alleviate this issue by manually kicking off a CHECKPOINT command.

Supporting references:
Factors That Can Delay Log Truncation
Checkpoints and the Active Portion of the Log


Tracing/Logging of Sybase ASA Trying to get logging/tracing of Sybase 9.0.2.3396 (Adaptive Server Anywhere). I wish to log all the SQL executed against the database.

I tried RIBO and while I got it "working," I never saw anything captured. Port 5003 for listen, server: localhost, and Server Port 2638.

I also tried ODBC tracing on WinXP but with limited success. I got some "connection information" but never any actual transaction tracing.

Take a look at the -zr switch on the server, which turns on "request level logging". Start the server with -zr all -zo reqlog.txt and then look at reqlog.txt after some queries have been executed. It's fairly verbose but should list the SQL that's being executed.

Disclaimer: I work for Sybase in SQL Anywhere engineering.


Failed to start service, error 2. TNS-12560: TNS:protocol adapter error TNS-00530: Protocol adapter error i have start oracle services but its giving error:
windows could not start the
OracleOraDb11g_home1ClrAgent Service on local computer.
Error2: the system cannot find the file specified.

Also there are multiple services OracleORaDbHome1 and OracleOraDbhome2.

Check the tnsnames.ora file in below navigation directory:

Drive_Name:\OraHome_1\network\ADMIN

Check the file property with installed oracle service details

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10..)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = PLSExtProc)
    )
  )

Also check the tnslistener along with it's service.


Licensing standalone instance of SSIS Basic background:  I have an application on an app server (no MSSQL installed) that I want to allow to call an SSIS package.

One Solution:  One option seems to be that we can install just SSIS on the app server without needing to install SQL Server itself.  The app would be able to run a package while referencing the DB itself on a separate server.

Question:  Would this require licensing SQL Server on the app server, even though the core engine is not part of the installation?

Yes. Now my disclaimer is I'm not your licensing rep or Microsoft so you should check with them on questions.

But the verbiage in the various license guides is typically "A Server running SQL Server - or any of its core components (SSRS, SSAS, SSIS)".

So if you are using SSIS on a server standalone, by the licensing guidelines you are running one of the core components on another server and that should be licensed separately from any other machine running a core component.

Depending on the load of your SSIS server, I have typically found that in many installations, just running the SSIS server on an already licensed machine - be it your warehouse server or a staging server or even your main production server works.

When you get into complex SSIS workloads with lots of moving parts and pieces and high overhead, you may start considering a separate processing server that perhaps also contains the DB instance for your SSIS logging and reporting type of environment. I'd say that I have to consider that sort of separation for performance rarely lately, though I have in the past.

Microsoft publishes several detailed and overview license guides. This one here is an example for SQL Server 2012.


Tablediff utility output file encoding When the SQL Server tablediff utility generates a file with differences between source and destination, if SSIS executes the script file which contains Arabic letters, then those letters are inserted as symbols.

Is there any way to to specify the code page for the output file ??
Or any work around ??

Note : it only works correctly when I manually change the encoding of the file from (UTF-8 without BOM) to (UTF-8).

Thanks

If you can manually change the encoding (to one that includes the Byte Order Mark) and have it work such that no characters are lost, then the file itself is fine.

It seems that the tablediff utility does not allow for specifying the encoding type of the output file, so you are left with two options:

Configure SSIS to specify that script as being UTF-8 rather than relying upon the default encoding (which is probably ANSI). If that doesn't work, trying reading the script as "Unicode" (which is really UTF-16 Little Endian, which is how Windows & SQL Server store strings).
Find a command-line utility that can convert the file encoding to UTF-8 (either unspecified or with BOM). This is certainly not the preferred approach ;-).


Deploying and rolling back changes in an Availability Group database I'm looking for a solution or workflow. Here's how I used to do database deploys when using a stand-alone server:

Create a snapshot of the production database
Deploy changes
Verify that the deploy was successful
If the deploy was not successful, restore the database from the snapshot.

I would like to perform a similar process on a database in an Availability Group, but restoring from a backup or snapshot is not a desirable option because the database is fairly large and I'd have to take the database out of the AG first. Here's the ideal type of solution I am looking for:

Suspend data movement in the AG
Deploy changes to primary replica
Verify that the deploy was successful
If the deploy was not successful, rollback the primary replica to the LSN where we suspended data movement and restart synchronization.

Is there a practical way of doing this?

I would use BEGIN/ROLLBACK TRANSACTION, but this is harder to implement with the automated build process, and given that I would first like to inspect the results (from a different database connection) before committing or rolling back.

There's not really a practical method. I can really think of three ways of rolling back a failed deployment:

BEGIN \ ROLLBACK TRANSACTION: Normally, this is what I'd recommend - it's cleanest, and you wouldn't have to suspend data movement to do it. However, since you need to be able to view the changes from another machine, it's not really an option.
Rollback script: With this, you pre-script the commands required to return the database to the state it was in prior to the deployment. It's not a true rollback, and it may be impractical on a large database in some circumstances.
RESTORE DATABASE: As you mentioned, there are hassles for this on a large database in an AG.


Why is alert.log not located in path indicated by BACKGROUND_DUMP_DEST in oracle 12c (12.1.0) Just do this :
startup open;

Then
show parameter background_dump

The result of the show command on my machine is :
/ora01/app/oracle/product/12.1.0/db_1/rdbms/log

So when I go to that location, there is no alert log there.

Then I went to
$ORACLE_BASE/diag/rdbms/<db_name>/<oracle_sid>/trace
I found the alert log there.

So my question is why BACKGROUND_DUMP_DEST  not containing the proper path to the alert log ?

BACKGROUD_DUMP_DEST parameter specifies the path where debugging trace files for the background processes like PMON, DBWn etc.

  BACKGROUD_DUMP_DEST

  This parameter is ignored by the diagnosability infrastructure introduced in Oracle Database 11g Release 1 (11.1), which places trace and core files in a location controlled by the DIAGNOSTIC_DEST initialization parameter.

The alert logs, trace files are  found  in Automatic Diagnostic Repository (ADR) Home determined by DIAGNOSTIC_DEST.
<diagnostic_dest>/diag/rdbms/<dbname>/<instname>.

The alert log location is <diagnostic_dest>/diag/rdbms/<dbname>/<instname>/trace/.

  DIAGNOSTIC_DEST

  Derived from the value of ORACLE_BASE ($ORACLE_BASE). If the $ORACLE_BASE environment variable does not exist, then $ORACLE_HOME is used.

The following files are located under the ADR home directory:
Alert Logs
Trace Files
Core Files
Incident Files

Reference:DIAGNOSTIC _DEST


Why does query runtime dramatically increase when made into subquery? I'm working on a query that selects counts from 2 servers and joins those together.  Here is what I have:

SELECT
    a.NAME
    , SUM(ISNULL(b.ttl,0)) AS [Total]
FROM TableFunction(@user, DEFAULT) a
-- Selecting NAME from Table1 takes <1s
LEFT JOIN (
    -- Sub-Query 1
    SELECT
        COUNT(*) ttl
        , NAME
    FROM Table2 c
    WHERE active=1
    GROUP BY NAME
    -- Sub-Query 1 (< 1s, 662 rows)
    UNION ALL
    -- Sub-Query 2
    SELECT
        COUNT(*) ttl
        , jt.NAME
    FROM OtherServer.dbase.dbo.Table3 d
    INNER JOIN OtherServer.dbase.dbo.JoiningTable jt ON d.ID=jt.ID
    WHERE ISNULL(d.DELETED,0)=0
    GROUP BY jt.NAME
    -- Sub-Query 2 (7s, 5576 rows)
    -- Full Sub-Query [1 UNION ALL 2] (7s, 6238 rows)
) b ON b.NAME=a.NAME
GROUP BY a.NAME
--Full query (3:44, 91 rows)

The Execution plan suggests that the problem comes from the remote query (Sub-Grid 2).

How can I improve the runtime of this?  Why does a 7 second sub-query take over 3 minutes when joined on a basically instant query?

UPDATE:

After some commenting out random things in hopes of finding something useful, it appears to be specifically the WHERE ISNULL(d.DELETED,0)=0 bit.  Removing that line improves query to 4s.  Why would this help at all?

UPDATE 2:

Playing around more, it seems the fact that the first table is a table-valued function may have been important.  Selecting it's result table into a temp table and using that temp table brings the query down to 8s.  The table-valued function code is:

--Inputs
@user varchar(50)
, @includeDeleted BIT = 0

--Function
RETURNS @tbl table (ID int
  , NAME varchar(max)
  , DESCRIPTION varchar(max))
AS
BEGIN
INSERT INTO @tbl (ID, NAME, DESCRIPTION) (
    select jt.ID, jt.NAME, jt.DESCRIPTION
    from OtherServer.dbase.dbo.JoiningTable jt
    where jt.USER = (select top 1 USER
                            from OtherServer.dbase.dbo.USERS
                            where user_name = @user)
    and 1=CASE WHEN @includeDeleted=1 THEN 1 ELSE
        CASE WHEN ISNULL(jt.Deleted, 0) <> 1 THEN 1 ELSE 0 END
    END
)

INSERT INTO @tbl (ID, NAME, DESCRIPTION) (
    SELECT ID, name, ''
    FROM RandomTable rt
    INNER JOIN RandomJoinTable rjt ON rjt.ID = rt.ID AND rjt.user = @user
    LEFT JOIN @tbl t ON t.ID = rt.ID
    WHERE 1=CASE WHEN @includeDeleted=1 THEN 1 ELSE
        CASE WHEN ISNULL(rt.Deleted,0)=0 THEN 1 ELSE 0 END
    END
    AND t.Name IS NULL
)

RETURN

Querying across databases introduces a lot of uncertainty to the query optimizer because it doesn't know much about the other end. For example, if you try to join two tables, it has no idea how big the other table is (10 rows? 1 million?), what is indexed, or how the data is organized. Should it try to use an index to get 10 rows or a full scan for 1 million?

Generally, what you want to do is minimize the amount of interaction or joining that needs to happen between the two. Is there a filter you can put on JoiningTable to reduce the number of rows? Can you structure it into two CTEs that only pull the results together at the end?


Profiling a PostgreSQL function if I use

explain (analyze true, verbose true, costs true, buffers true)
select * from mystoredprocedurefunction(arg1, arg2);

I do not get any information about what the server is actually doing inside my sp.  Instead I get 5 rows describing that I invoked a function and that it took a long time.  How do I look into my stored procedure and see what is going on?  I have something very inefficient occurring.

Look into the module auto-explain.

We had a very similar question some time ago, it should be of help:
Postgres query plan of a UDF invocation written in pgpsql


Stored procedure getting delayed more Stored procedure and it is getting delayed. Taking nearly more than half an hour if I call the stored procedure in MySQL command line client. This procedure is to update values in two tables

drop procedure IF EXISTS ALTERTABLEVALUES;
delimiter $$

CREATE PROCEDURE ALTERTABLEVALUES()
BEGIN
DECLARE finished INT DEFAULT 1;
DECLARE total_count INT DEFAULT 1;
DECLARE val varchar(15);
DECLARE i INT DEFAULT 1;
DECLARE table_num INT;
DECLARE table_tab varchar(5);
DECLARE number_cursor CURSOR FOR select outpassNo,tab from tempnumbers;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 0;

drop temporary table if exists tempnumbers;
CREATE TEMPORARY TABLE tempnumbers as
    select  num,'temp' as tab
        from  table1
        where  month(inTime)=2
          and  year(inTime)=2018
        union
     select  num,'vary' as tab
        from  table2
        where  month(inTime)=2
          and  year(inTime)=2018
        order by  outpassNo;

SET val = (select num from tempnumbers limit 1);
SET total_count = (select count(*) from tempnumbers);

OPEN number_cursor;
get_values: LOOP
FETCH number_cursor INTO table_num,table_tab;
IF finished = 0 THEN
 LEAVE get_values;
 END IF;
IF table_tab = 'temp' THEN
    update table1 set table1.num= val where table1.num= table_num;
ELSE
    update table2 set table2.num= val where table2.num = table_num;
END IF;
SET val = val +1;
END LOOP get_values;
CLOSE number_cursor;
DROP TEMPORARY TABLE tempnumbers;
END;
$$
delimiter ;

table1 has the newly inserted data and table2 has the processing values came from table1. A business need is there, where few data from table2 are moved to some other table on a monthly basis, based on some criteria. The expectation is to reorder the numbers column of table1 and table2 as a sequence with the missing values of table2.

Can anyone help on handling resultset without a cursor in stored procedure?

since you are going back to a temporary table and updating rows one by one, you should put an index on the table so the updates go faster. alter table tempnumbers add index(num);


Trigger Performance : Is Using Trigger is a good way? We are using SQL Server 2008 for a j2ee web application. I need to maintains the audits for updates for each column with their previous value and new value on one of my table which is very heavy, around 100 columns and 10K records and increasing rapidly on daily basis. Around 200 users work with this application and updates also occur very frequently.
I know handling it via code will require very frequent connections to database.
I want to know which is a better approach for doing this. Is handling it through code will provide a better performance than using SQL Server trigger for that.

Personally, I would use triggers for auditing.

If they fail, then the main DML gets rolled back
(same) The main update requires the DML to succeed
A table will typically have several DML paths
You may not be able to use code
You will have direct table update that bypass code at some point

Ok, so we'll use triggers. Some notes

Ensure they work for multiple rows: no CURSORs or loops
Use them only for auditing: nothing else (no, say, sending emails)
Ensure you use TRY/CATCH in the triggers: this massively improves the behaviour on rollbacks in triggers
CASCADE foreign keys will cause child table triggers to fire: Beware nesting and recursion and don't use CASCADEs if possible

This last list also addresses sonme points in @NevilleK's answer. Triggers are one tool to use: use wisely and when appropriate and correctly.

600 updates per hour is trivial, not a problem at all for triggers (subject to cursors, loops, sending email and other stupid stuff)


Postgres partition tables 1 million rows I have a 1 million rows in one of my table and expect it grow to 3-4M in next 1 or 2 years.

There is lot of averages and sum are run on the date column for this row.

I'm planning to partition the tables to make the query run faster..

Questions:

Is it ok to partition table that just has 1 million rows.
I have 10 columns in the table but there are 2 columns that are text which stores around 2000 bytes of data.
How many rows Postgres can handle without partitions.

Your database is small and doesn't require partitioning.

A quick guesstimate gives storage requirements: two 2,000 byte columns times 1M rows is 4GB; times 3-4M rows is 12-16GB. A proper calculation would include a fudge factor for the other columns, indices, and other overheads, but it's still obviously an amount that fits in RAM on anything but the most crusty of servers.

So to answer your questions:

You can partition a 1M row table, but it's not worth the effort with PostgreSQL. (PostgreSQL is not MySQL.)
This is not a question!
PostgreSQL's "about" page states there is no limit on the number of rows, but a table cannot exceed 32TB.


Delete rows where matched value is twice as much I'm trying to match two tables with two columns, table1 and table2.
Each has id and price columns.

I want the query to match each id and compare the price, then delete the row if the price in the second table is twice as much as the other one.

My query up to now ( does not work ):

include ('go.php');
$sql = "delete
from table1
where table1.id IN
    (select table2.id where table1.price > table2.price*2)"
or die(mysqli_error($sql));

Anyone have any suggestions?

If you want to DELETE all the information in table1.*:

DELETE table1.*
FROM test.table1
JOIN test.table2 ON (table2.id = table1.id)
WHERE table1.price < (table2.price*2);

This will DELETE all rows from table1 that match with the WHERE criteria. If you want to DELETE all the rows that match against the WHERE criteria in table2, just replace DELETE table1.* to DELETE table2.*.

As the MySQL's documentation says:

Multi-Table Deletes

  You can specify multiple tables in a DELETE
  statement to delete rows from one or more tables depending on the
  condition in the WHERE clause. You cannot use ORDER BY or LIMIT in a
  multiple-table DELETE. The table_references clause lists the tables
  involved in the join, as described in Section 13.2.9.2, “JOIN Syntax”.

  For the first multiple-table syntax, only matching rows from the
  tables listed before the FROM clause are deleted. For the second
  multiple-table syntax, only matching rows from the tables listed in
  the FROM clause (before the USING clause) are deleted. The effect is
  that you can delete rows from many tables at the same time and have
  additional tables that are used only for searching:

 DELETE t1, t2 FROM t1 INNER JOIN t2 INNER JOIN t3 WHERE t1.id=t2.id
 AND t2.id=t3.id;

  Or:

 DELETE FROM t1, t2 USING t1 INNER JOIN t2 INNER JOIN t3 WHERE
 t1.id=t2.id AND t2.id=t3.id;

  If you use a multiple-table DELETE statement involving InnoDB tables
  for which there are foreign key constraints, the MySQL optimizer might
  process tables in an order that differs from that of their
  parent/child relationship. In this case, the statement fails and rolls
  back. Instead, you should delete from a single table and rely on the
  ON DELETE capabilities that InnoDB provides to cause the other tables
  to be modified accordingly.

  Note If you declare an alias for a table, you must use the alias when
  referring to the table:

 DELETE t1 FROM test AS t1, test2 WHERE ...

  Table aliases in a multiple-table DELETE should be declared only in
  the table_references part of the statement. Elsewhere, alias
  references are permitted but not alias declarations.


Change SQL Server 2000 from Active/Active to Active/Passive I know it's cringe-worthy to use this term, but I have a SQL Server 2000 cluster with one clusetered instance running on it with multiple resources running on one of the two servers in the cluster all the time.

For legacy licensing purposes, we'd like to change this cluster from "active/active" to "active/passive" to save on two Enterprise licenses - they only run on one server anyways.

Microsoft Premier support politely told to me to fly a kite.  Does anyone have any links or resources on how to do this?
Any help would be appreciated

From a licensing perspective, I don't want to pay for 4 Enterprise SQL Server licenses. I want everything running on one node at a time so I only have to pay for 2 Enterprise SQL Server licenses.

You can review this MSSQLTip to get more information on what active/active and active/passive mean in regards to SQL Server Clusters:
SQL Server Clustering Active vs Passive

In your situation, and based on your comments, the only thing you need to do is configure the dependencies for your resources. So if I have a cluster resource for the the SQL Server Cluster Name and it fails over, then all of the dependent resources will follow suite.

I have not messed with or ever configured a SQL Server 2000 cluster so I do not know what how this was done during installation. I am aware that starting with SQL Server 2005 the installation did more of this for you: Failover cluster resource dependencies in SQL Server


Minimum permissions required to run sp_Blitz I need to run sp_Blitz and a bunch of DMV queries from Glenn Berry's Dr DMV set for SQL 2016. The documentation for sp_Blitz on BrentOzar.com states "Sysadmin permissions. sp_Blitz® checks a lot of system-level diagnostic data."

Is sysadmin really the minimum required permission level or has anybody been able to run this with a more restrictive permission level?

Not sure that I am going to get away with asking for sysadmin privilege on a client's production server.

Yes, it requires sa permissions. But since it's a stored procedure, you can get permissions via a certificate

See the section How to Grant Permissions to Non-DBAs

USE master;
GO
CREATE CERTIFICATE sp_BlitzFirst_cert
ENCRYPTION BY PASSWORD = '5OClockSomewhere'
WITH SUBJECT = 'Certificate for sp_BlitzFirst',
START_DATE = '20130711', EXPIRY_DATE = '21000101';
GO
CREATE LOGIN sp_BlitzFirst_login FROM CERTIFICATE sp_BlitzFirst_cert;
GO
CREATE USER sp_BlitzFirst_login FROM CERTIFICATE sp_BlitzFirst_cert;
GO
GRANT EXECUTE ON dbo.sp_BlitzFirst TO sp_BlitzFirst_login;
GO
GRANT CONTROL SERVER TO sp_BlitzFirst_login;
GO
ADD SIGNATURE TO sp_BlitzFirst BY CERTIFICATE sp_BlitzFirst_cert
WITH PASSWORD = '5OClockSomewhere';
GO
GRANT EXECUTE ON dbo.sp_BlitzFirst TO [public];
GO

Then whenever you update the proc, you’ll need to reassign the permissions by adding the signature again:

ADD SIGNATURE TO sp_BlitzFirst BY CERTIFICATE sp_BlitzFirst_cert
WITH PASSWORD = 'Get lucky';
GO
GRANT EXECUTE ON dbo.sp_BlitzFirst TO [public];
GO

Hope this helps!


session id null in sys.dm_os_tasks We are seeing parallelism causing worker thread shortage in SQL,while investigating we found session id is null for some sessions,Below is the query used.What does this session id null means ?

select session_id,count(*) as 'totalthreads' from sys.dm_os_tasks
group by session_id
having count(*)>1
order by totalthreads desc

Output varies as below:

The session is sleeping if the session_id value of sys.dm_os_tasks is NULL. This is stated in the link above under the Examples section: Associating Session IDs with Windows threads.

  The following query does not return information for sessions that are
  sleeping.


SMO, Powershell, & Failed Logins Background

At my current organization, we've got a SQL Agent Job running a Powershell script, originally based on Script All Server Level Objects to Recreate SQL Server.

This job writes a bunch of scripts that can be used in the event we need to do disaster recovery.
Our version queries a separate database that lists out the servers that should be checked.
Prior to this job, I hadn't used Powershell though I've had other scripting experience (i.e. Python).

We also have all of our servers setup, using the SQL Server Agent, to email the DBAs when we have failed logins.
One of our development servers has a couple of databases on it that are taken offline for some unknown (to me) business purpose.
When our SQL Agent Job runs, it always generates failed login alerts when it comes to those offline databases on that one development server. I've been asked to see if there's someway to have the script skip checking databases that are offline to prevent these false alerts.
I've traced the source of the alerts back to the function below. I believe this bit, if($database.Status -eq 'Normal') was intended to make sure the database is online but it doesn't work.
Based on some other reading online, I tried replacing if($database.Status -eq 'Normal') with if ($database.IsAccessible), but I still got the failed login alert email when running the script.

#Function to write out Database scripts
function ScriptOutDBObjects($serverObject, $objectType)
{
    foreach ($database in $srv.Databases)
    {
        if($database.Status -eq 'Normal')
        {
            $objectPath = $scriptPath + $objectType +'\'
            if (!(Test-Path -Path $objectPath))
            {
                New-Item -ItemType Directory -Path $objectPath
            }
            $scriptingOptions.FileName = $objectPath + $database.Name.Replace(':','').Replace('\', '_') +'.sql'
            $database.Script($scriptingOptions)
        }
    }
}

The job is using the Powershell type, though I get the same results when running the code from the Powershell ISE on my local computer testing things out.

Question

Will Microsoft.SqlServer.Management.Smo always try to open the database? If so, is there some flag or something I can pass it to make it not do so?

The answer to almost all questions about Powershell/SMO is to use SMOs object model less, and TSQL more.  EG

$dbs = Invoke-Sqlcmd "select name from sys.databases where state_desc = 'ONLINE'"

foreach ($db in $dbs)
{
  $dbName = $db.Name
  #. . .
}


Limit of Generating Script for Schema Only from SQL Server 2012 to SQL Server 2008R2 Is there a limit on this method? I've tried a 35GB Database and it works, but my question is how about if my Database is like 100-150GB? Will I encounter some error? Or it will work smoothly but a little bit slow because of a large database size?

I'm using the method given by @Kin here

Thanks!

Schema generation will totally depend on the amount of objects (tables, SPs, triggers, indexes, etc) in database.

Schema is just a skeleton of the database without data.

If you are concerned about using SSMS GUI, you can use PowerShell - which will definitely scale better - and can be automated easily as compared to SSMS GUI.

I have tried scripting out schema of 1TB data warehouse database without any issues (as I said previously, the more objects you have, the bigger script it will be - and slower too (better to use PowerShell)).


Finding Count of Occurrences & Time Gap between a Timeline in MySQL I have a Table, like the below, which has a timeline and values (like YES/NO) that are coming from different sensors. How do I do the following:

How many times there has a been a flip-flops (for YES to NO or NO to YES), in the below example it has happened 2 times.

1 , A0001, NO,  2017-06-14 02:03:26.0
2 , A0001, NO,  2017-06-14 02:03:26.0
3 , A0001, YES, 2017-06-14 02:03:26.0
4 , A0001, NO,  2017-06-14 02:03:26.0
5 , A0001, YES, 2017-06-14 02:03:26.0
6 , A0001, NO , 2017-06-14 02:03:26.0

Using Sql Server,

declare @t table(id int,col1 varchar(30), col2 varchar(30),col3 datetime)
insert into @t (id,col1,col2,col3) VALUES
(1 , 'A0001' , 'NO',  '2017-06-14 02:03:26.0')
,(2 , 'A0001' , 'NO', '2017-06-15 02:03:26.0')
,(3 , 'A0001' , 'YES','2017-06-16 02:03:26.0')
,(4 , 'A0001' , 'NO', '2017-06-17 02:03:26.0')
,(5 , 'A0001' , 'YES','2017-06-18 02:03:26.0')
,(6 , 'A0001',  'NO' ,'2017-06-19 02:03:26.0')

;With CTE1 as
(
select *
,ROW_NUMBER()over(PARTITION by col1 order by col3)rn from @t
)

, cte
AS (
    SELECT
        rn
        ,col1
        ,col2
        ,col3
        ,0 changecount
    FROM cte1
    where rn=1

    UNION ALL

    SELECT
        t.rn
        ,t.col1
        ,t.col2
        ,t.col3
        ,CASE
            WHEN t.col2 <> t1.col2
                THEN changecount + 1
            ELSE changecount
            END
    FROM cte1 t
    INNER JOIN cte t1 ON t.col1 = t1.col1
        AND t.rn = t1.rn + 1
    )
SELECT *
FROM cte


Stored Procedure as Create Table proxy? With SQL Server it was my understanding, I thought, that you could elevate a users permissions with specific constraints.

For example, you need a user to create a specific table object in a specific schema. Rather than giving them wholesale create table permission, you could wrap the create table in a stored procedure and give them access to only that. In this way they would be constrained to only creating the object as specified within the procedure and not have any direct create table permission on the database... at least I know it works this way with Select, Insert, Update, Delete. The Create Table statement in the Stored Procedure throws the Permission Denied error.

Am I mistaken or just doing something wrong?

Yes, it should be working how you think.  Below is an example proving this:

-- create the dummy login
create login TestLogin1
with password = 'p@$$w0rd';
go

use TestDB;
go

-- create the test user
create user TestUser1
for login TestLogin1;
go

-- create the proc to create a table
create procedure dbo.CreateTableProc
as

    create table dbo.SomeTestTable(id int);

go

-- give TestUser1 perms to execute CreateTableProc
grant execute
on dbo.CreateTableProc
to TestUser1;
go

-- execute CreateTableProc under the security context of TestUser1
execute as user = 'TestUser1';
go

exec dbo.CreateTableProc;
go

revert;
go
-- unsuccessful, permission denied

  Msg 262, Level 14, State 1, Procedure CreateTableProc, Line 4
  CREATE TABLE permission denied in database 'TestDB'.

-- alter the proc to execute under my security context (with CREATE TABLE perms)
alter procedure dbo.CreateTableProc
    with execute as owner
as

    create table dbo.SomeTestTable(id int);

go

-- execute CreateTableProc under the security context of TestUser1
execute as user = 'TestUser1';
go

exec dbo.CreateTableProc;
go

revert;
go
-- successful, table created

  Command(s) completed successfully.

In this example, TestUser1 does not have the permissions to create the table.  We see that when the original version of CreateTableProc is called, as the CREATE TABLE DDL executes under the security context of TestUser1.  But then by modifying the CreateTableProc stored procedure definition and including the WITH EXECUTE AS OWNER clause, now TestUser1 can successfully call the proc and create a table because now the CREATE TABLE DDL executes under the security context of the owner (my database user, in the db_owner role).


Inheritance and foreign keys in Postgres This is about using inheritance and foreign keys in Postgresql databases.
Consider the following simplistic example whose structure is based on what I am building at the moment (but the specifics were contrived in realtime just for this question, so please excuse any shortcomings!):

Parent table1: Person (columns: ID, Name). Child tables: Man, Woman.
Parent table2: Relationship (columns: ID, Partner1 and Partner2). Child
tables: Gay, Lesbian.

Each table has a primary key set on the column ID.
The table Relationship has two foreign keys set on columns Partner1 and Partner2 which reference the table Person (column ID).
The (inherited) tables Gay and Lesbian also need to have foreign keys set on their Partner1 and Partner2 columns. The question is whether these foreign keys should reference the parent table Person, or whether they should reference (as appropriate) the child tables Man and Woman.
The questions comes up because, as stated in the manual for v9.6, section 5.9.1 :

Caveats
... A serious limitation of the inheritance feature is that indexes
(including unique constraints) and foreign key constraints only apply
to single tables, not to their inheritance children ...... These
deficiencies will probably be fixed in some future release ...

To me, this (the fact that foreign keys do not apply across inherited tables and must be done separately) is a feature, and not a limitation. And a very useful feature too, as can be seen in the case of the aforementioned example:
When the child table Lesbian references the child table Woman (instead of the parent table Person), it is very easy to prevent errors of the sort where there's a lesbian relationship between two men!

Of course, constraints can very well be imposed to achieve this, but it seems to me as though what I wrote above is a more elegant way of doing things. But I am also concerned about what the manual states towards the end - that the development team sees this as a problem, and might get rid of it. So I am also worried if my design would totally break after a future upgrade.
Any tips on the design above and suggestions for alternate ways would be most appreciated.
I would also be very grateful if there's someone from the Postgres dev team lurking around here, and is kind enough to comment.

Parent table1: Person (columns: ID, Name). Child tables: Man, Woman.
Parent table2: Relationship (columns: ID, Partner1 and Partner2). Child tables: Gay, Lesbian.

I'm staunchly against the inheritance modeled being used by end users, but even here this isn't a valid use case of it. Your gender isn't a child table. It's an attribute on the table. The same can be said of your relationship.

Just add a column, gender and add another column relationship

  To me, this (the fact that foreign keys do not apply across inherited tables and must be done separately) is a feature, and not a limitation. And a very useful feature too, as can be seen in the case of the aforementioned example: When the child table Lesbian references the child table Woman (instead of the parent table Person), it is very easy to prevent errors of the sort where there's a lesbian relationship between two men!

I think is going to devolve into a political question but if people's gender can't change (horrible assumption) then the relationship is known between the two of them by the gender of the people. Why would you even want an attribute on the relationship table. The class gay or lesbian would be inferred from the genders of the participants. Yes if you put it there you can have an error where there is a lesbian relationship between two men, but that's an error introduced because your data should be inferred.

Which of these two seems more logical?

f(p1,p2) = class of relationship
f(p1,p2,relationship_class) = class of relationship

See also

Using table inheritance instead of mapping tables


Using a CTE in IF EXISTS Query Is it possible to do something similar to the following in SQL Server 2012?

IF EXISTS (
    WITH DATA AS (
        SELECT *,
        ROW_NUMBER() OVER(PARTITION BY column ORDER BY Column) AS rn
        FROM table )
    SELECT *
    FROM DATA
    WHERE rn = 2 )
BEGIN
...
END

I tried using this syntax and received an error. If this is not possible, would using a temp table be the best way to accomplish this?

A CTE can't be used as a subquery. One workaround would be:

IF EXISTS
(
  SELECT 1 FROM
  (
    SELECT ROW_NUMBER() OVER(PARTITION BY column ORDER BY Column) AS rn
    FROM table
  ) AS DATA
  WHERE rn = 2
)
BEGIN
  ...
END

Another would be:

IF EXISTS (SELECT 1 FROM dbo.table GROUP BY column HAVING COUNT(*) > 1)
BEGIN
  ...
END

Even if your proposed syntax were valid, the EXISTS wouldn't short circuit in that case anyway, I would think (and I suspect that is why you'd want to use it), since the window function must materialize over the full set before rn could be filtered.


How to identify overflow rows in DB2 LUW Situation: DB2 LUW database with several tables reporting overflow.

We are attempting to determine the cause of the overflow. The tables in question do NOT have varchar columns. Million+ rows are inserted per month. Updates to rows are mostly limited to changing a single column from a value of 0 to 1.  Tables are compressed. Clustered index.

Is anyone aware of a DB2 for LUW utility that can be used to identify the specific rows which have overflowed? We are hoping that by identifying the rows we can uncover the cause of the overflow.

Currently there are two causes being entertained - the combination of compression/update or the presence of the clustered index.

You are likely correct to suspect compression as the cause of overflows. Having a clustered index by itself does not cause row movement outside of a reorg.

You can use the db2dart (Database Analysis and Reporting Tool) utility to dump formatted table pages; the "Record Type" field will identify which records have overflown. For example, run

db2dart yourdb /dd /tsi 2 /tn YOURTABLE /ps 0 /np 0 /v n

to dump all pages (/ps 0 /np 0) from the table YOURTABLE (note the uppercase name) in tablespace 2.

Overflown record will appear like this:

        Slot 203:

           Offset Location = 1580  (x62C)
           Record Length = 12  (xC)

           Record Type = Pointer Record (tombstoned) (PUNC)

               Overflow Record Location:  Page 10  Slot 33

with their actual data in the new location:

        Slot 33:

           Offset Location = 3528  (xDC8)
           Record Length = 26  (x1A)

           OverFlow Record with Back Pointer

               Backpointer Location:  Page 7  Slot 203

           Compressed Record:  Row_Compressed

           Actual Record Length = 52  (x34)

           Record Type = Table Data Record (FIXEDVAR) (PUNC)

           Record Flags = 0

You can use the verbose flag (/v y) to include the actual row data in the report. For a large table you probably want to select a subset of pages by specifying a sensible value for /np.

Don't forget to FLUSH BUFFERPOOL ALL before running db2dart on an active database, otherwise the data on disk, where the utility reads them, may not reflect changes still in the bufferpool.


First order by importance then group by section In this table scheme http://sqlfiddle.com/#!9/101a9c/9/0

id  section     rev     importance
2   1           b       6
1   1           a       5
3   2           c       9
5   3           e       6
4   3           d       5

i want to order by **importance** then group by **section**

so i tried to use GROUP BY docs.section

SELECT docs.id, docs.section, docs.rev, (
  SELECT SUM(docs_importance.importance)
  FROM docs_importance
  WHERE docs_importance.doc_id = docs.id
) AS importance
FROM docs
GROUP BY docs.section    ---GROUPING
ORDER BY docs.section, importance DESC

but got this result:

id  section     rev     importance
1   1           a       5
3   2           c       9
4   3           d       5

but the result i want is supposed to be

id  section     rev     importance
2   1           b       6
3   2           c       9
5   3           e       6

i used this query

SELECT t.id, t.section, t.rev, t.importance FROM (
   SELECT docs.id, docs.section, docs.rev, (SELECT SUM(docs_importance.importance) FROM docs_importance WHERE docs_importance.doc_id = docs.id) AS importance
   FROM docs
   GROUP BY docs.id
   ORDER BY importance DESC
) AS t
GROUP BY t.section

where i first made a subquery as a temporary table then ordered it by importance then grouped the outer query by section to get the final result.