Error while Running query in postgreSQL from sql server 2016

The below query runs well in sql server 2016:

 select ResellerId, vCompanyName ,x.*  from wlt_tblReseller AS main  outer apply ( select (count (ipkReportTypeId)) AS "count", vReportTypeName                 from wlt_tblReseller _all                inner join wlt_tblClient clients  on clients.ifkParentResellerId = _all.ResellerId               inner join wlt_tblReport_CompanyMaster reporslogs on reporslogs.ifkCompanyId = clients.ClientId               inner join wlt_tblReports_TypeMaster rpt_types on rpt_types.ipkReportTypeId = reporslogs.ifkReportTypeId                where RootResellerId = main.ResellerId                  and rpt_types.bStatus =1                  and bIsStatic =1                  and vReportTypeName  is not null               group by ipkReportTypeId,vReportTypeName             ) AS x  WHERE IsMiniReseller = 0    and ResellerId <> 1     and vReportTypeName is not null  order by  vCompanyName desc  

But when I take it to postgreSQL and change outer apply to LEFT JOIN LATERAL,it does not run and produces the following error:

ERROR: syntax error at or near "WHERE" LINE 9: )AS x WHERE IsMiniReseller = 0 and ResellerId <> 1 and v… SQL state: 42601 Character: 649

What could I be missing?Any help will be much appreciated.

Regards Chris.

SPN for Availability group(AG) listener for SQL Server 2016

Requesting some inputs..

We have a 2 node SQL 2016 Availability group configured and apps currently use primary replica for connection. We are now planning to use listener instead. Questions is, how to make sure we are using kerberos authentication for application connectiions?. Is it mandatory to manually register SPN for the listener? or is it automatically created?. is this a one time activity?. How to make sure SPN is registered automatically for the listener when the AG restarts or failover to second node?. Appreciate all the responses.

SQL Server Transactional Replication between SQL server 2019 Standard and SQL server 2016 Web Edition

