Microsoft SQL Server

163K

Solutions

49K

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

combine 2 queries for a totals summary

I'm trying to combine 2 queries to display as below

segment                           no_of_lines           ColumnSum as Lines_with_Sales
Commercial Services         50634                       6453
Contractors                          82612                       7562
Government                         132158                     2345
Healthcare                          64542                       55443
Hospitality                         23028                         1221
International/Export         90226                        3432
Light Manufacturing         324910                         654
Natural Resources          27150                        3451

SELECT
[SEGMENT]
,COUNT([NOOFLINEITEMS]) AS NO_OF_LINES
  FROM [Metrics].[dbo].[HEADER_DETAIL]
  GROUP BY [SEGMENT]


SELECT	a.*
,		ColumnSum	= CAST( a.EXACT		as integer )
				+ CAST( a.FUNCTIONAL	as integer )
				+ CAST( a.DM		as integer )
				+ CAST( a.MATCH		as integer )
FROM
(	

SELECT
[SEGMENT] 

,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
 
 FROM [Metrics].[dbo].HEADER_DETAIL]

) A  

SEPARATES_COLUMNS   is a function

Open in new window


reference EEQ..
https://www.experts-exchange.com/questions/29143457/sum-fields-after-being-separated-into-new-fields-from-query.html?headerLink=workspace_answered_questions

Thanks
fordraiders
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.

If you had an old Asp.net web forms c# application using sql server for back end that needed to be re-written to use modern technologies , what would you use?

But you must use sql server 2016.

Would you use asp.net web forms?
Asp.net mvc 5?
Asp.net core?
Angular front end?

Also what technology would you use to connect to sql server?
Web API? Entity Framework dB first? Entity Framework code first? Any other way?
0
Hi,

I need to write a SQL server stored procedure for below scenario.

Select the 3 key columns(ex : id,name,create date) from Table A and then seek the record in table B
 if the records is exist in table B then
    select id,name, create date and resource id from table B insert and then into Table c if not exists in table c
else
 do nothing

Much appreciate your support
0
Microsft SQL Server 2017 - Attaching to 2014 database files.

I need to attach the DB data and log files from a former server (no longer avail).  The files are SQL 2014.  When attempting to attach to the database, I can specify the Data file, but the option to specify the existing log file is not available.  If I proceed it attempts to create a new log file.  What steps should be done to include the old log file to get the entire DB?

Clarification: Scripting works fine.  I'm wondering about the newer interface on SSMS not having the section to specify the log file location.
0
I have an array of bib numbers (AsgndBibs) on our local machine and I want to find any of those that are not in the db on the server so I can insert them.  I have the insert down.  I just want to create an array of bibs that are not on the server (BibsToInsert).  The approach I am using requires opening a recordset for each bib in the array to see if it exists in the server.  There must be a better way.  I would appreciate any help I can get.  Thanks.  Below is how I am populating the BibsToInsert right now.

        j = 0
        ReDim BibsToInsert(0)
        For i = 0 To UBound(AsgndBibs) - 1
            Set rs = New ADODB.Recordset
            sql = "SELECT Bib FROM PartRace WHERE RaceID IN (" & sEventRaces & ") AND Bib = " & AsgndBibs(i)
            rs.Open sql, srvr_conn, 1, 2
            If Not rs.RecordCount > 0 Then
               BibsToInsert(j) = AsgndBibs(i)
               j = j + 1
               ReDim Preserve BibsToInsert(j)
            End If
            rs.Close
            Set rs = Nothing
        Next i

Open in new window

0
here is my situation

i have 3 sql server tables
one is a customer table
one is an orderhistory table
and

one is a log table

the customer table has customerid along with name mailing address and email address

the orderhistory table has customer id and order total amount and order date

the log table has 2 fields
customerid
and insert date

I need to write an entry into the log table with the customerid for those customers that have placed an order over $50 in a given month that have an email address

this process is once a month on the 15


i need to check the log table before writing an entry to make sure customerid does not already exist

here is some sample data

customer table

customerid emailaddress
12345           jdoe@gmail.com




orderhistory table

customerid  order total orderdate

12345    40   11/20/2018
12345    60    02/19/2019
12345    70    04/14/2019

log table would have an entry
for customer 12345 since his 60 order on 02/19/2019

would have generated an entry

therefore
when the process ran on 4/15. i would not have wanted another entry written for customer 12345 written
even though there was an order over 50 placed on 4/14/2019

