SQL

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

Hi,
I have what should be a simple question to answer as it is common with many softwares and that is
How to Browse the data in a table in SQL Server Express 2017 ?
I can't seem to find a browse command even online.
I know I can browse the top 1,000 records but I want to browse the whole records.
Can someone please put me out of my misery
Many thanks
Ian
0
Ensure Business Longevity with As-A-Service
Ensure Business Longevity with As-A-Service

Using the as-a-service approach for your business model allows you to grow your revenue stream with new practice areas, without forcing you to part ways with existing clients just because they don’t fit the mold of your new service offerings.

This code below looks up the number of sales per day, based on similar dates.  How can I add up the TotalSales_Cnt from TABLE1 and TABLE2?  I've tried INNER JOINS with no success.

<%

Dim rs
Set rs = Server.CreateObject("ADODB.recordset")

SQL1 = "Select " & _ 
      "cast(PurchaseDate as date) PurchaseDate , count(*) TotalSales_Cnt " & _ 
      "from [DAL.ProductContext].[dbo].[SoldItemsTABLE1] " & _ 
      "Group By cast(PurchaseDate as date) " & _ 
      "order by 1 desc "

SQL2 = "Select " & _ 
      "cast(PurchaseDate as date) PurchaseDate , count(*) TotalSales_Cnt " & _ 
      "from [DAL.ProductContext].[dbo].[SoldItemsTABLE2] " & _ 
      "Group By cast(PurchaseDate as date) " & _ 
      "order by 1 desc "

rs.open SQL1, "DSN=dashboard"

response.write "<table border=1><tr><td><b>Sales Date</b></td><td><b>Total Sales TABLE1</b></td></tr>"
    
do while rs.eof = false
    response.Write("<tr><td>" & rs("PurchaseDate") & "</td><td>" & rs("TotalSales_Cnt") & "</td></tr" & vbcrlf)
    rs.movenext
loop

response.write "</table>"

rs.Close 

rs.open SQL2, "DSN=dashboard"

response.write "<table border=1><tr><td><b>Sales Date</b></td><td><b>Total Sales TABLE2</b></td></tr>"
    
do while rs.eof = false
    response.Write("<tr><td>" & rs("PurchaseDate") & "</td><td>" & rs("TotalSales_Cnt") & "</td></tr" & vbcrlf)
    rs.movenext
loop

response.write "</table>"

%>

Open in new window

0
I have some sales data, and each record has a purchase date.  I have multiple sales on each day, and want to count up how many sales per day based on the purchase date.  What's the best approach to be able to count them up, then print the results to the page?

Dim conn
Set conn = Server.CreateObject("ADODB.recordset")
conn.open "select * from [DAL.ProductContext].[dbo].[SoldItems] ORDER BY PurchaseDate DESC", "DSN=dashboard"

Open in new window

0
Hi

In SQL Server 2017 I am trying to convert a column to an integer and have used following command  
select convert(int,Prize)
from [NH Flat_2012]
Where Prize is the column which contains example data  £1,200 which I want to convert to  1200
The error message I receive is....
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value '"£1' to data type int.

Can someone kindly assist with correct syntax
Many thanks
Ian
0
I am getting this error when running a simply query.

dates are saved in DB in this format yyyy-mm-dd. query string being sent to database is..

SELECT count(WoID) as Pending FROM WorkOrders WHERE ( (Status='Approved') OR (Status='Assigned') or (Status = 'Rejected') )

running this direct in SSMS works and return the correct result, there is no date involved in this query its just getting a record count.


Microsoft SQL Server Native Client 10.0 error '80040e07'

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

/cmms/common/db_obj.asp, line 94
0
I created 3 tables
Roles
- IdRole Primary Key
- UserId Foreign Key
- Role Field

Employee
- IdEmp PrimaryKey
- Other fields

RolesEmps
- IdRoleEmp PrimaryKey
- IdEmp Foreign Key
- IdRole - I want this to be a foreign key and connect to Roles.IdRole but it is identifying it as a PK.  When I try to create a relationship I get "THE COLUMNS IN TABLE ROLES DO NOT MATCH AN EXISTING PRIMARY KEY OR UNIQUE CONSTRAINT".

