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

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 All,

I want to get current language id.
I have below code :

SELECT
    -- c => currency
    -- n => numeric
    FORMAT(987654321, N'N', C.culture) AS some_number
,   FORMAT(987654321, N'c', C.culture) AS some_currency
,   C.culture
FROM
    (
        -- Language culture names
        -- http://msdn.microsoft.com/en-us/library/ee825488(v=cs.20).aspx
        VALUES
            ('en-US')
        ,   ('en-GB')
        ,   ('ja-JP')
        ,   ('Ro-RO')
        ,   ('el-GR')
    ) C (culture);

Open in new window


I want to do something like :

SELECT
    -- c => currency
    -- n => numeric
    FORMAT(987654321, N'N', C.culture) AS some_number
,   FORMAT(987654321, N'c', C.culture) AS some_currency
,   C.culture
FROM
    (
        -- Language culture names
        -- http://msdn.microsoft.com/en-us/library/ee825488(v=cs.20).aspx
       SELECT ...... (get current system culture)
        
    ) C (culture);

Open in new window


How could I do it ?

Thank you.
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
I just installed Visual Studio Community 2017 with the SSDT tool kit, connected to my SQL server opened a table  but Diagram, Criteria and Results panes are grayed out. In "Tools" | "Options" | "Database Tools" | "Query and View Designers" all the checkboxes for the Diagram Pane, Criteria Pane, SQL Pane and Results Pane are checked. I am extremely new to VS I normally do all my work in SSMS.
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
Hello Experts,
I am trying to run a SSIS package using Visual Studio 2015.  When execute it with Start Debugging mode, the package runs just fine.  But when run in Start Without Debugging it fails with the below error message.  Any idea what is going on?  Please try to help.  Thank you in advance.

Error Message:  The task has failed to load. The contact information for this task is ""

Thank you!
0
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
Thank you all in advance for your help!

I need some help enabling the following script with loop capabilities.

In other words, the script should cycle through the list of recipients sending an  email to each.

Thanks again!



DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)
Declare @email NVARchar(4000)
Declare @count NVARCHAR(MAX)
Declare @lastname VARCHAR(MAX)


Set @count =
(Select count(email)
FROM  vw_Porivder_GenbaWalk_Activity_PM_SS_6_18_18
Where Created > ' 2018-4-1'
and email = 'ana@aol.com')



Set @lastname =
(
Select distinct (lastname) from vw_Porivder_GenbaWalk_Activity_PM_SS_6_18_18
Where Created > ' 2018-4-1'
and email = 'ana@aol.com'
)



SET @xml = CAST(( SELECT email AS 'td','', convert (varchar, Created, 101) as 'td','', LOC AS 'td'
FROM  
vw_Porivder_GenbaWalk_Activity_PM_SS_6_18_18
Where Created > '2018-4-1'
and email = 'ana@aol.com'
order by created desc







FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))



SET @body ='<html><body><H3>'+ 'Dr.' + @lastname + ','
+
' Currently ' + @count + ' genba walks have been registered for you this quarter.'
+
' '+ 'Please let us know if you have any questions regarding this count.'

+ ' '
+ 'Thank you!'
+

'</H3>
<table border = 1>
<tr>
<th> email </th> <th> Created </th> <th> Location </th></tr>'    

 
SET @body = @body + @xml +'</table></body></html>'


EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'ACE DB Admin', -- replace …
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
SELECT   COUNT(*) as total_items, "Category" =  
      CASE  
         WHEN NR_R < 1645 THEN 'less costly'
         WHEN NR_R > 1645 AND PKG_NR_R < 1700 THEN 'ok costly'  
         WHEN NR_R > 1800 THEN 'high costly'          
         ELSE 'not listed to sell'  
      END  
   
FROM SCHEMA1.TABLE_1
GROUP BY  NR_R
GO

above query working fine.

But i am trying to transpose data to look in column view where each category shows in the below column format.



less costly  ok costly  high costly
23                 50             77

SELECT   SUM (CASE WHEN NR_R < 1645 THEN 1 ELSE 0) as "less costly"
         SUM (CASE WHEN NR_R > 1645 and WHEN PKG_NR_R < 1700 THEN 1 ELSE 0) as "more costly"
         SUM (CASE WHEN NR_R > 1800 THEN 1 ELSE 0) as "most costly"
         ELSE 'not listed to sell'  
           
FROM SCHEMA1.TABLE_1

GROUP BY  NR_R
GO
i tried as above says incorrect syntax with )

Please advise
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
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
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
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 had this question after viewing SQL Replication problem..

Hello, experts.  I was able to successfully configure my publisher/distribution servers, however, when I created the near real time subscriptions, I ran into issues.  I then attempted to tear down replication and start over, however, I am now receiving the "Could not connect to server 'DISTSVR123' because distributor_admin is not defined as a remote login at the server....  

I am attempting to remove the publications previously created and am unable to do so because of  the aforementioned error.  The servers have not been renamed as that is the solution I'm seeing over and over when attempting to find the solution on my own.  Any assistance would be tremendously appreciated.  

Nikki
0
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 this query but it seems to take a really long time to complete. is there any modifications I could do that could perhaps make it a little more efficient?  I was envisaging for a year and a halfs worth of data, perhaps taking a few minutes, but I stopped it before at 23 minutes.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
declare @fromdate date
declare @todate   date
set @fromdate = '2017-01-01'
set @todate   = '2018-05-05'
SELECT 
c.de_number
,client.cl_number
,c.de_status
,Max(CASE  WHEN a1.co_type = '2'  THEN a1.co_description  ELSE c.de_status END) Prev_Stat
,c.de_type
,c.de_collector
,c.de_salesman
,c.de_listed_date
,c.de_owned_by_debtor
,c.de_principal
,c.de_principal + c.de_adjustments Netprin
,c.de_paid
,c.de_last_payment_date
,c.de_last_worked_date
,c.de_active
,c.de_poe
,c.de_zip
,CASE  WHEN c.de_cell_phone IS NOT NULL  THEN 'X'END Good_cell
,CASE  WHEN c.de_home_phone IS NOT NULL  THEN 'X'END Good_landline
,CASE  WHEN c.de_email IS NOT NULL  THEN 'X'END Good_Email
,Max(case when (co.co_description = 'DMOT Request for payment letter' OR co.co_description = 'DMOT Small Balance Letter') AND co.co_type = '0' then co.co_date end) first_demand_letter
,Max(case when co.co_description = '1st Demand Email' AND co.co_type = '0' then co.co_date end) first_demand_email
,Max(case when co.co_description = 'DMOT 48 Hour Notice' AND co.co_type = '0' then co.co_date end) 'DMOT-48hour'
,Max(case when co.co_description = '48 Hour  -Email' AND co.co_type = 

Open in new window

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.