[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Databases

57K

Solutions

40K

Contributors

Databases are organized collections of data, most commonly accessed through management systems including schemas, tables, queries and processes that allow users to enter and manipulate the information or utilize it in other fashions, such as with web applications or for reporting purposes.

Share tech news, updates, or what's on your mind.

Sign up to Post

Hello,

I want to be able to create a SQL trigger on my database to email me when a transaction is made and the margin is less than 20%.
A previous IT professional had created this trigger (which I think is the fundamentals) however it was never finished and I am unsure how to finish it off so it runs then emails me.
Are you able to help?

The example trigger is:

USE testdatabase
If EXISTS (SELECT name FROM sysobjects
WHERE name = ‘trig_mail’ AND type = ‘TR’)
DROP TRIGGER trig_mail
GO
CREATE TRIGGER trig_mail
ON TRANSDETAILS
FOR INSERT, UPDATE, DELETE
AS
Select from inserted where (ProductCode <> ‘Freight’)
@percentage = (Localcost-LocalUnitPrice) / (LocalUnitPrice*100)
If (percentage < 20)
Begin
Exec master.xp.sendmail ‘EmailID’, ‘EmailMessage’
END
GO

When I run the query I get:

Msg 102, Level 15, State 1, Procedure trig_mail, Line 6
Incorrect syntax near '@percentage'.
0
Big Business Goals? Which KPIs Will Help You
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

I have a dynamic web project ( SOAP web service), which takes parameters from a received message and inserts data to oracle db. This all works fine. However, db connection url, password and user are hardcoded.

This web app will be distributed to a number of servers with different db connection details, so it can't be hardcoded and generating a new app for each server is not an option.

I wonder if I could store needed info in an xml file and read from there. Is someone able to give me a nice simple example of how to do it?

Also, where such file would have to be placed, bearing in mind it would have to be possible to edit it after the deployment?
0
Looking for a query to query all non-system tables in an Oracle 11g database for a given value
Additionally, If you can given a flavour of the same query which can search all the table starting with "ENV"  or "DOC"  , it is much appreciated
0
Hello Expert,

Need to identify all tables in database
that begin with prefix  'D_'.

In PL/SQL (Oracle 12c) have tried

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLE
WHERE TABLE_NAME LIKE 'D_%'

SELECT table_name
FROM INFORMATION_SCHEMA.tables
WHERE name = 'd_%';

Both of these return
'Invalid table name'

Think these may be for SQL Server

Tried

SELECT TABLE_NAME
FROM ccm.user_tables
WHERE TABLE_NAME LIKE 'D_%'

This returns 'Table or view does not exist'

Thanks.

Allen in Dallas
0
The database has a set of tables that, when an user clicks the Edit button,
creates a draft copy that preserves a copy of the record in case the user
discards the edit. The d_address table mirrors the address table.
The d_employer table mirrors the employer table. There are dozens of
pairs.

For rather complex reasons drafts that are older than a couple of months cause
primary key constraint violations and errors in the front end application.

So the boss says delete all drafts older then three months.

A SQL script is written:
Delete FROM
    ccm.d_address
   where trunc(last_update_date) < '03-SEP-18'

And so several dozen of these scripts should be
written. All the table begin with d_  with syntax
d_tablename.

Is there a way to loop thru (or other method) the tables deleting older
draft records?  

Thanks.

Allen in Dallas
0
Is there any way a simple SQL Delete statement using a WHERE numeric column = numeric value can result in an Arithmetic overflow error converting numeric to data type varchar without a trigger?

delete OrderMeetings  where OrderMeetings.ORDER_NUMBER = 71935.00
Arithmetic overflow error converting numeric to data type varchar.

Open in new window


I have a customer saying that they are getting errors when deleting a row in a table. They can literally go into SQL and run a delete statement - as shown above - and it gives an Arithmetic overflow error converting numeric to data type varchar. They swear there are no triggers on the table. They sent us a copy of their database - no triggers but we can't replicate it either. However they say it always happens every time for them.

The ORDER_NUMBER column is numeric(15,2)

Any ideas?

They are using Microsoft SQL Server 2016.
0
Hi
Finally, after buying a second-hand book on SQL Server two days ago I managed to spot a recommended code on linking access app to SQL server database tables see below what they are saying:
When writing VBA code in Access, ADO (ActiveX Data Objects) is the preferred object model for working with SQL Server. However, the DAO (Data Access Objects) object model is more efficient and full-featured for working with Access objects, and tables linked to SQL Server are Access objects. So although you could use ADOX code to create links, the code presented in this chapter uses DAO.


I want to put the attached code in a module and call it using an autoexe macro at start , now can somebody help on the following items within the code:

(1)      Set tdf = db.TableDefs(LinkedTableName) How to list 120 tables here, show me an example ??
(2)      db.TableDefs.Delete LinkedTableName (What do I put here? Any example)
(3)      Set tdf = db.CreateTableDef(LinkedTableName) How to list 120 tables here, kindly show an example
(4)      tdf.Connect = ConnectionString  (Is it the ODBC with Less DSN File string????)
(5)      tdf.SourceTableName = SourceTableName (How to list 120 tables here, kindly an example??????????)


Public Function LinkTableDAO( _
LinkedTableName As String, _
SourceTableName As String, _
ConnectionString As String) As Boolean
‘ Links or re-links a single table.
‘ Returns True or False based on Err value.
Dim db As DAO.Database
Dim tdf As DAO.TableDef
On Error Resume Next
Set db …
0
Suppose I have a City which is divided into districts.
 

I have a database table  which is named "City". It includes the id of the district, and last names of people in the district.
As you can see in the sample table the last names occur more than once, as there are many people in the city with the same last name.

What I need is a query which will display the data broken up into the CityId, the DistrictId, and the unique last name of each person in each district. Note that the count of a last name should not occur more than once. So if the same district has 10 people with the last name of "Brown" that name should not be counted more than once. Also if the name was already counted in another district, is should not be counted again in a different district.

City Table :
CityId  |  DistrictId | FamilyName
----------------------------------
Austin  |  1          | Brown
Austin  |  1          | Brown
Austin  |  1          | James
Austin  |  1          | Maroni
Austin  |  2          | Maroni
Austin  |  2          | Trevor
Austin  |  2          | Jones
Austin  |  3          | Brown
Austin  |  3          | Greyson
Austin  |  3          | Maroni
Austin  |  3          | Nantula


Query result :

Austin | 1 | Brown
Austin | 1 | James
Austin | 1 | Maroni
Austin | 2 | Trevor
Austin | 2 | Jones
Austin | 3 | Greyson
Austin | 3 | Nantula

I have a sql query which will return the correct result in sql server, but it will not work in Sybase, because Sybase…
0
I have read from a few 'best practices' guides that every SQL Server administrator/DBA should configure their production instances to alert on any events with "severity levels 19 through 25". However, I am trying to grasp exactly what those events relate to, and translate that into something meaningful to senior management as to 'if you dont get alerts on these events - this could be the consequences... for those who do use SQL alerting, do you capture these events, and to be blunt "why"?
0
I have inherited a very old and poorly written stored procedure with nested fetch statements. This code processes a large amount of data by copying from one database into another. This process was working fine until updates were made to indexes. This past weekend the process was deadlocks on tables and failed to insert data. Added to this bad stored procedure the database we are inserting into is loaded with multiple triggers on just about every table.

I have reverted the indexes back to where they were but the inner fetch stays in an infinite loop. We have also tried rebooting the server to clear out any data stuck in memory. We are running SQL Server 2008R2

I can provide code if needed. Any help is greatly appreciated. We are impacting production at this point.
0
SolarWinds® VoIP and Network Quality Manager(VNQM)
SolarWinds® VoIP and Network Quality Manager(VNQM)

WAN and VoIP monitoring tools that can help with troubleshooting via an intuitive web interface. Review quality of service data, including jitter, latency, packet loss, and MOS. Troubleshoot call performance and correlate call issues with WAN performance for Cisco and Avaya calls

Table Name: Audit
Column name: CreatedON datetime

I am looking way to find the count of records that are created in every 30 minutes based on CreatedON column. At the end of the day i will run T-SQL select script and I need output like below.

Case_Count                CreatedON
10                               12/12/2018 9:00:00
12                               12/12/2018 9:30:00
18                                12/12/2018 10:00:00
.
.
.
.
200                          12/12/2018 23:30:00

Can help me how to accomplish above task using T-SQL script.
0
In MS Access VBA I am using TransferSpreadsheet to import raw data in preparation for purifying it and applying it to a table in the live database. The source worksheet always contains some rows of good test results of Radon in air or Radon in water. But it also always includes some 'junk data', specifically unused columns with #DIV/0! in the Results column. The TrasnsferSpreadsheet function cheerfully creates a Sheet1$_ImportErrors table listing all the divide-by-zero rows.

After a while there is a large number of such tables. So immediately after Sheet1$_ImportErrors is created, I would like to find it and delete it.

Here is the function that is doing the import.
Public Function GetRnWaterRaw (inToTable As String, inFullFilename As String) As Boolean
On Error GoTo HandleError
    
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "qdelEmptytblFromRnWaterRaw"
    DoCmd.SetWarnings True
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, inToTable, inFullFilename, True
'   Add new code here to delete the newly-created Sheet1$_ImportErrors table[embed=file 1406810][embed=file 1406811]
    GetRnWaterRaw = True

ExitProc:
    Exit Function
HandleError:
    GetRnWaterRaw = False
    MsgBox Err.Number & " " & Err.Description & " in GetRnWaterRaw", vbOKOnly + vbCritical, ModName
    Resume ExitProc
End Function

Open in new window



Can someone provide or point me to an example of VBA code that will check to see if the Sheet1$_ImportErrors table exists and proceed to delete it.
Import-target-tables.png
ImportErrors-tables.png
0
I want to create a website that would list a description for each of around one thousand textbooks.  I work at a University, so it is an educational project, purely for information for other faculty and students.  The textbooks themselves are located at multiple universities around the world, I am just going to link to them and describe them.

I would create a database (in Maria DB, probably) with the following fields:  Title, URL, Author, Author's credentials, Year of Publication, Number of pages, Type of license, Subject, Reviews & Ratings.  It would be great if visitors could a) search by keywords and b) filter the lists of books to find, say psychology only titles.

What is the path of least resistance for me to create a simple website like this? I have a couple of LAMP and XAMPP servers running pre-built solutions like a institutional repository or an open journal system.  Is there another pre-built product I can use?  If not, what would you suggest?
0
I'm using the Employees and Orders table in the Northwind sql server database.

Employees table looks like this:

Table_Employees.PNG
Orders table looks like this:

Table_Orders.PNG

Here is the script for the Employees table:

USE [Northwind]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Employees](
	[EmployeeID] [int] IDENTITY(1,1) NOT NULL,
	[LastName] [nvarchar](20) NOT NULL,
	[FirstName] [nvarchar](10) NOT NULL,
	[Title] [nvarchar](30) NULL,
	[TitleOfCourtesy] [nvarchar](25) NULL,
	[BirthDate] [datetime] NULL,
	[HireDate] [datetime] NULL,
	[Address] [nvarchar](60) NULL,
	[City] [nvarchar](15) NULL,
	[Region] [nvarchar](15) NULL,
	[PostalCode] [nvarchar](10) NULL,
	[Country] [nvarchar](15) NULL,
	[HomePhone] [nvarchar](24) NULL,
	[Extension] [nvarchar](4) NULL,
	[Photo] [image] NULL,
	[Notes] [ntext] NULL,
	[ReportsTo] [int] NULL,
	[PhotoPath] [nvarchar](255) NULL,
 CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED 
(
	[EmployeeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET IDENTITY_INSERT [dbo].[Employees] ON 

INSERT [dbo].[Employees] ([EmployeeID], [LastName], [FirstName], [Title], [TitleOfCourtesy], [BirthDate], [HireDate], [Address], [City], [Region], [PostalCode], [Country], [HomePhone], [Extension], [Photo], [Notes], [ReportsTo], [PhotoPath]) VALUES (1, N'Davolio', N'Nancy',

Open in new window

0
I've copied an on-premise SQL database to Azure SQL for testing, using the Azure online migration process, which is done and the database is in ready to cutover mode..  
From a local Windows 10 machine using ODBC driver 17, I can set up an ODBC connection to the Azure SQL database using Active Directory Integrated security and it works fine.
From the same machine, SSMS 17 connects to the Azure SQL database fine.
However when I try the same ODBC connection from an Azure VM (Server 2016, joined to the local domain via VPN) I cannot get it to authenticate even with the same domain user.  On the VM, I've tried ODBC driver 13.1 and 17.2.  Here is the error message I get:

"Microsoft ODBC Driver for SQL Server Version 13.00.4414

Running connectivity tests...

Attempting connection
[Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Failed to authenticate the user '<user>' in Active Directory (Authentication option is 'ActiveDirectoryIntegrated').
Error code 0xCAA9001F; state 10
Integrated Windows authentication supported only in federation flow.

TESTS FAILED!"

I'm not sure why this would be any different for an Azure VM than a local machine--both are domain joined.
-Azure AD Connect is synchronizing users to Azure.
-I have the firewall open on the Azure SQL database server to allow both local subnet and the Azure vNet the VM is on.
-I have the ADAL for SQL Server and the Microsoft Online Services Sign-in Assistant both installed on the VM.

Thanks for any …
0
Hi

Can you please tell me how to use getdate:00:00:00 and getdate:11:59:00 in SQL between condition

select count(*) from tablename where createdon between 'getdate:00:00:00' and 'getdate:11:59:00'

thanks in advance
0
Hi Experts ~
I'm looking for guidance on best practices when working with a database server, and a web server.
We have both and all our sites are hosted on the web server.
The work is all done on the d/b server and for many sites, .csv files are created from a SQL query.

My question:
Outside of reducing d/b server traffic - Is there any performance benefit on the site to storing those resulting .csv files on the web server vs. the d/b server?

Thanks
0
Hello,

I have created SQL job using below script to execute every 30 minutes interval from 12:00:00 AM and 11:59:59 PM. The job will create new CSV file on shared folder for every 30 minutes. Instead of creating new CSV file (multiple CSV) every time is it possible in SQL to concat (append) new data on existing file like below? If its possible in MS SQL, can you please share the sample script.

Like: data should be appended like below on single file:

Execution DateTime: 12102018 20:00
Count: 10

Execution DateTime: 12102018 20:30
Count: 10

Execution DateTime: 12102018 21:00
Count: 15

Execution DateTime: 12102018 21:30
Count: 10
DECLARE @FileName VARCHAR(500) = 'E:\FileShare\Count_' + FORMAT(getdate(), 'ddMMyyyy_HHmm') + '.csv'
DECLARE @SQLCmd VARCHAR(500) = 'bcp "select count(*) from DBName.dbo.IB where Notes = ''BO'' and CreatedOn between ''2018-11-22 00:00:10.000'' and ''2018-11-22 23:59:10.000''" queryout ' + @FileName + ' -T -c'
EXECUTE master..xp_cmdshell @SQLCmd

Open in new window

0
I have an InfoPath form connected to a SQL database.  I have two fields in the Query window for the user to choose from in retrieving records.  Previously I had three fields, but it was decided that the user did not ever query on the third field.  When I try and query with the new form on just the two fields, I get an error stating that there are no records.  I know that there is a record based on those 2 fields.  When I go back and use an older version of the InfoPath form and enter just the two fields, the record is returned.

What have I not done properly in modifying the form to only have the two fields to query on?

Any and all help would be greatly appreciated.
0
Determine the Perfect Price for Your IT Services
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Hello,

My requirement:
1.      I have to fetch number of records created in half an hour interval from 12am to 11:59pm in every 30 minutes.
2.      We have make it dynamic to run every 30 minute.
3.      This job will give a report for everyday and every 30 mints interval in csv format

To achieve above, I have created below SQL but it's giving error. Can you please help me to fix below error.
DECLARE @FileName VARCHAR(500) = 'E:\FileShare\YourData.csv'
DECLARE @SQLCmd VARCHAR(500) = 'bcp "select count(*) from IB where Notes = ''M_BO'' and CreatedOn between ''2018-11-22 00:00:10.000'' and ''2018-11-22 23:59:10.000''" queryout ' + @FileName + ' -T -c'
EXECUTE master..xp_cmdshell @SQLCmd

Open in new window

Error:
NULL
Starting copy...
SQLState = S0002, NativeError = 208
Error = [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Invalid object name 'IB'.
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 11 for SQL Server]Unable to resolve column level collations
NULL
BCP copy out failed
NULL
1
I am developing an employee database. I added all employees to an organisational unit (department etc) - and each organisational unit belongs to another organisational unit, except for the top one which is blank; the CEO. With a self-join, I can then create a hierarchy once all the data has been added.

 
 screengrab2.png

I tried creating a function which would populate a collection from the uppermost organisational unit (the CEO) all the way down and I used recursions to traverse the tree.

Function preorderProcessing(parentID As Integer)

    Dim MyDB            As DAO.Database
    Dim MyRS            As DAO.Recordset
    Dim x               As Integer
    
    Set MyDB = CurrentDb()
    Dim qdf As DAO.QueryDef
    Set colOrgUnits = New Collection
    
    If parentID = 0 Then
        Set qdf = MyDB.QueryDefs("qyrOrgUnits_SearchParentID_Null")
    Else
        Set qdf = MyDB.QueryDefs("qryOrgUnits_SearchParentID")
        qdf.Parameters("parentID") = parentID
    End If
    
    Dim rs As DAO.Recordset
    Set rs = qdf.OpenRecordset
    
    Do While Not rs.EOF
      colOrgUnits.Add (rs.Fields("org_OrganisationUnitID"))
      rs.MoveNext
    Loop
    rs.Close
    
    'debug
    Debug.Print "Now checking units: "
    For x = 1 To colOrgUnits.Count
        Debug.Print "In this stack: " & colOrgUnits.Item(x)
    Next x
    
    Debug.Print "COUNT: " & colOrgUnits.Count
    If colOrgUnits.Count > 0 Then
        For x = 1 To colOrgUnits.Count
            

Open in new window

0
 <Label>Status of Ticket :&nbsp;&nbsp;</Label><input type="radio" name="radnew" value="New Ticket">&nbsp;New Ticket</input>&nbsp;<input type="radio" name="radnew" value="nocategory">&nbsp;No Category</input><br /><br />
<Label>Origin of Ticket :&nbsp;&nbsp;</Label><input type="checkbox" name="emailsupport" value="emailsupport" />&nbsp;email to support&nbsp;<input type="checkbox" name="phonesupport" value="phonesupport" />&nbsp;Phone to support&nbsp;<input type="checkbox" name="smssupport" value="smssupport" />&nbsp;SMS to support&nbsp;<input type="checkbox" name="projectsupport" value="projectsupport" />&nbsp;Project work<br /><br />

Open in new window


I have the above code which is on a php page reporting database results
I need to be able to have the various radio and check boxes reflect that they have / have not got a value in the database and appear to be checked / unchecked if this is true / false
Can someone please give me a pointer on how to do it
0
Hi,
I have been tasked with a project where we have 4 identically constructed databases with different customer data in each running on premises using MSSQL and Microsoft Dynamics 2016.  I need to export specific customer data from each of the 4 databases and import that data into a MySQL database that is hosted off site with our web site. This data will then be periodically uploaded to the website database either with triggers or at a timed interval.

As I have very little experience with this type of database project, I am seeking information on how this can be done and what basic steps are needed in order for this to work.  Any additional details would be appreciated.
Thank you!
Troy
0
We have 5 machines, each machine inserts a record to a SQL database every second.
At the record generation time, it writes the order number, machineID, a machine status (either U up or D down) and the time period).
Machine data is attached.

Ref:
(note that ordernumber is 7 digits and the next digit is machineID, so first line order is 2633448 and machine is 2)
OrderNumber      Machine ID      Status      TimePeriod
2633448      2      U      2018-12-07 08:08:13.000
2601441      4      U      2018-12-07 08:08:12.000
2637183      3      D      2018-12-07 08:08:08.000
2624657      5      U      2018-12-07 08:08:08.000
2633448      2      U      2018-12-07 08:08:03.000
2601441      4      U      2018-12-07 08:08:02.000
2637183      3      D      2018-12-07 08:07:59.000
2624657      5      U      2018-12-07 08:07:59.000
2633448      2      U      2018-12-07 08:07:54.000
2601441      4      U      2018-12-07 08:07:52.000

What I'm trying to accomplish is a report of time-based data showing when the machine switches status and the total duration of the status.
Something like this:
OrderNumber      Machine ID      Status      Status End      Status Start      Duration
2633448      2      U      2018-12-07 08:08:13.000      2018-12-07 07:57:10.000      0:11:03
2633448      2      D      2018-12-07 07:57:49.000      2018-12-07 07:57:42.000      0:00:07
2633448      2      U      2018-12-07 07:57:41.000      2018-12-07 07:57:11.000      0:00:30

As part of the first step, my thinking is to find the points where the status changed using a lag.
Like this...

Select 
[OrderNumber],
[MachineNumber],
Status,
Lag(Timeperiod, 1) Over (partition by Ordernumber, [Status] order by OrderNumber, TimePEriod) As 

Open in new window

0
I trying to set up a job with SQL Server to send an email out whenever the Builder's General Contractor Ins Exp Date is coming up for expiration or expired.  If it is expiring/expired to send an email out to the specific contractor in the email column letting them know of it expiring.  I have set up database mail and tested that it is working.

How do I do this?
Capture.PNG
0

Databases

57K

Solutions

40K

Contributors

Databases are organized collections of data, most commonly accessed through management systems including schemas, tables, queries and processes that allow users to enter and manipulate the information or utilize it in other fashions, such as with web applications or for reporting purposes.