Microsoft SQL Server

161K

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

eg:BARRETT WILSON  BARRETT WILSON
BARRY BRANDON BARRY BRANDON
JENNAAAA      JENNA
AAAQIL      AQIL
AAA PETER      A PETER
 
create a function to  do this
0
Cloud Class® Course: C++ 11 Fundamentals
LVL 12
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

I have two table a and b
a (empid,name,dep)
b(b_id,empid,dep_move,month)
Example  
a values
empid   name    dep
——————————
  1           John      tech
  2          Sam       Sec
  3          Jack      Dep1

And b values
b_id     empid      dep_move    Month
—————————————————
 1            3              Dep2             6
 2            1               Ff                  6
 3            1               Dd                 7
the result in month 8
empid   name    dep
——————————
  1           John      Ff
  1.          John       Dd
  2          Sam       Sec
  3          Jack      Dep2
And when am using max in subquery in left join and asking for month 6 i get the base dep

This example for my cod
Select empid,name,case when b.month>=‘7’ then b.dep_move
Else a.dep end as depart
from a
Left join b on a.empid=b.empid
Group by empid,name,dep,dep_move
Order by empid,name,dep,dep_move

Thanks everyone
0
SQL Server 2008 r2 Maintenance Plan for backing up database has the following error:

Date            6/21/2018 3:06:59 PM
Log            Job History (M2M Backup 2.Subplan_1)

Step ID            1
Server            USC-SERVER
Job Name            M2M Backup 2.Subplan_1
Step Name            Subplan_1
Duration            00:00:27
Sql Severity            0
Sql Message ID            0
Operator Emailed            
Operator Net sent            
Operator Paged            
Retries Attempted            0

Message
Executed as user: USCONTROLS\Administrator. ...1600.1 for 64-bit  Copyright (C) Microsoft Corporation 2010. All rights reserved.    Started:  3:06:59 PM  Progress: 2018-06-21 15:07:00.18     Source: {0E1BB073-CFA4-4177-B985-518CC785330D}      Executing query "DECLARE @Guid UNIQUEIDENTIFIER      EXECUTE msdb..sp...".: 100% complete  End Progress  Progress: 2018-06-21 15:07:00.36     Source: Back Up Database Task      Executing query "EXECUTE master.dbo.xp_create_subdir N'D:\SQLBackup...".: 20% complete  End Progress  Progress: 2018-06-21 15:07:00.36     Source: Back Up Database Task      Executing query "EXECUTE master.dbo.xp_create_subdir N'D:\SQLBackup...".: 40% complete  End Progress  Progress: 2018-06-21 15:07:00.37     Source: Back Up Database Task      Executing query "EXECUTE master.dbo.xp_create_subdir N'D:\SQLBackup...".: 60% complete  End Progress  Progress: 2018-06-21 15:07:00.37     Source: Back Up Database Task      Executing query "EXECUTE master.dbo.xp_create_subdir N'D:\SQLBackup...".: 80% complete  End Progress  Progress: 2018-06-21 15:07:00.37     Source: Back Up …
0
SUBSTRING(partmstr.upc_code, 2, LEN(partmstr.upc_code) - 2) AS '10 DIGIT UPC'

Msg 537, Level 16, State 2, Line 2
Invalid length parameter passed to the LEFT or SUBSTRING function.


We have had this as part of a report that has been running for years, but something changed an now this won't run.  Can you tell me how to fix it?
0
I have stored procedures that work perfectly fine on standard SQL Server instances. 2008, 12... etc

However on Amazon RDS they can take an excruciating amount of time to run.
Instead of the normal 2 seconds max... they take 13 minutes if you let them run

I have isolated the issue...and we have same indexes on the tables...

In standard SQL if I have a query with a join on a sub query like the one below... its fine
On RDS 13 minutes

UNLESS... in RDS I FIRST create a @table
Insert data from the same query
Then join on @table
If I do that... it is actually FASTER than standard SQL.

