Hello.

I want to create a trigger on a table on INSERT. So when an INSERT attempts to happen on the table, a SELECT is first run and if the SELECT returns rows, the INSERT is cancelled but the transaction ls logged to another table.

The code that has the SELECT in, is as follows

BEGIN TRAN
IF (EXISTS (SELECT *
FROM DBO.TABLE1 AS A, DBO.TABLE2 AS B 
WHERE A.ACCOUNTID = B.ACCOUNTID
AND A.COUPONID = B.COUPONID
AND PREVBALANCE = 4 AND BALANCE = -2
AND ISSUEDLOCALDATE> '3-JUL-2018'))
BEGIN 
	SELECT @@TRANCOUNT,'UNABLE TO UPDATE TABLE 1 WITH THESE DETAILS - CHECK TABLE_ISSUE' 
	ROLLBACK
END

Select all Open in new window

The above works as expected.

I'm now trying to develop a trigger on the table in question:

USE [DB]
GO
/****** Object:  Trigger [dbo].[TRIG_WIDGET_ADDITIONAL_COUPON_ISSUE]    Script Date: 08/16/2018 09:41:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[TRIG_WIDGET_ADDITIONAL_COUPON_ISSUE]   
ON [dbo].[WIDGET_COUPON]   
AFTER INSERT 
AS 

	DECLARE @inserted TABLE ([couponID] [bigint] NOT NULL,[organizationID] [bigint] NOT NULL, [orgLevelID] [bigint] NULL, [couponTypeID] [bigint] NULL, [accountID] [bigint] NULL,	[promotionID] [bigint] NULL,[customerID] [bigint] NULL,[hashCode] [int] NULL,[couponCode] [nvarchar](32) NULL DEFAULT ((0)),[serialNumber] [nvarchar](50) NOT NULL,[createdLocalDateTime] [datetime] NOT NULL,[createdHostDateTime] [datetime] NOT NULL,[createdLocationID] [int] NULL,[issuedLocalDateTime] [datetime] 

Select all Open in new window

…

Need help with a SQL Server select to join tow tables.  There are duplicate rows between the two tables so I want include the duplicate row from Table A.  A duplicate row is a the same CatNum and Attribute ID.  So join two tables and resolve duplicates by only including the duplicate row from table A.

TableA
CatNum        Attribute ID     Description
999                444                   Test
888                211                    Test 2

Table B
CatNum      Attribute ID
999               444
500               422

Expected Results
CatNum        Attribute ID     Description
999                444                   Test              
500                422
888                211                    Test 2

Hello there,

I've working in the following script for the whole day, but I'm getting the following error:

ORA-01843: not a valid month
01843. 00000 -  "not a valid month"
*Cause:    
*Action:

Basically, I have to extract a date from a char type field called 'text' if it meets some conditions: if name field starts with 'P' otherwise, it should take another field which is date type.

SELECT
supplier_id, name,SUM(net_amount) Net_Amount
FROM supplier_table
WHERE
(CASE
     WHEN name like 'A%' and SUBSTR( text, 4 , 1 ) = '/'
                  THEN to_date(SUBSTR(text,5,8),'MM-DD-YY')                -- here, this field char type format 12-30-18
      WHEN name like 'B%' SUBSTR( text, 4 , 1 ) = '-')
                   THEN to_date(SUBSTR(text,4,8),'MM-DD-YY')               -- here, this field char type format 12-30-18
      WHEN name like '%C%'
                   THEN invoice_date                                                            -- here, this field date type format 30-DEC-18
      end) between TO_DATE('&START_DATE', 'mm/dd/yy') and TO_DATE('&END_DATE','mm/dd/yy')
group by supplier_id, name
order by name

I'm so confused about these type of date formats.
My database save date type with this format 01-JAN-18

Thanks for help.

JS

Greetings,

I am needing to propose a server configuration to a client for the following environment and applications:

- will be running 2 SQL Server applications under separate instances of SQL - one running on SQL Server 2012 and one on SQL Server 2014.
- the SQL Server 2012 application will be servicing c. 15 users, and the SQL Server 2014 app will be servicing c. 80 users.
- am recommending a Dell rack-mount server R440 with chassis for up to 10 X 2.5" drives, 64 GB RAM running Windows 2016 Server Standard
- CPU's are 2 X Intel Xeon Silver 4110 2.1 GHz 8C/16T 11M cache
- dual Broadcom DP 1 GB NIC's for fault tolerance
- controller is Perc H730P with 2 GB NVCache

For the drive configurations, my base / minimum configuration for budget purposes will be:

- for O/S paging file - 2 X SAS 300 GB 15K drives in RAID-1 array
- for 1st SQL app / SQL Server 2012 - 2 X SAS 300 GB 15K drives in RAID-1 array
- for 2nd SQL app / SQL Server 2014 - 2 X SAS 300 GB 15K drives in RAID-1 array

Giving each SQL app its own RAID array will ensure there is no (minimal?) drive / disk access contention between the 2 instances of SQL Server.  

Yes, pretty vanilla in most respects.  I've had amazing success with the plain-Jane 300 GB SAS 15K drives for the last decade+, even though there is definitely newer technology out there, so I tend to stay with what has worked very well.  Haven't had the same level of success with NL SAS and certainly not SATA, so I stay with the basic …

I need help with a SQL select that will remove a static value from a column value.  

ID           Name
123        http://attribute?id=eco_123
233        http://attribute?id=eco_444

Expected results
ID           Name
123        eco_123
233        eco_444

I just want to remove "http://attribute?id=" value from NAME column.

How do i enter array value into database.


My Sql

$stmt = new Database();
					//Inserting check state.
					$query = "INSERT INTO a_test (charityID, contactID, chk_name) VALUES (:clientId, :contactId, :chkName) ";
					$stmt->query( $query );
					$stmt->bind( ':clientId', $clientId );
					$stmt->bind( ':contactId', $contactId );
					$stmt->bind( ':chkName', $_POST['chkStateCollection'] );
					$stmt->execute();
					

					$result = "Record added";

Select all Open in new window

Result -


The Array -


the field chk_name needs to equal that of the array

The idea is from a Form a combo box is selected and the button is pressed once the selection is complete. when the button is pressed an Append Query is ran and appends to a table using the value that was just selected in the combo box along with a date and time stamp.

 Here is the Coder for the Button

Private Sub SubmitEvent_Click()

DoCmd.OpenQuery "Event"
DoCmd.SetWarnings False

End Sub

Select all Open in new window

The SQL for the Append Query is

INSERT INTO TroubleTicket ( TroubleCallBody, DateReported, TimeReported )
VALUES ('Set hero condition ' & [Hero].HeroCon, date(), now());

Select all Open in new window

where Hero is the table and HeroCon is the field the combo box is stored it. I thought of pulling the value direct from the combo box but wasn't able to get it to work.

when I run the query it prompts me for user input with a message box. I'm not sure what I am doing wrong.

Hello

I'm having some trouble following this Microsoft document:

https://docs.microsoft.com/en-us/system-center/scom/upgrade-sqlserver-2017-opsmgr-1807?view=sc-om-1807

I have successfully upgraded my SCOM instance to 1807. I have also successfully upgraded the SQL database instance from 2016 to 2017, as that is now supported. However, I am unable to upgrade reporting services successfully. According to the release notes of SCOM 1807, upgrading from SQL 2016 to SQL 2017 is supported, but not for fresh installations. I find it curious then, that this upgrade guide instructs a removal of Operations Manager Reporting Services, and after upgrading SSRS, to install Operations Manager Reporting Services again. So, either I’m not reading this document correctly, or nobody has tried to follow it yet, including its creators.

How does one reinstall Operations Manager Reporting Services when using SQL 2017 Reporting Services? There is no 1807 fresh installation media as far as I am aware, so naturally, the 1801 installation media gives me this error when trying to reinstall:

The installed version of SQL Server could not be verified or is not supported. Verify that the computer and the installed version of SQL Server meet the minimum requirements for installation, and that the firewall settings are correct. See the Supported Configurations document for further information.

Would anyone have a workaround for this at all, or should I just go back to SQL 2016 SSRS?

Hello All:

I have the following table structure (a result set inserted into a temp table):

What I am trying to do is one of the two following (if I can have both options laid out that would be fantastic) Both involve removing columns based on the values (in the columns with Dxx, Pxx, and Fxx):

1)Create a new result set that has generic columns across and the results look lie the following:

2)Create a new result set where the columns are separated based on F, P or D as such:

I'd love to have a solution for both, followed by 2 and then 1.

Many thanks!

There is a question on https://www.experts-exchange.com/questions/28687829/ADO-NET-Entity-Framework-LINQ-on-Visual-2013.html about whether to use Entity Framework or ADO.net for large databases.  The accepted answer is:

Again depends on the use case and what you intend to do with retrieved data. If you intend to display it in a control such as grid then it makes sense to use SQL to pull that data. Entity framework, as the name suggests, is more useful when you are dealing with entities in your code.

What is meant by an entity?
I'm used to connecting to a database with ADO.net and doing CRUD operations using stored procedures.  When is this approach better than Entity Framework and when is Entity Framework better?
I'm using C# for Visual Studio 2017

I need to update my mysql table for tax codes in woo commerce 3.4.  What query would I used to put in tax code PH400930 for all products in the essentials_oil category?

Thanks,

I need to install SQL Server Standard 2014 , the main usage will be to activate the SSRS ( Reporting Services) and have it send automated scheduled emails to client. The main point I need t clarify is : can i install this edition of SQL server and have the SSRS running, on a normal windows OS ( Widnows 7, or Windows 10) .... or must it be a Windows Server OS ?

Hi

In Excel I managed to pull data through from my Azure SQL database.
Is it possible to use variables in such a query?
For instance I would like to have a from date and a to date that the user can change before
the query is run. Is it possible to have such values in Excel cells?