SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.

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

Sign up to Post

I have a SQL db which is populated by a LoginScript.
Every row represents an unique Login and have several fields like ID,TIME,USERID,CLIENT, etc.
What I wonder is if I can, with a single SELECT get only 1 row (or few fields) per user, the LATEST, from lets say 1 month back?
Free Tool: Port Scanner
LVL 12
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

I need to modify a SQL Server Bulk Import SPROC statement slightly and I'm not sure of the syntax.

This is the statement I have used in the past
	set @sql = 'Insert into Balance_Import
	select * from openrowset( bulk '''+@ImportFileNameAndLoc+''' ,FORMATFILE = '''+@FormatFileNameAndLoc+''') as a'

Open in new window

The variables '@ImportFileNameAndLoc' and '@FormatFileNameAndLoc' are passed to the SPROC as parameters

It worked for file being imported that did not have field names in the first record of the imported file.

The import file I am working with now has field names in the first line so I need to add the  Firstrow=2 into the above statement.

How do I revise the original statement to add the 'Firstrow=2'?

If it helps here is the hard coded version of the Bulk import
--Insert into Balance_Import
--select * FROM OPENROWSET( BULK 'I:\My Documents\Access_Databases\JordanDelinq\TestDirectory\Balances\MJOLibrary Summaries_TabDelim.txt', firstrow = 2, 
--FORMATFILE = 'I:\My Documents\Access_Databases\JordanDelinq\TestDirectory\SQLImportFormatFiles\Balance_TabDelimited_Import_MArkW01.txt') as a

Open in new window

I am trying to write  SQL Script that cycles through a column of values and performs an insert based on the value of the row. I have some written below.  I am struggling with how to cycle  through the list while running the insert statement for each siteid value.

create table #mytable
(isiteid int);

insert into #mytable values(20);
insert into #mytable values(291);
insert into #mytable values(347);
insert into #mytable values(469);
insert into #mytable values(473);
insert into #mytable values(635);
insert into #mytable values(722);
insert into #mytable values(729);
insert into #mytable values(754);
insert into #mytable values(760);
insert into #mytable values(967);
insert into #mytable values(1188);
insert into #mytable values(1390);
insert into #mytable values(1463);
insert into #mytable values(1489);

declare @isiteid int
declare @icnt int

select @icnt= count(*) from #mytable

--everything works fine until the line below
select @isiteid = top 1 isiteid from #mytable
while @icnt>0

insert into DMRepository
txtLitContent,iEmailType,iFaxType,iPrintType,tiRecordStatus,chUpdateBy,dtUpdateDate,iSurveyId,iAutoType,iTrackingEmail,DMRepositoryDocumentSourceId, OemLocked)
'Hello ',

Delete top 1 from #mytable
Set @icnt= @icnt-1


Please let …
Where in SQL Prompt do I turn off the "Hints highlights?  Very distracting

SQL Server Query Syntax
I am creating a SQL SPROC that will be passed a headerID as the input parameter.  The Header ID is the primary key of the table,

There are several fields that are output parameters.  I want these fields loaded with the corresponding fields in the selected record, if any.

The SPROC is pretty simple but I definitely have my syntax wrong.  THis is the first time I've tried to use the (NoLock) but there is more wrong than that.  I'm pretty sure it's the way I'm trying to assign the table values to the return parameters.  This is the SPROC:
USE [JTSConversion]
/****** Object:  StoredProcedure [dbo].[tblMuni_Master_Get]    Script Date: 3/14/2018 11:24:34 PM ******/
-- =============================================
-- Author:		MJO
-- Create date: 
-- Description:	Get Payment Years
-- =============================================
Create PROCEDURE [dbo].[sptblMuni_Master_Get] 
	-- Add the parameters for the stored procedure here
	@passedHeaderID			int = 0,
	@MuniCode				int	= 0			Output,
	@SchoolDistrictNum		int	= 0			Output,
    @MuniName				nvarchar(25)	Output,
	@FormalName				nvarchar(25)    Output,
	@Millage				float = 0       Output,
	@PerCap					float = 0		Output,
	@ContactName			nvarchar(25)	Output,
	@Title					nvarchar(25)	output

	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.

		SELECT  Top 1
			@MuniCode			=

Open in new window