How can I create two FKs in RolesEmps to point to appropriate tables to set relationships.  I have IdEmp relationship set but I can't set IdRole
CREATE TABLE [dbo].[Roles](
	[IdRole] [int] IDENTITY(1,1) NOT NULL,
	[UserID] [int] NOT NULL,
	[Role] [nvarchar](max) NOT NULL,
 CONSTRAINT [PK_dbo.Roles] PRIMARY KEY CLUSTERED 
(
	[IdRole] ASC,
	[UserID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[Roles]  WITH CHECK ADD  CONSTRAINT [FK_Roles_Users] FOREIGN KEY([UserID])
REFERENCES [dbo].[Users] ([UserID])
GO

ALTER TABLE [dbo].[Roles] CHECK CONSTRAINT [FK_Roles_Users]
GO

Open in new window

CREATE TABLE [dbo].[Employees](
	[IdEmp] [int] IDENTITY(1,1) NOT NULL,
	[EmplId] [nvarchar](max) NULL,
	[FirstName] [nvarchar](max) NULL,
	[MI] [nvarchar](max) NULL,
	[LastName] [nvarchar](max) NULL,
 CONSTRAINT [PK_dbo.Employees] PRIMARY KEY CLUSTERED 
(
	[IdEmp] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Open in new window

I WANT TWO FK's IN THE BELOW TABLE
CREATE TABLE [dbo].[RolesEmps](
	[IdRoleEmp] [int] IDENTITY(1,1) NOT NULL,
	[IdEmp] [int] NOT NULL,
	[IdRole] [int] NOT NULL,
 CONSTRAINT [PK_RolesEmps] PRIMARY KEY CLUSTERED 
(
	[IdRoleEmp] ASC,
	[IdEmp] ASC,
	[IdRole] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[RolesEmps]  WITH CHECK ADD  CONSTRAINT [FK_RolesEmps_Employees] FOREIGN KEY([IdEmp])
REFERENCES [dbo].[Employees] ([IdEmp])
GO

ALTER TABLE [dbo].[RolesEmps] CHECK CONSTRAINT [FK_RolesEmps_Employees]
GO

Open in new window

0
sum fields after being separated into new fields from query

I have a query:
That takes a field that looks like :
I have a field called
PICKCOUNTS   that looks like  

PICKCOUNTS
123-43-456-56-564-65-0
The query belows  separates the data like below
Exact     Function     DM       Match       OUT_OF_SYSTEM          Xtra            DO_NOT_COUNT
123        43                 456        56                 564                             65                    0

Now i need to sum all the new columns/fields

Exact:Function:dm:match
123+43+456+56

SELECT     
[AUTOMATCH]
      ,[POSTCOUNTS]
      ,dbo.SEPARATES_COLUMNS(POSTCOUNTS, 1, '-') AS EXACT
,dbo.SEPARATES_COLUMNS(POSTCOUNTS, 2, '-') AS FUNCTIONAL
,dbo.SEPARATES_COLUMNS(POSTCOUNTS, 3, '-') AS DM
,dbo.SEPARATES_COLUMNS(POSTCOUNTS, 4, '-') AS MATCH
,dbo.SEPARATES_COLUMNS(POSTCOUNTS, 5, '-') AS OUT_OF_SYSTEM
,dbo.SEPARATES_COLUMNS(POSTCOUNTS, 6, '-') AS X
,dbo.SEPARATES_COLUMNS(POSTCOUNTS, 7, '-') AS DO_NOT_COUNT
      ,[MANUALMATCH]
      ,[EXCPETION_DIRECT]
      ,[AUDITEDBY]

FROM  [Metrics].[dbo].XREF_HEADER

Open in new window


FUNCTION

fUNCTION:
FUNCTION [dbo].[SEPARATES_COLUMNS](
@TEXT      varchar(8000)
,@COLUMN    tinyint
,@SEPARATOR char(1)
)RETURNS varchar(8000)
AS
  BEGIN
       DECLARE @POS_START  int = 1
       DECLARE @POS_END    int = CHARINDEX(@SEPARATOR, @TEXT, @POS_START)

       WHILE (@COLUMN >1 AND @POS_END> 0)
         BEGIN
             SET @POS_START = @POS_END + 1
             SET @POS_END = CHARINDEX(@SEPARATOR, @TEXT, @POS_START)
             SET @COLUMN = @COLUMN - 1
         END 

       IF @COLUMN > 1  SET @POS_START = LEN(@TEXT) + 1
       IF @POS_END = 0 SET @POS_END = LEN(@TEXT) + 1 

       RETURN SUBSTRING (@TEXT, @POS_START, @POS_END - @POS_START)
  END

Open in new window





thanks
fordraiders
0
Hi Experts!

Need your help with the following data pull in SQL 2012.

The data looks like this:

PerId   StateType     StatusType  StatusDate
01      Home          Active      01/01/2012
01      Home          Closed      12/31/2012

02      Home          Active      01/01/2013
02      Home          Closed      12/31/2015
02      Home          Active      10/01/2016

03      Away          Closed      01/01/2013
03      Away          Active      01/01/2015
03      Away          Active      12/31/2015

03      Home          Active      02/01/2013
03      Home          Closed      12/31/2013
03      Home          Closed      12/31/2018

DECLARE @PersonHistory TABLE
(PerId VARCHAR(50), StateType varchar(50), StatusType varchar(20), StatusDate datetime)

INSERT INTO @PersonHistory ([PerId], [StateType], [StatusType], [StatusDate]) VALUES (N'01',N'Home',N'Active', N'01/01/2012')
INSERT INTO @PersonHistory ([PerId], [StateType], [StatusType], [StatusDate]) VALUES (N'01',N'Home',N'Closed', N'12/31/2012')
INSERT INTO @PersonHistory ([PerId], [StateType], [StatusType], [StatusDate]) VALUES (N'02',N'Home',N'Active', N'01/01/2013')
INSERT INTO @PersonHistory ([PerId], [StateType], [StatusType], [StatusDate]) VALUES (N'02',N'Home',N'Closed', N'12/31/2015')
INSERT INTO @PersonHistory ([PerId], [StateType], [StatusType], [StatusDate]) VALUES (N'02',N'Home',N'Active', N'10/01/2016')
INSERT INTO @PersonHistory ([PerId], [StateType], [StatusType], [StatusDate]) VALUES 

Open in new window

0
I need to write a sql query to retrieve the loan number, state and city, customer first name for loans that
are in the states of CA,TX,FL,NV,NM but exclude the following cities (Dallas, SanFrancisco,
Oakland) and only return loans where customer first name begins with John.
0
This SQL command is causing an Invalid Character Error - -2147467259-[Oracle][ODBC][Ora]ORA-00911: invalid character

select * FROM LIMS.SAMPLES WHERE LIMS.SAMPLES.HSN = 804010001 and Format$(LIMS.SAMPLES.COLLECT_DATE, "MM/DD/YYYY") = #03/01/2018# order by LIMS.SAMPLES.COLLECT_DATE


Do you see anything in the command that would be the issue?
0
Active Protection takes the fight to cryptojacking
LVL 2
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

I am using ASP.NET with an MVC 5 project and entity framework. I want to remove the required attribute that is created implicitly based on the SQL database field. I keep getting entity validation errors on the EMailAddress2 field in the form. I tried these two things among others.

1)  I added @{ Html.EnableClientValidation(false);} to the code in the create view for that div, but that did not resolve the issue.
2) I added DataAnnotationsModelValidatorProvider.AddImplicitRequiredAttributeForValueTypes = false; to the global.asax.

Create view code:

<h2>Create</h2>

@{ Html.EnableClientValidation(false);}
@using (Html.BeginForm())
{
    @Html.AntiForgeryToken()

    <div class="form-horizontal">
        <h4>user</h4>
        <hr />
        @Html.ValidationSummary(true, "", new { @class = "text-danger" })
        <div class="form-group">
            @Html.LabelFor(model => model.CustomerName, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.CustomerName, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.CustomerName, "", new { @class = "text-danger" })
            </div>
        </div>

        <div class="form-group">
            @Html.LabelFor(model => model.AccountNumber, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model 

Open in new window

0
Let's say I have two tables:  TableA and TableB - FieldA has some values similar in FieldB and I want to do a compare.
The problem with TableB and it's FieldB - the values are longer than what's found in TableA, FieldA.  

To explain further I provided a sample database:

Table A - FieldA
University191803ABC
UNIVERSITY1923032304
University192305H2307
UNIVERSITY19121F51
UL158

TableB - FieldB has:
UNIVERSITY19251
University191803
UNIVERSITY20101
UNIVERSITY192303
UL1580AH14L

I want a query listing with a join to show the following results:

(Table A, TableB combined)

FieldB, FieldA
University191803ABC, University191803
UNIVERSITY1923032304, UNIVERSITY192303
UL158, UL1580AH14L

These match because the first set of characters match- from the start of the value.  

(University191803 for the first one.  University192303 for the second row.)
The first two are 19 characters that match whereas the last example grabs the first 5 cause of the smaller value: UL158....

How would I go about creating such a query in Microsoft Access?
ee-joinleftdata.accdb
0
I want to declare a list:

DECLARE @myList     nvarchar(max)  = '1,2,3~4,5,6';


Do cross appply (or similar) to get:

col 1, col 2, col 3
1         2        3
4         5        6

How would you do that?

/BK
0
Hi,
I am encountering an error message using SQL Server Express 2017 when trying to import an Excel csv file.  as under
"the microsoft.ace.oledb.16.0 provider is not registered on the local machine (system data)"
Can anyone please shed some light on it.
Many thanks
Ian
0
Hi

I have an Excel VB.net Add-in project that interacts with an Azure SQL database using a connection string.
I want to control which tables a user can see and edit.
What is the best way to do this. Should it be set up inside the Azure  SQL database per user name
and then a different connection string given to various people?

Thanks
0
SQL Server Maintenance

I have a SQL Server that houses a small db for about 20 users. I am by no means a DBA. I am currently using Datto to do image level backups every hour. The server has 2 Drives that are mirror running server 2008 and SQL 2008 and 8 GB of Ram. I would like to know what other tasks i should be doing to make sure the DB is healthy. Also i would like to know if there is a way to monitor its performance to make sure its running at its best.
0
If there were an Job Candidate table with an CandidateID primary key, and if each Candidate could have zero, one, or many certificates, then:
1. Would it be better to have a Certificate table with a CandidateID foreign key relation to the Candidate table primary key such that zero or more rows in that Certificate table could refer to a particular candidate
Or
2. Would it be better to have an intermediate mapping table with each row having a CandidateID column foreign key and a CertificateID foreign key referencing the Certificate table's CertificateID primary key

I seem to recall a previous company I worked for having some difficulty with the first approach; but I don't remember what it was.
Maybe the second approach of an intermediary mapping table was just for many to many relationships such as many authors could be connected to a book while many books could be tied to one author as well as any combination of the above. That's the example I remember from my books.
What's the better way for a one to many relationship? One of the above or something else?
What level of normalization is this working on?
Is there a correct term for that intermediary mapping table?
I used to know these things but it has been over a decade since I worked on database design and I don't have time to crack open the old books (if I even still have them anywhere). I appreciate everyone's help.
0
In a SQL Server database I have some reference tables to limit possible choices in a column.
For example, in a table called, Common, I have a column called, Exemption, which can only have Exempt or Non-Exempt as the possible values, so I created a table called, FLSA with one column called Exemption and I populated it with those two values. That one column is the primary key.
In another instance, I had a column called Degree for the minimum degree requirement for a position. I'm going to create a table called, Degrees, with one column called Degree which I will populate with Associates, Bachelors, Masters, PhD. Again, that one column is the primary key.
My questions are:
1. Do I need a foreign key relationship between the Common table Exemption column and the FLSA table Exemption column?  
I don't see why I would need any sort of relationship between those columns; but I want the business rules to be enforced on the data base side as much as possible instead of on the application side so that if any other future programmer (including a future me who's forgotten the business rules) tries to create another app from this data base or alter an existing app.
2. Even if I did have a foreign key relationship between Common Exemption and FLSA Exemption, that wouldn't really stop someone from putting an errant value into the Common table Exemption column, would it?
3. If I relationship between the main table and the reference table isn't the correct way to enforce the …
0
Help with complex join.

I have 18 tables that I need to merge all of the data into one result set.
Each table can have a variable number of columns...some identical some not.  
Each table has over 255 columns.
Each table has a requestId column with is the primary key but the primary key is unique in each of the 18 tables...so a left join doesnt do anything.


I need to be able to somehow dynamically add the rows and columns to a master result set.

I am seeing terminalogy for an full outer union ...but actually have never implemented this and am not finding good documentation.

I know how to do this the manually way of matching columns or creating aliases..but this approach will not work.  Its too dynamic.

Please help.  I am hoping theire is some ingenious solution out there.
0
Microsoft Azure 2017
LVL 13
Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Hi I have 2 customers in a table.

custid  type
1       deposit
1       payment
2       deposit
2       credit
3       deposit
3       payment

now I only want the customers who has BOTH type as Deposit and Payment. So i want to select custid 1
and 3 in this case .

can someone give me that tsql please ?

Many Thanks
0
Hi
I am using the following Excel VBA code to pull data from a SQL database. It doesn't pull the column headers.
How do I change it to include headers? Thanks

            Set objMyConn = New ADODB.Connection
            Set objMyRecordset = New ADODB.Recordset
         
            Dim S As String
    
        'Open Connection'
            objMyConn.ConnectionString = "xxxxxx"
            objMyConn.Open
    
    
        'Open Recordset'
            Set objMyRecordset.ActiveConnection = objMyConn
            objMyRecordset.Open strSQL
    
        'Copy Data to Excel'
            ActiveSheet.Range(oRangeStart).CopyFromRecordset (objMyRecordset)
            

Open in new window

0
Hi,
I have installed SQL Server Express 2017 about 4 months ago and now trying to use it for the first time.
I am unable to log onto the server getting the following message.
"A network-related or instance specific error occurred while establishing a connection to SQL Server.
the server was not found or was not accessible............................."
Is it possible to login via an IP address ? if so which IP address ?Many thanks
Ian.
0
Using SQL..specifically the xp_cmdshell
I would like to move a file to a folder during the loop in my stored proc.

So I declared the following

DECLARE @cmd varchar(500);

Then in my Try catch I would like to do the following.

Essentially on a successful try I want to move to one place...else if its in the catch I want to move it to another.

 BEGIN TRY
				EXEC dbo.uspInsertRecordTableExists @TableName,@ReqID;			
				
				Insert into SharePoint2007Archive.dbo.Attachments
				(RequestID,Form, Document)
				select		  
				   @ReqID RequestId,
				   @TableName, 
				   xData.Col.value('*:Documents[1]','nvarchar(MAX)') Document
				from @fileData.nodes('/*:AppropriationRequestForm/*:AttachmentsTable') AS xData(Col)
				WHERE DATALENGTH(xData.Col.value('*:Documents[1]','nvarchar(MAX)')) > 0

				print 'Ending Attachment Insert' 

				SET @cmd='MOVE /Y E:\''' + @Foldername + '''\''' + @fileName +''' E:\''' + @TableName + '''\'''
			    PRINT @cmd
			    EXEC sys.xp_cmdshell @cmd;


			  END TRY
			  BEGIN CATCH
			
			  SET @cmd='MOVE /Y E:\''' + @Foldername + '''\''' + @fileName +''' E:\ReProcess\'''
			  PRINT @cmd
			  EXEC sys.xp_cmdshell @cmd;

				INSERT INTO SharePoint2007Archive.dbo.DB_Errors
				VALUES
				(SUSER_SNAME(),
				 @TableName,
				 @fileName,
				 ERROR_NUMBER(),
				 ERROR_STATE(),
				 ERROR_SEVERITY(),
				 ERROR_LINE(),
				 ERROR_PROCEDURE(),
				 ERROR_MESSAGE(),
				 GETDATE());

			  END CATCH

Open in new window



The output when it printed the @cmd was
MOVE /Y E:\'AR Archive'\'1000774.xml' E:\'AR_Archive18'\'

which is clearly wrong.

But the varibles that created the source and destination of the move are correct in this instance.


Please help.
0
I am working on converting some of my queries in MS Access to stored procedures in SQL Server 2014 but I can't seem to figure out how to convert this part of my query. Can anyone help me figure this one out?

MS Access Query: IIf(IsNull([MaterialRoundness]),"   ",Format([MaterialRoundness],".0000##")) AS MaterialRoundness1

How would I write that in a stored procedure. That is just one part of my stored procedure that I cannot figure out. So if the [MaterialRoundness] IsNull then I want to leave 2 blank spaces, otherwise load the value that is in the [MaterialRoundness] field.
0
This is something that I've had trouble with over the years.   In the script below I need to move the date script in the where statement into the select.  so I want to the the last 365 days for po_count and line_count.  This way I see all suppliers even if they have a 0. The issue is the group by as I don't want to add date_created into the group by or it make a different line for each date and I want the sum so one line per supplier.

This is my case statment but requires me to put po_hdr.date_created into the group by which is not what I want.  

case when ( po_hdr.date_created >= Getdate() - 365 ) then  Count(DISTINCT po_hdr.po_no) else 0 end AS po_count

Open in new window

case when ( po_hdr.date_created >= Getdate() - 365 ) then  Count(DISTINCT po_line.po_line_uid) else 0 end AS po_count

Open in new window



SELECT DISTINCT p21_view_contacts.first_name + ' '
                + p21_view_contacts.last_name       AS buyer,
                p21_view_supplier.supplier_id,
                p21_view_supplier.supplier_name,
                Count(DISTINCT po_hdr.po_no)        AS po_count,
                Count(DISTINCT po_line.po_line_uid) AS line_count
FROM   p21_view_supplier
       INNER JOIN p21_view_contacts
               ON p21_view_supplier.buyer_id = p21_view_contacts.id
       INNER JOIN po_hdr
               ON p21_view_supplier.supplier_id = po_hdr.supplier_id
       INNER JOIN po_line
               ON po_hdr.po_no = po_line.po_no
WHERE  ( p21_view_supplier.delete_flag

Open in new window

0

SQL

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.