SQL

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.

Share tech news, updates, or what's on your mind.

Sign up to Post

Hi, we are using Azure Hybrid connection to connect to our internal SQL server from an Azure web app. This works without issue, however we now have a requirement to connect to an SQL Analysis Services database from Azure.

We have created a connection using the same hybrid connector install and the status is coming back as "Connected". In our web.config file we are connecting to the analysis server using the string      <add name="MyConnection" connectionString="Provider=MSOLAP;Data Source=SQLSERVERNAME:2383;Initial Catalog=DBName;" />

When we try to reference the connection string we are getting the error message "An attempt was made to access a socket in a way forbidden by its access permissions 127.0.0.65:2383"

This same web page connects to the internal SQL server fine.

We have tried setting up logging, enabling debug logs for the connection manager and looking in the SQL xp_readerrorlog but cant see anything that stands out.

Any help in getting this to work would be appreciated.
0
Top Threats of Q1 & How to Defend Against Them
LVL 1
Top Threats of Q1 & How to Defend Against Them

WEBINAR: Join WatchGuard CTO and our Threat Research Team on Aug. 2nd to hear the findings from our Q1 Internet Security Report! Learn more about the top threats detected in the first quarter and how you can defend your business against them!

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

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] 

Open in new window

0
Hi,

I have created a basic Website in WordPress using a number of plugins to include company information, images etc.

Does anyone know of a WordPress plugin that will connect to and query an MS SQL database? We have a SQL 2008 server on site and I would love to be able to retrieve and present information from the various tables/views onto an Intranet page / potentially including a search too.

I've seen a few plugins which allow you to access the mySql database behind Wordpress but nothing that allows you to connect to a separate database.

I look forward to your replies & thanks in advance!
0
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
0
I have a project for our shipping department - I need to created in SQL query that will provide the following results;
1. group by style & color (example: style:123R12, color: BLK) and create an alpha sequence (A) which 123R12BLK = A

2. if style is the same but color is different (example: style:123R12, color: BLUE) it should be sequence (B) which 123R12BLUE = B and so on.

3. Sequence could go as far A thru ZZZ
0
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
0
I'm looking to optimize the execution plan for a specific SQL statement, so I'm looking at how to achieve this using SQL profiles.

What I'm looking to do is add an index hint to a SQL statement, but this would need to be on the basis of matching against the first half of the statement as the number of predicates following this will be subject to change.

Is it possible to set up a profile based on a pattern match like this? Or should I be looking at something else to achieve this?

Any help much appreciated.
0
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 …
0
This is a follow-on to this question - https://www.experts-exchange.com/questions/29113292/Need-To-Know-Syntax-To-Display-Attribute-On-Checkout-Page.html

I need to know how to make this show attributes dynamically to whatever is in the shopping cart on the checkout page.

Thanks,
0
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.
0
Making Bulk Changes to Active Directory
LVL 8
Making Bulk Changes to Active Directory

Watch this video to see how easy it is to make mass changes to Active Directory from an external text file without using complicated scripts.

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

Open in new window


Result -

SQL result
The Array -

The Array, Normal Array
the field chk_name needs to equal that of the array
0
Our SQL Server network connectivity is broken! I can only get on it via a terminal from VMware.

Our server is set to a 192.168.0.x address, gateway 192.168.0.xxx. Ipconfig shows a 169.254.x.x address, I'm not sure why it is getting this.

Despite several re-boots I cannot ping it's IP address and several customer facing apps are failing. DNS looks OK, other servers with same settings are fine.

Can anyone help please!
0
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

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());

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.
0
Hello Experts,
Just wondering if there is a any opensource JAVA JAR file that I can use for a project that allows me to connect to a DB (like mysql or H2), create tables, insert data into tables, etc. Basically, I am looking for a solution which builds the queries itself when data is passed as parameters to a method. Any inputs really appreciated.
0
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?
0
I just need help with SQL query that will list out one row per folder name.  I have a table that contains product ID's by category.  Each category can contain one or more products.  I just need to list out each category and include only one 1 product.  It can be any product from the category.  

For example; here is my table:
ID             Folder Name
123          Cars
124          Cars
433          Cars
922          Video
913          Video

Expected results:
ID             Folder Name
123          Cars
913           Video
0
Hello All:

I have the following table structure (a result set inserted into a temp table):
Full 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:
Generic columns
2)Create a new result set where the columns are separated based on F, P or D as such:
Separated Columns
I'd love to have a solution for both, followed by 2 and then 1.

Many thanks!
0
I have a table named dbo.logSearch in an MS SQL database.  The table keeps track of all searches performed on my data.  The problem is that the spiders find the database and make numerous searches which results in the table getting extremely large.

I've written a query (with help from somone who knows what he as doing!) that groups all of the table rows grouping those with the same IP (remote_host).  This query lists each IP along with how many searches were made:

SELECT remote_host, count(*) AS count
FROM dbo.logsearch
GROUP BY remote_host
order by count desc

I would like to modify this query to delete all records made any IP in which more than 1000 searches were made, thereby deleting the search made by spiders.
Can you help?
0
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
0
Managing Security Policy in a Changing Environment
Managing Security Policy in a Changing Environment

The enterprise network environment is evolving rapidly as companies extend their physical data centers to embrace cloud computing and software-defined networking. This new reality means that the challenge of managing the security policy is much more dynamic and complex.

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,
0
what are the cautions we need take in SQL Server 2012 std( windows cluster 2012) and  Node1, Node2 are in cluster

as sql server 2012 sp4 patch upgrade we unable to install because of some installer files are missing, we tired to fix, but it is not working and  we are decided to go for SQL server Repair option on Node1

Pls let me know, what steps do i need to do..with a caution..on Node1

Pls note, once if it successful patched on sp4 on node2, after repair, then we will plan on node2.

Pls suggest. Thanks
0
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 ?
0
I have an Oracle 11g table TB_Doc with a column DocNo ( and a few more columns)
    Doc1Vol01/1
    Doc2Vol119/2
    Vol_Fred11/2      
    1-11-DocNormal/1
    SomeDoc    
    DocFileNormal.pdf
   
 I need to have a view in which I need to replace the word Vol and the volume number to nothing  (Only if it is at the end)  

So the above DocNos should come as
   Doc1/1
    Doc2/2
    Vol_Fred11/2      
    1-11-DocNormal/1
    SomeDoc    
    DocFileNormal.pdf
0
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?
0
Hi all,

I have a new server with windows 2016, and I need to copy across an small number of ASP websites.

So, starting with a single website, I installed all the necessary files and ran the site. When loading a page that connects to an SQL server database, an error occurs and I get a link to a website assuming that ASP is not activated.

To verify that it is, I have loaded a test .asp page and it does load correctly.

I think the issue is related to MDAC, I have tried to install version 2.8 but I can't tell if it actually did install or not. I can't confirm this is the issue but do believe it is database related.

Has anyone else encountered a similar issue, and resolved it?

Any help is appreciated.

resourcesys.
0

SQL

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.