[Last Call] Learn how to a build a cloud-first strategyRegister Now

x

Microsoft SQL Server

159K

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

In my SQL code I have the below code to remove LF and carriage returns.  However, this column, when copy and pasted to excel or exported to excel is creating additional columns for this field when I want all columns in 1 field.
	REPLACE ( REPLACE ( description, CHAR(13) , '' ) , CHAR(10) , ', ' ) AS Description 

Open in new window


Data example
Description
9/1/17 dho: Current LT is 3 wks., , *CC, LT: BONDED 25pc reels - 2wks - other suppliers for MOQ 1, AMU: 6, LPP: 286.50ea for reels - other suppliers 1: 350.00, LQP: 25      , LDP: 09.17.2015      ,


Below are going to different columns
LQP: 25
LDP: 09.17.2015      ,

Thanks
0
Technology Partners: We Want Your Opinion!
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

morning team,

What is the best approach and practice to upgrade a production sql server 2008 r2 (on win server 2008 r2)  to sql and win 2016 servers?

JohnE
0
Hello,
I have a query which works perfectly. Need to check one condition. Please find the excel sheet attached .
Query:

;WITH Drivers
AS ( SELECT   J.DriverID ,
              MAX(J.CollectionDatetime) AS LastAllocStDate
     FROM     Table_JOB J
     GROUP BY J.DriverID ) ,
     A
AS ( SELECT j.ID ,
            j.ClientName ,
            j.DriverID ,
            CAST(j.CollectionDateTime AS DATE) AS AllocStDate ,
            j.Allocation ,
            CAST(j.AllocEndDate AS DATE) AS AllocEndDate
     FROM  Table_JOB j
            INNER JOIN Drivers d ON j.driverId = d.driverId
                                    AND j.CollectionDatetime = d.LastAllocStDate ) ,
     T
AS ( SELECT B.NAME ,
            A.Allocation ,
            A.AllocStDate ,
            A.AllocEndDate ,
            A.ClientName ,
            A.ID ,
            IIF(A.AllocStDate <= CAST(GETDATE() AS DATE) AND A.AllocEndDate >= CAST(GETDATE() AS DATE), 'Allocated', NULL) AS OnJob
     FROM   A
            INNER JOIN FieldResource B ON A.DriverID = B.ID ) ,
     Z
AS ( SELECT DISTINCT T.Name ,
            T.Allocation ,
            T.AllocStDate ,
            T.AllocEndDate ,
            IIF(T.OnJob IS NULL, NULL, T.ClientName) AS ClientName ,
            IIF(T.OnJob IS NULL, NULL, T.ID) AS ID ,
            IIF(T.OnJob IS NULL, 'YES', NULL) AS NotAllocated ,
            T.OnJob AS Allocated
     FROM   T )
