We help IT Professionals succeed at work.

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.

Hello Experts,

Hope you are all keeping well.

Here is what I would like in the where clause of a sql select statement:

DATEDIFF(YY,GAC.BIRTHDATE,GETDATE()) - CASE WHEN DATEADD(YY,DATEDIFF(YY,GAC.BIRTHDATE,TERM.TERM_BEGIN_DT),GAC.BIRTHDATE) > TERM.TERM_BEGIN_DT THEN 1 ELSE 0 END

how do I do this ?  

thanks, Brock
0
Hi,

I have cluster SQL (2008) running under windows server 2008. The SQL server agent stop working.  I got an error 1069 which is related to failure in login (I did not change the password for SQL or windows).  I tried to reconfirm SQL password in SQL server manager but I got an error "The server threw an exception [0x80010105]". The SQL server service is working. However,  the SQL server agent not working. The VIA protocol is disable.

1.jpg
I tried to start SQL agent but I got an error.

2.jpg
3.jpg
4.jpg
0
Hi EE,

In SQL how would I check for records over a date range including only entries specific weekdays Saturday or Sunday in this case?

Any assistance is appreciated.

Thank you.
0
I am new to ASP.NET. I am using ASP.NET core 3.1 and razor page and wondering how to implement stored procudere on it.

So I have tables and stored procedure to insert and select on the sql server management studio. I would like to work with Database approach first, and I just want to insert some records on database and retrieve database table on the web form. My main concern is how do I establish connection using appsettings.json file and call insert,select, or update stored procedure. I have been using stored proc before using ADODB.net to create windows form application but I haven't done anything with ASP.net. So, any help regarding using stored proc with razor pages is highly appreciated. Thank you.
0
Hi there,

Does anyone have a quick SQL Server stored procedure/script that will allow me to search an entire database for a string and then the output will show the exact table and column of where that string is?

We're trying to review an SQL Server database for how everything is stored.

Thanks in advance!
- Christian
0
I have a Access Query that returns a lot of data (25,360 in current set) I  want to count how may Unique Building and Floors there are (In this Case 1 building and 2 floors) and I can't figure out how to do in within 1 query (I prefer not to create lots of  a sub query to count the Building, Floor etc... separately)

Attached is sample output I'm trying to achieve and a the query design

Design.PNGRestlts.PNG
-----------
SELECT [Lookup Current Surveys].[Adept Survey UID], [Lookup Current Surveys].[Accordant SUS UID], [Lookup Current Surveys].Instance, [Lookup Curent Project].Sector, Count([SUS Source - Survey Data - Srva].ID) AS Observations, Count([SUS Source - Survey Data - Locn].Building) AS [Building Count], Count([SUS Source - Survey Data - Locn].Floor) AS [Floor Count]
FROM [SUS Source - Survey Data - Locn] INNER JOIN (([Lookup Current Surveys] INNER JOIN [Lookup Curent Project] ON [Lookup Current Surveys].[Adept Survey UID] = [Lookup Curent Project].[Adept Survey UID]) INNER JOIN [SUS Source - Survey Data - Srva] ON [Lookup Current Surveys].[Accordant SUS UID] = [SUS Source - Survey Data - Srva].Survey) ON [SUS Source - Survey Data - Locn].Code = [SUS Source - Survey Data - Srva].Location
GROUP BY [Lookup Current Surveys].[Adept Survey UID], [Lookup Current Surveys].[Accordant SUS UID], [Lookup Current Surveys].Instance, [Lookup Curent Project].Sector;
0
Trying to use SQL in Access update values from one table to another
I want to update the values in table xxxxxxxxxx_TaxRecs_ForAttyFeeReconciliation from the values in tblTaxRecs if the BRT, TaxYear and TaxHeaderID are equal in both tables
Here's what I have, which isn't working

updateString = " Update xxxxxxxxxx_TaxRecs_ForAttyFeeReconciliation T1 " & _
                " Set [PostPrincipalAmt] = T2.[PrincipalAmt] " & _
                "  ,  [PostPenaltyAmt]   = T2.[PenaltyAmt]   " & _
                "  ,  [PostInterestAmt]  = T2.[InterestAmt]  " & _
                "  ,  [PostLienCost]     = T2.[LienCost]     " & _
                "  ,  [PostAttyFeesAmt]  = T2.[AttyFeesAmt]  " & _
                "  ,  [PostActive_YN]    = T2.[Active_YN]    " & _
                " FROM tblTaxRecs T2                         " & _
                " Where T1.[TaxYear]     = T2.[TaxYear]      " & _
                "  AND  T1.[BRT]         = T2.[BRT]          " & _
                "  AND  T1.[TaxHeaderID] = T2.[TaxHeaderID]  "

