Microsoft SQL Server





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

Hi all, hope you can help. I am having a problem that I was not expecting (based on my readings), so it seems clear that I have missed something.

I have a main form in an application that displays a Work Schedule for a machine. Periodically a legacy application (that I cannot change) updates the machine schedules meaning that the display on the screens needs to update. The application running on the machine screens has a Timer that fires every 10 minutes to query the schedule database to get the current Work Schedule for the particular machine.

The code on the Timer Tick event is simplicity itself:

Private Sub tmrRefreshJobs_Tick(sender As Object, e As EventArgs) Handles tmrRefreshJobs.Tick

End Sub

Open in new window

It just fires up a BackgroundWorker that goes off and queries the database for the current Work Schedule for the machine. The DoWork event is no more complicated, simply calling a routine to query the database and return a list of jobs. All of the work is done in that routine, and even that is straight-forward - it uses a DataTable and DataAdapter to query data from a SQL database and load it to the list.

Private Sub bgwRefreshJobs_DoWork(sender As Object, e As DoWorkEventArgs) Handles bgwRefreshJobs.DoWork

    Dim newPlannedJobs As List(Of MachineJob) = PlannedJobsForMachine()

End Sub

Open in new window

I was expecting the Main UI to remain responsive, but I have had reports of the screen "freezing" for a few …

In SQL reporting services there is an SCCM report for usage of an SCCM application.
The only info I need isOnly needed:

-Username, last usage but over the last 3 months (not per month)
-RuleName 'Microsoft Project'
-Month 2 (in report you can choose 1,2 but I'd need for last 3 months
-Year 2020

Below is what is defined in the epression of thedataset. How can I get only what I need in a SQL query?

="declare @TimeKey int
            declare @days float

            set @TimeKey=100*@Year+@Month

            select @days=DATEDIFF(day,IntervalStart,DATEADD(month,1,IntervalStart))
            from fn_rbac_SummarizationInterval(@UserSIDs) where TimeKey=@TimeKey

            if IsNull(@days,0) > 0
          select mu.FullName,
          MAX(mus.LastUsage) as LastUsage,
          SUM(UsageCount) + SUM(TSUsageCount) as C021,
          CAST((SUM(UsageCount) + SUM(TSUsageCount))/@days as numeric(38,2)) as C022,
          CAST(SUM(UsageTime)/60.0 as numeric(38,2)) as C023,
          CAST(SUM(UsageTime)/60.0/(SUM(UsageCount) + SUM(TSUsageCount)) as numeric(38,2)) as C024,
          CAST(SUM(UsageTime)/60.0/@days as numeric(38,2)) as C025
          from fn_rbac_MeteredUser(@UserSIDs) mu
          left join fn_rbac_R_User(@UserSIDs) u ON mu.FullName = u.Unique_User_Name0
          join fn_rbac_MonthlyUsageSummary(@UserSIDs) mus on mu.MeteredUserID=mus.MeteredUserID
          join fn_rbac_MeteredFiles(@UserSIDs) mf on mus.FileID=mf.MeteredFileID

Enviroment:  SQL Server 2017 Always ON 2 nodes with DAG DDBB.
Each server only 1 network interface.
DAG synchronous replica and manual fail over
No clustering enabled at windows OS

Right now, 1 listener defined but, as I could read, for  listener adding properly,  windows failover clustering is needed and also auto-failover needed

Is this correct?


We have a client that is running 1 Physical Server running 3 HyperV machines.

The SQL Server is running slow on all machines when accessing their CRM when generating reports and general navigation (on SQL)

We have been running the "LAN Speed Test (Lite)" Utility to check the speeds of the read and write and they are taking between 25-60 seconds to write a 1Mb file to the SQL Server.

The CRM company have wasted their hands of it as they said it's environmental.

We have checked the latest Windows Updates and none have installed, we have also removed the AV to test and still the same speeds occur.

We have even connected 1 PC and the Server into the router to remove the network switch and still an issue.

Is there anything else we can check?

thanks again
How to prep SQL Server for remote connection? I'm trying to connect to an Azure SQL Server thru Power Query Data resource?

In our test environment we are have an issue with an internal VB.NET application were members of an AD group are being prompted to re-enter their credentials. I have gone over the configuration of the IIS server with Microsoft and it is correct.

On a whim I asked our DBAs to add this group to the database SQLServer and give it exclude permissions. The issue was resolved. However this group is not needed for the Production database on SQL Server. Production is working fine.


Any insight would greatly be appriciated.
We currently have an application with an SQL server backend database.  User credentials are stored in an SQL table as a hash.  A stored procedure takes the user name and password parameters  and validates the credentials against that hash.   We have one customer that wants to use Active Directory instead of storing credentials in the database.  Is there any simple method of creating a stored procedure that can validate windows user names and passwords against active directory?   Is seems like this should be simple since SQL server allows you to connect using windows authentication.  I am not wanting to create a linked server to active directory either.  Any suggestions welcome.
I need to get the earliest non-breaking effective date by user from a list of effective/term dates. The data has a user id, effective date, term date and department, group and active indicator. A new row is created each year and typically has an effective date of the start of that year and a term date of the last day of the year, but not always.

The dates are contiguous in that, if there is more than one row for a user, the subsequent row's EffDate is the previous row's TermDate + 1.

If there is a 'break' in employment, there will be a single row with ActiveInd = 'I' with a date range from the day after their term date until the day before they are re-employed.

A 'break' is considered either ActiveInd going from A to I or a change in Department or Group. In the example I've attached, the Effective Date I want to return is 2015-01-01 because the earliest EffDt for Department MEH1 (without a break) is 2015-01-01. Until 2014-12-31 the Department was MEP1.

If the last row has a value of I for ActiveInd, and there are no subsequent A rows, then treat the I as an A, and look for the earliest EffDt for that department and group. In the example, if there were only the first 6 rows, the EffDt I'd want is 2008-01-01, since they termed on 201-05-12 in Department MEH1 and Group F.

Also, the last row for any user always has an open-ended TermDt value of 2199-12-31, regardless of whether they are active or not.

One caveat, if the user has a future row, but are currently…
In SSRS 2008 use to be an issue with AxtiveX control missing and couldn't see the report toolbar. In version 2019 having the same issue but I believe ActiveX isn't used anymore. I get letters in place of the controls and can't export files like to excel. What is the fix for this?
Report toolbar

The above screenshot shows that the Power BI refresh (both manual and scheduled) failed due to invalid credentials.  We have tried changing credentials and other properties but to no effect.

As you can tell, the two connections are represented by shared paths to Excel spreadsheets.  And, these spreadsheets pull data from a SQL view successfully.

This virtual machine from which this screenshot was taken pulls data from the box that has a personal gateway installed.  But, the virtual machine itself does not have a personal gateway installed.

What is the remedy for this?

Thank you!

Software Engineer
How to create a .xlsx file in C#. I am using Microsoft.ACE.OLEDB.12.0. On my local machine it is working fine. When I deploy the same code to server, its not working. Microsoft.ACE.OLEDB.12.0 is not registered on the server. Issue is not to install anything on the server. Please let me know how to fix the below attached code, creating .xlsx file without installing any exe on the server. I can include .dll with the the code that fix this issue?
I am trying to get a better understanding of what makes a SQL log file grow. For most of our clients they do not implement a point in time backup procedure so the recover model is set to Simple. Their databases tend to be fairly small(less than 10GB). I routinely perform upgrades to their ERP solution and during the upgrade process the log file may grow to 8GB due to all the insert, update and delete commands. Now sometimes a client does not upgrade at each revision so I need to perform a multi-hop upgrade to get them from the version they are on to the most current version. Now I just conducted an experiment. I just performed a four hop upgrade. At each upgrade I saw the log file grow to about 8GB. I then shrank the log file before performing the next upgrade. I then performed the complete upgrade without shrinking at each hop. I was surprise to see that the log file was about the same 8GB. Why didn't the log file grow to 32GB? Is SQL reusing some of the space in the log file?
I installed the on-site connector and successfully added it to Power Apps and Power Automate, working fine.  However, when I try and get data from SQL in Power BI, there does not seem to be a similar connector available.  I tried using just the SQL connector, specifying the Windows server name as the SQL server and the Table name I want and using the Windows user credentials, but it kept failing.

I found a thread that suggested the workaround was to run Power BI desktop as administrator, and when I tried that the response went to a time out message.

Really appreciate it if you can help.
Good day Experts,

Can you assist with the error message shown. The client is trying to connect to the application but receives that error.


I have an ssrs report which has a cell with value minutes, which I set to days:
=  ( Fields!Total.Value ) \ 1440

I color code this via
=IIF(Me.Value < 10,"LightGreen","Orange")

Now I'd like to set this cell to days:hours:minutes
and also keep same color coding: if days  < 10,"LightGreen","Orange"

Please advise howto
epo 5.10.  update 4.   sql 2016. windows 2012 R2
no issues for a couple of months. but now the log file of epo_db. grows at 1gb a minute till it fills the disk    only sometimes will it let me shrink it.    the drive has been increased to 50gb but in less than an hour it’s full.   disabled all the server tasks.  it still grows.   the db stays relatively stable.    it’s set to simple recover
all disk sizes are at least double mcafees recommendations.  and it’s only 50 clients

On SSRS, there is a folder SCCM, under that one there are lots of reports.
Inheritance is set on topfolders but on each subfolder inheritance is disabled(!). How can I reset it in bulk (there are like 40 reports) so they all inherite the parent rights?

We currently have a application that is connected to a SQL server database through LINQ, table adapters and stored procedures.
For a while now we have been getting a lot of problems with things like double adjustments sale not processing etc - (When a sale is processed for example the stored procedure for the sale are run twice, other times is does not run at all and most of the time the sale is processed correctly, we think this could be caused by deadlocks but we are unsure. When a sale is processed, we sometimes get that the stock has been adjusted incorrectly – double adjustments. These problems are currently occurring at our bigger clients).
Someone has mentioned making use of a transaction manger. The only one that I could find that is compactable with SQL server was Java database Connectivity (JDBC). Are there other transaction manager options we can use?
Or must we fix all the SQL in our application using SQL transactions?
Im looking for a way to change the fields of a Microsoft Server database table, in Design mode using a query
I have 2 SQL Servers, let's call them SourceSQL1 and LinkSQL1.

SourceSQL1 is Microsoft SQL Server 2014 (SP2). Let's say it has 3 databases: db1, db2 and db3 , db4 and db5. I have a SQL login named sql_link. The SQL Login has been given db level permission to db1 and db2. (db_datareader)
LinkSQL1 is Microsoft SQL Server 2012 (SP3)

I have created a SQL user on SourceSQL1 and granted the public role as well as granted db_dataReader on db1 and db2. I created a linked server on LinkSQL1 with the option to use the security context of sql_link. When I expand the linked server I see 3 databases! I am expecting to see only 2 databases: db1 and db2, however db3 also shows up in the list. The other 2 dbs (db4 and db5) do not show up in the list. When I attempt to query db3 via the linked server the query fails saying the table does not exist, so I am not concerned about a security hole, but I just wondering why this shows up in the list.
I am using PHP to make a call to MSSQL server.
It is returning a field type 'varbinary' that I am using to hold the data on an image

Then I would use the hex field to convert it and display it
$photo= hex2bin(	$photo);
 echo '<div class="img-outer"><img class="img img-responsive img-circle" src="data:image/jpeg;base64,'.base64_encode( $photo ).'"/></div>'; 

Open in new window

The code worked perfectly with PHP 5.6
Now the server has been upgraded to PHP 7.2 and now the binary field is coming back as gibberish and the hexbin is throwing an error that it's not hexdecimals.

What do I need to do to make this work now?
Thank you
I am an DBA required to design a partition strategy of a new data warehouse for my concern.  Since the development team is interested in partitioning by country code. I want to know if thats a good idea. Also i would like to know the factors to be considered for involving partition for a three country application along with the backup strategy for the same.

Expected data growth of the db in 7 years is 7 TB
I have date like below column is varchar 255

I want to update 1 hr to minutes so I want it to be

Dear ,

Im looking Discover LDAP  SQL inventory tools to pull below fields from SQL databases :

1- Server Name
2- Server IP
3- OS Version
4- SQL Version
5- Instance Name
6- DB Name
7- Database Restore Date
8- Last database access
9- Logins last access

I have this cursor inside a stored procedure. Inside this script is a call to a function called GETQUANTITY. This function exists in another database in the same instance. Now they want to move that database to another server. When I run this sp now I get this message even after adding the prefix for the linked server.
"Cannot find either column "MYDATABASE" or the user-defined function or aggregate "MYDATABASE.dbo.GETQUANTITY", or the name is ambiguous."  The purpose of this routine is to insert a purchase order. No value needs to be returned.

DECLARE po_line_cursor CURSOR
            ISNULL(POH.PODATE,'01/01/1900') AS PODATE,
            ISNULL(POH.REQDATE,'01/01/1900') AS REQDATE,

Microsoft SQL Server





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