,CTE1 AS
(
	SELECT   
			 Z.Name ,
			 Z.Allocation, 
			  Z.Allocated ,
			 Z.AllocStDate ,
	

Open in new window

0
Hello,
Need to add a condition in the this existing query
;WITH Drivers
AS ( SELECT   J.DriverID ,
              MAX(J.CollectionDatetime) AS LastAllocStDate
     FROM     Table_JOB J
     GROUP BY J.DriverID ) ,
     A
AS ( SELECT j.ID ,
            j.ClientName ,
            j.DriverID ,
            CAST(j.CollectionDateTime AS DATE) AS AllocStDate ,
            j.Allocation ,
            CAST(j.AllocEndDate AS DATE) AS AllocEndDate
     FROM   Table_JOB j
            INNER JOIN Drivers d ON j.driverId = d.driverId
                                    AND j.CollectionDatetime = d.LastAllocStDate ) ,
     T
AS ( SELECT B.NAME ,
            A.Allocation ,
            A.AllocStDate ,
            A.AllocEndDate ,
            A.ClientName ,
            A.ID ,
            IIF(A.AllocStDate <= CAST(GETDATE() AS DATE) AND A.AllocEndDate >= CAST(GETDATE() AS DATE), 'Allocated', NULL) AS OnJob
     FROM   A
            INNER JOIN FieldResource B ON A.DriverID = B.ID ) ,
     Z
AS ( SELECT DISTINCT T.Name ,
            T.Allocation ,
            T.AllocStDate ,
            T.AllocEndDate ,
            IIF(T.OnJob IS NULL, NULL, T.ClientName) AS ClientName ,
            IIF(T.OnJob IS NULL, NULL, T.ID) AS ID ,
            IIF(T.OnJob IS NULL, 'YES', NULL) AS NotAllocated ,
            T.OnJob AS Allocated
     FROM   T )
,CTE1 AS
(
	SELECT   
			 Z.Name ,
			 Z.Allocation, 
			  Z.Allocated ,
			 Z.AllocStDate ,
			 Z.AllocEndDate ,
			 Z.ClientName ,
			 Z.ID ,
			 

Open in new window

0
This will be difficult to explain what I want, but I'll do my best.

I have some records in a table that are names. I want to get results back of the names that have existed before in the table, and have some other column attributes. For example, from the below sample data, I would want to return ID 2 and 3 only, first  because the name Bob already exists AND has a Note of Happy AND is open. If the first bob had any other note, I wouldn't want to return it. I want 3 to return because it has a reason of Yellow and is Open. Below is the query I am trying...

ID |Name | Note    | Reason | Status
1       Bob    | Happy  |Sunny   | Closed
2      Bob     | Nice      |Sunny   | Open
3      Henry | Wow    |Yellow  | Open
4       John   | Thing   |Cloud   | Closed

select ID, Name, Note, Reason, Status
from myTable
where status = 'Open'
and (name = name or reason = 'Yellow')

Open in new window

0
Hello guys

I have some clients in mssql 2005 and 2008 , I want to update them to 2014 but I don't have the idea the impact with tables, functions ,views and store procedures.

Is there any place that I could get those information what changes are between them?

thanks
Alexandre
0
I know I can use the DATEADD function to take a date in TSQL and add a YEAR or subtract it. However how can I keep the weekday shifting.

For instance I can take 1/10/2018 and subtract a year to make it 1/10/2017 or add a year 1/10/2019. However all three of those days represent different weekdays.

What is the easiest method to see the same weekday a year in the future or a year in the past?

So extending the example above I would take 1/10/2018 and adding a year would return 1/9/2019 and subtracting a year would return 1/11/2017.
0
Hello,

I need to use the big int and I am not sure what version of mssql has the kind of field.

I use the mssql 2008 but I am not sure if in mssql 2005 there is this field bigInt.

Could you help me on this?

Thanks
0
Hello,

How can I do Unattended installation SQL Server 2017 Reporting Services ?
Do you have extract SQLServerReportingServices?
if yes how?

Thanks

regards
0
Hello,

I have this query which works perfectly but need few modifications :

WITH Drivers
AS ( SELECT   J.DriverID ,
              MAX(J.CollectionDatetime) AS LastAllocStDate
     FROM     TABLE_JOB J
     GROUP BY J.DriverID ) ,
     A
AS ( SELECT j.ID ,
            j.ClientName ,
            j.DriverID ,
            CAST(j.CollectionDateTime AS DATE) AS AllocStDate ,
            j.Allocation ,
            CAST(j.AllocEndDate AS DATE) AS AllocEndDate
     FROM   TABLE_JOB j
            INNER JOIN Drivers d ON j.driverId = d.driverId
                                    AND j.CollectionDatetime = d.LastAllocStDate ) ,
     T
AS ( SELECT B.NAME ,
            A.Allocation ,
            A.AllocStDate ,
            A.AllocEndDate ,
            A.ClientName ,
            A.ID ,
            IIF(A.AllocStDate <= CAST(GETDATE() AS DATE) AND A.AllocEndDate >= CAST(GETDATE() AS DATE), 'Allocated', NULL) AS OnJob
     FROM   A
            INNER JOIN FieldResource B ON A.DriverID = B.ID ) ,
     Z
AS ( SELECT DISTINCT T.Name ,
            T.Allocation ,
            T.AllocStDate ,
            T.AllocEndDate ,
            IIF(T.OnJob IS NULL, NULL, T.ClientName) AS ClientName ,
            IIF(T.OnJob IS NULL, NULL, T.ID) AS ID ,
            IIF(T.OnJob IS NULL, 'YES', NULL) AS NotAllocated ,
            T.OnJob AS Allocated
     FROM   T )
SELECT   Z.Name ,
         Z.Allocation, 
         Z.AllocStDate ,
         Z.AllocEndDate ,
         Z.ClientName ,
         Z.ID ,
      

Open in new window

0
Free Tool: IP Lookup
LVL 11
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

We are on SQL Server 2012.
We have three tables. TB_MASTER, TB_DETAIL and TB_FINAL
   
TB_MASTER	
OWNER_ID	OVER_AMOUNT_NO
123	      400
234	      300

TB_DETAIL
OWNER_ID	WORKER_ID	tobe_paid_AMOUNT_NO
123	  555	             200
123	  666	             300
234	  777	             200

Open in new window


I want the TB_FINAL to have the inserted rows and TB_MASTER to have updated rows as below:
TB_FINAL		
OWNER_ID	WORKER_ID	tobe_paid_AMOUNT_NO
123	555	              0
123	666	             100
234	777	             100

TB_MASTER
OWNER_ID	OVER_AMOUNT_NO
123	     0
234	     200

Open in new window


The explanation to the desired output is as follows:
* Owner 123 owes 400 to the agency and so, the amount to be paid 200, to worker  555 will be deducted from 400.
* After the above deduction, Owner 123 still owes 200 (400-200). Since Worker 666 needs to be paid 300, he will be paid 100 (300 - 200).
* Owner 234 owes 300 to the agency and the worker 777 is to be paid 200. So, worker 234 gets paid only 100.
* TB_MASTER table needs to update the outstanding overpayment for the Owners.

Can someone let me know of an easy way of achieving this?
0
Is there a way to strip HTML (Rich Text actually) from a SQL Server column in a query?

I've inherited a SQL Server database with an Access frontend.  Users have been allowed to edit a memo (long text) fields using Rich Text format, which leaves Rich Text (Div and font) tags embedded in the fields.  In Access, I can strip these tags using the PlainText function, but I'm working on editing a report in SSRS, and need to strip these tags using a SQL Server function.
0
I have a table like this

NAME , SegDate, Segment

I need to select the records where the latest segdate per NAME is <> 'JK' but where they do have a segdate that is not in the past
0
hi,
i am having one scheduled sp. if i shrink the database scheduled running fastly ( contain while loop  and 40 lak loans running one by one loans ) . otherwise running slowly. is this any thing we need to do?  i have created index and missing index also.
0
I currently have the resultset as below:

Program                        Rundate              Userid
---------------------------------------------------------------------
P:\Application2.rpt      2017-11-10         john                    
P:\Application2.rpt      2017-11-11         sam                        
P:\Application2.rpt      2017-11-13         sam

P:\Application4.rpt      2017-11-12         john  
P:\Application4.rpt      2017-11-14         sam                  
P:\Application4.rpt      2017-11-15         luna

P:\Application1.rpt      2017-11-14         john                    
P:\Application1.rpt      2017-11-15         luna                  
P:\Application1.rpt      2017-11-16         sam








I need to get the resultset as shown below:



Program                       LastDateRun1   LastRunUser1  LastDateRun2    LastRunUser2  LastDateRun3   LastRunUser3
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------

P:\Application2.rpt      2017-11-10         john                    2017-11-11       sam                   2017-11-13         sam
P:\Application4.rpt      2017-11-12         john                    2017-11-14       sam                   2017-11-15         luna
P:\Application1.rpt      2017-11-14         john                    2017-11-15       luna                   2017-11-16         sam
0
I need to get the last 3 users and their run dates for the programs we created.

For example: I have the data as shown below in the table

LogKey UserId   Program                    DateRun
-------------------------------------------------------------------
147715 john     L:\ApplicationReports4.rpt 2017-03-27 20:16:09.000
147796 marta    P:\ApplicationReports4.rpt 2017-03-29 11:11:15.000
147798 dealo    P:\ApplicationReports7.rpt 2017-03-29 11:15:15.000
149339 rubyo    K:\ApplicationReports4.rpt 2017-05-12 09:57:53.000
149340 runia    P:\ApplicationReports8.rpt 2017-05-12 09:58:02.000
150925 luna     P:\ApplicationReports6.rpt 2017-07-04 12:51:23.000
151969 rubyo    P:\ApplicationReports9.rpt 2017-08-09 16:09:23.000
152061 luna     M:\ApplicationReports4.rpt 2017-08-10 11:54:19.000
154722 john     P:\ApplicationReports2.rpt 2017-11-10 16:01:11.000
154723 sam      P:\ApplicationReports4.rpt 2017-11-10 16:01:17.000
154742 sam      P:\ApplicationReports1.rpt 2017-11-13 09:00:44.000


The Resultset should be as shown below:

154722 john     P:\ApplicationReports2.rpt 2017-11-10 16:01:11.000
154723 sam      P:\ApplicationReports4.rpt 2017-11-10 16:01:17.000
154742 sam      P:\ApplicationReports1.rpt 2017-11-13 09:00:44.000
0
I'm trying to execute the following script:
BEGIN TRY
  BEGIN TRANSACTION
	IF OBJECT_ID('fn.FOO') IS NOT NULL
		DROP FUNCTION [fn].[FOO]
	GO

	CREATE FUNCTION [fn].[FOO]
		(
		  @Value VARCHAR(4000)
		)
	RETURNS VARCHAR(4000)
	WITH SCHEMABINDING
	AS
		BEGIN
			-- DO STUFF HERE
			RETURN @Value;
		END;
	GO

	GRANT EXECUTE ON [fn].[FOO] TO [PUBLIC]
	GO

	INSERT INTO [SCRIPTRUN_LOG]
	(
		[NAME],
		[EXECUTED],
		[EXECUTEDDATE],
		[RESULTMESSAGE]
	)
	VALUES
	(
		'ALTER FOO',
		'Y',
		GETDATE(),
		ERROR_MESSAGE()
	)
  COMMIT TRANSACTION
END TRY
BEGIN CATCH
  ROLLBACK TRANSACTION;
  THROW
END CATCH

Open in new window

The idea here is that it will attempt to drop and create a function and, if successful, add an entry into a table named SCRIPTRUN_LOG which will indicate that the function has been executed. If anything fails then the transaction gets rolled back so no entry exists in SCRIPTRUN_LOG. Unfortunately, the above example doesn't work because there is a GO statement within the transaction. However, because I'm creating a function here and the function must be the only statement in a batch I need a GO to separate batches. So I'm a little stuck here. Anyone have any ideas on how to get around this?
0
For example I have the below MDX query where it gives me all the data i want. however i would like to column slice all the 20K DWT's in one column and all the 25K DWT's in another column. Could I replace all the information in the FROM columns section with something like  [Voyage].[Vessel Tag].&[LIKE %20K DWT%] , [Voyage].[Vessel Tag].&[LIKE %25K DWT%] ON COLUMNS?

SELECT NON EMPTY Hierarchize({DrilldownLevel({[Voyage].[Vessel Tag].[All]})}) ON COLUMNS ,
       NON EMPTY Hierarchize({DrilldownLevel({[Date].[Month].[All]})}) ON ROWS  
FROM (SELECT ({[Voyage].[Vessel Tag].&[FCCSP, 20K DWT, Eco, M],
               [Voyage].[Vessel Tag].&[FCCSP, 20K DWT, Eco, H],
               [Voyage].[Vessel Tag].&[FCCSP, 20K DWT, Eco, F],
               [Voyage].[Vessel Tag].&[FCCI, 20K DWT, Eco, Ma],
               [Voyage].[Vessel Tag].&[FCCI, 20K DWT, Eco, Le],
               [Voyage].[Vessel Tag].&[FCCI, 20K DWT, Eco, Ho],
               [Voyage].[Vessel Tag].&[FCCI, 20K DWT, Eco],
                     [Voyage].[Vessel Tag].&[FCCSP, 25K DWT, Eco, Marc],
               [Voyage].[Vessel Tag].&[FCCSP, 25K DWT, Eco, Ho],
               [Voyage].[Vessel Tag].&[FCCSP, 25K DWT, Eco, Fra],
               [Voyage].[Vessel Tag].&[FCCI, 25K DWT, Eco, Ma],
               [Voyage].[Vessel Tag].&[FCCI, 25K DWT, Eco, Le],
               [Voyage].[Vessel Tag].&[FCCI, 25K DWT, Eco, Hy],
               [Voyage].[Vessel Tag].&[FCCI, 25K DWT, Eco]}) ON COLUMNS  
      FROM [cube])
WHERE …
0
Installing SQL 2017 we were asked to "Accept" on "Microsoft R Open".  We looked it up some sort of open source, but What is it to us Microsoft product users?, also  Why click "Accept" it in this SQL install? and How can we take advantage of this "Microsoft R Open"?
0
Efficient way to get backups off site to Azure
LVL 1
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

We plan to begin coding (Delphi 10.x) application for smartphone, in a first stage, accessing the customer database, the smartphone being connected to the customer local network.
It seems I have two alternatives : Direct access or REST/HTTPS layer.
Is there some documentation explaining advantages and disavantages of both methods ?
0
I am able to back up and restore data from one DB to other DB by sqlcmd command. But I need to restore data from .bak file to another DB which have DB name different. Servers are SQL and command I am using is SQLCMD command.
0
This script returns the subject error. Done this sort of thing before, been a while. Snow Blind

/*
Error Message
(16 row(s) affected)
Msg 512, Level 16, State 1, Line 59
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

(16 row(s) affected)
*/

 declare @Po as int
  set @Po = 99308

  declare @PoHistory table
      (
       [ID] [int] identity (1, 1)
      ,[Po] [int] null
      ,[Item#] [float] null
      ,[Qty] [float] null default (0.0)
      ,[RecQty] [float] null default (0.0)
      ,[Amount] [decimal] (10, 2) default (0)
      ,[AuthorizedAmount] [decimal] (10, 2) default (0)
      ,[PendingAmount] [decimal] (10, 2) default (0)
      ,[PaidAmount] [decimal] (10, 2) default (0)
      )
            
      insert into @PoHistory
            (
             [Po]
            ,[Item#]
            ,[Qty]
            ,[RecQty]
            ,[Amount]
            ,[AuthorizedAmount]
            
            )
            (
            select
                   [PO_ID]
                  ,[ITEM_]
                  ,[QTY]
                  ,[RECVDQTY]
                  ,[TOTCOST]
                  ,[AuthorizedAmount]
            from [TVENDPOD]
            where [RECORD_] is not null
            and [PO_ID] = @Po
            )
            
      update @PoHistory set [PendingAmount] =
            (
            select
                  sum( [TOTCOST])
            from [ACCOUNTSPAYABLE_DETAIL]
            where [RECORD_] is not null
            and [PO_ID] = @Po
            and [CHECKNUMBER] = 0
            and [PAID_DATE] is null
            group by [PO_ID], [ITEM_]
            )                  
      from @PoHistory, [ACCOUNTSPAYABLE_DETAIL]
      where [ACCOUNTSPAYABLE_DETAIL].[PO_ID] = [Po]
      and …
0
I am using Developer 14, trying to create a SPROC that will return information contained in a view 'vtblProperty_Muni_LB_TB_Owner'

I pass three fields to the SPROC that are used to find the record I'm looking for, or not find it.

If a matching record is found I want to return three fields back to the routine calling the SPROC.  If a matching record is not found I return 0, 'UnKnown' and  'UnKnown' respectively.

This is what I have so far:
Create PROCEDURE [dbo].[spGetPropertyIDOwnerAndPropAddrFromMuni_LB_TB] 
	-- Add the parameters for the stored procedure here
	@Muni             int = 0,
	@LotBlock         nvarchar(30) = null,
	@TieBreaker       nvarchar(2) = null,
	@PropertyRecID    int = 0             OUTPUT,
	@PropertyOwner    nvarchar(10) = null Output,
	@PropertyAddress  nvarChar(10) = null Output
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here

	Set @PropertyRecID = IsNull(PropertyRecID,0),
		@PropertyOwner = isNull(LandOwner,'UnKnown'),
		@PropertyAddress = IsNull(PropAddr,'UnKnown')
	From vtblProperty_Muni_LB_TB_Owner
Select PropertyRecID, LandOwner, PropAdd 
	Where Muni            = @Muni         and
	      LotBlock        = @LotBlock     and
		  TieBreaker      = @TieBreaker  

Open in new window


But I am getting an syntax error:
Msg 102, Level 15, State 1, Procedure spGetPropertyIDOwnerAndPropAddrFromMuni_LB_TB, Line 25
Incorrect syntax near ','.

This is line 25 and 26
      Set @PropertyRecID = IsNull(PropertyRecID,0),
            @PropertyOwner = isNull(LandOwner,'UnKnown'),

Can anyone see what the issue is?
0
Hello Experts,
I am trying to understand the purpose/need of the TRUNC(date). I went through the documentation in Oracle and the example :

SELECT TRUNC(TO_DATE('27-OCT-92','DD-MON-YY'), 'YEAR')
  "New Year" FROM DUAL;
 
New Year
---------
01-JAN-92

I am not able to understand why Oct becomes Jan here. Please help me understand this behavior and any real scenarios where this method is used.
0
Well I have had a bit of an overwhelming SQL introduction in one of my recent deployments of a clients server. In the past we have never had to deal with it because the support staff for the product using it always maintained it. Well it went south with them and I had to figure it out as I went but it left me with the wild notion that I cannot depend on the support group to help. I need to learn SQL quite simply. I know its a vast amount of knowledge, best practices etc and there are a ton of paid options for training on this. What I need is a tool set or site or DIY learning method of learning the basics of SQL, how to administer it and maintain it from a introduction standpoint. Does anyone have any suggestions for learning this or a good path to follow in picking it up?

I have used sites like Codeacademy in the past etc but even they have went to paid versions now. Looking for some get started info.

Thanks in advance!
0

Microsoft SQL Server

159K

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.