We help IT Professionals succeed at work.

Microsoft SQL Server

164K

Solutions

50K

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.

Hello:

I have tried several different ways of combining my two CTEs within one "UNION ALL" statement but am having no luck.

How do I modify my syntax below to accomplish this?

Thank you!

Software Engineer

; with wip as 
(
select NULL as [LOTSEQNBR], FG.BatchNo as [FGBATCH], FG.FormulaId as [FG], FG.[Labor Cost] as [FGLABOR], FG.[OverHead Cost] as [FGOH], 
FG.[Material Cost] as [FGMAT], FG.[Labor Cost] + FG.[OverHead Cost] + FG.[Material Cost] as [FGCOST], FG.[TotalFGWeightYielded] as [FGWEIGHT], 
FG.[Labor Cost]/FG.[TotalFGWeightYielded] as [LABORLB],
FG.[OverHead Cost]/FG.[TotalFGWeightYielded] as [OHLB],
FG.[Material Cost]/FG.[TotalFGWeightYielded] as [MATLB],
NULL as [FORMULA], NULL as [FORMULAMAT], NULL AS [FORMULALABOR], NULL AS [FORMULAOH], NULL AS [Component], NULL as [LOT], NULL as [LOTQTY], 
NULL as [LOTMATUNITCOST], NULL as [LOTMATWIPCOST]
from BM_View_Dashboard_ProductionCostAnalysis FG
INNER JOIN BM010115 BOM ON FG.FormulaId = BOM.PPN_I
where FG.FormulaId = '506' and FG.BatchNo = '50418G12D' and BOM.SUBCAT_I = 1)
select [LOTSEQNBR], [FGBATCH], [FG], [FGLABOR], [FGOH], [FGMAT], [FGCOST], [FGWEIGHT], [LABORLB], [OHLB], [MATLB], [FORMULA], [FORMULAMAT], [FORMULALABOR], 
[FORMULAOH], [Component], [LOT], [LOTQTY], [LOTMATUNITCOST], [LOTMATWIPCOST],
[LABORLB]*[LOTQTY] as [LOTLABORWIPCOST],
[OHLB]*[LOTQTY] as [LOTOHWIPCOST]
from wip
; with wiplot as 
(
select * from (
select DISTINCT LOT.SLTSQNUM AS [LOTSEQNBR], NULL as [FGBATCH], NULL as [FG], NULL 

Open in new window

0
Hello all;
Windows 2016 Server CORE
Installing = SQL Server 2019

Windows Installer was not running - started it

I took the configuration.ini file from the SQL Server setup I did earlier.
I edited it to work with Server Core.
Here is the following edit.
;SQL Server 2019 Configuration File
[OPTIONS]

; By specifying this parameter and accepting Microsoft R Open and Microsoft R Server terms, 
; you acknowledge that you have read and understood the terms of use. 

IACCEPTSQLSERVERLICENSETERMS=true

; Specifies a Setup work flow, like INSTALL, UNINSTALL, or UPGRADE. This is a required parameter. 

ACTION="InstallFailoverCluster"

; Specify a default or named instance. MSSQLSERVER is the default instance for non-Express editions and SQLExpress for Express editions. 
; This parameter is required when installing the SQL Server Database Engine (SQL), or Analysis Services (AS). 

INSTANCENAME="SQL2019"

; The default is Windows Authentication. Use "SQL" for Mixed Mode Authentication. Remove ; to switch on mixed mode
SECURITYMODE="SQL"

; Specify the root installation directory for shared components.  
; This directory remains unchanged after shared components are already installed. 

INSTALLSHAREDDIR="D:\SQLShared64"

; Specify the root installation directory for the WOW64 shared components.  
; This directory remains unchanged after WOW64 shared components are already installed. 

INSTALLSHAREDWOWDIR="D:\SQLSharedx86"

; Specify the Instance ID for the SQL 

Open in new window

0
Hi Experts,

I want to update student status if the date is joindate is more than 180 days old from todays date.

Update Students Set IsNew = 0
Where joindate - todaysdate > 180days	
	

Open in new window


Thanks in advance!
0
Hi

I have to build Excel VBA code in an Excel spreadsheet that looks in another closed Excel file and finds the value at the intersection point between  the word " Sundries" which appears on a random row in column A and the word "Household" which appears in a random column in row 1.

What Excel VBA  code would I use to do this?

Thanks
0
perHello All;

I have just run into a situation where I cannot find any information on it.
Installation of SQL Server 2019 WITHOUT installing Data Quality Services
If I uncheck it, it unchecks EVERYTHING.
So, the question is...
How do you install the SQL Server Database Engine, without installing the Data Quality Services

The reason:
I am installing the "SQL Server Failover Cluster" on the 2016 Desk Experience Server.
After this, I will be installing "Add node to a SQL Server failover cluster" on the 2016 CORE Server.

From my experience, adding the NODE fails every time I have tried to install it on the core.
A person posted but without proof that the Data Quality Services does not run on Server Core.
(This is my thread about the issue)

I am building my SQL Servers Clusters from scratch, and want to get the Core servers running SQL Server database engine.
And whatever I install on the Main SQL Cluster, will filter down into the NODE installations.
So I need to be cautious about what gets installed to make sure everything is going to go smoothly on this build.
These are the typical features that get installed on the NODE's
SQLEngine, SQLEngine\Replication, SQLEngine\FullText, SQLEngine\DQ
So please, if anyone has any information on this TA or the other TA, please let me know.

Thanks.
Wayne
0
I have a datetime field that's in UTC. I need to convert it to local time.

Now, we have clients in the US that have different time zones. Clients in Europe. In Australia, etc.

I've been looking at examples and I found this one as an example : Example

Would this take care of converting UTC to correct local time?
0
I am trying to figure out an SQL query to get aggregate calculations from a data table.  The data table is a simple list of dates and data (real).  I am simply trying to get the AVG, MAX, and MIN of the top 100 data points in descending order.  There are several thousand data points in the table.  The Select statement I have used that does not work is:

SELECT TOP 100 Cur_Date, AVG(DataMean) AS DMEAN, MAX(DataMean) AS DMAX, MIN(DataMean) AS DMIN  FROM DataTable WHERE PartNumber = ? ORDER BY Cur_Date DESC.

Can anyone help?

Thanks,
0
I have a table with about 5 million records.
I would like to use MS Excel's Get External Data to retrieve records for a specific day (only a few thousand records).
The challenge is that Get External Data wants to return the entire table.
How do I create a query with Get External Data and pass a date parameter so that only the records for that day are returned.

Thanks.
0
Hello Experts,

How do I get the top 20 by Charges for each Store Name ?

I know this is just easy but I cannot figure it out.

See example attached.

Thank you for all your help.
GetTheTop20ChargesByStoreName.xlsx
0
I have an ssrs report (sql server 2014) that takes county in as a parameter (10 possible counties)

I have a file share set up for each of the counties

Would like to write a single data driven subscription that will write the output for each county to the file share dedicated to the county

is this possible?

All the searching Ive done seem to be for emails
0
Are there any way to add number days skipping Saturday and Sunday?

For example, if I add 10 days to 2020/4/1, the result is 2020/4/11. It's appreciate if I can know any good way to add days considering Saturday and Sunday.
I'm using SQL Server 2017

SELECT DATEADD(DAY, 10, '2020/4/1');

I want to get an result of 2020/4/14

2020/4/1 Wed
2020/4/2  Thr
2020/4/3  Fri
2020/4/4  Sat <- skip
2020/4/5  Sun <- skip
2020/4/6  Mon
2020/4/7  Tue
2020/4/8  Wed
2020/4/9  Thr
2020/4/10  Fri
2020/4/11  Sat <- skip
2020/4/12  Sun <- skip
2020/4/13  Mon
2020/4/14  Tue
0
Hi Expert,
I am having a big problem, The SolarWindsOrion database went to suspect mode after windows update. And we have tried all option to recover the database. No luck.

Tried repair option and it failed. Tried last full backup restore and the got the below error even resource governor is disabled.

Msg 41379, Level 16, State 0, Line 2
Restore operation failed for database 'SolarWindsOrion' due to insufficient memory in the resource pool 'default'. Close other applications to increase the available memory, ensure that both SQL Server memory configuration and resource pool memory limits are correct and try again. See 'http://go.microsoft.com/fwlink/?LinkID=507574' for more information.
Msg 1813, Level 16, State 2, Line 3
Could not open new database 'SolarWindsOrion'. CREATE DATABASE is aborted.

Any idea?
0
When running the following query, it never completes and pins the SQL Server at 100%. Found that if I comment out this below, it works fine:

AND (WOS.TAT_STOP_DATE_CUST_V2 IS NULL OR (DATEDIFF(MONTH, WOS.TAT_STOP_DATE_CUST_V2,GETDATE())<=36)) --AND WOS.TAT_STOP_DATE_CUST_V2 >= WOS.TAT_START_DATE AND WOS.TAT_STOP_DATE_CUST_V2<=GETDATE()))

