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.

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

Sign up to Post

I am new to SQL and am attempting to install SQL 2016 onto a Server running MS 2012R2.  I keep getting to the Service Accounts page and get two errors:
Specify the account in a domain format (domain\username) and specify the account in a local account (localhost\username).  I don't know what account???  Attached is a picture.  Any help would be appreciated.SQL Server 2016 setup services page errors
0
Simple Misconfiguration =Network Vulnerability
Simple Misconfiguration =Network Vulnerability

In this technical webinar, AlgoSec will present several examples of common misconfigurations; including a basic device change, business application connectivity changes, and data center migrations. Learn best practices to protect your business from attack.

I'm trying to check for the existence of windows folder in SQL Server 2014.  I specified an existing folder to check my routine, but when I run it, I get back that the directory does not exist.  Can someone tell me what I'm missing in my code?

My routine is as follows:

ALTER PROCEDURE [dbo].[uspCheckPathExists] 
--	@ParentFolder VARCHAR(MAX),
--	@DirName VARCHAR(MAX)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    DECLARE @path    VARCHAR(MAX),
	        @result  INT;

	SET @path = 'C:\HCDA\' + 'Log';
	--SET @path = @ParentFolder + @DirName;
	print 'Directory is ' + @path;

	EXEC master.dbo.xp_fileexist @path, @result OUTPUT;
	if @result = 1  print 'Directory exists';
	else begin
	   print 'Directory do not exist';
	   set @result = 0;
	end;

	print 'The return result is ' + str(@result,1);
	print 'Done';

	RETURN @result;
END

Open in new window


The message I get when I run the routine is as follows:

Directory is C:\HCDA\Log
Msg 22027, Level 15, State 1, Line 2
Usage:  EXECUTE xp_fileexist <filename> [, <file_exists INT> OUTPUT]
Directory do not exist
The return result is 0
Done

(1 row(s) affected)


Thank you.
0
Hello,

I am attempting to add two date paramaters to an existing query using a "between" clause like this:

AND TIMES.ADMINISTERED_TIME between '2017-01-01' and '2018-01-01'

Open in new window


