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

Hi All,

I have below code.

 UPDATE A
		SET A.NilaiAlokasi = ISNULL(B.TotalNilaiAlokasi, 0)
		FROM TDMONEYPAYDIST A
		INNER JOIN
		(
		SELECT
		KodePembayaran
		, SUM(NilaiAlokasi+NilaiLebih) AS TotalNilaiAlokasi
		FROM TDALCAP A
		WHERE EXISTS
		(
		SELECT
		NULL
		FROM TDALCAP B
		WHERE A.NoAlokasi = B.NoAlokasi
		AND B.NoAlokasi = @NoTransaksi
		)
		GROUP BY
		KodePembayaran
		) B

		ON A.ID = B.KodePembayaran

Open in new window


It is using INNER JOIN, but I think it is not correct, but if I change to LEFT JOIN, then it should not have correct data update.

What should I do ?

Thank you.
0
Using SQL 2008

I have a query that's not working for me

DECALRE @scan @varchar(10) = '1147'
DECALRE @email @varchar(10) = ''
DECALRE @search @varchar(10) = ''


select *
from etc
where (scanlocation like '%'+ @scan + '%' or emailaddress like '%' + @email + '%' or fidlocation like '%'+ @search +'%')

I use like because sometimes we only get part of the whole of the string we are looking for.
I want to use different combinations of data in the parameters, so sometimes run it with all three populated,
sometimes two and one blank or as above with one populated and the other two blank. If I run the query above I get all records back
instead of records where scnalocation = '1147'
0
Trying to pivot results
keeping getting the message that the list contains more items
Can you review to see what i am missing?

CREATE table #GCTotalStatusList( gcvalue int, gcrole int)

INSERT INTO #GCTotalStatusList
            (gcvalue,gcrole)
SELECT  
            COUNT(case when u.active in (0,1) then 1 end) as ttlcode,
            COUNT(case when u.active =0 then 1 end)  as active_counts,
            COUNT(case when u.active =1 then 1 end)  as inactive_counts,
            COUNT(case when u.active in (0,1) and u.orderroletype  IN ('Housestaff','Fellow') then 1 end ) as HSttl_counts,
            COUNT(case when u.active =0 and u.orderroletype  IN ('Housestaff','Fellow')  then 1 end)       as HSactive_counts,
            COUNT(case when u.active =1 and u.orderroletype  IN ('Housestaff','Fellow') then 1 end)        as HSinactive_counts

      FROM   SCM.CV3SecurityGroup sg join SCM.cv3usersecuritygroup usg (nolock) on usg.SecurityGroupGUID = sg.GUID
    join CV3User u (nolock) on u.guid = usg.UserGUID
    WHERE sg.code = 'Growth Charts'
      Group by sg.code, u.active, u.orderroletype
      
      SELECT  *
           
            FROM    
            (
            select
            *
            FROM #GCTotalStatusList
            )d
            Pivot(Max(gcvalue )for gcvalue in (GCTotal, GCActive,GCInactive,HSTotal,HSactive,HSInactive)

            )GCpiv
0
Using a CTE to get totals, would like assistance on adjusting CTE to concatenate multiple rows
I want the output in the following format:
GC total: 177  36/141 HS total: 10/10
These results are for totals as well active and inactive

;WITH
gcttl AS (
            SELECT  COUNT( sg.code)  as gcttl                                      
            FROM   SCM.CV3SecurityGroup sg join SCM.cv3usersecuritygroup usg (nolock) on usg.SecurityGroupGUID = sg.GUID
            join CV3User u (nolock) on u.guid = usg.UserGUID
            WHERE u.active IN (0,1) and sg.code = 'Growth Charts'
            ),
gcttlinactive as
            (
            SELECT  COUNT( sg.code)  as inactivecode                             
            FROM   SCM.CV3SecurityGroup sg join SCM.cv3usersecuritygroup usg (nolock) on usg.SecurityGroupGUID = sg.GUID
            join CV3User u (nolock) on u.guid = usg.UserGUID
            WHERE u.active =0 and sg.code = 'Growth Charts'
            )
 , gcttlactive as
            (
            SELECT  COUNT( sg.code)  as activecode                               
            FROM   SCM.CV3SecurityGroup sg join SCM.cv3usersecuritygroup usg (nolock) on usg.SecurityGroupGUID = sg.GUID
            join CV3User u (nolock) on u.guid = usg.UserGUID
            WHERE u.active =1 and sg.code = 'Growth Charts'
             )