DoCmd.RunSQL updateString

Open in new window


The error I'm getting is

Syntax error (missing operand) in query expression T2.[Active_YN] from tblTaxRecs T2

Can anyone spot the error?
0
I have a Powershell script that does a query on an SQL Server table and exports the data to a CSV file.  While the Powershell script is exporting, I need to update one value for each record from a specific field.  For example, if it has RTG-11111 in the "ID" column, I need to replace the "RTG" part with three random letters.  Do you have any suggestions?

Thanks!
-Christian
0
I am looking for an answer to something that for some reason is perplexing me.  I wrote this MYSQL query for a website a few years ago and when it first went into production, it worked perfectly.  Now, as the database has grown to millions of entries, this query, that once took less than a second, now takes upwards of five minutes to return the result set.  Any ideas on how to optimize this query?  ts in the query is the time stamp and I am looking for the most recent group of time stamps.

SELECT  r.site_id
                          ,r.tank
                          ,r.product
                          ,r.gallons
                          ,r.inches
                          ,r.water
                          ,r.deg
                          ,r.ullage
                          ,r.perc_full
                          ,r.ts
                                            
          FROM tank_readings r
             
                JOIN(
                         SELECT MAX(`ts`) mt
                         
                         FROM tank_readings
                        
                        WHERE site_id = ' . $site['site_id'] . '
                       ) tr
                       
                    ON r.ts >= (tr.mt -1)
                                                
                 JOIN site s
                    ON r.site_id = s.site_id
                                                                
                        WHERE r.site_id = ' . 

Open in new window

0
I need some help for the SQL Query. I have a loan table (1st lien and 2nd lien). If the loan program is 'Bond' for the 1st lien then I need to change 2nd lien loan program to 'Bond'.

Example:

Guid        LinkGuid     LoanNumber     LienType         ProgramCode
{f1244}  {2077a}       123456789      First Lien         Bond
{2077a} {f1244}        567345673      Second Lien    C30

Result
Guid        LinkGuid     LoanNumber     LienType         ProgramCode
{f1244}  {2077a}       123456789      First Lien         Bond
{2077a} {f1244}        567345673      Second Lien    Bond <-- should change to 'Bond'
0
I need to display average labor hours per PN and work performed by month. How would I do that using the example data attached?
EXAMPLE.csv
0
We have tables out in the field and we have to drop any indexes that we are not aware of.  How do you drop an index from a table without knowing the name.

Thanks
John
0
Using Teradata to export a query.  I am exporting it in pipe delimited text file. The export works except that the column headings come out with all of the format like this:

((((((((Member_ID||'|')||Product_ID)||'|')||Valid_From)||'|')||Valid_Till)||'|')||Enrollment_ID_Type)

Here is my code:

SELECT      Member_ID || '|' || Product_ID || '|' || Valid_From || '|' || Valid_Till || '|' || Enrollment_ID_Type
FROM      Medicare_Enrollment
;

Is there a way to have the columns come out like they are in the table?

Thanks,
Scott
0
I need to check for both indexes and constraints on a table and then drop them - do my update and then reinstate the index and constraint.  I need the correct syntax to do that.

The table is LUICD9 and the PK is ICD9Code - the index is listed as PK_LUICD9