The problem is that this part seems to get ignored (doesn't change the results of the query) when added.  

I do not receive any errors and I have tried various ways of referencing the ADMINISTERED_TIME field with not avail.

The following query (for testing) works as expected:

SELECT * FROM  ORDER_MED_ADMIN_TIMES WHERE ADMINISTERED_TIME BETWEEN '2017-01-01' AND '2018-01-01'

Open in new window


Here is the entire query where the date between clause has no effect:

SELECT
DISTINCT 
ORD.CLIENTID ||' '|| TRIM(CLTLST)||', '||TRIM(CLTFST) AS CLIENT
, Case when varchar_format (stopdatetime, 'YYYY-MM-DD HH24:MI') >= varchar_format (I.CHANGESTAMP, 'YYYY-MM-DD HH24:MI') then 'discharged' else ORD.STATUS end as STATUS
,UPPER(BRAND_NAME) AS TEST
,TRIM(BRAND_NAME)||' ('||TRIM(GENERIC_NAME)||') '||TRIM(STRENGTH) AS MEDICATION
,TRIM(SIG_ACTION)||' '|| TRIM(SIG_DOSE)||' '|| TRIM(SIG_DOSE_UNIT)||' '|| TRIM(SIG_ROUTE)||' '|| TRIM(SIG_DOSE_TIMING)||' '|| TRIM(SIG_DOSE_OTHER)||' '|| TRIM(SIG_DOSE_OTHER) AS SIG
, DATE(SCHEDULEDSTARTDT) AS START_DATE
, DATE(SCHEDULEDENDDT) AS END_DATE
, DATE(I.DISCHARGEDATE) as INPATIENT_DISCHARGE
,TRIM(INSTRUCTIONS) AS INSTRUCTION
,TRIM(STAFF.SMMLNM) ||', '|| TRIM(STAFF.SMMFNM) AS ORDERING_STAFF
,TRIM(STAFF2.SMMLNM) ||', '|| TRIM(STAFF2.SMMFNM) AS 

Open in new window

0
hi,

hi, about life cycle of Oracle 18c, how long we can use it once it is release ?

I come from MS SQL back ground we don't have this restriction, we can keep using it until we upgrade, then all license including CAL need to upgrade too.
0
Dear Experts

Could you please help me to shrink / compress SQL databases?  Thanks.
0
Need some sql that will get the min on a column and return the entire row without using a subquery or a group by... basically an analytic sql statement that doesn't require as subquery to return the lowest column row data based upon a particular column. Like a group by but not using a group by or a subquery.

thanks
0
Hi EE
I would like some advice with a Like search. I have an address field in a customer table, the address is combined Number and street (32 Smith Street) using the following
Search = "SELECT Address FROM qryCitySearchLeads WHERE [Address] Like " & Chr$(34) & "*" & Me!txtAddress & "*" & Chr$(34)

I would like to search for smith without the number because sometimes the number is not known.
Using the above I get not found unless I include the number

I have used “*” both ends Left only and right only. I have found a work around but curious to why the above won’t work.
Any help appreciated

Chestera
0
Greetings,

I am  working on servlet  project and I  had made a jsp form in my project.  I have fetch the data in my dropdownlist using jsp code.  But the error is that when I select dropdownlist
I  want  to fetch the data in my multiple textbox according to my sql database column.

Please can you help me?
0
Hi Experts,

This is in regards to following

https://www.experts-exchange.com/questions/29100944/Function-to-merge-2-records.html#acceptAnswerByMember

I would like to have a function/stored proc that loops thru all my records in Employeestbl and for every two records with same email it executes the stored proc below according to SkipImport = 0 being first.

procedure usp_update_employeestbl (@empid1 varchar(10) = NULL, @empid2 varchar(10)  = NULL, @status varchar(100) output)
as
BEGIN


if not exists (select NULL from employeestbl where ID = @empid1) begin set @status = 'ERROR - NO EMPID1' return end
if not exists (select NULL from employeestbl where ID = @empid2) begin set @status = 'ERROR - NO EMPID2' return end

-- first step is to identify columns that might be subject to updates

declare @where varchar(max)

select @where = isnull(@where+char(13)+char(10)+'or ','') + '(E.['+c.[name]+'] is NULL and S.['+c.[name]+'] is not NULL '+case when t.[name] like '%date%' then ' or E.['+c.[name]+'] < S.['+c.[name]+']' else '' end +')'
from sys.columns c
inner join sys.types t on t.user_type_id = c.user_type_id
where object_name(c.object_id) = 'EmployeesTbl'
and (c.is_nullable = 1
or t.[name] like '%date%') 

print @where

-- set up selection criteria to extract just the needed column names where there is a change

declare @select varchar(max)

select @select = isnull(@select+char(13)+char(10)+' + ','') + 'case when E.['+c.[name]+'] is NULL and S.['+c.[name]+'] is not

Open in new window

0
After restoring several SQL DBs to my instance of SQL Server, is there any way to query the DBs to determine what version they came from before I restored them?

The query "select @@Version" appears to query my instance rather than the individual DB, but I'd like to know which DBs came from Express, the past version, etc.

Is that even possible?
0
Get Cisco Certified in IT Security
Get Cisco Certified in IT Security

There’s a high demand for IT security experts and network administrators who can safeguard the data that individuals, corporations, and governments rely on every day. Pursue your B.S. in Network Operations and Security and gain the credentials you need for this high-growth field.

I am trying to create a crystal report. I have customers, work orders and the date enter for that work order. I am trying to show the new customers in past 3 years. I can't just exclude date enter below 2015 and show customers above the year 2015. if a customer been using us from 1990 and still using us will also appear if I exclude date enter below 2015. since some work orders are there for that customer after 2015. I want to see the minimum date enter and exclude those customers whose minimum date is less than 2015.
 Problem with that is I can do filters on summarized values like sum, max, min in the crystal.
Is there any other approach for this?
0
I need a SQL Select that change the orientation of a table.  Currently, I have a table containing two columns for each product.  Each row represents a attribute value for the product.  I need to list all the attributes horizontally as column not vertically as rows.  

Before:
UPC           Attribute ID           Attribute Value

1234         Brand                     Pillow
1234         Sub-Brand             MyPillow
1234         Color                       White
1234         Fixture                    N/A
2188         Brand                     Kennmore
3221         Color                      Black

After: (this is the format I need this data in)
UPC        Brand        Sub-Brand    Color     Fixture  

1234        Pillow        MyPillow      White     N/A
2188        Kennmore
3221                                                 Black
0
I am just writing a simple python script to connect to sql server .  SQL server that  I am trying to connect does not have trusted connection and so i have to login using integrated authentication. so in my script  i  try to login using the username and password that i use to login into management studio. but when i execute the script it gives me an error message saying login failed for user "it shows my windows username" and just ignores the username that I am passing in my script.
Below is my script.


server = "xxxxxxx.svr.us.xxxxxx.net\\Servername"
database = "MyDatabase"
username = "username"
password = "password"

con = pyodbc.connect('DRIVER={SQL Server};Server='+server+';Database='+database+';username='+username+';password='+password)
print (con)
con = conStr
cur = con.cursor()   #Generates error on this line
cur.execute("SELECT top 10 * from mytable;")

error that i get

pyodbc.Error: ('28000', "[28000] [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'OVER HERE I SEE MY WINDOWS USERNAME. (18456) (SQLDriverConnect)")
0
Dear Wizards, is there any CMD or Powershell command to get these information?
- RAID status
- Disk information (Good, bad sectors...)
- LED light status on Hard disk

Many thanks!
0
I have a Zabbix Monitoring System running on CentOS 7 which is getting out of control in size.

The System is running on MariaDB and is approx. 150GB in size and at the rate of expansion will consume the free space on the drive in less than 2 days. I have attempted to delete unwanted history from the database using

http://whatizee.blogspot.com/2016/10/zabbix-history-table-clean-up.html

This however appears to have deleted the history, but the database size remained constant and didn't change.

 
CREATE TABLE acknowledges_new LIKE acknowledges;
INSERT INTO acknowledges_new SELECT * FROM acknowledges;
CREATE TABLE acknowledges_new1 LIKE acknowledges;
insert into acknowledges_new1 SELECT * FROM acknowledges WHERE Clock > '1526270400';

********************************************
CREATE TABLE alerts_backup LIKE alerts;
INSERT INTO alerts_backup select * from alerts;
CREATE TABLE alerts_new LIKE alerts;
INSERT INTO alerts_new select * from alerts WHERE Clock > '1526270400';
ALTER TABLE alerts RENAME alerts_OLD;
ALTER TABLE alerts_new RENAME alerts;

*************************************************
CREATE TABLE events_backup LIKE events;
INSERT INTO events_backup select * from events;
CREATE TABLE events_new LIKE events;
INSERT INTO events_new select * from events WHERE Clock > '1526270400';
ALTER TABLE events RENAME events_OLD;
ALTER TABLE events_new RENAME events;

**************************************************************************

CREATE TABLE 

Open in new window

0
Using SharePoint 2013 Standard on Premise. Receiving an error when uploading files to a document library where more space is needed. I check the SharePoint server and SQL server and there is plenty of room. I have about 200 GB on the site now.  In Central Admin, Individual quota does not have any check marks in those configurations. What do I need to do to increase the storage for the site?
0
im facing a relativley complicated situation and i was wondering if somebody has experience with this issue.
i need to deploy SQL server to about 20 servers. and im requested to build a Powershell script that handles all of this at once.
what i need to do is a script that does the following.
1-create a diffrent AD- user name and password for every server.
2-creates a text file for the usernames and passwords that has been created.
3-use these accounts to deploy SQL on every server with the right service accounts that has been created.
its a bit complicated and i really can use pointers or ideas from experts who has done this before.
0
I have a working system that uses SQL Express as the database.
On 2 computers, window 8.1, everything is okay.
I have just installed another computer with Windows 10 and the program brings an error when assigning MyQuery.FieldByName('MyDateField').AsDateTime to a DateTime variable.
It only happens on this computer.

I have installed the program on my development computer that is also Windows 10 and using SQL Express and have no problems.

The ShortDateFormat is the same on all computers.

1. The command ShowMessage(ShortDateFormat) displays the same on all computers 'd/MM/yyyy'
2. FieldByName('MyDateField').AsString on the working computers displays the date in the ShortDateFormat, like '31/12/1999'
3. FieldByName('MyDateField').AsString on the new computer displays the date as '1999-12-31'
4. FloatToStr(FieldByName('MyDateField').AsDateTime) on the new computer raises error "'1999-12-31' is not a valie date time."

All the settings are the same

I have never seen anything like that and I need your help here.

Using Delphi 7 and SDAC
0
What can I do to tell if this is a true date in SQL "1942-06-10-00-00-00", If it is not a datetime, then I want a null.
0
Managing Security & Risk at the Speed of Business
Managing Security & Risk at the Speed of Business

Gartner Research VP, Neil McDonald & AlgoSec CTO, Prof. Avishai Wool, discuss the business-driven approach to automated security policy management, its benefits and how to align security policy management with business processes to address today's security challenges.

I have a from that is supposed to create, update and delete users as well as export a csv. the problems I am having are:
1) my edit button brings up the correct form, I can change information but it doesn't send the updated info to the mysql server.
2) I am not getting all the data from the sql server... I have 4 rows on the workbench but I am only getting 3 rows sent back via the localhost.
3) the export form sends a csv to the computer but i need to select a date range, right now it is set to give me anything that was entered yesterday.
I found this code online and think it will help but I'm not really sure how to implement it into my current code...I tried but it didn't work for me. This maybe because of 2 different reasons, a) the download page doesn't reflect variables for date range and I can find a good source to create it that way and mysql server table asks for a timestamp not an actual date... I can change that if needed, but really just don't know what to do with all this right now.
0
Ok below is my current SQL statement for a query in AccessDB I am trying to find a way to add a find max date (or most recent date) from the TxnDate column and only return results with the most recent TxnDate. Any help is greatly appreciated. Thanka