I cannot find a problem with the data in these columns.

SELECT
	data_query.*,
	SUM(PROFIT.REVENUE) REV,
	SUM(PROFIT.COGS_LABOR+PROFIT.COGS_PARTS+PROFIT.COGS_SUBCONTRACTING+PROFIT.COGS_REWORKS+PROFIT.COGS_SRU_EXCH+PROFIT.COGS_MISC) COGS, 
	SUM(PROFIT.REVENUE) - sum(PROFIT.COGS_LABOR+PROFIT.COGS_PARTS+PROFIT.COGS_SUBCONTRACTING+PROFIT.COGS_REWORKS+PROFIT.COGS_SRU_EXCH+PROFIT.COGS_MISC) MARGIN_WO
		
FROM (

	SELECT WOS.WO_NUMBER,
		WOS.WO_TYPE,
		WOS.ACCT_COMPANY,
		WOS.DEPT_NAME,
		CASE WHEN WON_AUTO_KEY=2 THEN 'GSTE'
			WHEN WON_AUTO_KEY=7 THEN 'OTHER/NOT CLASSIFIED' --'AF WH - DORAL 2'
			WHEN WON_AUTO_KEY=9 THEN 'DORAL 1'
			WHEN WON_AUTO_KEY=10 THEN 'MIAMI'
			WHEN WON_AUTO_KEY=11 THEN 'DORAL 2'
			WHEN WON_AUTO_KEY=12 THEN 'ATLANTA'
			WHEN WON_AUTO_KEY=13 THEN 'PHOENIX'
			WHEN WON_AUTO_KEY=14 THEN 'LOUISVILLE'
			WHEN WON_AUTO_KEY=32 THEN 'OTHER/NOT CLASSIFIED' --'BIC POOL - DORAL 2'
			ELSE  'OTHER/NOT CLASSIFIED'
		END LOCATION,
		WOS.SHOP_TYPE,
		WOS.MFG_CODE,
		ISNULL(TECHNO.ATTRIBUTE_VALUE, '_NOT CLASSIFIED') "TECHNOLOGY",
		ISNULL(PROD_GROUP.ATTRIBUTE_VALUE, '_NOT CLASSIFIED') "PRODUCT GROUP",
		

Open in new window

0
Hi experts,

I'm using SQL Server 2019.

I have a table called Employees that looks like this:

TableEmployees.PNG
I have another table called PromotedEmployees that looks like this:
This table holds some employee names and their new title.

PromotedEmployees.PNG
This is the sql script to create the Employees table:

USE [TestDatabase]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Employees](
	[EmployeeID] [int] IDENTITY(1,1) NOT NULL,
	[LastName] [nvarchar](20) NOT NULL,
	[FirstName] [nvarchar](10) NOT NULL,
	[Title] [nvarchar](30) NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Employees] ON 

