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

Hello, all.

OK, working with SQL Server 2019, and it works wonderfully with accented characters.
Dvořák Hall
It displays to the page just fine.
However, when I try to read from a URL QueryString, it's not working.
I get
Dvo
Instead of the full name.

I have at the top of my main page.
<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<%
Response.ContentType = "text/html"
Response.CodePage = 65001
Response.CharSet = "UTF-8"
%>
 

Open in new window


But it still will not get the URL: /Page/Dvořák_Hall
And display it correctly to the page, to use against the database query.

Any suggestions?
Wayne
0
Hi,

Please see https://www.experts-exchange.com/questions/29173210/SSRS-sort-on-datediff.html

Is there also a way to do a plain sql query (not ssrs)which puts the date diff in a separate (new) column and shows it it days,hours,minutes?

J
0
Hello all;

The query I am working with, I need a subquery to send back multiple records.
I know I have done this before in the past, but cannot find the code or remember what was done.

This is my query. (Of course, it gives this error: Subquery returned more than 1 value.)

SELECT   

(SELECT     distinct(Artists.ArtistName)
FROM            Albums INNER JOIN
                         Artists ON Albums.ArtistID = Artists.ArtistID INNER JOIN
                         Tracks ON Albums.AlbumID = Tracks.AlbumID INNER JOIN
                         Studios ON Tracks.TrackID = Studios.TrackID
WHERE        (Studios.StudioID = Studio.StudioID)) as Artists,


Studio.StName, ArtistPics.PicsPath
FROM            ArtistPics RIGHT OUTER JOIN
                         Studio ON ArtistPics.StudioID = Studio.StudioID
WHERE        (Studio.StName = 'Studio_308')

Open in new window


I need to return all the ArtistName's with a comma between them.

Thanks for any assistance on this one.
Wayne
0
i'm using sql server 2019.

I have a table that looks like this:

Table.PNG
This is the script to create the table.

