Microsoft SQL Server 2008

49K

Solutions

17K

Contributors

Microsoft SQL Server 2008 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. Major improvements include the  Always On technologies and support for unstructured data types.

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

Sign up to Post

I have a SQL statement like this

SELECT DISTINCT SiteAddress,sitecity,SiteState,SiteZip,SiteCountryCode

 FROM table
WHERE (Deleted =0
AND SiteCountryCode = 'USA')
or (SiteAddress != NULL
and SiteAddress != 'NULL')

Open in new window


SiteAddress is nvarchar(256)

Howcome this SQL brings back NULL in siteaddress?

s1.png
0
Cloud Class® Course: CompTIA Cloud+
LVL 12
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Hello,
Can you please help,
I need to get the [Driver_In] Minimum Date Time/ [Driver_Out] Maximum Date Time between 2 tables (Per Driver) , then calculate the difference (Hours and Minutes),
This is What I have so far (But , it is wrong, I'm getting 2 lines per Driver

Select PickUpDriver AS [Driver], (Select Name From Drivers Where Drivers.DriverNumber = ActiveOrders.PickUpDriver) AS [Name],
CONVERT(date, OrderDate) AS [Order_Date],MIN(CAST(REPLACE(PickedupTime, ',', ' 01,') AS DATETIME)) AS [Driver_In],Max(CAST(REPLACE(DeliveredTime, ',', ' 01,') AS DATETIME)) AS [Driver_Out],
DATEDIFF(second, MIN(CAST(REPLACE(PickedupTime, ',', ' 01,') AS DATETIME)), Max(CAST(REPLACE(DeliveredTime, ',', ' 01,') AS DATETIME))) / 3600.00 AS [Hours_Worked],
DATEDIFF(minute, MIN(CAST(REPLACE(PickedupTime, ',', ' 01,') AS DATETIME)), Max(CAST(REPLACE(DeliveredTime, ',', ' 01,') AS DATETIME))) AS [Minutes_Worked]
From ActiveOrders
where (((OrderDate > = DATEADD(d,0,DATEDIFF(d,0,GetDate())))  And (OrderDate <= DATEADD(s, +86399, DATEADD(d,0,DATEDIFF(d,0,GetDate())))))) AND Cast(datepart(yyyy,PickedUpTime)as varchar) + '-' + RIGHT('0' + RTRIM(MONTH(PickedUpTime)), 2) + '-' + Right('0'+cast(datepart(DD,PickedUpTime)as varchar(2)),2) = Cast(datepart(yyyy,DeliveredTime)as varchar) + '-' + RIGHT('0' + RTRIM(MONTH(DeliveredTime)), 2) + '-' + Right('0'+cast(datepart(DD,DeliveredTime)as varchar(2)),2)
And ((Select Name From Drivers Where Drivers.DriverNumber = ActiveOrders.PickUpDriver)
0
i have SQL data file which is 5 gb and sql log file is 71 gb..  in E Drive size is 100gb..
There a job full Daily Full backup are happening, .bak file is only 191 MB

After realizing the above I did one Transcational log backup to g drive(got temp space), it came around 18gb, after that I took again full backup the size of backup was same around 192MB

is some thing wrong with transaction log, there is no open transcations, i have verfied? is my full backup is 192 Mb only?

im shortage of space as well to take hourly transaction log on E drive, the db is in full recovery model

Version is sql server 2012 std
0
Hello,
I'm trying to find the duplicates within the same field in SQL.
Select NoteNumber,OrderNo,NoteText,EnteredBy,EnteredOnDate,IsPublicNote,OrderNoteTypeID
From ActiveOrderNotes
Where NoteText .............. Has Duplicates.

Sample attached.:
Your help is appreciated.
Sample-1.xlsx
0
We have a booking system, and have appointments. What I need to do is get a list of bookings and the duration, based on the difference between the 2 appointment times.

Is there a better way to write this SQL? I'm using MS SQL 2008.

Declare  @myTable TABLE(dID int, Doctor varchar(25),  AppDateTime datetime)

  Insert into @myTable Select 1,'Doctor 1', '2018-07-08 09:00:00'
  Insert into @myTable Select 2,'Doctor 1', '2018-07-08 09:10:00'
  Insert into @myTable Select 3,'Doctor 1', '2018-07-08 09:15:00'
  Insert into @myTable Select 4,'Doctor 1', '2018-07-08 09:30:00'
  Insert into @myTable Select 5,'Doctor 1', '2018-07-08 09:40:00'
  Insert into @myTable Select 6,'Doctor 2', '2018-07-08 09:00:00'
  Insert into @myTable Select 7,'Doctor 2', '2018-07-08 09:25:00'
  Insert into @myTable Select 8,'Doctor 2', '2018-07-08 09:35:00'
  Insert into @myTable Select 9,'Doctor 3', '2018-07-08 09:00:00'
  Insert into @myTable Select 10,'Doctor 3', '2018-07-08 09:05:00'
  Insert into @myTable Select 11,'Doctor 3', '2018-07-08 09:10:00'
  Insert into @myTable Select 12,'Doctor 3', '2018-07-08 09:25:00'
  Insert into @myTable Select 13,'Doctor 4', '2018-07-08 09:00:00'
  Insert into @myTable Select 14,'Doctor 4', '2018-07-08 09:30:00'
  Insert into @myTable Select 15,'Doctor 4', '2018-07-08 10:00:00'
  Insert into @myTable Select 16,'Doctor 5', '2018-07-08 09:00:00'
  Insert into @myTable Select 17,'Doctor 5', '2018-07-08 09:45:00'
  Insert into @myTable Select …
0
Hello ,
I have this query which errors :

  SELECT RequestedBy,Authority,Date,Ref,CorresType, Comments,SentTo,Regarding,RoomTypeRouting,RoomTypeRouting2,RoomTypeRouting3,RoomTypeRouting4,RoomTypeRouting5,RoomTypeRouting6,Faxnumber,
	             Guestname,ConfirmationNumber,ArrivalDate,ArrivalDate2,ArrivalDate3,ArrivalDate4,ArrivalDate5,ArrivalDate6,Attention,Roomno, Roomno2,Roomno3,Roomno4,Roomno5,Roomno6,(CASE
                 WHEN Extendedto IS NULL THEN DepartureDate
                 ELSE Extendedto
        END) AS DepartureDate,
		(CASE
                  WHEN Extendedto2 IS NULL THEN DepartureDate2
                  ELSE Extendedto2
        END) AS DepartureDate2,
		(CASE
                  WHEN Extendedto3 IS NULL THEN DepartureDate3
                  ELSE Extendedto3
        END) AS DepartureDate3,
			(CASE
                  WHEN Extendedto4 IS NULL THEN DepartureDate4
                  ELSE Extendedto4
        END) AS DepartureDate4,
		(CASE
                  WHEN Extendedto5 IS NULL THEN DepartureDate5
                  ELSE Extendedto5
        END) AS DepartureDate5,
		(CASE
                  WHEN Extendedto6 IS NULL THEN DepartureDate6
                  ELSE Extendedto6
        END) AS DepartureDate6 FROM  [CORR]  WHERE  [Ref]  = '29' AND  [Year_Ref] =24 union all SELECT Route,DateTo,DateFrom FROM  [PROT]  WHERE  [InvoiceNumber]  = 'XYZ'

Open in new window


Any suggestion?

Error:
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.
0
Hello,
How to create a view in sql server with parameters.
Example :
DECLARE @EndDate AS DATE = '20180706';
DECLARE @StartDate AS DATE = '20180630';


WITH Unpivoted (GuestName, Property, ArrivalType, ArrivalDate, DepatureDate, RoomNo, RoomType,RequestedBy)
AS ( SELECT GuestName ,
            SentTo,
            'A1',
            ArrivalDate ,
            ISNULL(Extendedto, DepartureDate),
            Roomno ,
            RoomTypeRouting,
            RequestedBy
     FROM   CORR
     WHERE  DeleteRecord IS NULL
            OR DeleteRecord = 0
     UNION ALL
     SELECT GuestName ,
            SentTo,
            'A2',
            ArrivalDate2 ,
            ISNULL(Extendedto2, DepartureDate2),
            Roomno2,                         
            RoomTypeRouting2,
                     RequestedBy
     FROM   CORR
     WHERE  DeleteRecord IS NULL
            OR DeleteRecord = 0
     UNION ALL
     SELECT GuestName ,
            SentTo,
            'A3',
            ArrivalDate3,
            ISNULL(Extendedto3, DepartureDate3),
            Roomno3,
            RoomTypeRouting3,
            RequestedBy
     FROM   CORR
     WHERE  DeleteRecord IS NULL
            OR DeleteRecord = 0
     UNION ALL
     SELECT GuestName ,
            SentTo,
            'A4',
            ArrivalDate4,
            ISNULL(Extendedto4, DepartureDate4) ,
            Roomno4,
            RoomTypeRouting4,
            RequestedBy
     FROM   CORR
     WHERE  DeleteRecord IS NULL
         

Open in new window

0
Hi Experts,
Advance Thanks!
I need the great help to do subquery . Have attached the original script and attached screen shot.
Currently am  getting 9 rows.

Expected output is count 6
There are multiple fileids(10671,10672,10700) in the original output (count 9). i need to pick max(isApprove) for those mutiple unique fileids...

My expected out is attached..
Please help me to tune this query...


Kind regards,
Pooja
Current-Output.PNG
Expected-Output.PNG
query.txt
0
Hi All,

I have detail data below :
NoTransaksi                    TglTransaksi            GdgCode   QtyTransaksi          ItemUnitCost                            NilaiKurs             NoTransaksiBeli                PPN  JenisCode
------------------------------ ----------------------- --------- --------------------- --------------------------------------- --------------------- ------------------------------ ---- ---------
GR OLT 20160731                2016-07-31 00:00:00.000 OLT       120.00                14850.000000                            1.00                  NULL                           T    BG  
GR OLT 20160731                2016-07-31 00:00:00.000 OLT       100.00                12976.000000                            1.00                  NULL                           T    BG  
GR OLT 20160731                2016-07-31 00:00:00.000 OLT       100.00                12976.000000                            1.00                  NULL                           T    BG  
GR OLT 20160731                2016-07-31 00:00:00.000 OLT       100.00                12976.000000                            1.00                  NULL                           T    BG  
GR OLT 20160731                2016-07-31 00:00:00.000 OLT       120.00                14850.000000                            1.00                  NULL                           T    BG  
SJ OLT 20160731                2016-07-31 00:00:00.000 OLT       -48.00                14850.000000                

Open in new window

0
I discovered an error in one of my tables where I was allowing NULL values. I went into design and unclicked Allow NULLS. When I went to save this change I received this warning message.

Function 'dbo.MyFunction ' :Schema binding will be removed.

Checking dependencies does show this function is dependent on this table. However, when checking dependencies after the change was committed still shows this function is dependent on this table. Does this dependency need to be dropped in order for the table change to occur and then does it simply gets reinstated after the change?
0
Keep up with what's happening at Experts Exchange!
LVL 12
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

What I am doing is whenever I have to update the records in TABLE A based on a given condition,I need to update table B with a ACTIVATION_STATUS_ID.

ie;  I have to flag SERIAL_PACK_NUMBER in TABLE A and set its value to 1 and the FLAG_COUNT Value in incremental order. Once that is done, I update the TABLE B and set the ACTIVATION_STATUS_ID to 1 (initial default value is 0)

The table A will have duplicate entries for SERIAL_PACK_NUM.

So, here is my code,

BEGIN
UPDATE TABLE A
SET
      FLAG_STATUS_ID=1,
      FLAG_COUNT=(I want to get the next seq value using count(*) when ever there is a SERIAL_PACK_NUM is already in the table row, It should be 0 if the SERIAL_PACK_NUM is not there otherwise, it should be reflecting the number of time the SERIAL_PACK_NUM is there in the system).
WHERE
      <conditions. There is no SERIAL_PACK_NUM in the condition>
END

BEGIN
UPDATE TABLE B
SET
      ACTIVATION_STATUS_ID
WHERE
      SERIAL_PACK_NUMB IN (SELECT SERIAL_PACK_NUMB FROM TABLE A WHERE SERIAL_PACK_NUMB=@SERIAL_PACK_NUMB)
END

Any help in this regards, greatly appreciated. I am using SQL Server 2008R2.
0
Hello:

I'm using SQL Server 2017 and have a doubt regarding which service really executes the jobs when remote devices are implied.

When I create a maintenance plan, this including a job, and the plan is executed, I know it is the Agent Service which executes the job.

My doubt concerns operations performed on a remote disk, for example a job with a backup in a network disk. Is it the Agent Service Account to which write permissions must be granted on the remote disk, or is it the Database Engine Service (MSSQLServer)?

Some link to documents regarding these issues, permissions, execution accounts, etc.,will be welcome.

Thanks in advance,

Juarrero
0
Hello Experts,
I am trying to search a column based table around 150 million rows.  To make the search easier I created a view using PIVOT operator.  When I make a simple search, it takes more than 2 minutes to execute.  The question is, how to make the search faster?

Thank you in advance.

Reference
https://www.experts-exchange.com/questions/29106205/Convert-columns-to-rows-in-SQL.html
0
Hello Experts,
I have a column based table of million of rows.  I am trying to convert the columns to rows for better readability.  See the attachment for details.  Please let me know how to get the output.  Thank you very much in advance.

Thank you!
Pivot.png
0
how to see description of a table in ms sql databasde to see which columns there, which is primary key, which is forign key which are indexed columns etc

please advise
0
SELECT NR_R
FROM SCHEMA1.TABLE_1
where NR_R>
(select round(AVG(NR_R)) FROM SCHEMA1.TABLE_1 )  

i tried round function as above
and
also as below

SELECT NR_R
FROM SCHEMA1.TABLE_1
where NR_R>
(select ROUND(AVG(NR_R)) FROM SCHEMA1.TABLE_1 )  

both of them not working
are these function names and other variable names etc case sensitive?
can yo please advise
error i get is round needs 2,3 arguments which is not clear to me
Msg 189, Level 15, State 1, Line 4
The round function requires 2 to 3 arguments.
0
Opps.xlsxHi, See below my code

What i need to find is user select opportunity stage backwards,
For example user first select the opportunity stage 3 and then user select again Stage 2 .

SELECT Change1.ChangeDate,Change1.OppertunityID,Change1.OppStage 
,ROW_NUMBER() OVER (PARTITION BY Change1.OppStage ORDER BY Change1.OppStage)
,(CASE WHEN Change1.OppStage <Temp.OppStage  AND Change1.ChangeDate<Temp.ChangeDate  THEN 1 ELSE 0 END)
FROM OppStageChange  Change1
INNER JOIN (
SELECT ChangeDate,OppertunityID,OppStage 
FROM OppStageChange 

) Temp ON Change1.OppertunityID=Temp.OppertunityID
WHERE Change1.OppertunityID='       507'
ORDER BY Change1.OppertunityID, Change1.ChangeDate,Change1.OppStage

Open in new window


Sample file attached in the excel file
Any idea much appriciated
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
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
Cloud Class® Course: Certified Penetration Testing
LVL 12
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

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
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
In SQL Server 2008/2012, how  do i check whether this patch KB4057114/KB4057116 is installed or not?
1
I am calling a table valued function which is returning multiple columns in select subquery, yet I know that select query returns single column at a time only.
So how is it possible to do

subquery
     SELECT TOP (1) CRFCA.Name, CRFCA.Rate, CRFCA.ChemicalFamily, CRFCA.WHP, CRFCA.qty, CRFCA.totalMixer
            From fnChemicalRecordForChemicalApplication(@companyID) as CRFCA           
            WHERE .ChemicalApplicationID = chemicalApplication.ChemicalApplicationID
            ) AS value8

Open in new window


Function
   create function fnChemicalRecordForChemicalApplication(@companyID int)
returns table
as
return (
		SELECT  chemicalApplicationChemical.ChemicalApplicationID as ChemicalApplicationID, chemical.Name as Name, chemicalApplicationChemical.Rate as Rate,                                               chemical.ChemicalType as ChemicalFamily, chemical.Whp as WHP, chemicalApplicationChemical.Quantity as qty, chemicalApplicationChemical.TotalMixture as totalMixer
                FROM ChemicalApplicationChemical AS chemicalApplicationChemical              
                INNER JOIN Chemical AS chemical ON chemical.ChemicalID = chemicalApplicationChemical.ChemicalID
				WHERE  (chemical.CompanyID = @companyID)
)

Open in new window

0
Hi

i have installed sql 2014 with a named instance on a server which has also sql 2008 running there. i'm facing a timeout issue every second time when i try to login with a DSN (ODBC). saying "unable to complete the login process due to delay..."

Please advise.

Joseph
0
Hello Folks,

I've a stored procedure in SQL Server along with lots of calculation & computation. Once, I execute it takes about 11 seconds.
But once concurrent users executes then it takes average 42 seconds.
FYI,
1. I am having SQL Server 2017 at local and production with Azure' DB as a service.
2. Isolation level is "Read Committed Snapshot"
3. I've marked NOLOCK hint with all physical tables in stored procedure.
4. Yes, the procedure is also using Temp Tables too.

Do you have any thoughts, why it is taking lots of time?

Best Regards
0

Microsoft SQL Server 2008

49K

Solutions

17K

Contributors

Microsoft SQL Server 2008 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. Major improvements include the  Always On technologies and support for unstructured data types.