Microsoft SQL Server

158K

Solutions

48K

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

Hello - we're setting up our application on SQL Svr 2012 and are looking for quick help with the best-practice performance settings for SQL.  For example - we know with "Max Degrees of Parallelism" its recommended to set that at "0" for auto-detect, correct?  What else should we look at?

Thanks for your help.
Damian
0
Use Case: Protecting a Hybrid Cloud Infrastructure
LVL 4
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

So i got my data from an sql server to excel using ado just fine but when it retrieves the data it is just plain data with now formatting.

How do i get the data into an already existing excel table by a refresh task (i.e. table1) . this way the table can shrink and increase with the formatting already intact.
0
Trying to sum an alias column and group by an ID.  

	select dt.member_id, dt.NumberOfMonthsBetween
	FROM ( 
			select member_id, DATEDIFF(month,date_credentialed,renewal_date) as NumberOfMonthsBetween
			from member_credential
		  ) dt
	where dt.NumberOfMonthsBetween > 6

Open in new window



Current Result:
_____________________________________________
|Member_ID |  NumberOfMonthsBetween|
---------------------------------------------------------------
|    48             |                11                              |
|     1              |                12                              |
|     6              |                12                              |
|     8              |                12                              |
|     8              |                8                                |
|    41             |               11                               |
|   48              |               10                               |
---------------------------------------------------------------


What I am looking for:
_____________________________________________
|Member_ID |  NumberOfMonthsBetween|
---------------------------------------------------------------
|     1              |                12                              |
|     6              |                12                              |
|     8              |               20                               |
|    41             |               11                               |
|   48              |               21                               |
---------------------------------------------------------------
0
Hi,

One of my user requested me to purge records older than 6 months. This is the following query

DECLARE @timeThreshold int
DECLARE @timeUnits int
DECLARE @recordType nvarchar(1024)
DECLARE @useLock int
DECLARE @lockHost nvarchar(1024)
DECLARE @result int
DECLARE @numRows int
--Choose how many units in the past
SET @timeThreshold = 180
--Where 0 = Days, 1 = Weeks, 2 = Months
SET @timeUnits = 0
SET @recordType = N'ALLR' --All Record Types
EXEC dbo.SpDeleteOlderRecords @timeThreshold, @timeUnits, @recordType

When i start delete log file grows bigger. EXEC dbo.SpDeleteOlderRecords this proc deletion limit is 50 million.

Please help how could i accomplish this task asap
0
I have two tables with related data in them. They can be joined on DocNbr (which is a varchar(10) column). I am trying to audit deletes from these two tables by sending a simple email when a record is deleted. The problem becomes, I am trying to include data from both tables in the email body and I'm not able to, as trying to join the two tables together on the DocNbr field isn't possible any longer (as one or both tables have now had their record deleted).

What are my alternative options here?
0
My bosses have recently asked me to reassess our current server environment.

We currently have a DC, exchange, file server, SQL server and archive server. The servers are approaching 7 years in age so the possibility of a possible refresh is very much welcome.

Our environment is rather basic, but a system failure would result in weeks of downtime due to waiting periods for server hardware. So my task is simple, I need to reduce downtime and maintain production even during a failure.

Now I'm thinking this shouldn't be too much of a head ache to achieve and given how hardware performance has improved since installing the current servers I'm thinking that we could do more with less. Dual CPU servers or servers with 64GB+ memory shouldn't be required, but then again I haven't played around with any new server hardware in years so I could be very wrong.

So here's what I'm thinking, the PDC can run on an entry level 1U server, I can add another as BDC. File server can also be entry level; I say entry level in the sense of CPU and RAM requirements, I will always run RAID arrays on my servers. SQL and Exchange; here I need some guidance.  The SQL server is a joke, it's an i7, 32GB RAM with 6 SSDs in RAID; it runs ok but has recently shown signs of wanting to kick the bucket, this will have to change to proper hardware.