INSERT [dbo].[Employees] ([EmployeeID], [LastName], [FirstName], [Title]) VALUES (1, N'Davolio', N'Nancy', N'Sales Representative')
INSERT [dbo].[Employees] ([EmployeeID], [LastName], [FirstName], [Title]) VALUES (2, N'Fuller', N'Andrew', N'Vice President, Sales')
INSERT [dbo].[Employees] ([EmployeeID], [LastName], [FirstName], [Title]) VALUES (3, N'Leverling', N'Janet', N'Sales Representative')
INSERT [dbo].[Employees] ([EmployeeID], [LastName], [FirstName], [Title]) VALUES (4, N'Peacock', N'Margaret', N'Sales Representative')
INSERT [dbo].[Employees] ([EmployeeID], [LastName], [FirstName], [Title]) VALUES (5, N'Buchanan', N'Steven', N'Sales Manager')
INSERT [dbo].[Employees] ([EmployeeID], [LastName], [FirstName], [Title]) VALUES (6, N'Suyama', N'Michael', N'Sales Representative')
INSERT [dbo].[Employees] ([EmployeeID], 

Open in new window

0
Hi Experts,

I am working with an old MS SQL database where the date is stored as Varchar, but I keep getting the following error

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.

Open in new window


You will see that I am trying two formats, both unsuccessfull
SELECT SUM(family) AS totFam, SUM(adults) AS totAdult, SUM(Seniors) AS totSen, SUM(students) AS totStud, 
SUM(children) AS totChild, SUM(infants) AS totInf 
FROM Invoices 

WHERE CAST( CASE WHEN responseCode IS NULL OR ISNUMERIC(ResponseCode) = 0 THEN '99' else responsecode end AS INT) < 50 
AND (Company = 'CSST' OR Company = 'COMBO') 

/*AND CAST(paymentDate AS DATE) >= CAST('12/5/2019' AS DATE) AND CAST(paymentDate AS DATE) <= CAST('3/24/2020' AS DATE)*/
AND CAST(paymentDate AS DATE) >= CAST('2019-12-05' AS DATE) AND CAST(paymentDate AS DATE) <= CAST('2020-03-24' AS DATE)

Open in new window


When I do a simple SELECT ..... ,CAST(paymentDate AS DATE) AS dt I get YYYY-MM-DD

Thank you,
0
Guys,

Do you knwo when I run sqlcmd command below with -h-1 if remove header in my output results but when I

SET @SQL='SQLCMD -S Best3 -E -Q "SET NOCOUNT ON; '+@SQL+'" -o "'+@FilePath+'" -h -1 -s "," -W'


do this way ( notice without -h -1 ) which the results appears with -------- in the 2nd row, how do i get rid of these line of dots

SET @SQL='SQLCMD -S Best3 -E -Q "SET NOCOUNT ON; '+@SQL+'" -o "'+@FilePath+'" -s "," -W'


Capture.JPG
0
I need to compare two databases and find the tables that have different schema.

I don't need to compare the data. Company doesn't have Redgate. I Googled for a script and didn't find one.

Anyone has a script to find which tables have different schema?

I need to compare the stored procs too if there's a script for that.

If there's no script, I'll look into SQL Adpet (someone on the team mentioned it)
0
I was looking in our records and noticed some alarming login attempts on our SQL server.  Seems like its relentless.  Is there a way to limit the number of times someone tries to logon to our SQL server if it coming from the same IP address and being rejected.
0
I'm working with a clients on-site SQL Server and an Access front-end

Recently I started getting an error in Access when I attempted to write to one of the tables in the database.  I was unable to resolve this issue from within Access so I turned to SSMS and now when I run this query
SELECT TOP 1000 *
from tbl_Cygnet_Readings_Frequent 
order by report_DT

Open in new window

I get the error:
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command.  The results, if any, should be discarded.

Open in new window

I'm able to run:
SELECT TOP 5 * from tbl_Cygnet_Readings_Frequent 

Open in new window

and get these results.
ID     FacilityID	Report_DT                 PDIFF      PSTATIC    RGAS       LastModified               SSMA_Timestamp        TMPGAS
3198031   10521	2018-04-20 20:49:00.097   40.61012   817.1606   1917.925   2018-04-20 20:49:00.097    0x00000000003902E6    74.510755
3197797   10521	2018-04-20 21:30:05.473   42.62617   818.8284   1965.338   2018-04-20 21:30:05.473    0x00000000003901E2    75.17493
3197673   10521	2018-04-20 21:49:35.800   42.16301   819.0925   1953.901   2018-04-20 21:49:35.800    0x0000000000390165    75.59758
3197415   10521	2018-04-20 22:30:05.313   41.17471   819.7466   1930.659   2018-04-20 22:30:05.313    0x000000000039001B    76.0209
3197222   10521	2018-04-20 22:50:40.303   41.18724   820.0338   1932.077   2018-04-20 22:50:40.303    0x000000000038FF4E    75.708015

Open in new window

But when I add an Order By clause containing any field other than FacilityID (nvarchar -20), I get the error mentioned above.

I can filter the data (over 9M records) using a where clause on any of the fields, and can even generate grouped data with queries like:

SELECT cast(Pstatic as integer) as PD2, COUNT(*)
from tbl_Cygnet_Readings_Frequent
WHERE LastModified >= '2020-03-01'
GROUP BY CAST(pstatic as integer)

Open in new window

or
SELECT FacilityID, COUNT(*)
from tbl_Cygnet_Readings_Frequent
WHERE LastModified >= '2020-03-01'
GROUP BY FacilityID

Open in new window

but as soon as I try something that requires a sort, it gives me the same error:
SELECT FacilityID, MAX(LastModified)
FROM tbl_Cygnet_Readings_Frequent
group by FacilityID

Open in new window

0
I have a cloud subscription in Azure. I can create a standard VM without trouble in the portal site, but I need to create it as a dev/test VM. There is not an option to select the VM as a dev/test VM in the portal.  I was able to create an estimate which gave me all the SKU's and Resource ID's to create what I want.  How would I go about creating that exact VM?
0
Hello all.

Working with windows 2016 Core servers VMs.

To run the following on separate servers of course.

IIS and SQL server 2019.

How much memory would be good for these vm cores?

I have 3 2016 file servers that run wonderfully with only 1gb of ram.

So how much for the others?
Waynr
0
Hi,
I would like to get Full, DIFF, T-LOG for all databases backup location and Clean up times using Scripts. I do have more than 100 Production Servers. I want to automate this process. This is the requirement for me to get all types backup location as well as Clean up times. I am using ola hallengren script for backup maintenance.
0
How do I create the table has the following date format in ms sql server

child_Id | name | birthdate
1            | sam   | 12/24/2013
2            | bob    | 1/3/2016    

create table children (
child_Id int not null identity primary key,
name varchar(30),
birthdate date // I think that this is wrong
)

insert into children(name,birthdate)
values ('sam','12/24/2013')

even if we do

insert into children(name,birthdate)
values('sam',convert(varchar,'12/24/2013))

it still doesn't work.
0
What exactly does DBCC CheckDB(MyDatabase, REPAIR_ALLOW_DATA_LOSS)  do?  I currently have this running. I do not see the mdf or ldf files grow nor does the tempdb data get any larger. I do see commands being run in SQL Profiler.
0
Hi Guys
In order to update my website I need to replace 3 access tables: table1, table2 and table3.
odbc for the sql server is 'PKC'
I am looking for vba code which will
1. delete the above 3 tables
2. replace them with 3 fresh tables (same tablenames)
The vba code is to run on access module  (I am using office 365 with ms access)
Any help is appreciated
Thanks
Dory
0

Microsoft SQL Server

164K

Solutions

50K

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.