SQL

1K

Solutions

6

Articles & Videos

1K

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

I am struggling to assess the effectiveness of  mod_rewrite in protecting against SQL injection for both authenticated and unauthenticated SQL injection attempts.

http://www.sectechno.com/using-mod_rewrite-to-harden-apache/

In particular the site recommends;
# SQL Injection Probing
RewriteCond %{QUERY_STRING} ^.*(\@\@version|CHR\(|CHAR\(|UNION%20SELECT|/select/|/union/|/insert/|/update/|/delete/).* [NC,OR]
RewriteCond %{QUERY_STRING} ^.*(or|and)%20([0-9]=[0-9]).* [NC,OR]

    Does anyone have experience with this or can site a source?

Thanks,
0
Free Tool: Path Explorer
LVL 9
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

I have an application where users are scanning barcodes.  Once in a while they "spaz" their finger will hit the trigger twice and two records are created that are identical except for the record ID.

How can I delete the nearly identical records?
0
Hello all,

I have a SQL select statement with a join such as:

SELECT c.* FROM Customer c
LEFT JOIN CustomerGroup g on (g.CustomerName IS NULL OR  c.CustomerName = g.CustomerName) AND
                                                         (g.CustomerLoc IS NULL OR  c.CustomerLoc = g.CustomerLoc)


I need to have a clean way to do the following:

CustomerGroup field values can contain token type strings value could be XYZ% or <>XYZ as the string value.   I need to search the string and if contains % then make the "=" a "LIKE" or if it contains "<>" make the "=" a "<>"

Thanks for any assist.  Case statements worst case but am trying to see if anything cleaner.
0
Hi I have a very long text field column that is passed as a parameter. I want to take that text and split them into multiple lines. Let's say I want to split them in every 50th character. I tried different functions using LEFT and Substring but it cuts a word when that 50th character falls in. I want to be able to not cut the word but find the the next space and break that into a line.

How can I achieve that? Please assist
Thank you
0
hi i have the following sql which is returning 0 but i do have value in database
am in oracle 11g database
sqlnull.txt
0
I have an MS ACCESS report.   in the detail section I count the number of objects  for each day, then have a percentage calculated.

In the footer, I SUM the counts for a grand total  of the selected dates.   My problem is, I dont know how to write the grand total percentage.

In the detail section, I write my percentage calculation like this and it works:  =[CountOfChartedStatus2]/[CountOfTOTAL1]

In the footer, these are the two fields I need to make a percentage of:    =Sum([CountOfChartedStatus2])   and   =Sum([CountOfTOTAL1])

How should I write this to get a percentage.   Thanks.
0
I have an ERP system that puts the date for all transactions with a time as 12:00:00 AM.
(example:  6/26/2017 12:00:00 AM)

I have a SQL VIEW that I am using to import data into another SQL database but the time needs to something other then that time because it conflicts with another import time. I want my time to be hardcoded as 23:59:00.00 PM instead of 12:00:00 AM.

SELECT     ShippedDate AS ShipTransaction
FROM         dbo.ShipmentOrder

My current result from above is:
6/21/2017 12:00:00 AM

My needed result for this line would be:
6/21/2017 23:59:00 PM

Note:  I could also use converted date as 2017-06-12 23:59:00.000  (is date format of system I am importing into)
0
I ran into a issue with what I use as my standard connection to sql 2014 from Crystal Reports 16 Service Pack 4.  It seems that after a Microsoft update the other day the OLE DB (ADO) connection does not show anything related to the database.  ie tables, sp.  

I even tried to make a new OLE DB (ADO) connection.  It will connect to the db but again will not show any of the tables...ect.

I have attached a screenshot of the connection.
2017-06-28.png
0
I am not able to see the MSDB Folder in the SSIS Stored Packages. We are using SQL 2014 64-bit. In the Database Folder Databases\System Databases\MSDB\Tables\System Tables, I see the table dbo.sysssispackages but no table contents. In our SQL 2008 32-bit I can view the contents and all the MSDB tables are available to view and the Stored Packages MSDB is visible . In SQL 2014 64-bit, that is not available.

Do I have a corrupted MSDB in 2014? How is the available and yet no editing or adding data to the table? How do I find out if the MSDB needs to be repaired?
0
We are running SQL Server 2012 Standard and have configured several SSRS report subscriptions to save to a file share daily.  This typically works great but when it fails we have no way of finding this out other than checking the folder to see if the report has been generated.  

We would like to configure SSRS or the associated SQL job to email us when the report generation fails for any reason.  Is this possible?  Basically we want to get away from having to manually check for problems.
0
Back Up Your Microsoft Windows Server®
LVL 4
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

I have SSIS package and it runs every hour. This package import data from csv into database table. From march 2017 this package has been failing. What i see there is a SQL task step which is failing but before it was succeeded. Here is the below code:-- I put the four *** sign where it is failing.


declare @toparse nvarchar(max)
declare @strlength int
declare @separator_location int
declare @instance nchar(10)

declare @instance_url nvarchar(max)

declare @hostname nvarchar(50)
declare @strIn nvarchar(2000)
declare @separator nchar(2) = ' '
declare @inner_separator nchar(1) = '|'

DECLARE @item nvarchar(1000)  -- assume maximun item length 1000 char
DECLARE @len int, @sepPos1 int,  @sepPos2 int

-- truncate table staging_oracle_version
--declare @truncate varchar(200)
--set @truncate = 'truncate table staging_oracle_version'
--exec (@truncate)

-- get oracle instances to load
declare  oracleinstance_csr cursor for
select certname
      , value
from puppetdb_certname_facts
where fact like 'oracle_instance_url'
   and value IS NOT NULL
   and value !=''
  and value != '""'

open oracleinstance_csr

fetch next from oracleinstance_csr into @hostname, @strIn

--select @hostname, @strIn

while @@FETCH_STATUS = 0
begin

  SET @strIn = REPLACE(@strIn, char(10), @separator)  -- Replace all the LF (char(10)) from the string and replace with defined separator [ the code for CR is char(13)]
  Set @sepPos1 = 1
  set @sepPos2 = …
0
In the following @table...

I need to set active = 1
Where ExternalID is duplicated and the ExternalID2 rows are not equal

As an aside.. the duplicated ExternalID LESSER IndividualID datarow's ExternalID2 will always be 0 and the second row will be > 0

DECLARE @Table TABLE
    (
        BusinessID INT ,
        IndividualID INT ,
        ExternalID INT ,
        ExternalID2 INT ,
        Active BIT
    );

INSERT INTO @Table (BusinessID ,IndividualID ,ExternalID ,ExternalID2,Active)
VALUES (12345 , 100 ,222 ,0 ,1) ,
       (12345 ,101 ,223 ,0,1) ,
       (12345 ,102 ,223 ,555,1) ,
       (12345 ,103 ,322 ,0,1);


SELECT * FROM   @Table;

Open in new window


SP
0
Hi have a query that I need to match on titles selecting that latest value

select top 10 itemtypeid,ItemType from ItemsTypes where ItemType like '%' + 'Mud Pump' + '%' order BY itemtypeid desc

this would return the below results,

12239      Radiator [Mud Pump Engine]
12238      Mud Pump Valve [Discharge]
12237      Mud Pump Valve [Bleed Off]
12236      Mud Pump Remote Station
12235      Mud Pump PTO
12234      Mud Pump Hydraulic Drive Pump
12233      Mud Pump Engine Transmission
12232      Mud Pump Engine Torque Converter Cooler
12231      Mud Pump Hydraulic Drive Motor
12230      Mud Pump Engine Torque Convertor

If I were to use this Title "Radiator [Mud Pump Engine]"  in the query no results would be retuned.

select top 10 itemtypeid,ItemType from ItemsTypes where ItemType like '%' + 'Radiator [Mud Pump Engine]' + '%' order BY itemtypeid desc

How can I make a better query to return data if the titles might contain square brackets or not ?

Thanks
0
And also this mapplet should be used in another mapping
0
i wrote a program like this but it was not right can anyone please modifiy the code ????

Education Details:<br>
 <style>
 table, th, td  {
    border: 1px solid black;
 }
 </style>

  <table style="width:200%"  />
  <tr>
    <th>Qualification</th>
    <th>Years</th>
    <th>University</th>
    <th>Percentage</th>
  </tr>

  <tr>
    <td><input type="text" name="Qualification1"></td>
    <td><input type="text" name="Years1"></td>
    <td><input type="text" name="University1"></td>
    <td><input type="text" name="Percentage1"></td>
  </tr>
  <tr>
    <td><input type="text"  name="Qualification2"></td>
    <td><input type="text" name="Years2"></td>
    <td><input type="text" name="University2"></td>
    <td><input type="text" name="Percentage2"></td>
  </tr>
  <tr>
    <td><input type="text"  name="Qualification3"></td>
    <td><input type="text" name="Years3"></td>
    <td><input type="text" name="University3"></td>
    <td><input type="text" name="Percentage3"></td>
  </tr>
  <tr>
    <td><input type="text"  name="Qualification4"></td>
    <td><input type="text" name="Years4"></td>
    <td><input type="text" name="University4"></td>
    <td><input type="text" name="Percentage4"></td>
  </tr>

 </table>
 </br>
0
Dear all,

I am running a syntax problem (I guess) from below while using JPA repository (HQL) to write the following 2 SQL statements.
(1) The first one works out fine.
(2) The second one involves DISTINCT, GROUPBY, SUM and I guess the syntax might be different.

Can anyone help to modify the second HQL statement to make it work? Many thanks,
Sam.

The generated error message looks like this:
 org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: . near line 1, column 111 [SELECT DISTINCT(v.cbmRegionOrder), v.cbmRegionDesc, v.kpiOfficeCode, v.kpiOfficeDesc, sum(v.inboundCount) as v.inboundCount, sum(v.outboundCount) as v.outboundCount, sum(v.totalCount) as v.totalCount FROM com.hktdc.cbm.report.model.VwCbmKpiCreatedByOffice v WHERE (:__$synthetic$__1 IS NULL or length(:__$synthetic$__1) = 0 or

public interface VwCbmKpiCreatedByOfficeRepository extends JpaRepository<VwCbmKpiCreatedByOffice, Long>{

      @Query("SELECT v FROM VwCbmKpiCreatedByOffice v " +
               "WHERE (:#{#reportSearchCriteria.firstReplyMonthFrom} IS NULL or length(:#{#reportSearchCriteria.firstReplyMonthFrom}) = 0 or to_date(v.firstReplyMonth,'yyyy-MM') >=  to_date(:#{#reportSearchCriteria.firstReplyMonthFrom},'MM-yyyy') ) " +
           "AND   (:#{#reportSearchCriteria.firstReplyMonthTo}   IS NULL or length(:#{#reportSearchCriteria.firstReplyMonthTo}) = 0   or to_date(v.firstReplyMonth,'yyyy-MM') < add_months(to_date(:#{#reportSearchCriteria.firstReplyMonthTo},'MM-yyyy'),1) ) " …
0
The Server (win 2008) name for one of our test Server was changed , however, the server hosted an SQL Database. with the change, it has resulted in us unable to connect to the SQL Database (sql 2008). the previous username does not work anymore and i even tried connecting with windows authentication and still nothing. is there anyway to resolve this?

Regards,
Grand
0
I am running the below query to count number of appointments for each day of the month and you can see the output below that.  I want to add a sub query to break out the total appointments for hour 17 for each day and have them show in the Hour17 column by each day.  I am currently getting the total number of appointments for the month in the Hour17 column.  I understand why but can't come up the solution to break it on down by day.

select Monthname = 'May',DATEPART(DD,ApptStart) as day,COUNT(DATEPART(DD,ApptStart)) as Total,
Hour17= (select COUNT(DATEPART(hh,ApptStart)) as HTotal
from Appointments apptb
where apptb.ResourceId = '560' and DATEPART(M,apptb.ApptStart) = 04 and DATEPART(yyyy,ApptStart) = 2017 and DATEPART(hh,apptb.ApptStart) = 17)
from Appointments appta
where appta.ResourceId = '560' and DATEPART(M,appta.ApptStart) = 04 and DATEPART(yyyy,appta.ApptStart) = 2017
group by DATEPART(DD,ApptStart)


May      3      10      77
May      4      8      77
May      5      4      77
May      6      8      77
May      10      7      77
May      11      7      77
May      12      12      77
May      13      3      77
May      17      6      77
May      18      12      77
May      19      11      77
May      20      19      77
May      24      9      77
May      25      6      77
May      26      11      77
May      27      9      77
0
Hi, how do I take the below and make it column delimited by id. I tried to do a coalesce but this didn't work. I just need the site code and the primary site fields to have the values for each line by id combined in the same site code or primary site columns separated by a comma. I am not sure how to do this. I appreciate any help!

sqlhe.pdf

and make it into this

sqlhe1.pdf
SELECT DISTINCT 
       emp.UniqueID AS 'EmployeeID'
       ,'b' + RIGHT('00000' + CONVERT(varchar, emp.UniqueID), 5) AS 'Username'
       , munispre.prem_lname AS 'LastName'
       ,munispre.prem_fname AS 'FirstName'
       ,munispre.prem_minit AS 'MiddleName'
       ,munispre.prem_hire AS 'HireDate'
       ,emp.Email
       ,munispre.prem_act_stat AS 'ActiveAccount'
       ,'' AS 'GroupTypeID'
--     ,emp.JobDescr AS 'DemogCode'
       ,CASE
              WHEN emp.JobDescr LIKE 'TEACHER MS 187 DAYS'                         THEN REPLACE(emp.JobDescr, '187 DAYS','')
              WHEN emp.JobDescr LIKE 'TEACHER ES 187 DAYS'                         THEN REPLACE(emp.JobDescr, '187 DAYS','')
              WHEN emp.JobDescr LIKE 'ADM ASST ES PG C5 @ 200 DAYS'         THEN REPLACE(emp.JobDescr, 'PG C5 @ 200 DAYS','')
              WHEN emp.JobDescr LIKE 'CN MANAGER MS PG4 @ 178 DAYS'         THEN REPLACE(emp.JobDescr, 'PG4 @ 178 DAYS','')
              WHEN emp.JobDescr LIKE 'COUNSELOR MS HS OR ES PG2'                   THEN REPLACE(emp.JobDescr, 'PG2','')
              

Open in new window

0
Transaction Monitoring Vs. Real User Monitoring
LVL 1
Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

I have a proc that generates / suggests indexes that are needed.

I have an individuals table...
Went from 25000 to 600k+ records

And selects on where FirstName = '%%' simply bogged down to a timeout.

So...
IIT was suggested I add this
Create NonClustered Index IX_Individuals_missing_1852 On [EverywareV3].[dbo].[Individuals] ([FirstName]);

I executed and it has been running 2 3/4 hours.

Is there anything more efficient?

This is the code that generated the "missing" index.

SELECT   t.name AS 'affected_table' ,
            'Create NonClustered Index IX_' + t.name + '_missing_' + CAST(ddmid.index_handle AS VARCHAR(10)) + ' On ' + ddmid.statement + ' ('
            + ISNULL(ddmid.equality_columns, '') + CASE WHEN ddmid.equality_columns IS NOT NULL
                                                            AND ddmid.inequality_columns IS NOT NULL THEN ','
                                                        ELSE ''
                                                END + ISNULL(ddmid.inequality_columns, '') + ')'
            + ISNULL(' Include (' + ddmid.included_columns + ');', ';') AS sql_statement ,
            ddmigs.user_seeks ,
            ddmigs.user_scans ,
            CAST(( ddmigs.user_seeks + ddmigs.user_scans ) * ddmigs.avg_user_impact AS INT) AS 'est_impact' ,
            ddmigs.last_user_seek
FROM     sys.dm_db_missing_index_groups AS ddmig
            INNER JOIN sys.dm_db_missing_index_group_stats AS ddmigs ON ddmigs.group_handle = 

Open in new window

0
I am setting up Database BackUp for an SQL Server 2014. It is a 1 GB database.  I first selected BackUp Type as "Full", but realized that the back up size grows after each back up. After 6 days the back up was 6 GB.

I now want to set up weekly Full back ups and daily differential back ups. My questions are:

- If I name my full back up mydbname.bak, when I create differential back ups, am I overwriting mydbname.bak or I have to create a new file like mydbname-diff.bak?

- If I have two separate back ups (one full and one differential, I am not sure how to use them in case I have to restore)

- Do I need to back up Transaction log?
0
A SQL job is scheduled to run at 6.00AM every day. Some days the job fails with the error: Unable to connect to SQL Server (local) failed.

When executing the job manually it always succeeds and it runs without failure, however there are times that the job fails for no apparent reason with the above error.

What could cause this behavior?
0
Hi,

We want to create a task manager using ASP.NET. Basically, when a user logs in, this person can:

1. create a task, schedule a task, put note. Similar to Outlook
2. email(s) will be sent out in the morning to remind this person what this person needs to do that day.

I know Outlook might do the trick, but we want to add more custom fields into the system. Any ideas?  thanks
0
Looking to complete the following - export ADFS configuration from an existing ADFS 2.0 with database hosted on SQL Server to a new instance of ADFS 2.0 with configuration database hosted on WID.
What tools would you recommend for backup/restore - or rather import/export. More to the point, is there a KB that goes into step-by-step process?
0
I need help creating custom fields within the ''ADD A NEW COURSE" page in moodle. I then need to build a report (SQL) to display all that information which then needs to be exported out into excel. I am currently using Configurable Reports
0

SQL

1K

Solutions

6

Articles & Videos

1K

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.