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.

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

Sign up to Post

I wish to perform a case sensitive query on an SQL Server table.
I have found an example :
Select * from a_table where attribute = 'k' COLLATE Latin1_General_CS_AS

Can I be assured that executing this query will not affectsubsequent queries that don't have the COLLATE statement ?

In other words, the default COLLATION for the table will not be affected ?

Thanks,

Roger
0
PMI ACP® Project Management
LVL 19
PMI ACP® Project Management

Prepare for the PMI Agile Certified Practitioner (PMI-ACP)® exam, which formally recognizes your knowledge of agile principles and your skill with agile techniques.

I need help with an SQL Join/results

I have two tables SCHOOLS and STUDENTS  They both have a SCHOOLID that can be joined.  What I need to see are ALL of the SCHOOLS and fields from the SCHOOLS table and  a virtual field telling me if there are ANY STUDENTS that have that SCHOOLID.

Example:

SCHOOLS
   SchoolID
   School
   Active

STUDENTS
   name
   SchoolID

 RESULT Table:
SchoolID     Active     Used (yes or No)  

So the virtual field USED is telling me if there are ANY schools that are in the STUDENTS table.  

Then I want to make another query that I can add a where clause to the SchoolID  So SAME result be asking "Does SchoolID have any students from the STUDENTS table that have that SchoolID"  Then it would list that school (ONE RECORD)  SchoolID, School, Active, Used
0
Hi,
How to copy contents of Column2 to Column1 .
Example:
Table1
Column1     Column2
ABC                  XYZ

Expected Result:
Table1
Column1     Column2
ABC XYZ

Any suggestions?

Thanks
0
I'm looking at partitioning the following tables because performance has been getting worse as we add more customers.  We only keep 90 days of data and it's always queried for one customer at a time, so I'm looking at partitioning it by customerID.  

SessionClosed   rows:  378,768,648  MB: 61,233
Access   rows:  112,975,561  MB:  26,323
SessionDenied  rows:  99,010,046  MB: 16,790

I've never done this.  Here are my questions and concerns:

1. How many files and/or filegroups should I create?  They will not be spread across multiple disks.  Currently we only have the one primary file group with just the 2 mdf and ldf files.

2. There are currently 32 customers and growing each w/average of 12 million records each.  The CustomerIDs range from 1 to 500.  How do I create the partitions for the random customerIDs?  Will I have to manually add partitions each time a customer is added?  Should I create 500 partitions?  Should I break them up into 5 partitions (1-99, 100-199, etc.)?  In that case would I have 5 filegroups with 1 file per?

Thank you!
Sarah
0
This sql statement returns a whole number, like:

1, 7, 14

I want it to return a number like:

1.0.0
7.0.0
14.0.0

CONVERT(varchar(10), ISNULL(etw.Listorder, 99)) as 'ListOrder3'

Open in new window


How would I alter it? Currently what I've tried gives errors pertaining to it not being an integer value.

thanks!
1
I have a table of data which consists of three measurements (called NPI) which are collected on a yearly basis for the whole country, counties and communities.

So for each year there are three country measurements, three for each county and three for each district.

The table has many years of data where each row defines the year, measurement number, countyid, communityid and the measured NPI value.

I would like to extract a years data for the country, a specific county and a specific community.  To me it is probaly a pivot but i cannt get my head round pivots.

So I know the year, the countyid and communityid.

The table below shows the original data.
Original data
And this is how i want to collect it:
e2.JPG
Note that a row with -1 for countyid and communityid represents the country value.

Would apprciate any tips in generating the sql query to get the required data,. SQL Server 2016.  Many thanks
0
Hi Experts,
i'm getting following datetime output but how to ignore when sending to the
SQL server. becuase SQL server genearion error message

01/01/0001 00:00:00

Sql error :
SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.
0
In SQL Server 2012, why won't the following dynamic SQL statement execute?

SELECT DISTINCT 7 As EntityId, E7Id As InstanceId FROM ##TempTable_461_0 UNION SELECT DISTINCT 1 As EntityId, E1Id As InstanceId FROM ##TempTable_461_0

Note: I verified that ##TempTable_461_0 has the columns "E7Id" and "E1Id".
0
Need to remove dashes and add ISNULL function to this data column.

I have this so far:
,'N' + RIGHT(REPLICATE('0', 11) + CAST(RX_NDC.NDC_CODE AS varchar(11)), 11) AS 'ERX_NDC_CODE'

Someone must have helped me with this in the past because I have no idea what Replicate is doing.  
My data currently returns like this:
N50474-002-6
N0409-6629-0
N0781-3204-9
NULL
N0338-0049-0

I need to remove the dashes when there is data, so I changed it to this:
 ,'N' + RIGHT(REPLICATE('0', 11) + CAST(REPLACE(RX_NDC.NDC_CODE,'-','') AS varchar(11)), 11) AS 'ERX_NDC_CODE'

This seems to work, but then when I try to add ISNULL anywhere in the statement it comes back with errors.