Any help is appreciated
John
0
Deleted a database in sql 2016 a week ago, and now logs are showing login failed for user xxxx\xxxx (domain account replaced with x's), cannot open database.  I have scrubbed everything i can think of for hours and can't find the culprit.

SqlServer Logs:
04/02/2020 09:46:15,Logon,Unknown,Login failed for user 'xxxx\xxxxxxx'. Reason: Failed to open the explicitly specified database 'SWPDM_CortecPDM'. [CLIENT: <local machine>]
04/02/2020 09:46:00,Logon,Unknown,Login failed for user 'xxxx\xxxxxxx'. Reason: Failed to open the explicitly specified database 'SWPDM_CortecPDM'. [CLIENT: <local machine>]
04/02/2020 09:41:00,Logon,Unknown,Login failed for user 'xxxx\xxxxxxx'. Reason: Failed to open the explicitly specified database 'SWPDM_CortecPDM'. [CLIENT: <local machine>]
04/02/2020 09:40:45,Logon,Unknown,Login failed for user 'xxxx\xxxxxxx'. Reason: Failed to open the explicitly specified database 'SWPDM_CortecPDM'. [CLIENT: <local machine>]
04/02/2020 09:35:45,Logon,Unknown,Login failed for user 'xxxx\xxxxxxx'. Reason: Failed to open the explicitly specified database 'SWPDM_CortecPDM'. [CLIENT: <local machine>]


SqlServer Agent Logs:
04/02/2020 09:46:15,,Error,[298] SQLServer Error: 4060<c/> Cannot open database "SWPDM_CortecPDM" requested by the login. The login failed. [SQLSTATE 42000]
04/02/2020 09:46:15,,Error,[298] SQLServer Error: 18456<c/> Login failed for user 'xxxx\xxxxxxxx'. [SQLSTATE 28000]
04/02/2020 09:46:00,,Error,[298] SQLServer Error: 4060<c/> …
0
Hi Team ,

I getting the below error when i run the  code in plsql  , ideally we should be able to run queries in case statement  , but not sure why it does not work in plsql program
Any help is really appreciated. Is it not possible to  write a subquery in the case statment in plsql unlike sql ?

declare
l_deptid number(10):=&1;
l_comments varchar2(40):=null;
begin

case
when (select count(*) from employees where department_id=l_deptid)>=40 then
      l_comments:='Highly Size department';
else
      l_comments:='Small Size department';
end case;

dbms_output.put_line(' Department is '|| l_comments);
end;
/


Error report:
ORA-06550: line 7, column 7:
PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:

   ( - + case mod new not null <an identifier>
   <a double-quoted delimited-identifier> <a bind variable>
   continue avg count current exists max min prior sql stddev
   sum variance execute forall merge time timestamp interval
   date <a string literal with character set specification>
   <a number> <a single-quoted SQL string> pipe
   <an alternatively-quoted string literal with character set specification>
   <an alternat
ORA-06550: line 7, column 66:
PLS-00103: Encountered the symbol ")" when expecting one of the following:

   . ( * @ % & - + ; / at for mod remainder rem
   <an exponent (**)> and or group having intersect minus order
   start union where connect || multiset
06550. 00000 -  "line %s, column …
0
I have a number of stored procedures I'd like to search for a particular string.

When I search using syscomments text it doesn't find anything.

However I know for sure it exists in one of the stored procedures, but this particular procedure has over 12,000 characters and the string doesn't appear until over 9,000 characters.

Has anyone any scripts to suggestions for how to definitively search my stored procedures? I have ApexSQL Search but that doesn't seem to actually search the text of a stored procedure and I also have RedGate SQL Search but I only just installed that and it isn't working - I have a support ticket open with them.
0
Hello:

I have tried several different ways of combining my two CTEs within one "UNION ALL" statement but am having no luck.

How do I modify my syntax below to accomplish this?

Thank you!

Software Engineer

; with wip as 
(
select NULL as [LOTSEQNBR], FG.BatchNo as [FGBATCH], FG.FormulaId as [FG], FG.[Labor Cost] as [FGLABOR], FG.[OverHead Cost] as [FGOH], 
FG.[Material Cost] as [FGMAT], FG.[Labor Cost] + FG.[OverHead Cost] + FG.[Material Cost] as [FGCOST], FG.[TotalFGWeightYielded] as [FGWEIGHT], 
FG.[Labor Cost]/FG.[TotalFGWeightYielded] as [LABORLB],
FG.[OverHead Cost]/FG.[TotalFGWeightYielded] as [OHLB],
FG.[Material Cost]/FG.[TotalFGWeightYielded] as [MATLB],
NULL as [FORMULA], NULL as [FORMULAMAT], NULL AS [FORMULALABOR], NULL AS [FORMULAOH], NULL AS [Component], NULL as [LOT], NULL as [LOTQTY], 
NULL as [LOTMATUNITCOST], NULL as [LOTMATWIPCOST]
from BM_View_Dashboard_ProductionCostAnalysis FG
INNER JOIN BM010115 BOM ON FG.FormulaId = BOM.PPN_I
where FG.FormulaId = '506' and FG.BatchNo = '50418G12D' and BOM.SUBCAT_I = 1)
select [LOTSEQNBR], [FGBATCH], [FG], [FGLABOR], [FGOH], [FGMAT], [FGCOST], [FGWEIGHT], [LABORLB], [OHLB], [MATLB], [FORMULA], [FORMULAMAT], [FORMULALABOR], 
[FORMULAOH], [Component], [LOT], [LOTQTY], [LOTMATUNITCOST], [LOTMATWIPCOST],
[LABORLB]*[LOTQTY] as [LOTLABORWIPCOST],
[OHLB]*[LOTQTY] as [LOTOHWIPCOST]
from wip
; with wiplot as 
(
select * from (
select DISTINCT LOT.SLTSQNUM AS [LOTSEQNBR], NULL as [FGBATCH], NULL as [FG], NULL 

Open in new window

0
Screen shot of upgrade progress showing error
The following error details are in the upgrade log:

The report server database is not a supported compatibility level or a connection cannot be established. Use Reporting Services Configuration Manager to verify the report server configuration and SQL Server management tools to verify the compatibility level.

The report server configuration is not complete or is invalid. Use Reporting Services Configuration Manager to verify the report server configuration.

The report server has already been upgraded to 2016.  The report config app on the report server looks normal.  REPORTS in this database continue to WORK properly before and after the report server upgrade to 2016.

We've deleted the account on the database and report server and re-added it.  We've re-selected the "ReportServer" database from the report config app and ran "Test Connection" with success. We're using the same domain account that was in use for 2014.

The report server has 2 instances.  We've applied the above attempts to both.

The last step will be to take out the existing ReportServer database and use the report server config app to "Create New" ReportServer database.  However, we would like to avoid this as we would have to manually re-deploy hundreds of reports to the server. ( And it might not solve the problem. )

Any ideas would be appreciated. Thanks.
0
Hi Experts,

I want to update student status if the date is joindate is more than 180 days old from todays date.

Update Students Set IsNew = 0
Where joindate - todaysdate > 180days	
	

Open in new window


Thanks in advance!
0
HI,

Hi I have done some calculations and have all my results in one row across a number of columns.

 Percent 1 percent 2, percent3

I would like to pivot these so i have 2 columns,

Percents.      Total

Percent1 0.64
Percent2 0.22
Etc
0
perHello All;

I have just run into a situation where I cannot find any information on it.
Installation of SQL Server 2019 WITHOUT installing Data Quality Services
If I uncheck it, it unchecks EVERYTHING.
So, the question is...
How do you install the SQL Server Database Engine, without installing the Data Quality Services

The reason:
I am installing the "SQL Server Failover Cluster" on the 2016 Desk Experience Server.
After this, I will be installing "Add node to a SQL Server failover cluster" on the 2016 CORE Server.

From my experience, adding the NODE fails every time I have tried to install it on the core.
A person posted but without proof that the Data Quality Services does not run on Server Core.
(This is my thread about the issue)

I am building my SQL Servers Clusters from scratch, and want to get the Core servers running SQL Server database engine.
And whatever I install on the Main SQL Cluster, will filter down into the NODE installations.
So I need to be cautious about what gets installed to make sure everything is going to go smoothly on this build.
These are the typical features that get installed on the NODE's
SQLEngine, SQLEngine\Replication, SQLEngine\FullText, SQLEngine\DQ
So please, if anyone has any information on this TA or the other TA, please let me know.

Thanks.
Wayne
0
First time posting. I have a dropdown with select options listed A through Z...

<select style="width:375px; text-align: left" id="alpha" onchange="fetch_select(this.value);">
<option value="" disabled selected>First Letter</option>
<option value="#">"0-9"</option>
<option value="A">"A"</option>
<option value="B">"B"</option>

<select id="operator" name='operator'>
			<option value="0">- Select Operator -</option>
		</select>
<select id="wnames">
	   <option value="0">- Select Well Name -</option>
	</select>

Open in new window


I also have two dependent dropdowns  that will return their values after the selection of the letter. Onchange of "apha", I need the value to pass to php via ajax, execute two sql queries simultaneously (not cascading), and return the data in json in the two dropdowns, "operator" and "wnames". I don't know how to execute the two sql statements with the one ajax variable and return the two sets of rows.       
Here is my ajax:
//Ajax to fetch operators once alpha chosen
		 function fetch_select(val){
        //var alpha = $(this).val();

        $.ajax({
			type: 'post',
            url: 'alphaScript53.php',
			dataType: 'json',
            data: {op_option:val},
            success:function(response){

                var op_len = response.length;

                $("#operator").empty();
                for( var i = 0; i<op_len; i++){
                    //var id = response[i]['Rig_ID'];
                    var op_name = response[i]['Operator'];
     

Open in new window

0
I have a SQL table with the following fields:

Customer ID |Fiscal Year | September Amount | October Amount| November Amount ....

I need to change the format so I can produce an SSRS report in the following format by customer:


 Months down the left hand column and Fiscal Years across the top. For example:

 Customer: 1

                         Fiscal Year1 |  Fiscal Year 2| Fiscal Year 3|
September        100.00       |     200.00       | 150.00         |
October              250.00      |     300.00       | 180.00         |
November
December
etc.

For some reason every approach I take leads to a dead end.

Thanks!
0
I have a datetime field that's in UTC. I need to convert it to local time.

Now, we have clients in the US that have different time zones. Clients in Europe. In Australia, etc.

I've been looking at examples and I found this one as an example : Example

Would this take care of converting UTC to correct local time?
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.