Our Exchange is a dual Xeon with 80GB RAM, 10 SAS drives in various RAID arrays; I'm thinking this can be reduced to a single CPU system with 64GB RAM,…
0
hi,

it seems that MS only release SQL server data tools for VS 2015 so that we can admin and develop even SSIS project within VS 2015, how about VS 2017?

it seems we don't have this option to develop SSIS package inside VS2017, am I right?
0
Hello Experts,
can someone help,
I need to group it by VehciletypeID

SELECT   'Montreal' AS City ,FO.OrderDate,
(Select Description from VehicleTypes where VehicleTypes.VehicleTypeID = FO.VehicleTypeID) AS [Vehicle],
(Select Name From Drivers where Drivers.DriverNumber = FO.PickupDriver) AS [PickupDriver],
(Select Name From Drivers where Drivers.DriverNumber = FO.DeliveryDriver) AS [DeliveryDriver],
(Select Name From Drivers where Drivers.DriverNumber = FO.Driver3) AS [Driver3],
(Select Name From Drivers where Drivers.DriverNumber = FO.Driver4) AS [Driver4],
(Select Name From Drivers where Drivers.DriverNumber = FO.Driver5) AS [Driver5],
DATEADD(wk, DATEDIFF(wk, 7, GETDATE()), 0) As Monday,
DATEADD(wk, DATEDIFF(wk, 7, GETDATE()), 1) As Tuesday,
DATEADD(wk, DATEDIFF(wk, 7, GETDATE()), 2) As Wednesday,
DATEADD(wk, DATEDIFF(wk, 7, GETDATE()), 3) As Thursday,
DATEADD(wk, DATEDIFF(wk, 7, GETDATE()), 4) As Friday
FROM     Finalizedorders FO
WHERE    FO.orderdate BETWEEN DATEADD(wk, DATEDIFF(wk, 7, GETDATE()), 0) AND DATEADD(wk, DATEDIFF(wk, 7, GETDATE()), 6)
AND NOT EXISTS (SELECT * FROM  Clients C WHERE  C.AccountNumber = FO.AccountNumber
AND (AccountCode LIKE 'K%' OR AccountCode LIKE 'A%' '))

Attached is a sample of the output, and how I would like to group it.

thanks for your help,
Sample.xlsx
0
Hi Experts,


I want to execute  the SQL Agent Job (list of stored procedures) in a loop  to process in a batch for historical loads.

For example , I want to do historical load for one year , I want to implement this logic in SQL Agent Job in small batches of 4 days for 92 iterations (365/4)

Note : Each Iteration , I can load only 4 days of data , as the data size is massive (~130 Million records for 4 days). So I want to process historical loads in small chunks of 4 days and batches.

In this example below , how to execute this logic in loop , auto increment the values of  @historical_begin_date && @historical_end_date for each iteration and execute the stored procedures in a loop.

Example
Declare @historical_begin_date date time
Declare @historical_end_date date time

while @historical_begin_date <@historical_end_date  
begin
Exec Stored procedure 1
Exec Stored procedure 2
end

Please walk me through the solution with code .

Thanks,
Sreekanth.
0
Hi,

I'm using SQL Server 2014.  I'm trying to build a dynamic WHERE clause based on an input parameter to my stored procedure.  If a user passes in a value of 1 for the "@active" parameter, I want to to add the SQL "AND e.date_posting_expires GTE getDate()"

I have this code snippet but it doesn't quite work.

      
         SELECT c.company_id, company_name
        FROM CompanyTable c
         WHERE c.company_id = @company_id

	IF (@active = 1 ) 
		BEGIN
		AND j.date_posting_expires GTE getDate()
	END

Open in new window

0
Optimize your web performance
LVL 1
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Hi Experts,
I have the following function to  check for a date if its within current week.
Public Function sThisWeekOpening(sDate As String) As String
    sThisWeekOpening = " DateDiff(ww, DateAdd(d, -2, IsNull([" & sDate & "], DateAdd(wk, 1, GetDate()))), DateAdd(d, -2, GetDate())) > 0"
End Function

Open in new window

However this ends of being very complicated in terms of debugging and maintaining.
just to give an example how its being used..
    OpenTPSQL = "((" & sThisWeekOpening(IIf(sTable = "view_Openings", "DayStart", "Day"))
    OpenTPSQL = OpenTPSQL & " or (" & sThisWeekOpening("StatusDate") & " or StatusDate is null))"
    OpenTPSQL = OpenTPSQL & " or " & IIf(sTable = "view_Openings", "DayStart", "Day") & " < getdate()-1)"
    OpenTPSQL = OpenTPSQL & " and InActiveStatus is null"

Open in new window


Therefore I'm looking for a way to simplify it. (perhaps just converting it to a SQL UDF..)

Also since this is using GetDate() which is getting the system date, how can I change it to test it on a diff date,
for example the following is checking if its pass Monday and there are open records for the coming week.
    OpenTPSQL = "((" & sThisWeekOpening(IIf(sTable = "view_Openings", "DayStart", "Day"))
    OpenTPSQL = OpenTPSQL & " or (" & sThisWeekOpening("StatusDate") & " or StatusDate is null))"
    OpenTPSQL = OpenTPSQL & " or " & IIf(sTable = "view_Openings", "DayStart", "Day") & " < getdate()-1)"
    OpenTPSQL = OpenTPSQL & " and InActiveStatus is null"

Open in new window

How can I test this without having to wait for the next Tue?

Thanks in advance.
0
Hi Experts,

I'm trying to setup an application that should allow users adding/editing/deleting a single row at a time.

Now for security purposes, I would like to have these users not being able to view more then one record at a time.

Was thinking of designing a stored procedure that will accept a parameter and display just that one record selected.

However I'm stuck with the following
A- A stored procedure is probably read only in Access.
B- In order to have users select a record, I need a drop down displaying all records, how can I have it just avail to the combo box record source while in database container it should not appear?

PS. for deleting single record I came up with this function that will perform it, so I dont give this user access to delete anything in SQL BE, and just have a button in Access calling the following
Public Function DeleteRecord(sTable As String, lID As Long) As Integer

    Dim cnn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim cmdString As String
    Dim i As Integer
    
    Set cnn = New ADODB.Connection
    cnn.ConnectionString = "driver={SQL Server};" & _
          "server=tcp:ServerName\Instance;uid=MyUser;pwd=MyPWD;database=MyDB"
    cnn.Open


    cmdString = "delete from " & sTable & " where id = " & lID
 
    cnn.Execute (cmdString), i
    cnn.Close
    DeleteRecord = i

End Function

Open in new window

So basically I'm looking to deploy the same idea for adding/editing records.

Would like to know what is the simplest way to accomplish it.

Thanks in Advance.
0
Little NT network, 30 computers, 1 server - On Server is 2008 R2, AD, DHCP, DNS, File server and runs several small sql databases, Protected with Symantec EndPoint SBE. Noticing a small SQL based program stopped running - checked Windows App log and there is a constant  "Classic Audit Failure twice every minute, Event ID 18456, Source: MSSQL$XXXXXXX since 7/21/17. This Service is for a different SQL database, which the program is running fine. Nothing to do with the SQL service that will not start and has affected a different program. Symantec runs scans daily, nothing caught, ran manual scan and still nothing.
Could it be something else than virus? What do you recommend?
0
When the below restore script  was executed ,it's trying to restore databases that already in non recovery mode (always on setup) I try to restore all databases  in no recovery mode but not databases which has already been set up for  always on . want to exclude restore with always on please help

RESTORE HEADERONLY FROM DISK = 'C:\backup\test.bak'

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[sp_RestoreFromAllFilesInDirectory]    Script Date: 16/07/2017 15:22:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[sp_RestoreFromAllFilesInDirectory]
@SourceDirBackupFiles nvarchar(200), @DestDirDbFiles
nvarchar(200),@DestDirLogFiles nvarchar(200)
AS
--Originally written by Tibor Karaszi 2004. Use at own risk.
--Restores from all files in a certain directory. Assumes that:
--  There's only one backup on each backup device.
--  Each database uses only two database files and the mdf file
---is returned first from the RESTORE FILELISTONLY command.
--Sample execution:
-- EXEC sp_RestoreFromAllFilesInDirectory 'C:\Mybakfiles\',
----'D:\Mydatabasesdirectory\' ,’C:\MylogDirectory\’
SET NOCOUNT ON

--Table to hold each backup file name in
CREATE TABLE #files(fname varchar(200),depth int, file_ int)
INSERT #files
EXECUTE master.dbo.xp_dirtree @SourceDirBackupFiles, 1, 1

--Table to hold the result from RESTORE HEADERONLY. Needed to get
---the database name out from
CREATE TABLE #bdev(
 BackupName …
0
The below script created a user SQL\srvSSISAcc on all my databases and granted datawriter and datareader to all databases but the use is not created in the security login, I need this name to be created on Security login before granting user mapping to all datbases

USE master

GO

DECLARE @DatabaseName VARCHAR(32)   
DECLARE @SQL NVARCHAR(max)
DECLARE @User VARCHAR(64)
SET @User = '[SQL\srvSSISAcc]' --Your User

DECLARE Grant_Permission CURSOR LOCAL FOR
SELECT name FROM MASTER.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')  

OPEN Grant_Permission  
FETCH NEXT FROM Grant_Permission INTO @DatabaseName  
WHILE @@FETCH_STATUS = 0  
BEGIN  

    SELECT @SQL = 'USE '+ '[' + @DatabaseName + ']' +'; '+ 'CREATE USER ' + @User + 
    'FOR LOGIN ' + @User + '; EXEC sp_addrolemember N''db_datareader'', 
    ' + @User + '; EXEC sp_addrolemember N''db_datawriter'', ' + @User + ''

    EXEC sp_executesql @SQL
    PRINT @SQL

FETCH NEXT FROM Grant_Permission INTO @DatabaseName  
END  
CLOSE Grant_Permission  
DEALLOCATE Grant_Permission

Open in new window

0
I created the below Table and insert some values in it.

What I need to do is if Field1 = 2 and Field2 = 2 Then use the Date in Field3 Else Use '1/1/3000'.
CREATE Table #Test(
	Field1	int,
	Field2	int,
	Field3	datetime
)

INSERT INTO #Test
(Field1, Field2, Field3)
VALUES
(2, 1, '1/1/2017'),
(2, 2, '1/2/2017'),
(2, 1, '1/3/2017'),
(2, 2, '1/4/2017'),
(2, 2, '1/1/2017')

SELECT * FROM #Test

DROP TABLE #Test

Open in new window

0
Hi experts,

I'm using sql server 2008.

I have a table with a column called Phones1 that holds phone numbers.
Some phone numbers are 7 digits and some are 10 digits.

I have a select query that gets all the columns from my table

if a phone number is 7 digits I want to format it like this:  123-4567
if a phone number is 10 digits I want to format it like this:  (204)123-4567

What's the best way to format this column like this  in my query?
0
I am trying to figure out how to build a query to select data from a specific table that is tied to two other tables.

The schema can be broken down to the following tables:
dbo.Orders
ID and OrderNumber

dbo.OrdersASIDocumentLink
ID, OrderID

dbo.ASIDocuments
ID, DocumentContents

What I need to do is to be able to select all rows in ASIDocuments that match the OrderNumber from Orders.  Both tables are joined by OrdersASIDocumentLink.

How can I do this?  I have a SQL Fiddle with the schema already created with some sample data.  Here is a link to a SQL Fiddle.
0
Hi All,

Can you please give an idea why am I getting this error when sending test mail in SQL Server 2012. And can I use mail.sql.com as smtp server?

 the requested name is valid but no data of the requested type was found

Thanks and best regards.
0
Instantly Create Instructional Tutorials
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Hello,
Can you please help,
I need to add the case statement in a Croos Apply

SELECT FO.Orderno , FO.Orderdate , FO.Accountnumber,LEFT(FON0.NoteID , LEN(FON0.NoteID)-1) NoteID,
LEFT(FON.reason , LEN(FON.reason)-1) reason
FROM FinalizedOrders FO
CROSS APPLY (SELECT CONVERT(varchar(10), FON0.OrderNoteTypeID)  + '__' FROM FinalizedOrderNotes FON0 where FO.[Orderno] = FON0.[Orderno] AND (FON0.OrderNoteTypeID in (9,10,11,12,13,14,15,16,17,18,19,20,21,22,23)) FOR XML PATH('')) FON0 (NoteID)
(CASE       WHEN FON0.OrderNoteTypeID = 1 THEN 'A'
      WHEN FON0.OrderNoteTypeID = 2 THEN 'D'
      WHEN FON0.OrderNoteTypeID = 3 THEN 'Y'
      WHEN FON0.OrderNoteTypeID = 4 THEN 'X') END
CROSS APPLY (SELECT FON.NoteText + '__' FROM FinalizedOrderNotes FON where FO.[Orderno] = FON.[Orderno] AND (FON.OrderNoteTypeID in (9,10,11,12,13,14,15,16,17,18,19,20,21,22,23)) FOR XML PATH('')) FON (reason)
WHERE xxxxxxxxxxxxxxxxxxxxxxxxxxxxx

Is this possible?
Thanks,
0
Hi Experts,

I have successfully deployed my website on azure but when I try to login I received an error which when debugged produces:-

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 52 - Unable to locate a Local Database Runtime installation. Verify that SQL Server Express is properly installed and that the Local Database Runtime feature is enabled.)