SELECT Customer.FullName, Customer.Email, Customer.Phone, ReceivePayment.TxnDate, ReceivePayment.TotalAmount, Customer.TotalBalance, Customer.CustomFieldDepartment
FROM Customer INNER JOIN ReceivePayment ON Customer.ListID = ReceivePayment.CustomerRefListID
GROUP BY Customer.FullName, Customer.Email, Customer.Phone, ReceivePayment.TxnDate, ReceivePayment.TotalAmount, Customer.TotalBalance, Customer.CustomFieldDepartment, Customer.IsActive
HAVING (((Customer.TotalBalance)>30) AND ((Customer.IsActive)=Yes))
ORDER BY Customer.FullName;
0
I have a view in Oracle 12c Windows that is running very poorly.  Lots of full table scans and very costly.  The developer has asked if I could create some indexes.  But I've been unsuccessful in getting the view to use any of the indexes I've tried.

Here is a snippet of the view.  

select
L.ADRDIR ADDRESS,
L.TAXDIST DISTRIBUTION,
L.UNITNO SUITE_NUMBER,
L.USER10 DATE_CREATED,
O.OWN1 OWNER1,
O.OWN2 OWNER2,
NVL(A2.VAL05.0) AS NEW_CONSTRUCTION,
NVL(A.APRBLDG,0) AS BLDG_VALUE
FROM      IAS.PARDAT P
            LEFT JOIN IAS.LEGDATL_VIEW L
                  ON      P.PARID = L.PARID AND P.TAXYR = L.TAXYR AND P.CUR = L.CUR
            LEFT JOIN IAS.APRVAL A
                  ON P.PARID = A.PARID AND P.TAXYR = A.TAXYR AND P.CUR = A.CUR
            LEFT JOIN IAS.OWNDAT O
                  ON P.PARID = O.PARID AND P.TAXYR = O.TAXYR      AND P.CUR = O.CUR
            LEFT JOIN IAS.ASMT A2
                   ON P.PARID = A2.PARID AND P.TAXYR = A2.TAXYR AND P.CUR = A2.CUR