Any ideas on why this is?



 JOIN (   SELECT   MAX(a.IndividualDispositionID) AS MaxRowID ,
                                                   a.IndividualID
                                          FROM     IndividualDispositions a
                                          WHERE    DATEDIFF(DAY, @DateRangeStart, a.DateAdded) >= 0
                                                   AND DATEDIFF(DAY, a.DateAdded, @DateRangeEnd) >= 0
                                          GROUP BY IndividualID ) a ON c.IndividualDispositionID = a.MaxRowID
                        WHERE    c.IndividualExportsMasterID IN (   SELECT c.IndividualExportsMasterID
                                                                    FROM   dbo.IndividualExportsMaster c
                                                                    WHERE  c.SalesSiteID IN (   SELECT *
                        

Open in new window

0
SELECT UPPER(COLUMN1)
FROM SCHEMA1.TABLE1

SELECT UPPER(COLUMN2)
FROM SCHEMA2.TABLE2

i have 100 records in column 1 and column 2 with roughly half capitol words(HONDA etc.) and rest of half small words(nissan etc.)

when i do UPPER character function

i expected to see 50 records with values like NISSAN


when i do LOWER character function
i expected to see 50 records with values like honda

but i got only 2 records.

can you please advise how to fix this
0
I am trying to write a simple query in MSSQL to fill a field in a database with sequential numbers, and I need some help.

The table is QA and it has these important fields:
  • QuestionID - the "identify" field
  • Question - I want to filter and only include rcords if the length of this field is greater than 3 (basically non-blank)
  • QuestionNum - this will contain the new sequential numbers

I looked online, and I have found some solutions, but I am having trouble putting them together (e.g. I found one solution with a "where" clause, but that was only a "select" statement, and not an "update" statement).

Thank you for your help.
0
I am working on a query. Getting this ERROR. please help out!!

SELECT
A.ID AS QUOTE_ID

FROM (
SELECT
PRICE_LIST.ID AS IDEX
FROM (
SELECT
LIST AS PP_ID from TABLE 1
) QUOTE-----------
LEFT OUTER JOIN TABLE 2 PRICE_LIST
ON QUOTE.PP_ID = PRICE_LIST.Id) QUOTE_PRICEPLAN

**************************************************************************

I am getting an error Invalid column name 'ID'.
PLEASE Help me on this at the earliest.
0
Hi!

I was trying to review the execution plan of a Query and saw the Nested Loop parameter "No join predicate" . How could I correct this Warning?


CREATE PROCEDURE [dbo].prd_3   
@sistema int,  
@aplicativo int,  
@codi_enc int,  
@codi_usu int    
As  

SELECT  per.codi_pfl AS 'ID',
                per.vNOMB_PFL AS 'DESCRIPCION',
                api.vNOMB_APL
FROM SEG_PERFIL AS per
INNER JOIN SEG_PERFIL_OPCION AS opc ON per.CODI_PFL = opc.CODI_PFL
INNER JOIN SEG_OPCION AS opi ON opi.CODI_OPC = opc.CODI_OPC
INNER JOIN SEG_APLICACION AS api ON api.CODI_APL = opi.CODI_APL
AND opi.CODI_SIS = api.CODI_SIS
INNER JOIN SEG_ENCARGATURA_APLICACION eapp ON api.codi_sis =eapp.CODI_SIS
AND api.codi_apl=eapp.[CODI_APL]
WHERE api.CODI_SIS = @sistema
  AND api.codi_apl= @aplicativo
  AND eapp.CODI_ENC=@codi_enc
  AND per.codi_pfl NOT IN (148,
                           158,
                           159)---perfil  no a listar
AND  EXISTS
    (SELECT 1
     FROM SEG_ENCARGATURA_PERFIL
	 inner join SEG_PERFIL  ON
	 per.CODI_PFL = SEG_ENCARGATURA_PERFIL.CODI_PFL
     WHERE CODI_USU = @codi_usu)
  AND eapp.CODI_USU=@codi_usu
	GROUP BY
	per.codi_pfl,
                per.vNOMB_PFL,
                api.vNOMB_APL

ORDER BY per.vNOMB_PFL
GO

Open in new window


I'd really appreciate your help!
0
Created an SSRS 2014 report by joining 2 table ,related by CS_ID filtered by year . Notes column is in 2nd table and for each year it is unique . I am able to display data in a tablix. Issue I am facing is ,when some of the rows  are having notes (value of which is same for all rows). How to display that note on  bottom of a report on  a textbox  ? Currently I use another dataset to do this .  I want to do away with this. But I feel there is  way to set a report variable on expression of a column ( I use status column) of the row which has that  note column value. SetValue() does not seem to work . Ensured that report variable is not ready only.
0
Ultimate Tool Kit for Technology Solution Provider
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

I have a query that I want to output to a pipe-delimited flat file.

I want to replace every pipe symbol with a ' - ' for every field, and I want to name the file something like QueryOutput.20180622.

What is the most efficient way to accomplish this?
0
Hi,
If I right click on the SP and choose Modify,
edit the code.
click Parse,
and then Execute.
It gives Error:
Msg 2714, Level 16, State 3, Procedure sp_name, Line 2 [Batch Start Line 4]
There is already an object named 'sp_name' in the database.
What am I doing wrong?
0
Here is some sample data for a sql server 2012 table

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


CREATE TABLE #WarehouseLocation (
     WarehouseID int NOT NULL,
     HistoryDate date NOT NULL,
     salescode varchar(5) NOT NULL
 );
 INSERT INTO #WarehouseLocation VALUES
 (1,                    '01/01/2018', 12345),
 (1 ,                   '02/01/2018', 12345),
 (1 ,                  '03/01/2018', 14567),
 (1,                    '04/01/2018', 12345),
 (1 ,                   '05/01/2018', 12345),
 (1 ,                  '06/01/2018', 14567),
 (2,                    '01/01/2018', 98765),
 (2 ,                   '02/01/2018', 98765),
 (2 ,                  '03/01/2018', 56789),
 (2,                    '04/01/2018', 67890),
 (2 ,                   '05/01/2018', 67890),
 (2 ,                  '06/01/2018', 67890)


Trying to write a query (perhaps a cursor ?)

That will  take a look at the sales code for a given warehouse id and list the date range it is valid

Given sample data,  here is expected result

WarehouseID      salescode      startdate         enddate
1                          12345            2018-01-01      2018-02-28
1                           14567             2018-03-01      2018-03-31
1                           12345           2018-04-01      2018-05-31
1                           14567           2018-06-01      Null
2                           98765           2018-01-01      2018-02-28
2                           56789             2018-03-01      2018-03-31
2                           67890  …
0
Access 2010 VBA
sql server 2008

What I have:
I have code that works fine for a Bulk Insert.  from access

What I need:

I need to read each field mapping the column in the text file to the field in the table.
Because I may have to special format fields  

For Example:   The data may contain  33.45223222
and i only need the field to accept   33.5( rounding it off to 2 decimal places.

Dim fname As String
                   
                    
                         fname = "C:\Program Files\Enterprise\EXPORT_iQ.txt"
                        strSQL = "BULK INSERT dbo.tblImportiQ_Exported FROM   '" & fname & "'  WITH ( FIELDTERMINATOR = '|', ROWTERMINATOR = '\n',KEEPNULLS )"
                        Dim objCommand As SqlCommand
                        objCommand = New SqlCommand(strSQL, objConn)
                        objCommand.CommandText = strSQL
                        objCommand.ExecuteNonQuery()
                        fname = Dir()
                   

Open in new window


Thanks
fordraiders
0
Access to the remote server is denied because no login-mapping exists. (#7416)
Receiving this error message when running a SQL stored procedure from MS Access Pass Through Query.

The error is coming up due to the following connection in the procedure:
			UPDATE #PartQueue
			SET LatheTSCount = (SELECT COUNT(ts.ID) 
								FROM [TSSLATHEIPAD]...[tblLatheTSS] ts
								WHERE ts.PrintNum = #PartQueue.jaPartNum)

Open in new window


 The connection is to a linked MS Access backend in SQL Server:
Linked Server to MS Access App
Here is the Linked Server Properties General Tab:
Linked Server Properties General Tab

Any ideas on how to configure this so that I can run this stored procedure from MS Access?

The Stored Procedure does run fine when executed within SQL Server Management Studio.
0
I had this question after viewing Connecting Physical Hard drvies to a Hyper V VM.

I understood that above solution is only for external USB HDD.  I have internal HDD that resides the data that I want to connect to SQL server 2008 R2 running on Hyper-V Wind 10Pro VM. How can I connect the data drive in SQL server.  I see all Host drives in window explorer and I can not access them but SQL server Can not ?

Any guidance would be appreciated.

Suresh Agrawal
0
Hi,

how can I declare a Textbox or render it accessible in relation to the error-message below?

"Textbox313' is not declared. It may be inaccessible due to its protection level."

Thanks
0
need a query for count of vehicles to be separated based on car ,motor,,lorry and bus..i have three  tables to be queried using payment ,transaction and  vehicle_ type  table
carpark_id -common key for all table.
transaction_id is common for payment and transaction table
Vehicle _type_id is common for transaction and vehicle type table
This query is only volume of car to be calculated on 1 day and plz check the query

SELECT Count(payment.amount)Volume,sum(payment.amount)Amount,count(description) from carpark_db.payment,carpark_db.transaction where payment.transaction_id = transaction.transaction_id and payment.carpark_id ='AMSDE5E27D5846D0B3F3A8BA4CA4C127' and payment.payment_date_time >= '2018-01-02 00:00:00'
and payment.payment_date_time < '2018-01-02 23:59:59' Innerjoin  carpark_db.vehicle_type on payment.vehicle_type_id = vehicle_type.vehicle_type_id where vehicle_type.description LIKE 'Car%'
0
In my quest to create a poor man's address correction routine I created about 25 stored procedures similar to the one below. The only thing that changes are the strings used in the search. I have wrapped all these sp's into a VS program plus an SSIS job. What I just discovered is that the only changes that are sticking are the ones in the last sp that is called. If I run them individually the changes stick. I have a COMMIT TRAN at the end of each one.  What must I change to get the changes to stick as each sp is called? It must have something to do with all these sps called inside one session.


CREATE PROCEDURE [dbo].[apd_UpdateAddressesCASESB02]

AS

BEGIN

      SET NOCOUNT ON

DECLARE @TranCount int
DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int

SET @TranCount = @@TRANCOUNT

IF @TranCount = 0
      BEGIN TRAN

BEGIN TRY

UPDATE mt
 SET [MOD_ESRI_LOCATION] = chg99.[MOD_ESRI_LOCATION]
 FROM [dbo].[cases] mt
 CROSS APPLY (
     SELECT CASE WHEN PATINDEX('%BLVD%', mt.[MOD_ESRI_LOCATION]) >= 4 THEN SUBSTRING(mt.[MOD_ESRI_LOCATION],1,PATINDEX('%BLVD%',mt.[MOD_ESRI_LOCATION])+1)
         ELSE mt.[MOD_ESRI_LOCATION] END  AS [MOD_ESRI_LOCATION]
 ) AS chg01
 CROSS APPLY (
     SELECT  CASE WHEN PATINDEX('%BSMT%', chg01.[MOD_ESRI_LOCATION]) >= 4 THEN SUBSTRING(chg01.[MOD_ESRI_LOCATION],1,PATINDEX('%BSMT%',chg01.[MOD_ESRI_LOCATION])-1)
         ELSE chg01.[MOD_ESRI_LOCATION] END AS [MOD_ESRI_LOCATION]
 ) AS chg02
 CROSS APPLY (
     SELECT CASE WHEN …
0
Free Tool: SSL Checker
LVL 12
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Hi,

i have parameters with values 1, 2 and 3. I would like to define the in such a way that the possible choices appear the following way:
"East" (if parameter = 1)
"South" (if parameter = 2)
"North" (if parameter = 3)
0
Hi,

how is it possible to set more than 1 filter in SSRS, such that the values in the 2nd filters are shown according to the choice lade in the 1st filter?
In my example (see attachment), I have a sales area divided into regions. I would like to appear only the regions which are presented in the sales area chosen in the 1st filter
0
Hello,
I have Table1 with date fields
From date  null
To      Date null
Howevr wen  run this query below it inserts 1900-01-01 in the column

INSERT INTO [TABLE1] 

SELECT 
         , IET.[From]
         , IET.[To]
 FROM [TABLE2] IET


WHERE [InvoiceItemGUID] NOT IN 
    (SELECT [InvoiceItemGUID] FROM TABLE1 )

Open in new window


Thanks
0
Hello,I have a project in winforms which takes care of the attendance of the students.In my project I have a form called AttendanceList which has the following:
-a textbox for the Serial number on the NFC tag( the attendance will be based on every student swiping the card over the card reader).
-a combobox where the teacher selects the Course and based on the course,when the teacher clicks a button,the list with all the students should appear.
- a datagridview which displays,updates and deletes the students.
The problem that i'm facing is that when a student registers,he will appear in table RegisterStudent(which is the table for all the registered students).I want to take the values from RegisterStudent(sNr,SN,fName and lName) and insert it in AttendanceList table.This is my method in doing so:
  private void LoadStudents()
        {
            using (SqlConnection conn = new SqlConnection(connstr))
            {
                try
                {
                    using (SqlCommand cmd = new SqlCommand())
                    {
                        string query = "insert into AttendanceList(SN,sNr,fName,lName) select SN, sNr, fName,lName from RegisterStudent p inner join StudentCourses c on p.SN=c.StudentId WHERE  c.CourseId = '" + cmbClassId.SelectedValue.ToString() + "'";
                      // cmd.Parameters.AddWithValue("@CourseId", cmbClassId.Text);
                        cmd.Parameters.AddWithValue("@SN", txtStudentId.Text);
               …
0
In SQL Server 2008/2012, how  do i check whether this patch KB4057114/KB4057116 is installed or not?
1
I want to get a list of sales orders that are 7 years old so I wrote the following query:

SELECT SalesOrderNumber, CAST (OrderDate AS date) AS 'Date of order'
FROM Sales.SalesOrderHeader
WHERE  DATEDIFF(YYYY, OrderDate, GETDATE()) = 7                            

I am concerned about the effect the  DATEDIFF expression in the WHERE clause will have on the execution plan that is decided upon by the optimizer because I know that one should not   have an expression on the lhs , so I changed it for this query and I gave me exactly the same execution plan:

DECLARE @compdate INT = 7
SELECT SalesOrderNumber, CAST (OrderDate AS date) AS 'Date of order'
FROM Sales.SalesOrderHeader
WHERE  @compdate = DATEDIFF(YYYY, OrderDate, GETDATE())  

Is there a better way of doing it ?
0

Microsoft SQL Server

161K

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.