I want to import/ bulk load a tab delimited .txt that is attached below this line directly into a SQL Table.
I have been thru this process with fixed length text files and I'm wondering what the difference would be in the FormatFile for the tab delimited file.
The input fixed length file did not have column headings as the first line in the file.  I can generate the tab delimited file with or without the first row being column headings.  Whichever works best.  Is one way or the other best?
The format file I developed to import the fixed length file looks something like this:
1       SQLCHAR       0       30        ""        1    CostName        ""             
2       SQLCHAR       0       4         ""        2    MiniCode        ""
3       SQLCHAR       0       17        ""        3    LotBlock        ""
4       SQLCHAR       0       1         ""        4    TaxType         ""
5       SQLCHAR       0       4         ""        5    MiniCode02      ""
6       SQLCHAR       0       7         ""        6    ControlNumber   ""
7       SQLCHAR       0       1         ""        7    TaxType02       ""
8       SQLCHAR       0       14000     ""        8    CostDetail      ""
9       SQLCHAR       0       30        ""        9    GRBNumber       ""
10      SQLCHAR       0       30        ""        10   GDNNumber       ""
11      SQLCHAR       0       75        "\r\n"    11   Remarks         ""

Open in new window


I want to import the tab delimited file I attached above into a SQL table defined like this:
Hi Experts,
I have a query question.  my query has a field "Last Name" and sort it by alphabet order from A to Z, how to I do if I just want to pull from L to Z?  I have a field which call Last Name A: Left([LASTNAME],1)

How can I create a new table from a query in SQL Server.
Is it possible to create a table from a tsql query ? for example

select  a,b,c from t1
select a,b,c from t2
In oracle, I want to see people who have the same Course Description this year as they did last year

So students where  Last years Course = This Years Couse

Data is stored as follows:

Student ID: 111000
Course Code: 100
Course Year: 2018
Student ID: 111000
Course Code: 100
Course Year: 2017

Any help much appreciated.

Hi guys,
I need help in using LAG function in recursive CTE. I want to get the previous contract last service date for the current contract but having no luch with it.
Below is my code
        ContractId INT PRIMARY KEY ,
        OriginatingContract INT ,
        LastService DATETIME,
        FirstContract INT ,
        Serviced BIT ,
        Expired BIT

INSERT INTO @Contracts
VALUES ( 1, NULL,GETDATE()-4, 1, 1, 1 ) ,
       ( 2, 1,GETDATE()-3, 1, 0, 1 ) ,
       ( 3, 2,GETDATE()-2, 1, 0, 1 ) ,
       ( 4, 3,GETDATE()-1, 1, 0, 0 ) ,
       ( 5, NULL,GETDATE()-4, 5, 0, 1 ) ,
       ( 6, 5,GETDATE()-3, 5, 1, 1 ) ,
       ( 7, 6,GETDATE()-2, 5, 0, 1 ) ,
       ( 8, 7,GETDATE()-1, 5, 0, 0 ) ,
       ( 9, NULL,GETDATE()-4, 9, 0, 1 ) ,
       ( 10, 9,GETDATE()-3, 9, 0, 1 ) ,
       ( 11, 10,GETDATE()-2, 9, 1, 1 ) ,
       ( 12, 11,GETDATE()-1, 9, 0, 0 ) ,
       ( 13, NULL,GETDATE()-4, 13, 0, 1 ) ,
       ( 14, 13,GETDATE()-3, 13, 0, 1 ) ,
       ( 15, 14,GETDATE()-2, 13, 0, 1 ) ,
       ( 16, 15,GETDATE()-1, 13, 0, 1 ) ,
       ( 17, 16,GETDATE()-4, 13, 1, 0 );

