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

Problem: Update query with "SET" utilizing an Aggregate Function

Wondering if you would be so kind as to assist me with formatting this query the property way.

Here is the Select Query:


SELECT     zzz_po_OpenPurchOrd.InvtID, SUM(zzz_po_OpenPurchOrd.QtyPending) AS QtyPendPO, ItemSite.QtyOnPO, ItemSite.SiteID
FROM         zzz_po_OpenPurchOrd LEFT OUTER JOIN
                      ItemSite ON zzz_po_OpenPurchOrd.InvtID = ItemSite.InvtID
WHERE     (zzz_po_OpenPurchOrd.InvtID like 'googsx%')
GROUP BY zzz_po_OpenPurchOrd.InvtID, ItemSite.QtyOnPO, ItemSite.SiteID
HAVING      (ItemSite.SiteID = 'WEST') and SUM(zzz_po_OpenPurchOrd.QtyPending) <>ItemSite.QtyOnPO

Open in new window


--the query below comes from converting a Select into an Update Query in Designer
--the goal is to updates the Itemsite.QtyonPO to actual pending Purchase order quantities (QtyPendPO --this is the calculation on the above select Query)


UPDATE    ItemSite
SET              QtyOnPO =SUM(zzz_po_OpenPurchOrd.QtyPending)
FROM         zzz_po_OpenPurchOrd LEFT OUTER JOIN
                      ItemSite ON zzz_po_OpenPurchOrd.InvtID = ItemSite.InvtID
WHERE     (zzz_po_OpenPurchOrd.InvtID LIKE 'googsx%') AND (ItemSite.SiteID = 'WEST') AND (zzz_po_OpenPurchOrd.QtyPending <> ItemSite.QtyOnPO)

Open in new window


Here is the error returned:
Msg 157, Level 15, State 1, Line 2
An aggregate may not appear in the set list of an UPDATE statement.
0
I need to migrate a server running Windows 2008 and SQL 2008 to Windows 2012 and SQL 2012.  What is the best approach?
0
Hi,
i got a windows 10 pro 64 bit desktop.
for some reason. windows update is not working,

i tried . - windows troubleshooting within windows
windows update troubleshooter
did check disk
did sfc/scan
deleted software distribution folder. restarted windows update service.
 but still its giving same error.  attached a pic of the error.

main reason i want update to work is - i need .netframework 2.0 from turn on feautres. when i try to enable .netframework2.0 it tries to download from windows update.

if my update does not work .  any other way to install .netframework2.0 ???
update.PNG
0
Hi,

In current production environment, we have requirement to truncate approx 860 million records from table in MS SQL server 2008. Please advise the best option to truncate table without impacting end users within production environment.


Regards
Tom
0
I am getting an error message
Error Executing Database Query. [Macromedia][SQLServer JDBC Driver][SQLServer]Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

