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

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.