WHERE      P.TAXYR = (SELECT THISYEAR FROM IAS.AASYSJUR)


The view selected on in the query, "IAS.LEGDATL_VIEW", is basically a "select * from IAS.LEGDAT WHERE CUR="Y";)

All these tables pretty much have 90% of the record with "CUR="Y".   The other 10% is another value. No nulls.

The explain plan shows full scans on the following tables:

ISA.LEGDAT
IAS.APRVAL
IAS.OWNDAT
IAS.ASMT

I figure if I can get an index working for IAS.OWNDAT, then I can get an index working on the other tables with the same attributes.  so I've been focusing on table IAS.OWNDAT.

So far, I've tried creating …
0
I am attempting to use an HTML form contained in a modal to transmit a PDF and an image to a PostgreSQL database. I don't have the database set up yet, but I've already run into an issue. My file structure has a folder for 'models', 'routes', 'views', and a main 'app.js' file. I've included the app file below as well as the files containing the issue. The form is in the 'btb.html' file (contained within the views folder) and begins on line 211 (there is a second form below it, but I haven't begun to use that one yet). The form should be posting to the URL of the 'btbRoute.js' file (contained within the routes folder) and the "router.post" there should handle the response and create an entry in the database. I've also included two files from the 'models' folder-- the 'order.js' file that defines the schema model for my database entry and the 'index.js' file that will eventually connect all my models together.

