Microsoft SQL Server

161K

Solutions

49K

Contributors

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

Share tech news, updates, or what's on your mind.

Sign up to Post

In SQL Server 2012, I am unable to connect to  SQL Server using Windows Authentication (via SQL Server Management Studio). I can sign on with the SA user using SQL Server Authentication and with another SQL Server login that I added. Just can't sign on with Windows Authentication.

According to the Server Properties, the server is set up for mixed mode (SQL Server and Windows Authentication mode).

The message that is thrown when I attempt to login with Windows Authentication is that the Login failed for User MXSVA\Administrator.

Any ideas?
0
Cloud Class® Course: C++ 11 Fundamentals
LVL 12
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Hi everyone. I'm designing a database app by using Microsoft Access as a front end (FE) to a MS SQL Server backend (BE). I want to optimize performance as much as possible. Currently, I'm using linked tables. However, for some of my queries, I use pass-through queries and it returns its results instantly. As I have observed, I can still use pass-though queries without linking the tables in the BE. The problem is that the results of pass-through queries are read-only.

Presently, for adding/editing records, I build those queries by basing them on the relevant linked tables. Sometime, I add records by building the query as an Append Query (INSERT INTO).  Is there any equivalent of a pass-through query that allows me to add/edit records? Please note that I will like to accomplish this using either the Query Design Grid (QDG) or by writing pure SQL in the QDG.

Thanks.
0
Hi All;


I have just tried to run my application with SQl Server back end on a different machine with a different network, now I'm getting the error attached .I'm not too sure what it is I asked the IT Manager he assured me that he installed the correct ODBC drivers. Could it be the code below not correctly done:

Option Compare Database
Option Explicit

Function ShowConnectInfo()
  Dim dbs As DAO.Database
  Dim tdf As DAO.TableDef
  Set dbs = CurrentDb()
For Each tdf In dbs.TableDefs
    If (tdf.Connect <> vbNullString) Then
        tdf.Connect = "ODBC;DRIVER={sql server};DATABASE=MDCAccounting;SERVER=USER\SQLEXPRESS;Trusted_Connection=Yes;"
Debug.Print tdf.Connect
        tdf.RefreshLink
    End If

 Next tdf

Set tdf = Nothing
  Set dbs = Nothing
End Function

Regards