The application works well on my local machine and connection string seems fine.

Just wondering if there are extra steps to be taken when deploying an application written in code first approach to azure?

I have followed numerous article and can't proceed further. I am no the azure portal>>application setting> connection string screen.

In here I have populated the name and value boxes with this: .

NAME---- DbConnectionString
VALUE--- Data Source=(l@@db)\$$$LocalDB;Initial Catalog=@@@inApp;Integrated Security=SSPI;

Any pointers will be greatly apprieciated.

Kind Regards

Mike

but still no luck
0
Receiving an error when trying to add this record. It errors out stating that the "conversion failed when converting date and/or time form character string." It will work properly when I manually key in a date for the @SQLWorkDate parameter (e.g. '2017-07-26'). Any idea how I can correct this?

ALTER PROCEDURE [dbo].[AutomaticUpdates_RouteBookDataPreProcessDE]
   @RequestUID TowerRequestUID,
   @SQLWhere PCSTowerReportFilter = NULL,
   @RouteWorkDate DateTime = NULL,
   @WorkDate DateTime = NULL
AS

	DECLARE @SQLRoutes VarChar(MAX)
	DECLARE @SQLCommand VarChar(MAX)
	DECLARE @SQLWorkDate DateTime
	
SET NOCOUNT ON

	SET @WorkDate = (Select DateValue From W_ReportParam Where RequestUID = @RequestUID AND ParamName = 'RouteWorkDate')
	SET @SQLWorkDate = CONVERT(date, @WorkDate)
	SET @SQLRoutes = REPLACE(REPLACE(@SQLWhere, ')', ''), '{Route.Route} IN (', '')
	
	SET @SQLCommand = 'INSERT INTO [dbo].[zRouteBookDataDE] Select ''' + convert(nvarchar(max),@RequestUID) + ''', * From [dbo].[Report_vw_RouteBookDE] Where ServiceDate = ''' + @SQLWorkDate + ''' AND Route IN (' + @SQLRoutes + ') 
		AND NOT(NewStart = 0 AND RouteBookOrder = 0) AND
		NOT( RouteBookOrder = 1 
			And ( 
				ServiceHistoryStatus IN (2,3,4,5,10) Or
				CustomerStatus = 2 Or
				SiteStatus <> ''AP''
				) 
			 )' 

Open in new window

0
I have a web site that sends out emails, and in that email, the user can click a link that confirms an order and comes back to our site and gets logged as a visit (there's more processing going on, the visit log is just a small piece of it). what I want to do is create a query that'll return each email address along with the number of times they clicked the links (or display zero if they haven't clicked it). I came up with the following query, but it's returning the total count for ALL emails that clicked the link for that particular order, and I'm having trouble narrowing it down:

  select distinct( c.email ), 
  (   SELECT Count( rr.OrderItemID )
		From clpResponses rr
			INNER JOIN OrderItems oi ON rr.OrderItemID = oi.OrderItemID
			inner join Contacts as c on (r.ContactID = c.ContactID) and (c.AdminID = 1426)
		Where oi.OrderID = 1093484 )
  from OrderItems as i 
	  left outer join clpResponses as r on (i.OrderItemID = r.OrderItemID) and (r.AdminID = 1426)
	  left outer join Contacts as c on (r.ContactID = c.ContactID) and (c.AdminID = 1426)
  where i.AdminID = 1426 and i.OrderID = 1093484

Open in new window


here is an example piece of data from the clpResponses table:
SysID	AdminID	OrderItemID	ContactID	UserID   	        UserIDRespondent	DateOfResponse
105426	1426	         2068699	521 	        474771   	4888360	                        2009-09-29 17:14:40.467

Open in new window


and the OrderItems table:
OrderID       AdminID     OrderItemID
1093484      1426            2068699

Open in new window


and finally the contacts table:
ContactID	AdminID	UserID	FirstName	LastName	Email	                       Active	RemoveRequested
521                 1426                474771   NULL             NULL              me@abc.com	                1              0

Open in new window

0
Hi,
i have data like below dimsupplier

supplierno  suppliername
001              ABC
001
001
002
002
002              BBC

Want results like below:

supplierno  suppliername
001              ABC
001             ABC
001            ABC
002           BBC
002           BBC
002             BBC
0
The following code has two counters. One runs directly from the form (in the main thread) and another is performed by a BackgroundWorker. Interestingly, the two counts do occur at the same time (which is the intent...win!) but the progressbar doesn't change at all until after the BackgroundWorker has finished. Of course, I'd like the progressbar to update during the counting. Help?

Note: The code below is just a test I'm performing so I can take the structure and expand it to a real-world, time-consuming, stored-proc.
Public Class Form1

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Me.ProgressBar1.Maximum = 10
    End Sub

    Private Sub btnHeavyTask_Click(sender As Object, e As EventArgs) Handles btnHeavyTask.Click
        Debug.Print("Starting to count now")
        BackgroundWorker1.WorkerReportsProgress = True
        BackgroundWorker1.RunWorkerAsync()
        mainThreadTask()
    End Sub

    Private Sub mainThreadTask() 
        Dim i As Integer = 0

        Do Until i = 15
            Me.txtHeavyTask.AppendText(vbCrLf + i.ToString)
            Me.txtHeavyTask.Refresh()
            Threading.Thread.Sleep(500)
            i += 1
        Loop

        Me.txtHeavyTask.Text = "Done"
    End Sub

    Private Sub BackgroundWorker1_DoWork(sender As Object, e As System.ComponentModel.DoWorkEventArgs) Handles BackgroundWorker1.DoWork
        Dim i As Integer = 1

        Do Until i = 10
            

Open in new window

0

Microsoft SQL Server

158K

Solutions

48K

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.