i was thinking of needing to combine a sql not in for the order amount rule and a not exists to determine if record already exists

looking for an example though

thanks!
0
i have a query "select firstname, lastname, state from customers" nut I want to read like below

CA  john smith, mary smith
TX   john chan, mary chan
FL   John Khan, mary khan

Is it possible in MS SQL Server? Thanks
0
Reinstalled Server 2008 R2 Standard  and joined to domain.   Workstations that reboot aren't being updated with the domain name resolution for our PITSQL server.    Ping'ing from the workstation that can't open our SQL app, is unsuccessful.   Workstation is running Windows 7 Pro, The Domain Controller is SBS2011.
0
Hi all

My current employer has a number of SQL Server 2008R2* tales whose clustered primary key is a uniqueidentifier column (usually with FILLFACTOR = 90) that either our front-end randomly generates, or is INSERTed via a stored procedure with passed parameters that generates a NEWID() value.  This all happened way before my time.

Potentially stupid question:  Since clustered primary keys physically store the data in order, and the uniqueidentifiers are randomly generated, should I change the clustered primary key to a non-clustered, and save all the time spent on page splits inserting values when the order truly does not matter?

EE-question-uniqueidentifer-clustere.jpg
Thanks in advance.
Jim

*  We're in the middle of a 2016 conversion.
0
Hi!

I wish you could help me with the following question about SQL Server and a performance problem I have:


Old Server:

I have a closing and opening process on my system that takes about 11 minutes.  


My storage is:
3Par 7200.
Raid5 15K SAS ( 35 disks)
Block Size: 128KB

IOPS  can deliver as maximum for Reading and Writing in a native way:  IOPS 9950


 3par-7200.png

The server has the version of SQL Server 2008 R2 Enterprise Windows with 2008 R2 Enterprise. It has 2 Processors with 16 Cores but the SQL Service only recognizes 16 cores.  

I haven't been able to capture the IO statistics or specific time in the processes because this process has many cursors and makes the SQL Server crash. Neither have I been able to capture the execution plan by the cursors that the process has.

New Server:


The same process takes 8 minutes but writing IOPS rise from 800 to 2000. Reading IOPS decrease from 1000 to 400.

 3Par 8200 with the following features:

Raid5 SAS of 10K ( 10 disks)   IOPS 2568Max. For CPG FC_Raid5_SinAO
400 Gb SSD disk arrays ( 8 disks)  IOPS 100239 Max For CPG_SSD_Raid5
Block Size: 32KB

IOPS can deliver as maximum for Reading and Writing in a native way: IOPS 2568
IOPS with AO 30,000 Max Reading ( The writing processes depend on the Native IOPS of the CPG used in this case FC_Raid5_10K )

3par-8200.png
The server has the version of SQL Server 2016 Enterprise Windows with 2012 R2…
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.

Hi,
I am importing data from a CSV file into SQL Server Express and one of the columns contains data format  £5,420 or  €5,420
Can the SQL table column be modified to accommodate data ? I would prefer it to be converted to format  5420 or  5420 if possible.
How can I go about it.
Thanks
Ian
0
Please Im developing web Api and im having issues with my stored procedure
DECLARE @json NVARCHAR(MAX)

SET @json=
'{ "cargo_type": 1
 , "operation_step": "Ship Shide"
 , "terminal": 1
 ,"budge_id" : "operator TITLE io"
 , "cargo_id": 139378
 , "values": [
{"id": 1, "value": 2 }
,{"id": 2, "value": true }
,{"id": 6, "value": "B2349"}
  , {
     "id": 7
, "value": "Yusuf Bamidele"
}, {
  "id": 9
  , "value": "Lorem Ipsum has been the industrys"
  } ]
  , "selections": [{ "id": 8, "selected_options": [1, 2] }]
  , "damages": [{ "id": 3
, "type": 9
, "location": "Border"
, "size": "Small" }]
   }';

 SELECT cargo_type,operation_step,terminal,budge_id,cargo_id, a.id ID_ANDROID_FIELDS_DEVICE,[VALUE]  INTO #JSON
     FROM
    OpenJson(@json)
    WITH (cargo_type int N'$.cargo_type',terminal int N'$.terminal',budge_id NVARCHAR(50) N'$.budge_id',cargo_id bigint N'$.cargo_id',operation_step NVARCHAR(100) N'$.operation_step', [FIELDS] NVARCHAR(MAX) N'$.values' AS JSON)
      OUTER APPLY
    OpenJson([FIELDS])
    WITH (id INT N'$.id',value NVARCHAR(30) N'$.value') a


 DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX); SET @columns = N''; SELECT @columns += N', p.' + QUOTENAME(ID_ANDROID_FIELDS_DEVICE)
  FROM (SELECT o.ID_ANDROID_FIELDS_DEVICE
    FROM #JSON AS p
     INNER JOIN Android.ANDROID_FIELDS_DEVICE AS o
     ON p.ID_ANDROID_FIELDS_DEVICE = o.ID_ANDROID_FIELDS_DEVICE
  GROUP BY p.VALUE,o.ID_ANDROID_FIELDS_DEVICE) AS x; SET @sql = N'