USE [TestDatabase]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[EmployeesReport](
	[EmployeeID] [int] IDENTITY(1,1) NOT NULL,
	[LastName] [nvarchar](20) NOT NULL,
	[FirstName] [nvarchar](10) NOT NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[EmployeesReport] ON 
GO
INSERT [dbo].[EmployeesReport] ([EmployeeID], [LastName], [FirstName]) VALUES (1, N'Davolio', N'Nancy')
GO
INSERT [dbo].[EmployeesReport] ([EmployeeID], [LastName], [FirstName]) VALUES (2, N'Fuller', N'Andrew')
GO
INSERT [dbo].[EmployeesReport] ([EmployeeID], [LastName], [FirstName]) VALUES (3, N'Leverling', N'Janet')
GO
INSERT [dbo].[EmployeesReport] ([EmployeeID], [LastName], [FirstName]) VALUES (4, N'Peacock', N'Margaret')
GO
INSERT [dbo].[EmployeesReport] ([EmployeeID], [LastName], [FirstName]) VALUES (5, N'Buchanan', N'Steven')
GO
INSERT [dbo].[EmployeesReport] ([EmployeeID], [LastName], [FirstName]) VALUES (6, N'Suyama', N'Michael')
GO
INSERT [dbo].[EmployeesReport] ([EmployeeID], [LastName], [FirstName]) VALUES (7, N'King', N'Robert')
GO
INSERT [dbo].[EmployeesReport] ([EmployeeID], [LastName], [FirstName]) VALUES (8, N'Callahan', N'Laura')
GO
INSERT [dbo].[EmployeesReport] ([EmployeeID], [LastName], [FirstName]) VALUES (9, N'Dodsworth', N'Anne')
GO
SET IDENTITY_INSERT [dbo].[EmployeesReport] OFF
GO

Open in new window



So far i have this query:

DECLARE @IsCurrentMonth VARCHAR(100), @LastName VARCHAR(100), @FirstName VARCHAR(100)
SET @LastName = 'Fuller'
SET @FirstName = 'Laura'
SET @IsCurrentMonth = 'Yes'

SELECT [EmployeeID]
      ,[LastName]
      ,[FirstName]
FROM [TestDatabase].[dbo].[EmployeesReport]

Open in new window



for the WHERE clause I want to use an CASE statement or if else statement.


What i need is the following,

if the parameter called @IsCurrentMonth equals 'Yes' i want the where clause to look like this. Filter by LastName.

-- if @IsCurrentMonth equals Yes use this where clause
WHERE [LastName] = @LastName



if the parameter called @IsCurrentMonth equals 'No' i want to the where clause to look like this. Filter by FirstName.

-- if @IsCurrentMonth equals No use this where clause
WHERE [FirstName] = @FirstName


I know I can just put the whole query inside an if statement, but I know what to apply the CASE statement to the WHERE clause.

What's the best syntax for this?
0
Hello All,

I need to return the first tracking number from a table per order (SOP Number) but I keep getting all the tracking numbers. The lowest DEX_ROW_ID per order is the tracking number I need.

Any help?

This is what I have so far:


SELECT DISTINCT
	SalesLineItems.[SOP Type] ,
       SalesLineItems.[SOP Number] ,
       SalesLineItems.[Actual Ship Date] ,
       SOP10107.Tracking_Number,
	   (SELECT TOP 1 dbo.SOP10107.DEX_ROW_ID) AS ID
  FROM
       SalesLineItems LEFT OUTER JOIN SOP10107
       ON SalesLineItems.[SOP Number] = SOP10107.SOPNUMBE

Open in new window



Results

Order      9877      2020-01-17 00:00:00.000      777xxxxx8696                                   3386856
Order      9877      2020-01-17 00:00:00.000      777xxxxx9420                                   3386857


Thanks,
0
I am adjusting a VB6 statement in which the application is making a request from the SQL database and the value contains quotation marks in it.  The result is an error:  Incorrect syntax near 'value'  

The requesting statement from VB6 is:

select count(*), Max(Grouping) from tblOrgHours where Narrative = '" & Text1 & "'"

If Text1 contains quotation marks in it then the error is produced.  How can I avoid this?
0
I am getting a error must declare the scalar variable "@qualityControl "

I think it's because in c# i have it as a checkmark box and in sql it is a bit.  how do I arrange the Parameter? see code.

using (SqlConnection SqlConn = new SqlConnection
                     ("Data Source = gcm10; Initial Catalog = Vendor; Integrated Security = True"))
            {
                string query = "Insert into vendor_approval(name, add1, add2, city, state, zipcode, phone_number, fax_number, contact_name, product_vendor, cert_quality, quality_assurance, product_sample, quality_control, specified_inspection, certificate_conformance, inspection_testing, supplier_testing, quality_system)" +
                    "values (@name, @add1, @add2, @city, @state, @zipcode, @phone_number, @fax_number, @contact_name, @product_vendor, @cert_quality, @Quality_Assurance, @Product_Sample, @qualityControl, @specified_inspection, @certificate_conformance, @inspection_testing, @supplier_testing, @quality_Testing)";
                SqlCommand cmd = new SqlCommand(query, SqlConn);
                cmd.Connection.Open();
                cmd.Parameters.AddWithValue("@name", Ven_Name.Text);
                cmd.Parameters.AddWithValue("@add1", Add_1.Text);
                cmd.Parameters.AddWithValue("@add2", Add_2.Text);
                cmd.Parameters.AddWithValue("@city", City.Text);
                cmd.Parameters.AddWithValue("@state", State_list.Text);
                …
0
Hi! I'm looking at a crime dataset and I want to create a query that finds the block of each district (there are 22 total) with the highest number of gun-related crimes. I think I have to create a subquery but not really sure how to do this. I tried creating an alias with mycount but that also didn't work.

My most successful attempt is:
gun='%GUN%'
try:
    cursor.execute("SELECT block, count(block) AS MYCOUNT FROM crimes WHERE DESCRIPTION::text LIKE %s GROUP BY block ORDER BY count(block) DESC",[gun])
    rows4=cursor.fetchall()
except:
    db_connection.rollback()
Annotation-2020-02-16-095100.pngand it creates the attached file. I want to select only the 22 blocks that have the most gun-related crimes in their corresponding district.
0
I am using this data set query that is generating error , The dated diff function requires 3 arguments(s). Incorrect syntax near Label 1. Incorrect syntax near 'Group Order'. The Query generates data for a DemographicsPar2(parameter. I am building a ssrs has board that uses this parameter.

SELECT DISTINCT 
                  'Age' DemographicGroup, 
CASE 
WHEN DATEDIFF(YY, DOB, @ReportDate) < 25 THEN '<=25'

 WHEN DATEDIFF(YY, DOB, @ReportDate) BETWEEN 25 AND 35 THEN '26-35' 
WHEN DATEDIFF(YY, DOB, @ReportDate) BETWEEN 36 AND   45 THEN '36-45' 
WHEN DATEDIFF(YY, DOB, @ReportDate) > 45 THEN '46+' END Label, CASE 
WHEN DATEDIFF(YY, DOB, @ReportDate) < 25 THEN 1 
WHEN DATEDIFF(YY, DOB, @ReportDate) BETWEEN 25 AND 35 THEN 2 
WHEN DATEDIFF(YY, DOB, @ReportDate) BETWEEN 36 AND 45 THEN 3 
WHEN DATEDIFF(YY, DOB, @ReportDate) > 45 THEN 4
END GroupOrder

INTO        #Demo2
FROM     [dbo].[Dim_Borrower]
UNION
SELECT DISTINCT 'Marital Status', MaritalStatus, 1
FROM     [dbo].[Dim_Borrower]
UNION
SELECT DISTINCT 'Race', Race, 1
FROM     [dbo].[Dim_Borrower]
UNION
SELECT DISTINCT 'Sex', Sex, 1
FROM     [dbo].[Dim_Borrower]
                      SELECT *, DemographicGroup + ' - ' + Label RealLabel
                      FROM     #Demo2
                      WHERE  DemographicGroup IN (@Demographics1)
                      ORDER BY DemographicGroup

Open in new window



When I run this query on the SSRS query Designer, the output shows Three columns. DemographicGroup / Label / GroupOrder/ Real Label ..
0
I'm using SQL Server 2019.

I have a table that looks like this:

1_TestTable3.PNG
Here is the script to create this table.

USE [TestDatabase]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TestTable3](
	[RecordID] [int] NULL,
	[FromDate] [smalldatetime] NULL,
	[ToDate] [smalldatetime] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[TestTable3] ([RecordID], [FromDate], [ToDate]) VALUES (2, CAST(N'2020-01-26T00:00:00' AS SmallDateTime), NULL)
GO
INSERT [dbo].[TestTable3] ([RecordID], [FromDate], [ToDate]) VALUES (3, CAST(N'2019-11-14T00:00:00' AS SmallDateTime), CAST(N'2020-01-19T00:00:00' AS SmallDateTime))
GO
INSERT [dbo].[TestTable3] ([RecordID], [FromDate], [ToDate]) VALUES (4, CAST(N'2019-08-19T00:00:00' AS SmallDateTime), CAST(N'2019-11-12T00:00:00' AS SmallDateTime))
GO
INSERT [dbo].[TestTable3] ([RecordID], [FromDate], [ToDate]) VALUES (5, CAST(N'2019-04-12T00:00:00' AS SmallDateTime), CAST(N'2019-08-18T00:00:00' AS SmallDateTime))
GO
INSERT [dbo].[TestTable3] ([RecordID], [FromDate], [ToDate]) VALUES (6, CAST(N'2019-02-05T00:00:00' AS SmallDateTime), CAST(N'2019-04-11T00:00:00' AS SmallDateTime))
GO

Open in new window




My existing query looks like this.

----- input parameter -----
DECLARE @HireDate SMALLDATETIME
SET @HireDate = '01/29/2020'
----- input parameter -----
----- query -----
SELECT [FromDate]
FROM [TestDatabase].[dbo].[TestTable3]
WHERE [FromDate] < @HireDate
----- query -----

Open in new window


The result of this query is this:

2_ExistingQueryResult.PNG

That's not my desired result. Depending on the input date is should only return 1 FromDate.

Here is the criteria explanation:

3_QueryCriteriaDescription.PNG


My Desired results should be this:


4_DesiredResults.PNG


How do i revise my existing query to get the desired results?
3_QueryCriteriaDescription.PNG
4_DesiredResults.PNG
0
I went to run a SQL script today that I have run for years and received this message.
Database ID 11, page (1:10932), slot 0, link number 1028 is invalid. Run DBCC CHECKTABLE.

I have run  DBCC CHECKTABLE on the 5 tables involved in the script and none come back reporting any issues.
I also ran DBCC CHECKDB(database) and that returned nothing either.

Any other DBCC UTILITIES or parameters i can run to find the problem?
0
I'm using Sql Server 2019.

I have a table that looks like this.

Question1Table.PNG
This is the script to create the table.

USE [TestDatabase]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TestTable2](
	[RecordID] [int] NOT NULL,
	[FromDate] [smalldatetime] NULL,
	[ToDate] [smalldatetime] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[TestTable2] ([RecordID], [FromDate], [ToDate]) VALUES (1, CAST(N'2020-08-05T00:00:00' AS SmallDateTime), NULL)
GO
INSERT [dbo].[TestTable2] ([RecordID], [FromDate], [ToDate]) VALUES (2, CAST(N'2020-01-26T00:00:00' AS SmallDateTime), NULL)
GO
INSERT [dbo].[TestTable2] ([RecordID], [FromDate], [ToDate]) VALUES (3, CAST(N'2019-11-14T00:00:00' AS SmallDateTime), CAST(N'2020-01-19T00:00:00' AS SmallDateTime))
GO
INSERT [dbo].[TestTable2] ([RecordID], [FromDate], [ToDate]) VALUES (4, CAST(N'2019-08-19T00:00:00' AS SmallDateTime), CAST(N'2019-11-12T00:00:00' AS SmallDateTime))
GO

Open in new window



I only want the record with the earliest FromDate whose ToDate is NULL

So my desired result is this.

Question1DesiredResult.PNG

How do i revise this query to get that desired result? I was thinking of using MIN but not sure.

SELECT [RecordID]
      ,[FromDate]
      ,[ToDate]
FROM [TestDatabase].[dbo].[TestTable2]
0
I have an application in VB.Net using a connection via ADODB.connection.  We are in the process of moving our "stuff to the cloud".  The new server is Server 2016 and the database is SQL Server 2008 Express.  I have enabled the protocols in SQL Server for Named Pipes, TCPIP.  It appears that I have the Server/Database/User/Pwd info correct, but the connection object is not supported?

The connection string is similar to this:
"Driver={SQL Server};Server=SQL1\SQLEXPRESS;Database=Orders;UID=sa;pwd=Adg991!; providerName=System.Data.SqlClient"

Thanks for you help!
0
I have been following several sources to set up an Oracle DBlink to select from a SQL Server database.

Oracle Version is 12.1.0.2.0 Standard Edition in Windows 2012 Datacenter R2 64-bit

SQL Server version is:  SQL Server 2016 (SP2-GDR) (KB4505220) - 13.0.5101.9 (X64)   Jun 15 2019 23:15:58   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows Server 2012 R2 Datacenter 6.3 <X64>

The ODBC set up Tests successfully.  I've named it "mysqlServer"

My Oracle Server's name is:  MyOraServer

Here's what I've done:

Step 1:  Added the following entries in the %Grid_Home%\network\admin\listener.ora

SID_LIST_LISTENER=
  (SID_LIST=
      (SID_DESC=
         (SID_NAME=mySqlServer)  <-- the name of the ODBC connection
         (ORACLE_HOME = D:\app\oracle\product\12.1.0\dbhome_1)
         (PROGRAM=hsodbc)
       )
      )


Step 2:  Created an init file in the %ORACLE_DBHOME%\hs\admin folder called "inithsMySqlServer.ora" with the following content:

HS_FDS_CONNECT_INFO = mySqlServer    <-- the name of the ODBC connection
HS_FDS_TRACE_LEVEL = off



Step 3:   Made the following entry in the %ORACLE_DBHOME%\network\admin\tnsnames.ora file:

SQLServerDB  =
  (DESCRIPTION=
    (ADDRESS=(PROTOCOL=tcp)(HOST=MyOraServer)(PORT=1521))
    (CONNECT_DATA=(SID=mySqlServer))  <-- the name of the ODBC connection
    (HS=OK)
  )


Step 4:  Created a DBLink in Oracle:

CREATE PUBLIC DATABASE LINK SQLServerDB CONNECT TO "myusername" …
0
How do I rewrite my SQL to put the 2 rows into 1 row.

I'm looking for 2 columns for Registered and NotRegitered in 1 row.

Here is a cut down example:

Declare  @myTable TABLE(dID int, SiteGroup varchar(25), Area varchar(2),  Flag bit)

  Insert into @myTable Select 1,1,'A1', 0
  Insert into @myTable Select 2,1,'A1', 1
  Insert into @myTable Select 3,1,'A1', 1
  Insert into @myTable Select 4,1,'A1', 0
  Insert into @myTable Select 5,2,'A2', 1
  Insert into @myTable Select 6,2,'A2', 1
  Insert into @myTable Select 7,2,'A2', 0
  Insert into @myTable Select 8,2,'A2', 1
  Insert into @myTable Select 9,2,'A1', 0
  Insert into @myTable Select 10,2,'A1', 0



Select

case when Flag = 0 then Count(Flag) End NotRegistered,
case when Flag = 1 then Count(Flag) End Registered

From  @myTable

Group By Flag


Andy
0
I want to convert a table column to DATETIME, I used alter table name [Dim_Loan] alter column LoanDate datetime, got an error, Conversion failed when converting date and/or time from character string . The column LoanDate is varchar(13), LoanDate column  has this output Jan 11 2020 1, am not sure what the 1 stands for. The Loan Date is being used in a store procedure  as a  Report Date with datetime as its data type
0
Hi,

I have a GUI which talks to a sql db backend via powershell and sql queries.

I execute queries remotely but now discovered stored procedures and views.
Created some views and a stored procedure.

What is the specific purpose of each item, the difference and what should I use for my gui?
Also, can I use the view for ssrs report?

J
0
We have a piece of third party software that was recently updated, including updates to the SQL database.  We also have a Crystal Report that makes a call to a stored procedure in this DB and now the report fails because something with the sproc has changed and I don't know what.  

To start with, here is the piece of code that execs in the Crystal Report:

DECLARE @SDATE DATETIME = NULL
DECLARE @EDATE DATETIME = NULL
SET @SDATE = dateadd(day, -(datepart(dw, getdate()) + 7), CONVERT(date,getdate()))
SET @EDATE = dateadd(day, -(datepart(dw, getdate()) + 1), CONVERT(date,getdate()))

exec software.dbo.sp_test @StartDate = @SDATE, 
	@EndDate = @EDATE, 
	@LocationCSVList='398EMD5F-B4D0-4AD7-88D8-4RTC721875C9,DE50439F-42CA-4158-BB94-8D172F442D89,B669534F-C021-4D2B-8F0F-E54842C94D27,B40AE910-BD62-40CC-99A3-F065MM8C80B3'

Open in new window


When this executes against the sproc now, we receive an error that it's unable to convert the varchar value to data type int.

Here is the sproc as it stands now:

USE [software]
GO

DECLARE @RC int
DECLARE @StartDate datetime
DECLARE @EndDate datetime
DECLARE @LocationCSVList varchar(1000)

-- TODO: Set parameter values here.

EXECUTE @RC = [dbo].[sp_test] 
   @StartDate
  ,@EndDate
  ,@LocationCSVList
GO

Open in new window


I can't change the code for a sproc in a third party app DB, so how do I change the code I'm using in Crystal to still get the results I need and convert that data type?
0
I have a FIREBIRD  data base connected by OBDC

Using VB6 i can call  customer data wich is in 2 tables located

first call    SELECT * FROM stammkunde where KUNDENNR = '         7'
will return  only the name  and phone and of customer and email in that table
and a second key     ADRKUNDID  value  to use in next call  

to make second call to other tabel  stammadresse
SELECT * FROM stammadresse  where  ADRESSID  =  ADRKUNDID



 this second table stammadresse contains

STRASSE  from   (stammadresse    street
PLZ            (from  (stammadresse    Zip
ORT           (from  (stammadresse    City
etc.

I need to update in both table the here showed fields with new data
please advise teh syntax for both SQL 's   to realize this update in VB6
Thanks
0
Hello Experts,

After lot of efforts I did my SSIS pkg working in my local, which connects to Oracle DB using Oracle.ManagedDataAccess.Client dll(32-bit). After deploying it to 2016 server it says below and also I was using script task in the SSIS pkg 2017 and the target version is sql serer 2014.

Cannot load script for execution.



Thanks,
ASPDEV
0
I finally have the end results working properly.  The statement looks in three different places in a specific order to find the answer.  The first place that is not null populates the field.

However, now I must build a validation file on at least one of the resulting fields in the Query.

I have no idea how to add a COUNT(*) to the statement.  Everything errors out and all the answers online do not fully explain how to accomplish this.

I need a Total # of Lines in the query as my result.

Attached is my query.  It is MS SQL Server Management Studio v17.4.[embedCostItems.xlsx=file 1443596]
I have also attached some sample data.  My answer for the Count(Coalesce etc) should be 37, because there are 37 rows in the excel document.
HB_CostItem_ForEE.sql
0
I'm looking at database monitoring and seeing that nearly all of our SQL databases report a high degree of fragmentation in their indexes. 99.8-99.9% fragmented is not uncommon. Can this play significantly into poorer performance for database interactions from .NET web servers which integrate with SQL?
0
I am new with SQL Server.  

A while ago I installed SQL Server 2016 in Windows 2012 R2.   I set up the maintenance plans to do daily backups of the database and transaction logs.  

Those maintenance plans worked just fine.

I recently decided I wanted to a text file of the backups to get created.   so I modified each maintenance plan to send a report (.txt) to the "C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Log" folder.

I did this by modifying the maintenance plan and clicking the icon for Reporting and Logging.  Then selecting the checkbox for "Generate a Text file report", select Create a new file, then specified the path above.

Each time the maintenance plan runs, I see an error in the history:

Access to the path 'C:\\Program Files\\Microsoft SQL Server\\MSSQL13.MSSQLSERVER\\MSSQL\\Log\\Backup DBs_Subplan_1_20200207170004.txt' is denied.

I tried changing the path to the same one the backups go to but still got the same error.  

Very perplexed.  

Why would it be able to write all other logs to the "C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Log" folder but not the backup reports?
0
Hi,

I'd need a sql query which matches:
-vendor,name,version of SQLTableSoftware
-to a SQLTableSCCM then get the status,deployedcollection of that SCCM

So that would be <pseudoscript>

---

select * from tblSoftware

WHERE Vendor,Name,Version are equal to  --Example Mozilla,Firefox,60.0
Vendor,Name,Version in tblSCCM -- Example Mozilla,Firefox,60.0

When Match then show Status and DeployedCollection of tblSCCM
If no match, show '' -- Example Mozilla,Firefox,60.0,Deployed,CollectionXYZ
0
hi,

I keep finding D:\ is full quickly and I see the Customer Experience Improvement Program (CEIP) is on for both SQL server 2017 and SQL 2019, I have stopped the service but where is the log file it create ?

I want to remove them all, is it possible ?
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.