I do know where the problem is but do not know how to correct the issue.

      
                DECLARE @userKey INT
                SET @userKey = 71963

                DECLARE @Dob   datetime
                SELECT  @Dob= (select dateOfBirth from users where userKey = 71963)
           
            SELECT top 1 F.sessionKey,F.locationKey,F.locationkeylist,F.officeStateKey,F.sessionStart,F.sessionEnd,F.locationName,F.address1,F.address2,F.city,F.state,F.zip,F.gender,F.badgeNum,
			F.seatCount,F.seats,F.availSeats,F.btwScheduleThreshold,F.availStateCode,F.repost,F.ConflictIn,F.ConflictIn2,F.buddyHoldIn,F.firstname,F.lastname,F.searcherAge,F.age,F.multiLoc
            FROM
            (
            	SELECT T.*,
                case WHEN T.availSeats = 1 THEN
                    (SELECT (CONVERT(int, CONVERT(varchar, T.sessionStart, 112)) - CONVERT(int, CONVERT(varchar, U.dateOfBirth, 112)))/10000 FROM users U inner join sessionMap SM2 on SM2.userKey = U.userKey WHERE SM2.sessionKey = T.sessionKey)
                    WHEN T.repost = 1 THEN
                    (SELECT (CONVERT(int, CONVERT(varchar, T.sessionStart, 112)) - CONVERT(int, CONVERT(varchar, U.dateOfBirth, 112)))/10000 FROM users U inner join 

Open in new window

0
I have two numbers in separate columns in my sql view. One is just the number of minutes and the other is the number of seconds. How can I combine these into one value that shows mm:ss? I tried just doing [Mins] + ':' + [Seconds] but if the seconds are 0 then it looks like this: 2:0. I need it to look like this: 2:00. How can I do this?
0
Hi,

Thank you experts for taking a look at my question.

Basically I have the below stored proc I am trying to create, The query itself works, but for some reason it is not letting my create a stored proc.

Could you please help me work out why?

USE [cv_masterdb]
GO

/****** Object:  StoredProcedure [dbo].[spdatereturn]    Script Date: 15/02/2019 10:37:03 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spmonthdiffnew]
	-- Add the parameters for the stored procedure here
	@monthfrom date,
	@monthto date
	
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
    -- Insert statements for procedure here
--DECLARE @firstDayOfListedMonth DATE = CONVERT(DATE, DATEADD(DAY, -DAY(GETDATE()) + 1, DATEADD(MONTH, - @monthsBack, GETDATE())));
--DECLARE @lastDayOfListedMonth DATE = CONVERT(DATE, DATEADD(DAY, -DAY(GETDATE()), DATEADD(MONTH, - @monthsBack + 1, GETDATE())));

select case when  DATEPART(DAY, @monthfrom) <=  DATEPART(DAY, @monthto) 
    then datediff(month, @monthfrom, @monthto)  
    else datediff(month, @monthfrom, @monthto) -1  
    END
GO

Open in new window


Basically it is a stored proc to return back the number of months in between 2 given dates.

many thanks for your help!
0
In the function below...

I am building a #temp table that will have from 20k - 5k records in it
I am then doing a select on that table

Select * from #temp
WHERE 1 = 1
      AND dbo.brkOwnsTimeshareContract(ISNULL(b.ExternalID2, '0')) = 1;

Open in new window


It seems slow... and I am wondering if I have a better solution or tweak on either my select or the underlying function itself

The dbo.everyware_t_contract table generally has @ 60k records in it

ALTER FUNCTION [dbo].[brkOwnsTimeshareContract]
    (
        @OwnerID INT = NULL
    )
RETURNS TINYINT
AS
    BEGIN

        DECLARE @OwnerBucket TINYINT = 0;

        IF EXISTS (   SELECT ContractID
                      FROM   dbo.everyware_t_contract c
                      WHERE  c.OwnerID = @OwnerID
                             AND (   c.ContractStatus = 'Active'
                                     OR CHARINDEX('Phase', c.ContractStatus) > 0
                                     OR CHARINDEX('Pender', c.ContractStatus) > 0 
									 OR CHARINDEX('Suspense', c.ContractStatus) > 0 
									 OR CHARINDEX('Not Back', c.ContractStatus) > 0 
									 OR CHARINDEX('Presale', c.ContractStatus) > 0 )
                             AND NOT (   CHARINDEX('club', c.ContractType) > 0
                                         OR CHARINDEX('guest', c.ContractType) > 0
                                         OR CHARINDEX('goc co', c.ContractType) > 0
                                         OR CHARINDEX('goc breck', 

Open in new window

0
Hi Experts,

Thank you for taking the time to take a look at my question - your help is very much appreciated and is invaluable!

basically I want to create a stored procedure, that will return 2 dates.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE datereturn
	-- Add the parameters for the stored procedure here
	<@monthsback int>
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
DECLARE @firstDayOfListedMonth DATE = CONVERT(DATE, DATEADD(DAY, -DAY(GETDATE()) + 1, DATEADD(MONTH, - @monthsBack, GETDATE())));
DECLARE @lastDayOfListedMonth DATE = CONVERT(DATE, DATEADD(DAY, -DAY(GETDATE()), DATEADD(MONTH, - @monthsBack + 1, GETDATE())));
END
GO

Open in new window


Should I just create a temptable that writes the 2 dates to a field and then do a select * from temptable ? if that is the case could you please help with that part?
0
At this link:

https://stackoverflow.com/questions/92082/add-a-column-with-a-default-value-to-an-existing-table-in-sql-server


I saw this example that works fine. It adds a column to the table and sets the default value of every row in that column to 1.

ALTER TABLE Protocols
ADD ProtocolTypeID int NOT NULL DEFAULT(1)
GO

Is there a way to write this line in two statements?
ADD ProtocolTypeID int NOT NULL DEFAULT(1)


So with the first statement add the column.
then with a separate statement set the default value to 1
0
Hi Experts!

firstly, much appreciated for taking a look at my question.

Basically my question is this:

I have these 2 queries:


select debtor.de_status ,count(de_number) as 'Number of A/Cs'
from debtor
join client on client.cl_rowid =  debtor.de_rowid_client
join debtor_status on debtor.de_status = debtor_status.ds_code
where de_active = 'A' and debtor.de_listed_date between '2019-02-01' and '2019-02-13'
group by de_status

select debtor.de_status ,count(de_number) as 'Number of A/Cs'
from debtor
join client on client.cl_rowid =  debtor.de_rowid_client
join debtor_status on debtor.de_status = debtor_status.ds_code
where de_active = 'C' and debtor.de_listed_date between '2019-01-01' and '2019-01-31'
group by de_status

Both of which work, but I need to know, is there a way to create a stored procedure that would run both of these in a loop back for X amount of months.

Any help you could give me to head me in the right direction would be greatly appreciated.
0
I am using MS Sql Server Management Studio 14.0.17224.0
I am connected to an AMAZON RDS SQL Server Instance 2014

When I open the Activity Monitor

Is there any way I can see a specific Stored Procedure that is running?
0
Hi,

I need to pull out the most recent record in the contact table only matching the details of the query - and then count them.

here is my current query to check the data being returned:
		SELECT debtor.de_number ,debtor.de_name ,client.cl_number ,debtor.de_owing ,contact.co_done_date ,contact.co_description
		FROM
			contact
		LEFT JOIN
			debtor ON de_rowid = co_rowid_debtor
		LEFT JOIN
			client ON cl_rowid = de_rowid_client
		WHERE
			contact.co_description = 'LOC'
			AND contact.co_done_date between '2019-02-01' and '2019-02-11'
			and contact.co_who = 'MNSW'

Open in new window


and this is my query I am trying to use to return the count:

		SELECT count(max(contact.co_done_date))
		/* debtor.de_number ,debtor.de_name ,client.cl_number ,debtor.de_owing ,contact.co_done_date ,contact.co_description */
		FROM
			contact
		LEFT JOIN
			debtor ON de_rowid = co_rowid_debtor
		LEFT JOIN
			client ON cl_rowid = de_rowid_client
		WHERE
			contact.co_description = 'LOC'
			AND contact.co_done_date between '2019-02-01' and '2019-02-11'
			and contact.co_who = 'MNSW'
		group by contact.co_done_date

Open in new window


and here are the tables:
Debtor Table:

de_number    de_name    debtor owing    de_rowid    de_rowid_client
1            Test Name     320.00        1    1
2            Test 2        132.00        2    2
3            Test 4        165.00        3    1
44           Test 6        178.23        4    1

Client Table

cl_rowid    cl_number
1           100
2           200
3           201

Contact Table:

co_rowid_debtor    co_description    co_done_date    co_who
1                  LOC               2019-02-01      MNSW
1                  LOC               2019-02-01      MVIC
1                  LOC               2019-02-03      MNSW
2                  LOC               2019-02-04      MNSW
3                  LOC               2019-02-04      MNSW
4                  LOC               2019-02-05      MNSW
2                  LOC               2019-02-02      MNSW
3                  LOC               2019-02-02      MNSW
1                  LOC               2019-02-013      MNSW

is it a matter of doing a grouping and a max on the co_done_date? Could someone possibly point me in the right direction?

many thanks!
0
Hi,

I'm trying to change the compatibility of the following statement to migrate my server to SQL Server 2008 R2 but I have problems with the number of rows that gives the modified sentence (ANSI 89 - ANSI 92) (Non ANSI outer join operations ("*=" or "=*") are not supported and will not work in compatibility levels 90 and above)

Original sentence:

SELECT C.CLASIFICACION_3,                       
       A.ARTICULO_GENERICO,                       
       C.DESCRIPCION,                       
       ( SELECT COSTO_SOL_INI FROM LOG_CSTS_FOOD.DBO.LOGISTICA_SALDOS WITH(NOLOCK) WHERE ARTICULO = A.ARTICULO_GENERICO AND MES = N'10'  AND ANHO = N'2015' ),                       
       CASE WHEN J.CODIGO = '99'                       
            THEN J.CODIGO + ' - ' + J.DESCRIP1                       
            ELSE J.CODIGO + ' - ' + J.DESCRIP2                       
            END,                         
       C.UNIDAD_ALMACEN,                       
       B.CANTIDAD_INI,                       
       B.COSTO_SOL_INI,                       
       ISNULL(A.FECHA,                       
       CAST( '' as datetime ) ),                       
       ISNULL( A.AJUSTE_CONFIG, '' ),                       
       CASE WHEN D.CODIGO = '99'                       
            THEN D.CODIGO + ' - ' + D.DESCRIPCION                       
            ELSE D.CODIGO + ' - ' + K.DESCRIPCION                       
            END,                         
       ISNULL( 

Open in new window

0
Using SQL Server 2008

This is a follow up to this example:
https://www.experts-exchange.com/questions/29134468/t-sql-display-query-result-as-list-of-comma-separated-values.html?anchor=a42789277¬ificationFollowed=222867761#a42789277

In that example

I had this table:

Use [Northwind]
create table Employee (EmployeeID int, LastName varchar(50))
insert into Employee values (1,'Davolio'), (2,'Fuller'), (3,'Leverling'), (4, 'Peacock'), (5,'Buchanan'), (6, 'Suyama'),(7,'King'),(8,'John'),(9,'Peter')

Open in new window



The input was a comma separated string list of LastName

------------- Input -------------
DECLARE @LastName varchar(2500)
SET @LastName = 'Davolio,Fuller,Leverling,Peacock,Buchanan,Suyama,King,Callahan,Dodsworth'
------------- Input -------------


The solution was this query. The output of this query is a list of EmployeeIDs of those last names.

------------- Input -------------
DECLARE @LastName varchar(2500)
SET @LastName = 'Davolio,Fuller,Leverling,Peacock,Buchanan,Suyama,King,Callahan,Dodsworth'
------------- Input -------------
------------- Query -------------
;with cte1 as (
select ltrim(SUBSTRING(LastNames, n, CHARINDEX(',', LastNames + ',',n) - n)) AS LastName
  from (select @LastName as LastNames) t1
  CROSS JOIN (SELECT number FROM master..spt_values WHERE type = 'P')  AS Numbers(n)
 WHERE SUBSTRING(',' + LastNames, n, 1) = ','
  AND n < LEN(LastNames) + 1) --select * from cte1
select distinct 

Open in new window

1
I'm using sql server 2008.

I'm using the Employees table from the Northwind database.

Right now i have this query where I input a comma separated string of last nams as input and it returns those employees.


Query 1

-- input is comma delimited list
DECLARE @EmployeeName varchar(2500)
SET @EmployeeName = 'Davolio,Fuller,King,'
-------------
SELECT [EmployeeID]
      ,[LastName]
      ,[FirstName]
      ,[LastName] + ' ' + [FirstName] AS FullName
FROM [Northwind].[dbo].[Employees]
WHERE( nullif( @EmployeeName, '') is null or (',' + @EmployeeName +',' LIKE '%,' + [LastName] + ',%'))
ORDER BY [LastName]

Open in new window


When I run this query i get this:

Query 1 output

Query 2

How do i fix the Query 2 below, so my input could be part of the last name?

So if I input this I should get the same result as Query 1.

-- input is comma delimited list of partial last names
DECLARE @EmployeeName varchar(2500)
SET @EmployeeName = 'Dav,Ful,Kin,'
-------------
SELECT [EmployeeID]
      ,[LastName]
      ,[FirstName]
      ,[LastName] + ' ' + [FirstName] AS FullName
FROM [Northwind].[dbo].[Employees]
WHERE( nullif( @EmployeeName, '') is null or (',' + @EmployeeName +',' LIKE '%,' + [LastName] + ',%'))
ORDER BY [LastName]

Open in new window

0
Hello Experts,
I am trying to create table which should contain Column Name, Data Type and Length from another Table.  Please see the example below:

Table - 1(Employee): Record Count (10)
---------------------------------------------------------
1. Employee_Name Varchar(50)
2. DateOfBirth Date
3. Salary Int
4. Active char(1)

Table - 2 (Temp)
--------------------------
1. Column_Name - any text data type say Varchar(50)
2. Data_Type - any text data type say Varchar(20)
3. Length - any int data type say Int

Data (in Table -2)
---------------------------
Employee_Name, Varchar, 50
DateOfBirth, Date, 8
Salary, Int, 10,2
Active, Char, 1

Basically I am just getting the structure from Table-1 in Table-2 in the form of columns.  Please let me know if this something possible.

Thank you in advance.

Thank you.
0
Hi
SQL Server - I have a table with over 70 million records. It will be queried mainly by date (non unique) and then by Company and maybe another category. What is the most efficient way to index this table. This table has no primary key and is queried without any joins to other tables.
Regards
0
I’m using sql server 2008.

In a table I have a smallmoney datatype column.

Values in that column look like this:

Column1
1.54
0.00
8.76
6.87
0.00
9.87


If I use this if a value is null it displays it as blank
ISNULL(Column1, ‘’) as column

What’s the syntax for if a value in that column is 0.00 then display it as blank?
0
I'm using the Northwind database  in sql server 2008.

-- Janet Leverling
UPDATE [Northwind].[dbo].[Employees]
SET [Title] = 'Lawyer'
WHERE [EmployeeID] = 3

-- Robert King
UPDATE [Northwind].[dbo].[Employees]
SET [Title] = 'Doctor'
WHERE [EmployeeID] = 7

I have two update statements like this.
When I run them individually they work fine.

I want to put these in a stored procedure, so when I run the stored procedure both records get updated.

Is there a syntax to combine both these statements into one statement?
0
I have set up the server 2017 mirroring. And it does not have a witness server.
I have seen the below youtube video to know how to recover the DB during failover scenarios. Regarding this, I have gone through some articles too.

https://www.youtube.com/watch?v=HyGzgzRQCcI

If you want to use the mirror database you need to have transaction log backup from the primary server.  If you have multiple transaction log backups then you need to restore one by one in mirror server to activate.   How to start the endpoint (mirror DB)  if not able to recover transaction log backup from the primary?
0
Hello,

Need a suggestion on how to use the join in the following example.

Thanks in advance.
Example5.xlsx
0
I'm using SQL Server 2008

I was looking at the RAISERROR documentation on this page:
https://docs.microsoft.com/en-us/sql/t-sql/language-elements/raiserror-transact-sql?view=sql-server-2017

So the syntax is this:

syntax
the first part is string message, then severity, then state

Then towards the bottom of the page it has this example with severity of 10 and state of 1:

example
What does a severity of 10 mean?
What does a severity of 16 mean?
What does a state of 1 mean?
0
This is SQL 2014 and it's related to the question here
https://www.experts-exchange.com/questions/29129110/Replace-works-but-is-there-a-better-way.html

And this https://www.experts-exchange.com/questions/29129724/Result-of-these-2-SQLs.html  (I got the "exists" line of code from this solution)

I'm using the solution in that question but wondering why I'm not getting the correct number of rows.

1. Please see attached for a sample data. It creates a table with some data in it.

2. This is the code I'm using. I've noted in it which one is correct and which one is not correct. I don't know what I'm missing.

DECLARE @results TABLE(SearchText NVARCHAR(4000))
DECLARE  @searchtext nvarchar(4000)
DECLARE  @searchtext2 nvarchar(4000)

SET @searchtext = '%019%'
SET @searchtext2 = '%O19%'

INSERT INTO @results --*************  0 and O C# code has for SerialNo. tfs892
	SELECT @searchtext UNION ALL
	SELECT REPLACE(@searchtext,'0','O') UNION ALL
	SELECT REPLACE(@searchtext,'O','0')

	SELECT * FROM @results

SELECT * FROM dbo.camillatest u
WHERE 
exists (Select 1 from @results r WHERE u.SerialNo LIKE @searchtext) --**** the solution I'm using. It brings back 27277 but it should be 27340
--(u.SerialNo LIKE @searchtext OR u.SerialNo LIKE @searchtext2) -- 27340 correct rows 
--(u.SerialNo LIKE '%019%' OR u.SerialNo LIKE '%O19%') -- 27340 correct row

Open in new window

sample-data-for-ee.txt
0
I have the below Purchase receipt table with these rows

Order #    Receipt #          DateTime
1233           2345                2018-04-04 13:21:55.153
1233           2346                2018-04-04 13:22:01.410
1067           2834                2018-04-03 12:18:01.410
1067           2835                2018-04-03 12:18:52.410
1067           2839                2018-04-03 12:21:52.410

I need to find out the receipt numbers using the SQL query to list the rows generated less than the one-minute time difference for the same purchase orders.
The output should be like this
Order #   Receipt #
1233         2345
1233         2346
1067         2834
1067         2835

the above receipts generated  for the same order # less than one-minute time difference. Please let me know how to find out using the SQL query.
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.