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?

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

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.

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

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.

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 = …

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

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>

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

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','')
              

Select all Open in new window

…

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 = 

Select all Open in new window

…

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?

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?