, hsttl as
            (
            SELECT  COUNT( sg.code)  as "hsttlcode"                        
            FROM   SCM.CV3SecurityGroup sg join SCM.cv3usersecuritygroup usg (nolock) on usg.SecurityGroupGUID = sg.GUID
            join CV3User u (nolock) on u.guid = usg.UserGUID
            WHERE (u.active IN (0,1) and sg.code = 'Growth Charts' and …
0
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
I have a failed SQL backup and have no clue why this is happening? Can anyone help with this?

Failed:(-1073548784) Executing the query "BACKUP DATABASE [JDE910] TO  DISK = N'\\\\x.x.x.x..." failed with the following error: "Cannot open backup device '\\\\x.x.x.x\\JDE Images\\SQL Backup\\Daily\\JDE910_backup_2018_07_14_220001_8461617.bak'. Operating system error 1311(There are currently no logon servers available to service the logon request.).
BACKUP DATABASE is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.


X.X.X.X are the IP address of the webserver jde
x.x.x.x is the ip address of the NAS device where the backup has to be moved
0
Hi

This is how query looks like:
First query:

select FullName, SystemUserId from SystemUser
ORDER BY FullName

This query will show 100x systemuserID now to the next one:

update UserEntityUISettingsBase
set RecentlyViewedXml = null
where OwnerId = 'SystemuserID'

ok here comes the question, how do I do this easiest? Declare a var for 100 systemuserID? how do i do this so the 100x systemuserID updates easiest.

Thanks
0
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
Dear expert,

Simple query, I want MSSQL to exclude all the NULL result in columns from the table.

Any easy solution?
Thx
0
I have a SQL 2008 R2 database that has needed a shrink for years but everyone was afraid.   It currently has allocated 688GB and only using 217GB.  This will be moving to a 2016 SQL server and I don't want to have all the unnecessary space moved.  
I have tested restoring the DB to the 2016 SQL and running the shrink to capture how long it takes.  (about 13 hours)   I then test the database on the 2016 SQL and find no issues,  
Now to do it live, I need to shrink the DB on it's production 2008 R2 SQL and no way to test to see how long it takes.  Compatibility doesn't change so there shouldn't be any data issues as well.
This is on a production cluster so I don't have another 2008 R2 SQL instance to test with on  the cluster to determine if it will take longer on the 2008 R2 instance.
Any known factors would help.
0
I am getting this error; 'Could not find stored procedure 'UP_UPDATE_LOG.'' on two different servers with two separate instances of SQL. One instance is 2008r2, and the other is 2016.

I am logged in as SA for the following steps
     1)  I tried to execute the sp, but I get the error; 'Could not find stored procedure 'UP_UPDATE_LOG.''
          a. I even tried this with; execute dbname.dbo.UP_UPDATE_LOG
     2)  I tried to create the sp, and I get this error;  There is already an object named 'UP_LOG_UPDATE' in the database.
     3)  I tried to drop the sp, and I get; Cannot drop the procedure 'UP_UPDATE_LOG,' because it does not exist or you do not have permission.
     4)  I tried to alter the sp, and I get; Commands completed successfully.
     5)  So I tried steps 1 to 3 and got the same errors as before


Any suggestions of what is going on and how to fix this issue?  and this looks like it is only happening to this one stored procedure.

Thank you,

Rich
1
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
Hi Expert,
SSIS using Lookup Table , i need matched with Target table
(See attached screenshot)
01.Invoice Number
02.Invoice Sequence
03. Invoice Line
04. Order Number
05. Order Line
06. Order Realse Number
07. SiteID

Then if not matched records fields ,then need to add to Target table (INSERT all the above 7Lookup unmatched feilds Records)

Can some let me know , looking at screenshot it's correct way to matched.
I have used Cached -Partial
0
when I choose server name (local) for connection string to connect SQL server
if network disconnect the connection is also disconnected why!!!
what's the relation!!!
0
I want to query out all applications and jobs associated with sa account usage. Does someone provide a query for me? Thanks
0
Hello,
I am getting an error:
Msg 8152, String or binary data would be truncated

on this query

ALTER PROCEDURE  [dbo].[wrt]
(
     @tableName varchar(MAX) = null,
	 @ColumnName1 varchar(MAX) = null,
	 @Value varchar(MAX) =
 null,
	 --Table2
	 @tableName2 varchar(MAX) = null,
	 @ColumnName2 varchar(MAX) = null,
	 @Value2 varchar(MAX) = null


)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;  	
	
	   declare @SQL varchar(max) = null
	  -- NONCLUSTERED INDEX [NIX__UNQ__UID_]
	   DECLARE @Year_Ref smallint
		SET @Year_Ref = (SELECT [Year_Ref] FROM YEAR_LOOKUP  WHERE [Year_no]= YEAR(GETDATE()))

	
	SET @SQL = '  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,null as Route,CAST(null as date) as DateFrom,  CAST(null as date) as DateTo,
		(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
 

Open in new window

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

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.