Chris
Sql-Server-error.docx
0
Hello,
Can you please help,
I need to get the [Driver_In] Minimum Date Time/ [Driver_Out] Maximum Date Time between 2 tables (Per Driver) , then calculate the difference (Hours and Minutes),
This is What I have so far (But , it is wrong, I'm getting 2 lines per Driver

Select PickUpDriver AS [Driver], (Select Name From Drivers Where Drivers.DriverNumber = ActiveOrders.PickUpDriver) AS [Name],
CONVERT(date, OrderDate) AS [Order_Date],MIN(CAST(REPLACE(PickedupTime, ',', ' 01,') AS DATETIME)) AS [Driver_In],Max(CAST(REPLACE(DeliveredTime, ',', ' 01,') AS DATETIME)) AS [Driver_Out],
DATEDIFF(second, MIN(CAST(REPLACE(PickedupTime, ',', ' 01,') AS DATETIME)), Max(CAST(REPLACE(DeliveredTime, ',', ' 01,') AS DATETIME))) / 3600.00 AS [Hours_Worked],
DATEDIFF(minute, MIN(CAST(REPLACE(PickedupTime, ',', ' 01,') AS DATETIME)), Max(CAST(REPLACE(DeliveredTime, ',', ' 01,') AS DATETIME))) AS [Minutes_Worked]
From ActiveOrders
where (((OrderDate > = DATEADD(d,0,DATEDIFF(d,0,GetDate())))  And (OrderDate <= DATEADD(s, +86399, DATEADD(d,0,DATEDIFF(d,0,GetDate())))))) AND Cast(datepart(yyyy,PickedUpTime)as varchar) + '-' + RIGHT('0' + RTRIM(MONTH(PickedUpTime)), 2) + '-' + Right('0'+cast(datepart(DD,PickedUpTime)as varchar(2)),2) = Cast(datepart(yyyy,DeliveredTime)as varchar) + '-' + RIGHT('0' + RTRIM(MONTH(DeliveredTime)), 2) + '-' + Right('0'+cast(datepart(DD,DeliveredTime)as varchar(2)),2)
And ((Select Name From Drivers Where Drivers.DriverNumber = ActiveOrders.PickUpDriver)
0
I am joining the below contact table with another table (id_customer).  There are 4 records.  
ContactI need the data to display as

415  Name 1 ....    593  Name 3 ...
592  Name 2 ....    628  Name 4 ...

If there are nulls then the record would have nulls.

So basically there are 2 buyers and 2 engineers.  I want a distinct record next to each other.
0
Hi Guys,

Does anyone know the syntax in MSSQL to  get all the characters left from the first non-alphanumeric character:

What I have so far is below:

The LABEL value is 'Joe Bloggs [Duplicate - do not use]'

SUBSTRING([LABEL],0,CHARINDEX('[',[LABEL],0))

Open in new window


This yields  'Joe Bloggs ' which is ok but the issue is that someone could put any other character therefore it wouldn't work if someone puts ^ or / etc.

Could someone please kindly help me withthis?
0
Hi all.

I have the following query that contains a subquery. I want to only display records whose count (from the subquery) is greater than 1.

The problem is that the count it's displaying is twice as many as the original count. So for example, it's showing record ABC as having a count of 4, when in reality the count is 2. When I use the subquery (SQ) as it's own query it correctly shows a count of 2, but when I add it to the query below (as a subquery) it doubles the count. What am I doing wrong?

SELECT DISTINCT a.GPOContractID, b.EntityCode, COUNT(SQ.EntityCode) AS Count
FROM         ContractAssignmentHeader AS a INNER JOIN
                      ContractAssignmentDetail AS b ON a.CANumber = b.CANumber INNER JOIN
                          (SELECT DISTINCT ContractAssignmentHeader.CANumber, ContractAssignmentHeader.GPOContractID, ContractAssignmentDetail.EntityCode
                            FROM          ContractAssignmentHeader INNER JOIN
                                                   ContractAssignmentDetail ON ContractAssignmentHeader.CANumber = ContractAssignmentDetail.CANumber
                            WHERE      (ContractAssignmentDetail.Status = N'Active') AND (ContractAssignmentHeader.ContractType = N'GPO')) AS SQ ON 
                      a.GPOContractID = SQ.GPOContractID AND b.EntityCode = SQ.EntityCode
GROUP BY a.GPOContractID, b.EntityCode
HAVING     (COUNT(SQ.EntityCode) > 1)
ORDER BY a.GPOContractID

Open in new window


Thank you in advance.
0
I have the following table in a MS SQL DB:

CREATE TABLE [dbo].[Forms](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[FormNumber] [int] NOT NULL,
	[Blob] [varbinary](max) NULL,
	[FileName] [nvarchar](1000) NULL,
	[FormName] [nvarchar](1000) NULL,
 CONSTRAINT [PK_Forms] PRIMARY KEY CLUSTERED 
(	[Id] ASC
)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Open in new window

The contents of the table look like this:
1	19000	 Form19000.pdf	Order Form 1
2	19001	 Form19001.pdf	Order Form 2
3	19002	 Form19002.pdf	Order Form 3

Open in new window

I have information in all the columns except the BLOB column.  I would like to load the blob of a file for each row with its corresponding file.  How would I do this for a table that has several hundred rows using T-SQL?

I know that I can manually load each row with the following statement but how would I do this for the whole table:
UPDATE dbo.Forms
SET Blob =
	(Select * from OPENROWSET(bulk 'C:\Forms\Form19000.pdf', single_blob) as a)
WHERE Id = 1

Open in new window

0
Hi all;

I have got a link table manager on the net and I have tried to modify it to see if it can be used to create a less DSN connection because I will be shipping my application to other people as well with different networks. The original code before amendments  is listed below:


'//Name     :   CreateDSNConnection
'//Purpose  :   Create a DSN to link tables to SQL Server
'//Parameters
'//     stServer: Name of SQL Server that you are linking to
'//     stDatabase: Name of the SQL Server database that you are linking to
'//     stUsername: Name of the SQL Server user who can connect to SQL Server, leave blank to use a Trusted Connection
'//     stPassword: SQL Server user password
Function CreateDSNConnection(stServer As String, stDatabase As String, Optional stUsername As String, Optional stPassword As String) As Boolean
    On Error GoTo CreateDSNConnection_Err

    Dim stConnect As String
   
    If Len(stUsername) = 0 Then
        '//Use trusted authentication if stUsername is not supplied.
        stConnect = "Description=myDSN" & vbCr & "SERVER=" & stServer & vbCr & "DATABASE=" & stDatabase & vbCr & "Trusted_Connection=Yes"
    Else
        stConnect = "Description=myDSN" & vbCr & "SERVER=" & stServer & vbCr & "DATABASE=" & stDatabase & vbCr
    End If
   
    DBEngine.RegisterDatabase "myDSN", "SQL Server", True, stConnect
       
    '// Add error checking.
    CreateDSNConnection = True
    Exit Function
CreateDSNConnection_Err:…
0
An XML document can be either attribute centric or element centric.  I can see that an element-centric document is more hierarchical than an attribute-centric one but is this the only reason why it has been designed like this ?  Are there any other reasons why a developer would choose one over another ?
0
Ultimate Tool Kit for Technology Solution Provider
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Hello,

is it supported to put SQL server on hyper-v replica

I know that Exchange does not support the Hyper-V Replica feature,

but for SQL I have not found clear answer.
Thank you
0
Hi All,

I pass a string from string builder to a Stored Procedure. The contents is :
 'RUKO HARCO MANGGA DUA PLAZA" & vbCrLf & "BLOK H NO. 21-21A" & vbCrLf & "JAKARTA PUSAT DKI JAKARTA" & vbCrLf & "TELP. : 021-22393825" & vbCrLf'

I try below :

DECLARE @Address VARCHAR(MAX) = 'RUKO HARCO MANGGA DUA PLAZA" & vbCrLf & "BLOK H NO. 21-21A" & vbCrLf & "JAKARTA PUSAT DKI JAKARTA" & vbCrLf & "TELP. : 021-22393825" & vbCrLf'

SELECT
@Address AS ColOriginal
 

SET @Address = REPLACE(@Address, '"', '''') 

SELECT
@Address AS ColOriginal01
 

SET @Address = REPLACE(@Address, '& vbCrLf &' , '+ CHAR(13) + ') 
SET @Address = REPLACE(@Address, '& vbCrLf' , '')
SET @Address = '''' +  @Address 

SELECT
@Address AS ColOriginal02

DECLARE @NewAddress VARCHAR(MAX) = 'RUKO HARCO MANGGA DUA PLAZA' + CHAR(13) +  'BLOK H NO. 21-21A' + CHAR(13) +  'JAKARTA PUSAT DKI JAKARTA' + CHAR(13) +  'TELP. : 021-22393825' 

SELECT
@NewAddress AS ColOriginal03
 

Open in new window


I expect ColOriginal02 should have the same result as ColOriginal03, but it does not.

What should I do ?

Thank you.
0
Sql Server 2008: I have SQL Server Agent configured to run a Maintenance Plan every 12 hours. This plan backs up the databases to an External HDD plugged into a USB socket on the server. The Maintenance Plan is configured so that the Backup Set will expire after 14 days. When I set up this backup, I expected the backup files to be deleted after 14 days when the Backup Set expires. This does not happen. The files accumulate on the External HDD drive, and I have to manually delete them in order to prevent the  disk filling up. Is there a way to automate the deletion of these expired backup files?
0
I am working on a MVC project with AngularJs  and trying to download SSRS report as an excel. Am new to AngularJs.
I have tried the following codes and output is download excel with binary code!

Please correct my code, so that will download excel in exact same format as in SSRS report.

report-list.ctrl.js
(function () {
    'use strict';
    var module = angular.module('app')

        //.directive('ngConfirmClick', [
        //    function () {
        //        return {
        //            link: function (scope, element, attr) {
        //                var msg = attr.ngConfirmClick || "Are you sure?";
        //                var clickAction = attr.confirmedClick;
        //                element.bind('click', function (event) {
        //                    if (window.confirm(msg)) {
        //                        scope.$eval(clickAction)
        //                    }
        //                });
        //            }
        //        };
        //    }])

        .controller('ReportListController', ['$scope', '$http', '$q', '$interval', '$location', '$window', '$modal', '$modalStack', 'toastr', 'dataService',
            function ($scope, $http, $q, $interval, $location, $window, $modal, $modalStack, toastr, dataService) {

                $scope.showreportlist = true;
                $scope.reports = [];
                $scope.clientsessionreportData = [];


                $scope.downloadReport = function (res) {

                    

Open in new window

1
i have SQL data file which is 5 gb and sql log file is 71 gb..  in E Drive size is 100gb..
There a job full Daily Full backup are happening, .bak file is only 191 MB

After realizing the above I did one Transcational log backup to g drive(got temp space), it came around 18gb, after that I took again full backup the size of backup was same around 192MB

is some thing wrong with transaction log, there is no open transcations, i have verfied? is my full backup is 192 Mb only?

im shortage of space as well to take hourly transaction log on E drive, the db is in full recovery model

Version is sql server 2012 std
0
I have an excel spreadsheet that inserts a security code and security description into a table. Clicking a Command Button executes a SQL Server stored procedure for the insert. The issue I am having is that some securities contain a singe quote (Test's) and error when passed to the stored procedure. I want to pass two single quotes (Test''s) to the stored procedure even though the security is entered as a single quote in the cell.

Example: Test’s will give me an error when passed to the stored procedure. Test’’s does not error when passed to the stored procedure.

I have attached a very basic security loader spreadsheet, stored procedure and table creation.

SecurityLoader.xlsm

Table-and-Stored-Procedure-Creation.sql

Code I wish to modify for the single quote to double quote transformation:

Private Sub CommandButton1_Click()
    Dim conn As New ADODB.Connection
    Dim iRowNo As Integer
    Dim sSecurityCode, sSecurityDesc As String

      With Sheets("Securities")
           
        'Open a connection to SQL Server
        conn.Open "Provider=SQLOLEDB;Data Source=YourSQLServer;Initial Catalog=TestDb;Integrated Security=SSPI;I"

        'Skip the header row
        iRowNo = 2
   
        'Loop until empty cell in CustomerId
        Do Until .Cells(iRowNo, 1) = ""
            sSecurityCode = .Cells(iRowNo, 1)
            sSecurityDesc = .Cells(iRowNo, 2)

             conn.Execute "testdb.dbo.uspSecurities '" & sSecurityCode & "', '" & sSecurityDesc & "'"…
0
How to replace null values with previous rows data?
E.g, Row 3 is the first null row, so the values need to show row 2.

Thanks in advance.

DateKey      TaskStatusId      TaskTypeId
20180104             3               30
20180104             3               31
20180105      NULL      NULL
20180106      NULL      NULL
20180107      NULL      NULL
20180108      NULL      NULL
20180109      NULL      NULL
20180110      NULL      NULL
20180111      NULL      NULL
20180112      NULL      NULL
20180113      NULL      NULL
20180114      NULL      NULL
20180115      3      9
20180115      3      17
20180115      3      33
0
I am having difficulty in trying to understand when a programmer would make use of a multi-statement table-valued function instead of an inline table-valued function. It is clear to me that the former creates a temporary table outside of the actual function, while the latter just gives the result of a SELECT statement from inside the function. But are there situations where one would create a desired outcome, while the other would not ? Can anyone give me some practical examples ?
0
Hi Experts!

Kindly assist me once me on this , after laboring transferring the 120 tables with the help of SSMA tool all the tables and data type were linked and handled properly,I did the connection by creating DSN File (Example :AccountsFileDSN) via SQL Server odbc 13.1 AND ensured that the READWRITE option was selected in the string.

(1) The second and third sub forms were  grayed and I cannot capture or edit anything, the first sub-form is working normally no issues, all queries, reports and macros VBA are working normally, the performance is also excellent.


How do I make the  second & third sub-forms work as well?


The same forms are working normally in MS access 2016 even now people are using them without problems. Please note this MS Access 2016 to SQL Server 2016 that is where the issue is.

Thank you in advance

Regards

Chris
0
ON-DEMAND: 10 Easy Ways to Lose a Password
LVL 1
ON-DEMAND: 10 Easy Ways to Lose a Password

Learn about the methods that hackers use to lift real, working credentials from even the most security-savvy employees in this on-demand webinar. We cover the importance of multi-factor authentication and how these solutions can better protect your business!

We have a booking system, and have appointments. What I need to do is get a list of bookings and the duration, based on the difference between the 2 appointment times.

Is there a better way to write this SQL? I'm using MS SQL 2008.

Declare  @myTable TABLE(dID int, Doctor varchar(25),  AppDateTime datetime)

  Insert into @myTable Select 1,'Doctor 1', '2018-07-08 09:00:00'
  Insert into @myTable Select 2,'Doctor 1', '2018-07-08 09:10:00'
  Insert into @myTable Select 3,'Doctor 1', '2018-07-08 09:15:00'
  Insert into @myTable Select 4,'Doctor 1', '2018-07-08 09:30:00'
  Insert into @myTable Select 5,'Doctor 1', '2018-07-08 09:40:00'
  Insert into @myTable Select 6,'Doctor 2', '2018-07-08 09:00:00'
  Insert into @myTable Select 7,'Doctor 2', '2018-07-08 09:25:00'
  Insert into @myTable Select 8,'Doctor 2', '2018-07-08 09:35:00'
  Insert into @myTable Select 9,'Doctor 3', '2018-07-08 09:00:00'
  Insert into @myTable Select 10,'Doctor 3', '2018-07-08 09:05:00'
  Insert into @myTable Select 11,'Doctor 3', '2018-07-08 09:10:00'
  Insert into @myTable Select 12,'Doctor 3', '2018-07-08 09:25:00'
  Insert into @myTable Select 13,'Doctor 4', '2018-07-08 09:00:00'
  Insert into @myTable Select 14,'Doctor 4', '2018-07-08 09:30:00'
  Insert into @myTable Select 15,'Doctor 4', '2018-07-08 10:00:00'
  Insert into @myTable Select 16,'Doctor 5', '2018-07-08 09:00:00'
  Insert into @myTable Select 17,'Doctor 5', '2018-07-08 09:45:00'
  Insert into @myTable Select …
0
We have a customer, who is requesting a solution that, in our opinion isn't recommended. However, we need to be able to present both sides, hoping that the C level team will realize that solution B is better than A (their original request). My question is primarily how to accomplish this task should they elect to go down this road.

The request and current setup are as follows. Customer has 4 locations, each with their own database. They are wanting to consolidate into a single database. The caveat here is they want to be able to also have local copies of the databases at each location. Their reasoning is: if location A goes down (no internet), they want to still be able to read/write to the database. Once location A comes back online they want to synchronize the data. Same would apply for locations B - D.

We have a better solution in mind, however as previously mentioned we need to at least present the non-recommended solution to the customer. We would prefer to use a 4 node SQL cluster with redundant internet connections, but that's a little off topic I suppose. Thank you in advance for your assistance.
0
Hello Experts,

I have a SQL query that I need to delete records if an Employee name has multiple status from dbo.MainTable and if it exists then insert it into a new table dbo.NewInsertTable. Please see attached file.

I tried to do case statements or subquery but I cannot get it to work.

I know this is just easy to all experts.

Thank you again.
DeleteAndInserttoAnotherSQLTableAny.xlsx
0
Hello ,
I have this query which errors :

  SELECT RequestedBy,Authority,Date,Ref,CorresType, Comments,SentTo,Regarding,RoomTypeRouting,RoomTypeRouting2,RoomTypeRouting3,RoomTypeRouting4,RoomTypeRouting5,RoomTypeRouting6,Faxnumber,
	             Guestname,ConfirmationNumber,ArrivalDate,ArrivalDate2,ArrivalDate3,ArrivalDate4,ArrivalDate5,ArrivalDate6,Attention,Roomno, Roomno2,Roomno3,Roomno4,Roomno5,Roomno6,(CASE
                 WHEN Extendedto IS NULL THEN DepartureDate
                 ELSE Extendedto
        END) AS DepartureDate,
		(CASE
                  WHEN Extendedto2 IS NULL THEN DepartureDate2
                  ELSE Extendedto2
        END) AS DepartureDate2,
		(CASE
                  WHEN Extendedto3 IS NULL THEN DepartureDate3
                  ELSE Extendedto3
        END) AS DepartureDate3,
			(CASE
                  WHEN Extendedto4 IS NULL THEN DepartureDate4
                  ELSE Extendedto4
        END) AS DepartureDate4,
		(CASE
                  WHEN Extendedto5 IS NULL THEN DepartureDate5
                  ELSE Extendedto5
        END) AS DepartureDate5,
		(CASE
                  WHEN Extendedto6 IS NULL THEN DepartureDate6
                  ELSE Extendedto6
        END) AS DepartureDate6 FROM  [CORR]  WHERE  [Ref]  = '29' AND  [Year_Ref] =24 union all SELECT Route,DateTo,DateFrom FROM  [PROT]  WHERE  [InvoiceNumber]  = 'XYZ'

Open in new window


Any suggestion?

Error:
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.
0
Dear All;


I have successfully linked the tables which I created in SQL Server 2016 to MS Access 2016 (FE)  using DNS, I can see the Global Icons on each link  and prefix or schema if you like (dbo).
Now the old links to the former native database are also still there, before I delete the old links , how do I update the new links in Ms Access to start pointing all the forms & queries to these new global icon links?

Regards

Chris
0
Hello,
How to create a view in sql server with parameters.
Example :
DECLARE @EndDate AS DATE = '20180706';
DECLARE @StartDate AS DATE = '20180630';


WITH Unpivoted (GuestName, Property, ArrivalType, ArrivalDate, DepatureDate, RoomNo, RoomType,RequestedBy)
AS ( SELECT GuestName ,
            SentTo,
            'A1',
            ArrivalDate ,
            ISNULL(Extendedto, DepartureDate),
            Roomno ,
            RoomTypeRouting,
            RequestedBy
     FROM   CORR
     WHERE  DeleteRecord IS NULL
            OR DeleteRecord = 0
     UNION ALL
     SELECT GuestName ,
            SentTo,
            'A2',
            ArrivalDate2 ,
            ISNULL(Extendedto2, DepartureDate2),
            Roomno2,                         
            RoomTypeRouting2,
                     RequestedBy
     FROM   CORR
     WHERE  DeleteRecord IS NULL
            OR DeleteRecord = 0
     UNION ALL
     SELECT GuestName ,
            SentTo,
            'A3',
            ArrivalDate3,
            ISNULL(Extendedto3, DepartureDate3),
            Roomno3,
            RoomTypeRouting3,
            RequestedBy
     FROM   CORR
     WHERE  DeleteRecord IS NULL
            OR DeleteRecord = 0
     UNION ALL
     SELECT GuestName ,
            SentTo,
            'A4',
            ArrivalDate4,
            ISNULL(Extendedto4, DepartureDate4) ,
            Roomno4,
            RoomTypeRouting4,
            RequestedBy
     FROM   CORR
     WHERE  DeleteRecord IS NULL
         

Open in new window

0

Microsoft SQL Server

161K

Solutions

49K

Contributors

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