I'll eventually want to render the information that I'm inputting elsewhere on the website, so if anything that I'm doing would prevent that I would need to find a different solution (I have no idea if that's even a thing, but just in case).

Thanks in advance.
index.js
order.js
btbRoute.js
btb.html
app.js
0
I am trying to BCP a text file using a File template  (FMT) file.  and when I execute the command i get this error

Starting copy...
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC Driver 11 for SQL Server]String data, right truncation

0 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 281

This is the content of the FMT file.
---start---
12.0
2
1       SQLCHAR             2       10      "|"   1     LUI        SQL_Latin1_General_CP1_CI_AS
2       SQLCHAR             0       8       "|\r\n"   2     CUI        SQL_Latin1_General_CP1_CI_AS
--end--

This is the format of the target table in sql server
--Start--
CREATE TABLE [dbo].[LUI](
      [LUI] [varchar](10) NOT NULL,
      [CUI] [varchar](8) NOT NULL
) ON [Metathesaurus_Data]

the command that was executed is.

--start--
 bcp dateabase.[dbo].[LUI]  IN C:\Documents\LUI.txt  -f C:\document\LUI.fmt -T -S Secure.server.local

--end--

and here is a sample of the data
--start--
L11056346|C3542425|
L1105638|C0015967|
L1105638|C0563594|
L11056420|C3554848|
L11056420|C3556751|
--end--

Can any one point me in the correct direction to make this work?
0
I have a query in Classic ASP that has the potential to grow to thousands of rows.



  <%

' Our own constants:
Const PAGE_SIZE = 10  ' The size of our pages.

' Declare our variables... always good practice!
Dim strURL     ' The URL of this page so the form will work
               ' no matter what this file is named.

Dim cnnSearch  ' ADO connection
Dim rstSearch  ' ADO recordset
Dim strDBPath  ' path to our Access database (*.mdb) file

Dim strSQL     ' The SQL Query we build on the fly
Dim strSearch  ' The text being looked for

Dim iPageCurrent ' The page we're currently on
Dim iPageCount   ' Number of pages of records
Dim iRecordCount ' Count of the records returned
Dim I            ' Standard looping variable

' Retreive the URL of this page from Server Variables
strURL = Request.ServerVariables("URL")

' Retreive the term being searched for.  I'm doing it on
' the QS since that allows people to bookmark results.
' You could just as easily have used the form collection.
strSearch = Request("search")

' Retrieve page to show or default to the first
If Request.QueryString("page") = "" Then
	iPageCurrent = 1
Else
	iPageCurrent = CInt(Request.QueryString("page"))
End If


If strSearch = "" Then

  'Display all the details passed

			'	for i=1 to request.form.count
			'	response.write Trim(request.form.key(i))&"; "& Trim(request.form.item(i)) &"<br>"
			'	Next

		'Response.End 
	
	Set cnnSearch = Server.CreateObject("ADODB.Connection")

	' This line is for the 

Open in new window

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.