WITH Hierarchy
AS ( SELECT A.ContractId ,
            A.OriginatingContract ,
            A.Serviced ,
            A.Expired ,
            A.ContractId AS RootContractID ,
            '\\' + CAST(A.ContractId AS NVARCHAR(MAX)) AS ContractPath ,
            0 AS ContractLevel ,
            A.Serviced AS InPathServiced,
            A.LastService as 

Open in new window

IT Degree with Certifications Included
IT Degree with Certifications Included

Aspire to become a network administrator, network security analyst, or computer and information systems manager? Make the most of your experience as an IT professional by earning your B.S. in Network Operations and Security.

Trying to use this method to move database from SQL 2008 to SQL 2016.    


The job fails and tells me to check the log file for details ... however no log file was created in the specified location.
Hello, I am having some trouble coming up with a SQL statement that will give me the results that I need and was hoping yall could help

given a table like:

ID  Day/Time                instance
1   Monday 10:00       1
2   Tuesday 10:00       1
3   Tuesday  12:00      2
4   Tuesday  18:00      3
5   Wednesday 9:00   1          

How can I query all values on the same day with only the highest instance number? so that i return something like:

ID  Day/Time                instance
1   Monday 10:00       1
4   Tuesday  18:00      3
5   Wednesday 9:00   1    

Thank you very much for taking a look and any help you can provide.
Good afternoon experts. Newbie here to vb.net. I created a form with ms visual studio 2017 community addition. The form contains a datagridview that connects to a sql database (sql 2008). On my form I have several text boxes that are used to enter data which then populates my grid via a "add record" button. The connection to my sql database is handled in a class I called SQLControl.vb (if that matters). Listed below is my code for the add record button. So far everything works great and now what I need is a check to prevent duplicate data from being entered so I am looking for a check against my text box and datagridview. The field (not a primary key) I would like to validate against is called EMPL_ID (employee id). I don't know how to accomplish what I'm sure is a simple task.

Here is the code behind the button:

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        If txtboxEmplID.Text = "" Or txtboxLastName.Text = "" Or txtboxFirstName.Text = "" Or txtboxEmailID.Text = "" Or txtboxSupervisor.Text = "" Or txtboxSupervisorID.Text = "" Or txtboxManager.Text = "" Or txtboxManagerID.Text = "" Or txtboxSupervisorEmail.Text = "" Or txtboxMgrEmail.Text = "" Then
            MsgBox("All Fields Are Required", MsgBoxStyle.Information)
            Exit Sub
        End If

        'Add SQL Params & Run The Command
        SQL.AddParam("@Empl_ID", txtboxEmplID.Text)
        SQL.AddParam("@Last_Name", txtboxLastName.Text)
I've never posted a question with a high priority before but I have a meeting with the client tomorrow and would like to be prepared.  The client only informed me of their final decision 1 hour ago.

I posted a question on this topic back in December.  Please review the question for background.

Prior Question on this topic

The short story is that my client wants to have all users on a multi user network, use the exact same copy of the application accde.  The  client says they have an existing Access application already running in this mode, so apparently it can be done.

I have been developing in Access since Access 2003 and this goes against everything I have ever been told and the information given by EE'ers in the prior post.  I sent the post to the client so they are aware.  I'm having difficulty even wrapping my head around multiple users in the same accde using the same form at the same time but working with different data.

My application is being created in MS Access 2013 and the back end DB is SQL Server.  A lot of development has already gone into this application prior to the client making this requirement known.  

Among other things my application uses linked SQL tables, linked SQL view, forms bound to the linked SQL table, forms bound to local Access tables, MS Access queries, local …
Without having to open a cursor and update one record at a time, is there a feature in SQL Server 2012 that I can use to assign a sort number to each Qty for the same Id as follows:
Id         Qty        SortNo
1           20          1
1           50          2
1           100        3
2           10          1
2           70          2

Note: The table would start out with blank SortNo data and the code of the stored procedure would update the SortNo of each record.
I need to include multiple rows of data in SQL Database onto a single row. Example

Result set
ID        Keycode
1          65056825
1          12121212

Desired Result Set
ID         Keycode1       Keycode 2
1           65056825       12121212
I am writing a script to export exchange message tracking information into SQL so it can be queried by an analyst.
I stored the a 4 tables
messagelog, contacts, event, messagerecipients.
I am trying to build a query that will produce a separate row for each recipient and another query that will combine all the recipients into a comma separated list into one column. Any guidance/answers?

I store each email only once in the contacts table and the event type (SEND,RECEIVE...) once in the event table.

The messagelog table is as follows:

I have a messagerecipients table that is a map of recipients to messagelog:

Table data

id      email
21      externaluser@domain.com
19      noreply@domain.com
17      noreply@email.education.com
13      noreply2@domain.com
23      person1@somewhere.com
24      person2@somewhere.com
25      person3@somewhere.com
22      staff.five@example.com
16      staff.four@example.com
14      staff.one@example.com
18      staff.six@example.com
15      staff.three@domain.com
20      staff.two@example.com

id      type
1      BADMAIL
2      DELIVER
3      DSN
4      FAIL
6      EXPAND
7      RECEIVE
8      RESOLVE
9      SEND
10      SUBMIT
11      TRANSFER

id      eventid      sender      subject      exmessageid            time
1            7            13                                    8325692                  2018-02-10 16:00:12.000
2            7            15                                    8325693                  2018-02-10 16:03:11.000
3            7            17                                    8325694                  2018-02-10 16:03:55.000
4            7            19                                    8325695                  2018-02-10 16:04:04.000
5            7            21                                    8325696                  …
Hello, I want to be able to create a SQL Trigger to email abc@gmail.com when the following field falls below -1 cost.

Are you able to help me, please?
I have a very basic program that reads a csv file into a SQL table, runs a couple of queries on that table and then reads that data and writes the converted data back to an output csv file.  On my development computer all works fine. However, when I drop it into production right after reading in the data I get this exception error message:

System.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0

        private void btnScrub_FileClick(object sender, EventArgs e)
            lblProcessing.Text = "Processing starting ... please wait.";


            Input_CSV_File();     /* Insert the data into SQL */


            Output_CSV_File();      /* Extract the data from SQL and write only required fields to the csv file */


Please cancel this question. I ran the query directly in SQL and discovered their tempdb was consuming all the disk space.

I do not know how to even troubleshoot something like this. I have increased the connection timeout for the SQL connection but that cannot be it because I open it and leave it open until the routine closes. I am wondering if it has something to do with the Application.DoEvents method? I do have a progress bar in the code and use this to see the progress bar progress. By the …
Free Tool: IP Lookup
LVL 12
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

I have imported some data from Excel. The table that I have imported (and there are more to come) has a similar structure like a DB : each table has as its first column a unique ID, like the customer table and the order table.  

When I imported the data and I was asked how to deal with the primary key issue, I set Access to create and use its own unique primary key, as I want Access to handle this.  That is why for example the customer table has two IDs .. OldID and KundenID (the primary key).

Obviously, the imported customer ID and the one from Access do not match. Therefore, I decided to create a SQL UPDATE query ... which would be called from a loop:
UpdateMe - is the procedure that loops through the customer table and
UpdateTable - is the procedure that updates the old customer id in the order table (tbl_test in this example) and replaces it with the actual Access primary key.

Sub UpdateTable(oldCustomerID As Integer, newCustomerID As Integer)
    Dim dbs As Database
    Dim qdf As QueryDef
    Set dbs = CurrentDb
    dbs.Execute "UPDATE tbl_test " _
        & "SET KundenID = " & newCustomerID _
        & "WHERE KundenID = " & oldCustomerID & ";"

If newCustomerID Mod 100 = 0 Then
    Debug.Print newCustomerID & " has been updated."
End If
End Sub

Sub UpdateMe()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim oldCustomerID As Integer
Dim newCustomerID As Integer

Set db = CurrentDb

Open in new window

How to move SQL system and temp databases.    As well as the FTdata folder, Jobs folder and logs folder.      I just loaded a brand new SQL 2016 standard edition.   During the installation I set the paths as follows.     D:\SQL Data for data.    D:\SQL Data\ for log ...  and D:\SQL Backup for backup.      my intent was to keep backups simple.     However the installation created another folder by default.    D:\MSSQL13.MSSQLSERVER\MSSQL    and then put another Data folder with the system database and temp database.     As well as the other folders mentioned above.

I would like to move this all to my folders I created manually for ease of backup.     All but I suppose the tempdb which I do not need to backup.   Perhaps though another root folder D:\SQL temp would be good for me to stay along the lines of keeping things simply.    

Is this normal for the installer to do this.    Would it be easier to remove and start over?
Is there a way to subtract 1 day from the date using SQL in this scenario?

FirstMonth = 2018-01-31
SecondMonth = 2018-02-02

conn.open "select * FROM [ExportSales].[dbo].[Transaction_report_3-8-2018v2] WHERE Category = 'Sales' AND cast(Date as Date) > ('" & (FirstMonth) & "' ) AND cast(Date as Date) < '" & (SecondMonth) & "' ORDER BY Date ASC", "DSN=sales"

Open in new window


I have a split database with multiple front ends and one back end. These are all stored on network drives.

When just using it for data entry, all works as it should. However, when running SQL queries, the performance slows right down and what takes 5 to 10 seconds when a single user is using it can take over 40 minutes when we have multiple users.

Can anyone explain why this could happen? I've compressed the FEs and the BE too.


Hi EE,

I have got a sample data as
DECLARE @data TABLE (ContractId INT, WorkorderId INT, AppointmentState VARCHAR(15), DateCreated DATETIME)
INSERT INTO @data VALUES (1,1,'Requested','20180310'),

select * from @data

Open in new window

In the dataset against the contractid 1 i have got multiple workorderid. I can have Appointment State of Booked, Requested and Outstanding.
1. If the AppointmentState of any workorder is Booked then i want only that record in my result set.
2. If the appointment status dones't contain Booked but if it contains Requested then i want only that record in my result set
3. If all the records of that contractid dont' have Booked and Requested state then i want to display that record only.
4. If there are duplicate states for a contractid for example two states of Booked then i want to get the first record by date created.

I can do this by WHILE loop but was just wondering is there any other way of doing it as the data set contains over 150,000 records and the performance of while loop will be very slow.

Kindest regards
Long question for you all.

I am using AutoSPInstaller to install and configure a multi-server farm for a customer (SharePoint 2013 Enterprise). In my DEV environment it works perfectly (albeit with some slightly different names).

In the live environment, it gets as far as creating the first Site Collection in the first Web Application, and it errors out - "sharepoint autospinstaller feature with id c85e5759-f323-4efb-b548-443d2216efb5 is not installed in this farm". When I checked the Central Admin site, it was stopped in IIS - when I started it, I was able to access the site - and I am unable to create the Site Collection via the GUI - it returns the same error.

The customer does have various permissions locked down in the environment - this may have something to do with it, or it could be an issue with the SQL server.

Before I try and get the other teams to investigate their side of things, is there anything anyone can suggest that I can check to help me rule out any issues with the SharePoint servers and AutoSPInstaller?



SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.