We have SQL Server 2016 Web Edition on production currently. Since this edition supports replication as Subscriber only, I set up a new server with SQL Server 2019 Standard edition. I want to configure SQL Server 2019 as Publisher and SQL Server 2016 as subscriber. To initialize data for Publisher database on SQL server 2019, I created a backup on SQL Server 2016 and restored it on SQL Server 2019. Since our database is very large, I tried to initialize replication from a backup. So I did reverse backup-restore again by creating a backup of Publisher on SQL Server 2019 and restoring it on Subscriber on SQL Server 2016. But this did not work because [SQL Server 2019 backups cannot be restored by any earlier version of SQL Server] (https://docs.microsoft.com/en-us/sql/relational-databases/databases/copy-databases-with-backup-and-restore?view=sql-server-ver15). Could you please tell me what is the best method for initializing the replication in this case? Thank you very much for reading my question!

How can I delete Duplicate key in SQL 2016?

Please help. I’m not into programming and i have this error on our server. Does anyone know how to delete duplicate key in this index? My Database name is 772

Error :The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name ‘dbo.Addresses’ and the index name ‘AddressAccountContactType’. The duplicate key value is (8cbd1462-37aa-434d-93cd-ebf5c227641b, 7d8bcd55-16b7-4372-8de8-6b643189dd0c, DEL).

Why restored SQL 2012 Database on SQL 2016 significantly larger then SQL 2012 Database?

Question about restored SQL 2012 database on SQL 2016 and backed up again on SQL 2016. I’ve backed up SQL 2012 database that is 33 MB in size. Then, restored it on SQL 2016 and created another backup but this time on SQL 2016. The size of the backup changed from SQL 2102 backup file of 33MB to 315MB backup file size on SQL 2016.

Why such big increase to the size of the backup file on the SQL 2016?

Appreciate any help. Wesley

MS SQL Express 2016 on Amazon AWS: I Can Take Database Offline but Can’t Bring It Online

I can take databases offline (via GUI) but can’t bring them back online. The server details are as follow:

RDBMS: MS SQL 2016 Express Host: Amazon AWS/RDS Free Tier

Details/History of the Problem A few months ago, I created a db instance on Amazon AWS and at the time of creation, the ‘master/admin’ account was setup via the AWS/RDS web page. With this ‘admin’ account, I have created several databases on that instance without any problems.

Over the past few months, I have used this ‘admin’ account to change several databases to contained databases. I do this so that I can setup contained users. I have also done this several times on this server instance with the same admin account with no problems.

Last night, I had just created a new database via this admin account. I then tried to set this new database as a contained database and the process failed. The dialog box error message stated among other things “please try again later”.

After the 3rd failed attempt, I decided to take the database offline (via the GUI in SSMS). I did this in a bid to force close any possible open processes or connections that might be on this new database. That worked. However, I have not been able to bring it back online. I have tried via the GUI and also via a query and it keeps failing.

I have then checked the server roles assigned to this ‘admin’ account. It is not part of sysadmin role. As I understand, the ‘sysadmin’ role can do absolutely anything on the db instance. I reckon my admin account is not of this sysadmin role because it is meant for the in-house DBAs at Amazon AWS. I have tried to add it as sysadmin but it fails.

To ensure that my ‘admin’ account is the problem, I have taken another database offline (it’s empty). It went offline but it is also failing to come back online.

What could be the problem? Please help. Note that my skill level is very very low and I’m learning as I go along.

The server logs don’t show anything useful. I have attached screenshots.

1. Bring DB Online Error (via GUI)

2. SQL Server Log

3. Server Roles of My Admin Account

4. Failure to add SysAdmin Role to Admin Account

[ Politics ] Open Question : In 2016 Sen.Bernie Sanders encouraged Elizabeth Warren to run for president. Now she says he’s sexist. Why is she lying like Hillary Clinton?

If anything, she’s turned many progressives against her as many are demanding refunds of donations made to her campaign.  Why value does she and Hillary see in attacking progressives instead of Trump?

How to mirror user access between Windows 2016 Servers

I’m setting up an intranet web server that will handle requests coming form multiple different types of employees.

One of the request that my server will need to handle is to query data from a second server. The second server is hosted by a different team and has restrictions as to who can access their server (using windows authentications – usedefaultcredentials). My server does have access to query the second web server.

For the requests that require my server to do the querying I would like a method to emulate the validation criteria set by this second server.

Is there a way to: 1. Forward the received request (or part of) to the second server to see if they validate the user? or 2. Send a new request to the second server to ask if this user is validated? or 3. Create a connection between the servers that will maintain the set of authorized users so that my server can validate the request. This would need to done via automation for there is no chance that I can add a recurring task to other teams workload (Manually informing us of which user is now listed or unlisted is not a feasible solution).

I can request the other team to do small changes but there can’t be any big overhaul. Also it can only be one way (my server can’t have any way to manage/change permissions on the second server).

People Search and User Profiles SP 2016

Tell me why this code does not work in SharePoint server 2016 on-premises, but is working 2013? Possible problem in another sourceId("B09A7990-05EA-4AF9-81EF-EDFAB16C4E31")???

<script src="//ajax.googleapis.com/ajax/libs/jquery/1.10.2/jquery.min.js"></script> <script src="/_layouts/15/sp.runtime.js" type="text/javascript"></script> <script src="/_layouts/15/sp.js" type="text/javascript"></script> <script src="/_layouts/15/sp.search.js" type="text/javascript"></script> <script src="/_layouts/15/sp.UserProfiles.js" type="text/javascript"></script>  <script type="text/javascript">     $  (document).ready(function () {          $  ("#btnSearch").click(function () {             users = [];             userProfileProperties = [];             SP.SOD.executeFunc('sp.js', 'SP.ClientContext', getAllUsers);         });     });      var users = [];     var userProfileProperties = [];      //Method to fetch all the users     function getAllUsers() {          //Textbox value containing search term         var searchTerm = $  ("#txtSearchBox").val();         clientContext = new SP.ClientContext.get_current();         //Building Keyword query for the search         var keywordQuery = new Microsoft.SharePoint.Client.Search.Query.KeywordQuery(clientContext);         keywordQuery.set_queryText(searchTerm);         keywordQuery.set_sourceId("B09A7990-05EA-4AF9-81EF-EDFAB16C4E31");         keywordQuery.set_rowLimit(500);         keywordQuery.set_trimDuplicates(false);         var searchExecutor = new Microsoft.SharePoint.Client.Search.Query.SearchExecutor(clientContext);         results = searchExecutor.executeQuery(keywordQuery);         clientContext.executeQueryAsync(onQuerySuccess, onQueryError);      }     function onQueryError(sender, args) {         alert(args.get_message());     }     function onQuerySuccess() {         $  .each(results.m_value.ResultTables[0].ResultRows, function () {             users.push(this.AccountName);          });          fetchProfilePropertiesForUsers();     }      function fetchProfilePropertiesForUsers() {         var peopleManager = new SP.UserProfiles.PeopleManager(clientContext);         var profilePropertyNames = ["PreferredName", "PictureURL", "AboutMe", "TechNetProfile", "AccountName"];          for (var i = 0; i < users.length; i++) {             var userProfilePropertiesForUser = new SP.UserProfiles.UserProfilePropertiesForUser(clientContext, users[i], profilePropertyNames);             userProfileProperties[i] = peopleManager.getUserProfilePropertiesFor(userProfilePropertiesForUser);         }         clientContext.executeQueryAsync(onSuccess, onQueryError);     }      function onSuccess() {         var html = "<style type='text/css'> .floatL {float:left;margin:10px;} .floatR {padding-top:10px} .profile {padding:10px 10px;} .editProfile{margin-left:100px;}  div>img {height:72px;width:72px;} </style>";         for (var i = 0; i < userProfileProperties.length; i++) {             html += "<div class='profile'><div class='floatL'><img src='" + userProfileProperties[i][1] + "' href='#' /></div><div class='floatR'><h2><span>" + userProfileProperties[i][0] + "</span></h2><span>About Me : " + userProfileProperties[i][2] + "</span><br /><span>TechNet Profile : </span><a href='" + userProfileProperties[i][3] + "'>" + userProfileProperties[i][3] + "</a><br /></div></div><br />";          }          $  ("#divUserProfiles").html(html);     } </script> <input type="text" id="txtSearchBox" /> <input id="btnSearch" type="button" value="Search Users" />  <div id="divUserProfiles"></div>