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

x

Query Syntax

49K

Solutions

19K

Contributors

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

We upgraded from Windows Server 2000 to 2012 and SQL Server 2000 to 2012.   Web pages that use CDONTS on Windows Server 2012 are working great!

But ... stored procedures in SQL 2012 that use CDONTS are not working.

How do you get CDONTS to work with SQL Server 2012  stored procedures so emails can be sent from stored procedures?
0
Keep up with what's happening at Experts Exchange!
LVL 11
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Hi

Just a general question

What are advantages/disadvantages of using Functions and Stored Procedures in a SQL query - just been asked?
0
Hi

New to Powerbi but looking at using it over Nav 2013 / 2017.  SQL analysis services work great but to drag in Navision tables I do get data and SQL as the source.   The available table selection does not pick up all the tables.  Within Nav there are lots of tables as each is prefixed with a company.  Seems there is a 10000 limit and no way around this.

It's a Micosoft product running over another Microsoft product you would just sort of think this works......  Anybody found a way around this before I look at "funky" workarounds which I have in my brain ..... thanks
0
hello guys,
i need your advice in creating scenario in azure that i need to create an web application using sql database and the number of users that will connect will be more than 3000 users and need it available 24/7 so how can i achieve it with a little bit cost :) ?
note: it'll be on windows.
thanks
0
Hi,
I am looking to paginate the results of the query, so I need to use
ROW_NUMBER() OVER (ORDER BY ...) AS Row_Counter
and then put:
WHERE   RowNum >= 1
    AND RowNum < 20

but I am not sure how to add it into this query that already has two joins.
Here is my current query:

$data_query = mysqli_query($conn, "
SELECT ZIPCodes.zipcode, usersearch_answers.username,
       (3959 * acos(cos(radians(ZIPCodes.latitude)) *
       cos(radians(center.latitude)) *
       cos(radians(ZIPCodes.longitude ) -
       radians(center.longitude)) +
       sin(radians(ZIPCodes.latitude)) *
       sin(radians(center.latitude)))) AS distance
FROM
(
    (
        SELECT usersearch_answers.username,
            usersearch_answers.zipcode,
            ZIPCodes.latitude,
            ZIPCodes.longitude
        FROM
        (
            usersearch_answers JOIN ZIPCodes ON usersearch_answers.zipcode = ZIPCodes.zipcode
        )    
        WHERE (ZIPCodes.zipcode='33133')
    ) center, ZIPCodes
) INNER JOIN usersearch_answers ON ZIPCodes.zipcode = usersearch_answers.zipcode
WHERE (usersearch_answers.username <> '$userLoggedIn')
HAVING (distance < 5000)
ORDER BY distance");

How do you combine the two?
Any tips are so greatly appreciated!  
-Aimee
0
Hello,
 We're considering a change from SQL Cluster to a SQL Always On configuration.  Please help me with the benefits this may also bring in relation to OS and SQL Updates in the future.  As it stands now, we'd need to rebuild our cluster to update the servers OS and I'm interested to know whether Always On will provide additional benefits that allow making updates to the OS or SQL version without taking the entire system down.

Thanks,
  Troy Taylor
0
I am setting up a new server to host a Windows 2012r2 Hyper-V VM.  The virtual machine will primarily be a 2012 SQL server.  What RAID level on the new host server would be recommended?
0
I am investing in another server to take some of the load off another older server that is running 2 Hyper-V machines.  One of the Virtual machines is a SQL server and the other is my exchange server.  Which of these 2 will benefit more from a machine with more available RAM?
0
I am using MS SQL and have the following example setup.

Database - DB1
Table - Status
Field - Active

Database - DB2
Table - Events
Field - DateTime

Both tables have a common ID field - UID

What I am trying to achieve :

- Review all entries in Events in DB2
- Identify any entries that have a DateTime which is more than 12 hours older than the current time
- For any entries above that are older than 12 hours set the value of the Active field in the Status table in DB2 to "False"

I have found lots of HowTo info online for replicating a value from one table to another using JOIN but this is not replicating data. This is setting a value in one table in one DB based on values for an entirely different variable in another table in another DB.

I'm a t-sql novice and have been trying to work this out but with no success so far.

Any advice would be greatly appreciated.

TIA.
0
I have a list of employee IDs that I want to update in a table.
The table currently has 5 fields:  a unique ID, Startdate, EndDate, EmpID, Assigned

All EmpIDs are currently a default example ID.  All StartDates begin on Wednesday and end the following Tuesday.

Ordinarily when I go through this process, it's a laborious copy/paste update table set id= where startdate = ...
Unfortunately there are 8 employees I need to enter into the table, but they do not have a repetitive assignment.
But I was hoping there was a way to just supply the list of IDs, give it the start date and have it process through the updates automagically (assigning the 2nd in the sequence based on start date + 7).
0
Free Tool: Subnet Calculator
LVL 11
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

I get this error when trying to join 2 fields.  Can anybody tell me what this is and how to make the join work?

Cannot resolve the collation conflict between "SQL_Latin1_General_Pref_CP1_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
0
Yesterday I added several tables and views. What query to run to get the tables and  views recently created.
It should the ones created on 12/11/2017. Is it possible?
0
I am struggling to complete a formulation for calculating the expiration of data entered which depending on which database table they are in depends on their expectoration time, either 24hrs, 7days, 14days or 21 days.
If there isn't any withing that particular table then it will default to 14.
This is my so far attempt, but have gaps i know and it is so frustrating that I cannot get my head around it....If anyone can shed some light it would be gratefully appreciated.
@example = INT(Table.example/2) MOD 2 <> 0

Open in new window

If {@example} then 1
Else
(

If IsNull{table.NAME} then
14
Else if {table.NAME}= help then
7
Else if {table.NAME} = more help then
21
Else
14
) 

Open in new window

0
Hello experts!

I need some help with SQL in extracting data from one table into another. In the table transactions, I have the Seller, SellerID, Listing, ListingID, SoldPrice, Date. I would like to extract each instance of Seller and Listing into individual records in another table called individualtransactions.

I figured the best way to showcase what I would like to accomplish is with an example before and after. Is this possible with one SQL statement or would I need to run multiple statements? If it is, can an expert guide me on how to accomplish this?

Table name: transactions
Seller, SellerID, Listing, ListingID, SoldPrice, Date
John, 1, Bill, 2, 1900, 12/01/2017
Jane, 3, Becky, 4, 2400, 12/02/2017
Joe, 5, Jane, 1, 2100, 12/03/2017

Table name: individualtransactions
Name, Price, Role, Date
John, 1900, Seller, 12/01/2017
Bill, 1900, Listing, 12/01/2017
Jane, 2400, Seller, 12/02/2017
Becky, 2400, Listing, 12/02/2017
Joe, 2100, Seller, 12/03/2017
Jane, 2100, Listing, 12/03/2017
0
$sql="
      SELECT
s.survey_id AS 'Inspection ID',
s.license_number As 'License Number',
s.name_pos AS 'Name of POS',
s.address_pos AS 'Address of POS',
s.updated_at As 'Inspection Date',
s.updated_at As 'Inspection Date',
t.name As 'Town Name',
c.name As 'Districts Name',
s.gps_location As 'Location',
sum(a.option_value) As 'Final Scoring',
a.option_value As 'Remarks'
FROM  ".DB_PREFIX . "survey s
INNER JOIN ".DB_PREFIX."town t
on t.town_id=s.town_id
INNER JOIN ".DB_PREFIX."city c
on c.city_id=s.city_id
INNER JOIN ".DB_PREFIX."answer a
on a.survey_id=s.survey_id WHERE a.question_id=371";

i want sum of option_value WHERE option_id!=0  in join query and  a.option_value As 'Remarks' WHERE a.question_id=371";

from same table
0
I'm using Dreamweaver cc 2018.  I cannot connect to SQL Server
0
Hello Experts,

This should be a piece of cake for you and here is what I am looking for.

1. I would like to get a sample code for a trigger in SQL.
2. Criteria : When Column A value gets updated/changes to value 'A' and Column B value gets updated/changes value 'B'
then Column C value should be updated to value 'C' (These columns are in the same or one table)


Thanks in advance.
0
First let me say that I am very new to SSIS and XML.........I have created a SSIS package that takes XML data in (it contains main data, and some attachment data), splits this data and puts it into Excel files, picture is attached.  During this processing it places the main data in one table, and the attachment data in another table. The 'separation' table contains a Guidid, SSN, claimeffdate, claimnum, lastname, firstname, middleinitial, suffix and some other information.  The attachments table comes in with a description, type of document, unique attachment id, actionable attachment, attachmentsize and the attachmentdata.

As I am transforming this data, I need to put the GUIDID on the SQL table with the attachment data.  I have two people with attachments, and the other 4 do not have attachments.  I'm using a query that runs before and after the 'Shred and Stage in my foreach loop container that is like:
declare @ID as decimal(38,0)

set @ID = (Select Top 1 StateGUID from SIDES.dbo.StateSeparationInformation order by StateGuid desc)

update sides.dbo.[INC-Attachment]
set StateGUID = @ID
where StateGUID is null


The query executes fine, and the first person gets the correct GUID on all their attachments.  The second person that should get a GUID for an attachment does not instead the GUID for another person gets  put on that attachment.

The query works great if everyone has attachments, just not when some have attachments and some do not.

I added it …
0
hi,

I am right now try to create a SQL server 2016 listener by reading this:

https://www.sqlshack.com/alwayson-availability-groups-how-to-setup-ag-between-a-clustered-and-standalone-instance-part-3/

question is :

1)  in the article above:

" To create a listener, we will also need an IP and Computer in the AD, and don’t forget that the cluster computer should have full access to the SQL Server computer account."

I am now have and IP address and a computer name (name only), is that mean that Computer name must exists in the AD for it to works? currently that computer name is not register in the AD.

now I am created a SQL 2016 AOG in a domain less configuration and I am not sure if listener will work in this case.

2) to create the listener, should/must the SQL server need to join the AD too ?

3)
when creating the listener on the wizard shown in the link above:

"A new window will be opened. Fill the “Listener DNS Name” with the name of your AD Computer Name. In our case SQLSRVAG01. In the “Port” field, choose an adequate port, something that is not being used.

what is listener DNS name ?  the listener name I have ? e.g. SQLSERVERListener ? do I need domain surfix for it?  

I can't create one using that name only and the error is :

listener error.
0
Independent Software Vendors: We Want Your Opinion
Independent Software Vendors: 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!

I have code that takes a nvarchar(max) value to run a SP.
If I run the SP directly on SSMS it works fine, the SP does the job. But using ASP Classic code to pass the parameter which is a very long XML string to the SP is giving errors saying the data is getting truncated. Here is the code we currently have:

set sp_usp_xmlparse = Server.CreateObject("ADODB.Command")
    sp_usp_xmlparse.ActiveConnection = MM_bluedot_STRING
    sp_usp_xmlparse.CommandText = "dbo.usp_XMLParse"
    sp_usp_xmlparse.CommandType = 4
    sp_usp_xmlparse.CommandTimeout = 0
    sp_usp_xmlparse.Prepared = true
    sp_usp_xmlparse.Parameters.Append sp_usp_xmlparse.CreateParameter("@RETURN_VALUE", 3, 4)
    sp_usp_xmlparse.Parameters.Append sp_usp_xmlparse.CreateParameter("@blob", 200, 1, -1, qnrBlobXML)
    sp_usp_xmlparse.Parameters.Append sp_usp_xmlparse.CreateParameter("@UserID", 3, 1, -1, UpdateStatus__MMColParam)
    sp_usp_xmlparse.Parameters.Append sp_usp_xmlparse.CreateParameter("@FirmID", 3, 1, -1, UpdateStatus__MMColParam2)
    sp_usp_xmlparse.Execute()