Can someone help?
0
I need to do some research, so can someone point into a direction; my quandary is ... is it possible to code an MSAccess VBA front end to use an MSSQL Server 2017 Express back-end, which runs on a Win10 box (64 bit) and use an iPad to run the Access front-end?
0
Ensure you’re charging the right price for your IT
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Hi,
This is  a variation on a question answered for me by pcelba
https://www.experts-exchange.com/questions/29159934/SQL-Server-Query-to-return-rows-based-on-a-column-value-change.html
In that question I wanted to run a query everyday against a table to identify the rows that had a LOCATION value change THAT DAY,  which pcelba provided a working solution.

I know want to expand on that requirement, I want to run a query that will return  all rows where a LOCATION changed historically, not just TODAY.

For example , using the code below, I get two rows returned,
What I want returned is all rows  that have had a location changed over their history, in this case in the last 730 days.
This would return  rows 6,7,10 from the top table,
Not just lines 6,7 as it currently does.

I see why line 10 is not returned,
this is due to the sub-query in my select statement which returns the TOP 1 value based on  date descending for each ID

I am looking for ways maybe to modify the existing code, if this is possible? or maybe a rewrite is needed

Any guidance appreciated, thanks

The current code i am using is
DECLARE @YourTable3 TABLE (Id int, Date datetime, location int)
INSERT INTO @YourTable3 VALUES (2833, CAST('2017-08-08' AS DATE), 185), (2833, CAST('2017-12-22'AS DATE), 185), (2833, CAST('2019-04-23' AS DATE), 185), 
  (6392, CAST('2017-05-15' AS DATE), 174), (6392,CAST('2017-05-29' AS DATE), 174), (6392, CAST('2019-10-01' AS DATE), 240),(2833, 

Open in new window

0
0
Hi I am struggling with how to apply this in MS SQL.  I want to get the status of a rental unit to populate a table showing the last status on a rental unit by month.  A range between an event status can span several months, ie, a unit is rented for a year.  I am trying to show a historical monthly view of unit status based on the max event of the historical month or the last non null status of the previous month that had a status change.  I've tried applying a max window function with the rows preceding unbounded hint unsuccessfully however because I am right outer joining the dates from a it doesn't seem to apply the partition range.  Any suggestions or help would be appreciated.


IF OBJECT_ID('TempDB..#tmpFUnit','U') IS NOT NULL
DROP TABLE #tmpFUnit

--===== Create the test table with

CREATE TABLE #tmpFUnit
(
rptMth varchar(6),Buiding varchar(4),Unit varchar(4),uStatus varchar(15)
)

insert into #tmpFUnit
SELECT '201809','1820','0203','Occupied' UNION ALL
SELECT '201810','','','' UNION ALL
SELECT '201811','','','' UNION ALL
SELECT '201812','1820','0203','Vacant' UNION ALL
SELECT '201901','','','' UNION ALL
SELECT '201902','','','' UNION ALL
SELECT '201903','','','Unrented' UNION ALL
SELECT '201904','','','' UNION ALL
SELECT '201905','','','' UNION ALL
SELECT '201906','1820','0203','Occupied' UNION ALL
SELECT '201907]','',' ','' UNION ALL
SELECT '201906','','',''

select * from #tmpFUnit

Desired Output
0
If you have any automated processes for populating data in your databases outside of the standard means (users adding/updating/removing records via a front end application), do you have any specific processes in place to monitor the 'feed' from say an external database for success. I am wondering how and what features in MSSQL (or something else) you would use to check data feeds from external sources have worked if they are set to feed data say on a nightly basis.
0
Unable to upgrade SQL Server from SP1 to SP2.  The database instance is greyed out.
Current version: 13.0.4001.0
Upgrade file being used: SQLServer2016-KB4524334-x64.exe
This file has worked on other instances on the same SQL server and brings those instances up to 13.0.5492.2
The instances that successfully upgraded were at 13.0.5026.0
The service pack installer does see the 13.0.4001.0 instance, and shows it unchecked for the current upgrade, but the checkbox is greyed out and can't be checked.
0
I'm trying to generate a database query (sql server 2012) of the 50 most recently search items for a particular user. All of the data is in a table called LogSearch, however, I cannot use a simple DISTINCT query because the SearchDate will always be unique. This is what I started with:

Select distinct top 50 SearchNumber, SearchDate from LogSearch where [REMOTE_HOST]='xxx' group By SearchNumber, SearchDate

What would be the best way to approach this? I've looked into CTE's, but am not sure if that's the best way to go. Any suggestions would be greatly appreciated!
0
Experts - I have a leave table as below:

EmpName, EmpLocation, FromDate,       ToDate,          LeaveType
ABC              XYZ                  11/01/2019     11/3/2019    Planned Leave

I call the above table as "TBL_Leave_Main"
How do i get the below result via query?

EmpName    EmpLocation     LeaveDate       LeaveType
ABC                 XYZ                     11/01/2019    Planned Leave
ABC                 XYZ                     11/02.2019    Planned Leave
ABC                 XYZ                     11/03/2019    Planned Leave

Kindly help
0
this was working, i change the call to the database to use the Parameters element of ADO as just passing the values failed when passing HTML to database..

i have obviously done something wrong but been looking at this now for 4 days and not got any closer to a solution..

your help would be a god send
Thank you


DIM cmd, DbStr
DbStr = "User ID=xxxx;Password=xxxx;Initial Catalog=CMMS;Data Source=MYCMMS;Provider=SQLOLEDB"

Set cmd = Server.CreateObject("ADODB.Command")
set cmd.ActiveConnection=DbStr
cmd.ActiveConnection.open


'Prepare the stored procedure
        cmd.CommandText = "ConfUpdate"
        cmd.CommandType = adCmdStoredProc
	cmd.Parameters("@Company") = MyCompany
	cmd.Parameters("@address1") = Myaddress1
	cmd.Parameters("@address2") = Myaddress2
	cmd.Parameters("@city") = Mycity
	cmd.Parameters("@county") = Mycounty
	cmd.Parameters("@postcode") = Mypostcode
	cmd.Parameters("@phone") = Myphone
	cmd.Parameters("@WorkorderEmailTPL") = MyWorkorderEmailTPL
	cmd.Parameters("@VendorEmailtpl") = MyVendorEmailtpl
	cmd.Parameters("@InstanceID") = MyInstanceID
	cmd.Parameters("@VersionNo") = MyVersionNo
	cmd.Parameters("@updatedBy") = MyupdatedBy
	cmd.Parameters("@IssueDate") = MyIssueDate
	cmd.Parameters("@IssuedBy") = MyIssuedBy
	cmd.Parameters("@LiveDate") = MyLiveDate
	cmd.Parameters("@emailfooter") = Myemailfooter
	cmd.Parameters("@emailDisclaimer") = MyemailDisclaimer
	cmd.Parameters("@action") = Myaction
	cmd.Parameters("@confID") = MyconfID


       

Open in new window

0
I obtained this code from the internet but it gives integer results.  How do I modify it so it will compute to 2 decimal places?  I am dealing with dollars and cents.

Public Shared Value as Integer=0
  Public Shared Function GetValue(Item as Integer) as Integer
     value= value + Item
     return Item
  End Function
  Public Shared Function GetTotal()
     return value
  End Function
1
Angular Fundamentals
LVL 19
Angular Fundamentals

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

I can get Date variable to match sql server date to use in code to find indexed line in database table.  I have tried multiple formatting options - nothing works.  I need to include miliseconds in the vb.net Date format - not in string format.  I use this in a <= operations to compare date to the database.  When it is = it uses that indexed line to include in the charted data.  Everything I have tried gives me date and time without miliseconds.

I have tried the following.

Dim MaxDate As Date = DataTable.Rows(0)(0)
MaxDate = Date.ParseExact(DataTable.Rows(0)(0), "yyyy-MM-ddTHH:mm:ss.fff", System.Globalization.CultureInfo.InvariantCulture)

I can convert using a string variable, but cannot use string variable in query search for date.
0
would SQL server have any form of log file which would list when a backup of a database was restored, and who performed the restore operation? I wasn't sure if it would be stored in a table or elsewhere, or where even to begin to look?
0
I need to update a table which has multiple sales orders which could have multiple lines.
I need to renumber the order lines for each distinct order number.

SalesOrder      SalesOrderLine
0001234          1
0001234           586
0001235          1
etc....
0001235          6

Needs to be

SalesOrder      SalesOrderLine
0001234          1
0001234          2
0001235          1
0001235          2
etc...

There are around 500 SalesOrders and they could have up to 10 SalesOrderLine numbers each.

Thanks in advance
0
Hi, I have 3 flat file sources. Now I want to count the number of records in each flat file and I want to write the counts to a flat file in the below output.

FileName  Count

can anyone suggest me step by step on how exactly I achieve this ?
Many Thanks
0
Hi Team,

Currently , iam woking on Miscrosoft SQL Server database . Iam new to this . I want help  on datadictionary views like we have in oracle to capture the table , table column information we have in oracle (user_tables / user_tab_columns).

I was told i can get it using any system stored procedure or some views. But able to find one . Can anyone help me with this information in Microsoft SQL SERVER .
0
Dear expert

I got a mssql query question: Table looks like this:

Name                            Date                             Pay
Eric Wei                        2019-01-02                   50
Eric Wei                        2019-01-06                   50
Eric Wei                        2019-02-10                   50
Eric Wei                        2019-02-17                   50
Lisa Ti                            2019-01-10                  20
Lisa Ti                           2019-02-17                   50
Martin B                        2019-01-10                  50
Martin B                        2019-02-17                   50

Output that I want:
Name                              Month                           Pay Count                 Sum
Eric Wei                          2019-01                            2                             100
Eric Wei                          2019-02                            2                             100
Lisa Ti                             2019-01                            1                               50
Lisa Ti                             2019-02                            1                               50
Martin B                        2019-01                             1                               50
Martin B                        2019-02                             1                               50

Anyone can help with this query, thanks
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.