SELECT ' + STUFF(@columns, 1, 2, 

Open in new window

0
We have a clean install of Windows Server 2008 Standard R2 and SQL Server 2014.    One of the *.bak files was restored from our damaged RAID 5 drives.    I've found instructions on how to restore with SQL 2005, but nothing for 2014.    I have very little knowledge of SQL, so I'm unsure whether I need to create a new database prior to the restore or whether it will recognize that it's not there and create it during the restore process.  The developer who installed and configure our database a couple of years ago is out of the country and not near a computer.  Can someone walk me thru it, or point me to instructions?  

Thanking you in advance.
0
The following query is returning a null for the column description.

Please recommend any other way to get the value.
I think the html is getting in way or my xquery statement needs refining

Here is the entire example:

IF OBJECT_ID('tempdb..#T1') IS NOT NULL
					DROP TABLE #T1

IF OBJECT_ID('tempdb..#T2') IS NOT NULL
					DROP TABLE #T2

IF OBJECT_ID('tempdb..##tmp') IS NOT NULL
					DROP TABLE #tmp

IF OBJECT_ID('tempdb..##T1') IS NOT NULL
					DROP TABLE ##T1
IF OBJECT_ID('tempdb..##T2') IS NOT NULL
					DROP TABLE ##T2
IF OBJECT_ID('tempdb..##T3') IS NOT NULL
					DROP TABLE ##T3
IF OBJECT_ID('tempdb..#tmpXML') IS NOT NULL
			 DROP TABLE #tmpXML
IF OBJECT_ID('tempdb..#tmpXML2') IS NOT NULL
			 DROP TABLE #tmpXML2


DECLARE @fileName varchar(100)
DECLARE @TableName varchar(Max)
DECLARE @FolderName varchar(100)


DECLARE @ReqID nvarchar(50)
DECLARE @ProjectDescription nvarchar(MAX)

DECLARE @SQLString nvarchar(MAX);

DECLARE @ProjectXML  XML

DECLARE @cols NVARCHAR(MAX);
DECLARE @query NVARCHAR(MAX);
DECLARE @query2 NVARCHAR(MAX);

SET @TableName = 'BringDownCertificate';
SET @FolderName = 'BDCERT'

declare @fileData xml = '
<my:myFields xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
xmlns:dfs="http://schemas.microsoft.com/office/infopath/2003/dataFormSolution" 
xmlns:tns="http://tempuri.org/" xmlns:xhtml="http://www.w3.org/1999/xhtml" 
 xmlns:http="http://schemas.xmlsoap.org/wsdl/http/" 
xmlns:soap12="http://schemas.xmlsoap.org/wsdl/soap12/" 

Open in new window

0
My boss is unimpressed by the fact that AlwaysOn Availability Groups on our non-clustered environment can only do a Manual Failover in the event of disaster.

I have been tasked with redesigning our setup to support Automatic Failover, and everyone knows you need a clustered environment to do that (right?)

However, before even getting down to the details, Automatic Failover requires that the Availability Group be set up with Synchronous Commit, which is obvious, but in the testing phase of our current AG-setup, we discovered that the performance (responsiveness, readiness) of our instances were dramatically slower on Synchronous Commit vs Asynchronous Commit.

To please my boss, I would have to set up our AG to have the responsiveness of Asynchronous Commit while having the high availability of Automatic Failover.  Is there any hope for me?

Our Environment:

Servers:
Intel Xeon 2643 v2 x 2
1.50 TB RAM
All-flash Samsung Enterprise SSD storage in RAID 10
Windows Server 2016 Datacenter
SQL Server 2017 Enterprise
Mellanox Dual 40G NIC

Network:
Cisco Nexus 3132X 40G (access layer)

A typical database in our environment is 300GB-1TB, and tables can easily have 10 million records.
0
Hi all - I know this may sound like a simple/stupid question but I'm having issues printing an entire database diagram I created in SQL Server Express 2017.  I'm able to change the page setup so that I can get it all to print on a reasonable number of legal sized pages but when I send the diagram to print it only prints the first page of the diagram even though 'All Pages' is selected in the print dialog.  What in the world am I missing - seems so simple and I know I've done it before.  Any insights or suggestions for a different means by which to print this diagram would be greatly appreciated.
0
I have an Sql Server table Tb_Docs with RevID column. sample Values below
   _25_1_N_
 _2_1_N_
_175_N_
_A_N_

I need to get the substring as follows
   25
  2
 175
 A
0
Does anyone have some thought on "the preferred sorting collation" for (unicode) data in a MSSQL database, for Chinese and also some other languages.
In other words: which collation would most people in these countries expect and consider to be "normal"?`

I am thinking about the following selected collations. Any input/experience on this?
All sorting should be case insensitive.

cn/China mainland: Chinese_Simplified_Stroke_Order_100_CI_AS_SC
ru/Russia: Cyrillic_General_CI_AS
ee/Estonia: Estonian_CI_AS
sk/Slovakia: Slovak_CI_AS
it/Italy: Latin1_General_CI_AS
fi/Finland: Finnish_Swedish_CI_AS
se/Sweden: Finnish_Swedish_CI_AS
en/USA: Latin1_General_CI_AS
no/Norway: Danish_Norwegian_CI_AS
fr/France: French_CI_AS
es/Spain: Modern_Spanish_CI_AS
de/Germany: Latin1_General_CI_AS
dk/Denmark: Danish_Norwegian_CI_AS
pl/Poland: Polish_CI_AS
none: Latin1_General_BIN2
0
I am looking for a SQL server script that would do the following:

1) Uncheck the "Enforce password policy"  for ALL the Logins(sql server authentication) that have the
   "Enforce password policy" enabled
2) and logins that are mapped to one specific database
3) and is not a system login (like ''sa'')
0
CEOs need to know what they should worry about
CEOs need to know what they should worry about

Nearly every week during the past few years has featured a headline about the latest data breach, malware attack, ransomware demand, or unrecoverable corporate data loss. Those stories are frequently followed by news that the CEOs at those companies were forced to resign.

Hi Experts,

I am receiving complaints from users from time to time that they are getting an error on a linked SQL view that could not be updated (see attached).

However the strange thing is that when I try doing that same change on my pc it works, moreover after a while (sometimes next day) the error doesn't occur on theirs either...

Any idea how to troubleshoot this?

Below is the SQL of the view in question.

SELECT     dbo.Skilled_Nursing_Visit_Note.ID, dbo.SNV_Printed_History.VendorsID, dbo.SNV_Printed_History.SNV_ID, dbo.Skilled_Nursing_Visit_Note.Client_Last_Name, 
                      dbo.Skilled_Nursing_Visit_Note.Nurse_Signature_Last_Name, dbo.Skilled_Nursing_Visit_Note.Visit_Date, dbo.Skilled_Nursing_Visit_Note.Shift_From_Hour, 
                      dbo.Skilled_Nursing_Visit_Note.Shift_To_Hour, dbo.Skilled_Nursing_Visit_Note.Date_Signed, dbo.Skilled_Nursing_Visit_Note.Nurse_Name_Stamp_SNV, 
                      dbo.Skilled_Nursing_Visit_Note.Nurse_User_ID_num_SNV, dbo.SNV_Printed_History.ReviewedBy, dbo.SNV_Printed_History.ReviewedDate, dbo.SNV_Printed_History.PrintedDate, 
                      dbo.SNV_Printed_History.PrintedBy, dbo.Skilled_Nursing_Visit_Note.Client_First_Name, dbo.Skilled_Nursing_Visit_Note.Nurse_Signature_First_Name, 
                      dbo.Skilled_Nursing_Visit_Note.Date_Of_Birth, dbo.Skilled_Nursing_Visit_Note.Shift_From_Minute, dbo.Skilled_Nursing_Visit_Note.Shift_To_Minute, 
                      

Open in new window

0
What I am trying to do is when I change the build/deploy from Production to Test, I want to be able to also change the shared data source reference for all the RDL files.

In the screenshot attached, I have ImpresarioProd as my Data source and ImpresarioProd as my Shared Data source.  I would like to change all the Data source in the rdl files in my sln to ImpresarioTest.  (Minus the bad naming convention of the Data Source, is it possible to do this without going into each rld file and making the change manually?

Screenshot of Data Source and RDL files
Thank you,

Rich
0
Looking to count the number of SQL records until a certain value in one of the table columns is reached. Using Asp /VBscript. Not sure how to formulate the query.

What I'd like is to organise the results by the column "points" in descending order, grouped by the "city_id". I then need the number of records counted until a specific "member_id" is matched. Some "points" values could also be the same, which can cause an incorrect count. Similar points values should be ignored.

the db table is named "rankings" and is structured like this:

id  |  member_id  |  city_id  |  points
1   |      1      |   12500   |    2
3   |      2      |   12500   |    5
4   |     34     |     800     |    1
5   |     14     |   12500   |    14
6   |      6      |     600     |    12
7   |     11     |   12500   |    11
8   |     12     |   12500   |    5

For example, if I want to find the ranking for member_id "2", who happens to belong to city_id "12500", the correct final returned value from should be 3. This is because member_id "2" has the third highest points value in city_id "12500", even after taking into account the tie in points with member_id "12".

This below is all I can think of as I'm not a pro by any means and I know it's missing a lot!


member_id = 2
city_id = 12500
SELECT Count() as city_ranking FROM (SELECT * from rankings WHERE city_id='"&city_id&"' AND member_id <> '"&member_id&"' ORDER BY points DESC)
0
Trying to get the most recent (by date) record in a query in SQL Server 2012. I've got two tables - TBLA and TBLB. TBLA is the "parent" and TBLB is the "child". One row in TBLA can have many matching rows in TBLB but I only want to get the most recent TBLB entry. My query right now looks as follows:

select a.col1, a.col2, b.fld1, b.fld2
from tbla a
left outer join tblb b
on a.col1 = b.fld1

The query works as expected. It returns a row from TBLA and all matching rows from TBLB. But, I only want the most recent TBLB entry. FLD2 in TBLB is a date time field and that's the column that needs to be "tested" for being the most recent date.

I've done a bunch of google searches on the topic but am confused with most of the solutions that I've found.

Any help on this will be greatly appreciated.
0
Using Firefox I still get asked for username and password after saving username and password when trying to access local SSRS reports. I can see the credentials on the passwords and security section. I am running the latest version of Firefox and we do have an SSL certificate.
0
Below is Crystal Report formula for Previous year three month range.
RunDate is parametar passed in crystal report and =  02/28/2019

Below is date formula from sQL which I use to cheeck data.

Should not those two date formula give me the same records?

In Crystal report I am missing records that have date in month December of 2017.
in SQL Management Studio i am getting those records.
What is wrong with this Crystal Report formula why it missed December of 2017.

NumberVar LastYearQrtAmount;



DateVar SMonth;
DateVar EMonth;


Datetimevar RunDate;
DateTimeVar StartDate;

RunDate:= {?Run_Date};
EMonth:= Date(Year(RunDate)-1,Month(RunDate),Day(RunDate)); // this is one year back date



StartDate:= DateAdd("m",-2,EMonth);
SMonth:= Date(Year(StartDate),Month(StartDate),1);// this is one year back then back 2 months and first day of month



If 
(
{@InvoiceDate}In (dateserial(year(EMonth),Month(EMonth) +1,1-1) to //this the end of run month 
dateserial(Year(sMonth),Month(sMonth),1))//this is begingin of three month back
)


Then

LastYearQrtAmount:={Command.TotalAmount}-{Command.TotalTaxes}

Open in new window


***************************************************************************
This part is from SQL Server Menagement Studio


Declare @St_D as date
Declare @En_D as Date

Set @St_D = '2017-12-01'
set @En_D = '2018-02-28'

Open in new window


..........
......
And
(
 InvoiceDate >= cast(convert(char(8),@St_D,112) as int)
 And
  InvoiceDate <= cast(convert(char(8), @En_D,112) as int)
)

Open in new window

0

Microsoft SQL Server

163K

Solutions

49K

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.