Open in new window


Any ideas what could be wrong?
0
Hi there,

I have this KB article from Atlassian, and they show a specific PostgreSQL query, but I need to run it against Microsoft SQL server. Anybody able to assist?

The article is: https://confluence.atlassian.com/confkb/some-attachments-or-links-are-no-longer-accessible-after-server-migration-214862724.html

The query itself that is shown is:

insert into contentmigration
select bodycontentid, substring(body from '\\|http://oldServerName/download/attachments/.*/') from bodycontent;


Thanks for any suggestions!
0
Hi Experts,

I'm suddenly getting an error "Timeout Expired" when opening a view.
I know earlier in the day it was working fine.
What should I do?

See attached.

Following is the view's SQL.
SELECT     TOP (99.9999) PERCENT dbo.View_OrientSchedOpenFillUnionQry.ID, dbo.View_OrientSchedOpenFillUnionQry.EmployeeID, 
                      dbo.View_OrientSchedOpenFillUnionQry.FacilityID, dbo.View_OrientSchedOpenFillUnionQry.FacilityDate, dbo.View_OrientSchedOpenFillUnionQry.Result, 
                      dbo.View_OrientSchedOpenFillUnionQry.FinalConfirmationYN, dbo.View_OrientSchedOpenFillUnionQry.FinalConfirmation, 
                      dbo.View_OrientSchedOpenFillUnionQry.FinalConfirmationDate, dbo.View_OrientSchedOpenFillUnionQry.FinalConfirmationInitial, 
                      dbo.View_OrientSchedOpenFillUnionQry.FinalConfirmationTime, dbo.View_OrientSchedOpenFillUnionQry.FileApproved, 
                      dbo.View_OrientSchedOpenFillUnionQry.ApprovalStatusDate, dbo.View_OrientSchedOpenFillUnionQry.CompleteFile, 
                      dbo.View_OrientSchedOpenFillUnionQry.CompleteFileDate, dbo.View_OrientSchedOpenFillUnionQry.DocMissing, 
                      dbo.View_OrientSchedOpenFillUnionQry.NotesPayment, dbo.View_OrientSchedOpenFillUnionQry.DateEntered, dbo.View_OrientSchedOpenFillUnionQry.Initial, 
                      ISNULL(dbo.Employeestbl.LastName, '') + ' ' + ISNULL(dbo.Employeestbl.FirstName, '') AS EmployeeName, dbo.Employeestbl.Title, 
       

Open in new window

0
I am using Dreamweaver cc 2018 with DMXzone extensions ServerConnect and Database Connection.  I have already downloaded the php driver and added the extensions, yet I still cannot connect to SQL server.  Can anybody help?  Thank you.
0
Hello,
Trying to create a selection in a specific order.
The table is like this

CompanyName , CompanyStatus, CompanyNumber, GroupNumber
Blackwell , group, A123G, A123
Symphony, subCompany, s678, A123
Blends, subCompany, t123, A123
Tiger, group, T123G, T123
Pepper, group, Z987G, Z987
Great Falls, subCompany, f545, T123
~~~~~~~~~~~~~

What I would like the result to be based on Alpha for the group names, then Alpha for their sub companies

CompanyName , CompanyStatus, CompanyNumber, GroupNumber
Blackwell group, A123G, A123
Blends, subCompany, t123, A123
Symphony subCompany, s678, A123
Pepper group, Z987G, Z987
Great Falls, subCompany, f545, T123
Tiger group, T123G, T123

Any ideas?
Thx
JS
0
I have a table (SSG_ADRPT) that contains it has a date column (ACQ_DATE) I only want to display the rows where the dates are the same
0

Query Syntax

49K

Solutions

19K

Contributors

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.