Query Syntax

51K

Solutions

20K

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

Hello Experts,

I have created 1 Instances with 9 Databases using SQL Server 2008 R2 Express.

Problem:

I keep losing connection to one or more databases. (My front end APP fails because it loses connection to said databases and SSMS loses connection as well).

As a note - I have put up to four instances (One database per instance and never had a problem like this...

I understand there is a memory limitation of 1 Gb in SQL server Express.

Does anyone know what is happening here and how I can get around it?


Thanks!
0
Cloud Class® Course: Certified Penetration Testing
LVL 12
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Hi experts,
I have the following table:

Date      Time      mVal      hVal
10/08/2014      00:05      1000      1500
11/08/2014      02:00      2000      2300
12/08/2014      06:00      2500      3000
13/08/2014      14:00      1500      3300
14/08/2014      20:00      1800      2800
01/02/2015      00:05      1000      1500
02/02/2015      02:00      2000      2300
03/02/2015      06:00      2500      3000
04/02/2015      14:00      1500      3300
05/02/2015      20:00      1800      2800
05/08/2016      10:00        800       1200
06/08/2016      12:00      1300      2200
07/08/2016      13:30      1800      3100
08/08/2016      15:00      1900      3500
09/08/2016      18:00      2200      2900

I am trying to write a query to get following data:

For each year, report the minimum value and the maximum value for the that year and show the date and time associated with the respective min and maximum values.

I am currently working in SQL Server 2014 but I would also like to be able to do same in Oracle 11g/12g

Hope this is description is simple enough.

Thanks
0
I have a SQL task called TransferInfo inside of a Container with other SQL tasks.

Every now and then, it appears that only the TransferInfo task runs twice. The other SQL tasks in the same container only runs once.

Please advise!!!
0
My apology our experts , I closed the the question about a code to re link  access to sql server back end on different network wrongly without proper testing. The code below could not work see the errors below:

(1) I have put the code in a module and tried to call it on a login form as follow ( Call AttachSqlServer), this was called on the on-click event, it gave me an error as argument not optional
(2) The following part of the called is also not compiling:

Err_AttachSqlServer:
    Call ErrorMox
    Resume Exit_AttachSqlServer
   End Function


I the issue is on the call function

Below is how I have replaced the parameters:


Public Function AttachSqlServer( _
    ByVal Hostname As String, _
    ByVal Database As String, _
    ByVal Username As String, _
    ByVal PassWord As String) _
    As Boolean

' Attach all tables linked via ODBC to SQL Server or Azure SQL.
' 2016-04-24. Cactus Data ApS, CPH.

    Const cstrDbType    As String = "ODBC"
    Const cstrAcPrefix  As String = "dbo_"

    Dim dbs             As DAO.Database
    Dim tdf             As DAO.TableDef
    Dim qdf             As DAO.QueryDef
   
    Dim strConnect      As String
    Dim strName         As String
   
    On Error GoTo Err_AttachSqlServer
   
    Set dbs = CurrentDb
    strConnect = ConnectionString(Hostname, Database, Username, PassWord)
   
    For Each tdf In dbs.TableDefs
        strName = tdf.Name
        If Asc(strName) <> Asc("~") Then
     …
0
We are a water treatment company and we had a programmer build an application that would handle truck waste deliveries.  Now the application has been built out to include Kiosk.  I'm not a programmer but I believe the program is either asp or c++ with a sql background. We have had the program without the Kiosk for years with of issues.  Even when we added a couple Kiosk it was ok.  It seemed when we added a third kiosk it was around the time we started to have this problem.  We do have a test system that runs on a completely different server.  the login is AD based and the problem is when the program sits after a while there a popup asking to relogin.  This effects the Kioks, if I just click cancel I can keep working in the app however the kiosk loose connection to the application.  The kicker is if I start the test system I don't get any pop ups.  I apologize if there's any confusion but this is a weird problem and I just trying to get all the problems in.  I can send some of the code for if anyone wants to see it.

Thanks in Advance.
0
Hi there!

I would like to create a string that

1. Begins with two-digit present year, in our case, 18.

This works:   substring(CAST(DATEPART(yyyy, GetDate()) as CHAR(4)),3,2)

2. Continues with two-digit present month, in our case, 08.

This does not work:  CAST(DATEPART(mm, GetDate()) as CHAR(2))     (It gives 8, when it should give 08)

3. Continues with a (say) ten-digit random string.

This seems to work:  CAST(10000000000*RAND() as bigint)   , say 1231231234  for example

4. CONCATENATE everything, in that order, in our case:   18081231231234

5. Store this in a field defined as nvarchar(50)

Could you please help me create the string ?

Thanks,
fskilnik.
0
I'm having issue with this SQL statement.
$sql_statement = "SELECT once.id, message, audio, audio_desc, accepted, postdate, tag, broadcast, once.mobile, once.username1, state, users.username, users.firstname, users.lastname, users.fullname, users.avatar, users.alias, useroptions.aliascheck, content_provider.content_type, comment.comment_date as c1 FROM once LEFT OUTER JOIN users ON once.username1 = users.username INNER JOIN useroptions ON once.username1 = useroptions.username LEFT OUTER JOIN content_provider ON once.username1 = content_provider.provider LEFT JOIN comment ON once.id = comment.post_id AND once.tag = comment.post_tag WHERE once.username1='logusername' OR once.username1='myusername' GROUP BY once.id
UNION ALL
SELECT daily.id, message, audio, audio_desc, accepted, postdate, tag, broadcast, daily.mobile, daily.username1, state, users.username, users.firstname, users.lastname, users.fullname, users.avatar, users.alias, useroptions.aliascheck, content_provider.content_type, comment.comment_date as c1 FROM daily LEFT OUTER JOIN users ON daily.username1 = users.username INNER JOIN useroptions ON daily.username1 = useroptions.username LEFT OUTER JOIN content_provider ON daily.username1 = content_provider.provider LEFT JOIN comment ON daily.id = comment.post_id AND daily.tag = comment.post_tag WHERE daily.username1='logusername' OR daily.username1='myusername' GROUP BY daily.id
UNION ALL
SELECT weekly.id, message, audio, audio_desc, accepted, postdate, tag, 

Open in new window

0
I am having an issue with my SQL 2012 server running an integrity check. When i try to run the job as an SA I get a message "Non-SysAdmins have been denied permission to run DTS Execution job steps without a proxy account.  The step failed." I know that SA is a SysAdmin so I don't know what the issue is.

This all started on Wednesday where it looks like an windows update come down on the SQL server and installed while the backup job was running. I restarted the server but it was during work hours so I couldn't re-run the job so I figured it would run last night and finish the job. Unfortunately, there wasn't a job scheduled to run till tonight so I decided to run the integrity check to make sure it won't fail tonight and I am getting the error above.

What can I do. How can I fix this?
0
Hello Experts,

I have a query that I want to replace leading zeros after the first 3 Letters of a value in SQL query. Please see attached.

Is this possible?

This is the query I have I come up with below:

REPLACE(LEFT([ClaimNo],5),'0',' ')+RIGHT([ClaimNo],9)

Open in new window


Thank you again for all your help.
ReplaceLeadingZero_To_Space.xlsx
0
I have a SQL statement that works when I enter it manually in SQL Management Studio, but fails when I run it in a C# program. The statement is:
select CaseHistory.CaseNo, CaseInvoice.InvoiceNo, CaseHistory.Code, Paid = cast(Paid as decimal(10,2)), VoucherDate, 
PaymentNo, CaseDebtCollection.RegisteredDate, Debtor.CountryCode, Client.Country from CaseHistory
inner join CaseDebtCollection on CaseDebtCollection.CaseNo = CaseHistory.CaseNo
inner join dbo.Debtor on dbo.CaseDebtCollection.DebtorNo = dbo.Debtor.DebtorNo
inner join dbo.Client on dbo.CaseDebtCollection.ClientNo = Client.ClientNo
inner join CaseInvoice on CaseInvoice.CaseNO=CaseHistory.CaseNo
    LEFT JOIN TempCHUnik ON (TempCHUnik.Unik = CaseHistory.Unik)
WHERE (CaseHistory.Code='301' or CaseHistory.Code='400') and
ClosingDate = '' and CaseDebtCollection.Workflow not in ('0012','0015','0016') and CaseInvoice.InvoiceLineNo=1 and TempCHUnik.Unik IS NULL

Open in new window

When running it in a C# program I get this error in the log:
Invalid object name 'TempCHUnik'.   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
The table TempCHUnik is there both before and after, so this is very strange.
I use this code segment to get data from the SQL Server, and it works perfectly with a different sql statement:
      cmd = new SqlCommand(sql, con);

                DataTable table = new DataTable();
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                adapter.Fill(table);
                con.Open();

                dr = cmd.ExecuteReader();
                dr.Read();

Open in new window

0
Cloud Class® Course: Microsoft Exchange Server
LVL 12
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

Hi,

The below SQL query works for me, but I have one issue.  Which is that if it happens to find a second or third "contact" (this part of the join:  "LEFT JOIN contact ON de_rowid = co_rowid_debtor AND co_done_date between CONVERT(DATE,'@tvarDatef',103) and CONVERT(DATE,'@tvarDatet',103)" ) it will return duplicated data.

SELECT DISTINCT bl_rowid
,de_cl_account
,CASE WHEN contact.co_description IN ('@tvarDirectDebitClaim', '@tvarBPAYClaim') THEN 'CLPD' 
WHEN contact.co_type = '22' THEN 'SMS1' 
WHEN contact.co_description = 'Copy' THEN 'CONC' 
WHEN contact.co_description = '@tvarTransVar' THEN 'TRAN' 
WHEN contact.co_description = 'HARD' THEN 'FINH' 
WHEN contact.co_description = '@tvarInfo' THEN 'INFO' 
WHEN contact.co_description = 'MRT' THEN 'RTS' 
WHEN contact.co_type = '1' and co_user_1 = 'Invalid' THEN 'NOAN' 
WHEN contact.co_type = '1' and co_user_1 = '@tvarMD' THEN 'LM' 
WHEN contact.co_type = '1' and co_user_1 = '@tvarMUd' THEN 'DISC' 
ELSE 'SMS1' 
END as TASK
,bl_text
    FROM blobs
    JOIN debtor ON de_rowid = bl_rowid_debtor
    JOIN client ON cl_rowid = de_rowid_client
    LEFT JOIN contact ON de_rowid = co_rowid_debtor AND co_done_date between CONVERT(DATE,'@tvarDatef',103) and CONVERT(DATE,'@tvarDatet',103)
    WHERE cl_number ='2005' and de_group_id < '1' AND bl_date between CONVERT(DATE,'@tvarDatef',103) and CONVERT(DATE,'@tvarDatet',103)

Open in new window


this is what it returns:
BL_ROWID      DE_CL_ACCOUNT      TASK      BL_TEXT
4961674      471146      INFO      NEW STATUS
0
Good evening,

We are having an issue with our SQL Server Express 2008R2 installation on our new server.  We currently have a server running SBS2011, along with SQL Server Express 2008R2.  Since support for SBS2011 is ending soon, we have purchased a new server, which currently has Server 2016 Standard and SQL Server Express 2008R2 installed.  I am currently testing the new server for functionality in a test environment, and we are unfortunately having an issue.  

We currently utilize an Access Database to track all of our patient information, along with SQL Server for authentication and reporting of the Access Database.  In order to move the database to the new server, I have backed up the database on the SBS2011 server.  I then installed SQL Server on new server (2016 Standard), and then restored the database.  When opening the Access Database file, it should query the SQL Server for permissions via Active Directory and SQL Server.  I have the authentication on the database set as mixed mode.  I have verified the credentials exist in SQL.  I am able to connect successfully from a workstation using the Microsoft Kerberos Configuration Manager for SQL Server tool as well.

I have attached screenshots of the errors that I am receiving and would greatly appreciate any input to assist in resolving this issue.

Thanks for the help.

Error 1Error 2Error 3
0
I had this question after viewing After SQL 2016 Upgrade SA Account Keeps Getting Locked by Backup Jobs.

I am getting the exact same error... except there is no message in the logs about a failed login attempt  by the SA account. And what is even more interesting, the SA password was never changed...

I am administering an isolated SQL Server database, managing a specific task for my company. This database takes data from other sources and consolidates it for marketing.

Two years ago, we updated the server and migrated the database, using SQL 2012 Standard. The server is a Dell

Last month, with the assistance my our corporate DBA, we upgraded from 2012 to 2016 Standard SP1.

Since then, I have been getting these messages about the SA account being locked out... well, why would that do that when the SA account password has never been changed? I can log into SSMS using that password... I can run jobs using that password, as soon as the SQL Server agent tries to run it, the errors fly.

This morning, I have updated the maintenance jobs to use a direct connection to the server vs the "local connection" default. I will test that shortly.

This is one of two problems... the other is different and will be under a different title.

Thank you all in advance!

Andy
0
This is a follow-on to https://www.experts-exchange.com/questions/29113572/Need-SQL-To-Change-Tax-Code-in-Woo-Commerce.html.  This query works well but I really need the description of the product, sku and product id for it to supply what I need to see.  Regarding the update that remains the same.  Albeit, I haven't tested it yet since I can't see without the other fields what products will be updated.

Thanks,
0
How to consume web API  in stored procedure I am using oracle 11g R1 DB  ? Please help with an example.
0
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
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
Upgrade your Question Security!
LVL 12
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

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
1
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

Query Syntax

51K

Solutions

20K

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.